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

UNDERWORK_REGISTER

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

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

Описание

Реестр подработки

Определение

CREATE PROCEDURE UNDERWORK_REGISTER(
    BEGDATE DATE,
    ENDDATE DATE,
    CLASSID INTEGER,
    SORTID INTEGER)
RETURNS (
    NET INTEGER,
    REGDATE DATE,
    SDIRT NUMERIC(4,2),
    SSCREENING NUMERIC(4,2),
    LDIRT NUMERIC(4,2),
    LSCREENING NUMERIC(4,2),
    STORAGENAME VARCHAR(100),
    STORAGEID INTEGER)
AS
begin
  for
    select IT.REGDATE, min(storage.storagename), sum(IT.NET),
      avg(SQUALITY.DIRT), avg(SQUALITY.SCREENING), avg(LQUALITY.DIRT), avg(LQUALITY.SCREENING), min(it.storageid)
    from IT
      left join uwoutacts on (it.invoiceid = uwoutacts.invoiceid)
      left join quality_select(uwoutacts.beforequalityid) SQUALITY on 0 = 0
      left join quality_select(uwoutacts.afterqualityid) LQUALITY on 0 = 0
      left join quality_select(it.samplerqualityid) ITQUALITY ON (0 = 0)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join storage on (storage.storageid = it.storageid)
    where IT.REGDATE between :begdate and :enddate
      and it.operationid in (29, 30, 31)
      and IT.state > -1 and IT.ParentID <= 0
      and it.operationparam = :CLASSID
      and uwoutacts.invoiceid is not null
      and coalesce(ITQUALITY.sortid, -1) = coalesce(:sortid, -1)
    group by IT.REGDATE, Invoices.invoicenumber
    union
    select IT.REGDATE, min(storage.storagename), sum(IT.NET), avg(SQUALITY.DIRT), avg(SQUALITY.SCREENING), avg(LQUALITY.DIRT),
      avg(LQUALITY.SCREENING), min(it.storageid)
    from IT
      left join quality_select(IT.SamplerQUALITYID) SQUALITY on 0 = 0
      left join quality_select(IT.LABQUALITYID) LQUALITY on 0 = 0
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join uwoutacts on (it.invoiceid = uwoutacts.invoiceid)
      left join recipes on (recipes.recipeid = uwoutacts.recipeid)
      left join storage on (storage.storageid = recipes.storageid)
    where IT.REGDATE between :begdate and :enddate
      and it.operationid in (7)
      and IT.state > -1 and IT.ParentID <= 0
      and it.operationparam = :classid
      and uwoutacts.invoiceid is not null
      and coalesce(SQUALITY.sortid, -1) = coalesce(:sortid, -1)
    group by IT.REGDATE, Invoices.invoicenumber

    into :regdate, :storagename, :net, :sdirt, :sscreening, :ldirt, :lscreening, :storageid
  do begin
    suspend;
  end
END