|
/* |
|
|
* Primary developers: Hedin & Kevin |
|
|
*/ |
|
1 |
|
|
2 |
#include "StdAfx.h" |
#include "StdAfx.h" |
3 |
#include ".\MySQLLayer.h" |
#include ".\MySQLLayer.h" |
19 |
db.Close(); |
db.Close(); |
20 |
} |
} |
21 |
|
|
|
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,statusid; |
|
|
|
|
|
CString SQL; |
|
|
|
|
|
wantBarcode.Replace("'","\""); |
|
|
|
|
|
Equipment e; |
|
|
|
|
|
SQL.Format((CString)"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 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 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); |
|
|
|
|
|
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 MySQLLayer::DeleteEquipment(Equipment DelEquip) |
|
|
{ |
|
|
CString SQL; |
|
|
DelEquip.barcode.Replace("'","\""); |
|
|
SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode); |
|
|
db.ExecuteSQL(SQL); |
|
|
return true; |
|
|
} |
|
|
|
|
22 |
|
|
23 |
// Developed by Torben H. Nielsen |
// Developed by Torben H. Nielsen |
24 |
vector<Equipment> MySQLLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited) |
vector<Equipment> MySQLLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited) |
92 |
return buffer; |
return buffer; |
93 |
} |
} |
94 |
|
|
95 |
// Developed by Torben H. Nielsen |
CString MySQLLayer::GenerateQueryForLatestCheckouts( CString barcode ) |
|
void MySQLLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs) |
|
96 |
{ |
{ |
97 |
CString barcode,name,description,placement,status; |
CString SQL; |
|
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); |
|
98 |
|
|
99 |
buffer.push_back(eq); |
SQL = "SELECT Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\ |
100 |
|
FROM Udlån\ |
101 |
|
INNER JOIN Person ON Udlån.PersonID = Person.PersonID "; |
102 |
|
SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC LIMIT 10"; return SQL; |
103 |
|
|
104 |
rs.MoveNext(); |
return SQL; |
|
} |
|
|
} |
|
|
} |
|
|
//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(); |
|
105 |
|
|
|
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; |
|
106 |
} |
} |
|
|
|