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
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", CDatabase::noOdbcDialog);
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	//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	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	WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
246	if (barcode != "")
247	SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
248	if (name != "")
249	SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
250	//if (inits != "")
251	// SQL += " AND Person.Initialer = '" + inits + "' ";
252
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	// 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	for (int i=0; i<buffer.size(); i++) {
285	//fill Equipment::checkouts
286
287	SQL = "SELECT TOP 10 Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
288	FROM Udl�n\
289	INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
290	SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO DESC";
291	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	SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Resevation.Antaldage, Person.Initialer\
310	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	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
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	}