public static function isDatabaseEmpty()
{
$db = DevblocksPlatform::getDatabaseService();
if (is_null($db)) {
return true;
}
$tables = $db->MetaTables('TABLE', true);
return empty($tables);
}
function handleRequest(DevblocksHttpRequest $request)
{
$stack = $request->path;
$db = DevblocksPlatform::getDatabaseService();
// **** BEGIN AUTH
@($verb = $_SERVER['REQUEST_METHOD']);
@($header_date = $_SERVER['HTTP_DATE']);
@($header_signature = $_SERVER['HTTP_PORTSENSOR_AUTH']);
@($this->_payload = $this->_getRawPost());
@(list($auth_worker_email, $auth_signature) = explode(":", $header_signature, 2));
$url_parts = parse_url(DevblocksPlatform::getWebPath());
$url_path = $url_parts['path'];
$url_query = $this->_sortQueryString($_SERVER['QUERY_STRING']);
$string_to_sign_prefix = "{$verb}\n{$header_date}\n{$url_path}\n{$url_query}\n{$this->_payload}";
if (!$this->_validateRfcDate($header_date)) {
$this->_error("Access denied! (Invalid timestamp)");
}
// if(strpos($auth_access_key,'@')) { // WORKER-LEVEL AUTH
$results = DAO_Worker::getWhere(sprintf("%s = %s", DAO_Worker::EMAIL, $db->qstr($auth_worker_email)));
if (empty($results)) {
$this->_error("Access denied! (Invalid authentication)");
} else {
$worker = array_shift($results);
$this->setActiveWorker($worker);
}
if (null == $this->getActiveWorker()) {
$this->_error("Access denied! (Invalid worker)");
}
if (!$worker->hasPriv('plugin.usermeet.webapi')) {
$this->_error("Access denied! (No permission)");
}
$pass = $this->getActiveWorker()->pass;
$string_to_sign = "{$string_to_sign_prefix}\n{$pass}\n";
$compare_hash = base64_encode(sha1($string_to_sign, true));
if (0 != strcmp($auth_signature, $compare_hash)) {
$this->_error("Access denied! (Invalid password)");
}
// **** END APP AUTH
// Figure out our format by looking at the last path argument
@(list($command, $format) = explode('.', array_pop($stack)));
array_push($stack, $command);
$this->_format = $format;
// Call the verb as an action
$method = strtolower($verb) . 'Action';
if (method_exists($this, $method)) {
call_user_func(array(&$this, $method), $stack);
} else {
$this->_error("Invalid action.");
}
}
static function getValue($context, $context_values)
{
$total_time_all = -1;
if ($context_values['id']) {
// Adds total time worked per ticket to the token list.
$db = DevblocksPlatform::getDatabaseService();
$sql = "SELECT sum(tte.time_actual_mins) mins ";
$sql .= "FROM timetracking_entry tte ";
$sql .= sprintf("WHERE tte.source_id = %d ", $context_values['id']);
$sql .= "AND tte.source_extension_id = 'timetracking.source.ticket' ";
$sql .= "GROUP BY tte.source_id ";
$rs = $db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
if ($row = mysql_fetch_assoc($rs)) {
$total_time_all = intval($row['mins']);
} else {
$total_time_all = 0;
}
mysql_free_result($rs);
}
return $total_time_all;
}
/**
* @param Model_DevblocksEvent $event
*/
function handleEvent(Model_DevblocksEvent $event)
{
switch ($event->id) {
case 'cron.maint':
$records_removed = 0;
$logger = DevblocksPlatform::getConsoleLog();
$logger->info("[Answernet.com Maint] Starting Purging Contact Addresses task");
@set_time_limit(0);
// Unlimited (if possible)
@ini_set('memory_limit', '128M');
$logger->info("[Answernet.com Maint] Overloaded memory_limit to: " . ini_get('memory_limit'));
$logger->info("[Answernet.com Maint] Overloaded max_execution_time to: " . ini_get('max_execution_time'));
$runtime = microtime(true);
//Do something
$db = DevblocksPlatform::getDatabaseService();
$sql = "SELECT a.id ";
$sql .= "FROM address a ";
$sql .= "LEFT JOIN message m ON a.id = m.address_id ";
$sql .= "LEFT JOIN requester r ON a.id = r.address_id ";
$sql .= "LEFT JOIN ticket_comment tc ON a.id = tc.address_id ";
$sql .= "WHERE a.contact_org_id = 0 ";
$sql .= "AND m.address_id IS NULL ";
$sql .= "AND r.address_id IS NULL ";
$sql .= "AND tc.address_id IS NULL ";
$sql .= "ORDER BY a.id ASC ";
$rs = $db->Execute($sql);
while (!$rs->EOF) {
// Loop though the records.
DAO_Address::delete($rs->fields['id']);
// Increament the records removed connecter
$records_removed++;
$rs->MoveNext();
}
$logger->info("[Answernet.com Maint] Total Records Removed: " . $records_removed);
$logger->info("[Answernet.com Maint] Total Runtime: " . (microtime(true) - $runtime) * 1000 . " ms");
break;
}
}
/**
* Loads parameters unique to this extension instance. Returns an
* associative array indexed by parameter key.
*
* @private
* @return array
*/
private function _getParams()
{
// static $params = null;
if (empty($this->id)) {
return null;
}
// if(null != $params)
// return $params;
$params = $this->manifest->params;
$db = DevblocksPlatform::getDatabaseService();
$prefix = APP_DB_PREFIX != '' ? APP_DB_PREFIX . '_' : '';
// [TODO] Cleanup
$sql = sprintf("SELECT property,value " . "FROM %sproperty_store " . "WHERE extension_id=%s ", $prefix, $db->qstr($this->id));
$rs = $db->Execute($sql) or die(__CLASS__ . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$params[$rs->fields['property']] = $rs->fields['value'];
$rs->MoveNext();
}
}
return $params;
}
function run()
{
$logger = DevblocksPlatform::getConsoleLog();
$logger->info("[Maint] Starting Maintenance Task");
@ini_set('memory_limit', '64M');
$db = DevblocksPlatform::getDatabaseService();
// Give plugins a chance to run maintenance (nuke NULL rows, etc.)
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('cron.maint', array()));
// // [JAS] Remove any empty directories inside storage/import/new
// $importNewDir = APP_STORAGE_PATH . '/import/new' . DIRECTORY_SEPARATOR;
// $subdirs = glob($importNewDir . '*', GLOB_ONLYDIR);
// if ($subdirs !== false) {
// foreach($subdirs as $subdir) {
// $directory_empty = count(glob($subdir. DIRECTORY_SEPARATOR . '*')) === 0;
// if($directory_empty && is_writeable($subdir)) {
// rmdir($subdir);
// }
// }
// }
//
// $logger->info('[Maint] Cleaned up import directories.');
}
function setCustomerAccountNumberAction()
{
$db = DevblocksPlatform::getDatabaseService();
@($account_number = DevblocksPlatform::importGPC($_REQUEST['acc_num'], 'string', ''));
@($m_id = DevblocksPlatform::importGPC($_REQUEST['m_id'], 'string', ''));
// Now Confirm the account exists and is active
$account = array_shift(DAO_CustomerAccount::getWhere(sprintf("%s = %d AND %s = '0' ", DAO_CustomerAccount::ACCOUNT_NUMBER, $account_number, DAO_CustomerAccount::IS_DISABLED)));
if (!isset($account)) {
return;
}
$message_obj = DAO_Message::get($m_id);
if (!isset($message_obj)) {
return;
}
$fields = get_object_vars($message_obj);
$fields[DAO_Message::ACCOUNT_ID] = $account->id;
$fields[DAO_Message::IMPORT_STATUS] = 0;
// Requeue
$m_status = DAO_Message::update($m_id, $fields);
// Give plugins a chance to note a message is assigned
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('message.account.assign', array('account_id' => $account->id, 'message_id' => $m_id)));
echo json_encode($fields);
}
function getTemplatesAction()
{
@($txt_name = DevblocksPlatform::importGPC($_REQUEST['txt_name'], 'string', ''));
@($reply_id = DevblocksPlatform::importGPC($_REQUEST['reply_id'], 'integer'));
@($folder = DevblocksPlatform::importGPC($_REQUEST['folder'], 'string', ''));
@($type = DevblocksPlatform::importGPC($_REQUEST['type'], 'integer', 0));
$db = DevblocksPlatform::getDatabaseService();
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->_TPL_PATH);
$tpl->assign('reply_id', $reply_id);
$tpl->assign('txt_name', $txt_name);
if (empty($folder)) {
$where = sprintf("%s = %d", DAO_MailTemplate::TEMPLATE_TYPE, $type);
} else {
$where = sprintf("%s = %s AND %s = %d ", DAO_MailTemplate::FOLDER, $db->qstr($folder), DAO_MailTemplate::TEMPLATE_TYPE, $type);
}
$templates = DAO_MailTemplate::getWhere($where);
$tpl->assign('templates', $templates);
$tpl->display('file:' . $this->_TPL_PATH . 'display/rpc/email_templates/template_results.tpl');
}
/**
* Returns an array of all contributed plugin manifests.
*
* @static
* @return DevblocksPluginManifest[]
*/
static function getPluginRegistry()
{
$cache = self::getCacheService();
if (false !== ($plugins = $cache->load(self::CACHE_PLUGINS))) {
return $plugins;
}
$db = DevblocksPlatform::getDatabaseService();
if (is_null($db)) {
return;
}
$prefix = APP_DB_PREFIX != '' ? APP_DB_PREFIX . '_' : '';
// [TODO] Cleanup
$sql = sprintf("SELECT * " . "FROM %splugin p " . "ORDER BY p.enabled DESC, p.name ASC ", $prefix);
$rs = $db->Execute($sql) or die(__CLASS__ . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
while (!$rs->EOF) {
$plugin = new DevblocksPluginManifest();
@($plugin->id = $rs->fields['id']);
@($plugin->enabled = intval($rs->fields['enabled']));
@($plugin->name = $rs->fields['name']);
@($plugin->description = $rs->fields['description']);
@($plugin->author = $rs->fields['author']);
@($plugin->revision = intval($rs->fields['revision']));
@($plugin->link = $rs->fields['link']);
@($plugin->is_configurable = intval($rs->fields['is_configurable']));
@($plugin->file = $rs->fields['file']);
@($plugin->class = $rs->fields['class']);
@($plugin->dir = $rs->fields['dir']);
if (file_exists(DEVBLOCKS_PLUGIN_PATH . $plugin->dir)) {
$plugins[$plugin->id] = $plugin;
}
$rs->MoveNext();
}
$sql = sprintf("SELECT p.id, p.name, p.params, p.plugin_id " . "FROM %sevent_point p ", $prefix);
$rs = $db->Execute($sql) or die(__CLASS__ . ':' . $db->ErrorMsg() . var_dump(debug_backtrace()));
/* @var $rs ADORecordSet */
while (!$rs->EOF) {
$point = new DevblocksEventPoint();
$point->id = $rs->fields['id'];
$point->name = $rs->fields['name'];
$point->plugin_id = $rs->fields['plugin_id'];
$params = $rs->fields['params'];
$point->params = !empty($params) ? unserialize($params) : array();
if (isset($plugins[$point->plugin_id])) {
$plugins[$point->plugin_id]->event_points[$point->id] = $point;
}
$rs->MoveNext();
}
// $extensions = DevblocksPlatform::getExtensionRegistry();
// foreach($extensions as $extension_id => $extension) { /* @var $extension DevblocksExtensionManifest */
// $plugin_id = $extension->plugin_id;
// if(isset($plugin_id)) {
// $plugins[$plugin_id]->extensions[$extension_id] = $extension;
// }
// }
$cache->save($plugins, self::CACHE_PLUGINS);
return $plugins;
}
* A legitimate license entitles you to support, access to the developer
* mailing list, the ability to participate in betas and the warm fuzzy
* feeling of feeding a couple obsessed developers who want to help you get
* more done than 'the other guy'.
*
* - Jeff Standen, Mike Fogg, Brenan Cavish, Darren Sugita, Dan Hildebrandt
* and Joe Geck.
* WEBGROUP MEDIA LLC. - Developers of Cerberus Helpdesk
*/
if (version_compare(PHP_VERSION, "5.2", "<")) {
die("Cerberus Helpdesk 5.x requires PHP 5.2 or later.");
}
require getcwd() . '/framework.config.php';
require DEVBLOCKS_PATH . 'Devblocks.class.php';
// If this is our first run, redirect to the installer
if ('' == APP_DB_DRIVER || '' == APP_DB_HOST || '' == APP_DB_DATABASE || null == ($db = DevblocksPlatform::getDatabaseService()) || DevblocksPlatform::isDatabaseEmpty()) {
header('Location: ' . dirname($_SERVER['PHP_SELF']) . '/install/index.php');
// [TODO] change this to a meta redirect
exit;
}
require APP_PATH . '/api/Application.class.php';
DevblocksPlatform::init();
DevblocksPlatform::setExtensionDelegate('C4_DevblocksExtensionDelegate');
// Request
$request = DevblocksPlatform::readRequest();
// Patches (if not on the patch page)
if (@0 != strcasecmp(@$request->path[0], "update") && !DevblocksPlatform::versionConsistencyCheck()) {
DevblocksPlatform::redirect(new DevblocksHttpResponse(array('update', 'locked')));
}
//DevblocksPlatform::readPlugins();
$session = DevblocksPlatform::getSessionService();
static function get_timestamp($tpl_name, &$tpl_timestamp, $smarty_obj)
{
/* @var $smarty_obj Smarty */
list($plugin_id, $tag, $tpl_path) = explode(':', $tpl_name, 3);
if (empty($plugin_id) || empty($tpl_path)) {
return false;
}
$plugins = DevblocksPlatform::getPluginRegistry();
$db = DevblocksPlatform::getDatabaseService();
if (null == ($plugin = @$plugins[$plugin_id])) {
/* @var $plugin DevblocksPluginManifest */
return false;
}
// Check if template is overloaded in DB/cache
$matches = DAO_DevblocksTemplate::getWhere(sprintf("plugin_id = %s AND path = %s %s", $db->qstr($plugin_id), $db->qstr($tpl_path), !empty($tag) ? sprintf("AND tag = %s ", $db->qstr($tag)) : ""));
if (!empty($matches)) {
$match = array_shift($matches);
/* @var $match Model_DevblocksTemplate */
// echo time(),"==(DB)",$match->last_updated,"<BR>";
$tpl_timestamp = $match->last_updated;
return true;
}
// If not in DB, check plugin's relative path on disk
$path = APP_PATH . '/' . $plugin->dir . '/templates/' . $tpl_path;
if (!file_exists($path)) {
return false;
}
$stat = stat($path);
$tpl_timestamp = $stat['mtime'];
// echo time(),"==(DISK)",$stat['mtime'],"<BR>";
return true;
}
/**
* Enter description here...
*
* @param DevblocksSearchCriteria[] $params
* @param integer $limit
* @param integer $page
* @param string $sortBy
* @param boolean $sortAsc
* @param boolean $withCounts
* @return array
*/
static function search($params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true)
{
$db = DevblocksPlatform::getDatabaseService();
$fields = SearchFields_TicketAuditLog::getFields();
// Sanitize
if (!isset($fields[$sortBy])) {
$sortBy = null;
}
list($tables, $wheres) = parent::_parseSearchParams($params, array(), $fields, $sortBy);
$start = $page * $limit;
// [JAS]: 1-based [TODO] clean up + document
$total = -1;
$sql = sprintf("SELECT " . "l.id as %s, " . "l.ticket_id as %s, " . "l.worker_id as %s, " . "l.change_date as %s, " . "l.change_field as %s, " . "l.change_value as %s " . "FROM ticket_audit_log l ", SearchFields_TicketAuditLog::ID, SearchFields_TicketAuditLog::TICKET_ID, SearchFields_TicketAuditLog::WORKER_ID, SearchFields_TicketAuditLog::CHANGE_DATE, SearchFields_TicketAuditLog::CHANGE_FIELD, SearchFields_TicketAuditLog::CHANGE_VALUE) . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : "") . (!empty($sortBy) ? sprintf("ORDER BY %s %s", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : "");
// [TODO] Could push the select logic down a level too
if ($limit > 0) {
$rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
} else {
$rs = $db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$total = $rs->RecordCount();
}
$results = array();
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$result = array();
foreach ($rs->fields as $f => $v) {
$result[$f] = $v;
}
$id = intval($rs->fields[SearchFields_TicketAuditLog::ID]);
$results[$id] = $result;
$rs->MoveNext();
}
}
// [JAS]: Count all
if ($withCounts) {
$rs = $db->Execute($sql);
$total = $rs->RecordCount();
}
return array($results, $total);
}
function handleRequest(DevblocksHttpRequest $request)
{
$stack = $request->path;
$db = DevblocksPlatform::getDatabaseService();
// **** BEGIN AUTH
@($verb = $_SERVER['REQUEST_METHOD']);
@($header_date = $_SERVER['HTTP_DATE']);
@($header_signature = $_SERVER['HTTP_CERB4_AUTH']);
@($this->_payload = $this->_getRawPost());
@(list($auth_access_key, $auth_signature) = explode(":", $header_signature, 2));
$url_parts = parse_url(DevblocksPlatform::getWebPath());
$url_path = $url_parts['path'];
$url_query = $this->_sortQueryString($_SERVER['QUERY_STRING']);
$string_to_sign_prefix = "{$verb}\n{$header_date}\n{$url_path}\n{$url_query}\n{$this->_payload}";
if (!$this->_validateRfcDate($header_date)) {
$this->_error("Access denied! (Invalid timestamp)");
}
if (strpos($auth_access_key, '@')) {
// WORKER-LEVEL AUTH
$workers = DAO_Worker::getAll();
foreach ($workers as $worker) {
/* @var $worker CerberusWorker */
if ($worker->email == $auth_access_key) {
$this->setActiveWorker($worker);
break;
}
}
if (null == $this->getActiveWorker()) {
$this->_error("Access denied! (Invalid worker)");
}
$pass = $this->getActiveWorker()->pass;
$string_to_sign = "{$string_to_sign_prefix}\n{$pass}\n";
$compare_hash = base64_encode(sha1($string_to_sign, true));
if (0 != strcmp($auth_signature, $compare_hash)) {
$this->_error("Access denied! (Invalid password)");
}
} else {
// APP-LEVEL AUTH
$stored_keychains = DAO_WebapiKey::getWhere(sprintf("%s = %s", DAO_WebapiKey::ACCESS_KEY, $db->qstr(str_replace(' ', '', $auth_access_key))));
/* @var $stored_keychain Model_WebApiKey */
if (!empty($stored_keychains)) {
@($stored_keychain = array_shift($stored_keychains));
@($auth_secret_key = $stored_keychain->secret_key);
@($auth_rights = $stored_keychain->rights);
$string_to_sign = "{$string_to_sign_prefix}\n{$auth_secret_key}\n";
$compare_hash = base64_encode(sha1($string_to_sign, true));
if (0 != strcmp($auth_signature, $compare_hash)) {
$this->_error("Access denied! (Invalid signature)");
}
// Check that this IP is allowed to perform the VERB
if (!$stored_keychain->isValidIp($_SERVER['REMOTE_ADDR'])) {
$this->_error(sprintf("Access denied! (IP %s not authorized)", $_SERVER['REMOTE_ADDR']));
}
} else {
$this->_error("Access denied! (Unknown access key)");
}
}
// **** END APP AUTH
// Figure out our format by looking at the last path argument
@(list($command, $format) = explode('.', array_pop($stack)));
array_push($stack, $command);
$this->_format = $format;
if (null != $this->getActiveWorker()) {
$method = strtolower($verb) . 'WorkerAction';
if (method_exists($this, $method)) {
call_user_func(array(&$this, $method), $stack);
}
} else {
$method = strtolower($verb) . 'Action';
if (method_exists($this, $method)) {
call_user_func(array(&$this, $method), $stack, $stored_keychain);
}
}
}
function run()
{
$logger = DevblocksPlatform::getConsoleLog();
$logger->info("[Maint] Starting Maintenance Task");
@ini_set('memory_limit', '64M');
$db = DevblocksPlatform::getDatabaseService();
// Purge Deleted Content
$purge_waitdays = intval($this->getParam('purge_waitdays', 7));
$purge_waitsecs = time() - intval($purge_waitdays) * 86400;
$sql = sprintf("DELETE QUICK FROM ticket " . "WHERE is_deleted = 1 " . "AND updated_date < %d ", $purge_waitsecs);
$db->Execute($sql);
$logger->info("[Maint] Purged " . $db->Affected_Rows() . " ticket records.");
// Give plugins a chance to run maintenance (nuke NULL rows, etc.)
$eventMgr = DevblocksPlatform::getEventService();
$eventMgr->trigger(new Model_DevblocksEvent('cron.maint', array()));
// Nuke orphaned words from the Bayes index
// [TODO] Make this configurable from job
$sql = "DELETE FROM bayes_words WHERE nonspam + spam < 2";
// only 1 occurrence
$db->Execute($sql);
$logger->info('[Maint] Purged ' . $db->Affected_Rows() . ' obscure spam words.');
// [mdf] Remove any empty directories inside storage/mail/new
$mailDir = APP_MAIL_PATH . 'new' . DIRECTORY_SEPARATOR;
$subdirs = glob($mailDir . '*', GLOB_ONLYDIR);
if ($subdirs !== false) {
foreach ($subdirs as $subdir) {
$directory_empty = count(glob($subdir . DIRECTORY_SEPARATOR . '*')) === 0;
if ($directory_empty && is_writeable($subdir)) {
rmdir($subdir);
}
}
}
$logger->info('[Maint] Cleaned up mail directories.');
// [JAS] Remove any empty directories inside storage/import/new
$importNewDir = APP_STORAGE_PATH . '/import/new' . DIRECTORY_SEPARATOR;
$subdirs = glob($importNewDir . '*', GLOB_ONLYDIR);
if ($subdirs !== false) {
foreach ($subdirs as $subdir) {
$directory_empty = count(glob($subdir . DIRECTORY_SEPARATOR . '*')) === 0;
if ($directory_empty && is_writeable($subdir)) {
rmdir($subdir);
}
}
}
$logger->info('[Maint] Cleaned up import directories.');
}
function showTabSettingsAction()
{
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->_TPL_PATH);
$license = FegLicense::getInstance();
$tpl->assign('license', $license);
$db = DevblocksPlatform::getDatabaseService();
$rs = $db->Execute("SHOW TABLE STATUS");
$total_db_size = 0;
$total_db_data = 0;
$total_db_indexes = 0;
$total_db_slack = 0;
$total_file_size = 0;
// [TODO] This would likely be helpful to the /debug controller
if (null != ($row = mysql_fetch_assoc($rs))) {
$table_name = $row['Name'];
$table_size_data = intval($row['Data_length']);
$table_size_indexes = intval($row['Index_length']);
$table_size_slack = intval($row['Data_free']);
$total_db_size += $table_size_data + $table_size_indexes;
$total_db_data += $table_size_data;
$total_db_indexes += $table_size_indexes;
$total_db_slack += $table_size_slack;
}
mysql_free_result($rs);
// $sql = "SELECT SUM(file_size) FROM attachment";
// $total_file_size = intval($db->GetOne($sql));
$tpl->assign('total_db_size', number_format($total_db_size / 1048576, 2));
$tpl->assign('total_db_data', number_format($total_db_data / 1048576, 2));
$tpl->assign('total_db_indexes', number_format($total_db_indexes / 1048576, 2));
$tpl->assign('total_db_slack', number_format($total_db_slack / 1048576, 2));
// $tpl->assign('total_file_size', number_format($total_file_size/1048576,2));
$tpl->display('file:' . $this->_TPL_PATH . 'setup/tabs/settings/index.tpl');
}
/**
* Enter description here...
*
* @param DevblocksSearchCriteria[] $params
* @param integer $limit
* @param integer $page
* @param string $sortBy
* @param boolean $sortAsc
* @param boolean $withCounts
* @return array
*/
static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true)
{
$db = DevblocksPlatform::getDatabaseService();
$fields = SearchFields_FeedbackEntry::getFields();
// Sanitize
if (!isset($fields[$sortBy])) {
$sortBy = null;
}
list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy);
$start = $page * $limit;
// [JAS]: 1-based [TODO] clean up + document
$select_sql = sprintf("SELECT " . "f.id as %s, " . "f.log_date as %s, " . "f.worker_id as %s, " . "f.quote_text as %s, " . "f.quote_mood as %s, " . "f.quote_address_id as %s, " . "f.source_url as %s, " . "a.email as %s ", SearchFields_FeedbackEntry::ID, SearchFields_FeedbackEntry::LOG_DATE, SearchFields_FeedbackEntry::WORKER_ID, SearchFields_FeedbackEntry::QUOTE_TEXT, SearchFields_FeedbackEntry::QUOTE_MOOD, SearchFields_FeedbackEntry::QUOTE_ADDRESS_ID, SearchFields_FeedbackEntry::SOURCE_URL, SearchFields_FeedbackEntry::ADDRESS_EMAIL);
$join_sql = "FROM feedback_entry f " . "LEFT JOIN address a ON (f.quote_address_id=a.id) ";
// [JAS]: Dynamic table joins
// (isset($tables['o']) ? "LEFT JOIN contact_org o ON (o.id=tt.debit_org_id)" : " ").
// (isset($tables['mc']) ? "INNER JOIN message_content mc ON (mc.message_id=m.id)" : " ").
// Custom field joins
list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables($tables, $params, 'f.id', $select_sql, $join_sql);
$where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : "");
$sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " ";
$sql = $select_sql . $join_sql . $where_sql . ($has_multiple_values ? 'GROUP BY f.id ' : '') . $sort_sql;
$rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$results = array();
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$result = array();
foreach ($rs->fields as $f => $v) {
$result[$f] = $v;
}
$id = intval($rs->fields[SearchFields_FeedbackEntry::ID]);
$results[$id] = $result;
$rs->MoveNext();
}
}
// [JAS]: Count all
$total = -1;
if ($withCounts) {
$count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT f.id) " : "SELECT COUNT(f.id) ") . $join_sql . $where_sql;
$total = $db->GetOne($count_sql);
}
return array($results, $total);
}
private static function gc()
{
$db = DevblocksPlatform::getDatabaseService();
$sql = sprintf("DELETE QUICK FROM community_session WHERE updated < %d", time() - 60 * 60);
$db->Execute($sql);
}
function getCountryAutoCompletionsAction()
{
@($starts_with = DevblocksPlatform::importGPC($_REQUEST['q'], 'string', ''));
$db = DevblocksPlatform::getDatabaseService();
// [TODO] Possibly internalize this exposed query.
$sql = sprintf("SELECT DISTINCT country FROM contact_org WHERE country LIKE '%s%%' ORDER BY country", $starts_with);
$rs = $db->Execute($sql);
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
echo $rs->fields['country'];
echo "\n";
$rs->MoveNext();
}
}
exit;
}
private function _getSearchAction($path)
{
@($p_query = DevblocksPlatform::importGPC($_REQUEST['query'], 'string', ''));
@($p_resources = DevblocksPlatform::importGPC($_REQUEST['resources'], 'string', ''));
$resource_where = null;
// Specific topics only?
if (!empty($p_resources)) {
$db = DevblocksPlatform::getDatabaseService();
$resource_ids = DevblocksPlatform::parseCsvString($p_resources);
if (!empty($resource_ids)) {
$resource_where = sprintf("%s IN (%s)", DAO_FnrExternalResource::ID, $db->qstr(implode(',', $resource_ids)));
}
}
$resources = DAO_FnrExternalResource::getWhere($resource_where);
$feeds = Model_FnrExternalResource::searchResources($resources, $p_query);
$xml_out = new SimpleXMLElement("<resources></resources>");
foreach ($feeds as $matches) {
$eMatch = $xml_out->addChild("resource");
$eMatch->addChild('name', $matches['name']);
$eMatch->addChild('topic', $matches['topic_name']);
$eMatch->addChild('link', $matches['feed']->link);
$eResults = $eMatch->addChild("results");
foreach ($matches['feed'] as $item) {
$eResult = $eResults->addChild("result");
if ($item instanceof Zend_Feed_Entry_Rss) {
$eResult->addChild('title', (string) $item->title());
$eResult->addChild('link', (string) $item->link());
$eResult->addChild('date', (string) $item->pubDate());
$eResult->addChild('description', (string) $item->description());
} elseif ($item instanceof Zend_Feed_Atom) {
$eResult->addChild('title', (string) $item->title());
$eResult->addChild('link', (string) $item->link['href']);
$eResult->addChild('date', (string) $item->published());
$eResult->addChild('description', (string) $item->summary());
}
}
}
$this->_render($xml_out->asXML());
}
public static function deleteByFieldId($field_id)
{
$db = DevblocksPlatform::getDatabaseService();
$tables = array('custom_field_stringvalue', 'custom_field_clobvalue', 'custom_field_numbervalue');
foreach ($tables as $table) {
$sql = sprintf("DELETE QUICK FROM %s WHERE field_id = %d", $table, $field_id);
$db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
}
}
* promise spending a couple bucks [Euro, Yuan, Rupees, Galactic Credits] to
* let us take over your shared e-mail headache is a worthwhile investment.
* It will give you a sense of control over your in-box that you probably
* haven't had since spammers found you in a game of "E-mail Address
* Battleship". Miss. Miss. You sunk my in-box!
*
* A legitimate license entitles you to support, access to the developer
* mailing list, the ability to participate in betas and the warm fuzzy
* feeling of feeding a couple obsessed developers who want to help you get
* more done than 'the other guy'.
*
* - Jeff Standen, Mike Fogg, Brenan Cavish, Darren Sugita, Dan Hildebrandt
* and Joe Geck.
* WEBGROUP MEDIA LLC. - Developers of Cerberus Helpdesk
*/
$db = DevblocksPlatform::getDatabaseService();
$datadict = NewDataDictionary($db, 'mysql');
/* @var $datadict ADODB2_mysql */
// ,'mysql'
$tables = $datadict->MetaTables();
$tables = array_flip($tables);
// `attachment` =============================
$columns = $datadict->MetaColumns('attachment');
$indexes = $datadict->MetaIndexes('attachment', false);
if (!isset($indexes['message_id'])) {
$sql = $datadict->CreateIndexSQL('message_id', 'attachment', 'message_id');
$datadict->ExecuteSQLArray($sql);
}
// `kb_category` =============================
if (!isset($tables['kb_category'])) {
$flds = "\r\n\t\tid I4 DEFAULT 0 NOTNULL PRIMARY,\r\n\t\tparent_id I4 DEFAULT 0 NOTNULL,\r\n\t\tname C(64) DEFAULT '' NOTNULL\r\n\t";
function AnswernetMetlifeReportDRReport($RunFromCron = 0)
{
$db = DevblocksPlatform::getDatabaseService();
$translate = DevblocksPlatform::getTranslationService();
if ($RunFromCron) {
$logger = DevblocksPlatform::getConsoleLog();
}
$radius = 12;
$start_time = 0;
date_default_timezone_set('Etc/UTC');
// Security
if (null == ($active_worker = CerberusApplication::getActiveWorker())) {
die($translate->_('common.access_denied'));
}
// import dates from form
@($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', ''));
if (empty($start)) {
$start = "Yesterday";
}
$start_time = strtotime($start);
if ($start_time === false) {
$start = "Yesterday";
$start_time = strtotime($start);
}
if (empty($start_time) || !is_numeric($start_time)) {
return;
}
$start_ofday = mktime(0, 0, 0, date("m", $start_time), date("d", $start_time), date("Y", $start_time));
$end_ofday = $start_ofday + 86400;
if ($RunFromCron) {
$logger->info("[Answernet.com] Gerating the Metlife DR Report.");
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.report.dr.date.from.text') . date(" Y-m-d H:i:s ", $start_ofday) . $translate->_('answernet.er.metlife.report.dr.date.to.text') . date(" Y-m-d H:i:s T", $end_ofday));
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.generate.report'));
} else {
print '<br><br><br>';
print $translate->_('answernet.er.metlife.report.dr.date.from.text');
print date(" Y-m-d H:i:s ", $start_ofday);
print $translate->_('answernet.er.metlife.report.dr.date.to.text');
print date(" Y-m-d H:i:s T", $end_ofday);
print '<br>';
print $translate->_('answernet.er.metlife.generate.report');
}
$filename = "report-metlife-dr-" . date("Ymd", $start_time) . ".xls";
$full_filename = getcwd() . '/storage/answernet/' . $filename;
if ($RunFromCron) {
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.generating'));
} else {
print '<br>';
print $translate->_('answernet.er.metlife.generating');
}
// Create new Excel Spreadsheet.
$workbook = new Spreadsheet_Excel_Writer($full_filename);
// Create Open Status Tab and set Column Width and Row Hight.
$worksheet_transaction =& $workbook->addWorksheet('Transaction Report');
$worksheet_transaction->setColumn(0, 0, $radius * 0.85);
$worksheet_transaction->setColumn(1, 1, $radius * 0.85);
$worksheet_transaction->setColumn(2, 2, $radius * 0.35);
$worksheet_transaction->setColumn(3, 3, $radius * 1.65);
$worksheet_transaction->setColumn(4, 4, $radius * 1.85);
$worksheet_transaction->setColumn(5, 5, $radius * 1.16);
$worksheet_transaction->setColumn(6, 6, $radius * 2.6);
$worksheet_transaction->setColumn(7, 8, $radius * 0.87);
$worksheet_transaction->setColumn(9, 9, $radius * 3.28);
$worksheet_transaction->setColumn(10, 10, $radius * 1.34);
// $worksheet_open_status->setRow(0, 28);
// $worksheet_open_status->setRow(2, 32);
// Create Open Status Tab and set Column Width and Row Hight.
$worksheet_open_status =& $workbook->addWorksheet('Open DR Report');
$worksheet_open_status->setColumn(0, 0, $radius * 0.85);
$worksheet_open_status->setColumn(1, 1, $radius * 0.35);
$worksheet_open_status->setColumn(2, 2, $radius * 0.7);
$worksheet_open_status->setColumn(3, 3, $radius * 1.65);
$worksheet_open_status->setColumn(4, 4, $radius * 1.85);
$worksheet_open_status->setColumn(5, 5, $radius * 1.16);
$worksheet_open_status->setColumn(6, 6, $radius * 2.6);
$worksheet_open_status->setColumn(7, 8, $radius * 0.87);
$worksheet_open_status->setColumn(9, 9, $radius * 3.28);
$worksheet_open_status->setColumn(10, 10, $radius * 1.34);
// $worksheet_open_status->setRow(0, 28);
// $worksheet_open_status->setRow(2, 32);
// Create monthly Tab and set Column Width and Row Hight.
$worksheet_monthly =& $workbook->addWorksheet('Month DR Summary');
$worksheet_monthly->setColumn(0, 0, $radius * 2.46);
$worksheet_monthly->setColumn(1, 1, $radius * 0.47);
$worksheet_monthly->setColumn(2, 2, $radius * 0.63);
$worksheet_monthly->setColumn(3, 3, $radius * 0.6899999999999999);
$worksheet_monthly->setColumn(4, 4, $radius * 0.6899999999999999);
$worksheet_monthly->setColumn(5, 5, $radius * 0.6899999999999999);
// $worksheet_monthly->setRow(0, 56);
// Formats used thoughout the workbook.
$format_general =& $workbook->addFormat();
$format_general->setBorder(1);
$format_general->setHAlign('left');
$format_general->setTextWrap();
$format_general_title =& $workbook->addFormat();
$format_general_title->setBorder(1);
$format_general_title->setColor('black');
$format_general_title->setFgColor('silver');
$format_general_title->setHAlign('left');
$format_general_title->setVAlign('vjustify');
$format_general_title->setVAlign('top');
$format_general_nowrap =& $workbook->addFormat();
$format_general_nowrap->setBorder(1);
// Setup templating for the formating of certain cells in the Monthly Group.
$format_monthly_title =& $workbook->addFormat();
$format_monthly_title->setColor(8);
$format_monthly_title->setFgColor(35);
$format_monthly_title->setBorder(1);
$format_monthly_title->setBold();
$format_monthly_title->setHAlign('center');
$format_monthly_title->setVAlign('vjustify');
$format_monthly_title->setVAlign('top');
$format_monthly_title->setTextWrap();
$format_monthly_title->setAlign('merge');
// Added headers since they never change in the transaction Group.
$worksheet_transaction->write(0, 0, 'Status', $format_general_title);
$worksheet_transaction->write(0, 1, 'Due Date', $format_general_title);
$worksheet_transaction->write(0, 2, 'SLA', $format_general_title);
$worksheet_transaction->write(0, 3, 'Date Received', $format_general_title);
$worksheet_transaction->write(0, 4, 'RM Name', $format_general_title);
$worksheet_transaction->write(0, 5, 'RM Employee id', $format_general_title);
$worksheet_transaction->write(0, 6, 'Request Type', $format_general_title);
$worksheet_transaction->write(0, 7, 'MetLife Staff', $format_general_title);
$worksheet_transaction->write(0, 8, 'New Hire', $format_general_title);
$worksheet_transaction->write(0, 9, 'Nates (email body)', $format_general_title);
$worksheet_transaction->write(0, 10, 'Ticket Mask', $format_general_title);
// Added headers since they never change in the Open Status Group.
$worksheet_open_status->write(0, 0, 'Due Date', $format_general_title);
$worksheet_open_status->write(0, 1, 'SLA', $format_general_title);
$worksheet_open_status->write(0, 2, 'Overdue', $format_general_title);
$worksheet_open_status->write(0, 3, 'Date Received', $format_general_title);
$worksheet_open_status->write(0, 4, 'RM Name', $format_general_title);
$worksheet_open_status->write(0, 5, 'RM Employee id', $format_general_title);
$worksheet_open_status->write(0, 6, 'Request Type', $format_general_title);
$worksheet_open_status->write(0, 7, 'MetLife Staff', $format_general_title);
$worksheet_open_status->write(0, 8, 'New Hire', $format_general_title);
$worksheet_open_status->write(0, 9, 'Nates (email body)', $format_general_title);
$worksheet_open_status->write(0, 10, 'Ticket Mask', $format_general_title);
// Added headers since they never change in the monthly Group.
$month_text = date("F-y", $start_time);
$worksheet_monthly->write(0, 0, $month_text, $format_monthly_title);
$worksheet_monthly->write(0, 1, '', $format_monthly_title);
$worksheet_monthly->write(0, 2, '', $format_monthly_title);
$worksheet_monthly->write(0, 3, '', $format_monthly_title);
$worksheet_monthly->write(0, 4, '', $format_monthly_title);
$worksheet_monthly->write(0, 5, '', $format_monthly_title);
$worksheet_monthly->write(1, 0, 'DR Summary', $format_general_title);
$worksheet_monthly->write(1, 1, '', $format_general_title);
$worksheet_monthly->write(2, 0, 'All DRs incoming*', $format_general);
$worksheet_monthly->write(3, 0, 'DRs Sent to MetLife', $format_general);
$worksheet_monthly->write(4, 0, 'DRs Completed', $format_general);
$worksheet_monthly->write(5, 0, 'Average time to reply (day)', $format_general);
$worksheet_monthly->write(6, 0, 'Missed DR SLA', $format_general);
$worksheet_monthly->write(7, 0, 'DR escalations', $format_general);
$worksheet_monthly->write(8, 0, 'DR Unique Users', $format_general);
$worksheet_monthly->write(9, 0, 'New Hires (30 Days)', $format_general);
$worksheet_monthly->write(10, 0, '', $format_general);
$worksheet_monthly->write(10, 1, '', $format_general);
$worksheet_monthly->write(11, 0, 'DR Categories', $format_general_title);
$worksheet_monthly->write(11, 1, '#s', $format_general_title);
$worksheet_monthly->write(11, 2, 'Avg SLA', $format_general_title);
$worksheet_monthly->write(11, 3, 'Linda #s', $format_general_title);
$worksheet_monthly->write(11, 4, 'Colin #s', $format_general_title);
$worksheet_monthly->write(11, 5, 'Sarfraz #s', $format_general_title);
$worksheet_monthly->write(12, 0, 'Import Contacts - New hire', $format_general);
$worksheet_monthly->write(13, 0, 'Import Contacts', $format_general);
$worksheet_monthly->write(14, 0, 'Create mailing list from exiting date', $format_general);
$worksheet_monthly->write(15, 0, 'Update existing contacts - batch', $format_general);
$worksheet_monthly->write(16, 0, 'Missing or incorrect customer info', $format_general);
$worksheet_monthly->write(17, 0, 'Fix duplicate contacts', $format_general);
$worksheet_monthly->write(18, 0, 'Export third party file', $format_general);
$worksheet_monthly->write(19, 0, 'Other', $format_general);
$worksheet_monthly->write(20, 0, 'Total', $format_general_title);
$worksheet_monthly->write(20, 1, '', $format_general_title);
$worksheet_monthly->write(20, 2, '', $format_general_title);
$worksheet_monthly->write(20, 3, '', $format_general_title);
$worksheet_monthly->write(20, 4, '', $format_general_title);
$worksheet_monthly->write(20, 5, '', $format_general_title);
$worksheet_monthly->write(22, 0, '* Some DRs will be deemed normal care and should use other reporting codes, remove from DR reporting', $format_general_nowrap);
$worksheet_monthly->write(23, 0, '** Days should be tracked as business days', $format_general_nowrap);
$worksheet_monthly->write(25, 0, 'SLA Goals', $format_general_title);
$worksheet_monthly->write(25, 1, '', $format_general_title);
$worksheet_monthly->write(26, 0, 'Import Contacts - New hire', $format_general);
$worksheet_monthly->write(27, 0, 'Import Contacts', $format_general);
$worksheet_monthly->write(28, 0, 'Create mailing list from exiting date', $format_general);
$worksheet_monthly->write(29, 0, 'Update existing contacts - batch', $format_general);
$worksheet_monthly->write(30, 0, 'Missing or incorrect customer info', $format_general);
$worksheet_monthly->write(31, 0, 'Fix duplicate contacts', $format_general);
$worksheet_monthly->write(32, 0, 'Export third party file', $format_general);
$worksheet_monthly->write(33, 0, 'Other', $format_general);
$worksheet_monthly->write(34, 0, 'Avgerage', $format_general_title);
$worksheet_monthly->write(34, 1, '', $format_general_title);
if ($RunFromCron) {
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.metlife.done'));
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.generating.dr.daily.report'));
} else {
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('answernet.er.metlife.generating.dr.daily.report');
}
//SELECT t.id, t.mask, t.created_date ticket_created_date, mc.content, t.is_closed
//FROM ticket t
//INNER JOIN message_content mc on t.first_message_id = mc.message_id
//INNER JOIN message m on t.first_message_id = m.id
//and team_id = 1721
//ORDER BY t.id
$sql = "SELECT t.id, t.mask, ";
$sql .= "t.created_date ticket_created_date, mc.content ";
$sql .= "FROM ticket t ";
$sql .= "INNER JOIN message_content mc on t.first_message_id = mc.message_id ";
$sql .= "WHERE t.is_closed = 0 ";
$sql .= "and t.team_id = 1721 ";
$sql .= "ORDER BY t.id ";
$rs = $db->Execute($sql);
$row = 1;
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$worksheet_open_status->setRow($row, 12);
$custom_fields = DAO_CustomFieldValue::getValuesBySourceIds(ChCustomFieldSource_Ticket::ID, $rs->fields['id']);
//print "<pre>";
//print_r($custom_fields);
//print "</pre>";
// Due Date, SLA, Date Recived, RM Name, RM Employee ID, Topic, Staff, New Hire, Notes/Email Body
// Due Date Column 0
$due_date_int = intval($custom_fields[$rs->fields['id']][1]);
if ($due_date_int) {
$ticket_due_date = date("n/j/y", $due_date_int);
} else {
$ticket_due_date = "";
}
$worksheet_open_status->writeString($row, 0, $ticket_due_date, $format_general);
// SLA Column 1
$worksheet_open_status->write($row, 1, $custom_fields[$rs->fields['id']][5], $format_general);
// Overdue Column 2
if (date("U", $due_date_int) >= time()) {
$worksheet_open_status->write($row, 2, "No", $format_general);
} else {
$worksheet_open_status->write($row, 2, "Yes", $format_general);
}
// Date Recieved Column 3
$ticket_created_date = date("n/j/y g:i:s A", intval($rs->fields['ticket_created_date']));
$worksheet_open_status->writeString($row, 3, $ticket_created_date, $format_general);
// RM Name Column 4
$worksheet_open_status->write($row, 4, $custom_fields[$rs->fields['id']][3], $format_general);
// RM Employee ID Column 5
$worksheet_open_status->write($row, 5, $custom_fields[$rs->fields['id']][2], $format_general);
// Topic / Request Type Column 6
$worksheet_open_status->write($row, 6, $custom_fields[$rs->fields['id']][4], $format_general);
// Staff Column 7
$worksheet_open_status->write($row, 7, $custom_fields[$rs->fields['id']][8], $format_general);
// New Hire Column 8
$worksheet_open_status->write($row, 8, $custom_fields[$rs->fields['id']][6], $format_general);
// Email Body Column 9
$message_content = $rs->fields['content'];
$worksheet_open_status->write($row, 9, trim($message_content), $format_general_nowrap);
// Ticket Mask Column 10
$mask = $rs->fields['mask'];
$worksheet_open_status->write($row, 10, $mask, $format_general);
$row++;
$rs->MoveNext();
}
}
if ($RunFromCron) {
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.metlife.done'));
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.generating.dr.transaction.report'));
} else {
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('answernet.er.metlife.generating.dr.transaction.report');
}
//SELECT t.mask, t.created_date ticket_created_date,
//m.created_date message_created_date, mc.content,
//m.is_outgoing
//FROM message m
//INNER JOIN ticket t ON m.ticket_id = t.id
//INNER JOIN address a ON m.address_id = a.id
//INNER JOIN message_content mc on m.id = mc.message_id
//WHERE t.team_id = 1721
//ORDER BY m.id;
$sql = "SELECT t.id, t.mask, t.created_date ticket_created_date, ";
$sql .= "m.created_date message_created_date, mc.content, m.is_outgoing ";
$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 .= sprintf("WHERE m.created_date > %d AND m.created_date <= %d ", $start_ofday, $end_ofday);
$sql .= "and t.team_id = 1721 ";
$sql .= "ORDER BY t.id ";
$rs = $db->Execute($sql);
$row = 1;
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$worksheet_transaction->setRow($row, 12);
$custom_fields = DAO_CustomFieldValue::getValuesBySourceIds(ChCustomFieldSource_Ticket::ID, $rs->fields['id']);
// Status, Due Date, SLA, SLA Age, Date Recived, RM Name, RM Employee ID, Topic, Staff, New Hire, Notes/Email Body
if (intval($rs->fields['is_outgoing'])) {
$worksheet_transaction->write($row, 0, "Recieved", $format_general);
} else {
$worksheet_transaction->write($row, 0, "Sent", $format_general);
}
// Due Date Column 1
$due_date_int = intval($custom_fields[$rs->fields['id']][1]);
if ($due_date_int) {
$ticket_due_date = date("n/j/y", $due_date_int);
} else {
$ticket_due_date = "";
}
$worksheet_transaction->writeString($row, 1, $ticket_due_date, $format_general);
// SLA Column 2
$worksheet_transaction->write($row, 2, $custom_fields[$rs->fields['id']][5], $format_general);
// Date Recieved Column 3
$ticket_created_date = date("n/j/y g:i:s A", intval($rs->fields['ticket_created_date']));
$worksheet_transaction->writeString($row, 3, $ticket_created_date, $format_general);
// RM Name Column 4
$worksheet_transaction->write($row, 4, $custom_fields[$rs->fields['id']][3], $format_general);
// RM Employee ID Column 5
$worksheet_transaction->write($row, 5, $custom_fields[$rs->fields['id']][2], $format_general);
// Topic / Request Type Column 6
$worksheet_transaction->write($row, 6, $custom_fields[$rs->fields['id']][4], $format_general);
// Staff Column 7
$worksheet_transaction->write($row, 7, $custom_fields[$rs->fields['id']][8], $format_general);
// New Hire Column 8
$worksheet_transaction->write($row, 8, $custom_fields[$rs->fields['id']][6], $format_general);
// Email Body Column 9
$message_content = $rs->fields['content'];
$worksheet_transaction->write($row, 9, trim($message_content), $format_general_nowrap);
// Ticket Mask Column 10
$mask = $rs->fields['mask'];
$worksheet_transaction->write($row, 10, $mask, $format_general);
$row++;
$rs->MoveNext();
}
}
if ($RunFromCron) {
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.metlife.done'));
$logger->info("[Answernet.com] " . $translate->_('answernet.er.metlife.generating.dr.monthly.report'));
} else {
print $translate->_('answernet.er.metlife.metlife.done');
print '<br>';
print $translate->_('answernet.er.metlife.generating.dr.monthly.report');
}
/*
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_general);
$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_general);
// Grand Total
$worksheet_in_count->write(1, 5, 'Grand Total Email', $format_general);
$worksheet_in_count->writeFormula(1, 6, "=SUM(B2,E2)", $format_general);
$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_general);
$worksheet_out_count->writeFormula(1, 1, "=SUM(B3:B".$row_count.")", $format_general);
$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_general);
$worksheet_out_count->write(1, 3, '', $format_out_count_total);
$worksheet_out_count->writeFormula(1, 4, "=SUM(E3:E".$row_count.")", $format_general);
// Grand Total
$worksheet_out_count->write(1, 5, 'Grand Total Email', $format_general);
$worksheet_out_count->writeFormula(1, 6, "=SUM(B2,E2)", $format_general);
*/
$workbook->close();
return $filename;
}
function doDeleteWorkspaceAction()
{
@($workspace = DevblocksPlatform::importGPC($_POST['workspace'], 'string', ''));
$db = DevblocksPlatform::getDatabaseService();
$active_worker = PortSensorApplication::getActiveWorker();
$lists = DAO_Worklist::getWhere(sprintf("%s = %s AND %s = %d", DAO_Worklist::WORKSPACE, $db->qstr($workspace), DAO_Worklist::WORKER_ID, $active_worker->id));
DAO_Worklist::delete(array_keys($lists));
DevblocksPlatform::redirect(new DevblocksHttpResponse(array('home')));
}
static function delete($ids)
{
if (!is_array($ids)) {
$ids = array($ids);
}
$db = DevblocksPlatform::getDatabaseService();
if (empty($ids)) {
return;
}
$ids_list = implode(',', $ids);
$db->Execute(sprintf("DELETE QUICK FROM kb_category WHERE id IN (%s)", $ids_list));
$db->Execute(sprintf("DELETE QUICK FROM kb_article_to_category WHERE kb_category_id IN (%s)", $ids_list));
return true;
}
/**
* @param string $tag_name
* @param boolean $create_if_new
* @return integer id
*/
static function lookupTag($tag_name, $create_if_new = false)
{
if (empty($tag_name)) {
return NULL;
}
$db = DevblocksPlatform::getDatabaseService();
$sql = sprintf("SELECT t.id FROM tag t WHERE t.name = %s", $db->qstr(strtolower($tag_name)));
$id = $db->GetOne($sql);
if (empty($id) && $create_if_new) {
$id = $db->GenID('tag_seq');
$sql = sprintf("INSERT INTO tag (id,name) VALUES (%d,%s)", $id, $db->qstr(strtolower($tag_name)));
$db->Execute($sql);
} else {
if (empty($id)) {
return NULL;
}
}
return self::getTag($id);
}
/**
* Enter description here...
*
* @param array $columns
* @param DevblocksSearchCriteria[] $params
* @param integer $limit
* @param integer $page
* @param string $sortBy
* @param boolean $sortAsc
* @param boolean $withCounts
* @return array
*/
static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true)
{
$db = DevblocksPlatform::getDatabaseService();
$fields = SearchFields_ImportSource::getFields();
// Sanitize
if (!isset($fields[$sortBy])) {
$sortBy = null;
}
list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy);
$start = $page * $limit;
// [JAS]: 1-based
$total = -1;
$select_sql = sprintf("SELECT " . "import_source.id as %s, " . "import_source.name as %s, " . "import_source.path as %s, " . "import_source.type as %s, " . "import_source.is_disabled as %s ", SearchFields_ImportSource::ID, SearchFields_ImportSource::NAME, SearchFields_ImportSource::PATH, SearchFields_ImportSource::TYPE, SearchFields_ImportSource::IS_DISABLED);
$join_sql = "FROM import_source ";
// Custom field joins
list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables($tables, $params, 'import_source.id', $select_sql, $join_sql);
$where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : "");
$sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " ";
$sql = $select_sql . $join_sql . $where_sql . ($has_multiple_values ? 'GROUP BY import_source.id ' : '') . $sort_sql;
// [TODO] Could push the select logic down a level too
if ($limit > 0) {
$rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs */
} else {
$rs = $db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs */
$total = mysql_num_rows($rs);
}
$results = array();
while ($row = mysql_fetch_assoc($rs)) {
$result = array();
foreach ($row as $f => $v) {
$result[$f] = $v;
}
$object_id = intval($row[SearchFields_ImportSource::ID]);
$results[$object_id] = $result;
}
// [JAS]: Count all
if ($withCounts) {
$count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT import_source.id) " : "SELECT COUNT(import_source.id) ") . $join_sql . $where_sql;
$total = $db->GetOne($count_sql);
}
mysql_free_result($rs);
return array($results, $total);
}
/**
* Enter description here...
*
* @param DevblocksSearchCriteria[] $params
* @param integer $limit
* @param integer $page
* @param string $sortBy
* @param boolean $sortAsc
* @param boolean $withCounts
* @return array
*/
static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true)
{
$db = DevblocksPlatform::getDatabaseService();
$fields = SearchFields_WatcherMailFilter::getFields();
// Sanitize
if (!isset($fields[$sortBy])) {
$sortBy = null;
}
list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy);
$start = $page * $limit;
// [JAS]: 1-based [TODO] clean up + document
$select_sql = sprintf("SELECT " . "wmf.id as %s, " . "wmf.pos as %s, " . "wmf.name as %s, " . "wmf.created as %s, " . "wmf.is_disabled as %s, " . "wmf.worker_id as %s ", SearchFields_WatcherMailFilter::ID, SearchFields_WatcherMailFilter::POS, SearchFields_WatcherMailFilter::NAME, SearchFields_WatcherMailFilter::CREATED, SearchFields_WatcherMailFilter::IS_DISABLED, SearchFields_WatcherMailFilter::WORKER_ID);
$join_sql = "FROM watcher_mail_filter wmf ";
// [JAS]: Dynamic table joins
// (isset($tables['o']) ? "LEFT JOIN contact_org o ON (o.id=tt.debit_org_id)" : " ").
// (isset($tables['mc']) ? "INNER JOIN message_content mc ON (mc.message_id=m.id)" : " ").
// Custom field joins
// list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables(
// $tables,
// $params,
// 'wmf.id',
// $select_sql,
// $join_sql
// );
$where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : "");
$sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " ";
$sql = $select_sql . $join_sql . $where_sql . $sort_sql;
$rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$results = array();
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
foreach ($rs->fields as $f => $v) {
$result[$f] = $v;
}
$id = intval($rs->fields[SearchFields_WatcherMailFilter::ID]);
$results[$id] = $result;
$rs->MoveNext();
}
}
// [JAS]: Count all
$total = -1;
if ($withCounts) {
$count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT wmf.id) " : "SELECT COUNT(wmf.id) ") . $join_sql . $where_sql;
$total = $db->GetOne($count_sql);
}
return array($results, $total);
}
protected function _stats($table_name)
{
$db = DevblocksPlatform::getDatabaseService();
$stats = array();
$results = $db->GetArray(sprintf("SELECT storage_extension, storage_profile_id, count(id) as hits, sum(storage_size) as bytes FROM %s GROUP BY storage_extension, storage_profile_id ORDER BY storage_extension", $table_name));
foreach ($results as $result) {
$stats[$result['storage_extension'] . ':' . intval($result['storage_profile_id'])] = array('storage_extension' => $result['storage_extension'], 'storage_profile_id' => $result['storage_profile_id'], 'count' => intval($result['hits']), 'bytes' => intval($result['bytes']));
}
return $stats;
}
function showTabSettingsAction()
{
$tpl = DevblocksPlatform::getTemplateService();
$tpl->assign('path', $this->_TPL_PATH);
$license = CerberusLicense::getInstance();
$tpl->assign('license', $license);
$db = DevblocksPlatform::getDatabaseService();
$rs = $db->Execute("SHOW TABLE STATUS");
$total_db_size = 0;
$total_db_data = 0;
$total_db_indexes = 0;
$total_db_slack = 0;
$total_file_size = 0;
// [TODO] This would likely be helpful to the /debug controller
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$table_name = $rs->fields['Name'];
$table_size_data = intval($rs->fields['Data_length']);
$table_size_indexes = intval($rs->fields['Index_length']);
$table_size_slack = intval($rs->fields['Data_free']);
$total_db_size += $table_size_data + $table_size_indexes;
$total_db_data += $table_size_data;
$total_db_indexes += $table_size_indexes;
$total_db_slack += $table_size_slack;
$rs->MoveNext();
}
}
$sql = "SELECT SUM(file_size) FROM attachment";
$total_file_size = intval($db->GetOne($sql));
$tpl->assign('total_db_size', number_format($total_db_size / 1048576, 2));
$tpl->assign('total_db_data', number_format($total_db_data / 1048576, 2));
$tpl->assign('total_db_indexes', number_format($total_db_indexes / 1048576, 2));
$tpl->assign('total_db_slack', number_format($total_db_slack / 1048576, 2));
$tpl->assign('total_file_size', number_format($total_file_size / 1048576, 2));
$tpl->display('file:' . $this->_TPL_PATH . 'configuration/tabs/settings/index.tpl');
}
/**
* Enter description here...
*
* @param DevblocksSearchCriteria[] $params
* @param integer $limit
* @param integer $page
* @param string $sortBy
* @param boolean $sortAsc
* @param boolean $withCounts
* @return array
*/
static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true)
{
$db = DevblocksPlatform::getDatabaseService();
$fields = SearchFields_CrmOpportunity::getFields();
// Sanitize
if (!isset($fields[$sortBy])) {
unset($sortBy);
}
list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields);
$start = $page * $limit;
// [JAS]: 1-based [TODO] clean up + document
$select_sql = sprintf("SELECT " . "o.id as %s, " . "o.name as %s, " . "o.amount as %s, " . "org.id as %s, " . "org.name as %s, " . "o.primary_email_id as %s, " . "a.email as %s, " . "o.created_date as %s, " . "o.updated_date as %s, " . "o.closed_date as %s, " . "o.is_closed as %s, " . "o.is_won as %s, " . "o.worker_id as %s ", SearchFields_CrmOpportunity::ID, SearchFields_CrmOpportunity::NAME, SearchFields_CrmOpportunity::AMOUNT, SearchFields_CrmOpportunity::ORG_ID, SearchFields_CrmOpportunity::ORG_NAME, SearchFields_CrmOpportunity::PRIMARY_EMAIL_ID, SearchFields_CrmOpportunity::EMAIL_ADDRESS, SearchFields_CrmOpportunity::CREATED_DATE, SearchFields_CrmOpportunity::UPDATED_DATE, SearchFields_CrmOpportunity::CLOSED_DATE, SearchFields_CrmOpportunity::IS_CLOSED, SearchFields_CrmOpportunity::IS_WON, SearchFields_CrmOpportunity::WORKER_ID);
$join_sql = "FROM crm_opportunity o " . "LEFT JOIN address a ON (a.id = o.primary_email_id) " . "LEFT JOIN contact_org org ON (org.id = a.contact_org_id) ";
// [JAS]: Dynamic table joins
// (isset($tables['m']) ? "INNER JOIN requester r ON (r.ticket_id=t.id)" : " ").
// Custom field joins
list($select_sql, $join_sql) = self::_appendSelectJoinSqlForCustomFieldTables($tables, 'o.id', $select_sql, $join_sql);
$where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : "");
$sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " ";
$group_sql = "GROUP BY o.id ";
$sql = $select_sql . $join_sql . $where_sql . $group_sql . $sort_sql;
$rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg());
/* @var $rs ADORecordSet */
$results = array();
if (is_a($rs, 'ADORecordSet')) {
while (!$rs->EOF) {
$result = array();
foreach ($rs->fields as $f => $v) {
$result[$f] = $v;
}
$id = intval($rs->fields[SearchFields_CrmOpportunity::ID]);
$results[$id] = $result;
$rs->MoveNext();
}
}
// [JAS]: Count all
$total = -1;
if ($withCounts) {
$count_sql = "SELECT COUNT(DISTINCT o.id) " . $join_sql . $where_sql;
$total = $db->GetOne($count_sql);
}
return array($results, $total);
}
