public static List <ChartPart> GetExpensesFromCategoryGroupedBySubCategoryInRange(DateTime minDate, DateTime maxDate, string categoryGlobalId) { List <ChartPart> models = new List <ChartPart>(); // Create a new connection using (var db = new SQLiteConnection(new SQLitePlatformWinRT(), DbPath)) { // Activate Tracing db.TraceListener = new DebugTraceListener(); var query = from item in db.Table <Operation>().ToList() where !String.IsNullOrEmpty(item.Date) && item.isExpense && IsDateInRange(item.Date, minDate, maxDate) && item.CategoryGlobalId == categoryGlobalId group item.Cost by item.SubCategory?.GlobalId into g orderby g.Sum() descending select new { SubCategoryId = g.Key, Cost = g.Sum() }; foreach (var item in query) { SubCategory subCategory = CategoriesDal.GetCategoryByGlobalId(item.SubCategoryId) as SubCategory; if (subCategory != null) { models.Add(new ChartPart { SolidColorBrush = subCategory.Brush, Name = subCategory.Name, UnrelativeValue = (double)item.Cost }); } else { Category category = CategoriesDal.GetCategoryByGlobalId(categoryGlobalId); models.Add(new ChartPart { SolidColorBrush = category.Brush, Name = category.Name, UnrelativeValue = (double)item.Cost }); } } } return(models); }
public static void AddItemsIfEmpty() { using (var db = DbConnection) { if (db.ExecuteScalar <int>("SELECT seq FROM sqlite_sequence WHERE name = 'Categories'") == 0) { CategoriesDal.AddCategory(new Category { Name = "Inne", ColorKey = "14", IconKey = "FontIcon_2", LastModifed = "2017.01.01 00:00:00", VisibleInIncomes = true, VisibleInExpenses = true, CantDelete = true, GlobalId = "1" }); CategoriesDal.AddCategory(new Category { Name = "Jedzenie", ColorKey = "04", IconKey = "FontIcon_6", LastModifed = "2017.01.01 00:00:00", VisibleInExpenses = true, VisibleInIncomes = true, GlobalId = "2" }); CategoriesDal.AddCategory(new Category { Name = "Rozrywka", ColorKey = "12", IconKey = "FontIcon_20", LastModifed = "2017.01.01 00:00:00", VisibleInIncomes = false, VisibleInExpenses = true, GlobalId = "3" }); CategoriesDal.AddCategory(new Category { Name = "Rachunki", ColorKey = "08", IconKey = "FontIcon_21", LastModifed = "2017.01.01 00:00:00", VisibleInIncomes = false, VisibleInExpenses = true, GlobalId = "4" }); CategoriesDal.AddCategory(new Category { Name = "Prezenty", ColorKey = "05", IconKey = "FontIcon_13", LastModifed = "2017.01.01 00:00:00", VisibleInIncomes = true, VisibleInExpenses = true, GlobalId = "5" }); CategoriesDal.AddCategory(new Category { Name = "Praca", ColorKey = "14", IconKey = "FontIcon_9", LastModifed = "2017.01.01 00:00:00", VisibleInIncomes = true, VisibleInExpenses = false, GlobalId = "6" }); CategoriesDal.AddCategory(new SubCategory { Name = "Prąd", ColorKey = "07", IconKey = "FontIcon_19", LastModifed = "2017.01.01 00:00:00", BossCategoryId = "4", VisibleInIncomes = false, VisibleInExpenses = true, GlobalId = "7" }); CategoriesDal.AddCategory(new SubCategory { Name = "Imprezy", ColorKey = "11", IconKey = "FontIcon_17", LastModifed = "2017.01.01 00:00:00", BossCategoryId = "3", VisibleInIncomes = false, VisibleInExpenses = true, GlobalId = "8" }); } if (db.ExecuteScalar <int>("SELECT seq FROM sqlite_sequence WHERE name = 'MAccount'") == 0) { MAccountsDal.AddAccount(new MAccount { Name = "Gotówka", ColorKey = "01", LastModifed = "2017.01.01 00:00:00", GlobalId = "1" }); MAccountsDal.AddAccount(new MAccount { Name = "Konto bankowe", ColorKey = "02", LastModifed = "2017.01.01 00:00:00", GlobalId = "2" }); MAccountsDal.AddAccount(new SubMAccount { Name = "Karta", ColorKey = "03", LastModifed = "2017.01.01 00:00:00", GlobalId = "3", BossAccountGlobalId = "2" }); } } }
public static void AddInitialElements() { using (var db = DbConnection) { db.TraceListener = new DebugTraceListener(); db.Execute("INSERT INTO sqlite_sequence (name, seq) SELECT 'Account', 0 WHERE NOT EXISTS(SELECT 1 FROM sqlite_sequence WHERE name = 'Account')"); // AddCategory(new Category { Id = 1, Name = "Inne", ColorKey = "14", IconKey = "FontIcon_2", VisibleInIncomes = true, VisibleInExpenses = true }); CategoriesDal.AddCategory(new Category { Id = 2, Name = "Jedzenie", ColorKey = "04", IconKey = "FontIcon_6", VisibleInExpenses = true, VisibleInIncomes = true }); CategoriesDal.AddCategory(new Category { Id = 3, Name = "Rozrywka", ColorKey = "12", IconKey = "FontIcon_20", VisibleInIncomes = false, VisibleInExpenses = true }); CategoriesDal.AddCategory(new Category { Id = 4, Name = "Rachunki", ColorKey = "08", IconKey = "FontIcon_21", VisibleInIncomes = false, VisibleInExpenses = true }); CategoriesDal.AddCategory(new Category { Id = 5, Name = "Prezenty", ColorKey = "05", IconKey = "FontIcon_13", VisibleInIncomes = true, VisibleInExpenses = true }); CategoriesDal.AddCategory(new Category { Id = 6, Name = "Praca", ColorKey = "14", IconKey = "FontIcon_9", VisibleInIncomes = true, VisibleInExpenses = false }); CategoriesDal.AddCategory(new SubCategory { Id = 1, Name = "Prąd", ColorKey = "07", IconKey = "FontIcon_19", BossCategoryId = "4", VisibleInIncomes = false, VisibleInExpenses = true }); CategoriesDal.AddCategory(new SubCategory { Id = 2, Name = "Imprezy", ColorKey = "11", IconKey = "FontIcon_17", BossCategoryId = "3", VisibleInIncomes = false, VisibleInExpenses = true }); MAccountsDal.AddAccount(new MAccount { Name = "Gotówka", ColorKey = "01" }); MAccountsDal.AddAccount(new MAccount { Name = "Konto bankowe", ColorKey = "02", }); MAccountsDal.AddAccount(new SubMAccount { Name = "Karta", ColorKey = "03", BossAccountGlobalId = db.ExecuteScalar <string>("SELECT GlobalId FROM MAccount WHERE Id = 2 LIMIT 1") }); } }