-- ------------------------------
-- 业务需求更新的相关sql建议放在这里
-- 最新的放在最上面
-- ------------------------------
-- 2024.12.10 系统菜单增加 ”打开方式“ 字段
INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('打开方式', 'open_type', 'Enum', 'N5jpk0bQ', '{\"statusLabelEnum\":{\"data\":[{\"value\":\"1\",\"text\":\"默认\",\"statusIcon\":\"\"},{\"value\":\"2\",\"text\":\"浏览器新窗口\",\"statusIcon\":\"\"}],\"parser\":\"StatusLabelEnum\",\"source\":\"system_app_menu\",\"sourceField\":\"open_type\"}}', 0, 'kS9f6gUM', -5, 'IaOhsuPL', 1733823193, 1733823193);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'tinyint', '11', '1', 0, 0, '打开方式', 'IaOhsuPL', 'system_app_menu', 'kS9f6gUM', 'open_type', '打开方式', -5, 'np2O0Pjx', 1733823193, 1733823193);
update business_field set is_build=1,build_at=1733823193 where `table`='system_app_menu' and `field`='open_type';
ALTER TABLE system_app_menu add column `open_type` tinyint (11)  DEFAULT '1' COMMENT '打开方式';
-- 2024.11.6 系统菜单增加字段
INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('图标', 'icon', 'Text', 'N5jpk0bQ', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'kS9f6gUM', -2, 'UyZaXPmy', 1730863231, 1730863231);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '255', '', 0, 0, '图标', 'UyZaXPmy', 'system_app_menu', 'kS9f6gUM', 'icon', '图标', -2, 'B7gcPLJM', 1730863231, 1730863231);
ALTER TABLE system_app_menu modify column `icon` varchar (255)  DEFAULT NULL COMMENT '图标';
update business_field set is_build=1,build_at=1730863231 where `table`='system_app_menu' and `field`='icon';
INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('图标颜色', 'icon_color', 'Text', 'N5jpk0bQ', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'kS9f6gUM', -3, 'mDiuvvyS', 1730863337, 1730863337);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '255', '', 0, 0, '图标颜色', 'mDiuvvyS', 'system_app_menu', 'kS9f6gUM', 'icon_color', '图标颜色', -3, 'sKz31Oow', 1730863337, 1730863337);
ALTER TABLE system_app_menu modify column `icon_color` varchar (255)  DEFAULT NULL COMMENT '图标颜色';
update business_field set is_build=1,build_at=1730863337 where `table`='system_app_menu' and `field`='icon_color';
INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('页签名称', 'tab_name', 'Text', 'N5jpk0bQ', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'kS9f6gUM', -4, 'bUPYZ7ZA', 1730863409, 1730863409);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '255', '', 0, 0, '页签名称', 'bUPYZ7ZA', 'system_app_menu', 'kS9f6gUM', 'tab_name', '页签名称', -4, 'erAwBpE8', 1730863409, 1730863409);
ALTER TABLE system_app_menu add column `tab_name` varchar (255)  DEFAULT NULL COMMENT '页签名称';
update business_field set is_build=1,build_at=1730863409 where `table`='system_app_menu' and `field`='tab_name';

-- 2024.9.26 兼容新版本集成平台所需的字段，corp 表及 corp_department 元数据:
INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('企业简称/别名', 'alias', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -1, 't2PwRDwJ', 1725934790, 1725934790);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '32', '', 0, 0, '企业简称/别名', 't2PwRDwJ', 'corp', 'tiJj2Hpy', 'alias', '企业简称/别名', -1, 'VgzAHjQj', 1725934790, 1725934790);
ALTER TABLE corp add column `alias` varchar (32)  DEFAULT NULL COMMENT '企业简称/别名';
update business_field set is_build=1,build_at=1725934790 where `table`='corp' and `field`='alias';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('联系人', 'contact', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -2, 'OaWrM6rV', 1725935019, 1725935019);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '32', '', 0, 0, '联系人', 'OaWrM6rV', 'corp', 'tiJj2Hpy', 'contact', '联系人', -2, 'aHDc4MQm', 1725935019, 1725935019);
ALTER TABLE corp add column `contact` varchar (32)  DEFAULT NULL COMMENT '联系人';
update business_field set is_build=1,build_at=1725935019 where `table`='corp' and `field`='contact';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('联系人电话', 'contact_tel', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -3, 'ti9HVAVo', 1725935468, 1725935468);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '32', '', 0, 0, '联系人电话', 'ti9HVAVo', 'corp', 'tiJj2Hpy', 'contact_tel', '联系人电话', -3, 'sn79e4tw', 1725935468, 1725935468);
ALTER TABLE corp add column `contact_tel` varchar (32)  DEFAULT NULL COMMENT '联系人电话';
update business_field set is_build=1,build_at=1725935469 where `table`='corp' and `field`='contact_tel';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('联系人邮箱', 'contact_email', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -4, 'QXP4wqLD', 1725935482, 1725935482);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '32', '', 0, 0, '联系人邮箱', 'QXP4wqLD', 'corp', 'tiJj2Hpy', 'contact_email', '联系人邮箱', -4, 'rvqlBIZm', 1725935482, 1725935482);
ALTER TABLE corp add column `contact_email` varchar (32)  DEFAULT NULL COMMENT '联系人邮箱';
update business_field set is_build=1,build_at=1725935483 where `table`='corp' and `field`='contact_email';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('联系的二维码', 'contact_qrcode', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -5, 'BRn2mI70', 1725935499, 1725935499);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '255', '', 0, 0, '联系的二维码', 'BRn2mI70', 'corp', 'tiJj2Hpy', 'contact_qrcode', '联系的二维码', -5, 'UUATekux', 1725935499, 1725935499);
ALTER TABLE corp add column `contact_qrcode` varchar (255)  DEFAULT NULL COMMENT '联系的二维码';
update business_field set is_build=1,build_at=1725935499 where `table`='corp' and `field`='contact_qrcode';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('地址', 'address', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -6, 'x6gXO2e5', 1725935512, 1725935512);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '255', '', 0, 0, '地址', 'x6gXO2e5', 'corp', 'tiJj2Hpy', 'address', '地址', -6, 'CpgBMJo5', 1725935512, 1725935512);
ALTER TABLE corp add column `address` varchar (255)  DEFAULT NULL COMMENT '地址';
update business_field set is_build=1,build_at=1725935513 where `table`='corp' and `field`='address';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('授权码', 'license', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -7, 'aR1y6bOF', 1725935524, 1725935524);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '1024', '', 0, 0, '授权码', 'aR1y6bOF', 'corp', 'tiJj2Hpy', 'license', '授权码', -7, 'NqosZUXE', 1725935524, 1725935524);
ALTER TABLE corp add column `license` varchar (1024)  DEFAULT NULL COMMENT '授权码';
update business_field set is_build=1,build_at=1725935527 where `table`='corp' and `field`='license';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('公钥', 'public_key', 'Text', 'nAm651tS', '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 0, 'tiJj2Hpy', -8, 'SgD8wKPZ', 1725935540, 1725935540);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'varchar', '1024', '', 0, 0, '公钥', 'SgD8wKPZ', 'corp', 'tiJj2Hpy', 'public_key', '公钥', -8, 'C54y2V5q', 1725935540, 1725935540);
ALTER TABLE corp add column `public_key` varchar (1024)  DEFAULT NULL COMMENT '公钥';
update business_field set is_build=1,build_at=1725935540 where `table`='corp' and `field`='public_key';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('开始时间', 'begin_at', 'Datetime', 'nAm651tS', '{\"date\":{\"parser\":\"DateTimeParser\",\"format\":\"YYYY-MM-DD HH:mm:ss\"}}', 0, 'tiJj2Hpy', -10, 'o35QH0RI', 1725935565, 1725935565);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'int', '50', '', 0, 1, '开始时间', 'o35QH0RI', 'corp', 'tiJj2Hpy', 'begin_at', '开始时间', -10, 'vYz9S2kw', 1725935565, 1725935565);
ALTER TABLE corp add column `begin_at` int (50)  unsigned  DEFAULT NULL COMMENT '开始时间';
update business_field set is_build=1,build_at=1725935566 where `table`='corp' and `field`='begin_at';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('过期时间', 'expire_at', 'Datetime', 'nAm651tS', '{\"date\":{\"parser\":\"DateTimeParser\",\"format\":\"YYYY-MM-DD HH:mm:ss\"}}', 0, 'tiJj2Hpy', -11, 'qqKJp4sH', 1725935577, 1725935577);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'int', '50', '', 0, 1, '过期时间', 'qqKJp4sH', 'corp', 'tiJj2Hpy', 'expire_at', '过期时间', -11, 'JvCNcZmM', 1725935577, 1725935577);
ALTER TABLE corp add column `expire_at` int (50)  unsigned  DEFAULT NULL COMMENT '过期时间';
update business_field set is_build=1,build_at=1725935577 where `table`='corp' and `field`='expire_at';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('连接数', 'conn_qty', 'Integer', 'nAm651tS', '{\"integer\":{\"parser\":\"IntegerParser\",\"negative\":false}}', 0, 'tiJj2Hpy', -12, 'HgqyLKkb', 1725935590, 1725935590);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'int', '11', '', 0, 0, '连接数', 'HgqyLKkb', 'corp', 'tiJj2Hpy', 'conn_qty', '连接数', -12, 'TkhTvIQb', 1725935590, 1725935590);
ALTER TABLE corp add column `conn_qty` int (11)  DEFAULT NULL COMMENT '连接数';
update business_field set is_build=1,build_at=1725935590 where `table`='corp' and `field`='conn_qty';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('方案数', 'solution_qty', 'Integer', 'nAm651tS', '{\"integer\":{\"parser\":\"IntegerParser\",\"negative\":false}}', 0, 'tiJj2Hpy', -13, 'PP4Awq8W', 1725935599, 1725935599);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'int', '11', '', 0, 0, '方案数', 'PP4Awq8W', 'corp', 'tiJj2Hpy', 'solution_qty', '方案数', -13, 'xrJfR9pl', 1725935599, 1725935599);
ALTER TABLE corp add column `solution_qty` int (11)  DEFAULT NULL COMMENT '方案数';
update business_field set is_build=1,build_at=1725935600 where `table`='corp' and `field`='solution_qty';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('类型', 'type', 'Enum', 'nAm651tS', '{\"statusLabelEnum\":{\"data\":[{\"value\":\"1\",\"text\":\"默认\",\"statusIcon\":\"\"},{\"value\":\"2\",\"text\":\"OEM\",\"statusIcon\":\"\"},{\"value\":\"3\",\"text\":\"代理商\",\"statusIcon\":\"\"}],\"parser\":\"StatusLabelEnum\",\"source\":\"corp\",\"sourceField\":\"type\"}}', 0, 'tiJj2Hpy', -14, 'aHz6AbQI', 1725935630, 1725935630);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'tinyint', '11', '1', 0, 0, '类型', 'aHz6AbQI', 'corp', 'tiJj2Hpy', 'type', '类型', -14, 'calBgbKT', 1725935630, 1725935630);
ALTER TABLE corp add column `type` tinyint (11)  DEFAULT '1' COMMENT '类型';
update business_field set is_build=1,build_at=1725935630 where `table`='corp' and `field`='type';

INSERT INTO `business_entity` (`name`, `no`, `type`, `parent_entity_id`, `meta_profile`, `is_preset`, `business_id`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES ('申请信息JSON', 'apply_info', 'MultiText', 'nAm651tS', '{}', 0, 'tiJj2Hpy', -15, 'PGcM7pDP', 1725935641, 1725935641);
INSERT INTO `business_field` (`is_required`, `type`, `length`, `default`, `zero_fill`, `is_unsigned`, `comment`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `sort_order`, `id`, `created_at`, `updated_at`) VALUES (0, 'text', '', '', 0, 0, '申请信息JSON', 'PGcM7pDP', 'corp', 'tiJj2Hpy', 'apply_info', '申请信息JSON', -15, 'mESiZeNe', 1725935641, 1725935641);
ALTER TABLE corp add column `apply_info` text DEFAULT NULL COMMENT '申请信息JSON';
update business_field set is_build=1,build_at=1725935641 where `table`='corp' and `field`='apply_info';

UPDATE `business_entity` SET `no` = 'corp_department', `business_id` = 0x724F734275646351, `parent_entity_id` = 0x3000000000000000, `name` = '企业部门', `type` = 'MainEntity', `is_preset` = 0, `form_component_id` = 0, `meta_profile` = '{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"corp_department\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"}}', `updated_at` = 0, `created_at` = 0, `sort_order` = 0 WHERE `id` = 'GX2ncw7E';
UPDATE `business_entity` SET `no` = 'corp', `business_id` = 0x74694A6A32487079, `parent_entity_id` = 0x3000000000000000, `name` = '企业', `type` = 'MainEntity', `is_preset` = 0, `form_component_id` = 0, `meta_profile` = '{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"corp\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"}}', `updated_at` = 0, `created_at` = 0, `sort_order` = 0 WHERE `id` = 'nAm651tS';

-- 2024.5.16 评论表处理为标准业务对象 有些标准字段需要补上
ALTER TABLE `comment` add column `data_status` int(11) DEFAULT 2 COMMENT '数据状态' after `id`;
ALTER TABLE `comment` add column `updated_at` int(11) DEFAULT 0 COMMENT '更新日期' after `created_at`;
ALTER TABLE `comment` add column `updated_by` int(11) DEFAULT 0 COMMENT '更新人' after `updated_at`;

-- 2023.12.26 工作流实例表增加备注字段
ALTER TABLE `workflow_instance` add column `remark` varchar(255) DEFAULT NULL COMMENT '备注' after `card`;

-- 2023.9.27 业务对象表增加数据严格模式字段，用于操作数据时校验是否严格校验权限
ALTER TABLE `business` add column `strict_type` tinyint(11) DEFAULT '0' COMMENT '数据严格模式' after `is_preset`;
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES (0x5A43345156767952, 'strict_type', 0x6D78325669766272, 0x454E3731366F5651, '数据严格模式', 'Enum', 0, 0, '{\"statusLabelEnum\":{\"data\":[{\"value\":\"0\",\"text\":\"关闭\",\"statusIcon\":\"\"},{\"value\":\"1\",\"text\":\"开启\",\"statusIcon\":\"\"}],\"parser\":\"StatusLabelEnum\",\"source\":\"business\",\"sourceField\":\"strict_type\"}}', 1695785133, 1695785133, -1);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES (0x535463597479376F, 0x5A43345156767952, 'business', 0x6D78325669766272, 'strict_type', '数据严格模式', 0, 'tinyint', '11', 0, '0', '数据严格模式', 0, 1, 1695785133, NULL, 1695785133, 1695785133, -1);

-- 2023.8.7 任务计划增加字段
ALTER TABLE `task_schedule_item` add column `remark` varchar(255) DEFAULT NULL COMMENT '未完成原因' after `report_manhours`;
ALTER TABLE `task_schedule_item` add column `is_unscheduled` tinyint(5) DEFAULT 0 COMMENT '计划外' after `remark`;
ALTER TABLE `task_schedule_item` add column `input_department_id` int(11) unsigned DEFAULT NULL COMMENT '投入部门' after `is_unscheduled`;
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES (0x59776D696D433535, 'input_department_id', 0x4C6F49425A345172, 0x7138423535597338, '投入部门', 'CorpDepartment', 0, 0, '{\"relationQuery\":{\"parser\":\"FieldJoinTable\",\"source\":\"task_schedule_item\",\"sourceField\":\"input_department_id\",\"target\":\"corp_department\",\"targetField\":\"id\",\"targetDisplayField\":\"name\"}}', 1686623654, 1686623654, 9987);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES (0x64556239744F794E, 'is_unscheduled', 0x4C6F49425A345172, 0x7138423535597338, '计划外', 'Enum', 0, 0, '{\"statusLabelEnum\":{\"data\":[{\"value\":\"0\",\"text\":\"计划内\",\"statusIcon\":\"\"},{\"value\":\"1\",\"text\":\"计划外\",\"statusIcon\":\"\"}],\"parser\":\"StatusLabelEnum\",\"source\":\"task_schedule_item\",\"sourceField\":\"is_unscheduled\"}}', 1646047719, 1646047719, 9988);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES (0x67376C6C4D6D414F, 'remark', 0x4C6F49425A345172, 0x7138423535597338, '未完成原因', 'Text', 0, 0, '{\"textFormat\":{\"parser\":\"TextFormatParser\",\"format\":\"common\"}}', 1646047667, 1646047667, 9989);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES (0x4443317078584139, 0x67376C6C4D6D414F, 'task_schedule_item', 0x4C6F49425A345172, 'remark', '未完成原因', 0, 'varchar', '255', 0, '', '未完成原因', 0, 1, 1646047667, NULL, 1646047667, 1646047667, 9989);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES (0x467A4A6736766D73, 0x64556239744F794E, 'task_schedule_item', 0x4C6F49425A345172, 'is_unscheduled', '计划外', 0, 'tinyint', '11', 0, '0', '计划外', 0, 1, 1646047719, NULL, 1646047719, 1646047719, 9988);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES (0x65756A346E6A4734, 0x59776D696D433535, 'task_schedule_item', 0x4C6F49425A345172, 'input_department_id', '投入部门', 0, 'int', '11', 1, '', '投入部门', 0, 1, 1686623654, NULL, 1686623654, 1686623654, 9987);


-- 2023.6.19 角色管理增加菜单设置
UPDATE `business` SET `is_extend` = 1, `object` = 'system\\SystemRole' WHERE `id` = 'xjC1P2oP';
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('BFE6jNK5', 'seq', 'xjC1P2oP', 'YNSacSOk', '分录序号', 'Integer', 1, 0, '{}', 1687155930, 1687155930, 9997);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('CBKmypiv', 'parent_id', 'xjC1P2oP', 'YNSacSOk', '主表主键', 'Integer', 1, 0, '{}', 1687155930, 1687155930, 9998);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('Gvd5u4x3', 'client_id', 'xjC1P2oP', 'YNSacSOk', '端应用', 'RelationBusiness', 0, 0, '{\"relationQuery\":{\"parser\":\"FieldJoinTable\",\"source\":\"system_role_menus\",\"sourceField\":\"client_id\",\"target\":\"system_app\",\"targetBusinessId\":\"HK8AmSTP\",\"targetField\":\"id\",\"targetDisplayField\":[\"name\"]}}', 1687156222, 1687156222, -3);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('YNSacSOk', 'system_role_menus', 'xjC1P2oP', 'jF2cWgP7', '菜单', 'Entry', 0, 0, '{\"extendInfo\":{\"displayFields\":[]}}', 1687155930, 1687155930, -1);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('ZrMVS5T6', 'system_menu_id', 'xjC1P2oP', 'YNSacSOk', '菜单', 'RelationBusiness', 0, 0, '{\"relationQuery\":{\"parser\":\"FieldJoinTable\",\"source\":\"system_role_menus\",\"sourceField\":\"system_menu_id\",\"target\":\"system_app_menu\",\"targetBusinessId\":\"kS9f6gUM\",\"targetField\":\"id\",\"targetDisplayField\":[\"name\"]}}', 1687155960, 1687155960, -2);
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES ('kSVX6r5y', 'id', 'xjC1P2oP', 'YNSacSOk', '分录主键', 'Integer', 1, 0, '{}', 1687155930, 1687155930, 9999);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES ('D6yUjKod', 'kSVX6r5y', 'system_role_menus', 'xjC1P2oP', 'id', '分录主键', 1, 'int', '11', NULL, 'AUTO_INCREMENT', 'system preset field', 0, 1, 1687155930, 1687155930, 1687155930, 1687155930, 9999);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES ('HGWklQjP', 'CBKmypiv', 'system_role_menus', 'xjC1P2oP', 'parent_id', '主表主键', 1, 'int', '11', NULL, '0', 'system preset field', 0, 1, 1687155930, 1687155930, 1687155930, 1687155930, 9998);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES ('RR83RBd3', 'ZrMVS5T6', 'system_role_menus', 'xjC1P2oP', 'system_menu_id', '菜单', 0, 'int', '11', 1, '', '菜单', 0, 1, 1687155960, NULL, 1687155960, 1687155960, -2);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES ('SvOriKJ3', 'BFE6jNK5', 'system_role_menus', 'xjC1P2oP', 'seq', '分录序号', 1, 'int', '11', NULL, '0', 'system preset field', 0, 1, 1687155930, 1687155930, 1687155930, 1687155930, 9997);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES ('gF10QTe1', 'Gvd5u4x3', 'system_role_menus', 'xjC1P2oP', 'client_id', '端应用', 0, 'int', '11', 1, '', '端应用', 0, 1, 1687156223, NULL, 1687156222, 1687156222, -3);
insert into `business_table`(`table`,`form_entity_id`,`business_id`,`engine`,`charset`,`collate`,`is_build`,`build_at`,`update_build_at`,`created_at`,`updated_at`,`is_required`,`sort_order`) values
    ('system_role_menus','YNSacSOk','xjC1P2oP','InnoDB','utf8','utf8_general_ci','1','1687155930','1687155930','1687155930','1687155933','0','-1');
insert into `business_index`(`id`,`form_entity_id`,`table`,`name`,`field`,`type`) values
    ('vPK24TWG','YNSacSOk','system_role_menus','PRIMARY','`id`','PRIMARY');
CREATE TABLE `system_role_menus` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'system preset field',
     `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT 'system preset field',
     `seq` int(11) NOT NULL DEFAULT '0' COMMENT 'system preset field',
     `system_menu_id` int(11) unsigned DEFAULT NULL COMMENT '菜单',
     `client_id` int(11) unsigned DEFAULT NULL COMMENT '端应用',
     PRIMARY KEY (`id`),
     UNIQUE KEY `uniq` (`parent_id`,`system_menu_id`,`client_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='菜单';

-- 2023.6.2 增加部门员工与部门关系的排序字段
ALTER TABLE `corp_department_user` add column `is_main_department` int(11) NOT NULL DEFAULT 0 COMMENT '是否主要部门 0否 1是' after `is_leader`;

-- 2023.5.24 用户登录记录增加记录端应用ID
INSERT INTO `business_entity` (`id`, `no`, `business_id`, `parent_entity_id`, `name`, `type`, `is_preset`, `form_component_id`, `meta_profile`, `updated_at`, `created_at`, `sort_order`) VALUES (0x6D6633473367304E, 'client_id', 0x6439596A6E727964, 0x6D35387534704431, '应用端', 'RelationBusiness', 0, 0, '{\"relationQuery\":{\"parser\":\"FieldJoinTable\",\"source\":\"system_user_login\",\"sourceField\":\"client_id\",\"target\":\"system_app_client\",\"targetBusinessId\":\"JZMcsgGA\",\"targetField\":\"id\",\"targetDisplayField\":[\"name\"]}}', 1684893207, 1684893207, 9988);
INSERT INTO `business_field` (`id`, `form_entity_id`, `table`, `business_id`, `field`, `name`, `is_required`, `type`, `length`, `is_unsigned`, `default`, `comment`, `zero_fill`, `is_build`, `build_at`, `update_build_at`, `created_at`, `updated_at`, `sort_order`) VALUES (0x475946336F6B7751, 0x6D6633473367304E, 'system_user_login', 0x6439596A6E727964, 'client_id', '应用端', 0, 'int', '11', 1, '', '应用端', 0, 1, 1684893209, NULL, 1684893207, 1684893207, 9988);
ALTER TABLE `system_user_login` add column `client_id` int(11) unsigned DEFAULT NULL COMMENT '应用端';
-- 注：执行以上SQL增加端应用ID字段后 需要登录后台业务对象管理清理缓存

-- 2022.6.18 页面设计增加导入导出模板设计类型
INSERT INTO `business_page_type` (`id`, `app_client_id`, `app_client_no`, `no`, `name`, `status`, `remark`, `designer`) VALUES (13, 1, 'AdminPC', 'Export', '导入/导出模板', 1, NULL, 'admin-pc-export');

-- 2022.01.18 部分业务对象关联报错，缺少字段
ALTER TABLE `corp_department` add column `created_by` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '创建人';
ALTER TABLE `corp_department` add column `updated_by` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '修改人';

ALTER TABLE `system_user_profile` add column `created_by` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '创建人';
ALTER TABLE `system_user_profile` add column `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间';
ALTER TABLE `system_user_profile` add column `updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改人';
ALTER TABLE `system_user_profile` add column `updated_by` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '修改时间';

-- 2022.01.19 部分业务对象不是按标准业务对象的操作实现，增加标准search
-- corp_department
insert into `business_operation`(`id`,`app_id`,`business_id`,`no`,`route`,`name`,`is_preset`) values
('Y9jhfnz9','1','rOsBudcQ','search','business/instance/form/operation/search','标准搜索','0');
insert into `system_perm`(`name`,`app_id`,`business_id`,`operation_id`,`route`,`sort_order`,`created_at`,`updated_at`) values
('标准搜索','1','rOsBudcQ','Y9jhfnz9','business/instance/form/operation/search','0','1642556264','1642556264');


-- 部分单据不需要“启动工作流”按钮,设置workflow_instance_id=-1，workflow_node_id=-1
-- 01.11 角色权限增加对象字段控制
CREATE TABLE `system_role_perm_field` (
  `role_id` int(10) unsigned NOT NULL default 0,
  `business_id` binary(8) NOT NULL COMMENT '业务对象',
  `rule` text COMMENT '字段规则JSON',
  `created_at` int(10) unsigned NOT NULL default 0,
  `updated_at` int(10) unsigned NOT NULL default 0,
  PRIMARY KEY (`role_id`,`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 comment='角色权限业务对象字段控制';
/*
rule:示例,1:读，2:写，3:读写；
[
    {
		"demo": [{
				"amount": 1
			},
			{
				"user_id": 2
			},
			{
				"project_id": 1
			}
		]
	},
	{
		"demo_item": [{
			"name": 1
		},{
			"project_id": 1
		}]
	}
]
*/

-- 2022.01.11 抄送人优化：增加符合条件才抄送
ALTER TABLE workflow_node modify COLUMN cc_scope text COMMENT '抄送人' after `personnel_scope`;
ALTER TABLE workflow_instance_node_cc add COLUMN `status` tinyint not null default 1 COMMENT '状态：1未读未抄送，2未读已抄送，3已读' after `user_id`;
/*
[{
    "condition": [
        [{
            "object": "NODE_75",
            "variable": "ND_APPROVAL_STATUS",
            "operator": "eq",
            "value": "1",
            "valueLabel": null
        }]
    ],
    "users": [{
        "key": "role_税务会计1_32",
        "label": "税务会计1"
    }]
}]
*/

-- 12.15 工作流优化：
-- 1、增加抄送人: 模板通知：xxx(入职申请)已通过/未通过/已修改 ...
ALTER TABLE workflow_node ADD COLUMN cc_scope varchar(2000) not null default '[]' COMMENT '抄送人' after `personnel_scope`;
CREATE TABLE `workflow_instance_node_cc` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `instance_id` int(11) NOT NULL default 0 comment '实例id，兼容旧的',
    `node_id` int(11) NOT NULL default 0 comment '节点id，兼容旧的',
    `instance_node_id` int(11) NOT NULL default 0 comment '实例节点id,新版用',
    `user_id` int(11) NOT NULL default 0,
    PRIMARY KEY (`id`),
    key (`node_id`, `user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 2、审批人加"角色"
/*
{ "key": "role_系统运维_4", "label": "系统运维" }
-- 完整的格式
[
  {
    "key": "user_x**_73",
    "label": "x**"
  },
  {
    "key": "formvariable__catalog.id__0",
    "label": "主键-catalog.id"
  },
  {
    "key": "InitiatorDepartmentLeader",
    "label": "发起人部门负责人"
  },
  {
    "key": "nodevariable__NODE_138__catalog.source_user_id__138",
    "label": "来源人-catalog.source_user_id"
  },
  {
    "key": "role_人事主管_21",
    "label": "人事主管"
  }
]
*/
-- 3、工作流后置事件增加调用业务对象的某个方法；
/*
-- 3.1增加调用业务对象方法
ACTION_BUSINESS_METHOD
{"business_id":"xxxx","method":"myUpdate"}
如项目审批后执行myTest方法 {"business_id":"sbNpQhJR","method":"myTest"}

-- 3.2已存在的：
-- 变量处理
MAP_TO_WORKFLOW_VARIABLE
[{"source":"NODE_22.ND_APPROVAL_STATUS","target":"NODE_21.executive_attendance_leave.status"}]

-- 调用实例方法，数据库中无数据，不清楚其它项目是否有用到，不处理
ACTION_INSTANCE_METHOD
{}

*/


-- 12.13 部分业务对象字段类型错误修正
update business_entity set type='Text' where business_id='L3iLthEE' and `no`='business_id';
update business_field set type='binary',length=8 where business_id='L3iLthEE' and `field`='business_id';

-- 09.01 业务对象数据导入导出
-- 参考更新语句：docs/db/business_import_setting.sql

-- 2021.10.08 标准列表的操作按钮json，即：operations:xxxx
/*
[{
		"key": "search",
		"method": "handleSearch",
		"label": "高级筛选",
		"type": "operation",
		"url": "",
		"openMode": 1,
		"pageType": 1,
		"needSelected": false,
		"needAlert": false,
		"show": true,
		"event": [

		],
		"render": {
			"basic": {
				"width": "100%",
				"size": "default",
				"type": "default",
				"icon": ""
			}
		}
	},
	{
		"key": "create",
		"method": "handleCreate",
		"label": "新增",
		"type": "link",
		"url": "\/business-form\/edit\/<businessId>\/<pageId>\/0\/1",
		"openMode": 3,
		"pageType": 2,
		"needSelected": false,
		"needAlert": false,
		"show": true,
		"event": [

		],
		"render": {
			"basic": {
				"width": "100%",
				"size": "default",
				"type": "primary",
				"icon": "plus"
			}
		}
	},
	{
		"key": "print",
		"method": "handlePrint",
		"label": "打印",
		"type": "operation",
		"url": "",
		"openMode": 1,
		"pageType": 1,
		"needSelected": true,
		"needAlert": false,
		"show": true,
		"event": [

		],
		"render": {
			"basic": {
				"width": "100%",
				"size": "default",
				"type": "default",
				"icon": "printer"
			}
		}
	},
	{
		"key": "delete",
		"method": "handleDelete",
		"label": "删除",
		"type": "operation",
		"url": "",
		"openMode": 1,
		"pageType": 1,
		"needSelected": true,
		"needAlert": true,
		"show": true,
		"event": [

		],
		"render": {
			"basic": {
				"width": "100%",
				"size": "default",
				"type": "default",
				"icon": "close"
			}
		}
	},
  {
    "key": "handleExportList",
    "method": "search",
    "label": "导出记录",
    "type": "operation",
    "openMode": 1,
    "pageType": 1,
    "needSelected": false,
    "needAlert": false,
    "show": true,
    "event": [],
    "render": {
      "basic": {
        "width": "100%",
        "size": "default",
        "type": "primary",
        "icon": ""
      }
    },
    "urlParams": ""
  },
	{
		"label": "导入/导出",
		"show": true,
		"button_group": [{
				"key": "",
				"method": "handleDownloadTemplate",
				"label": "下载导入模板",
				"type": "operation",
				"url": "",
				"openMode": 1,
				"pageType": 1,
				"needSelected": false,
				"needAlert": false,
				"show": true,
				"event": [],
				"render": {
					"basic": {
						"width": "100%",
						"size": "default",
						"type": "default",
						"icon": "file-excel"
					}
				}
			},
			{
				"key": "handleImport",
				"method": "handleImport",
				"label": "按模板导入",
				"type": "operation",
				"url": "",
				"openMode": 1,
				"pageType": 1,
				"fileImport": true,
				"needSelected": false,
				"needAlert": false,
				"show": true,
				"event": [],
				"render": {
					"basic": {
						"width": "100%",
						"size": "default",
						"type": "default",
						"icon": "vertical-align-top"
					}
				}
			},
			{
				"key": "handleImport",
				"method": "handleExportByTemplate",
				"label": "按模板导出",
				"type": "operation",
				"url": "",
				"openMode": 1,
				"pageType": 1,
				"asyncExport": true,
				"needSelected": false,
				"needAlert": false,
				"show": true,
				"event": [],
				"render": {
					"basic": {
						"width": "100%",
						"size": "default",
						"type": "default",
						"icon": "vertical-align-bottom"
					}
				}
			},
			{
				"key": "handleImport",
				"method": "handleExportNowData",
				"label": "导出当前数据",
				"type": "operation",
				"url": "",
				"openMode": 1,
				"pageType": 1,
				"asyncExport": true,
				"needSelected": false,
				"needAlert": false,
				"show": true,
				"event": [],
				"render": {
					"basic": {
						"width": "100%",
						"size": "default",
						"type": "default",
						"icon": "vertical-align-top"
					}
				}
			}
		]
	}
]

 */

-- 2021.08.13 业务对象组管理，parent_id 更改为正确的类型
UPDATE business_group SET parent_id = NULL WHERE parent_id = 0;
ALTER TABLE business_group MODIFY COLUMN parent_id binary(8) DEFAULT NULL;

-- 2021.07.27 已存在的业务对象要增加复制操作，需要在表 business_operation system_perm 手动增加记录，才能分配权限

-- 2021.07.20 菜单管理更新预设菜单
ALTER TABLE `system_app_menu` add column `is_preset` tinyint(11) DEFAULT '0' COMMENT '是否预设' after `is_enable`;
UPDATE system_app_menu SET is_preset = 1 WHERE id IN (1,2,3,4,5,12,13,26,27,38,49,82,300,301,557);

-- 2021.07.19 菜单管理及权限
INSERT INTO system_app_menu (id, app_id, parent_id, level, path, name, route, sort_order, is_enable, icon, icon_color, is_open,
 created_at, updated_at) VALUES (557, 1, 13, 2, '13,557', '菜单管理', '/system/menu/manage', 0, 1, '', '', 0, 1626319016, 1626319435);
INSERT INTO system_perm (name, app_id, business_id, operation_id, route, sort_order, created_at, updated_at) VALUES
('获取所有应用端', 1, 'HK8AmSTP', 'SlBcbwoi', 'system/app-client/clients', 0, 1626679856, 1626679856);
INSERT INTO system_perm (name, app_id, business_id, operation_id, route, sort_order, created_at, updated_at) VALUES
('获取端应用的菜单', 1, 'kS9f6gUM', 'UjabZ66b', 'system/app-menu/client-menus', 0, 1626680615, 1626680615);
INSERT INTO system_perm (name, app_id, business_id, operation_id, route, sort_order, created_at, updated_at) VALUES
('生成业务对象页面路由地址', 1, 'kS9f6gUM', 'UjabZ67b', 'system/app-menu/get-route', 0, 1626680615, 1626680615);
INSERT INTO business_operation (id, app_id, business_id, no, route, name, is_preset) VALUES
('SlBcbwoi', 1, 'HK8AmSTP', 'clients', 'system/app-client/clients', '获取所有应用端', 0);
INSERT INTO business_operation (id, app_id, business_id, no, route, name, is_preset) VALUES
('UjabZ66b', 1, 'kS9f6gUM', 'client-menus', 'system/app-menu/client-menus', '获取端应用的菜单', 0);
INSERT INTO business_operation (id, app_id, business_id, no, route, name, is_preset) VALUES
('UjabZ67b', 1, 'kS9f6gUM', 'get-route', 'system/app-menu/get-route', '生成业务对象页面路由地址', 0);

-- 2021.07.13 区分系统预设的业务对象，这些对象是不在具体项目是生成model文件
update business set is_preset=1 where no like 'business%';
update business set is_preset=1 where no like 'system%';
update business set is_preset=1 where no like 'assist_%';
update business set is_preset=1 where no like 'task%';
update business set is_preset=1 where no like 'corp%';
update business set is_preset=1 where no like 'integration%';
-- select no from business where no like 'business%' or no like 'system%' or no like 'assist_%' or no like 'task%' or no like 'corp%' order by no asc;


-- 2021.07.09 增加应用类型：以支持不同类型应用特性，如首页、操作...
ALTER TABLE `system_app` add column `app_type` tinyint not null default '10' COMMENT '应用类型：10低代码，20集成平台' after `name`;

-- 2021.07.02 增加系统权限白名单用户
-- ...

-- 2021.06.24 页面增加默认字段
ALTER TABLE `business_page` add column `is_default` tinyint not null default 1 COMMENT '是否默认' after `custom_url`;


-- 2021.06.17 增加：
-- 1、没有或type=1 默认处理方式，节点来源转目标；type=2 固定来源转目标，
-- 2、工作流，节点后置事件增加：来源字段取值后增加值的映射；如：审批状态更新到表单的对应状态处理：如：2->20,3->30 ....
-- workflow_node_after_action.action_config：
-- [{"source":"NODE_171.ND_APPROVAL_STATUS","sourceValueMap":{"1":"20","2":"30"},"target":"NODE_174.demo.status"}];
-- [{"source":"9999","type":"2","target":"NODE_174.demo.status"}];
-- [{"source":"9999","sourceValueMap":{"9999":"20","2":"30"},"type":"2","target":"NODE_174.demo.status"}];

-- 业务对象的工作流插件
update business_plugin set entity='bpm\\domain\\workflow\\instance\\Instance';

ALTER TABLE `business_entity` add INDEX (`business_id`);
ALTER TABLE `business_entity` add INDEX (`no`);
ALTER TABLE `business_field` add INDEX (`business_id`);
ALTER TABLE `business_operation` add INDEX (`business_id`);
ALTER TABLE `business_page` add INDEX (`business_id`);
ALTER TABLE `business_table` add INDEX (`business_id`);

-- 2021.05.27 页面增加代码版本
ALTER TABLE `business_page` add column `code_version` varchar(16) not null default 'V2' COMMENT '代码版本' after `design_data`;
update business_page set code_version='V1' where design_data not like '%mainEntityTable%';
update business_page set code_version='V1' where custom_url is not null;

-- 2021.05.19 页面方案对象不用扩展实现，加到标准
update business set is_extend=0,object=null where id='m2oUqEz4';
-- system_operation_log
update business set is_extend=0,object=null where id='ToKLHGI4';

-- 2021.05.11修改相关表名
-- 生成参考：./yii test/gen-alert-table-sql

-- 将找到的引用关系手动修改正确，移除_form、_app
-- select * from business_entity where meta_profile like '%business_form%'
-- select * from business_entity where meta_profile like '%business_app%'

alter table `business_app_operation` rename to `business_operation`;
alter table `business_form_component` rename to `business_component`;
alter table `business_form_entity` rename to `business_entity`;
alter table `business_form_event` rename to `business_event`;
alter table `business_form_field` rename to `business_field`;
alter table `business_form_index` rename to `business_index`;
alter table `business_form_page` rename to `business_page`;
alter table `business_form_page_component` rename to `business_page_component`;
alter table `business_form_page_export_setting` rename to `business_page_export_setting`;
alter table `business_form_page_solution` rename to `business_page_solution`;
alter table `business_form_page_type` rename to `business_page_type`;
alter table `business_form_plugin` rename to `business_plugin`;
alter table `business_form_service` rename to `business_service`;
alter table `business_form_table` rename to `business_table`;

update `business` set `no`='business_operation' where `no`='business_app_operation';
update `business` set `no`='business_component' where `no`='business_form_component';
update `business` set `no`='business_entity' where `no`='business_form_entity';
update `business` set `no`='business_event' where `no`='business_form_event';
update `business` set `no`='business_field' where `no`='business_form_field';
update `business` set `no`='business_index' where `no`='business_form_index';
update `business` set `no`='business_page' where `no`='business_form_page';
update `business` set `no`='business_page_component' where `no`='business_form_page_component';
update `business` set `no`='business_page_export_setting' where `no`='business_form_page_export_setting';
update `business` set `no`='business_page_solution' where `no`='business_form_page_solution';
update `business` set `no`='business_page_type' where `no`='business_form_page_type';
update `business` set `no`='business_plugin' where `no`='business_form_plugin';
update `business` set `no`='business_service' where `no`='business_form_service';
update `business` set `no`='business_table' where `no`='business_form_table';

update `business_entity` set `no`='business_operation' where `no`='business_app_operation';
update `business_entity` set `no`='business_component' where `no`='business_form_component';
update `business_entity` set `no`='business_entity' where `no`='business_form_entity';
update `business_entity` set `no`='business_event' where `no`='business_form_event';
update `business_entity` set `no`='business_field' where `no`='business_form_field';
update `business_entity` set `no`='business_index' where `no`='business_form_index';
update `business_entity` set `no`='business_page' where `no`='business_form_page';
update `business_entity` set `no`='business_page_component' where `no`='business_form_page_component';
update `business_entity` set `no`='business_page_export_setting' where `no`='business_form_page_export_setting';
update `business_entity` set `no`='business_page_solution' where `no`='business_form_page_solution';
update `business_entity` set `no`='business_page_type' where `no`='business_form_page_type';
update `business_entity` set `no`='business_plugin' where `no`='business_form_plugin';
update `business_entity` set `no`='business_service' where `no`='business_form_service';
update `business_entity` set `no`='business_table' where `no`='business_form_table';

update `business_table` set `table`='business_operation' where `table`='business_app_operation';
update `business_table` set `table`='business_component' where `table`='business_form_component';
update `business_table` set `table`='business_entity' where `table`='business_form_entity';
update `business_table` set `table`='business_event' where `table`='business_form_event';
update `business_table` set `table`='business_field' where `table`='business_form_field';
update `business_table` set `table`='business_index' where `table`='business_form_index';
update `business_table` set `table`='business_page' where `table`='business_form_page';
update `business_table` set `table`='business_page_component' where `table`='business_form_page_component';
update `business_table` set `table`='business_page_export_setting' where `table`='business_form_page_export_setting';
update `business_table` set `table`='business_page_solution' where `table`='business_form_page_solution';
update `business_table` set `table`='business_page_type' where `table`='business_form_page_type';
update `business_table` set `table`='business_plugin' where `table`='business_form_plugin';
update `business_table` set `table`='business_service' where `table`='business_form_service';
update `business_table` set `table`='business_table' where `table`='business_form_table';

update `business_index` set `table`='business_operation' where `table`='business_app_operation';
update `business_index` set `table`='business_component' where `table`='business_form_component';
update `business_index` set `table`='business_entity' where `table`='business_form_entity';
update `business_index` set `table`='business_event' where `table`='business_form_event';
update `business_index` set `table`='business_field' where `table`='business_form_field';
update `business_index` set `table`='business_index' where `table`='business_form_index';
update `business_index` set `table`='business_page' where `table`='business_form_page';
update `business_index` set `table`='business_page_component' where `table`='business_form_page_component';
update `business_index` set `table`='business_page_export_setting' where `table`='business_form_page_export_setting';
update `business_index` set `table`='business_page_solution' where `table`='business_form_page_solution';
update `business_index` set `table`='business_page_type' where `table`='business_form_page_type';
update `business_index` set `table`='business_plugin' where `table`='business_form_plugin';
update `business_index` set `table`='business_service' where `table`='business_form_service';
update `business_index` set `table`='business_table' where `table`='business_form_table';

update `business_field` set `table`='business_operation' where `table`='business_app_operation';
update `business_field` set `table`='business_component' where `table`='business_form_component';
update `business_field` set `table`='business_entity' where `table`='business_form_entity';
update `business_field` set `table`='business_event' where `table`='business_form_event';
update `business_field` set `table`='business_field' where `table`='business_form_field';
update `business_field` set `table`='business_index' where `table`='business_form_index';
update `business_field` set `table`='business_page' where `table`='business_form_page';
update `business_field` set `table`='business_page_component' where `table`='business_form_page_component';
update `business_field` set `table`='business_page_export_setting' where `table`='business_form_page_export_setting';
update `business_field` set `table`='business_page_solution' where `table`='business_form_page_solution';
update `business_field` set `table`='business_page_type' where `table`='business_form_page_type';
update `business_field` set `table`='business_plugin' where `table`='business_form_plugin';
update `business_field` set `table`='business_service' where `table`='business_form_service';
update `business_field` set `table`='business_table' where `table`='business_form_table';

/*
05.10
信晨DOMS升级3.0需要处理的数据：
1、business表需要变更object的命名空间，调标准实例的更新为null，其他的命名空间去掉domain\extends\standard
2、task表的source_entity需要替换命名空间，同1。
3、system_app的两个应用name都改为doms
*/
update business set object=null where object = 'domain\\business\\form\\instance\\Instance';
update business set object = replace(object, 'domain\\extend\\standard\\', '');
update task set source_entity = replace(source_entity, 'domain\\extend\\standard\\', '');
update task set source_entity=CONCAT('solution\\doms\\',source_entity) where source_entity is not null and source_entity<>'domain\\workflow\\instance\\task\\EventHandler' and source_entity not like 'solution%';
update system_app set name = 'doms';

-- 05.07 数据库部分字段使用float类型存在的问题[float(12,3)]：精度不准，将其修改为decimal(12,2)
-- 有发现则修改
ALTER TABLE `payment_bill` modify column `total_amount` decimal(12,2) DEFAULT NULL;
ALTER TABLE `payment_bill` modify column `amount` decimal(12,2) DEFAULT NULL;
ALTER TABLE `payment_bill` modify column `payable_expenses` decimal(12,2) DEFAULT 0.00;
ALTER TABLE `payment_bill` modify column `payment_expenses` decimal(12,2) DEFAULT NULL;
ALTER TABLE `payment_bill` modify column `received_persen` decimal(12,2) DEFAULT 0.00;
ALTER TABLE `payment_bill` modify column `received_amount` decimal(12,2) DEFAULT 0.00;
ALTER TABLE `payment_bill` modify column `total_expenses` decimal(12,2) DEFAULT NULL;

-- 04.25兼容标准业务对象
ALTER TABLE `business_form_page` add column `data_status` int(10) unsigned NOT NULL default 2 comment '数据状态';
ALTER TABLE `business_form_page` add column `created_by` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作人';
ALTER TABLE `business_form_page` add column `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间';
ALTER TABLE `business_form_page` add column `updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间';
ALTER TABLE `business_form_page` add column `updated_by` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改人';



-- 2021.04.19
-- 任务节点增加任务描述字段
ALTER TABLE `project_plan_node` add column `node_description` text COMMENT '任务描述' after `task_template_id`;

-- 2021.04.07
-- 加项目看板到菜单  /project/dashboard
INSERT INTO `business_app_operation` VALUES ('DTJWH6Hf',1,'sbNpQhJR','projecStat','business/form-custom/sbNpQhJR/projecStat','项目看板',0);

-- 2、权限：接口地址, #route为PHP转换后的路由#
INSERT INTO `system_perm` (name,app_id,business_id,operation_id,route,sort_order,created_at,updated_at) VALUES
('项目看板',1,'sbNpQhJR','DTJWH6Hf','business/instance/form/operation/custom/projecStat',0,1614323815,1614323815);

-- 3、菜单：客户端菜单地址
INSERT INTO `system_app_menu` VALUES
('320', '1',6, '2', '6,320', '项目看板', '/project/dashboard', '999', '1', null, null, '0', '1577185097', '1577185912');



-- 20210416增加：加站商机
INSERT INTO `crm_opportunity_type` VALUES ('3', '2', '加站商机', '1', '1', '1', '1');
INSERT INTO `crm_opportunity_decisive_factor_template` VALUES
('11', '2', '商机确认', '3', '1618554740', '73', '1618554740', '73'), ('12', '2', '商务报价', '3', '1618554759', '73', '1618554759', '73'), ('13', '2', '合同签订', '3', '1618554769', '73', '1618554769', '73'), ('14', '2', '收款确认', '3', '1618554775', '73', '1618554775', '73');
INSERT INTO `crm_opportunity_stage_template` VALUES
('3', '2', '3', '加站商机', '3', '1', '1', '1', '1', '1');

INSERT INTO `crm_opportunity_stage_template_entry` VALUES
('30', '3', '1', '0', '1', '需求阶段', '1', '1', '1'),
('31', '3', '2', '0', '1', '商务阶段', null, '1', null),
('32', '3', '3', '30', '2', '商机确认', '已经初次上门、获取关键联系人信息、初步了解需求。', '1', '{\r\n\"operation\": [ \"partya\",\"demand\" ]\r\n}'),

('33', '3', '4', '31', '2', '商务报价', '客户认可售前方案，整理报价方案。', '1', '{\r\n\"operation\": [ \"amount\" ]\r\n}'),
('34', '3', '5', '31', '2', '合同签订', '合同内部审批、提交客户审批、签订合同。', '1', '{\r\n\"operation\": [ \"win\" ]\r\n}'),
('35', '3', '6', '31', '2', '收款确认', '确认具体的收款时间', null, '{\r\n\"operation\": [ \"amount\" ]\r\n}');


-- 2021.02.25 增加全局操作日志
-- 集成平台用：有些项目的表结构不是这样的，所以要修改
CREATE TABLE `system_setting` (
  `variable_type` tinyint(3) unsigned NOT NULL COMMENT '变量类型：1.string 2.int 3.float 4.bool 5. json',
  `variable_name` varchar(64) NOT NULL COMMENT '变量名称',
  `variable_value` text NOT NULL COMMENT '变量值',
  `created_at` int(10) unsigned NOT NULL COMMENT '创建时间',
  `updated_at` int(10) unsigned NOT NULL COMMENT '更新时间',
  `describe` varchar(255) DEFAULT NULL,
  `group` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统全局设置';

-- ----------------------------
--  Records of `system_setting`
-- ----------------------------
BEGIN;
INSERT INTO `system_setting` VALUES ('1', 'budgetManager', '79', '1', '1', null, null), ('1', 'budgetPaidAmountReportUser', '1,74,96', '1', '1', null, null), ('1', 'copyright', '<strong>Copyright © 2008-2020 <a href=\"thincent.com\">信晨科技 </a>.</strong> All rights reserved.', '1530598495', '1576846858', null, null), ('2', 'defaultClueResponsibleBy', '74', '1', '1', null, null), ('1', 'domain', 'scrm.93wj.com', '1524119267', '1576846788', null, null), ('2', 'installed_at', '0', '1524119267', '1524119267', null, null), ('2', 'isAllowWxAppAutoBindMobile', '1', '1', '1', null, null), ('1', 'name', 'SCRM', '1530598495', '1530598495', null, null), ('2', 'operation_log_max_day', '360', '1576846858', '1576846858', '保留最近多少天的记录，0不限制', null), ('2', 'operation_log_max_record', '5000000', '1576846858', '1576846858', '最多保留多少条记录，0不限制', null), ('2', 'upgraded_at', '0', '1524119267', '1524119267', null, null), ('1', 'version', '1.0', '1524119267', '1524119267', null, null), ('1', 'wps_appid', '8e3a57070c204e9fb61de8a94ddb9344', '1', '1', null, null), ('1', 'wps_appkey', 'd6e7bfb9dd634b779b95bc08aa3c090a', '1', '1', null, null);
COMMIT;

-- doms用：结构相同，直接插入
INSERT INTO `system_setting` VALUES
(2, 'operation_log_max_record', 5000000, '1576846858', '1576846858', '最多保留多少条记录，0不限制',null),
(2, 'operation_log_max_day', 360, '1576846858', '1576846858', '保留最近多少天的记录，0不限制',null);

CREATE TABLE `system_operation_log` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `data_status` int(10) unsigned NOT NULL default 2 comment '数据状态',
    `client_id` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '端应用id',
    `business_id` binary(8) DEFAULT NULL COMMENT '对象ID',
    `business_operation` varchar(32) DEFAULT NULL COMMENT '对象操作',
    `ip` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT 'ip',
    `url` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '请求地址不含域名',
    `url_alias` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '请求地址对应的别名/模块',
    `action` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '请求方法:post,delete,put...',
    `body` longtext CHARACTER SET utf8 COMMENT '请求的body',
    `header` text CHARACTER SET utf8 COMMENT '请求头',
    `response_result` longtext CHARACTER SET utf8 COMMENT '响应返回',
    `remark` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '主动写的说明',
    `created_by` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作人',
    `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
    `updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
    `updated_by` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改人',
    PRIMARY KEY (`id`),
    KEY (`client_id`,`created_at`),
    KEY (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='操作日志';

-- doms：缺少的权限
INSERT INTO `system_app_menu` VALUES
('300', '1', '13', '2', '13,300', '操作日志', '/business-form/list/ToKLHGI4/xFChyBdn', '1', '1', null, null, '0', '1', '1');

-- 集成平台：缺少的权限
INSERT INTO `system_app_menu` VALUES
('13', '1', '0', '1', '13', '系统管理', null, '96', '1', null, null, '0', '1', '1'),
('49', '1', '13', '2', '13,49', '企业用户', '/system/corp', '1', '0', null, null, '0', '1', '1'),
('82', '1', '13', '2', '13,82', '角色权限', '/system/role/list', '2', '0', null, null, '0', '1', '1'),
('300', '1', '13', '2', '13,300', '操作日志', '/business-form/list/ToKLHGI4/xFChyBdn', '1', '1', null, null, '0', '1', '1');

-- 导出业务对象相关数据
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="id='ToKLHGI4'" thincent-cloud-doms business >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_app_operation >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_component >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_entity >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_event >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_field >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_page >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_page_export_setting >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_plugin >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_service >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_form_table >> /data/doms.sql
-- /usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms business_print_template >> /data/doms.sql
/usr/local/mysql/bin/mysqldump -uthincent_db_user -p -h203.195.198.23  --where="business_id='ToKLHGI4'" thincent-cloud-doms system_perm >> /data/doms.sql

-- 插入
INSERT INTO `business` VALUES ('ToKLHGI4','system_operation_log','操作日志','FORM','yGRrw3qG',1614323815,1,1614323815,1,1,'domain\\extend\\standard\\form\\system\\SystemOperationLog',NULL,44,0);
INSERT INTO `business_app_operation` VALUES ('DTJWH6Hy',2,'ToKLHGI4','update','business/instance/form/operation/update','更新',1),('Dge0a167',2,'ToKLHGI4','disable','business/instance/form/operation/disable','禁用',1),('E1pM9Nm0',2,'ToKLHGI4','export','business/instance/form/operation/export','导出',1),('ENv6dXfO',1,'ToKLHGI4','create','business/instance/form/operation/create','创建',1),('Ho9Q66sk',2,'ToKLHGI4','find-one','business/instance/form/operation/find-one','详情',1),('PAH3OLYB',2,'ToKLHGI4','delete','business/instance/form/operation/delete','删除',1),('RIYAESvJ',2,'ToKLHGI4','search','business/instance/form/operation/search','搜索',1),('Rhfh3Pf6',1,'ToKLHGI4','search','business/instance/form/operation/search','搜索',1),('V7PZppzM',1,'ToKLHGI4','disable','business/instance/form/operation/disable','禁用',1),('VT8Ly65i',1,'ToKLHGI4','import','business/instance/form/operation/import','导入',1),('YG00qWOV',1,'ToKLHGI4','delete','business/instance/form/operation/delete','删除',1),('ZBUnent3',1,'ToKLHGI4','export','business/instance/form/operation/export','导出',1),('ZYL0p8nP',2,'ToKLHGI4','create','business/instance/form/operation/create','创建',1),('dRjpzmm6',2,'ToKLHGI4','invalid','business/instance/form/operation/invalid','作废',1),('hseQOgFK',2,'ToKLHGI4','enable','business/instance/form/operation/enable','启用',1),('k4H2T672',1,'ToKLHGI4','invalid','business/instance/form/operation/invalid','作废',1),('n65tzj2W',1,'ToKLHGI4','update','business/instance/form/operation/update','更新',1),('nPsdjOdQ',2,'ToKLHGI4','import','business/instance/form/operation/import','导入',1),('uQcS60JC',1,'ToKLHGI4','find-one','business/instance/form/operation/find-one','详情',1),('wh3ddppX',1,'ToKLHGI4','enable','business/instance/form/operation/enable','启用',1);

INSERT INTO `business_form_entity` VALUES ('A2m92xJ4','created_by','ToKLHGI4','wNDbGUF9','创建人','FIELD',1,0,'{\"relationQuery\":{\"parser\":\"FieldJoinTable\",\"source\":\"system_operation_log\",\"sourceField\":\"created_by\",\"target\":\"system_user_profile\",\"targetField\":\"user_id\",\"targetDisplayField\":\"realname\"}}',1614323815,1614323815,3),('EYwlh8Sz','url_alias','ToKLHGI4','wNDbGUF9','模块','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"url_alias\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324076,1614324076,9994),('IqVARC7H','action','ToKLHGI4','wNDbGUF9','请求方法','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"action\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324094,1614324094,9993),('MovTI3yI','ip','ToKLHGI4','wNDbGUF9','ip地址','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"ip\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324040,1614324040,9996),('Oya9dyJH','client_id','ToKLHGI4','wNDbGUF9','端应用id','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"client_id\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614323982,1614323982,9999),('QhU3Wo4s','data_status','ToKLHGI4','wNDbGUF9','数据状态','FIELD',1,0,'{\"statusLabelEnum\":{\"parser\":\"StatusLabelEnum\",\"source\":\"system_operation_log\",\"sourceField\":\"data_status\",\"data\":[{\"value\":0,\"text\":\"失效\",\"statusIcon\":\"DISABLE\"},{\"value\":1,\"text\":\"暂存\",\"statusIcon\":\"PAUSE\"},{\"value\":2,\"text\":\"生效\",\"statusIcon\":\"ENABLE\"}]}}',1614323815,1614323815,10001),('VvsoOlie','id','ToKLHGI4','wNDbGUF9','主键','FIELD',1,0,'{}',1614323815,1614323815,10002),('X2HLG4Mb','remark','ToKLHGI4','wNDbGUF9','说明','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"remark\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324171,1614324171,9989),('aWEGIi7U','created_at','ToKLHGI4','wNDbGUF9','创建日期','FIELD',1,0,'{}',1614323815,1614323815,4),('bU73MXeQ','url','ToKLHGI4','wNDbGUF9','请求地址','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"url\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324056,1614324056,9995),('e51Wkimi','body','ToKLHGI4','wNDbGUF9','body','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"body\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324122,1614324122,9992),('i1ovJxKL','business_operation','ToKLHGI4','wNDbGUF9','对象操作','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"business_operation\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324023,1614324023,9997),('m1mJnoCA','updated_by','ToKLHGI4','wNDbGUF9','更新人','FIELD',1,0,'{}',1614391903,1614323815,1),('muTkRY5g','updated_at','ToKLHGI4','wNDbGUF9','更新日期','FIELD',1,0,'{}',1614323815,1614323815,2),('nbXgL0B2','header','ToKLHGI4','wNDbGUF9','请求头','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"header\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324137,1614324137,9991),('qqUWKoxe','response_result','ToKLHGI4','wNDbGUF9','响应','FIELD',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"response_result\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614324152,1614324152,9990),('wCUBotpm','business_id','ToKLHGI4','wNDbGUF9','对象','FIELD',0,0,'{\n    \"relationQuery\":{\n        \"parser\":\"FieldJoinTable\",\n        \"source\":\"system_operation_log\",\n        \"sourceField\":\"business_id\",\n        \"target\":\"business\",\n        \"targetField\":\"id\",\n        \"targetDisplayField\":\"name\"\n    }\n}',1614329652,1614323999,9998),('wNDbGUF9','system_operation_log','ToKLHGI4','0\0\0\0\0\0\0\0','操作日志','TABLE',0,0,'{\"extendInfo\":{\"parser\":\"EntityExtendInfo\",\"table\":\"system_operation_log\",\"paimaryField\":\"id\",\"displayFields\":[\"name\"],\"displayFieldsSeparator\":\"-\"},\"dataStatus\":{\"parser\":\"DataStatus\",\"defaultValue\":2}}',1614323815,1614323815,10000);

INSERT INTO `business_form_field` VALUES ('EGlIND0U','m1mJnoCA','system_operation_log','ToKLHGI4','updated_by','更新人',0,'int','11',NULL,'system preset field',0,0,NULL,NULL,1614323815,1614323815,1),('GSzNuNsU','EYwlh8Sz','system_operation_log','ToKLHGI4','url_alias','模块',0,'varchar','32','','',0,0,NULL,NULL,1614324076,1614324076,9994),('H1997pXo','e51Wkimi','system_operation_log','ToKLHGI4','body','body',0,'text','0','','',0,0,NULL,NULL,1614324122,1614324122,9992),('J53mQBEa','nbXgL0B2','system_operation_log','ToKLHGI4','header','请求头',0,'text','0','','',0,0,NULL,NULL,1614324137,1614324137,9991),('Qo1R44Hu','QhU3Wo4s','system_operation_log','ToKLHGI4','data_status','数据状态',0,'int','11','2','system preset field',0,0,NULL,NULL,1614323815,1614323815,10001),('TGKdhSL8','VvsoOlie','system_operation_log','ToKLHGI4','id','主键',1,'int','11','AUTO_INCREMENT','system preset field',0,0,NULL,NULL,1614323815,1614323815,10002),('Wkjqiu0B','bU73MXeQ','system_operation_log','ToKLHGI4','url','请求地址',0,'varchar','255','','',0,0,NULL,NULL,1614324056,1614324056,9995),('XBV5xwbh','muTkRY5g','system_operation_log','ToKLHGI4','updated_at','更新日期',0,'int','11',NULL,'system preset field',0,0,NULL,NULL,1614323815,1614323815,2),('YmvSEqXs','wCUBotpm','system_operation_log','ToKLHGI4','business_id','对象ID',0,'binary','8','','',0,0,NULL,NULL,1614323999,1614323999,9998),('aatPg8Yt','qqUWKoxe','system_operation_log','ToKLHGI4','response_result','响应',0,'text','0','','',0,0,NULL,NULL,1614324152,1614324152,9990),('brJqZUht','IqVARC7H','system_operation_log','ToKLHGI4','action','请求方法',0,'varchar','16','','',0,0,NULL,NULL,1614324094,1614324094,9993),('cY8sAB4Y','A2m92xJ4','system_operation_log','ToKLHGI4','created_by','创建人',1,'int','11',NULL,'system preset field',0,0,NULL,NULL,1614323815,1614323815,3),('fPRhyLXn','i1ovJxKL','system_operation_log','ToKLHGI4','business_operation','对象操作',0,'varchar','32','','',0,0,NULL,NULL,1614324023,1614324023,9997),('gdcQyp2H','Oya9dyJH','system_operation_log','ToKLHGI4','client_id','端应用id',1,'varchar','16','','',0,0,NULL,NULL,1614323982,1614323982,9999),('j6dxg5NJ','aWEGIi7U','system_operation_log','ToKLHGI4','created_at','创建日期',0,'int','11',NULL,'system preset field',0,0,NULL,NULL,1614323815,1614323815,4),('usNe1066','MovTI3yI','system_operation_log','ToKLHGI4','ip','ip地址',1,'varchar','16','','',0,0,NULL,NULL,1614324040,1614324040,9996),('zEo1MLAt','X2HLG4Mb','system_operation_log','ToKLHGI4','remark','说明',0,'varchar','255','','',0,0,NULL,NULL,1614324171,1614324171,9989);

INSERT INTO `business_form_page` VALUES ('xFChyBdn','ToKLHGI4',1,'操作日志',1,'{\"pageName\":\"操作日志\",\"multiSelect\":false,\"editPageUrl\":\"/business-form/edit/<businessId>/<dataId>\",\"detailPageUrl\":\"/business-form/detail/<businessId>/<dataId>\",\"addButton\":false,\"copyButton\":false,\"exportButton\":true,\"importButton\":false,\"batchUpdateButton\":false,\"printButton\":true,\"disableButton\":true,\"enableButton\":true,\"deleteButton\":false,\"workflowButton\":false,\"extendButton\":[],\"pageSize\":50,\"pageSizeOptions\":[\"50\",\"100\",\"200\",\"500\",\"1000\",\"2000\",\"5000\"],\"editPageJumpMode\":\"{\\\"mode\\\":\\\"newTab\\\"}\",\"rowKey\":\"system_operation_log_id\",\"searchFields\":[],\"displayFields\":[{\"seq\":0,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_id\",\"filter\":false,\"dataKey\":\"system_operation_log.id\",\"sorter\":true,\"title\":\"主键\",\"width\":50,\"customRender\":\"\"},{\"seq\":1,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_data_status\",\"filter\":false,\"dataKey\":\"system_operation_log.data_status\",\"sorter\":true,\"title\":\"数据状态data_status\",\"width\":100,\"customRender\":\"\"},{\"seq\":2,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_data_status_text\",\"filter\":false,\"dataKey\":\"case system_operation_log.data_status when 0 then \'失效\' when 1 then \'暂存\' when 2 then \'生效\' else \'\' end\",\"sorter\":true,\"title\":\"数据状态\",\"width\":100,\"customRender\":\"\"},{\"seq\":3,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_client_id\",\"filter\":true,\"filterExtend\":{\"component\":\"SelectorGeneral\",\"params\":{\"options\":[{\"value\":\"admin\",\"text\":\"管理后台\"},{\"value\":\"home\",\"text\":\"小程序端\"},{\"value\":\"openapi\",\"text\":\"openapi\"}]},\"operator\":\"eq\"},\"dataKey\":\"system_operation_log.client_id\",\"sorter\":false,\"title\":\"端应用\",\"width\":\"60\",\"customRender\":\"\"},{\"seq\":8,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_url_alias\",\"filter\":false,\"dataKey\":\"system_operation_log.url_alias\",\"sorter\":false,\"title\":\"模块\",\"width\":100,\"customRender\":\"\"},{\"seq\":4,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_business_id\",\"filter\":true,\"filterExtend\":{\"component\":\"SelectorBusiness\"},\"dataKey\":\"system_operation_log.business_id\",\"sorter\":false,\"title\":\"对象\",\"width\":100,\"customRender\":\"\"},{\"seq\":4,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"business_business_id_name\",\"filter\":false,\"filterExtend\":{\"component\":\"SelectorBusiness\"},\"dataKey\":\"business_business_id.name\",\"sorter\":false,\"title\":\"对象\",\"width\":\"80\",\"customRender\":\"\"},{\"seq\":5,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_business_operation\",\"filter\":true,\"dataKey\":\"system_operation_log.business_operation\",\"filterExtend\":{\"component\":\"InputText\",\"operator\":\"eq\"},\"sorter\":false,\"title\":\"对象操作\",\"width\":\"80\",\"customRender\":\"\"},{\"seq\":14,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_created_at\",\"filter\":true,\"filterExtend\":{\"component\":\"InputDatetimeRange\",\"operator\":\"between\"},\"dataKey\":\"system_operation_log.created_at\",\"sorter\":true,\"title\":\"创建日期\",\"width\":100,\"customRender\":\"DatetimeRender\"},{\"seq\":7,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_url\",\"filter\":true,\"dataKey\":\"system_operation_log.url\",\"filterExtend\":{\"component\":\"InputText\",\"operator\":\"llike\"},\"sorter\":false,\"title\":\"请求地址\",\"width\":\"200\",\"customRender\":\"\"},{\"seq\":9,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_action\",\"filter\":false,\"dataKey\":\"system_operation_log.action\",\"sorter\":false,\"title\":\"请求方法\",\"width\":\"50\",\"customRender\":\"\"},{\"seq\":10,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_body\",\"filter\":true,\"dataKey\":\"system_operation_log.body\",\"sorter\":false,\"title\":\"body\",\"width\":\"200\",\"customRender\":\"LongTextRender\"},{\"seq\":11,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_header\",\"filter\":false,\"dataKey\":\"system_operation_log.header\",\"sorter\":false,\"title\":\"请求头\",\"width\":\"120\",\"customRender\":\"LongTextRender\"},{\"seq\":12,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_response_result\",\"filter\":false,\"dataKey\":\"system_operation_log.response_result\",\"sorter\":false,\"title\":\"响应\",\"width\":\"200\",\"customRender\":\"LongTextRender\"},{\"seq\":13,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_remark\",\"filter\":false,\"dataKey\":\"system_operation_log.remark\",\"sorter\":false,\"title\":\"说明\",\"width\":\"80\",\"customRender\":\"\"},{\"seq\":15,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_created_by\",\"filter\":false,\"dataKey\":\"system_operation_log.created_by\",\"sorter\":true,\"title\":\"创建人created_by\",\"width\":100,\"customRender\":\"\"},{\"seq\":16,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_user_profile_created_by_realname\",\"filter\":true,\"dataKey\":\"system_user_profile_created_by.realname\",\"sorter\":false,\"title\":\"创建人\",\"width\":\"80\",\"customRender\":\"\"},{\"seq\":6,\"visible\":true,\"align\":\"left\",\"dataIndex\":\"system_operation_log_ip\",\"filter\":true,\"dataKey\":\"system_operation_log.ip\",\"filterExtend\":{\"component\":\"InputText\",\"operator\":\"eq\"},\"sorter\":false,\"title\":\"ip\",\"width\":\"80\",\"customRender\":\"\"},{\"seq\":17,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_updated_at\",\"filter\":false,\"dataKey\":\"system_operation_log.updated_at\",\"sorter\":true,\"title\":\"更新日期\",\"width\":100,\"customRender\":\"\"},{\"seq\":18,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_operation_log_updated_by\",\"filter\":false,\"dataKey\":\"system_operation_log.updated_by\",\"sorter\":true,\"title\":\"更新人updated_by\",\"width\":100,\"customRender\":\"\"},{\"seq\":19,\"visible\":false,\"align\":\"left\",\"dataIndex\":\"system_user_profile_updated_by_realname\",\"filter\":false,\"dataKey\":\"system_user_profile_updated_by.realname\",\"sorter\":true,\"title\":\"更新人\",\"width\":100,\"customRender\":\"\"}]}',0,NULL);

INSERT INTO `business_form_table` VALUES ('system_operation_log','wNDbGUF9','ToKLHGI4','InnoDB','utf8','uft8_general_ci',0,NULL,NULL,1614323815,1614323815,1,10000);

INSERT INTO `system_perm` VALUES (3656,'创建',1,'ToKLHGI4','ENv6dXfO','business/instance/form/operation/create',0,1614323815,1614323815),(3657,'创建',2,'ToKLHGI4','ZYL0p8nP','business/instance/form/operation/create',0,1614323815,1614323815),(3658,'删除',1,'ToKLHGI4','YG00qWOV','business/instance/form/operation/delete',0,1614323815,1614323815),(3659,'删除',2,'ToKLHGI4','PAH3OLYB','business/instance/form/operation/delete',0,1614323815,1614323815),(3660,'更新',1,'ToKLHGI4','n65tzj2W','business/instance/form/operation/update',0,1614323815,1614323815),(3661,'更新',2,'ToKLHGI4','DTJWH6Hy','business/instance/form/operation/update',0,1614323815,1614323815),(3662,'启用',1,'ToKLHGI4','wh3ddppX','business/instance/form/operation/enable',0,1614323815,1614323815),(3663,'启用',2,'ToKLHGI4','hseQOgFK','business/instance/form/operation/enable',0,1614323815,1614323815),(3664,'禁用',1,'ToKLHGI4','V7PZppzM','business/instance/form/operation/disable',0,1614323815,1614323815),(3665,'禁用',2,'ToKLHGI4','Dge0a167','business/instance/form/operation/disable',0,1614323815,1614323815),(3666,'作废',1,'ToKLHGI4','k4H2T672','business/instance/form/operation/invalid',0,1614323815,1614323815),(3667,'作废',2,'ToKLHGI4','dRjpzmm6','business/instance/form/operation/invalid',0,1614323815,1614323815),(3668,'导出',1,'ToKLHGI4','ZBUnent3','business/instance/form/operation/export',0,1614323815,1614323815),(3669,'导出',2,'ToKLHGI4','E1pM9Nm0','business/instance/form/operation/export',0,1614323815,1614323815),(3670,'导入',1,'ToKLHGI4','VT8Ly65i','business/instance/form/operation/import',0,1614323815,1614323815),(3671,'导入',2,'ToKLHGI4','nPsdjOdQ','business/instance/form/operation/import',0,1614323816,1614323816),(3672,'详情',1,'ToKLHGI4','uQcS60JC','business/instance/form/operation/find-one',0,1614323816,1614323816),(3673,'详情',2,'ToKLHGI4','Ho9Q66sk','business/instance/form/operation/find-one',0,1614323816,1614323816),(3674,'搜索',1,'ToKLHGI4','Rhfh3Pf6','business/instance/form/operation/search',0,1614323816,1614323816),(3675,'搜索',2,'ToKLHGI4','RIYAESvJ','business/instance/form/operation/search',0,1614323816,1614323816);



-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- 慎用      删除指定业务对象对象的无效表数据      慎用
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

delete from `business` where id='HAY3TKec';
-- delete t1 from business t1 left join business_group t2 on t1.group_id=t2.id where t2.id is null;

-- entity
-- select t2.id,t1.*
delete t1
from business_entity t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- field
-- select t2.id,t1.*
delete t1
from business_field t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_operation t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_component t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_event t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_page t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_plugin t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_table t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_index t1 left join business_entity t2 on t1.form_entity_id=t2.id
where t2.id is null;

-- select t2.id,t1.*
delete t1
from business_page_export_setting t1 left join business t2 on t1.business_id=t2.id
where t2.id is null;

delete t1
from business_page_solution t1 left join business_page t2 on t1.page_id=t2.id
where t2.id is null;

-- 权限相关
delete t1 from system_perm t1 left join business t2 on t1.business_id=t2.id where t2.id is null;
delete t1 from system_perm t1 left join business_operation t2 on t1.operation_id=t2.id where t2.id is null;
delete t1 from system_role_perm t1 left join system_role t2 on t1.role_id=t2.id where t2.id is null;
delete t1 from system_role_perm t1 left join system_perm t2 on t1.perm_id=t2.id where t2.id is null;
delete t1 from system_user_role t1 left join system_role t2 on t1.role_id=t2.id where t2.id is null;
delete t1 from system_user_role t1 left join system_user t2 on t1.user_id=t2.id where t2.id is null;
delete t1 from system_user_profile t1 left join system_user t2 on t1.user_id=t2.id where t2.id is null;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- 慎用      工作流数据删除      慎用
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- delete from workflow where start_event_target not in(select id from business);
-- delete from `workflow` where id=21;
-- select t2.id,t1.*
delete t1 from workflow_business_form t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_edge t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_inner_form t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_node t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_node_after_action t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_node_business_form t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_node_inner_form t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_node_variable t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_variable t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_version t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;

delete t1 from workflow_instance t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_instance_business_form t1 left join workflow t2 on t1.workflow_id=t2.id where t2.id is null;
delete t1 from workflow_instance_business_form t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_instance_edge t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_instance_log t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_instance_node t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_node_variable t1 left join workflow_node t2 on t1.node_id=t2.id where t2.id is null;
delete t1 from workflow_instance_node_personnel t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_instance_node_variable t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;
delete t1 from workflow_instance_variable t1 left join workflow_instance t2 on t1.instance_id=t2.id where t2.id is null;


-- 相关用户
-- delete from corp where id >12;
-- delete from corp_department where id >20;
-- delete t1 from corp_user t1 left join system_user t2 on t1.user_id=t2.id where t2.id is null;
-- delete t1 from corp_department_user t1 left join system_user t2 on t1.user_id=t2.id where t2.id is null;
-- delete t1 from system_app_client_user t1 left join system_user t2 on t1.user_id=t2.id where t2.id is null;
-- delete t1 from system_app_client_wxapp_user t1 left join system_app_client_user t2 on t1.client_user_id=t2.id where t2.id is null;
-- delete t1 from system_app_client_wxwork_user t1 left join system_app_client_user t2 on t1.client_user_id=t2.id where t2.id is null;



-- 数据脱敏处理，有完善的在这里增加
UPDATE corp SET `name`=CONCAT(LEFT(`name`,3), '*******' ,RIGHT(`name`,4));
UPDATE corp_user SET `name`=(if(LENGTH(`name`)>6,CONCAT(LEFT(`name`,1), '**' ),CONCAT(LEFT(`name`,1), '*' ))),
                     `alias`=(if(LENGTH(alias)>6,CONCAT(LEFT(alias,1), '**' ),CONCAT(LEFT(alias,1), '*' ))),
                     `mobile` = CONCAT(LEFT(mobile,3), '****' ,RIGHT(mobile,4)),
                     `email`=CONCAT(LEFT(`email`,3), '*******' ,RIGHT(`email`,13));
UPDATE corp_department SET `name`=CONCAT(LEFT(`name`,3), '*******' ,RIGHT(`name`,4));
-- 有唯一验证
UPDATE customer SET `name`=CONCAT(LEFT(`name`,3), '*******',floor(1+rand()*9) ,RIGHT(`name`,4),floor(1+rand()*1000)),
                    `address` = CONCAT(LEFT(address,3), '****' ,RIGHT(address,4)),
                    `contact_name`=CONCAT(LEFT(`contact_name`,1), '**' ),
                    `contact_phone` = CONCAT(LEFT(contact_phone,3), '****' ,RIGHT(contact_phone,4));
UPDATE customer_contact SET `name`=CONCAT(LEFT(`name`,1), '**' ),
                            `phone` = CONCAT(LEFT(phone,3), '****' ,RIGHT(phone,4)),
                            `email`=CONCAT(LEFT(`email`,3), '*******' ,RIGHT(`email`,7));
UPDATE project SET `name`=CONCAT(LEFT(`name`,1), '*******' ,RIGHT(`name`,4),floor(1+rand()*1000)),
                   `customer_contact_name`=CONCAT(LEFT(`customer_contact_name`,1), '**' ),
                   `customer_contact_phone` = CONCAT(LEFT(customer_contact_phone,3), '****' ,RIGHT(customer_contact_phone,4)),
                   `customer_address` = CONCAT(LEFT(customer_address,3), '*******' ,RIGHT(customer_address,3)),
                   `abbreviation` = CONCAT(LEFT(abbreviation,1), '****');
UPDATE project_team SET `name`=CONCAT(LEFT(`name`,1), '**' ),
                        `mobile` = CONCAT(LEFT(mobile,3), '****' ,RIGHT(mobile,4)),
                        `email`=CONCAT(LEFT(`email`,3), '*******' ,RIGHT(`email`,7));
UPDATE system_user_profile SET `nickname`=(if(LENGTH(nickname)>6,CONCAT(LEFT(nickname,1), '**' ),CONCAT(LEFT(nickname,1), '*' ))),
                               `realname`=(if(LENGTH(realname)>6,CONCAT(LEFT(realname,1), '**' ),CONCAT(LEFT(realname,1), '*' ))),
                               `email`=CONCAT(LEFT(`email`,3), '*******' ,RIGHT(`email`,13));

-- 附件：
truncate table `contract_media`;
truncate table `contract_media`;
truncate table `contract_cloud_media`;
truncate table `contract_approval_media`;