void BudgetWindow::CalcStats(const char *cat, Fixed &high, Fixed &low, Fixed &avg)
{
if(!cat)
return;
BString querystring;
Fixed cattotal;
CppSQLite3Query query;
// find the average amount
for(int32 j=0; j<gDatabase.CountAccounts(); j++)
{
Account *acc = gDatabase.AccountAt(j);
querystring = "select sum(amount) from account_" ;
querystring << acc->GetID() << " where category = '"
<< EscapeIllegalCharacters(cat)
<< "';";
query = gDatabase.DBQuery(querystring.String(),
"BudgetWindow::CalcStats:get average");
cattotal.AddPremultiplied(query.getInt64Field(0));
query.finalize();
}
avg = cattotal;
avg /= 12;
// find the highest amount
cattotal=0;
for(int32 j=0; j<gDatabase.CountAccounts(); j++)
{
Account *acc = gDatabase.AccountAt(j);
querystring = "select max(amount) from account_" ;
querystring << acc->GetID() << " where category = '"
<< EscapeIllegalCharacters(cat)
<< "';";
query = gDatabase.DBQuery(querystring.String(),
"BudgetWindow::CalcStats:get highest");
Fixed value;
value.SetPremultiplied(query.getInt64Field(0));
cattotal = MAX(cattotal,value);
query.finalize();
}
high = cattotal;
// find the lowest amount
cattotal=0;
for(int32 j=0; j<gDatabase.CountAccounts(); j++)
{
Account *acc = gDatabase.AccountAt(j);
querystring = "select min(amount) from account_" ;
querystring << acc->GetID() << " where category = '"
<< EscapeIllegalCharacters(cat)
<< "';";
query = gDatabase.DBQuery(querystring.String(),
"BudgetWindow::CalcStats:get highest");
Fixed value;
value.SetPremultiplied(query.getInt64Field(0));
cattotal = MIN(cattotal,value);
query.finalize();
}
low = cattotal;
}
void BudgetWindow::GenerateBudget(const bool &zero)
{
// Generate a budget based on the last year's transactions
ReportGrid income(1,0), spending(1,0);
gDatabase.DBCommand("delete from budgetlist",
"BudgetWindow::GenerateBudget:empty budget");
CppSQLite3Query query;
query = gDatabase.DBQuery("select * from categorylist order by name",
"BudgetWindow::GenerateBudget:get categories");
if(query.eof())
return;
float maxwidth=fCategoryList->StringWidth(TRANSLATE("Category"));
while(!query.eof())
{
BString catname = DeescapeIllegalCharacters(query.getStringField(0));
if(catname.ICompare(TRANSLATE("Transfer"))==0)
{
query.nextRow();
continue;
}
bool isexpense = !query.getIntField(1);
if(isexpense)
{
spending.AddItem();
spending.SetRowTitle(spending.CountItems()-1,catname.String());
}
else
{
income.AddItem();
income.SetRowTitle(income.CountItems()-1,catname.String());
}
float tempwidth = fCategoryList->StringWidth(catname.String());
maxwidth = MAX(maxwidth,tempwidth);
query.nextRow();
}
query.finalize();
// Now that we have the list of categories, query for transactions for each
// account from each category
BString querystring;
Fixed cattotal;
for(int32 i=0; i<income.CountItems(); i++)
{
querystring = "";
cattotal = 0;
if(!zero)
{
for(int32 j=0; j<gDatabase.CountAccounts(); j++)
{
Account *acc = gDatabase.AccountAt(j);
querystring = "select sum(amount) from account_" ;
querystring << acc->GetID() << " where category = '"
<< EscapeIllegalCharacters(income.RowTitle(i))
<< "' and date > " << DecrementDateByYear(GetCurrentDate()) << ";";
query = gDatabase.DBQuery(querystring.String(),
"BudgetWindow::GenerateBudget:get category");
cattotal.AddPremultiplied(query.getInt64Field(0));
query.finalize();
}
cattotal /= 12;
cattotal.Round();
}
income.SetValue(0,i,cattotal);
gDatabase.AddBudgetEntry(BudgetEntry(income.RowTitle(i),cattotal,BUDGET_MONTHLY,false));
}
for(int32 i=0; i<spending.CountItems(); i++)
{
querystring = "";
cattotal = 0;
if(!zero)
{
for(int32 j=0; j<gDatabase.CountAccounts(); j++)
{
Account *acc = gDatabase.AccountAt(j);
querystring = "select sum(amount) from account_" ;
querystring << acc->GetID() << " where category = '"
<< EscapeIllegalCharacters(spending.RowTitle(i))
<< "';";
query = gDatabase.DBQuery(querystring.String(),
"BudgetWindow::GenerateBudget:get category");
cattotal.AddPremultiplied(query.getInt64Field(0));
query.finalize();
}
cattotal /= 12;
cattotal.Round();
}
spending.SetValue(0,i,cattotal);
gDatabase.AddBudgetEntry(BudgetEntry(spending.RowTitle(i),cattotal,BUDGET_MONTHLY,true));
}
}