cucu_YOS/src/custom/restcontroller/webmanage/sale/dispatch/dispatch.java

package restcontroller.webmanage.sale.dispatch;

import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.dispatch.Dispatch;
import beans.parameter.Parameter;
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.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.order.Order;
import restcontroller.webmanage.sale.order.OrderItems;
import utility.ERPDocking;

import java.math.BigDecimal;
import java.util.ArrayList;

import static java.time.LocalDate.now;


/**
 * 发货单
 */
@API(title = "发货单")
public class dispatch extends Controller {
    private static Logger logger = Logger.getLogger(dispatch.class);
    public dispatch(JSONObject arg0) throws YosException {
        super(arg0);
        // TODO Auto-generated constructor stub
    }


    @API(title = "发货单新增更新", apiversion = R.ID20221114135203.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221114135303.v1.class, R.ID20221114135403.v1.class, R.ID20230320160203.v1.class})
    public String insertormodify_dispatch() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名

        String tableName = "sa_dispatch";
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        Long sa_orderid = content.getLong("sa_orderid");
        // Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        // Long sa_logiscompid = content.getLong("sa_logiscompid");
        // Long rec_contactsid = content.getLongValue("rec_contactsid");
        String remarks = content.getStringValue("remarks");
        String billdate = content.getStringValue("billdate");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单新增");

        if (sa_dispatchid <= 0) {
            sa_dispatchid = createTableID(tableName);
            Rows rowsinfo = dbConnect
                    .runSqlQuery("select contactsid from sys_enterprise_contacts where workaddress=1 and sys_enterpriseid="
                            + content.getLong("sys_enterpriseid"));
            if (rowsinfo.isEmpty()) {
                //return getErrReturnObject().setErrMsg("该订单不存在").toString();
            }
            sqlFactory.addParameter("sa_orderid", sa_orderid);
            // 订货企业id
            sqlFactory.addParameter("sys_enterpriseid",content.getLong("sys_enterpriseid"));
            // 物流公司档案ID
            //sqlFactory.addParameter("sa_logiscompid", rowsinfo.get(0).getLong("sa_logiscompid"));
            // 合作企业联系人表ID（收货信息）
            if(!rowsinfo.isEmpty()){
                sqlFactory.addParameter("rec_contactsid", rowsinfo.get(0).getLong("contactsid"));
            }else {
                sqlFactory.addParameter("rec_contactsid",0);
            }

            //运费状态
            sqlFactory.addParameter("freightstatus","");
            sqlFactory.addParameter("remarks",remarks);
            sqlFactory.addParameter("billdate", now());
            sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "新增", "发货单新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT status,sys_enterpriseid,rec_contactsid,sa_orderid,freightstatus from sa_dispatch WHERE sa_dispatchid = "
                            + sa_dispatchid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建")) {
                    sqlFactory = new SQLFactory(this, "发货单更新");
                    if (sa_orderid <= 0) {
                        sqlFactory.addParameter("sa_orderid", rows.get(0).getLong("sa_orderid"));
                        // 订货企业id
                        if (content.getLong("sys_enterpriseid") <= 0) {
                            sqlFactory.addParameter("sys_enterpriseid", rows.get(0).getLong("sys_enterpriseid"));
                        } else {
                            sqlFactory.addParameter("sys_enterpriseid", content.getLong("sys_enterpriseid"));
                        }
                        // 物流公司档案ID
//                        if (content.getLong("sa_logiscompid") <= 0) {
//                            sqlFactory.addParameter("sa_logiscompid", rows.get(0).getLong("sa_logiscompid"));
//                        } else {
//                            sqlFactory.addParameter("sa_logiscompid", content.getLong("sa_logiscompid"));
//                        }
                        // 合作企业联系人表ID（收货信息）
                        if (content.getLong("rec_contactsid") <= 0) {
                            sqlFactory.addParameter("rec_contactsid", rows.get(0).getLong("rec_contactsid"));
                        } else {
                            sqlFactory.addParameter("rec_contactsid", content.getLong("rec_contactsid"));
                        }
                    } else {
                        Rows rowsinfo = dbConnect.runSqlQuery(
                                "select rec_contactsid,sys_enterpriseid from sa_order where sa_orderid="
                                        + sa_orderid);
                        if (rowsinfo.isEmpty()) {
                            return getErrReturnObject().setErrMsg("该发货单不存在").toString();
                        }
                        sqlFactory.addParameter("sa_orderid", sa_orderid);
                        // 订货企业id
                        sqlFactory.addParameter("sys_enterpriseid", rowsinfo.get(0).getLong("sys_enterpriseid"));
                        // 物流公司档案ID
                        //sqlFactory.addParameter("sa_logiscompid", rowsinfo.get(0).getLong("sa_logiscompid"));
                        // 合作企业联系人表ID（收货信息）
                        sqlFactory.addParameter("rec_contactsid", rowsinfo.get(0).getLong("rec_contactsid"));
                    }
                    //运费状态
                    if (!StringUtils.isBlank(content.getStringValue("freightstatus"))) {
                        sqlFactory.addParameter("freightstatus", content.getStringValue("freightstatus"));
                    } else {
                        sqlFactory.addParameter("freightstatus", rows.get(0).getString("freightstatus"));
                    }
                    sqlFactory.addParameter("billdate", billdate);
                    sqlFactory.addParameter("remarks", remarks);
                    sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "更新", "发货单更新成功").getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("非新建状态下无法编辑").toString();
                }

            } else {
                return getErrReturnObject().setErrMsg("该发货单不存在").toString();
            }

        }

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
        // 发货单号createBillCode("dispatchbillno")
        sqlFactory.addParameter("billno", createBillCode("dispatchbill"));


        sqlList.add(sqlFactory.getSQL());
        dbConnect.runSqlUpdate(sqlList);

        content.put("sa_dispatchid", sa_dispatchid);

        return queryDispatchMain();
    }

    @API(title = "发货单详情", apiversion = R.ID20221114135303.v1.class)
    @CACHEING
    public String queryDispatchMain() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单详情查询");
        sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Rows sum = dbConnect.runSqlQuery("select sum(t1.qty * t2.price) sumamount,sum(t1.qty) qty from sa_dispatch_items t1 inner join sa_orderitems t2 on t1.sa_orderitemsid = t2.sa_orderitemsid and t1.siteid = t2.siteid  where t1.siteid='" + siteid + "' AND t1.sa_dispatchid=" + sa_dispatchid);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        if (sum.isNotEmpty()) {
            row.put("sumamount", sum.get(0).getBigDecimal("sumamount"));
            row.put("qty", sum.get(0).getBigDecimal("qty"));
        }
        //物流
//        SQLFactory sqlFactory1 = new SQLFactory(this, "发货单-查询物流信息");
//        sqlFactory1.addParameter("siteid", siteid);
//        sqlFactory1.addParameter("sa_dispatchid", sa_dispatchid);
//        Rows logiscompRows = dbConnect.runSqlQuery(sqlFactory1);
//        row.put("logiscomp", logiscompRows.isNotEmpty() ? logiscompRows.get(0) : new Row());
        //收货
        SQLFactory sqlFactory2 = new SQLFactory(this, "发货单-查询收货信息");
        sqlFactory2.addParameter("siteid", siteid);
        sqlFactory2.addParameter("sa_dispatchid", sa_dispatchid);
        Rows recviceRows = dbConnect.runSqlQuery(sqlFactory2);
        row.put("recvice", recviceRows.isNotEmpty() ? recviceRows.get(0) : new Row());

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

    @API(title = "查询发货单列表", apiversion = R.ID20221114135403.v1.class)
    @CACHEING
    public String queryDispatchList() 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.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.sonum 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("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("param") && !"".equals(whereObject.getString("param"))) {
               Rows rowsDispatchid = dbConnect.runSqlQuery("select t1.sa_dispatchid from sa_dispatch_items t1 left join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid " +
                       "left join sa_order t3 on t2.sa_orderid=t3.sa_orderid and t2.siteid=t3.siteid left join plm_item t4 on t1.itemid=t4.itemid and t1.siteid=t4.siteid where t1.siteid='"+siteid+"' and (t4.itemno like '%"+whereObject.getString("param")+"%' or t3.sonum='"+whereObject.getString("param")+"')");

               if(rowsDispatchid.toJsonArray("sa_dispatchid").isEmpty()){
                   where.append(" and t1.sa_dispatchid in (0)");
               }else {
                   String str = rowsDispatchid.toJsonArray("sa_dispatchid").toJSONString();
                   str = str.replace("[", "(").replace("]", ")");
                   where.append(" and t1.sa_dispatchid  in" ).append(str);
               }


            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "发货单列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        
        ArrayList<Long> ids = rows.toArrayList("sa_dispatchid", new ArrayList<>());
        //查询数量金额
        RowsMap dispatchAmountRowsMap = Dispatch.getDispatchAmountAndQty(this, ids);
        
        for (Row row : rows) {
            Long id = row.getLong("sa_dispatchid");
            if (dispatchAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("sumamount", dispatchAmountRowsMap.get(id.toString()).get(0).getBigDecimal("sumamount").toPlainString());
                row.put("sumqty", dispatchAmountRowsMap.get(id.toString()).get(0).getBigDecimal("sumqty").toPlainString());
            } else {
                row.put("sumamount", 0);
                row.put("sumqty", 0);
            }

        }

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

    @API(title = "经销商列表查询(管理端)", apiversion = R.ID20230324125803.v1.class)
    public String query_agentList() 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("t4.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "经销商合作企业查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);

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


        return getSucReturnObject().setData(rows).toString();
    }
    @API(title = "删除", apiversion = R.ID20221114135503.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221114135303.v1.class, R.ID20221114135403.v1.class, R.ID20230320160203.v1.class})
    public String delete() throws YosException {

        JSONArray sa_dispatchids = content.getJSONArray("sa_dispatchids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_dispatchids.size());
        for (Object o : sa_dispatchids) {
            long sa_dispatchid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select sa_dispatchid,status from sa_dispatch where siteid='"
                    + siteid + "' and sa_dispatchid='" + sa_dispatchid + "'");

            Rows logisticsRows = dbConnect.runSqlQuery("select * from sa_logistics_items where siteid='"
                    + siteid + "' and sa_dispatchid='" + sa_dispatchid + "'");

            
            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_dispatchid, "非新建状态的发货单无法删除");
                    continue;
                }
            }
            if(logisticsRows.isNotEmpty()) {
            	batchDeleteErr.addErr(sa_dispatchid, "存在发货物流明细信息，无法删除");
                continue;
            }

            dbConnect.runSqlUpdate(
                    "delete from sa_dispatch where siteid='" + siteid + "' and sa_dispatchid=" + sa_dispatchid);
        }
        return batchDeleteErr.getReturnObject().toString();

    }

    @API(title = "审核", apiversion = R.ID20221114135603.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, dispatch.class, dispatchItems.class, R.ID20230320160203.v1.class})
    public String check() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        Long sa_orderid = 0L;
        Rows rows = dbConnect.runSqlQuery("select sa_dispatchid,status,billno,sa_orderid from sa_dispatch where sa_dispatchid ='"
                + sa_dispatchid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该发货单不存在")
                    .toString();
        } else {
            sa_orderid = rows.get(0).getLong("sa_orderid");
            if (!rows.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的发货单为非新建状态，无法审核")
                        .toString();
            }
        }

        ArrayList<String> sqlList = new ArrayList<>();
        Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from sa_dispatch_items where sa_dispatchid="+ sa_dispatchid+")" );
        RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
        Rows rowsDispatchDetail = dbConnect.runSqlQuery("select t1.qty,t1.sa_orderitemsid,t1.rowno,t2.itemname from sa_dispatch_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.sa_dispatchid=" + sa_dispatchid);
        if (!rowsDispatchDetail.isEmpty()) {
            for (Row row : rowsDispatchDetail) {
                if (rowsMap.get(row.getString("sa_orderitemsid")).isEmpty()) {
                    return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细不存在对应得订单行，无法审核")
                            .toString();
                }
                if (row.getBigDecimal("qty").compareTo(rowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("undeliqty")) == 1) {
                    return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细的数量大于对应订单行的未发货数量，无法审核")
                            .toString();
                }
                sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").subtract(row.getBigDecimal("qty")) + ",deliedqty=" + (row.getBigDecimal("qty").add(rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
            }
        }
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "发货单审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "审核", "发货单审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);


        Executor.sendEml(this, "dispatchrecheck", sa_dispatchid,new ArrayList<>());
        return getSucReturnObject().toString();

    }


    @API(title = "反审核", apiversion = R.ID20221114135703.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, dispatch.class, dispatchItems.class, R.ID20230320160203.v1.class})
    public String uncheck() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        Long sa_orderid = 0L;
        Rows rows = dbConnect.runSqlQuery("select sa_dispatchid,status,billno,sa_orderid from sa_dispatch where sa_dispatchid ='"
                + sa_dispatchid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该发货单不存在")
                    .toString();
        } else {
            sa_orderid = rows.get(0).getLong("sa_orderid");
            if (!rows.get(0).getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的发货单为非审核状态，无法反审核")
                        .toString();
            }
        }

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

        Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from sa_dispatch_items where sa_dispatchid="+ sa_dispatchid+")" );
        RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
        Rows rowsDispatchDetail = dbConnect.runSqlQuery("select t1.qty,t1.sa_orderitemsid,t1.rowno,t2.itemname from sa_dispatch_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.sa_dispatchid=" + sa_dispatchid);
        if (!rowsDispatchDetail.isEmpty()) {
            for (Row row : rowsDispatchDetail) {
//				if(rowsMap.get(row.getLong("sa_orderitemsid")).isEmpty()) {
//					return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细不存在对应得订单行，无法审核")
//							.toString();
//				}
//				if(row.getBigDecimal("qty").compareTo(rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty"))==1) {
//					return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细的数量大于对应订单行的未发货数量，无法审核")
//							.toString();
//				}
                sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty")) + ",deliedqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty")) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
            }
        }
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "发货单反审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "反审核", "发货单反审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "手工关闭", apiversion = R.ID20230404134703.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, dispatch.class, dispatchItems.class, R.ID20230320160203.v1.class})
    public String unclose() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        Long sa_orderid = 0L;
        Rows rows = dbConnect.runSqlQuery("select sa_dispatchid,status,billno,sa_orderid from sa_dispatch where sa_dispatchid ='"
                + sa_dispatchid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该发货单不存在")
                    .toString();
        } else {
            sa_orderid = rows.get(0).getLong("sa_orderid");
            if (!rows.get(0).getString("status").equals("复核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的发货单为非复核状态，无法手工关闭")
                        .toString();
            }
        }
        //对接erp生成erp发货单
        if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true")) {
            ERPDocking erpDocking =new ERPDocking();
            String result =erpDocking.closeErpSainvoice(rows.get(0).getString("billno"),true,this,sa_dispatchid);
            if(!result.equals("true")){
                return getErrReturnObject().setErrMsg(result).toString();
            }else {

            }
        }
        ArrayList<String> sqlList = new ArrayList<>();

        Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from sa_dispatch_items where sa_dispatchid="+ sa_dispatchid+")" );
        RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
        Rows rowsDispatchDetail = dbConnect.runSqlQuery("select t1.qty,t1.outwarehouseqty,t1.sa_orderitemsid,t1.rowno,t2.itemname from sa_dispatch_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.sa_dispatchid=" + sa_dispatchid);
        if (!rowsDispatchDetail.isEmpty()) {
            for (Row row : rowsDispatchDetail) {
//				if(rowsMap.get(row.getLong("sa_orderitemsid")).isEmpty()) {
//					return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细不存在对应得订单行，无法审核")
//							.toString();
//				}
//				if(row.getBigDecimal("qty").compareTo(rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty"))==1) {
//					return getErrReturnObject().setErrMsg("行号为：【" + row.getString("rowno") + "】的发货单明细的数量大于对应订单行的未发货数量，无法审核")
//							.toString();
//				}
                sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + ",deliedqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty").subtract(row.getBigDecimal("outwarehouseqty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
            }
        }
        Rows orderRows =dbConnect.runSqlQuery("select distinct t1.sa_orderid from sa_orderitems t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='" + siteid + "' and t2.sa_dispatchid=" + sa_dispatchid);
        for (Row row:orderRows) {
            dbConnect.runSqlUpdate("update sa_order set status='审核' where sa_orderid=" + row.getLong("sa_orderid") + " and siteid='" + siteid + "'");
        }
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "发货单关闭");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactoryupdate.addParameter("closeby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "手工关闭", "发货单手工关闭成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "自动关闭（erp调用）", apiversion = R.ID20230413110103.v1.class,accesstoken = false)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, dispatch.class, dispatchItems.class, R.ID20230320160203.v1.class})
    public String autoClose() throws YosException {
        String finvonum = content.getString("finvonum");

        Rows rows = dbConnect.runSqlQuery("select * from sa_dispatch where status='复核' and siteid='ZZYOS' and billno='"+finvonum+"'");
        Rows rowsdetail = dbConnect.runSqlQuery("select t1.* from sa_dispatch_items t1 inner join sa_dispatch t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid where t2.status='复核' and t1.siteid='ZZYOS'");
        RowsMap rowsMap = rowsdetail.toRowsMap("sa_dispatchid");
        ArrayList<String> sqlList = new ArrayList<>();
        ERPDocking erpDocking =new ERPDocking();
        for (Row row : rows) {
            String sa_dispatchid = row.getString("sa_dispatchid");
            String billno = row.getString("billno");
            String result =erpDocking.closeErpSainvoice(billno,false,this, row.getLong("sa_dispatchid"));
            if(result.equals("true")){
                sqlList.add("update sa_dispatch set status='关闭' where sa_dispatchid='"+sa_dispatchid+"' and siteid='ZZYOS'");
                SQLFactory sqlFactory = new SQLFactory(new DataContrlLog(), "数据操作日志新增");
                sqlFactory.addParameter("ownertable", "sa_dispatch");
                sqlFactory.addParameter("ownerid", sa_dispatchid);
                sqlFactory.addParameter("action", "自动关闭");
                sqlFactory.addParameter("remarks", "发货单自动关闭成功");
                sqlFactory.addParameter("actionuserid", "1");
                sqlFactory.addParameter("actionby", "admin");
                sqlFactory.addParameter("siteid", "ZZYOS");
                sqlList.add(sqlFactory.getSQL());
            }else {
                logger.info("发货单自动关闭错误:"+result);
                return getErrReturnObject().setErrMsg(result).toString();
            }
            JSONArray jsonArray = erpDocking.queryErpSainvoice(billno);
            if(!jsonArray.isEmpty()){
                for (Object object:jsonArray) {
                    JSONObject jsonObject = (JSONObject)object;
                    sqlList.add("update sa_dispatch_items set outwarehouseqty="+jsonObject.getBigDecimalValue("fqty1")+" where rowno ="+jsonObject.getIntValue("frownum")+" and sa_dispatchid='"+sa_dispatchid+"' and siteid='ZZYOS'");
                }
            }

        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "复核反复核", apiversion = R.ID20221114135803.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221114135303.v1.class, R.ID20221114135403.v1.class, R.ID20230320160203.v1.class})
    public String recheck() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        boolean isrecheck = content.getBooleanValue("isrecheck");
        Rows rows = dbConnect.runSqlQuery("select t1.*,t3.agentnum,t2.enterprisename,t4.sonum,t5.name,t5.phonenumber,t5.address from sa_dispatch t1 left join sys_enterprise t2  on t1.sys_enterpriseid=t2.sys_enterpriseid and t1.siteid=t2.siteid left join sa_agents t3 on t1.sys_enterpriseid=t3.sys_enterpriseid and t1.siteid=t3.siteid left join sa_order t4 on t1.sa_orderid=t4.sa_orderid and t1.siteid=t4.siteid left join sys_enterprise_contacts t5 on t1.rec_contactsid=t5.contactsid and t1.siteid=t5.siteid where t1.sa_dispatchid ='"
                + sa_dispatchid + "' and  t1.siteid='" + siteid + "'");
        Rows rowsdetail = dbConnect.runSqlQuery("select t3.sonum,t2.rowno sorowno,t4.itemno,t1.batchno,t1.rowno,t1.qty,t2.price,t1.remarks from sa_dispatch_items t1 left join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid left join sa_order t3 on t2.sa_orderid=t3.sa_orderid and t2.siteid=t3.siteid left join plm_item t4 on t1.itemid=t4.itemid and t1.siteid=t4.siteid  where t1.sa_dispatchid ='"
                + sa_dispatchid + "' and  t1.siteid='" + siteid + "'");
        for (Row row : rows) {
            if (isrecheck) {
                if (!row.getString("status").equals("审核")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的发货单为非审核状态，无法复核")
                            .toString();
                }
            } else {
                if (!row.getString("status").equals("复核")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的发货单为非复核状态，无法反复核")
                            .toString();
                }
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactoryupdate;
        if (isrecheck) {
            sqlFactoryupdate = new SQLFactory(this, "发货单复核");
            Rows orderRows =dbConnect.runSqlQuery("select distinct t1.sa_orderid from sa_orderitems t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='" + siteid + "' and t2.sa_dispatchid=" + sa_dispatchid);
            //对接erp生成erp发货单
            if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true")) {
                ERPDocking erpDocking =new ERPDocking();
                String result =erpDocking.createErpSainvoice(rows.get(0),rowsdetail,this,sa_dispatchid);
                if(!result.equals("true")){
                     System.out.println(result);
                    return getErrReturnObject().setErrMsg(result).toString();
                }
            }
            //判断当前订单是否已发完货，如果发完，则生把订单变为关闭状态
            //总数量
            Rows totalRows = dbConnect.runSqlQuery("select count(1) count,sa_orderid from sa_orderitems where siteid='" + siteid + "' group by sa_orderid");
            RowsMap totalRowsMap=totalRows.toRowsMap("sa_orderid");
            //已发完数量
            Rows deliRows = dbConnect.runSqlQuery("select count(1) count,sa_orderid from sa_orderitems where undeliqty=0 and siteid='" + siteid + "' group by sa_orderid");
            RowsMap deliRowsMap=deliRows.toRowsMap("sa_orderid");
            for (Row row:orderRows) {
                if(totalRowsMap.containsKey(row.getString("sa_orderid")) && deliRowsMap.containsKey(row.getString("sa_orderid"))){
                    if (totalRowsMap.get(row.getString("sa_orderid")).get(0).getLong("count") ==deliRowsMap.get(row.getString("sa_orderid")).get(0).getLong("count")) {
                        dbConnect.runSqlUpdate("update sa_order set status='关闭' where sa_orderid=" + row.getLong("sa_orderid") + " and siteid='" + siteid + "'");
                    }
                }
            }
            Executor.sendEml(this, "dispatchreccheck", sa_dispatchid,new ArrayList<>());
            sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "复核", "发货单复核成功").getSQL());
        } else {
            sqlFactoryupdate = new SQLFactory(this, "发货单反复核");
            sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "反复核", "发货单反复核成功").getSQL());
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactoryupdate.addParameter("recheckby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "一键生成发货单", apiversion = R.ID20230220100604.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221114135303.v1.class, R.ID20221114135403.v1.class, R.ID20230320160203.v1.class})
    public String generatedispatch() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        Rows orderRows = dbConnect.runSqlQuery("select freightstatus,sys_enterpriseid,rec_contactsid,status,remarks from sa_order where siteid='" + siteid + "' and sa_orderid=" + sa_orderid);
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单无效").toString();
        }
        if (!orderRows.get(0).getString("status").equals("审核") && !orderRows.get(0).getString("status").equals("关闭")) {
            return getErrReturnObject().setErrMsg("非审核后订单无法生成发货单").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        long sa_dispatchid = createTableID("sa_dispatch");
        String billCode = createBillCode("dispatchbill");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
        sqlFactory.addParameter("billno", billCode);
        sqlFactory.addParameter("sys_enterpriseid", orderRows.get(0).getLong("sys_enterpriseid"));
        sqlFactory.addParameter("rec_contactsid", orderRows.get(0).getLong("rec_contactsid"));
        //sqlFactory.addParameter("sa_logiscompid", orderRows.get(0).getLong("sa_logiscompid"));
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("remarks", orderRows.get(0).getString("remarks"));
        sqlFactory.addParameter("freightstatus", orderRows.get(0).getString("freightstatus"));
        sqlFactory.addParameter("billdate", now());
        sqlList.add(sqlFactory.getSQL());
        RowsMap orderitemsRowsMap = dbConnect.runSqlQuery("select itemid,itemno,qty,sa_orderitemsid,sa_orderid,remarks from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid).toRowsMap("sa_orderitemsid");
        RowsMap dispatchitemsRowsMap;
        if (dbConnect.runSqlQuery("select 1 from sys_site_parameter where siteid='" + siteid + "' and dispatch_recheck=1").isEmpty()) {
            dispatchitemsRowsMap = dbConnect.runSqlQuery("select sa_orderid,sa_orderitemsid,ifnull(sum(qty),0) qty from sa_dispatch t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_dispatchid=t2.sa_dispatchid where t1.siteid='" + siteid + "' and t1.status in ('审核','关闭') group by t1.sa_orderid,t2.sa_orderitemsid").toRowsMap("sa_orderitemsid");
        } else {
            dispatchitemsRowsMap = dbConnect.runSqlQuery("select sa_orderid,sa_orderitemsid,ifnull(sum(qty),0) qty from sa_dispatch t1 inner join sa_dispatch_items t2 on t1.siteid=t2.siteid and t1.sa_dispatchid=t2.sa_dispatchid where t1.siteid='" + siteid + "' and t1.status in ('复核','关闭') group by t1.sa_orderid,t2.sa_orderitemsid").toRowsMap("sa_orderitemsid");
        }
        Long rowno = 1L;
        for (Object o : sa_orderitemsids) {
            String sa_orderitemsid = o.toString();
            BigDecimal qty = new BigDecimal("0");
            if (orderitemsRowsMap.containsKey(sa_orderitemsid)) {
                Rows orderitemsRows = orderitemsRowsMap.get(sa_orderitemsid);
                qty = orderitemsRows.get(0).getBigDecimal("qty");
                if (dispatchitemsRowsMap.containsKey(sa_orderitemsid)) {
                    RowsMap dispatchRowsMap = dispatchitemsRowsMap.get(sa_orderitemsid).toRowsMap("sa_orderid");
                    if (dispatchRowsMap.containsKey(sa_orderid.toString())) {
                        Rows orderitemRows = dispatchRowsMap.get(sa_orderid.toString());
                        if (orderitemsRows.get(0).getBigDecimal("qty").compareTo(orderitemRows.get(0).getBigDecimal("qty")) < 1) {
                            continue;
                        } else {
                            qty = qty.subtract(orderitemRows.get(0).getBigDecimal("qty"));
                        }
                    }
                }
                sqlFactory = new SQLFactory(this, "发货单明细新增");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_dispatch_itemsid", createTableID("sa_dispatch_items"));
                sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
                sqlFactory.addParameter("rowno", rowno);
                sqlFactory.addParameter("billno", billCode);
                rowno++;
                sqlFactory.addParameter("sa_orderitemsid", sa_orderitemsid);
                sqlFactory.addParameter("itemid", orderitemsRows.get(0).getLong("itemid"));
                sqlFactory.addParameter("qty", qty);
                sqlFactory.addParameter("batchcontrol", 0);
                sqlFactory.addParameter("batchno", "");
                sqlFactory.addParameter("remarks", orderRows.get(0).getString("remarks"));
                sqlFactory.addParameter("userid", userid);
                sqlFactory.addParameter("username", username);
                sqlList.add(sqlFactory.getSQL());
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_dispatchid", sa_dispatchid);
        return queryDispatchMain();
    }

    @API(title = "查询erp对接记录", apiversion = R.ID20230228133403.v1.class)
    @CACHEING
    public String queryErpupdatelog() throws YosException {
        Long sa_dispatchid = content.getLong("sa_dispatchid");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单erp对接记录", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_dispatchid", sa_dispatchid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows).toString();
    }
}