/[H6]/MySQLLayer.cpp
ViewVC logotype

Contents of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log

Revision 54 - (show annotations) (download)
Tue Sep 12 09:09:30 2006 UTC (17 years, 7 months ago) by torben
File size: 12532 byte(s) 
In the Search() function in the databaselayers we shouldn't waste time and space on finding
the previous checkouts and the current reservations - this should be done in getEquipment().

ResultDialog and ResultDetailsDialog now communicates throuh a CString barcode, insted of an index into the search result.

On an administrative action the ResultDetailsDialog now refreshes the data instead of closing the dialog.
1 /*
2 * Primary developers: Hedin & Kevin
3 */
4
5 #include "StdAfx.h"
6 #include ".\MySQLLayer.h"
7
8
9 MySQLLayer::MySQLLayer(ConfigFile &config)
10 {
11 CString dsn;
12 dsn.Format("ODBC;Description=asd;DRIVER=MySQL ODBC 3.51 Driver;SERVER=%s;user=%s;password=%s", config.host, config.username, config.password);
13 db.OpenEx(dsn, CDatabase::noOdbcDialog);
14
15 CString sql;
16 sql.Format("USE %s", config.database);
17 db.ExecuteSQL( sql );
18 }
19
20 MySQLLayer::~MySQLLayer(void)
21 {
22 db.Close();
23 }
24
25 vector<Person> MySQLLayer::GetPersonAll(void)
26 {
27 vector<Person> buffer;
28 CString id, inits, name, isadmin, pass;
29
30 CRecordset rs(&db);
31 rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
32 while(!rs.IsEOF())
33 {
34 rs.GetFieldValue((short)0,id);
35 rs.GetFieldValue(1,inits);
36 rs.GetFieldValue(2,name);
37 rs.GetFieldValue(3,isadmin);
38 rs.GetFieldValue(4,pass);
39
40 Person p;
41 p.id = id;
42 p.inits = inits;
43 p.name = name;
44 p.isadmin = (isadmin == 1);
45 p.pass = pass;
46
47 buffer.push_back(p);
48
49 rs.MoveNext();
50 }
51 rs.Close();
52 return buffer;
53 }
54
55 Person MySQLLayer::GetPerson(CString wantInits)
56 {
57
58 CString id, inits, name, isadmin, pass;
59 CString SQL;
60
61 Person p;
62
63 wantInits.Replace("'","\"");
64
65 SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";
66 CRecordset rs(&db);
67 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
68
69 if(!rs.IsEOF() )
70 {
71 rs.GetFieldValue((short)0,id);
72 rs.GetFieldValue(1,inits);
73 rs.GetFieldValue(2,name);
74 rs.GetFieldValue(3,isadmin);
75 rs.GetFieldValue(4,pass);
76
77 p.id = id;
78 p.inits = inits;
79 p.name = name;
80 p.isadmin = (isadmin == "1");
81 p.pass = pass;
82 }
83
84 return p;
85 }
86
87 bool MySQLLayer::AddPerson(Person NewPerson)
88 {
89 CString SQL;
90 NewPerson.inits.Replace("'","\"");
91 NewPerson.name.Replace("'","\"");
92 NewPerson.pass.Replace("'","\"");
93 SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",
94 NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
95
96 db.ExecuteSQL(SQL);
97 return true;
98 }
99
100 bool MySQLLayer::UpdatePerson(Person ChangePerson)
101 {
102 CString SQL;
103 ChangePerson.inits.Replace("'","\"");
104 ChangePerson.name.Replace("'","\"");
105 ChangePerson.pass.Replace("'","\"");
106 ChangePerson.id.Replace("'","\"");
107 SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'",
108 ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);
109
110 db.ExecuteSQL(SQL);
111 return true;
112 }
113
114 bool MySQLLayer::DeletePerson(Person RemovePerson)
115 {
116 CString SQL;
117 RemovePerson.id.Replace("'","\"");
118 SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
119 db.ExecuteSQL(SQL);
120 return true;
121 }
122
123 bool MySQLLayer::ReturnEquipment(CString barcode)
124 {
125 CString SQL;
126 barcode.Replace("'","\"");
127 SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
128 db.ExecuteSQL(SQL);
129
130 SQL.Format("UPDATE Udlån SET Afleveringsdato = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
131 db.ExecuteSQL(SQL);
132
133 checkReservations(barcode);
134 return true;
135 }
136
137 bool MySQLLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
138 {
139 CString SQL;
140 CheckEquip.barcode.Replace("'","\"");
141 CheckPersID.id.Replace("'","\"");
142 SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
143 db.ExecuteSQL(SQL);
144 SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', now(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
145 db.ExecuteSQL(SQL);
146 return true;
147 }
148
149 bool MySQLLayer::EquipmentReservation(CString barcode, Person CheckPerson)
150 {
151 CString SQL;
152 CheckPerson.id.Replace("'","\"");
153 barcode.Replace("'","\"");
154 SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', now())", CheckPerson.id, barcode);
155 db.ExecuteSQL(SQL);
156 checkReservations(barcode);
157 return true;
158 }
159
160 bool MySQLLayer::UpdateEquipment(Equipment CheckEquip)
161 {
162 CString SQL;
163 CheckEquip.name.Replace("'","\"");
164 CheckEquip.description.Replace("'","\"");
165 CheckEquip.placement.Replace("'","\"");
166 CheckEquip.status.Replace("'","\"");
167 CheckEquip.barcode.Replace("'","\"");
168 SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
169 db.ExecuteSQL(SQL);
170 return true;
171 }
172
173 bool MySQLLayer::AddEquipment(Equipment AddEquip)
174 {
175 CString SQL;
176 AddEquip.barcode.Replace("'","\"");
177 AddEquip.name.Replace("'","\"");
178 AddEquip.description.Replace("'","\"");
179 AddEquip.placement.Replace("'","\"");
180 SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");
181 db.ExecuteSQL(SQL);
182 return true;
183 }
184
185 vector<Equipment> MySQLLayer::GetEquipmentAll(void)
186 {
187 vector<Equipment> buffer;
188 CString barcode, name, description, placement, status;
189
190 CRecordset rs(&db);
191 rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
192 while(!rs.IsEOF())
193 {
194 rs.GetFieldValue((short)0,barcode);
195 rs.GetFieldValue(1,name);
196 rs.GetFieldValue(2,description);
197 rs.GetFieldValue(3,placement);
198 rs.GetFieldValue(4,status);
199
200 Equipment p;
201 p.barcode = barcode;
202 p.name = name;
203 p.description = description;
204 p.placement = placement;
205 p.status = status;
206 buffer.push_back(p);
207
208 rs.MoveNext();
209 }
210 rs.Close();
211 return buffer;
212 }
213
214 Equipment MySQLLayer::GetEquipment(CString wantBarcode)
215 {
216
217 CString barcode,name,description,placement,status;
218
219 CString SQL;
220
221 wantBarcode.Replace("'","\"");
222
223 Equipment e;
224
225 SQL.Format((CString)"SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr " +
226 "INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
227 CRecordset rs(&db);
228 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
229 if (!rs.IsEOF() )
230 {
231 rs.GetFieldValue((short)0,barcode);
232 rs.GetFieldValue(1,name);
233 rs.GetFieldValue(2,description);
234 rs.GetFieldValue(3,placement);
235 rs.GetFieldValue(4,status);
236
237 e.barcode = barcode;
238 e.name = name;
239 e.description = description;
240 e.placement = placement;
241 e.status = status;
242
243 rs.Close();
244
245 //fill Equipment::checkouts
246
247 SQL = "SELECT Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\
248 FROM Udlån\
249 INNER JOIN Person ON Udlån.PersonID = Person.PersonID ";
250 SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC LIMIT 10";
251 CRecordset rs(&db);
252 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
253 if (rs.GetRecordCount()>0) {
254 rs.MoveFirst();
255 while ( !rs.IsEOF() ) {
256 Checkout check;
257 rs.GetFieldValue((short)0, check.startdate);
258 rs.GetFieldValue(1, check.enddate);
259 rs.GetFieldValue(2, check.numdays);
260 rs.GetFieldValue(3, check.inits);
261
262 e.checkouts.push_back(check);
263 rs.MoveNext();
264 }
265 }
266 rs.Close();
267
268 // fill Equipment::reservations
269 SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
270 FROM Resevation\
271 INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
272 SQL += "WHERE Resevation.Stregkode = " + barcode + " ORDER BY STARTDATO ASC";
273
274 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
275 if (rs.GetRecordCount()>0) {
276 rs.MoveFirst();
277 while ( !rs.IsEOF() ) {
278 Reservation res;
279 rs.GetFieldValue((short)0, res.reservationID);
280 rs.GetFieldValue(1, res.startdate);
281 rs.GetFieldValue(2, res.inits);
282
283 e.reservations.push_back(res);
284 rs.MoveNext();
285 }
286 }
287 rs.Close();
288 }
289 return e;
290 }
291
292 bool MySQLLayer::DeleteEquipment(Equipment DelEquip)
293 {
294 CString SQL;
295 DelEquip.barcode.Replace("'","\"");
296 SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
297 db.ExecuteSQL(SQL);
298 return true;
299 }
300
301
302 // Developed by Torben H. Nielsen
303 vector<Equipment> MySQLLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
304 {
305 vector<Equipment> buffer;
306
307 barcode.Replace("'","\"");
308 name.Replace("'","\"");
309 inits.Replace("'","\"");
310
311 //here are the wanted equipments found through 3 seperate queries - I could also
312 //find them all in one query where I joined the 3 selects via a UNION
313 CString SQL;
314 if (available == true) {
315 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
316 FROM Udstyr \
317 INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
318 if (barcode != "")
319 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
320 if (name != "")
321 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
322
323 CRecordset rs(&db);
324 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
325 FillEquipmentVector(buffer,rs);
326 rs.Close();
327 }
328
329 if (reserved == true) {
330 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
331 FROM Udstyr \r\n\
332 INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
333 INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
334 INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
335 WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
336 if (barcode != "")
337 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
338 if (name != "")
339 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
340 //if (inits != "")
341 // SQL += " AND Person.Initialer = '" + inits + "' ";
342
343 CRecordset rs(&db);
344 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
345 FillEquipmentVector(buffer,rs);
346 rs.Close();
347 }
348
349
350 if (deposited == true) {
351 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
352 FROM Udstyr \
353 INNER JOIN Status ON Udstyr.status = Status.StatusID \
354 INNER JOIN Udlån ON Udstyr.Stregkode = Udlån.stregkode \
355 INNER JOIN Person ON Udlån.PersonID = Person.PersonID \
356 WHERE Udstyr.Status = 1 \
357 AND Udlån.Afleveringsdato IS NULL";
358 if (barcode != "")
359 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
360 if (name != "")
361 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
362 if (inits != "")
363 SQL += " AND Person.Initialer = '" + inits + "' ";
364
365 CRecordset rs(&db);
366 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
367 FillEquipmentVector(buffer,rs);
368 rs.Close();
369 }
370
371 return buffer;
372 }
373
374 // Developed by Torben H. Nielsen
375 void MySQLLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
376 {
377 CString barcode,name,description,placement,status;
378 if (rs.GetRecordCount()>0) {
379 rs.MoveFirst();
380 while (!rs.IsEOF() ) {
381 Equipment eq;
382 rs.GetFieldValue((short)0, eq.barcode);
383 rs.GetFieldValue(1, eq.name);
384 rs.GetFieldValue(2, eq.description);
385 rs.GetFieldValue(3, eq.placement);
386 rs.GetFieldValue(4, eq.status);
387
388 buffer.push_back(eq);
389
390 rs.MoveNext();
391 }
392 }
393 }
394 //Developed by Torben H. Nielsen
395 void MySQLLayer::checkReservations(CString barcode)
396 {
397 CString scount,status;
398 barcode.Replace("'","\"");
399 CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
400 CRecordset rs(&db);
401 rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
402 rs.MoveFirst();
403 rs.GetFieldValue((short)0,scount);
404 int reservationCount = atoi(scount);
405 rs.Close();
406
407 SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
408 rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
409 if (rs.GetRecordCount() >0) {
410 rs.MoveFirst();
411 rs.GetFieldValue((short)0,status);
412 }
413 rs.Close();
414
415 if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
416 SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
417 db.ExecuteSQL(SQL);
418 }
419 }
420
421 //Developed by Torben H. Nielsen
422 bool MySQLLayer::DeleteReservation(CString barcode, CString resid)
423 {
424 barcode.Replace("'","\"");
425 resid.Replace("'","\"");
426 CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
427 db.ExecuteSQL(SQL);
428 checkReservations(barcode);
429 return true;
430 }
431
  ViewVC Help
Powered by ViewVC 1.1.20