public List<KeyValuePair<string, double>> GetConsumeAmountList(string format, ConsumeRecortQueryOption option)
{
string cmdText = string.Format(@"SELECT strftime('{0}', R.ConsumeTime) as 'Time', Sum(R.Money) as 'Money'
FROM [ConsumeRecord] R
JOIN [USER] U ON R.[UserId] = U.[UserId]
JOIN [ConsumeType] 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.ConsumeType.TypeId != 0)
{
if (option.ConsumeType.ParentTypeId != 0)
{
cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.ConsumeType.TypeId);
}
else
{
cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.ConsumeType.TypeId);
}
}
if (option.UserId != 0)
{
cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId);
}
cmdText = string.Format("{0} GROUP BY strftime('{1}', R.ConsumeTime)", cmdText, format);
Debug.WriteLine(cmdText);
var reader = SqliteHelper.ExecuteReader(cmdText);
return reader.ToConsumeAmountList();
}
public List<ConsumeRecord> GetConsumeRecordList(ConsumeRecortQueryOption option, out int recordCount, out decimal totalMoney)
{
return _consumeRecordDal.GetConsumeRecordList(option, out recordCount, out totalMoney);
}
public List<KeyValuePair<string, double>> GetConsumeAmountByYear(ConsumeRecortQueryOption option)
{
return _consumeRecordDal.GetConsumeAmountList("%m", option);
}
public List<ConsumeRecord> GetConsumeRecordList(ConsumeRecortQueryOption option, out int recordCount, out decimal totalMoney)
{
string cmdText = @"SELECT R.[Id] As RecordId, R.[ConsumeTime], R.[Money], R.[Memo], R.[RecordTime],
U.[UserId], U.[UserName], U.[FullName],
T.[TypeId], T.[ParentTypeId], T.[TypeName]
FROM [ConsumeRecord] R
JOIN [USER] U ON R.[UserId] = U.[UserId]
JOIN [ConsumeType] 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.ConsumeType.TypeId != 0)
{
if(option.ConsumeType.ParentTypeId != 0)
{
cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.ConsumeType.TypeId);
}
else
{
cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.ConsumeType.TypeId);
}
}
if (option.UserId != 0)
{
cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId);
}
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.FullName":
sortName = "U.[FullName]";
break;
case "TypeId":
sortName = "T.[UserId]";
break;
case "ParentTypeId":
sortName = "T.[ParentTypeId]";
break;
case "Type.TypeName":
sortName = "T.[TypeName]";
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();
}
