Remove status 'Closed' after 30 days
9 posts by 3 authors in: Forums > CMS Builder
Last Post: June 24, 2015 (RSS)
By Mikey - June 17, 2015 - edited: June 18, 2015
I've got a bit of code listed below, where I'd like to only display tickets that are set as status 'Closed' for only an additional 30 days - from the time the ticket was set as 'Closed'. Anyone have any suggestions on how to achieve this in the code below?
$isStaff = $CURRENT_USER['_supportticket_staff'];
$where = "TRUE";
if (!$isStaff) {
$where = "creator = ".$CURRENT_USER['num'];
}
$tickets = mysql_select('_tickets', $where . " ORDER BY status = 'Closed', last_post_date DESC");
$userNums = array_unique(array_merge(array_pluck($tickets, 'creator'), array_pluck($tickets, 'last_post_author')));
$userByNum = array_groupBy(mysql_select(accountsTable(), "num IN (" . mysql_escapeCSV($userNums) . ")"), 'num');
By gkornbluth - June 17, 2015 - edited: June 17, 2015
Hi Zicky,
Seems that you might be able to use code similar to your "event signup" post
Here's a recipe from my CMSB Coobook http://www.thecmsbcookbook.com that might give you some ideas.
There's a lot moire in the cookbook about working with dates, and date ranges.
Jerry Kornbluth
USING THE NUMBER OF RECORDS MEETING A MYSQL QUERY TO CHANGE WHAT A VIEWER SHOWS
I had a page with a number of "Where" statements setting the criteria for which records wound appear on groups on a
page.
What my client wanted to do was hide all reference to any group of records where there were no records that met those
criteria.
Example. The heading HAPPENING THIS WEEK if there were no records with start dates falling within the next 7 days.
Here's the simple if statement code that worked for me.
<?php
list($eventsRecords, $eventsMetaData) = getRecords(array(
'tableName' => 'events',
'where' => 'start_date > TIMESTAMP(NOW()+ INTERVAL 7 DAY)',
'orderBy'=> 'end_date ASC',
));
?>
<?php if (!$eventsMetaData['totalRecords'] == 0): ?>
Show this code....
<?php else: ?>
Show some other code...
<?php endif ?>
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Mikey - June 17, 2015
Hey Jerry - thanks for the tip. I didn't have any luck getting it to work... just going to have to keep digging through the threads.
Zick
By Dave - June 18, 2015
Zicky, what field stores the date of when the ticket was set to closed?
interactivetools.com
By Mikey - June 18, 2015
There's a Multi section with a MySQL called "_tickets" which has a field for "last_post_date" for the ticketing system. Within the Multi section called "_tickets" there are "related records" which pull data from a Multi section called "_posts" which has a field called "createdDate".
The "_tickets" manages the correspondence of the "_posts" associated with the ticket.
By Dave - June 18, 2015
Hi Zick,
Ok, so your requirements are as follows:
I'd like to only display tickets that are set as status 'Closed' for only an additional 30 days - from the time the ticket was set as 'Closed'.
What about this:
// create where
$where = " `status` = 'Closed' ";
$where .= " AND `last_post_date` >= (NOW() - INTERVAL 30 DAY) ";
if ($isStaff) {
$where .= " AND `creator` = '" .mysql_escape($CURRENT_USER['num']). "'";
}
Let me know if that works for you.
interactivetools.com
By Mikey - June 18, 2015 - edited: June 18, 2015
Hey Dave,
I gave the lines of code a try, but it didn't achieve the desired result and only produced/triggered the following message "There are no Support Tickets to display."
It also eliminated all the open support tickets as well, which need to remain displayed until the ticket is set to Closed.
<?php else: ?>
<div style="font-weight:100; font-size:12px; padding-bottom:18px;">There are no Support Tickets to display.</div>
<?php endif ?>
PS: I'm not sure if this makes a difference, but I did not originally include the RED / Bold text in my original post as seen below. Maybe it's effecting your solution.
$isStaff = $CURRENT_USER['_supportticket_staff'];
$where = "TRUE";
if (!$isStaff) {
$where = "creator = ".$CURRENT_USER['num'];
}
$tickets = mysql_select('_tickets', $where . " ORDER BY status = 'Closed', last_post_date DESC");
$userNums = array_unique(array_merge(array_pluck($tickets, 'creator'), array_pluck($tickets, 'last_post_author')));
$userByNum = array_groupBy(mysql_select(accountsTable(), "num IN (" . mysql_escapeCSV($userNums) . ")"), 'num');
Thanks,
Zick
By Dave - June 23, 2015
Hi Zick,
Ok, I'm note sure the exact logic you're looking for. One thing I do when I'm trying to work out the logic is to display all the fields for all the records and then determine the query based on which ones I want to show.
Maybe you want 'open' records and 'closed' records that have a `last_post_date` in the last 30 days?
$whereOpen = " `status` = 'Open' ";
$whereClosedInLast30 = " (`status` = 'Closed' AND `last_post_date` >= (NOW() - INTERVAL 30 DAY) ) ";
$where = " $whereOpen OR $whereClosedInLast30 ";
In any case, if the query isn't returning the records you want, try adding and removing parts of it to determine which parts are working and which parts need adjusting.
Hope that helps!
interactivetools.com
By Mikey - June 24, 2015
Dave - thanks for the outstanding support! Your solution got this working perfectly!
Thanks,
Zicky