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