a.id,
a.pssId,
a.activityNo,
a.fullName,
a.typeCode,
a.province,
a.city,
a.area,
a.address,
a.number,
a.startDate,
a.endDate,
a.amount,
a.sponsor,
a.sponsorAddress,
a.zsponsor,
a.organizer,
a.assessmentorg,
a.isGov,
a.approveStatus,
a.approveDate,
a.surveyInfo,
a.isExported,
a.workflowVersion,
a.sync,
a.updater,
a.history,
a.addTime,
a.updateTime,
a.rejectContent,
a.isTransfer,
a.transferContent,
a.isBackUp,
a.isNeedReport,
a.nextApproveUser,
a.nextApproveRole,
a.coordinate,
a.introduce,
a.reContent,
IF(a.reportTime IS NULL OR a.reportTime = '','还未进行上报',CONCAT('上一次上报时间为：',a.reportTime)) reportTime,
a.isAddFiles
SELECT
SELECT
a.activityNo,
a.fullName,
a.number,
a.sponsor,
a.zsponsor,
CONCAT(DATE_FORMAT(a.startDate,'%Y-%m-%d %H:%i'),'~',DATE_FORMAT(a.endDate,'%Y-%m-%d %H:%i')) AS timeInterval,
(SELECT v.fullName FROM base_venue v inner JOIN sp_activity_venue av ON v.id = av.venueId WHERE av.activityId = a.id LIMIT 1) AS venueName,
CONCAT(
(SELECT r.fullName from base_region r where a.province=r.id),' '
,(SELECT r.fullName from base_region r where a.city=r.id),' '
,(SELECT r.fullName from base_region r where a.area=r.id),' '
,a.address) AS addr
FROM
sp_activity a
WHERE a.id = #{activityId}
SELECT
AND a.province = #{province}
AND a.city = #{city}
and a.area is null
AND a.city = #{city}
AND a.area = #{area}
AND a.isBackUp = 1
AND a.isBackUp = 0
AND a.approveStatus = #{approveStatus}
AND a.approveStatus in ('10','20','30')
AND a.approveStatus in ('SL10','SL20','SL30')
AND a.approveStatus in ('NONE','IN','REJECT')
AND a.approveStatus in ('YES','NO')
AND a.province = #{sProvince}
AND a.city = #{sCity}
AND a.area = #{sArea}
AND DATE_FORMAT(a.addTime,'%Y-%m-%d') = #{addTime}
AND a.startDate >= #{startDate}
AND a.startDate #{endDate}
AND a.endDate >= #{finishStartDate}
AND a.endDate #{finishEndDate}
AND a.sync = #{sync}
AND a.typeCode = #{typeCode}
AND a.addTime >= #{addTimeStartDate}
AND a.addTime #{addTimeEndDate}
AND (a.fullName like concat('%',#{keywords}, '%')
OR a.address like concat('%',#{keywords}, '%')
OR u.realName like concat('%',#{keywords}, '%')
OR u.userName like concat('%',#{keywords}, '%')
OR sponsor like concat('%',#{keywords}, '%'))
AND (a.fullName like concat('%',#{keywords}, '%')
OR a.address like concat('%',#{keywords}, '%')
OR a.sponsor like concat('%',#{keywords}, '%'))
AND a.activityChannel = #{activityChannel}
AND a.isBackUp = 0
AND a.isBackUp = 0
AND a.isBackUp = 1
ORDER BY a.id DESC
limit #{iDisplayStart},#{iDisplayLength}
SELECT
DISTINCT
AND a.province = #{province}
AND a.city = #{city}
AND a.city = #{city}
AND a.area = #{area}
AND a.isBackUp = 1
AND a.isBackUp = 0
AND a.approveStatus = #{approveStatus}
AND a.approveStatus in ('10','20','30')
AND a.approveStatus in ('SL10','SL20','SL30')
AND a.approveStatus in ('NONE','IN','REJECT')
AND a.approveStatus in ('YES','NO')
AND a.province = #{sProvince}
AND a.city = #{sCity}
AND a.area = #{sArea}
AND DATE_FORMAT(a.addTime,'%Y-%m-%d') = #{addTime}
AND a.startDate >= #{startDate}
AND a.startDate #{endDate}
AND a.endDate >= #{finishStartDate}
AND a.endDate #{finishEndDate}
AND a.sync = #{sync}
AND a.typeCode = #{typeCode}
AND a.addTime >= #{addTimeStartDate}
AND a.addTime #{addTimeEndDate}
AND a.approveDate >= #{addApproveDate}
AND a.approveDate #{endApproveDate}
AND (a.fullName like concat('%',#{keywords}, '%')
OR a.address like concat('%',#{keywords}, '%')
OR u.realName like concat('%',#{keywords}, '%')
OR u.userName like concat('%',#{keywords}, '%')
OR sponsor like concat('%',#{keywords}, '%'))
AND (a.fullName like concat('%',#{keywords}, '%')
OR a.address like concat('%',#{keywords}, '%')
OR a.sponsor like concat('%',#{keywords}, '%'))
AND a.activityChannel = #{activityChannel}
AND a.isBackUp = 0
AND a.isBackUp = 0
AND a.isBackUp = 1
AND a.approveLevel = #{regionLevel}
ORDER BY a.id DESC
limit #{iDisplayStart},#{iDisplayLength}
SELECT
CONCAT(a.id,'') AS id,
a.activityNo,
a.fullName,
CONCAT(DATE_FORMAT(a.startDate,'%Y-%m-%d %H:%i'),'~',DATE_FORMAT(a.endDate,'%Y-%m-%d %H:%i')) AS timeInterval,
(SELECT v.fullName FROM base_venue v inner JOIN sp_activity_venue av ON v.id = av.venueId WHERE av.activityId = a.id LIMIT 1) AS venueName
FROM
sp_activity a
WHERE 1 = 1
AND a.isBackUp = 0
and a.approveLevel = #{approveLevel}
ORDER BY a.id DESC
SELECT
DISTINCT a.*
FROM
sp_activity a,sp_workflow b
WHERE
a.approveStatus IN ('YES','NO','REJECT')
and a.sync != '1'
and a.pssId is not null
and a.isBackUp = 0
and a.approveLevel = #{approveLevel}
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
and a.workflowVersion=b.version
SELECT
DISTINCT a.*
from sp_activity a
left join sp_approve b on a.id = b.activityId
where b.id is null
and isBackUp = 0
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
SELECT
DISTINCT a.*
FROM
sp_activity a
WHERE 1=1
and a.approveStatus IN ('YES','NO','REJECT')
and a.sync != '1'
and a.pssId is not null
and a.isBackUp = 0
and a.workflowVersion is not null
SELECT
DISTINCT a.*
FROM
sp_activity a
WHERE 1=1
and a.approveStatus IN ('20','30','SL20','SL30')
and a.sync != '1'
and a.pssid is not null
and a.isBackUp = 0
and a.approveLevel = #{approveLevel}
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
SELECT
DISTINCT a.*
FROM
sp_activity a
WHERE
1=1
and a.approveStatus IN ('20','30','SL20','SL30')
and a.sync != '1'
and a.pssId is not null
and a.isBackUp = 0
SELECT
DISTINCT a.*
FROM
sp_activity a
WHERE
(a.workflowVersion is null or a.workflowVersion='')
AND a.province = #{value}
SELECT
COUNT(id)
FROM
sp_activity a
WHERE 1=1
AND a.province = #{province}
AND a.city = #{city}
AND a.city = #{city}
AND a.area = #{area}
AND a.isBackUp = 1
AND a.isBackUp = 0
AND a.approveStatus = #{approveStatus}
AND a.approveStatus in ('10','20','30')
AND a.approveStatus in ('SL10','SL20','SL30')
AND a.approveStatus in ('NONE','IN','REJECT')
AND a.approveStatus in ('YES','NO')
AND a.province = #{sProvince}
AND a.city = #{sCity}
AND a.area = #{sArea}
AND DATE_FORMAT(a.addTime,'%Y-%m-%d') = #{addTime}
AND startDate >= #{startDate}
AND startDate #{endDate}
AND endDate >= #{finishStartDate}
AND endDate #{finishEndDate}
AND a.approveDate >= #{addApproveDate}
AND a.approveDate #{endApproveDate}
AND sync = #{sync}
AND typeCode = #{typeCode}
AND addTime >= #{addTimeStartDate}
AND addTime #{addTimeEndDate}
AND (fullName like concat('%',#{keywords}, '%')
OR address like concat('%',#{keywords}, '%')
OR u.realName like concat('%',#{keywords}, '%')
OR u.userName like concat('%',#{keywords}, '%')
OR sponsor like concat('%',#{keywords}, '%'))
AND (fullName like concat('%',#{keywords}, '%')
OR address like concat('%',#{keywords}, '%')
OR sponsor like concat('%',#{keywords}, '%'))
AND activityChannel = #{activityChannel}
AND a.isBackUp = 0
AND a.isBackUp = 0
AND a.isBackUp = 1
AND a.approveLevel = #{regionLevel}
SELECT
COUNT(1)
FROM
sp_activity a
where workflowVersion= #{version}
SELECT
COUNT(a.id)
FROM
sp_activity a
left join sp_approve app on app.activityId = a.id
${param1}
and ( app.userId = #{param2} or a.approveStatus = 'NONE')
${value}
SELECT
a.id as activityId,
a.fullName as activityName,
a.pssId as pssId,
a.activityNo as activityNo,
b.approveStatus as approveStatus,
a.rejectContent as approveContent,
a.updateTime as approvalTime,
a.province as province,
a.city as city,
a.area as area
FROM
sp_activity a
inner join sp_sync_Task b on b.activityId = a.id
WHERE 1=1
and b.sync = '0'
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
UPDATE
sp_activity a
SET
sync = 0
WHERE
approveStatus = 'IN'
update sp_activity set area=null,unit=null
where id=#{activityId}
UPDATE
sp_activity a
inner join sp_activity_material m on m.activityId = a.id
SET
sync = 0,
approveStatus = 'SL10'
WHERE
approveStatus = '30'
and m.pgbg is not null
UPDATE
sp_activity a
SET
approveStatus = #{approveStatus},
reContent = #{reContent}
WHERE
id = #{activityId}
SELECT
a.id,
a.approveStatus,
u.id as 'u_id',
u.realName as 'u_realName',
ar.fullName as 'u_workrolename'
FROM
sp_activity a
INNER JOIN sp_workflow w ON w.version = a.workflowVersion AND w.seq = 1 and a.approveStatus in ('10','SL10','NONE')
INNER JOIN sp_se_user u ON u.workRole = w.role
INNER JOIN sp_approve_role ar ON u.workRole = ar.id
WHERE a.id = #{activityId}
UNION
SELECT
a.id,
a.approveStatus,
u.id as 'u_id',
u.realName as 'u_realName',
ar.fullName as 'u_workrolename'
FROM
sp_activity a
INNER JOIN sp_approve ap ON ap.activityId = a.id and a.approveStatus='IN' and ap.logicDel = 0 and ap.approve is null
INNER JOIN sp_se_user u ON u.id = ap.userId
INNER JOIN sp_approve_role ar ON u.workRole = ar.id
WHERE a.id = #{activityId}
SELECT
a.fullName as activityName ,
case
when a.approveStatus = '10' then '初审中'
when a.approveStatus = '20' then '初审修改'
when a.approveStatus = '30' then '初审通过'
when a.approveStatus = 'SL10' then '受理中'
when a.approveStatus = 'SL10' then '不受理'
when a.approveStatus = 'SL10' then '受理通过'
when a.approveStatus = 'NONE' then '未审核'
when a.approveStatus = 'IN' then '审核中'
when a.approveStatus = 'YES' then '许可'
when a.approveStatus = 'NO' then '不许可'
when a.approveStatus = 'REJECT' then '打回'
else '' end as approveStatus,
a.number ,
CASE
WHEN a.approveLevel = 1 THEN
'省'
WHEN a.approveLevel = 2 THEN
'市'
WHEN a.approveLevel = 3 THEN
'区' ELSE '无'
END AS approveLevel,
w.seq as approveSeq,
w.fullName as workRole,
u.username,
u.id as userId,
u.realName,
u.password,
p.fullName as province,
c.fullName as city,
ar.fullName
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
INNER JOIN base_region p ON p.id = a.province
INNER JOIN base_region c ON c.id = a.city
LEFT JOIN base_region ar ON ar.id = a.area
WHERE
a.id = #{activityId}
order by w.seq
select distinct B.userId,C.userName from sp_activity A
inner join sp_approve B on A.id=B.activityId
inner join sp_approve_history C on B.id=C.approveId
where A.id=#{activityId} and B.approve is not null
SELECT DISTINCT
u.username,
u.id as userId
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
INNER JOIN base_region p ON p.id = a.province
INNER JOIN base_region c ON c.id = a.city
LEFT JOIN base_region ar ON ar.id = a.area
WHERE a.id = #{activityId}
order by w.seq
select
ifnull(sum(case when a.approveStatus = '10' then 1 else 0 end ),0) as 'csz', /* 初审中 */
ifnull(sum(case when a.approveStatus = '20' then 1 else 0 end ),0) as 'csxg',/*初审修改*/
ifnull(sum(case when a.approveStatus = '30' then 1 else 0 end ),0) as 'cstg',/*初审通过*/
ifnull(sum(case when a.approveStatus = 'SL10' then 1 else 0 end ),0) as 'slz',/*受理中*/
ifnull(sum(case when a.approveStatus = 'SL20' then 1 else 0 end ),0) as 'bsl',/*不受理*/
ifnull(sum(case when a.approveStatus = 'SL30' then 1 else 0 end ),0) as 'ysl',/*已受理*/
ifnull(sum(case when a.approveStatus = 'NONE' then 1 else 0 end ),0) as 'wsp',/*未审批*/
ifnull(sum(case when a.approveStatus = 'IN' then 1 else 0 end ),0) as 'spz',/*审批中*/
ifnull(sum(case when a.approveStatus = 'REJECT' then 1 else 0 end ),0) as 'spth'/*审批退回*/
from sp_activity a
WHERE 1=1
and a.isBackUp = '0'
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
AND a.approveLevel = #{regionLevel}
select
ifnull(sum(case when a.approveStatus = 'YES' then 1 else 0 end ),0) as 'xk',/*许可*/
ifnull(sum(case when a.approveStatus = 'NO' then 1 else 0 end ),0) as 'bxk'/*不许可*/
from sp_activity a
WHERE 1=1
and a.isBackUp = '0'
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
AND a.addTime between #{beginDate} and CONCAT(#{endDate},' 23:59:59')
AND a.approveLevel = #{regionLevel}
select
ifnull(sum(case when isBackUp = '0' then 1 else 0 end ),0) 'xksp',/*许可审批*/
ifnull(sum(case when isBackUp = '1' then 1 else 0 end ),0) 'bb' /*报备*/
from sp_activity a
where addTime BETWEEN CURDATE() and now()
and a.province = #{province}
AND a.city = #{city}
AND a.area = #{area}
AND a.approveLevel = #{regionLevel}
SELECT a.id
FROM sp_activity a
WHERE a.activityNo = #{activityNo}
SELECT
t1.id,t1.activityNo
FROM sp_activity t1
where 1=1
and t1.province = #{province}
AND t1.city = #{city}
AND t1.area = #{area}
order by startDate desc
limit 1
UPDATE
sp_activity a
SET
isAddFiles = #{isAddFiles}
and id = #{activityId}
and pssId=#{pssId}
SELECT
a.id fkId,
a.activityNo actNo,
a.approveStatus approvalStatus
FROM
sp_activity a
WHERE 1=1
AND a.updateTime IS NOT NULL
AND a.updateTime>=DATE_ADD(NOW(), INTERVAL -2 HOUR)
SELECT
a.id fkId,
a.activityNo actNo,
a.fullName actName,
a.typeCode actType,
a.province,
a.city,
a.area,
a.address holdAddr,
a.number maxPersonNumber,
a.startDate beginTime,
a.endDate endTime,
a.sponsor organizerUnit,
a.sponsor reportUnit,
a.unit,
a.organizer responsibleUnit,
a.isGov,
a.approveStatus approvalStatus,-- TODO 需要映射
'未归档' archiveStatus,
'大活审批系统' dataSource,
a.addTime actReportTime,
a.addTime createTime,
a.updateTime,
a.rejectContent,
m.hdnr actContent,
m.baryzs securityNumber,
m.cdrl maxPersonNumber,
m.cdmj siteArea,
m.nspzs soldTicketsNumber,
m.zyzzs volunteerNumber,
a.reContent,
(SELECT IF(r.gradeRank=1,'一级',IF(r.gradeRank=2,'二级',IF(r.gradeRank=3,'三级',null))) FROM sp_approve r WHERE a.id=r.activityId AND r.approveType='10' AND r.approve='30' AND r.logicDel=0 AND r.isActive=1 AND r.gradeRank IS NOT NULL) riskLevel,
(SELECT r.address from sp_activity_contractor r where a.id=r.activityId) organizerRegist,
(SELECT r.fullName from base_region r where a.province=r.id) provincename,
(SELECT r.fullName from base_region r where a.city=r.id) cityname,
(SELECT r.fullName from base_region r where a.area=r.id) areaname
FROM
sp_activity a
LEFT JOIN sp_activity_detail m ON a.id=m.activityId
WHERE 1=1
AND instr(a.addTime, #{day}) OR instr(a.updateTime, #{day})
SELECT
a.id fkId,
a.activityNo actNo,
a.fullName actName,
a.typeCode actType,
a.province,
a.city,
a.area,
a.address holdAddr,
a.number maxPersonNumber,
a.startDate beginTime,
a.endDate endTime,
a.sponsor organizerUnit,
a.sponsor reportUnit,
a.unit,
a.organizer responsibleUnit,
a.isGov,
a.approveStatus approvalStatus,-- TODO 需要映射
'未归档' archiveStatus,
'大活审批系统' dataSource,
a.addTime actReportTime,
a.addTime createTime,
a.updateTime,
a.rejectContent,
m.hdnr actContent,
m.baryzs securityNumber,
m.cdrl maxPersonNumber,
m.cdmj siteArea,
m.nspzs soldTicketsNumber,
m.zyzzs volunteerNumber,
a.reContent,
(SELECT IF(r.gradeRank=1,'一级',IF(r.gradeRank=2,'二级',IF(r.gradeRank=3,'三级',null))) FROM sp_approve r WHERE a.id=r.activityId AND r.approveType='10' AND r.approve='30' AND r.logicDel=0 AND r.isActive=1 AND r.gradeRank IS NOT NULL) riskLevel,
(SELECT r.address from sp_activity_contractor r where a.id=r.activityId) organizerRegist,
(SELECT r.fullName from base_region r where a.province=r.id) provincename,
(SELECT r.fullName from base_region r where a.city=r.id) cityname,
(SELECT r.fullName from base_region r where a.area=r.id) areaname
FROM
sp_activity a
LEFT JOIN sp_activity_detail m ON a.id=m.activityId
WHERE 1=1
AND instr(a.addTime, #{day}) OR instr(a.updateTime, #{day})
AND a.activityNo IN (
#{item}
)
SELECT
a.id fkId,
a.activityNo actNo,
a.fullName actName,
a.typeCode actType,
a.province,
a.city,
a.area,
a.address holdAddr,
a.number maxPersonNumber,
a.startDate beginTime,
a.endDate endTime,
a.sponsor organizerUnit,
a.sponsor reportUnit,
a.unit,
a.organizer responsibleUnit,
a.isGov,
a.approveStatus approvalStatus,-- TODO 需要映射
'未归档' archiveStatus,
'大活审批系统' dataSource,
a.addTime actReportTime,
a.addTime createTime,
a.updateTime,
a.rejectContent,
m.hdnr actContent,
m.baryzs securityNumber,
m.cdrl maxPersonNumber,
m.cdmj siteArea,
m.nspzs soldTicketsNumber,
m.zyzzs volunteerNumber,
a.reContent,
(SELECT IF(r.gradeRank=1,'一级',IF(r.gradeRank=2,'二级',IF(r.gradeRank=3,'三级',null))) FROM sp_approve r WHERE a.id=r.activityId AND r.approveType='10' AND r.approve='30' AND r.logicDel=0 AND r.isActive=1 AND r.gradeRank IS NOT NULL) riskLevel,
(SELECT r.address from sp_activity_contractor r where a.id=r.activityId) organizerRegist,
(SELECT r.fullName from base_region r where a.province=r.id) provincename,
(SELECT r.fullName from base_region r where a.city=r.id) cityname,
(SELECT r.fullName from base_region r where a.area=r.id) areaname
FROM
sp_activity a
LEFT JOIN sp_activity_detail m ON a.id=m.activityId
WHERE 1=1
AND (instr(a.addTime, #{day}) OR instr(a.updateTime, #{day}))
AND a.activityNo NOT IN (
#{item}
)