md_YOS/src/custom/service/AutoDockItemService.java

package service;

import beans.parameter.Parameter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.BaseClass;
import common.ServiceController;
import common.YosException;
import common.data.*;
import common.data.db.DBConnect;
import common.data.db.initialization.base.YosObject;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import restcontroller.webmanage.sale.item.Item;
import utility.ERPDocking;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class AutoDockItemService extends ServiceController {

    String[] siteids = {"CCYOSG", "DLB"};

    @Override
    public void serviceRun() throws YosException {
        for (String siteid : siteids) {
            if (dbConnect.runSqlQuery("SELECT 1 from  sys_site WHERE siteid='" + siteid + "'").isEmpty()) {
                continue;
            }

            String paramname = "AutoDockItemServiceTime" + "_" + siteid;
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            simpleDateFormat.setTimeZone(TimeZone.getTimeZone("Etc/GMT-8"));
            String AutoDockItemServiceTime = getAutoDockItemServiceTime(paramname, simpleDateFormat);

            ArrayList<String> sqlList = new ArrayList<>();
            Rows itemRows = dbConnect.runSqlQuery("SELECT itemid,itemno from  plm_item WHERE siteid = '" + siteid + "' AND status = '新建' ");
            Rows itemRowsAll = dbConnect.runSqlQuery("SELECT itemid,itemno,status,isonsale,sa_customschemeid from  plm_item WHERE siteid = '" + siteid + "'");
            //单位
            Rows unitRows = dbConnect.runSqlQuery("SELECT unitid,unitname from  plm_unit WHERE siteid = '" + siteid + "'");
            RowsMap unitRowsMap = unitRows.toRowsMap("unitname");

            RowsMap itemRowsMap = itemRows.toRowsMap("itemno");
            RowsMap itemRowsMapAll = itemRowsAll.toRowsMap("itemno");
            ERPDocking erpDocking = new ERPDocking(siteid);

            JSONArray jsonArray = erpDocking.getErpItemRows(AutoDockItemServiceTime, 100000, 1);
            if (!jsonArray.isEmpty()) {
                for (Object object : jsonArray) {
                    JSONObject jsonObject = (JSONObject) object;
                    System.err.println(jsonObject);
                    if (!itemRowsMapAll.containsKey(jsonObject.getString("fitemno"))) {
                        long itemid = createTableID("plm_item");
                        SQLFactory sqlFactory = new SQLFactory(new Item(jsonObject), "货品档案新增");

                        sqlFactory.addParameter("siteid", siteid);
                        sqlFactory.addParameter("itemid", itemid);
                        sqlFactory.addParameter("username", "admin");
                        sqlFactory.addParameter("userid", 1);
                        sqlFactory.addParameter("itemno", jsonObject.getString("fitemno"));
                        sqlFactory.addParameter("marketingcategory", "");
                        if (unitRowsMap.containsKey(jsonObject.getString("funit"))) {
                            sqlFactory.addParameter("unitid", unitRowsMap.get(jsonObject.getString("funit")).get(0).getLong("unitid"));
                        } else {
                            sqlFactory.addParameter("unitid", 0);
                        }
                        sqlFactory.addParameter("isauxunit", 0);
                        sqlFactory.addParameter("unitgroupid", "null");

                        sqlFactory.addParameter("itemname", jsonObject.getString("fitemname"));
                        sqlFactory.addParameter("isonsale", 0);
                        sqlFactory.addParameter("model", jsonObject.getString("fmodel"));
                        sqlFactory.addParameter("spec", jsonObject.getString("spec"));
                        // 订单起订量
                        sqlFactory.addParameter("orderminqty", 1);
                        // 订单增量
                        sqlFactory.addParameter("orderaddqty", 1);
                        // 订单起订量控制单位（0:主单位；1:辅助单位）
                        sqlFactory.addParameter("orderminqty_auxunit", 0);
                        // 订单增量控制单位（0:主单位；1:辅助单位）
                        sqlFactory.addParameter("orderaddqty_auxunit", 0);
                        // 说明
                        sqlFactory.addParameter("remarks", "");
                        // 商品条形码
                        sqlFactory.addParameter("barcode", "");
                        // 是否单品管理
                        sqlFactory.addParameter("skucontrol", 0);
                        // 是否批次管理
                        sqlFactory.addParameter("batchcontrol", jsonObject.getBooleanValue("fisbatch"));
                        // 毛重(g)
                        sqlFactory.addParameter("grossweight", 0);
                        // 净重(g)
                        sqlFactory.addParameter("weight", 0);
                        // 高度(mm)
                        sqlFactory.addParameter("height", 0);
                        // 宽度(mm)
                        sqlFactory.addParameter("width", 0);
                        // 长度(mm)
                        sqlFactory.addParameter("length", 0);
                        // 交期
                        sqlFactory.addParameter("delivery", 1);
                        // 牌价
                        sqlFactory.addParameter("marketprice", 0);
                        // 是否工具
                        sqlFactory.addParameter("istool", 0);
                        // 产品标准
                        sqlFactory.addParameter("standards", "");
                        // 库存缺货标准
                        sqlFactory.addParameter("stockstatus2", 1);
                        // 库存充足标准
                        sqlFactory.addParameter("stockstatus1", 1);
                        // 保修期（年）
                        sqlFactory.addParameter("warrantyday", 1);
                        // 是否营销物料
                        sqlFactory.addParameter("iswuliao", 0);
                        // 是否服务物料
                        sqlFactory.addParameter("isservice", 0);

                        sqlFactory.addParameter("materialschemeid", 0);
                        sqlFactory.addParameter("colorschemeid", 0);
                        sqlFactory.addParameter("cheekschemeid", 0);


                        sqlFactory.addParameter("color", jsonObject.getStringValue("fcolor"));
                        sqlFactory.addParameter("cheek", jsonObject.getStringValue("fsizedescription"));
                        sqlFactory.addParameter("delistingstatus", "");

                        sqlFactory.addParameter("ismodule", 0);
                        sqlFactory.addParameter("packageqty", (StringUtils.isBlank(jsonObject.getStringValue("fpackqty")) ? "0" : jsonObject.getStringValue("fpackqty")));
                        sqlFactory.addParameter("iswoodproducts", 0);

                        sqlFactory.addParameter("sa_customschemeid", 0);
                        sqlFactory.addParameter("financeclasstype", jsonObject.getStringValue("fdescription"));
                        sqlFactory.addParameter("stockno", jsonObject.getStringValue("fstockno"));
                        sqlFactory.addParameter("volume", jsonObject.getBigDecimal("fvolume"));
                        sqlFactory.addParameter("pricingmetod", "2");

                        sqlFactory.addParameter("saleprice", 0);
                        sqlFactory.addParameter("cost", 0);
                        sqlFactory.addParameter("grossprofit", 0);
                        sqlFactory.addParameter("grossprofitmargin", 0);

                        sqlList.add(sqlFactory.getSQL());
                        // 货品档案扩展属性字段表

                        sqlFactory = new SQLFactory(new Item(jsonObject), "货品档案-扩展新增");
                        sqlFactory.addParameter("siteid", siteid);
                        sqlFactory.addParameter("userid", 1);
                        sqlFactory.addParameter("username", "admin");
                        sqlFactory.addParameter("plm_itemextendid", createTableID("plm_itemextend"));
                        sqlFactory.addParameter("itemid", itemid);
                        sqlFactory.addParameter("material", jsonObject.getString("fmaterialdes"));
                        sqlFactory.addParameter("pressure", "");
                        sqlFactory.addParameter("caliber", "");
                        sqlFactory.addParameter("butterflyplatedrive", "");
                        sqlFactory.addParameter("connection", "");
                        sqlFactory.addParameter("valveplatematerial", "");
                        sqlFactory.addParameter("bodymaterial", "");
                        sqlFactory.addParameter("actuatortype", "");
                        sqlFactory.addParameter("actuatorbrand", "");
                        sqlFactory.addParameter("isbutterfly", 0);
                        sqlFactory.addParameter("erpitemno", jsonObject.getString("fitemno"));
                        sqlFactory.addParameter("erpitemname", jsonObject.getString("fitemname"));
                        sqlFactory.addParameter("specalnote", "");
                        sqlFactory.addParameter("prodline", "");
                        sqlFactory.addParameter("device", "");
                        sqlList.add(sqlFactory.getSQL());

                        // 添加领域
                        Rows tradefieldRows = dbConnect.runSqlQuery("select value,siteid from sys_optiontypemx where isused=1 and (ifnull(siteid,'')='' or siteid='"+siteid+"') and optiontypeid in(select optiontypeid from sys_optiontype where typename='tradefield')");
                        if (tradefieldRows.isNotEmpty() && tradefieldRows.size()==1) {
                            sqlFactory = new SQLFactory(new Item(jsonObject), "新增领域");
                            sqlFactory.addParameter("siteid", siteid);
                            sqlFactory.addParameter("plm_item_tradefieldid", createTableID("plm_item_tradefield"));
                            sqlFactory.addParameter("tradefield", tradefieldRows.get(0).getString("value"));
                            sqlFactory.addParameter("userid", 1);
                            sqlFactory.addParameter("itemid", itemid);
                            sqlList.add(sqlFactory.getSQL());

                        }



                    } else {
//                        sqlList.add("update plm_item set itemname='" + jsonObject.getString("fitemname") + "',volume='" + jsonObject.getStringValue("fvolume") + "',packageqty='" + (StringUtils.isBlank(jsonObject.getStringValue("fpackqty")) ? "0" : jsonObject.getStringValue("fpackqty")) + "' where itemid=" + itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getLong("itemid"));
//                        System.out.println("update plm_item set itemname='" + jsonObject.getString("fitemname") + "',volume='" + jsonObject.getStringValue("fvolume") + "',packageqty='" + (StringUtils.isBlank(jsonObject.getStringValue("fpackqty")) ? "0" : jsonObject.getStringValue("fpackqty")) + "' where itemid=" + itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getLong("itemid"));
                        Long itemid = itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getLong("itemid");
                        String spec = jsonObject.getStringValue("spec");//尺寸
                        String cheek = jsonObject.getStringValue("fsizedescription");//工艺
                        String material = jsonObject.getStringValue("fmaterialdes");//选项
                        String color = jsonObject.getStringValue("fcolor");//颜色
                        boolean isonsale = itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getBoolean("isonsale");
                        if (dbConnect.runSqlQuery("SELECT 1 FROM plm_item t1 LEFT JOIN plm_itemextend t2 ON t2.itemid=t1.itemid AND t2.siteid=t1.siteid " +
                                "WHERE spec='" + spec + "' AND cheek='" + cheek + "' AND material='" + material + "' AND color='" + color + "'").isEmpty() && itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getLong("sa_customschemeid")!=0) {
                            isonsale = false;
                            sqlList.add("delete from sa_itemgroupmx WHERE itemid=" + itemid + " and siteid='" + siteid + "' ");
                            Rows rows = dbConnect.runSqlQuery("SELECT sa_itemgroupid from sa_itemgroupmx WHERE itemid=" + itemid + " and siteid='" + siteid + "' ");
                            ArrayList<Long> sa_itemgroupids = rows.toArrayList("sa_itemgroupid", new ArrayList<>());
                            for (Long sa_itemgroupid : sa_itemgroupids) {
                                int count = dbConnect.runSqlQuery(0, "SELECT count(0) count from sa_itemgroupmx WHERE sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "' ").getInteger("count");
                                if (count == 1) {
                                    sqlList.add("delete from sa_itemgroup WHERE sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "' ");
                                }
                            }
                        }

                        if (itemRowsMapAll.get(jsonObject.getString("fitemno")).get(0).getLong("sa_customschemeid")==0) {
                            Rows rows = dbConnect.runSqlQuery("SELECT sa_itemgroupid from sa_itemgroupmx WHERE itemid=" + itemid + " and siteid='" + siteid + "' ");
                            ArrayList<Long> sa_itemgroupids = rows.toArrayList("sa_itemgroupid", new ArrayList<>());
                            for (Long sa_itemgroupid : sa_itemgroupids) {
                                int count = dbConnect.runSqlQuery(0, "SELECT count(0) count from sa_itemgroupmx WHERE sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "' ").getInteger("count");
                                if (count == 1) {
                                    sqlList.add("update sa_itemgroup set groupname='"+jsonObject.getString("fitemname")+"' where sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "' ");
                                }
                            }
                        }
                        boolean iscustomsize = false;
                        if (spec.equals("custom") || cheek.equals("custom") || material.equals("custom") || color.equals("custom")) {
                            iscustomsize = true;
                        }

                        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(new DBConnect(), "plm_item");
                        updateSQL.setWhere("itemid", itemid);
                        updateSQL.setSiteid(siteid);
                        updateSQL.setValue("isonsale", isonsale);
                        updateSQL.setValue("iscustomsize", iscustomsize);
                        updateSQL.setValue("itemname", jsonObject.getString("fitemname"));
                        updateSQL.setValue("volume", jsonObject.getStringValue("fvolume"));
                        updateSQL.setValue("packageqty", (StringUtils.isBlank(jsonObject.getStringValue("fpackqty")) ? "0" : jsonObject.getStringValue("fpackqty")));
                        updateSQL.setValue("itemno", jsonObject.getString("fitemno"));//商品编码
                        updateSQL.setValue("model", jsonObject.getString("fmodel"));//型号
                        updateSQL.setValue("cheek", cheek);//工艺
                        updateSQL.setValue("changeuserid","1");
                        updateSQL.setValue("changeby","admin");
                        updateSQL.setValue("changedate",getDateTime_Str());
                        updateSQL.setValue("itemname", jsonObject.getString("fitemname"));//品名
//                        updateSQL.setValue("standards","");//规格
                        updateSQL.setValue("color", color);//颜色
                        updateSQL.setValue("batchcontrol", jsonObject.getBooleanValue("fisbatch"));//批次管理
                        if (unitRowsMap.containsKey(jsonObject.getString("funit"))) {
                            updateSQL.setValue("unitid", unitRowsMap.get(jsonObject.getString("funit")).get(0).getLong("unitid"));
                        } else {
                            updateSQL.setValue("unitid", 0);
                        }
                        updateSQL.setValue("spec", spec);//尺寸
                        if(!iscustomsize){
                            //updateSQL.setValue("sa_customschemeid", 0);
                            updateSQL.setValue("materialschemeid", 0);
                            updateSQL.setValue("colorschemeid", 0);
                            updateSQL.setValue("cheekschemeid", 0);
                            updateSQL.setValue("widthschemeid", 0);
                            updateSQL.setValue("lengthschemeid", 0);
                        }
                        sqlList.add(updateSQL.getSQL());

                        if(dbConnect.runSqlQuery("SELECT 1 FROM plm_itemextend WHERE itemid='" + itemid + "'").isEmpty()){
                            InsertSQL insertSQL = SQLFactory.createInsertSQL(new DBConnect(), "plm_itemextend");
                            insertSQL.setValue("plm_itemextendid", createTableID("plm_itemextend"));
                            insertSQL.setValue("siteid", siteid);
                            insertSQL.setValue("material", material);
                            insertSQL.setValue("changeuserid","1");
                            insertSQL.setValue("itemid", itemid);
                            sqlList.add(insertSQL.getSQL());
                        }else{
                            UpdateSQL updateSQL2 = SQLFactory.createUpdateSQL(new DBConnect(), "plm_itemextend");
                            updateSQL2.setWhere("itemid", itemid);
                            updateSQL2.setSiteid(siteid);
                            updateSQL2.setValue("changeuserid","1");
                            updateSQL2.setValue("material", material);//颜色
                            sqlList.add(updateSQL2.getSQL());
                        }


                    }

                }
            }
            for (String s:
            sqlList) {
                System.err.println(s);
            }

            dbConnect.runSqlUpdate(sqlList);

            YosObject.createParameter( YosObject.ParamType.文本, paramname, simpleDateFormat.format(new Date()), "");
        }
    }

    public String getAutoDockItemServiceTime(String paramname, SimpleDateFormat simpleDateFormat) throws YosException {
        Rows rows = new DBConnect().runSqlQuery("select * from sys_parameter where paramname='" + paramname + "'");
        Date date = new Date();
        String AutoDockItemServiceTime = simpleDateFormat.format(date);
        if (rows.isNotEmpty()) {
            AutoDockItemServiceTime = rows.get(0).getString("paramvalue");
            dbConnect.runSqlUpdate("delete from sys_parameter where paramname='" + paramname + "'");
        } else {
            YosObject.createParameter( YosObject.ParamType.文本, paramname, simpleDateFormat.format(date), "");
        }

        return AutoDockItemServiceTime;
    }

}