/[H6]/MySQLLayer.cpp
ViewVC logotype

Contents of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20