"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