<?php
function tracker_entry(){
$date = new DateTime();
$yesterday= $date->modify("-1 days")->format('Y-m-d');
 $today = date("Y-m-d h:i:s");

$total="SELECT count(*) as cnt1 FROM tracker WHERE date_modified >= '".$yesterday."' and date_modified <= '".$today."'";
$tcnt=outright_run_sql_one_row($total);
$count_res='<h3>Total Results: '.$tcnt['cnt1'].'</h3>';
 $query="SELECT count(*) as cnt,module_name,item_id,item_summary FROM tracker WHERE date_modified >='".$yesterday."' and date_modified <= '".$today."' group by module_name";
$run=outright_run_sql($query);
$body = "<table style='border:2px solid black; border-collapse: collapse;'><tr bgcolor='#fce8e6'><th style='border-collapse:collapse;border:1px solid black; '>Sr No.</th><th style='border-collapse: collapse;border:1px solid black;'>Module</th><th style='border-collapse: collapse;border:1px solid black;'>URL</th><th style='border-collapse: collapse;border:1px solid black;'>Total Entry</th></tr>";
$count=1;
$counts=1;
$col=1;
foreach ($run as $key => $value) {
if ($col=='1'){
	 $body=$body."<tr  bgcolor='#dee0e2'>";
	 $col='2';
	 }
	 else{
		 $body=$body."<tr  bgcolor='#fff'>";
		 $col='1';
	 }
	$body = $body."<td style='border:1px solid black;'>".$count++."</td><td style='border:1px solid black;'>".$value['module_name']."</td>";
	if($value['cnt']==1){
		global $sugar_config;
		$siteUrl = $sugar_config['site_url'];
		$url=$siteUrl.'/index.php?module='.$value['module_name'].'&action=DetailView&record='.$value['item_id'].'';
		$body .="<td style='border:1px solid black;border-collapse: collapse;'><a href=".$url.">".$value['item_summary']."</a></td>";
	}
	else{
	$body .="<td style='border:1px solid black;'>Multiple Record</td>";
	}
	$body .="<td style='border:1px solid black;'>".$value['cnt']."</td>";
	$body .="</tr>";
}
$body = $body."</table><br/>";
$body .= '<h2>Below are the total records on which actions are performed along with their respective link.</h2>';
$body .= "<table style='border:2px solid black; border-collapse: collapse;'><tr bgcolor='#fce8e6'; ><th style='border:1px solid black;'>Sr No.</th><th style='border:1px solid black;'>Module Name</th><th style='border:1px solid black;'>User Name</th><th style='border:1px solid black;'>Activity Type</th><th style='border:1px solid black;'>Record</th></tr>";
 $act="SELECT count(*) as cnt,module_name,item_id,item_summary,action,user_id FROM `tracker` where date_modified >= '".$yesterday."' and date_modified <= '".$today."' group by user_id,item_id order by module_name asc"; 
$act_res=outright_run_sql($act);
$color='1';
foreach( $act_res as $key => $single_record){

	$newtext = wordwrap($single_record['item_summary'] , 5, "<br />\n");
	$user='select user_name from users where deleted=0 and id="'.$single_record['user_id'].'"';
	$res=outright_run_sql_one_row($user);

	 $url1=$siteUrl.'/index.php?module='.$single_record['module_name'].'&action=DetailView&record='.$single_record['item_id'].'';
	 if ($color=='1'){
	 $body=$body."<tr  bgcolor='#dee0e2'>";
	 $color='2';
	 }
	 else{
		 $body=$body."<tr  bgcolor='#fff'>";
		 $color='1';
	 }
	$body = $body."<td style='border:1px solid black;'>".$counts++."</td><td style='border:1px solid black;'>".$single_record['module_name']."</td><td style='border:1px solid black;'>".$res['user_name']."</td><td style='border:1px solid black;'>".$single_record['action']."</td><td style='border:1px solid black;'><a href=".$url1." target='_blank'>".substr($single_record['item_summary'],0,50)."</a>(".$single_record['cnt'].")</td>";

}
$body .="</tr>";

$body = $body."</table>";
$dates=date("Y-m-d");
echo $msg = <<<EOT
<h3>Hi,

Here Tracker Entries for <a href=$siteUrl/index.php>Suitecrm</a>
$count_res
$body

<h2>Thanks and Regards,
� All rights reserved.</h2>
EOT;

$email_body = "<pre>".$msg."</pre>";
ors_send_email('ashish@outrightcrm.com','REMAX CRM::Tracker Entry of '.$dates,$email_body);
return true;
}

