CUCU2/src/custom/restcontroller/saletool/orderclue/web/orderclue.java

package restcontroller.saletool.orderclue.web;

import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.remind.Remind;
import beans.time.Time;
import beans.user.User;
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.annotation.cm;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import restcontroller.R;
import restcontroller.sale.enterprise.EnterpriseContacts;
import restcontroller.sale.project.Project;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.customer.Customer;
import restcontroller.webmanage.sale.enterprisefinance.EnterpriseFinance;
import restcontroller.webmanage.saletool.orderclue.privateclue.PrivateClue;
import restcontroller.webmanage.saletool.orderclue.publicclue.PublicClue;

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

@API(title = "线索池-web")
public class orderclue extends Controller {

    public orderclue(JSONObject content) throws YosException {
        super(content);
    }


    /**
     * 获取经销商的线索
     *
     * @param hrid
     * @return
     * @throws YosException
     */
    public ArrayList<Long> getAgentClue(Long hrid) throws YosException {
        if ( userInfo.getAgentID() > 0) {
            String idsSql = "SELECT sat_orderclueid FROM sat_orderclue_auth WHERE isused AND sa_agentsid = " +  userInfo.getAgentID() + " and siteid = '" + siteid + "'";
            if (!isleader) {
                idsSql = idsSql + " and hrid = " + hrid;
            }
            Rows rows = dbConnect.runSqlQuery(idsSql);
            return rows.toArrayList("sat_orderclueid", new ArrayList<>());
        } else {
            return new ArrayList<>();
        }
    }

    /**
     * 获取业务员的线索及管辖范围内的线索
     *
     * @return
     */
    public ArrayList<Long> getSalesmanClue() throws YosException {
        if (hrid > 0) {
            String idsSql = "SELECT sat_orderclueid FROM sat_orderclue_auth WHERE sa_agentsid in (SELECT sa_agentsid from sys_enterprise_tradefield WHERE sa_saleareaid in (SELECT sa_saleareaid from sa_salearea_hr WHERE hrid = " + hrid + ")) or hrid = " + hrid + " and siteid = '" + siteid + "'";
            Rows rows = dbConnect.runSqlQuery(idsSql);
            return rows.toArrayList("sat_orderclueid", new ArrayList<>());
        } else {
            return new ArrayList<>();
        }

    }

    @API(title = "线索池-web-列表", apiversion = R.ID20221101094502.v1.class)
    @CACHEING
    public String selectList() throws YosException, IOException {
        boolean isAll = content.getBoolean("isAll");
        int type = content.getIntValue("type");
        Long deleted = 0L;
        if (content.containsKey("deleted") && !"".equals(content.getString("deleted"))) {
            deleted = content.getLong("deleted");
        }
        String tablename = "sat_orderclue";
        /*
          过滤条件设置
         */
        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.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 t1.notes like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.notes like'%").append(whereObject.getString("condition")).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("allocationstatus") && !"".equals(whereObject.getString("allocationstatus"))) {
                where.append(" and(");
                where.append("t1.allocationstatus = '").append(whereObject.getString("allocationstatus")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("createdate") && !"".equals(whereObject.getString("createdate"))) {
                where.append(" and(");
                where.append("t1.createdate >= '").append(whereObject.getString("createdate")).append("' ");
                where.append(")");
            }
        }
        where.append(" and(").append("t1.deleted = ").append(deleted).append(")");
        SQLFactory sqlFactory = new SQLFactory(this, "线索列表", pageSize, pageNumber, pageSorting);
        if (isAll) {
            sqlFactory = new SQLFactory(this, "线索列表");
        }
        String where2 = "";
        if (type == 0) {
            where2 = DataTeam.getDataWhereStr(this, tablename, "t1", 1)
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 2)
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 3)
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 4)
                    + " or " + DataTeam.getDataWhereStr(this, tablename, "t1", 5);
        } else {
            where2 = DataTeam.getDataWhereStr(this, tablename, "t1", type);
        }
        sqlFactory.addParameter_SQL("where2", where2);

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_agentsid",  userInfo.getAgentID());
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        RowsMap leaderRows = DataTeam.getLeader(this, "sat_orderclue", rows.toArrayList("sat_orderclueid")).toRowsMap("ownerid");
        RowsMap deletereasonRowsMap = null;
        if (deleted == 1) {
            deletereasonRowsMap = DataExtend.getDeleteReasonSql(this, "sat_orderclue", rows.toArrayList("sat_orderclueid", new ArrayList<Long>()));
        }
        for (Row row : rows) {
            row.put("leader", leaderRows.get(row.getString("sat_orderclueid")));
            if (deletereasonRowsMap != null && deletereasonRowsMap.containsKey(row.getString("sat_orderclueid"))) {
                Row dataextendrow = deletereasonRowsMap.get(row.getString("sat_orderclueid")).get(0);
                row.put("deletereason", dataextendrow.getString("deletereason"));
                row.put("deletechangeby", dataextendrow.getString("changeby"));
                row.put("deletechangedate", dataextendrow.getString("changedate"));
            } else {
                row.put("deletereason", "");
                row.put("deletechangeby", "");
                row.put("deletechangedate", "");
            }
        }

//        if (isAll) {
//            Rows uploadRows = uploadExcelToObs("1111", "线索池", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }

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


    @API(title = "根据状态查询我负责的线索数量")
    public String getCount() throws YosException, IOException {
        String status = content.getString("status");

        SQLFactory sqlFactory = new SQLFactory(this, "根据状态查询线索数量");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("status", status);
        sqlFactory.addParameter_SQL("where", DataTeam.getDataWhereStr(this, "sat_orderclue", "t1", 1));
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        JSONObject resObj = new JSONObject();
        resObj.put("count", rows.get(0).getString("num"));

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

    @API(title = "新增或更新线索", apiversion = R.ID20221205162402.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221101094502.class, R.ID20221101094302.class, R.ID20221101094402.class, R.ID20221208163302.class})
    public String edit() throws YosException {
        String tablename = "sat_orderclue";
        Long id = content.getLong("sat_orderclueid");
        String enterprisename = content.getStringValue("enterprisename");
        String name = content.getStringValue("name");
        String phonenumber = content.getString("phonenumber", tablename);
        String province = content.getString("province");
        String city = content.getString("city");
        String county = content.getString("county");
        String address = content.getString("address", tablename);
        String cluesource = content.getStringValue("cluesource");
        String notes = content.getString("notes", tablename);
        Long isprivate = content.getLong("isprivate");
        Long sat_campaignid = content.getLongValue("sat_campaignid");
        String allocationstatus;
        if (isprivate == 0) {
            allocationstatus = "待分配";
        } else {
            allocationstatus = "已分配";
        }


        ArrayList<String> sqlList = new ArrayList<>();
        //是否新增
        Boolean isAdd = false;

        SQLFactory sqlFactory = new SQLFactory(this, "线索编辑");
        if (id <= 0) {
            isAdd = true;
            id = createTableID(tablename);
            sqlFactory = new SQLFactory(this, "线索新增");
            //添加操作记录
            if (isprivate == 1) {
                sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "新建", "新建线索" + enterprisename).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "新建", "新建公海线索" + enterprisename).getSQL());
                Executor.sendEml(this, "orderclue_add", id, siteid);
            }


        } else {
            //添加操作记录
            if (isprivate == 1) {
                sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "编辑", "编辑线索" + enterprisename).getSQL());
            } else {
                sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "编辑", "编辑公海线索" + enterprisename).getSQL());
            }

        }
        sqlFactory.addParameter("sat_orderclueid", id);
        sqlFactory.addParameter("enterprisename", enterprisename);
        sqlFactory.addParameter("name", name);
        sqlFactory.addParameter("phonenumber", phonenumber);
        sqlFactory.addParameter("province", province);
        sqlFactory.addParameter("city", city);
        sqlFactory.addParameter("county", county);
        sqlFactory.addParameter("address", address);
        sqlFactory.addParameter("cluesource", cluesource);
        sqlFactory.addParameter("notes", notes);
        sqlFactory.addParameter("createby", username);
        sqlFactory.addParameter("changeuserid", userid);
        sqlFactory.addParameter("changeby", username);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("cluetype", "业务员");
        sqlFactory.addParameter("sat_campaignid", sat_campaignid);
        sqlFactory.addParameter("sa_customersid", 0);
        sqlFactory.addParameter("isprivate", isprivate);
        sqlFactory.addParameter("allocationstatus", allocationstatus);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("projectname", content.getStringValue("projectname"));
        sqlFactory.addParameter("scale", content.getStringValue("scale"));
        sqlFactory.addParameter("costofconstruction", content.getBigDecimalValue("costofconstruction"));
        sqlFactory.addParameter("totalinvestment", content.getBigDecimalValue("totalinvestment"));
        sqlFactory.addParameter("begdate_due", content.getStringValue("begdate_due", false, "null"));
        sqlFactory.addParameter("enddate_due", content.getStringValue("enddate_due", false, "null"));

        String sql = sqlFactory.getSQL();
        sqlList.add(sql);
        if (isAdd) {
            if (isprivate == 1) {
                if (usertype == 1) {
                    sqlList.add(getAddAuthSql(String.valueOf(id), hrid));
                } else {
                    sqlList.add(getAddAuthSql(String.valueOf(id), getSysEnterpriseHrid()));
                }

                sqlList.addAll(DataTeam.createTeamSQL(this, tablename, id, userid));
                sqlList.addAll(DataTeam.createTeamMemberSql(this, tablename, id, userid));
            }
        }

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }

    @API(title = "详情")
    public String selectDetail() throws YosException {
        Long id = content.getLong("sat_orderclueid");

        //线索详情
        SQLFactory detailFactory = new SQLFactory(this, "线索详情");
        detailFactory.addParameter("siteid", siteid);
        detailFactory.addParameter("sat_orderclueid", id);
        Rows detailRows = dbConnect.runSqlQuery(detailFactory);
        RowsMap leaderRows = DataTeam.getLeader(this, "sat_orderclue", detailRows.toArrayList("sat_orderclueid")).toRowsMap("ownerid");
        for (Row row : detailRows) {
            row.put("leader", leaderRows.get(row.getString("sat_orderclueid")));
        }

        Row detailRow = detailRows.isNotEmpty() ? detailRows.get(0) : new Row();
        //创建人，创建时间
        String createBy = "";
        String createDate = "";
        //最近编辑人，最近编辑时间
        String editBy = "";
        String editDate = "";

        Rows createRows = dbConnect.runSqlQuery("SELECT createby,createdate,changeby,changedate FROM sat_orderclue WHERE sat_orderclueid = " + id);
        if (createRows.isNotEmpty()) {
            createBy = createRows.get(0).getString("createby");
            createDate = createRows.get(0).getString("createdate");
            editBy = createRows.get(0).getString("changeby");
            editDate = createRows.get(0).getString("changedate");
        }
        //分配人，分配时间
        String assignedBy = "";
        String assignedDate = "";
        Rows assignedRows = dbConnect.runSqlQuery("SELECT sat_orderclue_authid,createby,createdate FROM sat_orderclue_auth WHERE sat_orderclueid = " + id + " ORDER BY createdate  LIMIT 1");
        if (assignedRows.isNotEmpty()) {
            assignedBy = assignedRows.get(0).getString("createby");
            assignedDate = assignedRows.get(0).getString("createdate");
        }
        //转移人，转移时间
        String changeBy = "";
        String changeDate = "";
        Row countRow = dbConnect.runSqlQuery(0, " SELECT COUNT(0) count FROM sat_orderclue_auth WHERE sat_orderclueid = " + id);
        if (countRow.getLong("count") > 1) {
            Row changeRow = dbConnect.runSqlQuery(0, " SELECT changeby,changedate FROM sat_orderclue_auth WHERE isused AND sat_orderclueid = " + id);
            changeBy = changeRow.getString("changeby");
            changeDate = changeRow.getString("changedate");
        }
        //最近跟进人，最近跟进时间
        String followBy = "";
        String followDate = "";
        //获取跟进id
        String sql = "SELECT createby,createdate FROM sat_ordercluefollowuplog WHERE sat_orderclue_authid in (SELECT sat_orderclue_authid FROM sat_orderclue_auth WHERE sat_orderclueid= " + id + ") and siteid = '" + siteid + "' ORDER BY createdate desc";
        Rows followRows = dbConnect.runSqlQuery(sql);
        if (followRows.isNotEmpty()) {
            followBy = followRows.get(0).getString("createby");
            followDate = followRows.get(0).getString("createdate");
        }

        detailRow.put("createBy", createBy);
        detailRow.put("createDate", createDate);
        detailRow.put("editBy", editBy);
        detailRow.put("editDate", editDate);
        detailRow.put("assignedBy", assignedBy);
        detailRow.put("assignedDate", assignedDate);
        detailRow.put("changeBy", changeBy);
        detailRow.put("changeDate", changeDate);
        detailRow.put("followBy", followBy);
        detailRow.put("followDate", followDate);

        sql = "SELECT 1 from sa_customers WHERE sourcetable = 'sat_orderclue' and sourceid = '" + id + "' and siteid='" + siteid + "'";
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            detailRow.put("iscustomer", 1);
        } else {
            detailRow.put("iscustomer", 0);
        }
        sql = "SELECT 1 from sa_project WHERE sourcetable = 'sat_orderclue' and sourceid = '" + id + "' and siteid='" + siteid + "'";
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            detailRow.put("isproject", 1);
        } else {
            detailRow.put("isproject", 0);
        }

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


    @API(title = "跟进列表", apiversion = R.ID20221101094602.v1.class)
    public String getFollowList() throws YosException {
        Long id = content.getLong("sat_orderclueid");

        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("start") && !"".equals(whereObject.getString("start"))) {
                where.append(" and(");
                where.append("t1.createdate >='").append(whereObject.getString("start")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("end") && !"".equals(whereObject.getString("end"))) {
                where.append(" and(");
                where.append("t1.createdate <='").append(whereObject.getString("end")).append("' ");
                where.append(")");
            }
        }


        Rows authRows = dbConnect.runSqlQuery(" SELECT sat_orderclue_authid FROM sat_orderclue_auth WHERE    sat_orderclueid = " + id);

        ArrayList<Long> ids = authRows.toArrayList("sat_orderclue_authid", new ArrayList<>());
        if (ids.size() == 0) {
            ids.add(-1L);
        }
        SQLFactory sqlFactory = new SQLFactory(this, "线索跟进列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_in("sat_orderclue_authid", ids);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        ArrayList<Long> sat_ordercluefollowuplogids = rows.toArrayList("sat_ordercluefollowuplogid", new ArrayList<>());

        RowsMap rowsMap = getAttachmentUrl("sat_ordercluefollowuplog", sat_ordercluefollowuplogids);
        for (Row row : rows) {
            row.put("attinfo", rowsMap.get(row.getString("sat_ordercluefollowuplogid")));
        }


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

    @API(title = "线索跟进新增", apiversion = R.ID20221208100602.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = orderclue.class, method = {"getFollowList", "selectList", "getCount"}),
            @cm(clazz = PrivateClue.class, method = {"selectList"}),
            @cm(clazz = PublicClue.class, method = {"selectList"})})
    public String addFollowUpLog() throws YosException {

        Long id = content.getLong("sat_orderclueid");
        String contentTxt = content.getString("content", "sat_ordercluefollowuplog");
        String followupmode = content.getString("followupmode");
        String logtype = content.getString("logtype");
        String competitor = content.getString("competitor");

        Long sat_orderclue_authid = 0L;
        Rows authRows = dbConnect.runSqlQuery(" SELECT sat_orderclue_authid FROM sat_orderclue_auth WHERE isused AND  sat_orderclueid = " + id);
        if (authRows.isNotEmpty()) {
            sat_orderclue_authid = authRows.get(0).getLong("sat_orderclue_authid");
        }
        Long sat_ordercluefollowuplogid = content.getLong("sat_ordercluefollowuplogid");
        SQLFactory sqlFactory = new SQLFactory(this, "线索跟进新增");
        if (sat_ordercluefollowuplogid <= 0) {
            sat_ordercluefollowuplogid = createTableID("sat_ordercluefollowuplog");
        } else {
            sqlFactory = new SQLFactory(this, "线索跟进更新");
        }

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sat_ordercluefollowuplogid", sat_ordercluefollowuplogid);
        sqlFactory.addParameter("sat_orderclueid", id);
        sqlFactory.addParameter("content", contentTxt);
        sqlFactory.addParameter("followupmode", followupmode);
        sqlFactory.addParameter("logtype", logtype);
        sqlFactory.addParameter("competitor", competitor);
        sqlFactory.addParameter("sat_orderclue_authid", sat_orderclue_authid);
        sqlFactory.addParameter("latitude", content.getStringValue("latitude"));
        sqlFactory.addParameter("longitude", content.getStringValue("longitude"));
        sqlFactory.addParameter("address", content.getStringValue("address"));
        if (content.getStringValue("createdate").equals("")) {
            sqlFactory.addParameter("createdate", Time.getDateTime_Str());
        } else {
            sqlFactory.addParameter("createdate", content.getStringValue("createdate"));
        }
        String sql = sqlFactory.getSQL();
        dbConnect.runSqlUpdate(sql);
        String enterprisename = new PublicClue(content).getOrderClueEnterprisename(id);
        if (logtype.contains("跟进")) {
            updateStatus(id, "跟进中");
            //线索跟进
            dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sat_orderclue", id, "跟进", "线索跟进" + enterprisename).getSQL());

        } else if (logtype.contains("无效")) {
            updateStatus(id, "已无效");
            //线索无效
            dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sat_orderclue", id, "无效", "线索无效" + enterprisename).getSQL());
        }

        Rows rows = dbConnect.runSqlQuery("SELECT * from sat_ordercluefollowuplog WHERE sat_ordercluefollowuplogid = " + sat_ordercluefollowuplogid);

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

    }

    @API(title = "删除线索跟进", apiversion = R.ID20221208112002.v1.class)
    public String deleteFollowuplog() throws YosException {
        Long sat_ordercluefollowuplogid = content.getLong("sat_ordercluefollowuplogid");
        dbConnect.runSqlUpdate("DELETE  from sat_ordercluefollowuplog WHERE sat_ordercluefollowuplogid = '" + sat_ordercluefollowuplogid + "'");
        return getSucReturnObject().toString();
    }

    @API(title = "作废", apiversion = R.ID20221123193702.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221101094302.class, R.ID20221101094402.class, R.ID20221101094502.class})
    public String delete() throws YosException {
        JSONArray sat_orderclueids = content.getJSONArray("sat_orderclueids");

        String sql = "SELECT 1 from sat_orderclue WHERE sat_orderclueid in " + sat_orderclueids + " and `status` !='待跟进'";
        sql = sql.replace("[", "(").replace("]", ")");
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("只能作废待跟进的线索").toString();
        }

        sql = "UPDATE sat_orderclue SET deleted=1 WHERE sat_orderclueid in " + sat_orderclueids + " and status = '待跟进' ";
        sql = sql.replace("[", "(").replace("]", ")");
        dbConnect.runSqlUpdate(sql);
        //新增删除或作废原因
        DataExtend.createDeleteReasonSql(this, "sat_orderclue", sat_orderclueids.toJavaList(Long.class), content.getStringValue("deletereason"));
        //操作记录
        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : sat_orderclueids) {
            Long id = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "作废", "作废线索至回收站").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();

    }

    @API(title = "线索转移")
    @CACHEING_CLEAN(cms = {@cm(clazz = orderclue.class, method = {"selectList"}),
            @cm(clazz = PrivateClue.class, method = {"selectList"}),
            @cm(clazz = PublicClue.class, method = {"selectList"})
    })
    public String changeClue() throws YosException {
        Long sys_enterprise_hrid = content.getLong("sys_enterprise_hrid");
        JSONArray sat_orderclueid_array = content.getJSONArray("sat_orderclueid");

        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : sat_orderclueid_array) {
            Long id = Long.valueOf(obj.toString());
            Rows rows = dbConnect.runSqlQuery("SELECT * FROM sat_orderclue_auth WHERE sat_orderclueid = " + id + " AND hrid =" + sys_enterprise_hrid + " AND sa_agentsid = " +  userInfo.getAgentID());
            sqlList.add("UPDATE sat_orderclue_auth SET  isused = 0 where  sat_orderclueid = " + id + " AND sa_agentsid = " +  userInfo.getAgentID());
            if (rows.isEmpty()) {
                //新增
                sqlList.add(getAddAuthSql(id.toString(), sys_enterprise_hrid));
            } else {
                //更新
                sqlList.add("UPDATE sat_orderclue_auth SET isused = 1,changeby= '"
                        + username + "',changedate=CURRENT_TIME,changeuserid = "
                        + userid + " WHERE sat_orderclueid = "
                        + id + " AND sa_agentsid = "
                        +  userInfo.getAgentID() + " AND hrid = "
                        + sys_enterprise_hrid);
            }

            sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", id, "转移", "线索转移" + id).getSQL());

        }

        dbConnect.runSqlUpdate(sqlList);

        //发送消息
        sendMsg(sat_orderclueid_array, sys_enterprise_hrid);

        return getSucReturnObject().toString();
    }

    @API(title = "转化线索（项目）", apiversion = R.ID20221124154902.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221101094502.class})
    public String transformingToProject() throws YosException {

        Long sat_orderclueid = content.getLong("sat_orderclueid");

        Rows rows = dbConnect.runSqlQuery("SELECT 1 from sat_orderclue WHERE sat_orderclueid = " + sat_orderclueid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("未找到转化数据").toString();
        }
        String sql = "SELECT 1 from sa_project WHERE sourcetable = 'sat_orderclue' and sourceid = '" + sat_orderclueid + "' and siteid='" + siteid + "'";
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("当前线索已转化为项目，无法再次转化").toString();
        }

        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("UPDATE sat_orderclue SET `status`='已转化' WHERE sat_orderclueid=" + sat_orderclueid);

        Long sa_projectid = createTableID("sa_project");

        SQLFactory sqlFactory = new SQLFactory(this, "新增转化项目");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("projectname", content.getStringValue("projectname"));
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        sqlFactory.addParameter("departmentid", departmentid);
        sqlFactory.addParameter("projectnum", createBillCode("projectbill"));
        sqlFactory.addParameter("sourceid", sat_orderclueid);
        sqlFactory.addParameter("projecttype", content.getStringValue("projecttype"));
        sqlFactory.addParameter("scale", content.getStringValue("scale"));
        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("grade", content.getStringValue("grade"));
        sqlFactory.addParameter("costofconstruction", content.getBigDecimalValue("costofconstruction"));
        sqlFactory.addParameter("totalinvestment", content.getBigDecimalValue("totalinvestment"));
        sqlFactory.addParameter("begdate_due", content.getStringValue("begdate_due", false, "null"));
        sqlFactory.addParameter("enddate_due", content.getStringValue("enddate_due", false, "null"));
        sqlFactory.addParameter("sa_brandid", content.getLongValue("sa_brandid"));
        sqlFactory.addParameter("tradefield", content.getStringValue("tradefield"));
        sqlList.add(sqlFactory.getSQL());

        String enterprisename = new PublicClue(content).getOrderClueEnterprisename(sat_orderclueid);
        sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", sat_orderclueid, "转化项目", "线索转化为项目商机" + enterprisename).getSQL());

        RowsMap stageRowsMap = dbConnect.runSqlQuery("select t1.sa_projstagemagid ,t1.stagename,t1.remarks,t1.sequence,t2.projecttype from sa_projstagemag t1 inner join  sa_projstagemag_projecttype t2 on t1.siteid=t2.siteid and t1.sa_projstagemagid=t2.sa_projstagemagid where t1.siteid='" + siteid + "'").toRowsMap("projecttype");

        Rows stageRows = stageRowsMap.get(content.getStringValue("projecttype"));
        for (int i = 0; i < stageRows.size(); i++) {
            Row row = stageRows.getRow(i);
            sqlFactory = new SQLFactory(this, "工程项目阶段信息新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_project_stageid", createTableID("sa_project_stage"));
            sqlFactory.addParameter("sa_projectid", sa_projectid);
            sqlFactory.addParameter("sa_projstagemagid", row.getLong("sa_projstagemagid"));
            sqlFactory.addParameter("stagename", row.getString("stagename"));
            sqlFactory.addParameter("remarks", row.getString("remarks"));
            sqlFactory.addParameter("sequence", row.getLong("sequence"));
            if (i == 0) {
                sqlFactory.addParameter("active", 1);
            } else {
                sqlFactory.addParameter("active", 0);
            }
            sqlList.add(sqlFactory.getSQL());
        }


        sqlList.addAll(DataTeam.createTeamSQL(this, "sa_project", sa_projectid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_project", sa_projectid, userid));
        DataTag.createTag(this, "sa_project", sa_projectid, "直销");
        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_projectid", sa_projectid);


        ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sat_orderclue", sat_orderclueid).toArrayList("userid", new ArrayList<>());
        Executor.sendEml(this, "orderclue_project", sat_orderclueid, siteid,userids);

        return new Project(content).selectDetail();

    }

    @API(title = "转化线索（客户）", apiversion = R.ID20221216184302.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221101094502.v1.class})
    public String transformingToCustomer() throws YosException {

        Long sat_orderclueid = content.getLong("sat_orderclueid");
        Rows rows = dbConnect.runSqlQuery("SELECT 1 from sat_orderclue WHERE sat_orderclueid = " + sat_orderclueid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("未找到转化数据").toString();
        }
        String sql = "SELECT 1 from sa_customers WHERE sourcetable = 'sat_orderclue' and sourceid = '" + sat_orderclueid + "' and siteid='" + siteid + "'";
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("当前线索已转化为客户，无法再次转化").toString();
        }

        ArrayList<String> sqlList = new ArrayList<>();
        //更新线索状态
        sqlList.add("UPDATE sat_orderclue SET `status`='已转化' WHERE sat_orderclueid=" + sat_orderclueid);

        Long sys_enterpriseid = createTableID("sys_enterprise");
        //创建企业档案
        SQLFactory 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", content.getString("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"));
        sqlList.add(sqlFactory.getSQL());

        //创建客户档案
        Long sa_customersid = createTableID("sa_customers");
        Long parentid = content.getLongValue("parentid");
        sqlFactory = new SQLFactory(this, "客户新增");
        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", content.getString("type"));
        //客户池（公海池）ID
        sqlFactory.addParameter("sa_customerpoolid", 0);
        //客户来源
        sqlFactory.addParameter("source", content.getStringValue("source"));
        sqlFactory.addParameter("sourceid", content.getStringValue("sat_orderclueid"));
        sqlFactory.addParameter("customergrade", content.getStringValue("customergrade"));
        sqlFactory.addParameter("ispublic", 1);
        sqlList.add(sqlFactory.getSQL());

        //操作记录
        String enterprisename = new PublicClue(content).getOrderClueEnterprisename(sat_orderclueid);
        sqlList.add(DataContrlLog.createLog(this, "sat_orderclue", sat_orderclueid, "转化客户", "线索转化为客户" + enterprisename).getSQL());


        //创建客户标签为潜在
        DataTag.createTag(this, "sa_customers", sa_customersid, "潜在");
        //创建当前用户为负责人
        sqlList.addAll(DataTeam.createTeamSQL(this, "sa_customers", sa_customersid, userid));
        sqlList.addAll(DataTeam.createTeamMemberSql(this, "sa_customers", sa_customersid, userid));
        dbConnect.runSqlUpdate(sqlList);
        //添加财务信息
        EnterpriseFinance finance = new EnterpriseFinance(content);
        content.put("sys_enterpriseid", sys_enterpriseid);
        content.put("isdefault", 1);
        finance.insertOrUpdate();
        //添加联系人
        EnterpriseContacts contacts = new EnterpriseContacts(content);
        content.put("sys_enterpriseid", sys_enterpriseid);
        content.put("workaddress", 1);
        content.put("isdefault", 1);
        contacts.insertOrUpdate();

        content.put("sa_customersid", sa_customersid);


        ArrayList<Long> userids = DataTeam.queryTeamRow(this, "sat_orderclue", sat_orderclueid).toArrayList("userid", new ArrayList<>());
        Executor.sendEml(this, "orderclue_customer", sat_orderclueid, siteid,userids);
        return new Customer(content).selectDetail();

    }

    /**
     * 发送消息：经销商团队人员
     *
     * @param sat_orderclueid_array
     * @param sys_enterprise_hrid
     * @throws YosException
     */
    public void sendMsg(JSONArray sat_orderclueid_array, Long sys_enterprise_hrid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT userid FROM sys_enterprise_hr WHERE sys_enterprise_hrid = " + sys_enterprise_hrid);
        Long tempUserid = 0L;
        if (rows.isNotEmpty()) {
            tempUserid = rows.get(0).getLong("userid");
        }
        for (Object id : sat_orderclueid_array) {
            if (tempUserid != 0) {
                Remind remind = new Remind(this);
                remind.setType("应用");
                remind.setTitle("线索通知");
                remind.setContent("您有管理员转移的线索，请继续跟进！");
                remind.setTouserid(tempUserid);
                remind.setObjectname("sat_orderclue");
                remind.setObjectid(Long.parseLong(id.toString()));
                remind.sendByDialogMsg().createSys_message();
                remind.setToemail(User.getUserMailAddress(this, tempUserid));
                remind.sendByMail();
            }
        }
    }

    //通过userid获取sys_enterprise_hrid
    public Long getSysEnterpriseHrid() throws YosException {
        Long sys_enterprise_hrid = 0L;
        Rows agentHrRows = dbConnect.runSqlQuery("SELECT sys_enterprise_hrid FROM sys_enterprise_hr WHERE userid = " + userid);
        if (agentHrRows.isNotEmpty()) {
            sys_enterprise_hrid = agentHrRows.get(0).getLong("sys_enterprise_hrid");
        }
        return sys_enterprise_hrid;
    }

    //给新建的线索添加一条授权记录，默认授权给到经销商老板
    public String getAddAuthSql(String sat_orderclueid, Long sys_enterprise_hrid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "新增授权");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sat_orderclue_authid", createTableID("sat_orderclue_auth"));
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_agentsid",  userInfo.getAgentID());
        sqlFactory.addParameter("sat_orderclueid", sat_orderclueid);
        sqlFactory.addParameter("hrid", sys_enterprise_hrid);
        String sql = sqlFactory.getSQL();
        return sql;

    }


    //更新线索状态
    public void updateStatus(Long sat_orderclueid, String status) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "线索状态更新");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("status", status);
        sqlFactory.addParameter("sat_orderclueid", sat_orderclueid);
        dbConnect.runSqlUpdate(sqlFactory.getSQL());
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("sat_orderclueid", "id");
        titleMap.put("name", "客户名称");
        titleMap.put("phonenumber", "手机号");
        titleMap.put("province", "省");
        titleMap.put("city", "市");
        titleMap.put("county", "县");
        titleMap.put("address", "地址");
        titleMap.put("notes", "备注");
        titleMap.put("cluesource", "来源");
        titleMap.put("status", "状态");
        titleMap.put("enterprisename", "经销商名称");
        titleMap.put("leadername", "负责人");
        titleMap.put("isTeam", "是否总部");
        titleMap.put("followtime", "最近跟进时间");
        titleMap.put("isMemberFollow", "是否团员跟进");
        return titleMap;
    }

}