md_YOS/src/custom/common/crm/bean/customer.java

package common.crm.bean;

import com.alibaba.fastjson2.JSONObject;
import common.YosException;
import common.crm.bean.core.CrmBase;
import common.data.*;
import org.apache.logging.log4j.Logger;

import java.util.ArrayList;

public class customer extends CrmBase {
    public customer(Logger logger) {
        super(logger);
    }

    @Override
    public void autoAction() throws YosException {
        Rows rows = crmDBConnect.runSqlQuery("select t1.id as syncid,t1.ownerid,t2.* from (select max(id) id,ownerid from e_order_sync where ownertable='st_customer' group by ownerid) t1 left join st_customer t2 on t1.ownerid=t2.cid");
        logger.info("监测到crm有{}个客户档案待同步", rows.size());
        ArrayList<String> agentnums = rows.toArrayList("fagent_num_created");
        agentnums.addAll(rows.toArrayList("fagent_num"));

        QuerySQL agentQuery = SQLFactory.createQuerySQL(dbConnect, "sa_agents", "sa_agentsid", "sys_enterpriseid", "agentnum");
        agentQuery.setWhere("siteid", "MD");
        agentQuery.setWhere("agentnum", agentnums);
        RowsMap agentRowsMap = agentQuery.query().toRowsMap("agentnum");


        RowsMap regionsMap = dbConnect.runSqlQuery("select code,name from t_regions where countryid=1").toRowsMap("code");

        int sucesscount = 0;
        for (Row row : rows) {
            try {
                JSONObject jsonObject = row.toJsonObject();
                long syncid = jsonObject.getLong("syncid");
                long ownerid = jsonObject.getLong("ownerid");
                long cid = jsonObject.getLong("cid");
                if (cid > 0) {
                    String createby = jsonObject.getString("createby");
                    String createtime = jsonObject.getString("createtime");
                    String changeby = jsonObject.getString("changeby");
                    String changetime = jsonObject.getString("changetime");
                    String customer_status = jsonObject.getString("customer_status");
                    String fchannel = jsonObject.getString("fchannel");
                    String fname = jsonObject.getString("fname");
                    String fphone = jsonObject.getString("fphone");
                    String fcommunity = jsonObject.getString("fcommunity");
                    String fcustom_num = jsonObject.getString("fcustom_num");
                    String fagent_num_created = jsonObject.getString("fagent_num_created");
                    String fagent_num = jsonObject.getString("fagent_num");
                    String fprovince = jsonObject.getString("fprovince");
                    String fcity = jsonObject.getString("fcity");
                    String fcounty = jsonObject.getString("fcounty");

                    if (agentRowsMap.containsKey(fagent_num_created)) {
                        if (dbConnect.runSqlQuery("select * from sa_customers where sa_customersid=" + cid).isEmpty()) {
                            InsertSQL insertSQL = SQLFactory.createInsertSQL(dbConnect, "sa_customers");
                            insertSQL.setValue("siteid", "MD");
                            insertSQL.setValue("sa_customersid", cid);
                            insertSQL.setValue("createby", createby);
                            insertSQL.setValue("createdate", createtime);
                            insertSQL.setValue("changeby", changeby);
                            insertSQL.setValue("changedate", changetime);
                            insertSQL.setValue("sys_enterpriseid", agentRowsMap.get(fagent_num_created).get(0).getLong("sys_enterpriseid"));
                            insertSQL.setValue("sa_agentsid", agentRowsMap.get(fagent_num_created).get(0).getLong("sa_agentsid"));
                            if (agentRowsMap.containsKey(fagent_num)) {
                                insertSQL.setValue("sa_agentsid_to", agentRowsMap.get(fagent_num).get(0).getLong("sa_agentsid"));
                            }
                            insertSQL.setValue("status", customer_status);
                            insertSQL.setValue("source", fchannel);
                            insertSQL.setValue("tradingstatus", customer_status.equals("正式客户") ? "已成交" : "未成交");
                            insertSQL.setValue("datastatus", 0);
                            insertSQL.setValue("ispublic", 0);
                            insertSQL.setValue("name", fname);
                            insertSQL.setValue("phonenumber", fphone);
                            if (regionsMap.containsKey(fprovince)) {
                                insertSQL.setValue("province", regionsMap.get(fprovince).get(0).getString("name"));
                            }
                            if (regionsMap.containsKey(fcity)) {
                                insertSQL.setValue("city", regionsMap.get(fcity).get(0).getString("name"));
                            }
                            if (regionsMap.containsKey(fcounty)) {
                                insertSQL.setValue("county", regionsMap.get(fcounty).get(0).getString("name"));
                            }
                            insertSQL.setValue("address", fcommunity);
                            insertSQL.setValue("custnum", fcustom_num);
                            insertSQL.insert();
                        } else {
                            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(dbConnect, "sa_customers");
                            updateSQL.setValue("changeby", changeby);
                            updateSQL.setValue("changedate", changetime);
                            if (agentRowsMap.containsKey(fagent_num)) {
                                updateSQL.setValue("sa_agentsid_to", agentRowsMap.get(fagent_num).get(0).getLong("sa_agentsid"));
                            }
                            updateSQL.setValue("status", customer_status);
                            updateSQL.setValue("source", fchannel);
                            updateSQL.setValue("tradingstatus", customer_status.equals("正式客户") ? "已成交" : "未成交");
                            updateSQL.setValue("name", fname);
                            updateSQL.setValue("phonenumber", fphone);
                            if (regionsMap.containsKey(fprovince)) {
                                updateSQL.setValue("province", regionsMap.get(fprovince).get(0).getString("name"));
                            }
                            if (regionsMap.containsKey(fcity)) {
                                updateSQL.setValue("city", regionsMap.get(fcity).get(0).getString("name"));
                            }
                            if (regionsMap.containsKey(fcounty)) {
                                updateSQL.setValue("county", regionsMap.get(fcounty).get(0).getString("name"));
                            }
                            updateSQL.setValue("address", fcommunity);
                            updateSQL.setValue("custnum", fcustom_num);

                            updateSQL.setWhere("siteid", "MD");
                            updateSQL.setWhere("sa_customersid", cid);
                            updateSQL.update();
                        }
                    }
                }
                crmDBConnect.runSqlUpdate("delete from e_order_sync where ownertable='st_customer' and ownerid=" + ownerid + " and id<=" + syncid);
                sucesscount++;
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
        }
        logger.info("成功处理{}个客户档案同步", sucesscount);
    }
}