viewvc/H6/DatabaseLayer.cpp

/*
 * Primary developers: Hedin & Kevin
 */

#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();
                }
        }
}
//Developed by Torben H. Nielsen
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);
        }
}

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

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