public PhonebookDocument GetPhonebookDocument(int id)
{
PhonebookDocument PhonebookDocument = null;
string queryString =
selectString +
"WHERE Id = @Id;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = queryString;
command.Parameters.Add(new SqlParameter("@Id", id));
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
PhonebookDocument = Read(reader);
}
}
}
return(PhonebookDocument);
}
public static PhonebookDocumentViewModel ConvertToPhonebookDocumentViewModel(this PhonebookDocument PhonebookDocument)
{
PhonebookDocumentViewModel PhonebookDocumentViewModel = new PhonebookDocumentViewModel()
{
Id = PhonebookDocument.Id,
Identifier = PhonebookDocument.Identifier,
Phonebook = PhonebookDocument.Phonebook?.ConvertToPhonebookViewModelLite(),
Name = PhonebookDocument.Name,
CreateDate = PhonebookDocument.CreateDate,
Path = PhonebookDocument.Path,
ItemStatus = PhonebookDocument.ItemStatus,
IsActive = PhonebookDocument.Active,
CreatedBy = PhonebookDocument.CreatedBy?.ConvertToUserViewModelLite(),
Company = PhonebookDocument.Company?.ConvertToCompanyViewModelLite(),
UpdatedAt = PhonebookDocument.UpdatedAt,
CreatedAt = PhonebookDocument.CreatedAt,
};
return(PhonebookDocumentViewModel);
}
private static PhonebookDocument Read(SqlDataReader reader)
{
PhonebookDocument PhonebookDocument = new PhonebookDocument();
PhonebookDocument.Id = Int32.Parse(reader["PhonebookDocumentId"].ToString());
PhonebookDocument.Identifier = Guid.Parse(reader["PhonebookDocumentIdentifier"].ToString());
if (reader["PhonebookId"] != DBNull.Value)
{
PhonebookDocument.Phonebook = new Phonebook();
PhonebookDocument.PhonebookId = Int32.Parse(reader["PhonebookId"].ToString());
PhonebookDocument.Phonebook.Id = Int32.Parse(reader["PhonebookId"].ToString());
PhonebookDocument.Phonebook.Identifier = Guid.Parse(reader["PhonebookIdentifier"].ToString());
PhonebookDocument.Phonebook.Code = reader["PhonebookCode"].ToString();
PhonebookDocument.Phonebook.Name = reader["PhonebookName"].ToString();
}
if (reader["Name"] != DBNull.Value)
{
PhonebookDocument.Name = reader["Name"].ToString();
}
if (reader["CreateDate"] != DBNull.Value)
{
PhonebookDocument.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
}
if (reader["Path"] != DBNull.Value)
{
PhonebookDocument.Path = reader["Path"].ToString();
}
if (reader["ItemStatus"] != DBNull.Value)
{
PhonebookDocument.ItemStatus = Int32.Parse(reader["ItemStatus"].ToString());
}
PhonebookDocument.Active = bool.Parse(reader["Active"].ToString());
PhonebookDocument.UpdatedAt = DateTime.Parse(reader["UpdatedAt"].ToString());
if (reader["CreatedById"] != DBNull.Value)
{
PhonebookDocument.CreatedBy = new User();
PhonebookDocument.CreatedById = Int32.Parse(reader["CreatedById"].ToString());
PhonebookDocument.CreatedBy.Id = Int32.Parse(reader["CreatedById"].ToString());
PhonebookDocument.CreatedBy.FirstName = reader["CreatedByFirstName"]?.ToString();
PhonebookDocument.CreatedBy.LastName = reader["CreatedByLastName"]?.ToString();
}
if (reader["CompanyId"] != DBNull.Value)
{
PhonebookDocument.Company = new Company();
PhonebookDocument.CompanyId = Int32.Parse(reader["CompanyId"].ToString());
PhonebookDocument.Company.Id = Int32.Parse(reader["CompanyId"].ToString());
PhonebookDocument.Company.Name = reader["CompanyName"].ToString();
}
return(PhonebookDocument);
}
public static List <PhonebookDocumentViewModel> ConvertToPhonebookDocumentViewModelList(this IEnumerable <PhonebookDocument> PhonebookDocuments)
{
List <PhonebookDocumentViewModel> PhonebookDocumentViewModels = new List <PhonebookDocumentViewModel>();
foreach (PhonebookDocument PhonebookDocument in PhonebookDocuments)
{
PhonebookDocumentViewModels.Add(PhonebookDocument.ConvertToPhonebookDocumentViewModel());
}
return(PhonebookDocumentViewModels);
}
public PhonebookDocument Delete(Guid identifier)
{
PhonebookDocument dbEntry = context.PhonebookDocuments
.Union(context.ChangeTracker.Entries()
.Where(x => x.State == EntityState.Added && x.Entity.GetType() == typeof(PhonebookDocument))
.Select(x => x.Entity as PhonebookDocument))
.FirstOrDefault(x => x.Identifier == identifier && x.Active == true);
if (dbEntry != null)
{
dbEntry.Active = false;
dbEntry.UpdatedAt = DateTime.Now;
}
return(dbEntry);
}
public PhonebookDocument Create(PhonebookDocument PhonebookDocument)
{
if (context.PhonebookDocuments.Where(x => x.Identifier != null && x.Identifier == PhonebookDocument.Identifier).Count() == 0)
{
PhonebookDocument.Id = 0;
PhonebookDocument.Active = true;
PhonebookDocument.UpdatedAt = DateTime.Now;
PhonebookDocument.CreatedAt = DateTime.Now;
context.PhonebookDocuments.Add(PhonebookDocument);
return(PhonebookDocument);
}
else
{
// Load PhonebookDocument that will be updated
PhonebookDocument dbEntry = context.PhonebookDocuments
.FirstOrDefault(x => x.Identifier == PhonebookDocument.Identifier && x.Active == true);
if (dbEntry != null)
{
dbEntry.PhonebookId = PhonebookDocument.PhonebookId ?? null;
dbEntry.CompanyId = PhonebookDocument.CompanyId ?? null;
dbEntry.CreatedById = PhonebookDocument.CreatedById ?? null;
// Set properties
dbEntry.Name = PhonebookDocument.Name;
dbEntry.CreateDate = PhonebookDocument.CreateDate;
dbEntry.Path = PhonebookDocument.Path;
dbEntry.ItemStatus = PhonebookDocument.ItemStatus;
// Set timestamp
dbEntry.UpdatedAt = DateTime.Now;
}
return(dbEntry);
}
}
public static PhonebookDocument ConvertToPhonebookDocument(this PhonebookDocumentViewModel PhonebookDocumentViewModel)
{
PhonebookDocument PhonebookDocument = new PhonebookDocument()
{
Id = PhonebookDocumentViewModel.Id,
Identifier = PhonebookDocumentViewModel.Identifier,
PhonebookId = PhonebookDocumentViewModel.Phonebook?.Id ?? null,
Name = PhonebookDocumentViewModel.Name,
CreateDate = PhonebookDocumentViewModel.CreateDate,
Path = PhonebookDocumentViewModel.Path,
ItemStatus = PhonebookDocumentViewModel.ItemStatus,
Active = PhonebookDocumentViewModel.IsActive,
CreatedById = PhonebookDocumentViewModel.CreatedBy?.Id ?? null,
CompanyId = PhonebookDocumentViewModel.Company?.Id ?? null,
UpdatedAt = PhonebookDocumentViewModel.UpdatedAt,
CreatedAt = PhonebookDocumentViewModel.CreatedAt,
};
return(PhonebookDocument);
}
public PhonebookResponse Create(PhonebookViewModel re)
{
PhonebookResponse response = new PhonebookResponse();
try
{
// Backup notes
List <PhonebookNoteViewModel> PhonebookNotes = re.PhonebookNotes?.ToList();
re.PhonebookNotes = null;
// Backup documents
List <PhonebookDocumentViewModel> PhonebookDocuments = re.PhonebookDocuments?.ToList();
re.PhonebookDocuments = null;
// Backup phone
List <PhonebookPhoneViewModel> PhonebookPhones = re.PhonebookPhones?.ToList();
re.PhonebookPhones = null;
Phonebook createdPhonebook = unitOfWork.GetPhonebookRepository().Create(re.ConvertToPhonebook());
// Update notes
if (PhonebookNotes != null && PhonebookNotes.Count > 0)
{
// Items for create or update
foreach (var PhonebookNote in PhonebookNotes
.Where(x => x.ItemStatus == ItemStatus.Added || x.ItemStatus == ItemStatus.Edited)?.ToList() ?? new List <PhonebookNoteViewModel>())
{
PhonebookNote.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
PhonebookNote.ItemStatus = ItemStatus.Submited;
PhonebookNote createdPhonebookNote = unitOfWork.GetPhonebookNoteRepository()
.Create(PhonebookNote.ConvertToPhonebookNote());
}
foreach (var item in PhonebookNotes
.Where(x => x.ItemStatus == ItemStatus.Deleted)?.ToList() ?? new List <PhonebookNoteViewModel>())
{
item.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
unitOfWork.GetPhonebookNoteRepository().Create(item.ConvertToPhonebookNote());
unitOfWork.GetPhonebookNoteRepository().Delete(item.Identifier);
}
}
// Update documents
if (PhonebookDocuments != null && PhonebookDocuments.Count > 0)
{
// Items for create or update
foreach (var PhonebookDocument in PhonebookDocuments
.Where(x => x.ItemStatus == ItemStatus.Added || x.ItemStatus == ItemStatus.Edited)?.ToList() ?? new List <PhonebookDocumentViewModel>())
{
PhonebookDocument.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
PhonebookDocument.ItemStatus = ItemStatus.Submited;
PhonebookDocument createdPhonebookDocument = unitOfWork.GetPhonebookDocumentRepository()
.Create(PhonebookDocument.ConvertToPhonebookDocument());
}
foreach (var item in PhonebookDocuments
.Where(x => x.ItemStatus == ItemStatus.Deleted)?.ToList() ?? new List <PhonebookDocumentViewModel>())
{
item.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
unitOfWork.GetPhonebookDocumentRepository().Create(item.ConvertToPhonebookDocument());
unitOfWork.GetPhonebookDocumentRepository().Delete(item.Identifier);
}
}
// Update Phones
if (PhonebookPhones != null && PhonebookPhones.Count > 0)
{
// Items for create or update
foreach (var PhonebookPhone in PhonebookPhones
.Where(x => x.ItemStatus == ItemStatus.Added || x.ItemStatus == ItemStatus.Edited)?.ToList() ?? new List <PhonebookPhoneViewModel>())
{
PhonebookPhone.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
PhonebookPhone.ItemStatus = ItemStatus.Submited;
PhonebookPhone createdPhonebookPhone = unitOfWork.GetPhonebookPhoneRepository()
.Create(PhonebookPhone.ConvertToPhonebookPhone());
}
foreach (var item in PhonebookPhones
.Where(x => x.ItemStatus == ItemStatus.Deleted)?.ToList() ?? new List <PhonebookPhoneViewModel>())
{
item.Phonebook = new PhonebookViewModel()
{
Id = createdPhonebook.Id
};
unitOfWork.GetPhonebookPhoneRepository().Create(item.ConvertToPhonebookPhone());
unitOfWork.GetPhonebookPhoneRepository().Delete(item.Identifier);
}
}
unitOfWork.Save();
response.Phonebook = createdPhonebook.ConvertToPhonebookViewModel();
response.Success = true;
}
catch (Exception ex)
{
response.Phonebook = new PhonebookViewModel();
response.Success = false;
response.Message = ex.Message;
}
return(response);
}
