CUCU2/src/custom/restcontroller/webmanage/sale/salestarget/personnelstatistics.java

package restcontroller.webmanage.sale.salestarget;

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.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.saletool.orderclue.ExportExcel;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;

@API(title = "管理端-销售目标-人员目标统计")
public class personnelstatistics extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public personnelstatistics(JSONObject content) throws YosException {
        super(content);

    }

    @API(title = "人员目标导出", apiversion = R.ID20220916095402.v1.class)
    public String export() throws YosException {

        JSONArray array = content.getJSONArray("row");

        ExcelFactory excelFactory = new ExcelFactory("人员目标导出模板");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        personnelexcel.setBatchDetailSheetColumn3(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook);
        personnelexcel.batchDetailExport(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook, array);// 写入标题

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

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        personnelexcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        personnelexcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 2);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta( keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger(keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong(keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat(keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble(keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString(keylist.get(i1)));
                }
                if (i1 == 36) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
//    @API(title = "获取区域经理的营销区域", apiversion = R.ID20231018093803.v1.class)
//    public String getcurrentusersalearea() throws YosException {
//
//        SQLFactory sqlFactory = new SQLFactory(this, "业务员列表查询");
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("hrid", hrid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
//
//        SQLFactory areasqlFactory = new SQLFactory(this, "下级区域获取");
//        areasqlFactory.addParameter("siteid", siteid);
//        areasqlFactory.addParameter_in("sa_saleareaid", rows.toArrayList("sa_saleareaid",new ArrayList<>()));
//        Rows arearows = dbConnect.runSqlQuery(areasqlFactory.getSQL());
//        return getSucReturnObject().setData(arearows).toString();
//    }

    @API(title = "查询当前账户负责的区域", apiversion = R.ID20231108160203.v1.class)
    @CACHEING
    public String queryList_currentArea() throws YosException {
        SQLFactory hrsqlFactory = new SQLFactory(this, "业务员列表查询");
        hrsqlFactory.addParameter("siteid", siteid);
        hrsqlFactory.addParameter("hrid", hrid);
        Rows hrrows = dbConnect.runSqlQuery(hrsqlFactory.getSQL());
        return getSucReturnObject().setData(hrrows).toString();
    }


    @API(title = "人员目标统计(区域经理)", apiversion = R.ID20231018103203.v1.class)
    @CACHEING
    public String queryList_areamanager() throws YosException {
        //年份
        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        Rows billRows = dbConnect.runSqlQuery("SELECT sa_salestargetbillid,assessmentindicators,statisticaldimension,sa_accountclassids FROM sa_salestargetbill WHERE  year="+year+"  AND siteid ='" + siteid + "' and  targettype = '人员目标'");

        SQLFactory hrsqlFactory = new SQLFactory(this, "业务员列表查询");
        hrsqlFactory.addParameter("siteid", siteid);
        hrsqlFactory.addParameter("hrid", hrid);
        Rows hrrows = dbConnect.runSqlQuery(hrsqlFactory.getSQL());
        RowsMap hrrowsMap = hrrows.toRowsMap("sa_saleareaid");
//        if(sa_saleareaid==0){
//            if(hrrows.isNotEmpty()){
//                sa_saleareaid=hrrows.get(0).getLong("sa_saleareaid");
//            }
//        }
        SQLFactory areasqlFactory = new SQLFactory(this, "下级区域获取");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter_in("sa_saleareaid", hrrows.toArrayList("sa_saleareaid",new ArrayList<>()));
        Rows arearows = dbConnect.runSqlQuery(areasqlFactory.getSQL());
        ArrayList<Long> areaList = arearows.toArrayList("sa_saleareaid",new ArrayList<Long>());


        //考核指标类型
        long sa_salestargetbillid = 0;
        String assessmentindicators="";
        //统计维度
        String statisticaldimension="";
        //账户
        String sa_accountclassids="";
        if (billRows.isNotEmpty()) {
            sa_salestargetbillid=billRows.get(0).getLong("sa_salestargetbillid");
            assessmentindicators=billRows.get(0).getString("assessmentindicators");
            statisticaldimension=billRows.get(0).getString("statisticaldimension");
            sa_accountclassids=billRows.get(0).getString("sa_accountclassids");
        }

        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer();
        if(areaList.size()>0){
             where = where.append(" 1=1 and t.sa_saleareaid in "+areaList.toString().replace("[", "(").replace("]", ")"));
        }else{
            where = where.append(" 1=1 ");
        }


        SQLFactory sqlFactory = new SQLFactory(this, "人员-目标详情列表");
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        Rows saleareas = dbConnect.runSqlQuery("select * from sa_salearea where ifnull(parentid,0)=0 and siteid='"+siteid+"'");
        Long sa_saleareaidtop=0l;
        String areanametop="";
        if(!saleareas.isEmpty() && saleareas.size()==1){
            sa_saleareaidtop=saleareas.get(0).getLong("sa_saleareaid");
            areanametop=saleareas.get(0).getString("areaname");
            for (Row row :rows) {
                if(row.getLong("parentid")==0){
                    row.put("y1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1l"));
                    row.put("y1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1h"));
                    row.put("s1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1l"));
                    row.put("s1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1h"));
                    row.put("s2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2l"));
                    row.put("s2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2h"));
                    row.put("s3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3l"));
                    row.put("s3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3h"));
                    row.put("s4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4l"));
                    row.put("s4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4h"));
                    row.put("m1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1l"));
                    row.put("m1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1h"));
                    row.put("m2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2l"));
                    row.put("m2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2h"));
                    row.put("m3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3l"));
                    row.put("m3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3h"));
                    row.put("m4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4l"));
                    row.put("m4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4h"));
                    row.put("m5l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5l"));
                    row.put("m5h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5h"));
                    row.put("m6l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6l"));
                    row.put("m6h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6h"));
                    row.put("m7l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7l"));
                    row.put("m7h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7h"));
                    row.put("m8l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8l"));
                    row.put("m8h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8h"));
                    row.put("m9l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9l"));
                    row.put("m9h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9h"));
                    row.put("m10l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10l"));
                    row.put("m10h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10h"));
                    row.put("m11l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11l"));
                    row.put("m11h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11h"));
                    row.put("m12l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12l"));
                    row.put("m12h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12h"));
                }
            }
        }




        Rows actualRows = new Rows();
        //开票
        if (assessmentindicators .equals("开票")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-开票");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);

        }
        //订单
        if (assessmentindicators .equals("订单")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-订单");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //出货
        if (assessmentindicators .equals("出货")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-出货");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //收款
        if (assessmentindicators .equals("收款")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-收款");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }

            sqlFactory.addParameter("sa_saleareaid", sa_saleareaidtop);
            sqlFactory.addParameter("areaname", areanametop);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(statisticaldimension)){
                if(isJSONObject(statisticaldimension)){
                    JSONObject jsonObjectResult = JSONObject.parseObject(statisticaldimension);
                    if(!jsonObjectResult.isEmpty()){
                        StringBuffer stringBuffer= new StringBuffer();
                        if(!((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" or t1.class in"+ jsonObjectResult.get("type"));
                        }
                        if(!((JSONArray)jsonObjectResult.get("mx")).isEmpty()){
                            stringBuffer.append(" or t1.subclass in"+ jsonObjectResult.get("mx"));
                        }
                        if(((JSONArray)jsonObjectResult.get("mx")).isEmpty() && ((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" 1=1 ");
                        }
                        where1= where1+ " and ("+(stringBuffer.toString()).replaceFirst("or", "")+")";
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t1.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }

        if(!saleareas.isEmpty() && saleareas.size()==1){
            for (Row row :actualRows) {
                if(row.getLong("parentid")==0){
                    row.put("y1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1a"));
                    row.put("s1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1a"));
                    row.put("s2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2a"));
                    row.put("s3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3a"));
                    row.put("s4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4a"));
                    row.put("m1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1a"));
                    row.put("m2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2a"));
                    row.put("m3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3a"));
                    row.put("m4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4a"));
                    row.put("m5a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5a"));
                    row.put("m6a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6a"));
                    row.put("m7a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7a"));
                    row.put("m8a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8a"));
                    row.put("m9a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9a"));
                    row.put("m10a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10a"));
                    row.put("m11a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11a"));
                    row.put("m12a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12a"));
                }
            }
        }
        Rows rowsResult =new Rows();
        for (Row row : rows) {
            if(hrrows.size()!=0){
                if(hrrowsMap.containsKey(row.getString("sa_saleareaid"))){
                    if(row.getString("type").equals("区域")){
                        rowsResult.add(row);
                    }

                }
            }else{
                if(row.getString("type").equals("区域")){
                    rowsResult.add(row);
                }
            }


        }

        for (Row row : rowsResult) {
            //初始化
            row = addActualRow(row);

            Long sa_saleareaid1 = row.getLong("sa_saleareaid");
            Row actualRow = new Row();
            for (Row tempActualRow : actualRows) {
                if (tempActualRow.getLong("sa_saleareaid") == sa_saleareaid1) {
                    actualRow.putAll(tempActualRow);
                }
            }

            row.putAll(actualRow);
            row.putAll(calculate(row, actualRow));
        }


        Row rowResult = new Row();
        rowResult.put("y1l",sum1("y1l",rowsResult));
        rowResult.put("y1a",sum1("y1a",rowsResult));
        for (int i = 1; i < 5; i++) {
            rowResult.put("s" + i + "l",sum1("s" + i + "l",rowsResult));
            rowResult.put("s" + i + "a",sum1("s" + i + "a",rowsResult));
        }
        for (int i = 1; i < 13; i++) {
            rowResult.put("m" + i + "l",sum1("m" + i + "l",rowsResult));
            rowResult.put("m" + i + "a",sum1("m" + i + "a",rowsResult));
        }
        rowsResult = new Rows();
        rowsResult.add(rowResult);
        return getSucReturnObject().setData(rowsResult).toString();
    }

    @API(title = "人员目标统计列表(区域经理)", apiversion = R.ID20231018103303.v1.class)
    @CACHEING
    public String queryList_areamanagerList() throws YosException {
        //年份
        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);

        Rows billRows = dbConnect.runSqlQuery("SELECT sa_salestargetbillid,assessmentindicators,statisticaldimension,sa_accountclassids FROM sa_salestargetbill WHERE  year="+year+"  AND siteid ='" + siteid + "' and  targettype = '人员目标'");

        SQLFactory hrsqlFactory = new SQLFactory(this, "业务员列表查询");
        hrsqlFactory.addParameter("siteid", siteid);
        hrsqlFactory.addParameter("hrid", hrid);
        Rows hrrows = dbConnect.runSqlQuery(hrsqlFactory.getSQL());

        SQLFactory areasqlFactory = new SQLFactory(this, "下级区域获取");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter_in("sa_saleareaid", hrrows.toArrayList("sa_saleareaid",new ArrayList<>()));
        Rows arearows = dbConnect.runSqlQuery(areasqlFactory.getSQL());
        ArrayList<Long> areaList = arearows.toArrayList("sa_saleareaid",new ArrayList<Long>());
        areaList.add(0l);


        //考核指标类型
        long sa_salestargetbillid = 0;
        String assessmentindicators="";
        //统计维度
        String statisticaldimension="";
        //账户
        String sa_accountclassids="";
        if (billRows.isNotEmpty()) {
            sa_salestargetbillid=billRows.get(0).getLong("sa_salestargetbillid");
            assessmentindicators=billRows.get(0).getString("assessmentindicators");
            statisticaldimension=billRows.get(0).getString("statisticaldimension");
            sa_accountclassids=billRows.get(0).getString("sa_accountclassids");
        }

        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 and t.sa_saleareaid in "+areaList.toString().replace("[", "(").replace("]", ")"));

        SQLFactory sqlFactory = new SQLFactory(this, "人员-目标详情列表");
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        Rows saleareas = dbConnect.runSqlQuery("select * from sa_salearea where ifnull(parentid,0)=0 and siteid='"+siteid+"'");
        Long sa_saleareaidtop=0l;
        String areanametop="";
        if(!saleareas.isEmpty() && saleareas.size()==1){
            sa_saleareaidtop=saleareas.get(0).getLong("sa_saleareaid");
            areanametop=saleareas.get(0).getString("areaname");
            for (Row row :rows) {
                if(row.getLong("parentid")==0){
                    row.put("y1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1l"));
                    row.put("y1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1h"));
                    row.put("s1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1l"));
                    row.put("s1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1h"));
                    row.put("s2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2l"));
                    row.put("s2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2h"));
                    row.put("s3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3l"));
                    row.put("s3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3h"));
                    row.put("s4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4l"));
                    row.put("s4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4h"));
                    row.put("m1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1l"));
                    row.put("m1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1h"));
                    row.put("m2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2l"));
                    row.put("m2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2h"));
                    row.put("m3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3l"));
                    row.put("m3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3h"));
                    row.put("m4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4l"));
                    row.put("m4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4h"));
                    row.put("m5l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5l"));
                    row.put("m5h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5h"));
                    row.put("m6l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6l"));
                    row.put("m6h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6h"));
                    row.put("m7l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7l"));
                    row.put("m7h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7h"));
                    row.put("m8l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8l"));
                    row.put("m8h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8h"));
                    row.put("m9l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9l"));
                    row.put("m9h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9h"));
                    row.put("m10l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10l"));
                    row.put("m10h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10h"));
                    row.put("m11l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11l"));
                    row.put("m11h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11h"));
                    row.put("m12l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12l"));
                    row.put("m12h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12h"));
                }
            }
        }




        Rows actualRows = new Rows();
        //开票
        if (assessmentindicators .equals("开票")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-开票");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);

        }
        //订单
        if (assessmentindicators .equals("订单")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-订单");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //出货
        if (assessmentindicators .equals("出货")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-出货");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //收款
        if (assessmentindicators .equals("收款")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-收款");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_saleareaid", sa_saleareaidtop);
            sqlFactory.addParameter("areaname", areanametop);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(statisticaldimension)){
                if(isJSONObject(statisticaldimension)){
                    JSONObject jsonObjectResult = JSONObject.parseObject(statisticaldimension);
                    if(!jsonObjectResult.isEmpty()){
                        StringBuffer stringBuffer= new StringBuffer();
                        if(!((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" or t1.class in"+ jsonObjectResult.get("type"));
                        }
                        if(!((JSONArray)jsonObjectResult.get("mx")).isEmpty()){
                            stringBuffer.append(" or t1.subclass in"+ jsonObjectResult.get("mx"));
                        }
                        if(((JSONArray)jsonObjectResult.get("mx")).isEmpty() && ((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" 1=1 ");
                        }
                        where1= where1+ " and ("+(stringBuffer.toString()).replaceFirst("or", "")+")";
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }

            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t1.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }

        if(!saleareas.isEmpty() && saleareas.size()==1){
            for (Row row :actualRows) {
                if(row.getLong("parentid")==0){
                    row.put("y1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1a"));
                    row.put("s1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1a"));
                    row.put("s2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2a"));
                    row.put("s3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3a"));
                    row.put("s4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4a"));
                    row.put("m1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1a"));
                    row.put("m2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2a"));
                    row.put("m3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3a"));
                    row.put("m4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4a"));
                    row.put("m5a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5a"));
                    row.put("m6a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6a"));
                    row.put("m7a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7a"));
                    row.put("m8a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8a"));
                    row.put("m9a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9a"));
                    row.put("m10a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10a"));
                    row.put("m11a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11a"));
                    row.put("m12a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12a"));
                }
            }
        }

        for (Row row : rows) {
            //初始化
            row = addActualRow(row);

            Long sa_saleareaid1 = row.getLong("sa_saleareaid");
            Row actualRow = new Row();
            for (Row tempActualRow : actualRows) {
                if (tempActualRow.getLong("sa_saleareaid") == sa_saleareaid1) {
                    actualRow.putAll(tempActualRow);
                }
            }

            row.putAll(actualRow);
            row.putAll(calculate(row, actualRow));
        }

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

    @API(title = "人员目标统计", apiversion = R.ID20220904134102.v1.class)
    @CACHEING
    public String queryList() throws YosException {
        //年份
        Long sa_salestargetbillid = content.getLong("sa_salestargetbillid");
        Rows billRows = dbConnect.runSqlQuery("SELECT assessmentindicators,year,statisticaldimension,sa_accountclassids FROM sa_salestargetbill WHERE sa_salestargetbillid=" + sa_salestargetbillid + "  AND siteid ='" + siteid + "'");

        //考核指标类型
        String assessmentindicators = "";
        //统计维度
        String statisticaldimension="";
        //账户
        String sa_accountclassids="";
        long year = 2023;
        if (billRows.isNotEmpty()) {
            assessmentindicators=billRows.get(0).getString("assessmentindicators");
            statisticaldimension=billRows.get(0).getString("statisticaldimension");
            sa_accountclassids=billRows.get(0).getString("sa_accountclassids");
            year=billRows.get(0).getLong("year");
        }

        /*
          过滤条件设置
         */
        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("t.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.depfullname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.depname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t.position like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "人员-目标详情列表");
        sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        Rows saleareas = dbConnect.runSqlQuery("select * from sa_salearea where ifnull(parentid,0)=0 and siteid='"+siteid+"'");
        Long sa_saleareaidtop=0l;
        String areanametop="";
        if(!saleareas.isEmpty() && saleareas.size()==1){
            sa_saleareaidtop=saleareas.get(0).getLong("sa_saleareaid");
            areanametop=saleareas.get(0).getString("areaname");
            for (Row row :rows) {
                if(row.getLong("parentid")==0){
                    row.put("y1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1l"));
                    row.put("y1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1h"));
                    row.put("s1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1l"));
                    row.put("s1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1h"));
                    row.put("s2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2l"));
                    row.put("s2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2h"));
                    row.put("s3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3l"));
                    row.put("s3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3h"));
                    row.put("s4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4l"));
                    row.put("s4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4h"));
                    row.put("m1l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1l"));
                    row.put("m1h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1h"));
                    row.put("m2l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2l"));
                    row.put("m2h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2h"));
                    row.put("m3l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3l"));
                    row.put("m3h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3h"));
                    row.put("m4l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4l"));
                    row.put("m4h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4h"));
                    row.put("m5l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5l"));
                    row.put("m5h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5h"));
                    row.put("m6l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6l"));
                    row.put("m6h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6h"));
                    row.put("m7l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7l"));
                    row.put("m7h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7h"));
                    row.put("m8l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8l"));
                    row.put("m8h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8h"));
                    row.put("m9l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9l"));
                    row.put("m9h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9h"));
                    row.put("m10l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10l"));
                    row.put("m10h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10h"));
                    row.put("m11l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11l"));
                    row.put("m11h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11h"));
                    row.put("m12l",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12l"));
                    row.put("m12h",rows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12h"));
                }
            }
        }




        Rows actualRows = new Rows();
        //开票
        if (assessmentindicators .equals("开票")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-开票");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);

        }
        //订单
        if (assessmentindicators .equals("订单")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-订单");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(statisticaldimension.equals("订单审核")){
                where1=where1+" and t2.status='审核'";
            }else{
                where1=where1+" and t2.status='提交'";
            }
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //出货
        if (assessmentindicators .equals("出货")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-出货");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t2.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }
                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }
        //收款
        if (assessmentindicators .equals("收款")) {
            String where1 =" 1=1 ";
            sqlFactory = new SQLFactory(this, "人员目标统计-收款");
            if(content.getLongValue("num")==0){
                sqlFactory.addParameter("num",2);
            }else{
                sqlFactory.addParameter("num",content.getLongValue("num"));
            }
            sqlFactory.addParameter("sa_saleareaid", sa_saleareaidtop);
            sqlFactory.addParameter("areaname", areanametop);
            sqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
            sqlFactory.addParameter_SQL("where", where);
            if(StringUtils.isNotBlank(statisticaldimension)){
                if(isJSONObject(statisticaldimension)){
                    JSONObject jsonObjectResult = JSONObject.parseObject(statisticaldimension);
                    if(!jsonObjectResult.isEmpty()){
                        StringBuffer stringBuffer= new StringBuffer();
                        if(!((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" or t1.class in"+ jsonObjectResult.get("type"));
                        }
                        if(!((JSONArray)jsonObjectResult.get("mx")).isEmpty()){
                            stringBuffer.append(" or t1.subclass in"+ jsonObjectResult.get("mx"));
                        }
                        if(((JSONArray)jsonObjectResult.get("mx")).isEmpty() && ((JSONArray)jsonObjectResult.get("type")).isEmpty()){
                            stringBuffer.append(" 1=1 ");
                        }
                        where1= where1+ " and ("+(stringBuffer.toString()).replaceFirst("or", "")+")";
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }

            if(StringUtils.isNotBlank(sa_accountclassids)){
                if(isJSONArray(sa_accountclassids)){
                    JSONArray jsonArrayResult = JSONArray.parseArray(sa_accountclassids);
                    if(!jsonArrayResult.isEmpty()){
                        where1=where1+ " and t1.sa_accountclassid in"+jsonArrayResult;
                        where1 = where1.replace("[", "(").replace("]", ")");
                    }

                }
            }
            sqlFactory.addParameter_SQL("where1",where1);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("year", year);
            actualRows = dbConnect.runSqlQuery(sqlFactory);
        }

        if(!saleareas.isEmpty() && saleareas.size()==1){
            for (Row row :actualRows) {
                if(row.getLong("parentid")==0){
                    row.put("y1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("y1a"));
                    row.put("s1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s1a"));
                    row.put("s2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s2a"));
                    row.put("s3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s3a"));
                    row.put("s4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("s4a"));
                    row.put("m1a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m1a"));
                    row.put("m2a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m2a"));
                    row.put("m3a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m3a"));
                    row.put("m4a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m4a"));
                    row.put("m5a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m5a"));
                    row.put("m6a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m6a"));
                    row.put("m7a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m7a"));
                    row.put("m8a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m8a"));
                    row.put("m9a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m9a"));
                    row.put("m10a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m10a"));
                    row.put("m11a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m11a"));
                    row.put("m12a",actualRows.toRowsMap("parentid").get(sa_saleareaidtop.toString()).sum("m12a"));
                }
            }
        }



        for (Row row : rows) {
            //初始化
            row = addActualRow(row);

            Long sa_saleareaid = row.getLong("sa_saleareaid");
            Row actualRow = new Row();
            for (Row tempActualRow : actualRows) {
                if (tempActualRow.getLong("sa_saleareaid") == sa_saleareaid) {
                    actualRow.putAll(tempActualRow);
                }
            }

            row.putAll(actualRow);
            row.putAll(calculate(row, actualRow));
        }

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


    //添加实际数据
    public Row addActualRow(Row targetRow) {
        String key = "y1a";
        targetRow.put(key, 0);
        for (int i = 1; i < 5; i++) {
            key = "s" + i + "a";
            targetRow.put(key, 0);
        }
        for (int i = 1; i < 13; i++) {
            key = "m" + i + "a";
            targetRow.put(key, 0);
        }

        return targetRow;
    }

    public Row calculate(Row targetRow, Row actualRow) {

        Row row = new Row();
        String key_l = "y1l";
        String key_h = "y1h";
        String key_a = "y1a";
        String key_p_l = "y1pl";
        String key_p_h = "y1ph";
        if (targetRow.getDouble(key_l) == 0) {
            row.put(key_p_l, 0);
        } else {
            row.put(key_p_l, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_l)));
        }
        if (targetRow.getDouble(key_h) == 0) {
            row.put(key_p_h, 0);
        } else {
            row.put(key_p_h, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_h)));
        }

        for (int i = 1; i < 5; i++) {
            key_l = "s" + i + "l";
            key_h = "s" + i + "h";
            key_a = "s" + i + "a";
            key_p_l = "s" + i + "pl";
            key_p_h = "s" + i + "ph";
            if (targetRow.getDouble(key_l) == 0) {
                row.put(key_p_l, 0);
            } else {
                row.put(key_p_l, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_l)));
            }
            if (targetRow.getDouble(key_h) == 0) {
                row.put(key_p_h, 0);
            } else {
                row.put(key_p_h, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_h)));
            }
        }
        for (int i = 1; i < 13; i++) {
            key_l = "m" + i + "l";
            key_h = "m" + i + "h";
            key_a = "m" + i + "a";
            key_p_l = "m" + i + "pl";
            key_p_h = "m" + i + "ph";
            if (targetRow.getDouble(key_l) == 0) {
                row.put(key_p_l, 0);
            } else {
                row.put(key_p_l, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_l)));
            }
            if (targetRow.getDouble(key_h) == 0) {
                row.put(key_p_h, 0);
            } else {
                row.put(key_p_h, getPercent(actualRow.getDouble(key_a), targetRow.getDouble(key_h)));
            }
        }

        return row;
    }

    public double getPercent(Double a, Double b) {
        double c = a * 100 / b;
        BigDecimal bigDecimal = new BigDecimal(c);
        return bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
    }
    public BigDecimal sum1(String column,Rows rows) {
        BigDecimal value = new BigDecimal("0");
        for (Row row : rows) {
            if (row.containsKey(column)) {
                value = value.add(new BigDecimal(row.getString(column)));
            }
        }
        return value;
    }

}