/[H6]/MySQLLayer.cpp
ViewVC logotype

Annotation of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20