Subject Re: EQ of XBase TOTAL Command
From Mustansir Ghor <mustan31@hotmail.com>
Date Wed, 09 Aug 2017 14:50:45 -0400
Newsgroups dbase.getting-started

Dear Mervyn

Thank you. An eye opener for a programmer like me. Great programming. Three issues that has come as curiosity.

1. In the tabbar when report is on screen. if we click any of the pushbutton it does not respond until report is closed. How this is controlled.

2. In the new query in report file , the database = form.form.database1. Why there are 2 form.form
3. In the sql string there is group by  p.description,oi.partID. But these two columns represent same information. if it were because of sorting then why not use  order by.

Thanks once again.

May be this is the right for me to request to you to enlighten us to develop menubar to be used with _app.framewin. In earlier correspondence Ken sir established that code used in his tutorial does not work and crashes.

Best Regards
Mustansir

Mervyn Bick Wrote:

> On 2017-08-08 2:04 PM, Mustansir Ghor wrote:
> > Dear Mervyn
> >
> > Thank you. This was answer to Q.4. that assuming 1-3 has been done and resulting file (rowset) is orderitems. Lets consider a report that we need to know parts shipped in a period.
> >
> > 1. A form where shipped from and upto dates were to be input.
> >
> > 2. The above two variables can be considered as params for a query whose resulting rowset to be used in printing. Because we need summary report of parts(TOTAL ON parts) the query type can be UPDATE (but I dont SQL command). Here we also want to know for each part how many orders we had.
> >
> > 3. PartID can then be reference using lookupSQL to get description of part.
> >
> > Can we incorporate all of above in the same rep file. Or how do we go about.
>
> By JOINing three tables we can create a virtual rowset which will give
> you a list of part numbers, description, quantity sold and the number of
> orders for each part number.   You can have the list sorted numerically
> or alphabetically.
>
> Mervyn.
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 2017-08-08
> //
> parameter bModal
> local f
> f = new ordersForm()
> if (bModal)
>    f.mdi = false // ensure not MDI
>    f.readModal()
> else
>    f.open()
> endif
>
> class ordersForm of FORM
>    with (this)
>       onOpen = class::FORM_ONOPEN
>       onClose = class::FORM_ONCLOSE
>       height = 16.0
>       left = 75.7143
>       top = 5.2273
>       width = 40.0
>       text = ""
>    endwith
>
>    this.ENTRYFIELD1 = new ENTRYFIELD(this)
>    with (this.ENTRYFIELD1)
>       height = 1.0
>       left = 18.0
>       top = 4.0
>       width = 11.0
>       value = {2001-01-01}
>    endwith
>
>    this.ENTRYFIELD2 = new ENTRYFIELD(this)
>    with (this.ENTRYFIELD2)
>       height = 1.0
>       left = 18.0
>       top = 7.0
>       width = 11.0
>       value = {2001-12-31}
>    endwith
>
>    this.PUSHBUTTON1 = new PUSHBUTTON(this)
>    with (this.PUSHBUTTON1)
>       onClick = class::PUSHBUTTON1_ONCLICK
>       height = 1.0909
>       left = 10.0
>       top = 10.0
>       width = 19.0
>       text = "Report - alphabetic"
>    endwith
>
>    this.TEXTLABEL1 = new TEXTLABEL(this)
>    with (this.TEXTLABEL1)
>       height = 1.0
>       left = 4.0
>       top = 4.0
>       width = 12.0
>       text = "Start date"
>    endwith
>
>    this.TEXTLABEL2 = new TEXTLABEL(this)
>    with (this.TEXTLABEL2)
>       height = 1.0
>       left = 4.0
>       top = 7.0
>       width = 12.0
>       text = "End date"
>    endwith
>
>    this.PUSHBUTTON2 = new PUSHBUTTON(this)
>    with (this.PUSHBUTTON2)
>       onClick = class::PUSHBUTTON2_ONCLICK
>       height = 1.0909
>       left = 10.0
>       top = 12.5
>       width = 19.0
>       text = "Report - numerical"
>    endwith
>
>    this.TEXT1 = new TEXT(this)
>    with (this.TEXT1)
>       height = 2.5
>       left = 7.0
>       top = 0.5
>       width = 28.0
>       text = "Select dates between 1999-11-29 and 2004-04-08"
>    endwith
>
>
>    function PUSHBUTTON1_onClick()
>       form.oRep.text1.text = 'Parts sold from '+dtoc(form.entryfield1.value)+' to '+dtoc(form.entryfield2.value)
>       form.oRep.text2.text = 'Alhabetic'
>       if form.order = 'a'
>          form.oRep.orders1.params['sdate'] = form.entryfield1.value
>          form.oRep.orders1.params['edate'] = form.entryfield2.value
>          form.oRep.orders1.requery()
>        else
>          form.oRep.orders1.active = false
>          form.oRep.orders1.sql = form.sql_a
>          form.oRep.orders1.params['sdate'] = form.entryfield1.value
>          form.oRep.orders1.params['edate'] = form.entryfield2.value
>          form.oRep.orders1.active = true
>          form.order = 'a'
>       endif  
>       form.oRep.render()
>       return
>
>    function PUSHBUTTON2_onClick()
>      form.oRep.text2.text = 'Numeric'
>      form.oRep.text1.text = 'Parts sold from '+dtoc(form.entryfield1.value)+' to '+dtoc(form.entryfield2.value)
>       if form.order = 'n'
>          form.oRep.orders1.params['sdate'] = form.entryfield1.value
>          form.oRep.orders1.params['edate'] = form.entryfield2.value
>          form.oRep.orders1.requery()
>        else
>          form.oRep.orders1.active = false
>          form.oRep.orders1.sql = form.sql_n
>          form.oRep.orders1.params['sdate'] = form.entryfield1.value
>          form.oRep.orders1.params['edate'] = form.entryfield2.value
>          form.oRep.orders1.active = true
>          form.order = 'n'
>       endif  
>       form.oRep.render()      
>       return
>
>    function form_onClose()
>       close procedure orders.rep
>       return
>
>    function form_onOpen()
>       set procedure to orders.rep
>       form.oRep = new ordersreport()
>       form.order = 'a'
>      
>       form.sql_a = 'select cast(sum(oi.quantity) as int) as number_sold,count(oi.orderID) as orders,'
>       form.sql_a +='p.description,oi.partID from orders o '
>       form.sql_a +='inner join orderitems oi on o.orderID = oi.OrderID '
>       form.sql_a +='inner join parts p on oi.partID = p.partID '
>       form.sql_a +='where o.orderDate between :sdate and :edate '
>       form.sql_a +='group by p.description,oi.partID      '
>        
>       form.sql_n = 'select cast(sum(oi.quantity) as int) as number_sold,count(oi.orderID) as orders,'
>       form.sql_n +='p.description,oi.partID from orders o '
>       form.sql_n +='inner join orderitems oi on o.orderID = oi.OrderID '
>       form.sql_n +='inner join parts p on oi.partID = p.partID '
>       form.sql_n +='where o.orderDate between :sdate and :edate '
>       form.sql_n +='group by oi.partID, parts.description'
>       return
>
> endclass
>
> /*
>
>   The report designer will save te sql string in one long line. This copy for easy reference.
>
>       sql = 'select cast(sum(oi.quantity) as int) as number_sold,count(oi.orderID) as orders,'
>       sql +='p.description,oi.partID from orders o '
>       sql +='inner join orderitems oi on o.orderID = oi.OrderID '
>       sql +='inner join parts p on oi.partID = p.partID '
>       sql +='where o.orderDate between :sdate and :edate '
>       sql +='group by p.description,oi.partID '  
> */
> ** END HEADER -- do not remove this line
> //
> // Generated on 2017-08-08
> //
> local r
> r = new ordersReport()
> r.render()
>
> class ordersReport of REPORT
>    with (this)
>       autoSort = false
>    endwith
>
>    this.DBASESAMPLES1 = new DATABASE(this)
>    with (this.DBASESAMPLES1)
>       left = 1155.0
>       top = 135.0
>       width = 360.0
>       height = 360.0
>       databaseName = "DBASESAMPLES"
>       active = true
>    endwith
>
>    this.ORDERS1 = new QUERY(this)
>    with (this.ORDERS1)
>       left = 360.0
>       top = 105.0
>       width = 360.0
>       height = 360.0
>       database = form.form.dbasesamples1
>       sql = 'select cast(sum(oi.quantity) as int) as number_sold,count(oi.orderID) as orders,'
>       sql +='p.description,oi.partID from orders o '
>       sql +='inner join orderitems oi on o.orderID = oi.OrderID '
>       sql +='inner join parts p on oi.partID = p.partID '
>       sql +='where o.orderDate between :sdate and :edate '
>       sql +='group by p.description,oi.partID '  
>       params['sdate'] = {}
>       params['edate'] = {}
>       requestLive = false
>       active = true
>    endwith
>
>    this.PAGETEMPLATE1 = new PAGETEMPLATE(this)
>    with (this.PAGETEMPLATE1)
>       height = 16837.0
>       width = 11905.0
>       marginTop = 1095.0
>       marginLeft = 1110.0
>       marginBottom = 1065.0
>       marginRight = 1050.0
>       gridLineWidth = 0
>    endwith
>
>    this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
>    with (this.PAGETEMPLATE1.STREAMFRAME1)
>       height = 11592.0
>       left = 360.0
>       top = 1365.0
>       width = 9360.0
>       form.STREAMFRAME1 = form.pagetemplate1.streamframe1
>    endwith
>
>    this.PAGETEMPLATE1.TEXT1 = new TEXT(this.PAGETEMPLATE1)
>    with (this.PAGETEMPLATE1.TEXT1)
>       height = 645.0
>       left = 1815.0
>       top = 405.0
>       width = 6345.0
>       prefixEnable = false
>       fontSize = 14.0
>       text = "Parts sold between mm/dd/yyyy and mm/ddyyyy"
>       form.TEXT1 = form.pagetemplate1.text1
>    endwith
>
>    this.PAGETEMPLATE1.TEXT2 = new TEXT(this.PAGETEMPLATE1)
>    with (this.PAGETEMPLATE1.TEXT2)
>       height = 300.0
>       left = 300.0
>       top = 180.0
>       width = 1080.0
>       prefixEnable = false
>       text = "Alphabetic"
>       form.TEXT2 = form.pagetemplate1.text2
>    endwith
>
>    this.STREAMSOURCE1 = new STREAMSOURCE(this)
>    with (this.STREAMSOURCE1.detailBand)
>       height = 250.0
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TITLETEXTPARTID1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TITLETEXTPARTID1)
>       canRender = {||this.parent.firstOnFrame}
>       height = 300.0
>       left = 225.0
>       top = -10.0
>       width = 1170.0
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       suppressIfBlank = true
>       text = "<H3>Part no</H3>"
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TEXTPARTID1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TEXTPARTID1)
>       height = 293.0
>       left = 225.0
>       top = 298.0
>       width = 1170.0
>       variableHeight = true
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       text = {||this.form.orders1.rowset.fields["partid"].value}
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TITLETEXTDESCRIPTION1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TITLETEXTDESCRIPTION1)
>       canRender = {||this.parent.firstOnFrame}
>       height = 300.0
>       left = 1830.0
>       top = -10.0
>       width = 2880.0
>       prefixEnable = false
>       suppressIfBlank = true
>       text = "<H3>Description</H3>"
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TEXTDESCRIPTION1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TEXTDESCRIPTION1)
>       height = 293.0
>       left = 1830.0
>       top = 298.0
>       width = 2880.0
>       variableHeight = true
>       prefixEnable = false
>       text = {||this.form.orders1.rowset.fields["description"].value}
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TITLETEXTNUMBER_SOLD1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TITLETEXTNUMBER_SOLD1)
>       canRender = {||this.parent.firstOnFrame}
>       height = 300.0
>       left = 5295.0
>       top = -10.0
>       width = 1485.0
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       suppressIfBlank = true
>       text = "<H3>Number Sold</H3>"
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TEXTNUMBER_SOLD1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TEXTNUMBER_SOLD1)
>       height = 293.0
>       left = 5295.0
>       top = 298.0
>       width = 1485.0
>       variableHeight = true
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       text = {||this.form.orders1.rowset.fields["number_sold"].value}
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TITLETEXTORDERS1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TITLETEXTORDERS1)
>       canRender = {||this.parent.firstOnFrame}
>       height = 300.0
>       left = 7350.0
>       top = -10.0
>       width = 1170.0
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       suppressIfBlank = true
>       text = "<H3>Orders</H3>"
>    endwith
>
>    this.STREAMSOURCE1.detailBand.TEXTORDERS1 = new TEXT(this.STREAMSOURCE1.detailBand)
>    with (this.STREAMSOURCE1.detailBand.TEXTORDERS1)
>       height = 293.0
>       left = 7350.0
>       top = 298.0
>       width = 1170.0
>       variableHeight = true
>       prefixEnable = false
>       alignHorizontal = 2        // Right
>       text = {||this.form.orders1.rowset.fields["orders"].value}
>    endwith
>
>    with (this.printer)
>       duplex = 1        // None
>       orientation = 1        // Portrait
>       paperSource = 7
>       paperSize = 9
>       resolution = 3        // Medium
>       color = 2        // Color
>       trueTypeFonts = 1        // Bitmap
>    endwith
>
>    with (this.reportGroup.footerBand)
>       height = 250.0
>    endwith
>
>    with (this.reportGroup.headerBand)
>       height = 250.0
>    endwith
>
>    this.firstPageTemplate = this.form.pagetemplate1
>    this.form.pagetemplate1.nextPageTemplate = this.form.pagetemplate1
>    this.form.pagetemplate1.streamframe1.streamSource = this.form.streamsource1
>    this.form.streamsource1.rowset = this.form.orders1.rowset
>
> endclass
>