/**
* A private method for summarising data into the final tables when
* at least one hour is complete.
*
* @access private
* @param PEAR::Date $oStartDate The start date of the complete hour(s).
* @param PEAR::Date $oEndDate The end date of the complete hour(s).
*/
function _saveSummary($oStartDate, $oEndDate)
{
$message = '- Updating the data_summary_ad_hourly table for data after ' . $oStartDate->format('%Y-%m-%d %H:%M:%S') . ' ' . $oStartDate->tz->getShortName();
$this->oController->report .= $message . ".\n";
OA::debug($message, PEAR_LOG_DEBUG);
$oServiceLocator =& OA_ServiceLocator::instance();
$oDal =& $oServiceLocator->get('OX_Dal_Maintenance_Statistics');
$aTypes = array('types' => array(0 => 'request', 1 => 'impression', 2 => 'click'), 'connections' => array(1 => MAX_CONNECTION_AD_IMPRESSION, 2 => MAX_CONNECTION_AD_CLICK));
$oDal->saveSummary($oStartDate, $oEndDate, $aTypes, 'data_intermediate_ad', 'data_summary_ad_hourly');
}
/**
* A method to determine the number of impressions, clicks and conversions
* delivered by a given ecpm campaign to date.
*
* Can also determine the delivery information up to a given operation
* interval end date.
*
* @param integer $agencyId The agency ID.
* @param PEAR::Date $oDate Limits delivery information to that which is
* after this date.
* @param integer $priority Campaign priority (by default eCPM priority).
* @return array
*/
function getDeliveredEcpmCampainImpressionsByAgency($agencyId, $oDate, $priority = null)
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
if (is_null($priority)) {
$priority = DataObjects_Campaigns::PRIORITY_ECPM;
}
$query = "\n SELECT\n c.campaignid AS campaignid,\n SUM(dia.impressions) AS impressions_delivered\n FROM\n {$oDbh->quoteIdentifier($prefix . 'clients', true)} AS cl,\n {$oDbh->quoteIdentifier($prefix . 'campaigns', true)} AS c,\n {$oDbh->quoteIdentifier($prefix . 'banners', true)} AS b,\n {$oDbh->quoteIdentifier($prefix . 'data_intermediate_ad', true)} AS dia\n WHERE\n cl.agencyid = " . DBC::makeLiteral($agencyId) . "\n AND c.status = " . OA_ENTITY_STATUS_RUNNING . "\n AND c.priority = " . $priority . "\n AND cl.clientid = c.clientid\n AND b.bannerid = dia.ad_id\n AND b.campaignid = c.campaignid\n AND dia.interval_end >= '" . $oDate->format('%Y-%m-%d %H:%M:%S') . "'\n GROUP BY\n c.campaignid";
$rs = DBC::NewRecordSet($query);
if (PEAR::isError($rs)) {
return false;
}
return $rs->getAll(array(), 'campaignid');
}
/**
* A method to reject conversions which variables which are required to be
* non-empty, but which are in reality, empty, between the supplied operation
* interval start and end dates.
*
* @param PEAR::Date $oStart The start date/time of the operation interval.
* @param PEAR::Date $oEnd The end date/time of the operation interval.
*/
function rejectEmptyVarConversions($oStart, $oEnd)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$var = $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['variables'], true);
$diac = $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['data_intermediate_ad_connection'], true);
$diavv = $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['data_intermediate_ad_variable_value'], true);
$query = "\n UPDATE\n {$diac}\n SET\n connection_status = " . MAX_CONNECTION_STATUS_DISAPPROVED . ",\n updated = '" . OA::getNow() . "',\n comments = 'Rejected because ' || COALESCE(NULLIF(v.description, ''), v.name) || ' is empty'\n FROM\n {$diac} AS diac2\n JOIN\n {$var} AS v\n ON\n (\n diac2.tracker_id = v.trackerid\n )\n LEFT JOIN\n {$diavv} AS diavv\n ON\n (\n diac2.data_intermediate_ad_connection_id = diavv.data_intermediate_ad_connection_id\n AND\n v.variableid = diavv.tracker_variable_id\n )\n WHERE\n {$diac}.data_intermediate_ad_connection_id = diac2.data_intermediate_ad_connection_id\n AND\n {$diac}.tracker_date_time >= '" . $oStart->format('%Y-%m-%d %H:%M:%S') . "'\n AND\n {$diac}.tracker_date_time <= '" . $oEnd->format('%Y-%m-%d %H:%M:%S') . "'\n AND\n {$diac}.inside_window = 1\n AND\n v.reject_if_empty = 1\n AND\n (diavv.value IS NULL OR diavv.value = '')\n ";
$message = '- Rejecting conversions with empty required variables between ' . $oStart->format('%Y-%m-%d %H:%M:%S') . ' ' . $oStart->tz->getShortName() . ' and ' . $oEnd->format('%Y-%m-%d %H:%M:%S') . ' ' . $oEnd->tz->getShortName();
OA::debug($message, PEAR_LOG_DEBUG);
$rows = $this->oDbh->exec($query);
if (PEAR::isError($rows)) {
return MAX::raiseError($rows, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
}
/**
* A method to set the basic financial information in a summary table,
* on the basis of given ad financial information.
*
* @access private
* @param array $aAdFinanceInfo An array of arrays, each with the ad_id, revenue and
* revenue_type information for the ads that need updating.
* @param PEAR::Date $oStartDate The start date of records that need updating.
* @param PEAR::Date $oEndDate The end date of records that need updating.
* @param string $table The name of the summary table to update with financial
* information (e.g. 'data_summary_ad_hourly').
*
* Note: The method looks for a special variable in the service locator, called
* "aAdFinanceMappings". If found, and an array, the contents of the array
* are used to determine the column name that should be used when calculating
* the finance information in the SQL statement, for the appropriate revenue
* type. If not found, the default mapping is used:
* array(
* MAX_FINANCE_CPM => impressions,
* MAX_FINANCE_CPC => clicks,
* MAX_FINANCE_CPA => conversions
* )
*
* Note: The method looks for a special variable in the service locator, called
* "aAdFinanceLimitTypes". If found, and an array, the contents of the array
* are tested to see if the revenue type set for the ad ID to be updated is
* in the array. If it is not, then the finance information is not set for
* the ad.
*
* @TODO Update to deal with monthly tenancy.
*/
function _saveSummaryUpdateAdsWithFinanceInfo($aAdFinanceInfo, $oStartDate, $oEndDate, $table)
{
$aConf = $GLOBALS['_MAX']['CONF'];
if ($oStartDate->format('%H') != 0 || $oEndDate->format('%H') != 23) {
if ($oStartDate->format('%Y-%m-%d') != $oEndDate->format('%Y-%m-%d')) {
MAX::raiseError('_saveSummaryUpdateAdsWithFinanceInfo called with dates not on the same day.', null, PEAR_ERROR_DIE);
}
}
$oServiceLocator =& OA_ServiceLocator::instance();
// Prepare the revenue type to column name mapping array
$aAdFinanceMappings =& $oServiceLocator->get('aAdFinanceMappings');
if ($aAdFinanceMappings === false || !array($aAdFinanceMappings) || empty($aAdFinanceMappings)) {
$aAdFinanceMappings = array(MAX_FINANCE_CPM => 'impressions', MAX_FINANCE_CPC => 'clicks', MAX_FINANCE_CPA => 'conversions');
}
// Try to get the $aAdFinanceLimitTypes array
$aAdFinanceLimitTypes =& $oServiceLocator->get('aAdFinanceLimitTypes');
foreach ($aAdFinanceInfo as $aInfo) {
$query = '';
$setInfo = true;
// Test to see if the finance information should NOT be set for this ad
if ($aAdFinanceLimitTypes !== false) {
if (is_array($aAdFinanceLimitTypes) && !empty($aAdFinanceLimitTypes)) {
// Try to find the ad's revenue type in the array
if (!in_array($aInfo['revenue_type'], $aAdFinanceLimitTypes)) {
// It's not in the array, don't set the finance info
$setInfo = false;
}
}
}
// Prepare the SQL query to set the revenue information, if required
if ($setInfo) {
switch ($aInfo['revenue_type']) {
case MAX_FINANCE_CPM:
$query = "\n UPDATE\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table'][$table], true) . "\n SET\n total_revenue = {$aAdFinanceMappings[MAX_FINANCE_CPM]} * {$aInfo['revenue']} / 1000,\n updated = '" . OA::getNow() . "'\n WHERE\n ad_id = {$aInfo['ad_id']}\n AND date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
break;
case MAX_FINANCE_CPC:
$query = "\n UPDATE\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table'][$table], true) . "\n SET\n total_revenue = {$aAdFinanceMappings[MAX_FINANCE_CPC]} * {$aInfo['revenue']},\n updated = '" . OA::getNow() . "'\n WHERE\n ad_id = {$aInfo['ad_id']}\n AND date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
break;
case MAX_FINANCE_CPA:
$query = "\n UPDATE\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table'][$table], true) . "\n SET\n total_revenue = {$aAdFinanceMappings[MAX_FINANCE_CPA]} * {$aInfo['revenue']},\n updated = '" . OA::getNow() . "'\n WHERE\n ad_id = {$aInfo['ad_id']}\n AND date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
break;
}
}
if (!empty($query)) {
$rows = $this->oDbh->exec($query);
if (PEAR::isError($rows)) {
return MAX::raiseError($rows, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
}
}
}
/**
* A private method to calculate the operation interval start date at the
* upper bound of a range of operation intervals that require a ZIF update,
* where the upper bound has been set back by the required number of
* operation intervals so that current trends in differences between
* forecast and actual delivery can be calculated.
*
* @access private
* @param PEAR::Date $oDate The start date of the operation interval at the
* upper bound of the operation interval range
* requiring a ZIF update.
* @return PEAR::Date The new upper bound date.
*/
function _getTrendUpperDate($oDate)
{
$seconds = ZONE_FORECAST_TREND_OFFSET * OX_OperationInterval::secondsPerOperationInterval();
$oDate->subtractSeconds($seconds);
return $oDate;
}
/**
* A private function to migrate raw format clicks into bucket format
* clicks, post-upgrade to (or beyond) OpenX 2.8.
*
* @access private
* @param PEAR::Date $oStart The start date of the operation interval
* to migrate.
* @param PEAR::Date $oEnd The end date of the operation interval to
* migrate.
*/
private function _migrateRawClicks($oStart, $oEnd)
{
$message = " - Migrating raw clicks into the new bucket table, for the operation interval";
OA::debug($message, PEAR_LOG_DEBUG);
$message = " starting " . $oStart->format('%Y-%m-%d %H:%M:%S') . ' ' . $oStart->tz->getShortName() . " and ending " . $oEnd->format('%Y-%m-%d %H:%M:%S') . ' ' . $oEnd->tz->getShortName() . ".";
OA::debug($message, PEAR_LOG_DEBUG);
$oServiceLocator =& OA_ServiceLocator::instance();
$oDal =& $oServiceLocator->get('OX_Dal_Maintenance_Statistics');
$oDal->migrateRawClicks($oStart, $oEnd);
}
/**
* A method to convert the object's start and end dates into UTC format.
*/
function toUTC()
{
$this->oStartDate->toUTC();
$this->oEndDate->toUTC();
}
/**
* A method for obtaining the information required for the zone view targeting
* statistics screen.
*
* @param integer $zoneId The zone ID.
* @param PEAR::Date $oStartDate The start date of the operation interval.
* @param PEAR::Date $oEndDate The end date of the operation interval.
*
* @return mixed Returns false in the event of incorrect input, or in the case
* of being unable to connect to the database, otherwise, returns
* an array of arrays:
* array(
* [$adId] => array(
* ['impressions_requested'] => integer
* ['priority'] => double
* ['priority_factor'] => double
* ['actual_impressions'] => integer
* )
* .
* .
* .
* )
*
* For the operation interval specified by the start end end dates, the method
* should return the impressions requested, priority, priority factor and
* actual impressions delivered for each possible ad that was linked to the
* specified zone. This requires searching the data_intermediate_ad table for
* the ads which delivered in the zone, to get the number of acutal impressions,
* and also searching the data_summary_ad_zone_assoc table for the ads which were
* targeted to deliver in the zone.
*
* The impressions requested, priority and priority factor may need to be
* calculated as an "averge" value, in the event that there are multiple, differing
* values for the ad in a zone, in much the same way as is done in
* MAX_Dal_Maintenance::getPreviousAdDeliveryInfo().
*
* @todo Detect and handle multiple (overlapping) intervals
* @todo Read and return priority and priority factor
*/
function getZoneTargetingStatistics($zone_id, &$oStartDate, &$oEndDate)
{
$data_table = $this->intermediate_data_table_name;
$adzone_table_name = $this->ad_zone_table_name;
$start_sql = $oStartDate->format("'%Y-%m-%d %H:%M:%S'");
$end_sql = $oEndDate->format("'%Y-%m-%d %H:%M:%S'");
$sql = "\n SELECT\n interm.ad_id,\n impressions_requested as impressions_requested,\n impressions as actual_impressions,\n adzone.priority as priority\n FROM\n {$data_table} interm,\n {$adzone_table_name} adzone\n WHERE interm.zone_id = adzone.zone_id\n and interm.ad_id = adzone.ad_id\n and interm.interval_start = adzone.interval_start\n and interm.zone_id = " . $this->oDbh->quote($zone_id, 'integer') . "\n and interm.interval_start >= " . $this->oDbh->quote($start_sql, 'timestamp') . "\n and interm.interval_end <= " . $this->oDbh->quote($end_sql, 'timestamp') . "\n GROUP BY ad_id\n ";
$results = $this->oDbh->extended->getAll($sql);
$stats = array();
foreach ($results as $result) {
$ad_id = $result['ad_id'];
$stats[$ad_id] = $result;
}
return $stats;
}
/**
* 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']);
}
}
}
