dbo.sp_vgi

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_vgi
AS
BEGIN
  SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
 declare @now datetime
 set @now = GETDATE()
 declare @filecount int, @uruterror int 
 Declare @Size varchar(50)
 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 @filedir varchar(100)
 Declare @dir varchar(100)
 declare @dircsv varchar(500)
 declare @nama_file_replace varchar(200)
 --
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\vgi'
 set @dircsv='dir '+@dir+'\inventory\vgi\*.xls'
  
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircsv
 
 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
 
   
 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) + '.txt'
     set @logerror_dtl = @filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
  set @nama_file_replace = REPLACE(@FileName, '.xls', '')
  BEGIN TRY
     exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+ @filedata +''', [' + @nama_file_replace + 
'$]) where F1=''CCNICP'' and F2=''CCNITM'' and F3=''CCQISS'' and F4=''CCCLFT'' and F5=''CCCREN'' and F6=''CCDCDT'' ) if @cnt < 1 begin select ''DATA KOSONG/DATA TIDAK VALID. CEKFILE DAN ULANGI COMMAND'' as description,''06'' as status return end declare @tmptblhdr table( ISSUE_NO varchar(100) NULL, PART_NO varchar(100) NULL, ISSUE_QTY varchar(100) NULL, PLACE_TO varchar(100) NULL, REASON varchar(100) NULL, REG_DATE date NULL ) declare @tmp_inserted table ( ISSUE_NO varchar(100) null ,REG_DATE date null ) declare @tmp_tbl_inserted table ( id_vgi_hdr bigint ,ISSUE_NO varchar(100) null ,REG_DATE date null ) insert into @tmptblhdr ( ISSUE_NO, PART_NO, ISSUE_QTY, PLACE_TO, REASON, REG_DATE ) select CCNICP, CCNITM, CCQISS, CCCLFT, CCCREN, convert(date, STR(CCDCDT), 120) as REG_DATE FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@filedata +''', [' + @nama_file_replace + 
'$]) delete from @tmptblhdr where ISSUE_NO = '''' or ISSUE_NO is null declare @cnt2 int set @cnt2 = ( select count(*) from (select ISSUE_NO from @tmptblhdr group by ISSUE_NO ) zz ) declare @cnt3 int set @cnt3 =( select count(*) as cnt from (select * from ( SELECT tbl_vgi_dtl.issue_no, CASE WHEN EXISTS (SELECT x.ISSUE_NO FROM @tmptblhdr x WHERE x.ISSUE_NO = tbl_vgi_dtl.issue_no) THEN ''TRUE'' ELSE ''FALSE'' END AS sts FROM tbl_vgi_dtl group by tbl_vgi_dtl.issue_no ) xx where xx.sts=''TRUE'' ) xxx) insert into @tmp_inserted ( ISSUE_NO ,REG_DATE ) select ISSUE_NO ,REG_DATE from @tmptblhdr group by ISSUE_NO, REG_DATE declare @tblissuenodahada table ( issue_no varchar(100), sts varchar(50) ) insert into @tblissuenodahada (issue_no ) select issue_no from ( SELECT tbl_vgi_dtl.issue_no, CASE WHEN EXISTS (SELECT x.ISSUE_NO FROM @tmptblhdr x WHERE x.ISSUE_NO = tbl_vgi_dtl.issue_no and x.REG_DATE=tbl_vgi_dtl.vgi_date) THEN ''TRUE'' ELSE ''FALSE'' END AS sts FROM tbl_vgi_dtl group by tbl_vgi_dtl.issue_no, tbl_vgi_dtl.vgi_date) xx where xx.sts=''TRUE'' if @cnt3 = 0 begin MERGE tbl_vgi_hdr AS TARGET USING @tmp_inserted AS SOURCE ON (SOURCE.ISSUE_NO = TARGET.ISSUE_NO and SOURCE.REG_DATE=TARGET.REG_DATE) WHEN NOT MATCHED BY TARGET THEN insert ( from_file_name ,exec_date ,ISSUE_NO ,REG_DATE ,itemcount ,status ) values ( '''+ @filedata +
''' ,getdate() ,ISSUE_NO ,REG_DATE ,(select count(*) from @tmptblhdr a where a.ISSUE_NO=SOURCE.ISSUE_NO and a.REG_DATE=SOURCE.REG_DATE ) , ''new'' ) output inserted.id_vgi_hdr,inserted.ISSUE_NO,inserted.REG_DATE into @tmp_tbl_inserted ; INSERT INTO tbl_vgi_dtl ( id_vgi_hdr, issue_no, vgi_date, r1, r2, part_number, qty ) select a.id_vgi_hdr ,b.ISSUE_NO ,b.REG_DATE ,b.PLACE_TO ,b.REASON ,b.PART_NO ,b.ISSUE_QTY from @tmp_tbl_inserted a inner join @tmptblhdr b on a.ISSUE_NO=b.ISSUE_NO and a.REG_DATE=b.REG_DATE end if @cnt3 > 0 begin MERGE tbl_vgi_hdr AS TARGET USING (select ISSUE_NO, REG_DATE from @tmp_inserted where ISSUE_NO not in (select issue_no from @tblissuenodahada) ) AS SOURCE ON (SOURCE.ISSUE_NO = TARGET.ISSUE_NO and SOURCE.REG_DATE=TARGET.REG_DATE) WHEN NOT MATCHED BY TARGET THEN insert ( from_file_name ,exec_date ,ISSUE_NO ,REG_DATE ,itemcount ,status ) values ( '''+ @filedata +
''' ,getdate() ,ISSUE_NO ,REG_DATE ,(select count(*) from @tmptblhdr a where a.ISSUE_NO=SOURCE.ISSUE_NO and a.REG_DATE=SOURCE.REG_DATE ) , ''new'' ) output inserted.id_vgi_hdr,inserted.ISSUE_NO,inserted.REG_DATE into @tmp_tbl_inserted ; INSERT INTO tbl_vgi_dtl ( id_vgi_hdr, issue_no, vgi_date, r1, r2, part_number, qty ) select a.id_vgi_hdr ,b.ISSUE_NO ,b.REG_DATE ,b.PLACE_TO ,b.REASON ,b.PART_NO ,isnull(b.ISSUE_QTY, 0) from @tmp_tbl_inserted a inner join @tmptblhdr b on a.ISSUE_NO=b.ISSUE_NO and a.REG_DATE=b.REG_DATE --select ISSUE_NO, PART_NO, sum(cast(ISSUE_QTY as float)) , PLACE_TO, REASON, REG_DATE from @tmptblhdr --group by ISSUE_NO, PART_NO, PLACE_TO, REASON, REG_DATE --return MERGE tbl_vgi_dtl AS TARGET USING (select ISSUE_NO, PART_NO, sum(cast(ISSUE_QTY as float)) as ISSUE_QTY , PLACE_TO, REASON, REG_DATE from @tmptblhdr group by ISSUE_NO, PART_NO, PLACE_TO, REASON, REG_DATE) AS SOURCE ON (SOURCE.ISSUE_NO = TARGET.issue_no and SOURCE.REG_DATE=TARGET.vgi_date) WHEN MATCHED AND SOURCE.PLACE_TO=TARGET.r1 and TARGET.r2 = SOURCE.REASON and TARGET.part_number = SOURCE.PART_NO THEN UPDATE set TARGET.issue_no = SOURCE.ISSUE_NO, TARGET.vgi_date = SOURCE.REG_DATE, TARGET.r1 = SOURCE.PLACE_TO, TARGET.r2 = SOURCE.REASON, TARGET.part_number = SOURCE.PART_NO, TARGET.qty = SOURCE.ISSUE_QTY ; select ''00'' as status, ''DATA UPDATED - DATA SUDAH DI UPDDATE'' as description end --MERGE tbl_vgi_dtl AS TARGET --USING @tmptblhdr AS SOURCE --ON (SOURCE.ISSUE_NO = TARGET.issue_no and SOURCE.REG_DATE=TARGET.vgi_date) --WHEN MATCHED AND SOURCE.PLACE_TO=TARGET.r1 --and TARGET.r1 = SOURCE.PLACE_TO --and TARGET.r2 = SOURCE.REASON --and TARGET.part_number = SOURCE.PART_NO --THEN --UPDATE set --TARGET.issue_no = SOURCE.ISSUE_NO, --TARGET.vgi_date = SOURCE.REG_DATE, --TARGET.r1 = SOURCE.PLACE_TO, --TARGET.r2 = SOURCE.REASON, --TARGET.part_number = SOURCE.PART_NO, --TARGET.qty = SOURCE.ISSUE_QTY --WHEN NOT MATCHED BY TARGET THEN --INSERT ( --id_vgi_hdr, --issue_no, --vgi_date, --r1, --r2, --part_number, --qty --) values ( -- @vgi_hdr_id -- ,SOURCE.ISSUE_NO -- ,SOURCE.REG_DATE -- ,SOURCE.PLACE_TO -- ,SOURCE.REASON -- ,SOURCE.PART_NO -- ,SOURCE.ISSUE_QTY --) --; --select * from dbo.tbl_vgi_hdr --select * from dbo.tbl_vgi_dtl ')
  END TRY
  BEGIN CATCH
     -- Raise an error with the details of the exception
    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
    Set @ErrMsg = ERROR_MESSAGE()
    set @ErrSeverity = ERROR_SEVERITY()
    select @ErrMsg as description,@ErrSeverity as status
    RAISERROR(@ErrMsg, @ErrSeverity, 1)
  END CATCH; 
  
  
  update dbo.tbl_upload_vgi set status_upload = 'DATA PROCESSED' where filename=@FileName
 
  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 
END
GO