create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null, /* Брокер */
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @investor_id_s varchar(30) = null  --клиентский договор

as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @id numeric
begin

select @action_id = %ACTION_ID%

declare @broker_id numeric
declare @date_b datetime
declare @date_e datetime
declare @cur_plan numeric
declare @partner_id numeric
declare @class_type_id numeric
declare @investor_id numeric

select @broker_id = convert(numeric, @broker_id_s)
select @investor_id = convert(numeric, @investor_id_s)

select @partner_id = partner_id,
       @cur_plan = back_securs_id
    from tb_broker_change
    where id = @broker_id

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

create table #stocks(
  stock_id numeric null,
  sale_profit money null,
  tax_profit money null,
  sum_buy_price money null,
  real_sum money null,
  charge_sale money null,
  charge_buy money null,
  profit_comiss money null,
  tax_result money null,
  class_value varchar(30) null
)


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

if @class_type_id is null
  .EXIT_MESSAGE('В системе не найден классификатор с кодом "TAX_STOCKS"')

/* сделки РТС */
insert into #stocks(
  stock_id,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  contr.stock_id,
  (contr.sum4real_out + contr.loss4real_out),
  contr.sum4tax,
  contr.sum4real_in,
  contr.sum4real_out,
  contr.loss4real_out,
  contr.loss4real_in,
  contr.profit_loss_main,
  contr.profit_loss4tax

    from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems contr, tb_repo_params trp,
      t_purposes tp
    where t_items.id=contr.id
      and contr1.id=contr.id
      and contr1.owner_id = @partner_id
      and t_types.id = t_items.type_id
      and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE') -- 'DEALINGS_VIA_BROKER'
--      and upper(t_states.state_code) in ('PODT', 'OBYAZ_END')
      and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
      and t_states.id=t_items.state_id
      and contr1.in_date >= @date_b
      and contr1.in_date < dateadd(dd, 1, @date_e)
      and trp.id = contr.id
      and tp.id = trp.repo_type_id
      and tp.code not in (54, 55, 56, 57)
      and (contr.client_contract_id = @investor_id or contr.client_contract_id is null)



/* сделки ВБР */
insert into #stocks(
  stock_id,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  contr.stock_id,
  (contr.sum4real_out + contr.loss4real_out),
  contr.sum4tax,
  contr.sum4real_in,
  contr.sum4real_out,
  contr.loss4real_out,
  contr.loss4real_in,
  contr.profit_loss_main,
  contr.profit_loss4tax

    from t_items, t_types, t_states, td_depo_docs contr1, tb_baysale_docitems contr,
      tb_plan2fact_dates, tb_repo_params trp, t_purposes tp
    where t_items.id=contr.id
      and contr1.id=contr.id
      and contr1.owner_id = @partner_id
      and t_types.id=t_items.type_id
      and upper(t_types.type_code) in ('DOG_DEALINGS_SALE_VBR') -- 'DOG_DEALINGS_BAY_VBR'
--      and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END')
      and upper(t_states.state_code) in ('OBYAZ_END','PODP','CLOSED','OPL_END','POST_END')
      and tb_plan2fact_dates.id=contr.id
      and t_states.id=t_items.state_id
      and tb_plan2fact_dates.fact_reg_date >= @date_b
      and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
      and trp.id = contr.id
      and tp.id = trp.repo_type_id
      and tp.code not in (54, 55, 56, 57)
      and (contr.client_contract_id = @investor_id or contr.client_contract_id is null)

-- Получение классификаторов
update #stocks set
  class_value = t_classvalues.code
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 .ITEMS_EXISTS(t_item2class.id)
--  and .ITEMS_EXISTS(t_classvalues.id)

create table #report(
  no varchar(5) null,
  comment varchar(255) null,
  sale_profit money null,
  tax_profit money null,
  sum_buy_price money null,
  real_sum money null,
  charge_sale money null,
  charge_buy money null,
  profit_comiss money null,
  tax_result money null
)

-- Бумаги, обращающиеся на организованном рынке
insert into #report(
  no,
  comment)
select
  '1',
  'Бумаги, обращающиеся на организованном рынке'

insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.1',
  'Акции (RUR) в том числе:',
  sum(isnull(#stocks.sale_profit,0)),
  sum(isnull(#stocks.tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='1'

insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.2',
  'ГКО (RUR) в том числе:',
  sum(isnull(#stocks.sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='2'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.3',
  'Корпоративные облигации (RUR) в том числе:',
  sum(isnull(#stocks.sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='3'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.4',
  'ОФЗ (RUR) в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='4'

insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.5',
  'Региональные и муниципальные облигации (RUR) в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='5'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '1.6',
  'Другие бумаги, обращающиеся на ОРЦБ в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='6'

insert into #report(
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  'Итого по группе',
  sum(isnull(#stocks.sale_profit,0)),
  sum(isnull(#stocks.tax_profit,0)),
  sum(isnull(#stocks.sum_buy_price,0)),
  sum(isnull(#stocks.real_sum,0)),
  sum(isnull(#stocks.charge_sale,0)),
  sum(isnull(#stocks.charge_buy,0)),
  sum(isnull(#stocks.profit_comiss,0)),
  sum(isnull(#stocks.tax_result,0))

from #stocks
where #stocks.class_value in ('1','2','3','4','5','6')


insert into #report(
  no,
  comment)
select
  '2',
  'Бумаги, не обращающиеся на организованном рынке'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '2.1',
  'Еврооблигации (RUR) в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='7'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '2.2',
  'ОВГВЗ (RUR) в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='8'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '2.3',
  'Другие бумаги, не обращающиеся на организованном рынке в том числе:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where #stocks.class_value  ='9'


insert into #report(
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  'Итого по группе',
  sum(isnull(#stocks.sale_profit,0)),
  sum(isnull(#stocks.tax_profit,0)),
  sum(isnull(#stocks.sum_buy_price,0)),
  sum(isnull(#stocks.real_sum,0)),
  sum(isnull(#stocks.charge_sale,0)),
  sum(isnull(#stocks.charge_buy,0)),
  sum(isnull(#stocks.profit_comiss,0)),
  sum(isnull(#stocks.tax_result,0))

from #stocks
where #stocks.class_value in ('7','8','9')


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '2.4',
  'Непроклассифицированные бумаги:',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))

from #stocks
where isnull(#stocks.class_value, '0')  = '0'


insert into #report(
  no,
  comment,
  sale_profit,
  tax_profit,
  sum_buy_price,
  real_sum,
  charge_sale,
  charge_buy,
  profit_comiss,
  tax_result)
select
  '3',
  'Всего финансовый результат от реализации ЦБ',
  sum(isnull(sale_profit,0)),
  sum(isnull(tax_profit,0)),
  sum(isnull(sum_buy_price,0)),
  sum(isnull(real_sum,0)),
  sum(isnull(charge_sale,0)),
  sum(isnull(charge_buy,0)),
  sum(isnull(profit_comiss,0)),
  sum(isnull(tax_result,0))
from #report

select * from #report

end