1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
|
5 |
|
|
6 |
|
|
7 |
|
|
8 |
|
|
9 |
|
|
10 |
|
|
11 |
|
|
12 |
|
|
13 |
|
|
14 |
|
|
15 |
|
|
16 |
|
|
17 |
|
|
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 |
|
|
|
|
| 85,6% |
Uncovered Elements: 49 (341) |
Complexity: 93 |
Complexity Density: 0,41 |
|
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 |
|
|
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 |
|
|
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 |
|
|
|
|
| 81,1% |
Uncovered Elements: 7 (37) |
Complexity: 8 |
Complexity Density: 0,28 |
|
181 |
|
private static class DatabaseHelper extends SQLiteOpenHelper |
182 |
|
{ |
183 |
|
private final Context mCtx; |
184 |
|
private boolean mAllowSync; |
185 |
|
public SQLiteDatabase mDb; |
186 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
187 |
31
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (16) |
Complexity: 1 |
Complexity Density: 0,06 |
|
194 |
1
|
@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 |
|
|
|
|
| 0% |
Uncovered Elements: 5 (5) |
Complexity: 2 |
Complexity Density: 0,4 |
|
215 |
0
|
@Override... |
216 |
|
public void onUpgrade(SQLiteDatabase db, int old_ver, int new_ver) |
217 |
|
{ |
218 |
0
|
switch (old_ver) { |
219 |
|
|
220 |
|
|
221 |
|
|
222 |
0
|
case 14: |
223 |
0
|
default: |
224 |
0
|
onCreate(db); |
225 |
0
|
break; |
226 |
|
} |
227 |
|
} |
228 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
229 |
20
|
public void open()... |
230 |
|
{ |
231 |
20
|
mDb = getReadableDatabase(); |
232 |
|
} |
233 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
234 |
11
|
public void open_readwrite() throws SQLException... |
235 |
|
{ |
236 |
11
|
mDb = getWritableDatabase(); |
237 |
|
} |
238 |
|
|
|
|
| 80% |
Uncovered Elements: 1 (5) |
Complexity: 2 |
Complexity Density: 0,67 |
|
239 |
31
|
@Override... |
240 |
|
public synchronized void close() |
241 |
|
{ |
242 |
31
|
if (mDb != null) { |
243 |
31
|
mDb.close(); |
244 |
|
} |
245 |
31
|
super.close(); |
246 |
|
} |
247 |
|
} |
248 |
|
|
|
|
| - |
Uncovered Elements: 0 (0) |
Complexity: 1 |
Complexity Density: - |
|
249 |
29
|
public DbAdapter()... |
250 |
|
{ |
251 |
|
} |
252 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
253 |
20
|
public DbAdapter open(Context ctx)... |
254 |
|
{ |
255 |
20
|
mDbHelper = new DatabaseHelper(ctx, false); |
256 |
20
|
mDbHelper.open(); |
257 |
20
|
return this; |
258 |
|
} |
259 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
260 |
1
|
public DbAdapter open_readwrite(Context ctx) throws SQLException... |
261 |
|
{ |
262 |
1
|
return open_readwrite(ctx, true); |
263 |
|
} |
264 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
265 |
11
|
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 |
|
|
|
|
| 83,3% |
Uncovered Elements: 1 (6) |
Complexity: 2 |
Complexity Density: 0,5 |
|
272 |
1
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
281 |
31
|
public void close()... |
282 |
|
{ |
283 |
31
|
mDbHelper.close(); |
284 |
|
} |
285 |
|
|
286 |
|
@return |
|
|
| 0% |
Uncovered Elements: 1 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
287 |
0
|
public long create_city(ContentValues city)... |
288 |
|
{ |
289 |
0
|
return replace(city, TABLE_CITIES); |
290 |
|
} |
291 |
|
|
292 |
|
@return |
|
|
| 0% |
Uncovered Elements: 1 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
293 |
0
|
public long create_station(ContentValues station)... |
294 |
|
{ |
295 |
0
|
return replace(station, TABLE_STATIONS); |
296 |
|
} |
297 |
|
|
298 |
|
@return |
|
|
| 0% |
Uncovered Elements: 1 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
299 |
0
|
public long create_trip(ContentValues trip)... |
300 |
|
{ |
301 |
0
|
return replace(trip, TABLE_TRIPS); |
302 |
|
} |
303 |
|
|
304 |
|
@return |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
305 |
368
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (23) |
Complexity: 5 |
Complexity Density: 0,33 |
|
311 |
368
|
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 |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
340 |
3
|
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 |
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
357 |
3
|
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 |
377 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
378 |
1
|
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 |
404 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
405 |
2
|
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 |
431 |
|
|
432 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
433 |
6
|
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 |
465 |
|
|
466 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
467 |
13
|
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 |
494 |
|
|
495 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
496 |
2
|
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 |
532 |
|
|
533 |
|
@param |
534 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
535 |
3
|
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 |
572 |
|
|
573 |
|
@param |
574 |
|
@param |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
575 |
5
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
611 |
15
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
626 |
4
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
633 |
5
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
640 |
5
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
647 |
2
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 1 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
654 |
0
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 1 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
661 |
0
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
668 |
11
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
673 |
12
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (3) |
Complexity: 1 |
Complexity Density: 0,33 |
|
678 |
23
|
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 |
|
|
|
|
| 88,9% |
Uncovered Elements: 1 (9) |
Complexity: 2 |
Complexity Density: 0,29 |
|
766 |
23
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 2 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
803 |
0
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 2 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
838 |
0
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
874 |
1
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
887 |
2
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 2 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
900 |
0
|
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 |
|
|
|
|
| 0% |
Uncovered Elements: 2 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
933 |
0
|
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 |
|
|
|
|
| 88,9% |
Uncovered Elements: 1 (9) |
Complexity: 2 |
Complexity Density: 0,29 |
|
966 |
1
|
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 |
|
|
|
|
| 88,9% |
Uncovered Elements: 1 (9) |
Complexity: 2 |
Complexity Density: 0,29 |
|
988 |
4
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1023 |
2
|
public String getCompanyByOperator(long id)... |
1024 |
|
{ |
1025 |
2
|
return getCompany(id, KEY_OPERATOR); |
1026 |
|
} |
1027 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1028 |
1
|
public String getCompanyByAgent(long id)... |
1029 |
|
{ |
1030 |
1
|
return getCompany(id, KEY_AGENT); |
1031 |
|
} |
1032 |
|
|
|
|
| 87,5% |
Uncovered Elements: 1 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1033 |
3
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1060 |
3
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1073 |
3
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1086 |
3
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 2 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1099 |
15
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1112 |
2
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 2 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1125 |
18
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 2 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1138 |
3
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 2 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1151 |
3
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 2 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1164 |
3
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1177 |
10
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1190 |
2
|
public Cursor fetchOperatorReviews(String operator)... |
1191 |
|
{ |
1192 |
2
|
return fetchReviews(operator, KEY_OPERATOR); |
1193 |
|
} |
1194 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1195 |
1
|
public Cursor fetchAgentReviews(String agent)... |
1196 |
|
{ |
1197 |
1
|
return fetchReviews(agent, KEY_AGENT); |
1198 |
|
} |
1199 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1200 |
3
|
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 |
|
|
|
|
| 85% |
Uncovered Elements: 3 (20) |
Complexity: 2 |
Complexity Density: 0,11 |
|
1222 |
1
|
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 |
|
|
|
|
| 84,2% |
Uncovered Elements: 3 (19) |
Complexity: 2 |
Complexity Density: 0,12 |
|
1253 |
1
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1282 |
2
|
public void clear_tmp_table()... |
1283 |
|
{ |
1284 |
2
|
mDbHelper.mDb.delete(TABLE_TMP, null, null); |
1285 |
|
} |
1286 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1287 |
1
|
public void clear_tmp_complaint_table()... |
1288 |
|
{ |
1289 |
1
|
mDbHelper.mDb.delete(TABLE_TMP_COMPLAINT, null, null); |
1290 |
|
} |
1291 |
|
|
1292 |
|
@return |
|
|
| 83,3% |
Uncovered Elements: 1 (6) |
Complexity: 2 |
Complexity Density: 0,5 |
|
1293 |
1
|
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 |
|
|
| 100% |
Uncovered Elements: 0 (2) |
Complexity: 1 |
Complexity Density: 0,5 |
|
1307 |
1
|
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 |
|
|
| 100% |
Uncovered Elements: 0 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1316 |
2
|
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 |
|
|
|
|
| 75% |
Uncovered Elements: 3 (12) |
Complexity: 2 |
Complexity Density: 0,2 |
|
1329 |
2
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1346 |
2
|
public float getOperatorRating(String operator)... |
1347 |
|
{ |
1348 |
2
|
return getRating(operator, KEY_OPERATOR); |
1349 |
|
} |
1350 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1351 |
1
|
public float getAgentRating(String agent)... |
1352 |
|
{ |
1353 |
1
|
return getRating(agent, KEY_AGENT); |
1354 |
|
} |
1355 |
|
|
|
|
| 87,5% |
Uncovered Elements: 1 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1356 |
3
|
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 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1423 |
2
|
public float getOperatorComfort(String operator)... |
1424 |
|
{ |
1425 |
2
|
return getRatingAvg(operator, KEY_OPERATOR, KEY_COMFORT); |
1426 |
|
} |
1427 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1428 |
1
|
public float getAgentComfort(String agent)... |
1429 |
|
{ |
1430 |
1
|
return getRatingAvg(agent, KEY_AGENT, KEY_COMFORT); |
1431 |
|
} |
1432 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1433 |
2
|
public float getOperatorSafety(String operator)... |
1434 |
|
{ |
1435 |
2
|
return getRatingAvg(operator, KEY_OPERATOR, KEY_SAFETY); |
1436 |
|
} |
1437 |
|
|
|
|
| 100% |
Uncovered Elements: 0 (1) |
Complexity: 1 |
Complexity Density: 1 |
|
1438 |
1
|
public float getAgentSafety(String agent)... |
1439 |
|
{ |
1440 |
1
|
return getRatingAvg(agent, KEY_AGENT, KEY_SAFETY); |
1441 |
|
} |
1442 |
|
|
|
|
| 87,5% |
Uncovered Elements: 1 (8) |
Complexity: 2 |
Complexity Density: 0,33 |
|
1443 |
6
|
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 |
|
} |