
DROP VIEW IF EXISTS `get_break_mom`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_break_mom` AS 
select if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),'--',concat(`abc`.`mom`,'%')) AS `mom`,if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),0,`abc`.`flag`) AS `flag` from (select if(((`yesd`.`c` = 0) or (`tod`.`c` = `yesd`.`c`)),0,abs(round((((`tod`.`c` - `yesd`.`c`) / `yesd`.`c`) * 100),0))) AS `mom`,if(((`yesd`.`c` = 0) or (`tod`.`c` = `yesd`.`c`)),0,if((`tod`.`c` > `yesd`.`c`),1,2)) AS `flag` from (((select count(1) AS `c` from `wl_equipment_specific_alarm_log` where ((`wl_equipment_specific_alarm_log`.`create_date` like concat(curdate(),'%')) and (`wl_equipment_specific_alarm_log`.`type` = 'BREAKDOWN')))) `tod` join (select count(1) AS `c` from `wl_equipment_specific_alarm_log` where ((`wl_equipment_specific_alarm_log`.`create_date` like concat((curdate() - interval 1 day),'%')) and (`wl_equipment_specific_alarm_log`.`type` = 'BREAKDOWN'))) `yesd`)) `abc` ;

-- ----------------------------
-- View structure for get_building_tree
-- ----------------------------
DROP VIEW IF EXISTS `get_building_tree`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_building_tree` AS select `a`.`instance_id` AS `instanceId`,max((case when (`a`.`field_name` = 'parentId') then `a`.`field_value` end)) AS `parentId`,max((case when (`a`.`field_name` = 'name') then `a`.`field_value` end)) AS `instanceName` from `wl_form_instance` `a` group by `a`.`instance_id` ;

-- ----------------------------
-- View structure for get_danger_count
-- ----------------------------
DROP VIEW IF EXISTS `get_danger_count`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_danger_count` AS select '计划巡查' AS `name`,count(1) AS `value` from `p_latent_danger` where (`p_latent_danger`.`danger_type` = 2) union select '无计划检查' AS `name`,count(1) AS `value` from `p_latent_danger` where (`p_latent_danger`.`danger_type` = 3) union select '无码检查' AS `name`,count(1) AS `value` from `p_latent_danger` where (`p_latent_danger`.`danger_type` = 1) union select '随手拍' AS `name`,count(1) AS `value` from `p_latent_danger` where (`p_latent_danger`.`danger_type` = 4) ;

-- ----------------------------
-- View structure for get_danger_count_trans
-- ----------------------------
DROP VIEW IF EXISTS `get_danger_count_trans`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_danger_count_trans` AS select (select `get_danger_count`.`value` from `get_danger_count` where (convert(`get_danger_count`.`name` using utf8mb4) = '计划巡查')) AS `jhxc`,(select `get_danger_count`.`value` from `get_danger_count` where (convert(`get_danger_count`.`name` using utf8mb4) = '无计划检查')) AS `wjhxc`,(select `get_danger_count`.`value` from `get_danger_count` where (convert(`get_danger_count`.`name` using utf8mb4) = '无码检查')) AS `wmxc`,(select `get_danger_count`.`value` from `get_danger_count` where (convert(`get_danger_count`.`name` using utf8mb4) = '随手拍')) AS `ssp` ;

-- ----------------------------
-- View structure for get_dev_mom
-- ----------------------------
DROP VIEW IF EXISTS `get_dev_mom`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_dev_mom` AS select `abc`.`today` AS `today`,if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),'--',concat(`abc`.`mom`,'%')) AS `mom`,if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),0,`abc`.`flag`) AS `flag` from (select `to`.`c` AS `today`,if(((`ye`.`c` = 0) or (`ye`.`c` = `to`.`c`)),0,abs(round((((`to`.`c` - `ye`.`c`) / `ye`.`c`) * 100),0))) AS `mom`,if(((`ye`.`c` = 0) or (`to`.`c` = `ye`.`c`)),0,if((`to`.`c` > `ye`.`c`),1,2)) AS `flag` from (((select count(1) AS `c` from `p_plan_task_detail` where ((`p_plan_task_detail`.`status` = 2) or ((`p_plan_task_detail`.`status` = 3) and (`p_plan_task_detail`.`create_date` like concat(curdate(),'%')))))) `to` join (select count(1) AS `c` from `p_plan_task_detail` where ((`p_plan_task_detail`.`status` = 2) or ((`p_plan_task_detail`.`status` = 3) and (`p_plan_task_detail`.`create_date` like concat((curdate() - interval 1 day),'%'))))) `ye`)) `abc` ;

-- ----------------------------
-- View structure for get_fire_mom
-- ----------------------------
DROP VIEW IF EXISTS `get_fire_mom`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `get_fire_mom` AS select if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),'--',concat(`abc`.`mom`,'%')) AS `mom`,if((isnull(`abc`.`mom`) or (`abc`.`mom` = 0)),0,`abc`.`flag`) AS `flag` from (select if(((`yesd`.`c` = 0) or (`tod`.`c` = `yesd`.`c`)),0,abs(round((((`tod`.`c` - `yesd`.`c`) / `yesd`.`c`) * 100),0))) AS `mom`,if(((`yesd`.`c` = 0) or (`tod`.`c` = `yesd`.`c`)),0,if((`tod`.`c` > `yesd`.`c`),1,2)) AS `flag` from (((select count(1) AS `c` from `wl_equipment_specific_alarm` where ((`wl_equipment_specific_alarm`.`create_date` like concat(curdate(),'%')) and (`wl_equipment_specific_alarm`.`type` = 'FIREALARM')))) `tod` join (select count(1) AS `c` from `wl_equipment_specific_alarm` where ((`wl_equipment_specific_alarm`.`create_date` like concat((curdate() - interval 1 day),'%')) and (`wl_equipment_specific_alarm`.`type` = 'FIREALARM'))) `yesd`)) `abc` ;

-- ----------------------------
-- View structure for risk_danger_statistics_week
-- ----------------------------
DROP VIEW IF EXISTS `risk_danger_statistics_week`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `risk_danger_statistics_week` AS select count(1) AS `amount`,left(`l`.`create_date`,10) AS `vest_date`,`s`.`org_code` AS `org_code`,(week((`l`.`create_date` + interval 6 day),2) - 1) AS `week` from (`f_rpn_change_log` `l` left join `f_risk_source` `s` on((`l`.`risk_source_id` = `s`.`id`))) where ((`l`.`rpn` > `l`.`rpni`) and (`l`.`create_date` between (curdate() - interval (weekday(curdate()) + 1) day) and (curdate() - interval (weekday(curdate()) - 5) day))) group by (week((`l`.`create_date` + interval 6 day),2) - 1),`s`.`org_code` ;

-- ----------------------------
-- View structure for statisticsequipment_view
-- ----------------------------
DROP VIEW IF EXISTS `statisticsequipment_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `statisticsequipment_view` AS select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment_detail`.`name` AS `name_mx`,`wl_equipment_detail`.`id` AS `id_mx`,`wl_equipment_detail`.`code` AS `code_mx`,`wl_stock_detail`.`warehouse_structure_id` AS `warehouseStructureId`,`wl_equipment_detail`.`brand` AS `brand`,`wl_warehouse_structure`.`full_name` AS `fullName`,`wl_equipment_detail`.`standard` AS `standard`,`wl_stock_detail`.`qr_code` AS `qr_code`,`wl_stock_detail`.`amount` AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_stock_detail`.`status`) and (`wl_system_dic`.`type` = 'EquipStatus'))) AS `StateName`,'equipment' AS `type` from ((`wl_stock_detail` left join (`wl_equipment_detail` left join `wl_equipment` on((`wl_equipment`.`id` = `wl_equipment_detail`.`equipment_id`))) on((`wl_stock_detail`.`equipment_detail_id` = `wl_equipment_detail`.`id`))) left join `wl_warehouse_structure` on((`wl_stock_detail`.`warehouse_structure_id` = `wl_warehouse_structure`.`id`))) where ((`wl_stock_detail`.`status` in (1,2,10)) and (left(`wl_equipment`.`code`,1) <> '4')) union all select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment_detail`.`name` AS `name_mx`,`wl_equipment_detail`.`id` AS `id_mx`,`wl_equipment_detail`.`code` AS `code_mx`,`wl_stock_detail`.`warehouse_structure_id` AS `warehouseStructureId`,`wl_equipment_detail`.`brand` AS `brand`,`wl_warehouse_structure`.`full_name` AS `fullName`,`wl_equipment_detail`.`standard` AS `standard`,`wl_stock_detail`.`qr_code` AS `qr_code`,`wl_stock_detail`.`amount` AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_stock_detail`.`status`) and (`wl_system_dic`.`type` = 'EquipStatus'))) AS `StateName`,'equipment' AS `type` from ((`wl_stock_detail` left join (`wl_equipment_detail` left join `wl_equipment` on((`wl_equipment`.`id` = `wl_equipment_detail`.`equipment_id`))) on((`wl_stock_detail`.`equipment_detail_id` = `wl_equipment_detail`.`id`))) left join `wl_warehouse_structure` on((`wl_stock_detail`.`warehouse_structure_id` = `wl_warehouse_structure`.`id`))) where ((`wl_stock_detail`.`status` in (1,2,10)) and (left(`wl_equipment`.`code`,1) = '4')) union all select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment`.`name` AS `name_mx`,`wl_car`.`id` AS `id_mx`,`wl_equipment`.`code` AS `code_mx`,'' AS `warehouseStructureId`,`wl_car`.`brand` AS `brand`,'' AS `fullName`,`wl_car`.`standard` AS `standard`,`wl_car`.`qr_code` AS `qr_code`,1 AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_car`.`car_state`) and (`wl_system_dic`.`type` = 'CarState'))) AS `StateName`,'car' AS `type` from (`wl_car` join `wl_equipment`) where (`wl_equipment`.`id` = `wl_car`.`equipment_id`) ;

-- ----------------------------
-- View structure for s_danger_govern_count
-- ----------------------------
DROP VIEW IF EXISTS `s_danger_govern_count`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_danger_govern_count` AS select count(distinct `ld`.`id`) AS `danger_govern_count`,cast(now() as date) AS `vest_date` from (`p_latent_danger` `ld` left join `p_latent_danger_flow_record` `ldfr` on((`ldfr`.`danger_id` = `ld`.`id`))) where ((`ld`.`org_code` = '10') and (`ld`.`danger_state` = '5') and (cast(`ldfr`.`create_date` as date) = cast(now() as date))) ;

-- ----------------------------
-- View structure for s_equip_alarm_month
-- ----------------------------
DROP VIEW IF EXISTS `s_equip_alarm_month`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_equip_alarm_month` AS 
select `temp`.`org_code` AS `org_code`,`temp`.`total` AS `total`,`temp`.`fire_count` AS `fire_count`,`temp`.`fault_count` AS `fault_count`,`temp`.`fault_confirm_count` AS `fault_confirm_count`,`temp`.`fire_confirm_count` AS `fire_confirm_count`,year(now()) AS `year`,month(now()) AS `month`,now() AS `update_date` from (select `wl_equipment_specific_alarm_log`.`org_code` AS `org_code`,count(1) AS `total`,sum((case when (`wl_equipment_specific_alarm_log`.`type` = 'FIREALARM') then 1 else 0 end)) AS `fire_count`,sum((case when (`wl_equipment_specific_alarm_log`.`type` = 'BREAKDOWN') then 1 else 0 end)) AS `fault_count`,sum((case when ((`wl_equipment_specific_alarm_log`.`type` = 'BREAKDOWN') and (`wl_equipment_specific_alarm_log`.`confirm_type` is not null)) then 1 else 0 end)) AS `fault_confirm_count`,sum((case when ((`wl_equipment_specific_alarm_log`.`type` = 'FIREALARM') and (`wl_equipment_specific_alarm_log`.`confirm_type` is not null)) then 1 else 0 end)) AS `fire_confirm_count` from `wl_equipment_specific_alarm_log` where ((year(`wl_equipment_specific_alarm_log`.`create_date`) = year(now())) and (month(`wl_equipment_specific_alarm_log`.`create_date`) = month(now()))) group by `wl_equipment_specific_alarm_log`.`org_code`) `temp` ;

-- ----------------------------
-- View structure for s_fighting_system_alarm_state
-- ----------------------------
DROP VIEW IF EXISTS `s_fighting_system_alarm_state`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_fighting_system_alarm_state` AS select `temp`.`name` AS `name`,`temp`.`org_code` AS `org_code`,`temp`.`fault_count` AS `fault_count`,`temp`.`fire_count` AS `fire_count`,(case when ((`temp`.`fire_count` = 0) and (`temp`.`fault_count` = 0)) then 'normal' when (`temp`.`fire_count` > 0) then 'fire' when ((`temp`.`fire_count` = 0) and (`temp`.`fault_count` > 0)) then 'fault' end) AS `state`,`temp`.`code` AS `code`,`temp`.`create_date` AS `create_date` from (select `ffs`.`name` AS `name`,`ffs`.`org_code` AS `org_code`,ifnull(sum((case when ((`esa`.`type` = 'BREAKDOWN') and (`esa`.`status` = 1)) then 1 else 0 end)),0) AS `fault_count`,ifnull(sum((case when ((`esa`.`type` = 'FIREALARM') and (`esa`.`status` = 1)) then 1 else 0 end)),0) AS `fire_count`,`ec`.`code` AS `code`,`esa`.`create_date` AS `create_date` from (((`f_fire_fighting_system` `ffs` left join `wl_equipment_specific` `es` on((`es`.`system_id` = `ffs`.`id`))) left join `wl_equipment_specific_alarm` `esa` on((`esa`.`equipment_specific_id` = `es`.`id`))) left join `wl_equipment_category` `ec` on((`ffs`.`system_type` = `ec`.`id`))) where ((to_days(now()) - to_days(`esa`.`create_date`)) <= 30) group by `ffs`.`name`,`ffs`.`org_code`) `temp` order by `temp`.`fault_count` desc ;

-- ----------------------------
-- View structure for s_fire_equip_category_stock_statistics
-- ----------------------------
DROP VIEW IF EXISTS `s_fire_equip_category_stock_statistics`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_fire_equip_category_stock_statistics` AS select `temp`.`CODE` AS `CODE`,sum(`temp`.`amount`) AS `total`,`temp`.`org_code` AS `org_code`,(case when (`temp`.`CODE` = '1') then '消防人员防护装备' when (`temp`.`CODE` = '3') then '灭火器材装备' when (`temp`.`CODE` = '4') then '灭火药剂' when (`temp`.`CODE` = '5') then '抢险救援器材' when (`temp`.`CODE` = '6') then '消防通信指挥装备' when (`temp`.`CODE` = '7') then '特种消防装备' when (`temp`.`CODE` = '8') then '防火检查与火灾调查装备' else '其他类消防装备器材' end) AS `NAME` from (select `sd`.`amount` AS `amount`,`eq`.`code` AS `fullCode`,substr(`eq`.`code`,1,8) AS `shortCode`,substr(`eq`.`code`,1,1) AS `CODE`,`sd`.`org_code` AS `org_code` from (((`wl_stock_detail` `sd` left join `wl_equipment_detail` `ed` on((`ed`.`id` = `sd`.`equipment_detail_id`))) left join `wl_equipment` `eq` on((`eq`.`id` = `ed`.`equipment_id`))) left join `wl_equipment_category` `ec` on((`ec`.`id` = `eq`.`category_id`))) where ((`ec`.`industry_code` = '2') and (substr(`eq`.`code`,1,8) <> '85000000'))) `temp` group by `temp`.`org_code`,`temp`.`CODE` union all select '2' AS `CODE`,count(1) AS `total`,`wl_car`.`org_code` AS `org_code`,'消防车' AS `NAME` from `wl_car` ;

-- ----------------------------
-- View structure for s_fire_equip_stock_detail
-- ----------------------------
DROP VIEW IF EXISTS `s_fire_equip_stock_detail`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_fire_equip_stock_detail` AS select `sd`.`equipment_detail_id` AS `equipment_detail_id`,`sd`.`equipment_specific_id` AS `equipment_specific_id`,`sd`.`amount` AS `amount`,`sd`.`status` AS `status`,`sd`.`org_code` AS `org_code`,`we`.`code` AS `equipment_code`,`we`.`name` AS `equipment_name`,`ed`.`code` AS `equipment_detail_code`,`ed`.`name` AS `equipment_detail_name`,`ec`.`parent_id` AS `parent_id`,`ec`.`code` AS `category_code`,`ec`.`name` AS `category_name`,`ec1`.`code` AS `fire_system_category_code`,`ec1`.`name` AS `fire_system_name`,`wu`.`name` AS `unit_name`,`ec`.`industry_code` AS `industry_code` from (((((((`wl_stock_detail` `sd` left join `wl_equipment_specific` `es` on((`sd`.`equipment_specific_id` = `es`.`id`))) left join `wl_equipment_detail` `ed` on((`sd`.`equipment_detail_id` = `ed`.`id`))) left join `wl_equipment` `we` on((`ed`.`equipment_id` = `we`.`id`))) left join `wl_equipment_category` `ec` on((`we`.`category_id` = `ec`.`id`))) left join `f_fire_fighting_system` `fs` on((`es`.`system_id` = `fs`.`id`))) left join `wl_equipment_category` `ec1` on((`fs`.`system_type` = `ec1`.`id`))) left join `wl_unit` `wu` on((`we`.`unit_id` = `wu`.`id`))) where (`ec`.`industry_code` = '2') ;

-- ----------------------------
-- View structure for s_patrol_plan_exec_day
-- ----------------------------
DROP VIEW IF EXISTS `s_patrol_plan_exec_day`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_patrol_plan_exec_day` AS select count(`td`.`point_id`) AS `plan_task_num`,ifnull(sum((case when (`td`.`is_finish` = 0) then 1 else 0 end)),0) AS `unfinished_count`,ifnull(sum((case when (`td`.`is_finish` = 1) then 1 else 0 end)),0) AS `exec_plan_task_num`,ifnull(sum((case when (`td`.`is_finish` = 2) then 1 else 0 end)),0) AS `missed_check_num`,`p`.`org_code` AS `org_code`,cast(now() as date) AS `vest_date` from (`p_plan_task_detail` `td` left join `p_point` `p` on((`p`.`id` = `td`.`point_id`))) where (cast(`td`.`create_date` as date) = cast(now() as date)) ;

-- ----------------------------
-- View structure for s_patrol_plan_exec_week
-- ----------------------------
DROP VIEW IF EXISTS `s_patrol_plan_exec_week`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_patrol_plan_exec_week` AS select count(`td`.`point_id`) AS `plan_task_num`,ifnull(sum((case when (`td`.`is_finish` = 0) then 1 else 0 end)),0) AS `unfinished_count`,ifnull(sum((case when (`td`.`is_finish` = 1) then 1 else 0 end)),0) AS `exec_plan_task_num`,ifnull(sum((case when (`td`.`is_finish` = 2) then 1 else 0 end)),0) AS `missed_check_num`,`p`.`org_code` AS `org_code`,week(now(),0) AS `week`,year(now()) AS `year` from (`p_plan_task_detail` `td` left join `p_point` `p` on((`p`.`id` = `td`.`point_id`))) where ((week(`td`.`create_date`,0) = week(now(),0)) and (year(`td`.`create_date`) = year(now()))) ;

-- ----------------------------
-- View structure for s_patrol_plan_task_exec_day
-- ----------------------------
DROP VIEW IF EXISTS `s_patrol_plan_task_exec_day`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_patrol_plan_task_exec_day` AS select count(`pt`.`id`) AS `plan_task_num`,ifnull(sum((case when ((`pt`.`finish_status` = 0) or (`pt`.`finish_status` = 1)) then 1 else 0 end)),0) AS `unfinished_count`,ifnull(sum((case when (`pt`.`finish_status` = 2) then 1 else 0 end)),0) AS `exec_plan_task_num`,ifnull(sum((case when (`pt`.`finish_status` = 3) then 1 else 0 end)),0) AS `missed_check_num`,ifnull((select count(1) AS `input_abnormal_count` from `p_check` where ((`p_check`.`is_ok` = 2) and ((to_days(now()) - to_days(`p_check`.`check_time`)) = 0))),0) AS `input_abnormal_count`,cast(now() as date) AS `vest_date` from `p_plan_task` `pt` where (((to_days(`pt`.`begin_time`) - to_days(now())) = 0) and ((to_days(now()) - to_days(`pt`.`end_time`)) = 0)) ;

-- ----------------------------
-- View structure for s_risk_danger_day
-- ----------------------------
DROP VIEW IF EXISTS `s_risk_danger_day`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_risk_danger_day` AS select ifnull(sum((case when (`p_latent_danger`.`danger_level` = '1') then 1 else 0 end)),0) AS `general`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '2') then 1 else 0 end)),0) AS `major`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '0') then 1 else 0 end)),0) AS `safety_problem`,cast(now() as date) AS `vest_date`,`p_latent_danger`.`org_code` AS `org_code`,now() AS `update_date` from `p_latent_danger` where (cast(`p_latent_danger`.`create_date` as date) = cast(now() as date)) ;

-- ----------------------------
-- View structure for s_risk_danger_month
-- ----------------------------
DROP VIEW IF EXISTS `s_risk_danger_month`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_risk_danger_month` AS select ifnull(sum((case when (`p_latent_danger`.`danger_level` = '1') then 1 else 0 end)),0) AS `general`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '2') then 1 else 0 end)),0) AS `major`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '0') then 1 else 0 end)),0) AS `safety_problem`,year(now()) AS `year`,month(now()) AS `month`,`p_latent_danger`.`org_code` AS `org_code`,now() AS `update_date` from `p_latent_danger` where ((month(`p_latent_danger`.`create_date`) = month(now())) and (year(`p_latent_danger`.`create_date`) = year(now()))) ;

-- ----------------------------
-- View structure for s_risk_danger_week
-- ----------------------------
DROP VIEW IF EXISTS `s_risk_danger_week`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_risk_danger_week` AS select ifnull(sum((case when (`p_latent_danger`.`danger_level` = '1') then 1 else 0 end)),0) AS `general`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '2') then 1 else 0 end)),0) AS `major`,ifnull(sum((case when (`p_latent_danger`.`danger_level` = '0') then 1 else 0 end)),0) AS `safety_problem`,year(now()) AS `year`,week(now(),0) AS `week`,`p_latent_danger`.`org_code` AS `org_code`,now() AS `update_date` from `p_latent_danger` where ((week(`p_latent_danger`.`create_date`,0) = week(now(),0)) and (year(`p_latent_danger`.`create_date`) = year(now()))) ;

-- ----------------------------
-- View structure for s_risk_index_trend
-- ----------------------------
DROP VIEW IF EXISTS `s_risk_index_trend`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_risk_index_trend` AS select `f_safety_index_change_log`.`safety_index` AS `safety_index`,`f_safety_index_change_log`.`org_code` AS `org_code`,`f_safety_index_change_log`.`collect_date` AS `collect_date` from `f_safety_index_change_log` union all (select abs(((`f_risk_source`.`rpn` / 10) - 100)) AS `safety_index`,`f_risk_source`.`org_code` AS `org_code`,cast(now() as date) AS `collect_date` from `f_risk_source` where ((`f_risk_source`.`parent_id` = 0) or isnull(`f_risk_source`.`parent_id`))) ;

-- ----------------------------
-- View structure for s_risk_statistics
-- ----------------------------
DROP VIEW IF EXISTS `s_risk_statistics`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `s_risk_statistics` AS select count(`rs`.`code`) AS `total`,ifnull(sum((case when (`rl`.`level` = '1') then 1 else 0 end)),0) AS `one_level`,ifnull(sum((case when (`rl`.`level` = '2') then 1 else 0 end)),0) AS `two_level`,ifnull(sum((case when (`rl`.`level` = '3') then 1 else 0 end)),0) AS `three_level`,ifnull(sum((case when (`rl`.`level` = '4') then 1 else 0 end)),0) AS `four_level`,convert('riskArea' using utf8) AS `type`,`rs`.`org_code` AS `org_code` from (`f_risk_source` `rs` left join `f_risk_level` `rl` on((`rl`.`id` = `rs`.`risk_level_id`))) where ((`rs`.`parent_id` <> 0) and (`rs`.`risk_level_id` is not null) and (`rs`.`is_region` = 'TRUE')) union all select count(`rs`.`code`) AS `total`,ifnull(sum((case when (`rl`.`level` = '1') then 1 else 0 end)),0) AS `one_level`,ifnull(sum((case when (`rl`.`level` = '2') then 1 else 0 end)),0) AS `two_level`,ifnull(sum((case when (`rl`.`level` = '3') then 1 else 0 end)),0) AS `three_level`,ifnull(sum((case when (`rl`.`level` = '4') then 1 else 0 end)),0) AS `four_level`,convert('riskSource' using utf8) AS `type`,`rs`.`org_code` AS `org_code` from (`f_risk_source` `rs` left join `f_risk_level` `rl` on((`rl`.`id` = `rs`.`risk_level_id`))) where ((`rs`.`risk_level_id` is not null) and (`rs`.`is_region` = 'FALSE')) union all select count(`fm`.`id`) AS `total`,ifnull(sum((case when (`rl`.`level` = '1') then 1 else 0 end)),0) AS `one_level`,ifnull(sum((case when (`rl`.`level` = '2') then 1 else 0 end)),0) AS `two_level`,ifnull(sum((case when (`rl`.`level` = '3') then 1 else 0 end)),0) AS `three_level`,ifnull(sum((case when (`rl`.`level` = '4') then 1 else 0 end)),0) AS `four_level`,convert('riskFactor' using utf8) AS `type`,`rs`.`org_code` AS `org_code` from ((`f_fmea` `fm` left join `f_risk_source` `rs` on((`rs`.`id` = `fm`.`risk_source_id`))) left join `f_risk_level` `rl` on((`rl`.`id` = `fm`.`risk_level_id`))) where (`fm`.`risk_level_id` is not null) union all select count(`fm`.`id`) AS `total`,ifnull(sum((case when (`rl`.`level` = '1') then 1 else 0 end)),0) AS `one_level`,ifnull(sum((case when (`rl`.`level` = '2') then 1 else 0 end)),0) AS `two_level`,ifnull(sum((case when (`rl`.`level` = '3') then 1 else 0 end)),0) AS `three_level`,ifnull(sum((case when (`rl`.`level` = '4') then 1 else 0 end)),0) AS `four_level`,convert('riskFactorFailure' using utf8) AS `type`,`rs`.`org_code` AS `org_code` from ((`f_fmea` `fm` left join `f_risk_source` `rs` on((`rs`.`id` = `fm`.`risk_source_id`))) left join `f_risk_level` `rl` on((`rl`.`id` = `fm`.`risk_level_id`))) where ((`fm`.`risk_level_id` is not null) and (`fm`.`rpn` > `fm`.`rpni`)) ;


-- ----------------------------
-- View structure for v2
-- ----------------------------
DROP VIEW IF EXISTS `v2`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v2` AS select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment_detail`.`name` AS `name_mx`,`wl_equipment_detail`.`id` AS `id_mx`,`wl_equipment_detail`.`code` AS `code_mx`,`wl_stock_detail`.`warehouse_structure_id` AS `warehouseStructureId`,`wl_equipment_detail`.`brand` AS `brand`,`wl_warehouse_structure`.`full_name` AS `fullName`,`wl_equipment_detail`.`standard` AS `standard`,`wl_stock_detail`.`qr_code` AS `qr_code`,1 AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_stock_detail`.`status`) and (`wl_system_dic`.`type` = 'EquipStatus'))) AS `StateName`,'equipment' AS `type` from ((`wl_stock_detail` left join (`wl_equipment_detail` left join `wl_equipment` on((`wl_equipment`.`id` = `wl_equipment_detail`.`equipment_id`))) on((`wl_stock_detail`.`equipment_detail_id` = `wl_equipment_detail`.`id`))) left join `wl_warehouse_structure` on((`wl_stock_detail`.`warehouse_structure_id` = `wl_warehouse_structure`.`id`))) where ((`wl_stock_detail`.`status` in (1,2,10)) and (left(`wl_equipment`.`code`,1) <> '4')) union all select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment_detail`.`name` AS `name_mx`,`wl_equipment_detail`.`id` AS `id_mx`,`wl_equipment_detail`.`code` AS `code_mx`,`wl_stock_detail`.`warehouse_structure_id` AS `warehouseStructureId`,`wl_equipment_detail`.`brand` AS `brand`,`wl_warehouse_structure`.`full_name` AS `fullName`,`wl_equipment_detail`.`standard` AS `standard`,`wl_stock_detail`.`qr_code` AS `qr_code`,`wl_stock_detail`.`amount` AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_stock_detail`.`status`) and (`wl_system_dic`.`type` = 'EquipStatus'))) AS `StateName`,'equipment' AS `type` from ((`wl_stock_detail` left join (`wl_equipment_detail` left join `wl_equipment` on((`wl_equipment`.`id` = `wl_equipment_detail`.`equipment_id`))) on((`wl_stock_detail`.`equipment_detail_id` = `wl_equipment_detail`.`id`))) left join `wl_warehouse_structure` on((`wl_stock_detail`.`warehouse_structure_id` = `wl_warehouse_structure`.`id`))) where ((`wl_stock_detail`.`status` in (1,2,10)) and (left(`wl_equipment`.`code`,1) = '4')) union all select left(`wl_equipment`.`code`,2) AS `ercode`,`wl_equipment`.`name` AS `name`,`wl_equipment`.`code` AS `code`,`wl_equipment`.`id` AS `id`,`wl_equipment`.`name` AS `name_mx`,`wl_car`.`id` AS `id_mx`,`wl_equipment`.`code` AS `code_mx`,'' AS `warehouseStructureId`,`wl_car`.`brand` AS `brand`,'' AS `fullName`,`wl_car`.`standard` AS `standard`,`wl_car`.`qr_code` AS `qr_code`,1 AS `amount`,(select `wl_system_dic`.`name` from `wl_system_dic` where ((`wl_system_dic`.`code` = `wl_car`.`car_state`) and (`wl_system_dic`.`type` = 'CarState'))) AS `StateName`,'car' AS `type` from (`wl_car` left join `wl_equipment` on((`wl_equipment`.`id` = `wl_car`.`equipment_id`))) where (`wl_car`.`car_state` in (1,2)) ;

-- ----------------------------
-- View structure for v_alarm_equip_statistic_date_range
-- ----------------------------
DROP VIEW IF EXISTS `v_alarm_equip_statistic_date_range`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_alarm_equip_statistic_date_range` AS (
	SELECT
		`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
		`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
		`alarm`.`create_date` AS `create_date`,
		`alarm`.`alarm_reason` AS `alam_reason`,
		'今天' AS `dateRange`,
		`spec`.`system_id` AS `system_id`,
		(
			SELECT
				`sys`.`code`
			FROM
				`f_fire_fighting_system` `sys`
			WHERE
				find_in_set(
					`spec`.`system_id`,
					`sys`.`id`
				)
		) AS `code`,
		(
			CASE
			WHEN isnull(`alarm`.`confirm_type`) THEN
				'未确认'
			ELSE
				'已确认'
			END
		) AS `confirm_type`
	FROM
		(
			`wl_equipment_specific_alarm_log` `alarm`
			LEFT JOIN `wl_equipment_specific` `spec` ON (
				(
					`spec`.`id` = `alarm`.`equipment_specific_id`
				)
			)
		)
	WHERE
		(
			(
				to_days(now()) - to_days(`alarm`.`create_date`)
			) <= 1
		)
)
UNION ALL
	(
		SELECT
			`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
			`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
			`alarm`.`create_date` AS `create_date`,
			`alarm`.`alarm_reason` AS `alam_reason`,
			'近三天' AS `dateRange`,
			`spec`.`system_id` AS `system_id`,
			(
				SELECT
					`sys`.`code`
				FROM
					`f_fire_fighting_system` `sys`
				WHERE
					find_in_set(
						`spec`.`system_id`,
						`sys`.`id`
					)
			) AS `code`,
			(
				CASE
				WHEN isnull(`alarm`.`confirm_type`) THEN
					'未确认'
				ELSE
					'已确认'
				END
			) AS `confirm_type`
		FROM
			(
				`wl_equipment_specific_alarm_log` `alarm`
				LEFT JOIN `wl_equipment_specific` `spec` ON (
					(
						`spec`.`id` = `alarm`.`equipment_specific_id`
					)
				)
			)
		WHERE
			(
				(
					to_days(now()) - to_days(`alarm`.`create_date`)
				) <= 3
			)
	)
UNION ALL
	(
		SELECT
			`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
			`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
			`alarm`.`create_date` AS `create_date`,
			`alarm`.`alarm_reason` AS `alam_reason`,
			'近一周' AS `dateRange`,
			`spec`.`system_id` AS `system_id`,
			(
				SELECT
					`sys`.`code`
				FROM
					`f_fire_fighting_system` `sys`
				WHERE
					find_in_set(
						`spec`.`system_id`,
						`sys`.`id`
					)
			) AS `code`,
			(
				CASE
				WHEN isnull(`alarm`.`confirm_type`) THEN
					'未确认'
				ELSE
					'已确认'
				END
			) AS `confirm_type`
		FROM
			(
				`wl_equipment_specific_alarm_log` `alarm`
				LEFT JOIN `wl_equipment_specific` `spec` ON (
					(
						`spec`.`id` = `alarm`.`equipment_specific_id`
					)
				)
			)
		WHERE
			(
				(
					to_days(now()) - to_days(`alarm`.`create_date`)
				) <= 7
			)
	)
UNION ALL
	(
		SELECT
			`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
			`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
			`alarm`.`create_date` AS `create_date`,
			`alarm`.`alarm_reason` AS `alam_reason`,
			'近一月' AS `dateRange`,
			`spec`.`system_id` AS `system_id`,
			(
				SELECT
					`sys`.`code`
				FROM
					`f_fire_fighting_system` `sys`
				WHERE
					find_in_set(
						`spec`.`system_id`,
						`sys`.`id`
					)
			) AS `code`,
			(
				CASE
				WHEN isnull(`alarm`.`confirm_type`) THEN
					'未确认'
				ELSE
					'已确认'
				END
			) AS `confirm_type`
		FROM
			(
				`wl_equipment_specific_alarm_log` `alarm`
				LEFT JOIN `wl_equipment_specific` `spec` ON (
					(
						`spec`.`id` = `alarm`.`equipment_specific_id`
					)
				)
			)
		WHERE
			(
				(
					to_days(now()) - to_days(`alarm`.`create_date`)
				) <= 30
			)
	)
UNION ALL
	(
		SELECT
			`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
			`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
			`alarm`.`create_date` AS `create_date`,
			`alarm`.`alarm_reason` AS `alam_reason`,
			'近半年' AS `dateRange`,
			`spec`.`system_id` AS `system_id`,
			(
				SELECT
					`sys`.`code`
				FROM
					`f_fire_fighting_system` `sys`
				WHERE
					find_in_set(
						`spec`.`system_id`,
						`sys`.`id`
					)
			) AS `code`,
			(
				CASE
				WHEN isnull(`alarm`.`confirm_type`) THEN
					'未确认'
				ELSE
					'已确认'
				END
			) AS `confirm_type`
		FROM
			(
				`wl_equipment_specific_alarm_log` `alarm`
				LEFT JOIN `wl_equipment_specific` `spec` ON (
					(
						`spec`.`id` = `alarm`.`equipment_specific_id`
					)
				)
			)
		WHERE
			(
				(
					to_days(now()) - to_days(`alarm`.`create_date`)
				) <= 180
			)
	)
UNION ALL
	(
		SELECT
			`alarm`.`equipment_specific_name` AS `equipment_specific_name`,
			`alarm`.`equipment_specific_index_name` AS `equipment_specific_index_name`,
			`alarm`.`create_date` AS `create_date`,
			`alarm`.`alarm_reason` AS `alam_reason`,
			'近一年' AS `dateRange`,
			`spec`.`system_id` AS `system_id`,
			(
				SELECT
					`sys`.`code`
				FROM
					`f_fire_fighting_system` `sys`
				WHERE
					find_in_set(
						`spec`.`system_id`,
						`sys`.`id`
					)
			) AS `code`,
			(
				CASE
				WHEN isnull(`alarm`.`confirm_type`) THEN
					'未确认'
				ELSE
					'已确认'
				END
			) AS `confirm_type`
		FROM
			(
				`wl_equipment_specific_alarm_log` `alarm`
				LEFT JOIN `wl_equipment_specific` `spec` ON (
					(
						`spec`.`id` = `alarm`.`equipment_specific_id`
					)
				)
			)
		WHERE
			(
				(
					to_days(now()) - to_days(`alarm`.`create_date`)
				) <= 365
			)
	);


-- ----------------------------
-- View structure for v_check_all
-- ----------------------------
DROP VIEW IF EXISTS `v_check_all`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_check_all` AS 
SELECT
	ifnull(
		sum(
			(
				CASE
				WHEN (
					`p_plan_task`.`finish_status` = 3
				) THEN
					1
				ELSE
					0
				END
			)
		),
		0
	) AS `finishNum`
FROM
	`p_plan_task`
WHERE
	`p_plan_task`.check_date LIKE concat(curdate(), '%')
UNION ALL
	SELECT
		count(1) AS `finishNum`
	FROM
		`p_latent_danger`
	WHERE
		`p_latent_danger`.`create_date` LIKE concat(curdate(), '%')
UNION ALL
	SELECT
		count(1) AS `finishNum`
	FROM
		`wl_equipment_specific_alarm_log`
	WHERE
		`wl_equipment_specific_alarm_log`.`create_date` LIKE concat(curdate(), '%');
-- ----------------------------
-- View structure for v_check_pending
-- ----------------------------
DROP VIEW IF EXISTS `v_check_pending`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_check_pending` AS select ifnull(sum((case when (`p_plan_task`.`finish_status` = 3) then 1 else 0 end)),0) AS `pendingNum` from `p_plan_task` where (`p_plan_task`.`check_date` like concat(curdate(),'%')) union all select count(1) AS `pendingNum` from `p_latent_danger` where ((`p_latent_danger`.`danger_state` in (1,2,4,7,8,9)) and (`p_latent_danger`.`create_date` like concat(curdate(),'%'))) union all select ifnull(sum((case when (`wl_equipment_specific_alarm_log`.`confirm_type` is null) then 1 else 0 end)),0) AS `pendingNum` from `wl_equipment_specific_alarm_log` where (`wl_equipment_specific_alarm_log`.`create_date` like concat(curdate(),'%'));
-- ----------------------------
-- View structure for v_equipment_specific_alarm
-- ----------------------------
DROP VIEW IF EXISTS `v_equipment_specific_alarm`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equipment_specific_alarm` AS SELECT
	`c`.`a` AS `name`,
	`c`.`b` AS `sort`,
	sum(
		(
			CASE `c`.`type`
			WHEN 'FIREALARM' THEN
				1
			ELSE
				0
			END
		)
	) AS `value1`,
	sum(
		(
			CASE `c`.`type`
			WHEN 'BREAKDOWN' THEN
				1
			ELSE
				0
			END
		)
	) AS `value2`,
	sum(
		(
			CASE `c`.`type`
			WHEN 'SIGNALALARM' THEN
				1
			ELSE
				0
			END
		)
	) AS `value3`,
	`c`.`type` AS `type`
FROM
	(
		SELECT
			(
				CASE
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 6 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 6 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 5 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 5 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 4 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 4 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 3 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 3 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 2 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 2 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 1 DAY))
				) THEN
					date_format(
						(now() - INTERVAL 1 DAY),
						'%m-%d'
					)
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth(now())
				) THEN
					date_format(now(), '%m-%d')
				END
			) AS `a`,
			(
				CASE
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 6 DAY))
				) THEN
					1
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 5 DAY))
				) THEN
					2
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 4 DAY))
				) THEN
					3
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 3 DAY))
				) THEN
					4
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 2 DAY))
				) THEN
					5
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth((now() - INTERVAL 1 DAY))
				) THEN
					6
				WHEN (
					dayofmonth(
						`wl_equipment_specific_alarm_log`.`create_date`
					) = dayofmonth(now())
				) THEN
					7
				END
			) AS `b`,
			`wl_equipment_specific_alarm_log`.`type` AS `type`
		FROM
			`wl_equipment_specific_alarm_log`
		WHERE
			(
				
				
					(
						date_format(
							`wl_equipment_specific_alarm_log`.`create_date`,
							'%Y-%m-%d'
						) = date_format(now(), '%Y-%m-%d')
					)
					OR (
						date_format(
							`wl_equipment_specific_alarm_log`.`update_date`,
							'%Y-%m-%d'
						) = date_format(now(), '%Y-%m-%d')
					)
			)
	) `c`
WHERE
	(
		(`c`.`a` <> '')
		OR (`c`.`a` <> NULL)
	)
GROUP BY
	`c`.`a`
ORDER BY
	`c`.`b`;


-- ----------------------------
-- View structure for v_equip_alarm_today_statistics
-- ----------------------------
DROP VIEW IF EXISTS `v_equip_alarm_today_statistics`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equip_alarm_today_statistics` AS SELECT
	(
		count(1) - count(
			`clean_time`
		)
	) AS `unCleaned`,
	(
		count(1) - count(
			`confirm_type`
		)
	) AS `pending`
FROM
	`wl_equipment_specific_alarm_log`;

-- ----------------------------
-- View structure for v_equip_fire_control_water
-- ----------------------------
DROP VIEW IF EXISTS `v_equip_fire_control_water`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equip_fire_control_water` AS select '消防水池' AS `名称`,(select `wf`.`field_value` from `wl_form_instance` `wf` where (`wf`.`field_name` = 'waterStorage')) AS `水量` from `wl_form_instance` `wfi` where (`wfi`.`group_code` like '%pool%') group by `wfi`.`instance_id` ;

-- ----------------------------
-- View structure for v_equip_task_plan
-- ----------------------------
DROP VIEW IF EXISTS `v_equip_task_plan`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equip_task_plan` AS select `ppi`.`name` AS `name`,count(`pptd`.`id`) AS `checkNum` from (`p_plan_task_detail` `pptd` left join `p_point_classify` `ppi` on((`pptd`.`point_id` = `ppi`.`point_id`))) where ((curdate() - interval 30 day) <= cast(`pptd`.`create_date` as date)) group by `ppi`.`name` order by `checkNum` limit 10 ;

-- ----------------------------
-- View structure for v_fire_60list
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_60list`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_60list` AS
SELECT
	sal.id AS `id`,
	`ec`.`code` AS `mRid`,
	`sal`.`equipment_specific_name` AS `specificName`,
	`sal`.`equipment_specific_index_key` AS `indexKey`,
	`sal`.`equipment_specific_index_name` AS `indexName`,
	`sal`.`equipment_specific_id` AS `specificId`,
    IF
	( `sal`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
	`sal`.`iot_code` AS `iotCode`,
	`sal`.`create_date` AS `createDate`,
	(
		SELECT
			GROUP_CONCAT(id)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysId`,
	(
		SELECT
			GROUP_CONCAT(`code`)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysMRid`,
	(
		SELECT
			GROUP_CONCAT(`name`)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysName`
FROM
	wl_equipment_specific_alarm_log sal
LEFT JOIN `wl_equipment_specific` `ec` ON `sal`.`equipment_specific_id` = `ec`.`id`
ORDER BY
	`sal`.`create_date` DESC
LIMIT 60;

-- ----------------------------
-- View structure for v_fire_acs_num
-- ----------------------------
DROP VIEW
IF EXISTS `v_fire_acs_num`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_acs_num` AS 
SELECT
	`d`.`sffjnum` AS `sffjnum`,
	`d`.`sffjqd` AS `sffjqd`,
	`d`.`sffjgz` AS `sffjgz`,
	`d`.`sffjpb` AS `sffjpb`,
	`d`.`pffjnum` AS `pffjnum`,
	`d`.`pffjqd` AS `pffjqd`,
	`d`.`pffjgz` AS `pffjgz`,
	`d`.`pffjpb` AS `pffjpb`,
	`d`.`dtnum` AS `dtnum`,
	`d`.`dtqd` AS `dtqd`,
	`d`.`dtgz` AS `dtgz`,
	`d`.`dtpb` AS `dtpb`,
	`d`.`ktnum` AS `ktnum`,
	`d`.`ktqd` AS `ktqd`,
	`d`.`ktgz` AS `ktgz`,
	`d`.`ktpb` AS `ktpb`,
	`d`.`ntnum` AS `ntnum`,
	`d`.`ntqd` AS `ntqd`,
	`d`.`ntgz` AS `ntgz`,
	`d`.`ntpb` AS `ntpb`,
	`d`.`mjnum` AS `mjnum`,
	`d`.`mjqd` AS `mjqd`,
	`d`.`mjgz` AS `mjgz`,
	`d`.`mjpb` AS `mjpb`
FROM
	(
		SELECT
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '921606002E344'
			) AS `sffjnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '921606002E344'
				AND `wei`.`name_key` = 'SCS_Blower_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `sffjqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '921606002E344'
				AND `wei`.`name_key` = 'SCS_Blower_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `sffjgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '921606002E344'
				AND `wei`.`name_key` = 'SCS_Blower_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `sffjpb`,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '92160700GQP44'
			) AS `pffjnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92160700GQP44'
				AND `wei`.`name_key` = 'SCS_SmokeExhaust_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `pffjqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92160700GQP44'
				AND `wei`.`name_key` = 'SCS_SmokeExhaust_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `pffjgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92160700GQP44'
				AND `wei`.`name_key` = 'SCS_SmokeExhaust_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `pffjpb`,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '92170100NKI44'
			) AS `dtnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92170100NKI44'
				AND `wei`.`name_key` = 'ES_Elevator_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `dtqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92170100NKI44'
				AND `wei`.`name_key` = 'ES_Elevator_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `dtgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92170100NKI44'
				AND `wei`.`name_key` = 'ES_Elevator_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `dtpb`,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '92200100VAC44'
			) AS `ktnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200100VAC44'
				AND `wei`.`name_key` = 'AIRCS_AirCondition_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ktqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200100VAC44'
				AND `wei`.`name_key` = 'AIRCS_AirCondition_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ktgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200100VAC44'
				AND `wei`.`name_key` = 'AIRCS_AirCondition_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ktpb`,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '922002006IB44'
			) AS `ntnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '922002006IB44'
				AND `wei`.`name_key` = 'AIRCS_HVAC_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ntqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '922002006IB44'
				AND `wei`.`name_key` = 'AIRCS_HVAC_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ntgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '922002006IB44'
				AND `wei`.`name_key` = 'AIRCS_HVAC_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `ntpb`,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					`wed`.`code` = '92200300K6G44'
			) AS `mjnum`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200300K6G44'
				AND `wei`.`name_key` = 'ACS_AccessControl_Start'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `mjqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200300K6G44'
				AND `wei`.`name_key` = 'ACS_AccessControl_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `mjgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					`wed`.`code` = '92200300K6G44'
				AND `wei`.`name_key` = 'ACS_AccessControl_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `mjpb`
	) `d`;



-- ----------------------------
-- View structure for v_fire_alarm_equip_total_7d  弃用
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_alarm_equip_total_7d`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_alarm_equip_total_7d` AS select ifnull(`a`.`fightSysName`,NULL) AS `fightSysName`,ifnull(`a`.`create_date`,NULL) AS `createDate`,sum(if((`a`.`indxKey` = 'FireAlarm'),`a`.`total`,0)) AS `火灾告警`,sum(if((`a`.`indxKey` = 'Fault'),`a`.`total`,0)) AS `故障`,sum(if((`a`.`indxKey` = 'Shield'),`a`.`total`,0)) AS `屏蔽` from (select `sa`.`equipment_specific_index_key` AS `indxKey`,`sa`.`equipment_specific_index_name` AS `indexName`,count(distinct `sa`.`equipment_specific_id`) AS `total`,`sa`.`create_date` AS `create_date`,`fs`.`name` AS `fightSysName` from ((`wl_equipment_specific_alarm` `sa` left join `wl_equipment_specific` `ec` on((`sa`.`equipment_specific_id` = `ec`.`id`))) left join `f_fire_fighting_system` `fs` on(find_in_set(`fs`.`id`,`ec`.`system_id`))) where (((curdate() - interval 7 day) <= cast(`sa`.`create_date` as date)) and (`sa`.`equipment_specific_index_key` in ('FireAlarm','Fault','Shield')) and (`ec`.`system_id` is not null) and (`ec`.`system_id` <> '')) group by `sa`.`equipment_specific_index_key`,left(`sa`.`create_date`,10)) `a` group by `a`.`create_date`,`a`.`fightSysName` ;

-- ----------------------------
-- View structure for v_fire_cafs_num
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_cafs_num`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_cafs_num` AS 
	SELECT
	d.*
FROM
	(
		SELECT
			(
				SELECT
					wesi.`value`
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE 
					wes.equipment_code = '92032000N4344'
				AND wes.`name` LIKE '%CAFS1水箱%'         -- yeejoin 1号水箱名称模糊搜索，因为主机A和主机B都会上传水箱数据，所以需要进行模糊查询
				AND wesi.equipment_index_key = 'CAFS_WaterTank_WaterTankLevel'
				ORDER BY
					abs(wesi.`value`) DESC
				LIMIT 1
			) AS yhxfsxyw,
			(
					SELECT
					wesi.`value`
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wes.equipment_code = '92032000N4344'
				AND wes.`name` LIKE '%CAFS2水箱%' -- yeejoin 2号水箱名称模糊搜索，因为主机A和主机B都会上传水箱数据，所以需要进行模糊查询
				AND wesi.equipment_index_key = 'CAFS_WaterTank_WaterTankLevel'
				ORDER BY
					abs(wesi.`value`) DESC
				LIMIT 1
			) AS ehxfsxyw,
			(
					SELECT
					wesi.`value`
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wes.equipment_code = '92031900INJ44'
				AND wes.`name` LIKE '%CAFS1泡沫罐%' -- yeejoin 1号泡沫罐名称模糊搜索，因为主机A和主机B都会上传泡沫罐数据，所以需要进行模糊查询
				AND wesi.equipment_index_key = 'CAFS_FoamTank_FoamTankLevel'
					ORDER BY
					abs(wesi.`value`) DESC
				LIMIT 1
			) AS yhpmgyw,
			(
				SELECT
					wesi.`value`
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wes.equipment_code = '92031900INJ44'
				AND wes.`name` LIKE '%CAFS2泡沫罐%' -- yeejoin 2号泡沫罐名称模糊搜索，因为主机A和主机B都会上传泡沫罐数据，所以需要进行模糊查询
				AND wesi.equipment_index_key = 'CAFS_FoamTank_FoamTankLevel'
					ORDER BY
					abs(wesi.`value`) DESC
				LIMIT 1
			) AS ehpmgyw,
			(
				SELECT
					count(wes.id)
				FROM
					wl_equipment_specific wes 
				WHERE
					wes.equipment_code = '92030500MG644'
			) AS cszz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wesi.equipment_index_key = 'CAFS_FoamProductionDevice_Run'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030500MG644'
			) AS cszzyx,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamProductionDevice_Stop'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030500MG644'
			) AS cszztz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamProductionDevice_Fault'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030500MG644'
			) AS cszzgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamProductionDevice_Alarm'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030500MG644'
			) AS cszzgj,
			(
				SELECT
					count(wes.id)
				FROM
					wl_equipment_specific wes 
				WHERE
					wes.equipment_code = '92030700SFT44'
			) AS kqysj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamAirCompressor_Run'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030700SFT44'
			) AS kqysjyx,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamAirCompressor_Stop'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030700SFT44'
			) AS kqysjtz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamAirCompressor_Fault'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030700SFT44'
			) AS kqysjgz,
			(
				SELECT
					count(wes.id)
				FROM
					wl_equipment_specific wes WHERE
					 wes.equipment_code = '92030600QO344'
			) AS pmb,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamPump_Run'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030600QO344'
			) AS pmbyx,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamPump_Stop'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030600QO344'
			) AS pmbtz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_FoamPump_Fault'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030600QO344'
			) AS pmbgz,
			(
				SELECT
					count(wes.id)
				FROM
					wl_equipment_specific wes 
				WHERE
					wes.equipment_code = '92030300DI444'
			) AS qhf,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_SwitchValve_Close'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030300DI444'
			) AS qhfgdw,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_SwitchValve_Open'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030300DI444'
			) AS qhfkdw,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_SwitchValve_Fault'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030300DI444'
			) AS qhfgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE wesi.equipment_index_key = 'CAFS_SwitchValve_RemoteControl'
				AND wesi.`value` = 'true'
				AND wes.equipment_code = '92030300DI444'
			) AS qhfyf,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000034857'  -- yeejoin 主机A装备（消防智能控制主机1）24位编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS cafszja,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000037599'  -- yeejoin 主机B装备（消防智能控制主机2）24位编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS cafszjb,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036196' -- yeejoin 极一1号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jyyhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036202' -- yeejoin 极一2号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jyehxfp,
			(
					SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036219' -- yeejoin 极一3号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jyshxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036226' -- yeejoin 极一4号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jysshxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036233' -- yeejoin 极一5号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jywhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036240' -- yeejoin 极一6号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jylhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036257' -- yeejoin 极一7号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jyqhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036264' -- yeejoin 极一8号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jybhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036271' -- yeejoin 极二1号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jeyhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036288' -- yeejoin 极二2号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jeehxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036295' -- yeejoin 极二3号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jeshxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036301' -- yeejoin 极二4号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jesshxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036318' -- yeejoin 极二5号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jewhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036325' -- yeejoin 极二6号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jelhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036332' -- yeejoin 极二7号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jeqhxfp,
			(
				SELECT
					CONCAT(wesi.equipment_index_name,
					CASE
					WHEN wesi.value = 'true' THEN
						'（是）'
					WHEN wesi.value = 'false' THEN
						'（否）'
					ELSE
						wesi.value
					END 
				) AS fieldlabel
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE `wes`.code = '011023306003020000036349' -- yeejoin 极二8号消防炮装备编码
				ORDER BY
				wesi.update_date DESC
			LIMIT 1
			) AS jebhxfp
	) d;


-- ----------------------------
-- View structure for v_fire_equip_alarm_60list
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list`;
SELECT
	sal.id AS `id`,
	`ec`.`code` AS `mRid`,
	`sal`.`equipment_specific_name` AS `specificName`,
	`sal`.`equipment_specific_index_key` AS `indexKey`,
	`sal`.`equipment_specific_index_name` AS `indexName`,
	`sal`.`equipment_specific_id` AS `specificId`,
	concat(
		sal.equipment_specific_name,
		sal.equipment_specific_index_name
	) AS alamReason,
	`sal`.`iot_code` AS `iotCode`,
	date_format(
		`sal`.`create_date`,
		'%m-%d %H:%i:%s'
	) AS `createDate`,
	(
		SELECT
			GROUP_CONCAT(id)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysId`,
	(
		SELECT
			GROUP_CONCAT(`code`)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysMRid`,
	(
		SELECT
			GROUP_CONCAT(`name`)
		FROM
			f_fire_fighting_system fs
		WHERE
			find_in_set(`fs`.`id`, `ec`.`system_id`)
	) AS `fightSysName`
FROM
	wl_equipment_specific_alarm_log sal
LEFT JOIN `wl_equipment_specific` `ec` ON `sal`.`equipment_specific_id` = `ec`.`id`
ORDER BY
	`sal`.`create_date` DESC
LIMIT 60;

-- ----------------------------
-- View structure for v_fire_fas_num
-- ----------------------------
 DROP VIEW
 IF EXISTS `v_fire_fas_num`;
 CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_fas_num` AS 
SELECT
	d.*
FROM
	(
		SELECT
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '92041000LCW44'
			) AS sgbjqnum,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_SoundLightAlarm_Shield'
				AND s.`value` = 'true'
			) AS sgbjqpb,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_SoundLightAlarm_Fault'
				AND s.`value` = 'true'
			) AS sgbjqgz,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_SoundLightAlarm_FireAlarm'
				AND s.`value` = 'true'
			) AS sgbjqhj,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '92041200KNU44'
			) AS fbwgnum,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_TemperatureFireDetector_Shield'
				AND s.`value` = 'true'
			) AS fbwgpb,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_TemperatureFireDetector_Fault'
				AND s.`value` = 'true'
			) AS fbwggz,
			(
				SELECT
					count(1)
				FROM
					`wl_equipment_specific_index` s
				WHERE
					s.equipment_index_key = 'FAS_TemperatureFireDetector_FireAlarm'
				AND s.`value` = 'true'
			) AS fbwgqhj,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '92040700PEM44'
			) AS gwdlNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040700PEM44'
				AND wei.name_key = 'FAS_TemperatureCable_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS gwdlhj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040700PEM44'
				AND wei.name_key = 'FAS_TemperatureCable_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS gwdlgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040700PEM44'
				AND wei.name_key = 'FAS_TemperatureCable_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS gwdlpb,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '920402009EM44'
			) AS ywtcqNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920402009EM44'
				AND wei.name_key = 'FAS_SmokeAlarm_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS ywtcqhj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920402009EM44'
				AND wei.name_key = 'FAS_SmokeAlarm_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS ywtcqgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920402009EM44'
				AND wei.name_key = 'FAS_SmokeAlarm_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS ywtcqpb,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '920404002PU44'
			) AS sdbjaNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920404002PU44'
				AND wei.name_key = 'FAS_ManualAlarm_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS sdbjahj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920404002PU44'
				AND wei.name_key = 'FAS_ManualAlarm_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS sdbjagz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '920404002PU44'
				AND wei.name_key = 'FAS_ManualAlarm_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS sdbjapb,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '85010200FZJ44'
			) AS kqcyNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '85010200FZJ44'
				AND wei.name_key = 'FAS_AirSampler_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS kqcyhj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '85010200FZJ44'
				AND wei.name_key = 'FAS_AirSampler_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS kqcygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '85010200FZJ44'
				AND wei.name_key = 'FAS_AirSampler_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS kqcypb,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '92040800CZU44'
			) AS hwgsgyNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040800CZU44'
				AND wei.name_key = 'FAS_InfraredLight_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS hwgsgyhj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040800CZU44'
				AND wei.name_key = 'FAS_InfraredLight_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS hwgsgygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040800CZU44'
				AND wei.name_key = 'FAS_InfraredLight_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS hwgsgypb,
			(
				SELECT
					count(wed.id)
				FROM
					wl_equipment_detail wed
				WHERE
					wed.`code` = '92040900VUA44'
			) AS zwhyNum,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040900VUA44'
				AND wei.name_key = 'FAS_UltravioletLight_FireAlarm'
				AND upper(wesi.`value`) = 'TRUE'
			) AS zwhyhj,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040900VUA44'
				AND wei.name_key = 'FAS_UltravioletLight_Fault'
				AND upper(wesi.`value`) = 'TRUE'
			) AS zwhygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				WHERE
					wed.`code` = '92040900VUA44'
				AND wei.name_key = 'FAS_UltravioletLight_Shield'
				AND upper(wesi.`value`) = 'TRUE'
			) AS zwhypb
	) d;

-- ----------------------------
-- View structure for v_fire_ffs_num
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_ffs_num`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_ffs_num` AS 
SELECT
	d.*
FROM
	(
		SELECT
			(
				SELECT
					count(DISTINCT(wed.id))
				FROM
				wl_equipment_detail wed 
				WHERE wed.code  = '92110200UIU44'
			) AS dcfzs,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE wed.code  = '92110200UIU44'
				AND wei.name_key = 'FFS_SolenoidValve_Start'
				AND upper(wesi.value ) = 'TRUE'
			) AS dcfqd,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110200UIU44'
				AND wei.name_key = 'FFS_SolenoidValve_Fault'
				AND upper(wesi.value) = 'TRUE'
			) AS dcfgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110200UIU44'
				AND wei.name_key = 'FFS_SolenoidValve_Shield'
				AND upper(wesi.value ) = 'TRUE'
			) AS dcfpb,
			(
				SELECT
					count(DISTINCT(wed.id))
				FROM
				wl_equipment_detail wed 
				WHERE wed.code  = '92110300SS744'
			) AS qdpzs,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110300SS744'
				AND wei.name_key = 'FFS_Startbottle_Start'
				AND upper(wesi.value ) = 'TRUE'
			) AS qdpqd,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110300SS744'
				AND wei.name_key = 'FFS_Startbottle_StartupModuleFault'
				AND upper(wesi.value ) = 'TRUE'
			) AS qdpgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110300SS744'
				AND wei.name_key = 'FFS_Startbottle_StartupModuleShield'
				AND upper(wesi.value ) = 'TRUE'
			) AS qdppb,
			(
				SELECT
					count(DISTINCT(wed.id))
				FROM
				wl_equipment_detail wed 
				WHERE wed.code  = '92110400CRQ44'
			) AS dlqzs,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110400CRQ44'
				AND wei.name_key = 'FFS_Breaker_State'
				AND upper(wesi.value ) = 'TRUE'
			) AS dlqqd,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code = '92110400CRQ44'
				AND wei.name_key = 'FFS_Breaker_State'
				AND upper(wesi.value ) = 'FALSE'
			) AS dlqjt,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110400CRQ44'
				AND wei.name_key = 'FFS_Breaker_Fault'
				AND upper(wesi.value ) = 'TRUE'
			) AS dlqgz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wed.code  = '92110400CRQ44'
				AND wei.name_key = 'FFS_Breaker_Shield'
				AND upper(wesi.value ) = 'TRUE'
			) AS dlqpb
	) d;

-- ----------------------------
-- View structure for v_fire_fhs_num
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_fhs_num`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_fhs_num` AS 
SELECT
	`d`.`xfsbzs` AS `xfsbzs`,
	`d`.`xfsbqd` AS `xfsbqd`,
	`d`.`xfsbjt` AS `xfsbjt`,
	`d`.`xfsbgz` AS `xfsbgz`,
	`d`.`xfsbpb` AS `xfsbpb`,
	`d`.`wybzs` AS `wybzs`,
	`d`.`wybqd` AS `wybqd`,
	`d`.`wybjt` AS `wybjt`,
	`d`.`wybgz` AS `wybgz`,
	`d`.`wybpb` AS `wybpb`,
	`d`.`xhszs` AS `xhszs`,
	`d`.`xhshj` AS `xhshj`,
	`d`.`xhsgz` AS `xhsgz`,
	`d`.`xhspb` AS `xhspb`
FROM
	(
		SELECT
			(
				SELECT
					count(ed.`id`)
				FROM
					wl_equipment_detail ed
				WHERE
					`ed`.`code` = '920106007GV44'
			) AS `xfsbzs`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '920106007GV44'
				AND `wei`.`name_key` = 'FHS_FirePump_RunStatus'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xfsbqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '920106007GV44'
				AND `wei`.`name_key` = 'FHS_FirePump_Stop'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xfsbjt`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '920106007GV44'
				AND `wei`.`name_key` = 'FHS_FirePump_RunFault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xfsbgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '920106007GV44'
				AND `wei`.`name_key` = 'FHS_FirePump_RunShield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xfsbpb`,
			(
				SELECT
					count(ed.`id`)
				FROM
					wl_equipment_detail ed
				WHERE
					`ed`.`code` = '92010800KAL44'
			) AS `wybzs`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92010800KAL44'
				AND `wei`.`name_key` = 'FHS_PressurePump_RunStatus'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `wybqd`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92010800KAL44'
				AND `wei`.`name_key` = 'FHS_PressurePump_Stop'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `wybjt`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92010800KAL44'
				AND `wei`.`name_key` = 'FHS_PressurePump_RunFault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `wybgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92010800KAL44'
				AND `wei`.`name_key` = 'FHS_PressurePump_RunShield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `wybpb`,
			(
				SELECT
					count(ed.`id`)
				FROM
					wl_equipment_detail ed
				WHERE
					`ed`.`code` = '92040600ANG44'
			) AS `xhszs`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92040600ANG44'
				AND `wei`.`name_key` = 'FAS_FireHydrantButton_FireAlarm'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xhshj`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92040600ANG44'
				AND `wei`.`name_key` = 'FAS_FireHydrantButton_Fault'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xhsgz`,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_detail wed ON wed.id = wes.equipment_detail_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					`wed`.`code` = '92040600ANG44'
				AND `wei`.`name_key` = 'FAS_FireHydrantButton_Shield'
				AND upper(`wesi`.`value`) = 'TRUE'
			) AS `xhspb`
	) `d`;
-- ----------------------------
-- View structure for v_fire_fhs_yl_num
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_fhs_yl_num`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_fhs_yl_num` AS 

SELECT
	ROUND((d.yhxfscyw/d.yhxfscg)*100) AS yhxfscyl,
	ROUND((d.ehxfscyw/d.ehxfscg)*100)  AS ehxfscyl,
	d.yhxfscyw,
	d.ehxfscyw
FROM
	(
		SELECT
			
			(
				SELECT
					field_value AS height
				FROM
					wl_form_instance
				WHERE
					field_name = 'height'
				AND instance_id = (
					SELECT
						instance_id
					FROM
						wl_form_instance
					WHERE
						field_name = 'name'
					AND field_value = '1#消防水池' -- 水池名称
				)
			) AS yhxfscg,
			(
				SELECT
					field_value AS height
				FROM
					wl_form_instance
				WHERE
					field_name = 'height'
				AND instance_id = (
					SELECT
						instance_id
					FROM
						wl_form_instance
					WHERE
						field_name = 'name' 
					AND field_value = '2#消防水池' -- yeejoin 配置水池名称
				)
			) AS ehxfscg,
			(
				SELECT
					wesi.`value`

				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					wes.`code` = '92010700SHY44' -- yeejoin 配置1号水池液位采集装置装备编码
				AND wei.name_key = 'FHS_FirePoolDevice_WaterLevel'
			) AS yhxfscyw,
			(
				SELECT
					wesi.`value`
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				LEFT JOIN wl_equipment_index wei ON wei.id = wesi.equipment_index_id
				WHERE
					-- 配置2号水池液位采集装置装备编码
					wes.`code` = '92010700SHY44'
				AND wei.name_key = 'FHS_FirePoolDevice_WaterLevel'
			) AS ehxfscyw
	) d;

-- ----------------------------
-- View structure for v_fire_firecar_num
-- ----------------------------
DROP VIEW
IF EXISTS `v_fire_firecar_num`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_firecar_num` AS
SELECT
	car.id,
	car.`name`,
	car.car_num,
	(
		SELECT
			(
				CASE
				WHEN (
					DATEDIFF(CURDATE(), carm.update_date) <= 7
				) THEN
					'待命'
				ELSE
					'异常'
				END
			)
		FROM
			wl_car_property carm
		LEFT JOIN wl_equipment_index ei ON ei.id = carm.equipment_index_id
		WHERE
			ei.name_key = 'FireCar_Start'
		AND carm.car_id = car.id
	) as 'status',
	(
		SELECT
			(
				CASE
				WHEN carm.
				VALUE
					= 'true' THEN
						'启动'
					ELSE
						'停止'
					END
			) 
		FROM
			wl_car_property carm
		LEFT JOIN wl_equipment_index ei ON ei.id = carm.equipment_index_id
		WHERE
			ei.name_key = 'FireCar_Start'
		AND carm.car_id = car.id
	) AS startup,
	(
		SELECT
			DATE_FORMAT(
				carm.update_date,
				"%Y-%m-%d %H:%i:%s"
			) 
		FROM
			wl_car_property carm
		LEFT JOIN wl_equipment_index ei ON ei.id = carm.equipment_index_id
		WHERE
			ei.name_key = 'FireCar_Start'
		AND carm.car_id = car.id
	) AS startdate,
	(
		SELECT
			(
				CASE
				WHEN carm.`value` IS NULL THEN
					'异常'
				ELSE
					'正常'
				END
			)
		FROM
			wl_car_property carm
		LEFT JOIN wl_equipment_index ei ON ei.id = carm.equipment_index_id
		WHERE
			(
				ei.name_key = 'FireCar_GDLatitude'
				OR ei.name_key = 'FireCar_GDLongitude'
			)
		AND carm.car_id = car.id
		LIMIT 1
	) AS location
FROM
	wl_car AS car;
-- ----------------------------
-- View structure for v_fire_onl_num
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_onl_num`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_onl_num` AS 
SELECT
	d.*
FROM
	(
		SELECT
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE 
					wes.position like '%极Ⅰ高端%'  
				AND (
					wesi.equipment_index_key = 'ONL_DCPower_Fault'
					OR wesi.equipment_index_key = 'ONL_ACPower_Fault'
				)
				AND wesi.`value`= 'true'
			) AS jygddygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position like '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND wesi.`value`= 'true'
			) AS jygdfmqk,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND wesi.`value`= 'true'
			) AS jygdlyxh,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND wesi.`value`= 'true'
			) AS jygdfmqb,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ低端%'
				AND (
					wesi.equipment_index_key = 'ONL_DCPower_Fault'
					OR wesi.equipment_index_key = 'ONL_ACPower_Fault'
				)
				AND wesi.`value`= 'true'
			) AS jydddygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND wesi.`value`= 'true'
			) AS jyddfmqk,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND wesi.`value`= 'true'
			) AS jyddlyxh,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND wesi.`value`= 'true'
			) AS jyddfmqb,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ高端%'
				AND (
					wesi.equipment_index_key = 'ONL_DCPower_Fault'
					OR wesi.equipment_index_key = 'ONL_ACPower_Fault'
				)
				AND wesi.`value`= 'true'
			) AS jegddygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND wesi.`value`= 'true'
			) AS jegdfmqk,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND wesi.`value`= 'true'
			) AS jegdlyxh,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND wesi.`value`= 'true'
			) AS jegdfmqb,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ低端%'
				AND (
					wesi.equipment_index_key = 'ONL_DCPower_Fault'
					OR wesi.equipment_index_key = 'ONL_ACPower_Fault'
				)
				AND wesi.`value`= 'true'
			) AS jedddygz,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE

				wes.position like '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND wesi.`value`= 'true'
			) AS jeddfmqk,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND wesi.`value`= 'true'
			) AS jeddlyxh,
			(
				SELECT
					count(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
				wes.position like '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND wesi.`value`= 'true'
			) AS jeddfmqb
	) d;

-- ----------------------------
-- View structure for v_fire_wsp_fightingsys_equip_count
-- ----------------------------
DROP VIEW
IF EXISTS `v_fire_wsp_fightingsys_equip_count`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_wsp_fightingsys_equip_count` AS
SELECT
	SUM(
		CASE
		WHEN temp.`value` = 'true' THEN
			1
		ELSE
			0
		END
	) AS total,
	temp.fieldlabel,
	temp.fieldname
FROM
	(
		SELECT
			CASE
		WHEN
			esi.equipment_index_key like '%Open' THEN
				'Open'
				WHEN esi.equipment_index_key like '%FireAlarm' THEN
				'FireAlarm'
				WHEN esi.equipment_index_key like '%WarehouseSuccess' THEN
				'WarehouseSuccess'
				WHEN esi.equipment_index_key like '%Start' THEN
				'Start'
				WHEN esi.equipment_index_key like '%Fault' THEN
				'Fault'
			END AS fieldname,
		CASE
		WHEN
			esi.equipment_index_key like '%Open' THEN
				'开启'
				WHEN esi.equipment_index_key like '%FireAlarm' THEN
				'火警'
				WHEN esi.equipment_index_key like '%WarehouseSuccess' THEN
				'出舱'
				WHEN esi.equipment_index_key like '%Start' THEN
				'启动'
				WHEN esi.equipment_index_key like '%Fault' THEN
				'故障'
			END AS  fieldlabel,
	esi.`value`
FROM
	wl_equipment_specific_index esi
LEFT JOIN wl_equipment_specific es ON es.id = esi.equipment_specific_id
LEFT JOIN f_fire_fighting_system fs ON find_in_set(`fs`.`id`, `es`.`system_id`)
WHERE
	fs.`code` = '029026401813010000000061'
AND (
	esi.equipment_index_key LIKE '%Open'
	OR esi.equipment_index_key LIKE '%FireAlarm'
	OR esi.equipment_index_key LIKE '%WarehouseSuccess'
OR esi.equipment_index_key LIKE '%Start'
OR esi.equipment_index_key LIKE '%Fault'
)
	) temp
GROUP BY
	temp.fieldlabel,
	temp.fieldname;
	-- ----------------------------
-- View structure for v_fire_water_fightingsys_equip_count
-- ----------------------------
DROP VIEW
IF EXISTS `v_fire_water_fightingsys_equip_count`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_water_fightingsys_equip_count` AS
SELECT
	SUM(
		CASE
		WHEN temp.`value` = 'true' THEN
			1
		ELSE
			0
		END
	) AS total,
	temp.fieldlabel,
	temp.fieldname
FROM
	(
		SELECT
			CASE
		WHEN
			esi.equipment_index_key like '%FireAlarm' THEN
				'FireAlarm'
				WHEN esi.equipment_index_key like '%Shield' THEN
				'Shield'
				WHEN esi.equipment_index_key like '%Fault' THEN
				'Fault'
			END AS fieldname,
		CASE
		WHEN
				esi.equipment_index_key like '%FireAlarm' THEN
				'火警'
				WHEN esi.equipment_index_key like '%Shield' THEN
				'屏蔽'
				WHEN esi.equipment_index_key like '%Fault' THEN
				'故障'
			END AS  fieldlabel,
	esi.`value`
FROM
	wl_equipment_specific_index esi
LEFT JOIN wl_equipment_specific es ON es.id = esi.equipment_specific_id
LEFT JOIN f_fire_fighting_system fs ON find_in_set(`fs`.`id`, `es`.`system_id`)
WHERE
	fs.`code` = '029026401813010000000061'
AND (
	 esi.equipment_index_key LIKE '%FireAlarm'
	OR esi.equipment_index_key LIKE '%Shield'
OR esi.equipment_index_key LIKE '%Fault'
)
	) temp
GROUP BY
	temp.fieldlabel,
	temp.fieldname
	-- ----------------------------
-- View structure for v_fire_paomo_fightingsys_equip_count
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_paomo_fightingsys_equip_count`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_paomo_fightingsys_equip_count` AS 
SELECT
	SUM(
		CASE
		WHEN temp.`value` = 'true' THEN
			1
		ELSE
			0
		END
	) AS total,
	temp.fieldlabel,
	temp.fieldname
FROM
	(
		SELECT
			CASE
		WHEN (
			esi.equipment_index_key LIKE '%FireAlarm'
		) THEN
			'FireAlarm'
		WHEN (
			esi.equipment_index_key LIKE '%Shield'
		) THEN
			'Shield'
		WHEN (
			esi.equipment_index_key LIKE '%Fault'
		) THEN
			'Fault'
		WHEN (
			esi.equipment_index_key LIKE '%Start'
		) THEN
			'Start'
		ELSE
			NULL
		END AS fieldname,
		CASE
	WHEN (
		esi.equipment_index_key LIKE '%FireAlarm'
	) THEN
		'火警'
	WHEN (
		esi.equipment_index_key LIKE '%Shield'
	) THEN
		'屏蔽'
	WHEN (
		esi.equipment_index_key LIKE '%Fault'
	) THEN
		'故障'
	WHEN (
		esi.equipment_index_key LIKE '%Start'
	) THEN
		'启动'
	ELSE
		NULL
	END AS fieldlabel,
	esi.`value`
FROM
	wl_equipment_specific_index esi
LEFT JOIN wl_equipment_specific es ON es.id = esi.equipment_specific_id
LEFT JOIN f_fire_fighting_system fs ON find_in_set(`fs`.`id`, `es`.`system_id`)
WHERE
	fs.`code` = '029026401813010000000054'
AND (
	esi.equipment_index_key LIKE '%Shield'
	OR esi.equipment_index_key LIKE '%Start'
	OR esi.equipment_index_key LIKE '%FireAlarm'
	OR esi.equipment_index_key LIKE '%Fault'
)
	) temp
GROUP BY
	temp.fieldlabel,
	temp.fieldname
-- ----------------------------
-- View structure for v_fire_paiyou_fightingsys_equip_count
-- ----------------------------
DROP VIEW
IF EXISTS `v_fire_paiyou_fightingsys_equip_count`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_paiyou_fightingsys_equip_count` AS 
SELECT
	SUM(
		CASE
		WHEN temp.`value` = 'true' THEN
			1
		ELSE
			0
		END
	) AS total,
	temp.fieldlabel,
	temp.fieldname
FROM
	(
		SELECT
			CASE
		WHEN (
			esi.equipment_index_key LIKE '%Open'
		) THEN
			'Open'
		WHEN (
			esi.equipment_index_key LIKE '%Close'
		) THEN
			'Close'
		WHEN (
			esi.equipment_index_key LIKE '%Fault'
		) THEN
			'Fault'
		WHEN (
			esi.equipment_index_key LIKE '%OilLeak'
		) THEN
			'OilLeak'
		ELSE
			NULL
		END AS fieldname,
		CASE
	WHEN (
		esi.equipment_index_key LIKE '%Open'
	) THEN
		'阀开启'
	WHEN (
		esi.equipment_index_key LIKE '%Close'
	) THEN
		'阀关闭'
	WHEN (
		esi.equipment_index_key LIKE '%Fault'
	) THEN
		'故障'
	WHEN (
		esi.equipment_index_key LIKE '%OilLeak'
	) THEN
		'漏油'
	ELSE
		NULL
	END AS fieldlabel,
	esi.`value`
FROM
	wl_equipment_specific_index esi
LEFT JOIN wl_equipment_specific es ON es.id = esi.equipment_specific_id
LEFT JOIN f_fire_fighting_system fs ON find_in_set(`fs`.`id`, `es`.`system_id`)
WHERE
	fs.`code` = '029026401813010000000030'
AND (
	esi.equipment_index_key LIKE '%Open'
	OR esi.equipment_index_key LIKE '%Close'
	OR esi.equipment_index_key LIKE '%OilLeak'
	OR esi.equipment_index_key LIKE '%Fault'
)
	) temp
GROUP BY
	temp.fieldlabel,
	temp.fieldname
	-- ----------------------------
-- View structure for v_fire_fightingsys_equip_count
-- ----------------------------
-- 火灾报警系统
	DROP VIEW
IF EXISTS `v_fire_fightingsys_equip_count`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_fightingsys_equip_count` AS
SELECT
	SUM(
		CASE
		WHEN temp.`value` = 'true' THEN
			1
		ELSE
			0
		END
	) AS total,
	temp.fieldlabel,
	temp.fieldname
FROM
	(
		SELECT
			CASE
		WHEN (
			esi.equipment_index_key LIKE '%FireAlarm'
		) THEN
			'FireAlarm'
		WHEN (
			esi.equipment_index_key LIKE '%Fault'
		) THEN
			'Fault'
		WHEN (
			esi.equipment_index_key LIKE '%Shield'
		) THEN
			'Shield'
		ELSE
			NULL
		END AS fieldname,
		CASE
	WHEN (
		esi.equipment_index_key LIKE '%FireAlarm'
	) THEN
		'火灾报警'
	WHEN (
		esi.equipment_index_key LIKE '%Fault'
	) THEN
		'故障'
	WHEN (
		esi.equipment_index_key LIKE '%Shield'
	) THEN
		'屏蔽'
	ELSE
		NULL
	END AS fieldlabel,
	esi.`value`
FROM
	wl_equipment_specific_index esi
LEFT JOIN wl_equipment_specific es ON es.id = esi.equipment_specific_id
LEFT JOIN f_fire_fighting_system fs ON find_in_set(`fs`.`id`, `es`.`system_id`)
WHERE
	fs.`code` = '029026401813010000000016'
AND (
	esi.equipment_index_key LIKE '%FireAlarm'
	OR esi.equipment_index_key LIKE '%Fault'
	OR esi.equipment_index_key LIKE '%Shield'
)
	) temp
GROUP BY
	temp.fieldlabel,
	temp.fieldname;
	-- ----------------------------
-- View structure for v_fire_cafs_fightingsys_equip_count
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_cafs_fightingsys_equip_count`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_cafs_fightingsys_equip_count` AS
SELECT
	sum(
		(
			CASE
			WHEN (`temp`.`value` = 'true') THEN
				1
			ELSE
				0
			END
		)
	) AS `total`,
	`temp`.`fieldlabel` AS `fieldlabel`,
	`temp`.`fieldname` AS `fieldname`
FROM
	(
		SELECT
			(
				CASE
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Run'
				) THEN
					'Run'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Stop'
				) THEN
					'Stop'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%_Open'
				) THEN
					'Open'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Close'
				) THEN
					'Close'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Fault'
				) THEN
					'Fault'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%_RemoteControl'
				) THEN
					'RemoteControl'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Alarm'
				) THEN
					'Alarm'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Abnormal'
				) THEN
					'Abnormal'
				END
			) AS `fieldname`,
			(
				CASE
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Run'
				) THEN
					'运行'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Stop'
				) THEN
					'停止'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%_Open'
				) THEN
					'开到位'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Close'
				) THEN
					'关到位'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Fault'
				) THEN
					'故障'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%_RemoteControl'
				) THEN
					'远方'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Alarm'
				) THEN
					'告警'
				WHEN (
					`esi`.`equipment_index_key` LIKE '%Abnormal'
				) THEN
					'异常'
				END
			) AS `fieldlabel`,
			`esi`.`value` AS `value`
		FROM
			(
				(
					`wl_equipment_specific_index` `esi`
					LEFT JOIN `wl_equipment_specific` `es` ON (
						(
							`es`.`id` = `esi`.`equipment_specific_id`
						)
					)
				)
				LEFT JOIN `f_fire_fighting_system` `fs` ON (
					(
						0 <> find_in_set(`fs`.`id`, `es`.`system_id`)
					)
				)
			)
		WHERE
			(
				(
					`fs`.`code` = '029026401813010000000023'
				)
				AND (
					(
						`esi`.`equipment_index_key` LIKE '%Run'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%Stop'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%_Open'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%Close'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%Fault'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%_RemoteControl'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%Alarm'
					)
					OR (
						`esi`.`equipment_index_key` LIKE '%Abnormal'
					)
				)
			)
	) `temp`
GROUP BY
	`temp`.`fieldlabel`,
	`temp`.`fieldname`;
	-- ----------------------------
-- View structure for v_person_plan_task
-- ----------------------------
DROP VIEW IF EXISTS `v_person_plan_task`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_person_plan_task` AS select `pt`.`id` AS `taskId`,substring_index(substring_index(`pt`.`user_id`,',',(`pt2`.`help_topic_id` + 1)),',',-(1)) AS `user_id`,`pt`.`check_date` AS `check_date` from (`p_plan_task` `pt` join `mysql`.`help_topic` `pt2` on((`pt2`.`help_topic_id` < ((length(`pt`.`user_id`) - length(replace(`pt`.`user_id`,',',''))) + 1)))) ;


-- ----------------------------
-- View structure for v_risk_level
-- ----------------------------
DROP VIEW IF EXISTS `v_risk_level`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_risk_level` AS select (case when (abs(((`f_risk_source`.`rpn` / 10) - 100)) between 0 and 49.9) then '高风险' when (abs(((`f_risk_source`.`rpn` / 10) - 100)) between 50 and 89.9) then '中风险' when (abs(((`f_risk_source`.`rpn` / 10) - 100)) between 90 and 94.9) then '低风险' else '安全' end) AS `safetyLevel` from `f_risk_source` where ((`f_risk_source`.`org_code` = '10') and ((`f_risk_source`.`parent_id` = 0) or isnull(`f_risk_source`.`parent_id`))) ;

-- ----------------------------
-- View structure for v_fire_system_state
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_system_state`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_system_state` AS 
SELECT d.* FROM(
SELECT
(SELECT CASE WHEN (jygddygz > 0 OR jygdlyxh > 0 OR jydddygz > 0 OR jyddlyxh > 0 OR jegddygz > 0 OR jegdlyxh > 0 OR jedddygz > 0 OR jeddlyxh > 0)
THEN '异常'
ELSE '正常' END AS onlState
FROM v_fire_onl_num) AS onlState,
(SELECT CASE WHEN (dcfgz > 0 OR dcfpb > 0 OR qdpgz > 0 OR qdppb > 0 OR dlqqd > 0 OR dlqgz > 0 OR dlqpb > 0)
THEN '异常'
ELSE '正常' END AS ffsState
FROM v_fire_ffs_num) AS ffsState,
(SELECT CASE WHEN (xfsbqd > 0 OR xfsbgz > 0 OR xfsbpb > 0 OR wybqd > 0 OR wybgz > 0 OR wybpb > 0 OR xhshj > 0 OR xhsgz > 0 OR xhspb > 0)
THEN '异常'
ELSE '正常' END AS fhState
FROM v_fire_fhs_num) AS fhState,
(SELECT CASE WHEN (cszzgz > 0 OR cszzgj > 0 OR kqysjgz > 0 OR pmbgz > 0 OR qhfgz > 0)
THEN '异常'

ELSE
	'正常'
END AS cafsState
FROM v_fire_cafs_num) AS cafsState,
(SELECT CASE WHEN (gwdlhj > 0 OR gwdlgz > 0 OR gwdlpb > 0 OR ywtcqhj > 0 OR ywtcqgz > 0 OR ywtcqpb > 0 OR sdbjahj > 0 OR sdbjagz > 0 OR sdbjapb > 0 OR kqcyhj > 0 OR kqcygz > 0 OR kqcypb > 0 OR hwgsgyhj > 0 OR hwgsgygz > 0 OR hwgsgypb > 0 OR zwhyhj > 0 OR zwhygz > 0 OR zwhypb > 0)
THEN '异常'
ELSE '正常' END AS fasState
FROM v_fire_fas_num) AS fasState,
(SELECT CASE WHEN (sffjgz > 0 OR sffjpb > 0 OR pffjgz > 0 OR pffjpb > 0 OR dtgz > 0 OR dtpb > 0 OR ktgz > 0 OR ktpb > 0 OR ntgz > 0 OR ntpb > 0 OR mjgz > 0 OR mjpb > 0)
THEN '异常'
ELSE '正常' END AS acsState
FROM v_fire_acs_num) AS acsState
) d ;

-- ----------------------------
-- View structure for v_fire_patrol_point_total_30d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_patrol_point_total_30d`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_patrol_point_total_30d` AS select date_format(`detail`.`create_date`,'%m-%d') AS `createDate`,count(distinct `detail`.`point_id`) AS `pointNum`,count(distinct `ppc`.`equipment_id`) AS `equipmentNum` from (`p_plan_task_detail` `detail` left join `p_point_classify` `ppc` on((`detail`.`point_id` = `ppc`.`point_id`))) where ((date_format(`detail`.`create_date`,'%Y-%m-%d') > (curdate() - interval 30 day)) and (date_format(`detail`.`create_date`,'%Y-%m-%d') < (curdate() + interval 1 day))) group by date_format(`detail`.`create_date`,'%Y-%m-%d') order by `detail`.`create_date` desc;

-- ----------------------------
-- View structure for v_fire_check_total_30d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_check_total_30d`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_check_total_30d` AS select date_format(`p_plan_task_detail`.`create_date`,'%m-%d') AS `createDate`,count(1) AS `planNum`,ifnull(sum((case when (`p_plan_task_detail`.`is_finish` = 1) then 1 else 0 end)),0) AS `finishNum`,ifnull(sum((case when (`p_plan_task_detail`.`is_finish` = 2) then 1 else 0 end)),0) AS `unFinishNum` from `p_plan_task_detail` where ((date_format(`p_plan_task_detail`.`create_date`,'%Y-%m-%d') > (curdate() - interval 30 day)) and (date_format(`p_plan_task_detail`.`create_date`,'%Y-%m-%d') < (curdate() + interval 1 day))) group by date_format(`p_plan_task_detail`.`create_date`,'%Y-%m-%d') order by `createDate` desc;


-- ----------------------------
-- View structure for v_fire_cafssys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_cafssys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_cafssys_alarm_equip_total_7d` AS 
SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`Fault`, 0) AS `Fault`,
	ifnull(`b`.`Alarm`, 0) AS `Alarm`,
	ifnull(`b`.`Breakline`, 0) AS `Breakline`,
	ifnull(`b`.`Abnormal`, 0) AS `Abnormal`,
	ifnull(`b`.`PowerLoss`, 0) AS `PowerLoss`
FROM
	(
		(
			SELECT
				curdate() AS `click_date`
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`,
				sum(

					IF (
						locate('Alarm', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Alarm`,
				sum(

					IF (
						locate('Breakline', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Breakline`,
				sum(

					IF (
						locate('Abnormal', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Abnormal`,
				sum(

					IF (
						locate('PowerLoss', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `PowerLoss`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '011023306003010000000075'
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Fault'
								)
								OR (
									`rd`.`index_type` LIKE '%Alarm'
								)
								OR (
									`rd`.`index_type` LIKE '%Breakline'
								)
								OR (
									`rd`.`index_type` LIKE '%Abnormal'
								)
								OR (
									`rd`.`index_type` LIKE '%PowerLoss'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- ----------------------------
-- View structure for v_fire_fightingsys_equip_index_status_mes_all
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_fightingsys_equip_index_status_mes_all`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_fightingsys_equip_index_status_mes_all` AS 
SELECT
	temp.*, 
	fs.`name` as 'systemName',
	fs.`id` as 'systemId',
	fs.`code` as 'fscode',
	(SELECT
	IFNULL(SUM(frequency), 0)
FROM
	wl_equipment_alarm_report_day
WHERE
	equipment_specific_id = temp.equipment_specific_id
AND report_date = CURRENT_DATE) as 'total'
FROM
	(
		SELECT
			a.equipment_specific_name AS 'equipName',
			(
				CASE a.`value`
				WHEN 'true' THEN
					concat(
						a.equipment_index_name,
						'(是)'
					)
				WHEN 'false' THEN
					concat(
						a.equipment_index_name,
						'(否)'
					)
				END
			) AS `nowStatus`,
			a.equipment_specific_id,
			a.update_date
		FROM
			wl_equipment_specific_index a,
			(
				SELECT
					equipment_specific_id,
					MAX(update_date) AS update_date
				FROM
					wl_equipment_specific_index
				GROUP BY
					equipment_specific_id
			) b
		WHERE
			a.`value` IS NOT NULL
		AND a.equipment_specific_id = b.equipment_specific_id
		AND a.update_date = b.update_date
		GROUP BY
			a.equipment_specific_id
		ORDER BY
			a.update_date DESC
	) temp,
	wl_equipment_specific es
LEFT JOIN `f_fire_fighting_system` `fs` ON (
	find_in_set(`fs`.`id`, es.system_id)
)
WHERE
	temp.equipment_specific_id = es.id
ORDER BY temp.update_date DESC;

-- ----------------------------
-- View structure for v_fire_paiyousys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_paiyousys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_paiyousys_alarm_equip_total_7d` AS 
SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`Open`, 0) AS `Open`,
	ifnull(`b`.`Close`, 0) AS `Close`,
	ifnull(`b`.`OilLeak`, 0) AS `OilLeak`,
	ifnull(`b`.`Fault`, 0) AS `Fault`
FROM
	(
		(
			SELECT
				curdate() AS `click_date`
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Open', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Open`,
				sum(

					IF (
						locate('Close', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Close`,
				sum(

					IF (
						locate('OilLeak', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `OilLeak`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '011023306003010000000020'
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Open'
								)
								OR (
									`rd`.`index_type` LIKE '%Close'
								)
								OR (
									`rd`.`index_type` LIKE '%OilLeak'
								)
								OR (
									`rd`.`index_type` LIKE '%Fault'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- ----------------------------
-- View structure for v_fire_paomosys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_paomosys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_paomosys_alarm_equip_total_7d` AS 
SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`FireAlarm`, 0) AS `FireAlarm`,
	ifnull(`b`.`Fault`, 0) AS `Fault`,
	ifnull(`b`.`Shield`, 0) AS `Shield`,
	ifnull(`b`.`Startup`, 0) AS `Startup`,
	ifnull(`b`.`State`, 0) AS `State`
FROM
	(
		(
			SELECT
				curdate() AS `click_date`
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`,
				sum(

					IF (
						locate('FireAlarm', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `FireAlarm`,
				sum(

					IF (
						locate('Shield', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Shield`,
				sum(

					IF (
						locate('Start', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Startup`,
				sum(

					IF (
						locate('State', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `State`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '011023306003010000000082'
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Fault'
								)
								OR (
									`rd`.`index_type` LIKE '%FireAlarm'
								)
								OR (
									`rd`.`index_type` LIKE '%Shield'
								)
								OR (
									`rd`.`index_type` LIKE '%Start'
								)
								OR (
									`rd`.`index_type` LIKE '%State'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- ----------------------------
-- View structure for v_fire_sys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_sys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_sys_alarm_equip_total_7d` AS 
SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`FireAlarm`, 0) AS `FireAlarm`,
	ifnull(`b`.`Fault`, 0) AS `Fault`,
	ifnull(`b`.`Shield`, 0) AS `Shield`
FROM
	(
		(
			SELECT
				curdate() AS `click_date`
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`,
				sum(

					IF (
						locate('FireAlarm', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `FireAlarm`,
				sum(

					IF (
						locate('Shield', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Shield`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '029026401813010000000016'
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Fault'
								)
								OR (
									`rd`.`index_type` LIKE '%FireAlarm'
								)
								OR (
									`rd`.`index_type` LIKE '%Shield'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- ----------------------------
-- View structure for v_fire_watersys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_watersys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_watersys_alarm_equip_total_7d` AS 
SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`FireAlarm`, 0) AS `FireAlarm`,
	ifnull(`b`.`Fault`, 0) AS `Fault`,
	ifnull(`b`.`Shield`, 0) AS `Shield`,
	ifnull(`b`.`RunFault`, 0) AS `RunFault`,
	ifnull(`b`.`RunShield`, 0) AS `RunShield`,
	ifnull(`b`.`DeviceAbnormal`, 0) AS `DeviceAbnormal`,
	ifnull(`b`.`DeviceLock`, 0) AS `DeviceLock`
FROM
	(
		(
			SELECT
				curdate() AS `click_date`
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`,
				sum(

					IF (
						locate('FireAlarm', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `FireAlarm`,
				sum(

					IF (
						locate('Shield', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Shield`,
				sum(

					IF (
						locate('RunFault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `RunFault`,
				sum(

					IF (
						locate('RunShield', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `RunShield`,
				sum(

					IF (
						locate('Abnormal', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `DeviceAbnormal`,
				sum(

					IF (
						locate('DeviceLock', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `DeviceLock`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '011023306003010000000068'
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Fault'
								)
								OR (
									`rd`.`index_type` LIKE '%FireAlarm'
								)
								OR (
									`rd`.`index_type` LIKE '%Shield'
								)
								OR (
									`rd`.`index_type` LIKE '%RunFault'
								)
								OR (
									`rd`.`index_type` LIKE '%RunShield'
								)
								OR (
									`rd`.`index_type` LIKE '%Abnormal'
								)
								OR (
									`rd`.`index_type` LIKE '%DeviceLock'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- ----------------------------
-- View structure for v_fire_wspsys_alarm_equip_total_7d
-- ----------------------------
DROP VIEW IF EXISTS `v_fire_wspsys_alarm_equip_total_7d`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_fire_wspsys_alarm_equip_total_7d` AS SELECT
	ifnull(`b`.`fightSysId`, NULL) AS `fightSysId`,
	ifnull(`b`.`fightSysCode`, NULL) AS `fightSysCode`,
	ifnull(`b`.`fightSysName`, NULL) AS `fightSysName`,
	ifnull(
		`b`.`reportDate`,
		`c`.`click_date`
	) AS `reportDate`,
	ifnull(`b`.`Open`, 0) AS `Open`,
	ifnull(`b`.`Fault`, 0) AS `Fault`,
	ifnull(`b`.`FireAlarm`, 0) AS `FireAlarm`,
	ifnull(`b`.`WarehouseSuccess`, 0) AS `WarehouseSuccess`
FROM
	(
		(
			(
				SELECT
					curdate() AS `click_date`
			)
			UNION ALL
				SELECT
					(curdate() - INTERVAL 1 DAY) AS `click_date`
				UNION ALL
					SELECT
						(curdate() - INTERVAL 2 DAY) AS `click_date`
					UNION ALL
						SELECT
							(curdate() - INTERVAL 3 DAY) AS `click_date`
						UNION ALL
							SELECT
								(curdate() - INTERVAL 4 DAY) AS `click_date`
							UNION ALL
								SELECT
									(curdate() - INTERVAL 5 DAY) AS `click_date`
								UNION ALL
									SELECT
										(curdate() - INTERVAL 6 DAY) AS `click_date`
		) `c`
		LEFT JOIN (
			SELECT
				ifnull(`a`.`fightSysName`, NULL) AS `fightSysName`,
				ifnull(`a`.`fightSysCode`, NULL) AS `fightSysCode`,
				ifnull(`a`.`fightSysId`, NULL) AS `fightSysId`,
				ifnull(`a`.`report_date`, NULL) AS `reportDate`,
				sum(

					IF (
						locate('Open', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Open`,
				sum(

					IF (
						locate('FireAlarm', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `FireAlarm`,
				sum(

					IF (
						locate(
							'WarehouseSuccess',
							`a`.`indxKey`
						),
						`a`.`total`,
						0
					)
				) AS `WarehouseSuccess`,
				sum(

					IF (
						locate('Fault', `a`.`indxKey`),
						`a`.`total`,
						0
					)
				) AS `Fault`
			FROM
				(
					SELECT
						`rd`.`index_type` AS `indxKey`,
						`rd`.`index_name` AS `index_name`,
						count(
							DISTINCT `rd`.`equipment_specific_id`
						) AS `total`,
						`rd`.`report_date` AS `report_date`,
						`fs`.`name` AS `fightSysName`,
						`fs`.`code` AS `fightSysCode`,
						`fs`.`id` AS `fightSysId`
					FROM
						(
							`wl_equipment_alarm_report_day` `rd`
							LEFT JOIN `f_fire_fighting_system` `fs` ON (
								(
									0 <> find_in_set(
										`fs`.`id`,
										`rd`.`system_ids`
									)
								)
							)
						)
					WHERE
						(
							(
								(curdate() - INTERVAL 7 DAY) <= cast(`rd`.`report_date` AS date)
							)
							AND (
								`fs`.`code` = '011023306003010000000143'                      -- yeejoin 窝山炮系统
							)
							AND (
								(
									`rd`.`index_type` LIKE '%Open'
								)
								OR (
									`rd`.`index_type` LIKE '%FireAlarm'
								)
								OR (
									`rd`.`index_type` LIKE '%WarehouseSuccess'
								)
								OR (
									`rd`.`index_type` LIKE '%Fault'
								)
							)
							AND (`rd`.`value` = 'true')
						)
					GROUP BY
						`rd`.`equipment_specific_id`,
						`rd`.`report_date`,
						`rd`.`alarm_type_name`
				) `a`
			GROUP BY
				`a`.`report_date`,
				`a`.`fightSysCode`
		) `b` ON (
			(
				to_days(`c`.`click_date`) = to_days(`b`.`reportDate`)
			)
		)
	)
ORDER BY
	`c`.`click_date`;
-- 消防设备实时状态统计
DROP VIEW IF EXISTS `v_fire_equip_realtime_status`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_realtime_status` AS 
SELECT
	`wes`.`name` AS `equipName`,(
	CASE
			`wes`.`realtime_iot_index_value`
			WHEN 'true' THEN
			concat( `wes`.`realtime_iot_index_name`, '(是)' )
			WHEN 'false' THEN
			concat( `wes`.`realtime_iot_index_name`, '(否)' ) ELSE concat( `wes`.`realtime_iot_index_name`, '(', `wes`.`realtime_iot_index_value`, ')' )
		END
		) AS `nowStatus`,
		`wes`.`realtime_iot_index_update_date` AS `update_date`,(
		SELECT
			group_concat( `fs`.`code` SEPARATOR ',' )
		FROM
			`f_fire_fighting_system` `fs`
		WHERE
			(
			0 <> find_in_set( `fs`.`id`, `wes`.`system_id` ))) AS `fscode`,(
		SELECT
			group_concat( `fs`.`name` SEPARATOR ',' )
		FROM
			`f_fire_fighting_system` `fs`
		WHERE
			(
			0 <> find_in_set( `fs`.`id`, `wes`.`system_id` ))) AS `fsname`
	FROM
		`wl_equipment_specific` `wes`
	WHERE
		( `wes`.`realtime_iot_es_index_id` IS NOT NULL )
ORDER BY
	`wes`.`realtime_iot_index_update_date` DESC


-- 系统信息视图
DROP VIEW IF EXISTS `v_fire_system_info`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_system_info` AS
SELECT
	fs.id,
	fs.`code`,
	fs.`name`,
fs.install_date as 'installdate',
fs.charge_person_name as 'chargeperson',
fs.charge_person_phone as 'chargepersonphone',
mim.`name` as 'maintenanceunit',
mic.`name` as 'constructionunit',
CASE WHEN (SELECT
	count(1)
FROM
	wl_equipment_specific_alarm 
WHERE `status` = 1 AND find_in_set(fs.id,system_ids))>0 THEN
'异常'
ELSE '正常'
END as 'status'
FROM
	f_fire_fighting_system fs
LEFT JOIN wl_manufacturer_info mic ON mic.id = fs.maintenance_unit
LEFT JOIN wl_manufacturer_info mim ON mim.id = fs.construction_unit;


DROP VIEW IF EXISTS `v_fire_onl_three_small`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_onl_three_small` AS 
SELECT
	d.jygdfmnum,
	d.jygdfmqknum,
	d.jygdfmqbnum,
	d.jygdfmlynum,
	d.jygdzldynum,
	d.jygdzldygznum,
	ABS (
		(
			d.jygdzldynum - d.jygdzldygznum
		)
	) AS jygdzldyzcnum,
	d.jygdjldynum,
	d.jygdjldygznum,
(d.jygdzldygznum + d.jygdjldygznum) as jygddygznum,
	ABS (
		(
			d.jygdjldynum - d.jygdjldygznum
		)
	) AS jygdjldyzcnum,
	d.jyddfmnum,
	d.jyddfmqknum,
	d.jyddfmqbnum,
	d.jyddfmlynum,
	d.jyddzldynum,
	d.jyddzldygznum,
	ABS (
		(
			d.jyddzldynum - d.jyddzldygznum
		)
	) AS jyddzldyzcnum,
	d.jyddjldynum,
	d.jyddjldygznum,
	(d.jyddzldygznum+d.jyddjldygznum) as jydddygznum,
	ABS (
		(
			d.jyddjldynum - d.jyddjldygznum
		)
	) AS jyddjldyzcnum,
	d.jegdfmnum,
	d.jegdfmqknum,
	d.jegdfmqbnum,
	d.jegdfmlynum,
	d.jegdzldynum,
	d.jegdzldygznum,
	ABS (
		(
			d.jegdzldynum - d.jegdzldygznum
		)
	) AS jegdzldyzcnum,
	d.jegdjldynum,
	d.jegdjldygznum,
(d.jegdzldygznum+d.jegdjldygznum) as jegddygznum,
	ABS (
		(
			d.jegdjldynum - d.jegdjldygznum
		)
	) AS jegdjldyzcnum,
	d.jeddfmnum,
	d.jeddfmqknum,
	d.jeddfmqbnum,
	d.jeddfmlynum,
	d.jeddzldynum,
	d.jeddzldygznum,
	ABS (
		(
			d.jeddzldynum - d.jeddzldygznum
		)
	) AS jeddzldyzcnum,
	d.jeddjldynum,
	d.jeddjldygznum,
	(d.jeddzldygznum+d.jeddjldygznum) as jedddygznum,
	ABS (
		(
			d.jeddjldynum - d.jeddjldygznum
		)
	) AS jeddjldyzcnum
FROM
	(
		SELECT
			(
				SELECT
					count(1) 
					FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code = '92100400VWE44'  -- 阀门 
			) AS jygdfmnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE 
					esi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ高端%'
			) AS jygdfmqknum,
			(
				
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ高端%'
			) AS jygdfmqbnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ高端%'
			) AS jygdfmlynum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code = '92100300MNL44'
			) AS jygdzldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DCPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100300MNL44'
				AND es.position LIKE '%极Ⅰ高端%'
			) AS jygdzldygznum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code = '921002000JK44'
			) AS jygdjldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_ACPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '921002000JK44'
				AND es.position LIKE '%极Ⅰ高端%'
			) AS jygdjldygznum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code = '92100400VWE44'
			) AS jyddfmnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ低端%'
			) AS jyddfmqknum,
			(
			SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ低端%'
			) AS jyddfmqbnum,
			(
			SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅰ低端%'
			) AS jyddfmlynum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code = '92100300MNL44'
			) AS jyddzldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DCPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100300MNL44'
				AND es.position LIKE '%极Ⅰ低端%'
			) AS jyddzldygznum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code = '921002000JK44'
			) AS jyddjldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_ACPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '921002000JK44'
				AND es.position LIKE '%极Ⅰ低端%'
			) AS jyddjldygznum,
			(
			SELECT
					count(1) 
					FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code = '92100400VWE44'  -- 阀门 
			) AS jegdfmnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ高端%'
			) AS jegdfmqknum,
			(	
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ高端%'
			) AS jegdfmqbnum,
			(	
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ高端%'
			) AS jegdfmlynum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code = '92100300MNL44'
			) AS jegdzldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DCPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100300MNL44'
				AND es.position LIKE '%极Ⅱ高端%'
			) AS jegdzldygznum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code = '921002000JK44'
			) AS jegdjldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_ACPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '921002000JK44'
				AND es.position LIKE '%极Ⅱ高端%'
			) AS jegdjldygznum,
			(
					SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code = '92100400VWE44'
			) AS jeddfmnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Open'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ低端%'
			) AS jeddfmqknum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_Close'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ低端%'
			) AS jeddfmqbnum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DrainOilValve_OilLeak'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100400VWE44'
				AND es.position LIKE '%极Ⅱ低端%'
			) AS jeddfmlynum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code = '92100300MNL44'
			) AS jeddzldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_DCPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '92100300MNL44'
				AND es.position LIKE '%极Ⅱ低端%'
			) AS jeddzldygznum,
			(
				SELECT
					count(1) 
				FROM
					wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code = '921002000JK44'
			) AS jeddjldynum,
			(
				SELECT
					count(esi.id)
				FROM
					wl_equipment_specific_index esi
				LEFT JOIN wl_equipment_specific es ON esi.equipment_specific_id = es.id
				WHERE
					esi.equipment_index_key = 'ONL_ACPower_Fault'
				AND esi.`value` = 'true'
				AND es.equipment_code = '921002000JK44'
				AND es.position LIKE '%极Ⅱ低端%'
			) AS jeddjldygznum
	) d;
-- 灯泡状态灯
DROP VIEW IF EXISTS `v_equip_index_light_status`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equip_index_light_status` AS select (case `si`.`value` when 'true' then 1 when 'false' then 2 else 3 end) AS `status`,`si`.`equipment_index_key` AS `equipment_index_key`,`si`.`equipment_index_name` AS `equipment_index_name`,`s`.`code` AS `code`,`s`.`name` AS `name` from (`wl_equipment_specific_index` `si` join `wl_equipment_specific` `s`) where (`si`.`equipment_specific_id` = `s`.`id`);
--设备卷帘门指标状态
DROP VIEW IF EXISTS `v_fire_equip_door_status`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_door_status` AS select (case `si`.`value` when 'true' then '开启' else '关闭' end) AS `status`,`si`.`equipment_index_key` AS `equipment_index_key`,`s`.`name` AS `name`,`s`.`code` AS `code` from (`wl_equipment_specific_index` `si` join `wl_equipment_specific` `s`) where ((`si`.`equipment_specific_id` = `s`.`id`) and (`si`.`equipment_index_key` = 'FMS_EquipmentCompartment_RollingShutterDoorOpen'));
--消防水池液位
DROP VIEW IF EXISTS `v_fire_pool_water_level`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_pool_water_level` AS
SELECT
	`yh`.`yhxfscyw` AS `yhxfscyw`,
	`yh`.`yhxfscyl` AS `yhxfscyl`,
	`yh`.`yhxfsccs` AS `yhxfsccs`,
	`yh`.`yhxfscg` AS `yhxfscg`,
	`eh`.`ehxfscyw` AS `ehxfscyw`,
	`eh`.`ehxfscyl` AS `ehxfscyl`,
	`eh`.`ehxfsccs` AS `ehxfsccs`,
	`eh`.`ehxfscg` AS `ehxfscg`,
	`shsx`.`shsxyw` AS `shsxyw`,
	`shsx`.`shsxyl` AS `shsxyl`,
	`shsx`.`shsxcs` AS `shsxcs`,
	`shsx`.`shsxg` AS `shsxg`
FROM
	(
		(
			SELECT
				ifnull(sum(`a`.`value`), 0) AS `yhxfscyw`,
				ifnull(
					(
						CASE
						WHEN (`a`.`value` = NULL) THEN
							0
						WHEN (
							(`a`.`value` <> '')
							AND (`b`.`height` <> '')
						) THEN
							round(
								(
									(`a`.`value` / `b`.`height`) * 100
								),
								2
							)
						WHEN (`b`.`height` = NULL) THEN
							0
						END
					),
					0
				) AS `yhxfscyl`,
				ifnull(`b`.`waterStorage`, 0) AS `yhxfsccs`,
				ifnull(`b`.`height`, 0) AS `yhxfscg`
			FROM
				(
					(
						SELECT
							`si`.`value` AS `value`
						FROM
							(
								`wl_equipment_specific_index` `si`
								JOIN `wl_equipment_specific` `s`
							)
						WHERE
							(
								(
									`si`.`equipment_index_key` = 'FHS_FirePoolDevice_WaterLevel'
								)
								AND (
									`s`.`code` = '1HXFSCCJZZ100000'
								)
								AND (
									`si`.`equipment_specific_id` = `s`.`id`
								)
							)
					) `a`
					JOIN (
						SELECT
							max(
								(
									CASE
									WHEN (`i`.`field_name` = 'height') THEN
										`i`.`field_value`
									END
								)
							) AS `height`,
							max(
								(
									CASE
									WHEN (
										`i`.`field_name` = 'waterStorage'
									) THEN
										`i`.`field_value`
									END
								)
							) AS `waterStorage`
						FROM
							`wl_form_instance` `i`
						WHERE
							(
								(
									(`i`.`field_name` = 'height')
									OR (
										`i`.`field_name` = 'waterStorage'
									)
								)
								AND (
									`i`.`instance_id` = (
										SELECT
											`i`.`instance_id`
										FROM
											`wl_form_instance` `i`
										WHERE
											(
												(`i`.`field_name` = 'code')
												AND (
													`i`.`field_value` = 'SC106268'
												)
											)
										LIMIT 1
									)
								)
							)
					) `b`
				)
		) `yh`
		LEFT JOIN (
			SELECT
				ifnull(sum(`a`.`value`), 0) AS `ehxfscyw`,
				ifnull(
					(
						CASE
						WHEN (`a`.`value` = NULL) THEN
							0
						WHEN (
							(`a`.`value` <> '')
							AND (`b`.`height` <> '')
						) THEN
							round(
								(
									(`a`.`value` / `b`.`height`) * 100
								),
								2
							)
						WHEN (`b`.`height` = NULL) THEN
							0
						END
					),
					0
				) AS `ehxfscyl`,
				ifnull(`b`.`waterStorage`, 0) AS `ehxfsccs`,
				ifnull(`b`.`height`, 0) AS `ehxfscg`
			FROM
				(
					(
						SELECT
							`si`.`value` AS `value`
						FROM
							(
								`wl_equipment_specific_index` `si`
								JOIN `wl_equipment_specific` `s`
							)
						WHERE
							(
								(
									`si`.`equipment_index_key` = 'FHS_FirePoolDevice_WaterLevel'
								)
								AND (
									`s`.`code` = '1HXFSCCJZZ100000'
								)
								AND (
									`si`.`equipment_specific_id` = `s`.`id`
								)
							)
					) `a`
					JOIN (
						SELECT
							max(
								(
									CASE
									WHEN (`i`.`field_name` = 'height') THEN
										`i`.`field_value`
									END
								)
							) AS `height`,
							max(
								(
									CASE
									WHEN (
										`i`.`field_name` = 'waterStorage'
									) THEN
										`i`.`field_value`
									END
								)
							) AS `waterStorage`
						FROM
							`wl_form_instance` `i`
						WHERE
							(
								(
									(`i`.`field_name` = 'height')
									OR (
										`i`.`field_name` = 'waterStorage'
									)
								)
								AND (
									`i`.`instance_id` = (
										SELECT
											`i`.`instance_id`
										FROM
											`wl_form_instance` `i`
										WHERE
											(
												(`i`.`field_name` = 'code')
												AND (
													`i`.`field_value` = 'SC106268'
												)
											)
										LIMIT 1
									)
								)
							)
					) `b`
				)
		) `eh` ON 1 = 1
		LEFT JOIN (
			SELECT
				ifnull(sum(`a`.`value`), 0) AS `shsxyw`,
				ifnull(
					(
						CASE
						WHEN (`a`.`value` = NULL) THEN
							0
						WHEN (
							(`a`.`value` <> '')
							AND (`b`.`height` <> '')
						) THEN
							round(
								(
									(`a`.`value` / `b`.`height`) * 100
								),
								2
							)
						WHEN (`b`.`height` = NULL) THEN
							0
						END
					),
					0
				) AS `shsxyl`,
				ifnull(`b`.`waterStorage`, 0) AS `shsxcs`,
				ifnull(`b`.`height`, 0) AS `shsxg`
			FROM
				(
					(
						SELECT
							`si`.`value` AS `value`
						FROM
							(
								`wl_equipment_specific_index` `si`
								JOIN `wl_equipment_specific` `s`
							)
						WHERE
							(
								(
									`si`.`equipment_index_key` = 'FHS_FirePoolDevice_WaterLevel'
								)
								AND (
									`s`.`code` = '1HXFSCCJZZ100000'
								)
								AND (
									`si`.`equipment_specific_id` = `s`.`id`
								)
							)
					) `a`
					JOIN (
						SELECT
							max(
								(
									CASE
									WHEN (`i`.`field_name` = 'height') THEN
										`i`.`field_value`
									END
								)
							) AS `height`,
							max(
								(
									CASE
									WHEN (
										`i`.`field_name` = 'waterStorage'
									) THEN
										`i`.`field_value`
									END
								)
							) AS `waterStorage`
						FROM
							`wl_form_instance` `i`
						WHERE
							(
								(
									(`i`.`field_name` = 'height')
									OR (
										`i`.`field_name` = 'waterStorage'
									)
								)
								AND (
									`i`.`instance_id` = (
										SELECT
											`i`.`instance_id`
										FROM
											`wl_form_instance` `i`
										WHERE
											(
												(`i`.`field_name` = 'code')
												AND (
													`i`.`field_value` = 'SC106268'
												)
											)
										LIMIT 1
									)
								)
							)
					) `b`
				)
		) `shsx` ON 1 = 1
);
-- 泡沫灭火3小
DROP VIEW IF EXISTS `v_fire_equip_ffs_num`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_ffs_num` AS
SELECT
	d.jygdgwdl,
	d.jygdgwdlpb,
	d.jygdgwdlgz,
	d.jygdgwdlhj,
	ABS (d.jygdgwdl - d.jygdgwdlyc) AS jygdgwdlzc,
	d.jygddcf,
	d.jygddcfqd,
	d.jygddcfgz,
	d.jygddcfpb,
	ABS (d.jygddcf - d.jygddcfqd) AS jygddcfzc,
	d.jygdqdp,
	d.jygdqdpqd,
	d.jygdqdpgz,
	d.jygdqdppb,
	ABS (d.jygdqdp - d.jygdqdpqd) AS jygdqdpzc,
	d.jygddlq,
	d.jygddlqdl,
	d.jygddlqgz,
	d.jygddlqpb,
	ABS (d.jygddlq - d.jygddlqdl) AS jygddlqzc,
	d.jyddgwdl,
	d.jyddgwdlpb,
	d.jyddgwdlgz,
	d.jyddgwdlhj,
	ABS (d.jyddgwdl - d.jyddgwdlyc) AS jyddgwdlzc,
	d.jydddcf,
	d.jydddcfqd,
	d.jydddcfgz,
	d.jydddcfpb,
	ABS (d.jydddcf - d.jydddcfqd) AS jydddcfzc,
	d.jyddqdp,
	d.jyddqdpqd,
	d.jyddqdpgz,
	d.jyddqdppb,
	ABS (d.jyddqdp - d.jyddqdpqd) AS jyddqdpzc,
	d.jydddlq,
	d.jydddlqdl,
	d.jydddlqgz,
	d.jydddlqpb,
	ABS (d.jydddlq - d.jydddlqdl) AS jydddlqzc,
	d.jegdgwdl,
	d.jegdgwdlpb,
	d.jegdgwdlgz,
	d.jegdgwdlhj,
	ABS (d.jegdgwdl - d.jegdgwdlyc) AS jegdgwdlzc,
	d.jegddcf,
	d.jegddcfqd,
	d.jegddcfgz,
	d.jegddcfpb,
	ABS (d.jegddcf - d.jegddcfqd) AS jegddcfzc,
	d.jegdqdp,
	d.jegdqdpqd,
	d.jegdqdpgz,
	d.jegdqdppb,
	ABS (d.jegdqdp - d.jegdqdpqd) AS jegdqdpzc,
	d.jegddlq,
	d.jegddlqdl,
	d.jegddlqgz,
	d.jegddlqpb,
	ABS (d.jegddlq - d.jegddlqdl) AS jegddlqzc,
	d.jeddgwdl,
	d.jeddgwdlpb,
	d.jeddgwdlgz,
	d.jeddgwdlhj,
	ABS (d.jeddgwdl - d.jeddgwdlyc) AS jeddgwdlzc,
	d.jedddcf,
	d.jedddcfqd,
	d.jedddcfgz,
	d.jedddcfpb,
	ABS (d.jedddcf - d.jedddcfqd) AS jedddcfzc,
	d.jeddqdp,
	d.jeddqdpqd,
	d.jeddqdpgz,
	d.jeddqdppb,
	ABS (d.jeddqdp - d.jeddqdpqd) AS jeddqdpzc,
	d.jedddlq,
	d.jedddlqdl,
	d.jedddlqgz,
	d.jedddlqpb,
	ABS (d.jedddlq - d.jedddlqdl) AS jedddlqzc,
	(d.jygddcf + d.jydddcf + d.jegddcf + d.jedddcf) AS dcfzs,
	(d.jygddcfqd + d.jydddcfqd + d.jegddcfqd + d.jedddcfqd) AS dcfqd,
	(d.jygddcfgz + d.jydddcfgz + d.jegddcfgz + d.jedddcfgz) AS dcfgz,
	(d.jygddcfpb + d.jydddcfpb + d.jegddcfpb + d.jedddcfpb) AS dcfpb,
	(d.jygdqdp + d.jyddqdp + d.jegdqdp + d.jeddqdp) AS qdpzs,
	(d.jygdqdpqd + d.jyddqdpqd + d.jegdqdpqd + d.jeddqdpqd) AS qdpqd,
	(d.jygdqdpgz + d.jyddqdpgz + d.jegdqdpgz + d.jeddqdpgz) AS qdpgz,
	(d.jygdqdppb + d.jyddqdppb + d.jegdqdppb + d.jeddqdppb) AS qdppb,
	(d.jygddlq + d.jydddlq + d.jegddlq + d.jedddlq) AS dlqzs,
	(d.jygddlqdl + d.jydddlqdl + d.jegddlqdl + d.jedddlqdl) AS dlqqd,
	(d.jygddlqgz + d.jydddlqgz + d.jegddlqgz + d.jedddlqgz) AS dlqgz,
	(d.jygddlqpb + d.jydddlqpb + d.jegddlqpb + d.jedddlqpb) AS dlqpb,
	ABS	(d.jygddlq - d.jygddlqdl + d.jydddlq - d.jydddlqdl + d.jegddlq - d.jegddlqdl + d.jedddlq - d.jedddlqdl) AS dlqjt
FROM
	(
		SELECT
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code =  '92110100QY544'
			) AS jygdgwdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
				AND wesi.`value`= 'true'
			) AS jygdgwdlpb,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
				AND wesi.`value`= 'true'
			) AS jygdgwdlgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				AND wesi.`value`= 'true'
			) AS jygdgwdlhj,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND (
						wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				)
				AND wesi.`value`= 'true'
			) AS jygdgwdlyc,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code =  '92110200UIU44'
			) AS jygddcf,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Start'
				AND wesi.`value`= 'true'
			) AS jygddcfqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Fault'
				AND wesi.`value`= 'true'
			) AS jygddcfgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Shield'
				AND wesi.`value`= 'true'
			) AS jygddcfpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code =  '92110300SS744'
			) AS jygdqdp,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_Start'
				AND wesi.`value`= 'true'
			) AS jygdqdpqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleFault'
				AND wesi.`value`= 'true'
			) AS jygdqdpgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleShield'
				AND wesi.`value`= 'true'
			) AS jygdqdppb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ高端%'
				AND es.equipment_code =  '92110400CRQ44'
			) AS jygddlq,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_State'
				AND wesi.`value`= 'true'
			) AS jygddlqdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Fault'
				AND wesi.`value`= 'true'
			) AS jygddlqgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Shield'
				AND wesi.`value`= 'true'
			) AS jygddlqpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code =  '92110100QY544'
			) AS jyddgwdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
				AND wesi.`value`= 'true'
			) AS jyddgwdlpb,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
				AND wesi.`value`= 'true'
			) AS jyddgwdlgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				AND wesi.`value`= 'true'
			) AS jyddgwdlhj,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND (
						wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				)
				AND wesi.`value`= 'true'
			) AS jyddgwdlyc,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code =  '92110200UIU44'
			) AS jydddcf,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Start'
				AND wesi.`value`= 'true'
			) AS jydddcfqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Fault'
				AND wesi.`value`= 'true'
			) AS jydddcfgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Shield'
				AND wesi.`value`= 'true'
			) AS jydddcfpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code =  '92110300SS744'
			) AS jyddqdp,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_Start'
				AND wesi.`value`= 'true'
			) AS jyddqdpqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleFault'
				AND wesi.`value`= 'true'
			) AS jyddqdpgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleShield'
				AND wesi.`value`= 'true'
			) AS jyddqdppb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅰ低端%'
				AND es.equipment_code =  '92110400CRQ44'
			) AS jydddlq,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_State'
				AND wesi.`value`= 'true'
			) AS jydddlqdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Fault'
				AND wesi.`value`= 'true'
			) AS jydddlqgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅰ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Shield'
				AND wesi.`value`= 'true'
			) AS jydddlqpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code =  '92110100QY544'
			) AS jegdgwdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
				AND wesi.`value`= 'true'
			) AS jegdgwdlpb,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
				AND wesi.`value`= 'true'
			) AS jegdgwdlgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				AND wesi.`value`= 'true'
			) AS jegdgwdlhj,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND (
						wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				)
				AND wesi.`value`= 'true'
			) AS jegdgwdlyc,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code =  '92110200UIU44'
			) AS jegddcf,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Start'
				AND wesi.`value`= 'true'
			) AS jegddcfqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Fault'
				AND wesi.`value`= 'true'
			) AS jegddcfgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Shield'
				AND wesi.`value`= 'true'
			) AS jegddcfpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code =  '92110300SS744'
			) AS jegdqdp,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_Start'
				AND wesi.`value`= 'true'
			) AS jegdqdpqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleFault'
				AND wesi.`value`= 'true'
			) AS jegdqdpgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleShield'
				AND wesi.`value`= 'true'
			) AS jegdqdppb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ高端%'
				AND es.equipment_code =  '92110400CRQ44'
			) AS jegddlq,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_State'
				AND wesi.`value`= 'true'
			) AS jegddlqdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Fault'
				AND wesi.`value`= 'true'
			) AS jegddlqgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ高端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Shield'
				AND wesi.`value`= 'true'
			) AS jegddlqpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code =  '92110100QY544'
			) AS jeddgwdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
				AND wesi.`value`= 'true'
			) AS jeddgwdlpb,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
				AND wesi.`value`= 'true'
			) AS jeddgwdlgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				AND wesi.`value`= 'true'
			) AS jeddgwdlhj,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND (
						wesi.equipment_index_key = 'FFS_TemperatureCable_Shield'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_Fault'
						OR wesi.equipment_index_key = 'FFS_TemperatureCable_FireAlarm'
				)
				AND wesi.`value`= 'true'
			) AS jeddgwdlyc,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code =  '92110200UIU44'
			) AS jedddcf,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Start'
				AND wesi.`value`= 'true'
			) AS jedddcfqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Fault'
				AND wesi.`value`= 'true'
			) AS jedddcfgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_SolenoidValve_Shield'
				AND wesi.`value`= 'true'
			) AS jedddcfpb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code =  '92110300SS744'
			) AS jeddqdp,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_Start'
				AND wesi.`value`= 'true'
			) AS jeddqdpqd,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleFault'
				AND wesi.`value`= 'true'
			) AS jeddqdpgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Startbottle_StartupModuleShield'
				AND wesi.`value`= 'true'
			) AS jeddqdppb,
			(
				SELECT
					count(1)
				FROM
				 wl_equipment_specific es
				WHERE
					es.position LIKE '%极Ⅱ低端%'
				AND es.equipment_code =  '92110400CRQ44'
			) AS jedddlq,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_State'
				AND wesi.`value`= 'true'
			) AS jedddlqdl,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Fault'
				AND wesi.`value`= 'true'
			) AS jedddlqgz,
			(
				SELECT
					COUNT(DISTINCT(wes.id))
				FROM
					wl_equipment_specific_index wesi
				LEFT JOIN wl_equipment_specific wes ON wes.id = wesi.equipment_specific_id
				WHERE
					wes.position LIKE '%极Ⅱ低端%'
				AND wesi.equipment_index_key = 'FFS_Breaker_Shield'
				AND wesi.`value`= 'true'
			) AS jedddlqpb
	) d;
-- 今日告警统计，概览4小shiy
DROP VIEW IF EXISTS `v_equip_alarm_today_statistics`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_equip_alarm_today_statistics` AS select count(`wl_equipment_specific_alarm_log`.`confirm_type`) AS `confirmed`,(count(1) - count(`wl_equipment_specific_alarm_log`.`confirm_type`)) AS `pending` from `wl_equipment_specific_alarm_log` where ((to_days(now()) - to_days(`wl_equipment_specific_alarm_log`.`create_date`)) = 0);

-- 火灾报警系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_alarm`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_alarm` AS SELECT
	`sa`.`id` AS `id`,
	`ec`.`code` AS `mRid`,
	`sa`.`equipment_specific_name` AS `specificName`,
	`sa`.`equipment_specific_index_key` AS `indexKey`,
	`sa`.`equipment_specific_index_name` AS `indexName`,
	`sa`.`equipment_specific_id` AS `specificId`,
IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
	`sa`.`iot_code` AS `iotCode`,
	`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '029026401813010000000016', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- CAFS系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_cafs`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_cafs` AS SELECT
`sa`.`id` AS `id`,
`ec`.`code` AS `mRid`,
`sa`.`equipment_specific_name` AS `specificName`,
`sa`.`equipment_specific_index_key` AS `indexKey`,
`sa`.`equipment_specific_index_name` AS `indexName`,
`sa`.`equipment_specific_id` AS `specificId`,
IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
`sa`.`iot_code` AS `iotCode`,
`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '029026401813010000000023', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- 预混泡沫灭火系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_foam`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_foam` AS SELECT
	`sa`.`id` AS `id`,
	`ec`.`code` AS `mRid`,
	`sa`.`equipment_specific_name` AS `specificName`,
	`sa`.`equipment_specific_index_key` AS `indexKey`,
	`sa`.`equipment_specific_index_name` AS `indexName`,
	`sa`.`equipment_specific_id` AS `specificId`,
	IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
	`sa`.`iot_code` AS `iotCode`,
	`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '029026401813010000000054', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- 细水雾涡扇炮系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_foam_mist`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_foam_mist` AS SELECT
`sa`.`id` AS `id`,
`ec`.`code` AS `mRid`,
`sa`.`equipment_specific_name` AS `specificName`,
`sa`.`equipment_specific_index_key` AS `indexKey`,
`sa`.`equipment_specific_index_name` AS `indexName`,
`sa`.`equipment_specific_id` AS `specificId`,
IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
`sa`.`iot_code` AS `iotCode`,
`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '011023306003010000000082', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- 排油系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_onl`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_onl` AS SELECT
	`sa`.`id` AS `id`,
	`ec`.`code` AS `mRid`,
	`sa`.`equipment_specific_name` AS `specificName`,
	`sa`.`equipment_specific_index_key` AS `indexKey`,
	`sa`.`equipment_specific_index_name` AS `indexName`,
	`sa`.`equipment_specific_id` AS `specificId`,
	IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
	`sa`.`iot_code` AS `iotCode`,
	`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '029026401813010000000030', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- 消防给水系统4小告警列表前60
DROP VIEW IF EXISTS `v_fire_equip_alarm_60list_water`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `v_fire_equip_alarm_60list_water` AS SELECT
`sa`.`id` AS `id`,
`ec`.`code` AS `mRid`,
`sa`.`equipment_specific_name` AS `specificName`,
`sa`.`equipment_specific_index_key` AS `indexKey`,
`sa`.`equipment_specific_index_name` AS `indexName`,
`sa`.`equipment_specific_id` AS `specificId`,
IF
	( `sa`.`clean_time` IS NOT NULL, '已消除', '未消除' ) `cleanStatus`,
`sa`.`iot_code` AS `iotCode`,
`sa`.`create_date` AS `createDate`
FROM
	(
		`wl_equipment_specific_alarm_log` `sa`
		LEFT JOIN `wl_equipment_specific` `ec` ON ((
				`sa`.`equipment_specific_id` = `ec`.`id`
			)))
WHERE
	find_in_set( '029026401813010000000047', `sa`.`system_codes` )
ORDER BY
	`sa`.`create_date` DESC
	LIMIT 60;

-- 消防人员视图
DROP VIEW IF EXISTS `v_fire_fighter`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_fire_fighter` AS 
SELECT
  au.USER_ID as user_id,
	au.USER_NAME as user_name,
	au.REAL_NAME as real_name,
	au.MOBILE as mobile,
	d.DEPARTMENT_NAME as department_name,
  CONCAT(d.SEQUENCE_NBR) as department_id,
	d.ORG_CODE as org_code,
	r.ROLE_SEQ role_id
FROM
  b_privilege_user_org_role r
  LEFT JOIN b_privilege_department d ON r.DEPARTMENT_SEQ = d.SEQUENCE_NBR
	LEFT JOIN b_privilege_agency_user au ON au.USER_ID = r.USER_ID
WHERE
  d.`LEVEL` = '队伍' AND r.AGENCY_CODE = 'STATE_GRID'
  and (department_name = '消防专职队' or department_name = '志愿消防队')

  -- 值班人员视图
DROP VIEW IF EXISTS `v_duty_person`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_duty_person` AS
SELECT
	`fighter`.`department_name` AS `department_name`,
	count( 0 ) AS `total`,
	sum(
		(
		SELECT
			count( 0 )
		FROM
			`d_duty_details` `duty`
		WHERE
			(
				`duty`.`user_id` = `fighter`.`user_id`
				AND duty.is_duty = 1
				AND TO_DAYS( NOW( ) ) = TO_DAYS( duty.duty_date )
			)
		)
	) AS `on_duty`
FROM
	`v_fire_fighter` `fighter`
GROUP BY
	`fighter`.`department_name`;

  -- 今日值班与巡查人员
DROP VIEW IF EXISTS `v_duty_check_person`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_duty_check_person` AS
SELECT
	ddd.`duty_name` AS `personName`,
	au.`MOBILE` AS `userPhone`,
	'值班员' AS `postName`
FROM
	`d_duty_details` ddd
	LEFT JOIN b_privilege_agency_user au ON au.USER_ID = ddd.user_id
WHERE
	ddd.`duty_date` = curdate()
GROUP BY
	ddd.`user_id` UNION ALL
SELECT
	au.`REAL_NAME` AS `personName`,
	au.`MOBILE` AS `userPhone`,
	'巡查员' AS `postName`
FROM
	`p_plan_task` ppt
	LEFT JOIN b_privilege_agency_user au ON FIND_IN_SET(au.USER_ID, ppt.user_id) > 0
WHERE
	DATEDIFF(begin_time,CURRENT_DATE) <= 0 AND DATEDIFF(end_time,CURRENT_DATE) >= 0
GROUP BY
		au.USER_ID;
		
-- 今日巡查统计日常管理组态屏查询脚本
DROP VIEW IF EXISTS `v_check_point_num`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_check_point_num` AS
SELECT
	(
		SELECT
			ifnull(
				sum(`p_plan_task`.`point_num`),
				0
			)
		FROM
			`p_plan_task`
		WHERE
			(
				to_days(`p_plan_task`.`begin_time`) - to_days(curdate())
			) <= 0
		AND (
			to_days(`p_plan_task`.`end_time`) - to_days(curdate())
		) >= 0
	) AS `pointNum`,
	(
		SELECT
			ifnull(
				sum(`p_plan_task`.`point_num`),
				0
			)
		FROM
			`p_plan_task`
		WHERE
			`p_plan_task`.`finish_status` = 2
		AND (
			to_days(`p_plan_task`.`begin_time`) - to_days(curdate())
		) <= 0
		AND (
			to_days(`p_plan_task`.`end_time`) - to_days(curdate())
		) >= 0
	) AS `finishNum`,
	(
		SELECT
			ifnull(
				sum(`p_plan_task`.`point_num`),
				0
			)
		FROM
			`p_plan_task`
		WHERE
			(
				`p_plan_task`.`finish_status` = 0
				OR `p_plan_task`.`finish_status` = 1
			)
		AND (
			to_days(`p_plan_task`.`begin_time`) - to_days(curdate())
		) <= 0
		AND (
			to_days(`p_plan_task`.`end_time`) - to_days(curdate())
		) >= 0
	) AS `unFinishNum`;