| "localhost:/firebird/data/ILICHEVSK2.FDB". Триггеры |
|
IT_Set_Weight_In_Silage
| "localhost:/firebird/data/ILICHEVSK2.FDB". Триггеры |
|
IT_Set_Weight_In_Silage
Описание
Занесение/уборка веса силоса при приходе/расходе
Определение
CREATE TRIGGER "IT_Set_Weight_In_Silage" FOR IT
ACTIVE AFTER INSERT OR UPDATE POSITION 0
as
declare variable SILAGEPACKID Integer;
declare variable QUALITYID Integer;
declare variable MIXINGCLASSES Integer;
declare variable SQQUALITYID Integer;
declare variable SILAGEQUALITYID Integer;
declare variable OLDSQUALITYID Integer;
declare variable SILAGEID Integer;
declare variable PERCENTWEIGHT Integer;
declare variable VSP_SQID Integer;
declare variable VSP_QID Integer;
declare variable VSQ_QID Integer;
declare variable VCLASS_ID Integer;
declare variable VCROP_ID Integer;
declare variable VSORT_ID Integer;
---
declare variable ITCLASSID Integer;
declare variable OLDPERSENTWEIGHT Integer;
declare variable OLDSILAGEID Integer;
declare variable TRSUBTYPE Integer;
declare variable DELTA Integer;
declare variable OLDSILAGEQUALITYID Integer;
declare variable OLDSQID Integer;
declare variable PARTNET Integer;
begin -- добавление учета веса в SILAGEQaulity + и -
select STORAGE.MIXINGCLASSES
from STORAGE
where (STORAGE.STORAGEID = new.STORAGEID)
into :MIXINGCLASSES;
/* Приход */
/* 1. Наличие в SILAGEPAck SILAGEQUALITY : 1.2 update или 1.1 insert
Если есть 2. проверка по классу в SILAGEQUALITY : 2.2 update или 2.1 insert
1.1. insert в SILAGEQUALITY, gen_ID SILAGEQUALITYID, QUALITYID
update SILAGEPack SILAGEQUALITYID
1.2. update SILAGEQUALITY по классу ,
|
2.1 insert в SILAGEQUALITY, gen_ID QUALITYID
2.2 update SILAGEQUALITY по классу ,
п.3 Обновление по внутр. перемещениям (расход)
*/
if ((new.NET is not null) and (new.SILAGEID is not null)
and ((old.NET is null) or (old.SILAGEID is null))
and (new.OPERATIONID in (1,12,20,29,31,32,54)) and (new.STATE >= 0)) then begin
update SILAGE
set SILAGE.WEIGHT = coalesce(SILAGE.WEIGHT,0) + new.NET,
SILAGE.INVENTORYID = new.INVENTORYID
where (SILAGE.SILAGEID = new.SILAGEID);
/*
where (SILAGE.SILAGEID = new.SILAGEID) and (SILAGE.WEIGHT is not null);
update SILAGE
set SILAGE.WEIGHT = new.NET,
SILAGE.INVENTORYID = new.INVENTORYID
where (SILAGE.SILAGEID = new.SILAGEID) and (SILAGE.WEIGHT is null);
*/
-- добавить здесь
if (:MIXINGCLASSES = 1) then begin
VSQ_QID = null;
VSP_QID = null;
select QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID
from QUALITY
where (QUALITY.QUALITYID = new.SAMPLERQUALITYID)
into :VCROP_ID, :VCLASS_ID, :VSORT_ID;
-- 1.1
select SILAGEPACKS.SILAGEQUALITYID, SILAGEPACKS.QUALITYID
from SILAGEPACKS
left join SILAGE on (SILAGE.SILAGEPACKID = SILAGEPACKS.SILAGEPACKID)
where (SILAGE.SILAGEID = new.SILAGEID)
into :VSP_SQID, :VSP_QID;
if (:VSP_SQID is null) then begin --{1.1
VSP_QID = gen_id(QUALITYIDGEN,1);
VSQ_QID = gen_id(QUALITYIDGEN,1);
VSP_SQID = gen_id(SILAGEQUALITYIDGEN,1);
insert into QUALITY (QUALITYID, CROPID) -- new SILAGEPACKS.QUALITYID
values (:VSP_QID, :VCROP_ID);
insert into QUALITY (QUALITYID, CLASSID, CROPID, SORTID) -- new SILAGEQUALITY.QUALITYID
values (:VSQ_QID, :VCLASS_ID, :VCROP_ID, :VSORT_ID);
insert into SILAGEQUALITY (SILAGEQUALITYID, QUALITYID, WEIGHT)
values (:VSP_SQID, :VSQ_QID, new.NET);
update SILAGEPACKS
set SILAGEPACKS.SILAGEQUALITYID = :VSP_SQID,
SILAGEPACKS.QUALITYID = :VSP_QID
where (SILAGEPACKS.SILAGEPACKID = (select first 1 SILAGE.SILAGEPACKID
from SILAGE
where SILAGE.SILAGEID = new.SILAGEID));
end --1.1 }
else begin --проверка по классу 1.2 -> 2
select SILAGEQUALITY.QUALITYID
from SILAGEQUALITY
left join QUALITY Q1 on (Q1.QUALITYID = SILAGEQUALITY.QUALITYID)
where (SILAGEQUALITY.SILAGEQUALITYID = :VSP_SQID) and (Q1.CLASSID = :VCLASS_ID)
into: VSQ_QID;
if (:VSQ_QID is null) then begin --{2.1
VSQ_QID = gen_id(QUALITYIDGEN,1);
insert into SILAGEQUALITY(SILAGEQUALITYID, QUALITYID, WEIGHT)
values(:VSP_SQID, :VSQ_QID, new.NET);
insert into QUALITY (QUALITYID, CLASSID, CROPID, SORTID)
values (:VSQ_QID, :VCLASS_ID, :VCROP_ID, :VSORT_ID);
end --2.1}
else --{2.2
if (not (new.OPERATIONID in (29,31))) then begin
update SILAGEQUALITY
set SILAGEQUALITY.WEIGHT = SILAGEQUALITY.WEIGHT + new.NET
where (SILAGEQUALITY.SILAGEQUALITYID = :VSP_SQID)
and (SILAGEQUALITY.QUALITYID = :VSQ_QID);
--// --2.2}
end
end
end
-- if (new.OPERATIONID in (12,29,31)) then begin
if (new.OPERATIONID in (29,31)) then begin
update SILAGE
set SILAGE.INVENTORYID = new.INVENTORYID
where (SILAGE.SILAGEID = new.ROUTEID);
if (new.ROUTEID is not null) then begin
select SILAGEPACKS.SILAGEQUALITYID
from SILAGEPACKS
where (SILAGEPACKS.SILAGEPACKID = new.ROUTEID)
into :OLDSQUALITYID;
update SILAGEQUALITY
set WEIGHT = WEIGHT - new.NET
where (SILAGEQUALITY.SILAGEQUALITYID = :OLDSQUALITYID)
and (SILAGEQUALITY.QUALITYID = (select SQ.QUALITYID
from SILAGEQUALITY SQ
left join QUALITY Q1 on (SQ.QUALITYID = Q1.QUALITYID)
where (SQ.SILAGEQUALITYID = :OLDSQUALITYID)
and (Q1.CLASSID = :VCLASS_ID)));
if (:MIXINGCLASSES = 1) then
update SILAGEQUALITY
set SILAGEQUALITY.WEIGHT = SILAGEQUALITY.WEIGHT + new.NET
where (SILAGEQUALITY.SILAGEQUALITYID = :VSP_SQID)
and (SILAGEQUALITY.QUALITYID = :VSQ_QID);
end
end
select SILAGE.SILAGEPACKID
from SILAGE
where (SILAGE.SILAGEID = new.SILAGEID)
into :SILAGEPACKID;
select SILAGEPACKS.QUALITYID
from SILAGEPACKS
where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID)
into :QUALITYID;
if (:QUALITYID is null) then begin
if (:MIXINGCLASSES = 1) then
update SILAGEPACKS
set STATE = 0
where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID);
else
update SILAGEPACKS
set STATE = 0,
QUALITYID = new.SAMPLERQUALITYID
where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID);
end
else
update SILAGEPACKS
set STATE = 0
where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID);
end
/* Расход */
if ((new.NET is not null) and (new.SILAGEID is not null)
and ((old.NET is null) or (old.SILAGEID is null)) and (new.OPERATIONID in (5))
and (((new.STATE >= 0) and (old.STATE <> -100)) or (new.STATE = -100))) then begin
update SILAGE
set SILAGE.WEIGHT = SILAGE.WEIGHT - new.NET,
SILAGE.INVENTORYID = new.INVENTORYID
where (SILAGE.SILAGEID = new.SILAGEID) and (SILAGE.WEIGHT is not null);
if (:MIXINGCLASSES = 1) then begin
select SILAGEQUALITY.SILAGEQUALITYID, SILAGEQUALITY.QUALITYID
from SILAGE
join SILAGEPACKS on (SILAGE.SILAGEPACKID = SILAGEPACKS.SILAGEPACKID)
join SILAGEQUALITY on (SILAGEQUALITY.SILAGEQUALITYID = SILAGEPACKS.SILAGEQUALITYID)
join QUALITY Q1 on (new.SAMPLERQUALITYID = Q1.QUALITYID)
join QUALITY Q2 on (SILAGEQUALITY.QUALITYID = Q2.QUALITYID)
where (SILAGE.SILAGEID = new.SILAGEID) and (Q2.CLASSID = Q1.CLASSID)
into :SILAGEQUALITYID, :SQQUALITYID;
update SILAGEQUALITY
set SILAGEQUALITY.WEIGHT = SILAGEQUALITY.WEIGHT - new.NET
where (SILAGEQUALITY.SILAGEQUALITYID = :SILAGEQUALITYID)
and (SILAGEQUALITY.QUALITYID = :SQQUALITYID);
end
end
/* --- Для отгрузки не из одного силоса ---- переехало в ROUTESILAGECHEMA в триггер ROUTESCH_INSERTSQ*/
if ((new.NET is not null) and (new.SILAGEID is null)
and (old.SILAGEID is null) and (new.ROUTEID is not null)
and ((old.NET is null) or (old.ROUTEID is null)) and (new.OPERATIONID in (5))
and (((new.STATE >= 0) and (old.STATE <> -100)) or (new.STATE = -100))) then begin
for
select SILAGEID, PERCENTWEIGHT
from ROUTESILAGECHEMA
where (ROUTECHEMAID = new.ROUTEID)
into :SILAGEID, :PERCENTWEIGHT
do begin
PARTNET = cast(cast(new.NET * :PERCENTWEIGHT as numeric (15,2)) / 100 as integer);
update SILAGE
set SILAGE.WEIGHT = SILAGE.WEIGHT - :PARTNET,
SILAGE.INVENTORYID = new.INVENTORYID
where (SILAGE.SILAGEID = :SILAGEID) and (SILAGE.WEIGHT is not null);
if (:MIXINGCLASSES = 1) then begin
select SILAGEQUALITY.SILAGEQUALITYID, SILAGEQUALITY.QUALITYID
from SILAGE
join SILAGEPACKS on (SILAGEPACKS.SILAGEPACKID = SILAGE.SILAGEPACKID)
join SILAGEQUALITY on (SILAGEQUALITY.SILAGEQUALITYID = SILAGEPACKS.SILAGEQUALITYID)
join QUALITY Q1 on (new.SAMPLERQUALITYID = Q1.QUALITYID)
join QUALITY Q2 on (SILAGEQUALITY.QUALITYID = Q2.QUALITYID)
where (SILAGE.SILAGEID = :SILAGEID) and (Q2.CLASSID = Q1.CLASSID)
into :SILAGEQUALITYID, :SQQUALITYID;
update SILAGEQUALITY
set WEIGHT = WEIGHT - :PARTNET
where (SILAGEQUALITY.SILAGEQUALITYID = :SILAGEQUALITYID)
and (SILAGEQUALITY.QUALITYID = :SQQUALITYID);
end
end --*/
end
-- вот тут начинается веселье - изменение расхода и прихода
/*
********Общая часть изменения веса:********
1. выбрать силосы
a) если авто, то по SILAGEID из IT old.SILAGEID
б) иначе по ROUTEID -> ROUTESILAGECHEMA.ROUTECHEMAID ->
ROUTESILAGECHEMA.SILAGEID и ROUTESILAGECHEMA.PERCENTWEIGHT
2. Увеличить (уменьшить) вес в SILAGE по SILAGEID [SILAGE.WEIGHT = SILAGE.WEIGHT + DELTA ]
3. Увеличить (уменьшить) вес в SILAGEPack (триггер SILAGE_CalcSILAGEPACKSWEIGHT)
4. Увеличить (уменьшить) вес в SILAGEQUALITY по SILAGEPACKID и качеству
[SILAGEQUALITY.WEIGHT = SILAGEQUALITY.WEIGHT + DELTA ]
DELTA - разница:
1. old.NET - new.NET () - Меняется вес т.е. new.NET <> old.NET
2. old.NET Меняется STATE - если -1
3. old.NET Меняется SILAGEID
*/
if (((old.SILAGEID is not null) or ((old.ROUTEID is not null) and (old.SILAGEID is null))) -- если старый силос не null
and (old.NET is not null) -- если старый вес не null
and (old.OPERATIONID in (1,5)) -- приход и расход
and ((old.STATE >= 0) or (old.STATE = -100)) -- учитываемая запись
and ((coalesce(new.NET,0) <> old.NET) -- изменяется вес
or (((old.STATE >= 0) or (old.STATE = -100)) and (new.STATE = -1)) -- или изменяется состояние записи (удаляется)
or (old.SILAGEID <> new.SILAGEID) -- или изменяется силос
)
) then begin
-- 0. Вычисление DELTA
DELTA = 0;
if ((new.STATE = -1) or (old.SILAGEID <> new.SILAGEID)) then
DELTA = old.NET;
else begin
if (coalesce(new.NET,0) <> old.NET) then
DELTA = old.NET - coalesce(new.NET,0);
end
-- Если приход - поменяем знак на противоположный дабы не повторять фрагмент -
-- действия ведь практически те же что и при расходе только с точностью
-- до наоборот...
if (old.OPERATIONID in (1)) then
DELTA = -DELTA;
-- 1. Выбор силоса
for
select
case
when (TT.TRANSPORTSUBTYPE = 0) then
IT.SILAGEID
else ROUTESILAGECHEMA.SILAGEID
end SILAGEID,
/*case
when (TT.TRANSPORTSUBTYPE = 0) then
100
else ROUTESILAGECHEMA.PERCENTWEIGHT
end*/ 100 PERCENTWEIGHT,
QUALITY.CLASSID, SILAGEPACKS.SILAGEQUALITYID
from IT
join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
join TRANSPORTTYPE TT on (TT.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ROUTESILAGECHEMA on (ROUTESILAGECHEMA.ROUTECHEMAID = IT.ROUTEID)
/*
left join SILAGE on ((case
when (TT.TRANSPORTSUBTYPE = 1) then
ROUTESILAGECHEMA.SILAGEID
else old.SILAGEID
end) = SILAGE.SILAGEID)
*/
left join SILAGE on (old.SILAGEID = SILAGE.SILAGEID)
left join SILAGEPACKS on (SILAGEPACKS.SILAGEPACKID = SILAGE.SILAGEPACKID)
where (IT.INVENTORYID = old.INVENTORYID)
into :OLDSILAGEID, :OLDPERSENTWEIGHT, :ITCLASSID, :OLDSILAGEQUALITYID
do begin -- только авто?
-- 2. Увеличить (уменьшить) вес в SILAGE по SILAGEID [SILAGE.WEIGHT = SILAGE.WEIGHT + DELTA ]
update SILAGE
set SILAGE.WEIGHT = coalesce(SILAGE.WEIGHT,0) + :DELTA
where (SILAGE.SILAGEID = old.SILAGEID);
-- 4. Увеличить (уменьшить) вес в SILAGEQUALITY по SILAGEPACKID и качеству
if (:MIXINGCLASSES = 1) then begin
select SILAGEQUALITY.QUALITYID
from SILAGEQUALITY
join QUALITY on (QUALITY.QUALITYID = SILAGEQUALITY.QUALITYID)
where (SILAGEQUALITY.SILAGEQUALITYID = :OLDSILAGEQUALITYID)
and (QUALITY.CLASSID = :ITCLASSID)
into :OLDSQID;
update SILAGEQUALITY
set SILAGEQUALITY.WEIGHT = coalesce(SILAGEQUALITY.WEIGHT,0) + :DELTA
where (SILAGEQUALITY.SILAGEQUALITYID = :OLDSILAGEQUALITYID)
and (SILAGEQUALITY.QUALITYID = :OLDSQID);
end
if ((old.SILAGEID is not null) and (old.SILAGEID <> new.SILAGEID)) then begin --2, 4
update SILAGE
set SILAGE.WEIGHT = coalesce(SILAGE.WEIGHT,0) - :DELTA
where (SILAGE.SILAGEID = new.SILAGEID);
if (:MIXINGCLASSES = 1) then begin
select SILAGEQUALITY.SILAGEQUALITYID, SILAGEQUALITY.QUALITYID
from SILAGE
join SILAGEPACKS on (SILAGEPACKS.SILAGEPACKID = SILAGE.SILAGEPACKID)
join SILAGEQUALITY on (SILAGEQUALITY.SILAGEQUALITYID = SILAGEPACKS.SILAGEQUALITYID)
join QUALITY on (QUALITY.QUALITYID = SILAGEQUALITY.QUALITYID)
where (SILAGE.SILAGEID = new.SILAGEID) and (QUALITY.CLASSID = :ITCLASSID)
into :OLDSILAGEQUALITYID, :OLDSQID;
update SILAGEQUALITY
set SILAGEQUALITY.WEIGHT = coalesce(SILAGEQUALITY.WEIGHT,0) - :DELTA
where (SILAGEQUALITY.SILAGEQUALITYID = :OLDSILAGEQUALITYID)
and (SILAGEQUALITY.QUALITYID = :OLDSQID);
end
end
end
end
end
|
|