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

ACC_FORM37_CLIENT

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_FORM37_CLIENT(
    BEGINDATE DATE,
    ENDDATE DATE,
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    AOWNERID INTEGER,
    ACONTRACTID INTEGER,
    STORAGEID INTEGER,
    STORINGID INTEGER,
    YEA INTEGER,
    SHOWINGCROPYEAR INTEGER,
    SHOWINGWRITEOFF INTEGER,
    PRELIMINARYSHRINKAGE INTEGER)
RETURNS (
    OWNERID INTEGER,
    CLIENTFULLNAME VARCHAR(100),
    CLIENTNAME VARCHAR(30),
    PROVIDERID INTEGER,
    PROVIDERFULLNAME VARCHAR(100),
    CROPID INTEGER,
    CLASSID INTEGER,
    CROPNAME VARCHAR(20),
    CROPFULLNAME VARCHAR(100),
    HARVESTYEAR INTEGER,
    CROPTYPEID INTEGER,
    PACKNAME VARCHAR(30),
    COMEBEFORENET INTEGER,
    INCOMENETAVTO INTEGER,
    INCOMEQUANTITYAVTO INTEGER,
    INCOMENETRRW INTEGER,
    INCOMEQUANTITYRRW INTEGER,
    INCOMENET INTEGER,
    INCOMERENEVALNET INTEGER,
    OUTCOMENET INTEGER,
    OUTCOMERENEVALNET INTEGER,
    DECREASENET INTEGER,
    WRITEOFFNET INTEGER,
    COMEAFTERNET INTEGER,
    INCOMEPACK INTEGER,
    OUTCOMEPACK INTEGER,
    COMEBEFOREWEIGHT INTEGER,
    INCOMEWEIGHTAVTO INTEGER,
    INCOMEWEIGHTRRW INTEGER,
    INCOMEWEIGHT INTEGER,
    INCOMEWEIGHTALL INTEGER,
    OUTCOMEWEIGHT INTEGER,
    WRITEOFFWEIGHT INTEGER,
    COMEAFTERWEIGHT INTEGER,
    HUMWEIGHT INTEGER,
    PARTYID INTEGER,
    OPERATIONID INTEGER,
    CONTRACTID INTEGER)
AS
declare variable ASTORAGEID integer;
declare variable AOPERATIONPARAM integer;
begin
  if (:STORAGEID = -1) then
    ASTORAGEID = null;
  else ASTORAGEID = :STORAGEID;

  for
    select
        IT.OWNERID, OWNER.CLIENTFULLNAME, OWNER.CLIENTNAME,
        QUALITY.CROPID, QUALITY.CLASSID, CROP.CROPNAME,
        case
          when (:SHOWINGCROPYEAR = 1) then
            QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME || ' урожая ' || coalesce(cast(YEARGR.QUALITYVALUE as integer),0) || ' г.'
          else QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME
        end CROPFULLNAME,
        case
          when (coalesce(:YEA,-1) = -1) then
            0
          else YEARGR.QUALITYVALUE
        end YEARGR,
        min(case
              when (CROP.CROPTYPEID = 3) then
                100
              else CROP.CROPTYPEID
            end) CROPTYPEID,
        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)))
                                 and (IT.STATE > -1)
                                ) then
                             IT.NET
                           when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5))
                                 and (not (IT.OPERATIONID in (64,100))) and (IT.STATE > -1)
                                ) then
                             -IT.NET
                           when ((IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1) and (IT.STATE > -1)) then
                             -abs(IT.NET)
                         end
                     end),0) COMEBEFORENET,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (IT.STATE > -1) 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(count(case
                         when ((IT.REGDATE >= :BEGINDATE) and (IT.STATE > -1) and (OPERATION.OPERATIONTYPE in (0,4))
                               and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))
                              ) then
                           IT.INVOICEID
                       end),0) INCOMEQUANTITYAVTO,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (IT.STATE > -1) and (OPERATION.OPERATIONTYPE in (0,4))
                             and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)
                            ) then
                         IT.NET
                       else 0
                     end),0) INCOMENETRRW,
        coalesce(count(case
                         when ((IT.REGDATE >= :BEGINDATE) and (IT.STATE > -1) and (OPERATION.OPERATIONTYPE in (0,4))
                               and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)
                              ) then
                           IT.INVOICEID
                       end),0) INCOMEQUANTITYRRW,
        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 = 0)) and (IT.STATE > -1)
                             and ((OPERATION.OPERATIONTYPE in (0,4))
                                  or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null)))
                                 )
                            ) then
                         IT.NET
                       else 0
                     end),0) INCOMENET,
        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,64,71,100)))
                            ) 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 ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1)) then
                         abs(IT.NET)
                       else 0
                     end),0) WRITEOFFNET,
        coalesce(sum(case
                       when (((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6)))
                             and (IT.STATE > -1)
                            ) then
                         IT.NET
                       when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5))
                             and (not (IT.OPERATIONID in (64,100))) and (IT.STATE > -1)
                            ) then
                         -IT.NET
                       when ((IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1) and (IT.STATE > -1)) then
                         -abs(IT.NET)
                     end),0) COMEAFTERNET,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0)
                             and (OPERATION.OPERATIONTYPE in (0,4,6))
                            ) then
                         IT.CARGOQUANTITY
                     end),0) INCOMEPACK,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 1)
                             and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID <> 100)
                            ) then
                         IT.CARGOQUANTITY
                     end),0) OUTCOMEPACK,
        coalesce(sum(case
                       when (IT.REGDATE < :BEGINDATE) then
                         case
                           when (((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))
                                  and (IT.STATE <> -999)
                                 )
                                 or ((IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1))
                                ) then
                             IT.WEIGHT
                           when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5))
                                 and (IT.OPERATIONID not in (9,22,64,100))
                                ) then
                             -IT.WEIGHT
                           when (((IT.OPERATIONID in (22,100)) and (QUALITYWASTE.CLASSID = 141))
                                 or (IT.OPERATIONID in (9))
                                ) 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))
                                     )
                                 )
                             and (IT.STATE <> -999)
                            ) then
                         IT.WEIGHT
                       else 0
                     end),0) INCOMEWEIGHT,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0)
                             and ((OPERATION.OPERATIONTYPE in (0,4))
                                  or ((OPERATION.OPERATIONTYPE = 6) and (IT.WEIGHT > 0))
                                 )
                             and (IT.STATE <> -999)
                            ) then
                         IT.WEIGHT
                       else 0
                     end),0) INCOMEWEIGHTALL,
        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,64,100))
                            ) then
                         IT.WEIGHT
                       when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE = 6) and (IT.WEIGHT < 0)) then
                         -IT.WEIGHT
                     end),0) OUTCOMEWEIGHT,
        coalesce(sum(case
                       when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1)) then
                         -IT.WEIGHT
                     end),0) WRITEOFFWEIGHT,
        coalesce(sum(case
                       when (((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))
                              and (IT.STATE <> -999)
                             )
                             or ((IT.OPERATIONID = 64) and (:SHOWINGWRITEOFF = 1))
                            ) then
                         IT.WEIGHT
                       when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5))
                             and (IT.OPERATIONID not in (9,22,64,100))
                            ) then
                         -IT.WEIGHT
                       when ((IT.OPERATIONID in (9,22,100)) and (QUALITYWASTE.CLASSID = 141)
                            ) then
                         -IT.WEIGHT
--                       when ((IT.OPERATIONID in (9)) and (IT.STATE = -999)) then
--                         -IT.WEIGHT
                      end),0) COMEAFTERWEIGHT,
        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,
        IT.PARTYID,
        max(IT.CONTRACTID)
      from IT
/*
        join GETWORKPARTIES(:ISCURRENT,
                             case
                               when (:ISCURRENT = 0) then
                                 IT.REGDATE
                               else :PARTYDATE
                             end, null, null, :ASTORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
*/
        join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        join PARTIES on (((PARTIES.PARTYID = IT.PARTYID) and (IT.PARTYID <> -1))
                         and ((((:BEGINDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE, current_date))
                                or (:ENDDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE, current_date))
                                or (PARTIES.BEGINDATE between :BEGINDATE and :ENDDATE)
                                or (coalesce(PARTIES.ENDDATE, current_date) between :BEGINDATE and :ENDDATE))
                               and (:ISCURRENT = 0))
                              or (((PARTIES.BEGINDATE between :BEGINDATE and :ENDDATE)
                                   and (coalesce(PARTIES.ENDDATE, :ENDDATE) between :BEGINDATE and :ENDDATE))
                                  and (:ISCURRENT = 0))
                              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 QUALITY_SELECT(QUALITY.QUALITYID) on (0=0)
        left join QUALITY_CROPFULLNAME_FORAGE(QUALITY.CROPID, QUALITY.CLASSID, null, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0 = 0)
        left join QUALITYDATA YEARGR on ((YEARGR.QUALITYID = IT.LABQUALITYID) and (YEARGR.QUALITYTYPEID = 6))
        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 QUALITY QUALITYWASTE on ((QUALITYWASTE.QUALITYID = IT.OPERATIONPARAM) and (IT.OPERATIONID = 22))
      where (((not (IT.OPERATIONID in (45,46)))
              and (((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6)))
                   or ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)))
                  )
             )
             or ((IT.OPERATIONID in (45,46,64)) and (IT.OPERATIONPARAM is null))
            )
        and (IT.REGDATE <= :ENDDATE) and (not (IT.STATE in (-5,-3,-1)))
        and (not (coalesce(IT.NET,0) = 0)) and (coalesce(QUALITY.CLASSID,-1) <> 41)
        and ((IT.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((IT.CONTRACTID = :ACONTRACTID) or (coalesce(:ACONTRACTID,-1) = -1))
        and ((YEARGR.QUALITYVALUE = coalesce(:YEA,-1)) or (coalesce(:YEA,-1) = -1))
        and ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
        and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
    group by IT.OWNERID, OWNER.CLIENTFULLNAME, OWNER.CLIENTNAME,
        QUALITY.CROPID, QUALITY.CLASSID, CROP.CROPNAME, CROPFULLNAME, 10, 8,
        IT.PARTYID
    into :OWNERID, :CLIENTFULLNAME, :CLIENTNAME, :CROPID, :CLASSID, :CROPNAME,
         :CROPFULLNAME, :HARVESTYEAR, :CROPTYPEID, :PACKNAME,
         :COMEBEFORENET, :INCOMENETAVTO, :INCOMEQUANTITYAVTO, :INCOMENETRRW, :INCOMEQUANTITYRRW, :INCOMERENEVALNET, :INCOMENET,
         :OUTCOMENET, :OUTCOMERENEVALNET, :DECREASENET, :WRITEOFFNET, :COMEAFTERNET,
         :INCOMEPACK, :OUTCOMEPACK,
         :COMEBEFOREWEIGHT, :INCOMEWEIGHTAVTO, :INCOMEWEIGHTRRW, :INCOMEWEIGHT, :INCOMEWEIGHTALL,
         :OUTCOMEWEIGHT, :WRITEOFFWEIGHT, :COMEAFTERWEIGHT,
         :HUMWEIGHT, :PARTYID, :CONTRACTID

  do
    if ((COMEBEFORENET <> 0) or (INCOMENET <> 0) or (INCOMERENEVALNET <> 0)
        or (OUTCOMENET <> 0) or (OUTCOMERENEVALNET <> 0) or (DECREASENET <> 0)
        or (COMEAFTERNET <> 0) or (COMEBEFOREWEIGHT <> 0) or (INCOMEWEIGHT <> 0)
        or (OUTCOMEWEIGHT <> 0) or (COMEAFTERWEIGHT <> 0)) then
      suspend;
end