dbo.spi_stock_awal

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_stock_awal
(@filename varchar(100), @id_stockawal_upload bigint, @tipe varchar(50),  @username varchar(100), @typebahan varchar(10) )
AS
BEGIN
    --
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS 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+'\inventory\stock_awal'
 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_stockawal where id_stockawal_upload=@id_stockawal_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 +
''', [''STOCK AWAL$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_awal'' and F3=''qty_stock_awal'' and f4=''satuan_stock_awal'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK AWAL TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockawal_tmp table ( part_no varchar(100) null, tgl_stock_awal date null, qty_stock_awal float null, satuan_stock_awal varchar(50) null ); insert into @tbl_stockawal_tmp ( part_no, tgl_stock_awal, qty_stock_awal, satuan_stock_awal ) select part_no, tgl_stock_awal, qty_stock_awal, satuan_stock_awal FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK AWAL$'']) delete from @tbl_stockawal_tmp where part_no is null --declare @cnt2 int,@cnt3 int --set @cnt2=(select count(*) from ( -- select part_no from @tbl_stockawal_tmp -- except -- select part_no from dbo.tbl_data_part_hs -- ) x ) -- if @cnt2 > 0 -- begin -- set @cnt3=(select count(*) from @tbl_stockawal_tmp a -- inner join tbl_data_part_hs ON replace(tbl_data_part_hs.part_no, '' '', '''') = replace(a.part_no, '' '', '''')) -- 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 MERGE tbl_stock_awal AS TARGET USING (select a.part_no, a.qty_stock_awal, a.tgl_stock_awal, a.satuan_stock_awal from @tbl_stockawal_tmp a inner join tbl_data_part_hs ON replace(tbl_data_part_hs.part_no, '' '', '''') = replace(a.part_no, '' '', '''')) AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_stock_awal, TARGET.date_stock = SOURCE.tgl_stock_awal, TARGET.unit = SOURCE.satuan_stock_awal WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_awal ,SOURCE.part_no ,(select top 1 part_name_customs from tbl_data_part_hs where part_no2=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 part_name from tbl_data_part_hs where part_no2=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_awal ,SOURCE.satuan_stock_awal ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_stockawal set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockawal_upload =@id_stockawal_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
--go
--exec('spi_stock_awal @filename=''stock_awal.xls'', @id_stockawal_upload=8, @tipe=''STA'', @username=''admin'', @typebahan=''RM'' ')
GO