"localhost:/firebird/data/ILICHEVSK2.FDB". ���������

UNDERWORK_JOURNAL

������� ���������

�������� ���������

��������

(��� �������� ��� ��������� UNDERWORK_JOURNAL)

�����������

CREATE PROCEDURE UNDERWORK_JOURNAL(
    BEGDATE DATE,
    ENDDATE DATE)
RETURNS (
    REGDATE DATE,
    SESSION INTEGER,
    CROPNAME VARCHAR(50),
    USERNAME VARCHAR(50),
    WEIGHT INTEGER,
    LABQUALITYNUMBER INTEGER,
    SQUALITYNUMBER INTEGER,
    LHUMIDITY NUMERIC(9,2),
    LGRAINUNIT NUMERIC(9,2),
    LDIRT NUMERIC(9,2),
    LSCREENING NUMERIC(9,2),
    LPOISON NUMERIC(9,2),
    LINFECTION_COUNT NUMERIC(9,2),
    LINFECTIONNAME VARCHAR(255),
    LDEGREE VARCHAR(30),
    SHUMIDITY NUMERIC(9,2),
    SGRAINUNIT NUMERIC(9,2),
    SDIRT NUMERIC(9,2),
    SSCREENING NUMERIC(9,2),
    SPOISON NUMERIC(9,2),
    SINFECTION_COUNT NUMERIC(9,2),
    SINFECTIONNAME VARCHAR(255),
    SDEGREE VARCHAR(30),
    GROUPEXPRESSION INTEGER,
    STORAGEID INTEGER)
AS
DECLARE VARIABLE RECIPEN VARCHAR(20);
DECLARE VARIABLE STORAGENAME VARCHAR(100);
DECLARE VARIABLE F1C_A_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE F1C_A_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE F1C_A_DIRT NUMERIC(9,2);
DECLARE VARIABLE F1C_A_SCREENING NUMERIC(9,2);
DECLARE VARIABLE F1C_A_POISON NUMERIC(9,2);
DECLARE VARIABLE F1C_A_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE F1C_A_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE F1C_A_DEGREE VARCHAR(30);
DECLARE VARIABLE F1C_B_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE F1C_B_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE F1C_B_DIRT NUMERIC(9,2);
DECLARE VARIABLE F1C_B_SCREENING NUMERIC(9,2);
DECLARE VARIABLE F1C_B_POISON NUMERIC(9,2);
DECLARE VARIABLE F1C_B_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE F1C_B_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE F1C_B_DEGREE VARCHAR(30);
DECLARE VARIABLE F2C_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE F2C_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE F2C_DIRT NUMERIC(9,2);
DECLARE VARIABLE F2C_SCREENING NUMERIC(9,2);
DECLARE VARIABLE F2C_POISON NUMERIC(9,2);
DECLARE VARIABLE F2C_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE F2C_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE F2C_DEGREE VARCHAR(30);
DECLARE VARIABLE F3C_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE F3C_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE F3C_DIRT NUMERIC(9,2);
DECLARE VARIABLE F3C_SCREENING NUMERIC(9,2);
DECLARE VARIABLE F3C_POISON NUMERIC(9,2);
DECLARE VARIABLE F3C_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE F3C_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE F3C_DEGREE VARCHAR(30);
DECLARE VARIABLE BY_A_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE BY_A_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE BY_A_DIRT NUMERIC(9,2);
DECLARE VARIABLE BY_A_SCREENING NUMERIC(9,2);
DECLARE VARIABLE BY_A_POISON NUMERIC(9,2);
DECLARE VARIABLE BY_A_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE BY_A_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE BY_A_DEGREE VARCHAR(30);
DECLARE VARIABLE BY_B_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE BY_B_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE BY_B_DIRT NUMERIC(9,2);
DECLARE VARIABLE BY_B_SCREENING NUMERIC(9,2);
DECLARE VARIABLE BY_B_POISON NUMERIC(9,2);
DECLARE VARIABLE BY_B_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE BY_B_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE BY_B_DEGREE VARCHAR(30);
DECLARE VARIABLE SMALLGRAIN_HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_GRAINUNIT NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_DIRT NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_SCREENING NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_POISON NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_INFECTION_COUNT NUMERIC(9,2);
DECLARE VARIABLE SMALLGRAIN_INFECTIONNAME VARCHAR(255);
DECLARE VARIABLE SMALLGRAIN_DEGREE VARCHAR(30);
DECLARE VARIABLE CLASSNAME VARCHAR(20);
begin
  groupexpression = 0;
  for
    select labquality.qualitynumber, samplerquality.qualitynumber, users_fullname.userfullname, uwoutacts.weight,
      Uwoutacts.regdate, labquality.session, crop.shortcropname, class.classname, recipes.recipen, storage.storagename,
      labquality.humidity, labquality.grainunit, labquality.dirt, labquality.screening, labquality.poison, labquality.infection_count, LabInfection.selectvalue, LabDegree.degree,
      samplerquality.humidity, samplerquality.grainunit, samplerquality.dirt, samplerquality.screening, samplerquality.poison, samplerquality.infection_count, SamplerInfection.selectvalue, SamplerDegree.degree,
      F1C_a_Quality.humidity, F1C_a_Quality.grainunit, F1C_a_Quality.dirt, F1C_a_Quality.screening, F1C_a_Quality.poison, F1C_a_Quality.infection_count, F1C_a_Infection.selectvalue, F1C_a_Degree.degree,
      F1C_b_Quality.humidity, F1C_b_Quality.grainunit, F1C_b_Quality.dirt, F1C_b_Quality.screening, F1C_b_Quality.poison, F1C_b_Quality.infection_count, F1C_b_Infection.selectvalue, F1C_b_Degree.degree,
      F2C_Quality.humidity, F2C_Quality.grainunit, F2C_Quality.dirt, F2C_Quality.screening, F2C_Quality.poison, F2C_Quality.infection_count, F2C_Infection.selectvalue, F2C_Degree.degree,
      F3C_Quality.humidity, F3C_Quality.grainunit, F3C_Quality.dirt, F3C_Quality.screening, F3C_Quality.poison, F3C_Quality.infection_count, F3C_Infection.selectvalue, F3C_Degree.degree,
      BY_a_Quality.humidity, BY_a_Quality.grainunit, BY_a_Quality.dirt, BY_a_Quality.screening, BY_a_Quality.poison, BY_a_Quality.infection_count, BY_a_Infection.selectvalue, BY_a_Degree.degree,
      BY_b_Quality.humidity, BY_b_Quality.grainunit, BY_b_Quality.dirt, BY_b_Quality.screening, BY_b_Quality.poison, BY_b_Quality.infection_count, BY_b_Infection.selectvalue, BY_b_Degree.degree,
      SmallGrain_Quality.humidity, SmallGrain_Quality.grainunit, SmallGrain_Quality.dirt, SmallGrain_Quality.screening, SmallGrain_Quality.poison, SmallGrain_Quality.infection_count, SmallGrain_Infection.selectvalue, SmallGrain_Degree.degree,
      (select max(storageid) from it where it.invoiceid = uwoutacts.invoiceid)
    from uwoutacts
      left join quality_select(UWOUTACTS.afterqualityid) LABQUALITY on (0 = 0)
      left join quality_select_values(19, LABQUALITY.infectionid, 0) LabInfection on (0 = 0)
      left join infection_calc_degree(LABQUALITY.infectionid, LABQUALITY.infection_count, LABQUALITY.infection_accumulation, 0) LabDegree on (0 = 0)

      left join quality_select(UWOUTACTS.beforequalityid) SAMPLERQUALITY on (0 = 0)
      left join quality_select_values(19, SAMPLERQUALITY.infectionid, 0) SamplerInfection on (0 = 0)
      left join infection_calc_degree(SamplerQUALITY.infectionid, SamplerQUALITY.infection_count, SamplerQUALITY.infection_accumulation, 0) SamplerDegree on (0 = 0)

      left join quality_select(UWOUTACTS.forage1c_aid) F1C_a_Quality on (0 = 0)
      left join quality_select_values(19, F1C_a_Quality.infectionid, 0) F1C_a_Infection on (0 = 0)
      left join infection_calc_degree(F1C_a_Quality.infectionid, F1C_a_Quality.infection_count, F1C_a_Quality.infection_accumulation, 0) F1C_a_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.forage1c_bid) F1C_b_Quality on (0 = 0)
      left join quality_select_values(19, F1C_b_Quality.infectionid, 0) F1C_b_Infection on (0 = 0)
      left join infection_calc_degree(F1C_b_Quality.infectionid, F1C_b_Quality.infection_count, F1C_b_Quality.infection_accumulation, 0) F1C_b_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.forage2cid) F2C_Quality on (0 = 0)
      left join quality_select_values(19, F2C_Quality.infectionid, 0) F2C_Infection on (0 = 0)
      left join infection_calc_degree(F2C_Quality.infectionid, F2C_Quality.infection_count, F2C_Quality.infection_accumulation, 0) F2C_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.forage3cid) F3C_Quality on (0 = 0)
      left join quality_select_values(19, F3C_Quality.infectionid, 0) F3C_Infection on (0 = 0)
      left join infection_calc_degree(F3C_Quality.infectionid, F3C_Quality.infection_count, F3C_Quality.infection_accumulation, 0) F3C_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.byproduct_aid) BY_a_Quality on (0 = 0)
      left join quality_select_values(19, BY_a_Quality.infectionid, 0) BY_a_Infection on (0 = 0)
      left join infection_calc_degree(BY_a_Quality.infectionid, BY_a_Quality.infection_count, BY_a_Quality.infection_accumulation, 0) BY_a_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.byproduct_bid) BY_b_Quality on (0 = 0)
      left join quality_select_values(19, BY_b_Quality.infectionid, 0) BY_b_Infection on (0 = 0)
      left join infection_calc_degree(BY_b_Quality.infectionid, BY_b_Quality.infection_count, BY_b_Quality.infection_accumulation, 0) BY_b_Degree on (0 = 0)

      left join quality_select(UWOUTACTS.smallgrainid) SmallGrain_Quality on (0 = 0)
      left join quality_select_values(19, SmallGrain_Quality.infectionid, 0) SmallGrain_Infection on (0 = 0)
      left join infection_calc_degree(SmallGrain_Quality.infectionid, SmallGrain_Quality.infection_count, SmallGrain_Quality.infection_accumulation, 0) SmallGrain_Degree on (0 = 0)

      left join crop on (LabQUALITY.CROPID = crop.cropid)
      left join class on (LabQUALITY.classid = class.classid)
      left join recipes on (recipes.recipeid = Uwoutacts.recipeid)
      left join storage on (storage.storageid = recipes.storageid)
      left join users_fullname(SAMPLERQUALITY.userid) on (0 = 0)
    where uwoutacts.regdate >= :BegDate
      and uwoutacts.regdate <= :EndDate
      and uwoutacts.state > -1
    order by uwoutacts.regdate
    into :labqualitynumber, :squalitynumber, :USERNAME, :WEIGHT, :RegDate, :session, :cropname, :CLASSNAME, :recipen, :storagename,
      :LHumidity, :LGrainunit, :LDirt, :LScreening, :LPoison, :LInfection_count, :LInfectionName, :LDegree,
      :SHumidity, :SGrainunit, :SDirt, :SScreening, :SPoison, :SInfection_count, :SInfectionName, :SDegree,
      :F1C_a_Humidity, :F1C_a_Grainunit, :F1C_a_Dirt, :F1C_a_Screening, :F1C_a_Poison, :F1C_a_Infection_count, :F1C_a_InfectionName, :F1C_a_Degree,
      :F1C_b_Humidity, :F1C_b_Grainunit, :F1C_b_Dirt, :F1C_b_Screening, :F1C_b_Poison, :F1C_b_Infection_count, :F1C_b_InfectionName, :F1C_b_Degree,
      :F2C_Humidity, :F2C_Grainunit, :F2C_Dirt, :F2C_Screening, :F2C_Poison, :F2C_Infection_count, :F2C_InfectionName, :F2C_Degree,
      :F3C_Humidity, :F3C_Grainunit, :F3C_Dirt, :F3C_Screening, :F3C_Poison, :F3C_Infection_count, :F3C_InfectionName, :F3C_Degree,
      :BY_a_Humidity, :BY_a_Grainunit, :BY_a_Dirt, :BY_a_Screening, :BY_a_Poison, :BY_a_Infection_count, :BY_a_InfectionName, :BY_a_Degree,
      :BY_b_Humidity, :BY_b_Grainunit, :BY_b_Dirt, :BY_b_Screening, :BY_b_Poison, :BY_b_Infection_count, :BY_b_InfectionName, :BY_b_Degree,
      :SmallGrain_Humidity, :SmallGrain_Grainunit, :SmallGrain_Dirt, :SmallGrain_Screening, :SmallGrain_Poison, :SmallGrain_Infection_count, :SmallGrain_InfectionName, :SmallGrain_Degree,
      :storageid
  do begin
    if (:RECIPEN is not null) then CROPNAME = :CROPNAME || ' (�.�.' || :RECIPEN || ')';
    else if (:CLASSNAME is not null) then CROPNAME = :CROPNAME || ' ' || :CLASSNAME;
    groupexpression = groupexpression + 1;
    suspend;
    REGDATE = null;
    SHUMIDITY = NULL;
    SGRAINUNIT = NULL;
    SDIRT = NULL;
    SSCREENING = NULL;
    SPOISON = NULL;
    SINFECTION_COUNT = NULL;
    SINFECTIONNAME = NULL;
    SDEGREE = NULL;
    WEIGHT = NULL;

    if (:F1C_a_Humidity is not null or :F1C_a_Grainunit is not null or :F1C_a_Dirt is not null or :F1C_a_Screening is not null or :F1C_a_Poison is not null or :F1C_a_Infection_count is not null or (:F1C_a_InfectionName is not null and :F1C_a_InfectionName <> '�� ���.' and :F1C_a_InfectionName <> '�.�.') or :F1C_a_Degree is not null) then begin
      CROPNAME = '���. 1 ��� "�"';
      LHUMIDITY = :F1C_A_HUMIDITY;
      LGRAINUNIT = :F1C_a_GRAINUNIT;
      LDIRT = :F1C_a_DIRT;
      LSCREENING = :F1C_a_SCREENING;
      LPOISON = :F1C_a_POISON;
      LINFECTION_COUNT = :F1C_a_INFECTION_COUNT;
      LINFECTIONNAME = :F1C_a_INFECTIONNAME;
      LDEGREE = :F1C_a_DEGREE;
      suspend;
    end

    if (:F1C_b_Humidity is not null or :F1C_b_Grainunit is not null or :F1C_b_Dirt is not null or :F1C_b_Screening is not null or :F1C_b_Poison is not null or :F1C_b_Infection_count is not null or (:F1C_b_InfectionName is not null and :F1C_b_InfectionName <> '�� ���.' and :F1C_b_InfectionName <> '�.�.') or :F1C_b_Degree is not null) then begin
      CROPNAME = '���. 1 ��� "�"';
      LHUMIDITY = :F1C_b_HUMIDITY;
      LGRAINUNIT = :F1C_b_GRAINUNIT;
      LDIRT = :F1C_b_DIRT;
      LSCREENING = :F1C_b_SCREENING;
      LPOISON = :F1C_b_POISON;
      LINFECTION_COUNT = :F1C_b_INFECTION_COUNT;
      LINFECTIONNAME = :F1C_b_INFECTIONNAME;
      LDEGREE = :F1C_b_DEGREE;
      suspend;
    end

    if (:F2C_Humidity is not null or :F2C_Grainunit is not null or :F2C_Dirt is not null or :F2C_Screening is not null or :F2C_Poison is not null or :F2C_Infection_count is not null or (:F2C_InfectionName is not null and :F2C_InfectionName <> '�� ���.' and :F2C_InfectionName <> '�.�.') or :F2C_Degree is not null) then begin
      CROPNAME = '���. 2 ���';
      LHUMIDITY = :F2C_HUMIDITY;
      LGRAINUNIT = :F2C_GRAINUNIT;
      LDIRT = :F2C_DIRT;
      LSCREENING = :F2C_SCREENING;
      LPOISON = :F2C_POISON;
      LINFECTION_COUNT = :F2C_INFECTION_COUNT;
      LINFECTIONNAME = :F2C_INFECTIONNAME;
      LDEGREE = :F2C_DEGREE;
      suspend;
    end

    if (:F3C_Humidity is not null or :F3C_Grainunit is not null or :F3C_Dirt is not null or :F3C_Screening is not null or :F3C_Poison is not null or :F3C_Infection_count is not null or (:F3C_InfectionName is not null and :F3C_InfectionName <> '�� ���.' and :F3C_InfectionName <> '�.�.') or :F3C_Degree is not null) then begin
      CROPNAME = '���. 3 ���';
      LHUMIDITY = :F3C_HUMIDITY;
      LGRAINUNIT = :F3C_GRAINUNIT;
      LDIRT = :F3C_DIRT;
      LSCREENING = :F3C_SCREENING;
      LPOISON = :F3C_POISON;
      LINFECTION_COUNT = :F3C_INFECTION_COUNT;
      LINFECTIONNAME = :F3C_INFECTIONNAME;
      LDEGREE = :F3C_DEGREE;
      suspend;
    end

    if (:BY_a_Humidity is not null or :BY_a_Grainunit is not null or :BY_a_Dirt is not null or :BY_a_Screening is not null or :BY_a_Poison is not null or :BY_a_Infection_count is not null or (:BY_a_InfectionName is not null and :BY_a_InfectionName <> '�� ���.' and :BY_a_InfectionName <> '�.�.') or :BY_a_Degree is not null) then begin
      CROPNAME = '���. ����. "�"';
      LHUMIDITY = :BY_A_HUMIDITY;
      LGRAINUNIT = :BY_a_GRAINUNIT;
      LDIRT = :BY_a_DIRT;
      LSCREENING = :BY_a_SCREENING;
      LPOISON = :BY_a_POISON;
      LINFECTION_COUNT = :BY_a_INFECTION_COUNT;
      LINFECTIONNAME = :BY_a_INFECTIONNAME;
      LDEGREE = :BY_a_DEGREE;
      suspend;
    end

    if (:BY_b_Humidity is not null or :BY_b_Grainunit is not null or :BY_b_Dirt is not null or :BY_b_Screening is not null or :BY_b_Poison is not null or :BY_b_Infection_count is not null or (:BY_b_InfectionName is not null and :BY_b_InfectionName <> '�� ���.' and :BY_b_InfectionName <> '�.�.') or :BY_b_Degree is not null) then begin
      CROPNAME = '���. ����. "�"';
      LHUMIDITY = :BY_b_HUMIDITY;
      LGRAINUNIT = :BY_b_GRAINUNIT;
      LDIRT = :BY_b_DIRT;
      LSCREENING = :BY_b_SCREENING;
      LPOISON = :BY_b_POISON;
      LINFECTION_COUNT = :BY_b_INFECTION_COUNT;
      LINFECTIONNAME = :BY_b_INFECTIONNAME;
      LDEGREE = :BY_b_DEGREE;
      suspend;
    end

    if (:SMALLGRAIN_HUMIDITY is not null or :SMALLGRAIN_Grainunit is not null or :SMALLGRAIN_Dirt is not null or :SMALLGRAIN_Screening is not null or :SMALLGRAIN_Poison is not null or :SMALLGRAIN_Infection_count is not null or (:SMALLGRAIN_InfectionName is not null and :SMALLGRAIN_InfectionName <> '�� ���.' and :SMALLGRAIN_InfectionName <> '�.�.') or :SMALLGRAIN_Degree is not null) then begin
      CROPNAME = '����. ����.';
      LHUMIDITY = :SMALLGRAIN_HUMIDITY;
      LGRAINUNIT = :SMALLGRAIN_GRAINUNIT;
      LDIRT = :SMALLGRAIN_DIRT;
      LSCREENING = :SMALLGRAIN_SCREENING;
      LPOISON = :SMALLGRAIN_POISON;
      LINFECTION_COUNT = :SMALLGRAIN_INFECTION_COUNT;
      LINFECTIONNAME = :SMALLGRAIN_INFECTIONNAME;
      LDEGREE = :SMALLGRAIN_DEGREE;
      suspend;
    end
  end
end