CUCU2/src/custom/restcontroller/webmanage/sale/salestarget_cucu/performancetargetboard.java

package restcontroller.webmanage.sale.salestarget_cucu;

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

import javax.sql.RowSetReader;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.ConcurrentModificationException;
import java.util.List;

import static beans.salearea.SaleArea.getSubSaleAreaIds;
import static beans.salearea.SaleArea.getSubSaleAreas;

@API(title = "业绩目标看板")
public class performancetargetboard extends Controller {

    /**
     * 构造函数
     *
     * @param content
     */
    public performancetargetboard(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "获取当前业务员区域", apiversion = R.ID2025103010165903.v1.class)
    public String querycurrentAreaList() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr","name","hrid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "获取当前业务员区域及其下属区域", apiversion = R.ID2025103009445603.v1.class)
    public String querySalerAreaList() throws YosException {

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr","name","hrid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows rows = querySQL.query();
        Rows arearows=new Rows();

        ArrayList<Long> list = new ArrayList<>();

        for (Row row : rows) {
            list.add(row.getLong("sa_saleareaid"));
        }
        arearows =getSubSaleAreas(this,list);
        rows.addAll(arearows);
//        if(rows.isNotEmpty()){
//             arearows =getSubSaleAreas(this,list);
//             Rows saleareahrrows  =dbConnect.runSqlQuery("select t1.name,t2.sa_saleareaid,t1.hrid from sys_hr t1 inner join sa_salearea_hr t2 on t1.siteid = t2.siteid and t1.hrid = t2.hrid where t1.siteid='"+siteid+"'");
//             RowsMap saleareahrRowsMap =saleareahrrows.toRowsMap("sa_saleareaid");
//             for (Row row :arearows){
//                 if(saleareahrRowsMap.containsKey(row.getString("sa_saleareaid"))){
//                     for(Row row1 :saleareahrRowsMap.get(row.getString("sa_saleareaid"))){
//                         row.put("name",row1.getString("name"));
//                         row.put("hrid",row1.getString("hrid"));
//                         rows.add(row);
//                     }
//                 }
//             }
//        }

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

    }

    @API(title = "查询业务员指标", apiversion = R.ID2025103010192003.v1.class)
    public String queryperformancetarget() throws YosException {
        //long hrid= content.getLong("hrid");
        //long sa_saleareaid= content.getLong("sa_saleareaid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr","name","hrid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows arearows = querySQL.query();
        Rows arearows1=new Rows();

        ArrayList<Long> saleareaidlist = new ArrayList<>();
        for (Row row : arearows) {
            saleareaidlist.add(row.getLong("sa_saleareaid"));
        }
        arearows1 =getSubSaleAreas(this,saleareaidlist);
        arearows.addAll(arearows1);


        long year= content.getLong("year");
        long month= content.getLong("month");
        JSONArray months = content.getJSONArray("months");
        String monthstr = String.format("%02d", month);

        BigDecimal gcproportion =BigDecimal.ZERO;
        BigDecimal zzproportion =BigDecimal.ZERO;

        Rows salestargetbillrows =dbConnect.runSqlQuery("select * from sa_salestargetbill where siteid='"+siteid+"' and year="+year+" and targettype='人员目标'");
        Rows rows = dbConnect.runSqlQuery("select * from sys_site_parameter where siteid='" + siteid + "'");
        if(rows.isNotEmpty()){
            gcproportion=rows.get(0).getBigDecimal("gcproportion").divide(BigDecimal.valueOf(100));
            zzproportion=rows.get(0).getBigDecimal("zzproportion").divide(BigDecimal.valueOf(100));
        }

        long sa_salestargetbillid=0;
        if(salestargetbillrows.isNotEmpty()){
            sa_salestargetbillid=salestargetbillrows.get(0).getLong("sa_salestargetbillid");
        }
        SQLFactory personnelsqlFactory = new SQLFactory(this, "人员-目标详情列表");
        personnelsqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
        personnelsqlFactory.addParameter_SQL("where", " 1=1 ");
        personnelsqlFactory.addParameter("siteid", siteid);
        Rows salestargetrows = dbConnect.runSqlQuery(personnelsqlFactory.getSQL());
        RowsMap salestargetrowsMap = salestargetrows.toRowsMap("sa_saleareaid");


        SQLFactory paymentsqlFactory = new SQLFactory(this, "账户回款统计");
        paymentsqlFactory.addParameter("year", year);
        paymentsqlFactory.addParameter_SQL("where"," 1=1 ");
        paymentsqlFactory.addParameter("siteid", siteid);
        Rows paymentRows_All = dbConnect.runSqlQuery(paymentsqlFactory.getSQL());
        //RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");

        SQLFactory rebatesqlFactory = new SQLFactory(this, "账户返利统计");
        rebatesqlFactory.addParameter("year", year);
        rebatesqlFactory.addParameter_SQL("where"," 1=1 ");
        rebatesqlFactory.addParameter("siteid", siteid);
        Rows rebateRows_All = dbConnect.runSqlQuery(rebatesqlFactory.getSQL());
        //RowsMap rebateRowsMap =rebateRows.toRowsMap("month");

        for(Row row :arearows){
            BigDecimal rw_month=BigDecimal.ZERO;
            if(salestargetrowsMap.containsKey(row.getString("sa_saleareaid"))){
                //月度任务（万）
                row.put("rw_month",salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+month+"l"));
                rw_month=salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+month+"l");
            }else{
                row.put("rw_month",BigDecimal.ZERO);
            }
            ArrayList<Long> list = new ArrayList<>();
            list.add(row.getLong("sa_saleareaid"));
            list.addAll(getSubSaleAreaIds(this,list));
            Rows paymentRows =new Rows();
            Rows rebateRows =new Rows();
            for(Row paymentRow :paymentRows_All){
                if(list.contains(paymentRow.getLong("sa_saleareaid"))){
                    paymentRows.add(paymentRow);
                }
            }
            RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");
            for(Row rebateRow :rebateRows_All){
                if(list.contains(rebateRow.getLong("sa_saleareaid"))){
                    rebateRows.add(rebateRow);
                }
            }
            RowsMap rebateRowsMap =rebateRows.toRowsMap("month");

            BigDecimal xainjin=paymentRowsMap.containsKey("现金账户")?paymentRowsMap.get("现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
            BigDecimal huodong=paymentRowsMap.containsKey("活动预存账户")?paymentRowsMap.get("活动预存账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("活动预存账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
            BigDecimal fanli=rebateRowsMap.containsKey(monthstr)?rebateRowsMap.get(monthstr).sum("amount"):BigDecimal.ZERO;

            //回款完成额
            row.put("completedamount",xainjin.add(huodong).add(fanli));

            BigDecimal previousexceed=BigDecimal.ZERO;
            BigDecimal completedamountexceed100=BigDecimal.ZERO;
            for(int i=1;i<=month;i++){
                previousexceed=completedamountexceed100;
                BigDecimal looprw_month=salestargetrowsMap.get(row.getString("sa_saleareaid")).isNotEmpty()?salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+i+"l"):BigDecimal.ZERO;
                BigDecimal loopxainjin=paymentRowsMap.containsKey("现金账户")?paymentRowsMap.get("现金账户").toRowsMap("month").containsKey(String.format("%02d", i))?paymentRowsMap.get("现金账户").toRowsMap("month").get(String.format("%02d", i)).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal loophuodong=paymentRowsMap.containsKey("活动预存账户")?paymentRowsMap.get("活动预存账户").toRowsMap("month").containsKey(String.format("%02d", i))?paymentRowsMap.get("活动预存账户").toRowsMap("month").get(String.format("%02d", i)).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal loopfanli=rebateRowsMap.containsKey(String.format("%02d", i))?rebateRowsMap.get(String.format("%02d", i)).sum("amount"):BigDecimal.ZERO;

                BigDecimal loopgcgc=paymentRowsMap.containsKey("GC工程现金账户")?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").containsKey(String.format("%02d", i))?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").get(String.format("%02d", i)).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal loopzzgc=paymentRowsMap.containsKey("整装工程现金账户")?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").containsKey(String.format("%02d", i))?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").get(String.format("%02d", i)).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;

                completedamountexceed100=loopxainjin.add(loophuodong).add(loopfanli).add(previousexceed)
                        .add(loopgcgc.min(looprw_month.multiply(gcproportion).multiply(BigDecimal.valueOf(10000))))
                        .add(loopzzgc.min(looprw_month.multiply(zzproportion).multiply(BigDecimal.valueOf(10000))))
                        .subtract(looprw_month.multiply(BigDecimal.valueOf(10000)));

            }


            //上月完成额超出部分
            row.put("previousexceed",previousexceed.compareTo(BigDecimal.ZERO)>0?previousexceed:0);

            BigDecimal gcgc=paymentRowsMap.containsKey("GC工程现金账户")?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
            //GC工程完成额
            row.put("gccompletedamount",gcgc.min(rw_month.multiply(gcproportion).multiply(BigDecimal.valueOf(10000))));
            //GC工程超出部分
            row.put("gccompletedamountexceed",gcgc.subtract(rw_month.multiply(gcproportion).multiply(BigDecimal.valueOf(10000))).compareTo(BigDecimal.ZERO)>0?gcgc.subtract(rw_month.multiply(gcproportion).multiply(BigDecimal.valueOf(10000))):0);

            BigDecimal zzgc=paymentRowsMap.containsKey("整装工程现金账户")?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
            //整装工程完成额
            row.put("zzcompletedamount",zzgc.min(rw_month.multiply(zzproportion).multiply(BigDecimal.valueOf(10000))));
            //整装工程超出部分
            row.put("zzcompletedamountexceed",zzgc.subtract(rw_month.multiply(zzproportion).multiply(BigDecimal.valueOf(10000))).compareTo(BigDecimal.ZERO)>0?zzgc.subtract(rw_month.multiply(zzproportion).multiply(BigDecimal.valueOf(10000))):0);

            //完成额超出100%部分
            row.put("completedamountexceed100",completedamountexceed100.compareTo(BigDecimal.ZERO)>0?completedamountexceed100:0);
            //实际完成额
            row.put("actualcompletedamount",row.getBigDecimal("completedamount").add(row.getBigDecimal("gccompletedamount")).add(row.getBigDecimal("zzcompletedamount")));
            //实际完成百分比
            if(rw_month.compareTo(BigDecimal.ZERO)==0){
                row.put("actualcompletedpercentage",0);
            }else{
                row.put("actualcompletedpercentage", (row.getBigDecimal("completedamount").add(row.getBigDecimal("previousexceed")).add(row.getBigDecimal("gccompletedamount")).add(row.getBigDecimal("zzcompletedamount"))).divide((rw_month.multiply(BigDecimal.valueOf(10000))),4, RoundingMode.HALF_UP));
            }
        }
        Rows arearows_quarter= queryperformancetarget_quarter(month,arearows,salestargetrowsMap,paymentRows_All,rebateRows_All);
        System.err.println(arearows_quarter.get(0).getString("sa_saleareaid")+":"+arearows_quarter.get(0).getBigDecimal("completedamount"));
        RowsMap arearowsMap_quarter=arearows_quarter.toRowsMap("sa_saleareaid");
        Rows arearows_total= queryperformancetarget_total(month,months,arearows,salestargetrowsMap,paymentRows_All,rebateRows_All);
        RowsMap arearowsMap_total=arearows_total.toRowsMap("sa_saleareaid");
        for(Row row : arearows){
            if(arearowsMap_quarter.containsKey(row.getString("sa_saleareaid"))){
                row.put("rw_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("rw_quarter"));
                row.put("actualcompletedamount_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("actualcompletedamount_quarter"));
                row.put("zzcompletedamount_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("zzcompletedamount_quarter"));
                row.put("gccompletedamount_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("gccompletedamount_quarter"));
                row.put("actualcompletedpercentage_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("actualcompletedpercentage_quarter"));
                System.err.println(row.getString("sa_saleareaid")+":"+arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("completedamount_quarter"));
                row.put("completedamount_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("completedamount_quarter"));
                row.put("previousexceed_quarter",arearowsMap_quarter.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("previousexceed_quarter"));
            }else{
                row.put("rw_quarter",0);
                row.put("actualcompletedamount_quarter",0);
                row.put("zzcompletedamount_quarter",0);
                row.put("gccompletedamount_quarter",0);
                row.put("actualcompletedpercentage_quarter",0);
                row.put("completedamount_quarter",0);
                row.put("previousexceed_quarter",0);
            }
            if(arearowsMap_total.containsKey(row.getString("sa_saleareaid"))){
                row.put("rw_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("rw_total"));
                row.put("actualcompletedamount_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("actualcompletedamount_total"));
                row.put("zzcompletedamount_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("zzcompletedamount_total"));
                row.put("gccompletedamount_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("gccompletedamount_total"));
                row.put("actualcompletedpercentage_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("actualcompletedpercentage_total"));
                row.put("differenceamount_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("differenceamount_total"));
                row.put("completedamount_total",arearowsMap_total.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("completedamount_total"));
            }else{
                row.put("rw_total",0);
                row.put("actualcompletedamount_total",0);
                row.put("zzcompletedamount_total",0);
                row.put("gccompletedamount_total",0);
                row.put("actualcompletedpercentage_total",0);
                row.put("differenceamount_total",0);
                row.put("completedamount_total",0);
            }
        }

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

    /**
     * 查询业务员季度指标
     * @param selectmonth
     * @param arearows
     * @param salestargetrowsMap
     * @param paymentRows_All
     * @param rebateRows_All
     * @return
     * @throws YosException
     */
    public Rows queryperformancetarget_quarter(long selectmonth,Rows arearows,RowsMap salestargetrowsMap,Rows paymentRows_All,Rows rebateRows_All) throws YosException {
//        long hrid= content.getLong("hrid");
        //long sa_saleareaid= content.getLong("sa_saleareaid");

//        Rows salestargetbillrows =dbConnect.runSqlQuery("select * from sa_salestargetbill where siteid='"+siteid+"' and year="+year+" and targettype='人员目标'");
//        long sa_salestargetbillid=0;
//        if(salestargetbillrows.isNotEmpty()){
//            sa_salestargetbillid=salestargetbillrows.get(0).getLong("sa_salestargetbillid");
//        }
//        SQLFactory personnelsqlFactory = new SQLFactory(this, "人员-目标详情列表");
//        personnelsqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
//        personnelsqlFactory.addParameter_SQL("where", " 1=1 ");
//        personnelsqlFactory.addParameter("siteid", siteid);
//        Rows salestargetrows = dbConnect.runSqlQuery(personnelsqlFactory.getSQL());
//        RowsMap salestargetrowsMap = salestargetrows.toRowsMap("sa_saleareaid");


        List<Integer> months = getCurrentQuarterMonths((int)selectmonth);
        List<Integer> previousmonths =getPreviousQuarterMonthsCalendar((int)selectmonth);


//        SQLFactory paymentsqlFactory = new SQLFactory(this, "账户回款统计");
//        paymentsqlFactory.addParameter("year", year);
//        paymentsqlFactory.addParameter_SQL("where"," 1=1 ");
//        paymentsqlFactory.addParameter("siteid", siteid);
//        Rows paymentRows_All = dbConnect.runSqlQuery(paymentsqlFactory.getSQL());
//        RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");

//        SQLFactory rebatesqlFactory = new SQLFactory(this, "账户返利统计");
//        rebatesqlFactory.addParameter("year", year);
//        rebatesqlFactory.addParameter_SQL("where"," 1=1 ");
//        rebatesqlFactory.addParameter("siteid", siteid);
//        Rows rebateRows_All = dbConnect.runSqlQuery(rebatesqlFactory.getSQL());
//        RowsMap rebateRowsMap =rebateRows.toRowsMap("month");

        Rows arearowsResult =new Rows();
        for(Row row :arearows){
            Row arearowResult =new Row();
            arearowResult.put("sa_saleareaid",row.getLong("sa_saleareaid"));
            BigDecimal rw_quarter=BigDecimal.ZERO;
            BigDecimal rw_previousquarter=BigDecimal.ZERO;
            BigDecimal completedamount=BigDecimal.ZERO;
            BigDecimal gcgccompletedamount=BigDecimal.ZERO;
            BigDecimal zzgccompletedamount=BigDecimal.ZERO;
            if(salestargetrowsMap.containsKey(row.getString("sa_saleareaid"))){
                for(int month : months){
                    rw_quarter=rw_quarter.add(salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+month+"l"));
                }
                for(int previousmonth : previousmonths){
                    rw_previousquarter=rw_previousquarter.add(salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+previousmonth+"l"));
                }
            }
            //季度任务（万）
            arearowResult.put("rw_quarter",rw_quarter);
            ArrayList<Long> list = new ArrayList<>();
            list.add(row.getLong("sa_saleareaid"));
            list.addAll(getSubSaleAreaIds(this,list));

            Rows paymentRows =new Rows();
            Rows rebateRows =new Rows();
            for(Row paymentRow :paymentRows_All){
                if(list.contains(paymentRow.getLong("sa_saleareaid"))){
                    paymentRows.add(paymentRow);
                }
            }
            RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");
            for(Row rebateRow :rebateRows_All){
                if(list.contains(rebateRow.getLong("sa_saleareaid"))){
                    rebateRows.add(rebateRow);
                }
            }
            RowsMap rebateRowsMap =rebateRows.toRowsMap("month");

            for(int month : months){
                String monthstr = String.format("%02d", month);
                BigDecimal xainjin=paymentRowsMap.containsKey("现金账户")?paymentRowsMap.get("现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal huodong=paymentRowsMap.containsKey("活动预存账户")?paymentRowsMap.get("活动预存账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("活动预存账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal fanli=rebateRowsMap.containsKey(monthstr)?rebateRowsMap.get(monthstr).sum("amount"):BigDecimal.ZERO;
                BigDecimal gcgc=paymentRowsMap.containsKey("GC工程现金账户")?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal zzgc=paymentRowsMap.containsKey("整装工程现金账户")?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                gcgccompletedamount=gcgccompletedamount.add(gcgc);
                zzgccompletedamount=zzgccompletedamount.add(zzgc);
                completedamount=completedamount.add(xainjin).add(huodong).add(fanli);
            }


            BigDecimal previousexceed=BigDecimal.ZERO;
            BigDecimal completedamountexceed100=BigDecimal.ZERO;
            int quarter = getQuarterByMonth((int)selectmonth);
            for(int i=1;i<=quarter;i++){
                previousexceed=completedamountexceed100;
                List<Integer> loopmonths = getCurrentQuarterMonths(i*3-2);
                BigDecimal looprw_quarter=BigDecimal.ZERO;
                BigDecimal loopcompletedamount=BigDecimal.ZERO;
                BigDecimal loopgcgccompletedamount=BigDecimal.ZERO;
                BigDecimal loopzzgccompletedamount=BigDecimal.ZERO;
                if(salestargetrowsMap.get(row.getString("sa_saleareaid")).isNotEmpty()){
                    for(int month : loopmonths){
                        looprw_quarter=looprw_quarter.add(salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+month+"l"));
                    }
                }
                for(int month : loopmonths){
                    String monthstr = String.format("%02d", month);
                    BigDecimal loopxainjin=paymentRowsMap.containsKey("现金账户")?paymentRowsMap.get("现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                    BigDecimal loophuodong=paymentRowsMap.containsKey("活动预存账户")?paymentRowsMap.get("活动预存账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("活动预存账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                    BigDecimal loopfanli=rebateRowsMap.containsKey(monthstr)?rebateRowsMap.get(monthstr).sum("amount"):BigDecimal.ZERO;
                    BigDecimal loopgcgc=paymentRowsMap.containsKey("GC工程现金账户")?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                    BigDecimal loopzzgc=paymentRowsMap.containsKey("整装工程现金账户")?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                    loopgcgccompletedamount=loopgcgccompletedamount.add(loopgcgc);
                    loopzzgccompletedamount=loopzzgccompletedamount.add(loopzzgc);
                    loopcompletedamount=loopcompletedamount.add(loopxainjin).add(loophuodong).add(loopfanli);
                }

                completedamountexceed100=loopcompletedamount.add(previousexceed).add(loopgcgccompletedamount)
                        .add(loopzzgccompletedamount).subtract(looprw_quarter.multiply(BigDecimal.valueOf(10000)));

            }

            //回款完成额
            arearowResult.put("completedamount_quarter",completedamount);
            //上季完成额超出部分
            arearowResult.put("previousexceed_quarter",previousexceed.compareTo(BigDecimal.ZERO)>0?previousexceed:0);
            //GC工程完成额
            arearowResult.put("gccompletedamount_quarter",gcgccompletedamount);
            //整装工程完成额
            arearowResult.put("zzcompletedamount_quarter",zzgccompletedamount);
            //季度完成额
            arearowResult.put("actualcompletedamount_quarter",arearowResult.getBigDecimal("completedamount_quarter").add(arearowResult.getBigDecimal("previousexceed_quarter")).add(arearowResult.getBigDecimal("gccompletedamount_quarter")).add(arearowResult.getBigDecimal("zzcompletedamount_quarter")));
            //季度百分比
            if(rw_quarter.compareTo(BigDecimal.ZERO)==0){
                arearowResult.put("actualcompletedpercentage_quarter",0);
            }else{
                arearowResult.put("actualcompletedpercentage_quarter",arearowResult.getBigDecimal("actualcompletedamount_quarter").divide((rw_quarter.multiply(BigDecimal.valueOf(10000))),4, RoundingMode.HALF_UP));
            }
            arearowsResult.add(arearowResult);
        }
        return arearowsResult;

    }

    /**
     * 查询业务员总指标
     * @param selectmonth
     * @param arearows
     * @param salestargetrowsMap
     * @param paymentRows_All
     * @param rebateRows_All
     * @return
     * @throws YosException
     */
    public Rows queryperformancetarget_total(long selectmonth,JSONArray months,Rows arearows,RowsMap salestargetrowsMap,Rows paymentRows_All,Rows rebateRows_All) throws YosException {

        BigDecimal gcproportion =BigDecimal.ZERO.divide(BigDecimal.valueOf(100));
        BigDecimal zzproportion =BigDecimal.ZERO.divide(BigDecimal.valueOf(100));
//        Rows salestargetbillrows =dbConnect.runSqlQuery("select * from sa_salestargetbill where siteid='"+siteid+"' and year="+year+" and targettype='人员目标'");
        Rows rows = dbConnect.runSqlQuery("select * from sys_site_parameter where siteid='" + siteid + "'");
        if(months.isEmpty()){
            if(rows.isNotEmpty()){
                gcproportion=rows.get(0).getBigDecimal("gcproportion");
                zzproportion=rows.get(0).getBigDecimal("zzproportion");
                for(int i=1;i<=selectmonth;i++){
                    if(rows.get(0).getJSONArray("statistics_months").contains(i)){
                        months.add(i);
                    }
                }
            }
        }
//        long sa_salestargetbillid=0;
//        if(salestargetbillrows.isNotEmpty()){
//            sa_salestargetbillid=salestargetbillrows.get(0).getLong("sa_salestargetbillid");
//        }
//        SQLFactory personnelsqlFactory = new SQLFactory(this, "人员-目标详情列表");
//        personnelsqlFactory.addParameter("sa_salestargetbillid", sa_salestargetbillid);
//        personnelsqlFactory.addParameter_SQL("where", " 1=1 ");
//        personnelsqlFactory.addParameter("siteid", siteid);
//        Rows salestargetrows = dbConnect.runSqlQuery(personnelsqlFactory.getSQL());
//        RowsMap salestargetrowsMap = salestargetrows.toRowsMap("sa_saleareaid");


//        SQLFactory paymentsqlFactory = new SQLFactory(this, "账户回款统计");
//        paymentsqlFactory.addParameter("year", year);
//        paymentsqlFactory.addParameter_SQL("where"," 1=1 ");
//        paymentsqlFactory.addParameter("siteid", siteid);
//        Rows paymentRows_All = dbConnect.runSqlQuery(paymentsqlFactory.getSQL());
//        RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");

//        SQLFactory rebatesqlFactory = new SQLFactory(this, "账户返利统计");
//        rebatesqlFactory.addParameter("year", year);
//        rebatesqlFactory.addParameter_SQL("where"," 1=1 ");
//        rebatesqlFactory.addParameter("siteid", siteid);
//        Rows rebateRows_All = dbConnect.runSqlQuery(rebatesqlFactory.getSQL());
//        RowsMap rebateRowsMap =rebateRows.toRowsMap("month");
        Rows arearowsResult =new Rows();
        for(Row row :arearows){
            Row arearowResult =new Row();
            arearowResult.put("sa_saleareaid",row.getLong("sa_saleareaid"));
            BigDecimal rw_all=BigDecimal.ZERO;
            BigDecimal rw_year=BigDecimal.ZERO;
            BigDecimal completedamount=BigDecimal.ZERO;
            BigDecimal gcgccompletedamount=BigDecimal.ZERO;
            BigDecimal zzgccompletedamount=BigDecimal.ZERO;
            if(salestargetrowsMap.get(row.getString("sa_saleareaid")).isNotEmpty()){
                for(Object month : months){
                    rw_all=rw_all.add(salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("m"+ (Integer)month+"l"));
                }
                rw_year=salestargetrowsMap.get(row.getString("sa_saleareaid")).get(0).getBigDecimal("y1l");
            }
            ArrayList<Long> list = new ArrayList<>();
            list.add(row.getLong("sa_saleareaid"));
            list.addAll(getSubSaleAreaIds(this,list));

            Rows paymentRows =new Rows();
            Rows rebateRows =new Rows();
            for(Row paymentRow :paymentRows_All){
                if(list.contains(paymentRow.getLong("sa_saleareaid"))){
                    paymentRows.add(paymentRow);
                }
            }
            RowsMap paymentRowsMap =paymentRows.toRowsMap("accountname");
            for(Row rebateRow :rebateRows_All){
                if(list.contains(rebateRow.getLong("sa_saleareaid"))){
                    rebateRows.add(rebateRow);
                }
            }
            RowsMap rebateRowsMap =rebateRows.toRowsMap("month");

            //总任务（万）
            arearowResult.put("rw_total",rw_year);

            for(Object month : months){
                String monthstr = String.format("%02d", (Integer)month);
                BigDecimal xainjin=paymentRowsMap.containsKey("现金账户")?paymentRowsMap.get("现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal huodong=paymentRowsMap.containsKey("活动预存账户")?paymentRowsMap.get("活动预存账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("活动预存账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal fanli=rebateRowsMap.containsKey(monthstr)?rebateRowsMap.get(monthstr).sum("amount"):BigDecimal.ZERO;
                BigDecimal gcgc=paymentRowsMap.containsKey("GC工程现金账户")?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("GC工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                BigDecimal zzgc=paymentRowsMap.containsKey("整装工程现金账户")?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").containsKey(monthstr)?paymentRowsMap.get("整装工程现金账户").toRowsMap("month").get(monthstr).sum("amount"):BigDecimal.ZERO:BigDecimal.ZERO;
                gcgccompletedamount=gcgccompletedamount.add(gcgc);
                zzgccompletedamount=zzgccompletedamount.add(zzgc);
                completedamount=completedamount.add(xainjin).add(huodong).add(fanli);
            }
            //回款完成额
            arearowResult.put("completedamount_total",completedamount);
            //GC工程完成额
            arearowResult.put("gccompletedamount_total",gcgccompletedamount.min(rw_all.multiply(gcproportion).multiply(BigDecimal.valueOf(10000))));
            //整装工程完成额
            arearowResult.put("zzcompletedamount_total",zzgccompletedamount.min(rw_all.multiply(zzproportion).multiply(BigDecimal.valueOf(10000))));
            //总完成额
            arearowResult.put("actualcompletedamount_total",arearowResult.getBigDecimal("completedamount_total").add(arearowResult.getBigDecimal("gccompletedamount_total")).add(arearowResult.getBigDecimal("zzcompletedamount_total")));
            //百分比
            if(rw_all.compareTo(BigDecimal.ZERO)==0){
                arearowResult.put("actualcompletedpercentage_total",0);
            }else{
                arearowResult.put("actualcompletedpercentage_total",arearowResult.getBigDecimal("actualcompletedamount_total").divide((rw_all.multiply(BigDecimal.valueOf(10000))),4, RoundingMode.HALF_UP));
            }
            //和年度任务的差额
            BigDecimal differenceamount=rw_all.multiply(BigDecimal.valueOf(7000)).subtract(arearowResult.getBigDecimal("actualcompletedamount_total"));
            arearowResult.put("differenceamount_total",differenceamount.compareTo(BigDecimal.ZERO)>0?differenceamount:0);
            arearowsResult.add(arearowResult);
        }

        return arearowsResult;

    }

    @API(title = "大区查询", apiversion = R.ID2025103015481203.v1.class)
    public String queryareaname2() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname","level");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows rows = querySQL.query();
        Rows arearows=new Rows();

        ArrayList<Long> list = new ArrayList<>();

        for (Row row : rows) {
            list.add(row.getLong("sa_saleareaid"));
        }
        arearows =getSubSaleAreas(this,list);
        rows.addAll(arearows);
        Rows arearows2 = new Rows();
        for(Row row :rows){
            if(row.getLong("level")==2){
                arearows2.add(row);
            }

        }


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

    @API(title = "区域查询", apiversion = R.ID2025103015485403.v1.class)
    public String queryareaname3() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname","level");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows rows = querySQL.query();
        Rows arearows=new Rows();

        ArrayList<Long> list = new ArrayList<>();

        for (Row row : rows) {
            list.add(row.getLong("sa_saleareaid"));
        }
        arearows =getSubSaleAreas(this,list);
        rows.addAll(arearows);
        Rows arearows3 = new Rows();
        for(Row row :rows){
            if(row.getLong("level")==3){
                arearows3.add(row);
            }

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

    @API(title = "省份查询", apiversion = R.ID2025103015494803.v1.class)
    public String queryProvince() throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "t_province ", "province");
        querySQL.setTableAlias("t1");
        querySQL.setPage(pageSize, pageNumber);
        pageSorting=" t1.provinceid asc";
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "回款明细", apiversion = R.ID2025103015304303.v1.class)
    public String querypaymentdetail() throws YosException {
        long month =content.getLong("month");
        String monthstr = String.format("%02d", month);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr","name","hrid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea_hr", "t2", "t1.siteid = t2.siteid and t1.hrid = t2.hrid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_salearea", "t3", " t3.siteid = t2.siteid and t3.sa_saleareaid = t2.sa_saleareaid", "sa_saleareaid","areaname");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.hrid", hrid);

        Rows currentarearows = querySQL.query();
        Rows arearows=new Rows();

        ArrayList<Long> list = new ArrayList<>();

        for (Row row : currentarearows) {
            list.add(row.getLong("sa_saleareaid"));
        }
        list.addAll(getSubSaleAreaIds(this,list));
        long year= content.getLong("year");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t2.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("areaname3") && !"".equals(whereObject.getString("areaname3"))) {
                where.append(" and (t4.areaname3 ='").append(whereObject.getString("areaname3")).append("' or t4.areaname2 ='").append(whereObject.getString("areaname3")).append("' )");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "回款明细");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("year", year);
        sqlFactory.addParameter("period", monthstr);
        sqlFactory.addParameter_in("sa_saleareaids", list);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        return getSucReturnObject().setData(rows).toString();
    }


    public List<Integer> getCurrentQuarterMonths(int currentMonth) {
        int quarterStartMonth = ((currentMonth - 1) / 3) * 3 + 1;

        List<Integer> quarterMonths = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            quarterMonths.add(quarterStartMonth + i);
        }

        return quarterMonths;
    }
    public  int getQuarterByMonth(int month) {
        if (month >= 1 && month <= 3) {
            return 1;
        } else if (month >= 4 && month <= 6) {
            return 2;
        } else if (month >= 7 && month <= 9) {
            return 3;
        } else if (month >= 10 && month <= 12) {
            return 4;
        } else {
            throw new IllegalArgumentException("月份必须在 1-12 之间: " + month);
        }
    }

    public List<Integer> getPreviousQuarterMonthsCalendar(int currentMonth) {
        int currentQuarter = (currentMonth - 1) / 3 + 1;
        int lastQuarter = currentQuarter - 1;
        List<Integer> quarterMonths = new ArrayList<>();
        // 处理跨年情况
        if (lastQuarter == 0) {
            return quarterMonths;
        }

        int quarterStartMonth = (lastQuarter - 1) * 3 + 1;


        for (int i = 0; i < 3; i++) {
            quarterMonths.add(quarterStartMonth + i);
        }

        return quarterMonths;
    }

}