"localhost:/firebird/data/ILICHEVSK2.FDB". ���������

ACN_3017

������� ���������

�������� ���������

��������

(��� �������� ��� ��������� ACN_3017)

�����������

CREATE PROCEDURE ACN_3017(
    CLASSID INTEGER,
    CROPID INTEGER,
    DOCROP INTEGER,
    SORTID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    STORAGEID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    INCOME INTEGER,
    AOWNERID INTEGER,
    CONTRACTID INTEGER)
RETURNS (
    NET BIGINT,
    CDIRT BIGINT,
    CHUM BIGINT,
    DIRT NUMERIC(9,2),
    HUM NUMERIC(9,2),
    REGDATE DATE,
    ANALIS_ID INTEGER,
    NUMDOC VARCHAR(31),
    OWNERID INTEGER)
AS
BEGIN
  FOR
    select
      it.regdate,
      case when qd.qualitynumber is null
        then it.inventoryid
        else qd.qualitynumber
      end as analis_id,
      it.ownerid,
      case
        when not(it.operationid in (45,46)) then  case
          when (ittransport.transporttypeid = 2) then ittransport.transportnumber
          when (ittransport.transporttypeid in (0,1) and it.operationid = 5 and it.orderloaditemid is not null)
          then it.orderloaditemid
          when ((operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1)))
          then invoices.invoicenumber
         else it.registerid
        end
      end as numdoc,
     case
       when :income = 1 then coalesce(Sum(form36acoming.outnet),0)
     else
       coalesce(Sum(form36aexpense.outnet),0)
     end as net,

    case
       when :income = 1 then 
        cast(sum( form36acoming.outnet *
         cast(form36acoming.outdirt as numeric (3,1)))/100 as integer)
       else
        cast(sum( form36aexpense.outnet *
         cast(form36aexpense.outdirt as numeric (3,1)))/100 as integer)
    end as cdirt,

    case
       when :income = 1 then 
         cast(sum( form36acoming.outnet *
         cast(form36acoming.outhumidity as numeric (3,1)))/100 as integer)
       else
         cast(sum( form36aexpense.outnet *
         cast(form36aexpense.outhumidity as numeric (3,1)))/100 as integer)
     end as chum,
    
    case
       when :income = 1 then 
        case coalesce(sum(it.net),0) when 0 then 0 else
         cast(sum( form36acoming.outnet *
           form36acoming.outdirt)/sum(form36acoming.outnet) as numeric(3,1))
        end
       else
        case coalesce(sum(it.net),0) when 0 then 0 else
         cast(sum( form36aexpense.outnet *
           form36aexpense.outdirt)/sum(form36aexpense.outnet) as numeric(3,1))
        end
    end dirt,

    case
       when :income = 1 then 
        case coalesce(sum(it.net),0) when 0 then 0 else
          cast(sum( form36acoming.outnet *
          form36acoming.outhumidity)/sum(form36acoming.outnet) as numeric(3,1))
       end
       else
        case coalesce(sum(it.net),0) when 0 then 0 else
          cast(sum( form36aexpense.outnet *
          form36aexpense.outhumidity)/sum(form36aexpense.outnet) as numeric(3,1))
      end
    end hum
    
    from it
      join getworkparties(:partyid,:partydate, :cropid, :classid, :storageid)
      on (it.partyid = getworkparties.partyid)
      left join quality q on (it.BuhQualityid = q.qualityid)
      left join qualitydata_select_min(it.BuhQualityid) qd on (0 = 0)
      left join ittransport on it.inventoryid = ittransport.inventoryid
      left join operation on it.operationid = operation.operationid
      left join invoices on it.invoiceid = invoices.invoiceid
      left join form36aexpense(it.operationid, dirt, humidity,
         it.net) on (0=0)
      left join form36acoming(dirt, humidity,it.operationid,
         it.net, q.cropid) on (0=0)
    where
      ((q.classid = :classid and :docrop = 0) or (q.cropid = :cropid and :docrop = 1))
      and it.regdate between :date1 and :date2
      and it.storageid = :storageid
      and it.operationid = 17
      and ( (it.net > 0 and :income = 1)
            or (it.net < 0 and :income = 0))
      and it.state >= 0
      and it.parentid >= 0
      and ((q.sortid = :sortid) or (:sortid = 0))
      and ((it.ownerid = :aownerid) or (coalesce(:aownerid,0) = 0))
      and ((it.contractid = :contractid) or (coalesce(:contractid,0) = 0))
   group by
      it.regdate,
      analis_id,
      it.ownerid,
      numdoc, q.classid
    INTO :regdate,
         :analis_id,
         :ownerid,
         :numdoc,
         :net,
         :cdirt,
         :chum,
         :dirt,
         :hum
  DO
  BEGIN
    SUSPEND;
  END
END