📄 frmorder.frm
字号:
VERSION 5.00
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.1#0"; "COMCTL32.OCX"
Begin VB.Form frmOrder
BorderStyle = 5 'Sizable ToolWindow
Caption = "统计"
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 = "关闭(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 = "标题"
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 = "进货交易金额 TOP TEN"
Set clmX = lsvOrder.ColumnHeaders.Add(, , "厂商", 1000)
Set clmX = lsvOrder.ColumnHeaders.Add(, , "进货总额(元)", 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 = "顾客购买金额 TOP TEN"
Set clmX = lsvOrder.ColumnHeaders.Add(, , "顾客", 1000)
Set clmX = lsvOrder.ColumnHeaders.Add(, , "购买总额(元)", 1500)
'按客户名称取购买总额的结果集,降序排列
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(, , "型号", 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 = "商品销售量 TOP TEN"
Set clmX = lsvOrder.ColumnHeaders.Add(, , "商品", 1000)
Set clmX = lsvOrder.ColumnHeaders.Add(, , "型号", 1000)
Set clmX = lsvOrder.ColumnHeaders.Add(, , "销售总量", 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 = "业务员销售业绩"
Set clmX = lsvOrder.ColumnHeaders.Add(, , "业务员", 1000)
Set clmX = lsvOrder.ColumnHeaders.Add(, , "销售业绩(元)", 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 = "业务员月薪"
Set clmX = lsvOrder.ColumnHeaders.Add(, , "业务员", 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 ("统计")
lsvOrder.View = lvwReport
ShowList
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -