<?php
/**
 * Created by PhpStorm.
 */
namespace Frontend\Controller\UpdateDB;

use Think\Db;

class IndexController extends AbstractController
{
    /**
     * 数据库升级脚本
     *      1、为新增的培训阶段表，写入数据：为原来每个培训新建一个培训阶段，所有培训安排都放在各自的培训阶段中；
     *      2、将培训阶段表的数据，写入对应的培训安排表；
     * @author liyifei
     * @return string
     */
    public function Index()
    {
        $db = &Db::getInstance();

        // 查询已存在培训阶段的培训id
        $stage_list = $db->query("SELECT DISTINCT `ed_id` FROM `oa_education_stage` WHERE `status` < 3");
        $ed_ids = array_column($stage_list, 'ed_id');

        // 无培训阶段的培训id
        $ed_ids = !empty($ed_ids) ? implode(',', $ed_ids) : 0;
        $plan_list = $db->query("SELECT DISTINCT `ed_id`, `domain` FROM `oa_education_plan` WHERE `ed_id` NOT IN ({$ed_ids}) AND `ed_id` > 0 AND `stage_id` = 0 AND `status` < 3");

        // 拼接写入培训阶段表的sql
        $content = '';
        $times = MILLI_TIME;
        $stage_sql = 'INSERT INTO `oa_education_stage` (`ed_id`, `stage_name`, `stage_order`, `domain`, `status`, `created`) VALUES ';
        foreach ($plan_list as $plan) {
            $content .= "({$plan['ed_id']}, '阶段1', 1, '{$plan['domain']}', 1, {$times}),";
        }

        // 执行sql，写入"培训阶段"数据
        if (!empty($content)) {
            $stage_sql .= substr($content, 0, -1) . ';';

            try {
                $db->query($stage_sql);

            } catch (\Exception $e) {

                // exit("数据库升级失败：" . $e->getMessage());
            }
        }

        // 阶段表数据
        $stage_list = $db->query("SELECT * FROM `oa_education_stage` WHERE `status` < 3");
        $stage_list = array_combine_by_key($stage_list, 'ed_id');

        // 安排表数据
        $plan_list = $db->query("SELECT * FROM `oa_education_plan` WHERE `stage_id` = 0 AND `status` < 3");
        foreach ($plan_list as $plan) {
            $ed_id = $plan['ed_id'];
            if (isset($stage_list[$ed_id])) {
                $update_sql = 'UPDATE `oa_education_plan` SET `stage_id` = ' . $stage_list[$ed_id]['stage_id'] . ' WHERE `plan_id` = ' . $plan['plan_id'] . ';';

                // 执行sql，为培训安排表写入培训阶段id
                try {
                    $db->query($update_sql);

                } catch (\Exception $e) {

                    // exit("数据库升级失败：" . $e->getMessage());
                }
            }
        }

        exit('SUCCESS');
    }
}