Filter values for previous month

[PvN94]

I have a list with records that are linked to a certain month, by means of a dropdown to select a month, and a dropdown to select a year. Now I want the default view of the list to only view the records for the previous month.

So today I want to see all records for november, so the filter should be something like ?month[value][]=11&month[join]=AND&year[value][]=2016&year[join]=AND

I know of URL filtering to select rows with a date in a certain range, but I don't have an actual date here. Now I could make a hidden date field and filter on that, but I was wondering if there's a solution that actually uses the month and year fields.

 

[cheesegrits]

Try ...

&month[value]=11&year[value]=2016&year[join]=AND

The best way to play around with query string filters is to enable Fabrik debug (in our global options), append &fabrikdebug=1, and look at the getData section of the debug output. That'll show you what WHERE clause we're actually generating, which helps with tweaking the inputs.

-- hugh

 

[PvN94]

Thanks, but the issue isn't about the format of the query string, that was just a quick example I typed out.
My question is if there is something I can put in the spots where I now have '11' and '2016', to get the values for the previous month, like in a normal query I would use something like month=MONTH(CURDATE()-INTERVAL 1 MONTH) AND year = YEAR(CURDATE()-INTERVAL 1 MONTH). If i run the query today it returns the rows for 11-2016, but on january 1st it'll return them for 12-2016, etc.

Just to be clear, when I put in MONTH(CURDATE()-INTERVAL 1 MONTH) into the querystring, it doesn't work because there are quotes put around it, and the query searches for the literal value 'MONTH(CURDATE()-INTERVAL 1 MONTH)'

 

[cheesegrits]

Yeah, that's not possible, we're not about to let people submit actual SQL on the query string that we then execute. I'm sure you can imagine why ... kinda suicidal ... month=(DROP TABLE(#__users)) ....

https://xkcd.com/327/

I think the only way round this one will be to do something in a pre-filter.

If you are using a date element, we provide a built in 'last month' pre-filter (although i can't remember if it actually does "last month" as in date in month-1, or as in now - 30 days (or whatever)). But with your separate month and year elements, you'll need to roll your own.

So probably two pre-filters, joined with AND, that do

field: month
condition: =
value: MONTH(CURDATE()-INTERVAL 1 MONTH)
type: no quotes

AND

field: year
condition: =
value: YEAR(CURDATE()-INTERVAL 1 MONTH)
type: no quotes

If you need to be able to turn this on and off with a query string, you'll have to finagle it using an eval'ed PHP filter, although I'd have to think hard on how to actually achieve that.

-- hugh

 

[PvN94]

Yeah I wasn't expecting that to actually work, but when I typed out how I would do it in SQL I thought why not try if it works.

The point is that I want to achieve the effect like in your prefilter, but by filling in the filter fields, so that the user can change it to other months.
I think I might try just filling in the numbers, and writing a cronjob to update the menu link in the database every first of the month. Maybe not the prettiest option, but if it works, it works.

 

[cheesegrits]

Well, you can use query string placeholders in pre-filters.

-- hugh

 

[troester]

Well, you can use query string placeholders in pre-filters.

Oh, nice. I always did it with input->get...

 

[cheesegrits]

Although I STRONGLY recommend you only do it with the 'eval' filter type, as it opens you up to Nasty Things <tm> if you use it in a normal pre-filter. And sanity check the input.

So you would have a pre-filter which does ...

WHERE
field: id
condition: IN
value: some PHP which uses the query string to build a set of id's from your list matching your criteria, like "(1,3,5,7)"
type: eval

-- hugh

 

[cheesegrits]

Oh, nice. I always did it with input->get...

Yeah, it's not a feature I advertize, and am probably going to remove it, or at least only allow it in eval'ed filters. I added it ages ago for a project I was working on and didn't tell anyone, as it kind of opens you up to SQL injection attacks, if you don't properly quote it.

But yeah, technically you can use {foo} in a value and it'll use the foo qs value. Just make DARN sure you don't select "no quotes".

-- hugh

 

[troester]

If the developper doesn't sanitize also Eval won't really help; if you do setQuery("SELECT.... WHERE {foo}") you can also get "Little bobby drop table"

 

[cheesegrits]

Yup. Which is why I don't typically tell anyone about it.

I just figure if someone is writing code to query the database, there is a higher chance they understand that they need to sanitize.

-- hugh

 

[automill]

What I understand from your requirement, you need Month-Year as a drop down for your reports. For faster solution, I suggest you (and what I do) to create two tables:

First table - It will record the data through form with a calc element inserted along other elements. The code is:

$mth_yr = strtotime('{xxxx_your_table1___date_field}');
$new = date('M-Y', $mth_yr);
return $new;

This will calc, convert and record the every data input in "Nov-2016" etc.

Second Table: This will record single line of data (no add button but edit button every time you change your month-year) with all required report fields. Link calc element of the first with databasejoin element (table2_calc) in this table. Create another field of calc element. you have to use the following code:

$att_mthyr = strtotime('{xxxxx_table2_report___table2_calc_raw}');

$first_date = date('Y-m-01', $att_mthyr);
$last_date = date('Y-m-t', $att_mthyr);

$db = JFactory::getDbo();
$db->setQuery(  run your query on data table1 here.  But use  `date_of_attend` between "'.$first_date.' 00:00:00" and "'.$last_date.' 00:00:00")

$attend = $db->loadObjectList();

$list = array();
foreach ($attend as $att)
{
$list[] = "<tr><th>" . $newdt . "</th><th>" . $att->prod . "</th><th>" .$att->stff. "</th><th>" .$att->othr. "</th><th>" .$att->total. "</th></tr>";

}
return "<table><tr><th>Attendance Date</th><th>Prod. Count</th><th>Staff Count</th><th>Others Count</th><th>Total Present</th></tr>".implode($list)."</table>";

Hope it helps you and others.

Thanks
Sunit

 

[sunnyjey]

Everyday I'm learning a new thing. Thanks to the Fabrik community. I have been trying to generate Month-wise record as per suggested by Sunit. I have got stuck at second step.

I know, my following code is incorrect, particularly the setQuery. Looking for a help to run correct query. My code:

$att_mthyr = strtotime('{xxx_invoice___months_raw}');

$first_date = date('Y-m-01', $att_mthyr);
$last_date = date('Y-m-t', $att_mthyr);

$db = JFactory::getDbo();
$db->setQuery
->select($db->quoteName(array('invoice')))
->from($db->quoteName('xxx_cases'))
->where($db->quoteName(`xxx_cases___date_time`)." = ".$db->quote(between "'.$first_date.' 00:00:00" and "'.$last_date.' 00:00:00"));
$attend = $db->loadObjectList();

$list = array();
foreach ($attend as $att)
{
$list[] = "<tr><th>" . $caid . "</th><th>" . $att->name. "</th><th>" .$att->age. "</th><th>" .$att->sex. "</th><th>" .$att->date. "</th></tr>";

}
return "<table><tr><th>ID</th><th>Name</th><th>Age</th><th>Gender</th><th>Date</th></tr>".implode($list)."</table>";