dbo.spi_bb_added_adjustment_daily

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_bb_added_adjustment_daily
(@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\adjustment_daily'
 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_adjustment_daily where id_upload_adjustment_daily=@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=''tgl_adjustment'' and f3=''qty_in'' and f4=''qty_out'' and f5=''amount_in'' ) 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 ,qty_in decimal(18, 4) NULL ,qty_out decimal(18, 4) NULL ,amount_in decimal(18, 2) NULL ,amount_out decimal(18, 2) NULL ,tgl_adjustment date NULL ,jenis varchar(50) NULL ); insert into @tbl_data_part_hs ( partno2 ,qty_in ,qty_out ,amount_in ,amount_out ,tgl_adjustment ,jenis ) select partno2 ,qty_in ,qty_out ,amount_in ,amount_out ,tgl_adjustment ,''ADJUSTMENT_DAILY'' 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_adjustment IS NULL or dbo.trim(tgl_adjustment) = '''' ) > 0 begin select ''06'' as status, ''TGL TIDAK BOLEH KOSONG'' as description return end 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'' as description return end delete A from tbl_data_part_hs_adjustment A inner join @tbl_data_part_hs B on A.partno2 = B.partno2 and A.tgl_adjustment = B.tgl_adjustment and A.jenis = B.jenis insert into tbl_data_part_hs_adjustment ( partno2 ,qty_in ,qty_out ,amount_in ,amount_out ,tgl_adjustment ,jenis ) select partno2 ,qty_in ,qty_out ,amount_in ,amount_out ,tgl_adjustment ,jenis from @tbl_data_part_hs ')
 
 
 update tbl_upload_bb_added_adjustment_daily set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_upload_adjustment_daily =@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