· 6 years ago · Jul 01, 2019, 03:50 PM
1public class DbHelper extends SQLiteOpenHelper {
2
3 public static final int DATABASE_VERSION = 13;
4 public static final String DATABASE_NAME = "LoinReg.db";
5
6 public DbHelper(Context context) {
7 super(context, DATABASE_NAME, null, DATABASE_VERSION);
8 }
9
10 @Override
11 public void onCreate(SQLiteDatabase db) {
12 db.execSQL(SQL_CREATE_REG_ENTRIES);
13 db.execSQL(SQL_CREATE_FORM_ENTRIES);
14 db.execSQL(SQL_ADD_HISTORY);
15 }
16
17 @Override
18 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
19
20 if(newVersion > oldVersion){
21 db.execSQL(SQL_REG_DELETE_ENTRIES);
22 db.execSQL(SQL_FORM_DELETE);
23 db.execSQL(SQL_HISTORY_DELETE);
24 onCreate(db);
25 }
26 }
27
28 private static final String SQL_CREATE_FORM_ENTRIES =
29 "CREATE TABLE IF NOT EXISTS " + FormEntry.COLUMN_TABLE_NAME +
30 " (" + FormEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
31 + FormEntry.COLUMN_NAME_NAME + " TEXT,"
32 + FormEntry.COLUMN_NAME_EMAIL + " TEXT,"
33 + FormEntry.COLUMN_NAME_MOBILE + " TEXT,"
34 + FormEntry.COLUMN_NAME_ALTERNATE_NUMBER + " TEXT,"
35 + FormEntry.COLUMN_NAME_JEE + " TEXT,"
36 + COLUNM_NAME_JEERANK + " TEXT,"
37 + FormEntry.COLUMN_NAME_PERCENTAGE + " TEXT,"
38 + FormEntry.COLUMN_NAME_CITY + " TEXT,"
39 + FormEntry.COLUMN_NAME_DEPT + " TEXT,"
40 + FormEntry.COLUMN_NAME_SOURCE + " TEXT,"
41 + FormEntry.TIMESTAMP + " INTEGER )";
42 public boolean insertForm(String name, String email, String phone, String alternate, String jee, String jeeRank, String percentage, String city, String dept, String source){
43 SQLiteDatabase db = this.getWritableDatabase();
44
45 ContentValues values = new ContentValues();
46 values.put(FormContract.FormEntry.COLUMN_NAME_NAME,name);
47 values.put(FormContract.FormEntry.COLUMN_NAME_EMAIL,email);
48 values.put(FormContract.FormEntry.COLUMN_NAME_MOBILE,phone);
49 values.put(FormContract.FormEntry.COLUMN_NAME_ALTERNATE_NUMBER, alternate);
50 values.put(FormContract.FormEntry.COLUMN_NAME_JEE,jee);
51 values.put(FormContract.FormEntry.COLUNM_NAME_JEERANK,jeeRank);
52 values.put(FormContract.FormEntry.COLUMN_NAME_PERCENTAGE,percentage);
53 values.put(FormContract.FormEntry.COLUMN_NAME_CITY,city);
54 values.put(FormContract.FormEntry.COLUMN_NAME_DEPT,dept);
55 values.put(FormContract.FormEntry.COLUMN_NAME_SOURCE,source);
56 values.put(FormEntry.TIMESTAMP,System.currentTimeMillis());
57
58 long newRowId = db.insert(FormContract.FormEntry.COLUMN_TABLE_NAME,null,values);
59
60 if(newRowId == -1){
61 return false;
62 }else{
63 return true;
64 }
65}
66}
67
68public class MainActivity extends AppCompatActivity
69 implements NavigationView.OnNavigationItemSelectedListener, EnquiryAdapter.OnItemClickListener {
70
71 DbHelper mDbHelper;
72 public static final String FORM_ID = "form_id";
73 public static final String SQL = "sql";
74 public static final String DEPT = "dept";
75 public static final String SOURCE = "source";
76 public static final String STARTDATE = "start_date";
77 public static final String ENDDATE = "end_date";
78 private static String sql, department, source;
79 long StartDate, EndDate;
80
81
82 private RecyclerView mRecyclerView;
83 private EnquiryAdapter mAdapter;
84 private EditText mStartDate, mEndDate;
85 private Spinner mDeptSpinner, mSourceSpinner;
86
87
88 @Override
89 protected void onCreate(Bundle savedInstanceState) {
90 super.onCreate(savedInstanceState);
91 setContentView(R.layout.activity_main);
92 Toolbar toolbar = findViewById(R.id.toolbar);
93 setSupportActionBar(toolbar);
94 mDbHelper = new DbHelper(this);
95
96
97 FloatingActionButton fab = findViewById(R.id.fab);
98 fab.setOnClickListener(new View.OnClickListener() {
99 @Override
100 public void onClick(View view) {
101 Intent intent = new Intent(MainActivity.this,FormActivity.class);
102 startActivity(intent);
103 }
104 });
105
106 DrawerLayout drawer = findViewById(R.id.drawer_layout);
107 ActionBarDrawerToggle toggle = new ActionBarDrawerToggle(
108 this, drawer, toolbar, R.string.navigation_drawer_open, R.string.navigation_drawer_close);
109 drawer.addDrawerListener(toggle);
110 toggle.syncState();
111
112 NavigationView navigationView = findViewById(R.id.nav_view);
113 navigationView.setNavigationItemSelectedListener(this);
114 View headerView = navigationView.getHeaderView(0);
115
116 Intent intent = getIntent();
117 String email = intent.getStringExtra(LoginActivity.EXTRA_EMAIL);
118 TextView mEmail = headerView.findViewById(R.id.email);
119 mEmail.setText(email);
120
121 //RecyclerView Setup
122 mRecyclerView = findViewById(R.id.recyclerview);
123 mRecyclerView.setHasFixedSize(true);
124 mRecyclerView.setLayoutManager(new LinearLayoutManager(this));
125 mAdapter = new EnquiryAdapter(MainActivity.this,mDbHelper.getFormData());
126 mRecyclerView.setAdapter(mAdapter);
127 mAdapter.setOnItemClickListener(MainActivity.this);
128 }
129private void showFilterDialog(){
130
131 LayoutInflater layoutInflater = LayoutInflater.from(MainActivity.this);
132 View filterDialog = layoutInflater.inflate(R.layout.filters,null);
133
134 /**
135 * getting the reference to the @View
136 * */
137 mStartDate = filterDialog.findViewById(R.id.editText);
138 mEndDate = filterDialog.findViewById(R.id.editText2);
139 mDeptSpinner = filterDialog.findViewById(R.id.filter_dept);
140 mSourceSpinner = filterDialog.findViewById(R.id.filter_source);
141
142 AlertDialog.Builder alertBuilder = new AlertDialog.Builder(MainActivity.this);
143 alertBuilder.setTitle("Filters");
144 alertBuilder.setView(filterDialog);
145
146
147 mStartDate.setOnClickListener(new View.OnClickListener() {
148 @Override
149 public void onClick(View v) {
150 showDatePicker(v);
151 hideKeyboard(v);
152
153 }
154 });
155
156 mEndDate.setOnClickListener(new View.OnClickListener() {
157 @Override
158 public void onClick(View v) {
159 showEndDatePicker(v);
160 hideKeyboard(v);
161 }
162 });
163
164 /**
165 * Assigning the list of items to the @Spinner.
166 * */
167 ArrayAdapter<CharSequence> deptAdapter = ArrayAdapter.createFromResource(this,R.array.Department,android.R.layout.simple_spinner_dropdown_item);
168 deptAdapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
169 mDeptSpinner.setAdapter(deptAdapter);
170
171 ArrayAdapter<CharSequence> sourceAdapter = ArrayAdapter.createFromResource(this,R.array.Source,android.R.layout.simple_spinner_dropdown_item);
172 sourceAdapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
173 mSourceSpinner.setAdapter(sourceAdapter);
174
175 /**
176 * @sql statement.
177 * */
178 sql = "Select * from " +FormEntry.COLUMN_TABLE_NAME+ " where ";
179
180
181 alertBuilder.setPositiveButton("Apply", new DialogInterface.OnClickListener() {
182 @Override
183 public void onClick(DialogInterface dialog, int which) {
184
185 Log.d("Date in milisec", String.valueOf(StartDate));
186 Log.d("Date 2 in milisec", String.valueOf(EndDate));
187
188 if(sd != null && ed != null){
189
190
191 sql = sql + FormEntry.TIMESTAMP+ " BETWEEN" + " ?" + " AND" + " ?";
192 }else{
193 sql = "Select * from " +FormEntry.COLUMN_TABLE_NAME+ " where ";
194 }
195
196 department = mDeptSpinner.getSelectedItem().toString();
197 source = mSourceSpinner.getSelectedItem().toString();
198
199 if(mDeptSpinner.getSelectedItem() != null){
200 sql = sql + FormEntry.COLUMN_NAME_DEPT + " = ?";
201 }
202
203 if(mSourceSpinner.getSelectedItem() != null){
204 sql = sql + " OR "+ FormEntry.COLUMN_NAME_SOURCE + " = ?";
205 }
206
207 Intent intent = new Intent(MainActivity.this, FilterActivity.class);
208
209
210 intent.putExtra(STARTDATE, StartDate);
211 intent.putExtra(ENDDATE, EndDate);
212 intent.putExtra(DEPT,department);
213 intent.putExtra(SOURCE, source);
214 intent.putExtra(SQL,sql);
215
216 startActivity(intent);
217 }
218 })
219 .setNegativeButton("Cancel",
220 new DialogInterface.OnClickListener() {
221 public void onClick(DialogInterface dialog, int id) {
222 dialog.cancel();
223 }
224 });
225
226 AlertDialog alertDialog = alertBuilder.create();
227 alertDialog.show();
228 Button pos = alertDialog.getButton(DialogInterface.BUTTON_POSITIVE);
229 pos.setTextColor(Color.BLUE);
230 Button neg = alertDialog.getButton(DialogInterface.BUTTON_NEGATIVE);
231 neg.setTextColor(Color.BLUE);
232 }
233 public void showDatePicker(View view) {
234 DialogFragment newFragment = new DateFragment();
235 newFragment.show(getSupportFragmentManager(),"datePicker");
236 }
237 public void showEndDatePicker(View v){
238 DialogFragment dialogFragment = new EndDateFragment();
239 dialogFragment.show(getSupportFragmentManager(),"End Date");
240 }
241 public void processDatePickerResult(int year,int month,int day){
242 String month_string = Integer.toString(month +1);
243 String day_string = Integer.toString(day);
244 String year_string = Integer.toString(year);
245 String dateMessage = (year_string + "-" + month_string +
246 "-" + day_string);
247 mStartDate.setText(dateMessage);
248 StartDate = getTimeInMillis(day,month,year);
249
250 }
251 public void processEndDatePickerResult(int year, int month, int day){
252 String month_string = Integer.toString(month + 1);
253 String day_string = Integer.toString(day);
254 String year_String = Integer.toString(year);
255 String dateMessage = (year_String + "-" + month_string +
256 "-" + day_string);
257 mEndDate.setText(dateMessage);
258 EndDate = getTimeInMillis(day,month,year);
259 }
260
261 private void hideKeyboard(View view){
262 InputMethodManager imm = (InputMethodManager) getSystemService(Activity.INPUT_METHOD_SERVICE);
263 imm.hideSoftInputFromWindow(view.getWindowToken(), 0);
264
265 }
266
267 public static long getTimeInMillis(int day, int month, int year) {
268 Calendar calendar = Calendar.getInstance();
269 calendar.set(year, month, day);
270 return calendar.getTimeInMillis();
271 }
272}
273
274public class FilterActivity extends AppCompatActivity implements EnquiryAdapter.OnItemClickListener {
275 private RecyclerView mRecyclerView;
276 private EnquiryAdapter mAdapter;
277 private DbHelper mDbHelper;
278 public static final String FORM_ID = "form_id";
279
280
281 @Override
282 protected void onCreate(Bundle savedInstanceState) {
283 super.onCreate(savedInstanceState);
284 setContentView(R.layout.activity_filter);
285
286 mRecyclerView = findViewById(R.id.filter_recyclerview);
287 mDbHelper = new DbHelper(this);
288
289
290 SQLiteDatabase db = mDbHelper.getReadableDatabase();
291
292 Intent intent = getIntent();
293 intent.getExtras();
294
295 String sql = intent.getStringExtra(MainActivity.SQL);
296 String dept = intent.getStringExtra(MainActivity.DEPT);
297 String source = intent.getStringExtra(MainActivity.SOURCE);
298 long startDate = intent.getIntExtra(MainActivity.STARTDATE,0);
299 long endDate = intent.getIntExtra(MainActivity.ENDDATE,0);
300
301
302 Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(startDate), String.valueOf(endDate), dept, source});
303
304 mAdapter = new EnquiryAdapter(FilterActivity.this, cursor);
305
306 //RecyclerView Setup
307 mRecyclerView.setHasFixedSize(true);
308 mRecyclerView.setLayoutManager(new LinearLayoutManager(this));
309 //mAdapter = new EnquiryAdapter(FilterActivity.this,mDbHelper.getFormData());
310 mRecyclerView.setAdapter(mAdapter);
311 mAdapter.setOnItemClickListener(FilterActivity.this);
312 }
313 @Override
314 public void onItemClick(int position) {
315 Intent intent = new Intent(this, DetailedActivity.class);
316 int id = EnquiryAdapter.id;
317 intent.putExtra(FORM_ID,id);
318 startActivity(intent);
319 }
320}
321
322SELECT * FROM your_table WHERE your_timestamp_column BETWEEN ? AND ? your_dept_column = ? OR your_source_column = ?
323
324SELECT * FROM your_table WHERE your_timestamp_column BETWEEN ? AND ?
325 AND ( -- <<<<<<<<<<<< MISSING CONJUNCTION
326 your_dept_column = ? OR your_source_column = ?
327 )
328
329String time_filter_end_char = ""; //<<<<<<<<<< NO ) at end if no time_filter
330 String sd = String.valueOf(StartDate); //<<<<<<<<<< ?????????
331 String ed = String.valueOf(EndDate); //<<<<<<<<<< ??????????
332 if(sd != null && ed != null && sd.length() > 0 && ed.length() > 0 ){
333 time_filter_end_char = ")";
334 sql = sql + FormEntry.TIMESTAMP+ " BETWEEN" + " ?" + " AND" + " ? AND("; //<<<<<<<<<< CONJUNCTION ADDED
335 }
336 //<<<<<<<<<< else construct/clause REMOVED as it does nothing
337 department = mDeptSpinner.getSelectedItem().toString();
338 source = mSourceSpinner.getSelectedItem().toString();
339
340 if(mDeptSpinner.getSelectedItem() != null){
341 sql = sql + FormEntry.COLUMN_NAME_DEPT + " = ?";
342 }
343
344 if(mSourceSpinner.getSelectedItem() != null){
345 sql = sql + " OR "+ FormEntry.COLUMN_NAME_SOURCE + " = ?";
346 }
347 sql = sql + time_filter_end_char;
348
349String sd = String.valueOf(StartDate);
350 String ed = String.valueOf(EndDate);
351 String department = mDeptSpinner.getSelectedItem().toString();
352 String source = mSourceSpinner.getSelectedItem().toString();
353 String sql = "SELECT * FROM " + FormEntry.COLUMN_TABLE_NAME; //<<<<<<< no filtering
354 String time_filter = "";
355 String department_filter = "";
356 String source_filter = "";
357 if (sd != null && ed != null && sd.length() > 0 && ed.length() > 0) {
358 time_filter = FormEntry.TIMESTAMP + " BETWEEN" + " ?" + " AND" + " ? ";
359 }
360 if (department != null && department.length() > 0) {
361 department_filter = FormEntry.COLUMN_NAME_DEPT + " = ?";
362 }
363 if (source != null && source.length() > 0) {
364 source_filter = FormEntry.COLUMN_NAME_SOURCE + " = ?";
365 }
366 String dept_source_conjunction = "";
367 if (department_filter.length() > 0 && source_filter.length() > 0) {
368 dept_source_conjunction = " OR ";
369 }
370 if (time_filter.length() > 0 || department_filter.length() > 0 || source_filter.length() > 0) {
371 sql = sql + " WHERE ";
372 }
373 String end_dept_source_char = "";
374 if (time_filter.length() > 0) {
375 sql = sql + time_filter;
376 if (department_filter.length() > 0 || source_filter.length() > 0) {
377 sql = sql + " AND ";
378 if (dept_source_conjunction.length() > 0) {
379 sql = sql + "(";
380 end_dept_source_char = ")";
381 }
382 }
383 }
384 sql = sql + department_filter + dept_source_conjunction + source_filter + end_dept_source_char;
385
386private String testit(String StartDate, String EndDate, String mDS, String mSS) {
387 //String sd = String.valueOf(StartDate);
388 //String ed = String.valueOf(EndDate);
389 String sd = StartDate; //<<<<<<<<<< FOR TESTING
390 String ed = EndDate; //<<<<<<<<<< FOR TESTING
391 //String department = mDeptSpinner.getSelectedItem().toString();
392 //String source = mSourceSpinner.getSelectedItem().toString();
393 String department = mDS; //<<<<<<<<<< FOR TESTING
394 String source = mSS; //<<<<<<<<<< FOR TESTING
395 String sql = "SELECT * FROM " + FormEntry.COLUMN_TABLE_NAME; //<<<<<<< no filtering
396 String time_filter = "";
397 String department_filter = "";
398 String source_filter = "";
399 if (sd != null && ed != null && sd.length() > 0 && ed.length() > 0) {
400 time_filter = FormEntry.TIMESTAMP + " BETWEEN" + " ?" + " AND" + " ? ";
401 }
402 if (department != null && department.length() > 0) {
403 department_filter = FormEntry.COLUMN_NAME_DEPT + " = ?";
404 }
405 if (source != null && source.length() > 0) {
406 source_filter = FormEntry.COLUMN_NAME_SOURCE + " = ?";
407 }
408 String dept_source_conjunction = "";
409 if (department_filter.length() > 0 && source_filter.length() > 0) {
410 dept_source_conjunction = " OR ";
411 }
412 if (time_filter.length() > 0 || department_filter.length() > 0 || source_filter.length() > 0) {
413 sql = sql + " WHERE ";
414 }
415 String end_dept_source_char = "";
416 if (time_filter.length() > 0) {
417 sql = sql + time_filter;
418 if (department_filter.length() > 0 || source_filter.length() > 0) {
419 sql = sql + " AND ";
420 if (dept_source_conjunction.length() > 0) {
421 sql = sql + "(";
422 end_dept_source_char = ")";
423 }
424 }
425 }
426 sql = sql + department_filter + dept_source_conjunction + source_filter + end_dept_source_char;
427 return sql;
428 }
429
430Log.d("TEST2", testit(null,null,null,null)); //<<<<<<<<<< no filter
431 Log.d("TEST1",testit("1111111111","2222222222",null,null)); // time filter only
432 Log.d("TEST3", testit("1111111111","2222222222","d1","s1")); // full filter
433 Log.d("TEST4",testit("1111111111","2222222222",null,"s1")); // time and source
434 Log.d("TEST5",testit("1111111111","2222222222","d1","")); // time and dept
435
436D/TEST2: SELECT * FROM your_table_name
437D/TEST1: SELECT * FROM your_table_name WHERE your_timestamp_column BETWEEN ? AND ?
438D/TEST3: SELECT * FROM your_table_name WHERE your_timestamp_column BETWEEN ? AND ? AND (your_department_column = ? OR your_source_column = ?)
439D/TEST4: SELECT * FROM your_table_name WHERE your_timestamp_column BETWEEN ? AND ? AND your_source_column = ?
440D/TEST5: SELECT * FROM your_table_name WHERE your_timestamp_column BETWEEN ? AND ? AND your_department_column = ?