/**
* A method to launch and display the widget
*
* @param array $aParams The parameters array, usually $_REQUEST
*/
function display()
{
if (!$this->oTpl->is_cached()) {
RV::disableErrorHandling();
$oRss = new XML_RSS($this->url);
$result = $oRss->parse();
RV::enableErrorHandling();
// ignore bad character error which could appear if rss is using invalid characters
if (PEAR::isError($result)) {
if (!strstr($result->getMessage(), 'Invalid character')) {
PEAR::raiseError($result);
// rethrow
$this->oTpl->caching = false;
}
}
$aPost = array_slice($oRss->getItems(), 0, $this->posts);
foreach ($aPost as $key => $aValue) {
$aPost[$key]['origTitle'] = $aValue['title'];
if (strlen($aValue['title']) > 38) {
$aPost[$key]['title'] = substr($aValue['title'], 0, 38) . '...';
}
}
$this->oTpl->assign('title', $this->title);
$this->oTpl->assign('feed', $aPost);
$this->oTpl->assign('siteTitle', $this->siteTitle);
$this->oTpl->assign('siteUrl', $this->siteUrl);
}
$this->oTpl->display();
}
function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$prefix = $GLOBALS['_MAX']['CONF']['table']['prefix'];
if ($this->oDbh->dbsyntax == 'pgsql') {
$oTable =& $this->oUpgrade->oDBUpgrader->oTable;
foreach ($oTable->aDefinition['tables'] as $tableName => $aTable) {
foreach ($aTable['fields'] as $fieldName => $aField) {
if (!empty($aField['autoincrement'])) {
// Check actual sequence name
$oldSequenceName = $this->getLinkedSequence($prefix . $tableName, $fieldName);
if ($oldSequenceName) {
$newSequenceName = OA_DB::getSequenceName($this->oDbh, $tableName, $fieldName);
if ($oldSequenceName != $newSequenceName) {
$this->logOnly("Non standard sequence name found: " . $oldSequenceName);
$qTable = $this->oDbh->quoteIdentifier($prefix . $tableName, true);
$qField = $this->oDbh->quoteIdentifier($fieldName, true);
$qOldSequence = $this->oDbh->quoteIdentifier($oldSequenceName, true);
$qNewSequence = $this->oDbh->quoteIdentifier($newSequenceName, true);
RV::disableErrorHandling();
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
if (PEAR::isError($result)) {
if ($result->getCode() == MDB2_ERROR_ALREADY_EXISTS) {
$result = $this->oDbh->exec("DROP SEQUENCE {$qNewSequence}");
if (PEAR::isError($result)) {
$this->logError("Could not drop existing sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
}
}
if (PEAR::isError($result)) {
$this->logError("Could not rename {$oldSequenceName} to {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qTable} ALTER {$qField} SET DEFAULT nextval(" . $this->oDbh->quote($qNewSequence) . ")");
if (PEAR::isError($result)) {
$this->logError("Could not set column default to sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
RV::enableErrorHandling();
$result = $oTable->resetSequenceByData($tableName, $fieldName);
if (PEAR::isError($result)) {
$this->logError("Could not reset sequence value for {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$this->logOnly("Successfully renamed {$oldSequenceName} to {$newSequenceName}");
}
} else {
$this->logOnly("No sequence found for {$tableName}.{$fieldName}");
}
}
}
}
}
return true;
}
/**
* A method to test the checkOperationIntervalValue() method.
*
*/
function testCheckOperationIntervalValue()
{
RV::disableErrorHandling();
for ($i = -1; $i <= 61; $i++) {
$result = OX_OperationInterval::checkOperationIntervalValue($i);
if ($i == 1 || $i == 2 || $i == 3 || $i == 4 || $i == 5 || $i == 6 || $i == 10 || $i == 12 || $i == 15 || $i == 20 || $i == 30 || $i == 60) {
$this->assertTrue($result);
} else {
$this->assertTrue(PEAR::isError($result));
}
$result = OX_OperationInterval::checkOperationIntervalValue(120);
$this->assertTrue(PEAR::isError($result));
}
RV::enableErrorHandling();
}
/**
* Returns phpAdsNew style config version.
*
* The OpenX version "number" is converted to an int using the following table:
*
* 'beta-rc' => 0.1
* 'beta' => 0.2
* 'rc' => 0.3
* '' => 0.4
*
* i.e.
* v0.3.29-beta-rc10 becomes:
* 0 * 1000 +
* 3 * 100 +
* 29 * 1 + // Cannot exceed 100 patch releases!
* 0.1 +
* 10 / 1000 =
* -------------
* 3293.1
*/
function getConfigVersion($version)
{
$a = array('dev' => -0.001, 'beta-rc' => 0.1, 'beta' => 0.2, 'rc' => 0.3, 'stable' => 0.4);
$version = RV::stripVersion(strtolower($version), array('dev', 'stable'));
if (preg_match('/^v/', $version)) {
$v = preg_split('/[.-]/', substr($version, 1));
} else {
$v = preg_split('/[.-]/', $version);
}
if (count($v) < 3) {
return false;
}
// Prepare value from the first 3 items
$returnValue = $v[0] * 1000 + $v[1] * 100 + $v[2];
// How many items were there?
if (count($v) == 5) {
// Check that it is a beta-rc release
if (!$v[3] == 'beta' || !preg_match('/^rc(\\d+)/', $v[4], $aMatches)) {
return false;
}
// Add the beta-rc
$returnValue += $a['beta-rc'] + $aMatches[1] / 1000;
return $returnValue;
} else {
if (count($v) == 4) {
// Check that it is a tag or rc numer
if (isset($a[$v[3]])) {
// Add the beta
$returnValue += $a[$v[3]];
return $returnValue;
} else {
if (preg_match('/^rc(\\d+)/', $v[3], $aMatches)) {
// Add the rc
$returnValue += $a['rc'] + $aMatches[1] / 1000;
return $returnValue;
}
}
return false;
}
}
// Stable release
$returnValue += $a['stable'];
return $returnValue;
}
/**
* A method to test the drop table method.
*
* Requirements:
* Test 1: Test that a table can be dropped.
* Test 2: Test that a temporary table can be dropped.
* Test 3: Test that a tablename with uppercase prefix can be dropped.
* Test 4: Test that a tablename with a mixed prefix can be dropped.
*/
function testDropTable()
{
// Test 1
$conf =& $GLOBALS['_MAX']['CONF'];
$prefix = $conf['table']['prefix'];
$oDbh =& OA_DB::singleton();
$table = $oDbh->quoteIdentifier($prefix . 'foo', true);
$oTable = new OA_DB_Table();
$query = "CREATE TABLE {$table} ( a INTEGER )";
$oDbh->query($query);
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual($aExistingTables[0], $prefix . 'foo');
$this->assertTrue($oTable->dropTable($prefix . 'foo'));
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual(count($aExistingTables), 0, $prefix . 'foo');
//TestEnv::restoreEnv();
// Test 2
$conf =& $GLOBALS['_MAX']['CONF'];
$oDbh =& OA_DB::singleton();
$oTable = new OA_DB_Table();
$table = $oDbh->quoteIdentifier($prefix . 'foo', true);
$query = "CREATE TEMPORARY TABLE {$table} ( a INTEGER )";
$oDbh->query($query);
// Test table exists with an insert
$query = "INSERT INTO {$table} (a) VALUES (37)";
$result = $oDbh->query($query);
$this->assertTrue($result);
$this->assertTrue($oTable->dropTable($prefix . 'foo'));
// Test table does not exist with an insert
$query = "INSERT INTO {$table} (a) VALUES (37)";
RV::disableErrorHandling();
$result = $oDbh->query($query);
RV::enableErrorHandling();
$this->assertEqual(strtolower(get_class($result)), 'mdb2_error');
//TestEnv::restoreEnv();
// Test 3
$conf =& $GLOBALS['_MAX']['CONF'];
$conf['table']['prefix'] = 'OA_';
$prefix = $conf['table']['prefix'];
$oDbh =& OA_DB::singleton();
$table = $oDbh->quoteIdentifier($prefix . 'foo', true);
$query = "CREATE TABLE {$table} ( a INTEGER )";
$oDbh->query($query);
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual($aExistingTables[0], 'OA_foo');
$this->assertTrue($oTable->dropTable('OA_foo'));
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual(count($aExistingTables), 0, 'Table OA_foo');
//TestEnv::restoreEnv();
// Test 4
$conf =& $GLOBALS['_MAX']['CONF'];
$conf['table']['prefix'] = 'oA_';
$prefix = $conf['table']['prefix'];
$oDbh =& OA_DB::singleton();
$table = $oDbh->quoteIdentifier($prefix . 'foo', true);
$oTable = new OA_DB_Table();
$query = "CREATE TABLE {$table} ( a INTEGER )";
$oDbh->query($query);
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual($aExistingTables[0], 'oA_foo');
$this->assertTrue($oTable->dropTable('oA_foo'));
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
$this->assertEqual(count($aExistingTables), 0, 'Table oA_foo');
//TestEnv::restoreEnv();
}
/**
* A method to locate all "email" type zones that are linked
* to a given campaign, via the campaign's children banners
* (as campaigns cannot be linked to email zones directly).
*
* @param integer $campaignId The ID of the campaign.
* @return mixed An array of "email" type zone IDs found linked
* to the given campaign, an empry array if no
* such zones were found, or an MDB2_Error object
* on any kind of database error.
*/
function getLinkedEmailZoneIds($campaignId)
{
// Test input
if (!is_integer($campaignId) || $campaignId <= 0) {
// Not a valid campaign ID, return no found zones
$aResult = array();
return $aResult;
}
// Prepare and execute query
$prefix = $this->getTablePrefix();
$sQuery = "\n SELECT\n {$prefix}zones.zoneid AS zone_id\n FROM\n {$prefix}banners,\n {$prefix}ad_zone_assoc,\n {$prefix}zones\n WHERE\n {$prefix}banners.campaignid = " . $this->oDbh->quote($campaignId, 'integer') . "\n AND\n {$prefix}banners.bannerid = {$prefix}ad_zone_assoc.ad_id\n AND\n {$prefix}ad_zone_assoc.zone_id = {$prefix}zones.zoneid\n AND\n {$prefix}zones.delivery = " . $this->oDbh->quote(MAX_ZoneEmail, 'integer') . "\n ORDER BY\n zone_id";
RV::disableErrorHandling();
$rsResult = $this->oDbh->query($sQuery);
RV::enableErrorHandling();
if (PEAR::isError($rsResult)) {
return $rsResult;
}
$aResult = array();
while ($aRow = $rsResult->fetchRow()) {
$aResult[] = $aRow['zone_id'];
}
$rsResult->free();
return $aResult;
}
/**
* Resets a (postgresql) sequence to 1
* similar to OA_DB_Table::resetSequence()
* DOESN'T SEEM TO WORK THO
*
* @param string $sequence the name of the sequence to reset
* @return boolean true on success, false otherwise
*/
function resetSequence($tableName)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$oDbh = OA_DB::singleton();
if ($aConf['database']['type'] == 'pgsql') {
OA_DB::setCaseSensitive();
$aSequences = $oDbh->manager->listSequences();
OA_DB::disableCaseSensitive();
if (is_array($aSequences)) {
OA::debug('Resetting sequence ' . $sequence, PEAR_LOG_DEBUG);
RV::disableErrorHandling();
$tableName = substr($aConf['table']['prefix'] . $tableName, 0, 29) . '_';
foreach ($aSequences as $k => $sequence) {
if (strpos($sequence, $tableName) === 0) {
$sequence = $oDbh->quoteIdentifier($sequence . '_seq', true);
$result = $oDbh->exec("SELECT setval('{$sequence}', 1, false)");
break;
}
}
RV::enableErrorHandling();
if (PEAR::isError($result)) {
OA::debug('Unable to reset sequence on table ' . $tableName, PEAR_LOG_ERR);
return false;
}
}
} else {
if ($aConf['database']['type'] == 'mysql') {
$tableName = $aConf['table']['prefix'] . $tableName;
RV::disableErrorHandling();
$result = $oDbh->exec("ALTER TABLE {$tableName} AUTO_INCREMENT = 1");
RV::enableErrorHandling();
if (PEAR::isError($result)) {
OA::debug('Unable to reset sequence on table ' . $tableName, PEAR_LOG_ERR);
return false;
}
}
}
return true;
}
// Standard messages
$haltMessage = "\nThe {$scriptName} script will NOT be run.\n\n";
if ($argc !== 2) {
echo "\nRequires the hostname to be passed in as a string, as per the standard maintenance CLI script.\n";
echo $haltMessage;
exit;
}
if (!defined('INTERVAL_START') || !defined('INTERVAL_END')) {
echo "\nPlease ensure that you have read the comments in the {$scriptName} script.\n\nYou will also find out how to make this script work by reading the comments. :-)\n";
echo $haltMessage;
exit;
}
// Initialise the script
RV::disableErrorHandling();
$result = OX_OperationInterval::checkOperationIntervalValue(OX_OperationInterval::getOperationInterval());
RV::enableErrorHandling();
if (PEAR::isError($result)) {
$message = "\nThe operation interval in your OpenX configuration file is not valid. Please see the OpenX\ndocumentation for more details on valid operation interval values.\n";
echo $message;
echo $haltMessage;
exit;
}
$oStartDate = new Date(INTERVAL_START);
$result = OX_OperationInterval::checkDateIsStartDate($oStartDate);
if (!$result) {
$message = "\nThe start date defined in the {$scriptName} script is not a valid operation interval start date.\nPlease edit the statisticsTestAndCorrect.php script before running.\n";
echo $message;
echo $haltMessage;
exit;
}
$oEndDate = new Date(INTERVAL_END);
/**
* This method returns conversion statistics for a given campaign.
*
* @param integer $campaignId The ID of the campaign to view statistics
* @param date $oStartDate The date from which to get statistics (inclusive)
* @param date $oEndDate The date to which to get statistics (inclusive)
* @param bool $localTZ Should stats be using the manager TZ or UTC?
*
* @return MDB2_Result_Common
*<ul>
* <li><b>campaignID integer</b> The ID of the campaign
* <li><b>trackerID integer</b> The ID of the tracker
* <li><b>bannerID integer</b> The ID of the banner
* <li><b>conversionTime date</b> The time of the conversion
* <li><b>conversionStatus integer</b> The conversion status
* <li><b>userIp string</b> The IP address of the conversion
* <li><b>action integer</b> The conversion event type
* <li><b>window integer</b> The conversion window
* <li><b>variables array</b> Array of variables for this conversion
* with each variable as an array('variableName' => 'variableValue')
*</ul>
*/
public function getCampaignConversionStatistics($campaignId, $oStartDate, $oEndDate, $localTZ = false)
{
$tableBanners = $this->quoteTableName('banners');
$tableVariables = $this->quoteTableName('variables');
$tableDataIntermadiateAdConnection = $this->quoteTableName('data_intermediate_ad_connection');
$tableDataIntermadiateAdVariableValue = $this->quoteTableName('data_intermediate_ad_variable_value');
$localTZ = false;
$dateField = 'd.tracker_date_time';
$query = "\n SELECT\n d.data_intermediate_ad_connection_id as conversionid,\n b.campaignid as campaignid,\n d.tracker_id as trackerid,\n d.ad_id as bannerid,\n d.tracker_date_time as tracker_date_time,\n d.connection_date_time as connection_date_time,\n d.connection_status as conversionstatus,\n d.tracker_ip_address as userip,\n d.connection_action as action,\n v.name as variablename,\n i.value as variablevalue\n FROM\n {$tableBanners} AS b\n JOIN {$tableDataIntermadiateAdConnection} AS d ON (b.bannerid = d.ad_id)\n left JOIN {$tableDataIntermadiateAdVariableValue} AS i ON (d.data_intermediate_ad_connection_id = i.data_intermediate_ad_connection_id)\n left JOIN {$tableVariables} AS v ON (i.tracker_variable_id = v.variableid)\n WHERE\n TRUE " . $this->getWhereDate($oStartDate, $oEndDate, $localTZ, $dateField) . "\n AND b.campaignid = " . $campaignId . "\n ";
RV::disableErrorHandling();
$rsResult = $this->oDbh->query($query);
RV::enableErrorHandling();
$aResult = array();
while ($row = $rsResult->fetchRow()) {
$aResult[$row['conversionid']] = array('campaignID' => $row['campaignid'], 'trackerID' => $row['trackerid'], 'bannerID' => $row['bannerid'], 'conversionTime' => $row['tracker_date_time'], 'conversionStatus' => $row['conversionstatus'], 'userIp' => $row['userip'], 'action' => $row['action'], 'window' => strtotime($row['tracker_date_time'] . " ") - strtotime($row['connection_date_time'] . " "), 'variables' => null);
if (!empty($row['variablename'])) {
$aVariables[$row['conversionid']][] = array('name' => $row['variablename'], 'value' => $row['variablevalue']);
}
}
if (isset($aVariables)) {
foreach ($aVariables as $conversionId => $aConversionVariables) {
foreach ($aConversionVariables as $key => $aVariable) {
$aResult[$conversionId]['variables'][$aVariable['name']] = $aVariable['value'];
}
}
}
// array_values function used for not being affected by a PEAR bug
// https://pear.php.net/bugs/bug.php?id=16780
$aResult = array_values($aResult);
return $aResult;
}
/**
* A method to validate table name
*
* @param string $name
* @return true if valid PEAR error otherwise
*/
static function validateTableName($name)
{
/*if ( !preg_match( '/^([a-zA-z_])([a-zA-z0-9_])*$/', $name) )
{
$result = false;
}
else if (preg_match( '/(\\\\|\/|\"|\\\'| |\(|\)|\:|\;|\`|\[|\]|\^)/', $name))
{
$result = false;
}*/
$result = true;
RV::disableErrorHandling();
$pattern = '/(?P<found>[\\x00-\\x23]|[\\x25-\\x29]|[\\x2a-\\x2f]|[\\x3a-\\x3f]|[\\x40]|[\\x5b-\\x5e]|[\\x60]|[\\x7b-\\x7e]|[\\x9c]|[\\xff])/U';
if (preg_match($pattern, $name, $aMatches)) {
$msg = 'Illegal character in table name ' . $aMatches['found'] . ' chr(' . ord($aMatches['found']) . ')';
$result = PEAR::raiseError($msg);
}
if (PEAR::isError($result)) {
RV::enableErrorHandling();
$msg = 'Table names may not contain any of ! " # % & \' ( ) * + , - . \\/ : ; < = > ? @ [ \\ ] ^ ` { | } ~ £ nor any non-printing characters';
return $result;
}
$oDbh = OA_DB::singleton();
if (PEAR::isError($oDbh)) {
RV::enableErrorHandling();
return $oDbh;
}
$result = $oDbh->manager->validateTableName($name);
RV::enableErrorHandling();
if (PEAR::isError($result)) {
return $result;
}
return true;
}
function generateTags($zoneId, $codeType, $aParams = null)
{
// Backwards Compatibity Array for code types
$aBackwardsCompatibityTypes = array('adframe' => 'invocationTags:oxInvocationTags:adframe', 'adjs' => 'invocationTags:oxInvocationTags:adjs', 'adlayer' => 'invocationTags:oxInvocationTags:adlayer', 'adview' => 'invocationTags:oxInvocationTags:adview', 'adviewnocookies' => 'invocationTags:oxInvocationTags:adviewnocookies', 'local' => 'invocationTags:oxInvocationTags:local', 'popup' => 'invocationTags:oxInvocationTags:popup', 'xmlrpc' => 'invocationTags:oxInvocationTags:xmlrpc');
// Translate old code type to new Component Identifier
if (array_key_exists($codeType, $aBackwardsCompatibityTypes)) {
$codeType = $aBackwardsCompatibityTypes[$codeType];
}
if ($this->checkIdExistence('zones', $zoneId)) {
$doZones = OA_Dal::staticGetDO('zones', $zoneId);
if (!$this->checkPermissions(null, 'affiliates', $doZones->affiliateid, OA_PERM_ZONE_INVOCATION)) {
return false;
}
$aAllowedTags = $this->getAllowedTags();
if (!in_array($codeType, $aAllowedTags)) {
$this->raiseError('Field \'codeType\' must be one of the enum: ' . join(', ', $aAllowedTags));
return false;
}
if (!empty($codeType)) {
require_once MAX_PATH . '/lib/max/Admin/Invocation.php';
$maxInvocation = new MAX_Admin_Invocation();
// factory plugin for this $codetype
RV::disableErrorHandling();
$invocationTag = OX_Component::factoryByComponentIdentifier($codeType);
RV::enableErrorHandling();
if ($invocationTag === false) {
$this->raiseError('Error while factory invocationTag plugin');
return false;
}
$invocationTag->setInvocation($maxInvocation);
$aParams['zoneid'] = $zoneId;
$aParams['codetype'] = $codeType;
$buffer = $maxInvocation->generateInvocationCode($invocationTag, $aParams);
return $buffer;
} else {
$this->raiseError('Parameter codeType wrong');
}
}
return false;
}
/**
* Resets a (postgresql) sequence to a value
*
* Note: the value parameter is ignored on MySQL. Autoincrements will always be resetted
* to 1 or the highest value already present in the table.
*
* @param string $sequence the name of the sequence to reset
* @param int $value the sequence value for the next entry
* @return boolean true on success, false otherwise
*/
function resetSequence($sequence, $value = 1)
{
$aConf = $GLOBALS['_MAX']['CONF'];
OA::debug('Resetting sequence ' . $sequence, PEAR_LOG_DEBUG);
RV::disableErrorHandling();
if ($aConf['database']['type'] == 'pgsql') {
if ($value < 1) {
$value = 1;
} else {
$value = (int) $value;
}
$sequence = $this->oDbh->quoteIdentifier($sequence, true);
$result = $this->oDbh->exec("SELECT setval('{$sequence}', {$value}, false)");
RV::enableErrorHandling();
if (PEAR::isError($result)) {
OA::debug('Unable to reset sequence ' . $sequence, PEAR_LOG_ERR);
return false;
}
} else {
if ($aConf['database']['type'] == 'mysql') {
$result = $this->oDbh->exec("ALTER TABLE {$GLOBALS['_MAX']['CONF']['table']['prefix']}{$sequence} AUTO_INCREMENT = 1");
RV::enableErrorHandling();
if (PEAR::isError($result)) {
OA::debug('Unable to reset auto increment on table ' . $sequence, PEAR_LOG_ERR);
return false;
}
}
}
return true;
}
/**
* walk an array of version information to build a list of required upgrades
* they must be in the RIGHT order!!!
* hence the weird sorting of keys etc..
*/
function getUpgradePackageList($verPrev, $aVersions = null)
{
$verPrev = RV::stripVersion($verPrev);
$aFiles = array();
if (is_array($aVersions)) {
ksort($aVersions, SORT_NUMERIC);
foreach ($aVersions as $release => $aMajor) {
ksort($aMajor, SORT_NUMERIC);
foreach ($aMajor as $major => $aMinor) {
ksort($aMinor, SORT_NUMERIC);
foreach ($aMinor as $minor => $aStatus) {
if (array_key_exists('-beta-rc', $aStatus)) {
$aKeys = array_keys($aStatus['-beta-rc']);
sort($aKeys, SORT_NUMERIC);
foreach ($aKeys as $k => $v) {
$version = $release . '.' . $major . '.' . $minor . '-beta-rc' . $v;
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $aStatus['-beta-rc'][$v]['file'];
}
}
}
if (array_key_exists('-beta', $aStatus)) {
$aBeta = $aStatus['-beta'];
foreach ($aBeta as $key => $file) {
$version = $release . '.' . $major . '.' . $minor . '-beta';
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $file;
}
}
}
if (array_key_exists('-dev', $aStatus)) {
$aDev = $aStatus['-dev'];
foreach ($aDev as $key => $file) {
$version = $release . '.' . $major . '.' . $minor . '-dev';
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $file;
}
}
}
if (array_key_exists('-rc', $aStatus)) {
$aKeys = array_keys($aStatus['-rc']);
sort($aKeys, SORT_NUMERIC);
foreach ($aKeys as $k => $v) {
$version = $release . '.' . $major . '.' . $minor . '-rc' . $v;
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $aStatus['-rc'][$v]['file'];
}
}
}
if (array_key_exists('-RC', $aStatus)) {
$aKeys = array_keys($aStatus['-RC']);
sort($aKeys, SORT_NUMERIC);
foreach ($aKeys as $k => $v) {
$version = $release . '.' . $major . '.' . $minor . '-RC' . $v;
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $aStatus['-RC'][$v]['file'];
}
}
}
if (array_key_exists('file', $aStatus)) {
$version = $release . '.' . $major . '.' . $minor;
if (version_compare($verPrev, $version) < 0) {
$aFiles[] = $aStatus['file'];
}
}
}
}
}
}
return $aFiles;
}
/**
* Processes submit values of campaign form
*
* @param OA_Admin_UI_Component_Form $form form to process
* @return An array of Pear::Error objects if any
*/
function processCampaignForm($form, &$oComponent = null)
{
$aFields = $form->exportValues();
if (!empty($aFields['start'])) {
$oDate = new Date(date('Y-m-d 00:00:00', strtotime($aFields['start'])));
$oDate->toUTC();
$activate = $oDate->getDate();
} else {
$oDate = new Date(date('Y-m-d 00:00:00'));
$oDate->toUTC();
$activate = $oDate->getDate();
}
if (!empty($aFields['end'])) {
$oDate = new Date(date('Y-m-d 23:59:59', strtotime($aFields['end'])));
$oDate->toUTC();
$expire = $oDate->getDate();
} else {
$expire = null;
}
if (empty($aFields['campaignid'])) {
// The form is submitting a new campaign, so, the ID is not set;
// set the ID to the string "null" so that the table auto_increment
// or sequence will be used when the campaign is created
$aFields['campaignid'] = "null";
} else {
// The form is submitting a campaign modification; need to test
// if any of the banners in the campaign are linked to an email zone,
// and if so, if the link(s) would still be valid if the change(s)
// to the campaign were made...
$dalCampaigns = OA_Dal::factoryDAL('campaigns');
$aCurrentLinkedEmalZoneIds = $dalCampaigns->getLinkedEmailZoneIds($aFields['campaignid']);
if (PEAR::isError($aCurrentLinkedEmalZoneIds)) {
RV::disableErrorHandling();
$errors[] = PEAR::raiseError($GLOBALS['strErrorDBPlain']);
RV::enableErrorHandling();
}
$errors = array();
foreach ($aCurrentLinkedEmalZoneIds as $zoneId) {
$thisLink = Admin_DA::_checkEmailZoneAdAssoc($zoneId, $aFields['campaignid'], $activate, $expire);
if (PEAR::isError($thisLink)) {
$errors[] = $thisLink;
break;
}
}
}
//correct and check revenue and ecpm
correctAdnCheckNumericFormField($aFields, $errors, 'revenue', $GLOBALS['strErrorEditingCampaignRevenue']);
correctAdnCheckNumericFormField($aFields, $errors, 'ecpm', $GLOBALS['strErrorEditingCampaignECPM']);
if (empty($errors)) {
//check booked limits values
// If this is a remnant, ecpm or override campaign with an expiry date, set the target's to unlimited
if (!empty($expire) && ($aFields['campaign_type'] == OX_CAMPAIGN_TYPE_REMNANT || $aFields['campaign_type'] == OX_CAMPAIGN_TYPE_ECPM || $aFields['campaign_type'] == OX_CAMPAIGN_TYPE_OVERRIDE)) {
$aFields['impressions'] = $aFields['clicks'] = $aFields['conversions'] = -1;
} else {
if (!empty($aFields['impr_unlimited']) && $aFields['impr_unlimited'] == 't') {
$aFields['impressions'] = -1;
} else {
if (empty($aFields['impressions']) || $aFields['impressions'] == '-') {
$aFields['impressions'] = 0;
}
}
if (!empty($aFields['click_unlimited']) && $aFields['click_unlimited'] == 't') {
$aFields['clicks'] = -1;
} else {
if (empty($aFields['clicks']) || $aFields['clicks'] == '-') {
$aFields['clicks'] = 0;
}
}
if (!empty($aFields['conv_unlimited']) && $aFields['conv_unlimited'] == 't') {
$aFields['conversions'] = -1;
} else {
if (empty($aFields['conversions']) || $aFields['conversions'] == '-') {
$aFields['conversions'] = 0;
}
}
}
//pricing model - reset fields not applicable to model to 0,
//note that in new flow MAX_FINANCE_CPA allows all limits to be set
if ($aFields['revenue_type'] == MAX_FINANCE_CPM) {
$aFields['clicks'] = -1;
$aFields['conversions'] = -1;
} else {
if ($aFields['revenue_type'] == MAX_FINANCE_CPC) {
$aFields['conversions'] = -1;
} else {
if ($aFields['revenue_type'] == MAX_FINANCE_MT) {
$aFields['impressions'] = -1;
$aFields['clicks'] = -1;
$aFields['conversions'] = -1;
}
}
}
//check type and set priority
if ($aFields['campaign_type'] == OX_CAMPAIGN_TYPE_REMNANT) {
$aFields['priority'] = 0;
// low
} else {
if ($aFields['campaign_type'] == OX_CAMPAIGN_TYPE_CONTRACT_NORMAL) {
$aFields['priority'] = isset($aFields['high_priority_value']) ? $aFields['high_priority_value'] : 5;
//high
} else {
if ($aFields['campaign_type'] == OX_CAMPAIGN_TYPE_OVERRIDE) {
$aFields['priority'] = -1;
// override
} else {
if ($aFields['campaign_type'] == OX_CAMPAIGN_TYPE_ECPM) {
$aFields['priority'] = -2;
// ecpm
}
}
}
}
// Set target
$target_impression = 0;
$target_click = 0;
$target_conversion = 0;
if ($aFields['priority'] > 0) {
// Daily targets need to be set only if the campaign doesn't have both expiration and lifetime targets
$hasExpiration = !empty($expire);
$hasLifetimeTargets = $aFields['impressions'] != -1 || $aFields['clicks'] != -1 || $aFields['conversions'] != -1;
if (!($hasExpiration && $hasLifetimeTargets) && isset($aFields['target_value']) && $aFields['target_value'] != '-') {
switch ($aFields['target_type']) {
case 'target_impression':
$target_impression = $aFields['target_value'];
break;
case 'target_click':
$target_click = $aFields['target_value'];
break;
case 'target_conversion':
$target_conversion = $aFields['target_value'];
break;
}
}
$aFields['weight'] = 0;
} else {
// Set weight
if (!isset($aFields['weight']) || $aFields['weight'] == '-' || $aFields['weight'] == '') {
$aFields['weight'] = 0;
}
}
if ($aFields['anonymous'] != 't') {
$aFields['anonymous'] = 'f';
}
if ($aFields['companion'] != 1) {
$aFields['companion'] = 0;
}
if ($aFields['show_capped_no_cookie'] != 1) {
$aFields['show_capped_no_cookie'] = 0;
}
$new_campaign = $aFields['campaignid'] == 'null';
if (empty($aFields['revenue']) || $aFields['revenue'] <= 0) {
// No revenue information, set to null
$aFields['revenue'] = OX_DATAOBJECT_NULL;
}
if (empty($aFields['ecpm']) || $aFields['ecpm'] <= 0) {
// No ecpm information, set to null
$aFields['ecpm'] = OX_DATAOBJECT_NULL;
}
// Get the capping variables
$block = _initCappingVariables($aFields['time'], $aFields['capping'], $aFields['session_capping']);
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->campaignname = $aFields['campaignname'];
$doCampaigns->clientid = $aFields['clientid'];
$doCampaigns->views = $aFields['impressions'];
$doCampaigns->clicks = $aFields['clicks'];
$doCampaigns->conversions = $aFields['conversions'];
$doCampaigns->priority = $aFields['priority'];
$doCampaigns->weight = $aFields['weight'];
$doCampaigns->target_impression = $target_impression;
$doCampaigns->target_click = $target_click;
$doCampaigns->target_conversion = $target_conversion;
$doCampaigns->min_impressions = $aFields['min_impressions'];
$doCampaigns->ecpm = $aFields['ecpm'];
$doCampaigns->anonymous = $aFields['anonymous'];
$doCampaigns->companion = $aFields['companion'];
$doCampaigns->show_capped_no_cookie = $aFields['show_capped_no_cookie'];
$doCampaigns->comments = $aFields['comments'];
$doCampaigns->revenue = $aFields['revenue'];
$doCampaigns->revenue_type = $aFields['revenue_type'];
$doCampaigns->block = $block;
$doCampaigns->capping = $aFields['capping'];
$doCampaigns->session_capping = $aFields['session_capping'];
// Activation and expiration
$doCampaigns->activate_time = isset($activate) ? $activate : OX_DATAOBJECT_NULL;
$doCampaigns->expire_time = isset($expire) ? $expire : OX_DATAOBJECT_NULL;
if (!empty($aFields['campaignid']) && $aFields['campaignid'] != "null") {
$doCampaigns->campaignid = $aFields['campaignid'];
$doCampaigns->setEcpmEnabled();
$doCampaigns->update();
} else {
$doCampaigns->setEcpmEnabled();
$aFields['campaignid'] = $doCampaigns->insert();
}
if ($oComponent) {
$oComponent->processCampaignForm($aFields);
}
// Recalculate priority only when editing a campaign
// or moving banners into a newly created, and when:
//
// - campaign changes status (activated or deactivated) or
// - the campaign is active and target/weight are changed
//
if (!$new_campaign) {
$doCampaigns = OA_Dal::staticGetDO('campaigns', $aFields['campaignid']);
$status = $doCampaigns->status;
switch (true) {
case (bool) $status != (bool) $aFields['status_old']:
// Run the Maintenance Priority Engine process
OA_Maintenance_Priority::scheduleRun();
break;
case $status == OA_ENTITY_STATUS_RUNNING:
if (!empty($aFields['target_type']) && ${$aFields['target_type']} != $aFields['target_old'] || !empty($aFields['target_type']) && $aFields['target_type_old'] != $aFields['target_type'] || $aFields['weight'] != $aFields['weight_old'] || $aFields['clicks'] != $aFields['previousclicks'] || $aFields['conversions'] != $aFields['previousconversions'] || $aFields['impressions'] != $aFields['previousimpressions']) {
// Run the Maintenance Priority Engine process
OA_Maintenance_Priority::scheduleRun();
}
break;
}
}
// Rebuild cache
// include_once MAX_PATH . '/lib/max/deliverycache/cache-'.$conf['delivery']['cache'].'.inc.php';
// phpAds_cacheDelete();
// Delete channel forecasting cache
include_once 'Cache/Lite.php';
$options = array('cacheDir' => MAX_CACHE);
$cache = new Cache_Lite($options);
$group = 'campaign_' . $aFields['campaignid'];
$cache->clean($group);
$translation = new OX_Translation();
if ($new_campaign) {
// Queue confirmation message
$translated_message = $translation->translate($GLOBALS['strCampaignHasBeenAdded'], array(MAX::constructURL(MAX_URL_ADMIN, 'campaign-edit.php?clientid=' . $aFields['clientid'] . '&campaignid=' . $aFields['campaignid']), htmlspecialchars($aFields['campaignname']), MAX::constructURL(MAX_URL_ADMIN, 'banner-edit.php?clientid=' . $aFields['clientid'] . '&campaignid=' . $aFields['campaignid'])));
OA_Admin_UI::queueMessage($translated_message, 'local', 'confirm', 0);
OX_Admin_Redirect::redirect("advertiser-campaigns.php?clientid=" . $aFields['clientid']);
} else {
$translated_message = $translation->translate($GLOBALS['strCampaignHasBeenUpdated'], array(MAX::constructURL(MAX_URL_ADMIN, 'campaign-edit.php?clientid=' . $aFields['clientid'] . '&campaignid=' . $aFields['campaignid']), htmlspecialchars($aFields['campaignname'])));
OA_Admin_UI::queueMessage($translated_message, 'local', 'confirm', 0);
OX_Admin_Redirect::redirect("campaign-edit.php?clientid=" . $aFields['clientid'] . "&campaignid=" . $aFields['campaignid']);
}
}
//return processing errors
return $errors;
}
function checkPotentialUpgradeProblems()
{
$tableName = $GLOBALS['_MAX']['CONF']['table']['prefix'] . 'campaigns';
$query = "\n SELECT\n campaignid,\n revenue_type\n FROM\n " . $this->oSchema->db->quoteIdentifier($tableName) . "\n WHERE\n (revenue_type = " . MAX_FINANCE_CPM . " AND (clicks > 0 OR conversions > 0)) OR\n (revenue_type = " . MAX_FINANCE_CPC . " AND conversions > 0)\n ORDER BY\n campaignid\n ";
RV::disableErrorHandling();
$aResult = $this->oSchema->db->queryAll($query);
RV::enableErrorHandling();
if (!PEAR::isError($aResult) && count($aResult) > 0) {
$warning = false;
foreach ($aResult as $row) {
if ($v['revenue_type'] == MAX_FINANCE_CPM) {
$type = 'CPM';
$what = 'clicks and/or conversions';
} else {
$type = 'CPC';
$what = 'conversions';
}
$message = "campaign [id{$row['campaignid']}] is {$type} but has booked {$what} set";
if (!$warning) {
$warning = "Warning: the revenue type of some campaigns doesn't match the campaign targets. For example, {$message}. Check the install.log for the detailed campaign list.";
$this->_logWarning($warning);
}
$this->_logOnly('Revenue type mismatch: ' . $message);
}
}
return true;
}
/**
* Tests creating/dropping all of the MPE temporary tables.
*
* Requirements:
* Test 1: Test that all MPE temporary tables can be created and dropped.
*/
function testAllMaintenanceStatisticsTables()
{
$tmpTables = array('tmp_ad_impression', 'tmp_ad_click', 'tmp_tracker_impression_ad_impression_connection', 'tmp_tracker_impression_ad_click_connection', 'tmp_ad_connection');
// Test 1
$conf =& $GLOBALS['_MAX']['CONF'];
$conf['table']['prefix'] = '';
$oDbh =& OA_DB::singleton();
foreach ($tmpTables as $tableName) {
$query = "SELECT * FROM {$tableName}";
RV::disableErrorHandling();
$result = $oDbh->query($query);
RV::enableErrorHandling();
$this->assertEqual(strtolower(get_class($result)), 'mdb2_error');
}
$oTable =& OA_DB_Table_Statistics::singleton();
foreach ($tmpTables as $tableName) {
$oTable->createTable($tableName);
}
$aExistingTables = OA_DB_Table::listOATablesCaseSensitive();
foreach ($tmpTables as $tableName) {
// Test that the table has been created
$query = "SELECT * FROM {$tableName}";
$result = $oDbh->query($query);
$this->assertTrue($result);
// Test that the table can be dropped
// Use a different query to overcome MDB2 query buffering
$query = "SELECT foo FROM {$tableName}";
RV::disableErrorHandling();
$result = $oDbh->query($query);
RV::enableErrorHandling();
$this->assertEqual(strtolower(get_class($result)), 'mdb2_error');
}
// Restore the testing environment
TestEnv::restoreEnv();
}
function _execQuery($query)
{
RV::disableErrorHandling();
$result = $this->oDbh->exec($query);
RV::enableErrorHandling();
if (PEAR::isError($result)) {
return false;
}
return $result;
}
function parseLogFile()
{
$oDbh =& OA_DB::singleton();
RV::disableErrorHandling();
$fpsql = fopen(MAX_PATH . "/var/sql.log", 'r');
if (!$fpsql) {
$aResult[]['error'] = 'unable to open file ' . MAX_PATH . "/var/sql.log";
$aResult[]['error'] = 'to create ' . MAX_PATH . '/var/sql.log, trigger logging by setting [debug] logSQL="select|update|insert|delete (as appropriate) in your conf file.';
$aResult[]['error'] = 'running the devel explain utility also creates mysqlsla.log which can be fed to mysqlsla for analysis: mysqlsla --user <dbuser> --host <dbhost> --port <dbport> --te --sort e --raw mysqlsla.log > mysqlsla.txt';
$aResult[]['error'] = 'running the devel explain utility also creates mysqlqp.log which can be fed to mysql-query-profiler for analysis: mysql-query-profiler --user <dbuser> --host <dbhost> --port <dbport> --database <dbname> mysqlqp.log > mysqlqp.txt
';
return $aResult;
}
while ($v = fgets($fpsql, 4096)) {
$aQueries[] = $v;
}
fclose($fpsql);
$aQueries = array_unique($aQueries);
if (count($aQueries) > 1) {
// write a log for use by mysqlsla
$fpsla = fopen(MAX_PATH . "/var/mysqlsla.log", 'w');
fwrite($fpsla, "USE {$oDbh->connected_database_name};\n");
// write a log for use by mysql-query-profiler
$fpmqp = fopen(MAX_PATH . "/var/mysqlqp.log", 'w');
foreach ($aQueries as $k => $v) {
if (substr_count($v, 'tmp_') == 0) {
$i = preg_match('/((\\[(?P<type>[\\w]+)\\])(?P<query>[\\w\\W\\s]+))/', $v, $aMatches);
if ($i) {
$type = $aMatches['type'];
$query = trim($aMatches['query']);
$aResult[$k]['query'] = $query;
if ($type == 'prepare' || strpos($query, 'PREPARE MDB2_STATEMENT') === 0) {
$aResult[$k]['error'] = 'cannot execute statement: ' . $query;
} else {
$result = $oDbh->getAssoc('EXPLAIN ' . $query);
if (!PEAR::isError($result)) {
$aResult[$k]['result'] = $result;
fwrite($fpsla, $query . "\n");
fwrite($fpmqp, $query . "\n\n");
} else {
//$aResult[$k]['error'] = $result->getUserInfo();
$aResult[$k]['error'] = 'failed to explain statement: ' . $query;
}
}
}
}
}
fclose($fpsla);
fclose($fpmqp);
$aConf = $GLOBALS['_MAX']['CONF']['database'];
$cmd = "sudo /usr/local/sbin/mysqlsla --user {$aConf['username']} --host {$aConf['host']} --port {$aConf['port']} --time-each-query --sort e --top 50 --flush-qc --avg 10 --raw mysqlsla.log > mysqlsla.txt";
$fpsla = fopen(MAX_PATH . "/var/mysqlslarun", 'w');
fwrite($fpsla, $cmd);
fclose($fpsla);
$cmd = "mysql-query-profiler --user {$aConf['username']} --host {$aConf['host']} --port {$aConf['port']} --database {$aConf['name']} mysqlqp.log > mysqlqp.txt";
$fpmqp = fopen(MAX_PATH . "/var/mysqlqprun", 'w');
fwrite($fpmqp, $cmd);
fclose($fpmqp);
}
RV::enableErrorHandling();
return $aResult;
}
function tearDown()
{
RV::enableErrorHandling();
}
/**
* A method to search for any aligned hours in the data_intermediate_ad and
* data_summary_ad_hourly tables where the number of requests, impressions,
* clicks or conversions do not agree with each other, and, where any such
* hours are found, to search these hours for any cases of the
* data_summary_ad_hourly table containing fewer requests, impressions,
* clicks or conversions that the data_intermediate_ad table, and where
* these cases are found, to update the data_summary_ad_hourly to match
* the values found in the data_intermediate_ad table.
*
* @param Date $oStartDate The start date/time of the range to operate on.
* @param Date $oEndDate The end date/time of the farnce to operate on.
*/
function issueTwo($oStartDate, $oEndDate)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$sQuery = "\n SELECT\n t1.hour_date_time AS date_time,\n t1.requests AS intermediate_requests,\n t2.requests AS summary_requests,\n t1.impressions AS intermediate_impressions,\n t2.impressions AS summary_impressions,\n t1.clicks AS intermediate_clicks,\n t2.clicks AS summary_clicks,\n t1.conversions AS intermediate_conversions,\n t2.conversions AS summary_conversions\n FROM\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_intermediate_ad\n WHERE\n date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n GROUP BY\n hour_date_time\n ) AS t1,\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_summary_ad_hourly\n WHERE\n date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n GROUP BY\n hour_date_time\n ) AS t2\n WHERE\n t1.hour_date_time = t2.hour_date_time\n HAVING\n intermediate_requests > summary_requests\n OR\n intermediate_impressions > summary_impressions\n OR\n intermediate_clicks > summary_clicks\n OR\n intermediate_conversions > summary_conversions\n ORDER BY\n date_time";
RV::disableErrorHandling();
$rsResult = $this->oDbh->query($sQuery);
RV::enableErrorHandling();
if (PEAR::isError($rsResult)) {
$message = "\n Database error while searching for invalid data:\n " . $rsResult->getMessage() . "\n Cannot detect issues, so will not attepmt any corrections.\n";
echo $message;
return;
}
$rows = $rsResult->numRows();
if ($rows == 0) {
$message = "\n Did not detect any issues; no statistics data correction required.\n";
echo $message;
return;
}
$message = "\n Detected {$rows} operation intervals that need further inspection...\n";
echo $message;
while ($aRow = $rsResult->fetchRow()) {
$message = " Inspecting operation interval {$aRow['date_time']}...\n";
echo $message;
$message = " Correcting...\n";
echo $message;
$oDate = new Date($aRow['date_time']);
$sInnerQuery = "\n SELECT\n t1.hour_date_time AS date_time,\n t1.ad_id AS ad_id,\n t1.zone_id AS zone_id,\n t1.requests AS intermediate_requests,\n t2.requests AS summary_requests,\n t1.impressions AS intermediate_impressions,\n t2.impressions AS summary_impressions,\n t1.clicks AS intermediate_clicks,\n t2.clicks AS summary_clicks,\n t1.conversions AS intermediate_conversions,\n t2.conversions AS summary_conversions\n FROM\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n ad_id AS ad_id,\n zone_id AS zone_id,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_intermediate_ad\n WHERE\n date_time >= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:00:00'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:59:59'), 'timestamp') . "\n GROUP BY\n hour_date_time, ad_id, zone_id\n ) AS t1,\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n ad_id AS ad_id,\n zone_id AS zone_id,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_summary_ad_hourly\n WHERE\n date_time >= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:00:00'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:59:59'), 'timestamp') . "\n GROUP BY\n hour_date_time, ad_id, zone_id\n ) AS t2\n WHERE\n t1.hour_date_time = t2.hour_date_time\n AND\n t1.ad_id = t2.ad_id\n AND\n t1.zone_id = t2.zone_id\n HAVING\n intermediate_requests > summary_requests\n OR\n intermediate_impressions > summary_impressions\n OR\n intermediate_clicks > summary_clicks\n OR\n intermediate_conversions > summary_conversions\n ORDER BY\n date_time, ad_id, zone_id";
RV::disableErrorHandling();
$rsInnerResult = $this->oDbh->query($sInnerQuery);
RV::enableErrorHandling();
if (PEAR::isError($rsInnerResult)) {
$message = " Error while selecting unsummarised rows, please re-run script later!\n";
echo $message;
continue;
}
while ($aInnerRow = $rsInnerResult->fetchRow()) {
$message = " Correcting data for '{$aRow['date_time']}', Creative ID '{$aInnerRow['ad_id']}', Zone ID '{$aInnerRow['zone_id']}'...\n";
echo $message;
$sUpdateQuery = "\n UPDATE\n {$aConf['table']['prefix']}data_summary_ad_hourly\n SET\n requests = " . $this->oDbh->quote($aInnerRow['intermediate_requests'], 'integer') . ",\n impressions = " . $this->oDbh->quote($aInnerRow['intermediate_impressions'], 'integer') . ",\n clicks = " . $this->oDbh->quote($aInnerRow['intermediate_clicks'], 'integer') . ",\n conversions = " . $this->oDbh->quote($aInnerRow['intermediate_conversions'], 'integer') . "\n WHERE\n date_time = " . $this->oDbh->quote($aInnerRow['date_time'], 'timestamp') . "\n AND\n ad_id = " . $this->oDbh->quote($aInnerRow['ad_id'], 'integer') . "\n AND\n zone_id = " . $this->oDbh->quote($aInnerRow['zone_id'], 'integer') . "\n AND\n requests = " . $this->oDbh->quote($aInnerRow['summary_requests'], 'integer') . "\n AND\n impressions = " . $this->oDbh->quote($aInnerRow['summary_impressions'], 'integer') . "\n AND\n clicks = " . $this->oDbh->quote($aInnerRow['summary_clicks'], 'integer') . "\n AND\n conversions = " . $this->oDbh->quote($aInnerRow['summary_conversions'], 'integer') . "\n LIMIT 1";
if (defined('DEBUG_ONLY')) {
$message = " Running in debug mode only, if running correctly, the following would have been performed:\n";
echo $message;
$message = $sUpdateQuery;
$message = preg_replace('/\\n/', '', $message);
$message = preg_replace('/^ +/', '', $message);
$message = preg_replace('/ +/', ' ', $message);
$message = wordwrap($message, 75, "\n ");
$message = " " . $message . ";\n";
echo $message;
} else {
RV::disableErrorHandling();
$rsUpdateResult = $this->oDbh->exec($sUpdateQuery);
RV::enableErrorHandling();
if (PEAR::isError($rsUpdateResult)) {
$message = " Error while updating an incomplete row, please re-run script later!\n";
echo $message;
continue;
}
if ($rsUpdateResult > 1) {
$message = " Error: More than one incomplete row updated, please manually inspect data!\n once the script has completed running!\n";
echo $message;
continue;
}
$message = " Updated {$rsUpdateResult} incomplete row.\n";
echo $message;
}
if (defined('DEBUG_ONLY')) {
sleep(1);
}
}
}
}
/**
* Performs a batch insert using plain INSERTs
*
* @see OA_Dal::batchInsert()
*
* @param string $tableName The unquoted table name
* @param array $aFields The array of unquoted field names
* @param array $aValues The array of data to be inserted
* @param bool $replace Should the primary key be replaced when already present?
* @return int The number of rows inserted or PEAR_Error on failure
*/
private function _batchInsertPgSQL($qTableName, $fieldList, $aValues, $replace, $primaryKey)
{
$oDbh = OA_DB::singleton();
$delim = "\t";
$eol = "\n";
$null = '\\N';
// Disable error handler
RV::disableErrorHandling();
// we start by manually deleting conflicting unique rows
foreach ($aValues as $aRow) {
// because Postgresql doesn't have the REPLACE keyword,
// we manually delete the rows with the primary key first
if ($replace) {
$where = '';
foreach ($primaryKey as $fieldName) {
$where .= $fieldName . ' = \'' . $aRow[$fieldName] . '\' AND ';
}
$where = substr($where, 0, strlen($where) - 5);
$oDbh->query('DELETE FROM ' . $qTableName . ' WHERE ' . $where);
}
}
$pg = $oDbh->getConnection();
$result = $oDbh->exec("\n COPY\n {$qTableName} {$fieldList}\n FROM\n STDIN\n ");
if (PEAR::isError($result)) {
return MAX::raiseError('Error issuing the COPY query for the batch INSERTs.', PEAR_ERROR_RETURN);
}
foreach ($aValues as $aRow) {
// Stringify row
$row = '';
foreach ($aRow as $value) {
if (!isset($value) || $value === false) {
$row .= $null . $delim;
} else {
$row .= $value . $delim;
}
}
// Replace delim with eol
$row[strlen($row) - 1] = $eol;
// Send line
$ret = pg_put_line($pg, $row);
if (!$ret) {
return MAX::raiseError('Error COPY-ing data: ' . pg_errormessage($pg), PEAR_ERROR_RETURN);
}
}
$result = pg_put_line($pg, '\\.' . $eol) && pg_end_copy($pg);
$result = $result ? count($aValues) : new PEAR_Error('Error at the end of the COPY: ' . pg_errormessage($pg));
// Enable error handler again
RV::enableErrorHandling();
return $result;
}
function _assignUserAccountInfo($oCurrentSection)
{
global $session;
// Show currently logged on user and IP
if (OA_Auth::isLoggedIn() || defined('phpAds_installing')) {
$this->oTpl->assign('helpLink', OA_Admin_Help::getHelpLink($oCurrentSection));
if (!defined('phpAds_installing')) {
$this->oTpl->assign('infoUser', OA_Permission::getUsername());
$this->oTpl->assign('buttonLogout', true);
$this->oTpl->assign('buttonSupport', true);
$aAppConfig = RV::getAppConfig();
if ($aAppConfig['ui']['supportLink']) {
$this->oTpl->assign('supportLink', $aAppConfig['ui']['supportLink']);
} else {
$this->oTpl->assign('supportLink', 'http://www.revive-adserver.com/support/');
}
// Account switcher
OA_Admin_UI_AccountSwitch::assignModel($this->oTpl);
$this->oTpl->assign('strWorkingAs', $GLOBALS['strWorkingAs_Key']);
$this->oTpl->assign('keyWorkingAs', $GLOBALS['keyWorkingAs']);
$this->oTpl->assign('accountId', OA_Permission::getAccountId());
$this->oTpl->assign('accountName', OA_Permission::getAccountName());
$this->oTpl->assign('accountSearchUrl', MAX::constructURL(MAX_URL_ADMIN, 'account-switch-search.php'));
$this->oTpl->assign('productUpdatesCheck', OA_Permission::isAccount(OA_ACCOUNT_ADMIN) && $GLOBALS['_MAX']['CONF']['sync']['checkForUpdates'] && !isset($session['maint_update_js']));
if (OA_Permission::isUserLinkedToAdmin()) {
$this->oTpl->assign('maintenanceAlert', OA_Dal_Maintenance_UI::alertNeeded());
}
} else {
$this->oTpl->assign('buttonStartOver', true);
}
}
}
/**
* A method to test the getProcessLastRunInfo() method.
*
* Requirements:
* Test 1: Test with invalid data, and ensure false is returned.
* Test 2: Test with no data in the database and ensure null is returned.
* Test 3: Test with bad table and column names, and ensure false is returned.
* Test 4: Test that the correct values are returned from data_ tables.
* Test 5: Test that the correct values are returned from log_ tables.
*/
function testGetProcessLastRunInfo()
{
$aConf =& $GLOBALS['_MAX']['CONF'];
$oDbh =& OA_DB::singleton();
$log_maintenance_priority = $aConf['table']['prefix'] . $aConf['table']['log_maintenance_priority'];
$data_raw_ad_impression = $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_impression'];
$oDalMaintenanceCommon = new OA_Dal_Maintenance_Common();
// Test 1
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], 'foo', null, 'start_run', array());
$this->assertFalse($result);
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array(), null, 'start_run', 'foo');
$this->assertFalse($result);
// Test 2
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority']);
$this->assertNull($result);
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array(), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertNull($result);
// Test 3
RV::disableErrorHandling();
$result = $oDalMaintenanceCommon->getProcessLastRunInfo('foo', array(), null, 'start_run', array());
$this->assertFalse($result);
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('foo'), null, 'start_run', array());
$this->assertFalse($result);
$result = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array(), null, 'start_run', array('tableName' => 'foo', 'type' => 'hour'));
$this->assertFalse($result);
RV::enableErrorHandling();
// Test 4
$query = "\n INSERT INTO\n " . $oDbh->quoteIdentifier($data_raw_ad_impression, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2006-10-06 08:53:42',\n 1,\n 1,\n 1\n )";
$rows = $oDbh->exec($query);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 07:59:59');
$query = "\n INSERT INTO\n " . $oDbh->quoteIdentifier($data_raw_ad_impression, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2006-10-06 09:53:42',\n 1,\n 1,\n 1\n )";
$rows = $oDbh->exec($query);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 07:59:59');
$aConf['maintenance']['operationInterval'] = 60;
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 07:59:59');
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'oi'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 07:59:59');
$aConf['maintenance']['operationInterval'] = 30;
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 07:59:59');
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'oi'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 1);
$this->assertEqual($aResult['updated_to'], '2006-10-06 08:29:59');
TestEnv::restoreConfig();
TestEnv::restoreEnv();
// Test 5
$query = "\n INSERT INTO\n " . $oDbh->quoteIdentifier($log_maintenance_priority, true) . "\n (\n start_run,\n end_run,\n operation_interval,\n duration,\n run_type,\n updated_to\n )\n VALUES\n (\n '2006-10-06 12:07:01',\n '2006-10-06 12:10:01',\n 60,\n 180,\n 1,\n '2006-10-06 11:59:59'\n )";
$rows = $oDbh->exec($query);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval', 'run_type'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 3);
$this->assertEqual($aResult['updated_to'], '2006-10-06 11:59:59');
$this->assertEqual($aResult['operation_interval'], 60);
$this->assertEqual($aResult['run_type'], 1);
$query = "\n INSERT INTO\n " . $oDbh->quoteIdentifier($log_maintenance_priority, true) . "\n (\n start_run,\n end_run,\n operation_interval,\n duration,\n run_type,\n updated_to\n )\n VALUES\n (\n '2006-10-06 11:07:01',\n '2006-10-06 11:10:01',\n 60,\n 180,\n 0,\n '2006-10-06 20:59:59'\n )";
$rows = $oDbh->exec($query);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval', 'run_type'), null, 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 3);
$this->assertEqual($aResult['updated_to'], '2006-10-06 11:59:59');
$this->assertEqual($aResult['operation_interval'], 60);
$this->assertEqual($aResult['run_type'], 1);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval', 'run_type'), null, 'updated_to', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 3);
$this->assertEqual($aResult['updated_to'], '2006-10-06 20:59:59');
$this->assertEqual($aResult['operation_interval'], 60);
$this->assertEqual($aResult['run_type'], 0);
$aResult = $oDalMaintenanceCommon->getProcessLastRunInfo($aConf['table']['log_maintenance_priority'], array('operation_interval', 'run_type'), 'WHERE run_type = 0', 'start_run', array('tableName' => $aConf['table']['data_raw_ad_impression'], 'type' => 'hour'));
$this->assertTrue(is_array($aResult));
$this->assertEqual(count($aResult), 3);
$this->assertEqual($aResult['updated_to'], '2006-10-06 20:59:59');
$this->assertEqual($aResult['operation_interval'], 60);
$this->assertEqual($aResult['run_type'], 0);
TestEnv::restoreEnv();
}
/**
* Method to test function validateDatabaseName in MDB2 Manager modules
*/
function testValidateTableName()
{
$aConf = $GLOBALS['_MAX']['CONF'];
RV::disableErrorHandling();
$vals = array(0, 32, 33, 34, 35, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 58, 59, 60, 61, 62, 63, 64, 91, 92, 93, 94, 96, 123, 124, 125, 126, 156, 255);
//$pattern = '';
foreach ($vals as $i) {
//$pattern.= '\\x'.dechex($i);
$result = OA_DB::validateTableName('o' . chr($i) . '_table');
$this->assertTrue(PEAR::isError($result), 'chr(' . $i . ') /' . dechex($i));
}
if ($aConf['database']['type'] == 'mysql') {
$result = OA_DB::validateTableName('abcdefghij1234567890123456789012345678901234567890123456789012345');
//65 chars
$this->assertTrue(PEAR::isError($result));
$this->assertTrue(OA_DB::validateTableName('abcdefghij123456789012345678901234567890123456789012345678901234'));
//64 chars
$this->assertTrue(OA_DB::validateTableName('aBcDeFgHiJkLmNoPqRsTuVwXyZ_$1234567890'));
$result = OA_DB::validateTableName('2_$');
$this->assertFalse(PEAR::isError($result));
$result = OA_DB::validateTableName('$_2');
$this->assertFalse(PEAR::isError($result));
$result = OA_DB::validateTableName('_$2');
$this->assertFalse(PEAR::isError($result));
}
if ($aConf['database']['type'] == 'pgsql') {
$result = OA_DB::validateTableName('abcdefghij123456789012345678901234567890123456789012345678901234');
//64 chars
$this->assertTrue(PEAR::isError($result));
$this->assertTrue(OA_DB::validateTableName('abcdefghij12345678901234567890123456789012345678901234567890123'));
//63 chars
$result = OA_DB::validateTableName('0x_table');
$this->assertTrue(PEAR::isError($result));
$result = OA_DB::validateTableName(' x_table');
$this->assertTrue(PEAR::isError($result));
$this->assertTrue(OA_DB::validateTableName('aBcDeFgHiJkLmNoPqRsTuVwXyZ_$1234567890'));
$result = OA_DB::validateTableName('2_$');
$this->assertTrue(PEAR::isError($result));
$result = OA_DB::validateTableName('$_2');
$this->assertTrue(PEAR::isError($result));
$result = OA_DB::validateTableName('_$2');
$this->assertFalse(PEAR::isError($result));
}
RV::enableErrorHandling();
}
