public int EmailCounter(string email)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
return(dbConnection.User.Where(e => e.Email == email).Count());
}
}
public int UserCounter(string username)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
return(dbConnection.User.Where(u => u.Username == username).Count());
}
}
public IEnumerable <ActivityModel> GetActivityByCustomerId(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var customerId = dbConnection.Activity.Where(a => a.CustomerId == id).ToList();
return(customerId.Select(c => new ActivityModel(c)).ToList());
}
}
public ActivityModel GetActivityById(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var activity = dbConnection.Activity.Where(a => a.Id == id).FirstOrDefault();
return(activity == null ? null : new ActivityModel(activity));
}
}
public void InsertActivity(Activity activity)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
dbConnection.Activity.Add(activity);
dbConnection.SaveChanges();
}
}
public int EmailCounter(string email)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var emailCount = dbConnection.User.Where(u => u.Email == email).Count();
return(emailCount);
}
}
public ActivityModel GetLastActivity(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var lastActivity = dbConnection.Activity.Where(a => a.CustomerId == id).ToList();
return(lastActivity.LastOrDefault().ToModel());
}
}
public void Insert(User user)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
dbConnection.User.Add(user);
dbConnection.SaveChanges();
}
}
public IEnumerable <CustomerModel> GetAll()
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var customers = dbConnection.Customer.ToList();
return(customers.Select(c => new CustomerModel(c)).ToList());
}
}
public int PhoneCounter(string phone)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var phoneCount = dbConnection.Customer.Where(c => c.Phone == Convert.ToInt32(phone)).Count();
return(phoneCount);
}
}
public IEnumerable <UserModel> GetAll()
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var users = dbConnection.User.ToList();
return(users.Select(u => new UserModel(u)).ToList());
}
}
public void Insert(Customer customer)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
dbConnection.Customer.Add(customer);
dbConnection.SaveChanges();
}
}
public UserModel GetById(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var user = dbConnection.User.Where(u => u.Id == id).FirstOrDefault();
return(user == null ? null : new UserModel(user));
}
}
public int GetUserIdByLogin(string username, string password)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var user = dbConnection.User.Where(u => u.Username == username && u.Password == password).ToList().LastOrDefault();
return(user?.Id ?? 0);
}
}
public IEnumerable <ActivityModel> GetActivitiesByCustomerId(int customerId)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var activities = dbConnection.Activity.Where(a => a.CustomerId == customerId).ToList();
return(activities.Select(a => new ActivityModel(a)).ToList());
}
}
public IEnumerable <CustomerModel> GetCustomersByPage(int count, int pageNo)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var skipCount = count * (pageNo - 1);
var customers = dbConnection.Customer.Skip(skipCount).Take(count).ToList();
return(customers.Select(a => new CustomerModel(a)).ToList());
}
}
public ActivityModel GetActivityById(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
//var user = dbConnection.Users.Where(u => u.Id == id).FirstOrDefault();
var activity = dbConnection.Activity.Find(id);
return(new ActivityModel(activity));
}
}
public CustomerModel GetById(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
//var user = dbConnection.Users.Where(u => u.Id == id).FirstOrDefault();
var customer = dbConnection.Customer.Find(id);
return(new CustomerModel(customer));
}
}
public IEnumerable <UserModel> GetUsersByPage(int count, int pageNo)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var skipCount = count * (pageNo - 1);
var users = dbConnection.User.Skip(skipCount).Take(count).ToList();
return(users.Select(u => new UserModel(u)).ToList());
}
}
public IEnumerable <CustomerModel> GetCustomersByPageNumber(int pageNumber)
{
var skipCount = (pageNumber - 1) * 5;
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var customers = dbConnection.Customer.Skip(skipCount).Take(5).ToList();
return(customers.Select(c => new CustomerModel(c)).ToList());
}
}
public void Log(LogType logType, string message)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
Log newLog = new Log();
newLog.Type = logType;
newLog.Message = message;
newLog.Timestamp = DateTime.UtcNow.Ticks;
dbConnection.Log.Add(newLog);
dbConnection.SaveChanges();
}
}
public ActivityModel GetCustomerLastActivity(int id)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
var tempCustomerCount = dbConnection.Activity.Where(c => c.CustomerId == id).Count();
if (tempCustomerCount == 0)
{
return(null);
}
var activity = dbConnection.Activity.Where(a => a.CustomerId == id).LastOrDefault();
return(activity == null ? null : new ActivityModel(activity));
}
}
public void Log(LogType type, string message)
{
using (SQLiteDBContext dbConnection = this.OpenConnection())
{
Log tempLog = null;
tempLog.Message = message;
tempLog.Type = type;
tempLog.Timestamp = DateTime.UtcNow.Ticks;
dbConnection.Log.Add(tempLog);
dbConnection.SaveChanges();
//dbConnection.Execute("INSERT INTO Log (Type, Message, Timestamp) VALUES(@Type, @Message, @Timestamp)", new { Type = type, Message = message, Timestamp = DateTime.UtcNow.Ticks });
}
}