/**
* A function to generate a direct selection query preserving 2.0 backwards compatibility
*
* @param string $part The what parameter part to build the query
* @param boolean $lastpart True if there are no other parts to work on
* @param string $precondition Any SQL preconditions to apply
* @return string The generated query
*/
function OA_Dal_Delivery_buildAdInfoQuery($part, $lastpart, $precondition)
{
$conf = $GLOBALS['_MAX']['CONF'];
$aColumns = array('d.bannerid AS ad_id', 'd.campaignid AS placement_id', 'd.status AS status', 'd.storagetype AS type', 'd.contenttype AS contenttype', 'd.weight AS weight', 'd.width AS width', 'd.ext_bannertype AS ext_bannertype', 'd.height AS height', 'd.adserver AS adserver', 'd.block AS block_ad', 'd.capping AS cap_ad', 'd.session_capping AS session_cap_ad', 'd.compiledlimitation AS compiledlimitation', 'd.acl_plugins AS acl_plugins', 'd.alt_filename AS alt_filename', 'az.priority AS priority', 'az.priority_factor AS priority_factor', 'az.to_be_delivered AS to_be_delivered', 'm.campaignid AS campaign_id', 'm.priority AS campaign_priority', 'm.weight AS campaign_weight', 'm.companion AS campaign_companion', 'm.block AS block_campaign', 'm.capping AS cap_campaign', 'm.session_capping AS session_cap_campaign', 'm.show_capped_no_cookie AS show_capped_no_cookie', 'cl.clientid AS client_id', 'm.expire_time AS expire_time', 'm.revenue_type AS revenue_type', 'm.ecpm_enabled AS ecpm_enabled', 'm.ecpm AS ecpm', 'ct.status AS tracker_status', OX_Dal_Delivery_regex("d.htmlcache", "src\\s?=\\s?[\\'\"]http:") . " AS html_ssl_unsafe", OX_Dal_Delivery_regex("d.imageurl", "^http:") . " AS url_ssl_unsafe");
$aTables = array("" . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['banners']) . " AS d", "JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['ad_zone_assoc']) . " AS az ON (d.bannerid = az.ad_id)", "JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['campaigns']) . " AS m ON (m.campaignid = d.campaignid) ");
$select = "\n az.zone_id = 0\n AND m.status <= 0\n AND d.status <= 0";
// Add preconditions to query
if ($precondition != '') {
$select .= " {$precondition} ";
}
// Other
if ($part != '') {
$conditions = '';
$onlykeywords = true;
$part_array = explode(',', $part);
for ($k = 0; $k < count($part_array); $k++) {
// Process switches
if (substr($part_array[$k], 0, 1) == '+' || substr($part_array[$k], 0, 1) == '_') {
$operator = 'AND';
$part_array[$k] = substr($part_array[$k], 1);
} elseif (substr($part_array[$k], 0, 1) == '-') {
$operator = 'NOT';
$part_array[$k] = substr($part_array[$k], 1);
} else {
$operator = 'OR';
}
// Test statements
if ($part_array[$k] != '' && $part_array[$k] != ' ') {
// Banner dimensions, updated to support 2.3-only size keyword
if (preg_match('#^(?:size:)?(\\d+)x(\\d+)$#', $part_array[$k], $m)) {
$width = (int) $m[1];
$height = (int) $m[2];
if ($operator == 'OR') {
$conditions .= "OR (d.width = {$width} AND d.height = {$height}) ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.width = {$width} AND d.height = {$height}) ";
} else {
$conditions .= "AND (d.width <> {$width} OR d.height <> {$height}) ";
}
$onlykeywords = false;
} elseif (preg_match('#^width:(\\d*)(-?)(\\d*)$#', $part_array[$k], $m)) {
$min = (int) $m[1];
$range = !empty($m[2]);
$max = (int) $m[3];
if (!$range && $min) {
// Single value
if ($operator == 'OR') {
$conditions .= "OR d.width = {$min} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.width = {$min} ";
} else {
$conditions .= "AND d.width <> {$min} ";
}
} else {
if (!$min) {
// Only upper limit, set lower limit to make sure not text ads are delivered
$min = 1;
}
if (!$max) {
// Only lower limit
if ($operator == 'OR') {
$conditions .= "OR d.width >= {$min} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.width >= {$min} ";
} else {
$conditions .= "AND d.width < {$min} ";
}
} else {
// Both lower and upper limit
if ($operator == 'OR') {
$conditions .= "OR (d.width >= {$min} AND d.width <= {$max}) ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.width >= {$min} AND d.width <= {$max}) ";
} else {
$conditions .= "AND (d.width < {$min} OR d.width > {$max}) ";
}
}
}
$onlykeywords = false;
} elseif (preg_match('#^height:(\\d*)(-?)(\\d*)$#', $part_array[$k], $m)) {
$min = (int) $m[1];
$range = !empty($m[2]);
$max = (int) $m[3];
if (!$range && $min) {
// Single value
if ($operator == 'OR') {
$conditions .= "OR d.height = {$min} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.height = {$min} ";
} else {
$conditions .= "AND d.height <> {$min} ";
}
} else {
if (!$min) {
// Only upper limit, set lower limit to make sure not text ads are delivered
$min = 1;
}
if (!$max) {
// Only lower limit
if ($operator == 'OR') {
$conditions .= "OR d.height >= {$min} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.height >= {$min} ";
} else {
$conditions .= "AND d.height < {$min} ";
}
} else {
// Both lower and upper limit
if ($operator == 'OR') {
$conditions .= "OR (d.height >= {$min} AND d.height <= {$max}) ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.height >= {$min} AND d.height <= {$max}) ";
} else {
$conditions .= "AND (d.height < {$min} OR d.height > {$max}) ";
}
}
}
$onlykeywords = false;
} elseif (preg_match('#^(?:(?:bannerid|adid|ad_id):)?(\\d+)$#', $part_array[$k], $m)) {
$bannerid = (int) $m[1];
if ($bannerid) {
if ($operator == 'OR') {
$conditions .= "OR d.bannerid = {$bannerid} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.bannerid = {$bannerid} ";
} else {
$conditions .= "AND d.bannerid <> {$bannerid} ";
}
}
$onlykeywords = false;
} elseif (preg_match('#^(?:(?:clientid|campaignid|placementid|placement_id):)?(\\d+)$#', $part_array[$k], $m)) {
$campaignid = (int) $m[1];
if ($campaignid) {
if ($operator == 'OR') {
$conditions .= "OR d.campaignid = {$campaignid} ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.campaignid = {$campaignid} ";
} else {
$conditions .= "AND d.campaignid <> {$campaignid} ";
}
}
$onlykeywords = false;
} elseif (substr($part_array[$k], 0, 7) == 'format:') {
$format = OX_escapeString(trim(stripslashes(substr($part_array[$k], 7))));
if (!empty($format)) {
if ($operator == 'OR') {
$conditions .= "OR d.contenttype = '{$format}' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.contenttype = '{$format}' ";
} else {
$conditions .= "AND d.contenttype <> '{$format}' ";
}
}
$onlykeywords = false;
} elseif ($part_array[$k] == 'html') {
if ($operator == 'OR') {
$conditions .= "OR d.storagetype = 'html' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.storagetype = 'html' ";
} else {
$conditions .= "AND d.storagetype <> 'html' ";
}
$onlykeywords = false;
} elseif ($part_array[$k] == 'textad') {
if ($operator == 'OR') {
$conditions .= "OR d.storagetype = 'txt' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.storagetype = 'txt' ";
} else {
$conditions .= "AND d.storagetype <> 'txt' ";
}
$onlykeywords = false;
} else {
$conditions .= OA_Dal_Delivery_getKeywordCondition($operator, $part_array[$k]);
}
}
}
// Strip first AND or OR from $conditions
$conditions = strstr($conditions, ' ');
// Add global keyword
if ($lastpart == true && $onlykeywords == true) {
$conditions .= OA_Dal_Delivery_getKeywordCondition('OR', 'global');
}
// Add conditions to select
if ($conditions != '') {
$select .= ' AND (' . $conditions . ') ';
}
}
$columns = implode(",\n ", $aColumns);
$tables = implode("\n ", $aTables);
$leftJoin = "\n LEFT JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['campaigns_trackers']) . " AS ct ON (ct.campaignid = m.campaignid)\n LEFT JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['clients']) . " AS cl ON (cl.clientid = m.clientid)\n LEFT JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['agency']) . " AS a ON (a.agencyid = cl.agencyid)\n ";
$query = "SELECT\n " . $columns . "\nFROM\n " . $tables . $leftJoin . "\nWHERE " . $select;
return $query;
}
function OA_Dal_Delivery_buildQuery($part, $lastpart, $precondition)
{
$conf = $GLOBALS['_MAX']['CONF'];
$aColumns = array('d.bannerid AS ad_id', 'd.campaignid AS placement_id', 'd.status AS status', 'd.description AS name', 'd.storagetype AS type', 'd.contenttype AS contenttype', 'd.pluginversion AS pluginversion', 'd.filename AS filename', 'd.imageurl AS imageurl', 'd.htmltemplate AS htmltemplate', 'd.htmlcache AS htmlcache', 'd.width AS width', 'd.height AS height', 'd.weight AS weight', 'd.seq AS seq', 'd.target AS target', 'd.url AS url', 'd.alt AS alt', 'd.statustext AS statustext', 'd.bannertext AS bannertext', 'd.adserver AS adserver', 'd.block AS block_ad', 'd.capping AS cap_ad', 'd.session_capping AS session_cap_ad', 'd.compiledlimitation AS compiledlimitation', 'd.acl_plugins AS acl_plugins', 'd.append AS append', 'd.appendtype AS appendtype', 'd.bannertype AS bannertype', 'd.alt_filename AS alt_filename', 'd.alt_imageurl AS alt_imageurl', 'd.alt_contenttype AS alt_contenttype', 'd.parameters AS parameters', 'd.transparent AS transparent', 'd.ext_bannertype AS ext_bannertype', 'az.priority AS priority', 'az.priority_factor AS priority_factor', 'az.to_be_delivered AS to_be_delivered', 'm.campaignid AS campaign_id', 'm.priority AS campaign_priority', 'm.weight AS campaign_weight', 'm.companion AS campaign_companion', 'm.block AS block_campaign', 'm.capping AS cap_campaign', 'm.session_capping AS session_cap_campaign', 'm.clickwindow AS clickwindow', 'm.viewwindow AS viewwindow', 'cl.clientid AS client_id', 'cl.advertiser_limitation AS advertiser_limitation', 'a.account_id AS account_id', 'a.agencyid AS agency_id');
$aTables = array("" . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['banners']) . " AS d", "JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['campaigns']) . " AS m ON (d.campaignid = m.campaignid) ", "JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['clients']) . " AS cl ON (m.clientid = cl.clientid) ", "JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['ad_zone_assoc']) . " AS az ON (d.bannerid = az.ad_id)");
$select = "\naz.zone_id = 0\nAND m.status <= 0\nAND d.status <= 0";
// Add preconditions to query
if ($precondition != '') {
$select .= " {$precondition} ";
}
// Other
if ($part != '') {
$conditions = '';
$onlykeywords = true;
$part_array = explode(',', $part);
for ($k = 0; $k < count($part_array); $k++) {
// Process switches
if (substr($part_array[$k], 0, 1) == '+' || substr($part_array[$k], 0, 1) == '_') {
$operator = 'AND';
$part_array[$k] = substr($part_array[$k], 1);
} elseif (substr($part_array[$k], 0, 1) == '-') {
$operator = 'NOT';
$part_array[$k] = substr($part_array[$k], 1);
} else {
$operator = 'OR';
}
// Test statements
if ($part_array[$k] != '' && $part_array[$k] != ' ') {
// Banner dimensions, updated to support 2.3-only size keyword
if (preg_match('#^(?:size:)?([0-9]+x[0-9]+)$#', $part_array[$k], $m)) {
list($width, $height) = explode('x', $m[1]);
if ($operator == 'OR') {
$conditions .= "OR (d.width = {$width} AND d.height = {$height}) ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.width = {$width} AND d.height = {$height}) ";
} else {
$conditions .= "AND (d.width != {$width} OR d.height != {$height}) ";
}
$onlykeywords = false;
} elseif (substr($part_array[$k], 0, 6) == 'width:') {
$part_array[$k] = substr($part_array[$k], 6);
if ($part_array[$k] != '' && $part_array[$k] != ' ') {
if (is_int(strpos($part_array[$k], '-'))) {
// Width range
list($min, $max) = explode('-', $part_array[$k]);
// Only upper limit, set lower limit to make sure not text ads are delivered
if ($min == '') {
$min = 1;
}
// Only lower limit
if ($max == '') {
if ($operator == 'OR') {
$conditions .= "OR d.width >= '" . trim($min) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.width >= '" . trim($min) . "' ";
} else {
$conditions .= "AND d.width < '" . trim($min) . "' ";
}
}
// Both lower and upper limit
if ($max != '') {
if ($operator == 'OR') {
$conditions .= "OR (d.width >= '" . trim($min) . "' AND d.width <= '" . trim($max) . "') ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.width >= '" . trim($min) . "' AND d.width <= '" . trim($max) . "') ";
} else {
$conditions .= "AND (d.width < '" . trim($min) . "' OR d.width > '" . trim($max) . "') ";
}
}
} else {
// Single value
if ($operator == 'OR') {
$conditions .= "OR d.width = '" . trim($part_array[$k]) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.width = '" . trim($part_array[$k]) . "' ";
} else {
$conditions .= "AND d.width != '" . trim($part_array[$k]) . "' ";
}
}
}
$onlykeywords = false;
} elseif (substr($part_array[$k], 0, 7) == 'height:') {
$part_array[$k] = substr($part_array[$k], 7);
if ($part_array[$k] != '' && $part_array[$k] != ' ') {
if (is_int(strpos($part_array[$k], '-'))) {
// Height range
list($min, $max) = explode('-', $part_array[$k]);
// Only upper limit, set lower limit to make sure not text ads are delivered
if ($min == '') {
$min = 1;
}
// Only lower limit
if ($max == '') {
if ($operator == 'OR') {
$conditions .= "OR d.height >= '" . trim($min) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.height >= '" . trim($min) . "' ";
} else {
$conditions .= "AND d.height < '" . trim($min) . "' ";
}
}
// Both lower and upper limit
if ($max != '') {
if ($operator == 'OR') {
$conditions .= "OR (d.height >= '" . trim($min) . "' AND d.height <= '" . trim($max) . "') ";
} elseif ($operator == 'AND') {
$conditions .= "AND (d.height >= '" . trim($min) . "' AND d.height <= '" . trim($max) . "') ";
} else {
$conditions .= "AND (d.height < '" . trim($min) . "' OR d.height > '" . trim($max) . "') ";
}
}
} else {
// Single value
if ($operator == 'OR') {
$conditions .= "OR d.height = '" . trim($part_array[$k]) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.height = '" . trim($part_array[$k]) . "' ";
} else {
$conditions .= "AND d.height != '" . trim($part_array[$k]) . "' ";
}
}
}
$onlykeywords = false;
} elseif (preg_match('#^(?:(?:bannerid|adid|ad_id):)?([0-9]+)$#', $part_array[$k], $m)) {
$part_array[$k] = $m[1];
if ($part_array[$k]) {
if ($operator == 'OR') {
$conditions .= "OR d.bannerid='" . $part_array[$k] . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.bannerid='" . $part_array[$k] . "' ";
} else {
$conditions .= "AND d.bannerid!='" . $part_array[$k] . "' ";
}
}
$onlykeywords = false;
} elseif (preg_match('#^(?:(?:clientid|campaignid|placementid|placement_id):)?([0-9]+)$#', $part_array[$k], $m)) {
$part_array[$k] = $m[1];
if ($part_array[$k]) {
if ($operator == 'OR') {
$conditions .= "OR d.campaignid='" . trim($part_array[$k]) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.campaignid='" . trim($part_array[$k]) . "' ";
} else {
$conditions .= "AND d.campaignid!='" . trim($part_array[$k]) . "' ";
}
}
$onlykeywords = false;
} elseif (substr($part_array[$k], 0, 7) == 'format:') {
$part_array[$k] = substr($part_array[$k], 7);
if ($part_array[$k] != '' && $part_array[$k] != ' ') {
if ($operator == 'OR') {
$conditions .= "OR d.contenttype='" . trim($part_array[$k]) . "' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.contenttype='" . trim($part_array[$k]) . "' ";
} else {
$conditions .= "AND d.contenttype!='" . trim($part_array[$k]) . "' ";
}
}
$onlykeywords = false;
} elseif ($part_array[$k] == 'html') {
if ($operator == 'OR') {
$conditions .= "OR d.storagetype='html' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.storagetype='html' ";
} else {
$conditions .= "AND d.storagetype!='html' ";
}
$onlykeywords = false;
} elseif ($part_array[$k] == 'textad') {
if ($operator == 'OR') {
$conditions .= "OR d.storagetype='txt' ";
} elseif ($operator == 'AND') {
$conditions .= "AND d.storagetype='txt' ";
} else {
$conditions .= "AND d.storagetype!='txt' ";
}
$onlykeywords = false;
} else {
$conditions .= OA_Dal_Delivery_getKeywordCondition($operator, $part_array[$k]);
}
}
}
// Strip first AND or OR from $conditions
$conditions = strstr($conditions, ' ');
// Add global keyword
if ($lastpart == true && $onlykeywords == true) {
$conditions .= OA_Dal_Delivery_getKeywordCondition('OR', 'global');
}
// Add conditions to select
if ($conditions != '') {
$select .= ' AND (' . $conditions . ') ';
}
}
$columns = implode(",\n ", $aColumns);
$tables = implode("\n ", $aTables);
$leftJoin = "\nLEFT JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['clients']) . " AS c ON (c.clientid = m.clientid)\nLEFT JOIN " . OX_escapeIdentifier($conf['table']['prefix'] . $conf['table']['agency']) . " AS a ON (a.agencyid = c.agencyid)\n";
$query = "SELECT\n " . $columns . "\nFROM\n " . $tables . $leftJoin . "\nWHERE " . $select;
return $query;
}
