· 9 years ago · Oct 13, 2016, 07:00 PM
1import {Observable} from "rxjs";
2import {Injectable} from "@angular/core";
3import {SQLite} from "ionic-native";
4import {Platform} from "ionic-angular";
5declare var window: any;
6
7@Injectable()
8export class CustomDBService {
9
10 //private _storage: Storage;
11 private dbName: string = "pontuall-mobile.db";
12 private scriptCreates: string[] = [
13 `CREATE TABLE IF NOT EXISTS jwt_token_info
14 (
15 id INTEGER PRIMARY KEY,
16 token TEXT,
17 loggedrouters TEXT,
18 expires NUMBER,
19 manter_logado NUMBER,
20 contexto TEXT
21 ) `,
22 `CREATE TABLE IF NOT EXISTS abastecimento
23 (
24 id TEXT PRIMARY KEY,
25 usuarioAppId INTEGER,
26 veiculoId INTEGER,
27 kmAtual DOUBLE,
28 tipoCombustivel TEXT,
29 dataHora TEXT,
30 quantidadeLitros DOUBLE,
31 valor DOUBLE,
32 latitude DOUBLE,
33 longitude DOUBLE,
34 particular INTEGER,
35 version TEXT
36 ) `,
37 `CREATE TABLE IF NOT EXISTS veiculo
38 (
39 id TEXT PRIMARY KEY,
40 kmInicial DOUBLE,
41 statusVeiculo INTEGER,
42 cor TEXT,
43 placa TEXT,
44 marca TEXT,
45 ano INTEGER,
46 modelo TEXT,
47 dataCadastro TEXT,
48 version TEXT
49 ) `
50 ,
51 `CREATE TABLE IF NOT EXISTS marcacao_avulsa
52 (
53 id TEXT PRIMARY KEY,
54 usuarioAppId TEXT,
55 dataHora TEXT,
56 observacao TEXT,
57 latitude DOUBLE,
58 longitude DOUBLE,
59 precisao DOUBLE,
60 version TEXT
61 ) `,
62 `CREATE TABLE IF NOT EXISTS posicao
63 (
64 id TEXT PRIMARY KEY,
65 usuarioAppId TEXT,
66 dataHora TEXT,
67 latitude DOUBLE,
68 longitude DOUBLE,
69 precisao DOUBLE,
70 version TEXT
71 ) `,
72 `CREATE TABLE IF NOT EXISTS objetivo_visita
73 (
74 id INTEGER PRIMARY KEY,
75 usuarioAdmId TEXT,
76 descricao TEXT,
77 dataCadastro TEXT,
78 status INTEGER,
79 version TEXT
80 ) `,
81 `CREATE TABLE IF NOT EXISTS justificativa
82 (
83 id INTEGER PRIMARY KEY,
84 usuarioAdmId INTEGER,
85 descricao TEXT,
86 dataCadastro TEXT,
87 status INTEGER,
88 version TEXT
89 ) `,
90 `CREATE TABLE IF NOT EXISTS cliente
91 (
92 id INTEGER PRIMARY KEY,
93 razaoSocial TEXT,
94 nomeFantasia TEXT,
95 cpfCnpj TEXT,
96 inscricaoEstadual TEXT,
97 telefone TEXT,
98 codigoGrupo TEXT,
99 prazo TEXT,
100 email TEXT,
101 dataAlvara TEXT,
102 vencimentoVigilancia TEXT,
103 limiteCredito DOUBLE,
104 dataCadastro TEXT,
105 empresaId INTEGER,
106 version TEXT
107 ) `,
108 ` CREATE TABLE IF NOT EXISTS titulo
109 (
110 id INTEGER PRIMARY KEY,
111 clienteId INTEGER,
112 empresaId INTEGER,
113 vendedorId INTEGER,
114 tipoTitulo TEXT,
115 numeroTitulo TEXT,
116 parcela DOUBLE,
117 dataEmissao DOUBLE,
118 dataVencimento TEXT,
119 valorTitulo DOUBLE,
120 jurosDescontos DOUBLE,
121 dataPagamentoTitulo TEXT,
122 descontoAntecipacaoDia DOUBLE,
123 codigoBanco TEXT,
124 natureza INTEGER,
125 diasInadimplente INTEGER,
126 version TEXT
127) `,
128 `CREATE TABLE IF NOT EXISTS endereco
129 (
130 id INTEGER PRIMARY KEY,
131 clienteId INTEGER,
132 logradouro TEXT,
133 bairro TEXT,
134 cidade TEXT,
135 tipoEndereco INTEGER,
136 uf INTEGER,
137 cep INTEGER,
138 version TEXT
139 ) `,
140 `CREATE TABLE IF NOT EXISTS agenda
141 (
142 id TEXT PRIMARY KEY,
143 clienteId INTEGER,
144 justificativaId INTEGER,
145 objetivoVisitaId INTEGER,
146 usuarioAdmId INTEGER,
147 usuarioAppId INTEGER,
148 status INTEGER,
149 dataAgenda TEXT,
150 dataHoraInicio TEXT,
151 dataHoraTermino TEXT,
152 vendedorPresente INTEGER,
153 sequenciaVisita INTEGER,
154 obsNaoVisita TEXT,
155 dataCadastro TEXT,
156 visitaNoLocal INTEGER,
157 duracaoMilis INTEGER,
158 latitude DOUBLE,
159 longitude DOUBLE,
160 distanciaCliente DOUBLE,
161 enumStatusAgendamento TEXT,
162 version TEXT ,
163 dirty INTEGER,
164 deleted INTEGER
165 ) `,
166 `CREATE TABLE IF NOT EXISTS empresa
167 (
168 id INTEGER PRIMARY KEY,
169 version TEXT,
170 nomeEmpresa TEXT
171 )`,
172 `CREATE TABLE IF NOT EXISTS supervisor
173 (
174 id INTEGER PRIMARY KEY,
175 version TEXT,
176 nome TEXT
177 )`,
178 `CREATE TABLE IF NOT EXISTS vendedor
179 (
180 id INTEGER PRIMARY KEY,
181 supervisorId INTEGER,
182 nomeVendedor TEXT,
183 email TEXT,
184 version TEXT
185 )`, `CREATE TABLE IF NOT EXISTS configuracoes
186 (
187 id INTEGER PRIMARY KEY,
188 sincronizacaoAutomatica INTEGER,
189 tempoSincronizacao INTEGER,
190 tipoConexao TEXT
191 )`];
192
193 private db: SQLite;
194
195 constructor(private platform: Platform) {
196 platform.ready().then(() => {
197 this.initTables();
198 });
199 }
200
201 private initTables() {
202 this.db = new SQLite();
203 this.db.openDatabase({
204 name: this.dbName,
205 location: 'default' // the location field is required
206 }).then(() => {
207 this.db.sqlBatch(this.scriptCreates).then(() => {
208 console.log('initTables ok');
209 }, (err) => {
210 console.error('Unable to execute sql on initTables(): ', err);
211 });
212 }, (err) => {
213 console.error('Unable to open database: ', err);
214 });
215
216 }
217
218 private static S4(): string {
219 return (((1 + Math.random()) * 0x10000) | 0).toString(16).substring(1);
220 }
221
222 public static UUID(): string {
223 return (CustomDBService.S4() + CustomDBService.S4() + "-" + CustomDBService.S4() + "-4" + CustomDBService.S4().substr(0, 3) + "-" + CustomDBService.S4() + "-" + CustomDBService.S4() + CustomDBService.S4() + CustomDBService.S4()).toLowerCase();
224 }
225
226
227 public queryUnic(sql: string, params?: {}): Observable<any> {
228 return Observable.create(observer => {
229 this.db.executeSql(sql, params).then((data) => {
230 if (data.rows.length > 0) {
231 observer.next(data.rows.item(0));
232 observer.complete();
233 } else {
234 observer.next(undefined);
235 observer.complete();
236 }
237 }, (error) => {
238 observer.error(error);
239 });
240 });
241 }
242
243 public queryAll(sql: string, params?: {}): Observable<any[]> {
244 return Observable.create(observer => {
245 let retorno: any[] = [];
246 this.db.executeSql(sql, params).then((data) => {
247 if (data.rows.length > 0) {
248 for (var i = 0; i < data.rows.length; i++) {
249 retorno.push(data.rows.item(i));
250 }
251 }
252 observer.next(retorno);
253 observer.complete();
254 }, (error) => {
255 observer.error(error);
256 });
257 });
258 }
259
260
261 public insert(sql: string, params?: {}, fromSync?: boolean): Observable<any> {
262 return this.executeSql(sql, params);
263 }
264
265 private convertParam(params?: {}): { [key: string]: any } {
266 let keys: string[] = [];
267 let values: string[] = [];
268 let parameters: string[] = [];
269 for (let k in params) {
270 parameters.push('?');
271 keys.push(k);
272 values.push(params[k]);
273 }
274 return {keys: keys, values: values, parameters: parameters};
275 }
276
277 public customInsertOrUpdate(sql: string, params: {}) {
278 let filters: { [key: string]: any } = this.convertParam(params);
279 sql = `${sql}(${filters['keys'].join(',')} ) VALUES (`;
280 sql = `${sql}${filters['parameters'].join(',')} )`;
281 return this.executeSql(sql, filters['values']);
282 }
283
284
285 public update(sql: string, params?: {}): Observable<any> {
286 return this.executeSql(sql, params);
287 }
288
289 public delete(sql: string, params?: {}): Observable<any> {
290 return this.executeSql(sql, params);
291 }
292
293 private executeSql(sql: string, params?: {}): Observable<any> {
294 return Observable.create(observer => {
295 this.db.executeSql(sql, params).then((data) => {
296 observer.next({});
297 observer.complete();
298 }, (error) => {
299 observer.error(error);
300 });
301 });
302 }
303
304 public removeAllItens(tableName: string): Observable<any> {
305 return this.delete(`delete from ${tableName}`);
306 }
307
308 public removeAllTables() {
309 return Observable.concat(
310 this.removeAllItens('jwt_token_info'),
311 this.removeAllItens('abastecimento'),
312 this.removeAllItens('veiculo'),
313 this.removeAllItens('marcacao_avulsa'),
314 this.removeAllItens('posicao'),
315 this.removeAllItens('objetivo_visita'),
316 this.removeAllItens('justificativa'),
317 this.removeAllItens('cliente'),
318 this.removeAllItens('titulo'),
319 this.removeAllItens('endereco'),
320 this.removeAllItens('agenda'),
321 this.removeAllItens('empresa'),
322 this.removeAllItens('supervisor'),
323 this.removeAllItens('vendedor'),
324 this.removeAllItens('configuracoes'));
325 }
326
327}