create procedure dbo.%PROC%
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @broker_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%
--execute dbo.ap_nau33_oper_by_dogs_list;1  @broker_id_s='21535000001051575', @date_b_d = '01/10/05', @date_e_d = '31/12/05'
--.CHECK_USER
--.CHECK_VIEW
--execute dbo.ap_nau33_oper_by_dogs_list;1  @broker_id_s='21535000001051575', @date_b_d = '01/10/05', @date_e_d = '30/12/05'
declare @date_b datetime
declare @date_e datetime
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
declare @russia numeric(18,0)
declare @n numeric(3,1) -- int

select @russia = t_countries.id from t_countries
  where upper(t_countries.country_name) = 'РОССИЯ'
    and .ITEMS_EXISTS(t_countries.id)

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

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

create table #report(
  id numeric IDENTITY,
  nomer numeric(3,1) null,
  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,
  comment varchar(255) null,
  contract_id numeric null,
  cur_date datetime null )

/* получение класса */
declare @class_type_id numeric(18,0)
select @class_type_id = t_classes.id
  from t_classes
 where upper(t_classes.code)='FCS_STOCKS'
   and .ITEMS_EXISTS(t_classes.id)

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

create table #shablon(
  nomer numeric(3,1) null, -- int null,
  is_class int,
  class1 varchar(10) null,
  class2 varchar(10) null,
  select_type int,
  dog_type int,
  comment varchar(100) null )


select @n = 0
while @n <= 27
begin
  insert into #shablon select 1+@n, 0,  null,    null,   1, ((27-@n)/27 + 1), 'Сделки - всего'
  insert into #shablon select 2+@n, 1, '05310', '05320', 2, ((27-@n)/27 + 1), 'Сделки с госбумагами - всего'
  insert into #shablon select 3+@n, 1, '05310', '05320', 3, ((27-@n)/27 + 1), 'Сделки с госбумагами - нерезиденты'
  insert into #shablon select 4+@n, 1, '05310', '05320', 4, ((27-@n)/27 + 1), 'Сделки с госбумагами - по видам ценных бумаг'
  insert into #shablon select 5+@n, 1, '1',      null,   2, ((27-@n)/27 + 1), 'Сделки с ЦБ входящими в котировальный уровень - всего'
  insert into #shablon select 6+@n, 1, '1',      null,   3, ((27-@n)/27 + 1), 'Сделки с ЦБ входящими в котировальный уровень - нерезиденты'
  insert into #shablon select 7+@n, 1, '1',      null,   4, ((27-@n)/27 + 1), 'Сделки с ЦБ входящими в котировальный уровень - по видам ценных бумаг'
  insert into #shablon select 8+@n, 1, '05330',  '05350',2, ((27-@n)/27 + 1), 'Сделки с акциями и облигациями, допущенными к торгам через организаторов торговли на рынке ЦБ'
  insert into #shablon select 9+@n, 1, '05330',  '05350',3, ((27-@n)/27 + 1), 'Допущенные ЦБ - нерезиденты'
  insert into #shablon select 10+@n,1, '05330',  '05350',4, ((27-@n)/27 + 1), 'Допущенные ЦБ - по видам ценных бумаг'
  insert into #shablon select 11+@n,1, '05340',  '05360',2, ((27-@n)/27 + 1), 'Сделки с акциями и облигациями, не допущенными к торгам через организаторов торговли на рынке ЦБ'
  insert into #shablon select 12+@n,1, '05340',  '05360',3, ((27-@n)/27 + 1), 'Недопущенные ЦБ - нерезиденты'
  insert into #shablon select 13+@n,1, '05340',  '05360',4, ((27-@n)/27 + 1), 'Недопущенные ЦБ - по видам ценных бумаг'
  insert into #shablon select 14+@n,1, '05370',  null,   2, ((27-@n)/27 + 1), 'Сделки с прочими ценными бумагами'
  insert into #shablon select 15+@n,1, '05370',  null,   5, ((27-@n)/27 + 1), 'Сделки с прочими ценными бумагами - иностранных эмитентов'
  insert into #shablon select 16+@n,1, '05370',  null,   4, ((27-@n)/27 + 1), 'Сделки с прочими ценными бумагами - по видам ценных бумаг'
  insert into #shablon select 17+@n,0,  null,    null,   6, ((27-@n)/27 + 1), 'Сделки с производными финансовыми документами'
  insert into #shablon select 18+@n,0,  null,    null,   6, ((27-@n)/27 + 1), '   - в том числе с депозитарным расписками иностранных банков'
  insert into #shablon select 19+@n,0,  null,    null,   6, ((27-@n)/27 + 1), '   - в том числе по каждому виду депозитарной расписки'
  insert into #shablon select 20+@n,0,  null,    null,   6, ((27-@n)/27 + 1), 'Сделки с фьючерсными контрактами'
  insert into #shablon select 21+@n,0,  null,    null,   6, ((27-@n)/27 + 1), 'Сделки с опционами - всего'
  insert into #shablon select 22+@n,0,  null,    null,   7, ((27-@n)/27 + 1), 'Сделки типа РЕПО'
  insert into #shablon select 23+@n,0,  null,    null,   8, ((27-@n)/27 + 1), 'Маржинальные сделки'
  insert into #shablon select 24+@n,0,  null,    null,   6, ((27-@n)/27 + 1), 'Маржинальные сделки - в том числе доля клиента'
  insert into #shablon select 25+@n,0,  null,    null,   9, ((27-@n)/27 + 1), 'Сделки с ЦБ, совершённые через системы электронной и интернет торговли'
  insert into #shablon select 26+@n,0,  null,    null,  10, ((27-@n)/27 + 1), 'Непроклассифицированные ЦБ'
  select @n = @n + 27
end
insert into #shablon select 27,0,null,null,6, 0, 'Сделки, совершенные во исполнение договора на  управление ценными бумагами'


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'

/* селект № 1 */

insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment , contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
#shablon.comment,
tb_baysale_docitems.client_contract_id
,td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems,
  td_depo_docs, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 1

/* селект № 2 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date  )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment,
tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems,
  td_depo_docs, t_classvalues, t_item2class, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id = @cur_partner
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 2

/* селект № 3 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment , contract_id, cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and ((tb_baysale_docitems.client_id = t_partners.id and t_partners.country_id is not null and t_partners.country_id <> @russia)
      or (tb_baysale_docitems.client_contract_id=tbc.id and tbc.client_id=t_partners.id
          and t_partners.country_id is not null and t_partners.country_id <> @russia)
      or (tb_baysale_docitems.contragent_id = t_partners.id and t_partners.country_id is not null and t_partners.country_id <> @russia))
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 3

insert into #report(nomer,/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id, comment, cur_date)
  select #shablon.nomer, null, null,null, null,null, null, #shablon.comment, null
    from #shablon
    where #shablon.select_type = 4

/* селект № 4 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment,  contract_id,cur_date)
select
  (#shablon.nomer + 0.1),
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  t_stock_types.stock_type_name, tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_classvalues, t_item2class, t_stock_types, td_stock_emis, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id = @cur_partner
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
/* типы ЦБ */
  and td_stock_emis.id = tb_baysale_docitems.stock_id
  and t_stock_types.id = td_stock_emis.stock_type_id
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 4

/* селект № 5 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment,  contract_id , cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
 #shablon.comment,
tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, td_emitent_info, td_stock_emis, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and td_stock_emis.id = tb_baysale_docitems.stock_id
  and td_emitent_info.id = td_stock_emis.emmitent_id
  and t_partners.id = td_emitent_info.partner_id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 5

/* селект № 6 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, cur_date )
select
  #shablon.nomer,
  0,
  0,
  null,
  0,
  0,
  null,
  #shablon.comment, null
from #shablon
where #shablon.select_type = 6


/* селект № 7 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id , cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment,
tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  tb_repo_params, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and tb_repo_params.id = tb_baysale_docitems.id
  and tb_repo_params.id = td_depo_docs.id
  and .PURPOSE_CODE(tb_repo_params.repo_type_id) in (54,55,56,57)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 7

/* селект № 8 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date

from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_purposes,tb_repo_params, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and tb_repo_params.id = tb_baysale_docitems.id
  and tb_repo_params.repo_type_id=t_purposes.id
/* как по новому */
--   and tb_repo_params.margin_deal = 1
/* как по старому*/
  and tb_repo_params.repo_type_id=t_purposes.id
--  and t_purposes.id=.GET_PURPOSE_ID(@cur_partner,67,t_items.type_id)
  and t_purposes.id = (select t_purposes.id
                         from t_purposes,t_partners_types_list,t_items,t_relation2purposes
                         where t_purposes.id=t_partners_types_list.item_id and
                           t_items.id=@cur_partner and
                           t_purposes.code=67 and
                           t_partners_types_list.partner_type_id = t_items.type_id and
                           t_relation2purposes.doc_id =t_items.type_id and
                           t_purposes.id=t_relation2purposes.purpose_id)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 8

/* селект № 9 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems,
  td_depo_docs, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id=@cur_partner
  and td_depo_docs.document_form_id in (3, 4)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 9

/* селект № 10 */
insert into #report(
  nomer,/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id , cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
   #shablon.comment,
tb_baysale_docitems.client_contract_id,
td_depo_docs.in_date
from tb_contract_types tct, tb_contracts tbc, t_items, t_types, t_states, tb_baysale_docitems,
  td_depo_docs, #shablon  -- t_classvalues, t_item2class
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 tb_baysale_docitems.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', '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 td_depo_docs.owner_id = @cur_partner
/* проверка классификатора */
  and not exists (select 1
                    from t_item2class, t_classvalues
                    where t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2))
  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 10

/* половина по ВБР */
/* селект № 1 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id , cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
(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)
from tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems,
  td_depo_docs, tb_contract_types tct, tb_contracts tbc, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 1

/* селект № 2 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <>@usd_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @usd_id

          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,td_depo_docs.in_date
from tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, #shablon,
  td_depo_docs, t_classvalues, t_item2class, tb_contract_types tct, tb_contracts tbc
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id = @cur_partner
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 2

/* селект № 3 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id , cur_date)
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <>@usd_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
(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)
from tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, tb_contract_types tct, tb_contracts tbc, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ( 'DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
  and tb_baysale_docitems.client_id = t_partners.id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 3

/* селект № 4 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  (#shablon.nomer + 0.1),
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  t_stock_types.stock_type_name, tb_baysale_docitems.client_contract_id,td_depo_docs.in_date
from tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_classvalues, t_item2class, t_stock_types, td_stock_emis, tb_contract_types tct,
  tb_contracts tbc, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
-- and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
/* типы ЦБ */
  and td_stock_emis.id = tb_baysale_docitems.stock_id
  and t_stock_types.id = td_stock_emis.stock_type_id
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 4

/* селект № 5 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment,tb_baysale_docitems.client_contract_id,
(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)
from tb_contract_types tct, tb_contracts tbc, tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, td_emitent_info, td_stock_emis, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ( 'DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
  and td_stock_emis.id = tb_baysale_docitems.stock_id
  and td_emitent_info.id = td_stock_emis.emmitent_id
  and t_partners.id = td_emitent_info.partner_id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
/* проверка классификатора */
  and t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 5

/* селект № 7 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,
(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)
from tb_contract_types tct, tb_contracts tbc, tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  tb_repo_params, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ( 'DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
  and tb_repo_params.id = tb_baysale_docitems.id
  and tb_repo_params.id = td_depo_docs.id
  and .PURPOSE_CODE(tb_repo_params.repo_type_id) in (54,55,56,57)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 7

/* селект № 8 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,(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)
from tb_contract_types tct, tb_contracts tbc, tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_purposes,tb_repo_params, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ( 'DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
  and tb_repo_params.id = tb_baysale_docitems.id
/* как по новому */
--   and tb_repo_params.margin_deal = 1
/* как по старому*/
  and tb_repo_params.repo_type_id=t_purposes.id
--  and t_purposes.id=.GET_PURPOSE_ID(@cur_partner,67,t_items.type_id)
  and t_purposes.id = (select t_purposes.id
                         from t_purposes, t_partners_types_list, t_items, t_relation2purposes
                         where t_purposes.id = t_partners_types_list.item_id and
                           t_items.id = @cur_partner and
                           t_purposes.code = 67 and
                           t_partners_types_list.partner_type_id = t_items.type_id and
                           t_relation2purposes.doc_id = t_items.type_id and
                           t_purposes.id = t_relation2purposes.purpose_id
/* and exists(select 1
                from t_items, t_states
                where t_items.id=t_purposes.id
                  and t_states.id=t_items.state_id
                  and t_states.class_id=0) and
       exists(select 1
                from t_items, t_states
                where t_items.id=t_partners_types_list.id
                  and t_states.id=t_items.state_id
                  and t_states.class_id=0) and
       exists(select 1
                from t_items, t_states
                where t_items.id=t_relation2purposes.id
                  and t_states.id=t_items.state_id
                  and t_states.class_id=0) */ )
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 8

/* селект № 9 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,(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)
from tb_contract_types tct, tb_contracts tbc, tb_plan2fact_dates, t_items, t_types, t_states,
  tb_baysale_docitems, td_depo_docs, #shablon
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ( 'DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id=@cur_partner
  and td_depo_docs.document_form_id in (3, 4)
--  and tb_baysale_docitems.character_id = 2
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 9

/* селект № 10 */
insert into #report(
  nomer,
/*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id,
  comment, contract_id, cur_date )
select
  #shablon.nomer,
 s1 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id = @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
  (case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR'  and tb_baysale_docitems.cur_id <> @rur_id then tb_baysale_docitems.cur_id else 0 end),

  s2 = (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id = @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id
          then isnull(tb_baysale_docitems.summ_doc_total2unkd,0) else 0 end),
 (case when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' and tb_baysale_docitems.cur_id <> @rur_id then
         tb_baysale_docitems.cur_id end),
  #shablon.comment, tb_baysale_docitems.client_contract_id,(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)
from tb_plan2fact_dates, t_items, t_types, t_states, tb_baysale_docitems, #shablon,
  td_depo_docs, tb_contract_types tct, tb_contracts tbc -- , t_classvalues, t_item2class
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 tb_baysale_docitems.id = td_depo_docs.id
  and t_items.id = tb_plan2fact_dates.id
  and upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('POST_END', 'OBYAZ_END', 'CLOSED', 'POLUCH')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_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.owner_id = @cur_partner
/* проверка классификатора */
  and not exists (select 1
                    from t_item2class, t_classvalues
                    where t_item2class.item_id = tb_baysale_docitems.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 (t_classvalues.code = #shablon.class1 or t_classvalues.code = #shablon.class2))
/* проверка типа договора */
  and tbc.id = tb_baysale_docitems.client_contract_id -- td_depo_docs.id
  and tct.id = tbc.dog_type_id
  and tct.contract_class_id = #shablon.dog_type
  and #shablon.select_type = 10

insert into #report(nomer, /*  buy_sum,
  sale_sum,*/
  buy_sum_rur,
  buy_sum_cur,
  buy_cur_id,
  sale_sum_rur,
  sale_sum_cur,
  sale_cur_id, comment)
  select #shablon.nomer, null, null, null, null,null, null,#shablon.comment
    from #shablon, #report
    where #report.nomer <> #shablon.nomer

------------------------------отсеить договора с классификатором 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 = #report.contract_id
----------------------------------------------------------------------------------------------------
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 @data_sost datetime, @rate money, @rate_date datetime
declare @id numeric, @cur_date datetime

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

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

while .CURSOR_STATE=0
begin

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

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

update #report set
buy_sum_rur = isnull(@buy_sum_rur,0),
sale_sum_rur = isnull(@sale_sum_rur,0)
where nomer = @nomer
  and id = @id

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

close curs
.DEALLOCATE curs
----------------------------------------------------

select
  case
  when convert(varchar(10), #report.nomer) like '%.0'
  then substring(convert(varchar(10), #report.nomer),1,datalength(convert(varchar(10),#report.nomer))-2)
  else convert(varchar(10), #report.nomer)
  end,
  round(sum(#report.buy_sum_rur)  /1000.0, 0),
  round(sum(#report.sale_sum_rur) /1000.0, 0),
  #report.comment
from #report
group by #report.comment, #report.nomer
order by #report.nomer

end