Imports System.Data.SqlClient
Imports System.IO
Public Class DBController
    Private connectingstring As String
    Private myConn As SqlConnection
    Private ds As DataSet
    Private myAdapter As SqlDataAdapter
    Private myCmd As SqlCommand
    Public Sub New()
        connectingstring = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString").ToString.Trim
        myConn = New SqlConnection(connectingstring)
        'Close()
    End Sub
    '打開關閉連接
    Public Sub Open()
        myConn.Open()
    End Sub
    Public Sub Close()
        myConn.Close()
    End Sub
    Public Function GetDS() As DataSet
        Return ds
    End Function
    '=============================================================
    ' 函 數 名：Fill
    ' 功能描述：填充ds
    ' 輸入參數：sqlstr，SQL字符串
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Sub Fill(ByVal sqlstr As String)
        myAdapter = New SqlDataAdapter(sqlstr, myConn)
        ds = New DataSet()
        myAdapter.Fill(ds)
    End Sub
    '=============================================================
    ' 函 數 名：ExecNonSql
    ' 功能描述：執行無返回值的數據庫操作
    ' 輸入參數：sqlstr，查詢的SQL字符串
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Sub ExecNonSql(ByVal sqlstr As String)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        myCmd = New SqlCommand(sqlstr, myConn)
        myCmd.ExecuteNonQuery()
        myCmd.Dispose()
        Close()
    End Sub
    '=============================================================
    ' 函 數 名：ExecReaderSql
    ' 功能描述：執行查詢操作
    ' 輸入參數：sqlstr，查詢的SQL字符串
    ' 返 回 值：查詢結果，返回SqlDataReader對象
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Function ExecReaderSql(ByVal sqlstr As String) As SqlDataReader
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        myCmd = New SqlCommand(sqlstr, myConn)
        Dim reader As SqlDataReader
        reader = myCmd.ExecuteReader
        myCmd.Dispose()
        Return reader
    End Function
    '=============================================================
    ' 函 數 名：BindDBGrd
    ' 功能描述：對DataGrid進行數據綁定，無排序
    ' 輸入參數：sqlstr，查詢的SQL字符串；myDBGrd，需數據綁定的DataGrid控件
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        myDBGrd.DataSource = ds.Tables(0).DefaultView
        myDBGrd.DataBind()
    End Sub
    '=============================================================
    ' 函 數 名：BindDBGrd
    ' 功能描述：對DataGrid進行數據綁定，排序
    ' 輸入參數：sqlstr，查詢的SQL字符串；myDBGrd，需數據綁定的DataGrid控件
    '           SortExp：排序方式
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Sub BindDBGrd(ByVal sqlstr As String, ByVal myDBGrd As DataGrid, ByVal SortExp As Object)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        Dim dv As DataView = ds.Tables(0).DefaultView
        dv.Sort = SortExp
        myDBGrd.DataSource = dv
        myDBGrd.DataBind()
    End Sub
    '=============================================================
    ' 函 數 名：BindListBox
    ' 功能描述：對ListBox中進行綁定
    ' 輸入參數：sqlstr，查詢的SQL字符串；myListBox：需數據綁定的ListBox控件
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================
    Public Sub BindListBox(ByVal sqlstr As String, ByVal myListBox As ListBox)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        Dim dv As DataView = ds.Tables(0).DefaultView
        myListBox.DataSource = dv
        myListBox.DataBind()
    End Sub
 
    '=============================================================
    ' 函 數 名：BindDrpDownList
    ' 功能描述：對DropDownList進行數據綁定
    ' 輸入參數：sqlstr，查詢的SQL字符串；DrpDownList：需數據綁定的DropDownList控件
    ' 返 回 值：無
    ' 創建日期：2004-8-12
    ' 修改日期：2004-8-12
    ' 作    者：
    ' 附加說明：
    '==============================================================

    Public Sub BindDrpDownList(ByVal sqlstr As String, ByVal DrpDownList As DropDownList)
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Fill(sqlstr)
        DrpDownList.DataSource = ds.Tables(0).DefaultView
        DrpDownList.DataBind()
        Close()
    End Sub
  
    ' 功能描述：判斷用戶用戶名、密碼是否正確
    Public Function CheckUserberInfo(ByVal Userstr As String, ByVal Pwdstr As String) As Integer
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If
        Dim sqlstr As String
        If Pwdstr = "" Then
            sqlstr = "select * from UserInfo where UserID='" + Userstr.Trim + "' and Pass is null"
        Else
            sqlstr = "select * from UserInfo where UserID='" + Userstr.Trim + "' and Pass='" + Pwdstr + "'"
        End If

        Fill(sqlstr)
        If ds.Tables(0).Rows.Count = 0 Then
            Close()
            Return -1
        End If
        ds.Clear()
        Close()
        Return 1
    End Function
    ' 功能描述：獲取SQL查詢記錄條數
    Public Function GetRowsNum(ByVal sqlstr As String) As Integer
        If myConn.State = ConnectionState.Closed Then
            myConn.Open()
        End If

        Fill(sqlstr)
        Close()
        Return ds.Tables(0).Rows.Count
    End Function

End Class
Public Class BookController
    Private obj As DBController
    Public Sub New()
        obj = New DBController
    End Sub
    '獲取用戶最大的借書量
    Public Function GetMax(ByVal UserID As String) As Integer
        Dim sqlstr As String = "select BookNum from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID"

        Dim myreader As SqlDataReader = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("BookNum")
        End If
        obj.Close()
        Return num
    End Function
    '獲取該用戶允許借閱時間
    Public Function GetBookDate(ByVal UserID As String) As Integer
        Dim sqlstr As String = "select BookDate from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and UserID='" + UserID + "'"

        Dim myreader As SqlDataReader = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("BookDate")
        End If
        obj.Close()
        Return num
    End Function
    '判斷當前書的狀態
    '-2：表示該書不存在或不在流通中
    '-1：表示被借
    '0：已經被續借狀態
    '1：可借
    Public Function GetBookState(ByVal BookID As String) As Integer

        Dim sqlstr As String = "select * from BookInfo where BookState=1 and  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return -2
        End If

        sqlstr = "select * from LendInfo where IsBack=0 and BookID =" + BookID
        If obj.GetRowsNum(sqlstr) <> 0 Then
            Return -1
        End If

        sqlstr = "select * from BookingInfo where  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) <> 0 Then
            Return 0
        End If

        Return 1

    End Function
    '判斷用戶是否預定該本書
    Public Function IsUserBooking(ByVal UserID As String, ByVal BookID As String) As Boolean

        Dim sqlstr As String = "select * from BookingInfo where UserID='" + UserID + "' and  BookID=" + BookID
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '獲取用戶當前借書數量
    Public Function GetCurNum(ByVal UserID As String) As Integer
        Dim sqlstr As String = "select count(*) as Num from LendInfo where UserID='" + UserID + "' and IsBack=0 "

        Dim myreader As SqlDataReader = obj.ExecReaderSql(sqlstr)
        Dim num As Integer = 0
        If myreader.Read Then
            num = myreader("Num")
        End If
        obj.Close()
        Return num
    End Function
    '進行借書操作，如果借書成功，返回true，否則返回false
    Public Function LendBook(ByVal UserID As String, ByVal BookID As String) As Boolean
        Dim datenum As Integer = GetBookDate(UserID)
        Dim nowdate As Date = Date.Now
        Dim ReturnDate As Date = nowdate.AddDays(datenum)
        Dim sqlstr As String = "insert into LendInfo(BookID,UserID,LendDate,ReturnDate,IsBack)values( "
        sqlstr += "'" + BookID + "'"
        sqlstr += ",'" + UserID + "'"
        sqlstr += ",'" + nowdate.ToShortDateString + "'"
        sqlstr += ",'" + ReturnDate.ToShortDateString + "'"
        sqlstr += ",0)"
        obj.ExecNonSql(sqlstr)
        If IsUserBooking(UserID, BookID) Then
            sqlstr = "delete from  BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'"
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '取消預約書信息
    Public Function CancelBooking(ByVal UserID As String, ByVal BookID As String) As Boolean
        If IsUserBooking(UserID, BookID) Then
            Dim sqlstr As String = "delete from  BookingInfo where BookID=" + BookID + " and UserID='" + UserID + "'"
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '續借圖書
    Public Function ReLendBook(ByVal UserID As String, ByVal BookID As String) As Boolean
        Dim bookdate As Integer = GetBookDate(UserID)
        Dim sqlstr As String = "update LendInfo set ReturnDate=ReturnDate+" + bookdate.ToString + " where IsBack=0 and BookID=" + BookID + " and UserID='" + UserID + "'"
        obj.ExecNonSql(sqlstr)
        Return True
    End Function
    '還書操作
    Public Function ReturnBook(ByVal UserID As String, ByVal BookID As String) As Boolean
        Dim sqlstr As String = "update LendInfo set IsBack=1 where BookID=" + BookID + " and UserID='" + UserID + "'"
        obj.ExecNonSql(sqlstr)
        '修改預約狀態
        If Me.GetBookState(BookID) = 0 Then
            sqlstr = "update BookingInfo set BookingState=1 where BookID=" + BookID
            obj.ExecNonSql(sqlstr)
        End If
        Return True
    End Function
    '預約圖書信息
    Public Sub BookingBook(ByVal userID As String, ByVal BookID As String)
        Dim sqlstr As String = "insert into BookingInfo(BookID,UserID,BookingState)values( "
        sqlstr += BookID + ",'" + userID + "',0)"
    End Sub
    '是否允許預約
    Public Function AllowBooking(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and AllowBooking=1  and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否允許續借
    Public Function AllowReLend(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and ReLend=1  and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否為圖書管理員
    Public Function IsBookManager(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsBookManager=1 and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '是否為系統管理員
    Public Function IsSysManager(ByVal UserID As String) As Boolean
        Dim sqlstr As String = "select * from UserInfo,RoleInfo where UserInfo.RoleID=RoleInfo.RoleID and IsSysManager=1  and UserID='" + UserID + "'"
        If obj.GetRowsNum(sqlstr) = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

End Class