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