create procedure dbo.%PROC%
--  @data_type varchar(5) = null,
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @broker_id_s varchar(30) = null,
  @broker_name varchar(255) = null,
  @broker_code varchar(255) = null,
  @investor_id_s varchar(30) = null,
  @investor_name varchar(255) = null,
  @ocenka varchar(5) = null,
  @date_interval varchar(5) = null,
  @type_data varchar(5) = null

as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric
begin
set nocount on
-- execute dbo.ap_21535000002805244;1 @data_type=null, @date_b_d='13/07/05', @date_e_d='13/07/05', @broker_id_s='21535000000808453', @broker_name='Aladdin Systems', @broker_code='DEPO', @investor_id_s='21535000000809181', @investor_name='Договор с Альфаф


declare @broker_id numeric
declare @date_b datetime
declare @date_e datetime
declare @bank_acc_id numeric
declare @dogovor_id numeric
declare @cur_plan numeric
declare @cur_partner numeric
declare @subc_str_bal varchar(255)
declare @mess varchar(255)
declare @ocenka_bal int

select @ocenka_bal = convert(int, @ocenka)
select @broker_id = convert(numeric, @broker_id_s)
--select @date_b = convert(datetime, @date_b_d, 3)
--select @date_e = convert(datetime, @date_e_d, 3)
select @dogovor_id = convert(numeric, @investor_id_s )
-------------------------------------------------------
declare @d_type int
declare @interval int
select @d_type = convert(int, @type_data)
if @d_type = 0
  begin
  select @date_b = convert(datetime, @date_b_d, 3)
  select @interval = convert(int, @date_interval)
    if @interval = 0 select @date_e = dateadd(yy,1,@date_b)
     else
    if @interval = 1 select @date_e = dateadd(mm,3,@date_b)
     else
    if @interval = 2 select @date_e = dateadd(mm,1,@date_b)

select @date_b = .DATE_NORM(@date_b,B)
select @date_e = .DATE_NORM(@date_e,e)

  end
else
if @d_type = 1
  begin
  select @date_e = convert(datetime, @date_e_d, 3)
  select @date_e = .DATE_NORM(@date_e,e)
  end
else
if @d_type = 2
  begin
  select @date_b = convert(datetime, @date_b_d, 3)
  select @date_e = convert(datetime, @date_e_d, 3)
  select @date_b = .DATE_NORM(@date_b,B)
  select @date_e = .DATE_NORM(@date_e,e)
  end

-------------------------------------------------------
---------------деп.операции и платежи
create table #pay
(id numeric null,
 op_date datetime null,
 doc_sum money null,
 p_name varchar(255) null,
 p_code varchar(255) null,
 type_code varchar(255) null)

insert into #pay(id ,  op_date , doc_sum , p_code, type_code)
select d.id, d.in_date,
(case when t.type_code in( 'D_STOCK_OUT_OTHER_DEPO','D_STOCK_OUT') then (-1)*s.stock_price
     when t.type_code in( 'D_STOCK_IN_OTHER_DEPO','D_STOCK_IN') then s.stock_price end),
i.item_name, t.type_code
from td_depo_docs d, tb_baysale_docitems b, t_items i, t_types t
    , td_stock_moves s
where d.foundation_id=b.id
  and d.id <> b.id
  and i.id = d.id
  and b.client_contract_id = @dogovor_id
  and s.id = d.id
  and t.id = i.type_id
--  and d.in_date >= @date_b
  and d.in_date <= @date_e

declare @client_id numeric
select @client_id = (select client_id from tb_contracts where id = @dogovor_id )

insert into #pay
select p.id, d.in_date,
(case when pu.code = 82 then (-1)* p.doc_sum
     when pu.code in (37,64) then  p.doc_sum end),
pu.code, pu.name, t.type_code
from t_paydocs p, t_purposes pu, td_depo_docs d, t_items i, t_types t
where p.purpose_id = pu.id
  and pu.code in (37,64,82)  --10,
  and (p.from_partner_id = @client_id or p.to_partner_id = @client_id)
  and d.id = p.id
--  and d.in_date >= @date_b
  and d.in_date <= @date_e
  and d.id = i.id
  and i.type_id = t.id
----------------------------------------------------------------------
if @d_type = 1
begin
select @date_b = (select min(op_date) from #pay)
select @date_b = .DATE_NORM(@date_b,B)
end
----------------------------------------------------------------------
select @cur_partner = partner_id,
       @cur_plan = back_money_id
  from tb_broker_change
  where id = @broker_id

create table #rep
(stock_id numeric null,
 stock_qty money null,
 stock_price_b money null,
 stock_price_e money null,
 port_price_b money null,
 port_price_e money null,
 stock_type numeric null,
 stock_part money null,
 stock_name varchar(255) null
)

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)
.SUBC_STR_ADD(@subc_str_bal, 'STOCK_EMIS', null, Y)

declare @acc10_id numeric
------------------------количество-------------------
.ACCID_FROM_CONST2PLAN(@acc10_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'Наличие ЦБ на балансе', @mess)

declare @acc_num_eff_10 varchar(255)
select @acc_num_eff_10 = acc_num_eff from t_accounts where id = @acc10_id

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

----------------------------------------те, которые в наличии счет 10---------------------------
insert into #rep (stock_id, stock_qty)
  select s1_id, (q_saldo_de - q_saldo_ce) from #tmp_osv
    where ((q_saldo_de <> 0) or (q_saldo_ce <> 0))
      and (acc_num_eff LIKE @acc_num_eff_10 + '.%')

/*insert into #result (stock_id, stock_qty)
  select s1_id, (-1)*(q_saldo_de - q_saldo_ce) from #tmp_osv
    where ((q_saldo_de <> 0) or (q_saldo_ce <> 0))
      and ((acc_num_eff LIKE @acc_num_eff_11 + '.%') or (acc_num_eff LIKE @acc_num_eff_13 +'.%'))
---------------------------------------те, кот. к получению, счет 12,14----------------------------
insert into #result (stock_id, stock_qty)
  select s1_id, (q_saldo_de - q_saldo_ce) from #tmp_osv
    where ((q_saldo_de <> 0) or (q_saldo_ce <> 0))
      and ((acc_num_eff LIKE @acc_num_eff_12 + '.%') or (acc_num_eff LIKE @acc_num_eff_14 +'.%'))
*/

------------------котировка------------------------------
declare @stock_id numeric, @price_b numeric(18,8),  @price_e numeric(18,8)
declare curs1 cursor for
select stock_id from #rep

open curs1
fetch curs1 into @stock_id
while .CURSOR_STATE = 0
begin
------------------------------------------------------------------------------------
if @d_type in (0 ,2)
begin
.GET_STOCK_RATE_MP(@stock_id, @date_b, null, @price_b)
end

.GET_STOCK_RATE_MP(@stock_id, @date_e, null, @price_e)
--------------------------обновляем -------------------------------
 update #rep set
    stock_price_b = isnull(@price_b, 0),
    port_price_b = isnull(@price_b, 0) * stock_qty,
    stock_price_e = isnull(@price_e, 0),
    port_price_e = isnull(@price_e, 0) * stock_qty
  where #rep.stock_id = @stock_id

select @price_b = 0,  @price_e = 0
  fetch curs1 into @stock_id
end
close curs1
deallocate curs1
--------------------------
---------Находим тип. Базовый тип ЦБ [простой справочник 3060]
update #rep set
stock_type = (select  t.stock_type_id
from td_stock_emis s,t_stock_types t,t_simples
where s.id = stock_id
  and s.stock_type_id = t.id
  and t_simples.id = t.stock_type_id
  and t_simples.type_id = 3060)

update #rep
set stock_name = td_stock_emis.stock_name from td_stock_emis where td_stock_emis.id = #rep.stock_id

declare @port_price_b money --нач.стоимость портфеля
declare @vv_money money -- ввод/вывод активов

if @d_type in (0,2)
begin
select @port_price_b = sum(port_price_b) from #rep
select @vv_money = sum(doc_sum) from #pay where op_date >= @date_b and op_date<=@date_e
end
else
if @d_type = 1
begin
declare @date_m_b datetime
declare @date_m_e datetime
declare @date_m datetime
select @date_m = dateadd(dd,30,@date_b)
select @date_m_b = .DATE_NORM(@date_m,B)
select @date_m_e = .DATE_NORM(@date_m,E)
select @port_price_b = sum(doc_sum) from #pay where op_date >= @date_b and op_date<=@date_m_e
select @vv_money = sum(doc_sum) from #pay where op_date >= @date_m_b and op_date<=@date_e
end

declare @port_price money --стоимость портфеля
select @port_price = sum(port_price_e) from #rep

declare @edit_port money --изменение портфеля
select @edit_port = isnull(@port_price,0) + isnull(@vv_money ,0)

declare @dd int
select @dd = datediff(dd, @date_b, @date_e)

declare @yield money --доходность годовых
if isnull(@vv_money,0)<>0
select @yield = isnull(@edit_port,0)/@vv_money *365/@dd

declare @akcii money
declare @obl money
declare @other money
declare @rur money
if isnull(@port_price,0)<>0
select @rur = isnull(@vv_money,0)/@port_price

select @akcii = (sum(port_price_e)/@port_price)
  from #rep
 where stock_type = 1

select @obl = (sum(port_price_e)/@port_price)
  from #rep
 where stock_type = 2

select @other = (sum(port_price_e)/@port_price)
  from #rep
 where stock_type not in (1, 2)


declare @index_rts money
declare @index_mmvb money

/*select @index_rts = (select t_indices_values.index_value
from t_indices, t_indices_values
where t_indices.index_code = 'RTS'
  and t_indices_values.index_id = t_indices.id
  and t_indices_values.rate_date = @date_e)

select @index_mmvb = (select t_indices_values.index_value
from t_indices, t_indices_values
where t_indices.index_code = 'MICEX'
  and t_indices_values.index_id = t_indices.id
  and t_indices_values.rate_date = @date_e)
*/


select @date_b, @port_price_b , @vv_money , @port_price,@edit_port, @yield ,@rur, @akcii , @obl ,
@other, @date_e,isnull(@index_rts,0), isnull(@index_mmvb,0), stock_name, port_price_e
from #rep

/*declare @nekot money
if @ocenka = 1
begin
select @nekot =
end
*/
--select * from #pay




--select * from #rep
END