md_YOS/src/custom/service/GetStockBillFromU8.java

package service;

import beans.stockbill.Stockbill;
import common.ServiceController;
import common.YosException;
import common.data.*;
import common.data.db.DBConnect;

import java.util.ArrayList;

/**
 * 美大U8erp出入库单同步
 */

public class GetStockBillFromU8 extends ServiceController {
    private DBConnect YUNl_DB = new DBConnect("U8-YUNl_DB");

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

    @Override
    public ServiceParam paramSet() {
        return new ServiceParam("获取U8入库单（产品入库）", 1, RunType.minute);
    }

    /**
     * 产品入库单
     */
    public void getProdIN() throws YosException {
        //产品入库表头
        Rows u8billRows = YUNl_DB.runSqlQuery("select top(50) cvencode,ccode,ddate,cwhcode,cmemo,id,dnverifytime,chandler from rdrecord where status=0 and billtype='10' and dnverifytime is not null and accid in('666','888') and  isnull(errtimes,0)<=5 order by errtimes");
        if (u8billRows.isNotEmpty()) {
            logger.info("监测到有{}个u8产品入库单", u8billRows.size());
            //产品入库表体
            Rows u8billmxAllRows = SQLFactory.createQuerySQL(YUNl_DB, "RdRecords", "autoid,cinvcode,iquantity,iunitcost,iprice,cbmemo,cbatch").setWhere("billtype", "10").setWhere("accid in('666','888')").setWhere("id", u8billRows.toArrayList("id")).query();
            RowsMap u8billmxRowsMap = u8billmxAllRows.toRowsMap("id");
            //产品入库序列号
            RowsMap u8billmxCodeRowsMap = SQLFactory.createQuerySQL(YUNl_DB, "ST_SNDetail", "autoid,ivouchsid,ivouchid,irowno,cinvcode,cwhcode,cposition,cinvsn,cbatch").setWhere("billtype", "10").setWhere("accid in('666','888')").setWhere("ivouchid", u8billRows.toArrayList("id")).setWhere("ivouchsid", u8billmxAllRows.toArrayList("autoid")).query().toRowsMap("ivouchid");

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

            RowsMap itemRowsMap = SQLFactory.createQuerySQL(dbConnect, "plm_item", "itemid", "itemno").setWhere("itemno", u8billmxAllRows.toArrayList("cinvcode")).query().toRowsMap("itemno");

            RowsMap unitRowsMap = SQLFactory.createQuerySQL(dbConnect, "plm_unit", "unitid", "unitname").query().toRowsMap("unitid");

            RowsMap departmentRowsMap = SQLFactory.createQuerySQL(dbConnect, "sys_department", "departmentid", "depno").query().toRowsMap("depno");

            ArrayList<String> drpbillList = SQLFactory.createQuerySQL(dbConnect, "st_stockbill", "billno").setWhere("siteid", "MD").setWhere("status", "审核").setWhere("typemx", "生产入库u8").setWhere("billno", u8billRows.toArrayList("ccode")).query().toArrayList("billno");

            foreachbill:
            for (Row cInvCodeRow : u8billRows) {
                String id = cInvCodeRow.getString("id");
                try {
                    String ccode = cInvCodeRow.getString("ccode");

                    if (drpbillList.contains(ccode)) {
                        //已存在审核状态的单据，则直接判定成功
                        YUNl_DB.runSqlUpdate("update RdRecord set status=1  where ID =" + id + " and billtype='10'");
                        logger.info("入库单{}已存在,将中间表状态改为1", ccode);
                        continue;
                    }
                    String ddate = getDate_Str(cInvCodeRow.getDate("ddate"));
                    if (dbConnect.runSqlQuery("select * from st_period where isclose=0 and begdate<='" + ddate + "' and enddate>='" + ddate + "'").isEmpty()) {
                        //会计期间不存在，直接判定失败
                        YUNl_DB.runSqlUpdate("update RdRecord set ErrTimes=10,failreason='找不到有效的会计期间' where ID =" + id + " and billtype='10' ");
                        logger.info("入库单{}单据日期{}所在会计期间不存在", ccode, ddate);
                        continue;
                    }
                    String cwhcode = cInvCodeRow.getString("cwhcode");
                    if (stockRowsMap.get(cwhcode).isEmpty()) {
                        //仓库不存在，失败次数+1
                        YUNl_DB.runSqlUpdate("update RdRecord set ErrTimes=isnull(ErrTimes,0)+1,failreason='仓库不存在' where ID =" + id + " and billtype='10' ");
                        logger.info("入库单{}仓库{}不存在", ccode, cwhcode);
                        continue;
                    }
                    Rows u8billmxRows = u8billmxRowsMap.get(id);//获取表体

                    for (Row u8billmxRow : u8billmxRows) {
                        String cinvcode = u8billmxRow.getString("cinvcode");
                        if (itemRowsMap.get(cinvcode).isEmpty()) {
                            //商品编号不存在，失败次数+1
                            YUNl_DB.runSqlUpdate("update RdRecord set ErrTimes=isnull(ErrTimes,0)+1,failreason='商品不存在' where ID =" + id + " and billtype='10' ");
                            logger.info("入库单{}商品{}不存在", ccode, cinvcode);
                            continue foreachbill;
                        }

                    }

                    RowsMap u8billmxCodeMXRowsMap = u8billmxCodeRowsMap.get(id).toRowsMap("ivouchsid");//获取表体序列号

                    String cmemo = cInvCodeRow.getString("cmemo");
                    String dnverifytime = cInvCodeRow.getString("dnverifytime");
                    String chandler = cInvCodeRow.getString("chandler");

                    long stockid = stockRowsMap.get(cwhcode).get(0).getLong("stockid");

                    SQLDump sqlDump = new SQLDump();

                    long st_stockbillid = createTableID("st_stockbill");
                    InsertSQL stStockbill = SQLFactory.createInsertSQL(dbConnect, "st_stockbill");
                    stStockbill.setValue("st_stockbillid", st_stockbillid);
                    stStockbill.setValue("type", "其他入库");
                    stStockbill.setValue("typemx", "生产入库u8");
                    stStockbill.setValue("billno", ccode);
                    stStockbill.setValue("siteid", "MD");
                    stStockbill.setValue("departmentid", departmentRowsMap.get("103").get(0).getLong("departmentid"));
                    stStockbill.setValue("billdate", ddate);
                    stStockbill.setValue("stockid", stockid);
                    stStockbill.setValue("remarks", cmemo);
                    stStockbill.setValue("createby", "u8");
                    stStockbill.setValue("status", "新建");
                    stStockbill.setValue("rb", 1);


                    for (Row u8billmxRow : u8billmxRows) {
                        String cinvcode = u8billmxRow.getString("cinvcode");
                        String autoid = u8billmxRow.getString("autoid");
                        double iquantity = u8billmxRow.getDouble("iquantity");
//                        String iunitcost = u8billmxRow.getString("iunitcost");
//                        String iprice = u8billmxRow.getString("iprice");
                        String cbmemo = u8billmxRow.getString("cbmemo");
                        String cbatch = u8billmxRow.getString("cbatch");
                        String rowindex = u8billmxRow.getString("rowindex");

                        Row itemrow = itemRowsMap.get(cinvcode).get(0);


                        long st_stockbill_itemsid = createTableID("st_stockbill_items");
                        InsertSQL stStockbillitems = SQLFactory.createInsertSQL(dbConnect, "st_stockbill_items");
                        stStockbillitems.setValue("st_stockbill_itemsid", st_stockbill_itemsid);
                        stStockbillitems.setValue("st_stockbillid", st_stockbillid);
                        stStockbillitems.setValue("siteid", "MD");
                        stStockbillitems.setValue("rowno", rowindex);
                        stStockbillitems.setValue("itemid", itemrow.getLong("itemid"));
                        stStockbillitems.setValue("skucontrol", true);
                        stStockbillitems.setValue("itemno", itemrow.getString("itemno"));
                        stStockbillitems.setValue("itemname", itemrow.getString("itemname"));
                        stStockbillitems.setValue("model", itemrow.getString("model"));
                        if (unitRowsMap.get(itemrow.getString("unitid")).isNotEmpty()) {
                            stStockbillitems.setValue("unit", unitRowsMap.get(itemrow.getString("unitid")).get(0).getString("unitname"));
                        }
                        stStockbillitems.setValue("qty", iquantity < 0 ? -iquantity : iquantity);
                        if (!cbatch.isEmpty()) {
                            stStockbillitems.setValue("batchno", cbatch);
                        }
                        stStockbillitems.setValue("stockid", stockid);
                        stStockbillitems.setValue("remarks", cbmemo);

                        stStockbill.setValue("rb", iquantity < 0 ? "0" : "1");

                        sqlDump.add(stStockbillitems);

                        for (Row codeRow : u8billmxCodeMXRowsMap.get(autoid)) {
                            String cinvsn = codeRow.getString("cinvsn");
                            InsertSQL stStockbillItemsSku = SQLFactory.createInsertSQL(dbConnect, "st_stockbill_items_sku");
                            stStockbillItemsSku.setValue("siteid", "MD");
                            stStockbillItemsSku.setValue("createby", "u8");
                            stStockbillItemsSku.setValue("itemid", itemrow.getLong("itemid"));
                            stStockbillItemsSku.setValue("st_stockbillid", st_stockbillid);
                            stStockbillItemsSku.setValue("st_stockbill_itemsid", st_stockbill_itemsid);
                            stStockbillItemsSku.setValue("stockid", stockid);
                            stStockbillItemsSku.setValue("sku", cinvsn);
                            sqlDump.add(stStockbillItemsSku);
                        }
                    }
                    sqlDump.add(stStockbill);

                    sqlDump.commit();
                    try {
                        Stockbill.check(getSysController("MD"), st_stockbillid, true);
                        YUNl_DB.runSqlUpdate("update RdRecord set status=1,failreason='' where ID =" + id + " and billtype='10'");
                    } catch (Exception e) {
                        dbConnect.runSqlUpdate("delete from st_stockbill where st_stockbillid=" + st_stockbillid);
                        dbConnect.runSqlUpdate("delete from st_stockbill_items where st_stockbillid=" + st_stockbillid);
                        dbConnect.runSqlUpdate("delete from st_stockbill_items_sku where st_stockbillid=" + st_stockbillid);
                        YUNl_DB.runSqlUpdate("update RdRecord set ErrTimes=isnull(ErrTimes,0)+1,failreason='" + e.getMessage() + "' where ID =" + id + " and billtype='10' ");
                        logger.info("产品入库单{}审核失败", ccode, e);
                    }
                } catch (Exception e) {
                    YUNl_DB.runSqlUpdate("update RdRecord set ErrTimes=isnull(ErrTimes,0)+1,failreason='" + e.getMessage() + "' where ID =" + id + " and billtype='10' ");
                    logger.info("产品入库单ID{}同步失败", cInvCodeRow.toJsonObject(), e);
                }
            }
        }
    }

}