lhLoggingCarNew/src/main/java/com/hb/proj/car/service/EnergySumRptService.java

package com.hb.proj.car.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.hb.proj.model.CarConsume;
import com.hb.proj.model.CarConsumeMulRptVO;
import com.hb.proj.model.ERP;
import com.hb.proj.model.MonitorRemainOil;
import com.hb.proj.model.OtherConsume;
import com.hb.proj.model.Workload;
import com.hb.proj.model.Wpg;
import com.hb.proj.model.WpgCorrect;
import com.hb.xframework.dao.core.SpringJdbcDAO;

/**
 * 用能用水统计汇总-表6(含各项目部上报的合计数据+物资站ERP数据)
 * @author hb
 *
 */

@Service
public class EnergySumRptService {

	@Autowired
	private SpringJdbcDAO  dao;
	
	/**
	 * 指定月份内车辆的用油期末期初差值
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public MonitorRemainOil carOilPeriodDiff(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder(300);
		sql.append("select sum(case when r.oil_type='柴油' then ifnull(start_volume,0) else 0 end) coil_start_volume,");
		sql.append(" sum(case when r.oil_type='汽油' then ifnull(start_volume,0) else 0 end) oil_start_volume ");
		sql.append(" from t_oil_monitor_report mr ");
		sql.append(" inner join t_car c on mr.car_id=c.car_id ");
		sql.append(" inner join t_quota_rule r on c.quota_rule_id=r.record_id ");
		sql.append(" where mr.rpt_month=? ");
		
		MonitorRemainOil   startOil=dao.queryForObject(sql.toString(), MonitorRemainOil.class, startMonth);
		
		sql.setLength(0);
		sql.append("select sum(case when r.oil_type='柴油' then ifnull(end_volume,0) else 0 end) coil_end_volume,");
		sql.append(" sum(case when r.oil_type='汽油' then ifnull(end_volume,0) else 0 end) oil_end_volume ");
		sql.append(" from t_oil_monitor_report mr ");
		sql.append(" inner join t_car c on mr.car_id=c.car_id ");
		sql.append(" inner join t_quota_rule r on c.quota_rule_id=r.record_id ");
		sql.append(" where mr.rpt_month=? ");
		
		MonitorRemainOil   endOil=dao.queryForObject(sql.toString(), MonitorRemainOil.class, endMonth);
		
		if(startOil==null){
			return endOil;
		}
		if(endOil==null){
			return startOil;
		}
		startOil.setCoilEndVolume(endOil.getCoilEndVolume());
		startOil.setOilEndVolume(endOil.getOilEndVolume());
		return startOil;
	}
	
	/**
	 * 按月统计车辆的用油期末期初差值
	 * @param year
	 * @return
	 */
	public List<MonitorRemainOil> carOilPeriodDiffMonthGrp(String year){
		StringBuilder sql=new StringBuilder(300);
		sql.append("select mr.rpt_month, (case when r.oil_type='柴油' then ifnull(start_volume,0) else 0 end) coil_start_volume,");
		sql.append(" (case when r.oil_type='汽油' then ifnull(start_volume,0) else 0 end) oil_start_volume, ");
		sql.append(" (case when r.oil_type='柴油' then ifnull(end_volume,0) else 0 end) coil_end_volume,");
		sql.append(" (case when r.oil_type='汽油' then ifnull(end_volume,0) else 0 end) oil_end_volume ");
		sql.append(" from t_oil_monitor_report mr ");
		sql.append(" inner join t_car c on mr.car_id=c.car_id ");
		sql.append(" inner join t_quota_rule r on c.quota_rule_id=r.record_id ");
		sql.append(" where substr(mr.rpt_month,1,4)=? ");
		
		return dao.queryForListPojo(sql.toString(), MonitorRemainOil.class, year);
	}
	
	/**
	 * 综合分析-万元占比报表新指标2022.7.18
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public CarConsumeMulRptVO carOilMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("select sum(check_mile) check_mile,sum(case when r.oil_type='柴油' then ifnull(travel_mile,0) else 0 end) coil_travel_mile,");
		sql.append("sum(case when r.oil_type='汽油' then ifnull(travel_mile,0) else 0 end) oil_travel_mile,");
		sql.append("sum(ifnull(oil_erp,0)+ifnull(oil_kl,0)) oil_us_liter,");
		sql.append("sum(ifnull(coil_erp,0)+ifnull(coil_kl,0)) coil_us_liter,");
		sql.append("sum(ifnull(work_cost,0)) work_us_liter,sum(ifnull(engine_cost,0)) engine_us_liter");
		sql.append(" from t_car_consume s");
		sql.append(" inner join t_car c on s.car_id=c.car_id");
		sql.append(" inner join t_quota_rule r on c.quota_rule_id=r.record_id");
		sql.append(" where DATE_FORMAT(s.rpt_date,'%Y-%m')  between ?  and ? ");
		return dao.queryForObject(sql.toString(), CarConsumeMulRptVO.class,startMonth,endMonth);
	}
	
	/**
	 * 综合分析-用能用水按月统计新指标2022.7.18
	 * @param year
	 * @return
	 */
	public List<CarConsumeMulRptVO> carOilMulRptMonthGrp(String year){
		StringBuilder sql=new StringBuilder();
		sql.append("select DATE_FORMAT(s.rpt_date,'%Y-%m') rpt_month,sum(case when r.oil_type='柴油' then ifnull(travel_mile,0) else 0 end) coil_travel_mile,");
		sql.append("sum(case when r.oil_type='汽油' then ifnull(travel_mile,0) else 0 end) oil_travel_mile,");
		sql.append("sum(ifnull(oil_erp,0)+ifnull(oil_kl,0)) oil_us_liter,");
		sql.append("sum(ifnull(coil_erp,0)+ifnull(coil_kl,0)) coil_us_liter,");
		sql.append("sum(ifnull(work_cost,0)) work_us_liter,sum(ifnull(engine_cost,0)) engine_us_liter,sum(engine_cost_kg)/1000 engine_cost_ton");
		sql.append(" from t_car_consume s");
		sql.append(" inner join t_car c on s.car_id=c.car_id");
		sql.append(" inner join t_quota_rule r on c.quota_rule_id=r.record_id");
		sql.append(" where DATE_FORMAT(s.rpt_date,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(s.rpt_date,'%Y-%m')");
		return dao.queryForListPojo(sql.toString(), CarConsumeMulRptVO.class,year);
	}
	
	/**
	 * 油能源统计
	 * @param rptYear
	 * @return
	 */
	public List<CarConsume> carOilSumRpt(String rptYear){
		List<Object> params=new ArrayList<Object>();
		StringBuilder sql=new StringBuilder();
		sql.append("select  max(s.rpt_date) rpt_date,sum(oil_real_cost)/1000 oil_real_cost,sum(coil_real_cost)/1000 coil_real_cost,sum(oil_coal)oil_coal, sum(coil_coal) coil_coal, ");
		sql.append(" sum(engine_cost_kg)/1000 engine_cost_kg,sum(oil_kl_money)/10000 oil_kl_money,sum(coil_kl_money)/10000 coil_kl_money");
		sql.append(" from t_car_consume s   ");
		sql.append(" where DATE_FORMAT(s.rpt_date,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(s.rpt_date,'%Y-%m')");
		params.add(rptYear);
		return dao.queryForListPojo(sql.toString(),CarConsume.class,params.toArray());
	}
	
	/**
	 * 按年查询按月分组,物资ERP数据
	 * @param year
	 * @return
	 */
	public List<ERP> erpMonthGrp(String year){
		StringBuilder sql=new StringBuilder();
		sql.append("select max(rpt_month) rpt_month,sum(oil_count) oil_count,sum(oil_money) oil_money,sum(coil_count) coil_count,sum(coil_money) coil_money,");
		sql.append("sum(oil_count_correct) oil_count_correct,sum(oil_money_correct) oil_money_correct,");
		sql.append("sum(coil_count_correct) coil_count_correct,sum(coil_money_correct) coil_money_correct,");
		sql.append("sum(oil_coal) oil_coal,sum(coil_coal) coil_coal,sum(oil_correct_coal) oil_correct_coal,sum(coil_correct_coal) coil_correct_coal,");
		sql.append("sum(oil_sum_coal) oil_sum_coal,sum(coil_sum_coal) coil_sum_coal,");
		sql.append("sum(oil_count_sum) oil_count_sum,sum(coil_count_sum) coil_count_sum,sum(oil_money_sum) oil_money_sum,sum(coil_money_sum) coil_money_sum ");
		sql.append(" from t_material_erp");
		sql.append(" where DATE_FORMAT(rpt_month,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(rpt_month,'%Y-%m')");
		return dao.queryForListPojo(sql.toString(), ERP.class,year);
	}
	
	/**
	 * 多月累计物资ERP数据
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public ERP erpMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("select max(rpt_month) rpt_month,sum(oil_count) oil_count,sum(oil_money) oil_money,sum(coil_count) coil_count,sum(coil_money) coil_money,");
		sql.append("sum(oil_count_correct) oil_count_correct,sum(oil_money_correct) oil_money_correct,");
		sql.append("sum(coil_count_correct) coil_count_correct,sum(coil_money_correct) coil_money_correct,");
		sql.append("sum(oil_coal) oil_coal,sum(coil_coal) coil_coal,sum(oil_correct_coal) oil_correct_coal,sum(coil_correct_coal) coil_correct_coal,");
		sql.append("sum(oil_sum_coal) oil_sum_coal,sum(coil_sum_coal) coil_sum_coal,");
		sql.append("sum(oil_count_sum) oil_count_sum,sum(coil_count_sum) coil_count_sum,sum(oil_money_sum) oil_money_sum,sum(coil_money_sum) coil_money_sum ");
		sql.append(" from t_material_erp");
		sql.append(" where DATE_FORMAT(rpt_month,'%Y-%m') between ?  and ? ");
		return dao.queryForObject(sql.toString(), ERP.class,startMonth,endMonth);
	}
	
	/**
	 * 油能源统计-根据要求从t_other_consume取（各项目部录入月累计消耗数据），不再通过车辆累加2020.9.15
	 * @param rptYear
	 * @return
	 */
	public List<OtherConsume> otherMonthGrp(String rptYear){
		List<Object> params=new ArrayList<Object>();
		StringBuilder sql=new StringBuilder();
		sql.append("select  max(s.rpt_month) rpt_month,");
		sql.append(" sum(oil_count_kg)/1000 oil_count_kg,sum(coil_count_kg)/1000 coil_count_kg,");
		sql.append(" sum(oil_erp_count_kg)/1000 oil_erp_count_kg,sum(coil_erp_count_kg)/1000 coil_erp_count_kg,");
		sql.append(" sum(oil_kl_count_kg)/1000 oil_kl_count_kg,sum(coil_kl_count_kg)/1000 coil_kl_count_kg,");
		sql.append(" sum(oil_kl_coal) oil_kl_coal,sum(coil_kl_coal) coil_kl_coal,");
		sql.append(" sum(oil_kl_money)/10000 oil_kl_money,sum(coil_kl_money)/10000 coil_kl_money");
		sql.append(" from t_other_consume s   ");
		sql.append(" where DATE_FORMAT(s.rpt_month,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(s.rpt_month,'%Y-%m')");
		params.add(rptYear);
		return dao.queryForListPojo(sql.toString(),OtherConsume.class,params.toArray());
	}
	
	/**
	 * 多月累计各项目部上报油耗数据
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public OtherConsume otherMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("select  max(s.rpt_month) rpt_month,");
		sql.append(" sum(oil_count_kg)/1000 oil_count_kg,sum(coil_count_kg)/1000 coil_count_kg,");
		sql.append(" sum(oil_erp_count_kg)/1000 oil_erp_count_kg,sum(coil_erp_count_kg)/1000 coil_erp_count_kg,");
		sql.append(" sum(oil_kl_count_kg)/1000 oil_kl_count_kg,sum(coil_kl_count_kg)/1000 coil_kl_count_kg,");
		sql.append(" sum(oil_kl_coal) oil_kl_coal,sum(coil_kl_coal) coil_kl_coal,");
		sql.append(" sum(oil_kl_money)/10000 oil_kl_money,sum(coil_kl_money)/10000 coil_kl_money");
		sql.append(" from t_other_consume s   ");
		sql.append(" where DATE_FORMAT(s.rpt_month,'%Y-%m') between ?  and ? ");
		return dao.queryForObject(sql.toString(),OtherConsume.class,startMonth,endMonth);
	}
	
	
	/**
	 * 水电气能耗-按年查询按月分组
	 * @param rptYear
	 * @return
	 */
	public List<Wpg> wpgSumRpt(String rptYear){
		StringBuilder sql=new StringBuilder();
		
		sql.append("SELECT max(rpt_month) rpt_month,sum(water_count)/10000  water_count,sum(water_cost)/10000 water_cost, ");
		sql.append(" sum(power_count)/10000  power_count,sum(power_cost)/10000 power_cost, ");
		sql.append(" sum(ngas_count)/10000  ngas_count,sum(ngas_cost)/10000 ngas_cost, ");
		sql.append(" sum(lgas_count)/1000  lgas_count,sum(lgas_cost)/10000  lgas_cost,sum(power_coal) power_coal, sum(lgas_coal) lgas_coal,sum(ngas_coal) ngas_coal");
		sql.append(" FROM t_wpg_account  w  where  DATE_FORMAT(rpt_month,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(rpt_month,'%Y-%m') ");
		return dao.queryForListPojo(sql.toString(),Wpg.class,rptYear);
	}
	
	
	/**
	 * 多月累计水电气能耗
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public Wpg wpgMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		
		sql.append("SELECT max(rpt_month) rpt_month,sum(water_count)/10000  water_count,sum(water_cost)/10000 water_cost, ");
		sql.append(" sum(power_count)/10000  power_count,sum(power_cost)/10000 power_cost, ");
		sql.append(" sum(ngas_count)/10000  ngas_count,sum(ngas_cost)/10000 ngas_cost, ");
		sql.append(" sum(lgas_count)/1000  lgas_count,sum(lgas_cost)/10000  lgas_cost,sum(power_coal) power_coal, sum(lgas_coal) lgas_coal,sum(ngas_coal) ngas_coal");
		sql.append(" FROM t_wpg_account  w  where  DATE_FORMAT(rpt_month,'%Y-%m') between ?  and ? ");
		return dao.queryForObject(sql.toString(),Wpg.class,startMonth,endMonth);
	}
	
	
	/**
	 * 水电气按年查询，按月分组
	 * @param rptYear
	 * @return
	 */
	public List<WpgCorrect> wpgCorrectSumRpt(String rptYear){
		StringBuilder sql=new StringBuilder();
		sql.append("select max(rpt_month) rpt_month,sum(water_count_correct) water_count_correct,sum(water_cost_correct) water_cost_correct,sum(power_count_correct) power_count_correct,sum(power_cost_correct) power_cost_correct,");
		sql.append("sum(ngas_count_correct) ngas_count_correct,sum(ngas_cost_correct) ngas_cost_correct,sum(lgas_count_correct) lgas_count_correct,sum(lgas_cost_correct) lgas_cost_correct,");
		sql.append("sum(power_correct_coal) power_correct_coal,sum(ngas_correct_coal) ngas_correct_coal,sum(lgas_correct_coal) lgas_correct_coal");
		sql.append(" from t_wpg_correct where  DATE_FORMAT(rpt_month,'%Y') = ?");
		sql.append(" group by DATE_FORMAT(rpt_month,'%Y-%m') ");
		return dao.queryForListPojo(sql.toString(),WpgCorrect.class,rptYear);
	}
	
	/**
	 * 多月累计水电气修正值
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public WpgCorrect wpgCorrectMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("select max(rpt_month) rpt_month,sum(water_count_correct) water_count_correct,sum(water_cost_correct) water_cost_correct,sum(power_count_correct) power_count_correct,sum(power_cost_correct) power_cost_correct,");
		sql.append("sum(ngas_count_correct) ngas_count_correct,sum(ngas_cost_correct) ngas_cost_correct,sum(lgas_count_correct) lgas_count_correct,sum(lgas_cost_correct) lgas_cost_correct,");
		sql.append("sum(power_correct_coal) power_correct_coal,sum(ngas_correct_coal) ngas_correct_coal,sum(lgas_correct_coal) lgas_correct_coal");
		sql.append(" from t_wpg_correct where  DATE_FORMAT(rpt_month,'%Y-%m')  between ?  and ? ");
		return dao.queryForObject(sql.toString(),WpgCorrect.class,startMonth,endMonth);
	}
	
	
	/**
	 * 工作量，产值统计---按年查询按月分组
	 * @param rptYear
	 * @return
	 */
	public List<Workload> workloadSumRpt(String rptYear){
		List<Object> params=new ArrayList<Object>();
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT max(rpt_month) rpt_month,sum(well_count) well_count, ");
		sql.append(" sum(industry_value) industry_value,sum(added_value) added_value ");
		sql.append(" FROM  t_workload_value w ");
		sql.append(" where DATE_FORMAT(rpt_month,'%Y') = ? ");
		sql.append(" group by DATE_FORMAT(rpt_month,'%Y-%m') ");
		params.add(rptYear); 
		return dao.queryForListPojo(sql.toString(),Workload.class,params.toArray());
	}
	
	/**
	 * 工作量，产值统计---按跨月查询按月分组
	 * @param startMonth
	 * @param endMonth
	 * @return
	 */
	public List<Workload> workloadSumRpt(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT max(rpt_month) rpt_month,sum(well_count) well_count, ");
		sql.append(" sum(industry_value) industry_value,sum(added_value) added_value ");
		sql.append(" FROM  t_workload_value w ");
		sql.append(" where DATE_FORMAT(rpt_month,'%Y-%m') between ?  and ? ");
		sql.append(" group by DATE_FORMAT(rpt_month,'%Y-%m') ");
		return dao.queryForListPojo(sql.toString(),Workload.class,startMonth,endMonth);
	}
	
	/**
	 * 工作量，产值统计---多月累计
	 * @param rptYear
	 * @return
	 */
	public Workload workloadMonthSum(String startMonth,String endMonth){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT sum(well_count) well_count,sum(builder_area) builder_area,");
		sql.append(" sum(industry_value) industry_value,sum(added_value) added_value ");
		sql.append(" FROM  t_workload_value w ");
		sql.append(" where DATE_FORMAT(rpt_month,'%Y-%m') between ?  and ?  ");
		return dao.queryForObject(sql.toString(),Workload.class,startMonth,endMonth);
	}
}