"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры Previous topic Chapter index Next topic

ACN_SVODKA_VALARS

 

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

Параметр

Тип

Описание

BEGINDATE

DATE

 

ENDDATE

DATE

 

ISCURRENT

INTEGER

 

AOWNERID

INTEGER

 

CONTRACTID

INTEGER

 

STORAGEID

INTEGER

 

STORINGID

INTEGER

 

ONLYREST

INTEGER

 

SHOWWASTE

INTEGER

 

CHECKFORAGECLASS

INTEGER

 

OWNERWASTEID

INTEGER

 

PRELIMINARYSHRINKAGE

INTEGER

 

SHOWINGCROPYEAR

INTEGER

 

YEARHARVESTING

INTEGER

 

SHOWTRANSFERINEXPENSE

INTEGER

 

 

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

Параметр

Тип

Описание

OWNERID

INTEGER

 

CLIENTFULLNAME

VARCHAR(100)

 

CLIENTNAME

VARCHAR(30)

 

CLIENTTYPEID

INTEGER

 

PARTYID

INTEGER

 

PARTY_BEGINDATE

DATE

 

PARTY_ENDDATE

DATE

 

CROPID

INTEGER

 

CLASSID

INTEGER

 

CROPNAME

VARCHAR(20)

 

CROPFULLNAME

VARCHAR(100)

 

CROPYEARFULLNAME

VARCHAR(100)

 

CROPTYPEID

INTEGER

 

CROPYEAR

INTEGER

 

CROPGHI

INTEGER

 

CLASSGHI

INTEGER

 

PACKNAME

VARCHAR(30)

 

REMAINNET

INTEGER

 

INCOMENETAVTO

INTEGER

 

INCOMENETRRW

INTEGER

 

INCOMENET

INTEGER

 

REINCOME

INTEGER

 

OUTCOME

INTEGER

 

REOUTCOME

INTEGER

 

DECREASENET

INTEGER

 

RESTNET

INTEGER

 

HUMWEIGHT

INTEGER

 

REMAINWEIGHT

INTEGER

 

INCOMEWEIGHTAVTO

INTEGER

 

INCOMEWEIGHTRRW

INTEGER

 

INCOMEWEIGHT

INTEGER

 

OUTCOMEWEIGHT

INTEGER

 

RESTWEIGHT

INTEGER

 

ARRIVALNET

INTEGER

 

BEGINNINGNET

INTEGER

 

ARRIVALWEIGHT

INTEGER

 

BEGINNINGWEIGHT

INTEGER

 

REINCOMEWEIGHT

INTEGER

 

REOUTCOMEWEIGHT

INTEGER

 

BEGIN_HUMWEIGHT

INTEGER

 

PERIOD_HUMWEIGHT

INTEGER

 

ALLINCOMEWEIGHT

INTEGER

 

REMAINDECLARATION

INTEGER

 

INCOMEDECLARATION

INTEGER

 

OUTCOMEDECLARATION

INTEGER

 

COMEAFTERDECLARATION

INTEGER

 

STORAGENAME

VARCHAR(100)

 

CROPCLASSNAME

VARCHAR(41)

 

 

Описание

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

 

Определение

CREATE PROCEDURE ACN_SVODKA_VALARS(
    BEGINDATE DATE,
    ENDDATE DATE,
    ISCURRENT INTEGER,
    AOWNERID INTEGER,
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    STORINGID INTEGER,
    ONLYREST INTEGER,
    SHOWWASTE INTEGER,
    CHECKFORAGECLASS INTEGER,
    OWNERWASTEID INTEGER,
    PRELIMINARYSHRINKAGE INTEGER,
    SHOWINGCROPYEAR INTEGER,
    YEARHARVESTING INTEGER,
    SHOWTRANSFERINEXPENSE INTEGER)
RETURNS (
    OWNERID INTEGER,
    CLIENTFULLNAME VARCHAR(100),
    CLIENTNAME VARCHAR(30),
    CLIENTTYPEID INTEGER,
    PARTYID INTEGER,
    PARTY_BEGINDATE DATE,
    PARTY_ENDDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    CROPNAME VARCHAR(20),
    CROPFULLNAME VARCHAR(100),
    CROPYEARFULLNAME VARCHAR(100),
    CROPTYPEID INTEGER,
    CROPYEAR INTEGER,
    CROPGHI INTEGER,
    CLASSGHI INTEGER,
    PACKNAME VARCHAR(30),
    REMAINNET INTEGER,
    INCOMENETAVTO INTEGER,
    INCOMENETRRW INTEGER,
    INCOMENET INTEGER,
    REINCOME INTEGER,
    OUTCOME INTEGER,
    REOUTCOME INTEGER,
    DECREASENET INTEGER,
    RESTNET INTEGER,
    HUMWEIGHT INTEGER,
    REMAINWEIGHT INTEGER,
    INCOMEWEIGHTAVTO INTEGER,
    INCOMEWEIGHTRRW INTEGER,
    INCOMEWEIGHT INTEGER,
    OUTCOMEWEIGHT INTEGER,
    RESTWEIGHT INTEGER,
    ARRIVALNET INTEGER,
    BEGINNINGNET INTEGER,
    ARRIVALWEIGHT INTEGER,
    BEGINNINGWEIGHT INTEGER,
    REINCOMEWEIGHT INTEGER,
    REOUTCOMEWEIGHT INTEGER,
    BEGIN_HUMWEIGHT INTEGER,
    PERIOD_HUMWEIGHT INTEGER,
    ALLINCOMEWEIGHT INTEGER,
    REMAINDECLARATION INTEGER,
    INCOMEDECLARATION INTEGER,
    OUTCOMEDECLARATION INTEGER,
    COMEAFTERDECLARATION INTEGER,
    STORAGENAME VARCHAR(100),
    CROPCLASSNAME VARCHAR(41))
AS
declare variable TPARTYID integer;
declare variable MINDATE date;
declare variable MAXDATE date;
declare variable CLIENTID integer;
declare variable SORTID integer;
declare variable FORAGECROPID integer;
declare variable FORAGECLASSID integer;
declare variable REGDATE char(10);
declare variable NET integer;
declare variable WEIGHT integer;
declare variable OPERATIONSUBTYPE integer;
declare variable OPERATIONID integer;
declare variable AHUMWEIGHT integer;
declare variable ABEGIN_HUMWEIGHT integer;
declare variable APERIOD_HUMWEIGHT integer;
declare variable AUWORDERID integer;
declare variable AUWACTDATE date;
declare variable OPERATIONPARAM integer;
declare variable UWORDERID integer;
declare variable PARTYNAME varchar(31);
declare variable DEFAULTCROPYEAR integer;
declare variable INITIALDATE date;
begin

  if (BEGINDATE is null) then begin
    select max(CROPPARAMS.DEFAULTVALUE)
      from CROPPARAMS
      where ((CROPPARAMS.CROPID = -1) and (CROPPARAMS.QUALITYTYPEID = 6))
    into :DEFAULTCROPYEAR;

    select min(PARTIES.BEGINDATE)
      from PARTIES
      where (extract(YEAR from PARTIES.BEGINDATE) = :DEFAULTCROPYEAR)
    into INITIALDATE;

    BEGINDATE = ENDDATE;
  end
  else INITIALDATE = BEGINDATE;

  for
    select CLIENTID, CLIENTFULLNAME, CLIENTNAME, CLIENTTYPEID, PARTYID,
        min(BEGINDATE) BEGINDATE, min(ENDDATE) ENDDATE,
        CROPID, CLASSID, CROPNAME, CROPFULLNAME, min(CROPTYPEID) CROPTYPEID,
        CROPYEAR, min(CROPGHI) CROPGHI, min(CLASSGHI) CLASSGHI, PACKNAME,
        sum(REMAINNET) REMAINNET, sum(INCOMENETAVTO) INCOMENETAVTO,
        sum(INCOMENETRRW) INCOMENETRRW, sum(INCOMENET) INCOMENET,
        sum(INCOMERENEVALNET) INCOMERENEVALNET, sum(OUTCOMENET) OUTCOMENET,
        sum(OUTCOMERENEVALNET) OUTCOMERENEVALNET, sum(DECREASENET) DECREASENET,
        sum(COMEAFTERNET) COMEAFTERNET, sum(ARRIVALNET) ARRIVALNET,
        sum(BEGINNINGNET) BEGINNINGNET, sum(HUMWEIGHT) HUMWEIGHT,
        sum(COMEBEFOREWEIGHT) COMEBEFOREWEIGHT, sum(INCOMEWEIGHTAVTO) INCOMEWEIGHTAVTO,
        sum(INCOMEWEIGHTRRW) INCOMEWEIGHTRRW, sum(INCOMEWEIGHT) INCOMEWEIGHT,
        sum(REINCOMEWEIGHT) REINCOMEWEIGHT, sum(OUTCOMEWEIGHT) OUTCOMEWEIGHT,
        sum(REOUTCOMEWEIGHT) REOUTCOMEWEIGHT, sum(COMEAFTERWEIGHT) COMEAFTERWEIGHT,
        sum(ARRIVALWEIGHT) ARRIVALWEIGHT, sum(BEGINNINGWEIGHT) BEGINNINGWEIGHT,
        sum(ALLINCOMEWEIGHT) ALLINCOMEWEIGHT,
        sum(REMAINDECLARATION) REMAINDECLARATION, sum(INCOMEDECLARATION) INCOMEDECLARATION,
        sum(OUTCOMEDECLARATION) OUTCOMEDECLARATION, sum(COMEAFTERDECLARATION) COMEAFTERDECLARATION,
        STORAGENAME, min(CROPCLASSNAME) CROPCLASSNAME
      from (
        select
            case
              when (QUALITY.CROPID = 9) then
                :OWNERWASTEID
              else IT.OWNERID
            end CLIENTID,
            case
              when (QUALITY.CROPID = 9) then
                FORAGECLIENTS.CLIENTFULLNAME
              else OWNER.CLIENTFULLNAME
            end CLIENTFULLNAME,
            case
              when (QUALITY.CROPID = 9) then
                FORAGECLIENTS.CLIENTNAME
              else OWNER.CLIENTNAME
            end CLIENTNAME,
            case
              when (QUALITY.CROPID = 9) then
                FORAGECLIENTS.CLIENTTYPEID
              else coalesce(OWNER.CLIENTTYPEID,0)
            end CLIENTTYPEID,
            IT.PARTYID PARTYID, min(PARTIES.BEGINDATE) BEGINDATE,
            min(coalesce(PARTIES.ENDDATE,current_date)) ENDDATE,
            QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID, CROP.CROPNAME CROPNAME,
            QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME CROPFULLNAME,
            min(case
                  when (CROP.CROPTYPEID = 3) then
                    100
                  else CROP.CROPTYPEID
                end) CROPTYPEID,
            max(case
              when (:SHOWINGCROPYEAR = 1) then
                case
                  when (IT.STATE = -999) then
                    coalesce((select max(QUALITY_SELECT.HARVESTYEAR)
                       from IT IT2
                         left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
                       where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
                       group by IT2.REGISTERID),0)
                  else coalesce(YEARGR.QUALITYVALUE,coalesce(QUALITY_SELECT.HARVESTYEAR,0))
                end
              else 0
            end) CROPYEAR,
            min(coalesce(CROP.CROPGHI,0)) CROPGHI, min(coalesce(CLASS.CLASSGHI,0)) CLASSGHI,
            case
              when (PACKS.PACKID is null) then
                ''
              when (PACKS.PACKID = 1) then
                ''
              else PACKS.PACKNAME
            end PACKNAME,
-- Физический вес
            coalesce(sum(case
                  when (IT.REGDATE < :BEGINDATE) then
                    case
                      when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
                        IT.NET
                      when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.STATE > -1)) then
                        -IT.NET
                    end
                end),0) REMAINNET,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
                                 and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))) then
                             IT.NET
                           else 0
                         end),0) INCOMENETAVTO,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
                                 and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)) then
                             IT.NET
                           else 0
                         end),0) INCOMENETRRW,
            coalesce(sum(case
                           when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE in (0,4))
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))
                                          and ((IT.NET > 0) or (:SHOWTRANSFERINEXPENSE = 0))))) then
                             IT.NET
                           else 0
                         end),0) INCOMENET,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
                                 and (IT.NET > 0) and (IT.OPERATIONPARAM is null)) then
                             IT.NET
                         end),0) INCOMERENEVALNET,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and ((OPERATION.OPERATIONSUBTYPE = 1)
                                 and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,71,100)))) then
                             IT.NET
                           when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))
                                      and (IT.NET < 0) and (:SHOWTRANSFERINEXPENSE = 1))) then
                             -IT.NET
                         end),0) OUTCOMENET,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
                                 and (IT.NET < 0) and (IT.OPERATIONPARAM is null)) then
                             -IT.NET
                         end),0) OUTCOMERENEVALNET,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID in (9,22,71)) and (IT.STATE > -1)) then
                             IT.NET
                           else 0
                         end),0) DECREASENET,
            coalesce(sum(case
                           when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
                             IT.NET
                           when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.STATE > -1)) then
                             -IT.NET
                         end),0) COMEAFTERNET,
            coalesce(sum(case
                           when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE = 0)
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
                             IT.NET
                           else 0
                         end),0) ARRIVALNET,
            coalesce(sum(case
                           when (((IT.REGDATE <= :ENDDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE = 0)
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
                             IT.NET
                           else 0
                         end),0) BEGINNINGNET,
-- Условная усушка
            sum(case
                  when ((:PRELIMINARYSHRINKAGE = 1) and (IT.OPERATIONID = 9) and (IT.STATE = -999)) then
                    IT.NET
                  when (:PRELIMINARYSHRINKAGE = 2) then
                    ITTRANSPORT.HUMWEIGHT
                  when ((:PRELIMINARYSHRINKAGE in (1,2)) and (IT.OPERATIONID = 101)) then
                    -IT.NET
                  else 0
                end) HUMWEIGHT,
-- Зачетный вес
            coalesce(sum(case
                           when (IT.REGDATE < :BEGINDATE) then
                             case
                               when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
                                 IT.WEIGHT
                               when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,100))) then
                                 -IT.WEIGHT
                               when ((IT.OPERATIONID in (9,22,100)) and (QUALITYWASTE.CLASSID = 141)) then
                                 -IT.WEIGHT
                             end
                           end),0) COMEBEFOREWEIGHT,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
                                 and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))) then
                             IT.WEIGHT
                           else 0
                         end),0) INCOMEWEIGHTAVTO,
           coalesce(sum(case
                          when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
                                and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)) then
                            IT.WEIGHT
                          else 0
                        end),0) INCOMEWEIGHTRRW,
            coalesce(sum(case
                           when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE in (0,4))
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
                             IT.WEIGHT
                           else 0
                         end),0) INCOMEWEIGHT,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
                                 and (IT.NET > 0) and (IT.OPERATIONPARAM is null)) then
                             IT.WEIGHT
                         end),0) REINCOMEWEIGHT,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (22,100)))) then
                             IT.WEIGHT
                         end),0) OUTCOMEWEIGHT,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
                                 and (IT.NET < 0) and (IT.OPERATIONPARAM is null)) then
                             -IT.WEIGHT
                         end),0) REOUTCOMEWEIGHT,
            coalesce(sum(case
                          when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
                            IT.WEIGHT
                          when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,100))) then
                            -IT.WEIGHT
                          when ((IT.OPERATIONID in (9,22,100)) and (QUALITYWASTE.CLASSID = 141)) then
                            -IT.WEIGHT
                        end),0) COMEAFTERWEIGHT,
            coalesce(sum(case
                           when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE = 0)
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
                             IT.WEIGHT
                           else 0
                         end),0) ARRIVALWEIGHT,
            coalesce(sum(case
                           when (((IT.REGDATE <= :ENDDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
                                 and ((OPERATION.OPERATIONTYPE = 0)
                                      or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
                             IT.WEIGHT
                           else 0
                         end),0) BEGINNINGWEIGHT,
            coalesce(sum(case
                           when (OPERATION.OPERATIONTYPE in (0,4)) and (OPERATION.OPERATIONSUBTYPE = 0)
                                and (ITTRANSPORT.TRANSPORTTYPEID in (0,1)) then
                             IT.WEIGHT
                           else 0
                         end),0) ALLINCOMEWEIGHT,
-- Для декларации
            coalesce(sum(case
                           when (IT.REGDATE < :BEGINDATE) then
                             case
                               when (IT.OPERATIONID in (1,17,18,45)) then
                                 IT.WEIGHT
                               when ((IT.OPERATIONID in (5,9,22,46,70,71)) and (IT.STATE > -1)) then
                                -IT.WEIGHT
                             end
                         end),0) REMAINDECLARATION,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 1)) then
                             IT.WEIGHT
                           else 0
                         end),0) INCOMEDECLARATION,
            coalesce(sum(case
                           when ((IT.REGDATE >= :BEGINDATE) and ((IT.OPERATIONID in (5,9,22,70,71)) and (IT.STATE > -1))) then
                             IT.WEIGHT
                         end),0) OUTCOMEDECLARATION,
            coalesce(sum(case
                           when (IT.OPERATIONID in (1,17,18,45)) then
                             IT.WEIGHT
                           when ((IT.OPERATIONID in (5,9,22,46,70,71)) and (IT.STATE > -1)) then
                             -IT.WEIGHT
                         end),0) COMEAFTERDECLARATION,
            STORAGE.STORAGENAME,
            min(CROP.CROPNAME || ' ' || CLASS.CLASSNAME) CROPCLASSNAME
          from IT
            join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
            join PARTIES on (((PARTIES.PARTYID = IT.PARTYID) and (IT.PARTYID <> -1))
                             and ((((:INITIALDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,current_date))
                                    or (:ENDDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,current_date))
                                    or (PARTIES.BEGINDATE between :INITIALDATE and :ENDDATE)
                                    or (coalesce(PARTIES.ENDDATE,current_date) between :INITIALDATE and :ENDDATE))
                                   and (:ISCURRENT = 0) and (:ONLYREST <> 3))
                                  or (((PARTIES.BEGINDATE between :INITIALDATE and :ENDDATE)
                                       and (coalesce(PARTIES.ENDDATE,:ENDDATE) between :INITIALDATE and :ENDDATE))
                                      and (:ISCURRENT = 0) and (:ONLYREST = 3))
                                  or ((PARTIES.STATE = 1) and (:ISCURRENT = 1))))
            left join UWACTS on (UWACTS.UWORDERID = IT.UWORDERID)
            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
            left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
            left join CROP on (CROP.CROPID = QUALITY.CROPID)
            left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
            left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
            left join QUALITY_CROPFULLNAME_FORAGE(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, QUALITY_SELECT.FORAGECROPID,
                                                  (case
                                                     when (:CHECKFORAGECLASS = 1) then
                                                       QUALITY_SELECT.FORAGECLASSID
                                                     else null
                                                   end), 1) on (0=0)
            left join PACKS on (IT.PACKID = PACKS.PACKID)
            left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
            left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
            left join CLIENTS FORAGECLIENTS on (FORAGECLIENTS.CLIENTID = :OWNERWASTEID)
            left join QUALITY QUALITYWASTE on ((QUALITYWASTE.QUALITYID = IT.OPERATIONPARAM) and (IT.OPERATIONID = 22))
            left join QUALITYDATA YEARGR on ((YEARGR.QUALITYID = IT.BUHQUALITYID) and (YEARGR.QUALITYTYPEID = 6))
            left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
          where (((((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6)))
                   or ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)))
                   or ((OPERATION.OPERATIONSUBTYPE = 5) and (OPERATION.OPERATIONTYPE = 2)))
                  and (IT.STATE > -1))
                 or ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (2)) and (IT.STATE = -999)))
            and ((IT.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
            and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
--        and (((YEARGR.QUALITYVALUE = coalesce(:YEARHARVESTING,-1)) or (QUALITY_SELECT.HARVESTYEAR = coalesce(:YEARHARVESTING,-1)))
--             or (coalesce(:YEARHARVESTING,-1) = -1))
            and (IT.REGDATE <= :ENDDATE)
            and ((IT.STORAGEID = :STORAGEID) or (coalesce(:STORAGEID,-1) = -1))
            and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
            and ((CROP.CROPTYPEID <> 3) or (:SHOWWASTE = 1))
            and (not coalesce(IT.NET,0) = 0)
        group by 1, 2, 3, 4, IT.PARTYID, QUALITY.CROPID, QUALITY.CLASSID, CROP.CROPNAME, CROPFULLNAME, 16,
            QUALITY_SELECT.HARVESTYEAR, STORAGE.STORAGENAME
        order by 12, CROPFULLNAME, CROPYEAR, 3
      )
    group by CLIENTID, CLIENTFULLNAME, CLIENTNAME, CLIENTTYPEID, PARTYID,
        CROPID, CLASSID, CROPNAME, CROPFULLNAME, PACKNAME, CROPYEAR, STORAGENAME
    order by CROPTYPEID, CROPFULLNAME, CROPYEAR, PARTYID, CLIENTNAME

    into :OWNERID, :CLIENTFULLNAME, :CLIENTNAME, :CLIENTTYPEID,
         :PARTYID, PARTY_BEGINDATE, :PARTY_ENDDATE,
         :CROPID, :CLASSID, :CROPNAME, :CROPFULLNAME,
         :CROPTYPEID, :CROPYEAR, :CROPGHI, :CLASSGHI,
         :PACKNAME,
         :REMAINNET, :INCOMENETAVTO, :INCOMENETRRW, :INCOMENET,
         :REINCOME, :OUTCOME, :REOUTCOME, :DECREASENET, :RESTNET,
         :ARRIVALNET, :BEGINNINGNET, :HUMWEIGHT,
         :REMAINWEIGHT, :INCOMEWEIGHTAVTO, :INCOMEWEIGHTRRW, :INCOMEWEIGHT,
         :REINCOMEWEIGHT, :OUTCOMEWEIGHT, :REOUTCOMEWEIGHT, :RESTWEIGHT,
         :ARRIVALWEIGHT, :BEGINNINGWEIGHT, :ALLINCOMEWEIGHT,
         :REMAINDECLARATION, :INCOMEDECLARATION,
         :OUTCOMEDECLARATION, :COMEAFTERDECLARATION,
         :STORAGENAME, :CROPCLASSNAME

  do begin
    if (((ONLYREST = 3) and ((BEGINNINGNET <> 0) or (BEGINNINGWEIGHT <> 0)))
        or ((ONLYREST = 2) and ((ARRIVALNET <> 0) or (ARRIVALWEIGHT <> 0)))
        or ((ONLYREST = 1) and ((RESTNET <> 0) or (RESTWEIGHT <> 0)))
        or ((ONLYREST = 0) and ((REMAINNET <> 0) or (RESTNET <> 0) or (INCOMENET <> 0)
                                or (REINCOME <> 0) or (OUTCOME <> 0) or (REOUTCOME <> 0)
                                or (RESTNET <> 0) or (HUMWEIGHT <> 0)))) then begin
      if (SHOWINGCROPYEAR = 1) then
        CROPYEARFULLNAME = CROPFULLNAME || ' врожаю ' || CROPYEAR || ' року';
      else CROPYEARFULLNAME = CROPFULLNAME;

      suspend;

    end
  end

end

 

     Previous topic Chapter index Next topic