/[H6]/PostgresLayer.cpp
ViewVC logotype

Contents of /PostgresLayer.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: 12556 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 ".\PostgresLayer.h"
7
8
9 PostgresLayer::PostgresLayer(ConfigFile &config)
10 {
11 CString dsn;
12 dsn.Format("ODBC;Description=asd;DRIVER=PostgreSQL ANSI;SERVER=%s; uid=%s;password=%s;database=%s",config.host, config.username, config.password, config.database);
13 db.OpenEx(dsn, CDatabase::noOdbcDialog);
14 }
15
16 PostgresLayer::~PostgresLayer(void)
17 {
18 db.Close();
19 }
20
21 vector<Person> PostgresLayer::GetPersonAll(void)
22 {
23 vector<Person> buffer;
24 CString id, inits, name, isadmin, pass;
25
26 CRecordset rs(&db);
27 rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
28 while(!rs.IsEOF())
29 {
30 rs.GetFieldValue((short)0,id);
31 rs.GetFieldValue(1,inits);
32 rs.GetFieldValue(2,name);
33 rs.GetFieldValue(3,isadmin);
34 rs.GetFieldValue(4,pass);
35
36 Person p;
37 p.id = id;
38 p.inits = inits;
39 p.name = name;
40 p.isadmin = (isadmin == 1);
41 p.pass = pass;
42
43 buffer.push_back(p);
44
45 rs.MoveNext();
46 }
47 rs.Close();
48 return buffer;
49 }
50
51 Person PostgresLayer::GetPerson(CString wantInits)
52 {
53
54 CString id, inits, name, isadmin, pass;
55 CString SQL;
56
57 Person p;
58
59 wantInits.Replace("'","\"");
60
61 SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer ILIKE '" + wantInits + "')";
62 CRecordset rs(&db);
63 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
64
65 if(!rs.IsEOF() )
66 {
67 rs.GetFieldValue((short)0,id);
68 rs.GetFieldValue(1,inits);
69 rs.GetFieldValue(2,name);
70 rs.GetFieldValue(3,isadmin);
71 rs.GetFieldValue(4,pass);
72
73 p.id = id;
74 p.inits = inits;
75 p.name = name;
76 p.isadmin = (isadmin == "1");
77 p.pass = pass;
78 }
79
80 return p;
81 }
82
83 bool PostgresLayer::AddPerson(Person NewPerson)
84 {
85 CString SQL;
86 NewPerson.inits.Replace("'","\"");
87 NewPerson.name.Replace("'","\"");
88 NewPerson.pass.Replace("'","\"");
89 SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",
90 NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
91
92 db.ExecuteSQL(SQL);
93 return true;
94 }
95
96 bool PostgresLayer::UpdatePerson(Person ChangePerson)
97 {
98 CString SQL;
99 ChangePerson.inits.Replace("'","\"");
100 ChangePerson.name.Replace("'","\"");
101 ChangePerson.pass.Replace("'","\"");
102 ChangePerson.id.Replace("'","\"");
103 SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'",
104 ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);
105
106 db.ExecuteSQL(SQL);
107 return true;
108 }
109
110 bool PostgresLayer::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 PostgresLayer::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 = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
127 db.ExecuteSQL(SQL);
128
129 checkReservations(barcode);
130 return true;
131 }
132
133 bool PostgresLayer::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', now(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
141 db.ExecuteSQL(SQL);
142 return true;
143 }
144
145 bool PostgresLayer::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', now())", CheckPerson.id, barcode);
151 db.ExecuteSQL(SQL);
152 checkReservations(barcode);
153 return true;
154 }
155
156 bool PostgresLayer::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 PostgresLayer::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> PostgresLayer::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 PostgresLayer::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((CString)"SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr " +
222 "INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
223 CRecordset rs(&db);
224 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
225 if (!rs.IsEOF() )
226 {
227 rs.GetFieldValue((short)0,barcode);
228 rs.GetFieldValue(1,name);
229 rs.GetFieldValue(2,description);
230 rs.GetFieldValue(3,placement);
231 rs.GetFieldValue(4,status);
232
233 e.barcode = barcode;
234 e.name = name;
235 e.description = description;
236 e.placement = placement;
237 e.status = status;
238
239 rs.Close();
240
241 //fill Equipment::checkouts
242
243 SQL = "SELECT Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\
244 FROM Udlån\
245 INNER JOIN Person ON Udlån.PersonID = Person.PersonID ";
246 SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC LIMIT 10";
247 CRecordset rs(&db);
248 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
249 if (rs.GetRecordCount()>0) {
250 rs.MoveFirst();
251 while ( !rs.IsEOF() ) {
252 Checkout check;
253 rs.GetFieldValue((short)0, check.startdate);
254 rs.GetFieldValue(1, check.enddate);
255 rs.GetFieldValue(2, check.numdays);
256 rs.GetFieldValue(3, check.inits);
257
258 e.checkouts.push_back(check);
259 rs.MoveNext();
260 }
261 }
262 rs.Close();
263
264 // fill Equipment::reservations
265 SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
266 FROM Resevation\
267 INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
268 SQL += "WHERE Resevation.Stregkode = " + barcode + " ORDER BY STARTDATO ASC";
269
270 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
271 if (rs.GetRecordCount()>0) {
272 rs.MoveFirst();
273 while ( !rs.IsEOF() ) {
274 Reservation res;
275 rs.GetFieldValue((short)0, res.reservationID);
276 rs.GetFieldValue(1, res.startdate);
277 rs.GetFieldValue(2, res.inits);
278
279 e.reservations.push_back(res);
280 rs.MoveNext();
281 }
282 }
283 rs.Close();
284
285
286 }
287 return e;
288 }
289
290 bool PostgresLayer::DeleteEquipment(Equipment DelEquip)
291 {
292 CString SQL;
293 DelEquip.barcode.Replace("'","\"");
294 SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
295 db.ExecuteSQL(SQL);
296 return true;
297 }
298
299
300 // Developed by Torben H. Nielsen
301 vector<Equipment> PostgresLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
302 {
303 vector<Equipment> buffer;
304
305 barcode.Replace("'","\"");
306 name.Replace("'","\"");
307 inits.Replace("'","\"");
308
309 //here are the wanted equipments found through 3 seperate queries - I could also
310 //find them all in one query where I joined the 3 selects via a UNION
311 CString SQL;
312 if (available == true) {
313 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
314 FROM Udstyr \
315 INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
316 if (barcode != "")
317 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
318 if (name != "")
319 SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
320
321 CRecordset rs(&db);
322 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
323 FillEquipmentVector(buffer,rs);
324 rs.Close();
325 }
326
327 if (reserved == true) {
328 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
329 FROM Udstyr \r\n\
330 INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
331 INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
332 INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
333 WHERE ( Udstyr.Status = 2 OR Person.Initialer ILIKE '" + inits + "') ";
334 if (barcode != "")
335 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
336 if (name != "")
337 SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
338 //if (inits != "")
339 // SQL += " AND Person.Initialer = '" + inits + "' ";
340
341 CRecordset rs(&db);
342 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
343 FillEquipmentVector(buffer,rs);
344 rs.Close();
345 }
346
347
348 if (deposited == true) {
349 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
350 FROM Udstyr \
351 INNER JOIN Status ON Udstyr.status = Status.StatusID \
352 INNER JOIN Udlån ON Udstyr.Stregkode = Udlån.stregkode \
353 INNER JOIN Person ON Udlån.PersonID = Person.PersonID \
354 WHERE Udstyr.Status = 1 \
355 AND Udlån.Afleveringsdato IS NULL";
356 if (barcode != "")
357 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
358 if (name != "")
359 SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
360 if (inits != "")
361 SQL += " AND Person.Initialer ILIKE '" + inits + "' ";
362
363 CRecordset rs(&db);
364 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
365 FillEquipmentVector(buffer,rs);
366 rs.Close();
367 }
368
369 return buffer;
370 }
371
372 // Developed by Torben H. Nielsen
373 void PostgresLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
374 {
375 CString barcode,name,description,placement,status;
376 if (rs.GetRecordCount()>0) {
377 rs.MoveFirst();
378 while (!rs.IsEOF() ) {
379 Equipment eq;
380 rs.GetFieldValue((short)0, eq.barcode);
381 rs.GetFieldValue(1, eq.name);
382 rs.GetFieldValue(2, eq.description);
383 rs.GetFieldValue(3, eq.placement);
384 rs.GetFieldValue(4, eq.status);
385
386 buffer.push_back(eq);
387
388 rs.MoveNext();
389 }
390 }
391 }
392 //Developed by Torben H. Nielsen
393 void PostgresLayer::checkReservations(CString barcode)
394 {
395 CString scount,status;
396 barcode.Replace("'","\"");
397 CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
398 CRecordset rs(&db);
399 rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
400 rs.MoveFirst();
401 rs.GetFieldValue((short)0,scount);
402 int reservationCount = atoi(scount);
403 rs.Close();
404
405 SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
406 rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
407 if (rs.GetRecordCount() >0) {
408 rs.MoveFirst();
409 rs.GetFieldValue((short)0,status);
410 }
411 rs.Close();
412
413 if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
414 SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
415 db.ExecuteSQL(SQL);
416 }
417 }
418
419 //Developed by Torben H. Nielsen
420 bool PostgresLayer::DeleteReservation(CString barcode, CString resid)
421 {
422 barcode.Replace("'","\"");
423 resid.Replace("'","\"");
424 CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
425 db.ExecuteSQL(SQL);
426 checkReservations(barcode);
427 return true;
428 }
429
  ViewVC Help
Powered by ViewVC 1.1.20