public List<EmailAddress> GetEmailAddresses()
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.EmailAddresses.ToList();
}
}
public EmailAddress GetEmailAddressByReference(string emailAddressReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.EmailAddresses.FirstOrDefault(x => x.EmailAddressReference == emailAddressReference);
}
}
public PhoneNumber GetPhoneNumberByReference(string phoneNumberReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.PhoneNumbers.FirstOrDefault(x => x.PhoneNumberReference == phoneNumberReference);
}
}
public List<PhoneNumber> GetPhoneNumbers()
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.PhoneNumbers.ToList();
}
}
public PhoneNumber GetPhoneNumberByNumber(string number)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.PhoneNumbers.FirstOrDefault(x => x.Number == number);
}
}
public bool CreateEmail(Email email)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
//create EmailAddresses objects in the context for those that don't yet exist
//this ensures that the rare case where an email is related to the same address multiple times doesn't fail
foreach (var emailEmailAddress in email.EmailEmailAddressRelationships)
{
_emailAddressService.CreateEmailAddress(emailEmailAddress.EmailAddress);//will not create duplicates
}
//use existing EmailAddresses objects in the context rather than create duplicates
foreach (var emailEmailAddress in email.EmailEmailAddressRelationships)
{
emailEmailAddress.EmailAddress =
context.EmailAddresses.FirstOrDefault(x => x.Email == emailEmailAddress.EmailAddress.Email);
}
//don't save those relationships with null (invalid) email addresses
email.EmailEmailAddressRelationships.RemoveAll(x => x.EmailAddress == null);
context.Emails.Add(email);
return context.SaveChanges() > 0;
}
}
public bool CreateEmailEmailAddress(EmailEmailAddress emailEmailAddress)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
context.EmailEmailAddresses.Add(emailEmailAddress);
return context.SaveChanges() > 0;
}
}
public ICollection<Attachment> GetAttachmentsByEmail(string emailReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var email = context.Emails
.Include(e => e.Attachments)
.FirstOrDefault(x => x.EmailReference == emailReference);
return email?.Attachments.ToList() ?? new List<Attachment>();
}
}
public List<Email> GetAllEmailsForEmailAddress(string emailAddress)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return
context.Emails.Include(e => e.EmailEmailAddressRelationships.Select(p => p.EmailAddress))
.Where(x => x.EmailEmailAddressRelationships
.Any(eea => eea.EmailAddress.Email == emailAddress)
)
.ToList();
}
}
public Attachment GetAttachmentByReference(string emailReference, string attachmentReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var email = context.Emails
.Include(e => e.Attachments)
.FirstOrDefault(x => x.EmailReference == emailReference);
return email?.Attachments.FirstOrDefault(x => x.AttachmentReference == attachmentReference);
}
}
public bool DeletePhoneNumber(string phoneNumberReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var phoneNumber = context.PhoneNumbers.FirstOrDefault(x => x.PhoneNumberReference == phoneNumberReference);
if (phoneNumber == null) return false;
context.PhoneNumbers.Remove(phoneNumber);
return context.SaveChanges() > 0;
}
}
public bool DeleteEmailAddress(string emailAddressReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var emailAddress = context.EmailAddresses.FirstOrDefault(x => x.EmailAddressReference == emailAddressReference);
if (emailAddress == null) return false;
context.EmailAddresses.Remove(emailAddress);
return context.SaveChanges() > 0;
}
}
public bool DeleteSms(string smsReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var sms = context.Sms.FirstOrDefault(x => x.SmsReference == smsReference);
if (sms == null) return false;
context.Sms.Remove(sms);
return context.SaveChanges() > 0;
}
}
public bool DeleteCall(string callReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var call = context.Calls.FirstOrDefault(x => x.CallReference == callReference);
if (call == null) return false;
context.Calls.Remove(call);
return context.SaveChanges() > 0;
}
}
public bool CreatePhoneNumber(PhoneNumber phoneNumber)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
//do not create invalid records or duplicates
if (!phoneNumber.IsValidPhoneNumber() ||
GetPhoneNumberByNumber(phoneNumber.Number) != null)
return false;
context.PhoneNumbers.Add(phoneNumber);
return context.SaveChanges() > 0;
}
}
public bool CreateAttachment(string emailReference, Attachment attachment)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var email = context.Emails
.Include(e => e.Attachments)
.FirstOrDefault(x => x.EmailReference == emailReference);
if (email == null)
return false;
email.Attachments.Add(attachment);
return context.SaveChanges() > 0;
}
}
public bool CreateEmailAddress(EmailAddress emailAddress)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
emailAddress.Normalise();
//do not create invalid records or duplicates
if(!emailAddress.IsValidEmailAddress() ||
GetEmailAddressByEmail(emailAddress.Email) != null)
return false;
context.EmailAddresses.Add(emailAddress);
return context.SaveChanges() > 0;
}
}
public bool DeleteAttachment(string emailReference, string attachmentReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var email = context.Emails
.Include(e => e.Attachments)
.FirstOrDefault(x => x.EmailReference == emailReference);
var attachment = email?.Attachments.FirstOrDefault(x => x.AttachmentReference == attachmentReference);
if (attachment == null)
return false;
context.Attachments.Remove(attachment);
return context.SaveChanges() > 0;
}
}
public List<Sms> GetAllSmsForPhoneNumber(string phoneNumber)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var number = context.PhoneNumbers.FirstOrDefault(x => x.Number == phoneNumber);
if (number == null)
return new List<Sms>();
var inbound = context.Sms
.Include(c => c.From)
.Include(c => c.To)
.Where(x => x.From_PhoneNumberId == number.PhoneNumberId).ToList();
var outbound = context.Sms
.Include(c => c.To)
.Include(c => c.From)
.Where(x => x.To_PhoneNumberId == number.PhoneNumberId).ToList();
return inbound.Concat(outbound).OrderByDescending(c => c.Created).ToList();
}
}
public bool CreateSms(Sms sms)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
//ensure phone numbers are valid
if (!sms.To.IsValidPhoneNumber() || !sms.From.IsValidPhoneNumber())
return false;
//create phone numbers for to or from (this wont create duplicates)
_phoneNumberService.CreatePhoneNumber(sms.To);
_phoneNumberService.CreatePhoneNumber(sms.From);
//use newly created (or existing) Phone Number objects in the context for to and from
sms.From =
context.PhoneNumbers.FirstOrDefault(x => x.Number == sms.From.Number);
sms.To =
context.PhoneNumbers.FirstOrDefault(x => x.Number == sms.To.Number);
context.Sms.Add(sms);
return context.SaveChanges() > 0;
}
}
public List<Sms> GetSms()
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.Sms.Include(s => s.To).Include(s =>s.From).ToList();
}
}
public Email GetEmailByReference(string emailReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var email =
context.Emails
.Include(e => e.EmailEmailAddressRelationships.Select(p => p.EmailAddress))
.FirstOrDefault(x => x.EmailReference == emailReference);
return email;
}
}
public DateTime? GetTimeLastSmsSent(IEnumerable<string> phoneNumbers)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.Sms
.Where(s => !s.IsInbound && phoneNumbers.Any(pn=>pn == s.To.Number))
.Select(s=>s.Created)
.OrderByDescending(s=>s)
.FirstOrDefault();
}
}
public int GetSmsNewerThanDateTimeCount(IEnumerable<string> phoneNumbers, DateTime time)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return
context.Sms
.Include(s => s.From)
.Where( x => x.IsInbound && x.Created > time && phoneNumbers.Any(pn=>pn == x.From.Number))
.ToList()
.Count;
}
}
public Sms GetSmsByReference(string smsReference)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return
context.Sms
.Include(s => s.To)
.Include(s => s.From)
.FirstOrDefault(x => x.SmsReference == smsReference);
}
}
public List<Email> GetEmails()
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.Emails.Include(e => e.EmailEmailAddressRelationships.Select(p => p.EmailAddress)).ToList();
}
}
public int GetEmailsNewerThanDateTimeCount(IEnumerable<string> emailAddresses, DateTime time)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return
context.Emails.Include(e => e.EmailEmailAddressRelationships.Select(p => p.EmailAddress))
.Where(x => x.EmailEmailAddressRelationships
.Any(eea => emailAddresses.Any(ea=>ea == eea.EmailAddress.Email))
&& x.IsInbound
&& x.SentTime > time
)
.ToList()
.Count;
}
}
public DateTime? GetTimeLastEmailSent(IEnumerable<string> emailAddresses)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
return context.Emails
.Where(x => x.EmailEmailAddressRelationships.Any(eea => emailAddresses.Any(ea => ea == eea.EmailAddress.Email)) && !x.IsInbound)
.Select(e=>e.SentTime)
.OrderByDescending(x => x)
.FirstOrDefault();
}
}
public bool UpdateSms(string smsReference, Sms sms)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var currentSms = context.Sms.FirstOrDefault(x => x.SmsReference == smsReference);
if (currentSms == null) return false;
_smsMapper.Map(sms, currentSms);
return context.SaveChanges() > 0;
}
}
public bool UpdateEmail(string emailReference, Email email)
{
using (var context = new CommunicationsContext(_databaseSettings.ConnectionString))
{
var currentEmail = context.Emails.FirstOrDefault(x => x.EmailReference == emailReference);
if (currentEmail == null) return false;
_emailMapper.Map(email, currentEmail);
return context.SaveChanges() > 0;
}
}
