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

FORM36COMMON

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

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

��������

����� 36 �����

�����������

CREATE PROCEDURE FORM36COMMON(
    DATE1 DATE,
    DATE2 DATE,
    CLASSID SMALLINT,
    STORAGE INTEGER,
    STORINGID INTEGER,
    SILAGEID INTEGER,
    PARTYID INTEGER,
    USEAFTERQUALTY INTEGER,
    DOCROP INTEGER,
    PARTYDATE DATE)
RETURNS (
    REGDATE DATE,
    INCOME INTEGER,
    OUTCOME INTEGER,
    REGISTERID VARCHAR(31),
    QUALITYNUMBER INTEGER,
    DIRTIN NUMERIC(9,2),
    HUMIDITYIN NUMERIC(9,2),
    DIRTOUT NUMERIC(9,2),
    HUMIDITYOUT NUMERIC(9,2),
    PROVIDER VARCHAR(100),
    OWNER VARCHAR(100),
    OPERATIONSUBTYPE INTEGER,
    STORAGENAME VARCHAR(100),
    STATIONNAME VARCHAR(30),
    INCOME_DIN NUMERIC(9,2),
    INCOME_HIN NUMERIC(9,2),
    OUTCOME_DIN NUMERIC(9,2),
    OUTCOME_HIN NUMERIC(9,2),
    ISLASTRECORD SMALLINT)
AS
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE DIRT NUMERIC(9,2);
DECLARE VARIABLE HUMIDITY NUMERIC(9,2);
DECLARE VARIABLE MAXDATE DATE;
DECLARE VARIABLE TRANSPORTTYPESTR VARCHAR(10);
DECLARE VARIABLE OPERATIONID INTEGER;
BEGIN
    SELECT Max(it.regdate)
    FROM it
      join getworkparties(:partyid,:partydate,null,null,null)
         on (it.partyid = getworkparties.partyid)
       left JOIN operation ON (operation.operationid = it.operationid)
       left join quality_select(it.samplerqualityid) sampq on (0 = 0)
       left join ittransport on (it.inventoryid = ittransport.inventoryid)
    WHERE it.regdate BETWEEN :date1 and :date2
        and it.storageid = :storage
        and ((sampq.classid = :classid and :docrop = 0) or (sampq.cropid = :classid and :docrop = 1))
--        and operation.operationtype not in (2,6)
        and operation.operationtype <> 2
        and it.state >= 0
        and it.parentid <> -1
        and ((it.storingid = :storingid) or (:storingid = 0))
        and ((it.silageid = :silageid) or (:silageid = 0))
        and it.net is not null
    into
     :maxdate;

  FOR
    SELECT
      stations.stationname,
      it.regdate,
      SUM(coalesce(it.net, 0)),
      case when (ittransport.transporttypeid = 2) then
        cast(ittransport.transportnumber as varchar(31))
          when (operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1)) then
          invoices.invoicenumber
         else
          cast (invoiceregisters.registernumber as varchar(31))
      end registerid,

     labq.qualitynumber,

     case when ((:useafterqualty) = 0 or (it.operationid <> 7)) then
       labq.dirt
     else
       afterq.dirt
      end dirt1,

    case when ((:useafterqualty) = 0 or (it.operationid <> 7)) then
       labq.humidity
     else
       afterq.humidity
      end humidity1,

     case when ((operation.operationtype in (1,3,4,5)) or (operation.operationid = 70)) then
       suplier1.clientfullname
     else
       clients.clientfullname
     end provider,

     case when ((operation.operationtype in (1,3,4,5)) or (operation.operationid = 70)) then
       operation.operationname
     else
       suplier1.clientfullname 
     end owner,

     operationsubtype, storageto.storagename,

     case when ittransport.transporttypeid in (0,1) then 'AM'
     when ittransport.transporttypeid = 2 then '��'
     when ittransport.transporttypeid = 3 then '�'
     when it.operationid = 17 then '������.'  end transporttypestr,
     it.operationid

    FROM it
      join getworkparties(:partyid,:partydate,null,null,null)
         on (it.partyid = getworkparties.partyid)
       left JOIN operation ON (operation.operationid = it.operationid)
       left join invoices on (it.invoiceid = invoices.invoiceid)
       left join invoiceregisters on (it.registerid = invoiceregisters.invoiceregisterid)
       left join stations on (invoices.stationid = stations.stationid)
       left join quality_select(it.labqualityid) labq on (0 = 0)
       left join quality_select(it.samplerqualityid) sampq on (0 = 0)

       left join uwoutacts on (it.invoiceid = uwoutacts.invoiceid)
       left join quality_select(uwoutacts.afterqualityid) afterq on (0 = 0)

       LEFT JOIN class ON (sampq.classid = class.classid)
       LEFT JOIN crop ON (sampq.cropid = crop.cropid)
       LEFT JOIN clients suplier1 ON (it.ownerid = suplier1.clientid)
       LEFT JOIN storage storage1 ON (it.storageid = storage1.storageid)
       LEFT JOIN clients ON (it.providerid = clients.clientid)
       Left Join storage storageto on (it.providerid = storageto.storageid)
       left join ittransport on (it.inventoryid = ittransport.inventoryid)
    WHERE
         it.regdate BETWEEN :date1 and :date2
        and storage1.storageid = :storage
        and ((sampq.classid = :classid and :docrop = 0) or (sampq.cropid = :classid and :docrop = 1))
--        and operation.operationtype not in (2,6)
        and operation.operationtype <> 2
        and it.state >= 0
        and it.parentid <> -1
        and ((it.storingid = :storingid) or (:storingid = 0))
        and ((it.silageid = :silageid) or (:silageid = 0))
        and it.net is not null
    GROUP BY  stations.stationname,it.regdate,
     registerid,

     labq.qualitynumber,

     dirt1,

    humidity1,

     provider,

     owner,
       operationsubtype,
       storageto.storagename,

     transporttypestr,
     it.operationid
    INTO :stationname,
         :regdate,
         :net,
         :registerid,
         :qualitynumber,
         :dirt,
         :humidity,
         :provider,
         :owner,
         :operationsubtype,
         :storagename,
         :transporttypestr,
         :operationid
  DO
  BEGIN
    if (((:operationsubtype = 0) and (:operationid <> 17)) or ((:operationid = 17) and (:net > 0))) then
    begin
      outcome = 0;
      dirtout = 0;
      humidityout = 0;
      outcome_din = 0;
      outcome_hin = 0;
      dirtin = :dirt;
      humidityin = :humidity;
      income = :net;
      income_din = :income * :dirtin / 100;
      income_hin = :income * :humidityin / 100;
    end
    else if ((:operationsubtype = 1) or ((:operationid = 17) and (:net < 0))) then
    begin
      income = 0;
      dirtin = 0;
      income_din = 0;
      income_hin = 0;
      humidityin = 0;
      dirtout = :dirt;
      humidityout = :humidity;
      if (:operationid = 17) then
        outcome = :net* -1;
      else
        outcome = :net;
      outcome_din = :outcome * :dirtout / 100;
      outcome_hin = :outcome * :humidityout / 100;
    end
   if ((coalesce(:income, 0) <> 0) or (coalesce(:dirtin, 0) <> 0) or (coalesce(:humidityin,0) <> 0) and
      (coalesce(:dirtout,0 ) <> 0) or (coalesce(:humidityout, 0) <> 0) or (coalesce(:outcome, 0) <> 0)) then
   begin

    if (:maxdate = :regdate) then
      islastrecord = 1;
    else
      islastrecord = 0;

    if (:transporttypestr is not null) then
      owner = :transporttypestr||' '||:owner;
    SUSPEND;
   end
  END
END