create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null, /* Брокер */
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @stock_id_s varchar(30) = null,
  @dogovor_id_s varchar(30) = null,
  @section_id_s varchar(30) = null,
  @ocenka varchar(10) = null
as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @id numeric
begin

set nocount on

select @action_id = %ACTION_ID%


declare @broker_id numeric
declare @section_id numeric
declare @stock_id numeric
declare @date_b datetime
declare @date_e datetime
declare @asset_place_id numeric
declare @dogovor_id numeric
declare @cur_plan numeric
declare @cur_partner numeric
declare @subc_str_bal varchar(255)
declare @acc_id numeric, @acc20_id numeric, @acc252_id numeric, @acc440_id numeric,
  @acc444_id numeric, @acc2013_id numeric
declare @nkd_acc_id numeric
declare @stock_rate decimal(18,8)
declare @mess varchar(255)
declare @bs_stock_id numeric
declare @bs_stock_qty money, @nkd money
declare @investor2portfolio_id numeric

create table #stock_rep
(
  acc_id numeric null,
  stock_id numeric null,
  place_id numeric null,
  stock_s_b money null,
  obor_d money null,
  obor_c money null,
  stock_s_e money null,
  stock_qty_b money null,
  stock_qty_e money null,
  obor_qty_d money null,
  obor_qty_c money null,
  port_price money null,
  unkd money null,
  nkd money null
)

if @dogovor_id_s = '' select @dogovor_id_s = null
select @dogovor_id = convert(numeric, @dogovor_id_s)

if @section_id_s = '' select @section_id_s = null
select @section_id = convert(numeric, @section_id_s)

select @broker_id = convert(numeric, @broker_id_s)

if @stock_id_s = '' select @stock_id_s = null
select @stock_id = convert(numeric, @stock_id_s)

  select @cur_partner = partner_id,
         @cur_plan = back_money_id
    from tb_broker_change
    where id = @broker_id

select @date_b = convert(datetime, @date_b_d, 3)
select @date_e = convert(datetime, @date_e_d, 3)

declare @plan2partner_id numeric
select @plan2partner_id=.PLAN2PARTNER_ID(@cur_partner,@cur_plan)

select @subc_str_bal=''
.SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @dogovor_id, Y)

.ACCID_FROM_CONST2PLAN(@acc20_id, 'BACK_ACC_MONEY_PLACE', @cur_plan, 1, 1, 'Счёт по ДС', @mess)
.ACCID_FROM_CONST2PLAN(@acc252_id, 'BACK_ACC_OBLIG_COMIS', @cur_plan, 3, 1, 'Счёт кредит. задолжн.', @mess)
.ACCID_FROM_CONST2PLAN(@acc440_id, 'BACK_ACC_UNKD', @cur_plan, 1, 1, 'Счёт УНКД', @mess)
.ACCID_FROM_CONST2PLAN(@acc444_id, 'BACK_ACC_UNKD_SHORT', @cur_plan, 1, 1, 'Счёт УНКД на короткой позиции', @mess)
.ACCID_FROM_CONST2PLAN(@acc2013_id, 'BACK_ACC_DEPOZIT', @cur_plan, 1, 1, 'Депозитные счета в банках', @mess)

declare @money_fund money, @saldo_e money, @saldo_ras money, @obl_comiss money, @depozit money
.SALDO(@cur_partner, @cur_plan, @date_e, @acc20_id, @subc_str_bal, N, D, @money_fund)
.SALDO(@cur_partner, @cur_plan, @date_e, @acc252_id, @subc_str_bal, N, K, @obl_comiss)
.SALDO(@cur_partner, @cur_plan, @date_e, @acc2013_id,@subc_str_bal, N, D, @depozit)


.ACCID_FROM_CONST2PLAN(@acc_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'ЦБ и вложения', @mess)

select @subc_str_bal=''
.SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y)

if @section_id is not null
begin
  .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO', @section_id, Y)
end

.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y)
--.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y)

.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc_id,@subc_str_bal,L,N,Y,D,P)

--select * from #tmp_osv

delete #tmp_osv where (saldo_de = 0) and (saldo_ce = 0) and (q_saldo_de = 0) and (q_saldo_ce = 0)

insert into #stock_rep(
  stock_id,
  stock_s_b,
  stock_s_e,
  obor_d,
  obor_c,
  stock_qty_b,
  stock_qty_e,
  obor_qty_d,
  obor_qty_c
)
select
  s1_id,
  sum(saldo_db) - sum(saldo_cb),
  sum(saldo_de) - sum(saldo_ce),
  sum(obor_d),
  sum(obor_c),
  sum(q_saldo_db) - sum(q_saldo_cb),
  sum(q_saldo_de) - sum(q_saldo_ce),
  sum(q_obor_d),
  sum(q_obor_c)
from #tmp_osv
group by s1_id


declare curs1 cursor for
  select stock_id from #stock_rep

declare @price money, @rur_id numeric, @const varchar(30), @unkd_sum money

select @rur_id = id
  from t_currencies
  where cur_code = 'RUR'
    and .ITEMS_EXISTS(t_currencies.id)

open curs1
fetch curs1 into @stock_id

while .CURSOR_STATE = 0
begin

/*
  select @const = (.GET_CONST('STOCK_RATE_TYPE'))

  if (isnull(@const, '-1') <> '-1') and (@const = 'PROM_TRAD')
    .GET_STOCK_RATE_MP_COUPON(@stock_id, @date_e, @rur_id, @price)
  else
*/

.GET_STOCK_RATE_MP(@stock_id, @date_e, @rur_id, @price)
.GET_STOCK_RATE_COUPON(@stock_id, @date_e, @rur_id, @nkd)

select @subc_str_bal=''
.SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y)
.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y)

select @unkd_sum = sum(ol.deb_or_cred * ol.sum_bal)
from t_operations o, t_oper_list ol
where ol.partner2plan_id = @plan2partner_id
  and ((ol.acc_id = @acc440_id) or (ol.acc_id = @acc444_id))
  and ol.head_id = o.id
  and ol.subc_eff_num like '%'+@subc_str_bal+'%'
--  and o.op_date >= @date_b
  and o.op_date < dateadd(dd, 1, @date_e)


  update #stock_rep set
    port_price = @price * stock_qty_e,
    unkd = @unkd_sum,
    nkd = @nkd * stock_qty_e
  where stock_id = @stock_id

  fetch curs1 into @stock_id
end

close curs1
deallocate curs1


delete #stock_rep
 where stock_s_b = 0
   and stock_s_e = 0
   and obor_d = 0
   and obor_c = 0

if (select count(*) from #stock_rep) = 0
  insert into #stock_rep(stock_s_b, stock_s_e) values(0,0)

declare @bal_stoim money, @ocen_stoim money, @nkd_sum money, @unkd money
select
  @bal_stoim = sum(isnull(#stock_rep.stock_s_e,0)),
  @ocen_stoim = sum(isnull(#stock_rep.port_price,0)),
  @nkd_sum = sum(isnull(#stock_rep.nkd,0)),
  @unkd = sum(isnull(#stock_rep.unkd,0))
from #stock_rep

declare @ocenka_bal int
select @ocenka_bal = convert(int, @ocenka)

update #stock_rep
set port_price = null
where port_price = 0

if @ocenka_bal = 1
begin
select
  @bal_stoim = sum(isnull(#stock_rep.stock_s_e,0)),
  @ocen_stoim = sum(isnull(#stock_rep.port_price, #stock_rep.stock_s_e)),
  @nkd_sum = sum(isnull(#stock_rep.nkd,0)),
  @unkd = sum(isnull(#stock_rep.unkd,0))
from #stock_rep

select
  td_stock_emis.stock_name,
  td_stock_emis.emiss_statereg_no,
  #stock_rep.stock_s_e,
  #stock_rep.stock_qty_e,
  isnull(#stock_rep.port_price, #stock_rep.stock_s_e),
  (@bal_stoim + @money_fund - @obl_comiss + @unkd) as bal_stoim,
  (@ocen_stoim + @money_fund- @obl_comiss + @nkd_sum) as ocen_stoim,
  (@money_fund - @depozit) as money_fund,
  isnull(#stock_rep.unkd, 0) as unkd,
  td_stock_emis.stock_nominal,
  @depozit as depozit,
  isnull(#stock_rep.nkd, 0) as nkd

 from #stock_rep, td_stock_emis
where #stock_rep.stock_id *= td_stock_emis.id
order by  td_stock_emis.stock_name
end
else
begin
select
  @bal_stoim = sum(isnull(#stock_rep.stock_s_e,0)),
  @ocen_stoim = sum(isnull(#stock_rep.port_price,0)),
  @nkd_sum = sum(isnull(#stock_rep.nkd,0)),
  @unkd = sum(isnull(#stock_rep.unkd,0))
from #stock_rep

select
  td_stock_emis.stock_name,
  td_stock_emis.emiss_statereg_no,
  #stock_rep.stock_s_e,
  #stock_rep.stock_qty_e,
  isnull(#stock_rep.port_price,0),
  (@bal_stoim + @money_fund - @obl_comiss + @unkd) as bal_stoim,
  (@ocen_stoim + @money_fund- @obl_comiss + @nkd_sum) as ocen_stoim,
  (@money_fund - @depozit) as money_fund,
  isnull(#stock_rep.unkd, 0) as unkd,
  td_stock_emis.stock_nominal,
  @depozit as depozit,
  isnull(#stock_rep.nkd, 0) as nkd

 from #stock_rep, td_stock_emis
where #stock_rep.stock_id *= td_stock_emis.id
order by  td_stock_emis.stock_name
end
end