200 lines
10 KiB
MySQL
200 lines
10 KiB
MySQL
|
-- Phase 2: Purchase Management System
|
||
|
|
-- New tables: pur_supplier, pur_purchase_order, pur_purchase_order_detail,
|
||
|
|
-- pur_purchase_receipt, pur_purchase_receipt_detail,
|
||
|
|
-- pur_purchase_payment, inv_inventory_log
|
||
|
|
|
||
|
|
-- ==================== Supplier ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_supplier` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`supplier_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`supplier_name` VARCHAR(100) NOT NULL,
|
||
|
|
`phone` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`purchase_count` INT NOT NULL DEFAULT 0 COMMENT 'redundant: total confirmed orders',
|
||
|
|
`delivered_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'redundant: total received quantity',
|
||
|
|
`payable_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'redundant: sum of confirmed order contract amounts',
|
||
|
|
`paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'redundant: sum of all payments',
|
||
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1:enabled 0:disabled',
|
||
|
|
`remark` TEXT,
|
||
|
|
`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_supplier_id` (`supplier_id`),
|
||
|
|
UNIQUE KEY `uk_tenant_name` (`tenant_id`, `supplier_name`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商档案';
|
||
|
|
|
||
|
|
-- ==================== Purchase Order ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_purchase_order` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`order_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`order_no` VARCHAR(50) NOT NULL COMMENT 'format: PO-YYYYMMDD-0001',
|
||
|
|
`supplier_id` VARCHAR(32) NOT NULL,
|
||
|
|
`order_date` DATE NOT NULL,
|
||
|
|
`contract_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`received_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'updated on receipt confirm',
|
||
|
|
`paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'updated on payment create',
|
||
|
|
`payment_status` TINYINT NOT NULL DEFAULT 0 COMMENT '0:unpaid 1:partial 2:paid',
|
||
|
|
`receipt_status` TINYINT NOT NULL DEFAULT 0 COMMENT '0:none 1:partial 2:complete',
|
||
|
|
`purchase_by` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`creator` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`operator` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0:draft 1:confirmed 2:cancelled',
|
||
|
|
`remark` TEXT,
|
||
|
|
`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_order_id` (`order_id`),
|
||
|
|
UNIQUE KEY `uk_tenant_order_no` (`tenant_id`, `order_no`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_supplier_id` (`supplier_id`),
|
||
|
|
KEY `idx_status` (`status`),
|
||
|
|
KEY `idx_order_date` (`order_date`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购订单';
|
||
|
|
|
||
|
|
-- ==================== Purchase Order Detail ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_purchase_order_detail` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`detail_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`order_id` VARCHAR(32) NOT NULL,
|
||
|
|
`product_id` VARCHAR(32) NOT NULL,
|
||
|
|
`product_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'snapshot',
|
||
|
|
`spec` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'snapshot',
|
||
|
|
`color` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'snapshot',
|
||
|
|
`quantity` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'quantity * unit_price',
|
||
|
|
`received_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'updated on receipt confirm',
|
||
|
|
`remark` TEXT,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_detail_id` (`detail_id`),
|
||
|
|
KEY `idx_order_id` (`order_id`),
|
||
|
|
KEY `idx_product_id` (`product_id`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购订单明细';
|
||
|
|
|
||
|
|
-- ==================== Purchase Receipt ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_purchase_receipt` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`receipt_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`receipt_no` VARCHAR(50) NOT NULL COMMENT 'format: REC-YYYYMMDD-0001',
|
||
|
|
`order_id` VARCHAR(32) NOT NULL,
|
||
|
|
`receipt_date` DATE NOT NULL,
|
||
|
|
`received_by` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0:draft 1:confirmed',
|
||
|
|
`remark` TEXT,
|
||
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_receipt_id` (`receipt_id`),
|
||
|
|
UNIQUE KEY `uk_tenant_receipt_no` (`tenant_id`, `receipt_no`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_order_id` (`order_id`),
|
||
|
|
KEY `idx_status` (`status`),
|
||
|
|
KEY `idx_receipt_date` (`receipt_date`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购入库单';
|
||
|
|
|
||
|
|
-- ==================== Purchase Receipt Detail ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_purchase_receipt_detail` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`detail_id` VARCHAR(32) NOT NULL,
|
||
|
|
`receipt_id` VARCHAR(32) NOT NULL,
|
||
|
|
`order_detail_id` VARCHAR(32) NOT NULL,
|
||
|
|
`product_id` VARCHAR(32) NOT NULL,
|
||
|
|
`actual_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'cost price snapshot from order detail',
|
||
|
|
`remark` TEXT,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_detail_id` (`detail_id`),
|
||
|
|
KEY `idx_receipt_id` (`receipt_id`),
|
||
|
|
KEY `idx_order_detail_id` (`order_detail_id`),
|
||
|
|
KEY `idx_product_id` (`product_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购入库单明细';
|
||
|
|
|
||
|
|
-- ==================== Purchase Payment ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `pur_purchase_payment` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`payment_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`order_id` VARCHAR(32) NOT NULL,
|
||
|
|
`supplier_id` VARCHAR(32) NOT NULL,
|
||
|
|
`payment_date` DATE NOT NULL,
|
||
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`payment_method` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '现金/银行转账/微信/支付宝',
|
||
|
|
`operator` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`remark` TEXT,
|
||
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_payment_id` (`payment_id`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_order_id` (`order_id`),
|
||
|
|
KEY `idx_supplier_id` (`supplier_id`),
|
||
|
|
KEY `idx_payment_date` (`payment_date`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购付款记录';
|
||
|
|
|
||
|
|
-- ==================== Inventory Log ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `inv_inventory_log` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`log_id` VARCHAR(32) NOT NULL,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`product_id` VARCHAR(32) NOT NULL,
|
||
|
|
`change_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'positive=in, negative=out',
|
||
|
|
`balance_after` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'snapshot of quantity after change',
|
||
|
|
`change_type` TINYINT NOT NULL DEFAULT 1 COMMENT '1:purchase_in 2:sales_out 3:adjust 4:check',
|
||
|
|
`ref_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'PURCHASE/SALES/ADJUST/CHECK',
|
||
|
|
`ref_id` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'receipt_id / adjust_id etc.',
|
||
|
|
`contact_id` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'supplier_id or customer_id for display',
|
||
|
|
`log_date` DATE NOT NULL,
|
||
|
|
`operator` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`remark` TEXT,
|
||
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_log_id` (`log_id`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_product_id` (`product_id`),
|
||
|
|
KEY `idx_change_type` (`change_type`),
|
||
|
|
KEY `idx_ref_id` (`ref_id`),
|
||
|
|
KEY `idx_log_date` (`log_date`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存变动日志';
|
||
|
|
|
||
|
|
-- ==================== Menu Seed Data ====================
|
||
|
|
|
||
|
|
-- Purchase management menu group and sub-menus
|
||
|
|
INSERT IGNORE INTO `sys_menu` (`menu_id`, `parent_id`, `menu_name`, `menu_type`, `path`, `component`, `permission`, `icon`, `sort_order`, `visible`, `status`) VALUES
|
||
|
|
('m_400', '', '采购管理', 1, '/purchase', '', '', 'ShoppingCartOutlined', 400, 1, 1),
|
||
|
|
('m_401', 'm_400', '供应商管理', 2, '/purchase/suppliers', './Purchase/Suppliers', 'purchase:supplier:list', 'ContactsOutlined', 1, 1, 1),
|
||
|
|
('m_402', 'm_400', '采购订单', 2, '/purchase/orders', './Purchase/Orders', 'purchase:order:list', 'FileTextOutlined', 2, 1, 1),
|
||
|
|
('m_403', 'm_400', '入库记录', 2, '/purchase/receipts', './Purchase/Receipts', 'purchase:receipt:list', 'InboxOutlined', 3, 1, 1),
|
||
|
|
('m_404', 'm_400', '采购报表', 2, '/purchase/stats', './Purchase/Stats', 'purchase:stats:view', 'BarChartOutlined', 4, 1, 1),
|
||
|
|
('m_207', 'm_200', '库存变动记录', 2, '/inventory/logs', './Inventory/Logs', 'inventory:log:list', 'HistoryOutlined', 7, 1, 1);
|
||
|
|
|
||
|
|
-- Casbin policies for purchase module (admin gets all)
|
||
|
|
INSERT IGNORE INTO `casbin_rule` (`ptype`, `v0`, `v1`, `v2`) VALUES
|
||
|
|
('p', 'admin', '/api/v1/purchase/*', '*'),
|
||
|
|
('p', 'admin', '/api/v1/inventory/logs', 'GET'),
|
||
|
|
('p', 'warehouse', '/api/v1/inventory/logs', 'GET'),
|
||
|
|
('p', 'warehouse', '/api/v1/purchase/orders', 'GET'),
|
||
|
|
('p', 'warehouse', '/api/v1/purchase/orders/*', 'GET'),
|
||
|
|
('p', 'warehouse', '/api/v1/purchase/receipts', '*'),
|
||
|
|
('p', 'warehouse', '/api/v1/purchase/receipts/*', '*');
|
||
|
|
|
||
|
|
-- Grant all new menus to admin role
|
||
|
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
|
|
SELECT 'r_001', `menu_id` FROM `sys_menu` WHERE `menu_id` IN ('m_400','m_401','m_402','m_403','m_404','m_207');
|
||
|
|
|
||
|
|
-- Grant warehouse role access to receipts and inventory log menu
|
||
|
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES
|
||
|
|
('r_002', 'm_402'),
|
||
|
|
('r_002', 'm_403'),
|
||
|
|
('r_002', 'm_207');
|