/**
* @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);
}
}
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 render()
{
$tpl = DevblocksPlatform::getTemplateService();
$tpl->cache_lifetime = "0";
$tpl->assign('path', $this->tpl_path);
$tpl->assign('start', 'Last Monday');
$tpl->assign('end', 'now');
$db = DevblocksPlatform::getDatabaseService();
$workers = DAO_Worker::getAll();
$tpl->assign('workers', $workers);
// Teams
$teams = DAO_Group::getAll();
$tpl->assign('teams', $teams);
// Categories
$team_categories = DAO_Bucket::getTeams();
// [TODO] Cache these
$tpl->assign('team_categories', $team_categories);
// Security
if (null == ($active_worker = CerberusApplication::getActiveWorker())) {
die($translate->_('common.access_denied'));
}
$tpl->assign('active_worker', $active_worker);
$filename = "worker-" . $active_worker->id . ".csv";
$href_filename = 'storage/answernet/' . $filename;
$tpl->assign('href_filename', $href_filename);
$tpl->display('file:' . $this->tpl_path . '/report_worker_time.tpl');
}
// Dispatch Spam Bucket
$dispatch_spam_bid = DAO_Bucket::create('Spam', $dispatch_gid);
DAO_GroupSettings::set($dispatch_gid, DAO_GroupSettings::SETTING_SPAM_ACTION, '2');
DAO_GroupSettings::set($dispatch_gid, DAO_GroupSettings::SETTING_SPAM_ACTION_PARAM, $dispatch_spam_bid);
DAO_GroupSettings::set($dispatch_gid, DAO_GroupSettings::SETTING_SPAM_THRESHOLD, '85');
// Support Group
$support_gid = DAO_Group::createTeam(array(DAO_Group::TEAM_NAME => 'Support'));
// Support Spam Bucket
$support_spam_bid = DAO_Bucket::create('Spam', $support_gid);
DAO_GroupSettings::set($support_gid, DAO_GroupSettings::SETTING_SPAM_ACTION, '2');
DAO_GroupSettings::set($support_gid, DAO_GroupSettings::SETTING_SPAM_ACTION_PARAM, $support_spam_bid);
DAO_GroupSettings::set($support_gid, DAO_GroupSettings::SETTING_SPAM_THRESHOLD, '85');
// Sales Group
$sales_gid = DAO_Group::createTeam(array(DAO_Group::TEAM_NAME => 'Sales'));
// Sales Spam Bucket
$sales_spam_bid = DAO_Bucket::create('Spam', $sales_gid);
DAO_GroupSettings::set($sales_gid, DAO_GroupSettings::SETTING_SPAM_ACTION, '2');
DAO_GroupSettings::set($sales_gid, DAO_GroupSettings::SETTING_SPAM_ACTION_PARAM, $sales_spam_bid);
DAO_GroupSettings::set($sales_gid, DAO_GroupSettings::SETTING_SPAM_THRESHOLD, '85');
// Default catchall
DAO_Group::updateTeam($dispatch_gid, array(DAO_Group::IS_DEFAULT => 1));
}
// If this worker doesn't exist, create them
if (null === ($lookup = DAO_Worker::lookupAgentEmail($worker_email))) {
$worker_id = DAO_Worker::create($worker_email, $worker_pass, 'Super', 'User', 'Administrator');
// Superuser bit
$fields = array(DAO_Worker::IS_SUPERUSER => 1);
DAO_Worker::updateAgent($worker_id, $fields);
// Add the worker e-mail to the addresses table
if (!empty($worker_email)) {
DAO_Address::lookupAddress($worker_email, true);
/**
* 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;
}
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 getTopTicketsReportAction()
{
@($age = DevblocksPlatform::importGPC($_REQUEST['age'], 'string', '30d'));
$db = DevblocksPlatform::getDatabaseService();
@($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', ''));
@($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', ''));
@($countonly = DevblocksPlatform::importGPC($_REQUEST['countonly'], 'integer', 0));
@($by_address = DevblocksPlatform::importGPC($_REQUEST['by_address'], 'integer', 0));
// use date range if specified, else use duration prior to now
$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);
}
$tpl = DevblocksPlatform::getTemplateService();
$tpl->cache_lifetime = "0";
$tpl->assign('path', $this->tpl_path);
// Top Buckets
$groups = DAO_Group::getAll();
$tpl->assign('groups', $groups);
$group_buckets = DAO_Bucket::getTeams();
$tpl->assign('group_buckets', $group_buckets);
if ($by_address) {
$sql = sprintf("SELECT count(*) AS hits, a.id as contact_id, a.email as contact_name, t.team_id, t.category_id " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND t.team_id != 0 " . "GROUP BY a.email, t.team_id, t.category_id ORDER BY hits DESC ", $start_time, $end_time);
} else {
//default is by org
$sql = sprintf("SELECT count(*) AS hits, a.contact_org_id as contact_id, o.name as contact_name, t.team_id, t.category_id " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "INNER JOIN contact_org o ON a.contact_org_id = o.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND a.contact_org_id != 0 " . "AND t.team_id != 0 " . "GROUP BY a.contact_org_id, o.name, t.team_id, t.category_id " . "ORDER BY hits DESC ", $start_time, $end_time);
}
$rs_buckets = $db->Execute($sql);
$group_counts = array();
$max_orgs = 100;
$current_orgs = 0;
while (!$rs_buckets->EOF && $current_orgs <= $max_orgs) {
$org_id = intval($rs_buckets->fields['contact_id']);
$org_name = $rs_buckets->fields['contact_name'];
$team_id = intval($rs_buckets->fields['team_id']);
$category_id = intval($rs_buckets->fields['category_id']);
$hits = intval($rs_buckets->fields['hits']);
if (!isset($group_counts[$org_id])) {
$group_counts[$org_id] = array();
$current_orgs++;
}
if (!isset($group_counts[$org_id]['teams'])) {
$group_counts[$org_id]['teams'] = array();
}
if (!isset($group_counts[$org_id]['teams'][$team_id])) {
$group_counts[$org_id]['teams'][$team_id] = array();
}
if (!isset($group_counts[$org_id]['teams'][$team_id]['buckets'])) {
$group_counts[$org_id]['teams'][$team_id]['buckets'] = array();
}
$group_counts[$org_id]['name'] = $org_name;
$group_counts[$org_id]['teams'][$team_id]['buckets'][$category_id] = $hits;
@($group_counts[$org_id]['teams'][$team_id]['total'] = intval($group_counts[$org_id]['teams'][$team_id]['total']) + $hits);
@($group_counts[$org_id]['total'] = intval($group_counts[$org_id]['total']) + $hits);
$rs_buckets->MoveNext();
}
uasort($group_counts, array("ChReportTopTicketsByContact", "sortCountsArrayByHits"));
//echo "<pre>";print_r($group_counts);echo "</pre>";
$tpl->assign('group_counts', $group_counts);
$tpl->display('file:' . $this->tpl_path . '/reports/ticket/top_contacts_tickets/html.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 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');
}
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 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 render()
{
$db = DevblocksPlatform::getDatabaseService();
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->tpl_path);
// Year shortcuts
$years = array();
$sql = "SELECT date_format(from_unixtime(created_date),'%Y') as year FROM ticket WHERE created_date > 0 GROUP BY year having year <= date_format(now(),'%Y') ORDER BY year desc limit 0,10";
$rs = $db->query($sql);
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$years[] = intval($rs->fields['year']);
$rs->MoveNext();
}
}
$tpl->assign('years', $years);
// Dates
@($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '-30 days'));
@($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', 'now'));
// use date range if specified, else use duration prior to now
$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);
}
$tpl->assign('start', $start);
$tpl->assign('end', $end);
// Table
@($age = DevblocksPlatform::importGPC($_REQUEST['age'], 'string', '30d'));
@($by_address = DevblocksPlatform::importGPC($_REQUEST['by_address'], 'integer', 0));
$tpl->assign('by_address', $by_address);
// Top Buckets
$groups = DAO_Group::getAll();
$tpl->assign('groups', $groups);
$group_buckets = DAO_Bucket::getTeams();
$tpl->assign('group_buckets', $group_buckets);
if ($by_address) {
$sql = sprintf("SELECT count(*) AS hits, a.id as contact_id, a.email as contact_name, t.team_id, t.category_id " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND t.team_id != 0 " . "GROUP BY a.email, t.team_id, t.category_id ORDER BY hits DESC ", $start_time, $end_time);
} else {
//default is by org
$sql = sprintf("SELECT count(*) AS hits, a.contact_org_id as contact_id, o.name as contact_name, t.team_id, t.category_id " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "INNER JOIN contact_org o ON a.contact_org_id = o.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND a.contact_org_id != 0 " . "AND t.team_id != 0 " . "GROUP BY a.contact_org_id, o.name, t.team_id, t.category_id " . "ORDER BY hits DESC ", $start_time, $end_time);
}
$rs_buckets = $db->Execute($sql);
$group_counts = array();
$max_orgs = 100;
$current_orgs = 0;
while (!$rs_buckets->EOF && $current_orgs <= $max_orgs) {
$org_id = intval($rs_buckets->fields['contact_id']);
$org_name = $rs_buckets->fields['contact_name'];
$team_id = intval($rs_buckets->fields['team_id']);
$category_id = intval($rs_buckets->fields['category_id']);
$hits = intval($rs_buckets->fields['hits']);
if (!isset($group_counts[$org_id])) {
$group_counts[$org_id] = array();
$current_orgs++;
}
if (!isset($group_counts[$org_id]['teams'])) {
$group_counts[$org_id]['teams'] = array();
}
if (!isset($group_counts[$org_id]['teams'][$team_id])) {
$group_counts[$org_id]['teams'][$team_id] = array();
}
if (!isset($group_counts[$org_id]['teams'][$team_id]['buckets'])) {
$group_counts[$org_id]['teams'][$team_id]['buckets'] = array();
}
$group_counts[$org_id]['name'] = $org_name;
$group_counts[$org_id]['teams'][$team_id]['buckets'][$category_id] = $hits;
@($group_counts[$org_id]['teams'][$team_id]['total'] = intval($group_counts[$org_id]['teams'][$team_id]['total']) + $hits);
@($group_counts[$org_id]['total'] = intval($group_counts[$org_id]['total']) + $hits);
$rs_buckets->MoveNext();
}
uasort($group_counts, array("ChReportTopTicketsByContact", "sortCountsArrayByHits"));
$tpl->assign('group_counts', $group_counts);
// Chart
if ($by_address) {
$sql = sprintf("SELECT count(*) AS hits, a.id, a.email as name " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND t.team_id != 0 " . "GROUP BY a.id, a.email " . "ORDER BY hits DESC LIMIT 25 ", $start_time, $end_time);
} else {
//default is by org
$sql = sprintf("SELECT count(*) AS hits, a.contact_org_id, o.name " . "FROM ticket t " . "INNER JOIN address a ON t.first_wrote_address_id = a.id " . "INNER JOIN contact_org o ON a.contact_org_id = o.id " . "WHERE created_date > %d AND created_date <= %d " . "AND is_deleted = 0 " . "AND spam_score < 0.9000 " . "AND spam_training != 'S' " . "AND t.team_id != 0 " . "AND a.contact_org_id != 0 " . "GROUP BY a.contact_org_id, o.name " . "ORDER BY hits DESC LIMIT 25 ", $start_time, $end_time);
}
$rs = $db->Execute($sql);
$sorted_result = array();
$i = 0;
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$hits = intval($rs->fields['hits']);
$name = $rs->fields['name'];
$sorted_result[$i]['name'] = $name;
$sorted_result[$i]['hits'] = $hits;
$i++;
$rs->MoveNext();
}
}
//reverse the descending result because yui charts draws from the bottom up on the y-axis
$iter = 0;
$data = array();
$reversed = array_reverse($sorted_result);
foreach ($reversed as $result) {
$data[$iter++] = array('value' => $result['name'], 'hits' => $result['hits']);
}
$tpl->assign('data', $data);
$tpl->display('file:' . $this->tpl_path . '/reports/ticket/top_contacts_tickets/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>';
}
function render()
{
$tpl = DevblocksPlatform::getTemplateService();
$active_worker = CerberusApplication::getActiveWorker();
$memberships = $active_worker->getMemberships();
$response = DevblocksPlatform::getHttpResponse();
@($section = $response->path[1]);
//print_r($_REQUEST);exit();
//@$page = DevblocksPlatform::importGPC($_GET['password']);
@($page = DevblocksPlatform::importGPC($_REQUEST['page'], 'integer'));
if ($page == NULL) {
$page = 0;
}
if (isset($_POST['a2'])) {
@($section = $_POST['a2']);
} else {
@($section = $response->path[2]);
}
//print_r($section);
//echo $section;
switch ($section) {
case 'search':
$title = 'Search';
$query = $_POST['query'];
if ($query && false === strpos($query, '*')) {
$query = '*' . $query . '*';
}
if (!is_null($query)) {
$params = array();
$type = $_POST['type'];
switch ($type) {
case "mask":
$params[SearchFields_Ticket::TICKET_MASK] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_MASK, DevblocksSearchCriteria::OPER_LIKE, strtoupper($query));
break;
case "sender":
$params[SearchFields_Ticket::TICKET_FIRST_WROTE] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_FIRST_WROTE, DevblocksSearchCriteria::OPER_LIKE, strtolower($query));
break;
case "subject":
$params[SearchFields_Ticket::TICKET_SUBJECT] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_SUBJECT, DevblocksSearchCriteria::OPER_LIKE, $query);
break;
case "content":
$params[SearchFields_Ticket::TICKET_MESSAGE_CONTENT] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_MESSAGE_CONTENT, DevblocksSearchCriteria::OPER_LIKE, $query);
break;
}
} else {
//show the search form because no search has been submitted
$tpl->display('file:' . dirname(__FILE__) . '/templates/tickets/search.tpl');
return;
}
break;
case 'sidebar':
$groups = DAO_Group::getAll();
$tpl->assign('groups', $groups);
$group_buckets = DAO_Bucket::getTeams();
$tpl->assign('group_buckets', $group_buckets);
$workers = DAO_Worker::getAll();
$tpl->assign('workers', $workers);
$group_counts = DAO_Overview::getGroupTotals();
$tpl->assign('group_counts', $group_counts);
$waiting_counts = DAO_Overview::getWaitingTotals();
$tpl->assign('waiting_counts', $waiting_counts);
$worker_counts = DAO_Overview::getWorkerTotals();
$tpl->assign('worker_counts', $worker_counts);
$tpl->display('file:' . dirname(__FILE__) . '/templates/tickets/sidebar.tpl');
return;
break;
case 'overview':
default:
$workers = DAO_Worker::getAll();
$group_buckets = DAO_Bucket::getTeams();
$groups = DAO_Group::getAll();
@($filter = $response->path[3]);
switch ($filter) {
case 'group':
@($filter_group_id = $response->path[4]);
$params = array(SearchFields_Ticket::TICKET_CLOSED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CLOSED, '=', CerberusTicketStatus::OPEN), SearchFields_Ticket::TICKET_WAITING => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_WAITING, '=', 0), SearchFields_Ticket::TICKET_NEXT_WORKER_ID => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_NEXT_WORKER_ID, '=', 0));
if (!is_null($filter_group_id) && isset($groups[$filter_group_id])) {
$tpl->assign('filter_group_id', $filter_group_id);
$title = $groups[$filter_group_id]->name;
$params[SearchFields_Ticket::TICKET_TEAM_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID, '=', $filter_group_id);
@($filter_bucket_id = $response->path[5]);
if (!is_null($filter_bucket_id)) {
$tpl->assign('filter_bucket_id', $filter_bucket_id);
@($title .= ': ' . ($filter_bucket_id == 0 ? 'Inbox' : $group_buckets[$filter_group_id][$filter_bucket_id]->name));
$params[SearchFields_Ticket::TICKET_CATEGORY_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CATEGORY_ID, '=', $filter_bucket_id);
} else {
@($title .= ' (Spam Filtered)');
$params[SearchFields_Ticket::TICKET_SPAM_SCORE] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_SPAM_SCORE, '<=', '0.9000');
}
}
break;
case 'waiting':
@($filter_waiting_id = $response->path[4]);
$params = array(SearchFields_Ticket::TICKET_CLOSED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CLOSED, '=', CerberusTicketStatus::OPEN), SearchFields_Ticket::TICKET_WAITING => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_WAITING, '=', 1));
if (!is_null($filter_waiting_id) && isset($groups[$filter_waiting_id])) {
$tpl->assign('filter_waiting_id', $filter_waiting_id);
$title = '[Waiting] ' . $groups[$filter_waiting_id]->name;
$params[SearchFields_Ticket::TICKET_TEAM_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID, '=', $filter_waiting_id);
@($filter_bucket_id = $response->path[5]);
if (!is_null($filter_bucket_id)) {
$tpl->assign('filter_bucket_id', $filter_bucket_id);
@($title .= ': ' . ($filter_bucket_id == 0 ? 'Inbox' : $group_buckets[$filter_waiting_id][$filter_bucket_id]->name));
$params[SearchFields_Ticket::TICKET_CATEGORY_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CATEGORY_ID, '=', $filter_bucket_id);
}
}
break;
case 'worker':
@($filter_worker_id = $response->path[4]);
$params = array(SearchFields_Ticket::TICKET_CLOSED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CLOSED, '=', CerberusTicketStatus::OPEN), SearchFields_Ticket::TICKET_WAITING => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_WAITING, '=', 0), $params[SearchFields_Ticket::TICKET_TEAM_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID, 'in', array_keys($memberships)));
if (!is_null($filter_worker_id)) {
$tpl->assign('filter_bucket_id', $filter_bucket_id);
$title = "For " . $workers[$filter_worker_id]->getName();
$params[SearchFields_Ticket::TICKET_NEXT_WORKER_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_NEXT_WORKER_ID, '=', $filter_worker_id);
@($filter_group_id = $response->path[5]);
if (!is_null($filter_group_id) && isset($groups[$filter_group_id])) {
$title .= ' in ' . $groups[$filter_group_id]->name;
$params[SearchFields_Ticket::TICKET_TEAM_ID] = new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID, '=', $filter_group_id);
}
}
break;
case 'all':
default:
$title = 'All (Spam Filtered)';
$params = array(SearchFields_Ticket::TICKET_CLOSED => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CLOSED, '=', CerberusTicketStatus::OPEN), SearchFields_Ticket::TICKET_WAITING => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_WAITING, '=', 0), SearchFields_Ticket::TICKET_NEXT_WORKER_ID => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_NEXT_WORKER_ID, '=', 0), SearchFields_Ticket::TICKET_SPAM_SCORE => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_SPAM_SCORE, '<=', '0.9000'), SearchFields_Ticket::TICKET_TEAM_ID => new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID, 'in', array_keys($memberships)));
break;
}
// $params = array(
// new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_CLOSED,'=',CerberusTicketStatus::OPEN),
// new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_NEXT_WORKER_ID,'=',0),
// new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_SPAM_SCORE,'<=','0.9000'),
// new DevblocksSearchCriteria(SearchFields_Ticket::TICKET_TEAM_ID,'in',array_keys($memberships))
// );
// $title = "Overview";
break;
}
$mobileView = C4_AbstractViewLoader::getView('', "VIEW_MOBILE");
//print_r($mobileView);
if ($mobileView == NULL) {
$mobileView = new C4_MobileTicketView();
//C4_TicketView();
}
$mobileView->id = "VIEW_MOBILE";
$mobileView->name = $title;
$mobileView->view_columns = array(SearchFields_Ticket::TICKET_LAST_ACTION_CODE);
$mobileView->params = $params;
$mobileView->renderLimit = 10;
//$overViewDefaults->renderLimit;
$mobileView->renderPage = $page;
$mobileView->renderSortBy = SearchFields_Ticket::TICKET_UPDATED_DATE;
$mobileView->renderSortAsc = 0;
C4_AbstractViewLoader::setView($mobileView->id, $mobileView);
$views[] = $mobileView;
$tpl->assign('views', $views);
if ($filter == null) {
$filter = 'all';
}
$tpl->assign('filter', $filter);
$fid = $response->path[4];
if ($fid == null) {
$fid = '0';
}
$tpl->assign('fid', $fid);
$bucket_id = $response->path[5];
if ($bucket_id == null) {
$buket_id = 0;
}
$tpl->assign('bid', $bucket_id);
$tpl->assign('title', $title);
$tpl->assign('tickets', $tickets[0]);
$tpl->assign('next_page', $page + 1);
$tpl->assign('prev_page', $page - 1);
//print_r($tickets);exit();
$tpl->display('file:' . dirname(__FILE__) . '/templates/tickets.tpl');
}
function showBatchPanelAction()
{
@($ids = DevblocksPlatform::importGPC($_REQUEST['ids']));
@($view_id = DevblocksPlatform::importGPC($_REQUEST['view_id']));
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->_TPL_PATH);
$tpl->assign('view_id', $view_id);
$unique_sender_ids = array();
$unique_subjects = array();
if (!empty($ids)) {
$ticket_ids = DevblocksPlatform::parseCsvString($ids);
if (empty($ticket_ids)) {
break;
}
$tickets = DAO_Ticket::getTickets($ticket_ids);
if (is_array($tickets)) {
foreach ($tickets as $ticket) {
/* @var $ticket CerberusTicket */
$ptr =& $unique_sender_ids[$ticket->first_wrote_address_id];
$ptr = intval($ptr) + 1;
$ptr =& $unique_subjects[$ticket->subject];
$ptr = intval($ptr) + 1;
}
}
arsort($unique_subjects);
// sort by occurrences
$senders = DAO_Address::getWhere(sprintf("%s IN (%s)", DAO_Address::ID, implode(',', array_keys($unique_sender_ids))));
foreach ($senders as $sender) {
$ptr =& $unique_senders[$sender->email];
$ptr = intval($ptr) + 1;
}
arsort($unique_senders);
unset($senders);
unset($unique_sender_ids);
@$tpl->assign('ticket_ids', $ticket_ids);
@$tpl->assign('unique_senders', $unique_senders);
@$tpl->assign('unique_subjects', $unique_subjects);
}
// Teams
$teams = DAO_Group::getAll();
$tpl->assign('teams', $teams);
// Categories
$team_categories = DAO_Bucket::getTeams();
// [TODO] Cache these
$tpl->assign('team_categories', $team_categories);
$workers = DAO_Worker::getAllActive();
$tpl->assign('workers', $workers);
// Custom Fields
$custom_fields = DAO_CustomField::getBySource(ChCustomFieldSource_Ticket::ID);
$tpl->assign('custom_fields', $custom_fields);
$tpl->display('file:' . $this->_TPL_PATH . 'tickets/rpc/batch_panel.tpl');
}
function saveTabBucketsAction()
{
@($team_id = DevblocksPlatform::importGPC($_REQUEST['team_id'], 'integer'));
@($active_worker = CerberusApplication::getActiveWorker());
if (!$active_worker->isTeamManager($team_id) && !$active_worker->is_superuser) {
return;
}
// Inbox assignable
@($inbox_assignable = DevblocksPlatform::importGPC($_REQUEST['inbox_assignable'], 'integer', 0));
DAO_GroupSettings::set($team_id, DAO_GroupSettings::SETTING_INBOX_IS_ASSIGNABLE, intval($inbox_assignable));
//========== BUCKETS
@($ids = DevblocksPlatform::importGPC($_REQUEST['ids'], 'array'));
@($add_str = DevblocksPlatform::importGPC($_REQUEST['add'], 'string'));
@($pos = DevblocksPlatform::importGPC($_REQUEST['pos'], 'array'));
@($names = DevblocksPlatform::importGPC($_REQUEST['names'], 'array'));
@($assignables = DevblocksPlatform::importGPC($_REQUEST['is_assignable'], 'array'));
@($deletes = DevblocksPlatform::importGPC($_REQUEST['deletes'], 'array'));
// Updates
if (!empty($ids)) {
$cats = DAO_Bucket::getList($ids);
foreach ($ids as $idx => $id) {
@($cat = $cats[$id]);
if (is_object($cat)) {
$is_assignable = false === array_search($id, $assignables) ? 0 : 1;
$fields = array(DAO_Bucket::NAME => $names[$idx], DAO_Bucket::POS => intval($pos[$idx]), DAO_Bucket::IS_ASSIGNABLE => intval($is_assignable));
DAO_Bucket::update($id, $fields);
}
}
}
// Adds: Sort and insert team categories
$categories = DevblocksPlatform::parseCrlfString($add_str);
if (is_array($categories)) {
foreach ($categories as $category) {
$cat_id = DAO_Bucket::create($category, $team_id);
}
}
if (!empty($deletes)) {
DAO_Bucket::delete(array_values($deletes));
}
DevblocksPlatform::redirect(new DevblocksHttpResponse(array('groups', $team_id, 'buckets')));
}
function showTab()
{
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->tpl_path);
$tpl->cache_lifetime = "0";
$worker = CerberusApplication::getActiveWorker();
$tpl->assign('worker', $worker);
$groups = DAO_Group::getAll();
$tpl->assign('groups', $groups);
$group_buckets = DAO_Bucket::getTeams();
$tpl->assign('group_buckets', $group_buckets);
$memberships = $worker->getMemberships();
$tpl->assign('memberships', $memberships);
$addresses = DAO_AddressToWorker::getByWorker($worker->id);
$tpl->assign('addresses', $addresses);
@($notifications = DAO_WorkerMailForward::getWhere(sprintf("%s = %d", DAO_WorkerMailForward::WORKER_ID, $worker->id)));
$tpl->assign('notifications', $notifications);
$assign_notify_email = DAO_WorkerPref::get($worker->id, ChWatchersPlugin::WORKER_PREF_ASSIGN_EMAIL, '');
$tpl->assign('assign_notify_email', $assign_notify_email);
$tpl->display('file:' . $this->tpl_path . '/preferences/watchers.tpl');
}
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 integer $id
*/
static function deleteTeam($id)
{
if (empty($id)) {
return;
}
$db = DevblocksPlatform::getDatabaseService();
/*
* Notify anything that wants to know when groups delete.
*/
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('group.delete', array('group_ids' => array($id))));
$sql = sprintf("DELETE QUICK FROM team WHERE id = %d", $id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$sql = sprintf("DELETE QUICK FROM category WHERE team_id = %d", $id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
// [TODO] DAO_GroupSettings::deleteById();
$sql = sprintf("DELETE QUICK FROM group_setting WHERE group_id = %d", $id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$sql = sprintf("DELETE QUICK FROM worker_to_team WHERE team_id = %d", $id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$sql = sprintf("DELETE QUICK FROM group_inbox_filter WHERE group_id = %d", $id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
// DAO_GroupInboxFilter::deleteByMoveCodes(array('t'.$id));
self::clearCache();
DAO_Bucket::clearCache();
}
/**
* 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 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');
}
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;
}
}
