/** * Translates the string version of a group/bucket combo into their * respective IDs. * * @todo This needs a better name and home */ static function translateTeamCategoryCode($code) { $t_or_c = substr($code, 0, 1); $t_or_c_id = intval(substr($code, 1)); if ($t_or_c == 'c') { $categories = DAO_Bucket::getAll(); $team_id = $categories[$t_or_c_id]->team_id; $category_id = $t_or_c_id; } else { $team_id = $t_or_c_id; $category_id = 0; } return array($team_id, $category_id); }
function showContactHistoryAction() { $translate = DevblocksPlatform::getTranslationService(); @($ticket_id = DevblocksPlatform::importGPC($_REQUEST['ticket_id'], 'integer')); $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('path', $this->_TPL_PATH); $ticket = DAO_Ticket::getTicket($ticket_id); $requesters = $ticket->getRequesters(); $contact = DAO_Address::get($ticket->first_wrote_address_id); $tpl->assign('contact', $contact); $visit = CerberusApplication::getVisit(); /* @var $visit CerberusVisit */ $view = C4_AbstractViewLoader::getView('', 'contact_history'); if (null == $view) { $view = new C4_TicketView(); $view->id = 'contact_history'; $view->name = $translate->_('addy_book.history.view.title'); $view->view_columns = array(SearchFields_Ticket::TICKET_LAST_ACTION_CODE, SearchFields_Ticket::TICKET_CREATED_DATE, SearchFields_Ticket::TICKET_TEAM_ID, SearchFields_Ticket::TICKET_CATEGORY_ID); $view->params = array(); $view->renderLimit = 10; $view->renderSortBy = SearchFields_Ticket::TICKET_CREATED_DATE; $view->renderSortAsc = false; } $view->name = vsprintf($translate->_('addy_book.history.view.requester'), intval(count($requesters))); $view->params = array(SearchFields_Ticket::REQUESTER_ID => new DevblocksSearchCriteria(SearchFields_Ticket::REQUESTER_ID, 'in', array_keys($requesters)), SearchFields_Ticket::TICKET_DELETED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_DELETED, DevblocksSearchCriteria::OPER_EQ, 0)); $view->renderPage = 0; $tpl->assign('view', $view); C4_AbstractViewLoader::setView($view->id, $view); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $teams = DAO_Group::getAll(); $tpl->assign('teams', $teams); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $team_categories = DAO_Bucket::getTeams(); $tpl->assign('team_categories', $team_categories); $tpl->display('file:' . $this->_TPL_PATH . 'display/modules/history/index.tpl'); }
/** * 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 showTabInboxAction() { @($group_id = DevblocksPlatform::importGPC($_REQUEST['id'], 'integer', 0)); $tpl = DevblocksPlatform::getTemplateService(); $tpl_path = $this->_TPL_PATH; $tpl->assign('path', $tpl_path); $tpl->assign('group_id', $group_id); $active_worker = CerberusApplication::getActiveWorker(); if (!$active_worker->isTeamManager($group_id) && !$active_worker->is_superuser) { return; } $team_rules = DAO_GroupInboxFilter::getByGroupId($group_id); $tpl->assign('team_rules', $team_rules); $groups = DAO_Group::getAll(); $tpl->assign('groups', $groups); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); // Custom Field Sources $source_manifests = DevblocksPlatform::getExtensions('cerberusweb.fields.source', false); $tpl->assign('source_manifests', $source_manifests); // Custom Fields $custom_fields = DAO_CustomField::getAll(); $tpl->assign('custom_fields', $custom_fields); $tpl->display('file:' . $tpl_path . 'groups/manage/filters/index.tpl'); }
function handleRequest(DevblocksHttpRequest $request) { $worker = CerberusApplication::getActiveWorker(); if (empty($worker)) { return; } $stack = $request->path; array_shift($stack); // print @($object = strtolower(array_shift($stack))); // ticket|message|etc $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('path', $this->_TPL_PATH); $settings = DevblocksPlatform::getPluginSettingsService(); $tpl->assign('settings', $settings); $translate = DevblocksPlatform::getTranslationService(); $tpl->assign('translate', $translate); $teams = DAO_Group::getAll(); $tpl->assign('teams', $teams); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); // Security $active_worker = CerberusApplication::getActiveWorker(); $active_worker_memberships = $active_worker->getMemberships(); // [TODO] Make this pluggable // Subcontroller switch ($object) { case 'ticket': @($id = array_shift($stack)); @($ticket = is_numeric($id) ? DAO_Ticket::getTicket($id) : DAO_Ticket::getTicketByMask($id)); $convo_timeline = array(); $messages = $ticket->getMessages(); foreach ($messages as $message_id => $message) { /* @var $message CerberusMessage */ $key = $message->created_date . '_m' . $message_id; // build a chrono index of messages $convo_timeline[$key] = array('m', $message_id); } @($mail_inline_comments = DAO_WorkerPref::get($active_worker->id, 'mail_inline_comments', 1)); if ($mail_inline_comments) { // if inline comments are enabled $comments = DAO_TicketComment::getByTicketId($ticket->id); arsort($comments); $tpl->assign('comments', $comments); // build a chrono index of comments foreach ($comments as $comment_id => $comment) { /* @var $comment Model_TicketComment */ $key = $comment->created . '_c' . $comment_id; $convo_timeline[$key] = array('c', $comment_id); } } ksort($convo_timeline); $tpl->assign('convo_timeline', $convo_timeline); // Comment parent addresses $comment_addresses = array(); foreach ($comments as $comment) { /* @var $comment Model_TicketComment */ $address_id = intval($comment->address_id); if (!isset($comment_addresses[$address_id])) { $address = DAO_Address::get($address_id); $comment_addresses[$address_id] = $address; } } $tpl->assign('comment_addresses', $comment_addresses); // Message Notes $notes = DAO_MessageNote::getByTicketId($ticket->id); $message_notes = array(); // Index notes by message id if (is_array($notes)) { foreach ($notes as $note) { if (!isset($message_notes[$note->message_id])) { $message_notes[$note->message_id] = array(); } $message_notes[$note->message_id][$note->id] = $note; } } $tpl->assign('message_notes', $message_notes); // Make sure we're allowed to view this ticket or message if (!isset($active_worker_memberships[$ticket->team_id])) { echo "<H1>" . $translate->_('common.access_denied') . "</H1>"; return; } $tpl->assign('ticket', $ticket); $tpl->display('file:' . $this->_TPL_PATH . 'print/ticket.tpl'); break; case 'message': @($id = array_shift($stack)); @($message = DAO_Ticket::getMessage($id)); @($ticket = DAO_Ticket::getTicket($message->ticket_id)); // Make sure we're allowed to view this ticket or message if (!isset($active_worker_memberships[$ticket->team_id])) { echo "<H1>" . $translate->_('common.access_denied') . "</H1>"; return; } // Message Notes $notes = DAO_MessageNote::getByTicketId($ticket->id); $message_notes = array(); // Index notes by message id if (is_array($notes)) { foreach ($notes as $note) { if (!isset($message_notes[$note->message_id])) { $message_notes[$note->message_id] = array(); } $message_notes[$note->message_id][$note->id] = $note; } } $tpl->assign('message_notes', $message_notes); $tpl->assign('message', $message); $tpl->assign('ticket', $ticket); $tpl->display('file:' . $this->_TPL_PATH . 'print/message.tpl'); break; } }
function getTicketAssignmentReportAction() { @($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '')); @($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', '')); @($countonly = DevblocksPlatform::importGPC($_REQUEST['countonly'], 'integer', 0)); @($sel_worker_id = DevblocksPlatform::importGPC($_REQUEST['worker_id'], 'integer', 0)); @($sel_group_id = DevblocksPlatform::importGPC($_REQUEST['group_select'], 'string', '')); list($g_id, $b_id) = CerberusApplication::translateTeamCategoryCode($sel_group_id); // use date range if specified, else use duration prior to now $start_time = 0; $end_time = 0; if (empty($start) && empty($end)) { $start = "-5 year"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); } else { $start_time = strtotime($start); $end_time = strtotime($end); } $db = DevblocksPlatform::getDatabaseService(); $tpl = DevblocksPlatform::getTemplateService(); $tpl->cache_lifetime = "0"; $tpl->assign('path', $this->tpl_path); $workers = DAO_Worker::getAll(); $groups = DAO_Group::getAll(); $buckets = DAO_Bucket::getAll(); $tpl->assign('workers', $workers); $sql = "SELECT w.id worker_id, t.id ticket_id, t.mask, t.subject, t.created_date, "; $sql .= "t.updated_date, t.is_waiting, t.team_id, t.category_id "; $sql .= "FROM ticket t inner join worker w on t.next_worker_id = w.id "; $sql .= sprintf("WHERE updated_date > %d AND updated_date <= %d ", $start_time, $end_time); $sql .= "AND t.is_deleted = 0 "; $sql .= "AND t.is_closed = 0 "; $sql .= "AND t.spam_score < 0.9000 "; $sql .= "AND t.spam_training != 'S' "; if ($sel_worker_id) { $sql .= sprintf("AND w.id = %d ", $sel_worker_id); } if ($g_id > 0) { $sql .= sprintf("AND t.team_id = %d ", $g_id); if ($b_id > -1) { $sql .= sprintf("AND t.category_id = %d ", $b_id); } } $sql .= "ORDER by w.last_name"; $rs_buckets = $db->Execute($sql); $ticket_assignments = array(); while (!$rs_buckets->EOF) { $worker_id = intval($rs_buckets->fields['worker_id']); $mask = $rs_buckets->fields['mask']; $subject = $rs_buckets->fields['subject']; $created_date = intval($rs_buckets->fields['created_date']); $updated_date = intval($rs_buckets->fields['updated_date']); if ($rs_buckets->fields['is_waiting']) { $status = "Waiting for Reply"; } else { $status = "Open"; } $team_id = intval($rs_buckets->fields['team_id']); $category_id = intval($rs_buckets->fields['category_id']); if (!isset($ticket_assignments[$worker_id])) { $ticket_assignments[$worker_id] = array(); } unset($assignment); $assignment->mask = $mask; $assignment->subject = $subject; $assignment->created_date = $created_date; $assignment->updated_date = $updated_date; $assignment->status = $status; $assignment->team_id = $groups[$team_id]->name; if ($category_id) { $assignment->category_id = $buckets[$category_id]->name; } else { $assignment->category_id = 'Inbox'; } $ticket_assignments[$worker_id][] = $assignment; $rs_buckets->MoveNext(); } $tpl->assign('ticket_assignments', $ticket_assignments); //print_r($ticket_assignments);exit; $tpl->display('file:' . $this->tpl_path . '/report_stats_html.tpl'); }
function render() { $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('id', $this->id); $view_path = DEVBLOCKS_PLUGIN_PATH . 'cerberusweb.mobile/templates/tickets/'; $tpl->assign('view_path_mobile', $view_path_mobile); $tpl->assign('view', $this); $visit = CerberusApplication::getVisit(); $results = self::getData(); $tpl->assign('results', $results); @($ids = array_keys($results[0])); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $teams = DAO_Group::getAll(); $tpl->assign('teams', $teams); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $team_categories = DAO_Bucket::getTeams(); $tpl->assign('team_categories', $team_categories); // [TODO] Is this even used here or did mfogg copy it blindly? $ticket_fields = DAO_CustomField::getBySource(ChCustomFieldSource_Ticket::ID); $tpl->assign('ticket_fields', $ticket_fields); // Undo? // [TODO] Is this even used here or did mfogg copy it blindly? $last_action = C4_TicketView::getLastAction($this->id); $tpl->assign('last_action', $last_action); if (!empty($last_action) && !is_null($last_action->ticket_ids)) { $tpl->assign('last_action_count', count($last_action->ticket_ids)); } $tpl->cache_lifetime = "0"; $tpl->assign('view_fields', $this->getColumns()); $tpl->display('file:' . $view_path . 'ticket_view.tpl'); }
function render() { $this->_sanitize(); $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('id', $this->id); $tpl->assign('view', $this); $tpl->cache_lifetime = "0"; $tpl->assign('view_fields', $this->getColumns()); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $groups = DAO_Group::getAll(); $tpl->assign('groups', $groups); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $ticket_fields = SearchFields_Ticket::getFields(); $tpl->assign('ticket_fields', $ticket_fields); $tpl->display('file:' . DEVBLOCKS_PLUGIN_PATH . 'cerberusweb.auditlog/templates/display/log/log_view.tpl'); }
/** * @param integer[] $ticket_ids */ function run($ticket_ids) { if (!is_array($ticket_ids)) { $ticket_ids = array($ticket_ids); } $fields = array(); $field_values = array(); $groups = DAO_Group::getAll(); $buckets = DAO_Bucket::getAll(); // $workers = DAO_Worker::getAll(); $custom_fields = DAO_CustomField::getAll(); // actions if (is_array($this->actions)) { foreach ($this->actions as $action => $params) { switch ($action) { // case 'status': // if(isset($params['is_waiting'])) // $fields[DAO_Ticket::IS_WAITING] = intval($params['is_waiting']); // if(isset($params['is_closed'])) // $fields[DAO_Ticket::IS_CLOSED] = intval($params['is_closed']); // if(isset($params['is_deleted'])) // $fields[DAO_Ticket::IS_DELETED] = intval($params['is_deleted']); // break; // case 'assign': // if(isset($params['worker_id'])) { // $w_id = intval($params['worker_id']); // if(0 == $w_id || isset($workers[$w_id])) // $fields[DAO_Ticket::NEXT_WORKER_ID] = $w_id; // } // break; case 'move': if (isset($params['group_id']) && isset($params['bucket_id'])) { $g_id = intval($params['group_id']); $b_id = intval($params['bucket_id']); if (isset($groups[$g_id]) && (0 == $b_id || isset($buckets[$b_id]))) { $fields[DAO_Ticket::TEAM_ID] = $g_id; $fields[DAO_Ticket::CATEGORY_ID] = $b_id; } } break; // case 'spam': // if(isset($params['is_spam'])) { // if(intval($params['is_spam'])) { // foreach($ticket_ids as $ticket_id) // CerberusBayes::markTicketAsSpam($ticket_id); // } else { // foreach($ticket_ids as $ticket_id) // CerberusBayes::markTicketAsNotSpam($ticket_id); // } // } // break; // case 'spam': // if(isset($params['is_spam'])) { // if(intval($params['is_spam'])) { // foreach($ticket_ids as $ticket_id) // CerberusBayes::markTicketAsSpam($ticket_id); // } else { // foreach($ticket_ids as $ticket_id) // CerberusBayes::markTicketAsNotSpam($ticket_id); // } // } // break; default: // Custom fields if (substr($action, 0, 3) == "cf_") { $field_id = intval(substr($action, 3)); if (!isset($custom_fields[$field_id]) || !isset($params['value'])) { break; } $field_values[$field_id] = $params; } break; } } } if (!empty($ticket_ids)) { if (!empty($fields)) { DAO_Ticket::updateTicket($ticket_ids, $fields); } // Custom Fields C4_AbstractView::_doBulkSetCustomFields(ChCustomFieldSource_Ticket::ID, $field_values, $ticket_ids); } }
public function renderConfig(Model_Macro $macro, $source) { // we'll render the config here, _and_ populate the settings properly based on the macro $actions = parent::renderConfig($macro, $source); $tpl = DevblocksPlatform::getTemplateService(); $groups = DAO_Group::getAll(); $tpl->assign('groups', $groups); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $tpl->assign('actions', $actions); /* since the sources dont match we don't want the actions to be set to the values stored in the db */ if ($macro->source_extension_id !== $source) { $macro->actions = array(); } $tpl->assign('source', $source); $tpl->assign('macro', $macro); $tpl->display('file:' . dirname(dirname(__FILE__)) . '/templates/actions.tpl'); }
function viewAutoAssistAction() { @($view_id = DevblocksPlatform::importGPC($_POST['view_id'], 'string')); $visit = CerberusApplication::getVisit(); /* @var $visit CerberusVisit */ $view = C4_AbstractViewLoader::getView($view_id); $buckets = DAO_Bucket::getAll(); @($piles_always = DevblocksPlatform::importGPC($_POST['piles_always'], 'array', array())); @($piles_hash = DevblocksPlatform::importGPC($_POST['piles_hash'], 'array', array())); @($piles_moveto = DevblocksPlatform::importGPC($_POST['piles_moveto'], 'array', array())); @($piles_type = DevblocksPlatform::importGPC($_POST['piles_type'], 'array', array())); @($piles_type_param = DevblocksPlatform::importGPC($_POST['piles_type_param'], 'array', array())); @($piles_value = DevblocksPlatform::importGPC($_POST['piles_value'], 'array', array())); $piles_always = array_flip($piles_always); // Flip hash foreach ($piles_hash as $idx => $hash) { @($moveto = $piles_moveto[$idx]); @($type = $piles_type[$idx]); @($type_param = $piles_type_param[$idx]); @($val = $piles_value[$idx]); /* * [TODO] [JAS]: Somewhere here we should be ignoring these values for a bit * so other options have a chance to bubble up */ if (empty($hash) || empty($moveto) || empty($type) || empty($val)) { continue; } switch (strtolower(substr($moveto, 0, 1))) { // Team/Bucket Move case 't': $g_id = intval(substr($moveto, 1)); $doActions = array('move' => array('group_id' => $g_id, 'bucket_id' => 0)); break; case 'c': $b_id = intval(substr($moveto, 1)); @($g_id = intval($buckets[$b_id]->team_id)); if (!empty($g_id)) { $doActions = array('move' => array('group_id' => $g_id, 'bucket_id' => $b_id)); } break; // Action // Action case 'a': switch (strtolower(substr($moveto, 1))) { case 'c': // close $doActions = array('status' => array('is_closed' => 1, 'is_deleted' => 0)); break; case 's': // spam $doActions = array('status' => array('is_closed' => 1, 'is_deleted' => 1), 'spam' => array('is_spam' => 1)); break; case 'd': // delete $doActions = array('status' => array('is_closed' => 1, 'is_deleted' => 1)); break; } break; // Worker // Worker case 'w': $w_id = intval(substr($moveto, 1)); if (!empty($w_id)) { $doActions = array('assign' => array('worker_id' => $w_id)); } break; default: $doActions = array(); break; } $doTypeParam = $type_param; // Domains, senders are both sender batch actions switch ($type) { default: case 'sender': $doType = 'sender'; break; case 'subject': $doType = 'subject'; break; case 'header': $doType = 'header'; break; } // Make wildcards $doData = array(); if ($type == "domain") { $doData = array('*' . $val); } else { $doData = array($val); } $view->doBulkUpdate($doType, $doTypeParam, $doData, $doActions, array()); } // Reset the paging since we may have reduced our list size $view->renderPage = 0; C4_AbstractViewLoader::setView($view_id, $view); DevblocksPlatform::redirect(new DevblocksHttpResponse(array('tickets'))); }
function getTimeSpentPlus1ReportAction() { $db = DevblocksPlatform::getDatabaseService(); DevblocksPlatform::getExtensions('timetracking.source', true); // Security if (null == ($active_worker = CerberusApplication::getActiveWorker())) { die($translate->_('common.access_denied')); } $tpl = DevblocksPlatform::getTemplateService(); $tpl->cache_lifetime = "0"; $tpl->assign('path', $this->tpl_path); // import dates from form @($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '')); @($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', '')); $start_time = 0; $end_time = 0; if (empty($start) && empty($end)) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); } else { $start_time = strtotime($start); $end_time = strtotime($end); } if ($start_time === false || $end_time === false) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); $tpl->assign('invalidDate', true); } $groups = DAO_Group::getAll(); $buckets = DAO_Bucket::getAll(); // reload variables in template $tpl->assign('start', $start); $tpl->assign('end', $end); $sources = DAO_TimeTrackingEntry::getSources(); $tpl->assign('sources', $sources); $sql = "SELECT t.mask, t.id, sum(tte.time_actual_mins) mins, a.email, "; $sql .= "t.subject, t.created_date, t.updated_date, t.is_closed, "; $sql .= "t.is_waiting, t.team_id, t.category_id "; $sql .= "FROM timetracking_entry tte "; $sql .= "INNER JOIN ticket t ON tte.source_id = t.id "; $sql .= "INNER JOIN address a ON t.first_wrote_address_id = a.id "; $sql .= sprintf("WHERE log_date > %d AND log_date <= %d ", $start_time, $end_time); $sql .= "GROUP BY t.id "; $sql .= "ORDER BY t.id, tte.log_date "; // echo $sql; $rs = $db->Execute($sql); $time_entries = array(); $filename = "report-plus1-" . $active_worker->id . ".csv"; $full_filename = getcwd() . '/storage/answernet/' . $filename; if (file_exists($full_filename)) { if (!is_writable($full_filename)) { die("The file: {$full_filename} is not writable"); } } elseif (!is_writable(getcwd() . '/storage/answernet/')) { die("you cannot create files in this directory. Check the permissions"); } //open the file for Writing $fh = fopen($full_filename, "w"); //Lock the file for the write operation flock($fh, LOCK_EX); $label = array("Ticket Mask", "Ticket Number", "Client Name", "Asset Name", "Site Name", "Requestor", "Subject", "Created Date", "Last Updated", "Group", "Bucket", "Status", "Total Min"); fputcsv($fh, $label, ",", "\""); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $csv = array(); $custom_fields = array(); $mask = $rs->fields['mask']; $id = intval($rs->fields['id']); $email = $rs->fields['email']; $subject = $rs->fields['subject']; $team_id = intval($rs->fields['team_id']); $category_id = intval($rs->fields['category_id']); $created_date = intval($rs->fields['created_date']); $updated_date = intval($rs->fields['updated_date']); $status = "Open"; if (intval($rs->fields['is_waiting'])) { $status = "Waiting for Reply"; } if (intval($rs->fields['is_closed'])) { $status = "Completed"; } $mins = intval($rs->fields['mins']); if (!isset($time_entries[$id])) { $time_entries[$id] = array(); } $csv['mask'] = $mask; $csv['id'] = $id; unset($time_entry); $time_entry['mask'] = $mask; $custom_fields = DAO_CustomFieldValue::getValuesBySourceIds(ChCustomFieldSource_Ticket::ID, $id); if (isset($custom_fields[$id][10])) { $csv['client'] = $custom_fields[$id][10]; $time_entry['client'] = $custom_fields[$id][10]; } else { $csv['client'] = ""; $time_entry['client'] = ""; } if (isset($custom_fields[$id][11])) { $csv['asset'] = $custom_fields[$id][11]; $time_entry['asset'] = $custom_fields[$id][11]; } else { $csv['asset'] = ""; $time_entry['asset'] = ""; } if (isset($custom_fields[$id][1])) { $csv['sitename'] = $custom_fields[$id][1]; $time_entry['sitename'] = $custom_fields[$id][1]; } else { $csv['sitename'] = ""; $time_entry['sitename'] = ""; } $csv['email'] = $email; $time_entry['email'] = $email; $csv['subject'] = $subject; $time_entry['subject'] = $subject; $csv['created_date'] = date("Y-m-d h:i A", $created_date); $time_entry['created_date'] = $created_date; $csv['updated_date'] = date("Y-m-d h:i A", $updated_date); $time_entry['updated_date'] = $updated_date; $csv['group'] = $groups[$team_id]->name; $time_entry['group'] = $groups[$team_id]->name; if ($category_id) { $csv['bucket'] = $buckets[$category_id]->name; $time_entry['bucket'] = $buckets[$category_id]->name; } else { $csv['bucket'] = 'Inbox'; $time_entry['bucket'] = 'Inbox'; } $csv['status'] = $status; $time_entry['status'] = $status; $csv['mins'] = $mins; $time_entry['mins'] = $mins; $time_entries[$id] = $time_entry; fputcsv($fh, $csv, ",", "\""); $rs->MoveNext(); } } fclose($fh); $tpl->assign('time_entries', $time_entries); $tpl->display('file:' . $this->tpl_path . '/report_plus1_time_html.tpl'); }
function showContactHistoryAction() { $visit = CerberusApplication::getVisit(); /* @var $visit CerberusVisit */ $translate = DevblocksPlatform::getTranslationService(); @($ticket_id = DevblocksPlatform::importGPC($_REQUEST['ticket_id'], 'integer')); $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('path', $this->_TPL_PATH); // Ticket $ticket = DAO_Ticket::getTicket($ticket_id); $tpl->assign('ticket', $ticket); $requesters = $ticket->getRequesters(); // Addy $contact = DAO_Address::get($ticket->first_wrote_address_id); $tpl->assign('contact', $contact); // Scope $scope = $visit->get('display.history.scope', ''); // [TODO] Sanitize scope preference // Defaults $defaults = new C4_AbstractViewModel(); $defaults->class_name = 'C4_TicketView'; $defaults->id = 'contact_history'; $defaults->name = $translate->_('addy_book.history.view.title'); $defaults->view_columns = array(SearchFields_Ticket::TICKET_LAST_ACTION_CODE, SearchFields_Ticket::TICKET_CREATED_DATE, SearchFields_Ticket::TICKET_TEAM_ID, SearchFields_Ticket::TICKET_CATEGORY_ID); $defaults->params = array(); $defaults->renderLimit = 10; $defaults->renderSortBy = SearchFields_Ticket::TICKET_CREATED_DATE; $defaults->renderSortAsc = false; // View $view = C4_AbstractViewLoader::getView('contact_history', $defaults); // Sanitize scope options if ('org' == $scope) { if (empty($contact->contact_org_id)) { $scope = ''; } if (null == ($contact_org = DAO_ContactOrg::get($contact->contact_org_id))) { $scope = ''; } } if ('domain' == $scope) { $email_parts = explode('@', $contact->email); if (!is_array($email_parts) || 2 != count($email_parts)) { $scope = ''; } } switch ($scope) { case 'org': $view->params = array(SearchFields_Ticket::TICKET_FIRST_CONTACT_ORG_ID => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_FIRST_CONTACT_ORG_ID, '=', $contact->contact_org_id), SearchFields_Ticket::TICKET_DELETED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_DELETED, '=', 0)); $view->name = ucwords($translate->_('contact_org.name')) . ": " . $contact_org->name; break; case 'domain': $view->params = array(SearchFields_Ticket::REQUESTER_ADDRESS => new DevblocksSearchCriteria(SearchFields_Ticket::REQUESTER_ADDRESS, 'like', '*@' . $email_parts[1]), SearchFields_Ticket::TICKET_DELETED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_DELETED, '=', 0)); $view->name = ucwords($translate->_('common.email')) . ": *@" . $email_parts[1]; break; default: case 'email': $scope = 'email'; $view->params = array(SearchFields_Ticket::REQUESTER_ID => new DevblocksSearchCriteria(SearchFields_Ticket::REQUESTER_ID, 'in', array_keys($requesters)), SearchFields_Ticket::TICKET_DELETED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_DELETED, '=', 0)); $view->name = ucwords($translate->_('common.email')) . ": " . $contact->email; break; } $tpl->assign('scope', $scope); $view->renderPage = 0; $tpl->assign('view', $view); C4_AbstractViewLoader::setView($view->id, $view); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $teams = DAO_Group::getAll(); $tpl->assign('teams', $teams); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $team_categories = DAO_Bucket::getTeams(); $tpl->assign('team_categories', $team_categories); $tpl->display('file:' . $this->_TPL_PATH . 'display/modules/history/index.tpl'); }
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>'; }
private function _handleImportTicket($xml) { $settings = CerberusSettings::getInstance(); $logger = DevblocksPlatform::getConsoleLog(); $workers = DAO_Worker::getAll(); static $email_to_worker_id = null; static $group_name_to_id = null; static $bucket_name_to_id = null; // Hash Workers so we can ID their incoming tickets if (null == $email_to_worker_id) { $email_to_worker_id = array(); if (is_array($workers)) { foreach ($workers as $worker) { /* @var $worker CerberusWorker */ $email_to_worker_id[strtolower($worker->email)] = intval($worker->id); } } } // Hash Group names if (null == $group_name_to_id) { $groups = DAO_Group::getAll(); $group_name_to_id = array(); if (is_array($groups)) { foreach ($groups as $group) { $group_name_to_id[strtolower($group->name)] = intval($group->id); } } } // Hash Bucket names if (null == $bucket_name_to_id) { $buckets = DAO_Bucket::getAll(); $bucket_name_to_id = array(); if (is_array($buckets)) { foreach ($buckets as $bucket) { /* @var $bucket CerberusCategory */ // Hash by team ID and bucket name $hash = md5($bucket->team_id . strtolower($bucket->name)); $bucket_to_id[$hash] = intval($bucket->id); } } } $sMask = (string) $xml->mask; $sSubject = substr((string) $xml->subject, 0, 255); $sGroup = (string) $xml->group; $sBucket = (string) $xml->bucket; $iCreatedDate = (int) $xml->created_date; $iUpdatedDate = (int) $xml->updated_date; $isWaiting = (int) $xml->is_waiting; $isClosed = (int) $xml->is_closed; if (empty($sMask)) { $sMask = CerberusApplication::generateTicketMask(); } // Find the destination Group + Bucket (or create them) if (empty($sGroup)) { $iDestGroupId = 0; if (null != ($iDestGroup = DAO_Group::getDefaultGroup())) { $iDestGroupId = $iDestGroup->id; } } elseif (null == ($iDestGroupId = @$group_name_to_id[strtolower($sGroup)])) { $iDestGroupId = DAO_Group::createTeam(array(DAO_Group::TEAM_NAME => $sGroup)); // Give all superusers manager access to this new group if (is_array($workers)) { foreach ($workers as $worker) { if ($worker->is_superuser) { DAO_Group::setTeamMember($iDestGroupId, $worker->id, true); } } } // Rehash DAO_Group::getAll(true); $group_name_to_id[strtolower($sGroup)] = $iDestGroupId; } if (empty($sBucket)) { $iDestBucketId = 0; // Inbox } elseif (null == ($iDestBucketId = @$bucket_name_to_id[md5($iDestGroupId . strtolower($sBucket))])) { $iDestBucketId = DAO_Bucket::create($sBucket, $iDestGroupId); // Rehash DAO_Bucket::getAll(true); $bucket_name_to_id[strtolower($sBucket)] = $iDestBucketId; } // Xpath the first and last "from" out of "/ticket/messages/message/headers/from" $aMessageNodes = $xml->xpath("/ticket/messages/message"); $iNumMessages = count($aMessageNodes); @($eFirstMessage = reset($aMessageNodes)); if (is_null($eFirstMessage)) { $logger->warn('[Importer] Ticket ' . $sMask . " doesn't have any messages. Skipping."); return false; } if (is_null($eFirstMessage->headers) || is_null($eFirstMessage->headers->from)) { $logger->warn('[Importer] Ticket ' . $sMask . " first message doesn't provide a sender address."); return false; } $sFirstWrote = self::_parseRfcAddressList($eFirstMessage->headers->from, true); if (null == ($firstWroteInst = CerberusApplication::hashLookupAddress($sFirstWrote, true))) { $logger->warn('[Importer] Ticket ' . $sMask . " - Invalid sender adddress: " . $sFirstWrote); return false; } $eLastMessage = end($aMessageNodes); if (is_null($eLastMessage)) { $logger->warn('[Importer] Ticket ' . $sMask . " doesn't have any messages. Skipping."); return false; } if (is_null($eLastMessage->headers) || is_null($eLastMessage->headers->from)) { $logger->warn('[Importer] Ticket ' . $sMask . " last message doesn't provide a sender address."); return false; } $sLastWrote = self::_parseRfcAddressList($eLastMessage->headers->from, true); if (null == ($lastWroteInst = CerberusApplication::hashLookupAddress($sLastWrote, true))) { $logger->warn('[Importer] Ticket ' . $sMask . ' last message has an invalid sender address: ' . $sLastWrote); return false; } // Last action code + last worker $sLastActionCode = CerberusTicketActionCode::TICKET_OPENED; $iLastWorkerId = 0; if ($iNumMessages > 1) { if (null != @($iLastWorkerId = $email_to_worker_id[strtolower($lastWroteInst->email)])) { $sLastActionCode = CerberusTicketActionCode::TICKET_WORKER_REPLY; } else { $sLastActionCode = CerberusTicketActionCode::TICKET_CUSTOMER_REPLY; $iLastWorkerId = 0; } } // Dupe check by ticket mask if (null != DAO_Ticket::getTicketByMask($sMask)) { $logger->warn("[Importer] Ticket mask '" . $sMask . "' already exists. Making it unique."); $uniqueness = 1; $origMask = $sMask; // Append new uniqueness to the ticket mask: LLL-NNNNN-NNN-1, LLL-NNNNN-NNN-2, ... do { $sMask = $origMask . '-' . ++$uniqueness; } while (null != DAO_Ticket::getTicketIdByMask($sMask)); $logger->info("[Importer] The unique mask for '" . $origMask . "' is now '" . $sMask . "'"); } // Create ticket $fields = array(DAO_Ticket::MASK => $sMask, DAO_Ticket::SUBJECT => $sSubject, DAO_Ticket::IS_WAITING => $isWaiting, DAO_Ticket::IS_CLOSED => $isClosed, DAO_Ticket::FIRST_WROTE_ID => intval($firstWroteInst->id), DAO_Ticket::LAST_WROTE_ID => intval($lastWroteInst->id), DAO_Ticket::CREATED_DATE => $iCreatedDate, DAO_Ticket::UPDATED_DATE => $iUpdatedDate, DAO_Ticket::TEAM_ID => intval($iDestGroupId), DAO_Ticket::CATEGORY_ID => intval($iDestBucketId), DAO_Ticket::LAST_ACTION_CODE => $sLastActionCode, DAO_Ticket::LAST_WORKER_ID => intval($iLastWorkerId)); $ticket_id = DAO_Ticket::createTicket($fields); // echo "Ticket: ",$ticket_id,"<BR>"; // print_r($fields); // Create requesters if (!is_null($xml->requesters)) { foreach ($xml->requesters->address as $eAddress) { /* @var $eAddress SimpleXMLElement */ $sRequesterAddy = (string) $eAddress; // [TODO] RFC822 // Insert requesters if (null == ($requesterAddyInst = CerberusApplication::hashLookupAddress($sRequesterAddy, true))) { $logger->warn('[Importer] Ticket ' . $sMask . ' - Ignoring malformed requester: ' . $sRequesterAddy); continue; } DAO_Ticket::createRequester($requesterAddyInst->id, $ticket_id); } } // Create messages $is_first = true; if (!is_null($xml->messages)) { foreach ($xml->messages->message as $eMessage) { /* @var $eMessage SimpleXMLElement */ $eHeaders =& $eMessage->headers; /* @var $eHeaders SimpleXMLElement */ $sMsgFrom = (string) $eHeaders->from; $sMsgDate = (string) $eHeaders->date; $sMsgFrom = self::_parseRfcAddressList($sMsgFrom, true); if (NULL == $sMsgFrom) { $logger->warn('[Importer] Ticket ' . $sMask . ' - Invalid message sender: ' . $sMsgFrom . ' (skipping)'); continue; } if (null == ($msgFromInst = CerberusApplication::hashLookupAddress($sMsgFrom, true))) { $logger->warn('[Importer] Ticket ' . $sMask . ' - Invalid message sender: ' . $sMsgFrom . ' (skipping)'); continue; } @($msgWorkerId = intval($email_to_worker_id[strtolower($msgFromInst->email)])); // $logger->info('Checking if '.$msgFromInst->email.' is a worker'); $fields = array(DAO_Message::TICKET_ID => $ticket_id, DAO_Message::CREATED_DATE => strtotime($sMsgDate), DAO_Message::ADDRESS_ID => $msgFromInst->id, DAO_Message::IS_OUTGOING => !empty($msgWorkerId) ? 1 : 0, DAO_Message::WORKER_ID => !empty($msgWorkerId) ? $msgWorkerId : 0); $email_id = DAO_Message::create($fields); // First thread if ($is_first) { DAO_Ticket::updateTicket($ticket_id, array(DAO_Ticket::FIRST_MESSAGE_ID => $email_id)); $is_first = false; } // Create attachments if (!is_null($eMessage->attachments)) { foreach ($eMessage->attachments->attachment as $eAttachment) { /* @var $eAttachment SimpleXMLElement */ $sFileName = (string) $eAttachment->name; $sMimeType = (string) $eAttachment->mimetype; $sFileSize = (int) $eAttachment->size; $sFileContentB64 = (string) $eAttachment->content; // [TODO] This could be a little smarter about detecting extensions if (empty($sMimeType)) { $sMimeType = "application/octet-stream"; } $sFileContent = base64_decode($sFileContentB64); unset($sFileContentB64); $fields = array(DAO_Attachment::MESSAGE_ID => $email_id, DAO_Attachment::DISPLAY_NAME => $sFileName, DAO_Attachment::FILE_SIZE => intval($sFileSize), DAO_Attachment::FILEPATH => '', DAO_Attachment::MIME_TYPE => $sMimeType); $file_id = DAO_Attachment::create($fields); // Write file to disk using ID (Model) $file_path = Model_Attachment::saveToFile($file_id, $sFileContent); unset($sFileContent); // Update attachment table DAO_Attachment::update($file_id, array(DAO_Attachment::FILEPATH => $file_path)); } } // Create message content $sMessageContentB64 = (string) $eMessage->content; $sMessageContent = base64_decode($sMessageContentB64); // Content-type specific handling if (isset($eMessage->content['content-type'])) { // do we have a content-type? if (strtolower($eMessage->content['content-type']) == 'html') { // html? // Force to plaintext part $sMessageContent = CerberusApplication::stripHTML($sMessageContent); } } unset($sMessageContentB64); DAO_MessageContent::create($email_id, $sMessageContent); unset($sMessageContent); // Headers foreach ($eHeaders->children() as $eHeader) { /* @var $eHeader SimpleXMLElement */ DAO_MessageHeader::create($email_id, $eHeader->getName(), (string) $eHeader); } } } // Create comments if (!is_null($xml->comments)) { foreach ($xml->comments->comment as $eComment) { /* @var $eMessage SimpleXMLElement */ $iCommentDate = (int) $eComment->created_date; $sCommentAuthor = (string) $eComment->author; // [TODO] Address Hash Lookup $sCommentTextB64 = (string) $eComment->content; $sCommentText = base64_decode($sCommentTextB64); unset($sCommentTextB64); $commentAuthorInst = CerberusApplication::hashLookupAddress($sCommentAuthor, true); // [TODO] Sanity checking $fields = array(DAO_TicketComment::TICKET_ID => intval($ticket_id), DAO_TicketComment::CREATED => intval($iCommentDate), DAO_TicketComment::ADDRESS_ID => intval($commentAuthorInst->id), DAO_TicketComment::COMMENT => $sCommentText); $comment_id = DAO_TicketComment::create($fields); unset($sCommentText); } } $logger->info('[Importer] Imported ticket #' . $ticket_id); return true; }
function showTabHistoryAction() { $translate = DevblocksPlatform::getTranslationService(); @($org = DevblocksPlatform::importGPC($_REQUEST['org'])); $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('path', $this->_TPL_PATH); $contact = DAO_ContactOrg::get($org); $tpl->assign('contact', $contact); $visit = CerberusApplication::getVisit(); /* @var $visit CerberusVisit */ $tickets_view = C4_AbstractViewLoader::getView('contact_history'); // All org contacts $people = DAO_Address::getWhere(sprintf("%s = %d", DAO_Address::CONTACT_ORG_ID, $contact->id)); if (null == $tickets_view) { $tickets_view = new C4_TicketView(); $tickets_view->id = 'contact_history'; $tickets_view->name = $translate->_('addy_book.history.view_title'); $tickets_view->view_columns = array(SearchFields_Ticket::TICKET_LAST_ACTION_CODE, SearchFields_Ticket::TICKET_CREATED_DATE, SearchFields_Ticket::TICKET_TEAM_ID, SearchFields_Ticket::TICKET_CATEGORY_ID); $tickets_view->params = array(); $tickets_view->renderLimit = 10; $tickets_view->renderPage = 0; $tickets_view->renderSortBy = SearchFields_Ticket::TICKET_CREATED_DATE; $tickets_view->renderSortAsc = false; } @($tickets_view->name = $translate->_('ticket.requesters') . ": " . htmlspecialchars($contact->name) . ' - ' . intval(count($people)) . ' contact(s)'); $tickets_view->params = array(SearchFields_Ticket::REQUESTER_ID => new DevblocksSearchCriteria(SearchFields_Ticket::REQUESTER_ID, 'in', array_keys($people)), SearchFields_Ticket::TICKET_DELETED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_DELETED, DevblocksSearchCriteria::OPER_EQ, 0)); $tpl->assign('contact_history', $tickets_view); C4_AbstractViewLoader::setView($tickets_view->id, $tickets_view); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $teams = DAO_Group::getAll(); $tpl->assign('teams', $teams); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $team_categories = DAO_Bucket::getTeams(); $tpl->assign('team_categories', $team_categories); $tpl->display('file:' . $this->_TPL_PATH . 'contacts/orgs/tabs/history.tpl'); exit; }
function showWatcherPanelAction() { @($id = DevblocksPlatform::importGPC($_REQUEST['id'], 'integer', 0)); @($view_id = DevblocksPlatform::importGPC($_REQUEST['view_id'])); $tpl = DevblocksPlatform::getTemplateService(); $tpl->assign('path', $this->_TPL_PATH); $tpl->assign('view_id', $view_id); $active_worker = CerberusApplication::getActiveWorker(); if (null != ($filter = DAO_WatcherMailFilter::get($id))) { $tpl->assign('filter', $filter); } $groups = DAO_Group::getAll(); $tpl->assign('groups', $groups); $buckets = DAO_Bucket::getAll(); $tpl->assign('buckets', $buckets); $group_buckets = DAO_Bucket::getTeams(); $tpl->assign('group_buckets', $group_buckets); $memberships = $active_worker->getMemberships(); $tpl->assign('memberships', $memberships); if (null == @($worker_id = $filter->worker_id)) { $worker_id = $active_worker->id; } $addresses = DAO_AddressToWorker::getByWorker($worker_id); $tpl->assign('addresses', $addresses); $tpl->assign('workers', DAO_Worker::getAllActive()); $tpl->assign('all_workers', DAO_Worker::getAll()); // Custom Fields: Ticket $ticket_fields = DAO_CustomField::getBySource(ChCustomFieldSource_Ticket::ID); $tpl->assign('ticket_fields', $ticket_fields); // Custom Fields: Address $address_fields = DAO_CustomField::getBySource(ChCustomFieldSource_Address::ID); $tpl->assign('address_fields', $address_fields); // Custom Fields: Orgs $org_fields = DAO_CustomField::getBySource(ChCustomFieldSource_Org::ID); $tpl->assign('org_fields', $org_fields); $tpl->display('file:' . $this->_TPL_PATH . 'preferences/peek.tpl'); }
function renderCriteriaParam($param) { $field = $param->field; $values = !is_array($param->value) ? array($param->value) : $param->value; switch ($field) { case SearchFields_Ticket::TICKET_LAST_WORKER_ID: case SearchFields_Ticket::TICKET_NEXT_WORKER_ID: $workers = DAO_Worker::getAll(); $strings = array(); foreach ($values as $val) { if (empty($val)) { $strings[] = "Nobody"; } elseif (!isset($workers[$val])) { continue; } else { $strings[] = $workers[$val]->getName(); } } echo implode(", ", $strings); break; case SearchFields_Ticket::TICKET_TEAM_ID: $teams = DAO_Group::getAll(); $strings = array(); foreach ($values as $val) { if (!isset($teams[$val])) { continue; } $strings[] = $teams[$val]->name; } echo implode(", ", $strings); break; case SearchFields_Ticket::TICKET_CATEGORY_ID: $buckets = DAO_Bucket::getAll(); $strings = array(); foreach ($values as $val) { if (0 == $val) { $strings[] = "Inbox"; } elseif (!isset($buckets[$val])) { continue; } else { $strings[] = $buckets[$val]->name; } } echo implode(", ", $strings); break; case SearchFields_Ticket::TICKET_LAST_ACTION_CODE: $strings = array(); foreach ($values as $val) { switch ($val) { case 'O': $strings[] = "New Ticket"; break; case 'R': $strings[] = "Customer Reply"; break; case 'W': $strings[] = "Worker Reply"; break; } } echo implode(", ", $strings); break; case SearchFields_Ticket::TICKET_SPAM_TRAINING: $strings = array(); foreach ($values as $val) { switch ($val) { case 'S': $strings[] = "Spam"; break; case 'N': $strings[] = "Not Spam"; break; default: $strings[] = "Not Trained"; break; } } echo implode(", ", $strings); break; default: parent::renderCriteriaParam($param); break; } }