Problem with custom aggregate for date types

[TeeJT]

I have made a custom aggregate function which accepts a 'typeList' e.g. [ "min", "max" ] to display both min and max in the summary. It can be used with "count", "min", "max", "avg" and "sum" and this works well with numeric fields but not with date fields.

I used your demo at https://paramquery.com/pro/demos/group_rows
This is the Javascript:

Code: [Select]


    $(function () {
var agg = pq.aggregate;
        agg.count1 = function(arr, col) {
return "Cnt: " + agg.count(arr, col);
}

function myAgg(arr, col, fnName) {
var ret = "";
var res = fnName.split("_");
for(var i=0; i<res.length; i++) {
txt = "";
if (i>0) {txt = "<BR>";}
var fn = res[i];
switch (fn) {
case "avg":
txt += "Avg: " + pq.formatNumber( agg.avg(arr, col), col.format);
break;
case "max":
txt += "Max: " + pq.formatNumber( agg.max(arr, col), col.format);
break;
case "min":
txt += "Min: " + pq.formatNumber( agg.min(arr, col), col.format);
break;
case "sum":
txt += "Sum: " + pq.formatNumber( agg.sum(arr, col), col.format);
break;
case "count":
txt += "Cnt: " + agg.count(arr, col);
break;
}
ret += txt;
}
return ret;
}
function createAggFunc(typeTxt) {
var funcName = "Func_" + typeTxt;
window[funcName] = function f(arr, col) {
return myAgg(arr, col, typeTxt);
}
return window[funcName];
}

        function fillOptions(grid) {
            var column = grid.getColumn({ dataIndx: 'ShipCountry' });
            column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
            column.filter.cache = null;
            grid.refreshHeader();
        }
        var colM = [
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
                filter: {
                    type: 'select',
                    prepend: { '': 'All Countries' },
                    valueIndx: 'ShipCountry',
                    labelIndx: 'ShipCountry',
                    condition: 'equal',
                    listeners: ['change']
                }
            },
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
            { title: "Freight", width: 120, format: '$##,###.00',
                summary: {
                    "typeList": ["count", "sum", "avg"]
                },
                dataType: "float", dataIndx: "Freight"
            },
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date", format: 'dd-M-yy',
"summary": {
"typeList": ["max", "min"]
},
},
            { title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
        var dataModel = {
            location: "remote",
            dataType: "JSON",
            method: "GET",
            url: "/Content/orders.json"
            //url: "/pro/orders/get",//for ASP.NET
            //url: "orders.php",//for PHP
        };
        var groupModel = {
            on: true,
            dataIndx: ['ShipCountry'],
            collapsed: [true],
showSummary: [true],
grandSummary: true,
header: true,
            title: [
                "{0} ({1})",
                "{0} - {1}"
            ]
        };
        var obj = {
            height: 500,
            toolbar: {
                items: [{
                    type: 'button',
                    label: "Toggle grouping",
                    listener: function (evt) {
                        this.groupOption({
                            on: !grid.option('groupModel.on')
                        });
                    }
                }]
            },
            dataModel: dataModel,
            scrollModel: { autoFit: true },
            colModel: colM,
            create: function () {
var CM = this.getColModel(),
opts = [];
for (var i = 0; i < CM.length; i++) {
var cData = CM[i];
if (cData.summary) {
var typeList = cData.summary.typeList;
if (typeList) {
cData.summary.edit = true;
var typeTxt = typeList.join("_");
cData.summary.type = typeTxt;
if (!agg[typeTxt]) {
func = createAggFunc(typeTxt);
agg[typeTxt] = func;
}
if (typeTxt == "count") {typeTxt = "count1";}
cData.summary.type = typeTxt;
}
}
if (cData.hidden !== true) {
opts.push(cData.dataIndx);
}
}
            },
complete: function( event, ui ) {
grid = this; //pq.grid(gridDiv);
grid.refreshView();
},
            numberCell: { show: false },
            filterModel: { on: true, header: true, type: "local" },
            selectionModel: { type: 'cell' },
            groupModel: groupModel,
            load: function (evt, ui) {
                //options for ShipCountry filter.   
                fillOptions(grid);
            },
            showTitle: false,
            resizable: true,
            virtualX: true,
            virtualY: true,
            hwrap: false,
            wrap: false
        };
        var grid = pq.grid("#grid_group_rows", obj);

    });

If you notice I put the format of the date as 'dd-M-yy' and the output is dd-M-yy7 (the last digit of the year appears)
If I remove the format of the date column - then the max and min are numeric and not as dates anymore.

I used your function - pq.formatNumber( agg.max(arr, col), col.format)
I noticed the array arr are the formatted dates rather than the dates converted to numeric form to handle for max and min.

What can be done for this?

[paramvir]

pq.formatNumber is for numbers only, not for dates.

$.datepicker.formatDate( column.format, agg.max(arr, column) ) can be used to format the dates.

The data type of the column can be read from column.dataType property, column is passed into the custom aggregate method as 2nd parameter.

if ( column.dataType == "date"){
  //use $.datepicker.formatDate
}
else{
  //use pq.formatNumber
}

Hope it helps.

[TeeJT]

I have modified the code to use $.datepicker.formatDate
But the same result.
I have written before that the array arr are the formatted dates rather than the dates converted to numeric form to handle for max and min.

I used your demo at https://paramquery.com/pro/demos/group_rows
This is the Javascript:

Code: [Select]

    $(function () {
var agg = pq.aggregate;
        agg.count1 = function(arr, col) {
return "Cnt: " + agg.count(arr, col);
}

function myAgg(arr, col, fnName) {
var ret = "";
var res = fnName.split("_");
for(var i=0; i<res.length; i++) {
txt = "";
if (i>0) {txt = "<BR>";}
var fn = res[i];
switch (fn) {
case "avg":
txt += "Avg: " + pq.formatNumber( agg.avg(arr, col), col.format);
break;
case "max":
if ( col.dataType == "date"){
txt += "Min: " + $.datepicker.formatDate( agg.max(arr, col), col.format);
}
else{
txt += "Min: " + pq.formatNumber( agg.max(arr, col), col.format);
}
                        break;
break;
case "min":
if ( col.dataType == "date"){
txt += "Min: " + $.datepicker.formatDate( agg.min(arr, col), col.format);
}
else{
txt += "Min: " + pq.formatNumber( agg.min(arr, col), col.format);
}
                        break;
break;
case "sum":
txt += "Sum: " + pq.formatNumber( agg.sum(arr, col), col.format);
break;
case "count":
txt += "Cnt: " + agg.count(arr, col);
break;
}
ret += txt;
}
return ret;
}
function createAggFunc(typeTxt) {
var funcName = "Func_" + typeTxt;
window[funcName] = function f(arr, col) {
return myAgg(arr, col, typeTxt);
}
return window[funcName];
}

        function fillOptions(grid) {
            var column = grid.getColumn({ dataIndx: 'ShipCountry' });
            column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
            column.filter.cache = null;
            grid.refreshHeader();
        }
        var colM = [
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
                filter: {
                    type: 'select',
                    prepend: { '': 'All Countries' },
                    valueIndx: 'ShipCountry',
                    labelIndx: 'ShipCountry',
                    condition: 'equal',
                    listeners: ['change']
                }
            },
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
            { title: "Freight", width: 120, format: '$##,###.00',
                summary: {
                    "typeList": ["count", "sum", "avg"]
                },
                dataType: "float", dataIndx: "Freight"
            },
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date", format: 'dd-M-yy',
"summary": {
"typeList": ["max", "min"]
},
},
            { title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
        var dataModel = {
            location: "remote",
            dataType: "JSON",
            method: "GET",
            url: "/Content/orders.json"
            //url: "/pro/orders/get",//for ASP.NET
            //url: "orders.php",//for PHP
        };
        var groupModel = {
            on: true,
            dataIndx: ['ShipCountry'],
            collapsed: [true],
showSummary: [true],
grandSummary: true,
header: true,
            title: [
                "{0} ({1})",
                "{0} - {1}"
            ]
        };
        var obj = {
            height: 500,
            toolbar: {
                items: [{
                    type: 'button',
                    label: "Toggle grouping",
                    listener: function (evt) {
                        this.groupOption({
                            on: !grid.option('groupModel.on')
                        });
                    }
                }]
            },
            dataModel: dataModel,
            scrollModel: { autoFit: true },
            colModel: colM,
            create: function () {
var CM = this.getColModel(),
opts = [];
for (var i = 0; i < CM.length; i++) {
var cData = CM[i];
if (cData.summary) {
var typeList = cData.summary.typeList;
if (typeList) {
cData.summary.edit = true;
var typeTxt = typeList.join("_");
cData.summary.type = typeTxt;
if (!agg[typeTxt]) {
func = createAggFunc(typeTxt);
agg[typeTxt] = func;
}
if (typeTxt == "count") {typeTxt = "count1";}
cData.summary.type = typeTxt;
}
}
if (cData.hidden !== true) {
opts.push(cData.dataIndx);
}
}
            },
complete: function( event, ui ) {
grid = this; //pq.grid(gridDiv);
grid.refreshView();
},
            numberCell: { show: false },
            filterModel: { on: true, header: true, type: "local" },
            selectionModel: { type: 'cell' },
            groupModel: groupModel,
            load: function (evt, ui) {
                //options for ShipCountry filter.   
                fillOptions(grid);
            },
            showTitle: false,
            resizable: true,
            virtualX: true,
            virtualY: true,
            hwrap: false,
            wrap: false
        };
        var grid = pq.grid("#grid_group_rows", obj);

    });


[paramvir]

Please use this to format max date in custom aggregate:

$.datepicker.formatDate( col.format, new Date(agg.max(arr, col)) )

Kindly note the order of the parameters and new Date used in 2nd parameter. https://api.jqueryui.com/datepicker/

[TeeJT]

Yes I realized my mistake but I found that when all the dates in a group are empty or null - the Max or Min returns NaN-undefined-NaN
In your sample there are no groups with all the dates as null but my own data has such groups.
So I had to check if the aggregate function returns an invalid date e.g. null.
Now It's working properly.
Thank you very much!

I used your demo at https://paramquery.com/pro/demos/group_rows
This is the Javascript:

Code: [Select]

    $(function () {
var agg = pq.aggregate;
        agg.count1 = function(arr, col) {
return "Cnt: " + agg.count(arr, col);
}

function myAgg(arr, col, fnName) {
var ret = "";
var res = fnName.split("_");
for(var i=0; i<res.length; i++) {
txt = "";
if (i>0) {txt = "<BR>";}
var fn = res[i];
switch (fn) {
case "avg":
txt += "Avg: " + pq.formatNumber( agg.avg(arr, col), col.format);
break;
                    case "max":
if ( col.dataType == "date"){
var val1 = agg.max(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Max: " + $.datepicker.formatDate( col.format, date1);
}
else{
txt += "Max: " + pq.formatNumber( agg.max(arr, col), col.format);
}

                        break;
                    case "min":
if ( col.dataType == "date"){
var val1 = agg.min(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Min: " + $.datepicker.formatDate( col.format, date1);
}
else{
txt += "Min: " + pq.formatNumber( agg.min(arr, col), col.format);
}
                        break;
case "sum":
txt += "Sum: " + pq.formatNumber( agg.sum(arr, col), col.format);
break;
case "count":
txt += "Cnt: " + agg.count(arr, col);
break;
}
ret += txt;
}
return ret;
}
function createAggFunc(typeTxt) {
var funcName = "Func_" + typeTxt;
window[funcName] = function f(arr, col) {
return myAgg(arr, col, typeTxt);
}
return window[funcName];
}

        function fillOptions(grid) {
            var column = grid.getColumn({ dataIndx: 'ShipCountry' });
            column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
            column.filter.cache = null;
            grid.refreshHeader();
        }
        var colM = [
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
                filter: {
                    type: 'select',
                    prepend: { '': 'All Countries' },
                    valueIndx: 'ShipCountry',
                    labelIndx: 'ShipCountry',
                    condition: 'equal',
                    listeners: ['change']
                }
            },
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
            { title: "Freight", width: 120, format: '$##,###.00',
                summary: {
                    "typeList": ["count", "sum", "avg"]
                },
                dataType: "float", dataIndx: "Freight"
            },
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date", format: 'dd-M-yy',
"summary": {
"typeList": ["max", "min"]
},
},
            { title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
        var dataModel = {
            location: "remote",
            dataType: "JSON",
            method: "GET",
            url: "/Content/orders.json"
            //url: "/pro/orders/get",//for ASP.NET
            //url: "orders.php",//for PHP
        };
        var groupModel = {
            on: true,
            dataIndx: ['ShipCountry'],
            collapsed: [true],
showSummary: [true],
grandSummary: true,
header: true,
            title: [
                "{0} ({1})",
                "{0} - {1}"
            ]
        };
        var obj = {
            height: 500,
            toolbar: {
                items: [{
                    type: 'button',
                    label: "Toggle grouping",
                    listener: function (evt) {
                        this.groupOption({
                            on: !grid.option('groupModel.on')
                        });
                    }
                }]
            },
            dataModel: dataModel,
            scrollModel: { autoFit: true },
            colModel: colM,
            create: function () {
var CM = this.getColModel(),
opts = [];
for (var i = 0; i < CM.length; i++) {
var cData = CM[i];
if (cData.summary) {
var typeList = cData.summary.typeList;
if (typeList) {
cData.summary.edit = true;
var typeTxt = typeList.join("_");
cData.summary.type = typeTxt;
if (!agg[typeTxt]) {
func = createAggFunc(typeTxt);
agg[typeTxt] = func;
}
if (typeTxt == "count") {typeTxt = "count1";}
cData.summary.type = typeTxt;
}
}
if (cData.hidden !== true) {
opts.push(cData.dataIndx);
}
}
            },
complete: function( event, ui ) {
grid = this; //pq.grid(gridDiv);
grid.refreshView();
},
            numberCell: { show: false },
            filterModel: { on: true, header: true, type: "local" },
            selectionModel: { type: 'cell' },
            groupModel: groupModel,
            load: function (evt, ui) {
                //options for ShipCountry filter.   
                fillOptions(grid);
            },
            showTitle: false,
            resizable: true,
            virtualX: true,
            virtualY: true,
            hwrap: false,
            wrap: false
        };
        var grid = pq.grid("#grid_group_rows", obj);

    });