230628.sql 5.8 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192
  1. create table sys_dept_recharge
  2. (
  3. id int auto_increment comment '主键id'
  4. primary key,
  5. dept_id int null comment '组织机构ID',
  6. score int default 0 not null comment '积分余额',
  7. balance decimal(10, 2) default 0.00 not null comment '余额(元)',
  8. permissions varchar(64) null comment '权限,多个逗号分隔',
  9. service_charge decimal(5, 2) default 0.00 not null comment '服务费率',
  10. overdraw_amount decimal(10, 2) default 0.00 not null comment '可透支金额',
  11. overdraw_score int default 0 not null comment '可透支积分',
  12. del_flag varchar(10) default '0' not null comment '是否删除',
  13. create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
  14. create_user int null comment '创建人',
  15. update_time datetime null comment '更新时间',
  16. update_user int null comment '更新人',
  17. version int default 0 null,
  18. constraint sys_dept_recharge_dept_id_uindex
  19. unique (dept_id)
  20. )
  21. comment '积分充值表' charset = utf8mb4;
  22. create table sys_dept_recharge_record
  23. (
  24. id int auto_increment comment '主键id'
  25. primary key,
  26. recharge_id int null comment '积分充值表ID',
  27. dept_id int null comment '组织机构ID',
  28. relation_dept_id int null comment '关联组织机构ID(关联操作对象)',
  29. change_score int not null comment '充值积分',
  30. change_amount decimal(10, 2) default 0.00 not null comment '充值金额(元)',
  31. type char(25) not null comment '操作类型',
  32. current_score int not null comment '充值后积分余额',
  33. current_balance decimal(10, 2) default 0.00 not null comment '充值后积分余额(元)',
  34. package_id int null comment '积分包id',
  35. package_type varchar(32) null comment '积分包类型',
  36. del_flag varchar(10) default '0' not null comment '是否删除',
  37. create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
  38. create_user int null comment '创建人'
  39. )
  40. comment '积分充值/使用记录表' charset = utf8mb4;
  41. create index sys_dept_recharge_record_dept_id_index
  42. on sys_dept_recharge_record (dept_id);
  43. create table sys_dept_invoice
  44. (
  45. id int auto_increment comment '主键id'
  46. primary key,
  47. dept_id int null comment '组织机构ID',
  48. invoice_amount decimal(10, 2) default 0.00 not null comment '开票金额(元)',
  49. relation_package_score int default 0 not null comment '关联积分值',
  50. relation_package_number int default 0 not null comment '关联积分包个数',
  51. approval_status varchar(10) default 'PENDING' not null comment '审核状态',
  52. approval_time datetime null comment '审核时间',
  53. invoice_type char(10) not null comment '发票类型',
  54. address varchar(64) null comment '邮寄地址',
  55. addressee_name varchar(32) null comment '收件人姓名',
  56. addressee_phone varchar(20) null comment '收件人手机号',
  57. del_flag varchar(10) default '0' not null comment '是否删除',
  58. create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',
  59. create_user int null comment '创建人',
  60. update_time datetime null comment '更新时间',
  61. update_user int null comment '更新人'
  62. )
  63. comment '企业开票表' charset = utf8mb4;
  64. create index sys_dept_invoice_dept_id_index
  65. on sys_dept_invoice (dept_id);
  66. create table sys_dept_invoice_relation
  67. (
  68. id int auto_increment comment '主键id'
  69. primary key,
  70. invoice_id int null comment '企业开票表ID',
  71. dept_id int not null comment '发包企业id',
  72. package_id int not null comment '积分包id',
  73. invoice_status varchar(10) default 'PENDING' not null comment '开票状态',
  74. invoice_time datetime null comment '开票时间',
  75. del_flag varchar(10) default '0' not null comment '是否删除',
  76. create_time datetime default CURRENT_TIMESTAMP null comment '创建/申请时间',
  77. create_user int null comment '创建人',
  78. update_time datetime null comment '更新时间',
  79. update_user int null comment '更新人',
  80. constraint sys_dept_invoice_relation_invoice_id_package_id_uindex
  81. unique (invoice_id, package_id)
  82. )
  83. comment '企业开票关联表' charset = utf8mb4;