/** * A private method to obtain the raw delivery performance data for a given date range. * * @access private * @param Admin_UI_OrganisationScope $oScope The Admin_UI_OrganisationScope limitation object for * the report. * @param OA_Admin_DaySpan $oDaySpan The OA_Admin_DaySpan day range limitation object for the report, * or for "yesterday" or "today" as required. * @param boolean $spanIsForPlacementDates If true, $oDaySpan is used for the start/end date limitaion * of the placements, otherwise it is used to limit the * data to delivery that happened in the $oDaySpan range. * @return array */ function _getDeliveryPerformanceDataRange($oScope, $oDaySpan, $spanIsForPlacementDates = false, $statsTable = false, $appendSqlWhere = false) { $aConf = $GLOBALS['_MAX']['CONF']; if (empty($appendSqlWhere)) { $appendSqlWhere = "AND c.type = " . DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT . " "; } if ($statsTable === false) { $statsTable = $aConf['table']['prefix'] . $aConf['table']['data_summary_ad_hourly']; } $advertiserId = $oScope->getAdvertiserId(); $publisherId = $oScope->getPublisherId(); $agencyId = $oScope->getAgencyId(); $query = "\n SELECT\n c.campaignid AS campaign_id,\n c.campaignname AS campaign_name,\n c.priority AS campaign_priority,\n c.status AS campaign_is_active,\n c.activate_time AS campaign_start,\n c.expire_time AS campaign_end,\n c.views AS campaign_booked_impressions,\n SUM(stats.impressions) AS campaign_impressions,\n MAX(stats.date_time) AS stats_most_recent_date_time\n FROM\n {$aConf['table']['prefix']}{$aConf['table']['campaigns']} AS c,\n {$aConf['table']['prefix']}{$aConf['table']['banners']} AS b,\n " . $statsTable . " AS stats"; if ($publisherId) { $query .= ",\n {$aConf['table']['prefix']}{$aConf['table']['zones']} AS z"; } if ($agencyId) { $query .= ",\n {$aConf['table']['prefix']}{$aConf['table']['clients']} AS a"; } $query .= "\n WHERE\n c.campaignid = b.campaignid\n\t\t\t\t" . $appendSqlWhere . "\n\t\t\t\tAND\n b.bannerid = stats.ad_id"; if ($spanIsForPlacementDates) { $query .= "\n AND\n (\n c.activate_time <= " . DBC::makeLiteral($oDaySpan->getEndDateStringUTC(), 'string') . "\n OR\n c.activate_time IS NULL\n )\n AND\n (\n c.expire_time >= " . DBC::makeLiteral($oDaySpan->getStartDateStringUTC(), 'string') . "\n OR\n c.expire_time IS NULL\n )"; } else { $query .= "\n AND\n stats.date_time >= " . DBC::makeLiteral($oDaySpan->getStartDateStringUTC(), 'string') . "\n AND\n stats.date_time <= " . DBC::makeLiteral($oDaySpan->getEndDateStringUTC(), 'string') . "\n "; } if ($advertiserId) { $query .= "\n AND\n c.clientid = " . DBC::makeLiteral($advertiserId, 'integer'); } if ($publisherId) { $query .= "\n AND\n stats.zone_id = z.zoneid\n AND\n z.affiliateid = " . DBC::makeLiteral($publisherId, 'integer'); } if ($agencyId) { $query .= "\n AND\n c.clientid = a.clientid\n AND\n a.agencyid = " . DBC::makeLiteral($agencyId, 'integer'); } $query .= "\n GROUP BY\n campaign_id,\n campaign_name,\n campaign_priority,\n campaign_is_active,\n campaign_start,\n campaign_end,\n campaign_booked_impressions\n ORDER BY\n campaign_impressions"; $rsDeliveryPerformanceData = DBC::NewRecordSet($query); $rsDeliveryPerformanceData->find(); $aDeliveryPerformanceData = $rsDeliveryPerformanceData->getAll(); return $aDeliveryPerformanceData; }