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