dbo.spi_scrap

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_scrap
(@filename varchar(100), @id_scrap_upload 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)
 IF @tipe='SCRAPBB'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\scrap\bahan_baku'
 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_scrap where id_scrap_upload=@id_scrap_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 +
''', [''SCRAP IN$'']) WHERE F1=''kode_barang_scrap'' AND F2=''nama_barang_scrap'' and F3=''tgl_scrap_in'' and f5=''satuan_scrap_in'' and f6=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE SCRAP TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_scrap_tmp table ( kode_barang_scrap varchar(50), nama_barang_scrap varchar(100), tgl_scrap_in date, qty_scrap_in float, satuan_scrap_in varchar(50), query varchar(50) ); insert into @tbl_scrap_tmp ( kode_barang_scrap, nama_barang_scrap, tgl_scrap_in, qty_scrap_in, satuan_scrap_in, query ) select kode_barang_scrap, nama_barang_scrap, tgl_scrap_in, qty_scrap_in, satuan_scrap_in, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''SCRAP IN$'']) delete from @tbl_scrap_tmp where kode_barang_scrap is null declare @barangscrap table ( kode_barang varchar(50), part_name_customs varchar(100) ) insert into @barangscrap ( kode_barang, part_name_customs ) select part_no, part_name from tbl_scrap_data --select * from @tbl_scrap_tmp declare @x_kode_barang_scrap varchar(50),@null_kode_barang_scrap varchar(max) set @null_kode_barang_scrap = '''' DECLARE db_cursor CURSOR FOR select x.kode_barang_scrap from @tbl_scrap_tmp as x LEFT OUTER JOIN @barangscrap z ON replace(x.kode_barang_scrap, '' '', '''') = replace(z.kode_barang, '' '', '''') where z.kode_barang is null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_kode_barang_scrap WHILE @@FETCH_STATUS = 0 BEGIN set @null_kode_barang_scrap = @null_kode_barang_scrap + @x_kode_barang_scrap + '' | '' FETCH NEXT FROM db_cursor INTO @x_kode_barang_scrap END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@null_kode_barang_scrap) <> '''' begin select ''06'' as status, @null_kode_barang_scrap + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_scrap AS TARGET USING @tbl_scrap_tmp AS SOURCE ON ( replace(SOURCE.kode_barang_scrap, '' '', '''') = replace(TARGET.part_no, '' '', '''') AND TARGET.date_scrap = SOURCE.tgl_scrap_in ) WHEN MATCHED THEN UPDATE set TARGET.qty = SOURCE.qty_scrap_in, TARGET.unit = SOURCE.satuan_scrap_in WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_scrap] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,status ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_scrap_in ,SOURCE.kode_barang_scrap ,SOURCE.nama_barang_scrap ,SOURCE.nama_barang_scrap ,SOURCE.qty_scrap_in ,SOURCE.satuan_scrap_in ,'''+@typebahan+
''' ,''new'' ,'''+@username+''','''+@username+
''' ,@jam,@jam) --OUTPUT $action ; delete FROM tbl_scrap WHERE part_no IN ( select kode_barang_scrap from @tbl_scrap_tmp where query = ''delete'' ) and part_name in ( select nama_barang_scrap from @tbl_scrap_tmp where query = ''delete'' ) and date_scrap in ( select tgl_scrap_in from @tbl_scrap_tmp where query = ''delete'' ) ; ')
 
 
 update tbl_upload_scrap set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_scrap_upload =@id_scrap_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 IF @tipe='SCRAPBBSTA'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\scrap\bahan_baku'
 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_scrap where id_scrap_upload=@id_scrap_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 +
''', [''SCRAP STA$'']) WHERE F1=''kode_barang_scrap'' AND F2=''nama_barang_scrap'' and F3=''tgl_scrap_sta'' and f5=''satuan_scrap_sta'' and f6=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE SCRAP STA TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_scrap_sta_tmp table ( kode_barang_scrap varchar(50), nama_barang_scrap varchar(100), tgl_scrap_sta date, qty_scrap_sta float, satuan_scrap_sta varchar(50), query varchar(50) ); insert into @tbl_scrap_sta_tmp ( kode_barang_scrap, nama_barang_scrap, tgl_scrap_sta, qty_scrap_sta, satuan_scrap_sta, query ) select kode_barang_scrap, nama_barang_scrap, tgl_scrap_sta, qty_scrap_sta, satuan_scrap_sta, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''SCRAP STA$'']) delete from @tbl_scrap_sta_tmp where kode_barang_scrap is null declare @barangscrap table ( kode_barang varchar(50), part_name_customs varchar(100) ) insert into @barangscrap ( kode_barang, part_name_customs ) select part_no, part_name from tbl_scrap_data declare @x_kode_barang_scrap varchar(50),@null_kode_barang_scrap varchar(max) set @null_kode_barang_scrap = '''' DECLARE db_cursor CURSOR FOR select x.kode_barang_scrap from @tbl_scrap_sta_tmp as x LEFT OUTER JOIN @barangscrap z ON replace(x.kode_barang_scrap, '' '', '''') = replace(z.kode_barang, '' '', '''') where z.kode_barang is null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_kode_barang_scrap WHILE @@FETCH_STATUS = 0 BEGIN set @null_kode_barang_scrap = @null_kode_barang_scrap + @x_kode_barang_scrap + '' | '' FETCH NEXT FROM db_cursor INTO @x_kode_barang_scrap END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@null_kode_barang_scrap) <> '''' begin select ''06'' as status, @null_kode_barang_scrap + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_scrap_sta AS TARGET USING @tbl_scrap_sta_tmp AS SOURCE ON ( replace(SOURCE.kode_barang_scrap, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.date_scrap_sta = SOURCE.tgl_scrap_sta THEN UPDATE set TARGET.qty = SOURCE.qty_scrap_sta, TARGET.unit = SOURCE.satuan_scrap_sta WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_scrap_sta] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,status ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_scrap_sta ,SOURCE.kode_barang_scrap ,SOURCE.nama_barang_scrap ,SOURCE.nama_barang_scrap ,SOURCE.qty_scrap_sta ,SOURCE.satuan_scrap_sta ,'''+@typebahan+
''' ,''new'' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; delete FROM tbl_scrap_sta WHERE part_no IN ( select kode_barang_scrap from @tbl_scrap_sta_tmp where query = ''delete'' ) and part_name in ( select nama_barang_scrap from @tbl_scrap_sta_tmp where query = ''delete'' ) and date_scrap_sta in ( select tgl_scrap_sta from @tbl_scrap_sta_tmp where query = ''delete'' ) ; ')
 
 
 update tbl_upload_scrap set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_scrap_upload =@id_scrap_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 IF @tipe<>'SCRAPBB'
 begin
 select '06' as status, 'PROSES FAILED' as description
 end
END
--go
-- exec('spi_scrap @filename=''scrap20130614.xls'', @id_scrap_upload=16, @tipe=''SCRAPBB'', @username=''admin'', @typebahan=''SCRAPBB'' ')
GO