dbo.sp_assy_list

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_assy_list
AS
BEGIN
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
    declare @filecount int, @uruterror int 
 Declare @Size varchar(50),@filedir varchar(100) 
 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 @dir varchar(100)
    declare @dircmd varchar(500)
 
 Declare @tbl_list_assy_temp varchar(MAX) 
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 
 set @filedir=@dir+'\invoice\assy'
 set @dircmd='dir '+@dir+'\invoice\assy\*.csv'
 
  
 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 '06' 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
 
 --select COUNT(*) as jml from @Files
 
 set @tbl_list_assy_temp= 'tbl_list_assy_temp' + replace(convert(varchar(255) , newid()), '-','')
 
  exec ('create table ' + @tbl_list_assy_temp + 
' ( ASSY_CODE varchar(10) null, ASSY_NO varchar(25) null, DESC_CARLINE varchar(30) null, QTY_BOX varchar(50) null, PRICE varchar(50) null, NET_KGS varchar(50) null, CUST_CODE varchar(4) null, filename varchar(500) null, dateexec datetime, status_aktif varchar(10) null )')
    
 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) + '.xls'
     set @logerror_dtl = @filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.xls'
  
 
  BEGIN TRY
  exec(
  
' insert into '+ @tbl_list_assy_temp +
' ( [ASSY_CODE] ,[ASSY_NO] ,[DESC_CARLINE] ,[QTY_BOX] ,[PRICE] ,[NET_KGS] ,[CUST_CODE] ,filename ,dateexec ,status_aktif ) select F1 ,F2 ,F3 ,F4 ,F5 ,F6 ,F7 ,'''+ @filedata +
''' ,GETDATE() ,F8 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
']'') where F1 <> ''ASSY_CODE'' ')
  
  END TRY
  BEGIN CATCH
  exec ('drop table '+ @tbl_list_assy_temp +'')
  update [tbl_uploadassylist] set status_upload = 'Deleted' where status_upload='SUKSES' and filename=@FileName and filepath=replace(@filedata,'/','\')
  select ERROR_NUMBER() as status, ERROR_MESSAGE() as description
  
  END CATCH; 
  
  update [tbl_uploadassylist] set status_upload = 'DATA PROCESSED' where status_upload='SUKSES' and filename=@FileName and filepath=replace(@filedata,'/','\')
  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 (
' BEGIN TRY BEGIN TRANSACTION delete from '+ @tbl_list_assy_temp +
' where ASSY_CODE is null delete from '+ @tbl_list_assy_temp +
' where ASSY_CODE = ''ASSY_CODE'' INSERT INTO tbl_assy_list ( [ASSY_CODE] --F1 ,[ASSY_NO] --F2 ,[DESC_CARLINE]--F3 ,[QTY_BOX] --F4 ,[PRICE] --F5 ,[NET_KGS] --F6 ,[CUST_CODE] --F7 ,filename --F8 ,dateexec --F9 ,status_aktif --F10 ) select [ASSY_CODE] ,[ASSY_NO] ,[DESC_CARLINE] ,[QTY_BOX] ,[PRICE] ,[NET_KGS] ,[CUST_CODE] ,filename ,dateexec ,status_aktif from '+ @tbl_list_assy_temp +
' as t where not exists(select ASSY_CODE from dbo.tbl_assy_list where dbo.tbl_assy_list.ASSY_CODE = t.ASSY_CODE ) 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 @ErrSeverity as status, @ErrMsg as description RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH ')
 exec (
' --select * from '+ @tbl_list_assy_temp +
' drop table '+ @tbl_list_assy_temp +
' ') 
    select '00' as status, COUNT(*) as description from @Files
 
END
GO