dbo.spi_stockfinishgood

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_stockfinishgood
AS
BEGIN
  SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON

  declare @filecount int, @uruterror int 
 Declare @Size varchar(50),@filedir varchar(100) 
 Declare @FileName varchar(50)
 declare @filedata varchar(100), @logerror varchar(100), @logerror_dtl varchar(100)
 declare @cmdmove varchar(255)
 DECLARE @Files table(Names varchar(250) null)
 Declare @mdate varchar(14)
 declare @dircmd varchar(500)
 Declare @dir varchar(100)
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 
 Declare @tbl_stockfg_temp varchar(MAX) 
 Declare @tbl_stockfg_temp_hdr varchar(MAX) 
 
 set @filedir=@dir+'\inventory\stock_fg'
 set @dircmd='dir '+@dir+'\inventory\stock_fg\*.xls'
  
 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 '01: ' as status, names as description from @Files  where Names like '%File Not Found%'
  return
 end
 
 DELETE @Files WHERE Names LIKE '%%'
 --delete all informational messages
 DELETE @Files WHERE Names LIKE ' %'
 --delete the null values
 DELETE @Files WHERE Names IS NULL

 --get rid of dateinfo
 UPDATE @Files SET Names =RIGHT(Names,(LEN(Names)-20))
 --get rid of leading spaces
 UPDATE @Files SET Names =LTRIM(Names)
 --SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files order by Names
 
 delete from dbo.tbl_stock_fg
 delete from dbo.tbl_stock_fg_hdr
 
 set @tbl_stockfg_temp= 'tbl_stockfg_temp' + replace(convert(varchar(255) , newid()), '-','')
 set @tbl_stockfg_temp_hdr= 'tbl_stockfg_temp_hdr' + replace(convert(varchar(255) , newid()), '-','')
 
  exec ('create table ' + @tbl_stockfg_temp + 
' ( PERIODE varchar(50), LINE_NO varchar(50), LOADING_LINE varchar(50), ASSY_NO varchar(50), ASSY_CODE varchar(50), AWAL_BOX_SF float, AWAL_PCS_SF float, AWAL_BOX_AF float, AWAL_PCS_AF float, IN_BOX_SF float, IN_PCS_SF float, IN_BOX_AF float, IN_PCS_AF float, OUT_BOX_SF float, OUT_PCS_SF float, OUT_BOX_AF float, OUT_PCS_AF float, AKHIR_BOX_SF float, AKHIR_PCS_SF float, AKHIR_BOX_AF float, AKHIR_PCS_AF float )')
  
  exec(
   'create table ' + @tbl_stockfg_temp_hdr + 
' ( from_file_name varchar(500), exec_date datetime , status varchar(50), itemcount int , countrow int , periode varchar(50) ) ')
   
 set @uruterror=1
 DECLARE file_cursor CURSOR FOR 
 SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files order by Names
 OPEN file_cursor
    FETCH NEXT FROM file_cursor INTO @Size, @FileName 
 WHILE @@FETCH_STATUS = 0 
 BEGIN -- begin here
  set @filedata = @filedir +'\'+ @FileName 
  set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @FileName
     set @mdate = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')) 
     set @logerror = @filedir+'\log\error' + @mdate + CONVERT(VARCHAR, @uruterror) + '.xls'
     set @logerror_dtl = @filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.xls'
  exec(
' insert into '+ @tbl_stockfg_temp +
' ( [PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[AWAL_BOX_SF] ,[AWAL_PCS_SF] ,[AWAL_BOX_AF] ,[AWAL_PCS_AF] ,[IN_BOX_SF] ,[IN_PCS_SF] ,[IN_BOX_AF] ,[IN_PCS_AF] ,[OUT_BOX_SF] ,[OUT_PCS_SF] ,[OUT_BOX_AF] ,[OUT_PCS_AF] ,[AKHIR_BOX_SF] ,[AKHIR_PCS_SF] ,[AKHIR_BOX_AF] ,[AKHIR_PCS_AF] ) select [PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[AWAL_BOX_SF] ,[AWAL_PCS_SF] ,[AWAL_BOX_AF] ,[AWAL_PCS_AF] ,[IN_BOX_SF] ,[IN_PCS_SF] ,[IN_BOX_AF] ,[IN_PCS_AF] ,[OUT_BOX_SF] ,[OUT_PCS_SF] ,[OUT_BOX_AF] ,[OUT_PCS_AF] ,[AKHIR_BOX_SF] ,[AKHIR_PCS_SF] ,[AKHIR_BOX_AF] ,[AKHIR_PCS_AF] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;Database='+@filedata +
''', [''Sheet 1$'']) delete from '+@tbl_stockfg_temp+
' where PERIODE is null declare @jmlitem int set @jmlitem = (select count(*) from '+@tbl_stockfg_temp+
') insert into '+ @tbl_stockfg_temp_hdr +
' ( from_file_name ,exec_date ,itemcount ,status ,periode ,countrow ) select '''+ @filedata +
''' ,getdate() ,@jmlitem ,''xxx'' ,PERIODE ,count(PERIODE) from '+@tbl_stockfg_temp+
' group by PERIODE insert into tbl_stock_fg_hdr ( from_file_name ,exec_date ,itemcount ,status ,periode ,countrow ) select from_file_name ,exec_date ,itemcount ,status ,periode ,countrow from '+@tbl_stockfg_temp_hdr+
' DECLARE @id_stock_fg_hdr bigint, @periode varchar(50) DECLARE tbltemp_stockfg_cursor CURSOR FOR select id_stock_fg_hdr, periode from tbl_stock_fg_hdr where status=''xxx'' OPEN tbltemp_stockfg_cursor FETCH NEXT FROM tbltemp_stockfg_cursor into @id_stock_fg_hdr, @periode WHILE @@FETCH_STATUS = 0 BEGIN insert into tbl_stock_fg ( [id_stock_fg_hdr] ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[AWAL_BOX_SF] ,[AWAL_PCS_SF] ,[AWAL_BOX_AF] ,[AWAL_PCS_AF] ,[IN_BOX_SF] ,[IN_PCS_SF] ,[IN_BOX_AF] ,[IN_PCS_AF] ,[OUT_BOX_SF] ,[OUT_PCS_SF] ,[OUT_BOX_AF] ,[OUT_PCS_AF] ,[AKHIR_BOX_SF] ,[AKHIR_PCS_SF] ,[AKHIR_BOX_AF] ,[AKHIR_PCS_AF] ) select @id_stock_fg_hdr ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[AWAL_BOX_SF] ,[AWAL_PCS_SF] ,[AWAL_BOX_AF] ,[AWAL_PCS_AF] ,[IN_BOX_SF] ,[IN_PCS_SF] ,[IN_BOX_AF] ,[IN_PCS_AF] ,[OUT_BOX_SF] ,[OUT_PCS_SF] ,[OUT_BOX_AF] ,[OUT_PCS_AF] ,[AKHIR_BOX_SF] ,[AKHIR_PCS_SF] ,[AKHIR_BOX_AF] ,[AKHIR_PCS_AF] from '+@tbl_stockfg_temp+
' where PERIODE = @periode FETCH NEXT FROM tbltemp_stockfg_cursor into @id_stock_fg_hdr, @periode END CLOSE tbltemp_stockfg_cursor DEALLOCATE tbltemp_stockfg_cursor update tbl_stock_fg_hdr set status=''new'' where status=''xxx'' delete from '+@tbl_stockfg_temp+
' delete from '+@tbl_stockfg_temp_hdr+
' ')
    
  EXEC xp_cmdshell @cmdmove, NO_OUTPUT
  set @uruterror = @uruterror + 1; 
     FETCH NEXT FROM file_cursor INTO @Size, @FileName
 END
 CLOSE file_cursor
 DEALLOCATE file_cursor 
 
 
 
 select '00' as status, COUNT(*) as description from @Files 
 
 exec ('drop table '+ @tbl_stockfg_temp_hdr +'') 
 exec ('drop table '+ @tbl_stockfg_temp +'') 
 
END
GO