<file_sep>页面逻辑：

* 初始界面为index.php，跳转到activityList.html，显示活动列表

  * 活动列表列出网站上所有活动，每个活动可进入查看界面查看详细信息

    viewActivity.php -> viewActivityInfo.html

    * 若用户不是此活动的参加者，可在查看界面中申请加入此活动

      （申请应由活动管理员或者超级管理员审核，未实现）

* 注册/登陆 register/login.php -> register/login.html，结束后跳转到活动列表

* 创建活动 addActivity.php/html，创建结束后跳转到活动列表

* 活动管理 manageActivity.php/html
  * 活动管理界面只显示当前登陆用户所参加的活动
  * 若用户为活动的参加者，可以选择查看活动信息或者退出活动
  * 若用户为活动的管理员，还可以选择删除活动

* 若当前登陆用户为超级管理员，活动管理界面右上方有一个管理员入口 manageActivityManager.php/html
  * 管理员界面列出网站上所有活动，每个活动有管理选项 
  * 点击管理选项后进入有边栏的页面，默认进入第一个，infoManager.php/html
  * infoManager页面对活动信息作出修改（未实现）
  * 可通过边栏跳转到用户管理界面 attendentManager.php/html
    * 用户管理界面列出所有用户和他们的基本信息，每个用户有三种状态：未参加，参加者，管理员
      * 未参加者可以被添加为参加者或管理员
      * 参加者可以被添加为管理员或退出活动
      * 管理员可以被降为普通用户或退出活动
      
* 签到：当前活动开始时（begin_time）开放签到入口
  * 在user_activity表中，加一列signin表示是否签到成功，初始化为0表示未签到，1表示已签到
  * 在活动主页面上，首先检查该用户是否signin，如果signin在页面上（比如右上角）显示“签到成功！”，否则放一个二维码，二维码是网址 http://我们的IP/signin.php?activities_id=xxx 进行的编码。如果用户扫这个二维码，会跳转到这一个页面吧
  * 在signin.php中，根据SESSION可以获得user_id，根据GET可以获得activity_id，然后调用签到函数（user_activity.php中）
  * 在user_activity.php中，加一个函数表示签到，参数是据user_id和activity_id，可以找到这条记录并修改该记录的signin值为1
  * 最后header跳转到活动主页面，此时主页面右上角应该显示的是“签到成功！”
<file_sep><?php
	include_once("../../../lib/common.php");
    include_once("../../../lib/activity.php");
    session_start();

    $param = $_POST;
    $param['uid'] = $_SESSION['uid'];
    $ret = addActivities($param);

    if ($ret['status'] === 0) {
        //print("succeed");
        header("Location: ../../index.php");
        exit();
    }
    else {
        //header("Location: ../../addActivity.php");
        echo "<script>alert('已有相同的会议名称');</script>";
        echo "<script>location.href='../../addActivity.php'</script>";
        exit();
    }
?>
<file_sep><?php 
require_once("vendor/autoload.php"); 

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$objSpreadsheet = new Spreadsheet();
$sheet = $objSpreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '晨蕾');

$writer = new Xlsx($objSpreadsheet);
$writer->save('export.xlsx');
?>
<file_sep><?php
    include_once("../lib/common.php");
    $smarty3->display("./html/register.html");
?>
<file_sep><?php
	include_once("../../../lib/common.php");
	include_once("../../../lib/activity.php");
	session_start();

	$activity_id = intval($_GET['activity_id']);
	# whether to show the "back_to_info_change" button
	$from_info_change = intval($_GET['from_info_change']);
	$activity = getActivities(array("id" => $activity_id));
	$list = $activity['list'];
	$smarty3->assign('list', $list);
	$smarty3->assign('from_info_change', $from_info_change);

	if(!empty($_SESSION['username']))
	{
		$smarty3->assign('login', true);
		//$smarty3->assign('uid', $uid);
		$uid = $_SESSION['uid'];
		$smarty3->assign('user_id', $uid);
		if (checkActivityAttendent($uid, $activity_id) === 1)
			{$smarty3->assign('attendence', true);}
		else{$smarty3->assign('attendence', false);}
	}
	else{$smarty3->assign('login', false);}

	$smarty3->display('../../html/viewActivityInfo.html');
?>
<file_sep><?php
	include_once("../../../lib/common.php");
    include_once("../../../lib/activity.php");
    session_start();

    $param = $_POST;
    $activity_id = $param['activity_id'];
    $ret = changeActivityInfo($param);

    if ($ret['status'] === 0) {
        header("Location: ./viewActivity.php?activity_id=". $activity_id . "&from_info_change=1");
        exit();
    }
    else {
        header("Location: ../infoManager.php?activity_id=". $activity_id);
        exit();
    }
?><file_sep><?php
    include_once("../../lib/common.php");
    include_once("../../lib/activity.php");

    $param = empty($_GET) ? $_POST : $_GET;
    $activityname = $param['activityname'];
    if (checkActivitynameUsed($activityname))
        echo "已存在同名活动";
    else
        echo "";
?><file_sep><?php
    include_once(__DIR__ . "/config.php");
    // smarty3
    include_once(WEB_ROOT . "../third_party/smarty3/main.php");
?>
<file_sep><?php
    include_once("common.php");
    include_once("user_activity.php");

    /**
     *  get activities which meet conditions
     * @param filter: array
     * @return array
     */
    function getActivities($filter=array()) {
        $select = "select * from activities ";
        $where = "where true ";
        if (!empty($filter['id'])) {
            $id = $filter['id'];
            $where = $where . "and id = $id ";
        }
        if (!empty($filter['name'])) {
            $name = $filter['name'];
            $where = $where . "and name = '$name' ";
        }
        if (!empty($filter['abbr'])) {
            $abbr = $filter['abbr'];
            $where = $where . "and abbr = '$abbr' ";
        }
        if (!empty($filter['begin_date'])) {
            $beginDate = $filter['begin_date'];
            $where = $where . "and begin_date >= $beginDate ";
        }
        if (!empty($filter['end_date'])) {
            $endDate = $filter['end_date'];
            $where = $where . "and end_date <= $endDate ";
        }
        $order = "order by id desc";
        $sql = $select . $where . $order;
        
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        $ret = array(
            "num" => $result->num_rows,
            "list" => $result->fetch_all(MYSQLI_ASSOC)
        );
        $conn->close();
        return $ret;
    }

    /**
    * add a new activity
    * @param array consist of info
    * @return mixed
    */

    function addActivities($params){
        $ret = array(
        'status' => 1,
        'err_msg' => ''
        );

        $name = $params['name'];
        $abbr = isset($params['abbr']) ? $params['abbr'] : '';
        $begin_date = isset($params['begin_date']) ? $params['begin_date'] : '';
        $end_date = isset($params['end_date']) ? $params['end_date'] : '';

        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        $sql = $conn->prepare("insert into activities (name, abbr, begin_date, end_date) values (?, ?, ?, ?)");
        if ($sql === false) {
            echo($conn->error);
            exit();
            }
        $begin_date_pass = date('Y-m-d H:i:s',strtotime($begin_date));
        $end_date_pass = date('Y-m-d H:i:s',strtotime($end_date));
        $sql->bind_param("ssss", $name, $abbr, $begin_date_pass, $end_date_pass);
        
        $result = $sql->execute();
        if ($result === true) {
            $activityId = $conn->insert_id;
            $userId = $params['uid'];
            $sql = "insert into user_activity (user_id, activity_id, role) values ($userId, $activityId, 1)";
            $result = $conn->query($sql);
            if ($result === false) {
                $ret['err_msg'] = $conn->error;
            } else {
                $ret['status'] = 0;
            }
        }
        else {
            $ret['err_msg'] = $conn->error;
            //echo($conn->error);
            //ret();
        }
        $conn->close();
        return $ret;
    }

    /**
     * delete an activity by id
     * @param $id
     * @return mixed
     */
    function deleteActivity($id) {
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );

        $delete_ret = deleteRecordById($id);
        if ($delete_ret['status'] === 1) {
            $ret['err_msg'] = $delete_ret['err_msg'];
        } else {
            $sql = "delete from activities where id = $id";
            $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
            if ($conn->connect_error) {
                die($conn->connect_error);
            }
            $result = $conn->query($sql);
            if ($result === false) {
                $ret['err_msg'] = $conn->error;
            } else {
                $ret['status'] = 0;
            }
        }
        $conn->close();
        return $ret;
    }

    /**
     * change the activity information according to params
     * @param $params
     * @return mixed
     */
    function changeActivityInfo($params){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        $activity_id = $params['activity_id'];
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "update activities set";
        $name = $params['name'];
        $abbr = $params['abbr'];
        $begin_date = $params['begin_date'];
        $end_date = $params['end_date'];
        $description = $params['description'];
        $website_address = $params['website_address'];
        if (!empty($name))
        {
            $sql = $sql . " name = '$name',";
        }
        if (!empty($abbr))
        {
            $sql = $sql . " abbr = '$abbr',";
        }
        if (!empty($begin_date))
        {
            $sql = $sql . " begin_date = '$begin_date',";
        }
        if (!empty($begin_date))
        {
            $sql = $sql . " end_date = '$end_date',";
        }
        if (!empty($description))
        {
            $sql = $sql . " description = '$description',";
        }
        if (!empty($website_address))
        {
            $sql = $sql . " website_address = '$website_address',";
        }

        $sql = substr($sql, 0, strlen($sql)-1);
        $sql = $sql . " where id = $activity_id";
        #echo($sql);
        #exit();
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * check whether the activityname is occupied
     * @param activityname
     * @return true: occupied
     * @return false: not occupied
     */
    function checkActivitynameUsed($activityname) {
        $ret = getActivities(array("name"=>$activityname));
        if ($ret['num'] === 0) {
            return false;
        } else {
            return true;
        }
    }

?><file_sep><?php
include_once("common.php");

/**
 * get user's infomation by his/her username
 * @param username
 * @return array|null
 */
function getUserByUsername($username) {
    if (!isset($username)) {
        return null;
    }

    $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
    $sql = $conn->prepare("select * from users where username = ?");
    $sql->bind_param("s", $username);
    $sql->execute();
    $result = $sql->get_result();
   
    $info = null;
    if ($result->num_rows > 0) {
        $info = $result->fetch_assoc();
    }
    $conn->close();
    return $info; 
}

/**
 * check whether the username is occupied
 * @param username
 * @return true: username is occupied
 *         false: username has not been occupied
 */
function checkUsernameUsed($username) {
    $usr = getUserByUsername($username);
    if (is_null($usr)) {
        return false;
    }
    else {
        return true;
    } 
}

/**
 * check whether the user name and password consistent
 * @param username and password
 * @return status=1 if there is something wrong
 *         status=0 name and password consistent
 */
function checkUser($username, $password=null) {
    $ret = array(
        'status' => 1,
        'err_msg' => '',
        'uid' => -1,
        'role' => 0
    );
    // username can not be empty
    if (!isset($username)) {
        $ret['err_msg'] = 'empty_username';
        return $ret;
    }

    // username does not exist
    $userInfo = getUserByUsername($username);
    if ($userInfo === null) {
        $ret['err_msg'] = 'false_username';
        return $ret;
    }

    // wrong password
    if ($password !== $userInfo['password']) {
        $ret['err_msg'] = 'false_password';
        return $ret;
    }
    $ret['status'] = 0;
    $ret['uid'] = $userInfo['id'];
    $ret['role'] = $userInfo['role'];
    return $ret;
}

/**
 * register a new user 
 * @param array consist of info
 * @return mixed
 */
function addUser($params) {
    $ret = array(
        'status' => 1,
        'err_msg' => '',
        'uid' => -1
    );

    $username = $params['username'];
    $password = $params['<PASSWORD>'];
    $email = isset($params['email']) ? $params['email'] : '';

    $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
    $sql = $conn->prepare("insert into users (username, password, email) values (?, ?, ?)");
    $sql->bind_param("sss", $username, $password, $email);
    $result = $sql->execute();
    if ($result === true) {
        $ret['status'] = 0;
        $ret['uid'] = $conn->insert_id;
    }
    else {
        $ret['err_msg'] = $conn->error;
    }
    $conn->close();
    return $ret;
}


?><file_sep>CREATE DATABASE IF NOT EXISTS webdb;

USE webdb;

CREATE TABLE IF NOT EXISTS users (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  username varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  password varchar(30) COLLATE utf8mb4_general_ci NOT NULL,
  email varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  role int(10) DEFAULT 0 NOT NULL,
  extra_json varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE (username)
);

set sql_mode='ALLOW_INVALID_DATES';

CREATE TABLE IF NOT EXISTS activities (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  abbr varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
  begin_date timestamp NOT NULL,
  end_date timestamp NOT NULL,
  description text DEFAULT NULL,
  website_address varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

ALTER TABLE activities ADD COLUMN add_member int(10) DEFAULT 0 NOT NULL;

CREATE TABLE IF NOT EXISTS user_activity (
  user_id int(10) unsigned NOT NULL,
  activity_id int(10) unsigned NOT NULL,
  PRIMARY KEY(user_id, activity_id),
  extra_json varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  role int(10) DEFAULT 0 NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (activity_id) REFERENCES activities(id) 
);

ALTER TABLE user_activity ADD COLUMN signin int(10) DEFAULT 0 NOT NULL;

commit;

<file_sep><?php
    /**
     * 取消用户在活动中的管理员权限
     */
    include_once("../../../lib/common.php");
    include_once("../../../lib/user_activity.php");
    session_start();

    $user_id = intval($_GET['user_id']);
    $activity_id = intval($_GET['activity_id']);

    // 检查user是否已经是activity的管理员
    if (empty($user_id) || checkActivityManager($user_id, $activity_id) === 0) {
        header("Location: ../attendentManager.php?activity_id=". $activity_id);
        exit();
    }

    $ret = cancelActivityManager($activity_id, $user_id);
    if ($ret['status'] === 0) {
        header("Location: ../attendentManager.php?activity_id=" . $activity_id);
        exit();
    } else {
        echo($ret['err_msg']);
        exit();
    }
?>
<file_sep><?php
    /**
     * 为活动的参加者添加管理员权限
     */
    include_once("../../../lib/common.php");
    include_once("../../../lib/user_activity.php");
    session_start();

    $user_id = intval($_GET['user_id']);
    $activity_id = intval($_GET['activity_id']);

    // 检查user是否是activity的参加者
    if (empty($user_id) || checkActivityAttendent($user_id, $activity_id) === 0) {
        echo('user'+$user_id+' is not a participant of activity'+$activity_id);
        exit();
    }

    //检测user是否已经签到过了
    if (check_sign_in($activity_id, $user_id) === 1)
    {
        #$msg = 'user'+$user_id+' has already signed in activity'+$activity_id;
        $msg = '您已签到，请勿重复签到';
        $smarty3->assign('msg', $msg);
        $smarty3->display("../../html/successfulSignin.html");
        exit();
    }

    //执行签到操作
    $ret = add_sign_in($activity_id, $user_id);
    if ($ret['status'] === 0) {
        $msg = '您已成功签到';
        $smarty3->assign('msg', $msg);
        $smarty3->display("../../html/successfulSignin.html");
    } else {
        echo($ret['err_msg']);
        exit();
    }
?>
<file_sep><?php
    include_once("common.php");
    include_once("activity.php");

    /**
     * get activities by user_id
     * @param  $user_id
     * @return  array
     */
    function getActivitiesByUserId($user_id) {
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        $sql = "select * 
                from user_activity  
                where user_id = $user_id 
                order by activity_id desc";
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        $ret = array(
            "num" => $result->num_rows,
            "list" => array()
        );
        while ($row = $result->fetch_assoc()) {
            $activity_id = $row['activity_id'];
            $activities = getActivities(array("id" => $activity_id));
            $activities['list'][0]['role'] = $row['role'];
            $ret['list'][] = $activities['list'][0];
        }
        $conn->close();
        return $ret;
    }

    /**
     * get participants by activity_id
     * @param  $activity_id
     * @return  array
     */
    function getUserIdByActivities($activity_id) {
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        $sql = "select * 
                from user_activity  
                where activity_id = $activity_id 
                order by role, user_id desc";
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        $ret = array(
            "num" => $result->num_rows,
            "list" => array()
        );
        while ($row = $result->fetch_assoc()){
            $user['user_id'] = $row['user_id'];
            $user_id = $row['user_id'];
            $user['role'] = $row['role'];
            $user['signin'] = $row['signin'];
            $sql = "select * from users where id = $user_id";
            $result_user = $conn->query($sql);
            while ($row_user = $result_user->fetch_assoc())
            {
                $user['email'] = $row_user['email'];
                $user['username'] = $row_user['username'];
            }
            $ret['list'][] = $user;
        }
        $conn->close();
        return $ret;
    }

    /**
     * get users who do not currently participate in activity_id
     * used to show all users in attendent managament page
     * @param  $activity_id
     * @return  array
     */
    function getOtherUserIdByActivities($activity_id) {
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        $sql = "select * 
                from user_activity  
                where activity_id = $activity_id 
                order by user_id desc";
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        $sql_other = "select * from users ";
        $count = 0;
        while ($row = $result->fetch_assoc()){
            if ($count == 0)
                $sql_other = $sql_other . "where id != " . $row['user_id'];
            else $sql_other = $sql_other . " and id != " . $row['user_id'];
            $count = $count + 1;
        }
        //echo($sql_other);
        //exit();
        $result_other = $conn->query($sql_other);
        $ret = array(
            "num" => $result_other->num_rows,
            "list" => array()
        );
        while ($row_other = $result_other->fetch_assoc()) {
            $user['user_id'] = $row_other['id'];
            $user['email'] = $row_other['email'];
            $user['username'] = $row_other['username'];
            $ret['list'][] = $user;
        }
        $conn->close();
        return $ret;
    }

    /**
     * check whether the user is the manager of the activity
     * @param $user_id, $activity_id
     * @return 1 manager  0 attendent
     */
    function checkActivityManager($user_id, $activity_id) {
        $sql = "select * from user_activity
                where user_id = $user_id and activity_id = $activity_id";
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result === false || $result->num_rows === 0)  {
            //$ret = false;
            die("User-activity pair not found.");
        }
        $row = $result->fetch_assoc();
        $conn->close();
        return intval($row['role']);
    }

    /**
     * check whether user is the participant of the activity
     * @param $user_id, $activity_id
     * @return 1 manager or attendent 0 not
     */
    function checkActivityAttendent($user_id, $activity_id){
        $sql = "select * from user_activity
                where user_id = $user_id and activity_id = $activity_id";
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result === false || $result->num_rows === 0)
            return 0;
        return 1;
    }

    /**
     * check whether user_id has already signed in activity_id
     * @param $activity_id, $user_id
     * @return 1 already sign in 0 not
     */
    function check_sign_in($activity_id, $user_id){
        $sql = "select * from user_activity
                where user_id = $user_id and activity_id = $activity_id and signin = 1";
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result === false || $result->num_rows === 0)
            return 0;
        return 1;
    }

    /**
     * let user_id sign into activity_id
     * @param $activity_id, $user_id
     * @return array
     */
    function add_sign_in($activity_id, $user_id){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        if (empty($user_id)) {
            $ret['err_msg'] = 'empty user id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "update user_activity set signin = 1 where activity_id = $activity_id and user_id = $user_id";
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * delete an item by activity_id or/and user_id
     * @param $activity_id, $user_id
     * @return array
     */
    function deleteRecordById($activity_id, $user_id = NULL) {
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "delete from user_activity
                where activity_id = $activity_id ";
        if (!empty($user_id)) {
            $sql = $sql . "and user_id = $user_id";
        }
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * make user_id the manager of activity_id
     * @param $activity_id, $user_id
     * @return array
     */
    function addActivityManager($activity_id, $user_id){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        if (empty($user_id)) {
            $ret['err_msg'] = 'empty user id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "update user_activity set role = 1 where activity_id = $activity_id and user_id = $user_id";
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * make user_id(not activity's participant) the manager of activity_id
     * @param $activity_id, $user_id
     * @return array
     */
    function addActivityManagerNon($activity_id, $user_id){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        if (empty($user_id)) {
            $ret['err_msg'] = 'empty user id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "insert into user_activity (user_id, activity_id, role) values($user_id, $activity_id, 1);";
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * cancel the manager priority of user_id on activity_id
     * @param $activity_id, $user_id
     * @return array
     */
    function cancelActivityManager($activity_id, $user_id){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        if (empty($user_id)) {
            $ret['err_msg'] = 'empty user id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "update user_activity set role = 0 where activity_id = $activity_id and user_id = $user_id";
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }

    /**
     * make user_id user of activity_id
     * @param $activity_id, $user_id
     * @return array
     */
    function addActivityUser($activity_id, $user_id){
        $ret = array(
            'status' => 1,
            'err_msg' => ''
        );
        if (empty($activity_id)) {
            $ret['err_msg'] = 'empty activity id';
            return $ret;
        }
        if (empty($user_id)) {
            $ret['err_msg'] = 'empty user id';
            return $ret;
        }
        $conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DBNAME);
        if ($conn->connect_error) {
            die($conn->connect_error);
        }
        $sql = "insert into user_activity (user_id, activity_id, role) values($user_id, $activity_id, 0);";
        $result = $conn->query($sql);
        if ($result === false) {
            $ret['err_msg'] = $conn->error;
        } else {
            $ret['status'] = 0;
        }

        $conn->close();
        return $ret;
    }



    ?><file_sep># Project on Computer Network

### Authors
\- Fiona730   
\- ChillySong   
\- Tianye

### Description



### Usage
**How to run SQL script**<br>
Suppose SQL script is stored in `/path/to/sql/setup.sql`<br>
1. Run mysql server
2. Enter the client, e.g.
```
mysql -u <Username> -p
```
3. Run the SQL script
```
source /path/to/sql/setup.sql
```
<file_sep>{%extends file="./tpl/layout.html"%} 
{%block name="css"%}
	<link href="/css/login.css" rel="stylesheet" type="text/css" media="all">
	<link href="/css/link.css" rel="stylesheet" type="text/css" media="all">
{%/block%}

{%block name="title"%}<title>活动信息</title>{%/block%}

{%block name="body"%}
<body>
				<div class="mid-class">
					 <div class="art-right-w3ls">
                        <div id="" style="background-color:rgba(100,149,237,0.6);font-size:25px;position: absolute;color:rgba(255,255,255,1);padding:40px 50px 50px 50px;width:1000px">
							<h3 style="margin:10px;">{%$msg%}</h3>
                            <p style=" text-align:right;font-size:15px;"><a href="/manageActivity.php">返回信息管理界面</a></p>
					    </div>
                     </div>
				</div>
</body>
{%/block%}

{%block name="javascript"%}
{%/block%}<file_sep><?php
    include_once("../../../lib/common.php");
    include_once("../../../lib/user.php");
    session_start();

    $param = $_POST;
    $ret = addUser($param);
    
    if ($ret['status'] === 0) {
        $_SESSION['username'] = $_POST['username'];
        $_SESSION['uid'] = $ret['uid'];
        $_SESSION['role'] = 0;
        header("Location: ../../index.php");
        exit();
    }
    else {
        header("Location: ../../register.php");
        exit();
    }
?>
<file_sep><?php
	include_once("../../lib/common.php");
	include_once("../../lib/user_activity.php");
	session_start();

	$activity_id = intval($_GET['activity_id']);
	$other_users = getOtherUserIdByActivities($activity_id);
	$other_list = $other_users['list'];
	$smarty3->assign('other_list', $other_list);
	$smarty3->assign('activity_id', $activity_id);

	if(!empty($_SESSION['username']))
	{
		$smarty3->assign('login', true);
		$smarty3->assign('username', $_SESSION['username']);
        $smarty3->assign('role', $_SESSION['role']);
	}
	else{$smarty3->assign('login', false);}

	$smarty3->display('../html/attendentManager_others.html');
?>
<file_sep><?php
    include_once("../../lib/common.php");
    include_once("../../lib/user.php");

    $param = empty($_GET) ? $_POST : $_GET;
    $username = $param['username'];
    if (checkUsernameUsed($username)) 
        echo "用户名已被使用";
    else 
        echo "";
?>
<file_sep><?php
    /**
     * 这个是只给管理员删除活动所用的API, 会删除activity中的表项，如果只想删除某个用户
     * 参与某个活动的记录，应该调用deleteRecord.php
     */
    include_once("../../../lib/common.php");
    include_once("../../../lib/activity.php");
    include_once("../../../lib/user_activity.php");
    session_start();

    $uid = $_SESSION['uid'];
    $role = intval($_SESSION['role']);
    $activity_id = $_GET['activity_id'];

    // 身份验证，只有超级管理员与该活动的管理员才能进行删除
    if (!($role === 1 || checkActivityManager($uid, $activity_id) === 1)) {
        header("Location: manageActivity.php");
        exit();
    } 
    $ret = deleteActivity($activity_id);
    if ($ret['status'] === 0) {
        if ($role === 1)  header("Location: ../../index.php");
        else  header("Location: ../../manageActivity.php");
        exit();
    }
    else {
        echo($ret['err_msg']);
        exit();
    }
?><file_sep><?php
    include_once("../../../lib/common.php");
    include_once("../../../lib/activity.php");
    include_once("../../../lib/user_activity.php");
    
    require_once("../../../third_party/PHPSpreadSheet/vendor/autoload.php");
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    $param = $_POST;
    $activity_id = $param['activity_id'];
    
    $ret = getUserIdByActivities($activity_id);

    /*
     使用 PHPSpreadSheet库创建xlsx文件
    */ 
    $objSpreadsheet = new Spreadsheet();
    $objSpreadsheet->getActiveSheet()
                ->setCellValue('A1', '用户ID')
                ->setCellValue('B1', '用户名')
                ->setCellValue('C1', '用户身份')
                ->setCellValue('D1', '邮箱');
    
    for ($i = 0; $i < $ret['num']; $i++) {
        $user = $ret['list'][$i];
        if ($user['role'] == 0) {
            $user['role'] = '参加者';
        } else {
            $user['role'] = '管理员';
        }
        $objSpreadsheet->getActiveSheet()
                    ->setCellValue('A' . ($i + 2), $user['user_id'])
                    ->setCellValue('B' . ($i + 2), $user['username'])
                    ->setCellValue('C' . ($i + 2), $user['role'])
                    ->setCellValue('D' . ($i + 2), $user['email']);
    }

    $objSpreadsheet->getActiveSheet()->setTitle('参会者信息');
    $objSpreadsheet->setActiveSheetIndex(0);

    $tmp = tempnam('.', 'phpxltmp');
    $writer = new Xlsx($objSpreadsheet);
    //$writer->save('export.xlsx');
    $writer->save($tmp);

    $fp = fopen($tmp, 'r');
    $file_size = filesize($tmp);

    Header("Content-type: application/octet-stream");
    Header("Accept-Ranges: bytes");
    Header("Accept-Length: " . $file_size);
    Header("Content-Disposition: attachment; filename=参会者信息.xlsx");
    $buffer = 1024;
    $file_count = 0;

    while (!feof($fp) && $file_count < $file_size) {
        $file_con = fread($fp, $buffer);
        $file_count += $buffer;
        echo $file_con;
    }
    fclose($fp);

    unlink($tmp);
?><file_sep><?php
    /**
     * 删除某个用户参与活动的记录
     */
    include_once("../../../lib/common.php");
    include_once("../../../lib/user_activity.php");
    session_start();

    $uid = intval($_SESSION['uid']);
    $user_id = intval($_GET['user_id']);
    $activity_id = intval($_GET['activity_id']);

    // 身份验证，get中获取的user_id应该与session中的一致
    if (empty($user_id) || $uid !== $user_id) {
        header("Location: ../../manageActivity.php");
        exit();
    }

    $ret = deleteRecordById($activity_id, $user_id);
    if ($ret['status'] === 0) {
        header("Location: ../../manageActivity.php");
        exit();
    } else {
        echo($ret['err_msg']);
        exit();
    }
?>
<file_sep><?php
    include_once("../../../lib/common.php");
    include_once("../../../lib/user.php");
    session_start();
    $username = $_POST['username'];
    $password = $_POST['<PASSWORD>'];
    $ret = checkUser($username, $password);

    // login success
    if ($ret['status'] === 0) {
        $_SESSION['uid'] = $ret['uid'];
        $_SESSION['username'] = $username;
        $_SESSION['role'] = intval($ret['role']);
        header("Location: ../../index.php"); 
        exit();
    }
    // login fail
    else {
        header("Location: ../../login.php");
        exit();
    }
?>
<file_sep><?php
    include_once("../lib/user.php");

/*
    $username = $_GET[user];
    $ret = checkUsernameUsed($username);
    if ($ret) {
        echo "Username " . $username . " is used";
    } else {
        echo "Username " . $username . " is not used";
    }
*/
    echo $_GET[user];

?>
<file_sep><?php
	include_once("../../lib/common.php");
	include_once("../../lib/user_activity.php");
	session_start();

	$activity_id = intval($_GET['activity_id']);
	#$activity_id = intval(1);
	
	$users = getUserIdByActivities($activity_id);
	$list = $users['list'];
	$smarty3->assign('list', $list);
	$smarty3->assign('activity_id', $activity_id);

	if(!empty($_SESSION['username']))
	{
		$smarty3->assign('login', true);
		$smarty3->assign('username', $_SESSION['username']);
        $smarty3->assign('role', $_SESSION['role']);
	}
	else{$smarty3->assign('login', false);}

	$smarty3->display('../html/signInState.html');
?>
<file_sep>### 开机启动

+ 打开php-fpm

  ```bash 
  php-fpm --fpm-config /usr/local/etc/php-fpm.conf --prefix /usr/local/var
  ```

+ 打开nginx: 

  ```bash
  nginx -c /usr/local/etc/nginx/nginx.conf
  nginx -s reload
  ```

+ 打开mysql

  ```bash
  /usr/local/Cellar/mysql/8.0.15/bin/mysql.server restart
  ```


### .gitignore

* When you add something into a .gitignore file, try this:
```
git add [uncommitted changes you want to keep] && git commit
git rm -r --cached .
git add .
git commit -m "fixed untracked files"
```

* If you remove something from a .gitignore file, and the above steps don't work, try this:
```
git add -f [files you want to track again]
git commit -m "Refresh removing files from .gitignore file."

// For example, if you want the .java type file to be tracked again,
// The command should be:
//     git add -f *.java
```

### activityList.html的css样式说明

`style.css`:
```css
.content {
      float: left;
        padding: 1.875em;
          width: 100%; 
} /* 位于card类的外一层*/
```

`bootstrap.min.css`:
```css
.card {
        background-color: #fff;                 /* 背景颜色 */
        border: 1px solid rgba(0, 0, 0, .125);  /* 边框粗细、线型、颜色 */
        border-radius: .25rem                   /* 边框四角曲率 */
} /* 衬在表格下方的区域的属性，位于content类的内一层，table类的外一层 */

.table {
        background-color: transparent           /* 表格背景颜色 */
} /* 表格属性，位于card类的内一层 */
```

`bootstrap.min.css`定义了几种表格类的属性，例如`table-hover`表示带有悬停效果的表格样式。只要在html的`table`标签的`class`中添加`table-hover`即可使用。一张表格可以同时属于多个类，因此这些效果可以叠加。




### 环境配置

**For Ubuntu**

安装配置nginx+php+mysql:

   https://blog.51cto.com/13791715/2161170

**For mac**

(1) 下载nginx，配置nginx配置文件

   https://segmentfault.com/a/1190000014610688

   ```
       server {
           listen       8080;
           server_name  localhost;
           #charset koi8-r;
           root /Users/songchenlei/Desktop/Project-on-Computer-Network/project/web;
   
           #access_log  logs/host.access.log  main;
   
           location / {
               index index.php;
               #autoindex on;
               autoindex_exact_size off;
               autoindex_localtime on;
           }
   
           location ~ \.php$ {
               #proxy_pass   http://127.0.0.1;
               fastcgi_pass  127.0.0.1:9000;
               fastcgi_index index.php;
               fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
               include       fastcgi_params;
           }
   ```

(2) 安装下载php-fmp

   https://blog.csdn.net/ivan820819/article/details/54970330

   https://lzw.me/a/mac-osx-php-fpm-nginx-mysql.html

(3) 下载smarty，将lib文件夹复制到web目录下，创建tpls目录，并在该目录下创建templates、templates_c、configs、cache目录。创建一个main.php，可以看我本地的代码，
    
   smarty库已经在`project/smarty3`中，不需要重新下载，只需要修改配置文件`project/config.php`

   分隔符设置的是{%和%}

   https://www.open-open.com/solution/view/1319016010156

(4) 安装配置MySQL 修改密码`ALTER USER 'root'@'localhost' IDENTIFIED BY 'chichichi!';`

   ```sql
   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
   BY '<PASSWORD>';  
   ```

**For Windows**

http://www.nginx.cn/4514.html

或使用Win10内置的WSL功能，安装Ubuntu子系统，再按Linux的方法进行安装和配置

### Mysql表说明
+ users
   + role 1 表示管理员（需要手动在MySQL数据库内修改）  0 表示普通用户，注册时默认均为普通用户
+ activities
+ user_activity
   + role 1 表示当前用户为该活动的管理员  0 表示普通与会人员

### 一些ideas 

+ 保持客户端登录状态
  + session
+ 表单的一些验证，如密码、确认密码一致；密码长度；用户名… 用js
+ common.php 中WEB_ROOT改成自己电脑上的配置
+ 防止sql注入：使用预处理语句
  + Php.ini 中 magic_quotes_gpc=on， display_errors=off
  + 数字类型的sql字段，将str转为int
+ Smarty有自动cache机制，可能会导致显示的数据与后台不同步
+ login里面记住密码、忘记密码
+ 现阶段可以实现管理员删除activity的操作，接下去可以在删除活动时，发送消息给所有与会人员
+ 超级管理员的activity页面，可以设置删除/修改等操作
+ 个人profile，以及个人信息的修改
+ 弹窗/确认栏的美化
+ deleteRecord.php 用户删除参与会议的记录，只需要修改user_activity表
+ 千万注意！！！！从MySQL中取出的值，就算是int型，在php中也是str，必须用intval()函数进行转化
+ 可以设置某些用户为活动的管理人员

5/4

* 统一界面的中英文
* activities增加了description和web address两项，要在注册活动时加入这两项
* 查看活动信息界面的美化（太丑了…）
* 管理员权限
  * 现在只有超级管理员可以设置活动管理员，要让活动的管理员也能给自己的活动加管理员（即增加一个普通用户对自己管理的活动的管理界面）
  * 管理员之间（超级管理员vs活动管理员，活动创建者vs活动管理员）是否需要分优先级
* 超级管理员添加删除用户账号功能
* 导出活动参加者的数据为JSON文件 & 导入JSON文件一次性添加多个活动参加者
* 活动开始时间必须早于结束时间的约束
* 后续还可以实现活动时间的分段以及每个时间段不同安排<file_sep><?php
    include_once("../lib/common.php");
    include_once("../lib/activity.php");
    session_start();
    if (!empty($_SESSION['username'])) {
        $smarty3->assign('login', true);
        $smarty3->assign('username', $_SESSION['username']);
        $smarty3->assign('role', $_SESSION['role']);
    } else {
        $smarty3->assign('login', false);
    }

    $activities = getActivities();
    $list = $activities['list'];
    $smarty3->assign('list', $list);
    $smarty3->display('./html/activityList.html');
?>
