dbo.spi_mesin_stockawal

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_mesin_stockawal
(@filename varchar(100), @id_mesin_upload bigint, @tipe varchar(50),  @username varchar(100) )
AS
BEGIN
    --
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
 IF @tipe='MPKSTA'
    begin
    
    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\mesin_alat_kantor_stockawal'
 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_data_mesin_in_out where id_data_mesin=@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 +
''', [''SALDO AWAL$'']) WHERE F1=''KODE_BARANG'' AND F2=''NAMA_BARANG'' and F3=''DATE_STOCK'' and F4=''QTY'' and F5=''UNIT'' and f6=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA MESIN DAN PERALATAN KANTOR IN TIDAK SESUAI'' AS description RETURN END if ( select count(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''SALDO AWAL$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NAMA_BARANG = '''' or NAMA_BARANG is not null or DATE_STOCK = '''' or DATE_STOCK is not null or QTY is not null or UNIT = '''' or UNIT is not null ) = 0 begin SELECT ''06'' AS status, ''DATA TIDAK BISA DIPROSES'' AS description RETURN end Declare @tbl_dt_mpk_stockawal table ( KODE_BARANG varchar(100) null, NAMA_BARANG varchar(100) null, DATE_STOCK date null, QTY float null, UNIT varchar(100) null, query varchar(100) ); insert into @tbl_dt_mpk_stockawal ( KODE_BARANG, NAMA_BARANG, DATE_STOCK, QTY, UNIT, query ) select KODE_BARANG, NAMA_BARANG, DATE_STOCK, QTY, UNIT, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''SALDO AWAL$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NAMA_BARANG = '''' or NAMA_BARANG is not null or QTY is not null or UNIT = '''' or UNIT is not null or DATE_STOCK = '''' or DATE_STOCK is not null delete from @tbl_dt_mpk_stockawal where KODE_BARANG = '''' or KODE_BARANG is null or NAMA_BARANG = '''' or NAMA_BARANG is null or QTY is null or UNIT = '''' or UNIT is null declare @cnt2 int,@cnt3 int set @cnt2=(select count(*) from ( select KODE_BARANG from @tbl_dt_mpk_stockawal except select kode_brg from dbo.tbl_dt_mesindanalatkantor_code ) x ) if @cnt2 > 0 begin set @cnt3=(select count(*) from @tbl_dt_mpk_stockawal a inner join tbl_dt_mesindanalatkantor_code ON tbl_dt_mesindanalatkantor_code.kode_brg= a.KODE_BARANG) 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() --select a.KODE_BARANG, a.NAMA_BARANG, a.DATE_STOCK, sum(a.QTY) as QTY,a.UNIT from @tbl_dt_mpk_stockawal a group by a.KODE_BARANG,a.UNIT,a.DATE_STOCK, a.NAMA_BARANG MERGE tbl_dt_mpk_stockawal AS TARGET USING (select a.KODE_BARANG, a.NAMA_BARANG, a.DATE_STOCK, sum(a.QTY) as QTY,a.UNIT from @tbl_dt_mpk_stockawal a group by a.KODE_BARANG,a.UNIT,a.DATE_STOCK, a.NAMA_BARANG) AS SOURCE ON ( replace(SOURCE.KODE_BARANG, char(32), '''') = replace(TARGET.part_no, char(32), '''') and TARGET.part_name = SOURCE.NAMA_BARANG ) WHEN MATCHED THEN UPDATE set TARGET.part_no = SOURCE.KODE_BARANG, TARGET.part_name = SOURCE.NAMA_BARANG, TARGET.date_stockawal = SOURCE.DATE_STOCK, TARGET.qty = SOURCE.QTY, TARGET.unit = SOURCE.UNIT WHEN NOT MATCHED BY TARGET THEN INSERT ( [part_no] ,part_name ,date_stockawal ,[qty] ,unit ,create_by, update_by, create_date, update_date) values ( KODE_BARANG, NAMA_BARANG, DATE_STOCK, QTY, UNIT ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; delete FROM tbl_dt_mpk_stockawal WHERE part_no IN ( select KODE_BARANG from @tbl_dt_mpk_stockawal where query = ''delete'' ) and part_name in ( select NAMA_BARANG from @tbl_dt_mpk_stockawal where query = ''delete'' ) and unit in ( select UNIT from @tbl_dt_mpk_stockawal where query = ''delete'' ) ; ')
 
 
 update tbl_upload_data_mesin_in_out set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_data_mesin =@id_mesin_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
    end
    
  
 
END
GO