/[H6]/DatabaseLayer.cpp
ViewVC logotype

Annotation of /DatabaseLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


Revision 60 - (hide annotations) (download)
Mon Oct 22 13:00:14 2007 UTC (16 years, 5 months ago) by torben
File size: 9383 byte(s)
Refactored the database layer classes. Pushed most of the functions to the parent class.
1 torben 60
2    
3     #include "StdAfx.h"
4     #include "DatabaseLayer.h"
5    
6     // Developed by Torben H. Nielsen
7     void DatabaseLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
8     {
9     CString barcode,name,description,placement,status;
10     if (rs.GetRecordCount()>0) {
11     rs.MoveFirst();
12     while (!rs.IsEOF() ) {
13     Equipment eq;
14     rs.GetFieldValue((short)0, eq.barcode);
15     rs.GetFieldValue(1, eq.name);
16     rs.GetFieldValue(2, eq.description);
17     rs.GetFieldValue(3, eq.placement);
18     rs.GetFieldValue(4, eq.status);
19    
20     buffer.push_back(eq);
21    
22     rs.MoveNext();
23     }
24     }
25     }
26    
27     //Developed by Torben H. Nielsen
28     bool DatabaseLayer::DeleteReservation(CString barcode, CString resid)
29     {
30     barcode.Replace("'","\"");
31     resid.Replace("'","\"");
32     CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
33     db.ExecuteSQL(SQL);
34     checkReservations(barcode);
35     return true;
36     }
37     //Developed by Torben H. Nielsen
38     void DatabaseLayer::checkReservations(CString barcode)
39     {
40     CString scount,status;
41     barcode.Replace("'","\"");
42     CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
43     CRecordset rs(&db);
44     rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
45     rs.MoveFirst();
46     rs.GetFieldValue((short)0,scount);
47     int reservationCount = atoi(scount);
48     rs.Close();
49    
50     SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
51     rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
52     if (rs.GetRecordCount() >0) {
53     rs.MoveFirst();
54     rs.GetFieldValue((short)0,status);
55     }
56     rs.Close();
57    
58     if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
59     SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
60     db.ExecuteSQL(SQL);
61     }
62     }
63    
64     bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)
65     {
66     CString SQL;
67     DelEquip.barcode.Replace("'","\"");
68     SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
69     db.ExecuteSQL(SQL);
70     return true;
71     }
72     vector<Equipment> DatabaseLayer::GetEquipmentAll(void)
73     {
74     vector<Equipment> buffer;
75     CString barcode, name, description, placement, status;
76    
77     CRecordset rs(&db);
78     rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
79     while(!rs.IsEOF())
80     {
81     rs.GetFieldValue((short)0,barcode);
82     rs.GetFieldValue(1,name);
83     rs.GetFieldValue(2,description);
84     rs.GetFieldValue(3,placement);
85     rs.GetFieldValue(4,status);
86    
87     Equipment p;
88     p.barcode = barcode;
89     p.name = name;
90     p.description = description;
91     p.placement = placement;
92     p.status = status;
93     buffer.push_back(p);
94    
95     rs.MoveNext();
96     }
97     rs.Close();
98     return buffer;
99     }
100    
101     bool DatabaseLayer::AddEquipment(Equipment AddEquip)
102     {
103     CString SQL;
104     AddEquip.barcode.Replace("'","\"");
105     AddEquip.name.Replace("'","\"");
106     AddEquip.description.Replace("'","\"");
107     AddEquip.placement.Replace("'","\"");
108     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");
109     db.ExecuteSQL(SQL);
110     return true;
111     }
112    
113    
114     bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)
115     {
116     CString SQL;
117     CheckEquip.name.Replace("'","\"");
118     CheckEquip.description.Replace("'","\"");
119     CheckEquip.placement.Replace("'","\"");
120     CheckEquip.status.Replace("'","\"");
121     CheckEquip.barcode.Replace("'","\"");
122     SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
123     db.ExecuteSQL(SQL);
124     return true;
125     }
126    
127     bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson)
128     {
129     CString SQL;
130     CheckPerson.id.Replace("'","\"");
131     barcode.Replace("'","\"");
132     SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
133     db.ExecuteSQL(SQL);
134     checkReservations(barcode);
135     return true;
136     }
137    
138     bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
139     {
140     CString SQL;
141     CheckEquip.barcode.Replace("'","\"");
142     CheckPersID.id.Replace("'","\"");
143     SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
144     db.ExecuteSQL(SQL);
145     SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
146     db.ExecuteSQL(SQL);
147     return true;
148     }
149    
150     bool DatabaseLayer::ReturnEquipment(CString barcode)
151     {
152     CString SQL;
153     barcode.Replace("'","\"");
154     SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
155     db.ExecuteSQL(SQL);
156    
157     SQL.Format("UPDATE Udlån SET Afleveringsdato = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
158     db.ExecuteSQL(SQL);
159    
160     checkReservations(barcode);
161     return true;
162     }
163    
164     vector<Person> DatabaseLayer::GetPersonAll(void)
165     {
166     vector<Person> buffer;
167     CString id, inits, name, isadmin, pass;
168    
169     CRecordset rs(&db);
170     rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
171     while(!rs.IsEOF())
172     {
173     rs.GetFieldValue((short)0,id);
174     rs.GetFieldValue(1,inits);
175     rs.GetFieldValue(2,name);
176     rs.GetFieldValue(3,isadmin);
177     rs.GetFieldValue(4,pass);
178    
179     Person p;
180     p.id = id;
181     p.inits = inits;
182     p.name = name;
183     p.isadmin = (isadmin == 1);
184     p.pass = pass;
185    
186     buffer.push_back(p);
187    
188     rs.MoveNext();
189     }
190     rs.Close();
191     return buffer;
192     }
193    
194     Person DatabaseLayer::GetPerson(CString wantInits)
195     {
196    
197     CString id, inits, name, isadmin, pass;
198     CString SQL;
199    
200     Person p;
201    
202     wantInits.Replace("'","\"");
203    
204     SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";
205     CRecordset rs(&db);
206     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
207    
208     if(!rs.IsEOF() )
209     {
210     rs.GetFieldValue((short)0,id);
211     rs.GetFieldValue(1,inits);
212     rs.GetFieldValue(2,name);
213     rs.GetFieldValue(3,isadmin);
214     rs.GetFieldValue(4,pass);
215    
216     p.id = id;
217     p.inits = inits;
218     p.name = name;
219     p.isadmin = (isadmin == "1");
220     p.pass = pass;
221     }
222    
223     return p;
224     }
225    
226     bool DatabaseLayer::AddPerson(Person NewPerson)
227     {
228     CString SQL;
229     NewPerson.inits.Replace("'","\"");
230     NewPerson.name.Replace("'","\"");
231     NewPerson.pass.Replace("'","\"");
232     SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",
233     NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
234    
235     db.ExecuteSQL(SQL);
236     return true;
237     }
238    
239     bool DatabaseLayer::UpdatePerson(Person ChangePerson)
240     {
241     CString SQL;
242     ChangePerson.inits.Replace("'","\"");
243     ChangePerson.name.Replace("'","\"");
244     ChangePerson.pass.Replace("'","\"");
245     ChangePerson.id.Replace("'","\"");
246     SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'",
247     ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);
248    
249     db.ExecuteSQL(SQL);
250     return true;
251     }
252    
253     bool DatabaseLayer::DeletePerson(Person RemovePerson)
254     {
255     CString SQL;
256     RemovePerson.id.Replace("'","\"");
257     SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
258     db.ExecuteSQL(SQL);
259     return true;
260     }
261    
262     Equipment DatabaseLayer::GetEquipment(CString wantBarcode)
263     {
264    
265     CString barcode,name,description,placement,status,statusid;
266    
267     CString SQL;
268    
269     wantBarcode.Replace("'","\"");
270    
271     Equipment e;
272    
273     SQL.Format((CString)"SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse, Udstyr.Status FROM Udstyr " +
274     "INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
275     CRecordset rs(&db);
276     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
277     if (!rs.IsEOF() )
278     {
279     rs.GetFieldValue((short)0,barcode);
280     rs.GetFieldValue(1,name);
281     rs.GetFieldValue(2,description);
282     rs.GetFieldValue(3,placement);
283     rs.GetFieldValue(4,status);
284     rs.GetFieldValue(5,statusid);
285    
286     e.barcode = barcode;
287     e.name = name;
288     e.description = description;
289     e.placement = placement;
290     e.status = status;
291     e.statusid = atoi( statusid );
292    
293     rs.Close();
294    
295     //fill Equipment::checkouts
296    
297     SQL = GenerateQueryForLatestCheckouts(barcode);
298    
299     CRecordset rs(&db);
300     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
301     if (rs.GetRecordCount()>0) {
302     rs.MoveFirst();
303     while ( !rs.IsEOF() ) {
304     Checkout check;
305     rs.GetFieldValue((short)0, check.startdate);
306     rs.GetFieldValue(1, check.enddate);
307     rs.GetFieldValue(2, check.numdays);
308     rs.GetFieldValue(3, check.inits);
309    
310     e.checkouts.push_back(check);
311     rs.MoveNext();
312     }
313     }
314     rs.Close();
315    
316     // fill Equipment::reservations
317     SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
318     FROM Resevation\
319     INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
320     SQL += "WHERE Resevation.Stregkode = " + barcode + " ORDER BY STARTDATO ASC";
321    
322     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
323     if (rs.GetRecordCount()>0) {
324     rs.MoveFirst();
325     while ( !rs.IsEOF() ) {
326     Reservation res;
327     rs.GetFieldValue((short)0, res.reservationID);
328     rs.GetFieldValue(1, res.startdate);
329     rs.GetFieldValue(2, res.inits);
330    
331     e.reservations.push_back(res);
332     rs.MoveNext();
333     }
334     }
335     rs.Close();
336     }
337     return e;
338     }

  ViewVC Help
Powered by ViewVC 1.1.20