viewvc/H6/MySQLLayer.cpp

/*
 * Primary developers: Hedin & Kevin
 */

#include "StdAfx.h"
#include ".\MySQLLayer.h"


MySQLLayer::MySQLLayer(ConfigFile &config)
{
        CString dsn;
        dsn.Format("ODBC;Description=asd;DRIVER=MySQL ODBC 3.51 Driver;SERVER=%s;user=%s;password=%s", config.host, config.username, config.password);
        db.OpenEx(dsn, CDatabase::noOdbcDialog);

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

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

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

bool MySQLLayer::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 = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
        db.ExecuteSQL(SQL);

        checkReservations(barcode);
        return true;
}

bool MySQLLayer::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', now(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
        db.ExecuteSQL(SQL);
        return true;
}

bool MySQLLayer::EquipmentReservation(CString barcode, Person CheckPerson)
{
        CString SQL;
        CheckPerson.id.Replace("'","\"");
        barcode.Replace("'","\"");
        SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', now())", CheckPerson.id, barcode);
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        return true;
}

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

        CString barcode,name,description,placement,status;

        CString SQL;

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

        Equipment e;

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