Parsing data from mysql

[Dorinn]

Hello,

 

I use Vtiger and I want to make a report page for tickets. The mysql table for the report has columns like: id, title, status, update_log. The data in update_log column looks like this:

 

  " Status Changed to In Progress\. -- Tuesday 15th September 2009 04:12:40 PM by Operator--//-- Status Changed to Closed\. -- Friday 18th September 2009 10:39:22 AM by Admin--//-- "

 

I want to display a table with the following columns:

 

TicketID | Title | Status | Status Changed In Progress Date | Status Changed In Closed Date | Time elapse from In Progress to Closed

 

How could I achieve this? Parsing data is not my best.

 

Thanks

 

 

[Dorinn]

Any idea? 

[PFMaBiSmAd]

The following would indirectly or directly give the Progress Date ($datetime1 object), Closed Date ($datetime2 object), and Time elapsed (nifty code at the end) -

 

<?php
$string =" Status Changed to In Progress\. -- Tuesday 15th September 2009 04:12:40 PM by Operator--//-- Status Changed to Closed\. -- Friday 18th September 2009 10:39:22 AM by Admin--//-- ";

list($progress,$closed) = explode('--//--',$string);

list(,$progress) = explode('\. -- ',$progress);
list(,$closed) = explode('\. -- ',$closed);

list(,$day,$month,$year,$time,$ampm,,) = explode(' ',$progress);
$datetime1 = new DateTime("$day $month $year $time $ampm");

list(,$day,$month,$year,$time,$ampm,,) = explode(' ',$closed);
$datetime2 = new DateTime("$day $month $year $time $ampm");

$interval = $datetime1->diff($datetime2);

$array = array("y"=>"Year","m"=>"Month","d"=>"Day","h"=>"Hour","i"=>"Minute","s"=>"Second");

foreach($array as $key=>$unit){
if($interval->$key > 0){
	$plural = $interval->$key > 1 ? 's' : '';
	echo "{$interval->$key} $unit$plural<br />";
}
}

[Dorinn]

Hello,

 

Thanks for your response. The part of converting the dates and making the differences works great. The only problem I have is that the text in the 'update_log' column may vary: it could have other info in it or "Status Changed to In Progress" could miss.

 

e.g.

 

Ticket created. Assigned to  user Gabriel -- Monday 12th December 2011 12:21:58 PM by Admin--//-- Status Changed to In Progress\. -- Monday 12th December 2011 01:40:57 PM by Gabriel--//-- Priority Changed to High\. -- Monday 12th December 2011 04:41:17 PM by Gabriel--//-- Category Changed to Other\. -- Monday 12th December 2011 04:41:34 PM by Gabriel--//-- Status Changed to Closed\. -- Monday 12th December 2011 04:42:58 PM by Gabriel--//--

 

 

 

[PFMaBiSmAd]

Unless you have a definition of what it is you want to achieve, its not possible to write code to do it. Do you want to display the elapsed time between each change in status, between specifically named statuses, between the first and latest status and what do you want the output to be?

[Dorinn]

I if think twice, it's better to make the difference between closed date and created date because a ticket could be closed without being in progress.

 

So I need to parse the log and take only the date for the "Status changed in Closed" event and the display it and the difference between it and the created date.

 

The created date is other table's column which type is Datetime (2009-07-23 14:00:06).

 

Thanks again

[PFMaBiSmAd]

<?php
$string =" Status Changed to In Progress\. -- Tuesday 15th September 2009 04:12:40 PM by Operator--//-- Status Changed to Closed\. -- Friday 18th September 2009 10:39:41 AM by Admin--//-- ";

$parts = explode('--//--',$string);

// call back function for array_filter
function find_closed($string){
$find = "Status Changed to Closed";
$pos = strpos($string, $find);
return ($pos === false) ? false : true ;
}

$result = array_filter($parts, 'find_closed'); // find only the closed status

if(empty($result)){
echo "Item is not closed";
} else {
// is closed, get the date
list(,$closed) = explode('\. -- ',current($result));
echo "Closed date: $closed<br />";
list(,$day,$month,$year,$time,$ampm,,) = explode(' ',$closed);
$datetime2 = new DateTime("$day $month $year $time $ampm");

$start = "2009-07-23 14:00:06"; // your created date -----------------------------------------------------------
$datetime1 = new DateTime($start);

$interval = $datetime1->diff($datetime2);

$array = array("y"=>"Year","m"=>"Month","d"=>"Day","h"=>"Hour","i"=>"Minute","s"=>"Second");

foreach($array as $key=>$value){
	if($interval->$key > 0){
		$plural = $interval->$key > 1 ? 's' : '';
		echo "{$interval->$key} $value$plural<br />";
	}
}
}

[Dorinn]

Thanks man. You make my day.

 

This is how I use your code, maybe someone else will be in a similar situation using Vtiger (of course, some css could be added for a better looking):

 

<?php

//created by PFMaBiSmAd on http://www.phpfreaks.com

 

$con = mysql_connect("host","user","password");

mysql_select_db("vtiger", $con) or die;

$sql = "SELECT * FROM view_tickets"; // view created by joining vtiger_troubletickets and vtiger_crmentity on ticketid=crmid

$result1 = mysql_query($sql) or die(mysql_error());

 

// call back function for array_filter

function find_closed($string) {

$find = "Status Changed to Close";

$pos = strpos($string, $find);

return ($pos === false) ? false : true ;

}

 

echo "<table border='1'>";

echo "<th>Ticket no.</th>";

echo "<th>Created date</th>";

echo "<th>Title</th>";

echo "<th>Description</th>";

echo "<th>Closed date</th>";

echo "<th>Diff time</th>";

echo "</tr>";

 

while ($row = mysql_fetch_array($result1)) {

 

echo "<tr>";

echo "<td>" .$row['ticket_no'] . "</td>";

echo "<td>" .$row['createdtime'] . "</td>";

echo "<td>" .$row['title'] . "</td>";

echo "<td>" .$row['description'] . "</td>";

echo "<td>";

 

$string = $row['update_log'];

$start = $row['createdtime'];

 

$parts = explode('--//--',$string);

 

$result = array_filter($parts, 'find_closed'); // find only the closed status

 

if(empty($result)){

echo "Ticket is not closed </td>";

 

} else {

// is closed, get the date

list(,$closed) = explode('\. -- ',current($result));

echo "Closed date:" . $closed . "</td>";

 

list(,$day,$month,$year,$time,$ampm,,) = explode(' ',$closed);

$datetime2 = new DateTime("$day $month $year $time $ampm");

 

//$start = $row['dataCreare']; // your created date -----------------------------------------------------------

$datetime1 = new DateTime($start);

 

$interval = $datetime1->diff($datetime2);

 

$array = array("y"=>"Year","m"=>"Month","d"=>"Day","h"=>"Hour","i"=>"Minute","s"=>"Second");

echo "<td>";

foreach($array as $key=>$value){

if($interval->$key > 0){

$plural = $interval->$key > 1 ? 's' : '';

echo " {$interval->$key} $value$plural ";

}

}

echo "</td>";

}

echo "</tr>";

}

echo "</table>";

?>