frmreturnbook.vb

The Management Information System of Library using .NET

        Dim cnlib2004 As SqlConnection
        Dim cmdAuBorrowList As SqlCommand
        Dim daAuBorrowList As SqlDataAdapter
        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")
                    Me.PubBook_code = strBook_code
                    dr1Book.Close()
                    cmd1Book.CommandText = "Book1IsBorrowByWho"
                    Dim n As String = CType(cmd1Book.ExecuteScalar, String)
                    Me.PubBookLender = n
                    Me.lblIsInLib.Text = n + "借阅"
                Else
                    MessageBox.Show("没有此图书相关信息!", "信息", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.lblIsInLib.Text = ""
                    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
    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

    Private Sub txtAu_id_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAu_id.KeyPress
        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 frmReturnBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With Me.dbgBorrowList
            .Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                Or System.Windows.Forms.AnchorStyles.Left) _
                Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            .BackColor = System.Drawing.Color.Snow
            .BackgroundColor = System.Drawing.SystemColors.ActiveCaptionText
            .CaptionVisible = False
            .HeaderForeColor = System.Drawing.SystemColors.ControlText
            .ReadOnly = True
            .SelectionBackColor = Color.Red
            .SelectionForeColor = Color.Yellow
        End With
    End Sub


    Private Sub dbgBorrowList_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dbgBorrowList.DoubleClick
        If sender.CurrentRowIndex <> -1 Then
            Dim str As String = CType(sender.Item(sender.CurrentRowIndex, 0), String)
            BookTextBind(str)
            Me.txtBook_code.Text = str
            Me.txtBook_code.Focus()
        Else
            MessageBox.Show("记录为空!", "info", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If

    End Sub

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        PubAu_id = ""
        PubBook_code = ""
        PubBookLender = ""
        Me.txtAu_id.Clear()
        Me.txtBook_code.Clear()
        Me.clearAu()
        Me.clearBook()

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

    End Sub

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

    Private Sub btnReturnBook_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReturnBook.Click
        If Me.PubAu_id = "" Or Me.PubBook_code = "" Then
            MessageBox.Show("请先调出读者和图书的详细信息!", "归还失败", _
             MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        ElseIf Me.PubAu_id <> Me.PubBookLender Then
            MessageBox.Show("该读者并没有借过这本书!", "归还失败", _
                         MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        'MessageBox.Show(Me.PubAu_id, Me.PubBook_code)
        Return1Book(Me.PubAu_id, Me.PubBook_code)

        clearBook()
        Me.lblIsInLib.Text = ""
        Me.txtBook_code.Text = ""
        Me.txtBook_code.Focus()
        Me.PubBook_code = ""
    End Sub
    Function Return1Book(ByVal AUID As String, ByVal BOOKCODE As String)
        'MessageBox.Show(AUID & BOOKCODE)
        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 = "Update1Borrow"
            .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_returndays", 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

    Private Sub dbgBorrowList_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles dbgBorrowList.Navigate

    End Sub
End Class