create procedure dbo.%PROC%

  @sid varchar(30) = null,
  @broker_id_s varchar(30) = null,
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null

as
.BEGIN('N')

begin tran
declare @sum int,
        @rus_id numeric,
        @owner_id numeric,
        @line_name varchar(255),
        @line_code varchar(30)

set dateformat dmy
declare @date_db datetime, @date_de datetime
select @date_db = convert(datetime, @date_b_d), @date_de = convert(datetime, @date_e_d)

select @owner_id = partner_id
  from tb_broker_change
  where tb_broker_change.id = convert(numeric(18,0), @broker_id_s)

create table #report (n_str numeric(2,0), naimen varchar(255), qty numeric(18,0))

insert into #report (n_str, naimen, qty)
  select 1, 'Количество клиентов на конец отчётного периода - всего', count(tb_contracts.id)
    from tb_contracts, td_depo_docs, tb_contract_types
    where tb_contract_types.id = tb_contracts.dog_type_id
      and tb_contract_types.contract_class_id in (1, 2)
      and .ITEMS_EXISTS_BY_TYPE_STATE(tb_contracts.id,'CLIENT_CONTRACTS','SIGN')
      and (.ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id, 'PARTNERS_FIS' ) or .ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_JUR'))
      and td_depo_docs.id = tb_contracts.id
      and td_depo_docs.owner_id = @owner_id
      and tb_contracts.dogexec_begin_date <= @date_de
      and (tb_contracts.dogexec_end_date >= @date_de or tb_contracts.dogexec_end_date is null)
      and not exists ( select 1
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv, tb_contracts
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_contracts.id)

insert into #report (n_str, naimen, qty)
  select 2, ' - в том числе физических лиц', count(tb_contracts.id)
    from tb_contracts ,td_depo_docs,tb_contract_types
    where tb_contract_types.id=tb_contracts.dog_type_id
      and tb_contract_types.contract_class_id in (1, 2)
--      and tb_contract_types.code in ('2','3')
      and .ITEMS_EXISTS_BY_TYPE_STATE(tb_contracts.id,'CLIENT_CONTRACTS','SIGN')
      and .ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_FIS')
      and td_depo_docs.id=tb_contracts.id
      and td_depo_docs.owner_id=@owner_id
      and tb_contracts.dogexec_begin_date <= @date_de
      and (tb_contracts.dogexec_end_date >= @date_de or tb_contracts.dogexec_end_date is null)
      and not exists ( select 1
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv, tb_contracts
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_contracts.id)

insert into #report (n_str, naimen, qty)
  select 3, ' - в том числе юридических лиц', count(tb_contracts.id)
    from tb_contracts ,td_depo_docs,tb_contract_types
    where tb_contract_types.id=tb_contracts.dog_type_id
      and tb_contract_types.contract_class_id in (1, 2)
--      and tb_contract_types.code in ('2','3')
      and .ITEMS_EXISTS_BY_TYPE_STATE(tb_contracts.id,'CLIENT_CONTRACTS','SIGN')
      and .ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_JUR')
      and td_depo_docs.id=tb_contracts.id
      and td_depo_docs.owner_id=@owner_id
      and tb_contracts.dogexec_begin_date <= @date_de
      and (tb_contracts.dogexec_end_date >= @date_de or tb_contracts.dogexec_end_date is null)
      and not exists ( select 1
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv, tb_contracts
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_contracts.id)

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

insert into #report (n_str, naimen, qty)
  select 4, ' - в том числе резидентов', count(tb_contracts.id)
    from tb_contracts ,td_depo_docs,tb_contract_types,t_partners
    where tb_contract_types.id=tb_contracts.dog_type_id
      and tb_contract_types.contract_class_id in (1, 2)
--      and tb_contract_types.code in ('2','3')
      and .ITEMS_EXISTS_BY_TYPE_STATE(tb_contracts.id,'CLIENT_CONTRACTS','SIGN')
      and (.ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_FIS') or .ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_JUR'))
      and .ITEMS_EXISTS(tb_contracts.client_id)
      and t_partners.id=tb_contracts.client_id
      and td_depo_docs.id=tb_contracts.id
      and td_depo_docs.owner_id=@owner_id
      and ((t_partners.country_id is null) or (t_partners.country_id = @rus_id))
      and tb_contracts.dogexec_begin_date <= @date_de
      and (tb_contracts.dogexec_end_date >= @date_de or tb_contracts.dogexec_end_date is null)
      and not exists ( select 1
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv, tb_contracts
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_contracts.id)

insert into #report (n_str, naimen, qty)
  select 5, ' - в том числе нерезидентов', count(tb_contracts.id)
    from tb_contracts ,td_depo_docs,tb_contract_types,t_partners
    where tb_contract_types.id=tb_contracts.dog_type_id
      and tb_contract_types.contract_class_id in (1, 2)
--      and tb_contract_types.code in ('2','3')
      and .ITEMS_EXISTS_BY_TYPE_STATE(tb_contracts.id,'CLIENT_CONTRACTS','SIGN')
      and (.ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_FIS') or .ITEMS_EXISTS_BY_TYPE(tb_contracts.client_id,'PARTNERS_JUR'))
      and .ITEMS_EXISTS(tb_contracts.client_id)
      and t_partners.id=tb_contracts.client_id
      and t_partners.country_id is not null
      and t_partners.country_id <> @rus_id
      and td_depo_docs.id=tb_contracts.id
      and td_depo_docs.owner_id=@owner_id
      and tb_contracts.dogexec_begin_date <= @date_de
      and (tb_contracts.dogexec_end_date >= @date_de or tb_contracts.dogexec_end_date is null)
      and not exists ( select 1
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv, tb_contracts
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_contracts.id)

select * from #report

commit tran
.END