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

SERVICES_CALC

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

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

Описание

Ведомость начисления за услуги по всем культурам CalculationServices.frf

Определение

CREATE PROCEDURE SERVICES_CALC(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    CLIENTNAME VARCHAR(30),
    REGIONNAME VARCHAR(100),
    ACCEPTANCEBEFORE DECIMAL(15,2),
    ACCEPTANCE DECIMAL(15,2),
    DRYBEFORE DECIMAL(15,2),
    DRY DECIMAL(15,2),
    CLEANBEFORE DECIMAL(15,2),
    CLEAN DECIMAL(15,2),
    STORINGNETBEFORE DECIMAL(15,2),
    STORINGNET DECIMAL(15,2),
    REBILLBEFORE DECIMAL(15,2),
    REBILL DECIMAL(15,2),
    OUTCOMEBEFORE DECIMAL(15,2),
    OUTCOME DECIMAL(15,2),
    OTHERBEFORE DECIMAL(15,2),
    OTHER DECIMAL(15,2),
    TOTALBEFORE DECIMAL(15,2),
    TOTAL DECIMAL(15,2))
AS
declare variable ownerid integer;
declare variable storingprice decimal(15,2);
declare variable rebillprice decimal(15,2);
declare variable outcomeprice decimal(15,2);
declare variable temp decimal(15,3);
declare variable tempdate date;
declare variable acceptanceprice decimal(15,2);
declare variable dryprice decimal(15,2);
declare variable underworkprice decimal(15,2);
declare variable humpercent decimal(15,5);
declare variable dirtpercent decimal(15,2);
declare variable humpercent_simple decimal(15,2);
begin

  select coalesce(services.basecost,0) from services
  where services.serviceid = 1
  into
  :STORINGPRICE;

  select coalesce(services.basecost,0) from services
  where services.serviceid = 6
  into
  :REBILLPRICE;

  select coalesce(services.basecost,0) from services
  where services.serviceid = 7
  into
  :OUTCOMEPRICE;

  for
--priemka
   select it.ownerid, clients.clientname, regions.regionname
   from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   inner join clients on (it.ownerid =  clients.clientid)
   left join regions on (clients.regionid = regions.regionid)
   left join quality on (it.BuhQualityid = quality.qualityid)
   where it.regdate <= :date2
     and IT.State >= 0
     and it.parentid >= 0

   group by it.ownerid, clients.clientname,regions.regionname
   into
   :OWNERID,
   :CLIENTNAME,
   :REGIONNAME
  do
  begin

  dry = 0;
  DRYBEFORE = 0;
  clean = 0;
  CLEANBEFORE = 0;
  ACCEPTANCEBEFORE = 0;
  ACCEPTANCE = 0;
 for
  select (coalesce(invoiceregisters.incomecost,0)),  coalesce(sum(it.net),0), it.regdate,
--   case when (coalesce(sum(it.net),0) <= 50000) then (coalesce(sum(it.net),0) + 2)
--      else (coalesce(sum(it.net),0) + (coalesce(sum(it.net),0)/20000)) end, it.regdate,

       coalesce(invoiceregisters.drycost,0),
       coalesce(invoiceregisters.underworkcost,0),

   coalesce((((q2.humidity - q.humidity)* 100) /(100 - q.humidity)),0),

   case when coalesce((q2.humidity - q.humidity),0) < 0 then 0
      else coalesce((q2.humidity - q.humidity),0) end,

   coalesce((q2.dirt - q.dirt),0)

--  case when coalesce((q2.humidity - q.humidity),0) < 0 then 0 else coalesce((q2.humidity - q.humidity),0) end,
-- case when coalesce((q2.dirt - q.dirt),0) < 0 then 0 else coalesce((q2.dirt - q.dirt),0) end
 from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
  left join invoices on (it.invoiceid = invoices.invoiceid)
  left join invoiceregisters on (it.registerid = invoiceregisters.invoiceregisterid)
  left join quality_select(invoiceregisters.qualityid) q on (0 = 0)
  left join quality_select(it.BuhQualityid) q2 on (0 = 0)
  where it.regdate <= :date2
  and IT.State >= 0
  and it.parentid <> -1
  and it.ownerid = :OWNERID
  and it.registerid is not null
  group by invoiceregisters.registernumber, invoiceregisters.incomecost, it.regdate, invoiceregisters.drycost,
    invoiceregisters.underworkcost,
    q2.humidity, q.humidity, q2.dirt, q.dirt
  into
   :ACCEPTANCEPRICE,
   :TEMP,
   :TEMPDATE,
   :DRYPRICE,
   :UNDERWORKPRICE,
   :HUMPERCENT,
   :HUMPERCENT_SIMPLE,
   :DIRTPERCENT
  do
  begin
   temp = :TEMP/1000;
--   DIRTPERCENT = cast(:DIRTPERCENT as DECIMAL(3,2))/100;
   HUMPERCENT = cast(:HUMPERCENT as DECIMAL(15,5))/100;

   if (:TEMPDATE < :DATE1) then
   begin
     DRYBEFORE = :DRYBEFORE + :DRYPRICE * :TEMP * :HUMPERCENT_SIMPLE * 1.2;
     ACCEPTANCEBEFORE = :ACCEPTANCEBEFORE + :ACCEPTANCEPRICE * :TEMP * 1.2;
     CLEANBEFORE = :CLEANBEFORE + :UNDERWORKPRICE * (:TEMP - (:HUMPERCENT * :TEMP)) * :DIRTPERCENT * 1.2;
   end
   else
   begin
     DRY = :DRY + :DRYPRICE * :TEMP * :HUMPERCENT_SIMPLE * 1.2;
     ACCEPTANCE = :ACCEPTANCE + :ACCEPTANCEPRICE * :TEMP * 1.2;
     CLEAN = :CLEAN + :UNDERWORKPRICE * (:TEMP- (:HUMPERCENT * :TEMP))* :DIRTPERCENT * 1.2;
   end
  end

   select coalesce(sum(it.net),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
   left join operation on (it.operationid = operation.operationid)
   where it.regdate between :date1 and :date2
   and operation.operationsubtype = 0
   and it.ownerid = :OWNERID
   and it.storingid = 1
   and it.net <> 0
   and it.net is not null
   and IT.State >= 0
   and it.parentid <> -1
   into
    :STORINGNET;

  select coalesce(sum(it.net),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
   left join operation on (it.operationid = operation.operationid)
  where it.regdate < :date1 and operation.operationsubtype = 0
   and it.ownerid = :OWNERID and it.storingid = 1
   and IT.State >= 0
   and it.parentid <> -1
  into
   :STORINGNETBEFORE;

  STORINGNET = :STORINGNET * :STORINGPRICE / 1000 * 1.2;
  STORINGNETBEFORE = :STORINGNETBEFORE * :STORINGPRICE / 1000 * 1.2;

--rebillings
  select coalesce(sum(it.net*-1),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
  where it.regdate between :date1 and :date2 and it.operationid = 17
   and it.ownerid = :OWNERID
   and it.net < 0
   and IT.State >= 0
   and it.parentid <> -1

  into
   :REBILL;

  select coalesce(sum(it.net*-1),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
  where it.regdate < :date1 and it.operationid = 17
   and it.ownerid = :OWNERID
   and it.net < 0
   and IT.State >= 0
   and it.parentid <> -1

  into
   :REBILLBEFORE;

  REBILL = :REBILL * :REBILLPRICE / 1000 * 1.2;
  REBILLBEFORE = :REBILLBEFORE * :REBILLPRICE / 1000 * 1.2;

--outcome
  select coalesce(sum(it.net),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
  where it.regdate between :date1 and :date2 and it.operationid = 5
   and it.ownerid = :OWNERID
   and IT.State >= 0
   and it.parentid <> -1
  into
   :OUTCOME;

  select coalesce(sum(it.net),0) from it
     join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
   left join quality on (it.BuhQualityid = quality.qualityid)
  where it.regdate < :date1 and it.operationid = 5
   and it.ownerid = :OWNERID
   and IT.State >= 0
   and it.parentid <> -1

  into
   :OUTCOMEBEFORE;

  OUTCOME = :OUTCOME * :OUTCOMEPRICE/1000 * 1.2;
  OUTCOMEBEFORE = :OUTCOMEBEFORE * :OUTCOMEPRICE/1000 * 1.2;

  OTHER = 0;
  OTHERBEFORE = 0;
  TOTAL = :ACCEPTANCE + :DRY + :CLEAN + :STORINGNET + :REBILL + :OUTCOME + :OTHER;
  TOTALBEFORE = :ACCEPTANCEBEFORE + :DRYBEFORE + :CLEANBEFORE + :STORINGNETBEFORE
   + :REBILLBEFORE + :OUTCOMEBEFORE + :OTHERBEFORE;
  if (:total <> 0) then
    suspend;
  end
end