function pages($params, $node, $mode = 0)
{
global $CTX, $FUNCS, $PAGE, $DB, $AUTH;
$attr = $FUNCS->get_named_vars(array('masterpage' => '', 'id' => '', 'page_name' => '', 'page_title' => '', 'is_master' => '0', 'limit' => '', 'offset' => '0', 'startcount' => '', 'folder' => '', 'include_subfolders' => '1', 'start_on' => '', 'stop_before' => '', 'show_future_entries' => '0', 'orderby' => '', 'order' => '', 'paginate' => '0', 'custom_field' => '', 'skip_custom_fields' => '0', 'keywords' => '', 'page_id' => '', 'pid' => '', 'cid' => '', 'fid' => '', 'qs_param' => '', 'count_only' => '0', 'ids_only' => '0', 'sql' => '', 'fetch_pages' => '0', 'return_sql' => '0', 'show_unpublished' => '0', 'aggregate_by' => '', 'base_link' => ''), $params);
// HOOK: alter_page_tag_params
$FUNCS->dispatch_event('alter_page_tag_params', array(&$attr, $params, $node, &$mode));
extract($attr);
// sanitize params
$masterpage = trim($masterpage);
$page_name = trim($page_name);
$page_title = trim($page_title);
$limit = $FUNCS->is_non_zero_natural($limit) ? intval($limit) : 1000;
//Practically unlimited.
$offset = $FUNCS->is_natural($offset) ? intval($offset) : 0;
$startcount = $FUNCS->is_int($startcount) ? intval($startcount) : 1;
if ($mode == 1) {
$paginate = 1;
}
//pagination always on for 'search' tag
if ($mode == 3) {
$paginate = 0;
}
//pagination always off for 'calendar' tag
$paginate = $paginate == 1 ? 1 : 0;
$pgn_pno = 1;
$skip_custom_fields = $skip_custom_fields == 1 ? 1 : 0;
$count_only = $count_only == 1 ? 1 : 0;
$ids_only = $ids_only == 1 ? 1 : 0;
$raw_sql = trim($sql);
$fetch_pages = $fetch_pages == 1 ? 1 : 0;
$return_sql = $return_sql == 1 || $return_sql == 2 ? intval($return_sql) : 0;
$show_unpublished = $show_unpublished == 1 ? 1 : 0;
$aggregate_by = trim($aggregate_by);
$base_link = trim($base_link);
$qs_param = trim($qs_param);
if ($qs_param == '') {
$qs_param = $mode == 2 ? 'comments_pg' : 'pg';
}
if ($paginate) {
if (isset($_GET[$qs_param]) && $FUNCS->is_non_zero_natural($_GET[$qs_param])) {
$pgn_pno = (int) $_GET[$qs_param];
}
}
$show_future_entries = $show_future_entries == 1 ? 1 : 0;
$hide_future_entries = !$show_future_entries;
$sql = '';
$order_sql = '';
$limit_sql = '';
$distinct = 0;
$group_by = array();
$having = array();
$count_query_field_as = 'cnt';
$rec_tpl = array();
if ($mode == 0 || $mode == 3 || $mode == 4) {
// 3 if called from calender tag, 4 if called from related_pages/reverse_related_pages tag
$query_table = K_TBL_PAGES . ' p';
$default_orderby = 'publish_date';
if ($mode == 4) {
// related pages
if ($pid) {
$query_table .= "\r\n" . 'inner join ' . K_TBL_RELATIONS . ' rel on rel.cid = p.id';
$sql .= "rel.pid=" . $DB->sanitize($pid) . " AND rel.fid=" . $DB->sanitize($fid) . " AND\r\n";
} elseif ($cid) {
// reverse related
$query_table .= "\r\n" . 'inner join ' . K_TBL_RELATIONS . ' rel on rel.pid = p.id';
$sql .= "rel.cid=" . $DB->sanitize($cid) . " AND rel.fid=" . $DB->sanitize($fid) . " AND\r\n";
} else {
//huh?
return;
}
$default_orderby = 'rel.weight';
$mode = 0;
// convert to the normal 'pages' tag
}
$query_fields = array('p.id', 'p.template_id');
if ($mode == 3) {
$query_fields[] = 'p.publish_date';
}
$count_query_field = 'p.id';
if ($masterpage == '') {
$masterpage = $PAGE->tpl_name;
}
$include_subfolders = $include_subfolders == 0 ? 0 : 1;
$start_on = trim($start_on);
if ($start_on) {
$start_on = $FUNCS->make_date($start_on);
}
$stop_before = trim($stop_before);
if ($stop_before) {
$stop_before = $FUNCS->make_date($stop_before);
}
// build the sql where clause using the supplied params.
// masterpage
$rs = $DB->select(K_TBL_TEMPLATES, array('*'), "name='" . $DB->sanitize($masterpage) . "'");
if (!count($rs)) {
die("ERROR: Tag \"" . $node->name . "\": masterpage '" . $FUNCS->cleanXSS($masterpage) . "' not found");
}
$rec_tpl = $rs[0];
$tpl_id = $rs[0]['id'];
$tpl_is_gallery = $rs[0]['gallery'];
$sql .= "p.template_id='" . $DB->sanitize($tpl_id) . "'";
// id?
if ($id) {
$sql .= $FUNCS->gen_sql($id, 'p.id', 1);
}
// name?
if ($page_name) {
$sql .= $FUNCS->gen_sql($page_name, 'p.page_name');
}
// title?
if ($page_title) {
$sql .= $FUNCS->gen_sql($page_title, 'p.page_title');
}
// folder?
$folder = trim($folder);
if ($folder != '') {
$arr_folders = array();
// get all the folders of the masterpage
if ($masterpage == $PAGE->tpl_name) {
$folders =& $PAGE->folders;
} else {
$folders =& $FUNCS->get_folders_tree($tpl_id, $masterpage);
}
// Negation?
$neg = 0;
$pos = strpos(strtoupper($folder), 'NOT ');
if ($pos !== false && $pos == 0) {
$neg = 1;
$folder = trim(substr($folder, strpos($folder, ' ')));
}
// multiple folders specified?
$arr_parent_folders = array_map("trim", explode(',', $folder));
foreach ($arr_parent_folders as $parent_folder) {
if ($parent_folder) {
// locate the folder
$f =& $folders->find($parent_folder);
if ($f) {
if ($include_subfolders) {
// get all the child folders of it
$sub_folders = $f->get_children();
//includes the parent folder too
foreach ($sub_folders as $sf) {
if (!array_key_exists($sf->name, $arr_folders)) {
$arr_folders[$sf->name] = $sf->id;
}
}
} else {
if (!array_key_exists($f->name, $arr_folders)) {
$arr_folders[$f->name] = $f->id;
}
}
}
}
}
if (count($arr_folders)) {
$sql .= " AND ";
if ($neg) {
$sql .= "NOT";
}
$sql .= "(";
$sep = "";
foreach ($arr_folders as $k => $v) {
$sql .= $sep . "p.page_folder_id='" . $DB->sanitize($v) . "'";
$sep = " OR ";
}
$sql .= ")";
}
} else {
if (!$include_subfolders) {
$sql .= " AND p.page_folder_id='-1'";
}
}
// is_master?
if ($is_master) {
$sql .= " AND p.is_master = '1'";
}
// dates
if ($start_on) {
$sql .= " AND p.publish_date >= '" . $DB->sanitize($start_on) . "'";
}
if ($hide_future_entries) {
$cur_time = $FUNCS->get_current_desktop_time();
$stop_before = $FUNCS->smaller_date($stop_before, $cur_time);
}
if ($stop_before) {
$sql .= " AND p.publish_date < '" . $DB->sanitize($stop_before) . "'";
}
if ($AUTH->user->access_level < K_ACCESS_LEVEL_ADMIN || !$show_unpublished) {
$sql .= " AND NOT p.publish_date = '0000-00-00 00:00:00'";
}
// orderby
// canonical orderby will be prefixed with 'p.' in generated sql
// TODO: order by folder_name, folder_title, recent_comment, relation fields
$arr_canonical_orderby = array('publish_date', 'page_name', 'page_title', 'modification_date', 'comments_count');
if ($tpl_is_gallery) {
$arr_canonical_orderby = array_merge($arr_canonical_orderby, array('file_name', 'file_ext', 'file_size'));
}
$arr_acceptable_orderby = array('random');
if ($aggregate_by) {
$arr_acceptable_orderby[] = 'k_rel_count';
}
// HOOK: alter_valid_orderby
$FUNCS->dispatch_event('alter_valid_orderby', array(&$arr_acceptable_orderby, &$arr_canonical_orderby, $params, $node, $rec_tpl));
$arr_acceptable_orderby = array_merge($arr_canonical_orderby, $arr_acceptable_orderby);
$arr_custom_orderby = array();
// custom fields in 'order_by' clause (points to the entry in $arr_orderby and $arr_order below)
if ($mode == 3) {
// for calendar, these two params are always fixed
$order = 'asc';
$orderby = 'publish_date';
}
$arr_order = array_map("strtolower", array_map("trim", explode(',', $order)));
$arr_orderby = array_map("strtolower", array_map("trim", explode(',', $orderby)));
for ($i = 0; $i < count($arr_orderby); $i++) {
$orderby = $arr_orderby[$i];
if ($orderby) {
if (!in_array($orderby, $arr_acceptable_orderby)) {
$arr_custom_orderby[$arr_orderby[$i]] = $i;
}
} else {
$arr_orderby[$i] = $default_orderby;
//'publish_date';
}
$order = $arr_order[$i];
if ($order != 'desc' && $order != 'asc') {
$arr_order[$i] = 'desc';
}
}
if ($mode == 0) {
// custom fields
// e:g custom_field='my_intro=East | my_price>100000'
$arr_ops = array('\\!==', '\\!=', '\\<=', '\\>=', '==', '=', '\\<\\>', '\\<', '\\>');
$arr_params = array_map("trim", preg_split("/(?<!\\\\)\\|/", $custom_field));
// split at unescaped pipe char
$arr_custom_fields = array();
foreach ($arr_params as $param) {
if (!$param) {
continue;
}
foreach ($arr_ops as $op) {
$pattern = "/(?<!\\\\)" . $op . "/";
//split at unescaped ops
if (preg_match($pattern, $param, $matches)) {
$arr_tmp = array_map("trim", preg_split($pattern, $param));
$key = $arr_tmp[0];
$val = array_map("trim", preg_split("/(?<!\\\\),/", $arr_tmp[1]));
//split at unescaped comma
for ($x = 0; $x < count($val); $x++) {
$val[$x] = $DB->sanitize(trim(stripslashes($val[$x]), "'\""));
}
$arr_custom_fields[] = array('name' => $key, 'val' => $val, 'op' => stripslashes($op));
break;
}
}
}
// if 'aggregate_by' specified, set it as a custom relational field
if ($aggregate_by) {
$arr_custom_fields[] = array('name' => $aggregate_by, 'op' => '=', 'val' => array(), 'is_aggregate' => 1);
}
// HOOK: alter_page_tag_fields
$FUNCS->dispatch_event('alter_page_tag_fields', array(&$arr_custom_fields, &$arr_orderby, &$arr_order, &$arr_custom_orderby, $params, $node, $rec_tpl));
$arr_rel_fields = array();
$arr_rel_types = array();
// HOOK: alter_relational_types
// modules can add their custom types that store data in relation table
$FUNCS->dispatch_event('alter_relational_types', array(&$arr_rel_types, $params, $node, $rec_tpl));
$arr_rel_types = array_merge(array('relation'), $arr_rel_types);
if (count($arr_custom_fields) || count($arr_custom_orderby)) {
// resolve custom field names to ids
$rs_cf = $DB->select(K_TBL_FIELDS, array('*'), "template_id='" . $DB->sanitize($tpl_id) . "'");
$arr_tables = array();
$count = 0;
for ($x = 0; $x < count($arr_custom_fields); $x++) {
if ($arr_custom_fields[$x]['processed']) {
continue;
}
// can be set from hook
// is it a 'relation' field with template name ( e.g. 'courses.php::taken' )?
if (strpos($arr_custom_fields[$x]['name'], '::') !== false) {
list($rel_field_tpl, $rel_field_name) = array_map("trim", explode('::', $arr_custom_fields[$x]['name']));
if ($rel_field_tpl != $masterpage) {
$arr_rel_fields[$rel_field_tpl][] = array('name' => $rel_field_name, 'op' => $arr_custom_fields[$x]['op'], 'val' => $arr_custom_fields[$x]['val'], 'is_aggregate' => $arr_custom_fields[$x]['is_aggregate']);
$arr_custom_fields[$x]['processed'] = 1;
continue;
} else {
$arr_custom_fields[$x]['name'] = $rel_field_name;
}
}
// is it the 'k_rel_count' field that becomes available with 'aggregate_by'?
if ($aggregate_by && $arr_custom_fields[$x]['name'] == 'k_rel_count') {
$rel_op = $arr_custom_fields[$x]['op'];
if ($rel_op == '==') {
$rel_op = '=';
} elseif ($rel_op == '!==' || $rel_op == '!=') {
$rel_op = '<>';
}
$rel_val = $arr_custom_fields[$x]['val'][0];
if ($FUNCS->_validate_natural($rel_val)) {
$having[] = "k_rel_count {$rel_op} {$rel_val}";
$count_query_field_as = 'k_rel_count';
}
$arr_custom_fields[$x]['processed'] = 1;
continue;
}
for ($i = 0; $i < count($rs_cf); $i++) {
$f = $rs_cf[$i];
if ($f['name'] == $arr_custom_fields[$x]['name']) {
// 'relation' field?
if (in_array($f['k_type'], $arr_rel_types)) {
$arr_rel_fields[$masterpage][] = array('related_field' => $f, 'name' => $f['name'], 'op' => $arr_custom_fields[$x]['op'], 'val' => $arr_custom_fields[$x]['val'], 'id' => $f['id'], 'is_aggregate' => $arr_custom_fields[$x]['is_aggregate']);
$arr_custom_fields[$x]['processed'] = 1;
continue 2;
}
$arr_custom_fields[$x]['id'] = $f['id'];
$arr_custom_fields[$x]['type'] = $f['search_type'];
$arr_custom_fields[$x]['field_name'] = $f['search_type'] == 'text' ? 'search_value' : 'value';
if ($f['search_type'] == 'text') {
if ($arr_custom_fields[$x]['op'] == '=' || $arr_custom_fields[$x]['op'] == '!=') {
if ($arr_custom_fields[$x]['op'] == '=') {
$arr_custom_fields[$x]['op'] = 'LIKE';
} else {
$arr_custom_fields[$x]['op'] = 'NOT LIKE';
}
for ($c = 0; $c < count($arr_custom_fields[$x]['val']); $c++) {
$arr_custom_fields[$x]['val'][$c] = "%" . $arr_custom_fields[$x]['val'][$c] . "%";
}
}
}
if ($arr_custom_fields[$x]['op'] == '==') {
$arr_custom_fields[$x]['op'] = '=';
} elseif ($arr_custom_fields[$x]['op'] == '!==') {
$arr_custom_fields[$x]['op'] = '!=';
}
if (!array_key_exists($f['name'], $arr_tables)) {
$arr_tables[$f['name']]['id'] = $f['id'];
$arr_tables[$f['name']]['tbl_name'] = $f['search_type'] == 'text' ? K_TBL_DATA_TEXT : K_TBL_DATA_NUMERIC;
$arr_tables[$f['name']]['alias'] = sprintf("t%d", $count++);
}
$arr_custom_fields[$x]['table_name'] = $arr_tables[$f['name']]['alias'];
break;
}
}
if (!array_key_exists('id', $arr_custom_fields[$x])) {
die("ERROR: Custom Field \"" . $arr_custom_fields[$x]['name'] . "\" does not exist in '" . $FUNCS->cleanXSS($masterpage) . "'");
}
}
// resolve relation fields
if (count($arr_rel_fields)) {
foreach ($arr_rel_fields as $rel_field_tpl => $rel_fields) {
// for each masterpage
$rel_field_tpl_id = 0;
$rel_count = 0;
foreach ($rel_fields as $rel_field) {
// for each relation field in the masterpage
if ($rel_field['op'] !== '=' && $rel_field['op'] !== '!=') {
die("ERROR: Tag \"" . $node->name . "\": custom field '" . $FUNCS->cleanXSS($rel_field['name']) . "' does not support '" . $rel_field['op'] . "' operator");
}
// id of the field itself
if ($rel_field_tpl != $masterpage) {
if (!$rel_field_tpl_id) {
// get template_id of masterpage
$rs = $DB->select(K_TBL_TEMPLATES, array('id'), "name='" . $DB->sanitize($rel_field_tpl) . "'");
if (count($rs)) {
$rel_field_tpl_id = $rs[0]['id'];
} else {
die("ERROR: Tag \"" . $node->name . "\": masterpage '" . $FUNCS->cleanXSS($rel_field_tpl) . "' not found for custom field '" . $FUNCS->cleanXSS($rel_field['name']) . "'");
}
}
// get relation_field_id using template_id
$rs = $DB->select(K_TBL_FIELDS, array('*'), "template_id='" . $DB->sanitize($rel_field_tpl_id) . "'" . $FUNCS->gen_sql(implode(",", $arr_rel_types), 'k_type') . " AND name='" . $DB->sanitize($rel_field['name']) . "'");
if (count($rs)) {
$arr_rel_fields[$rel_field_tpl][$rel_count]['id'] = $rs[0]['id'];
} else {
die("ERROR: Tag \"" . $node->name . "\": custom field '" . $FUNCS->cleanXSS($rel_field['name']) . "' not defined in " . $FUNCS->cleanXSS($rel_field_tpl));
}
}
// ids of the field's values
if (count($rel_field['val'])) {
// .. check if values are already ids (e.g. 'id(373,372,371)')
$val1 = trim($rel_field['val'][0]);
$val2 = trim($rel_field['val'][count($rel_field['val']) - 1]);
if (stripos($val1, 'id(') === 0 && substr($val2, -1) == ')') {
$val1 = substr($val1, 3);
$arr_rel_fields[$rel_field_tpl][$rel_count]['val'][0] = $val1;
if (count($rel_field['val']) == 1) {
$val2 = $val1;
}
$val2 = substr($val2, 0, -1);
$arr_rel_fields[$rel_field_tpl][$rel_count]['val'][count($rel_field['val']) - 1] = $val2;
} else {
if ($rel_field_tpl != $masterpage) {
// reverse related - the related pages should belong to this very template
} else {
// get the related template
$obj_field = new Relation($rel_field['related_field'], new KError('dummy'), new KError('dummy'));
$related_template_name = trim($obj_field->masterpage);
unset($obj_field);
$rs = $DB->select(K_TBL_TEMPLATES, array('id'), "name='" . $DB->sanitize($related_template_name) . "'");
if (count($rs)) {
$rel_field_tpl_id = $rs[0]['id'];
} else {
die("ERROR: Tag \"" . $node->name . "\": masterpage '" . $FUNCS->cleanXSS($related_template_name) . "' not found for related field '" . $FUNCS->cleanXSS($rel_field['name']) . "'");
}
}
// convert related page_names to ids (if not 'ANY')
if (count($rel_field['val']) == 1 && trim($rel_field['val'][0]) == 'ANY') {
$arr_rel_fields[$rel_field_tpl][$rel_count]['skip_ids'] = 1;
$arr_rel_fields[$rel_field_tpl][$rel_count]['val'] = array();
} else {
$str_names = implode(",", $rel_field['val']);
$rs = $DB->select(K_TBL_PAGES, array('id'), "template_id='" . $DB->sanitize($rel_field_tpl_id) . "'" . $FUNCS->gen_sql($str_names, 'page_name'));
$arr_vals = array();
foreach ($rs as $rec) {
$arr_vals[] = $rec['id'];
}
$arr_rel_fields[$rel_field_tpl][$rel_count]['val'] = $arr_vals;
}
}
}
$rel_count++;
}
}
}
// resolve custom_fields used as order_by
foreach ($arr_custom_orderby as $k => $v) {
$cf_found = 0;
for ($i = 0; $i < count($rs_cf); $i++) {
$f = $rs_cf[$i];
if ($f['name'] == $k) {
// 'relation' field?
if (in_array($f['k_type'], $arr_rel_types)) {
unset($arr_orderby[$v]);
unset($arr_order[$v]);
continue 2;
}
$cf_found = 1;
if (!array_key_exists($f['name'], $arr_tables)) {
$arr_tables[$f['name']]['id'] = $f['id'];
$arr_tables[$f['name']]['tbl_name'] = $f['search_type'] == 'text' ? K_TBL_DATA_TEXT : K_TBL_DATA_NUMERIC;
$arr_tables[$f['name']]['alias'] = sprintf("t%d", $count++);
}
$arr_orderby[$v] = $arr_tables[$f['name']]['alias'] . "." . ($f['search_type'] == 'text' ? 'search_value' : 'value');
break;
}
}
if (!$cf_found) {
die("ERROR: Unknown orderby clause \"" . $FUNCS->cleanXSS($k) . "\"");
}
}
// generate sql to query custom fields
if (count($arr_rel_fields)) {
if (count($arr_rel_fields) > 1) {
$distinct = 1;
}
$rel_suffix = 1;
foreach ($arr_rel_fields as $rel_field_tpl => $rel_fields) {
// for each masterpage
foreach ($rel_fields as $rel_field) {
// for each relation field in the masterpage
if (count($rel_field['val']) > 1 || $rel_field['skip_ids'] && $rel_field['op'] == '=') {
$distinct = 1;
}
$rel_tbl = 'rel' . $rel_suffix;
$join_field = $rel_field_tpl == $masterpage ? 'pid' : 'cid';
$where_field = $rel_field_tpl == $masterpage ? 'cid' : 'pid';
if ($rel_field['is_aggregate']) {
$query_fields[] = 'count(p.id) as k_rel_count';
$group_by[] = 'p.id';
foreach ($arr_rel_fields[$rel_field_tpl] as $rfld) {
if ($rfld['id'] == $rel_field['id'] && !$rfld['is_aggregate']) {
continue 2;
// skip if table already joined
}
}
}
if ($rel_field['op'] == '!=') {
// Negation in N:M relation requires special consideration
$str_ids = $FUNCS->gen_sql(implode(",", $rel_field['val']), "{$where_field}", 1);
if ($str_ids || $rel_field['skip_ids']) {
$rs = $DB->select(K_TBL_RELATIONS, array("{$join_field} as id"), "fid='" . $DB->sanitize($rel_field['id']) . "'" . $str_ids, 1);
$arr_vals = array();
foreach ($rs as $rec) {
$arr_vals[] = $rec['id'];
}
if (count($arr_vals)) {
$sql .= " \r\n" . 'AND p.id NOT IN(' . implode(",", $arr_vals) . ')';
}
}
} else {
$str_ids = '';
if (!$rel_field['is_aggregate'] && !$rel_field['skip_ids']) {
$str_ids = $FUNCS->gen_sql(implode(",", $rel_field['val']), "{$rel_tbl}.{$where_field}", 1);
if (!$str_ids) {
$str_ids = " AND {$rel_tbl}.{$where_field}=-1";
}
}
$query_table .= "\r\n inner join " . K_TBL_RELATIONS . " {$rel_tbl} on {$rel_tbl}.{$join_field} = p.id";
$sql .= " \r\n" . $str_ids . " AND {$rel_tbl}.fid=" . $DB->sanitize($rel_field['id']);
}
$rel_suffix++;
}
}
}
if (count($arr_tables)) {
$where = ' AND ' . "\r\n" . '(' . "\r\n";
$sep = '';
foreach ($arr_tables as $k => $tbl) {
$join .= ' inner join ' . $tbl['tbl_name'] . ' ' . $tbl['alias'] . ' on ' . $tbl['alias'] . '.page_id = p.id' . "\r\n";
$where .= $sep . $tbl['alias'] . '.field_id=' . $DB->sanitize($tbl['id']);
$sep = ' AND' . "\r\n";
}
$where .= "\r\n" . ')' . "\r\n";
}
if (count($arr_custom_fields)) {
// skip processed custom_fields
$arr_tmp = $arr_custom_fields;
$arr_custom_fields = array();
foreach ($arr_tmp as $cf) {
if ($cf['processed']) {
continue;
}
$arr_custom_fields[] = $cf;
}
if (count($arr_custom_fields)) {
$where .= ' AND ' . "\r\n" . '(' . "\r\n";
$sep = '';
foreach ($arr_custom_fields as $cf) {
$where .= $sep;
if (count($cf['val']) > 1) {
$where .= '(';
}
$sep2 = '';
foreach ($cf['val'] as $val) {
$where .= $sep2 . $cf['table_name'] . '.' . $cf['field_name'] . ' ' . $cf['op'] . ' \'' . $val . '\'';
$sep2 = ' OR ';
}
if (count($cf['val']) > 1) {
$where .= ')';
}
$sep = ' AND' . "\r\n";
}
$where .= "\r\n" . ')' . "\r\n";
}
}
// append to original sql
$query_table .= "\r\n" . $join;
$sql .= $where;
}
}
// orderby
$sep = '';
for ($i = 0; $i < count($arr_orderby); $i++) {
$orderby = $arr_orderby[$i];
if (in_array($orderby, $arr_canonical_orderby)) {
$orderby = 'p.' . $orderby;
}
if ($orderby == 'random') {
if ($paginate) {
if (!session_id()) {
@session_start();
}
if (empty($_SESSION['k_seed'])) {
$_SESSION['k_seed'] = rand();
}
$orderby = 'RAND(' . $_SESSION['k_seed'] . ')';
} else {
$orderby = 'RAND()';
}
$PAGE->no_cache = 1;
}
$order_sql .= $sep . $DB->sanitize($orderby);
$order = $arr_order[$i];
$order_sql .= " " . $DB->sanitize($order);
$sep = ", ";
}
} elseif ($mode == 1) {
// called from 'Search' tag
$keywords = trim($keywords);
if (!strlen($keywords)) {
return;
}
// add the '+' for boolean search
$sep = "";
$keywords = explode(' ', $keywords);
foreach ($keywords as $kw) {
$kw = trim($kw);
if (!$kw) {
continue;
}
$bool_keywords .= $sep . "+" . $kw;
$sep = " ";
}
$query_table = K_TBL_FULLTEXT . " cf\r\n inner join " . K_TBL_PAGES . " cp on cp.id=cf.page_id\r\n inner join " . K_TBL_TEMPLATES . " ct on ct.id=cp.template_id";
$score_field = "((MATCH(cf.content) AGAINST ('" . $DB->sanitize($bool_keywords) . "') * 1) + (MATCH(cf.title) AGAINST ('" . $DB->sanitize($bool_keywords) . "') * 1.25)) as score";
$query_fields = array('cp.template_id', 'cp.id', 'cf.title', 'cf.content', $score_field);
$count_query_field = 'cf.page_id';
$sql = " ((MATCH(cf.content) AGAINST ('" . $DB->sanitize($bool_keywords) . "' IN BOOLEAN MODE) * 1) + (MATCH(cf.title) AGAINST ('" . $DB->sanitize($bool_keywords) . "' IN BOOLEAN MODE) * 1.25))";
// search within which template(s)?
if ($masterpage) {
// masterpage="NOT blog.php, testimonial.php"
$sql .= $FUNCS->gen_sql($masterpage, 'ct.name');
}
if ($hide_future_entries) {
$sql .= " AND cp.publish_date < '" . $FUNCS->get_current_desktop_time() . "'";
}
$sql .= " AND NOT cp.publish_date = '0000-00-00 00:00:00'";
$sql .= " AND cp.access_level<='" . $AUTH->user->access_level . "'";
$sql .= " AND ct.executable=1";
$order_sql = 'score DESC';
} elseif ($mode == 2) {
// called from 'Comments' tag
$query_table = K_TBL_COMMENTS . " cc";
$query_table .= "\n inner join " . K_TBL_TEMPLATES . " ct on ct.id=cc.tpl_id";
$query_table .= "\n inner join " . K_TBL_PAGES . " cp on cp.id=cc.page_id";
$query_fields = array('cp.page_title, cp.page_name, ct.name tpl_name, ct.clonable, cc.*');
$count_query_field = 'cc.id';
$sql = "cc.approved=1";
// comments of which template(s)?
if ($masterpage) {
// masterpage="NOT blog.php, testimonial.php"
$sql .= $FUNCS->gen_sql($masterpage, 'ct.name');
}
// comments of which page(s)?
if ($page_id) {
$sql .= $FUNCS->gen_sql($page_id, 'cc.page_id', 1);
}
if ($page_name) {
//$query_table .= "\n inner join couch_pages cp on cp.id=cc.page_id";
$sql .= $FUNCS->gen_sql($page_name, 'cp.page_name');
}
$sql .= " AND NOT cp.publish_date = '0000-00-00 00:00:00'";
// Order?
$order = strtolower(trim($order));
if ($order != 'desc' && $order != 'asc') {
$order = 'desc';
}
$order_sql = "cc.date " . $order;
// if being invoked on a page which has been loaded via comment_id,
// need to calculate the page (of paginated comments) where this comment will appear
if ($PAGE->comment_id && $page_id == $PAGE->id) {
$parent_id = $PAGE->id;
$tmp_sql = "page_id='" . $DB->sanitize($parent_id) . "' and ";
$tmp_sql .= "approved=1 and ";
if ($order == 'desc') {
$tmp_sql .= "date>='" . $DB->sanitize($PAGE->comment_date) . "' ";
} else {
$tmp_sql .= "date<='" . $DB->sanitize($PAGE->comment_date) . "' ";
}
$rs = $DB->select(K_TBL_COMMENTS, array('count(id) as cnt'), $tmp_sql);
$total_rows = $rs[0]['cnt'];
$total_rows -= $offset;
if ($total_rows > $limit) {
$PAGE->comment_page = ceil($total_rows / $limit);
}
// no need to process further. Redirection is imminent.
return;
}
}
// end mode==2 (comments)
// limit
$limit_sql = sprintf("%d, %d", ($pgn_pno - 1) * $limit + $offset, $limit);
// We have the sql query here..
if ($mode == 5) {
// raw query .. called from 'query' tag
$raw_sql = rtrim($raw_sql, ' ;');
$pattern = '/^(\\s*\\(?\\s*)(select\\s)/i';
$replacement = '${1}${2}SQL_CALC_FOUND_ROWS ';
// is SELECT?
if (!preg_match($pattern, $raw_sql)) {
ob_end_clean();
die("ERROR: Tag \"query\" can process only SELECT statements");
}
$raw_sql = preg_replace($pattern, $replacement, $raw_sql);
// retained for backward compatibility where
// ORDER BY clause containing calculated fields could mess up the count(*) query.
// For such cases the 'orderby' param was used to provide the raw ORDER BY statement.
// Now, the raw query itself can contain any orderby clause.
$orderby = trim($orderby);
if ($orderby) {
if (!preg_match("/^order\\s/is", $orderby)) {
$raw_sql .= ' ORDER BY';
}
$raw_sql .= ' ' . $orderby;
}
$raw_sql = rtrim($raw_sql, ' ;');
$raw_sql .= ' LIMIT ' . $limit_sql;
$rs = $DB->raw_select($raw_sql);
// get count for pagination
$rs2 = $DB->raw_select('SELECT FOUND_ROWS() as cnt;');
$total_rows = $rs2[0]['cnt'];
// return if only count asked for
if ($count_only) {
return $total_rows;
}
if ($fetch_pages && count($rs)) {
// do some sanity checks to make sure the query can fetch pages like cms:pages does
if (!(array_key_exists('id', $rs[0]) && array_key_exists('template_id', $rs[0]))) {
ob_end_clean();
die("ERROR: 'fetch_pages' param of tag \"query\" requires 'id' and 'template_id' fields in the SQL statement");
}
}
} else {
// HOOK: alter_page_tag_query
// called routine should check for $node->name to know which tag is being executed.
// rs_tpl (masterpage info) will be filled only for mode 0 (i.e. pages/related_pages/reverse_related_pages tags)
$FUNCS->dispatch_event('alter_page_tag_query', array(&$distinct, &$count_query_field, &$count_query_field_as, &$query_fields, &$query_table, &$sql, &$group_by, &$having, &$order_sql, &$limit_sql, &$mode, $params, $node, $rec_tpl));
$orig_sql = $sql;
$group_by = array_filter(array_map("trim", $group_by));
if (count($group_by)) {
$group_by = 'GROUP BY ' . implode(",", $group_by);
$sql .= "\r\n" . $group_by;
$distinct = 0;
} else {
$group_by = '';
}
$having = array_filter(array_map("trim", $having));
if (count($having)) {
$having = 'HAVING ' . implode(" AND ", $having);
$sql .= "\r\n" . $having;
} else {
$having = '';
}
// first query for pagination
$rs = $DB->select($query_table, array('count(' . $count_query_field . ') as ' . $count_query_field_as), $sql, $distinct);
$total_rows = $rs[0][$count_query_field_as];
// Return if only count asked for
if ($count_only) {
return $total_rows;
}
// actual query
if ($return_sql) {
$sep = $fields = $html = '';
foreach ($query_fields as $field) {
$fields .= $sep . $field;
$sep = ', ';
}
if ($return_sql == 2) {
// return query parts
$CTX->set('k_sql_distinct', $distinct);
// distinct
$CTX->set('k_sql_select', $fields);
// fields
$CTX->set('k_sql_from', $query_table);
// tables
$CTX->set('k_sql_where', $orig_sql);
// where
$CTX->set('k_sql_group_by', $group_by);
// group_by
$CTX->set('k_sql_having', $having);
// having
$CTX->set('k_sql_order', $order_sql);
// order
$CTX->set('k_sql_limit', $limit_sql);
// limit
foreach ($node->children as $child) {
$html .= $child->get_HTML();
}
} else {
// return complete query
$html = $distinct ? 'SELECT DISTINCT ' : 'SELECT ';
$html .= $fields . ' FROM ' . $query_table . ' WHERE ' . $sql . ' ORDER BY ' . $order_sql . ' LIMIT ' . $limit_sql;
}
return $html;
} else {
$sql .= ' ORDER BY ' . $order_sql;
$sql .= ' LIMIT ' . $limit_sql;
$rs = $DB->select($query_table, $query_fields, $sql, $distinct);
}
}
// If only ids asked for, return with them
if ($ids_only) {
$sep = '';
$html = '';
foreach ($rs as $rec) {
$html .= $sep . $rec['id'];
$sep = ',';
}
return $html;
}
// if called from calendar tag, return results.
if ($mode == 3) {
return $rs;
}
$total_rows -= $offset;
$total_pages = ceil($total_rows / $limit);
$count = count($rs);
$page_link = strlen($base_link) ? $base_link : K_SITE_URL . $PAGE->link;
// append querystring params, if any
$sep = '';
// HOOK: skip_qs_params_in_paginator
$skip_qs = array();
$FUNCS->dispatch_event('skip_qs_params_in_paginator', array(&$skip_qs));
foreach ($_GET as $qk => $qv) {
if ($qk == 'p' || $qk == 'f' || $qk == 'd' || $qk == 'fname' || $qk == 'pname' || $qk == '_nr_') {
continue;
}
if ($qk == $qs_param) {
continue;
}
//'pg' or 'comments_pg'
if (in_array($qk, $skip_qs)) {
continue;
}
if (is_array($qv)) {
//checkboxes
foreach ($qv as $qvv) {
$qs .= $sep . $qk . '[]=' . urlencode($qvv);
$sep = '&';
}
} else {
$qs .= $sep . $qk . '=' . urlencode($qv);
$sep = '&';
}
}
if ($qs) {
$page_link .= strpos($page_link, '?') === false ? '?' : '&';
$page_link .= $qs;
}
if ($total_rows > $limit) {
$paginated = 1;
$sep = strpos($page_link, '?') === false ? '?' : '&';
// 'Prev' link
if ($pgn_pno > 1) {
if ($pgn_pno == 2) {
$pgn_prev_link = $page_link;
} else {
$pgn_prev_link = sprintf("%s%s%s=%d", $page_link, $sep, $qs_param, $pgn_pno - 1);
}
}
// 'Next' link
if ($pgn_pno < $total_pages) {
$pgn_next_link = sprintf("%s%s%s=%d", $page_link, $sep, $qs_param, $pgn_pno + 1);
}
// Current paginated link
$pgn_cur_link = $pgn_pno == 1 ? $page_link : sprintf("%s%s%s=%d", $page_link, $sep, $qs_param, $pgn_pno);
}
if ($count) {
for ($x = 0; $x < $count; $x++) {
$rec = $rs[$x];
if ($mode == 2) {
//Comments
$CTX->set('k_comment_id', $rec['id']);
$CTX->set('k_comment_page_id', $rec['page_id']);
$CTX->set('k_comment_page_title', $rec['page_title']);
$CTX->set('k_comment_page_name', $rec['page_name']);
$CTX->set('k_comment_template_name', $rec['tpl_name']);
$CTX->set('k_comment_author_id', $rec['user_id']);
// 0 for unregistered
$CTX->set('k_comment_author', $rec['name']);
$CTX->set('k_comment_author_email', $rec['email']);
$CTX->set('k_comment_author_website', $rec['link']);
$CTX->set('k_comment_date', $rec['date']);
$CTX->set('k_comment', $rec['data']);
$CTX->set('k_comment_anchor', "comment-" . $rec['id']);
//anchor name
$parent_link = K_PRETTY_URLS ? $FUNCS->get_pretty_template_link($rec['tpl_name']) : $rec['tpl_name'];
$CTX->set('k_comment_link', K_SITE_URL . $parent_link . "?comment=" . $rec['id']);
} else {
// pages, search, query
if ($mode == 5) {
// raw query
$CTX->reset();
$rec_vars = array();
foreach ($rec as $rec_k => $rec_v) {
$rec_vars[$rec_k] = $rec_v;
}
$CTX->set_all($rec_vars);
}
if ($mode != 5 || $mode == 5 && $fetch_pages) {
//Pages & Search
$pg = new KWebpage($rec['template_id'], $rec['id'], 0, 0, $skip_custom_fields);
if ($pg->error) {
ob_end_clean();
die('ERROR: ' . $pg->err_msg);
}
$pg->set_context();
$pg->destroy();
// release the memory held by fields
if ($aggregate_by) {
$CTX->set('k_rel_count', $rec['k_rel_count']);
}
}
if ($mode == 1) {
// Search
if ($pg->tpl_is_clonable) {
$hilited = $FUNCS->hilite_search_terms($keywords, $rec['title'], 1);
} else {
$hilited = $pg->tpl_title ? $pg->tpl_title : $pg->tpl_name;
}
$CTX->set('k_search_title', $hilited);
$CTX->set('k_search_content', $rec['content']);
//entire content searched
$hilited = $FUNCS->hilite_search_terms($keywords, $rec['content']);
$CTX->set('k_search_excerpt', $hilited);
//hilighted excerpt of searched content
}
}
// Pagination related variables
$first_record_on_page = $limit * ($pgn_pno - 1) + $startcount;
$total_records_on_page = $count < $limit ? $count : $limit;
$CTX->set('k_count', $x + $startcount);
$CTX->set('k_total_records', $total_rows);
$CTX->set('k_total_records_on_page', $total_records_on_page);
$CTX->set('k_current_record', $first_record_on_page + $x);
$CTX->set('k_absolute_count', $first_record_on_page + $x);
//same as current record
$CTX->set('k_record_from', $first_record_on_page);
$CTX->set('k_record_to', $first_record_on_page + $total_records_on_page - 1);
$CTX->set('k_total_pages', $total_pages);
$CTX->set('k_current_page', $pgn_pno);
$CTX->set('k_paginate_limit', $limit);
if ($x == 0) {
$CTX->set('k_paginated_top', 1);
} else {
$CTX->set('k_paginated_top', 0);
}
if ($x == $count - 1) {
$CTX->set('k_paginated_bottom', 1);
} else {
$CTX->set('k_paginated_bottom', 0);
}
if ($paginate && $paginated) {
$CTX->set('k_paginator_required', 1);
$CTX->set('k_page_being_paginated', $page_link);
$CTX->set('k_qs_param', $qs_param);
$CTX->set('k_paginate_link_next', $pgn_next_link);
$CTX->set('k_paginate_link_prev', $pgn_prev_link);
$CTX->set('k_paginate_link_cur', $pgn_cur_link);
} else {
$CTX->set('k_paginator_required', 0);
$CTX->set('k_paginate_link_next', '');
$CTX->set('k_paginate_link_prev', '');
$CTX->set('k_paginate_link_cur', '');
}
// HOOK: alter_page_tag_context
$FUNCS->dispatch_event('alter_page_tag_context', array($rec, $mode, $params, $node, $rec_tpl));
// call the children
foreach ($node->children as $child) {
$html .= $child->get_HTML();
}
}
} else {
// find and execute 'no_results' tag
$html = '';
foreach ($node->children as $child) {
if ($child->type == K_NODE_TYPE_CODE && $child->name == 'no_results') {
// call the children of no_results
foreach ($child->children as $grand_child) {
$html .= $grand_child->get_HTML();
}
break;
}
}
}
return $html;
}
function db_delete($params, $node)
{
global $FUNCS, $DB, $CTX;
if (count($node->children)) {
die("ERROR: Tag \"" . $node->name . "\" is a self closing tag");
}
// handle params
extract($FUNCS->get_named_vars(array('masterpage' => '', 'page_id' => '', 'invalidate_cache' => '0'), $params));
$masterpage = trim($masterpage);
if (!$masterpage) {
die("ERROR: Tag \"" . $node->name . "\": 'masterpage' attribute missing");
}
$page_id = isset($page_id) && $FUNCS->is_non_zero_natural($page_id) ? (int) $page_id : null;
if (!$page_id) {
die("ERROR: Tag \"" . $node->name . "\": 'page_id' required");
}
// get down to business
$rs = $DB->select(K_TBL_TEMPLATES, array('id', 'clonable'), "name='" . $DB->sanitize($masterpage) . "'");
if (!count($rs)) {
die("ERROR: Tag \"" . $node->name . "\" - masterpage does not exist");
}
if (!$rs[0]['clonable']) {
die("ERROR: Tag \"" . $node->name . "\" - cannot delete non-clonable template");
}
$pg = new KWebpage($rs[0]['id'], $page_id);
if ($pg->error) {
die("ERROR: Tag \"" . $node->name . "\" - " . $pg->err_msg);
}
// delete..
$pg->delete();
// if we are here, delete was successful (script would have died otherwise)
$pg->destroy();
unset($pg);
if ($invalidate_cache) {
$FUNCS->invalidate_cache();
}
}
function create_cloned_page($tpl_id, $fid, $cid, $rid, $page_title, $img_url)
{
global $FUNCS;
// create a single cloned page
$pg = new KWebpage($tpl_id, -1);
if ($pg->error) {
return $FUNCS->raise_error($pg->err_msg);
}
// fill fields
$f =& $pg->_fields['k_page_title'];
// title
$f->store_posted_changes($page_title);
unset($f);
$f =& $pg->_fields['k_page_folder_id'];
// folder
$f->store_posted_changes($fid);
unset($f);
$f =& $pg->_fields['k_publish_date'];
// publish date
$f->store_posted_changes($FUNCS->get_current_desktop_time());
unset($f);
// find the image field (set 'required' off for all other fields as we go)
// also find the relation field if specified
if ($cid && $rid) {
$find_related = 1;
}
for ($x = 0; $x < count($pg->fields); $x++) {
$f =& $pg->fields[$x];
if (!$f->system) {
if ($f->k_type == 'image' && $f->name == 'gg_image') {
$f->store_posted_changes($img_url);
}
// related?
if ($find_related) {
if ($f->id == $rid && $f->k_type == 'relation') {
$f->store_posted_changes($cid);
$find_related = 0;
}
}
}
$f->required = 0;
unset($f);
}
// save
$errors = $pg->save();
if ($errors) {
$sep = '';
if (count($errors)) {
$str_err = '';
for ($x = 0; $x < count($pg->fields); $x++) {
$f =& $pg->fields[$x];
if ($f->err_msg) {
$str_err .= $sep . '<b>' . $f->name . ':</b> ' . $f->err_msg;
$sep = '<br/>';
}
}
return $FUNCS->raise_error($str_err);
}
}
$page_id = $pg->id;
$pg->destroy();
unset($pg);
return $page_id;
}
