#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();
}


// 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();
	}

	return buffer;
}

CString MySQLLayer::GenerateQueryForLatestCheckouts( CString barcode )
{
	CString SQL;

	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";	return SQL;

	return SQL;

}