frmanalysis.frm

<Visual Basic 数据库开发实例精粹（第二版）>一书首先介绍了Visual Basic(简称VB)开发的技巧和重点技术

      Top             =   0
      Width           =   1092
   End
End
Attribute VB_Name = "frmAnalysis"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

'Public dbs  As Database       '数据库变量用于打开数据库文件
Public rec As Recordset       '打开“StockInfo”的数据记录
Dim TotalCount As Integer     '打开的数据表中的记录总数
Dim MaxDate  As Date          '股票信息中日期的最大值
Dim MinDate  As Date          '股票信息中日期的最小指
Dim SearchDate As Date        '需要查询的日期
Dim AverageSel(3) As Integer  '查询价格时，设置边界点的数组
Dim ChangeRateSel(5) As Single '查询变化率时，设置边界点的数组

'三个组合框选择按钮处理函数
Private Sub ComboPrice_Click()
    Dim strSQL As String
    '按照组合框点击的索引，选择相应范围内的股票
    strSQL = "SELECT StockChange.Code as 股票代码,Name as 股票名称,Change as 涨跌,"
    strSQL = strSQL & "([Volume Price] / Volume) as 价格, TypeName as 类型 "
    strSQL = strSQL & " FROM StockChange,StockInfo,StockType "
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# AND "
    If ComboPrice.ListIndex = 3 Then
        strSQL = strSQL & " ([Volume Price] / Volume) >30 AND "
    Else
        strSQL = strSQL & " ([Volume Price] / Volume) > " & _
                    AverageSel(ComboPrice.ListIndex) & " AND "
        strSQL = strSQL & " ([Volume Price] / Volume) <= " & _
                    AverageSel(ComboPrice.ListIndex + 1) & " AND "
    End If
    strSQL = strSQL & " StockChange.Code=StockInfo.Code AND "
    strSQL = strSQL & " StockInfo.TypeId=StockType.ID "
        
    AdoAna.RecordSource = strSQL
    AdoAna.Refresh
    
    TotalCount = AdoAna.Recordset.RecordCount
    ComboChange.Enabled = True
    Label4.Caption = "共 " & TotalCount & " 条记录。"
End Sub

Private Sub ComboChange_Click()
    Dim strSQL As String

    '按照查询涨跌率组合框点击的索引，选择相应范围内的股票
    strSQL = "SELECT StockChange.Code as 股票代码,Name as 股票名称,Change as 涨跌,"
    strSQL = strSQL & " ([Volume Price] / Volume) as 价格,TypeName as 类型 "
    strSQL = strSQL & " FROM StockChange,StockInfo,StockType "
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# and "
    If ComboChange.ListIndex = 0 Then
        strSQL = strSQL & " StockChange.Change >0.1 and "
    ElseIf ComboChange.ListIndex = 5 Then
        strSQL = strSQL & " StockChange.Change <= -0.1 and "
    Else
        strSQL = strSQL & " StockChange.Change >" & _
                ChangeRateSel(ComboChange.ListIndex + 1) & " and "
        strSQL = strSQL & " StockChange.Change <= " & _
                ChangeRateSel(ComboChange.ListIndex) & " and "
    End If
    strSQL = strSQL & " StockChange.Code=StockInfo.Code and "
    strSQL = strSQL & " StockInfo.TypeId=StockType.ID"
        
    AdoAna.RecordSource = strSQL
    AdoAna.Refresh
    TotalCount = AdoAna.Recordset.RecordCount
    ComboChange.Enabled = True
    Label4.Caption = "共 " & TotalCount & " 条记录。"
 End Sub

Private Sub Command1_Click()
  Unload Me                           '退出
End Sub

Private Sub Command2_Click()
  MonthView1.Visible = True
  comQuery.Enabled = False
End Sub

Private Sub comQuery_Click()
    Dim arrMovAverage(3) As Integer       '统计价格处在各个范围内股票的数量
    Dim arrChange(5) As Integer           '统计涨跌率处在各个范围内股票的数量
    Dim RecordStatics As Recordset        '记录集变量，记录SQL语句的查询结果
    Dim i As Integer
    Dim strSQL As String
    
    SearchDate = Trim(Text1.Text)         '输入要查询股票信息的日期
    If SearchDate < MinDate Or SearchDate > MaxDate Then  '检验日期是否有效
        Call MsgBox("输入日期必须介于" & MinDate & " 和" & MaxDate & "之间！", _
                0 + 16, "股票分析系统")
        ComboPrice.Enabled = False
        ComboChange.Enabled = False
    Else                                  '显示该日期的所有股票信息
      
    strSQL = "SELECT StockChange.Code as 股票代码,Name as 股票名称,Change as 涨跌,"
    strSQL = strSQL & " ([Volume Price] / Volume) as 价格,TypeName as 类型 "
    strSQL = strSQL & " From StockChange, StockInfo, StockType "
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# and "
    strSQL = strSQL & " StockChange.Code=StockInfo.Code and "
    strSQL = strSQL & " StockInfo.TypeId=StockType.ID "
    
    AdoAna.RecordSource = strSQL
    AdoAna.Refresh
    
    '将价格处于"0-10"，"10-20"，"20-30"的股票数分别统计出来
    For i = 0 To 2
      strSQL = "select * "
      strSQL = strSQL & " From StockChange"
      strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "#  And"
      strSQL = strSQL & " (StockChange.[Volume Price] / StockChange.Volume) > " & _
                            AverageSel(i) & " And"
      strSQL = strSQL & " (StockChange.[Volume Price] / StockChange.Volume) <= " & _
                            AverageSel(i + 1)
                            
      Set RecordStatics = g_Conn.Execute(strSQL)
      'x = RecordStatics.RecordCount
      If Not RecordStatics.EOF Then
        RecordStatics.MoveLast
      End If
      arrMovAverage(i) = RecordStatics.RecordCount
    Next i
    '将价格"高于30"的股票数分别统计出来
    strSQL = "select * "
    strSQL = strSQL & " From StockChange"
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# And"
    strSQL = strSQL & " (StockChange.[Volume Price] / StockChange.Volume) > 30 "
    
    Set RecordStatics = g_Conn.Execute(strSQL)
    If Not RecordStatics.EOF Then
        RecordStatics.MoveLast
      End If
    arrMovAverage(3) = RecordStatics.RecordCount
    
    '将按价格分布的股票用图表显示出来
    MSChartPrice.ChartData = arrMovAverage         '设置图表控件的数据源
    MSChartPrice.Legend.VtFont.Size = 7            '设置图表控件中图例字体的大小
    MSChartPrice.Legend.VtFont.Name = "宋体"       '设置图表控件中图例的字体
    MSChartPrice.Title.VtFont.Size = 7             '设置标题的字体大小
    MSChartPrice.ShowLegend = True                 '设置图例可见
    MSChartPrice.Plot.SeriesCollection(1).LegendText = "0-10"  '设置图例显示内容
    MSChartPrice.Plot.SeriesCollection(2).LegendText = "10-20"
    MSChartPrice.Plot.SeriesCollection(3).LegendText = "20-30"
    MSChartPrice.Plot.SeriesCollection(4).LegendText = "高于30"
    
    '将按变化率分布的股票用图表显示出来
    '将变化率处于"涨5%-10%"，"涨0%-5%"，跌0%-5%"，"跌5%-10%"的股票数分别统计出来
    For i = 1 To 4
      strSQL = "select * "
      strSQL = strSQL & " From StockChange"
      strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# And"
      strSQL = strSQL & " StockChange.Change >" & ChangeRateSel(i + 1) & " and "
      strSQL = strSQL & " StockChange.Change <= " & ChangeRateSel(i) & " "
      Set RecordStatics = g_Conn.Execute(strSQL)
      
      If Not RecordStatics.EOF Then
        RecordStatics.MoveLast
      End If
      arrChange(i) = RecordStatics.RecordCount
    Next i
    '将变化率"涨过10%"的股票统计出来
    strSQL = "select * "
    strSQL = strSQL & " From StockChange"
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# And"
    strSQL = strSQL & " StockChange.Change >0.1 "
    Set RecordStatics = g_Conn.Execute(strSQL)
    
    If Not RecordStatics.EOF Then
        RecordStatics.MoveLast
    End If
    arrChange(0) = RecordStatics.RecordCount
    '将变化率"跌过10%"的股票统计出来
    strSQL = "select * "
    strSQL = strSQL & " From StockChange"
    strSQL = strSQL & " WHERE StockChange.Day=#" & SearchDate & "# And"
    strSQL = strSQL & " StockChange.Change <-0.1 "
    
    Set RecordStatics = g_Conn.Execute(strSQL)

    If Not RecordStatics.EOF Then
        RecordStatics.MoveLast
    End If
    arrChange(5) = RecordStatics.RecordCount
    
    MSChartChange.ChartData = arrChange     '设置图表控件的数据源
    MSChartChange.ShowLegend = True         '设置图例可见
    MSChartChange.Legend.VtFont.Size = 7    '设置图表控件中图例字体的大小
    '设置图例显示内容
    MSChartChange.Plot.SeriesCollection(1).LegendText = "涨过10%"
    MSChartChange.Plot.SeriesCollection(2).LegendText = "涨5%-10%"
    MSChartChange.Plot.SeriesCollection(3).LegendText = "涨0%-5%"
    MSChartChange.Plot.SeriesCollection(4).LegendText = "跌0%-5%"
    MSChartChange.Plot.SeriesCollection(5).LegendText = "跌5%-10%"
    MSChartChange.Plot.SeriesCollection(6).LegendText = "跌过10%"
    
    ComboPrice.Enabled = True             '设置筛选价格的组合框可用
    ComboChange.Enabled = True            '设置筛选涨跌率的组合框可用
    'AdoAna.Recordset.MoveLast
    TotalCount = AdoAna.Recordset.RecordCount '统计记录总数
    ComboChange.Enabled = False
    Label4.Caption = "共 " & TotalCount & " 条记录。"
    End If
End Sub

Private Sub Form_Load()
    Dim MovAverage As Variant              '声明添加到价格组合框里的字符串变量
    Dim ChangeRate As Variant              '声明添加到变化率组合框里的字符串变量
    Dim RecordDate As Recordset            '保存SQL语句搜索结果的记录集
    Dim i As Integer
    Dim strSQL As String
    
    AdoAna.ConnectionString = g_Conn.ConnectionString
    
    For i = 1 To 5                         '设置价格筛选范围的边界点
      ChangeRateSel(i) = 0.15 - 0.05 * i
    Next i
    For i = 0 To 3                         '设置变化率筛选范围的边界点
       AverageSel(i) = i * 10
    Next i
    '初始化字符串数组
    MovAverage = Array("0-10", "10-20", "20-30", "高于 30")
    ChangeRate = Array("涨过 10%", "涨 5%-10%", "涨 0%-5%", _
                       "跌  0%-5%", "跌  5%-10%", "跌过 10%")
    '循环过程，将数组的各个元素添加到组合框里
    For i = 0 To 3
        ComboPrice.AddItem MovAverage(i)
    Next i
    For i = 0 To 5
       ComboChange.AddItem ChangeRate(i)
    Next i
    
    MonthView1.Visible = False             '日期控件不可用
    Text1.Text = ""

    '查询所有记录中日期的最大值和最小值
    strSQL = "select max(Day) as nMax,min(Day) as nMin from StockChange"
    Set RecordDate = g_Conn.Execute(strSQL)
    If Not RecordDate.BOF And Not RecordDate.EOF Then
       MaxDate = RecordDate.Fields("nMax")
       MinDate = RecordDate.Fields("nMin")
    End If
    'grdAna.Columns.Count = 5      '设置显示表格的列总数
    'GridInit              '表格初始化
    strSQL = "SELECT StockChange.Code as 股票代码,Name as 股票名称, Change as 涨跌,"
    strSQL = strSQL & " ([Volume Price] / Volume) as 价格,Day as 日期 "
    strSQL = strSQL & " From StockChange, StockInfo, StockType "
    strSQL = strSQL & " WHERE StockChange.Code=StockInfo.Code and "
    strSQL = strSQL & " StockInfo.TypeId=StockType.ID "
    
    
    AdoAna.RecordSource = strSQL
    AdoAna.Refresh
    AdoAna.Recordset.MoveLast
    TotalCount = AdoAna.Recordset.RecordCount
    Label4.Caption = "共 " & TotalCount & " 条记录。"
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
  Text1.Text = MonthView1.Value
  MonthView1.Visible = False
  comQuery.Enabled = True
  comQuery_Click
End Sub