摄影工作室器材管理数据库部分

于 2025-06-26 20:58:23 发布
阅读量190 收藏
点赞数 8
CC 4.0 BY-SA版权
分类专栏： 数据库 文章标签： 数据库 摄影室器材管理数据库代码
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2303_80172110/article/details/148932924 
-- 创建数据库
DROP DATABASE IF EXISTS PhotoStudioDB;
CREATE DATABASE PhotoStudioDB;
USE PhotoStudioDB;

-- 1. 先创建供应商表（无外键依赖）
CREATE TABLE Supplier (
    SupplierID INT AUTO_INCREMENT PRIMARY KEY,
    SupplierName VARCHAR(100) NOT NULL,
    ContactPerson VARCHAR(50),
    Phone VARCHAR(20) NOT NULL,
    Email VARCHAR(100),
    Address TEXT,
    ServicePolicy TEXT,
    RepairNetwork TEXT,
    ContractStartDate DATE,
    ContractEndDate DATE,
    Rating TINYINT CHECK (Rating BETWEEN 1 AND 5),
    UNIQUE KEY uk_supplier_phone (Phone),
    UNIQUE KEY uk_supplier_email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 创建存放区表（无外键依赖）
CREATE TABLE StorageArea (
    AreaID INT AUTO_INCREMENT PRIMARY KEY,
    AreaName VARCHAR(50) NOT NULL,
    Location VARCHAR(100) NOT NULL,
    EnvironmentRequirement TEXT,
    Capacity INT,
    CurrentCount INT DEFAULT 0,
    Manager VARCHAR(50),
    LastCheckDate DATE,
    UNIQUE KEY uk_area_location (AreaName, Location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 创建员工表（无外键依赖）
CREATE TABLE Staff (
    StaffID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Gender ENUM('男','女','其他'),
    Department VARCHAR(50),
    Position VARCHAR(50),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    HireDate DATE,
    IsActive BOOLEAN DEFAULT TRUE,
    UNIQUE KEY uk_staff_phone (Phone),
    UNIQUE KEY uk_staff_email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 现在可以创建器材表（引用StorageArea和Supplier）
CREATE TABLE Equipment (
    EquipmentID INT AUTO_INCREMENT PRIMARY KEY,
    EquipmentName VARCHAR(100) NOT NULL,
    Brand VARCHAR(50) NOT NULL,
    Model VARCHAR(50) NOT NULL,
    Type ENUM('相机','镜头','三脚架','灯光','附件') NOT NULL,
    Pixel VARCHAR(20),
    PurchaseDate DATE NOT NULL,
    Price DECIMAL(10,2),
    Status ENUM('可用','借出','维修中','报废') DEFAULT '可用',
    StorageAreaID INT,
    SupplierID INT,
    LastMaintenanceDate DATE,
    NextMaintenanceDate DATE,
    Remarks TEXT,
    FOREIGN KEY (StorageAreaID) REFERENCES StorageArea(AreaID) ON DELETE SET NULL,
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID) ON DELETE SET NULL,
    INDEX idx_equipment_type (Type),
    INDEX idx_equipment_status (Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 创建入库记录表（引用Equipment和Staff）
CREATE TABLE StockIn (
    StockInID INT AUTO_INCREMENT PRIMARY KEY,
    EquipmentID INT NOT NULL,
    OperatorID INT NOT NULL,
    InspectionReport TEXT NOT NULL,
    InspectionDate DATE NOT NULL,
    InspectionResult ENUM('合格','不合格','待修') NOT NULL,
    Quantity INT DEFAULT 1,
    InvoiceNumber VARCHAR(50),
    PurchaseOrder VARCHAR(50),
    FOREIGN KEY (EquipmentID) REFERENCES Equipment(EquipmentID) ON DELETE CASCADE,
    FOREIGN KEY (OperatorID) REFERENCES Staff(StaffID),
    INDEX idx_stockin_date (InspectionDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 创建借还记录表（引用Equipment和Staff）
CREATE TABLE BorrowReturn (
    RecordID INT AUTO_INCREMENT PRIMARY KEY,
    EquipmentID INT NOT NULL,
    BorrowerID INT NOT NULL,
    BorrowDate DATETIME NOT NULL,
    ExpectedReturnDate DATETIME NOT NULL,
    ActualReturnDate DATETIME,
    BorrowPurpose TEXT,
    ApprovalStatus ENUM('待审批','已批准','已拒绝') DEFAULT '待审批',
    ApprovalID INT,
    ApprovalTime DATETIME,
    ReturnCondition ENUM('完好','轻微损坏','严重损坏'),
    DamageDescription TEXT,
    FOREIGN KEY (EquipmentID) REFERENCES Equipment(EquipmentID),
    FOREIGN KEY (BorrowerID) REFERENCES Staff(StaffID),
    FOREIGN KEY (ApprovalID) REFERENCES Staff(StaffID) ON DELETE SET NULL,
    CHECK (ExpectedReturnDate > BorrowDate),
    CHECK (ActualReturnDate IS NULL OR ActualReturnDate >= BorrowDate),
    INDEX idx_borrow_status (ApprovalStatus),
    INDEX idx_borrow_date (BorrowDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. 创建维修记录表（引用Equipment）
CREATE TABLE Maintenance (
    MaintenanceID INT AUTO_INCREMENT PRIMARY KEY,
    EquipmentID INT NOT NULL,
    FaultDescription TEXT NOT NULL,
    ReportDate DATETIME NOT NULL,
    RepairStatus ENUM('待处理','维修中','已修复','无法修复') DEFAULT '待处理',
    RepairCost DECIMAL(10,2),
    RepairDate DATETIME,
    Repairer VARCHAR(50),
    RepairNotes TEXT,
    FOREIGN KEY (EquipmentID) REFERENCES Equipment(EquipmentID),
    INDEX idx_maintenance_status (RepairStatus),
    INDEX idx_maintenance_date (ReportDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入供应商数据
INSERT INTO Supplier (SupplierName, ContactPerson, Phone, Email, Address, ServicePolicy, RepairNetwork, ContractStartDate, ContractEndDate, Rating) VALUES
('佳能中国', '张经理', '13800138001', 'canon@example.com', '北京市朝阳区佳能大厦', '3年保修，全国联保', '北京、上海、广州、深圳等20个城市设有维修中心', '2024-01-01', '2026-12-31', 5),
('尼康映像', '李总监', '13900139002', 'nikon@example.com', '上海市浦东新区尼康中心', '2年保修，主要城市联保', '北京、上海、成都、武汉等10个维修点', '2024-03-15', '2025-12-31', 4),
('索尼专业', '王技术', '13600136003', 'sony@example.com', '广州市天河区索尼维修站', '1年保修，原厂维修', '仅广州总部提供专业维修服务', '2024-02-10', '2025-02-09', 3),
('曼富图中国', '赵销售', '13500135004', 'manfrotto@example.com', '深圳市南山区曼富图办事处', '2年保修，配件更换服务', '北京、深圳双维修中心', '2024-05-20', '2026-05-19', 4),
('大疆', '李经理', '13900138001', 'cadddon@exqqmple.com', '北京市朝阳区大疆大厦', '3年保修，全国联保', '北京、上海、广州、深圳等20个城市设有维修中心', '2024-01-01', '2026-12-31', 5),
('影石', '王销售', '13520135004', 'manfrydtto@exqample.com', '深圳市南山区yingshi办事处', '2年保修，配件更换服务', '北京、深圳双维修中心', '2024-05-20', '2026-05-19', 4);

-- 插入存放区数据
INSERT INTO StorageArea (AreaName, Location, EnvironmentRequirement, Capacity, Manager, LastCheckDate) VALUES
('A区-相机柜', '1楼器材室东侧', '恒温20±2℃，湿度40-60%，防尘防静电', 50, '张管理员', '2025-05-15'),
('B区-镜头柜', '1楼器材室西侧', '恒温18-22℃，湿度35-55%，防霉防雾', 100, '李管理员', '2025-05-10'),
('C区-三脚架区', '1楼器材室北侧', '常温存放，防潮防磕碰', 30, '王管理员', '2025-05-12'),
('D区-灯光设备', '2楼专用仓库', '恒温15-25℃，湿度30-50%，防震', 20, '赵管理员', '2025-05-18');

-- 插入员工数据
INSERT INTO Staff (Name, Gender, Department, Position, Phone, Email, HireDate, IsActive) VALUES
('张三', '男', '摄影部', '首席摄影师', '13800138011', 'zhangsan@studio.com', '2020-06-15', TRUE),
('李四', '女', '摄影部', '助理摄影师', '13900139022', 'lisi@studio.com', '2021-03-20', TRUE),
('王五', '男', '器材部', '器材管理员', '13600136033', 'wangwu@studio.com', '2019-11-05', TRUE),
('赵六', '女', '行政部', '行政主管', '13500135044', 'zhaoliu@studio.com', '2018-09-10', TRUE),
('钱七', '男', '维修部', '技术工程师', '13400134055', 'qianqi@studio.com', '2022-02-28', TRUE),
('小李', '男', '摄影部', '首席摄影师', '13800198011', 'zhaeesan@stddudio.com', '2020-07-15', TRUE),
('张五', '女', '摄影部', '助理摄影师', '13900165022', 'lidsi@studddio.com', '2021-06-20', TRUE);

-- 插入器材数据
INSERT INTO Equipment (EquipmentName, Brand, Model, Type, Pixel, PurchaseDate, Price, Status, StorageAreaID, SupplierID, LastMaintenanceDate, NextMaintenanceDate, Remarks) VALUES
('全画幅专业相机', 'Canon', 'EOS R5', '相机', '45MP', '2024-01-10', 25999.00, '可用', 1, 1, '2025-04-01', '2025-10-01', '主力拍摄设备，配RF24-70镜头'),
('高像素机身', 'Sony', 'A7R IV', '相机', '61MP', '2023-11-15', 21999.00, '借出', 1, 3, '2025-03-15', '2025-09-15', '商业拍摄专用'),
('标准变焦镜头', 'Canon', 'RF24-70mm F2.8', '镜头', NULL, '2024-01-10', 12999.00, '可用', 2, 1, '2025-04-01', NULL, '与EOS R5配套使用'),
('长焦定焦镜头', 'Nikon', '70-200mm F2.8', '镜头', NULL, '2023-09-20', 14999.00, '维修中', 2, 2, '2024-12-10', '2025-06-10', '镜片有轻微划痕'),
('专业三脚架', 'Manfrotto', 'MT055CXPRO4', '三脚架', NULL, '2022-05-18', 3999.00, '可用', 3, 4, '2025-01-20', '2026-01-20', '重型三脚架，承重8kg'),
('便携三脚架', 'Manfrotto', 'Befree Advanced', '三脚架', NULL, '2024-02-28', 1999.00, '可用', 3, 4, NULL, NULL, '外拍便携设备'),
('影视灯光', 'Aputure', '300D II', '灯光', NULL, '2023-07-10', 5999.00, '可用', 4, NULL, '2025-03-10', '2025-09-10', '常亮LED影视灯');

-- 插入入库记录
INSERT INTO StockIn (EquipmentID, OperatorID, InspectionReport, InspectionDate, InspectionResult, Quantity, InvoiceNumber, PurchaseOrder) VALUES
(1, 3, '全新未拆封，包装完好，功能测试正常', '2024-01-12', '合格', 1, 'INV20240112001', 'PO202401001'),
(2, 3, '机身无划痕，CMOS无坏点，快门测试正常', '2023-11-18', '合格', 1, 'INV20231115002', 'PO202311002'),
(3, 3, '镜片无尘无霉，对焦环变焦环顺滑', '2024-01-12', '合格', 1, 'INV20240112003', 'PO202401001'),
(4, 3, '镜片边缘有轻微划痕，不影响成像，功能正常', '2023-09-25', '合格', 1, 'INV20230920004', 'PO202309003'),
(5, 3, '关节灵活，锁紧装置正常，无变形', '2022-05-20', '合格', 1, 'INV20220518005', 'PO202205004'),
(6, 3, '轻量化设计，展开后稳定，配件齐全', '2024-03-01', '合格', 1, 'INV20240228006', 'PO202402005'),
(7, 3, '亮度调节正常，色温准确，散热良好', '2023-07-15', '合格', 1, 'INV20230710007', 'PO202307006');

-- 插入借还记录
INSERT INTO BorrowReturn (EquipmentID, BorrowerID, BorrowDate, ExpectedReturnDate, ActualReturnDate, BorrowPurpose, ApprovalStatus, ApprovalID, ApprovalTime, ReturnCondition, DamageDescription) VALUES
(2, 1, '2025-05-10 09:30:00', '2025-05-17 18:00:00', NULL, '商业广告拍摄', '已批准', 4, '2025-05-09 14:15:00', NULL, NULL),
(1, 2, '2025-04-15 10:00:00', '2025-04-20 18:00:00', '2025-04-19 15:30:00', '婚纱外景拍摄', '已批准', 4, '2025-04-14 16:20:00', '完好', NULL),
(4, 1, '2025-03-01 08:45:00', '2025-03-08 18:00:00', '2025-03-05 11:20:00', '体育赛事拍摄', '已批准', 4, '2025-02-28 15:10:00', '轻微损坏', '镜筒有轻微磕碰痕迹'),
(3, 2, '2025-05-18 13:15:00', '2025-05-25 18:00:00', NULL, '产品静物拍摄', '已批准', 4, '2025-05-17 10:30:00', NULL, NULL);

-- 插入维修记录
INSERT INTO Maintenance (EquipmentID, FaultDescription, ReportDate, RepairStatus, RepairCost, RepairDate, Repairer, RepairNotes) VALUES
(4, '镜筒磕碰导致对焦环卡顿', '2025-03-05 12:00:00', '已修复', 1200.00, '2025-03-10 00:00:00', '钱七', '更换对焦环组件，重新校准'),
(2, 'CMOS传感器清洁', '2025-04-02 10:30:00', '已修复', 300.00, '2025-04-03 00:00:00', '钱七', '专业清洁传感器灰尘'),
(5, '云台锁紧装置松动', '2025-01-15 14:20:00', '已修复', 500.00, '2025-01-18 00:00:00', '钱七', '更换锁紧螺丝并加固'),
(7, '电源接口接触不良', '2025-02-20 11:00:00', '已修复', 800.00, '2025-02-22 00:00:00', '钱七', '更换电源接口模块');


-- 数据删除操作
-- 1. 删除供应商 (注意外键约束)
DELETE FROM Supplier WHERE SupplierName = '影石';
/* 注意：如果该供应商已被器材表引用，需要先处理相关器材记录 */

-- 2. 删除存放区 (注意外键约束)
DELETE FROM StorageArea WHERE AreaName = 'D区-灯光设备';
/* 注意：如果该存放区有器材存放，需要先更新这些器材的存放位置 */

-- 3. 删除员工 (注意外键约束)
DELETE FROM Staff WHERE Name = '小李';
/* 注意：如果员工有审批、操作或借用记录，需要先处理这些记录 */

-- 4. 删除器材 (会级联删除入库记录)
DELETE FROM Equipment WHERE EquipmentID = 6;

-- 5. 删除入库记录
DELETE FROM StockIn WHERE StockInID = 7;

-- 6. 删除借还记录
DELETE FROM BorrowReturn WHERE RecordID = 4;

-- 7. 删除维修记录
DELETE FROM Maintenance WHERE MaintenanceID = 4;

-- 更新操作
-- 1. 更新供应商信息
UPDATE Supplier 
SET ContactPerson = '张新经理', Phone = '13800138888'
WHERE SupplierID = 1;

-- 2. 更新存放区容量
UPDATE StorageArea 
SET Capacity = 60, LastCheckDate = CURDATE()
WHERE AreaID = 1;

-- 3. 更新员工状态
UPDATE Staff 
SET IsActive = FALSE 
WHERE StaffID = 6;

-- 4. 更新器材状态
UPDATE Equipment 
SET Status = '报废', Remarks = '设备进水损坏无法修复'
WHERE EquipmentID = 4;

-- 5. 更新借还记录状态
UPDATE BorrowReturn 
SET ActualReturnDate = NOW(), ReturnCondition = '完好'
WHERE RecordID = 1;

-- 6. 更新维修记录状态
UPDATE Maintenance 
SET RepairStatus = '无法修复', RepairNotes = '缺少替换零件'
WHERE MaintenanceID = 3;

DELIMITER //
-- 1. 器材借出存储过程
CREATE PROCEDURE BorrowEquipment(
    IN p_EquipmentID INT,
    IN p_BorrowerID INT,
    IN p_ExpectedReturnDate DATETIME,
    IN p_BorrowPurpose TEXT,
    IN p_ApproverID INT,
    OUT p_Result VARCHAR(100))
BEGIN
    DECLARE v_Status VARCHAR(20);
    DECLARE v_ApproverExists INT;
    
    -- 检查器材是否存在且可用
    SELECT Status INTO v_Status FROM Equipment WHERE EquipmentID = p_EquipmentID;
    
    IF v_Status IS NULL THEN
        SET p_Result = '错误：器材不存在';
    ELSEIF v_Status != '可用' THEN
        SET p_Result = CONCAT('错误：器材当前状态为', v_Status, '，不可借出');
    ELSE
        -- 检查审批人是否存在
        SELECT COUNT(*) INTO v_ApproverExists FROM Staff WHERE StaffID = p_ApproverID;
        
        IF v_ApproverExists = 0 THEN
            SET p_Result = '错误：审批人不存在';
        ELSE
            -- 插入借出记录
            INSERT INTO BorrowReturn (
                EquipmentID, 
                BorrowerID, 
                BorrowDate, 
                ExpectedReturnDate, 
                BorrowPurpose, 
                ApprovalStatus, 
                ApprovalID, 
                ApprovalTime
            ) VALUES (
                p_EquipmentID,
                p_BorrowerID,
                NOW(),
                p_ExpectedReturnDate,
                p_BorrowPurpose,
                '已批准',
                p_ApproverID,
                NOW()
            );
            
            -- 更新器材状态
            UPDATE Equipment SET Status = '借出' WHERE EquipmentID = p_EquipmentID;
            
            SET p_Result = '成功：器材借出记录已创建';
        END IF;
    END IF;
END //

-- 2. 器材归还存储过程
CREATE PROCEDURE ReturnEquipment(
    IN p_RecordID INT,
    IN p_ReturnCondition ENUM('完好','轻微损坏','严重损坏'),
    IN p_DamageDescription TEXT,
    OUT p_Result VARCHAR(100))
BEGIN
    DECLARE v_EquipmentID INT;
    DECLARE v_Status VARCHAR(20);
    
    -- 获取借出记录中的器材ID
    SELECT EquipmentID INTO v_EquipmentID FROM BorrowReturn WHERE RecordID = p_RecordID;
    
    IF v_EquipmentID IS NULL THEN
        SET p_Result = '错误：借出记录不存在';
    ELSE
        -- 更新借还记录
        UPDATE BorrowReturn 
        SET 
            ActualReturnDate = NOW(),
            ReturnCondition = p_ReturnCondition,
            DamageDescription = p_DamageDescription
        WHERE RecordID = p_RecordID;
        
        -- 根据归还状态更新器材状态
        IF p_ReturnCondition = '完好' THEN
            UPDATE Equipment SET Status = '可用' WHERE EquipmentID = v_EquipmentID;
        ELSE
            UPDATE Equipment SET Status = '维修中' WHERE EquipmentID = v_EquipmentID;
            
            -- 如果有损坏，自动创建维修记录
            INSERT INTO Maintenance (
                EquipmentID,
                FaultDescription,
                ReportDate,
                RepairStatus
            ) VALUES (
                v_EquipmentID,
                CONCAT('器材归还时发现损坏：', p_DamageDescription),
                NOW(),
                '待处理'
            );
        END IF;
        
        SET p_Result = '成功：器材归还处理完成';
    END IF;
END //

-- 3. 器材维护提醒存储过程
CREATE PROCEDURE CheckMaintenanceDue(
    IN p_DaysBefore INT)
BEGIN
    SELECT 
        e.EquipmentID,
        e.EquipmentName,
        e.Brand,
        e.Model,
        e.LastMaintenanceDate,
        e.NextMaintenanceDate,
        DATEDIFF(e.NextMaintenanceDate, CURDATE()) AS DaysUntilDue
    FROM 
        Equipment e
    WHERE 
        e.NextMaintenanceDate IS NOT NULL
        AND e.NextMaintenanceDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL p_DaysBefore DAY)
        AND e.Status != '报废'
    ORDER BY 
        DaysUntilDue ASC;
END //

DELIMITER ;


-- 触发器模块

DELIMITER //

-- 1. 器材状态变更触发器
CREATE TRIGGER trg_EquipmentStatusChange
AFTER UPDATE ON Equipment
FOR EACH ROW
BEGIN
    IF NEW.Status != OLD.Status THEN
        INSERT INTO EquipmentStatusLog (
            EquipmentID,
            OldStatus,
            NewStatus,
            ChangeTime,
            ChangedBy
        ) VALUES (
            NEW.EquipmentID,
            OLD.Status,
            NEW.Status,
            NOW(),
            CURRENT_USER()
        );
    END IF;
END //

-- 2. 器材借出前检查触发器
CREATE TRIGGER trg_BeforeBorrowInsert
BEFORE INSERT ON BorrowReturn
FOR EACH ROW
BEGIN
    DECLARE v_Status VARCHAR(20);
    
    -- 检查器材状态
    SELECT Status INTO v_Status FROM Equipment WHERE EquipmentID = NEW.EquipmentID;
    
    IF v_Status != '可用' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '器材当前不可借出，状态必须为"可用"';
    END IF;
    
    -- 设置默认审批时间为当前时间
    IF NEW.ApprovalStatus = '已批准' AND NEW.ApprovalTime IS NULL THEN
        SET NEW.ApprovalTime = NOW();
    END IF;
END //

-- 3. 器材归还后自动更新库存位置
CREATE TRIGGER trg_AfterReturnUpdate
AFTER UPDATE ON BorrowReturn
FOR EACH ROW
BEGIN
    -- 只有当实际归还日期从NULL变为有值时才触发
    IF OLD.ActualReturnDate IS NULL AND NEW.ActualReturnDate IS NOT NULL THEN
        -- 如果器材状态是"借出"，则更新为"可用"
        UPDATE Equipment 
        SET Status = '可用'
        WHERE EquipmentID = NEW.EquipmentID AND Status = '借出';
        
        -- 记录归还操作
        INSERT INTO EquipmentReturnLog (
            RecordID,
            EquipmentID,
            ReturnTime,
            ReturnCondition,
            ProcessedBy
        ) VALUES (
            NEW.RecordID,
            NEW.EquipmentID,
            NOW(),
            NEW.ReturnCondition,
            CURRENT_USER()
        );
    END IF;
END //

-- 4. 维护完成触发器
CREATE TRIGGER trg_AfterMaintenanceUpdate
AFTER UPDATE ON Maintenance
FOR EACH ROW
BEGIN
    -- 当维修状态变为"已修复"时
    IF NEW.RepairStatus = '已修复' AND OLD.RepairStatus != '已修复' THEN
        -- 更新器材的最后维护日期和状态
        UPDATE Equipment 
        SET 
            LastMaintenanceDate = NEW.RepairDate,
            NextMaintenanceDate = DATE_ADD(NEW.RepairDate, INTERVAL 6 MONTH),
            Status = '可用'
        WHERE EquipmentID = NEW.EquipmentID;
        
        -- 更新借还记录中的损坏描述（如果存在）
        UPDATE BorrowReturn
        SET DamageDescription = CONCAT(DamageDescription, ' | 已修复于', NEW.RepairDate)
        WHERE EquipmentID = NEW.EquipmentID 
          AND ActualReturnDate IS NOT NULL
          AND ReturnCondition != '完好';
    END IF;
END //

DELIMITER ;

-- 视图模块
-- 1. 可用器材视图
CREATE VIEW AvailableEquipment AS
SELECT 
    e.EquipmentID,
    e.EquipmentName,
    e.Brand,
    e.Model,
    e.Type,
    e.Pixel,
    sa.AreaName AS StorageArea,
    sa.Location,
    e.LastMaintenanceDate,
    e.NextMaintenanceDate
FROM Equipment e
JOIN StorageArea sa ON e.StorageAreaID = sa.AreaID
WHERE e.Status = '可用';

-- 2. 当前借出器材视图
CREATE VIEW CurrentlyBorrowedEquipment AS
SELECT 
    br.RecordID,
    e.EquipmentID,
    e.EquipmentName,
    e.Brand,
    e.Model,
    s.Name AS Borrower,
    br.BorrowDate,
    br.ExpectedReturnDate,
    DATEDIFF(br.ExpectedReturnDate, CURDATE()) AS DaysRemaining
FROM BorrowReturn br
JOIN Equipment e ON br.EquipmentID = e.EquipmentID
JOIN Staff s ON br.BorrowerID = s.StaffID
WHERE br.ActualReturnDate IS NULL 
AND br.ApprovalStatus = '已批准';

-- 3. 器材维护计划视图
CREATE VIEW MaintenanceSchedule AS
SELECT 
    e.EquipmentID,
    e.EquipmentName,
    e.Brand,
    e.Model,
    e.LastMaintenanceDate,
    e.NextMaintenanceDate,
    DATEDIFF(e.NextMaintenanceDate, CURDATE()) AS DaysUntilDue,
    CASE 
        WHEN DATEDIFF(e.NextMaintenanceDate, CURDATE()) <= 0 THEN '已过期'
        WHEN DATEDIFF(e.NextMaintenanceDate, CURDATE()) <= 7 THEN '紧急'
        WHEN DATEDIFF(e.NextMaintenanceDate, CURDATE()) <= 30 THEN '即将到期'
        ELSE '计划中'
    END AS Urgency
FROM Equipment e
WHERE e.NextMaintenanceDate IS NOT NULL
AND e.Status != '报废';
-- 4. 器材详细视图（包含供应商信息）
CREATE VIEW EquipmentDetails AS
SELECT 
    e.EquipmentID,
    e.EquipmentName,
    e.Brand,
    e.Model,
    e.Type,
    e.PurchaseDate,
    e.Price,
    e.Status,
    s.SupplierName,
    s.ContactPerson AS SupplierContact,
    s.Phone AS SupplierPhone,
    sa.AreaName AS StorageArea,
    sa.Location
FROM Equipment e
LEFT JOIN Supplier s ON e.SupplierID = s.SupplierID
LEFT JOIN StorageArea sa ON e.StorageAreaID = sa.AreaID;

-- 6. 维修状态视图
CREATE VIEW RepairStatus AS
SELECT 
    m.MaintenanceID,
    e.EquipmentID,
    e.EquipmentName,
    m.FaultDescription,
    m.ReportDate,
    m.RepairStatus,
    m.RepairDate,
    DATEDIFF(CURDATE(), m.ReportDate) AS DaysInRepair
FROM Maintenance m
JOIN Equipment e ON m.EquipmentID = e.EquipmentID
WHERE m.RepairStatus NOT IN ('已修复', '无法修复');

-- 7. 器材使用统计视图
CREATE VIEW EquipmentUsageStats AS
SELECT 
    e.EquipmentID,
    e.EquipmentName,
    COUNT(br.RecordID) AS TotalBorrows,
    SUM(CASE WHEN br.ActualReturnDate IS NULL THEN 1 ELSE 0 END) AS ActiveBorrows,
    MIN(br.BorrowDate) AS FirstBorrowDate,
    MAX(br.BorrowDate) AS LastBorrowDate,
    AVG(DATEDIFF(br.ActualReturnDate, br.BorrowDate)) AS AvgBorrowDays
FROM Equipment e
LEFT JOIN BorrowReturn br ON e.EquipmentID = br.EquipmentID
GROUP BY e.EquipmentID, e.EquipmentName;

-- 8. 损坏记录视图
CREATE VIEW DamageRecords AS
SELECT 
    br.RecordID,
    e.EquipmentID,
    e.EquipmentName,
    br.BorrowerID,
    s.Name AS Borrower,
    br.ReturnCondition,
    br.DamageDescription,
    br.ActualReturnDate,
    m.RepairStatus,
    m.RepairCost
FROM BorrowReturn br
JOIN Equipment e ON br.EquipmentID = e.EquipmentID
JOIN Staff s ON br.BorrowerID = s.StaffID
LEFT JOIN Maintenance m ON m.EquipmentID = e.EquipmentID 
    AND m.ReportDate = (
        SELECT MAX(ReportDate) 
        FROM Maintenance 
        WHERE EquipmentID = e.EquipmentID
    )
WHERE br.ReturnCondition != '完好' 
OR br.DamageDescription IS NOT NULL;

Borrowreturn表截图

Equipment截图

 

Maintenance截图 

Staff截图

 

 Stockin截图

 Storagearea

supplier

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值