BasicAdmin - Personal Organizer

#include "stdafx.h"
#include "dbOpers.h"
#include "BasicTables.h"
#include "../Utils/Utils.h"

void CdbOpers::AddCurrency(int* ID, int CurrType, CString Prefix, CString Description, int Reference, double Value, CString Observations)
{
	CString strcurtype, strreference, strvalue, strid;
	
	*ID = GetNextID("CURRENCIES");
	strid.Format("%d", *ID);
	strcurtype.Format("%d", CurrType);
	strreference.Format("%d", Reference);
	strvalue.Format("%8.5f",Value);

	CString strexec = "INSERT INTO CURRENCIES " + 
	GetCurrenciesColumns() +
	" VALUES (" + strid + "," + strcurtype + ",'" + Prefix + "','"
	+ Description + "'," + strreference + "," + strvalue + ",'" +  Observations + "')";

	dbAdmin.execDML(strexec);
	
}
void CdbOpers::ModifyCurrency(int ID, int CurrType, CString Prefix, CString Description, int Reference, double Value, CString Observations)
{
	CString strcurtype, strreference, strvalue, strid;
	
	strid.Format("%d", ID);
	strcurtype.Format("%d", CurrType);
	strreference.Format("%d", Reference);
	strvalue.Format("%8.5f",Value);

	CString strexec = "UPDATE CURRENCIES SET CURTYPE = " + strcurtype + ", PREFIX = '" + Prefix +
	"', DESCRIPTION = '" + Description + "', REFERENCE = " + strreference + ", VALUE = " +
	strvalue + ",OBSERVATIONS = '" + Observations + "' WHERE ID = " + strid;

	dbAdmin.execDML(strexec);
}
int CdbOpers::GetNextID(CString TableName)
{
	CppSQLite3Query q = dbAdmin.execQuery("SELECT MAX(ID) FROM " + TableName);
	return q.getIntField(0) + 1;
}
CString CdbOpers::GetCurrenciesColumns()
{
	return "(ID,CURTYPE,PREFIX,DESCRIPTION,REFERENCE,VALUE,OBSERVATIONS)";
}
CString CdbOpers::GetCurDescription(int ID)
{
	if (ID == 0) return GetStringFromRes(IDBASIC_CUR);

	CString strid;
	strid.Format("%d", ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT DESCRIPTION FROM CURRENCIES WHERE ID = " + strid);
	return q.getStringField(0);
}
void CdbOpers::GetCurrency(int ID, int* CurrType, CString* Prefix, CString* Description, int* Reference, double* Value, CString* Observations)
{
	CString strid;
	strid.Format("%d", ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT * FROM CURRENCIES WHERE ID = " + strid);
	*CurrType = q.getIntField("CURTYPE");
	*Prefix = q.getStringField("PREFIX");
	*Description = q.getStringField("DESCRIPTION");
	*Reference = q.getIntField("REFERENCE");
	*Value = q.getFloatField("VALUE");
	*Observations = q.getStringField("OBSERVATIONS");
	q.finalize();
}
void CdbOpers::DeleteCurrency(int ID)
{
	CString strid;
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM CURRENCIES WHERE ID = " + strid);
}
void CdbOpers::AddAccount(int* ID, CAccount* acc)
{
	CString stractype, strid, stridcur, strparent, short1, short2;

	acc->ID = GetNextID("ACCOUNTS");
	*ID = acc->ID;

	stractype.Format("%d", acc->AccountType);
	strid.Format("%d", acc->ID);
	stridcur.Format("%d", acc->IdCurrency);
	strparent.Format("%d", acc->Parent);
	short1.Format("%d", acc->Shortcut1);
	short2.Format("%d", acc->Shortcut2);

	CString strcolumns = "(PARENT,ID,ACCTYPE,DESCRIPTION,SHORTCUT1,SHORTCUT2,ID_CUR,OBSERVATIONS)";

	CString strexec = "INSERT INTO ACCOUNTS " + strcolumns + " VALUES (" + strparent + "," +
	strid + "," + stractype + ",'" + acc->Description + "'," + short1 + "," + short2 + "," + 
	stridcur + ",'" + acc->Observations + "')";

	dbAdmin.execDML(strexec);
}

void CdbOpers::AddTreeObj(int* ID, CTreeObj* obj)
{
	CString strid, strparent, short1, short2, strtype;

	obj->ID = GetNextID(TableObj);
	*ID = obj->ID;

	strid.Format("%d", obj->ID);
	strparent.Format("%d", obj->Parent);
	short1.Format("%d", obj->Shortcut1);
	short2.Format("%d", obj->Shortcut2);
	strtype.Format("%d", obj->Type);

	CString strcolumns = "(PARENT,ID,DESCRIPTION,SHORTCUT1,SHORTCUT2,OBSERVATIONS,TYPE)";

	CString strexec = "INSERT INTO " + TableObj  + strcolumns + " VALUES (" + strparent + "," +
	strid + ",'" + obj->Description + "'," + short1 + "," + short2 + 
	",'" + obj->Observations + "'," + strtype + ")";

	dbAdmin.execDML(strexec);
}

void CdbOpers::ModifyTreeObj(CTreeObj* obj)
{
	CString strid, strparent, short1, short2, strtype;

	strid.Format("%d", obj->ID);
	strparent.Format("%d", obj->Parent);
	short1.Format("%d", obj->Shortcut1);
	short2.Format("%d", obj->Shortcut2);
	strtype.Format("%d", obj->Type);

	CString strexec = "UPDATE " + TableObj + " SET PARENT = " + strparent +
	", DESCRIPTION = '" + obj->Description + "',SHORTCUT1 = " + short1 + ",SHORTCUT2 = " +
	short2 + ",OBSERVATIONS = '" + obj->Observations + "', TYPE = " + strtype + " WHERE ID = " + strid;

	dbAdmin.execDML(strexec);
}

void CdbOpers::ModifyAccount(CAccount* acc)
{
	CString stractype, strid, stridcur, strparent, short1, short2;

	stractype.Format("%d", acc->AccountType);
	strid.Format("%d", acc->ID);
	stridcur.Format("%d", acc->IdCurrency);
	strparent.Format("%d", acc->Parent);
	short1.Format("%d", acc->Shortcut1);
	short2.Format("%d", acc->Shortcut2);

	CString strexec = "UPDATE ACCOUNTS SET PARENT = " + strparent + ", ACCTYPE = " + stractype +
	", DESCRIPTION = '" + acc->Description + "',SHORTCUT1 = " + short1 + ",SHORTCUT2 = " +
	short2 + ",ID_CUR = " + stridcur + ",OBSERVATIONS = '" + acc->Observations + "' WHERE ID = " + strid;

	dbAdmin.execDML(strexec);
}
void CdbOpers::GetAccount(CAccount* acc)
{
	CString strid;
	strid.Format("%d", acc->ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT * FROM ACCOUNTS WHERE ID = " + strid);

	acc->AccountType = (CBasicTables::eAccountType)q.getIntField("ACCTYPE");
	acc->Description = q.getStringField("DESCRIPTION");
	acc->IdCurrency = q.getIntField("ID_CUR");
	acc->Observations = q.getStringField("OBSERVATIONS");
	acc->Parent = q.getIntField("PARENT");
	acc->Shortcut1 = q.getIntField("SHORTCUT1");
	acc->Shortcut2 = q.getIntField("SHORTCUT2");

	q.finalize();
}
void CdbOpers::DeleteAccount(int ID)
{
	CString strid;
	CUtils ut;
	
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM ACCOUNTS WHERE ID = " + strid);	
}

void CdbOpers::DeleteTreeObj(int ID)
{
	CString strid;
	strid.Format("%d", ID);

	CUtils ut;
	ut.DelParent(TableObj, "ID", "PARENT", ID);

	dbAdmin.execDML("DELETE FROM " + TableObj + " WHERE ID = " + strid);	
}
void CdbOpers::GetTreeObj(CTreeObj* obj)
{
	CString strid;
	strid.Format("%d", obj->ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT * FROM " + TableObj + " WHERE ID = " + strid);

	obj->Description = q.getStringField("DESCRIPTION");
	obj->Observations = q.getStringField("OBSERVATIONS");
	obj->Parent = q.getIntField("PARENT");
	obj->Shortcut1 = q.getIntField("SHORTCUT1");
	obj->Shortcut2 = q.getIntField("SHORTCUT2");
	obj->Type = q.getIntField("TYPE");

	q.finalize();
}
void CdbOpers::AddDailyInput(int* ID, CDailyInput* din)
{
	CString strid, stroperid, straccfrom, straccto, strtype, strvalue, strcurexchange, strcurcomission;

	din->ID = GetNextID("DAILYINPUT");
	*ID = din->ID;

	strid.Format("%d", din->ID);
	stroperid.Format("%d", din->OperID);
	straccfrom.Format("%d", din->AccFrom);
	straccto.Format("%d", din->AccTo);
	strtype.Format("%d", din->OperType);
	strvalue.Format("%12.5f",din->Value);
	strcurexchange.Format("%12.5f",din->Cur_Exchange);
	strcurcomission.Format("%12.5f",din->Cur_Comission);
	
	CString strcolumns = "(ID,OPERID,ACCFROM,ACCTO,OPERTYPE,VALUE,OBSERVATIONS,CUR_EXCHANGE,CUR_COMISSION,DATE)";

	CString strexec = "INSERT INTO DAILYINPUT " + strcolumns + " VALUES (" + strid + "," +
	stroperid + "," + straccfrom + "," + straccto + "," + strtype + "," + strvalue + ",'" + 
	din->Observations + "'," + strcurexchange + "," + strcurcomission + ",'" + din->Date + "')";

	dbAdmin.execDML(strexec);
}
void CdbOpers::ModifyDailyInput(CDailyInput* din)
{
	CString strid, stroperid, straccfrom, straccto, strtype, strvalue, strcurexchange, strcurcomission;

	strid.Format("%d", din->ID);
	stroperid.Format("%d", din->OperID);
	straccfrom.Format("%d", din->AccFrom);
	straccto.Format("%d", din->AccTo);
	strtype.Format("%d", din->OperType);
	strvalue.Format("%12.5f",din->Value);
	strcurexchange.Format("%12.5f",din->Cur_Exchange);
	strcurcomission.Format("%12.5f",din->Cur_Comission);

	CString strexec = "UPDATE DAILYINPUT SET OPERID = " + stroperid + ", ACCFROM = " + straccfrom +
	", ACCTO = " + straccto + ",DATE='" + din->Date + "',OPERTYPE = " + strtype + ",VALUE = " +
	strvalue + ",OBSERVATIONS = '" + din->Observations + "',CUR_EXCHANGE=" + strcurexchange +
	",CUR_COMISSION=" + strcurcomission + " WHERE ID = " + strid;

	dbAdmin.execDML(strexec);	
}
void CdbOpers::GetDailyInput(CDailyInput* din)
{
	CString strid;
	strid.Format("%d", din->ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT * FROM DAILYINPUT WHERE ID = " + strid);

	din->OperType = (CBasicTables::eDailyInputType)q.getIntField("OPERTYPE");
	din->AccFrom = q.getIntField("ACCFROM");
	din->AccTo = q.getIntField("ACCTO");
	din->Observations = q.getStringField("OBSERVATIONS");
	din->OperID = q.getIntField("OPERID");
	din->Value = q.getFloatField("VALUE");
	din->Cur_Exchange = q.getFloatField("CUR_EXCHANGE");
	din->Cur_Comission = q.getFloatField("CUR_COMISSION");
	din->Date = q.getStringField("DATE");
	q.finalize();
}
void CdbOpers::DeleteDailyInput(int ID)
{
	CString strid;
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM DAILYINPUT WHERE ID = " + strid);	
}
void CdbOpers::GetContactInfo(CContactInfo* con, CppSQLite3Query* q1)
{
	CString strid;
	strid.Format("%d", con->ID);
	CppSQLite3Query q;
	if (q1 == 0)
	{
		q = dbAdmin.execQuery("SELECT * FROM CONTACTS WHERE ID = " + strid);
		q1 = &q;
	}

	con->Name = q1->getStringField("NAME");
	con->SurName = q1->getStringField("SURNAME");
	con->Category = q1->getIntField("CATEGORY");
	con->Phone = q1->getStringField("PHONE");
	con->Address = q1->getStringField("ADDRESS");
	con->Observations = q1->getStringField("OBSERVATIONS");
	con->Mails = q1->getStringField("MAILS");
}
void CdbOpers::AddContactInfo(int* ID, CContactInfo* con)
{
	con->ID = GetNextID("CONTACTS");
	*ID = con->ID;
	
	CString strcat, strid;
	strid.Format("%d", con->ID);
	strcat.Format("%d", con->Category);

	CString strcolumns = "(ID,CATEGORY,NAME,SURNAME,PHONE,ADDRESS,OBSERVATIONS,MAILS)";

	CString strexec = "INSERT INTO CONTACTS " + strcolumns + " VALUES (" + strid + "," +
	strcat + ",'" + con->Name + "','" + con->SurName + "','" + con->Phone + "','" + con->Address + "','" + con->Observations + "','" + con->Mails + "')";

	dbAdmin.execDML(strexec);
}
void CdbOpers::ModifyContactInfo(CContactInfo* con)
{
	CString strid, strcat;

	strid.Format("%d", con->ID);
	strcat.Format("%d", con->Category);

	CString strexec = "UPDATE CONTACTS SET CATEGORY = " + strcat + ", NAME = '" + con->Name + "'," +
	"SURNAME = '" + con->SurName + "',PHONE = '" + con->Phone + "',ADDRESS = '" +
	con->Address + "',OBSERVATIONS = '" + con->Observations + "', MAILS= '" + con->Mails + "' WHERE ID = " + strid;

	dbAdmin.execDML(strexec);	
}
void CdbOpers::DeleteContactInfo(int ID)
{
	CString strid;
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM CONTACTS WHERE ID = " + strid);	
}
void CdbOpers::AddNotetInfo(int* ID, CNoteInfo* note)
{
	note->ID = GetNextID("NOTES");
	*ID = note->ID;
	
	CString strcat, strid;
	strid.Format("%d", note->ID);
	strcat.Format("%d", note->Category);

	CString strcolumns = "(ID,CATEGORY,NAME,OBSERVATIONS)";

	CString strexec = "INSERT INTO NOTES " + strcolumns + " VALUES (" + strid + "," +
	strcat + ",'" + note->Name + "','" + note->Observations + "')";

	dbAdmin.execDML(strexec);
}
void CdbOpers::ModifyNoteInfo(CNoteInfo* note)
{
	CString strid, strcat;

	strid.Format("%d", note->ID);
	strcat.Format("%d", note->Category);

	CString strexec = "UPDATE NOTES SET CATEGORY = " + strcat + ", NAME = '" + note->Name + "'," +
	"OBSERVATIONS = '" + note->Observations + "' WHERE ID = " + strid;

	dbAdmin.execDML(strexec);	
}
void CdbOpers::DeleteNoteInfo(int ID)
{
	CString strid;
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM NOTES WHERE ID = " + strid);	
}
void CdbOpers::GetNoteInfo(CNoteInfo* note)
{
	CString strid;
	strid.Format("%d", note->ID);
	CppSQLite3Query q = dbAdmin.execQuery("SELECT * FROM NOTES WHERE ID = " + strid);
	
	note->Name = q.getStringField("NAME");
	note->Category = q.getIntField("CATEGORY");
	note->Observations = q.getStringField("OBSERVATIONS");
}

void CdbOpers::AddTaskData(int* ID, CTaskData* td)
{
	td->ID = GetNextID("TASKDATA");
	*ID = td->ID;

	CString strcolumns = "(ID,PARENT,CATEGORY,NAME,START_DATE,FINISH_DATE,PRIO,COMPLETED,ALLOCATEDTO,DIFFICULTY,TYPE,WEIGHT,OBSERVATIONS)";

	CString strexec;
	strexec.Format("INSERT INTO TASKDATA %s VALUES (%d, %d, %d, '%s','%s','%s',%d,%.2f,%d,%d,%d,%.2f,'%s')",strcolumns,td->ID,td->Parent,td->Category,td->Name,td->DateFrom,td->DateFinish,td->Prio,td->Completed,td->IdAllocatedTo,td->Difficulty,td->Type,td->Weight,td->Observations);
	
	dbAdmin.execDML(strexec);
}
void CdbOpers::ModifyTaskData(CTaskData* td)
{
	CString strexec;

	strexec.Format("UPDATE TASKDATA SET PARENT=%d,CATEGORY=%d,NAME='%s',START_DATE='%s',FINISH_DATE='%s',PRIO=%d,COMPLETED=%.2f,ALLOCATEDTO=%d,DIFFICULTY=%d,TYPE=%d,WEIGHT=%.2f,OBSERVATIONS='%s'"
	" WHERE ID=%d", td->Parent,td->Category,td->Name,td->DateFrom,td->DateFinish,td->Prio,td->Completed,td->IdAllocatedTo,td->Difficulty,td->Type,td->Weight,td->Observations, td->ID);

	dbAdmin.execDML(strexec);	
}
void CdbOpers::DeleteTaskData(int ID)
{
	CString strid;
	strid.Format("%d", ID);
	dbAdmin.execDML("DELETE FROM TASKDATA WHERE ID = " + strid);	
}
void CdbOpers::GetTaskData(CTaskData* td, CppSQLite3Query* q1)
{
	CppSQLite3Query q;
	CString strid;
	strid.Format("%d", td->ID);
	if (q1 == 0)
	{
		q = dbAdmin.execQuery("SELECT * FROM TASKDATA WHERE ID = " + strid);
		q1 = &q;
	}

	td->Category = q1->getIntField("CATEGORY");
	td->Parent = q1->getIntField("PARENT");
	td->Prio = q1->getIntField("PRIO");
	td->Name = q1->getStringField("NAME");
	td->DateFrom = q1->getStringField("START_DATE");
	td->DateFinish = q1->getStringField("FINISH_DATE");
	td->Completed = q1->getFloatField("COMPLETED");
	td->Type = q1->getIntField("TYPE");
	td->Weight = q1->getFloatField("WEIGHT");

	td->IdAllocatedTo = q1->getIntField("ALLOCATEDTO");
	td->Difficulty = q1->getIntField("DIFFICULTY");
	td->Observations = q1->getStringField("OBSERVATIONS");
}
void CdbOpers::GetCurrency(CCurrency* cur, CppSQLite3Query* q1)
{
	CString strid;
	strid.Format("%d", cur->ID);
	CppSQLite3Query q;
	if (q1 == 0)
	{
		q = dbAdmin.execQuery("SELECT * FROM CURRENCIES WHERE ID = " + strid);
		q1 = &q;
	}
	cur->ID = q1->getIntField("ID");
	cur->CurrType = q1->getIntField("CURTYPE");
	cur->Prefix = q1->getStringField("PREFIX");
	cur->Description = q1->getStringField("DESCRIPTION");
	cur->Reference = q1->getIntField("REFERENCE");
	cur->Value = q1->getFloatField("VALUE");
	cur->Observations = q1->getStringField("OBSERVATIONS");
}