YOS_PRSX/src/custom/restcontroller/sale/customer/Customer.java

package restcontroller.sale.customer;

import beans.contract.Contract;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.hr.Hr;
import beans.phonebook.PhoneBook;
import beans.proxy.Proxy;
import beans.recycle.Recycle;
import beans.salearea.SaleArea;
import beans.salesfees.SalesFees;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.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.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;

import static beans.customers.Customers.getRepeatCustomers;
import static beans.datateam.DataTeam.getProxyUserids;

@API(title = "客户")
public class Customer extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Customer(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增或更新", apiversion = R.ID20221012163902.v1.class, intervaltime = 2000)
    @CACHEING_CLEAN(apiversions = {R.ID20221022165503.v1.class, R.ID20221011133602.class, R.ID20221013102602.class,
            R.ID20221012164402.class, R.ID20221014164702.class, R.ID20220920083901.class, R.ID20220929085401.class,
            R.ID20220920083901.v1.class})
    public String insertOrUpdate() throws YosException {
        String tablename = "sa_customers";
        Long sa_customersid = content.getLong("sa_customersid");
        String type = content.getString("type");
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        String enterprisename = content.getString("enterprisename");
        String address = content.getStringValue("address");
        String province = content.getStringValue("province");
        String city = content.getStringValue("city");
        String county = content.getStringValue("county");
        Long saler_userid = content.getLongValue("saler_userid");
        if (userInfo.isSaler()) {
            saler_userid = userid;
        }
        ArrayList<String> sqlList = new ArrayList<>();
        //创建企业档案
        if (sys_enterpriseid <= 0) {
            sys_enterpriseid = createTableID("sys_enterprise");
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sys_enterprise");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(sys_enterpriseid);
            insertSQL.setValue("province", province);
            insertSQL.setValue("city", city);
            insertSQL.setValue("county", county);
            insertSQL.setValue("address", address);
            insertSQL.setValue("enterprisename", enterprisename);
            sqlList.add(insertSQL.getSQL());
        } else {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sys_enterprise");
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(sys_enterpriseid);
            updateSQL.setValue("province", province);
            updateSQL.setValue("city", city);
            updateSQL.setValue("county", county);
            updateSQL.setValue("address", address);
            updateSQL.setValue("enterprisename", enterprisename);
            sqlList.add(updateSQL.getSQL());
        }

        boolean isAdd = false;
        if (sa_customersid <= 0) {
            isAdd = true;
            Rows stagenameRows = dbConnect.runSqlQuery("SELECT stagename from sa_devstage WHERE siteid='" + siteid + "' order by sequence  LIMIT 1");
            sa_customersid = createTableID(tablename);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_customers");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(sa_customersid);
            insertSQL.setValue("billno", createBillCode("hospital"));
            insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
            insertSQL.setValue("name", enterprisename);
            insertSQL.setValue("type", type);
            insertSQL.setValue("grade", content.getStringValue("grade"));
            insertSQL.setValue("host", content.getLongValue("host"));
            insertSQL.setValue("room", content.getLongValue("room"));
            insertSQL.setValue("bed", content.getLongValue("bed"));
            insertSQL.setValue("scale", content.getLongValue("scale"));
            insertSQL.setValue("sa_saleareaid", content.getLongValue("sa_saleareaid"));
            insertSQL.setValue("province", province);
            insertSQL.setValue("city", city);
            insertSQL.setValue("county", county);
            insertSQL.setValue("address", address);
            insertSQL.setValue("stagename", stagenameRows.isNotEmpty() ? stagenameRows.get(0).getString("stagename") : "");
            sqlList.add(insertSQL.getSQL());
        } else {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_customers");
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(sa_customersid);
            updateSQL.setValue("name", enterprisename);
            updateSQL.setValue("type", type);
            updateSQL.setValue("grade", content.getStringValue("grade"));
            updateSQL.setValue("host", content.getLongValue("host"));
            updateSQL.setValue("room", content.getLongValue("room"));
            updateSQL.setValue("bed", content.getLongValue("bed"));
            updateSQL.setValue("scale", content.getLongValue("scale"));
            updateSQL.setValue("sa_saleareaid", content.getStringValue("sa_saleareaid"));
            sqlList.add(updateSQL.getSQL());
        }

        if (isAdd) {
            //标签
            sqlList.add(DataTag.createTagSql(this, "sa_customers", sa_customersid, type));
            //数据团队新增
            sqlList.addAll(DataTeam.createTeamSQL(this, tablename, sa_customersid, saler_userid));
            sqlList.add(DataContrlLog.createLog(this, tablename, sa_customersid, "新建", "新建医院成功").getSQL());
            //标记数据来源
            DataExtend.markDataFrom(this, "sa_customers", sa_customersid);
        } else {
            sqlList.add(DataContrlLog.createLog(this, tablename, sa_customersid, "编辑", "编辑医院成功").getSQL());
        }

        //新增时插入财务信息和联系人信息
        if (isAdd) {
            //添加地址
            Long contactsid = createTableID("sys_enterprise_contacts");
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sys_enterprise_contacts");
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("contactsid", contactsid);
            insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
            insertSQL.setValue("province", province);
            insertSQL.setValue("city", city);
            insertSQL.setValue("county", county);
            insertSQL.setValue("address", address);
            insertSQL.setValue("workaddress", 1);
            insertSQL.setValue("isdefault", 1);
            insertSQL.setValue("isprimary", 1);
            sqlList.add(insertSQL.getSQL());

        }
        content.put("sa_customersid", sa_customersid);
        String time = userInfo.getDateTime_Str();
        dbConnect.runSqlUpdate(sqlList);

        DataTag.deleteTag(this, "sa_customers", sa_customersid, "疑似重复");
        Rows teamRows = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_customers", sa_customersid);

        Rows leaderRows = teamRows.toRowsMap("isleader").get("1");
        Long leaderuserid = leaderRows.isNotEmpty() ? leaderRows.get(0).getLong("userid") : 0;
        Long departmentid = getDepartment(leaderuserid).getLong("departmentid");
        ArrayList<Long> userids = new ArrayList<>();
        userids.add(leaderuserid);
        userids.addAll(Proxy.getProxyUserids(this));
        userids.add(Hr.getReportUserid(this, leaderuserid));
        userids.addAll(Hr.getLeaderRows(this, departmentid).toArrayList("userid", new ArrayList<Long>()));
        JSONObject extradata = new JSONObject();
        extradata.put("extraUserList", userids);

        //客户查重
        if (isAdd) {
            ArrayList<Long> repeatIds = getRepeatCustomers(this, sa_customersid);
            repeatIds.add(120L);
            for (Object obj : repeatIds) {
                Long id = Long.valueOf(obj.toString());
                JSONObject extradata1 = new JSONObject();
                extradata1.put("extraUserList", DataTeam.queryTeamRowWithoutHeadpic(this, "sa_customers", id).toArrayList("userid", new ArrayList<>()));
                Executor.sendEml_controller(this, "customers_repeat", sa_customersid, extradata1);
            }

            Executor.sendEml_controller(this, "customers_add_my", sa_customersid, extradata);
        } else {
            String changedValue = getChangedValue(sa_customersid, time);
            if (StringUtils.isEmpty(changedValue)) {
                DataContrlLog.createLog(this, "sa_customers", sa_customersid, "编辑", "编辑医院" + enterprisename + "(" + sa_customersid + ")，无字段值更新").insert();
            } else {
                DataContrlLog.createLog(this, "sa_customers", sa_customersid, "编辑", "编辑医院" + enterprisename + "(" + sa_customersid + ");" + changedValue).insert();
                Executor.sendEml_controller(this, "customers_update_my", sa_customersid, extradata);
            }
        }

        return selectDetail();
    }

    public String getChangedValue(Long sa_customersid, String time) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "查询客户编辑操作记录值变化");
        sqlFactory.addParameter("sa_customersid", sa_customersid);
        sqlFactory.addParameter("time", time);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
        if (rows.isNotEmpty()) {
            return rows.get(0).getString("content");
        }
        return "";
    }


    @API(title = "查询业务员负责的经销商", apiversion = R.ID20230214162602.v1.class)
    public String getAgentList() 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.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        if (content.containsKey("hrid")) {
            hrid = content.getLongValue("hrid");
        }
        Rows rows = dbConnect.runSqlQuery("SELECT userid from sys_hr WHERE hrid=" + hrid + " and siteid='" + siteid + "'");
        if (rows.isNotEmpty()) {
            userid = rows.get(0).getLong("userid");
        }
        ArrayList<Long> proxyUserids = getProxyUserids(this, userid, true);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("userid", proxyUserids);
        ArrayList<Long> hrids = querySQL.query().toArrayList("hrid", new ArrayList<>());


        SQLFactory sqlFactory = new SQLFactory(this, "查询业务员负责的经销商", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("hrid", hrids);
        sqlFactory.addParameter_SQL("where", where);
        rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

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

    @API(title = "客户管理的合同", apiversion = R.ID20230214163402.v1.class)
    @CACHEING
    public String list() throws YosException {


        String tablename = "sa_contract";
        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.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.projectnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.projectname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }


        }
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_contract", "sa_contractid", "createby", "createdate", "changeby",
                "changedate", "sa_projectid", "billno", "type", "begdate", "enddate", "sys_enterpriseid", "discountrate", "saler_hrid", "signdate",
                "signby", "calculatemodel", "orderratio", "productdiscount", "remarks", "title", "typemx", "ascription_contractid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid and t2.siteid = t1.siteid", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_project", "t3", "t3.sa_projectid = t1.sa_projectid and t3.siteid = t1.siteid", "projectname", "projectnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t4", "t4.hrid = t1.saler_hrid and t4.siteid = t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_contract", "t5", "t1.siteid = t5.siteid and t1.ascription_contractid = t5.sa_contractid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t6", "t5.siteid = t6.siteid and t5.sys_enterpriseid = t6.sys_enterpriseid");

        querySQL.addQueryFields("status", "if(t1.enddate < current_date and t1.status = '审核', '已过期', t1.status)");
        querySQL.addQueryFields("salername", "t4.name");
        querySQL.addQueryFields("source", "''");
        querySQL.addQueryFields("ascription_enterprisename", "t6.enterprisename");

        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.sys_enterpriseid", sys_enterpriseid);
        querySQL.setWhere("t1.deleted", 0);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);

        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("sa_contractid", new ArrayList<>());
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, tablename, ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, tablename, ids, true);
        //查询价格
        RowsMap signAmountRowsMap = Contract.getContractSignAmount(this, ids);

        for (Row row : rows) {
            Long id = row.getLong("sa_contractid");
            //非系统标签
            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 (signAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("signamount", signAmountRowsMap.get(id.toString()).get(0).getBigDecimal("signamount").toPlainString());
            } else {
                row.put("signamount", 0);
            }

        }

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

    }

    @API(title = "数据状态变更", apiversion = R.ID20221012164002.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String changeDataStatus() throws YosException {
        String tablename = "sa_customers";
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");
        Long datastatus = content.getLong("datastatus");
        if (datastatus == 1) {
            for (Object obj : sa_customersids) {
                Long sa_customersid = Long.valueOf(obj.toString());
                SQLFactory sqlFactory = new SQLFactory(this, "查询客户管理审核通过的合同");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_customersid", sa_customersid);
                if (dbConnect.runSqlQuery(sqlFactory).isNotEmpty()) {
                    return getReturnObject().setCode("0057", new String[0]).toString(); //存在审核通过的合同，无法作废
                }
            }
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents", "*");
            querySQL.setTableAlias("t1");
            querySQL.setSiteid(siteid);
            querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid=t2.siteid and t1.sys_enterpriseid=t2.sys_enterpriseid");
            querySQL.addJoinTable(JOINTYPE.inner, "sa_customers", "t3", "t2.siteid=t3.siteid and t2.sys_enterpriseid=t3.sys_enterpriseid");
            querySQL.addJoinTable(JOINTYPE.inner, "sys_dataextend", "t4", "t2.siteid=t4.siteid and t2.sys_enterpriseid=t4.ownerid and t4.ownertable='sys_enterprise'");
            querySQL.setWhere("(t4.erpagentnum !='' and t4.erpagentnum is not null)");
            querySQL.setWhere("t1.status='启用'");
            querySQL.setWhere("t3.sa_customersid", sa_customersids.toArray());
            querySQL.getSQL();
            Rows query = querySQL.query();
            if (query.isNotEmpty()) {
                return getReturnObject().setCode("0058", new String[0]).toString(); //存在合作伙伴，无法作废
            }
        }

        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("datastatus", datastatus);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        sqlList.add(sqlFactory.getSQL());

        if (datastatus == 1) {
            //新增删除或作废原因
            DataExtend.createDeleteReasonSql(this, tablename, sa_customersids.toJavaList(Long.class), content.getStringValue("deletereason"));
        }
        //操作记录
        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            if (datastatus == 0) {
                sqlList.add(DataContrlLog.createLog(this, tablename, id, "正常", "恢复正常").getSQL());
            }
            if (datastatus == 1) {
                sqlList.add(DataContrlLog.createLog(this, tablename, id, "作废", "作废客户至回收站").getSQL());

                Rows leaderRows = DataTeam.getLeader(this, "sa_customers", id);
                ArrayList<String> userids = dbConnect.runSqlQuery("SELECT userid from sys_hr WHERE isleader=1 and siteid='" + siteid + "' and departmentid=" + departmentid).toArrayList("userid");
                JSONObject extradata = new JSONObject();
                if (leaderRows.isNotEmpty()) {
                    userids.add(String.valueOf(leaderRows.get(0).getLong("userid")));
                }
                extradata.put("extraUserList", userids);
                Executor.sendEml_controller(this, "customers_invalid", id, extradata);

                //客户查重
                ArrayList<Long> repeatIds = getRepeatCustomers(this, id);
                DataTag.deleteTag(this, "sa_customers", id, "疑似重复");
                if (repeatIds.size() == 1) {
                    Rows tempRows = dbConnect.runSqlQuery("SELECT sa_customersid  from sa_customers WHERE sys_enterpriseid=" + repeatIds.get(0) + " and  siteid='" + siteid + "'");
                    if (tempRows.isNotEmpty()) {
                        DataTag.deleteTag(this, "sa_customers", tempRows.get(0).getLong("sa_customersid"), "疑似重复");
                    }

                }
            }
            if (datastatus == 2) {
                sqlList.add(DataContrlLog.createLog(this, tablename, id, "锁定", "锁定成功").getSQL());
            }

        }


        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "交易状态变更", apiversion = R.ID20221012164102.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String changeTradingStatus() throws YosException {
        String tablename = "sa_customers";
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");
        String tradingstatus = content.getString("tradingstatus");

        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("tradingstatus", tradingstatus);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        sqlList.add(sqlFactory.getSQL());
        //操作记录
        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, tablename, id, "交易状态变更", "交易状态变更为" + tradingstatus).getSQL());
        }

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

    @API(title = "详细", apiversion = R.ID20221012164302.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(false));

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

        RowsMap leaderRows = DataTeam.getLeader(this, "sa_customers", rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        //标签
        HashMap<Long, ArrayList<String>> tagList = DataTag.queryTag(this, "sa_customers", ids, false);
        //系统标签
        HashMap<Long, ArrayList<String>> sysTagList = DataTag.queryTag(this, "sa_customers", ids, true);
        for (Row row : rows) {
            Long id = row.getLong("sa_customersid");
            row.put("leader", leaderRows.get(row.getString("sa_customersid")));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>();
            if (row.getString("agentsstatus").equals("禁用") || row.getString("agentsstatus").equals("作废")) {
                tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
                sys_tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
            }
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            //预估手术总量
            row.putIfAbsent("totalop", 0);
            //关键人
            row.putIfAbsent("keyperson", "");
            //签约经销商
            row.putIfAbsent("signagent", "");
        }

        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        Long sys_enterpriseid = rows.isNotEmpty() ? row.getLong("sys_enterpriseid") : 0;
        Rows addressRows = dbConnect.runSqlQuery("SELECT * from  sys_enterprise_contacts WHERE isdefault=1 and workaddress=1 and deleted=0 and sys_enterpriseid=" + sys_enterpriseid + " and siteid='" + siteid + "'");
        row.put("province", addressRows.isNotEmpty() ? addressRows.get(0).getString("province") : new Row());
        row.put("city", addressRows.isNotEmpty() ? addressRows.get(0).getString("city") : new Row());
        row.put("county", addressRows.isNotEmpty() ? addressRows.get(0).getString("county") : new Row());
        row.put("address", addressRows.isNotEmpty() ? addressRows.get(0).getString("address") : new Row());


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

    @API(title = "医院列表", apiversion = R.ID20221012164402.v1.class)
    public String selectList() throws YosException, IOException {
        //  1：我负责的；2：我参与的；3：我下属负责的；4：我下属参与的
        int type = content.getIntValue("type");
        Long deleted = content.getLongValue("deleted");

        String tablename = "sa_customers";
        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.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t8.depname 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(" 00:00:00' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and(");
                where.append("t1.createdate <='").append(whereObject.getString("enddate")).append(" 23:59:59' ");
                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("grade") && !"".equals(whereObject.getString("grade"))) {
                where.append(" and(");
                where.append("t1.grade ='").append(whereObject.getString("grade")).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("tag") && !whereObject.getJSONArray("tag").isEmpty()) {
                for (Object o : whereObject.getJSONArray("tag")) {
                    where.append(" and exists(select 1 from sys_datatag WHERE ownertable = 'sa_customers' and siteid='").append(siteid).append("' ");
                    where.append(" and tag='").append(o).append("' and t1.sa_customersid=ownerid ");
                    where.append(")");
                }
            }
            //开发阶段
            if (whereObject.containsKey("stagename") && !"".equals(whereObject.getString("stagename"))) {
                where.append(" and(");
                where.append("t1.stagename ='").append(whereObject.getString("stagename")).append("' ");
                where.append(")");
            }
            //营销区域

            if (whereObject.containsKey("sa_saleareaid") && !"".equals(whereObject.getString("sa_saleareaid"))) {
                Long sa_saleareaid = whereObject.getLong("sa_saleareaid");
                ArrayList<Long> sa_saleareaids = SaleArea.getSubSaleAreaIds(this, sa_saleareaid);
                sa_saleareaids.add(-1L);
                Rows rows = dbConnect.runSqlQuery("SELECT DISTINCT t2.userid from sa_salearea_hr t1 " +
                        "inner join sys_hr t2 ON t2.hrid=t1.hrid and t2.siteid=t1.siteid " +
                        "WHERE  t1.siteid='" + siteid + "' and t1.sa_saleareaid in(" + StringUtils.join(sa_saleareaids, ",") + ") and t2.userid>0");
                HashSet<Long> userIds = new HashSet<>(rows.toArrayList("userid", new ArrayList<>()));
                userIds.add(-1L);
                where.append(" and(");
                where.append(" t6.userid in ").append(userIds.toString().replace("[", "(").replace("]", ")"));
                where.append(")");
            }
        }
        if (deleted == 1) {
            where.append(" and ( t1.datastatus =1 )");
        } else {
            where.append(" and ( t1.datastatus in (0,2) )");
        }

        String where2 = " 1=1 ";
        if (deleted == 0) {
            if (type == 0) {
                where2 = "(" + DataTeam.getDataWhereStr(this, tablename, "t1", 1)
                        + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 2) + ")";
            } else {
                where2 = DataTeam.getDataWhereStr(this, tablename, "t1", type);
            }
        } else {
            where2 = Recycle.getWhereSql(this, tablename, "t1.sa_customersid");
        }

        boolean isExport = content.getBoolean("isExport");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid", "createby", "createdate",
                "sys_enterpriseid", "status", "type", "tradingstatus", "datastatus", "billno", "province", "city", "county", "address", "stagename", "grade");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid AND t2.siteid = t1.siteid", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t3", "t3.sa_saleareaid = t1.sa_saleareaid AND t3.siteid = t1.siteid", "areaname");

        QuerySQL t5 = SQLFactory.createQuerySQL(this, "sys_datafollowup", "ownerid");
        t5.setWhere("ownertable", "sa_customers");
        t5.setSiteid(siteid);
        t5.addGroupBy("ownerid");
        t5.addQueryFields("followdate", "max(createdate)");
        t5.setRoleDataLimit(false);
        querySQL.addJoinTable(JOINTYPE.left, t5, "t5", "t5.ownerid = t1.sa_customersid", "followdate");
        querySQL.addJoinTable(JOINTYPE.left, "sys_datateam", "t6", "t6.ownerid = t1.sa_customersid and t6.siteid = t1.siteid and t6.ownertable = 'sa_customers' and t6.isleader = 1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t7", "t7.userid = t6.userid and t7.siteid = t6.siteid ");
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t8", "t8.departmentid = t7.departmentid and t8.siteid = t7.siteid ");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere(where2);
        if (isExport) {
            querySQL.setPage(999999, 1);
        } else {
            querySQL.setPage(pageSize, pageNumber);
        }
        querySQL.setOrderBy(pageSorting);

        querySQL.setRoleDataLimit(false);
        querySQL.withDeleteData(true);
        Rows rows = querySQL.query();
        RowsMap leaderRows = DataTeam.getLeaderWithoutHeadpic(this, "sa_customers", rows.toArrayList("sa_customersid")).toRowsMap("ownerid");
        ArrayList<Long> ids = rows.toArrayList("sa_customersid", new ArrayList<>());
        //标签
        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)));
            if (leaderRows.get(String.valueOf(id)).isNotEmpty()) {
                row.put("name", leaderRows.get(String.valueOf(id)).get(0).getString("name"));
                row.put("depname", leaderRows.get(String.valueOf(id)).get(0).getString("depname"));
            }
            ArrayList<String> tag = tagList.get(id) != null ? tagList.get(id) : new ArrayList<String>();
            ArrayList<String> sys_tag = sysTagList.get(id) != null ? sysTagList.get(id) : new ArrayList<String>();
            if (row.getString("agentsstatus").equals("禁用") || row.getString("agentsstatus").equals("作废")) {
                tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
                sys_tag.removeAll(row.getJSONArray("agentstag").toJavaList(String.class));
            }
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            row.putIfAbsent("followdate", "");
            //预估手术总量
            row.putIfAbsent("totalop", 0);
            //关键人
            row.putIfAbsent("keyperson", "");
            //签约经销商
            row.putIfAbsent("signagent", "");
        }
        if (isExport) {
            ExcelFactory excelFactory = new ExcelFactory("医院管理导出");

            XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("医院管理");
            XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
            ExportExcel.setBatchDetailSheetColumn(sheet);// 设置工作薄列宽
            XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle(xssfFWorkbook);
            XSSFCellStyle titleCellStyle2 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
            ExportExcel.download(sheet, titleCellStyle1, titleCellStyle2, rows);// 写入标题

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

    @API(title = "公海客户列表", apiversion = R.ID20221014164702.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(")");
            }
        }

        boolean isExport = content.getBoolean("isExport");
        SQLFactory sqlFactory = new SQLFactory(this, "公海客户列表", pageSize, pageNumber, pageSorting);
        if (isExport) {
            sqlFactory = new SQLFactory(this, "公海客户列表");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);


        RowsMap leaderRows = DataTeam.getLeaderWithoutHeadpic(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.getFieldList().remove("province");
            rows.getFieldList().remove("city");
            rows.getFieldList().remove("county");
            rows.getFieldList().remove("address");
            rows.getFieldList().remove("taxno");
            rows.getFieldList().remove("phonenumber");
            rows.getFieldList().remove("contact");


            Rows uploadRows = uploadExcelToObs("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.ID20221014101002.v1.class)
    @Deprecated
    public String selectParentCustomers() 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.createby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "上级客户列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter_SQL("where2", DataTeam.getDataWhereStr(this, "sa_customers", "t1", 6));
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

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

    @API(title = "转移", apiversion = R.ID20221014102502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String transfer() throws YosException {
        String tablename = "sa_customers";

        Long sa_customerpoolid = content.getLong("sa_customerpoolid");
        JSONArray sa_customersids = content.getJSONArray("sa_customersids");

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

        SQLFactory sqlFactory = new SQLFactory(this, "客户转移");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_customersid", sa_customersids.toArray());
        sqlFactory.addParameter("sa_customerpoolid", sa_customerpoolid);
        sqlList.add(sqlFactory.getSQL());

        //操作记录
        for (Object obj : sa_customersids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, tablename, id, "转移", "客户转移").getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

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

    @API(title = "退回", apiversion = R.ID20221014102602.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String goback() throws YosException {
        String tablename = "sa_customers";
        Long sa_customerpoolid = content.getLong("sa_customerpoolid");
        Long sa_customersid = content.getLong("sa_customersid");
        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_customers WHERE sa_customersid = " + sa_customersid + " and siteid ='" + siteid + "'");
        if (rows.get(0).getLong("ispublic") == 0) {
            return getReturnObject().setCode("0059", new String[0]).toString(); //非公海客户无法退回
        }

        if (dbConnect.runSqlQuery("SELECT 1 from sa_customerpool WHERE sa_customerpoolid = " + sa_customerpoolid + " and siteid = '" + siteid + "'").isEmpty()) {
            rows = dbConnect.runSqlQuery("SELECT sa_customerpoolid from sa_customerpool WHERE isdefault =1 and siteid = '" + siteid + "'");
            if (rows.isNotEmpty()) {
                sa_customerpoolid = rows.get(0).getLong("sa_customerpoolid");
            } else {
                return getReturnObject().setCode("0060", new String[0]).toString(); //没有默认公海池，请先设置默认公海池。
            }
        }


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

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

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

        //操作记录
        sqlStr.add(DataContrlLog.createLog(this, tablename, sa_customersid, "退回", "客户退回").getSQL());

        ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_customers", sa_customersid).toArrayList("userid", new ArrayList<>());
        JSONObject extradata = new JSONObject();
        extradata.put("extraUserList", userids);
        Executor.sendEml_controller(this, "customers_return", sa_customersid, extradata);


        dbConnect.runSqlUpdate(sqlStr);
        DataTeam.updatephonebookteam(this, "sa_customers", sa_customersid);

        return getSucReturnObject().toString();
    }

    @API(title = "领取", apiversion = R.ID20221014160202.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221011133602.class, R.ID20221013102602.class, R.ID20221012164402.class, R.ID20221014164702.class})
    public String receive() throws YosException {
        String tablename = "sa_customers";
        Long sa_customersid = content.getLong("sa_customersid");

        if (DataTeam.getLeader(this, "sa_customers", sa_customersid).isNotEmpty()) {
            return getReturnObject().setCode("0061", new String[0]).toString(); //当前客户已领取
        }

        String sql = "SELECT * from sa_customerpool WHERE sa_customerpoolid in (SELECT sa_customerpoolid_source from sa_customers WHERE sa_customersid =" + sa_customersid + " and siteid='" + siteid + "') and siteid='" + siteid + "'";
        Rows customerpoolRows = dbConnect.runSqlQuery(sql);

        if (customerpoolRows.isNotEmpty()) {
            Long sa_customerpoolid = customerpoolRows.get(0).getLong("sa_customerpoolid");
            ArrayList<Long> sa_saleareaids = userInfo.getSaleAreaIds();
            sa_saleareaids.add(0L);
//            String tempSql = "SELECT 1 from sa_customerpool_auth WHERE siteid='" + siteid + "' and (hrid=" + hrid + " and departmentid = " + departmentid + " and sa_agentsid=" + sa_agentsid + " or sa_saleareaid in " + sa_saleareaids + ") and sa_customerpoolid = " + sa_customerpoolid;
//            tempSql = tempSql.replace("[", "(").replace("]", ")");
//            if (dbConnect.runSqlQuery(tempSql).isEmpty()) {
//                return getErrReturnObject().setErrMsg("未授权，无法领取").toString();
//            }
            boolean freeget = customerpoolRows.get(0).getBoolean("freeget");
            if (!freeget) {
                return getReturnObject().setCode("0062", new String[0]).toString(); //数据所属公海池不支持自由领取！
            }
        }


        ArrayList<String> sqlStr = new ArrayList<>();
        SQLFactory sqlFactory = new SQLFactory(this, "客户领取");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_customersid", sa_customersid);
        sqlStr.add(sqlFactory.getSQL());

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

        //操作记录
        sqlStr.add(DataContrlLog.createLog(this, tablename, sa_customersid, "领取", "领取公海客户" + getCustomerEnterprisename(sa_customersid)).getSQL());


        Rows contactsRows = dbConnect.runSqlQuery("select sys_phonebookid from sa_customers t1 inner join sys_enterprise_contacts t2 on t1.siteid=t2.siteid and t1.sys_enterpriseid=t2.sys_enterpriseid where t1.siteid='" + siteid + "' and t1.sa_customersid=" + sa_customersid + " and workaddress=0");
        for (Row row : contactsRows) {
            Long sys_phonebookid = row.getLong("sys_phonebookid");
            sqlStr.addAll(DataTeam.createTeamSQL(this, "sys_phonebook", sys_phonebookid, userid));
            sqlStr.addAll(DataTeam.createTeamMemberSql(this, "sys_phonebook", sys_phonebookid, userid));
        }

        dbConnect.runSqlUpdate(sqlStr);

        ArrayList<Long> userids = DataTeam.queryTeamRowWithoutHeadpic(this, "sa_customers", sa_customersid).toArrayList("userid", new ArrayList<>());
        JSONObject extradata = new JSONObject();
        extradata.put("extraUserList", userids);
        Executor.sendEml_controller(this, "customers_receive", sa_customersid, extradata);

        return getSucReturnObject().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;
    }

    @API(title = "客户系统信息", apiversion = R.ID20221019091602.v1.class)
    public String getCustomerSysInfo() throws YosException {
        Long sa_customersid = content.getLong("sa_customersid");

        Rows infoRows = dbConnect.runSqlQuery("SELECT createby,createdate,changeby,changedate,tradingstatus,datastatus FROM  sa_customers WHERE sa_customersid = " + sa_customersid + " AND siteid = '" + siteid + "'");
        Row row = new Row();
        if (infoRows.isNotEmpty()) {
            row = infoRows.get(0);
        }
        row.put("changecount", 0);
        row.put("returnreson", "");
        row.put("percentage", "100%");

        SQLFactory sqlFactory = new SQLFactory(this, "查询客户最近信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("ownerid", sa_customersid);
        infoRows = dbConnect.runSqlQuery(sqlFactory);
        if (infoRows.isNotEmpty()) {
            row.putAll(infoRows.get(0));
        } else {
            row.put("followby", "");
            row.put("followdate", "");
        }


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

    @API(title = "获取可更换负责人列表", apiversion = R.ID20221020083902.v1.class)
    public String getChangeUserList() 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(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "获取可更换负责人列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "我的公海池", apiversion = R.ID20221206195102.v1.class)
    public String myPoolList() 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(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "我的公海池", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("hrid", hrid);
        sqlFactory.addParameter("sa_agentsid", userInfo.getAgentId());
        sqlFactory.addParameter_in("sa_saleareaid", userInfo.getSaleAreaIds());
        sqlFactory.addParameter("departmentid", departmentid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        return getSucReturnObject().setData(rows).toString();
    }

}