function test_getColumns()
{
// load hash representing data structure
require MAX_PATH . '/tests/data/data.entities.php';
foreach ($entities as $entity => $hash) {
$ret = SqlBuilder::_getColumns($entity, array(), true);
// Sort! Database column order is not relevant
$hash = sort($hash);
$ret = sort($ret);
$this->assertEqual($hash, $ret);
}
}
/**
* Returns an array of the entity's children.
*
* @param string $entity The name of the entity, for which the children are desired (e.g. "agency").
* @param array $aParams
* @param boolean $allFields Return *all* fields the entity has, or just the default fields?
* @param string $key The primary key field name.
* @return array An array of entity records.
*/
function _getEntitiesChildren($entity, $aParams, $allFields = false, $key = null)
{
$conf = $GLOBALS['_MAX']['CONF'];
if (empty($key)) {
$key = "{$entity}_id";
}
$aColumns = SqlBuilder::_getColumns($entity, $aParams, $allFields);
$aTables = SqlBuilder::_getTables($entity, $aParams);
$aLeftJoinedTables = array();
switch ($entity) {
case 'agency':
$aLeftJoinedTables[$conf['table']['prefix'] . $conf['table']['clients']] = 'a';
$aGroupBy = $aColumns;
$aColumns['COUNT(a.clientid)'] = 'num_children';
break;
case 'advertiser':
$aLeftJoinedTables[$conf['table']['prefix'] . $conf['table']['campaigns']] = 'm';
$aGroupBy = $aColumns;
$aColumns['COUNT(m.campaignid)'] = 'num_children';
$aGroupBy['a.type'] = 'a.type';
// Hack to allow this to work with Postgres
break;
case 'placement':
$aLeftJoinedTables[$conf['table']['prefix'] . $conf['table']['banners']] = 'd';
$aGroupBy = $aColumns;
$aGroupBy['m.status'] = 'm.status';
// Hack to allow this to work with Postgres
$aColumns['COUNT(d.bannerid)'] = 'num_children';
break;
case 'publisher':
$aLeftJoinedTables[$conf['table']['prefix'] . $conf['table']['zones']] = 'z';
$aGroupBy = $aColumns;
$aColumns['COUNT(z.zoneid)'] = 'num_children';
break;
default:
$aGroupBy = null;
}
$aLimitations = array_merge(SqlBuilder::_getLimitations($entity, $aParams), SqlBuilder::_getTableLimitations($aTables + $aLeftJoinedTables, $aParams));
return SqlBuilder::_select($aColumns, $aTables, $aLimitations, $aGroupBy, $key, $aLeftJoinedTables);
}
/**
* 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.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';
} 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.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 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' => 'status', 'd.bannertext' => 'bannertext', 'd.adserver' => 'adserver', 'd.block' => 'block', 'd.capping' => 'capping', 'd.session_capping' => 'session_capping', 'd.compiledlimitation' => 'compiledlimitation', 'd.append' => 'append', 'd.appendtype' => 'appendtype', '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');
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');
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.expire' => 'expire', 'cam.activate' => 'activate', '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');
}
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');
if ($allFields) {
$aColumns += array('m.views' => 'views', 'm.clicks' => 'clicks', 'm.conversions' => 'conversions', 'm.expire' => 'expire', 'm.activate' => 'activate', '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['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) {
$aColumns += array('z.affiliateid' => 'publisher_id');
}
}
$aColumns += array("CONCAT(s.ad_id, '_', s.zone_id)" => 'pkey', 's.ad_id' => 'ad_id', 's.zone_id' => 'zone_id') + SqlBuilder::_getColumns('stats_common', $aParams, $allFields);
if (isset($aParams['add_columns']) && is_array($aParams['add_columns'])) {
$aColumns += $aParams['add_columns'];
}
// Remove unused columns to avoid implicit group by
if (isset($aParams['exclude']) && is_array($aParams['exclude'])) {
if (array_search('ad_id', $aParams['exclude']) !== false) {
unset($aColumns["CONCAT(s.ad_id, '_', s.zone_id)"]);
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 {
$aColumns["(s.zone_id)"] = 'pkey';
}
} elseif (array_search('zone_id', $aParams['exclude']) !== false) {
unset($aColumns["CONCAT(s.ad_id, '_', s.zone_id)"]);
unset($aColumns['s.zone_id']);
$aColumns["(s.ad_id)"] = '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');
// variabletype field was removed in 0.2.0-alpha
# if ($allFields) $aColumns += array('v.description' => 'description', 'v.variabletype' => 'variable_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');
}
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;
}
return $aColumns;
}
