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>'; }