amf
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sms_kpi_insert_machine_cycle_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_machines_cycles WHERE tenant_id = _tenant_id AND day_id >= _from_day_id AND day_id <= _to_day_id; WITH all_cycles AS ( SELECT DISTINCT stc.day_id ,stc.tenant_id ,smsa.site_id AS site_id ,ss.NAME AS site_name ,stc.shift_day_id AS shift_id ,stc.shift_day_name AS shift_name ,stc.cycle_day_id ,stc.start_date AS cycle_date_start ,stc.local_hour AS cycle_hour_start ,stc.total_cycle_time AS cycle_time ,stc.truck_id AS dumper_id ,smd."type" AS dumper_type_id ,smtd."name" AS dumper_type ,smd."name" AS dumper_name ,stc.loader_id AS loader_id ,sml."type" AS loader_type_id ,smtl."name" AS loader_type ,sml."name" AS loader_name ,stc.dump_zone_id AS zone_dumping_id ,szd.NAME AS zone_dumping_name ,stc.load_zone_id AS zone_loading_id ,szl.NAME AS zone_loading_name ,stc.distance_loaded + stc.distance_empty AS dumper_distance ,stc.distance_loaded AS dumper_distance_loaded ,stc.distance_empty AS dumper_distance_empty ,(stc.travel_time_empty + stc.travel_time_loaded + stc.waiting_on_track_empty + stc.waiting_on_track_loaded + stc.loading_time + stc.dumping_time + stc.waiting_for_loading_time + stc.waiting_for_dumping_time) AS dumper_time_operating ,stc.travel_time_empty AS dumper_time_travelling_empty ,stc.travel_time_loaded AS dumper_time_travelling_loaded ,stc.waiting_on_track_empty AS dumper_time_waiting_empty ,stc.waiting_on_track_loaded AS dumper_time_waiting_loaded ,stc.stopped_engine_off_time AS dumper_time_engine_off ,stc.loading_time AS dumper_time_loading ,stc.dumping_time AS dumper_time_dumping ,stc.waiting_for_loading_time AS dumper_time_waiting_loading ,stc.waiting_for_dumping_time AS dumper_time_waiting_dumping ,stp.volume AS dumper_volume_theoretical ,stc.unknown_load AS dumper_operation_loader_unknown FROM sms_truck_cycle stc LEFT JOIN sms_machine smd ON stc.truck_id = smd.id AND stc.tenant_id = smd.tenant_id LEFT JOIN sms_machine_type smtd ON smd."type" = smtd.id AND smd.tenant_id = smtd.tenant_id LEFT JOIN sms_machine sml ON stc.loader_id = sml.id AND stc.tenant_id = sml.tenant_id LEFT JOIN sms_machine_type smtl ON sml."type" = smtl.id AND sml.tenant_id = smtl.tenant_id LEFT JOIN sms_zone szd ON stc.dump_zone_id = szd.id AND stc.tenant_id = szd.tenant_id LEFT JOIN sms_zone szl ON stc.load_zone_id = szl.id AND stc.tenant_id = szl.tenant_id LEFT JOIN sms_truck_parameters stp ON stc.truck_parameter_id = stp.id AND stc.tenant_id = stp.tenant_id LEFT JOIN sms_machine_site_association smsa ON stc.truck_id = smsa.machine_id AND stc.tenant_id = smsa.tenant_id AND stc.day_id >= to_char(smsa.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND stc.day_id <= to_char(coalesce(smsa.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT AND smsa.deleted_at IS NULL LEFT JOIN sms_site ss ON smsa.site_id = ss.id AND smsa.tenant_id = ss.tenant_id AND ss.deleted_at IS NULL WHERE stc.day_id >= _from_day_id AND stc.day_id <= _to_day_id UNION SELECT DISTINCT ssc.day_id ,ssc.tenant_id ,smsa.site_id AS site_id ,ss.NAME AS site_name ,ssc.shift_day_id AS shift_id ,ssc.shift_day_name AS shift_name ,ssc.cycle_day_id ,ssc.start_date AS cycle_date_start ,ssc.local_hour AS cycle_hour_start ,ssc.total_cycle_time AS cycle_time ,ssc.scraper_id AS dumper_id ,smd."type" AS dumper_type_id ,smtd."name" AS dumper_type ,smd."name" AS dumper_name ,ssc.dozer_id AS loader_id ,sml."type" AS loader_type_id ,smtl."name" AS loader_type ,sml."name" AS loader_name ,ssc.dump_zone_id AS zone_dumping_id ,szd.NAME AS zone_dumping_name ,ssc.load_zone_id AS zone_loading_id ,szl.NAME AS zone_loading_name ,ssc.distance_loaded + ssc.distance_empty AS dumper_distance ,ssc.distance_loaded AS dumper_distance_loaded ,ssc.distance_empty AS dumper_distance_empty ,(ssc.travel_time_empty + ssc.travel_time_loaded + ssc.waiting_on_track_empty + ssc.waiting_on_track_loaded + ssc.loading_time + ssc.dumping_time + ssc.waiting_for_loading_time + ssc.waiting_for_dumping_time) AS dumper_time_operating ,ssc.travel_time_empty AS dumper_time_travelling_empty ,ssc.travel_time_loaded AS dumper_time_travelling_loaded ,ssc.waiting_on_track_empty AS dumper_time_waiting_empty ,ssc.waiting_on_track_loaded AS dumper_time_waiting_loaded ,ssc.stopped_engine_off_time AS dumper_time_engine_off ,ssc.loading_time AS dumper_time_loading ,ssc.dumping_time AS dumper_time_dumping ,ssc.waiting_for_loading_time AS dumper_time_waiting_loading ,ssc.waiting_for_dumping_time AS dumper_time_waiting_dumping ,ssp.volume AS dumper_volume_theoretical ,ssc.unknown_load AS dumper_operation_loader_unknown FROM sms_scraper_cycle ssc LEFT JOIN sms_machine smd ON ssc.scraper_id = smd.id AND ssc.tenant_id = smd.tenant_id LEFT JOIN sms_machine_type smtd ON smd."type" = smtd.id AND smd.tenant_id = smtd.tenant_id LEFT JOIN sms_machine sml ON ssc.dozer_id = sml.id AND ssc.tenant_id = sml.tenant_id LEFT JOIN sms_machine_type smtl ON sml."type" = smtl.id AND sml.tenant_id = smtl.tenant_id LEFT JOIN sms_zone szd ON ssc.dump_zone_id = szd.id AND ssc.tenant_id = szd.tenant_id LEFT JOIN sms_zone szl ON ssc.load_zone_id = szl.id AND ssc.tenant_id = szl.tenant_id LEFT JOIN sms_scraper_parameters ssp ON ssc.scraper_parameter_id = ssp.id AND ssc.tenant_id = ssp.tenant_id LEFT JOIN sms_machine_site_association smsa ON ssc.scraper_id = smsa.machine_id AND ssc.tenant_id = smsa.tenant_id AND ssc.day_id >= to_char(smsa.valid_from::TIMESTAMP, 'YYYYMMDD')::INT AND ssc.day_id <= to_char(coalesce(smsa.valid_to, now())::TIMESTAMP, 'YYYYMMDD')::INT AND smsa.deleted_at IS NULL LEFT JOIN sms_site ss ON smsa.site_id = ss.id AND smsa.tenant_id = ss.tenant_id AND ss.deleted_at IS NULL WHERE ssc.day_id >= _from_day_id AND ssc.day_id <= _to_day_id ) INSERT INTO sms_kpi_machines_cycles (day_id, tenant_id, site_id, site_name, shift_id, shift_name, cycle_day_id, cycle_date_start, cycle_hour_start, cycle_time, dumper_id, dumper_type_id, dumper_type, dumper_name, loader_id, loader_type_id, loader_type, loader_name, zone_loading_id, zone_loading_name, zone_dumping_id, zone_dumping_name, dumper_distance, dumper_distance_empty, dumper_distance_loaded, dumper_time_operating, dumper_time_travelling_empty, dumper_time_travelling_loaded, dumper_time_waiting_empty, dumper_time_waiting_loaded, dumper_time_engine_off, dumper_time_loading, dumper_time_dumping, dumper_time_waiting_loading, dumper_time_waiting_dumping, dumper_volume_theoretical, dumper_operation_loader_unknown) SELECT day_id, tenant_id, site_id, site_name, shift_id, shift_name, cycle_day_id, cycle_date_start, cycle_hour_start, cycle_time, dumper_id, dumper_type_id, dumper_type, dumper_name, loader_id, loader_type_id, loader_type, loader_name, zone_loading_id, zone_loading_name, zone_dumping_id, zone_dumping_name, dumper_distance, dumper_distance_empty, dumper_distance_loaded, dumper_time_operating, dumper_time_travelling_empty, dumper_time_travelling_loaded, dumper_time_waiting_empty, dumper_time_waiting_loaded, dumper_time_engine_off, dumper_time_loading, dumper_time_dumping, dumper_time_waiting_loading, dumper_time_waiting_dumping, dumper_volume_theoretical, dumper_operation_loader_unknown FROM all_cycles ORDER BY day_id;