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;
        
        wantInits.Replace("'","\"");

        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;
        NewPerson.inits.Replace("'","\"");
        NewPerson.name.Replace("'","\"");
        NewPerson.pass.Replace("'","\"");
        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;
        ChangePerson.inits.Replace("'","\"");
        ChangePerson.name.Replace("'","\"");
        ChangePerson.pass.Replace("'","\"");
        ChangePerson.id.Replace("'","\"");
        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;
        RemovePerson.id.Replace("'","\"");
        SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
        db.ExecuteSQL(SQL);
        return true;
}

bool DatabaseLayer::ReturnEquipment(CString barcode)
{
        CString SQL;
        barcode.Replace("'","\"");
        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;
        CheckEquip.barcode.Replace("'","\"");
        CheckPersID.id.Replace("'","\"");
        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;
        CheckPerson.id.Replace("'","\"");
        barcode.Replace("'","\"");
        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;
        CheckEquip.name.Replace("'","\"");
        CheckEquip.description.Replace("'","\"");
        CheckEquip.placement.Replace("'","\"");
        CheckEquip.status.Replace("'","\"");
        CheckEquip.barcode.Replace("'","\"");
        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;
        AddEquip.barcode.Replace("'","\"");
        AddEquip.name.Replace("'","\"");
        AddEquip.description.Replace("'","\"");
        AddEquip.placement.Replace("'","\"");
        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;

        wantBarcode.Replace("'","\"");

        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;
        DelEquip.barcode.Replace("'","\"");
        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;

        barcode.Replace("'","\"");
        name.Replace("'","\"");
        inits.Replace("'","\"");

        //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;
        barcode.Replace("'","\"");
        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)
{
        barcode.Replace("'","\"");
        resid.Replace("'","\"");
        CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        return true;
}

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