package com.jjt.utils;

import com.jjt.common.utils.StringUtils;
import com.jjt.order.domain.MesStock;
import com.jjt.order.domain.VmsStock;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author wukai
 */
@Service
public class MssqlService {
    /**
     * SQLServer本地驱动的地址
     */
    @Value("${mssql.driver}")
    private String dbDriver;
    /**
     * 连接sqlserver数据库；本地连接；通过1433号端口号连接（根据你自身数据库的端口号来）,数据库名称DBMS
     * 你需要现在Sql Server客户端上建立一个数据库
     */
    @Value("${mssql.url}")
    private String dbUrl;
    /**
     * 用户名
     */
    @Value("${mssql.user}")
    private String user = "MES";
    /**
     * 密码
     */
    @Value("${mssql.pass}")
    private String pass = "MES";

    /**
     * 白柸布库存
     *
     * @return 结果
     */
    public List<VmsStock> vmsStock() {
        List<VmsStock> stocks = new ArrayList<>();
        try {
            //第一步：加载数据库驱动程序，此时不需要实例化，因为会由容器自己负责
            Class.forName(dbDriver);
            String sql = "select SKU,SKU_DESCRC,COUNT(1) NUM,SUM(QTY) QTY from V_WMS_STOCK where lot24 is not NULL and WAREHOUSE_ID='WH04-白坯仓库' and UOM='KG' group by sku,SKU_DESCRC order by sku";
            try (Connection conn = DriverManager.getConnection(dbUrl, user, pass); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(sql)) {
                //第二步：根据连接协议、用户名、密码连接数据库
                //第三步：进行数据库的数据操作
                //取得Statement对象
                //使用ResultSet接收数据
                while (rs.next()) {
                    String sku = rs.getString("SKU");
                    String desc = rs.getString("SKU_DESCRC");
                    Integer num = rs.getInt("NUM");
                    Float qty = rs.getFloat("QTY");
                    //将中文括号替换为英文括号
                    desc = desc.replace("（", "(").replace("）", ")");

                    VmsStock stock = new VmsStock();
                    stock.setSku(sku);
                    stock.setDesc(desc);
                    stock.setNum(num);
                    stock.setQty(qty);

                    // 使用正则表达式来提取数字部分
//                    String pattern1 = "\\d+(\\.\\d+)?\\*\\d+(\\.\\d+)?";
//                    String pattern2 = "L(\\d+)";
                    // 定义正则表达式模式
                    String patternString = "([A-Za-z]+-[A-Za-z])(\\d+)\\(([0-9.]+\\*[0-9.]+)\\)";
                    Pattern pattern = Pattern.compile(patternString);
                    String weight = "";
                    String specification = "";

                    Matcher matcher = pattern.matcher(desc.replace(" ", ""));
                    if (matcher.find()) {
                        // 提取重量部分，即第二组匹配的数字
                        weight = matcher.group(2);
                        stock.setWeight(Integer.parseInt(weight));
                        // 提取规格部分，即第三组匹配的表达式
                        specification = matcher.group(3);
                        String[] tmp = specification.split("\\*");
                        if (tmp.length > 1) {
                            stock.setLength(Float.parseFloat(tmp[0]));
                            stock.setMick(Integer.parseInt(tmp[1]));
                            stock.setGsm();
                        }
                    }
                    stocks.add(stock);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stocks;
    }

    /**
     * mes当前库存
     *
     * @param localDate 当前时间
     * @return 结果
     */
    public List<MesStock> mesStock(LocalDate localDate) {
        List<MesStock> list = new ArrayList<>();
        LocalDate start = localDate.minusMonths(1);
        String outView = "SELECT [仓库编码] AS CODE,[仓库名称] AS NAME,[物料类型] AS CLASS,CAST(DATEADD( HOUR,- 7,时间 ) AS DATE) AS TIME,[当日出库量] AS TOTAL,TRY_CAST(单托_支数_米数 AS DECIMAL) AS NUM,[单重_KG] WEIGHT,[单据类型] TYPE FROM V_MES_FAYUN WHERE DATEADD(HOUR,- 7,时间)>='" + start + "'";
        String inView = "SELECT [仓库编码] AS CODE,[仓库名称] AS NAME,[物料类型] AS CLASS,CAST(DATEADD( HOUR,- 7,时间 ) AS DATE) AS TIME,[当日入库量] AS TOTAL,TRY_CAST(单托_支数_米数 AS DECIMAL) AS NUM,[单重_KG] WEIGHT,[单据类型] TYPE FROM V_MES_SHANGJIA WHERE DATEADD(HOUR,- 7,时间)>='" + start + "'";
        String[] sqls = new String[2];
        //入库查询
        sqls[0] = "SELECT CODE,NAME,CLASS,TYPE,TIME,SUM(TOTAL) AS TOTAL FROM (" + inView + ") T GROUP BY CODE,NAME,CLASS,TYPE,TIME";
        //出库查询
        sqls[1] = "SELECT CODE,NAME,CLASS,TYPE,TIME,SUM(TOTAL) AS TOTAL FROM (" + outView + ") T GROUP BY CODE,NAME,CLASS,TYPE,TIME";
        try {
            //第一步：加载数据库驱动程序，此时不需要实例化，因为会由容器自己负责
            Class.forName(dbDriver);
            try (Connection conn = DriverManager.getConnection(dbUrl, user, pass); Statement statement = conn.createStatement()) {
                //第二步：根据连接协议、用户名、密码连接数据库
                //第三步：进行数据库的数据操作
                //取得Statement对象
                //当日出库
                for (int i = 0; i < sqls.length; i++) {
                    try (ResultSet rs = statement.executeQuery(sqls[i])) {
                        while (rs.next()) {
                            String code = rs.getString("CODE");
                            String name = rs.getString("NAME");
                            String clas = rs.getString("CLASS");
                            String type = rs.getString("TYPE");
                            String date = rs.getString("TIME");
                            Long total = rs.getLong("TOTAL");

                            MesStock mesStock = new MesStock();
                            mesStock.setCode(code);
                            mesStock.setName(name);
                            mesStock.setClas(clas);
                            mesStock.setType(type);
                            mesStock.setDate(LocalDate.parse(date));
                            mesStock.setTotal(total);
                            mesStock.setFlag(i == 0);
                            list.add(mesStock);
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询投放到产线的投放量
     *
     * @param localDate 当前时间
     * @return 结果
     */
    public List<MesStock> wmsOutTarget(LocalDate localDate) {
        List<MesStock> list = new ArrayList<>();
        String sql = "SELECT [仓库编码] AS CODE,[仓库名称] AS NAME,[物料类型] AS CLASS,CAST(DATEADD(HOUR,- 7,时间 ) AS DATE) AS TIME,[当日出库量] AS TOTAL,TRY_CAST(单托_支数_米数 AS DECIMAL) AS NUM,[单重_KG] WEIGHT,[单据类型] TYPE,[产线_目标库位名称] TARGET FROM V_MES_FAYUN" +
                " where [仓库编码]='WH04' and convert(date,DATEADD(HOUR,- 7,时间 ))=convert(date,'" + localDate + "') and [产线_目标库位名称] like '%上料点'";
        try {
            //第一步：加载数据库驱动程序，此时不需要实例化，因为会由容器自己负责
            Class.forName(dbDriver);
            try (Connection conn = DriverManager.getConnection(dbUrl, user, pass); Statement statement = conn.createStatement()) {
                //第二步：根据连接协议、用户名、密码连接数据库
                //第三步：进行数据库的数据操作
                //取得Statement对象
                //当日出库
                try (ResultSet rs = statement.executeQuery(sql)) {
                    while (rs.next()) {
                        String code = rs.getString("CODE");
                        String name = rs.getString("NAME");
                        String clas = rs.getString("CLASS");
                        String type = rs.getString("TYPE");
                        String date = rs.getString("TIME");
                        String target = rs.getString("TARGET");
                        Long total = rs.getLong("NUM");

                        MesStock mesStock = new MesStock();
                        mesStock.setCode(code);
                        mesStock.setName(name);
                        mesStock.setClas(clas);
                        mesStock.setType(type);
                        mesStock.setTarget(target);
                        mesStock.setDate(LocalDate.parse(date));
                        mesStock.setTotal(total);
                        list.add(mesStock);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询当前库存
     *
     * @return 结果
     */
    public Map<String, Double> currStock() {
        Map<String, Double> result = new HashMap<>();
        Map<String, String> mapping = new HashMap<>();
        mapping.put("WH01-原料仓库", "ycl");
        mapping.put("WH03-盘头仓库", "pt");
        mapping.put("WH04-白坯仓库", "bpb");
        mapping.put("WH08-成辅仓库", "cp");
        try {
            //第一步：加载数据库驱动程序，此时不需要实例化，因为会由容器自己负责
            Class.forName(dbDriver);
            try (Connection conn = DriverManager.getConnection(dbUrl, user, pass); Statement statement = conn.createStatement()) {
                //第二步：根据连接协议、用户名、密码连接数据库
                //第三步：进行数据库的数据操作
                //取得Statement对象
                //当日出库
                String sql = "SELECT WAREHOUSE_ID AS CODE,SUM(QTY) AS TOTAL FROM V_WMS_STOCK GROUP BY WAREHOUSE_ID";
                try (ResultSet rs = statement.executeQuery(sql)) {
                    while (rs.next()) {
                        String code = rs.getString("CODE");
                        Double total = rs.getDouble("TOTAL");
                        String key = mapping.get(code);
                        if (StringUtils.isNotEmpty(key)) {
                            result.put(key, total);
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 执行查询
     *
     * @param sql SQL
     * @return 结果
     */
    public List<Map<String, Object>> executeQuery(String sql) {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            Class.forName(dbDriver);
            try (Connection conn = DriverManager.getConnection(dbUrl, user, pass);
                 Statement statement = conn.createStatement();
                 ResultSet rs = statement.executeQuery(sql)) {

                // 获取结果集的元数据
                java.sql.ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(columnName, value);
                    }
                    result.add(row);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args) throws Exception {
    }
}
