meidu2021/daju/target/classes/cn/com/lzt/oilconsumption/controller/TBCarUseInfoController.java

package cn.com.lzt.oilconsumption.controller;


import cn.com.lzt.common.util.DateUtil;
import cn.com.lzt.common.util.StringUtil;
import cn.com.lzt.oilconsumption.entity.*;
import cn.com.lzt.useractiviti.data.controller.ActivitiToolsController;
import com.daju.mix.vendor.service.ECarVehicleService;
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.web.system.service.SystemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
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.concurrent.atomic.AtomicReference;
import java.util.logging.Logger;
import java.util.stream.Collectors;

import static cn.com.lzt.sign.controller.StatisticsController.getDaysOfMonth;

@Controller
@RequestMapping("/tBCarUseInfoController")
public class TBCarUseInfoController extends ActivitiToolsController {
	private static final Logger logger = Logger.getLogger(String.valueOf(TBCarUseInfoController.class));

	@Autowired
	private SystemService systemService;
	@Resource
	private ECarVehicleService eCarVehicleService;

    /**
	 * 车辆耗用明细列表 页面跳转
	 * 
	 * @return
	 */
	@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 sql = "SELECT\n" +
				"\tDATE_FORMAT(d.out_time,'%Y-%m'),\n" +
				"\td.material_dapt_id,\n" +
				"\tdi.receiver_car_id,\n" +
				"\tdp.departname,\n" +
				"\tdi.goods_id,\n" +
				"\tdi.out_num,\n" +
				"\tIFNULL(ca.plate,'未知车辆'),\n" +
				"\tgi.default_provider\n" +
				"FROM\n" +
				"\tt_b_material_delivery d\n" +
				"\tLEFT JOIN t_b_material_delivery_info di ON d.id = di.delivery_id\n" +
				"\tleft join t_b_goods_info gi on di.goods_id=gi.id\n" +
				"\tleft join t_b_car ca on ca.id=di.receiver_car_id\n" +
				"\tleft join t_s_depart dp on dp.id=d.material_dapt_id where 1=1";

		String yearNumSql = "SELECT\n" +
				"\td.material_dapt_id,\n" +
				"\tdi.receiver_car_id,\n" +
				"\tsum(gi.default_provider*di.out_num)\n" +
				"FROM\n" +
				"\tt_b_material_delivery d\n" +
				"\tLEFT JOIN t_b_material_delivery_info di ON d.id = di.delivery_id\n" +
				"\tleft join t_b_goods_info gi on di.goods_id=gi.id\n" +
				"\tleft join t_s_depart dp on dp.id=d.material_dapt_id where 1=1 ";

		if (!StringUtil.isEmpty(dept)) {
			sql += " and d.material_dapt_id='" + dept + "'";
			yearNumSql += " and d.material_dapt_id='" + dept + "'";
		}
		if (!StringUtil.isEmpty(nf) && !StringUtil.isEmpty(yf)) {
			String tsStart = "01";
			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);
				yf =sdf1.format(now);
			}
			tsEnd =Integer.toString(getDaysOfMonth(sdf2.parse(nf+"-"+yf)));
			sql += " and d.out_time >='" + nf + "-" + yf + "-" + tsStart + "' and d.out_time<='" + nf + "-" + yf + "-" + tsEnd + "'";
			yearNumSql += " and d.out_time >='" + nf + "-01-" + tsStart + "' and d.out_time<='" + nf + "-" + yf + "-" + tsEnd + "'";
		}

		List<Object[]> objects = systemService.findListbySql(sql);
        List<Object[]> years = systemService.findListbySql(yearNumSql + "GROUP BY d.material_dapt_id,di.receiver_car_id");
		List<TBCarYearNumRep> yearList = new ArrayList<>();
		if(years != null && years.size()>0){
			yearList=years.stream().map(e->{
				TBCarYearNumRep tbCarYearNumRep = new TBCarYearNumRep();
				tbCarYearNumRep.setDaptId(e[0].toString());
				tbCarYearNumRep.setCarId(e[1].toString());
				tbCarYearNumRep.setYearNum(e[2].toString());
				return tbCarYearNumRep;
			}).collect(Collectors.toList());
		}
		List<TBCarUseInfoRep> repList = new ArrayList<>();
		if(objects != null && objects.size() > 0){
			for (Object[] object : objects) {
				TBCarUseInfoRep rep = new TBCarUseInfoRep();
				rep.setOutTime(object[0].toString());
				rep.setMaterialDaptId(object[1].toString());
				rep.setReceiverCarId(object[2].toString());
				rep.setDepartName(object[3].toString());
				rep.setGoodsId(object[4].toString());
				rep.setOutNum(object[5].toString());
				rep.setCarName(object[6].toString());
				rep.setGoodsMoney(Optional.ofNullable(object[7]).map(Object::toString).orElse("0"));
				repList.add(rep);
			}
		}
		List<TBCarUseInfoCollectRep> result = new ArrayList<>();
		Map<String, List<TBCarYearNumRep>> collect1 = yearList.stream().collect(Collectors.groupingBy(TBCarYearNumRep::getDaptId));
		Map<String, List<TBCarUseInfoRep>> collect = repList.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getDepartName));
		if(collect != null && collect.size() > 0){
			collect.forEach((name,list)->{
				List<TBCarYearNumRep> daptYearNum = collect1.get(list.get(0).getMaterialDaptId());
				List<TBCarUseRep> carInfos = new ArrayList<>();
				Map<String, List<TBCarUseInfoRep>> carInfo = list.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getReceiverCarId));
				Map<String, String> carYearNum = daptYearNum.stream().collect(Collectors.toMap(TBCarYearNumRep::getCarId,TBCarYearNumRep::getYearNum));
				carInfo.forEach((carId,carDatas)->{
					Map<String, List<TBCarUseInfoRep>> dateData = carDatas.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getOutTime));
					String yearNum = carYearNum.get(carId);
					dateData.forEach((month,datas)->{
						double monthNum = datas.stream().mapToDouble(e -> Double.parseDouble(e.getOutNum()) * Double.parseDouble(e.getGoodsMoney())).sum();
						TBCarUseRep rep = new TBCarUseRep();
						rep.setCarId(carDatas.get(0).getCarName());
						rep.setMonth(month);
						rep.setMonthNum(Double.toString(monthNum));
						rep.setYearNum(yearNum);
						carInfos.add(rep);
					});
				});
				TBCarUseInfoCollectRep collectRep = new TBCarUseInfoCollectRep();
				collectRep.setDepartName(name);
				collectRep.setDepartId(list.get(0).getMaterialDaptId());
				collectRep.setList(carInfos);
				result.add(collectRep);
			});
		}
		Map<String,Object> monthMap = TBCarUseInfoCollectRep(result,collect1);
		request.setAttribute("monthZong",monthMap.get("allMonth"));
		request.setAttribute("yearZong",monthMap.get("allYear"));
		request.setAttribute("collectList",result);
		return new ModelAndView("cn/com/lzt/sign/tbCarGasConsumption/tBCarUseInfoList");
	}
	public Map<String,Object> TBCarUseInfoCollectRep(List<TBCarUseInfoCollectRep> result,Map<String, List<TBCarYearNumRep>> collect1){
		Map<String,Object> monthMap = new HashMap<>();
		double allMonth = 0.00d;
		double allYear = 0.00d;
		if(result.size()>0){
			for (TBCarUseInfoCollectRep collectRep : result) {
				double monthNum = collectRep.getList().stream().mapToDouble(e -> Double.parseDouble(e.getMonthNum())).sum();
				double yearNum = collect1.get(collectRep.getDepartId()).stream().mapToDouble(e -> Double.parseDouble(e.getYearNum())).sum();
				collectRep.setMonthNum(Double.toString(monthNum));
				collectRep.setYearNum(Double.toString(yearNum));
				allMonth += monthNum;
				allYear += yearNum;
			}
		}
		monthMap.put("allMonth",allMonth);
		monthMap.put("allYear",allYear);
		return monthMap;
	}
	@RequestMapping(params = {"exportXls"})
	public void export(HttpServletRequest request, HttpServletResponse response) throws ParseException, IOException {

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

		String sql = "SELECT\n" +
				"\tDATE_FORMAT(d.out_time,'%Y-%m'),\n" +
				"\td.material_dapt_id,\n" +
				"\tdi.receiver_car_id,\n" +
				"\tdp.departname,\n" +
				"\tdi.goods_id,\n" +
				"\tdi.out_num,\n" +
				"\tca.plate,\n" +
				"\tgi.default_provider\n" +
				"FROM\n" +
				"\tt_b_material_delivery d\n" +
				"\tLEFT JOIN t_b_material_delivery_info di ON d.id = di.delivery_id\n" +
				"\tleft join t_b_goods_info gi on di.goods_id=gi.id\n" +
				"\tleft join t_b_car ca on ca.id=di.receiver_car_id\n" +
				"\tleft join t_s_depart dp on dp.id=d.material_dapt_id where 1=1";

		String yearNumSql = "SELECT\n" +
				"\td.material_dapt_id,\n" +
				"\tdi.receiver_car_id,\n" +
				"\tsum(gi.default_provider*di.out_num)\n" +
				"FROM\n" +
				"\tt_b_material_delivery d\n" +
				"\tLEFT JOIN t_b_material_delivery_info di ON d.id = di.delivery_id\n" +
				"\tleft join t_b_goods_info gi on di.goods_id=gi.id\n" +
				"\tleft join t_s_depart dp on dp.id=d.material_dapt_id where 1=1 ";

		if (!StringUtil.isEmpty(dept)) {
			sql += " and d.material_dapt_id='" + dept + "'";
			yearNumSql += " and d.material_dapt_id='" + dept + "'";
		}
		if (!StringUtil.isEmpty(nf) && !StringUtil.isEmpty(yf)) {
			String tsStart = "01";
			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);
				yf =sdf1.format(now);
			}
			tsEnd =Integer.toString(getDaysOfMonth(sdf2.parse(nf+"-"+yf)));
			sql += " and d.out_time >='" + nf + "-" + yf + "-" + tsStart + "' and d.out_time<='" + nf + "-" + yf + "-" + tsEnd + "'";
			yearNumSql += " and d.out_time >='" + nf + "-01-" + tsStart + "' and d.out_time<='" + nf + "-" + yf + "-" + tsEnd + "'";
		}

		List<Object[]> objects = systemService.findListbySql(sql);
        List<Object[]> years = systemService.findListbySql(yearNumSql + "GROUP BY d.material_dapt_id,di.receiver_car_id");		List<TBCarYearNumRep> yearList=years.stream().map(e->{
			TBCarYearNumRep tbCarYearNumRep = new TBCarYearNumRep();
			tbCarYearNumRep.setDaptId(e[0].toString());
			tbCarYearNumRep.setCarId(e[1].toString());
			tbCarYearNumRep.setYearNum(e[2].toString());
			return tbCarYearNumRep;
		}).collect(Collectors.toList());

		List<TBCarUseInfoRep> repList = new ArrayList<>();
		for (Object[] object : objects) {
			TBCarUseInfoRep rep = new TBCarUseInfoRep();
			rep.setOutTime(object[0].toString());
			rep.setMaterialDaptId(object[1].toString());
			rep.setReceiverCarId(object[2].toString());
			rep.setDepartName(object[3].toString());
			rep.setGoodsId(object[4].toString());
			rep.setOutNum(object[5].toString());
			rep.setCarName(object[6].toString());
			rep.setGoodsMoney(Optional.ofNullable(object[7]).map(Object::toString).orElse("0"));
			repList.add(rep);
		}
		List<TBCarUseInfoCollectRep> result = new ArrayList<>();

		Map<String, List<TBCarYearNumRep>> collect1 = yearList.stream().collect(Collectors.groupingBy(TBCarYearNumRep::getDaptId));
		Map<String, List<TBCarUseInfoRep>> collect = repList.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getDepartName));
		collect.forEach((name,list)->{
			List<TBCarYearNumRep> daptYearNum = collect1.get(list.get(0).getMaterialDaptId());
			List<TBCarUseRep> carInfos = new ArrayList<>();
			Map<String, List<TBCarUseInfoRep>> carInfo = list.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getReceiverCarId));
			Map<String, String> carYearNum = daptYearNum.stream().collect(Collectors.toMap(TBCarYearNumRep::getCarId,TBCarYearNumRep::getYearNum));
			carInfo.forEach((carId,carDatas)->{
				Map<String, List<TBCarUseInfoRep>> dateData = carDatas.stream().collect(Collectors.groupingBy(TBCarUseInfoRep::getOutTime));
				String yearNum = carYearNum.get(carId);
				dateData.forEach((month,datas)->{
					double monthNum = datas.stream().mapToDouble(e -> Double.parseDouble(e.getOutNum()) * Double.parseDouble(e.getGoodsMoney())).sum();
					TBCarUseRep rep = new TBCarUseRep();
					rep.setCarId(carDatas.get(0).getCarName());
					rep.setMonth(month);
					rep.setMonthNum(Double.toString(monthNum));
					rep.setYearNum(yearNum);
					carInfos.add(rep);
				});
			});
			TBCarUseInfoCollectRep collectRep = new TBCarUseInfoCollectRep();
			collectRep.setDepartName(name);
			collectRep.setDepartId(list.get(0).getMaterialDaptId());
			collectRep.setList(carInfos);
			result.add(collectRep);
		});
		Map<String,Object> monthMap = TBCarUseInfoCollectRep(result,collect1);
		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 < result.size() + 2; i++) {
			TBCarUseInfoCollectRep rep = result.get(i - 2);
			HSSFRow row3 = sheet.createRow(l);
			l++;
			HSSFCell cel3 = row3.createCell(0);
			cel3.setCellValue(rep.getDepartName());

			List<TBCarUseRep> list = rep.getList();

			for (int k = 0; k < list.size(); k++) {
				TBCarUseRep tbCarUseRep = list.get(k);
				HSSFRow row4 = sheet.createRow(l);
				l++;

				row4.createCell(0).setCellValue(k + 1);
				row4.createCell(1).setCellValue(tbCarUseRep.getMonth());
				row4.createCell(2).setCellValue(tbCarUseRep.getCarId());
				row4.createCell(3).setCellValue(tbCarUseRep.getMonthNum());
//				row4.createCell(4).setCellValue(tbCarUseRep.getYearNum());

			}


			HSSFRow row5 = sheet.createRow(l);
			l++;
			row5.createCell(0).setCellValue("");
			row5.createCell(1).setCellValue("");
			row5.createCell(2).setCellValue("");
			row5.createCell(3).setCellValue("小计："+rep.getMonthNum());
//			row5.createCell(4).setCellValue(rep.getYearNum());

		}

		HSSFRow row6 = sheet.createRow(l);
		row6.createCell(0).setCellValue("年度累计金额");
		row6.createCell(1).setCellValue("");
		row6.createCell(2).setCellValue("");
		row6.createCell(3).setCellValue(monthMap.get("allMonth").toString());
//		row6.createCell(4).setCellValue(allYear);

		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();
	}
}