12.30

package com.example.ourhomework1.dao;

import com.example.ourhomework1.pojo.*;
import com.example.ourhomework1.utils.DbOpenHelper;

import java.lang.Record;
import java.util.ArrayList;
import java.util.List;

public class UserDao extends DbOpenHelper {
    public int addStudent(Student student) {

        int iRow = 0;
        try {
            getConnection();
            String sql = "insert into student values(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, student.getId());
            preparedStatement.setString(2, student.getName());
            preparedStatement.setString(3, student.getPhone());
            preparedStatement.setString(4, student.getStudentClass());
            preparedStatement.setString(5, student.getPassword());
            iRow = preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return iRow;
    }

    public int addRecord(Records records) {
        int iRow = 0;
        try {
            getConnection();
            String sql = "insert into records values(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, records.getId());
            preparedStatement.setString(2, records.getDate());
            preparedStatement.setString(3, records.getStartTime());
            preparedStatement.setString(4, records.getEndTime());
            preparedStatement.setString(5, records.getRecord());
            iRow = preparedStatement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return iRow;

    }

    public String login(int id) {
        String password = "";
        try {
            getConnection();
            String sql = "select password from student where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                password = resultSet.getString("password");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return password;
    }

    public String teacherLogin(int id) {
        String password = "";
        try {
            getConnection();
            String sql = "select password from teacher where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                password = resultSet.getString("password");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return password;
    }

    public String dailyRecord(int id) {
        String password = "";
        try {
            getConnection();
            String sql = "select password from teacher where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                password = resultSet.getString("password");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return password;
    }

    public int addObject(Objectives objectives) {

        int iRow = 0;
        try {
            getConnection();
            String sql = "insert into objectives(id,objective,isFinished) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, objectives.getId());
            preparedStatement.setString(2, objectives.getObjective());
            preparedStatement.setInt(3, objectives.getIsFinished());
            iRow = preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return iRow;
    }

    public int analysis(Analysis analysis) {

        int iRow = 0;
        try {
            getConnection();
            String sql = "insert into analysis(id,analysis,nextWeek) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, analysis.getId());
            preparedStatement.setString(2, analysis.getAnalysis());
            preparedStatement.setString(3, analysis.getNextWeek());
            iRow = preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return iRow;
    }

    public int getCount(int id) {
        int count = 0;

        try {
            getConnection();
            String sql = "select count(*) as count from objectives where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    public int getIsFinished(int id) {
        int isFinished = 0;
        int num = 0;
        int average = 0;
        try {
            getConnection();
            String sql = "select isFinished from objectives where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int temp = resultSet.getInt("isFinished");
                num++;
                isFinished += temp;
            }
            average = isFinished / num;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return average;
    }

    public int getDays(int id) {
        int days = 0;

        try {
            getConnection();
            String sql = "select count(*) as count from records where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                days = resultSet.getInt("count");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return days;
    }

    public List<Records> getAllRecords() {
        List<Records> allRecords = new ArrayList<>();
        try {
            getConnection();
            String sql = "select * from records ";
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Records record = new Records();
                record.setId(resultSet.getInt("id"));
                record.setDate(resultSet.getString("date"));
                record.setStartTime(resultSet.getString("startTime"));
                record.setEndTime(resultSet.getString("endTime"));
                record.setRecord(resultSet.getString("record"));

                allRecords.add(record);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return allRecords;
    }


    public List<Records> getDailyRecords(String search) {
        List<Records> allRecords = new ArrayList<>();
        try {
            getConnection();
            String sql = "select * from records where record like concat('%',?,'%')";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, search);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Records record = new Records();
                record.setId(resultSet.getInt("id"));
                record.setDate(resultSet.getString("date"));
                record.setStartTime(resultSet.getString("startTime"));
                record.setEndTime(resultSet.getString("endTime"));
                record.setRecord(resultSet.getString("record"));

                allRecords.add(record);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return allRecords;
    }


    public List<Information> getInformation() {


        List<Information> allRecords = new ArrayList<>();
        try {
            getConnection();
            String sql = "select " +
                    "student.*,count(records.id) as count " +
                    "from student left join records " +
                    "on student.id = records.id " +
                    "group by student.id";
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Information information = new Information();
                information.setId(resultSet.getInt("student.id"));
                information.setName(resultSet.getString("student.name"));
                information.setStudentClass(resultSet.getString("student.studentClass"));
                information.setCount(resultSet.getInt("count"));
                allRecords.add(information);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return allRecords;
    }

    /*public AllAnalysis getAllAnalysis(int id) {
        AllAnalysis allAnalysis = new AllAnalysis();
        allAnalysis.setId(id);
        allAnalysis.setRecordsNumber(getCount(id));
        allAnalysis.setAverage(getIsFinished(id));
        allAnalysis.setDayNumber(getDays(id));
        return allAnalysis;
    }*/
}