CUCU2/src/custom/restcontroller/webmanage/sale/dispatch/dispatchItems.java

package restcontroller.webmanage.sale.dispatch;

import beans.data.BatchDeleteErr;
import beans.dispatch.Dispatch;
import beans.itemclass.ItemClass;
import beans.parameter.Parameter;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.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 org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import restcontroller.R;
import restcontroller.sale.promotion.promotion;
import restcontroller.webmanage.sale.order.Order;
import restcontroller.webmanage.sale.order.OrderItems;
import utility.ERPDocking;
import utility.tools.WebRequest;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

public class dispatchItems extends Controller {

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

    @API(title = "审核订单列表查询", apiversion = R.ID20221114165903.v1.class)
    @CACHEING
    public String queryCheckOrderList() throws YosException {
        /*
         * 过滤条件设置
         */
        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("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("sonum") && !"".equals(whereObject.getString("sonum"))) {
                where.append(" and t1.sonum ='").append(whereObject.getString("sonum")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
//		String hrid = content.getString("hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "审核订单列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sys_enterpriseid", content.getLong("sys_enterpriseid"));
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

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

    @API(title = "发货商品列表查询", apiversion = R.ID20221114170003.v1.class)
    @CACHEING
    public String queryOrderItemgList() throws YosException {
        /*
         * 过滤条件设置
         */
        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("t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.unit like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.typemx like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("sa_orderid") && !"".equals(whereObject.getString("sa_orderid"))) {
                Long sa_orderid = whereObject.getLongValue("sa_orderid");
                if (sa_orderid > 0) {
                    where.append("  and t1.sa_orderid ='").append(sa_orderid).append("' ");
                }
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "发货商品列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sys_enterpriseid", content.getLongValue("sys_enterpriseid"));
        sqlFactory.addParameter("sa_dispatchid", content.getLongValue("sa_dispatchid"));
        sqlFactory.addParameter("siteid", siteid);

        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

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

    @API(title = "erp批次库存查询", apiversion = R.ID20230427101303.v1.class)
    @CACHEING
    public String queryErpIcinvbalBatchList() throws YosException {
        BigDecimal qty = content.getBigDecimalValue("qty");
        JSONArray jsonArray = content.getJSONArray("itemnos");
        JSONArray jsonArrayfinal = new JSONArray();
        ERPDocking erpDocking = new ERPDocking(siteid);
        JSONArray rows = erpDocking.getErpIcinvbalBatchRows(1000, 1, jsonArray);
        for (Object object : rows) {
            JSONObject jsonObject = (JSONObject) object;
            if (jsonObject.getBigDecimalValue("fqty").compareTo(qty) >= 0) {
                jsonArrayfinal.add(jsonObject);
            }
        }
        return getSucReturnObject().setData(jsonArrayfinal).toString();
    }

    @API(title = "新建或修改发货单商品明细", apiversion = R.ID20221115104603.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(apiClass = {dispatchItems.class, dispatch.class})
    public String insertormodify_dispatchItems() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        JSONArray iteminfos = content.getJSONArray("iteminfos");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rowscount = dbConnect.runSqlQuery("select billno,status from sa_dispatch where sa_dispatchid=" + sa_dispatchid);
        if (!rowscount.isEmpty()) {
            if (!rowscount.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("非新建状态的发货单无法新增修改").toString();
            }
        }
        int i = 0;
        long maxid = 0;
        long[] sa_dispatch_itemsid = createTableID("sa_dispatch_items", iteminfos.size());
        Rows maxidRows = dbConnect
                .runSqlQuery("select MAX(rowno) rowno from sa_dispatch_items where sa_dispatchid=" + sa_dispatchid);
        if (!maxidRows.isEmpty()) {
            maxid = maxidRows.get(0).getLong("rowno");
        }
        for (Object obj : iteminfos) {
            JSONObject iteminfo = (JSONObject) obj;
            if (iteminfo.getLong("sa_dispatch_itemsid") <= 0 || dbConnect
                    .runSqlQuery("select sa_dispatch_itemsid from sa_dispatch_items where sa_dispatch_itemsid="
                            + iteminfo.getLong("sa_dispatch_itemsid"))
                    .isEmpty()) {
//                Rows sa_dispatch_itemsrows = dbConnect.runSqlQuery("select sa_dispatch_itemsid,qty,remarks from sa_dispatch_items where sa_dispatchid="
//                        + sa_dispatchid + " and sa_orderitemsid=" + iteminfo.getLong("sa_orderitemsid"));
//                if (!sa_dispatch_itemsrows.isEmpty()) {
//                    SQLFactory saleFactory = new SQLFactory(this, "发货单明细更新");
//                    saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsrows.get(0).getLong("sa_dispatch_itemsid"));
//                    // saleFactory.addParameter("itemno", iteminfo.getString("itemno"));
//                    saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty").add(sa_dispatch_itemsrows.get(0).getBigDecimal("qty")));
//                    if (!StringUtils.isBlank(iteminfo.getString("remarks"))) {
//                        saleFactory.addParameter("remarks", iteminfo.getString("remarks"));
//                    } else {
//                        saleFactory.addParameter("remarks", sa_dispatch_itemsrows.get(0).getString("remarks"));
//                    }
//
//                    saleFactory.addParameter("userid", userid);
//                    saleFactory.addParameter("username", username);
//                    saleFactory.addParameter("billno", rowscount.get(0).getString("billno"));
//                    saleFactory.addParameter("batchno", iteminfo.getStringValue("batchno"));
//                    sqlList.add(saleFactory.getSQL());
//                } else {
//                    SQLFactory saleFactory = new SQLFactory(this, "发货单明细新增");
//                    saleFactory.addParameter("siteid", siteid);
//                    saleFactory.addParameter("rowno", maxid + i + 1);
//                    saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsid[i]);
//                    saleFactory.addParameter("sa_dispatchid", sa_dispatchid);
//                    saleFactory.addParameter("sa_orderitemsid", iteminfo.getLong("sa_orderitemsid"));
//                    saleFactory.addParameter("itemid", iteminfo.getString("itemid"));
//                    saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty"));
//                    saleFactory.addParameter("batchcontrol", iteminfo.getBooleanValue("batchcontrol"));
//                    saleFactory.addParameter("batchno", "");
//                    saleFactory.addParameter("remarks", iteminfo.getString("remarks"));
//                    saleFactory.addParameter("userid", userid);
//                    saleFactory.addParameter("username", username);
//                    saleFactory.addParameter("billno", rowscount.get(0).getString("billno"));
//                    sqlList.add(saleFactory.getSQL());
//                }
                SQLFactory saleFactory = new SQLFactory(this, "发货单明细新增");
                saleFactory.addParameter("siteid", siteid);
                saleFactory.addParameter("rowno", maxid + i + 1);
                saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsid[i]);
                saleFactory.addParameter("sa_dispatchid", sa_dispatchid);
                saleFactory.addParameter("sa_orderitemsid", iteminfo.getLong("sa_orderitemsid"));
                saleFactory.addParameter("itemid", iteminfo.getString("itemid"));
                saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty"));
                saleFactory.addParameter("batchcontrol", iteminfo.getBooleanValue("batchcontrol"));
                saleFactory.addParameter("batchno", "");
                saleFactory.addParameter("remarks", iteminfo.getString("remarks"));
                saleFactory.addParameter("userid", userid);
                saleFactory.addParameter("username", username);
                saleFactory.addParameter("billno", rowscount.get(0).getString("billno"));
                sqlList.add(saleFactory.getSQL());
                i++;
            } else {
                SQLFactory saleFactory = new SQLFactory(this, "发货单明细更新");
                saleFactory.addParameter("sa_dispatch_itemsid", iteminfo.getLong("sa_dispatch_itemsid"));
                // saleFactory.addParameter("itemno", iteminfo.getString("itemno"));
                saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty"));
                saleFactory.addParameter("remarks", iteminfo.getString("remarks"));
                saleFactory.addParameter("batchno", iteminfo.getStringValue("batchno"));
                saleFactory.addParameter("userid", userid);
                saleFactory.addParameter("username", username);
                saleFactory.addParameter("billno", rowscount.get(0).getString("billno"));
                sqlList.add(saleFactory.getSQL());
            }

        }

        dbConnect.runSqlUpdate(sqlList);
        return queryDispatchItemsList();
    }


    @API(title = "发货单商品明细列表", apiversion = R.ID20221115104703.v1.class)
    @CACHEING
    public String queryDispatchItemsList() throws YosException {
        /*
         * 过滤条件设置
         */
        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.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sa_dispatchid = content.getLong("sa_dispatchid");
//			String hrid = content.getString("hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单明细列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap rowsMap = rows.toRowsMap("itemno");
        ERPDocking erpDocking = new ERPDocking(siteid);
        JSONArray jsonArray = new JSONArray();
        JSONObject object = new JSONObject();
        object.put("classname", "getIcinvbal");
        object.put("method", "getMsg");

        JSONObject content = new JSONObject();
        content.put("fitemnos", rows.toJsonArray("itemno"));
        object.put("content", content);

        WebRequest request = new WebRequest();
        //System.out.println(object.toString());
        if(siteid.equalsIgnoreCase("lsa")){
            String result = request.doPost(object.toString(),
                    "http://60.190.151.198:8089/BYESB/jaxrs/webclientrest");
            if(isJSONArray(result)){
                jsonArray = JSONArray.parseArray(result);
            }
        }else{
            if (rows.toJsonArray("itemno").size() != 0) {
                if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") ) {
                    jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno"));
                }
            }
        }
        SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总");
        sqlFactory1.addParameter("siteid", siteid);
        sqlFactory1.addParameter_in("itemno", rows.toArrayList("itemno", new ArrayList<>()));
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno");
        if(!siteid.equalsIgnoreCase("lsa")){
            for(Row row : rows){
                if(StringUtils.isNotBlank(row.getString("batchno"))){
                    JSONArray jsonArray1 =new JSONArray();
                    jsonArray1.add(row.getString("itemno"));
                    JSONArray jsonArrayBatch = erpDocking.getErpIcinvbalBatchRowsforbatchno(20000, 1, jsonArray1,row.getString("batchno"));
                    if(!jsonArrayBatch.isEmpty()){
                        if(sumQtyRowsMap.containsKey(row.getString("itemno"))){
                            RowsMap rowsMapByBatchno = sumQtyRowsMap.get(row.getString("itemno")).toRowsMap("batchno");
                            if(rowsMapByBatchno.containsKey(row.getString("batchno"))){
                                for(Row row1 :sumQtyRowsMap.get(row.getString("itemno"))){
                                    if(row.getString("batchno").equals(row1.getString("batchno"))){
                                        row.put("delinvbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty").subtract(row1.getBigDecimal("qty")));
                                        row.put("invbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty"));
                                    }
                                }
                            }else{
                                row.put("delinvbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty"));
                                row.put("invbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty"));
                            }

                        }else{
                            row.put("delinvbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty"));
                            row.put("invbalqty", ((JSONObject)jsonArrayBatch.get(0)).getBigDecimalValue("fqty"));
                        }
                    }else{
                        row.put("delinvbalqty", row.getBigDecimal("qty").negate());
                        row.put("invbalqty", 0);
                    }
                }
            }
        }


        if (!jsonArray.isEmpty()) {
            for (Object obj : jsonArray) {
                JSONObject jsonObject = (JSONObject) obj;

                if (rowsMap.containsKey(jsonObject.getString("fitemno"))) {
                    if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) {
                        if (sumQtyRowsMap.containsKey(jsonObject.getString("fitemno"))) {
                            if(siteid.equalsIgnoreCase("lsa")){
                                for(Row row :rowsMap.get(jsonObject.getString("fitemno"))){
                                    row.put("delinvbalqty", jsonObject.getBigDecimalValue("FQty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty")));
                                }
                                //rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("FQty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty")));
                            }else{
                                for(Row row :rowsMap.get(jsonObject.getString("fitemno"))){
                                    if(StringUtils.isBlank(row.getString("batchno"))){
                                        for(Row row1 :sumQtyRowsMap.get(jsonObject.getString("fitemno"))){
                                            if(StringUtils.isBlank(row1.getString("batchno"))){
                                                row.put("delinvbalqty", jsonObject.getBigDecimalValue("fqty").subtract(row1.getBigDecimal("qty")));
                                            }
                                        }

                                    }
                                }
                                //rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty")));
                            }

                        } else {
                            if(siteid.equalsIgnoreCase("lsa")){
                                for(Row row :rowsMap.get(jsonObject.getString("fitemno"))){
                                    row.put("delinvbalqty", jsonObject.getBigDecimalValue("FQty"));
                                }
                                //rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("FQty"));
                            }else{
                                for(Row row :rowsMap.get(jsonObject.getString("fitemno"))){
                                    if(StringUtils.isBlank(row.getString("batchno"))){
                                        row.put("delinvbalqty", jsonObject.getBigDecimalValue("fqty"));
                                    }
                                }
                                //rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty"));
                            }

                        }
                        if(siteid.equalsIgnoreCase("lsa")){
                            rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("FQty"));
                        }else{
                            for(Row row :rowsMap.get(jsonObject.getString("fitemno"))){
                                if(StringUtils.isBlank(row.getString("batchno"))){
                                    row.put("invbalqty", jsonObject.getBigDecimalValue("fqty"));
                                }
                            }
                            //rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty"));
                        }

                    }
                }
            }
        }

        for (Row row : rows) {
            if (!row.containsKey("delinvbalqty")) {
                row.put("delinvbalqty", row.getBigDecimal("undeliqty").negate());
                row.put("invbalqty", 0);
            }
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "发货单商品所有明细列表", apiversion = R.ID20230508113003.v1.class)
    @CACHEING
    public String queryDispatchAllItemsList() throws YosException, IOException {
        /*
         * 过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (sys_enterpriseid > 0) {
            where.append(" and t4.sys_enterpriseid ='").append(sys_enterpriseid).append("' ");
            where.append(" and  ifnull(t1.outwarehouseqty, 0)>0 ");
        }

        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append(" t4.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t8.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t10.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append("t9.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t10.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("iteminfo") && !"".equals(whereObject.getString("iteminfo"))) {
                where.append(" and(");
                where.append("t2.itemno like'%").append(whereObject.getString("iteminfo")).append("%' ");
                where.append("or t2.itemname like'%").append(whereObject.getString("iteminfo")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t4.billdate >=date('").append(whereObject.getString("begindate")).append("') ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t4.billdate <=date('").append(whereObject.getString("enddate")).append("') ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t4.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("isnotsent") && !"".equals(whereObject.getString("isnotsent"))) {
                if (whereObject.getBooleanValue("isnotsent")) {
                    //System.out.println(" and t4.status ='复核' and  ifnull(t1.outwarehouseqty, 0)< ifnull(t1.qty, 0)");
                    where.append(" and t1.sa_dispatchid in(SELECT sa_dispatchid FROM (SELECT sum(outwarehouseqty) outwarehouseqty,sa_dispatchid FROM sa_dispatch_items WHERE siteid='"+siteid+"' GROUP BY sa_dispatchid) a WHERE a.outwarehouseqty=0)");
                }
            }
            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {

                ArrayList<Long> list = ItemClass.getSubItemClassIds(this, whereObject.getLong("itemclassid"));
                list.add(whereObject.getLong("itemclassid"));

                SQLFactory sqlFactory = new SQLFactory(this, "查询营销类别下的商品");
                sqlFactory.addParameter("siteid",siteid);
                sqlFactory.addParameter_in("itemclassid", list);
                where.append(" and t2.itemid in (").append(sqlFactory.getSQL()).append(") ");
            }
        }
        boolean isExport = content.getBooleanValue("isExport");
//        SQLFactory sqlFactory = new SQLFactory(this, "发货单明细所有列表查询", pageSize, pageNumber, pageSorting);
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "发货单明细所有列表查询");
//        }
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("siteid", siteid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = queryListManage(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
        querySQL.setPage(pageSize, pageNumber);

        rows = querySQL.query();

        SQLFactory areasqlFactory = new SQLFactory(this, "发货单明细关联区域列表查询");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter_in("sys_enterpriseids", rows.toArrayList("sys_enterpriseid", new ArrayList<>()));
        Rows areaRows = dbConnect.runSqlQuery(areasqlFactory);
        RowsMap areaRowsMap = areaRows.toRowsMap("sys_enterpriseid");

        SQLFactory itemclasssqlFactory = new SQLFactory(this, "发货单明细关联商品营销类别查询");
        itemclasssqlFactory.addParameter("siteid", siteid);
        itemclasssqlFactory.addParameter_in("itemids", rows.toArrayList("itemid", new ArrayList<>()));
        Rows itemclassRows = dbConnect.runSqlQuery(itemclasssqlFactory);
        RowsMap itemclassRowsMap = itemclassRows.toRowsMap("itemid");


//        SQLFactory itemsqlFactory = new SQLFactory(this, "发货单明细关联商品列表查询");
//        itemsqlFactory.addParameter("siteid", siteid);
//        itemsqlFactory.addParameter_in("itemids", rows.toArrayList("itemid", new ArrayList<>()));
//        Rows itemRows = dbConnect.runSqlQuery(itemsqlFactory);
//        RowsMap itemRowsMap = itemRows.toRowsMap("itemid");

//        SQLFactory ordersqlFactory = new SQLFactory(this, "发货单明细关联订单列表查询");
//        ordersqlFactory.addParameter("siteid", siteid);
//        ordersqlFactory.addParameter_in("sa_orderitemsids", rows.toArrayList("sa_orderitemsid", new ArrayList<>()));
//        Rows orderRows = dbConnect.runSqlQuery(ordersqlFactory);
//        RowsMap orderRowsMap = orderRows.toRowsMap("sa_orderitemsid");


        RowsMap rowsMap = rows.toRowsMap("itemno");
        ERPDocking erpDocking = new ERPDocking(siteid);
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
            if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") ) {
                //jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno"));
            }

        }
        SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总");
        sqlFactory1.addParameter("siteid", siteid);
        sqlFactory1.addParameter_in("itemno", rows.toArrayList("itemno", new ArrayList<>()));
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno");

        if (!jsonArray.isEmpty()) {
            for (Object object : jsonArray) {
                JSONObject jsonObject = (JSONObject) object;

                if (rowsMap.containsKey(jsonObject.getString("fitemno"))) {

                    if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) {
                        if (sumQtyRowsMap.containsKey(jsonObject.getString("fitemno"))) {
                            rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty")));
                        } else {
                            rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty"));
                        }
                        rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty"));
                    }
                }
            }
        }

        for (Row row : rows) {
            if (areaRowsMap.containsKey(row.getString("sys_enterpriseid"))) {
                row.put("areaname", areaRowsMap.get(row.getString("sys_enterpriseid")).get(0).getString("areaname"));
            } else {
                row.put("areaname", "");
            }
            if (itemclassRowsMap.containsKey(row.getString("itemid"))) {
                row.put("itemclassname", itemclassRowsMap.get(row.getString("itemid")).get(0).getString("itemclassname"));
            } else {
                row.put("itemclassname", "");
            }

            if (!row.containsKey("delinvbalqty")) {
                row.put("delinvbalqty", row.getBigDecimal("undeliqty").negate().doubleValue());
                row.put("invbalqty", 0);
            }
        }

//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("sa_dispatch_itemsid");
//            rows.getFieldList().remove("sa_dispatchid");
//            rows.getFieldList().remove("sa_orderitemsid");
//            rows.getFieldList().remove("sa_orderid");
//            rows.getFieldList().remove("sys_enterpriseid");
//            rows.getFieldList().remove("isclose");
//            rows.getFieldList().remove("rowno");
//            rows.getFieldList().remove("itemid");
//            rows.getFieldList().remove("batchno");
//            Rows rowsrolename= dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid="+userid);
//            if(!rowsrolename.isEmpty()){
//                rows.getFieldList().remove("price");
//                rows.getFieldList().remove("amount");
//            }
//            rows.getFieldList().remove("undeliqty");
//            rows.getFieldList().remove("deliedqty");
//            rows.getFieldList().remove("outwarehousedate");
//            Rows uploadRows = uploadExcelToObs("dispatch", "发货单明细列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }
        return getSucReturnObject().setData(rows).toString();
    }


    //查询订单列表(管理端)
    public QuerySQL queryListManage(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_dispatch_items",
                "sa_dispatch_itemsid", "sa_dispatchid", "sa_orderitemsid", "isclose", "rowno", "itemid", "outwarehousedate", "batchno", "remarks");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t2.itemid = t1.itemid AND t2.siteid = t1.siteid",
                "itemno","itemname");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_dispatch", "t4", "t4.sa_dispatchid = t1.sa_dispatchid AND t4.siteid = t1.siteid",
                "billno", "status", "billdate", "closedate");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t5", "t5.sa_orderitemsid=t1.sa_orderitemsid and t5.siteid=t1.siteid",
                "spec","price","undeliqty","deliedqty","amount","rowno");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t8", "t8.sa_orderid=t4.sa_orderid and t8.siteid=t4.siteid",
                "sonum","type","typemx","sa_accountclassinfos","sa_orderid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t9", "t9.sys_enterpriseid = t4.sys_enterpriseid and t9.siteid = t4.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t10", "t10.sys_enterpriseid = t4.sys_enterpriseid and t10.siteid = t4.siteid",
                "enterprisename", "sys_enterpriseid");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t11", "t11.unitid = t2.unitid and t11.siteid = t2.siteid",
                "unitname");

        querySQL.addJoinTable(JOINTYPE.left, "sa_accountclass", "t12", "t12.sa_accountclassid=t8.sa_accountclassid and t12.siteid=t8.siteid");
        querySQL.addQueryFields("qty", "ifnull(t1.qty, 0)");
        querySQL.addQueryFields("outwarehouseqty", "ifnull(t1.outwarehouseqty, 0)");
        querySQL.addQueryFields("orderRowno", "t5.rowno");
        querySQL.addQueryFields("accountnames", "if(ifnull(replace(replace(replace(JSON_EXTRACT (t8.sa_accountclassinfos, '$**.accountname' ),'[',''),']',''),'\\\"',''),'')='',t12.accountname,replace(replace(replace(JSON_EXTRACT (t8.sa_accountclassinfos, '$**.accountname' ),'[',''),']',''),'\\\"',''))");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        return querySQL;
    }


    @API(title = "工厂发货/退货明细", apiversion = R.ID20230626104003.v1.class)
    @CACHEING
    public String queryDispatchAndAftersalesmagItemsList() throws YosException, IOException {
        /*
         * 过滤条件设置
         */
        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("t.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t.status ='").append(whereObject.getString("status")).append("' ");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "工厂发货及退货明细(经销商)", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);



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

    @API(title = "工厂发货/退货明细(区域经理)", apiversion = R.ID20231030091403.v1.class)
    @CACHEING
    public String queryDispatchAndAftersalesmagItemsManagerList() throws YosException, IOException {
        /*
         * 过滤条件设置
         */
        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("t.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t.status ='").append(whereObject.getString("status")).append("' ");
            }
        }
        boolean isExport = content.getBooleanValue("isExport");


        SQLFactory areasqlFactory = new SQLFactory(new promotion(content), "区域经理查询下属营销区域");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter("hrid", hrid);
        Rows arearows = dbConnect.runSqlQuery(areasqlFactory);
        ArrayList<Long> arrayList = arearows.toArrayList("sa_saleareaid", new ArrayList<Long>());
        arrayList.addAll(SaleArea.getSubSaleAreaIds(this, arrayList));

        SQLFactory sqlFactory = new SQLFactory(this, "工厂发货及退货明细(区域经理)", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_in("sa_saleareaids", arrayList);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("sa_dispatch_itemsid");
//            rows.getFieldList().remove("sa_dispatchid");
//            rows.getFieldList().remove("sa_orderitemsid");
//            rows.getFieldList().remove("sa_orderid");
//            rows.getFieldList().remove("sys_enterpriseid");
//            rows.getFieldList().remove("isclose");
//            rows.getFieldList().remove("rowno");
//            rows.getFieldList().remove("itemid");
//            rows.getFieldList().remove("batchno");
//            Rows rowsrolename= dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid="+userid);
//            if(!rowsrolename.isEmpty()){
//                rows.getFieldList().remove("price");
//                rows.getFieldList().remove("amount");
//            }
//            rows.getFieldList().remove("undeliqty");
//            rows.getFieldList().remove("deliedqty");
//            rows.getFieldList().remove("outwarehousedate");
//            Rows uploadRows = uploadExcelToObs("dispatch", "发货单明细列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//       }
        return getSucReturnObject().setData(rows).toString();
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() throws YosException {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("billno", "发货单号");
        titleMap.put("status", "状态");
        titleMap.put("sonum", "订单号");
        titleMap.put("enterprisename", "企业名称");
        titleMap.put("agentnum", "经销商编号");
        titleMap.put("billdate", "发货日期");
        titleMap.put("orderRowno", "订单行号");
        titleMap.put("itemno", "产品编码");
        titleMap.put("itemname", "品名");
        titleMap.put("unitname", "单位");
        titleMap.put("qty", "发货数量");
        titleMap.put("remarks", "发货行备注");
        titleMap.put("outwarehouseqty", "已出库数量");
        Rows rowsrolename = dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid=" + userid);
        if (rowsrolename.isEmpty()) {
            titleMap.put("price", "单价");
            titleMap.put("amount", "金额");
        }

        return titleMap;
    }

    @API(title = "删除明细", apiversion = R.ID20221115104803.v1.class)
    @CACHEING_CLEAN(apiClass = {dispatchItems.class, dispatch.class})
    public String deletemx() throws YosException {

        JSONArray sa_dispatch_itemsids = content.getJSONArray("sa_dispatch_itemsids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_dispatch_itemsids.size());
        long sa_dispatchid = 0;
        for (Object o : sa_dispatch_itemsids) {
            long sa_dispatch_itemsid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select t1.sa_dispatch_itemsid,t2.status,t1.sa_dispatchid from sa_dispatch_items t1 left join sa_dispatch t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid where t1.siteid='"
                    + siteid + "' and t1.sa_dispatch_itemsid='" + sa_dispatch_itemsid + "'");

            if (RowsStatus.isNotEmpty()) {
                sa_dispatchid = RowsStatus.get(0).getLong("sa_dispatchid");
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_dispatch_itemsid, "非新建状态的发货单明细无法删除");
                    continue;
                }
            }
            ArrayList<String> list = new ArrayList<>();
            SQLFactory deletesql = new SQLFactory("sql:delete from sa_dispatch_items where siteid='" + siteid
                    + "' and sa_dispatch_itemsid=" + sa_dispatch_itemsid);
            list.add(deletesql.getSQL());

            dbConnect.runSqlUpdate(list);
        }
        //重新排序
        updateRowNo(sa_dispatchid);
        return batchDeleteErr.getReturnObject().toString();
    }


    /**
     * 重新对发货单行进行排序
     *
     * @param
     * @throws YosException
     */
    public void updateRowNo(Long sa_dispatchid) throws YosException {
        String sql = "SELECT sa_dispatch_itemsid from sa_dispatch_items WHERE sa_dispatchid= " + sa_dispatchid + " and siteid = '" + siteid + "' ORDER  BY sa_dispatch_itemsid asc ";
        ArrayList<Long> sa_dispatch_itemsids = dbConnect.runSqlQuery(sql).toArrayList("sa_dispatch_itemsid", new ArrayList<>());
        ArrayList<String> sqlList = new ArrayList<>();
        int rowno = 1;
        for (Long id : sa_dispatch_itemsids) {
            sqlList.add("UPDATE sa_dispatch_items SET rowno=" + rowno + " WHERE sa_dispatch_itemsid = " + id);
            rowno++;
        }
        dbConnect.runSqlUpdate(sqlList);
    }
}