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