package cn.com.lzt.sign.controller;

import cn.com.lzt.oilconsumption.controller.TBCarGasRefuelController;
import cn.com.lzt.oilconsumption.entity.TBCarGasRep;
import cn.com.lzt.sign.entity.TBMaterialInfoEntity;
import cn.com.lzt.sign.page.Statistics;
import cn.com.lzt.sign.page.StatisticsInfo;
import com.alibaba.fastjson.JSONObject;
import com.daju.base.BaseController;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jeecgframework.core.common.hibernate.qbc.CriteriaQuery;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil;
import org.jeecgframework.web.system.service.SystemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Logger;
import java.util.stream.Collectors;

/**
 * @Description: TODO
 * @author: scott
 * @date: 2021年11月20日 13:05
 */


@Controller
@RequestMapping({"/statisticsController"})
public class StatisticsController extends BaseController {
    private static final Logger logger = Logger.getLogger(String.valueOf(StatisticsController.class));

    @Autowired
    private SystemService systemService;


    @RequestMapping(params = {"list"})
    public ModelAndView list(HttpServletRequest request) throws ParseException {
        //基础数据准备
        String nf = request.getParameter("nf");
        String yf = request.getParameter("yf");
        String dept = request.getParameter("departId");
        String tsEnd = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf1 = new SimpleDateFormat("MM");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");
        Date now = new Date();
        if(null==nf || "".equals(nf)){

            nf =sdf.format(now);

        }


        if(null!=yf && !"".equals(yf)) {

            tsEnd = nf + "-" + yf+"-"+Integer.toString(getDaysOfMonth(sdf2.parse(nf + "-" + yf)));

        }else{
            tsEnd = nf + "-12-31";
        }

        //查询科室
        String sql_wh = "SELECT\n" +
                "\tbb.material_dapt_id departId,\n" +
                "\t( SELECT departname FROM t_s_depart WHERE t_s_depart.id = bb.material_dapt_id ) departname,\n" +
                "\tsubstr(bb.out_time,1,7),\n" +
                "\t\t(\n" +
                " SELECT GROUP_CONCAT( id ) FROM t_b_material_delivery_info WHERE delivery_id in (\n" +
                "\n" +
                "SELECT\n" +
                "\t\tid\n" +
                "\tFROM\n" +
                "\t\tt_b_material_delivery aa \n" +
                "\tWHERE\n" +
                "\t\n" +
                "\t\t substr(aa.out_time,1,7) = substr(bb.out_time,1,7)\n" +
                "\t\tAND bb.material_dapt_id = aa.material_dapt_id) \n" +
                "\t) goodsmouth \n" +
                "\t\n" +
                "FROM\n" +
                "\tt_b_material_delivery bb\n" +
                "where bb.out_time >='"+nf+"-01-01' and bb.out_time<='"+tsEnd+"'" ;


        if(null!=dept && !"".equals(dept)){
            sql_wh+=" and bb.material_dapt_id = '"+dept+"'";
        }


        if(null!=yf && !"".equals(yf)){
            sql_wh+=" and substr(bb.out_time,6,2) = '"+yf+"'";
        }

        sql_wh+=" group by bb.material_dapt_id,substr(bb.out_time,1,7) order by bb.material_dapt_id,substr(bb.out_time,6,2)";





        List<Object[]> departList = systemService.findListbySql(sql_wh);

        List<Statistics> collectList = departList.stream().map(e -> {
            Statistics sta = new Statistics();
            if(e[0]!=null){
                sta.setDepartId(e[0].toString());
            }
            if(e[1]!=null){
                sta.setDepartname(e[1].toString());
            }
            if(e[2]!=null) {
                sta.setYearmonth(e[2].toString());
            }

            if(e[3]!=null){
                sta.setGoodsmonth(e[3].toString());
            }

            return sta;
        }).collect(Collectors.toList());

        Double monthZong = 0.0;
      //  Double yearZong = 0.0;
        //查询每个科室的货品价格（按货品类型）（年）


        for(int i=0;i<collectList.size();i++){

            Double yearhnum =0.0  ;
            Double monthnum =0.0  ;




            String goodsmonth = collectList.get(i).getGoodsmonth();
            if(goodsmonth!=null && !"".equals(goodsmonth)){
                String[] goodsAry = goodsmonth.split(",");
                goodsmonth ="";
                for(int j=0;j<goodsAry.length;j++){
                    if(j==0){
                        goodsmonth = "'"+goodsAry[j]+"'";
                    }else{
                        goodsmonth +=",'"+goodsAry[j]+"'";
                    }
                }
            }







            String infomouth = "select info.typename,sum(info.money) money from (\n" +
                    "SELECT\n" +
                    "\t out_num*( SELECT default_provider FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id ) money,\n" +
                    " (select typename from t_s_type where t_s_type.typecode = ( SELECT belong_category FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id )) typename\n" +
                    "FROM\n" +
                    "\t t_b_material_delivery_info \n" +
                    "WHERE\n" +
                    "\t id IN ("+goodsmonth+")  ) info GROUP BY info.typename";
            List<Object[]> infomouthList = systemService.findListbySql(infomouth);

            List<StatisticsInfo> goodsmouthList = infomouthList.stream().map(e -> {
                StatisticsInfo sta = new StatisticsInfo();
                if(e[0]!=null){
                    sta.setGoodsTypeName(e[0].toString());
                }
                if(e[1]!=null){
                    sta.setMonthMoney(e[1].toString());

                }
                return sta;
            }).collect(Collectors.toList());


            for(int k=0;k<goodsmouthList.size();k++){
                monthnum+=(Double.valueOf(goodsmouthList.get(k).getMonthMoney()));
            }
            monthZong+=monthnum;





            collectList.get(i).setYearnum(yearhnum);
            collectList.get(i).setMonthnum(monthnum);

            collectList.get(i).setStatisticsInfoList(goodsmouthList);
            collectList.get(i).setMonth(yf);
            collectList.get(i).setYear(nf);
        }






        request.setAttribute("monthZong",monthZong);

        request.setAttribute("collectList",collectList);

        return new ModelAndView("cn/com/lzt/sign/statistics/type-list");
    }




    @RequestMapping(params = {"claim"})
    public ModelAndView claim(HttpServletRequest request) throws ParseException {

        //基础数据准备
        String nf = request.getParameter("nf");
        String yf = request.getParameter("yf");
        String dept = request.getParameter("departId");

        String tsEnd = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf1 = new SimpleDateFormat("MM");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");
        if(null==nf || "".equals(nf)){
            Date now = new Date();
            nf =sdf.format(now);

        }

        if(null!=yf && !"".equals(yf)) {

            tsEnd = nf + "-" + yf+"-"+Integer.toString(getDaysOfMonth(sdf2.parse(nf + "-" + yf)));

        }else{
            tsEnd = nf + "-12-31";
        }

        //查询科室
        String sql_wh = "SELECT\n" +
                "\tbb.material_dapt_id departId,\n" +
                "\t( SELECT departname FROM t_s_depart WHERE t_s_depart.id = bb.material_dapt_id ) departname,\n" +
                "\tsubstr(bb.out_time,1,7),\n" +
                "\t\t(\n" +
                " SELECT GROUP_CONCAT( id ) FROM t_b_material_delivery_info WHERE delivery_id in (\n" +
                "\n" +
                "SELECT\n" +
                "\t\tid\n" +
                "\tFROM\n" +
                "\t\tt_b_material_delivery aa \n" +
                "\tWHERE\n" +
                "\t\n" +
                "\t\t substr(aa.out_time,1,7) = substr(bb.out_time,1,7)\n" +
                "\t\tAND bb.material_dapt_id = aa.material_dapt_id) \n" +
                "\t) goodsmouth \n" +
                "\t\n" +
                "FROM\n" +
                "\tt_b_material_delivery bb\n" +
                "where bb.out_time >='"+nf+"-01-01' and bb.out_time<='"+tsEnd+"'" ;

        if(null!=dept && !"".equals(dept)){
            sql_wh+=" and bb.material_dapt_id = '"+dept+"'";
        }


        if(null!=yf && !"".equals(yf)){
            sql_wh+=" and substr(bb.out_time,6,2) = '"+yf+"'";
        }

        sql_wh+=" group by bb.material_dapt_id,substr(bb.out_time,1,7) order by bb.material_dapt_id,substr(bb.out_time,6,2)";







        List<Object[]> departList = systemService.findListbySql(sql_wh);
        List<Statistics> collectList = departList.stream().map(e -> {
            Statistics sta = new Statistics();
            if(e[0]!=null){
                sta.setDepartId(e[0].toString());
            }
            if(e[1]!=null){
                sta.setDepartname(e[1].toString());
            }
            if(e[2]!=null) {
                sta.setYearmonth(e[2].toString());
            }

            if(e[3]!=null){
                sta.setGoodsmonth(e[3].toString());
            }

            return sta;
        }).collect(Collectors.toList());

        Double monthZong = 0.0;
      //  Double yearZong = 0.0;






        for(int i=0;i<collectList.size();i++) {
            Double yearhnum = 0.0;
            Double monthnum = 0.0;



            String goodsmonth = collectList.get(i).getGoodsmonth();
            if (goodsmonth != null && !"".equals(goodsmonth)) {
                String[] goodsAry = goodsmonth.split(",");
                goodsmonth = "";
                for (int j = 0; j < goodsAry.length; j++) {
                    if (j == 0) {
                        goodsmonth = "'" + goodsAry[j] + "'";
                    } else {
                        goodsmonth += ",'" + goodsAry[j] + "'";
                    }
                }
            }




            String infomouth = " select sum(out_num*( SELECT default_provider FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id )) money,( SELECT goods_name FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id ) goodsname FROM \n" +
                    "                     t_b_material_delivery_info  \n" +
                    "                    WHERE " +
                    "\t id IN (" + goodsmonth + ")  group by goods_id";

            List<Object[]> infomouthList = systemService.findListbySql(infomouth);

            List<StatisticsInfo> goodsmouthList = infomouthList.stream().map(e -> {
                StatisticsInfo sta = new StatisticsInfo();
                if (e[0] != null) {
                    sta.setMonthMoney(e[0].toString());
                }
                if (e[1] != null) {
                    sta.setGoodsName(e[1].toString());

                }
                return sta;
            }).collect(Collectors.toList());


            for (int k = 0; k < goodsmouthList.size(); k++) {
                monthnum += (Double.valueOf(goodsmouthList.get(k).getMonthMoney()));
            }
            monthZong += monthnum;





            collectList.get(i).setYearnum(yearhnum);
            collectList.get(i).setMonthnum(monthnum);
            collectList.get(i).setStatisticsInfoList(goodsmouthList);
            collectList.get(i).setMonth(yf);
            collectList.get(i).setYear(nf);
        }











        request.setAttribute("monthZong",monthZong);
      //  request.setAttribute("yearZong",yearZong);
        request.setAttribute("collectList",collectList);


        return new ModelAndView("cn/com/lzt/sign/statistics/claim-list");
    }


    public static int getDaysOfMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    }



    @RequestMapping(params = {"exportXls"})
    public void exportXls(String id, HttpServletRequest request, HttpServletResponse response, ModelMap map) throws IOException, ParseException {


            //基础数据准备
        String nf = request.getParameter("nfinfo");
        String yf = request.getParameter("yfinfo");
        String dept = request.getParameter("departIdinfo");

            String tsEnd = "";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            SimpleDateFormat sdf1 = new SimpleDateFormat("MM");
            SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");
            if (null == nf || "".equals(nf)) {
                Date now = new Date();
                nf = sdf.format(now);

            }

        if(null!=yf && !"".equals(yf)) {

            tsEnd = nf + "-" + yf+"-"+Integer.toString(getDaysOfMonth(sdf2.parse(nf + "-" + yf)));

        }else{
            tsEnd = nf + "-12-31";
        }

        //查询科室
        String sql_wh = "SELECT\n" +
                "\tbb.material_dapt_id departId,\n" +
                "\t( SELECT departname FROM t_s_depart WHERE t_s_depart.id = bb.material_dapt_id ) departname,\n" +
                "\tsubstr(bb.out_time,1,7),\n" +
                "\t\t(\n" +
                " SELECT GROUP_CONCAT( id ) FROM t_b_material_delivery_info WHERE delivery_id in (\n" +
                "\n" +
                "SELECT\n" +
                "\t\tid\n" +
                "\tFROM\n" +
                "\t\tt_b_material_delivery aa \n" +
                "\tWHERE\n" +
                "\t\n" +
                "\t\t substr(aa.out_time,1,7) = substr(bb.out_time,1,7)\n" +
                "\t\tAND bb.material_dapt_id = aa.material_dapt_id) \n" +
                "\t) goodsmouth \n" +
                "\t\n" +
                "FROM\n" +
                "\tt_b_material_delivery bb\n" +
                "where bb.out_time >='"+nf+"-01-01' and bb.out_time<='"+tsEnd+"'" ;


        if(null!=dept && !"".equals(dept)){
            sql_wh+=" and bb.material_dapt_id = '"+dept+"'";
        }


        if(null!=yf && !"".equals(yf)){
            sql_wh+=" and substr(bb.out_time,6,2) = '"+yf+"'";
        }

        sql_wh+=" group by bb.material_dapt_id,substr(bb.out_time,1,7) order by bb.material_dapt_id,substr(bb.out_time,6,2)";





        List<Object[]> departList = systemService.findListbySql(sql_wh);

        List<Statistics> collectList = departList.stream().map(e -> {
            Statistics sta = new Statistics();
            if(e[0]!=null){
                sta.setDepartId(e[0].toString());
            }
            if(e[1]!=null){
                sta.setDepartname(e[1].toString());
            }
            if(e[2]!=null) {
                sta.setYearmonth(e[2].toString());
            }

            if(e[3]!=null){
                sta.setGoodsmonth(e[3].toString());
            }

            return sta;
        }).collect(Collectors.toList());

        Double monthZong = 0.0;
        //  Double yearZong = 0.0;
        //查询每个科室的货品价格（按货品类型）（年）


        for(int i=0;i<collectList.size();i++){

            Double yearhnum =0.0  ;
            Double monthnum =0.0  ;




            String goodsmonth = collectList.get(i).getGoodsmonth();
            if(goodsmonth!=null && !"".equals(goodsmonth)){
                String[] goodsAry = goodsmonth.split(",");
                goodsmonth ="";
                for(int j=0;j<goodsAry.length;j++){
                    if(j==0){
                        goodsmonth = "'"+goodsAry[j]+"'";
                    }else{
                        goodsmonth +=",'"+goodsAry[j]+"'";
                    }
                }
            }







            String infomouth = "select info.typename,sum(info.money) money from (\n" +
                    "SELECT\n" +
                    "\t out_num*( SELECT default_provider FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id ) money,\n" +
                    " (select typename from t_s_type where t_s_type.typecode = ( SELECT belong_category FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id )) typename\n" +
                    "FROM\n" +
                    "\t t_b_material_delivery_info \n" +
                    "WHERE\n" +
                    "\t id IN ("+goodsmonth+")  ) info GROUP BY info.typename";
            List<Object[]> infomouthList = systemService.findListbySql(infomouth);

            List<StatisticsInfo> goodsmouthList = infomouthList.stream().map(e -> {
                StatisticsInfo sta = new StatisticsInfo();
                if(e[0]!=null){
                    sta.setGoodsTypeName(e[0].toString());
                }
                if(e[1]!=null){
                    sta.setMonthMoney(e[1].toString());

                }
                return sta;
            }).collect(Collectors.toList());


            for(int k=0;k<goodsmouthList.size();k++){
                monthnum+=(Double.valueOf(goodsmouthList.get(k).getMonthMoney()));
            }
            monthZong+=monthnum;





            collectList.get(i).setYearnum(yearhnum);
            collectList.get(i).setMonthnum(monthnum);

            collectList.get(i).setStatisticsInfoList(goodsmouthList);
            collectList.get(i).setMonth(yf);
            collectList.get(i).setYear(nf);
        }





        HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("物料耗用分类明细表");
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cell = row1.createCell(0);
            cell.setCellValue("物料耗用分类明细表");
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("序号");
            row2.createCell(1).setCellValue("月份");
            row2.createCell(2).setCellValue("货品分类名称");
            row2.createCell(3).setCellValue("累计金额");


            int l = 2;
            for (int i = 2; i < collectList.size() + 2; i++) {



                Statistics statistics = collectList.get(i - 2);
                HSSFRow row3 = sheet.createRow(l);
                l++;
                HSSFCell cel3 = row3.createCell(0);
                cel3.setCellValue(statistics.getDepartname());

                List<StatisticsInfo> statisticsInfoList = statistics.getStatisticsInfoList();

                for (int k = 0; k < statisticsInfoList.size(); k++) {
                    StatisticsInfo statisticsInfo = statisticsInfoList.get(k);
                    HSSFRow row4 = sheet.createRow(l);
                    l++;

                    row4.createCell(0).setCellValue(k + 1);
                    row4.createCell(1).setCellValue(statistics.getYearmonth());
                    row4.createCell(2).setCellValue(statisticsInfo.getGoodsTypeName());
                    row4.createCell(3).setCellValue(statisticsInfo.getMonthMoney());
                 //   row4.createCell(4).setCellValue(statisticsInfo.getYearMoney());

                }


                HSSFRow row5 = sheet.createRow(l);
                l++;
                row5.createCell(0).setCellValue("");
                row5.createCell(1).setCellValue("");
                row5.createCell(2).setCellValue("");
                row5.createCell(3).setCellValue("小计："+statistics.getMonthnum());
              //  row5.createCell(4).setCellValue(statistics.getYearnum());

            }

            HSSFRow row6 = sheet.createRow(l);
            row6.createCell(0).setCellValue("年度累计金额");
            row6.createCell(1).setCellValue("");
            row6.createCell(2).setCellValue("");
            row6.createCell(3).setCellValue(""+monthZong);
           // row6.createCell(4).setCellValue("小计："+yearZong);

            ServletOutputStream servletOutputStream = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("物料耗用分类明细表.xls"));
            response.setContentType("application/octet-stream");

            wb.write((OutputStream) servletOutputStream);
            servletOutputStream.close();
        }




    @RequestMapping(params = {"exportXlsInfo"})
    public void exportXlsInfo(String id, HttpServletRequest request, HttpServletResponse response, ModelMap map) throws IOException, ParseException {


        //基础数据准备
        String nf = request.getParameter("nfinfo");
        String yf = request.getParameter("yfinfo");
        String dept = request.getParameter("departIdinfo");

        String tsEnd = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf1 = new SimpleDateFormat("MM");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");
        if(null==nf || "".equals(nf)){
            Date now = new Date();
            nf =sdf.format(now);

        }

        if(null!=yf && !"".equals(yf)) {

            tsEnd = nf + "-" + yf+"-"+Integer.toString(getDaysOfMonth(sdf2.parse(nf + "-" + yf)));

        }else{
            tsEnd = nf + "-12-31";
        }

        //查询科室
        String sql_wh = "SELECT\n" +
                "\tbb.material_dapt_id departId,\n" +
                "\t( SELECT departname FROM t_s_depart WHERE t_s_depart.id = bb.material_dapt_id ) departname,\n" +
                "\tsubstr(bb.out_time,1,7),\n" +
                "\t\t(\n" +
                " SELECT GROUP_CONCAT( id ) FROM t_b_material_delivery_info WHERE delivery_id in (\n" +
                "\n" +
                "SELECT\n" +
                "\t\tid\n" +
                "\tFROM\n" +
                "\t\tt_b_material_delivery aa \n" +
                "\tWHERE\n" +
                "\t\n" +
                "\t\t substr(aa.out_time,1,7) = substr(bb.out_time,1,7)\n" +
                "\t\tAND bb.material_dapt_id = aa.material_dapt_id) \n" +
                "\t) goodsmouth \n" +
                "\t\n" +
                "FROM\n" +
                "\tt_b_material_delivery bb\n" +
                "where bb.out_time >='"+nf+"-01-01' and bb.out_time<='"+tsEnd+"'" ;

        if(null!=dept && !"".equals(dept)){
            sql_wh+=" and bb.material_dapt_id = '"+dept+"'";
        }


        if(null!=yf && !"".equals(yf)){
            sql_wh+=" and substr(bb.out_time,6,2) = '"+yf+"'";
        }

        sql_wh+=" group by bb.material_dapt_id,substr(bb.out_time,1,7) order by bb.material_dapt_id,substr(bb.out_time,6,2)";







        List<Object[]> departList = systemService.findListbySql(sql_wh);
        List<Statistics> collectList = departList.stream().map(e -> {
            Statistics sta = new Statistics();
            if(e[0]!=null){
                sta.setDepartId(e[0].toString());
            }
            if(e[1]!=null){
                sta.setDepartname(e[1].toString());
            }
            if(e[2]!=null) {
                sta.setYearmonth(e[2].toString());
            }

            if(e[3]!=null){
                sta.setGoodsmonth(e[3].toString());
            }

            return sta;
        }).collect(Collectors.toList());

        Double monthZong = 0.0;
        //  Double yearZong = 0.0;






        for(int i=0;i<collectList.size();i++) {
            Double yearhnum = 0.0;
            Double monthnum = 0.0;



            String goodsmonth = collectList.get(i).getGoodsmonth();
            if (goodsmonth != null && !"".equals(goodsmonth)) {
                String[] goodsAry = goodsmonth.split(",");
                goodsmonth = "";
                for (int j = 0; j < goodsAry.length; j++) {
                    if (j == 0) {
                        goodsmonth = "'" + goodsAry[j] + "'";
                    } else {
                        goodsmonth += ",'" + goodsAry[j] + "'";
                    }
                }
            }




            String infomouth = " select sum(out_num*( SELECT default_provider FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id )) money,( SELECT goods_name FROM t_b_goods_info WHERE t_b_goods_info.id = t_b_material_delivery_info.goods_id ) goodsname FROM \n" +
                    "                     t_b_material_delivery_info  \n" +
                    "                    WHERE " +
                    "\t id IN (" + goodsmonth + ")  group by goods_id";

            List<Object[]> infomouthList = systemService.findListbySql(infomouth);

            List<StatisticsInfo> goodsmouthList = infomouthList.stream().map(e -> {
                StatisticsInfo sta = new StatisticsInfo();
                if (e[0] != null) {
                    sta.setMonthMoney(e[0].toString());
                }
                if (e[1] != null) {
                    sta.setGoodsName(e[1].toString());

                }
                return sta;
            }).collect(Collectors.toList());


            for (int k = 0; k < goodsmouthList.size(); k++) {
                monthnum += (Double.valueOf(goodsmouthList.get(k).getMonthMoney()));
            }
            monthZong += monthnum;





            collectList.get(i).setYearnum(yearhnum);
            collectList.get(i).setMonthnum(monthnum);
            collectList.get(i).setStatisticsInfoList(goodsmouthList);
            collectList.get(i).setMonth(yf);
            collectList.get(i).setYear(nf);
        }












        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("物料领用明細表");
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell = row1.createCell(0);
        cell.setCellValue("物料领用明細表");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        HSSFRow row2 = sheet.createRow(1);
        row2.createCell(0).setCellValue("序号");
        row2.createCell(1).setCellValue("月份");
        row2.createCell(2).setCellValue("货品名称");
        row2.createCell(3).setCellValue("累计金额");
      //  row2.createCell(4).setCellValue("年度累计金额");

        int l = 2;
        for (int i = 2; i < collectList.size() + 2; i++) {
            Statistics statistics = collectList.get(i - 2);
            HSSFRow row3 = sheet.createRow(l);
            l++;
            HSSFCell cel3 = row3.createCell(0);
            cel3.setCellValue(statistics.getDepartname());

            List<StatisticsInfo> statisticsInfoList = statistics.getStatisticsInfoList();

            for (int k = 0; k < statisticsInfoList.size(); k++) {
                StatisticsInfo statisticsInfo = statisticsInfoList.get(k);
                HSSFRow row4 = sheet.createRow(l);
                l++;

                row4.createCell(0).setCellValue(k + 1);
                row4.createCell(1).setCellValue(statistics.getYearmonth());
                row4.createCell(2).setCellValue(statisticsInfo.getGoodsName());
                row4.createCell(3).setCellValue(statisticsInfo.getMonthMoney());
           //     row4.createCell(4).setCellValue(statisticsInfo.getYearMoney());

            }


            HSSFRow row5 = sheet.createRow(l);
            l++;
            row5.createCell(0).setCellValue("");
            row5.createCell(1).setCellValue("");
            row5.createCell(2).setCellValue("");
            row5.createCell(3).setCellValue("小计："+statistics.getMonthnum());
          //  row5.createCell(4).setCellValue("小计："+statistics.getYearnum());

        }

        HSSFRow row6 = sheet.createRow(l);
        row6.createCell(0).setCellValue("年度累计金额");
        row6.createCell(1).setCellValue("");
        row6.createCell(2).setCellValue("");
        row6.createCell(3).setCellValue(monthZong);
      //  row6.createCell(4).setCellValue(yearZong);

        ServletOutputStream servletOutputStream = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode("物料领用明细表.xls"));
        response.setContentType("application/octet-stream");

        wb.write((OutputStream) servletOutputStream);
        servletOutputStream.close();
    }








}