/** * 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']; $query = "\n UPDATE\n {$aConf['table']['prefix']}{$aConf['table']['data_intermediate_ad_connection']} AS diac\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['variables']} AS v\n ON\n (\n diac.tracker_id = v.trackerid\n )\n LEFT JOIN\n {$aConf['table']['prefix']}{$aConf['table']['data_intermediate_ad_variable_value']} AS diavv\n ON\n (\n diac.data_intermediate_ad_connection_id = diavv.data_intermediate_ad_connection_id\n AND\n v.variableid = diavv.tracker_variable_id\n )\n SET\n diac.connection_status = " . MAX_CONNECTION_STATUS_DISAPPROVED . ",\n diac.updated = '" . OA::getNow() . "',\n diac.comments = CONCAT('Rejected because ', COALESCE(NULLIF(v.description, ''), v.name), ' is empty')\n WHERE\n diac.tracker_date_time >= " . $this->oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND\n diac.tracker_date_time <= " . $this->oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\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 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 private method that returns the start and end dates * that bound the span, based based on a pre-defined 'friendly' * value. * * See the {@link OA_Admin_DaySpan::setSpanPresetValue()} method * for the pre-defined values. * * @param string $presetValue The preset value string. * @return array An array of two elements, "start" and "end", * representing the start and end dates of * the span, respectively. */ function _getSpanDates($presetValue) { switch ($presetValue) { case 'today': $oDateStart = new Date($this->oNowDate->format('%Y-%m-%d')); $oDateEnd = new Date($this->oNowDate->format('%Y-%m-%d')); break; case 'yesterday': $oDateStart = new Date(Date_Calc::prevDay($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oDateEnd = new Date(Date_Calc::prevDay($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); break; case 'this_week': $oDateStart = new Date(Date_Calc::beginOfWeek($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oSixDaySpan = new Date_Span(); $oSixDaySpan->setFromDays(6); $oSevenDaySpan = new Date_Span(); $oSevenDaySpan->setFromDays(7); // Now have week start and end when week starts on Sunday // Does the user want to start on a different day? $beginOfWeek = OA_Admin_DaySpan::getBeginOfWeek(); if ($beginOfWeek > 0) { $oRequiredDaysSpan = new Date_Span(); $oRequiredDaysSpan->setFromDays($beginOfWeek); $oDateStart->addSpan($oRequiredDaysSpan); $oDateToday = new Date($this->oNowDate->format('%Y-%m-%d')); if ($oDateToday->getDayOfWeek() < $beginOfWeek) { $oDateStart->subtractSpan($oSevenDaySpan); } } $oDateEnd = new Date($this->oNowDate->format('%Y-%m-%d')); break; case 'last_week': $oDateStart = new Date(Date_Calc::beginOfPrevWeek($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oSixDaySpan = new Date_Span(); $oSixDaySpan->setFromDays(6); $oSevenDaySpan = new Date_Span(); $oSevenDaySpan->setFromDays(7); // Now have week start and end when week starts on Sunday // Does the user want to start on a different day? $beginOfWeek = OA_Admin_DaySpan::getBeginOfWeek(); if ($beginOfWeek > 0) { $oRequiredDaysSpan = new Date_Span(); $oRequiredDaysSpan->setFromDays($beginOfWeek); $oDateStart->addSpan($oRequiredDaysSpan); $oDateToday = new Date($this->oNowDate->format('%Y-%m-%d')); if ($oDateToday->getDayOfWeek() < $beginOfWeek) { $oDateStart->subtractSpan($oSevenDaySpan); } } $oDateEnd = new Date($this->oNowDate->format('%Y-%m-%d')); $oDateEnd->copy($oDateStart); $oDateEnd->addSpan($oSixDaySpan); break; case 'last_7_days': $oDateStart = new Date($this->oNowDate->format('%Y-%m-%d')); $oDateEnd = new Date($this->oNowDate->format('%Y-%m-%d')); $oOneDaySpan = new Date_Span(); $oOneDaySpan->setFromDays(1); $oSevenDaySpan = new Date_Span(); $oSevenDaySpan->setFromDays(7); $oDateStart->subtractSpan($oSevenDaySpan); $oDateEnd->subtractSpan($oOneDaySpan); break; case 'this_month': $oDateStart = new Date(Date_Calc::beginOfMonth($this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oDateEnd = new Date($this->oNowDate->format('%Y-%m-%d')); break; case 'this_month_full': $oDateStart = new Date(Date_Calc::beginOfMonth($this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oDateEnd = new Date(Date_Calc::beginOfNextMonth($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oOneDaySpan = new Date_Span(); $oOneDaySpan->setFromDays(1); $oDateEnd->subtractSpan($oOneDaySpan); break; case 'this_month_remainder': $oDateStart = new Date($this->oNowDate->format('%Y-%m-%d')); $oDateEnd = new Date(Date_Calc::beginOfNextMonth($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oOneDaySpan = new Date_Span(); $oOneDaySpan->setFromDays(1); $oDateEnd->subtractSpan($oOneDaySpan); break; case 'next_month': $oDateStart = new Date(Date_Calc::beginOfNextMonth($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oDateEnd = new Date(Date_Calc::endOfNextMonth($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); break; case 'last_month': $oDateStart = new Date(Date_Calc::beginOfPrevMonth($this->oNowDate->format('%d'), $this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oDateEnd = new Date(Date_Calc::beginOfMonth($this->oNowDate->format('%m'), $this->oNowDate->format('%Y'))); $oOneDaySpan = new Date_Span(); $oOneDaySpan->setFromDays(1); $oDateEnd->subtractSpan($oOneDaySpan); break; case 'all_stats': $oDateStart = null; $oDateEnd = null; break; case 'specific': $startDate = MAX_getStoredValue('startDate', date('Y-m-d')); $oDateStart = new Date($startDate); $endDate = MAX_getStoredValue('endDate', date('Y-m-d')); $oDateEnd = new Date($endDate); break; } $this->_setStartDate($oDateStart); $this->_setEndDate($oDateEnd); $aDates = array('start' => $oDateStart, 'end' => $oDateEnd); return $aDates; }
/** * 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; }