import com.square.WeakLinkServer.Game;
import com.square.WeakLinkServer.Preferences;
import com.square.WeakLinkServer.Printer;
import com.square.WeakLinkServer.Question;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Random;
public class QuestionManager {
private Game currentGame = null;
private static String user = Preferences.Database.user;
private static String password = Preferences.Database.password;
private static String url = Preferences.Database.url;
private static String driver = Preferences.Database.driver;
private ArrayList<Question> gameQuestions;
private int currentQuestion = 0;
private int[] questionOrder;
private ArrayList gamePlayers;
public void swap(int[] arr, int i, int j) {
int t = arr[i];
arr[i] = arr[j];
arr[j] = t;
}
public void bubbleSort(int[] arr, int[] key){
for(int i = arr.length-1 ; i > 0 ; i--){
for(int j = 0 ; j < i ; j++){
if( arr[j] > arr[j+1] )
swap(arr, j, j+1);
swap(key, j, j+1);
}
}
}
public void finalOn(){
//***
gamePlayers = new ArrayList(currentGame.getAllActivePlayers().keySet);
ArrayList<String> PlayerQuestions = getPlayerQuestions(gamePlayers);
try {
Class.forName(driver); //Регистрируем драйвер
} catch (ClassNotFoundException exc) {
Printer.exception("Exception in finalOn (driver)", exc);
}
Connection con = null;//Соединение с БД
try{
con = DriverManager.getConnection(url, user, password);//Установка соединения с БД
Statement st = con.createStatement();//Готовим запрос
ResultSet rs = st.executeQuery("SELECT qsn_id FROM Questions WHERE qsn_type = 1;");
ResultSet rs2 = st.executeQuery("SELECT qsn_id FROM Questions ORDER BY qsn_id DESC LIMIT 1;");
int qsnLength = rs2.getInt(1) + 1;
int[] qsn = new int[qsnLength];
int[] qsnKey = new int[qsnLength];
for(int i = 1; i <= qsnKey.length; i++)
qsnKey[i] = i;
while(rs.next())
qsn[rs.getInt(1)] = -100;
String[] s;
for(int i = 1; i <= PlayerQuestions.size(); i++){
s = PlayerQuestions.get(i).split(",");
for (int j = 0; j < s.length; j++){
qsn[Integer.valueOf(s[j])]++;
}
}
bubbleSort(qsn, qsnKey);
int i = 1;
String str = "(";
while(qsn[i] <= qsn[50] && i <= 100){
str = str + qsnKey[i] + ",";
i++;
}
str = str.substring(0, str.length()-2) + ")";
PreparedStatement pst = con.prepareStatement("SELECT qsn_id, gsn_content FROM Questions WHERE qsn_type = 0 AND qsn_id IN ?;");
pst.setString(1, str);
rs = pst.executeQuery();
JSONParser parser=new JSONParser();
String[] ans = new String[3];
while(rs.next()){
Object obj = parser.parse(rs.getString(2));
JSONObject json = (JSONObject) obj;
Question qsnTemp = new Question();
qsnTemp.question = json.get(qsn).toString();
qsnTemp.correct = 0;
ans[0] = json.get(1).toString();
ans[1] = json.get(2).toString();
ans[2] = json.get(3).toString();
ans[3] = json.get(4).toString();
qsnTemp.answers = ans;
qsnTemp.questionID = rs.getInt(1);
gameQuestions.add(qsnTemp);
}
Random rand = new Random();
questionOrder = new int[gameQuestions.size()];
for(i = 0; i < gameQuestions.size(); i++)
questionOrder[i] = i;
for(i = 0; i < gameQuestions.size(); i++)
swap(questionOrder, i, rand.nextInt(i));
} catch(Exception exc){
Printer.exception("Exception in finalOn (connection)", exc);
}
finally{
try {
if(con != null)
con.close();
} catch (SQLException exc) {
Printer.exception("Exception in finalOn (close connection)", exc);
}
}
}
public QuestionManager(Game game){
//***
gamePlayers = new ArrayList(game.getAllActivePlayers().keySet());
ArrayList<String> PlayerQuestions = getPlayerQuestions(gamePlayers);
try {
Class.forName(driver); //Регистрируем драйвер
} catch (ClassNotFoundException exc) {
Printer.exception("Exception in QuestionManager constructor (driver)", exc);
}
Connection con = null;//Соединение с БД
try{
con = DriverManager.getConnection(url, user, password);//Установка соединения с БД
Statement st = con.createStatement();//Готовим запрос
ResultSet rs = st.executeQuery("SELECT qsn_id FROM Questions WHERE qsn_type = 0;");
ResultSet rs2 = st.executeQuery("SELECT qsn_id FROM Questions ORDER BY qsn_id DESC LIMIT 1;");
int qsnLength = rs2.getInt(1) + 1;
int[] qsn = new int[qsnLength];
int[] qsnKey = new int[qsnLength];
for(int i = 1; i <= qsnKey.length; i++)
qsnKey[i] = i;
while(rs.next())
qsn[rs.getInt(1)] = -8;
String[] s;
for(int i = 1; i <= PlayerQuestions.size(); i++){
s = PlayerQuestions.get(i).split(",");
for (int j = 0; j < s.length; j++){
qsn[Integer.valueOf(s[j])]++;
}
}
bubbleSort(qsn, qsnKey);
int i = 1;
String str = "(";
while(qsn[i] <= qsn[50] && i <= 100){
str = str + qsnKey[i] + ",";
i++;
}
str = str.substring(0, str.length()-2) + ")";
PreparedStatement pst = con.prepareStatement("SELECT qsn_id, gsn_content FROM Questions WHERE qsn_type = 0 AND qsn_id IN ?;");
pst.setString(1, str);
rs = pst.executeQuery();
JSONParser parser=new JSONParser();
String[] ans = new String[3];
while(rs.next()){
Object obj = parser.parse(rs.getString(2));
JSONObject json = (JSONObject) obj;
Question qsnTemp = new Question();
qsnTemp.question = json.get(qsn).toString();
qsnTemp.correct = 0;
ans[0] = json.get(1).toString();
ans[1] = json.get(2).toString();
ans[2] = json.get(3).toString();
qsnTemp.answers = ans;
qsnTemp.questionID = rs.getInt(1);
gameQuestions.add(qsnTemp);
}
Random rand = new Random();
questionOrder = new int[gameQuestions.size()];
for(i = 0; i < gameQuestions.size(); i++)
questionOrder[i] = i;
for(i = 0; i < gameQuestions.size(); i++)
swap(questionOrder, i, rand.nextInt(i));
} catch(Exception exc){
Printer.exception("Exception in QuestionManager constructor (connection)", exc);
}
finally{
try {
if(con != null)
con.close();
} catch (SQLException exc) {
Printer.exception("Exception in QuestionManager constructor (close connection)", exc);
}
}
}
public ArrayList<String> getPlayerQuestions(ArrayList<Integer> PlayersID){
ArrayList<String> PlayerQuestions = new ArrayList();
try {
Class.forName(driver);//Регистрируем драйвер
} catch (ClassNotFoundException exc) {
Printer.exception("Exception in getPlayerQuestions (driver)", exc);
}
Connection con = null;//Соединение с БД
try{
con = DriverManager.getConnection(url, user, password);//Установка соединения с БД
Statement st = con.createStatement();//Готовим запрос
for(int i = 0; i < PlayersID.size(); i++) {
PreparedStatement pst = con.prepareStatement("select pl_qsn from players where pl_id = ?");//? - это параметр
pst.setInt(1, PlayersID.get(i));//установление значения параметра. Обратите внимание: нумерация параметров начинается не с 0, а с 1!
ResultSet prs = pst.executeQuery();
while(prs.next())
PlayerQuestions.add(prs.getString(1));//Последовательно для каждой строки выводим значение из колонки ColumnName
}
} catch(Exception exc){
Printer.exception("Exception in getPlayerQuestions (connection)", exc);
}
finally{
try {
if(con != null)
con.close();
} catch (SQLException exc) {
Printer.exception("Exception in getPlayerQuestions (close connection)", exc);
}
}
return PlayerQuestions;
}
public void setQuestions(String filePath){
try {
Class.forName(driver);//Регистрируем драйвер
} catch (ClassNotFoundException exc) {
Printer.exception("Exception in setQuestions (driver)", exc);
}
Connection con = null;//Соединение с БД
try{
con = DriverManager.getConnection(url, user, password);//Установка соединения с БД
File f = new File(filePath);
char[] CB = new char[(int) f.length()];
Reader reader = new InputStreamReader(new FileInputStream(f), "UTF-8");
reader.read(CB);
String s = new String(CB);
String[] file = s.split("\\r\\n");
int i = 0;
String number = file[0];
while(i < file.length) {
if(number.charAt(0) != 'Ф'){
JSONObject json = new JSONObject();
json.put("qsn", file[i + 1]);
json.put("1", file[i + 2]);
json.put("2", file[i + 3]);
json.put("3", file[i + 4]);
PreparedStatement pst = con.prepareStatement("INSERT INTO Questions (qsn_number, qsn_content, qsn_type) VALUES (?, ?, ?)");
pst.setInt(3, 0);
pst.setInt(1, Integer.parseInt(number));
pst.setString(2, json.toJSONString());
pst.executeUpdate();
if(i + 6 < file.length) {
number = file[i + 6];
i = i + 6;
}
else
i = i + 7;
}
else{
JSONObject json = new JSONObject();
json.put("qsn", file[i + 1]);
json.put("1", file[i + 2]);
json.put("2", file[i + 3]);
json.put("3", file[i + 4]);
json.put("4", file[i + 5]);
PreparedStatement pst = con.prepareStatement("INSERT INTO Questions (qsn_number, qsn_content, qsn_type) VALUES (?, ?, ?)");
pst.setInt(3, 1);
number = number.substring(1);
pst.setInt(1, Integer.parseInt(number));
pst.setString(2, json.toJSONString());
pst.executeUpdate();
if(i + 7 < file.length) {
number = file[i + 7];
i = i + 7;
}
else
i = i + 8;
}
}
} catch(Exception exc){
Printer.exception("Exception in setQuestions (connection)", exc);
}
finally{
try {
if(con != null)
con.close();
} catch (SQLException exc) {
Printer.exception("Exception in setQuestions (close connection)", exc);
}
}
}
public Question nextQuestion(){
try {
Class.forName(driver);//Регистрируем драйвер
} catch (ClassNotFoundException exc) {
Printer.exception("Exception in nextQuestion (driver)", exc);
}
Connection con = null;//Соединение с БД
try{
con = DriverManager.getConnection(url, user, password);//Установка соединения с БД
Statement st = con.createStatement();//Готовим запрос
//***
PreparedStatement pst;
gamePlayers = new ArrayList(currentGame.getAllActivePlayers().keySet());
for(int i = 0; i <= gamePlayers.size(); i++){
pst = con.prepareStatement("UPDATE Players SET pl_qsn = pl_qsn + ?");
pst.setString(1,"," + gameQuestions.get(questionOrder[currentQuestion]).questionID);
pst.executeUpdate();
}
} catch(Exception exc){
Printer.exception("Exception in nextQuestion (connection)", exc);
}
finally{
try {
if(con != null)
con.close();
} catch (SQLException exc) {
Printer.exception("Exception in nextQuestion (close connection)", exc);
}
}
currentQuestion++;
return gameQuestions.get(questionOrder[currentQuestion - 1]);
}
}