frm_losebook.vb

是关于图书管理系统的用vb.net 和access开发的

Imports System.Data.OleDb
Public Class frm_LoseBook

    Dim myConnection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data source=D:\library.mdb")
    Dim myCommand As OleDbCommand
    Dim myReader As OleDbDataReader

    Dim rentdate As DateTime
    Dim duedate As DateTime

    Dim overDateNo As Integer

    Dim finePerDay
    Dim totalFine As Decimal

    Dim readerno As String
    Dim isbn As String
    Dim rentNo As Integer
    Dim fineNo As Integer = 0


    Private Sub readerNotxt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles readerNotxt.KeyPress
        If e.KeyChar = ChrW(System.Windows.Forms.Keys.Enter) Then

            If checkValidReader() Then

                readerno = readerNotxt.Text
                setnamecombo()


            Else

                MsgBox("Invalid reader ID!")
                readerNotxt.Focus()
                readerNotxt.SelectAll()

            End If
        End If

    End Sub
    Sub setnamecombo()

        setIsbncombo()

        Dim i As Integer
        Dim temp As String


        myConnection.Open()

        For i = 0 To isbncombo.Items.Count - 1

            temp = isbncombo.Items(i).ToString

            myCommand = New OleDbCommand("select * from BookDetails where ISBN='" & temp & "'", myConnection)
            myReader = myCommand.ExecuteReader

            While myReader.Read
                namecombo.Items.Add(myReader("BookName"))
            End While
        Next

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub
    Sub setIsbncombo()

        myConnection.Open()
        myCommand = New OleDbCommand("select * from RentBook where ReaderNo='" & readerno & "'and Status='Nreturned'", myConnection)
        myReader = myCommand.ExecuteReader

        While myReader.Read
            isbncombo.Items.Add(myReader("ISBN"))
            rentNo = myReader("RentNo")
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub

   

  

    Private Sub namecombo_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles namecombo.SelectedValueChanged
      
        isbncombo.SelectedIndex = namecombo.SelectedIndex
        isbn = isbncombo.Items(isbncombo.SelectedIndex)
    End Sub




    Function checkValidReader() As Boolean

        myConnection.Open()
        myCommand = New OleDbCommand("select * from ReaderDetails where ReaderNo='" & readerNotxt.Text & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read
            readerno = myReader("ReaderNo")
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        If readerno = "" Then
            Return False
        Else
            Return True
        End If
    End Function

  

    Private Sub frm_LoseBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        namecombo.Text = "Select book name here"
    End Sub

  

    Private Sub namecombo_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles namecombo.DropDown

        If readerNotxt.Text = "" Then
            MsgBox("Input reader No. first!")
        End If
    End Sub

    Private Sub calbt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles calbt.Click
        If finePerDaytxt.Text = "" Then
            MsgBox("Input fine per day please!")
        Else
            finePerDay = finePerDaytxt.Text
            calculateFine()
        End If
    End Sub

    Sub calculateFine()

        Dim bookPrice As Decimal

        Dim oupstr = ""

        bookPrice = getBookPrice()
        totalFine = bookPrice + getOverDueFine()

        oupstr += "BookPrice: " & bookPrice & ControlChars.NewLine _
        & "Rent Date:" & rentdate & ControlChars.NewLine _
  & "Due Date:" & duedate & ControlChars.NewLine _
  & isOverdueStr() & ControlChars.NewLine & "Total Fine:" & totalFine & ControlChars.NewLine


        MsgBox(oupstr)

    End Sub
    Function isOverdueStr() As String
        If overDateNo < 0 Then
            Return ""
        Else
            Return "Exceed overdue date: " & overDateNo & " days"
        End If
    End Function

    Function getBookPrice() As Decimal
        Dim price As Decimal


        myConnection.Open()
        myCommand = New OleDbCommand("select * from BookDetails where ISBN='" & isbn & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read
            price = Decimal.Parse(myReader("Price"))

        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        Return price


    End Function

    Function getOverDueFine() As Decimal

        Dim overDatetemp As TimeSpan
        Dim fine As Decimal

        myConnection.Open()
        myCommand = New OleDbCommand("select * from RentBook where ISBN='" & isbn & "' and ReaderNo='" & readerno & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read
            rentdate = DateTime.Parse(myReader("RentDate"))
            duedate = DateTime.Parse(myReader("DueDate"))
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        overDatetemp = DateTime.Now.Subtract(duedate)
        overDateNo = overDatetemp.TotalDays - 1

        If overDateNo > 0 Then

            fine = overDateNo * finePerDay

            If fine > 100 Then
                fine = 100.0
            End If

        Else
            fine = 0.0

        End If

        Return fine

    End Function

    Private Sub paidbt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles paidbt.Click


        If readerNotxt.Text = "" Or isbncombo.Text = "" Or admNotxt.Text = "" Or passwordtxt.Text = "" Then
            MsgBox("Information is not enough!")
        ElseIf checkValidAdm() Then
            setfineNo()
            afterPaid()
        End If

End Sub
    Function checkValidAdm() As Boolean

        Dim admNo As String = ""
        Dim password As String = ""


        myConnection.Open()
        myCommand = New OleDbCommand("select * from Administrator where AdmNo = '" & admNotxt.Text & _
           "'", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read
            admNo = myReader("AdmNo")
            password = myReader("Password")
        End While

        myConnection.Close()
        myReader.Close()
        myCommand.Dispose()

        If admNo = "" Then
            MsgBox("Invalid Administrator ID!")
        Else

            If password = passwordtxt.Text Then
                Return True
            Else

                MsgBox("Invalid Administrator Password!")
                Return False

            End If
        End If
    End Function
    Sub afterPaid()
        changeBookDetailStatus()
        changeRentBookStatus()
        updateFine()
    End Sub
    Sub changeBookDetailStatus()
        myConnection.Open()

        myCommand = New OleDbCommand("Update BookDetails set Status= 'Lost' where ISBN='" & isbn & "'", myConnection)

        myCommand.ExecuteNonQuery()

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()
    End Sub
    Sub changeRentBookStatus()
        myConnection.Open()

        myCommand = New OleDbCommand("Update RentBook set Status= 'LostButPaid' where RentNo=" & rentNo, myConnection)

        myCommand.ExecuteNonQuery()

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub
    Sub updateFine()
        Dim fineDate As Date = Now

            myConnection.Open()

        myCommand = New OleDbCommand("INSERT INTO Fine VALUES( " & _
          fineNo & ",'" & isbn & "','" & readerno & "','" & admNotxt.Text & "','" _
          & fineDate & "','" & totalFine & "','paid','Book lost')", myConnection)


        myCommand.ExecuteNonQuery()
            myConnection.Close()
            myCommand.Dispose()
            myReader.Close()

        MsgBox("The fine record is added successfully!")

    End Sub
    Sub setfineNo()

        myConnection.Open()
        myCommand = New OleDbCommand("select * from Fine", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read()
            fineNo = fineNo + 1
        End While

        myConnection.Close()
        myReader.Close()
        myCommand.Dispose()

        fineNo = fineNo + 1

    End Sub
End Class