lsa_YOS/src/custom/restcontroller/webmanage/sale/databoard/databoard.java

package restcontroller.webmanage.sale.databoard;

import beans.itemclass.ItemClass;
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.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

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

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


    /**
     * 经销商订货分析
     *
     * @return
     */
    @API(title = "经销商订货分析", apiversion = R.ID20230803143703.v1.class)
    @CACHEING
    public String agentOrderAndPaymentAnalysis() throws Exception {

        String datetype = content.getStringValue("datetype");
        String begindate = "";
        String enddate = "";

        //
        String begindate1 = "";
        String enddate1 = "";

        String begindate2 = "";
        String enddate2 = "";


        switch (datetype) {
            case "月":
                begindate = getMonthFirstDay();
                begindate1 = caluteDate(Calendar.MONTH, begindate);
                begindate2 = caluteDate(Calendar.YEAR, begindate);
                enddate = getMonthLastDay();
                enddate1 = caluteDate(Calendar.MONTH, enddate);
                enddate2 = caluteDate(Calendar.YEAR, enddate);

                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                begindate1 = caluteDate(Calendar.YEAR, begindate);
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                enddate1 = caluteDate(Calendar.YEAR, enddate);
                break;
            case "日":
                begindate = sdf.format(new Date().getTime());
                begindate1 = caluteDate(Calendar.DAY_OF_MONTH, begindate);
                enddate = sdf.format(new Date().getTime());
                enddate1 = caluteDate(Calendar.DAY_OF_MONTH, enddate);
                break;
            default:
                break;
        }
        StringBuffer where = new StringBuffer(" 1=1 and t1.sys_enterpriseid=" + sys_enterpriseid);
        StringBuffer whereLast1 = new StringBuffer(" 1=1 and t1.sys_enterpriseid=" + sys_enterpriseid);
        StringBuffer whereLast2 = new StringBuffer(" 1=1 and t1.sys_enterpriseid=" + sys_enterpriseid);

        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(begindate).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(enddate).append("' ");
        }
        if (!begindate1.equals("")) {
            whereLast1.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(begindate1).append("' ");
        }
        if (!enddate1.equals("")) {
            whereLast1.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(enddate1).append("' ");
        }
        if (!begindate2.equals("")) {
            whereLast2.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(begindate2).append("' ");
        }
        if (!enddate2.equals("")) {
            whereLast2.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(enddate2).append("' ");
        }


        JSONArray jsonArray = new JSONArray();
        //订货额
        JSONObject jsonObject = new JSONObject();
        SQLFactory factory = new SQLFactory(this, "销售额统计查询_经销商");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));
        //环比
        SQLFactory factoryLast1 = new SQLFactory(this, "销售额统计查询_经销商");
        factoryLast1.addParameter("siteid", siteid);
        factoryLast1.addParameter_SQL("where", whereLast1);
        Rows rowsLast1 = dbConnect.runSqlQuery(factoryLast1.getSQL(false));
        //同比
        SQLFactory factoryLast2 = new SQLFactory(this, "销售额统计查询_经销商");
        factoryLast2.addParameter("siteid", siteid);
        factoryLast2.addParameter_SQL("where", whereLast2);
        Rows rowsLast2 = dbConnect.runSqlQuery(factoryLast2.getSQL(false));

        BigDecimal typestatistics = rows.get(0).getBigDecimal("typestatistics");
        BigDecimal typestatistics1 = rowsLast1.get(0).getBigDecimal("typestatistics");
        BigDecimal typestatistics2 = rowsLast2.get(0).getBigDecimal("typestatistics");
        BigDecimal huanbirate = BigDecimal.ZERO;
        BigDecimal tongbirate = BigDecimal.ZERO;
        if (typestatistics1.compareTo(BigDecimal.ZERO) > 0) {
            huanbirate = (typestatistics.subtract(typestatistics1)).divide(typestatistics1, 4, BigDecimal.ROUND_HALF_UP);
        }
        if (typestatistics2.compareTo(BigDecimal.ZERO) > 0) {
            tongbirate = (typestatistics.subtract(typestatistics2)).divide(typestatistics2, 4, BigDecimal.ROUND_HALF_UP);
        }

        jsonObject = new JSONObject();
        jsonObject.put("typestatistics", typestatistics);
        jsonObject.put("huanbirate", huanbirate);
        jsonObject.put("tongbirate", tongbirate);
        jsonArray.add(jsonObject);

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


    /**
     * 经销商余额
     *
     * @return
     */
    @API(title = "经销商余额", apiversion = R.ID20230803165003.v1.class)
    @CACHEING
    public String agentBalanceAnalysis() throws Exception {


        //账户余额
        SQLFactory balancefactory = new SQLFactory(this, "账户余额_经销商");
        balancefactory.addParameter("siteid", siteid);
        balancefactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        Rows balanceRows = dbConnect.runSqlQuery(balancefactory.getSQL(false));

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

    /**
     * 订货额类别占比分析
     *
     * @return
     */
    @API(title = "订货分析", apiversion = R.ID20230729103203.v1.class)
    @CACHEING
    public String orderAndPaymentAnalysis() throws Exception {

        String datetype = content.getStringValue("datetype");
        String begindate = "";
        String enddate = "";
        switch (datetype) {
            case "日":
                begindate =getDate_Str();
                enddate = getDate_Str();
                break;
            case "月":
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "季":
                begindate = sdf.format(getQuarterStart(new Date()).getTime());
                enddate = sdf.format(getQuarterEnd(new Date()).getTime());
                break;
            case "上半年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-06-30";
                break;
            case "下半年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-07-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            default:
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }

        StringBuffer where = new StringBuffer(" 1=1 ");
        StringBuffer whereLastYear = new StringBuffer(" 1=1 ");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        if (!begindate.equals("")) {
            where.append(" and t1.checkdate >='").append(begindate).append(" 00:00:00'");
            Date date = sdf.parse(begindate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and t1.checkdate >='").append(sdf.format(cal.getTime())).append(" 00:00:00'");
        }
        if (!enddate.equals("")) {
            where.append(" and t1.checkdate <='").append(enddate).append(" 23:59:59'");
            Date date = sdf.parse(enddate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and t1.checkdate <='").append(sdf.format(cal.getTime())).append(" 23:59:59'");
        }

        JSONArray jsonArray = new JSONArray();
        //订货额
        JSONObject jsonObject = new JSONObject();
        SQLFactory factory = new SQLFactory(this, "销售额统计查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

        SQLFactory factoryLastYear = new SQLFactory(this, "销售额统计查询");
        factoryLastYear.addParameter("siteid", siteid);
        factoryLastYear.addParameter_SQL("where", whereLastYear);
        Rows rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));
        jsonObject.put("type", "订货额");
        jsonObject.put("currentData", rows.get(0).getBigDecimal("typestatistics"));
        jsonObject.put("yearonyeargrowth", rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics")));
        if (rowsLastYear.get(0).getBigDecimal("typestatistics").compareTo(BigDecimal.ZERO) == 0) {
            jsonObject.put("yearonyearrate", 0);
        } else {
            jsonObject.put("yearonyearrate", (rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics"))).divide(rowsLastYear.get(0).getBigDecimal("typestatistics"), 4, BigDecimal.ROUND_HALF_UP));
        }
        jsonArray.add(jsonObject);

        //订单量
        jsonObject = new JSONObject();
        factory = new SQLFactory(this, "销售单量统计查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        rows = dbConnect.runSqlQuery(factory.getSQL(false));

        factoryLastYear = new SQLFactory(this, "销售单量统计查询");
        factoryLastYear.addParameter("siteid", siteid);
        factoryLastYear.addParameter_SQL("where", whereLastYear);
        rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));

        jsonObject.put("type", "订单量");
        ;
        jsonObject.put("currentData", rows.get(0).getBigDecimal("typestatistics"));
        jsonObject.put("yearonyeargrowth", rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics")));
        if (rowsLastYear.get(0).getBigDecimal("typestatistics").compareTo(BigDecimal.ZERO) == 0) {
            jsonObject.put("yearonyearrate", 0);
        } else {
            jsonObject.put("yearonyearrate", (rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics"))).divide(rowsLastYear.get(0).getBigDecimal("typestatistics"), 4, BigDecimal.ROUND_HALF_UP));
        }
        jsonArray.add(jsonObject);

        //客户数
        jsonObject = new JSONObject();
        factory = new SQLFactory(this, "销售客户数统计查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        rows = dbConnect.runSqlQuery(factory.getSQL(false));

        factoryLastYear = new SQLFactory(this, "销售客户数统计查询");
        factoryLastYear.addParameter("siteid", siteid);
        factoryLastYear.addParameter_SQL("where", whereLastYear);
        rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));

        jsonObject.put("type", "客户数");
        jsonObject.put("currentData", rows.get(0).getBigDecimal("typestatistics"));
        jsonObject.put("yearonyeargrowth", rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics")));
        if (rowsLastYear.get(0).getBigDecimal("typestatistics").compareTo(BigDecimal.ZERO) == 0) {
            jsonObject.put("yearonyearrate", 0);
        } else {
            jsonObject.put("yearonyearrate", (rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics"))).divide(rowsLastYear.get(0).getBigDecimal("typestatistics"), 4, BigDecimal.ROUND_HALF_UP));
        }
        jsonArray.add(jsonObject);

        //回款
        jsonObject = new JSONObject();
        factory = new SQLFactory(this, "回款统计查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        rows = dbConnect.runSqlQuery(factory.getSQL(false));

        factoryLastYear = new SQLFactory(this, "回款统计查询");
        factoryLastYear.addParameter("siteid", siteid);
        factoryLastYear.addParameter_SQL("where", whereLastYear);
        rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));

        jsonObject.put("type", "回款");
        jsonObject.put("currentData", rows.get(0).getBigDecimal("typestatistics"));
        jsonObject.put("yearonyeargrowth", rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics")));
        if (rowsLastYear.get(0).getBigDecimal("typestatistics").compareTo(BigDecimal.ZERO) == 0) {
            jsonObject.put("yearonyearrate", 0);
        } else {
            jsonObject.put("yearonyearrate", (rows.get(0).getBigDecimal("typestatistics").subtract(rowsLastYear.get(0).getBigDecimal("typestatistics"))).divide(rowsLastYear.get(0).getBigDecimal("typestatistics"), 4, BigDecimal.ROUND_HALF_UP));
        }
        jsonArray.add(jsonObject);

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

    /**
     * 销售类型分析
     *
     * @return
     */
    @API(title = "销售订单按月份分析", apiversion = R.ID20230729142603.v1.class)
    @CACHEING
    public String SalesOrderByMonthAnalysis() throws Exception {

        String datatype = content.getString("datatype");
        String where = " 1=1 ";
        if (sys_enterpriseid > 0) {
            where = where + " and t1.sys_enterpriseid=" + sys_enterpriseid;
        }
        /*
         * 过滤条件设置
         */
//        if (content.containsKey("where")) {
//            JSONObject whereObject = content.getJSONObject("where");
//            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
//                begindate = whereObject.getString("begindate");
//            }
//            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
//                enddate = whereObject.getString("enddate");
//            }
//        }

        SQLFactory factory;
        if (datatype.equals("订货额")) {
            factory = new SQLFactory(this, "销售额统计查询_月份");
        } else if (datatype.equals("订货单量")) {
            factory = new SQLFactory(this, "销售单量统计查询_月份");
        } else {
            return getErrReturnObject().setErrMsg("datatype类型错误").toString();
        }
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));
        String year = new SimpleDateFormat("yyyy").format(new Date());
        String lastyear = String.valueOf(Integer.parseInt(new SimpleDateFormat("yyyy").format(new Date())) - 1);
        List<DataTrans> list = new ArrayList();

        for (Row row : rows) {
            DataTrans dataTrans = new DataTrans();
            if (row.getString("y").equals(year) || row.getString("y").equals(lastyear)) {
                dataTrans.setName(row.getString("y") + "-" + row.getString("m"));
                dataTrans.setMonth(row.getString("m"));
                dataTrans.setYear(row.getString("y"));
                dataTrans.setValue(row.getBigDecimal("typestatistics"));
                list.add(dataTrans);
            }
        }
        list = supplementDate(Integer.parseInt(year), list);
        list = supplementDate(Integer.parseInt(lastyear), list);
        list.sort(Comparator.comparingLong(o -> Long.parseLong(StringUtils.isBlank(o.getMonth()) ? "9999" : o.getMonth())));
        return getSucReturnObject().setData(list).toString();
    }

    /**
     * 销售类型分析
     *
     * @return
     */
    @API(title = "销售类型分析", apiversion = R.ID20230728133003.v1.class)
    @CACHEING
    public String SalesTypeAnalysis() throws YosException {

        String datetype = content.getStringValue("datetype");
        String datatype = content.getString("datatype");
        String begindate = "";
        String enddate = "";
        switch (datetype) {
            case "月":
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "周":
                begindate = getWeekFirstDay();
                enddate = getWeekLastDay();
                break;
            default:
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(enddate).append("' ");
        }


        SQLFactory factory;
        if (datatype.equals("销售额")) {
            factory = new SQLFactory(this, "销售额统计查询_类型");
        } else if (datatype.equals("销售单量")) {
            factory = new SQLFactory(this, "销售单量统计查询_类型");
        } else {
            return getErrReturnObject().setErrMsg("datatype类型错误").toString();
        }

        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

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


    /**
     * 订货额类别占比分析
     *
     * @return
     */
    @API(title = "订货额类别占比分析", apiversion = R.ID20230728143503.v1.class)
    @CACHEING
    public String orderAmountCategoriesAnalysis() throws Exception {

        String datetype = content.getStringValue("datetype");
        String datatype = content.getString("datatype");
        String begindate = "";
        String enddate = "";
        switch (datetype) {
            case "月":
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "周":
                begindate = getWeekFirstDay();
                enddate = getWeekLastDay();
                break;
            default:
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }

        StringBuffer where = new StringBuffer(" 1=1 ");
        StringBuffer whereLastYear = new StringBuffer(" 1=1 ");


        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
            Date date = sdf.parse(begindate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(sdf.format(cal.getTime())).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(enddate).append("' ");
            Date date = sdf.parse(enddate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(sdf.format(cal.getTime())).append("' ");
        }

        if (sys_enterpriseid > 0) {
            where.append(" and t1.sys_enterpriseid=" + sys_enterpriseid);
        }

        if (datatype.equals("标准")) {
            where.append(" and t1.type='标准订单' ");
            whereLastYear.append(" and t1.type='标准订单' ");
        } else if (datatype.equals("促销")) {
            where.append(" and t1.type='促销订单' ");
            whereLastYear.append(" and t1.type='标准订单' ");
        } else if (datatype.equals("特殊")) {
            where.append(" and t1.type='特殊订单' ");
            whereLastYear.append(" and t1.type='标准订单' ");
        } else {
            where.append(" and 1=1 ");
            whereLastYear.append(" and 1=1 ");
        }


        //Rows topItemclassRows = dbConnect.runSqlQuery("select itemclassid,itemclassname marketingcategory from plm_itemclass where classtype='营销' and siteid='" + siteid + "' and ifnull(parentid,0)=0");
        SQLFactory factory = new SQLFactory(this, "订货额类别统计分析");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

        SQLFactory factoryLastYear = new SQLFactory(this, "订货额类别统计分析");
        factoryLastYear.addParameter("siteid", siteid);
        factoryLastYear.addParameter_SQL("where", whereLastYear);
        Rows rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));
        RowsMap rowsLastYearMap = rowsLastYear.toRowsMap("marketingcategory");

        for (Row row : rows) {
            if (rows.isNotEmpty()) {
                row.put("amount", row.getBigDecimal("typestatistics"));
            } else {
                row.put("amount", BigDecimal.ZERO);
            }
            if (rowsLastYearMap.containsKey(row.getString("marketingcategory"))) {
                if (rowsLastYearMap.get(row.getString("marketingcategory")).isNotEmpty()) {
                    row.put("lastyearamount", rowsLastYearMap.get(row.getString("marketingcategory")).get(0).getBigDecimal("typestatistics"));
                } else {
                    row.put("lastyearamount", BigDecimal.ZERO);
                }
            } else {
                row.put("lastyearamount", BigDecimal.ZERO);
            }
            if (row.getBigDecimal("lastyearamount").compareTo(BigDecimal.ZERO) == 0) {
                row.put("yearonyear", BigDecimal.ZERO);
            } else {
                row.put("yearonyear", (row.getBigDecimal("amount").subtract(row.getBigDecimal("lastyearamount"))).divide(row.getBigDecimal("lastyearamount"), 4, BigDecimal.ROUND_HALF_UP));
            }
        }
        BigDecimal sumamount = rows.sum("amount");
        for (Row row : rows) {
            if (sumamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("proportion", BigDecimal.ZERO);
            } else {
                row.put("proportion", row.getBigDecimal("amount").divide(sumamount, 4, BigDecimal.ROUND_HALF_UP));
            }
        }
        rows.sort(Comparator.comparingDouble(o -> o.getDouble("amount")));
        Collections.reverse(rows);
        return getSucReturnObject().setData(rows).toString();
    }

    /**
     * 财务统计分析
     *
     * @return
     */
    @API(title = "财务统计分析", apiversion = R.ID20230802111003.v1.class)
    @CACHEING
    public String financialStatistics() throws Exception {

        String datetype = content.getStringValue("datetype");
        String begindate = "";
        String enddate = "";
        StringBuffer where = new StringBuffer(" 1=1 ");
        SQLFactory factory;
        switch (datetype) {
            case "月":
                factory = new SQLFactory(this, "财务统计_月");
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                factory = new SQLFactory(this, "财务统计_年度");
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "季":
                factory = new SQLFactory(this, "财务统计_季");
                begindate = sdf.format(getQuarterStart(new Date()).getTime());
                enddate = sdf.format(getQuarterEnd(new Date()).getTime());
                break;
            default:
                factory = new SQLFactory(this, "财务统计_月");
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }
        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(checkdate, '%Y-%m-%d') <='").append(enddate).append("' ");
        }


        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));
        List<DataTrans> list = new ArrayList();

        switch (datetype) {
            case "月":
                for (Row row : rows) {
                    DataTrans dataTrans = new DataTrans();
                    if (row.getString("y").equals(new SimpleDateFormat("yyyy").format(new Date()))) {
                        dataTrans.setName(row.getString("y") + "-" + row.getString("d") + "-" + row.getString("type"));
                        dataTrans.setType(row.getString("type"));
                        dataTrans.setMonth(row.getString("d"));
                        dataTrans.setYear(row.getString("y"));
                        dataTrans.setValue(row.getBigDecimal("typestatistics"));
                        list.add(dataTrans);
                    }
                }
                list = supplementDate_cashbill_month(Integer.parseInt(new SimpleDateFormat("yyyy").format(new Date())), list);
                break;
            case "年":
                for (Row row : rows) {
                    DataTrans dataTrans = new DataTrans();
                    if (row.getString("y").equals(new SimpleDateFormat("yyyy").format(new Date()))) {
                        dataTrans.setName(row.getString("y") + "-" + row.getString("m") + "-" + row.getString("type"));
                        dataTrans.setType(row.getString("type"));
                        dataTrans.setMonth(row.getString("m"));
                        dataTrans.setYear(row.getString("y"));
                        dataTrans.setValue(row.getBigDecimal("typestatistics"));
                        list.add(dataTrans);
                    }
                }
                list = supplementDate_cashbill(Integer.parseInt(new SimpleDateFormat("yyyy").format(new Date())), list);
                break;
            case "季":
                for (Row row : rows) {
                    DataTrans dataTrans = new DataTrans();
                    if (row.getString("y").equals(new SimpleDateFormat("yyyy").format(new Date()))) {
                        dataTrans.setName(row.getString("y") + "-" + row.getString("m") + "-" + row.getString("type"));
                        dataTrans.setType(row.getString("type"));
                        dataTrans.setMonth(row.getString("m"));
                        dataTrans.setYear(row.getString("y"));
                        dataTrans.setValue(row.getBigDecimal("typestatistics"));
                        list.add(dataTrans);
                    }
                }
                list = supplementDate_cashbill_quarter(Integer.parseInt(new SimpleDateFormat("yyyy").format(new Date())), list);
                break;
            default:
                break;
        }

        list.sort(Comparator.comparingLong(o -> Long.parseLong(StringUtils.isBlank(o.getMonth()) ? "9999" : o.getMonth())));
        return getSucReturnObject().setData(list).toString();
    }


    /**
     * 新品销售占比
     *
     * @return
     */
    @API(title = "新品销售占比", apiversion = R.ID20230802100103.v1.class)
    @CACHEING
    public String newProductSalesAnalysis() throws Exception {

        String datetype = content.getStringValue("datetype");
        String begindate = "";
        String enddate = "";
        switch (datetype) {
            case "月":
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "周":
                begindate = getWeekFirstDay();
                enddate = getWeekLastDay();
                break;
            default:
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }

        StringBuffer where = new StringBuffer(" 1=1 ");
        StringBuffer whereLastYear = new StringBuffer(" 1=1 ");


        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
            Date date = sdf.parse(begindate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(sdf.format(cal.getTime())).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(enddate).append("' ");
            Date date = sdf.parse(enddate);
            //创建Calendar实例
            Calendar cal = Calendar.getInstance();
            //设置当前时间
            cal.setTime(date);
            //在当前时间基础上减一年
            cal.add(Calendar.YEAR, -1);
            whereLastYear.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(sdf.format(cal.getTime())).append("' ");
        }

        SQLFactory factory = new SQLFactory(this, "新品销售占比");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

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

    /**
     * 新品销售走势
     *
     * @return
     */
    @API(title = "新品销售走势", apiversion = R.ID20230802103303.v1.class)
    @CACHEING
    public String newProductSalesTrend() throws Exception {
        SQLFactory factory = new SQLFactory(this, "新品销售走势");
        String year = new SimpleDateFormat("yyyy").format(new Date());
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("year", year);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));
        List<DataTrans> list = new ArrayList();
        for (Row row : rows) {
            DataTrans dataTrans = new DataTrans();
            if (row.getString("y").equals(year)) {
                dataTrans.setName(row.getString("y") + "-" + row.getString("m"));
                dataTrans.setMonth(row.getString("m"));
                dataTrans.setYear(row.getString("y"));
                dataTrans.setValue(row.getBigDecimal("typestatistics"));
                list.add(dataTrans);
            }
        }
        list = supplementDate(Integer.parseInt(year), list);
        return getSucReturnObject().setData(list).toString();
    }


    /**
     * 战区数据分析
     *
     * @return
     */
    @API(title = "战区数据分析", apiversion = R.ID20230802093703.v1.class)
    @CACHEING
    public String warZoneAnalysis() throws YosException {

        String datetype = content.getStringValue("datetype");
        String datatype = content.getString("datatype");
        String begindate = "";
        String enddate = "";
        switch (datetype) {
            case "月":
                begindate = getMonthFirstDay();
                enddate = getMonthLastDay();
                break;
            case "年":
                begindate = new SimpleDateFormat("yyyy").format(new Date()) + "-01-01";
                enddate = new SimpleDateFormat("yyyy").format(new Date()) + "-12-31";
                break;
            case "周":
                begindate = getWeekFirstDay();
                enddate = getWeekLastDay();
                break;
            default:
                break;
        }
        /*
         * 过滤条件设置
         */
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                begindate = whereObject.getString("begindate");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                enddate = whereObject.getString("enddate");
            }
        }

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (!begindate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
        }
        if (!enddate.equals("")) {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(enddate).append("' ");
        }


        SQLFactory factory;
        if (datatype.equals("经销商")) {
            factory = new SQLFactory(this, "战区数据_经销商");
        } else if (datatype.equals("区域")) {
            factory = new SQLFactory(this, "战区数据_区域");
        } else {
            return getErrReturnObject().setErrMsg("datatype类型错误").toString();
        }

        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

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


    /**
     * 工厂待发明细
     *
     * @return
     */
    @API(title = "工厂待发明细", apiversion = R.ID20230803140903.v1.class)
    @CACHEING
    public String factoryPendingDetails() throws YosException {


        /*
         * 过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (sys_enterpriseid > 0) {
            where.append(" and t1.sys_enterpriseid=" + sys_enterpriseid);

        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
        }

        SQLFactory factory = new SQLFactory(this, "工厂待发订单明细查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

        SQLFactory sumfactory = new SQLFactory(this, "工厂待发发货明细查询");
        sumfactory.addParameter("siteid", siteid);
        sumfactory.addParameter_in("sa_orderitemsids", rows.toArrayList("sa_orderitemsid",new ArrayList<Long>()));
        Rows sumrows = dbConnect.runSqlQuery(sumfactory.getSQL(false));
        RowsMap sumrowsMap =sumrows.toRowsMap("sa_orderitemsid");
        BigDecimal sumundeliqty=BigDecimal.ZERO;
        BigDecimal sumundeliamount=BigDecimal.ZERO;
        for (Row row :rows) {
            if(sumrowsMap.containsKey(row.getString("sa_orderitemsid"))){
                row.put("undeliqty", row.getBigDecimal("qty").subtract(sumrowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("sumoutwarehouseqty")));
                row.put("undeliamount", (row.getBigDecimal("qty").subtract(sumrowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("sumoutwarehouseqty"))).multiply(row.getBigDecimal("price")));
                sumundeliqty=sumundeliqty.add(row.getBigDecimal("qty").subtract(sumrowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("sumoutwarehouseqty")));
                sumundeliamount=sumundeliamount.add((row.getBigDecimal("qty").subtract(sumrowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("sumoutwarehouseqty"))).multiply(row.getBigDecimal("price")));
            }else{
                row.put("undeliqty",row.getBigDecimal("qty"));
                row.put("undeliamount",row.getBigDecimal("amount"));
                sumundeliqty=sumundeliqty.add(row.getBigDecimal("qty"));
                sumundeliamount=sumundeliamount.add(row.getBigDecimal("amount"));
            }

        }
        if (rows.isNotEmpty()) {
            Rows sumrowstotal= new Rows();
            Row row =new Row();
            row.put("sumundeliqty",sumundeliqty);
            row.put("sumundeliamount",sumundeliamount);
            sumrowstotal.add(row);
            rows.get(0).put("sumrows", sumrowstotal);
        }

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


    /**
     * @Description: 获取季度第一天
     * 1.根据给定日期计算当前季度的第一个月份
     * 2.设置日历的月份为当前季度的第一个月份
     * 3.最后设置日历月份天数为第一天即可
     * @Author: wsp
     **/
    public static Calendar getQuarterStart(Date date) {
        Calendar startCalendar = Calendar.getInstance();
        startCalendar.setTime(date);
        //get方法：获取给定日历属性的值，如 endCalendar.get(Calendar.MONTH) 获取日历的月份
        //计算季度数：由于月份从0开始，即1月份的Calendar.MONTH值为0,所以计算季度的第一个月份只需 月份 / 3 * 3
        startCalendar.set(Calendar.MONTH, (((int) startCalendar.get(Calendar.MONTH)) / 3) * 3);
        startCalendar.set(Calendar.DAY_OF_MONTH, 1);
        return startCalendar;
    }

    /**
     * @Description: 获取季度最后一天
     * @Author: wsp
     **/
    public static Calendar getQuarterEnd(Date date) { // 季度结束
        Calendar endCalendar = Calendar.getInstance();
        endCalendar.setTime(date);
        //计算季度数：由于月份从0开始，即1月份的Calendar.MONTH值为0,所以计算季度的第三个月份只需 月份 / 3 * 3 + 2
        endCalendar.set(Calendar.MONTH, (((int) endCalendar.get(Calendar.MONTH)) / 3) * 3 + 2);
        endCalendar.set(Calendar.DAY_OF_MONTH, endCalendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        return endCalendar;
    }

    public static List<DataTrans> supplementDate(int year, List<DataTrans> params) {
        String[] monthAndDay = new String[]{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"};
        List<DataTrans> list = new ArrayList<>();
        if (params.isEmpty()) {
            for (String s : monthAndDay) {
                DataTrans dataTrans = new DataTrans();
                String date = year + "-" + s;
                dataTrans.setName(date);
                dataTrans.setMonth(s);
                dataTrans.setYear(String.valueOf(year));
                dataTrans.setValue(BigDecimal.ZERO);
                list.add(dataTrans);
            }
        } else {
            for (DataTrans data : params) {
                list.add(data);
                for (String s : monthAndDay) {
                    DataTrans dataTrans = new DataTrans();
                    String date = year + "-" + s;
                    if (!date.equals(data.getName())) {
                        dataTrans.setName(date);
                        dataTrans.setMonth(s);
                        dataTrans.setYear(String.valueOf(year));
                        dataTrans.setValue(BigDecimal.ZERO);
                        list.add(dataTrans);
                    }
                }
            }
        }
        // 集合中相同属性去重、值合并
        return merge(list);
    }

    public static List<DataTrans> supplementDate_cashbill(int year, List<DataTrans> params) {
        String[] monthAndDay = new String[]{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"};
        String[] types = new String[]{"支出", "收入"};
        List<DataTrans> list = new ArrayList<>();
        if (params.isEmpty()) {
            for (String type : types) {
                for (String s : monthAndDay) {
                    DataTrans dataTrans = new DataTrans();
                    String date = year + "-" + s + "-" + type;
                    dataTrans.setName(date);
                    dataTrans.setMonth(s);
                    dataTrans.setType(type);
                    dataTrans.setYear(String.valueOf(year));
                    dataTrans.setValue(BigDecimal.ZERO);
                    list.add(dataTrans);
                }
            }
        } else {
            for (DataTrans data : params) {
                list.add(data);
                for (String type : types) {
                    for (String s : monthAndDay) {
                        DataTrans dataTrans = new DataTrans();
                        String date = year + "-" + s + "-" + type;
                        if (!date.equals(data.getName())) {
                            dataTrans.setName(date);
                            dataTrans.setMonth(s);
                            dataTrans.setType(type);
                            dataTrans.setYear(String.valueOf(year));
                            dataTrans.setValue(BigDecimal.ZERO);
                            list.add(dataTrans);
                        }
                    }
                }

            }
        }
        // 集合中相同属性去重、值合并
        return merge(list);
    }

    public static List<DataTrans> supplementDate_cashbill_month(int year, List<DataTrans> params) {
        String[] monthAndDay = new String[]{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"};
        String[] types = new String[]{"支出", "收入"};
        List<DataTrans> list = new ArrayList<>();
        if (params.isEmpty()) {
            for (String type : types) {
                for (String s : monthAndDay) {
                    DataTrans dataTrans = new DataTrans();
                    String date = year + "-" + s + "-" + type;
                    dataTrans.setName(date);
                    dataTrans.setMonth(s);
                    dataTrans.setType(type);
                    dataTrans.setYear(String.valueOf(year));
                    dataTrans.setValue(BigDecimal.ZERO);
                    list.add(dataTrans);
                }
            }
        } else {
            for (DataTrans data : params) {
                list.add(data);
                for (String type : types) {
                    for (String s : monthAndDay) {
                        DataTrans dataTrans = new DataTrans();
                        String date = year + "-" + s + "-" + type;
                        if (!date.equals(data.getName())) {
                            dataTrans.setName(date);
                            dataTrans.setMonth(s);
                            dataTrans.setType(type);
                            dataTrans.setYear(String.valueOf(year));
                            dataTrans.setValue(BigDecimal.ZERO);
                            list.add(dataTrans);
                        }
                    }
                }
            }
        }

        // 集合中相同属性去重、值合并
        return merge(list);
    }

    public static List<DataTrans> supplementDate_cashbill_quarter(int year, List<DataTrans> params) {
        String[] monthAndDay = new String[3];
        Calendar startCalendar = Calendar.getInstance();
        startCalendar.setTime(new Date());
        //get方法：获取给定日历属性的值，如 endCalendar.get(Calendar.MONTH) 获取日历的月份
        //计算季度数：由于月份从0开始，即1月份的Calendar.MONTH值为0,所以计算季度的第一个月份只需 月份 / 3 * 3
        int current = (((int) startCalendar.get(Calendar.MONTH)) / 3) * 3;
        for (int i = 1; i <= 3; i++) {
            monthAndDay[i - 1] = String.format("%02d", current + i);
        }
        String[] types = new String[]{"支出", "收入"};
        List<DataTrans> list = new ArrayList<>();
        if (params.isEmpty()) {
            for (String type : types) {
                for (String s : monthAndDay) {
                    DataTrans dataTrans = new DataTrans();
                    String date = year + "-" + s + "-" + type;
                    dataTrans.setName(date);
                    dataTrans.setMonth(s);
                    dataTrans.setType(type);
                    dataTrans.setYear(String.valueOf(year));
                    dataTrans.setValue(BigDecimal.ZERO);
                    list.add(dataTrans);
                }
            }
        } else {
            for (DataTrans data : params) {
                list.add(data);
                for (String type : types) {
                    for (String s : monthAndDay) {
                        DataTrans dataTrans = new DataTrans();
                        String date = year + "-" + s + "-" + type;
                        if (!(date.equals(data.getName()) && type.equals(data.getType()))) {
                            dataTrans.setName(date);
                            dataTrans.setMonth(s);
                            dataTrans.setType(type);
                            dataTrans.setYear(String.valueOf(year));
                            dataTrans.setValue(BigDecimal.ZERO);
                            list.add(dataTrans);
                        }
                    }
                }
            }
        }
        // 集合中相同属性去重、值合并
        return merge(list);
    }

    public static List<DataTrans> merge(List<DataTrans> list) {
        List<DataTrans> result = list.stream()
                // 表示name为key，接着如果有重复的，那么从DataTrans对象o1与o2中筛选出一个，这里选择o1，
                // 并把name重复，需要将value与o1进行合并的o2, 赋值给o1，最后返回o1
                .collect(Collectors.toMap(DataTrans::getName, a -> a, (o1, o2) -> {
                    o1.setValue(o1.getValue().add(o2.getValue()));
                    return o1;
                })).values().stream().collect(Collectors.toList());
        return result;
    }

    public String caluteDate(int datetype, String selectdate) throws Exception {
        Date date = sdf.parse(selectdate);
        //创建Calendar实例
        Calendar cal = Calendar.getInstance();
        //设置当前时间
        cal.setTime(date);
        //在当前时间基础上减一年
        cal.add(datetype, -1);
        return sdf.format(cal.getTime());
    }


}