void CatBrowser::SetupQuery(void)
{
BString command, esccat = EscapeIllegalCharacters(fCategory.String());
CppSQLite3Query query;
command = "select count(number) from ";
command << esccat << ";";
query = DBQuery(command.String(), "CatBrowser::SetupQuery:get category count");
fCategoryCount = (query.eof()) ? 0 : query.getIntField(0);
for(int32 i = 0; i < fPageList.CountItems(); i++)
{
QueryPage *page = (QueryPage*)fPageList.ItemAt(i);
page->MakeEmpty();
}
fPageCount = (fCategoryCount / BROWSE_MODE_RECIPE_COUNT);
if( (fPageCount % BROWSE_MODE_RECIPE_COUNT) || fPageCount < BROWSE_MODE_RECIPE_COUNT)
fPageCount++;
if(fPageList.CountItems() < fPageCount)
{
for(int32 i = fPageList.CountItems(); i <= fPageCount; i++)
fPageList.AddItem(new QueryPage());
}
fCurrentPage = -1;
}
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));
}
}
status_t
LoadRules(const char *path, BObjectList<FilerRule> *ruleList)
{
BEntry entry("/boot/home/config/settings/FilerRules");
if (!entry.Exists())
return B_OK;
CppSQLite3DB db;
db.open("/boot/home/config/settings/FilerRules");
// Because this particular build of sqlite3 does not support multithreading
// because of lack of pthreads support, we need to do this in a slightly different order
CppSQLite3Query query;
query = DBQuery(db,"select name from RuleList order by ruleid;","PrefsWindow::LoadRules");
BString command;
while (!query.eof())
{
BString rulename = query.getStringField((int)0);
FilerRule *rule = new FilerRule;
rule->SetDescription(DeescapeIllegalCharacters(rulename.String()).String());
ruleList->AddItem(rule);
query.nextRow();
}
query.finalize();
for (int32 i = 0; i < ruleList->CountItems(); i++)
{
FilerRule *rule = ruleList->ItemAt(i);
if (!rule)
continue;
BString rulename(EscapeIllegalCharacters(rule->GetDescription()));
// Now comes the fun(?) part: loading the tests and actions. Joy. :/
command = "select * from ";
command << rulename << " where entrytype = 'test';";
query = DBQuery(db,command.String(),"PrefsWindow::LoadRules");
while (!query.eof())
{
BString classname = DeescapeIllegalCharacters(query.getStringField(1));
BMessage *test = new BMessage;
test->AddString("name",classname);
if (classname.ICompare("Attribute") == 0)
{
test->AddString("mimetype",DeescapeIllegalCharacters(query.getStringField(4)));
test->AddString("typename",DeescapeIllegalCharacters(query.getStringField(5)));
test->AddString("attrname",DeescapeIllegalCharacters(query.getStringField(6)));
}
test->AddString("mode",DeescapeIllegalCharacters(query.getStringField(2)).String());
test->AddString("value",DeescapeIllegalCharacters(query.getStringField(3)).String());
rule->AddTest(test);
query.nextRow();
}
query.finalize();
command = "select * from ";
command << rulename << " where entrytype = 'action';";
query = DBQuery(db,command.String(),"PrefsWindow::LoadRules");
while (!query.eof())
{
BMessage *action = new BMessage;
action->AddString("name",DeescapeIllegalCharacters(query.getStringField(1)));
action->AddString("value",DeescapeIllegalCharacters(query.getStringField(3)));
rule->AddAction(action);
query.nextRow();
}
query.finalize();
}
db.close();
return B_OK;
}
status_t
SaveRules(const char *path, BObjectList<FilerRule> *ruleList)
{
BEntry entry("/boot/home/config/settings/FilerRules");
if (entry.Exists())
entry.Remove();
CppSQLite3DB db;
db.open("/boot/home/config/settings/FilerRules");
// While we could use other means of obtaining table names, this table is also
// used for maintaining the order of the rules, which must be preserved
DBCommand(db,"create table RuleList (ruleid int primary key, name varchar);",
"PrefsWindow::SaveRules");
BString command;
for (int32 i = 0; i < ruleList->CountItems(); i++)
{
FilerRule *rule = ruleList->ItemAt(i);
// Test table:
// 0) Entry type (test vs action)
// 1) type
// 2) mode
// 3) value
// 4) attribute type (if Attribute test)
// 5) attribute type public name (short description)
// 6) attribute public name (if Attribute test)
BString tablename(EscapeIllegalCharacters(rule->GetDescription()));
command = "create table ";
command << tablename
<< "(entrytype varchar, testtype varchar, testmode varchar, testvalue varchar,
attrtype varchar, attrtypename varchar, attrpublicname varchar);";
DBCommand(db,command.String(), "PrefsWindow::SaveRules");
command = "insert into RuleList values(";
command << i << ",'" << tablename << "');";
DBCommand(db,command.String(), "PrefsWindow::SaveRules");
for (int32 j = 0; j < rule->CountTests(); j++)
{
BMessage *test = rule->TestAt(j);
if (!test)
continue;
BString name,mode,value,mimeType,typeName, attrType, attrName;
test->FindString("name",&name);
test->FindString("mode",&mode);
test->FindString("value",&value);
test->FindString("mimetype",&mimeType);
test->FindString("typename",&typeName);
test->FindString("attrtype",&attrType);
test->FindString("attrname",&attrName);
command = "insert into ";
command << tablename << " values('test', '" << EscapeIllegalCharacters(name.String())
<< "', '" << EscapeIllegalCharacters(mode.String())
<< "', '" << EscapeIllegalCharacters(value.String())
<< "', '" << EscapeIllegalCharacters(mimeType.String())
<< "', '" << EscapeIllegalCharacters(typeName.String())
<< "', '" << EscapeIllegalCharacters(attrName.String())
<< "');";
DBCommand(db,command.String(),"PrefsWindow::SaveRules:save test");
}
for (int32 j = 0; j < rule->CountActions(); j++)
{
BMessage *action = rule->ActionAt(j);
if (!action)
continue;
BString name,value;
action->FindString("name",&name);
action->FindString("value",&value);
command = "insert into ";
command << tablename << " values('action', '" << EscapeIllegalCharacters(name.String())
<< "', '"
<< "', '" << EscapeIllegalCharacters(value.String())
<< "', '', '', '');";
DBCommand(db,command.String(),"PrefsWindow::SaveRules:save action");
}
}
db.close();
return B_OK;
}
void CatBrowser::RunQuery(void)
{
BString esccat(EscapeIllegalCharacters(fCategory.String()));
if(fCategory.CountChars() < 1)
return;
fList->DeselectAll();
for(int32 i = 0; i < fList->CountItems(); i++)
{
RecipeItem *item = (RecipeItem*)fList->ItemAt(i);
delete item;
}
fList->MakeEmpty();
if(!fCategoryCount)
return;
int32 itemcount = fEditMode ? EDIT_MODE_RECIPE_COUNT : BROWSE_MODE_RECIPE_COUNT;
BString command;
CppSQLite3Query query;
if(fCurrentPage >= 0)
{
QueryPage *page = (QueryPage*)fPageList.ItemAt(fCurrentPage);
QueryPage *lastpage = NULL;
if(!page)
return;
command = "select * from ";
if(page->init)
{
command << esccat << " where (number >= " << page->start
<< ") and (number <= " << page->end << ") " << fSortString << ";";
}
else
{
lastpage = (QueryPage*)fPageList.ItemAt(fCurrentPage - 1);
command << esccat << " where number >= " << (lastpage->end + 1)
<< fSortString << ";";
}
query = DBQuery(command.String(), "CatBrowser:get category");
RecipeItem *ritem = NULL;
for(int32 i = 0; i < itemcount; i++)
{
if(query.eof())
break;
BString string = query.getStringField(1);
ritem = new RecipeItem(DeescapeIllegalCharacters(string.String()).String(),
esccat.String(), query.getIntField(0));
fList->AddItem(ritem);
query.nextRow();
}
if(lastpage && ritem)
{
page->start = lastpage->end + 1;
page->end = ritem->fNumber;
page->init = true;
}
}
else
{
command = "select * from ";
command << esccat << " where number >= 0 " << fSortString << ";";
query = DBQuery(command.String(), "CatBrowser:get category");
RecipeItem *ritem = NULL;
for(int32 i = 0; i < itemcount; i++)
{
if(query.eof())
break;
BString string = query.getStringField(1);
ritem = new RecipeItem(DeescapeIllegalCharacters(string.String()).String(),
esccat.String(), query.getIntField(0));
fList->AddItem(ritem);
query.nextRow();
}
if(ritem)
{
fCurrentPage = 0;
QueryPage *page = (QueryPage*)fPageList.ItemAt(fCurrentPage);
page->start = 0;
page->end = ritem->fNumber;
page->init = true;
}
}
fList->Select(0L);
fList->MakeFocus(true);
}
