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(255) null,     --тип сделки (покупка или продажа)
in_date datetime null,           --дата сделки
in_no varchar(100) null,         --номер операции
stock_id_s varchar(30) null,     --id бумаги
stock_name varchar(255) null,    --наименование бумаги
stock_type varchar(255) 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 varchar(255) null,    --площадка
unkd_sum money null,             --ункд
itogo_sum money null,            --итого
operation_name varchar(255) null, --хотят, чтобы "Покупка/Продажа ЦБ" было написано, как группировка
lf_date datetime null, --архивная дата (для группировки)
lf_no numeric null, --архивный номер (для группировки),
pur_name varchar(255) null
)

insert #result(
id,type_id,in_date,
in_no,stock_id_s,stock_name,
emiss_statereg_no,stock_price,
stock_qty,summ_doc_total,sale_place, unkd_sum,
lf_date,lf_no, pur_name
)

select
tb_baysale_docitems.id,

  type_id = case
      when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' then 1
      when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR' then 1
      when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' then -1
      when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' then -1
      else ''
    end,
/*
  type_name = case
    when (upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR', 'DEALINGS_VIA_BROKER'))
      and not exists (select 1 from tb_repo_params trp, t_purposes tp
                        where trp.id = tb_baysale_docitems.id
                          and tp.id = trp.repo_type_id
                          and tp.code in ('54','55','56','57')) then 'Покупка'
    when (upper(t_types.type_code) in ('DOG_DEALINGS_SALE_VBR', 'DEALINGS_VIA_BROKER_SALE'))
      and not exists (select 1 from tb_repo_params trp, t_purposes tp
                        where trp.id = tb_baysale_docitems.id
                          and tp.id = trp.repo_type_id
                          and tp.code in ('54','55','56','57')) then 'Продажа'
    when exists (select 1 from tb_repo_params trp, t_purposes tp
      where trp.id = tb_baysale_docitems.id
      and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57'))
   then (select tp.name from tb_repo_params trp, t_purposes tp
           where trp.id = tb_baysale_docitems.id
           and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57'))
   else ''
   end,
*/
  contr1.in_date,
  contr1.in_no,
  convert (varchar(30), tb_baysale_docitems.stock_id) as stock_id_s,
  (td_stock_emis.stock_name + ' (' + td_stock_emis.emiss_statereg_no + ')' ) as stock_name,
  td_stock_emis.emiss_statereg_no,
  round(tb_baysale_docitems.doc_sum/tb_baysale_docitems.stock_qty,8),
  tb_baysale_docitems.stock_qty,
  tb_baysale_docitems.doc_sum,
  (select place_name from td_rate_places where id=tb_baysale_docitems.sale_place_id) as sale_place,
  tb_baysale_docitems.coupon_deal_sum,
  contr1.lf_date,
  contr1.lf_no,
 (select t_purposes.name from tb_repo_params trp, t_purposes tp, t_purposes
                      where trp.id = tb_baysale_docitems.id
                        and tp.id = trp.repo_type_id
                        and tp.code in (54,55,56,57)
                        and t_purposes.id = trp.repo_type_id) as pur_name

  from t_items,
       t_types,
       t_states,
       td_depo_docs contr1,
       tb_baysale_docitems,
       td_stock_emis,
       tb_plan2fact_dates d

  where t_items.id = tb_baysale_docitems.id
    and tb_baysale_docitems.id = contr1.id
    and contr1.owner_id = @cur_partner
--    and tb_baysale_docitems.character_id = @character_id
    and t_types.id = t_items.type_id
    and t_states.id = t_items.state_id
    and t_states.class_id = 0
    and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR')
    and t_states.state_code in ('PODT', 'OBYAZ_END')
-- проверка на не незавершённость
    and d.id = tb_baysale_docitems.id
    and ((isnull(d.fact_reg_date, contr1.in_date) >= isnull(d.fact_pay_date, contr1.in_date)
          and isnull(d.fact_reg_date, contr1.in_date) < dateadd(dd, 1, @date_e)
          and isnull(d.fact_reg_date, contr1.in_date) >= @date_b)
      or (isnull(d.fact_reg_date, contr1.in_date) < isnull(d.fact_pay_date, contr1.in_date)
          and isnull(d.fact_pay_date, contr1.in_date)  < dateadd(dd, 1, @date_e)
          and isnull(d.fact_pay_date, contr1.in_date)  >= @date_b))
--
    and contr1.in_date >= @date_b
    and contr1.in_date <  dateadd(dd, 1, @date_e)
    and (tb_baysale_docitems.client_contract_id = @broker_id or tb_baysale_docitems.client_contract_id is null)
/*    and (td_stock_emis.id = @stock_id or td_stock_emis.id between @stock_id_bb and @stock_id_ee)*/
    and tb_baysale_docitems.stock_id = td_stock_emis.id
  /*  and not exists (select 1 from tb_repo_params trp, t_purposes tp
                      where trp.id = tb_baysale_docitems.id
                        and tp.id = trp.repo_type_id
                        and tp.code in (54,55,56,57))*/
--select * from #result --test

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

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
operation_name,
--pur_name,
(case when pur_name is not null then pur_name
      else
       (case when type_id = 1 then 'Покупка' else 'Продажа' end)  end) as type_name,
in_date,
in_no,
stock_name,
stock_type,
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(unkd_sum,0),
isnull(itogo_sum,0)
from #result
order by operation_name, sale_place,lf_date,lf_no--in_date,stock_name          --stock_name,stock_id_s,in_date,in_no



END