/**
* A method to prune the data_summary_ad_zone_assoc table
* Prune all entries where the ad_id is for a banner in a High Priority Campaign where:
* The campaign does not have any booked lifetime target values AND the caMpaign has an end date AND the end date has been passed AND the campaign is not active.
*
* @return integer : number of records deleted
*/
function _pruneDataSummaryAdZoneAssocInactiveExpired()
{
$tblAssoc = $this->_getTablename('data_summary_ad_zone_assoc');
$tblBanners = $this->_getTablename('banners');
$tblCampaigns = $this->_getTablename('campaigns');
$queryEnd = '' . ' LEFT JOIN ' . $tblCampaigns . ' AS c ON b.campaignid = c.campaignid' . ' WHERE ( ( c.status <> ' . OA_ENTITY_STATUS_RUNNING . ') AND (c.priority > 0 )) ' . ' AND' . '(' . ' (' . ' (c.target_impression < 1)' . ' AND' . ' (c.target_click < 1)' . ' AND' . ' (c.target_conversion < 1)' . ' )' . ' AND' . ' (c.expire_time < ' . $this->oDbh->quote(OA::getNowUTC('Y-m-d H:i:s')) . ')' . ')';
if ($this->oDbh->dbsyntax == 'pgsql') {
$query = 'DELETE FROM ' . $tblAssoc . ' WHERE data_summary_ad_zone_assoc_id IN (' . ' SELECT dsaza.data_summary_ad_zone_assoc_id FROM' . ' ' . $tblAssoc . ' AS dsaza' . ' LEFT JOIN ' . $tblBanners . ' AS b ON dsaza.ad_id = b.bannerid' . $queryEnd . ')';
} else {
$query = 'DELETE FROM ' . $tblAssoc . ' USING ' . $tblAssoc . ' LEFT JOIN ' . $tblBanners . ' AS b ON ' . $tblAssoc . '.ad_id = b.bannerid' . $queryEnd;
}
return $this->oDbh->exec($query);
}
/**
* This function recreates the data_summary_ad_hourly table rolls-up hourly stats to daily totals
* The roll-up is done in accordance to the user's (currently) selected timezone
*
* @param PEAR_Date $oDate The date before which hourly stats should be rolled up
*/
function _rollUpHourlyStatsToDaily($oDate)
{
$sDate = $oDate->format('%Y-%m-%d 00:00:00');
$updated = OA::getNowUTC('Y-m-d h:i:s');
OA::debug("Beginning stats rollup for pre-{$sDate}", PEAR_LOG_INFO);
// First create a temporary table with ad_id/offset pairs, this can then be joined in a (compled) INSERT INTO ... SELECT FROM statement
$aTimezones = OX_Admin_Timezones::availableTimezones(false);
$aAdminPreferences = OA_Preferences::loadAdminAccountPreferences(true);
$aAdminTzOffset = $this->_getSqlOffsetFromString($aTimezones[$aAdminPreferences['timezone']]);
// CREATE a timezone string => offset table (since the format we use in PHP is incompatible with the format used by MySQL)
$this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}tmp_tz_offset");
$this->oDbh->exec("CREATE TABLE `{$this->prefix}tmp_tz_offset`\n (`timezone` varchar(32) NOT NULL PRIMARY KEY, `offset` char(6) NOT NULL DEFAULT '+00:00') ENGINE={$this->conf['table']['type']}");
foreach ($aTimezones as $tzString => $tzData) {
$tzData = $this->_getSqlOffsetFromString($tzData);
$this->oDbh->exec("INSERT INTO {$this->prefix}tmp_tz_offset (timezone, offset) VALUES('{$tzString}', '{$tzData}')");
}
OA::debug("Created timezone/offset mapping table", PEAR_LOG_DEBUG);
// CREATE an ad_id => offset table
$this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}tmp_ad_offset");
$this->oDbh->exec("CREATE TABLE `{$this->prefix}tmp_ad_offset`\n (`ad_id` int(11) NOT NULL PRIMARY KEY, `offset` char(6) NOT NULL DEFAULT '+00:00') ENGINE={$this->conf['table']['type']}");
$this->oDbh->exec("INSERT INTO {$this->prefix}tmp_ad_offset SELECT bannerid, '{$aAdminTzOffset}' FROM {$this->prefix}banners AS b");
$this->oDbh->exec("UPDATE\n {$this->prefix}tmp_ad_offset AS ao,\n {$this->prefix}banners AS b,\n {$this->prefix}campaigns AS c,\n {$this->prefix}clients AS cl,\n {$this->prefix}agency AS ag,\n {$this->prefix}account_preference_assoc AS apa,\n {$this->prefix}preferences AS p,\n {$this->prefix}tmp_tz_offset AS tzo\n SET ao.offset = tzo.offset\n WHERE\n p.preference_name = 'timezone'\n AND apa.preference_id = p.preference_id\n AND apa.account_id = ag.account_id\n AND cl.agencyid=ag.agencyid\n AND ao.ad_id=b.bannerid\n AND c.campaignid=b.campaignid\n AND cl.clientid=c.clientid\n AND ag.agencyid=cl.agencyid\n AND apa.value = tzo.timezone;\n ");
OA::debug("Created ad/offset mapping table", PEAR_LOG_DEBUG);
// So, now we have a table tmp_ad_offset which contains every banner id, and the offset of the account it belongs to.
// We can use this to do a complex GROUP BY to collapse data down into the user's timzone's midday
$this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}data_summary_ad_hourly_rolledup");
$this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}data_summary_ad_hourly_backup");
// Create a new stats table, we do this because trying to delete a bunch of records from the existing table would just fragment the index
$this->oDbh->exec("CREATE TABLE {$this->prefix}data_summary_ad_hourly_rolledup LIKE {$this->prefix}data_summary_ad_hourly;");
// Copy stats over from the existing table to the new table, rolling up according to each ad's offset
OA::debug("Beginning rolled-up stats copy...", PEAR_LOG_DEBUG);
$this->oDbh->exec("INSERT INTO {$this->prefix}data_summary_ad_hourly_rolledup (\n date_time, ad_id, creative_id, zone_id, requests, impressions, clicks, conversions,\n total_basket_value, total_num_items, total_revenue, total_cost, total_techcost, updated )\n SELECT\n CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(dsah.date_time, '+00:00', ao.offset), '%Y-%m-%d 12:00:00'), ao.offset, '+00:00') AS tz_date_time,\n dsah.ad_id, dsah.creative_id, dsah.zone_id, SUM(dsah.requests), SUM(dsah.impressions), SUM(dsah.clicks), SUM(dsah.conversions),\n SUM(dsah.total_basket_value), SUM(dsah.total_num_items), SUM(dsah.total_revenue), SUM(dsah.total_cost), SUM(dsah.total_techcost), '{$updated}'\n FROM\n {$this->prefix}data_summary_ad_hourly AS dsah,\n {$this->prefix}tmp_ad_offset AS ao\n WHERE\n ao.ad_id=dsah.ad_id\n AND CONVERT_TZ(dsah.date_time, '+00:00', ao.offset) < '{$sDate}'\n GROUP BY\n tz_date_time, ad_id, creative_id, zone_id;\n ");
OA::debug("Completed rolled-up stats copy...", PEAR_LOG_DEBUG);
// Copy any un-rolled up stats records over into the new table
OA::debug("Beginning *non* rolled-up stats copy...", PEAR_LOG_DEBUG);
$this->oDbh->exec("INSERT INTO {$this->prefix}data_summary_ad_hourly_rolledup (\n date_time, ad_id, creative_id, zone_id, requests, impressions, clicks, conversions,\n total_basket_value, total_num_items, total_revenue, total_cost, total_techcost, updated)\n SELECT\n dsah.date_time AS tz_date_time, dsah.ad_id, dsah.creative_id, dsah.zone_id, dsah.requests, dsah.impressions, dsah.clicks, dsah.conversions,\n dsah.total_basket_value, dsah.total_num_items, dsah.total_revenue, dsah.total_cost, dsah.total_techcost, '{$updated}'\n FROM\n {$this->prefix}data_summary_ad_hourly AS dsah,\n {$this->prefix}tmp_ad_offset AS ao\n WHERE\n ao.ad_id=dsah.ad_id\n AND CONVERT_TZ(dsah.date_time, '+00:00', ao.offset) >= '{$sDate}'\n ");
OA::debug("Completed *non* rolled-up stats copy...", PEAR_LOG_DEBUG);
// Swap the old table with the new
$this->oDbh->exec("RENAME TABLE {$this->prefix}data_summary_ad_hourly TO {$this->prefix}data_summary_ad_hourly_backup");
$this->oDbh->exec("RENAME TABLE {$this->prefix}data_summary_ad_hourly_rolledup TO {$this->prefix}data_summary_ad_hourly");
OA::debug("Swapped new table for old...", PEAR_LOG_DEBUG);
// Cleanup
$this->oDbh->exec("DROP TABLE {$this->prefix}tmp_ad_offset;");
$this->oDbh->exec("DROP TABLE {$this->prefix}tmp_tz_offset;");
OA::debug("Woo hoo stats rolled up for pre-{$sDate}", PEAR_LOG_INFO);
}
/**
* Generate and save a recovery ID for a user
*
* @param int user ID
* @return array generated recovery ID
*/
function generateRecoveryId($userId)
{
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
// Make sure that recoveryId is unique in password_recovery table
do {
$recoveryId = strtoupper(md5(uniqid('', true)));
$recoveryId = substr(chunk_split($recoveryId, 8, '-'), -23, 22);
$doPwdRecovery->recovery_id = $recoveryId;
} while ($doPwdRecovery->find() > 0);
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
$doPwdRecovery->whereAdd('user_id = ' . DBC::makeLiteral($userId));
$doPwdRecovery->delete(true);
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
$doPwdRecovery->user_type = 'user';
$doPwdRecovery->user_id = $userId;
$doPwdRecovery->recovery_id = $recoveryId;
$doPwdRecovery->updated = OA::getNowUTC();
$doPwdRecovery->insert();
return $recoveryId;
}
/**
* Process input from user and creates/upgrades DB etc....
*
* @param OA_Admin_UI_Component_Form $oForm
* @param OX_Admin_UI_Install_Wizard $oWizard
*/
protected function processDatabaseAction($oForm, $oWizard)
{
$oUpgrader = $this->getUpgrader();
$upgraderSuccess = false;
$aDbConfig = $oForm->populateDbConfig();
if ($oUpgrader->canUpgradeOrInstall()) {
$installStatus = $oUpgrader->existing_installation_status;
define('DISABLE_ALL_EMAILS', 1);
OA_Permission::switchToSystemProcessUser('Installer');
if ($installStatus == OA_STATUS_NOT_INSTALLED) {
if ($oUpgrader->install($aDbConfig)) {
$message = $GLOBALS['strDBInstallSuccess'];
$upgraderSuccess = true;
}
} else {
if ($oUpgrader->upgrade($oUpgrader->package_file)) {
// Timezone support - hack
if ($oUpgrader->versionInitialSchema['tables_core'] < 538 && empty($aDbConfig['noTzAlert'])) {
OA_Dal_ApplicationVariables::set('utc_update', OA::getNowUTC());
}
// Clear the menu cache to built a new one with the new settings
OA_Admin_Menu::_clearCache(OA_ACCOUNT_ADMIN);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_MANAGER);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_ADVERTISER);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_TRAFFICKER);
OA_Admin_Menu::singleton();
$message = $GLOBALS['strDBUpgradeSuccess'];
$upgraderSuccess = true;
}
}
OA_Permission::switchToSystemProcessUser();
//get back to normal user previously logged in
} else {
if ($oUpgrader->existing_installation_status == OA_STATUS_CURRENT_VERSION) {
$upgraderSuccess = true;
//rare but can occur if DB has been installed and user revisits the screen
}
}
$dbSuccess = $upgraderSuccess && !$oUpgrader->oLogger->errorExists;
if ($dbSuccess) {
//show success status
OA_Admin_UI::getInstance()->queueMessage($message, 'global', 'info');
} else {
//sth went wrong, display messages from upgrader
$aMessages = OX_Admin_UI_Install_InstallUtils::getMessagesWithType($oUpgrader->getMessages());
$this->setModelProperty('aMessages', $aMessages);
}
return $dbSuccess;
}
/**
* Overrides _refreshUpdated() because the updated field is called 'lastused'.
* This method is called on insert() and update().
*
*/
function _refreshUpdated()
{
$this->lastused = OA::getNowUTC();
}
/**
* Enter description here...
*
* @param integer $actionid One of the following:
* - 1 for INSERT
* - 2 for UPDATE
* - 3 for DELETE
* @param unknown_type $oDataObject
* @param unknown_type $parentid
* @return unknown
*/
function audit($actionid, $oDataObject = null, $parentid = null)
{
if (OA::getConfigOption('audit', 'enabled', false)) {
if ($this->_auditEnabled()) {
if (is_null($this->doAudit)) {
$this->doAudit = $this->factory('audit');
}
$this->doAudit->actionid = $actionid;
$this->doAudit->context = $this->getTableWithoutPrefix();
$this->doAudit->contextid = $this->_getContextId();
$this->doAudit->parentid = $parentid;
$this->doAudit->username = OA_Permission::getUsername();
$this->doAudit->userid = OA_Permission::getUserId();
if (!isset($this->doAudit->usertype)) {
$this->doAudit->usertype = 0;
}
// Set the account IDs that need to be used in auditing
// this type of entity record
$aAccountIds = $this->getOwningAccountIds();
// Set the primary account ID
if (isset($aAccountIds[OA_ACCOUNT_MANAGER])) {
$this->doAudit->account_id = $aAccountIds[OA_ACCOUNT_MANAGER];
} else {
$this->doAudit->account_id = $aAccountIds[OA_ACCOUNT_ADMIN];
}
// Set the advertiser account ID, if required
if (isset($aAccountIds[OA_ACCOUNT_ADVERTISER])) {
$this->doAudit->advertiser_account_id = $aAccountIds[OA_ACCOUNT_ADVERTISER];
}
// Set the trafficker account ID, if required
if (isset($aAccountIds[OA_ACCOUNT_TRAFFICKER])) {
$this->doAudit->website_account_id = $aAccountIds[OA_ACCOUNT_TRAFFICKER];
}
// Prepare a generic array of data to be stored in the audit record
$aAuditFields = $this->_prepAuditArray($actionid, $oDataObject);
// Individual objects can customise this data (add, remove, format...)
$this->_buildAuditArray($actionid, $aAuditFields);
// Do not audit if nothing has changed
if (count($aAuditFields)) {
// Serialise the data
$this->doAudit->details = serialize($aAuditFields);
$this->doAudit->updated = OA::getNowUTC();
// Finally, insert the audit record
$id = $this->doAudit->insert();
// Perform post-audit actions
$this->_postAuditTrigger($actionid, $oDataObject, $id);
return $id;
}
}
}
return false;
}
/**
* Overrides _refreshUpdated() because the updated field is called t_stamp.
* This method is called on insert() and update().
*
*/
function _refreshUpdated()
{
$this->t_stamp = OA::getNowUTC();
}
/**
* Remove many unused sessions from storage.
*
* @todo Use ANSI SQL syntax, such as NOW() + INTERVAL '12' HOUR
*/
function pruneOldSessions()
{
$tableS = $this->oDbh->quoteIdentifier($this->getTablePrefix() . 'session', true);
$query = "\n DELETE FROM {$tableS}\n WHERE\n UNIX_TIMESTAMP('" . OA::getNowUTC() . "') - UNIX_TIMESTAMP(lastused) > 43200\n ";
$this->oDbh->query($query);
}
function MAX_AclSave($acls, $aEntities, $page = false)
{
//$conf = $GLOBALS['_MAX']['CONF'];
$oDbh =& OA_DB::singleton();
if ($page === false) {
$page = basename($_SERVER['SCRIPT_NAME']);
}
switch ($page) {
case 'banner-acl.php':
case 'market-campaign-acl.php':
$table = 'banners';
$aclsTable = 'acls';
$fieldId = 'bannerid';
break;
case 'channel-acl.php':
$table = 'channel';
$aclsTable = 'acls_channel';
$fieldId = 'channelid';
break;
default:
return false;
}
$aclsObjectId = $aEntities[$fieldId];
$sLimitation = OA_aclGetSLimitationFromAAcls($acls);
$doTable = OA_Dal::factoryDO($table);
$doTable->{$fieldId} = $aclsObjectId;
$found = $doTable->find(true);
if ($sLimitation == $doTable->compiledlimitation) {
return true;
// No changes to the ACL
}
$doAcls = OA_Dal::factoryDO($aclsTable);
$doAcls->whereAdd($fieldId . ' = ' . $aclsObjectId);
$doAcls->delete(true);
if (!empty($acls)) {
foreach ($acls as $acl) {
$deliveryLimitationPlugin =& OA_aclGetComponentFromRow($acl);
$doAcls = OA_Dal::factoryDO($aclsTable);
$doAcls->{$fieldId} = $aclsObjectId;
$doAcls->logical = $acl['logical'];
$doAcls->type = $acl['type'];
$doAcls->comparison = $acl['comparison'];
$doAcls->data = $deliveryLimitationPlugin->getData();
$doAcls->executionorder = $acl['executionorder'];
$id = $doAcls->insert();
if (!$id) {
return false;
}
}
}
$doTable->acl_plugins = MAX_AclGetPlugins($acls);
$doTable->compiledlimitation = $sLimitation;
$doTable->acls_updated = OA::getNowUTC();
$doTable->update();
// When a channel limitation changes - All banners with this channel must be re-learnt
if ($page == 'channel-acl.php') {
$affected_ads = array();
$table = modifyTableName('acls');
$query = "\n SELECT\n DISTINCT(bannerid)\n FROM\n {$table}\n WHERE\n type = 'deliveryLimitations:Site:Channel'\n AND (data = '{$aclsObjectId}' OR data LIKE '%,{$aclsObjectId}' OR data LIKE '%,{$aclsObjectId},%' OR data LIKE '{$aclsObjectId},%')\n ";
$res = $oDbh->query($query);
if (PEAR::isError($res)) {
return $res;
}
while ($row = $res->fetchRow()) {
$doBanners = OA_Dal::staticGetDO('banners', $row['bannerid']);
if ($doBanners->bannerid == $row['bannerid']) {
$doBanners->acls_updated = OA::getNowUTC();
$doBanners->update();
}
}
}
return true;
}
OA_Permission::switchToSystemProcessUser('Installer');
if ($installStatus == OA_STATUS_NOT_INSTALLED) {
if ($oUpgrader->install($_POST['aConfig'])) {
$message = $strDBCreatedSuccesful . ' ' . OA_VERSION;
$action = OA_UPGRADE_INSTALL;
}
} else {
if (empty($_COOKIE['oat']) || $_COOKIE['oat'] != OA_UPGRADE_UPGRADE) {
// Hey, what's going on, we shouldn't be here, go back to login!
$action = OA_UPGRADE_LOGIN;
} elseif ($oUpgrader->upgrade($oUpgrader->package_file)) {
$message = $strUpgradeComplete;
$action = OA_UPGRADE_UPGRADE;
// Timezone support - hack
if ($oUpgrader->versionInitialSchema['tables_core'] < 538 && empty($_POST['noTzAlert'])) {
OA_Dal_ApplicationVariables::set('utc_update', OA::getNowUTC());
}
// Clear the menu cache to built a new one with the new settings
OA_Admin_Menu::_clearCache(OA_ACCOUNT_ADMIN);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_MANAGER);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_ADVERTISER);
OA_Admin_Menu::_clearCache(OA_ACCOUNT_TRAFFICKER);
OA_Admin_Menu::singleton();
}
}
}
if ($action != OA_UPGRADE_UPGRADE && $action != OA_UPGRADE_INSTALL && $action != OA_UPGRADE_LOGIN || $oUpgrader->oLogger->errorExists) {
// if they're being redirected from an install, they will have DB info in POST, otherwise they will have DBinfo in CONF
if ($_POST['aConfig']) {
$aDatabase = $_POST['aConfig'];
} else {
