# Date: 06/09/06
# Version: 105

$DOC(REPORT_RESULT4PERIOD)
 name=Отчет "Итоги управаления за период"
 class=6
 product=ALD_SECUR
 name_formula=null
 remark_formula=null

 $STATE(SYSTEM)
  name=Системный статус
  class=0
 $ENDSTATE

 $FORM(REPORT_LIST_IN)
  name=Перечень отчета (доходы)
  class=4
  filter=null
  target_state=
  uo=uo_print
  dw=d_client_prom_trad_rep_list12
  procedure=ap_report_resutl4period_in
  patterns=null
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(12141760)
create procedure dbo.%PROC%
   @contract_id_s varchar(30) = null,
   @b_date_d varchar(30) = null,
   @e_date_d varchar(30) = null
as
BEGIN

-- vars --



declare @class_type_id numeric(18,0),
        @acc_442_id numeric(18,0),
        @acc_100_id numeric(18,0),
        @acc_4300_id numeric(18,0),
        @acc_41_id numeric(18,0),
        @acc_5_id numeric(18,0),
        @plan_id numeric(18,0),
        @msg varchar(255),
        @acc_4301_id numeric(18,0),
        @acc_435_id numeric(18,0),
        @acc_431_id numeric(18,0),

        @pereoc_1_1 decimal(18,8),
        @pereoc_1_2 decimal(18,8),
        @pereoc_2_1 decimal(18,8),
        @pereoc_2_2 decimal(18,8),

        @realiz_1_1 decimal(18,8),
        @realiz_1_2 decimal(18,8),
        @realiz_2_1 decimal(18,8),
        @realiz_2_2 decimal(18,8),

        @komiss_1 decimal(18,8),
        @komiss_2 decimal(18,8),

        @kupon_1_1 decimal(18,8),
        @kupon_1_2 decimal(18,8),
        @kupon_2_1 decimal(18,8),
        @kupon_2_2 decimal(18,8),
        @kupon_3_1 decimal(18,8),
        @kupon_3_2 decimal(18,8),
        @kupon_3_3 decimal(18,8),
        @kupon_3_4 decimal(18,8),
        @other decimal(18,8),
        @dividendi decimal(18,8),

        @perpose_52_id numeric(18,0),
        @perpose_46_id numeric(18,0),
        @owner_id numeric(18,0),
        @contract_id numeric(18,0),
        @doc_type_id numeric(18,0),
        @b_date datetime,
        @e_date datetime

select @b_date = convert(datetime,@b_date_d,3),
       @e_date = convert(datetime,@e_date_d,3)

select @contract_id = convert(numeric,@contract_id_s)

create table #report
(
is_b int null,
name varchar(255) null,
 sum decimal(18,8) null
)

create table #stocks
(
  stock_id numeric(18,0) null,
stock_type int null, --// 1 - Корпоративные, 2 - государственные и муниципальные
   is_orcb int null  --// 0 - не обращаются на ОРЦБ, 1 - обращаются
)

create table #kupons
(
item_id numeric(18,0)
)

select @perpose_52_id = t_purposes.id
  from t_purposes
 where t_purposes.code = 52
   and .ITEMS_EXISTS(t_purposes.id)

select @perpose_46_id = t_purposes.id
  from t_purposes
 where t_purposes.code = 46
   and .ITEMS_EXISTS(t_purposes.id)

-- собираем бумаги по которым были проводки в указанный период --

insert #stocks (stock_id)
select distinct td_stock_emis.id
  from td_stock_emis,
       t_operations,
       t_oper_subconto,
       t_oper_list
 where td_stock_emis.id = t_oper_subconto.item_id
   and t_oper_list.head_id = t_operations.id
   and t_oper_subconto.id = t_oper_list.id
   and t_operations.op_date >= @b_date
   and t_operations.op_date <= @e_date
   and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id,'TD_STOCK_EMIS')

-- классифицируем их как коропоративные или государственные, обращаются ли они на ОРЦБ. --

update #stocks
   set #stocks.stock_type = 1
  from td_stock_emis,
       td_emitent_info
 where td_stock_emis.id = #stocks.stock_id
   and td_stock_emis.emmitent_id = td_emitent_info.id
   and td_emitent_info.emi_owned_id = 1


update #stocks
   set stock_type = 2
 where stock_type is null

select @class_type_id = t_classes.id
  from t_classes
 where upper(t_classes.code)='PLACE2COTIR'
   and .ITEMS_EXISTS(t_classes.id)

update #stocks
   set is_orcb = 1
  from t_item2class, t_classvalues
 where t_item2class.item_id = #stocks.stock_id
   and t_item2class.value_id = t_classvalues.id
   and t_classvalues.class_id = @class_type_id
   and t_classvalues.code in ('COTIR')
   and .ITEMS_EXISTS(t_item2class.id)

update #stocks
   set is_orcb = 0
 where is_orcb is null

-- собираем операции с купонами --

insert #kupons
(
item_id
)
select distinct ts_sd_stock_moves.id
  from ts_sd_stock_moves,
       td_depo_docs
 where ts_sd_stock_moves.investor_id_form = @contract_id
   and td_depo_docs.perpose_id in (@perpose_52_id, @perpose_46_id)
   and ts_sd_stock_moves.id = td_depo_docs.id
   and .ITEMS_EXISTS_BY_STATE_CODE(td_depo_docs.id,'OP_END')

insert #kupons
(
item_id
)
select distinct tb_list_stock_redemption.id
  from tb_list_stock_redemption,
       td_depo_docs
 where tb_list_stock_redemption.item_id = td_depo_docs.id
   and tb_list_stock_redemption.investor_id = @contract_id
   and td_depo_docs.perpose_id in (@perpose_52_id, @perpose_46_id)
   and .ITEMS_EXISTS_BY_STATE_CODE(tb_list_stock_redemption.id,'OP_END')

-- счета --

select @plan_id = .PLAN('BACK_MONEY')

.ACCID_FROM_CONST2PLAN(@acc_442_id, 'BACK_ACC_PEREOC', @plan_id, 1, 1, 'Переоценка', @msg)
.ACCID_FROM_CONST2PLAN(@acc_100_id, 'BACK_ACC_SECUR_MP_LONG', @plan_id, 1, 1, 'Переоценка', @msg)
.ACCID_FROM_CONST2PLAN(@acc_4300_id, 'BACK_ACC_PROFIT_BAYSALE_MAIN', @plan_id, 1, 1, 'Реализация ЦБ', @msg)
.ACCID_FROM_CONST2PLAN(@acc_41_id, 'BACK_ACC_COMISSIONS', @plan_id, 1, 1, 'Комиссии', @msg)
.ACCID_FROM_CONST2PLAN(@acc_5_id, 'BACK_ACC_REALIZ', @plan_id, 1, 1, 'Реализация ЦБ', @msg)
.ACCID_FROM_CONST2PLAN(@acc_4301_id, 'BACK_ACC_PROFIT_BAYSALE_FUTUR', @plan_id, 1, 1, 'Реализация ЦБ', @msg)
.ACCID_FROM_CONST2PLAN(@acc_435_id, 'BACK_ACC_PROFIT_OTHER', @plan_id, 1, 1, 'Прочие расходы', @msg)
.ACCID_FROM_CONST2PLAN(@acc_431_id, 'BACK_ACC_PROFIT_DIVIDEND', @plan_id, 1, 1, 'Дивиденды', @msg)


-- переоценка --

select @pereoc_1_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 1)

select @pereoc_1_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 0)


select @pereoc_2_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 1)

select @pereoc_2_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 0)

-- реализация --

select @realiz_1_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 1)

select @realiz_1_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 0)


select @realiz_2_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 1)

select @realiz_2_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = -1
                         and ol2.deb_or_cred = 1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 0)

select @kupon_1_1 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 2
                        and #stocks.is_orcb = 1
                        and t_operations.item_id <> #kupons.item_id)

select @kupon_1_2 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 2
                        and #stocks.is_orcb = 0
                        and t_operations.item_id <> #kupons.item_id)

select @kupon_2_1 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 1
                        and #stocks.is_orcb = 1
                        and t_operations.item_id <> #kupons.item_id)

select @kupon_2_2 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 1
                        and #stocks.is_orcb = 0
                        and t_operations.item_id <> #kupons.item_id)

-- погашение купонов --

select @kupon_3_1 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 2
                        and #stocks.is_orcb = 1
                        and t_operations.item_id = #kupons.item_id)

select @kupon_3_2 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 2
                        and #stocks.is_orcb = 0
                        and t_operations.item_id = #kupons.item_id)

select @kupon_3_3 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 1
                        and #stocks.is_orcb = 1
                        and t_operations.item_id = #kupons.item_id)

select @kupon_3_4 = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            #stocks,
                            #kupons
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_4301_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol1.id
                        and os1.item_id = @contract_id
                        and os11.item_id = #stocks.stock_id
                        and #stocks.stock_type = 1
                        and #stocks.is_orcb = 0
                        and t_operations.item_id = #kupons.item_id)


------ Доход от долевого участия -------

select @dividendi = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1
                      where t_operations.id = ol1.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_431_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os1.item_id = @contract_id)


----------- Прочие доходы  -------------

select @other = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            t_oper_list ol11,
                            t_accounts
                      where t_operations.id = ol1.head_id
                        and t_operations.id = ol11.head_id
                        and ol1.deb_or_cred = -1
                        and ol1.acc_id = @acc_435_id
                        and ol11.deb_or_cred = 1
                        and ol11.acc_id = t_accounts.id
                        and t_accounts.acc_num_eff like '2.%'
                        and t_accounts.plan_id = @plan_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol11.id
                        and os1.item_id = @contract_id
                        and os11.item_id = @contract_id)

------ сбор таблицы ----

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       'Доходы управляющего за период',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0) + isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0) + isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0)
       + isnull(@realiz_2_1,0) + isnull(@realiz_2_2,0) + isnull(@kupon_1_1,0) + isnull(@kupon_1_2,0) + isnull(@kupon_2_1,0) + isnull(@kupon_2_2,0)
       + isnull(@kupon_3_1,0) + isnull(@kupon_3_2,0) + isnull(@kupon_3_3,0) + isnull(@kupon_3_4,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
        0,
       'в том числе',
       null
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '1. Переоценка ценных бумаг',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0) + isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1. государственные и муниципальные',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1.1. обращающиеся  на ОРЦБ',
       isnull(@pereoc_1_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1.2. не обращающиеся на ОРЦБ',
       isnull(@pereoc_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2. корпоративные',
       isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2.1. обращающиеся на ОРЦБ',
       isnull(@pereoc_2_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2.2. не обращающиеся на ОРЦБ',
       isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '2. Доход от реализации ценных бумаг',
       isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0) + isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0) + isnull(@kupon_1_1,0) + isnull(@kupon_1_2,0)
       + isnull(@kupon_2_1,0) + isnull(@kupon_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1. государственные и муниципальные',
       isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0) + isnull(@kupon_1_1,0) + isnull(@kupon_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.1. Стоимость ценных бумаг',
       isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.1.1. обращающиеся на ОРЦБ',
       isnull(@realiz_1_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.1.2. не обращающиеся на ОРЦБ',
       isnull(@realiz_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.2. Купон',
       isnull(@kupon_1_1,0) + isnull(@kupon_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.2.1. обращающиеся на ОРЦБ',
       isnull(@kupon_1_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.2.2. не обращающиеся на ОРЦБ',
       isnull(@kupon_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2. корпоративные',
       isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0) + isnull(@kupon_2_1,0) + isnull(@kupon_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.1. Стоимость ценных бумаг',
       isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.1.1. обращающиеся на ОРЦБ',
       isnull(@realiz_2_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.1.2. не обращающиеся на ОРЦБ',
       isnull(@realiz_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.2. Купон',
       isnull(@kupon_2_1,0)  + isnull(@kupon_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.2.1. обращающиеся на ОРЦБ',
       isnull(@kupon_2_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.2.2. не обращающиеся на ОРЦБ',
       isnull(@kupon_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '3. Доход от погашения купонов',
       isnull(@kupon_3_1,0) + isnull(@kupon_3_2,0) + isnull(@kupon_3_3,0) + isnull(@kupon_3_4,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.1. государственные и муниципальные',
       isnull(@kupon_3_1,0) + isnull(@kupon_3_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.1.1. обращающиеся  на ОРЦБ',
       isnull(@kupon_3_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.1.2. не обращающиеся на ОРЦБ',
       isnull(@kupon_3_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.2. корпоративные',
       isnull(@kupon_3_3,0) + isnull(@kupon_3_4,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.2.1. обращающиеся на ОРЦБ',
       isnull(@kupon_3_3,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.2.2. не обращающиеся на ОРЦБ',
       isnull(@kupon_3_4,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '4. Доход от долевого участия',
       isnull(@dividendi,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '5. Прочие доходы',
       isnull(@other,0)
)

--------

select #report.name,
       #report.sum,
       #report.is_b
  from #report


delete #report
delete #stocks
delete #kupons

END
  $ENDTEXT(12141760)
 $ENDFORM

 $FORM(REPORT_LIST_OUT)
  name=Перечень отчета (расходы)
  class=4
  filter=null
  target_state=
  uo=uo_print
  dw=d_client_prom_trad_rep_list11
  procedure=ap_report_resutl4period_out
  patterns=GENERIC_VIEW_LIST
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(12141762)
create procedure dbo.%PROC%
   @contract_id_s varchar(30) = null,
   @b_date_d varchar(30) = null,
   @e_date_d varchar(30) = null
as
BEGIN

-- vars --

declare @class_type_id numeric(18,0),
        @acc_442_id numeric(18,0),
        @acc_100_id numeric(18,0),
        @acc_4300_id numeric(18,0),
        @acc_41_id numeric(18,0),
        @acc_5_id numeric(18,0),
        @acc_435_id numeric(18,0),
        @plan_id numeric(18,0),
        @msg varchar(255),
        @contract_id numeric(18,0),

        @pereoc_1_1 decimal(18,8),
        @pereoc_1_2 decimal(18,8),
        @pereoc_2_1 decimal(18,8),
        @pereoc_2_2 decimal(18,8),

        @realiz_1_1 decimal(18,8),
        @realiz_1_2 decimal(18,8),
        @realiz_2_1 decimal(18,8),
        @realiz_2_2 decimal(18,8),

        @other decimal(18,8),

        @komiss_1 decimal(18,8),
        @komiss_2 decimal(18,8),
        @b_date datetime,
        @e_date datetime



select @b_date = convert(datetime,@b_date_d,3),
       @e_date = convert(datetime,@e_date_d,3)

select @contract_id = convert(numeric,@contract_id_s)

create table #report
(
is_b int null,
name varchar(255) null,
 sum decimal(18,8) null
)

create table #stocks
(
  stock_id numeric(18,0) null,
stock_type int null, --// 1 - Корпоративные, 2 - государственные и муниципальные
   is_orcb int null  --// 0 - не обращаются на ОРЦБ, 1 - обращаются
)

-- собираем бумаги по которым были проводки в указанный период --

insert #stocks (stock_id)
select distinct td_stock_emis.id
  from td_stock_emis,
       t_operations,
       t_oper_subconto,
       t_oper_list
 where td_stock_emis.id = t_oper_subconto.item_id
   and t_oper_list.head_id = t_operations.id
   and t_oper_subconto.id = t_oper_list.id
   and t_operations.op_date >= @b_date
   and t_operations.op_date <= @e_date
   and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id,'TD_STOCK_EMIS')

-- классифицируем их как коропоративные или государственные, обращаются ли они на ОРЦБ. --

update #stocks
   set stock_type = 1
  from td_stock_emis,
       td_emitent_info
 where td_stock_emis.id = #stocks.stock_id
   and td_stock_emis.emmitent_id = td_emitent_info.id
   and td_emitent_info.emi_owned_id = 1

update #stocks
   set stock_type = 2
 where stock_type is null

select @class_type_id = t_classes.id
  from t_classes
 where upper(t_classes.code)='PLACE2COTIR'
   and .ITEMS_EXISTS(t_classes.id)

update #stocks
   set is_orcb = 1
  from t_item2class, t_classvalues
 where t_item2class.item_id = #stocks.stock_id
   and t_item2class.value_id = t_classvalues.id
   and t_classvalues.class_id = @class_type_id
   and t_classvalues.code in ('COTIR')
   and .ITEMS_EXISTS(t_item2class.id)

update #stocks
   set is_orcb = 0
 where is_orcb is null

-- счета --

select @plan_id = .PLAN('BACK_MONEY')

.ACCID_FROM_CONST2PLAN(@acc_442_id, 'BACK_ACC_PEREOC', @plan_id, 1, 1, 'Переоценка', @msg)
.ACCID_FROM_CONST2PLAN(@acc_100_id, 'BACK_ACC_SECUR_MP_LONG', @plan_id, 1, 1, 'Переоценка', @msg)
.ACCID_FROM_CONST2PLAN(@acc_4300_id, 'BACK_ACC_PROFIT_BAYSALE_MAIN', @plan_id, 1, 1, 'Реализация ЦБ', @msg)
.ACCID_FROM_CONST2PLAN(@acc_41_id, 'BACK_ACC_COMISSIONS', @plan_id, 1, 1, 'Комиссии', @msg)
.ACCID_FROM_CONST2PLAN(@acc_5_id, 'BACK_ACC_REALIZ', @plan_id, 1, 1, 'Реализация ЦБ', @msg)
.ACCID_FROM_CONST2PLAN(@acc_435_id, 'BACK_ACC_PROFIT_OTHER', @plan_id, 1, 1, 'Прочие расходы', @msg)

-- переоценка --


select @pereoc_1_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 1)

select @pereoc_1_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 0)


select @pereoc_2_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 1)

select @pereoc_2_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_442_id
                         and ol2.acc_id = @acc_100_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 0)


-- реализация --

select @realiz_1_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 1)

select @realiz_1_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 2
                         and #stocks.is_orcb = 0)


select @realiz_2_1 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 1)

select @realiz_2_2 = (select sum(ol1.sum_bal)
                        from t_operations,
                             t_oper_list ol1,
                             t_oper_subconto os1,
                             t_oper_list ol2,
                             t_oper_subconto os11,
                             #stocks
                       where t_operations.id = ol1.head_id
                         and t_operations.id = ol2.head_id
                         and ol1.deb_or_cred = 1
                         and ol2.deb_or_cred = -1
                         and ol1.acc_id = @acc_4300_id
                         and ol2.acc_id = @acc_5_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol1.id
                         and os11.id = ol1.id
                         and os1.item_id = @contract_id
                         and os11.item_id = #stocks.stock_id
                         and #stocks.stock_type = 1
                         and #stocks.is_orcb = 0)


-- Космиссии --

select @komiss_1 = (select sum(ol.sum_bal)
                      from t_operations,
                           t_oper_list ol,
                           t_oper_subconto os1,
                           t_oper_subconto os11,
                           td_prices
                       where t_operations.id = ol.head_id
                         and ol.deb_or_cred = 1
                         and ol.acc_id = @acc_41_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol.id
                         and os11.id = ol.id
                         and os1.item_id = @contract_id
                         and os11.item_id = td_prices.id
                         and td_prices.service_id = 1)


select @komiss_2 = (select sum(ol.sum_bal)
                      from t_operations,
                           t_oper_list ol,
                           t_oper_subconto os1,
                           t_oper_subconto os11,
                           td_prices
                       where t_operations.id = ol.head_id
                         and ol.deb_or_cred = 1
                         and ol.acc_id = @acc_41_id
                         and t_operations.op_date >= @b_date
                         and t_operations.op_date <= @e_date
                         and os1.id = ol.id
                         and os11.id = ol.id
                         and os1.item_id = @contract_id
                         and os11.item_id = td_prices.id
                         and td_prices.service_id = 3)


----------- Прочие доходы  -------------

select @other = (select sum(ol1.sum_bal)
                       from t_operations,
                            t_oper_list ol1,
                            t_oper_subconto os1,
                            t_oper_subconto os11,
                            t_oper_list ol11,
                            t_accounts
                      where t_operations.id = ol1.head_id
                        and t_operations.id = ol11.head_id
                        and ol1.deb_or_cred = 1
                        and ol1.acc_id = @acc_435_id
                        and ol11.deb_or_cred = -1
                        and ol11.acc_id = t_accounts.id
                        and t_accounts.acc_num_eff like '2.%'
                        and t_accounts.plan_id = @plan_id
                        and t_operations.op_date >= @b_date
                        and t_operations.op_date <= @e_date
                        and os1.id = ol1.id
                        and os11.id = ol11.id
                        and os1.item_id = @contract_id
                        and os11.item_id = @contract_id)


------ сбор таблицы ----

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       'Расходы управляющего за период',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0) + isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0) + isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0)
       + isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0) + isnull(@komiss_1,0) + isnull(@komiss_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       'в том числе',
       null
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '1. Переоценка ценных бумаг',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0) + isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1. государственные и муниципальные',
       isnull(@pereoc_1_1,0) + isnull(@pereoc_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1.1. обращающиеся  на ОРЦБ',
       isnull(@pereoc_1_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.1.2. не обращающиеся на ОРЦБ',
       isnull(@pereoc_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2. корпоративные',
       isnull(@pereoc_2_1,0) + isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2.1. обращающиеся на ОРЦБ',
       isnull(@pereoc_2_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '1.2.2. не обращающиеся на ОРЦБ',
       isnull(@pereoc_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '2. Расход от реализации ценных бумаг',
       isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0) + isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1. государственные и муниципальные',
       isnull(@realiz_1_1,0) + isnull(@realiz_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.1. обращающиеся на ОРЦБ',
       isnull(@realiz_1_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.1.2. не обращающиеся на ОРЦБ',
       isnull(@realiz_1_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2. корпоративные',
       isnull(@realiz_2_1,0)  + isnull(@realiz_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.1. обращающиеся на ОРЦБ',
       isnull(@realiz_2_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '2.2.2. не обращающиеся на ОРЦБ',
       isnull(@realiz_2_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '3. Вознаграждения управляющего',
       0 -- пока не собирается
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.1. Вознаграждение за управление',
       0 -- пока не собирается
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '3.2. Вознаграждение за успех',
       0 -- пока не собирается
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '4. Комиссии',
       isnull(@komiss_1,0) + isnull(@komiss_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '4.1. Биржевая комиссия',
       isnull(@komiss_1,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       0,
       '4.2. Брокерская комиссия',
       isnull(@komiss_2,0)
)

insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '5. Возмещение расходов',
       0 -- пока не собирается
)


insert #report (
       is_b,
       name,
       sum
)values(
       1,
       '6. Прочие расходы',
       isnull(@other,0)
)

----------

select #report.name,
       #report.sum,
       #report.is_b
  from #report

delete #report

END
  $ENDTEXT(12141762)
 $ENDFORM

 $STATE2ACTION(SYSTEM.REPORT_LIST_IN)
  state=SYSTEM
  action=REPORT_LIST_IN
  is_available=0
 $ENDSTATE2ACTION

 $STATE2ACTION(SYSTEM.REPORT_LIST_OUT)
  state=SYSTEM
  action=REPORT_LIST_OUT
  is_available=0
 $ENDSTATE2ACTION
$ENDDOC