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"
*/