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
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), @subc_str_bal2 varchar(255)
declare @acc_id numeric, @acc20_id numeric, @acc252_id numeric, @acc440_id numeric,
  @acc444_id numeric, @acc2013_id numeric, @acc2102_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, @acc_num_eff varchar(255)

create table #stock_rep
(
--  id numeric IDENTITY not null,
  acc_id numeric null,
  stock_id numeric null,
  op_date datetime 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
)

/*
create table #result
(
  stock_id numeric null,
  stock_s_e money null,
  stock_qty_e 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(@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(@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(@acc2013_id, 'BACK_ACC_DEPOZIT', @cur_plan, 1, 1, 'Депозитные счета в банках', @mess)
.ACCID_FROM_CONST2PLAN(@acc2102_id, 'BACK_ACC_COUPON', @cur_plan, 2, 1, 'Задолженность по купонам', @mess)

declare @money_fund money, @saldo_e money, @saldo_ras money, @obl_comiss money
 , @depozit money,  @saldo_debit 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)
.SALDO(@cur_partner, @cur_plan, @date_e, @acc2102_id, @subc_str_bal, N, D, @saldo_debit)
*/

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

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

select @acc_num_eff = acc_num_eff from t_accounts where id = @acc_id

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)

--.TOTALS_QUERY(@cur_partner, @cur_plan, @date_b, @date_e, @acc_id, @subc_str_bal, L, N, S, D, O)
.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,
--  op_date,
  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,
  op_date,
  (saldo_db - saldo_cb),
  (saldo_de - saldo_ce),
  obor_d,
  obor_c,
  (q_saldo_db - q_saldo_cb),
  (q_saldo_de - q_saldo_ce),
  q_obor_d,
  q_obor_c
from #tmp_osv
*/
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 @stock_sum money

declare curs1 cursor for
  select stock_id, (stock_s_e/stock_qty_e) from #stock_rep

declare @price money, @rur_id numeric, @const varchar(30), @unkd_sum money, @stock_s_e money,
  @stock_type numeric, @nominal money, @op_id numeric, @canc_date datetime, @dd1 int,
  @dd2 int

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

open curs1
fetch curs1 into @stock_id, @stock_s_e  -- @op_id,

while .CURSOR_STATE = 0
begin
---------------------новое изменение--------------1/02/2006
---(Номинал – Цена последнего приобретения без учета переоценки) / (Дата погашения – Дата последнего поступления на баланс) *
--*(Дата переоценки - Дата последнего поступления на баланс) + Цена последнего приобретения без учета переоценки

select @stock_type = tst.stock_type_id, @nominal = tse.stock_nominal, @canc_date = tse.stock_cancel_date
    from t_stock_types tst, td_stock_emis tse
    where tse.id = @stock_id
      and tst.id = tse.stock_type_id

--select @op_date = op_date from #stock_rep where id = @op_id

if @stock_type = 3
begin
-- Векселя: (Номинал – Балансовая стоимость) / (Дата погашения – Дата постановки на баланс) * (Дата переоценки - Дата постановки на баланс) + Балансовая стоимость.

  select @subc_str_bal2 = ''
  .SUBC_STR_ADD(@subc_str_bal2, 'INVESTORS', @dogovor_id, Y)
  if @section_id is not null
  begin
  .SUBC_STR_ADD(@subc_str_bal2, 'INVESTOR2PORTFOLIO', @section_id, Y)
  end
  .SUBC_STR_ADD(@subc_str_bal2, 'STOCK_EMIS', @stock_id, Y)

  --set rowcount 1
  select top 1 @op_date = o.op_date, @stock_sum = ol.sum_bal*ol.deb_or_cred
  from t_operations o, t_oper_list ol, t_accounts a
  where ol.partner2plan_id = @plan2partner_id
    and ol.acc_id = a.id
    and a.acc_num_eff like @acc_num_eff + '.%'
    and ol.head_id = o.id
    and ol.subc_eff_num like '%' + @subc_str_bal2 + '%'
--    and o.op_date >= @date_b
    and o.op_date <  dateadd(dd, 1, @date_e)
    and ol.deb_or_cred = 1
   order by o.op_date desc


select @dd1 = datediff(dd, @op_date, @canc_date), @dd2 = datediff(dd, @op_date, @date_e)
select @price = (((@nominal - @stock_sum ) / @dd1) * @dd2) + @stock_sum

--if @stock_id  = 21535000000798673
--select  @op_date, @canc_date,@op_date, @date_e, @dd1,@dd2, @nominal , @stock_sum , @price


-- select @nominal as nom, @stock_s_e as stock_s_e, @dd1 as dd1, @dd2 as dd2, @op_date as op_date,
--  @canc_date as canc_date, @date_e as date_e

end
else
-- Для договоров 22-О3У012 и "Доверительного управления средствами пенс. накоплений Благосостояние"
-- берем рыночную 2
if @dogovor_id = 21535000000999663 or @dogovor_id=21535000000625521
begin
  .GET_STOCK_RATE_MP2(@stock_id, @date_e, @rur_id, @price)
    if @stock_id=21535000000734683
      begin
      select @price=round(@price,2)
      end

end
else
begin
  .GET_STOCK_RATE_MP(@stock_id, @date_e, @rur_id, @price)
end

.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 = isnull(@price, 0) * stock_qty_e,
    unkd = @unkd_sum,
    nkd = @nkd * stock_qty_e
  where stock_id = @stock_id
--    and id = @op_id

  fetch curs1 into @stock_id, @stock_s_e -- @op_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) select 0, 0

/*
insert into #result(
  stock_id,
  stock_s_e,
  stock_qty_e,
  port_price,
  unkd,
  nkd
)
select
  #stock_rep.stock_id,
  sum(#stock_rep.stock_s_e),
  sum(#stock_rep.stock_qty_e),
  sum(#stock_rep.port_price),
  sum(#stock_rep.unkd),
  sum(#stock_rep.nkd)
from #stock_rep
group by #stock_rep.stock_id
*/


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


select
  td_stock_emis.stock_name + ' ' + td_stock_emis.emiss_statereg_no,
  td_stock_emis.emiss_statereg_no,
  #stock_rep.stock_s_e,
  #stock_rep.stock_qty_e,
  #stock_rep.port_price,
  isnull(#stock_rep.unkd, 0) as unkd,
  td_stock_emis.stock_nominal,
  isnull(#stock_rep.nkd, 0) as nkd

/*
  (@bal_stoim + @money_fund - @obl_comiss + @unkd + isnull(@saldo_debit, 0)) as bal_stoim,
  (@ocen_stoim + @money_fund- @obl_comiss + @nkd_sum + isnull(@saldo_debit, 0)) as ocen_stoim,
  (@money_fund - @depozit) as money_fund,
  @depozit as depozit,
*/

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

end