package com.rongwei.bscommon.sys.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.rongwei.bsentity.domain.EquEnergyMeterDosageDayDo;
import com.rongwei.bsentity.domain.EquEnergyRecordDo;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface EquEnergyRecordDao extends BaseMapper<EquEnergyRecordDo> {


    @Select("SELECT\n" +
            "\ta.METERID,\n" +
            "\ta.OWNEDFACTORY,\n" +
            "\ta.TENANTID,\n" +
            "\tDATE_FORMAT( a.CBDATE, '%Y-%m-%d' ) 'CENTERDAY',\n" +
            "CASE\n" +
            "\t\t\n" +
            "\t\tWHEN a.VALUEZ IS NOT NULL \n" +
            "\t\tAND b.a.VALUEZ IS NOT NULL THEN\n" +
            "\t\tc.DBBB *(b.VALUEZ - a.VALUEZ)\t ELSE 0 \n" +
            "\t\tEND 'DOSAGE' \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tMETERID,\n" +
            "\t\tVALUEZ,\n" +
            "\t\tTENANTID,\n" +
            "\t\tOWNEDFACTORY,\n" +
            "\t\tCBDATE,\n" +
            "\t\tROW_NUMBER() OVER ( PARTITION BY METERID, DATE_FORMAT( CBDATE, '%Y-%m-%d' ) ORDER BY CBDATE DESC ) AS 'rank' \n" +
            "\tFROM\n" +
            "\t\tequ_energy_record \n" +
            "\tWHERE\n" +
            "\t\tCBDATE <= #{endTime} AND CBDATE >= #{startTime} \n" +
            "\t\tAND DELETED = '0' \n" +
            "\t) a\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tMETERID,\n" +
            "\t\tVALUEZ,\n" +
            "\t\tCBDATE,\n" +
            "\t\tROW_NUMBER() OVER ( PARTITION BY METERID, DATE_FORMAT( CBDATE, '%Y-%m-%d' ) ORDER BY CBDATE DESC ) AS 'rank' \n" +
            "\tFROM\n" +
            "\t\tequ_energy_record \n" +
            "\tWHERE\n" +
            "\t\tCBDATE <= #{endTime}  AND CBDATE >= #{startTime} \n" +
            "\t\tAND DELETED = '0' \n" +
            "\t) b ON a.METERID = b.METERID \n" +
            "\tAND b.rank = '1' \n" +
            "\tAND DATE_FORMAT( a.CBDATE, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( b.CBDATE, INTERVAL 1 DAY ), '%Y-%m-%d' ) \n" +
            "\tLEFT JOIN equ_energy_meter c ON c.ID =a.METERID and c.DELETED ='0'\n" +
            "WHERE\n" +
            "\ta.rank = '1'")
    List<EquEnergyMeterDosageDayDo> calRecordDataToDosageDay(@Param("startTime") String startTime,@Param("endTime") String endTime);
}
