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

FORWARDER_INCOMEJOURNALS

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_INCOMEJOURNALS(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    APROVIDERID INTEGER,
    ASTATIONID INTEGER,
    ASTORAGEID INTEGER,
    ATRSUBTYPE INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    EXPORTACTID INTEGER,
    ACTNUMBER VARCHAR(10),
    REGDATE DATE,
    TRANSPORTNUMBER VARCHAR(25),
    STATIONID INTEGER,
    STATIONFULLNAME VARCHAR(128),
    PROVIDERID INTEGER,
    PROVIDERFULLNAME VARCHAR(100),
    OWNERID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    NETSUM BIGINT,
    TARESUM BIGINT,
    GROSSSUM BIGINT,
    INVOICENETSUM BIGINT,
    INVOICETARE BIGINT,
    INVOICEGROSS BIGINT,
    CROPID INTEGER,
    CROPFULLNAME VARCHAR(100),
    TRANSPORTSUBTYPE INTEGER,
    DEFICIT INTEGER,
    CLASSID INTEGER,
    STORAGEID INTEGER,
    INOVICEID INTEGER,
    OUTCOMEDATE DATE,
    PID INTEGER,
    INVOICENUMBER VARCHAR(31),
    STORAGENAME VARCHAR(100))
AS
declare variable ORDREC integer;
declare variable INVENTORYID integer = 0;
declare variable FREGDATE date;
declare variable FINOVICEID integer = 0;
declare variable FORDREC integer;
declare variable FEXPORTACTID integer;
declare variable FACTNUMBER varchar(10);
declare variable FTRANSPORTNUMBER varchar(25);
declare variable FSTATIONID integer;
declare variable FSTATIONFULLNAME varchar(128);
declare variable FPROVIDERID integer;
declare variable FPROVIDERFULLNAME varchar(100);
declare variable FOWNERID integer;
declare variable FOWNERFULLNAME varchar(100);
declare variable FNETSUM bigint = 0;
declare variable FTARESUM bigint;
declare variable FGROSSSUM bigint;
declare variable FINVOICENETSUM bigint;
declare variable FINVOICETARE bigint;
declare variable FINVOICEGROSS bigint;
declare variable FCROPID integer;
declare variable FCLASSID integer;
declare variable FSTORAGEID integer;
declare variable FCROPFULLNAME varchar(100);
declare variable FTRANSPORTSUBTYPE integer;
declare variable FPID integer;
declare variable FOUTCOMEDATE date;
declare variable FINVOICENUMBER varchar(31);
declare variable FSTORAGENAME varchar(100);
declare variable FINVENTORYID integer = 0;
begin
  for
    select IT.REGDATE, IT.INVOICEID,
      case
        when (TRANSPORTTYPE.TRANSPORTTYPEID = 1) then
          2
        else 1
      end ORDREC,
      EXPORTACTS.EXPORTACTID, EXPORTACTS.ACTNUMBER, ITTRANSPORT.TRANSPORTNUMBER,
      INVOICES.STATIONID, STATIONS.STATIONFULLNAME, IT.PROVIDERID,
      PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, IT.OWNERID, OWNER.CLIENTFULLNAME OWNERFULLNAME,
      coalesce(sum(IT.NET),0) NETSUM, coalesce(sum(IT.TARE),0) TARESUM, coalesce(sum(IT.GROSS),0) GROSSSUM,
      coalesce(sum(ITTRANSPORT.INVOICENET),0) INVOICENETSUM, coalesce(sum(ITTRANSPORT.INVOICETARE),0) INVOICETARE,
      coalesce(sum(ITTRANSPORT.INVOICEGROSS),0) INVOICEGROSS,
--      (sum(IT.NET) - sum(ITTRANSPORT.INVOICENET)) as DEFICIT,
      QUALITY.CROPID, QUALITY.CLASSID, IT.STORAGEID,
      QUALITY_CROPFULLNAME.CROPFULLNAME, TRANSPORTTYPE.TRANSPORTTYPEID,
      case
        when (TRANSPORTTYPE.TRANSPORTTYPEID = 1) then
          2
        else 1
      end PID,
      case
        when (TRANSPORTTYPE.TRANSPORTSUBTYPE = 1) then
          INVOICES.INVOICEDATE
      end OUTCOMEDATE,
      INVOICES.INVOICENUMBER, STORAGE.STORAGESHORTNAME,
      max(IT.INVENTORYID)
    from IT
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE,
                            case
                              when (coalesce(:ACROPID,-1) = -1) then
                                null
                              else :ACROPID
                            end,
                            case
                              when (coalesce(:ACLASSID,-1) = -1) then
                                null
                              else :ACLASSID
                            end,
                            case
                              when (coalesce(:ASTORAGEID,-1) = -1) then
                                null
                              else :ASTORAGEID
                            end
                           ) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join EXPORTACTITEMS on ((EXPORTACTITEMS.INVENTORYID = IT.INVENTORYID) and (EXPORTACTITEMS.OPERATIONID = 1))
        left join EXPORTACTS on (EXPORTACTS.EXPORTACTID = EXPORTACTITEMS.EXPORTACTID)
        left join clients PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
        left join clients OWNER on (OWNER.CLIENTID = IT.OWNERID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME (QUALITY.CROPID, CLASSID, null, 1) on (0=0)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
      where (IT.REGDATE between :BEGINDATE and :ENDDATE)
        and ((IT.OWNERID = coalesce(:AOWNERID,-1)) or (coalesce(:AOWNERID,-1) = -1))
        and ((IT.PROVIDERID = coalesce(:APROVIDERID,-1)) or (coalesce(:APROVIDERID,-1) = -1))
        and ((IT.STORAGEID = coalesce(:ASTORAGEID,-1)) or (coalesce(:ASTORAGEID,-1) = -1))
        and (IT.OPERATIONID = 1) and (IT.STATE >= 0)
        and ((QUALITY.CROPID = coalesce(:ACROPID,-1)) or (coalesce(:ACROPID,-1) = -1))
        and ((QUALITY.CLASSID = coalesce(:ACLASSID,-1)) or (coalesce(:ACLASSID,-1) = -1))
        and ((TRANSPORTTYPE.TRANSPORTSUBTYPE = coalesce(:ATRSUBTYPE,-1)) or (coalesce(:ATRSUBTYPE,-1) = -1))
        and ((INVOICES.STATIONID = coalesce(:ASTATIONID,-1)) or (coalesce(:ASTATIONID,-1) = -1))
        and (((EXPORTACTS.EXPORTACTID is not null)
             and ((TRANSPORTTYPE.TRANSPORTSUBTYPE in (0,1,2)) and (EXPORTACTS.OPERATIONID = 1))
             or (EXPORTACTS.EXPORTACTID is null)))
    group by IT.REGDATE, EXPORTACTS.ACTNUMBER, QUALITY_CROPFULLNAME.CROPFULLNAME,
        INVOICES.INVOICENUMBER, IT.INVOICEID, TRANSPORTSUBTYPE, EXPORTACTS.EXPORTACTID,
        TRANSPORTTYPE.TRANSPORTTYPEID, ITTRANSPORT.TRANSPORTNUMBER, INVOICES.STATIONID,
        STATIONS.STATIONFULLNAME, IT.PROVIDERID, PROVIDER.CLIENTFULLNAME,
        IT.OWNERID, OWNER.CLIENTFULLNAME, QUALITY.CROPID, QUALITY.CLASSID,
        IT.STORAGEID, STORAGE.STORAGESHORTNAME, OUTCOMEDATE
    order by IT.REGDATE, EXPORTACTS.ACTNUMBER, QUALITY.CROPID, IT.INVOICEID
    into :FREGDATE, :FINOVICEID, :FORDREC, :FEXPORTACTID, :FACTNUMBER, :FTRANSPORTNUMBER,
         :FSTATIONID, :FSTATIONFULLNAME, :FPROVIDERID, :FPROVIDERFULLNAME, :FOWNERID,
         :FOWNERFULLNAME, :FNETSUM, :FTARESUM, :FGROSSSUM, :FINVOICENETSUM, :FINVOICETARE,
         :FINVOICEGROSS, /*:DEFICIT,*/ :FCROPID, :FCLASSID, :FSTORAGEID, :FCROPFULLNAME,
         :FTRANSPORTSUBTYPE, :FPID, :FOUTCOMEDATE, :FINVOICENUMBER, :FSTORAGENAME, :FINVENTORYID
  do begin
    if (FINVENTORYID <> INVENTORYID) then begin
      if (INVENTORYID <> 0) then begin
        if ((INOVICEID = FINOVICEID) and (FINVOICENETSUM = 0) and (FNETSUM <> 0)) then
          DEFICIT = DEFICIT + FNETSUM;

        suspend;
      end

      REGDATE = FREGDATE;
      INOVICEID = FINOVICEID;
      ORDREC = FORDREC;
      EXPORTACTID = FEXPORTACTID;
      ACTNUMBER = FACTNUMBER;
      TRANSPORTNUMBER = FTRANSPORTNUMBER;
      STATIONID = FSTATIONID;
      STATIONFULLNAME = FSTATIONFULLNAME;
      PROVIDERID = FPROVIDERID;
      PROVIDERFULLNAME = FPROVIDERFULLNAME;
      OWNERID = FOWNERID;
      OWNERFULLNAME = FOWNERFULLNAME;
      NETSUM = FNETSUM;
      TARESUM = FTARESUM;
      GROSSSUM = FGROSSSUM;
      INVOICENETSUM = FINVOICENETSUM;
      INVOICETARE = FINVOICETARE;
      INVOICEGROSS = FINVOICEGROSS;
      CROPID = FCROPID;
      CLASSID = FCLASSID;
      STORAGEID = FSTORAGEID;
      CROPFULLNAME = FCROPFULLNAME;
      TRANSPORTSUBTYPE = FTRANSPORTSUBTYPE;
      PID = FPID;
      OUTCOMEDATE = FOUTCOMEDATE;
      INVOICENUMBER = FINVOICENUMBER;
      STORAGENAME = FSTORAGENAME;
      INVENTORYID = FINVENTORYID;

      if ((FNETSUM <> 0) and (FINVOICENETSUM <> 0)) then
        DEFICIT = FNETSUM - FINVOICENETSUM;
      else DEFICIT = 0;
    end
  end

  suspend;
end