insurance_and_fullmoon.sql 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110
  1. -- =============================================
  2. -- 1. 保险公司主表
  3. -- =============================================
  4. DROP TABLE IF EXISTS `biz_insurance_company`;
  5. CREATE TABLE `biz_insurance_company` (
  6. `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  7. `company_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '保险公司名称',
  8. `company_address` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '公司地址',
  9. `contact_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '联系人',
  10. `contact_phone` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '联系电话',
  11. `status` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1' COMMENT '1正常 0停用',
  12. `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  13. `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  14. `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  15. `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  16. `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  17. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  18. `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  19. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  20. `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记:0-正常 1-已删除',
  21. PRIMARY KEY (`id`),
  22. KEY `idx_ins_company` (`tenant_no`,`del_flag`)
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险公司';
  24. -- =============================================
  25. -- 2. 保险公司开票信息
  26. -- =============================================
  27. DROP TABLE IF EXISTS `biz_insurance_company_invoice`;
  28. CREATE TABLE `biz_insurance_company_invoice` (
  29. `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  30. `insurance_company_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '保险公司ID',
  31. `company_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '单位名称',
  32. `tax_no` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '税号',
  33. `company_address` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '单位地址',
  34. `company_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电话',
  35. `bank_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '开户银行',
  36. `bank_account` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '银行账号',
  37. `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  38. `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  39. `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  40. `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  41. `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  42. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  43. `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  44. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  45. `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记:0-正常 1-已删除',
  46. PRIMARY KEY (`id`),
  47. KEY `idx_ins_inv` (`insurance_company_id`,`del_flag`)
  48. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险公司开票信息';
  49. -- =============================================
  50. -- 3. 保险项目价格
  51. -- =============================================
  52. DROP TABLE IF EXISTS `biz_insurance_project_price`;
  53. CREATE TABLE `biz_insurance_project_price` (
  54. `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  55. `insurance_company_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '保险公司ID',
  56. `project_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '项目名称',
  57. `project_fee` decimal(18,2) DEFAULT NULL COMMENT '项目费用(元)',
  58. `status` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1' COMMENT '1正常 0停用',
  59. `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  60. `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  61. `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  62. `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  63. `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  64. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  65. `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  66. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  67. `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记:0-正常 1-已删除',
  68. PRIMARY KEY (`id`),
  69. KEY `idx_ins_proj` (`insurance_company_id`,`del_flag`)
  70. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险项目价格';
  71. -- =============================================
  72. -- 4. 满月待办表扩展(新增 8 个保险字段)
  73. -- =============================================
  74. ALTER TABLE `biz_protocol_fullmoon_todo`
  75. ADD COLUMN `insurance_company_id` varchar(64) DEFAULT NULL COMMENT '保险公司ID' AFTER `insurance_attachments`,
  76. ADD COLUMN `insurance_company_name` varchar(200) DEFAULT NULL COMMENT '保险公司名称(冗余快照)' AFTER `insurance_company_id`,
  77. ADD COLUMN `insurance_type` varchar(200) DEFAULT NULL COMMENT '保险险种' AFTER `insurance_company_name`,
  78. ADD COLUMN `insurance_policy_no` varchar(100) DEFAULT NULL COMMENT '保险单号' AFTER `insurance_type`,
  79. ADD COLUMN `insurance_effective_date` char(8) DEFAULT NULL COMMENT '保险单生效时间YYYYMMDD' AFTER `insurance_policy_no`,
  80. ADD COLUMN `insurance_expiry_date` char(8) DEFAULT NULL COMMENT '保险单失效时间YYYYMMDD' AFTER `insurance_effective_date`,
  81. ADD COLUMN `insurance_subscribed` char(1) DEFAULT '0' COMMENT '是否已订购保险(1是 0否)' AFTER `insurance_expiry_date`,
  82. ADD COLUMN `insurance_fee` decimal(18,2) DEFAULT NULL COMMENT '保单费用(元)' AFTER `insurance_subscribed`;
  83. -- =============================================
  84. -- 5. 菜单数据初始化
  85. -- =============================================
  86. -- 父菜单:细胞存储管理 = 50000000000000000000000000000001 (parent_id=2)
  87. -- 父菜单:基础信息管理 = 120 (parent_id=2)
  88. -- 储存证管理:复用已软删除的 ...06 ID(原"分期缴费预警" del_flag=1)
  89. UPDATE `sys_menu` SET
  90. `menu_name` = '储存证管理',
  91. `path` = 'storage-cert',
  92. `component` = '/cell-storage/storage-cert/index',
  93. `perms` = 'cell-storage:storage-cert:list',
  94. `order_num` = 6,
  95. `del_flag` = 0,
  96. `update_time` = NOW()
  97. WHERE `id` = '50000000000000000000000000000006';
  98. -- 满月待办管理:新插入
  99. INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`)
  100. VALUES ('50000000000000000000000000000008', '满月待办管理', '50000000000000000000000000000001', 'C', 'fullmoon-todo', '/cell-storage/fullmoon-todo/index', 'cell-storage:fullmoon-todo:list', 1, '1', '1', 'zkxw', 7, NULL, '1', '', NOW(), '1', NOW(), 0);
  101. -- 保险公司及项目价格:新插入
  102. INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`)
  103. VALUES ('40000000000000000000000000000007', '保险公司及项目价格', '120', 'C', 'insurance-company', '/basic-info/insurance-company/index', 'basic-info:insurance-company:list', 1, '1', '1', 'zkxw', 6, NULL, '1', '', NOW(), '1', NOW(), 0);