<%@ page contentType="text/plain; charset=UTF-8"%>
<%@ page language="java"%>
<%@ page import="java.sql.*,ajax.db.DBUtils,org.json.simple.JSONObject,org.json.simple.JSONArray"%>
<%!
    String sessionKey = "_LOGIN_USER_";             //session內登錄key

    //用戶登錄
    String login(String userName, String password, HttpSession session) {
        StringBuffer result = new StringBuffer();
        String sql = "select id, name, password from task_users where name = ?";//定義SQL語句
        Connection conn = null;                     //聲明Connection對象
        PreparedStatement pstmt = null;             //聲明PreparedStatement對象
        ResultSet rs = null;                        //聲明ResultSet對象
        try {
            conn = DBUtils.getConnection();         //獲取數據庫連接
            pstmt = conn.prepareStatement(sql);     //根據sql創建PreparedStatement
            pstmt.setString(1, userName);           //設置參數
            rs = pstmt.executeQuery();              //執行查詢，返回結果集
            if (rs.next()) {
                String dbPass = rs.getString("password");       //獲取密碼
                //根據密碼匹配結果設置不同輸出結果
                if (dbPass.equals(password)) {                  //登錄成功
                    session.setAttribute(sessionKey, rs.getString("id")); //設置session值
                    result.append("1");                         //結果第一個字符設置為1
                    result.append("<div>歡迎用戶 " + userName + " 登錄系統。</div>");
                    result.append("<input type='button' value='退出' onclick='logout()'>");
                } else {                                        //登錄失敗
                    result.append("0");                         //結果第一個字符設置為0
                    result.append("密碼錯誤。");
                }
            } else {                                            //登錄失敗
                result.append("0");                             //結果第一個字符設置為0
                result.append("該用戶不存在。");
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                  //關閉結果集
            DBUtils.close(pstmt);               //關閉PreparedStatement
            DBUtils.close(conn);                //關閉連接
        }
        return result.toString();
    }

    //獲取任務列表
    String getLists(String userId) {
        JSONArray array = new JSONArray();      //定義JSON數組
        StringBuffer result = new StringBuffer("[");
        String sql = "select id, listname from task_lists where userid = ? order by listname asc";   //定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        ResultSet rs = null;                    //聲明ResultSet對象
        try {
            conn = DBUtils.getConnection();     //獲取數據庫連接
            pstmt = conn.prepareStatement(sql); //根據sql創建PreparedStatement
            pstmt.setString(1, userId);         //設置參數
            rs = pstmt.executeQuery();
            //遍歷結果集，給JSON數組中加入JSONObject
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                obj.put("id", rs.getString(1));
                obj.put("name", rs.getString(2));
                array.add(obj);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                  //關閉結果集
            DBUtils.close(pstmt);               //關閉PreparedStatement
            DBUtils.close(conn);                //關閉連接
        }
        return array.toString();
    }

    //獲取任務
    String getTasks(String userId, String listId) {
        JSONArray array = new JSONArray();      //定義JSON數組
        String sql = "select id, taskname, status from task_tasks where userid = ? and listid = ? order by taskname asc";   //定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        ResultSet rs = null;                    //聲明ResultSet對象
        try {
            conn = DBUtils.getConnection();     //獲取數據庫連接
            pstmt = conn.prepareStatement(sql); //根據sql創建PreparedStatement
            pstmt.setString(1, userId);         //設置參數
            pstmt.setString(2, listId);         //設置參數
            rs = pstmt.executeQuery();
            //遍歷結果集，給JSON數組中加入JSONObject
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                obj.put("id", rs.getString(1));
                obj.put("name", rs.getString(2));
                obj.put("status", rs.getString(3));
                array.add(obj);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                  //關閉結果集
            DBUtils.close(pstmt);               //關閉PreparedStatement
            DBUtils.close(conn);                //關閉連接
        }
        return array.toString();
    }

    //改變任務狀態
    void changeTaskStatus(String userId, String taskId, String status) {
        String sql = "update task_tasks set status = ? where userid = ? and id = ?";   //定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {
            conn = DBUtils.getConnection();     //獲取數據庫連接
            pstmt = conn.prepareStatement(sql); //根據sql創建PreparedStatement
            pstmt.setString(1, status);         //設置參數
            pstmt.setString(2, userId);         //設置參數
            pstmt.setString(3, taskId);         //設置參數
            pstmt.executeUpdate();              //執行更新
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關閉PreparedStatement
            DBUtils.close(conn);                //關閉連接
        }
    }

    //添加任務列表
    void addList(String userId, String listName) {
        String sql = "insert into task_lists(userid, listname) values(?,?)";   //定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {
            conn = DBUtils.getConnection();     //獲取數據庫連接
            pstmt = conn.prepareStatement(sql); //根據sql創建PreparedStatement
            pstmt.setString(1, userId);         //設置參數
            pstmt.setString(2, listName);       //設置參數
            pstmt.executeUpdate();              //執行插入
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關閉PreparedStatement
            DBUtils.close(conn);                //關閉連接
        }
    }

    //刪除任務列表
    void delList(String userId, String listId) {
        String sql1 = "delete from task_tasks where userid = ? and listid = ?"; //刪除任務SQL
        String sql2 = "delete from task_lists where userid = ? and id = ?";     //刪除列表SQL
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {