md_YOS/src/custom/service/GetBasicDataFromU8.java

package service;

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

import java.util.ArrayList;

/**
 * 美大U8erp数据传输自动任务
 */

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

    @Override
    public void serviceRun() throws Exception {
        getUnit();
        getItem();
        getMcode();
    }

    @Override
    public ServiceParam paramSet() {
        return new ServiceParam("获取U8基础资料（计量单位-商品档案-序列号）", 1, RunType.minute);
    }

    /**
     * 计量单位
     */
    public void getUnit() throws YosException {
        YUNl_DB.runSqlQuery("select distinct cComUnitName from ComputationUnit where status=0 and accid in('666','888')", new QueryCallBack() {
            @Override
            public void onRowLoaded(long rowindex, Row row) throws YosException {
                try {
                    String cComUnitName = row.getString("cComUnitName");
                    InsertSQL unitInsert = SQLFactory.createInsertSQL(dbConnect, "plm_unit");
                    unitInsert.setValue("siteid", "MD");
                    unitInsert.setValue("unitname", cComUnitName);
                    unitInsert.setWhere("not exists(select * from plm_unit where siteid='MD' and unitname='" + cComUnitName + "')");
                    unitInsert.insert();
                    YUNl_DB.runSqlUpdate("update ComputationUnit set status=1 where cComUnitName='" + cComUnitName + "' and accid in('666','888')");
                } catch (Exception e) {
                    logger.error("U8计量单位同步DRP失败", row.toJsonObject(), e);
                }
            }
        });
    }

    /**
     * 商品档案
     */
    public void getItem() throws YosException {
        RowsMap unitMap = SQLFactory.createQuerySQL(dbConnect, "plm_unit", "unitid,unitname").setWhere("siteid", "MD").query().toRowsMap("unitname");
        Rows cInvCodeRows = YUNl_DB.runSqlQuery("select " + "cInvCode," +//--存货编码
                "cInvName," +//存货名称
                "cInvStd," +//规格型号
                "cComUnitName," +//主计量单位名称
                "isnull(bSerial,0)as bSerial " +//是否序列号管理
                "from Inventory where status=0 and accid in('666','888')");

        ArrayList<String> itemnoList = SQLFactory.createQuerySQL(dbConnect, "plm_item", "itemno").setWhere("siteid", "MD").setWhere("itemno", cInvCodeRows.toArrayList("cInvCode")).query().toArrayList("itemno");
        for (Row cInvCodeRow : cInvCodeRows) {
            try {
                String cComUnitName = cInvCodeRow.getString("cComUnitName");
                if (!unitMap.containsKey(cComUnitName)) {
                    continue;//计量单位不存在
                }
                String cInvCode = cInvCodeRow.getString("cInvCode");
                String cInvName = cInvCodeRow.getString("cInvName");
                String cInvStd = cInvCodeRow.getString("cInvStd");
                boolean bSerial = cInvCodeRow.getBoolean("bSerial");
                if (itemnoList.contains(cInvCode)) {
                    UpdateSQL plmItem = SQLFactory.createUpdateSQL(dbConnect, "plm_item");
                    plmItem.setValue("itemname", cInvName);
                    plmItem.setValue("model", cInvStd);
                    plmItem.setValue("unitid", unitMap.get(cComUnitName).get(0).getLong("unitid"));
                    plmItem.setValue("skucontrol", bSerial);
                    plmItem.setValue("changeby", "U8");
                    plmItem.setValue("WMSUPLOADFLAG", 2);
                    plmItem.setWhere("siteid", "MD").setWhere("itemno", cInvCode);
                    plmItem.update();
                } else {
                    InsertSQL plmItem = SQLFactory.createInsertSQL(dbConnect, "plm_item");
                    plmItem.setValue("itemno", cInvCode);
                    plmItem.setValue("itemname", cInvName);
                    plmItem.setValue("model", cInvStd);
                    plmItem.setValue("unitid", unitMap.get(cComUnitName).get(0).getLong("unitid"));
                    plmItem.setValue("skucontrol", bSerial);
                    plmItem.setValue("createby", "U8");
                    plmItem.setValue("WMSUPLOADFLAG", 2);
                    plmItem.setValue("siteid", "MD");
                    plmItem.insert();
                }
                YUNl_DB.runSqlUpdate("update Inventory set status=1 where cInvCode='" + cInvCode + "' and accid in('666','888')");
            } catch (Exception e) {
                logger.error("U8货品档案同步DRP失败", cInvCodeRow.toJsonObject(), e);
            }
        }
    }

    /**
     * 序列号
     */
    public void getMcode() throws YosException {
        Rows codeRows = YUNl_DB.runSqlQuery("SELECT cInvCode,cInvSN FROM ST_SNState where status=0 and accid in('666','888')");
        Rows itemrows = SQLFactory.createQuerySQL(dbConnect, "plm_item", "itemid", "itemno").setWhere("siteid", "MD").setWhere("itemno", codeRows.toArrayList("cInvCode")).query();

        RowsMap itemMap = itemrows.toRowsMap("itemno");

        //已存在的序列号
        ArrayList<String> codeList = SQLFactory.createQuerySQL(dbConnect, "sa_itemsku", "sku").setWhere("siteid", "MD").setWhere("sku", codeRows.toArrayList("cInvSN")).query().toArrayList("sku");

        for (Row codeRow : codeRows) {
            String cInvCode = codeRow.getString("cInvCode");
            String cInvSN = codeRow.getString("cInvSN");
            if (itemMap.containsKey(cInvCode)) {
                if (codeList.contains(cInvSN)) {
                    UpdateSQL sku = SQLFactory.createUpdateSQL(dbConnect, "sa_itemsku");
                    sku.setValue("itemid", itemMap.get(cInvCode).get(0).getLong("itemid"));
                    sku.setValue("changeby", "U8");
                    sku.setWhere("siteid", "MD").setWhere("sku", cInvSN);
                    sku.setValue("WMSUPLOADFLAG", 2);
                    sku.update();
                } else {
                    InsertSQL sku = SQLFactory.createInsertSQL(dbConnect, "sa_itemsku");
                    sku.setValue("itemid", itemMap.get(cInvCode).get(0).getLong("itemid"));
                    sku.setValue("sku", cInvSN);
                    sku.setValue("status", "");
                    sku.setValue("createby", "U8");
                    sku.setValue("siteid", "MD");
                    sku.setValue("WMSUPLOADFLAG", 2);
                    sku.insert();
                }
                YUNl_DB.runSqlUpdate("update ST_SNState set status=1 where cInvCode='" + cInvCode + "' and cInvSN='" + cInvSN + "' and accid in('666','888')");
            } else {
                YUNl_DB.runSqlUpdate("update ST_SNState set status=2 where cInvCode='" + cInvCode + "' and cInvSN='" + cInvSN + "' and accid in('666','888')");
            }
        }
    }

}