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

package restcontroller.saletool.orderclue.web;

import beans.remind.Remind;
import beans.user.User;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import restcontroller.R;

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);
    }

    /**
     * 获取老板的线索id
     *
     * @return
     * @throws YosException
     */
    public ArrayList<Long> getLeaderIds() throws YosException {
        return dbConnect.runSqlQuery("SELECT sat_orderclueid FROM sat_orderclue_auth WHERE sys_enterpriseid = " + sys_enterpriseid).toArrayList("sat_orderclueid", new ArrayList<>());
    }

    /**
     * 获取业务员的线索id
     *
     * @return
     * @throws YosException
     */
    public ArrayList<Long> getSalesIds(Long sys_enterprise_hrid) throws YosException {
        return dbConnect.runSqlQuery("SELECT sat_orderclueid FROM sat_orderclue_auth WHERE sys_enterpriseid = " + sys_enterpriseid + " and hrid = " + sys_enterprise_hrid).toArrayList("sat_orderclueid", new ArrayList<>());
    }

    /**
     * 查询出当前经销商下线索状态是待跟进的数据
     *
     * @return
     * @throws YosException
     */
    public ArrayList<Long> getFollowIds() throws YosException {
        return dbConnect.runSqlQuery("SELECT sat_orderclueid FROM sat_orderclue WHERE sat_orderclueid in (SELECT sat_orderclueid FROM sat_orderclue_auth WHERE  sys_enterpriseid  = " + sys_enterpriseid + ") AND `status` = '待跟进'").toArrayList("sat_orderclueid", new ArrayList<>());
    }

    @API(title = "线索池-web-列表", apiversion = R.ID20221101094502.v1.class)
    public String selectList() throws YosException, IOException {
        boolean isAll = content.getBoolean("isAll");
        ArrayList<Long> sat_orderclueids = new ArrayList<>();
        if (usertype == 1) {
            sat_orderclueids = SQLFactory.createQuerySQL(this, "sat_orderclue_auth", "sat_orderclueid").setWhere("sys_enterpriseid", sys_enterpriseid).query().toArrayList("sat_orderclueid", new ArrayList<>());
        } else {
            if (isleader) {
                sat_orderclueids.addAll(getLeaderIds());
            } else {
                sat_orderclueids.addAll(getSalesIds(getEnterpriseHr(userid).getLong("sys_enterprise_hrid")));
            }
            sat_orderclueids.addAll(getFollowIds());
        }

        //用来判断是否是成员跟进
        Long memberHrid = 0L;
        if (usertype != 1) {
            if (isleader) {
                //经销商老板
                Rows leaderRows = dbConnect.runSqlQuery("SELECT sys_enterprise_hrid FROM sys_enterprise_hr WHERE isleader=1 AND sys_enterpriseid = " + sys_enterpriseid);
                if (leaderRows.isNotEmpty()) {
                    memberHrid = leaderRows.get(0).getLong("sys_enterprise_hrid");
                }
            } else {
                //经销商个人
                Rows leaderRows = dbConnect.runSqlQuery("SELECT sys_enterprise_hrid FROM sys_enterprise_hr WHERE userid = " + userid);
                if (leaderRows.isNotEmpty()) {
                    memberHrid = leaderRows.get(0).getLong("sys_enterprise_hrid");
                }
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_orderclue", "sat_orderclueid", "name", "phonenumber", "province", "city", "county", "address", "notes", "cluesource", "status").setTableAlias("t1");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sat_orderclue_auth", "t2", "sat_orderclueid=:sat_orderclueid ");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sys_enterprise", "t3", "t2.sys_enterpriseid=t3.sys_enterpriseid", "enterprisename");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sys_enterprise_hr", "t4", "t2.hrid=t4.sys_enterprise_hrid", "sys_enterprise_hrid");
        querySQL.addQueryFields("leadername", "t4.name");
        querySQL.addQueryFields("isTeam", "if(t1.isprivate = 0,'总部','团队')");
        querySQL.addQueryFields("isMemberFollow", "if(t2.hrid != " + memberHrid + ",1,0)");
        querySQL.setWhere("t1.sat_orderclueid", sat_orderclueids);
        if (!isAll) {
            querySQL.setPage(pageSize, pageNumber);
        }
        Rows rows = querySQL.query();

        QuerySQL ordercluefollowuplogQuery = SQLFactory.createQuerySQL(this, "sat_ordercluefollowuplog", "sat_orderclue_authid");
        ordercluefollowuplogQuery.addQueryFields("followtime", "MAX(createdate)");
        ordercluefollowuplogQuery.setWhere("sat_orderclue_authid", rows.toArrayList("sat_orderclue_authid"));
        RowsMap ordercluefollowuplogRowsMap = ordercluefollowuplogQuery.query().toRowsMap("sat_orderclue_authid");

        SQLFactory sqlFactory = new SQLFactory(this, "查询最新的跟进方式");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sat_orderclueid", sat_orderclueids);
        RowsMap logtypeRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("sat_orderclueid");

        RowsMap lognumRowsMap = dbConnect.runSqlQuery("SELECT COUNT(0) count, sat_orderclueid from sat_ordercluefollowuplog WHERE siteid='" + siteid + "' GROUP BY sat_orderclueid").toRowsMap("sat_orderclueid");
        for (Row row : rows) {
            if (ordercluefollowuplogRowsMap.containsKey(row.getString("sat_orderclue_authid"))) {
                row.put("followtime", ordercluefollowuplogRowsMap.get(row.getString("sat_orderclue_authid")).get(0).getString("followtime"));
            } else {
                row.put("followtime", "");
            }
            if (lognumRowsMap.containsKey(row.getString("sat_orderclueid"))) {
                row.put("lognum", lognumRowsMap.get(row.getString("sat_orderclueid")).get(0).getLong("count"));
            } else {
                row.put("lognum", 0);
            }
            if (logtypeRowsMap.containsKey(row.getString("sat_orderclueid"))) {
                Rows logtypeRows = logtypeRowsMap.get(row.getString("sat_orderclueid"));
                row.put("logtype", logtypeRows.get(0).getString("logtype"));
                row.put("followcontent", logtypeRows.get(0).getString("content"));
                row.put("followBy", logtypeRows.get(0).getString("createby"));
                row.put("followDate", logtypeRows.get(0).getString("createdate"));
            } else {
                row.put("logtype", "");
                row.put("followcontent", "");
                row.put("followBy", "");
                row.put("followDate", "");
            }
        }
        if (isAll) {
            String[] columns = {"sat_orderclueid", "sys_enterprise_hrid", "isMemberFollow", "logtype", "lognum", "logtype", "followBy", "followDate", "isTeam"};
            for (String column : columns) {
                rows.getFieldList().remove(column);
            }
            Rows uploadRows = saveToExcelAttachment("1111", "线索池", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "线索池-web-根据状态查询线索数量", apiversion = R.ID2026020914460801.v1.class)
    public String getCount() throws YosException {
        ArrayList<Long> ids = new ArrayList<>();
        //查询经销商老板还是经销商团队成员
        if (isleader) {
            ids.addAll(getLeaderIds());
        } else {
            ids.addAll(getSalesIds(getEnterpriseHr(userid).getLong("sys_enterprise_hrid")));
        }
        ids.addAll(getFollowIds());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_orderclue");
        querySQL.addQueryFields("num", "count(0)");
        querySQL.setWhere("status", new String[]{"待跟进", "跟进中"});
        querySQL.setWhere("sat_orderclueid", ids);
        Rows rows = querySQL.query();
        JSONObject resObj = new JSONObject();
        resObj.put("count", rows.get(0).getString("num"));
        return getSucReturnObject().setData(resObj).toString();
    }

    @API(title = "线索池-编辑", apiversion = R.ID20221205162402.v1.class)
    public String edit() throws YosException {
        Long sat_orderclueid = content.getLong("sat_orderclueid");
        String name = content.getString("name");
        String phonenumber = content.getString("phonenumber");
        String province = content.getString("province");
        String city = content.getString("city");
        String county = content.getString("county");
        String address = content.getString("address");
        String cluesource = content.getString("cluesource");
        String notes = content.getString("notes");

        SQLDump sqlDump = new SQLDump();
        if (sat_orderclueid <= 0) {
            sat_orderclueid = createTableID("sat_orderclue");
            InsertSQL orderclueInsert = SQLFactory.createInsertSQL(this, "sat_orderclue");
            orderclueInsert.setValue("sat_orderclueid", sat_orderclueid);
            orderclueInsert.setValue("name", name);
            orderclueInsert.setValue("phonenumber", phonenumber);
            orderclueInsert.setValue("province", province);
            orderclueInsert.setValue("city", city);
            orderclueInsert.setValue("county", county);
            orderclueInsert.setValue("address", address);
            orderclueInsert.setValue("notes", notes);
            orderclueInsert.setValue("cluesource", cluesource);
            orderclueInsert.setValue("isprivate", 1);
            orderclueInsert.setValue("status", "待跟进");
            sqlDump.add(orderclueInsert);
            sqlDump.add(getAddAuthSql(sat_orderclueid, getEnterpriseHr(userid).getLong("sys_enterprise_hrid")));
        } else {
            UpdateSQL orderclueUpdate = SQLFactory.createUpdateSQL(this, "sat_orderclue");
            orderclueUpdate.setValue("name", name);
            orderclueUpdate.setValue("phonenumber", phonenumber);
            orderclueUpdate.setValue("province", province);
            orderclueUpdate.setValue("city", city);
            orderclueUpdate.setValue("county", county);
            orderclueUpdate.setValue("address", address);
            orderclueUpdate.setValue("notes", notes);
            orderclueUpdate.setValue("cluesource", cluesource);
            orderclueUpdate.setUniqueid(sat_orderclueid);
            sqlDump.add(orderclueUpdate);
        }
        sqlDump.commit(this);
        return getSucReturnObject().toString();
    }

    @API(title = "线索池-web-详情", apiversion = R.ID2026020914464101.v1.class)
    public String selectDetail() throws YosException {
        Long sat_orderclueid = content.getLong("sat_orderclueid");
        Long memberHrid = 0L;

        QuerySQL hrQuery = SQLFactory.createQuerySQL(this, "sys_enterprise_hr", "sys_enterprise_hrid");
        if (isleader) {
            hrQuery.setWhere("isleader", 1).setWhere("sys_enterpriseid", sys_enterpriseid);
        } else {
            hrQuery.setWhere("userid", userid);
        }
        Rows query = hrQuery.query();
        if (query.isNotEmpty()) {
            memberHrid = query.get(0).getLong("sys_enterprise_hrid");
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_orderclue", "sat_orderclueid", "name", "phonenumber", "province", "city", "county", "address", "notes", "cluesource", "status").setTableAlias("t1");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sat_orderclue_auth", "t2", "sat_orderclueid=:sat_orderclueid ");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sys_enterprise", "t3", "t3.sys_enterpriseid=t2.sys_enterpriseid", "enterprisename");
        querySQL.addJoinTable(QuerySQL.JOINTYPE.left, "sys_enterprise_hr", "t4", "t4.sys_enterprise_hrid=t2.hrid");
        querySQL.addQueryFields("createBy", "t1.createby");
        querySQL.addQueryFields("createDate", "t1.createdate");
        querySQL.addQueryFields("editBy", "t1.changeby");
        querySQL.addQueryFields("editDate", "t1.changedate");
        querySQL.addQueryFields("leadername", "t4.name");
        querySQL.addQueryFields("leaderuserid", "t4.userid");
        querySQL.addQueryFields("isMemberFollow", "if(t2.hrid != " + memberHrid + ",1,0)");
        querySQL.setWhere("t1.sat_orderclueid", sat_orderclueid);
        Rows detailRows = querySQL.query();

        Row detailRow = detailRows.isNotEmpty() ? detailRows.get(0) : new Row();

        //分配人，分配时间
        String assignedBy = "";
        String assignedDate = "";
        Rows assignedRows = dbConnect.runSqlQuery("SELECT sat_orderclue_authid,createby,createdate FROM sat_orderclue_auth WHERE sat_orderclueid = " + sat_orderclueid + " ORDER BY sat_orderclue_authid  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 = " + sat_orderclueid);
        if (countRow.getLong("count") > 1) {
            Row changeRow = dbConnect.runSqlQuery(0, " SELECT changeby,changedate FROM sat_orderclue_auth WHERE sat_orderclueid = " + sat_orderclueid);
            changeBy = changeRow.getString("changeby");
            changeDate = changeRow.getString("changedate");
        }
        //最近跟进人，最近跟进时间
        String followBy = "";
        String followDate = "";
        //获取跟进id
        Long sat_orderclue_authid = 0L;
        Rows authRows = dbConnect.runSqlQuery(" SELECT sat_orderclue_authid FROM sat_orderclue_auth WHERE sat_orderclueid = " + sat_orderclueid);
        if (authRows.isNotEmpty()) {
            sat_orderclue_authid = authRows.get(0).getLong("sat_orderclue_authid");
        }
        Rows followRows = dbConnect.runSqlQuery(" SELECT createby,createdate FROM sat_ordercluefollowuplog WHERE sat_orderclue_authid = " + sat_orderclue_authid + " ORDER BY createdate DESC LIMIT 1");
        if (followRows.isNotEmpty()) {
            followBy = followRows.get(0).getString("createby");
            followDate = followRows.get(0).getString("createdate");
        }
        detailRow.put("assignedBy", assignedBy);
        detailRow.put("assignedDate", assignedDate);
        detailRow.put("changeBy", changeBy);
        detailRow.put("changeDate", changeDate);
        detailRow.put("followBy", followBy);
        detailRow.put("followDate", followDate);
        return getSucReturnObject().setData(detailRow).toString();
    }


    @API(title = "线索池-web-跟进列表", apiversion = R.ID20221101094602.v1.class)
    public String getFollowList() throws YosException {
        Long sat_orderclueid = content.getLong("sat_orderclueid");
        QuerySQL ordercluefollowuplogQuery = SQLFactory.createQuerySQL(this, "sat_ordercluefollowuplog", "createby", "createdate", "content", "followupmode", "logtype", "competitor").setTableAlias("t1");
        ordercluefollowuplogQuery.addJoinTable(JOINTYPE.inner, "sat_orderclue_auth", "t2", "sat_orderclue_authid=:sat_orderclue_authid");

        ordercluefollowuplogQuery.setWhere("t1.sat_orderclueid", sat_orderclueid);
        ordercluefollowuplogQuery.setOrderBy("t1.createdate");
        Rows rows = ordercluefollowuplogQuery.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "线索池-web-线索跟进新增", apiversion = R.ID20221208100602.v1.class)
    public String addFollowUpLog() throws YosException {
        Long sat_orderclueid = content.getLong("sat_orderclueid");
        //先转移操作
        content.put("sys_enterprise_hrid", getEnterpriseHr(userid).getLong("sys_enterprise_hrid"));
        content.put("sat_orderclueid", new Long[]{sat_orderclueid});

        String followupmode = content.getStringValue("followupmode");//跟进方式
        String competitor = content.getStringValue("competitor");//竟品
        String contentTxt = content.getStringValue("content");//跟进内容
        String logtype = content.getString("logtype");//记录类型

        long sat_orderclue_authid = 0L;
        Rows authRows = dbConnect.runSqlQuery(" SELECT sat_orderclue_authid FROM sat_orderclue_auth WHERE sat_orderclueid = " + sat_orderclueid);
        if (authRows.isNotEmpty()) {
            sat_orderclue_authid = authRows.get(0).getLong("sat_orderclue_authid");
        }
        InsertSQL ordercluefollowuplogInsert = SQLFactory.createInsertSQL(this, "sat_ordercluefollowuplog");
        ordercluefollowuplogInsert.setValue("sat_orderclueid", sat_orderclueid);
        ordercluefollowuplogInsert.setValue("content", contentTxt);
        ordercluefollowuplogInsert.setValue("followupmode", followupmode);
        ordercluefollowuplogInsert.setValue("logtype", logtype);
        ordercluefollowuplogInsert.setValue("competitor", competitor);
        ordercluefollowuplogInsert.setValue("sat_orderclue_authid", sat_orderclue_authid);
        ordercluefollowuplogInsert.insert();
        if (logtype.contains("跟进") || logtype.contains("预约到店") || logtype.contains("互加微信")) {
            updateStatus(sat_orderclueid, "跟进中");
        } else if (logtype.contains("成交")) {
            updateStatus(sat_orderclueid, "已成交");
        } else if (logtype.contains("无效")) {
            updateStatus(sat_orderclueid, "已无效");
        }
        return getSucReturnObject().toString();
    }

    /**
     * 发送消息：经销商团队人员
     *
     * @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);
        for (Row row : rows) {
            for (Object id : sat_orderclueid_array) {
                Remind remind = new Remind(this);
                remind.setType("应用");
                remind.setTitle("线索通知");
                remind.setContent("您有管理员转移的线索，请继续跟进！");
                remind.setTouserid(row.getLong("userid"));
                remind.setObjectname("sat_orderclue");
                remind.setObjectid(Long.parseLong(id.toString()));
                remind.sendByDialogMsg().createSys_message();
                remind.setToemail(User.getUserMailAddress(this, row.getLong("userid")));
                remind.sendByMail();
            }
        }
    }

    //给新建的线索添加一条授权记录，默认授权给到经销商老板
    public InsertSQL getAddAuthSql(long sat_orderclueid, Long sys_enterprise_hrid) throws YosException {
        InsertSQL orderclue_authInsert = SQLFactory.createInsertSQL(this, "sat_orderclue_auth");
        orderclue_authInsert.setValue("sys_enterpriseid", sys_enterpriseid);
        orderclue_authInsert.setValue("sat_orderclueid", sat_orderclueid);
        orderclue_authInsert.setValue("isused", 1);
        orderclue_authInsert.setValue("hrid", sys_enterprise_hrid);
        return orderclue_authInsert;
    }


    //更新线索状态
    public void updateStatus(Long sat_orderclueid, String status) throws YosException {
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sat_orderclue");
        updateSQL.setValue("status", status);
        updateSQL.setValue("sat_orderclueid", sat_orderclueid);
        updateSQL.update();
    }

    //返回导出的标题
    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("followcontent", "最近跟进内容");
        return titleMap;
    }

}