function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$prefix = $aConf['prefix'];
foreach (array('tblAppVar' => 'application_variable', 'tblAccounts' => 'accounts', 'tblAgency' => 'agency', 'tblClients' => 'clients', 'tblCampaigns' => 'campaigns', 'tblBanners' => 'banners', 'tblAcls' => 'acls', 'tblPrefs' => 'preferences', 'tblAccPrefs' => 'account_preference_assoc') as $k => $v) {
${$k} = $this->oDbh->quoteIdentifier($prefix . ($aConf[$v] ? $aConf[$v] : $v), true);
}
// Get admin account ID
$adminAccountId = (int) $this->oDbh->queryOne("SELECT value FROM {$tblAppVar} WHERE name = 'admin_account_id'");
if (PEAR::isError($adminAccountId)) {
$this->logError("No admin account ID");
return false;
}
// Get preference ID for timezone
$tzId = $this->oDbh->queryOne("SELECT preference_id FROM {$tblPrefs} WHERE preference_name = 'timezone'");
if (empty($tzId) || PEAR::isError($tzId)) {
// Upgrading from 2.4 maybe?
$tzId = 0;
$this->logOnly("No timezone preference available, using default server timezone");
$adminTz = date_default_timezone_get();
if (empty($adminTz)) {
// C'mon you should have set the timezone in your php.ini!
$this->logOnly("No default server timezone, using UTC");
$adminTz = 'UTC';
}
} else {
// Get admin timezone
$adminTz = $this->oDbh->queryOne("SELECT value FROM {$tblAccPrefs} WHERE preference_id = {$tzId} AND account_id = {$adminAccountId}");
if (empty($adminTz) || PEAR::isError($adminTz)) {
$this->logOnly("No admin timezone, using UTC");
$adminTz = 'UTC';
}
}
$joinList = "{$tblBanners} b JOIN\n {$tblCampaigns} ca USING (campaignid) JOIN\n {$tblClients} cl USING (clientid) JOIN\n {$tblAgency} a USING (agencyid) LEFT JOIN\n {$tblAccPrefs} p ON (p.account_id = a.account_id AND p.preference_id = {$tzId})";
$tzPart = "COALESCE(p.value, " . $this->oDbh->quote($adminTz) . ")";
$wherePart = "\n ac.bannerid = b.bannerid AND\n \tac.type LIKE 'deliveryLimitations:Time:%' AND\n \tac.data NOT LIKE '%@%'\n ";
if ($this->oDbh->dbsyntax == 'pgsql') {
$query = "\n UPDATE\n {$tblAcls} ac\n SET\n data = data || '@' || {$tzPart}\n FROM\n {$joinList}\n WHERE\n {$wherePart}\n ";
} else {
$query = "\n UPDATE\n {$tblAcls} ac,\n {$joinList}\n SET\n ac.data = CONCAT(ac.data, '@', {$tzPart})\n WHERE\n {$wherePart}\n ";
}
$ret = $this->oDbh->exec($query);
if (PEAR::isError($ret)) {
$this->logError($ret->getUserInfo());
return false;
}
// Rebuild ACLs
$this->oUpgrade->addPostUpgradeTask('Recompile_Acls');
// Also rebuild banner cache for OX-5184
$this->oUpgrade->addPostUpgradeTask('Rebuild_Banner_Cache');
$this->logOnly("Appended timezone information to {$ret} time based delivery limitations");
return true;
}
/**
* Build global statistics array to be sent through Sync
*
* @return array
*/
function buildStats()
{
$lastRun = OA_Dal_ApplicationVariables::get('sync_last_run');
if ($lastRun) {
$oStart = new Date($lastRun);
} else {
$oStart = new Date();
$oStart->subtractSpan(new Date_Span('1-0-0-0'));
}
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date();
$oEnd->setMinute(0);
$oEnd->setSecond(0);
$doDsah = OA_Dal::factoryDO('data_summary_ad_hourly');
$doDsah->selectAdd();
$doDsah->selectAdd('date_time');
$doDsah->selectAdd('SUM(impressions) AS total_impressions');
$doDsah->selectAdd('SUM(clicks) AS total_clicks');
$doDsah->whereAdd("date_time >= " . $this->oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S')));
$doDsah->whereAdd("date_time < " . $this->oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S')));
$doDsah->groupBy('date_time');
$doDsah->orderBy('date_time');
$doDsah->find();
$aStats = array();
while ($doDsah->fetch()) {
$row = $doDsah->toArray();
$aStats[$row['date_time']] = array('impressions' => $row['total_impressions'], 'clicks' => $row['total_clicks']);
}
return $aStats;
}
function getLinkedSequence($table, $field_name)
{
$query = "SELECT\n (SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)\n FROM pg_attrdef d\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default\n FROM pg_attribute a, pg_class c\n WHERE c.relname = " . $this->oDbh->quote($table, 'text') . "\n AND c.oid = a.attrelid\n AND NOT a.attisdropped\n AND a.attnum > 0\n AND a.attname = " . $this->oDbh->quote($field_name, 'text') . "\n ORDER BY a.attnum";
$column = $this->oDbh->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
if (!PEAR::isError($column)) {
if (preg_match('/nextval\\(\'(.*?)\'/', $column['default'], $m)) {
return $m[1];
}
}
return false;
}
function getSqlListOrder($listOrder, $orderDirection)
{
$direction = $this->getOrderDirection($this->oDbh->quote($orderDirection, 'text'));
$nameColumn = $this->getOrderColumn($this->oDbh->quote($listOrder, 'text'));
if (is_array($nameColumn)) {
$sqlTableOrder = ' ORDER BY ' . implode($direction . ',', $nameColumn) . $direction;
} else {
$sqlTableOrder = !empty($nameColumn) ? " ORDER BY {$nameColumn} {$direction}" : '';
}
return $sqlTableOrder;
}
private function getSubTagWhereClause()
{
$photo_id_sql = 'select PinholePhoto.id from PinholePhoto
inner join ImageSet on PinholePhoto.image_set = ImageSet.id';
$join_clauses = implode(' ', $this->getJoinClauses());
if ($join_clauses != '') {
$photo_id_sql .= ' ' . $join_clauses . ' ';
}
$where_clause = $this->getWhereClause();
if ($where_clause != '') {
$photo_id_sql .= ' where ' . $where_clause;
}
$sql = sprintf('PinholeTag.id in
(select tag from PinholePhotoTagBinding where photo in
(%s))', $photo_id_sql);
if ($this->app->getInstance() !== null) {
$sql .= sprintf(' and PinholeTag.instance = %s', $this->db->quote($this->app->getInstanceId(), 'integer'));
}
return $sql;
}
/**
* Efficiently loads tags for a set of posts
*
* @param BlorgPostWrapper $posts the posts for which to efficiently load
* tags.
*/
protected function loadPostTags(BlorgPostWrapper $posts)
{
$instance_id = $this->instance === null ? null : $this->instance->id;
$wrapper = SwatDBClassMap::get('BlorgTagWrapper');
// get post ids
$post_ids = array();
foreach ($posts as $post) {
$post_ids[] = $post->id;
}
$post_ids = $this->db->implodeArray($post_ids, 'integer');
// build SQL to select all tags
$sql = sprintf('select BlorgTag.*, BlorgPostTagBinding.post
from BlorgTag
inner join BlorgPostTagBinding on
BlorgTag.id = BlorgPostTagBinding.tag
where post in (%s) and BlorgTag.instance %s %s
order by post, createdate desc', $post_ids, SwatDB::equalityOperator($instance_id), $this->db->quote($instance_id, 'integer'));
// get all tags
$tags = SwatDB::query($this->db, $sql, $wrapper);
// assign empty recordsets for all posts
foreach ($posts as $post) {
$recordset = new $wrapper();
$post->setTags($recordset);
}
// assign tags to correct posts
$current_post_id = null;
$current_recordset = null;
foreach ($tags as $tag) {
$post_id = $tag->getInternalValue('post');
if ($post_id !== $current_post_id) {
$current_post_id = $post_id;
$current_recordset = $posts[$post_id]->getTags();
}
$current_recordset->add($tag);
}
}
/**
* Removes a document type
*
* After a document type is removed, documents of that type can no longer
* be indexed or queried.
*
* @param MDB2_Driver_Common $db the database driver to use to remove the
* document type identifier.
* @param string $type_shortname the shortname of the document type to
* remove.
*
* @throws NateGoSearchDBException if a database error occurs.
*/
public static function removeDocumentType(MDB2_Driver_Common $db, $type_shortname)
{
$type_shortname = (string) $type_shortname;
$sql = sprintf('delete from NateGoSearchType where shortname = %s', $db->quote($type_shortname, 'text'));
$result = $db->exec($sql);
if (MDB2::isError($result)) {
throw new NateGoSearchDBException($result);
}
}
/**
* Clears this search index
*
* The index is cleared for this indexer's document type
*
* @see NateGoSearchIndexer::__construct()
*
* @throws NateGoSearchDBException if a database error occurs.
*/
protected function clear()
{
$sql = sprintf('delete from NateGoSearchIndex where document_type = %s', $this->db->quote($this->document_type, 'integer'));
$result = $this->db->exec($sql);
if (MDB2::isError($result)) {
throw new NateGoSearchDBException($result);
}
}
/**
* 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";
OX::disableErrorHandling();
$rsResult = $this->oDbh->query($sQuery);
OX::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";
OX::disableErrorHandling();
$rsInnerResult = $this->oDbh->query($sInnerQuery);
OX::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 {
OX::disableErrorHandling();
$rsUpdateResult = $this->oDbh->exec($sUpdateQuery);
OX::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);
}
}
}
}
/**
* Get a list of popular/successful search keywords
*
* This is used to query the database for a list of keywords from the
* NateGoSearchHistory table. The results are based upon the document_count
* of each of the keywords and if the words have been searched recently.
*
* @param MDB2_Driver_Common $db the database driver to use.
* @param integer $document_threshold optional. The minimum number of
* results in which a word must be
* contained to be considered
* popular. If not specified, 150
* is used.
* @param string $date_threshold optional. Search keywords must be after
* this date to be considered popular. Uses
* strtotime format. If not specified,
* '6 months ago' is used.
*
* @return array an array of popular search words
*/
public static function getSearchHistoryPopularWords(MDB2_Driver_Common $db, $document_threshold = 150, $date_threshold = '6 months ago')
{
$date = strtotime($date_threshold);
$date = date('c', $date);
$sql = sprintf('select distinct keywords from NateGoSearchHistory
where document_count > %s and creation_date > %s', $db->quote($document_threshold, 'integer'), $db->quote($date, 'date'));
$words = $db->queryCol($sql, 'text');
if (MDB2::isError($words)) {
throw new NateGoSearchDBException($words);
}
return $words;
}
