/**
* 根据学校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']);
}
}
}
