/[H6]/DatabaseLayer.cpp
ViewVC logotype

Contents of /DatabaseLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20