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

LOGISTICS_JOURNAL

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

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

Описание

Журнал модуля "Логистик"

Определение

CREATE PROCEDURE LOGISTICS_JOURNAL(
    BEGINDATE DATE,
    ENDDATE DATE,
    ASTORAGEID INTEGER,
    SHOWALLOWNERS INTEGER,
    SHOWNOTRECEIVED INTEGER,
    SHOWWEIGHTED INTEGER,
    SHOWREMOTERECORDS INTEGER,
    SHOWDELETED INTEGER,
    AOWNERID INTEGER,
    ASENDERID INTEGER,
    ASTATIONID INTEGER,
    ACROPID INTEGER,
    ATRANSPORTNUMBER VARCHAR(10),
    AINVOICENUMBER VARCHAR(10))
RETURNS (
    OWNERNAME VARCHAR(100),
    PROVIDERNAME VARCHAR(100),
    TRANSPORTNUMBER VARCHAR(31),
    INVOICENUMBER VARCHAR(30),
    INVOICEDATE DATE,
    SAMPLEROUTDATE DATE,
    SENDERNAME VARCHAR(100),
    STATIONNAME VARCHAR(128),
    CROPFULLNAME VARCHAR(100),
    GMOPRESENCE VARCHAR(15),
    CERTIFICATENUMBER INTEGER,
    CERTIFICATEDATE TIMESTAMP,
    SNNUMBER VARCHAR(30),
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    DEVIATIONTIME VARCHAR(50),
    TARETIME TIMESTAMP,
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    GROSSDEFICITE INTEGER,
    NETDEFICITE INTEGER,
    SCALETYPENAME VARCHAR(10),
    DEVIATIONDESCRIPTION VARCHAR(100),
    PERMISSIONDESCRIPTION VARCHAR(100),
    PERMISSIONTIME VARCHAR(50),
    PERMISSIONUSERNAME VARCHAR(100),
    REMARK VARCHAR(100),
    WAITINGTIME VARCHAR(20),
    OVERTIME TIME,
    CERTIFICATEPRESENCE VARCHAR(10),
    SNPRESENCE VARCHAR(10),
    CONFIRMATIONTIME TIMESTAMP,
    INVENTORYID INTEGER,
    REGDATE DATE,
    INVOICEID INTEGER,
    GROSSTIME TIMESTAMP,
    OWNERID INTEGER,
    PROVIDERID INTEGER,
    SAMPLERQUALITYID INTEGER,
    LABQUALITYID INTEGER,
    STORAGEID INTEGER,
    CONTRACTID INTEGER,
    STATE INTEGER,
    SENDERQUALITYID INTEGER,
    EXPORTERID INTEGER,
    STATIONID INTEGER,
    SENDERID INTEGER,
    AGREEMENTNUMBER VARCHAR(10),
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    SCALESTYPEID INTEGER,
    GMOPRESENCEID INTEGER,
    TRANSPORTTYPEID INTEGER,
    FWAITINGDAYS INTEGER)
AS
declare variable FDEVIATIONTIME varchar(24);
declare variable FDEVIATIONDESCRIPTION varchar(50);
declare variable FPERMISSIONTIME varchar(24);
declare variable FPERMISSIONUSERNAME varchar(50);
declare variable FWAITING_DAYS integer;
declare variable FWAITINGSECONDS integer;
declare variable I integer;
declare variable HOURS integer;
declare variable MINUTES integer;
begin
  for
    select IT.INVENTORYID, IT.REGDATE, IT.INVOICEID, IT.GROSS, IT.TARE, IT.NET,
        IT.TARETIME, IT.GROSSTIME, IT.OWNERID, IT.PROVIDERID, IT.SAMPLERQUALITYID,
        IT.LABQUALITYID, IT.STORAGEID, IT.CONTRACTID, IT.STATE,
        ITTRANSPORT.SAMPLEROUTDATE, ITTRANSPORT.TRANSPORTNUMBER, ITTRANSPORT.INVOICEGROSS,
        ITTRANSPORT.INVOICETARE, ITTRANSPORT.INVOICENET, ITTRANSPORT.SENDERQUALITYID,
        ITTRANSPORT.TRANSPORTTYPEID,
        (IT.GROSS - ITTRANSPORT.INVOICEGROSS) GROSSDEFICITE,
        (IT.NET - ITTRANSPORT.INVOICENET) NETDEFICITE, CLIENTS.CLIENTFULLNAME
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
      where (IT.REGDATE between (:BEGINDATE - 10) and :ENDDATE)
            and ((coalesce(CLIENTS.DEVIATIONCONTROL,0) > 32767) or (:SHOWALLOWNERS = 1))
            and (ITTRANSPORT.TRANSPORTTYPEID = 2) and (IT.PARENTID <= 0) and (IT.OPERATIONID = 1)
            and ((IT.STORAGEID = :ASTORAGEID) or (:ASTORAGEID = -1))
            and ((IT.STATE <= -100)
                 or (((IT.STATE = 0) and (IT.NET is null))
                     or ((IT.REGDATE between :BEGINDATE and :ENDDATE)
                         and (((:SHOWWEIGHTED = 1) and (IT.STATE = 0) and (IT.NET is not null))
                              or ((:SHOWREMOTERECORDS = 1) and (IT.STATE in (-3,-2)))
                              or ((:SHOWDELETED = 1) and (IT.STATE = -1))
                             )
                        )
                    )
                    and (:SHOWNOTRECEIVED = 0)
                )
            and ((coalesce(:AOWNERID,-1) = -1) or (IT.OWNERID = :AOWNERID))
    order by IT.STATE, IT.NET, IT.REGDATE, IT.INVENTORYID
    into :INVENTORYID, :REGDATE, :INVOICEID, :GROSS, :TARE, :NET, :TARETIME,
         :GROSSTIME, :OWNERID, :PROVIDERID, :SAMPLERQUALITYID, :LABQUALITYID,
         :STORAGEID, :CONTRACTID, :STATE, :SAMPLEROUTDATE, :TRANSPORTNUMBER,
         :INVOICEGROSS, :INVOICETARE, :INVOICENET, :SENDERQUALITYID,
         :TRANSPORTTYPEID, :GROSSDEFICITE, :NETDEFICITE, :OWNERNAME
  do begin
    select INVOICES.INVOICENUMBER, INVOICES.INVOICEDATE, INVOICES.EXPORTERID,
        INVOICES.STATIONID, INVOICES.AGREEMENTNUMBER, INVOICES.SCALESTYPEID,
        INVOICES.CONFIRMATIONTIME,
        PROVIDER.CLIENTFULLNAME, SENDER.CLIENTID, SENDER.CLIENTFULLNAME,
        STATIONS.STATIONFULLNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
        QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID,
        QUALITY_SELECT.QUALITYSERTIFICATE, QUALITY_SELECT.GMOPRESENCEID,
        (select QUALITYTYPEVALUES.QUALITYNAME
           from QUALITYTYPEVALUES
           where (QUALITYTYPEVALUES.QUALITYTYPEID = 3252)
               and (QUALITYTYPEVALUES.QUALITYVALUE = QUALITY_SELECT.GMOPRESENCEID)
        ),
        SENDERQUALITY.CREATETIME, SENDERQUALITY.SNNUMBER, SCALESTYPE.SCALESTYPENAME
      from INVOICES
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = :PROVIDERID)
        left join CLIENTS SENDER on (SENDER.CLIENTID = INVOICES.SENDERID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join QUALITY_CROPFULLNAME_BYID(:SAMPLERQUALITYID, 1) on (0=0)
        left join QUALITY_SELECT(:SAMPLERQUALITYID) on (0=0)
        left join QUALITY on (QUALITY.QUALITYID = :LABQUALITYID)
        left join QUALITY SENDERQUALITY on (SENDERQUALITY.QUALITYID = :SENDERQUALITYID)
        left join SCALESTYPE on (SCALESTYPE.SCALESTYPEID = INVOICES.SCALESTYPEID)
      where (INVOICES.INVOICEID = :INVOICEID)
    into :INVOICENUMBER, :INVOICEDATE, :EXPORTERID,
         :STATIONID, :AGREEMENTNUMBER, :SCALESTYPEID, :CONFIRMATIONTIME,
         :PROVIDERNAME, :SENDERID, :SENDERNAME,
         :STATIONNAME, :CROPFULLNAME,
         :CROPID, :CLASSID, :SORTID,
         :CERTIFICATENUMBER, :GMOPRESENCEID, :GMOPRESENCE,
         :CERTIFICATEDATE, :SNNUMBER, :SCALETYPENAME;

    DEVIATIONTIME = '';
    DEVIATIONDESCRIPTION = '';
    PERMISSIONTIME = '';
    PERMISSIONUSERNAME = '';
    WAITINGTIME = '';
    FWAITINGDAYS = null;
    FWAITINGSECONDS = null;
    HOURS = null;
    MINUTES = null;
    I = 0;

    for
      select DEVIATIONTIME.DATESTR || ' ' || left(cast(cast(DEVIATION.DEVIATIONDATETIME as time) as char(13)),5),
          DEVIATIONTYPE.DEVIATIONTYPESHORTNAME,
          PERMISSIONTIME.DATESTR || ' ' || left(cast(cast(DEVIATION.PERMITDATETIME as time) as char(13)),5),
          coalesce(substring(PERMITUSER.USERNAME from 1 for 1) || '.','') || PERMITUSER.USERLASTNAME,
          case
            when (DEVIATION.PERMITDATETIME is not null) then
              cast(DEVIATION.PERMITDATETIME as date) - cast(DEVIATION.DEVIATIONDATETIME as date)
            else null
          end,
          case
            when (DEVIATION.PERMITDATETIME is not null) then
              cast(DEVIATION.PERMITDATETIME as time) - cast(DEVIATION.DEVIATIONDATETIME as time)
            else null
          end
        from DEVIATION
          left join DATE_TO_STR(DEVIATION.DEVIATIONDATETIME) DEVIATIONTIME on (0=0)
          left join DEVIATIONTYPE on (DEVIATIONTYPE.DEVIATIONTYPEID = DEVIATION.DEVIATIONTYPEID)
          left join DATE_TO_STR(DEVIATION.PERMITDATETIME) PERMISSIONTIME on (0=0)
          left join USERS PERMITUSER on (PERMITUSER.USERID = DEVIATION.PERMITUSERID)
        where (DEVIATION.INVOICEID = :INVOICEID)
      into :FDEVIATIONTIME, :FDEVIATIONDESCRIPTION, :FPERMISSIONTIME, :FPERMISSIONUSERNAME,
           :FWAITINGDAYS, :FWAITINGSECONDS
    do begin
      if (I > 0) then begin
        DEVIATIONTIME = DEVIATIONTIME || ascii_char(13);
        DEVIATIONDESCRIPTION = DEVIATIONDESCRIPTION || ascii_char(13);
        PERMISSIONTIME = PERMISSIONTIME || ascii_char(13);
        PERMISSIONUSERNAME = PERMISSIONUSERNAME || ascii_char(13);
        WAITINGTIME = WAITINGTIME || ascii_char(13);
      end

      I = I + 1;
      DEVIATIONTIME = DEVIATIONTIME || FDEVIATIONTIME;
      DEVIATIONDESCRIPTION = DEVIATIONDESCRIPTION || FDEVIATIONDESCRIPTION;
      PERMISSIONTIME = PERMISSIONTIME || FPERMISSIONTIME;
      PERMISSIONUSERNAME = PERMISSIONUSERNAME || FPERMISSIONUSERNAME;

      if (FWAITINGSECONDS <= 0) then
        FWAITINGSECONDS = FWAITINGSECONDS + ((FWAITINGDAYS * 24) * 3600);
      else
        if (FWAITINGDAYS > 0) then
          FWAITINGSECONDS = FWAITINGSECONDS + ((FWAITINGDAYS * 24) * 3600);

      HOURS = trunc(FWAITINGSECONDS / 3600);
      MINUTES = round((cast(FWAITINGSECONDS as numeric(12,3)) - (HOURS * 3600)) / 60);
      WAITINGTIME = WAITINGTIME || trim(trailing ' ' from cast(HOURS as char(5))) || ':';

      if (MINUTES < 10) then
        WAITINGTIME = WAITINGTIME || '0' || cast(MINUTES as char(2));
      else WAITINGTIME = WAITINGTIME || cast(MINUTES as char(3));
    end

    if (LABQUALITYID is not null) then
      if (CERTIFICATENUMBER is not null) then
        CERTIFICATEPRESENCE = '+';
      else CERTIFICATEPRESENCE = '—';
    else CERTIFICATEPRESENCE = '';

    if (LABQUALITYID is not null) then
      if (SNNUMBER is not null) then
        SNPRESENCE = '+';
      else SNPRESENCE = '—';
    else SNPRESENCE = '';

    if (((coalesce(:ASENDERID,-1) = -1) or (:SENDERID = :ASENDERID))
        and ((coalesce(:ASTATIONID,-1) = -1) or (:STATIONID = :ASTATIONID))
        and ((coalesce(:ACROPID,-1) = -1) or (:CROPID = :ACROPID))
        and ((:ATRANSPORTNUMBER = '') or (:TRANSPORTNUMBER containing :ATRANSPORTNUMBER))
        and ((AINVOICENUMBER = '') or (:INVOICENUMBER containing :AINVOICENUMBER))
       ) then
      suspend;
  end
end