function test_getTables()
{
require MAX_PATH . '/tests/data/data.entities.php';
foreach ($entities as $entity => $hash) {
$ret = SqlBuilder::_getTables($entity, array());
$this->assertTrue(is_array($ret));
if ($entity == 'stats') {
$expected = 4;
} else {
$expected = 1;
}
$this->assertTrue(count($ret) == $expected);
$keys = array_keys($ret);
$vals = array_values($ret);
$this->assertTrue(is_string($keys[0]));
$this->assertTrue(!is_null($vals[0]));
}
}
/**
* Get stats by entity.
*
* @param string $entity
* @param array $aParams
* @param boolean $allFields
* @param string $key The primary key field name
* @return array An array of entity records
*/
function _getEntitiesStats($entity, $aParams, $allFields = false, $key = null)
{
if (empty($key)) {
$key = "{$entity}_id";
}
$aColumns = SqlBuilder::_getStatsColumns($entity);
$aTables = SqlBuilder::_getTables($entity, $aParams, true);
$aLimitations = array_merge(SqlBuilder::_getStatsLimitations($entity, $aParams), SqlBuilder::_getTableLimitations($aTables, $aParams));
// An ugly hack to get the alias used for the entity table so
// the query works with Postgres.
end($aColumns);
$groupByColumn = key($aColumns);
reset($aColumns);
$aGroups = array($groupByColumn);
return SqlBuilder::_select($aColumns, $aTables, $aLimitations, $aGroups, $key);
}
/**
* Returns as array of table names and their aliases.
*
* @param string $entity
* @param array $aParams
* @param boolean $includeStats
* @return array
*/
function _getTables($entity, $aParams, $includeStats = false)
{
$conf = $GLOBALS['_MAX']['CONF'];
$aTables = array();
switch ($entity) {
case 'ad':
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if ($includeStats) {
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's');
}
break;
case 'advertiser':
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a');
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['ad_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_width'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_height'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['campaign_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if ($includeStats) {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's', $conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
break;
case 'ad_category_assoc':
$aTables += array($conf['table']['prefix'] . $conf['table']['ad_category_assoc'] => 'ac');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . ($conf['table']['clients'] = 'a'));
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (isset($aParams['ad_width'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_height'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
break;
case 'ad_zone_assoc':
$aTables += array($conf['table']['prefix'] . $conf['table']['ad_zone_assoc'] => 'az');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z', $conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['affiliates'] => 'p');
}
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['zone_inventory_forecast_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (isset($aParams['ad_width'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_height'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
break;
case 'agency':
$aTables += array($conf['table']['prefix'] . $conf['table']['agency'] => 'g');
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['ad_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_width'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_height'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['affiliates'] => 'p');
}
if (!empty($aParams['zone_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['affiliates'] => 'p', $conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (!empty($aParams['zone_inventory_forecast_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['affiliates'] => 'p', $conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if ($includeStats) {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's', $conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['affiliates'] => 'p', $conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
break;
case 'campaign':
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'cam');
case 'category':
$aTables += array($conf['table']['prefix'] . $conf['table']['category'] => 'cat');
break;
case 'channel':
$aTables += array($conf['table']['prefix'] . $conf['table']['channel'] => 'ch');
break;
case 'channel_limitation':
$aTables += array($conf['table']['prefix'] . $conf['table']['acls_channel'] => 'chl');
break;
case 'image':
$aTables += array($conf['table']['prefix'] . $conf['table']['images'] => 'i');
break;
case 'limitation':
$aTables += array($conf['table']['prefix'] . $conf['table']['acls'] => 'l');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . ($conf['table']['clients'] = 'a'));
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
break;
case 'placement':
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a');
}
if (!empty($aParams['ad_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_width'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (isset($aParams['ad_height'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['ad_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if ($includeStats) {
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
break;
case 'placement_tracker':
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns_trackers'] => 'mt');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['trackers'] => 't');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['trackers'] => 't');
}
break;
case 'placement_zone_assoc':
$aTables += array($conf['table']['prefix'] . $conf['table']['placement_zone_assoc'] => 'pz');
if (!empty($aParams['ad_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['affiliates'] => 'p');
}
if (!empty($aParams['placement_active'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['placement_anonymous'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm');
}
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (!empty($aParams['zone_inventory_forecast_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
break;
case 'publisher':
$aTables += array($conf['table']['prefix'] . $conf['table']['affiliates'] => 'p');
if (!empty($aParams['zone_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (!empty($aParams['zone_inventory_forecast_type'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if ($includeStats) {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's', $conf['table']['prefix'] . $conf['table']['zones'] => 'z');
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
}
break;
case 'history_span':
case 'history_day_hour':
case 'history_day':
case 'history_month':
case 'history_dow':
case 'history_hour':
case 'stats':
if (isset($aParams['custom_table'])) {
$aTables += array($conf['table']['prefix'] . $aParams['custom_table'] => 's');
} else {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's');
}
// stats always require the join to advertiser table to ensure only core stats are displayed (excluding market advertiser)
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
if (empty($aParams['market_stats_get_start_date'])) {
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['affiliates'] => 'p', $conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
if (!empty($aParams['publisher_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
}
if (isset($aParams['market_stats_including_zone_zero'])) {
unset($aTables[$conf['table']['prefix'] . $conf['table']['zones']]);
}
}
break;
case 'stats_by_entity':
if (isset($aParams['include']) && is_array($aParams['include'])) {
// Fake needed parameters
if (array_search('advertiser_id', $aParams['include']) !== false) {
$aParams['advertiser_id'] = 1;
}
if (array_search('placement_id', $aParams['include']) !== false) {
$aParams['placement_id'] = 1;
}
if (array_search('publisher_id', $aParams['include']) !== false) {
$aParams['publisher_id'] = 1;
}
}
$aTables += SqlBuilder::_getTables('stats', $aParams);
break;
case 'tracker':
$aTables += array($conf['table']['prefix'] . $conf['table']['trackers'] => 't');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a');
}
if (!empty($aParams['ad_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd', $conf['table']['prefix'] . $conf['table']['campaigns'] => 'm', $conf['table']['prefix'] . $conf['table']['campaigns_trackers'] => 'mt');
}
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['campaigns_trackers'] => 'mt');
}
break;
case 'variable':
$aTables += array($conf['table']['prefix'] . $conf['table']['variables'] => 'v');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['clients'] => 'a', $conf['table']['prefix'] . $conf['table']['trackers'] => 't');
}
if (!empty($aParams['advertiser_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['trackers'] => 't');
}
break;
case 'zone':
$aTables += array($conf['table']['prefix'] . $conf['table']['zones'] => 'z');
if (!empty($aParams['agency_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['affiliates'] => 'p');
}
if ($includeStats) {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's');
}
if ($includeStats) {
$aTables += array($conf['table']['prefix'] . $conf['table']['data_summary_ad_hourly'] => 's');
if (!empty($aParams['placement_id'])) {
$aTables += array($conf['table']['prefix'] . $conf['table']['banners'] => 'd');
}
}
break;
}
return $aTables;
}
/**
* Get stats by entity.
*
* @param string $entity
* @param array $aParams
* @param boolean $allFields
* @param string $key The primary key field name
* @return array An array of entity records
*/
function _getEntitiesStats($entity, $aParams, $allFields = false, $key = null)
{
if (empty($key)) {
$key = "{$entity}_id";
}
$aColumns = SqlBuilder::_getStatsColumns($entity);
$aTables = SqlBuilder::_getTables($entity, $aParams, true);
$aLimitations = array_merge(SqlBuilder::_getStatsLimitations($entity, $aParams), SqlBuilder::_getTableLimitations($aTables, $aParams));
$aGroupBy = array($key);
return SqlBuilder::_select($aColumns, $aTables, $aLimitations, $aGroupBy, $key);
}