dbo.sp_insert_data_hib

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.sp_insert_data_hib 
(@filename varchar(500), @file varchar(100))
 -- Add the parameters for the stored procedure here
 
AS
BEGIN
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
 
 Declare @dir varchar(100)
    declare @dircmd varchar(500)
 declare @filedir varchar(400)
 declare @logerror varchar(100), @logerror_dtl varchar(100), @mdate varchar(14)
    declare @tblinv_all varchar(MAX)
 DECLARE @Files table(Names varchar(250) null)
 declare @cmdmove varchar(255)
 
 
 set @mdate = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')) 
 
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\invoice\hib'
 set @dircmd='dir '+@dir+'\invoice\hib\*.TXT'
 
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @file + ' '+ @filedir +'\log\' + @file
    set @logerror =  @filedir+'\log\error' + @mdate + '.txt'
 
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 
 if (select COUNT(*) from @Files where Names like '%File Not Found%') > 0
 begin
  select '00' as status, 'Data Tidak Ditemukan' as description 
  return
 end
 
 DELETE @Files WHERE Names LIKE '%%'

 --delete all informational messages
 DELETE @Files WHERE Names LIKE ' %'

 --delete the null values
 DELETE @Files WHERE Names IS NULL

 --get rid of dateinfo
 UPDATE @Files SET Names =RIGHT(Names,(LEN(Names)-20))

 --get rid of leading spaces
 UPDATE @Files SET Names =LTRIM(Names)
 --split data into size and filename
 
 if (select COUNT(*) from @Files where Names like '%'+ @file +'%') = 0
 begin
  select '00' as status, 'Data Tidak Ditemukan' as description 
  return
 end
 
 
 set @tblinv_all= 'tblinv_all' + replace(convert(varchar(255) , newid()), '-','')
 
 BEGIN TRY  
 exec (
' create table ' + @tblinv_all + 
' ( rn int, r1 varchar(4), r2 varchar(50), r3 varchar(51), r4 varchar(50) null, r5 varchar(50) null, r6 varchar(50) null, r7 varchar(50) null, r8 varchar(50) null, r9 varchar(50) null, r10 varchar(50) null, r11 varchar(50) null, r12 varchar(50) null, r13 varchar(50) null, r14 varchar(120) null, r15 varchar(50) null, r16 varchar(50) null, r17 varchar(50) null, r18 varchar(50) null, r19 varchar(50) null, r20 varchar(50) null ) insert into ' + @tblinv_all + 
' ( rn, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r16, r17, r18, r19, r20 ) select row_number() over(order by (select 0)) as RN, F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@file+
']'') where F1 <> ''R1'' and F2 <> ''R2'' and F3 <> ''R3'' and F1 is not null and F2 is not null declare @cekudahada int, @seqno int, @seqnourut int, @r333 varchar(50) declare @inv_hdr_id_udahada bigint, @seqsama int, @cekseqno int set @seqno = 1 set @seqsama = 0 declare @inv_hdr_id bigint declare @noinv varchar(50) declare @tginv varchar(20) declare @rn int, @r1 varchar(4), @r2 varchar(50), @r3 varchar(51), @r4 varchar(50) , @r5 varchar(50) , @r6 varchar(50) , @r7 varchar(50) , @r8 varchar(50) , @r9 varchar(50) , @r10 varchar(50) , @r11 varchar(50) , @r12 varchar(50) , @r13 varchar(50) , @r14 varchar(120) , @r15 varchar(50) , @r16 varchar(50) , @r17 varchar(50) , @r18 varchar(50) , @r19 varchar(50) , @r20 varchar(50) DECLARE inv_cursor CURSOR FOR SELECT rn, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r16, r17, r18, r19, r20 from ' + @tblinv_all + 
' OPEN inv_cursor FETCH NEXT FROM inv_cursor into @rn, @r1, @r2, @r3, @r4, @r5, @r6, @r7, @r8, @r9, @r10, @r11, @r12, @r13, @r14, @r15, @r16, @r17, @r18, @r19, @r20 WHILE @@FETCH_STATUS = 0 BEGIN if @r1 = ''00'' begin set @seqsama = @seqsama + 1 --print '' seq sama '' + cast(@seqsama as varchar) if @r333 <> @r3 begin --print ''tidak sama =================================='' --print @r333 + '' dan '' + @r3 set @seqno = 1 end --else if @r333 = @r3 --begin -- print ''sama #####################################'' -- print @r333 + '' dan '' + @r3 --end set @cekudahada = ( select count(*) from tbl_inv_hdr where r1=@r1 and r2=@r2 and r3=@r3 and r5=@r5 ) if @cekudahada = 0 begin insert into tbl_inv_hdr ( [r1] ,[r2] ,[r3] ,[r4] ,[r5] ,[r6] ,[r7] ,[r8] ,[r9] ,[r10] ,[r11] ,[r12] ,[r13] ,[r14] ,[r15] ,[r16] ,[r17] ,[r18] ,[r19] ,[r20] ,[date_create] ,[status_inv] ,[car] ,[nobc] ,[nobl] ) values ( @r1, @r2, @r3, @r4, @r5, @r6, @r7, @r8, @r9, @r10, @r11, @r12, @r13, @r14, @r15, @r16, @r17, @r18, @r19, @r20, getdate(), 0, 0, 0, ''-'' ) set @inv_hdr_id = @@IDENTITY set @noinv=@r3 set @tginv=@r5 --print @seqno end if @cekudahada > 0 begin set @inv_hdr_id_udahada = ( select top 1 inv_hdr_id from tbl_inv_hdr where r1=@r1 and r2=@r2 and r3=@r3 and r5=@r5 ) set @inv_hdr_id = @inv_hdr_id_udahada set @noinv=@r3 set @tginv=@r5 end set @r333 = @r3 end if @r1 = ''01'' begin set @seqno = @seqno + 1 set @seqnourut = @seqno - 1 set @cekseqno = (select count(*) from [dbo].[tbl_inv_dtl] where (seqno=@seqnourut and [inv_hdr_id] =@inv_hdr_id) or (seqno is null and [inv_hdr_id] =@inv_hdr_id) ) --print @seqnourut --print cast(@cekseqno as varchar) + ''insert ================================================================='' + cast(@inv_hdr_id as varchar) if @cekseqno = 0 begin insert into tbl_inv_dtl ( [inv_hdr_id] ,seqno ,[r1] ,[r2] ,[r3] ,[r4] ,[r5] ,[r6] ,[r7] ,[r8] ,[r9] ,[r10] ,[r11] ,[r12] ,[r13] ,[r14] ,[car] ,[nobc] ,[noinv] ,[tglinv] ) values ( @inv_hdr_id, @seqnourut, @r1, @r2, @r3, @r4, @r5, @r6, @r7, @r8, @r9, @r10, @r11, @r12, @r13, @r14, 0, 0, @noinv, @tginv ) end --if (select count(*) from tbl_inv_dtl where inv_hdr_id = @inv_hdr_id) > 0 --begin -- select ''udah ada - update'', @inv_hdr_id --end end FETCH NEXT FROM inv_cursor into @rn, @r1, @r2, @r3, @r4, @r5, @r6, @r7, @r8, @r9, @r10, @r11, @r12, @r13, @r14, @r15, @r16, @r17, @r18, @r19, @r20 END CLOSE inv_cursor DEALLOCATE inv_cursor ')
 
 END TRY
 BEGIN CATCH
  exec (
' drop table '+ @tblinv_all +''
  ) 
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  Set @ErrMsg = ERROR_MESSAGE()
  set @ErrSeverity = ERROR_SEVERITY()
  select @ErrSeverity as status, @ErrMsg as description
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
 END CATCH
 
 update dbo.tbl_upload_invoice set status_upload='Data Processed' where filename=@File
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 
 exec (
' drop table '+ @tblinv_all +''
 ) 
 select '00' as status, 'SUKSES PROCESSED DATA' as description 
END  
--go
--EXEC ('sp_insert_data_hib @filename=''C:/wamp/www/pemi/loaderfiles/invoice/hib/b_9556_.txt'', @file=''b_9556_.txt'' ')
GO