-
Notifications
You must be signed in to change notification settings - Fork 0
/
MaturaDataSource.java
596 lines (524 loc) · 20.1 KB
/
MaturaDataSource.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
package org.indywidualni.dbproject.database;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import org.indywidualni.dbproject.model.AdminUser;
import org.indywidualni.dbproject.model.PointDistribution;
import org.indywidualni.dbproject.model.StudentExam;
import org.indywidualni.dbproject.model.StudentExamsStats;
import org.indywidualni.dbproject.model.StudentExerciseResult;
import org.indywidualni.dbproject.model.StudentSummary;
import org.indywidualni.dbproject.model.TeacherExam;
import java.util.ArrayList;
/**
* Created by Krzysztof Grabowski on 20.01.16.
* Singleton pattern. The core of an application. All the data is retrieved from
* the database in this class. Data retrieving methods are synchronized in order to
* make it thread safe.
*/
public class MaturaDataSource {
private static volatile MaturaDataSource instance;
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
private MaturaDataSource() {}
/**
* We want to make it thread safe, so the best way of achieving it is just to
* get an instance of this object every time we need it and don't worry about
* our future. Singleton pattern is rather a popular one.
* @return an instance of this object
*/
public static MaturaDataSource getInstance() {
if (instance == null) {
synchronized (MaturaDataSource.class) {
if (instance == null)
instance = new MaturaDataSource();
}
}
return instance;
}
/**
* Open a database, actually open a writable database
* @throws SQLException
*/
private void open() throws SQLException {
if (dbHelper == null) {
dbHelper = new MySQLiteHelper();
database = dbHelper.getWritableDatabase();
}
}
/**
* Close database when it's not needed anymore. Thread safe implementation
* although it's not needed at all because all the methods are synchronized
* to an object of the class.
*/
private void close() {
if (database != null) {
// actually not needed because of synchronization
//noinspection StatementWithEmptyBody
while (database.isDbLockedByCurrentThread()) {}
dbHelper.close();
dbHelper = null;
database = null;
}
}
/**
* Empty database connection. Just open a database and close it. It seems stupid, yeah?
* It's rather not. During the very first run of an app (or when a database has been
* upgraded), opening it forces database creation (recreation). We need to do it
* asynchronously during an app start to avoid any UI freezes.
* @throws SQLException
*/
public synchronized void emptyConnection() throws SQLException {
open();
close();
}
/**
* Open a database, get user password hash from a database, close a database.
* @param pesel pesel of an user
* @return salted hash on a password
* @throws SQLException
*/
public synchronized String getUserPassword(String pesel) throws SQLException {
open();
Cursor cursor = null;
String password = "";
try {
cursor = database.rawQuery("SELECT Haslo FROM Osoby WHERE PESEL=?", new String[] { pesel });
if(cursor.getCount() > 0) {
cursor.moveToFirst();
password = cursor.getString(cursor.getColumnIndex("Haslo"));
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return password;
}
/**
* Open a database, check is user a teacher, close a database.
* @param pesel pesel of an user
* @return is a teacher or not
* @throws SQLException
*/
public synchronized boolean getIsUserTeacher(String pesel) throws SQLException {
open();
Cursor cursor = null;
boolean isTeacher = false;
try {
cursor = database.rawQuery("Select PESEL from Nauczyciele Where PESEL=?", new String[] { pesel });
isTeacher = cursor.getCount() == 1;
} finally {
if (cursor != null)
cursor.close();
}
close();
return isTeacher;
}
/**
* Open a database, get a student summary into a model (PESEL, first name, etc.).
* Close a database then.
* @param pesel pesel of a student
* @return student summary model
* @throws SQLException
*/
public synchronized StudentSummary getStudentSummary(String pesel) throws SQLException {
open();
Cursor cursor = null;
StudentSummary studentSummary = null;
try {
cursor = database.rawQuery("Select * from statUczen where PESEL=?", new String[] { pesel });
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
int peselDatabase = cursor.getInt(cursor.getColumnIndex("PESEL"));
String firstName = cursor.getString(cursor.getColumnIndex("Pierwsze_Imie"));
String surname = cursor.getString(cursor.getColumnIndex("Nazwisko"));
int numberOfExams = cursor.getInt(cursor.getColumnIndex("Ilosc egzaminow"));
int passedExams = cursor.getInt(cursor.getColumnIndex("Zdane"));
int averageResult = cursor.getInt(cursor.getColumnIndex("Sredni wynik %"));
studentSummary = new StudentSummary(peselDatabase, firstName, surname,
numberOfExams, passedExams, averageResult);
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return studentSummary;
}
/**
* Open a database, get all the basic exam results for a student, close a database.
* @param pesel pesel of a student
* @return a list of all student exams results
* @throws SQLException
*/
public synchronized ArrayList<StudentExam> getAllStudentExams (String pesel) throws SQLException {
open();
Cursor cursor = null;
ArrayList<StudentExam> list = new ArrayList<>();
try {
cursor = database.rawQuery("Select E.Przedmiot, E.Poziom, E.Rok, E.Termin+1 AS Termin, " +
"R.Wynik, R.[Wynik proc], R.Zdany, R.[Nr egzaminu] from Egzaminy E Join Rezultaty R ON " +
"E.ID=R.Egzamin where Zdajacy=(Select ID from " +
"Uczniowie where PESEL=?)", new String[] { pesel });
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
StudentExam exam = cursorToStudentExam(cursor);
list.add(exam);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return list;
}
/**
* A cursor which reads a current row and loads all the data into a model
* @param cursor cursor
* @return student exam model
*/
private StudentExam cursorToStudentExam(Cursor cursor) {
String course = cursor.getString(0);
int level = cursor.getInt(1);
int year = cursor.getInt(2);
int time = cursor.getInt(3);
int result = cursor.getInt(4);
int percent = cursor.getInt(5);
boolean passed = cursor.getInt(6) > 0;
int id = cursor.getInt(7);
return new StudentExam(course, level, year, time, result, percent, passed, id);
}
/**
* Get exam results for a student
* @param examID exam ID
* @return a list of single exercises
* @throws SQLException
*/
public synchronized ArrayList<StudentExerciseResult> getStudentExamResult(String examID) throws SQLException {
open();
Cursor cursor = null;
ArrayList<StudentExerciseResult> list = new ArrayList<>();
try {
cursor = database.rawQuery("Select * from Punkty where [Nr egzaminu] = ?", new String[] { examID });
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
StudentExerciseResult exam = cursorToStudentExercise(cursor);
list.add(exam);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return list;
}
/**
* Cursor to student exercise
* @param cursor cursor
* @return a single exercise
*/
private StudentExerciseResult cursorToStudentExercise(Cursor cursor) {
return new StudentExerciseResult(cursor.getInt(1), cursor.getInt(2), cursor.getString(3));
}
/**
* Get all student's exams global stats. Just to check what are the average
* results for all the exams
* @param przedmiot subject
* @param rok year
* @param poziom level
* @param termin time
* @return a single exam stats object
* @throws SQLException
*/
public synchronized StudentExamsStats getStudentExamStats(
String przedmiot, String rok, String poziom, String termin) throws SQLException {
open();
Cursor cursor = null;
StudentExamsStats stats = null;
try {
String q = "SELECT * from statEgzamin WHERE poziom=" + poziom + " and termin="
+ termin + " and rok=" + rok + " and przedmiot like %s";
String query = String.format(q, "\""+ przedmiot + "%\"");
cursor = database.rawQuery(query, null);
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
int year = cursor.getInt(cursor.getColumnIndex("Rok"));
String subject = cursor.getString(cursor.getColumnIndex("Przedmiot"));
int level = cursor.getInt(cursor.getColumnIndex("Poziom"));
int time = cursor.getInt(cursor.getColumnIndex("Termin"));
int students = cursor.getInt(cursor.getColumnIndex("Ilosc zdajacych"));
int avrg = cursor.getInt(cursor.getColumnIndex("Sredni wynik"));
int percent = cursor.getInt(cursor.getColumnIndex("Sredni wynik %"));
int passed = cursor.getInt(cursor.getColumnIndex("Zdawalnosc"));
stats = new StudentExamsStats(year, subject, level, time, students, avrg, percent, passed);
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return stats;
}
/**
* Get all user's details. Used by an admin to check information about users
* @return a list of user objects
* @throws SQLException
*/
public synchronized ArrayList<AdminUser> getAllUsers() throws SQLException {
open();
Cursor cursor = null;
ArrayList<AdminUser> list = new ArrayList<>();
try {
cursor = database.rawQuery("Select * from Osoby", new String[] {});
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
AdminUser user = cursorToUsers(cursor);
list.add(user);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return list;
}
/**
* Cursor to get a single user data
* @param cursor cursor
* @return a single user object
*/
private AdminUser cursorToUsers(Cursor cursor) {
return new AdminUser(cursor.getInt(0), cursor.getString(1), cursor.getString(2),
cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6),
cursor.getString(7), cursor.getString(8), cursor.getInt(9), cursor.getInt(10));
}
/**
* Change user's password. Used by the admin
* @param pesel pesel of an user
* @param password new password
* @throws SQLException
*/
public synchronized void changeUserPassword(String pesel, String password) throws SQLException {
open();
database.execSQL("UPDATE Osoby SET Haslo='" + password + "' WHERE PESEL=" + pesel);
close();
}
/**
* Get all the teacher's student's
* @param pesel pesel of a teacher
* @return a list of single student summaries
* @throws SQLException
*/
public synchronized ArrayList<StudentSummary> getTeacherStudents(String pesel) throws SQLException {
open();
Cursor cursor = null;
ArrayList<StudentSummary> list = new ArrayList<>();
try {
cursor = database.rawQuery("Select * from statUczen where PESEL IN (Select Pesel from Uczniowie " +
"Where Wychowawca=(select ID from Nauczyciele where PESEL=?))", new String[] { pesel });
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
StudentSummary student = cursorToTeacherStudents(cursor);
list.add(student);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return list;
}
/**
* Cursor to get info about a single student
* @param cursor cursor
* @return a single student summary
*/
private StudentSummary cursorToTeacherStudents(Cursor cursor) {
return new StudentSummary(cursor.getInt(0), cursor.getString(1), cursor.getString(2),
cursor.getInt(3), cursor.getInt(4), cursor.getInt(5));
}
/**
* Get all the exams in order to be able to grade them
* @return all the existing exams
* @throws SQLException
*/
public synchronized ArrayList<TeacherExam> getTeacherAllExams() throws SQLException {
open();
Cursor cursor = null;
ArrayList<TeacherExam> list = new ArrayList<>();
try {
cursor = database.rawQuery("SELECT * FROM Egzaminy", new String[] {});
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
TeacherExam exam = cursorToTeacherAllExams(cursor);
list.add(exam);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return list;
}
/**
* cursor to get data about a single exam
* @param cursor cursor
* @return a single exam object for a teacher
*/
private TeacherExam cursorToTeacherAllExams(Cursor cursor) {
return new TeacherExam(cursor.getInt(0), cursor.getString(1), cursor.getInt(2), cursor.getInt(3),
cursor.getInt(4), cursor.getInt(5), cursor.getInt(6));
}
/**
* Check is teacher permitted to grade students
* @param pesel pesel of a teacher
* @return can grade or cannot grade
* @throws SQLException
*/
public synchronized boolean isTeacherPermitted(String pesel) throws SQLException {
open();
Cursor cursor = null;
boolean canGrade = false;
try {
cursor = database.rawQuery("SELECT Uprawnienia FROM Nauczyciele where pesel=? " +
"and Uprawnienia=1", new String[] { pesel });
canGrade = cursor.getCount() == 1;
} finally {
if (cursor != null)
cursor.close();
}
close();
return canGrade;
}
/**
* Get point distribution for all the exercises of a given exam
* @param examID a given exam id
* @return a list of a single exercise information
* @throws SQLException
*/
public synchronized ArrayList<PointDistribution> getPointDistribution(String examID)
throws SQLException { // not used anywhere
open();
Cursor cursor = null;
ArrayList<PointDistribution> pointDistributions = new ArrayList<>();
try {
cursor = database.rawQuery("SELECT * FROM [Rozklad Punktow] where Egzamin=?",
new String[] { examID });
if(cursor.getCount() > 0) {
// retrieve the data to my custom model
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
PointDistribution points = cursorToPointDistribution(cursor);
pointDistributions.add(points);
cursor.moveToNext();
}
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return pointDistributions;
}
/**
* Cursor to get points distribution
* @param cursor cursor
* @return a single point distribution object
*/
private PointDistribution cursorToPointDistribution(Cursor cursor) {
return new PointDistribution(cursor.getInt(0), cursor.getInt(1), cursor.getInt(2));
}
/**
* Get maximum number of points possible for an exercise
* @param examID exam id
* @param exercise exercise
* @return maximum number of points
* @throws SQLException
*/
public synchronized int getMaxPointsForExercise(String examID, String exercise)
throws SQLException {
open();
Cursor cursor = null;
int max = 0;
try {
cursor = database.rawQuery("SELECT * FROM [Rozklad Punktow] " +
"where [Nr zadania]=? and Egzamin=?", new String[] { exercise, examID });
if (cursor.getCount() > 0) {
cursor.moveToFirst();
max = cursor.getInt(cursor.getColumnIndex("Max pkt"));
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return max;
}
/** Get ID of a teacher which is permitted to grade
* @param pesel teacher's pesel
* @return an id of a permitted teacher
* @throws SQLException
*/
public synchronized int getExaminatorId(String pesel) throws SQLException {
open();
Cursor cursor = null;
int id = -1;
try {
cursor = database.rawQuery("SELECT ID FROM Nauczyciele WHERE pesel=?", new String[] { pesel });
if(cursor.getCount() > 0) {
cursor.moveToFirst();
id = cursor.getInt(cursor.getColumnIndex("ID"));
}
} finally {
if (cursor != null)
cursor.close();
}
close();
return id;
}
/**
* Update or insert points for a single exercise
* @param id exam id
* @param ex exercise number
* @param points new number of points
* @param description description for the grade
* @param teacher teacher who is going to grade this exercise
* @throws SQLException
*/
public synchronized void teacherInsertOrUpdatePoints(
String id, String ex, String points, String description, String teacher)
throws SQLException {
open();
String query = "INSERT or REPLACE INTO Punkty ([Nr egzaminu], [Nr zadania], Punkty, [Opis oceny], Oceniajacy) " +
"values (" + id + ", " + ex + ", " + points + ", \"" + description + "\", " + teacher + ");";
Log.v("insertOrUpdatePoints", query);
database.execSQL(query);
/* database.rawQuery("INSERT or REPLACE INTO Punkty ([Nr egzaminu], [Nr zadania], Punkty, [Opis oceny], Oceniajacy) " +
"values (?, ?, ?, ?, ?)", new String[] { id, ex, points, description, teacher});*/
close();
}
}