Filter Date Column by days left

tomrich

I have implemented the status and supplier filters but need to extend the date range filter.

What I would like to have is a filter under the Due column with a drop down box that includes the selections 'Due Tomorrow' and 'Overdue'

If you choose 'Tomorrow' it should only show records that are due in 1 day time
If you choose 'Overdue' it should only show records that are due today or the date has past the due date (duedateID)

http://labdocs.co.uk (u=admin p=admin)

Could anyone point me in the right direction to somehow calculate the difference between today and the due date and implement into the filter accordingly?

tomrich

So far I've managed to implement the following that at-least allows me to view all orders that are due today with a filter with friendly names

[code]
{ type: "select", values:
[{ "value" : dateString, "label" : "Today (Working)" },
{ "value" : duetomorrowcode, "label" : "Tomorrow" },
{ "value" : overduecode, "label" : "Overdue" }
]},[/code]

In the above example I'm hoping I can simulate the date range function for duetomorrowcode and overduecode

tomrich

I've updated my code so I can get all records that are due today and are due tomorrow.

[code]
{
type: "select",
values: [{
"value": dateString,
"label": "Today (Working)"
}, {
"value": duetomorrowcode,
"label": "Tomorrow"
}, {
"value": "",
"label": "Overdue"
}]
},
[/code]

To get the correct date format I've used the moment.js plugin which allows me to easily deduct or add as many days as I need to get a single date.

[code]
var TodaysDateFilter = moment().format("DD/MM/YY");

var Tommorrow = moment().add('days', 1);
var TomorrowsDateFilter = Tommorrow.format("DD/MM/YY");

[/code]



All I need to now is to get the overdue dates working. I'm still not sure how I can include a range of dates as a value but my idea was to show all documents that have a due date of yesterday or less. Or even with a due date that's within a range of 360 days old - which ever is easier.

allan

Hi,

Are you using a custom filtering plug-in, such as that here: http://datatables.net/release-datatables/examples/plug-ins/range_filtering.html ? I think you will need to, if you aren't, since although today and tomorrow can simply be string matched, dates in the past cannot be. It would need the date to be calculated into a format such as a unix timestamp (presumably that want moment.js's format method is doing?).

If possible, I would recommend using just a single plug-in filter to do all of your filtering for today, tomorrow and in the past, to keep things simple!

One other possible option is that you could have the server render an additional column which could be used to do simple string matching filtering (i.e. the built in filter in DataTables). The server would put a 1 in the column for today, 2 for tomorrow and 3 for the past (for example), then just use fnFilter to filter by the required type.

Regards,
Allan

tomrich

[quote] One other possible option is that you could have the server render an additional column which could be used to do simple string matching filtering (i.e. the built in filter in DataTables). The server would put a 1 in the column for today, 2 for tomorrow and 3 for the past [/quote]


How would I populate the extra column with the values and how would they update when ii's a new day

allan

To populate the extra column you'd need to add the 1,2,3 calculated data in your server-side script Are you using the Editor PHP libraries? If so, you could use the `data()` method ( https://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_data ) to get the data from the table and loop over it, calculating the extra data as needed.

> how would they update when ii's a new day

Reload the data in the table (a setInterval to check for a new day perhaps) I think. It would be smoother to use a plug-in filtering method in this regard, but it might also be a little more complicated.

Regards,
Allan

tomrich

Thanks for your help.

It's a little over my head and I've got a sneaky suspicion I'm going to struggle
but I'll have a look.

allan

The PHP in this example might help a bit: https://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html . If you look near the end of the PHP tab, you'll see how `data()` is used to get the data and them add extra information. In this case it adds extra properties to the top level object, but in your case you would want to loop over the `aaData` property and calculate the information needed for each array entry.

Allan