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

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

set nocount on
select @action_id = %ACTION_ID%

--.CHECK_USER
--.CHECK_VIEW

declare @date_b datetime
declare @date_e datetime
declare @dogovor_id numeric
declare @cur_plan numeric
declare @cur_partner numeric
declare @acc_id numeric
declare @subc_str_bal varchar(255)
declare @mess varchar(255)
declare @broker_id numeric


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

if @dogovor_id_s = '' select @dogovor_id_s = null
select @dogovor_id = convert(numeric, @dogovor_id_s)

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

create table #report(
  buy_sum money null,
  sale_sum money null,
  comment varchar(255) null
)

create table #rep(
  buy_sum money null,
  sale_sum money null,
  place_id numeric null
)


/* PLACE BUY */

insert into #rep(
  buy_sum,
  sale_sum,
  place_id
)
select
  sum(tb_baysale_docitems.summ_doc_total)+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
  0,
  tb_baysale_docitems.sale_place_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER')
  and upper(t_states.state_code) in ('PODT')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.in_date >= @date_b
  and td_depo_docs.in_date < dateadd(day, 1, @date_e)
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)

group by tb_baysale_docitems.sale_place_id

/* PLACE SALE */

insert into #rep(
  buy_sum,
  sale_sum,
  place_id
)
select
  0,
  sum(tb_baysale_docitems.summ_doc_total)+sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
  tb_baysale_docitems.sale_place_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE')
  and upper(t_states.state_code) in ('PODT')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.in_date >= @date_b
  and td_depo_docs.in_date < dateadd(day, 1, @date_e)
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)
group by tb_baysale_docitems.sale_place_id


/* сделки РТС */

insert into #rep(
  buy_sum,
  sale_sum,
  place_id
)
select
  sum(tb_baysale_docitems.summ_doc_total)+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
  0,
  tb_baysale_docitems.sale_place_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  td_rate_places, tb_plan2fact_dates
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and td_depo_docs.owner_id = @cur_partner
  and t_items.id  = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('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.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
--  and td_depo_docs.in_date >= @date_b
--  and td_depo_docs.in_date < dateadd(day, 1, @date_e)
  and td_rate_places.id = tb_baysale_docitems.sale_place_id
  and td_rate_places.place_code in ('RTS', 'RTS_BUKS')
   and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)
group by tb_baysale_docitems.sale_place_id

/* PLACE SALE RTS*/
insert into #rep(
  buy_sum,
  sale_sum,
  place_id
)
select
  0,
  sum(tb_baysale_docitems.summ_doc_total)+sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
  tb_baysale_docitems.sale_place_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  td_rate_places, tb_plan2fact_dates
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and td_depo_docs.owner_id = @cur_partner
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('DOG_DEALINGS_SALE_VBR')
  and upper(t_states.state_code) in ('CLOSED', 'OBYAZ_END')
  and tb_plan2fact_dates.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
--  and td_depo_docs.in_date >= @date_b
--  and td_depo_docs.in_date < dateadd(day, 1, @date_e)
  and td_rate_places.id = tb_baysale_docitems.sale_place_id
  and td_rate_places.place_code in ('RTS', 'RTS_BUKS')
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)
group by tb_baysale_docitems.sale_place_id

/* сумма ВБР */
declare @vbr_buy money
declare @vbr_sale money

select @vbr_buy = sum(tb_baysale_docitems.summ_doc_total)+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0))
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs, tb_plan2fact_dates
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('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 upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
and tb_plan2fact_dates.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
   and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)

if @vbr_buy is null
  select @vbr_buy = 0

select @vbr_sale = sum(tb_baysale_docitems.summ_doc_total) + sum(isnull(tb_baysale_docitems.coupon_deal_sum,0))
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs, tb_plan2fact_dates
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('DOG_DEALINGS_SALE_VBR')
  and upper(t_states.state_code) in ('CLOSED', 'OBYAZ_END')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and tb_plan2fact_dates.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)

if @vbr_sale is null
  select @vbr_sale = 0

/* RTS */
declare @rts_buy money
declare @rts_sale money

select @rts_buy = sum(tb_baysale_docitems.summ_doc_total)+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0))
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  tb_plan2fact_dates, td_rate_places
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('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 upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and tb_plan2fact_dates.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
  and td_rate_places.id = tb_baysale_docitems.sale_place_id
  and td_rate_places.place_code in ('RTS', 'RTS_BUKS')
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)
if @rts_buy is null
  select @rts_buy = 0

select @rts_sale = sum(tb_baysale_docitems.summ_doc_total)+sum(isnull(tb_baysale_docitems.coupon_deal_sum,0))
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  tb_plan2fact_dates, td_rate_places
where t_items.type_id = t_types.id
  and t_items.state_id = t_states.id
  and t_items.id = tb_baysale_docitems.id
  and t_items.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and td_depo_docs.owner_id = @cur_partner
  and upper(t_types.type_code) in ('DOG_DEALINGS_SALE_VBR')
  and upper(t_states.state_code) in ('CLOSED', 'OBYAZ_END')
-- and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and tb_plan2fact_dates.fact_reg_date >= @date_b
  and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e)
  and td_rate_places.id = tb_baysale_docitems.sale_place_id
  and td_rate_places.place_code in ('RTS', 'RTS_BUKS')
  and (tb_baysale_docitems.client_contract_id = @dogovor_id or @dogovor_id is null)
if @rts_sale is null
  select @rts_sale = 0

insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  sum(#rep.buy_sum) + @vbr_buy-@rts_buy,
  sum(#rep.sale_sum) + @vbr_sale-@rts_sale,
  'Совершенные сделки - всего'
 from #rep

insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  sum(#rep.buy_sum),
  sum(#rep.sale_sum),
  'Сделки совершенные через организаторов торговли, - всего'
 from #rep

insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  null,
  null,
  '- в том числе по организаторам торговли'


insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  sum(#rep.buy_sum),
  sum(#rep.sale_sum),
  td_rate_places.place_name
 from #rep, td_rate_places
where #rep.place_id = td_rate_places.id
group by td_rate_places.place_name
order by td_rate_places.place_name

insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  @vbr_buy - @rts_buy,
  @vbr_sale - @rts_sale,
  'Сделки, совершенные на внебиржевом рынке'

------------------------------отсеить договора с классификатором VISIBLE_IN_NAUFOR_REPORTS----------
---хотя в фильтре будет невозможно и выбрать такие договора, на всяк сл делаем еще такую проверку
/*delete #report
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv
where upper(ltrim(rtrim(t_c.code)))=upper(ltrim(rtrim('VISIBLE_IN_NAUFOR_REPORTS')))
  and t_cv.class_id=t_c.id
  and t_i2c.value_id=t_cv.id
  and t_cv.code = 'INVISIBLE_IN_REPORT'
  and t_i2c.item_id = @dogovor_id
  and @dogovor_id is not null*/
----------------------------------------------------------------------------------------------------
select
  null, -- вставить номер
  round(#report.buy_sum  /1000.0, 0),
  round(#report.sale_sum /1000.0, 0),
  #report.comment
from #report

end