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

FORM117_QUALITY

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

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

Описание

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

Определение

CREATE PROCEDURE FORM117_QUALITY(
    BEGDATE DATE,
    ENDDATE DATE,
    STORAGEID INTEGER)
RETURNS (
    CLASSID INTEGER,
    QTYPE INTEGER,
    QVALUE DOUBLE PRECISION,
    WEIGHT INTEGER)
AS
begin
    for
      select cast(quality_select.classid as Integer), cast(qualitydata.qualitytypeid as Integer),
        cast(sum(qualitydata.qualityvalue * it.net) / nullif(sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue), 0) as numeric(7,2)),
        sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from it
        left join quality_select(it.labqualityid) on (0 = 0)
        left join qualitydata on (it.labqualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
        left join ittransport on it.inventoryid = ittransport.inventoryid
      where it.operationid = 5
        and ittransport.sampleroutdate between :begdate and :enddate
        and it.storageid = :storageid
        and it.state > -1
        and it.parentid <= 0
        and it.labqualityid is not null
        and it.weight is not null
        and quality_select.classid not in (44, 46, 65, 66)
        and qualitydata.qualitytypeid not in(4, 34)
        and qualitytypes.categoryid in (0, 2)
        and qualitytypes.usesubtypes = 0
      group by quality_select.classid, qualitydata.qualitytypeid

      union

      select cast(gradeouts.classid as Integer), cast(q.qualitytypeid as Integer),
        cast(sum(gradeouts.weight * q.qualityvalue) / Sum(gradeouts.weight + q.qualityvalue - q.qualityvalue) as numeric(7, 2)),
        Sum(gradeouts.weight + q.qualityvalue - q.qualityvalue)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata q on (gradeouts.qualityid = q.qualityid)
        left join qualitytypes on (q.qualitytypeid = qualitytypes.qualitytypeid)
      where
        ((graderesults.workingdate >= :begdate
          and graderesults.workingdate <= :enddate
          and graderesults.storingid = 4
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :storageid
        and gradeouts.classid in (44, 13, 14, 15)
        and q.qualitytypeid not in(4, 34)
        and qualitytypes.categoryid in (0, 2)
        and qualitytypes.usesubtypes = 0
      group by gradeouts.classid, q.qualitytypeid

      union

      select gradeouts.classid, q.qualitytypeid,
        cast(sum(q.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + q.qualityvalue - q.qualityvalue) as numeric(7,2)),
        sum(gradeouts.weight + q.qualityvalue - q.qualityvalue)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata q on (gradeouts.qualityid = q.qualityid)
        left join qualitytypes on (q.qualitytypeid = qualitytypes.qualitytypeid)
      where
        ((graderesults.workingdate >= :begdate
          and graderesults.workingdate <= :enddate
          and graderesults.storingid = 3
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :storageid
        and gradeouts.classid in (46, 66)
        and q.qualitytypeid not in(4, 34)
        and qualitytypes.categoryid in (0, 2)
        and qualitytypes.usesubtypes = 0
      group by gradeouts.classid, q.qualitytypeid

      union

      select
        cast (-1 as Integer), cast(qualitydata.qualitytypeid as Integer),
        cast(sum(qualitydata.qualityvalue * it.net) / sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2)),
        sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from it
        left join quality_select(it.labqualityid) on (0 = 0)
        left join qualitydata on (it.labqualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
        left join ittransport on it.inventoryid = ittransport.inventoryid
      where it.operationid = 5
        and ittransport.sampleroutdate between :begdate and :enddate
        and it.storageid = :storageid
        and it.state > -1
        and it.parentid <= 0
        and it.labqualityid is not null
        and it.weight is not null
        and quality_select.classid in (13, 14, 15, 65, 44)
        and qualitydata.qualitytypeid in (2, 11)
        and qualitytypes.categoryid in (0, 2)
        and qualitytypes.usesubtypes = 0
      group by qualitydata.qualitytypeid

      union

      select cast(-1 as Integer), qualitydata.qualitytypeid,
        cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)  as numeric(7,2)),
        sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
      where
        ((graderesults.workingdate >= :BegDate
          and graderesults.workingdate <= :EndDate
          and graderesults.storingid = 4
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :StorageID
        and qualitydata.qualitytypeid in (2, 11)
        and gradeouts.classid in (44, 13, 14, 15)
      group by qualitydata.qualitytypeid

      union

      select
        cast (-2 as Integer), cast(qualitydata.qualitytypeid as Integer),
        cast(sum(qualitydata.qualityvalue * it.net) / sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue) as numeric(7,2)),
        sum(it.net + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from it
        left join quality_select(it.labqualityid) on (0 = 0)
        left join qualitydata on (it.labqualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
        left join ittransport on it.inventoryid = ittransport.inventoryid
      where it.operationid = 5
        and ittransport.sampleroutdate between :begdate and :enddate
        and it.storageid = :storageid
        and it.state > -1
        and it.parentid <= 0
        and it.labqualityid is not null
        and it.weight is not null
        and quality_select.classid in (13, 14, 15, 44, 46, 47)
        and qualitydata.qualitytypeid in (2, 11)
        and qualitytypes.categoryid in (0, 2)
        and qualitytypes.usesubtypes = 0
      group by qualitydata.qualitytypeid

      union

      select cast(-2 as Integer), qualitydata.qualitytypeid,
        cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)  as numeric(7,2)),
        sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
      where
        ((graderesults.workingdate >= :BegDate
          and graderesults.workingdate <= :EndDate
          and graderesults.storingid = 4
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :StorageID
        and qualitydata.qualitytypeid in (2, 11)
        and gradeouts.classid in (44, 13, 14, 15)
      group by qualitydata.qualitytypeid

      union

      select cast(-2 as Integer), qualitydata.qualitytypeid,
        cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)  as numeric(7,2)),
        sum(gradeouts.weight + qualitydata.qualityvalue - qualitydata.qualityvalue)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
        left join qualitytypes on (qualitydata.qualitytypeid = qualitytypes.qualitytypeid)
      where
        ((graderesults.workingdate >= :BegDate
          and graderesults.workingdate <= :EndDate
          and graderesults.storingid = 3
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :StorageID
        and qualitydata.qualitytypeid in (2, 11)
        and gradeouts.classid in (46, 66)
      group by qualitydata.qualitytypeid


      into :classid, :qtype, :qvalue, :weight
    do suspend;
end