private function _handleImportComment($xml)
{
$mask = (string) $xml->mask;
$author_email = (string) $xml->author_email;
$note = trim((string) $xml->note);
$created = intval((string) $xml->created_date);
$author_address = CerberusApplication::hashLookupAddress($author_email, true);
// Bad file
if (empty($note) || empty($author_address) || empty($mask)) {
return false;
}
// echo "MASK: ",$mask,"<BR>";
// echo " -- Author: ",$author_address->email,"<BR>";
// echo " -- Note: ",$note,"<BR>";
if (null !== ($ticket = DAO_Ticket::getTicketByMask($mask))) {
$fields = array(DAO_TicketComment::CREATED => $created, DAO_TicketComment::TICKET_ID => $ticket->id, DAO_TicketComment::COMMENT => $note, DAO_TicketComment::ADDRESS_ID => $author_address->id);
if (null !== ($comment_id = DAO_TicketComment::create($fields))) {
return true;
}
}
return false;
}
/**
* Enter description here...
*
* @param CerberusParserMessage $message
* @return integer
*/
public static function parseMessage(CerberusParserMessage $message, $options = array())
{
/*
* options:
* 'no_autoreply'
*/
$logger = DevblocksPlatform::getConsoleLog();
$settings = DevblocksPlatform::getPluginSettingsService();
$helpdesk_senders = CerberusApplication::getHelpdeskSenders();
// Pre-parse mail filters
$pre_filters = Model_PreParseRule::getMatches($message);
if (is_array($pre_filters) && !empty($pre_filters)) {
// Load filter action manifests for reuse
$ext_action_mfts = DevblocksPlatform::getExtensions('cerberusweb.mail_filter.action', false);
// Loop through all matching filters
foreach ($pre_filters as $pre_filter) {
// Do something with matching filter's actions
foreach ($pre_filter->actions as $action_key => $action) {
switch ($action_key) {
case 'blackhole':
return NULL;
break;
case 'redirect':
@($to = $action['to']);
CerberusMail::reflect($message, $to);
return NULL;
break;
case 'bounce':
@($msg = $action['message']);
@($subject = 'Delivery failed: ' . self::fixQuotePrintableString($message->headers['subject']));
// [TODO] Follow the RFC spec on a true bounce
if (null != ($fromAddressInst = CerberusParser::getAddressFromHeaders($message->headers))) {
CerberusMail::quickSend($fromAddressInst->email, $subject, $msg);
}
return NULL;
break;
default:
// Plugin pre-parser filter actions
if (isset($ext_action_mfts[$action_key])) {
if (null != @($ext_action = $ext_action_mfts[$action_key]->createInstance())) {
try {
/* @var $ext_action Extension_MailFilterAction */
$ext_action->run($pre_filter, $message);
} catch (Exception $e) {
}
}
}
break;
}
}
}
}
$headers =& $message->headers;
// From
if (null == ($fromAddressInst = CerberusParser::getAddressFromHeaders($headers))) {
$logger->err("[Parser] 'From' address could not be created.");
return NULL;
}
// To/Cc/Bcc
$to = array();
$sTo = @$headers['to'];
$bIsNew = true;
if (!empty($sTo)) {
// [TODO] Do we still need this RFC address parser?
$to = CerberusParser::parseRfcAddress($sTo);
}
// Subject
// Fix quote printable subject (quoted blocks can appear anywhere in subject)
$sSubject = "";
if (isset($headers['subject']) && !empty($headers['subject'])) {
$sSubject = $headers['subject'];
if (is_array($sSubject)) {
$sSubject = array_shift($sSubject);
}
}
// The subject can still end up empty after QP decode
if (empty($sSubject)) {
$sSubject = "(no subject)";
}
// Date
$iDate = @strtotime($headers['date']);
// If blank, or in the future, set to the current date
if (empty($iDate) || $iDate > time()) {
$iDate = time();
}
// Is banned?
if (1 == $fromAddressInst->is_banned) {
$logger->info("[Parser] Ignoring ticket from banned address: " . $fromAddressInst->email);
return NULL;
}
// Overloadable
$enumSpamTraining = '';
// Message Id / References / In-Reply-To
@($sMessageId = $headers['message-id']);
$body_append_text = array();
$body_append_html = array();
// [mdf]Check attached files before creating the ticket because we may need to overwrite the message-id
// also store any contents of rfc822 files so we can include them after the body
foreach ($message->files as $filename => $file) {
/* @var $file ParserFile */
switch ($file->mime_type) {
case 'message/rfc822':
$full_filename = $file->tmpname;
$mail = mailparse_msg_parse_file($full_filename);
$struct = mailparse_msg_get_structure($mail);
$msginfo = mailparse_msg_get_part_data($mail);
$inline_headers = $msginfo['headers'];
if (isset($headers['from']) && (strtolower(substr($headers['from'], 0, 11)) == 'postmaster@' || strtolower(substr($headers['from'], 0, 14)) == 'mailer-daemon@')) {
$headers['in-reply-to'] = $inline_headers['message-id'];
}
break;
}
}
// [JAS] [TODO] References header may contain multiple message-ids to find
if (null != ($ids = self::findParentMessage($headers))) {
$bIsNew = false;
$id = $ids['ticket_id'];
$msgid = $ids['message_id'];
// Is it a worker reply from an external client? If so, proxy
if (null != ($worker_address = DAO_AddressToWorker::getByAddress($fromAddressInst->email))) {
$logger->info("[Parser] Handling an external worker response from " . $fromAddressInst->email);
if (!DAO_Ticket::isTicketRequester($worker_address->address, $id)) {
// Watcher Commands [TODO] Document on wiki/etc
if (0 != ($matches = preg_match_all("/\\[(.*?)\\]/i", $message->headers['subject'], $commands))) {
@($command = strtolower(array_pop($commands[1])));
$logger->info("[Parser] Worker command: " . $command);
switch ($command) {
case 'close':
DAO_Ticket::updateTicket($id, array(DAO_Ticket::IS_CLOSED => CerberusTicketStatus::CLOSED));
break;
case 'take':
DAO_Ticket::updateTicket($id, array(DAO_Ticket::NEXT_WORKER_ID => $worker_address->worker_id));
break;
case 'comment':
$comment_id = DAO_TicketComment::create(array(DAO_TicketComment::ADDRESS_ID => $fromAddressInst->id, DAO_TicketComment::CREATED => time(), DAO_TicketComment::TICKET_ID => $id, DAO_TicketComment::COMMENT => $message->body));
return $id;
break;
default:
// Typo?
break;
}
}
$attachment_files = array();
$attachment_files['name'] = array();
$attachment_files['type'] = array();
$attachment_files['tmp_name'] = array();
$attachment_files['size'] = array();
$i = 0;
foreach ($message->files as $filename => $file) {
$attachment_files['name'][$i] = $filename;
$attachment_files['type'][$i] = $file->mime_type;
$attachment_files['tmp_name'][$i] = $file->tmpname;
$attachment_files['size'][$i] = $file->file_size;
$i++;
}
CerberusMail::sendTicketMessage(array('message_id' => $msgid, 'content' => $message->body, 'files' => $attachment_files, 'agent_id' => $worker_address->worker_id));
return $id;
} else {
// ... worker is a requester, treat as normal
$logger->info("[Parser] The external worker was a ticket requester, so we're not treating them as a watcher.");
}
} else {
// Reply: Not sent by a worker
/*
* [TODO] check that this sender is a requester on the matched ticket
* Otherwise blank out the $id
*/
}
}
$group_id = 0;
if (empty($id)) {
// New Ticket
$sMask = CerberusApplication::generateTicketMask();
$groups = DAO_Group::getAll();
// Routing new tickets
if (null != ($routing_rules = Model_MailToGroupRule::getMatches($fromAddressInst, $message))) {
if (is_array($routing_rules)) {
foreach ($routing_rules as $rule) {
// Only end up with the last 'move' action (ignore the previous)
if (isset($rule->actions['move'])) {
$group_id = intval($rule->actions['move']['group_id']);
// We don't need to move again when running rule actions
unset($rule->actions['move']);
}
}
}
}
// Make sure the group exists
if (!isset($groups[$group_id])) {
$group_id = null;
}
// Last ditch effort to check for a default group to deliver to
if (empty($group_id)) {
if (null != ($default_team = DAO_Group::getDefaultGroup())) {
$group_id = $default_team->id;
} else {
// Bounce
return null;
}
}
// [JAS] It's important to not set the group_id on the ticket until the messages exist
// or inbox filters will just abort.
$fields = array(DAO_Ticket::MASK => $sMask, DAO_Ticket::SUBJECT => $sSubject, DAO_Ticket::IS_CLOSED => 0, DAO_Ticket::FIRST_WROTE_ID => intval($fromAddressInst->id), DAO_Ticket::LAST_WROTE_ID => intval($fromAddressInst->id), DAO_Ticket::CREATED_DATE => $iDate, DAO_Ticket::UPDATED_DATE => $iDate, DAO_Ticket::LAST_ACTION_CODE => CerberusTicketActionCode::TICKET_OPENED);
$id = DAO_Ticket::createTicket($fields);
// Apply routing actions to our new ticket ID
if (isset($routing_rules) && is_array($routing_rules)) {
foreach ($routing_rules as $rule) {
$rule->run($id);
}
}
}
// [JAS]: Add requesters to the ticket
if (!empty($fromAddressInst->id) && !empty($id)) {
// Don't add a requester if the sender is a helpdesk address
if (isset($helpdesk_senders[$fromAddressInst->email])) {
$logger->info("[Parser] Not adding ourselves as a requester: " . $fromAddressInst->email);
} else {
DAO_Ticket::createRequester($fromAddressInst->id, $id);
}
}
// Add the other TO/CC addresses to the ticket
// [TODO] This should be cleaned up and optimized
if ($settings->get('cerberusweb.core', CerberusSettings::PARSER_AUTO_REQ, 0)) {
@($autoreq_exclude_list = $settings->get('cerberusweb.core', CerberusSettings::PARSER_AUTO_REQ_EXCLUDE, ''));
$destinations = self::getDestinations($headers);
if (is_array($destinations) && !empty($destinations)) {
// Filter out any excluded requesters
if (!empty($autoreq_exclude_list)) {
@($autoreq_exclude = DevblocksPlatform::parseCrlfString($autoreq_exclude_list));
if (is_array($autoreq_exclude) && !empty($autoreq_exclude)) {
foreach ($autoreq_exclude as $excl_pattern) {
$excl_regexp = DevblocksPlatform::parseStringAsRegExp($excl_pattern);
// Check all destinations for this pattern
foreach ($destinations as $idx => $dest) {
if (@preg_match($excl_regexp, $dest)) {
unset($destinations[$idx]);
}
}
}
}
}
foreach ($destinations as $dest) {
if (null != ($destInst = CerberusApplication::hashLookupAddress($dest, true))) {
// Skip if the destination is one of our senders or the matching TO
if (isset($helpdesk_senders[$destInst->email])) {
continue;
}
DAO_Ticket::createRequester($destInst->id, $id);
}
}
}
}
$attachment_path = APP_STORAGE_PATH . '/attachments/';
// [TODO] This should allow external attachments (S3)
$fields = array(DAO_Message::TICKET_ID => $id, DAO_Message::CREATED_DATE => $iDate, DAO_Message::ADDRESS_ID => $fromAddressInst->id);
$email_id = DAO_Message::create($fields);
// Content
DAO_MessageContent::create($email_id, $message->body);
// Headers
foreach ($headers as $hk => $hv) {
DAO_MessageHeader::create($email_id, $hk, $hv);
}
// [mdf] Loop through files to insert attachment records in the db, and move temporary files
if (!empty($email_id)) {
foreach ($message->files as $filename => $file) {
/* @var $file ParserFile */
//[mdf] skip rfc822 messages since we extracted their content above
if ($file->mime_type == 'message/rfc822') {
continue;
}
$fields = array(DAO_Attachment::MESSAGE_ID => $email_id, DAO_Attachment::DISPLAY_NAME => $filename, DAO_Attachment::MIME_TYPE => $file->mime_type, DAO_Attachment::FILE_SIZE => intval($file->file_size));
$file_id = DAO_Attachment::create($fields);
if (empty($file_id)) {
@unlink($file->tmpname);
// remove our temp file
continue;
}
// Make file attachments use buckets so we have a max per directory
$attachment_bucket = sprintf("%03d/", mt_rand(1, 100));
$attachment_file = $file_id;
if (!file_exists($attachment_path . $attachment_bucket)) {
@mkdir($attachment_path . $attachment_bucket, 0770, true);
// [TODO] Needs error checking
}
rename($file->getTempFile(), $attachment_path . $attachment_bucket . $attachment_file);
// [TODO] Split off attachments into its own DAO
DAO_Attachment::update($file_id, array(DAO_Attachment::FILEPATH => $attachment_bucket . $attachment_file));
}
}
// Pre-load custom fields
if (isset($message->custom_fields) && !empty($message->custom_fields)) {
foreach ($message->custom_fields as $cf_id => $cf_val) {
if (is_array($cf_val) && !empty($cf_val) || !is_array($cf_val) && 0 != strlen($cf_val)) {
DAO_CustomFieldValue::setFieldValue('cerberusweb.fields.source.ticket', $id, $cf_id, $cf_val);
}
}
}
// Finalize our new ticket details (post-message creation)
if ($bIsNew && !empty($id) && !empty($email_id)) {
// First thread (needed for anti-spam)
DAO_Ticket::updateTicket($id, array(DAO_Ticket::FIRST_MESSAGE_ID => $email_id));
// Prime the change fields (which a few things like anti-spam might change before we commit)
$change_fields = array(DAO_Ticket::TEAM_ID => $group_id);
$out = CerberusBayes::calculateTicketSpamProbability($id);
if (!empty($group_id)) {
@($spam_threshold = DAO_GroupSettings::get($group_id, DAO_GroupSettings::SETTING_SPAM_THRESHOLD, 80));
@($spam_action = DAO_GroupSettings::get($group_id, DAO_GroupSettings::SETTING_SPAM_ACTION, ''));
@($spam_action_param = DAO_GroupSettings::get($group_id, DAO_GroupSettings::SETTING_SPAM_ACTION_PARAM, ''));
if ($out['probability'] * 100 >= $spam_threshold) {
$enumSpamTraining = CerberusTicketSpamTraining::SPAM;
switch ($spam_action) {
default:
case 0:
// do nothing
break;
case 1:
// delete
$change_fields[DAO_Ticket::IS_CLOSED] = 1;
$change_fields[DAO_Ticket::IS_DELETED] = 1;
break;
case 2:
// move
$buckets = DAO_Bucket::getAll();
// Verify bucket exists
if (!empty($spam_action_param) && isset($buckets[$spam_action_param])) {
$change_fields[DAO_Ticket::TEAM_ID] = $group_id;
$change_fields[DAO_Ticket::CATEGORY_ID] = $spam_action_param;
}
break;
}
}
}
// end spam training
// Save properties
if (!empty($change_fields)) {
DAO_Ticket::updateTicket($id, $change_fields);
}
}
// Reply notifications (new messages are handled by 'move' listener)
if (!$bIsNew) {
// Inbound Reply Event
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('ticket.reply.inbound', array('ticket_id' => $id)));
}
// New ticket processing
if ($bIsNew) {
// Auto reply
@($autoreply_enabled = DAO_GroupSettings::get($group_id, DAO_GroupSettings::SETTING_AUTO_REPLY_ENABLED, 0));
@($autoreply = DAO_GroupSettings::get($group_id, DAO_GroupSettings::SETTING_AUTO_REPLY, ''));
/*
* Send the group's autoreply if one exists, as long as this ticket isn't spam
*/
if (!isset($options['no_autoreply']) && $autoreply_enabled && !empty($autoreply) && $enumSpamTraining != CerberusTicketSpamTraining::SPAM) {
CerberusMail::sendTicketMessage(array('ticket_id' => $id, 'message_id' => $email_id, 'content' => str_replace(array('#ticket_id#', '#mask#', '#subject#', '#timestamp#', '#sender#', '#sender_first#', '#orig_body#'), array($id, $sMask, $sSubject, date('r'), $fromAddressInst->email, $fromAddressInst->first_name, ltrim($message->body)), $autoreply), 'is_autoreply' => true, 'dont_keep_copy' => true));
}
}
// end bIsNew
unset($message);
// Re-open and update our date on new replies
if (!$bIsNew) {
DAO_Ticket::updateTicket($id, array(DAO_Ticket::UPDATED_DATE => time(), DAO_Ticket::IS_WAITING => 0, DAO_Ticket::IS_CLOSED => 0, DAO_Ticket::IS_DELETED => 0, DAO_Ticket::LAST_WROTE_ID => $fromAddressInst->id, DAO_Ticket::LAST_ACTION_CODE => CerberusTicketActionCode::TICKET_CUSTOMER_REPLY));
// [TODO] The TICKET_CUSTOMER_REPLY should be sure of this message address not being a worker
}
@imap_errors();
// Prevent errors from spilling out into STDOUT
return $id;
}
function saveRequestersPanelAction()
{
@($ticket_id = DevblocksPlatform::importGPC($_POST['ticket_id'], 'integer'));
@($msg_id = DevblocksPlatform::importGPC($_POST['msg_id'], 'integer'));
// Dels
@($req_deletes = DevblocksPlatform::importGPC($_POST['req_deletes'], 'array', array()));
if (!empty($req_deletes)) {
foreach ($req_deletes as $del_id) {
DAO_Ticket::deleteRequester($ticket_id, $del_id);
}
}
// Adds
@($req_adds = DevblocksPlatform::importGPC($_POST['req_adds'], 'string', ''));
$req_list = DevblocksPlatform::parseCrlfString($req_adds);
$req_addys = array();
if (is_array($req_list) && !empty($req_list)) {
foreach ($req_list as $req) {
if (empty($req)) {
continue;
}
$rfc_addys = imap_rfc822_parse_adrlist($req, 'localhost');
foreach ($rfc_addys as $rfc_addy) {
$addy = $rfc_addy->mailbox . '@' . $rfc_addy->host;
if (null != ($req_addy = CerberusApplication::hashLookupAddress($addy, true))) {
DAO_Ticket::createRequester($req_addy->id, $ticket_id);
}
}
}
}
$requesters = DAO_Ticket::getRequestersByTicket($ticket_id);
$list = array();
foreach ($requesters as $requester) {
$list[] = $requester->email;
}
echo implode(', ', $list);
exit;
}
function doImportAction()
{
$active_worker = CerberusApplication::getActiveWorker();
if (!$active_worker->hasPriv('crm.opp.actions.import')) {
return;
}
@($pos = DevblocksPlatform::importGPC($_REQUEST['pos'], 'array', array()));
@($field = DevblocksPlatform::importGPC($_REQUEST['field'], 'array', array()));
@($sync_dupes = DevblocksPlatform::importGPC($_REQUEST['sync_dupes'], 'array', array()));
@($include_first = DevblocksPlatform::importGPC($_REQUEST['include_first'], 'integer', 0));
@($is_blank_unset = DevblocksPlatform::importGPC($_REQUEST['is_blank_unset'], 'integer', 0));
@($opt_assign = DevblocksPlatform::importGPC($_REQUEST['opt_assign'], 'integer', 0));
@($opt_assign_worker_id = DevblocksPlatform::importGPC($_REQUEST['opt_assign_worker_id'], 'integer', 0));
$visit = CerberusApplication::getVisit();
$db = DevblocksPlatform::getDatabaseService();
$workers = DAO_Worker::getAllActive();
$csv_file = $visit->get('crm.import.last.csv', '');
$fp = fopen($csv_file, "rt");
if (!$fp) {
return;
}
// [JAS]: Do we need to consume a first row of headings?
if (!$include_first) {
@fgetcsv($fp, 8192, ',', '"');
}
while (!feof($fp)) {
$parts = fgetcsv($fp, 8192, ',', '"');
if (empty($parts) || 1 == count($parts) && is_null($parts[0])) {
continue;
}
$fields = array();
$custom_fields = array();
$sync_fields = array();
foreach ($pos as $idx => $p) {
$key = $field[$idx];
$val = $parts[$idx];
// Special handling
if (!empty($key)) {
switch ($key) {
case 'amount':
if (0 != strlen($val) && is_numeric($val)) {
@($val = floatval($val));
} else {
unset($key);
}
break;
// Translate e-mail address to ID
// Translate e-mail address to ID
case 'email':
if (null != ($addy = CerberusApplication::hashLookupAddress($val, true))) {
$key = 'primary_email_id';
$val = $addy->id;
} else {
unset($key);
}
break;
// Bools
// Bools
case 'is_won':
case 'is_closed':
if (0 != strlen($val)) {
@($val = !empty($val) ? 1 : 0);
} else {
unset($key);
}
break;
// Dates
// Dates
case 'created_date':
case 'updated_date':
case 'closed_date':
if (0 != strlen($val)) {
@($val = !is_numeric($val) ? strtotime($val) : $val);
} else {
unset($key);
}
break;
// Worker by name
// Worker by name
case 'worker':
unset($key);
if (is_array($workers)) {
foreach ($workers as $worker_id => $worker) {
if (0 == strcasecmp($val, $worker->getName())) {
$key = 'worker_id';
$val = $worker_id;
}
}
}
break;
}
if (!isset($key)) {
continue;
}
// Custom fields
if ('cf_' == substr($key, 0, 3)) {
$custom_fields[substr($key, 3)] = $val;
} elseif (!empty($key)) {
$fields[$key] = $val;
}
// Find dupe combos
if (in_array($idx, $sync_dupes)) {
$search_field = '';
$search_val = '';
switch ($key) {
case 'primary_email_id':
$search_field = SearchFields_CrmOpportunity::PRIMARY_EMAIL_ID;
$search_val = intval($val);
break;
case 'title':
$search_field = SearchFields_CrmOpportunity::NAME;
$search_val = $val;
break;
case 'amount':
$search_field = SearchFields_CrmOpportunity::AMOUNT;
$search_val = floatval($val);
break;
case 'is_won':
$search_field = SearchFields_CrmOpportunity::IS_WON;
$search_val = intval($val);
break;
case 'is_closed':
$search_field = SearchFields_CrmOpportunity::IS_CLOSED;
$search_val = intval($val);
break;
case 'created_date':
$search_field = SearchFields_CrmOpportunity::CREATED_DATE;
$search_val = intval($val);
break;
case 'updated_date':
$search_field = SearchFields_CrmOpportunity::UPDATED_DATE;
$search_val = intval($val);
break;
case 'closed_date':
$search_field = SearchFields_CrmOpportunity::CLOSED_DATE;
$search_val = intval($val);
break;
case 'worker_id':
$search_field = SearchFields_CrmOpportunity::WORKER_ID;
$search_val = intval($val);
break;
default:
// Custom field dupe
if ('cf_' == substr($key, 0, 3)) {
$search_field = $key;
// [TODO] Need to format this for proper custom fields
$search_val = $val;
}
break;
}
if (!empty($search_field) && !empty($search_val)) {
$sync_fields[$search_field] = new DevblocksSearchCriteria($search_field, '=', $search_val);
}
}
}
}
// end foreach($pos)
// Dupe checking
if (!empty($fields) && !empty($sync_fields)) {
list($dupes, $null) = DAO_CrmOpportunity::search(array(), $sync_fields, 1, 0, null, false, false);
}
if (!empty($fields)) {
if (isset($fields['primary_email_id'])) {
// Make sure a minimum amount of fields are provided
if (!isset($fields[DAO_CrmOpportunity::UPDATED_DATE])) {
$fields[DAO_CrmOpportunity::UPDATED_DATE] = time();
}
if ($opt_assign && !isset($fields[DAO_CrmOpportunity::WORKER_ID])) {
$fields[DAO_CrmOpportunity::WORKER_ID] = $opt_assign_worker_id;
}
if (empty($dupes)) {
// [TODO] Provide an import prefix for blank names
if (!isset($fields[DAO_CrmOpportunity::NAME]) && isset($addy)) {
$fields[DAO_CrmOpportunity::NAME] = $addy->email;
}
if (!isset($fields[DAO_CrmOpportunity::CREATED_DATE])) {
$fields[DAO_CrmOpportunity::CREATED_DATE] = time();
}
$id = DAO_CrmOpportunity::create($fields);
} else {
$id = key($dupes);
DAO_CrmOpportunity::update($id, $fields);
}
}
}
if (!empty($custom_fields) && !empty($id)) {
// Format (typecast) and set the custom field types
$source_ext_id = CrmCustomFieldSource_Opportunity::ID;
DAO_CustomFieldValue::formatAndSetFieldValues($source_ext_id, $id, $custom_fields, $is_blank_unset);
}
}
@unlink($csv_file);
// nuke the imported file
$visit->set('crm.import.last.csv', null);
DevblocksPlatform::redirect(new DevblocksHttpResponse(array('activity', 'opps')));
}
static function sendTicketMessage($properties = array())
{
$settings = DevblocksPlatform::getPluginSettingsService();
$helpdesk_senders = CerberusApplication::getHelpdeskSenders();
@($from_addy = $settings->get('cerberusweb.core', CerberusSettings::DEFAULT_REPLY_FROM, $_SERVER['SERVER_ADMIN']));
@($from_personal = $settings->get('cerberusweb.core', CerberusSettings::DEFAULT_REPLY_PERSONAL, ''));
// [TODO] If we still don't have a $from_addy we need a graceful failure.
/*
* [TODO] Move these into constants?
'message_id'
-----'ticket_id'
'subject'
'to'
'cc'
'bcc'
'content'
'files'
'closed'
'ticket_reopen'
'unlock_date'
'bucket_id'
'agent_id',
'is_autoreply',
'dont_send',
'dont_save_copy'
*/
$mail_succeeded = true;
try {
// objects
$mail_service = DevblocksPlatform::getMailService();
$mailer = $mail_service->getMailer(CerberusMail::getMailerDefaults());
$mail = $mail_service->createMessage();
// properties
@($reply_message_id = $properties['message_id']);
@($content = $properties['content']);
@($files = $properties['files']);
@($forward_files = $properties['forward_files']);
@($worker_id = $properties['agent_id']);
@($subject = $properties['subject']);
$message = DAO_Ticket::getMessage($reply_message_id);
$message_headers = DAO_MessageHeader::getAll($reply_message_id);
$ticket_id = $message->ticket_id;
$ticket = DAO_Ticket::getTicket($ticket_id);
// [TODO] Check that message|ticket isn't NULL
// If this ticket isn't spam trained and our outgoing message isn't an autoreply
if ($ticket->spam_training == CerberusTicketSpamTraining::BLANK && (!isset($properties['is_autoreply']) || !$properties['is_autoreply'])) {
CerberusBayes::markTicketAsNotSpam($ticket_id);
}
// Allow teams to override the default from/personal
@($group_reply = DAO_GroupSettings::get($ticket->team_id, DAO_GroupSettings::SETTING_REPLY_FROM, ''));
@($group_personal = DAO_GroupSettings::get($ticket->team_id, DAO_GroupSettings::SETTING_REPLY_PERSONAL, ''));
@($group_personal_with_worker = DAO_GroupSettings::get($ticket->team_id, DAO_GroupSettings::SETTING_REPLY_PERSONAL_WITH_WORKER, 0));
if (!empty($group_reply)) {
$from_addy = $group_reply;
}
if (!empty($group_personal)) {
$from_personal = $group_personal;
}
// Prefix the worker name on the personal line?
if (!empty($group_personal_with_worker) && null != ($reply_worker = DAO_Worker::getAgent($worker_id))) {
$from_personal = $reply_worker->getName() . (!empty($from_personal) ? ', ' . $from_personal : "");
}
// Headers
$mail->setFrom(array($from_addy => $from_personal));
$mail->generateId();
$headers = $mail->getHeaders();
$headers->addTextHeader('X-Mailer', 'Cerberus Helpdesk (Build ' . APP_BUILD . ')');
// Subject
if (empty($subject)) {
$subject = $ticket->subject;
}
if (!empty($properties['to'])) {
// forward
$mail->setSubject($subject);
} else {
// reply
@($group_has_subject = intval(DAO_GroupSettings::get($ticket->team_id, DAO_GroupSettings::SETTING_SUBJECT_HAS_MASK, 0)));
@($group_subject_prefix = DAO_GroupSettings::get($ticket->team_id, DAO_GroupSettings::SETTING_SUBJECT_PREFIX, ''));
$prefix = sprintf("[%s#%s] ", !empty($group_subject_prefix) ? $group_subject_prefix . ' ' : '', $ticket->mask);
$mail->setSubject(sprintf('Re: %s%s', $group_has_subject ? $prefix : '', $subject));
}
// References
if (!empty($message) && false !== @($in_reply_to = $message_headers['message-id'])) {
$headers->addTextHeader('References', $in_reply_to);
$headers->addTextHeader('In-Reply-To', $in_reply_to);
}
// Auto-reply handling (RFC-3834 compliant)
if (isset($properties['is_autoreply']) && $properties['is_autoreply']) {
$headers->addTextHeader('Auto-Submitted', 'auto-replied');
if (null == ($first_address = DAO_Address::get($ticket->first_wrote_address_id))) {
return;
}
// Don't send e-mail to ourselves
if (isset($helpdesk_senders[$first_address->email])) {
return;
}
// Make sure we haven't mailed this address an autoreply within 5 minutes
if ($first_address->last_autoreply > 0 && $first_address->last_autoreply > time() - 300) {
return;
}
$first_email = strtolower($first_address->email);
$first_split = explode('@', $first_email);
if (!is_array($first_split) || count($first_split) != 2) {
return;
}
// If return-path is blank
if (isset($message_headers['return-path']) && $message_headers['return-path'] == '<>') {
return;
}
// Ignore bounces
if ($first_split[0] == "postmaster" || $first_split[0] == "mailer-daemon") {
return;
}
// Ignore autoresponses to autoresponses
if (isset($message_headers['auto-submitted']) && $message_headers['auto-submitted'] != 'no') {
return;
}
if (isset($message_headers['precedence']) && ($message_headers['precedence'] == 'list' || $message_headers['precedence'] == 'junk' || ($message_headers['precedence'] = 'bulk'))) {
return;
}
// Set the auto-reply date for this address to right now
DAO_Address::update($ticket->first_wrote_address_id, array(DAO_Address::LAST_AUTOREPLY => time()));
// Auto-reply just to the initial requester
$mail->addTo($first_address->email);
// Not an auto-reply
} else {
// Forwards
if (!empty($properties['to'])) {
$aTo = DevblocksPlatform::parseCsvString(str_replace(';', ',', $properties['to']));
if (is_array($aTo)) {
foreach ($aTo as $to_addy) {
$mail->addTo($to_addy);
}
}
// Replies
} else {
// Recipients
$requesters = DAO_Ticket::getRequestersByTicket($ticket_id);
if (is_array($requesters)) {
foreach ($requesters as $requester) {
/* @var $requester Model_Address */
$mail->addTo($requester->email);
}
}
}
// Ccs
if (!empty($properties['cc'])) {
$aCc = DevblocksPlatform::parseCsvString(str_replace(';', ',', $properties['cc']));
$mail->setCc($aCc);
}
// Bccs
if (!empty($properties['bcc'])) {
$aBcc = DevblocksPlatform::parseCsvString(str_replace(';', ',', $properties['bcc']));
$mail->setBcc($aBcc);
}
}
/*
* [IMPORTANT -- Yes, this is simply a line in the sand.]
* You're welcome to modify the code to meet your needs, but please respect
* our licensing. Buy a legitimate copy to help support the project!
* http://www.cerberusweb.com/
*/
$license = CerberusLicense::getInstance();
if (empty($license) || @empty($license['serial'])) {
$content .= base64_decode("DQoNCi0tLQ0KQ29tYmF0IHNwYW0gYW5kIGltcHJvdmUgcmVzc" . "G9uc2UgdGltZXMgd2l0aCBDZXJiZXJ1cyBIZWxwZGVzayA0LjAhDQpodHRwOi8vd3d3LmNlc" . "mJlcnVzd2ViLmNvbS8NCg");
}
// Body
$mail->setBody($content);
// Mime Attachments
if (is_array($files) && !empty($files)) {
foreach ($files['tmp_name'] as $idx => $file) {
if (empty($file) || empty($files['name'][$idx])) {
continue;
}
$mail->attach(Swift_Attachment::fromPath($file)->setFilename($files['name'][$idx]));
}
}
// Forward Attachments
if (!empty($forward_files) && is_array($forward_files)) {
$attachments_path = APP_STORAGE_PATH . '/attachments/';
foreach ($forward_files as $file_id) {
$attachment = DAO_Attachment::get($file_id);
$attachment_path = $attachments_path . $attachment->filepath;
$mail->attach(Swift_Attachment::fromPath($attachment_path)->setFilename($attachment->display_name));
}
}
if (!DEMO_MODE) {
// If we're not supposed to send
if (isset($properties['dont_send']) && $properties['dont_send']) {
// ...do nothing
} else {
// otherwise send
if (!$mailer->send($mail)) {
$mail_succeeded = false;
throw new Exception('Mail not sent.');
}
}
}
} catch (Exception $e) {
// tag failure, so we can add a note to the message later
$mail_succeeded = false;
}
// Handle post-mail actions
$change_fields = array();
$fromAddressInst = CerberusApplication::hashLookupAddress($from_addy, true);
$fromAddressId = $fromAddressInst->id;
if ((!isset($properties['dont_keep_copy']) || !$properties['dont_keep_copy']) && (!isset($properties['is_autoreply']) || !$properties['is_autoreply'])) {
$change_fields[DAO_Ticket::LAST_WROTE_ID] = $fromAddressId;
$change_fields[DAO_Ticket::UPDATED_DATE] = time();
if (!empty($worker_id)) {
$change_fields[DAO_Ticket::LAST_WORKER_ID] = $worker_id;
$change_fields[DAO_Ticket::LAST_ACTION_CODE] = CerberusTicketActionCode::TICKET_WORKER_REPLY;
}
// Only change the subject if not forwarding
if (!empty($subject) && empty($properties['to'])) {
$change_fields[DAO_Ticket::SUBJECT] = $subject;
}
$fields = array(DAO_Message::TICKET_ID => $ticket_id, DAO_Message::CREATED_DATE => time(), DAO_Message::ADDRESS_ID => $fromAddressId, DAO_Message::IS_OUTGOING => 1, DAO_Message::WORKER_ID => !empty($worker_id) ? $worker_id : 0);
$message_id = DAO_Message::create($fields);
// Content
DAO_MessageContent::create($message_id, $content);
$headers = $mail->getHeaders();
// Headers
foreach ($headers->getAll() as $hdr) {
if (null != ($hdr_val = $hdr->getFieldBody())) {
if (!empty($hdr_val)) {
DAO_MessageHeader::create($message_id, $hdr->getFieldName(), CerberusParser::fixQuotePrintableString($hdr_val));
}
}
}
// Attachments
if (is_array($files) && !empty($files)) {
$attachment_path = APP_STORAGE_PATH . '/attachments/';
reset($files);
foreach ($files['tmp_name'] as $idx => $file) {
if (empty($file) || empty($files['name'][$idx]) || !file_exists($file)) {
continue;
}
$fields = array(DAO_Attachment::MESSAGE_ID => $message_id, DAO_Attachment::DISPLAY_NAME => $files['name'][$idx], DAO_Attachment::MIME_TYPE => $files['type'][$idx], DAO_Attachment::FILE_SIZE => filesize($file));
$file_id = DAO_Attachment::create($fields);
$attachment_bucket = sprintf("%03d/", mt_rand(1, 100));
$attachment_file = $file_id;
if (!file_exists($attachment_path . $attachment_bucket)) {
mkdir($attachment_path . $attachment_bucket, 0775, true);
}
if (!is_writeable($attachment_path . $attachment_bucket)) {
echo "Can't write to bucket " . $attachment_path . $attachment_bucket . "<BR>";
}
copy($file, $attachment_path . $attachment_bucket . $attachment_file);
@unlink($file);
DAO_Attachment::update($file_id, array(DAO_Attachment::FILEPATH => $attachment_bucket . $attachment_file));
}
}
// add note to message if email failed
if ($mail_succeeded === false) {
$fields = array(DAO_MessageNote::MESSAGE_ID => $message_id, DAO_MessageNote::CREATED => time(), DAO_MessageNote::WORKER_ID => 0, DAO_MessageNote::CONTENT => 'Exception thrown while sending email: ' . $e->getMessage(), DAO_MessageNote::TYPE => Model_MessageNote::TYPE_ERROR);
DAO_MessageNote::create($fields);
}
}
// Post-Reply Change Properties
if (isset($properties['closed'])) {
switch ($properties['closed']) {
case 0:
// open
$change_fields[DAO_Ticket::IS_WAITING] = 0;
$change_fields[DAO_Ticket::IS_CLOSED] = 0;
$change_fields[DAO_Ticket::IS_DELETED] = 0;
$change_fields[DAO_Ticket::DUE_DATE] = 0;
break;
case 1:
// closed
$change_fields[DAO_Ticket::IS_WAITING] = 0;
$change_fields[DAO_Ticket::IS_CLOSED] = 1;
$change_fields[DAO_Ticket::IS_DELETED] = 0;
if (isset($properties['ticket_reopen'])) {
@($time = intval(strtotime($properties['ticket_reopen'])));
$change_fields[DAO_Ticket::DUE_DATE] = $time;
}
break;
case 2:
// waiting
$change_fields[DAO_Ticket::IS_WAITING] = 1;
$change_fields[DAO_Ticket::IS_CLOSED] = 0;
$change_fields[DAO_Ticket::IS_DELETED] = 0;
if (isset($properties['ticket_reopen'])) {
@($time = intval(strtotime($properties['ticket_reopen'])));
$change_fields[DAO_Ticket::DUE_DATE] = $time;
}
break;
}
}
// Who should handle the followup?
if (isset($properties['next_worker_id'])) {
$change_fields[DAO_Ticket::NEXT_WORKER_ID] = $properties['next_worker_id'];
}
// Allow anybody to reply after
if (isset($properties['unlock_date']) && !empty($properties['unlock_date'])) {
$unlock = strtotime($properties['unlock_date']);
if (intval($unlock) > 0) {
$change_fields[DAO_Ticket::UNLOCK_DATE] = $unlock;
}
}
// Move
if (!empty($properties['bucket_id'])) {
// [TODO] Use API to move, or fire event
// [TODO] Ensure team/bucket exist
list($team_id, $bucket_id) = CerberusApplication::translateTeamCategoryCode($properties['bucket_id']);
$change_fields[DAO_Ticket::TEAM_ID] = $team_id;
$change_fields[DAO_Ticket::CATEGORY_ID] = $bucket_id;
}
if (!empty($ticket_id) && !empty($change_fields)) {
DAO_Ticket::updateTicket($ticket_id, $change_fields);
}
// Outbound Reply Event (not automated reply, etc.)
if (!empty($worker_id)) {
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('ticket.reply.outbound', array('ticket_id' => $ticket_id, 'worker_id' => $worker_id)));
}
}
function AnswernetMetlifeReportGroupReport1Action()
{
$db = DevblocksPlatform::getDatabaseService();
$translate = DevblocksPlatform::getTranslationService();
$url = DevblocksPlatform::getUrlService();
$workers = DAO_Worker::getAll();
$radius = 12;
// Security
if (null == ($active_worker = CerberusApplication::getActiveWorker())) {
die($translate->_('common.access_denied'));
}
// import dates from form
@($start_time = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', ''));
@($group = DevblocksPlatform::importGPC($_REQUEST['group'], 'string', ''));
if (empty($start_time) || !is_numeric($start_time)) {
return;
}
if (empty($group)) {
return;
}
$end_time = $start_time + 604800;
print $translate->_('answernet.er.metlife.week.number');
print date("W", $start_time);
print '<br>';
print $translate->_('answernet.er.metlife.generate.report');
switch ($group) {
case "All":
$filename = "report-metlife-week-" . date("W", $start_time) . ".xls";
$group_sql = "and (t.team_id = 756 or t.team_id = 782) ";
print " Group: ALL ";
$group_text = "All";
break;
case "fp":
$filename = "report-metlife-first-person-week-" . date("W", $start_time) . ".xls";
$group_sql = "and t.team_id = 756 ";
print " Group: First Person ";
$group_text = "First Person";
break;
case "iDesign":
$filename = "report-metlife-iDesign-week-" . date("W", $start_time) . ".xls";
$group_sql = "and team_id = 782 ";
print " Group: iDesign ";
$group_text = "iDesign";
break;
default:
print "Error: Group not set: " . $group;
return;
}
print '<br>';
print $translate->_('answernet.er.metlife.generating');
$full_filename = getcwd() . '/storage/answernet/' . $filename;
$href_filename = $url->write('storage/answernet/' . $filename, true);
$week_range_text = "Week # " . date("W - n/j/y", $start_time) . " - " . date("n/j/y", $start_time + 518400);
// Create new Excel Spreadsheet.
$workbook = new Spreadsheet_Excel_Writer($full_filename);
// Create metrics Tab and set Column Width and Row Hight.
$worksheet_metrics =& $workbook->addWorksheet('Weekly Metrics');
$worksheet_metrics->setColumn(0, 0, $radius * 1.71);
$worksheet_metrics->setColumn(0, 0, $radius * 0.5);
$worksheet_metrics->setRow(0, 56);
// Create ACD Calls(Inbound) Tab and set Column Width and Row Hight.
$worksheet_acd_in =& $workbook->addWorksheet('ACD calls(Inbound)');
$worksheet_acd_in->setColumn(0, 1, $radius * 0.78);
$worksheet_acd_in->setColumn(2, 2, $radius * 1.05);
$worksheet_acd_in->setColumn(3, 3, $radius * 1.23);
$worksheet_acd_in->setColumn(4, 4, $radius * 1.11);
$worksheet_acd_in->setColumn(5, 5, $radius * 1.15);
$worksheet_acd_in->setColumn(6, 6, $radius * 2.0);
$worksheet_acd_in->setColumn(7, 7, $radius * 0.78);
$worksheet_acd_in->setColumn(8, 8, $radius * 2.0);
$worksheet_acd_in->setColumn(9, 9, $radius * 0.78);
$worksheet_acd_in->setRow(0, 28);
$worksheet_acd_in->setRow(2, 32);
// Create ACD Calls(Outbound) Tab and set Column Width and Row Hight.
$worksheet_acd_out =& $workbook->addWorksheet('ACD calls(Outbound)');
// Create Phone Tickets Tab and set Column Width and Row Hight.
$worksheet_phone_tickets =& $workbook->addWorksheet('Phone Tickets');
// Create Email Tickets Tab and set Column Width and Row Hight.
$worksheet_email_tickets =& $workbook->addWorksheet('Email Tickets');
// Create Email Tickets Tab and set Column Width and Row Hight.
$worksheet_call_count =& $workbook->addWorksheet('Call Count');
// Create Inbound Count Tab and set Column Width and Row Hight.
$worksheet_in_count =& $workbook->addWorksheet('Inbound Email Count');
$worksheet_in_count->setColumn(0, 0, $radius * 2.87);
$worksheet_in_count->setColumn(1, 1, $radius * 0.66);
$worksheet_in_count->setColumn(2, 2, $radius * 2.87);
$worksheet_in_count->setColumn(3, 3, $radius * 0.62);
$worksheet_in_count->setColumn(4, 4, $radius * 0.66);
$worksheet_in_count->setColumn(5, 5, $radius * 2.87);
$worksheet_in_count->setColumn(6, 6, $radius * 0.66);
$worksheet_in_count->setRow(0, 32);
$worksheet_in_count->freezePanes(array(2, 0, 2, 0));
// Create Outbound Count Tab and set Column Width and Row Hight.
$worksheet_out_count =& $workbook->addWorksheet('Outbound Email Count');
$worksheet_out_count->setColumn(0, 0, $radius * 2.87);
$worksheet_out_count->setColumn(1, 1, $radius * 0.66);
$worksheet_out_count->setColumn(2, 2, $radius * 2.87);
$worksheet_out_count->setColumn(3, 3, $radius * 0.62);
$worksheet_out_count->setColumn(4, 4, $radius * 0.66);
$worksheet_out_count->setColumn(5, 5, $radius * 2.87);
$worksheet_out_count->setColumn(6, 6, $radius * 0.66);
$worksheet_out_count->setRow(0, 32);
$worksheet_out_count->freezePanes(array(2, 0, 2, 0));
// Create Inbound Tab and set Column Width and Row Hight.
$worksheet_inbound =& $workbook->addWorksheet('Inbound Emails');
$worksheet_inbound->setColumn(0, 0, $radius * 2.78);
$worksheet_inbound->setColumn(1, 1, $radius * 1.55);
$worksheet_inbound->setColumn(2, 2, $radius * 0.72);
$worksheet_inbound->setColumn(3, 4, $radius * 1.51);
$worksheet_inbound->setColumn(5, 5, $radius * 2.76);
$worksheet_inbound->setColumn(6, 9, $radius * 2.22);
$worksheet_inbound->setColumn(10, 10, $radius * 0.83);
$worksheet_inbound->setRow(0, 36);
$worksheet_inbound->freezePanes(array(2, 0, 2, 0));
// Create Outbound Tab and set Column Width and Row Hight.
$worksheet_outbound =& $workbook->addWorksheet('Outbound Emails');
$worksheet_outbound->setColumn(0, 0, $radius * 2.78);
$worksheet_outbound->setColumn(1, 1, $radius * 1.55);
$worksheet_outbound->setColumn(2, 2, $radius * 0.72);
$worksheet_outbound->setColumn(3, 4, $radius * 1.51);
$worksheet_outbound->setColumn(5, 5, $radius * 2.76);
$worksheet_outbound->setColumn(6, 9, $radius * 2.22);
$worksheet_outbound->setColumn(10, 10, $radius * 1.2);
$worksheet_outbound->setColumn(11, 11, $radius * 0.83);
$worksheet_outbound->setRow(0, 36);
$worksheet_outbound->freezePanes(array(2, 0, 2, 0));
// Formats used thoughout the workbook.
$format_general =& $workbook->addFormat();
$format_general->setBorder(1);
$format_general->setHAlign('center');
$format_general->setTextWrap();
$format_general_nowrap =& $workbook->addFormat();
$format_general_nowrap->setBorder(1);
// Setup templating for the formating of certain cells in the Metics Group.
$format_metrics_title =& $workbook->addFormat();
$format_metrics_title->setBorder(1);
$format_metrics_title->setBold();
$format_metrics_title->setColor(9);
$format_metrics_title->setFgColor(32);
$format_metrics_title->setHAlign('center');
$format_metrics_title->setVAlign('vjustify');
$format_metrics_title->setVAlign('vcenter');
$format_metrics_title->setTextWrap();
$format_metrics_title2 =& $workbook->addFormat();
$format_metrics_title2->setBorder(1);
$format_metrics_title2->setBold();
$format_metrics_title2->setColor(8);
$format_metrics_title2->setFgColor(43);
$format_metrics_title2->setHAlign('center');
$format_metrics_title2->setVAlign('vjustify');
$format_metrics_title2->setVAlign('vcenter');
$format_metrics_title2->setTextWrap();
$format_metrics_weekly =& $workbook->addFormat();
$format_metrics_weekly->setBorder(1);
$format_metrics_weekly->setBold();
$format_metrics_weekly->setColor(8);
$format_metrics_weekly->setFgColor(29);
$format_metrics_weekly->setHAlign('center');
$format_metrics_weekly->setVAlign('vjustify');
$format_metrics_weekly->setVAlign('vcenter');
$format_metrics_weekly->setTextWrap();
$format_metrics_daily =& $workbook->addFormat();
$format_metrics_daily->setBorder(1);
$format_metrics_daily->setBold();
$format_metrics_daily->setColor(8);
$format_metrics_daily->setFgColor(29);
$format_metrics_daily->setHAlign('center');
$format_metrics_daily->setVAlign('vjustify');
$format_metrics_daily->setVAlign('vcenter');
$format_metrics_daily->setTextWrap();
// Added headers since they never change in the Metics Group.
$worksheet_metrics->write(0, 0, 'Week Range', $format_metrics_title);
$worksheet_metrics->write(0, 1, 'Inbnd Field Emails', $format_metrics_title);
$worksheet_metrics->write(0, 2, 'Inbnd Admin Emails', $format_metrics_title);
$worksheet_metrics->write(0, 3, 'Weekly Total Inbnd', $format_metrics_title);
$worksheet_metrics->write(0, 4, 'Outbnd Field Emails', $format_metrics_title);
$worksheet_metrics->write(0, 5, 'Outbnd Admin Emails', $format_metrics_title);
$worksheet_metrics->write(0, 6, 'Weekly Total Outbnd', $format_metrics_title);
$worksheet_metrics->write(0, 7, 'Avg time to respond (hrs)', $format_metrics_title);
$worksheet_metrics->write(0, 8, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 9, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 10, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 11, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 12, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 13, ' ', $format_metrics_title2);
$worksheet_metrics->write(0, 14, ' ', $format_metrics_title2);
$week_range_text_metrics = date("n/j/y", $start_time) . " - " . date("n/j/y", $start_time + 518400);
$worksheet_metrics->write(1, 0, $week_range_text_metrics, $format_general);
$worksheet_metrics->write(5, 0, 'Grand Total', $format_general);
$worksheet_metrics->write(6, 0, 'Weekly Averages', $format_metrics_weekly);
$worksheet_metrics->write(7, 0, 'Daily Averages', $format_metrics_daily);
$worksheet_metrics->write(8, 0, '%', $format_general);
// Setup templating for the formating of certain cells in the Inbound Count Group.
$format_acd_in_title =& $workbook->addFormat();
$format_acd_in_title->setSize(18);
$format_acd_in_title->setColor(8);
$format_acd_in_title->setFgColor(34);
$format_acd_in_title->setBorder(1);
$format_acd_in_title->setBold();
$format_acd_in_title->setHAlign('center');
$format_acd_in_title->setVAlign('vjustify');
$format_acd_in_title->setVAlign('top');
$format_acd_in_title->setTextWrap();
$format_acd_in_title->setAlign('merge');
$format_acd_in_title2 =& $workbook->addFormat();
$format_acd_in_title2->setColor(8);
$format_acd_in_title2->setFgColor(43);
$format_acd_in_title2->setBorder(1);
$format_acd_in_title2->setBold();
$format_acd_in_title2->setHAlign('center');
$format_acd_in_title2->setVAlign('vjustify');
$format_acd_in_title2->setVAlign('top');
$format_acd_in_title2->setTextWrap();
$format_acd_in_title2->setAlign('merge');
$format_acd_in_title3 =& $workbook->addFormat();
$format_acd_in_title3->setColor(8);
$format_acd_in_title3->setFgColor(47);
$format_acd_in_title2->setBorder(1);
$format_acd_in_title3->setBold();
$format_acd_in_title3->setHAlign('center');
$format_acd_in_title3->setVAlign('vjustify');
$format_acd_in_title3->setVAlign('top');
$format_acd_in_title3->setTextWrap();
$format_acd_in_total =& $workbook->addFormat();
$format_acd_in_title->setSize(15);
$format_acd_in_total->setBorder(1);
$format_acd_in_total->setColor(8);
$format_acd_in_total->setFgColor(6);
$format_acd_in_total->setBold();
$format_acd_in_total->setHAlign('left');
$format_acd_in_total->setVAlign('vjustify');
$format_acd_in_total->setVAlign('top');
// Added headers since they never change in the acd in Group.
$worksheet_acd_in->write(0, 0, 'MetLife / ' . $group_text, $format_acd_in_title);
$worksheet_acd_in->write(0, 1, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 2, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 3, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 4, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 5, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 6, 'WEEKLY TOTALS', $format_acd_in_title);
$worksheet_acd_in->write(0, 7, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 8, '', $format_acd_in_title);
$worksheet_acd_in->write(0, 9, '', $format_acd_in_title);
$worksheet_acd_in->write(1, 0, $week_range_text, $format_acd_in_title2);
$worksheet_acd_in->write(1, 1, '', $format_acd_in_title2);
$worksheet_acd_in->write(1, 2, '', $format_acd_in_title2);
$worksheet_acd_in->write(1, 3, '', $format_acd_in_title2);
$worksheet_acd_in->write(1, 4, '', $format_acd_in_title2);
$worksheet_acd_in->write(1, 5, '', $format_acd_in_title2);
$worksheet_acd_in->write(2, 0, 'Date', $format_acd_in_title3);
$worksheet_acd_in->write(2, 1, 'Call Times', $format_acd_in_title3);
$worksheet_acd_in->write(2, 2, 'Agent Talk', $format_acd_in_title3);
$worksheet_acd_in->write(2, 3, '(MIN.SEC) Hold time', $format_acd_in_title3);
$worksheet_acd_in->write(2, 4, '(MIN.SEC) Patch Time', $format_acd_in_title3);
$worksheet_acd_in->write(2, 5, 'ANI', $format_acd_in_title3);
$worksheet_acd_in->write(1, 6, 'Weekly Total Calls', $format_acd_in_total);
$worksheet_acd_in->writeFormula(1, 7, "=count(A1:A1000)", $format_acd_in_title2);
$worksheet_acd_in->write(1, 8, 'Weekly Average Patch Time', $format_acd_in_total);
$worksheet_acd_in->writeFormula(1, 9, "=SUM(B2,E2)", $format_acd_in_title2);
$worksheet_acd_in->write(2, 6, 'Weekly Agent Talk Time', $format_acd_in_total);
$worksheet_acd_in->writeFormula(2, 7, "=SUM(B2,E2)", $format_acd_in_title2);
$worksheet_acd_in->write(2, 8, 'Weekly Average Hold Time', $format_acd_in_total);
$worksheet_acd_in->writeFormula(2, 9, "=SUM(B2,E2)", $format_acd_in_title2);
// Setup templating for the formating of certain cells in the Inbound Count Group.
$format_in_count_title =& $workbook->addFormat();
$format_in_count_title->setSize(15);
$format_in_count_title->setColor(8);
$format_in_count_title->setBorder(1);
$format_in_count_title->setFgColor(35);
$format_in_count_title->setBold();
$format_in_count_title->setHAlign('center');
$format_in_count_title->setVAlign('vjustify');
$format_in_count_title->setVAlign('top');
$format_in_count_title->setTextWrap();
$format_in_count_title2 =& $workbook->addFormat();
$format_in_count_title2->setColor(8);
$format_in_count_title2->setFgColor(47);
$format_in_count_title2->setBold();
$format_in_count_title2->setHAlign('center');
$format_in_count_title2->setVAlign('vjustify');
$format_in_count_title2->setVAlign('top');
$format_in_count_title2->setTextWrap();
$format_in_count_total =& $workbook->addFormat();
$format_in_count_total->setBorder(1);
$format_in_count_total->setColor(8);
$format_in_count_total->setFgColor(6);
$format_in_count_total->setBold();
$format_in_count_total->setSize(18);
$format_in_count_total->setHAlign('left');
$format_in_count_total->setVAlign('vjustify');
$format_in_count_total->setVAlign('top');
$format_in_count_grand =& $workbook->addFormat();
$format_in_count_grand->setBorder(1);
$format_in_count_grand->setColor(8);
$format_in_count_grand->setFgColor(43);
$format_in_count_grand->setBold();
$format_in_count_grand->setSize(18);
$format_in_count_grand->setHAlign('left');
$format_in_count_grand->setVAlign('vjustify');
$format_in_count_grand->setVAlign('top');
// Added headers since they never change in the Inbound Count Group.
$worksheet_in_count->write(0, 0, 'Email Count Admin', $format_in_count_title);
$worksheet_in_count->write(0, 1, 'Totals', $format_in_count_title);
$worksheet_in_count->write(0, 2, 'Email Count Field', $format_in_count_title);
$worksheet_in_count->write(0, 3, 'ID', $format_in_count_title);
$worksheet_in_count->write(0, 4, 'Totals', $format_in_count_title);
// Setup templating for the formating of certain cells in the Outbound Count Group.
$format_out_count_title =& $workbook->addFormat();
$format_out_count_title->setSize(15);
$format_out_count_title->setColor(8);
$format_out_count_title->setBorder(1);
$format_out_count_title->setFgColor(14);
$format_out_count_title->setBold();
$format_out_count_title->setHAlign('center');
$format_out_count_title->setVAlign('vjustify');
$format_out_count_title->setVAlign('top');
$format_out_count_title->setTextWrap();
$format_out_count_title2 =& $workbook->addFormat();
$format_out_count_title2->setColor(8);
$format_out_count_title2->setFgColor(47);
$format_out_count_title2->setBold();
$format_out_count_title2->setHAlign('center');
$format_out_count_title2->setVAlign('vjustify');
$format_out_count_title2->setVAlign('top');
$format_out_count_title2->setTextWrap();
$format_out_count_total =& $workbook->addFormat();
$format_out_count_total->setBorder(1);
$format_out_count_total->setColor(8);
$format_out_count_total->setFgColor(42);
$format_out_count_total->setBold();
$format_out_count_total->setSize(18);
$format_out_count_total->setHAlign('left');
$format_out_count_total->setVAlign('vjustify');
$format_out_count_total->setVAlign('top');
$format_out_count_grand =& $workbook->addFormat();
$format_out_count_grand->setBorder(1);
$format_out_count_grand->setColor(8);
$format_out_count_grand->setFgColor(43);
$format_out_count_grand->setBold();
$format_out_count_grand->setSize(18);
$format_out_count_grand->setHAlign('left');
$format_out_count_grand->setVAlign('vjustify');
$format_out_count_grand->setVAlign('top');
// Added headers since they never change in the Outbound Count Group.
$worksheet_out_count->write(0, 0, 'Email Count Admin', $format_out_count_title);
$worksheet_out_count->write(0, 1, 'Totals', $format_out_count_title);
$worksheet_out_count->write(0, 2, 'Email Count Field', $format_out_count_title);
$worksheet_out_count->write(0, 3, 'ID', $format_out_count_title);
$worksheet_out_count->write(0, 4, 'Totals', $format_out_count_title);
// Setup templating for the formating of certain cells in the Inbound Group.
$format_inbound_title =& $workbook->addFormat();
$format_inbound_title->setSize(15);
$format_inbound_title->setColor(8);
$format_inbound_title->setBorder(1);
$format_inbound_title->setFgColor(35);
$format_inbound_title->setBold();
$format_inbound_title->setHAlign('center');
$format_inbound_title->setVAlign('vjustify');
$format_inbound_title->setVAlign('top');
$format_inbound_title->setTextWrap();
$format_inbound_title2 =& $workbook->addFormat();
$format_inbound_title2->setColor(8);
$format_inbound_title2->setFgColor(47);
$format_inbound_title2->setBold();
$format_inbound_title2->setHAlign('center');
$format_inbound_title2->setVAlign('vjustify');
$format_inbound_title2->setVAlign('top');
$format_inbound_title2->setTextWrap();
// Setup templating for the formating of certain cells in the Inbound Group.
$format_inbound_title3 =& $workbook->addFormat();
$format_inbound_title3->setSize(15);
$format_inbound_title3->setColor(8);
$format_inbound_title3->setBorder(1);
$format_inbound_title3->setFgColor(34);
$format_inbound_title3->setBold();
$format_inbound_title3->setHAlign('center');
$format_inbound_title3->setVAlign('vjustify');
$format_inbound_title3->setVAlign('top');
$format_inbound_title3->setTextWrap();
// Added headers since they never change in the Inbound Group.
$worksheet_inbound->setInputEncoding('utf-8');
$worksheet_inbound->write(0, 0, 'Inbound Email From', $format_inbound_title);
$worksheet_inbound->write(0, 1, 'User Name', $format_inbound_title);
$worksheet_inbound->write(0, 2, 'ID', $format_inbound_title);
$worksheet_inbound->write(0, 3, 'Date Email Received', $format_inbound_title);
$worksheet_inbound->write(0, 4, 'Ticket Mask', $format_inbound_title);
$worksheet_inbound->write(0, 5, 'Subject Line', $format_inbound_title);
$worksheet_inbound->write(0, 6, 'Email Contents', $format_inbound_title);
$worksheet_inbound->write(0, 7, 'Category', $format_inbound_title3);
$worksheet_inbound->write(0, 8, 'Code', $format_inbound_title3);
$worksheet_inbound->write(0, 9, 'Description(or snapshot)', $format_inbound_title3);
$worksheet_inbound->write(0, 10, 'Group', $format_inbound_title);
$worksheet_inbound->write(1, 0, $week_range_text, $format_inbound_title2);
$worksheet_inbound->write(1, 1, "", $format_inbound_title2);
$worksheet_inbound->write(1, 2, "", $format_inbound_title2);
$worksheet_inbound->write(1, 3, "", $format_inbound_title2);
$worksheet_inbound->write(1, 4, "", $format_inbound_title2);
$worksheet_inbound->write(1, 5, "", $format_inbound_title2);
$worksheet_inbound->write(1, 6, "", $format_inbound_title2);
$worksheet_inbound->write(1, 7, "", $format_inbound_title2);
$worksheet_inbound->write(1, 8, "", $format_inbound_title2);
$worksheet_inbound->write(1, 9, "", $format_inbound_title2);
$worksheet_inbound->write(1, 10, "", $format_inbound_title2);
// Setup templating for the formating of certain cells in the Outbound Group.
$format_outbound_title =& $workbook->addFormat();
$format_outbound_title->setSize(15);
$format_outbound_title->setColor(8);
$format_outbound_title->setBorder(1);
$format_outbound_title->setFgColor(11);
$format_outbound_title->setBold();
$format_outbound_title->setHAlign('center');
$format_outbound_title->setVAlign('vjustify');
$format_outbound_title->setVAlign('top');
$format_outbound_title->setTextWrap();
$format_outbound_title2 =& $workbook->addFormat();
$format_outbound_title2->setColor(8);
$format_outbound_title2->setFgColor(6);
$format_outbound_title2->setBold();
$format_outbound_title2->setHAlign('center');
$format_outbound_title2->setVAlign('vjustify');
$format_outbound_title2->setVAlign('top');
$format_outbound_title2->setTextWrap();
$format_outbound_title3 =& $workbook->addFormat();
$format_outbound_title3->setSize(15);
$format_outbound_title3->setColor(8);
$format_outbound_title3->setBorder(1);
$format_outbound_title3->setFgColor(34);
$format_outbound_title3->setBold();
$format_outbound_title3->setHAlign('center');
$format_outbound_title3->setVAlign('vjustify');
$format_outbound_title3->setVAlign('top');
$format_outbound_title3->setTextWrap();
// Added headers since they never change in the Outbound Group.
$worksheet_outbound->setInputEncoding('utf-8');
$worksheet_outbound->write(0, 0, 'Outbound Email To', $format_outbound_title);
$worksheet_outbound->write(0, 1, 'User Name', $format_outbound_title);
$worksheet_outbound->write(0, 2, 'ID', $format_outbound_title);
$worksheet_outbound->write(0, 3, 'Date Email Sent', $format_outbound_title);
$worksheet_outbound->write(0, 4, 'Ticket Mask', $format_outbound_title);
$worksheet_outbound->write(0, 5, 'Subject Line', $format_outbound_title);
$worksheet_outbound->write(0, 6, 'Email Contents', $format_outbound_title);
$worksheet_outbound->write(0, 7, 'Category', $format_outbound_title3);
$worksheet_outbound->write(0, 8, 'Code', $format_outbound_title3);
$worksheet_outbound->write(0, 9, 'Description(or snapshot)', $format_outbound_title3);
$worksheet_outbound->write(0, 10, 'Responder', $format_outbound_title);
$worksheet_outbound->write(0, 11, 'Group', $format_outbound_title);
$worksheet_outbound->write(1, 0, $week_range_text, $format_outbound_title2);
$worksheet_outbound->write(1, 1, "", $format_outbound_title2);
$worksheet_outbound->write(1, 2, "", $format_outbound_title2);
$worksheet_outbound->write(1, 3, "", $format_outbound_title2);
$worksheet_outbound->write(1, 4, "", $format_outbound_title2);
$worksheet_outbound->write(1, 5, "", $format_outbound_title2);
$worksheet_outbound->write(1, 6, "", $format_outbound_title2);
$worksheet_outbound->write(1, 7, "", $format_outbound_title2);
$worksheet_outbound->write(1, 8, "", $format_outbound_title2);
$worksheet_outbound->write(1, 9, "", $format_outbound_title2);
$worksheet_outbound->write(1, 10, "", $format_outbound_title2);
$worksheet_outbound->write(1, 11, "", $format_outbound_title2);
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('answernet.er.metlife.generating.email.detail');
$groups = DAO_Group::getAll();
$buckets = DAO_Bucket::getAll();
$sql = "SELECT t.mask, a.email, m.address_id, a.contact_org_id, ";
$sql .= "t.created_date ticket_created_date, t.team_id, ";
$sql .= "m.created_date message_created_date, mc.content, ";
$sql .= "mh.header_value message_subject, m.worker_id, ";
$sql .= "m.is_outgoing, mh_to.header_value outbound_email, t.team_id ";
$sql .= "FROM message m ";
$sql .= "INNER JOIN ticket t ON m.ticket_id = t.id ";
$sql .= "INNER JOIN address a ON m.address_id = a.id ";
$sql .= "INNER JOIN message_content mc on m.id = mc.message_id ";
$sql .= "INNER JOIN message_header mh on m.id = mh.message_id ";
$sql .= "and mh.header_name = 'subject' ";
$sql .= "INNER JOIN message_header mh_to on m.id = mh_to.message_id ";
$sql .= "and mh_to.header_name = 'to' ";
$sql .= sprintf("WHERE m.created_date > %d AND m.created_date <= %d ", $start_time, $end_time);
// Set abouve based on group selected.
$sql .= $group_sql;
$sql .= "ORDER BY m.id ";
$rs = $db->Execute($sql);
$row_inbound = 2;
$row_outbound = 2;
$in_count_admin = array();
$in_count_other = array();
$out_count_admin = array();
$out_count_other = array();
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$mask = $rs->fields['mask'];
$ticket_created_date = intval($rs->fields['ticket_created_date']);
$team_id = intval($rs->fields['team_id']);
// Date Format Month/Day/Year Hour:Min:Sec AM/PM
$message_created_date = date("n/j/y g:i:s A", intval($rs->fields['message_created_date']));
$message_content = $rs->fields['content'];
$message_subject = $rs->fields['message_subject'];
$worker_id = $rs->fields['worker_id'];
$is_outgoing = $rs->fields['is_outgoing'];
if ($team_id == 756) {
$team_text = 'First Person';
} elseif ($team_id == 782) {
$team_text = 'iDesign';
} else {
$team_text = 'Error';
}
if ($worker_id) {
$worker_name = $workers[$worker_id]->first_name;
} else {
$worker_name = "";
}
if ($is_outgoing) {
$outbound_email = $rs->fields['outbound_email'];
$to = array();
$to = CerberusParser::parseRfcAddress($outbound_email);
@($toAddress = $to[0]->mailbox . '@' . $to[0]->host);
$toAddressInst = CerberusApplication::hashLookupAddress($toAddress, true);
$address_id = $toAddressInst->id;
$contact_org_id = $toAddressInst->contact_org_id;
$email = $toAddressInst->email;
} else {
$address_id = $rs->fields['address_id'];
$contact_org_id = $rs->fields['contact_org_id'];
$email = $rs->fields['email'];
}
if ($is_outgoing) {
$worksheet_outbound->setRow($row_outbound, 12);
$worksheet_outbound->write($row_outbound, 0, $email, $format_general);
$worksheet_outbound->write($row_outbound, 1, "", $format_general);
$worksheet_outbound->write($row_outbound, 2, "", $format_general);
$worksheet_outbound->write($row_outbound, 3, $message_created_date, $format_general);
$worksheet_outbound->write($row_outbound, 4, $mask, $format_general);
$worksheet_outbound->write($row_outbound, 5, trim($message_subject), $format_general_nowrap);
$worksheet_outbound->write($row_outbound, 6, trim(strip_tags($message_content)));
$worksheet_outbound->writeString($row_outbound, 10, $worker_name, $format_general);
$worksheet_outbound->write($row_outbound, 11, $team_text, $format_general);
$row_outbound++;
} else {
$worksheet_inbound->setRow($row_inbound, 12);
$worksheet_inbound->write($row_inbound, 0, $email, $format_general);
$worksheet_inbound->write($row_inbound, 1, "", $format_general);
$worksheet_inbound->write($row_inbound, 2, "", $format_general);
$worksheet_inbound->write($row_inbound, 3, $message_created_date, $format_general);
$worksheet_inbound->write($row_inbound, 4, $mask, $format_general);
$worksheet_inbound->write($row_inbound, 5, trim($message_subject), $format_general_nowrap);
$worksheet_inbound->writeString($row_inbound, 6, trim(strip_tags($message_content)));
$worksheet_inbound->write($row_inbound, 10, $team_text, $format_general);
$row_inbound++;
}
if ($is_outgoing) {
if ($contact_org_id == 1) {
if (!isset($out_count_admin[$address_id]['count'])) {
$out_count_admin[$address_id]['email'] = $email;
$out_count_admin[$address_id]['count'] = 1;
} else {
$out_count_admin[$address_id]['count']++;
}
} else {
if (!isset($out_count_other[$address_id]['count'])) {
$out_count_other[$address_id]['email'] = $email;
$out_count_other[$address_id]['count'] = 1;
} else {
$out_count_other[$address_id]['count']++;
}
}
} else {
if ($contact_org_id == 1) {
if (!isset($in_count_admin[$address_id]['count'])) {
$in_count_admin[$address_id]['email'] = $email;
$in_count_admin[$address_id]['count'] = 1;
} else {
$in_count_admin[$address_id]['count']++;
}
} else {
if (!isset($in_count_other[$address_id]['count'])) {
$in_count_other[$address_id]['email'] = $email;
$in_count_other[$address_id]['count'] = 1;
} else {
$in_count_other[$address_id]['count']++;
}
}
}
$rs->MoveNext();
}
}
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('answernet.er.metlife.generating.email.count');
$worksheet_in_count->setRow(1, 24);
$row_count = 2;
foreach ($in_count_admin as $record) {
$worksheet_in_count->write($row_count, 0, $record['email'], $format_general);
$worksheet_in_count->write($row_count, 1, $record['count'], $format_general);
$row_count++;
}
$worksheet_in_count->write(1, 0, 'Total Admin Email', $format_in_count_total);
$worksheet_in_count->writeFormula(1, 1, "=SUM(B3:B" . $row_count . ")", $format_in_count_total);
$row_count = 2;
foreach ($in_count_other as $record) {
$worksheet_in_count->write($row_count, 2, $record['email'], $format_general);
$worksheet_in_count->write($row_count, 3, '', $format_general);
$worksheet_in_count->write($row_count, 4, $record['count'], $format_general);
$row_count++;
}
$worksheet_in_count->write(1, 2, 'Total Field Email', $format_in_count_total);
$worksheet_in_count->write(1, 3, '', $format_in_count_total);
$worksheet_in_count->writeFormula(1, 4, "=SUM(E3:E" . $row_count . ")", $format_in_count_total);
// Grand Total
$worksheet_in_count->write(1, 5, 'Grand Total Email', $format_in_count_grand);
$worksheet_in_count->writeFormula(1, 6, "=SUM(B2,E2)", $format_in_count_grand);
$worksheet_out_count->setRow(1, 24);
$row_count = 2;
foreach ($out_count_admin as $record) {
$worksheet_out_count->write($row_count, 0, $record['email'], $format_general);
$worksheet_out_count->write($row_count, 1, $record['count'], $format_general);
$row_count++;
}
$worksheet_out_count->write(1, 0, 'Total Admin Email', $format_out_count_total);
$worksheet_out_count->writeFormula(1, 1, "=SUM(B3:B" . $row_count . ")", $format_out_count_total);
$row_count = 2;
foreach ($out_count_other as $record) {
$worksheet_out_count->write($row_count, 2, $record['email'], $format_general);
$worksheet_out_count->write($row_count, 3, '', $format_general);
$worksheet_out_count->write($row_count, 4, $record['count'], $format_general);
$row_count++;
}
$worksheet_out_count->write(1, 2, 'Total Field Email', $format_out_count_total);
$worksheet_out_count->write(1, 3, '', $format_out_count_total);
$worksheet_out_count->writeFormula(1, 4, "=SUM(E3:E" . $row_count . ")", $format_out_count_total);
// Grand Total
$worksheet_out_count->write(1, 5, 'Grand Total Email', $format_out_count_grand);
$worksheet_out_count->writeFormula(1, 6, "=SUM(B2,E2)", $format_out_count_grand);
$workbook->close();
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('ranswernet.er.metlife.generating');
print $translate->_('answernet.er.metlife.metlife.done');
print '<br><br>';
print '<b><a href=' . $href_filename . '>' . $translate->_('answernet.er.metlife.download.xls') . '</a></b>';
print '<br><br>';
}
