insurance_company_seed.sql 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281
  1. -- =============================================
  2. -- 保险公司及项目价格 — 演示/初始化数据
  3. -- =============================================
  4. -- 前置条件:已执行 docs/sql/insurance_and_fullmoon.sql(建表 + 菜单)
  5. -- 用法:mysql -u... -p... <dbname> < docs/sql/insurance_company_seed.sql
  6. -- 说明:使用固定主键,先删后插,可重复执行;仅写入 tenant_no=zkxw 的演示数据
  7. -- =============================================
  8. SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
  9. -- 固定主键(32 位十六进制,无连字符;变量显式指定与表字段一致的 collation)
  10. SET @ins_co_1 = 'A1B2C3D4E5F6478091A2B3C4D5E6F701' COLLATE utf8mb4_unicode_ci;
  11. SET @ins_co_2 = 'A1B2C3D4E5F6478091A2B3C4D5E6F702' COLLATE utf8mb4_unicode_ci;
  12. SET @ins_co_3 = 'A1B2C3D4E5F6478091A2B3C4D5E6F703' COLLATE utf8mb4_unicode_ci;
  13. SET @ins_inv_1 = 'B1B2C3D4E5F6478091A2B3C4D5E6F701' COLLATE utf8mb4_unicode_ci;
  14. SET @ins_inv_2 = 'B1B2C3D4E5F6478091A2B3C4D5E6F702' COLLATE utf8mb4_unicode_ci;
  15. SET @ins_inv_3 = 'B1B2C3D4E5F6478091A2B3C4D5E6F703' COLLATE utf8mb4_unicode_ci;
  16. SET @ins_pr_1 = 'C1B2C3D4E5F6478091A2B3C4D5E6F701' COLLATE utf8mb4_unicode_ci;
  17. SET @ins_pr_2 = 'C1B2C3D4E5F6478091A2B3C4D5E6F702' COLLATE utf8mb4_unicode_ci;
  18. SET @ins_pr_3 = 'C1B2C3D4E5F6478091A2B3C4D5E6F703' COLLATE utf8mb4_unicode_ci;
  19. SET @ins_pr_4 = 'C1B2C3D4E5F6478091A2B3C4D5E6F704' COLLATE utf8mb4_unicode_ci;
  20. SET @ins_pr_5 = 'C1B2C3D4E5F6478091A2B3C4D5E6F705' COLLATE utf8mb4_unicode_ci;
  21. SET @ins_pr_6 = 'C1B2C3D4E5F6478091A2B3C4D5E6F706' COLLATE utf8mb4_unicode_ci;
  22. SET @admin_id = 'd3000000000000000000000000000001' COLLATE utf8mb4_unicode_ci;
  23. -- 清理旧种子(子表 → 主表;DELETE 使用字面量避免 IN 与变量 collation 混用)
  24. DELETE FROM `biz_insurance_project_price`
  25. WHERE `id` IN (
  26. 'C1B2C3D4E5F6478091A2B3C4D5E6F701',
  27. 'C1B2C3D4E5F6478091A2B3C4D5E6F702',
  28. 'C1B2C3D4E5F6478091A2B3C4D5E6F703',
  29. 'C1B2C3D4E5F6478091A2B3C4D5E6F704',
  30. 'C1B2C3D4E5F6478091A2B3C4D5E6F705',
  31. 'C1B2C3D4E5F6478091A2B3C4D5E6F706'
  32. );
  33. DELETE FROM `biz_insurance_company_invoice`
  34. WHERE `id` IN (
  35. 'B1B2C3D4E5F6478091A2B3C4D5E6F701',
  36. 'B1B2C3D4E5F6478091A2B3C4D5E6F702',
  37. 'B1B2C3D4E5F6478091A2B3C4D5E6F703'
  38. );
  39. DELETE FROM `biz_insurance_company`
  40. WHERE `id` IN (
  41. 'A1B2C3D4E5F6478091A2B3C4D5E6F701',
  42. 'A1B2C3D4E5F6478091A2B3C4D5E6F702',
  43. 'A1B2C3D4E5F6478091A2B3C4D5E6F703'
  44. );
  45. -- =============================================
  46. -- 1. 保险公司主表
  47. -- =============================================
  48. INSERT INTO `biz_insurance_company` (
  49. `id`, `company_name`, `company_address`, `contact_name`, `contact_phone`,
  50. `status`, `tenant_no`, `order_num`, `remark`,
  51. `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
  52. ) VALUES
  53. (
  54. @ins_co_1,
  55. '中国人寿保险股份有限公司安徽省分公司',
  56. '安徽省合肥市蜀山区长江西路200号国寿大厦',
  57. '张经理',
  58. '0551-62880001',
  59. '1',
  60. 'zkxw',
  61. 1,
  62. '满月待办默认合作保险公司(演示数据)',
  63. @admin_id,
  64. '',
  65. NOW(),
  66. @admin_id,
  67. NOW(),
  68. 0
  69. ),
  70. (
  71. @ins_co_2,
  72. '中国平安财产保险股份有限公司合肥中心支公司',
  73. '安徽省合肥市包河区徽州大道与九华山路交口平安大厦',
  74. '李经理',
  75. '0551-63660002',
  76. '1',
  77. 'zkxw',
  78. 2,
  79. '演示数据',
  80. @admin_id,
  81. '',
  82. NOW(),
  83. @admin_id,
  84. NOW(),
  85. 0
  86. ),
  87. (
  88. @ins_co_3,
  89. '太平洋人寿保险股份有限公司安徽分公司',
  90. '安徽省合肥市庐阳区阜阳北路与北一环交口',
  91. '王经理',
  92. '0551-65660003',
  93. '0',
  94. 'zkxw',
  95. 3,
  96. '停用状态示例,用于筛选演示',
  97. @admin_id,
  98. '',
  99. NOW(),
  100. @admin_id,
  101. NOW(),
  102. 0
  103. );
  104. -- =============================================
  105. -- 2. 保险公司开票信息
  106. -- =============================================
  107. INSERT INTO `biz_insurance_company_invoice` (
  108. `id`, `insurance_company_id`, `company_name`, `tax_no`, `company_address`, `company_phone`,
  109. `bank_name`, `bank_account`, `tenant_no`, `order_num`, `remark`,
  110. `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
  111. ) VALUES
  112. (
  113. @ins_inv_1,
  114. @ins_co_1,
  115. '中国人寿保险股份有限公司安徽省分公司',
  116. '91340000123456789A',
  117. '安徽省合肥市蜀山区长江西路200号',
  118. '0551-62880001',
  119. '中国工商银行合肥蜀山支行',
  120. '6222021234567890123',
  121. 'zkxw',
  122. 1,
  123. NULL,
  124. @admin_id,
  125. '',
  126. NOW(),
  127. @admin_id,
  128. NOW(),
  129. 0
  130. ),
  131. (
  132. @ins_inv_2,
  133. @ins_co_2,
  134. '中国平安财产保险股份有限公司合肥中心支公司',
  135. '91340000987654321B',
  136. '安徽省合肥市包河区徽州大道平安大厦',
  137. '0551-63660002',
  138. '招商银行合肥分行',
  139. '6225881234567890123',
  140. 'zkxw',
  141. 1,
  142. NULL,
  143. @admin_id,
  144. '',
  145. NOW(),
  146. @admin_id,
  147. NOW(),
  148. 0
  149. ),
  150. (
  151. @ins_inv_3,
  152. @ins_co_3,
  153. '太平洋人寿保险股份有限公司安徽分公司',
  154. '91340000555555555C',
  155. '安徽省合肥市庐阳区阜阳北路',
  156. '0551-65660003',
  157. '中国建设银行合肥庐阳支行',
  158. '6227001234567890123',
  159. 'zkxw',
  160. 1,
  161. NULL,
  162. @admin_id,
  163. '',
  164. NOW(),
  165. @admin_id,
  166. NOW(),
  167. 0
  168. );
  169. -- =============================================
  170. -- 3. 保险项目价格
  171. -- =============================================
  172. INSERT INTO `biz_insurance_project_price` (
  173. `id`, `insurance_company_id`, `project_name`, `project_fee`, `status`,
  174. `tenant_no`, `order_num`, `remark`,
  175. `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
  176. ) VALUES
  177. -- 中国人寿:细胞储存相关险种
  178. (
  179. @ins_pr_1,
  180. @ins_co_1,
  181. '新生儿干细胞储存险',
  182. 1280.00,
  183. '1',
  184. 'zkxw',
  185. 1,
  186. '适用于满月待办订购,含首年保障',
  187. @admin_id,
  188. '',
  189. NOW(),
  190. @admin_id,
  191. NOW(),
  192. 0
  193. ),
  194. (
  195. @ins_pr_2,
  196. @ins_co_1,
  197. '脐带血储存综合险',
  198. 2580.00,
  199. '1',
  200. 'zkxw',
  201. 2,
  202. '脐带血+干细胞联合保障',
  203. @admin_id,
  204. '',
  205. NOW(),
  206. @admin_id,
  207. NOW(),
  208. 0
  209. ),
  210. (
  211. @ins_pr_3,
  212. @ins_co_1,
  213. '细胞储存年度续保',
  214. 680.00,
  215. '1',
  216. 'zkxw',
  217. 3,
  218. '次年续保参考价',
  219. @admin_id,
  220. '',
  221. NOW(),
  222. @admin_id,
  223. NOW(),
  224. 0
  225. ),
  226. -- 平安:分级保障计划
  227. (
  228. @ins_pr_4,
  229. @ins_co_2,
  230. '新生儿保障计划(基础版)',
  231. 980.00,
  232. '1',
  233. 'zkxw',
  234. 1,
  235. NULL,
  236. @admin_id,
  237. '',
  238. NOW(),
  239. @admin_id,
  240. NOW(),
  241. 0
  242. ),
  243. (
  244. @ins_pr_5,
  245. @ins_co_2,
  246. '新生儿保障计划(尊享版)',
  247. 1980.00,
  248. '1',
  249. 'zkxw',
  250. 2,
  251. '含住院津贴及意外医疗',
  252. @admin_id,
  253. '',
  254. NOW(),
  255. @admin_id,
  256. NOW(),
  257. 0
  258. ),
  259. -- 太平洋(公司已停用,项目仍可作历史参考)
  260. (
  261. @ins_pr_6,
  262. @ins_co_3,
  263. '围产期母婴健康险',
  264. 1580.00,
  265. '0',
  266. 'zkxw',
  267. 1,
  268. '已停用项目示例',
  269. @admin_id,
  270. '',
  271. NOW(),
  272. @admin_id,
  273. NOW(),
  274. 0
  275. );