140 lines
5.0 KiB
SQL
140 lines
5.0 KiB
SQL
-- 酷冰甲 CMS 升级脚本：新增 CRM（客户管理）与 PSI（进销存）模块
|
||
-- 用法：在远端 MySQL 执行本文件（phpMyAdmin / 命令行均可）。
|
||
-- 幂等，可重复执行；兼容 MySQL 5.7 与 8.0（5.7 不支持 ADD COLUMN IF NOT EXISTS，故用存储过程判断）。
|
||
SET NAMES utf8mb4;
|
||
|
||
/* ===================== CRM 客户管理系统 ===================== */
|
||
CREATE TABLE IF NOT EXISTS `crm_customers` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`name` VARCHAR(120) NOT NULL,
|
||
`company` VARCHAR(160) DEFAULT '',
|
||
`contact` VARCHAR(60) DEFAULT '',
|
||
`phone` VARCHAR(40) DEFAULT '',
|
||
`email` VARCHAR(120) DEFAULT '',
|
||
`country` VARCHAR(60) DEFAULT '',
|
||
`type` VARCHAR(20) DEFAULT 'trade',
|
||
`source` VARCHAR(40) DEFAULT '',
|
||
`level` VARCHAR(20) DEFAULT 'C',
|
||
`remark` TEXT,
|
||
`owner` VARCHAR(60) DEFAULT '',
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `crm_leads` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`customer_id` INT DEFAULT 0,
|
||
`title` VARCHAR(200) NOT NULL,
|
||
`amount` DECIMAL(12,2) DEFAULT 0,
|
||
`stage` VARCHAR(20) DEFAULT 'new',
|
||
`expected_close` VARCHAR(20) DEFAULT '',
|
||
`owner` VARCHAR(60) DEFAULT '',
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `crm_followups` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`customer_id` INT DEFAULT 0,
|
||
`lead_id` INT DEFAULT 0,
|
||
`content` TEXT,
|
||
`next_at` VARCHAR(20) DEFAULT '',
|
||
`owner` VARCHAR(60) DEFAULT '',
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
/* ===================== 进销存 PSI 系统 ===================== */
|
||
CREATE TABLE IF NOT EXISTS `psi_materials` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`code` VARCHAR(40) DEFAULT '',
|
||
`name` VARCHAR(160) NOT NULL,
|
||
`spec` VARCHAR(120) DEFAULT '',
|
||
`unit` VARCHAR(10) DEFAULT '个',
|
||
`category` VARCHAR(40) DEFAULT '',
|
||
`stock` DECIMAL(12,2) DEFAULT 0,
|
||
`price` DECIMAL(10,2) DEFAULT 0,
|
||
`supplier_id` INT DEFAULT 0,
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `psi_products` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`code` VARCHAR(40) DEFAULT '',
|
||
`name` VARCHAR(160) NOT NULL,
|
||
`spec` VARCHAR(120) DEFAULT '',
|
||
`unit` VARCHAR(10) DEFAULT '件',
|
||
`category` VARCHAR(40) DEFAULT '',
|
||
`stock` DECIMAL(12,2) DEFAULT 0,
|
||
`cost` DECIMAL(10,2) DEFAULT 0,
|
||
`price` DECIMAL(10,2) DEFAULT 0,
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `psi_suppliers` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`name` VARCHAR(160) NOT NULL,
|
||
`contact` VARCHAR(60) DEFAULT '',
|
||
`phone` VARCHAR(40) DEFAULT '',
|
||
`country` VARCHAR(60) DEFAULT '',
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `psi_purchases` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`order_no` VARCHAR(40) NOT NULL,
|
||
`supplier_id` INT DEFAULT 0,
|
||
`item_type` VARCHAR(10) DEFAULT 'material',
|
||
`item_id` INT DEFAULT 0,
|
||
`qty` DECIMAL(12,2) DEFAULT 0,
|
||
`price` DECIMAL(10,2) DEFAULT 0,
|
||
`amount` DECIMAL(12,2) DEFAULT 0,
|
||
`status` VARCHAR(20) DEFAULT 'pending',
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `psi_sales` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`order_no` VARCHAR(40) NOT NULL,
|
||
`customer` VARCHAR(160) DEFAULT '',
|
||
`channel` VARCHAR(20) DEFAULT 'domestic',
|
||
`item_id` INT DEFAULT 0,
|
||
`qty` DECIMAL(12,2) DEFAULT 0,
|
||
`price` DECIMAL(10,2) DEFAULT 0,
|
||
`amount` DECIMAL(12,2) DEFAULT 0,
|
||
`status` VARCHAR(20) DEFAULT 'pending',
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `psi_stock_moves` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`item_type` VARCHAR(10) DEFAULT 'product',
|
||
`item_id` INT DEFAULT 0,
|
||
`direction` VARCHAR(10) DEFAULT 'in',
|
||
`qty` DECIMAL(12,2) DEFAULT 0,
|
||
`ref_no` VARCHAR(40) DEFAULT '',
|
||
`remark` TEXT,
|
||
`created_at` VARCHAR(20) DEFAULT ''
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
/* 为 admin_users 补齐分系统角色列（幂等，兼容 5.7） */
|
||
DROP PROCEDURE IF EXISTS `add_crm_psi_cols`;
|
||
DELIMITER $$
|
||
CREATE PROCEDURE `add_crm_psi_cols`()
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admin_users' AND COLUMN_NAME='crm_role') THEN
|
||
ALTER TABLE `admin_users` ADD COLUMN `crm_role` VARCHAR(20) DEFAULT 'none';
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admin_users' AND COLUMN_NAME='psi_role') THEN
|
||
ALTER TABLE `admin_users` ADD COLUMN `psi_role` VARCHAR(20) DEFAULT 'none';
|
||
END IF;
|
||
END$$
|
||
DELIMITER ;
|
||
CALL `add_crm_psi_cols`();
|
||
DROP PROCEDURE IF EXISTS `add_crm_psi_cols`;
|
||
|
||
-- 升级完成后，登录后台 → 用户管理，将超级管理员/相关账号的 crm_role、psi_role 设为 admin。
|