public function cacheCategories($catID = 0)
{
// clear stats of affected categories
$sql = "UPDATE `tbl_categories` SET `stats` = NULL";
if ($catID > 0) {
$sql .= " WHERE `cid` = {$catID};";
}
$this->exec($sql);
$categories = new \DB\SQL\Mapper($this->db, $this->prefix . 'categories');
$sql = "SELECT C.cid, C.category, COUNT(DISTINCT S.sid) as counted, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.category SEPARATOR '||' ) as sub_categories, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.stats SEPARATOR '||' ) as sub_stats\r\n\t\t\tFROM `tbl_categories`C \r\n\t\t\t\tINNER JOIN (SELECT leveldown FROM `tbl_categories` WHERE `stats` = '' ORDER BY leveldown DESC LIMIT 0,1) c2 ON ( C.leveldown = c2.leveldown )\r\n\t\t\t\tLEFT JOIN `tbl_stories_categories`SC ON ( C.cid = SC.cid )\r\n\t\t\t\tLEFT JOIN `tbl_stories`S ON ( S.sid = SC.sid )\r\n\t\t\t\tLEFT JOIN `tbl_categories`C1 ON ( C.cid = C1.parent_cid )\r\n\t\t\tGROUP BY C.cid";
do {
$items = $this->exec($sql);
$change = FALSE;
foreach ($items as $item) {
if ($item['sub_categories'] == NULL) {
$sub = NULL;
} else {
$sub_categories = explode("||", $item['sub_categories']);
$sub_stats = explode("||", $item['sub_stats']);
$sub_stats = array_map("json_decode", $sub_stats);
foreach ($sub_categories as $key => $value) {
$item['counted'] += $sub_stats[$key]->count;
$sub[$value] = $sub_stats[$key]->count;
}
}
$stats = json_encode(["count" => (int) $item['counted'], "cid" => $item['cid'], "sub" => $sub]);
unset($sub);
$categories->load(array('cid=?', $item['cid']));
$categories->stats = $stats;
$categories->save();
$change = $change ?: $categories->changed();
}
} while ($change != FALSE);
}
public function saveShout($id, array $data)
{
$shout = new \DB\SQL\Mapper($this->db, $this->prefix . 'shoutbox');
$shout->load(array('id=?', $id));
$shout->copyfrom(["message" => $data['message']]);
$i = $shout->changed("message");
$shout->save();
return $i;
}
