SELECT
*,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.province=r.id) provincename,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.city=r.id) cityname,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.area=r.id) areaname,
(SELECT r.fullName from sp_approve_role r where a.workRole=r.id) workrolename
FROM
sp_se_user a
WHERE 1=1
AND a.province = #{province}
AND a.city = #{sCity}
AND a.area = #{sArea}
AND a.province = #{province}
AND a.city = #{city}
AND a.area = #{sArea}
AND a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
AND a.workRole = #{sWorkRole}
AND ( ( workRole IS NOT NULL AND workRole != '' ) OR role = 'ADMIN' )
AND (workRole is null or workRole = '')
AND role = #{sRole}
AND a.role = #{role}
AND province = #{areaCode}
AND (city = '' or city is null)
AND (area = '' or area is null)
AND city = #{areaCode}
AND (area = '' or area is null)
AND area = #{areaCode}
AND a.active = #{active}
AND (a.username like concat('%',#{keywords}, '%')
OR a.realName like concat('%',#{keywords}, '%')
or a.policeNumber = concat('%',#{keywords}, '%') )
ORDER BY a.id DESC
limit #{iDisplayStart},#{iDisplayLength}
SELECT
count(*)
FROM
sp_se_user a
WHERE 1=1
AND a.province = #{province}
AND a.city = #{sCity}
AND a.area = #{sArea}
AND a.province = #{province}
AND a.city = #{city}
AND a.area = #{sArea}
AND a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
AND a.workRole = #{sWorkRole}
AND ( ( workRole IS NOT NULL AND workRole != '' ) OR role = 'ADMIN' )
AND (workRole is null or workRole = '')
AND role = #{sRole}
AND a.role = #{role}
AND province = #{areaCode}
AND (city = '' or city is null)
AND (area = '' or area is null)
AND city = #{areaCode}
AND (area = '' or area is null)
AND area = #{areaCode}
AND a.active = #{active}
AND (a.username like concat('%',#{keywords}, '%')
OR a.realName like concat('%',#{keywords}, '%')
or a.policeNumber = concat('%',#{keywords}, '%') )
SELECT
*,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.province=r.id) provincename,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.city=r.id) cityname,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.area=r.id) areaname
FROM
sp_se_user a
where a.id = #{userId}
SELECT
*,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.province=r.id) provincename,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.city=r.id) cityname,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.area=r.id) areaname
FROM
sp_se_user a
where a.policeNumber = #{policeNumber}
SELECT
*,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.province=r.id) provincename,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.city=r.id) cityname,
(SELECT case when r.alias is not null then r.alias else r.fullName end from base_region r where a.area=r.id) areaname
FROM
sp_se_user a
where a.userName = #{userName}
SELECT
id,realName
FROM
sp_se_user b
where b.workRole = #{role}
and b.province = #{province}
and (b.province is null or b.province='')
AND b.city = #{city}
and (b.city is null or b.city='')
AND b.area = #{area}
AND (b.area is null or b.area='')
SELECT
distinct u.id
FROM
sp_activity a
inner join sp_workflow w on w.version = a.workflowVersion
inner join sp_se_user u on u.workRole = w.role
where a.approveLevel = #{level}
and w.seq = 1
and a.id = #{activityId}
SELECT
distinct u.id
FROM
sp_activity a
inner join sp_workflow w on w.version = a.workflowVersion
inner join sp_se_user u on u.workRole = w.role
where a.approveLevel = #{level}
and a.id = #{activityId}
SELECT
u.id
FROM
sp_activity a
inner join sp_workflow w on w.version = a.workflowVersion
inner join sp_se_user u on u.workRole = w.role
where 1=1
and w.seq = 1
and a.id = #{activityId}
SELECT
ap.userId as id
FROM
sp_activity a
inner join sp_approve ap on ap.activityId = a.id
where 1=1
and ap.logicDel = 0
and a.id = #{activityId}
and ap.approve is null
order by ap.id
limit 1
select
r.alias as 'orgName',-- 机构名称
summary1.areaCode,-- 区域编码
summary1.adminApproveCount,-- 全权限账号数
summary1.adminCount,-- 管理员账号数
summary1.approveCount,-- 审批账号数
IFNULL(summary2.approveRoleCount,0) as 'approveRoleCount',-- 审批角色数
IFNULL(summary3.workFlowCount,0) as 'workFlowCount'-- 工作流数
from
(
select
IFNULL(u.area,IFNULL(u.city,u.province)) as areaCode,
sum(if(u.role ='ADMIN_APPROVE',1,0)) as 'adminApproveCount',
sum(if(u.role ='ADMIN',1,0)) as 'adminCount',
sum(if(u.role ='APPROVE',1,0)) as 'approveCount'
from
sp_se_user u
where
u.active=1
and
u.role in ('ADMIN','APPROVE','ADMIN_APPROVE')
and
u.province=#{province}
and u.city=#{city}
and u.area=#{area}
group by
IFNULL(u.area,IFNULL(u.city,u.province))
) summary1
LEFT JOIN
(
select
IFNULL(w.area,IFNULL(w.city,w.province)) as areaCode,
count(1) as approveRoleCount
from
sp_workflow w
where
w.active=1
and
w.province=#{province}
and w.city=#{city}
and w.area=#{area}
GROUP BY
IFNULL(w.area,IFNULL(w.city,w.province))
)summary2
on summary1.areaCode =summary2.areaCode
LEFT JOIN
(
select
IFNULL(w.area,IFNULL(w.city,w.province)) as areaCode,
count(1) as workFlowCount
from
sp_workflow w
where
w.active=1
and
w.province=#{province}
and w.city=#{city}
and w.area=#{area}
GROUP BY
IFNULL(w.area,IFNULL(w.city,w.province))
)summary3
on summary1.areaCode =summary3.areaCode
INNER JOIN base_region r on summary1.areaCode=r.id
select count(1) from (
select *
from
sp_se_user
where
active=1
and role='ADMIN'
and regionLevel = #{regionLevel}
and(
(province=#{province} and city=#{city} and area=#{area} and regionLevel=3)
or
(province=#{province} and city=#{city} and (area='' or area is null) and regionLevel=2)
or
(province=#{province} and (city='' or city is null) and (area='' or area is null) and regionLevel=1)
)
SELECT
b.regionLevel,
b.city,
b.area,
b.realName
FROM
sp_approve a
INNER JOIN sp_se_user b ON a.userId = b.id
WHERE
a.activityId = #{activityId}
AND a.approveType = '20'
AND a.approve = 'SL30'
select *
from
sp_se_user
where
active=1
and (role='ADMIN' OR role = 'APPROVE')
and regionLevel = #{approveLevel}
and(
(province=#{province} and city=#{city} and area=#{area} and regionLevel=3)
or
(province=#{province} and city=#{city} and (area='' or area is null) and regionLevel=2)
or
(province=#{province} and (city='' or city is null) and (area='' or area is null) and regionLevel=1)
)
select
cast(t2.seq AS CHAR)
from sp_se_user t1
inner join sp_workflow t2 on t1.workrole = t2.role
where t1.id = #{userId}
and t2.active = 1
order by t2.seq
limit 1