dbo.spi_supplier

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_supplier
AS
BEGIN
  SET ANSI_NULLS ON
 SET ANSI_WARNINGS 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_supplier_temp varchar(MAX) 
 
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 
 set @filedir=@dir+'\invoice\supplier'
 set @dircmd='dir '+@dir+'\invoice\supplier\*.xls'
 
 
  
 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
 
 select '00' as status, COUNT(*) as description from @Files 
 
 set @tbl_supplier_temp= 'tbl_supplier_temp' + replace(convert(varchar(255) , newid()), '-','')
 
  exec ('create table ' + @tbl_supplier_temp + 
' ( SUPP_CODE varchar(50) null, SUPP_NAME varchar(50) null, KD_NEGARA varchar(50) null, BASE_PRICE varchar(50) null, COPPERBASE_REF varchar(50) null, SUPP_GCT varchar(50) null, SUPPCODE_AS400 varchar(50) 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'
  
  exec('insert into '+ @tbl_supplier_temp +
' ( SUPP_CODE, SUPP_NAME, KD_NEGARA, BASE_PRICE, COPPERBASE_REF, SUPP_GCT, SUPPCODE_AS400 ) select SUPP_CODE, SUPP_NAME, KD_NEGARA, BASE_PRICE, COPPERBASE_REF, SUPP_GCT, SUPPCODE_AS400 from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @filedata +
'; Extended properties=Excel 8.0;'' )...[''Sheet 1$''] delete from '+ @tbl_supplier_temp +
' where SUPP_CODE is null ')
    
  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 MERGE tbl_data_supplier AS TARGET USING '+ @tbl_supplier_temp +
' AS SOURCE ON (TARGET.supplier_code = SOURCE.SUPP_CODE) WHEN MATCHED THEN UPDATE SET TARGET.supplier_name = SOURCE.SUPP_NAME, TARGET.supplier_country = SOURCE.KD_NEGARA, TARGET.base_price = SOURCE.BASE_PRICE, TARGET.copper_base_reff = SOURCE.COPPERBASE_REF, TARGET.subgct = SOURCE.SUPP_GCT, TARGET.subcode_as400 = SOURCE.SUPPCODE_AS400 WHEN NOT MATCHED BY TARGET THEN INSERT ( supplier_code, supplier_name, supplier_country, base_price, copper_base_reff, subgct, subcode_as400 ) VALUES ( SOURCE.SUPP_CODE, SOURCE.SUPP_NAME, SOURCE.KD_NEGARA, SOURCE.BASE_PRICE, SOURCE.COPPERBASE_REF, SOURCE.SUPP_GCT, SOURCE.SUPPCODE_AS400 ) ; 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 ')
 
 update tbl_uploadsupplier set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES'
 exec ('drop table '+ @tbl_supplier_temp +'')
 
 select '00' as status, COUNT(*) as description from @Files 
  
END
GO