· 9 years ago · Nov 28, 2016, 04:46 PM
1package com.example.marcel.dusicky;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.util.Log;
9
10import java.sql.Date;
11import java.text.DateFormat;
12import java.text.ParseException;
13import java.text.SimpleDateFormat;
14import java.util.ArrayList;
15import java.util.List;
16import java.util.Locale;
17
18/**
19 * Created by Marcel on 27.11.2016.
20 */
21public class DBDusicky extends SQLiteOpenHelper {
22
23
24
25
26
27 private static final String DATABASE_NAME = "Dusicky";
28
29
30 //nazvy tabuliek
31 private static final String TABLE_ZAKAZNICI = "zakaznici";
32 private static final String TABLE_PRACLISTY = "pracListy";
33
34
35 //nazvy stlpcov zakaznik
36 private static final String ZAKAZNICI_id = "id";
37 private static final String ZAKAZNICI_meno = "meno";
38 private static final String ZAKAZNICI_priezvisko = "priezvisko";
39 private static final String ZAKAZNICI_telcislo = "telcislo";
40 private static final String ZAKAZNICI_email = "email";
41 private static final String ZAKAZNICI_mesto = "mesto";
42 private static final String ZAKAZNICI_ulica = "ulica";
43 private static final String ZAKAZNICI_zariadenie = "zariadenie";
44
45
46
47 //nazvy stlpcov pracovny list
48
49 private static final String PRACLISTY_id = "id";
50 private static final String PRACLISTY_idZakaznika = "id_zakaznika";
51 private static final String PRACLISTY_menoZakaznika = "meno_zakaznika";
52 private static final String PRACLISTY_praca = "praca";
53 private static final String PRACLISTY_cena = "cena";
54 private static final String PRACLISTY_datum = "datum";
55
56 //prikaz na vytvorenie tabulky zakaznik
57 private static final String CREATE_TABLE_ZAKAZNIK = "CREATE TABLE " + TABLE_ZAKAZNICI + "(" + ZAKAZNICI_id + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + ZAKAZNICI_meno +" TEXT," + ZAKAZNICI_priezvisko + " TEXT,"+ ZAKAZNICI_telcislo + " TEXT,"+ ZAKAZNICI_email + " TEXT,"+ ZAKAZNICI_mesto + " TEXT,"+ ZAKAZNICI_ulica + " TEXT,"+ ZAKAZNICI_zariadenie + " TEXT" +")";
58
59 //prikaz na vytvorenie tabulky pracovny list
60 private static final String CREATE_TABLE_PRACLIST = "CREATE TABLE " + TABLE_PRACLISTY + "(" + PRACLISTY_id + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + PRACLISTY_idZakaznika +" INTEGER," + PRACLISTY_menoZakaznika + " TEXT,"+ PRACLISTY_praca + " TEXT,"+ PRACLISTY_cena + " REAL,"+ PRACLISTY_datum + " DATETIME," +")";
61
62
63 public DBDusicky(Context context) {
64 super(context, DATABASE_NAME, null, 1);
65 }
66
67
68 @Override
69 public void onCreate(SQLiteDatabase db) {
70//vytvorenie tabuliek
71 db.execSQL(CREATE_TABLE_ZAKAZNIK);
72 db.execSQL(CREATE_TABLE_PRACLIST);
73 }
74
75 @Override
76 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
77 // on upgrade drop older tables
78 db.execSQL("DROP TABLE IF EXISTS " + TABLE_ZAKAZNICI);
79 db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRACLISTY);
80
81 // create new tables
82 onCreate(db);
83
84 }
85
86//metoda na pridanie zakaznika do databazy
87
88 public boolean createZakaznik(Zakaznik zakaznik) {
89 SQLiteDatabase db = this.getWritableDatabase();
90
91 ContentValues values = new ContentValues();
92 values.put(ZAKAZNICI_meno, zakaznik.getMeno());
93 values.put(ZAKAZNICI_priezvisko, zakaznik.getPriezvisko());
94 values.put(ZAKAZNICI_telcislo, zakaznik.getTelCislo());
95 values.put(ZAKAZNICI_email, zakaznik.getEmail());
96 values.put(ZAKAZNICI_mesto, zakaznik.getMesto());
97 values.put(ZAKAZNICI_ulica, zakaznik.getUlica());
98 values.put(ZAKAZNICI_zariadenie, zakaznik.getZariadenie());
99
100 // insert row
101 db.insert(TABLE_ZAKAZNICI,null,values);
102 return true;
103 }
104
105 public boolean createPracList(PracovnyList pracovnyList) {
106 SQLiteDatabase db = this.getWritableDatabase();
107
108 ContentValues values = new ContentValues();
109 values.put(PRACLISTY_idZakaznika, pracovnyList.getIdZakaznika());
110 values.put(PRACLISTY_menoZakaznika, pracovnyList.getMenoZakaznika());
111 values.put(PRACLISTY_praca, pracovnyList.getPraca());
112 values.put(PRACLISTY_cena, pracovnyList.getCena());
113 values.put(PRACLISTY_datum, pracovnyList.getDatum().toString());
114
115 // insert row
116 db.insert(TABLE_PRACLISTY, null, values);
117
118
119
120 return true;
121 }
122
123 /* public Zakaznik getZakaznik(long zakaznik_id) {
124 SQLiteDatabase db = this.getReadableDatabase();
125
126 String selectQuery = "SELECT * FROM " + TABLE_ZAKAZNICI + " WHERE "
127 + ZAKAZNICI_id + " = " + zakaznik_id;
128
129 Log.e(LOG, selectQuery);
130
131 Cursor c = db.rawQuery(selectQuery, null);
132
133 if (c != null)
134 c.moveToFirst();
135
136 Zakaznik zakaznik = new Zakaznik();
137 zakaznik.setId(c.getInt((c.getColumnIndex(ZAKAZNICI_id))));
138 zakaznik.setMeno((c.getString(c.getColumnIndex(ZAKAZNICI_meno))));
139 zakaznik.setPriezvisko((c.getString(c.getColumnIndex(ZAKAZNICI_priezvisko))));
140 zakaznik.setTelCislo((c.getString(c.getColumnIndex(ZAKAZNICI_telcislo))));
141 zakaznik.setEmail((c.getString(c.getColumnIndex(ZAKAZNICI_email))));
142 zakaznik.setMesto((c.getString(c.getColumnIndex(ZAKAZNICI_mesto))));
143 zakaznik.setUlica((c.getString(c.getColumnIndex(ZAKAZNICI_ulica))));
144 zakaznik.setZariadenie((c.getString(c.getColumnIndex(ZAKAZNICI_zariadenie))));
145 return zakaznik;
146 }
147*/
148
149
150
151
152 /*
153 * getting all zakaznikov
154 * */
155 public List<Zakaznik> getAllZakaznikov() {
156
157
158 List<Zakaznik> zakaznici = new ArrayList<>();
159 String selectQuery = "SELECT * FROM " + TABLE_ZAKAZNICI;
160 SQLiteDatabase db = this.getReadableDatabase();
161
162
163 Cursor c = db.rawQuery(selectQuery, null);
164
165 c.moveToFirst();
166 // looping through all rows and adding to list
167 while (!c.isAfterLast()) {
168
169 Zakaznik zakaznik = new Zakaznik();
170 zakaznik.setId(c.getInt((c.getColumnIndex(ZAKAZNICI_id))));
171 zakaznik.setMeno((c.getString(c.getColumnIndex(ZAKAZNICI_meno))));
172 zakaznik.setPriezvisko((c.getString(c.getColumnIndex(ZAKAZNICI_priezvisko))));
173 zakaznik.setTelCislo((c.getString(c.getColumnIndex(ZAKAZNICI_telcislo))));
174 zakaznik.setEmail((c.getString(c.getColumnIndex(ZAKAZNICI_email))));
175 zakaznik.setMesto((c.getString(c.getColumnIndex(ZAKAZNICI_mesto))));
176 zakaznik.setUlica((c.getString(c.getColumnIndex(ZAKAZNICI_ulica))));
177 zakaznik.setZariadenie((c.getString(c.getColumnIndex(ZAKAZNICI_zariadenie))));
178
179
180 // adding to todo list
181 zakaznici.add(zakaznik);
182 c.moveToNext();
183 }
184
185 c.close();
186 return zakaznici;
187 }
188
189
190 public List<PracovnyList> getAllPracListov() throws ParseException {
191 List<PracovnyList> pracListy = new ArrayList<PracovnyList>();
192 String selectQuery = "SELECT * FROM " + TABLE_PRACLISTY;
193
194
195
196 SQLiteDatabase db = this.getReadableDatabase();
197 Cursor c = db.rawQuery(selectQuery, null);
198
199 // looping through all rows and adding to list
200 if (c.moveToFirst()) {
201 do {
202
203 PracovnyList pracList = new PracovnyList();
204 pracList.setId(c.getInt((c.getColumnIndex(PRACLISTY_id))));
205 pracList.setIdZakaznika(c.getInt((c.getColumnIndex(PRACLISTY_idZakaznika))));
206 pracList.setMenoZakaznika(c.getString((c.getColumnIndex(PRACLISTY_menoZakaznika))));
207 pracList.setPraca(c.getString((c.getColumnIndex(PRACLISTY_praca))));
208 pracList.setCena(c.getDouble((c.getColumnIndex(PRACLISTY_cena))));
209 String datumString = c.getString(c.getColumnIndex(PRACLISTY_datum));
210 DateFormat format = new SimpleDateFormat("dd.MM.yy", Locale.ENGLISH);
211 Date datum = (Date) format.parse(datumString);
212 pracList.setDatum(datum);
213
214
215 // adding to todo list
216 pracListy.add(pracList);
217 } while (c.moveToNext());
218 }
219
220 return pracListy;
221 }
222
223
224 public void closeDB() {
225 SQLiteDatabase db = this.getReadableDatabase();
226 if (db != null && db.isOpen())
227 db.close();
228 }
229
230
231
232}