create procedure dbo.%PROC%
       @in_date_db varchar(30) = null,
       @in_date_de varchar(30) = null,
       @brocker_id_s varchar(30) = null,
       @deal_type int, --добавили
       @dogovor_type int,
       @client_contract_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,
@date_e datetime,
@brocker_id numeric
declare @partner_id numeric

declare @client_id numeric

if (@client_contract_id_s = '') or (@client_contract_id_s = ' ')
  select @client_id = null
 select @client_id = convert(numeric, @client_contract_id_s)

select @brocker_id=convert(numeric, @brocker_id_s)
select @date_b=convert(datetime, @in_date_db, 3)
select @date_e=convert(datetime, @in_date_de, 3)
select @partner_id = partner_id from tb_broker_change where id = @brocker_id

create table #result(
id numeric,
in_date datetime,
in_time datetime null,
in_no varchar(100) null,
ticket_type_id_s varchar(30),
character_id_s varchar(30),
plan_doc_date datetime null,
fact_dog_date datetime null,
plan_reg_doc_date datetime null,
fact_reg_doc_date datetime null,
plan_pay_date datetime null,
fact_pay_date datetime null,
contragent varchar(255) null,
owner varchar(255) null,
client_name varchar(255) null,
stock varchar(255) null,
sale_kind varchar(255) null,
stock_price decimal(18,8) null,
stock_qty money null,
comis_brok money null,
comis_reg money null,
comis_other money null,
summ_doc_total money null,
sale_cur varchar(100) null,
pay_cur varchar(100) null,
poruch varchar(255) null,
sale_place varchar(255) null,
client_id numeric null,--добавлено
deal_type_in int null,--характер
dogovor_type_in int null) --тип договора



insert #result(
id,in_date,in_time,in_no,ticket_type_id_s,
character_id_s,plan_doc_date,fact_dog_date,
plan_reg_doc_date,fact_reg_doc_date,
plan_pay_date,fact_pay_date,contragent,
owner,client_name,stock,sale_kind,
stock_price,stock_qty,summ_doc_total,
sale_cur,pay_cur,poruch,sale_place,client_id
)
select
contr1.id,
contr1.in_date,
contr.in_time,
contr1.in_no,
convert(varchar(30),contr.ticket_type_id) as ticket_type_id_s,
convert(varchar(30),contr.character_id) as character_id_s,--тип сделки
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then null
  else tb_plan2fact_dates.plan_dog_date
end) as plan_doc_date,
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then null
  else tb_plan2fact_dates.fact_dog_date
end) as fact_dog_date,
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then contr1.in_date
  else tb_plan2fact_dates.plan_reg_date
end) as plan_reg_doc_date,
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then contr1.in_date
  else tb_plan2fact_dates.fact_reg_date
end) as fact_reg_doc_date,
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then contr1.in_date
  else tb_plan2fact_dates.plan_pay_date
end) as plan_pay_date,
(case
  when  lower(t_types.type_code) in ('dealings_via_broker','dealings_via_broker_sale') then contr1.in_date
  else tb_plan2fact_dates.fact_pay_date
end) as fact_pay_date,
(select partner_name from t_partners where id=contr.contragent_id) as contragent,
(select partner_name from t_partners where id=contr1.owner_id) owner,
(select partner_name from t_partners where id=contr.client_id
                                      and  contr.character_id=2) as client_name,
(select stock_name from td_stock_emis where id=contr.stock_id) stock,
(select simple_name from t_simples where type_id=5008 and id=contr.ticket_type_id) as sale_kind,
contr.stock_price,
contr.stock_qty,
contr.summ_doc_total,
(select cur_code from t_currencies where id=contr.cur_id) as sale_cur,
(select cur_code from t_currencies where id=contr.pay_cur_id) as pay_cur,
(select item_name from t_items where id=contr.direction_id) as poruch,
(select place_name from td_rate_places where id=contr.sale_place_id) as sale_place,
 contr.client_contract_id

from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems contr,tb_plan2fact_dates
where t_items.id=contr.id
  and contr1.in_date >=@date_b
  and contr1.in_date < dateadd(day, 1, @date_e)
  and contr1.id=contr.id
  and contr1.owner_id = @partner_id
  and t_types.id=t_items.type_id
  and t_states.id=t_items.state_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', 'POLUCH', 'CLOSED')
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED','OPL_END','POST_END')
  and contr.id *= tb_plan2fact_dates.id

update #result set
comis_brok=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 3 --поправлено
      and .ITEMS_EXISTS(td_comis2oper.id)),
comis_reg=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id = 7
      and .ITEMS_EXISTS(td_comis2oper.id)),
comis_other=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #result.id
      and td_comis2oper.service_id=1 --комиссия биржи
      and .ITEMS_EXISTS(td_comis2oper.id))

-- новое
update #result
set dogovor_type_in=0--, deal_type_in=2 --'ДУ', клиентские
             from tb_contracts c1,
                  tb_contract_types tbct,td_pay_fond,
                  t_partners t_p,
                  tb_baysale_docitems contr
             where contr.id = #result.id
              -- c1.id =#report.investor_id
               and c1.id=contr.client_contract_id
               and tbct.id=c1.dog_type_id
               and tbct.contract_class_id=1--тип договора ДУ
               and c1.client_id=t_p.id
               and (.ITEMS_EXISTS_BY_TYPE(t_p.id,'PARTNERS_JUR') or
                     .ITEMS_EXISTS_BY_TYPE(t_p.id,'PARTNERS_FIS'))
               and #result.character_id_s=2


update #result
set dogovor_type_in=1--,  deal_type_in=2 --ПИФ, клиентские
               from tb_contracts c1,
                    t_partners t_p,
                    td_pay_fond,
                    tb_baysale_docitems contr
          where contr.id = #result.id
              -- c1.id =#report.investor_id
                and c1.id=contr.client_contract_id
                and c1.client_id=t_p.id
                and t_p.is_company=1
                and td_pay_fond.id=t_p.id
                and .ITEMS_EXISTS_BY_TYPE(td_pay_fond.id,'PIF_FUND')
                and #result.character_id_s=2

if @deal_type is not null and @deal_type<>0
  delete from #result where character_id_s <> @deal_type

if @deal_type=2 and @dogovor_type is not null
  delete from #result where dogovor_type_in <> @dogovor_type



select
in_date,
in_time,
in_no,
ticket_type_id_s,
character_id_s,
plan_doc_date,
fact_dog_date,
plan_reg_doc_date,
fact_reg_doc_date,
plan_pay_date,
fact_pay_date,
contragent,
owner,
client_name,
stock,
sale_kind,
stock_price,
stock_qty,
comis_brok,
comis_reg,
comis_other,
summ_doc_total,
sale_cur,
pay_cur,
poruch,
sale_place

from #result
where ((client_id =@client_id and @client_id is not null) or @client_id is null)--добавлено
order by in_date,in_no

end