/[H6]/DatabaseLayer.cpp
ViewVC logotype

Contents of /DatabaseLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


Revision 60 - (show 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
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