if exists (select 1
  from sysobjects
  where name = 'ap_10000000000034746'
  and type = 'P')
  drop procedure dbo.ap_10000000000034746
go

create procedure dbo.ap_10000000000034746 @sid varchar(30) = null
   ,
@dog_type_id_s varchar(30) = null,
@client_id_s varchar(30) = null,
@dogexec_begin_date_db varchar(30) = null,
@dogexec_begin_date_de varchar(30) = null,
@dogexec_end_date_db varchar(30) = null,
@dogexec_end_date_de varchar(30) = null,
@contr_code varchar(50) = null,
@contr_tax_type_id_s varchar(30) = null,
@in_date_db varchar(30) = null,
@in_date_de varchar(30) = null,
@out_no varchar(50) = null,
@document_form_id_s varchar(30) = null,
@folder_id_s varchar(30) = null,
@owner_id_s varchar(30) = null,
@state_name_id_s varchar(30) = null,
  @item_remark varchar(255) = null

as

begin

declare @id numeric
declare @type_id numeric
declare @state_id numeric
declare @new_state_id numeric
declare @action_id numeric
declare @action_class_id numeric
declare @action_history_id numeric
declare @user_id numeric
declare @user_state int
declare @proc_result int
--
-- Метод
--
select @action_id=10000000000034746, @type_id=type_id, @action_class_id=class_id, @new_state_id=state_id
  from t_actions
 where id=10000000000034746
--
-- Пользователь
--
select @user_id=id, @user_state=user_state from dbo.t_users where login_name=suser_sname()
--
-- Документ
--
if @sid is null
   select @id = -1
else
   select @id=convert(numeric, @sid)

if @id!=-1 select @type_id=type_id, @state_id=state_id from dbo.t_items where id=@id

if upper('N')='Y' and
  ((@user_state&0x10=0x10 and @action_class_id in (1,2,3,9))
    or
   (@user_state&0x20=0x20 and @action_class_id in (4,5))
  ) begin
--
-- Протокол действий пользователей
--
   exec ap_getid @action_history_id out

   if @@error!=0 begin
      rollback tran
      raiserror 40000 'Ошибка генерации идентификатора записи протакола'
      return
    end

    insert dbo.t_actions_history(id,start_date,action_date,doc_id,action_id,src_id,dst_id,user_id,mes_id)
    values(@action_history_id,getdate(),getdate(),@id,@action_id,@state_id,null,@user_id,null)

    if @@error!=0 begin
       rollback tran
       raiserror 40000 'Запись протокола не прошла'
       return
    end
end


--
--  Проверка пользователя (начало)
--

if @user_id is null
begin
  select @user_id=(select id from t_users where login_name=suser_sname())
  if @@error !=0 begin
  if @@trancount<>0 rollback tran
  raiserror 40000 'Невозможно установить имя пользователя. Доступ закрыт.'
  return -1
end
end

if @user_id is null
begin
  if @@trancount<>0 rollback tran
  raiserror 40000 'Невозможно применить метод не являсь зарегистрированным пользователем системы.'
  return -1
end

--
--  Проверка пользователя (окончание)
--
--
-- Список допусков к состояниям (начало)
--
select @type_id=(select type_id from t_actions where id=@action_id)
if @@error!=0 begin
   rollback tran
   return
end
if @type_id is null begin
   rollback tran
   raiserror 40000 'Невозможно применить метод к документу неизвестного типа'
   return
end

create table #state_list (
   state_id numeric
)

insert #state_list( state_id )
select t_state2action2user.state_id
  from t_state2action2user
 where t_state2action2user.action_id=@action_id
   and t_state2action2user.suser_name=suser_sname()

insert #state_list( state_id )
select t_state2action2user.state_id
  from t_actions, t_state2action2user
 where t_actions.vaction_id=@action_id
   and t_state2action2user.action_id=t_actions.id
   and t_state2action2user.suser_name=suser_sname()
   and t_state2action2user.state_id not in (select state_id from #state_list)
--
-- Список допусков к состояниям (конец)
--
--
-- Список допусков к папкам(начало)
--
create table #folders_list (
   folder_id numeric
)

insert #folders_list(folder_id)
select t_folder2user.folder_id from t_folder2user
  where t_folder2user.suser_name=suser_sname()
--
-- Список допусков к папкам (конец)
--



declare @dog_type_id_nn numeric(18,0)
declare @dog_type_id_bb numeric(18,0)
declare @dog_type_id_ee numeric(18,0)
if @dog_type_id_s is NULL or @dog_type_id_s='' begin
   select @dog_type_id_nn = null
   select @dog_type_id_bb = -999999999999999998
   select @dog_type_id_ee = 999999999999999999
end
else begin
   select @dog_type_id_nn = convert(numeric,@dog_type_id_s)
   select @dog_type_id_bb = @dog_type_id_nn
   select @dog_type_id_ee = @dog_type_id_nn
end

declare @client_id_nn numeric(18,0)
declare @client_id_bb numeric(18,0)
declare @client_id_ee numeric(18,0)
if @client_id_s is NULL or @client_id_s='' begin
   select @client_id_nn = null
   select @client_id_bb = -999999999999999998
   select @client_id_ee = 999999999999999999
end
else begin
   select @client_id_nn = convert(numeric,@client_id_s)
   select @client_id_bb = @client_id_nn
   select @client_id_ee = @client_id_nn
end

declare @dogexec_begin_date_nn datetime
declare @dogexec_begin_date_bb datetime
declare @dogexec_begin_date_ee datetime
if @dogexec_begin_date_db is NULL or @dogexec_begin_date_db='' begin
   select @dogexec_begin_date_nn = null
   select @dogexec_begin_date_bb = convert(datetime,'1/1/1753',103)
end
else begin
   select @dogexec_begin_date_nn = convert(datetime,@dogexec_begin_date_db,3)
   select @dogexec_begin_date_bb = @dogexec_begin_date_nn
   select @dogexec_begin_date_bb = convert(datetime,convert(varchar(30),@dogexec_begin_date_bb,3),3)
end
if @dogexec_begin_date_de is NULL or @dogexec_begin_date_de = '' begin
   select @dogexec_begin_date_nn = null
   select @dogexec_begin_date_ee = convert(datetime,'31/12/9999',103)
end
else begin
   select @dogexec_begin_date_nn = convert(datetime,@dogexec_begin_date_de,3)
   select @dogexec_begin_date_ee = @dogexec_begin_date_nn
   select @dogexec_begin_date_ee = dateadd(ms,-10,dateadd(day,1,convert(datetime,convert(varchar(30),@dogexec_begin_date_ee,3),3)))
end

declare @dogexec_end_date_nn datetime
declare @dogexec_end_date_bb datetime
declare @dogexec_end_date_ee datetime
if @dogexec_end_date_db is NULL or @dogexec_end_date_db='' begin
   select @dogexec_end_date_nn = null
   select @dogexec_end_date_bb = convert(datetime,'1/1/1753',103)
end
else begin
   select @dogexec_end_date_nn = convert(datetime,@dogexec_end_date_db,3)
   select @dogexec_end_date_bb = @dogexec_end_date_nn
   select @dogexec_end_date_bb = convert(datetime,convert(varchar(30),@dogexec_end_date_bb,3),3)
end
if @dogexec_end_date_de is NULL or @dogexec_end_date_de = '' begin
   select @dogexec_end_date_nn = null
   select @dogexec_end_date_ee = convert(datetime,'31/12/9999',103)
end
else begin
   select @dogexec_end_date_nn = convert(datetime,@dogexec_end_date_de,3)
   select @dogexec_end_date_ee = @dogexec_end_date_nn
   select @dogexec_end_date_ee = dateadd(ms,-10,dateadd(day,1,convert(datetime,convert(varchar(30),@dogexec_end_date_ee,3),3)))
end

declare @contr_code_nn varchar(50)
declare @contr_code_bb varchar(50)
declare @contr_code_ee varchar(50)
if @contr_code is NULL begin
   select @contr_code_nn = null
   select @contr_code_bb = ' '
   select @contr_code_ee = 'я'
end
else begin
   select @contr_code_nn = @contr_code
   select @contr_code_bb = @contr_code_nn
   select @contr_code_ee = @contr_code_nn
end

declare @contr_tax_type_id_nn numeric(18,0)
declare @contr_tax_type_id_bb numeric(18,0)
declare @contr_tax_type_id_ee numeric(18,0)
if @contr_tax_type_id_s is NULL or @contr_tax_type_id_s='' begin
   select @contr_tax_type_id_nn = null
   select @contr_tax_type_id_bb = -999999999999999998
   select @contr_tax_type_id_ee = 999999999999999999
end
else begin
   select @contr_tax_type_id_nn = convert(numeric,@contr_tax_type_id_s)
   select @contr_tax_type_id_bb = @contr_tax_type_id_nn
   select @contr_tax_type_id_ee = @contr_tax_type_id_nn
end

declare @in_date_nn datetime
declare @in_date_bb datetime
declare @in_date_ee datetime
if @in_date_db is NULL or @in_date_db='' begin
   select @in_date_nn = null
   select @in_date_bb = convert(datetime,'1/1/1753',103)
end
else begin
   select @in_date_nn = convert(datetime,@in_date_db,3)
   select @in_date_bb = @in_date_nn
   select @in_date_bb = convert(datetime,convert(varchar(30),@in_date_bb,3),3)
end
if @in_date_de is NULL or @in_date_de = '' begin
   select @in_date_nn = null
   select @in_date_ee = convert(datetime,'31/12/9999',103)
end
else begin
   select @in_date_nn = convert(datetime,@in_date_de,3)
   select @in_date_ee = @in_date_nn
   select @in_date_ee = dateadd(ms,-10,dateadd(day,1,convert(datetime,convert(varchar(30),@in_date_ee,3),3)))
end

declare @out_no_nn varchar(50)
if @out_no is NULL begin
   select @out_no_nn = ''
end
else begin
   select @out_no_nn = @out_no
end

declare @document_form_id_nn numeric(18,0)
declare @document_form_id_bb numeric(18,0)
declare @document_form_id_ee numeric(18,0)
if @document_form_id_s is NULL or @document_form_id_s='' begin
   select @document_form_id_nn = null
   select @document_form_id_bb = -999999999999999998
   select @document_form_id_ee = 999999999999999999
end
else begin
   select @document_form_id_nn = convert(numeric,@document_form_id_s)
   select @document_form_id_bb = @document_form_id_nn
   select @document_form_id_ee = @document_form_id_nn
end

declare @folder_id_nn numeric(18,0)
declare @folder_id_bb numeric(18,0)
declare @folder_id_ee numeric(18,0)
if @folder_id_s is NULL or @folder_id_s='' begin
   select @folder_id_nn = null
   select @folder_id_bb = -999999999999999998
   select @folder_id_ee = 999999999999999999
end
else begin
   select @folder_id_nn = convert(numeric,@folder_id_s)
   select @folder_id_bb = @folder_id_nn
   select @folder_id_ee = @folder_id_nn
end

declare @owner_id_nn numeric(18,0)
declare @owner_id_bb numeric(18,0)
declare @owner_id_ee numeric(18,0)
if @owner_id_s is NULL or @owner_id_s='' begin
   select @owner_id_nn = null
   select @owner_id_bb = -999999999999999998
   select @owner_id_ee = 999999999999999999
end
else begin
   select @owner_id_nn = convert(numeric,@owner_id_s)
   select @owner_id_bb = @owner_id_nn
   select @owner_id_ee = @owner_id_nn
end

declare @state_name_id_nn numeric(18,0)
declare @state_name_id_bb numeric(18,0)
declare @state_name_id_ee numeric(18,0)
if @state_name_id_s is NULL or @state_name_id_s='' begin
   select @state_name_id_nn = null
   select @state_name_id_bb = -999999999999999998
   select @state_name_id_ee = 999999999999999999
end
else begin
   select @state_name_id_nn = convert(numeric,@state_name_id_s)
   select @state_name_id_bb = @state_name_id_nn
   select @state_name_id_ee = @state_name_id_nn
end



  select 
  sid = convert(varchar, t_items.id),
  state_sid = convert(varchar, t_items.state_id),
  type_sid = convert(varchar, t_items.type_id),
  t_items.item_name ,
  state_name=(select t_states.state_name from t_states where t_states.id=t_items.state_id),
   type_name=(select t_types.type_name from t_types where t_types.id=t_items.type_id),
  convert(varchar,tb_contracts.dog_type_id) as dog_type_id_s,
  convert(varchar,tb_contracts.client_id) as client_id_s,
  tb_contracts.client_reg_no,
  convert(varchar,tb_contracts.depo_acc_id) as depo_acc_id_s,
  convert(varchar,tb_contracts.money_acc_id) as money_acc_id_s,
  convert(varchar,tb_contracts.dog_form_id) as dog_form_id_s,
  convert(varchar,tb_contracts.comission_cur_id) as comission_cur_id_s,
  convert(varchar,tb_contracts.comission_period_id) as comission_period_id_s,
  tb_contracts.dogexec_begin_date as dogexec_begin_date_d,
  tb_contracts.dogexec_end_date as dogexec_end_date_d,
  convert(varchar,tb_contracts.manager_id) as manager_id_s,
  convert(varchar,tb_contracts.dog_city_id) as dog_city_id_s,
  td_depo_docs.out_date as out_date_d,
  td_depo_docs.in_date as in_date_d,
  td_depo_docs.who_get,
  td_depo_docs.who_put,
  td_depo_docs.out_no,
  td_depo_docs.in_no,
  convert(varchar,td_depo_docs.correspondent_id) as correspondent_id_s,
  convert(varchar,td_depo_docs.payer_id) as payer_id_s,
  convert(decimal(18,4),td_depo_docs.pay_sum) as pay_sum,
  convert(varchar,td_depo_docs.initiator_id) as initiator_id_s,
  convert(varchar,td_depo_docs.document_form_id) as document_form_id_s,
  td_depo_docs.depo_doc_type,
  convert(varchar,td_depo_docs.folder_id) as folder_id_s,
  convert(varchar,td_depo_docs.sum_cur_id) as sum_cur_id_s,
  td_depo_docs.doc_comment,
  convert(varchar,td_depo_docs.owner_id) as owner_id_s,
  tb_contracts.fin_result,
  tb_contracts.contr_code,
  convert(decimal(18,4),tb_contracts.min_urm) as min_urm,
  convert(decimal(18,4),tb_contracts.ogr_urm) as ogr_urm,
  convert(decimal(18,4),tb_contracts.close_urm) as close_urm,
  convert(varchar,tb_contracts.type_close_short_id) as type_close_short_id_s,
  tb_contracts.is_margin,
  tb_contracts.bank_account_no,
  convert(varchar,tb_contracts.bank_id) as bank_id_s,
  tb_contracts.bank_account_name,
  tb_contracts.is_bank_form,
  tb_contracts.depo_acc_no,
  tb_contracts.depo_acc_name,
  convert(varchar,tb_contracts.partner_id) as partner_id_s,
  tb_contracts.is_depo_form,
  tb_contracts.is_buch_type_id,
  convert(varchar,tb_contracts.contr_tax_type_id) as contr_tax_type_id_s,
  tb_contracts.is_allow_short4money,
  tb_contracts.contract_name,
r2023274563_10000000000020686.name as name,
r1861581670_10000000000034675.partner_code as partner_code_cl,
r1861581670_10000000000034675.partner_short_name as partner_short_name_cl,
r1714105147_10000000000034680.cur_code as cur_code,
r1861581670_10000000000034701.partner_code as org_code,
r1861581670_10000000000034701.partner_short_name as org_short_name,
r1621125066_21535000000508977.simple_name as money_short_close_name

--.VIEW_CALC

from
  #state_list,
  t_items ,
 tb_contracts ,
 td_depo_docs ,
 tb_contract_types r2023274563_10000000000020686 ,
 t_partners r1861581670_10000000000034675 ,
 t_currencies r1714105147_10000000000034680 ,
 t_partners r1861581670_10000000000034701 ,
 t_simples r1621125066_21535000000508977 
where   
 tb_contracts.dog_type_id *= r2023274563_10000000000020686.id
and 
   tb_contracts.client_id *= r1861581670_10000000000034675.id
and 
   tb_contracts.comission_cur_id *= r1714105147_10000000000034680.id
and 
   td_depo_docs.owner_id *= r1861581670_10000000000034701.id
and 
   tb_contracts.is_allow_short4money *= r1621125066_21535000000508977.id
and r1621125066_21535000000508977.type_id = 6070

  and (tb_contracts.dog_type_id between @dog_type_id_bb and @dog_type_id_ee
       or @dog_type_id_nn is null
      )
  and (tb_contracts.client_id between @client_id_bb and @client_id_ee
       or @client_id_nn is null
      )
  and (tb_contracts.dogexec_begin_date between @dogexec_begin_date_bb and @dogexec_begin_date_ee
       or @dogexec_begin_date_nn is null
      )
  and (tb_contracts.dogexec_end_date between @dogexec_end_date_bb and @dogexec_end_date_ee
       or @dogexec_end_date_nn is null
      )
  and (tb_contracts.contr_code between @contr_code_bb and @contr_code_ee
       or @contr_code_nn is null
      )
  and (tb_contracts.contr_tax_type_id between @contr_tax_type_id_bb and @contr_tax_type_id_ee
       or @contr_tax_type_id_nn is null
      )
  and (td_depo_docs.in_date between @in_date_bb and @in_date_ee
       or @in_date_nn is null
      )
  and isnull(rtrim(ltrim(upper(td_depo_docs.out_no))),'') LIKE isnull('%'+rtrim(ltrim(upper(@out_no_nn)))+'%','%')
  and (td_depo_docs.document_form_id between @document_form_id_bb and @document_form_id_ee
       or @document_form_id_nn is null
      )
  and (td_depo_docs.folder_id between @folder_id_bb and @folder_id_ee
       or @folder_id_nn is null
      )
  and (td_depo_docs.owner_id between @owner_id_bb and @owner_id_ee
       or @owner_id_nn is null
      )

and (isnull(upper(ltrim(rtrim(t_items.item_remark))),'') LIKE isnull('%'+upper(ltrim(rtrim(@item_remark))) + '%','%')) 
 and #state_list.state_id = t_items.state_id 

 and  t_items.id = tb_contracts.id
 and  t_items.id = td_depo_docs.id


if @action_history_id is not null begin
   update t_actions_history
      set doc_id=@id,
          action_date=getdate()
    where id=@action_history_id
   if @@error!=0 begin
      rollback tran
      return
   end
end

end


go

grant execute on dbo.ap_10000000000034746 to PUBLIC
grant execute on dbo.ap_10000000000034746 to ADMIN
go