dbo.spuload_bb_added_adjustment_daily

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spuload_bb_added_adjustment_daily
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
    SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON 
  if @tipe='ADJUSTMENT_DAILY'
 begin 
   if @trx = 'add' 
   begin
    if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where [filename] = @filename and [status_upload] = 'SUKSES') > 0
    begin
   select '06' as status, 'File Sudah Pernah Di Upload' 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='+@filepath +''', ['+@tipe+
'$]) WHERE F1=''partno2'' and F2=''tgl_adjustment'' and f3=''qty_in'' and f4=''qty_out'' and f5=''amount_in'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_bb_added_adjustment_daily] ([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 TRANSFERED'' AS description ')  
    
   end
   if @trx = 'delete' 
   begin
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] = 'Deleted') > 0
  begin
   select '06' as status, 'File Sudah Deleted' as description
   return 
  end
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] <> 'SUKSES') > 0
  begin
   select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
   return 
  end
  
  update tbl_upload_bb_added_adjustment_daily set status_upload='Deleted' where id_upload_adjustment_daily = @id
  SELECT '00' AS status, 'DATA DELETED' AS description
   end
 end
 if @tipe='ADJUSTMENT_STO'
 begin 
   if @trx = 'add' 
   begin
    if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where [filename] = @filename and [status_upload] = 'SUKSES') > 0
    begin
   select '06' as status, 'File Sudah Pernah Di Upload' 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='+@filepath +''', ['+@tipe+
'$]) WHERE F1=''partno2'' and F2=''tgl_adjustment'' and f3=''qty'' and f4=''amount'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_bb_added_adjustment_daily] ([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 TRANSFERED'' AS description ')  
    
   end
   if @trx = 'delete' 
   begin
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] = 'Deleted') > 0
  begin
   select '06' as status, 'File Sudah Deleted' as description
   return 
  end
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] <> 'SUKSES') > 0
  begin
   select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
   return 
  end
  
  update tbl_upload_bb_added_adjustment_daily set status_upload='Deleted' where id_upload_adjustment_daily = @id
  SELECT '00' AS status, 'DATA DELETED' AS description
   end
 end
 if @tipe='STO'
 begin 
   if @trx = 'add' 
   begin
    if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where [filename] = @filename and [status_upload] = 'SUKSES') > 0
    begin
   select '06' as status, 'File Sudah Pernah Di Upload' 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='+@filepath +''', ['+@tipe+
'$]) WHERE F1=''partno2'' and F2=''tgl_sto'' and f3=''qty'' and f4=''amount'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_bb_added_adjustment_daily] ([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 TRANSFERED'' AS description ')  
    
   end
   if @trx = 'delete' 
   begin
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] = 'Deleted') > 0
  begin
   select '06' as status, 'File Sudah Deleted' as description
   return 
  end
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] <> 'SUKSES') > 0
  begin
   select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
   return 
  end
  
  update tbl_upload_bb_added_adjustment_daily set status_upload='Deleted' where id_upload_adjustment_daily = @id
  SELECT '00' AS status, 'DATA DELETED' AS description
   end
 end
 if @tipe='REQUIREMENT'
 begin 
   if @trx = 'add' 
   begin
    if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where [filename] = @filename and [status_upload] = 'SUKSES') > 0
    begin
   select '06' as status, 'File Sudah Pernah Di Upload' 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='+@filepath +''', ['+@tipe+
'$]) WHERE F1=''partno2'' and F2=''tgl_requirement'' and f3=''qty'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FORMAT FILE TIDAK SESUAI'' AS description RETURN END INSERT INTO [tbl_upload_bb_added_adjustment_daily] ([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 TRANSFERED'' AS description ')  
    
   end
   if @trx = 'delete' 
   begin
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] = 'Deleted') > 0
  begin
   select '06' as status, 'File Sudah Deleted' as description
   return 
  end
  if (select COUNT(*) from tbl_upload_bb_added_adjustment_daily where id_upload_adjustment_daily = @id and [status_upload] <> 'SUKSES') > 0
  begin
   select '06' as status, 'DATA TIDAK BISA DI DELETE' as description
   return 
  end
  
  update tbl_upload_bb_added_adjustment_daily set status_upload='Deleted' where id_upload_adjustment_daily = @id
  SELECT '00' AS status, 'DATA DELETED' AS description
   end
 end
 
END

GO