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 com.sun.org.apache.bcel.internal.generic.NEW;
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 restcontroller.R;

import javax.swing.tree.RowMapper;
import java.math.BigDecimal;
import java.math.RoundingMode;
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.ID20230729103203.v1.class)
    @CACHEING
    public String orderAndPaymentAnalysis() 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 = 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;
            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 != "") {
            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 != "") {
            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("' ");
        }

        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");
        /*
         * 过滤条件设置
         */
//        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);
        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);

        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 != "") {
            where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(begindate).append("' ");
        }
        if (enddate != "") {
            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 != "") {
            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 != "") {
            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 (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 from plm_itemclass where classtype='营销' and siteid='" + siteid + "' and ifnull(parentid,0)=0");
        for (Row row : topItemclassRows) {
            ArrayList<Long> itemclassids = ItemClass.getSubItemClassIds(this, row.getLong("itemclassid"));
            SQLFactory factory = new SQLFactory(this, "订货额类别统计分析");
            factory.addParameter("siteid", siteid);
            factory.addParameter_SQL("where", where);
            factory.addParameter_in("itemclassid", itemclassids);
            Rows rows = dbConnect.runSqlQuery(factory.getSQL(false));

            SQLFactory factoryLastYear = new SQLFactory(this, "订货额类别统计分析");
            factoryLastYear.addParameter("siteid", siteid);
            factoryLastYear.addParameter_SQL("where", whereLastYear);
            factoryLastYear.addParameter_in("itemclassid", itemclassids);
            Rows rowsLastYear = dbConnect.runSqlQuery(factoryLastYear.getSQL(false));
            if (rows.isNotEmpty()) {
                row.put("amount", rows.get(0).getBigDecimal("typestatistics"));
            } else {
                row.put("amount", BigDecimal.ZERO);
            }
            if (rowsLastYear.isNotEmpty()) {
                row.put("lastyearamount", rowsLastYear.get(0).getBigDecimal("typestatistics"));
            } 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 = topItemclassRows.sum("amount");
        for (Row row : topItemclassRows) {
            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));
            }
        }

        return getSucReturnObject().setData(topItemclassRows).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<>();
        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> 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;
    }



}