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

STORAGETICKETS_QUALITYDATA_CALC

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

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

Описание

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

Определение

CREATE PROCEDURE STORAGETICKETS_QUALITYDATA_CALC(
    INVOICEREGISTERID INTEGER,
    CATEGORYID INTEGER)
RETURNS (
    QUALITYTYPEID INTEGER,
    QUALITYVALUE NUMERIC(9,2),
    QUALITYNAME VARCHAR(31),
    QUALITYPARENTID INTEGER)
AS
DECLARE VARIABLE QTYPE INTEGER;
DECLARE VARIABLE QVALUE DOUBLE PRECISION;
DECLARE VARIABLE QMAXVALUE NUMERIC(9,2);
DECLARE VARIABLE USESUBTYPE INTEGER;
DECLARE VARIABLE ACATEGORYID INTEGER;
begin
-- if categoryid is null then select all quality
  for
    select QualityData.Qualitytypeid,
      max(qualitytypes.usesubtypes), max(qualitytypes.categoryid),
      sum(QualityData.QualityValue * IT.NET) / nullif(sum(IT.NET + QualityData.QualityValue - QualityData.QualityValue), 0),
      max(QualityData.QualityValue), max(qualitytypes.qualityshortname), max(qualitytypes.qualityparentid)
    from invoiceregisters
      left join it on invoiceregisters.invoiceregisterid = it.registerid
      left join qualitydata on qualitydata.qualityid = it.labqualityid
      left join qualitytypes on (qualitytypes.qualitytypeid = QualityData.qualitytypeid)
      left join QUALITY on (IT.LABQUALITYID = QUALITY.QUALITYID)
    where invoiceregisters.invoiceregisterid = :invoiceregisterid and it.state > -1
      and QualityData.Qualitytypeid not in (1, 54, 96, 86)
      and (:categoryid is null or :categoryid = qualitytypes.categoryid)
    group by QualityData.Qualitytypeid
    into :qualitytypeid, :USESUBTYPE, :ACATEGORYID, :QVALUE, :QMAXVALUE, :qualityname, :qualityparentid
  do begin
    if (:USESUBTYPE = 1 or :ACATEGORYID = 1) then begin
      qualityvalue = cast(QMAXVALUE as integer);
    end
    else begin
      if (:qualitytypeid = 3) then qualityvalue = cast(:qvalue as numeric(9,1));
      else if (:qtype = 4) then qualityvalue = cast(:qvalue as integer);
      else if (:qtype = 7) then qualityvalue = cast(:qvalue as integer);
      else if (:qtype = 12) then qualityvalue = cast(:qvalue as integer);
      else if (:qtype = 22) then qualityvalue = cast(:qvalue as integer);
      else qualityvalue = :qvalue;
    end
    SUSPEND;
  end
END