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

WEIGHER_WASTE_ACT

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

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

Описание

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

Определение

CREATE PROCEDURE WEIGHER_WASTE_ACT(
    WASTEACTREGDATE DATE,
    WASTEACTPROVIDERID INTEGER,
    WASTECLASSID INTEGER,
    WASTEFORAGECROPID INTEGER,
    WASTEFORAGECLASSID INTEGER,
    PROVIDERSEPARATED INTEGER)
RETURNS (
    WASTEACTID INTEGER,
    REGDATE DATE,
    CLIENTFULLNAME VARCHAR(100),
    WASTEACTNUMBER INTEGER,
    NET INTEGER,
    PROVIDERNAME VARCHAR(30),
    INVOICENUMBERS VARCHAR(300),
    FOREMENUSER VARCHAR(31),
    WEIGHTUSER VARCHAR(20),
    LABUSER VARCHAR(20),
    SECURITYUSER VARCHAR(20),
    DIRECTOR VARCHAR(31),
    LABMASTER VARCHAR(31),
    FOREMASTER VARCHAR(31),
    SECURITYMASTER VARCHAR(31),
    STORAGEID INTEGER,
    STORAGENAME VARCHAR(100),
    QUALITYNUMBER VARCHAR(10),
    QUALITYDATE DATE,
    WASTEDIRT NUMERIC(4,2),
    RESULT VARCHAR(100),
    DIRECTORORDERNUMBER INTEGER,
    DIRECTORORDERDATA DATE,
    UWORDERID INTEGER,
    UWACTN VARCHAR(10),
    UWACTDATE DATE)
AS
declare variable ANET integer;
declare variable AWASTEACTID integer;
declare variable APROVIDERNAME varchar(30);
declare variable ASTORAGEID integer;
declare variable AWASTEACTNUMBER integer;
declare variable AINVOICENUMBER varchar(31) = '';
declare variable AWASTEDIRT numeric(4,2);
declare variable AWASTEDIRTC numeric(15,4);
declare variable AWASTEFORAGECROP varchar(30);
declare variable AWASTEFORAGECLASS varchar(30);
declare variable AQUALITYNUMBER integer;
declare variable ANALYSISDATE date;
declare variable ADIRECTOR varchar(50);
declare variable APOS integer;
declare variable AUWORDERID integer;
begin

  NET = 0;
  AWASTEDIRTC = 0;
  INVOICENUMBERS = '';
  QUALITYNUMBER = '';
  QUALITYDATE = null;

  for
    select IT.NET, IT.RELATIONID, IT.STORAGEID, CLIENTS.CLIENTNAME, INVOICES.INVOICENUMBER,
        LABQUALITY.QUALITYNUMBER, QUALITY.ANALYSISDATE, LABQUALITY.DIRT, IT.UWORDERID
      from IT
        left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
        left join QUALITY_SELECT(IT.LABQUALITYID) LABQUALITY on (0 = 0)
        left join QUALITY_SELECT(IT.SAMPLERQUALITYID) on (0 = 0)
      where ((IT.REGDATE = :WASTEACTREGDATE)
        and (IT.OPERATIONID = 5)
        and (((:PROVIDERSEPARATED = 1) and (IT.PROVIDERID = :WASTEACTPROVIDERID)) or (:PROVIDERSEPARATED = 0))
        and ((QUALITY_SELECT.CLASSID = :WASTECLASSID)
          and ((coalesce(:WASTEFORAGECROPID, -1) = -1) or ((:WASTEFORAGECROPID > -1) and (QUALITY_SELECT.FORAGECROPID = :WASTEFORAGECROPID)))
          and ((coalesce(:WASTEFORAGECLASSID, -1) = -1) or ((:WASTEFORAGECLASSID > -1) and (QUALITY_SELECT.FORAGECLASSID = :WASTEFORAGECLASSID))))
        and (IT.STATE >= 0))
    into :ANET, :AWASTEACTID, :ASTORAGEID, :APROVIDERNAME, :AINVOICENUMBER,
         :AQUALITYNUMBER, :ANALYSISDATE, :AWASTEDIRT, :AUWORDERID

  do begin
    if (WASTEACTID is null) then
      WASTEACTID = :AWASTEACTID;
    if (PROVIDERNAME is null) then
      PROVIDERNAME = :APROVIDERNAME;
    if (STORAGEID is null) then
      STORAGEID = :ASTORAGEID;
    NET = NET + coalesce(:ANET, 0);
    AWASTEDIRTC = AWASTEDIRTC + cast((coalesce(:ANET, 0) * coalesce(:AWASTEDIRT, 0) / 100) as numeric (15, 4));
    if (ANET > 0) then
      if (INVOICENUMBERS <> '') then
        INVOICENUMBERS = INVOICENUMBERS || coalesce(', ' || :AINVOICENUMBER, '');
      else INVOICENUMBERS = coalesce(:AINVOICENUMBER, '');
    if (AQUALITYNUMBER is not null) then
      if (QUALITYNUMBER = '') then
        QUALITYNUMBER = cast(:AQUALITYNUMBER as varchar (10));
      else
        if (QUALITYNUMBER <> cast(:AQUALITYNUMBER as varchar (10))) then
          QUALITYNUMBER = 'ср.вз.';
    if ((QUALITYDATE is null) and (:ANALYSISDATE is not null)) then
      QUALITYDATE = :ANALYSISDATE;
    if (UWORDERID is null) then
      UWORDERID = :AUWORDERID;
  end

  if (WASTEACTID is null) then begin
    REGDATE = :WASTEACTREGDATE;
    select first 1 WASTEACTS.WASTEACTNUMBER from WASTEACTS
        where ((WASTEACTS.STATE is null) or (WASTEACTS.STATE = 0))
      order by WASTEACTS.WASTEACTID desc
    into :AWASTEACTNUMBER;
    WASTEACTNUMBER = coalesce(:AWASTEACTNUMBER, 0) + 1;

  end
  else begin
    select WASTEACTS.REGDATE, WASTEACTS.WASTEACTNUMBER, WASTEACTS.FOREMENUSER,
        WASTEACTS.WEIGHTUSER, WASTEACTS.LABUSER, WASTEACTS.SECURITYUSER,
        WASTEACTS.DIRECTORORDERNUMBER, WASTEACTS.DIRECTORORDERDATA,
        STORAGE.STORAGENAME, CLIENTS.CLIENTFULLNAME
      from WASTEACTS
        left join STORAGE on (STORAGE.STORAGEID = :STORAGEID)
        left join CLIENTS on (CLIENTS.STORAGEID = :STORAGEID)
      where (WASTEACTS.WASTEACTID = :WASTEACTID)
    into :REGDATE, :WASTEACTNUMBER, :FOREMENUSER,
      :WEIGHTUSER, :LABUSER, SECURITYUSER,
      :DIRECTORORDERNUMBER, :DIRECTORORDERDATA,
      :STORAGENAME, :CLIENTFULLNAME;

    if (WASTECLASSID = 41) then begin
      select coalesce(' ' || CROP.CROPNAME, ''), coalesce(' ' || CLASS.CLASSNAME, '')
        from CROP
          left join CLASS on (CLASS.CLASSID = :WASTEFORAGECLASSID)
        where (CROP.CROPID = :WASTEFORAGECROPID)
      into :AWASTEFORAGECROP, :AWASTEFORAGECLASS;
      RESULT = 'очищення' || :AWASTEFORAGECROP || :AWASTEFORAGECLASS;
    end
    else RESULT = 'аспирації';

    if (NET > 0) then
      WASTEDIRT = cast((cast((AWASTEDIRTC * 100) as numeric (15, 4)) / NET) as numeric (4, 2));
    else WASTEDIRT = 0;
  end

  select
      CLIENTS.DIRECTORNAME,
      coalesce(USERS2.USERLASTNAME, '') || coalesce(' ' || substring(USERS2.USERNAME from 1 for 1) || '.', '') || coalesce(substring(USERS2.USERMIDDLENAME from 1 for 1) || '.', ''),
      coalesce(USERS3.USERLASTNAME, '') || coalesce(' ' || substring(USERS3.USERNAME from 1 for 1) || '.', '') || coalesce(substring(USERS3.USERMIDDLENAME from 1 for 1) || '.', ''),
      coalesce(USERS4.USERLASTNAME, '') || coalesce(' ' || substring(USERS4.USERNAME from 1 for 1) || '.', '') || coalesce(substring(USERS4.USERMIDDLENAME from 1 for 1) || '.', '')
    from CLIENTS
      left join STORAGE on (STORAGE.STORAGEID = :ASTORAGEID)
      left join USERS USERS2 on (USERS2.USERID = STORAGE.STORAGELABID)
      left join USERS USERS3 on (USERS3.USERID = STORAGE.STORAGEUSERID)
      left join USERS USERS4 on (USERS4.USERID = (select USERID from USERSJOB where (JOBID = 13)))
    where (CLIENTS.STORAGEID = :ASTORAGEID)
  into :ADIRECTOR,
       :LABMASTER,
       :FOREMASTER,
       :SECURITYMASTER;
  APOS = position(' ' in :ADIRECTOR);
  if (APOS > 0) then begin
    DIRECTOR = coalesce(substring(:ADIRECTOR from 1 for APOS + 1) || '.', '');
    ADIRECTOR = substring(:ADIRECTOR from APOS + 1 for bit_length(:ADIRECTOR) - APOS);
    APOS = position(' ' in :ADIRECTOR);
    if (APOS > 0) then
      DIRECTOR = :DIRECTOR || coalesce(substring(:ADIRECTOR from APOS + 1 for 1) || '.', '');
  end

  if (not (UWORDERID is null)) then
    select
        cast(UWACTS.UWACTN as varchar(10)), UWACTS.UWACTDATE
      from UWACTS
      where (UWACTS.UWORDERID = :UWORDERID)
    into :UWACTN, :UWACTDATE;
  else UWACTN = '';

  suspend;

end