VERSION 5.00
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.1#0"; "COMCTL32.OCX"
Begin VB.Form frmOrder 
   BorderStyle     =   5  'Sizable ToolWindow
   Caption         =   "統(tǒng)計(jì)"
   ClientHeight    =   3960
   ClientLeft      =   60
   ClientTop       =   300
   ClientWidth     =   4395
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3960
   ScaleWidth      =   4395
   ShowInTaskbar   =   0   'False
   StartUpPosition =   2  'CenterScreen
   Begin VB.Frame fraOrder 
      Height          =   4005
      Left            =   0
      TabIndex        =   0
      Top             =   -60
      Width           =   4395
      Begin VB.CommandButton cmdClose 
         Caption         =   "關(guān)閉(C)"
         Default         =   -1  'True
         Height          =   315
         Left            =   2820
         TabIndex        =   3
         Top             =   3450
         Width           =   1335
      End
      Begin ComctlLib.ListView lsvOrder 
         Height          =   2715
         Left            =   180
         TabIndex        =   1
         TabStop         =   0   'False
         Top             =   540
         Width           =   4005
         _ExtentX        =   7064
         _ExtentY        =   4789
         LabelWrap       =   -1  'True
         HideSelection   =   -1  'True
         _Version        =   327680
         ForeColor       =   -2147483640
         BackColor       =   -2147483643
         BorderStyle     =   1
         Appearance      =   1
         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
         MouseIcon       =   "frmOrder.frx":0000
         NumItems        =   0
      End
      Begin VB.Label lblCaption 
         AutoSize        =   -1  'True
         Caption         =   "標(biāo)題"
         Height          =   180
         Left            =   240
         TabIndex        =   2
         Top             =   240
         Width           =   360
      End
   End
End
Attribute VB_Name = "frmOrder"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub ShowList()
  Dim i As Integer
  Dim clmX As ColumnHeader
  Dim itmX As ListItem
  Dim found As Boolean
  
On Error GoTo ErrorHandle
  
  Set rdoConn = New rdoConnection
  rdoConn.Connect = ConnectID
  rdoConn.EstablishConnection rdDriverNoPrompt, False
  Select Case WhatToOrder
  Case "IFO"
    lblCaption.Caption = "進(jìn)貨交易金額 TOP TEN"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "廠商", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "進(jìn)貨總額(元)", 1500)
    Set rdoRS = rdoConn.OpenResultset("select f_name,sum(i_inprice*i_count) i_sum " _
                                     & "from input,factory,product " _
                                     & "where input.p_id=product.p_id and product.f_id=factory.f_id " _
                                     & "group by factory.f_name " _
                                     & "order by i_sum desc")
    i = 1
    With rdoRS
      While i <= 10 And Not .EOF
        Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("f_name"))
        itmX.Tag = 1
        itmX.SubItems(1) = .rdoColumns("i_sum")
        i = i + 1
        .MoveNext
      Wend
      rdoRS.Close
    End With
    
  Case "TCO"
    lblCaption.Caption = "顧客購(gòu)買(mǎi)金額 TOP TEN"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "顧客", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "購(gòu)買(mǎi)總額(元)", 1500)
    '按客戶(hù)名稱(chēng)取購(gòu)買(mǎi)總額的結(jié)果集,降序排列
    Set rdoRS = rdoConn.OpenResultset("select c_name,sum((o_count-r_count)*o_price) o_sum " _
                                      & "from customer,output " _
                                      & "where output.c_id=customer.c_id " _
                                      & "group by c_name " _
                                      & "order by o_sum desc")
   i = 1
    With rdoRS
      While Not .EOF And i <= 10
        If .rdoColumns("o_sum") <> 0 Then
          Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("c_name"))
          itmX.Tag = 1
          itmX.SubItems(1) = .rdoColumns("o_sum")
          .MoveNext
          i = i + 1
        Else
          .MoveNext
        End If
      Wend
      .Close
    End With
    
  Case "CRO"
    lblCaption.Caption = "商品退貨總量 TOP TEN"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "商品", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "型號(hào)", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "退貨總量", 1000)
    Set rdoRS = rdoConn.OpenResultset("select p_name,p_model,sum(retreat.r_count) r_total " _
                                     & "from product,customer,retreat,output " _
                                     & "where retreat.o_id=output.o_id and output.p_id=product.p_id and output.c_id=customer.c_id " _
                                     & "group by p_name,p_model " _
                                     & "order by r_total desc ")
    i = 1
    With rdoRS
      While Not .EOF And i <= 10
        Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("p_name"))
        itmX.Tag = 2
        itmX.SubItems(1) = .rdoColumns("p_model")
        itmX.SubItems(2) = .rdoColumns("r_total")
        i = i + 1
        .MoveNext
      Wend
      .Close
    End With
    
  Case "PSO"
    lblCaption.Caption = "商品銷(xiāo)售量 TOP TEN"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "商品", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "型號(hào)", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "銷(xiāo)售總量", 1000)
    
    Set rdoRS = rdoConn.OpenResultset("select p_name,p_model,sum(o_count-r_count) o_total " _
                                      & "from product,output " _
                                      & "where output.p_id=product.p_id " _
                                      & "group by p_name,p_model " _
                                      & "order by o_total desc")
    i = 1
    With rdoRS
      While Not .EOF And i <= 10
        If .rdoColumns("o_total") <> 0 Then
          Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("p_name"))
          itmX.Tag = 2
          itmX.SubItems(1) = .rdoColumns("p_model")
          itmX.SubItems(2) = .rdoColumns("o_total")
          .MoveNext
          i = i + 1
        Else
          .MoveNext
        End If
      Wend
      .Close
    End With
    
  Case "BTO"
    lblCaption.Caption = "業(yè)務(wù)員銷(xiāo)售業(yè)績(jī)"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "業(yè)務(wù)員", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "銷(xiāo)售業(yè)績(jī)(元)", 1500)
    Set rdoRS = rdoConn.OpenResultset("select b_name,b_trades from businessman " _
                                       & "order by b_trades desc")
    With rdoRS
      While Not .EOF
        Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("b_name"))
        itmX.Tag = 1
        itmX.SubItems(1) = .rdoColumns("b_trades")
        .MoveNext
      Wend
      .Close
    End With
    
  Case "BS"
    lblCaption.Caption = "業(yè)務(wù)員月薪"
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "業(yè)務(wù)員", 1000)
    Set clmX = lsvOrder.ColumnHeaders.Add(, , "月薪(元)", 1000)
    Set rdoRS = rdoConn.OpenResultset("select b_name,b_salary= b_limitsalary+b_trades*0.02 from businessman " _
                                       & "order by b_salary desc")
    
    With rdoRS
      While Not .EOF
        Set itmX = lsvOrder.ListItems.Add(, , .rdoColumns("b_name"))
        itmX.Tag = 2
        itmX.SubItems(1) = Format(.rdoColumns("b_salary"), "#######.##")
        .MoveNext
      Wend
      .Close
    End With
  End Select
Exit Sub
ErrorHandle:
  ShowErr
End Sub
Private Sub cmdClose_Click()
  ShowStatus ("")
  Unload Me
End Sub

Private Sub Form_Load()
  ShowStatus ("統(tǒng)計(jì)")
  lsvOrder.View = lvwReport
  ShowList
End Sub