md_YOS/src/custom/restcontroller/webmanage/sale/invbal/invbal.java

package restcontroller.webmanage.sale.invbal;

import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;

import beans.itemclass.ItemClass;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;

import beans.invbal.Invbal;

import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;
import utility.ERPDocking;

@API(title = "库存管理")
public class invbal extends Controller {

    public invbal(JSONObject content) throws YosException {
        super(content);
        // TODO Auto-generated constructor stub
    }

    public static HashMap<String, Long> itemhashmap = new HashMap<>(16);
    public static HashMap<Long, String> itemnomap = new HashMap<>(16);


    @API(title = "从erp新增更新库存", apiversion = R.ID20230222203603.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230222203803.class})
    public String caculate() throws YosException {
        JSONArray itemids = content.getJSONArray("itemids");
        boolean rightnow = content.getBooleanValue("rightnow");
        JSONArray erpitemnos = new JSONArray();
        if (itemnomap.isEmpty()) {
            Rows rows = dbConnect
                    .runSqlQuery("select itemid,erpitemno from plm_itemextend where siteid='" + siteid + "'");
            for (Row row : rows) {
                itemnomap.put(row.getLong("itemid"), row.getString("erpitemno"));
            }
        }

        for (Object object : itemids) {
            long itemid = ((Integer) object).longValue();
            if (itemnomap.containsKey(itemid)) {
                erpitemnos.add(itemnomap.get(itemid));
            } else {
                Rows rows = dbConnect.runSqlQuery("select erpitemno from plm_itemextend where itemid='" + itemid
                        + "' and siteid='" + siteid + "'");
                if (!rows.isEmpty()) {
                    erpitemnos.add(rows.get(0).getString("erpitemno"));
                    itemnomap.put(itemid, rows.get(0).getString("erpitemno"));
                }
            }
        }
        Invbal.caculate_erpitemno(this, erpitemnos, rightnow);
        return getSucReturnObject().toString();
    }

    @API(title = "从erp新增更新库存(站点全部)", apiversion = R.ID20230222203703.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230222203803.class})
    public String caculateAll() throws YosException {
        if (itemnomap.isEmpty()) {
            Rows rows = dbConnect
                    .runSqlQuery("select itemid,erpitemno from plm_itemextend where siteid='" + siteid + "'");
            for (Row row : rows) {
                itemnomap.put(row.getLong("itemid"), row.getString("erpitemno"));
            }
        }
        Invbal.caculate_erpitemno(this);
        return getSucReturnObject().toString();
    }


    @API(title = "查询库存列表", apiversion = R.ID20230222203803.v1.class)
    public String queryInvbalList() throws YosException {
        boolean istotal = content.getBooleanValue("istotal");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("ismodule") && !"".equals(whereObject.getString("ismodule"))) {
                where.append(" and(");
                where.append("t2.ismodule ='").append(whereObject.getString("ismodule")).append("' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory;
        QuerySQL querySQL;
        if (istotal) {
//            sqlFactory = new SQLFactory(this, "销售库存列表查询", pageSize, pageNumber, pageSorting);
            querySQL = SQLFactory.createQuerySQL(this, "st_invbal_sale", "*");
            querySQL.setTableAlias("t1");
            querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid AND t1.siteid = t2.siteid","itemno","itemname","model");
            querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t3", "t3.unitid = t2.unitid AND t3.siteid = t2.siteid","unitname");
            querySQL.setSiteid(siteid);
            querySQL.setWhere(where.toString());

        } else {

//            sqlFactory = new SQLFactory(this, "即时库存列表查询", pageSize, pageNumber, pageSorting);
            querySQL = SQLFactory.createQuerySQL(this, "st_invbal", "*");
            querySQL.setTableAlias("t1");
            querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid AND t1.siteid = t2.siteid","itemno","itemname","model");
            querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t3", "t1.stockid = t3.stockid AND t1.siteid = t3.siteid","stockno","stockname");
            querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t4.unitid = t2.unitid AND t4.siteid = t2.siteid","unitname");
            querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t5", "t2.stockno = t5.stockno AND t2.siteid = t5.siteid");
            querySQL.setSiteid(siteid);
            querySQL.addQueryFields("defaultstock","t5.stockname");
            querySQL.setWhere(where.toString());
        }

//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        if(!istotal){
            ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
            // 营销类别
            RowsMap itemClassRowsMap = ItemClass.getAllItemClassRowsMap(this, ids);
            for (Row row : rows) {
                row.put("itemclass", itemClassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            }
        }

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询Erp库存列表", apiversion = R.ID20230408091703.v1.class)
    public String queryErpInvbalList() throws YosException, IOException {
//        boolean isExport = content.getBooleanValue("isExport");
        //String iteminfo = content.getStringValue("iteminfo");
        //String agentinfo = content.getStringValue("agentinfo");
        //boolean all = content.getBooleanValue("all");
        //JSONArray itemclassids = content.getJSONArray("itemclassids");
        String where = " 1=1 ";
//        SQLFactory sqlFactory = new SQLFactory(this, "商品列表", pageSize, pageNumber, pageSorting);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemno", "itemid",
                "itemname","isonsale","packageqty","spec","model","standards");
        querySQL.setTableAlias("t2");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t2.unitid=t4.unitid and t2.siteid=t4.siteid","unitname");
        querySQL.addQueryFields("canbesent","0");
        querySQL.addQueryFields("canbesale","0");
        querySQL.addQueryFields("canbesale","0");
        querySQL.addQueryFields("invbalqty","0");
        querySQL.addQueryFields("undelqty","0");
        querySQL.addQueryFields("itemclass","''");

//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "商品列表");
//        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("iteminfo")) {
                if (!StringUtils.isBlank(whereObject.getStringValue("iteminfo"))) {
                    where = where + " and (t2.itemno like '%" + whereObject.getStringValue("iteminfo") + "%' or t2.itemname like '%" + whereObject.getStringValue("iteminfo") + "%') ";
                }
            }
            if (whereObject.containsKey("itemclassids")) {
                if (whereObject.getLongValue("itemclassids") != 0) {
                    ArrayList<Long> itemclassList = new ArrayList<Long>();
                    //System.out.println(row.getLong("itemclassid"));
                    long itemclassid = whereObject.getLong("itemclassids");
                    itemclassList.add(itemclassid);
                    itemclassList.addAll(ItemClass.getSubItemClassIds(this, itemclassid));
                    String sql = " and t2.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + itemclassList + "  and siteid='" + siteid + "')";
                    sql = sql.replace("[", "(").replace("]", ")");
                    where = where + sql;
                }
            }

            if (whereObject.containsKey("all") && !"".equals(whereObject.getString("all"))) {
                if (!whereObject.getBooleanValue("all")) {
                    where = where + " and 1=2 ";
                }
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where = where + " and t2.isonsale ='" + whereObject.getString("isonsale") + "' ";
            }
            if (whereObject.containsKey("agentinfo")) {
                if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
//                    sqlFactory = new SQLFactory(this, "订单明细未发商品列表", pageSize, pageNumber, pageSorting);

                    querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "itemno", "itemid",
                            "itemname","isonsale","packageqty","spec","model","standards");
                    querySQL.setTableAlias("t2");
                    querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.siteid = t2.siteid and t1.itemid = t2.itemid");
                    querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t3", "t1.siteid=t3.siteid and t1.sa_orderid=t3.sa_orderid");
                    querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t2.unitid=t4.unitid and t2.siteid=t4.siteid","unitname");
                    querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid = t3.sys_enterpriseid and t5.siteid = t3.siteid");
                    querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid=t3.sys_enterpriseid and t6.siteid = t3.siteid");

                    querySQL.addQueryFields("canbesent","0");
                    querySQL.addQueryFields("canbesale","0");
                    querySQL.addQueryFields("canbesale","0");
                    querySQL.addQueryFields("invbalqty","0");
                    querySQL.addQueryFields("undelqty","0");
                    querySQL.addQueryFields("itemclass","''");
                    querySQL.setWhere(" t3.STATUS in ('审核') and ifnull(t1.undeliqty, 0) !=0 AND ifnull( t1.isclose, 0 )= 0");

//                    if (isExport) {
//                        sqlFactory = new SQLFactory(this, "订单明细未发商品列表");
//                    }
                    //System.out.println(StringUtils.isBlank(itemname) && StringUtils.isBlank(itemno) && itemclassids.size()==0);
                    if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
                        where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
                    }
                }
            }

        }

//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        //System.out.println(sqlFactory.getSQL());
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.addGroupBy("t2.itemno,\n" +
                "                            t2.itemid,\n" +
                "                            t2.itemname,\n" +
                "                            t2.isonsale,\n" +
                "                            t4.unitname,\n" +
                "                            t2.packageqty");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        Rows rowsitemclass = dbConnect.runSqlQuery(" select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 LEFT JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='" + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");

        RowsMap rowsMap = rows.toRowsMap("itemno");


        SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总");
        sqlFactory1.addParameter("siteid", siteid);
        where = " 1=1 ";
        sqlFactory1.addParameter_SQL("where", where);
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumAllQtyRowsMap = sumQtyRows.toRowsMap("itemno");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
                //where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
            }
        }

        sqlFactory1.addParameter_SQL("where", where);
        sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno");

        SQLFactory sqlFactory2 = new SQLFactory(this, "商品未发货数量汇总");
        sqlFactory2.addParameter("siteid", siteid);
        where = " 1=1 ";
        sqlFactory2.addParameter_SQL("where", where);
        Rows sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnAllQtyRowsMap = sumUnQtyRows.toRowsMap("itemno");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
                where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
            }
        }

        sqlFactory2.addParameter_SQL("where", where);
        sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnQtyRowsMap = sumUnQtyRows.toRowsMap("itemno");


        ERPDocking erpDocking = new ERPDocking(siteid);
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
//            if (Parameter.getBoolean("system.ccerp_dockswitch")) {
//                if (rows.toJsonArray("itemno").size() <= 2000) {
//                    jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno"));
//                } else {
//                    jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, new JSONArray());
//                }
//
//            }
        }

        if (!jsonArray.isEmpty()) {

            for (Object object : jsonArray) {
                JSONObject jsonObject = (JSONObject) object;
                if (rowsMap.containsKey(jsonObject.getString("fitemno"))) {
                    if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) {
                        rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimal("fqty"));
                    }
                }
            }
        }
        for (Row row : rows) {
            String itemclass = itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().substring(1, itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().length() - 1);
            row.put("itemclass", itemclass.replaceAll("\"", ""));
            if (sumQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("unsoldqty", sumQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("unsoldqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumUnQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("undelqty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty").stripTrailingZeros().toPlainString());
                row.put("qty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("undelqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
                row.put("qty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumUnAllQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("undelqtysum", sumUnAllQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty").stripTrailingZeros().toPlainString());
            } else {
                row.put("undelqtysum", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumAllQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("unsoldqtysum", sumAllQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("unsoldqtysum", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }

            if (!row.containsKey("invbalqty")) {
                row.put("invbalqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }

        }
        for (Row row : rows) {

            row.put("canbesent", (row.getBigDecimal("invbalqty").subtract(row.getBigDecimal("unsoldqty"))).stripTrailingZeros().toPlainString());
            row.put("canbesale", ((row.getBigDecimal("invbalqty").subtract(row.getBigDecimal("unsoldqty"))).subtract(row.getBigDecimal("undelqtysum"))).stripTrailingZeros().toPlainString());
//            if (row.getString("itemno").equals("10901371")) {
//                System.out.println(row.getString("canbesent"));
//                System.out.println(row.getString("canbesale"));
//                System.out.println(row.toJsonObject().toJSONString());
//            }
        }
//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("itemid");
//            rows.getFieldList().remove("packageqty");
//            rows.getFieldList().remove("qty");
////            for (Row row : rows) {
////                if (row.getString("itemno").equals("10901371")) {
////                    System.out.println(row.getString("canbesent"));
////                    System.out.println(row.getString("canbesale"));
////                    System.out.println(row.toJsonObject().toJSONString());
////                }
////
////            }
//            Rows uploadRows = uploadExcelToObs("invbal", "库存列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }
        return getSucReturnObject().setData(rows).toString();
    }

    //返回导出的标题
//    public HashMap<String, String> getTitleMap() {
//        HashMap<String, String> titleMap = new HashMap<>();
//        titleMap.put("itemname", "产品名称");
//        titleMap.put("itemno", "产品编号");
//        titleMap.put("undelqty", "未发数量");
//        titleMap.put("invbalqty", "库存数");
//        titleMap.put("canbesent", "预计可发量");
//        titleMap.put("canbesale", "预计可售量");
//        titleMap.put("unitname", "单位");
//        titleMap.put("itemclass", "营销分类");
//        titleMap.put("isonsale", "是否上架");
//        return titleMap;
//    }

    @API(title = "查询指定商品对应的未发货订单明细", apiversion = R.ID20230408101803.v1.class)
    public String queryOrderDetailList() throws YosException {
        String itemno = content.getString("itemno");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t5.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "未发货订单明细列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("itemno", itemno);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "qty", "undeliqty",
                "isfreeze");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.siteid = t2.siteid and t1.itemid = t2.itemid","itemno","itemname");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t3", "t1.siteid=t3.siteid and t1.sa_orderid=t3.sa_orderid","sonum","sa_orderid","checkdate","submitdate","remarks");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t2.unitid=t4.unitid and t2.siteid=t4.siteid","unitname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t3.sys_enterpriseid=t5.sys_enterpriseid and t3.siteid=t5.siteid","enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid=t3.sys_enterpriseid and t3.siteid=t6.siteid","agentnum");
        querySQL.addQueryFields("detailremarks","t1.remarks");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t3.STATUS in ('审核') and ifnull(t1.undeliqty, 0) !=0 and ifnull( t1.isclose, 0 )= 0");
        querySQL.setWhere("t2.itemno",itemno);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "商品补货分析", apiversion = R.ID20231228102402.v1.class)
    public String queryItemSalesList() throws YosException, IOException {
//        boolean isExport = content.getBooleanValue("isExport");
        int year = content.getIntValue("year");
        if (year <= 0) {
            Calendar calendar = Calendar.getInstance();
            year = calendar.get(Calendar.YEAR);
        }

//        String begindate = year+"-01-01";
//        String enddate = (year+1)+"-01-01";

        String where = " 1=1 ";
//        SQLFactory sqlFactory = new SQLFactory(this, "商品列表", pageSize, pageNumber, pageSorting);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemno", "itemid",
                "itemname","isonsale","packageqty","spec","model","standards");
        querySQL.setTableAlias("t2");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t2.unitid=t4.unitid and t2.siteid=t4.siteid","unitname");
        querySQL.addQueryFields("canbesent","0");
        querySQL.addQueryFields("canbesale","0");
        querySQL.addQueryFields("canbesale","0");
        querySQL.addQueryFields("invbalqty","0");
        querySQL.addQueryFields("undelqty","0");
        querySQL.addQueryFields("itemclass","''");
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "商品列表");
//        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("iteminfo")) {
                if (!StringUtils.isBlank(whereObject.getStringValue("iteminfo"))) {
                    where = where + " and (t2.itemno like '%" + whereObject.getStringValue("iteminfo")
                            + "%' or t2.itemname like '%" + whereObject.getStringValue("iteminfo")
                            + "%' or t2.standards like '%" + whereObject.getStringValue("iteminfo") + "%') ";
                }
            }
            if (whereObject.containsKey("itemclassids")) {
                if (whereObject.getLong("itemclassids") != 0) {
                    ArrayList<Long> itemclassList = new ArrayList<Long>();
                    long itemclassid = whereObject.getLong("itemclassids");
                    itemclassList.add(itemclassid);
                    itemclassList.addAll(ItemClass.getSubItemClassIds(this, itemclassid));
                    String sql = " and t2.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + itemclassList + "  and siteid='" + siteid + "')";
                    sql = sql.replace("[", "(").replace("]", ")");
                    where = where + sql;
                }
            }

            if (whereObject.containsKey("all") && !"".equals(whereObject.getString("all"))) {
                if (!whereObject.getBooleanValue("all")) {
                    where = where + " and 1=2 ";
                }
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where = where + " and t2.isonsale ='" + whereObject.getString("isonsale") + "' ";
            }
            if (whereObject.containsKey("agentinfo")) {
                if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
//                    sqlFactory = new SQLFactory(this, "订单明细未发商品列表", pageSize, pageNumber, pageSorting);
                    querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "itemno", "itemid",
                            "itemname","isonsale","packageqty","spec","model","standards");
                    querySQL.setTableAlias("t2");
                    querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.siteid = t2.siteid and t1.itemid = t2.itemid");
                    querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t3", "t1.siteid=t3.siteid and t1.sa_orderid=t3.sa_orderid");
                    querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t2.unitid=t4.unitid and t2.siteid=t4.siteid","unitname");
                    querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid = t3.sys_enterpriseid and t5.siteid = t3.siteid");
                    querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid=t3.sys_enterpriseid and t6.siteid = t3.siteid");

                    querySQL.addQueryFields("canbesent","0");
                    querySQL.addQueryFields("canbesale","0");
                    querySQL.addQueryFields("canbesale","0");
                    querySQL.addQueryFields("invbalqty","0");
                    querySQL.addQueryFields("undelqty","0");
                    querySQL.addQueryFields("itemclass","''");
                    querySQL.setWhere(" t3.STATUS in ('审核') and ifnull(t1.undeliqty, 0) !=0 AND ifnull( t1.isclose, 0 )= 0");
//                    if (isExport) {
//                        sqlFactory = new SQLFactory(this, "订单明细未发商品列表");
//                    }
                    //System.out.println(StringUtils.isBlank(itemname) && StringUtils.isBlank(itemno) && itemclassids.size()==0);
                    if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
                        where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
                    }
                }
            }

        }

        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.addGroupBy("t2.itemno,\n" +
                "                            t2.itemid,\n" +
                "                            t2.itemname,\n" +
                "                            t2.isonsale,\n" +
                "                            t4.unitname,\n" +
                "                            t2.packageqty");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        Rows rowsitemclass = dbConnect.runSqlQuery(" select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 LEFT JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='" + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");

        RowsMap rowsMap = rows.toRowsMap("itemno");


        SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总");
        sqlFactory1.addParameter("siteid", siteid);
        where = " 1=1 ";
        sqlFactory1.addParameter_SQL("where", where);
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumAllQtyRowsMap = sumQtyRows.toRowsMap("itemno");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
//                where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
            }
        }

        sqlFactory1.addParameter_SQL("where", where);
        sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno");

        SQLFactory sqlFactory2 = new SQLFactory(this, "商品未发货数量汇总");
        sqlFactory2.addParameter("siteid", siteid);
        where = " 1=1 ";
        sqlFactory2.addParameter_SQL("where", where);
        Rows sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnAllQtyRowsMap = sumUnQtyRows.toRowsMap("itemno");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (!StringUtils.isBlank(whereObject.getStringValue("agentinfo"))) {
                where = where + " and (t6.agentnum like '%" + whereObject.getStringValue("agentinfo") + "%' or t5.enterprisename like '%" + whereObject.getStringValue("agentinfo") + "%') ";
            }
        }

        sqlFactory2.addParameter_SQL("where", where);
        sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnQtyRowsMap = sumUnQtyRows.toRowsMap("itemno");


        ERPDocking erpDocking = new ERPDocking(siteid);
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
//            if (Parameter.getBoolean("system.ccerp_dockswitch")) {
//                if (rows.toJsonArray("itemno").size() <= 2000) {
//                    jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno"));
//                } else {
//                    jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, new JSONArray());
//                }
//
//            }
        }

        if (!jsonArray.isEmpty()) {

            for (Object object : jsonArray) {
                JSONObject jsonObject = (JSONObject) object;
                if (rowsMap.containsKey(jsonObject.getString("fitemno"))) {
                    if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) {
                        rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimal("fqty"));
                    }
                }
            }
        }
        for (Row row : rows) {
            String itemclass = itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().substring(1, itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().length() - 1);
            row.put("itemclass", itemclass.replaceAll("\"", ""));
            if (sumQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("unsoldqty", sumQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("unsoldqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumUnQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("undelqty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty").stripTrailingZeros().toPlainString());
                row.put("qty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("undelqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
                row.put("qty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumUnAllQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("undelqtysum", sumUnAllQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty").stripTrailingZeros().toPlainString());
            } else {
                row.put("undelqtysum", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }
            if (sumAllQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("unsoldqtysum", sumAllQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty").stripTrailingZeros().toPlainString());
            } else {
                row.put("unsoldqtysum", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }

            if (!row.containsKey("invbalqty")) {
                row.put("invbalqty", BigDecimal.ZERO.stripTrailingZeros().toPlainString());
            }

        }


        //查询统计商品月销量销
        SQLFactory sqlFactory3 = new SQLFactory(this, "查询统计商品月销量销");
        sqlFactory3.addParameter("siteid", siteid);
        sqlFactory3.addParameter_in("itemid", rows.toArray("itemid"));
        Rows rows3 = dbConnect.runSqlQuery(sqlFactory3);
        RowsMap monthRowsMap = rows3.toRowsMap("itemid");

        SQLFactory sqlFactory4 = new SQLFactory(this, "查询退货统计");
        sqlFactory4.addParameter("siteid", siteid);
        sqlFactory4.addParameter_in("itemid", rows.toArray("itemid"));
        Rows rows4 = dbConnect.runSqlQuery(sqlFactory4);
        RowsMap returnRowsMap = rows4.toRowsMap("itemid");


        SQLFactory sqlFactory5 = new SQLFactory(this, "查询手工关闭统计");
        sqlFactory5.addParameter("siteid", siteid);
        sqlFactory5.addParameter_in("itemid", rows.toArray("itemid"));
        Rows rows5 = dbConnect.runSqlQuery(sqlFactory4);
        RowsMap closeRowsMap = rows5.toRowsMap("itemid");


        for (Row row : rows) {

            row.put("canbesent", (row.getBigDecimal("invbalqty").subtract(row.getBigDecimal("unsoldqty"))).stripTrailingZeros().toPlainString());
            row.put("canbesale", ((row.getBigDecimal("invbalqty").subtract(row.getBigDecimal("unsoldqty"))).subtract(row.getBigDecimal("undelqtysum"))).stripTrailingZeros().toPlainString());
            Rows monthRows = monthRowsMap.getOrDefault(row.getString("itemid"), new Rows());
            for (Row month : monthRows) {
                row.put(month.getString("month"), month.getBigDecimal("qty"));
            }
            //补足月份
            row = initMonthRow(row);
            //处理退货
            Rows returnRows = returnRowsMap.getOrDefault(row.getString("itemid"), new Rows());
            for (Row returnRow : returnRows) {
                String key = returnRow.getString("month");
                row.replace(key, row.getBigDecimal(key).subtract(returnRow.getBigDecimal("qty")));
            }
            //
            Rows closeRows = closeRowsMap.getOrDefault(row.getString("itemid"), new Rows());
            for (Row closeRow : closeRows) {
                String key = closeRow.getString("month");
                row.replace(key, row.getBigDecimal(key).subtract(closeRow.getBigDecimal("qty")));
            }
        }
        for (Row row : rows) {
            for (int i = 0; i < 12; i++) {
                if (i < 9) {
                    row.replace("month_0" + (i + 1), row.get(getLast12Months().get(i)));
                } else {
                    row.replace("month_" + (i + 1), row.get(getLast12Months().get(i)));
                }
            }
        }
//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("itemid");
//            rows.getFieldList().remove("packageqty");
//            rows.getFieldList().remove("qty");
//            Rows uploadRows = uploadExcelToObs("invbal", "商品补货分析", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }
        return getSucReturnObject().setData(rows).toString();
    }

    public Row initMonthRow(Row row) {
        for (String key : getLast12Months()) {
            row.putIfAbsent(key, 0);
        }

        //初始化
        row.putIfAbsent("month_01", 0);
        row.putIfAbsent("month_02", 0);
        row.putIfAbsent("month_03", 0);
        row.putIfAbsent("month_04", 0);
        row.putIfAbsent("month_05", 0);
        row.putIfAbsent("month_06", 0);
        row.putIfAbsent("month_07", 0);
        row.putIfAbsent("month_08", 0);
        row.putIfAbsent("month_09", 0);
        row.putIfAbsent("month_10", 0);
        row.putIfAbsent("month_11", 0);
        row.putIfAbsent("month_12", 0);

        return row;
    }

    public List<String> getLast12Months() {
        // 获取当前日期
        LocalDate currentDate = LocalDate.now();

        // 创建一个存储日期的列表
        List<String> last12Months = new ArrayList<>();

        // 获取最近12个月的日期
        for (int i = 0; i < 12; i++) {
            // 使用DateTimeFormatter将日期格式化为字符串
            String formattedDate = currentDate.format(DateTimeFormatter.ofPattern("yyyy-MM"));
            last12Months.add(formattedDate);

            // 将当前日期减去一个月
            currentDate = currentDate.minusMonths(1);
        }
        return last12Months;
    }

}