md_YOS/src/custom/service/GetOutCodeFromTQWms.java

package service;

import common.ServiceController;
import common.YosException;
import common.data.*;
import common.data.db.DBConnect;

import java.util.ArrayList;
import java.util.Calendar;

public class GetOutCodeFromTQWms extends ServiceController {
    private DBConnect TQWMS = new DBConnect("TQWMS");//谈桥立体仓库中间服务

    @Override
    public void serviceRun() throws Exception {
        getOutCode();
    }

    @Override
    public ServiceParam paramSet() {
        return new ServiceParam("获取谈桥WMS出库单序列号", 1, RunType.minute);
    }

    public void getOutCode() throws YosException {
        Rows wmsrows = TQWMS.runSqlQuery("select distinct t1.billno,t2.materialcode,t3.serialno from TOutInt t1 inner join TOutDetailInt t2 on t1.OutPlanID=t2.OutPlanID inner join TOutSerialnoDetail t3 on t2.DetailID=t3.DetailID where t2.Status=2 and t2.IsDeleted=0and t1.IsDeleted=0 and year(t3.CreateDate)>=" + (Calendar.getInstance().get(Calendar.YEAR) - 1) + " and isnull(t3.status,0)=0");
        if (wmsrows.isNotEmpty()) {
            logger.info("监测到有{}个出库序列号", wmsrows.size());
            QuerySQL billCheckSQL = SQLFactory.createQuerySQL(dbConnect, "st_stockbill", "billno", "st_stockbillid", "sys_enterpriseid", "status", "iswx").setTableAlias("t1");
            billCheckSQL.addJoinTable(JOINTYPE.left, "st_stockbill_items_sku", "t2", "t1.st_stockbillid=t2.st_stockbillid", "sku");
            billCheckSQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t1.sys_enterpriseid=t3.sys_enterpriseid", "entity");
            billCheckSQL.setWhere("t1.siteid", "MD").setWhere("t1.billno", wmsrows.toArrayList("billno"));
            RowsMap billRowsMap = billCheckSQL.query().toRowsMap("billno");

            QuerySQL codeQuery = SQLFactory.createQuerySQL(dbConnect, "sa_itemsku", "sku", "stockid", "itemid").setTableAlias("t1");
            codeQuery.addJoinTable(JOINTYPE.inner, "st_stock", "t2", "stockid=:stockid");
            codeQuery.addJoinTable(JOINTYPE.inner, "plm_item", "t3", "t1.itemid=t3.itemid", "stockno");
            codeQuery.setWhere("t1.sku", wmsrows.toArrayList("serialno")).setWhere("t2.stockno", new String[]{"101", "103"});
            RowsMap codeRowsMap = codeQuery.query().toRowsMap("sku");

            RowsMap stockRowsMap = SQLFactory.createQuerySQL(dbConnect, "st_stock", "stockid", "stockno").query().toRowsMap("stockno");


            RowsMap wmsbillnoMap = wmsrows.toRowsMap("billno");
            for (String billno : wmsbillnoMap.keySet()) {
                logger.info("开始处理单据{}", billno);
                Row billrow = billRowsMap.get(billno).get(0);
                long st_stockbillid = billrow.getLong("st_stockbillid");
                long sys_enterpriseid = billrow.getLong("sys_enterpriseid");
                String entity = billrow.getString("entity");
                String status = billrow.getString("status");
                boolean iswx = billrow.getBoolean("iswx");


                for (Row row : wmsbillnoMap.get(billno)) {
                    String sku = row.getString("serialno");
                    logger.info("开始处理序列号{}", sku);
                    if (!billRowsMap.containsKey(billno)) {
                        //单据不存在
                        TQWMS.runSqlUpdate("update TOutSerialnoDetail set status=-1 where BillNo='" + billno + "' and SerialNo='" + sku + "'");
                        logger.info("E-订单单据{}不存在,将中间表状态改为-1", billno);
                        continue;
                    }
                    if (billRowsMap.containsKey(billno) && billRowsMap.get(billno).toArrayList("sku").contains(sku)) {
                        //出库单序列号已存在
                        TQWMS.runSqlUpdate("update TOutSerialnoDetail set status=1 where BillNo='" + billno + "' and SerialNo='" + sku + "'");
                        logger.info("E-订单单据{}，序列号{}已存在,将中间表状态改为1", billno, sku);
                        continue;
                    }
                    if (!codeRowsMap.containsKey(sku)) {
                        //序列号不存在或者不在101-103仓库中
                        logger.info("E-订单序列号{}不存在于仓库101或103中", sku);
                        continue;
                    }

                    Row coderow = codeRowsMap.get(sku).get(0);

                    long stockid = entity.equals("实业") ? stockRowsMap.get(coderow.getString("stockno")).get(0).getLong("stockid") : stockRowsMap.get("101").get(0).getLong("stockid");
                    long itemid = coderow.getLong("itemid");

                    ArrayList<Long> stockbill_itemsids = dbConnect.runSqlQuery("select t.st_stockbill_itemsid,sum(t.qty)as qty,sum(t.skucount)as skucount from (\n" +
                            "select st_stockbill_itemsid,qty,0 as skucount from st_stockbill_items t1 where st_stockbillid=" + st_stockbillid + " and wmsuploadflag=2 and itemid=" + itemid + "\n" +
                            "union all\n" +
                            "select st_stockbill_itemsid,0,1 as skucount from st_stockbill_items_sku where st_stockbillid=" + st_stockbillid + " and itemid=" + itemid + " and ifnull(st_stockbill_itemsid,0)>0 \n" +
                            ")t group by t.st_stockbill_itemsid having sum(t.qty)>sum(t.skucount)").toArrayList("st_stockbill_itemsid", new ArrayList<>());

                    SQLDump sqlDump = new SQLDump();
                    //创建入库单序列号
                    InsertSQL stStockbillItemsSku = SQLFactory.createInsertSQL(dbConnect, "st_stockbill_items_sku");
                    stStockbillItemsSku.setValue("siteid", "MD");
                    stStockbillItemsSku.setValue("createby", "WMS");
                    stStockbillItemsSku.setValue("itemid", itemid);
                    stStockbillItemsSku.setValue("st_stockbillid", st_stockbillid);
                    if (!stockbill_itemsids.isEmpty()) {
                        stStockbillItemsSku.setValue("st_stockbill_itemsid", stockbill_itemsids.get(0));
                    }
                    stStockbillItemsSku.setValue("stockid", stockid);
                    stStockbillItemsSku.setValue("sku", sku);
                    sqlDump.add(stStockbillItemsSku);
                    if (status.equals("审核")) {
                        //如果表单状态是审核的，则更新序列号表
                        UpdateSQL saItemsku = SQLFactory.createUpdateSQL(dbConnect, "sa_itemsku");
                        saItemsku.setValue("stockid", iswx ? stockRowsMap.get("00").get(0).getLong("stockid") : "null");//如果是网销销售出库单，则仓库置为00
                        saItemsku.setValue("sys_enterpriseid", sys_enterpriseid);
                        saItemsku.setWhere("sku", sku).setWhere("ifnull(stockid,0)>0");
                        sqlDump.add(saItemsku);
                    }
                    try {
                        sqlDump.commit();
                        TQWMS.runSqlUpdate("update TOutSerialnoDetail set status=1 where BillNo='" + billno + "' and SerialNo='" + sku + "'");
                    } catch (Exception e) {
                        logger.error("处理单据{}，序列号{}出错", billno, sku, e);
                    }
                }
                try {
                    //判断关闭中间表状态
                    Rows skucountRows = dbConnect.runSqlQuery("select sum(1) as skucount,sum(case when createby='WMS' then 1 else 0 end ) as wmsskucount from st_stockbill_items_sku where st_stockbillid=" + st_stockbillid);
                    Rows skuitemcountRows = dbConnect.runSqlQuery("select sum(qty) as totalcount,sum(case when t1.wmsuploadflag=2 then qty else 0 end ) as wmscount from st_stockbill_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t2.skucontrol=1 where t1.st_stockbillid=" + st_stockbillid);
                    if (skucountRows.isNotEmpty() && skuitemcountRows.isNotEmpty()) {
                        int skucount = skucountRows.get(0).getInteger("skucount");//已收集的序列号数
                        int wmsskucount = skucountRows.get(0).getInteger("wmsskucount");//从立库收集的序列号数
                        int totalcount = skuitemcountRows.get(0).getInteger("totalcount");//出库总数
                        int wmscount = skuitemcountRows.get(0).getInteger("wmscount");//已下发立库的总数
                        if (wmsskucount >= wmscount || totalcount >= skucount) {
                            //当从立库收集的序列号数大于等于已下发立库的总数时或者出库总数大于等于已收集的序列号数时，表示本次立库收集完成
                            ArrayList<String> sqlist = new ArrayList<>();
                            sqlist.add("update TOutInt set status=10 ,ModifyDate=getdate() where BillNo='" + billno + "'");
                            sqlist.add("update TOutDetailInt set status=10,ModifyDate=getdate() where BillNo='" + billno + "'");
                            sqlist.add("update TOutInt set Status=10 where OutPlanID in (select OutPlanID  from TOutDetailInt where Status=2 group by OutPlanID having sum(PlanOutQuantity)=0)");
                            sqlist.add("update TOutDetailInt set Status=10 where OutPlanID in (select OutPlanID  from TOutDetailInt where Status=2 group by OutPlanID having sum(PlanOutQuantity)=0)");
                            TQWMS.runSqlUpdate(sqlist);
                        }
                    }
                } catch (Exception e) {
                    logger.error("关闭中间表状态{}出错", billno, e);
                }
            }
        }
    }
}