amf
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sms_kpi_insert_site_machine_data(IN _from_day_id integer, IN _to_day_id integer, IN _tenant_id uuid)
Parameters
Name
Type
Mode
_from_day_id
integer
IN
_to_day_id
integer
IN
_tenant_id
uuid
IN
Definition
DELETE FROM sms_kpi_sites_machines WHERE tenant_id = _tenant_id AND day_id >= _from_day_id AND day_id <= _to_day_id; WITH days AS ( SELECT to_char(ref_date, 'YYYYMMDD')::INT AS day_id FROM generate_series(TO_DATE(_from_day_id::TEXT, 'YYYYMMDD'), TO_DATE(_to_day_id::TEXT, 'YYYYMMDD'), '1 day') AS ref_date ) ,machine_site AS ( SELECT sm.id ,sm.tenant_id ,smsa.site_id ,ss."name" AS site_name ,d.day_id FROM sms_machine sm LEFT JOIN sms_machine_site_association smsa ON sm.id = smsa.machine_id AND sm.tenant_id = smsa.tenant_id INNER JOIN days d ON d.day_id >= to_char(smsa.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(smsa.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT INNER JOIN sms_site ss ON smsa.site_id = ss.id WHERE sm.deleted_at IS NULL AND smsa.deleted_at IS NULL AND ss.deleted_at IS NULL AND sm.tenant_id = _tenant_id ) ,machine_parameters AS ( SELECT sm.id AS machine_id ,sm.tenant_id ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.day_id WHEN sm."type" = 2 THEN loader_parameters.day_id WHEN sm."type" = 3 THEN dozer_parameters.day_id WHEN sm."type" = 6 THEN scraper_parameters.day_id ELSE other_parameters.day_id END AS day_id ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.cost_per_hour WHEN sm."type" = 2 THEN loader_parameters.cost_per_hour WHEN sm."type" = 3 THEN dozer_parameters.cost_per_hour WHEN sm."type" = 6 THEN scraper_parameters.cost_per_hour ELSE other_parameters.cost_per_hour END AS cost_per_hour ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.theoretical_consumption WHEN sm."type" = 2 THEN loader_parameters.theoretical_consumption WHEN sm."type" = 3 THEN dozer_parameters.theoretical_consumption WHEN sm."type" = 6 THEN scraper_parameters.theoretical_consumption ELSE other_parameters.theoretical_consumption END AS theoretical_consumption ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.kg_co2_per_liter WHEN sm."type" = 2 THEN loader_parameters.kg_co2_per_liter WHEN sm."type" = 3 THEN dozer_parameters.kg_co2_per_liter WHEN sm."type" = 6 THEN scraper_parameters.kg_co2_per_liter ELSE other_parameters.kg_co2_per_liter END AS kg_co2_per_liter ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.tonne WHEN sm."type" = 6 THEN scraper_parameters.tonne ELSE NULL END AS tonne ,CASE WHEN sm."type" = 1 OR sm."type" = 7 THEN truck_parameters.volume WHEN sm."type" = 6 THEN scraper_parameters.volume ELSE NULL END AS volume FROM sms_machine sm LEFT JOIN ( SELECT stp.* ,d.day_id FROM sms_truck_parameters stp INNER JOIN days d ON d.day_id >= to_char(stp.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(stp.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT WHERE stp.deleted_at IS NULL ) truck_parameters ON truck_parameters.machine_id = sm.id AND truck_parameters.tenant_id = sm.tenant_id LEFT JOIN ( SELECT slp.* ,d.day_id FROM sms_loader_parameters slp INNER JOIN days d ON d.day_id >= to_char(slp.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(slp.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT WHERE slp.deleted_at IS NULL ) loader_parameters ON loader_parameters.machine_id = sm.id AND loader_parameters.tenant_id = sm.tenant_id LEFT JOIN ( SELECT ssp.* ,d.day_id FROM sms_scraper_parameters ssp INNER JOIN days d ON d.day_id >= to_char(ssp.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(ssp.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT WHERE ssp.deleted_at IS NULL ) scraper_parameters ON scraper_parameters.machine_id = sm.id AND scraper_parameters.tenant_id = sm.tenant_id LEFT JOIN ( SELECT sop.* ,d.day_id FROM sms_other_parameters sop INNER JOIN days d ON d.day_id >= to_char(sop.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(sop.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT WHERE sop.deleted_at IS NULL ) other_parameters ON other_parameters.machine_id = sm.id AND other_parameters.tenant_id = sm.tenant_id LEFT JOIN ( SELECT sdp.* ,d.day_id FROM sms_dozer_parameters sdp INNER JOIN days d ON d.day_id >= to_char(sdp.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(sdp.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT WHERE sdp.deleted_at IS NULL ) dozer_parameters ON dozer_parameters.machine_id = sm.id AND dozer_parameters.tenant_id = sm.tenant_id WHERE sm.deleted_at IS NULL AND sm.tenant_id = _tenant_id ) ,machine_tracker AS ( SELECT sm.id AS machine_id ,sm."name" AS NAME ,sm.serial_number ,sm."type" ,sm.model ,sm.make ,sm.tenant_id ,she.id AS hiboo_id ,she.serial_number AS hiboo_serial_number ,she."name" AS hiboo_name ,smtt.tracker_serial ,d.day_id FROM sms_machine sm LEFT JOIN sms_machine_teltonika_tracker smtt ON sm.id = smtt.machine_id AND sm.tenant_id = smtt.tenant_id INNER JOIN days d ON d.day_id >= to_char(smtt.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND d.day_id <= to_char(coalesce(smtt.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT LEFT JOIN sms_hiboo_equipment she ON sm.serial_number = she.serial_number AND sm.tenant_id = she.tenant_id WHERE sm.deleted_at IS NULL AND smtt.deleted_at IS NULL AND sm.tenant_id = _tenant_id ) ,tracker_gps_data AS ( SELECT stgd.id AS id ,stgd.TIME ,stgd.ign ,to_char(stgd.TIME, 'YYYYMMDD')::INT AS day_id ,stgd.serial_number AS tracker ,stgd.tenant_id AS tenant_id ,stci.delta_time ,stci.delta_distance FROM sms_teltonika_gps_data stgd LEFT JOIN sms_tgd_computed_info stci ON stgd.id = stci.id WHERE stgd.TIME >= TO_TIMESTAMP(CONCAT (TO_DATE(_from_day_id::TEXT, 'YYYYMMDD'),' 00:00:00'), 'YYYY-MM-DD HH24:MI:SS') AND stgd.TIME <= TO_TIMESTAMP(CONCAT (TO_DATE(_to_day_id::TEXT, 'YYYYMMDD'),' 23:59:59'), 'YYYY-MM-DD HH24:MI:SS') AND stgd.tenant_id = _tenant_id ) ,computed_tracker_gps_data AS ( SELECT count(tgd.id) AS gpscount ,tgd.tracker ,tgd.tenant_id ,tgd.day_id ,sum(CASE WHEN tgd.ign = true THEN tgd.delta_time ELSE NULL END) AS computed_duration ,sum(CASE WHEN tgd.ign = true THEN tgd.delta_distance ELSE NULL END) AS computed_distance FROM tracker_gps_data tgd WHERE tgd.day_id IS NOT NULL GROUP BY tgd.tracker ,tgd.tenant_id ,tgd.day_id ) ,machine_tracker_gps_data AS ( SELECT mt.machine_id ,mt."name" AS NAME ,mt.serial_number ,mt."type" ,mt.model ,mt.make ,mt.hiboo_id ,mt.hiboo_serial_number ,mt.hiboo_name ,coalesce(mt.tracker_serial, ctgd.tracker) AS tracker ,coalesce(mt.tenant_id, ctgd.tenant_id) AS tenant_id ,coalesce(mt.day_id, ctgd.day_id) AS day_id ,ctgd.gpscount ,ctgd.computed_duration ,ctgd.computed_distance FROM machine_tracker mt LEFT JOIN computed_tracker_gps_data ctgd ON mt.tracker_serial = ctgd.tracker AND mt.day_id = ctgd.day_id AND mt.tenant_id = ctgd.tenant_id ) ,machine_tracker_site_gps AS ( SELECT coalesce(ms.id, mtgd.machine_id) AS machine_id ,coalesce(ms.day_id, mtgd.day_id) AS day_id ,coalesce(ms.tenant_id, mtgd.tenant_id) AS tenant_id ,ms.site_id AS site_id ,ms.site_name AS site_name ,mtgd.gpscount ,mtgd.serial_number ,mtgd."name" ,mtgd."type" ,mtgd.model ,mtgd.make ,mtgd.tracker ,mtgd.computed_duration ,mtgd.computed_distance ,mtgd.hiboo_id ,mtgd.hiboo_serial_number ,mtgd.hiboo_name FROM machine_tracker_gps_data mtgd FULL JOIN machine_site ms ON ms.id = mtgd.machine_id AND ms.day_id = mtgd.day_id AND ms.tenant_id = mtgd.tenant_id ) ,machine_statistic AS ( SELECT mtsg.gpscount ,mtsg.day_id ,mtsg.tenant_id ,mtsg.site_id ,mtsg.site_name AS site_name ,mtsg.machine_id AS machine_id ,mtsg."type" AS machine_type ,mtsg."name" AS machine_name ,mtsg.model AS machine_model ,mtsg.make AS machine_make ,mtsg.serial_number ,mtsg.hiboo_id ,mtsg.hiboo_serial_number ,mtsg.hiboo_name ,mtsg.tracker ,sum(mtsg.computed_duration) AS computed_duration ,sum(mtsg.computed_distance) AS computed_distance ,mp.cost_per_hour ,mp.theoretical_consumption ,mp.kg_co2_per_liter ,mp.volume FROM machine_tracker_site_gps mtsg LEFT JOIN machine_parameters mp ON mtsg.day_id = mp.day_id AND mtsg.machine_id = mp.machine_id WHERE mtsg.day_id IS NOT NULL GROUP BY mtsg.machine_id ,mtsg.tenant_id ,mtsg.site_id ,mtsg.site_name ,mtsg.gpscount ,mtsg."name" ,mtsg."type" ,mtsg.model ,mtsg.make ,mtsg.serial_number ,mtsg.hiboo_id ,mtsg.hiboo_serial_number ,mtsg.hiboo_name ,mtsg.tracker ,mtsg.day_id ,mp.cost_per_hour ,mp.theoretical_consumption ,mp.kg_co2_per_liter ,mp.volume ORDER BY mtsg.day_id ) ,hiboo AS ( SELECT * FROM sms_hiboo_daily_data shdd WHERE shdd.calendar_day_id >= _from_day_id AND shdd.calendar_day_id <= _to_day_id AND shdd.tenant_id = _tenant_id ) ,machine_waitings AS ( SELECT stc.truck_id AS machine_id ,stc.tenant_id ,stc.day_id ,SUM(stc.waiting_on_track_empty + stc.waiting_on_track_loaded + stc.waiting_for_loading_time + stc.waiting_for_dumping_time) AS hours_idling FROM sms_truck_cycle stc GROUP BY stc.truck_id ,stc.tenant_id ,stc.day_id UNION SELECT ssc.scraper_id AS machine_id ,ssc.tenant_id ,ssc.day_id ,SUM(ssc.waiting_on_track_empty + ssc.waiting_on_track_loaded + ssc.waiting_for_loading_time + ssc.waiting_for_dumping_time) AS hours_idling FROM sms_scraper_cycle ssc GROUP BY ssc.scraper_id ,ssc.tenant_id ,ssc.day_id ) INSERT INTO PUBLIC.sms_kpi_sites_machines ( day_id ,tenant_id ,site_id ,site_name ,machine_id ,machine_type_id ,machine_type ,machine_name ,machine_make ,machine_model ,machine_sn ,tracker_gps_count_teltonika ,tracker_sn_hiboo ,tracker_name_hiboo ,tracker_sn_teltonika ,tracker_id_teltonika ,machine_hours_operating ,machine_hours_idle ,machine_hours_working ,machine_fuel_used ,machine_distance ,machine_fuel_consumption ,machine_hours_idling_percentage ,machine_co2_equivalent ,hiboo_hours_operating ,hiboo_hours_idle ,hiboo_hours_working ,hiboo_fuel_used ,hiboo_distance ,hiboo_fuel_consumption ,hiboo_hours_idling_percentage ,hiboo_co2_equivalent ) SELECT ms.day_id ,ms.tenant_id ,ms.site_id ,ms.site_name ,ms.machine_id ,ms.machine_type AS machine_type_id ,smt.NAME AS machine_type ,ms.machine_name ,ms.machine_make ,ms.machine_model ,ms.serial_number AS machine_sn ,ms.gpscount AS tracker_gps_count_teltonika ,ms.hiboo_serial_number AS tracker_sn_hiboo ,ms.hiboo_name AS tracker_name_hiboo ,ms.tracker AS tracker_sn_teltonika ,NULL AS tracker_id_teltonika ,ms.computed_duration / 3600::NUMERIC AS machines_hours_operating ,mw.hours_idling / 3600::NUMERIC AS machines_hours_idle ,(ms.computed_duration - mw.hours_idling) / 3600::NUMERIC AS machines_hours_working ,(ms.computed_duration / 3600 * ms.theoretical_consumption) AS machine_fuel_used ,ms.computed_distance AS machine_distance ,CASE WHEN ms.computed_duration IS NOT NULL AND coalesce(ms.computed_duration, 0) > 0 THEN (ms.computed_duration / 3600 * ms.theoretical_consumption) / (ms.computed_duration / 3600) ELSE NULL END AS machine_fuel_consumption ,CASE WHEN mw.hours_idling IS NOT NULL AND coalesce(ms.computed_duration, 0) > 0 THEN mw.hours_idling / ms.computed_duration * 100 ELSE NULL END AS machine_hours_idling_percentage ,(ms.computed_duration / 3600 * ms.theoretical_consumption * ms.kg_co2_per_liter) AS machine_co2_equivalent ,h.operating_hours AS hiboo_hours_operating ,h.idle_hours AS hiboo_hours_idle ,h.working_hours AS hiboo_hours_working ,h.fuel_used AS hiboo_fuel_used ,h.distance * 1000 AS hiboo_distance ,CASE WHEN h.fuel_used IS NOT NULL AND coalesce(h.operating_hours, 0) > 0 THEN h.fuel_used / h.operating_hours ELSE NULL END AS hiboo_fuel_consumption ,CASE WHEN h.idle_hours IS NOT NULL AND coalesce(h.operating_hours, 0) > 0 THEN h.idle_hours / h.operating_hours * 100 ELSE NULL END AS hiboo_hours_idling_percentage ,h.fuel_used * ms.kg_co2_per_liter AS hiboo_co2_equivalent FROM machine_statistic ms INNER JOIN sms_machine_type smt ON ms.machine_type = smt.id AND ms.tenant_id = smt.tenant_id LEFT JOIN hiboo h ON ms.hiboo_id = h.equipment_id AND ms.day_id = h.calendar_day_id AND ms.tenant_id = h.tenant_id LEFT JOIN machine_waitings mw ON ms.machine_id = mw.machine_id AND ms.tenant_id = mw.tenant_id AND ms.day_id = mw.day_id ORDER BY ms.day_id;