· 9 years ago · Dec 03, 2016, 10:12 PM
1using System;
2using System.Collections.Generic;
3using System.Data.SQLite;
4using System.Linq;
5using System.Text;
6using System.Threading.Tasks;
7
8namespace Praćenje_mernih_instrumenata
9{
10 class BazaPodataka
11 {
12 private string lozinkaBazePodataka = "draganemoj";
13 private string nazivBazePodataka = "bazapodataka.pp";
14
15 public BazaPodataka()
16 {
17
18 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
19 {
20 connection.Open();
21
22 string sql = "CREATE TABLE IF NOT EXISTS NALOZI (" +
23 "ID INTEGER PRIMARY KEY NOT NULL," +
24 "KORISNICKO_IME TEXT," +
25 "LOZINKA TEXT," +
26 "TIP INTEGER)";
27
28 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
29 {
30 command.ExecuteNonQuery();
31 }
32
33 /*sql = "CREATE TABLE IF NOT EXISTS PRISTUPI (" +
34 "ID INTEGER PRIMARY KEY NOT NULL," +
35 "KORISNICKO_IME TEXT," +
36 "SERIJSKI_BROJ_VAGE TEXT)";
37
38 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
39 {
40 command.ExecuteNonQuery();
41 }
42 */
43 sql = "SELECT COUNT(*) FROM NALOZI";
44
45 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
46 {
47 if (Convert.ToInt32(command.ExecuteScalar()) == 0)
48 {
49 sql = "INSERT INTO NALOZI (KORISNICKO_IME, LOZINKA, TIP) VALUES ('Admin', 'admin1', 1);";
50
51 using (SQLiteCommand command1 = new SQLiteCommand(sql, connection))
52 {
53 command1.ExecuteNonQuery();
54 }
55 }
56 }
57 /*
58 sql = "CREATE TABLE IF NOT EXISTS ADMINISTRACIJA (" +
59 "ID INTEGER PRIMARY KEY NOT NULL," +
60 "TEKSTOVI TEXT," +
61 "BROJ_UNETIH_VAGA INTEGER," +
62 "BROJ_DOZVOLJENIH_VAGA INTEGER," +
63 "DATUM_ISTEKA_LICENCE DATE)";
64
65 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
66 {
67 command.ExecuteNonQuery();
68 }
69
70 sql = "SELECT * FROM ADMINISTRACIJA";
71
72 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
73 {
74 using (SQLiteDataReader reader = command.ExecuteReader())
75 {
76 if (reader.Read() == false)
77 {
78 string sql1 = "INSERT INTO ADMINISTRACIJA (TEKSTOVI, BROJ_UNETIH_VAGA, BROJ_DOZVOLJENIH_VAGA, DATUM_ISTEKA_LICENCE) VALUES ('', 0, 0, NULL)";
79
80 using (SQLiteCommand command1 = new SQLiteCommand(sql1, connection))
81 {
82 command1.ExecuteNonQuery();
83 }
84 }
85 }
86 }*/
87
88 sql = "CREATE TABLE IF NOT EXISTS VAGE (" +
89 "ID INTEGER PRIMARY KEY NOT NULL," +
90 "PROIZVODJAC TEXT," +
91 "TIP TEXT," +
92 "SERIJSKI_BROJ TEXT," +
93 "INVENTORNI_BROJ TEXT," +
94 "GODINA_PROIZVODNJE INTEGER," +
95 "LOKACIJA TEXT," +
96 "ORGANIZACIONA_JEDINICA TEXT," +
97 "OPSEG_MERENJA REAL," +
98 "PODELJAK REAL," +
99 "SLIKA TEXT)";
100
101 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
102 {
103 command.ExecuteNonQuery();
104 }
105 }
106 }
107
108 public Nalog UcitajNalog(string korisnickoIme)
109 {
110 try
111 {
112 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
113 {
114 connection.Open();
115
116 string sql = "SELECT * FROM NALOZI WHERE KORISNICKO_IME = '" + korisnickoIme + "'";
117
118 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
119 {
120 using (SQLiteDataReader reader = command.ExecuteReader())
121 {
122 if (reader.Read())
123 {
124 Nalog nalog = new Nalog();
125 nalog.Ucitaj(Convert.ToString(reader["KORISNICKO_IME"]), Convert.ToString(reader["LOZINKA"]), Convert.ToBoolean(reader["TIP"]));
126
127 return nalog;
128 }
129 }
130 }
131 }
132
133 return null;
134 }
135 catch (Exception)
136 {
137 throw;
138 }
139 }
140
141 public List<Nalog> UcitajNaloge()
142 {
143 try
144 {
145 List<Nalog> nalozi = new List<Nalog>();
146
147 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
148 {
149 connection.Open();
150
151 string sql = "SELECT * FROM NALOZI";
152
153 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
154 {
155 using (SQLiteDataReader reader = command.ExecuteReader())
156 {
157 while (reader.Read())
158 {
159 Nalog nalog = new Nalog();
160 nalog.Ucitaj(Convert.ToString(reader["KORISNICKO_IME"]), Convert.ToString(reader["LOZINKA"]), Convert.ToBoolean(reader["TIP"]));
161
162 nalozi.Add(nalog);
163 }
164 }
165 }
166 }
167
168 return nalozi;
169 }
170 catch (Exception)
171 {
172 throw;
173 }
174 }
175
176 public bool DodajNalog(Nalog nalog)
177 {
178 try
179 {
180 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
181 {
182 connection.Open();
183
184 string sql = "SELECT COUNT(*) FROM NALOZI WHERE KORISNICKO_IME = '" + nalog.KorisnickoIme + "'";
185
186 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
187 {
188 if (Convert.ToInt32(command.ExecuteScalar()) != 0)
189 {
190 return false;
191 }
192
193 else
194 {
195 sql = "INSERT INTO NALOZI (KORISNICKO_IME, LOZINKA, TIP) VALUES ('" + nalog.KorisnickoIme + "', '" + nalog.Lozinka + "', " + (nalog.Tip == true ? 1 : 0) + ")";
196
197 using (SQLiteCommand command1 = new SQLiteCommand(sql, connection))
198 {
199 command1.ExecuteNonQuery();
200 }
201
202 return true;
203 }
204 }
205 }
206 }
207 catch (Exception)
208 {
209 throw;
210 }
211 }
212
213 public void ObrisiNalog(string korisnickoIme)
214 {
215 try
216 {
217 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
218 {
219 connection.Open();
220
221 string sql = "DELETE FROM NALOZI WHERE KORISNICKO_IME = '" + korisnickoIme + "'";
222
223 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
224 {
225 command.ExecuteNonQuery();
226 }
227 }
228 }
229 catch (Exception)
230 {
231 throw;
232 }
233 }
234
235 public void PromeniLozinkuNaloga(string korisnickoIme, string lozinka)
236 {
237 try
238 {
239 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
240 {
241 connection.Open();
242
243 string sql = "UPDATE NALOZI SET LOZINKA = '" + lozinka + "' WHERE KORISNICKO_IME = '" + korisnickoIme + "'";
244
245 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
246 {
247 command.ExecuteNonQuery();
248 }
249 }
250 }
251 catch (Exception)
252 {
253 throw;
254 }
255 }
256
257 public bool DodajVagu(Vaga vaga)
258 {
259 try
260 {
261 using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + nazivBazePodataka + ";Password=" + lozinkaBazePodataka + ";"))
262 {
263 connection.Open();
264
265 string sql = "SELECT COUNT(*) FROM VAGE WHERE SERIJSKI_BROJ = '" + vaga.SerijskiBroj + "'";
266
267 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
268 {
269 if(Convert.ToInt32(command.ExecuteScalar()) > 0)
270 return false;
271 }
272
273 sql = "INSERT INTO VAGE (PROIZVODJAC, TIP, SERIJSKI_BROJ, INVENTORNI_BROJ, GODINA_PROIZVODNJE, LOKACIJA, ORGANIZACIONA_JEDINICA, OPSEG_MERENJA, PODELJAK, SLIKA)" +
274 "VALUES ('" + vaga.Proizvodjac + "', '" + vaga.Tip + "', '" + vaga.SerijskiBroj + "', '" + vaga.InventorniBroj + "', " + vaga.GodinaProizvodnje + ", '" + vaga.Lokacija + "', '" + vaga.OrganizacionaJedinica + "', " + vaga.Opseg + ", " + vaga.Podeljak + ", '" + vaga.Slika + "')";
275
276 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
277 {
278 command.ExecuteNonQuery();
279 }
280 }
281
282 return true;
283 }
284 catch (Exception)
285 {
286 throw;
287 }
288 }
289 }
290}