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

WEIGHER_INVOICE

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

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

Описание

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

Определение

CREATE PROCEDURE WEIGHER_INVOICE(
    INVOICEID INTEGER)
RETURNS (
    REGDATE DATE,
    TTINVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    INVOICEDATE DATE,
    STATIONID INTEGER,
    STATIONNAME VARCHAR(30),
    DESTINATIONPOINTNAME VARCHAR(100),
    CLIENTID INTEGER,
    CLIENTFULLNAME VARCHAR(100),
    CLIENTNAME VARCHAR(30),
    CLIENTADDRESS VARCHAR(100),
    OWNERID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    OWNERNAME VARCHAR(30),
    SENDERID INTEGER,
    SENDERFULLNAME VARCHAR(100),
    SENDERNAME VARCHAR(30),
    FORWARDERID INTEGER,
    CUSTOMERID INTEGER,
    ORDERNUMBER VARCHAR(10),
    ORDERDATE DATE,
    PROXYPERSON VARCHAR(50),
    PROXYNUMBER VARCHAR(25),
    PROXYDATE DATE,
    DRIVERS VARCHAR(31),
    BARCODE VARCHAR(31),
    CARBARCODE VARCHAR(31),
    CARNUMBER VARCHAR(25),
    CARCROPNAME VARCHAR(300),
    CARGROSS INTEGER,
    CARTARE INTEGER,
    CARNET INTEGER,
    CARPACKNAME VARCHAR(30),
    CARCARGOQUANTITY INTEGER,
    CARHUMIDITY NUMERIC(9,2),
    CARDIRT NUMERIC(9,2),
    CARSCREENING NUMERIC(9,2),
    CARDROPNUMBER NUMERIC(9,2),
    TRAILERBARCODE VARCHAR(31),
    TRAILERNUMBER VARCHAR(25),
    TRAILERCROPNAME VARCHAR(60),
    TRAILERGROSS INTEGER,
    TRAILERTARE INTEGER,
    TRAILERNET INTEGER,
    TRAILERPACKNAME VARCHAR(30),
    TRAILERCARGOQUANTITY INTEGER,
    TRAILERHUMIDITY NUMERIC(9,2),
    TRAILERDIRT NUMERIC(9,2),
    TRAILERSCREENING NUMERIC(9,2),
    TRAILERDROPNUMBER NUMERIC(9,2),
    CROPCLASSNAME VARCHAR(300),
    GROSS INTEGER,
    GROSSTIME TIMESTAMP,
    TARE INTEGER,
    TARETIME TIMESTAMP,
    NET INTEGER,
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    DISCREPANSY INTEGER,
    CARGOQUANTITY INTEGER,
    WEIGHTUSERNAME VARCHAR(30),
    LABUSERNAME VARCHAR(30),
    WEIGHTNUMBER VARCHAR(30),
    CARSAMPLERQUALITYID INTEGER,
    TRAILERSAMPLERQUALITYID INTEGER,
    GLUTEN NUMERIC(9,2),
    PROTEIN NUMERIC(9,2),
    GLUTENQUALITY NUMERIC(9,2),
    GRAINUNIT NUMERIC(9,2),
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    DROPNUMBER NUMERIC(9,2),
    INFECTION VARCHAR(255),
    INFECTION_COUNT NUMERIC(9,2),
    COLOR VARCHAR(255),
    SMELL VARCHAR(255),
    SILAGENAME VARCHAR(30),
    PLATFORMNAME VARCHAR(30),
    MANAGERNAME VARCHAR(30),
    CITY VARCHAR(100),
    GENERALACCOUNT VARCHAR(50),
    ENTERPRISEFULLNAME VARCHAR(100),
    ENTERPRISENAME VARCHAR(30),
    ENTERPRISEADDRESS VARCHAR(100),
    OPERATIONID INTEGER,
    CHECKPOINTINTIME TIMESTAMP,
    CHECKPOINTSESSION INTEGER,
    QUALITYNUMBER INTEGER,
    SAMPLERINTIME TIMESTAMP,
    SENDERFULLNAMEPRINT VARCHAR(255),
    RECIPIENTFULLNAME VARCHAR(255),
    CARGOFORWARDINGFULLNAME VARCHAR(255),
    CARGOFORWARDINGADDRESS VARCHAR(100))
AS
declare variable AREGDATE date;
declare variable ABARCODE varchar(31);
declare variable AGROSS integer;
declare variable AGROSSTIME timestamp;
declare variable ATARE integer;
declare variable ATARETIME timestamp;
declare variable ANET integer;
declare variable AINVOICETARE integer;
declare variable AINVOICEGROSS integer;
declare variable AINVOICENET integer;
declare variable ATRANSPORTTYPEID integer;
declare variable ATRANSPORTNUMBER varchar(25);
declare variable ACROPNAME varchar(150);
declare variable ACLASSNAME varchar(60);
declare variable ASORTNAME varchar(60);
declare variable APACKNAME varchar(30);
declare variable ACARGOQUANTITY integer;
declare variable AWEIGHTUSERID integer;
declare variable ALABUSERID integer;
declare variable AWEIGHTNUMBER integer;
declare variable ASAMPLERQUALITYID integer;
declare variable AGLUTEN numeric(9,2);
declare variable AGLUTENQUALITY numeric(9,2);
declare variable APROTEIN numeric(9,2);
declare variable AGRAINUNIT numeric(9,2);
declare variable AHUMIDITY numeric(9,2);
declare variable ADIRT numeric(9,2);
declare variable ASCREENING numeric(9,2);
declare variable ADROPNUMBER numeric(9,2);
declare variable AINFECTION varchar(255);
declare variable AINFECTION_COUNT numeric(9,2);
declare variable ACOLOR varchar(255);
declare variable ASMELL varchar(255);
declare variable ASILAGENAME varchar(30);
declare variable APLATFORMNAME varchar(30);
declare variable STORAGEID integer;
declare variable GENERALACCOUNTFULLNAME varchar(100);
declare variable ACHECKPOINTINTIME timestamp;
declare variable ACHECKPOINTSESSION integer;
declare variable AQUALITYNUMBER integer;
declare variable ASAMPLERINTIME timestamp;
begin

  select INVOICES.INVOICEID, INVOICES.INVOICENUMBER, INVOICES.INVOICEDATE, INVOICES.STATIONID,
      IT.PROVIDERID, S1.CLIENTFULLNAME, S1.CLIENTNAME, coalesce(S1.ACTUALADDRESS,S1.ADDRESS) CLIENTADDRESS,
      IT.OWNERID, S3.CLIENTFULLNAME, S2.CLIENTNAME,
      INVOICES.SENDERID, S3.CLIENTFULLNAME, S3.CLIENTNAME, INVOICES.DRIVERS,
      ORDERLOAD.ORDERNUMBER, ORDERLOAD.BEGINDATE, ORDERLOAD.PROXYPERSON, ORDERLOAD.PROXYNUMBER, ORDERLOAD.PROXYDATE,
      STATIONS.STATIONNAME, STORAGE.STORAGENAME, S4.CITY, IT.OPERATIONID,
      coalesce(INVOICES.FORWARDERID,ORDERLOAD.FORWARDERID),
      ORDERLOAD.CUSTOMERID, S5.CLIENTFULLNAME, coalesce(S5.ACTUALADDRESS,S5.ADDRESS)
    from INVOICES
      left join IT on ((IT.INVOICEID = INVOICES.INVOICEID) and (IT.INVENTORYID = (select first 1 IT1.INVENTORYID from IT IT1 where ((INVOICES.INVOICEID = IT1.INVOICEID) and ((IT1.STATE >= 0) or (IT1.STATE = -3))))))
      left join IT_ORDERLOADIDBYINVOICEID(:INVOICEID) on (0=0)
      left join ORDERLOAD on (IT_ORDERLOADIDBYINVOICEID.ORDERLOADID = ORDERLOAD.ORDERLOADID)
      left join CLIENTS S1 on (S1.CLIENTID = coalesce(ORDERLOAD.RECIPIENTID,IT.PROVIDERID))
      left join CLIENTS S2 on (S2.CLIENTID = IT.OWNERID)
      left join CLIENTS S3 on (S3.CLIENTID = coalesce(ORDERLOAD.SENDERID,INVOICES.SENDERID))
      left join CLIENTS S4 on (S4.STORAGEID = IT.STORAGEID)
      left join CLIENTS S5 on (S5.CLIENTID = ORDERLOAD.CARGOFORWARDINGID)
      left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
      left join STORAGE on (STORAGE.STORAGEID = ORDERLOAD.DESTINATIONPOINTID)
     where (INVOICES.INVOICEID = :INVOICEID)
  into :TTINVOICEID, :INVOICENUMBER, :INVOICEDATE, :STATIONID,
      :CLIENTID, :CLIENTFULLNAME, :CLIENTNAME, :CLIENTADDRESS,
      :OWNERID, :OWNERFULLNAME, :OWNERNAME,
      :SENDERID, :SENDERFULLNAME, :SENDERNAME, :DRIVERS,
      :ORDERNUMBER, :ORDERDATE, :PROXYPERSON, :PROXYNUMBER, :PROXYDATE,
      :STATIONNAME, :DESTINATIONPOINTNAME, :CITY, :OPERATIONID,
      :FORWARDERID, :CUSTOMERID, :CARGOFORWARDINGFULLNAME, :CARGOFORWARDINGADDRESS;

  GROSS = 0;
  TARE = 0;
  NET = 0;
  INVOICEGROSS = 0;
  INVOICETARE = 0;
  INVOICENET = 0;
  CARGOQUANTITY = 0;
  BARCODE = '';
  WEIGHTNUMBER = '';
  SILAGENAME = '';
  PLATFORMNAME = '';

  for
    select IT.REGDATE, IT.GROSS, IT.TARE, IT.NET, IT.CARGOQUANTITY, IT.TARETIME,
        IT.GROSSTIME, IT.WEIGHTUSERID, IT.WEIGHTNUMBER, IT.SAMPLERQUALITYID,
        ITTRANSPORT.BARCODE, ITTRANSPORT.TRANSPORTTYPEID, ITTRANSPORT.TRANSPORTNUMBER,
        ITTRANSPORT.INVOICEGROSS, ITTRANSPORT.INVOICETARE, ITTRANSPORT.INVOICENET, 
        CROP.CROPNAME, CLASS.CLASSNAME, CROPSORT.SORTNAME, PACKS.PACKNAME,
        Q.GLUTEN, Q.GLUTENQUALITY, Q.PROTEIN, Q.GRAINUNIT, Q.HUMIDITY, Q.DIRT,
        Q.SCREENING, Q.DROPNUMBER, Q.INFECTION_COUNT, Q.USERID,
        INFECTION.SELECTVALUE INFECTION, COLOR.SELECTVALUE COLOR, SMELL.SELECTVALUE SMELL,
        SILAGE.NAME, PLATFORMS.PLATFORMNAME, IT.STORAGEID, ITTRANSPORT.CHECKPOINTINTIME,
        ITTRANSPORT.CHECKPOINTSESSION, cast(QUALITYDATA.QUALITYVALUE as integer),
        cast(ITTRANSPORT.SAMPLERINDATE + ITTRANSPORT.SAMPLERINTIME as timestamp)
      from IT
        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 CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
        left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
        left join PACKS on (PACKS.PACKID = IT.PACKID)
        left join QUALITY_SELECT(IT.LABQUALITYID) Q on (0=0)
        left join QUALITY_SELECT_VALUES(19, Q.INFECTIONID, 1) INFECTION on (0=0)
        left join QUALITY_SELECT_VALUES(25, Q.COLORID, 0) COLOR on (0=0)
        left join QUALITY_SELECT_VALUES(27, Q.SMELLID, 0) SMELL on (0=0)
        left join SILAGE on IT.SILAGEID = SILAGE.SILAGEID
        left join ROUTECHEMA on IT.ROUTEID = ROUTECHEMA.ROUTECHEMAID
        left join PLATFORMS on PLATFORMS.PLATFORMID = ROUTECHEMA.PLATFORMID
        left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.SAMPLERQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
      where (IT.INVOICEID = :INVOICEID) -- and ((IT.STATE > -1) or (IT.STATE = -5))
    order by IT.WEIGHTNUMBER
    into :AREGDATE, :AGROSS, :ATARE, :ANET, :ACARGOQUANTITY, :ATARETIME,
        :AGROSSTIME, :AWEIGHTUSERID, :AWEIGHTNUMBER, :ASAMPLERQUALITYID,
        :ABARCODE, :ATRANSPORTTYPEID, :ATRANSPORTNUMBER,
        :AINVOICEGROSS, :AINVOICETARE, :AINVOICENET,
        :ACROPNAME, :ACLASSNAME, :ASORTNAME, :APACKNAME,
        :AGLUTEN, :AGLUTENQUALITY, :APROTEIN, :AGRAINUNIT, :AHUMIDITY, :ADIRT,
        :ASCREENING, :ADROPNUMBER, :AINFECTION_COUNT, :ALABUSERID,
        :AINFECTION, :ACOLOR, :ASMELL,
        :ASILAGENAME, :APLATFORMNAME, :STORAGEID, :ACHECKPOINTINTIME,
        :ACHECKPOINTSESSION, :AQUALITYNUMBER, :ASAMPLERINTIME
  do begin
    if (not (ACROPNAME is null)) then begin
      if (not (ACLASSNAME is null)) then
        ACROPNAME = :ACROPNAME || ' ' || :ACLASSNAME;

      if (not (ASORTNAME is null)) then
        ACROPNAME = :ACROPNAME || ' (' || :ASORTNAME || ')';
    end

    if (REGDATE is null) then
      REGDATE = :AREGDATE;

    GROSS = :GROSS + coalesce(:AGROSS,0);

    if (GROSSTIME is null) then
      GROSSTIME = :AGROSSTIME;

    TARE = :TARE + coalesce(:ATARE,0);

    if (TARETIME is null) then
      TARETIME = :ATARETIME;

    NET = :NET + coalesce(:ANET,0);
    INVOICEGROSS = :INVOICEGROSS + coalesce(:AINVOICEGROSS,0);
    INVOICETARE = :INVOICETARE + coalesce(:AINVOICETARE,0);
    INVOICENET = :INVOICENET + coalesce(:AINVOICENET,0);
    CARGOQUANTITY = :CARGOQUANTITY + coalesce(:ACARGOQUANTITY,0);

    if (WEIGHTUSERNAME is null) then
      select coalesce(USERS.USERLASTNAME, '') || coalesce(' ' || substring(USERS.USERNAME from 1 for 1) || '.', '') || coalesce(substring(USERS.USERMIDDLENAME from 1 for 1) || '.', '') from USERS
        where (USERS.USERID = :AWEIGHTUSERID)
      into :WEIGHTUSERNAME;

    if (LABUSERNAME is null) then
      select coalesce(USERS.USERLASTNAME, '') || coalesce(' ' || substring(USERS.USERNAME from 1 for 1) || '.', '') || coalesce(substring(USERS.USERMIDDLENAME from 1 for 1) || '.', '') from USERS
        where (USERS.USERID = :ALABUSERID)
      into :LABUSERNAME;

    if (ATRANSPORTTYPEID = 1) then begin
      TRAILERNUMBER = :ATRANSPORTNUMBER;

      if (ANET > 0) then begin
        TRAILERBARCODE = :ABARCODE;
        TRAILERCROPNAME = :ACROPNAME;
        TRAILERPACKNAME = :APACKNAME;
        TRAILERCARGOQUANTITY = :ACARGOQUANTITY;
        TRAILERHUMIDITY = :AHUMIDITY;
        TRAILERDIRT = :ADIRT;
        TRAILERSCREENING = :ASCREENING;
        TRAILERDROPNUMBER = :ADROPNUMBER;
        TRAILERGROSS = :AGROSS;
        TRAILERTARE = :ATARE;
        TRAILERNET = :ANET;
        if (AGROSSTIME < GROSSTIME) then
          GROSSTIME = :AGROSSTIME;
        if (ATARETIME > TARETIME) then
          TARETIME = :ATARETIME;
      end

      TRAILERSAMPLERQUALITYID = :ASAMPLERQUALITYID;
/*
      TRAILERINVENTORYID = :AINVENTORYID;
      TRAILERSAMPLERQUALITYID = :ASAMPLERQUALITYID;
      TRAILERORDERLOADITEMID = :AORDERLOADITEMID;
      TRAILERCLASSID = :ACLASSID;
      TRAILERWEIGHT = :AWEIGHT;
      TRAILERGROSSTIME =:AGROSSTIME;
      TRAILERTARETIME = :ATARETIME;
      TRAILERSTORAGEID = :ASTORAGEID;
      TRAILERROUTEID = :AROUTEID;
      TRAILERSTORAGENAME = :ASTORAGENAME;
      TRAILERWEIGHTNUMBER = :AWEIGHTNUMBER;
      TRAILERBARCODE = :ABARCODE;
      TRAILERSTATE = :ASTATE;
      TRAILERTRANSPORTTYPEID = :ATRANSPORTTYPEID;
      TRAILERINVOICENET = :AINVOICENET;
      TRAILERINVOICEGROSS = :AINVOICEGROSS;
      TRAILERINVOICETARE = :AINVOICETARE;
      TRAILERGROSSBYHAND = :AGROSSBYHAND;
      TRAILERTAREBYHAND = :ATAREBYHAND;
*/
    end
    else begin
      CARBARCODE = :ABARCODE;
      CARNUMBER = :ATRANSPORTNUMBER;
      CARCROPNAME = :ACROPNAME;
      CARGROSS = :AGROSS;
      CARTARE = :ATARE;
      CARNET = :ANET;
      CARPACKNAME = :APACKNAME;
      CARCARGOQUANTITY = :ACARGOQUANTITY;
      CARHUMIDITY = :AHUMIDITY;
      CARDIRT = :ADIRT;
      CARSCREENING = :ASCREENING;
      CARDROPNUMBER = :ADROPNUMBER;
      BARCODE = cast(:CARBARCODE as varchar(30));
      WEIGHTNUMBER = cast(:AWEIGHTNUMBER as varchar(30));
      AWEIGHTNUMBER = null;
      CARSAMPLERQUALITYID = :ASAMPLERQUALITYID;
/*
      CARINVENTORYID = :AINVENTORYID;
      CARSAMPLERQUALITYID = :ASAMPLERQUALITYID;
      CARORDERLOADITEMID = :AORDERLOADITEMID;
      CARCLASSID = :ACLASSID;
      CARWEIGHT = :AWEIGHT;
      CARGROSSTIME =:AGROSSTIME;
      CARTARETIME = :ATARETIME;
      CARSTORAGEID = :ASTORAGEID;
      CARROUTEID = :AROUTEID;
      CARSTORAGENAME = :ASTORAGENAME;
      CARWEIGHTNUMBER = :AWEIGHTNUMBER;
      CARBARCODE = :ABARCODE;
      CARSTATE = :ASTATE;
      CARTRANSPORTTYPEID = :ATRANSPORTTYPEID;
      CARINVOICENET = :AINVOICENET;
      CARINVOICEGROSS = :AINVOICEGROSS;
      CARINVOICETARE = :AINVOICETARE;
      CARGROSSBYHAND = :AGROSSBYHAND;
      CARTAREBYHAND = :ATAREBYHAND;
*/
    end

    if (GLUTEN is null) then
      GLUTEN = AGLUTEN;

    if (GLUTENQUALITY is null) then
      GLUTENQUALITY = AGLUTENQUALITY;

    if (PROTEIN is null) then
      PROTEIN = APROTEIN;

    if (GRAINUNIT is null) then
      GRAINUNIT = AGRAINUNIT;

    if (HUMIDITY  is null) then
      HUMIDITY = AHUMIDITY;

    if (DIRT is null) then
      DIRT = ADIRT;

    if (SCREENING is null) then
      SCREENING = ASCREENING;

    if (DROPNUMBER is null) then
      DROPNUMBER = ADROPNUMBER;

    if (INFECTION is null) then
      INFECTION = AINFECTION;

    if (INFECTION_COUNT is null) then
      INFECTION_COUNT = AINFECTION_COUNT;

    if (COLOR is null) then
      COLOR = ACOLOR;

    if (SMELL is null) then
      SMELL = ASMELL;

    if (SILAGENAME = '') then
      SILAGENAME = coalesce(:ASILAGENAME, '');
    else
      if (ASILAGENAME is not null) then
        SILAGENAME = SILAGENAME || ' ' || ASILAGENAME;

    if (PLATFORMNAME = '') then
      PLATFORMNAME = coalesce(:APLATFORMNAME, '');
    else
      if (ASILAGENAME is not null) then
        PLATFORMNAME = PLATFORMNAME || ' ' || :APLATFORMNAME;

    if (CHECKPOINTINTIME is null) then
      CHECKPOINTINTIME = :ACHECKPOINTINTIME;

    if (CHECKPOINTSESSION is null) then
      CHECKPOINTSESSION = :ACHECKPOINTSESSION;

    if (QUALITYNUMBER is null) then
      QUALITYNUMBER = :AQUALITYNUMBER;

    if (SAMPLERINTIME is null) then
      SAMPLERINTIME = :ASAMPLERINTIME;
  end

  if ((CARCROPNAME is not null) and (TRAILERCROPNAME is not null)) then
    if (CARCROPNAME <> TRAILERCROPNAME) then
      CROPCLASSNAME = :CARCROPNAME || '; ' || :TRAILERCROPNAME;
    else CROPCLASSNAME = :CARCROPNAME;
  else CROPCLASSNAME = coalesce(:CARCROPNAME, :TRAILERCROPNAME, '');

  DISCREPANSY = :NET - :INVOICENET;

  if (WEIGHTNUMBER = '') then
    WEIGHTNUMBER = cast(:AWEIGHTNUMBER as varchar(30));
  else
    if (AWEIGHTNUMBER is not null) then
      WEIGHTNUMBER = WEIGHTNUMBER || ' ' || cast(:AWEIGHTNUMBER as varchar(30));

  select
      coalesce(CLIENTS.GENERALACCOUNT,CLIENTS_1.GENERALACCOUNT),
      coalesce(CLIENTS.CLIENTFULLNAME,CLIENTS_1.CLIENTFULLNAME),
      coalesce(CLIENTS.CLIENTNAME,CLIENTS_1.CLIENTNAME),
      coalesce(coalesce(CLIENTS.ACTUALADDRESS,CLIENTS.ADDRESS),coalesce(CLIENTS_1.ACTUALADDRESS,CLIENTS_1.ADDRESS)),
      (select USERS_SHORTNAME.USERSHORTNAME
         from STORAGE
           left join USERS_SHORTNAME(STORAGE.STORAGEUSERID) on (0=0)
         where (STORAGE.STORAGEID = :STORAGEID)
      )
    from STORAGE
      left join CLIENTS on (CLIENTS.STORAGEID = :STORAGEID)
      left join CLIENTS CLIENTS_1 on (CLIENTS_1.STORAGEID = 1)
    where (STORAGE.STORAGEID = :STORAGEID)
  into :GENERALACCOUNTFULLNAME, :ENTERPRISEFULLNAME, :ENTERPRISENAME, :ENTERPRISEADDRESS, :MANAGERNAME;

  GENERALACCOUNT = left(GENERALACCOUNTFULLNAME, position(' ' in GENERALACCOUNTFULLNAME));
  GENERALACCOUNTFULLNAME = right(GENERALACCOUNTFULLNAME, char_length(GENERALACCOUNTFULLNAME) - position(' ' in GENERALACCOUNTFULLNAME));
  GENERALACCOUNT = GENERALACCOUNT || substring(GENERALACCOUNTFULLNAME from 1 for 1) || '.'
    || substring(GENERALACCOUNTFULLNAME from position(' ' in GENERALACCOUNTFULLNAME) + 1 for 1) || '.';

  suspend;

end