frmborrowbook.vb

The Management Information System of Library using .NET

        Dim dsAuBorrowList As New DataSet

        cnlib2004 = New SqlConnection(cnstr)
        cmdAuBorrowList = New SqlCommand
        With cmdAuBorrowList
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SelectAuBorrowList"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmdAuBorrowList.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id
        daAuBorrowList = New SqlDataAdapter(cmdAuBorrowList)

        Try
            daAuBorrowList.Fill(dsAuBorrowList)
            Me.dbgBorrowList.DataSource = dsAuBorrowList.Tables(0)
            '以下代码目的是获取服务器时间,使判断图书超期不以本机时间为准,而以服务器时间为准
            '代码可以避免时间的不一致性
            cmdAuBorrowList.CommandType = CommandType.Text
            cmdAuBorrowList.CommandText = "select getdate()"
            cnlib2004.Open()
            Dim currdate As DateTime
            currdate = cmdAuBorrowList.ExecuteScalar
            'MsgBox(currdate)
            '以下代码加亮显示已经超期的图书
            Dim row As DataRow
            With dsAuBorrowList.Tables(0)
                Dim n As Int16 = 0
                For Each row In .Rows
                    If row.Item("应归还日期") < currdate Then
                        Me.dbgBorrowList.Select(n)
                    End If
                    n += 1
                Next
            End With
            '代码结束
        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    '显示某读者基本情况
    Function AuTextBind(ByVal strAu_id As String)
        Dim cnlib2004 As SqlConnection
        Dim cmdAuBorrowList As SqlCommand
        Dim drAuBorrowList As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmdAuBorrowList = New SqlCommand
        With cmdAuBorrowList
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Select1Au"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmdAuBorrowList.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id
        Try
            cnlib2004.Open()
            drAuBorrowList = cmdAuBorrowList.ExecuteReader(CommandBehavior.SingleRow)
            With drAuBorrowList
                If .Read() Then
                    Me.txtAu_adddate.Text = CType(.Item("Au_adddate"), String)
                    Me.txtAu_adr.Text = .Item("Au_adr")
                    Me.txtAu_name.Text = .Item("Au_name")
                    Me.txtAu_sex.Text = .Item("Au_sex")
                    Me.txtAu_sort.Text = .Item("Au_sort")
                    Me.PubAu_id = strAu_id
                Else
                    MessageBox.Show("无此读者!", "信息", 0, MessageBoxIcon.Exclamation)
                    Me.PubAu_id = ""
                    clearAu()
                End If
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    '显示某本书的基本情况
    Function BookTextBind(ByVal strBook_code As String)
        Dim cnlib2004 As SqlConnection
        Dim cmd1Book As SqlCommand
        Dim dr1Book As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmd1Book = New SqlCommand
        With cmd1Book
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Select1Book"
            .Connection = cnlib2004
        End With
        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50) '1
        cmd1Book.Parameters.Add(mBook_code)
        mBook_code.Value = strBook_code
        Try
            cnlib2004.Open()
            dr1Book = cmd1Book.ExecuteReader(CommandBehavior.SingleRow)
            With dr1Book
                If .Read() Then
                    txtBook_author.Text = .Item("Book_author")
                    'txtBook_code.Text = .Item("Book_code")
                    txtBook_isbn.Text = .Item("Book_isbn")
                    txtBook_name.Text = .Item("Book_name")
                    txtBook_page.Text = CType(.Item("Book_page"), String)
                    txtBook_place.Text = .Item("Book_place")
                    txtBook_price.Text = CType(.Item("Book_price"), String)
                    txtBook_pub.Text = .Item("Book_pub")
                    txtBook_pubdate.Text = CType(.Item("Book_pubdate"), String)
                    txtBook_remarks.Text = .Item("Book_remarks")
                    txtBook_sort.Text = .Item("Book_sort")

                    dr1Book.Close()
                    cmd1Book.CommandText = "Book1IsInLib"
                    Dim n As Int16 = CType(cmd1Book.ExecuteScalar, Int16)
                    If n = 0 Then
                        Me.lblIsInLib.Text = "不可借"
                        Me.PubBook_code = "不可借"
                    Else
                        Me.lblIsInLib.Text = "可借"
                        Me.PubBook_code = strBook_code
                    End If
                Else
                    MessageBox.Show("没有此图书相关信息!", "信息", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.lblIsInLib.Text = ""
                    PubBook_code = ""
                    clearBook()
                End If
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    '显示读者借书状态 最大借书册书,当前借书册数 等
    Function setBorrowState(ByVal strAu_id As String)
        Dim cnlib2004 As SqlConnection
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmd = New SqlCommand
        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SelectAuCurrentBorrowNo"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmd.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id

        Try
            cnlib2004.Open()
            CurBorrowNo = CType(cmd.ExecuteScalar, Int16)

            cmd.CommandText = "SelectAuAllBorrowNo"
            AllborrowNo = CType(cmd.ExecuteScalar, Int16)

            cmd.CommandText = "SelectAuSortInfo"
            dr = cmd.ExecuteReader(CommandBehavior.SingleRow)

            While dr.Read()
                BorrowDays = dr.Item("Au_borrowdays")
                BorrowBooks = dr.Item("Au_borrowbooks")
            End While

            Me.txtTotalBorrowNo.Text = "共借书" + AllborrowNo.ToString + "册"
            Me.txtMaxBorrowDays.Text = BorrowDays.ToString + "天"

            Me.txtBorrowQinkuang.Text = "[" + CurBorrowNo.ToString + "/"
            Me.txtBorrowQinkuang.Text += BorrowBooks.ToString + "]"
        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function

    Private Sub txtAu_id_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAu_id.KeyPress
        If sender.text.trim = "" Then
            Exit Sub
        End If
        If e.KeyChar.Equals(Chr(13)) Then
            databind(sender.text)
            AuTextBind(sender.text)
            If Me.PubAu_id <> "" Then
                Me.setBorrowState(sender.text)
            End If
        End If
    End Sub

    Private Sub txtBook_code_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBook_code.KeyPress
        If e.KeyChar.Equals(Chr(13)) Then
            'databind(sender.text)
            BookTextBind(sender.text)
        End If
    End Sub

    Private Sub BorrowBook()
        If Me.PubAu_id = "" Or Me.PubBook_code = "" Then
            MessageBox.Show("请调出读者和图书的详细信息再借阅!", "借阅失败", _
             MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        ElseIf Me.PubBook_code = "不可借" Then
            MessageBox.Show("该图书已经借出!", "借阅失败", _
                         MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If Me.CurBorrowNo >= Me.BorrowBooks Then
            MessageBox.Show("对不起,您已经超过最大借书数!", "借书失败", _
             MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        ElseIf Me.AuIsLost(Me.PubAu_id) Then
            MessageBox.Show("此图书证已经挂失,请予核实!!", "警告", _
                         MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Exit Sub
        Else
            Borrow1Book(Me.PubAu_id, Me.PubBook_code, Me.BorrowDays)
        End If
    End Sub
    Function Borrow1Book(ByVal AUID As String, ByVal BOOKCODE As String, ByVal BORROWDAY As Int16)
        Dim cnlib2004 As SqlConnection
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmd = New SqlCommand
        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Insert1Borrow"
            .Connection = cnlib2004
        End With

        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50)
        Dim mAu_borrowdays As New SqlParameter("@Au_borrowdays", SqlDbType.Int, 4)
        With cmd.Parameters
            .Add(mAu_id)
            .Add(mBook_code)
            .Add(mAu_borrowdays)
        End With

        mAu_id.Value = AUID
        mBook_code.Value = BOOKCODE
        mAu_borrowdays.Value = BORROWDAY


        Try
            cnlib2004.Open()
            cmd.ExecuteNonQuery()
            Me.PubBook_code = ""
            Me.databind(AUID)
            Me.setBorrowState(AUID)

            Me.borrowSuccess()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    Sub clearAu()
        Me.txtAu_adddate.Text = ""
        Me.txtAu_adr.Text = ""
        Me.txtAu_name.Text = ""
        Me.txtAu_sex.Text = ""
        Me.txtAu_sort.Text = ""
        Me.PubAu_id = ""

        Me.txtTotalBorrowNo.Clear()
        Me.txtBorrowQinkuang.Clear()
        Me.txtMaxBorrowDays.Clear()
    End Sub
    Sub clearBook()
        txtBook_author.Text = ""
        'txtBook_code.Text = .Item("Book_code")
        txtBook_isbn.Text = ""
        txtBook_name.Text = ""
        txtBook_page.Text = ""
        txtBook_place.Text = ""
        txtBook_price.Text = ""
        txtBook_pub.Text = ""
        txtBook_pubdate.Text = ""
        txtBook_remarks.Text = ""
        txtBook_sort.Text = ""
    End Sub
    '测试au_id的读者 借书证是否挂失
    '如果挂失 返回true
    '如果没挂失 返回False
    Private Function AuIsLost(ByVal au_id As String) As Boolean
        Dim cn As New SqlConnection(cnstr)
        Dim cmd As New SqlCommand
        Dim p As Int16

        With cmd
            .CommandType = CommandType.StoredProcedure
            .Connection = cn
            .CommandText = "SelectAuIsLost"
        End With
        Dim mLost_id As New SqlParameter("@Lost_id", SqlDbType.NVarChar, 50)
        cmd.Parameters.Add(mLost_id)
        mLost_id.Value = au_id
        Try
            cn.Open()
            If CType(cmd.ExecuteScalar, Int16) = 1 Then
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            cn.Close()
            If Not cn Is Nothing Then
                cn.Dispose()
            End If
        End Try
        Return False
    End Function
    Sub borrowSuccess()
        MessageBox.Show("借阅成功", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information)
        clearBook()
    End Sub

    Private Sub Reset()
        clearAu()
        clearBook()
        Me.PubAu_id = ""
        Me.PubBook_code = ""
        Me.txtBook_code.Clear()
        Me.txtAu_id.Clear()
        Me.txtAu_id.Focus()

        Dim p As DataTable
        p = CType(Me.dbgBorrowList.DataSource, DataTable)
        If Not p Is Nothing Then
            p.Clear()
            Me.dbgBorrowList.DataSource = p
        End If

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub


    Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
        If e.Button Is Me.tbtnQuery Then
            BorrowBook()
        ElseIf e.Button Is Me.tbtnReset Then
            Reset()
        ElseIf e.Button Is Me.tbtnExit Then
            Me.Close()
        End If
    End Sub
End Class