VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Area"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 AreaId  Int 地域編號
'2 AreaName  Varchar 40  地域名稱
'3 AreaType  Smallint  地域類型，1 - 洲、2 - 國家、3 - 省、4 - 市縣
'4 UpperId Int 上級地域編號

Public AreaId As Long
Public AreaName As String
Public AreaType As Integer
Public UpperId As Long

Public Sub Init()
  AreaId = 0
  AreaName = ""
  AreaType = 0
  UpperId = 0
End Sub

'刪除
Public Sub Delete(TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Area WHERE AreaId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Sub GetInfo(TmpId As Long)
  AreaId = TmpId
  If TmpId = 0 Then
    AreaName = ""
    AreaType = 0
    UpperId = -1
    Exit Sub
  End If
  DB_Connect
  
  SqlStmt = "SELECT * FROM Area WHERE AreaId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    AreaName = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    AreaType = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    UpperId = Val(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(TmpId As Long) As String
  DB_Connect
  
  SqlStmt = "SELECT AreaName FROM Area WHERE AreaId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> 100 Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetName = TrimStr(ColVal)
  Else
    GetName = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  
  DB_Connect
  
  SqlStmt = "SELECT AreaId FROM Area ORDER BY AreaId"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> 100
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
  
  GetNewId = i
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT AreaId FROM Area WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
  OdbcExt (SqlStmt)
  i = 0
  If SQLFetch(Hstmt) = 100 Then
    HaveSon = False
  Else
    HaveSon = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpAreaName As String, _
                      ByVal TmpAreaUpper As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT AreaId FROM Area WHERE AreaName='" + Trim(TmpAreaName) _
             + "' and UpperId=" + Trim(Str(TmpAreaUpper))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    In_DB = True
  Else
    In_DB = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function Insert() As Long
  AreaId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Area VALUES(" + Trim(Str(AreaId)) + ",'" _
            + Trim(AreaName) + "'," + Trim(Str(AreaType)) _
            + "," + Trim(Str(UpperId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  Insert = AreaId
End Function

Public Sub Load_Area()
  Erase Arr_AreaName
  Erase Arr_AreaId
  Erase Arr_AreaType
  Erase Arr_AreaUpper
  ReDim Arr_AreaName(0)
  ReDim Arr_AreaId(0)
  ReDim Arr_AreaType(0)
  ReDim Arr_AreaUpper(0)
  
  DB_Connect
  SqlStmt = "SELECT * FROM Area ORDER BY AreaType"
  OdbcExt (SqlStmt)
  i = 0
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_AreaId(i + 1)
    Arr_AreaId(i) = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_AreaName(i + 1)
    Arr_AreaName(i) = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_AreaType(i + 1)
    Arr_AreaType(i) = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_AreaUpper(i + 1)
    Arr_AreaUpper(i) = Val(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

'更新地域數據
Public Sub Update(ByVal TmpId As Long)
  DB_Connect
    
  SqlStmt = "UPDATE Area SET AreaName='" + Trim(AreaName) _
          + "' WHERE AreaId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub