| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
EXPORT_1C
| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
EXPORT_1C
Входные параметры
Параметр | Тип | Описание |
|---|---|---|
DATE1 |
DATE |
|
DATE2 |
DATE |
|
OWNERID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
PARTYID |
INTEGER |
|
PARTYDATE |
DATE |
|
CONSIDERRENEWALSWEIGHT |
INTEGER |
|
ISWEIGHT |
INTEGER |
|
PRICEPERMONTH |
INTEGER |
|
NODE |
VARCHAR(30) |
|
Выходные параметры
Параметр | Тип | Описание |
|---|---|---|
SERVICEPRICE |
DECIMAL(15,2) |
|
SERVICEVALUE |
DECIMAL(15,3) |
|
SERVICESUM |
DECIMAL(15,2) |
|
SERVICENAME |
VARCHAR(140) |
|
SERVICECODE |
VARCHAR(20) |
|
SERVICEDATE |
DATE |
|
UNIT |
VARCHAR(25) |
|
CROPID |
INTEGER |
|
CLASSID |
INTEGER |
|
CODE_POSLUGA |
VARCHAR(10) |
|
CODE_KULTURA |
VARCHAR(10) |
|
Описание
(Нет описания для процедуры EXPORT_1C)
Определение
CREATE PROCEDURE EXPORT_1C(
DATE1 DATE,
DATE2 DATE,
OWNERID INTEGER,
CONTRACTID INTEGER,
PARTYID INTEGER,
PARTYDATE DATE,
CONSIDERRENEWALSWEIGHT INTEGER,
ISWEIGHT INTEGER,
PRICEPERMONTH INTEGER,
NODE VARCHAR(30))
RETURNS (
SERVICEPRICE DECIMAL(15,2),
SERVICEVALUE DECIMAL(15,3),
SERVICESUM DECIMAL(15,2),
SERVICENAME VARCHAR(140),
SERVICECODE VARCHAR(20),
SERVICEDATE DATE,
UNIT VARCHAR(25),
CROPID INTEGER,
CLASSID INTEGER,
CODE_POSLUGA VARCHAR(10),
CODE_KULTURA VARCHAR(10))
AS
declare variable STORINGNET decimal(15,9);
declare variable STORINGPRICE decimal(15,2);
declare variable ASERVICECODE integer;
declare variable ASERVICEDATE date;
declare variable ASERVICENAME varchar(100);
declare variable AMEASUREMENTUNIT varchar(20);
declare variable CROPCLASSNAME varchar(40);
declare variable DAYCOUNT integer;
declare variable ACROPID integer;
declare variable ACLASSID integer;
declare variable DISPLAYREGISTER integer;
begin
/*
Входные параметры:
DATE1 - начальная дата;
DATE2 - конечная дата;
OWNERID - идентификатор владельца;
CONTRACTID - идентификатор контракта;
PARTYID - идентификатор партии (может не передаваться);
PARTYDATE - дата, на которую партии активны;
CONSIDERRENEWALSWEIGHT - считать переоформления в весе:
0 - считать по количеству произведенных переоформлений,
1 - считать по количеству переоформленных тонн;
ISWEIGHT - считать хранение в:
0 - физическом весе,
1 - зачетном весе;
PRICEPERMONTH - стоимость хранения за:
0 - тонно/день,
1 - тонно/месяц (делится на количество дней в текущем месяце),
2 - тонно/месяц (всегда делится на 30).
Параметры:
CONSIDERSTORAGE - производить расчет хранения: если 0 - то не считать хранение,
если не 0 - считать.
*/
-- Бланки складских квитанций --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'шт.')
from SERVICES
where (SERVICES.SERVICEID = 10)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select count(STORAGETICKETS.STORAGETICKETID), count(STORAGETICKETS.STORAGETICKETID) * GET_CONTRACT_COSTS.COST,
QUALITY.CROPID, QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from STORAGETICKETS
left join QUALITY on (QUALITY.QUALITYID = STORAGETICKETS.QUALITYID)
left join GET_CONTRACT_COSTS(STORAGETICKETS.REGDATE, :CONTRACTID, 10, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (STORAGETICKETS.REGDATE between :DATE1 and :DATE2)
and (STORAGETICKETS.OWNERID = :OWNERID)
and (STORAGETICKETS.STATE = 0)
and (STORAGETICKETS.CONTRACTID = :CONTRACTID)
group by QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID, QUALITY.CLASSID, GET_CONTRACT_COSTS.COST,
GET_CONTRACT_COSTS.CONTRACTCOSTDATE, QUALITY_CROPFULLNAME.CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Приемка
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 5)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * INVOICEREGISTERS.INCOMECOST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
INVOICEREGISTERS.INCOMECOST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(0, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, SERVICEPRICE, IT.REGDATE, QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Сушка --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т/п')
from SERVICES
where (SERVICES.SERVICEID = 3)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
case
when (min(INVOICEREGISTERS.DRYQUANTITY) is null) then
(cast((coalesce(sum(IT.NET),0) * (min(coalesce(BUHQUALITY.HUMIDITY,0)) - min(coalesce(INVOICEREGISTERSQUALITY.HUMIDITY,0)))) as decimal(15,3)) / 1000)
else min(INVOICEREGISTERS.DRYQUANTITY)
end SERVICEVALUE,
IT.STORINGID STORINGID, BUHQUALITY.CROPID CROPID, BUHQUALITY.CLASSID CLASSID,
INVOICEREGISTERS.DRYCOST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(0, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) INVOICEREGISTERSQUALITY on (0=0)
left join QUALITY_CROPFULLNAME(BUHQUALITY.CROPID, BUHQUALITY.CLASSID, BUHQUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 3, BUHQUALITY.CROPID, BUHQUALITY.CLASSID) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
and ((coalesce(BUHQUALITY.HUMIDITY,0) - coalesce(INVOICEREGISTERSQUALITY.HUMIDITY,0) > 0)
and (coalesce(BUHQUALITY.HUMIDITY,0) > coalesce(GET_CONTRACT_COSTS.HUMB,0)))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, BUHQUALITY.CROPID,
BUHQUALITY.CLASSID, INVOICEREGISTERS.DRYCOST, IT.REGDATE, IT.REGISTERID,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Вентилирование --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.DISPLAYREGISTER,0),
coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 24)
into :ASERVICENAME, :ASERVICECODE, :DISPLAYREGISTER, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
min(INVOICEREGISTERS.DRYQUANTITY) SERVICEVALUE,
IT.STORINGID STORINGID, BUHQUALITY.CROPID CROPID, BUHQUALITY.CLASSID CLASSID,
INVOICEREGISTERS.DRYCOST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(0, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) INVOICEREGISTERSQUALITY on (0=0)
left join QUALITY_CROPFULLNAME(BUHQUALITY.CROPID, BUHQUALITY.CLASSID, BUHQUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 3, BUHQUALITY.CROPID, BUHQUALITY.CLASSID) on (0=0)
where (:DISPLAYREGISTER <> 0) and (IT.REGDATE between :DATE1 and :DATE2)
and (IT.OPERATIONID = 1) and (IT.NET > 0) and (IT.OWNERID = :OWNERID)
and (IT.CONTRACTID = :CONTRACTID) and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
and ((coalesce(BUHQUALITY.HUMIDITY,0) - coalesce(INVOICEREGISTERSQUALITY.HUMIDITY,0) > 0)
and (coalesce(BUHQUALITY.HUMIDITY,0) <= coalesce(GET_CONTRACT_COSTS.HUMB,0)))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, BUHQUALITY.CROPID,
BUHQUALITY.CLASSID, INVOICEREGISTERS.DRYCOST, IT.REGDATE, IT.REGISTERID,
QUALITY_CROPFULLNAME.CODE_KULTURA
union
select
cast(UWORDERS.WEIGHT as numeric(12,3)) / 1000 SERVICEVALUE,
UWORDERS.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, UWORDERS.UWORDERDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from UWORDERS
left join UWACTS on (UWACTS.UWORDERID = UWORDERS.UWORDERID)
left join QUALITY on (QUALITY.QUALITYID = UWORDERS.BEFOREQUALITYID)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(UWORDERS.UWORDERDATE, :CONTRACTID, 24, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
where (:DISPLAYREGISTER = 0) and (UWACTS.UWACTDATE between :DATE1 and :DATE2)
and (UWORDERS.UWTYPEID = 5) and (UWORDERS.CLIENTID = :OWNERID)
and (UWORDERS.CONTRACTID = :CONTRACTID) and (UWORDERS.STATE = 0)
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Очистка --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т/п')
from SERVICES
where (SERVICES.SERVICEID = 4)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
case
when (min(INVOICEREGISTERS.CLEARQUANTITY) is null) then
case
when (min(coalesce(INVOICEREGISTERSQUALITY.DIRT,0)) > 0) then
(cast((coalesce(sum(IT.NET),0) * (min(coalesce(BUHQUALITY.DIRT,0)) - min(coalesce(INVOICEREGISTERSQUALITY.DIRT,0)))) as decimal(15,3)) / 1000)
end
else min(INVOICEREGISTERS.CLEARQUANTITY)
end SERVICEVALUE,
IT.STORINGID STORINGID, BUHQUALITY.CROPID CROPID, BUHQUALITY.CLASSID CLASSID,
INVOICEREGISTERS.UNDERWORKCOST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(0, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) INVOICEREGISTERSQUALITY on (0=0)
left join QUALITY_CROPFULLNAME(BUHQUALITY.CROPID, BUHQUALITY.CLASSID, BUHQUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 4, BUHQUALITY.CROPID, BUHQUALITY.CLASSID) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
and ((coalesce(BUHQUALITY.DIRT,0) - coalesce(INVOICEREGISTERSQUALITY.DIRT,0) > 0)
or (coalesce(coalesce(BUHQUALITY.SCREENING,BUHQUALITY.OILSEED),0) - coalesce(INVOICEREGISTERSQUALITY.SCREENING,0) > 0))
and (coalesce(GET_CONTRACT_COSTS.HUMB,100) >= coalesce(BUHQUALITY.HUMIDITY,0))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, BUHQUALITY.CROPID,
BUHQUALITY.CLASSID, INVOICEREGISTERS.UNDERWORKCOST, IT.REGDATE, IT.REGISTERID,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Очистка при большой влажности --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т/п')
from SERVICES
where (SERVICES.SERVICEID = 26)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
case
when (min(INVOICEREGISTERS.CLEARQUANTITY) is null) then
(cast((coalesce(sum(IT.NET),0) * (min(coalesce(BUHQUALITY.DIRT,0)) - min(coalesce(INVOICEREGISTERSQUALITY.DIRT,0)))) as decimal(15,3)) / 1000)
else min(INVOICEREGISTERS.CLEARQUANTITY)
end SERVICEVALUE,
IT.STORINGID STORINGID, BUHQUALITY.CROPID CROPID, BUHQUALITY.CLASSID CLASSID,
INVOICEREGISTERS.UNDERWORKCOST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) INVOICEREGISTERSQUALITY on (0=0)
left join QUALITY_CROPFULLNAME(BUHQUALITY.CROPID, BUHQUALITY.CLASSID, BUHQUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 26, BUHQUALITY.CROPID, BUHQUALITY.CLASSID) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
and ((coalesce(BUHQUALITY.DIRT,0) - coalesce(INVOICEREGISTERSQUALITY.DIRT,0) > 0)
or (coalesce(coalesce(BUHQUALITY.SCREENING,BUHQUALITY.OILSEED),0) - coalesce(INVOICEREGISTERSQUALITY.SCREENING,0) > 0))
and (coalesce(GET_CONTRACT_COSTS.HUMB,100) < coalesce(BUHQUALITY.HUMIDITY,0))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, BUHQUALITY.CROPID,
BUHQUALITY.CLASSID, INVOICEREGISTERS.UNDERWORKCOST, IT.REGDATE, IT.REGISTERID,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Очистка от амброзии --
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 35)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
coalesce(sum(cast(IT.NET as numeric(12,3))) / 1000,0) SERVICEVALUE,
IT.STORINGID STORINGID, BUHQUALITY.CROPID CROPID, BUHQUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, IT.REGDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) INVOICEREGISTERSQUALITY on (0=0)
left join QUALITY_CROPFULLNAME(BUHQUALITY.CROPID, BUHQUALITY.CLASSID, BUHQUALITY.SORTID, 1) on (0=0)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 35, BUHQUALITY.CROPID, BUHQUALITY.CLASSID) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
and (coalesce(BUHQUALITY.AMBROZIA,0) > 0)
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, BUHQUALITY.CROPID,
BUHQUALITY.CLASSID, SERVICEPRICE, IT.REGDATE, IT.REGISTERID,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Переоформление
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0),
coalesce(SERVICES.MEASUREMENTUNIT,case
when (:CONSIDERRENEWALSWEIGHT = 0) then
'шт.'
else 'т'
end)
from SERVICES
where (SERVICES.SERVICEID = 6)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select SERVICEVALUE,
cast((SERVICEVALUE * SERVICEPRICE) as decimal(15,2)) SERVICESUM,
CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select
case
when (:CONSIDERRENEWALSWEIGHT = 0) then
count(IT.INVENTORYID)
else cast(sum(-IT.NET) as decimal(15,3)) / 1000
end SERVICEVALUE,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 6, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 17) and (IT.NET < 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
IT.REGISTERID, QUALITY_CROPFULLNAME.CODE_KULTURA
)
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Анализ на токсичность
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 18)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 18, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.WEIGHT > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Анализ на глюкозинолаты и эруковую кислоту
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 19)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 19, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Анализ на масличность
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 20)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 20, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Анализ на масличность и протеин
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 21)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 21, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Анализ на ГМО
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 28)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, max(SERVICEDATE), CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 28, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 1) and (IT.NET > 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID,
QUALITY.CLASSID, GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Отгрузка на автомобильный транспорт
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 7)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(0, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 7, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 5)
and (TRANSPORTTYPE.TRANSPORTSUBTYPE = 0)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID, QUALITY.CLASSID,
GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE, IT.REGDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if ((SERVICEVALUE > 0) and (SERVICESUM > 0)) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Отгрузка на железнодорожный транспорт
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'т')
from SERVICES
where (SERVICES.SERVICEID = 8)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select (cast(coalesce(sum(IT.NET),0) as decimal(15,3)) / 1000) SERVICEVALUE,
cast((cast(coalesce(sum(IT.NET),0) as decimal(15,5)) * GET_CONTRACT_COSTS.COST / 1000) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 8, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 5)
and (TRANSPORTTYPE.TRANSPORTSUBTYPE = 1)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID, QUALITY.CLASSID,
GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE, IT.REGDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Использование железнодорожных путей
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'шт.')
from SERVICES
where (SERVICES.SERVICEID = 11)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select count(IT.INVENTORYID) SERVICEVALUE,
cast((count(IT.INVENTORYID) * GET_CONTRACT_COSTS.COST) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 11, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 5)
and (IT.NET > 0) and (TRANSPORTTYPE.TRANSPORTSUBTYPE = 1)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID, QUALITY.CLASSID,
GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE, IT.REGDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Взвешивание железнодорожных вагонов
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0), coalesce(SERVICES.MEASUREMENTUNIT,'шт.')
from SERVICES
where (SERVICES.SERVICEID = 12)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then begin
for
select sum(SERVICEVALUE), sum(SERVICESUM), CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CROPCLASSNAME, CODE_KULTURA
from (
select count(IT.INVENTORYID) SERVICEVALUE,
cast((count(IT.INVENTORYID) * GET_CONTRACT_COSTS.COST) as decimal(15,2)) SERVICESUM,
IT.STORINGID STORINGID, QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID,
GET_CONTRACT_COSTS.COST SERVICEPRICE, GET_CONTRACT_COSTS.CONTRACTCOSTDATE SERVICEDATE,
QUALITY_CROPFULLNAME.CROPFULLNAME CROPCLASSNAME, QUALITY_CROPFULLNAME.CODE_KULTURA
from IT
join GETWORKPARTIES(1, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
left join GET_CONTRACT_COSTS(IT.REGDATE, :CONTRACTID, 12, QUALITY.CROPID, QUALITY.CLASSID) on (0=0)
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
where (IT.REGDATE between :DATE1 and :DATE2) and (IT.OPERATIONID = 5)
and (IT.NET > 0) and (TRANSPORTTYPE.TRANSPORTSUBTYPE = 1)
and (IT.OWNERID = :OWNERID) and (IT.CONTRACTID = :CONTRACTID)
and ((IT.STATE >= 0) and (IT.PARENTID <> -1))
group by IT.STORINGID, QUALITY_CROPFULLNAME.CROPFULLNAME, QUALITY.CROPID, QUALITY.CLASSID,
GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE, IT.REGDATE,
QUALITY_CROPFULLNAME.CODE_KULTURA
)
group by CROPCLASSNAME, CROPID, CLASSID, SERVICEPRICE, SERVICEDATE, CODE_KULTURA
into :SERVICEVALUE, :SERVICESUM, :CROPID, :CLASSID, :SERVICEPRICE, :SERVICEDATE, :CROPCLASSNAME, :CODE_KULTURA
do
if (SERVICEVALUE > 0) then begin
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
-- Хранение
select SERVICES.SERVICEFULLNAME, coalesce(SERVICES.CODEIN1C,0),
coalesce(SERVICES.MEASUREMENTUNIT,case
when (:PRICEPERMONTH = 0) then
'т/день'
else 'т/місяць'
end)
from SERVICES
where (SERVICES.SERVICEID = 1)
into :ASERVICENAME, :ASERVICECODE, :AMEASUREMENTUNIT;
if (:ASERVICECODE <> 0) then
for
select QUALITY.CROPID, QUALITY.CLASSID
from CONTRACTCOSTS
left join QUALITY on (QUALITY.QUALITYID = CONTRACTCOSTS.QUALITYID)
left join PARTIES on ((PARTIES.CROPID = QUALITY.CROPID)
and ((PARTIES.CLASSID = QUALITY.CLASSID)
or (PARTIES.CLASSID is null)))
where (CONTRACTCOSTS.CONTRACTID = -1) and (CONTRACTCOSTS.SERVICEID = 1)
and (PARTIES.STATE = 1)
group by QUALITY.CROPID, QUALITY.CLASSID
into :ACROPID, :ACLASSID
do begin
SERVICEVALUE = 0;
SERVICESUM = 0;
for
select max(COSTSCALCULATION.STORING_WEIGHT), COSTSCALCULATION.STORAGECOST, COSTSCALCULATION.CONTRACTCOSTDATE,
COSTSCALCULATION.CROPID, COSTSCALCULATION.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME
from COSTSCALCULATION(:OWNERID, :ACLASSID, :DATE1, :DATE2, :CONTRACTID, null,
case
when (:ACLASSID is not null) then
0
else 1
end, :ACROPID, :DATE2, :ISWEIGHT, :PRICEPERMONTH)
left join QUALITY_CROPFULLNAME(COSTSCALCULATION.CROPID, COSTSCALCULATION.CLASSID, null, 1) on (0=0)
group by COSTSCALCULATION.STORAGECOST, COSTSCALCULATION.CONTRACTCOSTDATE,
COSTSCALCULATION.CROPID, COSTSCALCULATION.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME
order by COSTSCALCULATION.CONTRACTCOSTDATE
into :STORINGNET, :STORINGPRICE, :ASERVICEDATE, :CROPID, :CLASSID, :CROPCLASSNAME
do begin
if (STORINGNET > 0) then begin
-- SERVICEVALUE = round(STORINGNET * 1000 + 0.05) / 1000;
SERVICEVALUE = STORINGNET + 0.00005;
SERVICECODE = :ASERVICECODE || coalesce('_' || :CLASSID,'');
SERVICENAME = :ASERVICENAME || coalesce(' ' || CROPCLASSNAME, '');
SERVICEPRICE = :STORINGPRICE;
SERVICEDATE = :ASERVICEDATE;
SERVICESUM = :SERVICEVALUE * :STORINGPRICE;
UNIT = :AMEASUREMENTUNIT;
CODE_POSLUGA = :ASERVICECODE;
insert into GIVENSERVICES (CLIENTID, CONTRACTID, CROPID, CLASSID, USLUGA_COD, NAME, ED, KOL, PRICE, SUMMA, NODE)
values (:OWNERID, :CONTRACTID, :CROPID, :CLASSID, :SERVICECODE, :SERVICENAME, :UNIT, :SERVICEVALUE, :SERVICEPRICE, :SERVICESUM, :NODE);
suspend;
end
end
end
end
|
|