dbo.spi_mesindanalatktr

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_mesindanalatktr
(@filename varchar(100), @id_mesin_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\mesin_alat_ktr'
 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_uploadmesindanalatktr where id_mesin_upload=@id_mesin_upload 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 +
''', [''MESIN DAN ALAT KANTOR$'']) WHERE F1=''KODE_BARANG'' AND F2=''NAMA_BARANG'' and F3=''DESCRIPTION'' and F5=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA MESIN DAN PERALATAN KANTOR TIDAK SESUAI'' AS description RETURN END Declare @tbl_dt_mesindanalatkantor_code table ( KODE_BARANG varchar(100) null, NAMA_BARANG varchar(100) null, DESCRIPTION varchar(100) null, SATUAN varchar(100) null, query varchar(100) ); insert into @tbl_dt_mesindanalatkantor_code ( KODE_BARANG, NAMA_BARANG , DESCRIPTION, SATUAN, query ) select KODE_BARANG, NAMA_BARANG, DESCRIPTION, SATUAN, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''MESIN DAN ALAT KANTOR$'']) delete from @tbl_dt_mesindanalatkantor_code where KODE_BARANG = '''' or KODE_BARANG is null --select KODE_BARANG from @tbl_dt_mesindanalatkantor_code where query = ''delete'' --declare @cnt2 int,@cnt3 int --set @cnt2=(select count(*) from ( -- select PART_NO_MPK from @tbl_dt_mesindanalatkantor_code -- except -- select part_no from dbo.tbl_dt_mesindanalatkantor_code -- ) x ) -- if @cnt2 > 0 -- begin -- set @cnt3=(select count(*) from @tbl_dt_mesindanalatkantor_code a -- inner join tbl_dt_mesindanalatkantor_code ON replace(tbl_dt_mesindanalatkantor_code.part_no, '' '', '''') = replace(a.PART_NO_MPK, '' '', '''')) -- select ''07'' as status, ''ADA DATA TIDAK MATCH: ''+cast(@cnt2 as varchar)+'' MATCH: ''+ cast(@cnt3 as varchar) +'' Mohon dipriksa kembali datanya'' as description -- return -- end declare @jam date set @jam = getdate() MERGE tbl_dt_mesindanalatkantor_code AS TARGET USING @tbl_dt_mesindanalatkantor_code AS SOURCE ON SOURCE.KODE_BARANG = TARGET.kode_brg WHEN MATCHED AND TARGET.kode_brg = SOURCE.KODE_BARANG THEN UPDATE set TARGET.kode_brg = SOURCE.KODE_BARANG, TARGET.nama_barang = SOURCE.NAMA_BARANG, TARGET.keterangan = SOURCE.DESCRIPTION, TARGET.satuan = SOURCE.SATUAN WHEN NOT MATCHED BY TARGET THEN INSERT ( [kode_brg] ,[nama_barang] ,[keterangan] ,[satuan] ,create_by, update_by, create_date, update_date) values ( KODE_BARANG, NAMA_BARANG, DESCRIPTION, SATUAN, '''+@username+''','''+@username+
''' ,@jam,@jam) ; delete FROM tbl_dt_mesindanalatkantor_code WHERE kode_brg IN ( select KODE_BARANG from @tbl_dt_mesindanalatkantor_code where query = ''delete'' ); ')
 
 
 update tbl_uploadmesindanalatktr set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_mesin_upload =@id_mesin_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
GO