frmborrow_read.frm

图书简明管理系统源代码(VB),可作为软件工程课程设计用,也可以作为学习用。

'清空表框
Public Function clean()
   borrowbookGrid.Rows = 1
   txt读者编号.Text = ""
   txt图书编号.Text = ""
   lst读者.Rows = 0
   lst图书.Rows = 0
   txttotalprice.Text = 0
   txttotalsum.Text = 0
End Function



Private Sub cmdadd_Click()

  Dim sql As String
  Dim rs As New ADODB.Recordset

  If txt图书编号.Text = "" Then
        MsgBox "没有添加图书信息,请先选择图书", vbOKOnly
        Exit Sub
  End If
  
  '当前读者可借书数量
  Dim kjynum As Integer
  sql = "select 可借书数量 from dzlbb where 读者类别= '" & Trim(lst读者.TextMatrix(3, 1)) & "'" '不显示已借全书的读者
  kjynum = TransactSQL(sql).Fields(0)
   
  Dim totalsum As Single  '合计金额
  totalsum = Val(lst图书.TextMatrix(2, 1)) * Val(txt借阅数量.Text) '合计金额
  
  Dim totalnum As Single
  totalnum = Val(txttotalsum.Text) + Val(lst读者.TextMatrix(3, 1)) '存放每条添加前borrowbookgrid表中的书数量和已借书的数量和
  
  
  Dim number, i As Integer
  Dim flagfound As Boolean
  flagfound = False
  number = Val(txt借阅数量.Text) '借书，归还，续借 的书的数量
  
  i = 1
  
  
  
'借书 ----把每次一种书的数据添加到borrowbookGrid表中
If opt借书.Value Then
    
    '查是否有相同记录
   With borrowbookGrid
     For i = 1 To .Rows - 1
        If .TextMatrix(i, 0) = txt图书编号.Text Then
            flagfound = True
             number = Val(.TextMatrix(i, 3)) + Val(txt借阅数量.Text)
             If number > Val(lst图书.TextMatrix(3, 1)) Then
                  MsgBox "所选的借阅数量大于现存量，请核对数量再添加。", vbOKOnly + vbCritical
                  Exit Sub
             Else
                 .TextMatrix(i, 3) = number
             End If
             Exit For
        End If
     Next i
  End With
  
    If totalnum + number > kjynum Then
        MsgBox Trim(lst读者.TextMatrix(0, 1)) & "已借了" & Val(lst读者.TextMatrix(4, 1)) & " 本,只能再借" & _
                 Val(rs(0) - Val(lst读者.TextMatrix(4, 1))) & "本书，现已超出可借阅数量数量，今次添加无效， 请重新设借阅数量.", vbOKOnly
        With borrowbookGrid
          If flagfound = True Then
               number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
              .TextMatrix(i, 3) = number
          End If
        End With
    End If
          
    
     If flagfound = False And totalnum + number <= kjynum Then
        borrowbookGrid.AddItem txt图书编号.Text & vbTab & lst图书.TextMatrix(0, 1) & vbTab & _
             lst图书.TextMatrix(2, 1) & vbTab & Val(txt借阅数量.Text) & vbTab & totalsum & vbTab & txt借阅天数.Text
     End If

End If

'归还 ----把每次一种书的数据添加到borrowbookGrid表中
If opt归还.Value Then
  
  '提示交罚款
  sql = "select * from jsxxb where 图书编号='" & txt图书编号.Text & " ' and 读者编号='" & txt读者编号.Text & _
        "' and DateDiff('d', 应还日期,'" & Trim(Format$(Now, "yyyy-mm-dd")) & "') > 0"
  Set rs = TransactSQL(sql)

  If Not rs.EOF Then
      MsgBox "该书已超过应归还日期", vbOKOnly
  End If
  
    '查是否有相同记录
   With borrowbookGrid
     For i = 1 To .Rows - 1
        If .TextMatrix(i, 0) = txt图书编号.Text And DateDiff("d", lst图书.TextMatrix(5, 1), .TextMatrix(i, 6)) = 0 Then
            flagfound = True
             number = Val(.TextMatrix(i, 3)) + Val(txt借阅数量.Text)
             If number > Val(lst图书.TextMatrix(3, 1)) Then
                  MsgBox "所选的续借数量大于现存量，请核对数量再添加。", vbOKOnly + vbCritical
                  Exit Sub
             Else
                 .TextMatrix(i, 3) = number
             End If
             Exit For
        End If
     Next i
  End With
'  '提示设置还书数量
'  sql = "select * from jsxxb where 图书编号='" & txt图书编号.Text & " ' and 读者编号='" & txt读者编号.Text & "'"
'  Set rs = TransactSQL(sql)
'
'  If rs(4) < number Then
'     MsgBox "还书数量超过借书数量，请重新设置。", vbOKOnly
'        With borrowbookGrid
'          If .Rows < 2 Then
'              .Rows = 1
'          Else
'              number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
'              .TextMatrix(i, 3) = number
'          End If
'        End With
'     Exit Sub
'  End If
  
  If flagfound = False Then
    borrowbookGrid.AddItem txt图书编号.Text & vbTab & lst图书.TextMatrix(0, 1) & vbTab & lst图书.TextMatrix(2, 1) _
                          & vbTab & Val(txt借阅数量.Text) & vbTab & totalsum & vbTab & txt借阅天数.Text & vbTab & _
                          lst图书.TextMatrix(5, 1)
  End If
End If
'
''续借----把每次一种书的数据添加到borrowbookGrid表中
If opt续借.Value Then
  Dim sql2 As String, sql3 As String
  Dim rs2 As New ADODB.Recordset, rs3 As New ADODB.Recordset
  
  
  sql = "select * from jsxxb where 图书编号='" & txt图书编号.Text & " ' and 读者编号='" & txt读者编号.Text & _
       "' and DateDiff('d', 应还日期,'" & Trim(Format$(Now, "yyyy-mm-dd")) & "') > 0"
  'Set rs = TransactSQL(sql)
  If Not TransactSQL(sql).EOF Then  '当前日期大于应还日期
  
        MsgBox "已超过应还日期，不能续借该书，请归还该书", vbOKOnly
'        With borrowbookGrid
'          If .Rows < 2 Then
'              .Rows = 1
'          Else
'              number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
'              .TextMatrix(i, 3) = number
'          End If
'        End With
        Exit Sub
  End If
  'rs(7) = rs(7) + 1
  
    '查是否有相同记录
   With borrowbookGrid
     For i = 1 To .Rows - 1
        If .TextMatrix(i, 0) = txt图书编号.Text And DateDiff("d", lst图书.TextMatrix(5, 1), .TextMatrix(i, 6)) = 0 Then
            flagfound = True
             number = Val(.TextMatrix(i, 3)) + Val(txt借阅数量.Text)
             If number > Val(lst图书.TextMatrix(3, 1)) Then
                  MsgBox "所选的续借数量大于现存量，请核对数量再添加。", vbOKOnly + vbCritical
                  Exit Sub
             Else
                 .TextMatrix(i, 3) = number
             End If
             Exit For
        End If
     Next i
  End With
  
'  '续借数量> 当前图书的借阅数量
'  sql3 = "select * from jsxxb where  读者编号='" & txt读者编号.Text & "'"
'  Set rs3 = TransactSQL(sql3)
'
'  With borrowbookGrid
'    If .Rows < 2 Then
'        If Val(txt借阅数量.Text) > rs3(4) Then
'               MsgBox "该书的续借数量不能大于已借阅数量,不能续借", vbOKOnly
'               number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
'              .TextMatrix(i, 3) = number
'               Exit Sub
'        End If
'    Else
'        If .TextMatrix(borrowbookGrid.Row, 3) > rs3(4) Then
'               MsgBox "该书的续借数量不能大于已借阅数量,不能续借", vbOKOnly
'               number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
'              .TextMatrix(i, 3) = number
'               Exit Sub
'        End If
'    End If
'  End With
  
  '可续借次数
  sql3 = "select 续借次数 from jsxxb where 图书编号='" & txt图书编号.Text & " ' and 读者编号='" & txt读者编号.Text & _
        "' and DateDiff('d', 借阅日期,'" & lst图书.TextMatrix(5, 1) & "') = 0"
  Set rs3 = TransactSQL(sql3)
  sql2 = "select 可续借次数 from dzlbb where 读者类别= '" & Trim(lst读者.TextMatrix(3, 1)) & "'"
  Set rs2 = TransactSQL(sql2)
  
  If rs3(0) + 1 > rs2(0) Then    '续借次数+1 > 可续借次数
     MsgBox "不能续者该书。", vbOKOnly
'     With borrowbookGrid
'          If .Rows < 2 Then
'              .Rows = 1
'          Else
'              number = Val(.TextMatrix(i, 3)) - Val(txt借阅数量.Text)
'              .TextMatrix(i, 3) = number
'          End If
'        End With
      Exit Sub
  End If
  
  If flagfound = False Then
    borrowbookGrid.AddItem txt图书编号.Text & vbTab & lst图书.TextMatrix(0, 1) & vbTab & lst图书.TextMatrix(2, 1) _
                          & vbTab & Val(txt借阅数量.Text) & vbTab & totalsum & vbTab & txt借阅天数.Text & vbTab & _
                          lst图书.TextMatrix(5, 1)
  End If
  
End If


Call counter_pricesum  '计算总数
cmddel.Enabled = True
cmdsave.Enabled = True
cmdclear.Enabled = True
cmdadd.Enabled = False
End Sub





Private Sub cmdclear_Click()
     borrowbookGrid.Rows = 1
     cmdadd.Enabled = False
     cmddel.Enabled = False
     cmdsave.Enabled = False
     cmdclear.Enabled = False
End Sub

Private Sub cmddel_Click()

'    If borrowbookGrid.Rows = 2 Then
'        cmdadd.Enabled = True
'        cmdclear.Enabled = False
'        cmdsave.Enabled = False
'    Else
'        cmdadd.Enabled = True
'        cmdclear.Enabled = True
'        cmdsave.Enabled = True
'    End If
'
'    If borrowbookGrid.Rows = 2 Then
'       borrowbookGrid.Rows = 1
'       cmddel.Enabled = False
'    Else
'       borrowbookGrid.RemoveItem (borrowbookGrid.Row)
'       Call counter_pricesum
'    End If
'
'
'    End If
'cmdadd.Enabled = False
'cmdsave.Enabled = False

       If borrowbookGrid.Rows = 2 Then
            borrowbookGrid.Rows = 1
            cmddel.Enabled = False
            cmdadd.Enabled = True
            cmdclear.Enabled = False
            cmdsave.Enabled = False
        Else
             borrowbookGrid.RemoveItem (borrowbookGrid.Row)
             Call counter_pricesum
            cmdadd.Enabled = False
            cmddel.Enabled = True
            cmdclear.Enabled = True
            cmdsave.Enabled = True
        End If
End Sub

Private Sub cmdexit_Click()
Unload Me
End Sub

Private Sub cmdsave_Click() '执行操作按扭

Dim sql As String, sql2 As String, sql3 As String
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset, rs3 As New ADODB.Recordset
Dim flag As Integer
flag = 0

If borrowbookGrid.Rows < 2 Then
    MsgBox "没有信息,不能执行操作,请先选择图书.", vbOKOnly
Else

'借书----按borrowbookGrid表中的数据执行数据库中对应表的操作
If opt借书.Value Then
'    sql = "select 读者类别,已借书数 from dzxxb where 读者编号='" & Trim(txt读者编号.Text) & "'"
'    Set rs = TransactSQL(sql)
'    sql2 = "select  可借书数量 from dzlbb where 读者类别='" & rs(0) & "'"
'    Set rs2 = TransactSQL(sql2)
'
' If rs(1) > rs2(0) Then
'    MsgBox "借书数量超过最大数量", vbOKOnly
'    Exit Sub
' Else
'    sql = "select 应还日期 from jsxxb where 读者编号='" & txt读者编号.Text & "'"
'    Set rs = TransactSQL(sql)
'
'    Do While Not rs.EOF
'       If Format$(Now, "yyyy-mm-dd") > rs(0) Then
'           MsgBox "请先还该书后再借书", vbOKOnly
'           Exit Do
'           Exit Sub
'       End If
'
'       rs.MoveNext
'    Loop
'
'
'    sql2 = "select 是否交款 from hsxxb where 读者编号='" & Trim(txt读者编号.Text) & "'"
'    Set rs2 = TransactSQL(sql2)
'    If rs2(0) = "是" Then
'        MsgBox "请先交罚款后再借书", vbOKOnly
'        Exit Sub
'    End If
'    rs.Close
'    rs2.Close
    sql = "select * from jsxxb where 读者编号='" & Trim(txt读者编号.Text) & "' and  DateDiff('d', 应还日期,'" & Trim(Format$(Now, "yyyy-mm-dd")) & "') >0 "
    Set rs = TransactSQL(sql)
    If Not rs.EOF Then
        MsgBox "请先还到期的书后再借书", vbOKOnly + vbInformation
        Exit Sub
    End If
    
    sql = "select * from hsxxb where 读者编号='" & Trim(txt读者编号.Text) & "' and  是否交款='是' "
    Set rs = TransactSQL(sql)
    If Not rs.EOF Then
        MsgBox "请先交罚款后再借书", vbOKOnly + vbInformation
        Exit Sub
    End If
    
    
    With borrowbookGrid
     For i = 1 To .Rows - 1
        sql = "select * from jsxxb where 图书编号='" & Trim(.TextMatrix(i, 0)) & "' and 读者编号='" & _
             Trim(txt读者编号.Text) & "'and  DateDiff('d', 借阅日期,'" & Trim(Format$(Now, "yyyy-mm-dd")) & "') = 0"
        Set rs = TransactSQL(sql)

       If Not rs.EOF Then
            '更新jsxxb同一记录
            rs(4) = rs(4) + Val(borrowbookGrid.TextMatrix(i, 3))
            rs.Update
       Else
            'jsxxb插入新记录
            dateback = CDate(Format$(Now, "yyyy-mm-dd")) + Val(txt借阅天数.Text)
            sql = "insert into jsxxb values('" & .TextMatrix(i, 0) & "','" & .TextMatrix(i, 1) & "','" & txt读者编号.Text & "','" & _
                  lst读者.TextMatrix(0, 1) & "'," & Val(borrowbookGrid.TextMatrix(i, 3)) & ",'" & Format$(Now, "yyyy-mm-dd") & "','" & _
                  dateback & "'," & 0 & ",'" & user & "')"
            TransactSQL (sql)
       End If
       
       
       
       '修改读者信息表中已借书数
       sql = "update dzxxb set 已借书数=已借书数 + " & Val(borrowbookGrid.TextMatrix(i, 3)) & " where 读者编号='" & txt读者编号.Text & "'"
        TransactSQL (sql)
       
       '修改图书信息表中借阅次数,现存量
       sql = "update tsxxb set 借阅次数=借阅次数 +" & Val(borrowbookGrid.TextMatrix(i, 3)) & ",现存量=现存量 - " & Val(borrowbookGrid.TextMatrix(i, 3)) _
             & " where 图书编号= '" & .TextMatrix(i, 0) & "'"
        TransactSQL (sql)
    Next i
    End With
    
    
    MsgBox "借阅成功！", vbOKOnly

'  End If
    
End If



'归还----按borrowbookGrid表中的数据执行数据库中对应表的操作

If opt归还.Value Then

    Dim sql4 As String
    Dim rs4 As New ADODB.Recordset
    Dim overdate As Variant     '过期天数
    Dim finemoney As Single    '超期罚金
    Dim fineflage As String    '罚款标志

    '罚款
     sql3 = "select 逾期后缓冲天数,逾期后每天罚款金额 from dzlbb where 读者类别='" & Trim(lst读者.TextMatrix(3, 1)) & "'"
     Set rs3 = TransactSQL(sql3)
     
    For i = 1 To borrowbookGrid.Rows - 1

        sql2 = "select 应还日期 from jsxxb where 图书编号='" & borrowbookGrid.TextMatrix(i, 0) & "' and   读者编号='" & txt读者编号.Text & _
               "'and DateDiff('d', 借阅日期, '" & borrowbookGrid.TextMatrix(i, 6) & "')=0"
        Set rs2 = TransactSQL(sql2)
       
        '过期天数
        If rs2.EOF Then
        ElseIf DateDiff("d", rs2(0), Format$(Now, "yyyy-mm-dd")) > 0 Then        '应还日期 > 实还日期
            
            overdate = DateDiff("d", rs2(0), Format$(Now, "yyyy-mm-dd"))     ' rs(7) - rs(6)  实还日期 - 应还日期
          
        Else
            overdate = 0
'
        End If
        '超期罚金
    
            
        If overdate > 0 And overdate >= rs3(0) Then        'rs3 过期天数 > 0 & 过期天数 > 逾期后缓冲天数
            finemoney = overdate * rs3(1)                'rs3(1)->逾期后每天罚款金额
            fineflage = "是"
            MsgBox Trim(borrowbookGrid.TextMatrix(i, 1)) & "这本书过期归还，过期天数为 " & overdate & " ,需交罚款 " & finemoney & " 元", vbOKOnly
        Else
            finemoney = 0
            fineflage = "否"
        End If
        
        '查询hsxxb是否有相同记录
        sql = "select * from hsxxb where 图书编号= '" & borrowbookGrid.TextMatrix(i, 0) & "' and 读者编号='" & _