/[H6]/MicrosoftDBLayer.cpp
ViewVC logotype

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

  ViewVC Help
Powered by ViewVC 1.1.20