dbo.spi_bb_added_price

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_bb_added_price
(@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\price'
 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_price where id_upload_price=@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=''partno2'' and F2=''supplier'' and f3=''purchase_price'' and f4=''valuta'' and f5=''tgl_berlaku'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_data_part_hs table ( partno2 varchar(50) NULL, purchase_price decimal(18, 2) NULL, -- landed_cost decimal(18, 2) NULL, valuta varchar(50) NULL, tgl_berlaku date NULL, supplier varchar(50) NULL ); insert into @tbl_data_part_hs ( partno2 , purchase_price , valuta , tgl_berlaku , supplier ) select partno2 , purchase_price , valuta , tgl_berlaku , supplier FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) delete from @tbl_data_part_hs where partno2 is null if ( select count(*) from @tbl_data_part_hs where tgl_berlaku IS NULL or dbo.trim(tgl_berlaku) = '''' ) > 0 begin select ''06'' as status, ''TGL BERLAKU TIDAK BOLEH KOSONG'' as description return end declare @cnttidaksama int set @cnttidaksama = ( select count(a.partno2) from @tbl_data_part_hs a left outer join tbl_data_part_hs b on a.partno2 = b.part_no2 where b.part_no2 is null ) if ( select count(a.partno2) from @tbl_data_part_hs a left outer join tbl_data_part_hs b on a.partno2 = b.part_no2 where b.part_no2 is null ) > 0 begin select ''06'' as status, ''ADA DATA PART NO 2 TIDAK DITEMUKAN SEBANYAK '' + cast(@cnttidaksama as varchar) as description return end delete A from tbl_data_part_hs_price A inner join @tbl_data_part_hs B on A.partno2 = B.partno2 and A.tgl_berlaku = B.tgl_berlaku insert into tbl_data_part_hs_price ( partno2 , purchase_price , valuta , tgl_berlaku , supplier ) select partno2 , purchase_price , valuta , tgl_berlaku , supplier from @tbl_data_part_hs ')
 
 
 update tbl_upload_bb_added_price set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_upload_price =@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