create procedure dbo.%PROC%
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @broker_id_s varchar(30) = null,
  @deal_type int
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 @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

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 @russia = t_countries.id from t_countries
  where upper(t_countries.country_name) = 'РОССИЯ'
    and .ITEMS_EXISTS(t_countries.id)

/* получение класса */
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"')

/*
select @russia = -1 --!!!!!!!!!!!!!
select @date_b = convert(datetime, '01/01/00',3)
select @date_e = convert(datetime, '01/01/03',3)
select @broker_id = 10000000000037615
*/
select @cur_partner = partner_id,
       @cur_plan = back_money_id
  from tb_broker_change
  where id = @broker_id

create table #report(
  id numeric null,
  type_id numeric null,
  state_id numeric null,
  in_date datetime null,
  in_no varchar(100) null,
  stock_id numeric null,
  stock_qty money null,
  client_id numeric null,
  contragent_id numeric null,
  client_country_id numeric null,
  contragent_country_id numeric null,
  comment varchar(255) null
)
/*необходимо решить что правильно: собирать все вместе или в разбивке на клиента, контрагента, влаадельца.
но вот получается, что в разбивке собирается большее количество записей и по смыслу они все правильно попали, а вот
когда все в кучу, получается гораздо меньше записей*/
-- проба
/*insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  tb_baysale_docitems.client_id,
  tb_baysale_docitems.contragent_id,
(  case when tb_baysale_docitems.ticket_type_id = 1 then 'Покупка' else 'Продажа' end)
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, 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 upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR','DEALINGS_VIA_BROKER_SALE', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')

  and td_depo_docs.id = tb_plan2fact_dates.id
  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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
       or tb_baysale_docitems.contragent_id = t_partners.id
       or tb_baysale_docitems.cnt_properter_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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')
*/


--/*
/* CL BUY */

insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
 -- tb_baysale_docitems.client_id,
  t_partners.id,
  null,-- tb_baysale_docitems.contragent_id,
  'Покупка'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, tb_plan2fact_dates,  tb_contracts
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 upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id
  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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
      or (tb_baysale_docitems.client_contract_id=tb_contracts.id
      and tb_contracts.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_classvalues.id)
 and t_classvalues.code not in ('05320', '05310')

update #report
set  contragent_id=tb_baysale_docitems.contragent_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, tb_plan2fact_dates
where tb_baysale_docitems.id=#report.id
  and  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 upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id
  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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.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_classvalues.id)
 and t_classvalues.code not in ('05320', '05310')

/* CL SALE */
insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  t_partners.id,
  null, --  tb_baysale_docitems.contragent_id,
  'Продажа'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, tb_plan2fact_dates, tb_contracts
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 upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE', 'DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')

  and td_depo_docs.id = tb_plan2fact_dates.id
  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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
      or (tb_baysale_docitems.client_contract_id=tb_contracts.id
      and tb_contracts.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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')

update #report
set  contragent_id=tb_baysale_docitems.contragent_id
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, t_classvalues, t_item2class, tb_plan2fact_dates
where tb_baysale_docitems.id=#report.id
  and 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 upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE', 'DOG_DEALINGS_SALE_VBR')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id
  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (td_depo_docs.in_date >= @date_b
          and td_depo_docs.in_date < dateadd(day, 1, @date_e)) )
  and tb_baysale_docitems.contragent_id = t_partners.id
  and td_depo_docs.owner_id = @cur_partner
  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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')

/* CONT BUY */

insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  null, --  tb_baysale_docitems.client_id,
  tb_baysale_docitems.contragent_id,
  'Покупка'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs, t_partners,
  tb_plan2fact_dates, 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 t_items.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')

  and td_depo_docs.id = tb_plan2fact_dates.id

  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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.contragent_id = t_partners.id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
  and not exists(select 1 from #report where #report.id = tb_baysale_docitems.id)
/* проверка классификатора */
  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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')
  and tb_baysale_docitems.id not in (select id from #report rep)

/* CONT SALE */
insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  null, --  tb_baysale_docitems.client_id,
  tb_baysale_docitems.contragent_id,
  'Продажа'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, tb_plan2fact_dates, 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 t_items.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR')
 -- and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id

  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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.contragent_id = t_partners.id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
  and not exists(select 1 from #report where #report.id = tb_baysale_docitems.id)

/* проверка классификатора */

  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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')
  and tb_baysale_docitems.id not in (select id from #report rep)

/* VLAD BUY */

insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  null, --tb_baysale_docitems.client_id,
  tb_baysale_docitems.cnt_properter_id,
  'Покупка'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, tb_plan2fact_dates, 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 t_items.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR')
-- and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id

  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (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.cnt_properter_id = t_partners.id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
  and not exists(select 1 from #report where #report.id = tb_baysale_docitems.id)
/* проверка классификатора */
  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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')
  and tb_baysale_docitems.id not in (select id from #report rep)

/* VLAD SALE */

insert into #report(
  id,
  type_id,
  state_id,
  in_date,
  in_no,
  stock_id,
  stock_qty,
  client_id,
  contragent_id,
  comment
)
select
  distinct tb_baysale_docitems.id,
  t_items.type_id,
  t_items.state_id,
  isnull(tb_plan2fact_dates.fact_reg_date, td_depo_docs.in_date),
  td_depo_docs.in_no,
  tb_baysale_docitems.stock_id,
  tb_baysale_docitems.stock_qty,
  null, --tb_baysale_docitems.client_id,
  tb_baysale_docitems.cnt_properter_id,
  'Продажа'
from t_items, t_types, t_states, tb_baysale_docitems, td_depo_docs,
  t_partners, tb_plan2fact_dates, 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 t_items.id = td_depo_docs.id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR')
--  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'POST_END','CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and td_depo_docs.id = tb_plan2fact_dates.id

  and ((tb_plan2fact_dates.fact_reg_date >= @date_b
        and tb_plan2fact_dates.fact_reg_date < dateadd(day, 1, @date_e))
      or (td_depo_docs.in_date >= @date_b
          and td_depo_docs.in_date < dateadd(day, 1, @date_e)) )

--  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.cnt_properter_id = t_partners.id
  and t_partners.country_id is not null
  and t_partners.country_id <> @russia
  and not exists(select 1 from #report where #report.id = tb_baysale_docitems.id)

/* проверка классификатора */

  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_classvalues.id)
  and t_classvalues.code not in ('05320', '05310')
  and tb_baysale_docitems.id not in (select id from #report rep)


------------------------------отсеить договора с классификатором VISIBLE_IN_NAUFOR_REPORTS----------
delete #report
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv--, tb_contracts c
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.client_id  or t_i2c.item_id = #report.contragent_id)
----------------------------------------------------------------------------------------------------


update #report set
  client_country_id = t_partners.country_id
  from t_partners
 where #report.client_id = t_partners.id

update #report set
  contragent_country_id = t_partners.country_id
  from t_partners
 where #report.contragent_id = t_partners.id

--select * from #report
/*select t_countries.*, --i.item_name,
 r.* from #report r, t_countries--t_items i
where r.client_country_id=t_countries.id--i.id
  or r.contragent_country_id = t_countries.id --i.id
*/

if @deal_type=1--для ТС
begin
delete #report
from t_types
where #report.type_id= t_types.id
and t_types.type_code not in('DEALINGS_VIA_BROKER_SALE','DEALINGS_VIA_BROKER')
end

if @deal_type=2--для ВБР
begin
delete #report
from t_types
where #report.type_id=t_types.id
and t_types.type_code not in('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
end

select
  #report.comment,
  t_states.state_name,
  #report.in_no,
  #report.in_date as in_date_d,
  #report.stock_qty,
  td_stock_emis.stock_name,
  cli.partner_name as client_name,
  cli.partner_address as client_addres,
  clc.country_code as client_country,
  ctr.partner_name as contragent_name,
  ctr.partner_address as contragent_addres,
  ctc.country_code as contragent_country
from #report, t_states, td_stock_emis, t_partners cli, t_partners ctr, t_countries clc, t_countries ctc
where #report.state_id = t_states.id
  and #report.stock_id = td_stock_emis.id
  and #report.client_id *= cli.id
  and #report.contragent_id *= ctr.id
  and #report.client_country_id *= clc.id
  and #report.contragent_country_id *= ctc.id
order by #report.in_date, #report.in_no


end