? 完整代碼在這里 ?

https://github.com/AKGWSB/SZU-DBSC-LAB3

前言
要求
專案結構規劃
資料庫創建與資料準備
撰寫 show_xxxx 程序以全表列印
撰寫 report_monthly_sale 函式以列印報表
撰寫 add_purchase 函式以實作添加一筆購買記錄
為 purchases 表的 insert 操作注冊觸發器以實作業務邏輯
注冊觸發器以記錄 logs
為增刪改操作撰寫 sql 介面
撰寫資料管理模塊
撰寫下單模塊
撰寫報表模塊
主頁
快速部署

前言

唔銷售管理系統？~~銷售來點作用啊~~

這個實驗比較復雜，用到的東西比較雜，今天 mark 一下，，，

~~主要是周末做了好久太難頂了，，，最近摸魚了，有時間再更新圖形學系列罷（摸了~~

要求

1、建立資料庫以及相應的資料表，包括職工表、顧客表、產品表、供應表、采購表以及日志表，并按需求設定主鍵和外鍵，其中日志表主要描述用戶的具體操作信息，（5 分）

2、撰寫一個程序顯示每個表中的元組，例如撰寫 show_products()顯示在 products 表中所有產品，（6 分）

3、撰寫一個程序報告任何給定產品的每月銷售資訊，例如對于給定的產品 ID，您需要報告產品名稱、月份（月份的前三個字母）、年份、每月銷售總量、每月銷售總金額以及每月的平均銷售價格，（4 分）

4、撰寫將元組添加到購買表和產品表中的程序，例如，使用 add_purchase(pur_no， c_id，e_id，p_id，pur_qty)在購買表中添加一個元組，其中 pur_no，c_id，e_id， p_id 和 pur_qty 為該程序的引數，并且應該根據資料庫中的資料自動計算 total_price, 并且 ptime 應該是當前時間，（7 分）

5、自動往日志表 logs 中添加記錄，（使用觸發器實作）（9 分）

6、當實際進行購買之前，應確保要購買的數量小于等于手頭的數量，否則，應顯示一條用以提示庫存數量不足的提示資訊，并且拒絕購買請求，（4 分）

7、在采購表中添加元組后，應相應地修改產品表中的 qoh 列的值，（16 分）

8、在有例外情況存在時，應彈出適當的視窗用以提示錯誤資訊，例如，某人想查找客戶的購買記錄但輸入了不存在的客戶 ID，此時程式應報告出現問題的原因，（4 分）

專案結構規劃

在資料庫中通過 sql 實作一系列業務邏輯，并且封裝并提供一致的向外 sql 介面（函式 or 程序），然后后端程式統一呼叫該程序從而實作業務邏輯，

此外，后端程式通過 http 表單提交和前端程式建立資料互動，

在這里插入圖片描述
專案功能分析：整個專案分為三個版塊，分別是：

后臺資料管理
列印報表
下單

其中后臺資料管理版塊負責后臺資料的維護，比如增刪改操作，而列印報表版塊則根據提交的產品 id，按照年份和月份列印對應的銷售記錄報表，下單版塊則負責為系統添加一筆新訂單，并且完成相應的事務操作，

在這里插入圖片描述

專案目錄結構介紹：專案目錄大致分為如下的幾個檔案：
在這里插入圖片描述

先來介紹幾個檔案夾，每個檔案夾包含若干 sql 腳本，

其中 init_script 是初始化 sql 腳本，包括建立資料庫，添加資料等操作，

sql_implement 則是幾張表的對外增刪改介面，以存盤程序實作，

stored_function 則包含業務邏輯的操作，比如購買一件商品或者是列印報表，

stored_procedure 則是不包含引數的程序，比如全表列印，

triggers 則是注冊的觸發器，當增刪改操作進行時，執行對應的動作（比如記錄日志），

然后關注到 web 介面相關的代碼，table.bks.php 和 shop.bks.php 是 web 程式的 php 后臺，

其中 table.bks.php 負責處理表的資料管理，比如增刪改操作，而 shop.bks.php 負責處理下單請求，

tableInfo.php 和 shop.php 則是他們對應的前臺界面，monthly_report.php 則是報表列印頁面，負責根據提交的產品 id，列印對應產品的報表，

下面給出整個專案的檔案之間的邏輯關系：
在這里插入圖片描述

資料庫創建與資料準備

在該部分，我們撰寫兩個腳本，分別為 createTables.sql 和 addData.sql，分別完成資料表的創建，并且添加一些資料到表中，

下面是 createTables.sql 的內容：

# change all eid, cid, pid, pur, key_value to int types in the future
drop database if exists lab3;
create database if not exists lab3;
use lab3;

create table employees (
    eid int not null, 
    ename varchar(15),
    city varchar(15),
    primary key(eid)
);

create table customers (
    cid int not null,
    cname varchar(15),
    city varchar(15),
    visits_made int,
    last_visit_time datetime,
    primary key(cid)
);

create table suppliers (
    sid int not null,
    sname varchar(15) not null,
    city varchar(15),
    telephone_no char(11),
    primary key(sid),
    unique(sname)
);

create table products (
    pid int not null,
    pname varchar(15) not null,
    qoh int not null,
    qoh_threshold int,
    original_price decimal(6,2),
    discnt_rate decimal(3,2),
    sid int,
    primary key(pid),
    foreign key (sid) references suppliers (sid)
);

create table purchases (
    pur int not null auto_increment,
    cid int not null,
    eid int not null,
    pid int not null,
    qty int,
    ptime datetime,
    total_price decimal(7,2),
    primary key (pur),
    foreign key (cid) references customers(cid),
    foreign key (eid) references employees(eid),
    foreign key (pid) references products(pid)
);

create table logs (
    logid int not null auto_increment,
    who varchar(10) not null,
    time datetime not null,
    table_name varchar(20) not null,
    operation varchar(6) not null,
    key_value int,
    primary key (logid)
);

下面是 addData.sql 的內容：

use lab3;

# ---- insert into employees:
insert into `employees`
(`eid`, `ename`, `city`)
values
(90071, "bobWang", "GuangZhou"),
(90072, "samZhang", "ShangHai"),
(90073, "ZeMinJiang", "ShangHai"),
(90074, "DaSiMa", "WuHu"),
(90075, "MalaTang", "ChengDu");

# ---- insert into customers:
insert into `customers`
(`cid`, `cname`, `city`, `visits_made`, `last_visit_time`)
values
(114, "ShiYouLao", "HengYang", 3, "2020-12-2 16:03"),
(514, "SCWang", "MaoMing", 2, "2020-12-1 12:12"),
(191, "GaoKuaiDian", "BeiJin", 5, "2020-11-29 06:19"),
(981, "TuanTuan", "NewYork", 9, "2020-12-6 22:59");

# ---- insert into suppliers:
insert into `suppliers`
(`sid`, `sname`, `city`, `telephone_no`)
values
(8081, "HuaWei", "DongGuan", "13670778081"),
(4396, "XiaoMi", "ShangHai", "15343214396"),
(7777, "Apple", "BeiJIn", "18922007777");

# ---- insert into products:
insert into `products`
(`pid`, `pname`, `qoh`, `qoh_threshold`, `original_price`, `discnt_rate`, `sid`)
values
(3306, "mate40", 2147, 999, 778.00, 0.95, 8081),
(3307, "mate40pro", 364, 19, 556.00, 0.95, 8081),
(7158, "RedMi", 100, 50, 220.00, 0.85, 4396),
(7159, "SE-8", 100, 99, 440.00, 0.75, 4396),
(443, "iphoneX", 110, 23, 987.00, 1.00, 7777),
(444, "iphone12", 67, 28, 998.00, 1.00, 7777);

在執行之后，可以看到表中已經有資料：
在這里插入圖片描述

撰寫 show_xxxx 程序以全表列印

我們撰寫 6 個程序，分別列印 6 張表的資料，他們存放在 stored_procedure 目錄下：
在這里插入圖片描述
以 customers 表為例，show_customers.sql 的內容是:

use lab3;
drop procedure if exists show_customers;

delimiter //
create procedure show_customers()
begin
    select * from customers;
end //
delimiter ;

因為代碼過于相似，這里只展示 customers 表的全表列印程序

撰寫 report_monthly_sale 函式以列印報表

我們撰寫 report_monthly_sale 函式，他的入口引數是產品 id，輸出是 6 列資訊，分別是產品名稱，月份，年份，銷量，總價，均價，我們撰寫 report_monthly_sale.sql 并且存盤在 stored_function 目錄下，下面是它的內容：

use lab3;
drop procedure if exists report_monthly_sale;

delimiter //
create procedure report_monthly_sale(in p_id int)
begin
    select 
        products.pname, 
        SUBSTR(DATE_FORMAT(purchases.ptime, "%M"), 1, 3),
        DATE_FORMAT(purchases.ptime, "%Y"),
        SUM(qty),
        SUM(total_price),
        SUM(total_price)/SUM(qty)
    from products, purchases
    where products.pid=p_id and products.pid=purchases.pid
    group by DATE_FORMAT(purchases.ptime, "%Y"), DATE_FORMAT(purchases.ptime, "%m");
end//
delimiter ;

撰寫 add_purchase 函式以實作添加一筆購買記錄

我們撰寫 add_purchase.sql，它存盤在 stored_function 目錄下，值得注意的是，該程序負責 進行容錯判斷（比如是否超量，產品是否存在等），因此回傳的欄位永遠有兩個：

st 表示查詢狀態，0 / 1 失敗 or 成功
msg 表示提示資訊，比如當前查詢是因為什么而導致失敗的

此外，該程序只負責添加一條購買記錄，而扣除產品數量，更新顧客資訊的操作則被放在了 purchases 表的 insert 觸發器中執行，在下面我們會詳細解釋，先來看 add_purchase.sql 的代碼：

use lab3;
drop procedure if exists add_purchase;

delimiter //
create procedure add_purchase(
    in c_id int,
    in e_id int,
    in p_id int,
    in pur_qty int
)
add_purchase:
begin
    # 初始化回傳結果
    declare st, res int default 0;
    declare msg varchar(32) default "query fail";

    # 驗證 cid 是否存在
    select count(cid) from customers where cid=c_id limit 1 into res;
    if res<1 then
        set msg = "customer not found!";
        select st, msg;
        leave add_purchase;
    end if;

    # 驗證 eid 是否存在
    select count(eid) from employees where eid=e_id limit 1 into res;
    if res<1 then
        set msg = "employee not found!";
        select st, msg;
        leave add_purchase;
    end if;

    # 驗證 pid 是否存在
    select count(pid) from products where pid=p_id limit 1 into res;
    if res<1 then
        set msg = "product not found!";
        select st, msg;
        leave add_purchase;
    end if;

    # 驗證 pur_qty 是否超出 qoh
    select qoh from products where pid=p_id into res;
    if res<pur_qty then
        set msg = "purchase quantity out of limit!";
        select st, msg;
        leave add_purchase;
    end if;
    
    # 獲取總價格
    select original_price*discnt_rate from products where pid=p_id into res;
    set res = res*pur_qty;

    # 增加記錄到 purchases 表
    insert into purchases (cid, eid, pid, qty, ptime, total_price) values (c_id, e_id, p_id, pur_qty, now(), res);

    # 回傳成功
    set st = 1;
    set msg = "add purchase success!";
    select st, msg;
end//
delimiter ;

為 purchases 表的 insert 操作注冊觸發器以實作業務邏輯

在我們添加一條購買記錄后，我們實際上并沒有在 add_purchase 中修改產品和顧客的信息

我們需要在 purchases 表被 insert 之后完成這個操作，于是我們撰寫 trigger_insert.sql 文件，其中為除了 logs 外的 5 張表都注冊了插入觸發器，我們來看 purchases 表的觸發器：

# 為 purchases 表注冊 insert 觸發器
# 注意這里我們實作業務邏輯，包括
#   1.減少存貨量 
#   2.修改顧客資訊
#   3.補貨
create trigger triger_insert_purchases
after insert on purchases for each row
begin
    declare res int default 0;

    # 先插入記錄進logs
    insert into logs (`who`, `time`, `table_name`, `operation`, `key_value`)
    values
    (USER(), NOW(), "purchases", "insert", NEW.pur);
    
    # 減少商品存量
    update products set qoh=qoh-NEW.qty where pid=NEW.pid;

    # 修改顧客資訊
    update customers set visits_made=visits_made+1, last_visit_time=now() where cid=NEW.cid;

    # 補貨
    select count(*) from products
    where pid=New.pid and qoh<qoh_threshold
    into res;
    if res>0 then
        # 這里直接帶 old_qoh = qoh+NEW.qty 進去算
        update products set qoh=2*(qoh+NEW.qty) where pid=NEW.pid;
    end if;
end//

注冊觸發器以記錄 logs

我們在 trigger 目錄下放有三個檔案，分別是 trigger_delete.sql, trigger_insert.sql, trigger_update.sql，分別對應增刪改操作的觸發器：

在這里插入圖片描述

以 customers 表為例，我們為其注冊三個觸發器：

# 為 customers 表注冊 insert 觸發器
create trigger triger_insert_customers
after insert on customers for each row
begin
    insert into logs (`who`, `time`, `table_name`, `operation`, `key_value`)
    values
    (USER(), NOW(), "customers", "insert", NEW.cid);
end//

# 為 customers 表注冊 update 觸發器
create trigger triger_update_customers
after update on customers for each row
begin
    insert into logs (`who`, `time`, `table_name`, `operation`, `key_value`)
    values
    (USER(), NOW(), "customers", "update", NEW.cid);
end//

# 為 customers 表注冊 delete 觸發器
create trigger triger_delete_customers
after delete on customers for each row
begin
    insert into logs (`who`, `time`, `table_name`, `operation`, `key_value`)
    values
    (USER(), NOW(), "customers", "delete", OLD.cid);
end//

因為除了 purchases 表的 insert 操作需要特殊處理以外，其他觸發器的注冊都十分相似，我們這里僅展示 customers 表的代碼

為增刪改操作撰寫 sql 介面

我們撰寫 18 個.sql 腳本，分別對應 6 張表的增刪改操作，對于每個介面，我們都有如下的回傳值，以方便容錯并且告知用戶操作是否成功：

st 表示查詢狀態，0 / 1 失敗 or 成功
msg 表示提示資訊，比如當前查詢是因為什么而導致失敗的

以 customers 表為例，我們來看看 update_customers.sql 的內容，

其中我們輸入的是 customers 表的所有欄位，然后我們檢查是否存在目標記錄，如果不存在我們將會提供容錯并且回傳錯誤資訊，如果存在那么我們直接更新：

use lab3;
drop procedure if exists insert_customer;

delimiter //
create procedure insert_customer(
    in _cid int,
    in _cname varchar(15),
    in _city varchar(15),
    in _visit_made int,
    in _last_visit_time datetime
)
insert_customer:
begin
    # 初始化回傳結果
    declare st, res int default 0;
    declare msg varchar(32) default "query fail";

    # 檢查是否存在 cid 相同的記錄
    select count(cid) from customers where cid=_cid limit 1 into res;
    if res>0 then
        set msg = "customer already exists!";
        select st, msg;
        leave insert_customer;
    end if;

    # 執行插入陳述句
    insert into customers
    (`cid`, `cname`, `city`, `visits_made`, `last_visit_time`)
    values
    (_cid, _cname, _city, _visit_made, _last_visit_time);

    # 回傳成功
    set st = 1;
    set msg = "insert customer success!";
    select st, msg;
end//
delimiter ;

因為檔案過多，我們僅展示 customers 表的 update 介面，因為他們的代碼都是相似的，

在這里插入圖片描述

撰寫資料管理模塊

資料管理模塊由三個檔案組成，admin.php 是表的概要頁面，該頁面展示所有表的資訊，并且提供跳轉鏈接到表詳情頁，下面是 admin.php 的內容：

<div align="center">
資料庫 lab3 下的所有資料表：
<br><a href=index.html>回傳</a><hr><table border="2">
<?php
include("conn.php");

// 獲取表名
$res = mysqli_query($conn, "show tables");
$row = mysqli_num_rows($res);
for($i=0; $i<$row; $i++) {
    $tableName = mysqli_fetch_array($res)[0];
    // 拼湊 a 標簽 -- 方便跳轉
    echo '<tr><td>';
    echo '<a href="tableInfo.php?tableName=' . $tableName . '">' . $tableName . '</a>';
    echo '</td></tr>';
}

?>
</table>
<hr>
</div>
<style>
    tr {
        text-align: center;
    }
    td {
        padding: 10px;
    }
</style>

在這里插入圖片描述

然后我們撰寫 tableInfo.php，該頁面接收一個名為 tableName 的引數，并且查詢對應的表，同時列印資料，

我們首先根據表名，呼叫剛剛注冊的 show_xxxx 程序，以分別顯示每張表的資訊，此外，我們還提供了增加，修改和洗掉的表單提交，通過表單提交資訊到 table.bks.php 后臺以完成對應的操作，

下面是 tableInfo.php 的代碼：

<div align="center"><table border="2">
<?php
include("conn.php");

// 檢驗提交引數
if(!isset($_GET["tableName"])) {
    die("請選擇資料表");
}
$tableName = $_GET["tableName"];

echo "資料表 " . $tableName . " 下的資料記錄有:<br><a href=admin.php>回傳</a><hr>";

// 列印表頭
$columns = array();
$res = mysqli_query($conn, "show columns from " . $tableName);
$row = mysqli_num_rows($res);
echo '<tr style="background-color:red">';
for($i=0; $i<$row; $i++) {
    $dbrow = mysqli_fetch_array($res);
    echo '<td><b>' . $dbrow[0] . '</b></td>';
    array_push($columns, $dbrow[0]);
}
echo '<td><b>操作</b></td>';
echo '</tr><form method="GET" action="table.bks.php"><tr>';

// 列印插入欄
foreach($columns as $key) {
    echo '<td><input name="' . $key . '" value=""></td>';
}
// 按鈕
echo '<td><input type="submit" value="插入" style="width:40px">';
// 隱藏欄位: 表名和操作型別
echo '<input name="tableName" value="' . $tableName . '" style="display:none;">';
echo '<input name="opType" value="insert" style="display:none;">';
echo '</tr></form>';

// 列印資料
$res = mysqli_query($conn, "call show_" . $tableName);
$row = mysqli_num_rows($res);
// 遍歷每行
for($i=0; $i<$row; $i++) {
    echo '<form method="GET" action="table.bks.php"><tr>';
    $dbrow = mysqli_fetch_array($res);

    $url = "table.bks.php?tableName=" . $tableName . '&'; // 構造get提交的url

    // 按列回圈列印屬性
    for($j=0; $j<count($columns); $j++) {
        $key = $columns[$j];    // 屬性名
        $val = $dbrow[$key];    // 屬性值值
        echo '<td><input name="' . $key . '" value="' . $val . '"></td>';

        $url .= $key . '=' . $val . '&';
    }

    $url .= 'opType=delete';

    // 隱藏欄位: 表名和操作型別
    echo '<input name="tableName" value="' . $tableName . '" style="display:none;">';
    echo '<input name="opType" value="update" style="display:none;">';
    // 按鈕
    echo '<td><input type="submit" value="修改" style="width:40px"> ';
    echo '<a href="' . $url . '">洗掉</a></td>';
    echo '</tr></form>';
}
?>
</table>
<?php
if($row==0) {
    echo "表中沒有記錄";
}
?>
<hr></div>
<style>
    tr {
        text-align: center;
    }
    td {
        padding: 10px;
    }
    input {
        width: 130px;
    }
</style>

我們從 admin.php 點擊并且進入 tableInfo 頁面，可以看到如下的結果，我們一個頁面可以查詢任意表的資訊，這就是模塊化的好處：

在這里插入圖片描述

然后我們開始撰寫后臺 table.bks.php 的代碼，

我們首先從 tableInfo.php 中接收一條記錄的資訊（不管是什么操作，我們先接收該條記錄的資訊）和對應的操作型別，
我們根據提交的操作型別（增加洗掉或者修改），呼叫剛剛上面注冊的 insert_xxx 或者是 update_xxx 或者是 delete_xxx 存盤程序以完成對應的操作，

對于 sql 存盤程序的輸出，我們原封不動的回傳即可，因為我們注冊的所有 sql 介面具有相同的回傳欄位（st 和 msg）所以我們可以復用這段代碼到任何表，

下面來看 table.bks.php 的代碼：

<?php
include("conn.php");

if(!isset($_GET["tableName"])) {
    die("請選擇資料表");
}
$tableName = $_GET["tableName"];

// 獲取欄位名
$columns = array();
$res = mysqli_query($conn, "show columns from " . $tableName);
$row = mysqli_num_rows($res);
for($i=0; $i<$row; $i++) {
    $dbrow = mysqli_fetch_array($res);
    array_push($columns, $dbrow[0]);
}

// 獲取主鍵名稱
$sql = "SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='" . $tableName . "' AND constraint_name='PRIMARY'";
$res = mysqli_query($conn, $sql);
$primaryKeyName = mysqli_fetch_array($res)[0];

// 洗掉操作 -- 呼叫存盤程序 delete_xxxx() 
if($_GET["opType"]=="delete") {
    $sql = "call delete_" . $tableName;
}

// 更新操作 -- 呼叫存盤程序 update_xxxx() 
if($_GET["opType"]=="update") {
    $sql = "call update_" . $tableName;
}

// 插入操作 -- 呼叫存盤程序 insert_xxxx() 
if($_GET["opType"]=="insert") {
    $sql = "call insert_" . $tableName;  
}

// 構造 sql 陳述句
$sql = substr($sql, 0, -1); // 去掉 s 字符
$sql .= '(';

// 構造引數
$parameters = "";
foreach($columns as $key) {
    $parameters .= '"' . $_GET[$key] . '",';
}
$parameters = substr($parameters, 0, -1); // 去掉 , 字符

// 填引數
if($_GET["opType"]=="delete") {
    $sql .= $_GET[$primaryKeyName];   // 洗掉程序需要單個引數
} else {
    $sql .= $parameters;    // 所有引數
}
$sql .= ')';

// 執行操作
$res = mysqli_query($conn, $sql);
// 獲取 sql 介面的回傳資訊
$fetch_array = mysqli_fetch_array($res);
$st = $fetch_array[0];
$msg = $fetch_array[1];

// 回應
if($st==1) {
    $st = "成功";
} else {
    $st = "失敗";
}
echo '<script>alert("狀態: ' .$st . '\n資訊: ' . $msg . '");window.location.href=document.referrer;</script>';
?>

我們來測驗一下，我們修改 ShiYouLao 顧客的地址為 LiuZhou，我們點擊修改，可以看到后臺輸出的資訊，我們修改成功：
在這里插入圖片描述
回到頁面，我們發現資料修改成功：

在這里插入圖片描述
與此同時我們觀察 logs 表的記錄多了一條：

以插入資料為例，我們插入一條資料，但是這條資料（的主鍵）已經存在：

我們可以看到后臺的容錯邏輯開始作業了，我們拒絕了此次插入的請求：
在這里插入圖片描述
這次我們插入正常的記錄：

成功！并且我們的表中確實多了一條記錄，與此同時，logs 表也記錄了我們的操作：

然后我們嘗試洗掉該記錄：

我們洗掉成功，但是如果我們洗掉一條有外鍵約束的記錄呢？比如 Apple，有 products 表中的產品依賴于該供應商，
在這里插入圖片描述
可以看到洗掉失敗，我們注冊的 delete_xxx 程序很好的進行了容錯，并且拒絕了錯誤的操作

我們的日志也顯示，被拒絕掉的錯誤操作（洗掉 sid=7777 的記錄）并沒有實際發生，而合理的操作（洗掉 sid=5678 的記錄）被實際記錄下來了，
在這里插入圖片描述

撰寫下單模塊

下單模塊分為兩部分，shop.php 是前臺，而 shop.bks.php 是后臺，

其中前臺頁面查詢 products 表的資訊，并且列印商品詳情，此外，還查詢了 customers 和 employees 表的資訊，并且提供了下拉表，供用戶選擇員工和顧客，

值得注意的是，我們也是通過表單提交，將資訊傳到 shop.bks.php 并且實作對應的邏輯的，下面是 shop.php 的代碼：

<a href=index.html>回傳</a><hr>
<?php
include("conn.php");

// 獲取 customers 的資料
$customers = array();
$res = mysqli_query($conn, "select * from customers");
$row = mysqli_num_rows($res);
for($i=0; $i<$row; $i++) {
    $customers[$i] = mysqli_fetch_assoc($res);
}

// 獲取 employees 的資料
$employees = array();
$res = mysqli_query($conn, "select * from employees");
$row = mysqli_num_rows($res);
for($i=0; $i<$row; $i++) {
    $employees[$i] = mysqli_fetch_assoc($res);
}

// 列印資料
$res = mysqli_query($conn, "select * from products, suppliers where products.sid=suppliers.sid");
$row = mysqli_num_rows($res);
// 遍歷每行
for($i=0; $i<$row; $i++) {
    echo '<form method="GET" action="shop.bks.php"><div>';
    $product = mysqli_fetch_assoc($res);

    // 列印產品資訊
    $imgIndex = (int)($i%9);    // 圖片名稱索引
    echo '<img src="image/' . $imgIndex . '.jpg">';
    echo '<span class="pname">' . $product["pname"] . '</span> ';
    echo '<span class="sname_city">' . $product["sname"] . ' <br> ' . $product["city"] . '</span> ';
    echo '<span class="op"><s>' . $product["original_price"] . '</s></span> ';
    echo '<span class="cp">' . $product["original_price"] * $product["discnt_rate"]  . '</span> ';

    // 隱藏表單 -- 提交產品id(pid)
    echo '<input name="pid" value=' . $product["pid"] . ' style="display:none;">';

    // 選擇客戶串列
    echo '<span class="customers">選擇客戶:<br><select name="cid">';
    echo '<option value="-1">請選擇</option>';
    foreach($customers as $c) {
        echo '<option value="' . $c["cid"] . '">' . $c["cname"]. '</option>';
    }
    echo '</select></span>';

    // 選擇員工串列
    echo '<span class="employees">經手員工:<br><select name="eid">';
    echo '<option value="-1">請選擇</option>';
    foreach($employees as $e) {
        echo '<option value="' . $e["eid"] . '">' . $e["ename"]. '</option>';
    }
    echo '</select></span>';

    // 數量
    echo '<span class="qty">購買數量:<br><input name="qty" value="1">';
    echo ' / ' . $product["qoh"] . '</span>';

    // 購買按鈕
    echo '<input type="submit" value="下單">';

    echo '</div><hr></form>';
}
?>

<style>
    div {
        display: table-cell;
        height: 200px;
        vertical-align: middle;
        text-align: center
    }
    img {
        vertical-align: middle;
        width: 180px;
        #height: 180px;
    }
    .pname {
        display: inline-block;
        width: 200px;
        font-size: 32px;
        padding: 20px;
    }
    .sname_city {
        display: inline-block;
        width: 100px;
        border: 2px black solid;
        padding: 10px;
        border-radius: 20px;
    }
    .op {
        margin-left: 20px;
        display: inline-block;
        width: 50px;
        color: gray;
    }
    .cp {
        display: inline-block;
        width: 50px;
        color: red;
        font-size: 24px;
    }
    .customers {
        margin-left: 40px;
        #background-color: red;
        display: inline-block;
        width: 120px;
    }
    .employees {
        display: inline-block;
        width: 120px;
    }
    .qty {
        display: inline-block;
        width: 150px;
        
    }
    input {
        width: 60px;
        padding: 5px;
    }
    select {
        padding: 5px;
    }
</style>

~~這里需要用到億點點前端知識，，，~~

可以看到頁面的運行結果，其中所有的內容都是根據資料庫的資料動態生成的，此外，紅色的字體是原價 x 折扣之后的價格：
在這里插入圖片描述
我們撰寫 shop.bks.php 后臺，完成下單操作，

我們從前臺接收 cid, eid, pid, qty，然后呼叫注冊的 add_purchase 函式來完成下單的操作，下面是 shop.bks.php 的代碼：

<?php
include("conn.php");

$cid = $_GET["cid"];
$eid = $_GET["eid"];
$pid = $_GET["pid"];
$qty = $_GET["qty"];

$res = mysqli_query($conn, 'call add_purchase(' . $cid . ',' . $eid . ',' . $pid . ',' . $qty . ')');
$row = mysqli_num_rows($res);
// 獲取 sql 介面的回傳資訊
$fetch_array = mysqli_fetch_array($res);
$st = $fetch_array[0];
$msg = $fetch_array[1];

// 回應
if($st==1) {
    $st = "成功";
} else {
    $st = "失敗";
}
echo '<script>alert("狀態: ' .$st . '\n資訊: ' . $msg . '");window.location.href=document.referrer;</script>';
?>

我們嘗試進行下單操作：
在這里插入圖片描述
然后我們看到產品的數目減少了：

我們翻閱日志，確實發現多了如下的（三條）記錄：

首先我們插入了一條 purchases 記錄，該程序是 add_purchase 函式中完成的，

在這里插入圖片描述
然后可以看到 customers 和 products 表的資料被修改了，這是在 purchases 表的 insert 觸發器中完成的操作，

撰寫報表模塊

報表模塊 monthly_report.php 需要接收一個 pid 表示要查詢的產品的 id，

然后該頁面會呼叫我們注冊的 report_monthly_sale 函式，根據 pid 回傳對應的按月和年 group by 的報表，

下面是 monthly_report.php 的代碼：

<script type="text/javascript" src="lib_js/jscharts_mb.js"></script>
<div align="center">
<h2>查詢報表 -- 輸入產品id</h2>
<form method="GET">
<input name="pid"><input type="submit" value="查詢">
</form><a href=index.html>回傳</a><hr>
<?php
include("conn.php");

if(!isset($_GET["pid"]) || $_GET["pid"]=='') {
    die("請選擇產品");
} else {
    $pid = $_GET["pid"];
}

$data = array();  // 按年份分類存盤資料

// 查詢報表
$columns = array();
$res = mysqli_query($conn, 'call report_monthly_sale(' . $pid . ')');
$row = mysqli_num_rows($res);
for($i=0; $i<$row; $i++) {
    $dbrow = mysqli_fetch_array($res);
    $year = $dbrow[2];
    // 按照年份分組
    if(!isset($data[$year])) {
        $data[$year] = array(); // 不存在則新建
    }
    // fetch_array 轉為純 array 方便 JSON 序列化
    $arr = array();
    for($j=0; $j<6; $j++) {
        $arr[$j] = $dbrow[$j];
    }
    array_push($data[$year], $arr);  // 存在則加入
}

// 判斷是否查詢為空
if(count($data)==0) {
    die('<script>alert("沒有資料!");</script>');
}

// 將資料通過JSON傳遞給JavaScript
echo "<script>var data=JSON.parse('" . json_encode($data) . "');</script>";

// debug
// echo "console.log(data);</script>"; echo 'json_encode($data)';

// 分年份列印表
foreach($data as $year => $yearTable) {
    // 列印表頭
    echo '<h3>' . $year . '年報表</h3><table border="2"><tr><td>產品名稱</td><td>月份</td><td>年份</td><td>銷量</td><td>總價</td><td>均價</td></tr>';
    // 列印行資料
    foreach($yearTable as $row) {
        echo '<tr>';
        // 列印列
        for($j=0; $j<6; $j++) {
            echo '<td>' . $row[$j] . '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
    echo '<div id="' . $year . '1" style="display:inline-block;"></div>';  // 列印表格容器
    echo '<div id="' . $year . '2" style="display:inline-block;"></div><hr>';  
}
?>
</div>

<script type="text/javascript">
    // 月份映射與顏色映射
    var mmap = {"Jan":1, "Feb":2, "Mar":3, "Apr":4, "May":5, "Jun":6, "Jul":7, "Aug":8, "Sep":9, "Oct":10, "Nov":11, "Dec":12};
    var cmap = {"Jan":"#FFFF00", "Feb":"#FFC125", "Mar":"#FF83FA", "Apr":"#FF4500   ", "May":"#FFB90F", "Jun":"#BF3EFF", "Jul":"#98FB98", "Aug":"#7FFF00", "Sep":"#8B8378", "Oct":"#76EE00", "Nov":"#1E90FF", "Dec":"#00CDCD"};
    // 遍歷每年的資料 -- 每年的資料為一張表
    for(var year in data) {
        var chartData = new Array();
        var chartColor = new Array();
        var rows = data[year];
        //console.log(rows);    // debug
        for(var i=0; i<rows.length; i++) {
            var row = rows[i];  // 每一行資料
            //console.log(row); // debug
            var point = [row[1], parseFloat(row[4])];   // 插入 [月份, 總額]
            chartData.push(point);
            chartColor.push(cmap[row[1]]);
        }
        //console.log(chartData);   // debug
        var chart1 = new JSChart(year.toString()+'1', 'bar');
        chart1.setDataArray(chartData);
        chart1.colorizeBars(chartColor);
        chart1.draw();
        var chart2 = new JSChart(year.toString()+'2', 'pie');
        chart2.setDataArray(chartData);
        chart2.colorizePie(chartColor);
        chart2.draw();
    }
</script>

<style>
    tr {
        text-align: center;
    }
    td {
        padding: 10px;
    }
</style>

我們使用 jscharts 庫來繪制圖表，其中庫檔案存放于 lib_js 目錄下，

這里我們用了一個技巧向 JavaScript 代碼發送 php 獲取到的資料，那就是 JSON，

我們在 php 端先獲取資料，然后通過 JSON 編碼，將 php 資料以字串的形式傳到 JavaScript 代碼中，然后 JavaScript 進行解碼，獲得查詢的資料，并且利用 jscharts 庫進行報表的繪制，

我們進入頁面，此時還未選擇產品
在這里插入圖片描述
我們以 443 號產品為例，我們手動造假一些資料（因為要制造 2019 年的資料），包含 2019 和 2020 年的資料，其中 2019 年有兩個月的記錄，2020 年有三個月的記錄：

主頁

這部分的代碼相當無聊：

<!DOCTYPE html>
<html>
<head>
	<title></title>
</head>
<body align="center">
    <h3>銷售管理系統</h3><hr>
    <a href="monthly_report.php"><div class="item">報表</div></a><br>
    <a href="admin.php"><div class="item">資料管理</div></a><br>
    <a href="shop.php"><div class="item">下單</div></a><br>
</body>
</html>

<style>
.item {
    text-align: center;
    border: 2px black solid;
    padding: 10px;
    border-radius: 20px;
    width: 30%;
    margin-left: 35%;
}
</style>

在這里插入圖片描述

快速部署

因為注冊這么多的 sql 腳本真是太畝訓了，于是我匯出了所有 sql 腳本，按順序建立資料表，并且注冊的函式與程序，所有的 sql 代碼都在 dump.txt 中了，在提交的代碼中，可以找到該 txt 檔案，

只需要運行命令 mysql -u root -p < dump.txt 即可完成快速部署！
在這里插入圖片描述

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/232066.html

標籤：其他

上一篇：如果你在準備金三銀四，不妨來看看我今年阿里Java面試的經驗

下一篇：定階阿里P7，搞透這份高級程式員Java面試1000題決議就夠了

深大資料庫實驗3之銷售管理系統 -- php與mysql之后臺撰寫與介面

目錄

前言

要求

專案結構規劃

資料庫創建與資料準備

撰寫 show_xxxx 程序以全表列印

撰寫 report_monthly_sale 函式以列印報表

撰寫 add_purchase 函式以實作添加一筆購買記錄

為 purchases 表的 insert 操作注冊觸發器以實作業務邏輯

注冊觸發器以記錄 logs

為增刪改操作撰寫 sql 介面

撰寫資料管理模塊

撰寫下單模塊

撰寫報表模塊

主頁

快速部署