<% 
'本類用于保存對表Person的數據庫訪問操作
'表的每個字段對應類的一個成員變量
class Person
  public UserId			' 用戶名
  public UserPwd		' 密碼
  public Name			' 姓名
  public Sex			' 性別
  public Address		' 地址
  public Postcode		' 郵編
  public Email			' 電子郵件
  public Telephone		' 電話
  public Mobile			' 手機
  public Company		' 單位
  public Birthday		' 生日
  public Status			' 當前狀態
  public UserType		' 用戶類型
  public rs				' 用于返回結果集

  '獲取個人信息
  public sub GetPersonInfo(uid)
	'創建Recordset對象，處理查詢結果集
	Set rs=Server.CreateObject("ADODB.Recordset")
	'設置rs的ActiveConnection屬性，綁定到連接
	Set rs.ActiveConnection = Conn
	'設置游標類型
	rs.CursorType = 3
	'設置查詢的SELECT語句
 	sql = "SELECT * FROM Person WHERE UserId='" + uid + "'"
	'打開記錄集
	rs.Open sql
	'讀取個人數據
	if rs.EOF Then  
	  UserId = ""
	else
	  UserId = uid	
	  UserPwd = rs("UserPwd")
	  Name = rs("Name")
	  Sex = rs("Sex")
	  Address = rs("Address")
	  Postcode = rs("Postcode")
	  Email = rs("Email")
	  Telephone = rs("Telephone")
	  Mobile = rs("Mobile")
	  Company = rs("Company")
	  Birthday = rs("Birthday")
	  Status = rs("Status")
	  UserType = rs("UserType")
	end if
	rs.Close
  end sub

  '獲取所有個人信息，返回結果集
  public sub GetPersonlist()
		'創建Recordset對象，處理查詢結果集
		Set rs=Server.CreateObject("ADODB.Recordset")
		'設置rs的ActiveConnection屬性，綁定到連接
		Set rs.ActiveConnection = Conn
		'設置游標類型
		rs.CursorType = 3
		'設置查詢的SELECT語句
	 	sql = "SELECT * FROM Person"
		'打開記錄集
		rs.Open sql
  end sub

  '獲取最近一周的生日信息，返回結果集
  public sub GetRecentBirthlist()
		'創建Recordset對象，處理查詢結果集
		Set rs=Server.CreateObject("ADODB.Recordset")
		'設置rs的ActiveConnection屬性，綁定到連接
		Set rs.ActiveConnection = Conn
		'設置游標類型
		rs.CursorType = 3
		'設置查詢的SELECT語句
	 	sql = "SELECT Name, Birthday, DateDiff(day, getdate(), DateAdd(Year, " _
			& " year(getdate())-year(Birthday), Birthday)) " _
			& " FROM Person WHERE IsDate(Birthday)=1 And DateDiff(day, getdate(), DateAdd(Year, " _
			& " year(getdate())-year(Birthday), Birthday))<=7 ORDER BY Birthday DESC"

		'打開記錄集
		rs.Open sql
  end sub

  ' 判斷指定用戶名是否存在
  public function HavePerson(uid)
	dim exist
	'創建Recordset對象，處理查詢結果集
	Set rs=Server.CreateObject("ADODB.Recordset")
	'設置rs的ActiveConnection屬性，綁定到連接
	Set rs.ActiveConnection = Conn
	'設置游標類型
	rs.CursorType = 3
	'設置查詢的SELECT語句
 	sql = "SELECT * FROM Person WHERE UserId='" + uid + "'"
	'打開記錄集
	rs.Open sql
	'如果記錄集為空，則插入數據；否則修改數據
	if rs.EOF then  	
	  exist = false
	else
	  exist = true
	end if

	rs.Close
	HavePerson = exist
  end function

  '添加個人信息
  public sub insert()
    sql = "INSERT INTO Person VALUES ('" + UserId + "','" + UserPwd _
		  + "','" + Name + "'," + CStr(Sex) + ",'" + Address + "','" + Postcode _
		  + "','" + Email + "','" + Telephone + "','" + Mobile _
		  + "','" + Company + "','" + Birthday + "'," + CStr(Status) + "," + CStr(UserType) + ")"
	'執行SQL語句
	Conn.Execute(sql)
  end sub

  '修改個人信息
  public sub update(uid)
    sql = "UPDATE Person SET Name='" + Name _
		  + "', Sex=" + CStr(Sex) + ", Address='" + Address + "', Postcode='" _
		  + Postcode + "', Email='" + Email + "', Telephone='" + Telephone _
		  + "', Mobile='" + Mobile + "', Company='" + Company + "', Birthday='" _
		  + Birthday + "' WHERE UserId='" + uid + "'"
	'執行SQL語句
	Conn.Execute(sql)
  end sub

  public sub setpwd(uid)
	sql = "UPDATE Person SET UserPwd='" + UserPwd + "' WHERE UserId='" + uid + "'"
	Conn.Execute(sql)
  end sub

  ' 將Status設置為1，批準指定同學的加入申請
  public sub approve(uid)
    sql = "UPDATE Person SET Status=1 WHERE UserId='" & uid & "'"
	Conn.Execute(sql)
  end sub

  '刪除個人信息
  public sub delete(uid)
	sql = "DELETE FROM Person WHERE UserId='" & uid & "'"
	Conn.Execute(sql)
  end sub
end class
%>