frmborrowreturn.frm

图书管理系统New08-05-28.rar（包含文档）

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmBorrowReturn 
   BackColor       =   &H80000013&
   Caption         =   "借书、还书"
   ClientHeight    =   7545
   ClientLeft      =   2850
   ClientTop       =   2580
   ClientWidth     =   9630
   LinkTopic       =   "Form1"
   ScaleHeight     =   7545
   ScaleWidth      =   9630
   Begin VB.Frame Frame1 
      BackColor       =   &H80000013&
      Caption         =   "执行操作"
      Height          =   1095
      Left            =   240
      TabIndex        =   5
      Top             =   1320
      Width           =   9135
      Begin VB.CommandButton clean 
         Caption         =   "清 空"
         Height          =   375
         Left            =   7560
         TabIndex        =   10
         Top             =   480
         Width           =   975
      End
      Begin VB.CommandButton cmdContinu 
         Caption         =   "续 借"
         Enabled         =   0   'False
         Height          =   375
         Left            =   5880
         TabIndex        =   9
         Top             =   480
         Width           =   1095
      End
      Begin VB.CommandButton cmdReturn 
         Caption         =   "还 书"
         Enabled         =   0   'False
         Height          =   375
         Left            =   4200
         TabIndex        =   8
         Top             =   480
         Width           =   1095
      End
      Begin VB.CommandButton cmdBorrow 
         Caption         =   "借  书"
         Height          =   375
         Left            =   2400
         TabIndex        =   7
         Top             =   480
         Width           =   1095
      End
      Begin VB.CommandButton cmdFind 
         Caption         =   "查  询"
         Height          =   375
         Left            =   600
         TabIndex        =   6
         Top             =   480
         Width           =   1095
      End
   End
   Begin VB.TextBox tsbh 
      Height          =   375
      Left            =   5640
      TabIndex        =   3
      Top             =   480
      Width           =   2535
   End
   Begin VB.TextBox dzbh 
      Height          =   375
      Left            =   1680
      TabIndex        =   1
      Top             =   480
      Width           =   2415
   End
   Begin MSFlexGridLib.MSFlexGrid borrowbookGrid 
      Height          =   4095
      Left            =   240
      TabIndex        =   4
      Top             =   3120
      Width           =   9135
      _ExtentX        =   16113
      _ExtentY        =   7223
      _Version        =   393216
      Rows            =   1
      Cols            =   8
      FixedCols       =   0
      BackColor       =   -2147483629
      BackColorBkg    =   14737632
      GridColor       =   14737632
      GridColorFixed  =   14737632
      SelectionMode   =   1
      AllowUserResizing=   1
   End
   Begin VB.Label cqsl 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      ForeColor       =   &H000000FF&
      Height          =   180
      Left            =   1680
      TabIndex        =   16
      Top             =   2760
      Width           =   90
   End
   Begin VB.Label Label3 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      Caption         =   "超期图书数量："
      Height          =   180
      Left            =   360
      TabIndex        =   15
      Top             =   2760
      Width           =   1260
   End
   Begin VB.Label kjsl 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      ForeColor       =   &H000000FF&
      Height          =   180
      Left            =   7200
      TabIndex        =   14
      Top             =   2760
      Width           =   90
   End
   Begin VB.Label Label6 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      Caption         =   "可借图书数量："
      Height          =   180
      Left            =   5880
      TabIndex        =   13
      Top             =   2760
      Width           =   1260
   End
   Begin VB.Label yjsl 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      ForeColor       =   &H000000FF&
      Height          =   180
      Left            =   4320
      TabIndex        =   12
      Top             =   2760
      Width           =   90
   End
   Begin VB.Label Label4 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      Caption         =   "已借图书数量："
      Height          =   180
      Left            =   3000
      TabIndex        =   11
      Top             =   2760
      Width           =   1260
   End
   Begin VB.Label Label2 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      Caption         =   "图书编号"
      Height          =   180
      Left            =   4800
      TabIndex        =   2
      Top             =   600
      Width           =   720
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      BackColor       =   &H80000013&
      Caption         =   "借阅证号"
      Height          =   180
      Left            =   840
      TabIndex        =   0
      Top             =   600
      Width           =   720
   End
End
Attribute VB_Name = "frmBorrowReturn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'****************************************************************************
'       作者：林永刚
'       名称：frmBorrowReturn
'       功能：借书、还书、续借、查询借阅情况。
'****************************************************************************

'清 空
Private Sub clean_Click()
With borrowbookGrid
    .Clear
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "读者编号"
    .TextMatrix(0, 4) = "读者名字"
    .TextMatrix(0, 5) = "借书日期"
    .TextMatrix(0, 6) = "应还日期"
    .TextMatrix(0, 7) = "是否超期"
End With
End Sub

'借书
'**********************************
'借书条件：
'         1、该读者存在
'         2、该图书存在
'         3、该读者不能有超期的图书
'***********************************
Private Sub cmdBorrow_Click()
 Call clean_Click
 
 Dim rs2 As New ADODB.Recordset
 Dim sql2 As String
 Dim rs3 As New ADODB.Recordset
 Dim sql3 As String
 Dim rs4 As New ADODB.Recordset
 Dim sql5 As String
 Dim rs5 As New ADODB.Recordset
 Dim sql6 As String
 Dim rs6 As New ADODB.Recordset
 
 Dim sql4 As String
sql2 = "select * from readers where dzbh='" & Trim(dzbh.Text) & "'" '读者信息表
Set rs2 = ADOSQL(sql2)
sql3 = "select * from books where tsbh='" & Trim(tsbh.Text) & "'" '图书信息表
Set rs3 = ADOSQL(sql3)
sql4 = "select * from borrow_book where tsbh='" & Trim(dzbh.Text) & "'" '借书信息表
Set rs4 = ADOSQL(sql4)
sql6 = "select * from reader_category where dzlb='" & rs2.Fields("dzlb") & "'"  '读者类别
Set rs6 = ADOSQL(sql6)


'条件一
If dzbh.Text = "" Then
    MsgBox "请输入借书证号"
    Exit Sub
    dzbh.SetFocus
ElseIf rs2.EOF Then
    MsgBox "该借书证号不存在，或已经被注销"
    dzbh.Text = ""
    dzbh.SetFocus
    Exit Sub
'条件二
ElseIf tsbh.Text = "" Then
    MsgBox "请输入要图书编号"
    tsbh.SetFocus
    Exit Sub
ElseIf rs3.EOF Then
    MsgBox "该图书不存在，或不可借阅"
    tsbh.Text = ""
    tsbh.SetFocus
    Exit Sub
End If
'条件三
If Not rs4.EOF Then
     While Not rs4.EOF
     If rs4("yhrq") < Date Then
        MsgBox "该读者有超期图书未还，不能借阅！"
        Exit Sub
     End If
      rs4.MoveNext
     Wend
     rs4.Close
End If

'添加借书信息
    sql5 = "select * from borrow_book " '借书信息表
    Set rs5 = ADOSQL(sql5)
    rs5.AddNew                '添加新纪录
    rs5.Fields(1) = rs3.Fields("tsbh")
    rs5.Fields(2) = rs3.Fields("tsmc")
    rs5.Fields(3) = rs2.Fields("dzbh")
    rs5.Fields(4) = rs2.Fields("dzxm")
    rs5.Fields(5) = Date
    rs5.Fields(6) = Date + rs6.Fields("jsts")
    rs5.Fields(7) = rs3.Fields("tsjg")
    rs5.Fields(9) = user
    rs5.Update
    MsgBox "借书成功"
    rs2.Close
    rs3.Close
    rs5.Close
    rs6.Close
    Call cmdFind_Click
End Sub

'续 借
Private Sub cmdContinu_Click()

Dim rs11 As New ADODB.Recordset  '查找借书信息表
Dim sql11 As String
Dim rs12 As New ADODB.Recordset  '读者信息表
Dim sql12 As String
Dim rs13 As New ADODB.Recordset  '读者类别表
Dim sql13 As String

tsbh.Text = borrowbookGrid.TextMatrix(borrowbookGrid.Row, 0)
dzbh.Text = borrowbookGrid.TextMatrix(borrowbookGrid.Row, 3)

sql11 = "select xjcs from borrow_book where tsbh='" & tsbh.Text & "' and dzbh='" & dzbh.Text & "'"
Set rs11 = ADOSQL(sql11)
sql12 = "select dzlb from readers where dzbh='" & tsbh.Text & "'"
Set rs12 = ADOSQL(sql12)
sql13 = "select xjcs from reader_category where dzlb='" & rs12.Fields("dzlb") & "'"
Set rs13 = ADOSQL(sql13)
If rs11.Fields("xjcs") = rs13.Fields("xjcs") Then
  MsgBox "该书续借次数已经满额，不能再续借！"
  Exit Sub
Else
  rs11.Fields("xjcs") = rs11.Fields("xjcs") + 1
  rs11.Update
End If
End Sub

'查  询
Private Sub cmdFind_Click()
    Call clean_Click '先清空borrowbookGrid
    cmdReturn.Enabled = True
    cmdContinu.Enabled = True
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim rs1 As New ADODB.Recordset
    Dim sql1 As String
    
    sql1 = "select * from readers where dzbh='" & Trim(dzbh.Text) & "'" 'readers读者信息表
    Set rs1 = ADOSQL(sql1)
    If dzbh.Text = "" Then
        MsgBox "请输入借阅证号，再查询！"
        dzbh.Text = ""
        dzbh.SetFocus
        Exit Sub
    End If
    If rs1.EOF Then
      MsgBox "您输入的读者不存在，或者已经被注销！"
      rs1.Close
      dzbh.Text = ""
      dzbh.SetFocus
      Exit Sub
    End If
    
   sql = "select * from borrow_book where dzbh='" & Trim(dzbh.Text) & "'" 'borrow_book图书借阅表
  
   '将图书信息表所有记录显示borrowbookGrid表中
   Dim i As Integer
   Dim j As Integer
   j = 0
   i = 0
   Set rs = ADOSQL(sql)
   If Not rs.EOF Then
      With borrowbookGrid
        .Rows = 1
        While Not rs.EOF
        i = i + 1
        .Rows = .Rows + 1
        .TextMatrix(.Rows - 1, 0) = rs(1) '图书编号
        .TextMatrix(.Rows - 1, 1) = rs(2) '图书名称
        .TextMatrix(.Rows - 1, 2) = rs(7) '图书价格
        .TextMatrix(.Rows - 1, 3) = rs(3) '读者编号
        .TextMatrix(.Rows - 1, 4) = rs(4) '读者名字
        .TextMatrix(.Rows - 1, 5) = rs(5) '借书日期
        .TextMatrix(.Rows - 1, 6) = rs(6) '应还日期
        If rs(6) < Date Then
        .TextMatrix(.Rows - 1, 7) = "是" '是否超期
        j = j + 1 '累计超期图书数量
        Else
        .TextMatrix(.Rows - 1, 7) = "否" '是否超期
        End If
         rs.MoveNext
        Wend
      End With
    End If
    c = i '已借图书数量
    rs.Close
    cqsl.Caption = j  '累计超期图书数量
    yjsl.Caption = c  '已借图书数量
    sql = "select * from reader_category where dzlb='" & rs1.Fields("dzlb") & "'"  '读者类别
    Set rs = ADOSQL(sql)
        kjsl.Caption = rs.Fields("jssl") - c  '可借数量-已借数量
    borrowbookGrid.Refresh
End Sub

'还书
Private Sub cmdReturn_Click()
Dim rs8 As New ADODB.Recordset  '查找还书信息
Dim sql8 As String
Dim rs2 As New ADODB.Recordset  '删除借书信息（还书）
Dim sql2 As String
Dim rs9 As New ADODB.Recordset '添加到罚款信息表中
Dim sql9 As String
Dim rs10 As New ADODB.Recordset

tsbh.Text = borrowbookGrid.TextMatrix(borrowbookGrid.Row, 0)
dzbh.Text = borrowbookGrid.TextMatrix(borrowbookGrid.Row, 3)

sql8 = "select * from borrow_book where tsbh='" & tsbh.Text & "' and dzbh='" & dzbh.Text & "'"
sql2 = "delete from borrow_book where tsbh='" & tsbh.Text & "' and dzbh='" & dzbh.Text & "'"
sql9 = "select * from fines"
Set rs8 = ADOSQL(sql8)

If rs8.Fields("yhrq") < Date Then
  YesOrNo = MsgBox("该书已经超期，是否付费还书？", YesNo)
    '删除借书信息（还书）
    Set rs2 = ADOSQL(sql2)
    
    '添加到fines信息表中
    Set rs9 = ADOSQL(sql9)
    rs9.AddNew
        rs9.Fields("tsbh") = tsbh.Text
        rs9.Fields("tsmc") = rs8.Fields("tsmc")
        rs9.Fields("dzbh") = rs8.Fields("dzbh")
        rs9.Fields("dzxm") = rs8.Fields("dzxm")
        rs9.Fields("tsjg") = rs8.Fields("tsjg")
        rs9.Fields("yfje") = (Date - rs8.Fields("yhrq")) * 0.1  '计算罚款金额：天数*（每日罚款额0.1）
        rs9.Fields("fkrq") = Date
        rs9.Fields("czy") = user
    rs9.Update
    MsgBox "还书成功！"
    Call cmdFind_Click
    Exit Sub
Else
 Set rs10 = ADOSQL(sql2)
 MsgBox "还书成功！"
 Call cmdFind_Click
 Exit Sub
End If
'rs2.Close
rs8.Close
rs9.Close
rs10.Close
End Sub

'加 载
Private Sub Form_Load()
With borrowbookGrid
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "读者编号"
    .TextMatrix(0, 4) = "读者名字"
    .TextMatrix(0, 5) = "借书日期"
    .TextMatrix(0, 6) = "应还日期"
    .TextMatrix(0, 7) = "是否超期"
    .ColAlignment(0) = 1
    .ColWidth(0) = 800
    .ColWidth(1) = 2800
    .ColWidth(2) = 800
    .ColWidth(3) = 800
    .ColWidth(4) = 1000
    .ColWidth(5) = 1000
    .ColWidth(6) = 1000
    .ColWidth(7) = 1000
  End With
End Sub