<?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;
}
}