Remove status 'Closed' after 30 days

9 posts by 3 authors in: Forums > CMS Builder
Last Post: June 24, 2015   (RSS)

By Zicky - 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 Zicky - 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?

Dave Edis - Senior Developer
interactivetools.com

By Zicky - 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.

Dave Edis - Senior Developer
interactivetools.com

By Zicky - 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! 

Dave Edis - Senior Developer
interactivetools.com

By Zicky - June 24, 2015

Dave - thanks for the outstanding support! Your solution got this working perfectly!

Thanks,

Zicky