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

create procedure dbo.ap_10000000000030586 @sid varchar(30) = null
   ,
@out_date_db varchar(30) = null,
@out_date_de varchar(30) = null,
@in_date_db varchar(30) = null,
@in_date_de varchar(30) = null,
@out_no varchar(50) = null,
@in_no varchar(50) = null,
@correspondent_id_s varchar(30) = null,
@payer_id_s varchar(30) = null,
@pay_sum_b money = null,
@pay_sum_e money = null,
@initiator_id_s varchar(30) = null,
@depo_doc_type int = null,
@folder_id_s varchar(30) = null,
@sum_cur_id_s varchar(30) = null,
@arch_date_db varchar(30) = null,
@arch_date_de varchar(30) = null,
@arch_no varchar(50) = null,
-- For doc groups only
  @type_id_s varchar(30) = null,
  @state_name_id_s varchar(30) = null,
  @item_remark varchar(255) = null
-- End For doc groups only  

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

begin

  select @action_id = 10000000000030586

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

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

-- For doc groups only  
          declare @type_id_bb numeric,
          @type_id_ee numeric,
          @type_id_nn numeric
  if (@type_id_s is NULL) or (@type_id_s = '')
  begin 
 select @type_id_bb = -999999999999999998
  select @type_id_ee = 999999999999999999 
  end
  else
  begin 
  select @type_id_bb =convert(numeric,@type_id_s) 
  select @type_id_ee =convert(numeric,@type_id_s) 
  end  
  select @type_id_nn =convert(numeric,@type_id_s)

          declare @state_name_id_bb numeric,
          @state_name_id_ee numeric,
          @state_name_id_nn numeric
  if (@state_name_id_s is NULL) or (@state_name_id_s = '') 
  begin 
 select @state_name_id_bb = -999999999999999998
  select @state_name_id_ee = 999999999999999999 
  end
  else
  begin 
  select @state_name_id_bb =convert(numeric,@state_name_id_s) 
  select @state_name_id_ee =convert(numeric,@state_name_id_s) 
  end  
  select @state_name_id_nn =convert(numeric,@state_name_id_s) 
-- End for doc groups only
select @type_id=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
truncate table #state_list 

   insert #state_list( state_id )
   select distinct t_state2action2role.state_id
   from t_state2action2role, t_relation,t_user2role,t_states
   where t_user2role.user_id=@user_id
     and t_relation.s_type_id=@type_id
     and t_relation.class_id=5
     and t_state2action2role.action_id=t_relation.action_id
     and t_state2action2role.role_id=t_user2role.role_id
     and ((t_relation.t_type_id between @type_id_bb and @type_id_ee) or (t_relation.t_type_id = @type_id_nn))
     and t_state2action2role.state_id = t_states.id
     and ((t_states.state_id between @state_name_id_bb and @state_name_id_ee) or (t_states.state_id = @state_name_id_nn))

   insert #state_list( state_id )
   select distinct t_state2action2role.state_id
   from t_actions, t_state2action2role, t_relation,t_user2role,t_states
   where t_user2role.user_id=@user_id
     and t_relation.s_type_id=@type_id
     and t_relation.class_id=5
     and t_relation.action_id = t_actions.id
     and t_actions.vaction_id = t_state2action2role.action_id 
     and t_state2action2role.role_id=t_user2role.role_id
     and ((t_relation.t_type_id between @type_id_bb and @type_id_ee) or (t_relation.t_type_id = @type_id_nn))
     and t_state2action2role.state_id = t_states.id
     and ((t_states.state_id between @state_name_id_bb and @state_name_id_ee) or (t_states.state_id = @state_name_id_nn))
  and t_state2action2role.state_id not in (select state_id from #state_list)


truncate table #folder_list 
insert #folder_list(folder_id)
select distinct f.folder_id from t_folders2roles f,t_roles,t_user2role
  where t_user2role.user_id = @user_id
  and t_user2role.role_id = t_roles.id
  and f.role_id = t_roles.id




declare @out_date_nn datetime
declare @out_date_bb datetime
declare @out_date_ee datetime
if @out_date_db is NULL or @out_date_db='' begin
   select @out_date_nn = null
   select @out_date_bb = convert(datetime,'1/1/1753',103)
end
else begin
   select @out_date_nn = convert(datetime,@out_date_db,3)
   select @out_date_bb = @out_date_nn
   select @out_date_bb = convert(datetime,convert(varchar(30),@out_date_bb,3),3)
end
if @out_date_de is NULL or @out_date_de = '' begin
   select @out_date_nn = null
   select @out_date_ee = convert(datetime,'31/12/9999',103)
end
else begin
   select @out_date_nn = convert(datetime,@out_date_de,3)
   select @out_date_ee = @out_date_nn
   select @out_date_ee = dateadd(ms,-10,dateadd(day,1,convert(datetime,convert(varchar(30),@out_date_ee,3),3)))
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 @in_no_nn varchar(50)
if @in_no is NULL begin
   select @in_no_nn = ''
end
else begin
   select @in_no_nn = @in_no
end

declare @correspondent_id_nn numeric(18,0)
declare @correspondent_id_bb numeric(18,0)
declare @correspondent_id_ee numeric(18,0)
if @correspondent_id_s is NULL or @correspondent_id_s='' begin
   select @correspondent_id_nn = null
   select @correspondent_id_bb = -999999999999999998
   select @correspondent_id_ee = 999999999999999999
end
else begin
   select @correspondent_id_nn = convert(numeric,@correspondent_id_s)
   select @correspondent_id_bb = @correspondent_id_nn
   select @correspondent_id_ee = @correspondent_id_nn
end

declare @payer_id_nn numeric(18,0)
declare @payer_id_bb numeric(18,0)
declare @payer_id_ee numeric(18,0)
if @payer_id_s is NULL or @payer_id_s='' begin
   select @payer_id_nn = null
   select @payer_id_bb = -999999999999999998
   select @payer_id_ee = 999999999999999999
end
else begin
   select @payer_id_nn = convert(numeric,@payer_id_s)
   select @payer_id_bb = @payer_id_nn
   select @payer_id_ee = @payer_id_nn
end

declare @pay_sum_nn money
declare @pay_sum_bb money
declare @pay_sum_ee money
if @pay_sum_b is NULL begin
   select @pay_sum_nn = null
   select @pay_sum_bb = -922337203685477
end
else begin
   select @pay_sum_nn = @pay_sum_b
   select @pay_sum_bb = @pay_sum_nn
end
if @pay_sum_e is NULL begin
   select @pay_sum_nn = null
   select @pay_sum_ee = 922337203685477
end
else begin
   select @pay_sum_nn = @pay_sum_e
   select @pay_sum_ee = @pay_sum_nn
end

declare @initiator_id_nn numeric(18,0)
declare @initiator_id_bb numeric(18,0)
declare @initiator_id_ee numeric(18,0)
if @initiator_id_s is NULL or @initiator_id_s='' begin
   select @initiator_id_nn = null
   select @initiator_id_bb = -999999999999999998
   select @initiator_id_ee = 999999999999999999
end
else begin
   select @initiator_id_nn = convert(numeric,@initiator_id_s)
   select @initiator_id_bb = @initiator_id_nn
   select @initiator_id_ee = @initiator_id_nn
end

declare @depo_doc_type_nn int
declare @depo_doc_type_bb int
declare @depo_doc_type_ee int
if @depo_doc_type is NULL begin
   select @depo_doc_type_nn = null
   select @depo_doc_type_bb = -2147483648
   select @depo_doc_type_ee = 2147483647
end
else begin
   select @depo_doc_type_nn = @depo_doc_type
   select @depo_doc_type_bb = @depo_doc_type_nn
   select @depo_doc_type_ee = @depo_doc_type_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 @sum_cur_id_nn numeric(18,0)
declare @sum_cur_id_bb numeric(18,0)
declare @sum_cur_id_ee numeric(18,0)
if @sum_cur_id_s is NULL or @sum_cur_id_s='' begin
   select @sum_cur_id_nn = null
   select @sum_cur_id_bb = -999999999999999998
   select @sum_cur_id_ee = 999999999999999999
end
else begin
   select @sum_cur_id_nn = convert(numeric,@sum_cur_id_s)
   select @sum_cur_id_bb = @sum_cur_id_nn
   select @sum_cur_id_ee = @sum_cur_id_nn
end

declare @arch_date_nn datetime
declare @arch_date_bb datetime
declare @arch_date_ee datetime
if @arch_date_db is NULL or @arch_date_db='' begin
   select @arch_date_nn = null
   select @arch_date_bb = convert(datetime,'1/1/1753',103)
end
else begin
   select @arch_date_nn = convert(datetime,@arch_date_db,3)
   select @arch_date_bb = @arch_date_nn
   select @arch_date_bb = convert(datetime,convert(varchar(30),@arch_date_bb,3),3)
end
if @arch_date_de is NULL or @arch_date_de = '' begin
   select @arch_date_nn = null
   select @arch_date_ee = convert(datetime,'31/12/9999',103)
end
else begin
   select @arch_date_nn = convert(datetime,@arch_date_de,3)
   select @arch_date_ee = @arch_date_nn
   select @arch_date_ee = dateadd(ms,-10,dateadd(day,1,convert(datetime,convert(varchar(30),@arch_date_ee,3),3)))
end

declare @arch_no_nn varchar(50)
if @arch_no is NULL begin
   select @arch_no_nn = ''
end
else begin
   select @arch_no_nn = @arch_no
end


--set forceplan on

declare @id numeric
select @id=convert(numeric,@sid)


 
  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 ,
  t_items.item_remark ,
  state_name=(select t_states.state_name from t_states where t_states.id=t_items.state_id),
  t_types.type_name /*type_name=(select t_types.type_name from t_types where t_types.id=t_items.type_id)*/,
  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,
  td_depo_docs.arch_date as arch_date_d,
  td_depo_docs.arch_no,
r1861581670_10000000000030547.partner_short_name as payer_name,
r1813581499_10000000000030552.item_name as folder_name,
r1714105147_10000000000030553.cur_code as cur_code

--.VIEW_CALC

from
  #state_list,
  t_items,t_types  ,
 td_depo_docs ,
 t_partners r1861581670_10000000000030547 ,
 t_items r1813581499_10000000000030552 ,
 t_currencies r1714105147_10000000000030553 
where   
 td_depo_docs.payer_id *= r1861581670_10000000000030547.id
and 
   td_depo_docs.folder_id *= r1813581499_10000000000030552.id
and 
   td_depo_docs.sum_cur_id *= r1714105147_10000000000030553.id
 and (td_depo_docs.folder_id is null or exists(select 1 from #folders_list where folder_id=td_depo_docs.folder_id))

  and (td_depo_docs.out_date between @out_date_bb and @out_date_ee
       or @out_date_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 isnull(rtrim(ltrim(upper(td_depo_docs.in_no))),'') LIKE isnull('%'+rtrim(ltrim(upper(@in_no_nn)))+'%','%')
  and (td_depo_docs.correspondent_id between @correspondent_id_bb and @correspondent_id_ee
       or @correspondent_id_nn is null
      )
  and (td_depo_docs.payer_id between @payer_id_bb and @payer_id_ee
       or @payer_id_nn is null
      )
  and (td_depo_docs.pay_sum between @pay_sum_bb and @pay_sum_ee
       or @pay_sum_nn is null
      )
  and (td_depo_docs.initiator_id between @initiator_id_bb and @initiator_id_ee
       or @initiator_id_nn is null
      )
  and (td_depo_docs.depo_doc_type between @depo_doc_type_bb and @depo_doc_type_ee
       or @depo_doc_type_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.sum_cur_id between @sum_cur_id_bb and @sum_cur_id_ee
       or @sum_cur_id_nn is null
      )
  and (td_depo_docs.arch_date between @arch_date_bb and @arch_date_ee
       or @arch_date_nn is null
      )
  and isnull(rtrim(ltrim(upper(td_depo_docs.arch_no))),'') LIKE isnull('%'+rtrim(ltrim(upper(@arch_no_nn)))+'%','%')

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 = td_depo_docs.id  and
  t_items.type_id=t_types.id 


--set forceplan off

end

go

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