frmstat.frm

第一次用VB编写的图书管理系统,仅供大家参考,呵呵,不足之处多多包含

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form FrmStat 
   Caption         =   "统计信息"
   ClientHeight    =   4185
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7065
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   ScaleHeight     =   4185
   ScaleWidth      =   7065
   Begin VB.CommandButton Command6 
      Caption         =   "关闭"
      Height          =   375
      Left            =   4560
      TabIndex        =   6
      Top             =   3000
      Width           =   1455
   End
   Begin VB.CommandButton CmdPublish 
      Caption         =   "罚款统计"
      Height          =   375
      Left            =   2520
      TabIndex        =   5
      Top             =   3000
      Width           =   1455
   End
   Begin VB.CommandButton CmdHot 
      Caption         =   "最热门图书"
      Height          =   375
      Left            =   480
      TabIndex        =   4
      Top             =   3000
      Width           =   1575
   End
   Begin VB.CommandButton CmdLose 
      Caption         =   "丢失图书统计"
      Enabled         =   0   'False
      Height          =   375
      Left            =   4560
      TabIndex        =   3
      Top             =   2280
      Width           =   1335
   End
   Begin VB.CommandButton CmdCo 
      Caption         =   "超期图书统计"
      Height          =   375
      Left            =   2520
      TabIndex        =   2
      Top             =   2280
      Width           =   1455
   End
   Begin VB.CommandButton cmdB 
      Caption         =   "借书量统计"
      Height          =   375
      Left            =   480
      TabIndex        =   1
      Top             =   2280
      Width           =   1575
   End
   Begin MSFlexGridLib.MSFlexGrid flx 
      Height          =   1575
      Left            =   240
      TabIndex        =   0
      Top             =   120
      Width           =   6615
      _ExtentX        =   11668
      _ExtentY        =   2778
      _Version        =   393216
      FocusRect       =   0
      AllowUserResizing=   1
   End
End
Attribute VB_Name = "FrmStat"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub myCls()
Do Until flx.Rows <= 2
 flx.RemoveItem (flx.Rows - 1)
Loop
End Sub
Private Sub cmdB_Click()
Dim intIndex%
QueryInfo ("Select books.bookid,bookname,readers.readerid,readername,borrowdate,returndate,reborrow " & _
 "from books join (borrow join readers on readers.readerid=borrow.readerid ) on books.bookid=borrow.bookid where DATEDIFF(month,borrowDate,getdate())<1")
If DBRct.EOF Then MsgBox "这个月没有借出任何图书!", vbInformation, "结果": Exit Sub
myCls
flx.Cols = 8: flx.Row = 0
flx.Col = 0: flx.Text = "序号": flx.CellAlignment = 4
flx.Col = 1: flx.Text = "图书ID": flx.CellAlignment = 4
flx.Col = 2: flx.Text = "图书Name": flx.CellAlignment = 4
flx.Col = 3: flx.Text = "学生ID": flx.CellAlignment = 4
flx.Col = 4: flx.Text = "学生Name": flx.CellAlignment = 4
flx.Col = 5: flx.Text = "借书日期": flx.CellAlignment = 4: flx.ColWidth(5) = 2000
flx.Col = 6: flx.Text = "还书日期": flx.CellAlignment = 4: flx.ColWidth(6) = 2000
flx.Col = 7: flx.Text = "是否续借": flx.CellAlignment = 4
DBRct.MoveFirst
Do While DBRct.EOF = False
flx.AddItem (Empty): flx.Row = flx.Rows - 2
flx.Col = 0: flx.Text = flx.Rows - 2: flx.CellAlignment = 4
For intIndex = 1 To 7
 flx.Col = intIndex
 If DBRct.Fields(intIndex - 1) <> Empty Then flx.Text = DBRct.Fields(intIndex - 1): flx.CellAlignment = 4
Next
DBRct.MoveNext
Loop
End Sub

Private Sub CmdCo_Click()
QueryInfo ("Select books.bookid,bookname,readers.readerid,readername,borrowdate,returndate,reborrow,[day]=case" & _
      " when (reborrow='否' and datediff(day,borrowdate,getdate())<=30 )" & _
      "     or (reborrow='是' and datediff(day,borrowdate,getdate())<=15 ) " & _
      "   then datediff(day,borrowdate,getdate()) " & _
      " when reborrow='否'and datediff(day,borrowdate,getdate())>30" & _
      " then 30-datediff(day,borrowdate,getdate())" & _
      " else 15-datediff(day,borrowdate,getdate()) End " & _
      "from books join (borrow join readers on readers.readerid=borrow.readerid ) on books.bookid=borrow.bookid " & _
      " where DATEDIFF(month,borrowDate,getdate())<2")
If DBRct.EOF Then MsgBox "这二个月,没有任何人超期~~~", vbInformation, "结果": Exit Sub
DBRct.Find ("[day]<0")
If DBRct.EOF Then MsgBox "这二个月,没有任何人超期~~~", vbInformation, "结果": Exit Sub
myCls
flx.Cols = 9: flx.Row = 0
flx.Col = 0: flx.Text = "序号": flx.CellAlignment = 4
flx.Col = 1: flx.Text = "图书ID": flx.CellAlignment = 4
flx.Col = 2: flx.Text = "图书Name": flx.CellAlignment = 4
flx.Col = 3: flx.Text = "学生ID": flx.CellAlignment = 4
flx.Col = 4: flx.Text = "学生Name": flx.CellAlignment = 4
flx.Col = 5: flx.Text = "借书日期": flx.CellAlignment = 4: flx.ColWidth(5) = 2000
flx.Col = 6: flx.Text = "还书日期": flx.CellAlignment = 4: flx.ColWidth(6) = 2000
flx.Col = 7: flx.Text = "是否续借": flx.CellAlignment = 4
flx.Col = 8: flx.Text = "超期天数": flx.CellAlignment = 4
DBRct.MoveFirst
Do While DBRct.EOF = False
flx.AddItem (Empty): flx.Row = flx.Rows - 2
flx.Col = 0: flx.Text = flx.Rows - 2: flx.CellAlignment = 4
For intIndex = 1 To 8
 flx.Col = intIndex
 If DBRct.Fields(intIndex - 1) <> Empty Then flx.Text = DBRct.Fields(intIndex - 1): flx.CellAlignment = 4
Next
DBRct.MoveNext
Loop
End Sub

Private Sub CmdHot_Click()
QueryInfo ("Select books.bookid,bookname,[Count]=count(bookname) from " & _
           "books join borrow on books.bookid=borrow.bookid  " & _
           "Where DateDiff(Month, borrowDate, getdate()) < 1  " & _
           "Group by books.bookid,bookname  " & _
           "ORDER BY count(bookname)")
If DBRct.EOF Then MsgBox "这二个月,没有任何人借书~~~", vbInformation, "结果": Exit Sub
myCls
flx.Cols = 4: flx.Row = 0
flx.Col = 0: flx.Text = "序号": flx.CellAlignment = 4
flx.Col = 1: flx.Text = "图书ID": flx.CellAlignment = 4
flx.Col = 2: flx.Text = "图书Name": flx.CellAlignment = 4
flx.Col = 3: flx.Text = "被借次数": flx.CellAlignment = 4
DBRct.MoveFirst
Do While DBRct.EOF = False
flx.AddItem (Empty): flx.Row = flx.Rows - 2
flx.Col = 0: flx.Text = flx.Rows - 2: flx.CellAlignment = 4
For intIndex = 1 To 3
 flx.Col = intIndex
 If DBRct.Fields(intIndex - 1) <> Empty Then flx.Text = DBRct.Fields(intIndex - 1): flx.CellAlignment = 4
Next
DBRct.MoveNext
Loop
End Sub

Private Sub CmdPublish_Click()
Dim sum As Double
QueryInfo ("Select readerid,readername,publish From readers where publish is not null")
If DBRct.EOF Then MsgBox "没有任何人被罚款~~~", vbInformation, "结果": Exit Sub
flx.Cols = 4: flx.Row = 0
flx.Col = 0: flx.Text = "序号": flx.CellAlignment = 4
flx.Col = 1: flx.Text = "读者ID": flx.CellAlignment = 4
flx.Col = 2: flx.Text = "读者Name": flx.CellAlignment = 4
flx.Col = 3: flx.Text = "罚款": flx.CellAlignment = 4
DBRct.MoveFirst: sum = 0: myCls
Do While DBRct.EOF = False
flx.AddItem (Empty): flx.Row = flx.Rows - 2
flx.Col = 0: flx.Text = flx.Rows - 2: flx.CellAlignment = 4
For intIndex = 1 To 3
 flx.Col = intIndex
 If DBRct.Fields(intIndex - 1) <> Empty Then flx.Text = DBRct.Fields(intIndex - 1): flx.CellAlignment = 4
Next
sum = sum + DBRct.Fields(2): DBRct.MoveNext
Loop
MsgBox "总共未交的罚款为￥" & Format(sum, "##0.00"), vbInformation, "Totel"
End Sub

Private Sub Command6_Click()
Unload Me
End Sub

Private Sub Form_Resize()
Width = 7185: Height = 4605
End Sub