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