| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
UNDERWORK_CALCUWACT_PREV
| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
UNDERWORK_CALCUWACT_PREV
Входные параметры
Параметр | Тип | Описание |
|---|---|---|
METODSOLUTION |
INTEGER |
|
UWORDERID |
INTEGER |
|
CROPID |
INTEGER |
|
DEFAULTVALUE |
NUMERIC(4,2) |
|
HUMIDITYAFTERDRY |
NUMERIC(4,2) |
|
DIRTAFTERCLEAN |
NUMERIC(4,2) |
|
Выходные параметры
Параметр | Тип | Описание |
|---|---|---|
ALLWEIGHTBEFORECLEANING |
INTEGER |
|
SETTLEMENTWASTEWEIGHT |
INTEGER |
|
WASTEWEIGHT |
INTEGER |
|
ALLWEIGHTBEFOREDRYING |
INTEGER |
|
SHRINKAGEWEIGHT |
INTEGER |
|
SETTLEMENTSHRINKAGEWEIGHT |
INTEGER |
|
Описание
(Нет описания для процедуры UNDERWORK_CALCUWACT_PREV)
Определение
CREATE PROCEDURE UNDERWORK_CALCUWACT_PREV(
METODSOLUTION INTEGER,
UWORDERID INTEGER,
CROPID INTEGER,
DEFAULTVALUE NUMERIC(4,2),
HUMIDITYAFTERDRY NUMERIC(4,2),
DIRTAFTERCLEAN NUMERIC(4,2))
RETURNS (
ALLWEIGHTBEFORECLEANING INTEGER,
SETTLEMENTWASTEWEIGHT INTEGER,
WASTEWEIGHT INTEGER,
ALLWEIGHTBEFOREDRYING INTEGER,
SHRINKAGEWEIGHT INTEGER,
SETTLEMENTSHRINKAGEWEIGHT INTEGER)
AS
begin
if (:METODSOLUTION in (43,143)) then begin
select
sum(NET) NET, sum(DIRTWEIGHT),
sum(cast(cast(100 * (cast(DIRT as numeric (15,4)) - cast(:DIRTAFTERCLEAN as numeric(4,2))) / (100 - cast(:DIRTAFTERCLEAN as numeric(4,2))) as numeric(15,4)) * NET / 1000 as integer) * 10)
from (
select
sum(NET) NET, sum(DIRTWEIGHT) DIRTWEIGHT,
cast(cast(100 * sum(cast(NET * DIRT / 100 as integer)) as numeric (15,4)) / sum(NET) as numeric (4,2)) DIRT
from (
select
sum(IT.NET) NET, min(LQUALITY.DIRT) DIRT, min(IT.OWNERID) OWNERID, sum(ITTRANSPORT.DIRTWEIGHT) DIRTWEIGHT
from IT
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join CROP on (CROP.CROPID = :CROPID)
where (IT.UWORDERID = :UWORDERID) and (ITTRANSPORT.DIRTWEIGHT > 0)
and (INVOICEREGISTERS.INVOICEREGISTERID is not null)
and ((IT.OPERATIONID = 1) and (IT.STATE > -1) and (IT.PARENTID <= 0))
group by IT.REGISTERID
order by 3, IT.REGISTERID
)
group by OWNERID
)
into :ALLWEIGHTBEFORECLEANING, :WASTEWEIGHT, :SETTLEMENTWASTEWEIGHT;
-- WASTEWEIGHT = round(SETTLEMENTWASTEWEIGHT / 10) * 10;
end
if (:METODSOLUTION in (44,144)) then begin
select
sum(WEIGHTBEFORECLEANING), sum(DIRTWEIGHT), sum(WASTEWEIGHT),
sum(WEIGHTBEFOREDRYING) - (sum(WASTEWEIGHT) - sum(DIRTWEIGHT)),
cast(((sum(WEIGHTBEFOREDRYING) - (sum(WASTEWEIGHT) - sum(DIRTWEIGHT))) *
cast(100 * (cast(sum(cast(WEIGHTBEFOREDRYING as numeric(14,4)) * HUMIDITYBEFOREDRY) /
(sum(WEIGHTBEFOREDRYING) - (sum(WASTEWEIGHT) - sum(DIRTWEIGHT))) as numeric(4,2)) -
cast(:HUMIDITYAFTERDRY as numeric(4,2))) /
(100 - cast(:HUMIDITYAFTERDRY as numeric(4,2)))
as numeric(4,2)) / 100) as integer)
from (
select
sum(case
when ((IT.OPERATIONID = 1) and (ITTRANSPORT.DIRTWEIGHT > 0)) then
IT.NET
end) WEIGHTBEFORECLEANING,
sum(case
when (IT.OPERATIONID = 1) then
ITTRANSPORT.DIRTWEIGHT
end) DIRTWEIGHT,
sum(case
when (IT.OPERATIONID = 5) then
IT.NET
end) WASTEWEIGHT,
sum(case
when ((IT.OPERATIONID = 1) and (ITTRANSPORT.HUMWEIGHT > 0)
and (LQUALITY.HUMIDITY > :DEFAULTVALUE)) then
IT.NET - coalesce(ITTRANSPORT.DIRTWEIGHT,0)
end) WEIGHTBEFOREDRYING,
min(case
when ((IT.OPERATIONID = 1) and (ITTRANSPORT.HUMWEIGHT > 0)
and (LQUALITY.HUMIDITY > :DEFAULTVALUE)) then
LQUALITY.HUMIDITY
end) HUMIDITYBEFOREDRY
from IT
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0 = 0)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join CROP on (CROP.CROPID = QUALITY.CROPID)
where (IT.UWORDERID = :UWORDERID)
and ((IT.NET > 0) and (IT.STATE > -1))
and (((IT.OPERATIONID = 1) and (CROP.CROPTYPEID <> 3))
or ((IT.OPERATIONID = 5) and (CROP.CROPTYPEID = 3)))
group by IT.REGISTERID
order by IT.REGISTERID
)
into :ALLWEIGHTBEFORECLEANING, :SETTLEMENTWASTEWEIGHT, :WASTEWEIGHT,
:ALLWEIGHTBEFOREDRYING, :SHRINKAGEWEIGHT;
end
if (:METODSOLUTION in (45,145,47,147,48)) then begin
select
NET,
case
when (:METODSOLUTION in (47,147)) then
cast(cast(cast(NET * cast((cast(DIRT as numeric(6,4))- cast(:DIRTAFTERCLEAN as numeric(4,2))) / (100 - cast(:DIRTAFTERCLEAN as numeric(4,2))) as numeric (15,4)) as integer) as numeric (15,1)) / 10 as integer) * 10
when (:METODSOLUTION in (48,148)) then
cast(cast(cast(NET * cast((cast(DIRT as numeric(6,4))- cast(:DIRTAFTERCLEAN as numeric(4,2))) / (100 - cast(:DIRTAFTERCLEAN as numeric(4,2))) as numeric (15,4)) as integer) as numeric (15,1)) / 20 as integer) * 20
else WASTEWEIGHT
end
from (
select
sum(NET) NET,
sum(WASTEWEIGHT) WASTEWEIGHT,
cast(cast(100 * sum(cast(NET * DIRT / 100 as integer)) as numeric (15,4)) / sum(NET) as numeric (4,2)) DIRT
from (
select
sum(NET) NET,
sum(WASTEWEIGHT) WASTEWEIGHT,
cast(cast(100 * sum(cast(NET * DIRT / 100 as integer)) as numeric (15,4)) / sum(NET) as numeric (4,2)) DIRT
from (
select
case
when (min(IT.OPERATIONID) = 1) then
sum(IT.NET)
end NET,
case
when (min(IT.OPERATIONID) = 5) then
sum(IT.NET)
end WASTEWEIGHT,
min(LQUALITY.DIRT) DIRT, min(IT.OWNERID) OWNERID, sum(ITTRANSPORT.DIRTWEIGHT) DIRTWEIGHT
from IT
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join CROP on (CROP.CROPID = :CROPID)
left join CROP WASTECROP on (WASTECROP.CROPID = LQUALITY.CROPID)
where (IT.UWORDERID = :UWORDERID) and (IT.STATE > -1)
and (((LQUALITY.DIRT > :DIRTAFTERCLEAN) and (INVOICEREGISTERS.INVOICEREGISTERID is not null)
and (IT.OPERATIONID = 1) and (IT.PARENTID <= 0) and (CROP.CROPTYPEID <> 3))
or ((IT.OPERATIONID = 5) and (WASTECROP.CROPTYPEID = 3)))
group by IT.REGISTERID
order by 3, IT.REGISTERID
)
group by OWNERID
)
)
into :ALLWEIGHTBEFORECLEANING, :SETTLEMENTWASTEWEIGHT;
select
sum(DIRTWEIGHT) DIRTWEIGHT
from (
select
sum(DIRTWEIGHT) DIRTWEIGHT
from (
select
case
when (DIRT > :DIRTAFTERCLEAN) then
case
when (:METODSOLUTION in (47,147)) then
cast(NET * cast((DIRT - cast(:DIRTAFTERCLEAN as numeric (4,2))) as numeric (15,2)) / (100 - cast(:DIRTAFTERCLEAN as numeric (4,2))) as integer)
else cast(NET * cast((DIRT - cast(:DIRTAFTERCLEAN as numeric (4,2))) as numeric (15,2)) / 100 as integer)
end
else 0
end DIRTWEIGHT,
OWNERID OWNERID
from (
select
sum(IT.NET) NET,
min(LQUALITY.DIRT) DIRT,
min(IT.OWNERID) OWNERID
from IT
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join CROP on (CROP.CROPID = :CROPID)
where (IT.UWORDERID = :UWORDERID) and (IT.REGISTERID is not null)
and ((IT.OPERATIONID = 1) and (IT.STATE > -1) and (IT.PARENTID <= 0))
group by IT.REGISTERID
order by 3, IT.REGISTERID
)
)
group by OWNERID
)
into :WASTEWEIGHT;
select
NET, SHRINKAGEWEIGHT,
cast(NET * cast((cast(HUMIDITY as numeric(6,4))- cast(:HUMIDITYAFTERDRY as numeric(4,2))) / (100 - cast(:HUMIDITYAFTERDRY as numeric(4,2))) as numeric (15,4)) as integer)
from (
select
sum(NET) NET, sum(SHRINKAGEWEIGHT) SHRINKAGEWEIGHT,
cast(100 * sum(HUMIDITY_CP) / sum(NET1) as numeric (4,2)) HUMIDITY
from (
select
NET1 NET1, NET NET,
cast((NET *
cast(100 * (HUMIDITY - cast(:HUMIDITYAFTERDRY as numeric(4,2))) /
(100 - cast(:HUMIDITYAFTERDRY as numeric(4,2)))
as numeric(4,2)) / 100) as integer) SHRINKAGEWEIGHT,
case
when (NET1 < 10000) then
cast(NET1 * cast(HUMIDITY as numeric (4,2)) / 100 as numeric (15,4))
else cast(cast(NET1 * cast(HUMIDITY as numeric (4,2)) / 100 as integer) as numeric (15,4))
end HUMIDITY_CP,
HUMIDITY HUMIDITY
from (
select
sum(NET) NET1,
case
when (:WASTEWEIGHT <> 0) then
cast(sum(NET - (cast(DIRTWEIGHT as numeric(18,8)) * cast(:SETTLEMENTWASTEWEIGHT as integer) / cast(:WASTEWEIGHT as integer))) as integer)
else sum(NET - coalesce(DIRTWEIGHT,0))
end NET,
cast(100 * sum(HUMIDITY_CP) / sum(NET) as numeric (4,2)) HUMIDITY
from (
select
NET NET,
case
when (DIRT > :DIRTAFTERCLEAN) then
case
when (:METODSOLUTION in (47,147)) then
cast(NET * cast((DIRT - cast(:DIRTAFTERCLEAN as numeric (4,2))) as numeric (15,2)) / (100 - cast(:DIRTAFTERCLEAN as numeric (4,2))) as integer)
else cast(NET * cast((DIRT - cast(:DIRTAFTERCLEAN as numeric (4,2))) as numeric (15,2)) / 100 as integer)
end
else 0
end DIRTWEIGHT,
case
when (NET < 10000) then
cast(NET * cast(HUMIDITY as numeric (4,2)) / 100 as numeric (15,4))
else cast(cast(NET * cast(HUMIDITY as numeric (4,2)) / 100 as integer) as numeric (15,4))
end HUMIDITY_CP,
HUMIDITY HUMIDITY,
OWNERID OWNERID
from (
select
sum(IT.NET) NET,
min(LQUALITY.DIRT) DIRT,
min(LQUALITY.HUMIDITY) HUMIDITY,
min(IT.OWNERID) OWNERID
from IT
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join CROP on (CROP.CROPID = :CROPID)
where (IT.UWORDERID = :UWORDERID) and (LQUALITY.HUMIDITY > :DEFAULTVALUE)
and (IT.REGISTERID is not null)
and ((IT.OPERATIONID = 1) and (IT.STATE > -1) and (IT.PARENTID <= 0))
group by IT.REGISTERID
order by 3, IT.REGISTERID
)
)
group by OWNERID
)
)
)
into :ALLWEIGHTBEFOREDRYING, :SHRINKAGEWEIGHT, :SETTLEMENTSHRINKAGEWEIGHT;
end
if (:METODSOLUTION in (54,154)) then begin
select
NET,
cast(NET * cast((cast(HUMIDITY as numeric(6,4))- cast(:HUMIDITYAFTERDRY as numeric(4,2))) / (100 - cast(:HUMIDITYAFTERDRY as numeric(4,2))) as numeric (15,4)) as integer),
HUMWEIGHT
from (
select
sum(NET) NET,
cast(cast(100 * sum(HUMIDITY_CP) as numeric (15,4)) / sum(NET) as numeric (4,2)) HUMIDITY,
sum(cast(NET * cast((cast(HUMIDITY as numeric(6,4))- cast(:HUMIDITYAFTERDRY as numeric(4,2))) / (100 - cast(:HUMIDITYAFTERDRY as numeric(4,2))) as numeric (15,4)) as integer)) HUMWEIGHT
from (
select
sum(NET) NET,
cast(cast(100 * sum(cast(NET * cast(HUMIDITY as numeric (4,1)) / 100 as integer)) as numeric (15,4)) / sum(NET) as numeric (4,2)) HUMIDITY,
sum(cast(NET * cast(HUMIDITY as numeric (4,1)) / 100 as integer)) HUMIDITY_CP
from (
select
sum(IT.NET) NET, min(LQUALITY.HUMIDITY) HUMIDITY, min(IT.OWNERID) OWNERID
from IT
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join CROP on (CROP.CROPID = :CROPID)
where (IT.UWORDERID = :UWORDERID) and (LQUALITY.HUMIDITY > :DEFAULTVALUE)
and (IT.REGISTERID is not null)
and ((IT.OPERATIONID = 1) and (IT.STATE > -1) and (IT.PARENTID <= 0))
group by IT.REGISTERID
order by 3, IT.REGISTERID
)
group by OWNERID
)
)
into :ALLWEIGHTBEFOREDRYING, :SETTLEMENTSHRINKAGEWEIGHT, :SHRINKAGEWEIGHT;
select
sum(case
when ((IT.OPERATIONID = 1) and (ITTRANSPORT.DIRTWEIGHT > 0)) then
IT.NET
end) WEIGHTBEFORECLEANING,
sum(case
when (IT.OPERATIONID = 1) then
ITTRANSPORT.DIRTWEIGHT
end) DIRTWEIGHT,
sum(case
when (IT.OPERATIONID = 5) then
IT.NET
end) WASTEWEIGHT
from IT
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0 = 0)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join CROP on (CROP.CROPID = QUALITY.CROPID)
where (IT.UWORDERID = :UWORDERID)
and ((IT.NET > 0) and (IT.STATE > -1))
and (((IT.OPERATIONID = 1) and (CROP.CROPTYPEID <> 3))
or ((IT.OPERATIONID = 5) and (CROP.CROPTYPEID = 3)))
into :ALLWEIGHTBEFORECLEANING, :SETTLEMENTWASTEWEIGHT, :WASTEWEIGHT;
end
suspend;
end
|
|