Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb

Namespace Packaging

	Public Class BBS
		Public Id As Integer
		Public Title As String
		Public Author As String
		Public Content As String
		Public Parent As Integer
		Public Time As DateTime
		Public Clicks As Integer
		Public NumOfReplies As Integer
		Public face As Integer
	End Class
	
	
	Public Class BBSDB
	
		Public Function GetBBS1(ByVal Id As Integer) As DataView
			'取出特定Id號的文章
			ReadOnce(id)
			Dim MySQL As String
			MySQL="SELECT * FROM [BBS] WHERE BBSId="
			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 i As Integer
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=2
			For i=0 to MyDV.Table.Rows.Count-1
				MyDV.Table.Rows(i)("BBSContent")=MyFilter.Change4Showing(MyDV.Table.Rows(i)("BBSContent"))
			Next

			Return MyDV
		End Function

		Public Function GetBBS(ByVal Id As Integer) As DataView
			'取出特定Id號的文章
			ReadOnce(id)
			Dim MySQL As String
			MySQL="SELECT * FROM [BBS] WHERE BBSId="
			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

			Return MyDV
		End Function

		Public Sub AddBBS(ByVal MyBBS As BBS)
			'添加一篇文章
			change4SQL(MyBBS)

			Dim MySQL As String="INSERT INTO [BBS] "
			MySQL &= "(BBSTitle, BBSAuthor, BBSContent, BBSParent, BBSTime, "
			MySQL &= "BBSClicks, BBSFace) VALUES ('"
			MySQL &= MyBBS.Title & "', '"
			MySQL &= MyBBS.Author & "', '"
			MySQL &= MyBBS.Content & "', "
			MySQL &= MyBBS.Parent & ", #"
			MySQL &= DateTime.now() & "#, " & 0 & ", "
			MySQL &= MyBBS.Face & ")"

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

		End Sub

		Public Sub ChangeBBS(ByVal MyBBS As BBS)
			'更改文章
			Change4SQL(MyBBS)

			Dim MySQL As String="UPDATE [BBS] SET BBSTitle='"
			MySQL &= MyBBS.Title & "', BBSAuthor='"
			MySQL &= MyBBS.Author & "', BBSContent='"
			MySQL &= MyBBS.Content & "', BBSFace="
			MySQL &= MyBBS.Face & " WHERE BBSId="
			MySQL &= MyBBS.Id & " "

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

		Private Sub Change4SQL(ByRef MyBBS As BBS)
			Dim MyFilter As StringFilter=New StringFilter()
			MyFilter.FilterDegree=5
			
			MyBBS.Title=MyFilter.Change4Saving(MyBBS.Title)
			MyBBS.Author=MyFilter.Change4Saving(MyBBS.Author)
			MyBBS.Content=MyFilter.Change4Saving(MyBBS.Content)
		End Sub

		Public Sub DeleteBBS(ByVal Id As Integer)
			'刪除文章
			Dim MySQL As String="SELECT * FROM [BBS] WHERE BBSId="
			MySQL &= id

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

			Dim DV As DataView=New DataView()
			Dim DT As DataTable=New DataTable("My")

			Dim keyDC As DataColumn
			Dim DR As DataRow
			For Each keyDC In MyDS.Tables("My").Columns
				DT.Columns.Add(keyDC.ColumnName, keyDC.DataType)
			Next
			DT.Columns.Add("level", Type.GetType("System.Int32"))
			
			DR=DT.NewRow()
			Dim j As Integer
			For j=0 To DT.Columns.Count-2
				DR(j)=MyDS.Tables("My").Rows(0)(j)
			Next
			DR(DT.Columns.Count-1)=0
			DT.Rows.Add(DR)
			AddRepliesView(DR("BBSID"), 1, DT)

			DV.Table=DT

			Dim i As Integer
			For i=0 to DV.Table.Rows.Count-1
				MySQL ="DELETE FROM [BBS] WHERE BBSId="
				MySQL &= DV.Table.Rows(i)("BBSId")
				MySQL &= " "

				MyDB.strSQL=MySQL
				MyDB.ExecuteSQL()
				i+=1
			Next
		End Sub

		Private Sub ReadOnce(ByVal Id As Integer)
			'點擊了一次
			Dim MySQL As String="UPDATE [BBS] SET BBSClicks=BBSClicks+1"
			MySQL &= " WHERE BBSId="
			MySQL &= Id & " "

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			MyDB.ExecuteSQL()
		End Sub
		
		Public Function GetBBSView(ByVal CurPage As Integer, ByVal PageSize As Integer, ByRef TotalPage As Integer) As DataView
			'取得一個 BBS 格式的視圖
			Dim MySQL As String="SELECT * FROM [BBS] WHERE BBSParent=0 ORDER BY BBSTime DESC"

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

			Dim DV As DataView=New DataView()
			Dim DT As DataTable=New DataTable("My")

			Dim keyDC As DataColumn
			Dim DR As DataRow
			For Each keyDC In MyDS.Tables("My").Columns
				DT.Columns.Add(keyDC.ColumnName, keyDC.DataType)
			Next
			DT.Columns.Add("level", Type.GetType("System.Int32"))
			
			Dim i,j As Integer
			Dim StartItem As Integer= (CurPage-1) * PageSize + 1
			Dim EndItem As Integer= CurPage * PageSize
			Dim TotalItem As Integer=MyDS.Tables("My").Rows.Count
			TotalPage=TotalItem\PageSize+1
			If TotalItem Mod PageSize=0 Then
				TotalPage=TotalPage-1
			End If

			If EndItem>TotalItem Then
				EndItem=TotalItem
			End If
			For i=StartItem To EndItem
				DR=DT.NewRow()
				For j=0 To DT.Columns.Count-2
					DR(j)=MyDS.Tables("My").Rows(i-1)(j)
				Next
				DR(DT.Columns.Count-1)=0
				DT.Rows.Add(DR)
				AddRepliesView(DR("BBSID"), 1, DT)
			Next
			DV.Table=DT

			Return DV
		End Function

		Private Sub AddRepliesView(ByVal Parent As Integer, ByVal level As Integer, ByRef DT As DataTable)
			Dim DR As DataRow
			Dim i,j As Integer
			Dim MySQL As String="SELECT * FROM [BBS] WHERE BBSParent="
			MySQL &= Parent

			Dim MyDB As SQLDB=New SQLDB()
			MyDB.strSQL=MySQL
			Dim MyDS As DataSet=MyDB.SelectFromDB()
			Dim NumOfReplies As Integer=MyDS.Tables("My").Rows.Count

			For i=1 To NumOfReplies
				DR=DT.NewRow()
				For j=0 To DT.Columns.Count-2
					DR(j)=MyDS.Tables("My").Rows(i-1)(j)
				Next
				DR(DT.Columns.Count-1)=level
				DT.Rows.Add(DR)
				AddRepliesView(DR("BBSID"), level+1, DT)
			Next
		End Sub

		Public Function GetBBSTree(ByVal Parent As Integer) As DataView
			Dim MySQL As String="SELECT * FROM [BBS] WHERE BBSId="
			MySQL &= FindParent(Parent)

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

			Dim DV As DataView=New DataView()
			Dim DT As DataTable=New DataTable("My")

			Dim keyDC As DataColumn
			Dim DR As DataRow
			For Each keyDC In MyDS.Tables("My").Columns
				DT.Columns.Add(keyDC.ColumnName, keyDC.DataType)
			Next
			DT.Columns.Add("level", Type.GetType("System.Int32"))
			
			DR=DT.NewRow()
			Dim j As Integer
			For j=0 To DT.Columns.Count-2
				DR(j)=MyDS.Tables("My").Rows(0)(j)
			Next
			DR(DT.Columns.Count-1)=0
			DT.Rows.Add(DR)
			AddRepliesView(DR("BBSID"), 1, DT)

			DV.Table=DT

			Return DV
		End Function

		Private Function FindParent(ByVal Son As Integer) As Integer
			Dim Parent As Integer
			Dim MySQL As String="SELECT BBSParent, BBSId FROM [BBS] WHERE BBSId="
			MySQL &= Son

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

			If MyDS.Tables("My").Rows(0)("BBSParent")=0 Then
				Return MyDS.Tables("My").Rows(0)("BBSId")
			Else
				Return FindParent(MyDS.Tables("My").Rows(0)("BBSParent"))
			End If
		End Function
		
	End Class

End Namespace