package com.awspaas.user.apps.bnbm.datalinkup.service.impl;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.server.UserContext;
import com.actionsoft.bpms.util.DBSql;
import com.actionsoft.sdk.local.SDK;
import com.awspaas.user.apps.bnbm.datalinkup.entity.DateRange;
import com.awspaas.user.apps.bnbm.datalinkup.entity.Location;
import com.awspaas.user.apps.bnbm.datalinkup.service.DataSummaryService;
import com.awspaas.user.apps.bnbm.datalinkup.util.SaleUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 销售汇总维度
 */
public class SaleCountDimensionImpl implements DataSummaryService {
    private static final Logger LOGGER = LoggerFactory.getLogger(SaleCountDimensionImpl.class);
    // 维度表名常量
    /**
     * 销售_营业收入_年月
     */
    private static final String BO_EU_XS_YESR = "BO_EU_XS_YESR";
    /**
     * 销售_销量销额_年月
     */
    private static final String BO_EU_XS_XLXE = "BO_EU_XS_XLXE";
    /**
     * 销售_应收账款_年月
     */
    private static final String BO_EU_XS_YSZK = "BO_EU_XS_YSZK";
    /**
     * 销售_区域两金占比_年月
     */
    private static final String BO_EU_XS_QYLJZB = "BO_EU_XS_QYLJZB";

    // 原始数据表名
    /**
     * 销售_销售类_汇总表
     */
    private static final String SALES_DETAIL_TABLE = "BO_EU_BNBM_DATALINKUP_XS_XSL_HZ";
    /**
     * 销售_应收类_汇总
     */
    private static final String RECEIVABLE_DETAIL_TABLE = "BO_EU_BNBM_DATALINKUP_XS_YSL";
    /**
     * 采购_库存_汇总
     */
    private static final String BO_EU_DWD_ORDER_KC_HZ = "BO_EU_DWD_ORDER_KC_HZ";

    // 日期格式化
    private static final SimpleDateFormat YEAR_MONTH_FORMAT = new SimpleDateFormat("yyyy-MM");
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

    // 批量处理大小
    private static final int BATCH_SIZE = 1000;
    // 批量分页大小
    private static final int PAGE_SIZE = 1000;

    @Override
    public void calculateSummary(DateRange dateRange, BO mainConfig) {
        try {
            // 从主配置获取BKGS值
            String bkgs = mainConfig.getString("BKGS");
            if (bkgs == null || bkgs.isEmpty()) {
                LOGGER.error("主配置中BKGS为空，无法进行汇总计算");
                return;
            }
            if (dateRange == null || dateRange.getStartDate() == null || dateRange.getEndDate() == null) {
                LOGGER.info("未提供有效时间范围，按当前日期计算");
                Calendar cal = Calendar.getInstance();
//                DateRange dateRangeNow = new DateRange();

                // 设置为当前月的第一天
                cal.set(Calendar.DAY_OF_MONTH, 1);
                dateRange.setStartDate(cal.getTime());

                // 设置为当前月的最后一天
                cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
                dateRange.setEndDate(cal.getTime());
//                calculateForCurrentDate(bkgs);
//                calculateMonthlyData(dateRange, bkgs);
                // 处理营业收入数据
                newProcessRevenueData(dateRange,bkgs);
                // 处理销量销额数据（分页查询）
                newProcessSalesVolumeData(dateRange,bkgs);
                // 区域两金占比
                newProcessRegionTwoFundsRatio(dateRange,bkgs);

                LOGGER.info("{}销售数据多维度汇总计算完成",bkgs);
            } else {
                LOGGER.info("开始执行销售数据多维度汇总计算（时间范围: {} 至 {})",
                        dateRange.getStartDate(), dateRange.getEndDate());

                // 计算月度维度数据（按月遍历）
                calculateMonthlyData(dateRange, bkgs);
                // 处理营业收入数据
                newProcessRevenueData(dateRange,bkgs);
                // 处理销量销额数据（分页查询）
                newProcessSalesVolumeData(dateRange,bkgs);
                // 区域两金占比
                newProcessRegionTwoFundsRatio(dateRange,bkgs);

                LOGGER.info("{}销售数据多维度汇总计算完成",bkgs);
            }
        } catch (Exception e) {
            String errorMsg = "销售数据汇总计算失败: " + e.getMessage();
            LOGGER.error(errorMsg, e);
            throw new RuntimeException(errorMsg, e);
        }
    }

    /**
     * 区域两金占比
     * @param dateRange 时间范围
     * @param bkgs 时间范围
     */
    private void newProcessRegionTwoFundsRatio(DateRange dateRange, String bkgs) {
        LOGGER.info("开始处理区域两金占比，年月: {}-{}, 板块公司: {}", dateRange.getStartDate(),dateRange.getEndDate(), bkgs);

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_QYLJZB + " WHERE YEARMONTH >= ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(deleteSql, new Object[]{YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs});
            LOGGER.info("区域两金占比数据-已删除{}-{}条营业收入记录", YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), deleted);
        }catch (Exception e){
            LOGGER.error("区域两金占比-删除数据{}年月-{}板块数据错误，删除sql为：{}，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,deleteSql,e.getMessage());
            throw e;
        }

        try {
            // 第一个SQL：查询应收账款数据
            String receivableSql = "SELECT t1.XSZZ AS XSZZ, t1.QYGS AS QYGS, t1.BKGS AS BKGS," +
                    " DATE_FORMAT( t1.RQ, '%Y-%m' ) ASyear_month, t1.RQ AS max_rq, SUM( t1.YSYE ) AS YSZK," +
                    " SUM(t1.LJHK) AS LJHK,SUM(t1.LJXS) AS LJXS," +
                    " CASE " +
                    "        WHEN SUM(t1.LJXS) != 0 THEN SUM(t1.LJHK) / SUM(t1.LJXS)" +
                    "        ELSE NULL " +
                    "    END AS hkl" +
                    " FROM BO_EU_BNBM_DATALINKUP_XS_YSL t1" +
                    " INNER JOIN (" +
                    " SELECT QYGS, BKGS, DATE_FORMAT( RQ, '%Y-%m' ) ASyear_month, MAX( RQ ) AS max_rq" +
                    " FROM BO_EU_BNBM_DATALINKUP_XS_YSL" +
                    " WHERE RQ >= ? AND RQ < ? AND BKGS = ?" +
                    " GROUP BY QYGS,BKGS,DATE_FORMAT ( RQ, '%Y-%m' )" +
                    " ) t2 ON t1.QYGS = t2.QYGS AND t1.BKGS = t2.BKGS AND t1.RQ = t2.max_rq" +
                    " WHERE t1.RQ >= ? AND t1.RQ < ? AND t1.BKGS = ?" +
                    " GROUP BY t1.QYGS,t1.BKGS,DATE_FORMAT ( t1.RQ, '%Y-%m' ),t1.RQ";


            String startDate = DATE_FORMAT.format(dateRange.getStartDate());
            String endDate = DATE_FORMAT.format(dateRange.getEndDate());

            LOGGER.info("应收账款数据查SQL: {}", receivableSql);
            List<RowMap> receivableMaps = DBSql.getMaps(receivableSql, startDate, endDate, bkgs,startDate, endDate, bkgs);

            if (receivableMaps.isEmpty()) {
                LOGGER.info("{}应收账款数据无数据，停止分页查询", bkgs);
            } else {
                ArrayList<BO> bos = new ArrayList<>();

                // 收集所有销售组织用于库存查询
                List<String> xszzList = new ArrayList<>();
                Map<String, RowMap> receivableMap = new HashMap<>();

                for (RowMap map : receivableMaps) {
                    String qygs = map.getString("QYGS");
                    List<RowMap> maps = DBSql.getMaps("SELECT * FROM BO_EU_BNBM_QYGSDYB WHERE QYGS = '" + qygs + "'");
                    for (RowMap rowMap : maps) {
                        String xszz = rowMap.getString("XSZZ");
                        xszzList.add(xszz);
                        receivableMap.put(xszz, map);
                    }
                }

                // 合并数据并创建BO对象
                for (RowMap receivable : receivableMaps) {
                    String xszz = receivable.getString("XSZZ");
                    double yszk = receivable.getDouble("YSZK");
                    String year_month = receivable.getString("ASyear_month");
                    String[] split = year_month.split("-");
                    double kcje = 0.0;
                    // 第二个SQL：查询库存金额数据
                    if (!xszzList.isEmpty()) {
                        String placeholders = String.join(",", Collections.nCopies(xszzList.size(), "?"));
                        String inClause = xszzList.stream()
                                .map(s -> "'" + s.replace("'", "''") + "'") // 转义单引号防止SQL注入
                                .collect(Collectors.joining(","));
                        String inventorySql = "SELECT STOCKORGNAME, SUM(BALANCE_AMOUNT) as KCJE " +
                                " FROM " + BO_EU_DWD_ORDER_KC_HZ + " " +
                                " WHERE STOCKORGNAME IN (" + inClause + ") " +
                                " AND CATEGORY = '产成品' " +
                                " AND YEAR(INDATE) = YEAR('" + split[0] + "') " +
                                " AND MONTH(INDATE) = MONTH('" + split[1] + "') " +
                                " GROUP BY STOCKORGNAME";

                        LOGGER.info("库存金额数据查询，SQL: {}", inventorySql);

                        List<RowMap> inventoryMaps = DBSql.getMaps(inventorySql);
                        Map<String, Double> inventoryMap = inventoryMaps.stream()
                                .collect(Collectors.toMap(
                                        row -> row.getString("STOCKORGNAME"),
                                        row -> row.getDouble("KCJE"),
                                        (existing, replacement) -> existing
                                ));
                        kcje = inventoryMap.getOrDefault(xszz, 0.0);
                    }

                    BO bo = new BO();
                    bo.set("YEARMONTH", year_month);
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", receivable.getString("QYGS"));
                    bo.set("YSZK", yszk);
                    bo.set("CCJE", kcje);
                    bo.set("LJHK", receivable.getString("LJHK"));
                    bo.set("LJXS", receivable.getString("LJXS"));
                    bo.set("HKL", receivable.getString("HKL"));
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    int[] admins = SDK.getBOAPI().createDataBO(BO_EU_XS_QYLJZB, bos, UserContext.fromUID("admin"));
                    LOGGER.info("{}：{}-{}范围区域两金占比数据处理完成，查询{}条记录，累计{}条",
                            bkgs, dateRange.getStartDate(), dateRange.getEndDate(), bos.size(), admins[0]);
                }
            }
        }catch (Exception e){
            LOGGER.error("两金占比-新增数据{}年月-{}板块数据错误，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,e.getMessage());
            throw e;
        }

    }

    /**
     * 处理销量销额数据
     * @param dateRange 时间范围
     * @param bkgs 板块公司
     */
    private void newProcessSalesVolumeData(DateRange dateRange, String bkgs) {
        LOGGER.info("开始处理销量销额数据，年月: {}-{}, 板块公司: {}", dateRange.getStartDate(),dateRange.getEndDate(), bkgs);

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_XLXE + " WHERE YEARMONTH >= ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(deleteSql, new Object[]{YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs});
            LOGGER.info("销量销额数据-已删除{}-{}条营业收入记录", YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), deleted);
        }catch (Exception e){
            LOGGER.error("销量销额-删除数据{}年月-{}板块数据错误，删除sql为：{}，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,deleteSql,e.getMessage());
            throw e;
        }

        try {
            // 获取公司位置信息
            List<RowMap> companyList = DBSql.getMaps("SELECT GSMC,JD,WD FROM BO_EU_BNBM_DATALINKUP_GSJWD");
            Map<String, Location> resultMap = companyList.stream()
                    .filter(row -> row.get("GSMC") != null)
                    .collect(Collectors.toMap(
                            row -> row.get("GSMC").toString(),
                            row -> new Location(
                                    row.get("JD") != null ? row.get("JD").toString() : null,
                                    row.get("WD") != null ? row.get("WD").toString() : null
                            ),
                            (existing, replacement) -> existing
                    ));

            String querySql = "SELECT QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, SUM(ZSSL) AS XL," +
                    " SUM(SSJERMB) AS XE, SUM(XSSL) AS TSXL, NMNY ,YEARMONTH" +
                    " FROM " + SALES_DETAIL_TABLE +
                    " WHERE DZRQ >= ? AND DZRQ < ? AND BKGS = ?" +
                    " GROUP BY QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, YEARMONTH";

            LOGGER.info("销量销额数据查询，SQL: {}", querySql);
            List<RowMap> maps = DBSql.getMaps(querySql, DATE_FORMAT.format(dateRange.getStartDate()),
                    DATE_FORMAT.format(dateRange.getEndDate()), bkgs);

            if (maps.isEmpty()) {
                LOGGER.info("销量销额数据无数据，停止查询");
            } else {
                ArrayList<BO> bos = new ArrayList<>();
                // 循环外先获取下公装家装、基材面材类别
                List<BO> gzjzList = SDK.getBOAPI().query("BO_EU_DATALINKUP_CPJZGZJCMC").list();
                for (RowMap map : maps) {
                    String gc = map.getString("KCZZ");
                    String yearmonth = map.getString("YEARMONTH");
                    BO bo = new BO();
                    bo.set("YEARMONTH", yearmonth.substring(0, 4) + "-" + yearmonth.substring(4));
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", map.getString("QYGS"));
                    bo.set("GC", gc);
                    bo.set("LB_1", map.getString("LB_1"));
                    bo.set("LB_2", map.getString("LB_2"));
                    bo.set("LB_3", map.getString("LB_3"));
                    bo.set("SQ", map.getString("SQ"));
                    bo.set("CITY", map.getString("CS"));
                    bo.set("QX", map.getString("QY"));
                    if (resultMap.containsKey(gc)) {
                        Location location = resultMap.get(gc);
                        bo.set("JD", location.getLongitude());
                        bo.set("WD", location.getLatitude());
                    }
                    if ("石膏板".equals(map.getString("LB_1"))){
                        bo.set("XL", map.getDouble("TSXL"));
                    }else {
                        bo.set("XL", map.getDouble("XL"));
                    }
                    Double ssjermb = map.getDouble("SSJERMB");
                    Double xe = map.getDouble("XE");
                    bo.set("XE", ssjermb!=null?ssjermb:xe!=null?(xe*0.13):0.00);
                    // 匹配公装家装类别，如果未匹配到，则默认未其他
                    String[] gzjzStrings = SaleUtil.matchSingleField(bkgs, map.getString("LB_1"), map.getString("LB_2"), gzjzList, "其他", "其他");
                    bo.set("JZ_GZ", gzjzStrings[0]);
                    bo.set("MC_JC", gzjzStrings[1]);
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    int[] admins = SDK.getBOAPI().createDataBO(BO_EU_XS_XLXE, bos, UserContext.fromUID("admin"));
                    LOGGER.info("{}-销量销额数据处理完成，共查询除{}条记录，导入{}条", bkgs, bos.size(), admins[0]);
                }
            }
        }catch (Exception e){
            LOGGER.error("销量销额-新增数据{}年月-{}板块数据错误，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,e.getMessage());
            throw e;
        }
    }

    /**
     * 处理营业收入数据
     * @param dateRange 时间范围
     * @param bkgs 板块公司
     */
    private void newProcessRevenueData(DateRange dateRange, String bkgs) {
        LOGGER.info("开始处理营业收入数据，年月: {}-{}, 板块公司: {}", dateRange.getStartDate(),dateRange.getEndDate(), bkgs);

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_YESR + " WHERE YEARMONTH >= ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(deleteSql, new Object[]{YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs});
            LOGGER.info("营业收入数据-已删除{}-{}条营业收入记录", YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), deleted);
        }catch (Exception e){
            LOGGER.error("营业收入-删除数据{}年月-{}板块数据错误，删除sql为：{}，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,deleteSql,e.getMessage());
            throw e;
        }

        try {
            // 获取公司位置信息
            List<RowMap> companyList = DBSql.getMaps("SELECT GSMC,JD,WD FROM BO_EU_BNBM_DATALINKUP_GSJWD");
            Map<String, Location> resultMap = companyList.stream()
                    .filter(row -> row.get("GSMC") != null)
                    .collect(Collectors.toMap(
                            row -> row.get("GSMC").toString(),
                            row -> new Location(
                                    row.get("JD") != null ? row.get("JD").toString() : null,
                                    row.get("WD") != null ? row.get("WD").toString() : null
                            ),
                            (existing, replacement) -> existing
                    ));

            String querySql = "SELECT QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, SUM(NMNY) as YYSR, YEARMONTH" +
                    " FROM " + SALES_DETAIL_TABLE +
                    " WHERE DZRQ >= ? AND DZRQ < ? AND BKGS = ?" +
                    " GROUP BY QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, YEARMONTH";

            LOGGER.info("营业收入数据查询，SQL: {}", querySql);
            List<RowMap> maps = DBSql.getMaps(querySql, DATE_FORMAT.format(dateRange.getStartDate()),
                    DATE_FORMAT.format(dateRange.getEndDate()), bkgs);

            if (maps.isEmpty()) {
                LOGGER.info("营业收入数据无数据，停止查询");
            } else {
                ArrayList<BO> bos = new ArrayList<>();
                for (RowMap map : maps) {
                    String gc = map.getString("KCZZ");
                    String yearmonth = map.getString("YEARMONTH");
                    BO bo = new BO();
                    bo.set("YEARMONTH", yearmonth.substring(0, 4) + "-" + yearmonth.substring(4));
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", map.getString("QYGS"));
                    bo.set("GC", gc);
                    bo.set("LB_1", map.getString("LB_1"));
                    bo.set("LB_2", map.getString("LB_2"));
                    bo.set("LB_3", map.getString("LB_3"));
                    bo.set("SQ", map.getString("SQ"));
                    bo.set("CITY", map.getString("CS"));
                    bo.set("QX", map.getString("QY"));
                    if (resultMap.containsKey(gc)) {
                        Location location = resultMap.get(gc);
                        bo.set("JD", location.getLongitude());
                        bo.set("WD", location.getLatitude());
                    }
                    bo.set("YYSR", map.getDouble("YYSR"));
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    int[] admins = SDK.getBOAPI().createDataBO(BO_EU_XS_YESR, bos, UserContext.fromUID("admin"));
                    LOGGER.info("{}-营业收入数据处理完成，共查询{}条记录，导入{}条", bkgs, bos.size(), admins[0]);
                }
            }
        }catch (Exception e){
            LOGGER.error("营业收入-新增数据{}年月-{}板块数据错误，请检查数据库链接：{}",
                    YEAR_MONTH_FORMAT.format(dateRange.getStartDate()), bkgs,e.getMessage());
            throw e;
        }
    }

    /**
     * 按当前日期计算（无时间范围时使用）
     */
    private void calculateForCurrentDate(String bkgs) {
        Calendar cal = Calendar.getInstance();
        DateRange dateRange = new DateRange();

        // 设置为当前月的第一天
        cal.set(Calendar.DAY_OF_MONTH, 1);
        dateRange.setStartDate(cal.getTime());

        // 设置为当前月的最后一天
        cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
        dateRange.setEndDate(cal.getTime());



    }

    /**
     * 计算月度维度数据
     */
    private void calculateMonthlyData(DateRange dateRange, String bkgs) {
        Connection conn = null;
        try {
            conn = DBSql.open();
//            conn.setAutoCommit(false);
            // 获取时间范围内的所有月份
            List<String> yearMonths = getYearMonthsBetweenDates(dateRange.getStartDate(), dateRange.getEndDate());

            for (String yearMonth : yearMonths) {
                LOGGER.info("开始处理{}月份的数据，板块公司: {}", yearMonth, bkgs);
                // 3. 处理应收账款数据
                LOGGER.info("开始应收账款数据");
                processReceivableData(conn, yearMonth, bkgs);
            }
//            conn.commit();
            LOGGER.info("所有月份数据处理完成");
        }catch (Exception e) {
            try {
                if (conn != null) conn.rollback();
            } catch (Exception rollbackEx) {
                LOGGER.error("回滚事务失败", rollbackEx);
            }
            String errorMsg = "月度数据处理失败: " + e.getMessage();
            LOGGER.error(errorMsg, e);
            throw new RuntimeException(errorMsg, e);
        } finally {
            DBSql.close(conn);
        }
    }

    /**
     * 处理营业收入数据
     */
    private void processRevenueData(Connection conn, String yearMonth, String bkgs) throws Exception {
        LOGGER.info("开始处理营业收入数据，年月: {}, 板块公司: {}", yearMonth, bkgs);
        int totalCount = 0;

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_YESR + " WHERE YEARMONTH = ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(conn, deleteSql, new Object[]{yearMonth, bkgs});
            LOGGER.info("营业收入数据-已删除{}条营业收入记录", deleted);
        }catch (Exception e){
            LOGGER.error("营业收入-删除数据{}数据错误，删除sql为：{}，请检查数据库链接：{}",bkgs,deleteSql,e.getMessage());
            throw e;
        }

        // 获取公司位置信息
        List<RowMap> companyList = DBSql.getMaps("SELECT GSMC,JD,WD FROM BO_EU_BNBM_DATALINKUP_GSJWD");
        Map<String, Location> resultMap = companyList.stream()
                .filter(row -> row.get("GSMC") != null)
                .collect(Collectors.toMap(
                        row -> row.get("GSMC").toString(),
                        row -> new Location(
                                row.get("JD") != null ? row.get("JD").toString() : null,
                                row.get("WD") != null ? row.get("WD").toString() : null
                        ),
                        (existing, replacement) -> existing
                ));

        // 分页查询营业收入数据
        int page = 0;
        boolean hasMore = true;
        while (hasMore) {
            int offset = page * PAGE_SIZE;
            String querySql = "SELECT QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, " +
                    "SUM(SSJERMB) as YYSR " +
                    "FROM " + SALES_DETAIL_TABLE + " " +
                    "WHERE YEARMONTH = ? AND BKGS = ? " +
                    "GROUP BY QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY " +
                    "LIMIT " + PAGE_SIZE + " OFFSET " + offset;

            LOGGER.info("营业收入数据查询第{}页，SQL: {}", page + 1, querySql);
            List<RowMap> maps = DBSql.getMaps(conn, querySql, yearMonth.replace("-", ""), bkgs);

            if (maps.isEmpty()) {
                hasMore = false;
                LOGGER.info("营业收入数据第{}页无数据，停止分页查询", page + 1);
            } else {
                ArrayList<BO> bos = new ArrayList<>();
                for (RowMap map : maps) {
                    String gc = map.getString("KCZZ");
                    BO bo = new BO();
                    bo.set("YEARMONTH", yearMonth);
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", map.getString("QYGS"));
                    bo.set("GC", gc);
                    bo.set("LB_1", map.getString("LB_1"));
                    bo.set("LB_2", map.getString("LB_2"));
                    bo.set("LB_3", map.getString("LB_3"));
                    bo.set("SQ", map.getString("SQ"));
                    bo.set("CITY", map.getString("CS"));
                    bo.set("QX", map.getString("QY"));
                    if (resultMap.containsKey(gc)) {
                        Location location = resultMap.get(gc);
                        bo.set("JD", location.getLongitude());
                        bo.set("WD", location.getLatitude());
                    }
                    bo.set("YYSR", map.getDouble("YYSR"));
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    for (int i = 0; i < bos.size(); i += BATCH_SIZE) {
                        int end = Math.min(bos.size(), i + BATCH_SIZE);
                        List<BO> batchList = bos.subList(i, end);
                        SDK.getBOAPI().createDataBO(BO_EU_XS_YESR, batchList, UserContext.fromUID("admin"));
                    }
                    totalCount += bos.size();
                    LOGGER.info("营业收入数据第{}页处理完成，本页{}条记录，累计{}条", page + 1, bos.size(), totalCount);
                }
                page++;
            }
        }
        LOGGER.info("营业收入数据处理完成，共处理{}条记录", totalCount);
    }

    /**
     * 处理销量销额数据（分页查询）
     */
    private void processSalesVolumeData(Connection conn, String yearMonth, String bkgs) throws Exception {
        LOGGER.info("开始处理销量销额数据，年月: {}, 板块公司: {}", yearMonth, bkgs);
        int totalCount = 0;

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_XLXE + " WHERE YEARMONTH = ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(conn, deleteSql, new Object[]{yearMonth, bkgs});
            LOGGER.info("销量销额-已删除{}条记录", deleted);
        } catch (Exception e) {
            LOGGER.error("销量销额-删除{}数据错误，删除sql为：{}，请检查数据库链接：{}", bkgs, deleteSql, e.getMessage());
            throw e;
        }

        // 获取公司位置信息
        List<RowMap> companyList = DBSql.getMaps("SELECT GSMC,JD,WD FROM BO_EU_BNBM_DATALINKUP_GSJWD");
        Map<String, Location> resultMap = companyList.stream()
                .filter(row -> row.get("GSMC") != null)
                .collect(Collectors.toMap(
                        row -> row.get("GSMC").toString(),
                        row -> new Location(
                                row.get("JD") != null ? row.get("JD").toString() : null,
                                row.get("WD") != null ? row.get("WD").toString() : null
                        ),
                        (existing, replacement) -> existing
                ));

        // 分页查询销量销额数据
        int page = 0;
        boolean hasMore = true;
        while (hasMore) {
            int offset = page * PAGE_SIZE;
            String querySql = "SELECT QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY, " +
                    "SUM(ZSSL) as XL, SUM(SSJERMB) as XE ,SUM(XSSL) AS TSXL " +
                    "FROM " + SALES_DETAIL_TABLE + " " +
                    "WHERE YEARMONTH = ? AND BKGS = ? " +
                    "GROUP BY QYGS, KCZZ, LB_1, LB_2, LB_3, SQ, CS, QY " +
                    "LIMIT " + PAGE_SIZE + " OFFSET " + offset;

            LOGGER.info("销量销额数据查询第{}页，SQL: {}", page + 1, querySql);
            List<RowMap> maps = DBSql.getMaps(conn, querySql, yearMonth.replace("-", ""), bkgs);

            if (maps.isEmpty()) {
                hasMore = false;
                LOGGER.info("销量销额数据第{}页无数据，停止分页查询", page + 1);
            } else {
                ArrayList<BO> bos = new ArrayList<>();
                for (RowMap map : maps) {
                    String gc = map.getString("KCZZ");
                    BO bo = new BO();
                    bo.set("YEARMONTH", yearMonth);
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", map.getString("QYGS"));
                    bo.set("GC", gc);
                    bo.set("LB_1", map.getString("LB_1"));
                    bo.set("LB_2", map.getString("LB_2"));
                    bo.set("LB_3", map.getString("LB_3"));
                    bo.set("SQ", map.getString("SQ"));
                    bo.set("CITY", map.getString("CS"));
                    bo.set("QX", map.getString("QY"));
                    if (resultMap.containsKey(gc)) {
                        Location location = resultMap.get(gc);
                        bo.set("JD", location.getLongitude());
                        bo.set("WD", location.getLatitude());
                    }
                    if ("石膏板".equals(map.getString("LB_1"))){
                        bo.set("XL", map.getDouble("TSXL"));
                    }else {
                        bo.set("XL", map.getDouble("XL"));
                    }
                    bo.set("XE", map.getDouble("XE"));
                    bo.set("MC_JC", Math.random()%2==0?"面材":"基材");
                    bo.set("JZ_GZ", Math.random()%2==0?"家装":"工装");
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    for (int i = 0; i < bos.size(); i += BATCH_SIZE) {
                        int end = Math.min(bos.size(), i + BATCH_SIZE);
                        List<BO> batchList = bos.subList(i, end);
                        SDK.getBOAPI().createDataBO(BO_EU_XS_XLXE, batchList, UserContext.fromUID("admin"));
                    }
                    totalCount += bos.size();
                    LOGGER.info("销量销额数据第{}页处理完成，本页{}条记录，累计{}条", page + 1, bos.size(), totalCount);
                }
                page++;
            }
        }
        LOGGER.info("销量销额数据处理完成，共处理{}条记录", totalCount);
    }

    /**
     * 处理应收账款数据（分页查询）
     */
    private void processReceivableData(Connection conn, String yearMonth, String bkgs) throws Exception {
        LOGGER.info("开始处理应收账款数据，年月: {}, 板块公司: {}", yearMonth, bkgs);
        int totalCount = 0;

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_YSZK + " WHERE YEARMONTH = ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(conn, deleteSql, new Object[]{yearMonth, bkgs});
            LOGGER.info("应收账款-已删除{}条记录", deleted);
        } catch (Exception e) {
            LOGGER.error("应收账款-删除{}数据错误，删除sql为：{}，请检查数据库链接：{}", bkgs, deleteSql, e.getMessage());
            throw e;
        }

        // 获取指定年月最后一天
        Calendar cal = Calendar.getInstance();
        cal.setTime(YEAR_MONTH_FORMAT.parse(yearMonth));
        cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
        String lastDayOfMonth = DATE_FORMAT.format(cal.getTime());

        // 获取公司位置信息
        List<RowMap> companyList = DBSql.getMaps("SELECT GSMC,JD,WD FROM BO_EU_BNBM_DATALINKUP_GSJWD");
        Map<String, Location> resultMap = companyList.stream()
                .filter(row -> row.get("GSMC") != null)
                .collect(Collectors.toMap(
                        row -> row.get("GSMC").toString(),
                        row -> new Location(
                                row.get("JD") != null ? row.get("JD").toString() : null,
                                row.get("WD") != null ? row.get("WD").toString() : null
                        ),
                        (existing, replacement) -> existing
                ));

        // 分页查询应收账款数据
        int page = 0;
        boolean hasMore = true;
        while (hasMore) {
            int offset = page * PAGE_SIZE;
            String querySql = "SELECT QYGS, XSZZ, SHENGQU, SHIQU, QX, QCYE, LJXS, LJHK, YSYE, " +
                    "ZLFX0_60, ZLFX60_1, ZLFX1_2, ZLFX2_3, ZLFX3_4, ZLFX4_5, ZLFX5 " +
                    "FROM " + RECEIVABLE_DETAIL_TABLE + " " +
                    "WHERE DATE(RQ) = ? AND BKGS = ? " +
                    "LIMIT " + PAGE_SIZE + " OFFSET " + offset;

            LOGGER.info("应收账款数据查询第{}页，SQL: {}", page + 1, querySql);
            List<RowMap> maps = DBSql.getMaps(conn, querySql, lastDayOfMonth, bkgs);

            if (maps.isEmpty()) {
                hasMore = false;
                LOGGER.info("应收账款数据第{}页无数据，停止分页查询", page + 1);
            } else {
                ArrayList<BO> bos = new ArrayList<>();
                for (RowMap map : maps) {
                    String xszz = map.getString("XSZZ");
                    BO bo = new BO();
                    bo.set("YEARMONTH", yearMonth);
                    bo.set("BKGS", bkgs);
                    bo.set("QYGS", map.getString("QYGS"));
                    bo.set("GC", xszz);
                    bo.set("SQ", map.getString("SHENGQU"));
                    bo.set("CITY", map.getString("SHIQU"));
                    bo.set("QX", map.getString("QX"));
                    if (resultMap.containsKey(xszz)) {
                        Location location = resultMap.get(xszz);
                        bo.set("JD", location.getLongitude());
                        bo.set("WD", location.getLatitude());
                    }
                    bo.set("QCYE", map.getDouble("QCYE"));
                    bo.set("NFH", map.getDouble("LJXS"));
                    bo.set("NHK", map.getDouble("LJHK"));
                    bo.set("YSZE", map.getDouble("YSYE"));
                    bo.set("ZL60", map.getDouble("ZLFX0_60"));
                    bo.set("ZL60_1", map.getDouble("ZLFX60_1"));
                    bo.set("ZL1_2", map.getDouble("ZLFX1_2"));
                    bo.set("ZL2_3", map.getDouble("ZLFX2_3"));
                    bo.set("ZL3_4", map.getDouble("ZLFX3_4"));
                    bo.set("ZL4_5", map.getDouble("ZLFX4_5"));
                    bo.set("ZL5", map.getDouble("ZLFX5"));
                    bos.add(bo);
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    for (int i = 0; i < bos.size(); i += BATCH_SIZE) {
                        int end = Math.min(bos.size(), i + BATCH_SIZE);
                        List<BO> batchList = bos.subList(i, end);
                        SDK.getBOAPI().createDataBO(BO_EU_XS_YSZK, batchList, UserContext.fromUID("admin"));
                    }
                    totalCount += bos.size();
                    LOGGER.info("应收账款数据第{}页处理完成，本页{}条记录，累计{}条", page + 1, bos.size(), totalCount);
                }
                page++;
            }
        }
        LOGGER.info("应收账款数据处理完成，共处理{}条记录", totalCount);
    }

    /**
     * 处理区域两金占比数据（分页查询）
     */
    private void processRegionTwoFundsRatio(Connection conn, String yearMonth, String bkgs) throws Exception {
        LOGGER.info("开始处理区域两金占比数据，年月: {}, 板块公司: {}", yearMonth, bkgs);
        int totalCount = 0;

        // 删除已存在的记录
        String deleteSql = "DELETE FROM " + BO_EU_XS_QYLJZB + " WHERE YEARMONTH = ? AND BKGS = ?";
        try {
            int deleted = DBSql.update(conn, deleteSql, new Object[]{yearMonth, bkgs});
            LOGGER.info("区域两金占比-已删除{}条记录", deleted);
        } catch (Exception e) {
            LOGGER.error("区域两金占比-删除{}数据错误，删除sql为：{}，请检查数据库链接：{}", bkgs, deleteSql, e.getMessage());
            throw e;
        }

        // 获取指定年月最后一天
        Calendar cal = Calendar.getInstance();
        cal.setTime(YEAR_MONTH_FORMAT.parse(yearMonth));
        cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
        Date lastDayOfMonthDate = cal.getTime();
        String lastDayOfMonth = DATE_FORMAT.format(lastDayOfMonthDate);

        // 获取当前日期
        Calendar todayCal = Calendar.getInstance();
        todayCal.set(Calendar.HOUR_OF_DAY, 0);
        todayCal.set(Calendar.MINUTE, 0);
        todayCal.set(Calendar.SECOND, 0);
        todayCal.set(Calendar.MILLISECOND, 0);
        Date today = todayCal.getTime();

        // 比较日期 否则保持不变
        if (lastDayOfMonthDate.after(today)) {
            // 如果月末日期大于今天，使用今天日期
            lastDayOfMonth = DATE_FORMAT.format(today);
        }


        // 分页查询区域两金占比数据
        int page = 0;
        boolean hasMore = true;
        while (hasMore) {
            int offset = page * PAGE_SIZE;

            // 第一个SQL：查询应收账款数据
            String receivableSql = "SELECT QYGS, BKGS, YSYE as YSZK " +
                    "FROM " + RECEIVABLE_DETAIL_TABLE + " " +
                    "WHERE RQ = ? AND BKGS = ? " +
                    "GROUP BY QYGS, BKGS " +
                    "LIMIT " + PAGE_SIZE + " OFFSET " + offset;

            LOGGER.info("应收账款数据查询第{}页，SQL: {}", page + 1, receivableSql);
            List<RowMap> receivableMaps = DBSql.getMaps(conn, receivableSql, lastDayOfMonth, bkgs);

            if (receivableMaps.isEmpty()) {
                hasMore = false;
                LOGGER.info("应收账款数据第{}页无数据，停止分页查询", page + 1);
            } else {
                ArrayList<BO> bos = new ArrayList<>();

                // 收集所有销售组织用于库存查询
                List<String> xszzList = new ArrayList<>();
                Map<String, RowMap> receivableMap = new HashMap<>();

                for (RowMap map : receivableMaps) {
                    String qygs = map.getString("QYGS");
                    List<RowMap> maps = DBSql.getMaps("SELECT * FROM BO_EU_BNBM_QYGSDYB WHERE QYGS = '" + qygs + "'");
                    for (RowMap rowMap : maps) {
                        String xszz = rowMap.getString("XSZZ");
                        xszzList.add(xszz);
                        receivableMap.put(xszz, map);
                    }
                }

                // 第二个SQL：查询库存金额数据
                if (!xszzList.isEmpty()) {
                    String placeholders = String.join(",", Collections.nCopies(xszzList.size(), "?"));
                    String inClause = xszzList.stream()
                            .map(s -> "'" + s.replace("'", "''") + "'") // 转义单引号防止SQL注入
                            .collect(Collectors.joining(","));

                    String inventorySql = "SELECT STOCKORGNAME, SUM(BALANCE_AMOUNT) as KCJE " +
                            "FROM " + BO_EU_DWD_ORDER_KC_HZ + " " +
                            "WHERE STOCKORGNAME IN (" + inClause + ") " +
                            "AND CATEGORY = '产成品' " +
                            "AND YEAR(INDATE) = YEAR('"+lastDayOfMonth+"') " +
                            "AND MONTH(INDATE) = MONTH('"+lastDayOfMonth+"') " +
                            "GROUP BY STOCKORGNAME";

                    LOGGER.info("库存金额数据查询，SQL: {}", inventorySql);

                    List<RowMap> inventoryMaps = DBSql.getMaps(conn, inventorySql);
                    Map<String, Double> inventoryMap = inventoryMaps.stream()
                            .collect(Collectors.toMap(
                                    row -> row.getString("STOCKORGNAME"),
                                    row -> row.getDouble("KCJE"),
                                    (existing, replacement) -> existing
                            ));

                    // 合并数据并创建BO对象
                    for (RowMap receivable : receivableMaps) {
                        String xszz = receivable.getString("XSZZ");
                        double yszk = receivable.getDouble("YSZK");
                        double kcje = inventoryMap.getOrDefault(xszz, 0.0);

                        BO bo = new BO();
                        bo.set("YEARMONTH", yearMonth);
                        bo.set("BKGS", bkgs);
                        bo.set("QYGS", receivable.getString("QYGS"));
                        bo.set("YSZK", yszk);
                        bo.set("KCJE", kcje);
                        bos.add(bo);
                    }
                }

                // 批量新增BO
                if (!bos.isEmpty()) {
                    for (int i = 0; i < bos.size(); i += BATCH_SIZE) {
                        int end = Math.min(bos.size(), i + BATCH_SIZE);
                        List<BO> batchList = bos.subList(i, end);
                        SDK.getBOAPI().createDataBO(BO_EU_XS_QYLJZB, batchList, UserContext.fromUID("admin"));
                    }
                    totalCount += bos.size();
                    LOGGER.info("区域两金占比数据第{}页处理完成，本页{}条记录，累计{}条",
                            page + 1, bos.size(), totalCount);
                }
                page++;
            }
        }
        LOGGER.info("区域两金占比数据处理完成，共处理{}条记录", totalCount);
    }

    /**
     * 获取两个日期之间的所有年月
     */
    private List<String> getYearMonthsBetweenDates(Date startDate, Date endDate) {
        List<String> yearMonths = new ArrayList<>();
        Calendar startCal = Calendar.getInstance();
        startCal.setTime(startDate);
        startCal.set(Calendar.DAY_OF_MONTH, 1);

        Calendar endCal = Calendar.getInstance();
        endCal.setTime(endDate);
        endCal.set(Calendar.DAY_OF_MONTH, endCal.getActualMaximum(Calendar.DAY_OF_MONTH));

        Calendar currentCal = (Calendar) startCal.clone();

        while (!currentCal.after(endCal)) {
            yearMonths.add(YEAR_MONTH_FORMAT.format(currentCal.getTime()));
            currentCal.add(Calendar.MONTH, 1);
        }

        return yearMonths;
    }
}
