create or replace procedure StpTask(
iBatid in out number ,
iOrgid in outnumber
)
is
iboss number;
iFather number;
begin
delete From Evx ;
declare
v_Gidevob.gid%type ;
v_Bnameevbat.bname%type;
v_GNameevob.gname%type ;
v_Unit evob.unit%type;
v_Attr evob.attr%type;
v_Rank evob.rank%type;
v_Sn evob.sn%type
;
v_Note evob.note%type;
cursor AddOb is
select a.gid,a.gname,a.unit,a.attr,a.sn,a.rank,a.note,b.bname from evob a,evbat b where a.batid=ibatid and a.batid=b.batid(+);
begin
open AddOb;
loop
fetch AddOb into v_Gid,v_GName,v_Unit,v_Attr,v_Sn,v_Rank,v_Note,v_Bname;
exit when AddOb%notfound;
insert into EvJob(batid,gid,bname,gname,unit,status,attr,sn,backfrom,note)
values(ibatid,v_Gid,v_Bname,v_GName,v_Unit,1,v_attr,v_sn,0,v_Note);
end loop;
close AddOb;
end;
declare
v_Gidevavg.gid%type ;
v_EGName evavg.egname%type;
v_Egid evavg.egid%type;
v_Item evavg.item%type;
v_Sn evavg.sn%type
;
v_Note evavg.note%type;
v_stanumber;
cursor AdduOb is
select substr(a.userid,5,6),d.gname,a.avgid,b.sn,c.item,a.avtype from evhook a ,evjob b,evitem c,ev_usergroup d
where substr(a.userid,5,6)=b.gid and b.batid=ibatid
and b.batid=c.batid and b.sn=c.sn and a.avgid=d.orgid(+) and c.egid=1;
begin
open AdduOb;
loop
fetch AdduOb into v_Gid,v_eGName,v_Egid,v_Sn,v_Item,v_sta;
exit when AdduOb%notfound;
if substr(v_sn,2,1)=1 then
if ((v_item mod 2)=1 and v_sta=1) or ((v_item mod 2)=0 and v_sta=0) then
insert into EvAvg(batid,gid,sn,item,Egid,Egname,score,status,Note)
values(ibatid,v_Gid,v_sn,v_item,v_Egid,v_EGName,0,v_sta,'');
end if;
else
insert into EvAvg(batid,gid,sn,item,Egid,Egname,score,status,Note)
values(ibatid,v_Gid,v_sn,v_item,v_Egid,v_EGName,0,v_sta,'');
end if;
end loop;
close AdduOb;
end;
declare
v_Gidevx.gid%type ;
v_GNameevx.gname%type ;
v_Egid evx.egid%type;
v_Item evx.item%type;
v_Sn evx.sn%type
;
v_Attr evx.Attr%type;
v_Evfs evx.Evfs%type;
v_name evx.itemname%type;
v_boss number;
v_posnumber;
cursor Addtask is
select a.gid,a.gname,a.attr,a.sn,b.item,b.Egid,b.name from evjob a,evitem b
where a.batid=b.batid anda.sn=b.sn and a.batid=ibatid;
begin
open Addtask;
loop
fetch Addtask into v_Gid,v_GName,v_Egid,v_attr,v_Sn,v_Item,v_Evfs,v_name;
exit when Addtask%notfound;
v_Egid:=0;
if v_Evfs=1 thenv_Egid:=0; end if;
if v_Evfs=2 then
if length(v_gid)>6 then
select head into iboss from ev_usergroup where orgid=v_gid;
v_Egid:=iboss;
else
v_Egid:=v_gid;
end if;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
if v_Evfs=4 then
if length(v_gid)>6 then
select father into ifather from ev_usergroup where orgid=v_gid;
v_Egid:=ifather;
else
declare
cursor selvalue is
select boss from ev_joint where gid=v_gid;
begin
open selvalue;
fetch selvalue into v_Egid;
exit when selvalue%notfound;
close selvalue;
end;
end if;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
if v_Evfs=0 then
select gid into v_Egid from ev_users where groupid=8;
v_Evfs:=4;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
ifv_Evfs=1then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
if v_Evfs=3then
if v_attr=5 then
declare
cursor selvalue is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid
and attr=7;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
end if;
if v_attr=1 then
declare
cursor selvalue is
select gid from evob wherebatid=ibatid and (attr=5 or attr=11);
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
end if;
if v_attr=15 then
declare
cursor selvalue is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid
and attr=17;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
if substr(v_gid,5,2)='01' then
if iOrgid=4 then
declare
cursor selvalue is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid and attr=15 and substr(gid,5,2)>'01' ;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
v_Pos:=0;v_boss:=0;
declare
cursor spp is
select bossfrom ev_joint where gid=v_Egid;
begin
open spp;
loop
fetch spp into v_boss;
exit when spp%notfound;
end loop;
close spp;
end;
declare
cursor spps is
select ATTRfrom evob where gid=v_boss and batid=ibatid;
begin
open spps;
loop
fetch spps into v_pos;
exit when spps%notfound;
end loop;
close spps;
end;
if v_pos=11 then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
end loop;
close selvalue;
end;
else
declare
cursor selval is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid
and attr=15 and substr(gid,5,2)>'01' ;
begin
open selval;
loop
fetch selval into v_Egid;
exit when selval%notfound;
v_Pos:=0;v_boss:=0;
declare
cursor spp is
select bossfrom ev_joint where gid=v_Egid;
begin
open spp;
loop
fetch spp into v_boss;
exit when spp%notfound;
end loop;
close spp;
end;
declare
cursor spps is
select ATTRfrom evob where gid=v_boss and batid=ibatid;
begin
open spps;
loop
fetch spps into v_pos;
exit when spps%notfound;
end loop;
close spps;
end;
if v_pos=15then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
end loop;
close selval;
end;
end if;
end if;
end if;
if v_attr=21 then
declare
cursor selvalue is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid
and attr=23;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
if substr(v_gid,5,2)='01' then
if iOrgid=4 then
declare
cursor selvalue is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid and attr=21 and substr(gid,5,2)>'01' ;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
v_Pos:=0;v_boss:=0;
declare
cursor spp is
select bossfrom ev_joint where gid=v_Egid;
begin
open spp;
loop
fetch spp into v_boss;
exit when spp%notfound;
end loop;
close spp;
end;
declare
cursor spps is
select ATTRfrom evob where gid=v_boss and batid=ibatid;
begin
open spps;
loop
fetch spps into v_pos;
exit when spps%notfound;
end loop;
close spps;
end;
if v_pos=11 then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
end loop;
close selvalue;
end;
else
declare
cursor selval is
select gid from evob where substr(gid,3,2)=substr(v_gid,3,2) and batid=ibatid
and attr=21 and substr(gid,5,2)>'01' ;
begin
open selval;
loop
fetch selval into v_Egid;
exit when selval%notfound;
v_Pos:=0;v_boss:=0;
declare
cursor spp is
select bossfrom ev_joint where gid=v_Egid;
begin
open spp;
loop
fetch spp into v_boss;
exit when spp%notfound;
end loop;
close spp;
end;
declare
cursor spps is
select ATTRfrom evob where gid=v_boss and batid=ibatid;
begin
open spps;
loop
fetch spps into v_pos;
exit when spps%notfound;
end loop;
close spps;
end;
if v_pos=21then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
end loop;
close selval;
end;
end if;
end if;
end if;
if v_attr=11 then
declare
cursor selvalue is
select gid from evob wherebatid=ibatid and (attr=15 or attr=21) and substr(gid,5,2)='01';
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
if iorgid=3 then
declare
cursor selvalue is
select gid from evob wherebatid=ibatid and (attr=15 or attr=21) and substr(gid,5,2)>'01' ;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
v_Pos:=0;v_boss:=0;
declare
cursor spp is
select bossfrom ev_joint where gid=v_Egid;
begin
open spp;
loop
fetch spp into v_boss;
exit when spp%notfound;
end loop;
close spp;
end;
declare
cursor spps is
select ATTRfrom evob where gid=v_boss and batid=ibatid;
begin
open spps;
loop
fetch spps into v_pos;
exit when spps%notfound;
end loop;
close spps;
end;
if v_pos=11 then
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end if;
end loop;
close selvalue;
end;
end if;
end if;
if v_attr=13 then
declare
cursor selvalue is
select gid from evob wherebatid=ibatid and attr=21 and substr(gid,5,2)='01' ;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
if length(v_gid)>6 then
declare
cursor selvalue is
select distinct a.head from ev_usergroup a,ev_joint b,evpos c where a.orgid=b.orgid and b.position=c.id and c.attr=21;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
end if;
end if;
if v_attr=3 then
declare
cursor selvalue is
select gid from evob wherebatid=ibatid and attr=11 ;
begin
open selvalue;
loop
fetch selvalue into v_Egid;
exit when selvalue%notfound;
insert into Evx(batid,gid,sn,item,status,attr,Egid,evfs,gname,Note,itemname)
values(ibatid,v_Gid,v_sn,v_item,0,v_attr,v_Egid,v_evfs,v_GName,'',v_name);
end loop;
close selvalue;
end;
end if;
end if;
end loop;
close Addtask;
end;
Update Evbat set status=3 where batid=ibatid;
commit;
end StpTask;
/
|
