170 lines
8.0 KiB
SQL
170 lines
8.0 KiB
SQL
-- ============================================
|
|
-- SPA管理系统 - 财务报表统计SQL
|
|
-- ============================================
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- 1. 财务账户表
|
|
DROP TABLE IF EXISTS `spa_financial_account`;
|
|
CREATE TABLE `spa_financial_account` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`deleted_at` datetime(3) DEFAULT NULL,
|
|
`account_name` varchar(100) NOT NULL COMMENT '账户名称',
|
|
`account_type` tinyint NOT NULL COMMENT '账户类型 1现金 2银行 3支付宝 4微信',
|
|
`account_no` varchar(100) DEFAULT NULL COMMENT '账号',
|
|
`balance` decimal(12,2) DEFAULT 0.00 COMMENT '账户余额',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1正常 2冻结',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务账户表';
|
|
|
|
-- 2. 资金流水表
|
|
DROP TABLE IF EXISTS `spa_financial_flow`;
|
|
CREATE TABLE `spa_financial_flow` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`flow_no` varchar(50) NOT NULL COMMENT '流水号',
|
|
`account_id` bigint unsigned NOT NULL COMMENT '账户ID',
|
|
`flow_type` tinyint NOT NULL COMMENT '流水类型 1收入 2支出',
|
|
`amount` decimal(10,2) NOT NULL COMMENT '金额',
|
|
`before_balance` decimal(12,2) NOT NULL COMMENT '变动前余额',
|
|
`after_balance` decimal(12,2) NOT NULL COMMENT '变动后余额',
|
|
`business_type` tinyint NOT NULL COMMENT '业务类型 1订单收入 2提现支出 3工资支出 4采购支出 5其他',
|
|
`related_id` bigint unsigned DEFAULT NULL COMMENT '关联业务ID',
|
|
`related_no` varchar(50) DEFAULT NULL COMMENT '关联业务单号',
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_flow_no` (`flow_no`),
|
|
KEY `idx_account_id` (`account_id`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水表';
|
|
|
|
-- 3. 对账记录表
|
|
DROP TABLE IF EXISTS `spa_reconciliation`;
|
|
CREATE TABLE `spa_reconciliation` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`reconciliation_date` date NOT NULL COMMENT '对账日期',
|
|
`account_id` bigint unsigned NOT NULL COMMENT '账户ID',
|
|
`system_balance` decimal(12,2) NOT NULL COMMENT '系统余额',
|
|
`actual_balance` decimal(12,2) NOT NULL COMMENT '实际余额',
|
|
`difference` decimal(12,2) DEFAULT 0.00 COMMENT '差额',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1待对账 2已对账 3有差异',
|
|
`operator_id` bigint unsigned DEFAULT NULL COMMENT '操作人ID',
|
|
`remark` text COMMENT '备注',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_id` (`account_id`),
|
|
KEY `idx_reconciliation_date` (`reconciliation_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账记录表';
|
|
|
|
-- 4. 发票管理表
|
|
DROP TABLE IF EXISTS `spa_invoice`;
|
|
CREATE TABLE `spa_invoice` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`deleted_at` datetime(3) DEFAULT NULL,
|
|
`invoice_no` varchar(50) NOT NULL COMMENT '发票号码',
|
|
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
|
`customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
|
|
`invoice_type` tinyint NOT NULL COMMENT '发票类型 1普通 2专用',
|
|
`invoice_title` varchar(200) NOT NULL COMMENT '发票抬头',
|
|
`tax_no` varchar(50) DEFAULT NULL COMMENT '税号',
|
|
`amount` decimal(10,2) NOT NULL COMMENT '开票金额',
|
|
`status` tinyint DEFAULT 1 COMMENT '状态 1待开票 2已开票 3已作废',
|
|
`issue_time` datetime DEFAULT NULL COMMENT '开票时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_invoice_no` (`invoice_no`),
|
|
KEY `idx_order_id` (`order_id`),
|
|
KEY `idx_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票管理表';
|
|
|
|
-- 5. 日统计表
|
|
DROP TABLE IF EXISTS `spa_daily_statistics`;
|
|
CREATE TABLE `spa_daily_statistics` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`stat_date` date NOT NULL COMMENT '统计日期',
|
|
`store_id` bigint unsigned DEFAULT NULL COMMENT '店铺ID 0表示全部',
|
|
`order_count` int DEFAULT 0 COMMENT '订单数',
|
|
`order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
|
|
`paid_count` int DEFAULT 0 COMMENT '支付订单数',
|
|
`paid_amount` decimal(12,2) DEFAULT 0.00 COMMENT '支付金额',
|
|
`refund_count` int DEFAULT 0 COMMENT '退款订单数',
|
|
`refund_amount` decimal(12,2) DEFAULT 0.00 COMMENT '退款金额',
|
|
`new_customer_count` int DEFAULT 0 COMMENT '新增客户数',
|
|
`active_customer_count` int DEFAULT 0 COMMENT '活跃客户数',
|
|
`technician_count` int DEFAULT 0 COMMENT '在职技师数',
|
|
`service_count` int DEFAULT 0 COMMENT '服务次数',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_store_date` (`store_id`, `stat_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日统计表';
|
|
|
|
-- 6. 月统计表
|
|
DROP TABLE IF EXISTS `spa_monthly_statistics`;
|
|
CREATE TABLE `spa_monthly_statistics` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
|
|
`store_id` bigint unsigned DEFAULT NULL COMMENT '店铺ID 0表示全部',
|
|
`order_count` int DEFAULT 0 COMMENT '订单数',
|
|
`order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
|
|
`paid_count` int DEFAULT 0 COMMENT '支付订单数',
|
|
`paid_amount` decimal(12,2) DEFAULT 0.00 COMMENT '支付金额',
|
|
`refund_count` int DEFAULT 0 COMMENT '退款订单数',
|
|
`refund_amount` decimal(12,2) DEFAULT 0.00 COMMENT '退款金额',
|
|
`new_customer_count` int DEFAULT 0 COMMENT '新增客户数',
|
|
`total_customer_count` int DEFAULT 0 COMMENT '累计客户数',
|
|
`commission_amount` decimal(12,2) DEFAULT 0.00 COMMENT '分佣总额',
|
|
`withdrawal_amount` decimal(12,2) DEFAULT 0.00 COMMENT '提现总额',
|
|
`profit_amount` decimal(12,2) DEFAULT 0.00 COMMENT '利润',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_store_month` (`store_id`, `stat_month`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='月统计表';
|
|
|
|
-- 7. 技师绩效表
|
|
DROP TABLE IF EXISTS `spa_technician_performance`;
|
|
CREATE TABLE `spa_technician_performance` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
|
|
`stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
|
|
`service_count` int DEFAULT 0 COMMENT '服务次数',
|
|
`service_amount` decimal(12,2) DEFAULT 0.00 COMMENT '服务金额',
|
|
`commission_amount` decimal(12,2) DEFAULT 0.00 COMMENT '提成金额',
|
|
`avg_rating` decimal(3,2) DEFAULT 0.00 COMMENT '平均评分',
|
|
`complaint_count` int DEFAULT 0 COMMENT '投诉次数',
|
|
`attendance_days` int DEFAULT 0 COMMENT '出勤天数',
|
|
`rank` int DEFAULT 0 COMMENT '排名',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_tech_month` (`technician_id`, `stat_month`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师绩效表';
|
|
|
|
-- 8. 店铺绩效表
|
|
DROP TABLE IF EXISTS `spa_store_performance`;
|
|
CREATE TABLE `spa_store_performance` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`created_at` datetime(3) DEFAULT NULL,
|
|
`updated_at` datetime(3) DEFAULT NULL,
|
|
`store_id` bigint unsigned NOT NULL COMMENT '店铺ID',
|
|
`stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
|
|
`order_count` int DEFAULT 0 COMMENT '订单数',
|
|
`order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
|
|
`customer_count` int DEFAULT 0 COMMENT '客户数',
|
|
`technician_count` int DEFAULT 0 COMMENT '技师数',
|
|
`avg_rating` decimal(3,2) DEFAULT 0.00 COMMENT '平均评分',
|
|
`profit_amount` decimal(12,2) DEFAULT 0.00 COMMENT '利润',
|
|
`rank` int DEFAULT 0 COMMENT '排名',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_store_month` (`store_id`, `stat_month`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺绩效表';
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|