/** * 根据学校id归类获取信息 * * @param int $school_id * * @return mixed * @author Hunter.<*****@*****.**> */ public function getStatisticsForSchoolAndGrade($school_id = 0) { $page = new Page(); $page_table = $page->getSource(); $classes = new Classes(); $classes_table = $classes->getSource(); $eva_task_log = new EvaTaskLog(); $eva_task_log_table = $eva_task_log->getSource(); $sql_query = "select c.grade_id,count(DISTINCT p.id) as countP, 'reading' as type from {$page_table} as p\nleft join (\nSELECT grade_id,GROUP_CONCAT(id) as id_concat FROM {$classes_table}\nwhere school_id=:school_id\ngroup by grade_id\n) c on FIND_IN_SET(p.classes_id,c.id_concat)\nwhere is_finished=0\ngroup by c.grade_id\n\nunion all\n\nselect c.grade_id,count(DISTINCT p.id) as countP, 'reading_finished' as type from {$page_table} as p\nleft join (\nSELECT grade_id,GROUP_CONCAT(id) as id_concat FROM {$classes_table}\nwhere school_id=:school_id\ngroup by grade_id\n) c on FIND_IN_SET(p.classes_id,c.id_concat)\nwhere is_finished=1\ngroup by c.grade_id\n\nunion all\n\nselect c.grade_id,count(DISTINCT p.id) as countP, 'eva_task' as type from {$eva_task_log_table} as p\nleft join (\nSELECT grade_id,GROUP_CONCAT(id) as id_concat FROM {$classes_table}\nwhere school_id=:school_id\ngroup by grade_id\n) c on FIND_IN_SET(p.classes_id,c.id_concat)\ngroup by c.grade_id"; try { if (DEBUG) { $logger = Di::getDefault()->getLogger(); $logger->log($sql_query, \Phalcon\Logger::DEBUG); } $stmt = $this->di->getDefault()->get('db')->prepare($sql_query); $stmt->bindParam(':school_id', $school_id, \PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(\PDO::FETCH_ASSOC); return $result; } 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']); } } }
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']); } } }