VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Schedule"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Function Destinations(ByVal strFileDSN As String)
    Dim objContext As ObjectContext
    Set objContext = GetObjectContext
    
    On Error GoTo ErrorHandler
    
    Dim strSQL As String
    Dim rst As New ADODB.Recordset
    
    strSQL = "SELECT CityName from Destination ORDER BY CityName"
    rst.CursorLocation = adUseServer
    rst.Open strSQL, "FILEDSN=" & strFileDSN, adOpenStatic, adLockReadOnly
    Set Destinations = rst
    Exit Function

ErrorHandler:
    If Not rst Is Nothing Then Set rst = Nothing
    Set Destinations = Nothing
    objContext.SetAbort
    Err.Raise Err.Number, "Flight.Schedule()", Err.Description
End Function

Function CostOfFlightInMiles(ByVal strFileDSN As String, ByVal strFrom As String, ByVal strTo As String, ByVal strClass As String)
    Dim objContext As ObjectContext
    Set objContext = GetObjectContext
    
    On Error GoTo ErrorHandler
    
    Dim strSQL As String
    Dim rst As New ADODB.Recordset
    
    strSQL = "SELECT distinct MilesCostFirst, MilesCostBusiness, MilesCostCoach " & _
             "FROM FlightSchedule " & _
             "WHERE Origin = '" & strFrom & "' " & _
             "AND Destination = '" & strTo & "'"
    rst.CursorLocation = adUseServer
    rst.Open strSQL, "FILEDSN=" & strFileDSN, adOpenStatic, adLockReadOnly
    
    If strClass = "Coach" Then CostOfFlightInMiles = rst.Fields("MilesCostCoach")
    If strClass = "Business" Then CostOfFlightInMiles = rst.Fields("MilesCostBusiness")
    If strClass = "First" Then CostOfFlightInMiles = rst.Fields("MilesCostFirst")
    
    objContext.SetComplete
    
    Exit Function

ErrorHandler:
    If Not rst Is Nothing Then Set rst = Nothing
    CostOfFlightInMiles = -1
    objContext.SetAbort
    Err.Raise Err.Number, "Flight.CostOfFlightInMiles()", Err.Description
End Function

Function FlightNumber(ByVal strFileDSN As String, ByVal strFrom As String, _
    ByVal strTo As String)
    Dim objContext As ObjectContext
    Set objContext = GetObjectContext
    
    On Error GoTo ErrorHandler
    
    Dim strSQL As String
    Dim rst As New ADODB.Recordset
    
    strSQL = "SELECT distinct FlightNo " & _
             "FROM FlightSchedule " & _
             "WHERE Origin = '" & strFrom & "' " & _
             "AND Destination = '" & strTo & "'"
    rst.CursorLocation = adUseServer
    rst.Open strSQL, "FILEDSN=" & strFileDSN, adOpenStatic, adLockReadOnly
    
    FlightNumber = rst.Fields("FlightNo")
    
    objContext.SetComplete
    
    Exit Function

ErrorHandler:
    If Not rst Is Nothing Then Set rst = Nothing
    FlightNumber = -1
    objContext.SetAbort
    Err.Raise Err.Number, "Flight.FlightNumber()", Err.Description
End Function

Function BookFlight(ByVal strFileDSN As String, ByVal lngMemberID As Long, _
    ByVal lngFlightNo As Long, ByVal datFlightDate As Date, _
    ByVal lngMiles As Long, ByVal lngMilesLeft As Long, _
    ByVal strClass As String)
    
    Dim objContext As ObjectContext
    Set objContext = GetObjectContext
    
    On Error GoTo ErrorHandler
    
    BookFlight = True
    
    Dim strSQL As String
    Dim cnn As New ADODB.Connection
    cnn.Open "FILEDSN=" & strFileDSN
        
    strSQL = "UPDATE Member " & _
             "SET Mileage=" & lngMilesLeft & _
             "WHERE AccountID=" & lngMemberID
    cnn.Execute strSQL
    
    Dim strTranType As String, lngCompanyId As Long, lngMilesSubtract As Long, _
        lngSpecialId As Long, strComment As String
    strTranType = "USE"
    lngCompanyId = 1
    lngMilesSubtract = (lngMiles * -1)
    lngSpecialId = 1
    strComment = "Free Flight!"

    strSQL = "INSERT INTO Transactions " & _
                          "(AccountID, TranType, " & _
                          "FlightNo, FlightDate, " & _
                          "CompanyId, Miles, " & _
                          "SpecialId, Comment) " & _
                          "VALUES (" & _
                          lngMemberID & ", " & _
                          "'" & strTranType & "', " & _
                          lngFlightNo & ", " & _
                          "'" & datFlightDate & "', " & _
                          lngCompanyId & ", " & _
                          lngMilesSubtract & ", " & _
                          lngSpecialId & ", " & _
                          "'" & strComment & "'" & _
                          ")"
    cnn.Execute strSQL
    
    objContext.SetComplete
    
    Exit Function

ErrorHandler:
    If Not cnn Is Nothing Then Set cnn = Nothing
    BookFlight = False
    objContext.SetAbort
    Err.Raise Err.Number, "Flight.BookFlight()", Err.Description
End Function
