dbo.spi_bom

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

 
 declare @filecount int
 Declare @FileName varchar(50)
 declare @cmdmove varchar(255)
 DECLARE @Files table(Names varchar(250) null)
 -- bulk
 declare @filexls varchar(MAX)
 Declare @mdate varchar(14)
 Declare @Size varchar(50)
 Declare @tbl_bom_temp varchar(MAX)
    DECLARE @bulkinsert NVARCHAR(MAX)
 ----
 declare @filedir varchar(200), @dircmd varchar(500)
 Declare @dir varchar(100)
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\production\bom'
 set @dircmd='dir '+@filedir+'\*.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 dbo.tbl_bom_hdr
 --delete from dbo.tbl_bom_dtl
 
 
 set @tbl_bom_temp= 'tbl_bom_temp' + replace(convert(varchar(255) , newid()), '-','')
 
  exec ('create table ' + @tbl_bom_temp + 
' ( ASSY_CODE varchar(10), ASSY_NO varchar(25) null, PART_NO varchar(25), PARTNO2 varchar(25), QTY float null, FLAG varchar(1) null, STATUS varchar(2) null, filename varchar(500) )')
 
 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 @filexls = @filedir+'\' + @FileName 
 set @cmdmove = 'MOVE /Y '+@filedir+'\' + @FileName + ' '+@filedir+'\log\' + @FileName
    
    BEGIN TRY
    
 exec(
' insert into '+ @tbl_bom_temp +
' ( ASSY_CODE,ASSY_NO,PART_NO,PARTNO2,QTY,FLAG, STATUS,filename) select ASSY_CODE,ASSY_NO,PART_NO,PARTNO2,QTY,FLAG, STATUS, '''+@filexls +
''' FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filedir+
';HDR=YES;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
']'') delete from '+@tbl_bom_temp+
' where ASSY_CODE is null ' ) 
    
    END TRY
 BEGIN CATCH
 exec ('drop table '+ @tbl_bom_temp +'')
 select ERROR_NUMBER() as status, ERROR_MESSAGE() as description
 
 END CATCH; 
    
 --EXEC sp_executesql @bulkinsert
 --move
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 ---
 FETCH NEXT FROM file_cursor INTO @Size, @FileName
 END
 CLOSE file_cursor
 DEALLOCATE file_cursor 
  
 --exec('select * from '+@tbl_bom_temp+'')

 
 
 exec (
' declare @now date set @now = getdate() BEGIN TRY BEGIN TRANSACTION insert into dbo.tbl_bom_hdr ( ASSY_CODE, ASSY_NO, filename, itemcount, dateexec) select ASSY_CODE, ASSY_NO, filename, (count(ASSY_CODE)) as cnt, @now from '+ @tbl_bom_temp +
' as t where not exists(select ASSY_CODE from dbo.tbl_bom_hdr where dbo.tbl_bom_hdr.ASSY_CODE = t.ASSY_CODE ) group by ASSY_NO, ASSY_CODE, filename MERGE dbo.tbl_bom_dtl AS TARGET USING '+ @tbl_bom_temp +
' AS SOURCE ON ( SOURCE.ASSY_CODE = TARGET.ASSY_CODE and SOURCE.PARTNO2 = TARGET.PARTNO2 ) WHEN MATCHED THEN UPDATE set TARGET.QTY = SOURCE.QTY ,TARGET.status_bom = (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then ''not found part no'' else ''ok'' end) WHEN NOT MATCHED BY TARGET THEN INSERT ( ASSY_CODE, ASSY_NO,PART_NO, QTY, PARTNO2, FLAG, STATUS ,status_bom ) VALUES ( SOURCE.ASSY_CODE, SOURCE.ASSY_NO,SOURCE.PART_NO, SOURCE.QTY, SOURCE.PARTNO2, SOURCE.FLAG, SOURCE.STATUS , (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then ''not found part no'' else ''ok'' end) ) ; drop table '+ @tbl_bom_temp +
' COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT > 0 ROLLBACK drop table '+ @tbl_bom_temp +
' -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int Set @ErrMsg = ERROR_MESSAGE() set @ErrSeverity = ERROR_SEVERITY() select @ErrSeverity as status, @ErrMsg as description RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH ')
 
 --exec('select * from '+@tbl_bom_temp+'')
 --exec('drop table '+ @tbl_bom_temp +'')
 select '00' as status, COUNT(*) as description from @Files  
 

END
--go
--exec [dbo].[spi_bom]
--go
GO