VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "InOutList"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id 自動編號
'2 TypeId  數字 收支類型編號
'3 UseDate  文本 長度 20 收支日期
'4 ANo 文本 長度 50 賬號
'5 UseSum  數字 單精度 收支金額
'6 MemId 數字 家庭成員編號
'7 Memos  文本 長度 100 備注
'8 Flag 數字 字節 類型：0-收入；1-支出

Public Id As Long
Public TypeId As Long
Public UseDate As String
Public ANo As String
Public UseSum As Double
Public MemId As Long
Public Memos As String
Public Flag As Integer

Public Sub Init()
  TypeId = 0
  UseDate = ""
  ANo = ""
  UseSum = 0
  MemId = 0
  Memos = ""
  Flag = 0
End Sub

'刪除
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM InOutList WHERE Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset  '定義結果集對象
  
  '設置SELECT語句，讀取編號為TmpId的記錄
  SqlStmt = "SELECT * FROM InOutList WHERE Id=" + Trim(TmpId)
  '將結果集讀取到rs中
  Set rs = QueryExt(SqlStmt)

  If rs.EOF Then
    '如果結果集為空，則初始化
    Init
    GetInfo = False
  Else
    '將結果集中的數據賦值到成員變量中
    If IsNull(rs.Fields(0)) Then
      Id = 0
    Else
      Id = rs.Fields(0)
    End If
   
    If IsNull(rs.Fields(1)) Then
      TypeId = 0
    Else
      TypeId = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      UseDate = ""
    Else
      UseDate = Trim(rs.Fields(2))
    End If
    If IsNull(rs.Fields(3)) Then
      ANo = ""
    Else
      ANo = Trim(rs.Fields(3))
    End If
    If IsNull(rs.Fields(4)) Then
      UseSum = 0
    Else
      UseSum = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      MemId = 0
    Else
      MemId = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      Memos = ""
    Else
      Memos = Trim(rs.Fields(6))
    End If
    If IsNull(rs.Fields(7)) Then
      Flag = 0
    Else
      Flag = rs.Fields(7)
    End If
   GetInfo = True
  End If
End Function

'插入分類
Public Sub Insert()
  SqlStmt = "INSERT INTO InOutList(TypeId,UseDate,ANo,UseSum," _
       + "MemId,Memos,Flag) VALUES(" + Trim(TypeId) _
       + ",'" + Trim(UseDate) + "','" + Trim(ANo) + "'," _
       + Trim(UseSum) + "," + Trim(MemId) + ",'" _
       + Trim(Memos) + "'," + Trim(Flag) + ")"
  SQLExt (SqlStmt)
End Sub

'更新數據
Public Sub Update(ByVal TmpId As Long)
   SqlStmt = "UPDATE InOutList SET TypeId=" + Trim(TypeId) _
          + ",UseDate='" + Trim(UseDate) + "',ANo='" + Trim(ANo) _
          + "',UseSum=" + Trim(UseSum) + ",MemId=" + Trim(MemId) _
          + ",Memos='" + Trim(Memos) _
          + "',Flag=" + Trim(Flag) + " Where Id = " + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub

'計算收支金額
Public Function CalSum(ByVal TmpFlag As Integer) As Double
  Dim rs As New ADODB.Recordset  '定義結果集對象
  'Flag=0表示收入, Flag=1表示支出
  If TmpFlag = 0 Then  '收入
    SqlStmt = "SELECT Sum(UseSum) FROM InOutList WHERE Flag=0"
  Else
    SqlStmt = "SELECT Sum(UseSum) FROM InOutList WHERE Flag=1"
  End If
  '將結果集讀取到rs中
  Set rs = QueryExt(SqlStmt)
  '從結果集中獲取數據
  If IsNull(rs.Fields(0)) Then
    CalSum = 0
  Else
    CalSum = rs.Fields(0)
  End If
End Function