kmall-haikong/kmall-admin/src/main/java/com/kmall/admin/controller/statistics/MonthlyCustomersController.java

package com.kmall.admin.controller.statistics;

import com.kmall.admin.dao.GoodsDao;
import com.kmall.admin.dao.ProductStoreRelaDao;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.kmall.admin.dao.statistics.MonthlyCustomersDao;
import com.kmall.admin.dao.statistics.WechatFollowersDao;
import com.kmall.admin.entity.GoodsEntity;
import com.kmall.admin.entity.MonthlySalesGrowthEntity;
import com.kmall.admin.entity.ProductStoreRelaEntity;
import com.kmall.admin.entity.WechatFollowersEntity;
import com.kmall.admin.fromcomm.entity.SysUserEntity;
import com.kmall.admin.service.statistics.MonthlyCustomersService;
import com.kmall.admin.utils.ParamUtils;
import com.kmall.admin.utils.ShiroUtils;
import com.kmall.common.utils.R;
import com.kmall.common.utils.excel.ExcelExport;
import com.kmall.common.utils.excel.ExcelUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author zhangchuangbiao
 * @version 1.0
 * 2020-09-01 14:35
 */
@RestController
@RequestMapping("/monthly")
public class MonthlyCustomersController {

    @Autowired
    private MonthlyCustomersService monthlyCustomersService;

    @Autowired
    private ExcelUtil excelUtil;

    @Autowired
    private MonthlyCustomersDao monthlyCustomersDao;
    @Autowired
    private WechatFollowersDao wechatFollowersDao;
    @Autowired
    private GoodsDao goodsDao;
    @Autowired
    private ProductStoreRelaDao productStoreRelaDao;


    private void calculateDifferentMonth(List<String> monthList, String startMonth, String endMonth) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        Calendar bef = Calendar.getInstance();
        Calendar aft = Calendar.getInstance();
        bef.setTime(sdf.parse(startMonth));
        aft.setTime(sdf.parse(endMonth));

        do {
            monthList.add(sdf.format(bef.getTime()));
            bef.add(Calendar.MONTH, 1);
        }
        while (bef.compareTo(aft) <= 0);

    }

    /**
     * 每月销售额查询
     * @param startMonth
     * @param endMonth
     * @return
     */
    @RequestMapping("/customersQuery")
    public R queryMonthlyCustomers(@RequestParam("startMonth") String startMonth, @RequestParam("endMonth") String endMonth) {
        if (Objects.isNull(startMonth) || "".equals(startMonth)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endMonth)  || "".equals(endMonth)){
            return R.error("请先选择结束时间");
        }
        List<String> dateList = new ArrayList<>();
        Map<String, Object> returnMap = new HashMap<>();
        try {
            calculateDifferentMonth(dateList, startMonth, endMonth);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        try {
            String merchSn = null;
            SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
            if(!"1".equals(sysUser.getRoleType())){
                merchSn = sysUser.getMerchSn();
            }
            Map<String,Object> map = monthlyCustomersService.queryMonthlyCustomers(startMonth,endMonth,merchSn,dateList);

            returnMap.put("dateList", dateList);
            returnMap.putAll(map);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error(e.getMessage());
        }
        return R.ok(returnMap);
    }

    /**
     * 每月销售额销售量导出
     * @param params
     * @param response
     * @param request
     * @return
     */
    @RequestMapping("/monthlySalesGrowthExport")
    public R monthlySalesGrowthExport(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");
        if (Objects.isNull(params.get("startMonth"))  || "".equals(params.get("startMonth"))){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(params.get("endMonth"))  || "".equals(params.get("endMonth"))){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }
        List<MonthlySalesGrowthEntity> monthlySalesGrowthEntities = monthlyCustomersDao.queryMonthlyCustomers((String) params.get("startMonth"), (String) params.get("endMonth"), merchSn);
        ExcelExport ee = new ExcelExport("Monthly Sales Growth");
        String[] header = new String[]{"序号","商户编号","商户名称","销售额","销售数量","年-月"};
        List<Map<String, Object>> list = new ArrayList<>();
        int count = 1;
        if (monthlySalesGrowthEntities!=null && monthlySalesGrowthEntities.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : monthlySalesGrowthEntities) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                map.put("OrderNumber",count++);
                map.put("MerchSn",monthlySalesGrowthEntity.getMerchSn());
                map.put("MerchName",monthlySalesGrowthEntity.getMerchName());
                map.put("TotalSales",monthlySalesGrowthEntity.getTotalSales());
                map.put("TotalNumber",monthlySalesGrowthEntity.getTotalNumber());
                map.put("YearAndMonth",monthlySalesGrowthEntity.getYearAndMonth());
                list.add(map);
            }
        }
        ee.addSheetByMap("Monthly Sales Growth", list, header);
        ee.export(response);
        return R.ok();
    }

    /**
     * 导出销售额销售数客单数客单价
     * @param params
     * @param response
     * @param request
     * @return
     */
    @RequestMapping("/monthlySalesGrowthExport2")
    public R monthlySalesGrowthExport2(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");
        if (Objects.isNull(params.get("startMonth")) || "".equals(params.get("startMonth"))){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(params.get("endMonth"))  || "".equals(params.get("endMonth"))){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }
        List<MonthlySalesGrowthEntity> monthlySalesGrowthEntities = monthlyCustomersDao.queryMonthlyCustomers((String) params.get("startMonth"), (String) params.get("endMonth"), merchSn);
        ExcelExport ee = new ExcelExport("Monthly Customers&Avg Basket");
        String[] header = new String[]{"序号","商户编号","商户名称","销售额","销售数量","客户数","客单价","年-月"};
        List<Map<String, Object>> list = new ArrayList<>();
        int count = 1;
        if (monthlySalesGrowthEntities!=null && monthlySalesGrowthEntities.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : monthlySalesGrowthEntities) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                map.put("OrderNumber",count++);
                map.put("MerchSn",monthlySalesGrowthEntity.getMerchSn());
                map.put("MerchName",monthlySalesGrowthEntity.getMerchName());
                map.put("TotalSales",monthlySalesGrowthEntity.getTotalSales());
                map.put("TotalNumber",monthlySalesGrowthEntity.getTotalNumber());
                map.put("TotalCustomers",monthlySalesGrowthEntity.getTotalCustomers());
                String avgBasketSale = new BigDecimal(monthlySalesGrowthEntity.getTotalSales()).divide(new BigDecimal(monthlySalesGrowthEntity.getTotalNumber()),3, RoundingMode.HALF_UP).toString();
                map.put("avgBasketSale",avgBasketSale);
                map.put("YearAndMonth",monthlySalesGrowthEntity.getYearAndMonth());
                list.add(map);
            }
        }
        ee.addSheetByMap("Monthly Customers&Avg Basket", list, header);
        ee.export(response);
        return R.ok();
    }


    /**
     * 销售前10的产品
     * @param month
     * @param week
     * @return
     */
    @RequestMapping("/top10ForProduct")
    public R top10ForProduct(@RequestParam("month") String month, @RequestParam("week") String week){
        if (Objects.isNull(month) || "".equals(month)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(week)  || "".equals(week)){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }

        Map<String,Object> map = monthlyCustomersService.top10ForProduct(month,week,merchSn);

        return R.ok(map);
    }


    /**
     * 销售前10的品牌/供应商的商品
     * @param startDate
     * @param endDate
     * @return
     */
    @RequestMapping("/top10ByBrandAndSupplier")
    public R top10ByBrandAndSupplier(@RequestParam("startDate") String startDate,
                                     @RequestParam("endDate") String endDate){
        if (Objects.isNull(startDate) || "".equals(startDate)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endDate)  || "".equals(endDate)){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }

        Map<String,Object> map = monthlyCustomersService.top10ByBrandAndSupplier(startDate,endDate,merchSn);

        return R.ok(map);
    }

    @RequestMapping("/top10ByBrandAndSupplierExport")
    public R top10ByBrandAndSupplierExport(@RequestParam("startDate") String startDate,
                                     @RequestParam("endDate") String endDate, HttpServletResponse response, HttpServletRequest request){
        if (Objects.isNull(startDate) || "".equals(startDate)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endDate)  || "".equals(endDate)){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }

        Map<String,Object> map = monthlyCustomersService.top10ByBrandAndSupplier(startDate,endDate,merchSn);
        List<MonthlySalesGrowthEntity> top10ByBrand = (List<MonthlySalesGrowthEntity>) map.get("top10ByBrand");
        List<MonthlySalesGrowthEntity> top10BySupplier = (List<MonthlySalesGrowthEntity>) map.get("top10BySupplier");

        ExcelExport ee = new ExcelExport("产品销量前10");
        String[] header1 = new String[]{"序号","商户名称","品牌","产品规格","销售数量"};
        String[] header2 = new String[]{"序号","商户名称","供应商","产品规格","销售数量"};
        List<Map<String, Object>> list1 = new ArrayList<>();
        List<Map<String, Object>> list2 = new ArrayList<>();
        int count = 1;
        if (top10ByBrand!=null && top10ByBrand.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : top10ByBrand) {
                LinkedHashMap<String, Object> tempMap = new LinkedHashMap<>();
                tempMap.put("OrderNumber",count++);
                tempMap.put("MerchName",monthlySalesGrowthEntity.getMerchName());
                tempMap.put("Brand",monthlySalesGrowthEntity.getBrand());
                tempMap.put("GoodsSpecificationNameValue",monthlySalesGrowthEntity.getGoodsSpecificationNameValue());
                tempMap.put("sales",monthlySalesGrowthEntity.getSales());
                list1.add(tempMap);
            }
        }
        count=1;
        if (top10BySupplier!=null && top10BySupplier.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : top10BySupplier) {
                LinkedHashMap<String, Object> tempMap = new LinkedHashMap<>();
                tempMap.put("OrderNumber",count++);
                tempMap.put("MerchName",monthlySalesGrowthEntity.getMerchName());
                tempMap.put("Supplier",monthlySalesGrowthEntity.getSupplier());
                tempMap.put("GoodsSpecificationNameValue",monthlySalesGrowthEntity.getGoodsSpecificationNameValue());
                tempMap.put("sales",monthlySalesGrowthEntity.getSales());
                list2.add(tempMap);
            }
        }



        ee.addSheetByMap("top10ByBrand", list1, header1);
        ee.addSheetByMap("top10BySupplier", list2, header2);
        ee.export(response);


        return R.ok(map);
    }

    /**
     * 每周销售额销售量图表查询
     * @param startWeek
     * @param endWeek
     * @return
     * @throws ParseException
     */
    @RequestMapping("/customersQueryByWeek")
    public R queryWeeklyCustomers(@RequestParam("startWeek") String startWeek, @RequestParam("endWeek") String endWeek) throws ParseException {
        if (Objects.isNull(startWeek) || "".equals(startWeek)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endWeek)  || "".equals(endWeek)){
            return R.error("请先选择结束时间");
        }
        List<String> dateList = new ArrayList<>();
        try {
            calculateDifferentWeek(dateList, startWeek, endWeek);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }

        SimpleDateFormat weekSdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar bef = Calendar.getInstance();
        Calendar aft = Calendar.getInstance();
        bef.setFirstDayOfWeek(Calendar.MONDAY);
        aft.setFirstDayOfWeek(Calendar.MONDAY);
        bef.setTime(sdf.parse(startWeek));
        aft.setTime(sdf.parse(endWeek));
        int befWeek = bef.get(Calendar.WEEK_OF_YEAR);
        int aftWeek = aft.get(Calendar.WEEK_OF_YEAR);
        if(befWeek<10){
            startWeek =weekSdf.format(bef.getTime())+"-0"+befWeek;
        }else{
            startWeek =weekSdf.format(bef.getTime())+"-"+befWeek;
        }
        if(aftWeek<10){
            endWeek =weekSdf.format(aft.getTime())+"-0"+aftWeek;
        }else{
            endWeek =weekSdf.format(aft.getTime())+"-"+aftWeek;
        }
        Map<String,Object> map = monthlyCustomersService.queryWeeklyCustomers(startWeek,endWeek,merchSn,dateList);

        Map<String, Object> returnMap = new HashMap<>();
        returnMap.put("dateList", dateList);
        returnMap.putAll(map);


        return R.ok(returnMap);
    }

    @RequestMapping("/weeklySalesSummaryExport")
    public R weeklySalesSummaryExport(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) throws ParseException {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");
        if (Objects.isNull(params.get("startWeek"))){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(params.get("endWeek"))){
            return R.error("请先选择结束时间");
        }
        String startWeek = (String) params.get("startWeek");
        String endWeek = (String) params.get("endWeek");

        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }
        SimpleDateFormat weekSdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar bef = Calendar.getInstance();
        Calendar aft = Calendar.getInstance();
        bef.setFirstDayOfWeek(Calendar.MONDAY);
        aft.setFirstDayOfWeek(Calendar.MONDAY);
        bef.setTime(sdf.parse(startWeek));
        aft.setTime(sdf.parse(endWeek));
        int befWeek = bef.get(Calendar.WEEK_OF_YEAR);
        int aftWeek = aft.get(Calendar.WEEK_OF_YEAR);
        if(befWeek<10){
            startWeek =weekSdf.format(bef.getTime())+"-0"+befWeek;
        }else{
            startWeek =weekSdf.format(bef.getTime())+"-"+befWeek;
        }
        if(aftWeek<10){
            endWeek =weekSdf.format(aft.getTime())+"-0"+aftWeek;
        }else{
            endWeek =weekSdf.format(aft.getTime())+"-"+aftWeek;
        }
        List<MonthlySalesGrowthEntity> monthlySalesGrowthEntities = monthlyCustomersDao.queryWeeklyCustomers(startWeek, endWeek, merchSn);
        ExcelExport ee = new ExcelExport("Weekly Sales Summary");
        String[] header = new String[]{"序号","商户编号","商户名称","销售额","销售数量","年-周"};
        List<Map<String, Object>> list = new ArrayList<>();
        int count = 1;
        if (monthlySalesGrowthEntities!=null && monthlySalesGrowthEntities.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : monthlySalesGrowthEntities) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                map.put("OrderNumber",count++);
                map.put("MerchSn",monthlySalesGrowthEntity.getMerchSn());
                map.put("MerchName",monthlySalesGrowthEntity.getMerchName());
                map.put("TotalSales",monthlySalesGrowthEntity.getTotalSales());
                map.put("TotalNumber",monthlySalesGrowthEntity.getTotalNumber());
                map.put("YearAndWeek",monthlySalesGrowthEntity.getYearAndWeek());
                list.add(map);
            }
        }
        ee.addSheetByMap("Weekly Sales Summary", list, header);
        ee.export(response);
        return R.ok();
    }


    private void calculateDifferentWeek(List<String> weekList, String startWeek, String endWeek) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat weekSdf = new SimpleDateFormat("yyyy");
        Calendar bef = Calendar.getInstance();
        Calendar aft = Calendar.getInstance();
        bef.setFirstDayOfWeek(Calendar.MONDAY);
        aft.setFirstDayOfWeek(Calendar.MONDAY);
        bef.setTime(sdf.parse(startWeek));
        aft.setTime(sdf.parse(endWeek));

        do {
            int i = bef.get(Calendar.WEEK_OF_YEAR);
            if (i<10){
                weekList.add(weekSdf.format(bef.getTime())+"-0"+i);
            }else{
                weekList.add(weekSdf.format(bef.getTime())+"-"+i);
            }
            bef.set(Calendar.DAY_OF_MONTH,bef.get(Calendar.DAY_OF_MONTH)+7); //给当前时间增加一周
        }
        while (bef.compareTo(aft) <= 0);
    }


    /**
     * 每月微信好友图表查询
     * @param startMonth
     * @param endMonth
     * @return
     */
    @RequestMapping("/queryMonthlyWechatFollowers")
    public R queryMonthlyWechatFollowers(@RequestParam("startMonth") String startMonth, @RequestParam("endMonth") String endMonth) {
        if (Objects.isNull(startMonth) || "".equals(startMonth)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endMonth)  || "".equals(endMonth)){
            return R.error("请先选择结束时间");
        }
        List<String> dateList = new ArrayList<>();
        Map<String, Object> returnMap = new HashMap<>();
        try {
            calculateDifferentMonth(dateList, startMonth, endMonth);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        try {
            String merchSn = null;
            SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
            if(!"1".equals(sysUser.getRoleType())){
                merchSn = sysUser.getMerchSn();
            }


            Map<String,Object> map = monthlyCustomersService.queryMonthlyWechatFollowers(startMonth,endMonth,merchSn);

            returnMap.put("dateList", dateList);
            returnMap.putAll(map);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error(e.getMessage());
        }


        return R.ok(returnMap);
    }


    /**
     * 添加微信好友数据
     * @param wechatFollowers
     * @return
     */
    @RequestMapping("/addWechatFollowers")
    public R addWechatFollowers( @RequestParam("wechatFollowers") Integer wechatFollowers) {

        try {
            String merchSn = null;
            SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
            if(!"1".equals(sysUser.getRoleType())){
                merchSn = sysUser.getMerchSn();
            }
            WechatFollowersEntity wechatFollowersEntity = new WechatFollowersEntity();
            wechatFollowersEntity.setCreaterSn(ShiroUtils.getUserId().toString());
            wechatFollowersEntity.setCreateTime(new Date());
            wechatFollowersEntity.setWechatFollowers(wechatFollowers);
            wechatFollowersEntity.setMerchSn(merchSn);

            monthlyCustomersService.addWechatFollowers(wechatFollowersEntity);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error(e.getMessage());
        }


        return R.ok("添加成功");
    }

    @RequestMapping("/wechatFollowersGrowthExport")
    public R wechatFollowersGrowthExport(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");
        if (Objects.isNull(params.get("startMonth"))  || "".equals(params.get("startMonth"))){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(params.get("endMonth"))  || "".equals(params.get("endMonth"))){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }
        List<WechatFollowersEntity> wechatFollowersEntities = wechatFollowersDao.queryMonthlyWechatFollowers((String) params.get("startMonth"), (String) params.get("endMonth"), merchSn);
        ExcelExport ee = new ExcelExport("Wechat Followers Growth");
        String[] header = new String[]{"序号","商户编号","好友数量","年-月"};
        List<Map<String, Object>> list = new ArrayList<>();
        int count = 1;
        if (wechatFollowersEntities!=null && wechatFollowersEntities.size()>0) {
            for (WechatFollowersEntity wechatFollowersEntity : wechatFollowersEntities) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                map.put("OrderNumber",count++);
                map.put("MerchSn",wechatFollowersEntity.getMerchSn());
                map.put("WechatFollowers",wechatFollowersEntity.getWechatFollowers());
                map.put("YearAndMonth",wechatFollowersEntity.getYearAndMonth());
                list.add(map);
            }
        }
        ee.addSheetByMap("Wechat Followers Growth", list, header);
        ee.export(response);
        return R.ok();
    }



    /**
     * 品牌销售数量
     * @param startDate
     * @param endDate
     * @return
     */
    @RequestMapping("/salesByCategory")
    public R salesByCategory(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate){
        if (Objects.isNull(startDate) || "".equals(startDate)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endDate)  || "".equals(endDate)){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }

        return R.ok().put("list",monthlyCustomersService.salesByCategory(startDate,endDate,merchSn));
    }

    /**
     * 每月品牌销售数量查询
     * @param startMonth
     * @param endMonth
     * @return
     */
    @RequestMapping("/monthlyCategory")
    public R monthlyCategory(@RequestParam("startMonth") String startMonth, @RequestParam("endMonth") String endMonth) {
        if (Objects.isNull(startMonth) || "".equals(startMonth)){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(endMonth)  || "".equals(endMonth)){
            return R.error("请先选择结束时间");
        }
        List<String> dateList = new ArrayList<>();
        Map<String, Object> returnMap = new HashMap<>();
        try {
            calculateDifferentMonth(dateList, startMonth, endMonth);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        try {
            String merchSn = null;
            SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
            if(!"1".equals(sysUser.getRoleType())){
                merchSn = sysUser.getMerchSn();
            }
            Map<String,Object> map = monthlyCustomersService.monthlyCategory(startMonth,endMonth,merchSn,dateList);

            returnMap.put("dateList", dateList);
            returnMap.putAll(map);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error(e.getMessage());
        }
        return R.ok(returnMap);
    }


    @RequestMapping("/monthlyCategoryExport")
    public R monthlyCategoryExport(@RequestParam Map<String, Object> params, HttpServletResponse response, HttpServletRequest request) {
        ParamUtils.setQueryPowerByRoleType(params, "storeId", "merchSn", "thirdPartyMerchCode");
        if (Objects.isNull(params.get("startMonth"))  || "".equals(params.get("startMonth"))){
            return R.error("请先选择开始时间");
        }
        if (Objects.isNull(params.get("endMonth"))  || "".equals(params.get("endMonth"))){
            return R.error("请先选择结束时间");
        }
        String merchSn = null;
        SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
        if(!"1".equals(sysUser.getRoleType())){
            merchSn = sysUser.getMerchSn();
        }
        List<MonthlySalesGrowthEntity> monthlySalesGrowthEntities = monthlyCustomersDao.monthlyCategory((String) params.get("startMonth"), (String) params.get("endMonth"), merchSn);
        ExcelExport ee = new ExcelExport("品牌销售量比较");
        String[] header = new String[]{"序号","销售数量","品牌名","销售额","年-月"};
        List<Map<String, Object>> list = new ArrayList<>();
        int count = 1;
        if (monthlySalesGrowthEntities!=null && monthlySalesGrowthEntities.size()>0) {
            for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : monthlySalesGrowthEntities) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                map.put("OrderNumber",count++);
                map.put("Sales",monthlySalesGrowthEntity.getSales());
                map.put("Cgname",monthlySalesGrowthEntity.getCgname());
                map.put("TotalSales",monthlySalesGrowthEntity.getTotalSales());
                map.put("YearAndMonth",monthlySalesGrowthEntity.getYearAndMonth());
                list.add(map);
            }
        }
        ee.addSheetByMap("品牌销售量比较", list, header);
        ee.export(response);
        return R.ok();
    }






    @RequestMapping("/exportSelectedMonthData")
    public void exportSelectedMonthData(@RequestParam("startMonth") String startMonth, @RequestParam("endMonth") String endMonth
    ,@RequestParam("monthNumber") Integer monthNumber
    ,HttpServletRequest request ,HttpServletResponse response) {
        List<String> dateList = new ArrayList<>();
        Map<String, Object> returnMap = new HashMap<>();
        try {
            calculateDifferentMonth(dateList, startMonth, endMonth);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        try {
            String merchSn = null;
            SysUserEntity sysUser = (SysUserEntity) SecurityUtils.getSubject().getPrincipal();
            if(!"1".equals(sysUser.getRoleType())){
                merchSn = sysUser.getMerchSn();
            }
            List<MonthlySalesGrowthEntity> monthlySalesGrowthEntityList = monthlyCustomersService.exportSelectedMonthData(startMonth,endMonth,merchSn,dateList.size(),monthNumber);
            ExcelExport ee = new ExcelExport("Wechat Followers Growth");
            String[] header = new String[]{"序号","sku","产品名称","月份","销售数","平均销售","总库存","店铺库存","园区库存","在途库存","月均预订量","月消耗预订量","统计月份","预定月份","预定数"};
            List<Map<String, Object>> list = new ArrayList<>();
            int count = 1;
            if (monthlySalesGrowthEntityList!=null && monthlySalesGrowthEntityList.size()>0) {
                for (MonthlySalesGrowthEntity monthlySalesGrowthEntity : monthlySalesGrowthEntityList) {
                    LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                    map.put("序号",count++ +"");
                    map.put("sku",monthlySalesGrowthEntity.getSku());
                    map.put("产品名称",monthlySalesGrowthEntity.getGoodsName());
                    map.put("月份",monthlySalesGrowthEntity.getSalesMonth());
                    map.put("销售数",monthlySalesGrowthEntity.getTotalNumber());
                    map.put("平均销售",monthlySalesGrowthEntity.getAvgSalesNumber());
                    map.put("总库存",monthlySalesGrowthEntity.getTotalCount());
                    map.put("店铺库存",monthlySalesGrowthEntity.getStockNumber());
                    map.put("在途库存",monthlySalesGrowthEntity.getNumber());
                    map.put("园区库存",monthlySalesGrowthEntity.getGoodsNumber());
                    map.put("月均预订量",new BigDecimal(monthlySalesGrowthEntity.getAvgSalesNumber()).multiply(
                            new BigDecimal(monthNumber)).setScale(2,BigDecimal.ROUND_HALF_UP).toString() );
                    // 总库存 -  M*单品每月平均销量
                    map.put("月消耗预订量",new BigDecimal(monthlySalesGrowthEntity.getTotalCount()).subtract(
                            new BigDecimal(dateList.size()).multiply(new BigDecimal(monthlySalesGrowthEntity.getAvgSalesNumber())).setScale(2,RoundingMode.HALF_UP)).toString());
                    map.put("统计月份",dateList.size()+"");
                    map.put("预定月份",monthNumber+"");
                    map.put("预定数",monthlySalesGrowthEntity.getPredeterminedNumber());
                    list.add(map);
                }
            }
            Map<String, List<Map<String, Object>>/*该list为每个sheet页的数据*/> map = Maps.newHashMap();
            map.put("测试合并数据", list);
            createExcel(header, map, new int[]{0,1,2},request,response);
//            ee.addSheetByMap("Wechat Followers Growth", list, header);
//            ee.export(response);
//            return R.ok();
        } catch (Exception e) {
            e.printStackTrace();
//            return R.error(e.getMessage());
        }
    }


    public String createExcel(String[] title, Map<String, List<Map<String, Object>>> maps, int[] mergeIndex
            ,HttpServletRequest request ,HttpServletResponse response){
        if (title.length==0){
            return null;
        }
        /*初始化excel模板*/
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        int n = 0;
        /*循环sheet页*/
        for(Map.Entry<String, List<Map<String/*对应title的值*/, Object>>> entry : maps.entrySet()){
            /*实例化sheet对象并且设置sheet名称，book对象*/
            try {
                sheet = workbook.createSheet();
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
            }catch (Exception e){
                e.printStackTrace();
            }
            /*初始化head，填值标题行（第一行）*/
            Row row0 = sheet.createRow(0);
            for(int i = 0; i<title.length; i++){
                /*创建单元格，指定类型*/
                Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
                cell_1.setCellValue(title[i]);
            }
            /*得到当前sheet下的数据集合*/
            List<Map<String/*对应title的值*/, Object>> list = entry.getValue();
            /*遍历该数据集合*/
            List<PoiModel> poiModels = Lists.newArrayList();
            if(null!=workbook){
                Iterator iterator = list.iterator();
                int index = 1;/*这里1是从excel的第二行开始，第一行已经塞入标题了*/
                while (iterator.hasNext()){
                    Row row = sheet.createRow(index);
                    /*取得当前这行的map，该map中以key，value的形式存着这一行值*/
                    Map<String, String> map = (Map<String, String>)iterator.next();
                    /*循环列数，给当前行塞值*/
                    for(int i = 0; i<title.length; i++){
                        String old = "";
                        /*old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记*/
                        if(index > 1){
                            old = poiModels.get(i)==null?"":poiModels.get(i).getContent();
                        }
                        /*循环需要合并的列*/
                        for(int j = 0; j < mergeIndex.length; j++){
                            if(index == 1){
                                /*记录第一行的开始行和开始列*/
                                PoiModel poiModel = new PoiModel();
                                poiModel.setOldContent(map.get(title[i]));
                                poiModel.setContent(map.get(title[i]));
                                poiModel.setRowIndex(1);
                                poiModel.setCellIndex(i);
                                poiModels.add(poiModel);
                                break;
                            }else if(i > 0 && mergeIndex[j] == i){/*这边i>0也是因为第一列已经是最前一列了，只能从第二列开始*/
                                /*当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并*/
                                /*如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))去掉就行*/
                                if(!poiModels.get(i).getContent().equals(map.get(title[i])) || poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))){
                                    /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                    CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/
                                            , poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                    if (cra.getNumberOfCells() >= 2) {
                                        //在sheet里增加合并单元格
                                        sheet.addMergedRegion(cra);
                                    }
                                    //在sheet里增加合并单元格
                                    /*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
                                    poiModels.get(i).setContent(map.get(title[i]));
                                    poiModels.get(i).setRowIndex(index);
                                    poiModels.get(i).setCellIndex(i);
                                }
                            }
                            /*处理第一列的情况*/
                            if(mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(map.get(title[i]))){
                                /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                if (cra.getNumberOfCells() >= 2) {
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                }
                                /*重新记录该列的内容为当前内容，行标记改为当前行标记*/
                                poiModels.get(i).setContent(map.get(title[i]));
                                poiModels.get(i).setRowIndex(index);
                                poiModels.get(i).setCellIndex(i);
                            }

                            /*最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容*/
                            if(mergeIndex[j] == i && index == list.size()){
                                CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                //在sheet里增加合并单元格
                                if (cra.getNumberOfCells() >= 2) {
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                }
                            }
                        }
                        Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(map.get(title[i]));
                        /*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/
                        poiModels.get(i).setOldContent(old);
                    }
                    index++;
                }
            }
            n++;
        }
        /*生成临时文件*/
        String fileName = String.valueOf(new Date().getTime()/1000);
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            //根据浏览器不同，对文件的名字进行不同的编码设置
            final String userAgent = request.getHeader("USER-AGENT");
            String finalFileName = null;
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {    //IE浏览器
                finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
            } else if (StringUtils.contains(userAgent, "Mozilla")) {   //google,火狐浏览器
                finalFileName = new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1");
            } else {
                finalFileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");   //其他浏览器
            }
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + finalFileName);
            ServletOutputStream out = response.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;

            try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    bis.close();
                }
                if (bos != null) {
                    bos.close();
                }
            }
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "导出成功";
    }



}