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 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 ?>
The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

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 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