· 9 years ago · Feb 07, 2017, 12:56 PM
1sqliteDb.java
2=========================================
3package comq.example.amitk.a1_2_sampleapp_home.databases;
4
5import android.content.ContentValues;
6import android.content.Context;
7import android.database.Cursor;
8import android.database.SQLException;
9import android.database.sqlite.SQLiteDatabase;
10import android.database.sqlite.SQLiteOpenHelper;
11import android.util.Log;
12
13import comq.example.amitk.a1_2_sampleapp_home.databases.models.taskDetalis;
14import comq.example.amitk.a1_2_sampleapp_home.databases.models.userDetails;
15
16import static android.content.ContentValues.TAG;
17
18
19public class sqliteDb extends SQLiteOpenHelper {
20
21
22// Logcat tag
23 private static final String LOG = "sqliteDatabaseHelper";
24
25 // Database Version
26 public static final int DATABASE_VERSION = 1;
27
28 // Database Name
29 public static final String DATABASE_NAME = "taskApp.db";
30
31 SQLiteDatabase db;
32 Context context;
33
34 // Table Names
35 private static final String TABLE_USERS = "users";
36 private static final String TABLE_TASKS = "tasks";
37 private static final String TABLE_USER_TASK = "users_task";
38
39 private static final String KEY_ID = "id";
40
41 // Users Table - column names
42 private static final String USERNAME="UserName";
43 private static final String PASSWORD="password";
44 private static final String EMAIL="email";
45 private static final String NAME="name";
46 private static final String GENDER="gender";
47 private static final String COUNTRY="country";
48 private static final String PHOTO="photo";
49
50
51 // Task Table - column names
52
53 private static final String TASKNAME = "task_name";
54 private static final String TASKDETALIS= "task_detalis";
55 private static final String TASKSTARTDATE="task_start_date";
56 private static final String TASKSTARTHOURE="task_start_hour";
57 private static final String TASKSESTIMATEDENDDATE="task_estimated_end_date";
58 private static final String TASKSESTIMATEDENDHOUR="task_estimated_end_hour";
59 private static final String TASKENDDATE="task_end_date";
60 private static final String TASKENDHOUR="task_end_hour";
61 private static final String ISTASKDONE="is_task_done";
62
63 // user_task Table - column names
64 private static final String KEY_USER_ID = "user_id";
65 private static final String KEY_TASK_ID = "task_id";
66
67 // Table users Create Statements
68 private static final String CREATE_TABLE_USERS = "CREATE TABLE IF NOT EXISTS "
69 + TABLE_USERS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + USERNAME
70 + " TEXT," + PASSWORD + " TEXT," + EMAIL
71 + " TEXT," + NAME + " TEXT," + GENDER
72 + " TEXT," + COUNTRY + " TEXT," + PHOTO
73 + " BLOB"+ ")";
74
75 // TASK table create statement
76
77 private static final String CREATE_TABLE_TASKS = "CREATE TABLE IF NOT EXISTS " + TABLE_TASKS
78 + "(" + KEY_ID + " INTEGER PRIMARY KEY," + USERNAME + " TEXT,"+ TASKNAME + " TEXT,"
79 + TASKDETALIS + " TEXT," + TASKSTARTDATE + " DATETIME," + TASKSESTIMATEDENDDATE + " DATETIME,"
80 + TASKENDDATE + " DATETIME,"+ ISTASKDONE + " INTEGER" + ")";
81
82 //constructor
83public sqliteDb(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
84 super(context, DATABASE_NAME, null, DATABASE_VERSION);
85 this.context=context;
86 this.db=getWritableDatabase();
87 }
88
89
90 @Override
91 public void onCreate(SQLiteDatabase db) {
92
93 db.execSQL(CREATE_TABLE_USERS);
94 db.execSQL(CREATE_TABLE_TASKS);
95
96 Log.e(TAG,"tables\n"+"users:"+CREATE_TABLE_USERS+"\ntasks:"+CREATE_TABLE_TASKS);
97 }
98
99 @Override
100 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
101
102 db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
103 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
104
105 // create new tables
106 onCreate(db);
107 }
108
109 //*****************************************users*****************************************************************//
110
111 //check if user exists
112 public boolean checkUser(String username) throws SQLException {
113 Cursor c = db.rawQuery("SELECT * FROM "+TABLE_USERS+" WHERE "+USERNAME+ "= '" + username + "'", null);
114 if (c.getCount() > 0) {
115 return true;
116 }
117 else{
118 return false;
119 }
120 }
121
122 //check if email already exists
123 public boolean checkEmail(String email) throws SQLException {
124 Cursor c = db.rawQuery("SELECT * FROM "+TABLE_USERS+" WHERE "+EMAIL+ " = '" + email + "'", null);
125 if (c.getCount() > 0) {
126 return true;
127 }
128 else{
129 return false;
130 }
131 }
132
133 //get password for given user
134 public String getPassword(String userName)
135 {
136 Cursor cursor=db.query(TABLE_USERS, null, " userName=?", new String[]{userName}, null, null, null);
137
138 cursor.moveToFirst();
139 String password= cursor.getString(cursor.getColumnIndex(PASSWORD));
140 cursor.close();
141 return password;
142 }
143
144 public void addUser(userDetails u)
145 {
146 try {
147
148 ContentValues values = new ContentValues();
149 values.put(USERNAME,u.getUsername());
150 values.put(EMAIL,u.getMail());
151 values.put(PASSWORD,u.getPassword());
152
153 db.insert(TABLE_USERS,null,values);
154
155 } catch (Exception e) {
156 Log.e(TAG, e.getMessage().toString());
157 }
158
159
160 }
161
162 //*****************************************tasks*****************************************************************//
163
164 public boolean checkTaskName(String taskName) throws SQLException {
165 Cursor c = db.rawQuery("SELECT * FROM "+TABLE_TASKS+" WHERE "+TASKNAME+ "= '" + taskName + "'", null);
166 if (c.getCount() > 0) {
167 return true;
168 }
169 else{
170 return false;
171 }
172 }
173 public void addTask(taskDetalis t)
174 {
175 try {
176 ContentValues values = new ContentValues();
177 values.put(USERNAME,t.getUserName());
178 values.put(TASKNAME,t.getTaskName());
179 values.put(TASKDETALIS,t.getTaskDetalis());
180 values.put(TASKSTARTDATE, String.valueOf(t.getStartDate()));
181 values.put(TASKSESTIMATEDENDDATE, String.valueOf(t.getEstimatedEndDate()));
182 values.put(TASKENDDATE, String.valueOf(t.getEndDate()));
183 values.put(ISTASKDONE, (t.isTaskDone())? 1 : 0);
184
185 db.insert(TABLE_TASKS,null,values);
186
187 } catch (Exception e) {
188 Log.e(TAG, e.getMessage().toString());
189 }
190 }
191
192// get all task for a given user
193 public Cursor getAllTasksForUser(String userName)
194 {
195 String query="SELECT * FROM "+TABLE_TASKS+" WHERE "+USERNAME+ "= '" + userName + "'";
196 Cursor cursor=db.rawQuery(query,null);
197 return cursor;
198 }
199
200
201 public Cursor getFinishedTasksForUser(String userName,int status)
202 {
203 String query="SELECT * FROM "+TABLE_TASKS+" WHERE "+USERNAME+ "= '" + userName + "'"+" AND "+ISTASKDONE+" = "+ status;
204 Cursor cursor=db.rawQuery(query,null);
205 return cursor;
206 }
207 //get current task
208 public Cursor getCurrentTasksForUser(String userName,int status)
209 {
210 String query="SELECT * FROM "+TABLE_TASKS+" WHERE "+USERNAME+ "= '" + userName + "'"+" AND "+ISTASKDONE+" = "+ status;
211 Cursor cursor=db.rawQuery(query,null);
212 return cursor;
213 }
214
215 public Cursor getTasksByDate(String userName)
216 {
217 String query="SELECT * FROM "+TABLE_TASKS+" WHERE "+USERNAME+ "= '" + userName + "'"+" ORDER BY "+ TASKSTARTDATE +" ASC";
218 Cursor cursor=db.rawQuery(query,null);
219 return cursor;
220 }
221 public void updateTaskFinish(String taskName){
222 String query="UPDATE "+TABLE_TASKS+" SET "+ISTASKDONE+ " = "+ 1 +" WHERE "+TASKNAME+ "= '" + taskName + "'";
223 db.execSQL(query);
224 db.close();
225 }
226 public void updateTaskResume(String taskName){
227 String query="UPDATE "+TABLE_TASKS+" SET "+ISTASKDONE+ " = "+ 0 +" WHERE "+TASKNAME+ "= '" + taskName + "'";
228 db.execSQL(query);
229 db.close();
230 }
231
232 public void updateTaskEndDate(String taskName, String endDate){
233 String query="UPDATE "+TABLE_TASKS+" SET "+TASKENDDATE+ " = "+ endDate +" WHERE "+TASKNAME+ "= '" + taskName + "'";
234 db.execSQL(query);
235 db.close();
236 }
237
238 public void deleteTask(String taskName) {
239 db.delete(TABLE_TASKS, TASKNAME + " = ?",
240 new String[] { taskName});
241 db.close();
242 }
243 public void removeAllTasks(String userName)
244 {
245 String query = "delete from "+TABLE_TASKS+" WHERE "+USERNAME+ "= '" + userName + "'";
246 db.execSQL(query);
247 db.close();
248 }
249
250
251}