API tools faq
paste
Guest User

Untitled

a guest
Nov 22nd, 2016
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 72.34 KB | None | 0 0
raw download clone embed print report
  1. package com.enmaka.matistikk.repository;
  2.  
  3. import com.enmaka.matistikk.mappers.UserMapper;
  4. import com.enmaka.matistikk.mappers.TaskMapper;
  5. import com.enmaka.matistikk.mappers.TaskInfoExtractor;
  6. import com.enmaka.matistikk.mappers.TestMapper;
  7. import com.enmaka.matistikk.mappers.TeacherMapper;
  8. import com.enmaka.matistikk.mappers.StudentMapper;
  9. import com.enmaka.matistikk.mappers.StudentInfoMapper;
  10. import com.enmaka.matistikk.mappers.SchoolInfoMapper;
  11. import com.enmaka.matistikk.mappers.TaskAnswerExtractor;
  12. import com.enmaka.matistikk.mappers.TaskSolutionExtractor;
  13. import com.enmaka.matistikk.mappers.TaskInfoMapper;
  14. import com.enmaka.matistikk.mappers.AnswerMapper;
  15. import com.enmaka.matistikk.mappers.TeacherStatisticsExtractor;
  16. import com.enmaka.matistikk.mappers.ClassInfoMapper;
  17. import com.enmaka.matistikk.mappers.TeacherInfoMapper;
  18. import com.enmaka.matistikk.mappers.TestInfoMapper;
  19. import com.enmaka.matistikk.mappers.TaskStatisticsMapper;
  20. import com.enmaka.matistikk.mappers.TestInfoExtractor;
  21. import com.enmaka.matistikk.mappers.AnswerStatisticsMapper;
  22. import com.enmaka.matistikk.mappers.TestStatisticsMapper;
  23. import com.enmaka.matistikk.email.Mail;
  24. import com.enmaka.matistikk.objects.*;
  25. import com.enmaka.matistikk.service.*;
  26. import com.enmaka.matistikk.users.*;
  27. import java.io.BufferedReader;
  28. import java.io.BufferedWriter;
  29. import java.io.Console;
  30. import java.io.File;
  31. import java.io.FileInputStream;
  32. import java.io.FileNotFoundException;
  33. import java.io.FileOutputStream;
  34. import java.io.FileReader;
  35. import java.io.FileWriter;
  36. import java.io.IOException;
  37. import java.io.InputStream;
  38. import java.io.OutputStream;
  39. import static java.lang.System.console;
  40. import java.sql.Connection;
  41. import java.sql.PreparedStatement;
  42. import java.sql.ResultSet;
  43. import java.sql.SQLException;
  44. import java.sql.Statement;
  45. import java.util.ArrayList;
  46. import java.util.List;
  47. import javax.sql.DataSource;
  48. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  49. import org.apache.poi.ss.usermodel.Cell;
  50. import org.apache.poi.ss.usermodel.Row;
  51. import org.apache.poi.ss.usermodel.Sheet;
  52. import org.apache.poi.ss.usermodel.Workbook;
  53. import org.springframework.context.annotation.Bean;
  54. import org.springframework.context.annotation.ComponentScan;
  55. import org.springframework.context.annotation.Configuration;
  56. import org.springframework.jdbc.core.JdbcTemplate;
  57. import org.springframework.jdbc.datasource.DriverManagerDataSource;
  58. import org.springframework.jdbc.support.rowset.SqlRowSet;
  59. import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
  60. import org.springframework.web.servlet.config.annotation.EnableWebMvc;
  61. import org.zeroturnaround.zip.ZipUtil;
  62. import org.zeroturnaround.zip.commons.FileUtils;
  63.  
  64. /**
  65. *
  66. * @author Team ENMAKA
  67. *
  68. * Klassen tar for seg kommunikasjon mellom systemet og databasen.
  69. *
  70. * For mer informasjon om klassen, se designdokumentet kapittel 4.8.2.
  71. */
  72. @Configuration
  73. @EnableWebMvc
  74. @ComponentScan(basePackages = {"com.enmaka.matistikk.controllers"})
  75. public class DatabaseRepository implements UserRepository {
  76.  
  77. //Her finner man alle SQL-setningene som blir brukt av metodene til å hente, sette inn og oppdatere informasjon i databasen
  78. private final String sqlAddUser = "INSERT INTO USERS VALUES (?, ?, ?, ?)";
  79. private final String sqlAddStudentInfo = "INSERT INTO STUDENT_INFO VALUES (?, ?, ?, ?)";
  80. private final String sqlAddTeacherInfo = "INSERT INTO TEACHER_INFO (EMAIL_FK, FIRSTNAME, LASTNAME, SCHOOL_ID) VALUES(?, ?, ?, ?)";
  81. private final String sqlSelectAllStudents = "SELECT SI.EMAIL_FK, COUNT(TS.TEST_ID) AS TEST_COUNT, SI.AGE, SI.SEX, S.SCHOOL_NAME, SC.CLASS_NAME, U.ACTIVE FROM STUDENT_INFO SI INNER JOIN USERS U ON SI.EMAIL_FK = U.EMAIL "
  82. + "LEFT JOIN SCHOOL_CLASS SC ON SI.CLASS_ID = SC.CLASS_ID INNER JOIN SCHOOL S ON SC.SCHOOL_ID = S.SCHOOL_ID "
  83. + "LEFT JOIN TEST_STUDENT TS ON SI.EMAIL_FK = TS.EMAIL_FK GROUP BY SI.EMAIL_FK, SI.AGE, SI.SEX, S.SCHOOL_NAME, SC.CLASS_NAME, U.ACTIVE";
  84. private final String sqlGetPassword = "SELECT PASSWORD FROM USERS WHERE EMAIL = ?";
  85. private final String sqlChangePassword = "UPDATE USERS SET PASSWORD = ? WHERE EMAIL = ?";
  86. private final String sqlFindEmail = "SELECT COUNT(*) FROM USERS WHERE EMAIL = ?";
  87. private final String sqlFindUser = "SELECT EMAIL,PASSWORD,DESCRIPTION FROM USERS WHERE EMAIL = ? AND PASSWORD = ? AND ACTIVE = TRUE";
  88. private final String sqlSelectAllTeachers = "SELECT TI.EMAIL_FK, TI.FIRSTNAME, TI.LASTNAME, COUNT(T.TEST_ID) AS TEST_COUNT, S.SCHOOL_NAME FROM USERS U LEFT JOIN TEST T ON U.EMAIL=T.EMAIL_FK "
  89. + "INNER JOIN TEACHER_INFO TI ON U.EMAIL=TI.EMAIL_FK INNER JOIN SCHOOL S ON TI.SCHOOL_ID=S.SCHOOL_ID "
  90. + "WHERE U.DESCRIPTION='Teacher' GROUP BY TI.FIRSTNAME, TI.LASTNAME, TI.EMAIL_FK, S.SCHOOL_NAME";
  91. private final String sqlSelectTaskInfo = "SELECT T.TASK_ID, T.TASK_TYPE, T.TEXT, FT.NUMERATOR, FT.DENOMINATOR, T.EMAIL_FK FROM TASK T LEFT JOIN FRACTION_TASK FT ON T.TASK_ID = FT.TASK_ID WHERE T.TESTABLE = ?";
  92. private final String sqlSelectTaskInfoId = "SELECT T.TASK_ID, T.TASK_TYPE, T.TEXT, FT.NUMERATOR, FT.DENOMINATOR, T.EMAIL_FK FROM TASK T LEFT JOIN FRACTION_TASK FT ON T.TASK_ID = FT.TASK_ID WHERE T.TASK_ID = ?";
  93. private final String sqlSelectStudentInfo = "SELECT * FROM STUDENT_INFO WHERE EMAIL_FK = ?";
  94. private final String sqlSelectTeacherInfo = "SELECT TI.*, U.ACTIVE FROM TEACHER_INFO TI INNER JOIN USERS U ON TI.EMAIL_FK = U.EMAIL WHERE TI.EMAIL_FK = ?";
  95. private final String sqlAddAnswer = "INSERT INTO ANSWER(TEST_ID, TASK_ID, TOTAL_TIME, CORRECT, EMAIL_FK, EXPLENATION) VALUES (?, ?, ?, ?, ?, ?)";
  96. private final String sqlAddFractionAnswer = "INSERT INTO FRACTION_ANSWER(ANSWER_ID, NUMERATOR, DENOMINATOR) VALUES (?, ?, ?)";
  97. private final String sqlAddStringAnswer = "INSERT INTO STRING_ANSWER(ANSWER_ID, URL) VALUES (?, ?)";
  98. private final String sqlSelectAnswer = "SELECT A.*, T.TASK_TYPE FROM ANSWER A INNER JOIN TASK T ON A.TASK_ID = T.TASK_ID AND A.EMAIL_FK = ? AND A.TEST_ID = ? AND A.TASK_ID = ?";
  99. private final String sqlSelectFractionAnswer = "SELECT * FROM FRACTION_ANSWER WHERE ANSWER_ID = ?";
  100. private final String sqlSelectStringAnswer = "SELECT URL FROM STRING_ANSWER WHERE ANSWER_ID = ?";
  101. private final String sqlAddTask = "INSERT INTO TASK(TASK_TYPE, TEXT, TESTABLE, EMAIL_FK) VALUES (?, ?, ?, ?)";
  102. private final String sqlAddFractionTask = "INSERT INTO FRACTION_TASK(TASK_ID, NUMERATOR, DENOMINATOR) VALUES (?, ?, ?)";
  103. private final String sqlAddStringTask = "INSERT INTO STRING_TASK(TASK_ID, URL) VALUES (?, ?)";
  104. private final String sqlAddFractionSolution = "INSERT INTO FRACTION_SOLUTION(TASK_ID, NUMERATOR, DENOMINATOR) VALUES (?, ?, ?)";
  105. private final String sqlAddStringSolution = "INSERT INTO STRING_SOLUTION(TASK_ID, URL) VALUES (?, ?)";
  106. private final String sqlSelectTask = "SELECT * FROM TASK WHERE TASK.TASK_ID = ?";
  107. private final String sqlSelectFractionSolution = "SELECT NUMERATOR, DENOMINATOR FROM FRACTION_SOLUTION WHERE TASK_ID = ?";
  108. private final String sqlSelectStringSolution = "SELECT URL FROM STRING_SOLUTION WHERE TASK_ID = ?";
  109. private final String sqlSelectStringTask = "SELECT URL FROM STRING_TASK WHERE TASK_ID = ?";
  110. private final String sqlSelectFractionTask = "SELECT NUMERATOR, DENOMINATOR FROM FRACTION_TASK WHERE TASK_ID = ?";
  111. private final String sqlSelectTeacherClasses = "SELECT CLASS_ID FROM TEACHER_CLASS WHERE EMAIL_FK = ?";
  112. private final String sqlSelectTests = "SELECT TE.TEST_ID, TE.EMAIL_FK, TS.PROGRESS, COUNT(TA.TASK_ID) AS TASKS FROM TEST TE INNER JOIN TEST_TASK TT ON TE.TEST_ID = TT.TEST_ID INNER JOIN TASK TA ON TT.TASK_ID = TA.TASK_ID LEFT JOIN "
  113. + "(SELECT * FROM TEST_STUDENT WHERE EMAIL_FK = ?) TS ON TE.TEST_ID = TS.TEST_ID INNER JOIN TEST_CLASS TC ON TE.TEST_ID = TC.TEST_ID WHERE TE.TEST_ID NOT IN(SELECT TEST_ID FROM TEST_STUDENT TS WHERE"
  114. + " TS.EMAIL_FK = ? AND TS.COMPLETED = TRUE) AND TC.CLASS_ID = ? AND ACTIVE = TRUE AND TE.TESTABLE = TRUE GROUP BY TE.TEST_ID, TE.EMAIL_FK, TS.PROGRESS";
  115.  
  116. private final String sqlSelectPracticeTests = "SELECT TE.TEST_ID, TE.EMAIL_FK, TS.PROGRESS, COUNT(TA.TASK_ID) AS TASKS FROM TEST TE INNER JOIN TEST_TASK TT ON TE.TEST_ID = TT.TEST_ID INNER JOIN TASK TA ON TT.TASK_ID = TA.TASK_ID LEFT JOIN "
  117. + "(SELECT * FROM TEST_STUDENT WHERE EMAIL_FK = ?) TS ON TE.TEST_ID = TS.TEST_ID INNER JOIN TEST_CLASS TC ON TE.TEST_ID = TC.TEST_ID WHERE TE.TEST_ID NOT IN(SELECT TEST_ID FROM TEST_STUDENT TS WHERE"
  118. + " TS.EMAIL_FK = ? AND TS.COMPLETED = TRUE) AND TC.CLASS_ID = ? AND ACTIVE = TRUE AND TE.TESTABLE = FALSE GROUP BY TE.TEST_ID, TE.EMAIL_FK, TS.PROGRESS";
  119. private final String sqlSelectTestTask = "SELECT * FROM TEST_TASK WHERE TEST_ID = ?";
  120. private final String sqlSelectTest = "SELECT * FROM TEST WHERE TEST_ID = ?";
  121. private final String sqlAddTest = "INSERT INTO TEST(EMAIL_FK, ACTIVE, TESTABLE) VALUES (?, ?, ?)";
  122. private final String sqlAddTestTask = "INSERT INTO TEST_TASK VALUES (?, ?)";
  123. private final String sqlSelectTestUsers = "SELECT A.EMAIL_FK, SUM(A.TOTAL_TIME) AS TEST_TIME FROM ANSWER A INNER JOIN TASK TA ON A.TASK_ID = TA.TASK_ID INNER JOIN TEST_TASK TT ON TA.TASK_ID = TT.TASK_ID WHERE TT.TEST_ID = ? AND A.TEST_ID = TT.TEST_ID GROUP BY A.EMAIL_FK";
  124. private final String sqlSelectTestUsersResult = "SELECT TT.TASK_ID, A.ANSWER_ID, A.EMAIL_FK, A.CORRECT FROM TEST_STUDENT TS INNER JOIN TEST T ON T.TEST_ID = TS.TEST_ID INNER JOIN TEST_TASK TT ON "
  125. + "T.TEST_ID = TT.TEST_ID LEFT JOIN (SELECT * FROM ANSWER WHERE EMAIL_FK = ?) A ON TT.TASK_ID = A.TASK_ID WHERE TS.TEST_ID = T.TEST_ID AND TT.TEST_ID = ? GROUP BY TT.TASK_ID, A.ANSWER_ID, A.EMAIL_FK, A.CORRECT";
  126. private final String sqlSelectTaskSolution = "SELECT TA.TASK_ID, TA.TEXT, TA.TASK_TYPE, FS.NUMERATOR, FS.DENOMINATOR FROM TASK TA LEFT JOIN FRACTION_SOLUTION FS ON TA.TASK_ID = FS.TASK_ID INNER JOIN TEST_TASK TT ON TA.TASK_ID = TT.TASK_ID WHERE TT.TEST_ID = ?";
  127. private final String sqlSelectTaskSummary = "SELECT A.ANSWER_ID, A.EMAIL_FK, TA.TASK_TYPE, FA.NUMERATOR, FA.DENOMINATOR, A.CORRECT, A.EXPLENATION, A.TOTAL_TIME FROM ANSWER A LEFT JOIN FRACTION_ANSWER FA ON A.ANSWER_ID = FA.ANSWER_ID INNER JOIN TASK TA "
  128. + "ON A.TASK_ID = TA.TASK_ID INNER JOIN TEST_TASK TT ON TA.TASK_ID = TT.TASK_ID INNER JOIN TEST TE ON TT.TEST_ID = TE.TEST_ID INNER JOIN TEST_STUDENT TS ON TE.TEST_ID = TS.TEST_ID WHERE TS.TEST_ID = ? AND TT.TASK_ID = ? AND A.EMAIL_FK = TS.EMAIL_FK";
  129. private final String sqlAddTestStudent = "INSERT INTO TEST_STUDENT(TEST_ID, EMAIL_FK, COMPLETED, PROGRESS) VALUES (?, ?, ?, ?)";
  130. private final String sqlUpdateTestStudent = "UPDATE TEST_STUDENT SET PROGRESS = ? WHERE TEST_ID = ? AND EMAIL_FK = ?";
  131. private final String sqlUpdateTestStudentComplete = "UPDATE TEST_STUDENT SET COMPLETED = ?, PROGRESS = ? WHERE TEST_ID = ? AND EMAIL_FK = ?";
  132. private final String sqlAddDrawing = "INSERT INTO DRAWING(ANSWER_ID, FILE_PATH) VALUES (?, ?)";
  133. private final String sqlCheckAnswer = "SELECT ANSWER_ID FROM ANSWER WHERE TEST_ID = ? AND TASK_ID = ? AND EMAIL_FK = ?";
  134. private final String sqlUpdateFractionAnswer = "UPDATE FRACTION_ANSWER SET NUMERATOR = ?, DENOMINATOR = ? WHERE ANSWER_ID = ?";
  135. private final String sqlUpdateAnswer = "UPDATE ANSWER SET TOTAL_TIME = ?, CORRECT = ?, EXPLENATION = ? WHERE ANSWER_ID = ?";
  136. private final String sqlSelectFractionAnswerId = "SELECT FRACTION_ANSWER_ID FROM FRACTION_ANSWER WHERE ANSWER_ID = ?";
  137. private final String sqlUpdateFractionAnswers = "UPDATE FRACTION_ANSWER SET NUMERATOR = ?, DENOMINATOR = ? WHERE FRACTION_ANSWER_ID = ?";
  138. private final String sqlUpdateStringAnswer = "UPDATE STRING_ANSWER SET URL = ? WHERE ANSWER_ID = ?";
  139. private final String sqlSelectCountTestsTaken = "SELECT COUNT(TS.EMAIL_FK) AS ANTALL_TESTER FROM TEST_STUDENT TS, TEST T WHERE TS.TEST_ID = T.TEST_ID AND TS.EMAIL_FK = ? AND TS.COMPLETED = ? AND T.TESTABLE = ?";
  140. private final String sqlSelectCountTestsMade = "SELECT COUNT(T.EMAIL_FK) AS ANTALL_TESTER FROM TEST T WHERE T.EMAIL_FK = ?";
  141. private final String sqlSelectStatistics = "SELECT T.TEST_ID, T.EMAIL_FK, (SELECT COUNT(*) FROM TEST_TASK TT WHERE TT.TEST_ID = T.TEST_ID) TASK_COUNT, (SELECT COUNT(*) FROM TEST_STUDENT TS WHERE TS.TEST_ID = T.TEST_ID) STUDENT_COUNT, "
  142. + "(SELECT COUNT(*) FROM TEST_TEACHER TET WHERE TET.TEST_ID = T.TEST_ID) TEACHER_COUNT, T.ACTIVE FROM TEST T WHERE T.TESTABLE = ?";
  143. private final String sqlSelectTestStatistics = "SELECT TA.TASK_ID, COUNT(A.EMAIL_FK) AS USERS, SUM(CASE WHEN A.CORRECT = TRUE THEN 1 ELSE 0 END) AS CORRECT, SUM(CASE WHEN A.CORRECT = FALSE THEN 1 ELSE 0 END) AS WRONG"
  144. + " FROM TASK TA INNER JOIN TEST_TASK TT ON TA.TASK_ID = TT.TASK_ID INNER JOIN ANSWER A ON TA.TASK_ID = A.TASK_ID WHERE TT.TEST_ID = ? AND A.TEST_ID = TT.TEST_ID GROUP BY TA.TASK_ID";
  145. private final String sqlSelectTaskStatistics = "SELECT A.EMAIL_FK, A.CORRECT, A.TOTAL_TIME FROM ANSWER A WHERE A.TEST_ID = ? AND A.TASK_ID = ?";
  146. private final String sqlUpdateClassId = "UPDATE STUDENT_INFO SI SET SI.CLASS_ID = ? WHERE SI.EMAIL_FK = ?";
  147. private final String sqlCheckClass = "SELECT 1 FROM SCHOOL_CLASS WHERE CLASS_ID = ?";
  148. private final String sqlSelectClass = "SELECT SC.CLASS_ID, S.SCHOOL_NAME, SC.CLASS_NAME, COUNT(SI.EMAIL_FK) AS STUDENTS FROM SCHOOL_CLASS SC INNER JOIN SCHOOL S ON S.SCHOOL_ID = SC.SCHOOL_ID INNER JOIN STUDENT_INFO SI ON SC.CLASS_ID = SI.CLASS_ID WHERE SC.CLASS_ID = ? GROUP BY SC.CLASS_ID, S.SCHOOL_NAME, SC.CLASS_NAME";
  149. private final String sqlSelectDrawing = "SELECT FILE_PATH FROM MATISTIKK.DRAWING WHERE ANSWER_ID = ?";
  150. private final String sqlSelectTestInfo = "SELECT T.TEST_ID, T.EMAIL_FK, (SELECT COUNT(*) FROM TEST_TASK TT WHERE TT.TEST_ID = T.TEST_ID) TASKS, T.ACTIVE FROM TEST T WHERE T.TESTABLE = ? AND T.EMAIL_FK = ?";
  151. private final String sqlAddTestTeacher = "INSERT INTO TEST_TEACHER VALUES (?, ?, FALSE)";
  152. private final String sqlAddTeacherClass = "INSERT INTO TEACHER_CLASS VALUES (?, ?)";
  153. private final String sqlSelectTeacherClass = "SELECT SC.CLASS_NAME FROM SCHOOL_CLASS SC INNER JOIN TEACHER_CLASS TC ON SC.CLASS_ID = TC.CLASS_ID WHERE TC.EMAIL_FK = ?";
  154. private final String sqlSelectTestTeacher = "SELECT T.TEST_ID FROM TEST T INNER JOIN TEST_TEACHER TT ON T.TEST_ID = TT.TEST_ID WHERE TT.EMAIL_FK = ?";
  155. private final String sqlSelectClasses = "SELECT SC.CLASS_ID, SC.CLASS_NAME, S.SCHOOL_NAME, COUNT(SI.EMAIL_FK) AS STUDENTS FROM SCHOOL_CLASS SC INNER JOIN SCHOOL S ON S.SCHOOL_ID = SC.SCHOOL_ID LEFT JOIN STUDENT_INFO SI ON SC.CLASS_ID = SI.CLASS_ID WHERE SC.CLASS_ID NOT IN (SELECT CLASS_ID FROM TEACHER_CLASS WHERE EMAIL_FK = ?) AND SC.SCHOOL_ID = ? GROUP BY SC.CLASS_ID, SC.CLASS_NAME, S.SCHOOL_NAME";
  156. private final String sqlSelectTeacher = "SELECT TI.*, U.ACTIVE FROM TEACHER_INFO TI INNER JOIN USERS U ON TI.EMAIL_FK = U.EMAIL WHERE TI.EMAIL_FK = ?";
  157. private final String sqlUpdateUserActive = "UPDATE USERS SET ACTIVE = ? WHERE EMAIL = ?";
  158. private final String sqlSelectTestTeachers = "SELECT TI.EMAIL_FK, TI.FIRSTNAME, TI.LASTNAME, SC.CLASS_NAME, S.SCHOOL_NAME, (SELECT COUNT(TT.TEST_ID) FROM TEST_TEACHER TT WHERE TT.EMAIL_FK = TI.EMAIL_FK) TEST_COUNT FROM TEACHER_INFO TI INNER JOIN TEACHER_CLASS TC ON TI.EMAIL_FK = TC.EMAIL_FK "
  159. + "INNER JOIN SCHOOL_CLASS SC ON TI.SCHOOL_ID = SC.SCHOOL_ID INNER JOIN SCHOOL S ON S.SCHOOL_ID = TI.SCHOOL_ID WHERE TI.EMAIL_FK NOT IN (SELECT EMAIL_FK FROM TEST_TEACHER WHERE TEST_ID = ?) AND SC.CLASS_ID = TC.CLASS_ID";
  160. private final String sqlCheckTests = "SELECT COUNT(*) FROM TEST_TEACHER TT WHERE TT.EMAIL_FK = ? AND TT.PUBLISHED = FALSE";
  161. private final String sqlSelectPublishTests = "SELECT TE.TEST_ID, TE.EMAIL_FK, COUNT(TTA.TASK_ID) AS TASKS, TE.ACTIVE FROM TEST TE INNER JOIN TEST_TEACHER TT ON TE.TEST_ID = TT.TEST_ID INNER JOIN TEST_TASK TTA ON TE.TEST_ID = TTA.TEST_ID WHERE TT.PUBLISHED = FALSE AND TT.EMAIL_FK = ? GROUP BY TE.TEST_ID, TE.EMAIL_FK, TE.ACTIVE";
  162. private final String sqlSelectPublishClasses = "SELECT SC.CLASS_ID, S.SCHOOL_NAME, SC.CLASS_NAME, COUNT(SI.EMAIL_FK) AS STUDENTS FROM SCHOOL_CLASS SC INNER JOIN SCHOOL S ON SC.SCHOOL_ID = S.SCHOOL_ID INNER JOIN TEACHER_CLASS TC ON SC.CLASS_ID = TC.CLASS_ID LEFT JOIN STUDENT_INFO SI ON SC.CLASS_ID = SI.CLASS_ID WHERE TC.CLASS_ID IN (SELECT CLASS_ID FROM TEACHER_CLASS WHERE EMAIL_FK = ?)"
  163. + " AND TC.CLASS_ID NOT IN(SELECT CLASS_ID FROM TEST_CLASS WHERE TEST_ID = ?) AND SC.SCHOOL_ID = ? GROUP BY SC.CLASS_ID, SC.CLASS_NAME, S.SCHOOL_NAME";
  164. private final String sqlAddTestClass = "INSERT INTO TEST_CLASS VALUES(?, ?)";
  165. private final String sqlUpdateTestTeacher = "UPDATE TEST_TEACHER SET PUBLISHED = ? WHERE EMAIL_FK = ? AND TEST_ID = ?";
  166. private final String sqlSelectTestActive = "SELECT ACTIVE FROM TEST WHERE TEST_ID = ?";
  167. private final String sqlUpdateTestActive = "UPDATE TEST SET ACTIVE = ? WHERE TEST_ID = ?";
  168. private final String sqlSelectAllClassTeachers = "SELECT TI.EMAIL_FK, TI.FIRSTNAME, TI.LASTNAME, SC.CLASS_NAME, S.SCHOOL_NAME, (SELECT COUNT(TT.TEST_ID) FROM TEST_TEACHER TT WHERE TT.EMAIL_FK = TI.EMAIL_FK) TEST_COUNT FROM TEACHER_INFO TI INNER JOIN TEACHER_CLASS TC ON TI.EMAIL_FK = TC.EMAIL_FK "
  169. + "INNER JOIN SCHOOL_CLASS SC ON TI.SCHOOL_ID = SC.SCHOOL_ID INNER JOIN SCHOOL S ON S.SCHOOL_ID = TI.SCHOOL_ID WHERE SC.CLASS_ID = TC.CLASS_ID";
  170. private final String sqlSelectAllSchools = "SELECT S.SCHOOL_ID, S.SCHOOL_NAME, (SELECT COUNT(*) FROM SCHOOL_CLASS SC WHERE SC.SCHOOL_ID = S.SCHOOL_ID) AS CLASS_COUNT FROM SCHOOL S";
  171. private final String sqlAddSchool = "INSERT INTO SCHOOL(SCHOOL_NAME) VALUES (?)";
  172. private final String sqlSelectAllClasses = "SELECT SC.CLASS_ID, S.SCHOOL_NAME, SC.CLASS_NAME, (SELECT COUNT(*) FROM STUDENT_INFO SI WHERE SI.CLASS_ID = SC.CLASS_ID) AS STUDENTS FROM SCHOOL_CLASS SC INNER JOIN SCHOOL S ON SC.SCHOOL_ID = S.SCHOOL_ID WHERE SC.SCHOOL_ID = ?";
  173. private final String sqlAddClass = "INSERT INTO SCHOOL_CLASS(CLASS_NAME, SCHOOL_ID) VALUES (?, ?)";
  174. private final String sqlSelectStudentsClass = "SELECT SI.EMAIL_FK, COUNT(TS.TEST_ID) AS TEST_COUNT, SI.AGE, SI.SEX, S.SCHOOL_NAME, SC.CLASS_NAME, U.ACTIVE FROM STUDENT_INFO SI INNER JOIN USERS U ON SI.EMAIL_FK = U.EMAIL "
  175. + "LEFT JOIN SCHOOL_CLASS SC ON SI.CLASS_ID = SC.CLASS_ID INNER JOIN SCHOOL S ON SC.SCHOOL_ID = S.SCHOOL_ID "
  176. + "LEFT JOIN TEST_STUDENT TS ON SI.EMAIL_FK = TS.EMAIL_FK WHERE SI.CLASS_ID = ? GROUP BY SI.EMAIL_FK, SI.AGE, SI.SEX, S.SCHOOL_NAME, SC.CLASS_NAME, U.ACTIVE";
  177. /**
  178. * ******** GRUPPE 6**********
  179. */
  180. private final String sqlSelectFunctionAnswerType = "SELECT ANSWER_TYPE FROM FUNCTION_TASK WHERE TASK_ID = ?";
  181. private final String sqlAddFunctionAnswer = "INSERT INTO FUNCTION_ANSWER(FUNCTION_ANSWER_ID, ANSWER_ID, ANSWER_TEXT, ANSWER_BASE64, ANSWER_GEOLISTENER) VALUES (DEFAULT,?, ?, ?, ?)";
  182. private final String sqlUpdateFunctionAnswer = "UPDATE FUNCTION_ANSWER SET ANSWER_TEXT = ? WHERE ANSWER_ID = ?";
  183. private final String sqlSelectFunctionAnswer = "SELECT ANSWER_TEXT, ANSWER_BASE64, ANSWER_GEOLISTENER FROM FUNCTION_ANSWER WHERE ANSWER_ID = ?";
  184. private final String sqlAddFunctionSolution = "INSERT INTO FUNCTION_SOLUTION(FUNCTION_SOLUTION_ID, TASK_ID, SOLUTION) VALUES(DEFAULT, ?, ?)";
  185. private final String sqlAddFunctionTask = "INSERT INTO FUNCTION_TASK(FUNCTION_TASK_ID, TASK_ID, ANSWER_TYPE, FUNCTION_OPTIONS, CHECKBOX_EXPLANATION, CHECKBOX_DRAWING, URL, FUNCTION_STRING) VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?)";
  186. private final String sqlSelectFunctionOptions = "SELECT FUNCTION_OPTIONS FROM FUNCTION_TASK WHERE TASK_ID = ?";
  187. private final String sqlSelectFunctionCheckboxes = "SELECT CHECKBOX_EXPLANATION, CHECKBOX_DRAWING FROM FUNCTION_TASK WHERE TASK_ID = ?";
  188. private final String sqlSelectFunctionUrl = "SELECT URL FROM FUNCTION_TASK WHERE TASK_ID = ?";
  189. private final String sqlSelectFunctionString = "SELECT FUNCTION_STRING FROM FUNCTION_TASK WHERE TASK_ID = ?";
  190.  
  191.  
  192. JdbcTemplate jdbcTemplate;
  193. public String url = "jdbc:derby://localhost:1527/Matistikk"; //[1]: Her skriver man inn adressen til databasen.
  194. public String username = "matistikk"; //[2]: Databasens brukernavn
  195. public String password = "matistikk"; //[3]: Databasens passord
  196. Connection con;
  197. DriverManagerDataSource dmds;
  198.  
  199. public DatabaseRepository() {
  200. }
  201.  
  202. //Dette er metoden som tar seg av å opprette en forbindelse til databasen
  203. @Bean
  204. public DataSource dataSource() {
  205. dmds = new DriverManagerDataSource(url, username, password);
  206. dmds.setDriverClassName("org.apache.derby.jdbc.ClientDriver"); //[4]: Driveren som skal tas i bruk
  207. try {
  208. con = dmds.getConnection(); //Oppretter forbindelsen
  209. this.jdbcTemplate = new JdbcTemplate(dmds);
  210. } catch (Exception e) {
  211. }
  212. return dmds;
  213. }
  214.  
  215. //Denne metoden konfigurerer DefaultServletHandling
  216. public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
  217. configurer.enable();
  218. }
  219.  
  220. //Denne metoden oppretter en instans av UserServiceImpl
  221. @Bean
  222. public UserService studentService() {
  223. return new UserServiceImpl();
  224. }
  225.  
  226. //Denne metoden legger til en student
  227. @Override
  228. public boolean addStudent(Student student) {
  229. String password = Mail.generate(); //Genererer et passord for studenten
  230. String description = "Student";
  231. Integer classId = null;
  232. int i = jdbcTemplate.update(sqlAddUser,
  233. new Object[]{student.getUsername(),
  234. password,
  235. description,
  236. true});
  237. int j = jdbcTemplate.update(sqlAddStudentInfo,
  238. new Object[]{student.getUsername(),
  239. student.age,
  240. student.sex,
  241. classId});
  242. if (i > 0 && j > 0) {
  243. Mail.sendEmail(student.getUsername(), password); //Sender passord via e-post til studentens e-postadresse
  244. return true;
  245. }
  246. return false;
  247. }
  248.  
  249. //Denne metoden legger til en lærer
  250. @Override
  251. public boolean addTeacher(Teacher teacher) {
  252. String password = Mail.generate(); //Genererer et passord for læreren
  253. String description = "Teacher";
  254. int i = jdbcTemplate.update(sqlAddUser,
  255. new Object[]{teacher.getUsername(),
  256. password,
  257. description,
  258. true});
  259. int j = jdbcTemplate.update(sqlAddTeacherInfo,
  260. new Object[]{teacher.getUsername(),
  261. teacher.getFirstName(),
  262. teacher.getLastName(),
  263. teacher.getSchoolId()});
  264. if (i > 0 && j > 0) {
  265. Mail.sendEmail(teacher.getUsername(), password); //Sender passord via e-post til studentens e-postadresse
  266. return true;
  267. }
  268. return false;
  269. }
  270.  
  271. //Denne metoden tar seg av innloggingen til en bruker
  272. @Override
  273. public User login(String username, String password) {
  274. User u;
  275. try {
  276. u = jdbcTemplate.queryForObject(sqlFindUser, new Object[]{username, password}, new UserMapper()); //Sjekker om brukeren med oppgitt brukernavn og passord finnes
  277. if (u instanceof Student) {
  278. u = (Student) jdbcTemplate.queryForObject(sqlSelectStudentInfo, new Object[]{u.getUsername()}, new StudentMapper());
  279. }
  280. if (u instanceof Teacher) {
  281. u = (Teacher) jdbcTemplate.queryForObject(sqlSelectTeacherInfo, new Object[]{u.getUsername()}, new TeacherMapper());
  282. }
  283. } catch (Exception e) {
  284. u = null;
  285. }
  286. return u;
  287. }
  288.  
  289. //Denne metoden henter ut alle studentene i databasen
  290. @Override
  291. public List<StudentInfo> getAllStudents() {
  292. return jdbcTemplate.query(sqlSelectAllStudents, new StudentInfoMapper());
  293. }
  294.  
  295. //Denne metoden henter ut alle lærerne i databasen
  296. @Override
  297. public List<TeacherInfo> getAllTeachers() {
  298. return jdbcTemplate.query(sqlSelectAllTeachers, new TeacherInfoMapper());
  299. }
  300.  
  301. //Denne metoden henter ut en lærers informasjon basert på innsendt e-postadresse
  302. @Override
  303. public Teacher getTeacher(String email) {
  304. return (Teacher) jdbcTemplate.queryForObject(sqlSelectTeacher, new Object[]{email}, new TeacherMapper());
  305. }
  306.  
  307. //Denne metoden genererer et nytt passord for brukeren med innsendt e-postadresse
  308. @Override
  309. public boolean forgotPassword(String username) {
  310. String password = Mail.generate(); //Genererer et passord for brukeren
  311. int i = jdbcTemplate.update(sqlChangePassword, new Object[]{password, username});
  312. if (i > 0) {
  313. Mail.sendEmail(username, password); //Sender e-posten til brukeren
  314. return true;
  315. } else {
  316. return false;
  317. }
  318. }
  319.  
  320. //Denne metoden sjekker om det finnes en bruker med innsendt e-postadresse
  321. @Override
  322. public boolean findEmail(String username) {
  323. int i = jdbcTemplate.queryForObject(sqlFindEmail, new Object[]{username}, Integer.class);
  324. if (i > 0) {
  325. return true;
  326. }
  327. return false;
  328. }
  329.  
  330. //Denne metoden endrer passord for innsendt bruker
  331. @Override
  332. public boolean changePassword(String newPassword, User user) {
  333. int i = jdbcTemplate.update(sqlChangePassword, new Object[]{newPassword, user.getUsername()});
  334. return i == 1;
  335. }
  336.  
  337. //Denne metoden henter ut passordet til en bruker med innsendt e-postadresse
  338. @Override
  339. public String getPassword(String username) {
  340. String password = jdbcTemplate.queryForObject(sqlGetPassword, new Object[]{username}, String.class);
  341. return password;
  342. }
  343.  
  344. //Denne metoden legger til en besvarelse
  345. @Override
  346. public boolean addAnswer(Test test) {
  347. try {
  348. Answer answer = test.getCurrentTask().getAnswer();
  349. PreparedStatement check = con.prepareStatement(sqlCheckAnswer);
  350. check.setInt(1, test.getId());
  351. check.setInt(2, test.getCurrentTask().getId());
  352. check.setString(3, answer.getEmail());
  353. ResultSet res = check.executeQuery(); //Sjekker her om oppgaven allerede er besvart
  354. if (res != null && res.next()) { //Hvis oppgaven allerede er besvart må vi oppdatere besvarelsen
  355. int id = res.getInt("answer_id");
  356. int i = jdbcTemplate.update(sqlUpdateAnswer, new Object[]{answer.getTime(), answer.isCorrect(), answer.getExplenation(), id}); //Oppdaterer besvarelsen
  357. if (i == 0) {
  358. return false;
  359. }
  360. //Sjekker hvilken type oppgave besvarelsen tilhører
  361. if (answer instanceof AnswerSingleFraction) {
  362. i = jdbcTemplate.update(sqlUpdateFractionAnswer, new Object[]{((AnswerSingleFraction) answer).getValue().getNumerator(), ((AnswerSingleFraction) answer).getValue().getDenominator(), id}); //Må oppdatere brøken assosiert med besvarelsen
  363. if (i == 0) {
  364. return false;
  365. }
  366. } else if (answer instanceof AnswerMultipleFractions) {
  367. SqlRowSet srs = jdbcTemplate.queryForRowSet(sqlSelectFractionAnswerId, new Object[]{id});
  368. List<Integer> answerIds = new ArrayList<>();
  369. while (srs.next()) {
  370. answerIds.add(srs.getInt("fraction_answer_id"));
  371. }
  372. for (int j = 0; j < ((AnswerMultipleFractions) answer).getValue().length; j++) {
  373. i = jdbcTemplate.update(sqlUpdateFractionAnswers, new Object[]{((AnswerMultipleFractions) answer).getValue()[j].getNumerator(), ((AnswerMultipleFractions) answer).getValue()[j].getDenominator(), answerIds.get(j)});
  374. if (i == 0) {
  375. return false;
  376. }
  377. }
  378.  
  379. } else if (answer instanceof AnswerString) {
  380. i = jdbcTemplate.update(sqlUpdateStringAnswer, new Object[]{((AnswerString) answer).getValue(), id});
  381. if (i == 0) {
  382. return false;
  383. }
  384. } else if (answer instanceof AnswerFunction) {
  385. i = jdbcTemplate.update(sqlUpdateFunctionAnswer, new Object[]{((AnswerFunction) answer).getValue(), id});
  386. if (i == 0) {
  387. return false;
  388. }
  389. }
  390. addCoordinates(test.getId(), test.getCounter() + 1, id, answer.getEmail(), answer.getCoordinates()); //Legger til koordinatene for besvarelsen
  391. i = 0;
  392. if (!test.isStarted()) {
  393. i = jdbcTemplate.update(sqlAddTestStudent, new Object[]{test.getId(), answer.getEmail(), false, test.getCounter() + 1});
  394. } else if (test.getCounter() == (test.getLength() - 1)) {
  395. i = jdbcTemplate.update(sqlUpdateTestStudentComplete, new Object[]{true, test.getCounter() + 1, test.getId(), answer.getEmail()});
  396. } else if (test.getCounter() > 0 && test.getCounter() < (test.getLength() - 1)) {
  397. i = jdbcTemplate.update(sqlUpdateTestStudent, new Object[]{test.getCounter() + 1, test.getId(), answer.getEmail()});
  398. }
  399. if (i == 0) {
  400. return false;
  401. }
  402.  
  403. return true;
  404.  
  405. } else {
  406. PreparedStatement prep = con.prepareStatement(sqlAddAnswer, Statement.RETURN_GENERATED_KEYS);
  407. prep.setInt(1, test.getId());
  408. prep.setInt(2, test.getCurrentTask().getId());
  409. prep.setDouble(3, answer.getTime());
  410. prep.setBoolean(4, answer.isCorrect());
  411. prep.setString(5, answer.getEmail());
  412. prep.setString(6, answer.getExplenation());
  413. prep.execute();
  414. res = prep.getGeneratedKeys();
  415. if (res != null && res.next()) {
  416. answer.setId(res.getInt(1));
  417. }
  418. if (answer.getTaskId() == 0) {
  419. return false;
  420. }
  421. //Sjekker hvilken type oppgave besvarelsen tilhører
  422. if (answer instanceof AnswerSingleFraction) {
  423. int i = jdbcTemplate.update(sqlAddFractionAnswer, new Object[]{answer.getId(), ((AnswerSingleFraction) answer).getValue().getNumerator(), ((AnswerSingleFraction) answer).getValue().getDenominator()});
  424. if (i == 0) {
  425. return false;
  426. }
  427. } else if (answer instanceof AnswerMultipleFractions) {
  428. for (int j = 0; j < ((AnswerMultipleFractions) answer).getValue().length; j++) {
  429. int i = jdbcTemplate.update(sqlAddFractionAnswer, new Object[]{answer.getId(), ((AnswerMultipleFractions) answer).getValue()[j].getNumerator(), ((AnswerMultipleFractions) answer).getValue()[j].getDenominator()});
  430. if (i == 0) {
  431. return false;
  432. }
  433. }
  434.  
  435. } else if (answer instanceof AnswerString) {
  436. int i = jdbcTemplate.update(sqlAddStringAnswer, new Object[]{answer.getId(), ((AnswerString) answer).getValue()});
  437. if (i == 0) {
  438. return false;
  439. }
  440. } else if (answer instanceof AnswerFunction) {
  441. int i = jdbcTemplate.update(sqlAddFunctionAnswer, new Object[]{answer.getId(), ((AnswerFunction) answer).getValue(), ((AnswerFunction) answer).getGeoBase64(), ((AnswerFunction) answer).getGeoListener()});
  442. if (i == 0) {
  443. return false;
  444. }
  445. }
  446. addCoordinates(test.getId(), test.getCounter() + 1, answer.getId(), answer.getEmail(), answer.getCoordinates());
  447. int i = 0;
  448. if (test.getCounter() == 0) {
  449. boolean b = test.getLength() == 1;
  450. i = jdbcTemplate.update(sqlAddTestStudent, new Object[]{test.getId(), answer.getEmail(), b, test.getCounter() + 1});
  451. } else if (test.getCounter() == (test.getLength() - 1)) {
  452. i = jdbcTemplate.update(sqlUpdateTestStudentComplete, new Object[]{true, test.getCounter() + 1, test.getId(), answer.getEmail()});
  453. } else if (test.getCounter() > 0 && test.getCounter() < (test.getLength() - 1)) {
  454. i = jdbcTemplate.update(sqlUpdateTestStudent, new Object[]{test.getCounter() + 1, test.getId(), answer.getEmail()});
  455. }
  456. if (i == 0) {
  457. return false;
  458. }
  459. }
  460. return true;
  461. } catch (Exception e) {
  462. }
  463. return false;
  464. }
  465.  
  466. //Denne metoden henter ut en besvarelse basert på innsendt e-postadrese, en tests id og en oppgaves id
  467. @Override
  468. public Answer getAnswer(String email, int testId, int taskId) {
  469.  
  470. ResultSet res;
  471. Answer answer = null;
  472. SqlRowSet srs;
  473. try {
  474. answer = jdbcTemplate.queryForObject(sqlSelectAnswer, new Object[]{email, testId, taskId}, new AnswerMapper());
  475. //Sjekker hvilken type oppgave besvarelsen tilhører
  476. if (answer instanceof AnswerSingleFraction) {
  477. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionAnswer, new Object[]{answer.getId()});
  478. Fraction f = null;
  479. while (srs.next()) {
  480. f = new Fraction(srs.getInt("numerator"), srs.getInt("denominator"));
  481. }
  482. ((AnswerSingleFraction) answer).setValue(f);
  483.  
  484. } else if (answer instanceof AnswerMultipleFractions) {
  485. ArrayList<Fraction> fractionList = new ArrayList<>();
  486. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionAnswer, new Object[]{answer.getId()});
  487. while (srs.next()) {
  488. Fraction f = new Fraction(srs.getInt("numerator"), srs.getInt("denominator"));
  489. fractionList.add(f);
  490. }
  491. Fraction[] fractions = new Fraction[fractionList.size()];
  492. fractions = fractionList.toArray(fractions);
  493. ((AnswerMultipleFractions) answer).setFractions(fractions);
  494. } else if (answer instanceof AnswerString) {
  495. String s = null;
  496. srs = jdbcTemplate.queryForRowSet(sqlSelectStringAnswer, new Object[]{answer.getId()});
  497. while (srs.next()) {
  498. s = srs.getString("url");
  499. }
  500. ((AnswerString) answer).setValue(s);
  501. } /**
  502. * ***** GRUPPE 6 *****
  503. */
  504. else if (answer instanceof AnswerFunction) {
  505. String s = null;
  506. String a = null;
  507. String g = null;
  508. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionAnswer, new Object[]{answer.getId()});
  509. while (srs.next()) {
  510. s = srs.getString("answer_text");
  511. a = srs.getString("answer_base64");
  512. g = srs.getString("answer_geolistener");
  513. }
  514. ((AnswerFunction) answer).setValue(s);
  515. ((AnswerFunction) answer).setGeoBase64(a);
  516. ((AnswerFunction) answer).setGeoListener(g);
  517. }
  518.  
  519. } catch (Exception e) {
  520. }
  521. return answer;
  522. }
  523.  
  524. //Denne metoden legger til en oppgave
  525. @Override
  526. public boolean addTask(Task task, boolean type) {
  527. try {
  528. PreparedStatement prep = con.prepareStatement(sqlAddTask, Statement.RETURN_GENERATED_KEYS);
  529. ResultSet res;
  530. //Sjekker hvilken oppgavetype oppgaven er
  531. if (task instanceof Arithmetic) {
  532. prep.setInt(1, 1);
  533. prep.setString(2, task.getText());
  534. prep.setBoolean(3, type);
  535. prep.setString(4, task.getUsername());
  536. prep.execute();
  537. res = prep.getGeneratedKeys();
  538. if (res != null && res.next()) {
  539. task.setId(res.getInt(1));
  540. }
  541. if (task.getId() == 0) {
  542. return false;
  543. }
  544. Fraction f = ((Arithmetic) task).getSolution();
  545. int i = jdbcTemplate.update(sqlAddFractionSolution, new Object[]{task.getId(), f.getNumerator(), f.getDenominator()});
  546. if (i != 0) {
  547. return true;
  548. }
  549. } else if (task instanceof SingleChoice) {
  550. prep.setInt(1, 2);
  551. prep.setString(2, task.getText());
  552. prep.setBoolean(3, type);
  553. prep.setString(4, task.getUsername());
  554. prep.execute();
  555. res = prep.getGeneratedKeys();
  556. if (res != null && res.next()) {
  557. task.setId(res.getInt(1));
  558. }
  559. if (task.getId() == 0) {
  560. return false;
  561. }
  562. for (int i = 0; i < ((SingleChoice) task).getChoices().length; i++) {
  563. int j = jdbcTemplate.update(sqlAddFractionTask, new Object[]{task.getId(), ((SingleChoice) task).getChoices()[i].getNumerator(), ((SingleChoice) task).getChoices()[i].getDenominator()});
  564. if (j == 0) {
  565. return false;
  566. }
  567. }
  568. Fraction f = ((SingleChoice) task).getSolution();
  569. int i = jdbcTemplate.update(sqlAddFractionSolution, new Object[]{task.getId(), f.getNumerator(), f.getDenominator()});
  570. if (i != 0) {
  571. return true;
  572. }
  573. } else if (task instanceof Sort) {
  574. prep.setInt(1, 3);
  575. prep.setString(2, task.getText());
  576. prep.setBoolean(3, type);
  577. prep.setString(4, task.getUsername());
  578. prep.execute();
  579. res = prep.getGeneratedKeys();
  580. if (res != null & res.next()) {
  581. task.setId(res.getInt(1));
  582. }
  583. if (task.getId() == 0) {
  584. return false;
  585. }
  586. for (int i = 0; i < ((Sort) task).getFractions().length; i++) {
  587. int j = jdbcTemplate.update(sqlAddFractionTask, new Object[]{task.getId(), ((Sort) task).getFractions()[i].getNumerator(), ((Sort) task).getFractions()[i].getDenominator()});
  588. if (j == 0) {
  589. return false;
  590. }
  591. }
  592. Fraction[] solution = ((Sort) task).getSolution();
  593. for (int i = 0; i < solution.length; i++) {
  594. int j = jdbcTemplate.update(sqlAddFractionSolution, new Object[]{task.getId(), solution[i].getNumerator(), solution[i].getDenominator()});
  595. if (j == 0) {
  596. return false;
  597. }
  598. }
  599. return true;
  600. } else if (task instanceof NumberLine) {
  601. prep.setInt(1, 4);
  602. prep.setString(2, task.getText());
  603. prep.setBoolean(3, type);
  604. prep.setString(4, task.getUsername());
  605. prep.execute();
  606. res = prep.getGeneratedKeys();
  607. if (res != null && res.next()) {
  608. task.setId(res.getInt(1));
  609. }
  610. if (task.getId() == 0) {
  611. return false;
  612. }
  613.  
  614. Fraction f = ((NumberLine) task).getSolution();
  615. int i = jdbcTemplate.update(sqlAddFractionSolution, new Object[]{task.getId(), f.getNumerator(), f.getDenominator()});
  616. if (i != 0) {
  617. return true;
  618. }
  619. } else if (task instanceof Figures) {
  620. prep.setInt(1, 5);
  621. prep.setString(2, task.getText());
  622. prep.setBoolean(3, type);
  623. prep.setString(4, task.getUsername());
  624. prep.execute();
  625. res = prep.getGeneratedKeys();
  626. if (res != null && res.next()) {
  627. task.setId(res.getInt(1));
  628. }
  629. if (task.getId() == 0) {
  630. return false;
  631. }
  632. int j = jdbcTemplate.update(sqlAddStringTask, new Object[]{task.getId(), ((Figures) task).getFigureUrl()});
  633. if (j == 0) {
  634. return false;
  635. }
  636. int i = jdbcTemplate.update(sqlAddStringSolution, new Object[]{task.getId(), ((Figures) task).getSolutionUrl()});
  637. if (i != 0) {
  638. return true;
  639. }
  640.  
  641. ///*****************GRUPPE 6 REDIGERER*************************//
  642. } else if (task instanceof Function) {
  643. prep.setInt(1, 6);
  644. prep.setString(2, task.getText());
  645. prep.setBoolean(3, type);
  646. prep.setString(4, task.getUsername());
  647. prep.execute();
  648. res = prep.getGeneratedKeys();
  649. if (res != null && res.next()) {
  650. task.setId(res.getInt(1));
  651. }
  652. if (task.getId() == 0) {
  653. return false;
  654. }
  655. if (((Function) task).getAnswerType() == 2) {
  656. for (int i = 0; i < ((Function) task).getChoices().size(); i++) {
  657.  
  658. int j = jdbcTemplate.update(sqlAddFunctionTask, new Object[]{task.getId(), ((Function) task).getAnswerType(), ((Function) task).getChoices().get(i),
  659. ((Function) task).isChecked1(), ((Function) task).isChecked2(), ((Function) task).getUrl(), ((Function) task).getFunctionstring()});
  660.  
  661.  
  662. if (j == 0) {
  663. return false;
  664. }
  665. }
  666. } else {
  667. int j = jdbcTemplate.update(sqlAddFunctionTask, new Object[]{task.getId(), ((Function) task).getAnswerType(), null,
  668. ((Function) task).isChecked1(), ((Function) task).isChecked2(), ((Function) task).getUrl(), ((Function) task).getFunctionstring()});
  669.  
  670. if (j == 0) {
  671. return false;
  672. }
  673. }
  674.  
  675. int i = jdbcTemplate.update(sqlAddFunctionSolution, new Object[]{task.getId(), ((Function) task).getSolution()});
  676. if (i != 0) {
  677. return true;
  678. }
  679. }
  680.  
  681. } catch (Exception e) {
  682. }
  683. return false;
  684. }
  685.  
  686. //Denne metoden henter ut informasjon om alle oppgaver av innsendt type (øvingsoppgave eller forskningsoppgave)
  687. @Override
  688. public List<TaskInfo> getAllTasks(boolean type) {
  689. return jdbcTemplate.query(sqlSelectTaskInfo, new Object[]{type}, new TaskInfoExtractor());
  690. }
  691.  
  692. //Denne metoden henter ut en oppgave med innsendt id
  693. @Override
  694. public Task getTask(int id) {
  695. Task task = null;
  696. SqlRowSet srs;
  697. try {
  698. task = (Task) jdbcTemplate.queryForObject(sqlSelectTask, new Object[]{id}, new TaskMapper());
  699. //Sjekker hvilken oppgavetype oppgaven er
  700. if (task instanceof Arithmetic) {
  701. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionSolution, new Object[]{id});
  702. while (srs.next()) {
  703. ((Arithmetic) task).setSolution(new Fraction(srs.getInt("numerator"), srs.getInt("denominator")));
  704. }
  705. } else if (task instanceof SingleChoice) {
  706. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionTask, new Object[]{id});
  707. ArrayList<Fraction> list = new ArrayList<>();
  708. while (srs.next()) {
  709. list.add(new Fraction(srs.getInt("numerator"), srs.getInt("denominator")));
  710. }
  711. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionSolution, new Object[]{id});
  712. Fraction solution = null;
  713. while (srs.next()) {
  714. solution = new Fraction(srs.getInt("numerator"), srs.getInt("denominator"));
  715. }
  716. Fraction[] choices = new Fraction[list.size()];
  717. choices = list.toArray(choices);
  718. ((SingleChoice) task).setChoices(choices);
  719. ((SingleChoice) task).setSolution(solution);
  720. } else if (task instanceof Sort) {
  721. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionTask, new Object[]{id});
  722. ArrayList<Fraction> choicesList = new ArrayList<>();
  723. while (srs.next()) {
  724. choicesList.add(new Fraction(srs.getInt("numerator"), srs.getInt("denominator")));
  725. }
  726. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionSolution, new Object[]{id});
  727. ArrayList<Fraction> solutionList = new ArrayList<>();
  728. while (srs.next()) {
  729. solutionList.add(new Fraction(srs.getInt("numerator"), srs.getInt("denominator")));
  730. }
  731. Fraction[] choices = new Fraction[choicesList.size()];
  732. choices = choicesList.toArray(choices);
  733. Fraction[] solution = new Fraction[solutionList.size()];
  734. solution = solutionList.toArray(solution);
  735. ((Sort) task).setFractions(choices);
  736. ((Sort) task).setSolution(solution);
  737. } else if (task instanceof NumberLine) {
  738. srs = jdbcTemplate.queryForRowSet(sqlSelectFractionSolution, new Object[]{id});
  739. while (srs.next()) {
  740. ((NumberLine) task).setSolution(new Fraction(srs.getInt("numerator"), srs.getInt("denominator")));
  741. }
  742. } else if (task instanceof Figures) {
  743. srs = jdbcTemplate.queryForRowSet(sqlSelectStringTask, new Object[]{id});
  744. while (srs.next()) {
  745. ((Figures) task).setFigureUrl(srs.getString("url"));
  746. }
  747. srs = jdbcTemplate.queryForRowSet(sqlSelectStringSolution, new Object[]{id});
  748. while (srs.next()) {
  749. ((Figures) task).setSolutionUrl(srs.getString("url"));
  750. }
  751. } /**
  752. * ***** GRUPPE 6 *******
  753. */
  754. else if (task instanceof Function) {
  755. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionAnswerType, new Object[]{id});
  756. while (srs.next()) {
  757. ((Function) task).setAnswerType(srs.getInt("answer_type"));
  758. }
  759. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionCheckboxes, new Object[]{id});
  760. while (srs.next()) {
  761. boolean b = srs.getBoolean("checkbox_explanation");
  762. if (b){
  763. ((Function) task).setExplanationChecked();
  764. }
  765. else{
  766. ((Function) task).setExplanationUnchecked();
  767. }
  768.  
  769. boolean b1 = srs.getBoolean("checkbox_drawing");
  770. if (b1){
  771. ((Function) task).setDrawingChecked();
  772. }
  773. else{
  774. ((Function) task).setDrawingUnchecked();
  775. }
  776. }
  777. ArrayList<String> list = new ArrayList<>();
  778. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionOptions, new Object[]{id});
  779. while (srs.next()) {
  780.  
  781. list.add(srs.getString("function_options"));
  782. }
  783. ((Function) task).setChoices(list);
  784.  
  785. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionUrl, new Object[]{id});
  786. while (srs.next()) {
  787. ((Function) task).setUrl(srs.getString("url"));
  788. }
  789. srs = jdbcTemplate.queryForRowSet(sqlSelectFunctionString, new Object[]{id});
  790. while (srs.next()) {
  791. ((Function) task).setFunctionstring(srs.getString("function_string"));
  792. }
  793. }
  794. } catch (Exception e) {
  795. }
  796. return task;
  797. }
  798.  
  799. //Denne metoden henter ut informasjon om alle forskningstester som er tilknyttet en klasse id, og som ikke er fullført av innsendt e-postadresse
  800. @Override
  801. public List<TestInfo> getAllTests(String email, int classId) {
  802. return jdbcTemplate.query(sqlSelectTests, new Object[]{email, email, classId}, new TestInfoExtractor());
  803. }
  804.  
  805. //Denne metoden henter ut informasjon om alle øvingstester som er tilknyttet en klasse id, og som ikke er fullført av innsendt e-postadresse
  806. @Override
  807. public List<TestInfo> getAllPractiseTests(String email, int classId) {
  808. return jdbcTemplate.query(sqlSelectPracticeTests, new Object[]{email, email, classId}, new TestInfoExtractor());
  809. }
  810.  
  811. //Denne metoden henter ut informasjon om en oppgave basert på innsendt id
  812. @Override
  813. public TaskInfo getTaskInfoId(int id) {
  814. return jdbcTemplate.queryForObject(sqlSelectTaskInfoId, new Object[]{id}, new TaskInfoMapper());
  815. }
  816.  
  817. //Denne metoden henter ut en test basert på innsendt id og e-postadresse
  818. @Override
  819. public Test getTest(int id, String email) {
  820. Test test = null;
  821. SqlRowSet srs;
  822. try {
  823. test = (Test) jdbcTemplate.queryForObject(sqlSelectTest, new Object[]{id}, new TestMapper());
  824. srs = jdbcTemplate.queryForRowSet(sqlSelectTestTask, new Object[]{id});
  825. ArrayList<Task> taskIdList = new ArrayList<>();
  826. int counter = 0;
  827. while (srs.next()) {
  828. Task t = getTask(srs.getInt("task_id")); //Henter ut oppgave tilknyttet testen
  829. Answer a = null;
  830. try {
  831. a = getAnswer(email, id, t.getId()); //Henter ut besvarelse tilknyttet testen
  832. } catch (NullPointerException e) {
  833.  
  834. }
  835. if (a != null) {
  836. t.setAnswer(a); //Legger til besvarelsen i oppgaven
  837. counter++;
  838. }
  839. taskIdList.add(t); //Legger til oppgaven i en liste
  840. }
  841. Task[] tasks = new Task[]{};
  842. tasks = taskIdList.toArray(tasks);
  843. test.setTasks(tasks); //Legger til oppgavene
  844. test.setCounter(counter);
  845. } catch (Exception e) {
  846. }
  847. return test;
  848. }
  849.  
  850. //Denne metoden legger til en test
  851. @Override
  852. public int addTest(Test test, List<Integer> taskIds, boolean type) {
  853. int testId = 0;
  854. try {
  855. PreparedStatement prep = con.prepareStatement(sqlAddTest, Statement.RETURN_GENERATED_KEYS);
  856. prep.setString(1, test.getTeacher());
  857. prep.setBoolean(2, test.isActive());
  858. prep.setBoolean(3, type);
  859. prep.execute();
  860. ResultSet res = prep.getGeneratedKeys();
  861. while (res != null && res.next()) {
  862. testId = res.getInt(1);
  863. test.setId(testId);
  864. }
  865. for (int i = 0; i < taskIds.size(); i++) {
  866. int j = jdbcTemplate.update(sqlAddTestTask, new Object[]{test.getId(), taskIds.get(i)});
  867. if (j == 0) {
  868. return 0;
  869. }
  870. }
  871. if (!type) {
  872. SqlRowSet srs = jdbcTemplate.queryForRowSet(sqlSelectTeacherClasses, new Object[]{test.getTeacher()});
  873. while (srs.next()) {
  874. int k = jdbcTemplate.update(sqlAddTestClass, new Object[]{test.getId(), srs.getInt("class_id")});
  875. }
  876. }
  877. } catch (Exception e) {
  878. }
  879. return testId;
  880. }
  881.  
  882. //Denne metoden tar seg av eksporteringen av en test med innsendt id
  883. @Override
  884. public void exportTest(int id) throws FileNotFoundException, IOException, SQLException {
  885. Workbook wb = new HSSFWorkbook();
  886. Sheet testSheet = wb.createSheet("Oversikt"); //Lager et Excel-ark som skal inneholde den generelle oversikten for testen.
  887.  
  888. int testRow = 1;
  889. int column = 1;
  890. Row testIdRow = testSheet.createRow(0);
  891. Cell testIdCell = testIdRow.createCell(0);
  892. testIdCell.setCellValue("Test " + id);
  893. Row testHeaderRow = testSheet.createRow(testRow);
  894. Cell emailTestCell = testHeaderRow.createCell(0);
  895. emailTestCell.setCellValue("Brukernavn");
  896. SqlRowSet srs = jdbcTemplate.queryForRowSet(sqlSelectTestUsers, new Object[]{id}); //Henter ut alle brukerne som har tatt testen
  897. testRow++;
  898. while (srs.next()) { //Går gjennom alle brukerne og henter ut deres resultat i testen
  899. Row testDataRow = testSheet.createRow(testRow);
  900. int correct = 0;
  901. int wrong = 0;
  902. String s = srs.getString("email_fk");
  903. column = 1;
  904. Cell emailTestDataCell = testDataRow.createCell(0);
  905. emailTestDataCell.setCellValue(s);
  906. SqlRowSet srset = jdbcTemplate.queryForRowSet(sqlSelectTestUsersResult, new Object[]{s, id}); //Henter ut resultatet for hver oppgave i testen for hver enkelt bruker.
  907.  
  908. while (srset.next()) {
  909. Cell taskTestHeaderCell = testHeaderRow.createCell(column);
  910. taskTestHeaderCell.setCellValue("Oppgave " + srset.getInt("task_id"));
  911. Cell taskTestDataCell = testDataRow.createCell(column);
  912.  
  913. boolean b = srset.getBoolean("correct");
  914. if (b) { //Setter her de verdiene som kan eksporteres til andre programmer, og summerer antall riktige og gale svar for hver bruker.
  915. taskTestDataCell.setCellValue("1");
  916. correct++;
  917. } else {
  918. taskTestDataCell.setCellValue("0");
  919. wrong++;
  920. }
  921.  
  922. testSheet.autoSizeColumn(column);
  923. column++;
  924. }
  925.  
  926. /*Her settes annen relevant informasjon som antall riktige og gale svar, samt den totale tiden brukeren har brukt på hele testen. */
  927. Cell correctTestDataCell = testDataRow.createCell(column);
  928. correctTestDataCell.setCellValue(correct);
  929. Cell wrongTestDataCell = testDataRow.createCell(column + 1);
  930. wrongTestDataCell.setCellValue(wrong);
  931. Cell timeTestDataCell = testDataRow.createCell(column + 2);
  932. timeTestDataCell.setCellValue(srs.getDouble("test_time"));
  933. testRow++;
  934. }
  935.  
  936. Cell correctTestDataCell = testHeaderRow.createCell(column);
  937. correctTestDataCell.setCellValue("Antall korrekt");
  938. Cell wrongTestDataCell = testHeaderRow.createCell(column + 1);
  939. wrongTestDataCell.setCellValue("Antall feil");
  940. Cell timeTestDataCell = testHeaderRow.createCell(column + 2);
  941. timeTestDataCell.setCellValue("Tid brukt i sekunder");
  942.  
  943. testSheet.autoSizeColumn(0);
  944. testSheet.autoSizeColumn(column);
  945. testSheet.autoSizeColumn(column + 1);
  946. testSheet.autoSizeColumn(column + 2);
  947.  
  948. /* Har nå satt all informasjon som skal være i test-oversikten. Tar da for oss de individuelle oppgavene. */
  949. List<TaskSolution> taskSolutions = jdbcTemplate.query(sqlSelectTaskSolution, new Object[]{id}, new TaskSolutionExtractor());
  950. int task = 1;
  951. for (TaskSolution ts : taskSolutions) {
  952. int taskRow = 0;
  953. Sheet taskSheet = wb.createSheet("Oppgave" + task);
  954. Row taskHeaderRow = taskSheet.createRow(taskRow);
  955. Cell taskTextHeaderCell = taskHeaderRow.createCell(0);
  956. taskTextHeaderCell.setCellValue(ts.getText());
  957. Cell solutionHeaderCell = taskHeaderRow.createCell(1);
  958. solutionHeaderCell.setCellValue(ts.getSolutionString());
  959. List<TaskAnswer> taskAnswers = jdbcTemplate.query(sqlSelectTaskSummary, new Object[]{id, ts.getId()}, new TaskAnswerExtractor());
  960.  
  961. taskRow++;
  962. taskHeaderRow = taskSheet.createRow(taskRow);
  963. Cell emailTaskHeaderCell = taskHeaderRow.createCell(0);
  964. emailTaskHeaderCell.setCellValue("Brukernavn");
  965. Cell answerHeaderCell = taskHeaderRow.createCell(1);
  966. answerHeaderCell.setCellValue("Svar");
  967. Cell correctHeaderCell = taskHeaderRow.createCell(2);
  968. correctHeaderCell.setCellValue("Korrekt");
  969. Cell explenationHeaderCell = taskHeaderRow.createCell(3);
  970. explenationHeaderCell.setCellValue("Forklaring");
  971. Cell timeHeaderCell = taskHeaderRow.createCell(4);
  972. timeHeaderCell.setCellValue("Tid brukt i sekunder");
  973.  
  974. taskRow++;
  975. for (TaskAnswer ta : taskAnswers) {
  976. Row dataRow = taskSheet.createRow(taskRow);
  977. Cell emailDataCell = dataRow.createCell(0);
  978. emailDataCell.setCellValue(ta.getEmail());
  979. Cell answerDataCell = dataRow.createCell(1);
  980. answerDataCell.setCellValue(ta.getAnswerString());
  981. Cell correctDataCell = dataRow.createCell(2);
  982. correctDataCell.setCellValue(ta.getCorrect());
  983. Cell explenationDataCell = dataRow.createCell(3);
  984. explenationDataCell.setCellValue(ta.getExplenation());
  985. Cell timeDataCell = dataRow.createCell(4);
  986. timeDataCell.setCellValue(ta.getTime());
  987. taskRow++;
  988. }
  989. taskSheet.autoSizeColumn(0);
  990. taskSheet.autoSizeColumn(1);
  991. taskSheet.autoSizeColumn(2);
  992. taskSheet.autoSizeColumn(3);
  993. taskSheet.autoSizeColumn(4);
  994.  
  995. task++;
  996. }
  997. String outputDirPath = "D:/Dokumenter/Documents/Matistikk/Test" + id; //[5]: Setter hvor på serveren filen skal lagres
  998. File dir = new File(outputDirPath);
  999. if (!dir.exists()) {
  1000. if (!dir.mkdirs()) {
  1001. }
  1002. }
  1003. outputDirPath += "/Test" + id + ".xls";
  1004. File file = new File(outputDirPath);
  1005. if (file.exists()) {
  1006. file.delete();
  1007. }
  1008. FileOutputStream out = new FileOutputStream(outputDirPath);
  1009. wb.write(out);
  1010. out.close();
  1011. }
  1012.  
  1013. /*
  1014. Denne metoden tar seg av zippingen av valgte tester.
  1015.  
  1016. Koden tar i bruk biblioteket ZeroTurnaround ZIP Library som man finner på: https://github.com/zeroturnaround/zt-zip [Besøkt 23.05.16]
  1017. */
  1018. @Override
  1019. public String zipTests(List<Integer> testIds) {
  1020. String path = "";
  1021. String dstPath = "D:\\Dokumenter\\Documents\\Matistikk\\ZipFolder"; //[6]: Midlertidig mappestruktur som vil tas i bruk av .zip-filen
  1022. try {
  1023. File file = new File("D:\\Dokumenter\\Documents\\Matistikk\\Matistikk.zip"); //[7]: Setter hvor på serveren .zip-filen skal lagres
  1024. if (file.exists()) {
  1025. file.delete();
  1026. }
  1027. File dst = new File(dstPath);
  1028. for (Integer id : testIds) {
  1029. exportTest(id);
  1030. try {
  1031. copyFolder(new File("D:\\Dokumenter\\Documents\\Matistikk\\Test" + id), dst); //[8]: Kopiering av mappe
  1032. } catch (IOException e) {
  1033. }
  1034. }
  1035. ZipUtil.pack(dst, file);
  1036. path = "D:\\Dokumenter\\Documents\\Matistikk\\Matistikk.zip"; //[9]: Sier hvor på serveren .zip-filen er lagret
  1037. FileUtils.deleteDirectory(dst);
  1038. } catch (Exception e) {
  1039.  
  1040. }
  1041. return path;
  1042. }
  1043.  
  1044. /*
  1045. Denne metoden er en hjelpemetode for zipping av statistikk.
  1046.  
  1047. Hentet fra: http://www.mkyong.com/java/how-to-copy-directory-in-java/ [Besøkt: 23.05.15]
  1048. */
  1049. static void copyFolder(File src, File dest) throws IOException {
  1050. if (src.isDirectory()) {
  1051. if (!dest.exists()) {
  1052. dest.mkdir();
  1053. }
  1054. String files[] = src.list();
  1055.  
  1056. for (String file : files) {
  1057. File srcFile = new File(src, file);
  1058. File destFile = new File(dest, file);
  1059. copyFolder(srcFile, destFile);
  1060. }
  1061. } else {
  1062. InputStream in = new FileInputStream(src);
  1063. OutputStream out = new FileOutputStream(dest);
  1064.  
  1065. byte[] buffer = new byte[4096];
  1066.  
  1067. int length;
  1068. while ((length = in.read(buffer)) > 0) {
  1069. out.write(buffer, 0, length);
  1070. }
  1071.  
  1072. in.close();
  1073. out.close();
  1074. }
  1075. }
  1076.  
  1077. //Denne metoden sjekker om klassen med innsendt id eksisterer
  1078. @Override
  1079. public boolean checkClass(int id) {
  1080. try {
  1081. int i = jdbcTemplate.queryForObject(sqlCheckClass, new Object[]{id}, Integer.class);
  1082. return i == 1;
  1083. } catch (Exception e) {
  1084. return false;
  1085. }
  1086. }
  1087.  
  1088. //Henter ut inforamsjon om klassen med innsendt id
  1089. @Override
  1090. public ClassInfo getSchoolClass(int id) {
  1091. return jdbcTemplate.queryForObject(sqlSelectClass, new Object[]{id}, new ClassInfoMapper());
  1092. }
  1093.  
  1094. //Henter ut tegnekoordinatene som er tilknyttet besvarelsen med innsendt id
  1095. @Override
  1096. public List<Double> getCoordinates(int answerId) {
  1097. List<Double> list = null;
  1098. BufferedReader br = null;
  1099. try {
  1100. String s = jdbcTemplate.queryForObject(sqlSelectDrawing, new Object[]{answerId}, String.class); //Henter ut plasseringen til filen med tegnekoordinatene på serveren
  1101. String currentLine;
  1102. if (s != null) {
  1103. br = new BufferedReader(new FileReader(s));
  1104. list = new ArrayList<Double>(); //Oppretter listen med koordinatene
  1105. while ((currentLine = br.readLine()) != null) {
  1106. list.add(Double.parseDouble(currentLine)); //Legger til koordinatene i listen
  1107. }
  1108. }
  1109. } catch (Exception e) {
  1110. } finally {
  1111. try {
  1112. if (br != null) {
  1113. br.close();
  1114. }
  1115. } catch (Exception e) {
  1116.  
  1117. }
  1118. }
  1119. return list;
  1120. }
  1121.  
  1122. //Denne metoden legger til tegnekoordinatene
  1123. @Override
  1124. public boolean addCoordinates(int testId, int taskId, int answerId, String email, List<Double> cords) {
  1125. FileWriter fw = null;
  1126. BufferedWriter bw = null;
  1127. try {
  1128. boolean b = false;
  1129. String s = "D:\\Dokumenter\\Documents\\Matistikk\\Test" + testId + "\\Test" + testId + "\\Oppgave" + taskId + "\\" + email; //[10]: Velger hvor på serveren .txt-filen med koordinatene skal lagres
  1130. File dir = new File(s);
  1131. if (!dir.exists()) {
  1132. if (!dir.mkdirs()) {
  1133. return false;
  1134. }
  1135. }
  1136. String path = s + "\\tegning.txt";
  1137. File file = new File(dir, "tegning.txt"); //Oppretter tegnekoordinatfilen
  1138. if (file.exists()) {
  1139. file.delete();
  1140. }
  1141. file = new File(dir, "tegning.txt");
  1142. fw = new FileWriter(file);
  1143. bw = new BufferedWriter(fw);
  1144. for (int i = 0; i < cords.size(); i++) {
  1145. bw.write(cords.get(i).toString());
  1146. bw.newLine();
  1147. }
  1148. try {
  1149. jdbcTemplate.update(sqlAddDrawing, new Object[]{answerId, path}); //Legger til plasseringen på .txt-filen i databasen
  1150. } catch (Exception e) {
  1151. }
  1152. return true;
  1153. } catch (Exception e) {
  1154. return false;
  1155. } finally {
  1156. try {
  1157. bw.close();
  1158. fw.close();
  1159. } catch (IOException ex) {
  1160.  
  1161. }
  1162. }
  1163. }
  1164.  
  1165. //Denne metoden henter ut antall besvarte tester basert på innsendt e-postadresse
  1166. @Override
  1167. public String getTestCount(String username, boolean completed, boolean testable) {
  1168. return jdbcTemplate.queryForObject(sqlSelectCountTestsTaken, new Object[]{username, completed, testable}, String.class);
  1169. }
  1170.  
  1171. //Denne metoden henter ut antall tester laget av læreren med innsendt e-postadresse
  1172. @Override
  1173. public String getTestMadeCount(String username) {
  1174. String s = jdbcTemplate.queryForObject(sqlSelectCountTestsMade, new Object[]{username}, String.class);
  1175. return s;
  1176. }
  1177.  
  1178. //Denne metoden henter ut statistikken til alle testene av innsendt type (forskningstest eller øvingstest)
  1179. @Override
  1180. public List<TestStatistics> getStatistics(boolean type) {
  1181. return jdbcTemplate.query(sqlSelectStatistics, new Object[]{type}, new TestStatisticsMapper());
  1182. }
  1183.  
  1184. //Denne metoden henter ut statistikken til alle oppgavene som er tilknyttet innsendt test id
  1185. @Override
  1186. public List<TaskStatistics> getTestStatistics(int testId) {
  1187. return jdbcTemplate.query(sqlSelectTestStatistics, new Object[]{testId}, new TaskStatisticsMapper());
  1188. }
  1189.  
  1190. //Denne metoden henter ut statistikken til alle besvarelsene som er tilknyttet innsendt test id og oppgave id
  1191. @Override
  1192. public List<TaskAnswer> getTaskStatistics(int testId, int taskId) {
  1193. return jdbcTemplate.query(sqlSelectTaskStatistics, new Object[]{testId, taskId}, new AnswerStatisticsMapper());
  1194. }
  1195.  
  1196. //Denne metoden oppdaterer hvilken klasse brukeren med innsendt e-postadresse er tilknyttet
  1197. @Override
  1198. public boolean updateClassId(int classId, String username) {
  1199. int i = jdbcTemplate.update(sqlUpdateClassId, new Object[]{classId, username});
  1200. return i > 1;
  1201. }
  1202.  
  1203. //Denne metoden henter ut informasjon om alle testene av innsendt type (forskningstest eller øvingstest) som er opprettet av brukeren med innsendt e-postadresse
  1204. @Override
  1205. public List<TestInfo> getAllTestInfo(boolean type, String username) {
  1206. return jdbcTemplate.query(sqlSelectTestInfo, new Object[]{type, username}, new TestInfoMapper());
  1207. }
  1208.  
  1209. //Denne metoden henter ut alle tester som kan publiseres av læreren med innsendt e-postadresse
  1210. @Override
  1211. public List<Integer> getTestTeacher(String email) {
  1212. return (List<Integer>) jdbcTemplate.queryForList(sqlSelectTestTeacher, new Object[]{email}, Integer.class);
  1213. }
  1214.  
  1215. //Denne metoden oppretter en forbindelse mellom læreren med innsendt e-postadresse og klassene med innsendt id
  1216. @Override
  1217. public boolean addTeacherClasses(String email, List<Integer> classes) {
  1218. for (Integer i : classes) {
  1219. int j = jdbcTemplate.update(sqlAddTeacherClass, new Object[]{email, i});
  1220. if (j == 0) {
  1221. return false;
  1222. }
  1223. }
  1224. return true;
  1225. }
  1226.  
  1227. //Denne metoden henter ut alle klassene som er tilknyttet med læreren med innsendt e-postadresse
  1228. @Override
  1229. public List<String> getTeacherClass(String email) {
  1230. return (List<String>) jdbcTemplate.queryForList(sqlSelectTeacherClass, new Object[]{email}, String.class);
  1231. }
  1232.  
  1233. //Denne metoden henter ut informasjon om alle klassene som ikke allerede er tilknyttet læreren med innsendt e-postadresse men som er tilknyttet lærerens skole
  1234. @Override
  1235. public List<ClassInfo> getAllClassesInfo(String email, int schoolId) {
  1236. return (List<ClassInfo>) jdbcTemplate.query(sqlSelectClasses, new Object[]{email, schoolId}, new ClassInfoMapper());
  1237. }
  1238.  
  1239. //Denne metoden setter aktivstatusen til brukeren med innsendt e-postadresse
  1240. @Override
  1241. public boolean setUserActive(String email, boolean active) {
  1242. int i = jdbcTemplate.update(sqlUpdateUserActive, new Object[]{active, email});
  1243. return i == 1;
  1244. }
  1245.  
  1246. //Denne metoden henter ut statistikken om lærerene som er tilknyttet testen med innsendt id
  1247. @Override
  1248. public List<TeacherStatistics> getTestTeachers(int testId) {
  1249. return jdbcTemplate.query(sqlSelectTestTeachers, new Object[]{testId}, new TeacherStatisticsExtractor());
  1250. }
  1251.  
  1252. //Denne metoden legger til en hvilke lærere som kan publisere testen med innsendt test id
  1253. @Override
  1254. public boolean addTestTeachers(int testId, List<String> teachers) {
  1255. for (String s : teachers) {
  1256. int i = jdbcTemplate.update(sqlAddTestTeacher, new Object[]{testId, s});
  1257. if (i == 0) {
  1258. return false;
  1259. }
  1260. }
  1261. return true;
  1262. }
  1263.  
  1264. //Denne metoden sjekker om læreren med innsendt e-postadresse har upuliserte tester
  1265. @Override
  1266. public boolean checkPublishTests(String email) {
  1267. boolean b = false;
  1268. try {
  1269. b = jdbcTemplate.queryForObject(sqlCheckTests, new Object[]{email}, Integer.class) > 0;
  1270. } catch (Exception e) {
  1271. }
  1272. return b;
  1273. }
  1274.  
  1275. //Denne metoden henter de upubliserte testene for læreren med innsendt e-postadresse
  1276. @Override
  1277. public List<TestInfo> getPublishTests(String email) {
  1278. return jdbcTemplate.query(sqlSelectPublishTests, new Object[]{email}, new TestInfoMapper());
  1279. }
  1280.  
  1281. //Denne metoden henter ut de klassene som en test kan publiseres til basert på lærerens e-postadresse, lærerens skole og testens id
  1282. @Override
  1283. public List<ClassInfo> getPublishClasses(String email, int testId, int schoolId) {
  1284. return jdbcTemplate.query(sqlSelectPublishClasses, new Object[]{email, testId, schoolId}, new ClassInfoMapper());
  1285. }
  1286.  
  1287. //Denne metoden publiserer en test til valgte klasser
  1288. @Override
  1289. public boolean addPublishClasses(String email, int testId, List<Integer> classIds) {
  1290. try {
  1291. for (Integer i : classIds) {
  1292. int j = jdbcTemplate.update(sqlAddTestClass, new Object[]{testId, i});
  1293. if (j == 0) {
  1294. return false;
  1295. }
  1296. }
  1297. jdbcTemplate.update(sqlUpdateTestTeacher, new Object[]{true, email, testId});
  1298. return true;
  1299.  
  1300. } catch (Exception e) {
  1301. return false;
  1302. }
  1303. }
  1304.  
  1305. //Denne metoden henter ut aktivstatusen til testen med innsendt id
  1306. @Override
  1307. public boolean getTestActive(int testId) {
  1308. return jdbcTemplate.queryForObject(sqlSelectTestActive, new Object[]{testId}, Boolean.class);
  1309. }
  1310.  
  1311. //Denne metoden setter aktivstatuen til testen med innsendt id
  1312. @Override
  1313. public boolean setTestActive(int testId, boolean b) {
  1314. return jdbcTemplate.update(sqlUpdateTestActive, new Object[]{b, testId}) > 0;
  1315. }
  1316.  
  1317. //Denne metoden henter ut statistikken til alle lærerne
  1318. @Override
  1319. public List<TeacherStatistics> getAllTeacherStatistics() {
  1320. return jdbcTemplate.query(sqlSelectAllClassTeachers, new TeacherStatisticsExtractor());
  1321. }
  1322.  
  1323. //Denne metoden henter ut informasjon om alle skolene
  1324. @Override
  1325. public List<SchoolInfo> getAllSchools() {
  1326. return jdbcTemplate.query(sqlSelectAllSchools, new SchoolInfoMapper());
  1327. }
  1328.  
  1329. //Denne metoden legger til en skole med navn lik innsendt skolenavn
  1330. @Override
  1331. public boolean addSchool(String schoolName) {
  1332. return jdbcTemplate.update(sqlAddSchool, new Object[]{schoolName}) != 0;
  1333. }
  1334.  
  1335. //Denne metoden henter ut alle klassene som er tilknyttet skolen med innsendt id
  1336. @Override
  1337. public List<ClassInfo> getAllClasses(int schoolId) {
  1338. return jdbcTemplate.query(sqlSelectAllClasses, new Object[]{schoolId}, new ClassInfoMapper());
  1339. }
  1340.  
  1341. //Denne metoden legger til en klasse tilknyttet skolen med innsendt id
  1342. @Override
  1343. public boolean addClass(String className, int schoolId) {
  1344. return jdbcTemplate.update(sqlAddClass, new Object[]{className, schoolId}) != 0;
  1345. }
  1346.  
  1347. //Denne metoden henter ut all informasjon om studentene tilknyttet klassen med innsendt id
  1348. @Override
  1349. public List<StudentInfo> getAllStudentsClass(int classId) {
  1350. return jdbcTemplate.query(sqlSelectStudentsClass, new Object[]{classId}, new StudentInfoMapper());
  1351. }
  1352. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!