"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

ACN_APPENDIX_ACT_CLEARING

Входные параметры

Выходные параметры

Описание

(Нет описания для процедуры ACN_APPENDIX_ACT_CLEARING)

Определение

CREATE PROCEDURE ACN_APPENDIX_ACT_CLEARING(
    UWACTID INTEGER,
    CLIENTID INTEGER)
RETURNS (
    REGISTERID INTEGER,
    UWACTN INTEGER,
    UWACTDATE DATE,
    CROPFULLNAME VARCHAR(100),
    CLIENT_ID INTEGER,
    CLIENTFULLNAME VARCHAR(100),
    BEFOREWEIGHT INTEGER,
    AFTERWEIGHT INTEGER,
    BEFORECLEANINGWEIGHT INTEGER,
    BEFORE_CLEAN_HUMIDITY NUMERIC(4,2),
    BEFORE_CLEAN_DIRT NUMERIC(4,2),
    BEFORE_CLEAN_SCREENING NUMERIC(4,2),
    AFTERCLEANINGWEIGHT INTEGER,
    AFTER_CLEA_HUMIDITY NUMERIC(4,2),
    AFTER_CLEAN_DIRT NUMERIC(4,2),
    AFTER_CLEAN_SCREENING NUMERIC(4,2),
    FORAGE INTEGER,
    FORAGEHUMIDITY NUMERIC(4,2),
    FORAGEDIRT NUMERIC(4,2),
    FORAGESCREENING NUMERIC(4,2),
    FORAGE3C INTEGER,
    FORAGE3CHUMIDITY NUMERIC(4,2),
    FORAGE3CDIRT NUMERIC(4,2),
    FORAGE3CSCREENING NUMERIC(4,2),
    BEFORECLEANWEIGHT INTEGER,
    BEFORECLEANHUMIDITY NUMERIC(4,2),
    BEFORECLEANDIRT NUMERIC(4,2),
    BEFORECLEANSCREENING NUMERIC(4,2),
    AFTERCLEANWEIGHT INTEGER,
    AFTERCLEANHUMIDITY NUMERIC(4,2),
    AFTERCLEANDIRT NUMERIC(4,2),
    AFTERCLEANSCREENING NUMERIC(4,2),
    WASTEWEIGHT INTEGER,
    SCREENINGWEIGHT INTEGER,
    BEFOREDRYINGWEIGHT INTEGER,
    BEFORE_DRY_HUMIDITY NUMERIC(4,2),
    AFTERDRYINGWEIGHT INTEGER,
    AFTER_DRY_HUMIDITY NUMERIC(4,2),
    BEFOREDRYWEIGHT INTEGER,
    BEFOREDRYHUMIDITY NUMERIC(4,2),
    DRYWEIGHT INTEGER,
    AFTERDRYHUMIDITY NUMERIC(4,2),
    DRYING NUMERIC(4,2),
    ANALYSISNUMBER INTEGER,
    ANALYSISDATE DATE,
    NET INTEGER,
    METODSOLUTION INTEGER,
    BEFOREDRY_WEIGHT INTEGER,
    WASTE_WEIGHT INTEGER)
AS
declare variable UWORDERID integer = 0;
declare variable FORAGE_Q integer;
begin
  select UWACTS.UWACTN, UWACTS.UWACTDATE, UWORDERS.WEIGHT, UWACTS.WEIGHT,
      UWORDERS.BEFORECLEANINGWEIGHT, BEFORE_Q.HUMIDITY, BEFORE_Q.DIRT, BEFORE_Q.SCREENING,
      UWACTS.AFTERCLEANINGWEIGHT, AFTER_Q.HUMIDITY, AFTER_Q.DIRT, AFTER_Q.SCREENING,
      UWACTS.FORAGE3C, FORAGE3CQ.HUMIDITY, FORAGE3CQ.DIRT, FORAGE3CQ.SCREENING,
      UWORDERS.BEFOREDRYINGWEIGHT, BEFORE_DQ.HUMIDITY, UWACTS.AFTERDRYINGWEIGHT, AFTER_DQ.HUMIDITY,
      UWACTS.DRYING, UWTYPES.METODSOLUTION METODSOLUTION, UWACTS.UWORDERID
    from UWACTS
      left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
      left join QUALITY_SELECT(UWORDERS.BEFOREQUALITYID) BEFORE_Q on (0=0)
      left join QUALITY_SELECT(UWACTS.AFTERQUALITYID) AFTER_Q on (0=0)
      left join QUALITY_SELECT(UWACTS.QUALITYID_3C) FORAGE3CQ on (0=0)
      left join UWTYPES on (UWTYPES.UWTYPEID = UWORDERS.UWTYPEID)
      left join QUALITY_SELECT(UWORDERS.BEFOREDRYQUALITYID) BEFORE_DQ on (0=0)
      left join QUALITY_SELECT(UWACTS.AFTERDRYQUALITYID) AFTER_DQ on (0=0)
    where (UWACTS.UWACTID = :UWACTID)
  into :UWACTN, :UWACTDATE, :BEFOREWEIGHT, :AFTERWEIGHT,
       :BEFORECLEANINGWEIGHT, :BEFORE_CLEAN_HUMIDITY, :BEFORE_CLEAN_DIRT, :BEFORE_CLEAN_SCREENING,
       :AFTERCLEANINGWEIGHT, :AFTER_CLEA_HUMIDITY, :AFTER_CLEAN_DIRT, :AFTER_CLEAN_SCREENING,
       :FORAGE3C, FORAGE3CHUMIDITY, FORAGE3CDIRT, FORAGE3CSCREENING,
       :BEFOREDRYINGWEIGHT, :BEFORE_DRY_HUMIDITY, :AFTERDRYINGWEIGHT, :AFTER_DRY_HUMIDITY,
       :DRYING, :METODSOLUTION, :UWORDERID;

  select coalesce(UWACTS.FORAGE_BY_PRODUCT_A,0), UWACTS.QUALITYID_BY_PRODUCT_A
    from UWACTS
      left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
    where (UWACTS.UWACTID = :UWACTID)
  into :FORAGE, :FORAGE_Q;

  if (FORAGE = 0) then begin
    select coalesce(UWACTS.FORAGE_BY_PRODUCT_B,0), UWACTS.QUALITYID_BY_PRODUCT_B
      from UWACTS
        left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
      where (UWACTS.UWACTID = :UWACTID)
    into :FORAGE, :FORAGE_Q;

    if (FORAGE = 0) then begin
      select coalesce(UWACTS.FORAGE_BY_PRODUCT_D,0), UWACTS.QUALITYID_BY_PRODUCT_D
        from UWACTS
          left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
        where (UWACTS.UWACTID = :UWACTID)
      into :FORAGE, :FORAGE_Q;

      if (FORAGE = 0) then begin
        select coalesce(UWACTS.FORAGE1C_A,0), UWACTS.QUALITYID_1C_A
          from UWACTS
            left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
          where (UWACTS.UWACTID = :UWACTID)
        into :FORAGE, :FORAGE_Q;

        if (FORAGE = 0) then begin
          select coalesce(UWACTS.FORAGE1C_B,0), UWACTS.QUALITYID_1C_B
            from UWACTS
              left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
            where (UWACTS.UWACTID = :UWACTID)
          into :FORAGE, :FORAGE_Q;

          if (FORAGE = 0) then begin
            select coalesce(UWACTS.FORAGE2C,0), UWACTS.QUALITYID_2C
              from UWACTS
                left join UWORDERS on (UWORDERS.UWORDERID = UWACTS.UWORDERID)
              where (UWACTS.UWACTID = :UWACTID)
            into :FORAGE, :FORAGE_Q;
          end
        end
      end
    end
  end

  if (FORAGE > 0) then
    select QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
      from QUALITY_SELECT(:FORAGE_Q)
    into :FORAGEHUMIDITY, :FORAGEDIRT, :FORAGESCREENING;

  for
    select REGISTERID, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, CLIENT_ID, CLIENTS.CLIENTFULLNAME,
        sum(BEFORECLEANWEIGHT) BEFORECLEANWEIGHT, max(BEFORECLEANHUMIDITY) BEFORECLEANHUMIDITY,
        max(BEFORECLEANDIRT) BEFORECLEANDIRT, max(BEFORECLEANSCREENING) BEFORECLEANSCREENING,
        sum(AFTERCLEANWEIGHT) AFTERCLEANWEIGHT, max(AFTERCLEANHUMIDITY) AFTERCLEANHUMIDITY,
        max(AFTERCLEANDIRT) AFTERCLEANDIRT, max(AFTERCLEANSCREENING) AFTERCLEANSCREENING,
        case
          when (max(WASTEWEIGHT) > 0) then
            max(WASTEWEIGHT)
          else min(WASTEWEIGHT)
        end WASTEWEIGHT, 
        case
          when (max(SCREENINGWEIGHT) > 0) then
            max(SCREENINGWEIGHT)
          else min(SCREENINGWEIGHT)
        end SCREENINGWEIGHT,

        sum(BEFOREDRYWEIGHT) BEFOREDRYWEIGHT, max(BEFOREDRYHUMIDITY) BEFOREDRYHUMIDITY,
        case
          when (max(DRYWEIGHT) > 0) then
            max(DRYWEIGHT)
          else min(DRYWEIGHT)
        end DRYWEIGHT,
        max(AFTERDRYHUMIDITY) AFTERDRYHUMIDITY, max(ANALYSISNUMBER), max(ANALYSISDATE),
        sum(NET) NET, sum(BEFOREDRY_WEIGHT) BEFOREDRY_WEIGHT
      from (
        select IT.REGISTERID REGISTERID, IT.SAMPLERQUALITYID SAMPLERQUALITYID, IT.OWNERID CLIENT_ID,
            IT.GROSS BEFORECLEANWEIGHT, BEFOREQ.HUMIDITY BEFORECLEANHUMIDITY,
            BEFOREQ.DIRT BEFORECLEANDIRT, BEFOREQ.SCREENING BEFORECLEANSCREENING,
            IT.TARE AFTERCLEANWEIGHT, AFTERQ.HUMIDITY AFTERCLEANHUMIDITY,
            AFTERQ.DIRT AFTERCLEANDIRT, AFTERQ.SCREENING AFTERCLEANSCREENING,
            IT.NET WASTEWEIGHT, IT.WEIGHT SCREENINGWEIGHT,

            0 BEFOREDRYWEIGHT, BEFOREQ.HUMIDITY BEFOREDRYHUMIDITY,
            0 DRYWEIGHT, 0 AFTERDRYHUMIDITY,
            (select min(QUALITY_SELECT.QUALITYNUMBER)
               from IT IT2
                 left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
               where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
            ) ANALYSISNUMBER,
            (select min(QUALITY_SELECT.ANALYSISDATE)
               from IT IT2
                 left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
               where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
            ) ANALYSISDATE,
            (select sum(IT2.NET)
               from IT IT2
               where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
            ) NET,
            0 BEFOREDRY_WEIGHT, IT.OPERATIONPARAM
          from IT
            left join QUALITY_SELECT(IT.SAMPLERQUALITYID) BEFOREQ on (0=0)
            left join QUALITY_SELECT(IT.OPERATIONPARAM) AFTERQ on (0=0)
            left join IT IT2 on (IT2.REGISTERID = IT.REGISTERID)
          where (IT.UWORDERID = :UWORDERID) and (IT.STATE = -999) and (IT.OPERATIONID = 4)
            and ((IT.OWNERID = :CLIENTID) or (coalesce(:CLIENTID,-1) = -1))
        union
          select IT.REGISTERID REGISTERID, IT.SAMPLERQUALITYID SAMPLERQUALITYID, IT.OWNERID CLIENT_ID,
              0 BEFORECLEANWEIGHT, 0 BEFORECLEANHUMIDITY,
              0 BEFORECLEANDIRT, 0 BEFORECLEANSCREENING,
              0 AFTERCLEANWEIGHT, 0 AFTERCLEANHUMIDITY,
              0 AFTERCLEANDIRT, 0 AFTERCLEANSCREENING,
              0 WASTEWEIGHT, 0 SCREENINGWEIGHT,

              IT.GROSS BEFOREDRYWEIGHT, BEFOREQ.HUMIDITY BEFOREDRYHUMIDITY,
              IT.NET DRYWEIGHT, AFTERQ.HUMIDITY AFTERDRYHUMIDITY,
              (select min(QUALITY_SELECT.QUALITYNUMBER)
                 from IT IT2
                   left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
                 where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
              ) ANALYSISNUMBER,
              (select min(QUALITY_SELECT.ANALYSISDATE)
                 from IT IT2
                   left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
                 where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
              ) ANALYSISDATE,
              (select sum(IT2.NET)
                 from IT IT2
                 where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
              ) NET,
              IT.TARE BEFOREDRY_WEIGHT, IT.OPERATIONPARAM
            from IT
              left join QUALITY_SELECT(IT.SAMPLERQUALITYID) BEFOREQ on (0=0)
              left join QUALITY_SELECT(IT.OPERATIONPARAM) AFTERQ on (0=0)
            where (IT.UWORDERID = :UWORDERID) and (IT.STATE = -999) and (IT.OPERATIONID = 9)
              and ((IT.OWNERID = :CLIENTID) or (coalesce(:CLIENTID,-1) = -1))
        order by 1
      )
      left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = REGISTERID)
      left join QUALITY_CROPFULLNAME_BYID(SAMPLERQUALITYID, 1) on (0=0)
      left join CLIENTS on (CLIENTS.CLIENTID = CLIENT_ID)
    group by OPERATIONPARAM, REGISTERID, CROPFULLNAME, CLIENT_ID, CLIENTFULLNAME
    into :REGISTERID, :CROPFULLNAME, :CLIENT_ID, :CLIENTFULLNAME,
         :BEFORECLEANWEIGHT, :BEFORECLEANHUMIDITY, :BEFORECLEANDIRT, :BEFORECLEANSCREENING,
         :AFTERCLEANWEIGHT, :AFTERCLEANHUMIDITY, :AFTERCLEANDIRT, :AFTERCLEANSCREENING, :WASTEWEIGHT, :SCREENINGWEIGHT,
         :BEFOREDRYWEIGHT, :BEFOREDRYHUMIDITY, :DRYWEIGHT, :AFTERDRYHUMIDITY,
         :ANALYSISNUMBER, :ANALYSISDATE, :NET, :BEFOREDRY_WEIGHT

  do begin
    select sum(ITTRANSPORT.DIRTWEIGHT)
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.REGISTERID = :REGISTERID)
    group by IT.REGISTERID
    into :WASTE_WEIGHT;

    if (BEFORECLEANWEIGHT = 0) then
      BEFORECLEANWEIGHT = BEFOREDRYWEIGHT;

    suspend;
  end

end