CUCU2/src/custom/restcontroller/webmanage/saletool/data/DataDashboard.java

package restcontroller.webmanage.saletool.data;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import restcontroller.R;

import java.util.ArrayList;

/**
 * 数据大盘
 */
public class DataDashboard extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public DataDashboard(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "账户使用概况", apiversion = R.ID2024061109144502.v1.class)
    public String accountUseOverview() throws YosException {

        Row row = new Row();
        row.put("company_used", DataDashboardHelper.getUsedCount(this, 1));
        row.put("company_unbind", DataDashboardHelper.getUnBindCount(this, 1));
        row.put("company_stop", DataDashboardHelper.getStopCount(this, 1));
        row.put("company_all", DataDashboardHelper.getAllCount(this, 1));

        row.put("agent_used", DataDashboardHelper.getUsedCount(this, 21));
        row.put("agent_unbind", DataDashboardHelper.getUnBindCount(this, 21));
        row.put("agent_stop", DataDashboardHelper.getStopCount(this, 21));
        row.put("agent_all", DataDashboardHelper.getAllCount(this, 21));

        row.put("agentstaff_used", DataDashboardHelper.getUsedCount(this, 22));
        row.put("agentstaff_unbind", DataDashboardHelper.getUnBindCount(this, 22));
        row.put("agentstaff_stop", DataDashboardHelper.getStopCount(this, 22));
        row.put("agentstaff_all", DataDashboardHelper.getAllCount(this, 22));


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

    @API(title = "账号列表", apiversion = R.ID2024061110312202.v1.class)
    public String accountList() throws YosException {

        int usertype = content.getIntValue("usertype");
        int type = content.getIntValue("type");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_users",
                "accountno", "name", "phonenumber", "userid").setTableAlias("t");

        if (type == 1) {
            querySQL.setWhere("t.userid in (" + DataDashboardHelper.getUsedSql(this, usertype) + ")");
        }
        if (type == 2) {
            querySQL.setWhere("t.userid in (" + DataDashboardHelper.getUnBindSql(this, usertype) + ")");
        }
        if (type == 3) {
            querySQL.setWhere("t.userid in (" + DataDashboardHelper.getStopSql(this, usertype) + ")");
        }
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        for (Row row : rows) {

            row.put("usertype", usertype);
            row.put("rolename", userInfo.getUserRoleName(this, row.getLong("userid")));

        }


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

    @API(title = "添加分享记录", apiversion = R.ID2024061113525202.v1.class)
    public String insertShareLog() throws YosException {

        Long shareuserid = content.getLongValue("shareuserid");
        String channel = content.getStringValue("channel");
        String appname = content.getStringValue("appname");

        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sys_sharelog");
        insertSQL.setSiteid(siteid);
        insertSQL.setUniqueid(createTableID("sys_sharelog"));
        insertSQL.setValue("shareuserid", shareuserid);
        insertSQL.setValue("channel", channel);
        insertSQL.setValue("appname", appname);
        insertSQL.insert();

        return getSucReturnObject().toString();
    }

    @API(title = "账户使用情况", apiversion = R.ID2024061114184602.v1.class)
    public String accountInfo() throws YosException {

        int usertype = content.getIntValue("usertype");
        int type = 0;
        String begindate = "";
        String enddate = "";

        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                type = whereObject.getIntValue("type");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getStringValue("begindate");
                type=0;
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getStringValue("enddate");
                type=0;
            }
        }


        QuerySQL t2 = SQLFactory.createQuerySQL(this, "sys_userrequestlog", "userid", "usersiteid", "siteid");
        t2.addGroupBy("userid", "usersiteid", "siteid");
        if (type == 1) {
            t2.setWhere("DATE(lastrequestdate) = CURDATE()");
        }
        if (type == 2) {
            t2.setWhere("DATE(lastrequestdate) = CURDATE() - INTERVAL 1 DAY");
        }
        if (type == 3) {
            t2.setWhere("lastrequestdate >= CURRENT_DATE - INTERVAL 7 DAY");
        }
        if (type == 4) {
            t2.setWhere("lastrequestdate >= CURRENT_DATE - INTERVAL 30 DAY");
        }
        if (!begindate.isEmpty() && !enddate.isEmpty()) {
            t2.setWhere("lastrequestdate >= '"+begindate+"' and lastrequestdate<='"+enddate+" 23:59:59'");
        }
        t2.addQueryFields("lastrequestdate", "max(lastrequestdate)");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_usersite", "userid").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, t2, "t2", "t2.usersiteid=t1.usersiteid and t2.userid=t1.userid and t2.siteid=t1.siteid", "lastrequestdate");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_users", "t3", "t3.userid=t1.userid", "name");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("usertype", usertype);
        querySQL.setWhere("t3.`status` in ('active','inactive')");
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();


        ArrayList<Long> userids = rows.toArrayList("userid", new ArrayList<>());

        QuerySQL share = SQLFactory.createQuerySQL(this, "sys_sharelog", "createuserid");
        share.addQueryFields("count", "count(DISTINCT createuserid,shareuserid)");
        share.setSiteid(siteid);
        share.setWhere("createuserid", userids);
        if (type == 1) {
            share.setWhere("DATE(createdate) = CURDATE()");
        }
        if (type == 2) {
            share.setWhere("DATE(createdate) = CURDATE() - INTERVAL 1 DAY");
        }
        if (type == 3) {
            share.setWhere("createdate >= CURRENT_DATE - INTERVAL 7 DAY");
        }
        if (type == 4) {
            share.setWhere("createdate >= CURRENT_DATE - INTERVAL 30 DAY");
        }
        if (!begindate.isEmpty() && !enddate.isEmpty()) {
            t2.setWhere("createdate >= '"+begindate+"' and createdate<='"+enddate+" 23:59:59'");
        }
        share.addGroupBy("createuserid");
        RowsMap shareRowsMap = share.query().toRowsMap("createuserid");

        QuerySQL appointment = SQLFactory.createQuerySQL(this, "sa_appointment", "createuserid");
        appointment.addQueryFields("count", "count(DISTINCT createuserid,shareuserid)");
        appointment.setSiteid(siteid);
        appointment.setWhere("createuserid", userids);
        if (type == 1) {
            appointment.setWhere("DATE(createdate) = CURDATE()");
        }
        if (type == 2) {
            appointment.setWhere("DATE(createdate) = CURDATE() - INTERVAL 1 DAY");
        }
        if (type == 3) {
            appointment.setWhere("createdate >= CURRENT_DATE - INTERVAL 7 DAY");
        }
        if (type == 4) {
            appointment.setWhere("createdate >= CURRENT_DATE - INTERVAL 30 DAY");
        }
        if (!begindate.isEmpty() && !enddate.isEmpty()) {
            appointment.setWhere("createdate >= '"+begindate+"' and createdate<='"+enddate+" 23:59:59'");
        }
        appointment.addGroupBy("createuserid");
        RowsMap appointmentRowsMap = appointment.query().toRowsMap("createuserid");

        RowsMap areaRowsMap;
        if (usertype == 1) {
            QuerySQL area = SQLFactory.createQuerySQL(this, "sa_salearea_hr", "siteid").setTableAlias("t1");
            area.addJoinTable(JOINTYPE.inner, "sys_hr", "t2", "t2.hrid=t1.hrid and t2.siteid=t1.siteid", "userid");
            area.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", "t3.sa_saleareaid = t1.sa_saleareaid and t3.siteid=t1.siteid");
            area.addQueryFields("areaname", "GROUP_CONCAT(DISTINCT t3.areaname SEPARATOR',')");
            area.addGroupBy("userid", "siteid");
            area.setSiteid(siteid);
            area.setWhere("t2.userid", userids);
            areaRowsMap = area.query().toRowsMap("userid");
        } else {
            QuerySQL areaEnterprise = SQLFactory.createQuerySQL(this, "sys_enterprise_tradefield", "siteid").setTableAlias("t1");
            areaEnterprise.addJoinTable(JOINTYPE.inner, "sys_enterprise_hr", "t2", "t2.sys_enterpriseid=t1.sys_enterpriseid and t2.siteid=t1.siteid", "userid");
            areaEnterprise.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", "t3.sa_saleareaid = t1.sa_saleareaid and t3.siteid=t1.siteid");
            areaEnterprise.addQueryFields("areaname", "GROUP_CONCAT(DISTINCT t3.areaname SEPARATOR',')");
            areaEnterprise.addGroupBy("userid", "siteid");
            areaEnterprise.setSiteid(siteid);
            areaEnterprise.setWhere("t2.userid", userids);
            areaRowsMap = areaEnterprise.query().toRowsMap("userid");
        }


        for (Row row : rows) {
            row.putIfAbsent("lastrequestdate", "");
            Rows shareRows = shareRowsMap.getOrDefault(row.getString("userid"), new Rows());
            row.put("cust_count", shareRows.isEmpty() ? 0 : shareRows.get(0).getLong("count"));

            Rows appointmentRows = appointmentRowsMap.getOrDefault(row.getString("userid"), new Rows());
            row.put("appoint_count", appointmentRows.isEmpty() ? 0 : appointmentRows.get(0).getLong("count"));

            Rows areaRows = areaRowsMap.getOrDefault(row.getString("userid"), new Rows());
            row.put("areaname", areaRows.isEmpty() ? "" : areaRows.get(0).getString("areaname"));

        }

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


}