/[H6]/PostgresLayer.cpp
ViewVC logotype

Annotation of /PostgresLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log

Revision 54 - (hide 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 torben 43 /*
2     * Primary developers: Hedin & Kevin
3     */
4    
5     #include "StdAfx.h"
6     #include ".\PostgresLayer.h"
7    
8    
9 torben 47 PostgresLayer::PostgresLayer(ConfigFile &config)
10 torben 43 {
11 torben 47 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 torben 43 }
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 torben 54
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 torben 43 }
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