/**
* 根据学校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']);
}
}
}
/**
* 插入阅读页码
*
* @param $data
*
* @return array|int
* @author Hunter.<*****@*****.**>
* @throws \Exception
*/
public function insertPage($data = [])
{
$pageInfo = Page::findFirst(['book_id=:book_id: AND uid=:uid: AND read_task_id=:read_task_id: AND classes_id=:classes_id:', 'bind' => ['book_id' => $data['book_id'], 'uid' => $data['uid'], 'read_task_id' => $data['read_task_id'], 'classes_id' => $data['classes_id']]]);
if ($pageInfo) {
//如果已经完成了最后一页，则不需要再次添加
if ($pageInfo->getIsFinished() == 1) {
return 2;
}
//当前给的页码已经在page_content中，则不处理
if ($pageInfo->getPageContent()) {
if (in_array($data['last_page'], array_values($pageInfo->getPageContent()))) {
return 3;
}
}
}
//判断read_task_id是否有效
$taskInfo = $this->getCommon(['id=:read_task_id:', 'bind' => ['read_task_id' => $data['read_task_id']]], 'Task', true);
if (!$taskInfo or $taskInfo->getSUid() != $data['uid'] and $taskInfo->getClassesId() != $data['classes_id']) {
return 4;
}
$data['page_content'] = $data['last_page'];
//获取指定书的所有页码，如果给定的阅读页码为最后一页时，is_finished=1
//$bookInfo = $this->getBookInfo($data['book_id']);
$bookInfo = $this->getCommon($data['book_id'], 'Book', true);
if ($bookInfo->getCountPage() <= $data['last_page']) {
//到达尾页
$data['page_content'] = $data['last_page'] = $bookInfo->getCountPage();
$data['is_finished'] = 1;
$now_word = $bookInfo->getCountWord();
//当前字数就是这本书的总字数
} else {
//非尾页的其他情况
$data['is_finished'] = 0;
$now_word = ceil($data['last_page'] / $bookInfo->getCountPage() * $bookInfo->getCountWord());
}
if ($pageInfo) {
$data['now_word'] = max($pageInfo->getNowWord(), $now_word);
} else {
$data['now_word'] = $now_word;
}
//临时保存需要的数据，提供给控制器使用
$this->tmpData = ['now_word' => $now_word, 'old_word' => $pageInfo ? min($now_word, $pageInfo->getNowWord()) : 0, 'is_new' => $pageInfo ? 0 : 1, 'is_finished' => $data['is_finished'] == 1 ? 1 : 0];
return Page::dataSave($data, $pageInfo, 'Page');
}