Clover Coverage Report - RaspberryBusMalaysiaActivity Coverage Report
Coverage timestamp: mar dic 23 2014 15:39:35 EST
../../../img/srcFileCovDistChart9.png 36% of files have more coverage
255   1.473   101   3,31
46   1.192   0,4   38,5
77     1,31  
2    
This report was generated with an evaluation server license. Purchase Clover or configure your license.
 
  DbAdapter       Line # 35 226 93 85,6% 0.856305
  DbAdapter.DatabaseHelper       Line # 181 29 8 81,1% 0.8108108
 
No Tests
 
1    /*
2    Copyright (C) 2012,2013 Sweetie Piggy Apps <sweetiepiggyapps@gmail.com>
3   
4    This file is part of Raspberry Bus Malaysia.
5   
6    Raspberry Bus Malaysia is free software; you can redistribute it and/or modify
7    it under the terms of the GNU General Public License as published by
8    the Free Software Foundation; either version 3 of the License, or
9    (at your option) any later version.
10   
11    Raspberry Bus Malaysia is distributed in the hope that it will be useful,
12    but WITHOUT ANY WARRANTY; without even the implied warranty of
13    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14    GNU General Public License for more details.
15   
16    You should have received a copy of the GNU General Public License
17    along with Raspberry Bus Malaysia; if not, see <http://www.gnu.org/licenses/>.
18    */
19   
20    package com.sweetiepiggy.raspberrybusmalaysia;
21   
22    import java.util.ArrayList;
23    import java.util.Arrays;
24    import java.util.HashMap;
25    import java.util.Iterator;
26    import java.util.Map.Entry;
27   
28    import android.content.ContentValues;
29    import android.content.Context;
30    import android.database.Cursor;
31    import android.database.SQLException;
32    import android.database.sqlite.SQLiteDatabase;
33    import android.database.sqlite.SQLiteOpenHelper;
34   
 
35    public class DbAdapter
36    {
37    public static final String KEY_ROWID = "_id";
38    public static final String KEY_AGENT = "agent";
39    public static final String KEY_AGENT_ID = "agent_id";
40    public static final String KEY_OPERATOR = "operator";
41    public static final String KEY_OPERATOR_ID = "operator_id";
42    public static final String KEY_FROM_CITY = "from_city";
43    public static final String KEY_FROM_STN = "from_station";
44    public static final String KEY_TO_CITY = "to_city";
45    public static final String KEY_TO_STN = "to_station";
46    public static final String KEY_FROM_STN_ID = "from_station_id";
47    public static final String KEY_TO_STN_ID = "to_station_id";
48    public static final String KEY_SCHED_DEP = "scheduled_departure";
49    public static final String KEY_ACTUAL_DEP = "actual_departure";
50    public static final String KEY_ARRIVAL = "arrival_time";
51    public static final String KEY_CTR = "counter";
52    public static final String KEY_SAFETY = "safety";
53    public static final String KEY_COMFORT = "comfort";
54    public static final String KEY_OVERALL = "overall";
55    public static final String KEY_COMMENT = "comment";
56    /* TODO: correct KEY_LAST_UPDATE, should not be "comment" */
57    public static final String KEY_LAST_UPDATE = "comment";
58    public static final String KEY_UPDATE_DATE = "last_updated";
59    public static final String KEY_CITY = "city";
60    public static final String KEY_CITY_ID = "city_id";
61    public static final String KEY_CITY_EN = "city_en";
62    public static final String KEY_CITY_MS = "city_ms";
63    public static final String KEY_CITY_ZH = "city_zh";
64    public static final String KEY_LATITUDE = "latitude";
65    public static final String KEY_LONGITUDE = "longitude";
66    public static final String KEY_STN = "station";
67    public static final String KEY_STN_EN = "station_en";
68    public static final String KEY_STN_MS = "station_ms";
69    public static final String KEY_STN_ZH = "station_zh";
70    public static final String KEY_REG = "reg";
71   
72    public static final String TRIP_TIME = "(strftime('%s', " + KEY_ARRIVAL + ") - strftime('%s', " + KEY_SCHED_DEP + "))";
73    public static final String TRIP_DELAY = "(strftime('%s', " + KEY_ACTUAL_DEP + ") - strftime('%s', " + KEY_SCHED_DEP + "))";
74    public static final String AVG_TIME = "avg_time";
75    public static final String AVG_DELAY = "avg(" + TRIP_DELAY + ")";
76    public static final String NUM_TRIPS = "num_trips";
77    public static final String AVG_OVERALL = "avg_overall";
78   
79    private static final String CALC_NUM_TRIPS = "count(" + KEY_AGENT_ID + ")";
80   
81    public static final String TABLE_TRIPS = "trips";
82    public static final String TABLE_CITIES = "cities";
83    public static final String TABLE_STATIONS = "stations";
84    public static final String TABLE_AGENTS = "agents";
85    public static final String TABLE_OPERATORS = "operators";
86   
87    private static HashMap<String, ArrayList<String>> TABLE_MAPS = new HashMap<String, ArrayList<String>>();
88   
89    private static final int SEC_BETWEEN_UPDATES = 60 * 60 * 24 * 1;
90   
91    private DatabaseHelper mDbHelper;
92   
93    private static final String DATABASE_NAME = "rbm.db";
94    /** "tmp" table was originally named "submit" in db-v7 / rbm-v0.1.1 */
95    private static final String TABLE_TMP = "tmp";
96    private static final String TABLE_TMP_COMPLAINT = "tmp_complaint";
97    private static final String TABLE_LAST_UPDATE = "last_update";
98    private static final int DATABASE_VERSION = 15;
99   
100    private static final String DATABASE_CREATE_TRIPS =
101    "CREATE TABLE " + TABLE_TRIPS + " (" +
102    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
103    KEY_AGENT_ID + " INTEGER, " +
104    KEY_OPERATOR_ID + " INTEGER, " +
105    KEY_FROM_STN_ID + " INTEGER, " +
106    KEY_TO_STN_ID + " INTEGER, " +
107    KEY_SCHED_DEP + " TEXT NOT NULL, " +
108    KEY_ACTUAL_DEP + " TEXT NOT NULL, " +
109    KEY_ARRIVAL + " TEXT NOT NULL, " +
110    KEY_CTR + " TEXT, " +
111    KEY_SAFETY + " INTEGER, " +
112    KEY_COMFORT + " INTEGER, " +
113    KEY_OVERALL + " INTEGER, " +
114    KEY_COMMENT + " TEXT, " +
115    "FOREIGN KEY(" + KEY_FROM_STN_ID + ") REFERENCES " + TABLE_STATIONS + "(" + KEY_ROWID + "), " +
116    "FOREIGN KEY(" + KEY_TO_STN_ID + ") REFERENCES " + TABLE_STATIONS + "(" + KEY_ROWID + "));";
117   
118    private static final String DATABASE_CREATE_TMP =
119    "CREATE TABLE " + TABLE_TMP + " (" +
120    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
121    KEY_AGENT + " TEXT, " +
122    KEY_OPERATOR + " TEXT, " +
123    KEY_FROM_STN + " TEXT, " +
124    KEY_TO_STN + " TEXT, " +
125    KEY_SCHED_DEP + " TEXT, " +
126    KEY_ACTUAL_DEP + " TEXT, " +
127    KEY_ARRIVAL + " TEXT, " +
128    KEY_CTR + " TEXT, " +
129    KEY_SAFETY + " INTEGER, " +
130    KEY_COMFORT + " INTEGER, " +
131    KEY_OVERALL + " INTEGER, " +
132    KEY_COMMENT + " TEXT);";
133   
134    private static final String DATABASE_CREATE_TMP_COMPLAINT =
135    "CREATE TABLE " + TABLE_TMP_COMPLAINT + " (" +
136    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
137    KEY_AGENT + " TEXT, " +
138    KEY_OPERATOR + " TEXT, " +
139    KEY_FROM_CITY + " TEXT, " +
140    KEY_FROM_STN + " TEXT, " +
141    KEY_TO_CITY + " TEXT, " +
142    KEY_TO_STN + " TEXT, " +
143    KEY_SCHED_DEP + " TEXT, " +
144    KEY_CTR + " TEXT, " +
145    KEY_REG + " TEXT, " +
146    KEY_COMMENT + " TEXT);";
147   
148    private static final String DATABASE_CREATE_LAST_UPDATE =
149    "CREATE TABLE " + TABLE_LAST_UPDATE + " (" +
150    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
151    KEY_LAST_UPDATE + " TEXT);";
152   
153    private static final String DATABASE_CREATE_CITIES =
154    "CREATE TABLE " + TABLE_CITIES + " (" +
155    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
156    KEY_CITY_EN + " TEXT UNIQUE, " +
157    KEY_CITY_MS + " TEXT UNIQUE, " +
158    KEY_CITY_ZH + " TEXT UNIQUE);";
159   
160    private static final String DATABASE_CREATE_STATIONS =
161    "CREATE TABLE " + TABLE_STATIONS + " (" +
162    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
163    KEY_CITY_ID + " INTEGER, " +
164    KEY_STN_EN + " TEXT, " +
165    KEY_STN_MS + " TEXT, " +
166    KEY_STN_ZH + " TEXT, " +
167    KEY_LATITUDE + " INTEGER, " +
168    KEY_LONGITUDE + " INTEGER, " +
169    "FOREIGN KEY(" + KEY_CITY_ID + ") REFERENCES " + TABLE_CITIES + "(" + KEY_ROWID + "));";
170   
171    private static final String DATABASE_CREATE_AGENTS =
172    "CREATE TABLE " + TABLE_AGENTS + " (" +
173    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
174    KEY_AGENT + " TEXT UNIQUE);";
175   
176    private static final String DATABASE_CREATE_OPERATORS =
177    "CREATE TABLE " + TABLE_OPERATORS + " (" +
178    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
179    KEY_OPERATOR + " TEXT UNIQUE);";
180   
 
181    private static class DatabaseHelper extends SQLiteOpenHelper
182    {
183    private final Context mCtx;
184    private boolean mAllowSync;
185    public SQLiteDatabase mDb;
186   
 
187  31 toggle DatabaseHelper(Context context, boolean allow_sync)
188    {
189  31 super(context, DATABASE_NAME, null, DATABASE_VERSION);
190  31 mCtx = context;
191  31 mAllowSync = allow_sync;
192    }
193   
 
194  1 toggle @Override
195    public void onCreate(SQLiteDatabase db)
196    {
197  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRIPS);
198  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TMP);
199  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TMP_COMPLAINT);
200  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_LAST_UPDATE);
201  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_STATIONS);
202  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITIES);
203  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_AGENTS);
204  1 db.execSQL("DROP TABLE IF EXISTS " + TABLE_OPERATORS);
205  1 db.execSQL(DATABASE_CREATE_OPERATORS);
206  1 db.execSQL(DATABASE_CREATE_AGENTS);
207  1 db.execSQL(DATABASE_CREATE_CITIES);
208  1 db.execSQL(DATABASE_CREATE_STATIONS);
209  1 db.execSQL(DATABASE_CREATE_TRIPS);
210  1 db.execSQL(DATABASE_CREATE_TMP_COMPLAINT);
211  1 db.execSQL(DATABASE_CREATE_TMP);
212  1 db.execSQL(DATABASE_CREATE_LAST_UPDATE);
213    }
214   
 
215  0 toggle @Override
216    public void onUpgrade(SQLiteDatabase db, int old_ver, int new_ver)
217    {
218  0 switch (old_ver) {
219    /* replaced operator and agent columns with operator_id and agent_id,
220    created operators and agents tables */
221    /* just start from scratch */
222  0 case 14:
223  0 default:
224  0 onCreate(db);
225  0 break;
226    }
227    }
228   
 
229  20 toggle public void open()
230    {
231  20 mDb = getReadableDatabase();
232    }
233   
 
234  11 toggle public void open_readwrite() throws SQLException
235    {
236  11 mDb = getWritableDatabase();
237    }
238   
 
239  31 toggle @Override
240    public synchronized void close()
241    {
242  31 if (mDb != null) {
243  31 mDb.close();
244    }
245  31 super.close();
246    }
247    }
248   
 
249  29 toggle public DbAdapter()
250    {
251    }
252   
 
253  20 toggle public DbAdapter open(Context ctx)
254    {
255  20 mDbHelper = new DatabaseHelper(ctx, false);
256  20 mDbHelper.open();
257  20 return this;
258    }
259   
 
260  1 toggle public DbAdapter open_readwrite(Context ctx) throws SQLException
261    {
262  1 return open_readwrite(ctx, true);
263    }
264   
 
265  11 toggle public DbAdapter open_readwrite(Context ctx, boolean allow_sync) throws SQLException
266    {
267  11 mDbHelper = new DatabaseHelper(ctx, allow_sync);
268  11 mDbHelper.open_readwrite();
269  11 return this;
270    }
271   
 
272  1 toggle public void check_last_update_and_sync()
273    {
274  1 if (sec_since_last_update() > SEC_BETWEEN_UPDATES) {
275  1 boolean showProgress = never_updated();
276  1 SyncTask sync = new SyncTask(mDbHelper.mCtx, showProgress);
277  1 sync.execute();
278    }
279    }
280   
 
281  31 toggle public void close()
282    {
283  31 mDbHelper.close();
284    }
285   
286    /** @return row_id or -1 if failed */
 
287  0 toggle public long create_city(ContentValues city)
288    {
289  0 return replace(city, TABLE_CITIES);
290    }
291   
292    /** @return row_id or -1 if failed */
 
293  0 toggle public long create_station(ContentValues station)
294    {
295  0 return replace(station, TABLE_STATIONS);
296    }
297   
298    /** @return row_id or -1 if failed */
 
299  0 toggle public long create_trip(ContentValues trip)
300    {
301  0 return replace(trip, TABLE_TRIPS);
302    }
303   
304    /** @return row_id or -1 if failed */
 
305  368 toggle public long replace(ContentValues cv, String table)
306    {
307  368 rm_unknown_cols(cv, table);
308  368 return mDbHelper.mDb.replace(table, null, cv);
309    }
310   
 
311  368 toggle private void rm_unknown_cols(ContentValues cv, String table)
312    {
313  368 ArrayList<String> col_names;
314   
315  368 if (TABLE_MAPS.containsKey(table)) {
316  363 col_names = TABLE_MAPS.get(table);
317    } else {
318  5 Cursor c = mDbHelper.mDb.query(true, table, null, null, null,
319    null, null, null, "1");
320  5 col_names = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
321  5 TABLE_MAPS.put(table, col_names);
322    }
323   
324  368 ArrayList<String> to_remove = new ArrayList<String>();
325   
326  368 Iterator<Entry<String, Object>> itr = cv.valueSet().iterator();
327  4067 while (itr.hasNext()) {
328  3699 String key = itr.next().getKey().toString();
329  3699 if (!col_names.contains(key)) {
330  672 to_remove.add(key);
331    }
332    }
333  368 Iterator<String> to_rm_itr = to_remove.iterator();
334  1040 while (to_rm_itr.hasNext()) {
335  672 cv.remove(to_rm_itr.next());
336    }
337    }
338   
339    /** @return cities that exist as departure point in submitted trips */
 
340  3 toggle public Cursor fetch_from_cities()
341    {
342  3 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
343  3 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
344    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " + key_city + " AS " + KEY_FROM_CITY +
345    " FROM " + TABLE_TRIPS + " JOIN " + TABLE_STATIONS +
346    " ON " + TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
347    TABLE_STATIONS + "." + KEY_ROWID +
348    " JOIN " + TABLE_CITIES +
349    " ON " + KEY_CITY_ID + " == " +
350    TABLE_CITIES + "." + KEY_ROWID +
351    " GROUP BY " + key_city +
352    " ORDER BY " + key_city + " ASC",
353    null);
354    }
355   
356    /** @return stations that exist as departure point in submitted trips */
 
357  3 toggle public Cursor fetch_from_stations()
358    {
359  3 String key_station = KEY_STN + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
360  3 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
361  3 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
362    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " +
363    key_station + " AS " + KEY_STN + ", " +
364    key_city + " AS " + KEY_CITY + ", " +
365    KEY_LATITUDE + ", " + KEY_LONGITUDE +
366    " FROM " + TABLE_TRIPS + " JOIN " + TABLE_STATIONS +
367    " on " + TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
368    TABLE_STATIONS + "." + KEY_ROWID +
369    " JOIN " + TABLE_CITIES + " on " +
370    KEY_CITY_ID + " == " + TABLE_CITIES + "." + KEY_ROWID +
371    " GROUP BY " + key_station +
372    " ORDER BY " + key_station + " ASC",
373    null);
374    }
375   
376    /** @return cities that exist as departure point in submitted trips
377    @param agent - filter by agent */
 
378  1 toggle public Cursor fetch_agent_from_cities(String agent)
379    {
380  1 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
381  1 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
382    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " + key_city + " AS " + KEY_FROM_CITY +
383    " FROM " + TABLE_TRIPS +
384   
385    " JOIN " + TABLE_STATIONS +
386    " ON " + TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
387    TABLE_STATIONS + "." + KEY_ROWID +
388   
389    " JOIN " + TABLE_CITIES +
390    " ON " + KEY_CITY_ID + " == " +
391    TABLE_CITIES + "." + KEY_ROWID +
392   
393    " JOIN " + TABLE_AGENTS +
394    " ON " + KEY_AGENT_ID + " == " +
395    TABLE_AGENTS + "." + KEY_ROWID +
396   
397    " WHERE " + KEY_AGENT + " == ? " +
398    " GROUP BY " + key_city +
399    " ORDER BY " + key_city + " ASC",
400    new String[] {agent});
401    }
402   
403    /** @return cities that exist as departure point in submitted trips
404    @param operator - filter by operator */
 
405  2 toggle public Cursor fetch_operator_from_cities(String operator)
406    {
407  2 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
408  2 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
409    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " + key_city + " AS " + KEY_FROM_CITY +
410    " FROM " + TABLE_TRIPS +
411   
412    " JOIN " + TABLE_STATIONS +
413    " ON " + TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
414    TABLE_STATIONS + "." + KEY_ROWID +
415   
416    " JOIN " + TABLE_CITIES +
417    " ON " + KEY_CITY_ID + " == " +
418    TABLE_CITIES + "." + KEY_ROWID +
419   
420    " JOIN " + TABLE_OPERATORS +
421    " ON " + KEY_OPERATOR_ID + " == " +
422    TABLE_OPERATORS + "." + KEY_ROWID +
423   
424    " WHERE " + KEY_OPERATOR + " == ? " +
425    " GROUP BY " + key_city +
426    " ORDER BY " + key_city + " ASC",
427    new String[] {operator});
428    }
429   
430    /** @return cities that exist as arrival point in submitted trips for
431    a given departure city
432    @param from_city - departure city */
 
433  6 toggle public Cursor fetch_to_cities(String from_city)
434    {
435  6 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
436   
437  6 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
438    "." + KEY_ROWID + " AS " + KEY_ROWID +
439    ", TO_CITIES." + key_city + " AS " + KEY_TO_CITY +
440    " FROM " + TABLE_TRIPS +
441   
442    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
443    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
444    "FROM_STATIONS." + KEY_ROWID +
445   
446    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
447    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
448    "TO_STATIONS." + KEY_ROWID +
449   
450    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
451    " FROM_STATIONS." + KEY_CITY_ID + " == " +
452    "FROM_CITIES." + KEY_ROWID +
453   
454    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
455    " TO_STATIONS." + KEY_CITY_ID + " == " +
456    "TO_CITIES." + KEY_ROWID +
457   
458    " WHERE FROM_CITIES." + key_city + " == ? " +
459    " GROUP BY TO_CITIES." + key_city +
460    " ORDER BY TO_CITIES." + key_city + " ASC",
461    new String[] {from_city});
462    }
463   
464    /** @return stations that exist as arrival point in submitted trips
465    for a given departure station
466    @param from_station - departure station */
 
467  13 toggle public Cursor fetch_to_stations(String from_station)
468    {
469  13 String key_station = KEY_STN + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
470   
471  13 return mDbHelper.mDb.rawQuery(
472    "SELECT " +
473    TABLE_TRIPS + "." + KEY_ROWID + " AS " + KEY_ROWID + ", " +
474    "TO_STATIONS." + key_station + " AS " + KEY_TO_STN + ", " +
475    "TO_STATIONS." + KEY_LATITUDE + ", " +
476    "TO_STATIONS." + KEY_LONGITUDE +
477    " FROM " + TABLE_TRIPS +
478   
479    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
480    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
481    "FROM_STATIONS." + KEY_ROWID +
482   
483    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
484    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
485    "TO_STATIONS." + KEY_ROWID +
486   
487    " WHERE FROM_STATIONS." + key_station + " == ? " +
488    " GROUP BY TO_STATIONS." + key_station +
489    " ORDER BY TO_STATIONS." + key_station + " ASC",
490    new String[] {from_station});
491    }
492   
493    /** @return stations that exist as arrival point in submitted trips
494    for a given departure city
495    @param from_city - departure city */
 
496  2 toggle public Cursor fetch_to_stations_from_city(String from_city)
497    {
498  2 String key_station = KEY_STN + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
499  2 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
500   
501  2 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
502    "." + KEY_ROWID + " AS " + KEY_ROWID +
503    ", TO_STATIONS." + key_station + " AS " + KEY_STN +
504    ", TO_CITIES." + key_city + " AS " + KEY_CITY +
505    ", TO_STATIONS." + KEY_LATITUDE +
506    ", TO_STATIONS." + KEY_LONGITUDE +
507    " FROM " + TABLE_TRIPS +
508   
509    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
510    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
511    "FROM_STATIONS." + KEY_ROWID +
512   
513    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
514    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
515    "TO_STATIONS." + KEY_ROWID +
516   
517    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
518    " FROM_STATIONS." + KEY_CITY_ID + " == " +
519    "FROM_CITIES." + KEY_ROWID +
520   
521    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
522    " TO_STATIONS." + KEY_CITY_ID + " == " +
523    "TO_CITIES." + KEY_ROWID +
524   
525    " WHERE FROM_CITIES." + key_city + " == ? " +
526    " GROUP BY TO_STATIONS." + key_station +
527    " ORDER BY TO_STATIONS." + key_station + " ASC",
528    new String[] {from_city});
529    }
530   
531    /** @return cities that exist as arrival point in submitted trips
532    for a given departure city and agent
533    @param from_city - departure city
534    @param agent - filter by agent */
 
535  3 toggle public Cursor fetch_agent_to_cities(String from_city, String agent)
536    {
537  3 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
538   
539  3 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
540    "." + KEY_ROWID + " AS " + KEY_ROWID +
541    ", TO_CITIES." + key_city + " AS " + KEY_TO_CITY +
542    " FROM " + TABLE_TRIPS +
543   
544    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
545    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
546    "FROM_STATIONS." + KEY_ROWID +
547   
548    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
549    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
550    "TO_STATIONS." + KEY_ROWID +
551   
552    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
553    " FROM_STATIONS." + KEY_CITY_ID + " == " +
554    "FROM_CITIES." + KEY_ROWID +
555   
556    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
557    " TO_STATIONS." + KEY_CITY_ID + " == " +
558    "TO_CITIES." + KEY_ROWID +
559   
560    " JOIN " + TABLE_AGENTS +
561    " ON " + KEY_AGENT_ID + " == " +
562    TABLE_AGENTS + "." + KEY_ROWID +
563   
564    " WHERE FROM_CITIES." + key_city + " == ? " + " AND " +
565    KEY_AGENT + " == ? " +
566    " GROUP BY TO_CITIES." + key_city +
567    " ORDER BY TO_CITIES." + key_city + " ASC",
568    new String[] {from_city, agent});
569    }
570   
571    /** @return cities that exist as arrival point in submitted trips
572    for a given departure city and operator
573    @param from_city - departure city
574    @param operator - filter by operator */
 
575  5 toggle public Cursor fetch_operator_to_cities(String from_city, String operator)
576    {
577  5 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
578   
579  5 return mDbHelper.mDb.rawQuery("SELECT DISTINCT " + TABLE_TRIPS +
580    "." + KEY_ROWID + " AS " + KEY_ROWID +
581    ", TO_CITIES." + key_city + " AS " + KEY_TO_CITY +
582    " FROM " + TABLE_TRIPS +
583   
584    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
585    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
586    "FROM_STATIONS." + KEY_ROWID +
587   
588    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
589    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
590    "TO_STATIONS." + KEY_ROWID +
591   
592    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
593    " FROM_STATIONS." + KEY_CITY_ID + " == " +
594    "FROM_CITIES." + KEY_ROWID +
595   
596    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
597    " TO_STATIONS." + KEY_CITY_ID + " == " +
598    "TO_CITIES." + KEY_ROWID +
599   
600    " JOIN " + TABLE_OPERATORS +
601    " ON " + KEY_OPERATOR_ID + " == " +
602    TABLE_OPERATORS + "." + KEY_ROWID +
603   
604    " WHERE FROM_CITIES." + key_city + " == ? " + " AND " +
605    KEY_OPERATOR + " == ? " +
606    " GROUP BY TO_CITIES." + key_city +
607    " ORDER BY TO_CITIES." + key_city + " ASC",
608    new String[] {from_city, operator});
609    }
610   
 
611  15 toggle public Cursor fetch_stations()
612    {
613  15 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
614  15 String key_station = KEY_STN + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
615  15 return mDbHelper.mDb.rawQuery("SELECT " + TABLE_STATIONS +
616    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " +
617    key_station + " AS " + KEY_STN + ", " +
618    key_city + " AS " + KEY_CITY + ", " +
619    KEY_LATITUDE + ", " + KEY_LONGITUDE +
620    " FROM " + TABLE_STATIONS + " JOIN " + TABLE_CITIES +
621    " ON " + TABLE_STATIONS + "." + KEY_CITY_ID + " == " +
622    TABLE_CITIES + "." + KEY_ROWID,
623    null);
624    }
625   
 
626  4 toggle public Cursor fetch_cities()
627    {
628  4 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
629  4 return mDbHelper.mDb.query(true, TABLE_CITIES, new String[] {KEY_ROWID, key_city + " AS " + KEY_CITY},
630    null, null, null, null, key_city + " ASC", null);
631    }
632   
 
633  5 toggle public Cursor fetch_agents()
634    {
635  5 return mDbHelper.mDb.query(true, TABLE_AGENTS, new String[] {KEY_ROWID, KEY_AGENT},
636    "length(" + KEY_AGENT + ") != 0", null,
637    KEY_AGENT, null, KEY_AGENT + " ASC", null);
638    }
639   
 
640  5 toggle public Cursor fetch_operators()
641    {
642  5 return mDbHelper.mDb.query(true, TABLE_OPERATORS, new String[] {KEY_ROWID, KEY_OPERATOR},
643    "length(" + KEY_OPERATOR + ") != 0", null,
644    KEY_OPERATOR, null, KEY_OPERATOR + " ASC", null);
645    }
646   
 
647  2 toggle public Cursor fetch_counter_nums()
648    {
649  2 return mDbHelper.mDb.query(true, TABLE_TRIPS, new String[] {KEY_ROWID, KEY_CTR},
650    "length(" + KEY_CTR + ") != 0", null,
651    KEY_CTR, null, KEY_CTR + " ASC", null);
652    }
653   
 
654  0 toggle public Cursor fetch_agents(String agent_query)
655    {
656  0 return mDbHelper.mDb.query(true, TABLE_AGENTS, new String[] {KEY_ROWID, KEY_AGENT},
657    "length(" + KEY_AGENT + ") != 0 AND " + KEY_AGENT + " LIKE ?", new String[] {agent_query},
658    KEY_AGENT, null, KEY_AGENT + " ASC", null);
659    }
660   
 
661  0 toggle public Cursor fetch_operators(String operator_query)
662    {
663  0 return mDbHelper.mDb.query(true, TABLE_OPERATORS, new String[] {KEY_ROWID, KEY_OPERATOR},
664    "length(" + KEY_OPERATOR + ") != 0 AND " + KEY_OPERATOR + " LIKE ?", new String[] {operator_query},
665    KEY_OPERATOR, null, KEY_OPERATOR + " ASC", null);
666    }
667   
 
668  11 toggle public Cursor fetch_avg_by_agent(String from_city, String to_city)
669    {
670  11 return fetch_avg(from_city, to_city, KEY_AGENT_ID);
671    }
672   
 
673  12 toggle public Cursor fetch_avg_by_operator(String from_city, String to_city)
674    {
675  12 return fetch_avg(from_city, to_city, KEY_OPERATOR_ID);
676    }
677   
 
678  23 toggle private Cursor fetch_avg(String from_city, String to_city, String group_by)
679    {
680  23 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
681  23 String tripAvg = getTripAvg(from_city, to_city);
682  23 return mDbHelper.mDb.rawQuery(
683    "SELECT " +
684    "TIMES." + KEY_ROWID + " AS " + KEY_ROWID + ", " +
685    "TIMES." + KEY_AGENT + " AS " + KEY_AGENT + ", " +
686    "TIMES." + KEY_OPERATOR + " AS " + KEY_OPERATOR + ", " +
687    AVG_TIME + ", " + NUM_TRIPS + ", " +
688    "0.50 * RATINGS." + AVG_OVERALL + " + " +
689    "0.25 * min(5, max(1, 5 - ((" + AVG_TIME + " - " + tripAvg + ") / " + tripAvg + " + 0.1) / 0.1)) + " +
690    "0.25 * min(5, max(1, (25 - avg_delay / 60.) / 5.)) AS " + AVG_OVERALL +
691    " FROM " +
692   
693    " (SELECT " +
694    TABLE_TRIPS + "." + KEY_ROWID + ", " + group_by + ", " +
695    TABLE_AGENTS + "." + KEY_AGENT + " AS " + KEY_AGENT + ", " +
696    TABLE_OPERATORS + "." + KEY_OPERATOR + " AS " + KEY_OPERATOR + ", " +
697    " AVG(" + TRIP_TIME + ") AS " + AVG_TIME + ", " +
698    " AVG(" + TRIP_DELAY + ") AS avg_delay" +
699   
700    " FROM " + TABLE_TRIPS +
701   
702    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
703    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
704    "FROM_STATIONS." + KEY_ROWID +
705   
706    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
707    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
708    "TO_STATIONS." + KEY_ROWID +
709   
710    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
711    " FROM_STATIONS." + KEY_CITY_ID + " == " +
712    "FROM_CITIES." + KEY_ROWID +
713   
714    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
715    " TO_STATIONS." + KEY_CITY_ID + " == " +
716    "TO_CITIES." + KEY_ROWID +
717   
718    " JOIN " + TABLE_AGENTS + " ON " +
719    TABLE_TRIPS + "." + KEY_AGENT_ID + " == " +
720    TABLE_AGENTS + "." + KEY_ROWID +
721   
722    " JOIN " + TABLE_OPERATORS + " ON " +
723    KEY_OPERATOR_ID + " == " +
724    TABLE_OPERATORS + "." + KEY_ROWID +
725   
726    " WHERE FROM_CITIES." + key_city + " == ? AND " +
727    " TO_CITIES." + key_city + " == ? AND " +
728    KEY_ARRIVAL + " != 'Cancelled' " +
729   
730    " GROUP BY " + group_by + ") " +
731    " AS TIMES " +
732   
733    " LEFT JOIN " +
734   
735    "(SELECT " +
736    group_by +
737    ", " +
738    " 0.5 * AVG(" + KEY_OVERALL + ") + " +
739    " 0.25 * AVG(" + KEY_SAFETY + ") + " +
740    " 0.25 * AVG(" + KEY_COMFORT + ") " +
741    " AS " + AVG_OVERALL +
742   
743    ", COUNT(" + group_by + ") AS " + NUM_TRIPS +
744   
745    " FROM " + TABLE_TRIPS +
746   
747    " WHERE length(" + KEY_OVERALL + ") != 0 AND " +
748    " length(" + KEY_SAFETY + ") != 0 AND " +
749    " length(" + KEY_COMFORT + ") != 0 " +
750   
751    " GROUP BY " + group_by + ") " +
752   
753    " AS RATINGS ON " +
754    "TIMES." + group_by + " == " +
755    " RATINGS." + group_by +
756   
757    " ORDER BY " +
758    " LENGTH(TIMES." + KEY_OPERATOR + ") != 0 AND " +
759    " LENGTH(TIMES." + KEY_AGENT + ") != 0 DESC, " +
760    AVG_OVERALL + " DESC, " +
761    AVG_TIME + " ASC",
762   
763    new String[] {from_city, to_city});
764    }
765   
 
766  23 toggle public String getTripAvg(String from_city, String to_city)
767    {
768  23 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
769  23 String ret = "";
770  23 Cursor c = mDbHelper.mDb.query(true,
771    TABLE_TRIPS +
772    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
773    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
774    "FROM_STATIONS." + KEY_ROWID +
775   
776    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
777    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
778    "TO_STATIONS." + KEY_ROWID +
779   
780    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
781    " FROM_STATIONS." + KEY_CITY_ID + " == " +
782    "FROM_CITIES." + KEY_ROWID +
783   
784    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
785    " TO_STATIONS." + KEY_CITY_ID + " == " +
786    "TO_CITIES." + KEY_ROWID,
787   
788    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
789    "avg(" + TRIP_TIME + ") AS " + AVG_TIME},
790    "FROM_CITIES." + key_city + " = ? AND " +
791    "TO_CITIES." + key_city + " = ?",
792    new String[] {from_city, to_city},
793    null, null, null, null);
794  23 if (c.moveToFirst()) {
795  23 ret = c.getString(c.getColumnIndex(AVG_TIME));
796    }
797  23 c.close();
798  23 return ret;
799    }
800   
801   
802    /* TODO: combine fetch_agent_avg and fetch_operator_avg into one function? */
 
803  0 toggle public Cursor fetch_agent_avg(String from_city, String to_city, String agent)
804    {
805  0 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
806  0 return mDbHelper.mDb.query(true,
807    TABLE_TRIPS +
808    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
809    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
810    "FROM_STATIONS." + KEY_ROWID +
811   
812    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
813    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
814    "TO_STATIONS." + KEY_ROWID +
815   
816    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
817    " FROM_STATIONS." + KEY_CITY_ID + " == " +
818    "FROM_CITIES." + KEY_ROWID +
819   
820    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
821    " TO_STATIONS." + KEY_CITY_ID + " == " +
822    "TO_CITIES." + KEY_ROWID +
823   
824    " JOIN " + TABLE_AGENTS + " ON " +
825    KEY_AGENT_ID + " == " +
826    TABLE_AGENTS + "." + KEY_ROWID,
827   
828    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
829    "avg(" + TRIP_TIME + ") AS " + AVG_TIME,
830    AVG_DELAY, CALC_NUM_TRIPS + " AS " + NUM_TRIPS},
831    KEY_AGENT + " = ? AND " +
832    "FROM_CITIES." + key_city + " = ? AND " +
833    "TO_CITIES." + key_city + " = ?",
834    new String[] {agent, from_city, to_city},
835    null, null, null, null);
836    }
837   
 
838  0 toggle public Cursor fetch_operator_avg(String from_city, String to_city, String operator)
839    {
840  0 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
841  0 return mDbHelper.mDb.query(true,
842    TABLE_TRIPS +
843   
844    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
845    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
846    "FROM_STATIONS." + KEY_ROWID +
847   
848    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
849    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
850    "TO_STATIONS." + KEY_ROWID +
851   
852    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
853    " FROM_STATIONS." + KEY_CITY_ID + " == " +
854    "FROM_CITIES." + KEY_ROWID +
855   
856    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
857    " TO_STATIONS." + KEY_CITY_ID + " == " +
858    "TO_CITIES." + KEY_ROWID +
859   
860    " JOIN " + TABLE_OPERATORS + " ON " +
861    KEY_OPERATOR_ID + " == " +
862    TABLE_OPERATORS + "." + KEY_ROWID,
863   
864    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
865    "avg(" + TRIP_TIME + ") AS " + AVG_TIME, AVG_DELAY,
866    CALC_NUM_TRIPS + " AS " + NUM_TRIPS},
867    KEY_OPERATOR + " = ? AND " +
868    "FROM_CITIES." + key_city + " = ? AND " +
869    "TO_CITIES." + key_city + " = ?",
870    new String[] {operator, from_city, to_city},
871    null, null, null, null);
872    }
873   
 
874  1 toggle public Cursor fetch_avg_agent_delay(String agent)
875    {
876  1 return mDbHelper.mDb.query(true,
877    TABLE_TRIPS +
878   
879    " JOIN " + TABLE_AGENTS + " ON " +
880    KEY_AGENT_ID + " == " +
881    TABLE_AGENTS + "." + KEY_ROWID,
882   
883    new String[] {AVG_DELAY},
884    KEY_AGENT + " = ?", new String[] {agent}, null, null, null, null);
885    }
886   
 
887  2 toggle public Cursor fetch_avg_operator_delay(String operator)
888    {
889  2 return mDbHelper.mDb.query(true,
890    TABLE_TRIPS +
891   
892    " JOIN " + TABLE_OPERATORS + " ON " +
893    KEY_OPERATOR_ID + " == " +
894    TABLE_OPERATORS + "." + KEY_ROWID,
895   
896    new String[] {AVG_DELAY},
897    KEY_OPERATOR + " = ?", new String[] {operator}, null, null, null, null);
898    }
899   
 
900  0 toggle public Cursor fetch_agent_trips(String from_city, String to_city, String agent)
901    {
902  0 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
903  0 return mDbHelper.mDb.query(TABLE_TRIPS +
904    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
905    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
906    "FROM_STATIONS." + KEY_ROWID +
907   
908    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
909    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
910    "TO_STATIONS." + KEY_ROWID +
911   
912    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
913    " FROM_STATIONS." + KEY_CITY_ID + " == " +
914    "FROM_CITIES." + KEY_ROWID +
915   
916    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
917    " TO_STATIONS." + KEY_CITY_ID + " == " +
918    "TO_CITIES." + KEY_ROWID +
919   
920    " JOIN " + TABLE_AGENTS + " ON " +
921    KEY_AGENT_ID + " == " +
922    TABLE_AGENTS + "." + KEY_ROWID,
923   
924    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
925    KEY_SCHED_DEP, TRIP_DELAY, TRIP_TIME},
926    KEY_AGENT + " = ? AND " +
927    "FROM_CITIES." + key_city + " = ? AND " +
928    "TO_CITIES." + key_city + " = ?",
929    new String[] {agent, from_city, to_city},
930    null, null, "strftime('%s', " + KEY_SCHED_DEP + ") DESC", null);
931    }
932   
 
933  0 toggle public Cursor fetch_operator_trips(String from_city, String to_city, String operator)
934    {
935  0 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
936  0 return mDbHelper.mDb.query(TABLE_TRIPS +
937    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
938    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
939    "FROM_STATIONS." + KEY_ROWID +
940   
941    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
942    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
943    "TO_STATIONS." + KEY_ROWID +
944   
945    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
946    " FROM_STATIONS." + KEY_CITY_ID + " == " +
947    "FROM_CITIES." + KEY_ROWID +
948   
949    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
950    " TO_STATIONS." + KEY_CITY_ID + " == " +
951    "TO_CITIES." + KEY_ROWID +
952   
953    " JOIN " + TABLE_OPERATORS + " ON " +
954    KEY_OPERATOR_ID + " == " +
955    TABLE_OPERATORS + "." + KEY_ROWID,
956   
957    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
958    KEY_SCHED_DEP, TRIP_DELAY, TRIP_TIME},
959    KEY_OPERATOR + " = ? AND " +
960    "FROM_CITIES." + key_city + " = ? AND " +
961    "TO_CITIES." + key_city + " = ?",
962    new String[] {operator, from_city, to_city},
963    null, null, "strftime('%s', " + KEY_SCHED_DEP + ") DESC", null);
964    }
965   
 
966  1 toggle public String get_most_freq_from_city()
967    {
968  1 String ret = null;
969  1 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
970   
971  1 Cursor c = mDbHelper.mDb.rawQuery("SELECT " + key_city +
972    " FROM " + TABLE_TRIPS + " JOIN " + TABLE_STATIONS +
973    " ON " + TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
974    TABLE_STATIONS + "." + KEY_ROWID +
975    " JOIN " + TABLE_CITIES +
976    " ON " + TABLE_STATIONS + "." + KEY_CITY_ID + " == " +
977    TABLE_CITIES + "." + KEY_ROWID +
978    " GROUP BY " + key_city +
979    " ORDER BY COUNT(" + KEY_CITY_ID + ") DESC LIMIT 1",
980    null);
981  1 if (c.moveToFirst()) {
982  1 ret = c.getString(c.getColumnIndex(key_city));
983    }
984  1 c.close();
985  1 return ret;
986    }
987   
 
988  4 toggle public String get_most_freq_to_city(String from_city)
989    {
990  4 String ret = null;
991  4 String key_city = KEY_CITY + "_" + mDbHelper.mCtx.getResources().getString(R.string.lang_code);
992   
993  4 Cursor c = mDbHelper.mDb.rawQuery("SELECT TO_CITIES." + key_city + " AS " + key_city +
994    " FROM " + TABLE_TRIPS +
995   
996    " JOIN " + TABLE_STATIONS + " AS FROM_STATIONS ON " +
997    TABLE_TRIPS + "." + KEY_FROM_STN_ID + " == " +
998    "FROM_STATIONS." + KEY_ROWID +
999   
1000    " JOIN " + TABLE_STATIONS + " AS TO_STATIONS ON " +
1001    TABLE_TRIPS + "." + KEY_TO_STN_ID + " == " +
1002    "TO_STATIONS." + KEY_ROWID +
1003   
1004    " JOIN " + TABLE_CITIES + " AS FROM_CITIES ON " +
1005    " FROM_STATIONS." + KEY_CITY_ID + " == " +
1006    "FROM_CITIES." + KEY_ROWID +
1007   
1008    " JOIN " + TABLE_CITIES + " AS TO_CITIES ON " +
1009    " TO_STATIONS." + KEY_CITY_ID + " == " +
1010    "TO_CITIES." + KEY_ROWID +
1011   
1012    " WHERE FROM_CITIES." + key_city + " == ? " +
1013    " GROUP BY TO_CITIES." + key_city +
1014    " ORDER BY COUNT(TO_CITIES." + key_city + ") DESC LIMIT 1",
1015    new String[] {from_city});
1016  4 if (c.moveToFirst()) {
1017  4 ret = c.getString(c.getColumnIndex(key_city));
1018    }
1019  4 c.close();
1020  4 return ret;
1021    }
1022   
 
1023  2 toggle public String getCompanyByOperator(long id)
1024    {
1025  2 return getCompany(id, KEY_OPERATOR);
1026    }
1027   
 
1028  1 toggle public String getCompanyByAgent(long id)
1029    {
1030  1 return getCompany(id, KEY_AGENT);
1031    }
1032   
 
1033  3 toggle private String getCompany(long id, String group_by)
1034    {
1035  3 String ret = null;
1036   
1037  3 Cursor c = mDbHelper.mDb.query(true,
1038    TABLE_TRIPS +
1039   
1040    " JOIN " + TABLE_AGENTS + " ON " +
1041    KEY_AGENT_ID + " == " +
1042    TABLE_AGENTS + "." + KEY_ROWID +
1043   
1044    " JOIN " + TABLE_OPERATORS + " ON " +
1045    KEY_OPERATOR_ID + " == " +
1046    TABLE_OPERATORS + "." + KEY_ROWID,
1047   
1048    new String[] {TABLE_TRIPS + "." + KEY_ROWID,
1049    KEY_AGENT, KEY_OPERATOR},
1050    TABLE_TRIPS + "." + KEY_ROWID + " = ?",
1051    new String[] {Long.toString(id)},
1052    group_by, null, null, null);
1053  3 if (c.moveToFirst()) {
1054  3 ret = c.getString(c.getColumnIndex(group_by));
1055    }
1056  3 c.close();
1057  3 return ret;
1058    }
1059   
 
1060  3 toggle public Cursor fetch_tmp_sched_time()
1061    {
1062  3 return mDbHelper.mDb.query(TABLE_TMP,
1063    new String[] {KEY_ROWID,
1064    "strftime(\"%Y\", " + KEY_SCHED_DEP + ")",
1065    "strftime(\"%m\", " + KEY_SCHED_DEP + ")",
1066    "strftime(\"%d\", " + KEY_SCHED_DEP + ")",
1067    "strftime(\"%H\", " + KEY_SCHED_DEP + ")",
1068    "strftime(\"%M\", " + KEY_SCHED_DEP + ")",
1069    },
1070    KEY_SCHED_DEP + " IS NOT NULL", null, null, null, null, "1");
1071    }
1072   
 
1073  3 toggle public Cursor fetch_tmp_depart_time()
1074    {
1075  3 return mDbHelper.mDb.query(TABLE_TMP,
1076    new String[] {KEY_ROWID,
1077    "strftime(\"%Y\", " + KEY_ACTUAL_DEP + ")",
1078    "strftime(\"%m\", " + KEY_ACTUAL_DEP + ")",
1079    "strftime(\"%d\", " + KEY_ACTUAL_DEP + ")",
1080    "strftime(\"%H\", " + KEY_ACTUAL_DEP + ")",
1081    "strftime(\"%M\", " + KEY_ACTUAL_DEP + ")",
1082    },
1083    KEY_ACTUAL_DEP + " IS NOT NULL", null, null, null, null, "1");
1084    }
1085   
 
1086  3 toggle public Cursor fetch_tmp_arrival_time()
1087    {
1088  3 return mDbHelper.mDb.query(TABLE_TMP,
1089    new String[] {KEY_ROWID,
1090    "strftime(\"%Y\", " + KEY_ARRIVAL + ")",
1091    "strftime(\"%m\", " + KEY_ARRIVAL + ")",
1092    "strftime(\"%d\", " + KEY_ARRIVAL + ")",
1093    "strftime(\"%H\", " + KEY_ARRIVAL + ")",
1094    "strftime(\"%M\", " + KEY_ARRIVAL + ")",
1095    },
1096    KEY_ARRIVAL + " IS NOT NULL", null, null, null, null, "1");
1097    }
1098   
 
1099  15 toggle public String fetch_tmp(String key)
1100    {
1101  15 Cursor c = mDbHelper.mDb.query(TABLE_TMP,
1102    new String[] {KEY_ROWID, key},
1103    key + " IS NOT NULL", null, null, null, null, "1");
1104  15 String ret = "";
1105  15 if (c.moveToFirst()) {
1106  0 ret = c.getString(c.getColumnIndex(key));
1107    }
1108  15 c.close();
1109  15 return ret;
1110    }
1111   
 
1112  2 toggle public Cursor fetch_tmp_complaint_sched_time()
1113    {
1114  2 return mDbHelper.mDb.query(TABLE_TMP_COMPLAINT,
1115    new String[] {KEY_ROWID,
1116    "strftime(\"%Y\", " + KEY_SCHED_DEP + ")",
1117    "strftime(\"%m\", " + KEY_SCHED_DEP + ")",
1118    "strftime(\"%d\", " + KEY_SCHED_DEP + ")",
1119    "strftime(\"%H\", " + KEY_SCHED_DEP + ")",
1120    "strftime(\"%M\", " + KEY_SCHED_DEP + ")",
1121    },
1122    KEY_SCHED_DEP + " IS NOT NULL", null, null, null, null, "1");
1123    }
1124   
 
1125  18 toggle public String fetch_tmp_complaint(String key)
1126    {
1127  18 Cursor c = mDbHelper.mDb.query(TABLE_TMP_COMPLAINT,
1128    new String[] {KEY_ROWID, key},
1129    key + " IS NOT NULL", null, null, null, null, "1");
1130  18 String ret = "";
1131  18 if (c.moveToFirst()) {
1132  0 ret = c.getString(c.getColumnIndex(key));
1133    }
1134  18 c.close();
1135  18 return ret;
1136    }
1137   
 
1138  3 toggle public int fetch_safety()
1139    {
1140  3 Cursor c = mDbHelper.mDb.query(TABLE_TMP,
1141    new String[] {KEY_ROWID, KEY_SAFETY},
1142    KEY_SAFETY + " IS NOT NULL", null, null, null, null, "1");
1143  3 int ret = 3;
1144  3 if (c.moveToFirst()) {
1145  0 ret = c.getInt(c.getColumnIndex(KEY_SAFETY));
1146    }
1147  3 c.close();
1148  3 return ret;
1149    }
1150   
 
1151  3 toggle public int fetch_comfort()
1152    {
1153  3 Cursor c = mDbHelper.mDb.query(TABLE_TMP,
1154    new String[] {KEY_ROWID, KEY_COMFORT},
1155    KEY_COMFORT + " IS NOT NULL", null, null, null, null, "1");
1156  3 int ret = 3;
1157  3 if (c.moveToFirst()) {
1158  0 ret = c.getInt(c.getColumnIndex(KEY_COMFORT));
1159    }
1160  3 c.close();
1161  3 return ret;
1162    }
1163   
 
1164  3 toggle public int fetch_overall()
1165    {
1166  3 Cursor c = mDbHelper.mDb.query(TABLE_TMP,
1167    new String[] {KEY_ROWID, KEY_OVERALL},
1168    KEY_OVERALL + " IS NOT NULL", null, null, null, null, "1");
1169  3 int ret = 3;
1170  3 if (c.moveToFirst()) {
1171  0 ret = c.getInt(c.getColumnIndex(KEY_OVERALL));
1172    }
1173  3 c.close();
1174  3 return ret;
1175    }
1176   
 
1177  10 toggle public long fetch_max_id(String table)
1178    {
1179  10 Cursor c = mDbHelper.mDb.query(table,
1180    new String[] {KEY_ROWID},
1181    null, null, null, null, KEY_ROWID + " DESC", "1");
1182  10 long ret = 0;
1183  10 if (c.moveToFirst()) {
1184  5 ret = c.getInt(c.getColumnIndex(KEY_ROWID));
1185    }
1186  10 c.close();
1187  10 return ret;
1188    }
1189   
 
1190  2 toggle public Cursor fetchOperatorReviews(String operator)
1191    {
1192  2 return fetchReviews(operator, KEY_OPERATOR);
1193    }
1194   
 
1195  1 toggle public Cursor fetchAgentReviews(String agent)
1196    {
1197  1 return fetchReviews(agent, KEY_AGENT);
1198    }
1199   
 
1200  3 toggle private Cursor fetchReviews(String company, String key)
1201    {
1202  3 return mDbHelper.mDb.rawQuery(
1203    "SELECT " + TABLE_TRIPS +
1204    "." + KEY_ROWID + " AS " + KEY_ROWID + ", " + KEY_COMMENT +
1205   
1206    " FROM " + TABLE_TRIPS +
1207   
1208    " JOIN " + TABLE_AGENTS + " ON " +
1209    KEY_AGENT_ID + " == " +
1210    TABLE_AGENTS + "." + KEY_ROWID +
1211   
1212    " JOIN " + TABLE_OPERATORS + " ON " +
1213    KEY_OPERATOR_ID + " == " +
1214    TABLE_OPERATORS + "." + KEY_ROWID +
1215   
1216    " WHERE " + key + " == ? AND " +
1217    " LENGTH(" + KEY_COMMENT + ") != 0 " +
1218    " ORDER BY strftime('%s', " + KEY_SCHED_DEP + ") DESC",
1219    new String[] {company});
1220    }
1221   
 
1222  1 toggle public void save_tmp(String agent, String operator,
1223    String from_station, String to_station,
1224    String scheduled_departure, String actual_departure,
1225    String arrival_time, int safety, int comfort,
1226    int overall, String comment)
1227    {
1228  1 ContentValues cv = new ContentValues();
1229  1 cv.put(KEY_AGENT, agent);
1230  1 cv.put(KEY_OPERATOR, operator);
1231  1 cv.put(KEY_FROM_STN, from_station);
1232  1 cv.put(KEY_TO_STN, to_station);
1233  1 cv.put(KEY_SCHED_DEP, scheduled_departure);
1234  1 cv.put(KEY_ACTUAL_DEP, actual_departure);
1235  1 cv.put(KEY_ARRIVAL, arrival_time);
1236  1 cv.put(KEY_SAFETY, safety);
1237  1 cv.put(KEY_COMFORT, comfort);
1238  1 cv.put(KEY_OVERALL, overall);
1239  1 cv.put(KEY_COMMENT, comment);
1240   
1241  1 Cursor c = mDbHelper.mDb.query(TABLE_TMP, new String[] {KEY_ROWID},
1242    null, null, null, null, null, "1");
1243  1 if (c.moveToFirst()) {
1244  0 long row_id = c.getInt(0);
1245  0 mDbHelper.mDb.update(TABLE_TMP, cv,
1246    KEY_ROWID + " = ?", new String[] {Long.toString(row_id)});
1247    } else {
1248  1 mDbHelper.mDb.insert(TABLE_TMP, null, cv);
1249    }
1250  1 c.close();
1251    }
1252   
 
1253  1 toggle public void save_tmp_complaint(String agent, String operator,
1254    String from_city, String from_station, String to_city,
1255    String to_station, String scheduled_departure,
1256    String counter, String comment, String reg)
1257    {
1258  1 ContentValues cv = new ContentValues();
1259  1 cv.put(KEY_AGENT, agent);
1260  1 cv.put(KEY_OPERATOR, operator);
1261  1 cv.put(KEY_FROM_CITY, from_city);
1262  1 cv.put(KEY_FROM_STN, from_station);
1263  1 cv.put(KEY_TO_CITY, to_city);
1264  1 cv.put(KEY_TO_STN, to_station);
1265  1 cv.put(KEY_SCHED_DEP, scheduled_departure);
1266  1 cv.put(KEY_CTR, counter);
1267  1 cv.put(KEY_COMMENT, comment);
1268  1 cv.put(KEY_REG, reg);
1269   
1270  1 Cursor c = mDbHelper.mDb.query(TABLE_TMP_COMPLAINT, new String[] {KEY_ROWID},
1271    null, null, null, null, null, "1");
1272  1 if (c.moveToFirst()) {
1273  0 long row_id = c.getInt(0);
1274  0 mDbHelper.mDb.update(TABLE_TMP_COMPLAINT, cv,
1275    KEY_ROWID + " = ?", new String[] {Long.toString(row_id)});
1276    } else {
1277  1 mDbHelper.mDb.insert(TABLE_TMP_COMPLAINT, null, cv);
1278    }
1279  1 c.close();
1280    }
1281   
 
1282  2 toggle public void clear_tmp_table()
1283    {
1284  2 mDbHelper.mDb.delete(TABLE_TMP, null, null);
1285    }
1286   
 
1287  1 toggle public void clear_tmp_complaint_table()
1288    {
1289  1 mDbHelper.mDb.delete(TABLE_TMP_COMPLAINT, null, null);
1290    }
1291   
1292    /** @return seconds from last update until now */
 
1293  1 toggle private int sec_since_last_update()
1294    {
1295  1 Cursor c = mDbHelper.mDb.query(TABLE_LAST_UPDATE,
1296    new String[] {
1297    "strftime(\"%s\", 'now') - strftime(\"%s\", " + KEY_LAST_UPDATE + ")"
1298    },
1299    null, null, null, null, null, "1");
1300  1 int ret = c.moveToFirst() ? c.getInt(0) : Integer.MAX_VALUE;
1301  1 c.close();
1302   
1303  1 return ret;
1304    }
1305   
1306    /** @return true if database has never been synced */
 
1307  1 toggle private boolean never_updated()
1308    {
1309  1 Cursor c = mDbHelper.mDb.query(TABLE_LAST_UPDATE,
1310    new String[] { KEY_LAST_UPDATE },
1311    null, null, null, null, null, "1");
1312  1 return !c.moveToFirst();
1313    }
1314   
1315    /** @return last update in YYYY-mm-DD HH:MM:SS format */
 
1316  2 toggle public String getLastUpdate()
1317    {
1318  2 Cursor c = mDbHelper.mDb.query(TABLE_LAST_UPDATE,
1319    new String[] {KEY_LAST_UPDATE},
1320    null, null, null, null, null, "1");
1321  2 String ret = "1970-01-01 00:00:00";
1322  2 if (c.moveToFirst()) {
1323  1 ret = c.getString(c.getColumnIndex(KEY_LAST_UPDATE));
1324    }
1325  2 c.close();
1326  2 return ret;
1327    }
1328   
 
1329  2 toggle public void setLastUpdate()
1330    {
1331  2 Cursor c = mDbHelper.mDb.rawQuery("SELECT strftime(\"%Y-%m-%d %H:%M:%S\", 'now')", null);
1332  2 if (!c.moveToFirst()) {
1333  0 c.close();
1334  0 return;
1335    }
1336  2 String last_update = c.getString(0);
1337  2 c.close();
1338   
1339  2 ContentValues cv = new ContentValues();
1340  2 cv.put(KEY_LAST_UPDATE, last_update);
1341   
1342  2 mDbHelper.mDb.delete(TABLE_LAST_UPDATE, null, null);
1343  2 mDbHelper.mDb.insert(TABLE_LAST_UPDATE, null, cv);
1344    }
1345   
 
1346  2 toggle public float getOperatorRating(String operator)
1347    {
1348  2 return getRating(operator, KEY_OPERATOR);
1349    }
1350   
 
1351  1 toggle public float getAgentRating(String agent)
1352    {
1353  1 return getRating(agent, KEY_AGENT);
1354    }
1355   
 
1356  3 toggle private float getRating(String company, String key)
1357    {
1358  3 Cursor c = mDbHelper.mDb.rawQuery(
1359    "SELECT " +
1360    "0.75 * RATINGS." + AVG_OVERALL + " + " +
1361    "0.25 * min(5, max(1, (25 - avg_delay / 60.) / 5.)) AS " + AVG_OVERALL +
1362    " FROM " +
1363   
1364    " (SELECT " +
1365    key + ", " +
1366    " AVG(" + TRIP_DELAY + ") AS avg_delay" +
1367   
1368    " FROM " + TABLE_TRIPS +
1369   
1370    " JOIN " + TABLE_AGENTS + " ON " +
1371    TABLE_TRIPS + "." + KEY_AGENT_ID + " == " +
1372    TABLE_AGENTS + "." + KEY_ROWID +
1373   
1374    " JOIN " + TABLE_OPERATORS + " ON " +
1375    KEY_OPERATOR_ID + " == " +
1376    TABLE_OPERATORS + "." + KEY_ROWID +
1377   
1378    " WHERE " + key + " == ? AND " +
1379    KEY_ARRIVAL + " != 'Cancelled' " +
1380   
1381    " GROUP BY " + key + ") " +
1382    " AS TIMES " +
1383   
1384    " LEFT JOIN " +
1385   
1386    "(SELECT " +
1387    key + ", " +
1388    " 0.5 * AVG(" + KEY_OVERALL + ") + " +
1389    " 0.25 * AVG(" + KEY_SAFETY + ") + " +
1390    " 0.25 * AVG(" + KEY_COMFORT + ") " +
1391    " AS " + AVG_OVERALL +
1392   
1393    " FROM " + TABLE_TRIPS +
1394   
1395    " JOIN " + TABLE_AGENTS + " ON " +
1396    TABLE_TRIPS + "." + KEY_AGENT_ID + " == " +
1397    TABLE_AGENTS + "." + KEY_ROWID +
1398   
1399    " JOIN " + TABLE_OPERATORS + " ON " +
1400    KEY_OPERATOR_ID + " == " +
1401    TABLE_OPERATORS + "." + KEY_ROWID +
1402   
1403    " WHERE " + key + " == ? AND " +
1404    " length(" + KEY_OVERALL + ") != 0 AND " +
1405    " length(" + KEY_SAFETY + ") != 0 AND " +
1406    " length(" + KEY_COMFORT + ") != 0 " +
1407   
1408    " GROUP BY " + key + ") " +
1409   
1410    " AS RATINGS ON " +
1411    "TIMES." + key + " == " +
1412    " RATINGS." + key,
1413   
1414    new String[] {company, company});
1415  3 float ret = 0f;
1416  3 if (c.moveToFirst()) {
1417  3 ret = c.getFloat(c.getColumnIndex(AVG_OVERALL));
1418    }
1419  3 c.close();
1420  3 return ret;
1421    }
1422   
 
1423  2 toggle public float getOperatorComfort(String operator)
1424    {
1425  2 return getRatingAvg(operator, KEY_OPERATOR, KEY_COMFORT);
1426    }
1427   
 
1428  1 toggle public float getAgentComfort(String agent)
1429    {
1430  1 return getRatingAvg(agent, KEY_AGENT, KEY_COMFORT);
1431    }
1432   
 
1433  2 toggle public float getOperatorSafety(String operator)
1434    {
1435  2 return getRatingAvg(operator, KEY_OPERATOR, KEY_SAFETY);
1436    }
1437   
 
1438  1 toggle public float getAgentSafety(String agent)
1439    {
1440  1 return getRatingAvg(agent, KEY_AGENT, KEY_SAFETY);
1441    }
1442   
 
1443  6 toggle private float getRatingAvg(String company, String key, String ratingKey)
1444    {
1445  6 Cursor c = mDbHelper.mDb.rawQuery(
1446    "SELECT " +
1447    key + ", " +
1448    " AVG(" + ratingKey + ") " +
1449    " AS RESULT " +
1450   
1451    " FROM " + TABLE_TRIPS +
1452   
1453    " JOIN " + TABLE_AGENTS + " ON " +
1454    TABLE_TRIPS + "." + KEY_AGENT_ID + " == " +
1455    TABLE_AGENTS + "." + KEY_ROWID +
1456   
1457    " JOIN " + TABLE_OPERATORS + " ON " +
1458    KEY_OPERATOR_ID + " == " +
1459    TABLE_OPERATORS + "." + KEY_ROWID +
1460   
1461    " WHERE " + key + " == ? AND " +
1462    " length(" + ratingKey + ") != 0 " +
1463   
1464    " GROUP BY " + key,
1465    new String[] {company});
1466  6 float ret = 0f;
1467  6 if (c.moveToFirst()) {
1468  6 ret = c.getFloat(c.getColumnIndex("RESULT"));
1469    }
1470  6 c.close();
1471  6 return ret;
1472    }
1473    }