View Epta

Raccolta di view utilizzate in epta per mostrare dati al cliente. Vengono utilizzate delle view per fornire accesso solamente ai dati interessati senza dover condividere il DB.

Scada subworking con Id issue e descrizione issue

CREATE VIEW `scada_subworkings_view`  AS 
SELECT `scada_subworkings`.`sk_id` AS `sk_id`, `scada_subworkings`.`ar_code` AS `ar_code`, `scada_subworkings`.`ar_name` AS `ar_name`, `scada_subworkings`.`wo_header` AS `wo_header`, `scada_subworkings`.`wo_code` AS `wo_code`, `scada_subworkings`.`wk_phase` AS `wk_phase`, `scada_subworkings`.`sk_type` AS `sk_type`, `scada_subworkings`.`sk_setup_time` AS `sk_setup_time`, `scada_subworkings`.`sk_working_time` AS `sk_working_time`, `scada_subworkings`.`sk_expected_working_time` AS `sk_expected_working_time`, `scada_subworkings`.`sk_goods` AS `sk_goods`, `scada_subworkings`.`sk_wastes` AS `sk_wastes`, `scada_subworkings`.`sk_unit` AS `sk_unit`, `scada_subworkings`.`sk_energy` AS `sk_energy`, `scada_subworkings`.`sk_gas` AS `sk_gas`, `scada_subworkings`.`sk_start` AS `sk_start`, `scada_subworkings`.`sk_finish` AS `sk_finish`, `scada_subworkings`.`sk_timestamp` AS `sk_timestamp`, `scada_subworkings`.`pc_id` AS `pc_id`, `mes_production_causes`.`pc_name` AS `pc_name`, `scada_subworkings`.`qc_id` AS `qc_id`, `mes_quality_causes`.`qc_name` AS `qc_name`, json_extract(`scada_subworkings`.`sk_info`,'$.ail_xx_id') AS `st_id` 
FROM ((`scada_subworkings` left join `mes_production_causes` on(`scada_subworkings`.`pc_id` = `mes_production_causes`.`pc_id`)) left join `mes_quality_causes` on(`scada_subworkings`.`qc_id` = `mes_quality_causes`.`qc_id`)) ;

Scada machine issue con durata effettiva

CREATE VIEW `scada_machine_issues_view`  AS 
SELECT `scada_subworkings_view`.`sk_id` AS `sk_id`, `scada_subworkings_view`.`ar_code` AS `ar_code`, `scada_subworkings_view`.`ar_name` AS `ar_name`, `scada_subworkings_view`.`wo_header` AS `wo_header`, `scada_subworkings_view`.`wo_code` AS `wo_code`, `scada_subworkings_view`.`wk_phase` AS `wk_phase`, `scada_subworkings_view`.`sk_setup_time` AS `sk_setup_time`, `scada_subworkings_view`.`sk_working_time` AS `sk_working_time`, `scada_subworkings_view`.`sk_expected_working_time` AS `sk_expected_working_time`, `scada_subworkings_view`.`sk_goods` AS `sk_goods`, `scada_subworkings_view`.`sk_wastes` AS `sk_wastes`, `scada_subworkings_view`.`sk_unit` AS `sk_unit`, `scada_subworkings_view`.`sk_energy` AS `sk_energy`, `scada_subworkings_view`.`sk_gas` AS `sk_gas`, `scada_subworkings_view`.`sk_start` AS `sk_start`, `scada_subworkings_view`.`sk_finish` AS `sk_finish`, `scada_subworkings_view`.`sk_timestamp` AS `sk_timestamp`, `scada_subworkings_view`.`pc_id` AS `pc_id`, `scada_subworkings_view`.`pc_name` AS `pc_name`, `scada_subworkings_view`.`qc_id` AS `qc_id`, `scada_subworkings_view`.`qc_name` AS `qc_name`, `scada_machine_status_view`.`st_id` AS `st_id`, `scada_machine_status_view`.`wc_status` AS `wc_status`, `scada_machine_status_view`.`st_start` AS `st_start`, `scada_machine_status_view`.`st_stop` AS `st_stop`, `scada_machine_status_view`.`st_duration` AS `st_duration`, `scada_machine_status_view`.`st_hour` AS `st_hour` 
FROM (`scada_subworkings_view` left join `scada_machine_status_view` on(`scada_subworkings_view`.`st_id` = `scada_machine_status_view`.`st_id`)) 
WHERE `scada_subworkings_view`.`sk_type` = 'ISSUE' ;

Scada machine status con durata effettiva (eliminando status rotti)

CREATE VIEW `scada_machine_status_view`  AS 
SELECT `scada_machine_status_history`.`st_id` AS `st_id`, lag(`scada_machine_status_history`.`st_timestamp`,1) over ( order by `scada_machine_status_history`.`st_id`) AS `st_start`, `scada_machine_status_history`.`st_timestamp` AS `st_stop`, timediff(`scada_machine_status_history`.`st_timestamp`,lag(`scada_machine_status_history`.`st_timestamp`,1) over ( order by `scada_machine_status_history`.`st_id`)) AS `st_duration`, `scada_machine_status_history`.`wc_status` AS `wc_status`, `scada_machine_status_history`.`st_hour` AS `st_hour`, `scada_machine_status_history`.`st_timestamp` AS `st_timestamp` 
FROM `scada_machine_status_history` 
WHERE `scada_machine_status_history`.`st_timestamp` > '2023-10-05' ;