sql
SET SCHEMA = HR;
CREATE PROCEDURE HR.SPGETCLOTHES (
IN IEMPCODE VARCHAR(20),
IN IGIVETYPE INTEGER,
IN ICLOTHESTYPE INTEGER,
OUT FLAG VARCHAR(20),
OUT OUTERRNO INTEGER,
OUT OUTERRMSG VARCHAR(255) )
SPECIFIC SQL090321125518400
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
----------------------------------------------------------------------------------
-- Createor : Fanruyi
-- CreateDate : 2009.03.19
-- Description : 脚本
--
-- Last Modified: 2009.03.19
-- Version : 1.0.0.10
----------------------------------------------------------------------------------
begin
-- 异常处理相关变量声明区域
declare SQLCODE integer default 0;
declare SQLSTATE char(5) default '00000';
declare at_end integer default 0;
declare vSQLSTATE char(5); -- SQLSTATE局部变量
declare vZBreakPoint varchar(255); -- 程序总断点
declare vBreakPoint varchar(255); -- 程序子断点
declare vStartWork smallint; -- 内部事务 1 启动 0 没有启动
declare vErrMsg varchar(255) default ''; -- 错误信息
declare V_NOTFOUND smallint default 0;
declare vMaxStatus int;
declare vNextStatus int;
declare vAuditor varchar(16);
declare vTurnBack int default 0; --单据被退回标志
declare vReturnTxt varchar(300);
-- 自定义变量声明区域
declare vRowCount int default 0; -- 更新行数
declare vSheetType varchar(8) default '22550100'; -- 单据类型
declare vreturstr varchar(300);
-- 自定义变量声明区域
declare vclothesdate date; --发夏
declare vdateclothes date; --发冬
declare vcurrentTime date; --当前时间
declare vhiredate date; --入职时间
declare vtryoutdate date; --转正时间（不用）
declare vpromotebegindate date; --声明晋升生效起始时间
declare vTypeid integer; --声明入职状态
declare flag1 integer default 0; --初始化传出参数 0=不匹配;1=夏;2=冬;3=夏冬 对应新入职
declare flag2 integer default 0; --初始化传出参数 0=不匹配;1=夏;2=冬;3=夏冬 对应二年换新
declare flag3 integer default 0; --初始化传出参数 0=不匹配;1=夏;2=冬;3=夏冬 对应晋升
declare cf integer default 0; --变参标记 储备干部1
declare zz integer default 0; --变参标记 转正3
declare xszz integer default 0; --变参标记 学生转正3
--6个标记 分别针对三中类型是否已经申请
declare flag4 integer default 0;
declare flag5 integer default 0;
declare flag6 integer default 0;
declare flag7 integer default 0;
declare flag8 integer default 0;
declare flag9 integer default 0;
declare first integer ;--只是员工编号传进来 为 1 否则为0
-- 声明游标区域
-- 异常处理
declare exit handler for SQLEXCEPTION,SQLWARNING
begin
get diagnostics exception 1 vErrMsg = MESSAGE_TEXT;
set vSQLSTATE = SQLSTATE; --保存 SQLSTATE
set outErrNO = -1;
set outErrMsg = coalesce(vErrMsg,'') || ' 断点= ' || coalesce(vZBreakPoint,'') || coalesce(vBreakPoint,'') || 'SQLSTATE=' || coalesce(vSQLSTATE,'');
end;
declare continue handler for NOT FOUND
begin
--SET at_end = 1;
--Set outErrNO = 0;
Set V_NOTFOUND = 1;
end;
-- 程序体开始
set outErrNO = 0;
set vZBreakPoint = vSheetType;
set vBreakPoint = '22550100_000';
--得到最后一步审核流程
set vZBreakPoint = '22550100_010';
--初始化参数1
set flag ='00';--返回参数 第1位对应发放类型 第2为对应工衣类型（冬夏）
--初始化参数2 cf
set vZBreakPoint = '22550100_020';
if exists ( SELECT 1 FROM EMPLOYEE A INNER JOIN EMPSALARY B
ON A.EMPID = B.EMPID WHERE A.TYPEID IN (1,2,3) AND A.DEGREEID IN (5,6) AND A.STATUS IN (1,5,9)
AND B.SALARYTYPE = 1 AND A.EMPCODE = IEMPCODE ) then set cf = 1;--储备干部(新来和新来未领) 红蓝黄衣服
end if;
--初始化参数3 xszz 晋升的储备干部
set vZBreakPoint = '22550100_030';
if exists ( SELECT 1 FROM EMPLOYEE A INNER JOIN EMPSALARY B
ON A.EMPID = B.EMPID WHERE A.TYPEID IN (1,2) AND A.DEGREEID IN (5,6) AND A.STATUS IN (1,5,9)
AND B.SALARYTYPE = 1 AND A.EMPCODE = IEMPCODE ) then set xszz = 1;--红和蓝 (学生转正)
end if;
--初始化参数4 转正标记ZZ 和 转正时间 vpromotebegindate
set vZBreakPoint = '22550100_040';
if exists(select 1 from EMPPROMOTETRANSHEETITEM A
LEFT JOIN EMPPROMOTETRANSHEET B ON A.SHEETID=B.SHEETID
LEFT JOIN EMPLOYEE C ON C.EMPID=A.EMPID
where A.DUTYID <> A.newdutyid and B.STATUS=100 AND C.EMPCODE = IEMPCODE)
then set zz = 1;--晋升标记
--下面把时间传进入变量vpromotebegindate中
select date(B.AUDITDATE) into vpromotebegindate from EMPPROMOTETRANSHEETITEM A
LEFT JOIN EMPPROMOTETRANSHEET B ON A.SHEETID=B.SHEETID
LEFT JOIN EMPLOYEE C ON C.EMPID=A.EMPID
where A.DUTYID <> A.newdutyid AND B.STATUS=100 AND C.EMPCODE = IEMPCODE
order by B.AUDITDATE desc fetch first 1 row only; --晋升生效时间 只取一行确保这句不会引发异常
end if;
--初始化参数5 传入该员工状态,入职时间,发夏衣时间,发冬衣时间,
set vZBreakPoint = '22550100_050';
select tryoutdate,hiredate,typeid,date(clothesdate) as clothesdate,date(dateclothes) as dateclothes into vtryoutdate,vhiredate,vTypeid,vclothesdate,vdateclothes from employee where empcode =iempcode;
--初始化参数6 当前时间处理
Select Current date into vcurrentTime From Sysibm.Sysdummy1;
--初始化参数7 是够只传入员工编号
if igivetype = 0 then set first = 1 ; else set first = 0 ; end if ;
--第三部分 --判断是否申情
if first = 1 or igivetype =3 then ---我来自
for ee as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 3 and clothestype = 1 and typeid = vtypeid order by clothesdate desc fetch first 1 row only
do
if ee.clothesdate is not null then set flag8 =1;end if;
end for ;
for ff as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 3 and clothestype = 2 and typeid = vtypeid order by clothesdate desc fetch first 1 row only
do
if ff.clothesdate is not null then set flag9 =1;end if;
end for;
--判断是否发放
set vZBreakPoint = '22550100_070';
if xszz = 1 then --出干转正
if vTypeid = 2 then --蓝衣
if vcurrentTime-3 month > vhiredate then
if (vclothesDate < vhiredate + 3 month and flag8<> 1) then set flag3 = 1; else set flag3 =0; end if;
if (vDateclothes < vhiredate + 3 month and flag9<> 1) then set flag3 = flag3+2; else set flag3 =flag3+0; end if;
end if;
elseif vTypeid = 1 then --红衣
if vcurrentTime-1 year > vhiredate then
if (vclothesDate < vhiredate + 1 year and flag8<> 1) then set flag3 = 1; else set flag3 =0; end if;
if (vDateclothes < vhiredate + 1 year and flag9<> 1 ) then set flag3 = flag3+2; else set flag3 =flag3+0; end if;
end if;
end if;
end if;
-----over 3-1-------
if zz = 1 then --正规转正
if vclothesDate is null and flag8<> 1 then set flag3=1;
else
if (vclothesDate < vpromotebegindate and flag8<> 1 ) then set flag3 = 1; else set flag3 =0; end if;
end if;
if vDateclothes is null and flag9<> 1 then set flag3=flag3+2;
else
if (vDateclothes < vpromotebegindate and flag9<> 1) then set flag3 = flag3+2; else set flag3 =flag3+0; end if;
end if;
end if;
end if; -- end 我来自
-----over 3-2---
--传出结果
if first = 1 then
if flag3 = 0 then set flag = '00';
elseif flag3 = 1 then set flag ='31';
elseif flag3 = 2 then set flag ='32';
elseif flag3 = 3 then set flag ='33';
end if;
if flag <> '00' then return ; end if;
end if;
if igivetype = 3 then
if flag3 = 0 then set flag ='00';
elseif flag3 = 1 then if iclothestype = 1 then set flag ='31';elseif iclothestype = 2 then set flag ='00'; end if;
elseif flag3 = 2 then if iclothestype = 1 then set flag ='00';elseif iclothestype = 2 then set flag ='32'; end if;
elseif flag3 = 3 then if iclothestype = 1 then set flag ='31';elseif iclothestype = 2 then set flag ='32'; end if;
end if;
end if;
--第三部分 -结束
--第二部分 --判断是否申情
if first = 1 or igivetype =2 then --我来自中
for cc as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 2 and clothestype = 1 order by clothesdate fetch first 1 row only
do
if cc.clothesdate is not null and current date - 2 year < date(cc.clothesdate) then set flag6 = 1;end if;
end for ;
for dd as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 2 and clothestype = 2 order by clothesdate fetch first 1 row only
do
if dd.clothesdate is not null and current date - 2 year < date(dd.clothesdate) then set flag7 = 1;end if;
end for;
--判断是否发放
set vZBreakPoint = '22550100_070';
if(vclothesDate is null) then
if (vcurrentTime-2 year > vhiredate and flag6 <> 1) then set flag2 =1; else set flag2 =0; end if;
elseif (vclothesDate is not null ) then
if (vcurrentTime-2 year > vclothesDate and flag6 <> 1) then set flag2 =1; else set flag2 =0; end if;
end if;
if(vDateclothes is null) then
if (vcurrentTime-2 year > vhiredate and flag7 <> 1) then set flag2 =flag2+2; else set flag2=flag2+0 ;end if;
elseif (vDateclothes is not null) then
if (vcurrentTime-2 year > vDateclothes and flag7 <> 1) then set flag2 =flag2+2; else set flag2=flag2+0;end if;
end if;
end if; --end 我来自中
--传出结果
if first = 1 then
if flag2 = 0 then set flag = '00';
elseif flag2 = 1 then set flag ='21';
elseif flag2 = 2 then set flag ='22';
elseif flag2 = 3 then set flag ='23';
end if;
if flag <> '00' then return ; end if;
end if;
if igivetype = 2 then
if flag2 = 0 then set flag ='00';
elseif flag2 = 1 then if iclothestype = 1 then set flag ='21';elseif iclothestype = 2 then set flag ='00'; end if;
elseif flag2 = 2 then if iclothestype = 1 then set flag ='00';elseif iclothestype = 2 then set flag ='22'; end if;
elseif flag2 = 3 then if iclothestype = 1 then set flag ='21';elseif iclothestype = 2 then set flag ='22'; end if;
end if;
end if;
--第二部分结束
--第一部分 --判断是否申情
set vZBreakPoint = '22550100_062';
if first = 1 or igivetype= 1 then --我来自中国
for aa as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 1 and clothestype = 1 fetch first 1 row only
do
if aa.clothesdate is not null then set flag4 = 1;end if;
end for ;
for bb as
select * from clothesoutsheetitem where empcode=IEMPCODE and givetype = 1 and clothestype = 2 fetch first 1 row only
do
if bb.clothesdate is not null then set flag5 = 1;end if;
end for;
--判断是否发放
set vZBreakPoint = '22550100_061';
if cf=1 then --储干
if (vTypeid = 3 and vcurrentTime- 1 month >vhiredate ) then --黄衣
if (vclothesDate is null and flag4 <> 1) then set flag1 = 1; else set flag1 = 0 ;end if;
if (vDateclothes is null and flag5 <> 1) then set flag1 = flag1+2 ;else set flag1 = flag1+0 ;end if;
elseif (vTypeid <> 3 and vcurrentTime-3 month >vhiredate ) then --发 红 蓝衣未领黄衣
if (vclothesDate is null and flag4 <> 1) then set flag1 = 1 ;else set flag1 = 0 ;end if;
if (vDateclothes is null and flag5 <> 1) then set flag1 = flag1+2 ;else set flag1 = flag1+0 ;end if;
end if;
end if;
--非储干 时间为空 当前 时间-1> 入职时间
if (vTypeid = 3 and vcurrentTime - 1 month > vhiredate) then --黄衣服一月发
if (vclothesDate is null and flag4 <> 1) then set flag1 = 1 ;else set flag1 = 0 ;end if;
if (vDateclothes is null and flag5 <> 1) then set flag1 = flag1+2 ;else set flag1 = flag1+0 ;end if;
elseif (vTypeid <> 3 and vcurrentTime - 3 month > vhiredate) then -- 红蓝三月发
if (vclothesDate is null and flag4 <> 1) then set flag1 = 1 ;else set flag1 = 0 ;end if;
if (vDateclothes is null and flag5 <> 1) then set flag1 = flag1+2 ;else set flag1 = flag1+0 ;end if;
end if;
end if; ----我来自中国
--传出结果
if first = 1 then
if flag1 = 0 then set flag = '00';
elseif flag1 = 1 then set flag ='11';
elseif flag1 = 2 then set flag ='12';
elseif flag1 = 3 then set flag ='13';
end if;
if flag <> '00' then return ; end if;
end if ;
if igivetype = 1 then
if flag1 = 0 then set flag ='00';
elseif flag1 = 1 then if iclothestype = 1 then set flag ='11';elseif iclothestype = 2 then set flag ='00'; end if;
elseif flag1 = 2 then if iclothestype = 1 then set flag ='00';elseif iclothestype = 2 then set flag ='12'; end if;
elseif flag1 = 3 then if iclothestype = 1 then set flag ='11';elseif iclothestype = 2 then set flag ='12'; end if;
end if;
end if;
--第一部分结束
END;
GRANT EXECUTE ON PROCEDURE HR.SPGETCLOTHES( VARCHAR(20), INTEGER, INTEGER, VARCHAR(20), INTEGER, VARCHAR(255) ) TO USER DB2INST1;
GRANT EXECUTE ON PROCEDURE HR.SPGETCLOTHES( VARCHAR(20), INTEGER, INTEGER, VARCHAR(20), INTEGER, VARCHAR(255) ) TO GROUP DBUSER;
