Remove status 'Closed' after 30 days

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

Zicky, what field stores the date of when the ticket was set to closed?

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.

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.

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

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 - thanks for the outstanding support! Your solution got this working perfectly!

Thanks,

Zicky