VERSION 5.00
Begin VB.Form frmTicket2 
   Caption         =   "機票信息查詢"
   ClientHeight    =   1980
   ClientLeft      =   48
   ClientTop       =   348
   ClientWidth     =   6396
   LinkTopic       =   "Form1"
   ScaleHeight     =   1980
   ScaleWidth      =   6396
   StartUpPosition =   1  'CenterOwner
   Begin VB.TextBox ticketFsum 
      Height          =   270
      Left            =   5280
      TabIndex        =   13
      TabStop         =   0   'False
      Top             =   1440
      Visible         =   0   'False
      Width           =   735
   End
   Begin VB.TextBox ticketMsum 
      Height          =   270
      Left            =   4320
      TabIndex        =   12
      TabStop         =   0   'False
      Top             =   1440
      Visible         =   0   'False
      Width           =   735
   End
   Begin VB.Frame Frame1 
      Caption         =   "航線信息："
      Height          =   1212
      Index           =   0
      Left            =   120
      TabIndex        =   3
      Top             =   120
      Width           =   6132
      Begin VB.ComboBox Combo1 
         Height          =   288
         Index           =   2
         Left            =   4560
         Style           =   2  'Dropdown List
         TabIndex        =   11
         Top             =   720
         Width           =   1212
      End
      Begin VB.ComboBox Combo1 
         Height          =   288
         Index           =   1
         Left            =   4560
         Style           =   2  'Dropdown List
         TabIndex        =   10
         Top             =   360
         Width           =   1212
      End
      Begin VB.ComboBox Combo1 
         Height          =   288
         Index           =   0
         Left            =   1560
         Style           =   2  'Dropdown List
         TabIndex        =   9
         Top             =   360
         Width           =   1212
      End
      Begin VB.TextBox txtItem 
         Height          =   270
         Index           =   0
         Left            =   1560
         MaxLength       =   20
         TabIndex        =   4
         Top             =   720
         Width           =   1212
      End
      Begin VB.Label Label2 
         Caption         =   "到    達    城    市："
         Height          =   252
         Index           =   1
         Left            =   3360
         TabIndex        =   8
         Top             =   360
         Width           =   1212
      End
      Begin VB.Label Label2 
         Caption         =   "出    發    城    市："
         Height          =   252
         Index           =   0
         Left            =   360
         TabIndex        =   7
         Top             =   360
         Width           =   1212
      End
      Begin VB.Label Label2 
         Caption         =   "出    發    時    間："
         Height          =   252
         Index           =   5
         Left            =   360
         TabIndex        =   6
         Top             =   720
         Width           =   1212
      End
      Begin VB.Label Label2 
         Caption         =   "艙    位    類    型："
         Height          =   252
         Index           =   6
         Left            =   3360
         TabIndex        =   5
         Top             =   720
         Width           =   1212
      End
   End
   Begin VB.TextBox ticketCsum 
      Height          =   270
      Left            =   120
      TabIndex        =   2
      TabStop         =   0   'False
      Top             =   1440
      Visible         =   0   'False
      Width           =   735
   End
   Begin VB.CommandButton cmdOk 
      Caption         =   "確定 (&O)"
      Default         =   -1  'True
      Height          =   375
      Left            =   1440
      TabIndex        =   1
      Top             =   1440
      Width           =   1215
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "取消 (&X)"
      Height          =   375
      Left            =   2880
      TabIndex        =   0
      Top             =   1440
      Width           =   1215
   End
End
Attribute VB_Name = "frmTicket2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit


Private Sub cmdExit_Click()
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
    Dim intCount As Integer
    Dim sMeg As String
    Dim i, j, k

        
        
    For intCount = 0 To 1
        If Trim(Combo1(intCount) & " ") = "" Then
            Select Case intCount
                Case 0
                    sMeg = "出發城市"
                Case 1
                    sMeg = "到達城市"
            End Select
            sMeg = sMeg & "不能為空！"
            MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
            Combo1(intCount).SetFocus
            Exit Sub
        End If
    Next intCount
    
    If Trim(txtItem(0)) = "" Then
        sMeg = "出發日期不能為空！"
        MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
        txtItem(0).SetFocus
        Exit Sub
    End If
    
    If IsDate(txtItem(0)) Then
        txtItem(0) = Format(txtItem(0), "yyyy-mm-dd")
    Else
        MsgBox "時間應輸入日期（yyyy-mm-dd）！", vbOKOnly + vbExclamation, "警告"
        txtItem(0).SetFocus
        Exit Sub
    End If
    
    If Not (Trim(Combo1(2)) = "") Then
        txtSQL = "select * from ticketInfo where ticketDate = '" & Trim(txtItem(0)) & "' and departCity = '" & Trim(Combo1(0)) & "' and arrivalCity = '" & Trim(Combo1(1)) & "' and serviceType = '" & Trim(Combo1(2)) & "'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
                
        If Trim(Combo1(2)) = "經濟艙" Then
            intCount = ticketCsum - mrc.RecordCount
        ElseIf Trim(Combo1(2)) = "公務艙" Then
            intCount = ticketMsum - mrc.RecordCount
        Else
            intCount = ticketFsum - mrc.RecordCount
        End If
        
        If intCount > 0 Then
            MsgBox Trim(txtItem(0)) & "從" & Trim(Combo1(0)) & "到" & Trim(Combo1(1)) & Trim(Combo1(2)) & "的機票還有" & intCount & "張!", vbOKOnly + vbExclamation, "機票信息"
        Else
            MsgBox "對不起," & Trim(txtItem(0)) & "從" & Trim(Combo1(0)) & "到" & Trim(Combo1(1)) & Trim(Combo1(2)) & "的航班已經滿員，請預定其它航班！", vbOKOnly + vbExclamation, "機票信息"
        End If
        
    Else
        txtSQL = "select * from ticketInfo where ticketDate = '" & Trim(txtItem(0)) & "' and departCity = '" & Trim(Combo1(0)) & "' and arrivalCity = '" & Trim(Combo1(1)) & "' and serviceType = '經濟艙'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
                
        i = mrc.RecordCount
        
        mrc.Close
        
        txtSQL = "select * from ticketInfo where ticketDate = '" & Trim(txtItem(0)) & "' and departCity = '" & Trim(Combo1(0)) & "' and arrivalCity = '" & Trim(Combo1(1)) & "' and serviceType = '公務艙'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
        
        j = mrc.RecordCount
        
        mrc.Close
            
        txtSQL = "select * from ticketInfo where ticketDate = '" & Trim(txtItem(0)) & "' and departCity = '" & Trim(Combo1(0)) & "' and arrivalCity = '" & Trim(Combo1(1)) & "' and serviceType = '頭等艙'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
        
        k = mrc.RecordCount
        
        mrc.Close
        
        MsgBox Trim(txtItem(0)) & "從" & Trim(Combo1(0)) & "到" & Trim(Combo1(1)) & "經濟艙的機票還有" & (ticketCsum - i) & "張,公務艙的機票還有" & (ticketMsum - j) & "張，頭等艙的機票還有" & (ticketFsum - k) & "張！", vbOKOnly + vbExclamation, "機票信息"
    End If
    Unload Me
End Sub


Private Sub Combo1_Click(Index As Integer)
    Dim mrcc As ADODB.Recordset
    Dim MsgText As String
    Dim txtSQL As String
    
    
    If Index = 1 Then
        If Not (Trim(Combo1(0)) = "") Then
            txtSQL = "select *  from airlineInfo where departCity = '" & Trim(Combo1(0)) & "' and arrivalCity = '" & Trim(Combo1(1)) & "'"
            Set mrcc = ExecuteSQL(txtSQL, MsgText)
                        
            If Not mrcc.EOF Then
                ticketCsum = mrcc!planeNO
            End If
            mrcc.Close
            
            txtSQL = "select * from planeInfo where planeNO = '" & Trim(ticketCsum) & "'"
            Set mrcc = ExecuteSQL(txtSQL, MsgText)
            
            If Not mrcc.EOF Then
                ticketCsum = mrcc.Fields(4)
                ticketMsum = mrcc.Fields(5)
                ticketFsum = mrcc.Fields(6)
            End If
            
            mrcc.Close
        End If
    End If
End Sub

Private Sub Form_Load()
    
    
    Dim i As Integer
    Dim sSql As String
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
    
    For i = 0 To 2
        Combo1(i).Clear
    Next i
    
    txtSQL = "select distinct departCity from airlineInfo "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    If Not mrc.EOF Then
        Do While Not mrc.EOF
            Combo1(0).AddItem mrc.Fields(0)
            mrc.MoveNext
        Loop
    End If
        
    mrc.Close
        
    txtSQL = "select distinct arrivalCity from airlineInfo"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    If Not mrc.EOF Then
        Do While Not mrc.EOF
            Combo1(1).AddItem mrc.Fields(0)
            mrc.MoveNext
        Loop
    End If
    mrc.Close
        
    txtSQL = "select distinct serviceName from serviceInfo"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    If Not mrc.EOF Then
        Do While Not mrc.EOF
            Combo1(2).AddItem mrc.Fields(0)
            mrc.MoveNext
        Loop
    End If
    mrc.Close
    
    
End Sub


Private Sub txtItem_GotFocus(Index As Integer)
    txtItem(Index).SelStart = 0
    txtItem(Index).SelLength = Len(txtItem(Index))
End Sub