package com.rongwei.bscommon.sys.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.rongwei.bsentity.domain.EquEnergyMeterCenterDosageDayDo;
import com.rongwei.bsentity.domain.EquEnergyMeterCenterDosageMonthDo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface EquEnergyMeterCenterDosageMonthDao  extends BaseMapper<EquEnergyMeterCenterDosageMonthDo> {

    @Select("SELECT\n" +
            "\t\tb.ID,\n" +
            "\ta.* \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tCENTERID,\n" +
            "\t\tYEAR ( CENTERDAY ) 'CENTERYEAR',\n" +
            "\t\tMONTH ( CENTERDAY ) 'CENTERMONTH',TENANTID,OWNEDFACTORY,\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '1' THEN DOSAGE ELSE NULL END ) 'DAY1',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '2' THEN DOSAGE ELSE NULL END ) 'DAY2',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '3' THEN DOSAGE ELSE NULL END ) 'DAY3',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '4' THEN DOSAGE ELSE NULL END ) 'DAY4',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '5' THEN DOSAGE ELSE NULL END ) 'DAY5',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '6' THEN DOSAGE ELSE NULL END ) 'DAY6',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '7' THEN DOSAGE ELSE NULL END ) 'DAY7',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '8' THEN DOSAGE ELSE NULL END ) 'DAY8',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '9' THEN DOSAGE ELSE NULL END ) 'DAY9',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '10' THEN DOSAGE ELSE NULL END ) 'DAY10',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '11' THEN DOSAGE ELSE NULL END ) 'DAY11',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '12' THEN DOSAGE ELSE NULL END ) 'DAY12',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '13' THEN DOSAGE ELSE NULL END ) 'DAY13',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '14' THEN DOSAGE ELSE NULL END ) 'DAY14',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '15' THEN DOSAGE ELSE NULL END ) 'DAY15',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '16' THEN DOSAGE ELSE NULL END ) 'DAY16',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '17' THEN DOSAGE ELSE NULL END ) 'DAY17',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '18' THEN DOSAGE ELSE NULL END ) 'DAY18',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '19' THEN DOSAGE ELSE NULL END ) 'DAY19',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '20' THEN DOSAGE ELSE NULL END ) 'DAY20',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '21' THEN DOSAGE ELSE NULL END ) 'DAY21',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '22' THEN DOSAGE ELSE NULL END ) 'DAY22',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '23' THEN DOSAGE ELSE NULL END ) 'DAY23',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '24' THEN DOSAGE ELSE NULL END ) 'DAY24',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '25' THEN DOSAGE ELSE NULL END ) 'DAY25',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '26' THEN DOSAGE ELSE NULL END ) 'DAY26',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '27' THEN DOSAGE ELSE NULL END ) 'DAY27',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '28' THEN DOSAGE ELSE NULL END ) 'DAY28',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '29' THEN DOSAGE ELSE NULL END ) 'DAY29',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '30' THEN DOSAGE ELSE NULL END ) 'DAY30',\n" +
            "\t\tSUM( CASE WHEN DAY ( CENTERDAY )= '31' THEN DOSAGE ELSE NULL END ) 'DAY31' \n" +
            "\tFROM\n" +
            "\t\tequ_energy_meter_center_dosage_day \n" +
            "\tGROUP BY\n" +
            "\t\tCENTERID,TENANTID,OWNEDFACTORY,\n" +
            "\t\tYEAR ( CENTERDAY ),\n" +
            "\tMONTH ( CENTERDAY )) a\n" +
            "\tLEFT JOIN equ_energy_meter_center_dosage_month b ON a.CENTERID = b.CENTERID \n" +
            "\tAND a.CENTERYEAR = b.CENTERYEAR \n" +
            "\tAND a.CENTERMONTH = b.CENTERMONTH")
    List<EquEnergyMeterCenterDosageMonthDo> queryDaySum();
}
