· 8 years ago · Dec 26, 2017, 08:12 AM
1// ****** This is Example for using OrderDbHelper ******* //
2// let orders = {
3// 'TXNO':'TestTXNO',
4// 'PAYLOAD':'TestPayload',
5// 'DRIVERPAYLOAD':'TestDriverPayload',
6// 'IS_MULTI':'TestMulti'
7// };
8// **** Save Order ****
9// this.orderDB.saveOrders(orders).then((result) => {
10// MyApp.consoleLog(Home.TAG + " SAVE ORDER", true);
11// }, error => {
12// MyApp.consoleLog(Home.TAG + " SAVE ORDER", error);
13// });
14// **** Get Order ****
15// this.orderDB.getData().then((data) => {
16// MyApp.consoleLog(Home.TAG + " GETDATA:", data);
17// });
18// ********** END OF EXAMPLE ********* //
19
20import { Injectable } from '@angular/core';
21import 'rxjs/add/operator/map';
22import { MyApp } from '../app/app.component';
23import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
24
25/*
26 Generated class for the OrderDbHelper provider.
27
28 See https://angular.io/docs/ts/latest/guide/dependency-injection.html
29 for more info on providers and Angular 2 DI.
30*/
31@Injectable()
32export class OrderDbHelper {
33 public static DB_NAME = "order.db";
34 public static LOCATION = "default";
35
36 public TABLE_ORDER:string = 'orders';
37
38 // KEY Variables
39 public KEY_ID:string = "id";
40 public KEY_TXNO:string = "txno";
41 public KEY_TIMESTAMP:string = "timestamp";
42 public KEY_PAYLOAD:string = "payload";
43 public KEY_IS_MULTI:string = "is_multi";
44 public KEY_FROM:string = "start";
45 public KEY_TO:string = "target";
46 public KEY_DISTANCE:string = "distance";
47 public KEY_DURATION:string = "duration";
48 public KEY_FARES:string = "fares";
49 public KEY_DRIVER:string = "driver";
50 public KEY_DRIVERPAYLOAD:string = "driver_payload";
51 public KEY_RATING:string = "rating";
52 public KEY_STATUS:string = "status";
53 public KEY_DRIVER_TOKEN:string = "client_token";
54 public KEY_PROMO_CODE:string = "promo_code";
55
56 public SQL_CREATE_ORDER_TABLE:string = "CREATE TABLE IF NOT EXISTS " +
57 this.TABLE_ORDER + "(" +
58 this.KEY_ID + " INTERGER PRIMARY KEY," +
59 this.KEY_TXNO + " TEXT," +
60 this.KEY_TIMESTAMP + " REAL," +
61 this.KEY_PAYLOAD + " BLOB," +
62 this.KEY_DRIVERPAYLOAD + " BLOB," +
63 this.KEY_IS_MULTI + " INTEGER," +
64 this.KEY_FROM + " TEXT," +
65 this.KEY_TO + " TEXT," +
66 this.KEY_DISTANCE + " TEXT," +
67 this.KEY_DURATION + " TEXT," +
68 this.KEY_FARES + " TEXT," +
69 this.KEY_DRIVER + " INTEGER," +
70 this.KEY_RATING + " FLOAT," +
71 this.KEY_PROMO_CODE + " TEXT," +
72 this.KEY_STATUS + " INTEGER DEFAULT 0," +
73 this.KEY_DRIVER_TOKEN + " TEXT" + ")";
74
75 public SQL_TRUNCATE_ORDER:string = "DELETE FROM " + this.TABLE_ORDER;
76 public SQL_DELETE_ORDER:string = "DROP TABLE IF EXISTS " + this.TABLE_ORDER;
77
78 public isOpen: boolean;
79 public static TAG: string = "OrderDBHelper, ";
80 public database;
81 public static STATUS_CANCEL = 1;
82 public static STATUS_COMPLETE = 3;
83
84 constructor() {
85 // this.initializeOrderTable();
86 }
87
88 public initializeOrderTable() {
89 this.database = new SQLite();
90 this.database.create({
91 name: "order.db",
92 location: "default"
93 }).then((db: SQLiteObject) => {
94 this.database = db;
95 this.database.executeSql(this.SQL_CREATE_ORDER_TABLE, {})
96 .then((data) => {
97 MyApp.consoleLog(OrderDbHelper.TAG + " Table Order Created", data);
98 }, error => {
99 MyApp.consoleError("Unable to exec SQLite", error);
100 });
101 }, error => {
102 MyApp.consoleError("Unable to Open Database", error);
103 });
104 }
105
106 public saveOrders(params) {
107 let key = [];
108 let val = [];
109
110 let tx_id = 1;
111
112 params['ID'] = tx_id;
113 if(typeof params['ID'] === 'undefined' || params['ID'] == '') {
114 params['ID'] = tx_id;
115 }
116
117 for(let data in params) {
118 if(params.hasOwnProperty(data)) {
119 key.push(this['KEY_' + data]);
120 val.push("'" + params[data] + "'");
121 }
122 }
123 let addQuery = "INSERT INTO " + this.TABLE_ORDER + "(" +
124 key.join(',') + ") VALUES (" + val.join(',') + ")";
125
126 MyApp.consoleLog(OrderDbHelper.TAG, addQuery);
127 return new Promise((resolve, reject) => {
128 this.getData().then((data) => {
129
130 // Check if DB is not null
131 if(data == 0)
132 {
133 this.database.executeSql(addQuery, []).then((data) => {
134 resolve(data)
135 }, error => {
136 MyApp.consoleError(OrderDbHelper.TAG + " data null", error);
137 reject(error);
138 });
139 }
140 else {
141 this.deleteOrder();
142 this.database.executeSql(addQuery, []).then((data) => {
143 resolve(data)
144 }, error => {
145 MyApp.consoleError(OrderDbHelper.TAG + " replace data", error);
146 reject(error);
147 });
148 }
149 });
150 });
151 }
152
153 public saveOrdersMulti(id, params) {
154 let key = [];
155 let val = [];
156
157 let tx_id = id;
158
159 params['ID'] = tx_id;
160 if(typeof params['ID'] === 'undefined' || params['ID'] == '') {
161 params['ID'] = tx_id;
162 }
163
164 for(let data in params) {
165 if(params.hasOwnProperty(data)) {
166 key.push(this['KEY_' + data]);
167 val.push("'" + params[data] + "'");
168 }
169 }
170 let addQuery = "INSERT INTO " + this.TABLE_ORDER + "(" +
171 key.join(',') + ") VALUES (" + val.join(',') + ")";
172
173 MyApp.consoleLog(OrderDbHelper.TAG, addQuery);
174 return new Promise((resolve, reject) => {
175 this.getDataMulti(params['ID']).then((data) => {
176
177 // Check if DB is not null
178 if(data == 0)
179 {
180 this.database.executeSql(addQuery, []).then((data) => {
181 resolve(data)
182 }, error => {
183 MyApp.consoleError(OrderDbHelper.TAG + " data null", error);
184 reject(error);
185 });
186 }
187 else {
188 this.deleteOrderMultiPromise(params['ID'], addQuery) ;
189 resolve(data);
190 }
191 },error => { reject(error); }
192
193 );
194 });
195 }
196
197 public update(params, whereClause:String) {
198 let key = [];
199
200 for(let data in params) {
201 if(params.hasOwnProperty(data)) {
202 key.push(this['KEY_' + data] + " = '" + params[data] + "'");
203 }
204 }
205
206 let updQuery = "UPDATE " + this.TABLE_ORDER + " SET " + key.join(', ') +
207 " WHERE " + whereClause;
208
209 MyApp.consoleLog(OrderDbHelper.TAG, updQuery);
210 return new Promise((resolve, reject) => {
211 this.database.executeSql(updQuery, []).then((data) => {
212 resolve(data)
213 }, error => {
214 MyApp.consoleError(OrderDbHelper.TAG + " data null", error);
215 reject(error);
216 });
217 });
218 }
219
220 public getData() {
221 let query = "SELECT * FROM " + this.TABLE_ORDER;
222 return new Promise((resolve, reject) => {
223 this.database.executeSql(query, []).then((data) => {
224 let order = [];
225 if(data.rows.length > 0) {
226 for(let i = 0; i < data.rows.length; i++) {
227 order.push({
228 'ID':data.rows.item(i)[this.KEY_ID],
229 'TXNO':data.rows.item(i)[this.KEY_TXNO],
230 'TIMESTAMP':data.rows.item(i)[this.KEY_TIMESTAMP],
231 'PAYLOAD':data.rows.item(i)[this.KEY_PAYLOAD],
232 'IS_MULTI':data.rows.item(i)[this.KEY_IS_MULTI],
233 'FROM':data.rows.item(i)[this.KEY_FROM],
234 'TO':data.rows.item(i)[this.KEY_TO],
235 'DISTANCE':data.rows.item(i)[this.KEY_DISTANCE],
236 'DURATION':data.rows.item(i)[this.KEY_DURATION],
237 'FARES':data.rows.item(i)[this.KEY_FARES],
238 'DRIVER':data.rows.item(i)[this.KEY_DRIVER],
239 'DRIVERPAYLOAD':data.rows.item(i)[this.KEY_DRIVERPAYLOAD],
240 'RATING':data.rows.item(i)[this.KEY_RATING],
241 'STATUS':data.rows.item(i)[this.KEY_STATUS],
242 'DRIVER_TOKEN':data.rows.item(i)[this.KEY_DRIVER_TOKEN],
243 'PROMO_CODE':data.rows.item(i)[this.KEY_PROMO_CODE],
244 });
245 }
246 }
247 resolve(order);
248 }, error => {
249 MyApp.consoleError(OrderDbHelper.TAG, error);
250 reject(error);
251 });
252 });
253 }
254
255 public getDataMulti(id) {
256 let query = "SELECT * FROM " + this.TABLE_ORDER + " WHERE " + this.KEY_ID + " = " + id ;
257 return new Promise((resolve, reject) => {
258 this.database.executeSql(query, []).then((data) => {
259 let order = [];
260 if(data.rows.length > 0) {
261 for(let i = 0; i < data.rows.length; i++) {
262 order.push({
263 'ID':data.rows.item(i)[this.KEY_ID],
264 'TXNO':data.rows.item(i)[this.KEY_TXNO],
265 'TIMESTAMP':data.rows.item(i)[this.KEY_TIMESTAMP],
266 'PAYLOAD':data.rows.item(i)[this.KEY_PAYLOAD],
267 'IS_MULTI':data.rows.item(i)[this.KEY_IS_MULTI],
268 'FROM':data.rows.item(i)[this.KEY_FROM],
269 'TO':data.rows.item(i)[this.KEY_TO],
270 'DISTANCE':data.rows.item(i)[this.KEY_DISTANCE],
271 'DURATION':data.rows.item(i)[this.KEY_DURATION],
272 'FARES':data.rows.item(i)[this.KEY_FARES],
273 'DRIVER':data.rows.item(i)[this.KEY_DRIVER],
274 'DRIVERPAYLOAD':data.rows.item(i)[this.KEY_DRIVERPAYLOAD],
275 'RATING':data.rows.item(i)[this.KEY_RATING],
276 'STATUS':data.rows.item(i)[this.KEY_STATUS],
277 'DRIVER_TOKEN':data.rows.item(i)[this.KEY_DRIVER_TOKEN],
278 'PROMO_CODE':data.rows.item(i)[this.KEY_PROMO_CODE],
279 });
280 }
281 }
282 resolve(order);
283 }, error => {
284 MyApp.consoleError(OrderDbHelper.TAG, error);
285 reject(error);
286 });
287 });
288 }
289
290 public getDataAll() {
291 let query = "SELECT * FROM " + this.TABLE_ORDER;
292 return new Promise((resolve, reject) => {
293 this.database.executeSql(query, []).then((data) => {
294 let order = [];
295 if(data.rows.length > 0) {
296 for(let i = 0; i < data.rows.length; i++) {
297 order.push({
298 'ID':data.rows.item(i)[this.KEY_ID],
299 'TXNO':data.rows.item(i)[this.KEY_TXNO],
300 'TIMESTAMP':data.rows.item(i)[this.KEY_TIMESTAMP],
301 'PAYLOAD':data.rows.item(i)[this.KEY_PAYLOAD],
302 'IS_MULTI':data.rows.item(i)[this.KEY_IS_MULTI],
303 'FROM':data.rows.item(i)[this.KEY_FROM],
304 'TO':data.rows.item(i)[this.KEY_TO],
305 'DISTANCE':data.rows.item(i)[this.KEY_DISTANCE],
306 'DURATION':data.rows.item(i)[this.KEY_DURATION],
307 'FARES':data.rows.item(i)[this.KEY_FARES],
308 'DRIVER':data.rows.item(i)[this.KEY_DRIVER],
309 'DRIVERPAYLOAD':data.rows.item(i)[this.KEY_DRIVERPAYLOAD],
310 'RATING':data.rows.item(i)[this.KEY_RATING],
311 'STATUS':data.rows.item(i)[this.KEY_STATUS],
312 'DRIVER_TOKEN':data.rows.item(i)[this.KEY_DRIVER_TOKEN],
313 'PROMO_CODE':data.rows.item(i)[this.KEY_PROMO_CODE],
314 });
315 }
316 }
317 resolve(order);
318 }, error => {
319 MyApp.consoleError(OrderDbHelper.TAG, error);
320 reject(error);
321 });
322 });
323 }
324
325 public deleteOrder() {
326 this.database.executeSql(this.SQL_TRUNCATE_ORDER, []).then(() => {
327 MyApp.consoleLog(OrderDbHelper.TAG, 'Delete OrderDB');
328 return true;
329 }, error => {
330 MyApp.consoleError(OrderDbHelper.TAG, error);
331 return false;
332 });
333 }
334
335 public deleteOrderMulti(id) {
336 this.database.executeSql("DELETE FROM " + this.TABLE_ORDER + " WHERE " + this.KEY_ID + " = " + id , []).then(() => {
337 MyApp.consoleLog(OrderDbHelper.TAG, 'Delete OrderDB');
338 return true;
339 }, error => {
340 MyApp.consoleError(OrderDbHelper.TAG, error);
341 return false;
342 });
343 }
344
345 public deleteOrderMultiPromise(id, addQuery) {
346 this.database.executeSql("DELETE FROM " + this.TABLE_ORDER + " WHERE " + this.KEY_ID + " = " + id , []).then(() => {
347
348 MyApp.consoleLog(OrderDbHelper.TAG, 'Delete OrderDB');
349 MyApp.consoleLog(OrderDbHelper.TAG , "Delete MultiPromise Success");
350 this.database.executeSql(addQuery, []).then(() => {
351
352 MyApp.consoleLog(OrderDbHelper.TAG , " INSERT data Success");
353 return true;
354 }, error => {
355
356 MyApp.consoleError(OrderDbHelper.TAG + " REPLACE data Error ", error);
357 return false;
358 });
359 }, error => {
360 MyApp.consoleError(OrderDbHelper.TAG, error);
361 this.database.executeSql(addQuery, []).then(() => {
362
363 MyApp.consoleError(OrderDbHelper.TAG , " INSERT data Success 1002");
364 return true;
365 }, error => {
366
367 MyApp.consoleError(OrderDbHelper.TAG + " REPLACE data Error", error);
368 return false;
369 });
370 });
371 }
372
373 public deleteTx(txno) {
374 this.database.executeSql("DELETE FROM " + this.TABLE_ORDER + " WHERE " + this.KEY_TXNO + " = " + txno , []).then(() => {
375 MyApp.consoleLog(OrderDbHelper.TAG, 'Delete OrderDB');
376 return true;
377 }, error => {
378 MyApp.consoleError(OrderDbHelper.TAG, error);
379 return false;
380 });
381 }
382
383 public moveID(txno, newID) {
384 //Execute delete newID
385 //Update currentID jadi newID
386 MyApp.consoleLog(OrderDbHelper.TAG, 'Delete OrderDB');
387 let updateParams = { 'ID' : newID };
388 // updateParams[this.KEY_ID] = newID;
389 let whereParams = this.KEY_TXNO + " = " + txno;
390 this.update(updateParams, whereParams);
391 return true;
392 }
393}