if file('mbcustomers.dbf')
  // drop table mbcustomers
endif

if not file('mbcustomers.dbf')
   create table mbcustomers  (id autoinc,Name character(15),city character(15))
   insert into mbcustomers  (Name,city) values ("Abel","Johnnesburg")
   insert into mbcustomers  (Name,city) values ("Baker","Cape Town")
   insert into mbcustomers  (Name,city) values ("Charlie","Bloemfontein")
   insert into mbcustomers  (Name,city) values ("David","Durban")
   insert into mbcustomers  (Name,city) values ("Edward","Pretoria")
endif

if file('mbinvoice.dbf')
   drop table mbinvoice
endif

if not file('mbinvoice.dbf')
   create table mbinvoice  (id autoinc,Inv_no character(8),inv_date date,;
     cust_id integer)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("104",'01/22/2016',1.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("105",'01/23/2016',1.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("106",'01/23/2016',3.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("107",'01/23/2016',5.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("108",'02/01/2016',3.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("109",'02/01/2016',2.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("110",'02/01/2016',1.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("111",'02/02/2016',2.00)
   insert into mbinvoice  (Inv_no,inv_date,cust_id) values ("112",'02/03/2016',1.00)
endif

if file('mborderitems.dbf')
   drop table mborderitems
endif


if not file('mborderitems.dbf')
   create table mborderitems  (id autoinc,inv_no character(8),item character(15),;
     qty numeric(10,2),price numeric(10,2))
   insert into mborderitems  (inv_no,item,qty,price) values ("104","Widget 1",1.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("104","Widget 2",4.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("104","Widget 3",2.00,112.00)
   insert into mborderitems  (inv_no,item,qty,price) values ("105","Widget 4",1.00,22.00)
   insert into mborderitems  (inv_no,item,qty,price) values ("105","Widget 1",4.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("106","Widget 2",2.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("107","Widget 4",3.00,22.00)
   insert into mborderitems  (inv_no,item,qty,price) values ("107","Widget 1",3.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("108","Widget 1",1.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("108","Widget 3",6.00,112.00)
   insert into mborderitems  (inv_no,item,qty,price) values ("108","Widget 2",2.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("108","Widget 4",7.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("109","Widget 1",20.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("110","Widget 2",2.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("111","Widget 2",7.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("111","Widget 4",2.00,12.34)
	insert into mborderitems  (inv_no,item,qty,price) values ("112","Widget 1",1.00,12.34)
   insert into mborderitems  (inv_no,item,qty,price) values ("112","Widget 2",4.00,32.22)
   insert into mborderitems  (inv_no,item,qty,price) values ("112","Widget 3",2.00,112.00)
   insert into mborderitems  (inv_no,item,qty,price) values ("112","Widget 4",1.00,22.00)
	
	
endif
/*
  
     The designer streams out sql string as one long line which can make it hard to follow.  
	  Copies here for easy reference

       mbinvoice1
		 
       sql = "select i.cust_id,i.inv_date,o.inv_no,sum(o.price * o.qty) as Cost from mborderitems o "
		 sql +="inner join mbinvoice i "
		 sql +="on o.inv_no = i.Inv_no and i.cust_id = :id "
		 sql +="group by i.cust_id,i.inv_date,o.inv_no "
		 
		 Note that normally   cust_id = :id would be in a WHERE clause to link to the 
		 masterSource rowset.  In this case it is in the ON clause as there is a Join
		 in the SQL statement.
		
		
        mbcustomers1
		 
			sql = 'select c.id,c.Name,c.city,Sum(o.price * o.qty) As Total '
			sql +='from mborderitems o '
			sql +='full outer join mbinvoice i On o.inv_no = i.Inv_no '
			sql +='full outer join mbcustomers c On i.cust_id = c.id  '
			sql +='group By c.id,c.Name,c.city '



*/

** END HEADER -- do not remove this line
//
// Generated on 2016/03/26
//
parameter bModal
local f
f = new masterrowset2Form()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class masterrowset2Form of FORM
   with (this)
      height = 35.1364
      left = 26.5714
      top = -0.2727
      width = 141.8571
      text = ""
   endwith

   this.MBCUSTOMERS1 = new QUERY(this)
   with (this.MBCUSTOMERS1)
      left = 63.0
      sql = 'select c.id,c.Name,c.city,Sum(o.price * o.qty) As Total '
      sql +='from mborderitems o '
      sql +='full outer join mbinvoice i On o.inv_no = i.Inv_no '
      sql +='full outer join mbcustomers c On i.cust_id = c.id  '
      sql +='group By c.id,c.Name,c.city '
      active = true
   endwith

   this.MBINVOICE1 = new QUERY(this)
   with (this.MBINVOICE1)
      left = 76.0
      sql = "select i.cust_id,i.inv_date,o.inv_no,sum(o.price * o.qty) as Cost from mborderitems o inner join mbinvoice i On o.inv_no = i.Inv_no and i.cust_id = :id group by i.cust_id,i.inv_date,o.inv_no "
      params["id"] = ""
      masterSource = form.mbcustomers1.rowset
      active = true
   endwith

   this.MBORDERITEMS1 = new QUERY(this)
   with (this.MBORDERITEMS1)
      left = 86.0
      sql = "select o.*,qty*price as Cost from mborderitems o where inv_no = :inv_no"
      params["inv_no"] = ""
      masterSource = form.mbinvoice1.rowset
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.mbcustomers1.rowset
      height = 7.0
      left = 7.0
      top = 3.0
      width = 93.0
   endwith

   this.GRID2 = new GRID(this)
   with (this.GRID2)
      dataLink = form.mbinvoice1.rowset
      height = 6.0
      left = 7.0
      top = 14.5
      width = 84.0
   endwith

   this.GRID3 = new GRID(this)
   with (this.GRID3)
      dataLink = form.mborderitems1.rowset
      height = 7.5
      left = 7.0
      top = 24.0
      width = 133.0
   endwith

   this.TEXTLABEL1 = new TEXTLABEL(this)
   with (this.TEXTLABEL1)
      height = 1.0
      left = 7.0
      top = 22.0
      width = 58.0
      text = "Items for selected Invoice"
   endwith

   this.TEXTLABEL2 = new TEXTLABEL(this)
   with (this.TEXTLABEL2)
      height = 1.0
      left = 7.0
      top = 12.0
      width = 75.0
      text = "Invoices for selected Customer.  Click on Invoice to see its items."
   endwith

   this.TEXTLABEL3 = new TEXTLABEL(this)
   with (this.TEXTLABEL3)
      height = 1.0
      left = 7.0
      top = 1.0
      width = 69.0
      text = "Select Customer to see the invoices for the customer"
   endwith

   this.rowset = this.mbcustomers1.rowset

endclass