About the Vehicles Entity VehicleStatusID Field Sample Validation Script

This field's validation script automatically sets the vehicle's status automatically each time the record is saved. Within the context of the sample application, this requirement is defined in BPR11: Automatically Track Vehicle Status and Mileage and the implementation is described in About the Data Quality Design for the Sample Application.

The full text of the script appears below. This script queries other entities in the database to determine if outstanding Service Tickets or Rental Agreements exist for this vehicle to assist with setting the status appropriately. This is the most complicated of the validation scripts as it sets rather than merely checks the value of the VehicleStatusID field. It also includes Aptify's Exception Management functionality to capture and display any errors that occur during the validation update.

'Validation to enforce Business Rules for a Vehicle's Status 
'When a Vehicle's Status is attempted to be changed, the following rules must be met for the status: 
'1. If a Vehicle's DateDisposed and DisposalMethod are filled in, then the Vehicle's Status is Discarded and cannot be used at all. 
'2. Else If a RentalAgreement exists for the VehicleID and the RentalDate is < Today and no ReturnDate exists then the Vehicle is currently Rented 
'3. Else If a ServiceTicket exists for the VehicleID and it is still outstanding (NOT (ServiceStatus = Completed AND ReleaseToFleet = 1) Then the Vehicle's Status is Servicing 
'4. Else the Vehicle is Available. 
 
Try 
       Dim status As System.String 
       Dim sSQL As System.String 
       Dim count As System.Int32 
       Dim oDA As Aptify.Framework.DataServices.DataAction 
       oDA = New Aptify.Framework.DataServices.DataAction(geRecord.UserCredentials) 
       '1. Is this Vehicle Discarded? 
       ' Assertion: Both the DateDisposed and DisposalMethod not N/A for the Vehicle to be 'Discarded' 
       If Not IsDBNull(geRecord.GetValue("DateDisposed")) _ 
                    AndAlso geRecord.GetValue("DateDisposed").ToString <> "" _ 
                    AndAlso geRecord.GetValue("DisposalMethod").ToString <> "N/A" Then 
                   'This Vehicle is Discarded 
               status = "Discarded" 
       Else 
       '2. Is this Vehicle currently Rented? 
       ' Assertion: If CheckInClerkID is filled in then the rental has been Checked-In 
               sSQL = "SELECT count " & _ 
                  "FROM vwRentalAgreements " & _ 
                  "WHERE RentalDate < GetDate() " & _ 
                  "AND CheckInClerkID IS NULL " & _ 
                  "AND VehicleID = " & geRecord.GetValue("ID").ToString 
               count = CInt(oDA.ExecuteScalar(sSQL)) 
               If Not IsDBNull(count) And count > 0 Then 
                   'This Vehicle is rented 
                   status = "Rented" 
               Else 
                   '3. Is this vehicle being Serviced? 
                   sSQL = "SELECT count " & _ 
                       "FROM vwServiceTickets " & _ 
                      "WHERE NOT (ServiceStatus = 'Completed' " & _ 
                      "AND ReleaseToFleet = 1 ) " & _ 
                      "AND VehicleID = " & geRecord.GetValue("ID").ToString 
                  count = CInt(oDA.ExecuteScalar(sSQL)) 
                  If Not IsDBNull(count) And count > 0 Then 
                     'This Vehicle is rented 
                       status = "Servicing" 
                  Else 
                   '4. If none of the above conditions are true, then this vehicle is Available 
                    status = "Available" 
                  End If 
               End If 
       End If 
          'Force the Vehicle's Status 
          'The StatusID will need to be queried from the VehicleStatuses entity 
       sSQL = "SELECT ID " & _ 
           "FROM vwVehicleStatuses " & _ 
           "WHERE Name = '" & status & "'" 
       Dim statusID As System.Int32 = CInt(oDA.ExecuteScalar(sSQL)) 
       If Not IsDBNull(statusID) AndAlso statusID > 0 Then 
                    geRecord.SetValue("VehicleStatusID", statusID) 
                oResult.Success = True 
       Else 
                oResult.Success = False 
                oResult.Message = "The Vehicle Status script failed unexpectedly. " & _ 
                       " The query for " & status & " returned an ID of " & statusID & _ 
                       "Check the script for logical errors." 
 
       End If 
 Catch ex As System.Exception 
       ' Validation Script Failed 
       Aptify.Framework.ExceptionManagement.ExceptionManager.Publish(ex) 
       oResult.Success = False 
       oResult.Message = "The Vehicle Status script failed unexpectedly. " & _ 
                "See the Error Log for more details." 
 End Try

5.5 Product Knowledge

If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

About the Vehicles Entity VehicleStatusID Field Sample Validation Script