customer.cls

酒店管理系统 基于VB和SQL的一个很好的程序 可以实现酒店的日常管理工作

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Customer"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 CustId Varchar 40 客户编号（用客户身份证号码表示）
'2 Idtype  Varchar 20 证件类型（包括身份证、军官证、驾驶证、工作证、学生证、回乡证和护照等）
'3 CustName Varchar 50 联系人姓名
'4 OrgName Varchar 100  客户单位
'5 CustType Varchar 50 客户类别（由用户自定义，可以按行业或地域划分）
'6 CustFrom Varchar 50 国籍户籍
'7 Sex Varchar 10 性别
'8 Job Varchar 50 职务
'9 Office  Varchar 30 单位电话
'10  Mobile  Varchar 30 移动电话
'11  Home  Varchar 30 家庭电话
'12  CustLevel  Varchar 10 客户等级（本系统分为普通客户和贵宾客户两种等级，贵宾客户在结帐时有签单的权限。在设计宾馆酒店管理系统时，可以根据需要设置客户等级，并为不同等级的客户制定不同的优惠政策和权限）
'13  Address Varchar 200  家庭住址
'14  Postcode  Varchar 20 邮政编码

Public CustId As String
Public Idtype As String
Public CustName As String
Public OrgName As String
Public CustType As String
Public CustFrom As String
Public Sex As String
Public Job As String
Public Office As String
Public Mobile As String
Public Home As String
Public CustLevel As String
Public Address As String
Public Postcode As String

Public Sub Init()
  CustId = ""
  Idtype = ""
  CustName = ""
  OrgName = ""
  CustType = ""
  CustFrom = ""
  Sex = ""
  Job = ""
  Office = ""
  Mobile = ""
  Home = ""
  CustLevel = ""
  Address = ""
  Postcode = ""
End Sub

'删除
Public Sub Delete(ByVal TmpId As String)
  SqlStmt = "DELETE FROM Customer WHERE CustId='" + Trim(TmpId) + "'"
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpId As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  '设置SELECT语句，读取编号为TmpId的记录
  SqlStmt = "SELECT * FROM Customer WHERE CustId='" + Trim(TmpId) + "'"
  '将结果集读取到rs中
  Set rs = QueryExt(SqlStmt)

  If rs.EOF = True Then
    '如果结果集为空，则初始化
    Init
    GetInfo = False
  Else
    '将结果集中的数据赋值到成员变量中
    If IsNull(rs.Fields(0)) Then
      CustId = ""
    Else
      CustId = rs.Fields(0)
    End If
    If IsNull(rs.Fields(1)) Then
      Idtype = ""
    Else
      Idtype = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      CustName = ""
    Else
      CustName = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      OrgName = ""
    Else
      OrgName = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      CustType = ""
    Else
      CustType = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      CustFrom = ""
    Else
      CustFrom = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      Sex = ""
    Else
      Sex = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      Job = ""
    Else
      Job = rs.Fields(7)
    End If
    If IsNull(rs.Fields(8)) Then
      Office = ""
    Else
      Office = rs.Fields(8)
    End If
    If IsNull(rs.Fields(9)) Then
      Mobile = ""
    Else
      Mobile = rs.Fields(9)
    End If
    If IsNull(rs.Fields(10)) Then
      Home = ""
    Else
      Home = rs.Fields(10)
    End If
    If IsNull(rs.Fields(11)) Then
      CustLevel = ""
    Else
      CustLevel = rs.Fields(11)
    End If
    If IsNull(rs.Fields(12)) Then
      Address = ""
    Else
      Address = rs.Fields(12)
    End If
    If IsNull(rs.Fields(13)) Then
      Postcode = ""
    Else
      Postcode = rs.Fields(13)
    End If
    
    GetInfo = True
  End If
End Function

Public Function GetNum() As Long
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Count(CustId) FROM Customer"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    GetNum = 0
  Else
    GetNum = rs.Fields(0)
  End If
End Function

Public Function In_DB(ByVal TmpId As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT CustId FROM Customer WHERE CustId='" + Trim(TmpId) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    In_DB = False
  Else
    In_DB = True
  End If
End Function

Public Sub Insert()
  SqlStmt = "INSERT INTO Customer VALUES('" + Trim(CustId) + "','" + Trim(Idtype) + "','" _
          + Trim(CustName) + "','" + Trim(OrgName) + "','" + Trim(CustType) _
          + "','" + Trim(CustFrom) + "','" + Trim(Sex) + "','" + Trim(Job) _
          + "','" + Trim(Office) + "','" + Trim(Mobile) + "','" + Trim(Home) _
          + "','" + Trim(CustLevel) + "','" + Trim(Address) + "','" + Trim(Postcode) + "')"
  SQLExt (SqlStmt)
End Sub

Public Sub Loadtype()
  Dim i As Integer
  Dim rs As New ADODB.Recordset
  '对数组进行初始化
  Erase Arr_CustType
  ReDim Arr_CustType(0)
  '设置SQL语句,使用DISTINCT关键字避免出现重复的客户类别,结果集按CustType字段排序
  SqlStmt = "SELECT DISTINCT CustType FROM Customer ORDER BY CustType"
  '执行SQL语句，将返回结果集赋值到rs对象中
  Set rs = QueryExt(SqlStmt)
  '依次把读取的数据赋值到数组中
  i = 0
  Do Until rs.EOF = True
    ReDim Preserve Arr_CustType(i + 1)
    Arr_CustType(i) = Trim(rs.Fields(0))
    '将指针移动到下一条记录
    rs.MoveNext
    i = i + 1
  Loop
End Sub

Public Sub Update(ByVal TmpId As String)
  SqlStmt = "UPDATE Customer SET CustId='" + Trim(CustId) + "', Idtype='" + Trim(Idtype) _
          + "', CustName='" + Trim(CustName) + "', OrgName='" + Trim(OrgName) + "', CustType='" _
          + Trim(CustType) + "', CustFrom='" + Trim(CustFrom) + "', Sex='" + Trim(Sex) _
          + "', Job='" + Trim(Job) + "', Office='" + Trim(Office) + "', Mobile='" _
          + Trim(Mobile) + "', Home='" + Trim(Home) + "', CustLevel='" + Trim(CustLevel) _
          + "', Address='" + Trim(Address) + "', Postcode='" + Trim(Postcode) _
          + "' WHERE CustId='" + Trim(TmpId) + "'"
  SQLExt (SqlStmt)
End Sub