YOS_PRSX/src/custom/restcontroller/webmanage/sale/salestarget2/salestargetstatistics.java

package restcontroller.webmanage.sale.salestarget2;

import beans.CommonHepler;
import beans.brand.Brand;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.itemclass.ItemClass;
import beans.recycle.Recycle;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
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.sale.customer.ExportExcel;

import java.math.BigDecimal;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.time.temporal.IsoFields;
import java.util.*;
import java.util.stream.Collectors;

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

    @API(title = "营销目标", apiversion = R.ID2025122611124202.v1.class)
    public String marketingTarget() throws YosException {
        int type = content.getIntValue("type", 1);
        int year = content.getIntValue("year");
        int befyear = year - 1;
        int month = content.getIntValue("month");
        String currentMonth = year + "-" + (month > 9 ? month : "0" + month);

        Rows arearows = dbConnect.runSqlQuery("SELECT t1.* FROM view_areas t1 WHERE t1.siteid='" + siteid + "' ORDER BY t1.parentid,t1.sa_saleareaid");

        ArrayList<String> saleareaids = arearows.toArrayList("sa_saleareaid");
        ArrayList<String> alldates = yearToDate(year + "-12");
        SQLFactory sqlFactory = new SQLFactory(this, "依人员统计订单");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "依人员统计订单");
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "依医院统计订单");
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "依经销商统计订单");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("sa_saleareaid");

        sqlFactory = new SQLFactory(this, "依人员统计目标");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "依人员统计目标");
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "依医院统计目标");
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "依经销商统计目标");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("sa_saleareaid");

        Rows datas = new Rows();
        for (String sa_saleareaid : saleareaids) {
            Rows salesRows = salesRowsMap.getOrDefault(sa_saleareaid, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(sa_saleareaid, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("sa_saleareaid", sa_saleareaid);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("sa_saleareaid");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("sa_saleareaid"), new Rows());
            //计算当前月份
            ArrayList<String> dates = new ArrayList<>();
            dates.add(currentMonth);
            calculate("m", arearow, dataRows, dates);
            //计算季度
            calculate("s", arearow, dataRows, quarterToDate(currentMonth));
            //计算年度
            calculate("y", arearow, dataRows, yearToDate(currentMonth));
        }


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

    @API(title = "人员目标", apiversion = R.ID2025122611132802.v1.class)
    public String peopleTarget() throws YosException {
        int year = content.getIntValue("year");
        int befyear = year - 1;
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");

        Rows arearows = dbConnect.runSqlQuery(" SELECT t1.*,t3.`name`,t3.userid FROM view_areas t1 " +
                " INNER JOIN sa_salearea_hr t2 ON t2.sa_saleareaid=t1.sa_saleareaid " +
                " INNER JOIN sys_hr t3 ON t3.hrid=t2.hrid " +
                " WHERE t1.siteid='" + siteid + "' AND t1.parentid=" + sa_saleareaid +
                " ORDER BY t1.parentid,t1.sa_saleareaid");


        SQLFactory sqlFactory = new SQLFactory(this, "按人员分组统计订单");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("userid");

        sqlFactory = new SQLFactory(this, "按人员分组统计目标");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("userid");

        ArrayList<String> userids = arearows.toArrayList("userid");
        ArrayList<String> alldates = yearToDate(year + "-12");
        Rows datas = new Rows();
        for (String userid : userids) {
            Rows salesRows = salesRowsMap.getOrDefault(userid, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(userid, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("userid", userid);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("userid");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("userid"), new Rows());
            //计算月份
            for (String date : alldates) {
                String monthPart = date.split("-")[1];
                String fieldName = "m" + Integer.parseInt(monthPart);
                calculate(fieldName, arearow, dataRows, date);
            }
        }

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

    @API(title = "医院目标", apiversion = R.ID2025122611135102.v1.class)
    public String hospitalTarget() throws YosException {
        int year = content.getIntValue("year");
        int befyear = year - 1;
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");

        Rows arearows = dbConnect.runSqlQuery("SELECT t1.*,t3.`enterprisename` hospitalname,t2.sa_customersid FROM view_areas t1 " +
                " INNER JOIN sa_customers t2 ON t2.sa_saleareaid=t1.sa_saleareaid " +
                " INNER JOIN sys_enterprise t3 ON t3.sys_enterpriseid=t2.sys_enterpriseid " +
                " WHERE t1.siteid='" + siteid + "' AND t1.parentid= " + sa_saleareaid + " " +
                " ORDER BY t1.parentid,t1.sa_saleareaid ");


        SQLFactory sqlFactory = new SQLFactory(this, "按医院分组统计订单");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("sa_customersid");

        sqlFactory = new SQLFactory(this, "按医院分组统计目标");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("sa_customersid");

        ArrayList<String> sa_customersids = arearows.toArrayList("sa_customersid");
        ArrayList<String> alldates = yearToDate(year + "-12");
        Rows datas = new Rows();
        for (String sa_customersid : sa_customersids) {
            Rows salesRows = salesRowsMap.getOrDefault(sa_customersid, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(sa_customersid, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("sa_customersid", sa_customersid);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("sa_customersid");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("sa_customersid"), new Rows());
            //计算月份
            for (String date : alldates) {
                String monthPart = date.split("-")[1];
                String fieldName = "m" + Integer.parseInt(monthPart);
                calculate(fieldName, arearow, dataRows, date);
            }
        }

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

    @API(title = "经销商目标", apiversion = R.ID2025122714510802.v1.class)
    public String agentTarget() throws YosException {
        int year = content.getIntValue("year");
        int befyear = year - 1;
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");

        Rows arearows = dbConnect.runSqlQuery("SELECT t1.*,t3.enterprisename,t2.sa_agentsid FROM view_areas t1 " +
                " INNER JOIN sys_enterprise_tradefield t2 ON t2.sa_saleareaid=t1.sa_saleareaid " +
                " INNER JOIN sys_enterprise t3 ON t3.sys_enterpriseid=t2.sys_enterpriseid " +
                " WHERE t1.siteid='" + siteid + "' AND t1.parentid= " + sa_saleareaid + " " +
                " ORDER BY t1.parentid,t1.sa_saleareaid ");


        SQLFactory sqlFactory = new SQLFactory(this, "按经销商分组统计订单");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("sa_agentsid");

        sqlFactory = new SQLFactory(this, "按经销商分组统计目标");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("sa_agentsid");

        ArrayList<String> sa_agentsids = arearows.toArrayList("sa_agentsid");
        ArrayList<String> alldates = yearToDate(year + "-12");
        Rows datas = new Rows();
        for (String sa_agentsid : sa_agentsids) {
            Rows salesRows = salesRowsMap.getOrDefault(sa_agentsid, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(sa_agentsid, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("sa_agentsid", sa_agentsid);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("sa_agentsid");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("sa_agentsid"), new Rows());
            //计算月份
            for (String date : alldates) {
                String monthPart = date.split("-")[1];
                String fieldName = "m" + Integer.parseInt(monthPart);
                calculate(fieldName, arearow, dataRows, date);
            }
        }

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

    @API(title = "统计看板", apiversion = R.ID2025122915143202.v1.class)
    public String peoplePanel() throws YosException {
        int type = content.getIntValue("type", 1);
        int year = content.getIntValue("year");
        int befyear = year - 1;
        Long userid = content.getLongValue("userid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");


        Rows itemclassrows = dbConnect.runSqlQuery("SELECT DISTINCT itemclassname from plm_itemclass WHERE siteid='" + siteid + "' ");

        SQLFactory sqlFactory = new SQLFactory(this, "看板人员统计订单");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "看板人员统计订单");
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "看板医院统计订单");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "看板经销商统计订单");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);

        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("itemclassname");

        sqlFactory = new SQLFactory(this, "看板人员统计目标");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "看板人员统计目标");
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "看板医院统计目标");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "看板经销商统计目标");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);

        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("itemclassname");


        for (Row itemclassrow : itemclassrows) {
            String itemclassname = itemclassrow.getString("itemclassname");
            Rows salesRows = salesRowsMap.getOrDefault(itemclassname, new Rows());
            Rows targetRows = targetRowsMap.getOrDefault(itemclassname, new Rows());

            BigDecimal saleamount = BigDecimal.ZERO;
            BigDecimal saleamount_mom = BigDecimal.ZERO;
            BigDecimal saleamount_yoy = BigDecimal.ZERO;
            BigDecimal targetamount = BigDecimal.ZERO;
            for (Row salesRow : salesRows) {
                if (salesRow.getString("y").equals(String.valueOf(year))) {
                    saleamount = salesRow.getBigDecimal("amount");
                }
                if (salesRow.getString("y").equals(String.valueOf(befyear))) {
                    saleamount_mom = salesRow.getBigDecimal("amount");
                }
                if (salesRow.getString("y").equals(String.valueOf(befyear))) {
                    saleamount_yoy = salesRow.getBigDecimal("amount");
                }
            }
            for (Row targetRow : targetRows) {
                targetamount = targetRow.getBigDecimal("amount");
            }
            itemclassrow.put("y_saleamount", saleamount);
            itemclassrow.put("y_saleamount_mom", saleamount_mom);
            itemclassrow.put("y_saleamount_yoy", saleamount_yoy);
            itemclassrow.put("y_targetamount", targetamount);
            itemclassrow.put("y_balanceamount", saleamount.subtract(targetamount));
            //计算达成率
            itemclassrow.put("y_rate_achieve", calculateAchieveRate(saleamount, targetamount));
            //计算环比
            itemclassrow.put("y_rate_mom", calculateRate(saleamount, saleamount_mom));
            //计算同比
            itemclassrow.put("y_rate_yoy", calculateRate(saleamount, saleamount_yoy));

        }


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

    @API(title = "统计明细", apiversion = R.ID2025123014532102.v1.class)
    public String targetMX() throws YosException {
        int type = content.getIntValue("type", 1);
        int year = content.getIntValue("year");
        int befyear = year - 1;
        String yearMonthStr = year + "-12";
        Long userid = content.getLongValue("userid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("sa_customersid") && !"".equals(whereObject.getString("sa_customersid"))) {
                where.append(" and(");
                where.append("t4.sa_customersid ='").append(whereObject.getString("sa_customersid")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("sa_hospitaldepid") && !"".equals(whereObject.getString("sa_hospitaldepid"))) {
                where.append(" and(");
                where.append("t1.sa_hospitaldepid ='").append(whereObject.getString("sa_hospitaldepid")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("itemclassname") && !"".equals(whereObject.getString("itemclassname"))) {
                where.append(" and(");
                where.append("t8.itemclassname ='").append(whereObject.getString("itemclassname")).append("' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询人员的统计商品明细");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "查询人员的统计商品明细");
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "查询医院的统计商品明细");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "查询经销商的统计商品明细");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter_SQL("where", where);
        Rows itemrows = dbConnect.runSqlQuery(sqlFactory);

        sqlFactory = new SQLFactory(this, "查询人员的订单商品明细统计");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "查询人员的订单商品明细统计");
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "查询医院的订单商品明细统计");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "查询经销商的订单商品明细统计");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("keyid");

        sqlFactory = new SQLFactory(this, "查询人员的目标商品明细统计");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "查询人员的目标商品明细统计");
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "查询医院的目标商品明细统计");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "查询经销商的目标商品明细统计");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("keyid");

        ArrayList<String> keyids = itemrows.toArrayList("keyid");
        ArrayList<String> alldates = yearToDate(yearMonthStr);
        Rows datas = new Rows();
        for (String keyid : keyids) {
            Rows salesRows = salesRowsMap.getOrDefault(keyid, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(keyid, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("keyid", keyid);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("keyid");

        for (Row itemrow : itemrows) {
            Rows dataRows = dataRowsMap.getOrDefault(itemrow.getString("keyid"), new Rows());
            //计算月份
            for (String date : alldates) {
                String monthPart = date.split("-")[1];
                String fieldName = "m" + Integer.parseInt(monthPart);
                calculate(fieldName, itemrow, dataRows, date);
            }
            //计算年度
            calculate("y", itemrow, dataRows, yearToDate(yearMonthStr));
        }

        Row totalRow = new Row();
        totalRow.put("hospitalname", "合计");
        totalRow.put("hospitaldepname", "合计");
        // 处理1-12月数据
        for (int i = 1; i <= 12; i++) {
            processMonthData(itemrows, totalRow, "m" + i);
        }
        // 处理年度数据
        processMonthData(itemrows, totalRow, "y");
        itemrows.add(0, totalRow);


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


    @API(title = "营销类别明细", apiversion = R.ID2025123014533002.v1.class)
    public String itemclassname() throws YosException {

        Rows rows = dbConnect.runSqlQuery("SELECT DISTINCT itemclassname from plm_itemclass WHERE siteid='" + siteid + "'");


        return getSucReturnObject().setData(rows.toArrayList("itemclassname")).toString();
    }

    @API(title = "订单明细", apiversion = R.ID2025123114300602.v1.class)
    public String orderMX() throws YosException {

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t2.checkdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t2.checkdate <='").append(whereObject.getString("enddate")).append(" 23:59:59' ");
            }
        }

        String dateType = content.getString("dateType");
        int type = content.getIntValue("type", 1);
        Long userid = content.getLongValue("userid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems",
                "*");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t2", "t2.sa_orderid=t1.sa_orderid and t2.siteid=t1.siteid",
                "sonum", "checkdate");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t3.itemid = t1.itemid and t3.siteid = t1.siteid and t3.isshow=1",
                "orderminqty_auxunit", "orderminqty", "orderaddqty_auxunit", "orderaddqty", "assistance", "goodstype", "explains");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemextend", "t4", "t4.itemid = t1.itemid and t4.siteid = t1.siteid",
                "erpitemname", "erpitemno");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t6", "t6.hrid=t2.saler_hrid and t6.siteid=t2.siteid");

        querySQL.addQueryFields("salename", "t6.name");
        if (type == 1) {
            querySQL.setWhere("t6.userid", userid);
        }
        if (type == 2) {
            querySQL.setWhere("t2.sa_customersid", sa_customersid);
        }
        if (type == 3) {
            querySQL.setWhere("t2.sa_agentsid", sa_agentsid);
        }

        if (dateType.equals("本年")) {
            querySQL.setWhere("year(t2.checkdate) in (year(current_date))");
        }
        if (dateType.equals("本季")) {
            querySQL.setWhere("QUARTER(t2.checkdate) = QUARTER(CURDATE()) and year(t2.checkdate) in (year(current_date))");
        }
        if (dateType.equals("本月")) {
            querySQL.setWhere("DATE_FORMAT( t2.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
        }
        if (dateType.equals("上月")) {
            querySQL.setWhere("DATE_FORMAT( t2.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) - INTERVAL 1 MONTH , '%Y%m' )");
        }
        if (dateType.equals("上季")) {
            querySQL.setWhere("YEAR(t2.checkdate) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 QUARTER)) AND QUARTER(t2.checkdate) = QUARTER(DATE_SUB(CURDATE(), INTERVAL 1 QUARTER))");
        }

        querySQL.setWhere(where.toString());
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setTableAlias("t1");
        querySQL.setWhere("t2.`status` in ('审核', '关闭')");
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        // 商品品牌
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids);
        RowsMap itemclassRowsMap = ItemClass.getAllItemClassRowsMap(this, ids);
        for (Row row : rows) {
            Rows brandRows = brandRowsMap.getOrDefault(row.getString("itemid"), new Rows());
            Rows itemclassRows = itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows());
            row.put("brandname", StringUtils.join(brandRows.toArray("brandname"), ","));
            row.put("itemclassname", StringUtils.join(itemclassRows.toArray("itemclassname"), ","));
        }


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


    }


    private void processDataRows(Rows datas, Row row, Rows dateDate, Rows targetRows, String date) {
        row.put("ym", date);
        row.put("amount", dateDate.isNotEmpty() ? dateDate.get(0).getBigDecimal("amount") : 0);
        //根据日期中的月份（如 "11"）从 targetRows 中动态取出对应的 m{月}amount 字段值
        String monthPart = date.split("-")[1];
        String fieldName = "m" + Integer.parseInt(monthPart) + "amount";
        BigDecimal target = BigDecimal.ZERO;
        if (!targetRows.isEmpty()) {
            Object val = targetRows.get(0).get(fieldName);
            if (val instanceof BigDecimal) {
                target = (BigDecimal) val;
            } else if (val != null) {
                target = new BigDecimal(val.toString());
            }
        }
        row.put("target", target);
        datas.add(row);
    }

    //计算合计
    private void processMonthData(Rows itemrows, Row row, String key) {
        BigDecimal saleamount = itemrows.sum(key + "_saleamount");
        BigDecimal saleamount_mom = itemrows.sum(key + "_saleamount_mom");
        BigDecimal saleamount_yoy = itemrows.sum(key + "_saleamount_yoy");
        BigDecimal targetamount = itemrows.sum(key + "_targetamount");

        row.put(key + "_saleamount", saleamount);
        row.put(key + "_saleamount_mom", saleamount_mom);
        row.put(key + "_saleamount_yoy", saleamount_yoy);
        row.put(key + "_targetamount", targetamount);

        // 计算达成率
        row.put(key + "_rate_achieve", calculateAchieveRate(saleamount, targetamount));
        // 计算环比
        row.put(key + "_rate_mom", calculateRate(saleamount, saleamount_mom));
        // 计算同比
        row.put(key + "_rate_yoy", calculateRate(saleamount, saleamount_yoy));
    }


    public void calculate(String key, Row arearow, Rows dataRows, ArrayList<String> dates) {
        BigDecimal saleamount = BigDecimal.ZERO;
        BigDecimal saleamount_mom = BigDecimal.ZERO;
        BigDecimal saleamount_yoy = BigDecimal.ZERO;
        BigDecimal targetamount = BigDecimal.ZERO;
        for (String month : dates) {
            for (Row dataRow : dataRows) {
                String ym = dataRow.getString("ym");
                String momMonth = getMomMonth(month);
                String yoyMonth = getYoyMonth(month);
                BigDecimal amount = dataRow.getBigDecimal("amount");
                BigDecimal target = dataRow.getBigDecimal("target");
                if (ym.equals(month)) {
                    saleamount = saleamount.add(amount);
                    targetamount = targetamount.add(target);
                }
                if (ym.equals(momMonth)) {
                    saleamount_mom = saleamount_mom.add(amount);
                }
                if (ym.equals(yoyMonth)) {
                    saleamount_yoy = saleamount_yoy.add(amount);
                }
            }
        }
        arearow.putIfAbsent(key + "_saleamount", saleamount);
        arearow.putIfAbsent(key + "_saleamount_mom", saleamount_mom);
        arearow.putIfAbsent(key + "_saleamount_yoy", saleamount_yoy);
        arearow.putIfAbsent(key + "_targetamount", targetamount);
        arearow.putIfAbsent(key + "_balanceamount", saleamount.subtract(targetamount));
        //计算达成率
        arearow.put(key + "_rate_achieve", calculateAchieveRate(saleamount, targetamount));
        //计算环比
        arearow.put(key + "_rate_mom", calculateRate(saleamount, saleamount_mom));
        //计算同比
        arearow.put(key + "_rate_yoy", calculateRate(saleamount, saleamount_yoy));
    }

    public void calculate(String key, Row arearow, Rows dataRows, String month) {
        BigDecimal saleamount = BigDecimal.ZERO;
        BigDecimal saleamount_mom = BigDecimal.ZERO;
        BigDecimal saleamount_yoy = BigDecimal.ZERO;
        BigDecimal targetamount = BigDecimal.ZERO;

        for (Row dataRow : dataRows) {
            String ym = dataRow.getString("ym");
            String momMonth = getMomMonth(month);
            String yoyMonth = getYoyMonth(month);
            BigDecimal amount = dataRow.getBigDecimal("amount");
            BigDecimal target = dataRow.getBigDecimal("target");
            if (ym.equals(month)) {
                saleamount = saleamount.add(amount);
                targetamount = targetamount.add(target);
            }
            if (ym.equals(momMonth)) {
                saleamount_mom = saleamount_mom.add(amount);
            }
            if (ym.equals(yoyMonth)) {
                saleamount_yoy = saleamount_yoy.add(amount);
            }
        }

        arearow.putIfAbsent(key + "_saleamount", saleamount);
        arearow.putIfAbsent(key + "_saleamount_mom", saleamount_mom);
        arearow.putIfAbsent(key + "_saleamount_yoy", saleamount_yoy);
        arearow.putIfAbsent(key + "_targetamount", targetamount);
        //计算达成率
        arearow.put(key + "_rate_achieve", calculateAchieveRate(saleamount, targetamount));
        //计算环比
        arearow.put(key + "_rate_mom", calculateRate(saleamount, saleamount_mom));
        //计算同比
        arearow.put(key + "_rate_yoy", calculateRate(saleamount, saleamount_yoy));
        arearow.put("month", month);
    }

    //计算达成率
    public String calculateAchieveRate(BigDecimal saleamount, BigDecimal targetamount) {
        // 参数校验
        if (saleamount == null || targetamount == null) {
            return "-";
        }
        // 检查目标金额是否为0
        if (targetamount.compareTo(BigDecimal.ZERO) == 0) {
            return "-";
        }
        try {
            // 计算达成率：(saleamount / targetamount) * 100
            BigDecimal rate = saleamount.divide(targetamount, 10, BigDecimal.ROUND_HALF_UP)
                    .multiply(BigDecimal.valueOf(100))
                    .setScale(2, BigDecimal.ROUND_HALF_UP);
            return rate + "%";
        } catch (ArithmeticException e) {
            // 处理除法结果为无限循环小数的情况
            return "-";
        }
    }

    //计算环比
    public String calculateRate(BigDecimal saleamount, BigDecimal saleamount2) {
        if (saleamount2.compareTo(BigDecimal.ZERO) == 0) {
            return "-";
        } else {
            // 先计算差值
            BigDecimal difference = saleamount.subtract(saleamount2);
            // 计算比率，使用指定精度避免无限循环小数
            BigDecimal rate = difference.divide(saleamount2, 10, BigDecimal.ROUND_HALF_UP);
            // 乘以100转换为百分比并保留2位小数
            BigDecimal percentage = rate.multiply(BigDecimal.valueOf(100))
                    .setScale(2, BigDecimal.ROUND_HALF_UP);
            return percentage + "%";
        }
    }

    //当年1月 到 指定月份
    public static ArrayList<String> yearToDate(String yearMonthStr) {
        YearMonth target = YearMonth.parse(yearMonthStr);

        ArrayList<String> yearToDate = new ArrayList<>();
        YearMonth startOfYear = YearMonth.of(target.getYear(), 1);
        for (YearMonth ym = startOfYear; !ym.isAfter(target); ym = ym.plusMonths(1)) {
            yearToDate.add(ym.toString());
        }
        return yearToDate;
    }

    //  季度起始月 到 指定月份
    public static ArrayList<String> quarterToDate(String yearMonthStr) {
        YearMonth target = YearMonth.parse(yearMonthStr);
        int quarter = target.get(IsoFields.QUARTER_OF_YEAR); // 1~4
        int startMonthOfQuarter = (quarter - 1) * 3 + 1;   // Q1=1, Q2=4, Q3=7, Q4=10
        YearMonth startOfQuarter = YearMonth.of(target.getYear(), startMonthOfQuarter);

        ArrayList<String> quarterToDate = new ArrayList<>();
        for (YearMonth ym = startOfQuarter; !ym.isAfter(target); ym = ym.plusMonths(1)) {
            quarterToDate.add(ym.toString());
        }
        return quarterToDate;
    }

    //环比日期
    public static String getMomMonth(String yyyyMM) {
        YearMonth ym = YearMonth.parse(yyyyMM);
        return ym.minusMonths(1).format(DateTimeFormatter.ofPattern("yyyy-MM"));
    }

    //同比日期
    public static String getYoyMonth(String yyyyMM) {
        YearMonth ym = YearMonth.parse(yyyyMM);
        return ym.minusYears(1).format(DateTimeFormatter.ofPattern("yyyy-MM"));
    }

    public static ArrayList<String> getMonths(int year, int month_start, int month_end) {
        YearMonth ymstart = YearMonth.of(year, month_start);
        YearMonth ymend = YearMonth.of(year, month_end);
        ArrayList<String> months = new ArrayList<>();
        for (YearMonth ym = ymstart; !ym.isAfter(ymend); ym = ym.plusMonths(1)) {
            months.add(ym.toString());                   // 本期
        }

        return months;
    }

    @API(title = "小程序人员目标概况", apiversion = R.ID2026010509531602.v1.class)
    public String mAPeopleInfo() throws YosException {
        int year = content.getIntValue("year");
        int befyear = year - 1;
        int month = content.getIntValue("month");
        String currentMonth = year + "-" + (month > 9 ? month : "0" + month);
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");

        ArrayList<Long> sa_saleareaids = new ArrayList<>();
        if (sa_saleareaid == 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr  WHERE  siteid='" + siteid + "' and hrid=" + hrid);
            sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            if (sa_saleareaids.size() == 0) {
                rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea  WHERE  siteid='" + siteid + "' and level=1");
                sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            }
        } else {
            sa_saleareaids.add(sa_saleareaid);
        }

        ArrayList<Long> subSaleAreaIds = SaleArea.getSubSaleAreaIds(this, sa_saleareaids);
        subSaleAreaIds.add(sa_saleareaid);
        StringBuffer where = new StringBuffer(" 1=1 ");
        String sql = " and t4.sa_saleareaid in " + subSaleAreaIds + " ";
        sql = sql.replace("[", "(").replace("]", ")");
        where.append(sql);

        Rows arearows = new Rows();
        Row temprow = new Row();
        temprow.put("yearmonth", currentMonth);
        arearows.add(temprow);


        SQLFactory sqlFactory = new SQLFactory(this, "小程序人员订单概况");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("ym");

        sqlFactory = new SQLFactory(this, "小程序人员目标概况");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter_SQL("where", where);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);


        Rows datas = new Rows();
        Rows salesRows = salesRowsMap.getOrDefault(currentMonth, new Rows());
        RowsMap dateDateRow = salesRows.toRowsMap("ym");
        for (String date : yearToDate(year + "-12")) {
            Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
            Row row = new Row();
            row.put("currentMonth", currentMonth);
            processDataRows(datas, row, dateDate, targetrows, date);
        }


        RowsMap dataRowsMap = datas.toRowsMap("tempMonth");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("tempMonth"), new Rows());
            //计算当前月份
            ArrayList<String> dates = new ArrayList<>();
            dates.add(currentMonth);
            calculate("m", arearow, dataRows, dates);
            //计算季度
            calculate("s", arearow, dataRows, quarterToDate(currentMonth));
            //计算年度
            calculate("y", arearow, dataRows, yearToDate(currentMonth));
        }


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

    @API(title = "小程序人员/医院/经销商目标", apiversion = R.ID2026010610085402.v1.class)
    public String mATarget() throws YosException {
        int type = content.getIntValue("type", 1);
        int year = content.getIntValue("year");
        int befyear = year - 1;
        int month_start = content.getIntValue("month_start");
        int month_end = content.getIntValue("month_end");
        String yearMonthStr = year + "-12";
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");
        Long userid = content.getLongValue("userid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");

        ArrayList<Long> sa_saleareaids = new ArrayList<>();
        if (sa_saleareaid == 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr  WHERE  siteid='" + siteid + "' and hrid=" + hrid);
            sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            if (sa_saleareaids.size() == 0) {
                rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea  WHERE  siteid='" + siteid + "' and level=1");
                sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            }
        } else {
            sa_saleareaids.add(sa_saleareaid);
        }

        ArrayList<Long> subSaleAreaIds = SaleArea.getSubSaleAreaIds(this, sa_saleareaids);
        subSaleAreaIds.add(sa_saleareaid);
        StringBuffer where = new StringBuffer(" 1=1 ");
        String sql = " and t4.sa_saleareaid in " + subSaleAreaIds + " ";
        sql = sql.replace("[", "(").replace("]", ")");
        where.append(sql);

        Rows arearows = new Rows();
        Row temprow = new Row();
        temprow.put("year", year);
        temprow.put("month_start", month_start);
        temprow.put("month_end", month_end);
        arearows.add(temprow);
        SQLFactory sqlFactory = new SQLFactory(this, "小程序人员订单");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "小程序人员订单");
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "小程序医院订单");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "小程序经销商订单");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("ym");

        sqlFactory = new SQLFactory(this, "小程序人员目标");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "小程序人员目标");
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "小程序医院目标");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "小程序经销商目标");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);

        ArrayList<String> alldates = yearToDate(yearMonthStr);
        Rows datas = new Rows();

        for (String date : alldates) {
            Rows dateDate = salesRowsMap.getOrDefault(date, new Rows());
            Row row = new Row();
            row.put("date", date);
            processDataRows(datas, row, dateDate, targetrows, date);
        }

        RowsMap dataRowsMap = datas.toRowsMap("tempMonth");

        for (Row arearow : arearows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("tempMonth"), new Rows());
            //计算范围
            calculate("m", arearow, dataRows, getMonths(year, month_start, month_end));
        }

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

    @API(title = "小程序人员/医院/经销商产品类别", apiversion = R.ID2026010714131502.v1.class)
    public String mATargetCategory() throws YosException {
        int type = content.getIntValue("type", 1);
        int year = content.getIntValue("year");
        int befyear = year - 1;
        int month_start = content.getIntValue("month_start");
        int month_end = content.getIntValue("month_end");
        String yearMonthStr = year + "-12";
        Long sa_saleareaid = content.getLongValue("sa_saleareaid");
        Long userid = content.getLongValue("userid");
        Long sa_customersid = content.getLongValue("sa_customersid");
        Long sa_agentsid = content.getLongValue("sa_agentsid");

        ArrayList<Long> sa_saleareaids = new ArrayList<>();
        if (sa_saleareaid == 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr  WHERE  siteid='" + siteid + "' and hrid=" + hrid);
            sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            if (sa_saleareaids.size() == 0) {
                rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea  WHERE  siteid='" + siteid + "' and level=1");
                sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
            }
        } else {
            sa_saleareaids.add(sa_saleareaid);
        }

        ArrayList<Long> subSaleAreaIds = SaleArea.getSubSaleAreaIds(this, sa_saleareaids);
        subSaleAreaIds.add(sa_saleareaid);
        StringBuffer where = new StringBuffer(" 1=1 ");
        String sql = " and t4.sa_saleareaid in " + subSaleAreaIds + " ";
        sql = sql.replace("[", "(").replace("]", ")");
        where.append(sql);

        Rows itemclassrows = dbConnect.runSqlQuery("SELECT DISTINCT itemclassname from plm_itemclass WHERE siteid='" + siteid + "' ");


        SQLFactory sqlFactory = new SQLFactory(this, "小程序人员订单-产品类别");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "小程序人员订单-产品类别");
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "小程序医院订单-产品类别");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "小程序经销商订单-产品类别");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("befyear", befyear);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap salesRowsMap = rows.toRowsMap("itemclassname");

        sqlFactory = new SQLFactory(this, "小程序人员目标-产品类别");
        if (type == 1) {
            sqlFactory = new SQLFactory(this, "小程序人员目标-产品类别");
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter("userid", userid);
        }
        if (type == 2) {
            sqlFactory = new SQLFactory(this, "小程序医院目标-产品类别");
            sqlFactory.addParameter("sa_customersid", sa_customersid);
        }
        if (type == 3) {
            sqlFactory = new SQLFactory(this, "小程序经销商目标-产品类别");
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        Rows targetrows = dbConnect.runSqlQuery(sqlFactory);
        RowsMap targetRowsMap = targetrows.toRowsMap("itemclassname");

        ArrayList<String> itemclassnames = itemclassrows.toArrayList("itemclassname");
        ArrayList<String> alldates = yearToDate(yearMonthStr);
        Rows datas = new Rows();
        for (String itemclassname : itemclassnames) {
            Rows salesRows = salesRowsMap.getOrDefault(itemclassname, new Rows());
            RowsMap dateDateRow = salesRows.toRowsMap("ym");
            Rows targetRows = targetRowsMap.getOrDefault(itemclassname, new Rows());
            for (String date : alldates) {
                Rows dateDate = dateDateRow.getOrDefault(date, new Rows());
                Row row = new Row();
                row.put("itemclassname", itemclassname);
                processDataRows(datas, row, dateDate, targetRows, date);
            }
        }

        RowsMap dataRowsMap = datas.toRowsMap("itemclassname");

        for (Row arearow : itemclassrows) {
            Rows dataRows = dataRowsMap.getOrDefault(arearow.getString("itemclassname"), new Rows());
            //计算范围
            calculate("m", arearow, dataRows, getMonths(year, month_start, month_end));
        }

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


    @API(title = "查询当前账号的营销区域", apiversion = R.ID2026010513574702.v1.class)
    public String queryCurrentUserarea() throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT t1.*FROM sa_salearea t1 INNER JOIN sa_salearea_hr t2 ON t2.sa_saleareaid=t1.sa_saleareaid WHERE t2.hrid=" + hrid + " and t1.siteid='" + siteid + "'");
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询当前账号所在区域以及下级区域的医院", apiversion = R.ID2026010516151802.v1.class)
    public String hospitalList() throws YosException {
        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("or t3.areaname like'%").append(whereObject.getString("condition")).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(sa_saleareaid);
                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(")");
            }

        }

        where.append(" and ( t1.datastatus in (0,2) )");


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_customers", "sa_customersid", "createby", "createdate",
                "sys_enterpriseid", "status", "type", "tradingstatus", "datastatus", "billno", "province", "city", "county", "address", "stagename", "grade", "sa_saleareaid");
        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.addJoinTable(JOINTYPE.left, "sys_dataextend", "t10", "t1.siteid = t10.siteid and t1.sa_customersid = t10.ownerid and t10.ownertable = 'sa_customers'", "deletereason");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.sa_saleareaid", getUserAreas());
        querySQL.setPage(pageSize, pageNumber);
        if (pageSorting.equals("''")) {
            pageSorting = "t1.createdate desc";
        }
        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);
        Rows stageRows = dbConnect.runSqlQuery("SELECT stagename,sequence from sa_devstage WHERE siteid='" + siteid + "' order by sequence");
        RowsMap AgentRowsMap = CommonHepler.getAgentRowsMap(this, ids);
        RowsMap KeyDoctorsRowsMap = CommonHepler.getKeyDoctorsRowsMap(this, ids);
        RowsMap opRowsMap = CommonHepler.getHospitalOPRowsMap(this);
        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>();
            //非系统标签
            row.put("tag", tag);
            //系统标签
            row.put("tag_sys", sys_tag);
            ArrayList<String> tags = new ArrayList<>();
            tags.addAll(tag);
            tags.addAll(sys_tag);
            row.put("tagstr", StringUtils.join(tags, ","));
            row.putIfAbsent("followdate", "");
            //预估手术总量
            Rows totalop = opRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("totalop", totalop.isNotEmpty() ? totalop.get(0).getInteger("qty") : 0);
            //关键人
            Rows KeyDoctors = KeyDoctorsRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("keyperson", StringUtils.join(KeyDoctors.toArray("doctorname"), ","));
            //签约经销商
            Rows signagent = AgentRowsMap.getOrDefault(String.valueOf(id), new Rows());
            row.putIfAbsent("signagent", StringUtils.join(signagent.toArray("enterprisename"), ","));
            String stagename = row.getString("stagename");
            for (Row stageRow : stageRows) {
                if (stagename.equals(stageRow.getString("stagename"))) {
                    stageRow.put("active", 1);
                }
                stageRow.putIfAbsent("active", 0);
            }
            row.put("stages", stageRows);
        }

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

    @API(title = "查询当前账号所在区域以及下级区域的经销商", apiversion = R.ID2026010516152802.v1.class)
    public String agentList() 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.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpagentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t5.salernames like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        where.append(" and t1.status !='作废' ");

        JSONArray sa_saleareaidsArray = content.getJSONArray("sa_saleareaids");
        ArrayList<Long> sa_saleareaidsList = new ArrayList<>();
        for (Object o : sa_saleareaidsArray) {
            String sa_saleareaid = o.toString();
            sa_saleareaidsList.add(Long.parseLong(sa_saleareaid));
            sa_saleareaidsList.addAll(SaleArea.getSubSaleAreaIds(this, Long.parseLong(sa_saleareaid)));
        }

        if (sa_saleareaidsList.size() > 0) {
            String sqlStr = " and t1.sys_enterpriseid in (SELECT DISTINCT sys_enterpriseid from sys_enterprise_tradefield WHERE sa_saleareaid in " + sa_saleareaidsList + " )";
            sqlStr = sqlStr.replace("[", "(").replace("]", ")");
            where.append(sqlStr);
        } else {
            String sqlStr = " and t1.sys_enterpriseid in (SELECT DISTINCT sys_enterpriseid from sys_enterprise_tradefield WHERE sa_saleareaid in " + getUserAreas() + " and hrid ='" + hrid + "' )";
            sqlStr = sqlStr.replace("[", "(").replace("]", ")");
            where.append(sqlStr);
        }


        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商业务员");
        sqlFactory.addParameter("siteid", siteid);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents",
                        "sa_agentsid", "sys_enterpriseid", "gmname", "gmphonenumber", "agentnum", "cooperatetype", "remarks", "status", "createdate", "scale", "mainproducts").
                setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename", "province", "city", "county", "address");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t3", "t1.sys_enterpriseid = t3.ownerid and ownertable = 'sys_enterprise' and t1.siteid = t3.siteid",
                "erpagentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_dataextend", "t4", "t1.siteid = t4.siteid and t1.sa_agentsid = t4.ownerid and t4.ownertable = 'sa_agents'",
                "deletereason");
        querySQL.addJoinTable(JOINTYPE.left, sqlFactory, "t5", "t5.sa_agentsid = t1.sa_agentsid", "salernames");
        querySQL.addQueryFields("deletechangedate", "t4.changedate");
        querySQL.addQueryFields("deletechangeby", "t4.changeby");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);

        querySQL.setPage(pageSize, pageNumber);

        querySQL.setOrderBy("t1.createdate desc");
        Rows rows = querySQL.query();

        RowsMap areaRowsMap = CommonHepler.getAreaRowsMap(this, rows.toArrayList("sa_agentsid", new ArrayList<>()));
        for (Row row : rows) {
            row.put("p_c_c", row.getString("province") + "-" + row.getString("city") + "-" + row.getString("county"));
            Rows areaRows = areaRowsMap.getOrDefault(row.getString("sa_agentsid"), new Rows());
            row.put("areanames", StringUtils.join(areaRows.toArrayList("areaname"), ","));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    public ArrayList<Long> getUserAreas() throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr  WHERE  siteid='" + siteid + "' and hrid=" + hrid);
        ArrayList<Long> sa_saleareaids = rows.toArrayList("sa_saleareaid", new ArrayList<>());
        ArrayList<Long> subSaleAreaIds = SaleArea.getSubSaleAreaIds(this, sa_saleareaids);
        subSaleAreaIds.addAll(sa_saleareaids);
        subSaleAreaIds.add(-1L);
        return subSaleAreaIds;
    }


}