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

REGISTER_REPORT

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

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

Описание

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

Определение

CREATE PROCEDURE REGISTER_REPORT(
    REGISTERID INTEGER,
    PLUGWORK INTEGER)
RETURNS (
    INVOICEREGISTERID INTEGER,
    REGISTERNUMBER INTEGER,
    CLIENTNAME VARCHAR(30),
    OKPO VARCHAR(10),
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    SORTNAME VARCHAR(30),
    SUMNET BIGINT,
    SUMWEIGHT BIGINT,
    QUALITYNUMBER INTEGER,
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    BASHUM NUMERIC(9,2),
    BASDIRT NUMERIC(9,2),
    BASSC NUMERIC(9,2),
    INCOMECOST NUMERIC(9,2),
    DRYCOST NUMERIC(9,2),
    UNDERWORKCOST NUMERIC(9,2),
    ALLDET NUMERIC(9,4),
    ALLNET INTEGER,
    NETCOST NUMERIC(15,2),
    DRYNET NUMERIC(15,2),
    CLEANNET NUMERIC(15,2),
    INCOMEW NUMERIC(15,2),
    DRYW NUMERIC(15,2),
    CLEANW NUMERIC(15,2))
AS
BEGIN
    select
      invoiceregisters.invoiceregisterid,
      invoiceregisters.registernumber,
      clients.clientname,
      clients.okpo,
      crop.cropname,
      class.classname,
      cropsort.sortname,
      sum(it.net) sumNet,
      sum(it.weight) sumWeight,
    
      max(q.qualitynumber), --
      cast(Sum(q.humidity*it.net)/Sum(nullif(it.net, 0) + q.humidity - q.humidity) as numeric(4,2)) humidity,
      cast(Sum(q.dirt*it.net)/Sum(nullif(it.net, 0) + q.dirt - q.dirt) as numeric(4,2)) dirt,
      cast(Sum(q.screening*it.net)/Sum(nullif(it.net, 0) + q.screening - q.screening) as numeric(6,2)) screening,


      basq.humidity bashum,--
      basq.dirt basdirt,    --
      basq.screening bassc, --
    
      invoiceregisters.incomecost,
      invoiceregisters.drycost,
      invoiceregisters.underworkcost
    
    from invoiceregisters
      left join it on (invoiceregisters.invoiceregisterid = it.registerid)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join clients on (it.ownerid = clients.clientid)
      left join quality_select(it.buhqualityid) q on (0=0)
      left join quality_select(invoiceregisters.qualityid) basq on (0=0)
      left join crop on (q.cropid = crop.cropid)
      left join class on (q.classid = class.classid)
      left join cropsort on (q.sortid = cropsort.sortid)
      left join storing on (it.storingid = storing.storingid)
      left join storagetickets on (invoiceregisters.storageticketid = storagetickets.storageticketid)
    where invoiceregisters.invoiceregisterid = :registerid
      and it.state >= 0 and it.parentid >=0
    group by
      invoiceregisters.invoiceregisterid,
      invoiceregisters.registernumber,
      clients.clientname,
      clients.okpo,
      crop.cropname,
      class.classname,
      cropsort.sortname,
      basq.humidity,
      basq.dirt,
      basq.screening,
      invoiceregisters.incomecost,
      invoiceregisters.drycost,
      invoiceregisters.underworkcost
    INTO :INVOICEREGISTERID,
         :REGISTERNUMBER,
         :CLIENTNAME,
         :OKPO,
         :CROPNAME,
         :CLASSNAME,
         :SORTNAME,
         :SUMNET,
         :SUMWEIGHT,
         :QUALITYNUMBER,
         :HUMIDITY,
         :DIRT,
         :SCREENING,
         :BASHUM,
         :BASDIRT,
         :BASSC,
         :INCOMECOST,
         :drycost, 
         :UNDERWORKCOST;


  select
    register_calcquality.alldet,
    register_calcquality.allnet,
    register_calcquality.netcost,
    register_calcquality.drynet,
    register_calcquality.cleannet,
    register_calcquality.incomew,
    register_calcquality.dryw,
    register_calcquality.cleanw
  from register_calcquality(:BasHUM,:Basdirt,:Bassc,:PlugWork,
        :humidity,:dirt,:screening,:sumnet, :incomecost,
        :drycost,:underworkcost)
    INTO :alldet,
    :allnet,
    :netcost,
    :drynet,
    :cleannet,
    :incomew,
    :dryw,
    :cleanw;
  SUSPEND;
END