首頁 探索 說明
註冊 登入
shuzhihua
/
pms-iot
1
0
複刻 0
Files 問題管理 0 合併請求 0 Wiki
目錄樹: d795dbc7ac
分支列表 標籤列表
pms-iot
/
sql
/
mysql
/
point.sql

point.sql 12 KB
文件歷史 原始文件

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152 
  1. /*
    2. 

  2.  Navicat Premium Data Transfer
    3. 

  3. 

  4.  Source Server         : docer-master-root(3308)
    5. 

  5.  Source Server Type    : MySQL
    6. 

  6.  Source Server Version : 80030
    7. 

  7.  Source Host           : 10.211.55.5:3308
    8. 

  8.  Source Schema         : mall
    9. 

  9. 

  10.  Target Server Type    : MySQL
    11. 

  11.  Target Server Version : 80030
    12. 

  12.  File Encoding         : 65001
    13. 

  13. 

  14.  Date: 10/06/2023 20:13:57
    15. 

  15. */
    16. 

  16. 

  17. SET NAMES utf8mb4;
    18. 

  18. SET FOREIGN_KEY_CHECKS = 0;
    19. 

  19. 

  20. -- ----------------------------
    21. 

  21. -- Table structure for member_point_config
    22. 

  22. -- ----------------------------
    23. 

  23. DROP TABLE IF EXISTS `member_point_config`;
    24. 

  24. CREATE TABLE `member_point_config` (
    25. 

  25.   `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    26. 

  26.   `trade_deduct_enable` int DEFAULT NULL COMMENT '1 开启积分抵扣\n0 关闭积分抵扣',
    27. 

  27.   `trade_deduct_unit_price` decimal(10,2) DEFAULT NULL COMMENT '积分抵扣，抵扣最低为分 以0.01表示 1积分抵扣0.01元(单位：元)',
    28. 

  28.   `trade_deduct_max_price` bigint DEFAULT NULL COMMENT '积分抵扣最大值',
    29. 

  29.   `trade_give_point` bigint DEFAULT NULL COMMENT '1元赠送多少分',
    30. 

  30.   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    31. 

  31.   `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '变更时间',
    32. 

  32.   `tenant_id` varchar(255) DEFAULT NULL COMMENT '租户id',
    33. 

  33.   `deleted` int DEFAULT '0' COMMENT '是否被删除 0 未删除 1已删除',
    34. 

  34.   `creator` varchar(255) DEFAULT NULL COMMENT '创建人',
    35. 

  35.   `updater` varchar(255) DEFAULT NULL COMMENT '更新人',
    36. 

  36.   PRIMARY KEY (`id`)
    37. 

  37. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='积分设置';
    38. 

  38. 

  39. -- ----------------------------
    40. 

  40. -- Records of member_point_config
    41. 

  41. -- ----------------------------
    42. 

  42. BEGIN;
    43. 

  43. INSERT INTO `member_point_config` (`id`, `trade_deduct_enable`, `trade_deduct_unit_price`, `trade_deduct_max_price`, `trade_give_point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (1, 1, 0.01, 10000, 1, '2023-06-10 10:57:22', '2023-06-10 03:06:58', '1', 1, '1', '1');
    44. 

  44. INSERT INTO `member_point_config` (`id`, `trade_deduct_enable`, `trade_deduct_unit_price`, `trade_deduct_max_price`, `trade_give_point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (2, 1, 0.01, 10000, 10, '2023-06-10 11:07:12', '2023-06-10 11:07:12', '1', 0, '1', '1');
    45. 

  45. INSERT INTO `member_point_config` (`id`, `trade_deduct_enable`, `trade_deduct_unit_price`, `trade_deduct_max_price`, `trade_give_point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (3, 1, 12.00, 12, 12, '2023-06-10 16:09:26', '2023-06-10 08:10:53', '1', 1, '1', '1');
    46. 

  46. COMMIT;
    47. 

  47. 

  48. -- ----------------------------
    49. 

  49. -- Table structure for member_point_record
    50. 

  50. -- ----------------------------
    51. 

  51. DROP TABLE IF EXISTS `member_point_record`;
    52. 

  52. CREATE TABLE `member_point_record` (
    53. 

  53.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    54. 

  54.   `biz_id` varchar(255) DEFAULT NULL COMMENT '业务编码',
    55. 

  55.   `biz_type` varchar(255) DEFAULT NULL COMMENT '业务类型',
    56. 

  56.   `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '1增加 0扣减',
    57. 

  57.   `title` varchar(255) DEFAULT NULL COMMENT '积分标题',
    58. 

  58.   `description` varchar(5000) DEFAULT NULL COMMENT '积分描述',
    59. 

  59.   `point` int DEFAULT NULL COMMENT '积分',
    60. 

  60.   `total_point` int NOT NULL COMMENT '变动后的积分',
    61. 

  61.   `status` int DEFAULT NULL COMMENT '状态：1-订单创建，2-冻结期，3-完成，4-失效（订单退款）\n',
    62. 

  62.   `user_id` int DEFAULT NULL COMMENT '用户id',
    63. 

  63.   `freezing_time` datetime DEFAULT NULL COMMENT '冻结时间',
    64. 

  64.   `thawing_time` datetime DEFAULT NULL COMMENT '解冻时间',
    65. 

  65.   `create_time` datetime DEFAULT NULL COMMENT '发生时间',
    66. 

  66.   `tenant_id` varchar(255) DEFAULT NULL COMMENT '租户',
    67. 

  67.   `deleted` int DEFAULT '0' COMMENT '是否删除',
    68. 

  68.   `creator` varchar(255) DEFAULT NULL COMMENT '创建用户',
    69. 

  69.   `updater` varchar(255) DEFAULT NULL COMMENT '更新用户',
    70. 

  70.   `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    71. 

  71.   PRIMARY KEY (`id`),
    72. 

  72.   KEY `index_userId` (`user_id`),
    73. 

  73.   KEY `index_title` (`title`) USING BTREE
    74. 

  74. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户积分记录';
    75. 

  75. 

  76. -- ----------------------------
    77. 

  77. -- Records of member_point_record
    78. 

  78. -- ----------------------------
    79. 

  79. BEGIN;
    80. 

  80. INSERT INTO `member_point_record` (`id`, `biz_id`, `biz_type`, `type`, `title`, `description`, `point`, `total_point`, `status`, `user_id`, `freezing_time`, `thawing_time`, `create_time`, `tenant_id`, `deleted`, `creator`, `updater`, `update_time`) VALUES (1, '1', '1', '1', '12', NULL, 212, 12, 1, 12, '2023-06-13 00:00:00', '2023-06-20 00:00:00', '2023-06-10 12:38:48', '1', 1, '1', '1', '2023-06-10 04:42:24');
    81. 

  81. INSERT INTO `member_point_record` (`id`, `biz_id`, `biz_type`, `type`, `title`, `description`, `point`, `total_point`, `status`, `user_id`, `freezing_time`, `thawing_time`, `create_time`, `tenant_id`, `deleted`, `creator`, `updater`, `update_time`) VALUES (2, '12', '1', '0', NULL, NULL, 1212, 12, 2, 12, '2023-06-28 00:00:00', NULL, '2023-06-10 12:42:48', '1', 0, '1', '1', '2023-06-10 12:43:04');
    82. 

  82. INSERT INTO `member_point_record` (`id`, `biz_id`, `biz_type`, `type`, `title`, `description`, `point`, `total_point`, `status`, `user_id`, `freezing_time`, `thawing_time`, `create_time`, `tenant_id`, `deleted`, `creator`, `updater`, `update_time`) VALUES (3, '12', '1', '1', '12', NULL, 12, 12, 1, 12, '2023-06-27 00:00:00', '2023-06-23 00:00:00', '2023-06-10 20:06:48', '1', 0, '1', '1', '2023-06-10 20:06:48');
    83. 

  83. COMMIT;
    84. 

  84. 

  85. -- ----------------------------
    86. 

  86. -- Table structure for member_sign_in_config
    87. 

  87. -- ----------------------------
    88. 

  88. DROP TABLE IF EXISTS `member_sign_in_config`;
    89. 

  89. CREATE TABLE `member_sign_in_config` (
    90. 

  90.   `id` int NOT NULL AUTO_INCREMENT COMMENT '规则自增主键',
    91. 

  91.   `day` int DEFAULT NULL COMMENT '签到第x天',
    92. 

  92.   `point` int DEFAULT NULL COMMENT '签到天数对应分数',
    93. 

  93.   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    94. 

  94.   `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '变更时间',
    95. 

  95.   `tenant_id` varchar(255) DEFAULT NULL COMMENT '租户id',
    96. 

  96.   `deleted` int DEFAULT '0' COMMENT '是否删除',
    97. 

  97.   `creator` varchar(255) DEFAULT NULL COMMENT '创建人',
    98. 

  98.   `updater` varchar(255) DEFAULT NULL COMMENT '变更人',
    99. 

  99.   PRIMARY KEY (`id`)
    100. 

  100. ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='积分签到规则';
    101. 

  101. 

  102. -- ----------------------------
    103. 

  103. -- Records of member_sign_in_config
    104. 

  104. -- ----------------------------
    105. 

  105. BEGIN;
    106. 

  106. INSERT INTO `member_sign_in_config` (`id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (1, 1, 10, '2023-06-10 11:34:43', '2023-06-10 11:34:43', '1', 0, '1', '1');
    107. 

  107. INSERT INTO `member_sign_in_config` (`id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (2, 2, 20, '2023-06-10 11:34:59', '2023-06-10 03:55:35', '1', 1, '1', '1');
    108. 

  108. INSERT INTO `member_sign_in_config` (`id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (3, 7, 1001, '2023-06-10 17:47:45', '2023-06-10 19:54:37', '1', 0, '1', '1');
    109. 

  109. INSERT INTO `member_sign_in_config` (`id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (4, 6, 12121, '2023-06-10 17:47:55', '2023-06-10 19:48:37', '1', 0, '1', '1');
    110. 

  110. INSERT INTO `member_sign_in_config` (`id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (5, 2, 12, '2023-06-10 19:54:52', '2023-06-10 19:54:52', '1', 0, '1', '1');
    111. 

  111. COMMIT;
    112. 

  112. 

  113. -- ----------------------------
    114. 

  114. -- Table structure for member_sign_in_record
    115. 

  115. -- ----------------------------
    116. 

  116. DROP TABLE IF EXISTS `member_sign_in_record`;
    117. 

  117. CREATE TABLE `member_sign_in_record` (
    118. 

  118.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '签到自增id',
    119. 

  119.   `user_id` int DEFAULT NULL COMMENT '签到用户',
    120. 

  120.   `day` int DEFAULT NULL COMMENT '第几天签到',
    121. 

  121.   `point` int DEFAULT NULL COMMENT '签到的分数',
    122. 

  122.   `create_time` datetime DEFAULT NULL COMMENT '签到时间',
    123. 

  123.   `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '变更时间',
    124. 

  124.   `tenant_id` varchar(255) DEFAULT NULL COMMENT '租户id',
    125. 

  125.   `deleted` int DEFAULT '0' COMMENT '是否删除',
    126. 

  126.   `creator` varchar(255) DEFAULT NULL COMMENT '创建人',
    127. 

  127.   `updater` varchar(255) DEFAULT NULL COMMENT '更新人',
    128. 

  128.   PRIMARY KEY (`id`)
    129. 

  129. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户签到积分';
    130. 

  130. 

  131. -- ----------------------------
    132. 

  132. -- Records of member_sign_in_record
    133. 

  133. -- ----------------------------
    134. 

  134. BEGIN;
    135. 

  135. INSERT INTO `member_sign_in_record` (`id`, `user_id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (1, 121, 1, 123, '2023-06-10 12:58:18', '2023-06-10 04:59:00', '1', 1, '1', '1');
    136. 

  136. INSERT INTO `member_sign_in_record` (`id`, `user_id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (2, 12, 12, 12, '2023-06-10 19:56:39', '2023-06-10 11:56:45', '1', 1, '1', '1');
    137. 

  137. INSERT INTO `member_sign_in_record` (`id`, `user_id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (3, 12, 12, 1212, '2023-06-10 20:01:17', '2023-06-10 12:01:23', '1', 1, '1', '1');
    138. 

  138. INSERT INTO `member_sign_in_record` (`id`, `user_id`, `day`, `point`, `create_time`, `update_time`, `tenant_id`, `deleted`, `creator`, `updater`) VALUES (4, 12, 12, 1212, '2023-06-10 20:01:27', '2023-06-10 20:01:27', '1', 0, '1', '1');
    139. 

  139. COMMIT;
    140. 

  140. 

  141. SET FOREIGN_KEY_CHECKS = 1;
    142. 

  142. 

  143. 

  144. INSERT INTO `mall`.`system_dict_type` ( `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ( '积分业务类型', 'point_biz_type', 0, '', '1', '2023-06-10 12:15:00', '1', '2023-06-10 04:25:07', b'0', '1970-01-01 00:00:00');
    145. 

  145. INSERT INTO `mall`.`system_dict_type` ( `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ( '积分订单状态', 'point_status', 0, '', '1', '2023-06-10 12:16:27', '1', '2023-06-10 12:16:27', b'0', '1970-01-01 00:00:00');
    146. 

  146. 

  147. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 1, '购物', '1', 'point_biz_type', 0, '', '', '', '1', '2023-06-10 12:15:27', '1', '2023-06-10 04:25:15', b'0');
    148. 

  148. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 2, '签到', '2', 'point_biz_type', 0, '', '', '', '1', '2023-06-10 12:15:48', '1', '2023-06-10 04:25:18', b'0');
    149. 

  149. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 1, '订单创建', '1', 'point_status', 0, '', '', '', '1', '2023-06-10 12:16:42', '1', '2023-06-10 12:16:42', b'0');
    150. 

  150. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 2, '冻结期', '2', 'point_status', 0, '', '', '', '1', '2023-06-10 12:16:58', '1', '2023-06-10 12:16:58', b'0');
    151. 

  151. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 3, '完成', '3', 'point_status', 0, '', '', '', '1', '2023-06-10 12:17:07', '1', '2023-06-10 12:17:07', b'0');
    152. 

  152. INSERT INTO `mall`.`system_dict_data` ( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 4, '失效(订单退款)', '4', 'point_status', 0, '', '', '', '1', '2023-06-10 12:17:21', '1', '2023-06-10 12:17:21', b'0');
    153.