`

项目组长的存储过程

    博客分类:
  • OPEN
阅读更多
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




























分享到:
评论

相关推荐

    一个软件项目团队的分工.pdf

    3. 数据库设计:DBA 根据架构分析师的设计,实现数据库的设计,并写出相应的存储过程。 4. 程序设计:程序员根据架构分析师的设计,写程序设计书,实现各个类和业务逻辑操作。 5. 测试:测试员编写测试用例,写...

    JAVA真实包装项目经验-物流项目面试

    7. **框架搭建与版本控制**:项目组长和核心成员一起搭建项目的基本框架,并将其提交到远程仓库的master分支,以便团队成员进行分支拉取和开发。 8. **开发与测试**:根据需求文档进行编码和单元测试,确保每个模块...

    集成项目施工标准和方案

    - **项目组人员**:包括项目组长、项目经理、设计师、项目助理和执行人员,他们各自承担不同的职责。 - **第三方施工队伍**:在某些项目中,可能需要外部施工团队,需要提前确定并进行沟通,以便于后期合作。 - *...

    java项目经验 大概有20个

    - 责任:项目组长,负责需求确认、数据库设计、模块划分、工作分配、核心编码及后期维护。 2. **短信发送软件和号码拨测软件** - 开发时间:2010年3月至2010年8月 - 技术栈:J2EE,MyEclipse6.6,CVS,SWT ...

    2008年上半年信息系统项目管理师上午试题

    按照《计算机软件质量保证计划规范GB/T12504-1990》,项目开发组长在评审过程中的角色有一定的限制。 - (10) C. 可以作为评审组的成员,但不能担任评审组的组长或副组长 说明:项目开发组长虽然可以参与评审,...

    Bug管理系统 项目开发总结报告

    - **项目组表(pro_group)**: 存储项目组的信息,包括项目组的唯一标识符、项目组组长等。 - **项目表(project)**: 包含项目的名称、描述、所属项目组编号和备注等字段。 - **权限表(authority)**: 记录不同角色的...

    项目实施方案.docx

    项目成员的任务根据工作内容和计划分配,由项目组长全面管理团队。 **项目质量保证** - 遵循软件工程和信息系统建设的流程、标准和规范。 - 制定质量保证计划,规范开发过程。 - 设计技术文档规范和开发活动准则,...

    火工组长岗位安全职责.doc

    火工组长岗位安全职责在IT行业中虽然...这些职责反映了火工组长在危险品管理中的核心角色,他们的工作直接影响到作业人员的安全和项目的顺利进行。通过严谨的管理制度,火工组长可以有效降低风险,保障工程的正常运行。

    图书馆项目管理计划书.pdf

    《图书馆项目管理计划书》是针对图书馆管理系统软件开发的一个详细规划文档,旨在规范和指导项目的实施过程。项目涉及的主要知识点如下: 1. **项目背景**: - 项目任务提出者通常为图书馆或相关教育机构,希望...

    信息系统项目管理师历年真题试题及解析(2008-2011全

    - **评审组成员资格**:项目开发组长或其代表可以成为评审组的成员,但不能担任评审组的组长或副组长,以避免利益冲突。 **10. 软件开发过程活动** - **过程实施**:根据《软件生存周期过程GB/T8566-2001》,开发...

    项目开发计划书.docx

    各成员按照角色分配承担不同任务,如组长郑英杰负责项目管理,需求分析、概要设计、详细设计分别由不同成员主导,同时所有成员参与编码实现和测试工作。 3.2 项目进度: 项目进度以图表形式呈现,确保各阶段工作...

    8_网上人才招聘项目进度报告和总结1

    1. **项目管理**:项目管理是整个开发过程的核心,涉及需求分析、任务分配、时间规划和进度控制。在本项目中,团队采用了明确的角色分工,如组长负责架构分析、数据库设计等,组员则负责需求分析、UI设计等。通过...

    (1)项目开发计划.pdf

    - **主要参加人员**:明确了项目组的核心成员及其角色,赵可欣作为组长负责整体协调,其他成员负责各自的任务。 - **产品**:项目将产出一套完整的多媒体分享网站系统,包括: - **程序**:实际运行的网站应用,...

    库存管路系统之小打小闹

    本项目基于C#语言,属WinForm应用程序开发,该系统可实现对超市或仓储公司的以及对管理员和用户的现代化管理。 责任描述:担任项目小组组长对项目的...运用到多表连接查询,分页导航,图片浏览,存储过程模糊搜索等。

    智慧社区项目管理计划书(完整版).docx

    - **项目组长/主程序员**: 负责项目整体规划、设计、编码以及团队协调。 - **项目副组长/软件测试员/文档维护员**: 跟踪项目进度、文档管理和测试工作。 - **Android端开发负责人**: 主导移动客户端的开发和技术...

    某大型国企信息化项目验收管理办法.docx

    其中,基础设施项目涵盖网络系统、安全系统、存储与备份系统等。 2. **项目分类**: - 自主开发项目:以企业为主导,拥有自主产权的开发项目,如独立开发、联合开发、委托开发。 - 引进试点项目:引入成熟技术、...

    项目开发管理UML课设实训报告-网络教学系统

    项目小组的建立是项目启动的第一步,由责任心强的学生担任组长,带领团队遵循软件开发的规范进行。团队成员需要共同参与需求分析,利用UML进行系统设计,如用例图、类图、顺序图、协作图、活动图和状态图的绘制,...

    北大青鸟阶段项目(摇奖机2008年11月制作)

    在摇奖机项目中,C#用于实现摇奖逻辑、用户交互以及可能的数据存储等。 2. **面向对象编程**:C#是面向对象的语言,项目可能涉及类的设计,如创建“摇奖机”类、“奖项”类等,通过封装、继承和多态等原则来组织...

Global site tag (gtag.js) - Google Analytics