public static bool RemoveUser(int id)
{
try
{
using (var db = new AdapterDbEntities())
{
var user = db.User.Find(id);
foreach (var role in user.Roles.ToList())
{
user.Roles.Remove(role);
}
var membership = db.BusinessMember.Where(bm => bm.UserId == id);
db.BusinessMember.RemoveRange(membership);
db.Set <User>().Remove(user);
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
log.Error("RemoveUser error", ex);
return(false);
}
}
public static bool AddBusinessToSignupTicket(int ticketId, List <int> selectedBusinesses)
{
try
{
using (var db = new AdapterDbEntities())
{
var ticket = db.UserSignupTicket.Find(ticketId);
ticket.Business.Clear();
foreach (int bizId in selectedBusinesses)
{
var biz = db.Business.Find(bizId);
ticket.Business.Add(biz);
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("DeleteSignupTicket error", ex);
return(false);
}
}
public static bool CreateRole(string roleName, List <int> selectedPermissionIds)
{
try
{
using (var db = new AdapterDbEntities())
{
Roles dbRole = new Roles {
Name = roleName
};
var selectedPermissions = db.Permission.Where(p => selectedPermissionIds.Contains(p.Id)).ToList();
foreach (var sp in selectedPermissions)
{
dbRole.Permission.Add(sp);
}
db.Roles.Add(dbRole);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("CreateRole error", ex);
return(false);
}
}
private static List <AggregatedData> GetAggregatedData(AdapterDbEntities db, string aggregationProcedureName, DateTime timeFrom, DateTime timeTo, List <Business> businessList, List <Line> lineList)
{
var timeFromParam = new SqlParameter("@timeFrom", timeFrom);
var timeToParam = new SqlParameter("@timeTo", timeTo);
DataTable dtBusinesses = new DataTable();
dtBusinesses.Columns.Add("Value", typeof(int));
foreach (var business in businessList)
{
dtBusinesses.Rows.Add(business.Id);
}
var bizIdentsParam = new SqlParameter("@businessIds", SqlDbType.Structured);
bizIdentsParam.Value = dtBusinesses;
bizIdentsParam.TypeName = "dbo.Integers";
DataTable dtLines = new DataTable();
dtLines.Columns.Add("Value", typeof(int));
foreach (var line in lineList)
{
dtLines.Rows.Add(line.Id);
}
var lnIdentsParam = new SqlParameter("@lineIds", SqlDbType.Structured);
lnIdentsParam.Value = dtLines;
lnIdentsParam.TypeName = "dbo.Integers";
return(db.Database.SqlQuery <AggregatedData>(
$"EXEC {aggregationProcedureName} @timeFrom, @timeTo, @businessIds, @lineIds",
timeFromParam, timeToParam, bizIdentsParam, lnIdentsParam).ToList());
}
public static bool AssignTicketBusinesses(int userId, Guid guidTicketNumber)
{
try
{
using (var db = new AdapterDbEntities())
{
var ticket = db.UserSignupTicket.First(t => t.Guid == guidTicketNumber);
var businessIdList = ticket.Business.Select(b => b.Id);
var user = db.User.Find(userId);
foreach (var bizId in businessIdList)
{
db.BusinessMember.AddOrUpdate(
new BusinessMember
{
BusinessId = bizId,
UserId = user.Id
});
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("AssignBusinesses error", ex);
return(false);
}
}
public static bool ModifyRole(AdapterDb.Roles role, List <int> selectedPermissionIds)
{
try
{
using (var db = new AdapterDbEntities())
{
var dbRole = db.Roles.Find(role.Id);
dbRole.Name = role.Name;
dbRole.Permission.Clear();
var selectedPermissions = db.Permission.Where(p => selectedPermissionIds.Contains(p.Id)).ToList();
foreach (var sp in selectedPermissions)
{
dbRole.Permission.Add(sp);
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("ModifyRole error", ex);
return(false);
}
}
public static List <Employee> GetEmployeeList(IPrincipal user, List <int> lineIds)
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
if (user == null || user.IsInRole("Admin"))
{
var items = (from i in db.EmployeeLine
where lineIds.Count == 0 || lineIds.Contains(i.LineId)
orderby i.Employee.Name
select i.Employee).Distinct().ToList();
return(items);
}
else
{
var dbUser = db.User.Include("BusinessMember").Where(u => u.UserName == user.Identity.Name).First();
var allowedBizIds = dbUser.BusinessMember.Select(bm => bm.BusinessId);
var allowedLnIds = db.Line.Where(l => allowedBizIds.Contains(l.BusinessId)).Select(l => l.Id).ToList();
var filteredLnIds = allowedLnIds.Where(l => lineIds.Contains(l)).ToList();
var employees = db.EmployeeLine
.Where(el => filteredLnIds.Contains(el.LineId))
.Select(el => el.Employee)
.OrderBy(e => e.Name)
.ToList();
return(employees);
}
}
}
public static bool AssignBusinessToUser(int userId, List <int> businessIdList)
{
try
{
using (var db = new AdapterDbEntities())
{
var user = db.User.Find(userId);
var usersMembership = db.BusinessMember.Where(bm => bm.UserId == userId);
db.BusinessMember.RemoveRange(usersMembership);
foreach (var bizId in businessIdList)
{
db.BusinessMember.Add(
new BusinessMember
{
BusinessId = bizId,
UserId = user.Id
});
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("AssignBusinesses error", ex);
return(false);
}
}
public static bool DeleteSignupTicket(int ticketId)
{
try
{
using (var db = new AdapterDbEntities())
{
var ticket = db.UserSignupTicket.Find(ticketId);
foreach (var biz in ticket.Business)
{
biz.TicketId = null;
biz.UserSignupTicket = null;
}
db.UserSignupTicket.Remove(ticket);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("DeleteSignupTicket error", ex);
return(false);
}
}
public static List <Line> GetLineList(IPrincipal user, int?businessId = null)
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
if (user == null || user.IsInRole("Admin"))
{
var items = (from i in db.Line
where !businessId.HasValue || (i.BusinessId == businessId.Value)
select i).ToList();
return(items);
}
else
{
var dbUser = db.User.Include("BusinessMember").Where(u => u.UserName == user.Identity.Name).First();
var allowedBizIds = dbUser.BusinessMember.Select(bm => bm.BusinessId);
var items = (from i in db.Line
where allowedBizIds.Contains(i.BusinessId) && (!businessId.HasValue || (i.BusinessId == businessId.Value))
select i).ToList();
return(items);
}
}
}
public static List <EmployeeTimes> GetEmployeeTimes(int[] employeeIds, DateTime timeFrom, DateTime timeTo)
{
List <EmployeeTimes> itemList;
using (var db = new AdapterDbEntities())
{
// PARAMETERS
var timeFromParam = new SqlParameter("@timeFrom", timeFrom);
var timeToParam = new SqlParameter("@timeTo", timeTo);
DataTable dtEmployees = new DataTable();
dtEmployees.Columns.Add("Value", typeof(int));
foreach (var id in employeeIds)
{
dtEmployees.Rows.Add(id);
}
var empIdentsParam = new SqlParameter("@employeeIds", SqlDbType.Structured);
empIdentsParam.Value = dtEmployees;
empIdentsParam.TypeName = "dbo.Integers";
itemList = db.Database.SqlQuery <EmployeeTimes>(
$"EXEC GetEmployeeTimes @timeFrom, @timeTo, @employeeIds",
timeFromParam, timeToParam, empIdentsParam).ToList();
}
return(itemList);
}
public static bool SaveConfigItems(List <ConfigItem> configItems)
{
try
{
using (var db = new AdapterDbEntities())
{
// Update configuration items
foreach (var dbItem in db.ConfigItem)
{
var match = configItems.FirstOrDefault(i => i.Name.Equals(dbItem.Name));
if (match != null)
{
dbItem.Value = match.Value.Trim();
}
}
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
log.Error("SaveConfigItems error", ex);
return(false);
}
}
public static bool HasMultipleBusiness(string userName)
{
using (var db = new AdapterDbEntities())
{
var item = db.User.Where(u => u.UserName == userName).First();
return(item.BusinessMember.Count > 1);
}
}
public static string GetActivityRestrictionsHash()
{
using (var db = new AdapterDbEntities())
{
var hash = db.Database.SqlQuery <string>("EXEC SEC.GetAllowedActivitiesChecksum");
return(hash.Single());
}
}
public static bool CheckSignupTicketNumber(Guid guid)
{
using (var db = new AdapterDbEntities())
{
var item = db.UserSignupTicket.FirstOrDefault(t => t.Guid == guid);
return(item != null);
}
}
public static List <UserSignupTicket> GetSignupTicketList()
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
return(db.UserSignupTicket.Include("Business").ToList());
}
}
public static User GetUser(int id)
{
using (var db = new AdapterDbEntities())
{
var item = db.User.Find(id);
return(item);
}
}
public static Line GetLine(int lineId)
{
using (var db = new AdapterDbEntities())
{
var item = db.Line.Include("Business").First(l => l.Id == lineId);
return(item);
}
}
public static List <ActivityRestriction> GetActivityRestrictions()
{
using (var db = new AdapterDbEntities())
{
var restrictions = db.Database.SqlQuery <ActivityRestriction>("SELECT [UserName],[Controller],[Action],[Method] FROM SEC.[AllowedActivities]");
return(restrictions.ToList());
}
}
public static TPoco Find <TEntity, TPoco>(int id, Func <TEntity, TPoco> map) where TEntity : class
{
using (var db = new AdapterDbEntities())
{
var dbObj = db.Set <TEntity>().Find(id);
var obj = map(dbObj);
return(obj);
}
}
public static object GetSignupTicket(int ticketId)
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var item = db.UserSignupTicket.Include("Business").First(b => b.Id == ticketId);
return(item);
}
}
public static List <TPoco> GetAll <TEntity, TPoco>(Func <IEnumerable <TEntity>, IEnumerable <TPoco> > map) where TEntity : class
{
using (var db = new AdapterDbEntities())
{
var dbList = db.Set <TEntity>().ToList();
var list = map(dbList).ToList();
return(list);
}
}
public static Business GetBusiness(int businessId)
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var item = db.Business.Include("Line").First(b => b.Id == businessId);
return(item);
}
}
public static List <ConfigItem> GetConfigItems()
{
using (var db = new AdapterDbEntities())
{
var items = from i in db.ConfigItem
select i;
return(items.ToList());
}
}
public static List <int> GetBusinessIds(string userName)
{
using (var db = new AdapterDbEntities())
{
var item = db.User.Where(u => u.UserName == userName).First();
var ids = item.BusinessMember.Select(bm => bm.BusinessId);
return(ids.ToList());
}
}
public static List <User> GetUsers()
{
using (var db = new AdapterDbEntities())
{
var items = from i in db.User//.Include(u => u.Roles)
select i;
return(items.ToList());
}
}
public static List <Employee> GetCalledEmployees(IPrincipal user, int?businessId, DateTime timeFrom, DateTime timeTo)
{
using (var db = new AdapterDbEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var businessIds = new List <int>();
if (businessId.HasValue && user.IsInRole("Admin"))
{
businessIds.Add(businessId.Value);
}
else if (businessId.HasValue && !user.IsInRole("Admin"))
{
var dbUser = db.User.Include("BusinessMember").Where(u => u.UserName == user.Identity.Name).First();
var allowedBizIds = dbUser.BusinessMember.Select(bm => bm.BusinessId);
if (!allowedBizIds.Contains(businessId.Value))
{
return(new List <Employee>());
}
businessIds.Add(businessId.Value);
}
else if (!businessId.HasValue && /*user == null || */ user.IsInRole("Admin"))
{
var allIds = db.Business.Select(b => b.Id).ToList();
businessIds.AddRange(allIds);
}
else //if (!businessId.HasValue && !user.IsInRole("Admin"))
{
var dbUser = db.User.Include("BusinessMember").Where(u => u.UserName == user.Identity.Name).First();
var allowedBizIds = dbUser.BusinessMember.Select(bm => bm.BusinessId);
businessIds.AddRange(allowedBizIds);
}
// PARAMETERS
var timeFromParam = new SqlParameter("@timeFrom", timeFrom);
var timeToParam = new SqlParameter("@timeTo", timeTo);
DataTable dtBusinesses = new DataTable();
dtBusinesses.Columns.Add("Value", typeof(int));
foreach (var id in businessIds)
{
dtBusinesses.Rows.Add(id);
}
var bizIdentsParam = new SqlParameter("@businessIds", SqlDbType.Structured);
bizIdentsParam.Value = dtBusinesses;
bizIdentsParam.TypeName = "dbo.Integers";
var employees = db.Database.SqlQuery <Employee>(
$"EXEC GetCalledEmployees @timeFrom, @timeTo, @businessIds",
timeFromParam, timeToParam, bizIdentsParam).ToList();
return(employees);
}
}
public static Overview GetOverview()
{
var stats = new Overview();
using (var db = new AdapterDbEntities())
{
Overview item = db.Database.SqlQuery <Overview>("GetOverview").FirstOrDefault();
return(item);
}
}
public static int GetLineCount(int?businessId = null)
{
using (var db = new AdapterDbEntities())
{
var items = from i in db.Line
where !businessId.HasValue || (i.BusinessId == businessId.Value)
select i;
return(items.Count());
}
}
public static List <Business> GetBusinessList(string userName)
{
using (var db = new AdapterDbEntities())
{
var user = db.User.Where(u => u.UserName == userName).First();
var ids = user.BusinessMember.Select(bm => bm.BusinessId);
var items = db.Business.Include("Line").Where(b => ids.Contains(b.Id));
return(items.ToList());
}
}