· 9 years ago · Nov 21, 2016, 05:34 AM
1package moradi.ramin;
2
3import java.util.ArrayList;
4import java.util.List;
5import android.content.Context;
6import android.database.Cursor;
7import android.database.sqlite.SQLiteDatabase;
8import android.database.sqlite.SQLiteOpenHelper;
9
10
11public class DatabaseManager extends SQLiteOpenHelper {
12
13 public DatabaseManager(Context context)
14 {
15 super(context, "hesabdariDB", null, 1);
16 }
17
18
19 @Override
20 public void onCreate(SQLiteDatabase db) {
21 // TODO Auto-generated method stub
22 // ساخت جدول tbl_hesab
23
24 /************************************************************* ساخت جدول Øساب *****/
25 String query = "CREATE TABLE tbl_Hesab ( "
26 + " ID INTEGER PRIMARY KEY AUTOINCREMENT"
27 + " NOT NULL"
28 + " UNIQUE,"
29 + " NameHesab VARCHAR( 50 ),"
30 + " Mojodi NUMERIC "
31 + " );";
32
33 db.execSQL(query);
34
35 query = "INSERT INTO tbl_hesab (NameHesab,Mojodi) VALUES('کی٠پول',0)";
36 db.execSQL(query);
37
38 query = "INSERT INTO tbl_hesab (NameHesab,Mojodi) VALUES('بانک ملی',0)";
39 db.execSQL(query);
40
41 query = "INSERT INTO tbl_hesab (NameHesab,Mojodi) VALUES('بانک صادرات',0)";
42 db.execSQL(query);
43
44 query = "INSERT INTO tbl_hesab (NameHesab,Mojodi) VALUES('بانک ملت',0)";
45 db.execSQL(query);
46
47 /************************************************************* ساخت جدول دسته های اصلی *****/
48
49 query = "CREATE TABLE tbl_DastebandiAsli ( "
50 + " ID INTEGER PRIMARY KEY AUTOINCREMENT"
51 + " NOT NULL"
52 + " UNIQUE,"
53 + " NameDastebandi VARCHAR( 50 ),"
54 + " Kind CHAR "
55 + " );";
56 db.execSQL(query);
57
58 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('Øقوق','D')";
59 db.execSQL(query);
60
61 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('Ùروش','D')";
62 db.execSQL(query);
63
64 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('اجاره','D')";
65 db.execSQL(query);
66
67 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('يارانه','D')";
68 db.execSQL(query);
69
70 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('بدهکاران به من','D')";
71 db.execSQL(query);
72
73 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('اتومبيل','H')";
74 db.execSQL(query);
75
76 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('اجاره','H')";
77 db.execSQL(query);
78
79 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('رÙت Ùˆ آمد','H')";
80 db.execSQL(query);
81
82 query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) VALUES ('غذا','H')";
83 db.execSQL(query);
84
85 /********************************************************************** ساخت جدول دسته های Ùرعی ********/
86 query = "CREATE TABLE tbl_DastebandiFari ( "
87 + " ID INTEGER PRIMARY KEY AUTOINCREMENT"
88 + " NOT NULL"
89 + " UNIQUE,"
90 + " NameDastebandi VARCHAR( 50 ),"
91 + " ID_DastebandiAsli INTEGER REFERENCES tbl_DastebandiAsli ( ID ) "
92 + " ); ";
93 db.execSQL(query);
94
95 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('عمومي','1')";
96 db.execSQL(query);
97
98 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('عيدي','1')";
99 db.execSQL(query);
100
101 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('پاداش','1')";
102 db.execSQL(query);
103
104 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('Ù…Øصول 1','2')";
105 db.execSQL(query);
106
107 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('Ù…Øصول 2','2')";
108 db.execSQL(query);
109
110 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('Ù…Øصول 3','2')";
111 db.execSQL(query);
112
113 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('خانه','3')";
114 db.execSQL(query);
115
116 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('شرکت','3')";
117 db.execSQL(query);
118
119 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('مغازه','3')";
120 db.execSQL(query);
121
122 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('یارانه','4')";
123 db.execSQL(query);
124
125 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('بدهکاران به من','5')";
126 db.execSQL(query);
127
128 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('تعمیرگاه','6')";
129 db.execSQL(query);
130
131 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('بنزین','6')";
132 db.execSQL(query);
133
134 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('بیمه','6')";
135 db.execSQL(query);
136
137 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('جریمه','6')";
138 db.execSQL(query);
139
140 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('خانه','7')";
141 db.execSQL(query);
142
143 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('شرکت','7')";
144 db.execSQL(query);
145
146 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('مغازه','7')";
147 db.execSQL(query);
148
149 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('اتوبوس','8')";
150 db.execSQL(query);
151
152 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('مترو','8')";
153 db.execSQL(query);
154 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('تاکسی','8')";
155 db.execSQL(query);
156 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('آژانس','8')";
157 db.execSQL(query);
158 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('پارکینگ','8')";
159 db.execSQL(query);
160 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('رستوران','9')";
161 db.execSQL(query);
162 query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,ID_DastebandiAsli) VALUES ('کاÙÛŒ شاپ','9')";
163 db.execSQL(query);
164
165 /**************************************************** sakhte view_dastebandiha *************/
166 query = " CREATE VIEW view_dastebandiha AS "
167 + " SELECT tbl_dastebandiAsli.id, "
168 + " tbl_dastebandiAsli.namedastebandi,"
169 + " tbl_dastebandiAsli.kind,"
170 + " tbl_dastebandifari.Id AS IDfari,"
171 + " tbl_dastebandifari.namedastebandi AS namedastebandifari"
172 + " FROM tbl_dastebandiAsli"
173 + " INNER JOIN tbl_dastebandifari"
174 + " WHERE tbl_dastebandiasli.id = tbl_dastebandifari.id_dastebandiasli;";
175 db.execSQL(query);
176
177 /************************************************* sakhte tbl_HazineDaramad **************/
178 query = "CREATE TABLE tbl_HazineDaramad ( "
179 + " ID INTEGER PRIMARY KEY AUTOINCREMENT"
180 + " NOT NULL"
181 + " UNIQUE,"
182 + " Mablagh NUMERIC,"
183 + " IDDastebandiFari INTEGER REFERENCES tbl_DastebandiFari ( ID ) ON DELETE CASCADE,"
184 + " IDHesab INTEGER REFERENCES tbl_Hesab ( ID ) ON DELETE CASCADE,"
185 + " Tarikh VARCHAR,"
186 + " Tozih VARCHAR( 500 ) "
187 + " );";
188
189 db.execSQL(query);
190
191 /************************************************ sakhte view_hazine_daramad ***********/
192
193 query = " CREATE VIEW view_hazine_daramad AS "
194 + " SELECT * "
195 + " FROM tbl_hazinedaramad"
196 + " INNER JOIN view_dastebandiha"
197 + " WHERE tbl_hazinedaramad.iddastebandifari = view_dastebandiha.idfari;";
198 db.execSQL(query);
199
200 }
201
202
203 @Override
204 public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
205 // TODO Auto-generated method stub
206
207 db.execSQL("DROP TABLE IF EXISTS tbl_Hesab ");
208
209 db.execSQL("DROP TABLE IF EXISTS tbl_DastebandiAsli ");
210
211 db.execSQL("DROP TABLE IF EXISTS tbl_HazineDaramad ");
212
213 onCreate(db);
214 }
215
216
217 /************************************************************************* متدهای مربوط به Øساب *******/
218 public List<Hesab> getHesab()
219 {
220 List<Hesab> list_hesabha = new ArrayList<Hesab>();
221
222 String query = "SELECT ID,NameHesab,Mojodi FROM tbl_hesab";
223
224 SQLiteDatabase db = this.getReadableDatabase();
225
226 Cursor cursor = db.rawQuery(query, null);
227
228 while (cursor.moveToNext() == true)
229 {
230 Hesab h = new Hesab();
231 h.id = cursor.getInt(0);
232 h.nameHesab = cursor.getString(1);
233 h.mojodi = cursor.getDouble(2);
234
235 list_hesabha.add(h);
236 }
237
238 return list_hesabha;
239
240 }
241
242
243 public Boolean HesabJadid(String NameHesab, Double mojodi)
244 {
245 Boolean result;
246
247 String query = "INSERT INTO tbl_Hesab (NameHesab,Mojodi) " +
248 "VALUES ('" + NameHesab + "'," + mojodi + ")";
249
250 try
251 {
252 SQLiteDatabase db = this.getWritableDatabase();
253 db.execSQL(query);
254
255 db.close();
256
257 result = true;
258 }
259 catch (Exception ex)
260 {
261 result = false;
262 }
263
264 return result;
265
266 }
267
268
269 public Boolean deleteHesab(int id)
270 {
271 Boolean result;
272
273 String query = "DELETE FROM tbl_Hesab WHERE id = " + id;
274
275 try
276 {
277 SQLiteDatabase db = this.getWritableDatabase();
278 db.execSQL(query);
279 db.close();
280
281 result = true;
282 }
283 catch (Exception ex)
284 {
285 result = false;
286 }
287
288 return result;
289 }
290
291
292 public Boolean updateHesab(String NameHesab, Integer id_hesab)
293 {
294 Boolean result;
295
296 String query = "UPDATE tbl_hesab " +
297 " SET nameHesab = '" + NameHesab + "'" +
298 " WHERE id = " + id_hesab;
299
300 try
301 {
302 SQLiteDatabase db = this.getWritableDatabase();
303 db.execSQL(query);
304
305 db.close();
306 result = true;
307 }
308 catch (Exception ex)
309 {
310 result = false;
311 }
312
313 return result;
314 }
315
316
317 /************************************************************** متدهای مربوط به دسته بندی اصلی Ùˆ Ùرعی *******/
318
319 public List<Dastebandi> getDastebandiAsli(String kind)
320 {
321
322 List<Dastebandi> dastehaye_asli = new ArrayList<Dastebandi>();
323
324 String query = "SELECT id,NameDastebandi FROM tbl_DastebandiAsli WHERE kind = '" + kind + "'";
325
326 try
327 {
328 SQLiteDatabase db = this.getReadableDatabase();
329
330 Cursor cursor = db.rawQuery(query, null);
331
332 while (cursor.moveToNext() == true)
333 {
334 Dastebandi d = new Dastebandi();
335 d.id = cursor.getInt(0);
336 d.Onvan = cursor.getString(1);
337
338 dastehaye_asli.add(d);
339 }
340 }
341 catch (Exception ex)
342 {
343 String msg = ex.getMessage();
344 }
345 return dastehaye_asli;
346 }
347
348
349 public List<Dastebandi> getDastebandiFari(String id_daste_asli)
350 {
351
352 List<Dastebandi> dastehaye_fari = new ArrayList<Dastebandi>();
353
354 String query = "SELECT id,NameDastebandi FROM tbl_DastebandiFari WHERE id_dastebandiAsli = '" + id_daste_asli + "'";
355
356 try
357 {
358 SQLiteDatabase db = this.getReadableDatabase();
359
360 Cursor cursor = db.rawQuery(query, null);
361
362 while (cursor.moveToNext() == true)
363 {
364 Dastebandi d = new Dastebandi();
365 d.id = cursor.getInt(0);
366 d.Onvan = cursor.getString(1);
367
368 dastehaye_fari.add(d);
369 }
370 }
371 catch (Exception ex)
372 {
373 String msg = ex.getMessage();
374 }
375 return dastehaye_fari;
376 }
377
378
379 public boolean DasteAsliJadid(String nameDastebandi, String kind)
380 {
381 boolean result;
382
383 try
384 {
385 String query = "INSERT INTO tbl_DastebandiAsli (NameDastebandi,Kind) " +
386 "VALUES ( '" + nameDastebandi + "', '" + kind + "')";
387
388 // ایجاد ارتباط نوشتنی با دیتابیس
389 SQLiteDatabase db = this.getWritableDatabase();
390
391 db.execSQL(query);
392
393 db.close();
394 result = true;
395 }
396 catch (Exception ex)
397 {
398 result = false;
399 }
400
401 return result;
402 }
403
404
405 public boolean deleteDasteAsli(int id)
406 {
407 boolean result;
408
409 try
410 {
411 String query = "DELETE FROM tbl_dastebandiAsli WHERE id = " + id;
412
413 SQLiteDatabase db = this.getWritableDatabase();
414
415 db.execSQL(query);
416
417 db.close();
418
419 result = true;
420 }
421 catch (Exception ex)
422 {
423 result = false;
424 }
425
426 return result;
427 }
428
429
430 public boolean updateDasteAsli(String nameDastebandi, Integer id)
431
432 {
433 boolean result;
434
435 try
436 {
437 String query =
438 "UPDATE tbl_dastebandiAsli " +
439 " SET nameDastebandi='" + nameDastebandi + "'" +
440 " WHERE id = " + id;
441
442 SQLiteDatabase db = this.getWritableDatabase();
443
444 db.execSQL(query);
445
446 db.close();
447
448 result = true;
449 }
450 catch (Exception ex)
451 {
452 result = false;
453 }
454
455 return result;
456
457 }
458
459
460 public boolean DasteFariJadid(String nameDastebandi, String id_dastebandiAsli)
461 {
462 boolean result;
463
464 try
465 {
466 String query = "INSERT INTO tbl_DastebandiFari (NameDastebandi,id_dastebandiAsli) " +
467 "VALUES ( '" + nameDastebandi + "', '" + id_dastebandiAsli + "')";
468
469 // ایجاد ارتباط نوشتنی با دیتابیس
470 SQLiteDatabase db = this.getWritableDatabase();
471
472 db.execSQL(query);
473
474 db.close();
475 result = true;
476 }
477 catch (Exception ex)
478 {
479 result = false;
480 }
481
482 return result;
483 }
484
485
486 public boolean deleteDasteFari(int id)
487 {
488 boolean result;
489
490 try
491 {
492 String query = "DELETE FROM tbl_dastebandiFari WHERE id = " + id;
493 SQLiteDatabase db = this.getWritableDatabase();
494
495 db.execSQL(query);
496
497 db.close();
498
499 result = true;
500 }
501 catch (Exception ex)
502 {
503 result = false;
504 }
505
506 return result;
507 }
508
509
510 public boolean updateDasteFari(String nameDastebandi, String id)
511 {
512 boolean result;
513
514 try
515 {
516 String query =
517 "UPDATE tbl_dastebandiFari " +
518 " SET nameDastebandi='" + nameDastebandi + "'" +
519 " WHERE id = " + id;
520
521 SQLiteDatabase db = this.getWritableDatabase();
522
523 db.execSQL(query);
524
525 db.close();
526
527 result = true;
528 }
529 catch (Exception ex)
530 {
531 result = false;
532 }
533
534 return result;
535 }
536
537
538 /***************************************************************************/
539 public String NameDasteAsliFari(String id_fari)
540 {
541 String result = "";
542
543 try
544 {
545
546 String query = "SELECT nameDastebandi,nameDastebandiFari FROM view_dastebandiha WHERE IDfari = " + id_fari;
547
548 SQLiteDatabase db = this.getReadableDatabase();
549
550 Cursor cursor = db.rawQuery(query, null);
551
552 if (cursor.moveToFirst() == true)
553 {
554 result = cursor.getString(0) + " : " + cursor.getString(1);
555 }
556 }
557 catch (Exception ex)
558 {
559 String msg = ex.getMessage();
560 }
561
562 return result;
563 }
564
565
566 public Boolean DaramadHazineJadid(String mablagh, String ID_Fari, String ID_Hesab, String tarikh, String Tozih)
567 {
568 Boolean result;
569
570 String query = " INSERT INTO tbl_HazineDaramad(Mablagh,IDDastebandiFari,IDHesab,Tarikh,Tozih) "
571 + " VALUES('" + mablagh + "'," + ID_Fari + "," + ID_Hesab + ",'" + tarikh + "','" + Tozih + "')";
572
573 try
574 {
575 SQLiteDatabase db = this.getWritableDatabase();
576 db.execSQL(query);
577
578 db.close();
579 result = true;
580 }
581 catch (Exception ex)
582 {
583 result = false;
584 }
585
586 return result;
587
588 }
589
590
591 public Boolean UpdateMojodiHesab(String Mojodi, String id_hesab)
592 {
593 Boolean result;
594
595 try
596 {
597 String query = "UPDATE tbl_Hesab SET mojodi = mojodi + " + Mojodi + " WHERE id = " + id_hesab;
598 SQLiteDatabase db = this.getWritableDatabase();
599
600 db.execSQL(query);
601 db.close();
602 result = true;
603 }
604 catch (Exception ex)
605 {
606 result = false;
607 }
608
609 return result;
610 }
611
612
613 public List<HazineDaramad> getHazineDaramadbyTarikhAndKind(String azTarikh, String taTarikh, String Kind)
614 {
615 List<HazineDaramad> list_daramdHazine = new ArrayList<HazineDaramad>();
616
617 String query = "select sum(mablagh),namedastebandifari,tarikh from view_hazine_daramad where Kind='" + Kind + "' " +
618 " and tarikh between '" + azTarikh + "' and '" + taTarikh + "' group by idfari order by sum(mablagh) desc ";
619
620 SQLiteDatabase db = this.getWritableDatabase();
621
622 Cursor cursor = db.rawQuery(query, null);
623
624 while (cursor.moveToNext() == true)
625 {
626 HazineDaramad hd = new HazineDaramad();
627 hd.JameKol = cursor.getDouble(0);
628 hd.nameDastebandi = cursor.getString(1);
629
630 hd.Tarikh = cursor.getString(2);
631 hd.mablagh = Double.toString(cursor.getDouble(0));
632
633 list_daramdHazine.add(hd);
634
635 }
636
637 return list_daramdHazine;
638 }
639
640
641 public Double getBardashti(String id_hesab, String azTarikh, String taTarikh)
642 {
643 Double result = 0d;
644
645 String query = "SELECT SUM(mablagh) FROM view_hazine_daramad WHERE IDhesab = " + id_hesab +
646 " and tarikh between '" + azTarikh + "' and '" + taTarikh + "' and Kind = 'H'";
647
648 SQLiteDatabase db = this.getReadableDatabase();
649
650 Cursor cursor = db.rawQuery(query, null);
651
652 if (cursor.moveToFirst() == true)
653 {
654 result = cursor.getDouble(0);
655 }
656
657 return result;
658
659 }
660
661
662 public Double getVarizi(String id_hesab, String azTarikh, String taTarikh)
663 {
664 Double result = 0d;
665
666 String query = "SELECT SUM(mablagh) FROM view_hazine_daramad WHERE IDhesab = " + id_hesab +
667 " and tarikh between '" + azTarikh + "' and '" + taTarikh + "' and Kind = 'D'";
668
669 SQLiteDatabase db = this.getReadableDatabase();
670
671 Cursor cursor = db.rawQuery(query, null);
672
673 if (cursor.moveToFirst() == true)
674 {
675 result = cursor.getDouble(0);
676 }
677
678 return result;
679
680 }
681
682
683 public Double getMojodi(String id_hesab)
684 {
685 Double result = 0d;
686
687 String query = "SELECT mojodi FROM tbl_hesab WHERE ID = " + id_hesab;
688
689 SQLiteDatabase db = this.getReadableDatabase();
690
691 Cursor cursor = db.rawQuery(query, null);
692
693 if (cursor.moveToFirst() == true)
694 {
695 result = cursor.getDouble(0);
696 }
697
698 return result;
699
700 }
701
702}