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
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
110	checkReservations(barcode);
111	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	bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson)
125	{
126	CString SQL;
127	SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
128	db.ExecuteSQL(SQL);
129	checkReservations(barcode);
130	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	//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	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	WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
247	if (barcode != "")
248	SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
249	if (name != "")
250	SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
251	//if (inits != "")
252	// SQL += " AND Person.Initialer = '" + inits + "' ";
253
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	// 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	for (int i=0; i<buffer.size(); i++) {
286	//fill Equipment::checkouts
287
288	SQL = "SELECT TOP 10 Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
289	FROM Udl�n\
290	INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
291	SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO DESC";
292	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	SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
311	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	rs.GetFieldValue((short)0, res.reservationID);
321	rs.GetFieldValue(1, res.startdate);
322	rs.GetFieldValue(2, 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	}
354
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