UpdateCourseController.class.php
12.5 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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
<?php
/**
* Created by PhpStorm.
* User: zhonglei
* Date: 17/10/26
* Time: 18:25
*/
namespace Frontend\Controller\UpdateDB;
use Think\Db;
class UpdateCourseController extends AbstractController
{
public $qydomain = '';
public $article_List = [];
/**
* 数据中心课程改造升级
* @author zhonglei
*/
public function Index()
{
$this->UpdateTable();
//$this->UpdateData();
}
/**
* 增加oa_course_complete、oa_course_count、oa_course_study_time表
* 更新oa_course_article表，增加user_total字段
* @author zhonglei
*/
public function UpdateTable()
{
$db = &Db::getInstance();
// 增加oa_course_complete、oa_course_count、oa_course_study_time表
$sql = "
CREATE TABLE IF NOT EXISTS `oa_course_complete` (
`complete_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`article_id` int(10) NOT NULL COMMENT '课程ID',
`uid` char(32) NOT NULL COMMENT '用户ID',
`username` varchar(50) NOT NULL COMMENT '用户姓名',
`domain` char(32) NOT NULL COMMENT '企业域名',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '数据状态（1=新创建；2=已更新；3=已删除）',
`created` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted` bigint(20) NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`complete_id`),
KEY `article_id` (`article_id`),
KEY `uid` (`uid`),
KEY `domain` (`domain`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程已完成记录表';
CREATE TABLE IF NOT EXISTS `oa_course_count` (
`count_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`article_id` int(10) NOT NULL COMMENT '课程ID',
`user_total` int(10) NOT NULL DEFAULT '0' COMMENT '可参与学习人数（权限范围）',
`studying_total` int(10) NOT NULL DEFAULT '0' COMMENT '学习中人数（已参与学习，但未完成）',
`complete_total` int(10) NOT NULL DEFAULT '0' COMMENT '已完成人数',
`unstudy_total` int(10) NOT NULL DEFAULT '0' COMMENT '未参与学习人数（从未点击、访问过课程，没学习时间）',
`count_date` date NOT NULL COMMENT '统计时间',
`domain` char(32) NOT NULL COMMENT '企业域名',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '数据状态（1=新创建；2=已更新；3=已删除）',
`created` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted` bigint(20) NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`count_id`),
KEY `article_id` (`article_id`),
KEY `domain` (`domain`),
KEY `status` (`status`),
KEY `count_date` (`count_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程数据统计表';
CREATE TABLE IF NOT EXISTS `oa_course_study_time` (
`study_time_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`article_id` int(10) NOT NULL COMMENT '课程ID',
`article_chapter_id` int(10) NOT NULL COMMENT '章节ID',
`source_id` int(10) NOT NULL COMMENT '素材ID',
`uid` char(32) NOT NULL COMMENT '用户ID',
`username` varchar(50) NOT NULL COMMENT '用户姓名',
`study_time` int(10) NOT NULL COMMENT '学习时长，单位秒',
`domain` char(32) NOT NULL COMMENT '企业域名',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '数据状态（1=新创建；2=已更新；3=已删除）',
`created` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted` bigint(20) NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`study_time_id`),
KEY `article_id` (`article_id`),
KEY `uid` (`uid`),
KEY `domain` (`domain`),
KEY `status` (`status`),
KEY `source_id` (`source_id`),
KEY `article_chapter_id` (`article_chapter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='常规课程学习时长明细表';";
$db->query($sql);
// 更新oa_course_article表，增加user_total字段
$data = $db->query('DESC `oa_course_article` `user_total`');
if (empty($data)) {
$db->execute('ALTER TABLE `oa_course_article` ADD COLUMN `user_total` int(10) NOT NULL COMMENT \'可参与学习人数（权限范围）\' AFTER `is_step`;');
echo '已添加user_total字段<br />';
} else {
echo 'user_total字段已存在，无需更新<br />';
}
}
/**
* 更新课程中心的完成数据
*/
public function UpdateData()
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
$domains_json = I('get.domains_json');
if (empty($domains_json)) {
exit('暂无课程数据');
}
$domains = json_decode($domains_json, true);
if (empty($domains) || !is_array($domains)) {
exit('暂无课程数据');
}
$domain_str = '';
foreach ($domains as $v) {
if (empty($domain_str)) {
$domain_str = "'" . $v . "'";
} else {
$domain_str .= ",'" . $v . "'";
}
}
$db = &Db::getInstance();
$sql = "SELECT `article_id`,`course_type`,`et_ids`,`is_exam`,`domain` FROM `oa_course_article` WHERE `status` < 3 and domain in({$domain_str})";
$articleList_all = $db->query($sql);
if (empty($articleList_all)) {
exit('暂无课程数据');
}
sort($domains);
// 循环企业，按照企业来循环更新
foreach ($domains as $domain) {
$this->UpdateComplete($domain, $articleList_all);
}
}
/**
* 数据中心，课程中心改造：将已学习、已测评数据，升级至已完成表。
* @author liyifei
* @date 2017-10-10
*
* @param string $domain 企业domain
* @param array $articleList_all 所有课程列表
*/
protected function UpdateComplete($domain = '', $articleList_all = [])
{
$db = &Db::getInstance();
$qy_domain = $domain;
$this->qydomain = $domain;
array_filter($articleList_all, function ($v) {
if ($v['domain'] == $this->qydomain) {
$this->article_List[] = $v;
}
});
$articleList = $this->article_List;
if (empty($articleList)) {
exit('暂无课程数据');
}
// 写入已完成表的sql
$sqlStudyTime = "INSERT INTO `oa_course_study_time` (`article_id`, `uid`, `username`, `study_time`, `domain`, `status`, `created`) VALUES ";
// 写入已完成表的sql
$sqlComplete = "INSERT INTO `oa_course_complete` (`article_id`, `uid`, `username`, `domain`, `status`, `created`) VALUES ";
foreach ($articleList as $article) {
// 过滤非常规类型课程
if (isset($article['course_type']) && $article['course_type'] != 1) {
continue;
}
$dataComplete = '';
$dataStudyTime = '';
// 无测评（已学习=已完成）
if ($article['is_exam'] == 1 && empty($article['et_ids'])) {
$studyList = $db->query("SELECT * FROM `oa_course_study` WHERE `article_id` = {$article['article_id']} AND `domain` = '{$qy_domain}' AND `status` < 3");
if (empty($studyList)) {
continue;
}
// 课程完成表已存在的数据
$completeList = $this->_completeList($studyList);
foreach ($studyList as $study) {
// 过滤已完成表存在的数据
if (!empty($completeList)) {
foreach ($completeList as $complete) {
if ($study['article_id'] == $complete['article_id'] && $study['uid'] == $complete['uid']) {
continue 2;
}
}
}
$dataComplete .= "({$study['article_id']}, '{$study['uid']}', '{$study['username']}', '{$study['domain']}', 1, '{$study['created']}'),";
$dataStudyTime .= "({$study['article_id']}, '{$study['uid']}', '{$study['username']}', 1, '{$study['domain']}', 1, '{$study['created']}'),";
}
// 有测评（测评通过=已完成）
} else {
$examList = $db->query("SELECT * FROM `oa_course_exam` WHERE `article_id` = {$article['article_id']} AND `is_pass` = 2 AND `domain` = '{$qy_domain}' AND `status` < 3 ORDER BY `created` ASC");
if (empty($examList)) {
continue;
}
// 课程完成表已存在的数据
$completeList = $this->_completeList($examList);
$uids = [];
foreach ($examList as $exam) {
// 多次测评时，仅记录最早测评通过的一次
if (in_array($exam['uid'], $uids)) {
continue;
}
$uids[] = $exam['uid'];
// 过滤已完成表存在的数据
if (!empty($completeList)) {
foreach ($completeList as $complete) {
if ($exam['article_id'] == $complete['article_id'] && $exam['uid'] == $complete['uid']) {
continue 2;
}
}
}
$dataComplete .= "({$exam['article_id']}, '{$exam['uid']}', '{$exam['username']}', '{$exam['domain']}', 1, '{$exam['created']}'),";
$dataStudyTime .= "({$exam['article_id']}, '{$exam['uid']}', '{$exam['username']}', 1, '{$exam['domain']}', 1, '{$exam['created']}'),";
}
}
// 防止写入数据库时数据量过大，以课程为单位，分批写入
// 学习时长记录
if ($dataStudyTime) {
$dataStudyTime = substr($dataStudyTime, 0, -1);
$sql = $sqlStudyTime . $dataStudyTime;
try {
$db->query($sql);
echo "学习时长记录-成功的课程：{$article['article_id']}" . "\n";
} catch (\Exception $e) {
echo "学习时长记录-失败的课程：{$article['article_id']} - " . $e->getMessage() . "\n";
}
}
// 学习完成记录
if ($dataComplete) {
$dataComplete = substr($dataComplete, 0, -1);
$sql = $sqlComplete . $dataComplete;
try {
$db->query($sql);
echo "学习完成记录-成功的课程：{$article['article_id']}" . "\n";
} catch (\Exception $e) {
echo "学习完成记录-失败的课程：{$article['article_id']} - " . $e->getMessage() . "\n";
}
}
}
}
/**
* 课程已完成表中已存在的数据
*
* @param array $list 学习、测评数据
*
* @return array
*/
private function _completeList($list)
{
$db = &Db::getInstance();
$qy_domain = QY_DOMAIN;
$completeList = [];
$where = '';
foreach ($list as $v) {
$where .= " (`article_id` = {$v['article_id']} AND `uid` = '{$v['uid']}') OR";
}
if ($where) {
$where = substr($where, 0, -2);
$completeList = $db->query("SELECT * FROM `oa_course_complete` WHERE `domain` = '{$qy_domain}' AND `status` < 3 AND ({$where})");
}
return $completeList;
}
}