在 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)，新建步骤配置表，初始化步骤配置表的记录，删除租户的任务配置表、删除租户的任务步骤配置表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 | <?phpuse 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | // 基础设置 - 步骤配置[ '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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | <?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 如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | { "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 } ] } ] }} |
1 2 3 4 5 6 7 | 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` DESCSELECT * 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | <?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | <?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 如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 | { "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": { } }, "_meta": { "totalCount": 18, "pageCount": 1, "currentPage": 1, "perPage": 18 } }} |
1 2 3 4 5 | SELECT COUNT(*) FROM `pa_config_step` WHERE `is_deleted`=0SELECT COUNT(*) FROM `pa_config_step` WHERE `is_deleted`=0SELECT * FROM `pa_config_step` WHERE `is_deleted`=0 LIMIT 18 |
16、新建更新任务类型与步骤设置的方法文件(\api\rests\config_task_step\UpdateAction.php)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 | <?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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?phpnamespace 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | <?phpnamespace 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
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 transactionSELECT * 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
![在 Postman 中 PUT：http://api.pcs-api.localhost/v1/config-task-steps/my-group-id ，请求数据([])与执行的 SQL (未插入数据) 如下](http://www.shuijingwanwq.com/wp-content/uploads/2019/11/5-1.png)
图5
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 transactionSELECT * 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | [ { "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 } ] }] |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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 transactionSELECT * 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`=0SELECT * 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 如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | { "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 } ] } ] }} |
1 2 3 4 5 6 7 | 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` DESCSELECT * 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | [ { "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 } ] }] |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 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 transactionUPDATE `pa_config_task` SET `is_deleted`=1, `deleted_at`=1573630064 WHERE `id`=20SELECT * 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`=19INSERT 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`=0SELECT * 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`=91INSERT 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 如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | { "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 } ] } ] }} |
1 2 3 4 5 6 7 | 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` DESCSELECT * 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | [ { "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 } ] }] |
1 2 3 4 5 6 7 8 9 | Begin transactionUPDATE `pa_config_task` SET `is_deleted`=0, `deleted_at`=0 WHERE `id`=20UPDATE `pa_config_task` SET `sort_order`=3, `updated_at`=1573632089 WHERE `id`=20UPDATE `pa_config_task_step` SET `is_deleted`=0, `deleted_at`=0 WHERE `id`=99Commit 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | EXPLAIN SELECT * FROM `pa_config_task` WHERE `group_id`=''id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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 Extra1 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 Extra1 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 conditionEXPLAIN SELECT * FROM `pa_config_task` WHERE (`group_id`='') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESCid select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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 filesortEXPLAIN SELECT * FROM `pa_config_task_step` WHERE `group_id`='015ce30b116ce86058fa6ab4fea4ac63'id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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` DESCid select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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 filesortEXPLAIN SELECT * FROM `pa_config_task_step` WHERE (`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`is_deleted`=0) ORDER BY `sort_order`, `id` DESCid select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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 filesortEXPLAIN 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 2id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 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 indexEXPLAIN 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 Extra1 PRIMARY No tables used2 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 indexEXPLAIN 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 Extra1 PRIMARY No tables used2 SUBQUERY pa_config_step ref idx_code idx_code 130 const 1 10.00 Using whereEXPLAIN 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 Extra1 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 |
1 条回复
[…] […]