/[H6]/MySQLLayer.cpp
ViewVC logotype

Annotation of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


Revision 41 - (hide annotations) (download)
Tue Sep 5 12:50:38 2006 UTC (17 years, 7 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 torben 41 /*
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