/[H6]/DatabaseLayer.cpp
ViewVC logotype

Annotation of /DatabaseLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20