SignController.class.php
7.95 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
<?php
/**
* Created by PhpStorm.
* User: liyifei2012it
* Date: 17/8/15
* Time: 16:42
*/
namespace Frontend\Controller\UpdateDB;
use Think\Db;
class SignController extends AbstractController
{
public function before_action($action = '')
{
return parent::before_action($action);
}
/**
* 数据库升级脚本，签到数据同步
* @author tangxingguo
* @date 2017-9-11
*/
public function updateSignData()
{
$sql = $this->_signDataSql();
$this->_exeSql($sql);
}
/**
* 签到ICON路径修改
* @author tangxingguo
*/
public function updateSignConfig()
{
$sql = $this->_updateSignConfigSql();
$this->_exeSql($sql);
}
/**
* 更新课程任务相关字段
* @author tangxingguo
*/
public function updateCourse()
{
$sql = $this->_updateCourseSql();
$this->_exeSql($sql);
}
/**
* 任务中心ICON路径修改
* @author tangxingguo
*/
public function updateTaskConfig()
{
$sql = $this->_updateTaskConfigSql();
$this->_exeSql($sql);
}
/**
* 执行sql语句
* @author tangxingguo
* @param $sql
*/
private function _exeSql($sql)
{
if (empty($sql)) {
exit('没有可升级的sql');
}
$db = &Db::getInstance();
try {
$db->execute($sql);
} catch (\Exception $e) {
exit("数据库升级失败：" . $e->getMessage());
}
exit("数据库升级完成，执行sql如下：{$sql}");
}
/**
* 同步签到数据的sql
* 1、同步以前的签到数据
* @author tangxingguo
* @return string
*/
private function _signDataSql()
{
// sql初始化
$sql = '';
$qy_domain = QY_DOMAIN;
// 1、同步以前的签到数据
$DbServ = &Db::getInstance();
$signCountSql = "select * from `oa_sign_count`";
$countList = $DbServ->query($signCountSql);
if (!empty($countList)) {
// 清空表（多次操作兼容）
$sql = "TRUNCATE oa_task_user_sign;";
// 签到数据格式化
$sql .= "INSERT INTO `oa_task_user_sign` ( `uid`, `username`, `sign_total`, `integral`, `update_time`, `domain`, `status`, `created`, `updated`, `deleted`) VALUES ";
foreach ($countList as $v) {
$sql .= "('{$v['uid']}', '{$v['username']}', '{$v['continuous']}', 0, {$v['last_time']}, '{$qy_domain}', '{$v['status']}', '{$v['created']}', '{$v['updated']}', '{$v['deleted']}')";
if (end($countList) == $v) {
$sql .= ";";
} else {
$sql .= ",";
}
}
}
return $sql;
}
/**
* 增加运营中心ICON路径的sql
* 1、增加运营中心ICON路径
* @author tangxingguo
* @return string
*/
private function _updateTaskConfigSql()
{
// sql初始化
$sql = '';
$qy_domain = QY_DOMAIN;
$DbServ = &Db::getInstance();
// 取运营中心配置
$configSql = "select * from `oa_common_setting` where `domain` = '{$qy_domain}' and `key` = 'appConfig' and `status` < 3";
$config = $DbServ->query($configSql);
// 1、修改运营中心ICON配置
if (!empty($config)) {
$setting = unserialize($config[0]['value']);
$setting['Task'] = [
'open' => 1,
'name' => '员圈任务',
'iconApi' => 'Task/Apicp/Operate/IconApi',
'bannerApi' => [],
];
$setting = serialize($setting);
$updated = MILLI_TIME;
$sql .= "UPDATE `oa_common_setting` SET `value` = '{$setting}', `updated` = {$updated} WHERE `domain` = '{$qy_domain}' and `key` = 'appConfig' and `status` < 3;";
}
return $sql;
}
/**
* 修改签到运营中心ICON路径的sql
* 1、修改签到运营中心ICON路径
* @author tangxingguo
* @return string
*/
private function _updateSignConfigSql()
{
// sql初始化
$sql = '';
$qy_domain = QY_DOMAIN;
$DbServ = &Db::getInstance();
// 取运营中心配置
$configSql = "select * from `oa_common_setting` where `domain` = '{$qy_domain}' and `key` = 'appConfig' and `status` < 3";
$config = $DbServ->query($configSql);
// 1、修改运营中心ICON配置
if (!empty($config)) {
$setting = unserialize($config[0]['value']);
$setting['Sign'] = [
'open' => 1,
'name' => '签到',
'iconApi' => 'Task/Apicp/Operate/SignIconApi',
'bannerApi' => [],
];
$setting = serialize($setting);
$updated = MILLI_TIME;
$sql .= "UPDATE `oa_common_setting` SET `value` = '{$setting}', `updated` = {$updated} WHERE `domain` = '{$qy_domain}' and `key` = 'appConfig' and `status` < 3;";
}
return $sql;
}
/**
* 升级课程的sql
* 1、修改oa_course_article（课程主表），增加customtask_id字段；
* 2、修改oa_course_exam（课程测评结果记录表），增加customtask_id字段、索引；
* 3、修改oa_course_study（课程学习表），增加customtask_id字段、索引；
* 4、修改oa_course_study_record（课程学习记录表），增加customtask_id字段、索引；
* @author tangxingguo
*/
private function _updateCourseSql()
{
// sql初始化
$sql = '';
$DbServ = &Db::getInstance();
// 2、修改oa_course_article（课程主表），增加customtask_id字段；
$articleColumnList = $DbServ->query("show columns from `oa_course_article`");
$articleColumns = array_column($articleColumnList, 'field');
if (!in_array('course_type', $articleColumns)) {
$sql .= "ALTER TABLE `oa_course_article` ADD COLUMN `course_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '课程类别（1=常规课程；2=任务类课程）' AFTER `article_title`;";
}
// 3、修改oa_course_exam（课程测评结果记录表），增加customtask_id字段、索引；
$articleColumnList = $DbServ->query("show columns from `oa_course_exam`");
$articleColumns = array_column($articleColumnList, 'field');
if (!in_array('customtask_id', $articleColumns)) {
$sql .= "ALTER TABLE `oa_course_exam` ADD COLUMN `customtask_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '任务ID' AFTER `source_id`;";
$sql .= "CREATE INDEX `customtask_id` ON `oa_course_exam`(`customtask_id`) USING BTREE;";
}
// 4、修改oa_course_study（课程学习表），增加customtask_id字段、索引；
$articleColumnList = $DbServ->query("show columns from `oa_course_study`");
$articleColumns = array_column($articleColumnList, 'field');
if (!in_array('customtask_id', $articleColumns)) {
$sql .= "ALTER TABLE `oa_course_study` ADD COLUMN `customtask_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '任务ID' AFTER `article_id`;";
$sql .= "CREATE INDEX `customtask_id` ON `oa_course_study`(`customtask_id`) USING BTREE;";
}
// 5、修改oa_course_study_record（课程学习记录表），增加customtask_id字段、索引；
$articleColumnList = $DbServ->query("show columns from `oa_course_study_record`");
$articleColumns = array_column($articleColumnList, 'field');
if (!in_array('customtask_id', $articleColumns)) {
$sql .= "ALTER TABLE `oa_course_study_record` ADD COLUMN `customtask_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '任务ID' AFTER `article_chapter_id`;";
$sql .= "CREATE INDEX `customtask_id` ON `oa_course_study_record`(`customtask_id`) USING BTREE;";
}
return $sql;
}
}