public List <SumModel> GetWithTags(DateTime?FROM_DATE = null, DateTime?TO_DATE = null) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { List <SumModel> sums = this.Get(FROM_DATE, TO_DATE); ISumTagConnRepo stcRepo = new SumTagConnRepo(); List <SumTagConnModel> sumTagConn = stcRepo.Get_OrderBySumId_DepthTag(); foreach (SumModel sumItem in sums) { int i = 0; while (i < sumTagConn.Count && sumTagConn[i].SumId < sumItem.Id) { i++; } while (i < sumTagConn.Count && sumTagConn[i].SumId == sumItem.Id) { if (sumTagConn[i].Tag == null) { throw new Exception("Property tag shouldn't be null!"); } sumItem.Tags.Add(sumTagConn[i].Tag); sumTagConn.RemoveAt(i); // i is "increased" because RemoveAt } } return(sums); } }
public List <IntellisenseModel> GetOnLike(string like) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { List <IntellisenseModel> ret = (from d in context.Intellisense where (d.State == "Y" && d.Title.Contains(like)) orderby d.Id ascending select new IntellisenseModel() { Id = d.Id, Title = d.Title, SumTitle = d.SumTitle, SumDescription = d.SumDescription, SumSum = d.SumSum, SumInputDate = d.SumInputDate, SumAccountDate = d.SumAccountDate, SumDueDate = d.SumDueDate, Tags = null, CreateDate = d.CreateDate, CreateBy = d.CreateBy, ModifyDate = d.ModifyDate, ModifyBy = d.ModifyBy }).ToList(); ret = AssembleWithTags(ret); // Strip models to JSON conversion foreach (IntellisenseModel item in ret) { item.IntellisenseTagConn = null; } return(ret); } }
/// <summary> /// Includes: properties, TAG /// Ordered: SUM_ID ASC /// </summary> /// <param name="context"></param> /// <returns></returns> public List <SumTagConnModel> Get_OrderBySumId_DepthTag() { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { return((from d in context.SumTagConn orderby d.SumId ascending select new SumTagConnModel() { Id = d.Id, SumId = d.SumId, TagId = d.TagId, Tag = new TagModel() { Id = d.Tag.Id, Title = d.Tag.Title, Description = d.Tag.Description, Icon = d.Tag.Icon, QuickbarPlace = d.Tag.QuickbarPlace, CreateDate = d.Tag.CreateDate, CreateBy = d.Tag.CreateBy, ModifyDate = d.Tag.ModifyDate, ModifyBy = d.Tag.ModifyBy, State = d.Tag.State } }).ToList()); } }
public List <SumModel> Get(DateTime?FROM_DATE = null, DateTime?TO_DATE = null) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { return((from d in context.Sum where ( (d.State == "Y") && (FROM_DATE == null || d.InputDate >= FROM_DATE) && (TO_DATE == null || d.InputDate <= TO_DATE) ) orderby d.Id ascending select new SumModel() { Id = d.Id, Title = d.Title, Sum = d.Sum, InputDate = d.InputDate, AccountDate = d.AccountDate, DueDate = d.DueDate, CreateDate = d.CreateDate, CreateBy = d.CreateBy, ModifyDate = d.ModifyDate, ModifyBy = d.ModifyBy, State = d.State }).ToList()); } }
public void TmpCalc1() { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { var b = (from d in context.Dates join s in context.Sum on d.Date equals s.InputDate into ds select new { Date = d.Date, ItemCount = ds.ToList().Count //Items = (from item in ds // select new { // Title = item.Title, // Sum = item.Sum // }) }); } }
public TagModel Save(TagModel TAG) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { TagModel tag = context.Tag.Where(x => x.Id == TAG.Id).FirstOrDefault(); DateTime now = DateTime.Now; if (tag == null) { tag = new TagModel() { Id = TAG.Id, Title = TAG.Title, Description = TAG.Description, Icon = TAG.Icon, QuickbarPlace = TAG.QuickbarPlace, CreateDate = now, CreateBy = tmpUserId, ModifyDate = now, ModifyBy = tmpUserId, State = "Y" }; context.Tag.Add(tag); } else { tag.Title = TAG.Title; tag.Description = TAG.Description; tag.Icon = TAG.Icon; tag.QuickbarPlace = TAG.QuickbarPlace; tag.ModifyDate = now; tag.ModifyBy = tmpUserId; } if (context.SaveChanges() >= 1) { return(TAG); } else { return(null); } } }
public bool Delete(int ID) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { IntellisenseModel model = context.Intellisense.Where(x => x.Id == ID).FirstOrDefault(); if (model != null) { DateTime now = DateTime.Now; model.ModifyBy = tmpUserId; model.ModifyDate = now; model.State = "N"; context.SaveChanges(); return(true); } else { throw new Exception("Record not found for deletion."); } } }
public IntellisenseModel Save(IntellisenseModel input) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { IntellisenseModel model = context.Intellisense.Where(x => x.Id == input.Id).FirstOrDefault(); DateTime now = DateTime.Now; if (model == null) { model = new IntellisenseModel() { Title = input.Title, SumSum = input.SumSum, SumAccountDate = input.SumAccountDate, SumInputDate = input.SumInputDate, SumDueDate = input.SumDueDate, CreateDate = now, CreateBy = tmpUserId, ModifyDate = now, ModifyBy = tmpUserId, State = "Y" }; context.Intellisense.Add(model); } else { model.Title = input.Title; model.SumSum = input.SumSum; model.ModifyDate = now; model.ModifyBy = tmpUserId; } if (context.SaveChanges() >= 1) { return(model); } else { return(null); } } }
public List <TagModel> Get() { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { return((from d in context.Tag where d.State == "Y" orderby d.Id ascending select new TagModel() { Id = d.Id, Title = d.Title, Description = d.Description, Icon = d.Icon, QuickbarPlace = d.QuickbarPlace, CreateDate = d.CreateDate, CreateBy = d.CreateBy, ModifyDate = d.ModifyDate, ModifyBy = d.ModifyBy, State = d.State }).ToList()); } }
// from, to // month1 // tag1 income expense flow prevMonthDelta with up/down icon prevMonth and thisMonth átlDelta with u/d-i // tag2 income expense flow prevMonthDelta with up/down icon prevMonth and thisMonth átlDelta with u/d-i // month2 // ... #region MonthlySumups // from, to // 2016, december income expense flow cumulatedFlow up/down icon // 2017, january income expense flow cumulatedFlow up/down icon // 2017, february income expense flow cumulatedFlow up/down icon // ... public List <MonthlyResult> MonthlySumups(int FROM_YEAR, int FROM_MONTH, int TO_YEAR, int TO_MONTH, bool fakeData) { DateTime fromDate = new DateTime(FROM_YEAR, FROM_MONTH, 1); DateTime toDate = new DateTime(TO_YEAR, TO_MONTH, 1); toDate = toDate.AddMonths(1); List <SumModel> sums = new List <SumModel>(); using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { sums = (from d in context.Sum where ( (d.State == "Y") && d.InputDate >= fromDate && d.InputDate < toDate ) orderby d.InputDate ascending select new SumModel() { Id = d.Id, Title = d.Title, Sum = d.Sum, InputDate = d.InputDate }).ToList(); } List <MonthlyResult> ret = new List <MonthlyResult>(); int prevYear = -1; int prevMonth = -1; int id = 1; decimal cumulatedFlow = 0; // starts with 0 decimal income = 0; decimal expense = 0; ret.Add(new MonthlyResult()); foreach (SumModel sum in sums) { int thisYear = sum.InputDate.Value.Year; int thisMonth = sum.InputDate.Value.Month; if (fakeData) { sum.Sum = CalculationRepo.RND.Next(-150000, 150000); } if (prevYear != -1 && prevMonth != -1) { if (thisMonth != prevMonth || thisYear != prevYear) // new month { // Set last month this.SetMonthlyResult(ret[ret.Count - 1], id++, prevYear, prevMonth, expense, income, cumulatedFlow); // Add new month ret.Add(new MonthlyResult()); // If month(s) are missing // 2010-10 -> 2011-03 => add 11, 12, 1, 2 // || // 2010-09 -> 2010-12 => 10, 11 if ((thisYear != prevYear && prevMonth - thisMonth != 11) || (thisYear == prevYear && thisMonth - prevMonth != 1)) { while ((thisYear != prevYear && prevMonth - thisMonth != 11) || (thisYear == prevYear && thisMonth - prevMonth != 1)) { // Increase date (by 1 month) if (prevMonth == 12) { prevMonth = 1; prevYear++; } else { prevMonth++; } // Set last dummy month this.SetMonthlyResult(ret[ret.Count - 1], id++, prevYear, prevMonth, 0, 0, cumulatedFlow); // Add new month ret.Add(new MonthlyResult()); } } // Reset income = 0; expense = 0; } } // Set prevYear = thisYear; prevMonth = thisMonth; // Add SumModel ret[ret.Count - 1].sums.Add(sum); // Add up if (sum.Sum < 0) { expense += Math.Abs(sum.Sum.Value); } else { income += sum.Sum.Value; } cumulatedFlow += sum.Sum.Value; } // Set last month this.SetMonthlyResult(ret[ret.Count - 1], id, prevYear, prevMonth, expense, income, cumulatedFlow); return(ret); }
// a TagTotalResult-on lehet javítani. // (1) Ne az egész időszak napjaira és hónapjaira ossza le, hanem első megjelenésétől kezdve utolsó megjelenéséig. // (2) fix és monthly tageknél pontosan tudjuk az összeget és hogy havonta fordul elő. Pontosabb perDay, perMonth értékeket tudunk adni. // (3) monthly tagnél a kimaradt hónapokat kihagyhatjuk a monthCount-ból #endregion #region TagTotalResult // from, to // tag1 income expense flow átl/month // tag2 income expense flow átl/month // ... public List <TagTotalResult> TagTotalResult(int FROM_YEAR, int FROM_MONTH, int FROM_DAY, int TO_YEAR, int TO_MONTH, int TO_DAY, bool fakeData) { DateTime FROM = new DateTime(FROM_YEAR, FROM_MONTH, FROM_DAY); DateTime TO = new DateTime(TO_YEAR, TO_MONTH, TO_DAY); List <TagTotalResult> ret = new List <TagTotalResult>(); using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { List <SumTagConnModel> sumTagConns = (from d in context.SumTagConn orderby d.TagId ascending select new SumTagConnModel() { Id = d.Id, SumId = d.SumId, TagId = d.TagId, Tag = (from f in context.Tag where f.Id == d.TagId select new TagModel() { Id = f.Id, Title = f.Title, Description = f.Description, Icon = f.Icon, State = f.State }).Single(), Sum = (from g in context.Sum where g.Id == d.SumId select new SumModel() { Id = g.Id, Title = g.Title, Sum = g.Sum, InputDate = g.InputDate, State = g.State }).Single() }).ToList(); decimal prevTagId = 0; decimal income = 0; decimal expense = 0; decimal dayDiff = (decimal)(TO - FROM).TotalDays; decimal monthDiff = ((TO.Year - FROM.Year) * 12) + (TO.Month - FROM.Month); // TODO ellenőrizni, tényleg jó-e decimal monthDiffAvg = Math.Floor(dayDiff / 30m); TagModel lastTag = null; ret.Add(new TagTotalResult()); foreach (SumTagConnModel sumTagConn in sumTagConns) { if (sumTagConn.Tag.State != "Y" || sumTagConn.Sum.State != "Y" || sumTagConn.Sum.InputDate < FROM || sumTagConn.Sum.InputDate >= TO) { continue; } decimal thisTagId = sumTagConn.TagId; // New tag if (prevTagId > 0 && thisTagId != prevTagId) { this.SetTagTotalResult(ret[ret.Count - 1], lastTag, income, expense, dayDiff, monthDiff, monthDiffAvg); // Set next tag ret.Add(new TagTotalResult()); // Reset properties income = 0; expense = 0; } // Fake data if (fakeData) { sumTagConn.Sum.Sum = CalculationRepo.RND.Next(-150000, 150000); } // Cumulate props for next tag if (sumTagConn.Sum.Sum > 0) { income += sumTagConn.Sum.Sum.Value; } else if (sumTagConn.Sum.Sum < 0) { expense += Math.Abs(sumTagConn.Sum.Sum.Value); } // Set locals prevTagId = thisTagId; lastTag = sumTagConn.Tag; } // last one this.SetTagTotalResult(ret[ret.Count - 1], lastTag, income, expense, dayDiff, monthDiff, monthDiffAvg); } return(ret); }
public SumModel SaveGetSum(SumModel p_sum) { using (DBSHYMONEYV1Context context = new DBSHYMONEYV1Context()) { SumModel sum = context.Sum.Where(x => x.Id == p_sum.Id).FirstOrDefault(); DateTime now = DateTime.Now; if (sum == null) { sum = new SumModel() { Title = p_sum.Title, Sum = p_sum.Sum, AccountDate = p_sum.AccountDate, InputDate = p_sum.InputDate, DueDate = p_sum.DueDate, CreateDate = now, CreateBy = tmpUserId, ModifyDate = now, ModifyBy = tmpUserId, State = "Y", IsPayed = "Y" }; this.ResolveDateTypeDefaults(sum, p_sum); context.Sum.Add(sum); } else { List <SumTagConnModel> stcList = context.SumTagConn.Where(x => x.SumId == sum.Id).ToList(); sum.Title = p_sum.Title; sum.Sum = p_sum.Sum; sum.ModifyDate = now; sum.ModifyBy = tmpUserId; this.ResolveDateTypeDefaults(sum, p_sum); // remove old tag connections if (stcList.Count > 0) { context.SumTagConn.RemoveRange(stcList); } } // TODO Valamiért elrontja, ha vannak tagek // add tag connections if (p_sum.Tags != null) { foreach (TagModel tag in p_sum.Tags) { context.SumTagConn.Add(new SumTagConnModel() { SumId = sum.Id, TagId = tag.Id }); } } if (context.SaveChanges() >= 1) { return(sum); } else { return(null); } } }