228

package source;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.mysql.fabric.jdbc.FabricMySQLDriver;
import com.mysql.jdbc.Connection;

public class PhysSQL_Connection {

    private final String USERNAME = "root";
    private final String PASSWORD = "root";
    private final String URL = "jdbc:mysql://localhost:3306/mydbtest";

    private Connection connection;

    public PhysSQL_Connection() throws SQLException {

        Driver driver = new FabricMySQLDriver();
        DriverManager.registerDriver(driver);

        connection = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD);

    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public boolean authorization(String login, String pass) {
        boolean result = false;
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from usersbd2 where loginu=? and passwordu = ?");
            pstmnt.setString(1, login);
            pstmnt.setString(2, pass);
            ResultSet rs = pstmnt.executeQuery();
            while (rs.next()) {
                String password = rs.getString("passwordu");
                result = password.equals(pass);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getUserID(String login) {

        int id = 0;
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from usersbd2 where loginu=?");
            pstmnt.setString(1, login);

            ResultSet rs = pstmnt.executeQuery();
            while (rs.next()) {
                id = rs.getInt(1);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }
    //
    //
    //

    public void addUser(String surn, String name, String fname, String group) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "INSERT INTO potentialusers (surnpu, namepu, fnamepu, grouppu) VALUES (?, ?, ?, ?)");
            pstmnt.setString(1, surn);
            pstmnt.setString(2, name);
            pstmnt.setString(3, fname);
            pstmnt.setString(4, group);
            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void delUser(String login) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from usersbd2 where loginu=?");
            pstmnt.setString(1, login);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getPU() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from potentialusers");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getPUonID(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from potentialusers where idpotentialusers=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void addPUseronId(String surn, String name, String fname, String group, int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "UPDATE potentialusers set surnpu=?, namepu=?, fnamepu=?, grouppu=? WHERE idpotentialusers=?");
            pstmnt.setString(1, surn);
            pstmnt.setString(2, name);
            pstmnt.setString(3, fname);
            pstmnt.setString(4, group);
            pstmnt.setInt(5, id);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void delPUser(int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from potentialusers where idpotentialusers=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getSubj() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from subject");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void addSubj(String name, String descr) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("INSERT INTO subject (nameSubject, descSubject) VALUES (?, ?)");
            pstmnt.setString(1, name);
            pstmnt.setString(2, descr);

            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getSubjonID(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from subject where idsubject=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void delSubj(int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from subject where idsubject=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addSubjonId(String name, String descr, int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE subject set nameSubject=?, descSubject=? WHERE idsubject=?");
            pstmnt.setString(1, name);
            pstmnt.setString(2, descr);

            pstmnt.setInt(3, id);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getLevel() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from level");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public boolean addLevel(int num, String descr) {

        boolean reslt = false;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("INSERT INTO level (idlevel, lvlDescr) VALUES (?, ?)");
            pstmnt.setInt(1, num);
            pstmnt.setString(2, descr);

            if (!pstmnt.execute())
                reslt = true;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("lul:" + reslt);
        return reslt;
    }

    public void delLvl(int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from level where idlevel=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addLvlonId(int num, String descr) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE level set lvlDescr=? WHERE idlevel=?");

            pstmnt.setString(1, descr);
            pstmnt.setInt(2, num);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getLvlonID(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from level where idlevel=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUser() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from usersbd2");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void delUsr(int id) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from usersbd2 where idu=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addTheme(String name, String descr, int idS) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("INSERT INTO theme (nameTheme, descTheme, IdSubject) VALUES (?, ?, ?)");
            pstmnt.setString(1, name);
            pstmnt.setString(2, descr);
            pstmnt.setInt(3, idS);

            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getSubjonName(String name) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from subject where nameSubject=?");
            pstmnt.setString(1, name);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getTheme() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from theme");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getThemeonName(String name) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from theme where nameTheme=?");
            pstmnt.setString(1, name);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public int getThemesCount() {
        int count = 0;
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from theme");

            rs = pstmnt.executeQuery();

            while (rs.next()) {
                count++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("COUNT:" + count);

        return count;

    }

    public void delTheme(String name) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from theme where nameTheme=?");
            pstmnt.setString(1, name);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addThemeonName(String name, String descr, int idSu, String OLDname) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("UPDATE theme set nameTheme=?, descTheme=?, IdSubject=? WHERE nameTheme=?");
            pstmnt.setString(1, name);
            pstmnt.setString(2, descr);
            pstmnt.setInt(3, idSu);
            pstmnt.setString(4, OLDname);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addThemeonName2(String name, String descr, int idSu) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("UPDATE theme set nameTheme=?, descTheme=?, IdSubject=? WHERE nameTheme=?");
            pstmnt.setString(2, name);
            pstmnt.setString(3, descr);
            pstmnt.setInt(3, idSu);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getSubjectViaTheme(String name) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select * from subject,theme where subject.idSubject = theme.IdSubject AND theme.nameTheme=?");
            pstmnt.setString(1, name);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void addQuestion(String TextQuestion, int idLvl, int idTheme) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("INSERT INTO question (TextQuestion, idLevel, IdTheme) VALUES (?, ?, ?)");
            pstmnt.setString(1, TextQuestion);
            pstmnt.setInt(2, idLvl);
            pstmnt.setInt(3, idTheme);

            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public int getIDQuestiononText(String Que) {
        ResultSet rs = null;
        int res = 0;
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select idQuestion from question where TextQuestion=?");
            pstmnt.setString(1, Que);
            rs = pstmnt.executeQuery();

            while (rs.next()) {

                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return res;

    }

    public ResultSet getQuestiononText(String Que) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from question where TextQuestion=?");
            pstmnt.setString(1, Que);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void addAnswer(String TextAnsw, int isCor, int idQuest, int num) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("INSERT INTO answer (textAnswer, IsCorect, IdQuestion,Num) VALUES (?, ?, ?, ?)");
            pstmnt.setString(1, TextAnsw);
            pstmnt.setInt(2, isCor);
            pstmnt.setInt(3, idQuest);
            pstmnt.setInt(4, num);
            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getQuestion() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select * from question, answer where question.idQuestion=answer.IdQuestion ");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getQuestionInTest(int IDtheme) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select * from question, answer where question.idQuestion=answer.IdQuestion and question.IdTheme=?");
            pstmnt.setInt(1, IDtheme);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getQuestionInTest2(int IDtheme) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from question where question.IdTheme=?");
            pstmnt.setInt(1, IDtheme);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getAnswerInQuest(int IDQuest, int num) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from answer where IdQuestion=? and Num=? ");
            pstmnt.setInt(1, IDQuest);
            pstmnt.setInt(2, num);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void AnswerUpdate(String TextAnsw, int isCor, int idQuest, int num) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("UPDATE answer set textAnswer=?, IsCorect=? WHERE IdQuestion=? and Num=?");
            pstmnt.setString(1, TextAnsw);
            pstmnt.setInt(2, isCor);
            pstmnt.setInt(3, idQuest);
            pstmnt.setInt(4, num);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void QuestionUpdate(String TextQuestion, int idLvl, int idQ) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE question set TextQuestion=?, idLevel=? WHERE idQuestion=?");
            pstmnt.setString(1, TextQuestion);
            pstmnt.setInt(2, idLvl);
            pstmnt.setInt(3, idQ);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void delAnswer(int idQ) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from answer where IdQuestion=?");
            pstmnt.setInt(1, idQ);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void delQuestion(int idQ) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("delete from question where idQuestion=?");
            pstmnt.setInt(1, idQ);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public boolean registrationSQL(String name, String surname, String fathername, String group, String login,
            String pasw) throws SQLException {

        String query = "select * from potentialusers where surnpu=? and namepu = ? and fnamepu = ? and grouppu = ?";
        String delete = "delete from potentialusers where surnpu=? and namepu = ? and fnamepu = ? and grouppu = ?";

        PreparedStatement PrepStat = null;

        PrepStat = connection.prepareStatement(query);

        PrepStat.setString(1, surname);
        PrepStat.setString(2, name);
        PrepStat.setString(3, fathername);
        PrepStat.setString(4, group);

        ResultSet rs = PrepStat.executeQuery();

        int count = 0;

        while (rs.next()) {

            count++;

        }

        if (count > 0) {

            PrepStat = connection.prepareStatement(
                    "INSERT INTO usersbd2 (surnameu, nameu, fnameu, groupu, loginu, passwordu,Info) VALUES (?, ?, ?, ?, ?, ?,?)");

            PrepStat.setString(1, surname);
            PrepStat.setString(2, name);
            PrepStat.setString(3, fathername);
            PrepStat.setString(4, group);
            PrepStat.setString(5, login);
            PrepStat.setString(6, pasw);
            PrepStat.setString(7, "");
            PrepStat.execute();

        }

        PrepStat = connection.prepareStatement(delete);

        PrepStat.setString(1, surname);
        PrepStat.setString(2, name);
        PrepStat.setString(3, fathername);
        PrepStat.setString(4, group);

        if (PrepStat.executeUpdate() == 1) {
            ResultSet res = getUseronLog(login);
            while (res.next()) {
                addLog("Користувач " + login + " (" + surname + " " + name + " " + fathername + ") зареєструвався",
                        res.getInt(1));
            }

            return true;
        }

        return false;
    }

    public ResultSet getThemeonId(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from theme where idTheme=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void UpdThemeTry(int id, int tr) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE theme set try=? WHERE idTheme=?");
            pstmnt.setInt(1, tr);
            pstmnt.setInt(2, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getQuestiononId(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("select * from question where idQuestion=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getTest(int idT, int idU) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("SELECT * FROM themeuser where idThm=? and idUsr=?");
            pstmnt.setInt(1, idT);
            pstmnt.setInt(2, idU);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void addTest(int tryN, int idT, int idU) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("INSERT INTO themeuser (tryleft, idThm, idUsr) VALUES (?, ?, ?)");
            pstmnt.setInt(1, tryN);
            pstmnt.setInt(2, idT);
            pstmnt.setInt(3, idU);
            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void decTryTest(int tryN, int idTest) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE themeuser set tryleft=? WHERE idTU=?");
            pstmnt.setInt(1, tryN);
            pstmnt.setInt(2, idTest);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void UpdTest(int mar, int idTest) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("UPDATE themeuser set mark=? WHERE idTU=?");
            pstmnt.setInt(1, mar);
            pstmnt.setInt(2, idTest);
            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getStudMarks(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu, usersbd2.loginu, themeuser.mark, theme.nameTheme from usersbd2,themeuser,theme where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme "
                            + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getSubjThemes(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT question.TextQuestion,question.idLevel,theme.nameTheme,subject.nameSubject FROM question,theme,subject where question.IdTheme=theme.idTheme and theme.IdSubject=subject.idSubject "
                            + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getLikeUser(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("SELECT * FROM usersbd2 " + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getLikeTheme(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select theme.nameTheme, subject.nameSubject, theme.try from theme,subject where theme.IdSubject=subject.idSubject "
                            + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet betwLvl(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT question.TextQuestion, theme.nameTheme,level.idLevel,level.lvlDescr FROM question, theme, level where question.IdTheme=theme.idTheme and question.idLevel=level.idLevel  "
                            + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getMaxBalForTest(String theme) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select theme.nameTheme, sum(question.idLevel) from theme,question where theme.idTheme=question.IdTheme and theme.nameTheme=? ");
            pstmnt.setString(1, theme);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet betwBal(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select usersbd2.surnameu, usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu, themeuser.mark from usersbd2,themeuser,theme where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme  "
                            + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getThemesInSubject(String subj) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select theme.nameTheme from theme,subject where theme.IdSubject=subject.idSubject and subject.nameSubject = ?  ");
            pstmnt.setString(1, subj);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public int countThemes(String subj) {
        ResultSet rs = null;
        int col = 0;
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select count(*) from theme,subject where theme.IdSubject=subject.idSubject and subject.nameSubject = ?  ");
            pstmnt.setString(1, subj);
            rs = pstmnt.executeQuery();

            while (rs.next()) {
                col = rs.getInt(1);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return col;

    }

    public ResultSet getCountLevelThemes() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "Select level.idLevel, level.lvlDescr, count(*) from level,question where level.idLevel=question.idLevel group by level.idLevel");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUserMaxTest() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu, count(*)  from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu having count(*)>=all(select count(*) from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu)");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUserwithoutTest(String theme) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT usersbd2.surnameu, usersbd2.nameu,usersbd2.fnameu,usersbd2.groupu,usersbd2.loginu from usersbd2 where loginu not in(select usersbd2.loginu from usersbd2,themeuser,theme where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme and theme.nameTheme=? group by usersbd2.idu)");
            pstmnt.setString(1, theme);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getTestsSubject(String subj) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT usersbd2.surnameu, usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu, theme.nameTheme, themeuser.mark from usersbd2,themeuser,theme,subject where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme and subject.idSubject=theme.IdSubject and subject.nameSubject=?  ");
            pstmnt.setString(1, subj);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public int countTests(String subj) {
        ResultSet rs = null;
        int col = 0;
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT count(*) from usersbd2,themeuser,theme,subject where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme and subject.idSubject=theme.IdSubject and subject.nameSubject=? ");
            pstmnt.setString(1, subj);
            rs = pstmnt.executeQuery();

            while (rs.next()) {
                col = rs.getInt(1);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return col;

    }

    public ResultSet getCountThemesQuest() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "Select theme.nameTheme,count(*) from theme, question where theme.idTheme=question.IdTheme group by theme.nameTheme");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getThemeMaxTry() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select theme.nameTheme, theme.try from theme group by theme.nameTheme having theme.try>=all(select theme.try from theme group by theme.nameTheme)");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getThemewithoutQuest() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT theme.nameTheme,subject.nameSubject from theme,subject where theme.IdSubject=subject.idSubject and theme.nameTheme not in(SELECT theme.nameTheme from theme,subject,question where theme.IdSubject=subject.idSubject and theme.idTheme=question.IdTheme group by theme.nameTheme)");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUseronID(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("SELECT * FROM usersbd2 where usersbd2.idu=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUseronLog(String log) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("SELECT * FROM usersbd2 where usersbd2.loginu=?");
            pstmnt.setString(1, log);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUserStatsOnId(int id) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT theme.nameTheme,subject.nameSubject,themeuser.mark,themeuser.tryleft FROM usersbd2,theme,themeuser,subject where usersbd2.idu=themeuser.idUsr and themeuser.idThm=theme.idTheme and theme.IdSubject=subject.idSubject and usersbd2.idu=?");
            pstmnt.setInt(1, id);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getThemeGreaterQuest(int numQ) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select theme.nameTheme from theme where (select count(*) from question where question.IdTheme=theme.idTheme group by theme.nameTheme)>?");
            pstmnt.setInt(1, numQ);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getNameTheme() {
        ResultSet rs = null;

        Statement pstmnt = null;
        try {
            pstmnt = connection.createStatement();

            rs = pstmnt.executeQuery("select theme.nameTheme from theme");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUserLessTest(int numT) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "SELECT * from usersbd2 where (select count(*) from themeuser where usersbd2.idu= themeuser.idUsr group by usersbd2.idu)<? or (select count(*) from themeuser where usersbd2.idu= themeuser.idUsr group by usersbd2.idu) is null");
            pstmnt.setInt(1, numT);
            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUnionStud() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu,'Пройдено найбільше тестів' as 'Info' from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu having count(*)>=all(select count(*) from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu) union select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu,'Пройдено найменше тестів' as 'Info' from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu having count(*)<=all(select count(*) from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu) union select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu,'Не пройдено жодного тесту' from usersbd2 where (select count(*) from themeuser where usersbd2.idu= themeuser.idUsr group by usersbd2.idu) is null");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUnionLvl() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select level.idLevel,level.lvlDescr,'Питань з таким рівнем найбільше' from level,question where level.idLevel=question.idLevel group by level.idLevel having count(*)>=all(select count(*) from level,question where level.idLevel=question.idLevel group by level.idLevel) union select level.idLevel,level.lvlDescr,'Питань з таким рівнем найменше' from level,question where level.idLevel=question.idLevel group by level.idLevel having count(*)<=all(select count(*) from level,question where level.idLevel=question.idLevel group by level.idLevel) union select level.idLevel,level.lvlDescr,'Питання такого рівня відсутні' from level where (select count(*) from question where level.idLevel= question.idLevel group by level.idLevel) is Null ");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public ResultSet getUserMaxTest2() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu,'Пройдено найбільше тестів' as 'Info' from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu having count(*)>=all(select count(*) from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu)");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void UpdateInfoMax(int id) {

        // c
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "UPDATE usersbd2 set usersbd2.Info='' WHERE usersbd2.Info='Пройшов найбільше тестів'");
            pstmnt.executeUpdate();

            pstmnt = connection.prepareStatement(
                    "UPDATE usersbd2 set usersbd2.Info='Пройшов найбільше тестів' WHERE usersbd2.idu=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public ResultSet getUserMinTest() {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "select usersbd2.idu, usersbd2.surnameu,usersbd2.nameu, usersbd2.fnameu, usersbd2.groupu,usersbd2.loginu,'Пройдено найменше тестів' as 'Info' from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu having count(*)<=all(select count(*) from usersbd2, themeuser where usersbd2.idu=themeuser.idUsr group by usersbd2.idu)");

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    public void UpdateInfoMin(int id) {

        // c
        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement(
                    "UPDATE usersbd2 set usersbd2.Info='' WHERE usersbd2.Info='Пройшов найменше тестів'");
            pstmnt.executeUpdate();

            pstmnt = connection.prepareStatement(
                    "UPDATE usersbd2 set usersbd2.Info='Пройшов найменше тестів' WHERE usersbd2.idu=?");
            pstmnt.setInt(1, id);

            pstmnt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void addLog(String ActionU, int isUsr) {

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection
                    .prepareStatement("INSERT INTO log_journal (dateA, timeA, action,idUserA) VALUES (?, ?, ?, ?)");
            pstmnt.setString(1, nowDate());
            pstmnt.setString(2, nowTime());
            pstmnt.setString(3, ActionU);
            pstmnt.setInt(4, isUsr);
            pstmnt.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public String nowDate() {

        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
        Date date = new Date();
        String sDate = dateFormat.format(date);
        return sDate;
    }

    public String nowTime() {
        DateFormat dateFormat = new SimpleDateFormat("HH:mm:ss");
        Date date = new Date();
        String sTime = dateFormat.format(date);
        return sTime;
    }

    public ResultSet getLog(String part) {
        ResultSet rs = null;

        PreparedStatement pstmnt = null;
        try {
            pstmnt = connection.prepareStatement("SELECT * FROM log_journal" + part);

            rs = pstmnt.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

}