ProjectData.php
6.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
<?php
namespace app\school\controller\project_common;
use app\school\model\project_common\ProjectReviewResult;
use think\Db;
trait ProjectData
{
/**
* 返回中期检查前的的项目
* @param $projectWhere
* @return array
*/
private function approvalProjectData($projectWhere = [])
{
// $projectWhere = array_merge(['a.review_stage' => 0], $projectWhere);
$res = $this->projectData($projectWhere);
foreach ($res['rows'] as &$row) {
// 已经中期评审或者立项结题的项目状态为:已立项、已提交
if ('0' != $row['review_stage']) {
$row['check_stage'] = 3;
}
}
return $res;
}
/**
* 返回立项评审后项目结题前的项目
* @param array $projectWhere
* @return array
*/
private function midProjectData($projectWhere = [])
{
$projectWhere = array_merge(['a.review_stage' => 1], $projectWhere);
return $this->projectData($projectWhere);
}
/**
* 返回项目结题的项目
* @param array $projectWhere
* @return array
*/
private function conclusionProjectData($projectWhere = [])
{
$projectWhere = array_merge(['a.review_stage' => 2], $projectWhere);
return $this->projectData($projectWhere);
}
/**
* 根据筛选条件查询项目数据
* @param array $projectWhere
* @return array
*/
private function projectData($projectWhere = [])
{
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
// 默认查询项目容器下未淘汰(|未退回)的项目
$projectWhere = array_merge(['a.container_id' => $this->containerId, 'a.project_status' => 0], $projectWhere);
$field = [
'a.*',
't.name as project_type_name',
'u.nickname',
'u.student_no',
'c.college_name as college_name',
'd.specialty_name as major_name',
'SUM(r.result) as sum',
'count(member_id) as memberCount'
];
// $total = $this->model->alias('a')->where($projectWhere)->count();
$totalQuery = $this->model
->field('a.id')
->alias('a')
->join('user u', 'a.user_id = u.id', 'left')
->join('project_type t', 'a.project_type = t.id', 'left')
->join('college c', 'u.college = c.id', 'left')
->join('specialty d', 'u.specialty = d.id', 'left');
if(isset($this->reviewStage)) {
$totalQuery->join('project_review_result r',"a.id = r.project_register_id AND r.review_stage = {$this->reviewStage}",'left');
}else {
$totalQuery->join('project_review_result r',"a.id = r.project_register_id",'left');
}
$total= $totalQuery->group('a.id')->where($projectWhere)
->count();
// $total = $this->model->alias('a')->where($where)->where($projectWhere)->count();
$query = $this->model
->field($field)
->alias('a')
->join('user u', 'a.user_id = u.id', 'left')
->join('project_type t', 'a.project_type = t.id', 'left')
->join('college c', 'u.college = c.id', 'left')
->join('fa_members m','a.id = m.item_id and member_type = 1 and member_source = 2','left')
->join('specialty d', 'u.specialty = d.id', 'left');
if(isset($this->reviewStage)) {
$query->join('project_review_result r',"a.id = r.project_register_id AND r.review_stage = {$this->reviewStage}",'left');
}else {
$query->join('project_review_result r',"a.id = r.project_register_id",'left');
}
$rows = $query->group('a.id')
->order('sum','desc')
->where($projectWhere)
->limit($offset, $limit)
->select();
$projectIds = array_column($rows, 'id');
if ( isset($this->reviewStage) ) {
$tempScoreResults = (new ProjectReviewResult())
->where(['project_register_id' => ['in', $projectIds], 'review_stage' => $this->reviewStage])
->group('project_register_id')
->column('GROUP_CONCAT(review_id, \':\', IFNULL(result, \'-\'))', 'project_register_id');
foreach ($rows as &$row) {
$review_scores = [];
// 项目有评审结果,就添加上
if (isset($tempScoreResults[$row['id']])) {
$review_scores = $this->getReviewResultArr($tempScoreResults[$row['id']]);
}
$row['review_scores'] = $review_scores;
}
}
foreach ($rows as &$row) {
$row['project_teachers'] = json_decode($row['project_teachers'], true);
}
$result = [
'total' => $total,
'rows' => $rows
];
return $result;
}
/**
* 评审结果字符串转数组(字符串格式: // 项目id:项目打分结果,项目id:项目打分结果)
* @param $reviewResultStr
* @return array
*/
private function getReviewResultArr($reviewResultStr)
{
$result = [];
foreach (explode(',', $reviewResultStr) as $v) {
$temp = explode(':', $v);
$result[$temp[0]] = $temp[1];
}
return $result;//
}
/**
* 获取导出csv Sql
* @param array $projectWhere
* @return array
*/
public function getExportSql($projectWhere = [])
{
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
// 默认查询项目容器下未淘汰(|未退回)的项目
$projectWhere = array_merge(['a.container_id' => $this->containerId, 'a.project_status' => 0], $projectWhere);
$field = ['a.id','a.project_name','a.project_no','t.name as project_type_name','f.college_name as project_college','u.nickname','u.student_no','c.college_name as college_name','d.college_name as major_name','CASE a.project_level WHEN 0 THEN \'未评级\' WHEN 1 THEN \'院级\' WHEN 2 THEN \'校级\' WHEN 3 THEN \'省级\' ELSE \'国家级\' END as project_level','CASE WHEN a.check_stage < 2 THEN \'未立项\' ELSE \'已立项\' END as check_stage'];
$total = $this->model->alias('a')->where($where)->where($projectWhere)->count();
$sql = $this->model
->field($field)
->alias('a')
->join('user u', 'a.user_id = u.id', 'left')
->join('project_type t', 'a.project_type = t.id', 'left')
->join('college c', 'u.college = c.id', 'left')
->join('college d', 'u.specialty = d.id', 'left')
->join('college f','a.project_college = f.id','left')
->where($where)->where($projectWhere)
->fetchSql(true)
->select();
return ['sql' => $sql,'total' => $total];
}
}