在 Yii 2.0 中,编辑与更新租户的任务类型与步骤设置(多条记录、树形结构)的实现 (二)

1、编辑与更新租户的任务类型与步骤设置的需求有所变更,需要让用户可自主添加任务类型,然后从 18 个步骤中选择其中一些加入至新添加的任务类型中。上一版本的实现:http://www.shuijingwanwq.com/2019/11/06/3583/ ,在上一版本中,仅允许用户从现有的任务类型与步骤中删除或者取消勾选,但是不能够新增加任务类型。

2、在全局的任务步骤配置表中,再添加一个 手动任务 下的步骤(上传素材),后续每个租户第 1 次进入编辑页面时,默认包含 5 个任务类型,每个任务类型下的任务步骤全部为勾选状态。此时,用户可自主添加任务类型,然后从 18 个步骤中选择其中一些加入至新添加的任务类型中。任务类型可(添加、删除、重命名其名称、排序),不可(勾选、不勾选)。任务步骤可从 18 个任务步骤列表中选择(添加、删除、勾选、不勾选、排序)。

3、新建数据库迁移文件(\console\migrations\m191108_073439_config_task_steps.php),新建步骤配置表,初始化步骤配置表的记录,删除租户的任务配置表、删除租户的任务步骤配置表

<?php

use yii\db\Migration;

/**
 * Class m191108_073439_config_task_steps
 */class m191108_073439_config_task_steps extends Migration
{
    /**
     * {@inheritdoc}
     */    public function safeUp()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB COMMENT="步骤配置"';
        }
        $this->createTable('{{%config_step}}', [
            'id' => $this->primaryKey(),
            'group_id' => $this->string(32)->notNull()->comment('租户ID'),
            'code' => $this->string(32)->notNull()->comment('代码'),
            'name' => $this->string(32)->notNull()->comment('名称'),
            'category' => $this->smallInteger(1)->notNull()->defaultValue(1)->comment('类型,1:系统内置;2:用户定义'),
            'status' => $this->smallInteger()->notNull()->defaultValue(1)->comment('状态,0:禁用;1:启用'),
            'is_not_isolated' => $this->smallInteger()->notNull()->defaultValue(0)->comment('是否跨租户(不隔离),0:否;1:是'),
            'is_deleted' => $this->smallInteger()->notNull()->defaultValue(0)->comment('是否被删除,0:否;1:是'),
            'created_at' => $this->integer()->notNull()->defaultValue(0)->comment('创建时间'),
            'updated_at' => $this->integer()->notNull()->defaultValue(0)->comment('更新时间'),
            'deleted_at' => $this->integer()->notNull()->defaultValue(0)->comment('删除时间'),
        ], $tableOptions);

        $this->createIndex('idx_code', '{{%config_step}}', ['code'], $unique = false);

        $this->insert('{{%config_step}}', [
            'id' => 1,
            'group_id' => '',
            'code' => 'retrieval',
            'name' => '取稿',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 2,
            'group_id' => '',
            'code' => 'begin_shot',
            'name' => '上传素材',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 3,
            'group_id' => '',
            'code' => 'material_review',
            'name' => '素材审核',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 4,
            'group_id' => '',
            'code' => 'writing',
            'name' => '撰写稿件',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 5,
            'group_id' => '',
            'code' => 'end_shot',
            'name' => '拍摄完成',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 1,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => time(),
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 6,
            'group_id' => '',
            'code' => 'commit_doc',
            'name' => '提交稿件',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 1,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => time(),
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 7,
            'group_id' => '',
            'code' => 'manuscript_review',
            'name' => '稿件审核',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 8,
            'group_id' => '',
            'code' => 'writing_draft',
            'name' => '撰写通稿',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 9,
            'group_id' => '',
            'code' => 'newspaper_issued',
            'name' => '报纸签发',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 10,
            'group_id' => '',
            'code' => 'video_editing',
            'name' => '视频编辑',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 11,
            'group_id' => '',
            'code' => 'tandem_list',
            'name' => '串联单',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 12,
            'group_id' => '',
            'code' => 'wechat_group_reference',
            'name' => '微信组稿引用',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 13,
            'group_id' => '',
            'code' => 'finish_interview',
            'name' => '结束采访',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 14,
            'group_id' => '',
            'code' => 'program_render',
            'name' => '节目上传',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 15,
            'group_id' => '',
            'code' => 'website_release',
            'name' => '网站发布',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 16,
            'group_id' => '',
            'code' => 'program_authen',
            'name' => '节目审查',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 17,
            'group_id' => '',
            'code' => 'app_release',
            'name' => 'APP发布',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 18,
            'group_id' => '',
            'code' => 'wechat_release',
            'name' => '微信发布',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 19,
            'group_id' => '',
            'code' => 'blog_release',
            'name' => '微博发布',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);
        $this->insert('{{%config_step}}', [
            'id' => 20,
            'group_id' => '',
            'code' => 'qq_release',
            'name' => '企鹅号发布',
            'category' => 1,
            'status' => 1,
            'is_not_isolated' => 0,
            'is_deleted' => 0,
            'created_at' => time(),
            'updated_at' => time(),
            'deleted_at' => 0,
        ]);

        $this->alterColumn('{{%config_task_step}}', 'config_task_id', $this->integer()->notNull()->comment('任务配置ID'));
        $this->addColumn('{{%config_task_step}}', 'config_task_code', $this->string(32)->notNull()->comment('任务配置代码')->after('config_task_id'));
        $this->addColumn('{{%config_task_step}}', 'config_step_id', $this->integer()->notNull()->comment('步骤配置ID')->after('config_task_code'));
        $this->update('{{%config_task_step}}', ['config_task_code' => 'tv_broadcast'], ['config_task_id' => 1]);
        $this->update('{{%config_task_step}}', ['config_task_code' => 'inter_view'], ['config_task_id' => 2]);
        $this->update('{{%config_task_step}}', ['config_task_code' => 'rich_doc'], ['config_task_id' => 3]);
        $this->update('{{%config_task_step}}', ['config_step_id' => 1], ['step_code' => 'retrieval']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 2], ['step_code' => 'begin_shot']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 3], ['step_code' => 'material_review']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 4], ['step_code' => 'writing']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 5, 'status' => 1], ['step_code' => 'end_shot']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 6, 'is_default' => 1, 'is_deleted' => 1, 'deleted_at' => time()], ['step_code' => 'commit_doc']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 7], ['step_code' => 'manuscript_review']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 8], ['step_code' => 'writing_draft']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 9], ['step_code' => 'newspaper_issued']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 10], ['step_code' => 'video_editing']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 11], ['step_code' => 'tandem_list']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 12], ['step_code' => 'wechat_group_reference']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 13], ['step_code' => 'finish_interview']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 14], ['step_code' => 'program_render']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 15], ['step_code' => 'website_release']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 16], ['step_code' => 'program_authen']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 17], ['step_code' => 'app_release']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 18], ['step_code' => 'wechat_release']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 19], ['step_code' => 'blog_release']);
        $this->update('{{%config_task_step}}', ['config_step_id' => 20], ['step_code' => 'qq_release']);

        $this->dropTable('{{%config_group_task}}');
        $this->dropTable('{{%config_group_task_step}}');

        $this->insert('{{%config_task_step}}', [
            'id' => 27,
            'group_id' => '',
            'config_task_id' => 4,
            'config_task_code' => 'other',
            'config_step_id' => 2,
            'step_code' => 'begin_shot',
            'step_name' => '上传素材',
            'sort_order' => 1,
            'is_default' => 1,
            'up_status_type' => 2,
            'status' => 1,
            'created_at' => time(),
            'updated_at' => time()
        ]);

        $this->dropIndex('uc_group_id_config_task_id_step_code_is_deleted_deleted_at', '{{%config_task_step}}');
        $this->createIndex('uc_group_id_config_task_code_step_code_is_deleted_deleted_at', '{{%config_task_step}}', ['group_id', 'config_task_code', 'step_code', 'is_deleted', 'deleted_at'], $unique = true);
    }

    /**
     * {@inheritdoc}
     */    public function safeDown()
    {
        echo "m191108_073439_config_task_steps cannot be reverted.\n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m191108_073439_config_task_steps cannot be reverted.\n";

        return false;
    }
    */}

4、打开表:任务配置,如图1

图1

5、打开表:步骤配置,如图2

图2

6、打开表:任务步骤配置,如图3

图3

7、编辑路由配置文件,\api\config\urlManager.php

        // 基础设置 - 步骤配置
        [
            'class' => 'yii\rest\UrlRule',
            'controller' => ['v1/config-step'],
            'only' => ['index'],
        ],
        // 基础设置 - 租户设置 - 租户的任务类型与步骤设置
        [
            'class' => 'yii\rest\UrlRule',
            'controller' => ['v1/config-task-step'],
            'only' => ['view', 'edit', 'update'],
            'tokens' => ['{id}' => '<id:\\w[\\w,:;-]*>'],
            'extraPatterns' => [
                'GET edit/{id}' => 'edit',
            ],
        ],
        // 移动端 - 获取租户的任务类型与步骤设置详情
        [
            'class' => 'yii\rest\UrlRule',
            'controller' => ['v1/mobile/config-task-step'],
            'only' => ['view'],
            'tokens' => ['{id}' => '<id:\\w[\\w,:;-]*>'],
            'extraPatterns' => [
            ],
        ],

8、编辑控制器文件,\api\controllers\ConfigTaskStepController.php

<?php
/**
 * Created by PhpStorm.
 * User: Qiang Wang
 * Date: 2018/04/04
 * Time: 15:35
 */
namespace api\controllers;

use yii\rest\ActiveController;

/**
 * Class ConfigTaskStepController
 * @package api\controllers
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @since 1.0
 */class ConfigTaskStepController extends ActiveController
{
    /**
     * @inheritdoc
     */    public function actions()
    {
        $actions = parent::actions();
        // 禁用"index"、"create"、"delete"、"options"动作
        unset($actions['index'], $actions['create'], $actions['delete'], $actions['options']);
        $actions['view']['class'] = 'api\rests\config_task_step\ViewAction';
        $actions['update']['class'] = 'api\rests\config_task_step\UpdateAction';
        $actions['edit'] = [
            'class' => 'api\rests\config_task_step\EditAction',
            'modelClass' => $this->modelClass,
            'checkAccess' => [$this, 'checkAccess'],
        ];
        return $actions;
    }
}

9、新建编辑租户的任务类型与步骤设置的方法文件(\api\rests\config_task_step\EditAction.php)

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\config_task_step;

use Yii;
use api\models\redis\cmc_console\User as RedisCmcConsoleUser;
use api\services\ConfigTaskStepService;
use yii\web\UnprocessableEntityHttpException;

/**
 * 编辑租户的任务类型与步骤设置:/config-task-steps/edit/my-group-id(config-task-step/edit)
 *
 * For more details and usage information on ViewAction, see the [guide article on rest controllers](guide:rest-controllers).
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @since 1.0
 */class EditAction extends Action
{
    /**
     * Displays a model.
     * @param string $id the primary key of the model.
     * @return array
     * @throws UnprocessableEntityHttpException
     */    public function run($id)
    {
        // 当前用户的身份实例,未认证用户则为 Null
        /* @var $identity RedisCmcConsoleUser */        $identity = Yii::$app->user->identity;

        // 比对:group_id,检查其值是否等于:my-group-id
        if ($id != 'my-group-id') {
            throw new UnprocessableEntityHttpException(Yii::t('error', '226053'), 226053);
        }

        // 基于租户ID返回数据模型(任务步骤配置)列表
        $configTaskStepItems = ConfigTaskStepService::findModelsByGroupId($identity->group_id, false);

        return ['code' => 10000, 'message' => Yii::t('success', '126038'), 'data' => array_values($configTaskStepItems)];
    }
}

10、编辑租户的任务类型的服务文件(\common\services\ConfigTaskService.php)

<?php
/**
 * Created by PhpStorm.
 * User: Qiang Wang
 * Date: 2019/11/01
 * Time: 10:19
 */
namespace common\services;

use Yii;
use common\logics\ConfigTask;

class ConfigTaskService extends Service
{
    /**
     * 基于租户ID返回数据模型列表
     * @param string $groupId 租户ID
     * @return array 一个 ActiveRecord 实例数组
     * 格式如下:
     * [
     *     [ // object
     *         'id' => 1, // ID
     *         'group_id' => '', // 租户ID
     *         'code' => 'tv_broadcast', // 任务代码
     *         'name' => '电视播出', // 任务名称
     *         'sort_order' => 1, // 顺序,顺序排列
     *         'category' => 1, // 任务类型,1:系统内置;2:用户定义
     *         'parameter' => '', // 自定义参数,序列化存储
     *         'status' => 1, // 状态,0:禁用;1:启用
     *         'is_deleted' => 0, // 是否被删除,0:否;1:是
     *         'created_at' => 1573439809, // 创建时间
     *         'updated_at' => 1573439809, // 更新时间
     *         'deleted_at' => 0, // 删除时间
     *     ],
     *     [
     *         'id' => 1,
     *         'group_id' => '',
     *         'code' => 'inter_view',
     *         'name' => '采访任务',
     *         'sort_order' => 2,
     *         'category' => 1,
     *         'parameter' => '',
     *         'status' => 1,
     *         'is_deleted' => 0,
     *         'created_at' => 1573439809,
     *         'updated_at' => 1573439809,
     *         'deleted_at' => 0,
     *     ],
     *     ...
     * ]
     */    public static function findModelsByGroupId($groupId)
    {
        // 基于租户ID查找资源(任务配置)列表
        $configTaskItems = ConfigTask::find()->where(['group_id' => $groupId])->all();

        if (empty($configTaskItems)) {
            $groupId = '';
        }

        // 基于租户ID查找资源(任务配置、是否被删除:否)列表
        return ConfigTask::findAllByGroupId($groupId);
    }
}

11、编辑租户的任务类型与步骤设置的服务文件(\common\services\ConfigTaskStepService.php)

<?php
/**
 * Created by PhpStorm.
 * User: terryhong
 * Date: 2018/12/20
 * Time: 3:10 PM
 */
namespace common\services;

use Yii;
use common\logics\ConfigTask;
use common\logics\ConfigTaskStep;
use yii\web\NotFoundHttpException;
use yii\web\UnprocessableEntityHttpException;

class ConfigTaskStepService extends Service
{
    /**
     * 基于租户ID返回数据模型(任务步骤配置)列表
     * @param string $groupId 租户ID
     * @param bool $isDefaultYes 是否添加条件,默认步骤,1:是
     * @return array 一个 ActiveRecord 实例数组
     * 格式如下:
     * [
     *     1 = > [
     *         'id' => 1, // ID
     *         'code' => 'tv_broadcast', // 代码
     *         'name' => '电视播出', // 名称
     *         'sort_order' => 1, // 顺序,顺序排列
     *         'steps' => [ // 步骤列表
     *             [
     *                 'id' => 2, // ID
     *                 'code' => 'begin_shot', // 代码
     *                 'name' => '上传素材', // 名称
     *                 'sort_order' => 1, // 顺序,顺序排列
     *                 'is_default' => 1, // 是否默认步骤,1:是;0:否
     *             ],
     *             [
     *                 'id' => 4, // ID
     *                 'code' => 'writing', // 代码
     *                 'name' => '撰写稿件', // 名称
     *                 'sort_order' => 2, // 顺序,顺序排列
     *                 'is_default' => 1, // 是否默认步骤,1:是;0:否
     *             ],
     *             ...
     *         ],
     *     ],
     *     ...
     * ]
     */    public static function findModelsByGroupId($groupId, $isDefaultYes = true)
    {
        // 基于租户ID查找资源(任务步骤配置)列表
        $configTaskStepItems = ConfigTaskStep::find()->where(['group_id' => $groupId])->all();

        if (empty($configTaskStepItems)) {
            $groupId = '';
        }

        // 基于租户ID查找资源(任务步骤配置、是否被删除:否)列表
        if ($isDefaultYes) {
            $configTaskStepIsDeletedNoItems = ConfigTaskStep::findAllIsDefaultYesByGroupId($groupId);
        } else {
            $configTaskStepIsDeletedNoItems = ConfigTaskStep::findAllByGroupId($groupId);
        }

        // 基于 任务配置ID 分组
        $configTaskStepGroupItems = [];
        foreach ($configTaskStepIsDeletedNoItems as $configTaskStepIsDeletedNoItem) {
            $configTaskStepGroupItems[$configTaskStepIsDeletedNoItem->config_task_id][] = [
                'id' => $configTaskStepIsDeletedNoItem->config_step_id,
                'code' => $configTaskStepIsDeletedNoItem->step_code,
                'name' => $configTaskStepIsDeletedNoItem->step_name,
                'sort_order' => $configTaskStepIsDeletedNoItem->sort_order,
                'is_default' => $configTaskStepIsDeletedNoItem->is_default,
            ];
        }

        // 基于租户ID返回数据模型(任务配置)列表
        $configTaskItems = ConfigTaskService::findModelsByGroupId($groupId);

        $data = [];
        foreach ($configTaskItems as $configTaskItem) {
            $data[$configTaskItem->id] = [
                'id' => $configTaskItem->id,
                'code' => $configTaskItem->code,
                'name' => $configTaskItem->name,
                'sort_order' => $configTaskItem->sort_order,
                'steps' => isset($configTaskStepGroupItems[$configTaskItem->id]) ? $configTaskStepGroupItems[$configTaskItem->id] : [],
            ];
        }

        return $data;
    }

12、在 Postman 中 GET:http://api.pcs-api.localhost/v1/config-task-steps/edit/my-group-id ,当租户未自定义时,即 任务配置表、任务步骤配置表 中无当前租户ID的记录时,默认获取 任务配置表、任务步骤配置表 中的租户ID为空的记录,响应数据与执行的 SQL 如下

{
    "code": 10000,
    "message": "编辑租户的任务类型与步骤设置成功",
    "data": {
        "items": [
            {
                "id": 1,
                "code": "tv_broadcast",
                "name": "电视播出",
                "sort_order": 1,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 10,
                        "code": "video_editing",
                        "name": "视频编辑",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 11,
                        "code": "tandem_list",
                        "name": "串联单",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 14,
                        "code": "program_render",
                        "name": "节目上传",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 16,
                        "code": "program_authen",
                        "name": "节目审查",
                        "sort_order": 8,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 2,
                "code": "inter_view",
                "name": "采访任务",
                "sort_order": 2,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 3,
                        "code": "material_review",
                        "name": "素材审核",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 8,
                        "code": "writing_draft",
                        "name": "撰写通稿",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 13,
                        "code": "finish_interview",
                        "name": "结束采访",
                        "sort_order": 7,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 3,
                "code": "rich_doc",
                "name": "互联网发布",
                "sort_order": 3,
                "steps": [
                    {
                        "id": 1,
                        "code": "retrieval",
                        "name": "取稿",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 9,
                        "code": "newspaper_issued",
                        "name": "报纸签发",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 12,
                        "code": "wechat_group_reference",
                        "name": "微信组稿引用",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 15,
                        "code": "website_release",
                        "name": "网站发布",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 17,
                        "code": "app_release",
                        "name": "APP发布",
                        "sort_order": 8,
                        "is_default": 1
                    },
                    {
                        "id": 18,
                        "code": "wechat_release",
                        "name": "微信发布",
                        "sort_order": 9,
                        "is_default": 1
                    },
                    {
                        "id": 19,
                        "code": "blog_release",
                        "name": "微博发布",
                        "sort_order": 10,
                        "is_default": 1
                    },
                    {
                        "id": 20,
                        "code": "qq_release",
                        "name": "企鹅号发布",
                        "sort_order": 11,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 4,
                "code": "other",
                "name": "手动任务",
                "sort_order": 4,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    }
                ]
            }
        ]
    }
}
SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

SELECT * FROM `pa_config_task` WHERE `group_id`=''

SELECT * FROM `pa_config_task` WHERE (`group_id`='') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

13、新建获取步骤配置列表的方法文件,\api\rests\config_step\IndexAction.php

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\config_step;

use Yii;
use api\models\ConfigStep;
use yii\base\InvalidConfigException;
use yii\data\ActiveDataProvider;
use yii\web\UnprocessableEntityHttpException;

/**
 * 获取步骤配置列表:/config-steps(config-step/index)
 *
 * For more details and usage information on IndexAction, see the [guide article on rest controllers](guide:rest-controllers).
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @since 1.0
 */class IndexAction extends \yii\rest\IndexAction
{
    /**
     * Prepares the data provider that should return the requested collection of the models.
     * @return mixed|object|ActiveDataProvider
     * @throws InvalidConfigException if the configuration is invalid.
     * @throws UnprocessableEntityHttpException
     */    protected function prepareDataProvider()
    {
        $requestParams = Yii::$app->getRequest()->getBodyParams();
        if (empty($requestParams)) {
            $requestParams = Yii::$app->getRequest()->getQueryParams();
        }

        $filter = null;
        if ($this->dataFilter !== null) {
            $this->dataFilter = Yii::createObject($this->dataFilter);
            if ($this->dataFilter->load($requestParams)) {
                $filter = $this->dataFilter->build();
                if ($filter === false) {
                    $firstError = '';
                    foreach ($this->dataFilter->getFirstErrors() as $message) {
                        $firstError = $message;
                        break;
                    }
                    throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '224003'), ['first_error' => $firstError])), 224003);
                }
            }
        }

        if ($this->prepareDataProvider !== null) {
            return call_user_func($this->prepareDataProvider, $this, $filter);
        }

        /* @var $modelClass ConfigStep */        $modelClass = $this->modelClass;

        // 查询步骤配置
        $query = $modelClass::find()->isDeletedNo();

        // 设置每页资源数量为资源总数
        $count = $query->count();
        $requestParams['per-page'] = $count;

        return Yii::createObject([
            'class' => ActiveDataProvider::className(),
            'query' => $query,
            'pagination' => [
                'params' => $requestParams,
                'pageSizeLimit' => [1, $count],
            ],
            'sort' => [
                'params' => $requestParams,
            ],
        ]);
    }
}

14、新建获取步骤配置列表的序列化文件,\api\rests\config_step\Serializer.php

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\config_step;

use Yii;
use yii\data\DataProviderInterface;

/**
 * Serializer converts resource objects and collections into array representation.
 *
 * Serializer is mainly used by REST controllers to convert different objects into array representation
 * so that they can be further turned into different formats, such as JSON, XML, by response formatters.
 *
 * The default implementation handles resources as [[Model]] objects and collections as objects
 * implementing [[DataProviderInterface]]. You may override [[serialize()]] to handle more types.
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @since 1.0
 */class Serializer extends \yii\rest\Serializer
{
    /**
     * Serializes a data provider.
     * @param DataProviderInterface $dataProvider
     * @return array the array representation of the data provider.
     */    protected function serializeDataProvider($dataProvider)
    {
        if ($this->preserveKeys) {
            $models = $dataProvider->getModels();
        } else {
            $models = array_values($dataProvider->getModels());
        }
        $models = $this->serializeModels($models);

        if (($pagination = $dataProvider->getPagination()) !== false) {
            $this->addPaginationHeaders($pagination);
        }

        if ($this->request->getIsHead()) {
            return null;
        } elseif ($this->collectionEnvelope === null) {
            return $models;
        }

        $result = [
            $this->collectionEnvelope => $models,
        ];

        if ($pagination !== false) {
            return ['code' => 10000, 'message' => Yii::t('success', '126040'), 'data' => array_merge($result, $this->serializePagination($pagination))];
        }

        return ['code' => 10000, 'message' => Yii::t('success', '126040'), 'data' => $result];
    }
}

15、在 Postman 中 GET:http://api.pcs-api.localhost/v1/config-steps ,获取步骤配置列表,以便于用户选择步骤添加至对应的类型中,响应数据与执行的 SQL 如下

{
    "code": 10000,
    "message": "获取步骤配置列表成功",
    "data": {
        "items": [
            {
                "id": 1,
                "group_id": "",
                "code": "retrieval",
                "name": "取稿",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 2,
                "group_id": "",
                "code": "begin_shot",
                "name": "上传素材",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 3,
                "group_id": "",
                "code": "material_review",
                "name": "素材审核",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 4,
                "group_id": "",
                "code": "writing",
                "name": "撰写稿件",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 7,
                "group_id": "",
                "code": "manuscript_review",
                "name": "稿件审核",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 8,
                "group_id": "",
                "code": "writing_draft",
                "name": "撰写通稿",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 9,
                "group_id": "",
                "code": "newspaper_issued",
                "name": "报纸签发",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 10,
                "group_id": "",
                "code": "video_editing",
                "name": "视频编辑",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 11,
                "group_id": "",
                "code": "tandem_list",
                "name": "串联单",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 12,
                "group_id": "",
                "code": "wechat_group_reference",
                "name": "微信组稿引用",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 13,
                "group_id": "",
                "code": "finish_interview",
                "name": "结束采访",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 14,
                "group_id": "",
                "code": "program_render",
                "name": "节目上传",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 15,
                "group_id": "",
                "code": "website_release",
                "name": "网站发布",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 16,
                "group_id": "",
                "code": "program_authen",
                "name": "节目审查",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 17,
                "group_id": "",
                "code": "app_release",
                "name": "APP发布",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 18,
                "group_id": "",
                "code": "wechat_release",
                "name": "微信发布",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 19,
                "group_id": "",
                "code": "blog_release",
                "name": "微博发布",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            },
            {
                "id": 20,
                "group_id": "",
                "code": "qq_release",
                "name": "企鹅号发布",
                "category": 1,
                "status": 1,
                "is_not_isolated": 0,
                "is_deleted": 0,
                "created_at": 1573439819,
                "updated_at": 1573439819,
                "deleted_at": 0
            }
        ],
        "_links": {
            "self": {
                "href": "http://api.pcs-api.localhost/v1/config-steps?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=549e02e6b704dbb437618029a711f7c8&per-page=18&page=1"
            }
        },
        "_meta": {
            "totalCount": 18,
            "pageCount": 1,
            "currentPage": 1,
            "perPage": 18
        }
    }
}
SELECT COUNT(*) FROM `pa_config_step` WHERE `is_deleted`=0

SELECT COUNT(*) FROM `pa_config_step` WHERE `is_deleted`=0

SELECT * FROM `pa_config_step` WHERE `is_deleted`=0 LIMIT 18

16、新建更新任务类型与步骤设置的方法文件(\api\rests\config_task_step\UpdateAction.php)

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\config_task_step;

use Yii;
use api\models\ConfigTask;
use api\models\ConfigTaskStep;
use api\models\redis\cmc_console\User as RedisCmcConsoleUser;
use api\services\ConfigTaskStepService;
use yii\base\Model;
use yii\base\InvalidConfigException;
use yii\helpers\ArrayHelper;
use yii\web\UnprocessableEntityHttpException;
use yii\web\ServerErrorHttpException;

/**
 * 更新租户的任务类型与步骤设置:/config-task-steps/{group_id}(config-task-step/update)
 *
 * For more details and usage information on CreateAction, see the [guide article on rest controllers](guide:rest-controllers).
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @since 1.0
 */class UpdateAction extends Action
{
    /**
     * @var string the scenario to be assigned to the model before it is validated and updated.
     * $id {config_column_id}
     */    public $scenario = Model::SCENARIO_DEFAULT;

    /**
     * Updates an existing model.
     * @param string $id the primary key of the model.
     * @return array the model being updated
     * @throws InvalidConfigException if a registered parser does not implement the [[RequestParserInterface]].
     * @throws ServerErrorHttpException if there is any error when updating the model
     * @throws \Throwable
     */    public function run($id)
    {
        // 当前用户的身份实例,未认证用户则为 Null
        /* @var $identity RedisCmcConsoleUser */        $identity = Yii::$app->user->identity;

        // 比对:group_id,检查其值是否等于:my-group-id
        if ($id != 'my-group-id') {
            throw new UnprocessableEntityHttpException(Yii::t('error', '226053'), 226053);
        }

        /* @var $modelClass ConfigTaskStep */        $modelClass = $this->modelClass;

        $requestParams = Yii::$app->getRequest()->getBodyParams();

        /* @var $configGroupTaskItem ConfigTask */        // 基于租户ID查找资源(任务配置、是否被删除:否)列表
        $configTaskIsDeletedNoItems = ConfigTask::find()->where(['group_id' => $identity->group_id])->isDeletedNo()->indexBy(['code'])->all();
        // 基于租户ID查找资源(任务配置)列表
        $configTaskItems = ConfigTask::find()->where(['group_id' => $identity->group_id])->indexBy(['code'])->all();

        /* @var $configGroupTaskStepItem ConfigTaskStep */        // 基于租户ID查找资源(任务步骤配置、是否被删除:否)列表
        $configTaskStepIsDeletedNoItems = $modelClass::find()->where(['group_id' => $identity->group_id])->isDeletedNo()->indexBy(function ($row) { return $row['config_task_code'] . ':' . $row['step_code']; })->all();
        // 基于租户ID查找资源(任务步骤配置)列表
        $configTaskStepItems = $modelClass::find()->where(['group_id' => $identity->group_id])->indexBy(function ($row) { return $row['config_task_code'] . ':' . $row['step_code']; })->all();

        if (!is_array($requestParams)) {
            return ['code' => 226010, 'message' => Yii::t('error', '226010')];
        }

        $configTasks = [];
        $configTaskSteps = [];
        foreach ($requestParams as $configTask) {
            // 检查 任务配置的(代码、名称、顺序) 是否存在
            if (!ArrayHelper::keyExists('code', $configTask) || !ArrayHelper::keyExists('name', $configTask) || !ArrayHelper::keyExists('sort_order', $configTask)) {
                throw new UnprocessableEntityHttpException(Yii::t('error', '226080'), 226080);
            }

            $configTasks[$configTask['code']] = [
                'group_id' => $identity->group_id,
                'code' => $configTask['code'],
                'name' => $configTask['name'],
                'sort_order' => $configTask['sort_order'],
            ];

            // 检查 步骤配置列表 是否存在
            if (ArrayHelper::keyExists('steps', $configTask)) {
                foreach ($configTask['steps'] as $configTaskStep) {
                    // 检查 步骤代码、顺序、是否默认步骤 是否存在
                    if (!ArrayHelper::keyExists('code', $configTaskStep) || !ArrayHelper::keyExists('sort_order', $configTaskStep) || !ArrayHelper::keyExists('is_default', $configTaskStep)) {
                        throw new UnprocessableEntityHttpException(Yii::t('error', '226080'), 226080);
                    }

                    $configTaskSteps[$configTask['code'] . ':' . $configTaskStep['code']] = [
                        'group_id' => $identity->group_id,
                        'config_task_code' => $configTask['code'],
                        'step_code' => $configTaskStep['code'],
                        'sort_order' => $configTaskStep['sort_order'],
                        'is_default' => $configTaskStep['is_default'],
                    ];
                }

            }
        }

        /* 任务配置 */        // 使用键名比较计算数组的差集,如果不为空,则删除 (软删除) 出现在 任务配置模型(MySQL) 中但是未出现在 任务配置 中的记录
        $configTaskDiffItems = array_diff_key($configTaskIsDeletedNoItems, $configTasks);

        $configTasks = array_values($configTasks);

        $configTaskModels = [];

        foreach ($configTasks as $configTask) {
            if (isset($configTaskItems[$configTask['code']])) {
                $configTaskItems[$configTask['code']]->scenario = ConfigTask::SCENARIO_UPDATE;
                $configTaskModels[] = $configTaskItems[$configTask['code']];
            } else {
                $configTaskModels[] = new ConfigTask([
                    'scenario' => ConfigTask::SCENARIO_UPDATE,
                ]);
            }
        }

        // 批量填充模型属性
        if (!empty($configTaskModels) && !Model::loadMultiple($configTaskModels, $configTasks, '')) {
            return ['code' => 226010, 'message' => Yii::t('error', '226010')];
        }

        // 批量验证模型
        if (!empty($configTaskModels) && !Model::validateMultiple($configTaskModels)) {
            $configTaskModelsResult = self::handleValidateMultipleError($configTaskModels);
            if ($configTaskModelsResult['status'] === false) {
                return ['code' => $configTaskModelsResult['code'], 'message' => $configTaskModelsResult['message']];
            }
        }

        /* 任务步骤配置 */        // 使用键名比较计算数组的差集,如果不为空,则删除 (软删除) 出现在 任务步骤配置模型(MySQL) 中但是未出现在 任务步骤配置 中的记录
        $configTaskStepDiffItems = array_diff_key($configTaskStepIsDeletedNoItems, $configTaskSteps);

        $configTaskSteps = array_values($configTaskSteps);

        $configTaskStepModels = [];

        foreach ($configTaskSteps as $configTaskStep) {
            $index = $configTaskStep['config_task_code'] . ':' . $configTaskStep['step_code'];
            if (isset($configTaskStepItems[$index])) {
                $configTaskStepItems[$index]->scenario = ConfigTask::SCENARIO_UPDATE;
                $configTaskStepModels[] = $configTaskStepItems[$index];
            } else {
                $configTaskStepModels[] = new $modelClass([
                    'scenario' => $modelClass::SCENARIO_UPDATE,
                ]);
            }
        }

        // 批量填充模型属性
        if (!empty($configTaskStepModels) && !Model::loadMultiple($configTaskStepModels, $configTaskSteps, '')) {
            return ['code' => 226010, 'message' => Yii::t('error', '226010')];
        }

        // 批量验证模型
        if (!empty($configTaskStepModels) && !Model::validateMultiple($configTaskStepModels)) {
            $configTaskStepModelsResult = self::handleValidateMultipleError($configTaskStepModels);
            if ($configTaskStepModelsResult['status'] === false) {
                return ['code' => $configTaskStepModelsResult['code'], 'message' => $configTaskStepModelsResult['message']];
            }
        }

        /* 操作数据(事务) */
        $configTaskStepService = new ConfigTaskStepService();

        $result = $configTaskStepService->update($configTaskDiffItems, $configTaskModels, $configTaskStepDiffItems, $configTaskStepModels, $identity);
        if ($result['status'] === false) {
            throw new ServerErrorHttpException($result['message'], $result['code']);
        }

        return ['code' => 10000, 'message' => Yii::t('success', '126039')];
    }
}

17、新建更新任务类型与步骤设置的服务文件(\api\services\ConfigTaskService.php)

<?php
/**
 * Created by PhpStorm.
 * User: Qiang Wang
 * Date: 2019/11/01
 * Time: 10:17
 */
namespace api\services;

use Yii;
use api\models\ConfigTask;
use yii\web\ServerErrorHttpException;

class ConfigTaskService extends \common\services\ConfigTaskService
{
    /**
     * 创建任务配置
     * @param object $model 对象
     *
     * @param bool $runValidation 保存记录之前是否执行验证 (调用 [[validate()]]),默认为 true
     *
     * @return array
     * 格式如下:
     *
     * [
     *     'status' => true, // 成功
     *     'data' => [ // object
     *     ]
     * ]
     *
     * [
     *     'status' => false, // 失败
     *     'code' => 226081, // 返回码
     *     'message' => '', // 说明
     * ]
     *
     * @throws ServerErrorHttpException
     */    public function create($model, $runValidation = true)
    {
        if ($model->save($runValidation)) {
            return ['status' => true, 'data' => $model];
        } elseif ($model->hasErrors()) {
            $firstError = '';
            foreach ($model->getFirstErrors() as $message) {
                $firstError = $message;
                break;
            }
            return ['status' => false, 'code' => 226081, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '226081'), ['first_error' => $firstError]))];
        } elseif (!$model->hasErrors()) {
            throw new ServerErrorHttpException('Failed to create the object (task configuration) for unknown reason.');
        }
    }

    /**
     * 更新任务配置
     * @param object $model 对象
     *
     * @param bool $runValidation 保存记录之前是否执行验证 (调用 [[validate()]]),默认为 true
     *
     * @return array
     * 格式如下:
     *
     * [
     *     'status' => true, // 成功
     *     'data' => [ // object
     *     ]
     * ]
     *
     * [
     *     'status' => false, // 失败
     *     'code' => 226082, // 返回码
     *     'message' => '', // 说明
     * ]
     *
     * @throws ServerErrorHttpException
     */    public function update($model, $runValidation = true)
    {
        if ($model->save($runValidation)) {
            return ['status' => true, 'data' => $model];
        } elseif ($model->hasErrors()) {
            $firstError = '';
            foreach ($model->getFirstErrors() as $message) {
                $firstError = $message;
                break;
            }
            return ['status' => false, 'code' => 226082, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '226082'), ['first_error' => $firstError]))];
        } elseif (!$model->hasErrors()) {
            throw new ServerErrorHttpException('Failed to update the object (task configuration) for unknown reason.');
        }
    }

    /**
     * 批量删除(删除任务配置)
     *
     * @param $models
     *
     * @return bool the saved model or false if saving fails
     * @throws \Throwable
     */    public function deleteMultiple($models)
    {
        $transaction = Yii::$app->db->beginTransaction();
        try {
            foreach ($models as $model) {
                /* @var $model ConfigTask */                if($model->softDelete() === false) {
                    throw new ServerErrorHttpException(Yii::t('error', '226083'), 226083);
                }
            }
            $transaction->commit();

            return true;
        } catch (\Throwable $e) {
            $transaction->rollBack();
            throw $e;
        }
    }
}

18、新建更新任务类型与步骤设置的服务文件(\api\services\ConfigTaskStepService.php)

<?php
/**
 * Created by PhpStorm.
 * User: terryhong
 * Date: 2018/12/20
 * Time: 3:09 PM
 */
namespace api\services;

use Yii;
use api\models\ConfigTask;
use api\models\ConfigStep;
use api\models\ConfigTaskStep;
use yii\web\ServerErrorHttpException;

class ConfigTaskStepService extends \common\services\ConfigTaskStepService
{
    /**
     * 创建任务步骤配置
     * @param object $model 对象
     *
     * @param bool $runValidation 保存记录之前是否执行验证 (调用 [[validate()]]),默认为 true
     *
     * @return array
     * 格式如下:
     *
     * [
     *     'status' => true, // 成功
     *     'data' => [ // object
     *     ]
     * ]
     *
     * [
     *     'status' => false, // 失败
     *     'code' => 226084, // 返回码
     *     'message' => '', // 说明
     * ]
     *
     * @throws ServerErrorHttpException
     */    public function create($model, $runValidation = true)
    {
        if ($model->save($runValidation)) {
            return ['status' => true, 'data' => $model];
        } elseif ($model->hasErrors()) {
            $firstError = '';
            foreach ($model->getFirstErrors() as $message) {
                $firstError = $message;
                break;
            }
            return ['status' => false, 'code' => 226084, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '226084'), ['first_error' => $firstError]))];
        } elseif (!$model->hasErrors()) {
            throw new ServerErrorHttpException('Failed to create the object (task step configuration) for unknown reason.');
        }
    }

    /**
     * 添加|还原|更新|删除任务配置、添加|还原|更新|删除任务步骤配置
     *
     * @param array $deletedConfigTasks 需要删除的任务配置
     * 格式如下:
     * [
     *     [ // object
     *         'id' => 32, // ID
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'code' => 'other', // 代码
     *         'name' => '手动任务', // 名称
     *         'sort_order' => 1, // 顺序
     *         'category' => 2, // 任务类型,1:系统内置;2:用户定义
     *         'parameter' => 's:0:"";', // 自定义参数,序列化存储
     *         'status' => 1, // 状态,0:禁用;1:启用
     *         'is_deleted' => 0, // 是否被删除,0:否;1:是
     *         'created_at' => 1573632826, // 创建时间
     *         'updated_at' => 1573632826, // 更新时间
     *         'deleted_at' => 0, // 删除时间
     *     ],
     *     ...
     * ]
     *
     * @param array $configTasks 任务配置
     * 格式如下:
     * [
     *     [ // object
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'code' => 'tv_broadcast', // 代码
     *         'name' => '电视播出', // 名称
     *         'sort_order' => 1, // 顺序
     *     ],
     *     [ // object
     *         'id' => 29, // ID
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'code' => 'tv_broadcast', // 代码
     *         'name' => '电视播出', // 名称
     *         'sort_order' => 1, // 顺序
     *         'category' => 2, // 任务类型,1:系统内置;2:用户定义
     *         'parameter' => 's:0:"";', // 自定义参数,序列化存储
     *         'status' => 1, // 状态,0:禁用;1:启用
     *         'is_deleted' => 0, // 是否被删除,0:否;1:是
     *         'created_at' => 1573632826, // 创建时间
     *         'updated_at' => 1573632826, // 更新时间
     *         'deleted_at' => 0, // 删除时间
     *     ],
     *     ...
     * ]
     *
     * @param array $deletedConfigTaskSteps 需要删除的任务步骤配置
     * 格式如下:
     * [
     *     [ // object
     *         'id' => 145, // ID
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'config_task_id' => 32, // 任务配置ID
     *         'config_task_code' => 'other', // 任务配置代码
     *         'config_step_id' => 2, // 步骤配置ID
     *         'step_code' => 'begin_shot', // 步骤配置代码
     *         'step_name' => '上传素材', // 步骤配置名称
     *         'sort_order' => 1, // 步骤顺序,顺序排列
     *         'is_default' => 1, // 是否默认步骤,1:是;0:否
     *         'up_status_type' => 2, // 状态更新方式,1:接口;2:人工
     *         'status' => 1, // 状态,0:禁用;1:启用
     *         'is_deleted' => 0, // 是否被删除,0:否;1:是
     *         'created_at' => 1573632826, // 创建时间
     *         'updated_at' => 1573632826, // 更新时间
     *         'deleted_at' => 0, // 删除时间
     *     ],
     *     ...
     * ]
     *
     *
     * @param array $configTaskSteps 任务步骤配置
     * 格式如下:
     * [
     *     [ // object
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'config_task_code' => 'tv_broadcast', // 任务配置代码
     *         'step_code' => 'begin_shot', // 步骤代码
     *         'sort_order' => 1, // 顺序
     *         'is_default' => 1, // 是否默认步骤,1:是;0:否
     *     ],
     *     [ // object
     *         'id' => 122, // ID
     *         'group_id' => '015ce30b116ce86058fa6ab4fea4ac63', // 租户ID
     *         'config_task_id' => 29, // 任务配置ID
     *         'config_task_code' => 'tv_broadcast', // 任务配置代码
     *         'config_step_id' => 2, // 步骤配置ID
     *         'step_code' => 'begin_shot', // 步骤配置代码
     *         'step_name' => '上传素材', // 步骤配置名称
     *         'sort_order' => 1, // 步骤顺序,顺序排列
     *         'is_default' => 1, // 是否默认步骤,1:是;0:否
     *         'up_status_type' => 2, // 状态更新方式,1:接口;2:人工
     *         'status' => 1, // 状态,0:禁用;1:启用
     *         'is_deleted' => 0, // 是否被删除,0:否;1:是
     *         'created_at' => 1573632826, // 创建时间
     *         'updated_at' => 1573632826, // 更新时间
     *         'deleted_at' => 0, // 删除时间
     *     ],
     *     ...
     * ]
     *
     * @param object $identity 当前用户的身份实例
     *
     * @return array
     * 格式如下:
     *
     * [
     *     'status' => true, // 成功
     * ]
     *
     * @throws \Throwable
     */    public function update($deletedConfigTasks, $configTasks, $deletedConfigTaskSteps, $configTaskSteps, $identity)
    {

        /* 操作数据(事务) */        $transaction = Yii::$app->db->beginTransaction();
        try {

            $configTaskService = new ConfigTaskService();
            // 使用键名比较计算数组的差集,如果不为空,则删除 (软删除) 出现在 任务配置模型(MySQL) 中但是未出现在 任务配置列表 中的记录
            $configTaskService->deleteMultiple($deletedConfigTasks);

            // 遍历模型数组,判断在 任务配置 中是否存在,如果不存在则插入,如果存在则更新(已删除则先还原)
            foreach ($configTasks as $configTask) {
                $configTaskItem = ConfigTask::find()->where(['group_id' => $identity->group_id, 'code' => $configTask->code])->one();

                /* @var $model ConfigTask */                if (!isset($configTaskItem)) {

                    $configTask->category = $configTask::CATEGORY_CUSTOMIZE;
                    $configTask->parameter = serialize('');
                    $configTask->status = $configTask::STATUS_ENABLED;
                    $configTask->is_deleted = $configTask::IS_DELETED_NO;
                    $configTask->deleted_at = $configTask::DELETED_AT_DEFAULT;
                    $configTaskServiceCreateResult = $configTaskService->create($configTask, false);
                    if ($configTaskServiceCreateResult['status'] === false) {
                        throw new ServerErrorHttpException($configTaskServiceCreateResult['message'], $configTaskServiceCreateResult['code']);
                    }

                } else if (isset($configTaskItem)) {

                    if ($configTaskItem->is_deleted == ConfigTask::IS_DELETED_YES && $configTaskItem->restore() === false) {
                        throw new ServerErrorHttpException('Failed to restore the object (task configuration) for unknown reason.');
                    }

                    $configTaskItem->name = $configTask->name;
                    $configTaskItem->sort_order = $configTask->sort_order;
                    $configTaskItemServiceUpdateResult = $configTaskService->update($configTaskItem, false);
                    if ($configTaskItemServiceUpdateResult['status'] === false) {
                        throw new ServerErrorHttpException($configTaskItemServiceUpdateResult['message'], $configTaskItemServiceUpdateResult['code']);
                    }

                }
            }

            // 使用键名比较计算数组的差集,如果不为空,则删除 (软删除) 出现在 任务步骤配置模型(MySQL) 中但是未出现在 任务步骤配置列表 中的记录
            $this->deleteMultiple($deletedConfigTaskSteps);

            $configTaskItems = ConfigTask::find()->where(['group_id' => $identity->group_id])->indexBy(['code'])->all();
            $configStepItems = ConfigStep::find()->isDeletedNo()->indexBy(['code'])->all();

            // 遍历模型数组,判断在 任务步骤配置 中是否存在,如果不存在则插入,如果存在则更新(已删除则先还原)
            foreach ($configTaskSteps as $configTaskStep) {
                $configTaskStepItem = ConfigTaskStep::find()->where(['group_id' => $identity->group_id, 'config_task_code' => $configTaskStep->config_task_code, 'step_code' => $configTaskStep->step_code])->one();

                /* @var $model ConfigTaskStep */                if (!isset($configTaskStepItem)) {

                    $configTaskStep->config_task_id = $configTaskItems[$configTaskStep->config_task_code]->id;
                    $configTaskStep->config_step_id = $configStepItems[$configTaskStep->step_code]->id;
                    $configTaskStep->step_name = $configStepItems[$configTaskStep->step_code]->name;
                    $configTaskStep->status = $configTaskStep::STATUS_ENABLED;
                    $configTaskStep->is_deleted = $configTaskStep::IS_DELETED_NO;
                    $configTaskStep->deleted_at = $configTaskStep::DELETED_AT_DEFAULT;
                    $thisCreateResult = $this->create($configTaskStep, false);
                    if ($thisCreateResult['status'] === false) {
                        throw new ServerErrorHttpException($thisCreateResult['message'], $thisCreateResult['code']);
                    }

                } else if (isset($configTaskStepItem)) {

                    if ($configTaskStepItem->is_deleted == ConfigTaskStep::IS_DELETED_YES && $configTaskStepItem->restore() === false) {
                        throw new ServerErrorHttpException('Failed to restore the object (task step configuration) for unknown reason.');
                    }

                    $configTaskStepItem->sort_order = $configTaskStep->sort_order;
                    $configTaskStepItem->is_default = $configTaskStep->is_default;
                    if ($configTaskStepItem->save(false)) {
                        // return ['status' => true, 'data' => $configTaskStepItem];
                    } elseif ($configTaskStepItem->hasErrors()) {
                        $firstError = '';
                        foreach ($configTaskStepItem->getFirstErrors() as $message) {
                            $firstError = $message;
                            break;
                        }
                        throw new ServerErrorHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226085'), ['first_error' => $firstError])), 226085);
                    } elseif (!$configTaskStepItem->hasErrors()) {
                        throw new ServerErrorHttpException('Failed to update the object (task step configuration) for unknown reason.');
                    }

                }
            }

            $transaction->commit();
        } catch(\Throwable $e) {
            $transaction->rollBack();
            throw $e;
        }

        return ['status' => true];
    }

    /**
     * 批量删除(删除任务步骤配置)
     *
     * @param $models
     *
     * @return bool the saved model or false if saving fails
     * @throws \Throwable
     */    public function deleteMultiple($models)
    {
        $transaction = Yii::$app->db->beginTransaction();
        try {
            foreach ($models as $model) {
                if($model->softDelete() === false) {
                    throw new ServerErrorHttpException(Yii::t('error', '226086'), 226086);
                }
            }
            $transaction->commit();

            return true;
        } catch (\Throwable $e) {
            $transaction->rollBack();
            throw $e;
        }
    }
}

19、编辑更新任务设置的模型文件(\api\models\ConfigTask.php)

<?php

namespace api\models;

use yii\helpers\ArrayHelper;

class ConfigTask extends \common\logics\ConfigTask
{
    const SCENARIO_UPDATE = 'update';

    public function scenarios()
    {
        $scenarios = parent::scenarios();
        $scenarios[self::SCENARIO_UPDATE] = ['group_id', 'code', 'name', 'sort_order', 'is_deleted', 'deleted_at'];

        return $scenarios;
    }

    /**
     * @inheritdoc
     */    public function rules()
    {
        $rules = [
        ];
        $parentRules = parent::rules();

        return ArrayHelper::merge($rules, $parentRules);
    }

    /**
     * {@inheritdoc}
     * @return ConfigTaskQuery the active query used by this AR class.
     */    public static function find()
    {
        return new ConfigTaskQuery(get_called_class());
    }
}

20、编辑更新任务步骤设置的模型文件(\api\models\ConfigTaskStep.php)

<?php

namespace api\models;

use yii\helpers\ArrayHelper;

class ConfigTaskStep extends \common\logics\ConfigTaskStep
{
    const SCENARIO_UPDATE = 'update';

    public function scenarios()
    {
        $scenarios = parent::scenarios();
        $scenarios[self::SCENARIO_UPDATE] = ['group_id', 'config_task_code', 'step_code', 'sort_order', 'is_default', 'is_deleted', 'deleted_at'];

        return $scenarios;
    }

    /**
     * @inheritdoc
     */    public function rules()
    {
        $rules = [
            /* 添加、更新任务步骤配置 */            ['step_code', 'exist', 'targetClass' => '\api\models\ConfigStep', 'targetAttribute' => 'code', 'filter' => ['is_deleted' => ConfigStep::IS_DELETED_NO], 'on' => self::SCENARIO_UPDATE],
            [['is_default'], 'in', 'range' => [static::IS_DEFAULT_NO, static::IS_DEFAULT_YES], 'on' => self::SCENARIO_UPDATE],
        ];
        $parentRules = parent::rules();

        return ArrayHelper::merge($rules, $parentRules);
    }

    /**
     * {@inheritdoc}
     * @return ConfigTaskStepQuery the active query used by this AR class.
     */    public static function find()
    {
        return new ConfigTaskStepQuery(get_called_class());
    }
}

21、在 Postman 中 PUT:http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ,请求数据(null)与执行的 SQL (未插入数据) 如下,如图4

图4

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63')

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63')

Begin transaction

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

Commit transaction

22、在 Postman 中 PUT:http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ,请求数据([])与执行的 SQL (未插入数据) 如下,如图5

图5

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63')

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63')

Begin transaction

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

Commit transaction

23、在 Postman 中 PUT:http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ,请求数据(未自定义时,编辑接口的响应数据)与执行的 SQL (分别插入 4 、24 条记录) 如下,打开表,如图6

图6

[
    {
        "code": "tv_broadcast",
        "name": "电视播出",
        "sort_order": 1,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 4,
                "is_default": 1
            },
            {
                "code": "video_editing",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "tandem_list",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "program_render",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "program_authen",
                "sort_order": 8,
                "is_default": 1
            }
        ]
    },
    {
        "code": "inter_view",
        "name": "采访任务",
        "sort_order": 2,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "material_review",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 4,
                "is_default": 1
            },
            {
                "code": "writing_draft",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "finish_interview",
                "sort_order": 7,
                "is_default": 1
            }
        ]
    },
    {
        "code": "rich_doc",
        "name": "互联网发布",
        "sort_order": 3,
        "steps": [
            {
                "code": "retrieval",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 4,
                "is_default": 1
            },
            {
                "code": "newspaper_issued",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "wechat_group_reference",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "website_release",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "app_release",
                "sort_order": 8,
                "is_default": 1
            },
            {
                "code": "wechat_release",
                "sort_order": 9,
                "is_default": 1
            },
            {
                "code": "blog_release",
                "sort_order": 10,
                "is_default": 1
            },
            {
                "code": "qq_release",
                "sort_order": 11,
                "is_default": 1
            }
        ]
    },
    {
        "code": "other",
        "name": "手动任务",
        "sort_order": 4,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            }
        ]
    }
]
SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='tv_broadcast') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0)) // 总计 4 次执行

SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='inter_view') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0)) // 总计 4 次执行

SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='rich_doc') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0)) // 总计 4 次执行

SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='other') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0)) // 总计 4 次执行

SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`='begin_shot') AND (`is_deleted`=0)) // 总计 24 次执行【SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`=】

SELECT EXISTS(SELECT * FROM `pa_config_task_step` WHERE (`pa_config_task_step`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task_step`.`config_task_code`='tv_broadcast') AND (`pa_config_task_step`.`step_code`='begin_shot') AND (`pa_config_task_step`.`is_deleted`=0) AND (`pa_config_task_step`.`deleted_at`=0)) // 总计 120 次执行【SELECT EXISTS(SELECT * FROM `pa_config_task_step` WHERE (`pa_config_task_step`.`group_id`】

Begin transaction

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`code`='tv_broadcast') // 总计 4 次执行【SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`code`=】

INSERT INTO `pa_config_task` (`group_id`, `code`, `name`, `sort_order`, `is_deleted`, `deleted_at`, `category`, `parameter`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'tv_broadcast', '电视播出', 1, 0, 0, 2, 's:0:\"\";', 1, 1573626589, 1573626589) // 总计 4 次执行【SELECT * FROM `pa_config_task` WHERE (INSERT INTO `pa_config_task` (`group_id`, `code`, `name`, `sort_order`, `is_deleted`, `deleted_at`, `category`, `parameter`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63'】

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_step` WHERE `is_deleted`=0

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`config_task_code`='tv_broadcast') AND (`step_code`='begin_shot') // 总计 24 次执行【SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`config_task_code`=】

INSERT INTO `pa_config_task_step` (`group_id`, `config_task_code`, `step_code`, `sort_order`, `is_default`, `is_deleted`, `deleted_at`, `config_task_id`, `config_step_id`, `step_name`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'tv_broadcast', 'begin_shot', 1, 1, 0, 0, 17, 2, '上传素材', 1, 1573626589, 1573626589) // 总计 24 次执行【INSERT INTO `pa_config_task_step` (`group_id`, `config_task_code`, `step_code`, `sort_order`, `is_default`, `is_deleted`, `deleted_at`, `config_task_id`, `config_step_id`, `step_name`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63'】

Commit transaction

24、在 Postman 中 GET:http://api.pcs-api.localhost/v1/config-task-steps/edit/my-group-id ,当租户已自定义时,即 任务配置表、任务步骤配置表 中有当前租户的记录时,默认获取 任务配置表、任务步骤配置表 中的当前租户ID的记录,响应数据与执行的 SQL 如下

{
    "code": 10000,
    "message": "编辑租户的任务类型与步骤设置成功",
    "data": {
        "items": [
            {
                "id": 17,
                "code": "tv_broadcast",
                "name": "电视播出",
                "sort_order": 1,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 10,
                        "code": "video_editing",
                        "name": "视频编辑",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 11,
                        "code": "tandem_list",
                        "name": "串联单",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 14,
                        "code": "program_render",
                        "name": "节目上传",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 16,
                        "code": "program_authen",
                        "name": "节目审查",
                        "sort_order": 8,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 18,
                "code": "inter_view",
                "name": "采访任务",
                "sort_order": 2,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 3,
                        "code": "material_review",
                        "name": "素材审核",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 8,
                        "code": "writing_draft",
                        "name": "撰写通稿",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 13,
                        "code": "finish_interview",
                        "name": "结束采访",
                        "sort_order": 7,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 19,
                "code": "rich_doc",
                "name": "互联网发布",
                "sort_order": 3,
                "steps": [
                    {
                        "id": 1,
                        "code": "retrieval",
                        "name": "取稿",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 9,
                        "code": "newspaper_issued",
                        "name": "报纸签发",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 12,
                        "code": "wechat_group_reference",
                        "name": "微信组稿引用",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 15,
                        "code": "website_release",
                        "name": "网站发布",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 17,
                        "code": "app_release",
                        "name": "APP发布",
                        "sort_order": 8,
                        "is_default": 1
                    },
                    {
                        "id": 18,
                        "code": "wechat_release",
                        "name": "微信发布",
                        "sort_order": 9,
                        "is_default": 1
                    },
                    {
                        "id": 19,
                        "code": "blog_release",
                        "name": "微博发布",
                        "sort_order": 10,
                        "is_default": 1
                    },
                    {
                        "id": 20,
                        "code": "qq_release",
                        "name": "企鹅号发布",
                        "sort_order": 11,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 20,
                "code": "other",
                "name": "手动任务",
                "sort_order": 4,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    }
                ]
            }
        ]
    }
}
SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

25、在 Postman 中 PUT:http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ,请求数据(删除任务配置(采访任务)中的 6 项步骤配置;删除 1 项任务配置(手动任务)及其中的 1 项步骤配置;在 互联网发布 后添加 1 项任务配置(自定义一)及在其中添加 0 项步骤配置;在 自定义一 后添加 1 项任务配置(自定义二)及在其中添加 2 项步骤配置;更新 1 项任务配置(互联网发布)及其中的 1 项步骤配置)与执行的 SQL ( 任务配置表:2 条插入语句、2 条更新语句;任务步骤配置表:2 条插入语句、8 条更新语句) 如下,打开表,如图7

图7

[
    {
        "code": "tv_broadcast",
        "name": "电视播出",
        "sort_order": 1,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 4,
                "is_default": 1
            },
            {
                "code": "video_editing",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "tandem_list",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "program_render",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "program_authen",
                "sort_order": 8,
                "is_default": 1
            }
        ]
    },
    {
        "code": "inter_view",
        "name": "采访任务",
        "sort_order": 2,
        "steps": []
    },
    {
        "code": "rich_doc",
        "name": "互联网发布更新",
        "sort_order": 4,
        "steps": [
            {
                "code": "retrieval",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 3,
                "is_default": 1
            },
            {
                "code": "newspaper_issued",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "wechat_group_reference",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "website_release",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "app_release",
                "sort_order": 8,
                "is_default": 1
            },
            {
                "code": "wechat_release",
                "sort_order": 9,
                "is_default": 1
            },
            {
                "code": "blog_release",
                "sort_order": 10,
                "is_default": 1
            },
            {
                "code": "qq_release",
                "sort_order": 11,
                "is_default": 1
            }
        ]
    },
    {
        "code": "zdy1",
        "name": "自定义一",
        "sort_order": 5,
        "steps": []
    },
    {
        "code": "zdy2",
        "name": "自定义二",
        "sort_order": 6,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "program_render",
                "sort_order": 7,
                "is_default": 1
            }
        ]
    }
]
SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)

SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT `pa_config_task`.`id` FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='tv_broadcast') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0) LIMIT 2 // 总计 12 次执行【SELECT `pa_config_task`.`id` FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`=】

SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='zdy1') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0)) // 总计 8 次执行【SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`=】

SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`='begin_shot') AND (`is_deleted`=0)) // 总计 19 次执行【SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`=】

SELECT `pa_config_task_step`.`id` FROM `pa_config_task_step` WHERE (`pa_config_task_step`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task_step`.`config_task_code`='tv_broadcast') AND (`pa_config_task_step`.`step_code`='begin_shot') AND (`pa_config_task_step`.`is_deleted`=0) AND (`pa_config_task_step`.`deleted_at`=0) LIMIT 2 // 总计 85 次执行【SELECT `pa_config_task_step`.`id` FROM `pa_config_task_step` WHERE (`pa_config_task_step`.`group_id`=】

SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`='writing') AND (`is_deleted`=0)) // 总计 19 次执行【SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`=】

Begin transaction

UPDATE `pa_config_task` SET `is_deleted`=1, `deleted_at`=1573630064 WHERE `id`=20

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`code`='tv_broadcast') // 总计 5 次执行【SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`code`=】

UPDATE `pa_config_task` SET `name`='互联网发布更新', `sort_order`=4, `updated_at`=1573630064 WHERE `id`=19

INSERT INTO `pa_config_task` (`group_id`, `code`, `name`, `sort_order`, `is_deleted`, `deleted_at`, `category`, `parameter`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'zdy1', '自定义一', 5, 0, 0, 2, 's:0:\"\";', 1, 1573630064, 1573630064)

INSERT INTO `pa_config_task` (`group_id`, `code`, `name`, `sort_order`, `is_deleted`, `deleted_at`, `category`, `parameter`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'zdy2', '自定义二', 6, 0, 0, 2, 's:0:\"\";', 1, 1573630064, 1573630064)

UPDATE `pa_config_task_step` SET `is_deleted`=1, `deleted_at`=1573630064 WHERE `id`=83 // 总计 7 次执行【UPDATE `pa_config_task_step` SET `is_deleted`=1】

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_step` WHERE `is_deleted`=0

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`config_task_code`='tv_broadcast') AND (`step_code`='begin_shot') // 总计 19 次执行【SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`config_task_code`=】

UPDATE `pa_config_task_step` SET `sort_order`=3, `updated_at`=1573630064 WHERE `id`=91

INSERT INTO `pa_config_task_step` (`group_id`, `config_task_code`, `step_code`, `sort_order`, `is_default`, `is_deleted`, `deleted_at`, `config_task_id`, `config_step_id`, `step_name`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'zdy2', 'begin_shot', 1, 1, 0, 0, 22, 2, '上传素材', 1, 1573630064, 1573630064)

INSERT INTO `pa_config_task_step` (`group_id`, `config_task_code`, `step_code`, `sort_order`, `is_default`, `is_deleted`, `deleted_at`, `config_task_id`, `config_step_id`, `step_name`, `status`, `created_at`, `updated_at`) VALUES ('015ce30b116ce86058fa6ab4fea4ac63', 'zdy2', 'program_render', 7, 1, 0, 0, 22, 14, '节目上传', 1, 1573630064, 1573630064)

Commit transaction

26、在 Postman 中 GET:http://api.pcs-api.localhost/v1/config-task-steps/edit/my-group-id ,当租户已自定义时,即 任务配置表、任务步骤配置表 中有当前租户的记录时,默认获取 任务配置表、任务步骤配置表 中的当前租户ID的记录,响应数据与执行的 SQL 如下

{
    "code": 10000,
    "message": "编辑租户的任务类型与步骤设置成功",
    "data": {
        "items": [
            {
                "id": 17,
                "code": "tv_broadcast",
                "name": "电视播出",
                "sort_order": 1,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 4,
                        "is_default": 1
                    },
                    {
                        "id": 10,
                        "code": "video_editing",
                        "name": "视频编辑",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 11,
                        "code": "tandem_list",
                        "name": "串联单",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 14,
                        "code": "program_render",
                        "name": "节目上传",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 16,
                        "code": "program_authen",
                        "name": "节目审查",
                        "sort_order": 8,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 18,
                "code": "inter_view",
                "name": "采访任务",
                "sort_order": 2,
                "steps": []
            },
            {
                "id": 19,
                "code": "rich_doc",
                "name": "互联网发布更新",
                "sort_order": 4,
                "steps": [
                    {
                        "id": 1,
                        "code": "retrieval",
                        "name": "取稿",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 4,
                        "code": "writing",
                        "name": "撰写稿件",
                        "sort_order": 2,
                        "is_default": 1
                    },
                    {
                        "id": 7,
                        "code": "manuscript_review",
                        "name": "稿件审核",
                        "sort_order": 3,
                        "is_default": 1
                    },
                    {
                        "id": 9,
                        "code": "newspaper_issued",
                        "name": "报纸签发",
                        "sort_order": 5,
                        "is_default": 1
                    },
                    {
                        "id": 12,
                        "code": "wechat_group_reference",
                        "name": "微信组稿引用",
                        "sort_order": 6,
                        "is_default": 1
                    },
                    {
                        "id": 15,
                        "code": "website_release",
                        "name": "网站发布",
                        "sort_order": 7,
                        "is_default": 1
                    },
                    {
                        "id": 17,
                        "code": "app_release",
                        "name": "APP发布",
                        "sort_order": 8,
                        "is_default": 1
                    },
                    {
                        "id": 18,
                        "code": "wechat_release",
                        "name": "微信发布",
                        "sort_order": 9,
                        "is_default": 1
                    },
                    {
                        "id": 19,
                        "code": "blog_release",
                        "name": "微博发布",
                        "sort_order": 10,
                        "is_default": 1
                    },
                    {
                        "id": 20,
                        "code": "qq_release",
                        "name": "企鹅号发布",
                        "sort_order": 11,
                        "is_default": 1
                    }
                ]
            },
            {
                "id": 21,
                "code": "zdy1",
                "name": "自定义一",
                "sort_order": 5,
                "steps": []
            },
            {
                "id": 22,
                "code": "zdy2",
                "name": "自定义二",
                "sort_order": 6,
                "steps": [
                    {
                        "id": 2,
                        "code": "begin_shot",
                        "name": "上传素材",
                        "sort_order": 1,
                        "is_default": 1
                    },
                    {
                        "id": 14,
                        "code": "program_render",
                        "name": "节目上传",
                        "sort_order": 7,
                        "is_default": 1
                    }
                ]
            }
        ]
    }
}
SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'

SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC

27、在 Postman 中 PUT:http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ,请求数据(在 采访任务 后添加之前删除的 1 项任务配置(手动任务,调整其顺序值为 3)及其中的 1 项步骤配置)与执行的更新 SQL ( 任务配置表:2 条更新语句(先还原再更新);任务步骤配置表:1 条更新语句) 如下,打开表,如图8

图8

[
    {
        "code": "tv_broadcast",
        "name": "电视播出",
        "sort_order": 1,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 4,
                "is_default": 1
            },
            {
                "code": "video_editing",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "tandem_list",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "program_render",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "program_authen",
                "sort_order": 8,
                "is_default": 1
            }
        ]
    },
    {
        "code": "inter_view",
        "name": "采访任务",
        "sort_order": 2,
        "steps": []
    },
    {
        "code": "other",
        "name": "手动任务",
        "sort_order": 3,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            }
        ]
    },
    {
        "code": "rich_doc",
        "name": "互联网发布更新",
        "sort_order": 4,
        "steps": [
            {
                "code": "retrieval",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "writing",
                "sort_order": 2,
                "is_default": 1
            },
            {
                "code": "manuscript_review",
                "sort_order": 3,
                "is_default": 1
            },
            {
                "code": "newspaper_issued",
                "sort_order": 5,
                "is_default": 1
            },
            {
                "code": "wechat_group_reference",
                "sort_order": 6,
                "is_default": 1
            },
            {
                "code": "website_release",
                "sort_order": 7,
                "is_default": 1
            },
            {
                "code": "app_release",
                "sort_order": 8,
                "is_default": 1
            },
            {
                "code": "wechat_release",
                "sort_order": 9,
                "is_default": 1
            },
            {
                "code": "blog_release",
                "sort_order": 10,
                "is_default": 1
            },
            {
                "code": "qq_release",
                "sort_order": 11,
                "is_default": 1
            }
        ]
    },
    {
        "code": "zdy1",
        "name": "自定义一",
        "sort_order": 5,
        "steps": []
    },
    {
        "code": "zdy2",
        "name": "自定义二",
        "sort_order": 6,
        "steps": [
            {
                "code": "begin_shot",
                "sort_order": 1,
                "is_default": 1
            },
            {
                "code": "program_render",
                "sort_order": 7,
                "is_default": 1
            }
        ]
    }
]
Begin transaction

UPDATE `pa_config_task` SET `is_deleted`=0, `deleted_at`=0 WHERE `id`=20

UPDATE `pa_config_task` SET `sort_order`=3, `updated_at`=1573632089 WHERE `id`=20

UPDATE `pa_config_task_step` SET `is_deleted`=0, `deleted_at`=0 WHERE `id`=99

Commit transaction

28、已经有 3 个租户自定义任务类型与步骤设置,2 张表的总行数(20、97),基于 Explain 分析 SQL 如下(type:const|ref,rows 至多为表中总行数的 1/4),索引是合理的,如图9
注:
(1)type:显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
(2)rows:这个数表示 MySQL 要遍历多少数据才能找到,至多为表中总行数的 1/4 ,就是比较理想的情况

图9

EXPLAIN SELECT * FROM `pa_config_task` WHERE `group_id`=''
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task  ref uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 130 const 4 100.00 

EXPLAIN SELECT * FROM `pa_config_task` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task  ref uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 130 const 6 100.00 

EXPLAIN SELECT * FROM `pa_config_task` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0)
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task  ref uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 130 const 6 10.00 Using index condition

EXPLAIN SELECT * FROM `pa_config_task` WHERE (`group_id`='') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task  ref uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 130 const 4 10.00 Using index condition; Using filesort

EXPLAIN SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task_step  ref uc_group_id_config_task_code_step_code_is_deleted_deleted_at uc_group_id_config_task_code_step_code_is_deleted_deleted_at 130 const 26 100.00  

EXPLAIN SELECT * FROM `pa_config_task_step` WHERE (`group_id`='') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task_step  ref uc_group_id_config_task_code_step_code_is_deleted_deleted_at uc_group_id_config_task_code_step_code_is_deleted_deleted_at 130 const 27 10.00 Using index condition; Using filesort

EXPLAIN SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESC
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task_step  ref uc_group_id_config_task_code_step_code_is_deleted_deleted_at uc_group_id_config_task_code_step_code_is_deleted_deleted_at 130 const 26 10.00 Using index condition; Using filesort

EXPLAIN SELECT `pa_config_task`.`id` FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='tv_broadcast') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0) LIMIT 2
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task  const uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 266 const,const,const,const 1 100.00 Using index

EXPLAIN SELECT EXISTS(SELECT * FROM `pa_config_task` WHERE (`pa_config_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_task`.`code`='zdy1') AND (`pa_config_task`.`is_deleted`=0) AND (`pa_config_task`.`deleted_at`=0))
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY          No tables used
2 SUBQUERY pa_config_task  const uc_group_id_code_is_deleted_deleted_at uc_group_id_code_is_deleted_deleted_at 266 const,const,const,const 1 100.00 Using index

EXPLAIN SELECT EXISTS(SELECT * FROM `pa_config_step` WHERE (`pa_config_step`.`code`='writing') AND (`is_deleted`=0))
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY          No tables used
2 SUBQUERY pa_config_step  ref idx_code idx_code 130 const 1 10.00 Using where

EXPLAIN SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`config_task_code`='tv_broadcast') AND (`step_code`='begin_shot')
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pa_config_task_step  ref uc_group_id_config_task_code_step_code_is_deleted_deleted_at uc_group_id_config_task_code_step_code_is_deleted_deleted_at 390 const,const,const 1 100.00 

 

永夜

View Comments