md_YOS/src/custom/restcontroller/webmanage/sale/sendplan/sendplandetail.java

package restcontroller.webmanage.sale.sendplan;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.math.BigDecimal;
import java.util.*;

@API(title = "发货计划明细")
public class sendplandetail extends Controller {
    public sendplandetail(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "订单信息刷新(新)", apiversion = R.ID2025122609083103.v1.class)
    public String orderRefresh_newtest() throws YosException {
        long sa_sendplanid = content.getLongValue("sa_sendplanid");
        Rows sendplanRows = dbConnect.runSqlQuery("select * from sa_sendplan where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        if (sendplanRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("发货计划不存在,无法进行订单信息刷新").toString();
        }
        dbConnect.runSqlUpdate("delete from sa_sendplandetail where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 and t1.undeliqty>0 and t3.type='" + sendplanRows.get(0).getString("type")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='" + sendplanRows.get(0).getString("begindate")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='" + sendplanRows.get(0).getString("enddate")
                + "' and ( \n" +
                "\t\t(t3.type in('标准订单','网销订单')) or\n" +
                "\t\t(\n" +
                "\t\t\tt3.type='配件订单'\n" +
                "\t\t\tand t3.sys_enterpriseid in (\n" +
                "\t\t\tselect distinct sys_enterpriseid from st_stockbill \n" +
                "\t\t\twhere type='销售出库'  and status='审核' and \n" +
                "\t\t\tbilldate= '" + sendplanRows.get(0).getString("senddate") + "'\n" +
                "\t\t\t)\n" +
                "\t\t))");

        SQLFactory sqlFactory = new SQLFactory(this, "发货计划订单未发货量(新)");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        Rows stockbillrows = dbConnect.runSqlQuery("select t2.itemid,sum(t2.undeliqty) qty from sa_order t1 " +
                " inner join sa_orderitems t2 on t1.sa_orderid=t2.sa_orderid " +
                " where t1.status='审核' and t2.isclose=0 and t2.undeliqty>0 and t1.siteid='" + siteid + "' " +
                " group by t2.itemid ");
        Rows invbalSalerows = dbConnect.runSqlQuery("select itemid,qty from st_invbal_sale");

        RowsMap stockbillrowsMap = stockbillrows.toRowsMap("itemid");
        RowsMap invbalSalerowsMap = invbalSalerows.toRowsMap("itemid");

        ArrayList<String> list = new ArrayList<>();

        long oldItemid= 0;
        BigDecimal oldWilloutqty = BigDecimal.ZERO;
        BigDecimal oldCanuseqty = BigDecimal.ZERO;
        BigDecimal oldSendqty = BigDecimal.ZERO;
        long[] sendplandetailids = createTableID("sa_sendplandetail", rows.size());
        int i=0;
        for (Row row :rows) {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_sendplandetail");
            insertSQL.setUniqueid(sendplandetailids[i++]);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("sa_sendplanid", sa_sendplanid);
            insertSQL.setValue("sa_orderid", row.getLong("sa_orderid"));
            insertSQL.setValue("sa_orderitemsid", row.getLong("sa_orderitemsid"));
            insertSQL.setValue("outplace", row.getString("outplace"));
            insertSQL.setValue("sa_agentsid", row.getString("sa_agentsid"));
            BigDecimal willoutqty = BigDecimal.ZERO;
            BigDecimal invbalqty = BigDecimal.ZERO;
            if (stockbillrowsMap.containsKey(row.getString("itemid"))) {
                willoutqty = stockbillrowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            if (invbalSalerowsMap.containsKey(row.getString("itemid"))) {
                // 查询库存总量
                invbalqty = invbalSalerowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            insertSQL.setValue("willinqty", 0);
            insertSQL.setValue("willoutqty", willoutqty);
            insertSQL.setValue("sendqty", getBigDecimalOrZero(row.getBigDecimal("sendqty")));


            long currentitemid = row.getLong("itemid");

            if (oldItemid == 0 || oldItemid!=currentitemid) {
                // 新物料
                oldWilloutqty =willoutqty;

                oldCanuseqty = invbalqty.subtract(oldWilloutqty);
            } else {
                // 相同物料
                oldCanuseqty = oldCanuseqty.subtract(oldSendqty);
                oldWilloutqty = oldWilloutqty.add(oldSendqty);
            }

            // 设置计算后的值
            insertSQL.setValue("canuseqty",oldCanuseqty);
            insertSQL.setValue("willoutqty",oldWilloutqty);


            // 如果可用量<=0，设置发货量为0
            if (oldCanuseqty.compareTo(BigDecimal.ZERO) <= 0) {
                insertSQL.setValue("sendqty",BigDecimal.ZERO);
                insertSQL.setValue("colorflag", 0);
            }else{
                if (oldCanuseqty.compareTo(row.getBigDecimal("sendqty")) >= 0) {
                    insertSQL.setValue("colorflag", 2);
                } else {
                    insertSQL.setValue("colorflag", 3);
                }
            }

            // 保存当前值供下次循环使用
            oldItemid = currentitemid;
            oldSendqty = getBigDecimalOrZero(row.getBigDecimal("sendqty")).compareTo(BigDecimal.ZERO)>0?getBigDecimalOrZero(row.getBigDecimal("sendqty")):BigDecimal.ZERO;
            list.add(insertSQL.getSQL());
        }
        dbConnect.runSqlUpdate(list);
        list = new ArrayList<>();
        Rows rows1 = dbConnect.runSqlQuery("select t1.sa_orderid from (select sa_orderid,count(0)count from sa_sendplandetail where sa_sendplanid=" + sa_sendplanid + " group by sa_orderid) t1 inner join (select sa_orderid,count(0)count from sa_sendplandetail where colorflag='2' and  sa_sendplanid=" + sa_sendplanid + "  group by sa_orderid) t2 on  t1.sa_orderid=t2.sa_orderid and t1.count=t2.count");
        for (Row row : rows1) {
            list.add("update sa_sendplandetail set colorflag=1 where sa_sendplanid=" + sa_sendplanid + " and sa_orderid =" + row.getLong("sa_orderid"));
        }
        return getSucReturnObject().toString();
    }

    //ID2025120110193103
    @API(title = "订单信息刷新(新)", apiversion = R.ID2025120110193103.v1.class)
    public String orderRefresh_new() throws YosException {
        long sa_sendplanid = content.getLongValue("sa_sendplanid");
        Rows sendplanRows = dbConnect.runSqlQuery("select * from sa_sendplan where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        if (sendplanRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("发货计划不存在,无法进行订单信息刷新").toString();
        }
        dbConnect.runSqlUpdate("delete from sa_sendplandetail where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 and t1.undeliqty>0 and t3.type='" + sendplanRows.get(0).getString("type")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='" + sendplanRows.get(0).getString("begindate")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='" + sendplanRows.get(0).getString("enddate")
                + "' and ( \n" +
                "\t\t(t3.type in('标准订单','网销订单')) or\n" +
                "\t\t(\n" +
                "\t\t\tt3.type='配件订单'\n" +
                "\t\t\tand t3.sys_enterpriseid in (\n" +
                "\t\t\tselect distinct sys_enterpriseid from st_stockbill \n" +
                "\t\t\twhere type='销售出库'  and status='审核' and \n" +
                "\t\t\tbilldate= '" + sendplanRows.get(0).getString("senddate") + "'\n" +
                "\t\t\t)\n" +
                "\t\t))");

        SQLFactory sqlFactory = new SQLFactory(this, "发货计划订单未发货量(新)");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        Rows stockbillrows = dbConnect.runSqlQuery("select t2.itemid,sum(t2.undeliqty) qty from sa_order t1 " +
                " inner join sa_orderitems t2 on t1.sa_orderid=t2.sa_orderid " +
                " where t1.status='审核' and t2.isclose=0 and t2.undeliqty>0 and t1.siteid='" + siteid + "' " +
                " group by t2.itemid ");
        Rows invbalSalerows = dbConnect.runSqlQuery("select itemid,qty from st_invbal_sale");

        RowsMap stockbillrowsMap = stockbillrows.toRowsMap("itemid");
        RowsMap invbalSalerowsMap = invbalSalerows.toRowsMap("itemid");

        ArrayList<String> list = new ArrayList<>();
        long olditemid = 0;
        BigDecimal oldcanuseqty = BigDecimal.ZERO;
        BigDecimal oldwilloutqty = BigDecimal.ZERO;
        BigDecimal oldsendqty = BigDecimal.ZERO;
        long[] sendplandetailids = createTableID("sa_sendplandetail", rows.size());
        int i = 0;
        for (Row row : rows) {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_sendplandetail");
            insertSQL.setUniqueid(sendplandetailids[i++]);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("sa_sendplanid", sa_sendplanid);
            insertSQL.setValue("sa_orderid", row.getLong("sa_orderid"));
            insertSQL.setValue("sa_orderitemsid", row.getLong("sa_orderitemsid"));
            insertSQL.setValue("outplace", row.getString("outplace"));
            insertSQL.setValue("sa_agentsid", row.getString("sa_agentsid"));

            BigDecimal canuseqty = BigDecimal.ZERO;
            BigDecimal newcanuseqty = BigDecimal.ZERO;
            BigDecimal willoutqty = BigDecimal.ZERO;
            BigDecimal invbalqty = BigDecimal.ZERO;
            BigDecimal sendqty = BigDecimal.ZERO;
            if (stockbillrowsMap.containsKey(row.getString("itemid"))) {
                willoutqty = stockbillrowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            if (invbalSalerowsMap.containsKey(row.getString("itemid"))) {
                invbalqty = invbalSalerowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            canuseqty = invbalqty.subtract(willoutqty);


            insertSQL.setValue("willinqty", 0);
            insertSQL.setValue("canuseqty", canuseqty);



            if (row.getLong("itemid") == olditemid) {
                insertSQL.setValue("willoutqty", (oldwilloutqty.compareTo(BigDecimal.ZERO) == 0 ? willoutqty : oldwilloutqty).add(oldsendqty));
                insertSQL.setValue("canuseqty", (oldcanuseqty.compareTo(BigDecimal.ZERO) == 0 ? canuseqty : oldcanuseqty).subtract(oldsendqty));
                newcanuseqty=(oldcanuseqty.compareTo(BigDecimal.ZERO) == 0 ? canuseqty : oldcanuseqty).subtract(oldsendqty);
                oldcanuseqty=(oldcanuseqty.compareTo(BigDecimal.ZERO) == 0 ? canuseqty : oldcanuseqty).subtract(oldsendqty);

            } else {
                insertSQL.setValue("willoutqty", willoutqty);
                insertSQL.setValue("canuseqty", canuseqty);
                newcanuseqty=canuseqty;
            }

            if (newcanuseqty.compareTo(BigDecimal.ZERO) <= 0) {
                sendqty=BigDecimal.ZERO;
                insertSQL.setValue("sendqty", sendqty);
                insertSQL.setValue("colorflag", 0);
            } else {
                sendqty = row.getBigDecimal("sendqty");
                insertSQL.setValue("sendqty", sendqty);
                if (newcanuseqty.compareTo(row.getBigDecimal("sendqty")) >= 0) {
                    insertSQL.setValue("colorflag", 2);
                } else {
                    insertSQL.setValue("colorflag", 3);
                }
            }
            if (row.getLong("itemid") != olditemid) {
                oldcanuseqty = BigDecimal.ZERO;
            }
            olditemid = row.getLong("itemid");
            oldsendqty = sendqty;
            list.add(insertSQL.getSQL());
        }
        dbConnect.runSqlUpdate(list);
        list = new ArrayList<>();
        Rows rows1 = dbConnect.runSqlQuery("select t1.sa_orderid from (select sa_orderid,count(0)count from sa_sendplandetail where sa_sendplanid=" + sa_sendplanid + " group by sa_orderid) t1 inner join (select sa_orderid,count(0)count from sa_sendplandetail where colorflag='2' and  sa_sendplanid=" + sa_sendplanid + "  group by sa_orderid) t2 on  t1.sa_orderid=t2.sa_orderid and t1.count=t2.count");
        for (Row row : rows1) {
            list.add("update sa_sendplandetail set colorflag=1 where sa_sendplanid=" + sa_sendplanid + " and sa_orderid =" + row.getLong("sa_orderid"));
        }
        return getSucReturnObject().toString();
    }

    //ID2025032709331803
    @API(title = "订单信息刷新", apiversion = R.ID2025032709331803.v1.class)
    @CACHEING_CLEAN(apiClass = {sendplandetail.class})
    public String orderRefresh() throws YosException {
        long sa_sendplanid = content.getLongValue("sa_sendplanid");
        Rows sendplanRows = dbConnect.runSqlQuery("select * from sa_sendplan where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        if (sendplanRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("发货计划不存在,无法进行订单信息刷新").toString();
        }
        dbConnect.runSqlUpdate("delete from sa_sendplandetail where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 and t1.undeliqty>0 and t3.type='" + sendplanRows.get(0).getString("type")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='" + sendplanRows.get(0).getString("begindate")
                + "' and  DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='" + sendplanRows.get(0).getString("enddate")
                + "' and ( \n" +
                "\t\t(t3.type in('标准订单','网销订单')) or\n" +
                "\t\t(\n" +
                "\t\t\tt3.type='配件订单'\n" +
                "\t\t\tand t3.sys_enterpriseid in (\n" +
                "\t\t\tselect distinct sys_enterpriseid from st_stockbill \n" +
                "\t\t\twhere type='销售出库'  and status='审核' and \n" +
                "\t\t\tbilldate= '" + sendplanRows.get(0).getString("senddate") + "'\n" +
                "\t\t\t)\n" +
                "\t\t))");

        SQLFactory sqlFactory = new SQLFactory(this, "发货计划订单未发货量");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        ArrayList<String> list = new ArrayList<>();
        //计划发货插入明细
        int i = 0;
        long[] sendplandetailids = createTableID("sa_sendplandetail", rows.size());
        for (Row row : rows) {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_sendplandetail");
            insertSQL.setUniqueid(sendplandetailids[i++]);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("sa_sendplanid", sa_sendplanid);
            insertSQL.setValue("sendqty", row.getBigDecimal("sendqty"));
            insertSQL.setValue("sa_orderid", row.getLong("sa_orderid"));
            insertSQL.setValue("sa_orderitemsid", row.getLong("sa_orderitemsid"));
            insertSQL.setValue("outplace", row.getString("outplace"));
            insertSQL.setValue("sa_agentsid", row.getString("sa_agentsid"));
            insertSQL.setValue("canuseqty", 0);

            list.add(insertSQL.getSQL());
        }
        dbConnect.runSqlUpdate(list);

        //计划发货明细计算
        list = new ArrayList<>();
//        Rows stockbillrows = dbConnect.runSqlQuery("\t\tselect itemid,sum(t2.qty) qty from st_stockbill t1\n" +
//                "\t\t\tinner join st_stockbill_items t2 on t1.st_stockbillid=t2.st_stockbillid\n" +
//                "\t\t\twhere t1.type='销售出库'and t1.status='新建' and t1.siteid='" + siteid + "'\n" +
//                "\t\t\tand billdate <='" + sendplanRows.get(0).getString("senddate") + "'\n" +
//                "\t\t\tgroup by itemid");
        Rows stockbillrows = dbConnect.runSqlQuery("select t2.itemid,sum(t2.undeliqty) qty from sa_order t1 " +
                " inner join sa_orderitems t2 on t1.sa_orderid=t2.sa_orderid " +
                " where t1.status='审核' and t2.isclose=0 and t2.undeliqty>0 and t1.siteid='" + siteid + "' " +
                " group by t2.itemid ");
        Rows invbalSalerows = dbConnect.runSqlQuery("select itemid,qty from st_invbal_sale");
        Rows sendplandetails = dbConnect.runSqlQuery("select t1.sa_sendplandetailid,t1.sa_orderid,t2.itemid,t1.sendqty from sa_sendplandetail t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.sa_sendplanid=" + sa_sendplanid + " and t1.siteid='" + siteid + "' order by t2.sa_orderid desc");

        RowsMap stockbillrowsMap = stockbillrows.toRowsMap("itemid");
        RowsMap invbalSalerowsMap = invbalSalerows.toRowsMap("itemid");
        for (Row row : sendplandetails) {
            BigDecimal canuseqty = BigDecimal.ZERO;
            BigDecimal willoutqty = BigDecimal.ZERO;
            BigDecimal invbalqty = BigDecimal.ZERO;
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_sendplandetail");
            updateSQL.setUniqueid(row.getLong("sa_sendplandetailid"));
            if (stockbillrowsMap.containsKey(row.getString("itemid"))) {
                willoutqty = stockbillrowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            if (invbalSalerowsMap.containsKey(row.getString("itemid"))) {
                invbalqty = invbalSalerowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            canuseqty = invbalqty.subtract(willoutqty);
            updateSQL.setValue("willoutqty", willoutqty);
            updateSQL.setValue("willinqty", 0);
            updateSQL.setValue("canuseqty", canuseqty);
            if (canuseqty.compareTo(BigDecimal.ZERO) <= 0) {
                updateSQL.setValue("sendqty", 0);
                updateSQL.setValue("colorflag", 0);
            } else {
                if (canuseqty.compareTo(row.getBigDecimal("sendqty")) >= 0) {
                    updateSQL.setValue("colorflag", 2);
                } else {
                    updateSQL.setValue("colorflag", 3);
                }
            }
            list.add(updateSQL.getSQL());
        }
        dbConnect.runSqlUpdate(list);
        list = new ArrayList<>();
        Rows rows1 = dbConnect.runSqlQuery("select t1.sa_orderid from (select sa_orderid,count(0)count from sa_sendplandetail where sa_sendplanid=" + sa_sendplanid + " group by sa_orderid) t1 inner join (select sa_orderid,count(0)count from sa_sendplandetail where colorflag='2' and  sa_sendplanid=" + sa_sendplanid + "  group by sa_orderid) t2 on  t1.sa_orderid=t2.sa_orderid and t1.count=t2.count");
        for (Row row : rows1) {
            list.add("update sa_sendplandetail set colorflag=1 where sa_sendplanid=" + sa_sendplanid + " and sa_orderid =" + row.getLong("sa_orderid"));
        }

        Rows sendplandetailsgroupitemid = dbConnect.runSqlQuery("select t2.itemid,count(1) count from sa_sendplandetail t1  inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.sa_sendplanid=" + sa_sendplanid + " and t1.siteid='" + siteid + "' group by t2.itemid HAVING COUNT(1) > 1");
        RowsMap sendplandetailsrowsMap = sendplandetails.toRowsMap("itemid");
        for (Row row : sendplandetailsgroupitemid) {
            if (sendplandetailsrowsMap.containsKey(row.getString("itemid"))) {
                BigDecimal sendqty = BigDecimal.ZERO;
                for (int a = 0; a < sendplandetailsrowsMap.get(row.getString("itemid")).size(); a++) {
                    if (a > 0) {
                        list.add("update sa_sendplandetail set canuseqty=canuseqty-" + sendqty + " where sa_sendplandetailid=" + sendplandetailsrowsMap.get(row.getString("itemid")).get(a).getLong("sa_sendplandetailid"));
                    }
                    sendqty = sendqty.add(sendplandetailsrowsMap.get(row.getString("itemid")).get(a).getBigDecimal("sendqty"));
                }
            }
        }
        dbConnect.runSqlUpdate(list);


        return getSucReturnObject().toString();
    }

    @API(title = "计划清空", apiversion = R.ID2025032709342503.v1.class)
    @CACHEING_CLEAN(apiClass = {sendplandetail.class})
    public String clearPlan() throws YosException {
        Long sa_sendplanid = content.getLongValue("sa_sendplanid");
        dbConnect.runSqlUpdate("delete from sa_sendplandetail where sa_sendplanid='" + sa_sendplanid + "' and siteid='" + siteid + "'");
        return getSucReturnObject().toString();
    }

    @API(title = "更新计划发货数量", apiversion = R.ID2025101709333503.v1.class)
    @CACHEING_CLEAN(apiClass = {sendplandetail.class})
    public String updatesendqty() throws YosException {
        long sa_sendplandetailid = content.getLong("sa_sendplandetailid");
        BigDecimal sendqty = content.getBigDecimal("sendqty");
        String outplace = content.getString("outplace");
        Rows sendplandetails = dbConnect.runSqlQuery("select t2.itemid,t1.sa_sendplanid from sa_sendplandetail t1  inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.sa_sendplandetailid=" + sa_sendplandetailid + " and t1.siteid='" + siteid + "'");
        if (sendplandetails.isEmpty()) {
            return getErrReturnObject().setErrMsg("发货计划明细不存在").toString();
        }

        dbConnect.runSqlUpdate("update sa_sendplandetail set sendqty=" + sendqty + ",outplace='" + outplace + "' where sa_sendplandetailid=" + sa_sendplandetailid);


        ArrayList<String> list = new ArrayList<>();
        Rows sendplandetailsgroupitemid = dbConnect.runSqlQuery("select t2.itemid,t1.sa_sendplandetailid,t1.sendqty,t1.canuseqty from sa_sendplandetail t1  inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.sa_sendplanid=" + sendplandetails.get(0).getLong("sa_sendplanid") + " and t2.itemid=" + sendplandetails.get(0).getLong("itemid") + " and t1.siteid='" + siteid + "' order by t2.sa_orderid desc");
        BigDecimal sendqtyold = BigDecimal.ZERO;
        BigDecimal canuseqty = sendplandetailsgroupitemid.max("canuseqty");
        for (int a = 0; a < sendplandetailsgroupitemid.size(); a++) {
            if (a > 0) {
                list.add("update sa_sendplandetail set canuseqty=" + canuseqty.subtract(sendqtyold) + " where sa_sendplandetailid=" + sendplandetailsgroupitemid.get(a).getLong("sa_sendplandetailid"));
            }
            sendqtyold = sendqtyold.add(sendplandetailsgroupitemid.get(a).getBigDecimal("sendqty"));
        }
        dbConnect.runSqlUpdate(list);
        return getSucReturnObject().toString();
    }


    @API(title = "生成销售出库单", apiversion = R.ID2025032709355603.v1.class)
    @CACHEING_CLEAN(apiClass = {sendplandetail.class})
    public String createIcstockBill() throws YosException {
        long sa_sendplanid = content.getLong("sa_sendplanid");
        Rows rows = dbConnect.runSqlQuery("select * from sa_sendplan where sa_sendplanid=" + sa_sendplanid);
        ArrayList<Long> sa_orderitemsids = new ArrayList<>();
        String billdate = "";
        if (rows.isNotEmpty()) {
            departmentid = rows.get(0).getLong("departmentid");
            billdate=rows.get(0).getString("senddate");
        }
        JSONArray detailarray = content.getJSONArray("details");
        ArrayList<String> sqlList = new ArrayList<>();
        HashMap<String, HashMap<String, List<JSONObject>>> agentmap = new HashMap<String, HashMap<String, List<JSONObject>>>();
        ArrayList<String> sys_enterpriseidList = new ArrayList<>();
        for (Object object : detailarray) {
            JSONObject jsonObject = (JSONObject) object;
            if (jsonObject.getDouble("sendqty") <= 0) {
                continue;
            }
            sa_orderitemsids.add(jsonObject.getLongValue("sa_orderitemsid"));
            String key = "";
            String sys_enterpriseid = jsonObject.getString("sys_enterpriseid");
            sys_enterpriseidList.add(sys_enterpriseid);
            String outplace = jsonObject.getString("outplace");
            String topclassnum = jsonObject.getString("topclassnum");
            String invoicename = jsonObject.getString("invoicename");
            key = outplace+topclassnum+invoicename;
            HashMap<String, List<JSONObject>> agent = null;
            List<JSONObject> paolist = null;
            if (agentmap.containsKey(sys_enterpriseid)) {
                agent = agentmap.get(sys_enterpriseid);
                if (agent.containsKey(key)) {
                    paolist = agent.get(key);
                } else {
                    paolist = new ArrayList<JSONObject>();
                }
            } else {
                agent = new HashMap<String, List<JSONObject>>();
                paolist = new ArrayList<JSONObject>();
            }
            paolist.add(jsonObject);
            agent.put(key, paolist);
            agentmap.put(sys_enterpriseid, agent);
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "*");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_orderitemsid", sa_orderitemsids);
        Rows orderdetails = querySQL.query();
        RowsMap orderdetailsRowsMap = orderdetails.toRowsMap("sa_orderitemsid");
        Iterator<Map.Entry<String, HashMap<String, List<JSONObject>>>> iter = agentmap
                .entrySet().iterator();

        ArrayList<Long> st_stockbillids = new ArrayList<Long>();
        RowsMap itemRowsMap = SQLFactory.createQuerySQL(this, "plm_item", "*").setTableAlias("t1").addJoinTable(JOINTYPE.left, "st_stock", "t2", "t1.siteid = t2.siteid and t1.stockno = t2.stockno","stockid").setWhere("itemid", orderdetails.toArrayList("itemid")).query().toRowsMap("itemid");
        RowsMap agentsRowsMap = SQLFactory.createQuerySQL(this, "sa_agents", "*").setWhere("sys_enterpriseid", sys_enterpriseidList).query().toRowsMap("sys_enterpriseid");
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            String sys_enterpriseid = (String) entry.getKey();

            HashMap<String, List<JSONObject>> keymap = (HashMap<String, List<JSONObject>>) entry
                    .getValue();
            Iterator<Map.Entry<String, List<JSONObject>>> iter2 = keymap
                    .entrySet().iterator();
            while (iter2.hasNext()) {
                Map.Entry entry2 = (Map.Entry) iter2.next();
                String key = (String) entry2.getKey();
                List<JSONObject> paolist = (List<JSONObject>) entry2
                        .getValue();
                Iterator<JSONObject> senddetailpaoit1 = paolist.iterator();
                Iterator<JSONObject> senddetailpaoit = paolist.iterator();
                Iterator<JSONObject> senddetailpaoit2 = paolist.iterator();
                List<JSONObject> list = new ArrayList<>();
                senddetailpaoit2.forEachRemaining(list::add);
                System.err.println(list.size());
                SQLFactory sqlFactory = new SQLFactory(this, "销售出库单新增");
                long st_stockbillid = createTableID("st_stockbill");
                st_stockbillids.add(st_stockbillid);
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("st_stockbillid", st_stockbillid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                sqlFactory.addParameter("logisticsmethod", agentsRowsMap.containsKey(sys_enterpriseid)?agentsRowsMap.get(sys_enterpriseid).get(0).getString("delivery"):"");
                sqlFactory.addParameter("billno", createBillCode("stockbill"));
                sqlFactory.addParameter("type", "销售出库");
                sqlFactory.addParameter("rb", 1);
                sqlFactory.addParameter("departmentid", departmentid);
                JSONObject firstItem = senddetailpaoit1.hasNext() ? senddetailpaoit1.next() : null;
                if (firstItem != null) {
                    sqlFactory.addParameter("outplace", firstItem.getStringValue("outplace"));
                    sqlFactory.addParameter("rec_contactsid", firstItem.getStringValue("rec_contactsid"));
                    sqlFactory.addParameter("delivery", "");
                    //sqlFactory.addParameter("logisticsmethod", firstItem.getStringValue("logisticsmethod"));
                    sqlFactory.addParameter("invoice_enterprisename", firstItem.getStringValue("invoicename"));
                    sqlFactory.addParameter("invoice_address", firstItem.getStringValue("invoiceaddress"));
                    sqlFactory.addParameter("invoice_taxno", firstItem.getStringValue("invoicetaxno"));
                    sqlFactory.addParameter("name", firstItem.getStringValue("contact"));
                    sqlFactory.addParameter("phonenumber", firstItem.getStringValue("phonenumber"));
                    sqlFactory.addParameter("address", firstItem.getStringValue("address"));
                    sqlFactory.addParameter("province", firstItem.getString("province"));
                    sqlFactory.addParameter("city", firstItem.getString("city"));
                    sqlFactory.addParameter("county", firstItem.getString("county"));
                    sqlFactory.addParameter("remarks", mergeStrings(list,";"));
                } else {
                    sqlFactory.addParameter("outplace", "");
                    sqlFactory.addParameter("rec_contactsid", 0);
                    sqlFactory.addParameter("delivery", "");
                    //sqlFactory.addParameter("logisticsmethod", "");
                    sqlFactory.addParameter("invoice_enterprisename", "");
                    sqlFactory.addParameter("invoice_address","");
                    sqlFactory.addParameter("invoice_taxno", "");
                    sqlFactory.addParameter("name", "");
                    sqlFactory.addParameter("phonenumber", "");
                    sqlFactory.addParameter("address", "");
                    sqlFactory.addParameter("province","");
                    sqlFactory.addParameter("city","");
                    sqlFactory.addParameter("county", "");
                    sqlFactory.addParameter("remarks", "");
                }


                sqlFactory.addParameter("billdate", StringUtils.isBlank(billdate)?getDateTime_Str():billdate);
                sqlFactory.addParameter("createby", username);
                sqlFactory.addParameter("createdate", getDateTime_Str());
                sqlFactory.addParameter("changeby", username);
                sqlFactory.addParameter("changedate", getDateTime_Str());
                sqlFactory.addParameter("checkby", "");
                sqlFactory.addParameter("checkdate", "null");

                sqlList.add(sqlFactory.getSQL());

                int rowno = 1;
                while (senddetailpaoit.hasNext()) {
                    JSONObject jsonObject = senddetailpaoit.next();
                    BigDecimal qty = new BigDecimal(jsonObject.getString("sendqty"));
                    qty = qty.compareTo(BigDecimal.ZERO) < 0 ? qty.negate() : qty;
                    //String fdetailstockno = jsonObject.getString("fstockno");
                    //String fbatchno = jsonObject.getString("fbatchno");
                    SQLFactory sqlFactorydetail = new SQLFactory(this, "销售出库单明细新增");
                    sqlFactorydetail.addParameter("siteid", siteid);
                    sqlFactorydetail.addParameter("st_stockbill_itemsid", createTableID("st_stockbill_items"));
                    sqlFactorydetail.addParameter("st_stockbillid", st_stockbillid);

                    if (orderdetailsRowsMap.containsKey(jsonObject.getLongValue("sa_orderitemsid"))) {
                        BigDecimal defaultprice = orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("price").subtract(orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("custamount"));
                        sqlFactorydetail.addParameter("remarks", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getString("remarks"));
                        sqlFactorydetail.addParameter("price", defaultprice);
                        sqlFactorydetail.addParameter("amount", defaultprice.multiply(qty));
                        sqlFactorydetail.addParameter("defaultprice", defaultprice);
                        sqlFactorydetail.addParameter("custamount", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("custamount"));
                    } else {
                        sqlFactorydetail.addParameter("remarks", "");
                        sqlFactorydetail.addParameter("price", 0);
                        sqlFactorydetail.addParameter("amount", 0);
                        sqlFactorydetail.addParameter("defaultprice", 0);
                        sqlFactorydetail.addParameter("custamount", 0);
                    }
                    sqlFactorydetail.addParameter("itemid", jsonObject.getLongValue("itemid"));
                    sqlFactorydetail.addParameter("itemname", itemRowsMap.containsKey(jsonObject.getStringValue("itemid"))?itemRowsMap.get(jsonObject.getStringValue("itemid")).get(0).getString("itemname"):jsonObject.getStringValue("itemname"));
                    sqlFactorydetail.addParameter("itemno", itemRowsMap.containsKey(jsonObject.getStringValue("itemid"))?itemRowsMap.get(jsonObject.getStringValue("itemid")).get(0).getString("itemno"):jsonObject.getStringValue("itemno"));
                    sqlFactorydetail.addParameter("model", itemRowsMap.containsKey(jsonObject.getStringValue("itemid"))?itemRowsMap.get(jsonObject.getStringValue("itemid")).get(0).getString("model"):jsonObject.getStringValue("model"));
                    sqlFactorydetail.addParameter("skucontrol", itemRowsMap.containsKey(jsonObject.getStringValue("itemid"))?itemRowsMap.get(jsonObject.getStringValue("itemid")).get(0).getLong("skucontrol"):jsonObject.getLongValue("skucontrol"));

                    sqlFactorydetail.addParameter("qty", qty);
                    sqlFactorydetail.addParameter("sa_dispatch_itemsid", 0);
                    sqlFactorydetail.addParameter("rowno", rowno);
                    sqlFactorydetail.addParameter("stockid", 1603);

                    sqlFactorydetail.addParameter("sa_orderitemsid", jsonObject.getLongValue("sa_orderitemsid"));
                    sqlFactorydetail.addParameter("sa_orderid", jsonObject.getLongValue("sa_orderid"));
                    sqlList.add(sqlFactorydetail.getSQL());
                    rowno++;
                }

            }
        }
        dbConnect.runSqlUpdate(sqlList);
        ArrayList<String> sqlList1 = new ArrayList<>();
        QuerySQL detailquerySQL = SQLFactory.createQuerySQL(this, "st_stockbill_items", "amount", "qty", "st_stockbillid");
        detailquerySQL.setTableAlias("t1");
        detailquerySQL.setSiteid(siteid);
        detailquerySQL.setWhere("t1.st_stockbillid", st_stockbillids);
        Rows rowsdetail = detailquerySQL.query();
        RowsMap rowsdetailMap = rowsdetail.toRowsMap("st_stockbillid");
        for (long st_stockbillid : st_stockbillids) {
            if (rowsdetailMap.containsKey(String.valueOf(st_stockbillid))) {
                sqlList1.add("update st_stockbill set payamount=" + rowsdetailMap.get(String.valueOf(st_stockbillid)).sum("amount") + " where st_stockbillid=" + st_stockbillid);
            }
        }
        dbConnect.runSqlUpdate(sqlList1);
        return getSucReturnObject().toString();
    }

    @API(title = "发货计划明细列表", apiversion = R.ID2025032709373303.v1.class)
    @CACHEING
    public String queryList_sendplan() throws YosException {
        Long sa_sendplanid = content.getLongValue("sa_sendplanid");

        SQLFactory sqlFactory = new SQLFactory(this, "发货计划订单未发货量(简略)");
        sqlFactory.addParameter_SQL("where", "1=1");
        sqlFactory.addParameter("siteid", siteid);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_sendplandetail", "sa_sendplanid", "sa_sendplandetailid", "colorflag", "outplace", "sendqty", "willoutqty", "willinqty", "canuseqty");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t2", "t1.siteid = t2.siteid and t1.sa_orderid = t2.sa_orderid", "sonum", "checkdate", "sys_enterpriseid", "sa_orderid", "rec_contactsid", "delivery","logisticsmethod"
                , "invoicename", "invoiceaddress","invoicetaxno","contact","phonenumber","address","province","city","county","remarks");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t3", "t3.siteid = t1.siteid and t3.sa_orderitemsid = t1.sa_orderitemsid", "rowno", "sa_orderitemsid","custamount");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t4.siteid = t1.siteid and t4.sa_agentsid = t1.sa_agentsid", "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.siteid = t4.siteid and t5.sys_enterpriseid = t4.sys_enterpriseid", "enterprisename", "abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t6", "t6.siteid = t3.siteid and t6.itemid = t3.itemid", "itemno", "itemname", "model", "itemid", "topclassnum");
        querySQL.addJoinTable(JOINTYPE.left, sqlFactory, "t7", "t7.sa_orderitemsid = t3.sa_orderitemsid", "undeliqty");
        querySQL.addQueryFields("willoutqty_jh", "t7.willoutqty");
        querySQL.addQueryFields("sendqty_canzhao", "t1.sendqty");
        querySQL.addQueryFields("remarksdetail", "t3.remarks");

        querySQL.setSiteid(siteid);
        querySQL.setCondition("t1.outplace", "t2.sonum", "t4.agentnum", "t5.enterprisename");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setWhere("sa_sendplanid", sa_sendplanid);
        querySQL.setOrderBy("t2.sa_orderid desc");
        Rows rows = querySQL.query();


        QuerySQL querySQL_ck = SQLFactory.createQuerySQL(this, "st_invbal", "itemid", "qty");
        querySQL_ck.setTableAlias("t1");
        querySQL_ck.addJoinTable(JOINTYPE.left, "st_stock", "t2", "t1.siteid = t2.siteid and t1.stockid = t2.stockid", "stockno");
        querySQL_ck.setSiteid(siteid);
        querySQL_ck.setWhere("t1.itemid", rows.toArrayList("itemid"));
        Rows rows_ck = querySQL_ck.query();
        RowsMap rowsCkRowsMap = rows_ck.toRowsMap("stockno");


        QuerySQL querySQL_cksale = SQLFactory.createQuerySQL(this, "st_invbal_sale", "itemid", "qty");
        querySQL_cksale.setTableAlias("t1");
        querySQL_cksale.setSiteid(siteid);
        querySQL_cksale.setWhere("t1.itemid", rows.toArrayList("itemid"));
        Rows rows_cksale = querySQL_cksale.query();
        RowsMap rows_cksaleRowsMap = rows_cksale.toRowsMap("itemid");



        for (Row row : rows) {
            if (rowsCkRowsMap.containsKey("101")) {
                if (rowsCkRowsMap.get("101").toRowsMap("itemid").containsKey(row.getString("itemid"))) {
                    row.put("qty_xs", rowsCkRowsMap.get("101").toRowsMap("itemid").get(row.getString("itemid")).get(0).getBigDecimal("qty"));
                } else {
                    row.put("qty_xs", 0);
                }
            } else {
                row.put("qty_xs", 0);
            }

            if (rowsCkRowsMap.containsKey("103")) {
                if (rowsCkRowsMap.get("103").toRowsMap("itemid").containsKey(row.getString("itemid"))) {
                    row.put("qty_tq", rowsCkRowsMap.get("103").toRowsMap("itemid").get(row.getString("itemid")).get(0).getBigDecimal("qty"));
                } else {
                    row.put("qty_tq", 0);
                }
            } else {
                row.put("qty_tq", 0);
            }

            if (rowsCkRowsMap.containsKey("109")) {
                if (rowsCkRowsMap.get("109").toRowsMap("itemid").containsKey(row.getString("itemid"))) {
                    row.put("qty_mq", rowsCkRowsMap.get("109").toRowsMap("itemid").get(row.getString("itemid")).get(0).getBigDecimal("qty"));
                } else {
                    row.put("qty_mq", 0);
                }
            } else {
                row.put("qty_mq", 0);
            }

            if (rows_cksaleRowsMap.containsKey(row.getString("itemid"))) {
                row.put("qty_total", rows_cksaleRowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty"));
            } else{
                row.put("qty_total", 0);
            }

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


    @API(title = "删除明细", apiversion = R.ID2025032709370703.v1.class)
    @CACHEING_CLEAN(apiClass = {sendplandetail.class})
    public String deletemx() throws YosException {
        long sa_sendplandetailid = content.getLongValue("sa_sendplandetailid");
        ArrayList<String> list = new ArrayList<>();
        list.add("delete from sa_sendplandetail where siteid='" + siteid
                + "' and sa_sendplandetailid=" + sa_sendplandetailid);
        dbConnect.runSqlUpdate(list);
        return getSucReturnObject().toString();
    }

    public static String mergeStrings(List<JSONObject> list, String delimiter) throws YosException {
        StringBuilder result = new StringBuilder();
        Set<String> seen = new LinkedHashSet<>(); // 使用LinkedHashSet保持顺序
        for (JSONObject jsonObject : list) {
            System.err.println("1111");
            String remark = jsonObject.getString("remarks");
            if (remark != null && !remark.isEmpty()) {
                seen.add(remark);
            }
        }

        // 构建结果字符串
        for (String remark : seen) {
            if (result.length() > 0) {
                result.append(delimiter);
            }
            result.append(remark);
        }

        return result.toString();
    }
    private BigDecimal getBigDecimalOrZero(BigDecimal value) {
        return value != null ? value : BigDecimal.ZERO;
    }
}