1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
| TESTDATALOC = _samples+"data\testdata.DBC"
oForm = createobject('myForm')
oForm.Show
Read events
Define CLASS myform AS form
Top = 0
Left = 0
Height = 450
Width = 750
DoCreate = .T.
Caption = "Form1"
Name = "Form1"
Add OBJECT command1 as commandbutton with ;
Autosize = .t., ;
Top = 0, ;
Left = 0, ;
Name = "Set1", ;
Caption = 'Sample 1'
Add OBJECT command2 as commandbutton with ;
Autosize = .t., ;
Top = 0, ;
Left = 0, ;
Name = "Set2", ;
Caption = 'Sample 2'
Add OBJECT command3 as commandbutton with ;
Autosize = .t., ;
Top = 0, ;
Left = 0, ;
Name = "Set3", ;
Caption = 'Sample 3'
Add OBJECT command4 as commandbutton with ;
Autosize = .t., ;
Top = 0, ;
Left = 0, ;
Name = "Set4", ;
Caption = 'Sample 4'
Add OBJECT hflex AS olecontrol WITH ;
Top = 0, ;
Left = 0, ;
Height = 420, ;
Width = 750, ;
Name = "Hflex", ;
OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'
Procedure LoadSet
Lparameters tnSet
Local oRecordset,oConnection, strCn, strShp
strCn = [Provider=MSDataShape;Persist Security Info=False;]+;
[Data Source=]+TESTDATALOC+[;Data Provider=VFPOLEDB]
oRecordset = CreateObject("adodb.recordset")
oConnection = CreateObject("adodb.connection")
With oConnection
.Provider = "MSDataShape"
.ConnectionString = strCn
.Open
Endwith
lcSel1 = [ select customer.cust_id, ]+;
[ customer.Company,]+;
[ orders.order_id,]+;
[ orders.Order_date ]+;
[ from customer ]+;
[ inner join orders on customer.cust_id = orders.cust_id ]
lcSel2 = [ select od.order_id, od.line_no, ]+;
[ products.prod_name, ]+;
[ products.no_in_unit as 'Packaging', ]+;
[ od.unit_price, ]+;
[ od.Quantity, ]+;
[ od.unit_price * od.quantity as ExtendedPrice ]+;
[ from orditems as od ]+;
[ inner join products on od.product_id = products.product_id ]
Do case
Case tnSet = 1
strShp = [SHAPE TABLE customer ]+;
[ APPEND ( (SHAPE TABLE orders ]+;
[ APPEND (TABLE orditems RELATE order_id TO order_id)) ]+;
[ RELATE cust_id TO cust_id ) ]
Case tnSet = 2
strShp = [SHAPE { select Company, cust_id from customer } ]+;
[APPEND (( SHAPE { select distinct First_name, Last_name, a.emp_id + cust_id as "Emp_sel", cust_id from employee a inner join orders b on a.emp_id = b.emp_id } ]+;
[APPEND (( SHAPE { select order_date, order_net, shipped_on, emp_id + cust_id as "Emp_sel",order_id from orders } ]+;
[APPEND ( { select order_id, line_no, prod_name from orditems inner join products on products.product_id = orditems.product_id } AS rsOrditems ]+;
[RELATE order_id TO order_id )) AS rsEmployee ]+;
[RELATE emp_sel TO emp_sel )) AS rsOrders ]+;
[RELATE cust_id TO cust_id ) ]
Case tnSet = 3
strShp = [ SHAPE {SELECT cust_id, company FROM customer} ]+;
[APPEND ({SELECT cust_id, order_id, order_date, order_net ]+;
[ FROM orders ]+;
[ WHERE order_date < {1/1/1996} AND cust_id = ?} ]+;
[ RELATE cust_id TO PARAMETER 0) AS rsOldOrders, ]+;
[ ({SELECT cust_id, order_id, order_date, order_net ]+;
[ FROM orders ]+;
[ WHERE order_date >= {1/1/1996}} ]+;
[ RELATE cust_id TO cust_id) AS rsRecentOrders ]
Case tnSet = 4
strShp = [ SHAPE ]+;
[(SHAPE {]+lcSel1+[ } as rs1 ]+;
[ APPEND ({]+lcSel2+[ } AS rsDetails RELATE order_id TO order_id), ]+;
[ SUM(rsDetails.ExtendedPrice) AS OrderTotal, ANY(rsDetails.order_id)) AS rsOrders ]+;
[COMPUTE rsOrders, ]+;
[SUM(rsOrders.OrderTotal) AS CustTotal, ]+;
[ANY(rsOrders.Company) AS Cmpny ]+;
[ BY cust_id ]
Endcase
With oRecordset
.ActiveConnection = oConnection
.Source = strShp
.Open
Endwith
With this.hflex
.Datasource = oRecordset
.Mergecells = 3
.GridColorBand(1) = rgb(255,0,0)
.GridColorBand(2) = rgb(0,0,255)
.GridColorBand(3) = rgb(0,255,0)
.ColWidth(0,0) = 300
.CollapseAll
Endwith
Endproc
Procedure Init
With this
.Set2.Left = .Set1.Left + .Set1.Width + 5
.Set3.Left = .Set2.Left + .Set2.Width + 5
.Set4.Left = .Set3.Left + .Set3.Width + 5
.hflex.Top = .Set1.Top + .Set1.Height + 5
.hflex.Height = .Height - (.hflex.Top + 5)
.hflex.Left = 5
.hflex.Width = .Width - 10
.LoadSet(1)
Endwith
Endproc
Procedure QueryUnLoad
Clear events
Endproc
Procedure Set1.Click
Thisform.LoadSet(1)
Endproc
Procedure Set2.Click
Thisform.LoadSet(2)
Endproc
Procedure Set3.Click
Thisform.LoadSet(3)
Endproc
Procedure Set4.Click
Thisform.LoadSet(4)
Endproc
Enddefine |
