/// <summary>
/// 删除借还信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="borrowRepayID">借还ID</param>
public async Task <bool> DeleteBorrowRepayAsync(int familyID, int borrowRepayID)
{
using (IOSysContext db = new IOSysContext())
{
using (var trans = db.Database.BeginTransaction())
{
//获取原数据
var model = db.BorrowRepays.Where(m => m.ID == borrowRepayID && m.FamilyID == familyID).FirstOrDefault();
if (model == null)
{
return(false);
}
//删除
db.BorrowRepays.Remove(model);
//账户金额变动值
var amount = this.GetAmountDirectionForBorrowRepay(model.BRType, model.Amount);
//修改账户信息
await BasicDAL.Inst.UnUseAmountAccount(model.FamilyID, model.AmountAccountID, amount, db);
await db.SaveChangesAsync();
//提交事务
trans.Commit();
//移除最小交易流水日期缓存
this.RemoveMinTurnoverDate(familyID, model.BRDate);
return(true);
}
}
}
/// <summary>
/// 保存更新账户排序权重字段
/// </summary>
/// <param name="list">账户</param>
/// <returns>修改条数</returns>
public async Task <int> UpdateAmountAccountSortWeightAsync(List <AmountAccount> list)
{
int retCount = 0;
using (IOSysContext db = new IOSysContext())
{
foreach (var model in list)
{
//更新
if (model.ID > 0)
{
db.Entry(model).Property(m => m.SortWeight).IsModified = true;
}
}
retCount = await db.SaveChangesAsync();
}
//家庭ID
var lstFamilyID = list.Select(m => m.FamilyID).Distinct().ToList();
foreach (var familyID in lstFamilyID)
{
//移除缓存
var key = string.Format(SysConst.Cache.AmountAccountList_FamilyID, familyID);
CacheHelper.Remove(key);
}
return(retCount);
}
/// <summary>
/// 拼接统计条件
/// </summary>
/// <param name="db">数据库上下文</param>
/// <param name="familyID">家庭ID</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">截止日期</param>
/// <param name="lstBRType">统计类型</param>
/// <returns></returns>
private IQueryable <BorrowRepay> GetSumBorrowRepayWhere(IOSysContext db, int familyID, DateTime?startDate, DateTime?endDate, List <int> lstBRType)
{
//家庭ID
var q = db.BorrowRepays.AsNoTracking().Where(m => m.FamilyID == familyID);
//开始日期
if (startDate.HasValue)
{
q = q.Where(m => m.BRDate >= startDate.Value);
}
//截止日期
if (endDate.HasValue)
{
q = q.Where(m => m.BRDate <= endDate.Value);
}
//借还类型
if (lstBRType != null && lstBRType.Count > 0)
{
q = q.Where(m => lstBRType.Contains(m.BRType));
}
return(q);
}
/// <summary>
/// 检查收入类型是否已使用
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public async Task <bool> IsInTypeUsedAsync(int id)
{
using (IOSysContext db = new IOSysContext())
{
return(await db.InComes.AnyAsync(m => m.InTypeID == id));
}
}
/// <summary>
/// 删除转账信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="transferID">转账ID</param>
public async Task <bool> DeleteTransferAsync(int familyID, int transferID)
{
using (IOSysContext db = new IOSysContext())
{
using (var trans = db.Database.BeginTransaction())
{
//获取原数据
var model = db.AmountAccountTransfers.Where(m => m.ID == transferID && m.FamilyID == familyID).FirstOrDefault();
if (model == null)
{
return(false);
}
//删除
db.AmountAccountTransfers.Remove(model);
//修改账户信息
await BasicDAL.Inst.UnUseAmountAccount(model.FamilyID, model.FromAmountAccountID, -model.Amount, db);
await BasicDAL.Inst.UnUseAmountAccount(model.FamilyID, model.ToAmountAccountID, model.Amount, db);
await db.SaveChangesAsync();
//提交事务
trans.Commit();
//移除最小交易流水日期缓存
this.RemoveMinTurnoverDate(familyID, model.TransferDate);
return(true);
}
}
}
/// <summary>
/// 保存支出类型
/// </summary>
/// <param name="list">支出类型</param>
/// <returns>改动条数</returns>
public async Task <int> SaveOutTypesAsync(List <OutType> list)
{
int retCount = 0;
using (IOSysContext db = new IOSysContext())
{
foreach (var model in list)
{
//更新
if (model.ID > 0)
{
db.Entry(model).State = EntityState.Modified;
}
//新增
else
{
await db.OutTypes.AddAsync(model);
}
}
retCount = await db.SaveChangesAsync();
}
//家庭ID
var lstFamilyID = list.Select(m => m.FamilyID).Distinct().ToList();
foreach (var familyID in lstFamilyID)
{
//移除缓存
var key = string.Format(SysConst.Cache.OutTypeList_FamilyID, familyID);
CacheHelper.Remove(key);
}
return(retCount);
}
/// <summary>
/// 统计账户在借还表中的使用次数
/// </summary>
/// <param name="start">开始时间</param>
/// <param name="end">截止时间</param>
/// <returns></returns>
public async Task <List <UseTimesTemp> > SumAmountAccountTimesInBorrowRepayAsync(DateTime?start, DateTime?end)
{
using (IOSysContext db = new IOSysContext())
{
var q = db.BorrowRepays.AsNoTracking();
//开始时间
if (start.HasValue)
{
q = q.Where(m => m.BRDate >= start.Value);
}
//截止时间
if (end.HasValue)
{
q = q.Where(m => m.BRDate <= end.Value);
}
var list = await(from m in q
group m by m.AmountAccountID into g
select new UseTimesTemp()
{
ID = g.Key,
Times = g.Count()
}).ToListAsync();
return(list);
}
}
/// <summary>
/// 保存用户
/// </summary>
/// <param name="model">用户</param>
/// <returns>主键ID</returns>
public async Task <int> SaveUserAsync(User model)
{
using (IOSysContext db = new IOSysContext())
{
//更新
if (model.ID > 0)
{
db.Entry(model).State = EntityState.Modified;
}
//新增
else
{
await db.Users.AddAsync(model);
}
await db.SaveChangesAsync();
}
//移除缓存
var key = string.Format(SysConst.Cache.UserList_FamilyID, model.FamilyID);
CacheHelper.Remove(key);
return(model.ID);
}
/// <summary>
/// 检查支出类型是否已使用
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public async Task <bool> IsOutTypeUsedAsync(int id)
{
using (IOSysContext db = new IOSysContext())
{
return(await db.OutPuts.AnyAsync(m => m.OutTypeID == id));
}
}
/// <summary>
/// 获取家庭信息
/// </summary>
/// <param name="id">家庭ID</param>
/// <returns></returns>
public async Task <Family> GetFamilyAsync(int id)
{
//判断缓存是否存在
var key = string.Format(SysConst.Cache.Family_ID, id);
var family = CacheHelper.Get <Family>(key);
if (family != null)
{
return(family);
}
using (IOSysContext db = new IOSysContext())
{
//从数据库查找
family = await db.Familys.FindAsync(id);
//写入缓存
if (family != null)
{
CacheHelper.Set(key, family);
}
return(family);
}
}
/// <summary>
/// 查询指定日期的借还信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="date">日期</param>
/// <returns></returns>
public async Task <List <BorrowRepay> > QueryBorrowRepayAsync(int familyID, DateTime date)
{
using (IOSysContext db = new IOSysContext())
{
var list = await db.BorrowRepays.AsNoTracking().Where(m => m.FamilyID == familyID && m.BRDate == date.Date).ToListAsync();
return(list);
}
}
/// <summary>
/// 查询指定日期的收入信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="date">日期</param>
/// <returns></returns>
public async Task <List <InCome> > QueryInComeAsync(int familyID, DateTime date)
{
using (IOSysContext db = new IOSysContext())
{
var list = await db.InComes.AsNoTracking().Where(m => m.FamilyID == familyID && m.InDate == date.Date).ToListAsync();
return(list);
}
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="loginName">登录名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public async Task <User> GetUserAsync(string loginName, string password)
{
using (IOSysContext db = new IOSysContext())
{
var model = await db.Users.Where(m => m.LoginName == loginName && m.Password == password).FirstOrDefaultAsync();
return(model);
}
}
/// <summary>
/// 异步添加登录日志
/// </summary>
/// <param name="log">登录日志</param>
/// <returns></returns>
public async Task AddLogAsync(LoginLog log)
{
using (var db = new IOSysContext())
{
await db.LoginLogs.AddAsync(log);
await db.SaveChangesAsync();
}
}
/// <summary>
/// 查询指定日期的转账信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="date">日期</param>
/// <returns></returns>
public async Task <List <AmountAccountTransfer> > QueryTransferAsync(int familyID, DateTime date)
{
using (IOSysContext db = new IOSysContext())
{
var list = await db.AmountAccountTransfers.AsNoTracking().Where(m => m.FamilyID == familyID && m.TransferDate == date.Date).ToListAsync();
return(list);
}
}
/// <summary>
/// 查询所有支出类型
/// </summary>
/// <returns></returns>
public async Task <List <OutType> > QueryAllOutTypeAsync()
{
using (IOSysContext db = new IOSysContext())
{
//从数据库查找
var list = await db.OutTypes.OrderByDescending(m => m.IsActive).ThenByDescending(m => m.SortWeight).ToListAsync();
return(list);
}
}
/// <summary>
/// 删除支出类型
/// </summary>
/// <param name="model">支出类型</param>
public async Task DeleteOutTypeAsync(OutType model)
{
using (IOSysContext db = new IOSysContext())
{
db.OutTypes.Remove(model);
await db.SaveChangesAsync();
}
//移除缓存
var key = string.Format(SysConst.Cache.OutTypeList_FamilyID, model.FamilyID);
CacheHelper.Remove(key);
}
/// <summary>
/// 支出统计
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">截止日期</param>
/// <returns></returns>
public async Task <decimal> SumOutPutAsync(int familyID, DateTime?startDate, DateTime?endDate)
{
using (IOSysContext db = new IOSysContext())
{
//条件
var q = this.GetSumOutPutWhere(db, familyID, startDate, endDate);
//统计
var total = await q.SumAsync(m => m.Amount);
return(total);
}
}
/// <summary>
/// 删除账户
/// </summary>
/// <param name="model">账户</param>
public async Task DeleteAmountAccountAsync(AmountAccount model)
{
using (IOSysContext db = new IOSysContext())
{
db.AmountAccounts.Remove(model);
await db.SaveChangesAsync();
}
//移除缓存
var key = string.Format(SysConst.Cache.AmountAccountList_FamilyID, model.FamilyID);
CacheHelper.Remove(key);
}
/// <summary>
/// 获取收入信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="inComeID">收入ID</param>
/// <returns></returns>
public async Task <InCome> GetInComeAsync(int familyID, int inComeID)
{
if (inComeID <= 0)
{
return(null);
}
using (IOSysContext db = new IOSysContext())
{
var model = await db.InComes.AsNoTracking().Where(m => m.ID == inComeID && m.FamilyID == familyID).FirstOrDefaultAsync();
return(model);
}
}
/// <summary>
/// 获取转账信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="transferID">转账ID</param>
/// <returns></returns>
public async Task <AmountAccountTransfer> GetTransferAsync(int familyID, int transferID)
{
if (transferID <= 0)
{
return(null);
}
using (IOSysContext db = new IOSysContext())
{
var model = await db.AmountAccountTransfers.AsNoTracking().Where(m => m.ID == transferID && m.FamilyID == familyID).FirstOrDefaultAsync();
return(model);
}
}
/// <summary>
/// 获取借还信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="borrowRepayID">借还ID</param>
/// <returns></returns>
public async Task <BorrowRepay> GetBorrowRepayAsync(int familyID, int borrowRepayID)
{
if (borrowRepayID <= 0)
{
return(null);
}
using (IOSysContext db = new IOSysContext())
{
var model = await db.BorrowRepays.AsNoTracking().Where(m => m.ID == borrowRepayID && m.FamilyID == familyID).FirstOrDefaultAsync();
return(model);
}
}
/// <summary>
/// 获取支出信息
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="outPutID">支出ID</param>
/// <returns></returns>
public async Task <OutPut> GetOutPutAsync(int familyID, int outPutID)
{
if (outPutID <= 0)
{
return(null);
}
using (IOSysContext db = new IOSysContext())
{
var model = await db.OutPuts.AsNoTracking().Where(m => m.ID == outPutID && m.FamilyID == familyID).FirstOrDefaultAsync();
return(model);
}
}
/// <summary>
/// 统计账户在转账表中的使用次数
/// </summary>
/// <param name="start">开始时间</param>
/// <param name="end">截止时间</param>
/// <returns></returns>
public async Task <List <UseTimesTemp> > SumAmountAccountTimesInTransferAsync(DateTime?start, DateTime?end)
{
using (IOSysContext db = new IOSysContext())
{
var q = db.AmountAccountTransfers.AsNoTracking();
//开始时间
if (start.HasValue)
{
q = q.Where(m => m.TransferDate >= start.Value);
}
//截止时间
if (end.HasValue)
{
q = q.Where(m => m.TransferDate <= end.Value);
}
//原账户统计
var lstFrom = await(from m in q
group m by m.FromAmountAccountID into g
select new UseTimesTemp()
{
ID = g.Key,
Times = g.Count()
}).ToListAsync();
//目标账户统计
var lstTo = await(from m in q
group m by m.ToAmountAccountID into g
select new UseTimesTemp()
{
ID = g.Key,
Times = g.Count()
}).ToListAsync();
//合计
var list = (from m in lstFrom.Union(lstTo)
group m by m.ID into g
select new UseTimesTemp()
{
ID = g.Key,
Times = g.Sum(m => m.Times)
}).ToList();
return(list);
}
}
/// <summary>
/// 检查账户是否已使用
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public async Task <bool> IsAmountAccountUsedAsync(int id)
{
using (IOSysContext db = new IOSysContext())
{
//收入表是否有使用
var exists = await db.InComes.AnyAsync(m => m.AmountAccountID == id);
if (exists)
{
return(exists);
}
//支出表是否有使用
exists = await db.OutPuts.AnyAsync(m => m.AmountAccountID == id);
if (exists)
{
return(exists);
}
//收入类型表是否有使用
exists = await db.InTypes.AnyAsync(m => m.AmountAccountID == id);
if (exists)
{
return(exists);
}
//支出类型表是否有使用
exists = await db.OutTypes.AnyAsync(m => m.AmountAccountID == id);
if (exists)
{
return(exists);
}
//转账表是否有使用
exists = await db.AmountAccountTransfers.AnyAsync(m => m.FromAmountAccountID == id || m.ToAmountAccountID == id);
if (exists)
{
return(exists);
}
return(exists);
}
}
/// <summary>
/// 按月份统计
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="req">条件</param>
/// <returns></returns>
public async Task <List <SumListInfo <string> > > SumInComeMonthAsync(int familyID, InSumReq req)
{
using (IOSysContext db = new IOSysContext())
{
//条件
var q = this.GetSumInComeWhere(db, familyID, req.StartDate, req.EndDate);
//统计
var list = await(from m in q
group m by new { m.InDate.Year, m.InDate.Month } into g
select new SumListInfo <string>()
{
name = g.First().InDate.ToString("yyyy-MM"),
value = g.Sum(m => m.Amount)
}).ToListAsync();
return(list);
}
}
/// <summary>
/// 按账户统计
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="req">条件</param>
/// <returns></returns>
public async Task <List <SumListInfo <int> > > SumOutPutAmountAccountAsync(int familyID, OutSumReq req)
{
using (IOSysContext db = new IOSysContext())
{
//条件
var q = this.GetSumOutPutWhere(db, familyID, req.StartDate, req.EndDate);
//统计
var list = await(from m in q
group m by m.AmountAccountID into g
select new SumListInfo <int>
{
name = g.Key,
value = g.Sum(m => m.Amount)
}).ToListAsync();
return(list);
}
}
/// <summary>
/// 拼接统计条件
/// </summary>
/// <param name="db">数据库上下文</param>
/// <param name="familyID">家庭ID</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">截止日期</param>
/// <returns></returns>
private IQueryable <OutPut> GetSumOutPutWhere(IOSysContext db, int familyID, DateTime?startDate, DateTime?endDate)
{
//家庭ID
var q = db.OutPuts.AsNoTracking().Where(m => m.FamilyID == familyID);
//开始日期
if (startDate.HasValue)
{
q = q.Where(m => m.OutDate >= startDate.Value);
}
//截止日期
if (endDate.HasValue)
{
q = q.Where(m => m.OutDate <= endDate.Value);
}
return(q);
}
/// <summary>
/// 按年度统计
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <param name="req">条件</param>
/// <returns></returns>
public async Task <List <SumListInfo <string> > > SumOutPutYearAsync(int familyID, OutSumReq req)
{
using (IOSysContext db = new IOSysContext())
{
//条件
var q = this.GetSumOutPutWhere(db, familyID, req.StartDate, req.EndDate);
//统计
var list = await(from m in q
group m by m.OutDate.Year into g
select new SumListInfo <string>()
{
name = CvtHelper.ToStr(g.Key),
value = g.Sum(m => m.Amount)
}).ToListAsync();
return(list);
}
}
/// <summary>
/// 获取最小流水日期
/// </summary>
/// <param name="familyID">家庭ID</param>
/// <returns></returns>
public async Task <DateTime> GetMinTurnoverDate(int familyID)
{
//判断缓存是否存在
var key = string.Format(SysConst.Cache.MinTurnoverDate_FamilyID, familyID);
var minDate = CacheHelper.Get <DateTime?>(key);
if (minDate != null)
{
return(minDate.Value);
}
using (IOSysContext db = new IOSysContext())
{
//收入
var taskIn = db.InComes.Where(m => m.FamilyID == familyID).OrderBy(m => m.InDate).Take(1).FirstOrDefaultAsync();
//支出
var taskOut = db.OutPuts.Where(m => m.FamilyID == familyID).OrderBy(m => m.OutDate).Take(1).FirstOrDefaultAsync();
//转账
var taskTransfer = db.AmountAccountTransfers.Where(m => m.FamilyID == familyID).OrderBy(m => m.TransferDate).Take(1).FirstOrDefaultAsync();
//借还
var taskBR = db.BorrowRepays.Where(m => m.FamilyID == familyID).OrderBy(m => m.BRDate).Take(1).FirstOrDefaultAsync();
minDate = DateTime.Today;
//收入
var income = await taskIn;
minDate = (income != null && minDate > income.InDate) ? income.InDate : minDate;
//支出
var output = await taskOut;
minDate = (output != null && minDate > output.OutDate) ? output.OutDate : minDate;
//转账
var transfer = await taskTransfer;
minDate = (transfer != null && minDate > transfer.TransferDate) ? transfer.TransferDate : minDate;
//借还
var br = await taskBR;
minDate = (br != null && minDate > br.BRDate) ? br.BRDate : minDate;
//写入缓存
CacheHelper.Set(key, minDate);
return(minDate ?? DateTime.Today);
}
}
