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

SERVICES_CALC_CROP_CLIENT

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

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

Описание

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

Определение

CREATE PROCEDURE SERVICES_CALC_CROP_CLIENT(
    DATE1 DATE,
    DATE2 DATE,
    CROPID INTEGER,
    OWNERID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    REGDATE DATE,
    INVOICENUMBER VARCHAR(31),
    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 acceptanceprice decimal(15,2);
declare variable storingprice decimal(15,2);
declare variable rebillprice decimal(15,2);
declare variable outcomeprice decimal(15,2);
declare variable net decimal(14,3);
declare variable operationid integer;
declare variable storingid integer;
declare variable operationsubtype integer;
declare variable temp decimal(14,3);
declare variable dryprice decimal(15,2);
declare variable underworkprice decimal(15,2);
declare variable humpercent decimal(15,5);
declare variable dirtpercent decimal(15,5);
declare variable humpercent_simple decimal(15,5);
begin
  storingnetbefore = 0;
  rebillbefore = 0;
  outcomebefore = 0;
  otherbefore = 0;
  acceptancebefore = 0;
  drybefore = 0;
  cleanbefore = 0;

  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
    select
      it.regdate,
      case
        when ((ittransport.transporttypeid = 2)
          or ((operation.operationsubtype = 0) and (it.operationid <> 17) ))
        then invoiceregisters.registernumber
        else invoices.invoicenumber
      end invoicenumber,
      coalesce(sum(it.net),0),
      it.operationid,
      operation.operationsubtype,
      it.storingid
    from it
      join getworkparties(:partyid,:partydate, :cropid,null,null)
        on (it.partyid = getworkparties.partyid)
      left join quality on (it.BuhQualityid  = quality.qualityid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join invoiceregisters on (it.registerid = invoiceregisters.invoiceregisterid)
      left join operation on (it.operationid = operation.operationid)
    where it.regdate <= :date2
      and it.ownerid = :ownerid
      and quality.cropid = :cropid
      and (operation.operationsubtype = 0 or (it.operationid = 5))
      and operation.operationtype not in (3,4)
      and it.state >= 0
      and it.parentid <> -1
    group by
      it.regdate,
      invoicenumber,
      it.operationid,
      operation.operationsubtype,
      it.storingid
    into :regdate,
      :invoicenumber,
      :net,
      :operationid,
      :operationsubtype,
      :storingid
  do
  begin
    net = cast(:net as numeric(10,3))/1000;
    storingnet = 0;
    rebill = 0;
    outcome = 0;
    other = 0;
--priemka --sushka i ochistka
    dry = 0;
    clean = 0;
    acceptance = 0;

    if (:operationid in (1,2)) then --check transporttype
    begin
    for
      select
         (coalesce(invoiceregisters.incomecost,0)),
        coalesce(sum(it.net),0),
        coalesce(invoiceregisters.drycost,0),
        coalesce(invoiceregisters.underworkcost,0),
        coalesce((((itqhum.qualityvalue - inqhum.qualityvalue)* 100) /(100 - inqhum.qualityvalue)),0),
        case when coalesce((itqhum.qualityvalue - inqhum.qualityvalue),0) < 0 then 0
          else coalesce((itqhum.qualityvalue - inqhum.qualityvalue),0) end,
        coalesce((itqdirt.qualityvalue - inqdirt.qualityvalue),0)
      from it
       -- left join invoices on (IT.invoiceid = invoices.invoiceid)
        left join invoiceregisters on (it.registerid = invoiceregisters.invoiceregisterid)
        left join qualitydata inqhum on (invoiceregisters.qualityid = inqhum.qualityid
          and inqhum.qualitytypeid = 2)
        left join qualitydata itqhum on (it.buhqualityid = itqhum.qualityid
          and itqhum.qualitytypeid = 2)
        left join qualitydata inqdirt on (invoiceregisters.qualityid = inqdirt.qualityid
          and inqdirt.qualitytypeid = 13)
        left join qualitydata itqdirt on (it.buhqualityid = itqdirt.qualityid
          and itqdirt.qualitytypeid = 13)
      where it.regdate = :regdate
        and invoiceregisters.registernumber = :invoicenumber
        and it.state >= 0
        and it.parentid <> -1
      group by
        invoiceregisters.incomecost,
        invoiceregisters.drycost,
        invoiceregisters.underworkcost,
        itqhum.qualityvalue,
        inqhum.qualityvalue,
        itqdirt.qualityvalue,
        inqdirt.qualityvalue,
        invoiceregisters.registernumber
      into :acceptanceprice,
        :temp,
        :dryprice,
        :underworkprice,
        :humpercent,
        :humpercent_simple,
        :dirtpercent
    do
    begin
      temp = cast(:temp as numeric(8,3))/1000;
  --  DIRTPERCENT = cast(:DIRTPERCENT as decimal(15,5))/100;
      humpercent = cast(:humpercent as decimal(15,5))/100;

      if (:regdate < :date1) then
      begin
        drybefore = :drybefore + :dryprice * cast(:temp as decimal(14,3))  * :humpercent_simple;
        acceptancebefore = :acceptancebefore + (:acceptanceprice * cast(:temp as decimal(14,3)));
        cleanbefore = :cleanbefore + :underworkprice * (:temp  - (:humpercent * :temp)) * :dirtpercent;
      end
      else if (:regdate >= :date1) then
      begin
        dry = :dry + :dryprice * cast(:temp as decimal(14,3)) * :humpercent_simple;
        acceptance = :acceptance + (:acceptanceprice * cast(:temp as decimal(14,3)));
        clean = :clean + :underworkprice * (:temp - (:humpercent * :temp)) * :dirtpercent;
      end
    end
  end
--storing
  if (:operationsubtype = 0 and :storingid = 1) then
    if (:regdate between :date1 and :date2) then
    begin
      if (:net < 0 ) then
       storingnet = :storingnet + (:net * -1) * :storingprice;
      else
       storingnet = :storingnet + (:net) * :storingprice;
    end
    else if (:regdate < :date1) then
    begin
      if (:net < 0 ) then
      storingnetbefore = :storingnetbefore + (:net * -1)* :storingprice;
      else
      storingnetbefore = :storingnetbefore + (:net)* :storingprice;
    end
--rebillings
    if (:operationid = 17 and :net < 0) then
      if (:regdate between :date1 and :date2) then
        rebill = :rebill + (:net*(-1)) * :rebillprice;
      else if (:regdate < :date1) then
        rebillbefore = :rebillbefore + (:net*(-1)) * :rebillprice;
--outcome
    if (:operationid = 5) then
      if (:regdate between :date1 and :date2) then
        outcome = :outcome + :net * :outcomeprice;
      else if (:regdate < :date1) then
        outcomebefore = :outcomebefore + :net * :outcomeprice;

    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