frmborrow_read.frm

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

              Trim(txt读者编号.Text) & "' and  DateDiff( 'd', 借阅日期, '" & borrowbookGrid.TextMatrix(i, 6) & "')=0 and DateDiff('d', 实还日期,'" _
              & Format$(Now, "yyyy-mm-dd") & "') = 0"
        Set rs = TransactSQL(sql)
       
        
       If Not rs.EOF Then
          '更新hsxxb记录
            If rs(8) > 0 And rs(8) >= rs3(0) Then   '需要罚款
                 rs(9) = rs(9) + rs3(1)        '每天加罚款
                 rs(10) = "是"
            Else
                 rs(4) = rs(4) + Val(borrowbookGrid.TextMatrix(i, 3))
                 rs(10) = "否"
            End If
            rs.Update
        Else
           'hsxxb插入新记录
           sql = "insert into hsxxb values('" & Trim(borrowbookGrid.TextMatrix(i, 0)) & "','" & Trim(borrowbookGrid.TextMatrix(i, 1)) _
                 & "','" & Trim(txt读者编号.Text) & "','" & Trim(lst读者.TextMatrix(0, 1)) & "'," & Val(borrowbookGrid.TextMatrix(i, 3)) & ",'" & Trim(lst图书.TextMatrix(5, 1)) _
                 & "','" & rs2(0) & "','" & Format$(Now, "yyyy-mm-dd") & "'," & overdate & "," & finemoney & ",'" & fineflage & "','" & user & "')"
           TransactSQL (sql)
        End If
        
        '更新jsxxb中的借书数量级
        sql4 = "select 借阅数量 from jsxxb where 图书编号='" & Trim(borrowbookGrid.TextMatrix(i, 0)) & "' and 读者编号='" & Trim(txt读者编号.Text) & _
               "' and DateDiff('d','" & Trim(borrowbookGrid.TextMatrix(i, 6)) & "',借阅日期 ) = 0"

        Set rs4 = TransactSQL(sql4)

        If rs4(0) = Val(borrowbookGrid.TextMatrix(i, 3)) Then        '归还该书的所借的数量,从jsxxb删除记录 借书数=还书数
            rs4.Delete
        Else                           '减去归还数量
            rs4(0) = rs4(0) - Val(borrowbookGrid.TextMatrix(i, 3))
            rs4.Update
        End If
        
        
        '更新dzxxb中的已借书数
        sql2 = "update dzxxb set 已借书数=已借书数-" & Val(borrowbookGrid.TextMatrix(i, 3)) & " where  读者编号='" & Trim(txt读者编号.Text) & "'"
        TransactSQL (sql2)
    
         '更新tsxxb的现存量
         sql2 = "update tsxxb set 现存量=现存量+" & Val(borrowbookGrid.TextMatrix(i, 3)) & " where 图书编号='" & Trim(txt图书编号.Text) & "'"
         TransactSQL (sql2)
    Next i
    
    MsgBox "归还成功！", vbOKOnly
    
    rs.Close
    rs2.Close
    rs3.Close
    
End If



'续借----按borrowbookGrid表中的数据执行数据库中对应表的操作
If opt续借.Value Then
   
   With borrowbookGrid
   For i = 1 To .Rows - 1
   
       sql3 = "select * from jsxxb where 图书编号='" & txt图书编号.Text & " ' and 读者编号='" & txt读者编号.Text & _
            "' And DateDiff('d',  借阅日期 ,'" & .TextMatrix(i, 6) & "') = 0 "
       Set rs3 = TransactSQL(sql3)
   
        If DateDiff("d", rs3(5), Format$(Now, "yyyy-mm-dd")) = 0 Then  '续借当天的书，不进行操作
            MsgBox "借阅日期与续借日期同一天，不能进行续借。", vbExclamation + vbOKOnly
        Else
            If rs3(4) = Val(.TextMatrix(i, 3)) Then     '原借阅数量等于现借阅数量
                rs3(7) = rs3(7) + 1                         'jsxxb中续借次数+1
                rs3(5) = Format$(Now, "yyyy-mm-dd")         '更新jsxxb 借阅日期
                rs3(6) = rs3(5) + Val(txt借阅天数.Text)     '更新jsxxb应还日期
                rs3.Update
            Else                                                '原借阅数量大于现借阅数量
                
                rs3(4) = rs3(4) - Val(borrowbookGrid.TextMatrix(i, 3))
                rs3.Update
       
                '查询是否有超出原借阅数且相同的记录
                sql = "select * from jsxxb where 图书编号='" & Trim(txt图书编号.Text) & "' and 读者编号='" & Trim(txt读者编号.Text) & _
                     "' And DateDiff('d', 借阅日期 ,'" & Format$(Now, "yyyy-mm-dd") & "') = 0 "
                    
                Set rs = TransactSQL(sql)
                
                If Not rs.EOF Then  '借阅数量>续借数量
                     rs(4) = rs(4) + Val(.TextMatrix(i, 3))  '更新借阅数量
                     rs.Update
                Else
                     
                      '插入新记录
                     sql = "insert into jsxxb values('" & Trim(borrowbookGrid.TextMatrix(i, 0)) & "','" & Trim(borrowbookGrid.TextMatrix(i, 1)) & _
                           "','" & Trim(txt读者编号.Text) & "','" & Trim(lst读者.TextMatrix(0, 1)) & "'," & Val(borrowbookGrid.TextMatrix(i, 3) & ",'" & _
                            Format(Trim(borrowbookGrid.TextMatrix(i, 6)), "yyyy-mm-dd") & _
                           "', " & borrowbookGrid.TextMatrix(i, 6)) + Val(txt借阅天数.Text) & "," & Val(rs3(7) + 1) & ",'" & user & "')"
                        
                    TransactSQL (sql)
                 
                   
                End If
                rs.Close
             End If
             MsgBox "续借成功！", vbOKOnly
        End If
   Next i
   End With

     rs3.Close
End If

End If
Call clean     '清空表框
cmdadd.Enabled = False
cmddel.Enabled = False
cmdsave.Enabled = False
cmdclear.Enabled = False
cmd图书选择.Enabled = False
End Sub






Private Sub cmd读者选择_Click()

Dim sql As String
Dim rs As New ADODB.Recordset
'借书---读者信息表为空不能进入frmreader_choose
If frmborrow_read.opt借书.Value Then
   sql = "select * from dzxxb "
End If

'归还/续借----借书信息表为空时不能进入frmreader_choose
If frmborrow_read.opt归还.Value Or frmborrow_read.opt续借.Value Then
   sql = "select * from jsxxb  "
End If

Set rs = TransactSQL(sql)

If rs.EOF Then
      MsgBox "没有读者记录可选。", vbOKOnly
Else
    frmreader_choose.Show 1
End If
rs.Close

End Sub

Private Sub cmd图书选择_Click()
Dim sql As String
Dim rs As New ADODB.Recordset
'借书---读者信息表为空不能进入frmreader_choose
If frmborrow_read.opt借书.Value Then
   sql = "select * from dzxxb "
End If

'归还/续借----借书信息表为空时不能进入frmreader_choose
If frmborrow_read.opt归还.Value Or frmborrow_read.opt续借.Value Then
   sql = "select * from jsxxb  "
End If

Set rs = TransactSQL(sql)
If rs.EOF Then
      MsgBox "没有读者记录可选。", vbOKOnly
Else
    frmbook_choose.Show 1
End If
rs.Close

VScroll2.Value = 1
End Sub






Private Sub Form_Load()
Call fullpic(Me, Picbg1) '背景图
Me.Left = (frmmain.ScaleWidth - Me.Width) / 2
Me.Top = (frmmain.ScaleHeight - Me.Height) / 2

txt借阅数量.Text = 1
txt图书编号.Enabled = False
txt读者编号.Enabled = False
opt借书.Value = True

 With borrowbookGrid
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "  数量"
    .TextMatrix(0, 4) = "合计金额"
    .TextMatrix(0, 5) = "  天数"
    .ColAlignment(0) = 1
    .ColWidth(0) = 1500
    .ColWidth(1) = 3100
    .ColWidth(2) = 1000
    .ColWidth(3) = 1000
    .ColWidth(4) = 1000
    .ColWidth(5) = 1000
  End With
    
'借书----窗体的初始化

  With lst图书
     .Cols = 2
     .Rows = 5
     .ColAlignment(1) = 1
     .RowHeightMin = .Height / 5
     .ColWidth(0) = 1300
     .ColWidth(1) = .Width - .ColWidth(0)
  End With
  
  With lst读者
     .Cols = 2
     .Rows = 5
     .FixedRows = 0
     .FixedCols = 0
     .ColAlignment(1) = 1
     .RowHeightMin = .Height / 5
     .ColWidth(0) = 1300
     .ColWidth(1) = .Width - .ColWidth(0)
  End With
  
  If txt读者编号.Text = "" Then
      cmd图书选择.Enabled = False
  Else
      cmd图书选择.Enabled = True
  End If
  
  cmdadd.Enabled = False
  cmddel.Enabled = False
  
  If Style = "borrow" Then
      opt借书.Value = True
      Call opt借书_Click
  ElseIf Style = "return" Then
      opt归还.Value = True
      Call opt归还_Click
  ElseIf Style = "borrowagain" Then
      opt续借.Value = True
      Call opt续借_Click
  End If

 cmdsave.Enabled = False
 cmdadd.Enabled = False
 cmddel.Enabled = False
 cmdclear.Enabled = False

End Sub









Private Sub opt借书_Click()
  
If opt借书.Value Then
  With borrowbookGrid
     .Rows = 6
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "  数量"
    .TextMatrix(0, 4) = "合计金额"
    .TextMatrix(0, 5) = "  天数"
    .ColWidth(0) = 1500
    .ColWidth(1) = 2200
    .ColWidth(2) = 1200
    .ColWidth(3) = 1200
    .ColWidth(4) = 1300
    .ColWidth(5) = 1200
  End With
  
  With lst图书
     .Rows = 5
     .ColAlignment(1) = 1
     .RowHeightMin = .Height / 5
     .ColWidth(0) = 1300
     .ColWidth(1) = .Width - .ColWidth(0)
  End With
  
   opt借书.FontBold = True
   opt续借.FontBold = False
   opt归还.FontBold = False
   
   Label3.Visible = True
   txt借阅天数.Visible = True
   Label4.Visible = True
   Label4.Caption = "借阅数量："
   txt借阅数量.Visible = True
   VScroll2.Visible = True
   
   txt读者编号.Text = ""
   txt图书编号.Text = ""
   lst读者.clear
   lst图书.clear
   borrowbookGrid.Rows = 1
   cmd图书选择.Enabled = False
End If
End Sub



Private Sub opt归还_Click()
If opt归还.Value Then
 With borrowbookGrid
    .Cols = 7
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "  数量"
    .TextMatrix(0, 4) = "合计金额"
    .TextMatrix(0, 5) = "  天数"
    .TextMatrix(0, 6) = "借阅日期"
    .ColWidth(0) = 1400
    .ColWidth(1) = 2200
    .ColWidth(2) = 1000
    .ColWidth(3) = 1000
    .ColWidth(4) = 1000
    .ColWidth(5) = 1000
    .ColWidth(6) = 1000
  End With
  
With lst图书
     .Rows = 6
     .ColAlignment(1) = 1
     .RowHeightMin = .Height / 6
     .ColWidth(0) = 1300
     .ColWidth(1) = .Width - .ColWidth(0)
 End With
     
   opt归还.FontBold = True
   opt借书.FontBold = False
   opt续借.FontBold = False
   
   Label3.Visible = False
   txt借阅天数.Visible = False
   Label4.Visible = True
   Label4.Caption = "归还数量："
   txt借阅数量.Visible = True
   VScroll2.Visible = True

   
   txt读者编号.Text = ""
   txt图书编号.Text = ""
   lst读者.clear
   lst图书.clear
   borrowbookGrid.Rows = 1
   cmd图书选择.Enabled = False

End If
End Sub


Private Sub opt续借_Click()
If opt续借.Value Then
 With borrowbookGrid
    .Cols = 7
    .TextMatrix(0, 0) = "图书编号"
    .TextMatrix(0, 1) = "图书名称"
    .TextMatrix(0, 2) = "图书价格"
    .TextMatrix(0, 3) = "  数量"
    .TextMatrix(0, 4) = "合计金额"
    .TextMatrix(0, 5) = "  天数"
    .TextMatrix(0, 6) = "借阅日期"
    .ColWidth(0) = 1400
    .ColWidth(1) = 2200
    .ColWidth(2) = 1000
    .ColWidth(3) = 1000
    .ColWidth(4) = 1000
    .ColWidth(5) = 1000
    .ColWidth(6) = 1000
  End With

  With lst图书
     .Rows = 6
     .ColAlignment(1) = 1
     .RowHeightMin = .Height / 6
     .ColWidth(0) = 1300
     .ColWidth(1) = .Width - .ColWidth(0)
  End With
  
   opt续借.FontBold = True
   opt借书.FontBold = False
   opt归还.FontBold = False

   Label3.Visible = True
   txt借阅天数.Visible = True
   Label4.Visible = True
   Label4.Caption = "续借数量："
   txt借阅数量.Visible = True
   VScroll2.Visible = True
   
   txt读者编号.Text = ""
   txt图书编号.Text = ""
   lst读者.clear
   lst图书.clear
   borrowbookGrid.Rows = 1
   cmd图书选择.Enabled = False
   
End If
End Sub

Private Sub VScroll2_Change()
'Dim sql As String, sql2 As String
'Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
'Dim booksum As Integer
'
'sql = "select 读者类别,已借书数 from dzxxb where 读者编号='" & txt读者编号.Text & "'"
'Set rs = TransactSQL(sql)
'If Not rs.EOF Then
'    sql2 = "select 可借书数量 from dzlbb where 读者类别='" & rs(0) & "'"
'    Set rs2 = TransactSQL(sql2)
'    booksum = Val(rs2(0)) - Val(rs(1))
    
    If VScroll2.Value <= 0 Then
        VScroll2.Value = 1
    Else
        If opt借书.Value Then
             If VScroll2.Value > lst图书.TextMatrix(3, 1) Then
                      VScroll2.Value = Val(lst图书.TextMatrix(3, 1))
             End If
        Else
             If VScroll2.Value > lst图书.TextMatrix(4, 1) Then
                      VScroll2.Value = Val(lst图书.TextMatrix(4, 1))
             End If
        End If
    End If
    txt借阅数量.Text = VScroll2.Value

End Sub