<%@ 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內(nèi)登錄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();         //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql);     //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, userName);           //設(shè)置參數(shù)
            rs = pstmt.executeQuery();              //執(zhí)行查詢，返回結(jié)果集
            if (rs.next()) {
                String dbPass = rs.getString("password");       //獲取密碼
                //根據(jù)密碼匹配結(jié)果設(shè)置不同輸出結(jié)果
                if (dbPass.equals(password)) {                  //登錄成功
                    session.setAttribute(sessionKey, rs.getString("id")); //設(shè)置session值
                    result.append("1");                         //結(jié)果第一個(gè)字符設(shè)置為1
                    result.append("<div>歡迎用戶 " + userName + " 登錄系統(tǒng)。</div>");
                    result.append("<input type='button' value='退出' onclick='logout()'>");
                } else {                                        //登錄失敗
                    result.append("0");                         //結(jié)果第一個(gè)字符設(shè)置為0
                    result.append("密碼錯(cuò)誤。");
                }
            } else {                                            //登錄失敗
                result.append("0");                             //結(jié)果第一個(gè)字符設(shè)置為0
                result.append("該用戶不存在。");
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                  //關(guān)閉結(jié)果集
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
        return result.toString();
    }

    //獲取任務(wù)列表
    String getLists(String userId) {
        JSONArray array = new JSONArray();      //定義JSON數(shù)組
        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();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, userId);         //設(shè)置參數(shù)
            rs = pstmt.executeQuery();
            //遍歷結(jié)果集，給JSON數(shù)組中加入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);                  //關(guān)閉結(jié)果集
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
        return array.toString();
    }

    //獲取任務(wù)
    String getTasks(String userId, String listId) {
        JSONArray array = new JSONArray();      //定義JSON數(shù)組
        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();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, userId);         //設(shè)置參數(shù)
            pstmt.setString(2, listId);         //設(shè)置參數(shù)
            rs = pstmt.executeQuery();
            //遍歷結(jié)果集，給JSON數(shù)組中加入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);                  //關(guān)閉結(jié)果集
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
        return array.toString();
    }

    //改變?nèi)蝿?wù)狀態(tài)
    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();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, status);         //設(shè)置參數(shù)
            pstmt.setString(2, userId);         //設(shè)置參數(shù)
            pstmt.setString(3, taskId);         //設(shè)置參數(shù)
            pstmt.executeUpdate();              //執(zhí)行更新
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
    }

    //添加任務(wù)列表
    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();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, userId);         //設(shè)置參數(shù)
            pstmt.setString(2, listName);       //設(shè)置參數(shù)
            pstmt.executeUpdate();              //執(zhí)行插入
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
    }

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