dbo.sp_insert_data_non_hib

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.sp_insert_data_non_hib 
 -- Add the parameters for the stored procedure here
 
AS
BEGIN
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON

    declare @now datetime
 set @now = GETDATE()
 declare @filecount int, @uruterror int 
 Declare @Size varchar(50)
 Declare @FileName varchar(50)
 declare @filedata varchar(100), @logerror varchar(100), @logerror_dtl varchar(100)
 declare @cmdmove varchar(255)
 DECLARE @Files table(Names varchar(250) null)
 Declare @mdate varchar(14)
 Declare @filedir varchar(100)
 --bulk
 declare @tbltemp_nonhib varchar(MAX)
 DECLARE @ParmDefinition nvarchar(MAX);
 DECLARE @str_data nvarchar(MAX);
 declare @tbltemp_nonhib_dtl varchar(MAX)
 
 Declare @dir varchar(100)
    declare @dircmd varchar(500)
 
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\invoice\nonhib'
 set @dircmd='dir '+@dir+'\invoice\nonhib\*.TXT'
  
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 
 if @filecount = 1
 begin
  select '01: ' as status, names as description from @Files  where Names like '%File Not Found%'
  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)
 --SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files order by Names
 set @tbltemp_nonhib= 'tbltemp_nonhib' + replace(convert(varchar(255) , newid()), '-','')
 set @tbltemp_nonhib_dtl='tbltemp_nonhib_dtl' + replace(convert(varchar(255) , newid()), '-','')
 
 --select '00' as status, COUNT(*) as description from @Files
 

 exec (' create table ' + @tbltemp_nonhib + 
' ( listdata varchar(MAX) ) ')
 exec (' create table ' + @tbltemp_nonhib_dtl + 
' ( noinv varchar(50) ,tglinv varchar(50) ,filename varchar(500) ,vendor varchar(50) ) ')
 
 
 --delete from dbo.tbl_nonhib
 --delete from dbo.tbl_nonhib_dtl
   
 set @uruterror=1
 DECLARE file_cursor CURSOR FOR 
 SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files order by Names
 OPEN file_cursor
    FETCH NEXT FROM file_cursor INTO @Size, @FileName 
 WHILE @@FETCH_STATUS = 0 
 BEGIN -- begin here
  set @filedata = @filedir +'\'+ @FileName 
  set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @FileName
     set @mdate = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')) 
     set @logerror = @filedir+'\log\error' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
     set @logerror_dtl = @filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
  
   
   
  SET @str_data = 
N' BULK INSERT ' + @tbltemp_nonhib + 
' FROM '''+ @filedata +
''' WITH ( ROWTERMINATOR = ''\n'' ,FIRSTROW = 2 ,MAXERRORS = 0 ,KEEPNULLS ,ERRORFILE = '''+ @logerror +
''' ) delete from ' + @tbltemp_nonhib + 
' where (substring(listdata,1,11) like ''%INVOICE-%'') or (substring(listdata,1,11) like ''%* * * E N %'') or (substring(listdata,1,130) like ''%PAGE%'') insert into '+@tbltemp_nonhib_dtl+
' ( noinv ,tglinv ,filename ,vendor ) select substring(listdata,1,11) as invoice_no ,replace(substring(listdata,12,11),'','','''') as invoice_date ,'''+@filedata+
''' ,substring(listdata,127,6) as VENDOR from '+ @tbltemp_nonhib +
' declare @cntdata int set @cntdata = (select count(*) from '+@tbltemp_nonhib+
') BEGIN TRY BEGIN TRANSACTION merge tbl_nonhib AS TARGET USING (select filename ,count(noinv) as cntinv ,noinv ,dbo.str_to_date(tglinv) as tglinv1 ,tglinv ,vendor from '+@tbltemp_nonhib_dtl+
' group by noinv, tglinv, filename, vendor) AS SOURCE ON (TARGET.[noinv] = SOURCE.noinv and TARGET.[tglinv] = SOURCE.tglinv1 ) WHEN NOT MATCHED BY TARGET THEN insert ( from_file_name,exec_date, totalitemfile, itemcount,noinv,tglinv,tglinv2, status, nobl,vendor, nocar ) values ( SOURCE.filename, GETDATE(), @cntdata, SOURCE.cntinv, SOURCE.noinv,SOURCE.tglinv1,SOURCE.tglinv ,''new'' ,''-'' ,SOURCE.vendor ,''-'' ) ; merge tbl_nonhib_dtl AS TARGET USING (select substring(listdata,1,11) as invoice_no ,dbo.str_to_date(replace(substring(listdata,12,11),'','','''')) as invoice_date ,replace(substring(listdata,12,11),'','','''') as invdate2 ,dbo.trim(substring(listdata,23,26)) as ITEMNUMBER ,dbo.trim(substring(listdata,49,26)) as ITEMNUMBER2 ,dbo.trim(substring(listdata,75,13)) as CONTNO ,CAST(replace(substring(listdata,88,7),'','','''') as float ) as INVQTY ,CAST(replace(substring(listdata,95,7),'','','''') as float) as PACK ,dbo.trim(substring(listdata,103,7)) as UOMPFX ,dbo.trim(substring(listdata,110,4)) as CNO ,dbo.trim(substring(listdata,116,11)) as ORDERNO ,dbo.trim(substring(listdata,127,6)) as VENDOR from '+ @tbltemp_nonhib +
' ) AS SOURCE ON (TARGET.INVOICE_NO = SOURCE.invoice_no and TARGET.INVDATE = SOURCE.invoice_date ) WHEN NOT MATCHED BY TARGET THEN insert ( [INVOICE_NO] ,[INVDATE] ,[invdate2] ,[ITEMNUMBER] ,[ITEMNUMBER2] ,[CONTNO] ,[INVQTY] ,[PACK] ,[UOMPFX] ,[CNO] ,[ORDERNO] ,[VENDOR] ) values( SOURCE.invoice_no ,SOURCE.invoice_date ,SOURCE.invdate2 ,SOURCE.ITEMNUMBER ,SOURCE.ITEMNUMBER2 ,SOURCE.CONTNO ,SOURCE.INVQTY ,SOURCE.PACK ,SOURCE.UOMPFX ,SOURCE.CNO ,SOURCE.ORDERNO ,SOURCE.VENDOR ) ; DECLARE @noinv varchar(50), @tglinv varchar(10), @filename varchar(500) DECLARE tbltemp_nonhib_cursor CURSOR FOR select noinv, tglinv, filename from '+@tbltemp_nonhib_dtl+
' group by noinv, tglinv, filename OPEN tbltemp_nonhib_cursor FETCH NEXT FROM tbltemp_nonhib_cursor into @noinv, @tglinv, @filename WHILE @@FETCH_STATUS = 0 BEGIN update [tbl_nonhib_dtl] set [id_nonhib_hdr] = (select top 1 id_nonhib_hdr from [tbl_nonhib] where [noinv] = @noinv and [tglinv] = @tglinv) where [INVOICE_NO] = @noinv and [invdate2] = @tglinv FETCH NEXT FROM tbltemp_nonhib_cursor into @noinv, @tglinv, @filename END CLOSE tbltemp_nonhib_cursor DEALLOCATE tbltemp_nonhib_cursor COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int Set @ErrMsg = ERROR_MESSAGE() set @ErrSeverity = ERROR_SEVERITY() select @ErrMsg as description,@ErrSeverity as status RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH delete from '+ @tbltemp_nonhib +
' delete from '+ @tbltemp_nonhib_dtl +
' ';
     
     
     BEGIN TRY
     
      EXECUTE sp_executesql  @str_data
  
  END TRY
  BEGIN CATCH
   exec (
' drop table '+ @tbltemp_nonhib_dtl +
' drop table '+ @tbltemp_nonhib +''
   ) 
   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=@FileName and tipe = 'NON HIB'
  
  EXEC xp_cmdshell @cmdmove, NO_OUTPUT
  set @uruterror = @uruterror + 1; 
     FETCH NEXT FROM file_cursor INTO @Size, @FileName
 END
 CLOSE file_cursor
 DEALLOCATE file_cursor 
 
 exec ('drop table '+ @tbltemp_nonhib_dtl+'')
 exec ('drop table '+ @tbltemp_nonhib+'')
 select '00' as status, COUNT(*) as description from @Files 

END
GO