· 8 years ago · Dec 11, 2017, 06:10 PM
1package gymhelp.feup.org.pushupfit;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.content.Intent;
6import android.database.Cursor;
7import android.database.sqlite.SQLiteDatabase;
8import android.database.sqlite.SQLiteOpenHelper;
9import android.util.Log;
10
11import java.util.ArrayList;
12import java.util.List;
13
14/**
15 * Created by Paulo Maia on 18/11/2017.
16 */
17
18public class DatabaseHandler extends SQLiteOpenHelper {
19
20 // All Static variables
21 // Database Version
22 private static final int DATABASE_VERSION = 1;
23 public Boolean isLogin;
24
25 // Database Name
26 private static final String DATABASE_NAME = "userManager";
27
28 // Contacts table name
29 private static final String TABLE_CONTACTS = "users";
30 private static final String TABLE_ACTIVITY="activities";
31 private static final String TABLE_MONITORING="monitoring";
32 private static final String TABLE_CALIBRATION="calibration";
33 private static final String TABLE_YMCA="ymca";
34 private static final String TABLE_EXPERIENCE="experience";
35
36
37
38 // Contacts Table Columns names
39 private static final String USER_ID = "user_id";
40 private static final String NAME = "name";
41 private static final String PW = "password";
42 private static final String AGE = "age";
43 private static final String GENDER = "gender";
44 private static final String UP_NO = "up_number";
45 private static final String XP = "experience";
46
47 //Activities Table Column names
48 private static final String ACT_ID="activity_id";
49 private static final String ACT_NAME="activity_name";
50 private static final String ACT_DATE="activity_date";
51
52 //Monitoring Table Column names
53 private static final String MONI_ID="monitoring_id";
54 private static final String MEAN_RC="mean_rc";
55 private static final String OPTIMAL_TRAINING_TIME="opti_train_time";
56
57 //Calibration Table Column names
58 private static final String CALI_ID="calibration_id";
59 private static final String BASAL_RC="basal_rc";
60 private static final String CALI_DATE="calibration_date";
61
62 //YMCA Table Column names
63 private static final String YMCA_ID="ymca_id";
64 private static final String YMCA_RC="ymca_rc";
65 private static final String YMCA_DATE="ymca_date";
66
67 //Experience Table Column names
68 private static final String EXPERIENCE_ID="experience";
69 private static final String EXPERIENCE="experience";
70 private static final String EXPERIENCE_DATE="exp_date";
71
72
73
74
75 private static final String CREATE_CONTACTS_TABLE="CREATE TABLE " + TABLE_CONTACTS + "("
76 + USER_ID + " INTEGER PRIMARY KEY," + NAME + " TEXT,"
77 + UP_NO + " TEXT," + PW + " TEXT," + XP + " INTEGER," + GENDER +" TEXT," + AGE +" AGE"+ ")";
78
79 private static final String CREATE_ACTIVITIES_TABLE= "CREATE TABLE " + TABLE_ACTIVITY + "("
80 + ACT_ID + " INTEGER PRIMARY KEY," + ACT_NAME + " TEXT," + ACT_DATE + " TEXT," + USER_ID + " INTEGER, FOREIGN KEY ("+USER_ID+") REFERENCES "+TABLE_CONTACTS+"("+TABLE_CONTACTS+") ON DELETE CASCADE" +")";
81
82 private static final String CREATE_MONITORING_TABLE= "CREATE TABLE " + TABLE_MONITORING + "("
83 + MONI_ID + " INTEGER PRIMARY KEY," + MEAN_RC + " TEXT," + OPTIMAL_TRAINING_TIME + " TEXT," + ACT_ID + " INTEGER," + USER_ID + " INTEGER, " + "FOREIGN KEY ("+ACT_ID+") REFERENCES "+TABLE_ACTIVITY+"("+TABLE_ACTIVITY+") ON DELETE CASCADE, FOREIGN KEY ("+USER_ID+") REFERENCES "+TABLE_CONTACTS+"("+TABLE_CONTACTS+") ON DELETE CASCADE" +")";
84
85 private static final String CREATE_CALIBRATION_TABLE= "CREATE TABLE " + TABLE_CALIBRATION + "("
86 + CALI_ID + " INTEGER PRIMARY KEY," + BASAL_RC + " TEXT," + CALI_DATE + " TEXT," + USER_ID + " INTEGER, FOREIGN KEY ("+USER_ID+") REFERENCES "+TABLE_CONTACTS+"("+TABLE_CONTACTS+") ON DELETE CASCADE" +")";
87
88 private static final String CREATE_YMCA_TABLE= "CREATE TABLE " + TABLE_YMCA + "("
89 + YMCA_ID + " INTEGER PRIMARY KEY," + YMCA_RC + " TEXT," + YMCA_DATE + " TEXT," + USER_ID + " INTEGER, FOREIGN KEY ("+USER_ID+") REFERENCES "+TABLE_CONTACTS+"("+TABLE_CONTACTS+") ON DELETE CASCADE" +")";
90
91 private static final String CREATE_EXPERIENCE_TABLE= "CREATE TABLE " + TABLE_EXPERIENCE + "("
92 + EXPERIENCE_ID + " INTEGER PRIMARY KEY," + EXPERIENCE + " INTEGER," + EXPERIENCE_DATE + " TEXT," + USER_ID + " INTEGER, FOREIGN KEY ("+USER_ID+") REFERENCES "+TABLE_CONTACTS+"("+TABLE_CONTACTS+") ON DELETE CASCADE" +")";
93
94
95
96 Context _context;
97
98 public DatabaseHandler(Context context) {
99 super(context, DATABASE_NAME, null, DATABASE_VERSION);
100 this._context=context;
101 }
102
103 // Creating Tables
104 @Override
105 public void onCreate(SQLiteDatabase db) {
106 db.execSQL(CREATE_CONTACTS_TABLE);
107 db.execSQL(CREATE_ACTIVITIES_TABLE);
108 db.execSQL(CREATE_MONITORING_TABLE);
109 db.execSQL(CREATE_CALIBRATION_TABLE);
110 db.execSQL(CREATE_YMCA_TABLE);
111 db.execSQL(CREATE_EXPERIENCE_TABLE);
112
113 }
114
115 // Upgrading database
116 @Override
117 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
118 // Drop older table if existed
119 db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
120 db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACTIVITY);
121 db.execSQL("DROP TABLE IF EXISTS " + TABLE_MONITORING);
122 db.execSQL("DROP TABLE IF EXISTS " + TABLE_CALIBRATION);
123 db.execSQL("DROP TABLE IF EXISTS " + TABLE_YMCA);
124 db.execSQL("DROP TABLE IF EXISTS " + TABLE_EXPERIENCE);
125
126
127
128
129
130
131 // Create tables again
132 onCreate(db);
133 }
134
135 //YMCA functions
136
137 //adding EXPERIENCE
138 public void addExperience(Experience experience){
139 SQLiteDatabase db=this.getWritableDatabase();
140
141 ContentValues values = new ContentValues();
142 values.put(EXPERIENCE_ID,experience.getEXP_id());
143 values.put(EXPERIENCE, experience.getEXP());
144 values.put(EXPERIENCE_DATE, experience.getEXP_date());
145 values.put(USER_ID, experience.getUser_id());
146
147 // Inserting Row
148 db.insert(TABLE_EXPERIENCE, null, values);
149 db.close(); // Closing database connection
150
151 };
152
153 public Experience getEXP(int user_id) {
154 SQLiteDatabase db = this.getReadableDatabase();
155
156 Cursor cursor = db.query(TABLE_EXPERIENCE, new String[] { EXPERIENCE_ID, EXPERIENCE, EXPERIENCE_DATE, USER_ID
157 }, USER_ID + "=?",
158 new String[] { String.valueOf(user_id) }, null, null, null, null);
159 if (cursor != null)
160 cursor.moveToFirst();
161
162 Experience experience = new Experience(Integer.parseInt(cursor.getString(0)), Integer.parseInt(cursor.getString(1)), cursor.getString(2),
163 Integer.parseInt(cursor.getString(3)));
164
165 return experience;
166 }
167
168 public List<Experience> getAllEXPsuserid(int user_id) {
169 List<Experience> EXPList = new ArrayList<Experience>();
170 SQLiteDatabase db = this.getReadableDatabase();
171
172
173 Cursor cursor = db.query(TABLE_EXPERIENCE, new String[] { EXPERIENCE_ID, EXPERIENCE, EXPERIENCE_DATE, USER_ID
174 }, USER_ID + "=?",
175 new String[] { String.valueOf(user_id) }, null, null, null, null);
176
177 // looping through all rows and adding to list
178 if (cursor.moveToFirst()) {
179 do {
180 Experience experience = new Experience(Integer.parseInt(cursor.getString(0)), Integer.parseInt(cursor.getString(1)), cursor.getString(2),
181 Integer.parseInt(cursor.getString(3)));
182
183
184 // Adding calibration to list
185 EXPList.add(experience);
186 } while (cursor.moveToNext());
187 }
188
189 return EXPList;
190 }
191
192 //YMCA functions
193
194 //adding YMCA
195 public void addYMCA(YMCA ymca){
196 SQLiteDatabase db=this.getWritableDatabase();
197
198 ContentValues values = new ContentValues();
199 values.put(YMCA_ID,ymca.getYMCA_id());
200 values.put(YMCA_RC, ymca.getYMCA_rc());
201 values.put(YMCA_DATE, ymca.getYMCA_date());
202 values.put(USER_ID, ymca.getUser_id());
203
204 // Inserting Row
205 db.insert(TABLE_YMCA, null, values);
206 db.close(); // Closing database connection
207
208 };
209
210 public YMCA getYMCA(int user_id) {
211 SQLiteDatabase db = this.getReadableDatabase();
212
213 Cursor cursor = db.query(TABLE_YMCA, new String[] { YMCA_ID, YMCA_RC, YMCA_DATE, USER_ID
214 }, USER_ID + "=?",
215 new String[] { String.valueOf(user_id) }, null, null, null, null);
216 if (cursor != null)
217 cursor.moveToFirst();
218
219 YMCA ymca = new YMCA(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2),
220 Integer.parseInt(cursor.getString(3)));
221
222 return ymca;
223 }
224
225 public List<YMCA> getAllYMCAsuserid(int user_id) {
226 List<YMCA> YMCAList = new ArrayList<YMCA>();
227 SQLiteDatabase db = this.getReadableDatabase();
228
229
230 Cursor cursor = db.query(TABLE_YMCA, new String[] { YMCA_ID, YMCA_RC, YMCA_DATE, USER_ID
231 }, USER_ID + "=?",
232 new String[] { String.valueOf(user_id) }, null, null, null, null);
233
234 // looping through all rows and adding to list
235 if (cursor.moveToFirst()) {
236 do {
237 YMCA ymca = new YMCA(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2),
238 Integer.parseInt(cursor.getString(3)));
239
240
241 // Adding calibration to list
242 YMCAList.add(ymca);
243 } while (cursor.moveToNext());
244 }
245
246 return YMCAList;
247 }
248
249//calibration functions
250
251 //adding calibration
252 public void addCalibration(Calibration calibration){
253 SQLiteDatabase db=this.getWritableDatabase();
254
255 ContentValues values = new ContentValues();
256 values.put(CALI_ID,calibration.getCali_id());
257 values.put(BASAL_RC, calibration.getBasal_rc());
258 values.put(CALI_DATE, calibration.getCali_date());
259 values.put(USER_ID, calibration.getUser_id());
260
261 // Inserting Row
262 db.insert(TABLE_CALIBRATION, null, values);
263 db.close(); // Closing database connection
264
265 };
266
267 public Calibration getCalibration(int user_id) {
268 SQLiteDatabase db = this.getReadableDatabase();
269
270 Cursor cursor = db.query(TABLE_CALIBRATION, new String[] { CALI_ID, BASAL_RC, CALI_DATE, USER_ID
271 }, USER_ID + "=?",
272 new String[] { String.valueOf(user_id) }, null, null, null, null);
273 if (cursor != null)
274 cursor.moveToFirst();
275
276 Calibration calibration = new Calibration(Integer.parseInt(cursor.getString(0)), Double.parseDouble(cursor.getString(1)), cursor.getString(2),
277 Integer.parseInt(cursor.getString(3)));
278
279 return calibration;
280 }
281
282 public List<Calibration> getAllCalibrations_userid(int user_id) {
283 List<Calibration> caliList = new ArrayList<Calibration>();
284 SQLiteDatabase db = this.getReadableDatabase();
285
286
287 Cursor cursor = db.query(TABLE_CALIBRATION, new String[] { CALI_ID, BASAL_RC, CALI_DATE, USER_ID
288 }, USER_ID + "=?",
289 new String[] { String.valueOf(user_id) }, null, null, null, null);
290
291 // looping through all rows and adding to list
292 if (cursor.moveToFirst()) {
293 do {
294 Calibration calibration = new Calibration(Integer.parseInt(cursor.getString(0)), Double.parseDouble(cursor.getString(1)), cursor.getString(2),
295 Integer.parseInt(cursor.getString(3)));
296
297
298 // Adding calibration to list
299 caliList.add(calibration);
300 } while (cursor.moveToNext());
301 }
302
303 return caliList;
304 }
305
306// monitoring functions
307
308 //adding monitoring
309 public void addMonitoring(TrainingStatistics monitoring){
310 SQLiteDatabase db=this.getWritableDatabase();
311
312 ContentValues values = new ContentValues();
313 values.put(MONI_ID,monitoring.getMoni_id());
314 values.put(MEAN_RC, monitoring.getMean_rc());
315 values.put(OPTIMAL_TRAINING_TIME, monitoring.getOptimal_training_time());
316 values.put(ACT_ID, monitoring.getActivity_id());
317 values.put(USER_ID, monitoring.getUser_id());
318
319 // Inserting Row
320 db.insert(TABLE_ACTIVITY, null, values);
321 db.close(); // Closing database connection
322
323 };
324
325 public TrainingStatistics getTrainingStatistics(int user_id) {
326 SQLiteDatabase db = this.getReadableDatabase();
327
328 Cursor cursor = db.query(TABLE_MONITORING, new String[] { MONI_ID, MEAN_RC, OPTIMAL_TRAINING_TIME, ACT_ID, USER_ID
329 }, USER_ID + "=?",
330 new String[] { String.valueOf(user_id) }, null, null, null, null);
331 if (cursor != null)
332 cursor.moveToFirst();
333
334 TrainingStatistics statistics = new TrainingStatistics(Integer.parseInt(cursor.getString(0)), Double.parseDouble(cursor.getString(1)), Double.parseDouble(cursor.getString(2)),
335 Integer.parseInt(cursor.getString(3)), Integer.parseInt(cursor.getString(4)));
336
337 return statistics;
338 }
339
340 public List<TrainingStatistics> getAllStatistics_userid(int user_id) {
341 List<TrainingStatistics> statList = new ArrayList<TrainingStatistics>();
342 SQLiteDatabase db = this.getReadableDatabase();
343
344
345 Cursor cursor = db.query(TABLE_MONITORING, new String[] { MONI_ID, MEAN_RC, OPTIMAL_TRAINING_TIME, ACT_ID,USER_ID
346 }, USER_ID + "=?",
347 new String[] { String.valueOf(user_id) }, null, null, null, null);
348
349 // looping through all rows and adding to list
350 if (cursor.moveToFirst()) {
351 do {
352 TrainingStatistics trainingStatistics = new TrainingStatistics();
353 trainingStatistics.setMoni_id(Integer.parseInt(cursor.getString(0)));
354 trainingStatistics.setMean_rc(Double.parseDouble(cursor.getString(1)));
355 trainingStatistics.setOptimal_training_time(Double.parseDouble(cursor.getString(2)));
356 trainingStatistics.setActivity_id(Integer.parseInt(cursor.getString(3)));
357 trainingStatistics.setUser_id(Integer.parseInt(cursor.getString(4)));
358
359
360 // Adding statistics to list
361 statList.add(trainingStatistics);
362 } while (cursor.moveToNext());
363 }
364
365 return statList;
366 }
367
368
369
370
371
372
373// activity functions
374
375 //adding activity
376 public void addActivity(Phys_Activity activity){
377 SQLiteDatabase db=this.getWritableDatabase();
378
379 ContentValues values = new ContentValues();
380 values.put(ACT_NAME,activity.getName());
381 values.put(USER_ID, activity.getUser_id());
382 values.put(ACT_DATE, activity.getDate());
383
384 // Inserting Row
385 db.insert(TABLE_ACTIVITY, null, values);
386 db.close(); // Closing database connection
387
388 };
389
390
391 public Phys_Activity getActivity(int user_id) {
392 SQLiteDatabase db = this.getReadableDatabase();
393
394 Cursor cursor = db.query(TABLE_ACTIVITY, new String[] { ACT_ID, ACT_NAME, ACT_DATE, USER_ID
395 }, USER_ID + "=?",
396 new String[] { String.valueOf(user_id) }, null, null, null, null);
397 if (cursor != null)
398 cursor.moveToFirst();
399
400 Phys_Activity activity = new Phys_Activity(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2),
401 Integer.parseInt(cursor.getString(3)));
402
403 return activity;
404 }
405
406 public List<Phys_Activity> getAllActivities_userid(int user_id) {
407 List<Phys_Activity> actList = new ArrayList<Phys_Activity>();
408 SQLiteDatabase db = this.getReadableDatabase();
409
410
411 Cursor cursor = db.query(TABLE_ACTIVITY, new String[] { ACT_ID, ACT_NAME, ACT_DATE, USER_ID
412 }, USER_ID + "=?",
413 new String[] { String.valueOf(user_id) }, null, null, null, null);
414
415 // looping through all rows and adding to list
416 if (cursor.moveToFirst()) {
417 do {
418 Phys_Activity physicalAct = new Phys_Activity();
419 physicalAct.setAct_id(Integer.parseInt(cursor.getString(0)));
420 physicalAct.setName(cursor.getString(1));
421 physicalAct.setDate(cursor.getString(2));
422 physicalAct.setUser_id(Integer.parseInt(cursor.getString(3)));
423
424 // Adding activity to list
425 actList.add(physicalAct);
426 } while (cursor.moveToNext());
427 }
428
429 return actList;
430 }
431
432
433
434
435
436
437
438
439
440
441
442
443// user functions
444
445
446
447 //adding user
448
449 public void addUser(User user) {
450 SQLiteDatabase db = this.getWritableDatabase();
451
452 ContentValues values = new ContentValues();
453 values.put(NAME, user.getName()); // Contact Name
454 values.put(UP_NO, user.getupNumber()); // Contact Phone Number
455 values.put(PW, user.getPassword());
456 values.put(XP, user.getExperience());
457 values.put(GENDER, user.getGender());
458 values.put(AGE, user.getAge());
459
460 // Inserting Row
461 db.insert(TABLE_CONTACTS, null, values);
462 db.close(); // Closing database connection
463 }
464
465
466
467 // Updating single user
468 public int updateUser(User user) {
469 SQLiteDatabase db = this.getWritableDatabase();
470
471 ContentValues values = new ContentValues();
472 values.put(NAME, user.getName());
473 values.put(PW, user.getPassword());
474 values.put(UP_NO, user.getupNumber());
475 values.put(XP, user.getExperience());
476 values.put(GENDER, user.getGender());
477 values.put(AGE, user.getAge());
478
479 // updating row
480 return db.update(TABLE_CONTACTS, values, USER_ID + " = ?",
481 new String[] { String.valueOf(user.getId()) });
482 }
483
484 // Getting single contact
485 public User getUser(int id) {
486 SQLiteDatabase db = this.getReadableDatabase();
487
488 Cursor cursor = db.query(TABLE_CONTACTS, new String[] { USER_ID,
489 NAME, UP_NO, PW, XP, GENDER, AGE }, USER_ID + "=?",
490 new String[] { String.valueOf(id) }, null, null, null, null);
491 if (cursor != null)
492 cursor.moveToFirst();
493
494 User user = new User(Integer.parseInt(cursor.getString(0)),
495 cursor.getString(1), cursor.getString(2), cursor.getString(3), Integer.parseInt(cursor.getString(4)), cursor.getString(5), Integer.parseInt(cursor.getString(6)));
496 // return user
497 return user;
498 }
499
500 // Getting All Contacts
501 public List<User> getAllUsers() {
502 List<User> userList = new ArrayList<User>();
503 // Select All Query
504 String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;
505
506 SQLiteDatabase db = this.getWritableDatabase();
507 Cursor cursor = db.rawQuery(selectQuery, null);
508
509 // looping through all rows and adding to list
510 if (cursor.moveToFirst()) {
511 do {
512 User user = new User();
513 user.setId(Integer.parseInt(cursor.getString(0)));
514 user.setName(cursor.getString(1));
515 user.setUpNumber(cursor.getString(2));
516 user.setPassword(cursor.getString(3));
517 user.setExperience(Integer.parseInt(cursor.getString(4)));
518 user.setGender(cursor.getString(5));
519 user.setAge(Integer.parseInt(cursor.getString(6)));
520
521 // Adding user to list
522 userList.add(user);
523 } while (cursor.moveToNext());
524 }
525
526 // return user list
527 return userList;
528 }
529
530 // Getting users Count
531 public int getUsersCount() {
532 String countQuery = "SELECT * FROM " + TABLE_CONTACTS;
533 SQLiteDatabase db = this.getReadableDatabase();
534 Cursor cursor = db.rawQuery(countQuery, null);
535 cursor.close();
536
537 // return count
538 return cursor.getCount();
539 }
540
541
542 // Deleting single contact
543 public void deleteContact(User user) {
544 SQLiteDatabase db = this.getWritableDatabase();
545 db.delete(TABLE_CONTACTS, USER_ID + " = ?",
546 new String[] { String.valueOf(user.getId()) });
547 db.close();
548 }
549
550 // public User findLoggedUserInfo()
551 // {
552
553 // }
554
555 public void createLoginSession(){
556 // Storing login value as TRUE
557 isLogin = true;
558
559 }
560
561 public void checkLogin() {
562 // Check login status
563 if (isLogin!=null) {
564
565 // If the user is not logged in, redirect him to Login Activity
566 Intent i = new Intent(_context, LoginActivity.class);
567 // Closing all the Activities
568 i.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
569
570 // Add new Flag to start new Activity
571 i.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
572
573 // Staring Login Activity
574 _context.startActivity(i);
575 }
576 }
577
578 public void logoutUser(){
579 // Clearing all data from Shared Preferences
580 // editor.clear();
581 // editor.commit();
582
583 isLogin=false;
584
585 // After logout redirect user to Login Activity
586 Intent i = new Intent(_context, LoginActivity.class);
587 // Closing all the Activities
588 i.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
589
590 // Add new Flag to start new Activity
591 i.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
592
593 // Staring Login Activity
594 _context.startActivity(i);
595 }
596
597
598}