45 lines
2.1 KiB
SQL
45 lines
2.1 KiB
SQL
-- +goose Up
|
|
|
|
CREATE TABLE manual_disbursements (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
disbursement_no VARCHAR(64) NOT NULL COMMENT '其他线下出款单号',
|
|
category VARCHAR(32) NOT NULL COMMENT '出款分类',
|
|
payee_name VARCHAR(100) NOT NULL COMMENT '收款人或单位名称',
|
|
amount_cent BIGINT NOT NULL COMMENT '实际出款金额(分)',
|
|
paid_at DATETIME NOT NULL COMMENT '实际出款时间',
|
|
remark VARCHAR(500) NOT NULL COMMENT '出款用途备注',
|
|
voucher_url VARCHAR(500) NOT NULL DEFAULT '' COMMENT '付款凭证地址',
|
|
status VARCHAR(16) NOT NULL DEFAULT 'paid' COMMENT '状态: paid已出款/voided已作废',
|
|
created_by BIGINT UNSIGNED NOT NULL COMMENT '录入管理员ID',
|
|
voided_by BIGINT UNSIGNED NULL COMMENT '作废管理员ID',
|
|
voided_at DATETIME NULL COMMENT '作废时间',
|
|
void_reason VARCHAR(255) NOT NULL DEFAULT '' COMMENT '作废原因',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_manual_disbursements_no (disbursement_no),
|
|
KEY idx_manual_disbursements_paid (status, paid_at),
|
|
KEY idx_manual_disbursements_created (created_at),
|
|
KEY idx_manual_disbursements_category (category)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='其他线下出款记账';
|
|
|
|
INSERT INTO permissions (code, name, resource, action) VALUES
|
|
('finance:manual_disbursement', '管理其他线下出款', 'finance', 'manual_disbursement')
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
resource = VALUES(resource),
|
|
action = VALUES(action);
|
|
|
|
INSERT IGNORE INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id FROM roles r, permissions p
|
|
WHERE r.code IN ('super_admin', 'finance') AND p.code = 'finance:manual_disbursement';
|
|
|
|
-- +goose Down
|
|
|
|
DELETE rp FROM role_permissions rp
|
|
JOIN permissions p ON p.id = rp.permission_id
|
|
WHERE p.code = 'finance:manual_disbursement';
|
|
|
|
DELETE FROM permissions WHERE code = 'finance:manual_disbursement';
|
|
DROP TABLE IF EXISTS manual_disbursements;
|