frm_statistics.frm

图书馆信息管理系统

            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H80000008&
         Height          =   480
         Left            =   0
         ScaleHeight     =   480
         ScaleWidth      =   480
         TabIndex        =   1
         Top             =   0
         Width           =   480
         Begin VB.PictureBox Pic_TBarSymbol 
            Appearance      =   0  'Flat
            AutoRedraw      =   -1  'True
            BackColor       =   &H00C0C0C0&
            BorderStyle     =   0  'None
            BeginProperty Font 
               Name            =   "MS Sans Serif"
               Size            =   8.25
               Charset         =   0
               Weight          =   400
               Underline       =   0   'False
               Italic          =   0   'False
               Strikethrough   =   0   'False
            EndProperty
            ForeColor       =   &H80000008&
            Height          =   240
            Left            =   120
            Picture         =   "Frm_Statistics.frx":84A6
            ScaleHeight     =   240
            ScaleWidth      =   240
            TabIndex        =   2
            Top             =   120
            Width           =   240
         End
      End
      Begin LabMangeSystem.XButton Cmd_TBarClose 
         Height          =   240
         Left            =   8520
         TabIndex        =   4
         Top             =   120
         Width           =   240
         _ExtentX        =   423
         _ExtentY        =   423
         Caption         =   ""
         ToolTip         =   "关闭当前窗口"
         BackColor       =   16777215
         MouseDownColor  =   -2147483644
         MouseOnColor    =   -2147483644
         StyleColor      =   0
         Style3dColor1   =   16577259
         Style3dColor2   =   8421504
         Picture         =   "Frm_Statistics.frx":8A30
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
      End
      Begin VB.Label Lbl_TBarText 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "TitleBar Text"
         BeginProperty Font 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   195
         Left            =   600
         TabIndex        =   3
         Top             =   150
         Width           =   1095
      End
   End
   Begin VB.Line Line_SplitShadow 
      BorderColor     =   &H00FFFFFF&
      X1              =   255
      X2              =   255
      Y1              =   615
      Y2              =   6855
   End
   Begin VB.Line Line_Split 
      BorderColor     =   &H00C0C0C0&
      X1              =   240
      X2              =   240
      Y1              =   600
      Y2              =   6840
   End
End
Attribute VB_Name = "Frm_Statistics"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim LastHLight As XButton    '当前的高亮按钮

Dim strSQL As String       'SQL查询语句
Dim strSELECT As String    'SQL查询语句的SELECT子句
Dim strFROM As String      'SQL查询语句的FROM子句
Dim strWHERE As String     'SQL查询语句的WHERE子句
Dim strGROUP As String     'SQL查询语句的GROUP子句
Dim strORDER As String     'SQL查询语句的ORDER子句
Dim Err_Msg As String      '错误信息
Option Explicit

Private Sub Cmd_Book_Click()
    If LastHLight.caption = Cmd_Book.caption Then Exit Sub
    Call ShowBook
End Sub

Private Sub Cmd_Borrow_Click()
    If LastHLight.caption = Cmd_Borrow.caption Then Exit Sub
    Call ShowBorrow
End Sub

Private Sub Cmd_OK_Click()
    Opt_Book.Value = True
    Call LoadData_Borrow
End Sub

Private Sub Cmd_Reader_Click()
    If LastHLight.caption = Cmd_Reader.caption Then Exit Sub
    Call ShowReader
End Sub

Private Sub Cmd_TBarClose_Click()
    Unload Me
    Frm_Welcome.Show
    Set LastForm = Frm_Welcome
End Sub

Private Sub Form_Load()
    Lbl_TBarText.caption = Me.caption
   
    Frame_Book.Left = 480
    Frame_Book.Top = 1200
    Frame_Reader.Left = 480
    Frame_Reader.Top = 1200
    Frame_Borrow.Left = 480
    Frame_Borrow.Top = 1200
    
    Call ShowBook
End Sub

Private Sub Form_Resize()
On Error Resume Next
    Pic_NBar.Width = Me.Width - Pic_NBar.Left - 240 - 45
    Pic_NBarSub1.Width = Pic_NBar.Width - Pic_NBarSub1.Left
    
    Frame_Book.Width = Me.Width - Frame_Book.Left - 240 - 45
    Frame_Book.Height = Me.Height - Frame_Book.Top - 240 - 435
    Chart_Book.Width = Frame_Book.Width - Chart_Book.Left - 240 - 45
    Chart_Book.Height = Frame_Book.Height - Chart_Book.Top - 240 - 45
    
    Frame_Reader.Width = Frame_Book.Width
    Frame_Reader.Height = Frame_Book.Height
    Chart_Reader.Width = Frame_Book.Width - Chart_Reader.Left - 240 - 45
    Chart_Reader.Height = Frame_Book.Height - Chart_Reader.Top - 240 - 45
    
    Frame_Borrow.Width = Frame_Book.Width
    Frame_Borrow.Height = Frame_Book.Height
    Chart_Brw.Width = Frame_Book.Width - Chart_Brw.Left - 240 - 45
    Chart_Brw.Height = Frame_Book.Height - Chart_Brw.Top - 240 - 45
    
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Call ShowTip
End Sub

Private Sub Opt_Book_Click()
    Txt_From.SetFocus
End Sub

Private Sub Opt_Reader_Click()
    Call LoadData_Borrow
End Sub

Private Sub Pic_TitleBar_Resize()
On Error Resume Next
    Cmd_TBarClose.Left = Pic_TitleBar.Width - 24 * 15
End Sub

Private Sub Txt_From_Click()
    Opt_Book.Value = True
End Sub

Private Sub Txt_To_Click()
    Opt_Book.Value = True
End Sub

Private Sub ShowBook()
    Call LoadData_Book
    
    Call SetHLight(Cmd_Book)
    Frame_Book.Visible = True
    Frame_Reader.Visible = False
    Frame_Borrow.Visible = False
End Sub

Private Sub ShowReader()
    Call LoadData_Reader
    
    Call SetHLight(Cmd_Reader)
    Frame_Book.Visible = False
    Frame_Reader.Visible = True
    Frame_Borrow.Visible = False
End Sub

Private Sub ShowBorrow()
    Opt_Reader.Value = True
    Call LoadData_Borrow

    Call SetHLight(Cmd_Borrow)
    Frame_Book.Visible = False
    Frame_Reader.Visible = False
    Frame_Borrow.Visible = True
End Sub

Private Sub SetHLight(Optional HLCmd As XButton)
On Error Resume Next
    '恢复高亮按钮(包括HLCmd对象为空情况)
    LastHLight.BackColor = &H8000000F
    LastHLight.ForeColor = &H808080
    LastHLight.Font.Bold = False
    
    '设置HLCmd对象为高亮按钮
    HLCmd.BackColor = RGB(255, 255, 255)
    HLCmd.ForeColor = RGB(0, 0, 0)
    HLCmd.Font.Bold = True
    Set LastHLight = HLCmd
End Sub

Private Sub LoadData_Book()
On Error Resume Next
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    strSELECT = "SELECT DISTINCT ISBN"
    strFROM = "FROM 图书档案"
    strSQL = strSELECT & " " & strFROM
    rs.Open strSQL, cnMain, 1, 1
    Lbl_BookNum.caption = "共有图书 " & rs.RecordCount & " 种"
    
    Set rs = New ADODB.Recordset
    strSELECT = "SELECT 图书编号"
    strFROM = "FROM 图书库存"
    strSQL = strSELECT & " " & strFROM
    rs.Open strSQL, cnMain, 1, 1
    Lbl_BookNum.caption = Lbl_BookNum.caption & "，总计库存 " & rs.RecordCount & " 本"
   
    Set rs = New ADODB.Recordset
    strSELECT = "SELECT TOP 5 出版社名称, COUNT(出版社名称) AS 图书种类"
    strFROM = "FROM 图书档案"
    strGROUP = "GROUP BY 出版社名称"
    strORDER = "ORDER BY 图书种类 DESC"
    strSQL = strSELECT & " " & strFROM & " " & strGROUP & " " & strORDER
    rs.Open strSQL, cnMain, 1, 1
    
    With Chart_Book
        .RowCount = 5
        .ColumnCount = 1
        For i = 1 To rs.RecordCount
            .Row = i
            .RowLabel = vbNullString
            .RowLabel = rs(0).Value
            .Data = 0
            .Data = rs(1).Value
            rs.MoveNext
        Next i
        .Refresh
    End With
End Sub

Private Sub LoadData_Reader()
On Error Resume Next
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    strSELECT = "SELECT DISTINCT 读者编号"
    strFROM = "FROM 读者档案"
    strSQL = strSELECT & " " & strFROM
    rs.Open strSQL, cnMain, 1, 1
    Lbl_ReaderNum.caption = "共有读者 " & rs.RecordCount & " 名"
    
    Set rs = New ADODB.Recordset
    strSELECT = "SELECT TOP 10 读者编号, 账户金额"
    strFROM = "FROM 读者档案"
    strORDER = "ORDER BY 账户金额 DESC"
    strSQL = strSELECT & " " & strFROM & " " & strORDER
    rs.Open strSQL, cnMain, 1, 1
    
    With Chart_Reader
        .RowCount = 10
        .ColumnCount = 1
        For i = 1 To rs.RecordCount
            .Row = i
            .RowLabel = vbNullString
            .RowLabel = rs(0).Value
            .Data = 0
            .Data = rs(1).Value
            rs.MoveNext
        Next i
        .Refresh
    End With
End Sub

Private Sub LoadData_Borrow()
On Error Resume Next
    Dim rs As ADODB.Recordset
    Dim i As Integer
    
    With Chart_Brw
        If Opt_Reader.Value = True Then
            Set rs = New ADODB.Recordset
            strSELECT = "SELECT TOP 10 读者编号, 总借书数量"
            strFROM = "FROM 读者档案"
            strORDER = "ORDER BY 总借书数量 DESC"
            strSQL = strSELECT & " " & strFROM & " " & strORDER
            rs.Open strSQL, cnMain, 1, 1
            
            .Title = Opt_Reader.caption
        Else
            Dim D_From As String
            Dim D_To As String
            
            If IsDate(Txt_From.Text) = False Then
                D_From = "2000-01-01"
                Txt_From.Text = D_From
            Else
                D_From = Trim(Txt_From.Text)
            End If
            
            If IsDate(Txt_To.Text) = False Then
                D_To = Format(Date, "yyyy-mm-dd")
                Txt_To.Text = D_To
            Else
                D_To = Trim(Txt_To.Text)
            End If
            
            Set rs = New ADODB.Recordset
            strSELECT = "SELECT TOP 10 图书档案.ISBN, COUNT(图书档案.ISBN) AS 借阅次数"
            strFROM = "FROM 图书借阅, 图书档案, 图书库存"
            strWHERE = "WHERE 图书借阅.图书编号 = 图书库存.图书编号 AND 图书库存.ISBN = 图书档案.ISBN AND 借书日期 BETWEEN " & Str2SQL(D_From) & " AND " & Str2SQL(D_To)
            strGROUP = "GROUP BY 图书档案.ISBN"
            strORDER = "ORDER BY 借阅次数 DESC"
            strSQL = strSELECT & " " & strFROM & " " & strWHERE & " " & strGROUP & " " & strORDER
            rs.Open strSQL, cnMain, 1, 1
            
            .Title = Opt_Book.caption
        End If
        
        .RowCount = 10
        .ColumnCount = 1
        For i = 1 To 10
            .Row = i
            .RowLabel = vbNullString
            .RowLabel = Trim(rs(0).Value)
            .Data = 0
            .Data = rs(1).Value
            rs.MoveNext
        Next i
        .Refresh
    End With
End Sub