select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, m.batteryVolume*m.batteryQuantity as supplyBatteryPower, p.name as plantName, p.supplyPower, TRUNCATE(d.pvPower/p.supplyPower/100, 2) as todayHours, (d.chargePower-d.y1ChargePower) as todayChargePower, (d.dischargePower-d.y1DischargePower) as todayDischargePower, (d.chargePower-d.y1ChargePower)/m.batteryVolume*100/(d.data15/10-d.data33) as chargePowerPer, (d.dischargePower-d.y1DischargePower)/(d.chargePower-d.y1ChargePower)*100 as dischargePowerPer, p.clientOrderSeq from ( select d.*, m.chargePower as y1ChargePower, m.dischargePower as y1DischargePower from t_pmsdata_statistic_day d, t_pmsdata_statistic_day m where d.plantSeq = m.plantSeq and d.pcsIdx = m.pcsIdx and d.inputDate = #{inputDate} and m.inputDate = date_add(#{inputDate}, INTERVAL -1 DAY) and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} and m.pcsIdx = #{pcsIdx} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq and p.clientOrderSeq = #{clientOrderSeq} order by d.plantSeq desc order by todayHours desc order by todayChargePower desc order by todayDischargePower desc order by chargePowerPer desc order by dischargePowerPer desc order by dayPvPower desc order by data15 desc order by data16 desc select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, m.batteryVolume*m.batteryQuantity as supplyBatteryPower, p.name as plantName, p.supplyPower, TRUNCATE(d.pvPower/p.supplyPower/100, 2) as sumHours, (d.chargePower-d.prevChargePower) as todayChargePower, (d.dischargePower-d.prevDischargePower) as todayDischargePower, (d.chargePower-d.prevChargePower) * 10000 /m.batteryVolume*m.batteryQuantity/d.soc as chargePowerPer, (d.dischargePower-d.prevDischargePower)/(d.chargePower-d.prevChargePower)*100 as dischargePowerPer, p.clientOrderSeq from ( select d.*, m.data32, m.data33 from t_pmsdata_statistic_month d left join t_pmsdata_statistic_day m on d.plantSeq = m.plantSeq and d.pcsIdx = m.pcsIdx and m.inputDate = concat(#{inputYear}, '-', #{inputMonth}, '-01') and m.pcsIdx = #{pcsIdx} where d.inputYear = #{inputYear} and d.inputMonth = #{inputMonth} and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq and p.clientOrderSeq = #{clientOrderSeq} order by d.plantSeq desc order by sumHours desc order by dayPvPower desc order by soc desc order by soh desc select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, m.batteryVolume*m.batteryQuantity as supplyBatteryPower, p.name as plantName, p.supplyPower, TRUNCATE(d.pvPower/p.supplyPower/100, 2) as sumHours, (d.chargePower-d.prevChargePower) as todayChargePower, (d.dischargePower-d.prevDischargePower) as todayDischargePower, (d.chargePower-d.prevChargePower) * 10000 /m.batteryVolume*m.batteryQuantity/d.soc as chargePowerPer, (d.dischargePower-d.prevDischargePower)/(d.chargePower-d.prevChargePower)*100 as dischargePowerPer from ( select d.* from t_pmsdata_statistic_year d where d.inputYear = #{inputYear} and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.plantSeq desc order by sumHours desc order by pvPower desc order by soc desc order by soh desc select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d.* , s1.chargePower as y1ChargePower, s1.dischargePower as y1DischargePower from t_pmsdata_statistic_hour d left join t_pmsdata_statistic_day s1 on d.plantSeq = s1.plantSeq and d.pcsIdx = s1.pcsIdx and s1.plantSeq = #{plantSeq} and s1.pcsIdx = #{pcsIdx} and s1.inputDate = date_format(date_add(#{inputDate}, INTERVAL -1 DAY), '%Y-%m-%d') where 1=1 and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} and d.inputDate = #{inputDate} and d.inputHour = #{inputHour} and d.seq = #{seq} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.plantSeq desc, d.inputHour asc, d.pcsIdx asc order by d.plantSeq desc, d.inputHour desc, d.pcsIdx asc select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d.*, m.chargePower as y1ChargePower, m.dischargePower as y1DischargePower from t_pmsdata_statistic_day d left join t_pmsdata_statistic_month m on d.plantSeq = m.plantSeq and d.pcsIdx = m.pcsIdx and m.inputDate = concat(date_format(date_add(#{inputDate}, INTERVAL -1 MONTH), '%Y-%m'), '-01') and m.plantSeq = #{plantSeq} and m.pcsIdx = #{pcsIdx} where 1=1 and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} and d.inputDate between #{str1} and #{str2} and d.inputDate = #{inputDate} and d.inputYear = #{inputYear} and d.inputMonth = #{inputMonth} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.inputYear asc, d.inputMonth asc, d.inputDay asc, d.pcsIdx asc order by d.inputYear desc, d.inputMonth desc, d.inputDay desc, d.pcsIdx asc select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d1.*, d2.inputDate as inputDateB1 from t_pmsdata_statistic_month d1 left join t_pmsdata_statistic_month d2 on d1.plantSeq = d2.plantSeq and d1.pcsIdx = d2.pcsIdx and d1.inputDate = date_add(d2.inputDate, INTERVAL 1 MONTH) and d2.plantSeq = #{plantSeq} and d2.pcsIdx = #{pcsIdx} where 1=1 and d1.plantSeq = #{plantSeq} and d1.pcsIdx = #{pcsIdx} and d1.inputYear = #{inputYear} and d1.inputMonth = #{inputMonth} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.inputYear asc, d.inputMonth asc, d.pcsIdx asc order by d.inputYear desc, d.inputMonth desc, d.pcsIdx asc select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d1.* from t_pmsdata_statistic_year d1 where d1.inputYear = #{inputYear} and d1.plantSeq = #{plantSeq} and d1.pcsIdx = #{pcsIdx} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.inputYear asc, d.inputMonth asc, d.pcsIdx asc order by d.inputYear desc, d.inputMonth desc, d.pcsIdx asc select p.seq as plantSeq, p.plantName, p.plantStatus, p.supplyPower, p.pmsStatus, p.pcsMaker, p.pcsQuantity, d.seq, d.pcsIdx, d.timetableSeq, d.inputDate, d.inputHour, d.inputMinute, d.chargePower, d.dischargePower, d.pvPower, (p.pcsVolume * p.pcsQuantity) as supplyPcsPower, (p.batteryVolume * p.batteryQuantity) as supplyBatteryPower, d.data1, d.data2, d.data3, d.data4, d.data5, d.data6, d.data7, d.data8, d.data9, d.data10, d.data11, d.data12, d.data13, d.data14, d.data15, d.data16, d.data17, d.data18, d.data19, d.data20, d.data21, d.data22, d.data23, d.data24, d.data25, d.data26, d.data27, d.data28, d.data29, d.data30, d.data31, d.data32, d.data33, d.data51, d.data52, d.data53, d.data54, d.data55, d.data56, d.data57, d.data58, d.data59, d.data60, d.data61, d.data62, d.data63, d.data64, d.data65, d.data66, d.data67, d.data68, d.data69, d.data70, d.createDatetime, d.count from ( select p.seq, p.name as plantName, p.plantStatus, p.supplyPower, pm.pmsStatus, pm.pcsMaker, pm.pcsQuantity, pm.pcsVolume, pm.batteryQuantity, pm.batteryVolume from t_plant p, t_pms pm where p.seq = pm.plantSeq and p.plantStatus = #{plantStatus} and pm.pmsStatus = #{pmsStatus} and p.name like concat('%', #{plantName}, '%') and p.seq = #{plantSeq} ) p left join ( select * from t_pmsdata_statistic_day where pcsIdx=0 and inputDate = #{inputDate} and plantSeq = #{plantSeq} ) d on p.seq = d.plantSeq order by d.data15 desc order by d.pvPower/p.supplyPower desc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.inputdate, s.pcsIdx, max(input00) as input00, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, count(plantSeq) as total from ( select s.plantSeq, s.inputdate, s.inputHour, s.pcsIdx, (CASE WHEN inputHour = '00' THEN 1 ELSE 0 END) as input00, (CASE WHEN inputHour = '01' THEN 1 ELSE 0 END) as input01, (CASE WHEN inputHour = '02' THEN 1 ELSE 0 END) as input02, (CASE WHEN inputHour = '03' THEN 1 ELSE 0 END) as input03, (CASE WHEN inputHour = '04' THEN 1 ELSE 0 END) as input04, (CASE WHEN inputHour = '05' THEN 1 ELSE 0 END) as input05, (CASE WHEN inputHour = '06' THEN 1 ELSE 0 END) as input06, (CASE WHEN inputHour = '07' THEN 1 ELSE 0 END) as input07, (CASE WHEN inputHour = '08' THEN 1 ELSE 0 END) as input08, (CASE WHEN inputHour = '09' THEN 1 ELSE 0 END) as input09, (CASE WHEN inputHour = '10' THEN 1 ELSE 0 END) as input10, (CASE WHEN inputHour = '11' THEN 1 ELSE 0 END) as input11, (CASE WHEN inputHour = '12' THEN 1 ELSE 0 END) as input12, (CASE WHEN inputHour = '13' THEN 1 ELSE 0 END) as input13, (CASE WHEN inputHour = '14' THEN 1 ELSE 0 END) as input14, (CASE WHEN inputHour = '15' THEN 1 ELSE 0 END) as input15, (CASE WHEN inputHour = '16' THEN 1 ELSE 0 END) as input16, (CASE WHEN inputHour = '17' THEN 1 ELSE 0 END) as input17, (CASE WHEN inputHour = '18' THEN 1 ELSE 0 END) as input18, (CASE WHEN inputHour = '19' THEN 1 ELSE 0 END) as input19, (CASE WHEN inputHour = '20' THEN 1 ELSE 0 END) as input20, (CASE WHEN inputHour = '21' THEN 1 ELSE 0 END) as input21, (CASE WHEN inputHour = '22' THEN 1 ELSE 0 END) as input22, (CASE WHEN inputHour = '23' THEN 1 ELSE 0 END) as input23 from t_pmsdata_statistic_hour s where s.inputDate=#{inputDate} and s.plantSeq = #{plantSeq} and s.pcsIdx=#{pcsIdx} group by s.plantSeq, s.pcsIdx, s.inputDate, s.inputHour ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDate asc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, max(input24) as input24, max(input25) as input25, max(input26) as input26, max(input27) as input27, max(input28) as input28, max(input29) as input29, max(input30) as input30, max(input31) as input31, sum(total) as total from ( select s.plantSeq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, s.seq, (CASE WHEN inputDay = '01' THEN cnt END) as input01, (CASE WHEN inputDay = '02' THEN cnt END) as input02, (CASE WHEN inputDay = '03' THEN cnt END) as input03, (CASE WHEN inputDay = '04' THEN cnt END) as input04, (CASE WHEN inputDay = '05' THEN cnt END) as input05, (CASE WHEN inputDay = '06' THEN cnt END) as input06, (CASE WHEN inputDay = '07' THEN cnt END) as input07, (CASE WHEN inputDay = '08' THEN cnt END) as input08, (CASE WHEN inputDay = '09' THEN cnt END) as input09, (CASE WHEN inputDay = '10' THEN cnt END) as input10, (CASE WHEN inputDay = '11' THEN cnt END) as input11, (CASE WHEN inputDay = '12' THEN cnt END) as input12, (CASE WHEN inputDay = '13' THEN cnt END) as input13, (CASE WHEN inputDay = '14' THEN cnt END) as input14, (CASE WHEN inputDay = '15' THEN cnt END) as input15, (CASE WHEN inputDay = '16' THEN cnt END) as input16, (CASE WHEN inputDay = '17' THEN cnt END) as input17, (CASE WHEN inputDay = '18' THEN cnt END) as input18, (CASE WHEN inputDay = '19' THEN cnt END) as input19, (CASE WHEN inputDay = '20' THEN cnt END) as input20, (CASE WHEN inputDay = '21' THEN cnt END) as input21, (CASE WHEN inputDay = '22' THEN cnt END) as input22, (CASE WHEN inputDay = '23' THEN cnt END) as input23, (CASE WHEN inputDay = '24' THEN cnt END) as input24, (CASE WHEN inputDay = '25' THEN cnt END) as input25, (CASE WHEN inputDay = '26' THEN cnt END) as input26, (CASE WHEN inputDay = '27' THEN cnt END) as input27, (CASE WHEN inputDay = '28' THEN cnt END) as input28, (CASE WHEN inputDay = '29' THEN cnt END) as input29, (CASE WHEN inputDay = '30' THEN cnt END) as input30, (CASE WHEN inputDay = '31' THEN cnt END) as input31, sum(s.cnt) total from ( select s.plantSeq, s.pcsIdx, s.inputDate, substr(s.inputDate, 1, 4) as inputYear, substr(s.inputDate, 6, 2) as inputMonth, substr(s.inputDate, 9) as inputDay, s.seq, count(s.seq) as cnt from t_pmsdata_statistic_hour s where inputDate like concat(#{inputYear}, '-', #{inputMonth}, '-__') and pcsIdx=#{pcsIdx} group by s.plantSeq, s.inputDate order by plantSeq asc ) s group by s.plantSeq, s.inputDate ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDay asc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, max(input24) as input24, max(input25) as input25, max(input26) as input26, max(input27) as input27, max(input28) as input28, max(input29) as input29, max(input30) as input30, max(input31) as input31, count(seq) as total from ( select s.plantSeq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, s.seq, (CASE WHEN inputDay = '01' THEN 1 ELSE 0 END) as input01, (CASE WHEN inputDay = '02' THEN 1 ELSE 0 END) as input02, (CASE WHEN inputDay = '03' THEN 1 ELSE 0 END) as input03, (CASE WHEN inputDay = '04' THEN 1 ELSE 0 END) as input04, (CASE WHEN inputDay = '05' THEN 1 ELSE 0 END) as input05, (CASE WHEN inputDay = '06' THEN 1 ELSE 0 END) as input06, (CASE WHEN inputDay = '07' THEN 1 ELSE 0 END) as input07, (CASE WHEN inputDay = '08' THEN 1 ELSE 0 END) as input08, (CASE WHEN inputDay = '09' THEN 1 ELSE 0 END) as input09, (CASE WHEN inputDay = '10' THEN 1 ELSE 0 END) as input10, (CASE WHEN inputDay = '11' THEN 1 ELSE 0 END) as input11, (CASE WHEN inputDay = '12' THEN 1 ELSE 0 END) as input12, (CASE WHEN inputDay = '13' THEN 1 ELSE 0 END) as input13, (CASE WHEN inputDay = '14' THEN 1 ELSE 0 END) as input14, (CASE WHEN inputDay = '15' THEN 1 ELSE 0 END) as input15, (CASE WHEN inputDay = '16' THEN 1 ELSE 0 END) as input16, (CASE WHEN inputDay = '17' THEN 1 ELSE 0 END) as input17, (CASE WHEN inputDay = '18' THEN 1 ELSE 0 END) as input18, (CASE WHEN inputDay = '19' THEN 1 ELSE 0 END) as input19, (CASE WHEN inputDay = '20' THEN 1 ELSE 0 END) as input20, (CASE WHEN inputDay = '21' THEN 1 ELSE 0 END) as input21, (CASE WHEN inputDay = '22' THEN 1 ELSE 0 END) as input22, (CASE WHEN inputDay = '23' THEN 1 ELSE 0 END) as input23, (CASE WHEN inputDay = '24' THEN 1 ELSE 0 END) as input24, (CASE WHEN inputDay = '25' THEN 1 ELSE 0 END) as input25, (CASE WHEN inputDay = '26' THEN 1 ELSE 0 END) as input26, (CASE WHEN inputDay = '27' THEN 1 ELSE 0 END) as input27, (CASE WHEN inputDay = '28' THEN 1 ELSE 0 END) as input28, (CASE WHEN inputDay = '29' THEN 1 ELSE 0 END) as input29, (CASE WHEN inputDay = '30' THEN 1 ELSE 0 END) as input30, (CASE WHEN inputDay = '31' THEN 1 ELSE 0 END) as input31 from t_pmsdata_statistic_day s where inputYear = #{inputYear} and inputMonth=#{inputMonth} and s.plantSeq = #{plantSeq} and s.pcsIdx=#{pcsIdx} ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDay asc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.pcsIdx, s.inputYear, s.inputMonth, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, count(seq) as total from ( select s.plantSeq, s.pcsIdx, s.inputYear, s.inputMonth, s.seq, (CASE WHEN inputMonth = '01' THEN 1 ELSE 0 END) as input01, (CASE WHEN inputMonth = '02' THEN 1 ELSE 0 END) as input02, (CASE WHEN inputMonth = '03' THEN 1 ELSE 0 END) as input03, (CASE WHEN inputMonth = '04' THEN 1 ELSE 0 END) as input04, (CASE WHEN inputMonth = '05' THEN 1 ELSE 0 END) as input05, (CASE WHEN inputMonth = '06' THEN 1 ELSE 0 END) as input06, (CASE WHEN inputMonth = '07' THEN 1 ELSE 0 END) as input07, (CASE WHEN inputMonth = '08' THEN 1 ELSE 0 END) as input08, (CASE WHEN inputMonth = '09' THEN 1 ELSE 0 END) as input09, (CASE WHEN inputMonth = '10' THEN 1 ELSE 0 END) as input10, (CASE WHEN inputMonth = '11' THEN 1 ELSE 0 END) as input11, (CASE WHEN inputMonth = '12' THEN 1 ELSE 0 END) as input12 from t_pmsdata_statistic_month s where inputYear = #{inputYear} and s.plantSeq = #{plantSeq} and s.pcsIdx=#{pcsIdx} ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputMonth asc select inputHour, createDatetime, count(seq) as count from t_pmsdata_statistic_hour where inputDate=#{inputDate} and inputHour = (select max(inputHour) from t_pmsdata_statistic_hour where inputDate = #{inputDate}) and pcsIdx=0 select inputMonth, inputDay, createDatetime, count(seq) as count from t_pmsdata_statistic_day where inputDate=#{inputDate} and pcsIdx=0; select inputHour, createDatetime, count(seq) from t_pmsdata_statistic_hour where inputDate=#{inputDate} and inputHour = (select max(inputHour) from t_pmsdata_statistic_hour where inputDate = #{inputDate}) and pcsIdx=0