dbo.spi_alatkantor_out

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_alatkantor_out
(@filename varchar(100), @id_uploadak_out_upload bigint, @tipe varchar(50),  @username varchar(100) )
AS
BEGIN
    --
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS 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\alat_kantor_out'
 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_uploadalatkantor_out where id_uploadak_out_upload=@id_uploadak_out_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 +
''', [''AK OUT$'']) WHERE F1=''doc_no'' AND F2=''doc_date'' and F3=''part_no'' and F4=''qty'' and F5=''UOM'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA ALAT KANTOR TIDAK SESUAI'' AS description RETURN END Declare @tbl_alat_kantor_out table ( doc_no varchar(100) null, doc_date date null, part_no varchar(100) null, qty float null, qty_out float null, UOM varchar(100) null, filename varchar(255) null ); insert into @tbl_alat_kantor_out ( doc_no, doc_date, part_no , qty , qty_out , UOM , filename ) select doc_no, doc_date, part_no , qty , qty , UOM , ''' + @fullpath + 
''' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''AK OUT$'']) delete from @tbl_alat_kantor_out where doc_no is null or dbo.trim(doc_no) = '''' delete from @tbl_alat_kantor_out where part_no is null or dbo.trim(part_no) = '''' declare @part_no varchar(100), @not_found varchar(max) set @not_found = '''' DECLARE db_cursor CURSOR FOR select part_no from @tbl_alat_kantor_out except select part_no from tbl_data_alat_kantor OPEN db_cursor FETCH NEXT FROM db_cursor INTO @part_no WHILE @@FETCH_STATUS = 0 BEGIN set @not_found = @not_found + @part_no + '' '' FETCH NEXT FROM db_cursor INTO @part_no END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@not_found) <> '''' begin select ''06 '' as status, '' PART TIDAK ADA, PEBAIKI DAHULU '' + @not_found as description return end declare @jam date set @jam = getdate() MERGE tbl_alatkantor_out AS TARGET USING (select doc_no, doc_date, part_no , sum(qty) as qty, sum(qty_out) as qty_out, UOM , filename from @tbl_alat_kantor_out group by doc_no, doc_date, part_no , UOM , filename ) AS SOURCE ON SOURCE.doc_no = TARGET.doc_no and SOURCE.doc_date = TARGET.doc_date and SOURCE.part_no = TARGET.part_no WHEN MATCHED THEN UPDATE set TARGET.qty = SOURCE.qty, TARGET.qty_out = SOURCE.qty_out WHEN NOT MATCHED BY TARGET THEN INSERT ( doc_no, doc_date, part_no , qty , qty_out , UOM , part_name, doc_out_to, approved_date, status_data, filename ,create_by, update_by, create_date, update_date) values ( doc_no, doc_date, part_no , qty , qty_out , UOM , (select top 1 part_name from tbl_data_alat_kantor where part_no = SOURCE.part_no) , 2, doc_date, ''APPROVED'', filename, '''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_uploadalatkantor_out set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_uploadak_out_upload =@id_uploadak_out_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
GO