md_YOS/src/custom/service/DRPWarrantycardCheck.java

package service;

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

import java.util.HashMap;

public class DRPWarrantycardCheck extends ServiceController {
    private static HashMap<String, Long> agentsnumMapping = new HashMap<>();

    static {
        try {
            Rows rows = new DBConnect().runSqlQuery("select agentnum,sa_agentsid from sa_agents");
            for (Row row : rows) {
                agentsnumMapping.put(row.getString("agentnum"), row.getLong("sa_agentsid"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void serviceRun() throws YosException {
        try {
            SQLDump sqlDump = new SQLDump();
            Rows drpWarrantycardRows = dbConnect.runSqlQuery("select * from mddrp.twarrantycard where eordermatchflag=0 limit 1000");

            RowsMap warrantycardRowsMap = SQLFactory.createQuerySQL(dbConnect, "sa_warrantycard", "cardno", "sa_customersid").setWhere("cardno", drpWarrantycardRows.toArrayList("fbillnum")).query().toRowsMap("cardno");
            RowsMap itemRowsMap = SQLFactory.createQuerySQL(dbConnect, "plm_item", "itemno", "itemid").setWhere("itemno", drpWarrantycardRows.toArrayList("fitemno")).query().toRowsMap("itemno");

            for (Row drpWarrantycardRow : drpWarrantycardRows) {
                String fbillnum = drpWarrantycardRow.getString("fbillnum");
                String fmachinecode = drpWarrantycardRow.getString("fmachinecode");
                String ftoagentnum = drpWarrantycardRow.getString("ftoagentnum");

                if (warrantycardRowsMap.containsKey(fbillnum)) {//如果E-订单中存在该保修卡
                    if (ftoagentnum.isEmpty()) {
                        sqlDump.add("update sa_warrantycard set toagentnum=null,drpmatchflag=1 where cardno='" + fbillnum + "' and sku='" + fmachinecode + "'");
                        sqlDump.add("update sa_customers set sa_agentsid_to=null where sa_customersid='" + warrantycardRowsMap.get(fbillnum).get(0).getLong("sa_customersid") + "'");
                    } else {
                        sqlDump.add("update sa_warrantycard set toagentnum='" + ftoagentnum + "',drpmatchflag=1 where cardno='" + fbillnum + "' and sku='" + fmachinecode + "'");
                        sqlDump.add("update sa_customers set sa_agentsid_to=" + agentsnumMapping.getOrDefault(ftoagentnum, 0L) + " where sa_customersid='" + warrantycardRowsMap.get(fbillnum).get(0).getLong("sa_customersid") + "'");
                    }
                } else {
                    String fagentnum = drpWarrantycardRow.getString("fagentnum");
                    InsertSQL customersInsert = SQLFactory.createInsertSQL(dbConnect, "sa_customers");
                    customersInsert.setValue("datastatus", 0);
                    customersInsert.setValue("tradingstatus", "已成交");
                    customersInsert.setValue("siteid", "MD");
                    customersInsert.setValue("sa_agentsid_to", agentsnumMapping.getOrDefault(ftoagentnum, 0L));
                    customersInsert.setValue("custnum", fbillnum);
                    customersInsert.setValue("address", drpWarrantycardRow.getString("faddress"));
                    customersInsert.setValue("phonenumber", drpWarrantycardRow.getString("fphonenumber"));
                    customersInsert.setValue("name", drpWarrantycardRow.getString("fname"));
                    customersInsert.setValue("sa_agentsid", agentsnumMapping.getOrDefault(fagentnum, 0L));
                    customersInsert.setValue("customergrade", "MD");
                    customersInsert.setValue("sys_enterpriseid", agentsnumMapping.getOrDefault(fagentnum, 0L));
                    customersInsert.setValue("status", "合作中");
                    customersInsert.setValue("createby", drpWarrantycardRow.getString("createby"));
                    if (!drpWarrantycardRow.getString("createdate").isEmpty()) {
                        customersInsert.setValue("createdate", drpWarrantycardRow.getString("createdate"));
                    } else {
                        customersInsert.setValue("createdate", null);
                    }
                    InsertSQL warrantycardInsert = SQLFactory.createInsertSQL(dbConnect, "sa_warrantycard");
                    warrantycardInsert.setValue("siteid", "MD");
                    warrantycardInsert.setValue("drpmatchflag", 2);
                    warrantycardInsert.setValue("isonlinejoin", drpWarrantycardRow.getBoolean("fisonlinejoin"));
                    warrantycardInsert.setValue("name", drpWarrantycardRow.getString("fname"));
                    warrantycardInsert.setValue("phonenumber", drpWarrantycardRow.getString("fphonenumber"));
                    warrantycardInsert.setValue("isonline", drpWarrantycardRow.getBoolean("fisonline"));
                    warrantycardInsert.setValue("sex", drpWarrantycardRow.getString("fsex"));
                    warrantycardInsert.setValue("address", drpWarrantycardRow.getString("faddress"));
                    warrantycardInsert.setValue("onlinename", drpWarrantycardRow.getString("fonlinename"));
                    warrantycardInsert.setValue("onlineordernum", drpWarrantycardRow.getString("fonlineordernum"));
                    warrantycardInsert.setValue("toagentnum", drpWarrantycardRow.getString("ftoagentnum"));
                    warrantycardInsert.setValue("isvoid", drpWarrantycardRow.getBoolean("fisvoid"));
                    warrantycardInsert.setValue("count", drpWarrantycardRow.getInteger("fcount"));
                    if (!drpWarrantycardRow.getString("fvoiddate").isEmpty()) {
                        warrantycardInsert.setValue("voiddate", drpWarrantycardRow.getString("fvoiddate"));
                    }
                    warrantycardInsert.setValue("voidreason", drpWarrantycardRow.getString("fvoidreason"));
                    warrantycardInsert.setValue("sku", drpWarrantycardRow.getString("fmachinecode"));
                    if (itemRowsMap.containsKey(drpWarrantycardRow.getString("fitemno"))) {
                        warrantycardInsert.setValue("itemid", itemRowsMap.get(drpWarrantycardRow.getString("fitemno")).get(0).getLong("itemid"));
                    }
                    warrantycardInsert.setValue("sa_agentsid", agentsnumMapping.getOrDefault(fagentnum, 0L));
                    if (!drpWarrantycardRow.getString("fenddate").isEmpty()) {
                        warrantycardInsert.setValue("enddate", drpWarrantycardRow.getString("fenddate"));
                    }
                    if (!drpWarrantycardRow.getString("fbegdate").isEmpty()) {
                        warrantycardInsert.setValue("begdate", drpWarrantycardRow.getString("fbegdate"));
                    }
                    warrantycardInsert.setValue("sa_customersid", customersInsert.getUniqueid());
                    warrantycardInsert.setValue("cardno", fbillnum);
                    warrantycardInsert.setValue("createby", drpWarrantycardRow.getString("createby"));
                    if (!drpWarrantycardRow.getString("createdate").isEmpty()) {
                        warrantycardInsert.setValue("createdate", drpWarrantycardRow.getString("createdate"));
                    } else {
                        warrantycardInsert.setValue("createdate", null);
                    }
                    sqlDump.add(customersInsert);
                    sqlDump.add(warrantycardInsert);
                }
                sqlDump.add("update mddrp.twarrantycard set eordermatchflag=1 where fbillnum='" + fbillnum + "' and fmachinecode='" + fmachinecode + "'");
            }
            sqlDump.commit();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
        }
    }

    @Override
    public ServiceParam paramSet() {
        return new ServiceParam("DRP保修卡归属经销商同步任务", 1, RunType.minute);
    }

}