md_YOS/src/custom/restcontroller/webmanage/sale/customer/Customer.java

package restcontroller.webmanage.sale.customer;

import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.saletool.orderclue.ExportExcel;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

@API(title = "客户")
public class Customer extends Controller {

    /**
     * 构造函数
     *
     * @param content
     */
    public Customer(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增或更新", apiversion = R.ID20221010164302.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String insertOrUpdate() throws YosException {
        String tablename = "sa_customers";
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        String enterprisename = content.getString("enterprisename");

        ArrayList<String> sqlList = new ArrayList<>();
        //创建企业档案
        SQLFactory sqlFactory = new SQLFactory(this, "客户企业档案新增");
        if (sys_enterpriseid <= 0) {
            sys_enterpriseid = createTableID("sys_enterprise");
        } else {
            sqlFactory = new SQLFactory(this, "客户企业档案更新");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("province", content.getStringValue("province"));
        sqlFactory.addParameter("city", content.getStringValue("city"));
        sqlFactory.addParameter("county", content.getStringValue("county"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        sqlFactory.addParameter("enterprisename", enterprisename);
        sqlFactory.addParameter("abbreviation", content.getStringValue("abbreviation"));
        sqlFactory.addParameter("taxno", content.getStringValue("taxno"));
        sqlFactory.addParameter("grade", content.getLongValue("grade") == 0 ? "null" : content.getLongValue("grade"));
        sqlFactory.addParameter("industry", content.getJSONArray("industry"));
        sqlFactory.addParameter("contact", content.getStringValue("contact"));
        sqlFactory.addParameter("phonenumber", content.getStringValue("phonenumber"));
        sqlFactory.addParameter("telephone", content.getStringValue("telephone"));

        sqlList.add(sqlFactory.getSQL());


        //扩展字段
        String erpagentnum = content.getStringValue("erpagentnum");
        String sql = "SELECT * from sys_dataextend WHERE ownertable='sys_enterprise' and ownerid=" + sys_enterpriseid + " and siteid = '" + siteid + "'";
        Rows extendRows = dbConnect.runSqlQuery(sql);
        Long sys_dataextendid = 0L;
        if (extendRows.isEmpty()) {
            sys_dataextendid = createTableID("sys_dataextend");
            sqlFactory = new SQLFactory(this, "经销商扩展字段新增");
        } else {
            sys_dataextendid = extendRows.get(0).getLong("sys_dataextendid");
            sqlFactory = new SQLFactory(this, "经销商扩展字段更新");

        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sys_dataextendid", sys_dataextendid);
        sqlFactory.addParameter("ownerid", sys_enterpriseid);
        sqlFactory.addParameter("ownertable", "sys_enterprise");
        sqlFactory.addParameter("erpagentnum", erpagentnum);
        sqlList.add(sqlFactory.getSQL());


        Long sa_customersid = content.getLong("sa_customersid");
        Long parentid = content.getLong("parentid");
        String type = content.getString("type");
        Long sa_customerpoolid = content.getLong("sa_customerpoolid");

        boolean isAdd = true;
        sqlFactory = new SQLFactory(this, "客户新增");
        if (sa_customersid <= 0) {
            sa_customersid = createTableID("sa_customers");
            DataTag.createTag(this, "sa_customers", sa_customersid, "潜在");
            isAdd = true;
        } else {
            sqlFactory = new SQLFactory(this, "客户更新");
            isAdd = false;
        }

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_customersid", sa_customersid);
        //上级客户ID
        sqlFactory.addParameter("parentid", parentid == 0 ? "null" : parentid);
        //合作企业档案ID
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        //客户类型
        sqlFactory.addParameter("type", type);
        //客户池（公海池）ID
        sqlFactory.addParameter("sa_customerpoolid", sa_customerpoolid);
        //客户来源
        sqlFactory.addParameter("source", content.getStringValue("source"));
        sqlFactory.addParameter("customergrade", content.getStringValue("customergrade"));
        Long ispublic = content.getLongValue("ispublic", 1L);
        sqlFactory.addParameter("ispublic", ispublic);
        sqlFactory.addParameter("name", enterprisename);
        sqlFactory.addParameter("province", content.getStringValue("province"));
        sqlFactory.addParameter("city", content.getStringValue("city"));
        sqlFactory.addParameter("county", content.getStringValue("county"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        sqlFactory.addParameter("phonenumber", content.getStringValue("phonenumber"));

        sqlList.add(sqlFactory.getSQL());

        //操作记录
        if (isAdd) {
            if (ispublic == 1) {
                sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "新建", "新建公海客户" + enterprisename).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "新建", "新建客户" + enterprisename).getSQL());
            }

            Executor.sendEml(this, "customers_add", sa_customersid, siteid, getUsers(sa_customerpoolid));


        } else {
            if (ispublic == 1) {
                sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "编辑", "编辑公海客户" + enterprisename).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "编辑", "编辑客户" + enterprisename).getSQL());
            }
        }


        dbConnect.runSqlUpdate(sqlList);

        content.put("sa_customersid", sa_customersid);

        return selectDetail();
    }

    @API(title = "数据状态变更", apiversion = R.ID20221010164402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String changeDataStatus() throws YosException {
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");
        Long datastatus = content.getLong("datastatus");
        SQLFactory sqlFactory = new SQLFactory(this, "客户数据状态变更");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("datastatus", datastatus);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        dbConnect.runSqlUpdate(sqlFactory);
        if (datastatus == 1) {
            //新增删除或作废原因
            DataExtend.createDeleteReasonSql(this, "sa_customers", sa_customersids.toJavaList(Long.class), content.getStringValue("deletereason"));
        }
        return getSucReturnObject().toString();
    }

    @API(title = "交易状态变更", apiversion = R.ID20221010164502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String changeTradingStatus() throws YosException {
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");
        String tradingstatus = content.getString("tradingstatus");
        SQLFactory sqlFactory = new SQLFactory(this, "客户交易状态变更");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("tradingstatus", tradingstatus);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        dbConnect.runSqlUpdate(sqlFactory);
        return getSucReturnObject().toString();
    }

    @API(title = "客户状态变更", apiversion = R.ID20221010164602.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class, R.ID20220929085401.class})
    public String changeStatus() throws YosException {
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");
        String status = content.getString("status");

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "客户状态变更");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("status", status);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        sqlList.add(sqlFactory.getSQL());

        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            DataTag.deleteTag(this, "sa_customers", id, "潜在");
            DataTag.deleteTag(this, "sa_customers", id, "合作中");
            DataTag.deleteTag(this, "sa_customers", id, "已终止");
            DataTag.createTag(this, "sa_customers", id, status);
            //操作记录
            sqlList.add(DataContrlLog.createLog(this, "sa_customers", id, "更改合作状态", "更改合作状态为:" + status).getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }

    @API(title = "详细", apiversion = R.ID20221011133502.v1.class)
    public String selectDetail() throws YosException {
        Long sa_customersid = content.getLong("sa_customersid");

        SQLFactory sqlFactory = new SQLFactory(this, "客户详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_customersid", sa_customersid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());


        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Row()).toString();
    }

    @API(title = "公海客户列表", apiversion = R.ID20221011133602.v1.class)
    @CACHEING
    public String selectPublicList() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.sa_customersid like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.status like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.tradingstatus like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.datastatus like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.poolname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.industry like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.grade like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("startdate") && !"".equals(whereObject.getString("startdate"))) {
                where.append(" and(");
                where.append("t1.createdate >='").append(whereObject.getString("startdate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and(");
                where.append("t1.createdate <='").append(whereObject.getString("enddate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("sa_customerpoolid") && !"".equals(whereObject.getString("sa_customerpoolid"))) {
                where.append(" and(");
                where.append("t1.sa_customerpoolid ='").append(whereObject.getString("sa_customerpoolid")).append("' ");
                where.append(")");
            }
            //客户状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            //客户类型
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }
            //行业
            if (whereObject.containsKey("industry") && !"".equals(whereObject.getString("industry"))) {
                where.append(" and(");
                where.append("t3.industry ='").append(whereObject.getString("industry")).append("' ");
                where.append(")");
            }
            //客户等级
            if (whereObject.containsKey("customergrade") && !"".equals(whereObject.getString("customergrade"))) {
                where.append(" and(");
                where.append("t1.customergrade ='").append(whereObject.getString("customergrade")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("tradingstatus") && !"".equals(whereObject.getString("tradingstatus"))) {
                where.append(" and(");
                where.append("t1.tradingstatus ='").append(whereObject.getString("tradingstatus")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("grade") && !"".equals(whereObject.getString("grade"))) {
                where.append(" and(");
                where.append("t3.grade ='").append(whereObject.getString("grade")).append("' ");
                where.append(")");
            }
        }
        boolean isExport = content.getBoolean("isExport");
        String tablename = "sa_customers";

        SQLFactory sqlFactory = new SQLFactory(this, "客户(公海)列表");
        if (isExport) {
            sqlFactory = new SQLFactory(this, "客户(公海)列表");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_customersid", new ArrayList<>());

        RowsMap leaderRows = DataTeam.getLeader(this, tablename, rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, tablename, ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, tablename, ids, true);
        for (Row row : rows) {
            Long id = row.getLong("sa_customersid");
            row.put("leader", leaderRows.get(String.valueOf(id)));
            //非系统标签
            row.put("tag", tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>());
            //系统标签
            row.put("tag_sys", sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>());
        }
        if (isExport) {
            //去除不要的导出项
            rows.getFieldList().remove("sa_customersid");
            rows.getFieldList().remove("parentid");
            rows.getFieldList().remove("sys_enterpriseid");
            rows.getFieldList().remove("sa_customerpoolid");
            for (Row row : rows) {
                switch (row.getString("datastatus")) {
                    case "0":
                        row.put("datastatusStr", "正常");
                    case "1":
                        row.put("datastatusStr", "作废");
                    case "2":
                        row.put("datastatusStr", "锁定");
                }
            }
            rows.getFieldList().remove("datastatus");
            rows.getFieldList().add("datastatusStr");
            Rows uploadRows = saveToExcelAttachment("1111", "公海客户列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "私域客户列表", apiversion = R.ID20221013102602.v1.class)
    @CACHEING
    public String selectPrivateList() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.sa_customersid like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.status like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.tradingstatus like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.datastatus like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.poolname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.industry like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.grade like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("startdate") && !"".equals(whereObject.getString("startdate"))) {
                where.append(" and(");
                where.append("t1.createdate >='").append(whereObject.getString("startdate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and(");
                where.append("t1.createdate <='").append(whereObject.getString("enddate")).append("' ");
                where.append(")");
            }
            //客户状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            //客户类型
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }
            //行业
            if (whereObject.containsKey("industry") && !"".equals(whereObject.getString("industry"))) {
                where.append(" and(");
                where.append("t3.industry ='").append(whereObject.getString("industry")).append("' ");
                where.append(")");
            }
            //客户等级
            if (whereObject.containsKey("customergrade") && !"".equals(whereObject.getString("customergrade"))) {
                where.append(" and(");
                where.append("t1.customergrade ='").append(whereObject.getString("customergrade")).append("' ");
                where.append(")");
            }
        }

        boolean isExport = content.getBoolean("isExport");
        SQLFactory sqlFactory = new SQLFactory(this, "客户(私域)列表");
        if (isExport) {
            sqlFactory = new SQLFactory(this, "客户(私域)列表");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber);
        }
        Rows rows = querySQL.query();

        RowsMap leaderRows = DataTeam.getLeader(this, "sa_customers", rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        for (Row row : rows) {
            row.put("leader", leaderRows.get(row.getString("sa_customersid")));
        }
        if (isExport) {
            //去除不要的导出项
            rows.getFieldList().remove("sa_customersid");
            rows.getFieldList().remove("parentid");
            rows.getFieldList().remove("sys_enterpriseid");
            rows.getFieldList().remove("sa_customerpoolid");
            for (Row row : rows) {
                switch (row.getString("datastatus")) {
                    case "0":
                        row.put("datastatusStr", "正常");
                    case "1":
                        row.put("datastatusStr", "作废");
                    case "2":
                        row.put("datastatusStr", "锁定");
                }
            }
            rows.getFieldList().remove("datastatus");
            rows.getFieldList().add("datastatusStr");
            Rows uploadRows = saveToExcelAttachment("1111", "私域客户列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }


        return getSucReturnObject().setData(rows).toString();
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("sa_customersid", "客户ID");
        titleMap.put("createby", "创建人");
        titleMap.put("createdate", "创建时间");
        titleMap.put("status", "客户状态");
        titleMap.put("type", "客户类型");
        titleMap.put("sa_customerpoolid", "公海池ID");
        titleMap.put("source", "客户来源");
        titleMap.put("tradingstatus", "交易状态");
        titleMap.put("datastatusStr", "数据状态");
        titleMap.put("poolname", "公海池名称");
        titleMap.put("enterprisename", "客户名称");
        titleMap.put("abbreviation", "客户简称");
        titleMap.put("industry", "所属行业");
        titleMap.put("grade", "客户等级");
        titleMap.put("superiorenterprisename", "上级企业");
        titleMap.put("followdate", "最近跟进时间");
        return titleMap;
    }

    @API(title = "分配,分配公海客户至业务人员私域", apiversion = R.ID20221014165502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String distribution() throws YosException {

        JSONArray sa_customersids = content.getJSONArray("sa_customersids");


        String str = "select DISTINCT sa_customerpoolid from sa_customers where siteid='" + siteid + "' and sa_customerpoolid != 0 and sa_customersid in " + sa_customersids + " ";
        str = str.replace("[", "(").replace("]", ")");

        if (dbConnect.runSqlQuery(str).size() > 1) {
            return getErrReturnObject().setErrMsg("分配的客户所在的公海池不一致").toString();
        }

        ArrayList<String> sqlStr = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "客户分配");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        sqlStr.add(sqlFactory.getSQL());

        Long userid = content.getLong("userid");
        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            //数据团队新增
            sqlStr.addAll(DataTeam.createTeamSQL(this, "sa_customers", id, userid));
            sqlStr.addAll(DataTeam.createTeamMemberSql(this, "sa_customers", id, userid));

            sqlStr.add(DataContrlLog.createLog(this, "sa_customers", id, "分配", "分配公海客户" + getCustomerEnterprisename(id)).getSQL());
        }


        dbConnect.runSqlUpdate(sqlStr);

        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sa_customers", id).toArrayList("userid", new ArrayList<>());
            Executor.sendEml(this, "customers_receive", id, siteid, userids);
        }


        return getSucReturnObject().toString();
    }

    @API(title = "回收,回收业务人员的客户至公海", apiversion = R.ID20221014165602.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String recovery() throws YosException {
        Long sa_customersid = content.getLong("sa_customersid");
        Long sa_customerpoolid = content.getLong("sa_customerpoolid");

        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_customers WHERE sa_customersid = " + sa_customersid + " and siteid ='" + siteid + "'");
        if (rows.get(0).getLong("ispublic") == 0) {
            return getErrReturnObject().setErrMsg("非公海客户无法回收").toString();
        }


        ArrayList<String> sqlStr = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "客户回收");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_customerpoolid", sa_customerpoolid);
        sqlFactory.addParameter_in("sa_customersid", sa_customersid);
        sqlStr.add(sqlFactory.getSQL());

        String str = "delete from sys_datateam where siteid='" + siteid + "' and ownertable='sa_customers' and ownerid = " + sa_customersid + " ";
        sqlStr.add(str);


        sqlStr.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "回收", "回收客户至公海" + sa_customersid).getSQL());

        ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sa_customers", sa_customersid).toArrayList("userid", new ArrayList<>());
        Executor.sendEml(this, "customers_recovery", sa_customersid, siteid, userids);

        dbConnect.runSqlUpdate(sqlStr);
        return getSucReturnObject().toString();
    }


    @API(title = "可分配业务员列表", apiversion = R.ID20221017094102.v1.class)
    public String saleList() throws YosException {
        Long sa_customerpoolid = content.getLong("sa_customerpoolid");

        Rows rows = dbConnect.runSqlQuery("SELECT t1.userid,t1.`name`  FROM sys_datateam t1 WHERE ownertable = 'sa_customerpool' AND ownerid = " + sa_customerpoolid + "");

        for (Row row : rows) {
            row.put("headpic", getHeadPic(row.getLong("userid")));
        }

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "客户导入模板", apiversion = R.ID20221028100502.v1.class)
    public String downloadExcel() throws YosException {

        ExcelFactory excelFactory = new ExcelFactory("客户导入模板");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        // 设置工作薄列宽
        CustomerExcel.setBatchDetailSheetColumn1(sheet);
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        // 写入标题
        CustomerExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);

        Rows aa = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();

    }

    @API(title = "客户导入", apiversion = R.ID20221110135304.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String importExcel() throws YosException {

        ExcelFactory e;
        try {

            // 华为云
            e = getExcelAttachment(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            ArrayList<String> keys = new ArrayList<>();
            ArrayList<String> sqlList = new ArrayList<>();
            keys.add("enterprisename");
            keys.add("sa_customerpoolid");
            keys.add("source");
            keys.add("type");
            keys.add("province");
            keys.add("city");
            keys.add("county");
            keys.add("address");
            keys.add("contact");
            keys.add("phonenumber");

            Rows rows = e.getSheetRows(0, keys, 2);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            // 手机号码的格式：第一位只能为1，第二位可以是3，4，5，7，8，第三位到第十一位可以为0-9中任意一个数字
            String regex = "^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17[013678])|(18[0,5-9]))\\d{8}$";
            for (Row row : rows) {
                StringBuffer err = new StringBuffer();
                if (StringUtils.isEmpty(row.getString("enterprisename"))) {
                    iserr = true;
                    err.append("错误信息:客户名称不能为空;");
                }
                if (StringUtils.isEmpty(row.getString("sa_customerpoolid"))) {
                    iserr = true;
                    err.append("错误信息:客户池ID不能为空;");
                } else if (dbConnect.runSqlQuery("select sa_customerpoolid from sa_customerpool where poolname='"
                        + row.getString("sa_customerpoolid") + "' and siteid='" + siteid + "'").isEmpty()) {
                    iserr = true;
                    err.append("错误信息:客户池ID不存在;");
                    row.put("sa_customerpoolid", -1);
                }
//                if (!row.getString("phonenumber").matches(regex)) {
//                    iserr = true;
//                    err.append("错误信息:手机号格式不正确");
//                }
                if (err.length() > 0) {
                    row.put("msg", err);
                    rowserr.add(row);
                } else {
                    rowssuc.add(row);
                }

            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("客户导入错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("enterprisename");
                colNameList.add("sa_customerpoolid");
                colNameList.add("source");
                colNameList.add("type");
                colNameList.add("province");
                colNameList.add("city");
                colNameList.add("county");
                colNameList.add("address");
                colNameList.add("contact");
                colNameList.add("phonenumber");
                colNameList.add("msg");
                keytypemap.put("enterprisename", String.class);
                keytypemap.put("sa_customerpoolid", String.class);
                keytypemap.put("source", String.class);
                keytypemap.put("type", String.class);
                keytypemap.put("province", String.class);
                keytypemap.put("city", String.class);
                keytypemap.put("county", String.class);
                keytypemap.put("putress", String.class);
                keytypemap.put("contact", String.class);
                keytypemap.put("phonenumber", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    Long sys_enterpriseid = createTableID("sys_enterprise");
                    SQLFactory sqlFactoryupload = new SQLFactory(this, "客户企业档案新增");
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("sys_enterpriseid", sys_enterpriseid);
                    sqlFactoryupload.addParameter("province", row.getString("province"));
                    sqlFactoryupload.addParameter("city", row.getString("city"));
                    sqlFactoryupload.addParameter("county", row.getString("county"));
                    sqlFactoryupload.addParameter("address", row.getString("address"));
                    sqlFactoryupload.addParameter("enterprisename", row.getString("enterprisename"));
                    sqlFactoryupload.addParameter("abbreviation", "");
                    sqlFactoryupload.addParameter("taxno", "");
                    sqlFactoryupload.addParameter("grade", "null");
                    sqlFactoryupload.addParameter("industry", "null");
                    sqlFactoryupload.addParameter("contact", row.getString("contact"));
                    sqlFactoryupload.addParameter("phonenumber", row.getString("phonenumber"));
                    sqlFactoryupload.addParameter("telephone", row.getString("phonenumber"));
                    sqlList.add(sqlFactoryupload.getSQL());

                    sqlFactoryupload = new SQLFactory(this, "客户新增");

                    Long sa_customersid = createTableID("sa_customers");


                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("customergrade", "普通客户");
                    sqlFactoryupload.addParameter("ispublic", 1);
                    sqlFactoryupload.addParameter("sa_customersid", sa_customersid);
                    //上级客户ID
                    sqlFactoryupload.addParameter("parentid", "null");
                    //合作企业档案ID
                    sqlFactoryupload.addParameter("sys_enterpriseid", sys_enterpriseid);
                    //客户类型
                    sqlFactoryupload.addParameter("type", row.getString("type"));
                    //客户池（公海池）ID
                    Rows sa_customerpoolRows = dbConnect.runSqlQuery("select sa_customerpoolid from sa_customerpool where poolname='"
                            + row.getString("sa_customerpoolid") + "' and siteid='" + siteid + "'");
                    if (!sa_customerpoolRows.isEmpty()) {
                        sqlFactoryupload.addParameter("sa_customerpoolid", sa_customerpoolRows.get(0).getString("sa_customerpoolid"));
                    } else {
                        sqlFactoryupload.addParameter("sa_customerpoolid", 0);
                    }
                    sqlFactoryupload.addParameter("source", row.getString("source"));
                    //客户来源
                    sqlFactoryupload.addParameter("name", row.getString("enterprisename"));
                    sqlFactoryupload.addParameter("province", row.getString("province"));
                    sqlFactoryupload.addParameter("city", row.getString("city"));
                    sqlFactoryupload.addParameter("county", row.getString("county"));
                    sqlFactoryupload.addParameter("address", row.getString("address"));
                    sqlFactoryupload.addParameter("phonenumber", row.getString("phonenumber"));
                    sqlList.add(sqlFactoryupload.getSQL());
                    sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "导入", "导入公海客户" + row.getString("enterprisename")).getSQL());


//                    ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sa_customers", sa_customersid).toArrayList("userid", new ArrayList<>());
                    Executor.sendEml(this, "customers_add", sa_customersid, siteid, getUsers(sa_customerpoolRows.get(0).getLong("sa_customerpoolid")));
                }
            }


            if (sqlList.size() > 0) {
                dbConnect.runSqlUpdate(sqlList);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    public ArrayList<Long> getUsers(Long sa_customerpoolid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "查询范围内的账号");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_customerpoolid", sa_customerpoolid);
        return dbConnect.runSqlQuery(sqlFactory).toArrayList("userid", new ArrayList<>());
    }

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        CustomerExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        CustomerExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 2);
            //System.err.println(keylist);
            //System.err.println(datarows.getFieldList());
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                //System.err.println(keylist.get(i1));
                Class fieldclazztype = null;
                try {
                    String fieldname = keylist.get(i1);
                    fieldclazztype = datarows.getFieldMeta(fieldname).getFieldtype();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger(keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong(keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat(keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble(keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString(keylist.get(i1)));
                }
                if (i1 == 10) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    @API(title = "变更经销商", apiversion = R.ID20221103093202.v1.class)
    public String changeAgent() throws YosException {
        String tablename = "sa_customers";
        Long sa_customersid = content.getLong("sa_customersid");
        //经销商团队负责人id
        Long userid = content.getLong("userid");

        String sql = "SELECT sys_datatagid from sys_datatag WHERE ownertable = 'sa_customers' and ownerid = " + sa_customersid + " and siteid='" + siteid + "' AND tag='直销'";
        Rows rows = dbConnect.runSqlQuery(sql);

        ArrayList<String> sqlList = new ArrayList<>();
        if (rows.isEmpty()) {
            if (dbConnect.runSqlQuery("SELECT 1 from sys_datatag WHERE ownertable = 'sa_customers' and ownerid = " + sa_customersid + " and siteid='" + siteid + "' AND tag='经销'").isEmpty()) {
                DataTag.createTag(this, tablename, sa_customersid, "经销");
            }
        } else {
            Long sys_datatagid = rows.get(0).getLong("sys_datatagid");
            dbConnect.runSqlUpdate("UPDATE sys_datatag SET tag='经销' WHERE sys_datatagid =" + sys_datatagid);
        }


        sqlList.add("delete from sys_datateam WHERE ownertable = 'sa_customers' and siteid = '" + siteid + "' and ownerid = " + sa_customersid + " AND sys_enterpriseid >0");
        //数据团队新增
        sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_customersid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_customers", sa_customersid, userid));
        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }

    @API(title = "撤回", apiversion = R.ID20221103105002.v1.class)
    public String revoke() throws YosException {
        String tablename = "sa_customers";
        Long sa_customersid = content.getLong("sa_customersid");
        //1：变更为经销，2：变更为直销
        Long type = content.getLong("type");
        if (content.containsKey("userid")) {
            userid = content.getLong("userid");
        }

        String oldTag = type == 1 ? "直销" : "经销";
        String newTag = type == 1 ? "经销" : "直销";

        String sql = "SELECT sys_datatagid from sys_datatag WHERE ownertable = 'sa_customers' and ownerid = " + sa_customersid + " and siteid='" + siteid + "' AND tag='" + oldTag + "'";
        Rows rows = dbConnect.runSqlQuery(sql);

        if (rows.isEmpty()) {
            if (dbConnect.runSqlQuery("SELECT 1 from sys_datatag WHERE ownertable = 'sa_customers' and ownerid = " + sa_customersid + " and siteid='" + siteid + "' AND tag='" + newTag + "'").isEmpty()) {
                DataTag.createTag(this, tablename, sa_customersid, newTag);
            }
        } else {
            Long sys_datatagid = rows.get(0).getLong("sys_datatagid");
            dbConnect.runSqlUpdate("UPDATE sys_datatag SET tag='" + newTag + "' WHERE sys_datatagid =" + sys_datatagid);
        }
        ArrayList<String> sqlList = new ArrayList<>();
        if (type == 1) {
            sqlList.add("delete from sys_datateam WHERE ownertable = 'sa_customers' and siteid = '" + siteid + "' and ownerid = " + sa_customersid + " AND sys_enterpriseid >0");
        } else if (type == 2) {
            sqlList.add("delete from sys_datateam WHERE ownertable = 'sa_customers' and siteid = '" + siteid + "' and ownerid = " + sa_customersid);
        }

        //数据团队新增
        sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_customersid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_customers", sa_customersid, userid));

        sqlList.add(DataContrlLog.createLog(this, "sa_customers", sa_customersid, "撤回", "客户撤回" + sa_customersid).getSQL());
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "查询客户相关的线索", apiversion = R.ID20221208163302.v1.class)
    @CACHEING
    public String selectClueList() throws YosException {

        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.notes like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "线索列表");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_customersid", content.getLong("sa_customersid"));
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "客户查重", apiversion = R.ID20221208172002.v1.class)
    public String checkRepeat() throws YosException {

        Long sa_customersid = content.getLong("sa_customersid");
        SQLFactory sqlFactory = new SQLFactory(this, "客户查重");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("enterprisename", content.getStringValue("enterprisename"));
        sqlFactory.addParameter("taxno", content.getStringValue("taxno"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        sqlFactory.addParameter("sa_customersid", sa_customersid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "终端客户档案", apiversion = R.ID2025090809202003.v1.class)
    @CACHEING
    public String queryMdCustomers() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("sys_enterpriseid") && !"".equals(whereObject.getString("sys_enterpriseid"))) {
                where.append(" and(");
                where.append("t2.sys_enterpriseid ='").append(whereObject.getString("sys_enterpriseid")).append("' ");
                where.append(" or t4.sys_enterpriseid ='").append(whereObject.getString("sys_enterpriseid")).append("' ");
                where.append(")");
            }
        }
        QuerySQL querySQL;
        querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid","name","phonenumber","province","city","county","address","createdate","sa_agentsid","sa_agentsid_to");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t2", "t1.sa_agentsid = t2.sa_agentsid AND t1.siteid = t2.siteid","agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t2.sys_enterpriseid = t3.sys_enterpriseid AND t2.siteid = t3.siteid","enterprisename","abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t1.sa_agentsid_to = t4.sa_agentsid AND t1.siteid = t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t4.sys_enterpriseid = t5.sys_enterpriseid AND t4.siteid = t3.siteid");
        querySQL.addQueryFields("agentnum_to","t4.agentnum");
        querySQL.addQueryFields("enterprisename_to","t5.enterprisename");
        querySQL.addQueryFields("abbreviation_to","t5.abbreviation");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("(ifnull(t1.sa_agentsid,0)!=0 or ifnull(t1.sa_agentsid_to,0)!=0)");

        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }




    @API(title = "经销商查询终端客户档案", apiversion = R.ID2025090909115603.v1.class)
    @CACHEING
    public String queryAgentMdCustomers() throws YosException {
        long sa_agentsid=0;
        Rows agentrows = dbConnect.runSqlQuery("select sa_agentsid from sa_agents where sys_enterpriseid="+sys_enterpriseid);
        if(agentrows.isNotEmpty()){
            sa_agentsid=agentrows.get(0).getLong("sa_agentsid");
        }
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        QuerySQL querySQL;
        querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid","name","phonenumber","province","city","county","address","createdate","sa_agentsid","sa_agentsid_to");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t2", "t1.sa_agentsid = t2.sa_agentsid AND t1.siteid = t2.siteid","agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t3", "t2.sys_enterpriseid = t3.sys_enterpriseid AND t2.siteid = t3.siteid","enterprisename","abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t1.sa_agentsid_to = t4.sa_agentsid AND t1.siteid = t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t4.sys_enterpriseid = t5.sys_enterpriseid AND t4.siteid = t3.siteid");
        querySQL.addQueryFields("agentnum_to","t4.agentnum");
        querySQL.addQueryFields("enterprisename_to","t5.enterprisename");
        querySQL.addQueryFields("abbreviation_to","t5.abbreviation");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("(ifnull(t1.sa_agentsid,0)!=0 or ifnull(t1.sa_agentsid_to,0)!=0)");
        querySQL.setWhere("(t1.sa_agentsid="+sa_agentsid+" or t1.sa_agentsid_to="+sa_agentsid+")");

        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    /**
     * 获取客户名称
     *
     * @param sa_customersid
     * @return
     * @throws YosException
     */
    public String getCustomerEnterprisename(Long sa_customersid) throws YosException {
        String enterprisename = "";
        Rows rows = dbConnect.runSqlQuery("SELECT enterprisename FROM sys_enterprise WHERE sys_enterpriseid in (SELECT sys_enterpriseid from sa_customers WHERE sa_customersid = " + sa_customersid + " ) and siteid = '" + siteid + "'");
        if (rows.isNotEmpty()) {
            enterprisename = rows.get(0).getString("enterprisename");
        }

        return enterprisename;
    }

}