frmmain.frm

图书馆管理

         Caption         =   "借阅查询"
         Shortcut        =   ^Y
      End
   End
   Begin VB.Menu mnuSetSys 
      Caption         =   "系统设置"
      Begin VB.Menu mnuSetBasic 
         Caption         =   "基本设置"
         Shortcut        =   ^B
      End
      Begin VB.Menu mnuSetBook 
         Caption         =   "图书类别"
         Shortcut        =   ^T
      End
   End
   Begin VB.Menu sys_manage 
      Caption         =   "登录管理"
      Begin VB.Menu add_admin 
         Caption         =   "添加管理员"
      End
      Begin VB.Menu change_pwd 
         Caption         =   "修改密码"
      End
   End
End
Attribute VB_Name = "Frmmain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private Sub chkQuery_Click()
If chkQuery.Value = 1 Then
   txtQueryBookID.Enabled = True
Else
   txtQueryBookID.Enabled = False
End If
End Sub

Private Sub add_admin_Click()
frmadduser.Show
End Sub

Private Sub change_pwd_Click()
frmchangepwd.Show
End Sub

Private Sub cmdLendBook_Click()
'正常借书的借出书籍
If txtReaderID.Text <> "" Then '判断是否输入读者编号
    
    If lblRemain.Caption <> "0" Then  '判断是否已经借满
        '进行lentInfo数据表信息的添加
        Set g_rs = g_db.OpenRecordset("lentInfo", dbOpenTable)
        With g_rs
            .AddNew
            .Fields("读者编号") = txtReaderID.Text
            .Fields("书籍编号") = txtBookID.Text
            .Fields("借书日期") = dtpLendDate.Value
            .Update
        End With
        Set g_rs = Nothing
        
        '更新bookInfo表,设置该书是否借出属性为"借出"
        g_strSql = "select * from bookInfo where 书籍编号='" & txtBookID.Text & "'"
        Set g_rs = g_db.OpenRecordset(g_strSql)
        g_rs.Edit
        g_rs.Fields("是否借出").Value = True
        g_rs.Update
        Set g_rs = Nothing
        
         MsgBox "借出完毕!", vbOKOnly, "提示"
         txtBookID.Text = ""
          txtBookName.Text = ""
        txtBookPrice.Text = ""
        txtBookLeibie.Text = ""
        txtBookConcern.Text = ""
        txtBookPage.Text = ""
         
         InitDataGrid (False) '进行DataGrid控件的初始化，False参数表明是正常借书
    Else
         MsgBox "您的书已经借满,不能再借!", vbOKOnly, "提示"
    
    End If
Else
    MsgBox "请先输入读者编号!", vbOKOnly, "提示"
End If
End Sub

Private Sub cmdOK_Click()
    '续借书籍
    g_strSql = "select * from lentInfo where 书籍编号='" & txtBookIDRenew.Text & "' and 读者编号='" & txtReaderIDRenew.Text & "' and 借书日期=#" & txtLendDate.Text & "#"
    Set g_rs = g_db.OpenRecordset(g_strSql)
    g_rs.Edit
    g_rs.Fields("借书日期").Value = dtpLendDateChange.Value
    g_rs.Update
    Set g_rs = Nothing
    Adodc1.Recordset.Update
    MsgBox "续借完毕!", vbOKOnly, "提示"

End Sub
Private Sub cmdReturn_Click()
On Error GoTo err
'归还书籍
    g_strSql = "select * from lentInfo where 书籍编号='" & txtBookIDReturn.Text & "' and 读者编号='" & txtReadIDReturn.Text & "' and 借书日期=#" & txtLendDateReturn.Text & "#"
    Set g_rs = g_db.OpenRecordset(g_strSql)
    g_rs.Edit
    g_rs.Fields("还书日期").Value = txtReturnDate.Text
    g_rs.Fields("超出天数").Value = txtDayCount.Text
    g_rs.Fields("罚款金额").Value = txtFakuan.Text
    g_rs.Delete
    g_rs.CancelUpdate
    
err:
    Set g_rs = Nothing
         '更新bookInfo表中"是否借出"字段为false,表明尚可未借出
    g_strSql = "select * from bookInfo where 书籍编号='" & txtBookIDReturn.Text & "'"
    Set g_rs = g_db.OpenRecordset(g_strSql)
    
    g_rs.Edit
    
    g_rs.Fields("是否借出").Value = False
    g_rs.Update
    Set g_rs = Nothing
         '清空归还书籍的信息
    txtBookIDReturn.Text = ""
    txtBookNameReturn.Text = ""
    txtBookPriceReturn.Text = ""
    txtBookLeibieReturn.Text = ""
    txtBookPageReturn.Text = ""
    txtReadIDReturn.Text = ""
    txtReaderNameReturn.Text = ""
    txtLendDateReturn.Text = ""
    txtDay.Text = ""
    txtReturnDate.Text = ""
    txtFactDay.Text = ""
    txtDayCount.Text = ""
    cmdReturn.Enabled = False
    MsgBox "归还完毕!", vbOKOnly, "提示"
End Sub

Private Sub Form_Load()
'在主窗口打开的同时进行数据库的连接
 dtpLendDate.Value = Date
 dtpLendDateChange.Value = Date

 Set g_ws = DBEngine.Workspaces(0)
 Set g_db = g_ws.OpenDatabase(App.Path + "\图书馆查询管理系统.mdb")
 Me.Hide
 frmLogin.Show
End Sub

Private Sub Form_Unload(Cancel As Integer)
'在主窗体关闭的同时关闭与数据库的连接
 g_db.Close
 Set g_db = Nothing
 g_ws.Close
 Set g_ws = Nothing
 End
End Sub

Private Sub mnuBookManage_Click()
FrmBookManage.Show
End Sub

Private Sub mnuBookQuery_Click()
FrmBookQuery.Show
End Sub
Private Sub mnuLendQuery_Click()
FrmLendQuery.Show
End Sub
Private Sub mnuSetBasic_Click()
FrmSetBasic.Show
End Sub

Private Sub mnuSetBook_Click()
FrmSetBook.Show
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)

'根据点击工具栏按钮的caption属性值进行不同的操作
Select Case Button.Caption
    Case "书库管理"
        FrmBookManage.Show
    Case "新书"
        FrmAddnew.Show
    Case "借阅列表"
        FrmLendList.Show
    Case "书库查询"
        FrmBookQuery.Show
    Case "图书类别"
        FrmSetBook.Show
    Case "退出"
        Call Form_Unload(0)
End Select
End Sub


Private Sub txtBookID_KeyPress(KeyAscii As Integer)

'判断用户按下回车键并且是否输入读者编号和书籍编号
If KeyAscii = "13" And txtReaderID.Text <> "" And txtBookID.Text <> "" Then

    g_strSql = "select bookInfo.书籍名称,bookInfo.书籍价格,bookInfo.出版社,bookInfo.书籍页码," _
            & "bookInfo.是否借出,bookType.书籍类别 from bookInfo,bookType where 书籍编号='" & txtBookID.Text & "'" _
            & " and bookInfo.类别代码=bookType.类别代码"
     Set g_rs = g_db.OpenRecordset(g_strSql)
    
    If Not g_rs.EOF Then '判断是否查到该书籍信息
        txtBookName.Text = g_rs!书籍名称
        txtBookPrice.Text = g_rs!书籍价格
        txtBookLeibie.Text = g_rs!书籍类别
        txtBookConcern.Text = g_rs!出版社
        txtBookPage.Text = g_rs!书籍页码
        
        If g_rs!是否借出 = True Then '判断该书籍是否借出
            MsgBox "该书已经借出,请选择其它图书!", vbOKOnly, "提示"
            cmdLendBook.Enabled = False
        Else
            cmdLendBook.Enabled = True
        End If
    Else
        MsgBox "没有该书信息!", vbOKOnly, "提示"
        txtBookName.Text = ""
        txtBookPrice.Text = ""
        txtBookLeibie.Text = ""
        txtBookConcern.Text = ""
        txtBookPage.Text = ""
    End If
     Set g_rs = Nothing
    
ElseIf KeyAscii = "13" And txtReaderID.Text = "" Then
   MsgBox "请先输入读者编号", vbOKOnly, "提示"
ElseIf KeyAscii = "13" And txtReaderID.Text <> "" And txtBookID.Text = "" Then
   MsgBox "请先输入书籍编号", vbOKOnly, "提示"
End If

End Sub


Private Sub txtBookIDReturn_KeyPress(KeyAscii As Integer)
Dim strSQL As String

'判断用户按下回车键并且是否输入读者编号和书籍编号
 If KeyAscii = "13" And txtBookIDReturn.Text <> "" Then

    strSQL = "select lentInfo.读者编号,readerInfo.读者姓名,lentInfo.书籍编号,bookInfo.书籍价格," _
    & " bookInfo.书籍名称,bookInfo.书籍页码,lentInfo.借书日期,bookType.书籍类别,bookType.借出天数" _
    & " from readerInfo,bookInfo,lentInfo,bookType where readerInfo.读者编号=lentInfo.读者编号" _
    & " and bookInfo.书籍编号=lentInfo.书籍编号 and bookInfo.书籍编号='" & txtBookIDReturn.Text & "'" _
    & " and bookInfo.类别代码=bookType.类别代码"

     g_strSql = strSQL
     Set g_rs = g_db.OpenRecordset(g_strSql)

    If Not g_rs.EOF Then     '判断是否查到该书籍信息
        cmdLendBook.Enabled = True
        txtBookNameReturn.Text = g_rs!书籍名称
        txtBookPriceReturn.Text = g_rs!书籍价格
        txtBookLeibieReturn.Text = g_rs!书籍类别
        txtBookPageReturn.Text = g_rs!书籍页码
        txtReadIDReturn.Text = g_rs!读者编号
        txtReaderNameReturn.Text = g_rs!读者姓名
        txtLendDateReturn.Text = g_rs!借书日期
        txtDay.Text = g_rs!借出天数
        
        txtReturnDate.Text = Date     '设置还书日期为当前日期
        txtFactDay.Text = CStr(Date - g_rs!借书日期)   '设置实际借书天数
        
        '设置超期天数
        If CInt(txtFactDay.Text) - CInt(txtDay.Text) > 0 Then
            txtDayCount.Text = CStr(-Val(txtFactDay.Text) + Val(txtReturnDate.Text))
        Else
            txtDayCount.Text = "0"
        End If
        Set g_rs = Nothing
        
        g_strSql = strSQL
        Set g_rs = g_db.OpenRecordset("select * from basicSet")
        txtFakuan.Text = CStr(g_rs!罚款 * Val(txtDayCount.Text))   '设置罚款金额
        Set g_rs = Nothing
        cmdReturn.Enabled = True
        
    Else
        Set g_rs = Nothing
        MsgBox "没有该书信息!", vbOKOnly, "提示"
        txtBookNameReturn.Text = ""
        txtBookPriceReturn.Text = ""
        txtBookLeibieReturn.Text = ""
        txtBookPageReturn.Text = ""
        txtReadIDReturn.Text = ""
        txtReaderNameReturn.Text = ""
        txtLendDateReturn.Text = ""
        txtDay.Text = ""
        txtReturnDate.Text = ""
        txtFactDay.Text = ""
        txtDayCount.Text = ""
        cmdReturn.Enabled = False
        
    End If
     
    
ElseIf KeyAscii = "13" And txtBookIDReturn.Text = "" Then
   MsgBox "请先输入书籍编号", vbOKOnly, "提示"
End If

End Sub
Private Sub txtReaderID_KeyPress(KeyAscii As Integer)
'判断用户按下回车键并且是否输入读者编号
If KeyAscii = "13" And txtReaderID.Text <> "" Then
    
    '根据输入的读者编号,查找读者姓名
    g_strSql = "select * from readerInfo where 读者编号='" & txtReaderID.Text & "'"
     Set g_rs = g_db.OpenRecordset(g_strSql) '进行数据库的查询
    
    '判断是否找到
    If Not g_rs.EOF Then
        txtReaderName.Text = g_rs!读者姓名
        InitDataGrid (False)         '初始化DataGrid控件信息
    Else
        MsgBox "没有该读者信息!", vbOKOnly, "提示"
        txtReaderName.Text = ""
    End If
    
    Set g_rs = Nothing
ElseIf KeyAscii = "13" And txtReaderID.Text = "" Then
   MsgBox "请先输入读者编号", vbOKOnly, "提示"
End If

End Sub
Public Function InitDataGrid(blnRenew As Boolean)
         '初始化DataGrid控件信息,其中得参数用来区分是正常借书页面还是续借页面
Dim strDataSource As String
Dim intCount As Integer
Dim strReaderID As String
If blnRenew = False Then
   strReaderID = txtReaderID.Text
Else
   strReaderID = txtReaderIDRenew.Text
End If

strDataSource = "select lentInfo.读者编号,readerInfo.读者姓名,lentInfo.书籍编号," _
    & " bookInfo.书籍名称,bookType.书籍类别,bookInfo.出版社,bookInfo.书籍页码,lentInfo.借书日期," _
    & " lentInfo.还书日期,lentInfo.超出天数,lentInfo.罚款金额 from readerInfo,bookInfo,lentInfo,bookType " _
    & " where readerInfo.读者编号=lentInfo.读者编号 and bookInfo.书籍编号=lentInfo.书籍编号 and " _
    & " lentInfo.读者编号='" & strReaderID & "' and bookType.类别代码=bookInfo.类别代码 and lentInfo.还书日期 is null"

Adodc1.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\图书馆查询管理系统.mdb;Persist Security Info=False"
Adodc1.CursorLocation = adUseClient
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = strDataSource
Adodc1.Refresh

If blnRenew = False Then  '如果是正常借书进行如下操作
    Set dtgrdLendBook.DataSource = Adodc1
    dtgrdLendBook.Refresh
    '显示一共借了多少书
    lblLendCount.Caption = "所借图书:" + CStr(Adodc1.Recordset.RecordCount)
    '显示还能够借多少书
    g_strSql = "select * from basicSet"
     Set g_rs = g_db.OpenRecordset(g_strSql)
    intCount = g_rs!借书册数 - Adodc1.Recordset.RecordCount
    lblRemain.Caption = CStr(intCount)
Else
    Set dtgrdLendBookRenew.DataSource = Adodc1
    dtgrdLendBookRenew.Refresh
End If

End Function
Private Sub txtReaderIDRenew_KeyPress(KeyAscii As Integer)
'判断用户按下回车键并且是否输入读者编号
If KeyAscii = "13" And txtReaderIDRenew.Text <> "" Then
    
    '根据输入的读者编号,查找读者姓名
    g_strSql = "select * from readerInfo where 读者编号='" & txtReaderIDRenew.Text & "'"
     Set g_rs = g_db.OpenRecordset(g_strSql) '进行数据库的查询
    
    '判断是否找到
    If Not g_rs.EOF Then
        txtReaderNameRenew.Text = g_rs!读者姓名
        InitDataGrid (True)         '初始化DataGrid控件信息
        cmdOK.Enabled = True
    Else
        MsgBox "没有该读者信息!", vbOKOnly, "提示"
        txtReaderNameRenew.Text = ""
        cmdOK.Enabled = False
    End If
    
    Set g_rs = Nothing
ElseIf KeyAscii = "13" And txtReaderIDRenew.Text = "" Then
   MsgBox "请先输入读者编号", vbOKOnly, "提示"
   cmdOK.Enabled = False
End If

End Sub