function getById($jobid)
{
// do Bacula ACLs
Zend_Loader::loadClass('Job');
$table = new Job();
if (!$table->isJobIdExists($jobid)) {
return FALSE;
}
$select = new Zend_Db_Select($this->db);
switch ($this->db_adapter) {
case 'PDO_SQLITE':
// bug http://framework.zend.com/issues/browse/ZF-884
$select->distinct();
$select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
break;
default:
// mysql, postgresql
$select->distinct();
$select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
}
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
return $stmt->fetchAll();
}
/**
* init the select
*/
private function _initSelector()
{
if (!$this->_tableName) {
throw new Exception("if you wanna use model, please specify the table name first");
}
$this->_selector->reset();
$this->_selector->from($this->_tableName);
}
/**
* Retrieve cms page collection array
*
* @param unknown_type $storeId
* @return Zend_Db_Statement_Interface
*/
public function getCollection($storeId, $includeEEHierarchy = false)
{
$this->_select = $this->_getWriteAdapter()->select();
$this->_select->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName(), 'DATE(main_table.update_time) as updated_at'))->join(array('store_table' => $this->getTable('cms/page_store')), 'main_table.page_id=store_table.page_id', array())->where('main_table.is_active=1')->where('store_table.store_id IN(?)', array(0, $storeId));
if ($includeEEHierarchy) {
$this->_addHierarchy();
} else {
$this->_select->columns('main_table.identifier as url');
}
// die((string)$this->_select);
$query = $this->_getWriteAdapter()->query($this->_select);
return $query;
}
/**
* A select used by subclasses to add fields to the select.
*
* @param string $login_name
* @param int $organization
* @return \Zend_Db_Select
*/
protected function getUserSelect($login_name, $organization)
{
// 'user_group' => 'gsf_id_primary_group', 'user_logout' => 'gsf_logout_on_survey',
$select = new \Zend_Db_Select($this->db);
$select->from('gems__user_logins', array('user_login_id' => 'gul_id_user', 'user_active' => 'gul_can_login'))->join('gems__respondent2org', 'gul_login = gr2o_patient_nr AND gul_id_organization = gr2o_id_organization', array('user_login' => 'gr2o_patient_nr', 'user_base_org_id' => 'gr2o_id_organization'))->join('gems__respondents', 'gr2o_id_user = grs_id_user', array('user_id' => 'grs_id_user', 'user_email' => 'grs_email', 'user_first_name' => 'grs_first_name', 'user_surname_prefix' => 'grs_surname_prefix', 'user_last_name' => 'grs_last_name', 'user_gender' => 'grs_gender', 'user_locale' => 'grs_iso_lang', 'user_birthday' => 'grs_birthday'))->join('gems__organizations', 'gr2o_id_organization = gor_id_organization', array('user_group' => 'gor_respondent_group'))->join('gems__groups', 'gor_respondent_group = ggp_id_group', array('user_role' => 'ggp_role', 'user_allowed_ip_ranges' => 'ggp_allowed_ip_ranges'))->joinLeft('gems__user_passwords', 'gul_id_user = gup_id_user', array('user_password_reset' => 'gup_reset_required', 'user_resetkey_valid' => new \Zend_Db_Expr('CASE WHEN DATE_ADD(gup_reset_requested, INTERVAL ' . $this->hoursResetKeyIsValid . ' HOUR) >= CURRENT_TIMESTAMP THEN 1 ELSE 0 END'), 'user_password_last_changed' => 'gup_last_pwd_change'))->joinLeft('gems__reception_codes', 'gr2o_reception_code = grc_id_reception_code', array())->where('ggp_group_active = 1')->where('grc_success = 1')->where('gul_can_login = 1')->where('gul_login = ?')->where('gul_id_organization = ?')->limit(1);
return $select;
}
protected function _addContentEventProfile()
{
$db = $this->getDb();
$select = new Zend_Db_Select($db);
// Check if it's already been placed
$select = new Zend_Db_Select($db);
$hasWidget = $select->from('engine4_core_pages', new Zend_Db_Expr('TRUE'))->where('name = ?', 'event_profile_index')->limit(1)->query()->fetchColumn();
// Add it
if (empty($hasWidget)) {
$db->insert('engine4_core_pages', array('name' => 'event_profile_index', 'displayname' => 'Event Profile', 'title' => 'Event Profile', 'description' => 'This is the profile for an event.', 'custom' => 0));
$page_id = $db->lastInsertId('engine4_core_pages');
// containers
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'main', 'parent_content_id' => null, 'order' => 1, 'params' => ''));
$container_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'middle', 'parent_content_id' => $container_id, 'order' => 3, 'params' => ''));
$middle_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'left', 'parent_content_id' => $container_id, 'order' => 1, 'params' => ''));
$left_id = $db->lastInsertId('engine4_core_content');
// middle column
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.container-tabs', 'parent_content_id' => $middle_id, 'order' => 2, 'params' => '{"max":"6"}'));
$tab_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-status', 'parent_content_id' => $middle_id, 'order' => 1, 'params' => ''));
// left column
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photo', 'parent_content_id' => $left_id, 'order' => 1, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-options', 'parent_content_id' => $left_id, 'order' => 2, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-info', 'parent_content_id' => $left_id, 'order' => 3, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-rsvp', 'parent_content_id' => $left_id, 'order' => 4, 'params' => ''));
// tabs
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'activity.feed', 'parent_content_id' => $tab_id, 'order' => 1, 'params' => '{"title":"Updates"}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-members', 'parent_content_id' => $tab_id, 'order' => 2, 'params' => '{"title":"Guests","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photos', 'parent_content_id' => $tab_id, 'order' => 3, 'params' => '{"title":"Photos","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-discussions', 'parent_content_id' => $tab_id, 'order' => 4, 'params' => '{"title":"Discussions","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.profile-links', 'parent_content_id' => $tab_id, 'order' => 5, 'params' => '{"title":"Links","titleCount":true}'));
}
}
public function getTotal($type, $start = null, $end = null)
{
$select = new Zend_Db_Select($this->getAdapter());
$select->from($this->info('name'), 'SUM(value) as sum')->where('type = ?', $type);
// Can pass "today" into start
switch ($start) {
case 'day':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j") + 1, gmdate("Y"));
break;
case 'week':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1, gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1 + 7, gmdate("Y"));
break;
case 'month':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n") + 1, gmdate("j"), gmdate("Y"));
break;
case 'year':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y") + 1);
break;
}
if (null !== $start) {
$select->where('date >= ?', gmdate('Y-m-d', $start));
}
if (null !== $end) {
$select->where('date < ?', gmdate('Y-m-d', $end));
}
$data = $select->query()->fetch();
if (!isset($data['sum'])) {
return 0;
}
return $data['sum'];
}
function onInstall()
{
// member homepage
$db = $this->getDb();
$select = new Zend_Db_Select($db);
$select->from('engine4_core_pages')->where('name = ?', 'user_index_home')->limit(1);
$page_id = $select->query()->fetchObject()->page_id;
// Check if it's already been placed
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'widget')->where('name = ?', 'social-connect.call-popup-invite');
$info = $select->query()->fetch();
if (empty($info)) {
// container_id (will always be there)
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'container')->limit(1);
$container_id = $select->query()->fetchObject()->content_id;
// middle_id (will always be there)
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('parent_content_id = ?', $container_id)->where('type = ?', 'container')->where('name = ?', 'middle')->limit(1);
$middle_id = $select->query()->fetchObject()->content_id;
// tab on profile
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'social-connect.call-popup-invite', 'parent_content_id' => $middle_id, 'params' => '', 'order' => 999));
}
parent::onInstall();
}
public function joinComments()
{
$select = new Zend_Db_Select($connection = Mage::getSingleton('core/resource')->getConnection('read'));
$select->from(Mage::getSingleton('core/resource')->getTableName('mdlblog/comment'), array('post_id', 'comment_count' => new Zend_Db_Expr('COUNT(IF(status = 2, post_id, NULL))')))->group('post_id');
$this->getSelect()->joinLeft(array('comments_select' => $select), 'main_table.post_id = comments_select.post_id', 'comment_count');
return $this;
}
/**
* Saving information about customer
*
* @param Mage_Log_Model_Visitor $visitor
*
* @return Mage_Log_Model_Resource_Visitor
*/
protected function _saveCustomerInfo($visitor)
{
$adapter = $this->_getWriteAdapter();
if ($visitor->getDoCustomerLogout() && ($logId = $visitor->getCustomerLogId())) {
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Zend_Db_Select($connection);
$select->from($resource->getTableName('log/customer'));
$select->reset(Zend_Db_Select::COLUMNS);
$select->columns('login_at');
$select->where('log_id = ?', $logId);
$loginAt = $connection->fetchOne($select);
if (!$loginAt) {
return parent::_saveCustomerInfo($visitor);
}
$data = new Varien_Object(array('login_at' => $loginAt, 'logout_at' => Mage::getSingleton('core/date')->gmtDate(), 'store_id' => (int) Mage::app()->getStore()->getId()));
$bind = $this->_prepareDataForTable($data, $this->getTable('log/customer'));
$condition = array('log_id = ?' => (int) $logId);
$adapter->update($this->getTable('log/customer'), $bind, $condition);
$visitor->setDoCustomerLogout(false);
$visitor->setCustomerId(null);
$visitor->setCustomerLogId(null);
} else {
return parent::_saveCustomerInfo($visitor);
}
return $this;
}
public function indexAction()
{
// Users
$userTable = Engine_Api::_()->getItemTable('user');
$select = new Zend_Db_Select($userTable->getAdapter());
$select->from($userTable->info('name'), 'COUNT(user_id) as count');
$data = $select->query()->fetch();
$this->view->userCount = (int) $data['count'];
// Reports
$reportTable = Engine_Api::_()->getDbtable('reports', 'core');
$select = new Zend_Db_Select($reportTable->getAdapter());
$select->from($reportTable->info('name'), 'COUNT(report_id) as count')->where('`read` = ?', 0);
$data = $select->query()->fetch();
$this->view->reportCount = (int) $data['count'];
// Plugins
$moduleTable = Engine_Api::_()->getDbtable('modules', 'core');
$select = new Zend_Db_Select($moduleTable->getAdapter());
$select->from($moduleTable->info('name'), 'COUNT(TRUE) as count')->where('type = ?', 'extra');
$data = $select->query()->fetch();
$this->view->pluginCount = (int) $data['count'];
// Notifications
// Hook-based
$event = Engine_Hooks_Dispatcher::_()->callEvent('getAdminNotifications');
$this->view->notifications = $event->getResponses();
// Database-based
$select = Engine_Api::_()->getDbtable('log', 'core')->select()->where('domain = ?', 'admin')->order('timestamp DESC');
$this->view->paginator = $paginator = Zend_Paginator::factory($select);
$paginator->setItemCountPerPage(4);
}
/**
* Replaces calc_invoice_paid()
*
* @param int $invoice
*/
public function getPaidAmountForInvoice($invoice)
{
$select = new Zend_Db_Select($this->getAdapter());
$select->from($this->_name, array('amount' => new Zend_Db_Expr("COALESCE(SUM(ac_amount), 0)")));
$select->where('ac_inv_id=?', $invoice);
return $this->getAdapter()->fetchOne($select);
}
/**
* Returns the select object to be used for validation.
* @return Zend_Db_Select $select
*/
public function getSelect()
{
if (null === $this->_select) {
$db = $this->getAdapter();
// Zend_Debug::dump($this->_excludeId ); // die(0);
$select = new Zend_Db_Select($db);
$select->from($this->_table, array($this->_field), $this->_schema);
if ($db->supportsParameters('named')) {
$select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
// named
} else {
$select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
// positional
}
$select->limit(1);
if (!empty($this->_meetingId)) {
$select->where($db->quoteInto('`meeting_id` = ?', $this->_meetingId));
}
if (!empty($this->_excludeId)) {
$select->where($db->quoteInto('`id` != ?', $this->_excludeId));
}
$this->_select = $select;
}
return $this->_select;
}
/**
* Run query and returns matches, or null if no matches are found.
*
* @param String $value
* @return Array when matches are found.
*/
protected function _query($value)
{
/**
* Check for an adapter being defined. if not, fetch the default adapter.
*/
if ($this->_adapter === null) {
$this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
}
/**
* Build select object
*/
$select = new Zend_Db_Select($this->_adapter);
$select->from($this->_table, array($this->_field), $this->_schema)->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
if ($this->_exclude !== null) {
if (is_array($this->_exclude)) {
$select->where($this->_adapter->quoteIdentifier($this->_exclude['field']) . ' != ?', $this->_exclude['value']);
} else {
$select->where($this->_exclude);
}
}
$select->limit(1);
/**
* Run query
*/
$result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
return $result;
}
/**
* Lists all tests which are available in system
* @return dataset
*/
public function getAll()
{
$obj = new Zend_Db_Select(Zend_Db_Table::getDefaultAdapter());
$select = $obj->from('tests as t', array('id', 'name'))->order('t.name');
$result = $select->query()->fetchAll();
return $result;
}
public function onStatistics($event)
{
$table = Engine_Api::_()->getItemTable('ynfeedback_idea');
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), 'COUNT(*) AS count');
$event->addResponse($select->query()->fetchColumn(0), 'feedback');
}
public function onStatistics($event)
{
$table = Engine_Api::_()->getDbTable('photos', 'album');
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), 'COUNT(*) AS count');
$event->addResponse($select->query()->fetchColumn(0), 'photo');
}
public function getPostIndex()
{
$table = $this->getTable();
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), new Zend_Db_Expr('COUNT(post_id) as count'))->where('topic_id = ?', $this->topic_id)->where('post_id < ?', $this->getIdentity())->order('post_id ASC');
$data = $select->query()->fetch();
return (int) $data['count'];
}
function getVesion()
{
$select = new Zend_Db_Select($this->db);
$select->from('Version', 'VersionId');
$select->limit(1);
$res = $this->db->fetchOne($select);
return $res;
}
public function getAllOutboxCountSelect($users)
{
$rName = Engine_Api::_()->getDbtable('recipients', 'messages')->info('name');
$cName = $this->info('name');
$select = new Zend_Db_Select($this->getAdapter());
$select->from($cName, new Zend_Db_Expr('COUNT(1) AS zend_paginator_row_count'))->joinRight($rName, "`{$rName}`.`conversation_id` = `{$cName}`.`conversation_id`", null)->where("`{$rName}`.`user_id` IN (?)", $users)->where("`{$rName}`.`outbox_deleted` = ?", 0);
return $select;
}
protected function _editProfilePage()
{
// get page profile member
$db = $this->getDb();
$select = new Zend_Db_Select($db);
$select->from('engine4_core_pages')->where('name = ?', 'user_profile_index')->limit(1);
$info = $select->query()->fetch();
$db->query("DELETE FROM `engine4_core_content` WHERE `name` = 'user.profile-photo' AND `page_id` = " . $info['page_id']);
$db->query("DELETE FROM `engine4_core_content` WHERE `name` = 'user.profile-fields' AND `page_id` = " . $info['page_id']);
$page_id = $info['page_id'];
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'container')->where('name = ?', 'top')->limit(1);
$info = $select->query()->fetch();
if (!$info) {
//insert top container
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'top', 'parent_content_id' => null, 'order' => 1, 'params' => ''));
$top_id = $db->lastInsertId('engine4_core_content');
// Insert top-middle
$db->insert('engine4_core_content', array('type' => 'container', 'name' => 'middle', 'page_id' => $page_id, 'parent_content_id' => $top_id));
$top_middle_id = $db->lastInsertId('engine4_core_content');
}
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('type = ?', 'container')->where('page_id = ?', $page_id)->where('name = ?', 'left')->limit(1);
$info = $select->query()->fetch();
$main_left_id = $info['content_id'];
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('name = ?', 'ynmember.general-rating')->limit(1);
$info = $select->query()->fetch();
if (!$info) {
//general rating
$db->insert('engine4_core_content', array('type' => 'widget', 'name' => 'ynmember.general-rating', 'page_id' => $page_id, 'parent_content_id' => $main_left_id));
}
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('name = ?', 'ynmember.profile-statistics')->limit(1);
$info = $select->query()->fetch();
if (!$info) {
//profile statistics
$db->insert('engine4_core_content', array('type' => 'widget', 'name' => 'ynmember.profile-statistics', 'page_id' => $page_id, 'parent_content_id' => $main_left_id));
}
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('name = ?', 'ynmember.profile-cover')->limit(1);
$info = $select->query()->fetch();
if (!$info) {
//Profile cover
$db->insert('engine4_core_content', array('type' => 'widget', 'name' => 'ynmember.profile-cover', 'page_id' => $page_id, 'parent_content_id' => $top_middle_id, 'order' => 3));
}
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('name = ?', 'core.container-tabs')->limit(1);
$info = $select->query()->fetch();
$content_tab_id = $info['content_id'];
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('name = ?', 'ynmember.profile-fields')->limit(1);
$info = $select->query()->fetch();
if (!$info) {
//Profile fields
$db->insert('engine4_core_content', array('type' => 'widget', 'name' => 'ynmember.profile-fields', 'page_id' => $page_id, 'parent_content_id' => $content_tab_id, 'params' => '{"title":"Info"}'));
}
}
protected function _runCustomQueries()
{
$db = $this->getDb();
// Check for levels column
try {
$cols = $db->describeTable('engine4_core_pages');
if (!isset($cols['levels'])) {
$db->query('ALTER TABLE `engine4_core_pages` ' . 'ADD COLUMN `levels` text default NULL AFTER `layout`');
} else {
if ($cols['levels']['DEFAULT'] != 'NULL') {
$db->query('ALTER TABLE `engine4_core_pages` ' . 'CHANGE COLUMN `levels` `levels` text default NULL AFTER `layout`');
}
}
} catch (Exception $e) {
throw $e;
}
// Get array of levels
$select = new Zend_Db_Select($db);
$levels = $select->from('engine4_authorization_levels', 'level_id')->query()->fetchAll(Zend_Db::FETCH_COLUMN);
$levels = Zend_Json::encode($levels);
// assign levels json to any pages missing it
try {
$db->update('engine4_core_pages', array('levels' => $levels), array('custom = ?' => 1, 'levels = \'\' OR levels = \'[]\' OR levels IS NULL'));
} catch (Exception $e) {
}
// Remove public column for adcampaigns
$cols = $db->describeTable('engine4_core_adcampaigns');
if (isset($cols['public'])) {
$publicLevelId = $db->select()->from('engine4_authorization_levels', 'level_id')->where('flag = ?', 'public')->limit(1)->query()->fetchColumn();
$publicAdCampaigns = $db->select()->from('engine4_core_adcampaigns')->where('public = ?', 1)->query()->fetchAll();
if ($publicLevelId && $publicAdCampaigns) {
foreach ($publicAdCampaigns as $publicAdCampaign) {
if (empty($publicAdCampaign['level']) || !($levels = Zend_Json::decode($publicAdCampaign['level'])) || !is_array($levels)) {
$levels = array();
}
if (!in_array($publicLevelId, $levels)) {
$levels[] = $publicLevelId;
$db->update('engine4_core_adcampaigns', array('level' => Zend_Json::encode($levels)), array('adcampaign_id = ?' => $publicAdCampaign['adcampaign_id']));
}
}
}
$db->query('ALTER TABLE `engine4_core_adcampaigns` DROP COLUMN `public`');
}
// Update all ip address to ipv6
$this->_convertToIPv6($db, 'engine4_core_nodes', 'ip', false);
$this->_convertToIPv6($db, 'engine4_core_bannedips', 'start', false);
$this->_convertToIPv6($db, 'engine4_core_bannedips', 'stop', false);
$this->_addContactPage();
$this->_addPrivacyPage();
$this->_addTermsOfServicePage();
if (method_exists($this, '_addGenericPage')) {
$this->_addGenericPage('core_error_requireuser', 'Sign-in Required', 'Sign-in Required Page', '');
$this->_addGenericPage('core_search_index', 'Searc', 'Search Page', '');
} else {
$this->_error('Missing _addGenericPage method');
}
}
public function getOutboxCountSelect(User_Model_User $user)
{
$rName = Engine_Api::_()->getDbtable('recipients', 'messages')->info('name');
$cName = $this->info('name');
$enabledModules = Engine_Api::_()->getDbtable('modules', 'core')->getEnabledModuleNames();
$select = new Zend_Db_Select($this->getAdapter());
$select->from($cName, new Zend_Db_Expr('COUNT(1) AS zend_paginator_row_count'))->joinRight($rName, "`{$rName}`.`conversation_id` = `{$cName}`.`conversation_id`", null)->where("`{$rName}`.`user_id` = ?", $user->getIdentity())->where("`{$cName}`.`resource_type` IS NULL or `{$cName}`.`resource_type` ='' or `{$cName}`.`resource_type` IN (?)", $enabledModules)->where("`{$rName}`.`outbox_deleted` = ?", 0);
return $select;
}
/**
* Get the total paid amount by this customer.
* Replaces: calc_customer_paid()
* Enter description here ...
*/
public function getPaidAmount()
{
$tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
$select = new Zend_Db_Select($this->_db);
$select->from($tbl_prefix . "payment", array('amount' => new Zend_Db_Expr("COALESCE(SUM(" . $tbl_prefix . "payment.ac_amount), 0)")));
$select->joinInner($tbl_prefix . "invoices", $tbl_prefix . "payment.ac_inv_id=" . $tbl_prefix . "invoices.id", NULL);
$select->where($tbl_prefix . "invoices.customer_id=?", $this->_id);
return $this->_db->fetchOne($select);
}
public function indexAction()
{
// Don't render this if not authorized
$viewer = Engine_Api::_()->user()->getViewer();
if (!Engine_Api::_()->core()->hasSubject()) {
return $this->setNoRender();
}
// Don't render this if friendships are disabled
if (!Engine_Api::_()->getApi('settings', 'core')->user_friends_eligible) {
return $this->setNoRender();
}
// Get subject and check auth
$subject = Engine_Api::_()->core()->getSubject('user');
//if( !$subject->authorization()->isAllowed($viewer, 'view') ) {
// return $this->setNoRender();
//}
// If no viewer or viewer==subject, don't display
if (!$viewer->getIdentity() || $viewer->isSelf($subject)) {
return $this->setNoRender();
}
// Diff friends
$friendsTable = Engine_Api::_()->getDbtable('membership', 'user');
$friendsName = $friendsTable->info('name');
// Mututal friends/followers mode
// $col1 = 'user_id';
// $col2 = 'resource_id';
// Mututal friends/following mode
$col1 = 'resource_id';
$col2 = 'user_id';
$select = new Zend_Db_Select($friendsTable->getAdapter());
$select->from($friendsName, $col1)->join($friendsName, "`{$friendsName}`.`{$col1}`=`{$friendsName}_2`.{$col1}", null)->where("`{$friendsName}`.{$col2} = ?", $viewer->getIdentity())->where("`{$friendsName}_2`.{$col2} = ?", $subject->getIdentity())->where("`{$friendsName}`.active = ?", 1)->where("`{$friendsName}_2`.active = ?", 1);
// Now get all common friends
$uids = array();
foreach ($select->query()->fetchAll() as $data) {
$uids[] = $data[$col1];
}
// Do not render if nothing to show
if (count($uids) <= 0) {
return $this->setNoRender();
}
// Get paginator
$usersTable = Engine_Api::_()->getItemTable('user');
$select = $usersTable->select()->where('user_id IN(?)', $uids);
$this->view->paginator = $paginator = Zend_Paginator::factory($select);
// Set item count per page and current page number
$paginator->setItemCountPerPage($this->_getParam('itemCountPerPage', 6));
$paginator->setCurrentPageNumber($this->_getParam('page', 1));
// Do not render if nothing to show
if ($paginator->getTotalItemCount() <= 0) {
return $this->setNoRender();
}
// Add count to title if configured
if ($this->_getParam('titleCount', false) && $paginator->getTotalItemCount() > 0) {
$this->_childCount = $paginator->getTotalItemCount();
}
}
public function indexAction()
{
$viewer = Engine_Api::_()->user()->getViewer();
$params = $this->_getAllParams();
if (!empty($params['itemCountPerPage'])) {
$limit = $params['itemCountPerPage'];
} else {
$limit = 3;
}
// Don't render this if friendships are disabled
if (!Engine_Api::_()->getApi('settings', 'core')->user_friends_eligible) {
return $this->setNoRender();
}
if (!$viewer->getIdentity()) {
return $this->setNoRender();
}
$tableUser = Engine_Api::_()->getItemTable('user');
$select = $tableUser->select()->where('user_id <> ?', $viewer->getIdentity())->where('enabled = 1')->where('verified = 1')->where('approved = 1')->order(new Zend_Db_Expr('rand()'));
$list_users = $tableUser->fetchAll($select);
$list_show_users = array();
foreach ($list_users as $subject) {
if (count($list_show_users) >= $limit) {
break;
}
// Diff friends
$friendsTable = Engine_Api::_()->getDbtable('membership', 'user');
$friendsName = $friendsTable->info('name');
// Mututal friends/following mode
$col1 = 'resource_id';
$col2 = 'user_id';
$select = new Zend_Db_Select($friendsTable->getAdapter());
$select->from($friendsName, $col1)->join($friendsName, "`{$friendsName}`.`{$col1}`=`{$friendsName}_2`.{$col1}", null)->where("`{$friendsName}`.{$col2} = ?", $viewer->getIdentity())->where("`{$friendsName}_2`.{$col2} = ?", $subject->getIdentity())->where("`{$friendsName}`.active = ?", 1)->where("`{$friendsName}_2`.active = ?", 1);
// Now get all common friends
$uids = array();
foreach ($select->query()->fetchAll() as $data) {
$uids[] = $data[$col1];
}
// Do not render if nothing to show
if (count($uids) > 0) {
if (!$subject->membership()->isMember($viewer)) {
$list_show_users[] = array('user_id' => $subject->getIdentity(), 'number_mutual' => count($uids));
}
}
}
if (count($list_show_users) <= 0) {
foreach ($list_users as $subject) {
if (count($list_show_users) >= $limit) {
break;
}
if (!$subject->membership()->isMember($viewer)) {
$list_show_users[] = array('user_id' => $subject->getIdentity());
}
}
}
$this->view->list_show_users = $list_show_users;
}
public function mailAction()
{
$this->view->form = $form = new Core_Form_Admin_Message_Mail();
// let the level_ids be specified in GET string
$level_ids = $this->_getParam('level_id', false);
if (is_array($level_ids)) {
$form->target->setValue($level_ids);
}
if (!$this->getRequest()->isPost()) {
return;
}
if (!$form->isValid($this->getRequest()->getPost())) {
return;
}
$values = $form->getValues();
$table = Engine_Api::_()->getItemTable('user');
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), 'email')->where('enabled = ?', true);
// Do not email disabled members
$level_ids = $this->_getParam('target');
if (is_array($level_ids) && !empty($level_ids)) {
$select->where('level_id IN (?)', $level_ids);
}
$emails = array();
foreach ($select->query()->fetchAll(Zend_Db::FETCH_COLUMN, 0) as $email) {
$emails[] = $email;
}
// temporarily enable queueing if requested
$temporary_queueing = Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing;
if (isset($values['queueing']) && $values['queueing']) {
Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing = 1;
}
$mailApi = Engine_Api::_()->getApi('mail', 'core');
$mail = $mailApi->create();
$mail->setFrom($values['from_address'], $values['from_name'])->setSubject($values['subject'])->setBodyHtml(nl2br($values['body']));
if (!empty($values['body_text'])) {
$mail->setBodyText($values['body_text']);
} else {
$mail->setBodyText(strip_tags($values['body']));
}
foreach ($emails as $email) {
$mail->addTo($email);
}
$mailApi->send($mail);
$mailComplete = $mailApi->create();
$mailComplete->addTo(Engine_Api::_()->user()->getViewer()->email)->setFrom($values['from_address'], $values['from_name'])->setSubject('Mailing Complete: ' . $values['subject'])->setBodyHtml('Your email blast to your members has completed. Please note that, while the emails have been
sent to the recipients\' mail server, there may be a delay in them actually receiving the email due to
spam filtering systems, incoming mail throttling features, and other systems beyond SocialEngine\'s control.');
$mailApi->send($mailComplete);
// emails have been queued (or sent); re-set queueing value to original if changed
if (isset($values['queueing']) && $values['queueing']) {
Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing = $temporary_queueing;
}
$this->view->form = null;
$this->view->status = true;
}
public function getPendingComments($db)
{
$select = new Zend_Db_Select($db);
$cols = array('comment_id', 'page_id', 'comment', 'comment_date', 'name', 'email', 'status');
$select->from(array('c' => 'comments_page'), $cols)->order('comment_date DESC')->where("status = 1")->joinLeft(array('p' => 'pages'), 'p.page_id = c.page_id', array('url_slug', 'title'));
//->where("p.page_id =?", 'c.page_id' );
$stmt = $db->query($select);
$results = $stmt->fetchAll();
return $results;
}
function getClientId($client_name)
{
$select = new Zend_Db_Select($this->_db);
$select->from('Client');
$select->where("Name = ?", $client_name);
$select->limit(1);
$stmt = $select->query();
$res = $stmt->fetch();
return $res['clientid'];
}
public function getAllDraftPosts($db)
{
$select = new Zend_Db_Select($db);
$cols = array('post_id', 'user_id', 'date_created', 'heading', 'image', 'description', 'is_comment', 'tags', 'is_in_draft', 'date_published');
$select->from(array('p' => 'posts'), $cols)->where("is_in_draft = 1")->order('date_published DESC');
//->joinLeft(array('u' => 'users'),'u.user_id = p.user_id',array('user_name','role_id'));//->where("p.page_id =?", 'c.page_id' );
$stmt = $db->query($select);
$results = $stmt->fetchAll();
return $results;
}
/**
* Get the data for the object by the given id
*
* @param integer $id
*/
public function getById($id)
{
$select = new Zend_Db_Select($this->db->getResource());
$data = $select->from(self::TABLE_NAME)->where('id = ?', $id)->query()->fetch();
if ($data && $data["id"] > 0) {
$this->assignVariablesToModel($data);
} else {
throw new Exception("Answer with ID '{$id}' doesn't exists");
}
}
