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 restcontroller.R;

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

@API(title = "发货计划明细")
public class sendplandetail extends Controller {
    public sendplandetail(JSONObject content) throws YosException {
        super(content);
    }
    //ID2025120110193103
    @API(title = "订单信息刷新(新)", apiversion = R.ID2025032709331803.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 invbalrows = dbConnect.runSqlQuery("select itemid,sum(qty) qty from st_invbal t1 group by itemid");

        RowsMap stockbillrowsMap = stockbillrows.toRowsMap("itemid");
        RowsMap invbalrowsMap = invbalrows.toRowsMap("itemid");

        ArrayList<String> list = new ArrayList<>();
        long olditemid=0;
        BigDecimal oldcanuseqty=BigDecimal.ZERO;
        BigDecimal oldsendqty=BigDecimal.ZERO;
        for (Row row : rows) {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_sendplandetail");
            long sa_sendplandetailid=createTableID("sa_sendplandetailid");
            insertSQL.setUniqueid(sa_sendplandetailid);
            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 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 (invbalrowsMap.containsKey(row.getString("itemid"))) {
                invbalqty = invbalrowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            canuseqty = invbalqty.subtract(willoutqty);

            insertSQL.setValue("willoutqty", willoutqty);
            insertSQL.setValue("willinqty", 0);
            insertSQL.setValue("canuseqty", canuseqty);
            if (canuseqty.compareTo(BigDecimal.ZERO) <= 0) {
                insertSQL.setValue("sendqty", 0);
                insertSQL.setValue("colorflag", 0);
            } else {
                sendqty=row.getBigDecimal("sendqty");
                insertSQL.setValue("sendqty", sendqty);
                if (canuseqty.compareTo(row.getBigDecimal("sendqty")) >= 0) {
                    insertSQL.setValue("colorflag", 2);
                } else {
                    insertSQL.setValue("colorflag", 3);
                }
            }

            if(row.getLong("itemid")==olditemid){
                insertSQL.setValue("canuseqty", oldcanuseqty.subtract(oldsendqty));
            }
            olditemid=row.getLong("itemid");
            oldcanuseqty=oldcanuseqty.subtract(oldsendqty);
            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.ID2025120110193103.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 invbalrows = dbConnect.runSqlQuery("select itemid,sum(qty) qty from st_invbal t1 group by itemid");
        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 invbalrowsMap = invbalrows.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")).setSiteid(siteid);
            if (stockbillrowsMap.containsKey(row.getString("itemid"))) {
                willoutqty = stockbillrowsMap.get(row.getString("itemid")).get(0).getBigDecimal("qty");
            }
            if (invbalrowsMap.containsKey(row.getString("itemid"))) {
                invbalqty = invbalrowsMap.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<>();

        if(rows.isNotEmpty()){
            departmentid=rows.get(0).getLong("departmentid");
        }
        JSONArray detailarray = content.getJSONArray("details");
        ArrayList<String> sqlList = new ArrayList<>();
        HashMap<String, HashMap<String, List<JSONObject>>> agentmap = new HashMap<String, HashMap<String, List<JSONObject>>>();
        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");
            String topclassnum = jsonObject.getString("topclassnum");
            key = topclassnum;
            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>();

        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();
                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("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", firstItem.getStringValue("delivery"));
                } else {
                    sqlFactory.addParameter("outplace", "");
                    sqlFactory.addParameter("rec_contactsid",0);
                    sqlFactory.addParameter("delivery","");
                }


                sqlFactory.addParameter("billdate", getDateTime_Str());
                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");
                sqlFactory.addParameter("remarks", "");
                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"))) {
                        sqlFactorydetail.addParameter("remarks", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getString("remarks"));
                        sqlFactorydetail.addParameter("price", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("price"));
                        sqlFactorydetail.addParameter("amount", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("price").multiply(qty));
                        sqlFactorydetail.addParameter("defaultprice", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getBigDecimal("defaultprice"));
                        sqlFactorydetail.addParameter("custamount", orderdetailsRowsMap.get(jsonObject.getLongValue("sa_orderitemsid")).get(0).getString("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("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","invoicename","invoiceaddress");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t3", "t3.siteid = t1.siteid and t3.sa_orderitemsid = t1.sa_orderitemsid", "rowno", "remarks", "sa_orderitemsid");
        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.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");
        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);
            }

        }
        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();
    }


}