leading-project/finereport/预警/预警-项目级-水电、综合、巡查.sql

-- ======================================
-- 预警-项目级-水电费收缴率-98%
-- ======================================
with a as( -- 所有有角色的人员
    select t1.username,t3.name as role_name from finedb.fine_user t1
    inner join finedb.fine_user_role_middle t2 on t1.id = t2.userId
    inner join finedb.fine_custom_role t3 on t2.roleId = t3.id
),
b as( -- 找到单条预警规则**
     select index_name,comm_organ,index_value,mang_organ from ods_index_early_warning
     where index_name = '水电费收缴率'
       and comm_organ = '项目'
       and index_value = '0.98'
     limit 1
 ),
 c as( -- 找到关联的用户
     select distinct username from b
    join a on FIND_IN_SET(a.role_name,b.mang_organ)


),
 d as( -- 找到规则下受影响的项目**
     select t1.organ_name,t1.comm_name,t1.water_electric_rate from dim_early_warning_comm_d t1
    inner join b t2 on t1.water_electric_rate < t2.index_value
 ),
 e as( -- 要推送的总部人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '总部人员'
 ),
 f as( -- 要推送的片区人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '片区人员'
    inner join d t3 on FIND_IN_SET(t3.organ_name,t2.ProjectAuth)
 ),
 g as( -- 要推送的项目人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '项目人员'
    inner join d t3 on FIND_IN_SET(t3.comm_name,t2.ProjectAuth)
)
-- 要推送的人员合集
select username,LoginCode,user_type,ProjectAuth from e
union all
select username,LoginCode,user_type,ProjectAuth from f
union all
select username,LoginCode,user_type,ProjectAuth from g
;

-- ======================================
-- 预警-项目级-综合收缴完成率-80%
-- ======================================
with a as( -- 所有有角色的人员
    select t1.username,t3.name as role_name from finedb.fine_user t1
    inner join finedb.fine_user_role_middle t2 on t1.id = t2.userId
    inner join finedb.fine_custom_role t3 on t2.roleId = t3.id
),
b as( -- 找到单条预警规则**
     select index_name,comm_organ,index_value,mang_organ from ods_index_early_warning
     where index_name = '综合收缴完成率'
       and comm_organ = '项目'
       and index_value = '0.8'
     limit 1
 ),
 c as( -- 找到关联的用户
     select distinct username from b
    join a on FIND_IN_SET(a.role_name,b.mang_organ)
),
 d as( -- 找到规则下受影响的项目**
     select t1.organ_name,t1.comm_name,t1.total_coll_comple_rate from dim_early_warning_comm_d t1
    inner join b t2 on t1.total_coll_comple_rate < t2.index_value
 ),
 e as( -- 要推送的总部人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '总部人员'
 ),
 f as( -- 要推送的片区人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '片区人员'
    inner join d t3 on FIND_IN_SET(t3.organ_name,t2.ProjectAuth)
 ),
 g as( -- 要推送的项目人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '项目人员'
    inner join d t3 on FIND_IN_SET(t3.comm_name,t2.ProjectAuth)
)
-- 要推送的人员合集
select username,LoginCode,user_type,ProjectAuth from e
union all
select username,LoginCode,user_type,ProjectAuth from f
union all
select username,LoginCode,user_type,ProjectAuth from g
;



-- ======================================
-- 预警-项目级-综合收缴完成率-70%
-- ======================================
with a as( -- 所有有角色的人员
    select t1.username,t3.name as role_name from finedb.fine_user t1
    inner join finedb.fine_user_role_middle t2 on t1.id = t2.userId
    inner join finedb.fine_custom_role t3 on t2.roleId = t3.id
),
 b as( -- 找到单条预警规则**
     select index_name,comm_organ,index_value,mang_organ from ods_index_early_warning
     where index_name = '综合收缴完成率'
       and comm_organ = '项目'
       and index_value = '0.7'
     limit 1
 ),
 c as( -- 找到关联的用户
     select distinct username from b
    join a on FIND_IN_SET(a.role_name,b.mang_organ)
 ),
 d as( -- 找到规则下受影响的项目**
     select t1.organ_name,t1.comm_name,t1.total_coll_comple_rate from dim_early_warning_comm_d t1
    inner join b t2 on t1.total_coll_comple_rate < t2.index_value
 ),
 e as( -- 要推送的总部人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '总部人员'
 ),
 f as( -- 要推送的片区人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '片区人员'
    inner join d t3 on FIND_IN_SET(t3.organ_name,t2.ProjectAuth)
 ),
 g as( -- 要推送的项目人员
     select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '项目人员'
    inner join d t3 on FIND_IN_SET(t3.comm_name,t2.ProjectAuth)
 )
-- 要推送的人员合集
select username,LoginCode,user_type,ProjectAuth from e
union all
select username,LoginCode,user_type,ProjectAuth from f
union all
select username,LoginCode,user_type,ProjectAuth from g
;


-- ======================================
-- 预警-项目级-综合收缴完成率-60%
-- ======================================
with a as( -- 所有有角色的人员
    select t1.username,t3.name as role_name from finedb.fine_user t1
                                                     inner join finedb.fine_user_role_middle t2 on t1.id = t2.userId
                                                     inner join finedb.fine_custom_role t3 on t2.roleId = t3.id
),
b as( -- 找到单条预警规则**
    select index_name,comm_organ,index_value,mang_organ from ods_index_early_warning
    where index_name = '综合收缴完成率'
      and comm_organ = '项目'
      and index_value = '0.6'
    limit 1
),
c as( -- 找到关联的用户
    select distinct username from b
   join a on FIND_IN_SET(a.role_name,b.mang_organ)
),
d as( -- 找到规则下受影响的项目**
    select t1.organ_name,t1.comm_name,t1.total_coll_comple_rate from dim_early_warning_comm_d t1
    inner join b t2 on t1.total_coll_comple_rate < t2.index_value
),
e as( -- 要推送的总部人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '总部人员'
),
f as( -- 要推送的片区人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '片区人员'
    inner join d t3 on FIND_IN_SET(t3.organ_name,t2.ProjectAuth)
),
g as( -- 要推送的项目人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '项目人员'
    inner join d t3 on FIND_IN_SET(t3.comm_name,t2.ProjectAuth)
)
-- 要推送的人员合集
select username,LoginCode,user_type,ProjectAuth from e
union all
select username,LoginCode,user_type,ProjectAuth from f
union all
select username,LoginCode,user_type,ProjectAuth from g
;



-- ======================================
-- 预警-项目级-设备空间巡查完成率-98%
-- ======================================
with a as( -- 所有有角色的人员
    select t1.username,t3.name as role_name from finedb.fine_user t1
    inner join finedb.fine_user_role_middle t2 on t1.id = t2.userId
    inner join finedb.fine_custom_role t3 on t2.roleId = t3.id
),
b as( -- 找到单条预警规则**
    select index_name,comm_organ,index_value,mang_organ from ods_index_early_warning
    where index_name = '设备空间巡查完成率'
      and comm_organ = '项目'
      and index_value = '0.98'
    limit 1
),
c as( -- 找到关联的用户
    select distinct username from b
    join a on FIND_IN_SET(a.role_name,b.mang_organ)
),
d as( -- 找到规则下受影响的项目**
    select t1.organ_name,t1.comm_name,t1.device_space_rate from dim_early_warning_comm_d t1
    inner join b t2 on t1.device_space_rate < t2.index_value
),
e as( -- 要推送的总部人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '总部人员'
),
f as( -- 要推送的片区人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '片区人员'
    inner join d t3 on FIND_IN_SET(t3.organ_name,t2.ProjectAuth)
),
g as( -- 要推送的项目人员
    select distinct t2.username,t2.LoginCode,t2.user_type,t2.ProjectAuth from c t1
    inner join v_erp_user_auth t2 on t1.username = t2.LoginCode and t2.user_type = '项目人员'
    inner join d t3 on FIND_IN_SET(t3.comm_name,t2.ProjectAuth)
)
-- 要推送的人员合集
select username,LoginCode,user_type,ProjectAuth from e
union all
select username,LoginCode,user_type,ProjectAuth from f
union all
select username,LoginCode,user_type,ProjectAuth from g
;