--- DatabaseLayer.cpp	2006/08/31 16:02:50	16
+++ DatabaseLayer.cpp	2006/08/31 16:32:37	17
@@ -1,124 +1,7 @@
 #include "StdAfx.h"
 #include ".\databaselayer.h"
 
-DatabaseLayer::DatabaseLayer(void)
-{
-	Person p1;
-	p1.id="0";
-	p1.inits="MaPe";
-	p1.isadmin = 0;
-	p1.name="Mads Pedersen";
-	persons.push_back(p1);
-
-	Person p2;
-	p2.id="1";
-	p2.inits = "thn";
-	p2.isadmin = 1;
-	p2.name = "Torben Nielsen";
-	p2.pass = "thn";
-	persons.push_back(p2);
-
-	Person p3;
-	p3.id = "2";
-	p3.inits = "FiKa";
-	p3.isadmin = 0;
-	p3.name = "Finn Karlsen";
-	persons.push_back(p3);
-
-	Equipment e1;
-	e1.barcode = "1234567890123";
-	e1.name = "1 ks. øl";
-	e1.description = "30 Stk. Hancock Høker";
-	e1.placement = "i kælderen";
-	e1.status = "Drukket";
-	equipments.push_back(e1);
-
-}
-
-vector<Person> DatabaseLayer::GetPersonAll(void)
-{
-	return persons;
-}
-
-Person DatabaseLayer::GetPerson(CString wantInits)
-{
-	for (int i=0; i<persons.size(); i++) {
-		if (persons[i].inits == wantInits)
-			return persons[i];
-	}
-	Person p;
-	return p;
-}
 
-Equipment DatabaseLayer::GetEquipment(CString wantBarcode)
-{
-	for (int i=0; i<equipments.size(); i++) {
-		if (equipments[i].barcode == wantBarcode) {
-			return equipments[i];
-		}
-	}
-}
-
-bool DatabaseLayer::UpdatePerson(Person ChangePerson)
-{
-	for (int i=0; i<persons.size(); i++) {
-		if (persons[i].inits == ChangePerson.inits) {
-			persons[i] = ChangePerson;
-			break;
-		}
-	}
-
-	return true;
-}
-
-
-bool DatabaseLayer::UpdateEquipment(Equipment ChangeEquipment)
-{
-	for (int i=0; i<equipments.size(); i++) {
-		if (equipments[i].barcode == ChangeEquipment.barcode) {
-			equipments[i] = ChangeEquipment;
-			break;
-		}
-	}
-
-	return true;
-
-}
-
-bool DatabaseLayer::AddPerson(Person NewPerson)
-{
-	persons.push_back(NewPerson);
-	return true;
-}
-
-
-vector<Equipment> DatabaseLayer::GetEquipmentAll()
-{
-	return equipments;
-}
-
-bool DatabaseLayer::AddEquipment(Equipment e)
-{
-	equipments.push_back(e);
-	return true;
-}
-
-bool DatabaseLayer::DeletePerson(Person RemovePerson)
-{
-	//vector kan ikke slette fra midten, så de der ikke slettes kopieres til ny vector
-	//og  gl.vector overskrivets
-
-	vector<Person> newvector;
-	for (int i = 0; i != persons.size(); i++) {
-		if ( persons[i].inits != RemovePerson.inits) {
-			newvector.push_back( persons[i] );
-		}
-	}
-	persons = newvector;
-	return true;
-}
-
-/*
 DatabaseLayer::DatabaseLayer(void)
 {
 	db.OpenEx("ODBC; Description=; DRIVER=SQL Server; SERVER=TK-Teacher; UID=GroupTwo; PWD=toog30");
@@ -127,6 +10,7 @@
 
 DatabaseLayer::~DatabaseLayer(void)
 {
+	db.Close();
 }
 
 vector<Person> DatabaseLayer::GetPersonAll(void)
@@ -135,7 +19,7 @@
 	CString id, inits, name, isadmin, pass;
 
 		CRecordset rs(&db);
-		rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person");
+		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);
@@ -212,4 +96,250 @@
 	return true;
 }
 
-*/
\ No newline at end of file
+bool DatabaseLayer::ReturnEquipment(CString barcode)
+{
+	CString SQL;
+	SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
+	db.ExecuteSQL(SQL);
+	
+	SQL.Format("UPDATE Udlån SET Afleveringsdato = Getdate() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
+{
+	CString SQL;
+	SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
+	db.ExecuteSQL(SQL);
+	SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+bool DatabaseLayer::EquipmentReservation(Person CheckPersID, Equipment CheckEquip, int Numdays)
+{
+	CString SQL;
+	SQL.Format("UPDATE Udstyr SET Status = '2' WHERE Stregkode = '%s' ", CheckEquip.barcode);
+	db.ExecuteSQL(SQL);
+	SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)
+{
+	CString SQL;
+	SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s', Status = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.status, CheckEquip.barcode);
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+bool DatabaseLayer::AddEquipment(Equipment AddEquip)
+{
+	CString SQL;
+	SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+vector<Equipment> DatabaseLayer::GetEquipmentAll(void)
+{
+	vector<Equipment> buffer;
+	CString barcode, name, description, placement, status;
+
+	CRecordset rs(&db);
+	rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
+	while(!rs.IsEOF())
+	{
+		rs.GetFieldValue((short)0,barcode);
+		rs.GetFieldValue(1,name);
+		rs.GetFieldValue(2,description);
+		rs.GetFieldValue(3,placement);
+		rs.GetFieldValue(4,status);
+		
+		Equipment p;
+		p.barcode		= barcode;
+		p.name			= name;
+		p.description	= description;
+		p.placement		= placement;
+		p.status		= status;
+		buffer.push_back(p);
+
+		rs.MoveNext();
+	}
+	rs.Close();
+	return buffer;
+}
+
+Equipment DatabaseLayer::GetEquipment(CString wantBarcode)
+{
+
+	CString barcode,name,description,placement,status;
+
+	CString SQL;
+
+	Equipment e;
+
+	SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
+	CRecordset rs(&db);
+	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
+	if (!rs.IsEOF() )
+	{
+		rs.GetFieldValue((short)0,barcode);
+		rs.GetFieldValue(1,name);
+		rs.GetFieldValue(2,description);
+		rs.GetFieldValue(3,placement);
+		rs.GetFieldValue(4,status);
+
+		e.barcode = barcode;
+		e.name = name;
+		e.description = description;
+		e.placement = placement;
+		e.status = status;
+	}
+	rs.Close();
+	return e;
+}
+
+bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)
+{
+	CString SQL;
+	SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
+	db.ExecuteSQL(SQL);
+	return true;
+}
+
+
+// Developed by Torben H. Nielsen
+vector<Equipment> DatabaseLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
+{
+	vector<Equipment> buffer;
+
+	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 ";
+		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();
+	}
+
+	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 ASC";
+		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.StartDato, Resevation.Antaldage, 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.startdate);
+				rs.GetFieldValue(1, res.numdays);
+				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();
+		}
+	}
+}