# Date: 01/08/06
# Version: 105

$DOC(VALUATION_EFF_MANAGE_REP)
 name=Отчет "Оценка эффективного управления" для НМ-траст
 class=1
 product=ALD_SECUR
 name_formula=null
 remark_formula=null

 $STATE(НАЧАЛЬНЫЙ)
  name=Начальный
  class=1
 $ENDSTATE

 $FORM(FILTER)
  name=Фильтр
  class=6
  filter=null
  target_state=
  uo=uo_filter
  dw=d_ocenka_eff_filter
  procedure=ap_21535000002383908
  patterns=null
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=2437
  height=908
  proc_text=$TEXT(11424586)
create procedure dbo.%PROC%
as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric
begin

set nocount on

declare @operday datetime
select @operday = .OPERDAY
declare @broker_id numeric
declare @broker_code varchar(255)
declare @broker_name varchar(255)

select @broker_id = .ORG_FROM_PARTNER((.ORGANIZATION))

select
  @broker_name = tb_broker_change.broker_name,
  @broker_code = tb_broker_change.broker_code
  from tb_broker_change
 where tb_broker_change.id = @broker_id

select
--  convert( varchar(5),null) as data_type,
  convert(datetime,null) as date_b_d,
  convert(datetime,null) as date_e_d,
  convert(varchar(30), @broker_id) as broker_id_s,
  @broker_name as broker_name,
  @broker_code as broker_code,
  convert(varchar(30), null) as investor_id_s,
  convert(varchar(255), null) as investor_name,
  convert(varchar(5), 1) as ocenka,
  convert(varchar(5), null) as date_interval,
  convert(varchar(5), null) as type_data,
  convert(varchar(5), null) as nach_ocenka
end
  $ENDTEXT(11424586)
 $ENDFORM

 $FORM(OCENKA_EFF_GRAPH1)
  name=График 1 по инструментам в портфеле
  class=4
  filter=null
  target_state=
  uo=uo_list_rep
  dw=null
  procedure=ap_ocenka_eff_graph1
  patterns=null
  user_define=1
  isvisible=1
  istab=0
  posx=null
  posy=null
  width=null
  height=null
  proc_text=$TEXT(11424586)
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='Договор с Альфаф
-- execute dbo.ap_21535000002384149;1 @date_b_d='01/01/06', @date_e_d='06/04/06', @broker_id_s='21535000000792670', @broker_name='ОАО "НМ Траст"', @broker_code='НМ-Траст', @investor_id_s='21535000000905648',
-- @investor_name='Договор ДУ 16', @ocenka='1', @date_interval='1', @type_data='0'

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)

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

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, 'BANK_ACCOUNT', NULL, Y)

INSERT into #pay (id, op_date,doc_sum)
select ol.id, o.op_date, deb_or_cred*ol.sum_bal
from t_operations o,t_oper_list ol, t_accounts a,
     t_purposes pu, t_paydocs p
where o.id = ol.head_id
  and a.acc_num_eff like '2.0.0'
  and ol.acc_id = a.id
--  and ol.op_date >= @date_b
  and ol.op_date <= @date_e  --  and ol.op_date<= dateadd(dd,-1,@date_e)
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and pu.code in (39,84,101,102,103,104,96,97,124,128,132,135,137)
  and o.item_id = p.id
  and p.purpose_id = pu.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

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)

------------------------количество-------------------
create table #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_b money null,
  nkd_e 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,
 stock_type_name varhcar(255) null
)

declare @acc1_id numeric
declare @acc10_id numeric
declare @acc11_id numeric
declare @acc12_id numeric
declare @acc13_id numeric
declare @acc14_id numeric

.ACCID_FROM_CONST2PLAN(@acc1_id,  'BACK_ACC_SECUR_MP_ALL', @cur_plan, 1, 1, 'ЦБ и вложения', @mess)
.ACCID_FROM_CONST2PLAN(@acc10_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'Наличие ЦБ на балансе', @mess)
.ACCID_FROM_CONST2PLAN(@acc11_id, 'BACK_ACC_SECUR_VBR', @cur_plan, 3, 1, 'Текущие расчеты по ЦБ при отчуждении ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc12_id, 'BACK_ACC_SECUR_VBR', @cur_plan, 2, 1, 'Текущие расчеты по ЦБ при приобретении ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc13_id, 'BACK_ACC_STOCK_MOVE_INSTR_ALL', @cur_plan, 3, 1, 'Счёт по ожидаемому расходу ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc14_id, 'BACK_ACC_STOCK_MOVE_INSTR_ALL', @cur_plan, 2, 1, 'Счёт по ожидаемому приходу ЦБ', @mess)

declare @acc_num_eff_1 varchar(255), @acc_num_eff_10 varchar(255)  , @acc_num_eff_11 varchar(255),
  @acc_num_eff_12 varchar(255), @acc_num_eff_13 varchar(255), @acc_num_eff_14 varchar(255),
  @acc_num_eff_445 varchar(255)

select @acc_num_eff_1 =  acc_num_eff from t_accounts where id = @acc1_id
select @acc_num_eff_10 = acc_num_eff from t_accounts where id = @acc10_id
select @acc_num_eff_11 = acc_num_eff from t_accounts where id = @acc11_id
select @acc_num_eff_12 = acc_num_eff from t_accounts where id = @acc12_id
select @acc_num_eff_13 = acc_num_eff from t_accounts where id = @acc13_id
select @acc_num_eff_14 = acc_num_eff from t_accounts where id = @acc14_id

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

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

insert into #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


---------Находим тип. Базовый тип ЦБ [простой справочник 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)
------------------котировка------------------------------
declare @rur_id  numeric
select @rur_id = id
  from t_currencies
  where cur_code = 'RUR'
    and .ITEMS_EXISTS(t_currencies.id)

declare @nkd_b1 money,@nkd_b2 money, @nkd_e1 money, @nkd_e2 money

declare @stock_id numeric, @price_b numeric(18,8),  @price_e numeric(18,8),
@stock_qty_b money, @stock_qty_e money
declare curs1 cursor for
select stock_id, stock_qty_b, stock_qty_e   from #rep

open curs1
fetch curs1 into @stock_id, @stock_qty_b, @stock_qty_e
while .CURSOR_STATE = 0
begin
------------------------------------------------------------------------------------
if @d_type in (0 ,2)
begin
.GET_STOCK_RATE_MP(@stock_id, @date_b, @rur_id, @price_b)
.BACK_CALC_COUPON(@stock_id,@stock_qty_b,@date_b,0,@nkd_b2,@rur_id)--другой шаблон в случае,если котировка в этот день отсутствует
.GET_STOCK_RATE_COUPON(@stock_id, @date_b, @rur_id, @nkd_b1)
end
.GET_STOCK_RATE_MP(@stock_id, @date_e, @rur_id, @price_e)
.BACK_CALC_COUPON(@stock_id,@stock_qty_e,@date_e,0,@nkd_e2,@rur_id)--другой шаблон в случае,если котировка в этот день отсутствует
.GET_STOCK_RATE_COUPON(@stock_id, @date_e, @rur_id, @nkd_e1)
--------------------------обновляем -------------------------------

update #rep set
    stock_price_b = isnull(@price_b, 0),
    port_price_b = isnull(@price_b, 0)* @stock_qty_b,--+ isnull(@nkd_b,0)/*) * @stock_qty_b*/,
    stock_price_e = isnull(@price_e, 0),
    port_price_e = isnull(@price_e, 0)* @stock_qty_e--+ isnull(@nkd_e,0)/*) * @stock_qty_e*/
  where #rep.stock_id = @stock_id

--для облтгаций
 update #rep set
  port_price_b = port_price_b + isnull(@nkd_b2,0),
  port_price_e = port_price_e + isnull(@nkd_e2,0)
   -- port_price_b = port_price_b + isnull(@nkd_b1*@stock_qty_b,isnull(@nkd_b2,0)),
   -- port_price_e = port_price_e + isnull(@nkd_e1*@stock_qty_e,isnull(@nkd_e2,0))
  where #rep.stock_id = @stock_id
  and stock_type = 2

select @price_b = 0,  @price_e = 0
  fetch curs1 into @stock_id, @stock_qty_b, @stock_qty_e
end
close curs1
deallocate curs1
--------------------------

---------------ДС----------------------------
declare @money_fund money
declare @acc20_id numeric

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)

.SALDO(@cur_partner, @cur_plan, @date_e, @acc20_id, @subc_str_bal, N, D, @money_fund)

insert #rep (stock_type_name, port_price_e)
select 'Рубли', @money_fund

update #rep
set stock_type_name = (case when stock_type = 1 then 'Акции'
                            when stock_type = 2 then 'Облигации' end)

declare @mon_b numeric
.SALDO(@cur_partner, @cur_plan, @date_b, @acc20_id, @subc_str_bal, N, D, @mon_b)
---------------------------------------------------------
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(isnull(doc_sum,0)) 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(isnull(doc_sum,0)) from #pay where op_date >= @date_m_b and op_date<=@date_e
end

declare @port_price money --стоимость портфеля
-------------------------галочка оценки------------------
declare @bal_stoim money
declare @nekot money
if @ocenka_bal = 1
begin
select  @port_price = sum(isnull(#rep.port_price_e, #rep.stock_s_e)) + isnull(@money_fund,0)
from #rep

if isnull(@port_price ,0)<>0
select  @nekot  = sum(isnull(stock_s_e,0))/@port_price
from #rep
where isnull(port_price_e,0) = 0

end
else
begin
select @port_price = sum(isnull(port_price_e,0))+ isnull(@money_fund,0) from #rep
end
-------------------------------------------------
declare @edit_port money --изменение портфеля
select @edit_port = isnull(@port_price,0) - isnull(@vv_money ,0) - isnull(@port_price_b,0)

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


/*Давайте упростим расчет доходности.
Доходность годовых=(стоимость портфеля/(нач.стоим.портфеля+вводы/выводы активов)-1)*356/(отчетная дата-начальная дата) */

declare @yield money --доходность годовых
if isnull(@vv_money,0)<>0
select @yield = (@port_price/(@port_price_b+@vv_money)-1)*356/@dd   --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(@money_fund,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)

update #rep
set port_price_e = null
where isnull(port_price_e,0) = 0

declare @client varchar(255)
select @client=t_partners.partner_name
from t_partners,tb_contracts
where tb_contracts.id=@dogovor_id
and tb_contracts.client_id=t_partners.id

update #rep
set stock_s_e = isnull(stock_s_e,0),
port_price_e = isnull(port_price_e,0)

if @ocenka_bal = 1
begin
select stock_name,
       isnull(port_price_e, stock_s_e),
       stock_type_name,
       stock_qty_e
from #rep
end
else
begin
select stock_name,
       port_price_e,
       stock_type_name,
       stock_qty_e
from #rep
end
END
  $ENDTEXT(11424586)
 $ENDFORM

 $FORM(TRUST_MANAGE_PORT_REP)
  name=Отчет о доверительном управлении портфелем ЦБ
  class=4
  filter=FILTER
  target_state=
  uo=uo_list_rep
  dw=d_ocenka_eff_list
  procedure=ap_21535000002383909
  patterns=null
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11424587)
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,
  @nach_ocenka 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='Договор с Альфаф
-- execute dbo.ap_21535000002384149;1 @date_b_d='01/01/06', @date_e_d='06/04/06', @broker_id_s='21535000000792670', @broker_name='ОАО "НМ Траст"', @broker_code='НМ-Траст', @investor_id_s='21535000000905648',
-- @investor_name='Договор ДУ 16', @ocenka='1', @date_interval='1', @type_data='0'

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
declare @nach_oc int

select @nach_oc = convert(int, @nach_ocenka)  --галочка оценка начальной стоимости
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_b = c.dogexec_begin_date
                        from td_depo_docs d, tb_contracts c
                       where d.id = c.id
                         and c.id = @dogovor_id

  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

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

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, 'BANK_ACCOUNT', NULL, Y)

/*INSERT into #pay (id, op_date,doc_sum)
select ol.id, o.op_date, deb_or_cred*ol.sum_bal
from t_operations o,t_oper_list ol, t_accounts a,
     t_purposes pu, t_paydocs p
where o.id = ol.head_id
  and a.acc_num_eff like '2.0.0'
  and ol.acc_id = a.id
--  and ol.op_date >= @date_b
  and ol.op_date <= @date_e  --  and ol.op_date<= dateadd(dd,-1,@date_e)
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and pu.code in (39,84,101,102,103,104,96,97,124,128,132,135,137)
  and o.item_id = p.id
  and p.purpose_id = pu.id*/

INSERT into #pay (id, op_date,doc_sum)
select ol.id, o.op_date, deb_or_cred*ol.sum_bal
from t_operations o,t_oper_list ol, t_accounts a,
     t_purposes pu, t_paydocs p
where o.id = ol.head_id
  and a.acc_num_eff like '2.0.0'
  and ol.acc_id = a.id
--  and ol.op_date >= @date_b
  and ol.op_date <= @date_e
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and pu.code in (37,64,39)
  and o.item_id = p.id
  and p.purpose_id = pu.id
--
insert into #pay (id, op_date,doc_sum)
select ol.id, o.op_date, deb_or_cred*ol.sum_bal
from t_operations o,t_oper_list ol, t_accounts a, td_stock_moves m, t_purposes pu
where o.id = ol.head_id
  and a.acc_num_eff like '1.0.0'
  and ol.acc_id = a.id
--  and ol.op_date >=@date_b
  and ol.op_date<= @date_e
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and o.item_id = m.id
  and m.is_dogovor_id = pu.id
  and pu.code in (29,93,32)

/* отбирается все, что не платежи, не перевод внутри инвестора, вылавливает ручные проводки
INSERT into #pay (id, op_date,doc_sum)
select ol.id, o.op_date, deb_or_cred*ol.sum_bal
from t_operations o,t_oper_list ol, t_accounts a, t_items i, t_types t
where o.id = ol.head_id
  and a.acc_num_eff like '2.0.0'
  and ol.acc_id = a.id
  and ol.op_date >=@date_b
  and ol.op_date<= dateadd(dd,-1,@date_e)
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and o.item_id = i.id
  and i.type_id = t.id
  and t.type_code not in ('REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT', 'MONEY_MOVE_COMMISSION_2')
*/
----------------------------------------------------------------------
if @d_type = 1
begin
select @date_b = (select min(op_date) from #pay)
select @date_b = .DATE_NORM(@date_b,B)
end
----------------------------------------------------------------------
/*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
)*/

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

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)

------------------------количество-------------------
create table #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_b money null,
  nkd_e 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,
 stock_type_name varchar(255) null,
 st_type_money money null  --криво, но билдер-тупой, он при построении графика типа pie считает что каждого экземпляра по 1, остальные он игнорит
)

declare @acc1_id numeric
declare @acc10_id numeric
declare @acc11_id numeric
declare @acc12_id numeric
declare @acc13_id numeric
declare @acc14_id numeric

.ACCID_FROM_CONST2PLAN(@acc1_id,  'BACK_ACC_SECUR_MP_ALL', @cur_plan, 1, 1, 'ЦБ и вложения', @mess)
.ACCID_FROM_CONST2PLAN(@acc10_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'Наличие ЦБ на балансе', @mess)
.ACCID_FROM_CONST2PLAN(@acc11_id, 'BACK_ACC_SECUR_VBR', @cur_plan, 3, 1, 'Текущие расчеты по ЦБ при отчуждении ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc12_id, 'BACK_ACC_SECUR_VBR', @cur_plan, 2, 1, 'Текущие расчеты по ЦБ при приобретении ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc13_id, 'BACK_ACC_STOCK_MOVE_INSTR_ALL', @cur_plan, 3, 1, 'Счёт по ожидаемому расходу ЦБ', @mess)
.ACCID_FROM_CONST2PLAN(@acc14_id, 'BACK_ACC_STOCK_MOVE_INSTR_ALL', @cur_plan, 2, 1, 'Счёт по ожидаемому приходу ЦБ', @mess)

declare @acc_num_eff_1 varchar(255), @acc_num_eff_10 varchar(255)  , @acc_num_eff_11 varchar(255),
  @acc_num_eff_12 varchar(255), @acc_num_eff_13 varchar(255), @acc_num_eff_14 varchar(255),
  @acc_num_eff_445 varchar(255)

select @acc_num_eff_1 =  acc_num_eff from t_accounts where id = @acc1_id
select @acc_num_eff_10 = acc_num_eff from t_accounts where id = @acc10_id
select @acc_num_eff_11 = acc_num_eff from t_accounts where id = @acc11_id
select @acc_num_eff_12 = acc_num_eff from t_accounts where id = @acc12_id
select @acc_num_eff_13 = acc_num_eff from t_accounts where id = @acc13_id
select @acc_num_eff_14 = acc_num_eff from t_accounts where id = @acc14_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 + '.%')*/
----------------------------------------те, которые к списанию 11,13---------------------------
/*insert into #rep (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 #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_12 + '.%') or (acc_num_eff LIKE @acc_num_eff_14 +'.%'))
*/

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

insert into #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


---------Находим тип. Базовый тип ЦБ [простой справочник 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)
------------------котировка------------------------------
declare @rur_id  numeric
select @rur_id = id
  from t_currencies
  where cur_code = 'RUR'
    and .ITEMS_EXISTS(t_currencies.id)

declare @nkd_b1 money,@nkd_b2 money, @nkd_e1 money, @nkd_e2 money
declare @date_end datetime -- =(дата конец-1) --котировка будет рассчитываться именно на эту дату, т.к. так хочет клиент
select  @date_end = dateadd(dd,-1, @date_e)

declare @stock_id numeric, @price_b numeric(18,8),  @price_e numeric(18,8),
@stock_qty_b money, @stock_qty_e money
declare curs1 cursor for
select stock_id, stock_qty_b, stock_qty_e   from #rep

open curs1
fetch curs1 into @stock_id, @stock_qty_b, @stock_qty_e
while .CURSOR_STATE = 0
begin
------------------------------------------------------------------------------------
if @d_type in (0 ,2)
begin
.GET_STOCK_RATE_MP(@stock_id, @date_b, @rur_id, @price_b)
.BACK_CALC_COUPON(@stock_id,@stock_qty_b,@date_b,0,@nkd_b2,@rur_id)--другой шаблон в случае,если котировка в этот день отсутствует
.GET_STOCK_RATE_COUPON(@stock_id, @date_b, @rur_id, @nkd_b1)
end
.GET_STOCK_RATE_MP(@stock_id, @date_end, @rur_id, @price_e)
.BACK_CALC_COUPON(@stock_id,@stock_qty_e,@date_end,0,@nkd_e2,@rur_id)--другой шаблон в случае,если котировка в этот день отсутствует
.GET_STOCK_RATE_COUPON(@stock_id, @date_end, @rur_id, @nkd_e1)
--------------------------обновляем -------------------------------

update #rep set
    stock_price_b = isnull(@price_b, 0),
    port_price_b = isnull(@price_b, 0)* @stock_qty_b,--+ isnull(@nkd_b,0)/*) * @stock_qty_b*/,
    stock_price_e = isnull(@price_e, 0),
    port_price_e = isnull(@price_e, 0)* @stock_qty_e--+ isnull(@nkd_e,0)/*) * @stock_qty_e*/
  where #rep.stock_id = @stock_id

--для облтгаций
 update #rep set
  port_price_b = port_price_b + isnull(@nkd_b2,0),
  port_price_e = port_price_e + isnull(@nkd_e2,0)
   -- port_price_b = port_price_b + isnull(@nkd_b1*@stock_qty_b,isnull(@nkd_b2,0)),
   -- port_price_e = port_price_e + isnull(@nkd_e1*@stock_qty_e,isnull(@nkd_e2,0))
  where #rep.stock_id = @stock_id
  and stock_type = 2

select @price_b = 0,  @price_e = 0
  fetch curs1 into @stock_id, @stock_qty_b, @stock_qty_e
end
close curs1
deallocate curs1
--------------------------

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

--select stock_name,stock_s_e, port_price_b,  port_price_e, stock_qty_b, stock_qty_e,stock_price_b, stock_price_e from #rep
--order by stock_name  --test
---------------ДС----------------------------
declare @money_fund money
declare @acc20_id numeric

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)

.SALDO(@cur_partner, @cur_plan, @date_e, @acc20_id, @subc_str_bal, N, D, @money_fund)

update #rep
set stock_type_name = (case when stock_type = 1 then 'Акции'
                            when stock_type = 2 then 'Облигации' end)

insert #rep (stock_type_name, stock_name, port_price_e, stock_type)
select 'Рубли', 'Рубли', @money_fund, 100 --здесь 100 - означает деньги

declare @mon_b numeric
.SALDO(@cur_partner, @cur_plan, @date_b, @acc20_id, @subc_str_bal, N, D, @mon_b)
---------------------------------------------------------
declare @port_price_b money --нач.стоимость портфеля
declare @vv_money money -- ввод/вывод активов

if @d_type in (0,2)
begin

if @nach_oc = 1
select @port_price_b = sum(isnull(#rep.stock_s_e,0)) from #rep
else
select @port_price_b = sum(isnull(port_price_b,0)) from #rep

select @vv_money = sum(isnull(doc_sum,0)) 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(isnull(doc_sum,0)) from #pay where op_date >= @date_m_b and op_date<=@date_e
end

--select @port_price_b--test
--select sum(isnull(#rep.stock_s_e,0)), sum(isnull(port_price_b,0)) from #rep--test

declare @port_price money --стоимость портфеля
-------------------------галочка оценки------------------
declare @bal_stoim money
declare @nekot money

update #rep
set port_price_e = null
where port_price_e = 0

if @ocenka_bal = 1
begin
select  @port_price = sum(isnull(#rep.port_price_e, #rep.stock_s_e))-- + isnull(@money_fund,0)
from #rep

if isnull(@port_price ,0)<>0
select  @nekot  = sum(isnull(stock_s_e,0))/@port_price
from #rep
where isnull(port_price_e,0) = 0

end
else
begin
select @port_price = sum(isnull(port_price_e,0))    --+ isnull(@money_fund,0)
from #rep
end

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

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


/*Давайте упростим расчет доходности.
Доходность годовых=(стоимость портфеля/(нач.стоим.портфеля+вводы/выводы активов)-1)*356/(отчетная дата-начальная дата) */

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

declare @akcii money --decimal(18,8)
declare @obl money --decimal(18,8)
declare @other money --decimal(18,8)
declare @rur money --decimal(18,8)

if isnull(@port_price,0)<>0
select @rur = isnull(@money_fund,0)--/@port_price

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

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

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

---------------для тупого билдера для построения графика типа pie-----
update #rep
set st_type_money = (case when stock_type = 1 then @akcii
                          when stock_type = 2 then @obl
                          when stock_type = 100 then @rur
                          else @other end)
----------------------------------------------------------------------
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)
*/

update #rep
set port_price_e = null
where isnull(port_price_e,0) = 0

declare @client varchar(255)
select @client=t_partners.partner_name
from t_partners,tb_contracts
where tb_contracts.id=@dogovor_id
and tb_contracts.client_id=t_partners.id

/*SELECT  @port_price as rinochnaya_stoim,
          stock_type_name, port_price_e, stock_name from #rep order by stock_type_name--test

select (sum(port_price_e)/@port_price)*100 as stock_type from #rep group by stock_type_name
select (sum(port_price_e)/@port_price)*100 as stock_name from #rep group by stock_name
*/

if @ocenka_bal = 1
begin
select (select 'Договор доверительного управления № '+d.out_no + 'от '+ convert(varchar(8), c.dogexec_begin_date, 3)
                        from td_depo_docs d, tb_contracts c
                       where d.id = c.id
                         and c.id = @dogovor_id)   as investor_name,
       @date_b as date_b,
       @port_price_b as port_price_b,
       @vv_money as vv_money,
       @port_price as port_price,
       @edit_port as edit_port,
       @yield as yield,
       @rur as rur,
       @akcii as akcii,
       @obl as obl,
       @other as other,
       @nekot as nekot,
       @date_e as date_e,
 isnull(@index_rts,0), isnull(@index_mmvb,0),
 stock_name,
      isnull(port_price_e, stock_s_e) as port_price_e,
      @client as client,
      stock_type_name, st_type_money
from #rep
end
else
begin
select (select 'Договор доверительного управления № '+d.out_no + 'от '+ convert(varchar(8), c.dogexec_begin_date, 3)
                        from td_depo_docs d, tb_contracts c
                       where d.id = c.id
                         and c.id = @dogovor_id)   as investor_name,
       @date_b as date_b,
       @port_price_b as port_price_b,
       @vv_money as vv_money,
       @port_price as port_price,
       @edit_port as edit_port,
       @yield as yield,
       @rur as rur,
       @akcii as akcii,
       @obl as obl,
       @other as other,
       @nekot as nekot,
       @date_e as date_e,
 isnull(@index_rts,0), isnull(@index_mmvb,0),
 stock_name,
 port_price_e,
 @client as client,
 stock_type_name, st_type_money
from #rep
end
END
  $ENDTEXT(11424587)
 $ENDFORM

 $STATE2ACTION(НАЧАЛЬНЫЙ.FILTER)
  state=НАЧАЛЬНЫЙ
  action=FILTER
  is_available=1
 $ENDSTATE2ACTION

 $STATE2ACTION(НАЧАЛЬНЫЙ.OCENKA_EFF_GRAPH1)
  state=НАЧАЛЬНЫЙ
  action=OCENKA_EFF_GRAPH1
  is_available=1
 $ENDSTATE2ACTION

 $STATE2ACTION(НАЧАЛЬНЫЙ.TRUST_MANAGE_PORT_REP)
  state=НАЧАЛЬНЫЙ
  action=TRUST_MANAGE_PORT_REP
  is_available=1
 $ENDSTATE2ACTION
$ENDDOC