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

FORM36CLIENT

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

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

Описание

Форма 36 по клиенту

Определение

CREATE PROCEDURE FORM36CLIENT(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    CLASS INTEGER,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    STORINGID INTEGER,
    SILAGEID INTEGER,
    PARTYID INTEGER,
    USEAFTERQUALTY INTEGER,
    PARTYDATE DATE,
    DOCROP SMALLINT,
    DONET SMALLINT)
RETURNS (
    REGDATE DATE,
    CLASSNAME VARCHAR(20),
    CROPNAME VARCHAR(20),
    QUALITYNUMBER INTEGER,
    DIRTIN NUMERIC(9,2),
    HUMIN NUMERIC(9,2),
    HUMOUT NUMERIC(9,2),
    DIRTOUT NUMERIC(9,2),
    PROVIDER VARCHAR(100),
    OWNER VARCHAR(100),
    INCOME INTEGER,
    OPERATIONSUBTYPE INTEGER,
    REGISTERID VARCHAR(31),
    OPERATIONID INTEGER,
    STATIONNAME VARCHAR(30),
    OUTCOME INTEGER,
    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 weight integer;
declare variable operationtype integer;
declare variable maxdate date;
begin
  select max(it.regdate)
    from it
      join getworkparties(:partyid,:partydate,null,null,:storageid)
        on (it.partyid = getworkparties.partyid)
     left join operation on (it.operationid = operation.operationid)
     left join quality_select(it.BuhQualityid) quality on (0 = 0)
    where it.regdate between :date1 and :date2
     and it.storageid= :storageid
     and operation.operationsubtype  in (0,1)
    and operation.operationtype <> 2
     and it.state >= 0
     and it.parentid >= 0
     and ((quality.classid = :class and :docrop = 0) or (quality.cropid = :class and :docrop = 1))
     and it.ownerid= :ownerid

     and ((it.storingid = :storingid) or (:storingid = 0))
     and ((it.contractid = :contractid) or (:contractid = 0))
     and ((it.silageid = :silageid) or (:silageid = 0))
  into :maxdate;

  for
  select
    stations.stationname,
    it.operationid,
    it.regdate,
    class.classname,
    crop.cropname,
    lquality.qualitynumber,
    case
      when (:useafterqualty = 1  and it.operationid = 7) then
        afterq.dirt
      else
       lquality.dirt end dirt1,

    case
      when (:useafterqualty = 1  and it.operationid = 7) then
        afterq.humidity
      else
       lquality.humidity end humidity1,

    case
      when ((operation.operationtype in (1,2,3,4,5))) then
        cast (operation.operationname as varchar(100))
      else
        suplier2.clientname end provider,
    suplier1.clientname owner,

    coalesce(cast(sum(abs.result) as integer),0) absweight,

    case :donet
    when 0 then coalesce(cast(sum(it.weight) as integer),0)
    when 1 then coalesce(cast(sum(it.net) as integer),0) end,
    operation.operationsubtype,
    operation.operationtype,
    case
      when ((((operation.operationsubtype  = 1) or (it.operationid = 17))
       and (ittransport.transporttypeid <> 2))
       or (operation.operationtype = 3) ) then
        cast(invoices.invoicenumber as varchar(31))
      when ((ittransport.transporttypeid = 2)) then
        cast(ittransport.transportnumber as varchar(31))
      else
        cast(invoiceregisters.registernumber as varchar(31))
     end  registerid
    from it
      join getworkparties(:partyid,:partydate,null,null,:storageid)
       on (it.partyid = getworkparties.partyid)
     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 operation on (it.operationid = operation.operationid)

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

     left join quality_select(it.BuhQualityid) lquality on (0 = 0)
     left join class on (lquality.classid = class.classid)
     left join crop on (lquality.cropid = crop.cropid)
     left join clients suplier1 on (it.ownerid = suplier1.clientid)
     left join clients suplier2 on (it.providerid = suplier2.clientid)
     left join contracts on (it.contractid = contracts.contractid)
     left join abs(it.weight) on (0=0)
     left join ittransport on (it.inventoryid = ittransport.inventoryid)
    where it.regdate between :date1 and :date2
     and it.storageid= :storageid
     and operation.operationsubtype  in (0,1)
     and operation.operationtype <> 2
     and it.state >= 0
     and it.parentid >= 0
     and ((lquality.classid = :class and :docrop = 0) or (lquality.cropid = :class and :docrop = 1))
     and it.ownerid= :ownerid
     and ((it.storingid = :storingid) or (:storingid = 0))
     and ((it.contractid = :contractid) or (:contractid = 0))
     and ((it.silageid = :silageid) or (:silageid = 0))
    group by
    stations.stationname,
    it.operationid,
    it.regdate,
    class.classname,
    crop.cropname,
    lquality.qualitynumber,
    dirt1,
    humidity1,
    provider,
    owner,
    operation.operationsubtype, operation.operationtype,  registerid
  into :stationname,
         :operationid,
         :regdate,
         :classname,
         :cropname,
         :qualitynumber,
         :dirt,
         :humidity,
         :provider,
         :owner,
         :weight,
         :net,
         :operationsubtype,
         :operationtype,
         :registerid
  do
  begin
  if (:operationsubtype = 0) then
    begin
      outcome =0;
      outcome_din = 0;
      outcome_hin = 0;
      dirtout = 0;
      humout = 0;
      dirtin = :dirt;
      humin = :humidity;
--      income_din = :income * :dirtin / 100;
--      income_hin = :income * :humin / 100;
      if (:operationtype in (1,2,4,5)) then
        income = :weight;
      else
        income = :net;

      income_din = (cast(:dirtin as numeric(9,2)) * :income)/100;
      income_hin = (cast(:humin as numeric(9,2)) * :income)/100;
    end

  if ((:operationsubtype = 1)  or ((:operationid = 17) and (:net < 0))) then
    begin
      income = 0;
      income_din = 0;
      income_hin = 0;
      dirtin = 0;
      humin = 0;
      dirtout = :dirt;
--      outcome_din = :outcome * :dirtout / 100;
--      outcome_hin = :outcome * :humout / 100;
      humout = :humidity;
      if ((:operationid = 17) or (:operationtype in (1,2,4,5))) then
        outcome = :weight;
      else
        outcome = :net;

      outcome_din  = (cast(:dirtout as numeric(9,2)) * :outcome)/100;
      outcome_hin = (cast(:humout as numeric(9,2)) * :outcome)/100;

    end

   if ((coalesce(:income, 0) <> 0) or (coalesce(:dirtin, 0) <> 0) or (coalesce(:humin,0) <> 0) and
      (coalesce(:dirtout,0 ) <> 0) or (coalesce(:humout, 0) <> 0) or (coalesce(:outcome, 0) <> 0)) then
   begin
    if (:maxdate = :regdate) then
      islastrecord = 1;
    else
      islastrecord = 0;

    if (:operationid = 17) then
      provider = 'переоф.'||' '||:provider;
    suspend;
   end
  end
end