Subject Re: oodml coversion From Charlie <trainman@traincity.com> Date Mon, 23 May 2016 15:54:29 -0400 Newsgroups dbase.getting-started Mervyn Bick Wrote: > 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" > */ > > Hi Mervyn.. Thanks very much!  I'll start working on this.  I miss coding and this will give me something to learn!