- 浏览: 1884716 次
- 性别:
- 来自: 合肥
文章分类
- 全部博客 (514)
- OPEN (41)
- WARN (33)
- EXPER (16)
- RESOURCE (7)
- TOOL (4)
- DWR (10)
- Struts1.x (10)
- Ibtais (18)
- MyEclipse (30)
- Sql Server (64)
- Tomcat (7)
- APACHE (4)
- JSP (18)
- SERVLET (6)
- ENGLISH (0)
- ECSide (8)
- JasperReports (7)
- JAVA (24)
- JS (42)
- XML (26)
- CVS (8)
- Mind (1)
- JQUERY (2)
- IBATIS (6)
- PROJECT (0)
- STRUTS2 (0)
- PROXOOL (0)
- SPRING (4)
- Hibernate (0)
- SSI (0)
- JBPM (11)
- FLEX (3)
- JSON (2)
- GWT (1)
- jeecms v3 (1)
- Flash (2)
- DATA (1)
- ORACLE (3)
- 查询oracle 中逗号分隔字符串中所有值 (1)
最新评论
-
小小西芹菜:
GoEasy web三步轻松实现web实时推送1. 引入goe ...
服务器推送技术 java -
kg_1997:
这个方法太棒了,可以不用to_date函数,实在是棒!!!
java/oracle日期处理 -
wodesunday:
:idea:
SQL的分段统计查询语句 -
wodesunday:
引用
SQL的分段统计查询语句 -
BlueSkator:
讲的有点浅,没有深入进去
tomcat需要的重新发布和重启服务器的几种情况
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.工作人员月上岗率不低于85%,每低于1个百分点扣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
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.工作人员月上岗率不低于85%,每低于1个百分点扣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
发表评论
-
Apache FileUpload组件
2013-11-22 15:05 5453Apache FileUpload组件 在最初的 htt ... -
各种数据库对应的jar包、驱动类名和URL格式
2012-11-20 16:38 1522各种数据库对应的jar包、驱动类名和URL格式 2011 ... -
Java Web Start
2011-09-14 10:45 1355一.首先了解一下什么是java web start 1JNL ... -
The template at a glance一目了然的模板
2011-08-05 09:06 1503The template at a glance一目了然的模板 ... -
FreeMarker
2011-08-05 09:04 1132一目了然的数据模型 As you have seen, th ... -
jeecms v3
2011-07-04 15:28 1575好久没有逛jeecms的官方论坛了,今天去看了下,jeecms ... -
gwt整合ibatis
2011-07-02 09:42 2089最近整合gwt整合ibatis,常见错误 控制台报错: 一.N ... -
GWT RPC原理浅析(二)
2011-06-17 16:14 2859前一篇介绍了RPC大体的流程,核心方法是RemoteServi ... -
GWT RPC原理浅析
2011-06-17 15:26 5337GWT中前后台交互有多种方式,包括JSON,XML,RPC 其 ... -
五种开源协议
2011-04-25 14:54 1120五种开源协议(BSD,Apache,G ... -
JAVA代码调用客户端摄像头 初步探讨
2011-02-21 16:51 6764首先到sun下载最新的jmf,然后安装。 然后,说一下 ... -
tomcat部署jbpm项目 loader constraint violation
2011-02-11 14:22 1806当tomcat部署jbpm项目 时,启动项目控制台出错 ja ... -
E3.Tree参考手册
2010-12-10 09:52 3586E3.Tree参考手册 (v1.0) 目录 简介 2 系统 ... -
e3.tree 1.5 发布,很好,很强大,有截图[转]
2010-12-07 14:14 1145E3.Tree是E3平台下一个用于构造树型UI(menu,tr ... -
在Action中获取ServletContext实例
2010-11-25 13:43 37741:在Action中获取servletContext的时候可以 ... -
Bean named 'sqlMapClient' must be of type [org.springframework.orm.ibatis.SqlMap
2010-11-22 09:57 4952在整合spring2.5和ibatis2,出现了ibatis的 ... -
spring,ibatis的笔记
2010-11-22 08:51 1050在上文中,我们把iface ... -
spring,ibatis的笔记
2010-11-22 08:51 978在上文中,我们把iface ... -
后台错误处理
2010-11-17 15:10 1332后台处理 if (!isTokenValid(request) ... -
struts1 和Spring整合jar包
2010-11-17 11:33 5020struts1 和Spring整合 struts1 和Spri ...
相关推荐
3. 数据库设计:DBA 根据架构分析师的设计,实现数据库的设计,并写出相应的存储过程。 4. 程序设计:程序员根据架构分析师的设计,写程序设计书,实现各个类和业务逻辑操作。 5. 测试:测试员编写测试用例,写...
7. **框架搭建与版本控制**:项目组长和核心成员一起搭建项目的基本框架,并将其提交到远程仓库的master分支,以便团队成员进行分支拉取和开发。 8. **开发与测试**:根据需求文档进行编码和单元测试,确保每个模块...
- **项目组人员**:包括项目组长、项目经理、设计师、项目助理和执行人员,他们各自承担不同的职责。 - **第三方施工队伍**:在某些项目中,可能需要外部施工团队,需要提前确定并进行沟通,以便于后期合作。 - *...
- 责任:项目组长,负责需求确认、数据库设计、模块划分、工作分配、核心编码及后期维护。 2. **短信发送软件和号码拨测软件** - 开发时间:2010年3月至2010年8月 - 技术栈:J2EE,MyEclipse6.6,CVS,SWT ...
按照《计算机软件质量保证计划规范GB/T12504-1990》,项目开发组长在评审过程中的角色有一定的限制。 - (10) C. 可以作为评审组的成员,但不能担任评审组的组长或副组长 说明:项目开发组长虽然可以参与评审,...
- **项目组表(pro_group)**: 存储项目组的信息,包括项目组的唯一标识符、项目组组长等。 - **项目表(project)**: 包含项目的名称、描述、所属项目组编号和备注等字段。 - **权限表(authority)**: 记录不同角色的...
项目成员的任务根据工作内容和计划分配,由项目组长全面管理团队。 **项目质量保证** - 遵循软件工程和信息系统建设的流程、标准和规范。 - 制定质量保证计划,规范开发过程。 - 设计技术文档规范和开发活动准则,...
火工组长岗位安全职责在IT行业中虽然...这些职责反映了火工组长在危险品管理中的核心角色,他们的工作直接影响到作业人员的安全和项目的顺利进行。通过严谨的管理制度,火工组长可以有效降低风险,保障工程的正常运行。
《图书馆项目管理计划书》是针对图书馆管理系统软件开发的一个详细规划文档,旨在规范和指导项目的实施过程。项目涉及的主要知识点如下: 1. **项目背景**: - 项目任务提出者通常为图书馆或相关教育机构,希望...
- **评审组成员资格**:项目开发组长或其代表可以成为评审组的成员,但不能担任评审组的组长或副组长,以避免利益冲突。 **10. 软件开发过程活动** - **过程实施**:根据《软件生存周期过程GB/T8566-2001》,开发...
各成员按照角色分配承担不同任务,如组长郑英杰负责项目管理,需求分析、概要设计、详细设计分别由不同成员主导,同时所有成员参与编码实现和测试工作。 3.2 项目进度: 项目进度以图表形式呈现,确保各阶段工作...
1. **项目管理**:项目管理是整个开发过程的核心,涉及需求分析、任务分配、时间规划和进度控制。在本项目中,团队采用了明确的角色分工,如组长负责架构分析、数据库设计等,组员则负责需求分析、UI设计等。通过...
- **主要参加人员**:明确了项目组的核心成员及其角色,赵可欣作为组长负责整体协调,其他成员负责各自的任务。 - **产品**:项目将产出一套完整的多媒体分享网站系统,包括: - **程序**:实际运行的网站应用,...
本项目基于C#语言,属WinForm应用程序开发,该系统可实现对超市或仓储公司的以及对管理员和用户的现代化管理。 责任描述:担任项目小组组长对项目的...运用到多表连接查询,分页导航,图片浏览,存储过程模糊搜索等。
- **项目组长/主程序员**: 负责项目整体规划、设计、编码以及团队协调。 - **项目副组长/软件测试员/文档维护员**: 跟踪项目进度、文档管理和测试工作。 - **Android端开发负责人**: 主导移动客户端的开发和技术...
其中,基础设施项目涵盖网络系统、安全系统、存储与备份系统等。 2. **项目分类**: - 自主开发项目:以企业为主导,拥有自主产权的开发项目,如独立开发、联合开发、委托开发。 - 引进试点项目:引入成熟技术、...
项目小组的建立是项目启动的第一步,由责任心强的学生担任组长,带领团队遵循软件开发的规范进行。团队成员需要共同参与需求分析,利用UML进行系统设计,如用例图、类图、顺序图、协作图、活动图和状态图的绘制,...
在摇奖机项目中,C#用于实现摇奖逻辑、用户交互以及可能的数据存储等。 2. **面向对象编程**:C#是面向对象的语言,项目可能涉及类的设计,如创建“摇奖机”类、“奖项”类等,通过封装、继承和多态等原则来组织...