create procedure dbo.%PROC%
  @in_date_db varchar(30) = null,
  @in_date_de varchar(30) = null,
  @broker_id_s varchar(30) = null,
  @stock_id_s varchar(30) = null,
  @is_client int,
  @client_contract_id_s varchar(30) = null,
  @is_place int,
  @place_id_s varchar(30) = null,
  @deal_type int,
  @contr_type int

as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric
begin

--.EXIT_MESSAGE_PARM('параметры', @contr_type)

set nocount on

select @action_id = %ACTION_ID%

.CHECK_USER
--.CHECK_VIEW

declare
@date_b datetime,
@date_e datetime,
@broker_id numeric
declare @partner_id numeric
declare
  @stock_id numeric,
  @client_id numeric,
  @place_id numeric


select @broker_id=convert(numeric, @broker_id_s)
select @date_b=convert(datetime, @in_date_db, 3)
select @date_e=dateadd(day,1,convert(datetime, @in_date_de, 3))
select @partner_id = partner_id from tb_broker_change where id = @broker_id

if (@stock_id_s = '') or (@stock_id_s = ' ')
  select @stock_id = null
else
  select @stock_id = convert(numeric, @stock_id_s)

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

if (@place_id_s = '') or (@place_id_s = ' ')
  select @place_id = null
else
  select @place_id = convert(numeric, @place_id_s)

create table #report
  (id numeric,
  client_name varchar(150) null,
  stock_name varchar(150) null,
  sale_kind varchar(30) null,
  first_doc varchar(150) null,
  sale_cur varchar(10) null,
  pay_cur varchar(10) null,
  tip_sdelki varchar(30) null,
  poruch varchar(255) null,
  place_name varchar(150) null,
  saler_name varchar(150) null,
  bayer_name varchar(150) null,
  stock_id numeric(18,0) null,
  client_id numeric(18,0) null,
  place_id numeric(18,0) null,
  deal_type numeric(18,0) null,
  contr_type int null
)


declare @fdd datetime

insert #report
  (id, sale_kind, first_doc, sale_cur, pay_cur, tip_sdelki, poruch, place_name, stock_id,
  client_id, place_id, deal_type, contr_type)
select
  contr1.id,

  (case when tp.code in (54,55,56,57) then ltrim(rtrim(tp.name))
        else (select s.simple_name from t_simples s where s.type_id = 5008 and s.id = contr.ticket_type_id)
   end),

  (case when upper(t_types.type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE')
     then 'Отчет брокера за '+convert(varchar,contr1.in_date,103)
     else
     (case when tpd.fact_dog_date > contr1.in_date then
        'Тикет '+isnull(contr1.out_no,' ')+' / '+isnull(convert(varchar,contr1.out_date,103),' ')
      else
        'Договор '+isnull(contr.dogovor_name,' ')+' / '+isnull(convert(varchar,(select tpd.fact_dog_date from tb_plan2fact_dates tpd where tpd.id = contr.id),103),' ')
      end)
  end),

  (select cur_code from t_currencies where id=contr.cur_id),
  (select cur_code from t_currencies where id=contr.pay_cur_id),

  (case contr.character_id
      when 1 then 'Дилерская'
      else (case (select tbct.contract_class_id
                    from tb_contracts c1, tb_contract_types tbct
                    where c1.id = contr.client_contract_id
                      and tbct.id=c1.dog_type_id)
              when 1 then 'ДУ' else 'Собственная' end)
   end),
  (case contr.character_id when 1 then ''
     else (case (select tbct.contract_class_id
                   from tb_contracts c1, tb_contract_types tbct
                   where c1.id = contr.client_contract_id
                     and tbct.id=c1.dog_type_id)
             when 1 then (select isnull(d.out_no, ' ') + ' ' + isnull(convert(varchar,d.out_date,103),' ') from td_depo_docs d where d.id=contr.client_contract_id)
             else (case when exists (select td2.id from tb_directions td1, tb_directions td2 where td1.id = contr.direction_id and td2.id = td1.multy_direction_id)
                        then (select isnull(d.out_no,' ')+ ' ' + isnull(convert(varchar,d.out_date,103),' ') from tb_directions td1, td_depo_docs d where td1.id = contr.direction_id and d.id = td1.multy_direction_id)
                        else (select isnull(d.out_no,' ')+ ' ' + isnull(convert(varchar,d.out_date,103),' ') from td_depo_docs d where d.id = contr.direction_id)
                   end)
           end)
      end),

  (select place_name from td_rate_places where id=contr.sale_place_id),
  contr.stock_id,
  contr.client_contract_id,
  contr.sale_place_id,
  contr.character_id,
  null

from t_items, t_types, t_states, tb_baysale_docitems contr, td_depo_docs contr1,
  tb_plan2fact_dates tpd, tb_repo_params trp, t_purposes tp

where t_items.id = contr.id
  and contr1.id = contr.id
  and contr1.owner_id = @partner_id
  and t_types.id = t_items.type_id
  and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR', 'DEALINGS_VIA_BROKER_SALE', 'DOG_DEALINGS_SALE_VBR')
  and t_states.id = t_items.state_id
  and upper(t_states.state_code) in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED')
  and contr1.in_date >= @date_b
  and contr1.in_date <  dateadd(dd, 1, @date_e)
  and tpd.id = contr.id
  and trp.id = contr.id
  and tp.id = trp.repo_type_id


/*
    or (t_states.state_code in ('PODP', 'POLUCH', 'WAIT_EXEC')
          and contr1.in_date >= @date_b
          and contr1.in_date <  dateadd(dd, 1, @date_e)))
      or (t_states.state_code in ('PODT', 'OBYAZ_END') and
         ((tpd.fact_reg_date >= tpd.fact_pay_date and
           tpd.fact_reg_date > @date_e
           and contr1.in_date >= @date_b
           and contr1.in_date <  dateadd(dd, 1, @date_e))
       or (tpd.fact_reg_date < tpd.fact_pay_date and
           tpd.fact_pay_date > @date_e
           and contr1.in_date >= @date_b
           and contr1.in_date <  dateadd(dd, 1, @date_e) ) ) ) ) )
*/

------------------------------отсеить договора с классификатором 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
----------------------------------------------------------------------------------------------------

update #report set
client_name=(select t.partner_short_name from t_partners t where t.id=contr.client_id),
stock_name=(select se.stock_name from td_stock_emis se where se.id=contr.stock_id)
from tb_baysale_docitems contr
where contr.id=#report.id

if isnull(@stock_id,0) <> 0
  delete from #report where stock_id <> @stock_id

if (@is_place = 1) and (@place_id is not null)
  delete from #report where place_id <> @place_id

if @deal_type = 1
  delete from #report where deal_type <> @deal_type

if @deal_type = 2
  begin
  if (@contr_type is not null) and (@contr_type <> 0)
    delete from #report where (deal_type <> @deal_type)
                          or ((select convert(int, tct.code)
                                 from tb_baysale_docitems contr, tb_contracts tbc,
                                   tb_contract_types tct
                                 where contr.id = #report.id
                                   and tbc.id = contr.client_contract_id
                                   and tct.id = tbc.dog_type_id) <> @contr_type)
  else
    delete from #report where deal_type <> @deal_type
  end

if (@deal_type = 2) and (@is_client = 1) and (@client_id is not null)
  delete from #report where client_id <> @client_id

update #report set
  saler_name=(select p.partner_short_name from t_partners p where p.id=bsd.contragent_id),
  bayer_name=(case bsd.character_id
                 when 1 then (select t.partner_short_name from t_partners t where t.id=bsd.organisation_id)
                 when 2 then (select m.partner_short_name from t_partners m where m.id=bsd.client_id)
              end)
from t_items,t_types,tb_baysale_docitems bsd
where t_items.id=#report.id
  and t_types.id=t_items.type_id
  and upper(t_types.type_code)='DOG_DEALINGS_BAY_VBR'
  and bsd.id=#report.id

update #report set
bayer_name=(select p.partner_short_name from t_partners p where p.id=bsd.contragent_id),
saler_name=(case bsd.character_id
                 when 1 then (select t.partner_short_name from t_partners t where t.id=bsd.organisation_id)
                 when 2 then (select m.partner_short_name from t_partners m where m.id=bsd.client_id)
end)
from t_items,t_types,tb_baysale_docitems bsd
where t_items.id=#report.id
and t_types.id=t_items.type_id
and upper(t_types.type_code)='DOG_DEALINGS_SALE_VBR'
and bsd.id=#report.id


/*
update #report set
com_br=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #report.id
      and td_comis2oper.service_id = 6
      and .ITEMS_EXISTS(td_comis2oper.id)),
com_other=(select sum(td_comis2oper.comis) from td_comis2oper
    where td_comis2oper.item_id = #report.id
      and td_comis2oper.service_id <> 6
      and .ITEMS_EXISTS(td_comis2oper.id))
*/


if (@is_client = 1) and (@is_place = 1)
begin
select
  convert(varchar,contr1.in_date,103)+' '+isnull(convert(varchar,contr.in_time,108),' ') as in_date2,
  contr1.in_no,
  tb_plan2fact_dates.plan_reg_date as plan_reg_doc_date,
  tb_plan2fact_dates.fact_reg_date as fact_reg_doc_date,
  tb_plan2fact_dates.plan_pay_date as plan_pay_date,
  tb_plan2fact_dates.fact_pay_date as fact_pay_date,
  r.client_name as client_dog,
  r.stock_name as stock,
  r.sale_kind,
  r.first_doc,
  contr.stock_price,
  contr.stock_qty,
  contr.summ_doc_total+ isnull(contr.coupon_deal_sum,0),
  r.sale_cur,
  isnull(r.pay_cur, r.sale_cur),
  r.tip_sdelki,
  r.poruch,
  r.place_name as sale_place,
  r.saler_name,
  r.bayer_name,
--  r.com_br,
--  r.com_other,
  r.place_name as place_group

  from #report r,td_depo_docs contr1, tb_baysale_docitems contr,
    tb_plan2fact_dates

  where r.id=contr.id
    and contr1.id=contr.id
    and tb_plan2fact_dates.id=contr.id

  order by client_dog, sale_place, contr1.in_date, contr.in_time, contr1.in_no
end


if (@is_client = 1) and (@is_place = 0)
begin
select
  convert(varchar,contr1.in_date,103)+' '+isnull(convert(varchar,contr.in_time,108),' ') as in_date2,
  contr1.in_no,
  tb_plan2fact_dates.plan_reg_date as plan_reg_doc_date,
  tb_plan2fact_dates.fact_reg_date as fact_reg_doc_date,
  tb_plan2fact_dates.plan_pay_date as plan_pay_date,
  tb_plan2fact_dates.fact_pay_date as fact_pay_date,
  r.client_name as client_dog,
  r.stock_name as stock,
  r.sale_kind,
  r.first_doc,
  contr.stock_price,
  contr.stock_qty,
  contr.summ_doc_total+ isnull(contr.coupon_deal_sum,0),
  r.sale_cur,
  isnull(r.pay_cur, r.sale_cur),
  r.tip_sdelki,
  r.poruch,
  r.place_name as sale_place,
  r.saler_name,
  r.bayer_name,
--  r.com_br,
--  r.com_other,
  null as place_group

  from #report r,td_depo_docs contr1, tb_baysale_docitems contr,
    tb_plan2fact_dates

  where r.id=contr.id
    and contr1.id=contr.id
    and tb_plan2fact_dates.id=contr.id

  order by client_dog, contr1.in_date, contr.in_time, contr1.in_no
end

if (@is_client = 0) and (@is_place = 1)
begin
select
  convert(varchar,contr1.in_date,103)+' '+isnull(convert(varchar,contr.in_time,108),' ') as in_date2,
  contr1.in_no,
  tb_plan2fact_dates.plan_reg_date as plan_reg_doc_date,
  tb_plan2fact_dates.fact_reg_date as fact_reg_doc_date,
  tb_plan2fact_dates.plan_pay_date as plan_pay_date,
  tb_plan2fact_dates.fact_pay_date as fact_pay_date,
  r.client_name as client_dog,
  r.stock_name as stock,
  r.sale_kind,
  r.first_doc,
  contr.stock_price,
  contr.stock_qty,
  contr.summ_doc_total+ isnull(contr.coupon_deal_sum,0),
  r.sale_cur,
  isnull(r.pay_cur, r.sale_cur),
  r.tip_sdelki,
  r.poruch,
  r.place_name as sale_place,
  r.saler_name,
  r.bayer_name,
--  r.com_br,
--  r.com_other,
  r.place_name as place_group

  from #report r,td_depo_docs contr1, tb_baysale_docitems contr,
    tb_plan2fact_dates

  where r.id=contr.id
    and contr1.id=contr.id
    and tb_plan2fact_dates.id=contr.id

  order by sale_place, contr1.in_date, contr.in_time, contr1.in_no
end

if (@is_client = 0) and (@is_place = 0)
begin
select
  convert(varchar,contr1.in_date,103)+' '+isnull(convert(varchar,contr.in_time,108),' ') as in_date2,
  contr1.in_no,
  tb_plan2fact_dates.plan_reg_date as plan_reg_doc_date,
  tb_plan2fact_dates.fact_reg_date as fact_reg_doc_date,
  tb_plan2fact_dates.plan_pay_date as plan_pay_date,
  tb_plan2fact_dates.fact_pay_date as fact_pay_date,
  r.client_name as client_dog,
  r.stock_name as stock,
  r.sale_kind,
  r.first_doc,
  contr.stock_price,
  contr.stock_qty,
  contr.summ_doc_total+ isnull(contr.coupon_deal_sum,0),
  r.sale_cur,
  isnull(r.pay_cur, r.sale_cur),
  r.tip_sdelki,
  r.poruch,
  r.place_name as sale_place,
  r.saler_name,
  r.bayer_name,
--  r.com_br,
--  r.com_other,
  null as place_group

  from #report r,td_depo_docs contr1, tb_baysale_docitems contr,
    tb_plan2fact_dates

  where r.id=contr.id
    and contr1.id=contr.id
    and tb_plan2fact_dates.id=contr.id

  order by contr1.in_date, contr.in_time, contr1.in_no
end

end