<?php
/**
 * Created by PhpStorm.
 * User: 李化云
 * Date: 2021/7/4
 * Time: 18:11
 */


class  Mysql
{

    var $servername = "localhost";
    var $username = "www_bilihuayun_com";
    var $password = "www_bilihuayun_com";
    var $database = "www_bilihuayun_com";
    var $conn = null;

    static private $instance;

    //防止使用new直接创建对象
    private function __construct()
    {
    }

    //防止使用clone克隆对象
    private function __clone()
    {
    }

    public function __destruct()
    {
        // TODO: Implement __destruct() method.
        $this->conn->close();

    }
    //单例模式
    static public function getInstance()
    {
        //判断$instance是否是Singleton的对象，不是则创建
        if (!self::$instance instanceof self) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    //数据库连接
    public function mysql()
    {
        $this->conn = new mysqli($this->servername, $this->username, $this->password, $this->database);

// 检测连接
        if ($this->conn->connect_error) {
            $this->conn = null;
            die("连接失败: " . $this->conn->connect_error);
        }

    }
    //显示内容
    public function show()
    {
        $num_rec_per_page=20;
        if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };
        $start_from = ($page-1) * $num_rec_per_page;
   //     $sql = "SELECT * FROM student LIMIT $start_from, $num_rec_per_page";



        $sql = "SELECT * FROM `student`ORDER BY `student`.`id` ASC LIMIT $start_from, $num_rec_per_page  ";

        $result = $this->conn->query($sql);

        $total_records =$this->conn->query("SELECT id FROM `student`")->num_rows;//总条数

        $total_pages = ceil($total_records / $num_rec_per_page);  // 计算总页数


        if ($result->num_rows > 0) {
            // 输出数据


            echo "<table class='table'>
 
              
                

                <thead>
                <tr>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>性别</th>
                    <th>年龄</th>
                    <th>学科</th>
                    <th>创建时间</th>
                    <th>操作</th>
                </tr>
                </thead>
                <tbody>";


            while ($row = $result->fetch_assoc()) {
                $index=1;

                switch ($row["st_sex"] ){
                    case 0:
                        $sex='未知';
                        break;
                    case 1:
                        $sex='男';
                        break;
                    default:
                        $sex='女';
                }

                $sql1="SELECT subject FROM `subject` WHERE `subject_id` =".$row["st_subject"];
                $res= $this->conn->query($sql1) ;
                $sub='--';
                if ($res->num_rows > 0) {


                    while ($rows = $res->fetch_assoc()) {
                        $sub= $rows['subject'] ;
                    }
                }


                $uid=$row['id'];
                $uname=$row["st_name"];
                $unamecode=json_encode( $row["st_name"]);
                $usex=array(
                    'uid'=>$uid,
                    'uname'=>$uname,
                    'usex'=>$sex,
                    'usexv'=>$row["st_sex"],

                    'uage'=>$row['st_age'],
                    'usub'=>$sub,
                    'usubv'=>$row["st_subject"]
            );$uencode=json_encode($usex);

                echo "<tr>";
                echo "<td>".$uid."</td> <td> " . $uname . "</td><td> " . $sex . "</td><td>" . $row["st_age"] . "</td><td>" . $sub . "</td><td>" . date("Y-m-d H:i", $row['st_createtime']) . "</td><td> <a href='javascript:void(0);'  data-toggle='modal' data-target='#delete' class='btn btn-danger  glyphicon glyphicon-trash' onclick='deleteuser($uid,$unamecode)'> </a><a href='javascript:void(0);' style='margin-left: 4px' class='btn btn-warning   glyphicon glyphicon-edit' data-target='#edit'    data-toggle='modal'    onclick='edituser($uencode)'> </a></td>";
                echo "</tr>";

            }

            if($_GET['page']<=1){
                $_GET['page']=2;
            }
            if($_GET['page']>=$total_pages){
                $_GET['page']=$total_pages-1;
            }


            echo "</tbody></table>";

             
            echo " <div><ul class='pagination'>
 
                        <li><a href='guanli.php?a=show&page=".($_GET['page']-1)."'>&laquo;</a></li>";
                        for ($i=1; $i<=$total_pages; $i++) {

                            echo "<li><a href='guanli.php?a=show&page=".$i."'>".$i."</a></li>";
                        };



            echo "  <li><a href='guanli.php?a=show&page=".($_GET['page']+1)."'>&raquo;</a></li>
                    </ul></div> ";





        } else {
            echo " <p style='color: #bb3122;'> 0 结果</p>";
        }

    }

    public function showSubject()
    {
        $sql = "SELECT * FROM `subject`";

        $result = $this->conn->query($sql);


        if ($result->num_rows > 0) {
            // 输出数据
            while ($row = $result->fetch_assoc()) {

                echo " <option class='form-control' value='" . $row["subject_id"] . "'> " . $row["subject"] . "</option>";

            }


        } else {
            echo " <div> 0 结果</div> ";
        }


    }
    //增加
    public function add($st_name,$st_sex, $st_age,$st_subject)
    {

        // $sql = "INSERT INTO `student` (`id`, `name`, `sex`, `age`, `subject`, `createtime`) VALUES (NULL, '李333云', '3', '2', '3', time())";




        $st_createtime = time();
        $stmt = $this->conn->prepare("INSERT INTO student (st_name,st_sex,st_age,st_subject,st_createtime) VALUES (?,?,?,?,?)");
        $stmt->bind_param("siiii", $st_name, $st_sex, $st_age, $st_subject, $st_createtime);


        $stmt->execute();
        $stmt->close();

        header("Refresh:0.6;url=/guanli.php?a=show");

    }

    //删除
    public function delete($uid){
        $sql="DELETE FROM `student` WHERE `student`.`id` =".$uid;
        $this->conn->query($sql);
    }

    //修改
    public function edit($a,$b,$c,$d,$e){
        echo $a;
        echo "<br/>";
        echo $b; echo "<br/>";
        echo $c; echo "<br/>";
        echo $d; echo "<br/>";
        echo $e;
    //  $sql=" UPDATE `student` SET `st_name` = '修改3', `st_sex` = '2', `st_age` = '233', `st_subject` = '2' WHERE `student`.`id` = 132;";

      //  $stmt = $this->conn->prepare("INSERT INTO student (st_name,st_sex,st_age,st_subject,st_createtime) VALUES (?,?,?,?,?)");


        $stmt = $this->conn->prepare("UPDATE `student` SET `st_name` = ?, `st_sex` =?, `st_age` = ?, `st_subject` =?  WHERE `student`.`id` = ?;");

        $stmt->bind_param( "siiii",$a,$b,$c,$d,$e);
        $stmt->execute();
        $stmt->close();

  }

    //admin登录操作
    public function login($admin, $password)
    {


        $sql = "SELECT * FROM `admin` WHERE `admin` = '" . $admin . "' AND `password` = '" . $password . "'";

        $result = $this->conn->query($sql);

        if ($result->num_rows > 0) {
            session_start();
            $_SESSION['admin_login'] = true;
            $_SESSION['admin_name'] = $admin;
            echo "<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <title>登录成功</title>
    ";


            require_once('css.html');
            echo "
</head>
<body>
<h4 style='margin-top: 10%;margin-left: 10%;color: #00bb00'>成功登录,3秒后跳到首页</h4>

</body>
</html>";
            header("Refresh:3;url=index.php");
        } else {
            $_SESSION['admin_login'] = false;
            echo "<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <title>登录失败</title>
    ";


            require_once('css.html');
            echo "
</head>
<body>
<h4 style='margin-top: 10%;margin-left: 10%;color: #bb3122'>账号或密码错误,3秒后跳到首页</h4>

</body>
</html>";
            header("Refresh:3;url=index.php");
        }
    }

    //退出
    public function logout()
    {
        session_start();

        session_destroy();


        echo "<!DOCTYPE html>
            <html lang='en'>
            <head>
                <meta charset='UTF-8'>
                <title>退出成功，正在跳转</title>
                ";


        require_once('css.html');
        echo "
            </head>
            <body>
            <h4 style='margin-top: 10%;margin-left: 10%;color: #00bb00'>退出成功,3秒后跳到首页</h4>
            
            </body>
            </html>";


        header("Refresh:2;url=index.php");
    }

    public function action($a)
    {
        switch ($a) {
            case 'add':
                $this->add($_POST['s_name'],$_POST['s_sex'],$_POST['s_age'],$_POST['s_sub']);
                break;
            case 'login':
                $this->login($_POST['admin'], $_POST['password']);
                break;
            case 'logout':
                $this->logout();
                break;
            case 'delete':
                $this->delete($_POST['uid']);
                break;
            case 'edit':
                $this->edit($_POST['a'],$_POST['b'],$_POST['c'],$_POST['d'],$_POST['e']);
                break;
            case 'show':
                $this->show($_POST['page']);
                break;
            default;
        }
    }
}

$sql = Mysql::getInstance();
$sql->mysql();
$sql->action($_GET['a']);




