viewvc/H6/DatabaseLayer.cpp

#include "StdAfx.h"
#include ".\databaselayer.h"


DatabaseLayer::DatabaseLayer(void)
{
        db.OpenEx("ODBC; Description=; DRIVER=SQL Server; SERVER=TK-Teacher; UID=GroupTwo; PWD=toog30");
        db.ExecuteSQL("USE GroupTwo");
}

DatabaseLayer::~DatabaseLayer(void)
{
        db.Close();
}

vector<Person> DatabaseLayer::GetPersonAll(void)
{
        vector<Person> buffer;
        CString id, inits, name, isadmin, pass;

                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
                while(!rs.IsEOF())
                {
                        rs.GetFieldValue((short)0,id);
                        rs.GetFieldValue(1,inits);
                        rs.GetFieldValue(2,name);
                        rs.GetFieldValue(3,isadmin);
                        rs.GetFieldValue(4,pass);
                        
                        Person p;
                        p.id            = id;
                        p.inits         = inits;
                        p.name          = name;
                        p.isadmin       = (isadmin == 1);
                        p.pass          = pass;

                        buffer.push_back(p);

                        rs.MoveNext();
                }
                rs.Close();
        return buffer;
}

Person DatabaseLayer::GetPerson(CString wantInits)
{

        CString id, inits, name, isadmin, pass;
        CString SQL;

        Person p;
        //FIX SQL INJECTION
        SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);

        if(!rs.IsEOF() )
        {
                        rs.GetFieldValue((short)0,id);
                        rs.GetFieldValue(1,inits);
                        rs.GetFieldValue(2,name);
                        rs.GetFieldValue(3,isadmin);
                        rs.GetFieldValue(4,pass);
                        
                        p.id            = id;
                        p.inits         = inits;
                        p.name          = name;
                        p.isadmin       = (isadmin == "1");
                        p.pass          = pass;
        }
        return p;
}

bool DatabaseLayer::AddPerson(Person NewPerson)
{
        CString SQL;
        SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::UpdatePerson(Person ChangePerson)
{
        CString SQL;
        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);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::DeletePerson(Person RemovePerson)
{
        CString SQL;
        SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::ReturnEquipment(CString barcode)
{
        CString SQL;
        SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
        db.ExecuteSQL(SQL);
        
        SQL.Format("UPDATE Udl�n SET Afleveringsdato = Getdate() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
{
        CString SQL;
        SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
        db.ExecuteSQL(SQL);
        SQL.Format("INSERT INTO Udl�n (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::EquipmentReservation(Person CheckPersID, Equipment CheckEquip, int Numdays)
{
        CString SQL;
        SQL.Format("UPDATE Udstyr SET Status = '2' WHERE Stregkode = '%s' ", CheckEquip.barcode);
        db.ExecuteSQL(SQL);
        SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)
{
        CString SQL;
        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);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::AddEquipment(Equipment AddEquip)
{
        CString SQL;
        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");
        db.ExecuteSQL(SQL);
        return true;
}

vector<Equipment> DatabaseLayer::GetEquipmentAll(void)
{
        vector<Equipment> buffer;
        CString barcode, name, description, placement, status;

        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
        while(!rs.IsEOF())
        {
                rs.GetFieldValue((short)0,barcode);
                rs.GetFieldValue(1,name);
                rs.GetFieldValue(2,description);
                rs.GetFieldValue(3,placement);
                rs.GetFieldValue(4,status);
                
                Equipment p;
                p.barcode               = barcode;
                p.name                  = name;
                p.description   = description;
                p.placement             = placement;
                p.status                = status;
                buffer.push_back(p);

                rs.MoveNext();
        }
        rs.Close();
        return buffer;
}

Equipment DatabaseLayer::GetEquipment(CString wantBarcode)
{

        CString barcode,name,description,placement,status;

        CString SQL;

        Equipment e;

        SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
        if (!rs.IsEOF() )
        {
                rs.GetFieldValue((short)0,barcode);
                rs.GetFieldValue(1,name);
                rs.GetFieldValue(2,description);
                rs.GetFieldValue(3,placement);
                rs.GetFieldValue(4,status);

                e.barcode = barcode;
                e.name = name;
                e.description = description;
                e.placement = placement;
                e.status = status;
        }
        rs.Close();
        return e;
}

bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)
{
        CString SQL;
        SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
        db.ExecuteSQL(SQL);
        return true;
}


// Developed by Torben H. Nielsen
vector<Equipment> DatabaseLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
{
        vector<Equipment> buffer;

        CString SQL;
        if (available == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
                                   FROM Udstyr \
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }
        
        if (reserved == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
                                   FROM Udstyr \r\n\
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
                                   INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
                                   INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
                                   WHERE Udstyr.Status = 2 ";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                if (inits != "")
                        SQL += " AND Person.Initialer = '" + inits + "' ";
                
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }


        if (deposited == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
                                   FROM Udstyr \r\n\
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
                                   INNER JOIN Udl�n ON Udstyr.Stregkode = Udl�n.stregkode \r\n\
                                   INNER JOIN Person ON Udl�n.PersonID = person.PersonID\r\n\
                                   WHERE Udstyr.Status = 1 \r\n\
                                   AND Udl�n.Afleveringsdato IS NULL";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                if (inits != "")
                        SQL += " AND Person.Initialer = '" + inits + "' ";

                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }

        for (int i=0; i<buffer.size(); i++) { 
                //fill Equipment::checkouts
                
                SQL = "SELECT Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
                                FROM Udl�n\
                                INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
                SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                if (rs.GetRecordCount()>0) {
                        rs.MoveFirst();
                        while ( !rs.IsEOF() ) {
                                Checkout check;
                                rs.GetFieldValue((short)0, check.startdate);
                                rs.GetFieldValue(1, check.enddate);
                                rs.GetFieldValue(2, check.numdays);
                                rs.GetFieldValue(3, check.inits);

                                buffer[i].checkouts.push_back(check);
                                rs.MoveNext();
                        }
                }
                rs.Close();
                                
                // fill Equipment::reservations
                SQL = "SELECT Resevation.StartDato, Resevation.Antaldage, Person.Initialer\
                                FROM Resevation\
                                INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
                SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
                
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                if (rs.GetRecordCount()>0) {
                        rs.MoveFirst();
                        while ( !rs.IsEOF() ) {
                                Reservation res;
                                rs.GetFieldValue((short)0, res.startdate);
                                rs.GetFieldValue(1, res.numdays);
                                rs.GetFieldValue(2, res.inits);

                                buffer[i].reservations.push_back(res);
                                rs.MoveNext();
                        }
                }
                rs.Close();
        }

        return buffer;
}

// Developed by Torben H. Nielsen
void DatabaseLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
{
        CString barcode,name,description,placement,status;
        if (rs.GetRecordCount()>0) {
                rs.MoveFirst();
                while (!rs.IsEOF() ) {
                        Equipment eq;
                        rs.GetFieldValue((short)0, eq.barcode);
                        rs.GetFieldValue(1, eq.name);
                        rs.GetFieldValue(2, eq.description);
                        rs.GetFieldValue(3, eq.placement);
                        rs.GetFieldValue(4, eq.status);

                        buffer.push_back(eq);

                        rs.MoveNext();
                }
        }
}
1	#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	db.Close();
14	}
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	rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
23	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	p.isadmin = (isadmin == "1");
70	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
99	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	}