require_once LIB_PATH . '/Maintenance/Statistics/MigrateBucketData.php'; require_once OX_PATH . '/lib/pear/Date.php'; // 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 OX::disableErrorHandling(); $result = OX_OperationInterval::checkOperationIntervalValue(OX_OperationInterval::getOperationInterval()); OX::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;
/** * 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 OX::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 OX::enableErrorHandling(); return $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); } } } }
/** * 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)) { OX::disableErrorHandling(); $errors[] = PEAR::raiseError($GLOBALS['strErrorDBPlain']); OX::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 exclusive 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_CONTRACT_EXCLUSIVE)) { $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_CONTRACT_EXCLUSIVE) { $aFields['priority'] = -1; //exclusive } 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; }
/** * 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"; OX::disableErrorHandling(); $rsResult = $this->oDbh->query($sQuery); OX::enableErrorHandling(); if (PEAR::isError($rsResult)) { return $rsResult; } $aResult = array(); while ($aRow = $rsResult->fetchRow()) { $aResult[] = $aRow['zone_id']; } $rsResult->free(); return $aResult; }
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 "; OX::disableErrorHandling(); $aResult = $this->oSchema->db->queryAll($query); OX::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; }
/** * 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 "; OX::disableErrorHandling(); $rsResult = $this->oDbh->query($query); OX::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; }