You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
181 lines
14 KiB
Plaintext
181 lines
14 KiB
Plaintext
create trigger [dbo].[td_IN_STATIONSQUADSUM] on [dbo].[IN_STATIONSQUADSUM] for delete as
|
|
begin
|
|
delete IN_STATIONSQUADSUM_BCENTER from IN_STATIONSQUADSUM_BCENTER ib,IN_STATIONSQUADSUM i
|
|
where ib.id=i.ID;
|
|
end;
|
|
go
|
|
create trigger [dbo].[td_OUT_STATIONSQUADSUM] on [dbo].[OUT_STATIONSQUADSUM] for delete as
|
|
begin
|
|
|
|
delete OUT_STATIONSQUADSUM_BCENTER from OUT_STATIONSQUADSUM_BCENTER ib,OUT_STATIONSQUADSUM i
|
|
where ib.id=i.ID;
|
|
end;
|
|
|
|
|
|
ALTER trigger [dbo].[ti_STATIONSQUADSUM] on [dbo].[STATIONSQUADSUM] for INSERT as
|
|
declare @classdt datetime
|
|
declare @seqno int
|
|
declare @roadno int
|
|
declare @stano int
|
|
declare @portype int
|
|
declare @gbstatinid varchar(20)
|
|
declare @id varchar(50)
|
|
begin
|
|
select @classdt=CLASSDATE,@roadno=ROADNO,@stano=STANO,@seqno=SEQNO,@portype=PORTTYPE from INSERTED
|
|
insert into STATIONSQUADSUM_center select * from inserted i;
|
|
--select @seqno=(case when max(SEQNO)>0 then max(SEQNO)+1 else 1 end) from OUT_STATIONSQUADSUM where date=@classdt and ROADNO=@roadno and STANO=@stano
|
|
--n.ROADNO=o.roadno and n.STANO=o.STANO
|
|
select @gbstatinid=GBSTATIONID from NAME_TABLE n where n.ROADNO=@roadno and n.STANO=@stano;
|
|
set @id=@gbstatinid+CONVERT(varchar(10),@classdt,112);
|
|
if(@portype=2)
|
|
begin
|
|
delete from OUT_STATIONSQUADSUM where ID=@id;
|
|
insert into OUT_STATIONSQUADSUM(ID,SEQNO,ROADNO,STANO,DATE,ETCTYPECOUNT,ETCCLASSCOUNT,ETCSUCCESSCOUNT,ETCSUCCESSFEE,ETCFAILCOUNT,ETCFAILFEE
|
|
,CPCTYPECOUNT,CPCCLASSCOUNT,CPCSUCCESSCOUNT,CPCSUCCESSFEE,CPCFAILCOUNT,CPCFAILFEE,PAPERSUCCESSCOUNT,PAPERSUCCESSFEE,PAPERFAILCOUNT,PAPERFAILFEE)
|
|
select (select GBSTATIONID from NAME_TABLE n where n.ROADNO=@roadno and n.STANO=@stano)+CONVERT(varchar(10),CLASSDATE,112) as GBSTATIONID,
|
|
@seqno,ROADNO,STANO,CLASSDATE,
|
|
'1:'+CONVERT(varchar(10),SUM(case when TYPE=1 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'2:'+CONVERT(varchar(10),SUM(case when TYPE=2 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'3:'+CONVERT(varchar(10),SUM(case when TYPE=3 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'4:'+CONVERT(varchar(10),SUM(case when TYPE=4 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'11:'+CONVERT(varchar(10),SUM(case when TYPE=11 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'12:'+CONVERT(varchar(10),SUM(case when TYPE in(5,12) and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'13:'+CONVERT(varchar(10),SUM(case when TYPE in(6,13) and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when TYPE in(7,14) and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'15:'+CONVERT(varchar(10),SUM(case when TYPE in(8,15) and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'16:'+CONVERT(varchar(10),SUM(case when TYPE in(9,16) and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when TYPE=21 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when TYPE=22 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when TYPE=23 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when TYPE=24 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when TYPE=25 and PAYTYPE in(22,23) then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when TYPE=26 and PAYTYPE in(22,23) then 1 else 0 end)) as ETC_TYPE_CT,
|
|
'0:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=0 then 1 else 0 end))+'|'+
|
|
'8:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=8 then 1 else 0 end))+'|'+
|
|
'10:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=10 then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=14 then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=21 then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=22 then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=23 then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=24 then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=25 then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=26 then 1 else 0 end))+'|'+
|
|
'27:'+CONVERT(varchar(10),SUM(case when PAYTYPE in(22,23) and vehicleClass=27 then 1 else 0 end)) as ETC_CLASS_CT,
|
|
SUM(case when PAYCASH<>0 and PAYTYPE in(22,23) then 1 else 0 end) as ETC_CT,
|
|
SUM(case when PAYCASH>0 and PAYTYPE in(22,23) then PAYCASH else 0 end) as ETC_PAYCASH,0,0,
|
|
'1:'+CONVERT(varchar(10),SUM(case when TYPE=1 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'2:'+CONVERT(varchar(10),SUM(case when TYPE=2 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'3:'+CONVERT(varchar(10),SUM(case when TYPE=3 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'4:'+CONVERT(varchar(10),SUM(case when TYPE=4 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'11:'+CONVERT(varchar(10),SUM(case when TYPE=11 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'12:'+CONVERT(varchar(10),SUM(case when TYPE in(5,12) and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'13:'+CONVERT(varchar(10),SUM(case when TYPE in(6,13) and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when TYPE in(7,14) and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'15:'+CONVERT(varchar(10),SUM(case when TYPE in(8,15) and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'16:'+CONVERT(varchar(10),SUM(case when TYPE in(9,16) and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when TYPE=21 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when TYPE=22 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when TYPE=23 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when TYPE=24 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when TYPE=25 and PAYTYPE not in(22,23) then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when TYPE=26 and PAYTYPE not in(22,23) then 1 else 0 end)) as CPC_TYPE_CT,
|
|
'0:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=0 then 1 else 0 end))+'|'+
|
|
'8:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=8 then 1 else 0 end))+'|'+
|
|
'10:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=10 then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=14 then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=21 then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=22 then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=23 then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=24 then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=25 then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=26 then 1 else 0 end))+'|'+
|
|
'27:'+CONVERT(varchar(10),SUM(case when PAYTYPE not in(22,23) and vehicleClass=27 then 1 else 0 end)) as CPC_CLASS_CT,
|
|
SUM(case when INCASH>0 and PAYTYPE not in(22,23) then 1 else 0 end) as CPC_CT,
|
|
SUM(case when INCASH>0 and PAYTYPE not in(22,23) then INCASH else 0 end) as CPC_INCASH,0,0,
|
|
SUM(case when CARDTP=5 then 1 else 0 end) as PAPER_CT,
|
|
SUM(case when CARDTP=5 then INCASH else 0 end) as PAPER_INCASH,0,0
|
|
from out_port o where CLASSDATE=@classdt and ROADNO=@roadno and STANO=@stano group by ROADNO,STANO,CLASSDATE
|
|
end
|
|
if(@portype=1)
|
|
begin
|
|
delete from IN_STATIONSQUADSUM where ID=@id;
|
|
insert into IN_STATIONSQUADSUM(ID,SEQNO,ROADNO,STANO,DATE,ETCTYPECOUNT,ETCCLASSCOUNT,ETCSUCCESSCOUNT,ETCFAILCOUNT
|
|
,CPCTYPECOUNT,CPCCLASSCOUNT,CPCCOUNT,PAPERCOUNT)
|
|
select (select GBSTATIONID from NAME_TABLE n where n.ROADNO=@roadno and n.STANO=@stano)+CONVERT(varchar(10),CLASSDATE,112) as GBSTATIONID,
|
|
@seqno,ROADNO,STANO,CLASSDATE,
|
|
'1:'+CONVERT(varchar(10),SUM(case when TYPE=1 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'2:'+CONVERT(varchar(10),SUM(case when TYPE=2 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'3:'+CONVERT(varchar(10),SUM(case when TYPE=3 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'4:'+CONVERT(varchar(10),SUM(case when TYPE=4 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'11:'+CONVERT(varchar(10),SUM(case when TYPE=11 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'12:'+CONVERT(varchar(10),SUM(case when TYPE in(5,12) and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'13:'+CONVERT(varchar(10),SUM(case when TYPE in(6,13) and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when TYPE in(7,14) and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'15:'+CONVERT(varchar(10),SUM(case when TYPE in(8,15) and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'16:'+CONVERT(varchar(10),SUM(case when TYPE in(9,16) and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when TYPE=21 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when TYPE=22 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when TYPE=23 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when TYPE=24 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when TYPE=25 and CARDTP in(22,23) then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when TYPE=26 and CARDTP in(22,23) then 1 else 0 end)) as ETC_TYPE_CT,
|
|
'0:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=0 then 1 else 0 end))+'|'+
|
|
'8:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=8 then 1 else 0 end))+'|'+
|
|
'10:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=10 then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=14 then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=21 then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=22 then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=23 then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=24 then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=25 then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=26 then 1 else 0 end))+'|'+
|
|
'27:'+CONVERT(varchar(10),SUM(case when CARDTP in(22,23) and vehicleClass=27 then 1 else 0 end)) as ETC_CLASS_CT,
|
|
SUM(case when CARDTP in(22,23) then 1 else 0 end) as ETC_CT,0,
|
|
'1:'+CONVERT(varchar(10),SUM(case when TYPE=1 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'2:'+CONVERT(varchar(10),SUM(case when TYPE=2 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'3:'+CONVERT(varchar(10),SUM(case when TYPE=3 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'4:'+CONVERT(varchar(10),SUM(case when TYPE=4 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'11:'+CONVERT(varchar(10),SUM(case when TYPE=11 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'12:'+CONVERT(varchar(10),SUM(case when TYPE in(5,12) and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'13:'+CONVERT(varchar(10),SUM(case when TYPE in(6,13) and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when TYPE in(7,14) and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'15:'+CONVERT(varchar(10),SUM(case when TYPE in(8,15) and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'16:'+CONVERT(varchar(10),SUM(case when TYPE in(9,16) and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when TYPE=21 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when TYPE=22 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when TYPE=23 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when TYPE=24 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when TYPE=25 and CARDTP not in(22,23) then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when TYPE=26 and CARDTP not in(22,23) then 1 else 0 end)) as CPC_TYPE_CT,
|
|
'0:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=0 then 1 else 0 end))+'|'+
|
|
'8:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=8 then 1 else 0 end))+'|'+
|
|
'10:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=10 then 1 else 0 end))+'|'+
|
|
'14:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=14 then 1 else 0 end))+'|'+
|
|
'21:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=21 then 1 else 0 end))+'|'+
|
|
'22:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=22 then 1 else 0 end))+'|'+
|
|
'23:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=23 then 1 else 0 end))+'|'+
|
|
'24:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=24 then 1 else 0 end))+'|'+
|
|
'25:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=25 then 1 else 0 end))+'|'+
|
|
'26:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=26 then 1 else 0 end))+'|'+
|
|
'27:'+CONVERT(varchar(10),SUM(case when CARDTP not in(22,23) and vehicleClass=27 then 1 else 0 end)) as CPC_CLASS_CT,
|
|
SUM(case when CARDTP not in(22,23) then 1 else 0 end) as CPC_CT,
|
|
SUM(case when CARDTP=5 then 1 else 0 end) as PAPER_CT
|
|
from IN_port o where CLASSDATE=@classdt and ROADNO=@roadno and STANO=@stano group by ROADNO,STANO,CLASSDATE
|
|
end
|
|
if(@portype=8)
|
|
begin
|
|
DELETE GANTRY_STAT FROM GANTRY_STAT G,Inserted I WHERE G.ROADNO=I.ROADNO AND G.STANO=I.STANO
|
|
AND G.CLASSDATE=I.CLASSDATE;
|
|
INSERT INTO GANTRY_STAT SELECT
|
|
G.roadNo,G.staNo,I.CLASSDATE,G.gantryId,G.mediaType,G.vehicleType,G.vehicleClass,G.specialType,
|
|
G.axleCount,COUNT(1),G.tradeResult,G.holidayState,G.validStatus,SUM(G.payFee),SUM(G.fee),
|
|
SUM(G.FEESUMLOCALAFTER),0,0,'0','0',GETDATE()
|
|
FROM GANTRY_PASS G,Inserted I
|
|
WHERE G.hourbatchNo >=CONVERT(varchar(12),@classdt,112)+'00' and G.hourbatchNo <=CONVERT(varchar(12),@classdt,112)+'23'
|
|
--G.transTime>=I.CLASSDATE AND G.transTime<=DATEADD(s,59,DATEADD(n,59,DATEADD(hh,23,I.CLASSDATE)))
|
|
AND G.roadNo=I.ROADNO AND G.staNo=I.STANO AND I.PORTTYPE=8
|
|
GROUP BY
|
|
G.roadNo,G.staNo,G.gantryId,G.mediaType,G.vehicleType,g.vehicleClass,G.specialType,
|
|
G.axleCount,G.tradeResult,G.holidayState,G.validStatus,I.CLASSDATE;
|
|
end
|
|
end |