/[H6]/MicrosoftDBLayer.cpp
ViewVC logotype

Annotation of /MicrosoftDBLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20