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

ACC_FOREC_VEDOMPRANDOTGR

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_FOREC_VEDOMPRANDOTGR(
    DATE1 DATE,
    DATE2 DATE,
    FIRSTDATEINYEAR DATE)
RETURNS (
    CLIENTNAME VARCHAR(50),
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    INCOME NUMERIC(15,1),
    OUTCOME NUMERIC(15,1),
    PRICE NUMERIC(15,2),
    SUMMA NUMERIC(15,2),
    INCOMEFROMSTART NUMERIC(15,1),
    OUTCOMEFROMSTART NUMERIC(15,1),
    SUMMAFROMSTART NUMERIC(15,2),
    CROPID INTEGER)
AS
DECLARE VARIABLE operationsubtype SMALLINT;
DECLARE VARIABLE net NUMERIC(15,1);
DECLARE VARIABLE ownerid INTEGER;
DECLARE VARIABLE classid INTEGER;
DECLARE VARIABLE inthismonth INTEGER; /* 1- in current month, 2 - else */
DECLARE VARIABLE tempsum NUMERIC(15,2);
BEGIN
    income = 0;
    outcome = 0;
    price = 0;
    summa = 0;
    outcomefromstart = 0;
    incomefromstart = 0;
    summafromstart = 0;

  for
    select clients.clientname, it.ownerid, crop.cropname, class.classname, q1.cropid, q1.classid
   from  it
      left join clients on (it.ownerid = clients.clientid)
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join operation  ON (it.operationid = operation.operationid)
      left join crop on (q1.cropid = crop.cropid)
--      left join quality_cropfullname(q1.cropid, q1.classid, null, 0) qn on (0 = 0)
      left join class on (q1.classid = class.classid)
   where it.net is not null
     and it.regdate between :firstdateinyear and :date2
     and crop.croptypeid <> 3
     and it.state >= 0
     and it.parentid >= 0
     and operation.operationsubtype in (0,1)
     and operation.operationtype not in (6,4,2,5)
     and it.operationid <> 70
    group by clients.clientname, it.ownerid, crop.cropname, class.classname, q1.cropid, q1.classid
    order by  clients.clientname, crop.cropname, class.classname
   into
     :clientname,
     :ownerid,
     :cropname,
     :classname,
     :cropid,
     :classid
   do
   begin
   for
     select SUM(it.net),
            case when (it.regdate between :date1 and :date2) then 1 else 2 end inthismonth,

          case when (operation.operationsubtype = 1) then
             Sum(cast(it.net as double precision) *  (select first 1 contractcosts.cost from contractcosts
             inner join quality contractq on (contractcosts.qualityid = contractq.qualityid
             and ((contractq.classid = q1.classid) or ((contractq.cropid = q1.cropid) and (contractq.classid is null))
             ))
             where it.contractid = contractcosts.contractid and contractcosts.serviceid = 7 and (contractcosts.contractcostdate <= it.regdate) order by contractcosts.contractcostdate desc))
          end,

          operation.operationsubtype
   from  it
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join operation  ON (it.operationid = operation.operationid)
     where it.net is not null
     and it.regdate between :firstdateinyear and :date2
     and it.state >= 0
     and it.parentid >= 0
     and operation.operationsubtype in (0,1)
     and operation.operationtype not in (6,4,2,5)
     and it.operationid <> 70
     and it.ownerid = :ownerid
     and q1.classid = :classid
    group by
      inthismonth,
      operation.operationsubtype
    into
     :net,
     :inthismonth,
     :tempsum,
     :operationsubtype
   do
  begin
     if (:net is null) then
       net = 0;
     if (:tempsum is null) then
       tempsum = 0;

     if (:operationsubtype = 0) then
     begin
       if (:inthismonth = 1) then
         income = :income + :net;
       incomefromstart = :incomefromstart + :net;
     end

     else if ((:operationsubtype = 1)) then
     begin
       if (:inthismonth = 1) then
       begin
         summa = :summa + :tempsum;
         outcome = :outcome + :net;
       end
       summafromstart = :summafromstart + :tempsum;
       outcomefromstart = :outcomefromstart + :net;
     end
  end

    income =  cast(:income as double precision)/ 1000;
    outcome = cast(:outcome as double precision)/ 1000;
    summa = cast(:summa as double precision)/ 1000;
    outcomefromstart = cast(:outcomefromstart as double precision)/ 1000;
    incomefromstart = cast(:incomefromstart as double precision)/ 1000;
    summafromstart = cast(:summafromstart as double precision)/ 1000;


   if (:outcome <> 0)
    then
     price = cast(:summa as double precision)/ (:outcome);
   else
     price = 0;

   SUSPEND;

    income = 0;
    outcome = 0;
    price = 0;
    summa = 0;
    outcomefromstart = 0;
    incomefromstart = 0;
    summafromstart = 0;
  end
END