六安人民，增加科室过滤功能，涉及到四个视图修改 #31

New Issue

2025-12-10T00:31:53+08:00

bigtran commented

2025-12-10 00:31:53 +08:00

mysql> drop view v_check;
Query OK, 0 rows affected (0.09 sec)

mysql> create view v_check as select `ect_actions`.`action_id` AS `action_id`,`ect_actions`.`op_starttime` AS `op_starttime`,`ect_meta_endoscope`.`department` AS `department`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,`ect_actions`.`opuser_name` AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_meta_endoscope`.`endoscope_type` AS `endoscope_type`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_actions`.`patient_id` AS `patient_id`,`ect_actions`.`patient_code` AS `patient_code`,`ect_actions`.`patient_name` AS `patient_name`,`ect_patients`.`patient_age` AS `patient_age`,`ect_patients`.`patient_gender` AS `patient_gender`,`ect_patients`.`patient_check_no` AS `patient_check_no`,`ect_patients`.`scrub_nurse` AS `scrub_nurse`,`ect_patients`.`source` AS `source`,`ect_patients`.`medicalRecordNumber` AS `medicalRecordNumber`,`ect_patients`.`consultingRoom` AS `consultingRoom`,`ect_patients`.`checkProject` AS `checkProject`,`ect_patients`.`clinicNumber` AS `clinicNumber`,`ect_patients`.`hospitalizationNumber` AS `hospitalizationNumber` from (((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_patients` on((`ect_actions`.`patient_id` = `ect_patients`.`patient_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 0) order by `ect_actions`.`op_starttime` desc;



mysql> create view v_clean as select `a`.`op_batchno` AS `op_batchno`,`em`.`department` AS `department`,`em`.`department_id` AS `department_id`,`em`.`endoscope_type` AS `endoscope_type`,`a`.`endoscope_id` AS `endoscope_id`,max(`a`.`action_type_name`) AS `action_type_name`,max(`a`.`op_enhance`) AS `op_enhance`,max(`a`.`op_morning`) AS `op_morning`,max(`a`.`opuser_name`) AS `opuser_name`,max(`a`.`opuser_rfid`) AS `opuser_rfid`,max(`a`.`endoscope_name`) AS `endoscope_name`,max(`em`.`endoscope_steel_no`) AS `endoscope_steel_no`,max(`a`.`endoscope_rfid`) AS `endoscope_rfid`,min(`a`.`op_starttime`) AS `op_starttime`,max(`a`.`op_starttime`) AS `op_endtime`,(max(`a`.`op_starttime`) - min(`a`.`op_starttime`)) AS `op_duration`,NULL AS `has_diagnosis` from (`ect_actions` `a` left join `ect_meta_endoscope` `em` on((`a`.`endoscope_id` = `em`.`endoscope_id`))) where (`a`.`action_type` in (1,2,3,4,5,6)) group by `a`.`op_batchno`,`a`.`endoscope_id` order by min(`a`.`op_starttime`) desc;
Query OK, 0 rows affected (0.03 sec)

mysql> drop view v_leak_test;
Query OK, 0 rows affected (0.01 sec)

mysql> create view v_leak_test as select `ect_actions`.`action_id` AS `action_id`,`ect_actions`.`op_starttime` AS `op_starttime`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,ifnull(`ect_actions`.`opuser_name`,'') AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id` from ((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 7) order by `ect_actions`.`op_starttime` desc;
Query OK, 0 rows affected (0.01 sec)

mysql> drop view v_storage;
Query OK, 0 rows affected (0.01 sec)

mysql> create view v_storage as select `ect_actions`.`op_starttime` AS `op_starttime`,`ect_actions`.`op_endtime` AS `op_endtime`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,ifnull(`ect_actions`.`opuser_name`,'') AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id` from ((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 8) order by `ect_actions`.`op_starttime` desc;

``` mysql> drop view v_check; Query OK, 0 rows affected (0.09 sec) mysql> create view v_check as select `ect_actions`.`action_id` AS `action_id`,`ect_actions`.`op_starttime` AS `op_starttime`,`ect_meta_endoscope`.`department` AS `department`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,`ect_actions`.`opuser_name` AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_meta_endoscope`.`endoscope_type` AS `endoscope_type`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_actions`.`patient_id` AS `patient_id`,`ect_actions`.`patient_code` AS `patient_code`,`ect_actions`.`patient_name` AS `patient_name`,`ect_patients`.`patient_age` AS `patient_age`,`ect_patients`.`patient_gender` AS `patient_gender`,`ect_patients`.`patient_check_no` AS `patient_check_no`,`ect_patients`.`scrub_nurse` AS `scrub_nurse`,`ect_patients`.`source` AS `source`,`ect_patients`.`medicalRecordNumber` AS `medicalRecordNumber`,`ect_patients`.`consultingRoom` AS `consultingRoom`,`ect_patients`.`checkProject` AS `checkProject`,`ect_patients`.`clinicNumber` AS `clinicNumber`,`ect_patients`.`hospitalizationNumber` AS `hospitalizationNumber` from (((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_patients` on((`ect_actions`.`patient_id` = `ect_patients`.`patient_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 0) order by `ect_actions`.`op_starttime` desc; mysql> create view v_clean as select `a`.`op_batchno` AS `op_batchno`,`em`.`department` AS `department`,`em`.`department_id` AS `department_id`,`em`.`endoscope_type` AS `endoscope_type`,`a`.`endoscope_id` AS `endoscope_id`,max(`a`.`action_type_name`) AS `action_type_name`,max(`a`.`op_enhance`) AS `op_enhance`,max(`a`.`op_morning`) AS `op_morning`,max(`a`.`opuser_name`) AS `opuser_name`,max(`a`.`opuser_rfid`) AS `opuser_rfid`,max(`a`.`endoscope_name`) AS `endoscope_name`,max(`em`.`endoscope_steel_no`) AS `endoscope_steel_no`,max(`a`.`endoscope_rfid`) AS `endoscope_rfid`,min(`a`.`op_starttime`) AS `op_starttime`,max(`a`.`op_starttime`) AS `op_endtime`,(max(`a`.`op_starttime`) - min(`a`.`op_starttime`)) AS `op_duration`,NULL AS `has_diagnosis` from (`ect_actions` `a` left join `ect_meta_endoscope` `em` on((`a`.`endoscope_id` = `em`.`endoscope_id`))) where (`a`.`action_type` in (1,2,3,4,5,6)) group by `a`.`op_batchno`,`a`.`endoscope_id` order by min(`a`.`op_starttime`) desc; Query OK, 0 rows affected (0.03 sec) mysql> drop view v_leak_test; Query OK, 0 rows affected (0.01 sec) mysql> create view v_leak_test as select `ect_actions`.`action_id` AS `action_id`,`ect_actions`.`op_starttime` AS `op_starttime`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,ifnull(`ect_actions`.`opuser_name`,'') AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id` from ((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 7) order by `ect_actions`.`op_starttime` desc; Query OK, 0 rows affected (0.01 sec) mysql> drop view v_storage; Query OK, 0 rows affected (0.01 sec) mysql> create view v_storage as select `ect_actions`.`op_starttime` AS `op_starttime`,`ect_actions`.`op_endtime` AS `op_endtime`,`ect_actions`.`endoscope_id` AS `endoscope_id`,`ect_actions`.`action_type_name` AS `action_type_name`,`ect_actions`.`process_name` AS `process_name`,ifnull(`ect_actions`.`opuser_name`,'') AS `opuser_name`,`ect_actions`.`opuser_rfid` AS `opuser_rfid`,`ect_actions`.`endoscope_name` AS `endoscope_name`,`ect_meta_endoscope`.`endoscope_steel_no` AS `endoscope_steel_no`,`ect_actions`.`endoscope_rfid` AS `endoscope_rfid`,`ect_meta_facilities`.`facility_name` AS `facility_name`,`ect_meta_endoscope`.`department_id` AS `department_id` from ((`ect_actions` left join `ect_meta_endoscope` on((`ect_actions`.`endoscope_id` = `ect_meta_endoscope`.`endoscope_id`))) left join `ect_meta_facilities` on((`ect_actions`.`reader_id` = `ect_meta_facilities`.`reader_id`))) where (`ect_actions`.`action_type` = 8) order by `ect_actions`.`op_starttime` desc; ```

bigtran commented

2025-12-10 00:32:18 +08:00