viewvc/H6/MicrosoftDBLayer.cpp

/*
 * Primary developers: Hedin & Kevin
 */

#include "StdAfx.h"
#include ".\MicrosoftDBLayer.h"


MicrosoftDBLayer::MicrosoftDBLayer(ConfigFile &config)
{
        CString dsn;
        dsn.Format("ODBC; Description=; DRIVER=SQL Server; SERVER=%s; UID=%s; PWD=%s", config.host, config.username, config.password);
        db.OpenEx(dsn, CDatabase::noOdbcDialog);

        CString sql;
        sql.Format("USE %s", config.database);
        db.ExecuteSQL(sql);
}

MicrosoftDBLayer::~MicrosoftDBLayer(void)
{
        db.Close();
}

vector<Person> MicrosoftDBLayer::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 MicrosoftDBLayer::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 MicrosoftDBLayer::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 MicrosoftDBLayer::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 MicrosoftDBLayer::DeletePerson(Person RemovePerson)
{
        CString SQL;
        RemovePerson.id.Replace("'","\"");
        SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
        db.ExecuteSQL(SQL);
        return true;
}

bool MicrosoftDBLayer::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 MicrosoftDBLayer::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 MicrosoftDBLayer::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 MicrosoftDBLayer::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' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
        db.ExecuteSQL(SQL);
        return true;
}

bool MicrosoftDBLayer::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> MicrosoftDBLayer::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 MicrosoftDBLayer::GetEquipment(CString wantBarcode)
{

        CString barcode,name,description,placement,status,statusid;

        CString SQL;

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

        Equipment e;

        SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse, Udstyr.Status 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);
                rs.GetFieldValue(5,statusid);

                e.barcode = barcode;
                e.name = name;
                e.description = description;
                e.placement = placement;
                e.status = status;
                e.statusid = atoi( statusid );

                rs.Close();

                //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 = " + 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);

                                e.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 = " + 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);

                                e.reservations.push_back(res);
                                rs.MoveNext();
                        }
                }
                rs.Close();
        }

        return e;
}

bool MicrosoftDBLayer::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> MicrosoftDBLayer::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();
        }

        return buffer;
}

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