protected IQueryable <Publisher> AddFilters(MailPusherDBContext context, string searchCriteria, PublisherStatus publisherStatuses, bool isPotentiallyCancelled, List <string> countries, List <int> categories, int minEmailAmount)
{
var query = context.Publishers.Select(x => x);
if (publisherStatuses != 0)
{
query = query.Where(x => (x.Status & publisherStatuses) > 0);
}
if (!string.IsNullOrEmpty(searchCriteria))
{
query = query.Where(x => x.Name.Contains(searchCriteria));
}
if (isPotentiallyCancelled)
{
var date = DateTime.UtcNow.AddDays(-30);
query = query.Where(x => x.LastReceivedEmail < date);
}
if (countries != null && countries.Count > 0)
{
query = query.Where(x => countries.Contains(x.Language));
}
if (categories != null && categories.Count > 0)
{
query = query.Where(x => categories.Contains(x.NACEID));
}
if (minEmailAmount > 0)
{
query = query.Where(x => context.Emails.Where(y => y.PublisherID == x.ID).Count() >= minEmailAmount);
}
return(query);
}
public Publisher Update(Publisher publisher)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
Publisher dbPublisher = context.Publishers.FirstOrDefault(x => x.ID == publisher.ID);
if (dbPublisher != null)
{
dbPublisher.Domain = publisher.Domain;
if (publisher.Status != dbPublisher.Status)
{
dbPublisher.StatusChanged = DateTime.UtcNow;
}
dbPublisher.Status = publisher.Status;
dbPublisher.Language = publisher.Language;
dbPublisher.NACEID = publisher.NACEID;
dbPublisher.Name = publisher.Name;
dbPublisher.Updater = publisher.Updater;
}
context.Entry(dbPublisher).State = EntityState.Modified;
context.SaveChanges();
result = context.Publishers.Include(x => x.NACE).FirstOrDefault(x => x.ID == publisher.ID);;
}
return(result);
}
public List <NACE> GetPublisherNaces()
{
List <NACE> result = new List <NACE>();
using (var context = new MailPusherDBContext())
{
var query = (from nace in context.NACEs
join publisher in context.Publishers
on nace.ID equals publisher.NACEID
group nace by new { nace.ID, nace.Description }
into gr
select new
{
ID = gr.Key.ID,
Description = gr.Key.Description
}).OrderBy(x => x.Description);
foreach (var item in query)
{
result.Add(new NACE()
{
ID = item.ID,
Description = item.Description
});
}
}
return(result);
}
public Publisher GetPublisherByCountryAndStatus(string countryCode, PublisherStatus status, Direction direction, int publisherId)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
var query = context.Publishers.Include(x => x.NACE);
if (direction == Direction.Next)
{
query = query.Where(x => x.ID > publisherId);
}
if (direction == Direction.Previous)
{
query = query.Where(x => x.ID < publisherId);
}
if (!string.IsNullOrEmpty(countryCode))
{
query = query.Where(x => x.Language == countryCode);
}
result = query.FirstOrDefault(x => x.Status == status);
}
return(result);
}
public Publisher GetFirstPublisherByStatus(PublisherStatus status)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.Include(x => x.NACE).FirstOrDefault(x => x.Status == status);
}
return(result);
}
public Publisher GetPublisher(int publisherId)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.Include(x => x.NACE).FirstOrDefault(x => x.ID == publisherId);
}
return(result);
}
public int GetTotalRecords()
{
int result = 0;
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.Count();
}
return(result);
}
public List <Publisher> GetPuglishers(List <int> publisherIds)
{
List <Publisher> result = new List <Publisher>();
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.Where(x => publisherIds.Contains(x.ID)).ToList();
}
return(result);
}
public UserSettings GetUserSettings(string userId)
{
UserSettings result = new UserSettings();
using (var context = new MailPusherDBContext())
{
result = context.UserSettings.FirstOrDefault(x => x.UserId == userId);
}
return(result);
}
public Email GetPrevEmailByPublisherCountryAndStatus(string countryCode, PublisherStatus status, int publisherId)
{
Email result = new Email();
using (var context = new MailPusherDBContext())
{
result = GenerateQueryForEmailByPublisherCountryAndStatus(context, countryCode, status, publisherId, Direction.Previous).FirstOrDefault();
}
return(result);
}
public EmailRawData GetEmailBody(int emailId)
{
EmailRawData result = new EmailRawData();
using (var context = new MailPusherDBContext())
{
result = context.EmailsRawData.FirstOrDefault(x => x.EmailID == emailId);
}
return(result);
}
public Email Get(int emailID)
{
Email result = new Email();
using (var context = new MailPusherDBContext())
{
result = context.Emails.FirstOrDefault(x => x.ID == emailID);
}
return(result);
}
public List <NACE> GetAllRecords()
{
List <NACE> result = new List <NACE>();
using (var context = new MailPusherDBContext())
{
result = context.NACEs.ToList();
}
return(result);
}
public int GetTotalFilteredRecords(string searchCriteria, PublisherStatus publisherStatuses, bool isPotentiallyCancelled, List <string> countries, List <int> categories, int minEmailAmount)
{
int result = 0;
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = AddFilters(context, searchCriteria, publisherStatuses, isPotentiallyCancelled, countries, categories, minEmailAmount).Count();
}
return(result);
}
public Email GetFirstPublisherEmail(PublisherStatus status, int publisherID)
{
Email result = new Email();
using (var context = new MailPusherDBContext())
{
result = context.Emails.OrderBy(x => x.ID).FirstOrDefault(x => x.PublisherID == publisherID);
}
return(result);
}
public Email GetEmailByPublisherCountryAndStatus(string countryCode, PublisherStatus status)
{
Email result = new Email();
using (var context = new MailPusherDBContext())
{
result = GenerateQueryForEmailByPublisherCountryAndStatus(context, countryCode, status, null, 0).FirstOrDefault();
}
return(result);
}
public int GetTotalFilteredRecords(string searchText, int publisherID, DateTime?from, DateTime?to)
{
int result = 0;
using (var context = new MailPusherDBContext())
{
var query = AddFilters(context, searchText, publisherID, from, to);
result = query.Count();
}
return(result);
}
public Publisher UpdateLastReceivedEmailDate(int publisherID)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.FirstOrDefault(x => x.ID == publisherID);
result.LastReceivedEmail = DateTime.UtcNow;
context.Entry(result).State = EntityState.Modified;
context.SaveChanges();
}
return(result);
}
public Publisher Create(Publisher publisher)
{
publisher.Created = DateTime.UtcNow;
publisher.Updated = DateTime.UtcNow;
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
result = context.Publishers.Add(publisher);
context.SaveChanges();
}
return(result);
}
public List <Publisher> GetPuglishers(int start, int length, string searchCriteria, PublisherStatus publisherStatuses, bool isPotentiallyCancelled, List <string> countries, List <int> categories, int minEmailAmount)
{
List <Publisher> result = new List <Publisher>();
using (MailPusherDBContext context = new MailPusherDBContext())
{
var query = AddFilters(context, searchCriteria, publisherStatuses, isPotentiallyCancelled, countries, categories, minEmailAmount);
var tmpResult = query.Include(x => x.NACE).OrderBy(x => x.ID).Skip(start).Take(length);
result = tmpResult.ToList();
}
return(result);
}
public bool Delete(int publisherID)
{
using (MailPusherDBContext context = new MailPusherDBContext())
{
Publisher dbPublisher = new Publisher()
{
ID = publisherID
};
context.Publishers.Attach(dbPublisher);
context.Publishers.Remove(dbPublisher);
context.SaveChanges();
}
return(true);
}
public Email GetFirstPublisherEmail(PublisherStatus status)
{
Email result = new Email();
using (var context = new MailPusherDBContext())
{
var publisher = context.Publishers.FirstOrDefault(x => x.Status == status);
if (publisher != null)
{
result = context.Emails.OrderBy(x => x.ID).FirstOrDefault(x => x.PublisherID == publisher.ID);
}
}
return(result);
}
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
GlobalConfiguration.Configure(WebApiConfig.Register);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
DataTables.AspNet.Mvc5.Configuration.RegisterDataTables();
Scheduler.MainScheduler.Start();
log4net.Config.XmlConfigurator.Configure(new FileInfo(Server.MapPath("~/Web.config")));
MailPusherDBContext.Create();
}
public List <Email> GetPublisherEmails(int start, int length, string searchCriteria, int publisherID, DateTime?from, DateTime?to, List <Common.Models.SortColumn> sorting)
{
List <Email> result = new List <Email>();
using (var context = new MailPusherDBContext())
{
var query = AddFilters(context, searchCriteria, publisherID, from, to, sorting);
if (sorting == null || sorting.Count == 0)
{
query = query.OrderByDescending(x => x.ReceivedGMT);
}
result = query.Skip(start).Take(length).ToList();
}
return(result);
}
public List <TimeChartPoint> GetConfirmedSubscriptionsByDate()
{
List <TimeChartPoint> result = new List <TimeChartPoint>();
using (MailPusherDBContext context = new MailPusherDBContext())
{
var query = context.Publishers.Where(x => x.StatusChanged.HasValue && x.Status == PublisherStatus.Confirmed).OrderBy(x => x.StatusChanged).GroupBy(x => DbFunctions.TruncateTime(x.StatusChanged.Value)).Select(group => new TimeChartPoint()
{
Date = group.Key.Value,
Value = group.Count()
});
result = query.ToList();
}
return(result);
}
public List <PublisherStats> GetStats(List <int> publisherIds)
{
List <PublisherStats> result = new List <PublisherStats>();
using (MailPusherDBContext context = new MailPusherDBContext())
{
var resultQuery = context.Emails.Where(x => publisherIds.Contains(x.PublisherID)).GroupBy(x => x.PublisherID).Select(group => new PublisherStats()
{
PublisherId = group.Key,
ReceivedEmails = group.Count()
});
result.AddRange(resultQuery);
}
return(result);
}
public UserSettings Create(string userId, string language)
{
UserSettings result = new UserSettings()
{
Language = language,
UserId = userId
};
using (var context = new MailPusherDBContext())
{
result = context.UserSettings.Add(result);
context.SaveChanges();
}
return(result);
}
public int GetTotalRecords(int publisherID)
{
int result = 0;
using (var context = new MailPusherDBContext())
{
var query = context.Emails.AsQueryable();
if (publisherID > 0)
{
query = query.Where(x => x.PublisherID == publisherID);
}
result = context.Emails.Count();
}
return(result);
}
public Publisher GetFirstPublisherByCountryAndStatus(string countryCode, PublisherStatus status)
{
Publisher result = new Publisher();
using (MailPusherDBContext context = new MailPusherDBContext())
{
var query = context.Publishers.Include(x => x.NACE);
if (!string.IsNullOrEmpty(countryCode))
{
query = query.Where(x => x.Language == countryCode);
}
result = query.FirstOrDefault(x => x.Status == status);
}
return(result);
}
public UserSettings Update(string userId, string language)
{
UserSettings result = new UserSettings();
using (var context = new MailPusherDBContext())
{
result = context.UserSettings.FirstOrDefault(x => x.UserId == userId);
if (result.Language != language)
{
result.Language = language;
context.Entry(result).State = EntityState.Modified;
context.SaveChanges();
}
}
return(result);
}
