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);
 }
Example #3
0
 /**
  * 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);
 }