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