项目组长的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: <Author,,wmh>
-- Create date: <Create Date,,2010-07-20>
-- Description: <Description,,列出考核自动计算信息>
-- @checkid 被考核ID
-- @itemKind 项目种类，如1：人员考核，2：窗口考核等
-- @type 1：月考核，2：季度考核，3：年考核
-- =============================================
ALTER PROCEDURE [dbo].[p_kh_autocheckrecord]
(@checkid bigint,
@startDate varchar(20),
@endDate varchar(20),
@itemcode varchar(50),
@itemKind int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--定义临时表空间
CREATE TABLE #Temp
(
userid bigint,
deptid bigint,
--deptName varchar(200), --部门名称
--userName varchar(100), --人员名称
recordScores decimal(18,2), --分值
recordDate datetime, --考核日期
recorder varchar(50), --考核人员
itemcode varchar(50), --项目编号
ItemName varchar(100),--项目名称
recordName ntext,--考核内容
itemKind varchar(20) --项目类型
--ifcancel varchar(2) --是否取消年度评优资格
);
DECLARE @recordCount int,@iTemp int;
DECLARE @username varchar(200),@deptname varchar(200);
DECLARE @userCode varchar(200);
DECLARE @ruleName nvarchar(4000);
DECLARE @recordScores decimal(18,2);
DECLARE @sql nvarchar(4000);
DECLARE @sqlCondition nvarchar(800);
DECLARE @id int,@userid int;
SET @recordCount=0;
SET @iTemp=0;
DECLARE @veryRate decimal(5,2),@noVeryRate decimal(5,2),@sumCnt int,@veryCnt int,@noVeryCnt int,@apprusercode varchar(50);
IF @itemKind=1 BEGIN--人员考核
--1.向当事人解答问题含糊，或没一次性完整地告知应提交的申报材料，导致服务对象两次以上(含两次)补件的，每件次扣2分；当月发生3件次的，扣10分。
DECLARE @casecnt int ;
SET @sql='DECLARE R_CRSR CURSOR FOR select c.casereceiver,count(caseid)as caseCnt from t_casebaseinfo c
where casesupplytimes>=2
and datediff(day,'''+@startDate+''',casestartdate)>=0
and datediff(day,casestartdate,'''+@endDate+''')>0 ';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and c.casereceiver='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by c.casereceiver ';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @userid,@casecnt;
WHILE @@fetch_status = 0 BEGIN
SET @recordScores=@casecnt*2;
SET @ruleName='有'+cast(@casecnt as varchar(20))+'办件,补办两次或两次以上，扣分'+cast(@recordScores as varchar(20))+';';
IF @casecnt>=3 BEGIN
SET @recordScores=@recordScores+10;
SET @ruleName=@ruleName+'当月发生3件次,扣分10分';
END
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','101','行为规范',@ruleName,'人员考核');
FETCH next FROM R_CRSR into @userid,@casecnt;
END
close R_CRSR
deallocate R_CRSR

--2.迟到、早退的每次扣0.5分；擅自脱岗30分钟的，每次扣2分；脱岗60分钟以上的按旷工处理。无故旷工的，每半天扣4分；累计旷工3次的，加扣2分。
SET @recordScores=0;
SET @ruleName='';
DECLARE @laterCnt int,@earlyCnt int,@awayCnt decimal(18,1);
SET @sql='DECLARE R_CRSR CURSOR FOR SELECT a.userid,sum(laterCnt)laterCnt,sum(earlyCnt)earlyCnt,sum(awayCnt)awayCnt
FROM T_kq_userAttendinfo a where
datediff(day,'''+@startDate+''',attendday)>=0
and datediff(day,attendday,'''+@endDate+''')>0';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and a.userid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by a.userid';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @userid,@laterCnt,@earlyCnt,@awayCnt;
WHILE @@fetch_status = 0 BEGIN
IF @laterCnt>0 BEGIN
SET @recordScores=@laterCnt*0.5;
SET @ruleName='迟到'+cast(@laterCnt as varchar(20))+'次,扣'+cast((@laterCnt*0.5) as varchar(20))+'分;';
END
IF @earlyCnt>0 BEGIN
SET @recordScores=@recordScores+@earlyCnt*0.5;
SET @ruleName=@ruleName+'早退'+cast(@earlyCnt as varchar(20))+'次,扣'+cast((@earlyCnt*0.5) as varchar(20))+'分;';
END
IF @awayCnt>0 BEGIN
SET @recordScores=@recordScores+@awayCnt/0.5*4;
SET @ruleName=@ruleName+'无故旷工'+cast(@awayCnt as varchar(20))+'天,扣'+cast((@awayCnt/0.5*4) as varchar(20))+'分;';
END
IF @awayCnt>=3 BEGIN
SET @recordScores=@recordScores+2;
SET @ruleName=@ruleName+'无故旷工累计'+cast(@awayCnt as varchar(20))+'天,加扣2分;';
END
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','103','出勤管理',@ruleName,'人员考核');
FETCH next FROM R_CRSR into @userid,@laterCnt,@earlyCnt,@awayCnt;
END
close R_CRSR
deallocate R_CRSR
--3.工作人员月上岗率不低于８5％，每低于１个百分点扣1分。注：1、上岗率=（应出勤工作日-229事假工作日-233病假工作日-旷工工作日-235休假工作日）÷应出勤工作日×100%
SET @recordScores=0;
SET @ruleName='';
DECLARE @realOnCnt int,@yingOnCnt int,@notNoCnt int;
DECLARE @noRate decimal(10,2);
SET @sql='DECLARE R_CRSR CURSOR FOR SELECT a.userid,round((count(*)-dbo.f_getLeaveTypeDays('''+@startDate+''','''+@endDate+''',''229,233,235'','+cast(@checkid as varchar(20))+',1)-sum(awayCnt))/count(*)*100,2) as onRate
FROM T_kq_userAttendinfo a where
datediff(day,'''+@startDate+''',attendday)>=0
and datediff(day,attendday,'''+@endDate+''')>0';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and a.userid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by a.userid';
print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @userid,@noRate;
WHILE @@fetch_status = 0 BEGIN

IF @noRate<85 BEGIN
SET @recordScores=(85-@noRate);
SET @ruleName='工作人员月上岗率为'+cast(@noRate as varchar(20))+'%,扣分'+cast(@recordScores as varchar(20));
END
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','103','出勤管理',@ruleName,'人员考核');
FETCH next FROM R_CRSR into @userid,@noRate;
END
close R_CRSR
deallocate R_CRSR
--4.由于个人原因，办件出现超时（红灯警示）的，每扣1分,每逾期1天加扣1分。
DECLARE @overDays int;
SET @recordScores=0;
SET @ruleName='';
SET @casecnt=0;
SET @sql='DECLARE R_CRSR CURSOR FOR select c.casereceiver,count(caseid) as casecnt,sum(datediff(day,casepromisedate,(case when b.busioperatedate is not null then b.busioperatedate else getdate() end))) as days
from t_casebaseinfo c
left join (select busicaseid,max(busioperatedate)busioperatedate from T_CASEBUSINESSINFO where busioperate=3 group by busicaseid) b on c.caseid=b.busicaseid

where datediff(day,'''+@startDate+''',casestartdate)>=0
and datediff(day,casestartdate,'''+@endDate+''')>0
and datediff(day,casepromisedate,(case when b.busioperatedate is not null then b.busioperatedate else getdate() end))>0 ';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and c.casereceiver='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by c.casereceiver ';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @userid,@casecnt,@overDays;
WHILE @@fetch_status = 0 BEGIN
SET @recordScores=@overDays+@casecnt;
SET @ruleName='办件出现超时,超时'+cast(@overDays as varchar(20))+'天,扣'+cast(@recordScores as varchar(20))+'分';
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','104','工作实绩',@ruleName,'人员考核');
FETCH next FROM R_CRSR into @userid,@casecnt,@overDays;
END
close R_CRSR
deallocate R_CRSR

--5.手工评议、网站评议、评议器评议结果汇总为窗口人员的群众满意评议：群众评议满意率达到95%以上得5分，低于95%的，每低一个百分点扣0.5分；不满意率高于5%的，每高一个百分点扣1分。窗口人员无评议票的，按各窗口人员平均分得分。
SET @recordScores=0;
SET @ruleName='';
SET @iTemp=0;
SET @sql='DECLARE R_CRSR CURSOR FOR SELECT u.userid,count(apprid) sumCnt,sum(case when apprlevel in(''非常满意'',''比较满意'',''一般'') then 1 else 0 end) as veryCnt,sum(case when apprlevel in(''不满意'') then 1 else 0 end)as noVeryCnt
FROM T_appraise a inner join t_user u on u.usercode=a.apprusercode where
datediff(day,'''+@startDate+''',apprdate)>=0
and datediff(day,apprdate,'''+@endDate+''')>0';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and u.userid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by u.userid';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @userid,@sumCnt,@veryCnt,@noVeryCnt;
WHILE @@fetch_status = 0 BEGIN
IF @sumCnt>0 BEGIN--有评议
SET @veryRate=@veryCnt/@sumCnt*100;
SET @noVeryRate=@noVeryCnt/@sumCnt*100;
IF @veryRate<95 BEGIN
SET @recordScores=(95-@veryRate)*0.5;
SET @ruleName='群众评议满意率为'+cast(@veryRate as varchar(20))+'%,扣'+cast((95-@veryRate)*0.5 as varchar(20))+'分;';
END

IF @noVeryRate>5 BEGIN
SET @recordScores=@recordScores+(@noVeryRate-5);
SET @ruleName=@ruleName+'不满意率为'+cast(@noVeryRate as varchar(20))+'%,扣'+cast((@noVeryRate-5) as varchar(20))+'分';
END
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','105','工作评议',@ruleName,'人员考核');
END ELSE BEGIN --没有评议，按各窗口人员平均分得分
DECLARE R_CRSR1 CURSOR FOR
SELECT apprusercode,count(apprid),sum(case when apprlevel in('非常满意','比较满意','一般') then 1 else 0 end),sum(case when apprlevel in('不满意') then 1 else 0 end)
FROM T_appraise where apprusercode is not null
and datediff(day,@startDate,apprdate)>=0
and datediff(day,apprdate,@endDate)>0
group by apprusercode;
OPEN R_CRSR1;
FETCH next FROM R_CRSR1 into @apprusercode,@sumCnt,@veryCnt,@noVeryCnt;
SET @iTemp=0;
WHILE @@fetch_status = 0 BEGIN
SET @iTemp=@iTemp+1;
SET @veryRate=@veryCnt/@sumCnt*100;
SET @noVeryRate=@noVeryCnt/@sumCnt*100;
IF @veryRate<95 BEGIN
SET @recordScores=@recordScores+(95-@veryRate)*0.5;
END

IF @noVeryRate>5 BEGIN
SET @recordScores=@recordScores+(@noVeryRate-5);
END
FETCH next FROM R_CRSR1 into @apprusercode,@sumCnt,@veryCnt,@noVeryCnt;
END
close R_CRSR1
deallocate R_CRSR1
--计算有评议人员的平均扣分
IF @iTemp>0 BEGIN
SET @recordScores=@recordScores/@iTemp;
SET @ruleName='此窗口人员无评议票，按各窗口人员评议平均分扣分，扣'+cast(@recordScores as varchar(20))+'分';
INSERT INTO #Temp(userid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@userid,-@recordScores,getdate(),'系统自动计算','105','工作评议',@ruleName,'人员考核');
END
END
FETCH next FROM R_CRSR into @userid,@sumCnt,@veryCnt,@noVeryCnt;
END
close R_CRSR
deallocate R_CRSR
END ELSE IF @itemKind=2 BEGIN
--1.因窗口原因出现超时(红灯)办结申请事项的,每件扣2分；
SET @recordScores=0;
SET @ruleName='';
SET @casecnt=0;
SET @sql='DECLARE R_CRSR CURSOR FOR select c.casedeptid,count(caseid) as casecnt
from t_casebaseinfo c
inner join (select busicaseid,max(busioperatedate)busioperatedate from T_CASEBUSINESSINFO where busioperate=3 group by busicaseid) b on c.caseid=b.busicaseid

where datediff(day,'''+@startDate+''',casestartdate)>=0
and datediff(day,casestartdate,'''+@endDate+''')>0
and datediff(day,casepromisedate,b.busioperatedate )>0 ';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and c.casedeptid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by c.casedeptid ';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @id,@casecnt;
WHILE @@fetch_status = 0 BEGIN
SET @recordScores=@casecnt*2;
SET @ruleName='办件出现超时办结,超时'+cast(@casecnt as varchar(20))+'件,扣'+cast(@recordScores as varchar(20))+'分';
INSERT INTO #Temp(deptid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@id,-@recordScores,getdate(),'系统自动计算','20103','办理结果',@ruleName,'窗口考核');
FETCH next FROM R_CRSR into @id,@casecnt;
END
close R_CRSR
deallocate R_CRSR
--2.即办件100%做到现场办结，每低1个百分点扣0.5分；承诺件、上报件100%按时办结，每低1个百分点扣0.5分；办件的准确率100%，每低一个百分点扣0.5分。
DECLARE @asRate decimal(5,2);
SET @recordScores=0;
SET @ruleName='';
SET @casecnt=0;
SET @sql='DECLARE R_CRSR CURSOR FOR select d.deptid,
isnull(Y.INTIME_OPERATE_CASE_NUM,0)/isnull(XX.RECIEVE_CASE_NUM,0)*100 AS asRate
from t_department d
left join
( SELECT B.casedeptid,sum(B.CASENUMBER) AS RECIEVE_CASE_NUM
FROM T_CASEBASEINFO AS B
WHERE datediff(day,'''+@startDate+''',casestartdate)>=0
and datediff(day,casestartdate,'''+@endDate+''')>0 and casetypeid in(151,132,133)
group by B.casedeptid
) AS XX on d.deptid=XX.casedeptid
left JOIN
( SELECT B.casedeptid ,sum(B.CASENUMBER) AS INTIME_OPERATE_CASE_NUM
FROM T_CASEBASEINFO AS B
inner join (select busicaseid,max(busioperatedate)busioperatedate from T_CASEBUSINESSINFO where busioperate in(3,5,6,7) group by busicaseid) BB on B.caseid=BB.busicaseid
WHERE B.CASESTATUSid IN (199,141,142,143)
AND datediff(day,BB.BUSIOPERATEDATE,B.CASEPROMISEDATE)>=0
AND datediff(day,'''+@startDate+''',B.casestartdate)>=0
and datediff(day,B.casestartdate,'''+@endDate+''')>0
and casetypeid in(151,132,133)
group by B.casedeptid
) AS Y ON Y.casedeptid = d.deptid
where d.enable=''启用'' and XX.RECIEVE_CASE_NUM<>0';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and d.deptid='+cast(@checkid as varchar(20));
END

--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @id,@asRate;
WHILE @@fetch_status = 0 BEGIN
IF @asRate<100 BEGIN
SET @recordScores=(100-@asRate)*0.5;
SET @ruleName='即办件、承诺件、上报件按时办结率为'+cast(@asRate as varchar(20))+'%,扣'+cast(@recordScores as varchar(20))+'分';
INSERT INTO #Temp(deptid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@id,-@recordScores,getdate(),'系统自动计算','20103','办理结果',@ruleName,'窗口考核');
END
FETCH next FROM R_CRSR into @id,@asRate;
END
close R_CRSR
deallocate R_CRSR

--3.本项分值30分按照窗口工作人员多少平均，即：个人窗口形象标准分=30÷n(人数)，窗口形象=个人形象的和。
--个人行为规范得分finalScores1(30)+个人出勤finalScores3(30)得分30/60=X/@sumScores =>x=30*@sumScores/60
DECLARE @sumScores decimal(10,2);
SET @recordScores=0;
SET @ruleName='';
SET @sql='DECLARE R_CRSR CURSOR FOR select deptid,sum(finalScores1+finalScores3)/count(userid)
from T_KH_CHECKUSERS where datediff(day,'''+@startDate+''',yearmonths)>=0
and datediff(day,yearmonths,'''+@endDate+''')>0 '
IF @checkid<>0 BEGIN
SET @sql=@sql+' and deptid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+'group by deptid';
--print @sql;
EXEC (@sql);
OPEN R_CRSR;
FETCH next FROM R_CRSR into @id,@sumScores;
WHILE @@fetch_status = 0 BEGIN
SET @recordScores=30-(30*@sumScores)/60;
SET @ruleName='窗口形象,扣'+cast(@recordScores as varchar(20))+'分';
if @recordScores!=0 begin
INSERT INTO #Temp(deptid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@id,-@recordScores,getdate(),'系统自动计算','204','窗口形象',@ruleName,'窗口考核');
end
FETCH next FROM R_CRSR into @id,@sumScores;
END
close R_CRSR
deallocate R_CRSR
--4.手工评议、网站评议、评议器评议结果汇总为窗口的群众满意评议：群众评议满意率低于95%的，每低一个百分点扣0.5分；不满意率高于5%的，每高一个百分点扣0.5分。窗口无评议票的，按各窗口平均分得分。
declare @apprdeptid int;
SET @recordScores=0;
SET @ruleName='';
SET @iTemp=0;
SET @sql='DECLARE R_CRSR CURSOR FOR SELECT a.apprdeptid,count(apprid) sumCnt,sum(case when apprlevel in(''非常满意'',''比较满意'',''一般'') then 1 else 0 end) as veryCnt,sum(case when apprlevel in(''不满意'') then 1 else 0 end)as noVeryCnt
FROM T_appraise a where
datediff(day,'''+@startDate+''',apprdate)>=0
and datediff(day,apprdate,'''+@endDate+''')>0 and apprusercode is null';
IF @checkid<>0 BEGIN
SET @sql=@sql+' and a.apprdeptid='+cast(@checkid as varchar(20));
END
SET @sql=@sql+' group by a.apprdeptid ';
--print @sql;
EXEC (@sql);

OPEN R_CRSR;
FETCH next FROM R_CRSR into @id,@sumCnt,@veryCnt,@noVeryCnt;
WHILE @@fetch_status = 0 BEGIN
IF @sumCnt>0 BEGIN--有评议
SET @veryRate=@veryCnt/@sumCnt*100;
SET @noVeryRate=@noVeryCnt/@sumCnt*100;
IF @veryRate<95 BEGIN
SET @recordScores=(95-@veryRate)*0.5;
SET @ruleName='群众评议满意率为'+cast(@veryRate as varchar(20))+'%,扣'+cast((95-@veryRate)*0.5 as varchar(20))+'分;';
END

IF @noVeryRate>5 BEGIN
SET @recordScores=@recordScores+(@noVeryRate-5);
SET @ruleName=@ruleName+'不满意率为'+cast(@noVeryRate as varchar(20))+'%,扣'+cast((@noVeryRate-5) as varchar(20))+'分';
END
INSERT INTO #Temp(deptid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@id,-@recordScores,getdate(),'系统自动计算','20501','群众评议',@ruleName,'窗口考核');
END ELSE BEGIN --没有评议，按各窗口人员平均分得分
DECLARE R_CRSR1 CURSOR FOR
SELECT apprdeptid,count(apprid),sum(case when apprlevel in('非常满意','比较满意','一般') then 1 else 0 end),sum(case when apprlevel in('不满意') then 1 else 0 end)
FROM T_appraise where apprusercode is null
and datediff(day,@startDate,apprdate)>=0
and datediff(day,apprdate,@endDate)>0
group by apprdeptid;
OPEN R_CRSR1;
FETCH next FROM R_CRSR1 into @apprdeptid,@sumCnt,@veryCnt,@noVeryCnt;
SET @iTemp=0;
WHILE @@fetch_status = 0 BEGIN
SET @iTemp=@iTemp+1;
SET @veryRate=@veryCnt/@sumCnt*100;
SET @noVeryRate=@noVeryCnt/@sumCnt*100;
IF @veryRate<95 BEGIN
SET @recordScores=@recordScores+(95-@veryRate)*0.5;
END

IF @noVeryRate>5 BEGIN
SET @recordScores=@recordScores+(@noVeryRate-5);
END
FETCH next FROM R_CRSR1 into @apprdeptid,@sumCnt,@veryCnt,@noVeryCnt;
END
close R_CRSR1
deallocate R_CRSR1
--计算有评议人员的平均扣分
IF @iTemp>0 BEGIN
SET @recordScores=@recordScores/@iTemp;
SET @ruleName='此窗口无评议票，按各窗口评议平均分扣分，扣'+cast(@recordScores as varchar(20))+'分';
INSERT INTO #Temp(deptid,recordScores,recordDate,recorder,itemcode,ItemName,recordName,itemKind)
VALUES(@id,-@recordScores,getdate(),'系统自动计算','20501','群众评议',@ruleName,'窗口考核');
END
END
FETCH next FROM R_CRSR into @id,@sumCnt,@veryCnt,@noVeryCnt;
END
close R_CRSR
deallocate R_CRSR

----to do here
END

IF @itemKind=1 BEGIN

IF @itemcode !='' BEGIN

select u.userid,d.deptid,d.deptname,u.username,t.recordScores,t.recordDate,t.recorder,t.itemcode,t.ItemName,t.recordName,t.itemKind
from #Temp t inner join t_user u on u.userid=t.userid inner join t_department d on d.deptid=u.deptid
where dbo.getlocate(ITEMCODE ,@itemcode)>0;
END ELSE BEGIN
select userid,itemcode,0 as itemscores,recordScores as finalScores from #Temp ;
END
END IF @itemKind=2 BEGIN
IF @itemcode !='' BEGIN
select d.deptid,d.deptname,t.recordScores,t.recordDate,t.recorder,t.itemcode,t.ItemName,t.recordName,t.itemKind
from #Temp t inner join t_department d on d.deptid=t.deptid
where dbo.getlocate(ITEMCODE ,@itemcode)>0;
END ELSE BEGIN
select deptid,itemcode,0 as itemscores,recordScores as finalScores from #Temp ;
END
END

drop table #Temp;
END