set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[prc_getFleetRequisitionInfo]
(
	@USER_NAME  varchar(100),
	@StatusID int = NULL
)
AS

if @USER_NAME = 'ALL' 
	set @USER_NAME = NULL


SELECT DISTINCT fleetRequisitionID, ConditionID into #TMP FROM 
(
SELECT 
	F.fleetRequisitionID,	CASE WHEN EXISTS (Select * 
							from 	relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1
							Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
							AND		R.fleetRequisitionID = F1.fleetRequisitionID
							AND		F1.fleetRequisitionID = F.fleetRequisitionID
							AND		wsOrderStatusID = 3) 
		THEN 4
		WHEN 	(Select count(*) from 	relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1
							Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
							AND		R.fleetRequisitionID = F1.fleetRequisitionID
							AND		F1.fleetRequisitionID = F.fleetRequisitionID
							AND		wsOrderStatusID = 2) 
							= 
							(Select count(*) from 	relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1
							Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
							AND		R.fleetRequisitionID = F1.fleetRequisitionID
							AND		F1.fleetRequisitionID = F.fleetRequisitionID
							)				
		THEN 3  
		WHEN	EXISTS (Select * 
							from 	relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1
							Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
							AND		R.fleetRequisitionID = F1.fleetRequisitionID
							AND		F1.fleetRequisitionID = F.fleetRequisitionID
							AND		wsOrderStatusID = 2)
		THEN 2
		ELSE 1
		END as ConditionID
from 	relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F
Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
AND		R.fleetRequisitionID = F.fleetRequisitionID
AND                   ((F.onlineEquipmentPurchase <> 1) OR (F.onlineEquipmentPurchase=1 AND OnlinePurchaseID IS NOT NULL))  
) A

IF @StatusID IS NOT NULL And @StatusID<>0
BEGIN
	DELETE FROM #TMP
	WHERE
	CASE 
		WHEN ConditionID>1 THEN ConditionID-1
		ELSE 1
	END NOT LIKE @StatusID
END

select F1.fleetRequisitionId as id  ,R.fleetRequisitionapprovalid, B.costCenter as costCenter, fleetRequisitionDate as Date,
fleetApprovalProcessAuthorizorNTUserName as Authorizer, fleetRequisitionMadeBy as Requestor,
CASE WHEN T.ConditionID = 1 then (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = 1)
else (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = T.ConditionID - 1) end As Status,
 CASE 
		WHEN  fleetRequisitionApprovalBy IS NULL THEN  fleetApprovalProcessAuthorizorNTUserName 
  ELSE   FLEETrequisitionApprovalBy end  as AssignedTo,
OnlinePurchaseID,
CASE onlineEquipmentPurchase
WHEN 1 THEN 'Eq. Purch.'
ELSE 'Fleet'
END As Type
FROM relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1, #TMP T, lkpBranch B
					Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
					AND		R.fleetRequisitionID = F1.fleetRequisitionID
					AND		R.fleetRequisitionID = T.fleetRequisitionID
					AND 		F1.branchID = B.branchID
					AND		R.FleetApprovalProcessID = (Select max(FleetApprovalProcessID) from  relFleetRequisitionApproval A 
											Where A.fleetRequisitionID = T.fleetRequisitionID)
					AND		T.ConditionID = 3
					AND 		(P.fleetApprovalProcessAuthorizorNTUserName = ISNULL(@USER_NAME, P.fleetApprovalProcessAuthorizorNTUserName) 
							OR  P.fleetApprovalProcessAuthorizorDelegateNTUserName = ISNULL(@USER_NAME,P.fleetApprovalProcessAuthorizorDelegateNTUserName))

UNION
SELECT F1.fleetRequisitionId as id  ,R.fleetRequisitionapprovalid, B.costCenter as costCenter, fleetRequisitionDate as Date,
fleetApprovalProcessAuthorizorNTUserName as Authorizer, fleetRequisitionMadeBy as Requestor,
CASE WHEN T.ConditionID = 1 then (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = 1)
else (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = T.ConditionID - 1) end As Status, 
 CASE 
		WHEN  fleetRequisitionApprovalBy IS NULL THEN  fleetApprovalProcessAuthorizorNTUserName 
  ELSE   FLEETrequisitionApprovalBy end  as AssignedTo,
OnlinePurchaseID,
CASE onlineEquipmentPurchase
WHEN 1 THEN 'Eq. Purch.'
ELSE 'Fleet'
END As Type
FROM relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1, #TMP T, lkpBranch B
					Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
					AND		R.fleetRequisitionID = F1.fleetRequisitionID
					AND		R.fleetRequisitionID = T.fleetRequisitionID
					AND 		F1.branchID = B.branchID
					AND		R.FleetApprovalProcessID = (Select min(FleetApprovalProcessID) from  relFleetRequisitionApproval A 
											Where A.fleetRequisitionID = T.fleetRequisitionID)
					AND		T.ConditionID = 1
					AND 		(P.fleetApprovalProcessAuthorizorNTUserName = ISNULL(@USER_NAME, P.fleetApprovalProcessAuthorizorNTUserName) 
							OR  P.fleetApprovalProcessAuthorizorDelegateNTUserName = ISNULL(@USER_NAME,P.fleetApprovalProcessAuthorizorDelegateNTUserName))

UNION
SELECT F1.fleetRequisitionId as id  ,R.fleetRequisitionapprovalid, B.costCenter as costCenter, fleetRequisitionDate as Date,
fleetApprovalProcessAuthorizorNTUserName as Authorizer, fleetRequisitionMadeBy as Requestor,
CASE WHEN T.ConditionID = 1 then (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = 1)
else (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = T.ConditionID - 1) end As Status,
 CASE 
		WHEN  fleetRequisitionApprovalBy IS NULL THEN  fleetApprovalProcessAuthorizorNTUserName 
  ELSE   FLEETrequisitionApprovalBy end  as AssignedTo,
OnlinePurchaseID,
CASE onlineEquipmentPurchase
WHEN 1 THEN 'Eq. Purch.'
ELSE 'Fleet'
END As Type
FROM relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1, #TMP T, lkpBranch B
					Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId
					AND		R.fleetRequisitionID = F1.fleetRequisitionID
					AND		R.fleetRequisitionID = T.fleetRequisitionID
					AND 		F1.branchID = B.branchID
					AND		R.FleetApprovalProcessID = (Select min(FleetApprovalProcessID) from  relFleetRequisitionApproval A 
											Where A.fleetRequisitionID = T.fleetRequisitionID AND wsOrderStatusID=1)
					AND		T.ConditionID = 2
					AND 		(P.fleetApprovalProcessAuthorizorNTUserName = ISNULL(@USER_NAME, P.fleetApprovalProcessAuthorizorNTUserName) 
							OR  P.fleetApprovalProcessAuthorizorDelegateNTUserName = ISNULL(@USER_NAME,P.fleetApprovalProcessAuthorizorDelegateNTUserName))

UNION
SELECT F1.fleetRequisitionId as id  ,R.fleetRequisitionapprovalid, B.costCenter as costCenter, fleetRequisitionDate as Date,
fleetApprovalProcessAuthorizorNTUserName as Authorizer, fleetRequisitionMadeBy as Requestor,
CASE WHEN T.ConditionID = 1 then (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = 1)
else (select wsOrderStatus from lkpWSOrderStatus where wsOrderSTatusID = T.ConditionID - 1) end As Status,
 CASE 
		WHEN  fleetRequisitionApprovalBy IS NULL THEN  fleetApprovalProcessAuthorizorNTUserName 
  ELSE   FLEETrequisitionApprovalBy end  as AssignedTo,
OnlinePurchaseID,
CASE onlineEquipmentPurchase
WHEN 1 THEN 'Eq. Purch.'
ELSE 'Fleet'
END As Type		
FROM relFleetRequisitionApproval R, 	relFleetApprovalProcess P, 	tblFleetRequisition F1, #TMP T, lkpBranch B
					Where	R.fleetApprovalProcessId = P.fleetApprovalProcessId	
					AND		R.fleetRequisitionID = F1.fleetRequisitionID
					AND		R.fleetRequisitionID = T.fleetRequisitionID
					AND 		F1.branchID = B.branchID
					AND		R.FleetApprovalProcessID = (Select min(FleetApprovalProcessID) from  relFleetRequisitionApproval A 
											Where A.fleetRequisitionID = T.fleetRequisitionID AND wsOrderStatusID=3)
					AND		T.ConditionID = 4
					AND 		(P.fleetApprovalProcessAuthorizorNTUserName = ISNULL(@USER_NAME, P.fleetApprovalProcessAuthorizorNTUserName) 
							OR  P.fleetApprovalProcessAuthorizorDelegateNTUserName = ISNULL(@USER_NAME,P.fleetApprovalProcessAuthorizorDelegateNTUserName))

order by Date desc