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