Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic.Strings

Namespace Packaging

	Public Class UserInfo
		Public UserId As Integer
		Public UserAccount As String
		Public UserPass As String
		Public UserName As String
		Public UserGender As String
		Public UserBirthday AS DateTime
		Public UserEmail As String
		Public UserQQ As String
		Public UserMSN As String
		Public UserHomepage As String
		Public UserDesc As String
		Public UserLastAccess As DateTime
		Public UserOnline As Boolean
		Public UserRegTime As DateTime
		Public UserLocked As Boolean
	End Class


	Public Class UserDB

		Private Sub Change4SQL(ByRef MyInfo As UserInfo)
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=0
			On Error Resume Next

			MyInfo.UserAccount=MyFilter.Change4Saving(MyInfo.UserAccount)
			MyInfo.UserPass=MyFilter.Change4Saving(MyInfo.UserPass)
			MyInfo.UserName=MyFilter.Change4Saving(MyInfo.UserName)
			MyInfo.UserEmail=MyFilter.Change4Saving(MyInfo.UserEmail)
			MyInfo.UserMSN=MyFilter.Change4Saving(MyInfo.UserMSN)
			MyInfo.UserHomepage=MyFilter.Change4Saving(MyInfo.UserHomepage)
			MyInfo.UserDesc=MyFilter.Change4Saving(MyInfo.UserDesc)
		End Sub

		Public Function GetUserInfo(ByVal account As String) As DataView
			'根據 UserAccount 取得用戶資料
			Dim MySQL As String
			MySQL="SELECT * FROM [User] WHERE UserAccount='"
			MySQL &= Account
			MySQL &= "'"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			Dim MyDV As DataView=MyDS.Tables("My").DefaultView
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=3

			Dim i As Integer
			On Error Resume Next
			For i=0 to MyDV.Table.Rows.Count-1
				MyDV.Table.Rows(i)("UserDesc")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserDesc"))
				MyDV.Table.Rows(i)("UserEmail")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserEmail"))
				MyDV.Table.Rows(i)("UserMSN")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserMSN"))
				MyDV.Table.Rows(i)("UserHomepage")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserHomepage"))
			Next

			Return MyDV
		End Function

		Public Function GetUserInfo2(ByVal account As String) As DataView
			'根據 UserAccount 取得用戶資料
			Dim MySQL As String
			MySQL="SELECT * FROM [User] WHERE UserAccount='"
			MySQL &= Account
			MySQL &= "'"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			Dim MyDV As DataView=MyDS.Tables("My").DefaultView
			Return MyDV
		End Function

		Public Function GetAllUser() As DataView
			Dim MySQL As String
			MySQL="SELECT * FROM [User]"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			Dim MyDV As DataView=MyDS.Tables("My").DefaultView
			Return MyDV
		End Function

		Public Function GetUserInfo1(ByVal id As Integer) As DataView
			'根據 UserId 取得用戶資料
			Dim MySQL As String
			MySQL="SELECT * FROM [User] WHERE UserId="
			MySQL &= id
			MySQL &= ""

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			Dim MyDV As DataView=MyDS.Tables("My").DefaultView
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=3

			Dim i As Integer
			On Error Resume Next
			For i=0 to MyDV.Table.Rows.Count-1
				MyDV.Table.Rows(i)("UserDesc")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserDesc"))
				MyDV.Table.Rows(i)("UserEmail")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserEmail"))
				MyDV.Table.Rows(i)("UserMSN")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserMSN"))
				MyDV.Table.Rows(i)("UserHomepage")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("UserHomepage"))
			Next

			Return MyDV
		End Function

		Public Function IsExist(ByVal UserAccount As String) As Integer
			'判斷是否存在 UserAccount 的賬號，如果有，返回他的 UserId；反之，返回０
			Dim MySQL As String
			MySQL="SELECT * FROM [User] WHERE UserAccount='"
			MySQL &= UserAccount
			MySQL &= "'"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			If MyDS.Tables("My").Rows.Count=0 Then
				Return 0
			Else
				Return MyDS.Tables("My").Rows(0).Item("UserId")
			End If
		End Function

		Public Sub AddUserInfo(ByVal MyUserInfo As UserInfo)
			'添加一個新用戶的資料
			Change4SQL(MyUserInfo)

			Dim MySQL As String="INSERT INTO [User] "
			MySQL &= "(UserAccount, UserPass, UserName, UserGender, "
			MySQL &= "UserLastAccess, UserRegTime) VALUES ('"
			MySQL &= MyUserInfo.UserAccount & "', '"
			MySQL &= MyUserInfo.UserPass & "', '"
			MySQL &= MyUserInfo.UserName & "', '"
			MySQL &= MyUserInfo.UserGender & "', #"
			MySQL &= DateTime.Now() & "#, #"
			MySQL &= DateTime.Now() & "#)"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub

		Public Sub ChangeUserInfo(ByVal MyUserInfo As UserInfo)
			'更改用戶資料
			Change4SQL(MyUserInfo)
		
			Dim MySQL As String="UPDATE [User] SET UserName='"
			MySQL &= MyUserInfo.UserName & "', UserGender='"
			MySQL &= MyUserInfo.UserGender & "', UserBirthday=#"
			MySQL &= MyUserInfo.UserBirthday & "#, UserEmail='"
			MySQL &= MyUserInfo.UserEmail & "', UserQQ='"
			MySQL &= MyUserInfo.UserQQ & "', UserMSN='"
			MySQL &= MyUserInfo.UserMSN & "', UserHomepage='"
			MySQL &= MyUserInfo.UserHomepage & "', UserDesc='"
			MySQL &= MyUserInfo.UserDesc & "' WHERE UserId="
			MySQL &= MyUserInfo.UserId & " "

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub

		Public Function Login(ByVal UserAccount As String, ByVal UserPass As String) As Integer
			'判斷訪問者輸入的賬戶、密碼是否正確
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=0
			UserAccount=MyFilter.Change4Saving(UserAccount)
			UserPass=MyFilter.Change4Saving(UserPass)
			Dim MySQL As String
			MySQL="SELECT * FROM [User] WHERE UserAccount='"
			MySQL &= UserAccount
			MySQL &= "' AND UserLocked=False AND UserPass='"
			MySQL &= UserPass
			MySQL &= "'"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			If MyDS.Tables("My").Rows.Count=0 Then
				Return 0
			Else
				Dim id As Integer= MyDS.Tables("My").Rows(0).Item("UserId")
				MySQL="UPDATE [User] SET UserOnline=True WHERE UserID="
				MySQL &= id

				MyDB.strSQL=MySQL
				MyDB.ExecuteSQL()
				UserVisit(id)

				Return id
			End If
		End Function

		Public Sub DeleteUserInfo(ByVal UserId As Integer)
			'刪除一個用戶的資料
			Dim MySQL As String="DELETE FROM [User] WHERE UserId="
			MySQL &= UserId
			MySQL &= " "

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub

		Public Sub LockUser(ByVal UserId As Integer)
			Dim DV As DataView=GetUserInfo1(UserId)
			If DV.Table.Rows.Count>0 Then
				Dim MySQL As String
				If DV.Table.Rows(0)("UserLocked")=True Then
					MySQL="UPDATE [User] SET UserLocked=True WHERE UserId="
				Else
					MySQL="UPDATE [User] SET UserLocked=False WHERE UserId="
				End If
				MySQL &= UserId
				MySQL &= " "

				Dim MyDB As SQLDB=New SQLDB()
				MyDB.strSQL=MySQL
				MyDB.ExecuteSQL()
			End If
		End Sub

		Public Sub UserVisit(ByVal account As String)
			Dim MySQL As String="UPDATE [User] SET UserLastAccess=#"
			MySQL &= DateTime.Now() & "#, "
			MySQL &= "UserOnline=True WHERE UserAccount='" & account & "'" 

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub

		Public Function GetOnlineUsers() As DataView
			Dim MySQL As String="SELECT UserID, UserLastAccess "
			MySQL &= "FROM [User] WHERE UserOnline=True"

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()

			Dim MyDV As DataView=MyDS.Tables("My").DefaultView
			Return MyDV
		End Function

		Public Sub UserSignOff(ByVal Id As Integer)
			Dim MySQL="UPDATE [User] SET UserOnline=False WHERE UserID="
			MySQL &= id

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub

		Public Sub ChangePass(ByVal Account As String, ByVal NewPass As String)
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=0
			NewPass=MyFilter.Change4Saving(NewPass)
			Account=MyFilter.Change4Saving(Account)
			Dim MySQL As String="UPDATE [User] SET UserPass='"
			MySQL &= NewPass & "' WHERE UserAccount='"
			MySQL &= Account & "'"

			Dim MyDB As SQLDB =New SQLDB()
			MyDB.strSQL=mySQL
			MYDB.ExecuteSQL()
		End Sub

		Public Function ResetPass(ByVal Id As Integer) As String
			Dim NewPass As String
			Dim nRandom As Random = New Random(DateTime.Now.Millisecond)
			NewPass=Left(nRandom.Next().ToString(),20)

			Dim MySQL As String="UPDATE [User] SET UserPass='"
			MySQL &= NewPass & "' WHERE UserId="
			MySQL &= Id & ""

			Dim MyDB As SQLDB =New SQLDB()
			MyDB.strSQL=mySQL
			MYDB.ExecuteSQL()

			Return NewPass
		End Function
	End Class

End Namespace