package java_code.cloud_database;

import java.sql.*;
import java.util.Scanner;
import java.util.ArrayList;
import java.text.ParseException;
import java.text.SimpleDateFormat;


public class StaffManagementSystem {
    static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
    static final String DB_URL = "jdbc:opengauss://110.41.126.115:8000/CQU_DB2021?ApplicationName=app1";
    static final String USER = "db_user2021_230";
    static final String PASS = "db_user@123";
    static Connection conn = null;
    static Statement stmt = null;
    static Scanner in = null;
    static String userID = null;

    public static void main(String[] args) {

        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("连接数据库中...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            System.out.println("成功连接数据库。");
        } catch (SQLException se) {
            System.out.println("连接数据库失败！");
            System.out.println("报错信息：" + se);
            return;
        } catch (Exception e) {
            System.out.println("程序运行失败！");
            System.out.println("报错信息：" + e);
            return;
        }
        in = new Scanner(System.in);
        System.out.println("欢迎来到企业员工管理系统");
        LogIn();
        boolean exit = false;
        while (!exit) {
            Boolean isAdmin = null;
            try {
                String sql = "SELECT administrator FROM staff_member WHERE ID_card_number = " + userID;
                ResultSet rs = stmt.executeQuery(sql);
                rs.next();
                isAdmin = rs.getBoolean("administrator");
            } catch (SQLException se) {
                System.out.println("报错信息：" + se);
                return;
            }
            System.out.println("请选择您想要执行的操作：");
            if (isAdmin) {
                System.out.println("(p)人员信息 (d)部门信息 (r)批假 (e)退出");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "p":
                        StaffInfo();
                        break;
                    case "d":
                        DeptInfo();
                        break;
                    case "r":
                        RecessInfo();
                        break;
                    case "e":
                        exit = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                        break;
                }
            } else {
                System.out.println("(p)个人信息 (r)请假 (e)退出");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "p":
                        Self();
                        break;
                    case "r":
                        Recess();
                        break;
                    case "e":
                        exit = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                        break;
                }
            }
        }
        in.close();
        try {
            if (stmt != null) stmt.close();
        } catch (SQLException se2) {
        }
        try {
            if (conn != null) conn.close();
        } catch (SQLException se) {
            System.out.println("关闭数据库连接失败！");
            System.out.println("报错信息：" + se);
        }
        System.out.println("已退出");
    }

    public static Integer InputInt() {
        Integer num = null;
        boolean valid = false;
        while (!valid) {
            String s = in.nextLine();
            try {
                num = Integer.parseInt(s);
                valid = true;
            } catch (NumberFormatException ne) {
                System.out.println("格式错误！");
                System.out.println("请输入整数：");
            }
        }
        return num;
    }

    public static Date InputDate() {
        java.sql.Date date = null;
        boolean valid = false;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        while (!valid)
            try {
                System.out.println("请输入年份：");
                String yyyy = in.nextLine();
                System.out.println("请输入月份：");
                String MM = in.nextLine();
                System.out.println("请输入日期：");
                String dd = in.nextLine();
                java.util.Date parse = null;
                parse = sdf.parse(yyyy + "年" + MM + "月" + dd + "日");
                date = new java.sql.Date(parse.getTime());
                valid = true;
            } catch (ParseException pe) {
                System.out.println("格式错误！");
                System.out.println("报错信息：" + pe);
            }
        return date;
    }

    public static Double InputDouble() {
        Double d = null;
        Boolean valid = false;
        while (!valid)
            try {
                String s = in.nextLine();
                d = Double.parseDouble(s);
                valid = true;
            } catch (NumberFormatException ne) {
                System.out.println("格式错误！");
                System.out.println("请输入浮点数：");
            }
        return d;
    }

    public static Boolean InputBoolean() {
        Boolean b = null;
        Boolean valid = false;
        while (!valid)
            try {
                String s = in.nextLine();
                switch (s) {
                    case "true":
                        valid = true;
                        break;
                    case "false":
                        valid = true;
                        break;
                    default:
                        System.out.println("格式错误！");
                        System.out.println("请输入true或者false：");
                }
                b = Boolean.parseBoolean(s);
            } catch (NumberFormatException ne) {
            }
        return b;
    }

    public static String InputGender() {
        String s = null;
        Boolean valid = false;
        while (!valid) {
            s = in.nextLine();
            switch (s) {
                case "M":
                    valid = true;
                    break;
                case "F":
                    valid = true;
                    break;
                default:
                    System.out.println("格式错误！");
                    System.out.println("请输入M或者F：");
            }
        }
        return s;
    }

    public static void LogIn() {
        System.out.println("请先登录您的账号");
        boolean back = false;
        ResultSet rs = null;
        while (!back) {
            System.out.println("请输入身份证号：");
            String ID = in.nextLine();
            System.out.println("请输入密码：");
            String passWord = in.nextLine();
            boolean exist = false;
            boolean match = false;
            try {
                String sql = "SELECT ID_card_number FROM staff_member WHERE ID_card_number = " + ID;
                rs = stmt.executeQuery(sql);
                exist = rs.next();
            } catch (SQLException se) {
            }
            if (!exist) {
                System.out.println("账号不存在！");
                continue;
            }
            try {
                String sql = "SELECT ID_card_number,name FROM staff_member WHERE ID_card_number = " + ID + "AND password = " + passWord;
                rs = stmt.executeQuery(sql);
                match = rs.next();
            } catch (SQLException se) {
            }
            if (match)
                back = true;
            else
                System.out.println("密码错误！");
        }
        String userName = "unknown";
        try {
            userID = rs.getString("ID_card_number");
            userName = rs.getString("name");
            System.out.println("登录成功");
            System.out.println("您好! " + userName + " :)");
        } catch (SQLException se) {
        }
    }

    public static void StaffInfo() {
        boolean back = false;
        ArrayList<String> ID_card_number = new ArrayList<String>();
        try {
            while (!back) {
                String sql = "SELECT ID_card_number,dept_name,supervisor_ID,name FROM staff_member ORDER BY name";
                ResultSet rs = stmt.executeQuery(sql);
                System.out.println("   身份证号， 姓名，部门， 上司");
                int count = 0;
                ID_card_number.clear();
                while (rs.next()) {
                    ID_card_number.add(rs.getString("ID_card_number"));
                    count++;
                    System.out.println("(" + count + ")" + rs.getString("ID_card_number") + "， " + rs.getString("name") + "， " + rs.getString("dept_name") + "， " + rs.getString("supervisor_ID"));
                }
                rs.close();
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(c)查看员工信息 (n)新增员工 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "c":
                        System.out.println("请输入数字以选择您要查看的员工：");
                        int num = InputInt();
                        if (num <= 0 || count < num)
                            System.out.println("没有该选项！");
                        else
                            StaffUpdate(ID_card_number.get(num - 1));
                        break;
                    case "n":
                        NewStaff();
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void StaffUpdate(String ID_card_number) {
        Boolean back = false;
        try {
            while (!back) {
                String sql = "SELECT * FROM staff_member WHERE ID_card_number = '" + ID_card_number + "'";
                ResultSet rs = stmt.executeQuery(sql);
                rs.next();
                System.out.println("(1)*身份证号：" + rs.getString(1));
                System.out.println("(2)*部门：" + rs.getString(2));
                System.out.println("(3)o上级的身份证号：" + rs.getString(3));
                System.out.println("(4)*姓名：" + rs.getString(4));
                System.out.println("(5)*性别：" + rs.getString(5));
                System.out.println("(6)*出生日期：" + rs.getDate(6));
                System.out.println("(7)*就职日期：" + rs.getDate(7));
                System.out.println("(8)*职位：" + rs.getString(8));
                System.out.println("(9)*工资：" + rs.getString(9));
                System.out.println("(10)*密码：" + rs.getString(10));
                System.out.println("(11)*是否是管理员：" + rs.getBoolean(11));
                System.out.println("(12)o居住地：" + rs.getString(12));
                System.out.println("(13)o电话号码：" + rs.getString(13));
                System.out.println("(14)o电子邮件地址：" + rs.getString(14));
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(1)*身份证号 (2)*部门  (3)o上级 (4)*姓名 (5)*性别 (6)*出生日期 (7)*就职日期 (8)*职位 (9)*工资 (10)*密码");
                System.out.println("(11)*是否是管理员 (12)o居住地 (13)o电话号码 (14)o电子邮件地址 (d)删除该员工 (b)返回");
                String cmd = null;
                cmd = in.nextLine();
                switch (cmd) {
                    case "1":
                        System.out.println("请输入身份证号：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET ID_card_number = '" + cmd + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        if (ID_card_number.equals(userID))
                            userID = cmd;
                        ID_card_number = cmd;
                        System.out.println("修改成功");
                        break;
                    case "2":
                        System.out.println("请输入部门名：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET dept_name = '" + cmd + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "3":
                        System.out.println("请输入上级身份证号：");
                        cmd = in.nextLine();
                        if (!cmd.equals("null"))
                            cmd = "'" + cmd + "'";
                        stmt.executeUpdate("UPDATE staff_member SET supervisor_ID = " + cmd + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "4":
                        System.out.println("请输入姓名：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET name = '" + cmd + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "5":
                        System.out.println("请输入性别(M/F)：");
                        stmt.executeUpdate("UPDATE staff_member SET gender = '" + InputGender() + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "6":
                        System.out.println("请输入出生日期：");
                        stmt.executeUpdate("UPDATE staff_member SET birth_date = '" + InputDate() + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "7":
                        System.out.println("请输入就职日期：");
                        stmt.executeUpdate("UPDATE staff_member SET joining_date = '" + InputDate() + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "8":
                        System.out.println("请输入职务：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET duty = '" + cmd + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "9":
                        System.out.println("请输入工资：");
                        stmt.executeUpdate("UPDATE staff_member SET salary = " + InputDouble() + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "10":
                        System.out.println("请输入密码：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET password = '" + cmd + "' WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "11":
                        System.out.println("是否是管理员(true/false)：");
                        stmt.executeUpdate("UPDATE staff_member SET administrator = " + InputBoolean() + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "12":
                        System.out.println("请输入居住地：");
                        cmd = in.nextLine();
                        if (!cmd.equals("null"))
                            cmd = "'" + cmd + "'";
                        stmt.executeUpdate("UPDATE staff_member SET residence_address = " + cmd + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "13":
                        System.out.println("请输入电话号码：");
                        cmd = in.nextLine();
                        if (!cmd.equals("null"))
                            cmd = "'" + cmd + "'";
                        stmt.executeUpdate("UPDATE staff_member SET phone_number = " + cmd + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "14":
                        System.out.println("请输入电子邮件地址：");
                        cmd = in.nextLine();
                        if (!cmd.equals("null"))
                            cmd = "'" + cmd + "'";
                        stmt.executeUpdate("UPDATE staff_member SET email_address = " + cmd + " WHERE ID_card_number = '" + ID_card_number + "'");
                        System.out.println("修改成功");
                        break;
                    case "d":
                        if (!ID_card_number.equals(userID)) {
                            stmt.execute("DELETE FROM staff_member WHERE ID_card_number = " + ID_card_number);
                            System.out.println("删除成功");
                            back = true;
                        } else
                            System.out.println("不允许删除自己！");
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void NewStaff() {
        String ID_card_number = null;
        String dept_name = null;
        String supervisor_ID = null;
        String name = null;
        String gender = null;
        Date birth_date = null;
        Date joining_date = null;
        String duty = null;
        Double salary = null;
        String password = null;
        Boolean administrator = null;
        String residence_address = null;
        String phone_number = null;
        String email_address = null;
        Boolean back = false;
        while (!back) {
            System.out.println("(1)*身份证号：" + ID_card_number);
            System.out.println("(2)*部门：" + dept_name);
            System.out.println("(3)o上级的身份证号：" + supervisor_ID);
            System.out.println("(4)*姓名：" + name);
            System.out.println("(5)*性别：" + gender);
            System.out.println("(6)*出生日期：" + birth_date);
            System.out.println("(7)*就职日期：" + joining_date);
            System.out.println("(8)*职位：" + duty);
            System.out.println("(9)*工资：" + salary);
            System.out.println("(10)*初始密码：" + password);
            System.out.println("(11)*是否是管理员：" + administrator);
            System.out.println("(12)o居住地：" + residence_address);
            System.out.println("(13)o电话号码：" + phone_number);
            System.out.println("(14)o电子邮件地址：" + email_address);
            System.out.println("请选择您想要执行的操作：");
            System.out.println("(1)*身份证号 (2)*部门  (3)o上级 (4)*姓名 (5)*性别 (6)*出生日期 (7)*就职日期 (8)*职位 (9)*工资 (10)*初始密码");
            System.out.println("(11)*是否是管理员 (12)o居住地 (13)o电话号码 (14)o电子邮件地址 (a)确认 (c)取消");
            try {
                String cmd = null;
                cmd = in.nextLine();
                switch (cmd) {
                    case "1":
                        System.out.println("请输入身份证号：");
                        cmd = in.nextLine();
                        ID_card_number = cmd;
                        break;
                    case "2":
                        System.out.println("请输入部门名：");
                        cmd = in.nextLine();
                        dept_name = cmd;
                        break;
                    case "3":
                        System.out.println("请输入上级身份证号：");
                        cmd = in.nextLine();
                        supervisor_ID = cmd;
                        break;
                    case "4":
                        System.out.println("请输入姓名：");
                        cmd = in.nextLine();
                        name = cmd;
                        break;
                    case "5":
                        System.out.println("请输入性别(M/F)：");
                        gender = InputGender();
                        break;
                    case "6":
                        System.out.println("请输入出生日期：");
                        birth_date = InputDate();
                        break;
                    case "7":
                        System.out.println("请输入就职日期：");
                        joining_date = InputDate();
                        break;
                    case "8":
                        System.out.println("请输入职务：");
                        cmd = in.nextLine();
                        duty = cmd;
                        break;
                    case "9":
                        System.out.println("请输入工资：");
                        salary = InputDouble();
                        break;
                    case "10":
                        System.out.println("请输入初始密码：");
                        cmd = in.nextLine();
                        password = cmd;
                        break;
                    case "11":
                        System.out.println("是否是管理员(true/false)：");
                        administrator = InputBoolean();
                        break;
                    case "12":
                        System.out.println("请输入居住地：");
                        cmd = in.nextLine();
                        residence_address = cmd;
                        break;
                    case "13":
                        System.out.println("请输入电话号码：");
                        cmd = in.nextLine();
                        phone_number = cmd;
                        break;
                    case "14":
                        System.out.println("请输入电子邮件地址：");
                        cmd = in.nextLine();
                        email_address = cmd;
                        break;
                    case "a":
                        String _supervisor_ID_ = null;
                        String _residence_address_ = null;
                        String _phone_number_ = null;
                        String _email_address_ = null;
                        if (supervisor_ID != null && !supervisor_ID.equals("null")) {
                            _supervisor_ID_ = "'" + supervisor_ID + "'";
                        }
                        if (residence_address != null && !residence_address.equals("null")) {
                            _residence_address_ = "'" + residence_address + "'";
                        }
                        if (phone_number != null && !phone_number.equals("null")) ;
                    {
                        _phone_number_ = "'" + phone_number + "'";
                    }
                    if (email_address != null && !phone_number.equals("null")) ;
                    {
                        _email_address_ = "'" + email_address + "'";
                    }
                    stmt.execute("INSERT INTO staff_member VALUES('" + ID_card_number + "','" + dept_name + "'," + _supervisor_ID_ + ",'" + name + "','" + gender + "','" + birth_date + "','" + joining_date + "','" + duty + "'," + salary + ",'" + password + "'," + administrator + "," + _residence_address_ + "," + _phone_number_ + "," + _email_address_ + ")");
                    System.out.println("新增成功");
                    ;
                    back = true;
                    break;
                    case "c":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                        break;
                }
            } catch (SQLException se) {
                System.out.println("报错信息：" + se);
            } catch (Exception e) {
                System.out.println("报错信息：" + e);
            }
        }
    }

    public static void DeptInfo() {
        try {
            Boolean back = false;
            ArrayList<String> dept_name = new ArrayList<String>();
            while (!back) {
                String sql = "SELECT dept_name,location FROM department";
                ResultSet rs = stmt.executeQuery(sql);
                System.out.println("   部门名， 地址");
                int count = 0;
                dept_name.clear();
                while (rs.next()) {
                    dept_name.add(rs.getString("dept_name"));
                    count++;
                    System.out.println("(" + count + ")" + rs.getString("dept_name") + "， " + rs.getString("location"));
                }
                rs.close();
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(c)查看账本 (n)新增部门 (d)删除部门 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "c":
                        System.out.println("请输入数字以选择您所要查看的部门：");
                        int num = InputInt();
                        if (num <= 0 || count < num)
                            System.out.println("没有该选项！");
                        else
                            IncomeExpense(dept_name.get(num - 1));
                        break;
                    case "n":
                        NewDept();
                        break;
                    case "d":
                        System.out.println("请输入数字以选择您所要删除的部门：");
                        num = InputInt();
                        if (num <= 0 || count < num)
                            System.out.println("没有该选项！");
                        else
                            stmt.execute("DELETE FROM department WHERE dept_name = '" + dept_name.get(num - 1) + "'");
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void NewDept() {
        try {
            boolean back = false;
            String dept_name = null;
            String location = null;
            while (!back) {
                System.out.println("部门名：" + dept_name);
                System.out.println("地址：" + location);
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(1)*部门名 (2)*地址 (a)确认 (c)取消");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "1":
                        System.out.println("请输入部门名：");
                        cmd = in.nextLine();
                        dept_name = cmd;
                        break;
                    case "2":
                        System.out.println("请输入部门地址：");
                        cmd = in.nextLine();
                        location = cmd;
                        break;
                    case "a":
                        stmt.execute("INSERT INTO department VALUES('" + dept_name + "','" + location + "')");
                        System.out.println("新增成功");
                        back = true;
                        break;
                    case "c":
                        back = true;
                        break;
                    default:
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void IncomeExpense(String dept_name) {
        try {
            boolean back = false;
            boolean onlyin = false;
            boolean onlyex = false;
            Date begin = Date.valueOf("2000-01-01");
            Date stop = Date.valueOf("2100-12-31");
            while (!back) {
                String cond = "dept_name = '" + dept_name + "' AND income_expense_time >= '" + begin + "' AND income_expense_time <= '" + stop + "'";
                if (onlyin)
                    cond += " AND income_expense >= 0 ";
                else if (onlyex)
                    cond += " AND income_expense < 0 ";
                String sql = "SELECT income_expense_time,income_expense FROM income_expense_record WHERE " + cond + "ORDER BY income_expense_time";
                ResultSet rs = stmt.executeQuery(sql);
                System.out.println(begin + "~" + stop + "之间的记录：");
                System.out.println("   时间， 金额");
                int count = 0;
                double sum = 0;
                while (rs.next()) {
                    count++;
                    System.out.println("(" + count + ")" + rs.getDate("income_expense_time") + "， " + rs.getDouble("income_expense"));
                    sum += rs.getDouble("income_expense");
                }
                System.out.println("总金额：" + sum);
                rs.close();
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(+)只显示收入 (-)只显示支出 (*)收入支出都显示 ");
                System.out.println("([)起始日期 (])截止日期");
                System.out.println("(n)新增记录 (d)删除这些记录 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "+":
                        onlyin = true;
                        onlyex = false;
                        break;
                    case "-":
                        onlyin = false;
                        onlyex = true;
                        break;
                    case "*":
                        onlyin = false;
                        onlyex = false;
                        break;
                    case "[":
                        begin = InputDate();
                        break;
                    case "]":
                        stop = InputDate();
                        break;
                    case "n":
                        System.out.println("请输入时间：");
                        Date date = InputDate();
                        System.out.println("请输入金额：");
                        Double doub = InputDouble();
                        try {
                            stmt.execute("INSERT INTO income_expense_record VALUES('" + dept_name + "','" + date + "'," + doub + ")");
                            System.out.println("新增成功");
                        } catch (SQLException se) {
                            System.out.println("新增失败！");
                            System.out.println("报错信息：" + se);
                        }
                        break;
                    case "d":
                        try {
                            stmt.execute("DELETE FROM income_expense_record WHERE " + cond);
                            System.out.println("删除成功");
                            begin = Date.valueOf("2000-01-01");
                            stop = Date.valueOf("2100-12-31");
                        } catch (SQLException se) {
                            System.out.println("删除失败！");
                            System.out.println("报错信息：" + se);
                        }
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void RecessInfo() {
        try {
            boolean back = false;
            ArrayList<String> ID_card_number = new ArrayList<String>();
            ArrayList<Date> start_time = new ArrayList<Date>();
            while (!back) {
                String sql = "SELECT * FROM recess_record NATURAL JOIN staff_member ORDER BY start_time ";
                ResultSet rs = stmt.executeQuery(sql);
                ID_card_number.clear();
                start_time.clear();
                System.out.println("   员工，请假时间");
                int count = 0;
                while (rs.next()) {
                    ID_card_number.add(rs.getString("ID_card_number"));
                    start_time.add(rs.getDate("start_time"));
                    count++;
                    System.out.print("(" + count + ")" + rs.getString("name") + "， " + rs.getDate("start_time") + "~" + rs.getDate("end_time"));
                    if (rs.getBoolean("checked") == false)
                        System.out.println(" *");
                    else
                        System.out.println("");
                }
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(c)查看 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "c":
                        System.out.println("请输入数字以选择您要查看的记录：");
                        int num = InputInt();
                        if (num <= 0 || count < num)
                            System.out.println("没有该选项！");
                        else
                            RecessAdmit(ID_card_number.get(num - 1), start_time.get(num - 1));
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void RecessAdmit(String ID_card_number, Date start_time) {
        try {
            stmt.execute("UPDATE recess_record SET checked = true WHERE ID_card_number = '" + ID_card_number + "' AND start_time = '" + start_time + "'");
            boolean back = false;
            while (!back) {
                String sql = "SELECT * FROM recess_record NATURAL JOIN staff_member WHERE ID_card_number = '" + ID_card_number + "' AND start_time = '" + start_time + "'";
                ResultSet rs = stmt.executeQuery(sql);
                rs.next();
                System.out.println("员工：" + rs.getString("name"));
                System.out.println("请假时间：" + rs.getDate("start_time") + "~" + rs.getDate("end_time"));
                System.out.println("请假原因：" + rs.getString("reason"));
                System.out.println("是否批准：" + rs.getBoolean("admitted"));
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(y)批准 (n)驳回 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "y":
                        stmt.execute("UPDATE recess_record SET admitted = true WHERE ID_card_number = '" + ID_card_number + "' AND start_time = '" + start_time + "'");
                        break;
                    case "n":
                        stmt.execute("UPDATE recess_record SET admitted = false WHERE ID_card_number = '" + ID_card_number + "' AND start_time = '" + start_time + "'");
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void Self() {
        Boolean back = false;
        try {
            while (!back) {
                String sql = "SELECT * FROM staff_member WHERE ID_card_number = '" + userID + "'";
                ResultSet rs = stmt.executeQuery(sql);
                rs.next();
                System.out.println("身份证号：" + rs.getString(1));
                System.out.println("部门：" + rs.getString(2));
                System.out.println("上级的身份证号：" + rs.getString(3));
                System.out.println("姓名：" + rs.getString(4));
                System.out.println("性别：" + rs.getString(5));
                System.out.println("出生日期：" + rs.getDate(6));
                System.out.println("就职日期：" + rs.getDate(7));
                System.out.println("职位：" + rs.getString(8));
                System.out.println("工资：" + rs.getString(9));
                System.out.println("密码：" + rs.getString(10));
                System.out.println("居住地：" + rs.getString(12));
                System.out.println("电话号码：" + rs.getString(13));
                System.out.println("email地址：" + rs.getString(14));
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(w)修改密码 (r)修改居住地 (p)修改电话号码 (e)修改email地址 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "w":
                        System.out.println("请输入密码：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET password = '" + cmd + "' WHERE ID_card_number = '" + userID + "'");
                        System.out.println("修改成功");
                        break;
                    case "r":
                        System.out.println("请输入居住地：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET residence_address = '" + cmd + "' WHERE ID_card_number = '" + userID + "'");
                        System.out.println("修改成功");
                        break;
                    case "p":
                        System.out.println("请输入电话号码：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET phone_number = '" + cmd + "' WHERE ID_card_number = '" + userID + "'");
                        System.out.println("修改成功");
                        break;
                    case "e":
                        System.out.println("请输入email地址：");
                        cmd = in.nextLine();
                        stmt.executeUpdate("UPDATE staff_member SET email_address = '" + cmd + "' WHERE ID_card_number = '" + userID + "'");
                        System.out.println("修改成功");
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

    public static void Recess() {
        try {
            boolean back = false;
            while (!back) {
                String sql = "SELECT * FROM recess_record WHERE ID_card_number = '" + userID + "' ORDER BY start_time ";
                ResultSet rs = stmt.executeQuery(sql);
                System.out.println("   请假时间，是否被批准");
                int count = 0;
                while (rs.next()) {
                    count++;
                    System.out.print("(" + count + ")" + rs.getDate("start_time") + "~" + rs.getDate("end_time") + "，");
                    if (rs.getBoolean("admitted"))
                        System.out.println("已被批准");
                    else
                        System.out.println("未被批准");
                }
                System.out.println("请选择您想要执行的操作：");
                System.out.println("(n)新增申请 (b)返回");
                String cmd = in.nextLine();
                switch (cmd) {
                    case "n":
                        System.out.println("请输入起始时间：");
                        Date start_time = InputDate();
                        System.out.println("请输入结束时间：");
                        Date end_time = InputDate();
                        System.out.println("请输入请假原因：");
                        String reason = in.nextLine();
                        stmt.execute("INSERT INTO recess_record VALUES('" + userID + "','" + start_time + "','" + end_time + "'," + false + ",'" + reason + "'," + null + ")");
                        break;
                    case "b":
                        back = true;
                        break;
                    default:
                        System.out.println("没有该选项！");
                }
            }
        } catch (SQLException se) {
            System.out.println("报错信息：" + se);
        }
    }

}