| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
FORM117_BOTTOM
| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
FORM117_BOTTOM
Входные параметры
Параметр | Тип | Описание |
|---|---|---|
BEGDATE |
DATE |
|
ENDDATE |
DATE |
|
STORAGEID |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
|---|---|---|
QNAME1 |
VARCHAR(20) |
|
BEFOREQUALITY |
VARCHAR(10) |
|
AFTERQUALITY |
VARCHAR(10) |
|
QNAME2 |
VARCHAR(20) |
|
SEMOLINA |
VARCHAR(10) |
|
MEALEXTRA |
VARCHAR(10) |
|
MEAL1 |
VARCHAR(10) |
|
MEAL2 |
VARCHAR(10) |
|
MEAL3 |
VARCHAR(10) |
|
ALLPRODUCTION |
VARCHAR(10) |
|
BRAN |
VARCHAR(10) |
|
FLAKES |
VARCHAR(10) |
|
MIDI |
VARCHAR(10) |
|
Описание
(Нет описания для процедуры FORM117_BOTTOM)
Определение
CREATE PROCEDURE FORM117_BOTTOM(
BEGDATE DATE,
ENDDATE DATE,
STORAGEID INTEGER)
RETURNS (
QNAME1 VARCHAR(20),
BEFOREQUALITY VARCHAR(10),
AFTERQUALITY VARCHAR(10),
QNAME2 VARCHAR(20),
SEMOLINA VARCHAR(10),
MEALEXTRA VARCHAR(10),
MEAL1 VARCHAR(10),
MEAL2 VARCHAR(10),
MEAL3 VARCHAR(10),
ALLPRODUCTION VARCHAR(10),
BRAN VARCHAR(10),
FLAKES VARCHAR(10),
MIDI VARCHAR(10))
AS
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE QTYPE INTEGER;
DECLARE VARIABLE QVALUE DOUBLE PRECISION;
DECLARE VARIABLE B_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE B_ASH NUMERIC(9,2);
DECLARE VARIABLE B_GRAINUNIT INTEGER;
DECLARE VARIABLE B_DIRT NUMERIC(9,2);
DECLARE VARIABLE B_MINERAL NUMERIC(9,2);
DECLARE VARIABLE B_SCREENING NUMERIC(9,2);
DECLARE VARIABLE B_SMALLGRAIN NUMERIC(9,2);
DECLARE VARIABLE B_POISON NUMERIC(9,2);
DECLARE VARIABLE B_MARAN NUMERIC(9,2);
DECLARE VARIABLE B_SINEGUZ NUMERIC(9,2);
DECLARE VARIABLE B_GLASSNESS INTEGER;
DECLARE VARIABLE B_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE A_DIRT NUMERIC(9,2);
DECLARE VARIABLE A_ASH NUMERIC(9,2);
DECLARE VARIABLE A_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE A_MINERAL NUMERIC(9,2);
DECLARE VARIABLE S_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE S_ASH NUMERIC(9,2);
DECLARE VARIABLE S_DIRT NUMERIC(9,2);
DECLARE VARIABLE S_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE S_WHITE NUMERIC(9,2);
DECLARE VARIABLE S_REST NUMERIC(9,2);
DECLARE VARIABLE S_PASS NUMERIC(9,2);
DECLARE VARIABLE S_V INTEGER;
DECLARE VARIABLE S_HD NUMERIC(9,2);
DECLARE VARIABLE ME_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE ME_ASH NUMERIC(9,2);
DECLARE VARIABLE ME_DIRT NUMERIC(9,2);
DECLARE VARIABLE ME_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE ME_WHITE NUMERIC(9,2);
DECLARE VARIABLE ME_REST NUMERIC(9,2);
DECLARE VARIABLE ME_PASS NUMERIC(9,2);
DECLARE VARIABLE ME_V INTEGER;
DECLARE VARIABLE ME_HD NUMERIC(9,2);
DECLARE VARIABLE M1_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE M1_ASH NUMERIC(9,2);
DECLARE VARIABLE M1_DIRT NUMERIC(9,2);
DECLARE VARIABLE M1_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE M1_WHITE NUMERIC(9,2);
DECLARE VARIABLE M1_REST NUMERIC(9,2);
DECLARE VARIABLE M1_PASS NUMERIC(9,2);
DECLARE VARIABLE M1_V INTEGER;
DECLARE VARIABLE M1_HD NUMERIC(9,2);
DECLARE VARIABLE M2_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE M2_ASH NUMERIC(9,2);
DECLARE VARIABLE M2_DIRT NUMERIC(9,2);
DECLARE VARIABLE M2_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE M2_WHITE NUMERIC(9,2);
DECLARE VARIABLE M2_REST NUMERIC(9,2);
DECLARE VARIABLE M2_PASS NUMERIC(9,2);
DECLARE VARIABLE M2_V INTEGER;
DECLARE VARIABLE M2_HD NUMERIC(9,2);
DECLARE VARIABLE M3_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE M3_ASH NUMERIC(9,2);
DECLARE VARIABLE M3_DIRT NUMERIC(9,2);
DECLARE VARIABLE M3_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE M3_WHITE NUMERIC(9,2);
DECLARE VARIABLE M3_REST NUMERIC(9,2);
DECLARE VARIABLE M3_PASS NUMERIC(9,2);
DECLARE VARIABLE M3_V INTEGER;
DECLARE VARIABLE M3_HD NUMERIC(9,2);
DECLARE VARIABLE AP_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE AP_ASH NUMERIC(9,2);
DECLARE VARIABLE AP_DIRT NUMERIC(9,2);
DECLARE VARIABLE AP_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE AP_WHITE NUMERIC(9,2);
DECLARE VARIABLE AP_REST NUMERIC(9,2);
DECLARE VARIABLE AP_PASS NUMERIC(9,2);
DECLARE VARIABLE AP_V INTEGER;
DECLARE VARIABLE AP_HD NUMERIC(9,2);
DECLARE VARIABLE BR_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE BR_ASH NUMERIC(9,2);
DECLARE VARIABLE BR_DIRT NUMERIC(9,2);
DECLARE VARIABLE BR_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE BR_WHITE NUMERIC(9,2);
DECLARE VARIABLE BR_REST NUMERIC(9,2);
DECLARE VARIABLE BR_PASS NUMERIC(9,2);
DECLARE VARIABLE BR_V INTEGER;
DECLARE VARIABLE BR_HD NUMERIC(9,2);
DECLARE VARIABLE F_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE F_ASH NUMERIC(9,2);
DECLARE VARIABLE F_DIRT NUMERIC(9,2);
DECLARE VARIABLE F_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE F_WHITE NUMERIC(9,2);
DECLARE VARIABLE F_REST NUMERIC(9,2);
DECLARE VARIABLE F_PASS NUMERIC(9,2);
DECLARE VARIABLE F_V INTEGER;
DECLARE VARIABLE F_HD NUMERIC(9,2);
DECLARE VARIABLE MW_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE MW_ASH NUMERIC(9,2);
DECLARE VARIABLE MW_DIRT NUMERIC(9,2);
DECLARE VARIABLE MW_GLUTEN NUMERIC(9,1);
DECLARE VARIABLE MW_WHITE NUMERIC(9,2);
DECLARE VARIABLE MW_REST NUMERIC(9,2);
DECLARE VARIABLE MW_PASS NUMERIC(9,2);
DECLARE VARIABLE MW_V INTEGER;
DECLARE VARIABLE MW_HD NUMERIC(9,2);
begin
/* Определение качества до очистки*/
select
cast(Sum(quality_select.humidity * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.humidity - quality_select.humidity) as numeric(4,2)) humidity,
cast(Sum(quality_select.ash * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.ash - quality_select.ash) as numeric(4,2)) ash,
cast(Sum(quality_select.grainunit * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.grainunit - quality_select.grainunit) as integer) grainunit,
cast(Sum(quality_select.glassness * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.glassness - quality_select.glassness) as integer) glassness,
cast(Sum(quality_select.dirt * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.dirt - quality_select.dirt) as numeric(4,2)) dirt,
cast(Sum(quality_select.screening * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.screening - quality_select.screening) as numeric(4,2)) screening,
cast(Sum(quality_select.smallgrain * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.smallgrain - quality_select.smallgrain) as numeric(4,2)) smallgrain,
cast(Sum(quality_select.gluten * uwoutacts.weight) / Sum(nullif(uwoutacts.weight, 0) + quality_select.gluten - quality_select.gluten) as numeric(4,2)) gluten
from uwoutacts
left join quality_select(uwoutacts.afterqualityid) on (0 = 0)
left join recipes on (recipes.recipeid = uwoutacts.recipeid)
where uwoutacts.workingdate between :begdate and :enddate
and uwoutacts.state > -1
and recipes.storageid = :storageid
into :b_humidity, :b_ash, :b_grainunit, :b_glassness, :b_dirt, :b_screening, :b_smallgrain, :b_gluten;
/* Определение качества после очистки*/
for
select qualitydata.qualitytypeid, avg(qualitydata.qualityvalue)
from graderesults
inner join qualitydata on (graderesults.qualityaftercleanid = qualitydata.qualityid)
where graderesults.workingdate between :begdate and :enddate
and graderesults.storageid = :storageid
and graderesults.storingid = 3
group by qualitydata.qualitytypeid
into :qtype, :qvalue
do begin
if (:qtype = 2) then a_humidity = :qvalue;
if (:qtype = 11) then a_ash = :qvalue;
if (:qtype = 13) then a_dirt = :qvalue;
if (:qtype = 46) then a_mineral = :qvalue;
end
/* Определение качества продукции Мельзавод 1 */
if (:storageid = 3) then begin
for
select classid, qtype ,
cast(sum(qvalue * weight) / sum(weight + qvalue - qvalue) as numeric(7, 2))
from form117_quality(:begdate, :enddate, :storageid)
group by classid, qtype
into :classid, :qtype, :qvalue
do begin
if (:qtype = 2) then begin /* Влажность */
if (:classid = 44) then s_humidity = :qvalue;
if (:classid = 13) then me_humidity = :qvalue;
if (:classid = 14) then m1_humidity = :qvalue;
if (:classid = 15) then m2_humidity = :qvalue;
if (:classid = 65) then m3_humidity = :qvalue;
if (:classid = 46) then br_humidity = :qvalue;
if (:classid = 47) then f_humidity = :qvalue;
if (:classid = -1) then ap_humidity = :qvalue;
if (:classid = -2) then mw_humidity = :qvalue;
end
if (:qtype = 11) then begin /* Зольность*/
if (:classid = 44) then s_ash = :qvalue;
if (:classid = 13) then me_ash = :qvalue;
if (:classid = 14) then m1_ash = :qvalue;
if (:classid = 15) then m2_ash = :qvalue;
if (:classid = 65) then m3_ash = :qvalue;
if (:classid = 46) then br_ash = :qvalue;
if (:classid = 47) then f_ash = :qvalue;
if (:classid = -1) then ap_ash = :qvalue;
if (:classid = -2) then mw_ash = :qvalue;
end
if (:qtype = 13) then begin /* Сорная */
if (:classid = 44) then s_dirt = :qvalue;
if (:classid = 13) then me_dirt = :qvalue;
if (:classid = 14) then m1_dirt = :qvalue;
if (:classid = 15) then m2_dirt = :qvalue;
if (:classid = 65) then m3_dirt = :qvalue;
if (:classid = 46) then br_dirt = :qvalue;
if (:classid = 47) then f_dirt = :qvalue;
if (:classid = -1) then ap_dirt = :qvalue;
if (:classid = -2) then mw_dirt = :qvalue;
end
if (:qtype = 3) then begin /* Клейковина */
if (:classid = 44) then s_gluten = :qvalue;
if (:classid = 13) then me_gluten = :qvalue;
if (:classid = 14) then m1_gluten = :qvalue;
if (:classid = 15) then m2_gluten = :qvalue;
if (:classid = 65) then m3_gluten = :qvalue;
if (:classid = 46) then br_gluten = :qvalue;
if (:classid = 47) then f_gluten = :qvalue;
if (:classid = -1) then ap_gluten = :qvalue;
if (:classid = -2) then mw_gluten = :qvalue;
end
if (:qtype = 43) then begin /* Белизна */
if (:classid = 44) then s_white = :qvalue;
if (:classid = 13) then me_white = :qvalue;
if (:classid = 14) then m1_white = :qvalue;
if (:classid = 15) then m2_white = :qvalue;
if (:classid = 65) then m3_white = :qvalue;
if (:classid = 46) then br_white = :qvalue;
if (:classid = 47) then f_white = :qvalue;
if (:classid = -1) then ap_white = :qvalue;
if (:classid = -2) then mw_white = :qvalue;
end
if (:qtype = 44 or :qtype = 38 or :qtype = 37 or :qtype = 20) then begin /* Остаток на сите */
if (:classid = 44) then s_rest = :qvalue;
if (:classid = 13) then me_rest = :qvalue;
if (:classid = 14) then m1_rest = :qvalue;
if (:classid = 15) then m2_rest = :qvalue;
if (:classid = 65) then m3_rest = :qvalue;
if (:classid = 46) then br_rest = :qvalue;
if (:classid = 47) then f_rest = :qvalue;
if (:classid = -1) then ap_rest = :qvalue;
if (:classid = -2) then mw_rest = :qvalue;
end
if (:qtype = 21 or :qtype = 36 or :qtype = 45 or :qtype = 35) then begin /* Проход сита */
if (:classid = 44) then s_pass = :qvalue;
if (:classid = 13) then me_pass = :qvalue;
if (:classid = 14) then m1_pass = :qvalue;
if (:classid = 15) then m2_pass = :qvalue;
if (:classid = 65) then m3_pass = :qvalue;
if (:classid = 46) then br_pass = :qvalue;
if (:classid = 47) then f_pass = :qvalue;
if (:classid = -1) then ap_pass = :qvalue;
if (:classid = -2) then mw_pass = :qvalue;
end
if (:qtype = 22) then begin /* Обемный выход */
if (:classid = 44) then s_v = :qvalue;
if (:classid = 13) then me_v = :qvalue;
if (:classid = 14) then m1_v = :qvalue;
if (:classid = 15) then m2_v = :qvalue;
if (:classid = 65) then m3_v = :qvalue;
if (:classid = 46) then br_v = :qvalue;
if (:classid = 47) then f_v = :qvalue;
if (:classid = -1) then ap_v = :qvalue;
if (:classid = -2) then mw_v = :qvalue;
end
if (:qtype = 23) then begin /* Отношение H:D */
if (:classid = 44) then s_HD = :qvalue;
if (:classid = 13) then me_HD = :qvalue;
if (:classid = 14) then m1_HD = :qvalue;
if (:classid = 15) then m2_HD = :qvalue;
if (:classid = 65) then m3_HD = :qvalue;
if (:classid = 46) then br_HD = :qvalue;
if (:classid = 47) then f_HD = :qvalue;
if (:classid = -1) then ap_HD = :qvalue;
if (:classid = -2) then mw_HD = :qvalue;
end
end
end
/* Определение качества продукции Мельзавод 2*/
if (:storageid = 4) then begin
for
select gradeouts.classid, qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(nullif(gradeouts.weight, 0) + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :begdate and :enddate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :storageid
and gradeouts.classid not in (65, 66)
and qualitydata.qualitytypeid not in(4, 34)
and qualitytypes.categoryid in (0, 2)
and qualitytypes.usesubtypes = 0
group by gradeouts.classid, qualitydata.qualitytypeid
-- вот тут то и надо бы проверить какие типы качеств нужно
union
select cast(-1 as Integer), qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :BegDate and :EndDate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :StorageID
and qualitydata.qualitytypeid in (2, 11)
and gradeouts.classid in (13, 14, 15, 65, 44)
group by qualitydata.qualitytypeid
union
select cast(-2 as Integer), qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :BegDate and :EndDate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :StorageID
and qualitydata.qualitytypeid in (2, 11)
and gradeouts.classid in (13, 14, 15, 44, 46, 47)
group by qualitydata.qualitytypeid
into :classid, :qtype, :qvalue
do begin
if (:qtype = 2) then begin /* Влажность*/
if (:classid = 44) then s_humidity = :qvalue;
if (:classid = 13) then me_humidity = :qvalue;
if (:classid = 14) then m1_humidity = :qvalue;
if (:classid = 15) then m2_humidity = :qvalue;
if (:classid = 65) then m3_humidity = :qvalue;
if (:classid = 46) then br_humidity = :qvalue;
if (:classid = 47) then f_humidity = :qvalue;
if (:classid = -1) then ap_humidity = :qvalue;
if (:classid = -2) then mw_humidity = :qvalue;
end
if (:qtype = 11) then begin /* Зольность*/
if (:classid = 44) then s_ash = :qvalue;
if (:classid = 13) then me_ash = :qvalue;
if (:classid = 14) then m1_ash = :qvalue;
if (:classid = 15) then m2_ash = :qvalue;
if (:classid = 65) then m3_ash = :qvalue;
if (:classid = 46) then br_ash = :qvalue;
if (:classid = 47) then f_ash = :qvalue;
if (:classid = -1) then ap_ash = :qvalue;
if (:classid = -2) then mw_ash = :qvalue;
end
if (:qtype = 13) then begin /* Сорная */
if (:classid = 44) then s_dirt = :qvalue;
if (:classid = 13) then me_dirt = :qvalue;
if (:classid = 14) then m1_dirt = :qvalue;
if (:classid = 15) then m2_dirt = :qvalue;
if (:classid = 65) then m3_dirt = :qvalue;
if (:classid = 46) then br_dirt = :qvalue;
if (:classid = 47) then f_dirt = :qvalue;
if (:classid = -1) then ap_dirt = :qvalue;
if (:classid = -2) then mw_dirt = :qvalue;
end
if (:qtype = 3) then begin /* Клейковина */
if (:classid = 44) then s_gluten = :qvalue;
if (:classid = 13) then me_gluten = :qvalue;
if (:classid = 14) then m1_gluten = :qvalue;
if (:classid = 15) then m2_gluten = :qvalue;
if (:classid = 65) then m3_gluten = :qvalue;
if (:classid = 46) then br_gluten = :qvalue;
if (:classid = 47) then f_gluten = :qvalue;
if (:classid = -1) then ap_gluten = :qvalue;
if (:classid = -2) then mw_gluten = :qvalue;
end
if (:qtype = 43) then begin /* Белизна */
if (:classid = 44) then s_white = :qvalue;
if (:classid = 13) then me_white = :qvalue;
if (:classid = 14) then m1_white = :qvalue;
if (:classid = 15) then m2_white = :qvalue;
if (:classid = 65) then m3_white = :qvalue;
if (:classid = 46) then br_white = :qvalue;
if (:classid = 47) then f_white = :qvalue;
if (:classid = -1) then ap_white = :qvalue;
if (:classid = -2) then mw_white = :qvalue;
end
if (:qtype = 44 or :qtype = 38 or :qtype = 37 or :qtype = 20) then begin /* Остаток на сите */
if (:classid = 44) then s_rest = :qvalue;
if (:classid = 13) then me_rest = :qvalue;
if (:classid = 14) then m1_rest = :qvalue;
if (:classid = 15) then m2_rest = :qvalue;
if (:classid = 65) then m3_rest = :qvalue;
if (:classid = 46) then br_rest = :qvalue;
if (:classid = 47) then f_rest = :qvalue;
if (:classid = -1) then ap_rest = :qvalue;
if (:classid = -2) then mw_rest = :qvalue;
end
if (:qtype = 21 or :qtype = 36 or :qtype = 45 or :qtype = 35) then begin /* Проход сита */
if (:classid = 44) then s_pass = :qvalue;
if (:classid = 13) then me_pass = :qvalue;
if (:classid = 14) then m1_pass = :qvalue;
if (:classid = 15) then m2_pass = :qvalue;
if (:classid = 65) then m3_pass = :qvalue;
if (:classid = 46) then br_pass = :qvalue;
if (:classid = 47) then f_pass = :qvalue;
if (:classid = -1) then ap_pass = :qvalue;
if (:classid = -2) then mw_pass = :qvalue;
end
if (:qtype = 22) then begin /* Обемный выход */
if (:classid = 44) then s_v = :qvalue;
if (:classid = 13) then me_v = :qvalue;
if (:classid = 14) then m1_v = :qvalue;
if (:classid = 15) then m2_v = :qvalue;
if (:classid = 65) then m3_v = :qvalue;
if (:classid = 46) then br_v = :qvalue;
if (:classid = 47) then f_v = :qvalue;
if (:classid = -1) then ap_v = :qvalue;
if (:classid = -2) then mw_v = :qvalue;
end
if (:qtype = 23) then begin /* Отношение H:D */
if (:classid = 44) then s_HD = :qvalue;
if (:classid = 13) then me_HD = :qvalue;
if (:classid = 14) then m1_HD = :qvalue;
if (:classid = 15) then m2_HD = :qvalue;
if (:classid = 65) then m3_HD = :qvalue;
if (:classid = 46) then br_HD = :qvalue;
if (:classid = 47) then f_HD = :qvalue;
if (:classid = -1) then ap_HD = :qvalue;
if (:classid = -2) then mw_HD = :qvalue;
end
end
end
/* Определение качества продукции Мельзавод 3*/
if (:storageid = 5) then begin
for
select gradeouts.classid, qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :begdate and :enddate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :storageid
and gradeouts.classid not in(13, 14, 15, 66)
and qualitydata.qualitytypeid not in(4, 34)
and qualitytypes.categoryid in (0, 2)
and qualitytypes.usesubtypes = 0
group by gradeouts.classid, qualitydata.qualitytypeid
union
select cast(-1 as Integer), qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :BegDate and :EndDate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :StorageID
and gradeouts.classid in (13, 14, 15, 65, 44)
and qualitydata.qualitytypeid in (2, 11)
group by qualitydata.qualitytypeid
union
select cast(-2 as Integer), qualitydata.qualitytypeid,
cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2))
from graderesults
left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
where
((graderesults.workingdate between :BegDate and :EndDate
and graderesults.storingid = 3
)
or graderesults.graderesultid < -1
)
and graderesults.storageid = :StorageID
and gradeouts.classid in (13, 14, 15, 65, 44, 46)
and qualitydata.qualitytypeid in (2, 11)
group by qualitydata.qualitytypeid
into :classid, :qtype, :qvalue
do begin
if (:qtype = 2) then begin /* Влажность*/
if (:classid = 44) then s_humidity = :qvalue;
if (:classid = 13) then me_humidity = :qvalue;
if (:classid = 14) then m1_humidity = :qvalue;
if (:classid = 15) then m2_humidity = :qvalue;
if (:classid = 65) then m3_humidity = :qvalue;
if (:classid = 46) then br_humidity = :qvalue;
if (:classid = 47) then f_humidity = :qvalue;
if (:classid = -1) then ap_humidity = :qvalue;
if (:classid = -2) then mw_humidity = :qvalue;
end
if (:qtype = 11) then begin /* Зольность*/
if (:classid = 44) then s_ash = :qvalue;
if (:classid = 13) then me_ash = :qvalue;
if (:classid = 14) then m1_ash = :qvalue;
if (:classid = 15) then m2_ash = :qvalue;
if (:classid = 65) then m3_ash = :qvalue;
if (:classid = 46) then br_ash = :qvalue;
if (:classid = 47) then f_ash = :qvalue;
if (:classid = -1) then ap_ash = :qvalue;
if (:classid = -2) then mw_ash = :qvalue;
end
if (:qtype = 13) then begin /* Сорная */
if (:classid = 44) then s_dirt = :qvalue;
if (:classid = 13) then me_dirt = :qvalue;
if (:classid = 14) then m1_dirt = :qvalue;
if (:classid = 15) then m2_dirt = :qvalue;
if (:classid = 65) then m3_dirt = :qvalue;
if (:classid = 46) then br_dirt = :qvalue;
if (:classid = 47) then f_dirt = :qvalue;
if (:classid = -1) then ap_dirt = :qvalue;
if (:classid = -2) then mw_dirt = :qvalue;
end
if (:qtype = 3) then begin /* Клейковина */
if (:classid = 44) then s_gluten = :qvalue;
if (:classid = 13) then me_gluten = :qvalue;
if (:classid = 14) then m1_gluten = :qvalue;
if (:classid = 15) then m2_gluten = :qvalue;
if (:classid = 65) then m3_gluten = :qvalue;
if (:classid = 46) then br_gluten = :qvalue;
if (:classid = 47) then f_gluten = :qvalue;
if (:classid = -1) then ap_gluten = :qvalue;
if (:classid = -2) then mw_gluten = :qvalue;
end
if (:qtype = 43) then begin /* Белизна */
if (:classid = 44) then s_white = :qvalue;
if (:classid = 13) then me_white = :qvalue;
if (:classid = 14) then m1_white = :qvalue;
if (:classid = 15) then m2_white = :qvalue;
if (:classid = 65) then m3_white = :qvalue;
if (:classid = 46) then br_white = :qvalue;
if (:classid = 47) then f_white = :qvalue;
if (:classid = -1) then ap_white = :qvalue;
if (:classid = -2) then mw_white = :qvalue;
end
if (:qtype = 44 or :qtype = 38 or :qtype = 37 or :qtype = 20) then begin /* Остаток на сите */
if (:classid = 44) then s_rest = :qvalue;
if (:classid = 13) then me_rest = :qvalue;
if (:classid = 14) then m1_rest = :qvalue;
if (:classid = 15) then m2_rest = :qvalue;
if (:classid = 65) then m3_rest = :qvalue;
if (:classid = 46) then br_rest = :qvalue;
if (:classid = 47) then f_rest = :qvalue;
if (:classid = -1) then ap_rest = :qvalue;
if (:classid = -2) then mw_rest = :qvalue;
end
if (:qtype = 21 or :qtype = 36 or :qtype = 45 or :qtype = 35) then begin /* Проход сита */
if (:classid = 44) then s_pass = :qvalue;
if (:classid = 13) then me_pass = :qvalue;
if (:classid = 14) then m1_pass = :qvalue;
if (:classid = 15) then m2_pass = :qvalue;
if (:classid = 65) then m3_pass = :qvalue;
if (:classid = 46) then br_pass = :qvalue;
if (:classid = 47) then f_pass = :qvalue;
if (:classid = -1) then ap_pass = :qvalue;
if (:classid = -2) then mw_pass = :qvalue;
end
if (:qtype = 22) then begin /* Обемный выход */
if (:classid = 44) then s_v = :qvalue;
if (:classid = 13) then me_v = :qvalue;
if (:classid = 14) then m1_v = :qvalue;
if (:classid = 15) then m2_v = :qvalue;
if (:classid = 65) then m3_v = :qvalue;
if (:classid = 46) then br_v = :qvalue;
if (:classid = 47) then f_v = :qvalue;
if (:classid = -1) then ap_v = :qvalue;
if (:classid = -2) then mw_v = :qvalue;
end
if (:qtype = 23) then begin /* Отношение H:D */
if (:classid = 44) then s_HD = :qvalue;
if (:classid = 13) then me_HD = :qvalue;
if (:classid = 14) then m1_HD = :qvalue;
if (:classid = 15) then m2_HD = :qvalue;
if (:classid = 65) then m3_HD = :qvalue;
if (:classid = 46) then br_HD = :qvalue;
if (:classid = 47) then f_HD = :qvalue;
if (:classid = -1) then ap_HD = :qvalue;
if (:classid = -2) then mw_HD = :qvalue;
end
end
end
/* Вывод данных */
QNAME1 = 'Влажность';
BEFOREQUALITY = :b_humidity;
AFTERQUALITY = :a_humidity;
QNAME2 = 'Влажность';
SEMOLINA = :s_humidity;
MEALEXTRA = :me_humidity;
MEAL1 = :m1_humidity;
MEAL2 = :m2_humidity;
MEAL3 = :m3_humidity;
ALLPRODUCTION = :ap_humidity;
BRAN = :br_humidity;
FLAKES = :f_humidity;
MIDI = :mw_humidity;
suspend;
QNAME1 = 'Зольность';
BEFOREQUALITY = :b_ash;
AFTERQUALITY = :a_ash;
QNAME2 = 'Зольность';
SEMOLINA = :s_ash;
MEALEXTRA = :me_ash;
MEAL1 = :m1_ash;
MEAL2 = :m2_ash;
MEAL3 = :m3_ash;
ALLPRODUCTION = :ap_ash;
BRAN = :br_ash;
FLAKES = :f_ash;
MIDI = :mw_ash;
suspend;
QNAME1 = 'Натура';
BEFOREQUALITY = cast(:b_grainunit as Integer);
AFTERQUALITY = null;
QNAME2 = 'Сорн.прим.';
SEMOLINA = :s_dirt;
MEALEXTRA = :me_dirt;
MEAL1 = :m1_dirt;
MEAL2 = :m2_dirt;
MEAL3 = :m3_dirt;
ALLPRODUCTION = :ap_dirt;
BRAN = :br_dirt;
FLAKES = :f_dirt;
MIDI = :mw_dirt;
suspend;
QNAME1 = 'Сорн.прим.';
BEFOREQUALITY = :b_dirt;
AFTERQUALITY = :a_dirt;
QNAME2 = 'Клейковина';
SEMOLINA = :s_gluten;
MEALEXTRA = :me_gluten;
MEAL1 = :m1_gluten;
MEAL2 = :m2_gluten;
MEAL3 = :m3_gluten;
ALLPRODUCTION = :ap_gluten;
BRAN = :br_gluten;
FLAKES = :f_gluten;
MIDI = :mw_gluten;
suspend;
QNAME1 = 'Минер.прим.';
BEFOREQUALITY = :b_mineral;
AFTERQUALITY = :a_mineral;
QNAME2 = 'Белизна';
SEMOLINA = :s_white;
MEALEXTRA = :me_white;
MEAL1 = :m1_white;
MEAL2 = :m2_white;
MEAL3 = :m3_white;
ALLPRODUCTION = :ap_white;
BRAN = :br_white;
FLAKES = :f_white;
MIDI = :mw_white;
suspend;
QNAME1 = 'Зерн.прим.';
BEFOREQUALITY = :b_screening;
AFTERQUALITY = null;
QNAME2 = 'Остаток на сите';
SEMOLINA = :s_rest;
MEALEXTRA = :me_rest;
MEAL1 = :m1_rest;
MEAL2 = :m2_rest;
MEAL3 = :m3_rest;
ALLPRODUCTION = :ap_rest;
BRAN = :br_rest;
FLAKES = :f_rest;
MIDI = :mw_rest;
suspend;
QNAME1 = 'Мелк.зерно';
BEFOREQUALITY = :b_smallgrain;
AFTERQUALITY = null;
QNAME2 = 'Проход сита';
SEMOLINA = :s_pass;
MEALEXTRA = :me_pass;
MEAL1 = :m1_pass;
MEAL2 = :m2_pass;
MEAL3 = :m3_pass;
ALLPRODUCTION = :ap_pass;
BRAN = :br_pass;
FLAKES = :f_pass;
MIDI = :mw_pass;
suspend;
QNAME1 = 'Вредн.прим.';
BEFOREQUALITY = :b_poison;
AFTERQUALITY = null;
QNAME2 = 'Об.вых.хлеба';
SEMOLINA = :s_v;
MEALEXTRA = :me_v;
MEAL1 = :m1_v;
MEAL2 = :m2_v;
MEAL3 = :m3_v;
ALLPRODUCTION = :ap_v;
BRAN = :br_v;
FLAKES = :f_v;
MIDI = :mw_v;
suspend;
QNAME1 = 'Мараных';
BEFOREQUALITY = :b_maran;
AFTERQUALITY = null;
QNAME2 = 'Отношение H:D';
SEMOLINA = :s_hd;
MEALEXTRA = :me_hd;
MEAL1 = :m1_hd;
MEAL2 = :m2_hd;
MEAL3 = :m3_hd;
ALLPRODUCTION = :ap_hd;
BRAN = :br_hd;
FLAKES = :f_hd;
MIDI = :mw_hd;
suspend;
QNAME1 = 'Синегузоч.';
BEFOREQUALITY = :b_sineguz;
AFTERQUALITY = null;
QNAME2 = 'Металлоприм.';
SEMOLINA = 'След.';
MEALEXTRA = 'След.';
MEAL1 = 'След.';
MEAL2 = 'След.';
MEAL3 = 'След.';
ALLPRODUCTION = null;
BRAN = 'След.';
FLAKES = 'След.';
MIDI = null;
suspend;
QNAME1 = 'Стекловидн.';
BEFOREQUALITY = :b_glassness;
AFTERQUALITY = null;
QNAME2 = 'Цвет';
SEMOLINA = 'Соот.';
MEALEXTRA = 'Соот.';
MEAL1 = 'Соот.';
MEAL2 = 'Соот.';
MEAL3 = 'Соот.';
ALLPRODUCTION = null;
BRAN = 'Соот.';
FLAKES = 'Соот.';
MIDI = null;
suspend;
QNAME1 = 'Клейков.';
BEFOREQUALITY = :b_gluten;
AFTERQUALITY = null;
QNAME2 = 'Вкус';
SEMOLINA = 'Свойст.';
MEALEXTRA = 'Свойст.';
MEAL1 = 'Свойст.';
MEAL2 = 'Свойст.';
MEAL3 = 'Свойст.';
ALLPRODUCTION = null;
BRAN = 'Свойст.';
FLAKES = 'Свойст.';
MIDI = null;
suspend;
QNAME1 = null;
BEFOREQUALITY = null;
AFTERQUALITY = null;
QNAME2 = 'Запах';
SEMOLINA = 'Свойст.';
MEALEXTRA = 'Свойст.';
MEAL1 = 'Свойст.';
MEAL2 = 'Свойст.';
MEAL3 = 'Свойст.';
ALLPRODUCTION = null;
BRAN = 'Свойст.';
FLAKES = 'Свойст.';
MIDI = null;
suspend;
QNAME1 = null;
BEFOREQUALITY = null;
AFTERQUALITY = null;
QNAME2 = 'Хруст';
SEMOLINA = 'Не ощ.';
MEALEXTRA = 'Не ощ.';
MEAL1 = 'Не ощ.';
MEAL2 = 'Не ощ.';
MEAL3 = 'Не ощ.';
ALLPRODUCTION = null;
BRAN = 'Не ощ.';
FLAKES = 'Не ощ.';
MIDI = null;
suspend;
QNAME1 = null;
BEFOREQUALITY = null;
AFTERQUALITY = null;
QNAME2 = 'Заражен.';
SEMOLINA = 'Не обн.';
MEALEXTRA = 'Не обн.';
MEAL1 = 'Не обн.';
MEAL2 = 'Не обн.';
MEAL3 = 'Не обн.';
ALLPRODUCTION = null;
BRAN = 'Не обн.';
FLAKES = 'Не обн.';
MIDI = null;
suspend;
QNAME1 = null;
BEFOREQUALITY = null;
AFTERQUALITY = null;
QNAME2 = 'Налич.цел.';
SEMOLINA = 'Не обн.';
MEALEXTRA = 'Не обн.';
MEAL1 = 'Не обн.';
MEAL2 = 'Не обн.';
MEAL3 = 'Не обн.';
ALLPRODUCTION = null;
BRAN = 'Не обн.';
FLAKES = 'Не обн.';
MIDI = null;
suspend;
END
|
|