public function build($params)
{
$simple_tableless_objects = PodsForm::simple_tableless_objects();
$file_field_types = PodsForm::file_field_types();
$defaults = array('select' => '*', 'calc_rows' => false, 'distinct' => true, 'table' => null, 'join' => null, 'where' => null, 'groupby' => null, 'having' => null, 'orderby' => null, 'limit' => -1, 'offset' => null, 'id' => null, 'index' => null, 'page' => 1, 'pagination' => $this->pagination, 'search' => $this->search, 'search_query' => null, 'search_mode' => null, 'search_across' => false, 'search_across_picks' => false, 'search_across_files' => false, 'filters' => array(), 'fields' => array(), 'object_fields' => array(), 'pod_table_prefix' => null, 'traverse' => array(), 'sql' => null, 'strict' => false);
$params = (object) array_merge($defaults, (array) $params);
if (0 < strlen($params->sql)) {
return $params->sql;
}
$pod = false;
if (is_array($this->pod_data)) {
$pod = $this->pod_data;
}
// Validate
$params->page = pods_absint($params->page);
$params->pagination = (bool) $params->pagination;
if (0 == $params->page || !$params->pagination) {
$params->page = 1;
}
$params->limit = (int) $params->limit;
if (0 == $params->limit) {
$params->limit = -1;
}
$this->limit = $params->limit;
$offset = $params->limit * ($params->page - 1);
if (0 < (int) $params->offset) {
$params->offset += $offset;
} else {
$params->offset = $offset;
}
if (!$params->pagination || -1 == $params->limit) {
$params->page = 1;
$params->offset = 0;
}
if ((empty($params->fields) || !is_array($params->fields)) && !empty($pod) && isset($this->fields) && !empty($this->fields)) {
$params->fields = $this->fields;
}
if ((empty($params->object_fields) || !is_array($params->object_fields)) && !empty($pod) && isset($pod['object_fields']) && !empty($pod['object_fields'])) {
$params->object_fields = $pod['object_fields'];
}
if (empty($params->filters) && $params->search) {
$params->filters = array_keys($params->fields);
} elseif (empty($params->filters)) {
$params->filters = array();
}
if (empty($params->index)) {
$params->index = $this->field_index;
}
if (empty($params->id)) {
$params->id = $this->field_id;
}
if (empty($params->table) && !empty($pod) && isset($this->table) && !empty($this->table)) {
$params->table = $this->table;
}
if (empty($params->pod_table_prefix)) {
$params->pod_table_prefix = 't';
}
if (!empty($pod) && !in_array($pod['type'], array('pod', 'table')) && 'table' == $pod['storage']) {
$params->pod_table_prefix = 'd';
}
$params->meta_fields = false;
if (!empty($pod) && !in_array($pod['type'], array('pod', 'table')) && ('meta' == $pod['storage'] || 'none' == $pod['storage'] && function_exists('get_term_meta'))) {
$params->meta_fields = true;
}
if (empty($params->table)) {
return false;
}
if (false === strpos($params->table, '(') && false === strpos($params->table, '`')) {
$params->table = '`' . $params->table . '`';
}
if (!empty($params->join)) {
$params->join = array_merge((array) $this->join, (array) $params->join);
} elseif (false === $params->strict) {
$params->join = $this->join;
}
$params->where_defaulted = false;
$params->where_default = $this->where_default;
if (false === $params->strict) {
// Set default where
if (!empty($this->where_default) && empty($params->where)) {
$params->where = array_values((array) $this->where_default);
$params->where_defaulted = true;
}
if (!empty($this->where)) {
if (is_array($params->where) && isset($params->where['relation']) && 'OR' == strtoupper($params->where['relation'])) {
$params->where = array_merge(array($params->where), array_values((array) $this->where));
} else {
$params->where = array_merge((array) $params->where, array_values((array) $this->where));
}
}
}
// Allow where array ( 'field' => 'value' ) and WP_Query meta_query syntax
if (!empty($params->where)) {
$params->where = $this->query_fields((array) $params->where, $pod, $params);
}
if (empty($params->where)) {
$params->where = array();
} else {
$params->where = (array) $params->where;
}
// Allow having array ( 'field' => 'value' ) and WP_Query meta_query syntax
if (!empty($params->having)) {
$params->having = $this->query_fields((array) $params->having, $pod, $params);
}
if (empty($params->having)) {
$params->having = array();
} else {
$params->having = (array) $params->having;
}
if (!empty($params->orderby)) {
if ('post_type' == $pod['type'] && 'meta' == $pod['storage'] && is_array($params->orderby)) {
foreach ($params->orderby as $i => $orderby) {
if (strpos($orderby, '.meta_value_num')) {
$params->orderby[$i] = 'CAST(' . str_replace('.meta_value_num', '.meta_value', $orderby) . ' AS DECIMAL)';
} elseif (strpos($orderby, '.meta_value_date')) {
$params->orderby[$i] = 'CAST(' . str_replace('.meta_value_date', '.meta_value', $orderby) . ' AS DATE)';
}
}
}
$params->orderby = (array) $params->orderby;
} else {
$params->orderby = array();
}
if (false === $params->strict && !empty($this->orderby)) {
$params->orderby = array_merge($params->orderby, (array) $this->orderby);
}
if (!empty($params->traverse)) {
$this->traverse = $params->traverse;
}
$allowed_search_modes = array('int', 'text', 'text_like');
if (!empty($params->search_mode) && in_array($params->search_mode, $allowed_search_modes)) {
$this->search_mode = $params->search_mode;
}
$params->search = (bool) $params->search;
if (1 == pods_v('pods_debug_params_all', 'get', 0) && pods_is_admin(array('pods'))) {
pods_debug($params);
}
$params->field_table_alias = 't';
// Get Aliases for future reference
$selectsfound = '';
if (!empty($params->select)) {
if (is_array($params->select)) {
$selectsfound = implode(', ', $params->select);
} else {
$selectsfound = $params->select;
}
}
// Pull Aliases from SQL query too
if (null !== $params->sql) {
$temp_sql = ' ' . trim(str_replace(array("\n", "\r"), ' ', $params->sql));
$temp_sql = preg_replace(array('/\\sSELECT\\sSQL_CALC_FOUND_ROWS\\s/i', '/\\sSELECT\\s/i'), array(' SELECT ', ' SELECT SQL_CALC_FOUND_ROWS '), $temp_sql);
preg_match('/\\sSELECT SQL_CALC_FOUND_ROWS\\s(.*)\\sFROM/i', $temp_sql, $selectmatches);
if (isset($selectmatches[1]) && !empty($selectmatches[1]) && false !== stripos($selectmatches[1], ' AS ')) {
$selectsfound .= (!empty($selectsfound) ? ', ' : '') . $selectmatches[1];
}
}
// Build Alias list
$this->aliases = array();
if (!empty($selectsfound) && false !== stripos($selectsfound, ' AS ')) {
$theselects = array_filter(explode(', ', $selectsfound));
if (empty($theselects)) {
$theselects = array_filter(explode(',', $selectsfound));
}
foreach ($theselects as $selected) {
$selected = trim($selected);
if (strlen($selected) < 1) {
continue;
}
$selectfield = explode(' AS ', str_replace(' as ', ' AS ', $selected));
if (2 == count($selectfield)) {
$field = trim(trim($selectfield[1]), '`');
$real_field = trim(trim($selectfield[0]), '`');
$this->aliases[$field] = $real_field;
}
}
}
// Search
if (!empty($params->search) && !empty($params->fields)) {
if (false !== $params->search_query && 0 < strlen($params->search_query)) {
$where = $having = array();
if (false !== $params->search_across) {
foreach ($params->fields as $key => $field) {
if (is_array($field)) {
$attributes = $field;
$field = pods_v('name', $field, $key, true);
} else {
$attributes = array('type' => '', 'options' => array());
}
if (isset($attributes['options']['search']) && !$attributes['options']['search']) {
continue;
}
if (in_array($attributes['type'], array('date', 'time', 'datetime', 'number', 'decimal', 'currency', 'phone', 'password', 'boolean'))) {
continue;
}
$fieldfield = '`' . $field . '`';
if ('pick' == $attributes['type'] && !in_array(pods_v('pick_object', $attributes), $simple_tableless_objects)) {
if (false === $params->search_across_picks) {
continue;
} else {
if (empty($attributes['table_info'])) {
$attributes['table_info'] = $this->api->get_table_info(pods_v('pick_object', $attributes), pods_v('pick_val', $attributes));
}
if (empty($attributes['table_info']['field_index'])) {
continue;
}
$fieldfield = $fieldfield . '.`' . $attributes['table_info']['field_index'] . '`';
}
} elseif (in_array($attributes['type'], $file_field_types)) {
if (false === $params->search_across_files) {
continue;
} else {
$fieldfield = $fieldfield . '.`post_title`';
}
} elseif (isset($params->fields[$field])) {
if ($params->meta_fields) {
$fieldfield = $fieldfield . '.`' . $params->pod_table_prefix . '`';
} else {
$fieldfield = '`' . $params->pod_table_prefix . '`.' . $fieldfield;
}
} elseif (!empty($params->object_fields) && !isset($params->object_fields[$field]) && 'meta' == $pod['storage']) {
$fieldfield = $fieldfield . '.`meta_value`';
} else {
$fieldfield = '`t`.' . $fieldfield;
}
if (isset($this->aliases[$field])) {
$fieldfield = '`' . $this->aliases[$field] . '`';
}
if (!empty($attributes['real_name'])) {
$fieldfield = $attributes['real_name'];
}
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "{$fieldfield} LIKE '%" . pods_sanitize_like($params->search_query) . "%'";
} else {
$where[] = "{$fieldfield} LIKE '%" . pods_sanitize_like($params->search_query) . "%'";
}
}
} elseif (!empty($params->index)) {
$attributes = array();
$fieldfield = '`t`.`' . $params->index . '`';
if (isset($params->fields[$params->index])) {
if ($params->meta_fields) {
$fieldfield = '`' . $params->index . '`.`' . $params->pod_table_prefix . '`';
} else {
$fieldfield = '`' . $params->pod_table_prefix . '`.`' . $params->index . '`';
}
} elseif (!empty($params->object_fields) && !isset($params->object_fields[$params->index]) && 'meta' == $pod['storage']) {
$fieldfield = '`' . $params->index . '`.`meta_value`';
}
if (isset($attributes['real_name']) && false !== $attributes['real_name'] && !empty($attributes['real_name'])) {
$fieldfield = $attributes['real_name'];
}
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "{$fieldfield} LIKE '%" . pods_sanitize_like($params->search_query) . "%'";
} else {
$where[] = "{$fieldfield} LIKE '%" . pods_sanitize_like($params->search_query) . "%'";
}
}
if (!empty($where)) {
$params->where[] = '( ' . implode(' OR ', $where) . ' )';
}
if (!empty($having)) {
$params->having[] = '( ' . implode(' OR ', $having) . ' )';
}
}
// Filter
foreach ($params->filters as $filter) {
$where = $having = array();
if (!isset($params->fields[$filter])) {
continue;
}
$attributes = $params->fields[$filter];
$field = pods_v('name', $attributes, $filter, true);
$filterfield = '`' . $field . '`';
if ('pick' == $attributes['type'] && !in_array(pods_v('pick_object', $attributes), $simple_tableless_objects)) {
if (empty($attributes['table_info'])) {
$attributes['table_info'] = $this->api->get_table_info(pods_v('pick_object', $attributes), pods_v('pick_val', $attributes));
}
if (empty($attributes['table_info']['field_index'])) {
continue;
}
$filterfield = $filterfield . '.`' . $attributes['table_info']['field_index'] . '`';
} elseif (in_array($attributes['type'], $file_field_types)) {
$filterfield = $filterfield . '.`post_title`';
} elseif (isset($params->fields[$field])) {
if ($params->meta_fields && 'meta' == $pod['storage']) {
$filterfield = $filterfield . '.`meta_value`';
} else {
$filterfield = '`' . $params->pod_table_prefix . '`.' . $filterfield;
}
} elseif (!empty($params->object_fields) && !isset($params->object_fields[$field]) && 'meta' == $pod['storage']) {
$filterfield = $filterfield . '.`meta_value`';
} else {
$filterfield = '`t`.' . $filterfield;
}
if (isset($this->aliases[$field])) {
$filterfield = '`' . $this->aliases[$field] . '`';
}
if (!empty($attributes['real_name'])) {
$filterfield = $attributes['real_name'];
}
if ('pick' == $attributes['type']) {
$filter_value = pods_v('filter_' . $field, 'get');
if (!is_array($filter_value)) {
$filter_value = (array) $filter_value;
}
foreach ($filter_value as $filter_v) {
if (in_array(pods_v('pick_object', $attributes), $simple_tableless_objects)) {
if (strlen($filter_v) < 1) {
continue;
}
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "( {$filterfield} = '" . pods_sanitize($filter_v) . "'" . " OR {$filterfield} LIKE '%\"" . pods_sanitize_like($filter_v) . "\"%' )";
} else {
$where[] = "( {$filterfield} = '" . pods_sanitize($filter_v) . "'" . " OR {$filterfield} LIKE '%\"" . pods_sanitize_like($filter_v) . "\"%' )";
}
} else {
$filter_v = (int) $filter_v;
if (empty($filter_v) || empty($attributes['table_info']) || empty($attributes['table_info']['field_id'])) {
continue;
}
$filterfield = '`' . $field . '`.`' . $attributes['table_info']['field_id'] . '`';
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "{$filterfield} = " . $filter_v;
} else {
$where[] = "{$filterfield} = " . $filter_v;
}
}
}
} elseif (in_array($attributes['type'], array('date', 'datetime'))) {
$start_value = pods_v('filter_' . $field . '_start', 'get', false);
$end_value = pods_v('filter_' . $field . '_end', 'get', false);
if (empty($start_value) && empty($end_value)) {
continue;
}
if (!empty($start_value)) {
$start = date_i18n('Y-m-d', strtotime($start_value)) . ('datetime' == $attributes['type'] ? ' 00:00:00' : '');
} else {
$start = date_i18n('Y-m-d') . ('datetime' == $attributes['type']) ? ' 00:00:00' : '';
}
if (!empty($end_value)) {
$end = date_i18n('Y-m-d', strtotime($end_value)) . ('datetime' == $attributes['type'] ? ' 23:59:59' : '');
} else {
$end = date_i18n('Y-m-d') . ('datetime' == $attributes['type']) ? ' 23:59:59' : '';
}
if (isset($attributes['date_ongoing']) && true === $attributes['date_ongoing']) {
$date_ongoing = '`' . $attributes['date_ongoing'] . '`';
if (isset($this->aliases[$date_ongoing])) {
$date_ongoing = '`' . $this->aliases[$date_ongoing] . '`';
}
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "(({$filterfield} <= '{$start}' OR ({$filterfield} >= '{$start}' AND {$filterfield} <= '{$end}')) AND ({$date_ongoing} >= '{$start}' OR ({$date_ongoing} >= '{$start}' AND {$date_ongoing} <= '{$end}')))";
} else {
$where[] = "(({$filterfield} <= '{$start}' OR ({$filterfield} >= '{$start}' AND {$filterfield} <= '{$end}')) AND ({$date_ongoing} >= '{$start}' OR ({$date_ongoing} >= '{$start}' AND {$date_ongoing} <= '{$end}')))";
}
} else {
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "({$filterfield} BETWEEN '{$start}' AND '{$end}')";
} else {
$where[] = "({$filterfield} BETWEEN '{$start}' AND '{$end}')";
}
}
} else {
$filter_value = pods_v('filter_' . $field, 'get', '');
if (strlen($filter_value) < 1) {
continue;
}
if (isset($attributes['group_related']) && false !== $attributes['group_related']) {
$having[] = "{$filterfield} LIKE '%" . pods_sanitize_like($filter_value) . "%'";
} else {
$where[] = "{$filterfield} LIKE '%" . pods_sanitize_like($filter_value) . "%'";
}
}
if (!empty($where)) {
$params->where[] = implode(' AND ', $where);
}
if (!empty($having)) {
$params->having[] = implode(' AND ', $having);
}
}
}
// Traverse the Rabbit Hole
if (!empty($this->pod)) {
$haystack = implode(' ', (array) $params->select) . ' ' . implode(' ', (array) $params->where) . ' ' . implode(' ', (array) $params->groupby) . ' ' . implode(' ', (array) $params->having) . ' ' . implode(' ', (array) $params->orderby);
$haystack = preg_replace('/\\s/', ' ', $haystack);
$haystack = preg_replace('/\\w\\(/', ' ', $haystack);
$haystack = str_replace(array('(', ')', ' ', '\\\'', "\\\""), ' ', $haystack);
preg_match_all('/`?[\\w\\-]+`?(?:\\.`?[\\w\\-]+`?)+(?=[^"\']*(?:"[^"]*"[^"]*|\'[^\']*\'[^\']*)*$)/', $haystack, $found, PREG_PATTERN_ORDER);
$found = (array) @current($found);
$find = $replace = $traverse = array();
foreach ($found as $key => $value) {
$value = str_replace('`', '', $value);
$value = explode('.', $value);
$dot = $last_value = array_pop($value);
if ('t' == $value[0]) {
continue;
} elseif (array_key_exists($value[0], $params->join)) {
// Don't traverse for tables we are already going to join
continue;
} elseif (1 == count($value) && '' == preg_replace('/[0-9]*/', '', $value[0]) && '' == preg_replace('/[0-9]*/', '', $last_value)) {
continue;
}
$found_value = str_replace('`', '', $found[$key]);
$found_value = '([`]{1}|\\b)' . str_replace('.', '[`]*\\.[`]*', $found_value) . '([`]{1}|\\b)';
$found_value = '/' . $found_value . '(?=[^"\']*(?:"[^"]*"[^"]*|\'[^\']*\'[^\']*)*$)/';
if (in_array($found_value, $find)) {
continue;
}
$find[$key] = $found_value;
if ('*' != $dot) {
$dot = '`' . $dot . '`';
}
$replace[$key] = '`' . implode('_', $value) . '`.' . $dot;
$value[] = $last_value;
if (!in_array($value, $traverse)) {
$traverse[$key] = $value;
}
}
if (!empty($this->traverse)) {
foreach ((array) $this->traverse as $key => $traverse) {
$traverse = str_replace('`', '', $traverse);
$already_found = false;
foreach ($traverse as $traversal) {
if (is_array($traversal)) {
$traversal = implode('.', $traversal);
}
if ($traversal == $traverse) {
$already_found = true;
break;
}
}
if (!$already_found) {
$traverse['traverse_' . $key] = explode('.', $traverse);
}
}
}
$joins = array();
if (!empty($find)) {
// See: "#3294 OrderBy Failing on PHP7" Non-zero array keys
// here in PHP 7 cause odd behavior so just strip the keys
$find = array_values($find);
$replace = array_values($replace);
$params->select = preg_replace($find, $replace, $params->select);
$params->where = preg_replace($find, $replace, $params->where);
$params->groupby = preg_replace($find, $replace, $params->groupby);
$params->having = preg_replace($find, $replace, $params->having);
$params->orderby = preg_replace($find, $replace, $params->orderby);
if (!empty($traverse)) {
$joins = $this->traverse($traverse, $params->fields, $params);
} elseif (false !== $params->search) {
$joins = $this->traverse(null, $params->fields, $params);
}
}
}
// Traversal Search
if (!empty($params->search) && !empty($this->search_where)) {
$params->where = array_merge((array) $this->search_where, $params->where);
}
if (!empty($params->join) && !empty($joins)) {
$params->join = array_merge($joins, (array) $params->join);
} elseif (!empty($joins)) {
$params->join = $joins;
}
// Build
if (null === $params->sql) {
$sql = "\n SELECT\n " . ($params->calc_rows ? 'SQL_CALC_FOUND_ROWS' : '') . "\n " . ($params->distinct ? 'DISTINCT' : '') . "\n " . (!empty($params->select) ? is_array($params->select) ? implode(', ', $params->select) : $params->select : '*') . "\n FROM {$params->table} AS `t`\n " . (!empty($params->join) ? is_array($params->join) ? implode("\n ", $params->join) : $params->join : '') . "\n " . (!empty($params->where) ? 'WHERE ' . (is_array($params->where) ? implode(' AND ', $params->where) : $params->where) : '') . "\n " . (!empty($params->groupby) ? 'GROUP BY ' . (is_array($params->groupby) ? implode(', ', $params->groupby) : $params->groupby) : '') . "\n " . (!empty($params->having) ? 'HAVING ' . (is_array($params->having) ? implode(' AND ', $params->having) : $params->having) : '') . "\n " . (!empty($params->orderby) ? 'ORDER BY ' . (is_array($params->orderby) ? implode(', ', $params->orderby) : $params->orderby) : '') . "\n " . (0 < $params->page && 0 < $params->limit ? 'LIMIT ' . $params->offset . ', ' . $params->limit : '') . "\n ";
if (!$params->calc_rows) {
// Handle COUNT() SELECT
$total_sql_select = "COUNT( " . ($params->distinct ? 'DISTINCT `t`.`' . $params->id . '`' : '*') . " )";
// If 'having' is set, we have to select all so it has access to anything it needs
if (!empty($params->having)) {
$total_sql_select .= ', ' . (!empty($params->select) ? is_array($params->select) ? implode(', ', $params->select) : $params->select : '*');
}
$this->total_sql = "\n\t\t\t\t\tSELECT {$total_sql_select}\n\t\t\t\t\tFROM {$params->table} AS `t`\n\t\t\t\t\t" . (!empty($params->join) ? is_array($params->join) ? implode("\n ", $params->join) : $params->join : '') . "\n\t\t\t\t\t" . (!empty($params->where) ? 'WHERE ' . (is_array($params->where) ? implode(' AND ', $params->where) : $params->where) : '') . "\n\t\t\t\t\t" . (!empty($params->groupby) ? 'GROUP BY ' . (is_array($params->groupby) ? implode(', ', $params->groupby) : $params->groupby) : '') . "\n\t\t\t\t\t" . (!empty($params->having) ? 'HAVING ' . (is_array($params->having) ? implode(' AND ', $params->having) : $params->having) : '') . "\n\t\t\t\t";
}
} else {
$sql = ' ' . trim(str_replace(array("\n", "\r"), ' ', $params->sql));
$sql = preg_replace(array('/\\sSELECT\\sSQL_CALC_FOUND_ROWS\\s/i', '/\\sSELECT\\s/i'), array(' SELECT ', ' SELECT SQL_CALC_FOUND_ROWS '), $sql);
// Insert variables based on existing statements
if (false === stripos($sql, '%%SELECT%%')) {
$sql = preg_replace('/\\sSELECT\\sSQL_CALC_FOUND_ROWS\\s/i', ' SELECT SQL_CALC_FOUND_ROWS %%SELECT%% ', $sql);
}
if (false === stripos($sql, '%%WHERE%%')) {
$sql = preg_replace('/\\sWHERE\\s(?!.*\\sWHERE\\s)/i', ' WHERE %%WHERE%% ', $sql);
}
if (false === stripos($sql, '%%GROUPBY%%')) {
$sql = preg_replace('/\\sGROUP BY\\s(?!.*\\sGROUP BY\\s)/i', ' GROUP BY %%GROUPBY%% ', $sql);
}
if (false === stripos($sql, '%%HAVING%%')) {
$sql = preg_replace('/\\sHAVING\\s(?!.*\\sHAVING\\s)/i', ' HAVING %%HAVING%% ', $sql);
}
if (false === stripos($sql, '%%ORDERBY%%')) {
$sql = preg_replace('/\\sORDER BY\\s(?!.*\\sORDER BY\\s)/i', ' ORDER BY %%ORDERBY%% ', $sql);
}
// Insert variables based on other existing statements
if (false === stripos($sql, '%%JOIN%%')) {
if (false !== stripos($sql, ' WHERE ')) {
$sql = preg_replace('/\\sWHERE\\s(?!.*\\sWHERE\\s)/i', ' %%JOIN%% WHERE ', $sql);
} elseif (false !== stripos($sql, ' GROUP BY ')) {
$sql = preg_replace('/\\sGROUP BY\\s(?!.*\\sGROUP BY\\s)/i', ' %%WHERE%% GROUP BY ', $sql);
} elseif (false !== stripos($sql, ' ORDER BY ')) {
$sql = preg_replace('/\\ORDER BY\\s(?!.*\\ORDER BY\\s)/i', ' %%WHERE%% ORDER BY ', $sql);
} else {
$sql .= ' %%JOIN%% ';
}
}
if (false === stripos($sql, '%%WHERE%%')) {
if (false !== stripos($sql, ' GROUP BY ')) {
$sql = preg_replace('/\\sGROUP BY\\s(?!.*\\sGROUP BY\\s)/i', ' %%WHERE%% GROUP BY ', $sql);
} elseif (false !== stripos($sql, ' ORDER BY ')) {
$sql = preg_replace('/\\ORDER BY\\s(?!.*\\ORDER BY\\s)/i', ' %%WHERE%% ORDER BY ', $sql);
} else {
$sql .= ' %%WHERE%% ';
}
}
if (false === stripos($sql, '%%GROUPBY%%')) {
if (false !== stripos($sql, ' HAVING ')) {
$sql = preg_replace('/\\sHAVING\\s(?!.*\\sHAVING\\s)/i', ' %%GROUPBY%% HAVING ', $sql);
} elseif (false !== stripos($sql, ' ORDER BY ')) {
$sql = preg_replace('/\\ORDER BY\\s(?!.*\\ORDER BY\\s)/i', ' %%GROUPBY%% ORDER BY ', $sql);
} else {
$sql .= ' %%GROUPBY%% ';
}
}
if (false === stripos($sql, '%%HAVING%%')) {
if (false !== stripos($sql, ' ORDER BY ')) {
$sql = preg_replace('/\\ORDER BY\\s(?!.*\\ORDER BY\\s)/i', ' %%HAVING%% ORDER BY ', $sql);
} else {
$sql .= ' %%HAVING%% ';
}
}
if (false === stripos($sql, '%%ORDERBY%%')) {
$sql .= ' %%ORDERBY%% ';
}
if (false === stripos($sql, '%%LIMIT%%')) {
$sql .= ' %%LIMIT%% ';
}
// Replace variables
if (0 < strlen($params->select)) {
if (false === stripos($sql, '%%SELECT%% FROM ')) {
$sql = str_ireplace('%%SELECT%%', $params->select . ', ', $sql);
} else {
$sql = str_ireplace('%%SELECT%%', $params->select, $sql);
}
}
if (0 < strlen($params->join)) {
$sql = str_ireplace('%%JOIN%%', $params->join, $sql);
}
if (0 < strlen($params->where)) {
if (false !== stripos($sql, ' WHERE ')) {
if (false !== stripos($sql, ' WHERE %%WHERE%% ')) {
$sql = str_ireplace('%%WHERE%%', $params->where . ' AND ', $sql);
} else {
$sql = str_ireplace('%%WHERE%%', ' AND ' . $params->where, $sql);
}
} else {
$sql = str_ireplace('%%WHERE%%', ' WHERE ' . $params->where, $sql);
}
}
if (0 < strlen($params->groupby)) {
if (false !== stripos($sql, ' GROUP BY ')) {
if (false !== stripos($sql, ' GROUP BY %%GROUPBY%% ')) {
$sql = str_ireplace('%%GROUPBY%%', $params->groupby . ', ', $sql);
} else {
$sql = str_ireplace('%%GROUPBY%%', ', ' . $params->groupby, $sql);
}
} else {
$sql = str_ireplace('%%GROUPBY%%', ' GROUP BY ' . $params->groupby, $sql);
}
}
if (0 < strlen($params->having) && false !== stripos($sql, ' GROUP BY ')) {
if (false !== stripos($sql, ' HAVING ')) {
if (false !== stripos($sql, ' HAVING %%HAVING%% ')) {
$sql = str_ireplace('%%HAVING%%', $params->having . ' AND ', $sql);
} else {
$sql = str_ireplace('%%HAVING%%', ' AND ' . $params->having, $sql);
}
} else {
$sql = str_ireplace('%%HAVING%%', ' HAVING ' . $params->having, $sql);
}
}
if (0 < strlen($params->orderby)) {
if (false !== stripos($sql, ' ORDER BY ')) {
if (false !== stripos($sql, ' ORDER BY %%ORDERBY%% ')) {
$sql = str_ireplace('%%ORDERBY%%', $params->groupby . ', ', $sql);
} else {
$sql = str_ireplace('%%ORDERBY%%', ', ' . $params->groupby, $sql);
}
} else {
$sql = str_ireplace('%%ORDERBY%%', ' ORDER BY ' . $params->groupby, $sql);
}
}
if (0 < $params->page && 0 < $params->limit) {
$start = ($params->page - 1) * $params->limit;
$end = $start + $params->limit;
$sql .= 'LIMIT ' . (int) $start . ', ' . (int) $end;
}
// Clear any unused variables
$sql = str_ireplace(array('%%SELECT%%', '%%JOIN%%', '%%WHERE%%', '%%GROUPBY%%', '%%HAVING%%', '%%ORDERBY%%', '%%LIMIT%%'), '', $sql);
$sql = str_replace(array('``', '`'), array(' ', ' '), $sql);
}
return $sql;
}