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);

        checkReservations(barcode);
        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(CString barcode, Person CheckPerson)
{
        CString SQL;
        SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        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,  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.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();
                }
        }
}

void DatabaseLayer::checkReservations(CString barcode)
{
        CString scount,status;
        CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode  + "'";
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
        rs.MoveFirst();
        rs.GetFieldValue((short)0,scount);
        int reservationCount = atoi(scount);
        rs.Close();

        SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
        rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
        if (rs.GetRecordCount() >0) {
                rs.MoveFirst();
                rs.GetFieldValue((short)0,status);
        }
        rs.Close();


        if (reservationCount > 0 && status == 3) {//equipment is available and has at lease one reservation
                SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
                db.ExecuteSQL(SQL);
        }
}

bool DatabaseLayer::DeleteReservation(CString barcode, CString resid)
{
        CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        return true;
}

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	torben	27
110			checkReservations(barcode);
111	torben	17	return true;
112			}
113
114			bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
115			{
116			CString SQL;
117			SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
118			db.ExecuteSQL(SQL);
119			SQL.Format("INSERT INTO Udl�n (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
120			db.ExecuteSQL(SQL);
121			return true;
122			}
123
124	torben	27	bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson)
125	torben	17	{
126			CString SQL;
127	torben	27	SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
128	torben	17	db.ExecuteSQL(SQL);
129	torben	27	checkReservations(barcode);
130	torben	17	return true;
131			}
132
133			bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)
134			{
135			CString SQL;
136			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);
137			db.ExecuteSQL(SQL);
138			return true;
139			}
140
141			bool DatabaseLayer::AddEquipment(Equipment AddEquip)
142			{
143			CString SQL;
144			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");
145			db.ExecuteSQL(SQL);
146			return true;
147			}
148
149			vector<Equipment> DatabaseLayer::GetEquipmentAll(void)
150			{
151			vector<Equipment> buffer;
152			CString barcode, name, description, placement, status;
153
154			CRecordset rs(&db);
155			rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
156			while(!rs.IsEOF())
157			{
158			rs.GetFieldValue((short)0,barcode);
159			rs.GetFieldValue(1,name);
160			rs.GetFieldValue(2,description);
161			rs.GetFieldValue(3,placement);
162			rs.GetFieldValue(4,status);
163
164			Equipment p;
165			p.barcode = barcode;
166			p.name = name;
167			p.description = description;
168			p.placement = placement;
169			p.status = status;
170			buffer.push_back(p);
171
172			rs.MoveNext();
173			}
174			rs.Close();
175			return buffer;
176			}
177
178			Equipment DatabaseLayer::GetEquipment(CString wantBarcode)
179			{
180
181			CString barcode,name,description,placement,status;
182
183			CString SQL;
184
185			Equipment e;
186
187			SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
188			CRecordset rs(&db);
189			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
190			if (!rs.IsEOF() )
191			{
192			rs.GetFieldValue((short)0,barcode);
193			rs.GetFieldValue(1,name);
194			rs.GetFieldValue(2,description);
195			rs.GetFieldValue(3,placement);
196			rs.GetFieldValue(4,status);
197
198			e.barcode = barcode;
199			e.name = name;
200			e.description = description;
201			e.placement = placement;
202			e.status = status;
203			}
204			rs.Close();
205			return e;
206			}
207
208			bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)
209			{
210			CString SQL;
211			SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
212			db.ExecuteSQL(SQL);
213			return true;
214			}
215
216
217			// Developed by Torben H. Nielsen
218			vector<Equipment> DatabaseLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
219			{
220			vector<Equipment> buffer;
221
222	torben	26	//here are the wanted equipments found through 3 seperate queries - I could also
223			//find them all in one query where I joined the 3 selects via a UNION
224	torben	17	CString SQL;
225			if (available == true) {
226			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
227			FROM Udstyr \
228			INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
229			if (barcode != "")
230			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
231			if (name != "")
232			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
233
234			CRecordset rs(&db);
235			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
236			FillEquipmentVector(buffer,rs);
237			rs.Close();
238			}
239
240			if (reserved == true) {
241			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
242			FROM Udstyr \r\n\
243			INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
244			INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
245			INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
246	torben	26	WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
247	torben	17	if (barcode != "")
248			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
249			if (name != "")
250			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
251	torben	26	//if (inits != "")
252			// SQL += " AND Person.Initialer = '" + inits + "' ";
253	torben	17
254			CRecordset rs(&db);
255			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
256			FillEquipmentVector(buffer,rs);
257			rs.Close();
258			}
259
260
261			if (deposited == true) {
262			SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
263			FROM Udstyr \r\n\
264			INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
265			INNER JOIN Udl�n ON Udstyr.Stregkode = Udl�n.stregkode \r\n\
266			INNER JOIN Person ON Udl�n.PersonID = person.PersonID\r\n\
267			WHERE Udstyr.Status = 1 \r\n\
268			AND Udl�n.Afleveringsdato IS NULL";
269			if (barcode != "")
270			SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
271			if (name != "")
272			SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
273			if (inits != "")
274			SQL += " AND Person.Initialer = '" + inits + "' ";
275
276			CRecordset rs(&db);
277			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
278			FillEquipmentVector(buffer,rs);
279			rs.Close();
280			}
281
282	torben	26	// now I have the equipment rows the user asked for, now I need some more checkout and
283			// reservation data for each piece of equipment
284
285	torben	17	for (int i=0; i<buffer.size(); i++) {
286			//fill Equipment::checkouts
287
288	torben	19	SQL = "SELECT TOP 10 Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
289	torben	17	FROM Udl�n\
290			INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
291	torben	19	SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO DESC";
292	torben	17	CRecordset rs(&db);
293			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
294			if (rs.GetRecordCount()>0) {
295			rs.MoveFirst();
296			while ( !rs.IsEOF() ) {
297			Checkout check;
298			rs.GetFieldValue((short)0, check.startdate);
299			rs.GetFieldValue(1, check.enddate);
300			rs.GetFieldValue(2, check.numdays);
301			rs.GetFieldValue(3, check.inits);
302
303			buffer[i].checkouts.push_back(check);
304			rs.MoveNext();
305			}
306			}
307			rs.Close();
308
309			// fill Equipment::reservations
310	torben	27	SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
311	torben	17	FROM Resevation\
312			INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
313			SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
314
315			rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
316			if (rs.GetRecordCount()>0) {
317			rs.MoveFirst();
318			while ( !rs.IsEOF() ) {
319			Reservation res;
320	torben	25	rs.GetFieldValue((short)0, res.reservationID);
321			rs.GetFieldValue(1, res.startdate);
322	torben	27	rs.GetFieldValue(2, 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			}
354	torben	27
355			void DatabaseLayer::checkReservations(CString barcode)
356			{
357			CString scount,status;
358			CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
359			CRecordset rs(&db);
360			rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
361			rs.MoveFirst();
362			rs.GetFieldValue((short)0,scount);
363			int reservationCount = atoi(scount);
364			rs.Close();
365
366			SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
367			rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
368			if (rs.GetRecordCount() >0) {
369			rs.MoveFirst();
370			rs.GetFieldValue((short)0,status);
371			}
372			rs.Close();
373
374
375			if (reservationCount > 0 && status == 3) {//equipment is available and has at lease one reservation
376			SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
377			db.ExecuteSQL(SQL);
378			}
379			}
380
381			bool DatabaseLayer::DeleteReservation(CString barcode, CString resid)
382			{
383			CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
384			db.ExecuteSQL(SQL);
385			checkReservations(barcode);
386			return true;
387			}
388