[2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_gaz HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][DEBUG] : panel_price_water HCHP type_abo:HCHP [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%d%c%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%d%c%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%d%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%d%c%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%d%c%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%d%c%Y") = DATE_FORMAT(j.`rec_date`,"%d%c%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%d%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%d%c%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%d%c%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : GetTabpie id:845 Total HC:11.767999649048 Total HP:5.2699999809265 [2024-09-26 21:25:15][DEBUG] : GetTabPie Equipement:4 [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%d%c%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%d%c%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%d%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 1 DAY,"%d%c%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%d%c%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%d%c%Y") = DATE_FORMAT(j.`rec_date`,"%d%c%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%d%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 1 DAY,"%d%c%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%d%c%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : GetTabpie id:845 Total HC:26.961000442505 Total HP:4.8410000801086 [2024-09-26 21:25:15][DEBUG] : GetTabPie Equipement:4 [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%c%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%c%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%c%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%c%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%c%Y") = DATE_FORMAT(j.`rec_date`,"%c%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%c%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%c%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : GetTabpie id:845 Total HC:308.68799901009 Total HP:111.63099956512 [2024-09-26 21:25:15][DEBUG] : GetTabPie Equipement:4 [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%v%c%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%v%c%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%v%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%v%c%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%v%c%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%v%c%Y") = DATE_FORMAT(j.`rec_date`,"%v%c%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%v%c%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%v%c%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%v%c%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : GetTabpie id:845 Total HC:54.678000450134 Total HP:17.15499997139 [2024-09-26 21:25:15][DEBUG] : GetTabPie Equipement:4 [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%Y") = DATE_FORMAT(j.`rec_date`,"%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%Y") = DATE_FORMAT(CURDATE()- INTERVAL 0 DAY,"%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : GetTabpie id:845 Total HC:813.4189966619 Total HP:93452464.6025 [2024-09-26 21:25:15][DEBUG] : GetTabPie Equipement:4 [2024-09-26 21:25:15][INFO] : N° parent:845 [2024-09-26 21:25:15][DEBUG] : GetTabPie Requete:SELECT myid AS id, IF(IFNULL(istotal,0)>0 ,"Total",IF(categorie="NC",myname,categorie)) AS name_cat, isHCHP , istotal , IFNULL(SUM(shc),0) + IFNULL(SUM(shc2),0) + IFNULL(SUM(shc3),0) AS totalhc, IFNULL(SUM(shp),0) + IFNULL(SUM(shp2),0) + IFNULL(SUM(shp3),0) AS totalhp, IFNULL(ROUND(SUM(shp)+SUM(shc),2),0) +IFNULL(ROUND(SUM(shp2)+SUM(shc2),2),0) + IFNULL(ROUND(SUM(shp3)+SUM(shc3),2),0) AS total, ROUND(SUM(hcprix),2) + ROUND(SUM(hcprix2),2) + ROUND(SUM(hcprix3),2) AS prixhc, ROUND(SUM(hpprix),2) + ROUND(SUM(hpprix2),2) + ROUND(SUM(hpprix3),2) AS prixhp, ROUND(SUM(totalprix),2) AS prix, IFNULL(ROUND((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hc, IFNULL(ROUND((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS total_percent_ecq_hp, IFNULL((SUM(shc)+SUM(shc2)+SUM(shc3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hc, IFNULL((SUM(shp)+SUM(shp2)+SUM(shp3))*100/(SUM(shp)+SUM(shc)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)),0) AS percent_ecq_hp, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (case WHEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) > (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) THEN (SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3)) ELSE (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3) END),2),0) AS percent_ecq_reel, IFNULL(ROUND((SUM(shc)+SUM(shp)+SUM(shc2)+SUM(shp2)+SUM(shc3)+SUM(shp3))*100/ (res.totalhc+res.totalhp+res.totalhc2+res.totalhp2+res.totalhc3+res.totalhp3),2),0) AS percent_ecq, myid FROM ( SELECT CASE WHEN configuration like '%"visibleConsumptionLight":"1"%' THEN "Lumieres" WHEN configuration like '%"visibleConsumptionElectrical":"1"%' THEN "Electromenager" WHEN configuration like '%"visibleConsumptionAutomatism":"1"%' THEN "Automatisme" WHEN configuration like '%"visibleConsumptionHeating":"1"%' THEN "Chauffage" WHEN configuration like '%"visibleConsumptionMultimedia":"1"%' THEN "Multimedia" WHEN configuration LIKE '%"visibleConsumptionVehicules":"1"%' THEN "Véhicules" WHEN configuration LIKE '%"visibleConsumptionHardware":"1"%' THEN "Mat. Informatique" WHEN configuration LIKE '%"visibleConsumptionAirConditioner":"1"%' THEN "Climatisation" WHEN configuration LIKE '%"visibleConsumptionSwimmingPool":"1"%' THEN "Piscine" WHEN configuration LIKE '%"visibleConsumptionAutomation":"1"%' THEN "Domotique" WHEN configuration LIKE '%"visibleConsumptionOther":"1"%' THEN "Autres" ELSE "NC" END as categorie, id AS myid, IF(INSTR(configuration, '"type_abo":"HCHP"' )>0 or INSTR(configuration, '"type_abo":"TEMPO"' )>0, 1 , 0 ) AS isHCHP , IF(INSTR(configuration, '"total":"1"' )>0 or id = 845, 1 , 0 ) AS istotal , NAME AS myname, /* MIN(j.rec_date) AS DATE,*/ periode, hc AS shc, hp AS shp, hc2 AS shc2, hp2 AS shp2, hc3 AS shc3, hp3 AS shp3, totalhp, totalhc, totalhp2, totalhc2, totalhp3, totalhc3, prix_hc * hc AS hcprix, prix_hp * hp AS hpprix, prix_hc2 * hc2 AS hcprix2, prix_hp2 * hp2 AS hpprix2, prix_hc3 * hc3 AS hcprix3, prix_hp3 * hp3 AS hpprix3, (prix_hc * hc) + (prix_hp * hp) + (prix_hc2 * hc2) + (prix_hp2 * hp2) + (prix_hc3 * hc3) + (prix_hp3 * hp3) AS totalprix, DATE_FORMAT(j.`rec_date`,"%Y") AS mois, j.`rec_date`, p.id_eq FROM conso_jour j INNER JOIN ( SELECT jo.id_eq, DATE_FORMAT(jo.`rec_date`,"%Y"), jo.rec_date, SUM(hp) AS totalhp, SUM(hc) AS totalhc, SUM(hp2) AS totalhp2, SUM(hc2) AS totalhc2, SUM(hp3) AS totalhp3, SUM(hc3) AS totalhc3, (SELECT FORMAT(hc,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc, (SELECT FORMAT(hp,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp, (SELECT FORMAT(hc2,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc2, (SELECT FORMAT(hp2,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp2, (SELECT FORMAT(hc3,4) AS hc FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1) AS prix_hc3, (SELECT FORMAT(hp3,4) AS hp FROM conso_price WHERE type_ecq LIKE "%elect%" AND UNIX_TIMESTAMP(DATE_FORMAT(jo.rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) LIMIT 0,1 ) AS prix_hp3 FROM conso_jour jo INNER JOIN eqLogic eqc ON eqc.id = jo.id_eq WHERE jo.id_eq in (858,859,867,845) and DATE_FORMAT(jo.`rec_date`,"%Y") = DATE_FORMAT(CURDATE()- INTERVAL 365 DAY,"%Y") AND configuration LIKE "%elect%" AND (configuration LIKE '%"total":"1"%' or id = 845) GROUP BY DATE_FORMAT(jo.`rec_date`,"%Y"), jo.id_eq ) AS p ON DATE_FORMAT(p.`rec_date`,"%Y") = DATE_FORMAT(j.`rec_date`,"%Y") INNER JOIN eqLogic eq ON eq.id = j.id_eq AND isEnable = 1 WHERE j.id_eq in (858,859,867,845) and DATE_FORMAT(j.`rec_date`,"%Y") = DATE_FORMAT(CURDATE()- INTERVAL 365 DAY,"%Y") AND configuration LIKE "%elect%" /* GROUP BY j.id_eq*/ ) AS res GROUP BY DATE_FORMAT(res.`rec_date`,"%Y"), name_cat ORDER BY istotal DESC [2024-09-26 21:25:15][DEBUG] : Action loadingDash équipement:845 trame hier [2024-09-26 21:25:15][DEBUG] : Recherche id en cours 2 [2024-09-26 21:25:15][DEBUG] : Nb équipement avt:select count(*) as nb_equipement FROM `eqLogic` WHERE `eqType_name` = "conso" and configuration like '%\"parent_id\":\"845\"%' or id = 845 [2024-09-26 21:25:15][DEBUG] : nb_equipement6 [2024-09-26 21:25:15][DEBUG] : GetCalculPrice Date deb: 2024-09-26 Date fin:2024-09-26 Type graph:jours Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_jours as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-26" AND "2024-09-26" ) GROUP BY cat_jours ORDER BY rec_date ASC) as req GROUP by req.cat_jours ORDER BY req.rec_date ASC [2024-09-26 21:25:15][DEBUG] : GetCalculPrice Date deb: 2024-09-25 Date fin:2024-09-25 Type graph:jours Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_jours as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-25" AND "2024-09-25" ) GROUP BY cat_jours ORDER BY rec_date ASC) as req GROUP by req.cat_jours ORDER BY req.rec_date ASC [2024-09-26 21:25:15][DEBUG] : GetCalculPrice Date deb: 2024-09-23 Date fin:2024-09-29 Type graph:semaine Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_semaine as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-23" AND "2024-09-29" ) GROUP BY cat_semaine ORDER BY rec_date ASC) as req GROUP by req.cat_semaine ORDER BY req.rec_date ASC [2024-09-26 21:25:15][DEBUG] : GetCalculPrice Date deb: 2024-09-01 Date fin:2024-09-30 Type graph:mois Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_month as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-01" AND "2024-09-30" ) GROUP BY cat_month ORDER BY rec_date ASC) as req GROUP by req.cat_month ORDER BY req.rec_date ASC [2024-09-26 21:25:15][DEBUG] : GetCalculPrice Date deb: 2024-1-01 Date fin:2024-12-31 Type graph:mois Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_month as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-1-01" AND "2024-12-31" ) GROUP BY cat_month ORDER BY rec_date ASC) as req GROUP by req.cat_month ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : init autre du 2024-09-20 au 2024-09-26 [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2024-09-20 Date fin:2024-09-26 Type graph:jours Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_jours as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-20" AND "2024-09-26" ) GROUP BY cat_jours ORDER BY rec_date ASC) as req GROUP by req.cat_jours ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2024-09-20 Date fin:2024-09-26 Type graph:jours Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_jours as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN DATE_SUB("2024-09-20", INTERVAL 372 DAY) AND DATE_SUB("2024-09-26", INTERVAL 364 DAY) ) GROUP BY cat_jours ORDER BY rec_date ASC) as req GROUP by req.cat_jours ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : init autre du 2024-09-02 au 2024-09-29 [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2024-09-02 Date fin:2024-09-29 Type graph:semaines Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_semaine as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2024-09-02" AND "2024-09-29" ) GROUP BY cat_semaine ORDER BY rec_date ASC) as req GROUP by req.cat_semaine ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2024-09-02 Date fin:2024-09-29 Type graph:semaines Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_semaine as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN DATE_SUB("2024-09-02", INTERVAL 372 DAY) AND DATE_SUB("2024-09-29", INTERVAL 364 DAY) ) GROUP BY cat_semaine ORDER BY rec_date ASC) as req GROUP by req.cat_semaine ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : init autre du 2023-10-01 au 2024-09-30 [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2023-10-01 Date fin:2024-09-30 Type graph:mois Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_month as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2023-10-01" AND "2024-09-30" ) GROUP BY cat_month ORDER BY rec_date ASC) as req GROUP by req.cat_month ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2023-10-01 Date fin:2024-09-30 Type graph:mois Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, cat_month as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN DATE_SUB("2023-10-01", INTERVAL 372 DAY) AND DATE_SUB("2024-09-30", INTERVAL 364 DAY) ) GROUP BY cat_month ORDER BY rec_date ASC) as req GROUP by req.cat_month ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : init autre du 2000-01-01 au 2024-09-30 [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2000-01-01 Date fin:2024-09-30 Type graph:year Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, annee as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN "2000-01-01" AND "2024-09-30" ) GROUP BY cat_anne ORDER BY rec_date ASC) as req GROUP by req.cat_anne ORDER BY req.rec_date ASC [2024-09-26 21:25:16][DEBUG] : GetCalculPrice Date deb: 2000-01-01 Date fin:2024-09-30 Type graph:year Requete: SELECT 845 as id_equipement, id_parent,tva,tva_abo, abonnement, annee, mois, jour, semaine, sum(hp) as hp,sum(hc) as hc ,sum(hp2) as hp2,sum(hc2) as hc2 ,sum(hp3) as hp3,sum(hc3) as hc3 , sum(total_hp) as total_hp, sum(total_hc) as total_hc , sum(total_hp2) as total_hp2, sum(total_hc2) as total_hc2 , sum(total_hp3) as total_hp3, sum(total_hc3) as total_hc3 , sum(kwh) as kwh, prix_hp, prix_hc, prix_hp2, prix_hc2, prix_hp3, prix_hc3, temp_min, temp_max, temp_moy, dju, dju_clim, mois, annee as categorie , rec_date FROM ( SELECT FORMAT(MIN(temp_min),2) AS temp_min, FORMAT(MAX(temp_max),2) AS temp_max, FORMAT(AVG(temp_moy),2) AS temp_moy, SUM(dju) AS dju, SUM(dju_clim) AS dju_clim, "non" as id_parent, `timestamp`, rec_date, rec_date as cat_jours, DATE_FORMAT(s.`rec_date`,"%Y") AS annee, DATE_FORMAT(s.`rec_date`,"%c") AS mois, DATE_FORMAT(s.`rec_date`,"%e") AS jour, IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,52,DATE_FORMAT(s.`rec_date`,"%v")) AS semaine, IF(DATE_FORMAT(s.`rec_date`,"%c") = 1 AND DATE_FORMAT(s.`rec_date`,"%v") in (52,53),CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_SUB(s.`rec_date`, INTERVAL 1 YEAR),"%y")) , IF(DATE_FORMAT(s.`rec_date`,"%c") = 12 AND DATE_FORMAT(s.`rec_date`,"%v") = 1,CONCAT(DATE_FORMAT(s.`rec_date`,"sem %v")," ",DATE_FORMAT(DATE_ADD(s.`rec_date`, INTERVAL 1 YEAR),"%y")),DATE_FORMAT(s.`rec_date`,"sem %v %y"))) AS cat_semaine, /*Ajout du 1er janviers 2017 dans la semaie 52 de 2016*/ /* DATE_FORMAT(s.`rec_date`,"sem %v %y") AS cat_semaine,*/ DATE_FORMAT(s.`rec_date`,"%b %y") AS cat_month, DATE_FORMAT(s.`rec_date`,"%y") AS cat_anne, ROUND(SUM(s.hp),3) AS hp, ROUND(SUM(s.hc),3) AS hc, ROUND(SUM(s.hp2),3) AS hp2, ROUND(SUM(s.hc2),3) AS hc2, ROUND(SUM(s.hp3),3) AS hp3, ROUND(SUM(s.hc3),3) AS hc3, ROUND(SUM((SELECT SUM(ifnull(hc,0)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) AS kwh, (SELECT SUM(FORMAT(montant,2, "fr_FR") * (1 + cst.valeur/100)) AS abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) ) as abonnement, (SELECT cst.valeur tva_abo FROM conso_abo aa INNER JOIN conso_tva cst on cst.id = aa.id_tva where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( aa.date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( aa.date_fin, "%Y-%m-%d" ) ) limit 0,1 ) as tva_abo, (SELECT FORMAT(SUM(hc),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc, (SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp, (SELECT FORMAT(SUM(hc2),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc2, (SELECT FORMAT(SUM(hp2),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp2, (SELECT FORMAT(SUM(hc3),4, "fr_FR") AS hc FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hc3, (SELECT FORMAT(SUM(hp3),4, "fr_FR") AS hp FROM conso_price where type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d" ) ) ) as prix_hp3, (SELECT FORMAT(valeur ,2) FROM conso_tva where UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP( DATE_FORMAT( date_debut, "%Y-%m-%d" ) ) AND UNIX_TIMESTAMP( DATE_FORMAT( date_fin, "%Y-%m-%d")) and global = 1 limit 0,1) as tva, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc,4)) AS hc FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc ),2) END AS total_hc, CASE WHEN "electricity" = "gaz" THEN ROUND(SUM((SELECT (SUM(FORMAT(hp,4)) * hc) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp/1000 ),2) WHEN "electricity" IN ("oil") THEN ROUND(SUM((SELECT FORMAT(SUM(hp),4, "fr_FR") AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) ELSE ROUND(SUM((SELECT SUM(FORMAT(hp,4)) AS hp FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp ),2) END AS total_hp, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc2,4)) AS hc2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc2 ),2) END AS total_hc2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp2,4)) AS hp2 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp2 ),2) END AS total_hp2, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hc3,4)) AS hc3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hc3 ),2) END AS total_hc3, CASE WHEN "electricity" IN ("gaz","oil","water") THEN 0 ELSE ROUND(SUM((SELECT SUM(FORMAT(hp3,4)) AS hp3 FROM conso_price WHERE type_ecq like "electricity" AND UNIX_TIMESTAMP(DATE_FORMAT(rec_date , "%Y-%m-%d")) BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(date_debut, "%Y-%m-%d")) AND UNIX_TIMESTAMP(DATE_FORMAT(date_fin, "%Y-%m-%d")) ) * s.hp3 ),2) END AS total_hp3 FROM conso_jour s WHERE id_eq = 845 AND (`rec_date` BETWEEN DATE_SUB("2000-01-01", INTERVAL 372 DAY) AND DATE_SUB("2024-09-30", INTERVAL 364 DAY) ) GROUP BY cat_anne ORDER BY rec_date ASC) as req GROUP by req.cat_anne ORDER BY req.rec_date ASC [2024-09-26 21:25:23][INFO] : CrontabJour:REPLACE INTO conso_jour (`timestamp`,rec_date,periode,hp,hc,hp2,hc2,hp3,hc3,idx_max_hp,idx_min_hp,idx_max_hc,idx_min_hc,idx_max_hp2,idx_min_hp2,idx_max_hc2,idx_min_hc2,idx_max_hp3,idx_min_hp3,idx_max_hc3,idx_min_hc3,id_eq,temp_max,temp_min,temp_moy,dju_clim,dju) SELECT MIN(`timestamp`) AS `timestamp` , `conso_teleinfo`.`rec_date` AS `rec_date`, DATE_FORMAT(`conso_teleinfo`.`rec_date`,'%a %e %y') AS `periode`, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchp`) - MIN(`hchp`))) ELSE ((MAX(`hchp`) - MIN(`hchp`)) / 1000) END AS hp, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchc`) - MIN(`hchc`))) ELSE ((MAX(`hchc`) - MIN(`hchc`)) / 1000) END AS hc, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchp2`) - MIN(`hchp2`))) ELSE ((MAX(`hchp2`) - MIN(`hchp2`)) / 1000) END AS hp2, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchc2`) - MIN(`hchc2`))) ELSE ((MAX(`hchc2`) - MIN(`hchc2`)) / 1000) END AS hc2, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchp3`) - MIN(`hchp3`))) ELSE ((MAX(`hchp3`) - MIN(`hchp3`)) / 1000) END AS hp3, CASE WHEN id_equipement IN (864) THEN ((MAX(`hchc3`) - MIN(`hchc3`))) ELSE ((MAX(`hchc3`) - MIN(`hchc3`)) / 1000) END AS hc3,MAX(hchp) as idx_max_hp, MIN(hchp) as idx_min_hp, MAX(hchc) as idx_max_hc, MIN(hchc) as idx_min_hc, MAX(hchp2) as idx_max_hp2, MIN(hchp2) as idx_min_hp2, MAX(hchc2) as idx_max_hc2, MIN(hchc2) as idx_min_hc2, MAX(hchp3) as idx_max_hp3, MIN(hchp3) as idx_min_hp3, MAX(hchc3) as idx_max_hc3, MIN(hchc3) as idx_min_hc3, id_equipement, FORMAT(MAX(temp),2) AS temp_max, FORMAT(MIN(NULLIF(temp,0)),2) AS temp_min, FORMAT(AVG(NULLIF(temp,0)),2) AS temp_moy, CASE WHEN DATE_FORMAT(rec_date, '%m-%d') NOT BETWEEN DATE_FORMAT(STR_TO_DATE('06-15', '%m-%d'), '%m-%d') AND DATE_FORMAT(STR_TO_DATE('09-15', '%m-%d'), '%m-%d') THEN 0 WHEN 18 = 0 THEN 0 WHEN FORMAT(AVG(NULLIF(temp,0)),2) = 0 THEN 0 WHEN 18 >= FORMAT(MAX(temp),2) THEN 0 WHEN 18 <= FORMAT(MIN(NULLIF(temp,0)),2) THEN FORMAT(AVG(NULLIF(temp,0)),2)-18 WHEN FORMAT(MIN(NULLIF(temp,0)),2) < 18 AND 18 <= FORMAT(MAX(temp),2) THEN (FORMAT(MAX(temp),2)-18) * (0.08+0.42* (FORMAT(MAX(NULLIF(temp,0)),2)-18) / (FORMAT(MAX(temp),2) - FORMAT(MIN(NULLIF(temp,0)),2))) END AS dju_clim, CASE WHEN DATE_FORMAT(rec_date, '%m-%d') NOT BETWEEN DATE_FORMAT(STR_TO_DATE('10-15', '%m-%d'), '%m-%d') AND '12-31' AND DATE_FORMAT(rec_date, '%m-%d') NOT BETWEEN '01-01' AND DATE_FORMAT(STR_TO_DATE('04-15', '%m-%d'), '%m-%d') THEN 0 WHEN 18 = 0 THEN 0 WHEN FORMAT(AVG(NULLIF(temp,0)),2) = 0 THEN 0 WHEN 18 >= FORMAT(MAX(temp),2) THEN 18 - FORMAT(AVG(NULLIF(temp,0)),2) WHEN 18 <= FORMAT(MIN(NULLIF(temp,0)),2) THEN 0 WHEN FORMAT(MIN(NULLIF(temp,0)),2) < 18 AND 18 <= FORMAT(MAX(temp),2) THEN (18-FORMAT(MIN(NULLIF(temp,0)),2)) * (0.08+0.42* (18-FORMAT(MIN(NULLIF(temp,0)),2)) / (FORMAT(MAX(temp),2) - FORMAT(MIN(NULLIF(temp,0)),2))) END AS dju FROM `conso_teleinfo` INNER JOIN eqLogic ON id=id_equipement GROUP BY rec_date,id_equipement