dbo.spi_bahan_penolong

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_bahan_penolong
(@filename varchar(100), @id_uploadbahanpenolong_upload bigint, @tipe varchar(50),  @username varchar(100) )

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+'\invoice\bahan_penolong\'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
 
 --
    
    if @tipe <> 'BP' 
    begin
  select '07' as status, 'DATA TIDAK DAPAT DI PROSES DIPORSES' as description 
  
  return 
    end
    
    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_uploadbahanpenolong where id_uploadbahanpenolong_upload=@id_uploadbahanpenolong_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  
  return 
 end
    
     
 BEGIN TRY 
 exec (
' declare @cnt bigint SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Text;FMT=Delimited;HDR=NO;IMEX=1;Database='+@filedir +''', ''SELECT * FROM ['+@FileName+
'] '') WHERE F1=''KODE_BARANG'' AND F2=''PART_NAME_CUSTOMS'' AND F3=''PART_NO'' AND F4=''PART_NAME'' AND F18=''CONSUMABLE'' AND F7=''ITEM_TYPE1'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA BAHAN PENOLONG TIDAK SESUAI'' AS description RETURN END Declare @tbl_data_bahan_penolong table ( [part_no] [varchar](250) NULL, [part_name] [varchar](250) NULL, [part_name_customs] [varchar](250) NULL, [hs_code] [varchar](250) NULL, [bm] [float] NULL, [cek_price] [float] NULL, [currency] [varchar](250) NULL, [part_no2] [varchar](250) NULL, [kode_barang] [varchar](250) NULL, [item_code] [varchar](250) NULL, [uom] [varchar](250) NULL, [seri_mesin] [varchar](250) NULL, [item_type1] [varchar](250) NULL, [item_type2] [varchar](250) NULL, [net_weight_unit] [float] NULL, [dangerous_good] [varchar](50) NULL, [saldo_awal] [float] NULL, [consumable] [varchar](250) NULL, [kode_supplier] [varchar](250) NULL, [create_date] [datetime] NULL, [create_by] [varchar](250) NULL, [update_date] [datetime] NULL, [update_by] [varchar](250) NULL ) insert into @tbl_data_bahan_penolong ( KODE_BARANG, PART_NAME_CUSTOMS, PART_NO, PART_NAME, SERI_MESIN, ITEM_CODE, ITEM_TYPE1, ITEM_TYPE2, HS_CODE, BM, CEK_PRICE, UOM, CURRENCY, NET_WEIGHT_UNIT, DANGEROUS_GOOD, KODE_SUPPLIER, SALDO_AWAL, CONSUMABLE, part_no2 ) select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, replace(F3,'' '','''') FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Text;FMT=Delimited;HDR=NO;IMEX=1;Database='+@filedir +''', ''SELECT * FROM ['+@filename+
'] '') where F1 <> ''KODE_BARANG'' and F2 <> ''PART_NAME_CUSTOMS'' and F3 <> ''PART_NO'' delete from @tbl_data_bahan_penolong where part_no is null and kode_barang is null if(select count(*) from @tbl_data_bahan_penolong where part_no is null or kode_barang is null or part_name is null ) > 0 begin SELECT ''06'' AS status, ''ADA DATA PART NO, KODE BARANG, PART NAME YANG KOSONG, MOHON DILENGKAPI'' AS description RETURN end MERGE tbl_data_bahan_penolong AS TARGET USING (select [part_no], [part_name], [part_name_customs], [hs_code], [bm], [cek_price], [currency], [part_no2], [kode_barang], [item_code], [uom], [seri_mesin], [item_type1], [item_type2], [net_weight_unit] , [dangerous_good], [saldo_awal], [consumable], [kode_supplier], [create_date], [create_by], [update_date], [update_by] from @tbl_data_bahan_penolong) AS SOURCE ON (TARGET.part_no2 = SOURCE.part_no2) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no --OR TARGET.part_name_customs <> SOURCE.part_name_customs --OR TARGET.hs_code <> SOURCE.hs_code --OR TARGET.bm <> SOURCE.bm --OR TARGET.cek_price <> SOURCE.cek_price --OR TARGET.kode_barang <> SOURCE.kode_barang --OR TARGET.item_code <> SOURCE.item_code --OR TARGET.uom <> SOURCE.uom --OR TARGET.item_type1 <> SOURCE.item_type1 --OR TARGET.item_type2 <> SOURCE.item_type2 --OR TARGET.net_weight_unit <> SOURCE.net_weight_unit --OR TARGET.saldo_awal <> SOURCE.saldo_awal --OR TARGET.consumable <> SOURCE.consumable --OR TARGET.kode_supplier <> SOURCE.kode_supplier --OR TARGET.currency <> SOURCE.currency THEN UPDATE SET TARGET.part_name = SOURCE.part_name, TARGET.part_name_customs = SOURCE.part_name_customs, TARGET.hs_code = SOURCE.hs_code, TARGET.bm = SOURCE.bm, TARGET.cek_price = SOURCE.cek_price, TARGET.kode_barang = SOURCE.kode_barang, TARGET.item_code = SOURCE.item_code, TARGET.uom = SOURCE.uom, TARGET.item_type1 = SOURCE.item_type1, TARGET.item_type2 = SOURCE.item_type2, TARGET.net_weight_unit = SOURCE.net_weight_unit, TARGET.saldo_awal = SOURCE.saldo_awal, TARGET.consumable = SOURCE.consumable, TARGET.kode_supplier = SOURCE.kode_supplier, TARGET.currency = SOURCE.currency WHEN NOT MATCHED BY TARGET THEN INSERT ([part_no], [part_name], [part_name_customs], [hs_code], [bm], [cek_price], [currency], [part_no2], [kode_barang], [item_code], [uom], [seri_mesin], [item_type1], [item_type2], [net_weight_unit] , [dangerous_good], [saldo_awal], [consumable], [kode_supplier], [create_date], [create_by], [update_date], [update_by], aktif ) VALUES (SOURCE.part_no, SOURCE.part_name, SOURCE.part_name_customs, SOURCE.hs_code, SOURCE.bm, SOURCE.cek_price, SOURCE.currency, SOURCE.part_no2, SOURCE.kode_barang, SOURCE.[item_code], SOURCE.[uom], SOURCE.[seri_mesin], SOURCE.[item_type1], SOURCE.[item_type2], SOURCE.[net_weight_unit] , SOURCE.[dangerous_good], SOURCE.[saldo_awal], SOURCE.[consumable], SOURCE.[kode_supplier], SOURCE.[create_date], SOURCE.[create_by], SOURCE.[update_date], SOURCE.[update_by], ''aktif'' ) ; ')
 
 END TRY
 BEGIN CATCH
  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
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 update tbl_uploadbahanpenolong set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_uploadbahanpenolong_upload=@id_uploadbahanpenolong_upload
 select '00' as status, ' SUKSES' as description 
 --from @Files

END

--go
-- BEGIN TRY
-- exec('[spi_bahan_penolong] @filename=''master_bahan_penolong.csv'', @id_uploadbahanpenolong_upload=12, @tipe=''BP'', @username=''admin'' ')
-- END TRY
-- BEGIN CATCH
-- SELECT
-- 'error' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;
GO