/**
* A method that can be inherited and used by children classes to get the
* required date span of a statistics page.
*
* @param object $oCaller The calling object. Expected to have the
* the following class variables:
* $oCaller->aPlugins - An array of statistics fields plugins
* $oCaller->oStartDate - Will be set by method
* $oCaller->spanDays - Will be set by method
* $oCaller->spanWeeks - Will be set by method
* $oCaller->spanMonths - Will be set by method
* @param array $aParams An array of query parameters for
* {@link Admin_DA::fromCache()}.
*/
function getSpan(&$oCaller, $aParams)
{
$oStartDate = new Date(date('Y-m-d'));
$oStartDate->setHour(0);
$oStartDate->setMinute(0);
$oStartDate->setSecond(0);
// Check span using all plugins
foreach ($oCaller->aPlugins as $oPlugin) {
$aPluginParams = call_user_func(array($oPlugin, 'getHistorySpanParams'));
$aSpan = Admin_DA::fromCache('getHistorySpan', $aParams + $aPluginParams);
if (!empty($aSpan['start_date'])) {
$oDate = new Date($aSpan['start_date']);
$oDate->setTZbyID('UTC');
if ($oDate->before($oStartDate)) {
$oDate->convertTZ($oStartDate->tz);
$oStartDate = new Date($oDate);
}
}
}
$oStartDate->setHour(0);
$oStartDate->setMinute(0);
$oStartDate->setSecond(0);
$oNow = new Date();
$oSpan = new Date_Span(new Date($oStartDate), new Date($oNow->format('%Y-%m-%d')));
// Store the span data required for stats display
$oCaller->oStartDate = $oStartDate;
$oCaller->spanDays = (int) ceil($oSpan->toDays());
$oCaller->spanWeeks = (int) ceil($oCaller->spanDays / 7) + ($oCaller->spanDays % 7 ? 1 : 0);
$oCaller->spanMonths = ($oNow->getYear() - $oStartDate->getYear()) * 12 + ($oNow->getMonth() - $oStartDate->getMonth()) + 1;
// Set the caller's aDates span in the event that it's empty
if (empty($oCaller->aDates)) {
$oCaller->aDates['day_begin'] = $oStartDate->format('%Y-%m-%d');
$oCaller->aDates['day_end'] = $oNow->format('%Y-%m-%d');
}
}
/**
* Overloaded compare method
*
* The convertTZ calls are time intensive calls. When a compare call is
* made in a recussive loop the lag can be significant.
*/
function compare($d1, $d2, $convertTZ = false)
{
if (!is_object($d1)) {
$d1 = new Date($d1);
}
if (!is_object($d2)) {
$d2 = new Date($d2);
}
if ($convertTZ) {
$d1->convertTZ(new Date_TimeZone('UTC'));
$d2->convertTZ(new Date_TimeZone('UTC'));
}
$days1 = Date_Calc::dateToDays($d1->day, $d1->month, $d1->year);
$days2 = Date_Calc::dateToDays($d2->day, $d2->month, $d2->year);
$comp_value = 0;
if ($days1 - $days2) {
$comp_value = $days1 - $days2;
} else {
if ($d1->hour - $d2->hour) {
$comp_value = dPsgn($d1->hour - $d2->hour);
} else {
if ($d1->minute - $d2->minute) {
$comp_value = dPsgn($d1->minute - $d2->minute);
} else {
if ($d1->second - $d2->second) {
$comp_value = dPsgn($d1->second - $d2->second);
}
}
}
}
return dPsgn($comp_value);
}
/**
* Compares two dates
*
* Compares two dates. Suitable for use
* in sorting functions.
*
* @access public
* @param object Date $xd1 the first date
* @param object Date $xd2 the second date
* @return int 0 if the dates are equal, -1 if d1 is before d2, 1 if d1 is after d2
*/
function compare($xd1, $xd2)
{
$d1 = new Date();
$d1->copy($xd1);
$d2 = new Date();
$d2->copy($xd2);
$d1->convertTZ(new Date_TimeZone('UTC'));
$d2->convertTZ(new Date_TimeZone('UTC'));
$days1 = Date_Calc::dateToDays($d1->day, $d1->month, $d1->year);
$days2 = Date_Calc::dateToDays($d2->day, $d2->month, $d2->year);
if ($days1 < $days2) {
return -1;
}
if ($days1 > $days2) {
return 1;
}
if ($d1->hour < $d2->hour) {
return -1;
}
if ($d1->hour > $d2->hour) {
return 1;
}
if ($d1->minute < $d2->minute) {
return -1;
}
if ($d1->minute > $d2->minute) {
return 1;
}
if ($d1->second < $d2->second) {
return -1;
}
if ($d1->second > $d2->second) {
return 1;
}
return 0;
}
$aCampaign['clicks'] = phpAds_formatNumber($aCampaign['clicks']);
$aCampaign['conversions'] = phpAds_formatNumber($aCampaign['conversions']);
if (!empty($aCampaign['activate_time'])) {
$oActivateDate = new Date($aCampaign['activate_time']);
$oTz = $oActivateDate->tz;
$oActivateDate->setTZbyID('UTC');
$oActivateDate->convertTZ($oTz);
$aCampaign['activate'] = $oActivateDate->format($date_format);
} else {
$aCampaign['activate'] = '-';
}
if (!empty($aCampaign['expire_time'])) {
$oExpireDate = new Date($aCampaign['expire_time']);
$oTz = $oExpireDate->tz;
$oExpireDate->setTZbyID('UTC');
$oExpireDate->convertTZ($oTz);
$aCampaign['expire'] = $oExpireDate->format($date_format);
} else {
$aCampaign['expire'] = '-';
}
if ($aCampaign['type'] == DataObjects_Campaigns::CAMPAIGN_TYPE_MARKET_CONTRACT) {
$aCampaign['system'] = true;
$aCampaign['type'] = OX_Util_Utils::getCampaignType($aCampaign['priority']);
} else {
$aCampaign['type'] = OX_Util_Utils::getCampaignType($aCampaign['priority']);
}
if ($aCampaign['priority'] == -1) {
$aCampaign['priority'] = $strOverride;
} elseif ($aCampaign['priority'] == -2) {
$aCampaign['priority'] = $strCampaignECPM;
} elseif ($aCampaign['priority'] == 0) {
/**
* A method to activate/deactivate campaigns, based on the date and/or the inventory
* requirements (impressions, clicks and/or conversions). Also sends email reports
* for any campaigns that are activated/deactivated, as well as sending email reports
* for any campaigns that are likely to expire in the near future.
*
* @param Date $oDate The current date/time.
* @return string Report on the campaigns activated/deactivated.
*/
function manageCampaigns($oDate)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$oServiceLocator =& OA_ServiceLocator::instance();
$oEmail =& $oServiceLocator->get('OA_Email');
if ($oEmail === false) {
$oEmail = new OA_Email();
$oServiceLocator->register('OA_Email', $oEmail);
}
$report = "\n";
// Select all campaigns in the system, where:
// The campaign is ACTIVE and:
// - The end date stored for the campaign is not null; or
// - The campaign has a lifetime impression, click or conversion
// target set.
//
// That is:
// - It is possible for the active campaign to be automatically
// stopped, as it has a valid end date. (No limitations are
// applied to those campaigns tested, as the ME may not have
// run for a while, and if so, even campaigns with an end date
// of many, many weeks ago should be tested to ensure they are
// [belatedly] halted.)
// - It is possible for the active campaign to be automatically
// stopped, as it has at leaast one lifetime target that could
// have been reached.
//
// The campaign is INACTIVE and:
// - The start date stored for the campaign is not null; and
// - The weight is greater than zero; and
// - The end date stored for the campaign is either null, or is
// greater than "today" less one day.
//
// That is:
// - It is possible for the inactive campaign to be automatically
// started, as it has a valid start date. (No limitations are
// applied to those campaigns tested, as the ME may not have run
// for a while, and if so, even campaigns with an activation date
// of many, many weeks ago should be tested to ensure they are
// [belatedy] enabled.)
// - The campaign is not in a permanently inactive state, as a
// result of the weight being less then one, which means that
// it cannot be activated.
// - The test to start the campaign is unlikely to fail on account
// of the end date. (Inactive campaigns with start dates may have
// passed the start date, but they may also have passed the end
// date - unfortunately, because the dates are not stored in UTC,
// it's not possible to know exactly which campaigns have passed
// the end date or not, until the values are converted to UTC based
// on the Advertiser Account timezone preference - so it's necessary
// to get some campaigns that might be passed the end date, and do
// the converstion to UTC and test to check.)
$prefix = $this->getTablePrefix();
$oYesterdayDate = new Date();
$oYesterdayDate->copy($oDate);
$oYesterdayDate->subtractSeconds(SECONDS_PER_DAY);
$query = "\n SELECT\n cl.clientid AS advertiser_id,\n cl.account_id AS advertiser_account_id,\n cl.agencyid AS agency_id,\n cl.contact AS contact,\n cl.email AS email,\n cl.reportdeactivate AS send_activate_deactivate_email,\n ca.campaignid AS campaign_id,\n ca.campaignname AS campaign_name,\n ca.views AS targetimpressions,\n ca.clicks AS targetclicks,\n ca.conversions AS targetconversions,\n ca.status AS status,\n ca.activate AS start,\n ca.expire AS end\n FROM\n {$prefix}campaigns AS ca,\n {$prefix}clients AS cl\n WHERE\n ca.clientid = cl.clientid\n AND\n ca.status = " . $this->oDbh->quote(OA_ENTITY_STATUS_RUNNING, 'integer') . "\n AND\n (\n ca.expire " . OA_Dal::notEqualNoDateString() . "\n OR\n (\n ca.views > 0\n OR\n ca.clicks > 0\n OR\n ca.conversions > 0\n )\n )\n UNION ALL\n SELECT\n cl.clientid AS advertiser_id,\n cl.account_id AS advertiser_account_id,\n cl.agencyid AS agency_id,\n cl.contact AS contact,\n cl.email AS email,\n cl.reportdeactivate AS send_activate_deactivate_email,\n ca.campaignid AS campaign_id,\n ca.campaignname AS campaign_name,\n ca.views AS targetimpressions,\n ca.clicks AS targetclicks,\n ca.conversions AS targetconversions,\n ca.status AS status,\n ca.activate AS start,\n ca.expire AS end\n FROM\n {$prefix}campaigns AS ca,\n {$prefix}clients AS cl\n WHERE\n ca.clientid = cl.clientid\n AND\n ca.status != " . $this->oDbh->quote(OA_ENTITY_STATUS_RUNNING, 'integer') . "\n AND\n ca.activate " . OA_Dal::notEqualNoDateString() . "\n AND\n (\n ca.weight > 0\n OR\n ca.priority > 0\n )\n AND\n (\n ca.expire >= " . $this->oDbh->quote($oYesterdayDate->format('%Y-%m-%d'), 'timestamp') . "\n OR\n ca.expire " . OA_Dal::equalNoDateString() . "\n )\n ORDER BY\n advertiser_id";
$rsResult = $this->oDbh->query($query);
if (PEAR::isError($rsResult)) {
return MAX::raiseError($rsResult, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
OA::debug('- Found ' . $rsResult->numRows() . ' campaigns to test for activation/deactivation', PEAR_LOG_DEBUG);
while ($aCampaign = $rsResult->fetchRow()) {
if ($aCampaign['status'] == OA_ENTITY_STATUS_RUNNING) {
// The campaign is currently running, look at the campaign
$disableReason = 0;
$canExpireSoon = false;
if ($aCampaign['targetimpressions'] > 0 || $aCampaign['targetclicks'] > 0 || $aCampaign['targetconversions'] > 0) {
// The campaign has an impression, click and/or conversion target,
// so get the sum total statistics for the campaign
$query = "\n SELECT\n SUM(dia.impressions) AS impressions,\n SUM(dia.clicks) AS clicks,\n SUM(dia.conversions) AS conversions\n FROM\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['data_intermediate_ad'], true) . " AS dia,\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['banners'], true) . " AS b\n WHERE\n dia.ad_id = b.bannerid\n AND b.campaignid = {$aCampaign['campaign_id']}";
$rsResultInner = $this->oDbh->query($query);
$valuesRow = $rsResultInner->fetchRow();
if (!is_null($valuesRow['impressions']) || !is_null($valuesRow['clicks']) || !is_null($valuesRow['conversions'])) {
// There were impressions, clicks and/or conversions for this
// campaign, so find out if campaign targets have been passed
if (is_null($valuesRow['impressions'])) {
// No impressions
$valuesRow['impressions'] = 0;
}
if (is_null($valuesRow['clicks'])) {
// No clicks
$valuesRow['clicks'] = 0;
}
if (is_null($valuesRow['conversions'])) {
// No conversions
$valuesRow['conversions'] = 0;
}
if ($aCampaign['targetimpressions'] > 0) {
if ($aCampaign['targetimpressions'] <= $valuesRow['impressions']) {
// The campaign has an impressions target, and this has been
// passed, so update and disable the campaign
$disableReason |= OX_CAMPAIGN_DISABLED_IMPRESSIONS;
}
}
if ($aCampaign['targetclicks'] > 0) {
if ($aCampaign['targetclicks'] <= $valuesRow['clicks']) {
// The campaign has a click target, and this has been
// passed, so update and disable the campaign
$disableReason |= OX_CAMPAIGN_DISABLED_CLICKS;
}
}
if ($aCampaign['targetconversions'] > 0) {
if ($aCampaign['targetconversions'] <= $valuesRow['conversions']) {
// The campaign has a target limitation, and this has been
// passed, so update and disable the campaign
$disableReason |= OX_CAMPAIGN_DISABLED_CONVERSIONS;
}
}
if ($disableReason) {
// One of the campaign targets was exceeded, so disable
$message = '- Exceeded a campaign quota: Deactivating campaign ID ' . "{$aCampaign['campaign_id']}: {$aCampaign['campaign_name']}";
OA::debug($message, PEAR_LOG_INFO);
$report .= $message . "\n";
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->campaignid = $aCampaign['campaign_id'];
$doCampaigns->find();
$doCampaigns->fetch();
$doCampaigns->status = OA_ENTITY_STATUS_EXPIRED;
$result = $doCampaigns->update();
if ($result == false) {
return MAX::raiseError($rows, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
phpAds_userlogSetUser(phpAds_userMaintenance);
phpAds_userlogAdd(phpAds_actionDeactiveCampaign, $aCampaign['campaign_id']);
} else {
// The campaign didn't have a diable reason,
// it *might* possibly be diabled "soon"...
$canExpireSoon = true;
}
}
}
// Does the campaign need to be disabled due to the date?
if ($aCampaign['end'] != OA_Dal::noDateValue()) {
// The campaign has a valid end date, stored in the timezone of the advertiser;
// create an end date in the advertiser's timezone, set the time, and then
// convert to UTC so that it can be compared with the MSE run time, which is
// in UTC
$aAdvertiserPrefs = OA_Preferences::loadAccountPreferences($aCampaign['advertiser_account_id'], true);
$oTimezone = new Date_Timezone($aAdvertiserPrefs['timezone']);
$oEndDate = new Date();
$oEndDate->convertTZ($oTimezone);
$oEndDate->setDate($aCampaign['end'] . ' 23:59:59');
// Campaigns end at the end of the day
$oEndDate->toUTC();
if ($oDate->after($oEndDate)) {
// The end date has been passed; disable the campaign
$disableReason |= OX_CAMPAIGN_DISABLED_DATE;
$message = "- Passed campaign end time of '{$aCampaign['end']} 23:59:59 {$aAdvertiserPrefs['timezone']} (" . $oEndDate->format('%Y-%m-%d %H:%M:%S') . ' ' . $oEndDate->tz->getShortName() . ")': Deactivating campaign ID {$aCampaign['campaign_id']}: {$aCampaign['campaign_name']}";
OA::debug($message, PEAR_LOG_INFO);
$report .= $message . "\n";
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->campaignid = $aCampaign['campaign_id'];
$doCampaigns->find();
$doCampaigns->fetch();
$doCampaigns->status = OA_ENTITY_STATUS_EXPIRED;
$result = $doCampaigns->update();
if ($result == false) {
return MAX::raiseError($rows, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
phpAds_userlogSetUser(phpAds_userMaintenance);
phpAds_userlogAdd(phpAds_actionDeactiveCampaign, $aCampaign['campaign_id']);
} else {
// The campaign wasn't disabled based on the end
// date, to it *might* possibly be disabled "soon"...
$canExpireSoon = true;
}
}
if ($disableReason) {
// The campaign was disabled, so send the appropriate
// message to the campaign's contact
$query = "\n SELECT\n bannerid AS advertisement_id,\n description AS description,\n alt AS alt,\n url AS url\n FROM\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['banners'], true) . "\n WHERE\n campaignid = {$aCampaign['campaign_id']}";
OA::debug("- Getting the advertisements for campaign ID {$aCampaign['campaign_id']}", PEAR_LOG_DEBUG);
$rsResultAdvertisement = $this->oDbh->query($query);
if (PEAR::isError($rsResultAdvertisement)) {
return MAX::raiseError($rsResultAdvertisement, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
while ($advertisementRow = $rsResultAdvertisement->fetchRow()) {
$advertisements[$advertisementRow['advertisement_id']] = array($advertisementRow['description'], $advertisementRow['alt'], $advertisementRow['url']);
}
if ($aCampaign['send_activate_deactivate_email'] == 't') {
$oEmail->sendCampaignActivatedDeactivatedEmail($aCampaign['campaign_id'], $disableReason);
}
} else {
if ($canExpireSoon) {
// The campaign has NOT been deactivated - test to see if it will
// be deactivated "soon", and send email(s) warning of this as required
$oEmail->sendCampaignImpendingExpiryEmail($oDate, $aCampaign['campaign_id']);
}
}
} else {
// The campaign is not active - does it need to be enabled,
// based on the campaign starting date?
if ($aCampaign['start'] != OA_Dal::noDateValue()) {
// The campaign has a valid start date, stored in the timezone of the advertiser;
// create an end date in the advertiser's timezone, set the time, and then
// convert to UTC so that it can be compared with the MSE run time, which is
// in UTC
$aAdvertiserPrefs = OA_Preferences::loadAccountPreferences($aCampaign['advertiser_account_id'], true);
$oTimezone = new Date_Timezone($aAdvertiserPrefs['timezone']);
$oStartDate = new Date();
$oStartDate->convertTZ($oTimezone);
$oStartDate->setDate($aCampaign['start'] . ' 00:00:00');
// Campaigns start at the start of the day
$oStartDate->toUTC();
if ($aCampaign['end'] != OA_Dal::noDateValue()) {
// The campaign has a valid end date, stored in the timezone of the advertiser;
// create an end date in the advertiser's timezone, set the time, and then
// convert to UTC so that it can be compared with the MSE run time, which is
// in UTC
$oEndDate = new Date();
$oEndDate->convertTZ($oTimezone);
$oEndDate->setDate($aCampaign['end'] . ' 23:59:59');
// Campaign end at the end of the day
$oEndDate->toUTC();
} else {
$oEndDate = null;
}
if ($oDate->after($oStartDate)) {
// The start date has been passed; find out if there are any impression, click
// or conversion targets for the campaign (i.e. if the target values are > 0)
$remainingImpressions = 0;
$remainingClicks = 0;
$remainingConversions = 0;
if ($aCampaign['targetimpressions'] > 0 || $aCampaign['targetclicks'] > 0 || $aCampaign['targetconversions'] > 0) {
// The campaign has an impression, click and/or conversion target,
// so get the sum total statistics for the campaign so far
$query = "\n SELECT\n SUM(dia.impressions) AS impressions,\n SUM(dia.clicks) AS clicks,\n SUM(dia.conversions) AS conversions\n FROM\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['data_intermediate_ad'], true) . " AS dia,\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['banners'], true) . " AS b\n WHERE\n dia.ad_id = b.bannerid\n AND b.campaignid = {$aCampaign['campaign_id']}";
$rsResultInner = $this->oDbh->query($query);
$valuesRow = $rsResultInner->fetchRow();
// Set the remaining impressions, clicks and conversions for the campaign
$remainingImpressions = $aCampaign['targetimpressions'] - $valuesRow['impressions'];
$remainingClicks = $aCampaign['targetclicks'] - $valuesRow['clicks'];
$remainingConversions = $aCampaign['targetconversions'] - $valuesRow['conversions'];
}
// In order for the campaign to be activated, need to test:
// 1) That there is no impression target (<= 0), or, if there is an impression target (> 0),
// then there must be remaining impressions to deliver (> 0); and
// 2) That there is no click target (<= 0), or, if there is a click target (> 0),
// then there must be remaining clicks to deliver (> 0); and
// 3) That there is no conversion target (<= 0), or, if there is a conversion target (> 0),
// then there must be remaining conversions to deliver (> 0); and
// 4) Either there is no end date, or the end date has not been passed
if (($aCampaign['targetimpressions'] <= 0 || $aCampaign['targetimpressions'] > 0 && $remainingImpressions > 0) && ($aCampaign['targetclicks'] <= 0 || $aCampaign['targetclicks'] > 0 && $remainingClicks > 0) && ($aCampaign['targetconversions'] <= 0 || $aCampaign['targetconversions'] > 0 && $remainingConversions > 0) && (is_null($oEndDate) || $oEndDate->format('%Y-%m-%d') != OA_Dal::noDateValue() && Date::compare($oDate, $oEndDate) < 0)) {
$message = "- Passed campaign start time of '{$aCampaign['start']} 00:00:00 {$aAdvertiserPrefs['timezone']} (" . $oStartDate->format('%Y-%m-%d %H:%M:%S') . ' ' . $oStartDate->tz->getShortName() . ")': Activating campaign ID {$aCampaign['campaign_id']}: {$aCampaign['campaign_name']}";
OA::debug($message, PEAR_LOG_INFO);
$report .= $message . "\n";
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->campaignid = $aCampaign['campaign_id'];
$doCampaigns->find();
$doCampaigns->fetch();
$doCampaigns->status = OA_ENTITY_STATUS_RUNNING;
$result = $doCampaigns->update();
if ($result == false) {
return MAX::raiseError($rows, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
phpAds_userlogSetUser(phpAds_userMaintenance);
phpAds_userlogAdd(phpAds_actionActiveCampaign, $aCampaign['campaign_id']);
// Get the advertisements associated with the campaign
$query = "\n SELECT\n bannerid AS advertisement_id,\n description AS description,\n alt AS alt,\n url AS url\n FROM\n " . $this->oDbh->quoteIdentifier($aConf['table']['prefix'] . $aConf['table']['banners'], true) . "\n WHERE\n campaignid = {$aCampaign['campaign_id']}";
OA::debug("- Getting the advertisements for campaign ID {$aCampaign['campaign_id']}", PEAR_LOG_DEBUG);
$rsResultAdvertisement = $this->oDbh->query($query);
if (PEAR::isError($rsResultAdvertisement)) {
return MAX::raiseError($rsResultAdvertisement, MAX_ERROR_DBFAILURE, PEAR_ERROR_DIE);
}
while ($advertisementRow = $rsResultAdvertisement->fetchRow()) {
$advertisements[$advertisementRow['advertisement_id']] = array($advertisementRow['description'], $advertisementRow['alt'], $advertisementRow['url']);
}
if ($aCampaign['send_activate_deactivate_email'] == 't') {
$oEmail->sendCampaignActivatedDeactivatedEmail($aCampaign['campaign_id']);
}
}
}
}
}
}
}
/**
* Fetch the history stats using the specified parameters
*
* @param array $aParams Query parameters
* @param string $link Optional link for the leftmost column content
*/
function getHistory($aParams, $link = '')
{
$oNow = new Date();
$aParams['tz'] = $oNow->tz->getID();
$method = $this->oHistory->setBreakdownInfo($this);
// Add plugin aParams
$pluginParams = array();
foreach ($this->aPlugins as $oPlugin) {
$oPlugin->addQueryParams($pluginParams);
}
$aStats = Admin_DA::fromCache($method, $aParams + $this->aDates + $pluginParams);
// Merge plugin additional $oPlugin
foreach ($this->aPlugins as $oPlugin) {
$oPlugin->mergeData($aStats, $method, $aParams + $this->aDates, $this->aEmptyRow);
}
if (count($aStats) == 0) {
$this->noStatsAvailable = true;
return $aStats;
}
// Fill unused plugins columns
foreach (array_keys($aStats) as $k) {
$aStats[$k] += $this->aEmptyRow;
}
// Set some of the variables that used to be set by getSpan
if (!empty($aStats)) {
$dates = array_keys($aStats);
// assumes first row has earliest date
$firstDate = new Date($dates[0]);
// Convert to current TZ
$firstDate->setTZbyID('UTC');
$firstDate->convertTZ($oNow->tz);
$firstDate->setHour(0);
$firstDate->setMinute(0);
$firstDate->setSecond(0);
if (empty($this->aDates)) {
$this->aDates['day_begin'] = $firstDate->format('%Y-%m-%d');
$this->aDates['day_end'] = $oNow->format('%Y-%m-%d');
}
$this->oStartDate = new Date($firstDate);
}
$aDates = $this->oHistory->getDatesArray($this->aDates, $this->statsBreakdown, $this->oStartDate);
$this->oHistory->fillGapsAndLink($aStats, $aDates, $this, $link);
if (!in_array($this->listOrderField, array_merge(array($this->statsBreakdown), array_keys($this->aColumns)))) {
$this->listOrderField = $this->statsBreakdown;
$this->listOrderDirection = $this->statsBreakdown == 'hour' || $this->statsBreakdown == 'dow' ? 'up' : 'down';
}
// If required, re-format the data in the weekly breakdown format
if ($this->statsBreakdown == 'week') {
$this->oHistory->prepareWeekBreakdown($aStats, $this);
}
MAX_sortArray($aStats, $this->listOrderField, $this->listOrderDirection == 'up');
// Summarise the values into a the totals array, & format
$this->_summariseTotalsAndFormat($aStats, true);
return $aStats;
}
/**
* A private method to calcuate the number of impressions an advertisement needs to deliver
* in the next operation interval, based on the total number of impressions the ad needs to
* deliver over the rest of the campaigns, the operaion intervals the ad will be active
* in, and the average zone pattern of the zones the ad is linked to.
*
* @access private
* @param OA_Maintenance_Priority_Ad $oAd An ad object, representing the advertisement.
* @param integer $totalRequiredAdImpressions The total number of impressions the advertisement
* needs to deliver.
* @param PEAR::Date $oDate A Date object, set in the current operation interval.
* @param PEAR::Date $oCampaignExpiryDate A Date object representing the end of the advertisement's
* parent campaign.
* @param OA_Maintenance_Priority_DeliveryLimitation $oDeliveryLimitation The delivery limitation
* object for the ad.
* @param array $aAdZones An array of arrays, no particular index in the outer array, in the
* inner arrays, each as an index "zone_id" containing one zone ID that
* the ad is linked to.
* @return integer The number of impressions the advertisement should deliver in the next
* operation interval.
*/
function _getAdImpressions($oAd, $totalRequiredAdImpressions, $oDate, $oCampaignExpiryDate, $oDeliveryLimitation, $aAdZones)
{
// Check the parameters, and return 0 impressions if not valid
if (!is_a($oAd, 'OA_Maintenance_Priority_Ad') || !is_numeric($totalRequiredAdImpressions) || !is_a($oDate, 'Date') || !is_a($oCampaignExpiryDate, 'Date') || !is_a($oDeliveryLimitation, 'OA_Maintenance_Priority_DeliveryLimitation') || !is_array($aAdZones) || empty($aAdZones)) {
OA::debug('- Invalid parameters in _getAdImpressions, skipping...', PEAR_LOG_ERR);
return 0;
}
// This part must be run using the agency timezone
$oStart = new Date($oDate);
$oStart->convertTZ($this->currentTz);
$oEnd = new Date($oCampaignExpiryDate);
$oEnd->convertTZ($this->currentTz);
if ($oDeliveryLimitation->deliveryBlocked($oStart) == true) {
// The advertisement is not currently able to deliver, and so
// no impressions should be allocated for this operation interval
return 0;
}
// Get the cumulative associated zones forecasts for the previous week's
// zone inventory forecasts, keyed by the operation interval ID
$aCumulativeZoneForecast = $this->_getCumulativeZoneForecast($oAd->id, $aAdZones);
// Get the total number of zone impressions remaining in which this
// ad is capable of delivering (taking into account any operation
// intervals where the ad is blocked)
$totalAdLifetimeZoneImpressionsRemaining = $oDeliveryLimitation->getAdLifetimeZoneImpressionsRemaining($oStart, $oEnd, $aCumulativeZoneForecast);
// Are there impressions forecast?
if ($totalAdLifetimeZoneImpressionsRemaining == 0) {
return 0;
}
// Get the current operation interval ID
$currentOperationIntervalID = OX_OperationInterval::convertDateToOperationIntervalID($oDate);
// Scale the total required impressions for the ad over its lifetime
// into the current operation interval forecast, relative to the total
// zone-pattern based forecast for the remaining lifetime of the ad
$scale = $aCumulativeZoneForecast[$currentOperationIntervalID] / $totalAdLifetimeZoneImpressionsRemaining;
$impressions = $totalRequiredAdImpressions * $scale;
return round($impressions);
}
if (!empty($data['expire_time'])) {
$oExpireDate = new Date($data['expire_time']);
$oTz = $oExpireDate->tz;
$oExpireDate->setTZbyID('UTC');
$oExpireDate->convertTZ($oTz);
$campaign['expire_f'] = $oExpireDate->format($date_format);
$campaign['expire_date'] = $oExpireDate->format('%Y-%m-%d');
}
$campaign['status'] = $doCampaigns->status;
$campaign['an_status'] = $doCampaigns->an_status;
$campaign['as_reject_reason'] = $doCampaigns->as_reject_reason;
if (!empty($data['activate_time'])) {
$oActivateDate = new Date($data['activate_time']);
$oTz = $oActivateDate->tz;
$oActivateDate->setTZbyID('UTC');
$oActivateDate->convertTZ($oTz);
$campaign['activate_f'] = $oActivateDate->format($date_format);
$campaign['activate_date'] = $oActivateDate->format('%Y-%m-%d');
}
$campaign['priority'] = $data['priority'];
$campaign['weight'] = $data['weight'];
$campaign['target_impression'] = $data['target_impression'];
$campaign['target_click'] = $data['target_click'];
$campaign['target_conversion'] = $data['target_conversion'];
$campaign['min_impressions'] = $data['min_impressions'];
$campaign['ecpm'] = OA_Admin_NumberFormat::formatNumber($data['ecpm'], 4);
$campaign['anonymous'] = $data['anonymous'];
$campaign['companion'] = $data['companion'];
$campaign['show_capped_no_cookie'] = $data['show_capped_no_cookie'];
$campaign['comments'] = $data['comments'];
$campaign['revenue'] = OA_Admin_NumberFormat::formatNumber($data['revenue'], 4);
/**
* Returns an array of conversions.
*
* @param array $aParams
* @return array
*/
function getConversions($aParams)
{
$conf = $GLOBALS['_MAX']['CONF'];
$oDbh =& OA_DB::singleton();
$where = '';
if (!empty($aParams['day'])) {
$aParams['day_begin'] = $aParams['day_end'] = $aParams['day'];
}
if (!empty($aParams['day_begin'])) {
$oStart = new Date($aParams['day_begin']);
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oStart->toUTC();
$where .= ' AND ac.tracker_date_time >= ' . $oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
}
if (!empty($aParams['day_end'])) {
$oEnd = new Date($aParams['day_end']);
$oEnd->setHour(23);
$oEnd->setMinute(59);
$oEnd->setSecond(59);
$oEnd->toUTC();
$where .= ' AND ac.tracker_date_time <= ' . $oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
}
if (!empty($aParams['month'])) {
$oStart = new Date("{$aParams['month']}-01");
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date(Date_Calc::beginOfNextMonth($oStart->getDay(), $oStart->getMonth, $oStart->getYear(), '%Y-%m-%d'));
$oEnd->setHour(0);
$oEnd->setMinute(0);
$oEnd->setSecond(0);
$oEnd->subtractSeconds(1);
$oStart->toUTC();
$oEnd->toUTC();
$where .= ' AND ac.tracker_date_time >= ' . $oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
$where .= ' AND ac.tracker_date_time <= ' . $oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
}
if (!empty($aParams['day_hour'])) {
$oStart = new Date("{$aParams['day_hour']}:00:00");
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date($oStart);
$oStart->setMinute(59);
$oStart->setSecond(59);
$where .= ' AND ac.tracker_date_time >= ' . $oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
$where .= ' AND ac.tracker_date_time <= ' . $oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S'), 'timestamp');
}
if (!empty($aParams['agency_id'])) {
$where .= ' AND c.agencyid=' . $oDbh->quote($aParams['agency_id'], 'integer');
}
if (!empty($aParams['clientid'])) {
$where .= ' AND c.clientid=' . $oDbh->quote($aParams['clientid'], 'integer');
}
if (isset($aParams['zonesIds'])) {
$where .= ' AND ac.zone_id IN (' . $oDbh->escape(implode(',', $aParams['zonesIds'])) . ")";
}
if (!empty($aParams['campaignid'])) {
$where .= ' AND m.campaignid=' . $oDbh->quote($aParams['campaignid'], 'integer');
}
if (!empty($aParams['bannerid'])) {
$where .= ' AND d.bannerid=' . $oDbh->quote($aParams['bannerid'], 'integer');
}
if (!empty($aParams['statuses'])) {
$where .= ' AND ac.connection_status IN (' . $oDbh->escape(implode(',', $aParams['statuses'])) . ')';
}
if (isset($aParams['startRecord']) && is_numeric($aParams['startRecord']) && is_numeric($aParams['perPage'])) {
$limit = ' LIMIT ' . $oDbh->quote($aParams['perPage'], 'text', false) . ' OFFSET ' . $oDbh->quote($aParams['startRecord'], 'text', false);
} elseif (!empty($aParams['perPage'])) {
$limit = ' LIMIT ' . $oDbh->quote($aParams['perPage'], 'integer', false) . ' OFFSET 0';
} else {
$limit = '';
}
$query = "SELECT\n ac.data_intermediate_ad_connection_id as connection_id,\n c.clientid,\n m.campaignid,\n m.campaignname AS campaignname,\n ac.tracker_id as tracker_id,\n ac.connection_status,\n ac.connection_date_time AS connection_date_time,\n ac.tracker_date_time as date_time,\n t.trackername,\n ac.tracker_ip_address,\n ac.tracker_country,\n ac.connection_action,\n t.type AS connection_type,\n ac.tracker_country,\n ac.ad_id,\n ac.creative_id,\n ac.zone_id,\n ac.comments\n FROM\n {$conf['table']['prefix']}{$conf['table']['clients']} AS c,\n {$conf['table']['prefix']}{$conf['table']['data_intermediate_ad_connection']} AS ac,\n {$conf['table']['prefix']}{$conf['table']['banners']} AS d,\n {$conf['table']['prefix']}{$conf['table']['campaigns']} AS m,\n {$conf['table']['prefix']}{$conf['table']['trackers']} AS t\n WHERE\n c.clientid=m.clientid\n AND m.campaignid=d.campaignid\n AND d.bannerid=ac.ad_id\n AND t.trackerid=ac.tracker_id\n AND ac.inside_window = 1\n " . $where . "\n ORDER BY\n ac.tracker_date_time\n {$limit}";
$aStats = $oDbh->queryAll($query, null, MDB2_FETCHMODE_DEFAULT, true);
$oNow = new Date();
foreach (array_keys($aStats) as $k) {
$oDate = new Date($aStats[$k]['date_time']);
$oDate->setTZbyID('UTC');
$oDate->convertTZ($oNow->tz);
$aStats[$k]['date_time'] = $oDate->format('%Y-%m-%d %H:%M:%S');
$oDate = new Date($aStats[$k]['connection_date_time']);
$oDate->setTZbyID('UTC');
$oDate->convertTZ($oNow->tz);
$aStats[$k]['connection_date_time'] = $oDate->format('%Y-%m-%d %H:%M:%S');
}
return $aStats;
}
/**
* A method to determine how long it will be until a campaign "expires".
*
* Returns the earliest possible date from the following values:
* - The campaign's expiration date, if set.
* - The eStimated expiration date based on lifetime impression delivery
* rate, if applicable.
* - The eStimated expiration date based on lifetime click delivery rate
* if applicable.
* - The eStimated expiration date based on lifetime conversion rate,
* if applicable.
*
* Usage:
* $desc = $dalCampaigns->getDaysLeftString($campaignid);
*
* Where:
* $desc is a string to display giving how the expiration was calculated
* eg. "Estimated expiration", or that there is no expiration date
*
* @param integer $campaignId The campaign ID.
* @return string
*/
function getDaysLeftString($campaignId)
{
global $date_format, $strNoExpiration, $strDaysLeft, $strEstimated, $strExpirationDate, $strNoExpirationEstimation, $strDaysAgo, $strCampaignStop;
$prefix = $this->getTablePrefix();
// Define array to store possible expiration date results
$aExpiration = array();
// Get the campaign target info
$now = OA::getNow('Y-m-d');
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->selectAdd("views AS impressions");
$doCampaigns->get($campaignId);
$aCampaignData = $doCampaigns->toArray();
if (!empty($aCampaignData['expire_time'])) {
$oNow = new Date($now);
$oNow->setHour(0);
$oNow->setMinute(0);
$oNow->setSecond(0);
$oDate = new Date($aCampaignData['expire_time']);
$oDate->setTZbyID('UTC');
$oDate->convertTZ($oNow->tz);
$oDate->setHour(0);
$oDate->setMinute(0);
$oDate->setSecond(0);
$oSpan = new Date_Span();
$oSpan->setFromDateDiff($oNow, $oDate);
$aCampaignData['expire_f'] = $oDate->format($date_format);
$aCampaignData['days_left'] = $oSpan->toDays() * ($oDate->before($oNow) ? -1 : 1);
}
$oDbh = OA_DB::singleton();
$tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
$tableD = $oDbh->quoteIdentifier($prefix . 'data_intermediate_ad', true);
// Define array to return the expiration dates (if they exist)
$aReturn = array('estimatedExpiration' => '', 'campaignExpiration' => '');
// Does the campaign have lifetime impression targets?
// If yes, try to get a stimated expiration date
if ($aCampaignData['impressions'] > 0) {
$query = "\n \t SELECT\n \t SUM(dia.impressions) AS delivered,\n \t DATE_FORMAT(MIN(dia.date_time), '%Y-%m-%d') AS day_of_first\n \t FROM\n \t {$tableD} AS dia,\n \t {$tableB} AS b\n \t WHERE\n \t dia.ad_id = b.bannerid\n \t AND\n \t b.campaignid = " . DBC::makeLiteral($campaignId);
$rsImpressions = DBC::FindRecord($query);
if ($rsImpressions) {
$aImpressions = $rsImpressions->toArray();
// Get the number of days until the campaign will end
// based on the impression target delivery data
$aExpiration = $this->_calculateRemainingDays($aImpressions, $aCampaignData['impressions']);
}
} elseif ($aCampaignData['clicks'] > 0) {
$query = "\n \t SELECT\n \t SUM(dia.clicks) AS delivered,\n \t DATE_FORMAT(MIN(dia.date_time), '%Y-%m-%d') AS day_of_first\n \t FROM\n \t {$tableD} AS dia,\n \t {$tableB} AS b\n \t WHERE\n \t dia.ad_id = b.bannerid\n \t AND\n \t b.campaignid = " . DBC::makeLiteral($campaignId);
$rsClicks = DBC::FindRecord($query);
if ($rsClicks) {
$aClicks = $rsClicks->toArray();
// Get the number of days until the campaign will end
// based on the click target delivery data
$aExpiration = $this->_calculateRemainingDays($aClicks, $aCampaignData['clicks']);
}
} elseif ($aCampaignData['conversions'] > 0) {
$query = "\n \t SELECT\n \t SUM(dia.conversions) AS delivered,\n \t DATE_FORMAT(MIN(dia.date_time), '%Y-%m-%d') AS day_of_first\n \t FROM\n \t {$tableD} AS dia,\n \t {$tableB} AS b\n \t WHERE\n \t dia.ad_id = b.bannerid\n \t AND\n \t b.campaignid = " . DBC::makeLiteral($campaignId);
$rsConversions = DBC::FindRecord($query);
if ($rsConversions) {
$aConversions = $rsConversions->toArray();
// Get the number of days until the campaign will end
// based on the conversion target delivery data
$aExpiration = $this->_calculateRemainingDays($aConversions, $aCampaignData['conversions']);
}
}
// flags to control if the campaign expiration date and
// the estimated expiration date are going to be showed
$existExpirationDate = false;
$showEtimatedDate = false;
// is there a expiration date?
if (!empty($aCampaignData['expire_time'])) {
$existExpirationDate = true;
}
if ($existExpirationDate) {
// has the expiration date been reached?
if ((int) $aCampaignData['days_left'] < 0) {
$aReturn['campaignExpiration'] = $strCampaignStop . ": " . $aCampaignData['expire_f'];
$aReturn['campaignExpiration'] = $aReturn['campaignExpiration'] . " (" . abs((int) round($aCampaignData['days_left'])) . " {$strDaysAgo})";
} else {
$aReturn['campaignExpiration'] = $strExpirationDate . ": " . $aCampaignData['expire_f'];
$aReturn['campaignExpiration'] = $aReturn['campaignExpiration'] . " (" . $strDaysLeft . ": " . round($aCampaignData['days_left']) . ")";
}
} else {
$aReturn['campaignExpiration'] = $strNoExpiration;
}
// There is a estimated expiration date?
// If yes, check if the campaign expiration date is set up and compare
// both expiration dates to show only relevant estimated expiration dates
if (!empty($aExpiration)) {
if ($existExpirationDate == true) {
if (round($aCampaignData['days_left']) >= 0) {
$campaignExpirationDate = new Date($aCampaignData['expire_time']);
$aExpiration['date']->hour = 0;
$aExpiration['date']->minute = 0;
$aExpiration['date']->second = 0;
$aExpiration['date']->partsecond = 0;
$compareDate = Date::compare($aExpiration['date'], $campaignExpirationDate);
// the estimated expiration date is previous or equal to the
// campaign expiration date and hasn't the expiration date been reached?
if ($compareDate <= 0 && (int) $aCampaignData['days_left'] >= 0) {
$showEtimatedDate = true;
}
}
} else {
$showEtimatedDate = true;
}
} elseif ($existExpirationDate && round($aCampaignData['days_left']) >= 0 || !$existExpirationDate) {
$aReturn['estimatedExpiration'] = $strEstimated . ": " . $strNoExpirationEstimation;
}
if ($showEtimatedDate) {
$aExpiration['daysLeft'] = phpAds_formatNumber($aExpiration['daysLeft']);
$aReturn['estimatedExpiration'] = $strEstimated . ": " . $aExpiration['date_f'] . " (" . $strDaysLeft . ": " . $aExpiration['daysLeft'] . ")";
}
return $aReturn;
}
/**
* A private method to prepare an array of connections and variable values for the
* report.
*
* @access private
* @return array An array with the following format:
* array(
* $trackerId => array(
* 'connections' => array(
* $connectionId => array(
* 'data_intermediate_ad_connection_id' => Integer: The conversion ID
* 'tracker_date_time' => Timestamp: The date/time of the conversion
* 'tracker_day' => String: The day of the conversion in YYYY-MM-DD format
* 'connection_date_time' => Timestamp: The date/time of the ad impression/click
* 'connection_status' => Integer: The status of the connection
* 'connection_channel' => Integer: The channel ID of the ad impression/click
* 'connection_action' => Integer: If it was an ad impression or click
* 'connection_ip_address' => String: The IP address of the ad impression/click
* 'connection_country' => String: The country of the ad impression/click
* 'connection_domain' => String: The domain of the ad impression/click
* 'connection_language' => String: The language of the ad impression/click
* 'connection_os' => String: The operating system of the ad impression/click
* 'connection_browser' => String: The browser of the ad impression/click
* 'connection_comments' => String: Any comments associated with the connection
* 'advertiser_id' => Integer: The advertiser ID of the ad impression/click
* 'advertiser_name' => String: The name of the advertiser of the ad impression/click
* 'placement_id' => Integer: The placement ID of the ad impression/click
* 'placement_name' => String: The name of the placement of the ad impression/click
* 'ad_id' => Integer: The ad ID of the ad impression/click
* 'ad_name' => String: The name of the ad of the ad impression/click
* 'ad_alt' => String: The alt. name of the ad of the ad impression/click
* 'publisher_id' => Integer: The publisher ID of the ad impression/click
* 'publisher_name' => String: The name of the publisher of the ad impression/click
* 'zone_id' => Integer: The zone ID of the ad impression/click
* 'zone_name' => String: The name of the zone of the ad impression/click
* 'tracker_id' => Integer: The tracker ID for the conversion
* 'variables' => array(
* $trackerVariableId => array(
* tracker_variable_id => Integer: The tracker variable ID
* tracker_variable_value => Mixed: The tracker variable value for the conversion
* )
* )
* )
* )
* )
* )
*/
function _prepareConnections()
{
$aConnections = array();
$aConf = $GLOBALS['_MAX']['CONF'];
// Prepare the start and end dates for the conversion range
$oDaySpan = new OA_Admin_DaySpan();
$oDaySpan->setSpanDays($this->_oDaySpan->oStartDate, $this->_oDaySpan->oEndDate);
$oDaySpan->toUTC();
$startDateString = $oDaySpan->getStartDateString('%Y-%m-%d %H:%M:%S');
$endDateString = $oDaySpan->getEndDateString('%Y-%m-%d %H:%M:%S');
// Prepare the agency/advertiser/publisher limitations
$agencyId = $this->_oScope->getAgencyId();
$advertiserId = $this->_oScope->getAdvertiserId();
$publisherId = $this->_oScope->getPublisherId();
// Prepare the query to select the required conversions and variable values
$query = "\n SELECT\n diac.data_intermediate_ad_connection_id AS data_intermediate_ad_connection_id,\n diac.tracker_date_time AS tracker_date_time,\n diac.tracker_id AS tracker_id,\n diac.connection_date_time AS connection_date_time,\n diac.connection_status AS connection_status,\n diac.connection_channel AS connection_channel,\n diac.connection_action AS connection_action,\n diac.tracker_ip_address AS connection_ip_address,\n diac.tracker_country AS connection_country,\n diac.tracker_domain AS connection_domain,\n diac.tracker_language AS connection_language,\n diac.tracker_os AS connection_os,\n diac.tracker_browser AS connection_browser,\n diac.comments AS connection_comments,\n z.zoneid AS zone_id,\n z.zonename AS zone_name,\n p.affiliateid AS publisher_id,\n p.name AS publisher_name,\n a.clientid AS advertiser_id,\n a.clientname AS advertiser_name,\n c.campaignid AS placement_id,\n c.campaignname AS campaign_name,\n b.bannerid AS ad_id,\n b.description AS ad_name,\n b.alt AS ad_alt,\n diavv.tracker_variable_id AS tracker_variable_id,\n diavv.value AS tracker_variable_value\n FROM\n {$aConf['table']['prefix']}{$aConf['table']['data_intermediate_ad_connection']} AS diac\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['banners']} AS b\n ON\n (\n diac.ad_id = b.bannerid\n )\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['campaigns']} AS c\n ON\n (\n b.campaignid = c.campaignid\n )\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['clients']} AS a\n ON\n (\n c.clientid = a.clientid\n )\n LEFT JOIN\n {$aConf['table']['prefix']}{$aConf['table']['zones']} AS z\n ON\n (\n diac.zone_id = z.zoneid\n )\n LEFT JOIN\n {$aConf['table']['prefix']}{$aConf['table']['affiliates']} AS p\n ON\n (\n z.affiliateid = p.affiliateid\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 )\n WHERE\n diac.tracker_date_time >= " . DBC::makeLiteral($startDateString, 'string') . "\n AND\n diac.tracker_date_time <= " . DBC::makeLiteral($endDateString, 'string') . "\n AND\n diac.inside_window = 1";
if ($agencyId) {
$query .= "\n AND\n a.agencyid = " . DBC::makeLiteral($agencyId, 'integer');
}
if ($advertiserId) {
$query .= "\n AND\n a.clientid = " . DBC::makeLiteral($advertiserId, 'integer');
}
if ($publisherId) {
$query .= "\n AND\n z.affiliateid = " . DBC::makeLiteral($publisherId, 'integer');
}
$query .= "\n ORDER BY\n tracker_id,\n data_intermediate_ad_connection_id";
// Select the conversions in the report
$rsConversions = DBC::NewRecordSet($query);
$rsConversions->find();
while ($rsConversions->fetch()) {
$aConversion = $rsConversions->toArray();
$trackerId = $aConversion['tracker_id'];
$connectionId = $aConversion['data_intermediate_ad_connection_id'];
// Does this tracker/connection pair exist in the result array already?
// It might, due to multiple attached variable values...
if (!isset($aConnections[$trackerId]['connections'][$connectionId])) {
// It's not set, store the connection details
$oTrackerDate = new Date($aConversion['tracker_date_time']);
$oTrackerDate->setTZbyID('UTC');
$oTrackerDate->convertTZ($this->_oDaySpan->oStartDate->tz);
$oConnectionDate = new Date($aConversion['connection_date_time']);
$oConnectionDate->setTZbyID('UTC');
$oConnectionDate->convertTZ($this->_oDaySpan->oStartDate->tz);
$aConnections[$trackerId]['connections'][$connectionId] = array('data_intermediate_ad_connection_id' => $connectionId, 'tracker_date_time' => $oTrackerDate->format('%Y-%m-%d %H:%M:%S'), 'tracker_day' => $oTrackerDate->format('%Y-%m-%d'), 'connection_date_time' => $oConnectionDate->format('%Y-%m-%d %H:%M:%S'), 'connection_status' => $aConversion['connection_status'], 'connection_channel' => $aConversion['connection_channel'], 'connection_action' => $aConversion['connection_action'], 'connection_ip_address' => $aConversion['connection_ip_address'], 'connection_country' => $aConversion['connection_country'], 'connection_domain' => $aConversion['connection_domain'], 'connection_language' => $aConversion['connection_language'], 'connection_os' => $aConversion['connection_os'], 'connection_browser' => $aConversion['connection_browser'], 'connection_comments' => $aConversion['connection_comments'], 'advertiser_id' => $aConversion['advertiser_id'], 'advertiser_name' => $aConversion['advertiser_name'], 'placement_id' => $aConversion['placement_id'], 'placement_name' => $aConversion['placement_name'], 'ad_id' => $aConversion['ad_id'], 'ad_name' => $aConversion['ad_name'], 'ad_alt' => $aConversion['ad_alt'], 'publisher_id' => $aConversion['publisher_id'], 'publisher_name' => $aConversion['publisher_name'], 'zone_id' => $aConversion['zone_id'], 'zone_name' => $aConversion['zone_name'], 'tracker_id' => $aConversion['tracker_id']);
}
// Store the variable value associated with this connection, if one exists
$trackerVariableId = $aConversion['tracker_variable_id'];
if (!empty($trackerVariableId)) {
$aConnections[$trackerId]['connections'][$connectionId]['variables'][$trackerVariableId] = array('tracker_variable_id' => $trackerVariableId, 'tracker_variable_value' => $aConversion['tracker_variable_value']);
}
}
// Return the connections
return $aConnections;
}
function convertStartEndDate(&$oStartDate, &$oEndDate, $oTimezone)
{
if (isset($oStartDate)) {
$oStartTz = new Date($oStartDate);
$oStartTz->convertTZ($oTimezone);
$oStartTz->setHour(0);
$oStartTz->setMinute(0);
$oStartTz->setSecond(0);
if ($oStartTz->after($oStartDate)) {
$oStartTz->subtractSpan(new Date_Span('1-0-0-0'));
}
} else {
$oStartTz = null;
}
if (!isset($oEndDate)) {
$oEndDate = new Date();
}
$oEndTz = new Date($oEndDate);
$oEndTz->convertTZ($oTimezone);
$oEndTz->setHour(0);
$oEndTz->setMinute(0);
$oEndTz->setSecond(0);
$oEndTz->subtractSeconds(1);
if ($oEndTz->after($oEndDate)) {
$oEndTz->subtractSpan(new Date_Span('1-0-0-0'));
}
$oStartDate = $oStartTz;
$oEndDate = $oEndTz;
}
/**
* requires permission checks
*
* @param array $aParam
* @return array
*/
function getAuditLogForAuditWidget($aParam = array())
{
$oAudit = OA_Dal::factoryDO('audit');
// Apply account level filters
if (!empty($aParam['account_id'])) {
$oAudit->account_id = $aParam['account_id'];
}
if (!empty($aParam['advertiser_account_id'])) {
$oAudit->advertiser_account_id = $aParam['advertiser_account_id'];
}
if (!empty($aParam['website_account_id'])) {
$oAudit->website_account_id = $aParam['website_account_id'];
}
$oDate = new Date();
$oDate->toUTC();
$oDate->subtractSpan(new Date_Span('7-0-0-0'));
$oAudit->whereAdd("username <> 'Maintenance'");
$oAudit->whereAdd('parentid IS NULL');
$oAudit->whereAdd("updated >= " . DBC::makeLiteral($oDate->format('%Y-%m-%d %H:%M:%S')));
$oAudit->orderBy('auditid DESC');
$oAudit->limit(0, 5);
$numRows = $oAudit->find();
$oNow = new Date();
$aResult = array();
while ($oAudit->fetch()) {
$aAudit = $oAudit->toArray();
$oDate = new Date($aAudit['updated']);
$oDate->setTZbyID('UTC');
$oDate->convertTZ($oNow->tz);
$aAudit['updated'] = $oDate->format('%Y-%m-%d %H:%M:%S');
$aAudit['details'] = unserialize($aAudit['details']);
$aAudit['context'] = $this->getContextDescription($aAudit['context']);
$aResult[] = $aAudit;
}
return $aResult;
}
function convertTimeToUTC($value, $format = '')
{
// if the field is date/time and is "empty", then it's false
if (preg_match('/^00:00(?::00)?/', $value) || preg_match('/^0000-00-00 00:00(?::00)?/', $value)) {
$value = false;
}
// no SITE_TIME_ZONE is specified
if ($value) {
$utc_tz = new Date_TimeZone('UTC');
$client_tz =& NDate::getClientTZ();
// if no format is specified, then it's a best guess
if (!$format) {
switch (1 == 1) {
case preg_match('/^\\d\\d\\d\\d-\\d\\d-\\d\\d \\d\\d:\\d\\d(?::\\d\\d)?$/', $value):
$format = '%Y-%m-%d %H:%M:%S';
break;
case preg_match('/^\\d\\d\\d\\d-\\d\\d-\\d\\d$/', $value):
$format = '%Y-%m-%d';
break;
case preg_match('/^\\d\\d:\\d\\d(?::\\d\\d)?$/', $value):
$format = '%H:%M:%S';
break;
default:
$format = '%Y-%m-%d %H:%M:%S';
}
}
$dateobj = new Date($value);
$dateobj->setTZ($client_tz);
$dateobj->convertTZ($utc_tz);
// if it's an int, then assume it's a DATE_FORMAT constant
if (is_int($format)) {
$value = $dateobj->getDate($format);
} else {
$value = $dateobj->format($format);
}
unset($server_tz);
unset($client_tz);
unset($dateobj);
}
return $value;
}
/**
* A private method to create an OA_Admin_DaySpan object with the campaign's
* start and end date values.
*
* @access private
* @param array $aCampaignData An array of campaign data.
* @return OA_Admin_DaySpan The date range of the campaign's activation date and
* expiry date.
*/
function &_rangeFromCampaign($aCampaignData)
{
$oCampaignDaySpan = new OA_Admin_DaySpan();
$oDate = new Date();
$oBeginDate = new Date($aCampaignData['campaign_start']);
$oEndDate = new Date($aCampaignData['campaign_end']);
$oBeginDate->setTzByID('UTC');
$oEndDate->setTzByID('UTC');
$oBeginDate->convertTZ($oDate->tz);
$oEndDate->convertTZ($oDate->tz);
$oCampaignDaySpan->setSpanDays($oBeginDate, $oEndDate);
return $oCampaignDaySpan;
}
/**
* A method that runs the supplied query and returns data grouped by day either in UTC or manager's TZ
*
* The query needs to return "day" and "hour" fields. Any other field will be aggregated with a SUM()
*
* @param string $query
* @param bool $localTZ
* @return array
*/
function getDailyStatsAsArray($query, $localTZ = false)
{
$oTz = $this->getTimeZone($localTZ);
if ($oTz->getShortName() == 'UTC') {
// Disable TZ conversion
$oTz = false;
} else {
$oUTC = new Date_TimeZone('UTC');
}
$aResult = array();
$oResult = $this->oDbh->query($query);
while ($aRow = $oResult->fetchRow()) {
if ($oTz) {
$oDate = new Date($aRow['day']);
$oDate->setHour($aRow['hour']);
$oDate->setTZ($oUTC);
$oDate->convertTZ($oTz);
$aRow['day'] = $oDate->format('%Y-%m-%d');
}
// Remove day & hour
unset($aRow['hour']);
// Add entry
if (!isset($aResult[$aRow['day']])) {
$aResult[$aRow['day']] = $aRow;
} else {
foreach ($aRow as $k => $v) {
// Perform SUM() GROUP BY day
if ($k == 'day') {
continue;
}
$aResult[$aRow['day']][$k] += $v;
}
}
}
return array_values($aResult);
}