· 6 years ago · Dec 02, 2019, 02:22 PM
1package com.example.todo;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.widget.EditText;
9
10import java.sql.Timestamp;
11import java.util.ArrayList;
12
13public class SqliteHelper extends SQLiteOpenHelper {
14 private ArrayList<Task> activeTasks = new ArrayList<Task>();
15 private ArrayList<Task> userActiveTasks = new ArrayList<Task>();
16
17 //DATABASE NAME
18 public static final String DATABASE_NAME = "todo.com";
19 public static final int DATABASE_VERSION = 1;
20
21 //TABLE NAME
22 public static final String TABLE_USERS = "users";
23 public static final String KEY_ID = "id";
24 public static final String KEY_USER_NAME = "login";
25 public static final String KEY_EMAIL = "email";
26 public static final String KEY_PASSWORD = "password";
27
28 public static final String SQL_TABLE_USERS = " CREATE TABLE " + TABLE_USERS
29 + " ( "
30 + KEY_ID + " INTEGER PRIMARY KEY, "
31 + KEY_USER_NAME + " TEXT, "
32 + KEY_EMAIL + " TEXT, "
33 + KEY_PASSWORD + " TEXT"
34 + " ) ";
35
36 //TABLE NAME
37 public static final String TABLE_A_TASKS = "active_tasks";
38 public static final String KEY_A_TASK_ID = "id";
39 public static final String KEY_A_TASKS_USERID = "user_ID";
40 public static final String KEY_A_TASKS_NAME = "name";
41 public static final String KEY_A_TIME = "creation_time";
42 public static final String KEY_A_STATUS = "status";
43
44 public static final String SQL_TABLE_A_TASKS = " CREATE TABLE " + TABLE_A_TASKS
45 + " ( "
46 + KEY_A_TASK_ID + " INTEGER PRIMARY KEY, "
47 + KEY_A_TASKS_NAME + " TEXT, "
48 + KEY_A_TASKS_USERID + " TEXT, "
49 + KEY_A_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, "
50 + KEY_A_STATUS + " TEXT"
51 + " ) ";
52
53 public SqliteHelper(Context context) {
54 super(context, DATABASE_NAME, null, DATABASE_VERSION);
55 }
56
57 @Override
58 public void onCreate(SQLiteDatabase sqLiteDatabase) {
59 sqLiteDatabase.execSQL(SQL_TABLE_USERS);
60 }
61
62 @Override
63 public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
64 //drop table to create new one if database version updated
65 sqLiteDatabase.execSQL(" DROP TABLE IF EXISTS " + TABLE_USERS);
66 sqLiteDatabase.execSQL(" DROP TABLE IF EXISTS " + TABLE_A_TASKS);
67 }
68
69 //using this method we can add users to user table
70 public void addUser(User user) {
71 SQLiteDatabase db = this.getWritableDatabase();
72
73 ContentValues values = new ContentValues();
74
75 values.put(KEY_USER_NAME, user.getLogin());
76 values.put(KEY_EMAIL, user.getEmail());
77 values.put(KEY_PASSWORD, user.getPassword());
78
79 db.insert(TABLE_USERS, null, values);
80 }
81
82 public String getLogin(String id) {
83 SQLiteDatabase db = this.getReadableDatabase();
84 Cursor cursor = db.query(TABLE_USERS,// Selecting Table
85 new String[]{KEY_ID, KEY_USER_NAME},//Selecting columns want to query
86 KEY_ID + "=?",
87 new String[]{id},//Where clause
88 null, null, null);
89
90 if (cursor != null && cursor.moveToFirst() && cursor.getCount() > 0) {
91 return cursor.getString(1);
92 }
93
94 return null;
95 }
96
97 public boolean isLoginExists(String login) {
98 SQLiteDatabase db = this.getReadableDatabase();
99 Cursor cursor = db.query(TABLE_USERS,// Selecting Table
100 new String[]{KEY_ID, KEY_USER_NAME},//Selecting columns want to query
101 KEY_USER_NAME + "=?",
102 new String[]{login},//Where clause
103 null, null, null);
104
105 if (cursor != null && cursor.moveToFirst() && cursor.getCount() > 0) {
106 return true;
107 }
108
109 return false;
110 }
111
112 public String isUserValid(String login, String password) {
113 SQLiteDatabase db = this.getReadableDatabase();
114 Cursor cursor = db.query(TABLE_USERS,// Selecting Table
115 new String[]{KEY_ID, KEY_USER_NAME, KEY_PASSWORD},//Selecting columns want to query
116 KEY_USER_NAME + "=?",
117 new String[]{login},//Where clause
118 null, null, null);
119
120 if (cursor != null && cursor.moveToFirst() && cursor.getCount() > 0) {
121 //Match both passwords check they are same or not
122 if (password.equals(cursor.getString(2))) {
123 return cursor.getString(0);
124 }
125 }
126
127 return null;
128 }
129
130 public ArrayList<Task> getUserActiveTasks(String userID) {
131 SQLiteDatabase db = this.getReadableDatabase();
132 initTasksInDB(db);
133 String[] columns = new String[] { KEY_A_TASK_ID, KEY_A_TASKS_NAME, KEY_A_TIME };
134 Cursor cursor = db.query(TABLE_A_TASKS,// Selecting Table
135 null,//Selecting columns want to query
136 KEY_A_TASKS_USERID + "= ? AND " + KEY_A_STATUS + "= ? ",
137 new String[]{userID, "1"},//Where clause
138 null, null, null);
139
140 if (cursor.moveToFirst()) {
141 do {
142 userActiveTasks.add(new Task(cursor.getString(cursor.getColumnIndex(KEY_A_TASK_ID)), userID, cursor.getString(cursor.getColumnIndex(KEY_A_TASKS_NAME)), cursor.getString(cursor.getColumnIndex(KEY_A_TIME)), 1));
143 } while (cursor.moveToNext());
144 }
145 cursor.close();
146 return userActiveTasks;
147 }
148
149 public void addTask(String name, String userID, Integer status) {
150 SQLiteDatabase db = this.getWritableDatabase();
151
152 ContentValues values = new ContentValues();
153
154 values.put(KEY_A_TASKS_NAME, name);
155 values.put(KEY_A_TASKS_USERID, userID);
156 values.put(KEY_A_STATUS, status);
157
158 db.insert(TABLE_USERS, null, values);
159 }
160
161 public void archiveTask(String taskName, String userID) {
162 SQLiteDatabase db = this.getWritableDatabase();
163 ContentValues values = new ContentValues();
164 values.put(KEY_A_STATUS, "0");
165
166 db.update(TABLE_A_TASKS, values, KEY_A_TASKS_NAME + " =? AND " + KEY_A_TASKS_USERID + "=?", new String[]{taskName, userID});
167 }
168
169 public void initTasksInDB(SQLiteDatabase db) {
170 db.execSQL(SQL_TABLE_A_TASKS);
171 for (int i = 0; i < 30; i++) {
172 for (int j = 1; j < 5; j++) {
173 activeTasks.add(new Task("", Integer.toString(i), "Task " + j, "", 1));
174 activeTasks.add(new Task("", Integer.toString(i), "Task " + j, "", 0));
175 }
176 }
177
178 for(Task activeTask: activeTasks) {
179 ContentValues values = new ContentValues();
180
181 values.put(KEY_A_TASKS_NAME, activeTask.getName());
182 values.put(KEY_A_TASKS_USERID, activeTask.getUserID());
183 values.put(KEY_A_STATUS, Integer.toString(activeTask.getStatus()));
184
185 db.insert(TABLE_A_TASKS, null, values);
186 }
187 }
188}