/** * A method to test when there are no old format raw requests, * impressions and clicks. */ function testNoData() { $aConf =& $GLOBALS['_MAX']['CONF']; $aConf['maintenance']['operationInterval'] = 60; // Prepare an array of the required tables used in testing $aTables = array($aConf['table']['prefix'] . $aConf['table']['data_raw_ad_request'] => $aConf['table']['prefix'] . 'data_bkt_r', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_impression'] => $aConf['table']['prefix'] . 'data_bkt_m', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_click'] => $aConf['table']['prefix'] . 'data_bkt_c'); // Install the openXDeliveryLog plugin, which will create the // data bucket tables required for testing TestEnv::installPluginPackage('openXDeliveryLog', false); // Ensure that there are no old format raw data foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 0); } // Ensure that there are no new format bucket data foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 0); } // Run the migration of raw data DAL code for a given OI $oStart = new Date('2009-01-09 12:00:00'); $oEnd = new Date('2009-01-09 12:59:59'); $this->oDal->migrateRawRequests($oStart, $oEnd); $this->oDal->migrateRawImpressions($oStart, $oEnd); $this->oDal->migrateRawClicks($oStart, $oEnd); // Re-test that there are still no new format bucket data foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 0); } // Uninstall the installed plugin TestEnv::uninstallPluginPackage('openXDeliveryLog', false); // Restore the test environment configuration TestEnv::restoreConfig(); }
function execute($aParams) { $this->oUpgrade =& $aParams[0]; $this->oDbh =& OA_DB::singleton(); $aConf = $GLOBALS['_MAX']['CONF']['table']; $this->prefix = $aConf['prefix']; $this->tblPreferences = $aConf['prefix'] . ($aConf['preferences'] ? $aConf['preferences'] : 'preferences'); $query = "INSERT INTO " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . "\n (preference_name, account_type)\n VALUES('campaign_ecpm_enabled', 'MANAGER')"; $ret = $this->oDbh->query($query); //check for error if (PEAR::isError($ret)) { $this->logError($ret->getUserInfo()); return false; } $this->logOnly("Added 'campaign_ecpm_enabled' preference to 'MANAGER' account"); return true; }
function execute($aParams) { $this->oUpgrade =& $aParams[0]; // Recompile the delivery limitations to update the compiled limitations as well $this->oUpgrade->addPostUpgradeTask('Recompile_Acls'); $this->oDbh =& OA_DB::singleton(); $aConf = $GLOBALS['_MAX']['CONF']['table']; $this->prefix = $aConf['prefix']; $this->tblPreferences = $aConf['prefix'] . ($aConf['preferences'] ? $aConf['preferences'] : 'preferences'); $this->tblAccountPreferenceAssoc = $aConf['prefix'] . ($aConf['account_preference_assoc'] ? $aConf['account_preference_assoc'] : 'account_preference_assoc'); $query = "SELECT preference_id\n FROM " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . "\n WHERE preference_name = 'auto_alter_html_banners_for_click_tracking'"; $rs = $this->oDbh->query($query); //check for error if (PEAR::isError($rs)) { $this->logError($rs->getUserInfo()); return false; } $preferenceId = $rs->fetchRow(MDB2_FETCHMODE_ASSOC); $preferenceId = $preferenceId['preference_id']; if (!empty($preferenceId)) { $sql = "DELETE FROM " . $this->oDbh->quoteIdentifier($this->tblAccountPreferenceAssoc, true) . " WHERE preference_id = {$preferenceId}"; $rs = $this->oDbh->exec($sql); //check for error if (PEAR::isError($rs)) { $this->logError($rs->getUserInfo()); return false; } $this->logOnly("Removed entries in account_preferences_assoc table related to auto_alter_html_banners_for_click_tracking"); $sql = "DELETE FROM " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . " WHERE preference_id = {$preferenceId}"; $rs = $this->oDbh->exec($sql); //check for error if (PEAR::isError($rs)) { $this->logError($rs->getUserInfo()); return false; } $this->logOnly("Removed auto_alter_html_banners_for_click_tracking preference in preferences table"); } return true; }
/** * Update the "Administrator Account" account to "System Administrator", if * it still exists for the user, for improved understanding of the account * purpose */ function _updateAdministratorAccountToSystemAdministrator() { $this->oDbh =& OA_DB::singleton(); $aConf = $GLOBALS['_MAX']['CONF']['table']; $this->logOnly("Attempting to rename the 'Administrator Account' account to 'System Administrator' in the 'accounts' table"); $tblAccounts = $aConf['prefix'] . ($aConf['accounts'] ? $aConf['accounts'] : 'accounts'); $query = "UPDATE " . $this->oDbh->quoteIdentifier($tblAccounts, true) . " SET account_name = 'System Administrator' WHERE account_name = 'Administrator account'"; $result = $this->oDbh->query($query); if (!PEAR::isError($result)) { $this->logOnly("Renamed the old 'Administrator Account' account in the 'accounts' table"); } else { $this->logError("Failed to rename the old 'Administrator Account' account in the 'accounts' table"); } }
/** * A private method to perform assertions on the contents of the * log_maintenance_priority table. * * @access private * @param integer $id Optional row ID to test on, if not set, tests * that table is empty. * @param PEAR::Date $oBeforeUpdateDate The before date to test the row with. * @param PEAR::Date $oAfterUpdateDate The after date to test the row with. * @param integer $oi The operation interval to test the row with. * @param integer $runType The run type value to test the row with. * @param string $updatedTo The updated to date to test the row with, if any. */ function _assertLogMaintenance($id = null, $oBeforeUpdateDate = null, $oAfterUpdateDate = null, $oi = null, $runType = null, $updatedTo = null) { $aConf = $GLOBALS['_MAX']['CONF']; $tableName = $aConf['table']['prefix'] . 'log_maintenance_priority'; $table = $this->oDbh->quoteIdentifier($tableName, true); $query = "\n SELECT\n start_run,\n end_run,\n operation_interval,\n run_type,\n updated_to\n FROM\n {$table}"; if (!is_null($id)) { $query .= "\n WHERE\n log_maintenance_priority_id = {$id}"; } $rc = $this->oDbh->query($query); $aRow = $rc->fetchRow(); if (is_null($id)) { // Check there are no rows returned $this->assertNull($aRow); } else { // Check the returned row's values $oStartRunDate = new Date($aRow['start_run']); $oEndRunDate = new Date($aRow['end_run']); $result = $oBeforeUpdateDate->before($oStartRunDate); $this->assertTrue($result); $result = $oBeforeUpdateDate->before($oEndRunDate); $this->assertTrue($result); $result = $oAfterUpdateDate->after($oStartRunDate); $this->assertTrue($result); $result = $oAfterUpdateDate->after($oEndRunDate); $this->assertTrue($result); $result = $oStartRunDate->after($oEndRunDate); $this->assertFalse($result); $this->assertEqual($aRow['operation_interval'], $oi); $this->assertEqual($aRow['run_type'], $runType); if (!is_null($updatedTo)) { $this->assertEqual($aRow['updated_to'], $updatedTo); } else { $this->assertNull($aRow['updated_to']); } } }
/** * 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); } } } }
/** * A method to test when there are old format raw requests, * impressions and clicks, in the operation interval being * migrated. */ function testComplexValidData() { $aConf =& $GLOBALS['_MAX']['CONF']; $aConf['maintenance']['operationInterval'] = 60; // Prepare an array of the required tables used in testing $aTables = array($aConf['table']['prefix'] . $aConf['table']['data_raw_ad_request'] => $aConf['table']['prefix'] . 'data_bkt_r', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_impression'] => $aConf['table']['prefix'] . 'data_bkt_m', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_click'] => $aConf['table']['prefix'] . 'data_bkt_c'); // Install the openXDeliveryLog plugin, which will create the // data bucket tables required for testing TestEnv::installPluginPackage('openXDeliveryLog', false); // Ensure that there are no old format raw data foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 0); } // Ensure that there are no new format bucket data foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 0); } // Insert some old style raw data in an operation interval // that will not be migrated foreach ($aTables as $rawTable => $bucketTable) { $query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($rawTable, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2009-01-09 11:30:00',\n 1,\n 0,\n 1\n )"; $this->oDbh->exec($query); } // Ensure that the old format raw data was inserted correctly foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 1); } // Insert some old style raw data in an operation interval // that will be migrated foreach ($aTables as $rawTable => $bucketTable) { $query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($rawTable, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2009-01-09 12:30:00',\n 1,\n 0,\n 1\n )"; $this->oDbh->exec($query); $query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($rawTable, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2009-01-09 12:31:00',\n 1,\n 0,\n 1\n )"; $this->oDbh->exec($query); $query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($rawTable, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2009-01-09 12:32:00',\n 2,\n 0,\n 3\n )"; $this->oDbh->exec($query); } // Ensure that the old format raw data was inserted correctly foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 4); } // Run the migration of raw data DAL code for a given OI $oStart = new Date('2009-01-09 12:00:00'); $oEnd = new Date('2009-01-09 12:59:59'); $this->oDal->migrateRawRequests($oStart, $oEnd); $this->oDal->migrateRawImpressions($oStart, $oEnd); $this->oDal->migrateRawClicks($oStart, $oEnd); // Test that the data was migrated correctly foreach ($aTables as $rawTable => $bucketTable) { $query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['count'], 2); $query = "\n SELECT\n *\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true); $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 2); $query = "\n SELECT\n *\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true) . "\n WHERE\n creative_id = 1"; $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['interval_start'], '2009-01-09 12:00:00'); $this->assertEqual($aRow['creative_id'], 1); $this->assertEqual($aRow['zone_id'], 1); $this->assertEqual($aRow['count'], 2); $query = "\n SELECT\n *\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true) . "\n WHERE\n creative_id = 2"; $rsResult = $this->oDbh->query($query); $this->assertNotA($rsReults, 'PEAR_Error'); $rows = $rsResult->numRows(); $this->assertEqual($rows, 1); $aRow = $rsResult->fetchRow(); $this->assertEqual($aRow['interval_start'], '2009-01-09 12:00:00'); $this->assertEqual($aRow['creative_id'], 2); $this->assertEqual($aRow['zone_id'], 3); $this->assertEqual($aRow['count'], 1); } // Uninstall the installed plugin TestEnv::uninstallPluginPackage('openXDeliveryLog', false); // Restore the test environment configuration TestEnv::restoreConfig(); }
function execute($aParams) { $this->oUpgrade =& $aParams[0]; $this->oDbh =& OA_DB::singleton(); $aConf = $GLOBALS['_MAX']['CONF']['table']; $this->prefix = $aConf['prefix']; $this->tblCampaigns = $aConf['prefix'] . ($aConf['campaigns'] ? $aConf['campaigns'] : 'campaigns'); $this->queryUpdateTemplate = "UPDATE " . $this->oDbh->quoteIdentifier($this->tblCampaigns, true) . "\n SET revenue_type = %s\n WHERE campaignid in (%s)"; $query = 'SELECT campaignid, clicks, conversions FROM ' . $this->oDbh->quoteIdentifier($this->tblCampaigns, true) . ' c WHERE c.revenue_type IS NULL '; $rs = $this->oDbh->query($query); //check for error if (PEAR::isError($rs)) { $this->logError($rs->getUserInfo()); return false; } /* process campaigns and derive the revenue_type from set limits if (conversions set) -> revenue type is CPA else if (clicks set) -> revenue type is CPC else if (impressions set) -> revenue type is CPM else -> default to CPM */ $aCPMCampaigns = array(); $aCPCCampaigns = array(); $aCPACampaigns = array(); while ($aCampaign = $rs->fetchRow(MDB2_FETCHMODE_ASSOC)) { if ($aCampaign['conversions'] > 0) { $aCPACampaigns[] = $aCampaign['campaignid']; } else { if ($aCampaign['clicks'] > 0) { $aCPCCampaigns[] = $aCampaign['campaignid']; } else { //views set or no limits CPM as well $aCPMCampaigns[] = $aCampaign['campaignid']; } } } /* update campaigns accordingly 'MAX_FINANCE_CPM', 1); 'MAX_FINANCE_CPC', 2); 'MAX_FINANCE_CPA', 3); */ $cpmCount = count($aCPMCampaigns); $cpcCount = count($aCPCCampaigns); $cpaCount = count($aCPACampaigns); $count = $cpmCount + $cpcCount + $cpaCount; $this->logOnly("Found " + $count + " campaign(s) to set missing revenue type: " + ($cpmCount > 0 ? "{$cpmCount} to CPM," : '') + ($cpcCount > 0 ? "{$cpcCount} to CPC," : '') + ($cpaCount > 0 ? "{$cpaCount} to CPA" : '')); if ($cpmCount > 0) { $query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPM, implode(',', $aCPMCampaigns)); $result = $this->oDbh->exec($query); if (PEAR::isError($result)) { $this->logError($result->getUserInfo()); return false; } else { $this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPM"); } } if ($cpcCount > 0) { $query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPC, implode(',', $aCPCCampaigns)); $result = $this->oDbh->exec($query); if (PEAR::isError($result)) { $this->logError($result->getUserInfo()); return false; } else { $this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPC"); } } if ($cpaCount > 0) { $query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPA, implode(',', $aCPACampaigns)); $result = $this->oDbh->exec($query); if (PEAR::isError($result)) { $this->logError($result->getUserInfo()); return false; } else { $this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPA"); } } return true; }