dbo.spi_bb_added_master

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_bb_added_master
(@filename varchar(100), @id bigint, @tipe varchar(50),  @username varchar(100), @typebahan varchar(10) )
AS
BEGIN
  --
  SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
    Declare @dir varchar(100)
 declare @filedir varchar(200)
 declare @filecount int
 declare @fullpath varchar(200)
 DECLARE @Files table(Names varchar(250) null)
 declare @dircmd varchar(500)
 declare @cmdmove varchar(500)




 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\bb_added\master'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 
 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+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_upload_bb_added_master where id_upload_master=@id and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
'])WHERE F1=''part_no2'' and F2=''part_no_sap'' and f3=''order_number'' and f4=''new_part'' and f5=''shipment_term'' and f6=''supplier'' and f7=''currency'' and f8=''part_name_added'' and f9=''uom'' and f10=''supplier_kode'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE PAJAK TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_data_part_hs table ( part_no2 varchar(50) null, part_no_sap varchar(50) null, order_number varchar(50) null, new_part varchar(50) null, shipment_term varchar(50) null, supplier varchar(50) null, currency varchar(50) null, part_name_added varchar(50) null, uom varchar(50) null, supplier_kode varchar(50) null ); insert into @tbl_data_part_hs ( part_no2, part_no_sap , order_number, new_part , shipment_term, supplier , currency, part_name_added , uom , supplier_kode ) select part_no2, part_no_sap , order_number, new_part , shipment_term, supplier , currency, part_name_added, uom , supplier_kode FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) delete from @tbl_data_part_hs where part_no2 is null update A set A.[part_no_sap] = T.[part_no_sap], A.[order_number] = T.[order_number], A.[new_part] = T.[new_part], A.[shipment_term] = T.[shipment_term], A.[supplier] = T.[supplier], A.[currency] = T.[currency], A.[part_name_added] = T.[part_name_added], A.[uom] = T.[uom], A.[supplier_kode] = T.[supplier_kode] FROM tbl_data_part_hs A INNER JOIN @tbl_data_part_hs T on A.part_no2 = T.part_no2 ')
 
 
 update tbl_upload_bb_added_master set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_upload_master =@id
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description

 
END
--go
--BEGIN TRY
-- exec('spi_validasi_pajak @filename=''format_val_pajak.xls'', @id_validasi_pajak=5, @tipe=''a1'', @username=''admin'', @typebahan=''a1'' ')
-- END TRY
-- BEGIN CATCH
-- SELECT
-- 'error' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;
GO