/**
* Returns SQL code to use when fetching entries that are contained within multiple categories
*
* @access public
* @param string A listing of category ids to check, separated by ";"
* @param boolean Toggle whether to include or exclude entries of this category
* @return string Returns the SQL code for selecting entries of the calculated categories
*/
function serendipity_getMultiCategoriesSQL($cats, $invert = false)
{
global $serendipity;
$mcategories = explode(';', $cats);
$cat_sql_array = array();
foreach ($mcategories as $categoryid) {
$categoryid = (int) $categoryid;
if ($categoryid != 0) {
$cat_sql_array[] = " (c.category_left " . ($invert ? " NOT " : "") . " BETWEEN " . implode(' AND ', serendipity_fetchCategoryRange($categoryid)) . ')';
}
}
if (count($cat_sql_array) < 1) {
return '';
}
return '(' . implode($invert ? ' AND ' : ' OR ', $cat_sql_array) . ')';
}
function generate_content(&$title)
{
global $serendipity;
$number = $this->get_config('number');
$dateformat = $this->get_config('dateformat');
$category = $this->get_config('category', 'none');
$show_where = $this->get_config('show_where', 'both');
if ($show_where == 'extended' && (!isset($serendipity['GET']['id']) || !is_numeric($serendipity['GET']['id']))) {
return false;
} else {
if ($show_where == 'overview' && isset($serendipity['GET']['id']) && is_numeric($serendipity['GET']['id'])) {
return false;
}
}
if ($category == '_cur') {
$category = $serendipity['GET']['category'];
if (empty($category) && !empty($serendipity['GET']['id'])) {
$entry = serendipity_fetchEntry('id', $serendipity['GET']['id']);
$category = $entry['categories'][0]['categoryid'];
}
}
$title = $this->get_config('title', $this->title);
$number_from_sw = $this->get_config('number_from');
$randomize = $this->get_config('randomize') == "yes" ? true : false;
$sql_condition = array();
$sql_condition['joins'] = '';
$sql_condition['and'] = '';
if ($category != 'none' && !empty($category)) {
$sql_categories = array();
if (is_numeric($category)) {
$sql_categories[] = $category;
} else {
$sql_categories = explode('^', $category);
}
$category_parts = array();
foreach ($sql_categories as $sql_category) {
$category_parts[] = "\n" . implode(' AND ', serendipity_fetchCategoryRange($sql_category));
}
$sql_condition['and'] .= ' AND (c.category_left BETWEEN ' . implode(' OR c.category_left BETWEEN ', $category_parts) . ')';
}
if (!$number || !is_numeric($number) || $number < 1) {
$number = 10;
}
$sql_number = serendipity_db_limit_sql($number);
$db = $serendipity['dbType'];
switch ($number_from_sw) {
case 'skip':
$sql_number = serendipity_db_limit_sql(serendipity_db_limit($serendipity['fetchLimit'], $number));
break;
}
if (!$dateformat || strlen($dateformat) < 1) {
$dateformat = '%A, %B %e %Y';
}
if ($randomize) {
if ($db == 'mysql' || $db == 'mysqli') {
$sql_order = "ORDER BY RAND()";
} else {
// SQLite and PostgreSQL support this, hooray.
$sql_order = "ORDER BY RANDOM()";
}
} else {
$sql_order = "ORDER BY timestamp DESC ";
}
$sql_condition['and'] .= "AND timestamp <= " . time();
serendipity_ACL_SQL($sql_condition, $category == 'none');
if (!stristr($sql_condition['joins'], $serendipity['dbPrefix'] . 'category')) {
$sql_condition['joins'] = ' LEFT OUTER JOIN ' . $serendipity['dbPrefix'] . 'category AS c ON ec.categoryid = c.categoryid ' . $sql_condition['joins'];
}
if (!stristr($sql_condition['joins'], $serendipity['dbPrefix'] . 'entrycat')) {
$sql_condition['joins'] = ' LEFT OUTER JOIN ' . $serendipity['dbPrefix'] . 'entrycat AS ec ON id = ec.entryid ' . $sql_condition['joins'];
}
$entries_query = "SELECT DISTINCT id,\n title,\n timestamp,\n epm.value AS multilingual_title\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_condition['joins']}\n\n LEFT OUTER JOIN {$serendipity['dbPrefix']}entryproperties AS epm\n ON (epm.entryid = e.id AND epm.property = 'multilingual_title_" . $serendipity['lang'] . "')\n\n WHERE isdraft = 'false' {$sql_condition['and']}\n {$sql_order}\n {$sql_number}";
$entries = serendipity_db_query($entries_query);
if (is_string($entries)) {
echo $entries . "<br />\n";
echo $entries_query . "<br />\n";
}
if (isset($entries) && is_array($entries)) {
echo '<dl>' . "\n";
foreach ($entries as $k => $entry) {
if (!empty($entry['multilingual_title'])) {
$entry['title'] = $entry['multilingual_title'];
}
$entryLink = serendipity_archiveURL($entry['id'], $entry['title'], 'serendipityHTTPPath', true, array('timestamp' => $entry['timestamp']));
if (empty($entry['title'])) {
$entry['title'] = '#' . $entry['id'];
}
echo '<dt class="serendipity_recententries_entrylink"><a href="' . $entryLink . '" title="' . serendipity_specialchars($entry['title']) . '">' . serendipity_specialchars($entry['title']) . '</a></dt>' . "\n" . '<dd class="serendipity_recententries_entrydate serendipitySideBarDate">' . serendipity_specialchars(serendipity_strftime($dateformat, $entry['timestamp'])) . '</dd>' . "\n";
}
echo '</dl>' . "\n\n";
}
}
} else {
serendipity_updateCategory($serendipity['GET']['cid'], $name, $desc, $authorid, $icon, $parentid, $serendipity['POST']['cat']['sort_order'], $serendipity['POST']['cat']['hide_sub'], $admin_category);
serendipity_ACLGrant($serendipity['GET']['cid'], 'category', 'read', $serendipity['POST']['cat']['read_authors']);
serendipity_ACLGrant($serendipity['GET']['cid'], 'category', 'write', $serendipity['POST']['cat']['write_authors']);
}
}
}
serendipity_rebuildCategoryTree();
$serendipity['GET']['adminAction'] = 'view';
}
/* Delete a category */
if ($serendipity['GET']['adminAction'] == 'doDelete' && serendipity_checkFormToken()) {
$data['doDelete'] = true;
if ($serendipity['GET']['cid'] != 0) {
$remaining_cat = (int) $serendipity['POST']['cat']['remaining_catid'];
$category_ranges = serendipity_fetchCategoryRange((int) $serendipity['GET']['cid']);
$category_range = implode(' AND ', $category_ranges);
if ($serendipity['dbType'] == 'postgres' || $serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'sqlite3oo' || $serendipity['dbType'] == 'pdo-sqlite') {
$query = "UPDATE {$serendipity['dbPrefix']}entrycat\n SET categoryid={$remaining_cat} WHERE entryid IN\n (\n SELECT DISTINCT(e.id) FROM {$serendipity['dbPrefix']}entries e,\n {$serendipity['dbPrefix']}category c,\n {$serendipity['dbPrefix']}entrycat ec\n WHERE e.id=ec.entryid AND c.categoryid=ec.categoryid\n AND c.category_left BETWEEN {$category_range} {$admin_category}\n )";
} else {
$query = "UPDATE {$serendipity['dbPrefix']}entries e,\n {$serendipity['dbPrefix']}entrycat ec,\n {$serendipity['dbPrefix']}category c\n SET ec.categoryid={$remaining_cat}\n WHERE e.id = ec.entryid\n AND c.categoryid = ec.categoryid\n AND c.category_left BETWEEN {$category_range}\n {$admin_category}";
}
serendipity_db_query($query);
if (serendipity_deleteCategory($category_range, $admin_category)) {
foreach ($category_ranges as $cid) {
if (serendipity_ACLCheck($serendipity['authorid'], $cid, 'category', 'write')) {
serendipity_ACLGrant($cid, 'category', 'read', array());
serendipity_ACLGrant($cid, 'category', 'write', array());
}
}
$data['deleteSuccess'] = true;
function generate_content(&$title)
{
global $serendipity;
$number = $this->get_config('number');
$category = $this->get_config('category', 'none');
$title = $this->get_config('title', $this->title);
$number_from_sw = $this->get_config('number_from');
if ($category == '_cur') {
$category = $serendipity['GET']['category'];
}
$sql_join = '';
$sql_where = '';
if ($category != 'none' && is_numeric($category)) {
$sql_join = 'LEFT OUTER JOIN ' . $serendipity['dbPrefix'] . 'entrycat AS ec ON e.id = ec.entryid
LEFT OUTER JOIN ' . $serendipity['dbPrefix'] . 'category AS c ON ec.categoryid = c.categoryid';
$sql_where = ' AND (c.category_left BETWEEN ' . implode(' AND ', serendipity_fetchCategoryRange($category)) . ')';
}
if (!$number || !is_numeric($number) || $number < 1) {
$number = 10;
}
$sql_number = $number;
switch ($number_from_sw) {
case 'skip':
$sql_number = serendipity_db_limit_sql(serendipity_db_limit($serendipity['fetchLimit'], $sql_number));
break;
default:
$sql_number = serendipity_db_limit_sql(serendipity_db_limit(0, $sql_number));
break;
}
$sortby = $this->get_config('sortby', 'comments');
switch ($sortby) {
case 'comments':
$entries_query = "SELECT e.id,\n e.title,\n e.comments AS points,\n e.timestamp\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_join}\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n {$sql_where}\n ORDER BY e.comments DESC\n {$sql_number}";
break;
case 'commentors':
$entries_query = "SELECT c.author, count(c.id) AS points\n FROM {$serendipity['dbPrefix']}entries AS e\n JOIN {$serendipity['dbPrefix']}comments AS c\n ON c.entry_id = e.id\n {$sql_join}\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n AND c.status = 'approved'\n {$sql_where}\n GROUP BY c.author\n ORDER BY points DESC\n {$sql_number}";
break;
case 'karma':
$entries_query = "SELECT e.id,\n e.title,\n e.comments,\n e.timestamp,\n k.points AS points\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_join}\n LEFT OUTER JOIN {$serendipity['dbPrefix']}karma AS k\n ON k.entryid = e.id\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n {$sql_where}\n GROUP BY e.id, e.title, e.comments, e.timestamp, k.visits\n ORDER BY k.points DESC\n {$sql_number}";
break;
case 'visits':
$entries_query = "SELECT e.id,\n e.title,\n e.comments,\n e.timestamp,\n k.visits AS points\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_join}\n LEFT OUTER JOIN {$serendipity['dbPrefix']}karma AS k\n ON k.entryid = e.id\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n {$sql_where}\n GROUP BY e.id, e.title, e.comments, e.timestamp, k.visits\n ORDER BY k.visits DESC\n {$sql_number}";
break;
case 'lowvisits':
$entries_query = "SELECT e.id,\n e.title,\n e.comments,\n e.timestamp,\n k.visits AS points\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_join}\n LEFT OUTER JOIN {$serendipity['dbPrefix']}karma AS k\n ON k.entryid = e.id\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n {$sql_where}\n GROUP BY e.id, e.title, e.comments, e.timestamp, k.visits \n ORDER BY k.visits ASC\n {$sql_number}";
break;
case 'exits':
$entries_query = "SELECT e.id,\n e.title,\n e.comments,\n e.timestamp,\n SUM(ex.count) AS points\n FROM {$serendipity['dbPrefix']}entries AS e\n {$sql_join}\n LEFT OUTER JOIN {$serendipity['dbPrefix']}exits AS ex\n ON ex.entry_id = e.id\n WHERE e.isdraft = 'false' AND e.timestamp <= " . time() . "\n {$sql_where}\n GROUP BY ex.entry_id\n ORDER BY points DESC\n {$sql_number}";
break;
}
$entries = serendipity_db_query($entries_query);
$hidden = explode(',', trim($this->get_config('commentors_hide')));
echo '<ul class="plainList">';
if (isset($entries) && is_array($entries)) {
foreach ($entries as $k => $entry) {
if ($sortby == 'commentors') {
if (in_array($entry['author'], $hidden)) {
continue;
}
$entryLink = $serendipity['serendipityHTTPPath'] . ($serendipity['rewrite'] == 'none' ? $serendipity['indexFile'] . '?/' : '') . PATH_COMMENTS . '/' . urlencode($entry['author']);
echo '<li><a href="' . $entryLink . '" title="' . (function_exists('serendipity_specialchars') ? serendipity_specialchars($entry['author']) : htmlspecialchars($entry['author'], ENT_COMPAT, LANG_CHARSET)) . '">' . (function_exists('serendipity_specialchars') ? serendipity_specialchars($entry['author']) : htmlspecialchars($entry['author'], ENT_COMPAT, LANG_CHARSET)) . '</a>';
echo ' <span class="serendipitySideBarDate">(' . (!empty($entry['points']) ? function_exists('serendipity_specialchars') ? serendipity_specialchars($entry['points']) : htmlspecialchars($entry['points'], ENT_COMPAT, LANG_CHARSET) : 0) . ')</span></li>';
} else {
$entryLink = serendipity_archiveURL($entry['id'], $entry['title'], 'serendipityHTTPPath', true, array('timestamp' => $entry['timestamp']));
echo '<li><a href="' . $entryLink . '" title="' . (function_exists('serendipity_specialchars') ? serendipity_specialchars($entry['title']) : htmlspecialchars($entry['title'], ENT_COMPAT, LANG_CHARSET)) . '">' . $entry['title'] . '</a>';
echo ' <span class="serendipitySideBarDate">(' . (!empty($entry['points']) ? function_exists('serendipity_specialchars') ? serendipity_specialchars($entry['points']) : htmlspecialchars($entry['points'], ENT_COMPAT, LANG_CHARSET) : 0) . ')</span></li>';
}
}
}
echo '</ul>';
}
