CREATE DATABASEIFNOTEXISTS inventory_db;

USE inventory_db;

-- 商品庫(kù)存表
CREATETABLE product_stock (
    idINT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOTNULL,
    stock_quantity INTNOTNULL,
    threshold INTNOTNULLDEFAULT10-- 庫(kù)存預(yù)警閾值
);

-- 插入一些測(cè)試數(shù)據(jù)
INSERTINTO product_stock (product_name, stock_quantity, threshold)
VALUES ('Product A', 15, 10),
       ('Product B', 8, 10),
       ('Product C', 20, 10);

-- 創(chuàng)建預(yù)警記錄表
CREATETABLEIFNOTEXISTS stock_alerts (
    idINT AUTO_INCREMENT PRIMARY KEY,
    product_id INTNOTNULL,
    alert_time TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    FOREIGNKEY (product_id) REFERENCES product_stock(id)
);

-- 創(chuàng)建觸發(fā)器，在庫(kù)存數(shù)量減少到閾值以下時(shí)插入一條預(yù)警記錄
DELIMITER //

CREATETRIGGER after_update_product_stock
AFTERUPDATEON product_stock
FOREACHROW
BEGIN
    IF NEW.stock_quantity < NEW.threshold THEN
        INSERTINTO stock_alerts (product_id) VALUES (NEW.id);
    ENDIF;
END//

DELIMITER ;

<dependencies>
    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- MyBatis Framework -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Lombok for reducing boilerplate code -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <!-- Spring Boot Starter Mail -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-mail</artifactId>
    </dependency>

    <!-- Spring Boot Starter Task -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-task</artifactId>
    </dependency>
</dependencies>

# DataSource Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/inventory_db?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=password

# MyBatis Mapper Location
mybatis.mapper-locations=classpath*:mapper/*.xml

# Email Configuration
spring.mail.host=smtp.gmail.com
spring.mail.port=587
spring.mail.username=fg456hj@gmail.com
spring.mail.password=AAbb123456
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true

package com.example.inventory.model;

import lombok.Data;

@Data
public class ProductStock {
    private Integer id;           // 主鍵ID
    private String productName;   // 商品名稱
    private Integer stockQuantity;// 庫(kù)存量
    private Integer threshold;    // 預(yù)警閾值
}

package com.example.inventory.mapper;

import com.example.inventory.model.ProductStock;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
publicinterface ProductStockMapper {

    @Select("SELECT * FROM product_stock WHERE id = #{id}")
    ProductStock getProductById(Integer id); // 根據(jù)ID獲取商品庫(kù)存信息

    @Update("UPDATE product_stock SET stock_quantity = stock_quantity - #{quantity} WHERE id = #{id}")
    void reduceStock(@Param("id") Integer id, @Param("quantity") Integer quantity); // 減少指定ID的商品庫(kù)存

    @Select("SELECT COUNT(*) FROM stock_alerts WHERE product_id = #{productId}")
    int getAlertCountForProduct(Integer productId); // 獲取指定商品ID的預(yù)警次數(shù)

    @Select("SELECT * FROM product_stock WHERE stock_quantity < threshold")
    List<ProductStock> getLowStockProducts(); // 獲取所有低于閾值的商品庫(kù)存信息
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.inventory.mapper.ProductStockMapper">
    <!-- Additional mappings if needed can be defined here -->
</mapper>

package com.example.inventory.service;

import com.example.inventory.mapper.ProductStockMapper;
import com.example.inventory.model.ProductStock;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;

@Service
publicclass InventoryService {

    @Autowired
    private ProductStockMapper productStockMapper;

    @Autowired
    private JavaMailSender mailSender;

    public ProductStock getProductById(int id) {
        return productStockMapper.getProductById(id); // 根據(jù)ID獲取商品庫(kù)存信息
    }

    @Transactional
    public boolean reduceStock(int productId, int quantity) {
        productStockMapper.reduceStock(productId, quantity); // 減少指定ID的商品庫(kù)存
        int alertCount = productStockMapper.getAlertCountForProduct(productId); // 獲取預(yù)警次數(shù)
        // 如果有預(yù)警，則發(fā)送郵件通知
        if (alertCount > 0) {
            sendAlertEmail(productStockMapper.getProductById(productId));
        }
        return alertCount > 0;
    }

    @Scheduled(fixedRate = 3600000) // 每小時(shí)執(zhí)行一次
    public void checkAndSendAlerts() {
        List<ProductStock> lowStockProducts = productStockMapper.getLowStockProducts(); // 獲取所有低于閾值的商品庫(kù)存信息
        for (ProductStock product : lowStockProducts) {
            sendAlertEmail(product); // 發(fā)送郵件通知
        }
    }

    private void sendAlertEmail(ProductStock product) {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setTo("recipient@example.com"); // 收件人郵箱地址
        message.setSubject("Inventory Alert: Low Stock for " + product.getProductName()); // 郵件主題
        message.setText("The stock quantity for " + product.getProductName() +
                " has dropped below the threshold. Current stock: " + product.getStockQuantity()); // 郵件正文
        mailSender.send(message); // 發(fā)送郵件
    }
}

package com.example.inventory.controller;

import com.example.inventory.model.ProductStock;
import com.example.inventory.service.InventoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/products")
publicclass ProductController {

    @Autowired
    private InventoryService inventoryService;

    @GetMapping("/{id}")
    public ProductStock getProductById(@PathVariable int id) {
        return inventoryService.getProductById(id); // 根據(jù)ID獲取商品庫(kù)存信息
    }

    @PostMapping("/{id}/reduce/{quantity}")
    public boolean reduceStock(@PathVariable int id, @PathVariable int quantity) {
        return inventoryService.reduceStock(id, quantity); // 減少指定ID的商品庫(kù)存并檢查是否需要發(fā)送預(yù)警
    }
}

package com.example.inventory;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.JavaMailSenderImpl;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.util.Properties;

@SpringBootApplication
@MapperScan("com.example.inventory.mapper")
@EnableScheduling
publicclass InventoryApplication {

    public static void main(String[] args) {
        SpringApplication.run(InventoryApplication.class, args);
    }

    @Bean
    public JavaMailSender getJavaMailSender() {
        JavaMailSenderImpl mailSender = new JavaMailSenderImpl();
        mailSender.setHost("smtp.gmail.com");
        mailSender.setPort(587);

        mailSender.setUsername("fg456hj@gmail.com"); // 替換為您的Gmail地址
        mailSender.setPassword("AAbb123456"); // 替換為您的Gmail密碼

        Properties props = mailSender.getJavaMailProperties();
        props.put("mail.transport.protocol", "smtp");
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.starttls.enable", "true");
        props.put("mail.debug", "true");

        return mailSender;
    }
}