/** * This function recreates the data_summary_ad_hourly table rolls-up hourly stats to daily totals * The roll-up is done in accordance to the user's (currently) selected timezone * * @param PEAR_Date $oDate The date before which hourly stats should be rolled up */ function _rollUpHourlyStatsToDaily($oDate) { $sDate = $oDate->format('%Y-%m-%d 00:00:00'); $updated = OA::getNowUTC('Y-m-d h:i:s'); OA::debug("Beginning stats rollup for pre-{$sDate}", PEAR_LOG_INFO); // First create a temporary table with ad_id/offset pairs, this can then be joined in a (compled) INSERT INTO ... SELECT FROM statement $aTimezones = OX_Admin_Timezones::availableTimezones(false); $aAdminPreferences = OA_Preferences::loadAdminAccountPreferences(true); $aAdminTzOffset = $this->_getSqlOffsetFromString($aTimezones[$aAdminPreferences['timezone']]); // CREATE a timezone string => offset table (since the format we use in PHP is incompatible with the format used by MySQL) $this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}tmp_tz_offset"); $this->oDbh->exec("CREATE TABLE `{$this->prefix}tmp_tz_offset`\n (`timezone` varchar(32) NOT NULL PRIMARY KEY, `offset` char(6) NOT NULL DEFAULT '+00:00') ENGINE={$this->conf['table']['type']}"); foreach ($aTimezones as $tzString => $tzData) { $tzData = $this->_getSqlOffsetFromString($tzData); $this->oDbh->exec("INSERT INTO {$this->prefix}tmp_tz_offset (timezone, offset) VALUES('{$tzString}', '{$tzData}')"); } OA::debug("Created timezone/offset mapping table", PEAR_LOG_DEBUG); // CREATE an ad_id => offset table $this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}tmp_ad_offset"); $this->oDbh->exec("CREATE TABLE `{$this->prefix}tmp_ad_offset`\n (`ad_id` int(11) NOT NULL PRIMARY KEY, `offset` char(6) NOT NULL DEFAULT '+00:00') ENGINE={$this->conf['table']['type']}"); $this->oDbh->exec("INSERT INTO {$this->prefix}tmp_ad_offset SELECT bannerid, '{$aAdminTzOffset}' FROM {$this->prefix}banners AS b"); $this->oDbh->exec("UPDATE\n {$this->prefix}tmp_ad_offset AS ao,\n {$this->prefix}banners AS b,\n {$this->prefix}campaigns AS c,\n {$this->prefix}clients AS cl,\n {$this->prefix}agency AS ag,\n {$this->prefix}account_preference_assoc AS apa,\n {$this->prefix}preferences AS p,\n {$this->prefix}tmp_tz_offset AS tzo\n SET ao.offset = tzo.offset\n WHERE\n p.preference_name = 'timezone'\n AND apa.preference_id = p.preference_id\n AND apa.account_id = ag.account_id\n AND cl.agencyid=ag.agencyid\n AND ao.ad_id=b.bannerid\n AND c.campaignid=b.campaignid\n AND cl.clientid=c.clientid\n AND ag.agencyid=cl.agencyid\n AND apa.value = tzo.timezone;\n "); OA::debug("Created ad/offset mapping table", PEAR_LOG_DEBUG); // So, now we have a table tmp_ad_offset which contains every banner id, and the offset of the account it belongs to. // We can use this to do a complex GROUP BY to collapse data down into the user's timzone's midday $this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}data_summary_ad_hourly_rolledup"); $this->oDbh->exec("DROP TABLE IF EXISTS {$this->prefix}data_summary_ad_hourly_backup"); // Create a new stats table, we do this because trying to delete a bunch of records from the existing table would just fragment the index $this->oDbh->exec("CREATE TABLE {$this->prefix}data_summary_ad_hourly_rolledup LIKE {$this->prefix}data_summary_ad_hourly;"); // Copy stats over from the existing table to the new table, rolling up according to each ad's offset OA::debug("Beginning rolled-up stats copy...", PEAR_LOG_DEBUG); $this->oDbh->exec("INSERT INTO {$this->prefix}data_summary_ad_hourly_rolledup (\n date_time, ad_id, creative_id, zone_id, requests, impressions, clicks, conversions,\n total_basket_value, total_num_items, total_revenue, total_cost, total_techcost, updated )\n SELECT\n CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(dsah.date_time, '+00:00', ao.offset), '%Y-%m-%d 12:00:00'), ao.offset, '+00:00') AS tz_date_time,\n dsah.ad_id, dsah.creative_id, dsah.zone_id, SUM(dsah.requests), SUM(dsah.impressions), SUM(dsah.clicks), SUM(dsah.conversions),\n SUM(dsah.total_basket_value), SUM(dsah.total_num_items), SUM(dsah.total_revenue), SUM(dsah.total_cost), SUM(dsah.total_techcost), '{$updated}'\n FROM\n {$this->prefix}data_summary_ad_hourly AS dsah,\n {$this->prefix}tmp_ad_offset AS ao\n WHERE\n ao.ad_id=dsah.ad_id\n AND CONVERT_TZ(dsah.date_time, '+00:00', ao.offset) < '{$sDate}'\n GROUP BY\n tz_date_time, ad_id, creative_id, zone_id;\n "); OA::debug("Completed rolled-up stats copy...", PEAR_LOG_DEBUG); // Copy any un-rolled up stats records over into the new table OA::debug("Beginning *non* rolled-up stats copy...", PEAR_LOG_DEBUG); $this->oDbh->exec("INSERT INTO {$this->prefix}data_summary_ad_hourly_rolledup (\n date_time, ad_id, creative_id, zone_id, requests, impressions, clicks, conversions,\n total_basket_value, total_num_items, total_revenue, total_cost, total_techcost, updated)\n SELECT\n dsah.date_time AS tz_date_time, dsah.ad_id, dsah.creative_id, dsah.zone_id, dsah.requests, dsah.impressions, dsah.clicks, dsah.conversions,\n dsah.total_basket_value, dsah.total_num_items, dsah.total_revenue, dsah.total_cost, dsah.total_techcost, '{$updated}'\n FROM\n {$this->prefix}data_summary_ad_hourly AS dsah,\n {$this->prefix}tmp_ad_offset AS ao\n WHERE\n ao.ad_id=dsah.ad_id\n AND CONVERT_TZ(dsah.date_time, '+00:00', ao.offset) >= '{$sDate}'\n "); OA::debug("Completed *non* rolled-up stats copy...", PEAR_LOG_DEBUG); // Swap the old table with the new $this->oDbh->exec("RENAME TABLE {$this->prefix}data_summary_ad_hourly TO {$this->prefix}data_summary_ad_hourly_backup"); $this->oDbh->exec("RENAME TABLE {$this->prefix}data_summary_ad_hourly_rolledup TO {$this->prefix}data_summary_ad_hourly"); OA::debug("Swapped new table for old...", PEAR_LOG_DEBUG); // Cleanup $this->oDbh->exec("DROP TABLE {$this->prefix}tmp_ad_offset;"); $this->oDbh->exec("DROP TABLE {$this->prefix}tmp_tz_offset;"); OA::debug("Woo hoo stats rolled up for pre-{$sDate}", PEAR_LOG_INFO); }
function testAvailableTimezones() { global $_DATE_TIMEZONE_DATA; // Get time zones $aTimezone = OX_Admin_Timezones::availableTimezones(true); // Test that it is an array, not empty and contains some items + a blank $this->assertTrue(is_array($aTimezone)); $this->assertTrue(count($aTimezone) > 2); $this->assertEqual($aTimezone[0], ''); // Remove blank element array_shift($aTimezone); // Check returned data against global array from PEAR::Date foreach ($aTimezone as $key => $value) { $this->assertTrue(array_key_exists($key, $_DATE_TIMEZONE_DATA)); // Check label to ensure it was created properly $offset = OX_Admin_Timezones::_convertOffset($_DATE_TIMEZONE_DATA[$key]['offset']); $offset = $_DATE_TIMEZONE_DATA[$key]['offset'] >= 0 ? 'GMT+' . $offset : 'GMT-' . $offset; $this->assertEqual($value, "({$offset}) {$key}"); } }
$translated_message = $translation->translate($GLOBALS['strXPreferencesHaveBeenUpdated'], array(htmlspecialchars($title))); OA_Admin_UI::queueMessage($translated_message, 'local', 'confirm', 0); OX_Admin_Redirect::redirect(basename($_SERVER['PHP_SELF'])); } // Could not write the preferences to the database, store this // error message and continue $aErrormessage[0][] = $strUnableToWritePrefs; } // Set the correct section of the preference pages and display the drop-down menu $setPref = $oOptions->getSettingsPreferences($prefSection); $title = $setPref[$prefSection]['name']; // Display the settings page's header and sections $oHeaderModel = new OA_Admin_UI_Model_PageHeaderModel($title); phpAds_PageHeader('account-preferences-index', $oHeaderModel); // Get timezone dropdown information $aTimezones = OX_Admin_Timezones::availableTimezones(true); $oConfigTimezone = trim($GLOBALS['_MAX']['PREF']['timezone']); if (empty($oConfigTimezone)) { // There is no value stored in the configuration file, as it // is not required (ie. the TZ comes from the environment) - // so set that environment value in the config file now $GLOBALS['_MAX']['PREF']['timezone'] = $timezone; } // What display string do we need to show for the timezone? if (!empty($oConfigTimezone)) { $strTimezoneToDisplay = $oConfigTimezone; } else { $strTimezoneToDisplay = $timezone; } $strTimezoneToDisplay = $GLOBALS['_MAX']['PREF']['timezone']; // Prepare an array of HTML elements to display for the form, and