antissoft / lvqianmeiye_ERP

-- ============================================
-- 库存管理审批功能相关表结构SQL
-- ============================================
-- 说明：为库存使用记录添加审批功能，包括申请记录、审批记录等
-- 执行时间：2025年
-- 注意：本SQL只包含实际使用的表和字段，已删除未使用的节点审批人表
-- ============================================

-- ============================================
-- 1. 修改库存使用记录表，添加单价和合计金额字段（如果不存在）
-- ============================================
-- 说明：用于统计成本费用，单价从产品表获取，合计金额 = 单价 × 数量

-- 检查并添加单价字段
SET @dbname = DATABASE();
SET @tablename = 'lq_inventory_usage';
SET @columnname = 'F_UnitPrice';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN `', @columnname, '` decimal(18,2) DEFAULT 0.00 COMMENT ''单价（从产品表F_Price获取）'' AFTER `F_UsageQuantity`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 检查并添加合计金额字段
SET @columnname = 'F_TotalAmount';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN `', @columnname, '` decimal(18,2) DEFAULT 0.00 COMMENT ''合计金额（单价×数量）'' AFTER `F_UnitPrice`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 添加索引（如果不存在）
-- 注意：MySQL 5.7+ 不支持 IF NOT EXISTS，使用存储过程或先检查
-- 这里先检查索引是否存在，如果不存在则添加
SET @indexname = 'idx_unit_price';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (INDEX_NAME = @indexname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD INDEX `', @indexname, '` (`F_UnitPrice`)')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @indexname = 'idx_total_amount';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (INDEX_NAME = @indexname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD INDEX `', @indexname, '` (`F_TotalAmount`)')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 数据迁移：更新已有数据的单价和合计金额（从产品表获取价格）
UPDATE `lq_inventory_usage` u
INNER JOIN `lq_product` p ON u.`F_ProductId` = p.`F_Id`
SET u.`F_UnitPrice` = COALESCE(p.`F_Price`, 0),
    u.`F_TotalAmount` = COALESCE(p.`F_Price`, 0) * u.`F_UsageQuantity`
WHERE (u.`F_UnitPrice` = 0 OR u.`F_UnitPrice` IS NULL) AND p.`F_Price` IS NOT NULL;

-- ============================================
-- 2. 创建库存使用申请表（如果不存在）
-- ============================================
CREATE TABLE IF NOT EXISTS `lq_inventory_usage_application` (
  `F_Id` varchar(50) NOT NULL COMMENT '申请编号',
  `F_UsageBatchId` varchar(50) NOT NULL COMMENT '使用批次ID（关联lq_inventory_usage.F_UsageBatchId）',
  `F_ApplicationUserId` varchar(50) NOT NULL COMMENT '申请人ID',
  `F_ApplicationUserName` varchar(100) DEFAULT NULL COMMENT '申请人姓名',
  `F_ApplicationStoreId` varchar(50) DEFAULT NULL COMMENT '申请门店ID',
  `F_ApplicationTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
  `F_NodeCount` int DEFAULT 1 COMMENT '节点数量（固定为1）',
  `F_CurrentNodeOrder` int DEFAULT 0 COMMENT '当前审批节点（0-待审批，1-审批中，2-已完成）',
  `F_CurrentNodeId` varchar(50) DEFAULT NULL COMMENT '当前节点ID',
  `F_ApprovalStatus` varchar(20) DEFAULT '待审批' COMMENT '审批状态（待审批/审批中/已通过/未通过/已退回）',
  `F_IsReceived` int DEFAULT 0 COMMENT '是否已领取（1-已领取，0-未领取）',
  `F_ReceiveTime` datetime DEFAULT NULL COMMENT '领取时间',
  `F_ReceiveUser` varchar(50) DEFAULT NULL COMMENT '领取人ID',
  `F_Remarks` varchar(500) DEFAULT NULL COMMENT '备注',
  `F_CreateTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `F_CreateUser` varchar(50) DEFAULT NULL COMMENT '创建人ID',
  `F_UpdateTime` datetime DEFAULT NULL COMMENT '更新时间',
  `F_UpdateUser` varchar(50) DEFAULT NULL COMMENT '更新人ID',
  `F_IsEffective` int DEFAULT 1 COMMENT '是否有效（1-有效，0-无效）',
  PRIMARY KEY (`F_Id`),
  UNIQUE KEY `uk_usage_batch_id` (`F_UsageBatchId`),
  KEY `idx_application_user_id` (`F_ApplicationUserId`),
  KEY `idx_application_store_id` (`F_ApplicationStoreId`),
  KEY `idx_current_node` (`F_CurrentNodeId`),
  KEY `idx_approval_status` (`F_ApprovalStatus`),
  KEY `idx_is_received` (`F_IsReceived`),
  KEY `idx_create_time` (`F_CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存使用申请表';

-- ============================================
-- 3. 创建库存使用申请审批记录表（如果不存在）
-- ============================================
CREATE TABLE IF NOT EXISTS `lq_inventory_usage_approval_record` (
  `F_Id` varchar(50) NOT NULL COMMENT '记录编号',
  `F_ApplicationId` varchar(50) NOT NULL COMMENT '申请ID',
  `F_NodeId` varchar(50) NOT NULL COMMENT '节点编号',
  `F_NodeOrder` int NOT NULL DEFAULT 1 COMMENT '节点顺序（固定为1）',
  `F_ApproverId` varchar(50) NOT NULL COMMENT '审批人ID',
  `F_ApproverName` varchar(100) DEFAULT NULL COMMENT '审批人姓名',
  `F_ApprovalResult` varchar(20) NOT NULL COMMENT '审批结果（通过/不通过/退回）',
  `F_ApprovalOpinion` varchar(500) DEFAULT NULL COMMENT '审批意见',
  `F_ApprovalTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '审批时间',
  `F_IsCurrentNode` int DEFAULT 0 COMMENT '是否当前节点（1-是，0-否）',
  PRIMARY KEY (`F_Id`),
  KEY `idx_application_id` (`F_ApplicationId`),
  KEY `idx_node_id` (`F_NodeId`),
  KEY `idx_approver_id` (`F_ApproverId`),
  KEY `idx_node_order` (`F_ApplicationId`, `F_NodeOrder`),
  KEY `idx_approval_result` (`F_ApprovalResult`),
  KEY `idx_approval_time` (`F_ApprovalTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存使用申请审批记录表';

-- ============================================
-- 4. 检查并添加可能缺失的字段（兼容性处理）
-- ============================================

-- 检查 lq_inventory_usage_application 表是否有所需字段
-- 如果表已存在但字段缺失，则添加字段

-- 检查并添加 F_IsReceived 字段（如果不存在）
SET @tablename = 'lq_inventory_usage_application';
SET @columnname = 'F_IsReceived';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN `', @columnname, '` int DEFAULT 0 COMMENT ''是否已领取（1-已领取，0-未领取）'' AFTER `F_ApprovalStatus`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 检查并添加 F_ReceiveTime 字段（如果不存在）
SET @columnname = 'F_ReceiveTime';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN `', @columnname, '` datetime DEFAULT NULL COMMENT ''领取时间'' AFTER `F_IsReceived`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 检查并添加 F_ReceiveUser 字段（如果不存在）
SET @columnname = 'F_ReceiveUser';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN `', @columnname, '` varchar(50) DEFAULT NULL COMMENT ''领取人ID'' AFTER `F_ReceiveTime`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ============================================
-- SQL脚本执行完成
-- ============================================
-- 说明：
-- 1. lq_inventory_usage 表：添加了 F_UnitPrice 和 F_TotalAmount 字段
-- 2. lq_inventory_usage_application 表：创建申请记录表
-- 3. lq_inventory_usage_approval_record 表：创建审批记录表
-- 4. 注意：未使用节点审批人表（lq_inventory_usage_application_node_user），已删除相关实体