Subject |
Re: oodml coversion |
From |
Mervyn Bick <invalid@invalid.invalid> |
Date |
Mon, 23 May 2016 17:29:28 +0200 |
Newsgroups |
dbase.getting-started |
Attachment(s) |
email1.wfm |
On 22/05/2016 09:05, Charlie wrote:
> I am starting to convert this xdml code to oodml code.
>
> I believe there may be a couple of ways to do this. As you can see in xdml I have several filters set based on what radio button is selected. Can I filter in a similar way in oodml or would I be better off to define each filter in new queries?
There is almost always more than one way to do something. In fact it's
usually 12 programmers, a dozen ways. :-)
Yes, you can filter in much the same way that you have been doing BUT (
:-) ) filter expressions for OODML rowsets are SQL expressions. This
means you can't incorporate today(), which is dBASE and not SQL,
directly into the filter expression as you have been doing. So, while
it can be done the code would tend to be "messy".
The way I would do it is shown in the attached example. Bear in mind
though that you are likely to get as many different ideas as answers.
So, while this is the Bick way, there may well be a Better way.
The query in the form is parameter driven so that today's date can
easily be passed into it. The resulting rowset has calculated fields
for the number of orders for each customer in the past 7 days, the past
30 days, the past 90 days, the past year and the past two years.
The example, as it stands, makes no provision for varying the 7 day
period. It is, however, a simple matter to add a second parameter to
deal with this.
Because a query can handle a parameter as well as a between predicate in
a where clause the coding becomes much "cleaner" than a filter would be.
Each day when you start the form a new set of values is calculated
automatically.
Once the query has been set up the filters become dead simple. It is
simply a matter of checking for a value greater than 0 in the
appropriate calculated field. To find your loyal customers all you need
to do is filter for > 10 in the 2year field.
It's not written on tablets of stone but temporary files should be a
last resort. The code to send the emails should be incorporated into
this form. The rowset has already been whittled down to the customers
you want to email. Simply loop through the rowset and send them.
Mervyn.
| ** END HEADER -- do not remove this line
//
// Generated on 2016/05/23
//
parameter bModal
local f
f = new email1Form()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class email1Form of FORM
with (this)
onOpen = class::FORM_ONOPEN
height = 28.6364
left = 34.1429
top = 0.5
width = 114.4286
text = ""
endwith
this.DBASESAMPLES1 = new DATABASE(this)
with (this.DBASESAMPLES1)
left = 7.0
databaseName = "DBASESAMPLES"
active = true
endwith
this.CUSTOMERS1 = new QUERY(this)
with (this.CUSTOMERS1)
left = 26.0
database = form.dbasesamples1
sql = "Select customers.CustomerID,customers.Company,Count(o.OrderDate) As c7,"
sql +="Count(o1.OrderDate) As c30,Count(o2.OrderDate) As c90,"
sql +="Count(o3.OrderDate) As c365,Count(o4.OrderDate) As c730 "
sql +="From customers "
sql +="Left Join orders o On (o.orderdate between :d1 - 7 and :d1) and customers.CustomerID = o.CustomerID "
sql +="Left Join orders o1 On (o1.orderdate between :d1 - 30 and :d1) and customers.CustomerID = o1.CustomerID "
sql +="Left Join orders o2 On (o2.orderdate between :d1 - 90 and :d1) and customers.CustomerID = o2.CustomerID "
sql +="Left Join orders o3 On (o3.orderdate between :d1 - 365 and :d1) and customers.CustomerID = o3.CustomerID "
sql +="Left Join orders o4 On (o4.orderdate between :d1 - 730 and :d1) and customers.CustomerID = o4.CustomerID "
sql +="Group By customers.CustomerID,customers.Company"
params["d1"] = { / / }
active = true
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.customers1.rowset
columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1)
with (columns["COLUMN1"])
dataLink = form.customers1.rowset.fields["customerid"]
editorType = 3 // SpinBox
width = 12.2857
endwith
columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1)
with (columns["COLUMN2"])
dataLink = form.customers1.rowset.fields["company"]
editorType = 1 // EntryField
width = 32.4286
endwith
with (columns["COLUMN1"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with (columns["COLUMN1"].headingControl)
value = "CustomerID"
endwith
with (columns["COLUMN2"].headingControl)
value = "Company"
endwith
integralHeight = true
allowEditing = false
height = 18.0
left = 54.0
top = 2.5
width = 51.0
endwith
this.RADIOBUTTON1 = new RADIOBUTTON(this)
with (this.RADIOBUTTON1)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 3.3182
width = 43.0
text = "Customers with orders in last 7 days"
group = true
endwith
this.RADIOBUTTON2 = new RADIOBUTTON(this)
with (this.RADIOBUTTON2)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 5.6364
width = 43.0
text = "Customers with orders in last 30 days"
endwith
this.RADIOBUTTON3 = new RADIOBUTTON(this)
with (this.RADIOBUTTON3)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 7.9545
width = 43.0
text = "Customers with orders in last 90 days"
endwith
this.RADIOBUTTON4 = new RADIOBUTTON(this)
with (this.RADIOBUTTON4)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 10.2727
width = 43.0
text = "Customers with orders in past year"
endwith
this.RADIOBUTTON5 = new RADIOBUTTON(this)
with (this.RADIOBUTTON5)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 12.5909
width = 43.0
text = "Customers with orders in past 2 years"
endwith
this.RADIOBUTTON6 = new RADIOBUTTON(this)
with (this.RADIOBUTTON6)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 14.9091
width = 43.0
text = "Loyal customers (more than 10 orders in 2 years)"
endwith
this.RADIOBUTTON7 = new RADIOBUTTON(this)
with (this.RADIOBUTTON7)
onChange = class::RADIOBUTTON_ONCHANGE
height = 1.0909
left = 4.0
top = 17.2273
width = 44.0
text = "All customers"
value = true
endwith
this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
height = 1.0
left = 67.0
top = 22.0
width = 8.0
value = 0
endwith
this.TEXTLABEL1 = new TEXTLABEL(this)
with (this.TEXTLABEL1)
height = 1.0
left = 77.0
top = 22.0
width = 17.0
text = "Records selected"
endwith
this.COMBOBOX1 = new COMBOBOX(this)
with (this.COMBOBOX1)
onChange = class::COMBOBOX1_ONCHANGE
onOpen = class::COMBOBOX1_ONOPEN
height = 1.0
left = 4.0
top = 22.0
width = 46.0
dataSource = 'array {"New stock has arrived","Fantastic savings","Extremely rare offerings for the next auction"}'
style = 1 // DropDown
endwith
this.PUSHBUTTON1 = new PUSHBUTTON(this)
with (this.PUSHBUTTON1)
enabled = false
height = 3.5
left = 71.0
top = 24.0
width = 15.2857
text = "Send email to selected customers "
endwith
this.rowset = this.customers1.rowset
function COMBOBOX1_onChange()
form.pushbutton1.enabled = true
return
function COMBOBOX1_onOpen()
this.value = "Choose an email to send"
return
function RADIOBUTTON_onChange()
if form.radiobutton1.value =true
form.customers1.rowset.filter = "c7 > 0"
elseif form.radiobutton2.value = true
form.customers1.rowset.filter = "c30 > 0"
elseif form.radiobutton3.value = true
form.customers1.rowset.filter = "c90 > 0"
elseif form.radiobutton4.value = true
form.customers1.rowset.filter = "c365 > 0"
elseif form.radiobutton5.value = true
form.customers1.rowset.filter = "c730 > 0"
elseif form.radiobutton6.value = true
form.customers1.rowset.filter = "c730 > 10"
elseif form.radiobutton7.value = true
form.customers1.rowset.filter = ""
form.customers1.requery()
endif
form.combobox1.value = "Choose an email to send"
form.pushbutton1.enabled = false
form.entryfield1.value = form.customers1.rowset.count()
return
function form_onOpen()
form.customers1.active = false
// form.customers1.params['d1'] = today()
form.customers1.params['d1'] = new date(2002,11,31)
form.customers1.active = true
form.entryfield1.value = form.customers1.rowset.count()
return
endclass
/*
If the form is opened in the designer the SQL statement will be output as a long line.
This copy is for easy reference.
sql = "Select customers.CustomerID,customers.Company,Count(o.OrderDate) As c7,"
sql +="Count(o1.OrderDate) As c30,Count(o2.OrderDate) As c90,"
sql +="Count(o3.OrderDate) As c365,Count(o4.OrderDate) As c730 "
sql +="From customers "
sql +="Left Join orders o On (o.orderdate between :d1 - 7 and :d1) and customers.CustomerID = o.CustomerID "
sql +="Left Join orders o1 On (o1.orderdate between :d1 - 30 and :d1) and customers.CustomerID = o1.CustomerID "
sql +="Left Join orders o2 On (o2.orderdate between :d1 - 90 and :d1) and customers.CustomerID = o2.CustomerID "
sql +="Left Join orders o3 On (o3.orderdate between :d1 - 365 and :d1) and customers.CustomerID = o3.CustomerID "
sql +="Left Join orders o4 On (o4.orderdate between :d1 - 730 and :d1) and customers.CustomerID = o4.CustomerID "
sql +="Group By customers.CustomerID,customers.Company"
*/
|
|