package com.jojubanking.boot.module.system.service.xytjjg;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.jojubanking.boot.module.system.dal.mysql.xytjjg.XytjjgBatchInsertMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import org.springframework.validation.annotation.Validated;

import java.sql.*;
import java.util.*;
import com.jojubanking.boot.module.system.controller.admin.xytjjg.vo.*;
import com.jojubanking.boot.module.system.dal.dataobject.xytjjg.XytjjgDO;
import com.jojubanking.boot.framework.common.pojo.PageResult;

import com.jojubanking.boot.module.system.convert.xytjjg.XytjjgConvert;
import com.jojubanking.boot.module.system.dal.mysql.xytjjg.XytjjgMapper;

import static com.jojubanking.boot.framework.common.exception.util.ServiceExceptionUtil.exception;
import static com.jojubanking.boot.module.system.enums.ErrorCodeConstants.*;

/**
 * HIS项目 Service 实现类
 *
 * @author admin
 */
@Service
@Validated
@Slf4j
public class XytjjgServiceImpl implements XytjjgService {

    @Resource
    private XytjjgMapper xytjjgMapper;

    @Resource
    private XytjjgBatchInsertMapper xytjjgBatchInsertMapper;
    @Override
    public Long createXytjjg(XytjjgCreateReqVO createReqVO) {
        // 插入
        XytjjgDO xytjjg = XytjjgConvert.INSTANCE.convert(createReqVO);
        xytjjgMapper.insert(xytjjg);
        // 返回
        return xytjjg.getId();
    }

    @Override
    public void updateXytjjg(XytjjgUpdateReqVO updateReqVO) {
        // 校验存在
        this.validateXytjjgExists(updateReqVO.getId());
        // 更新
        XytjjgDO updateObj = XytjjgConvert.INSTANCE.convert(updateReqVO);
        xytjjgMapper.updateById(updateObj);
    }

    @Override
    public void deleteXytjjg(Long id) {
        // 校验存在
        this.validateXytjjgExists(id);
        // 删除
        xytjjgMapper.deleteById(id);
    }

    private void validateXytjjgExists(Long id) {
        if (xytjjgMapper.selectById(id) == null) {
            throw exception(XYTJJG_NOT_EXISTS);
        }
    }

    @Override
    public XytjjgDO getXytjjg(Long id) {
        return xytjjgMapper.selectById(id);
    }

    @Override
    public List<XytjjgDO> getXytjjgList(Collection<Long> ids) {
        return xytjjgMapper.selectBatchIds(ids);
    }

    @Override
    public PageResult<XytjjgDO> getXytjjgPage(XytjjgPageReqVO pageReqVO) {
        return xytjjgMapper.selectPage(pageReqVO);
    }

    @Override
    public List<XytjjgDO> getXytjjgList(XytjjgExportReqVO exportReqVO) {
        return xytjjgMapper.selectList(exportReqVO);
    }

    @Override
    public List<XytjjgDO> getXytjjgFilterList(XytjjgExportReqVO exportReqVO) {
        return xytjjgMapper.selectFilterList(exportReqVO);
    }

//        return selectList(new LambdaQueryWrapper<XytjjgDO>()
//                .like(XytjjgDO::getItemno, reqVO.getItemno())
//            .or().like(XytjjgDO::getItemname, reqVO.getItemname())
//            .orderByAsc(XytjjgDO::getItemno));
    public List<XytjjgDO> getXytjjgFilterList01(XytjjgExportReqVO exportReqVO) {
        log.info("getXytjjgFilterList01");
        String URL = "jdbc:sqlserver://168.168.0.10:1433;databaseName=THIS4";
        String USER = "joju";
        String PASSWORD = "Joju@123";
        List<XytjjgDO> list = new ArrayList<>();
        // 1.加载驱动程序
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // 2.获得数据库链接
            log.info("开始链接数据库");
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
            log.info("打印链接状态：" + conn);
            // 3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
            String sql = "select * from joju_lcxm  where name like '%" + exportReqVO.getItemname()
                    + "%' or id like '%" + exportReqVO.getItemno() + "%' order by id asc";
            log.info("打印joju_lcxm sql:" + sql);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            log.info("返回结果是：" + rs);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                //log.info("开始处理数据");
                XytjjgDO xytjjgDO = new XytjjgDO();
                //标识（1小项目 2临床 ）
                xytjjgDO.setItemflag("2");
                xytjjgDO.setItemname(rs.getString("name"));
                xytjjgDO.setItemno(rs.getString("id"));
                xytjjgDO.setItemprice(rs.getString("xmdj"));
                list.add(xytjjgDO);
            }
            log.info("list size： " + list.size());

            // 关闭资源【多谢指正】
            rs.close();
            statement.close();

            // 4.查询小项目表
            //两个表都要查，数据合并一起返回
            {
                sql = "select * from joju_sfxxm  where name like '%" + exportReqVO.getItemname()
                        + "%' or id like '%" + exportReqVO.getItemno() + "%' order by id asc";
                log.info("joju_sfxxm sql:" + sql);
                statement = conn.createStatement();
                rs = statement.executeQuery(sql);
                log.info("xxm返回结果是：" + rs);
                // 5.处理数据库的返回结果(使用ResultSet类)
                while (rs.next()) {
                    //log.info("开始处理数据");
                    XytjjgDO xytjjgDO = new XytjjgDO();
                    //标识（1小项目 2临床 ）
                    xytjjgDO.setItemflag("1");
                    xytjjgDO.setItemname(rs.getString("name"));
                    xytjjgDO.setItemno(rs.getString("id"));
                    xytjjgDO.setItemprice(rs.getString("xmdj"));
                    list.add(xytjjgDO);
                }
                log.info("xxm list size： " + list.size());
            }

            // 关闭资源【多谢指正】
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;

    }

    @Override
    public String updateItemsFromHIS(){
            log.info("拼接数据");
//        String URL = "jdbc:sqlserver:thin:@//168.168.0.10:1433/THIS4";
            String URL = "jdbc:sqlserver://168.168.0.10:1433;databaseName=THIS4";
            String USER = "joju";
            String PASSWORD = "1";
            //List<Map<String, Object>> list = null;
            List<XytjjgDO> jgItemsList = new ArrayList<>();
            // 1.加载驱动程序
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                // 2.获得数据库链接
                log.info("开始链接数据库");
                Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
                log.info("打印链接状态：" + conn);
                // 3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）

                String sql = "select * from JOJU_XYTJJG  where 1 = 1";
                log.info("打印sql" + sql);
                Statement statement = conn.createStatement();
                ResultSet rs = statement.executeQuery(sql);
                log.info("返回结果是：" + rs);
                // 4.处理数据库的返回结果(使用ResultSet类)
                while (rs.next()) {
                    log.info("开始处理数据");
                    XytjjgDO xytjjgDO = new XytjjgDO();
                    xytjjgDO.setItemno(rs.getString("itemno"));
                    xytjjgDO.setItemname(rs.getString("itemno"));
                    xytjjgDO.setItemprice(rs.getString("itemno"));
                    xytjjgDO.setItemflag(rs.getString("itemno"));
                    jgItemsList.add(xytjjgDO);
                }

                // 关闭资源【多谢指正】
                rs.close();
                statement.close();
                conn.close();

                if(jgItemsList.size() != 0){
                    xytjjgBatchInsertMapper.saveOrUpdateBatch(jgItemsList, jgItemsList.size());
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return "";
        }
}
