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

ACN_ACT_CALCULATION_UWACT

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_ACT_CALCULATION_UWACT(
    BEGINDATE DATE,
    ENDDATE DATE,
    OWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    STORINGID INTEGER,
    PARTYID INTEGER,
    NOTSHOWDRYING INTEGER,
    ALWAYSSHOWUWACTS INTEGER)
RETURNS (
    LINENUMBER INTEGER,
    LINE_NUMBER INTEGER,
    UWACT_NUMBER_DATA VARCHAR(255),
    NET INTEGER,
    POB INTEGER,
    FORAGE3C INTEGER,
    DRYING INTEGER,
    BEFORECLEANWEIGHT INTEGER,
    BEFOREDRYINGWEIGHT INTEGER)
AS
declare variable UWACT_UWACTN integer;
declare variable UWACT_DATE date;
begin

  LINENUMBER = 0;
  LINE_NUMBER = 0;

  for
    select
        IT.REGDATE, 0, 'Акт розподілення №' || INVOICES.INVOICENUMBER || ' від ' || DATE_TO_STR.DATESTR, 0,
        coalesce(sum(case when WQ.CLASSID in (105,107) then coalesce(IT.NET,0) end),0) +
                 coalesce(sum(case when WQ.CLASSID in (39,40,68) then coalesce(IT.NET,0) end),0),
        coalesce(sum(case when WQ.CLASSID in (41,141) then coalesce(IT.NET,0) end),0),
        0, 0, 0
      from IT
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join QUALITY Q on (Q.QUALITYID = IT.BUHQUALITYID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join QUALITY WQ on (WQ.QUALITYID = IT.OPERATIONPARAM)
        left join DATE_TO_STR(IT.REGDATE) on (0=0)
      where (IT.REGDATE between :BEGINDATE and :ENDDATE)
        and (IT.PARTYID = :PARTYID) and (IT.OWNERID = :OWNERID)
        and (OPERATION.OPERATIONTYPE = 5)
        and (WQ.CLASSID in (39,40,41,105,107,141))
        and ((Q.CROPID = :CROPID) or (coalesce(:CROPID,-1) = -1))
        and ((Q.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
        and ((Q.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
        and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
        and (IT.STORAGEID = :STORAGEID)
        and ((IT.PARENTID >= 0) and (IT.STATE >= 0))
        and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
        and (IT.UWORDERID is null)
    group by IT.REGDATE, 2, 3
    union
      select
          IT.REGDATE, UWACTS.UWACTN,
          'Очищення ' ||
          case
            when (position('_', PATH.UWFULLNAME) > 0) then
              substring(PATH.UWFULLNAME from 1 for (position('_', PATH.UWFULLNAME) - 1))
            else coalesce(PATH.UWFULLNAME,'')
          end
          || ', акт №' || coalesce(UWACTS.UWACTN,0) || ' від ' || DATE_TO_STR.DATESTR,
          max((select coalesce(sum(ACN_APPENDIX_ACT_CLEARING.BEFORECLEANWEIGHT),0)
                      from ACN_APPENDIX_ACT_CLEARING(UWACTS.UWACTID, :OWNERID))),
          coalesce(sum(case when WQ.CLASSID in (105,107) then coalesce(IT.NET,0) end),0) +
                   coalesce(sum(case when WQ.CLASSID in (39,40,68) then coalesce(IT.NET,0) end),0),
          coalesce(sum(case when WQ.CLASSID in (41,141) then coalesce(IT.NET,0) end),0),
          0,
          max((select coalesce(sum(ACN_APPENDIX_ACT_CLEARING.BEFORECLEANWEIGHT),0)
                      from ACN_APPENDIX_ACT_CLEARING(UWACTS.UWACTID, :OWNERID))),
          0
        from IT
          left join UWORDERS on (UWORDERS.UWORDERID = IT.UWORDERID)
          left join UWACTS on (UWACTS.UWORDERID = UWORDERS.UWORDERID)
          left join UWTYPES on (UWTYPES.UWTYPEID = UWORDERS.UWTYPEID)
          left join QUALITY Q on (Q.QUALITYID = IT.BUHQUALITYID)
          left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
          left join QUALITY WQ on (WQ.QUALITYID = IT.OPERATIONPARAM)
          left join DATE_TO_STR(UWACTS.UWACTDATE) on (0=0)
          left join UWTYPES PATH on (PATH.UWTYPEID = UWORDERS.PATHID)
        where (IT.REGDATE between :BEGINDATE and :ENDDATE)
          and (IT.PARTYID = :PARTYID) and (IT.OWNERID = :OWNERID)
          and (OPERATION.OPERATIONTYPE = 5)
          and (WQ.CLASSID in (39,40,41,105,107,141))
          and ((Q.CROPID = :CROPID) or (coalesce(:CROPID,-1) = -1))
          and ((Q.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
          and ((Q.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
          and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
          and (IT.STORAGEID = :STORAGEID)
          and ((IT.PARENTID >= 0) and (IT.STATE >= 0))
          and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
          and (not (IT.UWORDERID is null))
      group by IT.REGDATE, UWACTS.UWACTN, 3
    union
      select
          IT.REGDATE, UWACTS.UWACTN,
          case
            when (UWORDERS.UWTYPEID = 5) then
              cast('Освіження (активне вентилювання) ' as varchar(33))
            else cast('Сушіння ' as varchar(8))
          end ||
          case
            when (position('_', PATH.UWFULLNAME) > 0) then
              substring(PATH.UWFULLNAME from (position('_', PATH.UWFULLNAME) + 1))
            else coalesce(PATH.UWFULLNAME,'')
          end
          || ', акт №' || coalesce(UWACTS.UWACTN,0) || ' від ' || DATE_TO_STR.DATESTR,
          sum(IT.GROSS), 0, 0, sum(coalesce(IT.NET,0)), 0,
          sum(IT.GROSS)
        from IT
          left join UWORDERS on (UWORDERS.UWORDERID = IT.UWORDERID)
          left join UWACTS on (UWACTS.UWORDERID = UWORDERS.UWORDERID)
          left join UWTYPES on (UWTYPES.UWTYPEID = UWORDERS.UWTYPEID)
          left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
          left join QUALITY on (QUALITY.QUALITYID = IT.BUHQUALITYID)
          left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
          left join DATE_TO_STR(UWACTS.UWACTDATE) on (0=0)
          left join UWTYPES PATH on (PATH.UWTYPEID = UWORDERS.PATHID)
        where (:NOTSHOWDRYING = 0)
          and ((IT.REGDATE between :BEGINDATE and :ENDDATE)
            and (IT.PARTYID = :PARTYID) and (IT.OWNERID = :OWNERID)
            and ((QUALITY.CROPID = :CROPID) or (coalesce(:CROPID,- 1) = -1))
            and ((QUALITY.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
            and ((QUALITY.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
            and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
            and (OPERATION.OPERATIONID = 9) and (IT.STORAGEID = :STORAGEID)
            and (IT.STATE = -999)
            and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
            and (not (IT.UWORDERID is null)))
      group by IT.REGDATE, UWACTS.UWACTN, 3
    order by 1, 2, 3
    into :UWACT_DATE, :UWACT_UWACTN, :UWACT_NUMBER_DATA, :NET, :POB, :FORAGE3C,
         :DRYING, :BEFORECLEANWEIGHT, :BEFOREDRYINGWEIGHT

  do begin
    LINENUMBER = :LINENUMBER + 1;
    LINE_NUMBER = :LINE_NUMBER + 1;
    suspend;
  end

  if ((:LINENUMBER < 3) and (:ALWAYSSHOWUWACTS < 2)) then begin
    LINE_NUMBER = null;
    UWACT_NUMBER_DATA = null;
    NET = null;
    POB = null;
    FORAGE3C = null;
    DRYING = null;
    BEFORECLEANWEIGHT = null;
    BEFOREDRYINGWEIGHT = null;
    while (:LINENUMBER < 3) do begin
      LINENUMBER = :LINENUMBER + 1;
      suspend;
    end
  end
  
end