public Dictionary<string, double> GetAccountAmountInfo(string format, AccountRecordQueryOption option) { string cmdText = string.Format(@"SELECT strftime('{0}', R.ConsumeTime) as 'Time', CAST(Sum(R.Money) as DOUBLE) as 'Money' FROM [AccountRecord] R JOIN [USER] U ON R.[UserId] = U.[UserId] JOIN [AccountType] T ON T.[TypeId] = R.[TypeId] WHERE 1=1", format); cmdText = string.Format("{0} AND R.[ConsumeTime] >= '{1}'", cmdText, option.BeginTime.ToString("yyyy-MM-dd HH:mm:ss")); cmdText = string.Format("{0} AND R.[ConsumeTime] < '{1}'", cmdText, option.EndTime.ToString("yyyy-MM-dd HH:mm:ss")); if (!option.ShowAccessorial) { cmdText = string.Format("{0} AND R.[IsAccessorial] = 0", cmdText); } if (option.AccountType.TypeId != 0) { if (option.AccountType.ParentTypeId != 0) { cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.AccountType.TypeId); } else { cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.AccountType.TypeId); } } if (option.UserId != 0) { cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId); } if (option.AccountCategory != AccountCategory.Undefined) { cmdText = string.Format("{0} AND T.[Category] = {1}", cmdText, (int)option.AccountCategory); } // Keyword if (!string.IsNullOrWhiteSpace(option.KeyWord)) { cmdText = string.Format(@"{0} AND R.[Memo] like '%{1}%' escape '{2}'", cmdText, GetFinalKeyword(option.KeyWord), ESCAPER); } cmdText = string.Format("{0} GROUP BY strftime('{1}', R.ConsumeTime)", cmdText, format); Debug.WriteLine(cmdText); var reader = SqliteHelper.ExecuteReader(cmdText); return reader.ToConsumeAmountDictionary(); }
public AccountRecordsResult GetConsumeRecordList(AccountRecordQueryOption option) { int recordCount; decimal totalMoney; List<AccountRecord> records = _accountRecordDAL.GetAccountRecordList(option, out recordCount, out totalMoney); AccountRecordsResult result = new AccountRecordsResult { Records = records, TotalCount = recordCount, TotalMoney = totalMoney }; return result; }
public AccountRecordsResult GetConsumeRecordList(AccountRecordQueryOption option) { return _consumeRecordBLL.GetConsumeRecordList(option); }
public Dictionary<string, double> GetConsumeAmountByYear(AccountRecordQueryOption option) { return _consumeRecordBLL.GetConsumeAmountByYear(option); }
public Dictionary<string, double> GetAccountTypeInfo(AccountRecordQueryOption option, int typeLevel) { return _consumeRecordBLL.GetAccountTypeInfo(option, typeLevel); }
/// <summary> /// 查询消费记录 /// </summary> private void QueryRecords() { if (this.CurrQueryOperation != null && !this.CurrQueryOperation.IsComplete) { return; } if (_accountType == null) { _accountType = AccountBookContext.Instance.DefaultAccountType; } var option = new AccountRecordQueryOption { AccountType = _accountType.Clone(), UserId = _consumer == null ? AccountBookContext.Instance.DefaultConsumer.UserId : _consumer.UserId, PageIndex = 0, PageSize = int.MaxValue, BeginTime = _beginDate.HasValue ? _beginDate.Value : DateTime.MinValue, EndTime = _endDate.HasValue ? _endDate.Value.AddDays(1) : DateTime.MaxValue, KeyWord = _keyword, AccountCategory = _accountCategory, ShowAccessorial = _showAccessorial }; if (_statisticsType == StatisticsType.AmountInfo) { if (_statisticsRange == "Month") { // 获取服务端消费记录数据 this.CurrQueryOperation = ContextFactory.RecordsContext.GetConsumeAmountByMonth(option, result => { if (result.IsCanceled) { return; } if (result.HasError) { ErrorWindow.CreateNew(result.Error); } else { _dataInfos = result.Value; DrawChart(_dataInfos, _renderAs); } }, null); } else { // 获取服务端消费记录数据 this.CurrQueryOperation = ContextFactory.RecordsContext.GetConsumeAmountByYear(option, result => { if (result.IsCanceled) { return; } if (result.HasError) { ErrorWindow.CreateNew(result.Error); } else { _dataInfos = result.Value; DrawChart(_dataInfos, _renderAs); } }, null); } } else if(_statisticsType == StatisticsType.AccountType) { this.CurrQueryOperation = ContextFactory.RecordsContext.GetAccountTypeInfo(option, 2, result => { if (result.IsCanceled) { return; } if (result.HasError) { ErrorWindow.CreateNew(result.Error); } else { _dataInfos = result.Value; DrawChart(_dataInfos, _renderAs); } }, null); } }
/// <summary> /// 查询消费记录 /// </summary> private void QueryRecords() { if (this.CurrQueryOperation != null && !this.CurrQueryOperation.IsComplete) { return; } if (_accountType == null) { _accountType = AccountBookContext.Instance.DefaultAccountType; } var option = new AccountRecordQueryOption { AccountType = _accountType.Clone(), UserId = _consumer == null ? AccountBookContext.Instance.DefaultConsumer.UserId : _consumer.UserId, PageIndex = RecordsPager.PageIndex == -1 ? 0 : RecordsPager.PageIndex, PageSize = RecordsPager.PageSize, BeginTime = _begigDate.HasValue ? _begigDate.Value : DateTime.MinValue, EndTime = _endDate.HasValue ? _endDate.Value.AddDays(1) : DateTime.MaxValue, SortName = _sortName, SortDir = _sortDir, KeyWord = _keyword, AccountCategory = _accountCategory, ShowAccessorial = _showAccessorial }; if (_accountCategory == AccountCategory.Expense) { BtnCreateExpenseRecord.Visibility = Visibility.Visible; BtnCreateIncomeRecord.Visibility = Visibility.Collapsed; } else if (_accountCategory == AccountCategory.Income) { BtnCreateExpenseRecord.Visibility = Visibility.Collapsed; BtnCreateIncomeRecord.Visibility = Visibility.Visible; } else { BtnCreateExpenseRecord.Visibility = Visibility.Visible; BtnCreateIncomeRecord.Visibility = Visibility.Visible; } // 获取服务端消费记录数据 this.CurrQueryOperation = ContextFactory.RecordsContext.GetConsumeRecordList(option, operation => { if (operation.IsCanceled) { return; } this.RecordsGrid.ItemsSource = operation.Value.Records; TotalMoney.Text = operation.Value.TotalMoney.ToString(); if (operation.Value.TotalCount != RecordsPager.ItemCount) { _recordsCounts.Clear(); for (int count = 0; count < operation.Value.TotalCount; count++) { _recordsCounts.Add(count); } var pcv = new PagedCollectionView(_recordsCounts); this.RecordsPager.Source = pcv; this.RecordsPager.PageIndex = _currPageIndex; } }, null); }
public Dictionary<string, double> GetConsumeAmountByYear(AccountRecordQueryOption option) { return _accountRecordDAL.GetAccountAmountInfo("%Y年", option); }
public List<AccountRecord> GetAccountRecordList(AccountRecordQueryOption option, out int recordCount, out decimal totalMoney) { string cmdText = @"SELECT R.[Id] As RecordId, R.[ConsumeTime], R.[Money], R.[Memo], R.[RecordTime], R.[IsAccessorial], U.[UserId], U.[UserName], U.[FriendlyName], T.[TypeId], T.[ParentTypeId], T.[TypeName], T.[Category] FROM [AccountRecord] R JOIN [USER] U ON R.[UserId] = U.[UserId] JOIN [AccountType] T ON T.[TypeId] = R.[TypeId] WHERE 1=1"; cmdText = string.Format("{0} AND R.[ConsumeTime] >= '{1}'", cmdText, option.BeginTime.ToString("yyyy-MM-dd HH:mm:ss")); cmdText = string.Format("{0} AND R.[ConsumeTime] < '{1}'", cmdText, option.EndTime.ToString("yyyy-MM-dd HH:mm:ss")); if (!option.ShowAccessorial) { cmdText = string.Format("{0} AND R.[IsAccessorial] = 0", cmdText); } if (option.AccountType.TypeId != 0) { if (option.AccountType.ParentTypeId != 0) { cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.AccountType.TypeId); } else { cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.AccountType.TypeId); } } if (option.UserId != 0) { cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId); } if(option.AccountCategory != AccountCategory.Undefined) { cmdText = string.Format("{0} AND T.[Category] = {1}", cmdText, (int)option.AccountCategory); } // Keyword if (!string.IsNullOrWhiteSpace(option.KeyWord)) { cmdText = string.Format(@"{0} AND R.[Memo] like '%{1}%' escape '{2}'", cmdText, GetFinalKeyword(option.KeyWord), ESCAPER); } // Sort if (!string.IsNullOrWhiteSpace(option.SortName)) { string sortName = option.SortName; switch (option.SortName) { case "Memo": sortName = "R.[Memo]"; break; case "ConsumeTime": sortName = "R.[ConsumeTime]"; break; case "Money": sortName = "R.[Money]"; break; case "RecordTime": sortName = "R.[RecordTime]"; break; case "UserId": sortName = "U.[UserId]"; break; case "UserName": sortName = "U.[UserName]"; break; case "User.FriendlyName": sortName = "U.[FriendlyName]"; break; case "TypeId": sortName = "T.[TypeId]"; break; case "ParentTypeId": sortName = "T.[ParentTypeId]"; break; case "Type.TypeName": sortName = "T.[TypeName]"; break; case "Accessorial": sortName = "R.[IsAccessorial]"; break; } cmdText = string.Format("{0} ORDER BY {1} {2}", cmdText, sortName, option.SortDir); } Debug.WriteLine(cmdText); var reader = SqliteHelper.ExecutePagerReader(out recordCount, option.PageIndex, option.PageSize, cmdText); cmdText = string.Format("SELECT SUM([Money]) FROM ({0})", cmdText); decimal.TryParse(SqliteHelper.ExecuteScalar(cmdText).ToString(), out totalMoney); return reader.ToConsumeRecordList(); }