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

REGISTERS_SELECT_TTNS

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

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

Описание

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

Определение

CREATE PROCEDURE REGISTERS_SELECT_TTNS(
    DATEBEGIN DATE,
    DATEEND DATE,
    CROPID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    GROUPBYPROV SMALLINT,
    INCLASSID INTEGER,
    AZHS INTEGER,
    ASTORAGEID INTEGER,
    AUTONUMBERING INTEGER,
    NOTDIVIDENUMBERING INTEGER)
RETURNS (
    BASHUM NUMERIC(5,2),
    BASDIRT NUMERIC(5,2),
    BASSC NUMERIC(5,2),
    BUHQUALITYID INTEGER,
    HUM NUMERIC(5,2),
    DIRT NUMERIC(5,2),
    SCREEN NUMERIC(5,2),
    PROVIDERNAME VARCHAR(30),
    OWNERNAME VARCHAR(30),
    CROPFULLNAME VARCHAR(100),
    NET INTEGER,
    WEIGHT INTEGER,
    REGDATE DATE,
    REGNUM INTEGER,
    ACROPID INTEGER,
    CLASSID INTEGER,
    INCOMECOST NUMERIC(12,4),
    DRYCOST NUMERIC(12,4),
    CLEANCOST NUMERIC(12,4),
    CONTRACTID INTEGER,
    CONTRACTNUMBER VARCHAR(31),
    REGISTERID INTEGER,
    STORTICKNUM INTEGER,
    STORTICKBLANKNUM VARCHAR(6),
    STORTICKBLANKSERIE VARCHAR(2),
    OWNERID INTEGER,
    QUALITYNUMBER INTEGER,
    PROVIDER INTEGER,
    INVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    TRANSPORTTYPE INTEGER,
    INNERRELOCATION SMALLINT,
    HUMWEIGHT INTEGER,
    DRYWEIGHT INTEGER,
    SCREENWEIGHT INTEGER,
    ZHS SMALLINT,
    STORAGEID INTEGER,
    STORAGENAME VARCHAR(15),
    HUMIDITYSTATE NUMERIC(4,2),
    DIRTSTATE NUMERIC(4,2))
AS
declare variable TEMPQ integer;
declare variable TEMPDATE date;
declare variable TEMPOWN integer;
declare variable TEMPCONTR integer;
declare variable TEMPPROV integer;
declare variable TEMPCLASS integer;
declare variable TEMPHUM numeric(5,2);
declare variable TEMPDIRT numeric(5,2);
declare variable MAXREGNUM integer;
declare variable MAXREGNUMZD integer;
declare variable MAXREGNUMAVTO integer;
declare variable REGID integer;
declare variable TEMPREGID integer;
declare variable STATIONID integer;
declare variable TEMPSTATIONID integer;
declare variable TEMPTRANSPORTTYPE integer;
declare variable TEMPHUMIDITYSTATE numeric(4,2);
declare variable TEMPDIRTSTATE numeric(4,2);
declare variable SORTINDEX integer;
begin
  TEMPQ = 0;
  TEMPOWN = 0;
  TEMPCONTR = 0;
  TEMPPROV = 0;
  TEMPDATE = null;
  TEMPCLASS = null;
  TEMPHUM = 0;
  TEMPDIRT = 0;
  TEMPREGID = 0;
  TEMPSTATIONID = 0;
  TEMPTRANSPORTTYPE = 0;

  if (:NOTDIVIDENUMBERING = 0) then begin
    select first 1
        INVOICEREGISTERS.REGISTERNUMBER REGISTERNUMBER, INVOICEREGISTERS.INVOICEREGISTERID INVOICEREGISTERID
      from IT
        join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        join INVOICEREGISTERS on ((INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID) and (INVOICEREGISTERS.REGISTERNUMBER is not null))
      where ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1)) -- new
        and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))
        and (INVOICEREGISTERS.REGISTERNUMBER is not null)
    order by INVOICEREGISTERS.INVOICEREGISTERID desc
    into :MAXREGNUMAVTO, :REGID;

    select first 1
        INVOICEREGISTERS.REGISTERNUMBER, INVOICEREGISTERS.INVOICEREGISTERID
      from IT
        join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        join INVOICEREGISTERS on ((INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID) and (INVOICEREGISTERS.REGISTERNUMBER is not null))
      where ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1)) -- new
        and (not (ITTRANSPORT.TRANSPORTTYPEID in (0,1,3)))
        and (INVOICEREGISTERS.REGISTERNUMBER is not null)
--        and (ITTRANSPORT.TRANSPORTTYPEID = 2)
    order by INVOICEREGISTERS.INVOICEREGISTERID desc
    into :MAXREGNUMZD, :REGID;
  end
  else
    select first 1
        INVOICEREGISTERS.REGISTERNUMBER, INVOICEREGISTERS.INVOICEREGISTERID
      from IT
        join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
      where ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))  -- new
        and (INVOICEREGISTERS.REGISTERNUMBER is not null)
      order by INVOICEREGISTERS.INVOICEREGISTERID desc
      into :MAXREGNUM, :REGID;

  for
    select IT.REGDATE, INVOICEREGISTERS.REGISTERNUMBER, TRANSPORTTYPE.TRANSPORTSUBTYPE,
        INVOICEREGISTERS.INVOICEREGISTERID, INVOICEREGISTERS.ZHS, IT.OWNERID,
        IT.CONTRACTID, FUL.CROPFULLNAME, IT.INVOICEID, IT.BUHQUALITYID, LABQD.QUALITYNUMBER,
        LABQD.HUMIDITY, GET_GRAINSTATE.HUMIDITYSTATE, LABQD.DIRT, GET_GRAINSTATE.DIRTSTATE,
        case
          when (CROP.CROPTYPEID = 5) then
            LABQD.OILSEED
          else LABQD.SCREENING
        end SCREENING,
        IT.PROVIDERID, PROVIDERS.CLIENTNAME, OWNERS.CLIENTNAME,
        sum(IT.NET), sum(IT.WEIGHT), LABQD.CROPID, LABQD.CLASSID,
        BASQ.HUMIDITY, BASQ.DIRT, BASQ.SCREENING,
        INVOICEREGISTERS.INCOMECOST, INVOICEREGISTERS.DRYCOST, INVOICEREGISTERS.UNDERWORKCOST,
        CONTRACTS.CONTRACTNUMBER, STORAGETICKETS.NUMBER, STORAGETICKETS.BLANKSERIES,
        STORAGETICKETS.BLANKNUMBER, INVOICES.INVOICENUMBER, INVOICES.STATIONID,
        case
          when (CS.STORAGEID is null) then
            0
          else 1
        end INNERRELOCATION,
        STORAGE.STORAGEID, STORAGE.STORAGESHORTNAME,                                              -- new
        sum(ITTRANSPORT.HUMWEIGHT), sum(ITTRANSPORT.DIRTWEIGHT), sum(ITTRANSPORT.SCREENWEIGHT),
        case
          when (IT.REGISTERID is not null) then
            1
          else 0
        end SORTINDEX
    from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join INVOICES on (IT.INVOICEID = INVOICES.INVOICEID)
        left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
        left join QUALITY_SELECT(IT.BUHQUALITYID) LABQD on (0=0)
        left join QUALITYDATA_SELECT_MIN(INVOICEREGISTERS.QUALITYID) BASQ on (0=0)
        left join QUALITY_CROPFULLNAME(LABQD.CROPID, LABQD.CLASSID, null, 1) FUL on (0=0)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
        left join CLIENTS OWNERS on (OWNERS.CLIENTID = IT.OWNERID)
        left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = IT.PROVIDERID)
        left join CLIENTS CS on (CS.CLIENTID = INVOICES.SENDERID)
        Left Join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join CONTRACTS on (CONTRACTS.CONTRACTID = IT.CONTRACTID)
        left join STORAGETICKETS on (STORAGETICKETS.STORAGETICKETID = INVOICEREGISTERS.STORAGETICKETID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join CROP on (CROP.CROPID = LABQD.CROPID)
        left join GET_GRAINSTATE(LABQD.CROPID, LABQD.CLASSID, LABQD.HUMIDITY, LABQD.DIRT) on (0=0)
      where (IT.REGDATE between :DATEBEGIN and :DATEEND) and (IT.OPERATIONID not in (17,4,18,49,50))
        and (IT.NET is not null) and (IT.STATE >= 0) and (IT.PARENTID <> -1)
        and ((IT.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))  -- new
        and (STORAGE.STORAGETYPEID = 1) and (OPERATION.OPERATIONSUBTYPE = 0)
        and ((LABQD.CROPID = :CROPID) or (coalesce(:CROPID,-1) = -1))
        and ((LABQD.CLASSID = :INCLASSID) or (coalesce(:INCLASSID,-1) = -1))
        and ((LABQD.HUMIDITY is not null) or (LABQD.DIRT is not null))
    group by IT.REGDATE, INVOICEREGISTERS.REGISTERNUMBER, TRANSPORTTYPE.TRANSPORTSUBTYPE,
        INVOICEREGISTERS.ZHS, INVOICEREGISTERS.INVOICEREGISTERID, IT.OWNERID, IT.CONTRACTID,
        FUL.CROPFULLNAME, IT.INVOICEID, IT.BUHQUALITYID, LABQD.QUALITYNUMBER, LABQD.DIRT,
        LABQD.HUMIDITY, GET_GRAINSTATE.HUMIDITYSTATE, GET_GRAINSTATE.DIRTSTATE, 16, IT.PROVIDERID,
        PROVIDERS.CLIENTNAME, OWNERS.CLIENTNAME, LABQD.CROPID, LABQD.CLASSID, BASQ.HUMIDITY, BASQ.DIRT,
        INVOICEREGISTERS.INCOMECOST, INVOICEREGISTERS.DRYCOST, INVOICEREGISTERS.UNDERWORKCOST,
        CONTRACTS.CONTRACTNUMBER, BASQ.SCREENING, STORAGETICKETS.NUMBER, STORAGETICKETS.BLANKSERIES,
        STORAGETICKETS.BLANKNUMBER, INVOICES.INVOICENUMBER, INVOICES.STATIONID, STORAGE.STORAGEID,
        STORAGE.STORAGESHORTNAME, INNERRELOCATION, SORTINDEX
    order by SORTINDEX, TRANSPORTTYPE.TRANSPORTSUBTYPE, IT.OWNERID, IT.CONTRACTID,
        LABQD.CROPID, LABQD.CLASSID, GET_GRAINSTATE.HUMIDITYSTATE, GET_GRAINSTATE.DIRTSTATE
    into :REGDATE, :REGNUM, :TRANSPORTTYPE, :REGISTERID, :ZHS, :OWNERID, :CONTRACTID,
         :CROPFULLNAME, :INVOICEID, :BUHQUALITYID, :QUALITYNUMBER, :HUM, :HUMIDITYSTATE,
         :DIRT, :DIRTSTATE, :SCREEN, :PROVIDER, :PROVIDERNAME, :OWNERNAME, :NET,
         :WEIGHT, :ACROPID, :CLASSID, :BASHUM, :BASDIRT, :BASSC, :INCOMECOST, :DRYCOST,
         :CLEANCOST, :CONTRACTNUMBER, :STORTICKNUM, :STORTICKBLANKSERIE, :STORTICKBLANKNUM,
         :INVOICENUMBER, :STATIONID, :INNERRELOCATION, :STORAGEID, :STORAGENAME,
         :HUMWEIGHT, :DRYWEIGHT, :SCREENWEIGHT, :SORTINDEX

  do begin
    if (:REGISTERID is null) then begin
      if (:AUTONUMBERING = 0) then
        REGNUM = QUALITYNUMBER;
      else begin
        if (((:TEMPOWN <> :OWNERID) or (:TEMPDATE <> :REGDATE)
             or ((:TEMPPROV <> :PROVIDER) and (:GROUPBYPROV = 1))
             or (coalesce(:TEMPCONTR,-1) <> coalesce(:CONTRACTID,-1))
             or (:CLASSID <> :TEMPCLASS)
             or (:TRANSPORTTYPE <> :TEMPTRANSPORTTYPE)
             or (((:MAXREGNUMAVTO is null) and (:TRANSPORTTYPE = 0))
                 and ((:MAXREGNUMZD is null) and (:TRANSPORTTYPE = 1)))
             or (coalesce(:REGISTERID,-1) <> coalesce(:TEMPREGID,-1)))
            or (((:TEMPQ <> :QUALITYNUMBER) or (:HUM <> :TEMPHUM) or (:DIRT <> :TEMPDIRT))
                and ((:TRANSPORTTYPE <> 1) and (coalesce(:AZHS,3) = 3)))
            or (((:HUMIDITYSTATE <> :TEMPHUMIDITYSTATE) or (:DIRTSTATE <> :TEMPDIRTSTATE))
                and (coalesce(:AZHS,3) = 4))
           ) then begin
          if (:NOTDIVIDENUMBERING = 0) then begin
            if (:TRANSPORTTYPE = 0) then begin
               if (:MAXREGNUMAVTO is null) then
                 MAXREGNUMAVTO = 0;

               MAXREGNUMAVTO = :MAXREGNUMAVTO + 1;
            end
            else
              if (:TRANSPORTTYPE = 1) then begin
                if (:MAXREGNUMZD is null) then
                  MAXREGNUMZD = 0;

                MAXREGNUMZD = :MAXREGNUMZD + 1;
              end
          end
          else begin
            if (:MAXREGNUM is null) then
              MAXREGNUM = 0;

            MAXREGNUM = :MAXREGNUM + 1;
          end
        end

        if (:NOTDIVIDENUMBERING = 0) then begin
          if (:TRANSPORTTYPE = 0) then
            REGNUM = :MAXREGNUMAVTO;
          else
            if (:TRANSPORTTYPE = 1) then
              REGNUM = :MAXREGNUMZD;
        end
        else REGNUM = :MAXREGNUM;
      end
    end

    TEMPDATE = :REGDATE;
    TEMPOWN = :OWNERID;
    TEMPQ = :QUALITYNUMBER;
    TEMPCONTR = :CONTRACTID;
    TEMPPROV = :PROVIDER;
    TEMPCLASS = :CLASSID;
    TEMPHUM = :HUM;
    TEMPHUMIDITYSTATE = :HUMIDITYSTATE;
    TEMPDIRT = :DIRT;
    TEMPDIRTSTATE = :DIRTSTATE;
    TEMPREGID = :REGISTERID;
    TEMPSTATIONID = :STATIONID;
    TEMPTRANSPORTTYPE = :TRANSPORTTYPE;

    if (:GROUPBYPROV = 2) then
      PROVIDER = 0;

    suspend;
  end
end