md_YOS/src/custom/restcontroller/webmanage/lsak3/stockbill/stockbill.java

package restcontroller.webmanage.lsak3.stockbill;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.datacontrllog.DataContrlLog;
import beans.parameter.Parameter;
import beans.remind.Remind;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
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.OrderItemsHelper;
import service.AftersalesmagAutoRecheckService;

import javax.swing.text.StyledEditorKit;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class stockbill extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public stockbill(JSONObject content) throws YosException {
        super(content);
    }

    private static Logger logger = Logger.getLogger(stockbill.class);

    /**
     * 更新YOS销售出库单
     *
     * @return
     * @throws YosException
     */
    @API(title = "更新YOS销售出库单", apiversion = R.ID20230717144803.v1.class, accesstoken = false)
    public String updateIcstockbill() throws YosException {
        String msg = "";
        int code = 1;
        ArrayList<String> sqlList = new ArrayList<>();
        // 单据编号
        String fbillnum = content.getString("fbillnum");
        try {
            // 经销商编号
            String fagentnum = content.getString("fagentnum");
            long sys_enterpriseid = 0;
            if (dbConnect.runSqlQuery("select sys_enterpriseid from sa_agents where siteid='lsa' and  agentnum='" + fagentnum + "'").isNotEmpty()) {
                sys_enterpriseid = dbConnect.runSqlQuery("select sys_enterpriseid from sa_agents where siteid='lsa' and  agentnum='" + fagentnum + "'").get(0).getLong("sys_enterpriseid");
            } else {
                msg = "yos中不存在【" + fagentnum + "】经销商";
                return getErrReturnObject().setErrMsg(msg).toString();
            }
            // 单据日期
            String fdate = content.getString("fdate");
            // 会计期间
            //  String fperiod = content.getString("fperiod");
            // 红蓝字
            String frb = content.getString("frb");
            // 表头仓库
            // String fstockno = content.getString("fstockno");
            // 单据类型明细
            //String ftypemx = content.getString("ftypemx");
            // 单据状态
            String fstatus = content.getString("fstatus");
            // 制单人
            String createby = content.getString("createby");
            // 制单日期
            String createdate = content.getString("createdate");
            // 审核人
            String checkby = content.getString("checkby");
            // 审核日期
            String checkdate = content.getString("checkdate");
            // 备注
            String fnotes = content.getString("fnotes");
            // 是否删除
            String isdelete = content.getString("isdelete");

            siteid = "lsa";

            Long sa_cashbillid = 0L;

            Rows stockbillRows = dbConnect.runSqlQuery("select * from st_stockbill where siteid='lsa' and status='审核' and billno='" + fbillnum + "'");
            Long st_stockbillid = 0L;
            if (stockbillRows.isEmpty() && "0".equals(isdelete) && "审核".equals(fstatus)) {
                /**
                 * DRP不存在销售出库单，且中间表状态为审核且不在删除状态时，DRP中需新增该销售出库单
                 */
                SQLFactory sqlFactory = new SQLFactory(this, "销售出库单新增");
                st_stockbillid = createTableID("st_stockbill");
                sqlFactory.addParameter("siteid", "lsa");
                sqlFactory.addParameter("st_stockbillid", st_stockbillid);
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                sqlFactory.addParameter("billno", fbillnum);
                sqlFactory.addParameter("type", "销售出库");
                sqlFactory.addParameter("rb", frb.equals("蓝") ? 1 : 0);
                sqlFactory.addParameter("billdate", fdate);
                sqlFactory.addParameter("createby", createby);
                sqlFactory.addParameter("createdate", getDate(createdate));
                sqlFactory.addParameter("changeby", createby);
                sqlFactory.addParameter("changedate", getDate(createdate));
                sqlFactory.addParameter("checkby", checkby);
                sqlFactory.addParameter("checkdate", getDate(checkdate));
                sqlFactory.addParameter("remarks", fnotes);
                sqlList.add(sqlFactory.getSQL());

                JSONArray detailarray = content.getJSONArray("icstockbilldetails");
                StringBuffer str = new StringBuffer();

                List<Long> sa_dispatch_itemsids = new ArrayList<>();
                sa_dispatch_itemsids.add(0l);
                for (Object object : detailarray) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (str.toString().equals("")) {
                        str.append("'" + jsonObject.getString("fitemno") + "'");
                    } else {
                        str.append(",'" + jsonObject.getString("fitemno") + "'");
                    }
                    sa_dispatch_itemsids.add(jsonObject.getLong("sainvoicedetailid"));
                }
                Rows rows;
                if (!str.toString().equals("")) {
                    rows = dbConnect.runSqlQuery("select * from plm_item  where siteid='lsa' and itemno in " + "(" + str.toString() + ")");
                } else {
                    rows = dbConnect.runSqlQuery("select * from plm_item  where siteid='lsa' and itemno in('')");
                }
                RowsMap itemRowsMap = rows.toRowsMap("itemno");
                Rows distiptchRows = dbConnect.runSqlQuery("select t2.price,t1.sa_dispatch_itemsid,t1.sa_orderitemsid,t2.sa_orderid,t3.sys_enterpriseid,t4.sonum,t5.itemno,t5.itemname from sa_dispatch_items t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid inner join sa_dispatch t3 on t1.sa_dispatchid=t3.sa_dispatchid and t1.siteid=t3.siteid left join sa_order t4 on t2.sa_orderid=t4.sa_orderid and t2.siteid=t4.siteid left join plm_item t5 on t1.itemid=t5.itemid and t1.siteid=t5.siteid where t3.status in('审核','复核','关闭') and  t1.siteid='lsa' and  t1.sa_dispatch_itemsid in " + sa_dispatch_itemsids.toString().replace("[", "(").replace("]", ")"));
                RowsMap distiptchRowsMap = distiptchRows.toRowsMap("sa_dispatch_itemsid");

                Rows aftersalesmagRows = dbConnect.runSqlQuery("select t1.price,t1.sa_aftersalesmag_itemsid,t1.sa_orderitemsid,t2.sa_orderid from sa_aftersalesmag_items t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid  where  t1.siteid='lsa' and  t1.sa_aftersalesmag_itemsid in " + sa_dispatch_itemsids.toString().replace("[", "(").replace("]", ")"));
                RowsMap aftersalesmagRowsMap = aftersalesmagRows.toRowsMap("sa_aftersalesmag_itemsid");
                BigDecimal amountTotal = BigDecimal.ZERO;
                for (Object object : detailarray) {
                    JSONObject jsonObject = (JSONObject) object;
                    String frownum = jsonObject.getString("frownum");
                    String fitemno = jsonObject.getString("fitemno");
                    System.err.println(frownum + ":" + fitemno);
                    BigDecimal fqty = new BigDecimal(jsonObject.getString("fqty"));
                    fqty = fqty.compareTo(BigDecimal.ZERO) < 0 ? fqty.negate() : fqty;
                    //String fdetailstockno = jsonObject.getString("fstockno");
                    long sainvoicedetailid = jsonObject.getLong("sainvoicedetailid");
                    //String fbatchno = jsonObject.getString("fbatchno");
                    String fdetailnotes = jsonObject.getString("fnotes");
                    SQLFactory sqlFactorydetail = new SQLFactory(this, "销售出库单明细新增");
                    sqlFactorydetail.addParameter("siteid", "lsa");
                    sqlFactorydetail.addParameter("st_stockbill_itemsid", createTableID("st_stockbill_items"));
                    sqlFactorydetail.addParameter("st_stockbillid", st_stockbillid);
                    sqlFactorydetail.addParameter("rowno", frownum);
                    sqlFactorydetail.addParameter("sa_dispatch_itemsid", sainvoicedetailid);
                    sqlFactorydetail.addParameter("remarks", fnotes);
                    if (itemRowsMap.containsKey(fitemno) && itemRowsMap.get(fitemno).isNotEmpty()) {
                        sqlFactorydetail.addParameter("itemid", itemRowsMap.get(fitemno).get(0).getLong("itemid"));
                    } else {
                        sqlFactorydetail.addParameter("itemid", 0);
                    }
                    sqlFactorydetail.addParameter("remarks", fdetailnotes);
                    sqlFactorydetail.addParameter("qty", fqty);

                    if (frb.equals("蓝")) {
                        if (distiptchRowsMap.containsKey(String.valueOf(sainvoicedetailid)) && distiptchRowsMap.get(sainvoicedetailid).isNotEmpty()) {
                            sqlFactorydetail.addParameter("price", distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price"));
                            amountTotal = amountTotal.add(fqty.multiply(distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price")));
                            sqlFactorydetail.addParameter("amount", fqty.multiply(distiptchRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price")));
                            sqlFactorydetail.addParameter("sa_orderitemsid", distiptchRowsMap.get(sainvoicedetailid).get(0).getLong("sa_orderitemsid"));
                            sqlFactorydetail.addParameter("sa_orderid", distiptchRowsMap.get(sainvoicedetailid).get(0).getLong("sa_orderid"));
                            System.out.println(amountTotal);
                            //您的订单【XXX】商品【XXX】工厂已发货，出库单号【XXX】
                            String message = "您的订单【" + distiptchRowsMap.get(sainvoicedetailid).get(0).getString("sonum") + "】商品【" + distiptchRowsMap.get(sainvoicedetailid).get(0).getString("itemname") + "】工厂已发货，出库单号【" + fbillnum + "】！";
                            sendMsg(message, st_stockbillid, distiptchRowsMap.get(sainvoicedetailid).get(0).getLong("sys_enterpriseid"));
                        } else {
                            sqlFactorydetail.addParameter("price", 0);
                            sqlFactorydetail.addParameter("amount", 0);
                            sqlFactorydetail.addParameter("sa_orderitemsid", 0);
                            sqlFactorydetail.addParameter("sa_orderid", 0);
                        }

                        /**
                         * 更新发货单数量
                         */
                        sqlList.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=ifnull(outwarehouseqty,0)+" + fqty + " where siteid='lsa' and sa_dispatch_itemsid=" + sainvoicedetailid);
                    } else {
                        if (aftersalesmagRowsMap.containsKey(String.valueOf(sainvoicedetailid)) && aftersalesmagRowsMap.get(sainvoicedetailid).isNotEmpty()) {
                            logger.info(aftersalesmagRowsMap.get(sainvoicedetailid).get(0).toJsonObject().toString());
                            sqlFactorydetail.addParameter("price", aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price"));
                            sqlFactorydetail.addParameter("amount", fqty.multiply(aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("price")));
                            logger.info(aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("sa_orderitemsid"));
                            logger.info(aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getBigDecimal("sa_orderid"));
                            sqlFactorydetail.addParameter("sa_orderitemsid", aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getLong("sa_orderitemsid"));
                            sqlFactorydetail.addParameter("sa_orderid", aftersalesmagRowsMap.get(sainvoicedetailid).get(0).getLong("sa_orderid"));
                        } else {
                            sqlFactorydetail.addParameter("price", 0);
                            sqlFactorydetail.addParameter("amount", 0);
                            sqlFactorydetail.addParameter("sa_orderitemsid", 0);
                            sqlFactorydetail.addParameter("sa_orderid", 0);
                        }
                    }

                    sqlList.add(sqlFactorydetail.getSQL());
                }
                if (frb.equals("蓝")) {
                    BigDecimal billamount = BigDecimal.ZERO;
                    //返利比例
                    String icstockbillrebateaccount = Parameter.getString(siteid, "icstockbillrebateaccount"); //经销商销售出库单返利账户
                    String icstockbillrebaterate1 = Parameter.getString(siteid, "icstockbillrebaterate"); //销售出库单返利比例
                    if (!StringUtils.isBlank(icstockbillrebateaccount) && !StringUtils.isBlank(icstockbillrebaterate1)) {
                        BigDecimal icstockbillrebaterate = new BigDecimal(icstockbillrebaterate1);
                        //经销商返利
                        BigDecimal rebateamount = icstockbillrebaterate.multiply(amountTotal);
                        System.out.println(rebateamount);
                        CashbillEntity entity = new CashbillEntity();
                        entity.setAmount(rebateamount);
                        entity.setOwnerid(st_stockbillid);
                        entity.setOwnertable("st_stockbill");
                        entity.setSource("");
                        entity.setRemarks("销售出库单" + fbillnum + "返利");
                        JSONObject cashbillIncome = Accountbalance.createCashbillIncome(this, sys_enterpriseid, Long.valueOf(icstockbillrebateaccount).longValue(), entity, true, false);
                        sqlList.addAll(cashbillIncome.getJSONArray("sqlList").toJavaList(String.class));
                        sa_cashbillid = cashbillIncome.getLong("sa_cashbillid");
                    }


                }

                sqlList.add("update st_stockbill set status='审核',checkby='" + checkby + "',checkdate=CURRENT_TIMESTAMP where st_stockbillid = " + st_stockbillid + " and siteid='lsa'");

            } else if (!stockbillRows.isEmpty() && "0".equals(isdelete) && "审核".equals(fstatus)) {
                /**
                 * DRP存在销售出库单，且中间表状态为审核且不在删除状态时，DRP中需将当前销售出库单先反审核，修改内容后再进行审核
                 */
                //销售出库单反审核逻辑待添加
                if (frb.equals("蓝")) {
                    BigDecimal billamount = BigDecimal.ZERO;
                    Rows stockbilldetails = dbConnect.runSqlQuery("select * from st_stockbill_items where st_stockbillid ='"
                            + stockbillRows.get(0).getLong("st_stockbillid") + "' and  siteid='" + siteid + "'");
                    for (Row row : stockbilldetails) {
                        billamount = billamount.add(row.getBigDecimal("amount").abs().negate());
                    }

                    //返利比例
                    String icstockbillrebateaccount = Parameter.getString(siteid, "icstockbillrebateaccount"); //经销商销售出库单返利账户
                    String icstockbillrebaterate1 = Parameter.getString(siteid, "icstockbillrebaterate"); //销售出库单返利比例
                    BigDecimal icstockbillrebaterate = new BigDecimal(icstockbillrebaterate1);
                    //经销商返利
                    BigDecimal rebateamount = icstockbillrebaterate.multiply(billamount);

                    CashbillEntity entity = new CashbillEntity();
                    entity.setAmount(rebateamount);
                    entity.setOwnerid(stockbillRows.get(0).getLong("st_stockbillid"));
                    entity.setOwnertable("st_stockbill");
                    entity.setSource("");
                    entity.setRemarks("销售出库单" + fbillnum + "反返利");
                    JSONObject cashbillIncome = Accountbalance.createCashbillIncome(this, sys_enterpriseid, Long.valueOf(icstockbillrebateaccount).longValue(), entity, true, false);
                    sqlList.addAll(cashbillIncome.getJSONArray("sqlList").toJavaList(String.class));
                    sa_cashbillid = cashbillIncome.getLong("sa_cashbillid");
                    /**
                     * 更新发货单数量
                     */
                    Rows stockbilldetailrows = dbConnect.runSqlQuery("select t1.sa_dispatch_itemsid,sum(t1.qty) qty,t2.sa_dispatchid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid where t1.st_stockbillid = " + stockbillRows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa' group by t1.sa_dispatch_itemsid");
                    for (Row row : stockbilldetailrows) {
                        sqlList.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=ifnull(outwarehouseqty,0)-" + row.getBigDecimal("qty") + " where siteid='lsa' and sa_dispatch_itemsid=" + row.getLong("sa_dispatch_itemsid"));
                        sqlList.add("update sa_dispatch set closedate=null,closeby='',status='复核' where siteid='lsa' and sa_dispatchid=" + row.getLong("sa_dispatchid"));
                    }
                }

                sqlList.add("update st_stockbill set status='新建',checkby='',checkdate=null where st_stockbillid = " + stockbillRows.get(0).getLong("st_stockbillid") + " and siteid='lsa'");

                msg = "DRP单据状态为审核，现已反审核，下次同步时进行审核操作";
                code = 0;
            } else if (!stockbillRows.isEmpty() && ("1".equals(isdelete) || "新建".equals(fstatus))) {
                /**
                 * DRP存在收入凭证，且中间表状态为新建或在删除状态时，DRP中需将当前收入销售出库单先反审核
                 */
                //销售出库单反审核逻辑待添加
                if (frb.equals("蓝")) {
                    BigDecimal billamount = BigDecimal.ZERO;
                    Rows stockbilldetails = dbConnect.runSqlQuery("select * from st_stockbill_items where st_stockbillid ='"
                            + stockbillRows.get(0).getLong("st_stockbillid") + "' and  siteid='" + siteid + "'");
                    for (Row row : stockbilldetails) {
                        billamount = billamount.add(row.getBigDecimal("amount").abs().negate());
                    }

                    //返利比例
                    String icstockbillrebateaccount = Parameter.getString(siteid, "icstockbillrebateaccount"); //经销商销售出库单返利账户
                    String icstockbillrebaterate1 = Parameter.getString(siteid, "icstockbillrebaterate"); //销售出库单返利比例
                    BigDecimal icstockbillrebaterate = new BigDecimal(icstockbillrebaterate1);
                    //经销商返利
                    BigDecimal rebateamount = icstockbillrebaterate.multiply(billamount);

                    CashbillEntity entity = new CashbillEntity();
                    entity.setAmount(rebateamount);
                    entity.setOwnerid(stockbillRows.get(0).getLong("st_stockbillid"));
                    entity.setOwnertable("st_stockbill");
                    entity.setSource("");
                    entity.setRemarks("销售出库单" + fbillnum + "反返利");
                    JSONObject cashbillIncome = Accountbalance.createCashbillIncome(this, sys_enterpriseid, Long.valueOf(icstockbillrebateaccount).longValue(), entity, true, false);
                    sqlList.addAll(cashbillIncome.getJSONArray("sqlList").toJavaList(String.class));
                    sa_cashbillid = cashbillIncome.getLong("sa_cashbillid");
                    /**
                     * 更新发货单数量
                     */
                    Rows stockbilldetailrows = dbConnect.runSqlQuery("select t1.sa_dispatch_itemsid,sum(t1.qty) qty,t2.sa_dispatchid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid where t1.st_stockbillid = " + stockbillRows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa' group by t1.sa_dispatch_itemsid");
                    for (Row row : stockbilldetailrows) {
                        sqlList.add("update sa_dispatch_items set outwarehousedate=CURRENT_TIME,outwarehouseqty=ifnull(outwarehouseqty,0)-" + row.getBigDecimal("qty") + " where siteid='lsa' and sa_dispatch_itemsid=" + row.getLong("sa_dispatch_itemsid"));
                        sqlList.add("update sa_dispatch set closedate=null,closeby='',status='复核' where siteid='lsa' and sa_dispatchid=" + row.getLong("sa_dispatchid"));
                    }


                }

                sqlList.add("update st_stockbill set status='新建',checkby='',checkdate=null where st_stockbillid = " + stockbillRows.get(0).getLong("st_stockbillid") + " and siteid='lsa'");

            }

            dbConnect.runSqlUpdate(sqlList);


            Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
            if (stockbillRows.isEmpty() && "0".equals(isdelete) && "审核".equals(fstatus)) {
                ArrayList<Long> userIdList = dbConnect.runSqlQuery("select userid from sys_enterprise_hr where siteid='" + siteid + "' and sys_enterpriseid=" + sys_enterpriseid).toArrayList("userid", new ArrayList<>());
                Executor.sendEml(this, "stocck_check", st_stockbillid, siteid, userIdList);
            }

            Rows stockbillrows = dbConnect.runSqlQuery("select st_stockbillid,status,rb from st_stockbill where billno='" + fbillnum + "' and siteid='lsa'");
            if (frb.equals("蓝")) {
                //查询可关闭的发货单
                Rows dispatchrows = dbConnect.runSqlQuery("select t.sa_dispatchid from (select sa_dispatchid,sum(qty) sumqty,sum(ifnull(outwarehouseqty,0)) sumoutwarehouseqty from sa_dispatch_items where sa_dispatchid in(select sa_dispatchid from sa_dispatch_items where sa_dispatch_itemsid in(select t1.sa_dispatch_itemsid from st_stockbill_items t1 inner join st_stockbill t2 on t1.st_stockbillid=t2.st_stockbillid and t1.siteid=t2.siteid where t1.st_stockbillid =" + stockbillrows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa')) group by sa_dispatchid)t where t.sumqty=t.sumoutwarehouseqty");
                Rows undispatchrows = dbConnect.runSqlQuery("select t.sa_dispatchid from (select sa_dispatchid,sum(qty) sumqty,sum(ifnull(outwarehouseqty,0)) sumoutwarehouseqty from sa_dispatch_items where sa_dispatchid in(select sa_dispatchid from sa_dispatch_items where sa_dispatch_itemsid in(select t1.sa_dispatch_itemsid from st_stockbill_items t1 inner join st_stockbill t2 on t1.st_stockbillid=t2.st_stockbillid and t1.siteid=t2.siteid where t1.st_stockbillid =" + stockbillrows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa')) group by sa_dispatchid)t where t.sumqty!=t.sumoutwarehouseqty");
                ArrayList<String> sqldispatch = new ArrayList<>();
                for (Row row : dispatchrows) {
                    sqldispatch.add("update sa_dispatch set status='关闭',closedate=current_time,closeby='admin' where sa_dispatchid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa'");
                    SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                    sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                    sqlFactory.addParameter("ownertable", "sa_dispatch");
                    sqlFactory.addParameter("ownerid", row.getLong("sa_dispatchid"));
                    sqlFactory.addParameter("action", "自动关闭");
                    sqlFactory.addParameter("remarks", "发货单自动关闭成功");
                    sqlFactory.addParameter("actionuserid", "1");
                    sqlFactory.addParameter("actionby", "admin");
                    sqlFactory.addParameter("siteid", "lsa");
                    sqldispatch.add(sqlFactory.getSQL());
                }
                for (Row row : undispatchrows) {
                    sqldispatch.add("update sa_dispatch set status='复核',closedate=null,closeby='' where sa_dispatchid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa'");
                    sqldispatch.add("delete from sys_contrllog where ownertable='sa_dispatch' and action='自动关闭' and ownerid='" + row.getLong("sa_dispatchid") + "' and siteid='lsa'");
                }

                dbConnect.runSqlUpdate(sqldispatch);

                //查询可关闭的订单
                //订单出库总数量
                Rows orderinstockrows = dbConnect.runSqlQuery("select t1.sa_orderid,t1.siteid,sum(ifnull(t2.outwarehouseqty,0)) sumoutwarehouseqty from sa_orderitems t1 left join sa_dispatch_items t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where  t1.sa_orderid in(select t3.sa_orderid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid inner join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid  where t1.st_stockbillid =" + stockbillrows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa')   group by t1.sa_orderid,t1.siteid");
                //订单总数量
                Rows ordersumrows = dbConnect.runSqlQuery("select t1.sa_orderid,t1.siteid,sum(qty) sumqty from sa_orderitems t1  where  t1.sa_orderid in(select t3.sa_orderid from st_stockbill_items t1 inner join  sa_dispatch_items t2 on t1.sa_dispatch_itemsid=t2.sa_dispatch_itemsid and t1.siteid=t2.siteid inner join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid  where t1.st_stockbillid =" + stockbillrows.get(0).getLong("st_stockbillid") + " and t1.siteid='lsa')   group by t1.sa_orderid,t1.siteid");
                RowsMap orderinstockRowsMap = orderinstockrows.toRowsMap("sa_orderid");


                ArrayList<String> sqlorder = new ArrayList<>();
                ArrayList<Long> sa_orderids = new ArrayList<>();
                for (Row row : ordersumrows) {
                    if (orderinstockRowsMap.containsKey(row.getString("sa_orderid"))) {
                        if (orderinstockRowsMap.get(row.getString("sa_orderid")).get(0).getBigDecimal("sumoutwarehouseqty").compareTo(row.getBigDecimal("sumqty")) == 0) {
                            sqlorder.add("update sa_order set status='关闭',closedate=current_time,closeby='admin' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa'");
                            SQLFactory sqlFactory = new SQLFactory(this, "数据操作日志新增");
                            sqlFactory.addParameter("sys_contrllogid", createTableID("sys_contrllog"));
                            sqlFactory.addParameter("ownertable", "sa_order");
                            sqlFactory.addParameter("ownerid", row.getLong("sa_orderid"));
                            sqlFactory.addParameter("action", "自动关闭");
                            sqlFactory.addParameter("remarks", "订单自动关闭成功");
                            sqlFactory.addParameter("actionuserid", "1");
                            sqlFactory.addParameter("actionby", "admin");
                            sqlFactory.addParameter("siteid", "lsa");
                            sqlorder.add(sqlFactory.getSQL());
                            sa_orderids.add(row.getLong("sa_orderid"));
                        } else {
                            sqlorder.add("update sa_order set status='审核',closedate=null,closeby='' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa'");
                            sqlorder.add("delete from sys_contrllog  where ownertable='sa_order' and action='自动关闭'  and  ownerid='" + row.getLong("sa_orderid") + "' and siteid='lsa'");
                        }
                    } else {
                        sqlorder.add("update sa_order set status='审核',closedate=null,closeby='' where sa_orderid='" + row.getLong("sa_orderid") + "' and siteid='lsa'");
                        sqlorder.add("delete from sys_contrllog  where ownertable='sa_order' and action='自动关闭'  and  ownerid='" + row.getLong("sa_orderid") + "' and siteid='lsa'");
                    }

                }
                dbConnect.runSqlUpdate(sqlorder);


                //是否自动返利结算
                OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
                for (Long sa_orderid : sa_orderids) {
                    orderItemsHelper.executeAutoRebate(sa_orderid, sys_enterpriseid);
                }


            }

            /**
             * 如单据为新建状态，则，先把单据强行删除掉
             */
            dbConnect.runSqlUpdate("delete from st_stockbill where status='新建'");

        } catch (Exception e) {
            e.printStackTrace();
            msg = e.getMessage();
            if (msg == null) {
                msg = "";
            }
            code = 0;
            dbConnect.runSqlUpdate("delete from st_stockbill_items where  st_stockbillid in(select st_stockbillid from st_stockbill where siteid='lsa' and billno='" + fbillnum + "')");
            dbConnect.runSqlUpdate("delete from st_stockbill where  billno='" + fbillnum + "' and siteid='lsa'");

        }
        JSONObject returnObject = new JSONObject();
        returnObject.put("msg", msg);
        returnObject.put("code", code);
        return returnObject.toString();
    }

    public String getDate(String date) {
        try {
            Date a = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(date);
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(a);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void sendMsg(String content, Long st_stockbillid, Long sys_enterpriseid) throws YosException {

        ArrayList<Long> userList = getEnterpriseHrs(sys_enterpriseid).toArrayList("userid", new ArrayList<>());
        Remind remind = new Remind(this);
        remind.setTitle("销售出库单消息");
        remind.setContent(content);
        remind.setType("应用");
        remind.setObjectid(st_stockbillid);
        remind.setObjectname("st_stockbill");
        remind.setTouserid(userList);
        remind.sendByDialogMsg().createSys_message();
    }
}