t_weather_2021 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.seq, d.plantSeq, d.pcsIdx, d.inputDate, d.inputYear, d.inputMonth, d.inputDay, d.chargePower, d.dischargePower, IFNULL(d.dayPvPower, d.pvPower) as pvPower, d.data15, d.data16, d.data32, d.data33, d.createDatetime, w.* from t_pmsdata_statistic_day d, ( select p.seq, p.name as plantName, p.supplyPower, w.inputYmd, w.weatherCode, max(w.cloud) as cloud, ( CASE avg(cloud) >= 4 WHEN true THEN round(avg(cloud)*10) WHEN false THEN 0 END ) as count from t_plant p left join w on p.weatherCode = w.weatherCode and w.inputYmd between #{str1} and #{str2} where p.seq = #{plantSeq} group by w.inputYmd ) w where d.plantSeq = w.seq and d.plantSeq = #{plantSeq} and d.inputDate = w.inputYmd and d.pcsIdx = 0 select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower, m.pcsQuantity, m.pcsQuantity * m.pcsVolume as supplyPcsPower, m.batteryMaker, m.batteryQuantity * m.batteryVolume as supplyBatteryPower from ( select d.seq, d.plantSeq, d.pcsIdx, d.inputDate, d.inputYear, d.inputMonth, d.inputDay, d.chargePower, d.dischargePower, IFNULL(d.dayPvPower, d.pvPower) as pvPower, d.data15, d.data16, d.data32, d.data33, d.createDatetime, 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 d.*, count(c.seq) as count from ( select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d.seq, d.plantSeq, d.pcsIdx, d.inputDate, d.inputYear, d.inputMonth, d.inputDay, d.chargePower, d.dischargePower, IFNULL(d.dayPvPower, d.pvPower) as pvPower, d.data15, d.data16, d.data32, d.data33, d.data34, d.data35, d.data36, d.createDatetime, 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 ) d left join t_cs c on d.plantSeq = c.plantSeq and d.inputDate = c.inputDate group by d.inputDate 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.*, count(c.seq) as count from ( select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d1.seq, d1.plantSeq, d1.pcsIdx, d1.inputDate, d1.inputYear, d1.inputMonth, d1.chargePower, d1.dischargePower, IFNULL(d1.dayPvPower, d1.pvPower) as pvPower, d1.createDatetime, d1.soc, d1.soh, d1.prevChargePower as y1ChargePower, d1.prevDischargePower as y1DischargePower, 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 ) d left join t_cs c on d.plantSeq = c.plantSeq and c.inputDate like concat(substr(d.inputDate, 1, 7), "-__") group by d.inputDate 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 select p.name as plantName, p.supplyPower, (m.pcsQuantity*m.pcsVolume) as supplyPcsPower, (m.batteryQuantity*m.batteryVolume) as supplyBatteryPower , d.* from ( select d.seq, d.plantSeq, d.inputDate, d.inputHour, max(d.pvPower) as sumPvPower, avg(d.pvPower) as avgPvPower, sum(d.chargePower) as sumChargePower, sum(d.dischargePower) as sumDischargePower, avg(d.chargePower) as avgChargePower, avg(d.dischargePower) as avgDischargePower, max(d.data15) as maxSoc, IFNULL(sum(d.input00), 0) as 'input00', IFNULL(sum(d.input01), 0) as 'input01', IFNULL(sum(d.input02), 0) as 'input02', IFNULL(sum(d.input03), 0) as 'input03', IFNULL(sum(d.input04), 0) as 'input04', IFNULL(sum(d.input05), 0) as 'input05', IFNULL(sum(d.input06), 0) as 'input06', IFNULL(sum(d.input07), 0) as 'input07', IFNULL(sum(d.input08), 0) as 'input08', IFNULL(sum(d.input09), 0) as 'input09', IFNULL(sum(d.input10), 0) as 'input10', IFNULL(sum(d.input11), 0) as 'input11', IFNULL(sum(d.input12), 0) as 'input12', IFNULL(sum(d.input13), 0) as 'input13', IFNULL(sum(d.input14), 0) as 'input14', IFNULL(sum(d.input15), 0) as 'input15', IFNULL(sum(d.input16), 0) as 'input16', IFNULL(sum(d.input17), 0) as 'input17', IFNULL(sum(d.input18), 0) as 'input18', IFNULL(sum(d.input19), 0) as 'input19', IFNULL(sum(d.input20), 0) as 'input20', IFNULL(sum(d.input21), 0) as 'input21', IFNULL(sum(d.input22), 0) as 'input22', IFNULL(sum(d.input23), 0) as 'input23' from ( select d1.seq, d1.plantSeq, d1.inputDate, d1.inputHour, d1.chargePower, d1.dischargePower, d1.pvPower/10 as pvPower, d1.data15, d1.data16, case when d1.inputHour = '00' then d1.${dataName} end as 'input00', case when d1.inputHour = '01' then d1.${dataName} end as 'input01', case when d1.inputHour = '02' then d1.${dataName} end as 'input02', case when d1.inputHour = '03' then d1.${dataName} end as 'input03', case when d1.inputHour = '04' then d1.${dataName} end as 'input04', case when d1.inputHour = '05' then d1.${dataName} end as 'input05', case when d1.inputHour = '06' then d1.${dataName} end as 'input06', case when d1.inputHour = '07' then d1.${dataName} end as 'input07', case when d1.inputHour = '08' then d1.${dataName} end as 'input08', case when d1.inputHour = '09' then d1.${dataName} end as 'input09', case when d1.inputHour = '10' then d1.${dataName} end as 'input10', case when d1.inputHour = '11' then d1.${dataName} end as 'input11', case when d1.inputHour = '12' then d1.${dataName} end as 'input12', case when d1.inputHour = '13' then d1.${dataName} end as 'input13', case when d1.inputHour = '14' then d1.${dataName} end as 'input14', case when d1.inputHour = '15' then d1.${dataName} end as 'input15', case when d1.inputHour = '16' then d1.${dataName} end as 'input16', case when d1.inputHour = '17' then d1.${dataName} end as 'input17', case when d1.inputHour = '18' then d1.${dataName} end as 'input18', case when d1.inputHour = '19' then d1.${dataName} end as 'input19', case when d1.inputHour = '20' then d1.${dataName} end as 'input20', case when d1.inputHour = '21' then d1.${dataName} end as 'input21', case when d1.inputHour = '22' then d1.${dataName} end as 'input22', case when d1.inputHour = '23' then d1.${dataName} end as 'input23' from ( select d1.seq, d1.plantSeq, d1.pcsIdx, d1.inputDate, d1.inputHour, IFNULL(d1.dayPvPower, d1.pvPower) as pvPower, d1.data15, d1.data16, (d1.chargePower - d2.chargePower) as chargePower, (d1.dischargePower - d2.dischargePower) as dischargePower from t_pmsdata_statistic_hour d1, t_pmsdata_statistic_hour d2 where d1.plantSeq = d2.plantSeq and d1.pcsIdx = d2.pcsIdx and d1.inputDate = d2.inputDate and d1.inputDate = #{inputDate} and d2.inputDate = #{inputDate} and cast(d1.inputHour as signed) = (cast(d2.inputHour as signed)+1) and d1.pcsIdx = #{pcsIdx} and d2.pcsIdx = #{pcsIdx} and d1.plantSeq = #{plantSeq} and d1.plantSeq in #{seq} ) d1 group by d1.plantSeq, d1.inputDate, d1.inputHour ) d group by plantSeq ) d, t_plant p, t_pms m where p.seq = d.plantSeq and d.plantSeq = m.plantSeq and p.plantType in ('01', '03') and p.plantStatus = #{plantStatus} and p.clientOrderSeq = #{clientOrderSeq} order by d.plantSeq desc select p.name as plantName, p.supplyPower, (m.pcsQuantity*m.pcsVolume) as supplyPcsPower, (m.batteryQuantity*m.batteryVolume) as supplyBatteryPower , d.* from ( select d.seq, d.plantSeq, d.inputDate, sum(d.pvPower) as sumPvPower, avg(d.pvPower) as avgPvPower, sum(d.chargePower) as sumChargePower, sum(d.dischargePower) as sumDischargePower, avg(d.chargePower) as avgChargePower, avg(d.dischargePower) as avgDischargePower, avg(d.data15) as avgSoc, IFNULL(sum(d.input01), 0) as 'input01', IFNULL(sum(d.input02), 0) as 'input02', IFNULL(sum(d.input03), 0) as 'input03', IFNULL(sum(d.input04), 0) as 'input04', IFNULL(sum(d.input05), 0) as 'input05', IFNULL(sum(d.input06), 0) as 'input06', IFNULL(sum(d.input07), 0) as 'input07', IFNULL(sum(d.input08), 0) as 'input08', IFNULL(sum(d.input09), 0) as 'input09', IFNULL(sum(d.input10), 0) as 'input10', IFNULL(sum(d.input11), 0) as 'input11', IFNULL(sum(d.input12), 0) as 'input12', IFNULL(sum(d.input13), 0) as 'input13', IFNULL(sum(d.input14), 0) as 'input14', IFNULL(sum(d.input15), 0) as 'input15', IFNULL(sum(d.input16), 0) as 'input16', IFNULL(sum(d.input17), 0) as 'input17', IFNULL(sum(d.input18), 0) as 'input18', IFNULL(sum(d.input19), 0) as 'input19', IFNULL(sum(d.input20), 0) as 'input20', IFNULL(sum(d.input21), 0) as 'input21', IFNULL(sum(d.input22), 0) as 'input22', IFNULL(sum(d.input23), 0) as 'input23', IFNULL(sum(d.input24), 0) as 'input24', IFNULL(sum(d.input25), 0) as 'input25', IFNULL(sum(d.input26), 0) as 'input26', IFNULL(sum(d.input27), 0) as 'input27', IFNULL(sum(d.input28), 0) as 'input28', IFNULL(sum(d.input29), 0) as 'input29', IFNULL(sum(d.input30), 0) as 'input30', IFNULL(sum(d.input31), 0) as 'input31' from ( select d1.seq, d1.plantSeq, d1.inputDate, d1.chargePower, d1.dischargePower, d1.pvPower/10 as pvPower, d1.data15, d1.data16, case when d1.inputDay = '01' then d1.${dataName} end as 'input01', case when d1.inputDay = '02' then d1.${dataName} end as 'input02', case when d1.inputDay = '03' then d1.${dataName} end as 'input03', case when d1.inputDay = '04' then d1.${dataName} end as 'input04', case when d1.inputDay = '05' then d1.${dataName} end as 'input05', case when d1.inputDay = '06' then d1.${dataName} end as 'input06', case when d1.inputDay = '07' then d1.${dataName} end as 'input07', case when d1.inputDay = '08' then d1.${dataName} end as 'input08', case when d1.inputDay = '09' then d1.${dataName} end as 'input09', case when d1.inputDay = '10' then d1.${dataName} end as 'input10', case when d1.inputDay = '11' then d1.${dataName} end as 'input11', case when d1.inputDay = '12' then d1.${dataName} end as 'input12', case when d1.inputDay = '13' then d1.${dataName} end as 'input13', case when d1.inputDay = '14' then d1.${dataName} end as 'input14', case when d1.inputDay = '15' then d1.${dataName} end as 'input15', case when d1.inputDay = '16' then d1.${dataName} end as 'input16', case when d1.inputDay = '17' then d1.${dataName} end as 'input17', case when d1.inputDay = '18' then d1.${dataName} end as 'input18', case when d1.inputDay = '19' then d1.${dataName} end as 'input19', case when d1.inputDay = '20' then d1.${dataName} end as 'input20', case when d1.inputDay = '21' then d1.${dataName} end as 'input21', case when d1.inputDay = '22' then d1.${dataName} end as 'input22', case when d1.inputDay = '23' then d1.${dataName} end as 'input23', case when d1.inputDay = '24' then d1.${dataName} end as 'input24', case when d1.inputDay = '25' then d1.${dataName} end as 'input25', case when d1.inputDay = '26' then d1.${dataName} end as 'input26', case when d1.inputDay = '27' then d1.${dataName} end as 'input27', case when d1.inputDay = '28' then d1.${dataName} end as 'input28', case when d1.inputDay = '29' then d1.${dataName} end as 'input29', case when d1.inputDay = '30' then d1.${dataName} end as 'input30', case when d1.inputDay = '31' then d1.${dataName} end as 'input31' from ( select d1.seq, d1.plantSeq, d1.pcsIdx, d1.inputDate, d1.inputDay, IFNULL(d1.dayPvPower, d1.pvPower) as pvPower, d1.data15, d1.data16, (d1.chargePower - d2.chargePower) as chargePower, (d1.dischargePower - d2.dischargePower) as dischargePower from t_pmsdata_statistic_day d1 left join t_pmsdata_statistic_day d2 on d1.plantSeq = d2.plantSeq and d1.pcsIdx = d2.pcsIdx and d1.inputDate = date_format(date_add(d2.inputDate, INTERVAL 1 DAY), '%Y-%m-%d') and d2.inputDate between #{strb1} and #{strb2} where d1.inputDate between #{str1} and #{str2} and d1.pcsIdx = #{pcsIdx} and d1.plantSeq = #{plantSeq} and d1.plantSeq in #{seq} ) d1 group by d1.plantSeq, d1.inputDate ) d group by plantSeq ) d, t_plant p, t_pms m where p.seq = d.plantSeq and d.plantSeq = m.plantSeq and p.plantType in ('01', '03') and p.plantStatus = #{plantStatus} and p.clientOrderSeq = #{clientOrderSeq} order by d.plantSeq desc select p.name as plantName, p.supplyPower, (m.pcsQuantity*m.pcsVolume) as supplyPcsPower, (m.batteryQuantity*m.batteryVolume) as supplyBatteryPower , d.* from ( select d.seq, d.plantSeq, d.inputDate, sum(d.pvPower) as sumPvPower, avg(d.pvPower) as avgPvPower, sum(d.chargePower) as sumChargePower, sum(d.dischargePower) as sumDischargePower, avg(d.chargePower) as avgChargePower, avg(d.dischargePower) as avgDischargePower, avg(d.data15) as avgSoc, IFNULL(sum(d.input01), 0) as 'input01', IFNULL(sum(d.input02), 0) as 'input02', IFNULL(sum(d.input03), 0) as 'input03', IFNULL(sum(d.input04), 0) as 'input04', IFNULL(sum(d.input05), 0) as 'input05', IFNULL(sum(d.input06), 0) as 'input06', IFNULL(sum(d.input07), 0) as 'input07', IFNULL(sum(d.input08), 0) as 'input08', IFNULL(sum(d.input09), 0) as 'input09', IFNULL(sum(d.input10), 0) as 'input10', IFNULL(sum(d.input11), 0) as 'input11', IFNULL(sum(d.input12), 0) as 'input12' from ( select d1.seq, d1.plantSeq, d1.inputDate, d1.chargePower, d1.dischargePower, d1.pvPower/10 as pvPower, d1.data15, d1.data16, case when d1.inputMonth = '01' then d1.${dataName} end as 'input01', case when d1.inputMonth = '02' then d1.${dataName} end as 'input02', case when d1.inputMonth = '03' then d1.${dataName} end as 'input03', case when d1.inputMonth = '04' then d1.${dataName} end as 'input04', case when d1.inputMonth = '05' then d1.${dataName} end as 'input05', case when d1.inputMonth = '06' then d1.${dataName} end as 'input06', case when d1.inputMonth = '07' then d1.${dataName} end as 'input07', case when d1.inputMonth = '08' then d1.${dataName} end as 'input08', case when d1.inputMonth = '09' then d1.${dataName} end as 'input09', case when d1.inputMonth = '10' then d1.${dataName} end as 'input10', case when d1.inputMonth = '11' then d1.${dataName} end as 'input11', case when d1.inputMonth = '12' then d1.${dataName} end as 'input12' from ( select d1.seq, d1.plantSeq, d1.pcsIdx, d1.inputDate, d1.inputYear, d1.inputMonth, IFNULL(d1.dayPvPower, d1.pvPower) as pvPower, d1.soc as data15 , d1.soh as data16, (d1.chargePower - d1.prevChargePower) as chargePower, (d1.dischargePower - d1.prevDischargePower) as dischargePower from t_pmsdata_statistic_month d1 where d1.inputDate between #{str1} and #{str2} and d1.inputYear = #{inputYear} and d1.pcsIdx = #{pcsIdx} and d1.plantSeq = #{plantSeq} and d1.plantSeq in #{seq} ) d1 group by d1.plantSeq, d1.inputDate ) d group by plantSeq ) d, t_plant p, t_pms m where p.seq = d.plantSeq and d.plantSeq = m.plantSeq and p.plantType in ('01', '03') and p.plantStatus = #{plantStatus} and p.clientOrderSeq = #{clientOrderSeq} order by d.plantSeq desc