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", CDatabase::noOdbcDialog);
        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;

        //here are the wanted equipments found through 3 seperate queries - I could also 
        //find them all in one query where I joined the 3 selects via a UNION
        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 OR Person.Initialer = '" + inits + "') ";
                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();
        }

        // now I have the equipment rows the user asked for, now I need some more checkout and 
        // reservation data for each piece of equipment

        for (int i=0; i<buffer.size(); i++) { 
                //fill Equipment::checkouts
                
                SQL = "SELECT TOP 10 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 DESC";
                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.ResevationID, 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.reservationID);
                                rs.GetFieldValue(1, res.startdate);
                                rs.GetFieldValue(2, res.numdays);
                                rs.GetFieldValue(3, 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	torben	19
2
3	torben	8	#include "StdAfx.h"
4			#include ".\databaselayer.h"
5
6
7			DatabaseLayer::DatabaseLayer(void)
8			{
9	torben	23	db.OpenEx("ODBC; Description=; DRIVER=SQL Server; SERVER=TK-Teacher; UID=GroupTwo; PWD=toog30", CDatabase::noOdbcDialog);
10	torben	8	db.ExecuteSQL("USE GroupTwo");
11			}
12
13			DatabaseLayer::~DatabaseLayer(void)
14			{
15	torben	17	db.Close();
16	torben	8	}
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	torben	17	rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
25	torben	8	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	torben	9	p.isadmin = (isadmin == "1");
72	torben	8	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	torben	9
101	torben	17	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	torben	26	//here are the wanted equipments found through 3 seperate queries - I could also
222			//find them all in one query where I joined the 3 selects via a UNION
223	torben	17	CString SQL;
224			if (available == true) {
225			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
226			FROM Udstyr \
227			INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
228			if (barcode != "")
229			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
230			if (name != "")
231			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
232
233			CRecordset rs(&db);
234			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
235			FillEquipmentVector(buffer,rs);
236			rs.Close();
237			}
238
239			if (reserved == true) {
240			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
241			FROM Udstyr \r\n\
242			INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
243			INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
244			INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
245	torben	26	WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
246	torben	17	if (barcode != "")
247			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
248			if (name != "")
249			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
250	torben	26	//if (inits != "")
251			// SQL += " AND Person.Initialer = '" + inits + "' ";
252	torben	17
253			CRecordset rs(&db);
254			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
255			FillEquipmentVector(buffer,rs);
256			rs.Close();
257			}
258
259
260			if (deposited == true) {
261			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
262			FROM Udstyr \r\n\
263			INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
264			INNER JOIN Udl�n ON Udstyr.Stregkode = Udl�n.stregkode \r\n\
265			INNER JOIN Person ON Udl�n.PersonID = person.PersonID\r\n\
266			WHERE Udstyr.Status = 1 \r\n\
267			AND Udl�n.Afleveringsdato IS NULL";
268			if (barcode != "")
269			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
270			if (name != "")
271			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
272			if (inits != "")
273			SQL += " AND Person.Initialer = '" + inits + "' ";
274
275			CRecordset rs(&db);
276			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
277			FillEquipmentVector(buffer,rs);
278			rs.Close();
279			}
280
281	torben	26	// now I have the equipment rows the user asked for, now I need some more checkout and
282			// reservation data for each piece of equipment
283
284	torben	17	for (int i=0; i<buffer.size(); i++) {
285			//fill Equipment::checkouts
286
287	torben	19	SQL = "SELECT TOP 10 Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
288	torben	17	FROM Udl�n\
289			INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
290	torben	19	SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO DESC";
291	torben	17	CRecordset rs(&db);
292			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
293			if (rs.GetRecordCount()>0) {
294			rs.MoveFirst();
295			while ( !rs.IsEOF() ) {
296			Checkout check;
297			rs.GetFieldValue((short)0, check.startdate);
298			rs.GetFieldValue(1, check.enddate);
299			rs.GetFieldValue(2, check.numdays);
300			rs.GetFieldValue(3, check.inits);
301
302			buffer[i].checkouts.push_back(check);
303			rs.MoveNext();
304			}
305			}
306			rs.Close();
307
308			// fill Equipment::reservations
309	torben	25	SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Resevation.Antaldage, Person.Initialer\
310	torben	17	FROM Resevation\
311			INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
312			SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
313
314			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
315			if (rs.GetRecordCount()>0) {
316			rs.MoveFirst();
317			while ( !rs.IsEOF() ) {
318			Reservation res;
319	torben	25	rs.GetFieldValue((short)0, res.reservationID);
320			rs.GetFieldValue(1, res.startdate);
321			rs.GetFieldValue(2, res.numdays);
322			rs.GetFieldValue(3, res.inits);
323	torben	17
324			buffer[i].reservations.push_back(res);
325			rs.MoveNext();
326			}
327			}
328			rs.Close();
329			}
330
331			return buffer;
332			}
333
334			// Developed by Torben H. Nielsen
335			void DatabaseLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
336			{
337			CString barcode,name,description,placement,status;
338			if (rs.GetRecordCount()>0) {
339			rs.MoveFirst();
340			while (!rs.IsEOF() ) {
341			Equipment eq;
342			rs.GetFieldValue((short)0, eq.barcode);
343			rs.GetFieldValue(1, eq.name);
344			rs.GetFieldValue(2, eq.description);
345			rs.GetFieldValue(3, eq.placement);
346			rs.GetFieldValue(4, eq.status);
347
348			buffer.push_back(eq);
349
350			rs.MoveNext();
351			}
352			}
353			}