New component almost works

[madtowng]

Hi everyone,

I'm apparently a glutton for punishment.
I converted a component that mostly used xbasic to one that
uses only javascript (it does not even use a database).

From what I understand about console.log, it's functioning without errors,
yet it fails to display a populated viewbox.

I am sure this is something stupid I've done, but I can't for the life of me
figure it out.

The code is below (with irrelevant comment lines removed) and the component
is attached.

For me, this is a practical experiment working with javascript dates and of course,
the viewbox control.

Any help is greatly appreciated.

Gregg

function processChosenDate(gsDate){
    var chosenDate = new Date(gsDate);
    console.log('chosen date = ' + chosenDate);
    var refDate = new Date('10/13/2012');
    var refMS = refDate.getTime()
    //var chosenDate = new Date('12/23/2022');
    var write = document.getElementById('selected');
    var chosenDateMS = new Date(chosenDate).setHours(0,0,0,0);
    var aDay = 1000*60*60*24;
    var aPayperiod = aDay * 14;
    var pdiSpan = Math.floor((chosenDate-refDate)/aPayperiod )
    var pdiBeg = new Date(pdiSpan * aPayperiod + refMS)
    var pdiBegMS = pdiBeg.getTime();
    var pdiFin = new Date(pdiBegMS + (aDay*13) )
    var pdiPaydate = new Date(pdiBegMS + (aDay*30) )
    var pdiLastPaydate =  new Date(pdiBegMS + (aDay*2) )
    var pdiLastBeg = new Date(pdiBegMS - (aPayperiod*2) )
    var pdiLastFin = new Date(pdiBegMS - (aDay*15) )
    var pdiNextPaydate = new Date(pdiBegMS + (aDay*16) )
    var pdiNextBeg = new Date(pdiBegMS - (aPayperiod) )
    var pdiNextFin = new Date(pdiBegMS - (aDay) )
    var pdData = []
        console.log('pdiPaydate to ISOString : ' + pdiPaydate.toISOString().substring(0,10));

    var dataRow = '{"category":"projected",' + '"paydate":"' +pdiPaydate.toISOString().substring(0,10)+'",'+'"periodbeg":"'+pdiBeg.toISOString().substring(0,10)+'",'+ '"periodfin":"'+pdiFin.toISOString().substring(0,10)+'"}'
        pdData.push(dataRow);
        dataRow = '{"category":"last",' + '"paydate":"' +pdiLastPaydate.toISOString().substring(0,10)+'",'+'"periodbeg":"'+pdiLastBeg.toISOString().substring(0,10)+'",'+ '"periodfin":"'+pdiLastFin.toISOString().substring(0,10)+'"}'
        pdData.push(dataRow);
        dataRow = '{"category":"next",' + '"paydate":"' +pdiNextPaydate.toISOString().substring(0,10)+'",'+'"periodbeg":"'+pdiNextBeg.toISOString().substring(0,10)+'",'+ '"periodfin":"'+pdiNextFin.toISOString().substring(0,10)+'"}'
        pdData.push(dataRow);
        console.log('pdData = ' + pdData);
        console.log('data type : ' + typeof(pdData));
    var vbPTR = {dialog.Object}.getControl('resultsVB');
        vbPTR.populate(pdData,false);
        console.log(vbPTR.data)
}

​

[peteconway]

Gregg your going to have to clean up this question - then say what is the VB supposed to be doing.

anyhow use alert() to see what's happening if you want to go down tis path.. like this

//alert('at - processChosenDate(gsDate)');
var chosenDate = new Date(gsDate);
alert('chosen date = ' + chosenDate);
var refDate = new Date('10/13/2012');
var refMS = refDate.getTime()
//var chosenDate = new Date('12/23/2022');
var write = document.getElementById('selected');
var chosenDateMS = new Date(chosenDate).setHours(0,0,0,0);
var aDay = 1000*60*60*24;
var aPayperiod = aDay * 14;
var pdiSpan = Math.floor((chosenDate-refDate)/aPayperiod )
var pdiBeg = new Date(pdiSpan * aPayperiod + refMS)
var pdiBegMS = pdiBeg.getTime();
var pdiFin = new Date(pdiBegMS + (aDay*13) )
var pdiPaydate = new Date(pdiBegMS + (aDay*30) )
var pdiLastPaydate = new Date(pdiBegMS + (aDay*2) )
var pdiLastBeg = new Date(pdiBegMS - (aPayperiod*2) )
var pdiLastFin = new Date(pdiBegMS - (aDay*15) )
var pdiNextPaydate = new Date(pdiBegMS + (aDay*16) )
var pdiNextBeg = new Date(pdiBegMS - (aPayperiod) )
var pdiNextFin = new Date(pdiBegMS - (aDay) )
var pdData = []
alert('pdiPaydate to ISOString : ' + pdiPaydate.toISOString().substring(0,10));

Pete - going to bed.

[madtowng]

Hopefully this brief video does a better job of showing what I'm trying to accomplish and what I'm running into.
https://www.screencast.com/t/Mxzdou5z
I'm also including the most recent version of the component.


EVL20console.logLines.png

[madtowng]

I got it figured out.
After the array is complete, it needs to be converted to a JSON object using the code below.

Now I can apply some css to make it easy to read, and get rid of all those other controls.

Capture.png

        /* Convert the array to a JSON object */
        pdData = JSON.parse('[' + pdData + ']');
        /* pdData is now a JSON object */​

[Lance Gurd]

Well done Gregg,

I was trying all sorts but normal life got in the way

Very interesting what you are working on

[peteconway]

Good work - now you will have full control over what you develop - AWESOME.

[peteconway]

I just watched your video - nice - I bet that's when you could see the JSON issue. The fundamentals you have learned will free you from Grids and Lists and give you control. Also some advice - don't waste time on the HTML type of Viewbox - a lot of effort- little gain and its weird, I treat everything as data source. ever if it's just [ ] to get it going.

Proud of you for hanging in there, good things are hard learned, and easy to remember.

Pete

[madtowng]

Thanks Pete.
The JSON looked good to me, but I got the idea to use the JSON to populate
another viewbox. As soon as I tried to look at the fields I got an error message.
The error somehow lead me to JSON.parse().

The final result looks a lot better to me.
One of these days I'll learn how to create group breaks in a viewbox display.

2022-12-20_22-58-03.png

Untitled.png

[Lance Gurd]

Hi Gregg,

My message I posted after you managed to get it working is still sitting there unapproved! But well done!

Creating group breaks in viewboxes is pretty easy, you just need to get your head around nested SQL queries to make hierarchical data. There is an example viewbox available which shows the layout of grouped data available when you first make a new viewbox. On the Home tab, hit Load Sample Viewbox link, then choose the Advanced: List of orders for a customer.... sample and then go to the layout tab, there is an xBasic bit that you need to cut out of the layout and put into an xbasic function, but the rest is pretty easy and gives a great looking result with some nice functionality. I have zipped up one I made earlier

GreggVB.zip

[madtowng]

Hi Lance,

Good to see you can get something past the censors.
My goal is to use the rowheader column as the group break,

Do I have the general idea of nested sql ?

EVL21sqlOutput.png
EVL21sqlCode.png

[Lance Gurd]

Hi Gregg,

That is some pretty fancy looking SQL, but I think it is just returning a flat resultset. Have a look at the attached web component. The data source tab is based on a nested database query, these can be many tables deep or just based on one table with each nest pulling in data. I have found that if it gets to a lot of nests with a lot of data it can be slow but there are ways around this. If you open the nested SQL query builder you can see the table structure I have added. This is a very basic link between the customers and orders tables in alphasports (you may need to alter the connection string to see it), the two queries are linked on the customerID field, Highlight the second query to see this. There is a preview query button at the bottom of the builder, this shows the nested JSON that is required to build group break templates.

The layout tab shows the use of the genie to set out group breaks. To add some nice header actions, like show/hide of child records and getting summary values from child records etc you will need to swap to the freeform editor on the layout tab

This took a couple of minutes to throw together, if you want me to make it have more levels and interactive let me know

nested example.zip

[madtowng]

Hi Lance,
I'm gonna start with Merry Christmas.
I meant to respond sooner but have been feeling a bit under the weather.

After battling with the concept of nested sql (I think it's another poorly chosen name) for a few days, I finally
decided to see if there was a genie for it, and sure enough, there is.

I don't like to blindly trust the genies (I find it's a help to know what's going on).

I was able to modify the xbasic below to my preference when then produced the JSON below that.

I found an old video where Selwyn demonstrates templating hierarchal data using the template tester https://www.viddler.com/v/4f5b3e82 .
I'm going to watch it a few times hoping I begin to understand things.

dim ops as p
dim ops.sql as c
dim args as sql::arguments
dim json as c
dim errorText as c
dim p as p

ops.connectionString = "::Name::Connection1"

ops.json = .t.
ops.crc = ""
ops.sparse = .F.
ops.flagUseOptimizedQueryIfPossible = .t. 'Applies only to SQL Server Release 2016 and greater
ops.convertNumbersToStrings = .f. 'Applies only to SQL Server Release 2016 and greater
'define the nested sql queries and query properties. each level of indentation denotes a parent-child relationship
ops.sql = <<%xstr%
{sql: 'SELECT distinct rowheader as eventHeader  FROM eventview2    ORDER BY sortcol',name: 'Top',sqlSecurityJSON: ''}
    {sql: 'SELECT eTime, DIR, ListID, Status, findIN,rowheader,sortcol  FROM eventview2    ORDER BY sortcol, eTime, DIR, ListID',name: 'eventData',parentKey: 'rowheader',key: 'rowheader',sqlSecurityJSON: ''}
%xstr%

debug(1)
'execute the definition to get the JSON data
p = a5_sql_nested_query_to_json_document(ops,args)
if p.hasError = .f. then

    json = p.json

else
    'there was an error

    errorText = p.errortext
end if

debug(1)
​​

[
        {
            "eventHeader": "TUESDAY 2022-12-27",
            "eventData": [
                {
                    "eTime": "09:00",
                    "DIR": "OUT",
                    "ListID": "EVL21-5",
                    "Status": "READY",
                    "findIN": null,
                    "rowheader": "TUESDAY 2022-12-27",
                    "sortcol": "2022-12-27"
                },
                {
                    "eTime": "11:56",
                    "DIR": "IN",
                    "ListID": "EVL27-5",
                    "Status": "WAITING",
                    "findIN": "",
                    "rowheader": "TUESDAY 2022-12-27",
                    "sortcol": "2022-12-27"
                }
            ]
        },
        {
            "eventHeader": "WEDNESDAY 2022-12-28",
            "eventData": [
                {
                    "eTime": "09:00",
                    "DIR": "OUT",
                    "ListID": "EVL20-5B",
                    "Status": "FRESH",
                    "findIN": null,
                    "rowheader": "WEDNESDAY 2022-12-28",
                    "sortcol": "2022-12-28"
                }
            ]
        },
        {
            "eventHeader": "TUESDAY 2023-01-03",
            "eventData": [
                {
                    "eTime": "09:00",
                    "DIR": "OUT",
                    "ListID": "EVL22-14",
                    "Status": "FRESH",
                    "findIN": null,
                    "rowheader": "TUESDAY 2023-01-03",
                    "sortcol": "2023-01-03"
                }
            ]
        },
        {
            "eventHeader": "WEDNESDAY 2023-01-04",
            "eventData": [
                {
                    "eTime": "09:00",
                    "DIR": "OUT",
                    "ListID": "EVL20-5C",
                    "Status": "FRESH",
                    "findIN": null,
                    "rowheader": "WEDNESDAY 2023-01-04",
                    "sortcol": "2023-01-04"
                }
            ]
        }
    ]​

[Lance Gurd]

Merry Christmas Gregg and all.

This is great work, well done