public static List <Rating> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "",
string Keywords = "", string StartsWith = "", int UserId = 0, int ListingId = 0)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Ratings.Include(o => o.User).Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().StartsWith(Keywords.ToLower()));
}
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (ListingId > 0)
{
query = query.Where(o => o.ListingId == ListingId);
}
return(query.OrderBy(Sort.Coalesce(DefaultSort)).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public bool UpdateOrder()
{
var success = false;
using (var ctx = new AgrishareEntities())
{
if (SortOrder > 0)
{
ctx.Faqs.Where(o => o.SortOrder >= SortOrder).ToList().ForEach(o => { o.SortOrder += 1; });
}
success = ctx.SaveChanges() > 0;
}
using (var ctx = new AgrishareEntities())
{
ctx.Faqs.Attach(this);
if (SortOrder == 0)
{
SortOrder = ctx.Faqs.Max(o => o.SortOrder) + 1;
}
ctx.Entry(this).State = EntityState.Modified;
success = ctx.SaveChanges() > 0;
}
return(success);
}
public static Device Find(int Id = 0, string Token = "")
{
if (Id == 0 && Token.IsEmpty())
{
return new Device
{
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow,
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Devices.Include(o => o.User).Where(o => !o.Deleted);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
if (!Token.IsEmpty())
{
query = query.Where(e => e.Token == Token);
}
return(query.FirstOrDefault());
}
}
public static Template Find(int Id = 0, string Title = "")
{
if (Id == 0 && Title.IsEmpty())
{
return new Template
{
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Templates.Where(o => !o.Deleted);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
if (!Title.IsEmpty())
{
query = query.Where(e => e.Title.Equals(Title, StringComparison.InvariantCultureIgnoreCase));
}
return(query.FirstOrDefault());
}
}
public static int Count(string Keywords = "", string StartsWith = "", int UserId = 0, int ListingId = 0, int Stars = 0)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Ratings.Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().StartsWith(Keywords.ToLower()));
}
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (ListingId > 0)
{
query = query.Where(o => o.ListingId == ListingId);
}
if (Stars > 0)
{
query = query.Where(o => o.Stars == Stars);
}
return(query.Count());
}
}
public static Blog Find(int Id = 0, string UrlPath = "")
{
if (!UrlPath.IsEmpty() && Regex.IsMatch(UrlPath, @"^/blog/[\d]+/.+$"))
{
Id = Convert.ToInt32(Regex.Replace(UrlPath, @"^/blog/([\d]+)/.+$", "$1"));
}
if (Id == 0)
{
return new Blog {
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow,
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Blogs.Where(o => o.Deleted == false);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
return(query.FirstOrDefault());
}
}
public static List <Category> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "", string Keywords = "", int?ParentId = null)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Categories.Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
if (ParentId.HasValue)
{
if (ParentId == 0)
{
query = query.Where(e => e.ParentId == null);
}
else
{
query = query.Where(e => e.ParentId == ParentId);
}
}
return(query.OrderBy(Sort.Coalesce(DefaultSort)).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static Rating Find(int Id = 0, int BookingId = 0)
{
if (Id == 0 && BookingId == 0)
{
return new Rating
{
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow,
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Ratings.Include(o => o.User).Where(o => !o.Deleted);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
if (BookingId > 0)
{
query = query.Where(e => e.BookingId == BookingId);
}
return(query.FirstOrDefault());
}
}
public static Transaction Find(int Id = 0, string ClientCorrelator = "")
{
if (Id == 0 && ClientCorrelator.IsEmpty())
{
return new Transaction
{
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow,
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Transactions.Include(o => o.BookingUser).Where(o => !o.Deleted);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
if (!ClientCorrelator.IsEmpty())
{
query = query.Where(e => e.ClientCorrelator == ClientCorrelator);
}
return(query.FirstOrDefault());
}
}
public static List <Listing> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "",
string Keywords = "", string StartsWith = "", int UserId = 0, int CategoryId = 0, ListingStatus Status = ListingStatus.None, bool Deleted = false)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Listings.Where(o => o.Deleted == Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().StartsWith(Keywords.ToLower()));
}
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (CategoryId > 0)
{
query = query.Where(o => o.CategoryId == CategoryId);
}
if (Status != ListingStatus.None)
{
query = query.Where(o => o.StatusId == Status);
}
return(query.OrderBy(Sort.Coalesce(DefaultSort)).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static List <Notification> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "", int UserId = 0, NotificationGroup GroupId = NotificationGroup.None, int BookingId = 0, NotificationType Type = NotificationType.None)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Notifications.Include(o => o.Booking).Include(o => o.Booking.Service).Include(o => o.Booking.Service.Listing).Where(o => !o.Deleted && !o.Booking.Deleted && !o.Booking.Service.Deleted && !o.Booking.Service.Listing.Deleted);
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (GroupId != NotificationGroup.None)
{
query = query.Where(o => o.GroupId == GroupId);
}
if (BookingId > 0 && Type != NotificationType.None)
{
query = query.Where(e => e.BookingId == BookingId && e.TypeId == Type);
}
query = query.OrderBy(Sort.Coalesce(DefaultSort));
return(query.Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static int Count(string Keywords = "", string StartsWith = "", Gender Gender = Entities.Gender.None, int FailedLoginAttempts = 0, bool Deleted = false)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Users.Where(o => o.Deleted == Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => (o.FirstName + " " + o.LastName).ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => (o.FirstName + " " + o.LastName).ToLower().StartsWith(Keywords.ToLower()));
}
if (Gender != Gender.None)
{
query = query.Where(o => o.GenderId == Gender);
}
if (FailedLoginAttempts > 0)
{
query = query.Where(o => o.FailedLoginAttempts > 0);
}
return(query.Count());
}
}
public static List <User> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "", string Keywords = "", string StartsWith = "", Gender Gender = Entities.Gender.None, int FailedLoginAttempts = 0, bool Deleted = false)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Users.Where(o => o.Deleted == Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => (o.FirstName + " " + o.LastName).ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => (o.FirstName + " " + o.LastName).ToLower().StartsWith(Keywords.ToLower()));
}
if (Gender != Gender.None)
{
query = query.Where(o => o.GenderId == Gender);
}
if (FailedLoginAttempts > 0)
{
query = query.Where(o => o.FailedLoginAttempts > 0);
}
return(query.OrderBy(Sort.Coalesce(DefaultSort)).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static int Count(string Keywords = "", string StartsWith = "", int UserId = 0, int CategoryId = 0, ListingStatus Status = ListingStatus.None, bool Deleted = false)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Listings.Where(o => o.Deleted == Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
if (!StartsWith.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().StartsWith(Keywords.ToLower()));
}
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (CategoryId > 0)
{
query = query.Where(o => o.CategoryId == CategoryId);
}
if (Status != ListingStatus.None)
{
query = query.Where(o => o.StatusId == Status);
}
return(query.Count());
}
}
public static List <Transaction> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "",
int BookingId = 0, int BookingUserId = 0, TransactionStatus StatusId = TransactionStatus.None)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Transactions.Include(o => o.BookingUser).Where(o => !o.Deleted);
if (BookingId > 0)
{
query = query.Where(o => o.BookingId == BookingId);
}
if (BookingUserId > 0)
{
query = query.Where(o => o.BookingUserId == BookingUserId);
}
if (StatusId != TransactionStatus.None)
{
query = query.Where(o => o.StatusId == StatusId);
}
query = query.OrderBy(Sort.Coalesce(DefaultSort));
return(query.Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static int ActiveUsers(DateTime StartDate, DateTime EndDate)
{
using (var ctx = new AgrishareEntities())
{
var sql = $"SELECT COUNT(DISTINCT(UserId)) FROM Counters WHERE DATE(DateCreated) >= DATE('{SQL.Safe(StartDate)}') AND DATE(DateCreated) <= ('{SQL.Safe(EndDate)}')";
return(ctx.Database.SqlQuery <int>(sql).DefaultIfEmpty(0).FirstOrDefault());
}
}
private bool Add()
{
using (var ctx = new AgrishareEntities())
{
ctx.Journals.Attach(this);
ctx.Entry(this).State = EntityState.Added;
return(ctx.SaveChanges() > 0);
}
}
private bool Update()
{
using (var ctx = new AgrishareEntities())
{
ctx.Bookings.Attach(this);
ctx.Entry(this).State = EntityState.Modified;
return(ctx.SaveChanges() > 0);
}
}
public static decimal TotalAmountPaid(DateTime StartDate, DateTime EndDate)
{
using (var ctx = new AgrishareEntities())
{
StartDate = StartDate.StartOfDay();
EndDate = EndDate.EndOfDay();
return(ctx.Bookings.Where(o => !o.Deleted && o.StatusId == BookingStatus.Complete && o.StartDate <= EndDate && o.EndDate >= StartDate).Select(e => e.Price).DefaultIfEmpty(0).Sum());
}
}
private bool Add()
{
ClientCorrelator = Guid.NewGuid().ToString();
using (var ctx = new AgrishareEntities())
{
ctx.Transactions.Attach(this);
ctx.Entry(this).State = EntityState.Added;
return(ctx.SaveChanges() > 0);
}
}
private bool Add()
{
StatusId = ListingStatus.Live;
using (var ctx = new AgrishareEntities())
{
ctx.Listings.Attach(this);
ctx.Entry(this).State = EntityState.Added;
return(ctx.SaveChanges() > 0);
}
}
private bool Add()
{
StatusId = NotificationStatus.Pending;
using (var ctx = new AgrishareEntities())
{
ctx.Notifications.Attach(this);
ctx.Entry(this).State = EntityState.Added;
return(ctx.SaveChanges() > 0);
}
}
private bool Add()
{
using (var ctx = new AgrishareEntities())
{
try { SortOrder = ctx.Faqs.Max(o => o.SortOrder) + 1; }
catch { SortOrder = 1; }
ctx.Faqs.Attach(this);
ctx.Entry(this).State = EntityState.Added;
return(ctx.SaveChanges() > 0);
}
}
public static int CleanDeletedAccounts()
{
using (var ctx = new AgrishareEntities())
{
var oneYearAgo = DateTime.Now.AddYears(-1);
var users = ctx.Users.Where(o => o.Deleted && o.LastModified > oneYearAgo);
foreach (var user in users)
{
ctx.Entry(user).State = EntityState.Deleted;
}
return(ctx.SaveChanges());
}
}
public static User Find(int Id = 0, string EmailAddress = "", string Telephone = "", string AuthToken = "", bool Deleted = false)
{
if (!AuthToken.IsEmpty())
{
var item = Cache.Instance.Get <User>(CacheKey(AuthToken));
if (item != null)
{
// HACK to remove duplicates from roles list
item.Roles = item.Roles.Distinct().ToList();
return(item);
}
}
if (Id == 0 && EmailAddress.IsEmpty() && Telephone.IsEmpty() && AuthToken.IsEmpty())
{
return new User {
DateCreated = DateTime.UtcNow,
LastModified = DateTime.UtcNow,
}
}
;
using (var ctx = new AgrishareEntities())
{
var query = ctx.Users.Where(o => o.Deleted == Deleted);
if (Id > 0)
{
query = query.Where(e => e.Id == Id);
}
if (!EmailAddress.IsEmpty())
{
query = query.Where(e => e.EmailAddress.Equals(EmailAddress, StringComparison.InvariantCultureIgnoreCase));
}
if (!Telephone.IsEmpty())
{
query = query.Where(e => e.Telephone.Equals(Telephone, StringComparison.InvariantCultureIgnoreCase));
}
if (!AuthToken.IsEmpty())
{
query = query.Where(e => e.AuthToken.Equals(AuthToken));
}
return(query.FirstOrDefault());
}
}
public static List <Template> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "", string Keywords = "")
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Templates.Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
return(query.OrderBy(Sort.Coalesce(DefaultSort)).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static int Count(string Keywords = "")
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Faqs.Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => (o.Question + " " + o.Answer).ToLower().Contains(Keywords.ToLower()));
}
return(query.Count());
}
}
public static int Count(string Keywords = "")
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Logs.Where(o => !o.Deleted);
if (!Keywords.IsEmpty())
{
query = query.Where(o => o.Title.ToLower().Contains(Keywords.ToLower()));
}
return(query.Count());
}
}
public static List <Booking> List(int PageIndex = 0, int PageSize = int.MaxValue, string Sort = "", int ListingId = 0, int UserId = 0,
int SupplierId = 0, DateTime?StartDate = null, DateTime?EndDate = null, BookingStatus Status = BookingStatus.None, bool Upcoming = false)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Bookings.Include(o => o.User).Include(o => o.Service).Include(o => o.Listing).Where(o => !o.Deleted);
if (ListingId > 0)
{
query = query.Where(o => o.ListingId == ListingId);
}
if (UserId > 0)
{
query = query.Where(o => o.UserId == UserId);
}
if (SupplierId > 0)
{
query = query.Where(o => o.Listing.UserId == SupplierId);
}
if (StartDate.HasValue)
{
var startDate = StartDate.Value.StartOfDay();
query = query.Where(o => o.EndDate >= startDate);
}
if (EndDate.HasValue)
{
var endDate = EndDate.Value.StartOfDay();
query = query.Where(o => o.StartDate <= endDate);
}
if (Status != BookingStatus.None)
{
query = query.Where(e => e.StatusId == Status);
}
if (Upcoming)
{
query = query.Where(e => e.StatusId == BookingStatus.Approved || e.StatusId == BookingStatus.InProgress);
}
query = query.OrderBy(Sort.Coalesce(DefaultSort));
return(query.Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
}
public static decimal OfferingSummary(int UserId, DateTime?StartDate = null)
{
using (var ctx = new AgrishareEntities())
{
var query = ctx.Bookings.Include(o => o.Listing)
.Where(o => !o.Deleted && o.Listing.UserId == UserId && (o.StatusId == BookingStatus.Approved || o.StatusId == BookingStatus.Complete || o.StatusId == BookingStatus.InProgress));
if (StartDate.HasValue)
{
var startDate = StartDate.Value.StartOfDay();
query = query.Where(o => o.StartDate >= startDate);
}
return(query.Select(o => o.Price).DefaultIfEmpty(0).Sum());
}
}