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(
  id numeric IDENTITY,
  buy_sum_rur money null,
  buy_sum_cur money null,
  sale_sum_rur money null,
  sale_sum_cur money null,
  buy_cur_id numeric null,
  sale_cur_id numeric null,
  place_id numeric null,
  investor_id numeric(18,0) null
)

declare @rur_id numeric
select @rur_id = id
from t_currencies
where cur_code = 'RUR'

declare @usd_id numeric
select @usd_id = id
from t_currencies
where cur_code = 'USD'


/* PLACE BUY */

insert into #rep(
  buy_sum_rur ,
  buy_sum_cur ,
  buy_cur_id,
/*  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,*/
  place_id,
  investor_id
)
select
--  sum(tb_baysale_docitems.doc_sum) ,--+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
(case when tb_baysale_docitems.cur_id = @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),
--  0,
  tb_baysale_docitems.sale_place_id,
  tb_baysale_docitems.client_contract_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, tb_baysale_docitems.cur_id,
tb_baysale_docitems.client_contract_id

/* PLACE SALE */

insert into #rep(
/*  buy_sum_rur ,
  buy_sum_cur ,
  buy_cur_id,*/
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  place_id,
  investor_id
)
select
--  0,
--  sum(tb_baysale_docitems.doc_sum),-- + sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
(case when tb_baysale_docitems.cur_id = @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),
  tb_baysale_docitems.sale_place_id,
  tb_baysale_docitems.client_contract_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, tb_baysale_docitems.cur_id,
tb_baysale_docitems.client_contract_id


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

insert into #rep(
  buy_sum_rur ,
  buy_sum_cur ,
  buy_cur_id,
/*  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,*/
  place_id,
  investor_id
)
select
--  sum(tb_baysale_docitems.doc_sum) ,--+ sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
-- 0,
(case when tb_baysale_docitems.cur_id = @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  tb_baysale_docitems.sale_place_id,
  tb_baysale_docitems.client_contract_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, tb_baysale_docitems.cur_id,tb_baysale_docitems.client_contract_id

/* PLACE SALE RTS*/
insert into #rep(
/*  buy_sum_rur ,
  buy_sum_cur ,
  buy_cur_id,*/
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  place_id,
  investor_id
)
select
--  0,
--  sum(tb_baysale_docitems.doc_sum),-- + sum(isnull(tb_baysale_docitems.coupon_deal_sum,0)),
(case when tb_baysale_docitems.cur_id = @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0)) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  tb_baysale_docitems.sale_place_id,
  tb_baysale_docitems.client_contract_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, tb_baysale_docitems.cur_id, tb_baysale_docitems.client_contract_id

/* сумма ВБР */
declare @vbr_buy_rur money
declare @vbr_buy_usd money
declare @vbr_buy_usd2rur money
declare @vbr_sale_rur money
declare @vbr_sale_usd money
declare @vbr_sale_usd2rur money

create table #vbr
(type_id int null,
 sum_cur money null,
 cur_id numeric null,
 sum_rur money null,
 cur_date datetime null
)

select @vbr_buy_rur = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,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)
 and tb_baysale_docitems.cur_id = @rur_id
 and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)

--изменяем, поскольку курс конвертации валюты д.б. на дату факт перерег-ии
--select @vbr_buy_usd =  sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0))
--dog_calc_order
insert into #vbr(type_id,sum_cur, cur_id, sum_rur,cur_date)
select  1, --@vbr_buy
(case when tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else null end),
(case when tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else null end),
(case when tb_baysale_docitems.cur_id <> @rur_id then
   (case when tb_baysale_docitems.dog_calc_order = 1 then tb_plan2fact_dates.fact_pay_date
      when tb_baysale_docitems.dog_calc_order = 2 then tb_plan2fact_dates.fact_reg_date
    end)
else null end)

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)
 and tb_baysale_docitems.cur_id = @usd_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


if @vbr_buy_rur is null
  select @vbr_buy_rur = 0

select @vbr_sale_rur = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,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)
  and tb_baysale_docitems.cur_id = @rur_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)

--изменяем, поскольку курс конвертации валюты д.б. на дату факт перерег-ии
--select @vbr_sale_usd = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0))

insert into #vbr(type_id,sum_cur, cur_id, sum_rur,cur_date)
select  2, --@vbr_sale_usd
(case when tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else null end),
(case when tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else null end),
(case when tb_baysale_docitems.cur_id <> @rur_id then
   (case when tb_baysale_docitems.dog_calc_order = 1 then tb_plan2fact_dates.fact_pay_date
      when tb_baysale_docitems.dog_calc_order = 2 then tb_plan2fact_dates.fact_reg_date
    end)
else null end)

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)
  and tb_baysale_docitems.cur_id = @usd_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


if @vbr_sale_rur is null
  select @vbr_sale_rur = 0

/* RTS */
declare @rts_buy_rur money
declare @rts_buy_usd money
declare @rts_buy_usd2rur money
declare @rts_sale_rur money
declare @rts_sale_usd money
declare @rts_sale_usd2rur money


select @rts_buy_rur = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,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)
  and tb_baysale_docitems.cur_id = @rur_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


--изменяем, поскольку курс конвертации валюты д.б. на дату факт перерег-ии
--select @rts_buy_usd = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0))

insert into #vbr(type_id,sum_cur, cur_id, sum_rur,cur_date)
select  3, --@rts_buy_usd
(case when tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else null end),
(case when tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else null end),
(case when tb_baysale_docitems.cur_id <> @rur_id then
   (case when tb_baysale_docitems.dog_calc_order = 1 then tb_plan2fact_dates.fact_pay_date
      when tb_baysale_docitems.dog_calc_order = 2 then tb_plan2fact_dates.fact_reg_date
    end)
else null end)

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)
  and tb_baysale_docitems.cur_id = @usd_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


if @rts_buy_rur is null
  select @rts_buy_rur = 0

select @rts_sale_rur = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,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)
  and tb_baysale_docitems.cur_id = @rur_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


--изменяем, поскольку курс конвертации валюты д.б. на дату факт перерег-ии
--select @rts_sale_usd = sum(isnull(tb_baysale_docitems.summ_doc_total2unkd,0))

insert into #vbr(type_id,sum_cur, cur_id, sum_rur,cur_date)
select  4, --@rts_sale_usd
(case when tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
(case when tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else null end),
(case when tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else null end),
(case when tb_baysale_docitems.cur_id <> @rur_id then
   (case when tb_baysale_docitems.dog_calc_order = 1 then tb_plan2fact_dates.fact_pay_date
      when tb_baysale_docitems.dog_calc_order = 2 then tb_plan2fact_dates.fact_reg_date
    end)
else null end)

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)
  and tb_baysale_docitems.cur_id = @usd_id
and not exists (select 1
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 =tb_baysale_docitems.client_contract_id)


if @rts_sale_rur is null
  select @rts_sale_rur = 0
---------------------------------------------------------------------
declare @usl_type_id int,@sum_cur money, @sum_cur_id numeric,@cur_date datetime
declare @sum_rur money
declare @rate money, @rate_date datetime

declare curs cursor for
select type_id,sum_cur, cur_id,cur_date from #vbr
where isnull(sum_cur,0)<>0
for update

open curs
fetch curs into @usl_type_id,@sum_cur, @sum_cur_id,@cur_date

while .CURSOR_STATE=0
begin

--1, --@vbr_buy_usd,  2, --@vbr_sale_usd, 3, --@rts_buy_usd, --4--@rts_sale_usd,

.CONVERT_SUM(@sum_cur_id, @rur_id, @cur_date , 0, @sum_cur, @sum_rur, @rate, @rate_date)

select @vbr_buy_rur = isnull(@vbr_buy_rur,0) + @sum_rur
where @usl_type_id = 1

select @vbr_sale_rur = isnull(@vbr_sale_rur,0) + @sum_rur
where @usl_type_id = 2

select @rts_buy_rur = isnull(@rts_buy_rur,0) + @sum_rur
where @usl_type_id = 3

select @rts_sale_rur = isnull(@rts_sale_rur,0) + @sum_rur
where @usl_type_id = 4

fetch curs into @usl_type_id,@sum_cur, @sum_cur_id,@cur_date
end

close curs
.DEALLOCATE curs

---------------------------------------------------------------------
/*select @vbr_buy_usd = isnull(@vbr_buy_usd,0),
       @vbr_sale_usd = isnull(@vbr_sale_usd ,0),
       @rts_buy_usd = isnull(@rts_buy_usd ,0),
       @rts_sale_usd = isnull(@rts_sale_usd ,0)

.CONVERT_SUM(@usd_id, @rur_id, @date_e, 0, @vbr_buy_usd , @vbr_buy_usd2rur, @rate, @rate_date)
.CONVERT_SUM(@usd_id, @rur_id, @date_e, 0, @vbr_sale_usd , @vbr_sale_usd2rur, @rate, @rate_date)
.CONVERT_SUM(@usd_id, @rur_id, @date_e, 0, @rts_buy_usd , @rts_buy_usd2rur, @rate, @rate_date)
.CONVERT_SUM(@usd_id, @rur_id, @date_e, 0, @rts_sale_usd , @rts_sale_usd2rur , @rate, @rate_date)

select @vbr_buy_rur = @vbr_buy_rur + @vbr_buy_usd2rur
select @vbr_sale_rur = @vbr_sale_rur + @vbr_sale_usd2rur
select @rts_buy_rur = @rts_buy_rur + @rts_buy_usd2rur
select @rts_sale_rur = @rts_sale_rur + @rts_sale_usd2rur
*/
----------------------------курсор для конвертации валюты----
declare @nomer numeric(3,1), @buy_sum_cur money, @buy_cur_id numeric, @sale_sum_cur money, @sale_cur_id numeric
declare @buy_sum_rur money,  @sale_sum_rur money
declare @id numeric

declare curs cursor for
select id, buy_sum_cur, buy_cur_id, sale_sum_cur, sale_cur_id from #rep
where isnull(buy_sum_cur,0)<>0 or isnull(sale_sum_cur,0)<>0
for update

open curs
fetch curs into @id, @buy_sum_cur, @buy_cur_id, @sale_sum_cur, @sale_cur_id

while .CURSOR_STATE=0
begin

select @buy_sum_cur = isnull(@buy_sum_cur,0),
       @sale_sum_cur = isnull(@sale_sum_cur,0)

if isnull(@buy_sum_cur,0) <> 0
.CONVERT_SUM(@buy_cur_id, @rur_id, @date_e , 0, @buy_sum_cur, @buy_sum_rur, @rate, @rate_date)

if isnull(@sale_sum_cur,0) <> 0
.CONVERT_SUM(@sale_cur_id, @rur_id, @date_e , 0, @sale_sum_cur, @sale_sum_rur, @rate, @rate_date)

update #rep set
buy_sum_rur = isnull(@buy_sum_rur,0),
sale_sum_rur = isnull(@sale_sum_rur,0)
where id = @id

fetch curs into @id, @buy_sum_cur, @buy_cur_id, @sale_sum_cur, @sale_cur_id
end

close curs
.DEALLOCATE curs

------------------------------отсеить договора с классификатором VISIBLE_IN_NAUFOR_REPORTS----------
---хотя в фильтре будет невозможно и выбрать такие договора, на всяк сл делаем еще такую проверку
delete #rep
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 =investor_id
--= @dogovor_id
--  and @dogovor_id is not null
-------------------------------------------------------
-----------------------------------------
--здесь!
insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  sum(isnull(#rep.buy_sum_rur,0)) + isnull(@vbr_buy_rur,0)-isnull(@rts_buy_rur,0),
  sum(isnull(#rep.sale_sum_rur,0)) + isnull(@vbr_sale_rur,0)-isnull(@rts_sale_rur,0),
  'Совершенные сделки - всего'
 from #rep

--select   #rep.sale_sum_rur, @vbr_sale_rur, @rts_sale_rur from #rep  --test

insert into #report(
  buy_sum,
  sale_sum,
  comment)
select
  sum(#rep.buy_sum_rur),
  sum(#rep.sale_sum_rur),
  'Сделки совершенные через организаторов торговли, - всего'
 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_rur),
  sum(#rep.sale_sum_rur),
  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_rur - @rts_buy_rur,
  @vbr_sale_rur - @rts_sale_rur,
  'Сделки, совершенные на внебиржевом рынке'

select
  null, -- вставить номер
  round(#report.buy_sum  /1000.0, 0),
  round(#report.sale_sum /1000.0, 0),
  #report.comment
from #report

end