/** * 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; }