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

WEIGHTING_JOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE WEIGHTING_JOURNAL(
    REGDATEB DATE,
    REGDATEE DATE,
    OPERATIONID INTEGER,
    STORAGEIN VARCHAR(50),
    TRANSPORTSUBTYPE INTEGER,
    SEPARATERECORD INTEGER,
    SELECTEDONWEIGHTUSER INTEGER,
    SHOWHARVESTYEAR INTEGER)
RETURNS (
    INVOICENUMBER VARCHAR(31),
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    CLIENTID INTEGER,
    CLIENTNAME VARCHAR(100),
    OWNERID INTEGER,
    OWNERNAME VARCHAR(100),
    REGDATE DATE,
    WEIGHTNUMBER INTEGER,
    CARWEIGHTNUMBER INTEGER,
    TRAILERWEIGHTNUMBER INTEGER,
    CARNUMBER VARCHAR(25),
    TRAILERNUMBER VARCHAR(25),
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    INVOICENET INTEGER,
    DISCREPANCY INTEGER,
    STORAGENAME VARCHAR(60),
    SILAGENAME VARCHAR(60),
    CLASSID INTEGER,
    CROPS VARCHAR(100),
    GROSSTIME TIMESTAMP,
    TARETIME TIMESTAMP,
    WEIGHTUSERID INTEGER,
    WEIGHTUSERNAME VARCHAR(31),
    SHIFT INTEGER,
    REGIONNAME VARCHAR(200),
    WEIGHT INTEGER,
    CORNFIELDNAME VARCHAR(30),
    SENDERNAME VARCHAR(100))
AS
declare variable TRAILERTNUMBER varchar(25);
declare variable TRAILERGROSS integer;
declare variable TRAILERTARE integer;
declare variable TRAILERWEIGHT integer;
declare variable TRAILERNET integer;
declare variable TRAILERSTORAGENAME varchar(60);
declare variable TRAILERCROP varchar(60);
declare variable TRAILERGROSSTIME timestamp;
declare variable TRAILERTARETIME timestamp;
declare variable TRAILERINVOICEGROSS integer;
declare variable TRAILERINVOICETARE integer;
declare variable TRAILERINVOICENET integer;
declare variable CARSTATE integer;
declare variable TRAILERSTATE integer;
declare variable CARSAMPLERQUALITYID integer;
declare variable TRAILERSAMPLERQUALITYID integer;
declare variable CARLABQUALITYID integer;
declare variable TRAILERLABQUALITYID integer;
declare variable AINVOCEID integer;
declare variable TWEIGHTNUMBER integer;
declare variable TRAILERCLASSID integer;
declare variable HARVESTYEAR integer;
begin
  for
    select WEIGHER.INVOICEID,
        WEIGHER.INVOICENUMBER, WEIGHER.CARINVOICEGROSS, WEIGHER.CARINVOICETARE, WEIGHER.CARINVOICENET,
        WEIGHER.TRAILERINVOICEGROSS, WEIGHER.TRAILERINVOICETARE, WEIGHER.TRAILERINVOICENET,
        WEIGHER.CLIENTNAME, WEIGHER.CLIENTID, WEIGHER.OWNERNAME, WEIGHER.OWNERID, WEIGHER.REGDATE,
        WEIGHER.CARWEIGHTNUMBER, WEIGHER.TRAILERWEIGHTNUMBER, WEIGHER.CARNUMBER, WEIGHER.TRAILERNUMBER,
        WEIGHER.CARGROSS, WEIGHER.CARTARE, WEIGHER.CARNET, WEIGHER.WEIGHT,
        WEIGHER.TRAILERGROSS, WEIGHER.TRAILERTARE, WEIGHER.TRAILERNET,
        WEIGHER.CARSTORAGENAME, WEIGHER.TRAILERSTORAGENAME, WEIGHER.CARCLASSID,
        WEIGHER.CARCROP, WEIGHER.TRAILERCLASSID, WEIGHER.TRAILERCROP, WEIGHER.CARGROSSTIME,
        WEIGHER.CARTARETIME, WEIGHER.TRAILERGROSSTIME, WEIGHER.TRAILERTARETIME, WEIGHER.CARSTATE,
        WEIGHER.TRAILERSTATE, WEIGHER.CARSAMPLERQUALITYID, WEIGHER.TRAILERSAMPLERQUALITYID,
        WEIGHER.CARLABQUALITYID, WEIGHER.TRAILERLABQUALITYID, WEIGHER.WEIGHTUSERID,
        WEIGHER.SILAGENAME, WEIGHER.SHIFT, WEIGHER.SENDERNAME,
        USERS.USERLASTNAME || ' ' || left(USERS.USERNAME, 1) || '.' || left(USERS.USERMIDDLENAME, 1) || '.'
      from WEIGHER(:REGDATEB, :REGDATEE, :OPERATIONID, :STORAGEIN, :TRANSPORTSUBTYPE, 0, 0, 0)
        left join USERS on (USERS.USERID = WEIGHER.WEIGHTUSERID)
      where (coalesce(WEIGHER.NET,0) > 0)
    order by WEIGHER.REGDATE, WEIGHER.SHIFT, USERS.USERLASTNAME, WEIGHER.CARCROP, WEIGHER.CLIENTNAME, WEIGHER.OWNERNAME, WEIGHER.INVOICEID
    into :AINVOCEID,
         :INVOICENUMBER, :INVOICEGROSS, :INVOICETARE, :INVOICENET,
         :TRAILERINVOICEGROSS, :TRAILERINVOICETARE, :TRAILERINVOICENET,
         :CLIENTNAME, :CLIENTID, :OWNERNAME, :OWNERID, :REGDATE,
         :CARWEIGHTNUMBER, :TRAILERWEIGHTNUMBER, :CARNUMBER, :TRAILERNUMBER,
         :GROSS, :TARE, :NET, :WEIGHT,
         :TRAILERGROSS, :TRAILERTARE, :TRAILERNET,
         :STORAGENAME, :TRAILERSTORAGENAME, :CLASSID,
         :CROPS, :TRAILERCLASSID, :TRAILERCROP, :GROSSTIME,
         :TARETIME, :TRAILERGROSSTIME, :TRAILERTARETIME, :CARSTATE,
         :TRAILERSTATE, :CARSAMPLERQUALITYID, :TRAILERSAMPLERQUALITYID,
         :CARLABQUALITYID, :TRAILERLABQUALITYID, :WEIGHTUSERID,
         :SILAGENAME, :SHIFT, :SENDERNAME, :WEIGHTUSERNAME

  do begin

    if (SELECTEDONWEIGHTUSER = 0) then begin
      WEIGHTUSERID = null;
      WEIGHTUSERNAME = null;
    end

    DISCREPANCY = null;
    REGIONNAME = '';
    CORNFIELDNAME = '';

    if (CARSTATE < 0) then begin
      INVOICEGROSS = null;
      INVOICETARE = null;
      INVOICENET = null;
--      WEIGHTNUMBER = null;
      CARNUMBER = null;
      GROSS = null;
      TARE = null;
      NET = null;
      WEIGHT = null;
      STORAGENAME = null;
      CROPS = '';
      GROSSTIME = null;
      TARETIME = null;
    end
    else begin
      WEIGHTNUMBER = :CARWEIGHTNUMBER;

      select REGIONS.REGIONNAME, CORNFIELDS.CORNFIELDNAME
        from QUALITY_SELECT(:CARSAMPLERQUALITYID)
          left join REGIONS on (REGIONS.REGIONID = QUALITY_SELECT.REGIONID)
          left join CORNFIELDS on (CORNFIELDS.CORNFIELDID = QUALITY_SELECT.CORNFIELDID)
      into :REGIONNAME, :CORNFIELDNAME;

      if (:SHOWHARVESTYEAR = 1) then begin
        select QUALITY_SELECT.HARVESTYEAR
          from QUALITY_SELECT(:CARLABQUALITYID)
        into :HARVESTYEAR;

        CROPS = :CROPS || ' ' || coalesce(:HARVESTYEAR,'') || ' р.в.';
      end
    end

    if ((TRAILERSTATE < 0) and (TRAILERSTATE <> -5)) then begin
      TRAILERINVOICEGROSS = null;
      TRAILERINVOICETARE = null;
      TRAILERINVOICENET = null;
      TRAILERWEIGHTNUMBER = null;
      TRAILERNUMBER = null;
      TRAILERGROSS = null;
      TRAILERTARE = null;
      TRAILERNET = null;
      TRAILERWEIGHT = null;
      TRAILERSTORAGENAME = null;
      TRAILERCROP = null;
      TRAILERGROSSTIME = null;
      TRAILERTARETIME = null;
    end

    if ((TRAILERGROSS is not null) or (TRAILERTARE is not null)) then begin
      if (((SEPARATERECORD = 1) and ((GROSS is not null) or (TARE is not null)))
          or ((CROPS <> TRAILERCROP) or (CROPS is null))) then begin
        TRAILERTNUMBER = :TRAILERNUMBER;
        TRAILERNUMBER = null;
        TWEIGHTNUMBER = TRAILERWEIGHTNUMBER;
        TRAILERWEIGHTNUMBER = null;

        if (WEIGHTNUMBER is not null) then begin
          if ((INVOICENET <> 0) and ((WEIGHTNUMBER is not null) and (NET is not null))) then begin
            if (TRAILERINVOICENET is null) then
              DISCREPANCY = (coalesce(:NET,0) + coalesce(:TRAILERNET,0)) - INVOICENET;
            else DISCREPANCY = NET - INVOICENET;
          end

          if (NET > 0) then
            suspend;
        end
        TRAILERNUMBER = :TRAILERTNUMBER;
        TRAILERWEIGHTNUMBER = TWEIGHTNUMBER;
        WEIGHTNUMBER = null;
        CARNUMBER = null;
        STORAGENAME = :TRAILERSTORAGENAME;

        if (:SHOWHARVESTYEAR = 1) then begin
          select QUALITY_SELECT.HARVESTYEAR
            from QUALITY_SELECT(:CARLABQUALITYID)
          into :HARVESTYEAR;

          CROPS = :TRAILERCROP || ' ' || coalesce(:HARVESTYEAR,'') || ' р.в.';
        end
        else CROPS = :TRAILERCROP;

        CLASSID = :TRAILERCLASSID;
        GROSSTIME = :TRAILERGROSSTIME;
        TARETIME = :TRAILERTARETIME;

        if (INVOICENET is null) then
          DISCREPANCY = (coalesce(:NET,0) + coalesce(:TRAILERNET,0)) - TRAILERINVOICENET;
        else DISCREPANCY = TRAILERNET - TRAILERINVOICENET;

        GROSS = :TRAILERGROSS;
        TARE = :TRAILERTARE;
        NET = :TRAILERNET;
        WEIGHT = :TRAILERWEIGHT;

        INVOICEGROSS = :TRAILERINVOICEGROSS;
        INVOICETARE = :TRAILERINVOICETARE;
        INVOICENET = :TRAILERINVOICENET;
      end
      else begin
        TRAILERNUMBER = :TRAILERNUMBER || ' *';
        GROSS = coalesce(:GROSS,0) + coalesce(:TRAILERGROSS,0);
        TARE = coalesce(:TARE,0) + coalesce(:TRAILERTARE,0);
        NET = coalesce(:NET,0) + coalesce(:TRAILERNET,0);
        WEIGHT = coalesce(:WEIGHT,0) + coalesce(:TRAILERWEIGHT,0);
        GROSSTIME = coalesce(:GROSSTIME,:TRAILERGROSSTIME);
        TARETIME = coalesce(:TARETIME,:TRAILERTARETIME);
        INVOICEGROSS = coalesce(:INVOICEGROSS,0) + coalesce(:TRAILERINVOICEGROSS,0);
        INVOICETARE = coalesce(:INVOICETARE,0) + coalesce(:TRAILERINVOICETARE,0);
        INVOICENET = coalesce(:INVOICENET,0) + coalesce(:TRAILERINVOICENET,0);

        if (INVOICENET <> 0) then
          DISCREPANCY = NET - INVOICENET;
      end

      suspend;
    end
    else begin
      if (INVOICENET <> 0) then
        DISCREPANCY = NET - INVOICENET;

      suspend;
    end

  end

end