create or replace trigger tri_sync_zjgcandrysd
after insert or update on ztb_new.ZTB_CHECKBIDSEGMENTDETAIL
for each row
declare
-- local variables here
vflag number; ---判断标志
jgbm number; ---监管部门ID
gg_kssj date; ---公告开始时间
gg_jssj date; ---公告结束时间
bm_dd varchar2(256); ---资审报名地点
zs_kssj date; ---资审开始时间
zs_jssj date; ---资审结束时间
zs_dd varchar2(256); ---资审地点
kb_kssj date; ---投标开标开始时间
kb_jssj date; ---投标开标结束时间
kb_dd varchar2(256); ---投标开标地点
pb_kssj date; ---评标开始时间
pb_jssj date; ---评标结束时间
pb_dd varchar2(256); ---评标地点
bdlx varchar2(256); ---标段类型
DIC_ZBHXR CONSTANT varchar2(32) := '10778ec22cb27538'; ---中标候选人标识
DIC_SG CONSTANT varchar2(32) := '10722cf72fa1486c15'; ---定义施工类型常量
DIC_JL CONSTANT varchar2(32) := '10722d04c81184f188'; ---定义监理类型常量
DIC_SJ CONSTANT varchar2(32) := '10722cf9a39196313c'; ---定义设计类型常量
DIC_CG CONSTANT varchar2(32) := '10722d1716a1847f54'; ---定义采购类型常量
DIC_JC CONSTANT varchar2(32) := '112025046074f0f9700000000'; ---定义检测类型常量
/**************以下同步在建工程用***********/
dwid number;
bdid varchar2(32);
gcmc varchar2(256);
jhkgsj date;
jhwgsj date;
htj number(16, 4);
gclx varchar2(32);
hth varchar2(256);
jsdw varchar2(256);
xmjlmc varchar2(20);
xmjlid varchar2(150);
begin
-----------将评标流程中录入中标单位信息以及项目经理信息同步到企业的在建项目中并同时将项目经理进行锁定-------
---------判断是否是中标单位记录-------
if (:new.bidintraduce = DIC_ZBHXR) then
--**********以下为根据标段ID从各表得到相应插入或更新的信息
---公告时间信息
select signbegindate, signenddate
into gg_kssj, gg_jssj
from ztb_bidsegmentplacard
where instr(bidsegmentids, :new.bidsegmentid) > 0
and rownum = 1;
bm_dd := '网上报名'; ---默认资审地点为网上报名
---资审时间信息
begin
select begindate, enddate, nvl(confirmendarea, '')
into zs_kssj, zs_jssj, zs_dd
from ZTB_QUALIFICATIONCONFIRM
where bidsegmentid = :new.bidsegmentid;
exception
when others then
begin
zs_kssj := null;
zs_jssj := null;
zs_dd := '';
end;
end;
---投标开标时间信息
begin
select begindate, enddate, nvl(invitebidbeginarea, '')
into kb_kssj, kb_jssj, kb_dd
from ZTB_INVITEANDTENDER
where bidsegmentid = :new.bidsegmentid;
exception
when others then
begin
kb_kssj := null;
kb_jssj := null;
kb_dd := '';
end;
end;
--zs_kssj = null;
--zs_jssj = null;
---评标时间信息
select begindate, enddate, nvl(checkbiginarea, '')
into pb_kssj, pb_jssj, pb_dd
from ZTB_CHECKBIDSEGMENT
where bidsegmentid = :new.bidsegmentid;
---判断标段扩展信息表中是否有该标段数据,如没有则插入,否则更新
select count(*)
into vflag
from ztb_bidsegmentextendinfo t
where t.bidsegmentid = :new.bidsegmentid;
---取得该标段的监管部门
select checkentri
into jgbm
from ztb_bidsegmentbaseinfo
where id = :new.bidsegmentid;
if (vflag = 0) then
------如果没有记录则插入
begin
insert into ztb_bidsegmentextendinfo
(bidsegmentid,
signbegindate,
signenddate,
signbeginarea,
placardbegindate,
placardenddate,
confirmbegindate,
confirmenddate,
confirmendarea,
invitebidbegindate,
inviteenddate,
invitebidbeginarea,
checkbigindate,
checkenddate,
checkbiginarea,
bidcomids,
bidprice,
projectmanager,
checkcom)
values
(:new.bidsegmentid,
gg_kssj,
gg_jssj,
bm_dd,
gg_kssj,
gg_jssj,
zs_kssj,
zs_jssj,
zs_dd,
kb_kssj,
kb_jssj,
kb_dd,
pb_kssj,
pb_jssj,
pb_dd,
:new.comids,
:new.bidprice / 10000,
getpmname(getPrjmanager(:new.bidsegmentid, :new.comids)),
jgbm);
end;
else
------有记录,进行更新
begin
update ztb_bidsegmentextendinfo
set signbegindate = gg_kssj,
signenddate = gg_jssj,
signbeginarea = bm_dd,
placardbegindate = gg_kssj,
placardenddate = gg_jssj,
confirmbegindate = zs_kssj,
confirmenddate = zs_jssj,
confirmendarea = zs_dd,
invitebidbegindate = kb_kssj,
inviteenddate = kb_jssj,
invitebidbeginarea = kb_dd,
checkbigindate = pb_kssj,
checkenddate = pb_jssj,
checkbiginarea = pb_dd,
bidcomids = :new.comids,
bidprice = :new.bidprice / 10000,
projectmanager = getpmname(getPrjmanager(:new.bidsegmentid,
:new.comids)),
checkcom = jgbm
where bidsegmentid = :new.bidsegmentid;
end;
end if;
---结合标段类型判断当前企业在建项目中是否存该业绩,存在则更新否则插入
--得到当前标段类型
select projectype
into bdlx
from ztb_bidsegmentbaseinfo
where id = :new.bidsegmentid;
---根据企业类型同步在建项目信息至不同类型单位
---施工
if (bdlx = DIC_SG) then
begin
---先删除再插入
--select count(*) into vflag from ztb_conenterprojectinfo where pk=:new.bidsegmentid and id=:new.comids;
delete from ztb_conenterprojectinfo where pk = :new.bidsegmentid;
---取得同步在建工程相关信息
select :new.comids,
:new.bidsegmentid,
x.name,
y.planbegindate,
y.planenddate,
:new.bidprice / 10000,
y.buildtype,
y.name,
getdwmc(x.constrcomid),
getpmname(getprjmanager(:new.bidsegmentid, :new.comids)),
getprjmanager(:new.bidsegmentid, :new.comids)
into dwid,
bdid,
gcmc,
jhkgsj,
jhwgsj,
htj,
gclx,
hth,
jsdw,
xmjlmc,
xmjlid
from ztb_projectbaseinfo x
join ztb_bidsegmentbaseinfo y on x.id = y.projectid
where y.id = :new.bidsegmentid;
---插入
insert into ztb_conenterprojectinfo
(id,
pk,
prjname,
begintime,
finishtime,
contractprice,
prjtype,
contractno,
buildingunit,
prjmanager,
prjmanagerid,
enterprjtype)
values
(dwid,
bdid,
gcmc,
jhkgsj,
jhwgsj,
htj,
gclx,
hth,
jsdw,
xmjlmc,
xmjlid,
'1');
end;
end if;
---监理
if (bdlx = DIC_JL) then
begin
delete from ztb_watchenterproject where pk = :new.bidsegmentid;
select :new.comids,
:new.bidsegmentid,
x.name,
y.planbegindate,
y.planenddate,
y.buildtype,
y.name,
getdwmc(x.constrcomid),
getpmname(getprjmanager(:new.bidsegmentid, :new.comids))
into dwid, bdid, gcmc, jhkgsj, jhwgsj, gclx, hth, jsdw, xmjlmc
from ztb_projectbaseinfo x
join ztb_bidsegmentbaseinfo y on x.id = y.projectid
where y.id = :new.bidsegmentid;
insert into ztb_watchenterproject
(id,
pk,
prjname,
prjtype,
foundunit,
begintime,
finishtime,
watchleader,
prjstatus)
values
(dwid,
bdid,
gcmc || '(' || hth || ')',
gclx,
jsdw,
jhkgsj,
jhwgsj,
xmjlmc,
'1');
end;
end if;
---设计
if (bdlx = DIC_SJ) then
begin
delete from ztb_surveyenterprojectinfo
where pk = :new.bidsegmentid;
select :new.comids,
:new.bidsegmentid,
x.name,
y.planbegindate,
y.planenddate,
y.name,
getdwmc(x.constrcomid),
getpmname(getprjmanager(:new.bidsegmentid, :new.comids)),
getprjmanager(:new.bidsegmentid, :new.comids),
:new.bidprice / 10000
into dwid,
bdid,
gcmc,
jhkgsj,
jhwgsj,
hth,
jsdw,
xmjlmc,
xmjlid,
htj
from ztb_projectbaseinfo x
join ztb_bidsegmentbaseinfo y on x.id = y.projectid
where y.id = :new.bidsegmentid;
insert into ztb_surveyenterprojectinfo
(id,
pk,
prjname,
buildingunit,
contractprice,
prjmanager,
begintime,
finishtime,
prjmanagerid,
enterprjtype)
values
(dwid,
bdid,
gcmc || '(' || hth || ')',
jsdw,
htj,
xmjlmc,
jhkgsj,
jhwgsj,
xmjlid,
'1');
end;
end if;
---采购
if (bdlx = DIC_CG) then
begin
delete from ztb_purchaseenterprojectinfo
where pk = :new.bidsegmentid;
select :new.comids,
:new.bidsegmentid,
x.name,
y.planbegindate,
y.planenddate,
y.name,
getdwmc(x.constrcomid),
getpmname(getprjmanager(:new.bidsegmentid, :new.comids)),
getprjmanager(:new.bidsegmentid, :new.comids),
:new.bidprice / 10000
into dwid,
bdid,
gcmc,
jhkgsj,
jhwgsj,
hth,
jsdw,
xmjlmc,
xmjlid,
htj
from ztb_projectbaseinfo x
join ztb_bidsegmentbaseinfo y on x.id = y.projectid
where y.id = :new.bidsegmentid;
insert into ztb_purchaseenterprojectinfo
(id,
pk,
prjname,
buildingunit,
contractprice,
prjmanager,
begintime,
finishtime,
prjmanagerid,
enterprjtype)
values
(dwid,
bdid,
gcmc || '(' || hth || ')',
jsdw,
htj,
xmjlmc,
jhkgsj,
jhwgsj,
xmjlid,
'1');
end;
end if;
end if;
end tri_sync_zjgcandrysd;
