/**
* Return counts for spam, banned, viruses, bad headers, and pending
* @param string full email address
* @return array of the 5 counts
*/
function get_user_summary($emailaddresses)
{
global $conf;
$rval = array();
$total = array('spam' => 0, 'banned' => 0, 'virus' => 0, 'header' => 0, 'pending' => 0, 'total' => 0);
// Get where clause for recipient email address(es)
$recipEmailClause = $this->convertEmailaddresses2SQL($emailaddresses);
# mysql seems to run faster with a left join
if ($conf['db']['dbType'] == 'mysql') {
$join_type = ' LEFT JOIN';
} else {
$join_type = ' INNER JOIN';
}
$query = "SELECT date,\n\t\t\tMAX(stattable.spam) AS spam,\n\t\t\tMAX(stattable.banned) AS banned,\n\t\t\tMAX(stattable.viruses) AS viruses,\n\t\t\tMAX(stattable.badheaders) AS badheaders,\n\t\t\tMAX(stattable.pending) AS pending\n\t\t\tFROM (\n\t\t\t\tSELECT CAST(time_iso AS DATE) AS date,\n\t\t\t\t\tCOUNT(msgs.content) AS spam,\n\t\t\t\t\t0 AS banned,\n\t\t\t\t\t0 AS viruses,\n\t\t\t\t\t0 AS badheaders,\n\t\t\t\t\t0 AS pending\n\t\t\t\t\tFROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id\n\t\t\t\t\t{$join_type} maddr AS recip ON msgrcpt.rid=recip.id\n\t\t\t\t\tWHERE msgs.content='S' AND NOT (msgs.quar_type = '') AND msgrcpt.rs IN ('','v')\n\t\t\t\t\tAND {$recipEmailClause}\n\t\t\t\t\tGROUP BY CAST(time_iso AS DATE)\n\t\t\t\tUNION\n\t\t\t\tSELECT CAST(time_iso AS DATE) AS date,\n\t\t\t\t\t0 AS spam,\n\t\t\t\t\tCOUNT(msgs.content) AS banned,\n\t\t\t\t\t0 AS viruses,\n\t\t\t\t\t0 AS badheaders,\n\t\t\t\t\t0 AS pending\n\t\t\t\t\tFROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id\n\t\t\t\t\t{$join_type} maddr AS recip ON msgrcpt.rid=recip.id\n\t\t\t\t\tWHERE msgs.content='B' AND NOT (msgs.quar_type = '') AND msgrcpt.rs IN ('','v')\n\t\t\t\t\tAND {$recipEmailClause}\n\t\t\t\t\tGROUP BY CAST(time_iso AS DATE)\n\t\t\t\tUNION\n\t\t\t\tSELECT CAST(time_iso AS DATE) AS date,\n\t\t\t\t\t0 AS spam,\n\t\t\t\t\t0 AS banned,\n\t\t\t\t\tCOUNT(msgs.content) AS viruses,\n\t\t\t\t\t0 AS badheaders,\n\t\t\t\t\t0 AS pending\n\t\t\t\t\tFROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id\n\t\t\t\t\t{$join_type} maddr AS recip ON msgrcpt.rid=recip.id\n\t\t\t\t\tWHERE msgs.content='V' AND NOT (msgs.quar_type = '') AND msgrcpt.rs IN ('','v')\n\t\t\t\t\tAND {$recipEmailClause}\n\t\t\t\t\tGROUP BY CAST(time_iso AS DATE)\n\t\t\t\tUNION\n\t\t\t\tSELECT CAST(time_iso AS DATE) AS date,\n\t\t\t\t\t0 AS spam,\n\t\t\t\t\t0 AS banned,\n\t\t\t\t\t0 AS viruses,\n\t\t\t\t\tCOUNT(msgs.content) AS badheaders,\n\t\t\t\t\t0 AS pending\n\t\t\t\t\tFROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id\n\t\t\t\t\t{$join_type} maddr AS recip ON msgrcpt.rid=recip.id\n\t\t\t\t\tWHERE msgs.content='H' AND NOT (msgs.quar_type = '') AND msgrcpt.rs IN ('','v')\n\t\t\t\t\tAND {$recipEmailClause}\n\t\t\t\t\tGROUP BY CAST(time_iso AS DATE)\n\t\t\t\tUNION\n\t\t\t\tSELECT CAST(time_iso AS DATE) AS date,\n\t\t\t\t\t0 AS spam,\n\t\t\t\t\t0 AS banned,\n\t\t\t\t\t0 AS viruses,\n\t\t\t\t\t0 AS badheaders,\n\t\t\t\t\tCOUNT(msgs.content) AS pending\n\t\t\t\t\tFROM msgs INNER JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id\n\t\t\t\t\t{$join_type} maddr AS recip ON msgrcpt.rid=recip.id\n\t\t\t\t\tWHERE msgrcpt.rs='p' AND NOT (msgs.quar_type = '')\n\t\t\t\t\tAND {$recipEmailClause}\n\t\t\t\t\tGROUP BY CAST(time_iso AS DATE)\n\t\t\t) AS stattable\n\t\t\tGROUP BY date\n\t\t\tORDER BY date";
// Prepare query
$q = $this->db->prepare($query);
// Execute query
$result = $this->db->execute($q);
// Check if error
$this->check_for_error($result, $query);
while ($rs = $result->fetchRow()) {
$timestamp = CmnFns::formatDateISO($rs['date']);
$date = CmnFns::formatDate($timestamp);
$totalthisdate = $rs['spam'] + $rs['banned'] + $rs['viruses'] + $rs['badheaders'] + $rs['pending'];
$rval[$date] = array('spam' => $rs['spam'], 'banned' => $rs['banned'], 'virus' => $rs['viruses'], 'header' => $rs['badheaders'], 'pending' => $rs['pending'], 'total' => $totalthisdate);
}
// Total the data
foreach ($rval as $date => $typearray) {
foreach ($typearray as $type => $count) {
$total[$type] += $count;
}
}
$rval['Total'] = $total;
$result->free();
return $rval;
}
