public function _____getResultClassReadingAct($classId = 0, $limit = 10)
{
$book = new Book();
$book_table = $book->getSource();
$user = new User();
$user_table = $user->getSource();
$page = new Page();
$page_table = $page->getSource();
$note = new Note();
$note_table = $note->getSource();
$note_comment = new NoteComment();
$note_comment_table = $note_comment->getSource();
$note_zan_log = new NoteZanLog();
$note_zan_log_table = $note_zan_log->getSource();
$task = new Task();
$task_table = $task->getSource();
$sql_count_columns = 'count(*) as counts';
$sql_columns = 'tt.id, tt._type, tt.book_id, tt.book_name, tt.c, tt.create_at,
user.id AS user_id,
user.real_name AS user_real_name,
user.image AS user_image,
user.classes_id AS user_classes_id,
count(DISTINCT comm.id) AS comm_id_count,
count(DISTINCT zan.id) AS zan_id_count,
IF (zan.uid="' . $this->thisController->user->id . '",1,0) AS userZan,
GROUP_CONCAT(DISTINCT zan.uid) as zan_uid';
$sql_join = 'left join ' . $note_comment_table . ' as comm on comm.read_note_id=tt.id and tt._type="note" left join ' . $note_zan_log_table . ' as zan on zan.read_note_id=tt.id and tt._type="note"';
$sql_group = 'GROUP BY tt._type,tt.id';
$sql_common_union_all_join = 'left join ' . $book_table . ' as book on book.id=book_id';
$sql = "SELECT %column% FROM {$user_table} AS user\nLEFT JOIN (\n\t(SELECT page.id,page.uid,book_id,book.name as book_name,is_finished,last_page AS c,page.update_at as create_at,'page' AS _type FROM {$page_table} as page {$sql_common_union_all_join})\n\tUNION ALL\n\t(SELECT note.id,note.uid,book_id,book.name as book_name,0,content AS c,note.create_at,'note' AS _type FROM {$note_table} as note {$sql_common_union_all_join})\n\tUNION ALL\n\t(select task.id,t_uid as uid,group_concat(book_id) as book_id,GROUP_CONCAT(book.name SEPARATOR '~~~') as book_name,0,0,task.create_at,'task' AS _type from {$task_table} as task {$sql_common_union_all_join} group by create_at)\n) AS tt ON tt.uid = user.id\n%join%\nWHERE user.type = 1 and FIND_IN_SET(:classes_id, user.classes_id) and tt.c is not null %group_by% ORDER BY tt.create_at DESC %limit%";
try {
$sql_count = str_replace(['%column%', '%join%', '%group_by%', '%limit%'], [$sql_count_columns, '', '', ''], $sql);
if (DEBUG) {
$logger = Di::getDefault()->getLogger();
$logger->log($sql_count, \Phalcon\Logger::DEBUG);
}
$stmt = $this->di->getDefault()->get('db')->prepare($sql_count);
$stmt->bindParam(':classes_id', $classId, \PDO::PARAM_INT);
$stmt->execute();
$count_result = $stmt->fetch(\PDO::FETCH_ASSOC);
if ($count_result['counts'] < 0) {
return null;
}
$page = new \PageNew($count_result['counts'], $limit);
$page_limit = 'LIMIT' . $page->limit;
$sql_query = str_replace(['%column%', '%join%', '%group_by%', '%limit%'], [$sql_columns, $sql_join, $sql_group, $page_limit], $sql);
if (DEBUG) {
$logger = Di::getDefault()->getLogger();
$logger->log($sql_query, \Phalcon\Logger::DEBUG);
}
$stmt = $this->di->getDefault()->get('db')->prepare($sql_query);
$stmt->bindParam(':classes_id', $classId, \PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return ['result' => $result, 'page' => $page, 'page_info' => $page->getPageInfo()];
} catch (\PDOException $e) {
if (DEBUG) {
die($e->getMessage());
} else {
$this->di->get('logger_error')->log('PDOException: ' . $e->getMessage(), \Phalcon\Logger::ERROR);
$this->di->get('dispatcher')->forward(['module' => 'home', 'controller' => 'error', 'action' => 'show_sql_error']);
}
}
}
