/** * 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->selectAdd("DATE_FORMAT(expire, '{$date_format}') as expire_f"); $doCampaigns->selectAdd("TO_DAYS(expire) - TO_DAYS('{$now}') as days_left"); $doCampaigns->get($campaignId); $aCampaignData = $doCampaigns->toArray(); $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_f']) && !OA_Dal::isNullDate($aCampaignData['expire'])) { $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']); $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; }