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

declare @cur_operday_id numeric
declare @cur_operday_date datetime

select @cur_operday_date = (select t_operday.date_oper_day
   from t_users, t_operday , t_items, t_states
  where  t_users.login_name = suser_name()
    and t_users.id =t_items.id
    and t_items.state_id = t_states.id
    and t_states.class_id = 0
    and t_users.operday_id = t_operday.id)

if @cur_operday_date is null
begin
  rollback tran
  raiserror 40000 'Не установлен операционный день для пользователя'
  return
end

if @in_date is null
  select @in_date = @cur_operday_date

if (@cur_operday_date != @in_date) or (@cur_operday_date is null)
begin

 select @cur_operday_id = ( select id from  t_operday
   where date_oper_day = convert(datetime, convert(varchar(10), @in_date, 103), 103))

 if @cur_operday_id is null
 begin
  select @cur_operday_date = convert(datetime, convert(varchar(10), @in_date, 103), 103)
  -- TYPE_INIT N=9 , TYPE=operday

declare @id9_1 numeric
declare @new_state_id9_1 numeric
declare @type_id9_1 numeric

declare @date_oper_day9_1 datetime
 declare @item_name9_1 varchar(255)
 declare @item_remark9_1 varchar(255)
if @date_oper_day9_1 is null
begin
select @date_oper_day9_1=convert(datetime,convert(varchar,getdate(),3),3)
end

  select @date_oper_day9_1 =@cur_operday_date

     exec ap_getid @id9_1 out
   if @@error!=0 begin
      rollback tran
      return
   end

select @item_name9_1 = 'Опер. день ' + convert(varchar,@date_oper_day9_1,103)
if @item_name9_1 is null select @item_name9_1 = 'Не задан'
select @item_remark9_1 = ''
select @type_id9_1=11000000000002427
select @new_state_id9_1 = id from t_states where type_id=@type_id9_1
and rtrim(ltrim(UPPER(state_code))) = 'OPEN'
   insert t_items (
    id,
    type_id,
    state_id,
   item_name,
   item_remark
    )
   values (
    @id9_1,
    @type_id9_1,
    @new_state_id9_1,
    @item_name9_1,
    @item_remark9_1
    )
   if @@error!=0  or @@rowcount =0 begin
      rollback tran
     raiserror 40000 'Вставка шапки документа 9_1 не прошла'
      return
   end

if @id9_1 is null 
begin
  rollback tran
  raiserror 40000 'Не задано поле идентификатора документа @id.'
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - start
--


insert t_operday(
  id,
  date_oper_day
  )
values(
  @id9_1,
  @date_oper_day9_1
)
if @@rowcount =0 begin
  rollback tran
  raiserror 40000 'Вставка в таблицу документа t_operday не прошла'
  return
end

if @@error!=0 begin
  rollback tran
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - end
--


  select @cur_operday_id = @id9_1

 end

 update t_users
 set  operday_id =  @cur_operday_id
 where login_name = suser_name()
end

if @payer_id is null
select  @pay_sum = 0

if @oprType = 'АП'
begin

if @t_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Раздел приемник не задан'
  return
end

select @type_id = (select id
from t_types
where upper(type_code)=upper('d_stock_in'))
select @keep_subacc_id= (select act_subacc_id
  from td_depo_subacc
  where id = @t_subacc_id)

if  @keep_subacc_id is null
  select @keep_subacc_id= (select acc_branch_id
  from td_stock_emis
  where id=@stock_id)
if @keep_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Не задан активный раздел'
  return
end


if @keep_subacc_id is null
select  @keep_subacc_id = td_depo_subacc.id from td_depo_subacc,t_items,t_states
where upper(td_depo_subacc.depo_subacc_no) = upper('1OB01' )
and td_depo_subacc.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0
and t_items.type_id = @subacc_doctype_id

if @keep_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Активный раздел не задан'
  return
end


select @s_subacc_id = @keep_subacc_id

end
else if @oprType = 'ПА'
begin

if @s_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Раздел поставщик не задан'
  return
end

select @type_id = (select id
from t_types
where upper(type_code)=upper('d_stock_out'))
select @keep_subacc_id= (select act_subacc_id
  from td_depo_subacc
  where id = @s_subacc_id)

if  @keep_subacc_id is null
  select @keep_subacc_id= (select acc_branch_id
  from td_stock_emis
  where id=@stock_id)
if @keep_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Не задан активный раздел'
  return
end

if @keep_subacc_id is null
select  @keep_subacc_id = td_depo_subacc.id from td_depo_subacc,t_items,t_states
where upper(td_depo_subacc.depo_subacc_no) = upper('1OB01' )
and td_depo_subacc.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0
and t_items.type_id = @subacc_doctype_id

if @keep_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Активный раздел не задан'
  return
end


select @t_subacc_id = @keep_subacc_id

end
else if @oprType = 'ПП'
begin
select @type_id = (select id
from t_types
where upper(type_code)=upper('stock_move'))
if @s_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Раздел поставщик не задан'
  return
end
if @t_subacc_id is null
begin
  rollback tran
  raiserror 40000 'Раздел приемник не задан'
  return
end
end
else
begin
commit tran
return
end

select @in_no =  isnull(@oprpref,'') +  replicate( '0' , 6  -  char_length( @in_no  )) + @in_no

select @in_no  = rtrim(@in_no)

if @in_date is null
begin
select @in_date=(select t_operday.date_oper_day
   from t_users, t_operday , t_items, t_states
  where  t_users.login_name = suser_name()
    and t_users.id =t_items.id
    and t_items.state_id = t_states.id
    and t_states.class_id = 0
    and t_users.operday_id = t_operday.id)
end
if @depo_doc_type is null
begin
select @depo_doc_type=1
end
if @sum_cur_id is null
begin
select @sum_cur_id=(select c.id
  from t_currencies c ,t_items,t_states
  where upper(c.cur_code) = upper("RUR")
  and c.id=t_items.id
  and t_items.state_id=t_states.id
  and t_states.class_id = 0)
end


if @sum_cur_id_i = '1'
select @sum_cur_id = (select c.id
  from t_currencies c ,t_items,t_states
  where upper(c.cur_code) = upper('RUR')
  and c.id=t_items.id
  and t_items.state_id=t_states.id
  and t_states.class_id = 0)
else if @sum_cur_id_i = '2'
select @sum_cur_id = (select c.id
  from t_currencies c ,t_items,t_states
  where upper(c.cur_code) = upper('USD')
  and c.id=t_items.id
  and t_items.state_id=t_states.id
  and t_states.class_id = 0)

if @stock_id is null
begin
  rollback tran
  raiserror 40000 'Не задана ЦБ'
  return
end

if  @stock_qty is null
begin
  rollback tran
  raiserror 40000 'Не задано количество бумаг '
  return
end

if  @stock_qty <= 0
begin
  rollback tran
  raiserror 40000 'Количество бумаг не может быть отрицательным или 0'
  return
end

if @in_no is null or ltrim(@in_no) = ''
begin
select @in_no = 'не задан'
end


/*
.TEST_UNIQUE
*/

/* Foundation*/
  declare @doc_date datetime
  if @doc_date_d ='' select @doc_date_d =null
  select @doc_date=convert(datetime,@doc_date_d,3)

  declare @exec_date datetime
  if @exec_date_d ='' select @exec_date_d =null
  select @exec_date=convert(datetime,@exec_date_d,3)


  declare @doc_found_type_id numeric

  select @doc_found_type_id= convert(numeric,@doc_found_type_id_i)

  if @doc_found_type_id = 0 select @doc_found_type_id = null

/* Foundation */
-- TYPE_INIT N=1 , TYPE=foundation_docs

declare @id1_1 numeric
declare @new_state_id1_1 numeric
declare @type_id1_1 numeric

declare @doc_number1_1 varchar(100)
declare @doc_date1_1 datetime
declare @doc_comment1_1 varchar(255)
declare @exec_date1_1 datetime
declare @bs_register_id1_1 numeric(18, 0)
declare @doc_found_type_id1_1 numeric(18, 0)
declare @exec_no1_1 varchar(100)
declare @depo_doc_id1_1 numeric(18, 0)
declare @function_id1_1 numeric(18, 0)
 declare @item_name1_1 varchar(255)
 declare @item_remark1_1 varchar(255)
if @function_id1_1 is null
begin
select @function_id1_1=1
end

select @doc_number1_1 =@doc_number

select @doc_date1_1 =@doc_date

select @exec_date1_1 =@exec_date

select @exec_no1_1 =@exec_no

select @function_id1_1 =1

select @depo_doc_id1_1 =@id

select @doc_comment1_1 =@doc_comment

select @doc_found_type_id1_1 =@doc_found_type_id

   exec ap_getid @id1_1 out
   if @@error!=0 begin
      rollback tran
      return
   end

select @item_name1_1 = "Док осн. "+  (select simple_name from t_simples where type_id = 4013 and id = @doc_found_type_id1_1) + " N " + @doc_number1_1 + " от " + convert(varchar,@doc_date1_1,104)
if @item_name1_1 is null select @item_name1_1 = 'Не задан'
select @item_remark1_1 = ''
select @type_id1_1=10550000000001087
select @new_state_id1_1 = id from t_states where type_id=@type_id1_1
and rtrim(ltrim(UPPER(state_code))) = 'CRT'
   insert t_items (
    id,
    type_id,
    state_id,
   item_name,
   item_remark
    )
   values (
    @id1_1,
    @type_id1_1,
    @new_state_id1_1,
    @item_name1_1,
    @item_remark1_1
    )
   if @@error!=0  or @@rowcount =0 begin
      rollback tran
     raiserror 40000 'Вставка шапки документа 1_1 не прошла'
      return
   end

if @id1_1 is null 
begin
  rollback tran
  raiserror 40000 'Не задано поле идентификатора документа @id.'
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - start
--


insert td_foundation_docs(
  id,
  doc_number,
  doc_date,
  doc_comment,
  exec_date,
  bs_register_id,
  doc_found_type_id,
  exec_no,
  depo_doc_id,
  function_id
  )
values(
  @id1_1,
  @doc_number1_1,
  @doc_date1_1,
  @doc_comment1_1,
  @exec_date1_1,
  @bs_register_id1_1,
  @doc_found_type_id1_1,
  @exec_no1_1,
  @depo_doc_id1_1,
  @function_id1_1
)
if @@rowcount =0 begin
  rollback tran
  raiserror 40000 'Вставка в таблицу документа td_foundation_docs не прошла'
  return
end

if @@error!=0 begin
  rollback tran
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - end
--


/* End Foundation */

select @foundation_remark =
isnull( (select simple_name from t_simples where type_id = 1005 and id = @doc_found_type_id1_1) , '') +
isnull (' '+@doc_number1_1,' ') + ' от ' + convert(varchar,@doc_date1_1,3)
/* + '(Основание ' + .SIMPLE_NAME(4021,@function_id1_1) +')' */

declare @item_name varchar(255)
declare @item_remark varchar(255)
declare @id numeric
declare @new_state_id numeric
declare @type_id numeric
   exec ap_getid @id out
   if @@error!=0 begin
      rollback tran
      return
   end

select @item_name = (case (select type_code from t_types where id= @type_id)
    when 'd_deblock' then
       (case (select t_types.type_code from t_items,t_types
where t_items.id = (select foundation_id from td_depo_docs where id=@id) and t_items.type_id = t_types.id)
           when 'd_block_stock' then 'Снятие блокирования ценной бумаги'
           when 'd_block_emis' then 'Снятие блокирования выпуска ценных бумаг'
           when 'd_block_acc' then 'Снятие блокирования счета ДЕПО'
           when 'd_block_subacc' then 'Снятие блокирования раздела счета ДЕПО'
        end)
    when 'd_block_stock' then 'Блокирование ценной бумаги'
    when 'd_block_emis' then 'Блокирование выпуска ценных бумаг'
    when 'd_block_acc' then 'Блокирование счета ДЕПО'
    when 'd_block_subacc' then 'Блокирование раздела счета ДЕПО'
    else (select type_name from t_types where id= @type_id)
 end) + ' № ' + @in_no+' от '+convert(varchar,@in_date,104)
if @item_name is null select @item_name='Не задан'

select @item_remark=convert(varchar, convert(numeric(18,0), @stock_qty)) + ' ' + isnull((select item_name from t_items where id= @stock_id),'') + ' '+ 
isnull((select item_name from t_items where id= @s_subacc_id),'') + '->' + isnull((select item_name from t_items where id= @t_subacc_id),'') + ' ' +
isnull(@move_contragent_name, '') + ' ' + isnull(@contragent_comment, '')


select @new_state_id = state_id from t_actions where id = @action_id
if @new_state_id is null
  select @new_state_id = @state_id
   insert t_items (
    id,
    type_id,
    state_id,
   item_name,
   item_remark
    )
   values (
    @id,
    @type_id,
    @new_state_id,
    @item_name,
    @item_remark
    )
   if @@rowcount =0 begin
      rollback tran
      raiserror 40000 'Вставка шапки документа  не прошла'
      return
   end
   if @@error!=0 begin
      rollback tran
      return
   end


if @id is null 
begin
  rollback tran
  raiserror 40000 'Не задано поле идентификатора документа @id.'
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - start
--


insert td_depo_docs(
  id,
  out_date,
  in_date,
  who_get,
  who_put,
  out_no,
  in_no,
  correspondent_id,
  payer_id,
  pay_sum,
  initiator_id,
  document_form_id,
  depo_doc_type,
  class_id,
  folder_id,
  sum_cur_id,
  doc_comment,
  foundation_remark
  )
values(
  @id,
  @out_date,
  @in_date,
  @who_get,
  @who_put,
  @out_no,
  @in_no,
  @correspondent_id,
  @payer_id,
  @pay_sum,
  @initiator_id,
  @document_form_id,
  @depo_doc_type,
  @class_id,
  @folder_id,
  @sum_cur_id,
  @doc_comment,
  @foundation_remark
)
if @@rowcount =0 begin
  rollback tran
  raiserror 40000 'Вставка в таблицу документа td_depo_docs не прошла'
  return
end

if @@error!=0 begin
  rollback tran
  return
end


insert td_stock_moves(
  id,
  stock_id,
  stock_qty,
  keep_subacc_id,
  t_subacc_id,
  s_subacc_id,
  move_comment,
  contragent_comment,
  reestr_oper_date,
  depocorr_oper_date,
  stock_price,
  stock_price_cur_id
  )
values(
  @id,
  @stock_id,
  @stock_qty,
  @keep_subacc_id,
  @t_subacc_id,
  @s_subacc_id,
  @move_comment,
  @contragent_comment,
  @reestr_oper_date,
  @depocorr_oper_date,
  @stock_price,
  @stock_price_cur_id
)
if @@rowcount =0 begin
  rollback tran
  raiserror 40000 'Вставка в таблицу документа td_stock_moves не прошла'
  return
end

if @@error!=0 begin
  rollback tran
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - end
--



/*Foundation */
update td_foundation_docs
set depo_doc_id = @id
where id = @id1_1
/* End Foundation */


 select @new_state_id = (select id from t_states where type_id =  @type_id
and  rtrim(ltrim(upper(state_code))) = rtrim(ltrim(upper(  'ED'  ))))

/*
  Начало изменения статуса
*/
if @new_state_id is null
select @new_state_id = (select state_id from t_actions where id=@action_id)

if @new_state_id is null
  select @new_state_id=@state_id

if @new_state_id!=@state_id
begin
update t_items
   set state_id = @new_state_id
 where id=@id
end
/*
  Конец изменения статуса
*/
if @oprType = 'АП'
begin
delete t_item2relation 
    from t_item2relation,t_relation 
    where t_item2relation.rel_id=t_relation.id 
    and (t_relation.class_id =2)  
    and t_item2relation.s_item_id = @id 



end
else if @oprType = 'ПА'
begin
delete t_item2relation 
    from t_item2relation,t_relation 
    where t_item2relation.rel_id=t_relation.id 
    and (t_relation.class_id =2)  
    and t_item2relation.s_item_id = @id 



end
else if @oprType = 'ПП'
begin
delete t_item2relation 
    from t_item2relation,t_relation 
    where t_item2relation.rel_id=t_relation.id 
    and (t_relation.class_id =2)  
    and t_item2relation.s_item_id = @id 



end

--
-- Начало записи протокола
--
declare @action_history_id numeric

if @action_history_id is null begin
   exec ap_getid @action_history_id out

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

   insert t_actions_history(id,action_date,doc_id,action_id,src_id,dst_id,user_id,mes_id)
   values(@action_history_id,getdate(),@id,@action_id,@state_id,@new_state_id,@user_id,null)
end
else begin
   update t_actions_history
      set action_date=getdate(),
          doc_id=@id,
          action_id=@action_id,
          src_id=@state_id,
          dst_id=@new_state_id,
          user_id=@user_id,
          mes_id=null
    where id=@action_history_id
end

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


/* добавление в сводное поручение */

  declare @joint_order_id numeric
  if @joint_order_id_s ='' select @joint_order_id_s =null
  select @joint_order_id=convert(numeric,@joint_order_id_s)

-- TYPE_INIT N=2 , TYPE=PARTAD_JOINT_ORDER_LIST

declare @id2_1 numeric
declare @new_state_id2_1 numeric
declare @type_id2_1 numeric

declare @j_order_id2_1 numeric(18, 0)
declare @order_id2_1 numeric(18, 0)
declare @order_type_id2_1 numeric(18, 0)
declare @order_no2_1 varchar(50)
declare @order_date2_1 datetime
 declare @item_name2_1 varchar(255)
 declare @item_remark2_1 varchar(255)

select @j_order_id2_1 =@joint_order_id

select @order_id2_1 =@id

select @order_type_id2_1 =@type_id

select @order_no2_1 =@in_no

select @order_date2_1 =@in_date

   exec ap_getid @id2_1 out
   if @@error!=0 begin
      rollback tran
      return
   end

select @item_name2_1 = 'Не задан'
if @item_name2_1 is null select @item_name2_1 = 'Не задан'
select @item_remark2_1 = ''
select @type_id2_1=61005000000316512
select @new_state_id2_1 = id from t_states where type_id=@type_id2_1
and rtrim(ltrim(UPPER(state_code))) = 'AD'
   insert t_items (
    id,
    type_id,
    state_id,
   item_name,
   item_remark
    )
   values (
    @id2_1,
    @type_id2_1,
    @new_state_id2_1,
    @item_name2_1,
    @item_remark2_1
    )
   if @@error!=0  or @@rowcount =0 begin
      rollback tran
     raiserror 40000 'Вставка шапки документа 2_1 не прошла'
      return
   end

if @id2_1 is null 
begin
  rollback tran
  raiserror 40000 'Не задано поле идентификатора документа @id.'
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - start
--


insert td_partad_j_order_list(
  id,
  j_order_id,
  order_id,
  order_type_id,
  order_no,
  order_date
  )
values(
  @id2_1,
  @j_order_id2_1,
  @order_id2_1,
  @order_type_id2_1,
  @order_no2_1,
  @order_date2_1
)
if @@rowcount =0 begin
  rollback tran
  raiserror 40000 'Вставка в таблицу документа td_partad_j_order_list не прошла'
  return
end

if @@error!=0 begin
  rollback tran
  return
end


--
-- .INSERT(%TABLE_NAME%,%PAGE_NO%) - end
--



/* END сводное поручение */


commit tran
select sid = convert(varchar, @id)

go

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