dbo.spi_proses_packinglist262

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_proses_packinglist262
(@filename varchar(100), @id_upload_packinglist 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+'\exim\packing_list262'
 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_packinglist_262 where id_upload_packinglist=@id_upload_packinglist 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+
''', [packinglist$]) WHERE F1=''no_packinglist'' AND F2=''tgl_packinglist'' AND F3=''assy_no'' AND F4=''qty_box'' AND F5=''qty'' AND F6=''unit'' AND F7=''price'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE PACKINGLIST BC262 TIDAK SESUAI'' AS description RETURN END Declare @tbl_dtpackinglist_bc262 table ( no_packinglist varchar(100) null, tgl_packinglist date null, assy_no varchar(100) null, qty_box float null, qty float null, unit varchar(10) null, price float null ); insert into @tbl_dtpackinglist_bc262 ( no_packinglist, tgl_packinglist, assy_no , qty_box, qty, unit, price ) select no_packinglist, tgl_packinglist, assy_no , qty_box, qty, unit, price FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [packinglist$]) delete from @tbl_dtpackinglist_bc262 where no_packinglist = '''' or assy_no is null insert into [dbo].[tbl_packinglist_bc262] ( [no_packinglist] ,[tgl_packinglist] ,[assy_no] ,[qty_box] ,[qty] ,[unit] ,[price] ,[amount] ,[status] ) select no_packinglist, tgl_packinglist, assy_no, qty_box, qty, unit, price, cast((qty * price) as decimal(10,2)), ''NEW'' FROM @tbl_dtpackinglist_bc262 ')
 
 
 update tbl_upload_packinglist_262 set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_upload_packinglist =@id_upload_packinglist
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
GO