ALTER PROCEDURE [dbo].[SLTJ](@pId VARCHAR(50))
AS
BEGIN
DECLARE
@addDate Date,@userOrgId INT,@purpose VARCHAR(50),@handlePersonId INT,@isfirst INT,@typeLetter INT,
@xfsl INT, @xfjssl INT, @xfcqsl INT,
@xfcqwsl INT, @jgsl INT, @jgjssl INT, @jgcqsl INT, @jgcqwsl INT,
@ydf INT, @aqdf INT, @cqdf INT, @cqwddf INT,
@xfmy INT, @xfjbmy INT, @xfbmy INT, @xfyipj INT,@xfcqwpj INT,@xfyinpj INT,
@jgmy INT, @jgjbmy INT, @jgbmy INT, @jgyipj INT,@jgcqwpj INT,@jgyinpj INT,
@path VARCHAR(50)
set @path = '%.'+@pid+'.%'
--@tempCursor VARCHAR(50);
CREATE TABLE ##SLTJ
(
[addDate] [date] ,
[userOrgId] [int],
[purpose] [VARCHAR](50) ,
[handlePersonId] [int] ,
[isfirst] [int],
[typeLetter] [int] ,
[xfsl] [int],
[xfjssl] [int],
[xfcqsl] [int],
[xfcqwsl] [int],
[jgsl] [int],
[jgjssl] [int],
[jgcqsl] [int],
[jgcqwsl] [int],
[ydf] [int],
[aqdf] [int],
[cqdf] [int],
[cqwddf] [int],
[xfmy] [int],
[xfjbmy] [int],
[xfbmy] [int],
[xfyipj] [int],
[xfcqwpj] [int],
[xfyinpj] [int],
[jgmy] [int],
[jgjbmy] [int],
[jgbmy] [int],
[jgyipj] [int],
[jgcqwpj] [int],
[jgyinpj] [int]
)
DECLARE tempCursor CURSOR
FOR
select letter.addDate,letter.userOrgId,letter.purpose,letter.handlePersonId,
case when letter.oldInsertId <> 0 then 1 else 0 end as isfirst,letter.typeLetter,
SUM(case when letter.handle = '交办' or letter.handle = '转送' then '1' ELSE 0 end) as xfsl, /*信访受理*/
SUM(case when jiaobanhan.updatedAt <= dateadd(day,15,letter.addDate) AND letter.handle='交办' And jiaobanhan.overFlag = 1 then 1
when letter.handle='转送' and info.createdAt <= dateadd(day,15,letter.addDate) then 1
else 0 end) as xfjssl, /*信访及时受理*/
SUM(case when jiaobanhan.updatedAt > dateadd(day,15,letter.addDate) AND letter.handle='交办' And jiaobanhan.overFlag = 1 then 1
when letter.handle='转送' and info.createdAt > dateadd(day,15,letter.addDate) then 1
else 0 end) as xfcqsl, /*信访超期受理*/
SUM(case when Getdate() > dateadd(day,15,letter.addDate) AND letter.handle='交办' And (jiaobanhan.overFlag = 0 or jiaobanhan.id is null )then 1
when letter.handle='转送' and Getdate() > dateadd(day,15,letter.addDate) and info.id is null then 1
else 0 end) as xfcqwsl, /*信访超期未受理*/
SUM (case when letter.handle='交办' And jiaobanhan.overFlag = 1 then 1
when letter.handle='转送' and info.orgId <> 0 then 1 else 0 END ) as jgsl, /*有权机构受理*/
SUM (case when letter.handle='交办' And jiaobanhan.overFlag = 1 and mail.createdAt <= dateadd(day,15,jiaobanhan.updatedAt) then 1
when letter.handle='转送' and mail.createdAt <= dateadd(day,15,info.createdAt) then 1
when letter.handle='自办' and info.createdAt <= dateadd(day,15,letter.addDate) then 1 else 0 END ) as jgjssl, /*有权机构及时受理*/
SUM (case when letter.handle='交办' And jiaobanhan.overFlag = 1 and mail.createdAt > dateadd(day,15,jiaobanhan.updatedAt) then 1
when letter.handle='转送' and mail.createdAt > dateadd(day,15,info.createdAt) then 1
when letter.handle='自办' and info.createdAt > dateadd(day,15,letter.addDate) then 1 else 0 END ) as jgcqsl, /*有权机构超期受理*/
SUM (case when letter.handle='交办' And jiaobanhan.overFlag = 1 and Getdate() > dateadd(day,15,jiaobanhan.updatedAt) and mail.id is null then 1
when letter.handle='转送' and Getdate() > dateadd(day,15,info.createdAt) and mail.id is NULL then 1
when letter.handle='自办' and Getdate() > dateadd(day,15,letter.addDate) and info.id is NULL then 1 else 0 END ) as jgcqwsl, /*有权机构超期未受理*/
SUM (case when letter.handle='交办' and letter.handlePersonId <> 0 then 1
when (letter.handle='自办' or letter.handle = '转送') and info.orgId is not null then 1 else 0
END) as ydf, /*应答复*/
SUM(case when letter.handle='交办' and jiaobanhan.days <> 0 and jiaobanhan.id is not null and replay.createdAt < dateadd(day,jiaobanhan.days ,letter.addDate)
and replay.id is not NULL then 1
when letter.handle='交办' and jiaobanhan.days = 0 and jiaobanhan.id is not null and replay.createdAt < dateadd(day,60 ,letter.addDate)
and replay.id is not NULL then 1
when (letter.handle='自办' or letter.handle = '转送') and replay.createdAt < dateadd(day,60 ,letter.addDate)
and replay.id is not NULL then 1
else 0 end) as aqdf, /*按期答复*/
SUM(case when letter.handle='交办' and jiaobanhan.days <> 0 and jiaobanhan.id is not null and replay.createdAt > dateadd(day,jiaobanhan.days ,letter.addDate)
and replay.id is not NULL then 1
when letter.handle='交办' and jiaobanhan.days = 0 and jiaobanhan.id is not null and replay.createdAt > dateadd(day,60 ,letter.addDate)
and replay.id is not NULL then 1
when (letter.handle='自办' or letter.handle = '转送') and replay.createdAt > dateadd(day,60 ,letter.addDate )
and replay.id is not NULL then 1
else 0 end) as cqdf, /*超期答复*/
SUM(case when letter.handle='交办' and jiaobanhan.days <> 0 and jiaobanhan.id is not null and Getdate() > dateadd(day,jiaobanhan.days ,letter.addDate)
and replay.id is NULL then 1
when letter.handle='交办' and jiaobanhan.days = 0 and jiaobanhan.id is not null and Getdate() > dateadd(day,60 ,letter.addDate)
and replay.id is NULL then 1
when (letter.handle='自办' or letter.handle = '转送') and Getdate() > dateadd(day,60 ,letter.addDate )
and replay.id is NULL then 1
else 0 end) as cqwddf, /*超期未答复*/
SUM(case when replay.id is not null and letter.pleased = 1 then 1 else 0 end) as xfmy, /*信访机构满意*/
SUM(case when replay.id is not null and letter.pleased = 3 then 1 else 0 end) as xfjbmy, /*基本满意*/
SUM(case when replay.id is not null and letter.pleased = 2 then 1 else 0 end) as xfbmy, /*不满意*/
SUM(case when replay.id is not null and letter.pleased is not NULL then 1 else 0 end) as xfyipj, /*已评价*/
SUM(case when replay.id is not null and letter.pleased is NULL and Getdate() > dateadd(day,30 ,replay.createdAt) then 1 else 0 end) as xfcqwpj, /*超期未评价*/
SUM(case when replay.id is not null then 1 else 0 end) as xfyinpj,/*应评价*/
SUM(case when replay.id is not null and letter.orgPleased = 1 then 1 else 0 end) as jgmy,/*有权机构满意*/
SUM(case when replay.id is not null and letter.orgPleased = 3 then 1 else 0 end) as jgjbmy,/*基本满意*/
SUM(case when replay.id is not null and letter.orgPleased = 2 then 1 else 0 end) as jgbmy,/*不满意*/
SUM(case when replay.id is not null and letter.orgPleased is not NULL then 1 else 0 end) as jgyipj,/*已评价*/
SUM(case when replay.id is not null and letter.orgPleased is NULL and Getdate() > dateadd(day,30 ,replay.createdAt) then 1 else 0 end) as jgcqwpj, /*超期未评价*/
SUM(case when replay.id is not null then 1 else 0 end) as jgyinpj /*应评价*/
from HpisLetter letter left join HpisJiaobanhan jiaobanhan on letter.insertId = jiaobanhan.insertId
LEFT JOIN HpisHandleInfo info on letter.insertId = info.insertId
LEFT JOIN HpisReportMail mail on letter.insertId = mail.insertId
LEFT JOIN HpisReplyMail replay on letter.insertId = replay.insertId
LEFT JOIN CommArea c on c.id = letter.pAreaId where c.path like @path or c.id=@pId
group by letter.addDate,letter.userOrgId,letter.oldInsertId,letter.typeLetter,letter.purpose,letter.handlePersonId
OPEN tempCursor;
FETCH NEXT FROM tempCursor INTO
@addDate,@userOrgId,@purpose,@handlePersonId,@isfirst,@typeLetter,
@xfsl, @xfjssl, @xfcqsl,
@xfcqwsl, @jgsl, @jgjssl, @jgcqsl, @jgcqwsl,
@ydf, @aqdf, @cqdf, @cqwddf,
@xfmy, @xfjbmy, @xfbmy, @xfyipj,@xfcqwpj,@xfyinpj,
@jgmy, @jgjbmy, @jgbmy, @jgyipj,@jgcqwpj,@jgyinpj;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into ##SLTJ ( addDate,userOrgId,purpose,handlePersonId,isfirst,typeLetter,
xfsl, xfjssl, xfcqsl,
xfcqwsl, jgsl,jgjssl,jgcqsl,jgcqwsl,
ydf, aqdf, cqdf, cqwddf,
xfmy, xfjbmy, xfbmy, xfyipj,xfcqwpj,xfyinpj,
jgmy, jgjbmy, jgbmy, jgyipj,jgcqwpj,jgyinpj
) values ( @addDate,@userOrgId,@purpose,@handlePersonId,@isfirst,@typeLetter,
@xfsl, @xfjssl, @xfcqsl,
@xfcqwsl,@jgsl,@jgjssl,@jgcqsl, @jgcqwsl,
@ydf, @aqdf, @cqdf, @cqwddf,
@xfmy, @xfjbmy, @xfbmy, @xfyipj,@xfcqwpj, @xfyinpj,
@jgmy, @jgjbmy, @jgbmy, @jgyipj,@jgcqwpj, @jgyinpj
);
FETCH NEXT FROM tempCursor INTO @addDate,@userOrgId,@purpose,@handlePersonId,@isfirst,@typeLetter,
@xfsl, @xfjssl, @xfcqsl,
@xfcqwsl, @jgsl, @jgjssl, @jgcqsl, @jgcqwsl,
@ydf, @aqdf, @cqdf, @cqwddf,
@xfmy, @xfjbmy, @xfbmy, @xfyipj,@xfcqwpj,@xfyinpj,
@jgmy, @jgjbmy, @jgbmy, @jgyipj,@jgcqwpj,@jgyinpj;
END
CLOSE tempCursor
DEALLOCATE tempCursor
END