zeerd's blog         Search     Categories     Tags     Feed

闲来生雅趣,无事乐逍遥。对窗相望雪,一盏茶香飘。

基于PHP和Sqlite3的简易设备预订系统

#PHP #SQL #Sqlite @Website


Contents:

首先是要准备一个数据库,参照下面的sql语句创建:

book.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "sets" (
    "set_id" INTEGER PRIMARY KEY NOT NULL DEFAULT (0),
    "name" TEXT NOT NULL DEFAULT ('""')
);
INSERT INTO "sets" VALUES(1,'Equip1');
INSERT INTO "sets" VALUES(2,'Equip2');
INSERT INTO "sets" VALUES(3,'Equip3');
INSERT INTO "sets" VALUES(4,'Equip4');
CREATE TABLE "users" (
    "ip" TEXT NOT NULL DEFAULT ('127.0.0.1'),
    "name" TEXT NOT NULL
);
CREATE TABLE "records" (
    "book_user" TEXT NOT NULL,
    "book_date" TEXT NOT NULL,
    "book_time" TEXT NOT NULL,
    "set_name" TEXT NOT NULL
);
CREATE TABLE "times" (
    "time_section" TEXT NOT NULL,
    "id" INTEGER PRIMARY KEY NOT NULL
);
INSERT INTO "times" VALUES('9:00 - 12:00',1);
INSERT INTO "times" VALUES('13:00 - 18:00',2);
CREATE VIEW "record_all" AS select records.*, times.id as book_time_id from records left join times on times.time_section=records.book_time;
CREATE UNIQUE INDEX unique_index_ip_in_users on users (ip ASC);
COMMIT;

使用方法:

install -d db
sqlite3 db/equip_booking.db < book.sql

其次,需要一个页面用来显示设备的列表及预订信息。 在首次使用这个系统的时候,会先要求用户输入一个名字并绑定到客户IP上。

index.html

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />

<style type="text/css">
form {
 margin-bottom: 0px;
}
</style>
</head>
<body>

<?php

$db = new SQLite3('db/equip_booking.db', 6);


$weekday = array("日", "一", "二", "三", "四", "五", "六");
$time = array();
$equip = array();

$sql = "select name from users where ip='".get_client_ip()."'";
$result = $db->query($sql);
$row = $result->fetchArray();
$user_name=$row['name'];

$result = $db->query("select time_section from times");
$i=0;
while ($row = $result->fetchArray())
{
    $time[$i] = $row['time_section'];
    $i = $i + 1;
}

$result = $db->query("select set_id, name from sets");
$i=0;
while ($row = $result->fetchArray())
{
    $equip[$i] = $row['name'];
    $i = $i + 1;
}

function get_client_ip()
{
    if ($_SERVER['REMOTE_ADDR']) {
        $cip = $_SERVER['REMOTE_ADDR'];
    } elseif (getenv("REMOTE_ADDR")) {
        $cip = getenv("REMOTE_ADDR");
    } elseif (getenv("HTTP_CLIENT_IP")) {
        $cip = getenv("HTTP_CLIENT_IP");
    } else {
        $cip = "unknown";
    }
    return $cip;
}
?>


<?php

if($user_name == ""){
    echo "<form action=\"user.php\" method=\"post\">";
        echo "你的IP地址是:";
        echo "<input type=\"text\" name=\"user_ip\" readonly=true value=\"".get_client_ip()."\"/>";
        echo "<br/>";
        echo "请输入你的真实姓名:";
        echo "<input type=\"text\" name=\"user_name\" />";
        echo "<input type=\"submit\" name=\"submit\" value=\"提交\" />";
    echo "</form>";
}
else{
    echo "<form action=\"user.php\" method=\"post\">";
        echo "你好,".$user_name."!";
        echo "<input type=\"hidden\" name=\"user_ip\" readonly=true value=\"".get_client_ip()."\"/>";
        echo "(实际上,我是";
        echo "<input type=\"text\" name=\"user_name\" />";
        echo "<input type=\"submit\" name=\"submit\" value=\"更名\" />";
        echo ")";
    echo "</form>";
    echo "<hr/>";

    echo "<table border=1>";
    echo "<tr>";
    echo "<th></th>";

    $equip_count = 0;
    foreach ($equip as $value){
        echo "<th>". $value ."</th>";
        $equip_count++;
    }

    echo "<th>日期</th>";
    echo "</tr>";

    $day_max = 3;
    if(date("w") >= 4){
        $day_max += 2;
        }
    for($i=0;$i<$day_max;$i++){
        $data = mktime(0,0,0,date("m"),date("d")+$i,date("Y"));
        foreach ($time as $time_value){
            echo "<tr>";
            echo "<td>".$time_value."</td>";
            foreach ($equip as $value){
                echo "<td>";
                $sql = "select book_user from records ".
                    "where set_name='".$value."' and book_date='".date("Y-m-d", $data)."' and book_time='".$time_value."'";
                $result = $db->query($sql);
                $row = $result->fetchArray();
                $book_user = $row['book_user'];
                if($book_user == ""){
                    echo "<form action=\"book.php\" method=\"post\">";
                    echo "<input type=\"hidden\" name=\"user\" value=\"".$user_name."\"/>";
                    echo "<input type=\"hidden\" name=\"data\" value=\"".date("Y-m-d", $data)."\"/>";
                    echo "<input type=\"hidden\" name=\"time\" value=\"".$time_value."\"/>";
                    echo "<input type=\"hidden\" name=\"equip\" value=\"".$value."\"/>";
                    echo "<input type=\"submit\" name=\"submit\" value=\"预定\" />";
                    echo "</form>";
                }
                else if($book_user == $user_name){
                    echo $book_user."(<a href=\"cancel.php?user=".$user_name."&data=".date("Y-m-d", $data)."&time=".$time_value."&equip=".$value."\">取消</a>)";
                }
                else{
                    echo "<b>".$book_user."</b>";
                }
                echo "</td>";
            }
            echo "<td>". date("Y-m-d", $data) ."(".$weekday[date("w", $data)].")</td>";
            echo "</tr>";
        }

        for($ec=0;$ec<$equip_count+2;$ec++){
            echo "<td><hr></td>";
        }
    }
    echo "</table>";

}
?>


</body>
</html>

接下来这个页面用来进行用户的绑定:

user.php

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
</head>
<body>
<?php

$db = new SQLite3('db/equip_booking.db', 6);

$sql = "replace into users values('".$_POST['user_ip']."', '"
                                    .$_POST['user_name']."')";
//echo $sql;
$db->query($sql);

echo "<a href=\"index.php\">返回</a>";

?>
</body>
</html>


接下来这个页面用于进行设备预订:
book.php


<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
</head>
<body>
<?php

$booked_sets = array();
$booked_times = array();

$db = new SQLite3('db/equip_booking.db', 6);

$user_in = $_POST['user'];
$data_in = $_POST['data'];
$time_in = $_POST['time'];
$equip_in = $_POST['equip'];

$db->query("begin transaction");

$sql = "select book_time_id from record_all where book_time='".$time_in."'";
$result = $db->query($sql);
$row = $result->fetchArray();
$book_time_id = (int)$row['book_time_id'];



if($book_time_id > 1){
    $sql = "select set_name, book_time from record_all ";
    $sql = $sql."where book_date='".$data_in."' and book_user='".$user_in."' ";
    $sql = $sql."    and (book_time_id=".($book_time_id+1)
                    ." or book_time_id=".($book_time_id-1).")";
    //echo $sql."<br/>";
    $result = $db->query($sql);
    $book_user_count = 0;
    while ($row = $result->fetchArray())
    {
        $booked_sets[$book_user_count] = $row['set_name'];
        $booked_times[$book_user_count] = $row['book_time'];
        $book_user_count++;
    }
}
else{
    $sql = "select set_name, book_time from record_all ";
    $sql = $sql."where book_date='".$data_in."' and book_user='".$user_in."' ";
    $sql = $sql."    and (book_time_id=".($book_time_id+1).")";
    //echo $sql."<br/>";
    $result = $db->query($sql);
    $book_user_count = 0;
    while ($row = $result->fetchArray())
    {
        $booked_sets[$book_user_count] = $row['set_name'];
        $booked_times[$book_user_count] = $row['book_time'];
        $book_user_count++;
    }
}

if($book_user_count != 0){
    echo "<a href=\"index.php\">你已经预订了";
    for($i=0;$i<$book_user_count;$i++){
        echo "[“".$booked_times[$i]."”的“".$booked_sets[$i]."”]";
    }
    echo ",请不要连续预定!</a>";
}
else{
    $sql = "select book_user from records where book_date='".$data_in
                                           ."' and book_time='".$time_in
                                           ."' and set_name='".$equip_in."'";
    $result = $db->query($sql);
    $row = $result->fetchArray();
    $book_user = $row['book_user'];

    if($book_user == ""){
        $sql = "insert into records values('".$user_in."', '"
                                             .$data_in."', '"
                                             .$time_in."', '"
                                             .$equip_in."')";
        $db->query($sql);
    }

    $db->query("commit transaction");

    if($book_user == ""){
        echo "<a href=\"index.php\">返回</a>";
    }
    else{
        echo "<a href=\"index.php\">晚了一步。已经被".$book_user."预订了。</a>";
    }
}

?>
</body>
</html>

最后这页面实在不需要的时候取消预定的:

cancel.php

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
</head>
<body>
<?php

$db = new SQLite3('db/equip_booking.db', 6);

$sql = "delete from records where book_user='".$_GET['user']
     ."' and book_date='".$_GET['data']
     ."' and book_time='".$_GET['time']
     ."' and set_name='".$_GET['equip']."'";
//echo $sql;
$db->query($sql);

echo "<a href=\"index.php\">返回</a>";
?>
</body>
</html>

运行环境:

apt install nginx-light php-fpm php-sqlite3