public function getStudentSortSource($student_id = 0, $ab_id = 30, $classes_id = 0)
{
if (!$student_id) {
return false;
}
$statistics = new Statistics();
$statistics_table = $statistics->getSource();
$other_where = '';
$sql = "select count(1) as f from {$statistics_table} s where s.val >=\n(select ssssss.val from metathink_read_statistics ssssss where ssssss.uid = :student_id and ssssss.ab_id=30) and s.ab_id=30";
}
public function testAction()
{
$r = Statistics::findFirst(1);
var_dump($r->toArray());
$user = $r->user->real_name;
var_dump($user);
die(__METHOD__);
}
/**
* 根据班级id获取班级所有学生（可通过get参数的classes_id获取）
*
* @param int $classId
* @param bool/array $other_sql 获取其他数据，且排序
*
* @return \ST\Common\Models\User[]
* @author Hunter.<*****@*****.**>
*/
public function getClassStudent($classId = 0, $other_sql = false)
{
if (!$classId) {
return false;
}
$key = md5(__NAMESPACE__ . __METHOD__ . $classId . st_array_to_string($other_sql));
$result = self::$cache->getCache($key);
if ($result === null) {
//根据班级id获取信息
if ($other_sql === false) {
$result = $this->getCommon(["conditions" => "classes_id = :classId: AND type = 1", "bind" => ['classId' => $classId]], 'User');
} else {
//根据配置获取所有的统计key
$tmp_k = array_keys(Config('CONFIG_STATISTICS_AB_ID'));
if (!in_array($other_sql['key'], $tmp_k)) {
$other_sql['key'] = current($tmp_k);
}
//todo 重新构建sql查询
$user = new User();
$user_table = $user->getSource();
$statistics = new Statistics();
$statistics_table = $statistics->getSource();
//构建if ab_id查询和group_concat查询
$if_ab_id_query = '';
$group_concat = '';
foreach (Config('CONFIG_STATISTICS_AB_ID') as $key => $ab) {
$if_ab_id_query .= "if(ab_id={$key}, val, '') as ab_{$key},";
$group_concat .= "GROUP_CONCAT(s.ab_{$key} SEPARATOR '') as ab{$key},";
}
$if_ab_id_query = rtrim($if_ab_id_query, ',');
$group_concat = rtrim($group_concat, ',');
$sql_query = "select u.id,u.real_name,u.image,{$group_concat} from {$user_table} as u\nleft join (\n select\n uid,{$if_ab_id_query}\n from {$statistics_table}\n) as s on s.uid=u.id\nwhere u.type=1 and u.classes_id=:classes_id\ngroup by u.id\norder by ab{$other_sql['key']} {$other_sql['sort']}";
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(':classes_id', $classId, \PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return ['result' => $result];
} catch (\PDOException $e) {
if (DEBUG) {
die($e->getMessage());
} else {
$this->di->get('logger_error')->log('PDOException: ' . $e->getMessage(), \Phalcon\Logger::ERROR);
return $this->di->get('dispatcher')->forward(['module' => 'home', 'controller' => 'error', 'action' => 'show_sql_error']);
}
}
return null;
//移除配置key中提交的key
/*$st_tmp = array_flip($tmp_k);
unset($st_tmp[$other_sql['key']]);
$tmp_k = array_flip($st_tmp);
$tmp_v = ','.implode(',', $tmp_k);
//组合order查询 FIELD(ab_id,60,1,2,3,4,30,40),val+0 desc
//todo 如果在Statistics表中没有对应的ab_id数据，排序失败
$order = 'ab_id '.$other_sql['sort'].',FIELD(ab_id,'.$other_sql['key'].$tmp_v.'),val+0 '.$other_sql['sort'];
$builder = $this->di->get('modelsManager')->createBuilder()
->columns(['user.real_name','user.image','user.id as userid','s.*'])
->from(['user' => 'ST\Common\Models\User'])
->leftJoin('ST\Common\Models\Statistics', 's.uid=user.id and s.classes_id=user.classes_id and s.school_id=user.school_id', 's')
->where('user.type=1')
->andWhere('user.classes_id=:classes_id:')
->andWhere('user.school_id=:school_id:')
->orderBy($order);
$result = $builder->getQuery()->execute($other_sql['where']);*/
}
self::$cache->setCache($key, $result);
}
return $result;
}