dbo.sp_incoming_finisgood

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_incoming_finisgood 
AS
BEGIN
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS 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 @tbl_incoming_fg_temp varchar(MAX) 
 Declare @tbl_incoming_fg_temp_hdr varchar(MAX) 
 
 Declare @dir varchar(100)
    declare @dircmd varchar(500)
    
    set @dir = (select top 1 direktori from dbo.tbl_direktori)
    
    set @filedir=@dir+'\inventory\finishgood'
 set @dircmd='dir '+@dir+'\inventory\finishgood\*.csv'
 

  
 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 tbl_incoming_fg
 --delete from dbo.tbl_incoming_fg_dtl
 
 set @tbl_incoming_fg_temp= 'tbl_incoming_fg_temp' + replace(convert(varchar(255) , newid()), '-','')
 set @tbl_incoming_fg_temp_hdr= 'tbl_incoming_fg_temp_hdr' + replace(convert(varchar(255) , newid()), '-','')
 
    exec ('create table ' + @tbl_incoming_fg_temp + 
' ( PROD_DATE datetime , PERIODE varchar(50) , LINE_NO varchar(50) , LOADING_LINE varchar(50) null, ASSY_NO varchar(50) null, ASSY_CODE varchar(50) null, QTY_BOX_SF float null , QTY_BOX_AF float null , ATOT_BOX_SF float null , ATOT_PCS_SF float null , ATOT_BOX_AF float null , ATOT_PCS_AF float null , BTOT_BOX_SF float null , BTOT_PCS_SF float null , BTOT_BOX_AF float null , BTOT_PCS_AF float null , CTOT_BOX_SF float null , CTOT_PCS_SF float null , CTOT_BOX_AF float null , CTOT_PCS_AF float null , GROUP_NO varchar(50) null, LBJ_NO varchar(50) null ) create table ' + @tbl_incoming_fg_temp_hdr + 
' ( from_file_name varchar(500), exec_date datetime , status varchar(50), itemcount int , countrow int , incoming_date date ) ')
 
 Declare @dynmaicsql as  nvarchar(1000);
 Declare @dynamicparam as nvarchar(1000);
 
 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'
  
    Set @dynmaicsql =  
' set @icount =( select count(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''text;HDR=NO;IMEX=1;FMT=Delimited;Database='+@filedir +
''', ''SELECT * FROM ['+@FileName+
'] '') WHERE F5=''ASSY_NO'' and f6=''ASSY_CODE'' ) '
  Set @dynamicparam = '@icount int output'
  declare @jmldata int
  execute sp_executesql @dynmaicsql, @dynamicparam, @jmldata output
  if  @jmldata = 0 
  begin
   exec ('drop table '+ @tbl_incoming_fg_temp_hdr +
' drop table '+ @tbl_incoming_fg_temp +'')
   EXEC xp_cmdshell @cmdmove, NO_OUTPUT 
   CLOSE file_cursor
   DEALLOCATE file_cursor 
   
   select '07' as status, 'Data Tidak Sesuai, Lakukan Load Ulang' as description
   return  
  end
  
  BEGIN TRY  
  
  exec(
' BEGIN TRY insert into '+ @tbl_incoming_fg_temp +
' ( [PROD_DATE] ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[QTY_BOX_SF] ,[QTY_BOX_AF] ,[ATOT_BOX_SF] ,[ATOT_PCS_SF] ,[ATOT_BOX_AF] ,[ATOT_PCS_AF] ,[BTOT_BOX_SF] ,[BTOT_PCS_SF] ,[BTOT_BOX_AF] ,[BTOT_PCS_AF] ,[CTOT_BOX_SF] ,[CTOT_PCS_SF] ,[CTOT_BOX_AF] ,[CTOT_PCS_AF] ,[GROUP_NO] ,[LBJ_NO] ) select (case when LEN(PROD_DATE) = 7 then (SUBSTRING(cast([PROD_DATE] as varchar), 4, 4) + ''-'' + SUBSTRING(cast([PROD_DATE] as varchar), 2,2) + ''-'' + ''0''+SUBSTRING(cast([PROD_DATE] as varchar), 1,1) ) else (SUBSTRING(cast([PROD_DATE] as varchar), 5, 4) + ''-'' + SUBSTRING(cast([PROD_DATE] as varchar), 3,2) + ''-'' + SUBSTRING(cast([PROD_DATE] as varchar), 1,2) ) end ) as PROD_DATE ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[QTY_BOX_SF] ,[QTY_BOX_AF] ,[ATOT_BOX_SF] ,[ATOT_PCS_SF] ,[ATOT_BOX_AF] ,[ATOT_PCS_AF] ,[BTOT_BOX_SF] ,[BTOT_PCS_SF] ,[BTOT_BOX_AF] ,[BTOT_PCS_AF] ,[CTOT_BOX_SF] ,[CTOT_PCS_SF] ,[CTOT_BOX_AF] ,[CTOT_PCS_AF] ,[GROUP_NO] ,[LBJ_NO] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''text;HDR=YES;IMEX=1;FMT=Delimited;Database='+@filedir +
''', ''SELECT * FROM ['+@FileName+
'] '') END TRY BEGIN CATCH SELECT ''error'' AS status ,ERROR_MESSAGE() AS description; END CATCH; delete from '+@tbl_incoming_fg_temp+
' where ASSY_NO is null delete from '+@tbl_incoming_fg_temp+
' where LOADING_LINE is null declare @jmlitem int set @jmlitem = (select count(*) from '+@tbl_incoming_fg_temp+
') insert into '+ @tbl_incoming_fg_temp_hdr +
' ( from_file_name ,exec_date ,itemcount ,status ,incoming_date ,countrow ) select '''+ @filedata +
''' ,getdate() ,@jmlitem ,''xxx'' ,PROD_DATE ,count(PROD_DATE) from '+@tbl_incoming_fg_temp+
' group by PROD_DATE insert into tbl_incoming_fg ( from_file_name ,exec_date ,itemcount ,status ,incoming_date ,countrow ) select from_file_name ,exec_date ,itemcount ,status ,incoming_date ,countrow from '+@tbl_incoming_fg_temp_hdr+
' DECLARE @id_incoming_fg_hdr int, @incoming_date datetime DECLARE tbltemp_incomingfg_cursor CURSOR FOR select id_incoming_fg_hdr, incoming_date from tbl_incoming_fg where status=''xxx'' OPEN tbltemp_incomingfg_cursor FETCH NEXT FROM tbltemp_incomingfg_cursor into @id_incoming_fg_hdr, @incoming_date WHILE @@FETCH_STATUS = 0 BEGIN insert into tbl_incoming_fg_dtl ( [id_incoming_fg] ,[PROD_DATE] ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,[QTY_BOX_SF] ,[QTY_BOX_AF] ,[ATOT_BOX_SF] ,[ATOT_PCS_SF] ,[ATOT_BOX_AF] ,[ATOT_PCS_AF] ,[BTOT_BOX_SF] ,[BTOT_PCS_SF] ,[BTOT_BOX_AF] ,[BTOT_PCS_AF] ,[CTOT_BOX_SF] ,[CTOT_PCS_SF] ,[CTOT_BOX_AF] ,[CTOT_PCS_AF] ,[GROUP_NO] ,[LBJ_NO] ) select @id_incoming_fg_hdr ,[PROD_DATE] ,[PERIODE] ,[LINE_NO] ,[LOADING_LINE] ,[ASSY_NO] ,[ASSY_CODE] ,isnull([QTY_BOX_SF],0) ,isnull([QTY_BOX_AF],0) ,isnull([ATOT_BOX_SF],0) ,isnull([ATOT_PCS_SF],0) ,isnull([ATOT_BOX_AF],0) ,isnull([ATOT_PCS_AF],0) ,isnull([BTOT_BOX_SF],0) ,isnull([BTOT_PCS_SF],0) ,isnull([BTOT_BOX_AF],0) ,isnull([BTOT_PCS_AF],0) ,isnull([CTOT_BOX_SF],0) ,isnull([CTOT_PCS_SF],0) ,isnull([CTOT_BOX_AF],0) ,isnull([CTOT_PCS_AF],0) ,[GROUP_NO] ,[LBJ_NO] from '+@tbl_incoming_fg_temp+
' where PROD_DATE = @incoming_date FETCH NEXT FROM tbltemp_incomingfg_cursor into @id_incoming_fg_hdr, @incoming_date END CLOSE tbltemp_incomingfg_cursor DEALLOCATE tbltemp_incomingfg_cursor update tbl_incoming_fg set status=''new'' where status=''xxx'' delete from '+@tbl_incoming_fg_temp+
' delete from '+@tbl_incoming_fg_temp_hdr+
' ')
  
  END TRY
  BEGIN CATCH
   exec(' drop table '+ @tbl_incoming_fg_temp_hdr +' ') 
   DECLARE @ErrMsg1 nvarchar(4000), @ErrSeverity1 int
   Set @ErrMsg1 = ERROR_MESSAGE()
   set @ErrSeverity1 = ERROR_SEVERITY()
   select @ErrSeverity1 as status, @ErrMsg1 as description
   RAISERROR(@ErrMsg1, @ErrSeverity1, 1) 
  END CATCH
    
  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 
 
 
 exec ('drop table '+ @tbl_incoming_fg_temp_hdr +'') 
 exec ('drop table '+ @tbl_incoming_fg_temp +'') 
 
 select '00' as status, convert(varchar, COUNT(*))+' FILE SUKSES LOAD' as description from @Files
 
 
END

GO