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

import com.jojubanking.boot.module.system.controller.app.xytjinfo.vo.ItemListReqVO;
import com.jojubanking.boot.module.system.dal.dataobject.order.OrderDO;
import com.jojubanking.boot.module.system.dal.dataobject.xytjjg.XytjjgDO;
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.item.vo.*;
import com.jojubanking.boot.module.system.dal.dataobject.item.ItemDO;
import com.jojubanking.boot.framework.common.pojo.PageResult;

import com.jojubanking.boot.module.system.convert.item.ItemConvert;
import com.jojubanking.boot.module.system.dal.mysql.item.ItemMapper;

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

/**
 * 检查项目 Service 实现类
 *
 * @author admin
 */
@Service
@Validated
@Slf4j
public class ItemServiceImpl implements ItemService {

    @Resource
    private ItemMapper itemMapper;

    public Map<String, Object> getLCzxks(String itemname){
        log.info("getLCzxks");
        Map<String, Object> resmap = new HashMap<String, Object>();

        String URL = "jdbc:sqlserver://168.168.0.10:1433;databaseName=THIS4";
        String USER = "joju";
        String PASSWORD = "Joju@123";
        List<Map<String, Object>> 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 inHosnum = "811134";
            String sql = "select * from joju_lcxm  where name ='" + itemname + "'";
            log.info("打印sql" + sql);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            log.info("返回结果是：" + rs);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                log.info("开始处理数据");
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", rs.getString("id"));
                map.put("name", rs.getString("name"));
                map.put("zxks_id", rs.getString("zxks_id"));
                String strzxksname = rs.getString("zxks_name");
                if(strzxksname == null){
                    map.put("zxks_name", "");
                }else{
                    map.put("zxks_name", rs.getString("zxks_name"));
                }
                map.put("xmdj", rs.getString("xmdj"));
                list.add(map);
            }
            log.info("list size： " + list.size());
            // 关闭资源【多谢指正】
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //每个项目应该只对应一个zxks 或者为空
        if(list.size() == 0){
            log.info("执行科室为空，默认1716");
            resmap.put("zxks", "1716");
            resmap.put("zxksname", "儿童保健部");
            return resmap;
        }else{
            String strzxks = list.get(0).get("zxks_id").toString();
            String strzxksname = list.get(0).get("zxks_name").toString();
            if(strzxks.trim().isEmpty()){
                log.info("执行科室为空，默认1716");
                resmap.put("zxks", "1716");
                resmap.put("zxksname", "儿童保健部");
                return resmap;
            }else{
                log.info("执行科室为:" + strzxks);
                resmap.put("zxks", strzxks);
                resmap.put("zxksname", strzxksname);
                return resmap;
            }
        }
    }

    public Map<String, Object> getSFXMMzxks(String itemname){
        log.info("getSFXMMzxks");
        Map<String, Object> resmap = new HashMap<String, Object>();
//        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 = "Joju@123";
        List<Map<String, Object>> 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 inHosnum = "811134";
            String sql = "select * from joju_sfxxm  where name ='" + itemname  + "'";;
            log.info("打印sql" + sql);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            log.info("返回结果是：" + rs);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                log.info("开始处理数据");
                Map<String, Object> map = new HashMap<String, Object>();
//                log.info("打印itemType" + rs.getString("ItemType"));
                map.put("id", rs.getString("id"));
                map.put("name", rs.getString("name"));
                map.put("zxks_id", rs.getString("zxks_id"));
                String strzxksname = rs.getString("zxks_name");
                if(strzxksname == null){
                    map.put("zxks_name", "");
                }else{
                    map.put("zxks_name", rs.getString("zxks_name"));
                }
                map.put("xmdj", rs.getString("xmdj"));
                list.add(map);
            }
            log.info("list is " + list);
            // 关闭资源【多谢指正】
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //每个项目应该只对应一个zxks 或者为空
        if(list.size() == 0){
            log.info("执行科室为空，默认1716");
            resmap.put("zxks", "1716");
            resmap.put("zxksname", "儿童保健部");
            return resmap;
        }else{
            String strzxks = list.get(0).get("zxks_id").toString();
            String strzxksname = list.get(0).get("zxks_name").toString();
            if(strzxks.trim().isEmpty()){
                log.info("执行科室为空，默认1716");
                resmap.put("zxks", "1716");
                resmap.put("zxksname", "儿童保健部");
                return resmap;
            }else{
                log.info("执行科室为:" + strzxks);
                resmap.put("zxks", strzxks);
                resmap.put("zxksname", strzxksname);
                return resmap;
            }
        }

    }

    public List<Map<String, Object>> getItemPricexxm(String itemnos){
        log.info("getItemPricexxm");
        String URL = "jdbc:sqlserver://168.168.0.10:1433;databaseName=THIS4";
        String USER = "joju";
        String PASSWORD = "Joju@123";
        List<Map<String, Object>> 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_sfxxm  where id in (" + itemnos + ")";
            log.info("打印sql" + sql);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            log.info("返回结果是：" + rs);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                log.info("开始处理数据");
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", rs.getString("id"));
                map.put("name", rs.getString("name"));
                map.put("zxks_id", rs.getString("zxks_id"));
                map.put("xmdj", rs.getString("xmdj"));
                list.add(map);
            }
            log.info("list size： " + list);
            // 关闭资源【多谢指正】
            rs.close();
            statement.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> getItemPriceLc(String itemnos){
        log.info("getLCzxks");
        String URL = "jdbc:sqlserver://168.168.0.10:1433;databaseName=THIS4";
        String USER = "joju";
        String PASSWORD = "Joju@123";
        List<Map<String, Object>> 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 id in (" + itemnos + ")";
            log.info("打印sql" + sql);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            log.info("返回结果是：" + rs);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                log.info("开始处理数据");
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", rs.getString("id"));
                map.put("name", rs.getString("name"));
                map.put("zxks_id", rs.getString("zxks_id"));
                map.put("xmdj", rs.getString("xmdj"));
                list.add(map);
            }
            log.info("list size： " + list);
            // 关闭资源【多谢指正】
            rs.close();
            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public Object ItemPriceQuery(String QueryNum){
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String dburl = "jdbc:sqlserver://168.168.0.79:1433;DatabaseName = THIS4";
//        // 方案一
//        String dburl = "jdbc:sqlserver://localhost:1433;DatabaseName = SQL_Road;encrypt=false";
//        // 方案一
//        String dburl = "jdbc:sqlserver://localhost:1433;DatabaseName = SQL_Road;encrypt=true;trustServerCertificate=true";
        String userName = "a";//数据库对应用户
        String userPwd = "1";//数据库对应密码
        Map<String,Object> map=new HashMap<String,Object>();
        try {
            Class.forName(driverName);
            log.info("THIS4驱动连接成功!!!");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            log.info("THIS4驱动加载失败");
        }

        try {
            Connection dbcon = DriverManager.getConnection(dburl,userName,userPwd);
            log.info("THIS4数据库连接成功！");

            //预编译
            String sql="select * from VW_XMJGHQ where id='"+QueryNum+"'";
            Statement statement = dbcon.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            // 4.处理数据库的返回结果(使用ResultSet类)
            while (rs.next()) {
                map.put("name",rs.getString("name"));
                map.put("dxmmc",rs.getString("dxmmc"));
                map.put("py",rs.getString("py"));
                map.put("xmdw",rs.getString("xmdw"));
                map.put("xmdj",rs.getString("xmdj"));
                map.put("zxks_mc",rs.getString("zxks_mc"));
                break;
            }

            // 关闭资源
            rs.close();
            statement.close();
            dbcon.close();
        } catch (SQLException e) {
            e.printStackTrace();
            log.info("THIS4q驱动连接失败");
        }

        return map;
    }

    @Override
    public Long createItem(ItemCreateReqVO createReqVO) {
        // 插入
        ItemDO item = ItemConvert.INSTANCE.convert(createReqVO);
        itemMapper.insert(item);
        // 返回
        return item.getId();
    }

    @Override
    public void updateItem(ItemUpdateReqVO updateReqVO) {
        // 校验存在
        this.validateItemExists(updateReqVO.getId());
        // 更新
        ItemDO updateObj = ItemConvert.INSTANCE.convert(updateReqVO);
        itemMapper.updateById(updateObj);
    }
    @Override
    public void updateItemPriceByNo(ItemDO itemDO){
        itemMapper.updateItemPriceByNo(itemDO);
    }

    @Override
    public void updateItemByChild(ItemDO itemDO){
        itemMapper.updateItemByChild(itemDO);
    }

    @Override
    public List<Map<String, Object>> getItemPrice(String itemnos){
        String sql = "select * from HIS_RJKDXMCX  where HIS代码  in (" + itemnos + ") ";
        log.info("打印sql" + sql);
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("itemno", "387");
        map.put("itemname", "静脉采血(检验科)");
        map.put("itemprice", "200");
        list.add(map);
        Map<String, Object> map1 = new HashMap<String, Object>();
        map1.put("itemno", "310300007A");
        map1.put("itemname", "验光");
        map1.put("itemprice", "300");
        list.add(map1);

        return list;

//        //体检项目价格视图 HIS_RJKDXMCX
////        SELECT * FROM [dbo].[HIS_RJKDXMCX]
////        where [HIS代码]='387'
////        select * from HIS_RJKDXMCX
////        where [HIS代码] IN ('387','485')
//            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;
//            // 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 inHosnum = "811134";
//                String sql = "select * from HIS_RJKDXMCX  where HIS代码  in (" + itemnos + ") ";
//                log.info("打印sql" + sql);
//                Statement statement = conn.createStatement();
//                ResultSet rs = statement.executeQuery(sql);
//                log.info("返回结果是：" + rs);
//                // 4.处理数据库的返回结果(使用ResultSet类)
//                while (rs.next()) {
//                    log.info("开始处理数据");
//                    Map<String, Object> map = new HashMap<String, Object>();
////                log.info("打印itemType" + rs.getString("ItemType"));
//                    map.put("itemno", rs.getString("HIS代码"));
//                    map.put("itemname", rs.getString("项目名称"));
//                    map.put("itemprice", rs.getString("单价"));
//                    list.add(map);
//                }
//                log.info("list is " + list);
//                // 关闭资源【多谢指正】
//                rs.close();
//                statement.close();
//                conn.close();
//            } catch (ClassNotFoundException e) {
//                e.printStackTrace();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//            return list;
    }

    @Override
    public void deleteItem(Long id) {
        // 校验存在
        this.validateItemExists(id);
        // 删除
        itemMapper.deleteById(id);
    }

    private void validateItemExists(Long id) {
        if (itemMapper.selectById(id) == null) {
            throw exception(ITEM_NOT_EXISTS);
        }
    }

    @Override
    public ItemDO getItem(Long id) {
        return itemMapper.selectById(id);
    }

    @Override
    public List<ItemDO> getItemList(Collection<Long> ids) {
        return itemMapper.selectBatchIds(ids);
    }

    @Override
    public List<ItemDO> getSimpleItemList(String patid, String packageno, String batchno) {
     //   return itemMapper.selectSimpleList(itemListReqVO);
            return itemMapper.selectItemStandardListByOrder(patid, packageno, batchno);
    }

    @Override
    public PageResult<ItemDO> getItemPage(ItemPageReqVO pageReqVO) {
        return itemMapper.selectPage(pageReqVO);
    }

    @Override
    public List<ItemDO> getItemList(ItemExportReqVO exportReqVO) {
        return itemMapper.selectList(exportReqVO);
    }

}
