create procedure dbo.%PROC% @sid varchar(30) = null
as
declare @action_id numeric,
        @id numeric,
        @user_id numeric,
        @state_id numeric,
        @new_state_id numeric,
        @type_id numeric
begin

  select @action_id = %ACTION_ID%
  select @id = convert(numeric, @sid)
begin tran
.GET_PROPS

declare
@proc_result numeric,
@owner_id numeric,
@org_id numeric,
@foundation_id numeric,
@dogovor_id numeric,
@contragent_id numeric,
@character_id numeric,
@deal_id numeric,
@full_qty money,
@full_sum money,
@bill_pos_id numeric,
@investor_id numeric,
@type_partition2investor_id numeric,
@type_partition2oper_id numeric,
@type_bill_display_id numeric,
@doc_sum money,
@part_sum_id numeric,
@contract_id numeric,
@price_position_id numeric,
@s_sum money,
@qty money,
@count_deal int,
@count_com money,
@count_com_nds money,
@count_exec int,
@com2deal money,
@price_position_cl_id numeric,
@is_nds int,
@is_nds_in_revard int,
@nds money,
@nds_sum money,
@all_nds_sum money,
@com money,
@plan_money_id numeric,
@cur2plan_id numeric,
@cur2tarif_id numeric,
@price_list_id numeric,
@service_id numeric,
@comis_currency money,
@rate_value money,
@rate_date datetime,
@comis2nds money

select @foundation_id = (select foundation_id from td_depo_docs where id=@id)
select @owner_id = (select owner_id from td_depo_docs where id = @foundation_id)
select @org_id =(.BACK_GET_BROKER_ID(@owner_id))
select @plan_money_id = (select back_money_id from tb_broker_change where id = @org_id)
select @cur2plan_id = (.CUR2PLAN(@plan_money_id))
select @dogovor_id=(select contract_id from td_queries where id=@foundation_id)
select @nds=convert(money,(.GET_CONST('VAT')))

update td_depo_doc2bill
  set  rate_cur_id=@rate_cur_id,
       rate=@sum_rate,
       cur_id=@cur_id,
       bill_list_sum=doc_sum*@sum_rate
 where bill_id=@id

declare @vat money
select @vat =  (1+@nds)/100.0
select @sum_total=sum(isnull(doc_sum,0))
  from td_depo_doc2bill
 where bill_id=@id
   and .ITEMS_EXISTS(td_depo_doc2bill.id)
select @clear_sum_total = round(@sum_total/@vat,2)
select @sum_tax = @sum_total - @clear_sum_total

.UPDATE(ALL)
.ACTION_HISTORY

commit tran
.TRANSACTION_SAVE

create table #inv2obor
( bill_pos_id numeric,
  type_partition2investor_id numeric,
  type_partition2oper_id numeric,
  type_bill_display_id numeric,
  part_sum_id numeric,
  contract_id numeric,
  price_position_id numeric,
  doc_sum money,
  investor_id numeric,
  s_sum money,
  qty money,
  com money null)

insert into #inv2obor(bill_pos_id, type_partition2investor_id, investor_id, s_sum, qty)
select
 td_depo_doc2bill.id,
 td_depo_doc2bill.type_partition2investor_id,
 tb_bill2dealings.investor_id,
 (case td_depo_doc2bill.type_partition2investor_id
     when 1 then  sum(tb_bill2dealings.deal_sum*tb_bill2dealings.bay_or_sale)
     else sum(tb_bill2dealings.deal_sum)
 end),
 (case td_depo_doc2bill.type_partition2investor_id
     when 1 then  sum(tb_bill2dealings.stock_qty*tb_bill2dealings.bay_or_sale)
     else sum(tb_bill2dealings.stock_qty)
  end)
from td_depo_doc2bill,
     tb_bill2dealings
where  td_depo_doc2bill.bill_id=@id
  and (.ITEMS_EXISTS_BY_TYPE(td_depo_doc2bill.id,'BILL_IN_COM_OPER'))
  and type_exec_id in (1,2,3)
  and tb_bill2dealings.pos2bill_id = td_depo_doc2bill.id
group by td_depo_doc2bill.id, td_depo_doc2bill.type_partition2investor_id,
         td_depo_doc2bill.type_partition2oper_id, tb_bill2dealings.investor_id

--select * from #inv2obor

update #inv2obor
 set
   qty = abs(qty),
   s_sum = abs(s_sum),
   doc_sum = td_depo_doc2bill.doc_sum,
   type_bill_display_id = td_depo_doc2bill.type_bill_display_id,
   type_partition2oper_id = td_depo_doc2bill.type_partition2oper_id,
   part_sum_id = td_depo_doc2bill.part_sum_id,
   contract_id = td_depo_doc2bill.contract_id,
   price_position_id = td_depo_doc2bill.price_position_id
from td_depo_doc2bill
where td_depo_doc2bill.id = #inv2obor.bill_pos_id

select @full_qty=(select sum(qty) from #inv2obor)
select @full_sum=(select sum(s_sum) from #inv2obor)

update #inv2obor
set
  com = (case type_partition2investor_id
           when 3 then (case  type_partition2oper_id
                          when 1 then round(convert(money,(convert(double precision,doc_sum)*convert(double precision,abs(qty))/convert(double precision,@full_qty,2))),2)
                          else round(convert(money,(convert(double precision,doc_sum)*convert(double precision,abs(s_sum))/convert(double precision,@full_sum,2))),2)
                        end)
           else (case part_sum_id
                   when 1 then round(convert(money,(convert(double precision,doc_sum)*convert(double precision,abs(s_sum))/convert(double precision,@full_sum,2))),2)
                   else round(convert(money,(convert(double precision,doc_sum)*convert(double precision,abs(qty))/convert(double precision,@full_qty,2))),2)
                 end)

           end)

--select * from #inv2obor
-- type_partition2investor_id: разброс по инвесторам: 1 - пропорц. НЕТТО оборотам; 2 - пропорц. полным оборотам; 3 - без разбиения
-- type_partition2oper_id: разбиение по операциям: 1 - пропорционально кол - ву ЦБ; 2 - пропорционально суммам
-- part_sum_id: опорная сумма: 1 - Сумма операции; 2 - количество ЦБ

declare cur2inv2obor cursor for
 select *
 from #inv2obor
for read only

open cur2inv2obor
fetch cur2inv2obor into @bill_pos_id, @type_partition2investor_id, @type_partition2oper_id,
                        @type_bill_display_id, @part_sum_id, @contract_id, @price_position_id,
                        @doc_sum, @investor_id, @s_sum, @qty, @com

while .CURSOR_STATE=0
begin
  if exists(select 1 from tb_contracts where id=@investor_id)
      select @character_id=2
    else
     select @character_id=1
  if @type_bill_display_id in (1,2,3)
    begin
      if @type_partition2investor_id in (1,2,3)
      begin
        select @all_nds_sum = @doc_sum - round(@doc_sum/(1 + (@nds/100.0)),2)
        declare cur2deal cursor for
          select
              tb_bill2dealings.id
          from tb_bill2dealings
          where investor_id=@investor_id
            and pos2bill_id=@bill_pos_id
          for read only

          open cur2deal
          fetch cur2deal into @deal_id

          while .CURSOR_STATE=0
          begin
            .TYPE_GET('bill2dealing',@deal_id,1)

            select @count_deal=(select count(tb_bill2dealings.id)
                                from tb_bill2dealings
                                where pos2bill_id = @bill_pos_id)

            select @count_com=isnull((select sum(isnull(comis,0))
                                      from tb_bill2dealings
                                      where pos2bill_id=@bill_pos_id
                                        and .ITEMS_EXISTS_BY_TYPE_STATE(tb_bill2dealings.id,'BILL2DEALING','EXEC')),0)

            select @count_com_nds=isnull((select sum(isnull(comis_nds,0))
                                          from tb_bill2dealings
                                          where pos2bill_id = @bill_pos_id
                                            and .ITEMS_EXISTS_BY_TYPE_STATE(tb_bill2dealings.id,'BILL2DEALING','EXEC')),0)

            select @count_exec=(select count(tb_bill2dealings.id)
                                from tb_bill2dealings
                                where pos2bill_id = @bill_pos_id
                                  and .ITEMS_EXISTS_BY_TYPE_STATE(tb_bill2dealings.id,'BILL2DEALING','EXEC'))
            if @dogovor_id is null
            begin
              select @dogovor_id=@contract_id
              select @contragent_id = (select contract_id
                                       from td_depo_doc2bill
                                       where td_depo_doc2bill.id = @bill_pos_id)
            end
            else
            begin
              .BACK_CALC_CONTRAGENT(@contragent_id, @price_position_id, @dogovor_id, @owner_id,2,@character_id)
            end

            if @type_partition2investor_id in (1,2) -- разбивка пропорционально нетто или полным оборотам по ИНВЕСТОРУ
            begin
               if @type_partition2oper_id=1    --Пропорционально кол-ву ЦБ по оборотам ИНВЕСТОРА
                  select @com2deal=round(convert(money,convert(double precision,@com)*convert(double precision,@stock_qty1)/convert(double precision,@qty)),2)
               else                            --Пропорционально суммам по оборотам ИНВЕСТОРА
                  select @com2deal=round(convert(money,convert(double precision,@com)*convert(double precision,@deal_sum1)/convert(double precision,@s_sum)),2)
            end
            else
              if @type_partition2investor_id = 3 -- без разбивки (т.е пропорционально полным оборотам по всем инвесторам)
              begin
                if @type_partition2oper_id=1   --Пропорционально кол-ву ЦБ по оборотам всех ИНВЕСТОРОВ
                  select @com2deal=round(convert(money,convert(double precision,@com)*convert(double precision,@stock_qty1)/convert(double precision,@full_qty)),2)
                else                           --Пропорционально суммам по оборотам всех ИНВЕСТОРОВ
                  select @com2deal=round(convert(money,convert(double precision,@com)*convert(double precision,@deal_sum1)/convert(double precision,@full_sum)),2)
              end

            if (@count_deal-1)=@count_exec  ---сделка последняя
            begin
              if (@count_com+@com2deal)<= @doc_sum
                select @com2deal = @com2deal+(@doc_sum - (@count_com + @com2deal))
              else
                select @com2deal = @com2deal - ((@count_com + @com2deal) - @doc_sum)
            end
            select @is_nds=.GET_IS_NDS(@portfolio_sub_id1)
            select
               @is_nds_in_revard=is_nds
            from tb_settings_comis
            where settings_id=@price_position_id

            if @is_nds=1 and @is_nds_in_revard<>2
              select @nds_sum = round(convert(money,(@com2deal * (@nds/100.0))/(1 + (@nds/100.0))),2)
            if (@count_deal - 1) = @count_exec  ---сделка последняя
            begin
              if (@count_com_nds + @nds_sum)<= @all_nds_sum
                select @nds_sum = @nds_sum + (@all_nds_sum - (@count_com_nds + @nds_sum))
              else
                select @nds_sum = @nds_sum - ((@count_com_nds + @nds_sum) - @all_nds_sum)
            end

            select @comis2nds=@com2deal
            select @com2deal=(@com2deal-isnull(@nds_sum,0))

            select
              @price_list_id=price_list_id,
              @service_id=service_id,
              @cur2tarif_id = cur_tariff_id
            from td_prices where id=@price_position_id

            update tb_bill2dealings
            set
              comis=@comis2nds,
              comis_cur_id=@cur2tarif_id,
              comis_nds=@nds_sum
            where id=@deal_id

            .ITEM_MOVE_STATE(@deal_id,'EXEC')

             if @cur2plan_id <> @cur2tarif_id
             begin
               .CONVERT_SUM(@cur2tarif_id,@cur2plan_id,@in_date,0,@comis2nds,@comis_currency,@rate_value,@rate_date)
             end
             else
               select @comis_currency = @comis2nds

            .TYPE_INIT('TD_COMIS2OPER')
            .TYPE_ASSIGN(item_id,@item_id1)
            .TYPE_ASSIGN(comis,@comis2nds)
            .TYPE_ASSIGN(comis_currency,@comis_currency)
            .TYPE_ASSIGN(comment,'Начисление по истечении периода')
            .TYPE_ASSIGN(cur_id,@cur2tarif_id)
            .TYPE_ASSIGN(currency,@cur2plan_id)
            .TYPE_ASSIGN(price_list_id,@price_list_id)
            .TYPE_ASSIGN(price_position_id,@price_position_id)
            .TYPE_ASSIGN(recipient_id,@contragent_id)
            .TYPE_ASSIGN(service_id,@service_id)
            .TYPE_ASSIGN(is_edit,0)
            .TYPE_ASSIGN(is_nds,(1-@is_nds))
            .TYPE_ASSIGN(comis_foundation_id,@id)
            .TYPE_INSERT('TD_COMIS2OPER','PODT_LAST')

            fetch cur2deal into @deal_id
          end
          close cur2deal
         .DEALLOCATE cur2deal
      end
    end

  fetch cur2inv2obor into @bill_pos_id, @type_partition2investor_id, @type_partition2oper_id,
                          @type_bill_display_id, @part_sum_id, @contract_id, @price_position_id,
                          @doc_sum, @investor_id, @s_sum, @qty, @com
end
close cur2inv2obor

.DEALLOCATE cur2inv2obor

drop table #inv2obor

.TRANSACTION_RESTORE

--.EXEC_ACTION(@sid,'BILL_IN','OPER_BILL_IN_BACK')

end