amf.public

Parameters

Name	Type	Mode
_from_day_id	integer	IN
_to_day_id	integer	IN
_tenant_id	uuid	IN

Definition

DELETE 
	FROM sms_kpi_sites
	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
			,sm."name" AS NAME
			,sm.serial_number
			,sm."type"
			,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_cycles AS (
		SELECT stc.day_id
			,stc.tenant_id
			,stc.truck_id AS machine_id
			,count(stc.id) AS cycles
		FROM sms_truck_cycle stc
		INNER JOIN days d ON d.day_id::INT >= stc.day_id
			AND d.day_id::INT <= stc.day_id
		WHERE stc.unknown_load = false
			AND stc.tenant_id = _tenant_id
		GROUP BY stc.day_id
			,stc.tenant_id
			,stc.truck_id
		UNION
		SELECT ssc.day_id
			,ssc.tenant_id
			,ssc.scraper_id AS machine_id
			,count(ssc.id) AS cycles
		FROM sms_scraper_cycle ssc
		INNER JOIN days d ON d.day_id::INT >= ssc.day_id
			AND d.day_id::INT <= ssc.day_id
		WHERE ssc.unknown_load = false
			AND ssc.unknown_dump = false
			AND ssc.tenant_id = _tenant_id
		GROUP BY ssc.day_id
			,ssc.tenant_id
			,ssc.scraper_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_gps_data AS (
		SELECT stgd.id AS id
			,stgd.TIME
			,to_char(stgd.TIME, 'YYYYMMDD')::INT AS day_id
			,stgd.serial_number AS tracker
			,stgd.tenant_id AS tenant_id
			,smtt.machine_id AS machine_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
		LEFT JOIN sms_machine_teltonika_tracker smtt ON stgd."time" >= smtt.valid_from
			AND stgd."time" < coalesce(smtt.valid_to, now())
			AND stgd.serial_number = smtt.tracker_serial
			AND stgd.tenant_id = smtt.tenant_id
		INNER JOIN sms_machine sm ON smtt.machine_id = sm.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 smtt.deleted_at IS NULL
			AND sm.deleted_at IS NULL
			AND stgd.tenant_id = _tenant_id
			AND stgd.ign = true
	)
	,computed_machine_gps_data AS (
	 	SELECT count(mgd.id) AS gpscount
			,mgd.tracker
			,mgd.tenant_id
			,mgd.day_id
			,mgd.machine_id
			,sum(mgd.delta_time) AS computed_duration
			,sum(mgd.delta_distance) AS computed_distance
		FROM machine_gps_data mgd
		WHERE mgd.day_id IS NOT NULL
		GROUP BY mgd.tracker
			,mgd.tenant_id
			,mgd.day_id
			,mgd.machine_id
	)
	,machine_site_gps AS (
		SELECT coalesce(ms.id, cmgd.machine_id) AS id
			,coalesce(ms.day_id, cmgd.day_id) AS day_id
			,coalesce(ms.tenant_id, cmgd.tenant_id) AS tenant_id
			,ms.site_id AS site_id
			,ms.site_name as site_name
			,CASE 
				WHEN cmgd.gpscount > 0
					THEN 1
				ELSE 0
				END AS active
			,cmgd.machine_id
			,ms.serial_number
			,ms."name"
			,ms."type"
			,cmgd.computed_duration
			,cmgd.computed_distance
		FROM machine_site ms
		FULL JOIN computed_machine_gps_data cmgd ON ms.id = cmgd.machine_id
			AND ms.day_id = cmgd.day_id
			AND ms.tenant_id = cmgd.tenant_id
			--WHERE ms.day_id IS NOT NULL
	)
	,machine_statistic AS (
		SELECT msg.active AS machine_active
			,msg.day_id
			,msg.tenant_id
			,msg.site_id
			,msg.site_name as site_name
			,msg.id AS machine_id
			,msg."type" AS machine_type
			,msg."name" AS machine_name
			,sum(msg.computed_duration) AS computed_duration
			,sum(msg.computed_distance) AS computed_distance
			,mp.cost_per_hour
			,mp.theoretical_consumption
			,mp.kg_co2_per_liter
			,mp.volume
			,mc.cycles
		FROM machine_site_gps msg 
		LEFT JOIN machine_parameters mp ON msg.day_id = mp.day_id
			AND msg.id = mp.machine_id
		LEFT JOIN machine_cycles mc ON msg.day_id = mc.day_id
			AND msg.id = mc.machine_id
		WHERE msg.day_id IS NOT NULL
		GROUP BY msg.id
			,msg.tenant_id
			,msg.site_id
			,msg.site_name
			,msg.active
			,msg."name"
			,msg."type"
			,msg.day_id
			,mp.cost_per_hour
			,mp.theoretical_consumption
			,mp.kg_co2_per_liter
			,mp.volume
			,mc.cycles
		ORDER BY msg.day_id
	)
		
	INSERT INTO sms_kpi_sites (day_id, tenant_id, site_id, site_name, machines_count, machines_count_active, machines_volume, machines_fuel_used, machines_co2_equivalent, machines_hours_operating, machines_cost)
		
	SELECT ms.day_id
		,ms.tenant_id
		,ms.site_id
		,ms.site_name
		,count(ms.machine_id) AS machines_count
		,sum(ms.machine_active) AS machines_count_active
		,sum(ms.volume * ms.cycles) AS machines_volume
		,sum(ms.computed_duration / 3600 * ms.theoretical_consumption) AS machines_fuel_used
		,sum(ms.computed_duration / 3600 * ms.theoretical_consumption * ms.kg_co2_per_liter) AS machines_co2_equivalent
		,sum(ms.computed_duration / 3600) AS machines_hours_operating
		,sum(ms.computed_duration / 3600 * ms.cost_per_hour) AS machines_cost
	FROM machine_statistic ms
	GROUP BY ms.day_id
		,ms.tenant_id
		,ms.site_id
		,ms.site_name
	ORDER BY ms.day_id;

sms_kpi_insert_computed_site_data(IN _from_day_id integer, IN _to_day_id integer, IN _tenant_id uuid)

Parameters

Definition