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

package cn.com.lzt.oilconsumption.controller;


import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.com.lzt.common.util.DateUtil;
import cn.com.lzt.common.util.StringUtil;
import cn.com.lzt.oilconsumption.entity.*;
import cn.com.lzt.oilconsumption.service.TBCarGasRefuelService;
import cn.com.lzt.useractiviti.data.controller.ActivitiToolsController;
import com.alibaba.fastjson.JSONObject;
import com.daju.mix.dao.entity.TBAlarm;
import com.daju.mix.entity.CarMileageInfo;
import com.daju.mix.vendor.dao.entity.ECarMil;
import com.daju.mix.vendor.service.ECarVehicleService;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
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.usermodel.CellStyle;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.service.CommonService;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
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.validation.ObjectError;
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.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
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;

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

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

    /**
     * 车辆加油管理列表 页面跳转
     *
     * @return
     */
    @RequestMapping(params = "list")
    public ModelAndView list(HttpServletRequest request) {
        return new ModelAndView("cn/com/lzt/sign/tbCarGasConsumption/tBCarGasConsumption");
    }

    /**
     * easyui AJAX请求数据
     *
     * @param request
     * @param response
     * @param dataGrid
     */

    @RequestMapping(params = "datagrid")
    public void datagrid(TBConsumptionReq tbConsumptionReq, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
        String startDateStr = request.getParameter("startDate_begin");
        String endDateStr = request.getParameter("startDate_end");
        //查询carIdsql拼装 start
        StringBuilder carIdSql = new StringBuilder("select gas.car_id,c.type,gas.car_plate from t_b_car_gas_refuel gas left join t_b_car c on gas.car_id=c.id where 1=1");
        if (!StringUtil.isEmpty(tbConsumptionReq.getCarPlate())) {
            carIdSql.append(" and gas.car_plate='").append(tbConsumptionReq.getCarPlate()).append("'");
        }
        if (!StringUtil.isEmpty(tbConsumptionReq.getCarType())) {
            carIdSql.append(" and c.type='").append(tbConsumptionReq.getCarType()).append("'");
        }
        if (!StringUtil.isEmpty(startDateStr)) {
            carIdSql.append(" and gas.refuel_date>='").append(startDateStr).append(" 00:00:00'");
        }
        if (!StringUtil.isEmpty(endDateStr)) {
            carIdSql.append(" and gas.refuel_date<='").append(endDateStr).append(" 00:00:00'");
        }
        carIdSql.append(" group by car_id ");
        //查询carIdsql拼装 end
        List<BigInteger> count = systemService.findListbySql("select count(*) from " + "(" + carIdSql + ") a");

        //查询carId列表
        String beginRow = String.valueOf((dataGrid.getPage() - 1) * dataGrid.getRows());
        carIdSql.append("").append(" limit ").append(beginRow).append(",").append(dataGrid.getRows());
        List<Object[]> carIdList = systemService.findListbySql(carIdSql.toString());

        //拼装数据
        StringBuilder getLastTwoData = new StringBuilder("select quantity,refuel_date from t_b_car_gas_refuel where car_id=? ");
        if (!StringUtil.isEmpty(startDateStr)) {
            getLastTwoData.append(" and refuel_date>='").append(startDateStr).append(" 00:00:00'");
        }
        if (!StringUtil.isEmpty(endDateStr)) {
            getLastTwoData.append(" and refuel_date<='").append(endDateStr).append(" 00:00:00'");
        }
        getLastTwoData.append(" order by refuel_date desc");
        if (StringUtil.isEmpty(startDateStr) && StringUtil.isEmpty(endDateStr)) {
            getLastTwoData.append(" limit 2");
        }
        List<TBConsumptionRep> reps = new ArrayList<>();
        carIdList.forEach(e -> {
            List<Object[]> datas = systemService.findListbySql(getLastTwoData.toString().replace("?", "'" + e[0] + "'"));
            String startDate = "", endDate = "", allConsumption = "";
            if (CollectionUtils.isNotEmpty(datas)) {
                double sum = datas.stream().mapToDouble(quantity -> Double.parseDouble(quantity[0].toString())).sum();
                allConsumption = Double.toString(sum);
                endDate = Optional.ofNullable(datas.get(0)[1]).map(Object::toString).orElse("");
                if (datas.size() > 1)
                    startDate = Optional.ofNullable(datas.get(datas.size() - 1)[1]).map(Object::toString).orElse("");
            }

            TBConsumptionRep build = build(Optional.ofNullable(e[0]).map(Object::toString).orElse(""),
                    Optional.ofNullable(e[2]).map(Object::toString).orElse(""), Optional.ofNullable(e[1]).map(Object::toString).orElse(""), startDate, endDate, allConsumption);
            reps.add(build);
        });
        dataGrid.setResults(reps);
        dataGrid.setTotal(count.get(0).intValue());
        TagUtil.datagrid(response, dataGrid);
    }

    private TBConsumptionRep build(String carId, String carPlate, String carType, String startDate, String endDate, String allConsumption) {
        BigDecimal allMile = BigDecimal.ZERO;
        List<String> days = new ArrayList<>();
        if (!StringUtil.isEmpty(startDate) && !StringUtil.isEmpty(endDate)) {
            try {
                days = DateUtil.getDays(startDate, endDate);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        } else {
            if (!StringUtil.isEmpty(startDate)) days.add(startDate);
            if (!StringUtil.isEmpty(endDate)) days.add(endDate);
        }
        for (String day : days) {
            ECarMil<CarMileageInfo> result = eCarVehicleService.request_vehicleTrip(carId, day);
            if (result.getCode() == 0) {
                String mile = result.getData().getMileage();
                allMile = allMile.add(new BigDecimal(mile));
            }
        }
        TBConsumptionRep tbConsumptionRep = new TBConsumptionRep();
        tbConsumptionRep.setCarPlate(carPlate);
        tbConsumptionRep.setCarType(carType);
        tbConsumptionRep.setStartDate(startDate);
        tbConsumptionRep.setEndDate(endDate);
        tbConsumptionRep.setMileages(allMile.toString());
        tbConsumptionRep.setAllConsumption(allConsumption);

        if (!StringUtil.isEmpty(allConsumption) && !allMile.equals(BigDecimal.ZERO)) {
            BigDecimal all = new BigDecimal(allConsumption);
            BigDecimal val = all.divide(allMile, BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(100));
            tbConsumptionRep.setConsumptionHundred(val.toString());
        }

        return tbConsumptionRep;
    }

    /**
     * 油耗管理-》车辆油耗统计
     *
     * @param request
     * @param response
     * @param dataGrid
     * @author 刘梦祥
     * @Date 2022年1月6日14:01:30
     */
    @RequestMapping(params = "newDatagrid")
    public void newDatagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
        try {
            queryToTBCarGasRefuel(request, dataGrid);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("车辆油耗统计 error:" + getClass(), e);
        }
        TagUtil.datagrid(response, dataGrid);
    }

    /**
     * 车辆油耗统计查询主方法
     *
     * @param request
     * @param dataGrid
     * @author 刘梦祥
     * @Date 2022年1月12日13:17:35
     */
    public void queryToTBCarGasRefuel(HttpServletRequest request, DataGrid dataGrid) {
        String startDateStr = request.getParameter("queryDate_begin");
        String endDateStr = request.getParameter("queryDate_end");
        String plate = request.getParameter("plate");
        String carType = request.getParameter("carType");
        String gasStation = request.getParameter("gasStation");
        String queryType = request.getParameter("queryType");
        // 默认按车辆统计
        if (StringUtils.isEmpty(queryType)) {
            queryType = "1";
        }
        Map<String, String> queryTypeMap = new HashMap<>();
        queryTypeMap.put("1", "tbcgr.car_id");
        queryTypeMap.put("2", "tbc.type");
        queryTypeMap.put("3", "tbcgr.gas_station");
        String sqlStr = "SELECT\n" +
                "\ttbcgr.car_id carId,\n" +
                "\tgroup_concat(DISTINCT tbc.plate) plate,\n" +
                "\tgroup_concat(DISTINCT tbc.type) carType,\n" +
                "\tgroup_concat(DISTINCT tbcgr.gas_station) gasStation,\n" +
                "\tMIN(tbcgr.refuel_date) refuelStartTime,\n" +
                "\tMAX(tbcgr.refuel_date) refuelEndTime,\n" +
                "\tSUM(tbcgr.quantity / 100) quantity,\n" +
                "\ttbcmd.totalMileage totalMileage,\n" +
                "\tSUM(tbcgr.quantity)/tbcmd.totalMileage baseQuantity,\n" +
                "\tSUM(tbcgr.refuel_money / 100) refuelMoney,\n" +
                "\ttbcmd.startTime startTime,\n" +
                "\ttbcmd.endTime endTime,\n" +
                "\tIF(SUM(tbcgr.quantity)/tbcmd.totalMileage > 100,'异常','正常') errStatus\n" +
                "FROM\n" +
                "\tt_b_car_gas_refuel tbcgr\n" +
                "LEFT JOIN t_b_car tbc ON tbc.id = tbcgr.car_id\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tcar_id,\n" +
                "\t\tSUM(mileage) totalMileage,\n" +
                "\t\tMIN(date) startTime,\n" +
                "\t\tMAX(update_time) endTime\n" +
                "\tFROM\n" +
                "\t\tt_b_car_mileage_day\n";
        // 封装查询时间范围条件
        if (StringUtils.isNotEmpty(startDateStr) && StringUtils.isNotEmpty(endDateStr)) {
            sqlStr += "where DATE_FORMAT(date,'%Y-%d-%m') >= \"" + startDateStr + "\" and DATE_FORMAT(update_time,'%Y-%d-%m') <= \"" + endDateStr + "\"\n";
        }
        sqlStr += "\tGROUP BY\n" +
                "\t\tcar_id\n" +
                "\tORDER BY\n" +
                "\t\tMIN(date) DESC\n" +
                ") tbcmd ON tbcmd.car_id = tbcgr.car_id\n" +
                "\twhere tbc.`enable` = \"1\"\n";
        // 封装查询时间范围条件
        if (StringUtils.isNotEmpty(startDateStr) && StringUtils.isNotEmpty(endDateStr)) {
            sqlStr += "\tand tbcgr.refuel_date BETWEEN \"" + startDateStr + "\" and \"" + endDateStr + "\"\n";
        }
        // 封装车牌号查询条件
        if (StringUtils.isNotEmpty(plate)) {
            sqlStr += "\tand tbc.plate like \"%" + plate + "%\"\n";
        }
        // 封装车辆类型查询条件
        if (StringUtils.isNotEmpty(carType)) {
            sqlStr += "\tand tbc.type = \"" + carType + "\"\n";
        }
        // 封装加油站点模糊查询条件
        if (StringUtils.isNotEmpty(gasStation)) {
            sqlStr += "\tand tbcgr.gas_station like \"%" + gasStation + "%\"\n";
        }
        sqlStr += "GROUP BY\n" +
                "\t" + queryTypeMap.get(queryType) + "\n" +
                "ORDER BY\n" +
                "\tMIN(tbcgr.refuel_date) DESC ";
        List<Map<String, Object>> totalData = systemService.findForJdbc(sqlStr);
        dataGrid.setTotal(totalData.size());
        Double totalOil = 0.00D;
        Double totalMileage = 0.00D;
        Double totalMoney = 0.00D;
        for (Map<String, Object> totalDataItem : totalData) {
            if(totalDataItem.containsKey("quantity") && totalDataItem.get("quantity") != null){
                totalOil += Double.parseDouble(String.valueOf(totalDataItem.get("quantity")));
            }
            if(totalDataItem.containsKey("totalMileage") && totalDataItem.get("totalMileage") != null){
                totalMileage += Double.parseDouble(String.valueOf(totalDataItem.get("totalMileage")));
            }
            if(totalDataItem.containsKey("refuelMoney") && totalDataItem.get("refuelMoney") != null){
                totalMoney += Double.parseDouble(String.valueOf(totalDataItem.get("refuelMoney")));
            }
        }
        Map<String, Double> totalDataMap = new HashMap<>();
        totalDataMap.put("totalOil", totalOil);
        totalDataMap.put("totalMileage", totalMileage);
        totalDataMap.put("totalMoney", totalMoney);
        totalDataMap.put("queryType", Double.parseDouble(queryType));
        dataGrid.setObj(totalDataMap);
        // 封装分页条件
        sqlStr += "LIMIT " + (dataGrid.getPage() - 1) * dataGrid.getRows() + "," + (dataGrid.getPage()) * dataGrid.getRows() + ";";
        List<Map<String, Object>> returndata = systemService.findForJdbc(sqlStr);
        dataGrid.setResults(returndata);
    }

    /**
     * 车辆油耗统计导出方法接口
     *
     * @param request
     * @param response
     * @param dataGrid
     * @param modelMap
     * @return
     */
    @RequestMapping(params = "exportXls")
    public void exportXlsByT(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid, ModelMap modelMap) {
        try {
            List<TBCarGasRefuelExcelEntity> list = new ArrayList<>();
            // 执行sql查询出所有的车辆类型字段对照
            String getCarTypeSqlStr = "select * from t_s_type where typegroupid = (select id from t_s_typegroup where typegroupcode = \"cartype\")";
            List<Map<String, Object>> carTypeList = systemService.findForJdbc(getCarTypeSqlStr);
            Map<Object, Object> carTypeMap = new HashMap<>();
            if (carTypeList.size() > 0) {
                for (Map<String, Object> carTypeListItem : carTypeList) {
                    carTypeMap.put(carTypeListItem.get("typecode"), carTypeListItem.get("typename"));
                }
            }
            queryToTBCarGasRefuel(request, dataGrid);
            List<Map<String, Object>> selectData = (List<Map<String, Object>>) dataGrid.getResults();
            if (selectData.size() > 0) {
                for (Map<String, Object> selectItem : selectData) {
                    TBCarGasRefuelExcelEntity tbCarGasRefuelExcelEntity = JSONObject.parseObject(JSONObject.toJSONString(selectItem), TBCarGasRefuelExcelEntity.class);
                    tbCarGasRefuelExcelEntity.setCarType(String.valueOf(carTypeMap.get(tbCarGasRefuelExcelEntity.getCarType())));
                    list.add(tbCarGasRefuelExcelEntity);
                }
            }

            Map<String, Object> queryType = JSONObject.parseObject(JSONObject.toJSONString(dataGrid.getObj()));
            int queryTypeI = 0;
            if (queryType.containsKey("queryType") && queryType.get("queryType") != null) {
                queryTypeI = Integer.parseInt(String.valueOf(queryType.get("queryType")));
            }
            Map<Integer, String> queryLiNameMap = new HashMap<>();
            queryLiNameMap.put(0, "未知统计模式");
            queryLiNameMap.put(1, "车牌号");
            queryLiNameMap.put(2, "车辆类型");
            queryLiNameMap.put(3, "加油点");
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("车辆油耗统计");
            HSSFRow title = sheet.createRow(0);
            title.createCell(0).setCellValue(queryLiNameMap.get(queryTypeI));
            title.createCell(1).setCellValue("加油统计开始时间");
            title.createCell(2).setCellValue("加油统计结束时间");
            title.createCell(3).setCellValue("里程统计开始时间");
            title.createCell(4).setCellValue("里程统计结束时间");
            title.createCell(5).setCellValue("加油量(L)");
            title.createCell(6).setCellValue("加油费(￥)");
            title.createCell(7).setCellValue("总里程(Km)");
            title.createCell(8).setCellValue("百公里油耗(L/100*Km)");
            title.createCell(9).setCellValue("油耗状态");

            for (int i = 1; i < list.size() + 1; i++) {
                TBCarGasRefuelExcelEntity item = list.get(i - 1);
                HSSFRow row = sheet.createRow(i);
                String setCellCalueStr = "";
                switch (queryTypeI) {
                    case 1:
                        setCellCalueStr = item.getPlate();
                        break;
                    case 2:
                        setCellCalueStr = item.getCarType();
                        break;
                    case 3:
                        setCellCalueStr = item.getGasStation();
                        break;
                    default:
                        setCellCalueStr = "未知统计模式";
                        break;
                }
                row.createCell(0).setCellValue(setCellCalueStr);
                row.createCell(1).setCellValue(toDateStrBySql(item.getRefuelStartTime()));
                row.createCell(2).setCellValue(toDateStrBySql(item.getRefuelEndTime()));
                row.createCell(3).setCellValue(toDateStrBySql(item.getStartTime()));
                row.createCell(4).setCellValue(toDateStrBySql(item.getEndTime()));
                row.createCell(5).setCellValue(item.getQuantity());
                row.createCell(6).setCellValue(item.getRefuelMoney());
                row.createCell(7).setCellValue(item.getTotalMileage());
                row.createCell(8).setCellValue(item.getBaseQuantity());
                row.createCell(9).setCellValue(item.getErrStatus());
            }
            HSSFRow totalRow = sheet.createRow(list.size()+2);
            totalRow.createCell(0).setCellValue("总计：");
            totalRow.createCell(1).setCellValue("");
            totalRow.createCell(2).setCellValue("");
            totalRow.createCell(3).setCellValue("");
            totalRow.createCell(4).setCellValue("");
            totalRow.createCell(5).setCellValue(queryType.containsKey("totalOil") && queryType.get("totalOil") != null ? String.valueOf(queryType.get("totalOil")) : "0");
            totalRow.createCell(6).setCellValue(queryType.containsKey("totalMoney") && queryType.get("totalMoney") != null ? String.valueOf(queryType.get("totalMoney")) : "0");
            totalRow.createCell(7).setCellValue(queryType.containsKey("totalMileage") && queryType.get("totalMileage") != null ? String.valueOf(queryType.get("totalMileage")) : "0");
            totalRow.createCell(8).setCellValue("");
            totalRow.createCell(9).setCellValue("");
            ServletOutputStream servletOutputStream = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("车辆油耗统计") + ".xls");
            response.setContentType("application/vnd.ms-excel");
            wb.write((OutputStream) servletOutputStream);
            servletOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 时间戳字符串转时间
     * @param sqlTime（秒）
     * @return
     */
    public String toDateStrBySql(String sqlTime){
        if(sqlTime != null){
            Long time = Long.valueOf(sqlTime);
            return new SimpleDateFormat("yyyy-MM-dd").format(new Date(time));
        }else{
            return "";
        }
    }
}