create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null,
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30)= null

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

set nocount on

select @action_id = %ACTION_ID%

declare @broker_id numeric
declare @date_b datetime
declare @date_e datetime
declare @asset_place_id numeric
declare @cur_plan numeric
declare @cur_partner numeric
declare @subc_str_bal varchar(255)
declare @acc_id numeric
declare @mess varchar(255)
declare @cur_partner2plan numeric
declare @stock_subc numeric
declare @investor2portfolio_id numeric
declare @temp_broker_id numeric


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

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

if upper(.ITEM_TYPE_CODE(@broker_id)) = 'BROKER_INFO'
begin
  select @temp_broker_id = @broker_id

  select @cur_partner = partner_id,
         @cur_plan = back_securs_id
    from tb_broker_change
    where id = @broker_id
end
else
begin
  select @cur_partner = td_depo_docs.owner_id
    from td_depo_docs
   where td_depo_docs.id = @broker_id

  select @temp_broker_id = .ORG_FROM_PARTNER(@cur_partner)

  select @cur_plan = back_securs_id
    from tb_broker_change
    where id = @temp_broker_id
end

--select @broker_id, @cur_partner , @cur_plan --test

create table #result(
id numeric null,                 --id депозит. операции
type_id numeric null,            --зачисление- (1), списание (- 1)
type_name varchar(100) null,     --целевое назначение операции
in_date datetime null,           --дата
in_no varchar(100) null,         --номер операции
stock_id varchar(30) null,     --id бумаги
stock_name varchar(255) null,    --наименование бумаги
stock_type varchar(255) null,    --тип бумаги
stock_type_id numeric null,
emiss_statereg_no varchar(255) null, --гос. номер бумаги
stock_price decimal(18,8) null,
stock_qty money null,            --количество
summ_doc_total money null,       --сумма сделки
comis_ts money null,             --биржевая комиссия
comis_dp money null,             --депозитарный сбор
comis_vb money null,             --комиссия внешнего брокера
comis_ou money null,             --комиссия брокера
sale_place_id numeric null,
sale_place varchar(255) null,    --площадка
unkd_sum money null,             --ункд
itogo_sum money null,
operation_name varchar(255) null
)

--select @cur_partner, @broker_id --test

insert #result(
id,type_id,type_name,in_date,
in_no,stock_id,stock_name,
--emiss_statereg_no,
stock_price,
stock_qty,summ_doc_total,
unkd_sum
)

select
td_stock_moves.id,
  type_id = case
      when upper(t_types.type_code) = 'D_STOCK_IN_OTHER_DEPO' then 1--Прием ЦБ во внешнем депозитарии
      when upper(t_types.type_code) = 'D_STOCK_OUT_OTHER_DEPO' then -1 --Снятие с хранения ЦБ во внешнем депозитарии
      end,
  t_purposes.name,
  td_depo_docs.in_date,
  td_depo_docs.in_no,
  convert (varchar(30), td_stock_moves.stock_id) as stock_id,
 (td_stock_emis.stock_name + ' (' + td_stock_emis.emiss_statereg_no + ')' ) as stock_name,
 -- td_stock_emis.emiss_statereg_no,
 -- td_stock_moves.stock_price,
 round(td_stock_moves.stock_price/td_stock_moves.stock_qty,8),
  td_stock_moves.stock_qty,
  td_stock_moves.stock_price,--сумма сделки
  td_stock_moves.coupon_deal_sum

  from t_items,
       t_types,
       t_states,
       td_depo_docs ,
       td_stock_moves,
       t_purposes,
       td_stock_emis--, tb_plan2fact_dates d

  where t_items.id = td_stock_moves.id
    and td_stock_moves.id= td_depo_docs.id
    and td_depo_docs.owner_id = @cur_partner
    and t_types.id = t_items.type_id
    and t_states.id = t_items.state_id
    and t_states.class_id = 0
    and t_states.state_code in ('OP_END')
    and upper(t_types.type_code) <> 'STOCK_TRANSFER_OTHER_DEPO'
    and td_depo_docs.in_date >= @date_b
    and td_depo_docs.in_date <  dateadd(dd, 1, @date_e)
    and (td_stock_moves.client_contract_id = @broker_id or td_stock_moves.client_contract_id is null)
    and td_stock_moves.stock_id = td_stock_emis.id
    and td_stock_moves.is_dogovor_id  =t_purposes.id


update #result
set  sale_place= td_rate_places.place_name
from tb_official_props,
     td_rate_places, td_stock_moves
where isnull(td_stock_moves.to_object_id,td_stock_moves.from_object_id)= tb_official_props.asset_place_id
  and tb_official_props.rate_place_id=td_rate_places.id
and tb_official_props.investor_id =@broker_id
and td_stock_moves.id= #result.id

update #result
set stock_type = t_stock_types.stock_type_name, stock_type_id=t_stock_types.stock_type_id
from td_stock_emis tds,
     t_stock_types
where tds.id =  stock_id
  and tds.stock_type_id *= t_stock_types.id

--select * from #result --test

--для векселей
update #result
set emiss_statereg_no = '('+ isnull(tds.serial,'')+' / '+ isnull(tds.stock_emiss_no,'')+')'
from td_stock_emis tds
where tds.id =  stock_id
and #result.stock_type_id  = 3 --векселя

update #result set
comis_ts=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 1
      and .ITEMS_EXISTS(td_comis2oper.id)),
/*comis_ts_nds=(select sum(td_comis2oper.comis) * @nds from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 1
      and .ITEMS_EXISTS(td_comis2oper.id)),*/
comis_dp=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 7
      and .ITEMS_EXISTS(td_comis2oper.id))

/*update #result set
comis_dp_nds=(select sum(td_comis2oper.comis) * @nds from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 7
      and .ITEMS_EXISTS(td_comis2oper.id)),
comis_kk=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 5
      and .ITEMS_EXISTS(td_comis2oper.id)),
comis_kk_nds=(select sum(td_comis2oper.comis) * @nds from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 5
      and .ITEMS_EXISTS(td_comis2oper.id))*/

update #result set
comis_vb=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 3
      and .ITEMS_EXISTS(td_comis2oper.id)),
/*comis_vb_nds=(select sum(td_comis2oper.comis) * @nds from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 3
      and .ITEMS_EXISTS(td_comis2oper.id)),*/
comis_ou=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 6
      and .ITEMS_EXISTS(td_comis2oper.id))

/*update #result set
comis_ou_nds=(select sum(td_comis2oper.comis) * @nds from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 6
      and .ITEMS_EXISTS(td_comis2oper.id))*/

/*update #result
set itogo_sum = case when type_id = 1 then (-1)*(isnull(summ_doc_total,0)+isnull(unkd_sum,0)+ isnull(comis_ts,0)+
isnull(comis_dp,0)+isnull(comis_vb,0))
                     when type_id = -1 then (isnull(summ_doc_total,0)+isnull(unkd_sum,0)- isnull(comis_ts,0)-
isnull(comis_dp,0)-isnull(comis_vb,0))
                end,
operation_name = 'Покупка/Продажа ЦБ'*/

--select * from #result --test

select
(case when type_id = 1 then 'Зачисление ЦБ' else 'Списание ЦБ' end) as operation_name,
type_name,
in_date,
in_no,
stock_name,
stock_type,
isnull(emiss_statereg_no,''),
isnull(stock_price,0),
isnull(stock_qty,0),--*type_id,
isnull(summ_doc_total,0),
isnull(comis_ts,0),
isnull(comis_dp,0),
isnull(comis_vb,0),
--isnull(comis_ou,0),
sale_place,--isnull(sale_place,'ВБР'),
isnull(unkd_sum,0)--,isnull(itogo_sum,0)
from #result
order by operation_name, sale_place,in_date,stock_name          --stock_name,stock_id,in_date,in_no



END