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

IT_AVG_QUALITY_WEIGTH_SUMMARY

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

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

Описание

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

Определение

CREATE PROCEDURE IT_AVG_QUALITY_WEIGTH_SUMMARY(
    TODATE DATE,
    OWNERID INTEGER,
    CLASSIDCROP INTEGER,
    OPERATIONID INTEGER,
    BEGPARTYDATE DATE,
    TRANSPORTSUBTYPE INTEGER)
RETURNS (
    COMMENT VARCHAR(15),
    CLIENTNAME VARCHAR(30),
    TRANSPORTNUMBER VARCHAR(30),
    WEIGHT INTEGER,
    LABQUALITYID INTEGER,
    DIRT NUMERIC(9,2),
    HUMIDITY NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    GRAINUNIT INTEGER,
    PROTEIN NUMERIC(9,2),
    GLUTEN NUMERIC(9,2),
    GLUTENQUALITY NUMERIC(9,2),
    DROPNUMBER INTEGER,
    GLASSNESS NUMERIC(9,2),
    TURTLEBUG NUMERIC(9,2),
    SMALLGRAIN NUMERIC(9,2),
    SPOILT NUMERIC(9,2),
    ACIDITY NUMERIC(9,2),
    BEATEN NUMERIC(9,2),
    DEFECTIVE NUMERIC(9,2))
AS
declare variable SORTING integer;
begin
  if (:BEGPARTYDATE is null) then begin
    select first 1 PARTIES.PARTYID
      from PARTIES
        where (PARTIES.STATE > 0)
        and (PARTIES.CROPID = (select CROPID from CLASS where (CLASS.CLASSID = :CLASSIDCROP)))
        and (PARTIES.STORAGEID = 1)
    order by coalesce(PARTIES.CLASSID, 100000)
    into :BEGPARTYDATE;
  end

  for
    select 1, ' на  начало', '', '', cast(sum(IT.NET) as integer), null,
        sum(IT.NET * QUALITY_SELECT.DIRT) / nullif(sum(IT.NET + QUALITY_SELECT.DIRT - QUALITY_SELECT.DIRT), 0),
        sum(IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(sum(IT.NET + QUALITY_SELECT.HUMIDITY - QUALITY_SELECT.HUMIDITY), 0),
--      sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING), 0),
        case
          when (min(CROP.CROPTYPEID) <> 5) then
            sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING), 0)
          else sum(IT.NET * QUALITY_SELECT.OILSEED) / nullif(sum(IT.NET + QUALITY_SELECT.OILSEED - QUALITY_SELECT.OILSEED), 0)
        end,
        sum(IT.NET * QUALITY_SELECT.GLUTEN) / nullif(sum(IT.NET + QUALITY_SELECT.GLUTEN - QUALITY_SELECT.GLUTEN), 0),
        sum(IT.NET * QUALITY_SELECT.GLUTENQUALITY) / nullif(sum(IT.NET + QUALITY_SELECT.GLUTENQUALITY - QUALITY_SELECT.GLUTENQUALITY), 0),
        sum(IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(sum(IT.NET + QUALITY_SELECT.GRAINUNIT - QUALITY_SELECT.GRAINUNIT), 0),
        sum(IT.NET * QUALITY_SELECT.PROTEIN) / nullif(sum(IT.NET + QUALITY_SELECT.PROTEIN - QUALITY_SELECT.PROTEIN), 0),
        sum(IT.NET * QUALITY_SELECT.DROPNUMBER) / nullif(sum(IT.NET + QUALITY_SELECT.DROPNUMBER - QUALITY_SELECT.DROPNUMBER), 0),
        sum(IT.NET * QUALITY_SELECT.GLASSNESS) / nullif(sum(IT.NET + QUALITY_SELECT.GLASSNESS - QUALITY_SELECT.GLASSNESS), 0),
        sum(IT.NET * QUALITY_SELECT.TURTLEBUG) / nullif(sum(IT.NET + QUALITY_SELECT.TURTLEBUG - QUALITY_SELECT.TURTLEBUG), 0),
        sum(IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(sum(IT.NET + QUALITY_SELECT.SMALLGRAIN - QUALITY_SELECT.SMALLGRAIN), 0),
        sum(IT.NET * QUALITY_SELECT.SPOILT) / nullif(sum(IT.NET + QUALITY_SELECT.SPOILT - QUALITY_SELECT.SPOILT), 0),
        sum(IT.NET * QUALITY_SELECT.ACIDITY) / nullif(sum(IT.NET + QUALITY_SELECT.ACIDITY - QUALITY_SELECT.ACIDITY), 0),
        sum(IT.NET * QUALITY_SELECT.BEATEN) / nullif(sum(IT.NET + QUALITY_SELECT.BEATEN - QUALITY_SELECT.BEATEN), 0),
        sum(IT.NET * QUALITY_SELECT.DEFECTIVE) / nullif(sum(IT.NET + QUALITY_SELECT.DEFECTIVE - QUALITY_SELECT.DEFECTIVE), 0)
    from IT
        left join QUALITY on (coalesce(IT.LABQUALITYID, IT.SAMPLERQUALITYID) = QUALITY.QUALITYID)
        left join QUALITY_SELECT(coalesce(IT.LABQUALITYID, IT.SAMPLERQUALITYID)) on (0 = 0)
        left join CROP on CROP.CROPID = QUALITY_SELECT.CROPID
        left join PARTIES on PARTIES.PARTYID = IT.PARTYID
        left join ITTRANSPORT on ITTRANSPORT.INVENTORYID = IT.INVENTORYID
        left join TRANSPORTTYPE on TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID
      where (IT.STATE <> -1)
        and ((IT.REGDATE < :TODATE) and (IT.REGDATE >= :BEGPARTYDATE))
        and ((IT.OWNERID = :OWNERID) or (:OWNERID is null))
        and (QUALITY.CLASSID = :CLASSIDCROP)
        and (IT.OPERATIONID = :OPERATIONID)
        and ((TRANSPORTTYPE.TRANSPORTSUBTYPE = :TRANSPORTSUBTYPE) or (:TRANSPORTSUBTYPE is null))
        and (PARTIES.STATE > 0)
    union all
      select 2, '',Clients.clientname,
          ITTRANSPORT.TRANSPORTNUMBER || coalesce(', ' || (select ITTRANSPORT.TRANSPORTNUMBER
                                                            from IT IT2
                                                              left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT2.INVENTORYID)
                                                            where (IT2.INVOICEID = IT.INVOICEID)
                                                              and (coalesce(IT2.NET,0) = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 1)
                                                              and (not (IT2.STATE in (-1,-3)))
                                                  ),''),
          IT.NET, IT.LABQUALITYID,
          (IT.NET * QUALITY_SELECT.DIRT) / nullif(IT.NET + QUALITY_SELECT.DIRT - QUALITY_SELECT.DIRT, 0) ,
          (IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(IT.NET + QUALITY_SELECT.HUMIDITY - QUALITY_SELECT.HUMIDITY, 0),
--      (IT.NET * QUALITY_SELECT.SCREENING) / nullif(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING, 0),
          case
            when (CROP.CROPTYPEID <> 5) then
              (IT.NET * QUALITY_SELECT.SCREENING) / nullif(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING, 0)
            else (IT.NET * QUALITY_SELECT.OILSEED) / nullif(IT.NET + QUALITY_SELECT.OILSEED - QUALITY_SELECT.OILSEED, 0)
          end,
          (IT.NET * QUALITY_SELECT.GLUTEN) / nullif(IT.NET + QUALITY_SELECT.GLUTEN - QUALITY_SELECT.GLUTEN, 0),
          (IT.NET * QUALITY_SELECT.GLUTENQUALITY) / nullif(IT.NET + QUALITY_SELECT.GLUTENQUALITY - QUALITY_SELECT.GLUTENQUALITY, 0),
          (IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(IT.NET + QUALITY_SELECT.GRAINUNIT - QUALITY_SELECT.GRAINUNIT, 0),
          (IT.NET * QUALITY_SELECT.PROTEIN) / nullif(IT.NET + QUALITY_SELECT.PROTEIN - QUALITY_SELECT.PROTEIN, 0),
          (IT.NET * QUALITY_SELECT.DROPNUMBER) / nullif(IT.NET + QUALITY_SELECT.DROPNUMBER - QUALITY_SELECT.DROPNUMBER, 0),
          (IT.NET * QUALITY_SELECT.GLASSNESS) / nullif(IT.NET + QUALITY_SELECT.GLASSNESS - QUALITY_SELECT.GLASSNESS, 0),
          (IT.NET * QUALITY_SELECT.TURTLEBUG) / nullif(IT.NET + QUALITY_SELECT.TURTLEBUG - QUALITY_SELECT.TURTLEBUG, 0),
          (IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif((IT.NET + QUALITY_SELECT.SMALLGRAIN - QUALITY_SELECT.SMALLGRAIN), 0),
          (IT.NET * QUALITY_SELECT.SPOILT) / nullif((IT.NET + QUALITY_SELECT.SPOILT - QUALITY_SELECT.SPOILT), 0),
          (IT.NET * QUALITY_SELECT.ACIDITY) / nullif(IT.NET + QUALITY_SELECT.ACIDITY - QUALITY_SELECT.ACIDITY, 0),
          (IT.NET * QUALITY_SELECT.BEATEN) / nullif(IT.NET + QUALITY_SELECT.BEATEN - QUALITY_SELECT.BEATEN, 0),
          (IT.NET * QUALITY_SELECT.DEFECTIVE) / nullif(IT.NET + QUALITY_SELECT.DEFECTIVE - QUALITY_SELECT.DEFECTIVE, 0)
    from IT
          left join QUALITY on (coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID) = QUALITY.QUALITYID)
          left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
          left join CROP on (CROP.CROPID = QUALITY_SELECT.CROPID)
          left join PARTIES on (PARTIES.PARTYID = IT.PARTYID)
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
          left join clients on (clients.clientid=IT.providerid)
          left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        where (IT.STATE <> -1) and(coalesce(IT.NET,0) <> 0)
          and (IT.REGDATE = :TODATE)
          and ((IT.OWNERID = :OWNERID) or (:OWNERID is null))
          and (QUALITY.CLASSID = :CLASSIDCROP)
          and (IT.OPERATIONID = :OPERATIONID)
          and ((TRANSPORTTYPE.TRANSPORTSUBTYPE = :TRANSPORTSUBTYPE) or (:TRANSPORTSUBTYPE is null))
          and (PARTIES.STATE > 0)
      order by 6
      into :SORTING, :COMMENT, :CLIENTNAME,:TRANSPORTNUMBER, :WEIGHT, :LABQUALITYID,
           :DIRT, :HUMIDITY, :SCREENING, :GLUTEN, :GLUTENQUALITY, :GRAINUNIT,
           :PROTEIN, :DROPNUMBER, :GLASSNESS, :TURTLEBUG, :SMALLGRAIN, :SPOILT,
           :ACIDITY, :BEATEN, :DEFECTIVE
  do
    if (WEIGHT > 0) then
      suspend;
end