# Date: 21/07/06
# Version: 105

$DOC(IMPORT_PAYMT_EXCEL)
 name=Импорт платежей(Excel)
 class=1
 product=ALD_PIF
 name_formula=null
 remark_formula=null

 $STATE(NEW)
  name=Начальный
  class=1
 $ENDSTATE

 $FORM(D_EXCEL_PAYMT_IMPORT)
  name=Платежи
  class=5
  filter=null
  target_state=
  uo=uo_excel_paymt_import
  dw=d_excel_paymt_import
  procedure=ap_21535000001981138
  patterns=null
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=null
 $ENDFORM

 $ACTION(ACTION_INSERT)
  name=Импорт
  class=1
  form=D_EXCEL_PAYMT_IMPORT
  target_state=null
  procedure=ap_21535000001981139
  patterns=GENERIC_IMPORT
  user_define=1
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11541961)
create procedure dbo.%PROC%
  @fond varchar(255) = null                   /*ФОНД*/,
  @paymt_date varchar(30) = null              /*Дата платежа*/,
  @paymt_no varchar(255) = null               /*Номер платежа*/,
  @paymt_purpose varchar(255) = null          /*Назначение платежа*/,
  @queries_no varchar(255) = null             /*Номер заявки*/,
  @pay_owner_last_name varchar(255) = null    /*Фамилие пайщика*/,
  @pay_owner_second_name varchar(255) = null  /*Отчество пайщика*/,
  @pay_owner_name  varchar(255) = null        /*Имя пайщика*/,
  @paymt_sum money = null                     /*Сумма платежа*/
as
declare @action_id numeric,
        @id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @item_name varchar(100),
        @new_state_id numeric,
        @owner_id numeric(18,0),
        @pif_pay_id numeric(18,0),
        @partner_id numeric(18,0),
        @purpose_id numeric(18,0),
        @to_partner_id numeric(18,0),
        @to_partner_acc_id numeric(18,0),
        @partner_name varchar(255),
        @to_partner_acc_no varchar(255),
        @cur_id numeric(18,0),
        @doc_date datetime,
        @out_date datetime,
        @days int






begin tran
select @action_id = %ACTION_ID%
.CHECK_USER
.CHECK_INSERT

select @cur_id = .CUR('RUR')

select @fond = replace(@fond,' ','')
select @doc_date = convert(datetime,@paymt_date)


select @owner_id = max(i.item_id)
  from t_item2class i, t_classvalues v, t_classes c
 where rtrim(ltrim(upper(@fond))) = ltrim(upper(i.code))
   and i.value_id=v.id and v.class_id=c.id
   and upper(c.code)='FOND_CODE'
   and upper(v.code)='FOND_CODE'
   and .ITEMS_EXISTS_BY_TYPE(i.item_id,'PIF_FUND')



if @owner_id is null .EXIT_MESSAGE_PARM('Не найден фонд с кодом:',@fond)


select @partner_name = @pay_owner_last_name + ' ' + @pay_owner_second_name + ' ' + @pay_owner_name

select @partner_id = t_partners.id
         from t_partners , t_partners_fis
        where t_partners.is_company = 0
              and t_partners.id =  t_partners_fis.id
              and upper(t_partners_fis.f_last_name) = upper(rtrim(ltrim(@pay_owner_last_name)))
              and (upper(t_partners_fis.f_middle_name) = upper(rtrim(ltrim(@pay_owner_second_name))) and isnull(@pay_owner_second_name,'')!=''
                   or isnull(@pay_owner_second_name,'')='')
              and upper(t_partners_fis.f_first_name) = upper(rtrim(ltrim(@pay_owner_name)))


if @partner_id is null .EXIT_MESSAGE_PARM('НЕ найден пайщик с реквизитами',@partner_name)



select @pif_pay_id = pq.id
from tp_pif_queries pq,
      td_depo_docs dd
where pq.id=dd.id
      and dd.owner_id=@owner_id
      and pq.pay_owner_id = @partner_id
      and upper(pq.agent_regs_no) = upper(@queries_no)
      and .ITEMS_EXISTS_BY_TYPE(pq.id,'PIF_PAY_ALLOCATE')


if @pif_pay_id is null .EXIT_MESSAGE_PARM('НЕ найдена заявка с номером ',@queries_no)




select @purpose_id = t_purposes.id
  from   t_purposes
 where t_purposes.code  = 4011


select @to_partner_id = td_pay_fond.ctrl_comp_id
  from td_pay_fond
 where td_pay_fond.id = @owner_id


select @to_partner_acc_id = t_bank_accounts.id,
       @to_partner_acc_no = t_bank_accounts.bank_account_no
  from t_bank_accounts
 where t_bank_accounts.partner_id = @to_partner_id
       and t_bank_accounts.fund_id = @owner_id
       and t_bank_accounts.acc_purpose_id = 3
       and t_bank_accounts.bank_acc_cur_id = @cur_id



while len(@paymt_no) < 5
begin
select @paymt_no = '0' + @paymt_no
end


if exists(select t_paydocs.id
 from t_paydocs,  t_items , t_states , td_depo_docs
 where  rtrim(ltrim(t_paydocs.doc_number))=rtrim(ltrim(@paymt_no))
   and t_paydocs.doc_date=@doc_date
   and t_paydocs.id=td_depo_docs.id
   and td_depo_docs.owner_id=@owner_id
   and t_paydocs.doc_sum = @paymt_sum
   and t_paydocs.from_partner_id = @partner_id
   and .PURPOSE_CODE(t_paydocs.purpose_id) = 4011
   and .ITEMS_EXISTS_BY_TYPE(t_paydocs.id,'SD_PAYBANK_IN'))
 .EXIT_MESSAGE_PARM('Платеж уже есть в системе ',@paymt_no)



exec ap_calc_end_period
                 @date_beg = @paymt_date,
                 @days_beg = 2,
                 @date_end = @out_date out,
                 @days =@days



.TYPE_INIT('SD_PAYBANK_IN', 8)
     .TYPE_ASSIGN(purpose_id,@purpose_id, 8)
     .TYPE_ASSIGN(owner_id,@owner_id, 8)
     .TYPE_ASSIGN(doc_number,@paymt_no, 8)
     .TYPE_ASSIGN(doc_date,@doc_date, 8)
     .TYPE_ASSIGN(complete_date,@out_date, 8)
     .TYPE_ASSIGN(doc_sum,@paymt_sum, 8)
     .TYPE_ASSIGN(perpose_str,@paymt_purpose, 8)
     .TYPE_ASSIGN(pay_perpose,'4011', 8)
     .TYPE_ASSIGN(to_partner_id,@to_partner_id, 8)
     .TYPE_ASSIGN(from_partner_id,@partner_id, 8)
     .TYPE_ASSIGN(to_partner_acc_id,@to_partner_acc_id, 8)
     .TYPE_ASSIGN(to_partner_acc_no,@to_partner_acc_no, 8)
     .TYPE_ASSIGN(pif_pay_id,@pif_pay_id, 8)
.TYPE_INSERT('SD_PAYBANK_IN','ED', 8)




commit tran
  $ENDTEXT(11541961)
 $ENDACTION

 $STATE2ACTION(NEW.ACTION_INSERT)
  state=NEW
  action=ACTION_INSERT
  is_available=1
 $ENDSTATE2ACTION

 $STATE2ACTION(NEW.D_EXCEL_PAYMT_IMPORT)
  state=NEW
  action=D_EXCEL_PAYMT_IMPORT
  is_available=1
 $ENDSTATE2ACTION
$ENDDOC