在存储过程中计算考核总分

ALTER PROCEDURE [dbo].[Zhkh_Sp_CheckCompute]
 @kCode char(10),--种类
 @kjYear int,--会计年度
 @period int--会计月度
AS
declare @type int--类型：0-部门，1-员工
declare @mCode char(10)--模块编码
declare @DeptCode char(10)--部门编码
declare @TitleCode char(10)--类别编码
declare @tType int--类别种类，1-绩效，2-党委，3-6S，4-无
declare @wNumber decimal(18,6)--权重
declare @number decimal(18,6)--分数
declare @TotalNumber decimal(18,6)--总分数
declare @TotalNumber1 decimal(18,6)--考核总分数
declare @TotalNumber2 decimal(18,6)--领导总分数
declare @lCode char(10)--领导对应操作员编码
declare @lCode1 char(10)--领导编码
declare @lScore char(4)--领导评分等级
declare @lScore1 decimal(18,6)--领导评分分数
--分数存储：0-各考核要素分数，1-最终得分，2-考核总分，3-领导主观评分
BEGIN
 --删除旧数据
 delete from Zhkh_BaseTotal where kCode=@kCode and kjYear=@kjYear and period=@period

 select @type=0
 declare DeptCode_Cursor cursor for
 select DeptID from Zhkh_Dept where dType=@kCode order by dID

 open DeptCode_Cursor
 fetch next from DeptCode_Cursor into @DeptCode
 while @@fetch_status=0
 begin
  --以下计算总考核分数
  select @TotalNumber1=0
  declare weigh_cursor cursor for
  select mCode,wNumber from Zhkh_v_Weigh where kCode=@kCode order by mCode

  open weigh_cursor
  fetch next from weigh_cursor into @mCode,@wNumber
  while @@fetch_status=0
  begin
   select @number=-1
   select @TitleCode=titleCode,@tType=tType from Zhkh_Title where kCode=@kCode and mCode=@mCode and kType=@type
   if (@tType=1)
    select @number=ObjectTotal from Kh_BaseTotal where TotalType=4 and TitleCode=@TitleCode and kjYear=@kjYear and period=@period and EmpID=@DeptCode
   else if (@tType=2)
    select @number=ObjectTotal from Dwkh_BaseTotal where TotalType=4 and TitleCode=@TitleCode and kjYear=@kjYear and period=@period and EmpID=@DeptCode
   else if (@tType=3)
    select @number=-1
   else if (@tType=4)
    select @number=-1
   --若大于100或者小于0,则退出本次循环
   if(@number>100 or @number<0)
    begin
     select @TotalNumber1=@TotalNumber1+0
    end
   else
    begin
     select @TotalNumber1=@TotalNumber1+@number*@wNumber
     --将各模块分数写入表中，阶段0
     insert into Zhkh_BaseTotal(kjYear,period,Score,kCode,mCode,EmpID,bType) values(@kjYear,@period,@number,@kCode,@mCode,@DeptCode,0)
    end
   fetch next from weigh_cursor into @mCode,@wNumber
  end
  close weigh_cursor
  deallocate weigh_cursor
  --将总分数写入表中,步骤2
  insert into Zhkh_BaseTotal(kjYear,period,Score,kCode,EmpId,bType) values(@kjYear,@period,@TotalNumber1,@kCode,@DeptCode,2)
  --以上计算总考核分数

  --以下计算领导主观印象分数
  select @TotalNumber2=0
  --查找公司领导
  declare ZhkhLeader_Cursor cursor for
  select Czybm,lCode from Zhkh_leader a inner join Gy_Czygl b on a.EmpID=b.EmpNo

  open ZhkhLeader_Cursor
  fetch next from ZhkhLeader_Cursor into @lCode,@lCode1
  while @@fetch_status=0
  begin
   --获取考核分数
   select @lScore=count(1) from Zhkh_Score where Deptcode=@deptcode and lCode=@lCode and kjYear=@kjYear and period=@period
   if(@lScore>0)
   begin
    select @lScore=Score from Zhkh_Score where Deptcode=@deptcode and lCode=@lCode
    select @lScore1=GauListMark from Kh_GauList where GauListCode=@lScore
   end
   else
   begin
    select top 1 @lScore1=GauListMark from Kh_GauList where CheckCode='A01' order by GauListCode
   end
   select @wNumber=lWeigh from Zhkh_lWeigh where lCode=@lCode1 and DeptCode=@deptCode
   select @TotalNumber2=@TotalNumber2+@lScore1*@wNumber

   fetch next from ZhkhLeader_Cursor into @lCode,@lCode1
  end
  close ZhkhLeader_Cursor
  deallocate ZhkhLeader_Cursor
  --将总分数写入表中,步骤3
  insert into Zhkh_BaseTotal(kjYear,period,Score,kCode,EmpID,bType) values(@kjYear,@period,@TotalNumber2,@kCode,@DeptCode,3)
  --以上计算领导主观印象分数

  --以下计算总分数
  select @TotalNumber=0
  select @wNumber=wNumber from Zhkh_ColWeigh where wID=1--基本考核权重
  select @TotalNumber=@TotalNumber1*@wNumber
  select @wNumber=wNumber from Zhkh_ColWeigh where wID=2--领导主观印象考核权重
  select @TotalNumber=@TotalNumber+@TotalNumber2*@wNumber
  --将总分数写入表中,步骤1
  insert into Zhkh_BaseTotal(kjYear,period,Score,kCode,EmpID,bType) values(@kjYear,@period,@TotalNumber,@kCode,@DeptCode,1)
  --以上计算总分数

  fetch next from DeptCode_Cursor into @deptCode
 end
 close DeptCode_Cursor
 deallocate DeptCode_Cursor
END