首頁 探索 說明
登入
sckj
/
ruoyi-vue
1
0
複刻 0
檔案 問題管理 0 合併請求 0 Wiki
目錄樹: 534e7c414d
分支列表 標籤列表
ruoyi-vue
/
sql
/
update
/
20230609-JiFenSC.sql

20230609-JiFenSC.sql 12 KB
文件歷史 原始文件

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081 
  1. 

  2. -- ----------------------------
    3. 

  3. -- 插入积分商城代金券相关菜单配置
    4. 

  4. -- ----------------------------
    5. 

  5. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('积分商城',0,15,'shopping',NULL,NULL,1,0,'M','0','0',NULL,'shopping','admin','2023-06-08 14:04:56','',NULL,'');
    6. 

  6. SELECT @parentId := LAST_INSERT_ID();
    7. 

  7. 

  8. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券管理', @parentId,0,'voucher','goods/voucher/index',NULL,1,0,'C','0','0',NULL,'money','admin','2023-06-08 14:06:24','',NULL,'');
    9. 

  9. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券兑换管理', @parentId,1,'exchange','goods/voucher/exchange',NULL,1,0,'C','0','0',NULL,'build','admin','2023-06-08 15:00:05','',NULL,'');
    10. 

  10. 

  11. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('代金券统计',3139,3,'statvoucher','stat/voucher/index',NULL,1,0,'C','0','0',NULL,'chart','admin','2023-06-12 10:18:43','',NULL,'');
    12. 

  12. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('兑换数统计',3139,4,'exchange','stat/integral/exchange',NULL,1,0,'C','0','0','','chart','admin','2023-06-13 13:47:24','admin','2023-06-13 13:48:55','');
    13. 

  13. INSERT INTO `sys_menu` (`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('核销数统计',3139,5,'writeOff','stat/writeOff/index',NULL,1,0,'C','0','0','','chart','admin','2023-06-12 10:27:31','admin','2023-06-13 13:45:15','');
    14. 

  14. 

  15. -- ----------------------------
    16. 

  16. -- 代金券相关字典配置
    17. 

  17. -- ----------------------------
    18. 

  18. INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('社区','community','0','admin','2023-06-09 15:31:11','',NULL,NULL);
    19. 

  19. INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('领取群体','receive_tags','0','admin','2023-06-09 16:13:32','',NULL,NULL);
    20. 

  20. INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('领取状态','voucher_status','0','admin','2023-06-09 16:50:50','',NULL,NULL);
    21. 

  21. INSERT INTO `sys_dict_type` (`dict_name`,`dict_type`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES ('商品状态','goods_status','0','admin','2023-06-09 16:50:50','',NULL,NULL);
    22. 

  22. 

  23. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'中海南溪','中海南溪','community',NULL,'default','N','0','admin','2023-06-09 15:31:26','',NULL,NULL);
    24. 

  24. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'环城社区','环城社区','community',NULL,'default','N','0','admin','2023-06-09 15:31:41','',NULL,NULL);
    25. 

  25. 

  26. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'下沉干部','下沉干部','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:12','',NULL,NULL);
    27. 

  27. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'在职党员','在职党员','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:22','',NULL,NULL);
    28. 

  28. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'网格长','网格长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:30','',NULL,NULL);
    29. 

  29. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'楼栋长','楼栋长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:38','',NULL,NULL);
    30. 

  30. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'单元长','单元长','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:45','',NULL,NULL);
    31. 

  31. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'志愿者','志愿者','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:14:52','',NULL,NULL);
    32. 

  32. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'社区工作者','社区工作者','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:00','',NULL,NULL);
    33. 

  33. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'居民','居民','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:08','',NULL,NULL);
    34. 

  34. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'所有','所有','receive_tags',NULL,'default','N','0','admin','2023-06-09 16:15:20','',NULL,NULL);
    35. 

  35. 

  36. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'已兑换','1','voucher_status',NULL,'default','N','0','admin','2023-06-09 16:51:06','',NULL,NULL);
    37. 

  37. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'已核销','9','voucher_status',NULL,'default','N','0','admin','2023-06-09 16:51:17','',NULL,NULL);
    38. 

  38. 

  39. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'上架','1','goods_status',NULL,'default','N','0','admin','2023-06-15 14:13:50','',NULL,NULL);
    40. 

  40. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'下架','0','goods_status',NULL,'default','N','0','admin','2023-06-15 14:13:57','',NULL,NULL);
    41. 

  41. 

  42. SET SQL_SAFE_UPDATES = 0;
    43. 

  43. delete from sys_dict_data where dict_type = 'voucher_category';
    44. 

  44. SET SQL_SAFE_UPDATES = 1;
    45. 

  45. 

  46. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'生活服务','live','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:39:42','admin','2023-06-13 11:26:43',NULL);
    47. 

  47. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'文化娱乐','entertainment','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:40:08','',NULL,NULL);
    48. 

  48. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (0,'其他','other','voucher_category',NULL,'default','N','0','admin','2023-05-26 16:41:09','',NULL,NULL);
    49. 

  49. 

  50. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'幸福餐厅','幸福餐厅','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:52:49','admin','2023-06-13 11:19:19','live');
    51. 

  51. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'超市','超市','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:53:29','admin','2023-06-13 11:28:19','live');
    52. 

  52. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'理疗按摩','理疗按摩','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:18:27','admin','2023-06-13 11:19:02','live');
    53. 

  53. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'生鲜','生鲜','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:19:47','',NULL,'live');
    54. 

  54. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'洗衣','洗衣','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:01','admin','2023-06-13 11:20:21','live');
    55. 

  55. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'理发','理发','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:15','',NULL,'live');
    56. 

  56. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'汽修','汽修','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:37','',NULL,'live');
    57. 

  57. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (1,'洗浴','洗浴','voucher_category',NULL,'default','N','0','admin','2023-06-13 11:20:52','',NULL,'live');
    58. 

  58. INSERT INTO `sys_dict_data` (`dict_sort`,`dict_label`,`dict_value`,`dict_type`,`css_class`,`list_class`,`is_default`,`status`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`) VALUES (2,'幸福课堂','幸福课堂','voucher_category',NULL,'default','N','0','admin','2023-05-26 13:53:08','admin','2023-06-13 11:21:10','entertainment');
    59. 

  59. 

  60. -- ----------------------------
    61. 

  61. -- 创建触发器，每新增一条biz_user记录，同时：
    62. 

  62. --      插入一条biz_user_tag记录，tag默认为居民
    63. 

  63. --      插入一条che_points，积分默认为0（便于手工调整赋分）
    64. 

  64. -- ----------------------------
    65. 

  65. DROP TRIGGER IF EXISTS `insert_biz_user_tag`;
    66. 

  66. DROP TRIGGER IF EXISTS `insert_biz_user`;
    67. 

  67. DELIMITER $$
    68. 

  68. CREATE TRIGGER `insert_biz_user` AFTER INSERT ON `biz_user` FOR EACH ROW
    69. 

  69. BEGIN
    70. 

  70.     INSERT INTO `biz_user_tag` (`user_id`, `tag`) VALUES (NEW.user_id, '其他');
    71. 

  71.     INSERT INTO `che_points` (`user_id`, `honor_points`, `spend_points`) VALUES (NEW.user_id, 0, 0);
    72. 

  72. END$$
    73. 

  73. DELIMITER ;
    74. 

  74. 

  75. DROP TRIGGER IF EXISTS `insert_che_resident`;
    76. 

  76. DELIMITER $$
    77. 

  77. CREATE TRIGGER `insert_che_resident` AFTER INSERT ON `che_resident` FOR EACH ROW
    78. 

  78. BEGIN
    79. 

  79.     UPDATE `biz_user_tag` SET tag = '居民' WHERE user_id = NEW.applicant_id;
    80. 

  80. END$$
    81. 

  81. DELIMITER ;
    82.