dbo.sp_part_hs

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_part_hs
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_part_hs_temp varchar(MAX) 
 set @dir = (select top 1 direktori from dbo.tbl_direktori)

 set @filedir=@dir+'\invoice\part_hs'
 set @dircmd='dir '+@dir+'\invoice\part_hs\*.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 '00' as status, names+' 0 ' 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 @tbl_part_hs_temp= 'tbl_part_hs_temp' + replace(convert(varchar(255) , newid()), '-','')
 
 Declare @dynmaicsql as  nvarchar(1000);
 Declare @dynamicparam as nvarchar(1000);
 Declare @returncnt as int
 
 
 
 
 exec (
' create table ' + @tbl_part_hs_temp + 
' ( PART_NO varchar(25) null, --F1 PART_NO2 varchar(25) null, --F2 PART_NAME varchar(30) null, --F3 PART_NAME_CUSTOMS varchar(30) null, --F4 HSCODE varchar(20) null, --F5 BM float null, --F6 CEKPRICE float null, --F7 KODE_BARANG varchar(10) null --F8 ) ')
    
 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) + '.csv'
     set @logerror_dtl = @filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.csv'
  
  Set @dynmaicsql = 
' SET @cnt = ( select count (*) FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
']'') WHERE F1=''PART_NO'' AND F2=''PARTNO2'' AND F4=''PART_NAME_CUSTOM'' AND F8=''KD_BARANG'') '
  
  Set @dynamicparam = '@cnt int output'
  execute sp_executesql @dynmaicsql, @dynamicparam ,@returncnt  output
  
  if @returncnt = 0
  begin
   EXEC xp_cmdshell @cmdmove, NO_OUTPUT
   exec ('drop table '+ @tbl_part_hs_temp +'') 
   CLOSE file_cursor
   DEALLOCATE file_cursor 
   select '00' as status, 'FORMAT DATA SALAH' as description 
   return
  end
  
  BEGIN TRY  
  
/** exec(' insert into '+ @tbl_part_hs_temp +' ( PART_NO, PART_NO2, PART_NAME, PART_NAME_CUSTOMS, HSCODE, BM, CEKPRICE,KODE_BARANG ) select distinct PART_NO, PART_NO2, PART_NAME, PART_NAME_CUSTOMS, HSCODE, BM, CEKPRICE,KD_BARANG FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+';HDR=YES;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+']'') where KD_BARANG is not null group by PART_NO, PART_NO2, PART_NAME, PART_NAME_CUSTOMS, HSCODE, BM, CEKPRICE,KD_BARANG ') **/
  exec(
' insert into '+ @tbl_part_hs_temp +
' ( PART_NO, PART_NO2, PART_NAME, PART_NAME_CUSTOMS, HSCODE, BM, CEKPRICE,KODE_BARANG ) select distinct F1, F2, F3, F4, F5, F6,F7,F8 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
']'') where F1 is not null AND f1 <> ''PART_NO'' group by F1, F2, F3, F4, F5, F6,F7,F8 ') 


  END TRY
  BEGIN CATCH
   exec(' drop table '+ @tbl_part_hs_temp +' ') 
   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(
' delete from '+ @tbl_part_hs_temp +
' where PART_NO is null delete from '+ @tbl_part_hs_temp +
' where PART_NAME_CUSTOMS is null delete from '+ @tbl_part_hs_temp +
' where KODE_BARANG is null delete from '+ @tbl_part_hs_temp +
' where PART_NO = ''PART_NO'' ')
    
  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 
 
 
 BEGIN TRY 
 exec (
' MERGE tbl_data_part_hs AS TARGET USING '+ @tbl_part_hs_temp +
' AS SOURCE ON (TARGET.part_no2 = SOURCE.PART_NO2 and TARGET.part_no = SOURCE.PART_NO) WHEN MATCHED AND TARGET.part_name = SOURCE.PART_NAME OR TARGET.part_name_customs <> SOURCE.PART_NAME_CUSTOMS OR TARGET.hs_code <> SOURCE.HSCODE OR TARGET.bm <> SOURCE.BM OR TARGET.cek_price <> SOURCE.CEKPRICE OR TARGET.kode_barang <> SOURCE.KODE_BARANG THEN UPDATE SET TARGET.part_name = SOURCE.PART_NAME, TARGET.part_name_customs = SOURCE.PART_NAME_CUSTOMS, TARGET.hs_code = SOURCE.HSCODE, TARGET.bm = SOURCE.BM, TARGET.cek_price = SOURCE.CEKPRICE, TARGET.kode_barang = SOURCE.KODE_BARANG WHEN NOT MATCHED BY TARGET THEN INSERT (part_no, part_name, part_name_customs, hs_code, bm, cek_price, part_no2, kode_barang ) VALUES (SOURCE.PART_NO, SOURCE.PART_NAME, SOURCE.PART_NAME_CUSTOMS, SOURCE.HSCODE, SOURCE.BM, SOURCE.CEKPRICE, SOURCE.PART_NO2, SOURCE.KODE_BARANG ) ; ')
 
 END TRY
 BEGIN CATCH
  exec(' drop table '+ @tbl_part_hs_temp +' ') 
  DECLARE @ErrMsg1 nvarchar(4000), @ErrSeverity1 int
  Set @ErrMsg1 = ERROR_MESSAGE()
  set @ErrSeverity1 = ERROR_SEVERITY()
  select @ErrSeverity1 as status, @ErrMsg1 as description
  RAISERROR(@ErrMsg1, @ErrSeverity1, 1) 
 END CATCH
 
 --update tbl_uploadparths set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES'
 update tbl_uploadparths set status_upload='DATA PROCESSED' where  [status_upload] = 'SUKSES'
 select '00' as status, COUNT(*) as description from @Files 
 exec ('drop table '+ @tbl_part_hs_temp +'') 
END
GO