public static void RegisterOrUpdate(Users user, LoginInfo loginInfo) { using (var db = new DBExpenceContext()) { var userId = user.IdUser; //check if (db.LoginInfo.Any(li => li.LoginName == loginInfo.LoginName) && userId != -1) { throw new Exception("User with this login name already exists"); } if (db.Users.Any(e => e.IdUser == userId)) { loginInfo.IdUser = userId; db.Users.Attach(user); db.Entry(user).State = System.Data.Entity.EntityState.Modified; db.LoginInfo.Attach(loginInfo); db.Entry(loginInfo).State = System.Data.Entity.EntityState.Modified; } else { db.Users.Add(user); db.SaveChanges(); loginInfo.IdUser = user.IdUser; db.LoginInfo.Add(loginInfo); } db.SaveChanges(); } }
public static ExpenceTypes GetMostPopularCategory(int userId, DateTime dateFrom, DateTime dateTo) { if (GetDateOfLatestExpence(userId) < dateFrom) { throw new Exception(); } using (var db = new DBExpenceContext()) { var mostPopularCategoryId = db.Expences .Where(e => e.IdUser == userId && e.BillDate >= dateFrom && e.BillDate <= dateTo) .GroupBy(e => e.IdExpenceType) .OrderByDescending(e => e.Count()) .Select(g => g.Key) .First(); var query = (from t in db.ExpenceTypes where t.IdExpenceType == mostPopularCategoryId select t).Single(); return(query); } }
public static bool CheckIfExpencesEmpty(int userId) { using (var db = new DBExpenceContext()) { bool result = db.Expences.Any(e => e.IdUser == userId); return(result); } }
public static Users GetUserById(int userId) { using (var db = new DBExpenceContext()) { var query = (from u in db.Users where u.IdUser == userId select u).SingleOrDefault(); return(query); } }
public static DateTime GetDateOfLatestExpence(int userId) { using (var db = new DBExpenceContext()) { var query = (from e in db.Expences where e.IdUser == userId select e.BillDate).Max(); return(query); } }
public static int GetLoginIdByUserId(int userId) { using (var db = new DBExpenceContext()) { var query = (from li in db.LoginInfo where li.IdUser == userId select li).Single(); return(query.IdLoginInfo); } }
// For user login public static int GetUserId(string login, string password) { using (var db = new DBExpenceContext()) { var query = (from li in db.LoginInfo where li.Pass == password && li.LoginName == login select li).SingleOrDefault(); if (query == null) { throw new Exception(); } return(query.IdUser); } }
public static int GetExpenceTypeId(string text) { using (var db = new DBExpenceContext()) { var query = (from t in db.ExpenceTypes where text == t.Name select t).SingleOrDefault(); var type = query as ExpenceTypes; if (type == null) { throw new Exception(); } return(type.IdExpenceType); } }
public static Expences GetMostExpencive(int userId, DateTime dateFrom, DateTime dateTo) { if (dateFrom > dateTo) { throw new Exception(); } using (var db = new DBExpenceContext()) { var query = (from e in db.Expences where e.IdUser == userId && e.BillDate >= dateFrom && e.BillDate <= dateTo select e) .OrderByDescending(e => e.MonthAmount) .First(); return(query); } }
//Updates if record exists inserts if there is no such record public static void InsertExpence(Expences expence) { using (var db = new DBExpenceContext()) { var id = expence.IdExpence; if (db.Expences.Any(e => e.IdExpence == id)) { db.Expences.Attach(expence); db.Entry(expence).State = System.Data.Entity.EntityState.Modified; } else { db.Expences.Add(expence); } db.SaveChanges(); } }
public static List <Expences> GetFilteredListExpances(int userId, DateTime dateFrom, DateTime dateTo) { using (var db = new DBExpenceContext()) { var query = (from e in db.Expences where e.IdUser == userId && e.BillDate >= dateFrom && e.BillDate <= dateTo select e).ToList(); foreach (var el in query) { el.ExpenceTypes = (from t in db.ExpenceTypes where t.IdExpenceType == el.IdExpenceType select t).Single(); } return(query); } }
public static List <Expences> GetListExpances(int userId) { using (var db = new DBExpenceContext()) { var query = (from e in db.Expences where e.IdUser == userId select e).ToList(); foreach (var el in query) { el.ExpenceTypes = (from t in db.ExpenceTypes where t.IdExpenceType == el.IdExpenceType select t).Single(); } return(query); } }