dbo.spuload_bc27

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spuload_bc27
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON
 

  if @trx = 'add' 
  begin
   if (select COUNT(*) from [tbl_upload_bc27] where [filename] = @filename and [status_upload] = 'Sukses') > 0
   begin
  select '06' as status, 'File Sudah Pernah Di Upload' as description
  return 
   end
   
   --SELECT '00' AS status, @filepath AS description
   --return
   EXEC (
' DECLARE @cnt INT set @cnt = (select COUNT(*) as cnt FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filepath+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''H0'' or F1=''D0'') --select * -- FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', -- ''Text;Database='+@filepath+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', -- ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''H0'' or F1=''D0'' IF @cnt<1 BEGIN SELECT ''06'' AS status, ''FILE DATA bc27 TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_bc27] ([filename],[filepath],[status_upload], create_by,create_date, update_by, update_date, date_upload, tipe) VALUES ('''+ @filename +''','''+ @filepath +''',''SUKSES'','''+@username+''',GETDATE(), '''+@username+''',GETDATE(), GETDATE(), '''+@tipe+
''') SELECT ''00'' AS status, ''SUKSES '' AS description ')  
   
  end
  if @trx = 'delete' 
  begin
 if (select COUNT(*) from [tbl_upload_bc27] where id_bc27_upload = @id and [status_upload] = 'Deleted') > 0
 begin
  select '06' as status, 'File Sudah Deleted' as description
  return 
 end
 if (select COUNT(*) from [tbl_upload_bc27]  where id_bc27_upload = @id and [status_upload] <> 'SUKSES') > 0
 begin
  select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
  return 
 end
 
 update [tbl_upload_bc27]  set status_upload='Deleted' where id_bc27_upload = @id
 SELECT '00' AS status, 'DATA DELETED' AS description
  end
  
 
END
--go
--exec('[spuload_bc27] @filepath=''C:/wamp/www/pemi/loaderfiles/exim/bc27/'', @filename=''PIB_BERKALA_242_1.csv'', @username=''admin'', @tipe=''bc27'', @trx=''add'', @id=0 ')
GO