<?php

/*
+---------------------------------------------------------------------------+
| OpenX v2.8                                                                |
| ==========                                                                |
|                                                                           |
| Copyright (c) 2003-2009 OpenX Limited                                     |
| For contact details, see: http://www.openx.org/                           |
|                                                                           |
| This program is free software; you can redistribute it and/or modify      |
| it under the terms of the GNU General Public License as published by      |
| the Free Software Foundation; either version 2 of the License, or         |
| (at your option) any later version.                                       |
|                                                                           |
| This program is distributed in the hope that it will be useful,           |
| but WITHOUT ANY WARRANTY; without even the implied warranty of            |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the             |
| GNU General Public License for more details.                              |
|                                                                           |
| You should have received a copy of the GNU General Public License         |
| along with this program; if not, write to the Free Software               |
| Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA |
+---------------------------------------------------------------------------+
$Id: SqlBuilder.php 62345 2010-09-14 21:16:38Z chris.nutting $
*/

if(!isset($GLOBALS['_MAX']['FILES']['/lib/max/Delivery/common.php'])) {
    // Required by PHP5.1.2
    require_once MAX_PATH . '/lib/max/Delivery/common.php';
}

/**
 * @package    MaxDal
 * @author     Scott Switzer <scott@m3.net>
 */
class SqlBuilder
{
    // +---------------------------------------+
    // | Relational/constraint methods         |
    // |                                       |
    // | for entity builders                   |
    // +---------------------------------------+

    /**
     * Returns the fields given the particular entity.
     *
     * @param string $entity
     * @param array $aParams
     * @param boolean $allFields
     * @return array
     */
    function _getColumns($entity, $aParams, $allFields)
    {
        $aColumns = array();
        switch ($entity) {

        case 'ad' :
            $aColumns += array('d.bannerid' => 'ad_id', 'd.campaignid' => 'placement_id', 'd.status' => 'status', 'd.description' => 'name', 'd.storagetype' => 'type', 'd.ext_bannertype' => 'ext_bannertype');
            if ($allFields) $aColumns += array('d.contenttype' => 'contenttype', 'd.pluginversion' => 'pluginversion', 'd.filename' => 'filename', 'd.imageurl' => 'imageurl', 'd.htmltemplate' => 'htmltemplate', 'd.htmlcache' => 'htmlcache', 'd.width' => 'width', 'd.height' => 'height', 'd.weight' => 'weight', 'd.seq' => 'seq', 'd.target' => 'target', 'd.url' => 'url', 'd.alt' => 'alt', 'd.statustext' => 'statustext', 'd.bannertext' => 'bannertext', 'd.adserver' => 'adserver', 'd.block' => 'block', 'd.capping' => 'capping', 'd.session_capping' => 'session_capping', 'd.compiledlimitation' => 'compiledlimitation', 'd.prepend' => 'prepend', 'd.append' => 'append', 'd.bannertype' => 'bannertype', 'd.alt_filename' => 'alt_filename', 'd.alt_imageurl' => 'alt_imageurl', 'd.alt_contenttype' => 'alt_contenttype', 'd.comments' => 'comments', 'd.parameters' => 'parameters', 'd.transparent' => 'transparent');
            break;

        case 'advertiser' :
            $aColumns += array('a.clientid' => 'advertiser_id', 'a.agencyid' => 'agency_id', 'a.clientname' => 'name', 'a.type' => 'type');
            if ($allFields) $aColumns += array('a.contact' => 'contact', 'a.email' => 'email', 'a.report' => 'report', 'a.reportinterval' => 'report_interval', 'a.reportlastdate' => 'report_last_date', 'a.reportdeactivate' => 'report_deactivate');
            break;

        case 'ad_category_assoc' :
            $aColumns += array('ac.ad_category_assoc_id' => 'ad_category_assoc_id', 'ac.ad_id' => 'ad_id', 'ac.category_id' => 'category_id');
            break;

        case 'ad_zone_assoc' :
            $aColumns += array('az.ad_zone_assoc_id' => 'ad_zone_assoc_id', 'az.ad_id' => 'ad_id', 'az.zone_id' => 'zone_id', 'az.priority' => 'priority');
            break;

        case 'agency' :
            $aColumns += array('g.agencyid' => 'agency_id', 'g.name' => 'name', 'g.active' => 'active');
            if ($allFields) $aColumns += array(
                'g.contact' => 'contact',
                'g.email' => 'email',
                'g.logout_url' => 'logout_url'
                );
            break;

        case 'campaign' :
            $aColumns += array('cam.campaignid' => 'campaign_id', 'cam.campaignname' => 'campaignname', 'cam.clientid' => 'client_id', 'cam.anonymous' => 'anonymous', 'cam.type' => 'type');
            if ($allFields) $aColumns += array('cam.campaignid' => 'campaign_id', 'cam.campaignname' => 'campaignname', 'cam.clientid' => 'client_id', 'cam.views' => 'views', 'cam.clicks' => 'clicks', 'cam.conversions' => 'conversions', 'cam.priority' => 'priority', 'cam.weight' => 'weight', 'cam.target_impression' => 'target_impression', 'cam.target_click' => 'target_click', 'cam.target_conversion' => 'target_conversion', 'cam.anonymous' => 'anonymous', 'cam.companion' => 'companion', 'cam.comments' => 'comments', 'cam.revenue' => 'revenue', 'cam.revenue_type' => 'revenue_type', 'cam.updated' => 'updated', 'cam.block' => 'block', 'cam.capping' => 'capping', 'cam.session_capping' => 'session_capping', 'cam.an_campaign_id' => 'an_campaign_id', 'cam.as_campaign_id' => 'as_campaign_id', 'cam.an_status' => 'an_status', 'cam.activate_time' => 'activate_time', 'cam.expire_time' => 'expire_time', 'cam.show_capped_no_cookie' => 'show_capped_no_cookie');
            break;
        case 'category' :
            $aColumns += array('cat.category_id' => 'category_id', 'cat.name' => 'name');
            break;

        case 'channel' :
            $aColumns += array('ch.channelid' => 'channel_id', 'ch.agencyid' => 'agency_id', 'ch.affiliateid' => 'publisher_id', 'ch.name' => 'name');
            if ($allFields) $aColumns += array('ch.description' => 'description', 'ch.compiledlimitation' => 'compiledlimitation', 'ch.active' => 'active', 'ch.comments' => 'comments');
            break;

        case 'channel_limitation' :
            $aColumns += array('chl.logical' => 'logical', 'chl.type' => 'type', 'chl.comparison' => 'comparison', 'chl.data' => 'data', 'chl.executionorder' => 'executionorder');
            break;
        case 'image' :
            $aColumns += array('i.filename' => 'file_name');
            if ($allFields) $aColumns += array('i.t_stamp' => 't_stamp', 'i.contents' => 'contents');
            break;

        case 'limitation' :
            $aColumns += array('l.bannerid' => 'ad_id', 'l.logical' => 'logical', 'l.type' => 'type', 'l.comparison' => 'comparison', 'l.data' => 'data', 'l.executionorder' => 'executionorder');
            break;

        case 'placement' :
            $aColumns += array('m.clientid' => 'advertiser_id', 'm.campaignid' => 'placement_id', 'm.campaignname' => 'name', 'm.status' => 'status', 'm.anonymous' => 'anonymous', 'm.priority' => 'priority',  'm.type' => 'mtype');
            if ($allFields) $aColumns += array('m.views' => 'views', 'm.clicks' => 'clicks', 'm.conversions' => 'conversions', 'm.activate_time' => 'activate_time', 'm.expire_time' => 'expire_time', 'm.weight' => 'weight', 'm.target_impression' => 'target_impression', 'm.target_click' => 'target_click', 'm.target_conversion' => 'target_conversion', 'm.anonymous' => 'anonymous');
            break;

        case 'placement_tracker' :
            $aColumns += array('mt.campaign_trackerid' => 'placement_tracker_id', 'mt.campaignid' => 'placement_id', 'mt.trackerid' => 'tracker_id');
            if ($allFields) $aColumns += array('mt.status' => 'status', 'mt.viewwindow' => 'view_window', 'mt.clickwindow' => 'click_window');
            break;

        case 'publisher' :
            $aColumns += array('p.affiliateid' => 'publisher_id', 'p.agencyid' => 'agency_id', 'p.name' => 'name');
            if ($allFields) $aColumns += array('p.mnemonic' => 'mnemonic', 'p.contact' => 'contact', 'p.email' => 'email', 'p.website' => 'website');
            break;

        case 'stats' :
            $aColumns += array("DATE_FORMAT(date_time, '%Y-%m-%d')" => 'day', 'HOUR(date_time)' => 'hour', 'SUM(s.requests)' => 'sum_requests', 'SUM(s.impressions)' => 'sum_views', 'SUM(s.clicks)' => 'sum_clicks', 'SUM(s.conversions)' => 'sum_conversions');
            break;
            
        case 'stats_by_entity' :
            if (isset($aParams['add_columns']) && is_array($aParams['add_columns'])) {
                $aColumns += $aParams['add_columns'];
            }
            
            if(empty($aParams['market_stats_get_start_date'])) {
                if (isset($aParams['include']) && is_array($aParams['include'])) {
                    if (array_search('advertiser_id', $aParams['include']) !== false) $aColumns += array('m.clientid' => 'advertiser_id');
                    if (array_search('placement_id', $aParams['include']) !== false)  $aColumns += array('d.campaignid' => 'placement_id');
                    if (array_search('publisher_id', $aParams['include']) !== false) {
                        if(!empty($aParams['market_stats'])) {
                            $aColumns += array( 's.website_id' => 'publisher_id');
                        } else {
                            $aColumns += array('z.affiliateid' => 'publisher_id');
                        }
                    }
                }
                $db = OA_DB::singleton();
                $concat_pkey = "CONCAT(s.ad_id, '_', s.zone_id)";
                if($db->dbsyntax == 'pgsql') {
                    $concat_pkey = " s.ad_id || '_' || s.zone_id ";
                }
                $aColumns += array(
                	$concat_pkey => 'pkey', 
                	's.ad_id' => 'ad_id', 
                	's.zone_id' => 'zone_id'
                    ) 
                    + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
    
    
                // Remove unused columns to avoid implicit group by (otherwise postgresql errors out)
                if (isset($aParams['exclude']) && is_array($aParams['exclude'])) {
                    if (array_search('ad_id', $aParams['exclude']) !== false) {
                        unset($aColumns[$concat_pkey]);
                        unset($aColumns['s.ad_id']);
                        if (array_search('zone_id', $aParams['exclude']) !== false) {
                            unset($aColumns['s.zone_id']);
                            if (count($aParams['include'])) {
                                $tr = array(
                                    'placement_id'  => 'd.campaignid',
                                    'advertiser_id' => 'm.clientid',
                                    'publisher_id' => 'z.affiliateid'
                                );
                                $aColumns["CONCAT(".strtr(join(", '_', ", $aParams['include']), $tr).")"] = 'pkey';
                            } else {
                                $aColumns["(0)"] = 'pkey';
                            }
                        } else {
                            if(false !== ($found = array_search('ad_id',$aColumns))) {  
                                unset($aColumns[$found]);
                            }
                            if(isset($aParams['market_stats_including_zone_zero'])) {
                                $pkey = "CONCAT(s.website_id,'-',s.zone_id)";
                                if($db->dbsyntax == 'pgsql') {
                                    $pkey = " s.website_id || '-' || s.zone_id ";
                                }
                                $aColumns[$pkey] = 'pkey';
                            } else {
                                $aColumns["(s.zone_id)"] = 'pkey';
                            }
                        }
                    } elseif (array_search('zone_id', $aParams['exclude']) !== false) {
                        unset($aColumns[$concat_pkey]);
                        unset($aColumns['s.zone_id']);
            
                        // core stats: the primary key is simply the ad_id (unique across all rows)
                        if(empty($aParams['market_stats']))
                        {
                            $aColumns["(s.ad_id)"] = 'pkey';
                        }
                        // for market stats, the primary key was especially built, we reuse it
                        else
                        {
                            $pkey = array_search('ad_id', $aParams['custom_columns']);
                            
                            if(empty($pkey)) {
                                $pkey = 's.ad_id';
                            } else {
                            // here is one more ugly hack because, well, there is no choice...
                            $aColumns["($pkey)"] = 'pkey';
                            }
                        }
                    }
                }
            }
            break;

        case 'history_span' :
            if (isset($aParams['custom_columns']) && is_array($aParams['custom_columns'])) {
                $aColumns += $aParams['custom_columns'];
            } else {
                $aColumns += array('MIN(s.date_time)' => 'start_date');
            }
            break;

        case 'targeting_span' :
            if (isset($aParams['custom_columns']) && is_array($aParams['custom_columns'])) {
                $aColumns += $aParams['custom_columns'];
            } else {
                $aColumns += array('MIN(s.interval_start)' => 'start_date');
            }
            break;

        case 'history_day_hour' :
            $aColumns += array('s.date_time' => 'date_time') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
            break;

        case 'history_day' :
            $aColumns += array("DATE_FORMAT(s.date_time, , '%Y-%m-%d')" => 'day', "DATE_FORMAT(s.date_time, '{$GLOBALS['date_format']}')" => 'date_f') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
            break;

        case 'history_month' :
            $aColumns += array("DATE_FORMAT(s.date_time, '%Y-%m')" => 'month', "DATE_FORMAT(s.date_time, '{$GLOBALS['month_format']}')" => 'date_f') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
            break;

        case 'history_dow' :
            $aColumns += array("(DAYOFWEEK(s.date_time) - 1)" => 'dow') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
            break;

        case 'history_hour' :
            $aColumns += array("HOUR(s.date_time)" => 'hour') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
            break;

        case 'stats_common' :
            if (isset($aParams['custom_columns']) && is_array($aParams['custom_columns'])) {
                $aColumns += $aParams['custom_columns'];
            } else {
                $aColumns += array('SUM(s.requests)' => 'sum_requests', 'SUM(s.impressions)' => 'sum_views', 'SUM(s.clicks)' => 'sum_clicks', 'SUM(s.conversions)' => 'sum_conversions');
            }

            if (isset($aParams['add_columns']) && is_array($aParams['add_columns'])) {
                $aColumns += $aParams['add_columns'];
            }
            break;

        case 'tracker' :
            $aColumns += array('t.clientid' => 'advertiser_id', 't.trackerid' => 'tracker_id', 't.trackername' => 'name');
            if ($allFields) $aColumns += array('t.description' => 'description', 't.viewwindow' => 'viewwindow', 't.clickwindow' => 'clickwindow', 't.blockwindow' => 'blockwindow', 't.variablemethod' => 'variablemethod', 't.appendcode' => 'appendcode');
            break;

        case 'variable' :
            $aColumns += array('v.variableid' => 'variable_id', 'v.trackerid' => 'tracker_id', 'v.name' => 'name', 'v.datatype' => 'type');
            if ($allFields) $aColumns += array('v.description' => 'description', 'v.variablecode' => 'variablecode');
            break;

        case 'zone' :
            $aColumns += array('z.zoneid' => 'zone_id', 'z.affiliateid' => 'publisher_id', 'z.zonename' => 'name', 'z.delivery' => 'type');
            if ($allFields) $aColumns += array('z.description' => 'description', 'z.width' => 'width', 'z.height' => 'height', 'z.chain' => 'chain', 'z.prepend' => 'prepend', 'z.append' => 'append', 'z.appendtype' => 'appendtype', 'z.forceappend' => 'forceappend', 'z.inventory_forecast_type' => 'inventory_forecast_type', 'z.comments' => 'comments', 'z.block' => 'block', 'z.capping' => 'capping', 'z.session_capping' => 'session_capping', 'z.category' => 'category', 'z.ad_selection' => 'ad_selection', 'z.is_in_ad_direct' => 'is_in_ad_direct', 'z.rate' => 'rate', 'z.pricing' => 'pricing', 'z.show_capped_no_cookie' => 'show_capped_no_cookie');
            break;

        case 'placement_zone_assoc' :
            $aColumns += array('pz.placement_zone_assoc_id' => 'placement_zone_assoc_id', 'pz.placement_id' => 'placement_id', 'pz.zone_id' => 'zone_id');
            break;
        }
        
        $matchingEntitiesToFix = 'history_';
        if(substr($entity, 0, strlen($matchingEntitiesToFix)) == $matchingEntitiesToFix) {
            // postgresql throws an error: column "m.campaignid" must appear in the GROUP BY clause or be used in an aggregate function
            // we therefore remove the column ad_id built on a concatenation of various other fields, as this particular field
            // is not in use in the Global History stats screen (when entity == history_*)
            if(false !== ($found = array_search('ad_id',$aColumns))) {  
                unset($aColumns[$found]);
            }

        }
        return $aColumns;
    }

    /**
     * Returns the fields required by a stats query for a given entity.
     *
     * @param string $entity
     * @return array
     */
    function _getStatsColumns($entity)
    {
        $aColumns = array('SUM(s.requests)' => 'sum_requests', 'SUM(s.impressions)' => 'sum_views', 'SUM(s.clicks)' => 'sum_clicks', 'SUM(s.conversions)' => 'sum_conversions');
        switch ($entity) {

        case 'ad' :         $aColumns += array('d.bannerid' => 'ad_id'); break;
        case 'advertiser' : $aColumns += array('a.clientid' => 'advertiser_id'); break;
        case 'agency' :     $aColumns += array('g.agencyid' => 'agency_id'); break;
        case 'placement' :  $aColumns += array('m.campaignid' => 'placement_id'); break;
        case 'publisher' :  $aColumns += array('p.affiliateid' => 'publisher_id'); break;
        case 'zone' :       $aColumns += array('z.zoneid' => 'zone_id'); break;
        }

        return $aColumns;
    }

    /**
     * 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;
    }

    /**
     * Returns an array of constraints given an entity with params.
     *
     * @param string $entity
     * @param array $aParams
     * @return array
     */
    function _getLimitations($entity, $aParams)
    {
        if (!empty($aParams) && is_array($aParams)) {
            $aParams = MAX_commonSlashArray($aParams);
        }

        $aLimitations = array();

        // size limitation e.g. ((width = -1 AND height = -1) OR (width = 468 AND height = 60));
        $aSizeLimitations = array();
        $aZoneSize = array();
        if (isset($aParams['ad_width'])) SqlBuilder::_addLimitation($aZoneSize, 'ad_width', 'd.width', $aParams['ad_width']);
        if (isset($aParams['ad_height'])) SqlBuilder::_addLimitation($aZoneSize, 'ad_height', 'd.height', $aParams['ad_height']);
        $zoneSize = implode(' AND ',$aZoneSize);
        if (!empty($zoneSize)) {
            if (!empty($aParams['ad_nosize'])) {
                $aNoSize = array();
                SqlBuilder::_addLimitation($aNoSize, 'ad_width', 'd.width', -1);
                SqlBuilder::_addLimitation($aNoSize, 'ad_height', 'd.height', -1);
                $aSizeLimitations[] = '('.implode(' AND ',$aNoSize).')';
            }
            $aSizeLimitations[] = '('.$zoneSize.')';
            $sizeLimitation = implode(' OR ',$aSizeLimitations);
            if (!empty($sizeLimitation)) {
                $aLimitations[] = '('.$sizeLimitation.')';
            }
        }
        
        if (!empty($aParams['ad_type'])) {
            if ($aParams['ad_type'] == "!txt") {
                SqlBuilder::_addLimitation($aLimitations, 'ad_type', 'd.storagetype', 'txt', MAX_LIMITATION_NOT_EQUAL);
            } else {
                SqlBuilder::_addLimitation($aLimitations, 'ad_type', 'd.storagetype', $aParams['ad_type']);
            }
        }
        if (!empty($aParams['ad_active'])) {
            SqlBuilder::_addLimitation($aLimitations, 'ad_active', 'd.status', OA_ENTITY_STATUS_RUNNING,
                $aParams['ad_active'] == 't' ? MAX_LIMITATION_EQUAL : MAX_LIMITATION_NOT_EQUAL);
        }
        if (!empty($aParams['placement_active'])) {
            SqlBuilder::_addLimitation($aLimitations, 'placement_active', 'm.status', OA_ENTITY_STATUS_RUNNING,
                $aParams['placement_active'] == 't' ? MAX_LIMITATION_EQUAL : MAX_LIMITATION_NOT_EQUAL);
        }
        if (!empty($aParams['placement_anonymous'])) SqlBuilder::_addLimitation($aLimitations, 'placement_anonymous', 'm.anonymous', $aParams['placement_anonymous']);
        if (!empty($aParams['zone_inventory_forecast_type'])) SqlBuilder::_addLimitation($aLimitations, 'zone_inventory_forecast_type', 'z.inventory_forecast_type', $aParams['zone_inventory_forecast_type'], MAX_LIMITATION_BITWISE);

        switch ($entity) {

        case 'ad' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            // never return the Market ad entity (in stats, UI)
            if(empty($aParams['market_ads_include'])) {
                $aLimitations[] = "(d.ext_bannertype <> 'market-optin-banner' OR d.ext_bannertype IS NULL)";
            }
            break;

        case 'advertiser' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'a.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'm.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            // always filter by type
            if (!class_exists(DataObjects_Clients)) {OA_Dal::factoryDO('Clients');}
            if (empty($aParams['advertiser_type'])) {
                $aParams['advertiser_type'] = DataObjects_Clients::ADVERTISER_TYPE_DEFAULT; //always add default type
            } else {
                $aParams['advertiser_type'] .= ",".DataObjects_Clients::ADVERTISER_TYPE_DEFAULT; //always add default type
            }
            SqlBuilder::_addLimitation($aLimitations, 'advertiser_type', 'a.type', $aParams['advertiser_type']);
            // filter by campaign types only if param given
            if (isset($aParams['campaign_type']))
            {
                if (!class_exists(DataObjects_Campaigns)) {OA_Dal::factoryDO('Campaigns');}
                if (empty($aParams['campaign_type'])) {
                    $aParams['campaign_type'] = DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT; //always add default type
                } else {
                    $aParams['campaign_type'] .= ",".DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT; //always add default type
                }
                SqlBuilder::_addLimitation($aLimitations, 'campaign_type', 'm.type', $aParams['campaign_type']);
            }
            break;

        case 'ad_category_assoc' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'c.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'ac.ad_id', $aParams['ad_id']);
            if (!empty($aParams['ad_category_assoc_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_category_assoc_id', 'ac.ad_category_assoc_id', $aParams['ad_category_assoc_id']);
            break;

        case 'ad_zone_assoc' :
            if (!empty($aParams['agency_id'])) {
               SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'p.agencyid', $aParams['agency_id']);
               SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            }
            if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'z.affiliateid', $aParams['publisher_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 'az.zone_id', $aParams['zone_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'az.ad_id', $aParams['ad_id']);
            if (!empty($aParams['ad_zone_assoc_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_zone_assoc_id', 'az.ad_zone_assoc_id', $aParams['ad_zone_assoc_id']);
            break;

        case 'agency' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'g.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'a.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'm.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'p.affiliateid', $aParams['publisher_id']);
            if (!empty($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 'z.zoneid', $aParams['zone_id']);
            break;

        case 'campaign' :
            if (!empty($aParams['client_id'])) SqlBuilder::_addLimitation($aLimitations, 'client_id', 'cam.clientid', $aParams['client_id']);
            break;

        case 'category' :
            if (!empty($aParams['name'])) SqlBuilder::_addLimitation($aLimitations, 'name', 'cat.name', $aParams['name']);
            break;

        case 'channel' :
            if (isset($aParams['publisher_id'])) {
                SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'ch.affiliateid', $aParams['publisher_id']);
            } elseif (isset($aParams['channel_type']) && $aParams['channel_type'] == 'publisher') {
                SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'ch.affiliateid', 0, MAX_LIMITATION_NOT_EQUAL);
            }
            if (!empty($aParams['channel_id'])) SqlBuilder::_addLimitation($aLimitations, 'channel_id', 'ch.channelid', $aParams['channel_id']);
            if (isset($aParams['agency_id'])) {
                SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'agencyid', $aParams['agency_id']);
                SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'ch.affiliateid', 0);
            }
            break;

        case 'channel_limitation' :
            if (!empty($aParams['channel_id'])) SqlBuilder::_addLimitation($aLimitations, 'channel_id', 'chl.channelid', $aParams['channel_id']);
            break;

        case 'image' :
            if (!empty($aParams['file_name'])) SqlBuilder::_addLimitation($aLimitations, 'file_name', 'i.filename', $aParams['file_name']);
            break;

        case 'limitation' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'c.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'l.bannerid', $aParams['ad_id']);
            break;

        case 'placement' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'm.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            // always filter by type
            if (!class_exists(DataObjects_Campaigns)) {OA_Dal::factoryDO('Campaigns');}
            if (empty($aParams['campaign_type'])) {
                $aParams['campaign_type'] = DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT; //always add default type
            } else {
                $aParams['campaign_type'] .= ",".DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT; //always add default type
            }
            SqlBuilder::_addLimitation($aLimitations, 'campaign_type', 'm.type', $aParams['campaign_type']);
            break;

        case 'placement_tracker' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'c.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) {
               SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
               SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 't.clientid', $aParams['advertiser_id']);
            }
            if (!empty($aParams['tracker_id'])) SqlBuilder::_addLimitation($aLimitations, 'tracker_id', 'mt.trackerid', $aParams['tracker_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'mt.campaignid', $aParams['placement_id']);
            break;

        case 'placement_zone_assoc' :
            if (!empty($aParams['agency_id'])) {
               SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'p.agencyid', $aParams['agency_id']);
               SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'c.agencyid', $aParams['agency_id']);
            }
            if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'z.affiliateid', $aParams['publisher_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 'pz.zone_id', $aParams['zone_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'pz.placement_id', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            if (!empty($aParams['placement_zone_assoc_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_zone_assoc_id', 'pz.placement_zone_assoc_id', $aParams['placement_zone_assoc_id']);
            break;

        case 'publisher' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'p.agencyid', $aParams['agency_id']);
            if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'p.affiliateid', $aParams['publisher_id']);
            if (!empty($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 'z.zoneid', $aParams['zone_id']);
            break;

        case 'history_span' :
        case 'history_day_hour' :
        case 'history_day' :
        case 'history_month' :
        case 'history_dow' :
        case 'history_hour' :
        case 'stats' :
            if (!empty($aParams['agency_id'])) {

                // join to affiliates if stats by zone
                if (!empty($aParams['publisher_id']) ||
                    !empty($aParams['zone_id']) ||
                    isset($aParams['zone_type']) ||
                    isset($aParams['zone_width']) ||
                    isset($aParams['zone_height'])) {
                        SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'p.agencyid', $aParams['agency_id']);
                }

                $adIds = SqlBuilder::_getBannerIdsForAgency($aParams['agency_id']);
                if (!empty($adIds)) {
                    SqlBuilder::_addLimitation($aLimitations, 'ad_id', 's.ad_id', $adIds);
                } else {
                    SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
                }
            }
            
            if (!empty($aParams['publisher_id'])) {
                if(!empty($aParams['market_stats'])) {
                    SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 's.website_id', $aParams['publisher_id']);
                } else {
                    SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'z.affiliateid', $aParams['publisher_id']);
                }
            } 
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
            if (isset($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 's.zone_id', $aParams['zone_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 's.ad_id', $aParams['ad_id']);
            
            if(empty($aParams['market_stats_get_start_date'])) {
    			
                $do = OA_Dal::factoryDO('Clients');
                // Core stats exclude all Market advertiser activity
    			// Market stats only include the Market advertiser activity 
    			if(!empty($aParams['market_stats'])) {
                    $aLimitations[] = 'a.type = ' . DataObjects_Clients::ADVERTISER_TYPE_MARKET;
                    // if we are not explicitely selecting zone_id = 0 stats, we exclude all zone_id stats
                    if(empty($aParams['market_stats_including_zone_zero'])) {
                        $aLimitations[] = 's.zone_id <> 0';
                    }
                    
                } else {
                    $aLimitations[] = 'a.type = ' . DataObjects_Clients::ADVERTISER_TYPE_DEFAULT;
                }
                if (!empty($aParams['custom_table']) && $aParams['custom_table'] == 'data_intermediate_ad_connection') {
                    $dateTimeCol = "s.tracker_date_time";
                } else {
                    $dateTimeCol = "s.date_time";
                }
                if (!empty($aParams['day_begin'])) $aLimitations[]="{$dateTimeCol}>='".SqlBuilder::_dayToDateTime($aParams['day_begin'], true)."'";
                if (!empty($aParams['day_end'])) $aLimitations[]="{$dateTimeCol}<='".SqlBuilder::_dayToDateTime($aParams['day_end'], false)."'";
            }
            break;

        case 'stats_by_entity' :
            $aLimitations += SqlBuilder::_getLimitations('stats', $aParams);
            if (isset($aParams['zone_id']) && $aParams['zone_id'] == 0) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 's.zone_id', 0);
            break;

        case 'tracker' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 't.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['tracker_id'])) SqlBuilder::_addLimitation($aLimitations, 'tracker_id', 't.trackerid', $aParams['tracker_id']);
            if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'mt.campaignid', $aParams['placement_id']);
            if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 'd.bannerid', $aParams['ad_id']);
            break;

        case 'variable' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'a.agencyid', $aParams['agency_id']);
            if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 't.clientid', $aParams['advertiser_id']);
            if (!empty($aParams['tracker_id'])) SqlBuilder::_addLimitation($aLimitations, 'tracker_id', 'v.trackerid', $aParams['tracker_id']);
            if (!empty($aParams['variable_id'])) SqlBuilder::_addLimitation($aLimitations, 'variable_id', 'v.variableid', $aParams['variable_id']);
            break;

        case 'zone' :
            if (!empty($aParams['agency_id'])) SqlBuilder::_addLimitation($aLimitations, 'agency_id', 'p.agencyid', $aParams['agency_id']);
            if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'z.affiliateid', $aParams['publisher_id']);
            if (isset($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 'z.zoneid', $aParams['zone_id']);
            if (isset($aParams['zone_type'])) SqlBuilder::_addLimitation($aLimitations, 'zone_type', 'z.delivery', $aParams['zone_type']);
            if (isset($aParams['zone_width'])) SqlBuilder::_addLimitation($aLimitations, 'zone_width', 'z.width', $aParams['zone_width']);
            if (isset($aParams['zone_height'])) SqlBuilder::_addLimitation($aLimitations, 'zone_height', 'z.height', $aParams['zone_height']);
            break;
        }
        return $aLimitations;
    }

    function _dayToDateTime($day, $begin = true)
    {
        $oDate = new Date($day);
        if (!$begin) {
            $oDate->setHour(23);
            $oDate->setMinute(59);
            $oDate->setSecond(59);
        }
        $oDate->toUTC();

        return $oDate->format('%Y-%m-%d %H:%M:%S');
    }

    /**
     * Builds the limitations array.
     *
     * @param array $aLimitations
     * @param string $entityIdName
     * @param string $columnName
     * @param string $value
     * @param string $comparison_type (optional)
     */
    function _addLimitation(&$aLimitations, $entityIdName, $columnName, $value, $comparison_type = MAX_LIMITATION_EQUAL)
    {
        // Add single quotes around non-integer columns
        if (($entityIdName == 'ad_type')
         || ($entityIdName == 'ad_active')
         || ($entityIdName == 'placement_active')
         || ($entityIdName == 'placement_anonymous')
        ) {
            $value = "'" . str_replace(',', "','", $value) . "'";
        }

        // If there are multiple values, use IN instead of =
        if (strpos($value, ',') !== false) {
            $aLimitations[] = "$columnName IN ($value)";
        } else {
            switch ($comparison_type) {
            case MAX_LIMITATION_NOT_EQUAL:
                $aLimitations[] = "$columnName != $value";
                break;
            case MAX_LIMITATION_BITWISE:
                $aLimitations[] = "($columnName & $value > 0)";
                break;
            default:
                $aLimitations[] = "$columnName = $value";
                break;
            }
        }
    }

    /**
     * Returns an array of constraints given an entity with params.
     *
     * @param string $entity
     * @param array $aParams
     * @return array
     */
    function _getStatsLimitations($entity, $aParams)
    {
        $aLimitations = array();

        if (!empty($aParams['agency_id'])) {
            switch ($entity) {

            case 'advertiser' :
            case 'placement' :
            case 'ad' :
                $aLimitations[] = "a.agencyid={$aParams['agency_id']}";
                break;
            case 'publisher' :
            case 'zone' :
                $aLimitations[] = "p.agencyid={$aParams['agency_id']}";
                break;
            default :
                $aLimitations[] = "p.agencyid={$aParams['agency_id']}";
                $aLimitations[] = "a.agencyid={$aParams['agency_id']}";
            }
        }

        if (!empty($aParams['publisher_id'])) SqlBuilder::_addLimitation($aLimitations, 'publisher_id', 'z.affiliateid', $aParams['publisher_id']);
        if (!empty($aParams['advertiser_id'])) SqlBuilder::_addLimitation($aLimitations, 'advertiser_id', 'm.clientid', $aParams['advertiser_id']);
        if (!empty($aParams['zone_id'])) SqlBuilder::_addLimitation($aLimitations, 'zone_id', 's.zone_id', $aParams['zone_id']);
        if (!empty($aParams['placement_id'])) SqlBuilder::_addLimitation($aLimitations, 'placement_id', 'd.campaignid', $aParams['placement_id']);
        if (!empty($aParams['ad_id'])) SqlBuilder::_addLimitation($aLimitations, 'ad_id', 's.ad_id', $aParams['ad_id']);
        if (!empty($aParams['day_begin'])) $aLimitations[]="s.date_time>='{$aParams['day_begin']}'";
        if (!empty($aParams['day_end'])) $aLimitations[]="s.date_time<='{$aParams['day_end']}'";

        return $aLimitations;
    }

    /**
     * Builds an array of contraints given a list of tables.
     *
     * @todo Added some code specific for data_summary_ad_arrival_hourly table
     *
     * @param array $aTables
     * @param array $aParams
     * @return array
     */
    function _getTableLimitations($aTables, $aParams = array())
    {
        $conf = $GLOBALS['_MAX']['CONF'];
        $aTableLimitations = array();
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['agency']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['clients']])))
            $aTableLimitations[]='g.agencyid=a.agencyid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['agency']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['affiliates']])))
            $aTableLimitations[]='g.agencyid=p.agencyid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['affiliates']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']])))
            $aTableLimitations[]='p.affiliateid=z.affiliateid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['clients']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns']])))
            $aTableLimitations[]='a.clientid=m.clientid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['clients']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['trackers']])))
            $aTableLimitations[]='a.clientid=t.clientid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']])))
            $aTableLimitations[]='m.campaignid=d.campaignid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['placement_zone_assoc']])))
            $aTableLimitations[]='m.campaignid=pz.placement_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns_trackers']])))
            $aTableLimitations[]='m.campaignid=mt.campaignid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['trackers']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['campaigns_trackers']])))
            $aTableLimitations[]='t.trackerid=mt.trackerid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['data_summary_ad_hourly']])))
            $aTableLimitations[]='d.bannerid=s.ad_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aParams['market_stats'])))
            $aTableLimitations[]='d.bannerid=s.ad_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']]) && (!empty($aParams['market_stats'])))
            $aTableLimitations[]='z.zoneid=s.zone_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['data_intermediate_ad_connection']])))
            $aTableLimitations[]='d.bannerid=s.ad_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['ad_category_assoc']])))
            $aTableLimitations[]='d.bannerid=ac.ad_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['ad_zone_assoc']])))
            $aTableLimitations[]='d.bannerid=az.ad_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['banners']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['acls']])))
            $aTableLimitations[]='d.bannerid=l.bannerid';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['data_summary_ad_hourly']])))
            $aTableLimitations[]='z.zoneid=s.zone_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['data_intermediate_ad_connection']])))
            $aTableLimitations[]='z.zoneid=s.zone_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['ad_zone_assoc']])))
            $aTableLimitations[]='z.zoneid=az.zone_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['zones']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['placement_zone_assoc']])))
            $aTableLimitations[]='z.zoneid=pz.zone_id';
        if (!empty($aTables[$conf['table']['prefix'].$conf['table']['channel']]) && (!empty($aTables[$conf['table']['prefix'].$conf['table']['affiliates']])))
            $aTableLimitations[]='p.affiliateid=ch.affiliateid';
        return $aTableLimitations;
    }

    /**
     * Returns the grouping given the particular entity.
     *
     * @param string $entity
     * @param array $aParams
     * @return array
     */
    function _getGroupColumns($entity, $aParams)
    {
        $aGroupColumns = array();
        switch ($entity) 
        {
            case 'history_day_hour' : $aGroupColumns[] = 'date_time'; break;
            case 'history_day' :      $aGroupColumns[] = 'day'; break;
            case 'history_month' :    $aGroupColumns[] = 'month'; break;
            case 'history_dow' :      $aGroupColumns[] = 'dow'; break;
            case 'history_hour' :     $aGroupColumns[] = 'hour'; break;
    
            case 'stats_by_entity' :
                if(empty($aParams['market_stats_get_start_date'])) {
                    
                    $aGroupColumns = array('ad_id', 'zone_id');
                    if (isset($aParams['include'])) $aGroupColumns = array_merge($aGroupColumns, $aParams['include']);
                    if (isset($aParams['exclude'])) $aGroupColumns = array_diff($aGroupColumns, $aParams['exclude']);
                    if ($aParams['market_stats']) {
                        $aGroupColumns = array_diff($aGroupColumns, array('ad_id'));
                        $aGroupColumns[] = 'pkey';
                    }
                }
                break;
        }
        return count($aGroupColumns) ? $aGroupColumns : null;
    }

    /**
     * Returns the tables which need a LEFT JOIN given the particular entity.
     *
     * @param string $entity
     * @param array $aParams
     * @return array
     */
    function _getLeftJoinedTables($entity, $aParams, $aTables = null)
    {
        $conf = $GLOBALS['_MAX']['CONF'];
        $aLeftJoinedTables = array();
        switch ($entity) {

        case 'stats_by_entity' :
            if (isset($aParams['exclude']) && !empty($aParams['agency_id'])) {
                if (array_search('zone_id', $aParams['exclude']) !== false) {
                    // include deleted zones and direct selection in advertiser stats
                    // except for market stats: it would otherwise include zoneid=0 which is the bucket for stats migrated from pre-2.8.4
                    // these zoneid=0 stats will be selected in a separate query and should not be selected in the standard stats query,
                    // or stats would be duplicated
                    if(empty($aParams['market_stats']))
                    {
                        $aLeftJoinedTables[$conf['table']['prefix'].$conf['table']['zones']] = 'z';
                        $aLeftJoinedTables[$conf['table']['prefix'].$conf['table']['affiliates']] = 'p';
                    }
                }
            }
            break;
        case 'history_span' :
        case 'history_day_hour' :
        case 'history_day' :
        case 'history_month' :
        case 'history_dow' :
        case 'history_hour' :
            // include deleted zones and direct selection in history stats
            if(empty($aParams['market_stats']))
            {
                $aLeftJoinedTables[$conf['table']['prefix'].$conf['table']['zones']] = 'z';
                $aLeftJoinedTables[$conf['table']['prefix'].$conf['table']['affiliates']] = 'p';
            }
            break;
        }

        return count($aLeftJoinedTables) ? $aLeftJoinedTables : null;
    }

    // +---------------------------------------+
    // | SQL operations methods                |
    // |                                       |
    // | for entity builders                   |
    // +---------------------------------------+

    function _doDelete($table, $aParams)
    {
        $do = OA_Dal::factoryDO($table);
        if ($do === false) {
            return false;
        }
        $success = $do->setFrom($aParams);
        if (!$success === true) {
            return false;
        }
        return $do->delete();
    }


    /**
     * Performs an SQL insert.
     *
     * @param array $table Table name.
     * @param array $aVariables Map of column to value for the created row.
     * @return integer The newly inserted row id if the table has any
     * autoincrement field defined. Otherwise, true on success and false
     * on failure.
     */
    function _insert($table, $aVariables)
    {
        $do = OA_Dal::factoryDO($table);
        if ($do === false) {
            return false;
        }
        $success = $do->setFrom($aVariables);
        if (!$success === true) {
            return false;
        }
        return $do->insert();
    }


    function _getFieldIdName($table)
    {
        $dbh =& OA_DB::singleton();
        $schema = MDB2_Schema::factory($dbh);
        $definition = $schema->getDefinitionFromDatabase(array($table));
        foreach ($definition['tables'][$table]['fields'] as $fieldname => $dataField) {
            if (isset($dataField['autoincrement']) && 1 == $dataField['autoincrement']) {
                return $fieldname;
            }
        }
        return null;
    }

    /**
     * Wraps an SQL query.
     *
     * @param array $aColumns
     * @param array $aTables
     * @param array $aLimitations
     * @param array $aGroupColumns
     * @param string $primaryKey
     * @param array $aLeftJoinedTables
     * @return integer  The number of rows affected by the query
     */
    function _select($aColumns, $aTables, $aLimitations, $aGroupColumns, $primaryKey, $aLeftJoinedTables = null)
    {
        $conf = $GLOBALS['_MAX']['CONF'];
        $oDbh = OA_DB::singleton();
        $columns = '';
        if (is_array($aColumns)) {
            foreach ($aColumns as $column => $alias) {
                $columns .= ($columns == '') ? "SELECT $column AS $alias" : ",$column AS $alias";
            }
        } else {
            $columns = "SELECT $aColumns";
        }

        $prev_aliases = array();
        $tables = '';
        if (is_array($aTables)) {
            $x = 0;
            if (!is_null($aLeftJoinedTables)) {
                $aTables = array_diff($aTables, $aLeftJoinedTables);
            }
            $joinType = 'INNER';
            while (count($aTables) && $x < 50) {
                $x++;
                foreach ($aTables as $tableKey => $alias) {
                    $table = $tableKey;

                    //  check for prefix
                    if (!empty($conf['table']['prefix']) && strpos($table, $conf['table']['prefix']) != 0) {
                        $table = $conf['table']['prefix'] . $table;
                    }

                    $qTable = $oDbh->quoteIdentifier($table, true);

                    $joinLimitation = '';

                    if (count($prev_aliases)) {
                        if (is_array($aLimitations)) {
                            foreach ($aLimitations as $limitationKey => $limitation) {
                                if (preg_match("/({$alias}\.[a-z0-9_]+ *= *(".join('|', $prev_aliases).")\..+|(".join('|', $prev_aliases).")\.[a-z0-9_]+ *= *{$alias}\..+)/", $limitation)) {
                                    $joinLimitation = $limitation;
                                    unset($aLimitations[$limitationKey]);
                                    break;
                                }
                            }
                        }
                    } else {
                        $tables .= " FROM $qTable AS $alias";
                    }

                    if ($joinLimitation) {
                        $tables .= " $joinType JOIN $qTable AS $alias ON ($joinLimitation)";
                    } elseif (count($prev_aliases)) {
                        continue;
                    }

                    $prev_aliases[] = $alias;
                    unset ($aTables[$tableKey]);
                }

                if (!is_null($aLeftJoinedTables) && !count($aTables)) {
                    $aTables = $aLeftJoinedTables;
                    $aLeftJoinedTables = null;
                    $joinType = 'LEFT';
                }
            }
        } else {
            $tables = "FROM ".$oDbh->quoteIdentifier($aTables, true);
        }

        $where = '';
        if (is_array($aLimitations)) {
            foreach ($aLimitations as $limitation) {
                $where .= ($where == '') ? " WHERE $limitation" : " AND $limitation";
            }
        } else {
            $where = " WHERE $aLimitations";
        }

        $group = '';
        if (is_array($aGroupColumns) && count($aGroupColumns) > 0) {
            $group = ' GROUP BY ' . implode(',', $aGroupColumns);
        }

        $query = $columns . $tables . $where . $group;
//var_dump($query);
        return  SqlBuilder::_query($query, $primaryKey);
    }

    /**
     * Performs an SQL query.
     *
     * @param string $query
     * @param string $primaryKey
     * @return array    An array of entity records
     */
    function _query($query, $primaryKey)
    {
//        var_dump($query);
        $oDbh = OA_DB::singleton();
        $aResult =  $oDbh->queryAll($query);
        $aDataEntities = array();
        if (PEAR::isError($aResult))
        {
            return false;
        }


        foreach ($aResult AS $k => $dataEntity)
        {
            $aDataEntities[$dataEntity[$primaryKey]] = $dataEntity;
        }
        return $aDataEntities;
    }

    /**
     * Retieves a list of banner ids linked to the given agency
     *
     * @param string $agencyId
     * @return string A comma delimited list of banner ids
     */
    function _getBannerIdsForAgency($agencyId)
    {
        $conf = $GLOBALS['_MAX']['CONF'];
        $query = 'select d.bannerid as ad_id from '.
            $conf['table']['prefix'].$conf['table']['banners'] . ' AS d '.
            'INNER JOIN '.
            $conf['table']['prefix'].$conf['table']['campaigns'] . ' AS m '.
            'ON (m.campaignid=d.campaignid) '.
            'INNER JOIN '.
            $conf['table']['prefix'].$conf['table']['clients'] . ' AS a '.
            'ON (a.clientid=m.clientid) '.
            'WHERE a.agencyid = ' . $agencyId . ' order by bannerid;';

        $oDbh = OA_DB::singleton();
        $aResult =  $oDbh->queryAll($query);
        $aDataEntities = array();
        if (PEAR::isError($aResult))
        {
            return false;
        }

        foreach ($aResult AS $k => $dataEntity)
        {
            $aDataEntities[] = $dataEntity['ad_id'];
        }
        return implode (',', $aDataEntities);
    }
}

?>
