public CPersonDto FindItemByKeyFullLoad(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
System.Data.Entity.Infrastructure.DbQuery <CPersonDto> query = context.CPeopleDto
.Include("Reminders")
.Include("PersonalTargets")
.Include("SpendingHistories")
.Include("SubCategories")
.Include("SubCategories.Payments")
.Include("LoginData")
.Include("Family.FamilyTargets")
.Include("Family.People.Reminders")
.Include("Family.People.PersonalTargets")
.Include("Family.People.SpendingHistories")
.Include("Family.People.SubCategories")
.Include("Family.People.SubCategories.Payments")
.Include("Family.People.LoginData");
CPersonDto person = query.FirstOrDefault(p => p.PersonID == key);
return(person);
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find Person (id = {0}) in DB. Message: {1}", key, ex.Message);
return(null);
}
}
public bool UpdateItem(CPersonalTargetDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPersonalTargetDto target = context.CPersonalTargetsDto.Where(t => t.PersonID == item.PersonID &&
t.CategoryID == item.CategoryID &&
t.Month == item.Month)
.FirstOrDefault();
if (target == null)
{
log.Info("Can't update CPersonalTarget because it doesn't exist in database (PersonId = {0}, categoryId = {1}, date = {2})",
item.PersonID, item.CategoryID, item.Month);
return(false);
}
target.Type = item.Type;
target.Amount = item.Amount;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update CPersonalTarget (PersonId = {1}, categoryId = {2}, date = {3}). Message: {0}",
ex.Message, item.PersonID, item.CategoryID, item.Month);
return(false);
}
return(true);
}
public bool DeleteItemByKey(CReminderKey key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CReminderDto reminder = context.CRemindersDto.Where(r => r.PersonID == key.PersonId && r.Title.Equals(key.Title)).FirstOrDefault();
if (reminder == null)
{
log.Info("Can't delete reminder because it doesn't exist in database id = {0}, Title: {1}", key.PersonId, key.Title);
return(false);
}
context.CRemindersDto.Remove(reminder);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete CReminders (id = {1} title: {2}). Message: {0}", ex.Message, key.PersonId, key.Title);
return(false);
}
return(true);
}
public bool DeleteItemByKey(CPersonalTargetKey key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPersonalTargetDto target = context.CPersonalTargetsDto.Where(t => t.PersonID == key.PersonId &&
t.CategoryID == key.CategoryId &&
t.Month == key.Month)
.FirstOrDefault();
if (target == null)
{
log.Info("Can't delete CPersonalTarget because it doesn't exist in database (PersonId = {0}, categoryId = {1}, date = {2})",
key.PersonId, key.CategoryId, key.Month);
return(false);
}
context.CPersonalTargetsDto.Remove(target);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete CPersonalTargets (PersonId = {1}, categoryId = {2}, date = {3}). Message: {0}",
ex.Message, key.PersonId, key.CategoryId, key.Month);
return(false);
}
return(true);
}
public bool UpdateItem(CPersonDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPersonDto person = context.CPeopleDto.Where(p => p.PersonID == item.PersonID).FirstOrDefault();
if (person == null)
{
log.Info("Can't update person data because it doesn't exist in database (personId = {0})", item.PersonID);
return(false);
}
person.Name = item.Name;
person.FamilyID = item.FamilyID;
person.Budget = item.Budget;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update person in DB (PersonId = {1}). Message: {0}", ex.Message, item.PersonID);
return(false);
}
return(true);
}
public bool UpdateItem(CFamilyDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CFamilyDto family = context.CFamiliesDto.Where(f => f.FamilyID == item.FamilyID).FirstOrDefault();
if (family == null)
{
log.Info("Can't update family because it doesn't exist in database (familyId = {0})", item.FamilyID);
return(false);
}
family.FamilyName = item.FamilyName;
family.Budget = item.Budget;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update family in BD (FamilyId = {1}). Message: {0}", ex.Message, item.FamilyID);
return(false);
}
return(true);
}
public Boolean LeaveFamily(Int32 personId)
{
try
{
CPersonRepository personRep = new CPersonRepository();
CPersonDto personDto = personRep.FindItemByKey(personId);
if (personDto.FamilyID == null)
{
log.Warn("Person (id = {1}) can't leave family because he doesn't have one.", personId);
return(false);
}
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
System.Data.SqlClient.SqlParameter personIdParam = new System.Data.SqlClient.SqlParameter("@personId", personId);
context.Database.ExecuteSqlCommand("LeaveFamilyProc @personId", personIdParam);
}
return(true);
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to leave family in DB. Message: {0}", ex.Message);
return(false);
}
}
public bool DeleteItemByKey(Int32 key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPaymentDto payment = context.CPaymentsDto.Where(p => p.PaymentID == key).FirstOrDefault();
if (payment == null)
{
log.Info("Can't update payment because it doesn't exist in database (paymentId = {0})", key);
return(false);
}
context.CPaymentsDto.Remove(payment);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete payment (paymentId = {1}). Message: {0}", ex.Message, key);
return(false);
}
return(true);
}
public Boolean TryDeleteSubCategory(Int32 personId, Int32 subCategoryId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CSubCategoryDto subCategoryDto = context.CSubCategoriesDto.Where(s => s.SubCategoryID == subCategoryId).FirstOrDefault();
if (subCategoryDto == null)
{
log.Warn("Can't find subCategory (id = {0}) to delete)", subCategoryId);
return(false);
}
if (subCategoryDto.PersonID != personId)
{
log.Warn("Can't delete subCategory because person (id = {0}) not an owner of subCategory (id = {1})", personId, subCategoryId);
return(false);
}
}
return(DeleteItemByKey(subCategoryId));
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to edit subCategory (id = {1}) into DB. Message: {0}", ex.Message, subCategoryId);
return(false);
}
}
public Boolean JoinFamily(Int32 personId, Int32 familyId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CFamilyDto familyDto = context.CFamiliesDto.Where(f => f.FamilyID == familyId).FirstOrDefault();
if (familyDto == null)
{
log.Warn("Person (id = {0}) cant join to the family (id = {1}) because family doesn't exist", personId, familyId);
return(false);
}
CPersonDto personDto = context.CPeopleDto.Where(p => p.PersonID == personId).FirstOrDefault();
if (personDto == null)
{
log.Warn("Person (id = {0}) cant join to the family (id = {1}) because person doesn't exist", personId, familyId);
return(false);
}
personDto.Family = familyDto;
familyDto.MembersCounter++;
context.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying person (id={1}) to join to family (id={2}) in DB. Message: {0}", ex.Message, personId, familyId);
return(false);
}
}
public bool UpdateItem(CLoginDataDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CLoginDataDto loginData = context.CLoginDatasDto.Where(l => l.PersonID == item.PersonID).FirstOrDefault();
if (loginData == null)
{
log.Info("Can't update loginData because it doesn't exist in database (personId = {0})", item.PersonID);
return(false);
}
loginData.email = item.email;
loginData.Username = item.Username;
loginData.PasswordHash = item.PasswordHash;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update loginData (PersonId = {1}). Message: {0}", ex.Message, item.PersonID);
return(false);
}
return(true);
}
public bool UpdateItem(CPaymentDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPaymentDto payment = context.CPaymentsDto.Where(p => p.PaymentID == item.PaymentID).FirstOrDefault();
if (payment == null)
{
log.Info("Can't update payment because it doesn't exist in database (paymentId = {0})", item.PaymentID);
return(false);
}
payment.SubCategoryID = item.SubCategoryID;
payment.Date = item.Date;
payment.Spended = item.Spended;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update payment (paymentId = {1}). Message: {0}", ex.Message, item.PaymentID);
return(false);
}
return(true);
}
public bool UpdateItem(CReminderDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CReminderDto reminder = context.CRemindersDto.Where(r => r.PersonID == item.PersonID && r.Title.Equals(item.Title)).FirstOrDefault();
if (reminder == null)
{
log.Info("Can't update reminder because it doesn't exist in database id = {0}, Title: {1}", item.PersonID, item.Title);
return(false);
}
reminder.DayOfMonth = item.DayOfMonth;
reminder.Description = item.Description;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update CReminders (id = {1} title: {2}). Message: {0}", ex.Message, item.PersonID, item.Title);
return(false);
}
return(true);
}
public bool UpdateItem(CSpendingHistoryDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CSpendingHistoryDto spendingHistory = context.CSpendingHistoriesDto.Where(h => h.PersonID == item.PersonID &&
h.CategoryID == item.CategoryID &&
h.Month == item.Month)
.FirstOrDefault();
if (spendingHistory == null)
{
log.Info("Can't update SpendingHistory because it doesn't exist in database (personId = {0}, categoryId = {1}, Month: {2})",
item.PersonID, item.CategoryID, item.Month);
return(false);
}
spendingHistory.Spended = item.Spended;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update SpendingHistory (personId = {1}, categoryId = {2}, Month: {3}). Message: {0}",
ex.Message, item.PersonID, item.CategoryID, item.Month);
return(false);
}
return(true);
}
public bool DeleteItemByKey(CSpendingHistoryKey key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CSpendingHistoryDto spendingHistory = context.CSpendingHistoriesDto.Where(h => h.PersonID == key.PersonId &&
h.CategoryID == key.CategoryId &&
h.Month == key.Month)
.FirstOrDefault();
if (spendingHistory == null)
{
log.Info("Can't Delete SpendingHistory because it doesn't exist in database (personId = {0}, categoryId = {1}, Month: {2})",
key.PersonId, key.CategoryId, key.Month);
return(false);
}
context.CSpendingHistoriesDto.Remove(spendingHistory);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete SpendingHistory (personId = {1}, categoryId = {2}, Month: {3}). Message: {0}",
ex.Message, key.PersonId, key.CategoryId, key.Month);
return(false);
}
return(true);
}
public Boolean DeleteItemByKey(Int32 key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CSubCategoryDto subCategory = context.CSubCategoriesDto.Where(s => s.SubCategoryID == key).FirstOrDefault();
if (subCategory == null)
{
log.Info("Can't delete subCategory because it doesn't exist in database (SubCategoryid = {0})", key);
return(false);
}
context.CSubCategoriesDto.Remove(subCategory);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete subCategory (SubCategoryId = {1}). Message: {0}", ex.Message, key);
return(false);
}
return(true);
}
public Boolean UpdateItem(CSubCategoryDto item)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CSubCategoryDto subCategory = context.CSubCategoriesDto.Where(s => s.SubCategoryID == item.SubCategoryID).FirstOrDefault();
if (subCategory == null)
{
log.Info("Can't update subCategory because it doesn't exist in database (SubCategoryid = {0})", item.SubCategoryID);
return(false);
}
subCategory.ParentCategoryID = item.ParentCategoryID;
subCategory.Title = item.Title;
subCategory.Description = item.Description;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to update subCategory (SubCategoryid = {1}). Message: {0}", ex.Message, item.SubCategoryID);
return(false);
}
return(true);
}
public bool DeleteItemByKey(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CFamilyDto family = context.CFamiliesDto.Where(f => f.FamilyID == key).FirstOrDefault();
if (family == null)
{
log.Info("Can't delete family because it doesn't exist in database (familyId = {0})", key);
return(false);
}
context.CFamiliesDto.Remove(family);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete family (FamilyId = {1}). Message: {0}", ex.Message, key);
return(false);
}
return(true);
}
public bool DeleteItemByKey(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPersonDto person = context.CPeopleDto.Where(p => p.PersonID == key).FirstOrDefault();
if (person == null)
{
log.Info("Can't delete person data because it doesn't exist in database (personId = {0})", key);
return(false);
}
context.CPeopleDto.Remove(person);
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to delete person data from DB (PersonId = {1}). Message: {0}", ex.Message, key);
return(false);
}
return(true);
}
//Данный метод искуственно сделан через двойной context.SaveChanges, что бы выполнить задание - 2 изменения в одной транзацкии.
public Boolean CreateFamily(Int32 personId, String familyName, Int32 budget)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
CPersonDto personDto = context.CPeopleDto.Where(p => p.PersonID == personId).FirstOrDefault();
if (personDto == null)
{
log.Warn("Can't create new family because person (id = {0}) doesn't exist", personId);
return(false);
}
if (personDto.FamilyID != null)
{
log.Warn("Can't create new family because person (id = {0}) already has family!", personId);
return(false);
}
using (System.Data.Entity.DbContextTransaction transaction = context.Database.BeginTransaction())
{
try
{
CFamilyDto familyDto = new CFamilyDto();
familyDto.FamilyName = familyName;
familyDto.Budget = budget;
familyDto.MembersCounter = 1;
context.CFamiliesDto.Add(familyDto);
context.SaveChanges();
Int32 familyId = familyDto.FamilyID;
personDto.FamilyID = familyId;
context.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
log.Error(ex, "There is an error in transaction when created new family. Message: {0}", ex.Message);
transaction.Rollback();
return(false);
}
}
}
return(true);
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to create new family in DB. Message: {0}", ex.Message);
return(false);
}
}
public CSubCategoryDto FindItemByKey(Int32 key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CSubCategoriesDto.Where(s => s.SubCategoryID == key).FirstOrDefault());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find subCategory (SubCategoryId = {1}). Message: {0}", ex.Message, key);
return(null);
}
}
public CFamilyDto FindItemByKey(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CFamiliesDto.Where(f => f.FamilyID == key).FirstOrDefault());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find family (FamilyId = {1}). Message: {0}", ex.Message, key);
return(null);
}
}
public ICollection <CCategoryDto> GetAllItems()
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CCategoriesDto.ToList());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to get all Categories from DB. Message: {0}", ex.Message);
return(new List <CCategoryDto>());
}
}
public ICollection <CFamilyDto> GetAllItems()
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CFamiliesDto.ToList());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to get all families. Message: {0}", ex.Message);
throw;
}
}
public CLoginDataDto FindItemByKey(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CLoginDatasDto.Where(l => l.PersonID == key).FirstOrDefault());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find loginData (PersonId = {1}). Message: {0}", ex.Message, key);
return(null);
}
}
public CPaymentDto FindItemByKey(Int32 key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
return(context.CPaymentsDto.Where(l => l.PaymentID == key).FirstOrDefault());
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find payment (paymentId = {1}). Message: {0}", ex.Message, key);
return(null);
}
}
public CLoginDataDto FindLoginDataByUsername(string username)
{
try
{
SpendingHelperDBEntities connection = CSConnectionPool.GetConnection();
CLoginDataDto loginDto = connection.CLoginDatasDto.FirstOrDefault(l => l.Username.Equals(username));
return(loginDto);
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to FindLoginDataByUsername. Message: {0}", ex.Message);
return(null);
}
}
public ICollection <CPersonalTargetDto> FindByPersonId(int personId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
return(context.CPersonalTargetsDto.Where(t => t.PersonID == personId).ToList());
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find person's (id = {0}) personal targets in DB. Message: {1}", personId, ex.Message);
return(null);
}
}
public CFamilyDto FindByPersonId(int personId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
return(context.CPeopleDto.Where(p => p.PersonID == personId).FirstOrDefault().Family);
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find family by personId (id = {0}) in DB. Message: {1}", personId, ex.Message);
return(null);
}
}
public ICollection <CPaymentDto> FindBySubCategoryId(Int32 subCategoryId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
return(context.CPaymentsDto.Where(p => p.SubCategoryID == subCategoryId).ToList());
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find subCategory's (id = {0}) payments in DB. Message: {1}", subCategoryId, ex.Message);
return(null);
}
}
