/[H6]/MicrosoftDBLayer.cpp
ViewVC logotype

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