public List<RIGHT> GetRightList()
{
using (var context = new OpletalovaDbEntities())
{
return context.RIGHT.ToList();
}
}
public int CreateForumItem(int eventId, string forumItemText, int creatorId)
{
if (creatorId <= 0)
throw new Exception("Nepříhlášený uživatel nemá právo vytvářet příspěvky ve fóru! (systémové omezení)");
using (var context = new OpletalovaDbEntities())
{
var user = context.USER.FirstOrDefault(u => u.Id == creatorId);
if (user == null)
throw new Exception("Uživatel nenalezen (příspěvek nelze vytvořit!)");
var evnt = context.EVENT.FirstOrDefault(e => e.Id == eventId);
if (evnt == null)
throw new Exception("Událost nanalezena (příspěvek nelze vytvořit!)");
var forumItem = new FORUM_ITEM()
{
EVENT = evnt,
USER = user,
CreatorId = user.Id,
Message = forumItemText,
EventId = evnt.Id,
Created = DateTime.Now
};
context.FORUM_ITEM.Add(forumItem);
context.SaveChanges();
return forumItem.Id;
}
}
public List<EVENT_ACCESSIBILITY> GetEventAccessibilityList()
{
using (var context = new OpletalovaDbEntities())
{
return context.EVENT_ACCESSIBILITY.ToList();
}
}
public void RestoreDatabaseToDefaultValues()
{
using (var context = new OpletalovaDbEntities())
{
context.RESTORE_DB_TO_DEFAULT();
context.SaveChanges();
}
}
public void CreateGroup(GROUP group)
{
using (var context = new OpletalovaDbEntities())
{
context.GROUP.Add(group);
context.SaveChanges();
}
}
public List<GROUP> GetGroupList()
{
using (var context = new OpletalovaDbEntities())
{
return context.GROUP
.Include(g => g.RIGHT)
.Include(g => g.USER)
.ToList();
}
}
public FILE_INFO DownloadEventFile(int fileId, int eventId)
{
using (var context = new OpletalovaDbEntities())
{
return context.FILE_INFO
.Include(e => e.EVENT)
.Include(e => e.FILE_CONTENT)
.FirstOrDefault(a => a.Id == fileId);
}
}
public GROUP GetGroupById(int id)
{
using (var context = new OpletalovaDbEntities())
{
var user = context.GROUP.FirstOrDefault(u => u.Id == id);
if (user == null)
throw new Exception(string.Format("User not found! Id: {0}", id));
return user;
}
}
public void DeleteForumItem(int forumItemId)
{
using (var context = new OpletalovaDbEntities())
{
var forumItem = context.FORUM_ITEM.FirstOrDefault(f => f.Id == forumItemId);
if (forumItem == null)
return;
context.FORUM_ITEM.Remove(forumItem);
context.SaveChanges();
}
}
public IEnumerable<EventForumItemInfo> GetEvetnForumItemsInfo(int currentUser, string eventIdInStringList, string separator)
{
using (var context = new OpletalovaDbEntities())
{
var returnedDbData = context.GET_EVENT_FORUM_ITEM_INFO(currentUser, eventIdInStringList, separator).ToList();
return (from i in returnedDbData select new EventForumItemInfo()
{
EventId = i.EVENT_ID,
LastId = i.LAST_ID.Value,
NotVisited = i.NOT_VISITED.Value,
Total = i.TOTAL.Value
}).ToList();
}
}
public void DeleteGroupById(int id)
{
using (var context = new OpletalovaDbEntities())
{
var group = context.GROUP
.Include(g => g.USER)
.Include(g => g.RIGHT)
.FirstOrDefault(g => g.Id == id);
if (group == null)
throw new Exception("Skupina již v DB neexistuje");
context.GROUP.Remove(group);
context.SaveChanges();
}
}
public void DeleteFile(int fileId)
{
using (var context = new OpletalovaDbEntities())
{
var fileContent = context.FILE_CONTENT.FirstOrDefault(f => f.FileInfoId == fileId);
var fileInfo = context.FILE_INFO.FirstOrDefault(f => f.Id == fileId);
if (fileContent != null)
context.FILE_CONTENT.Remove(fileContent);
if (fileInfo != null)
context.FILE_INFO.Remove(fileInfo);
context.SaveChanges();
}
}
public int CreateEvent(EVENT newEvent)
{
if (newEvent.Creator <= 0)
throw new Exception("Nepřihlášený uživatel nemůže vytvářet události! (Sytémové omezení)");
using (var context = new OpletalovaDbEntities())
{
newEvent.USER = context.USER.First(u => u.Id == newEvent.USER.Id);
newEvent.EVENT_TYPE = context.EVENT_TYPE.First(t => t.Id == newEvent.EVENT_TYPE.Id);
newEvent.EVENT_ACCESSIBILITY = context.EVENT_ACCESSIBILITY.First(a => a.Id == newEvent.EVENT_ACCESSIBILITY.Id);
context.EVENT.Add(newEvent);
context.SaveChanges();
return newEvent.Id;
}
}
public void DeleteEventById(int id)
{
using (var context = new OpletalovaDbEntities())
{
Locator.GetDbFileManager().DeleteAllEventFiles(id);
var delEvent = context.EVENT
.Include(e => e.FILE_INFO)
.Include(e => e.FORUM_ITEM)
.Include(e => e.USER_LAST_FORUM_ITEM)
.FirstOrDefault(e => e.Id == id);
if (delEvent == null)
return;
context.EVENT.Remove(delEvent);
context.SaveChanges();
}
}
public void DeleteAllEventFiles(int eventId)
{
using (var context = new OpletalovaDbEntities())
{
var dbEvent = context.EVENT
.Include(e => e.FILE_INFO)
.FirstOrDefault(e => e.Id == eventId);
if (dbEvent == null)
return;
while (dbEvent.FILE_INFO.Any())
{
context.FILE_INFO.Remove(dbEvent.FILE_INFO.First());
}
context.SaveChanges();
}
}
public EVENT_TYPE GetEventTypeById(int id)
{
using (var context = new OpletalovaDbEntities())
{
return context.EVENT_TYPE.FirstOrDefault(et => et.Id == id);
}
}
public EVENT GetEventById(int id)
{
using (var context = new OpletalovaDbEntities())
{
var evnt = context.EVENT
.Include(e => e.USER)
.Include(e => e.EVENT_TYPE)
.Include(e => e.EVENT_ACCESSIBILITY)
.Include(e => e.FILE_INFO)
.FirstOrDefault(a => a.Id == id);
// other layers must solve problem when event is null
return evnt;
}
}
public List<EVENT_TYPE> GetEventTypeList()
{
using (var context = new OpletalovaDbEntities())
{
return context.EVENT_TYPE.ToList();
}
}
public FORUM_ITEM GetContreteForumItem(int forumItemId)
{
using (var context = new OpletalovaDbEntities())
{
var forumItem = context.FORUM_ITEM
.Include(fi => fi.USER)
.FirstOrDefault(f => f.Id == forumItemId);
if (forumItem == null)
throw new Exception("Příspěvek nenalezen!");
return forumItem;
}
}
public void UpdateForumItem(int forumItemId, string forumItemText, int creatorId)
{
if (creatorId <= 0)
throw new Exception("Nepříhlášený uživatel nemá právo aktualizovat příspěvky ve fóru! (systémové omezení)");
using (var context = new OpletalovaDbEntities())
{
var forumItem = context.FORUM_ITEM.FirstOrDefault(x => x.Id == forumItemId);
if (forumItem == null)
throw new Exception("Příspěvek nenalezen (nelze aktualizovat!)");
var user = context.USER.FirstOrDefault(u => u.Id == creatorId);
if (user == null)
throw new Exception("Uživatel nenalezen (příspěvek nelze aktualizovat!)");
forumItem.USER = user;
forumItem.Message = forumItemText;
context.SaveChanges();
}
}
public List<GROUP> GetGroupListIncludeUsers(int id)
{
using (var context = new OpletalovaDbEntities())
{
var groups = context.GROUP
.Include(g => g.RIGHT)
.Include(g => g.USER);
if (id > 0)
groups = groups.Where(g => g.Id == id);
return groups.ToList();
}
}
public void SetLastVisitedForumItemId(int loggedUserId, int eventId, int forumItemId = -1)
{
// when nobody is logged in (public person) than we do not insert record to remember which forum item was last visited!
using (var context = new OpletalovaDbEntities())
{
// when we set forumItemId to -1 it means that we want to set last forum item for currently logged user
// but if there is not any forum item we should set 0
if (forumItemId < 0)
{
var forumItem = context.FORUM_ITEM.Where(fi => fi.EventId == eventId)
.OrderByDescending(fi => fi.Id)
.FirstOrDefault();
forumItemId = forumItem == null ? 0 : forumItem.Id;
}
var lastVisitedForumItem = context.USER_LAST_FORUM_ITEM.FirstOrDefault(x => x.UserId == loggedUserId && x.EventId == eventId);
if (lastVisitedForumItem == null)
{
var evnt = context.EVENT.First(e => e.Id == eventId);
var user = context.USER.First(u => u.Id == loggedUserId);
lastVisitedForumItem = new USER_LAST_FORUM_ITEM()
{
EVENT = evnt,
USER = user,
EventId = evnt.Id,
UserId = user.Id,
LastViewedForumItemId = forumItemId
};
context.USER_LAST_FORUM_ITEM.Add(lastVisitedForumItem);
}
else
{
lastVisitedForumItem.LastViewedForumItemId = forumItemId;
}
context.SaveChanges();
}
}
public int GetLastVisitedForumItemId(int loggedUserId, int eventId)
{
// when nobody is logged in (public person) we will return int.max so this person will never have any unread forum item
using (var context = new OpletalovaDbEntities())
{
var lastVisitedForumItem = context.USER_LAST_FORUM_ITEM.FirstOrDefault(x => x.UserId == loggedUserId && x.EventId == eventId);
if (lastVisitedForumItem == null)
return 0;
return lastVisitedForumItem.LastViewedForumItemId;
}
}
public EventListDTO GetEventList(EventDbFilter dbFilter, DatabasePager pager)
{
using (var context = new OpletalovaDbEntities())
{
var query = context.EVENT
.OrderByDescending(e => e.From)
.ThenBy(e => e.To)
.AsQueryable();
// file names are in different table with 1:N relation (not M:N!)
// I am sure it can be done another way but this is fast!
if (!String.IsNullOrEmpty(dbFilter.FileName))
{
var eventIds =
context.FILE_INFO.Where(f => f.FileName.Contains(dbFilter.FileName))
.Select(f => f.EventId)
.ToList();
query = query.Where(e => eventIds.Contains(e.Id));
}
if (!String.IsNullOrEmpty(dbFilter.ActionName))
query = query.Where(q => q.Name.Contains(dbFilter.ActionName));
if (dbFilter.EventTypeId.HasValue)
query = query.Where(q => q.Type == dbFilter.EventTypeId.Value);
query = dbFilter.AccessibilityId.HasValue
? query.Where(q => q.Accessibility == dbFilter.AccessibilityId.Value)
: query.Where(q => dbFilter.UserAccessibilitiesByRight.Contains(q.Accessibility));
if (dbFilter.CreatorId.HasValue)
query = query.Where(q => q.USER.Id == dbFilter.CreatorId.Value);
query = dbFilter.IsFindNewer
? query.Where(q => q.From >= dbFilter.PilotTime)
: query.Where(q => q.From <= dbFilter.PilotTime);
// we need to know how many records are ther in total (to be able set paging)
var totalRecords = query.Count();
// lets return only records which we want to show
var skipNum = pager.SelectedPageIndex * pager.PageSize;
if (skipNum < 0)
skipNum = 0;
query = query
.Include(e => e.USER)
.Include(e => e.EVENT_ACCESSIBILITY)
.Include(e => e.EVENT_TYPE)
.Include(e => e.FILE_INFO)
.Skip(skipNum) // how many records we should escape
.Take(pager.PageSize); // how many rows we should returned
return new EventListDTO()
{
Eventlist = query.ToList(),
TotalRecords = totalRecords
};
}
}
public void UpdateEvent(EVENT updEvent)
{
using (var context = new OpletalovaDbEntities())
{
var dbEvent = context.EVENT.First(e => e.Id == updEvent.Id);
dbEvent.Created = updEvent.Created;
dbEvent.Creator = updEvent.Creator;
dbEvent.From = updEvent.From;
dbEvent.Name = updEvent.Name;
dbEvent.Text = updEvent.Text;
dbEvent.To = updEvent.To;
dbEvent.Type = updEvent.EVENT_TYPE.Id;
dbEvent.Accessibility = updEvent.EVENT_ACCESSIBILITY.Id;
if (updEvent.FILE_INFO != null)
dbEvent.FILE_INFO = updEvent.FILE_INFO;
context.SaveChanges();
}
}
public void UpdateUser2Group(List<GroupItemSetting> usrs2Groups)
{
using (var context = new OpletalovaDbEntities())
{
foreach (var groupSetting in usrs2Groups)
{
var dbGroup = context.GROUP.Include(u => u.USER).FirstOrDefault(g => g.Id == groupSetting.GroupId);
if (dbGroup == null)
continue;
foreach (var userSetting in groupSetting.GroupUserListSetting)
{
var dbUser = context.USER.FirstOrDefault(u => u.Id == userSetting.ItemId);
if (dbUser == null)
continue;
var isUserInGroup = dbGroup.USER.FirstOrDefault(u => u.Id == dbUser.Id) != null;
if (userSetting.IsInGroup)
{
if (isUserInGroup)
continue;
dbUser.GROUP.Add(dbGroup);
dbGroup.USER.Add(dbUser);
}
else
{
if (!isUserInGroup)
continue;
dbUser.GROUP.Add(dbGroup);
dbGroup.USER.Remove(dbUser);
}
}
}
context.SaveChanges();
}
}
public ForumItemListDTO GetForumList(int eventId, DatabasePager pager)
{
using (var context = new OpletalovaDbEntities())
{
var skipNum = pager.SelectedPageIndex*pager.PageSize;
if (skipNum < 0)
skipNum = 0;
var dto = new ForumItemListDTO();
var query = context.FORUM_ITEM
.Where(fi => fi.EventId == eventId);
dto.TotalRecords = query.Count();
dto.ForumItemList = query
.Include(fi => fi.USER)
.OrderByDescending(fi => fi.Created)
.Skip(skipNum) // how many records we should escape
.Take(pager.PageSize).ToList(); // how many rows we should returned
return dto;
}
}
public List<EVENT> GetEventList(int count)
{
using (var context = new OpletalovaDbEntities())
{
var list = context.EVENT
.Include(e => e.USER)
.Include(e => e.EVENT_ACCESSIBILITY)
.Include(e=> e.EVENT_TYPE)
.Include(e=> e.FILE_INFO)
.Take(count)
.OrderByDescending(e => e.From)
.ThenBy(e => e.To)
.ToList();
return list;
}
}
public List<EVENT_ACCESSIBILITY> GetEventAccessibilityList(short[] accessibilityIds)
{
if (accessibilityIds == null || accessibilityIds.Count() <= 0)
return new List<EVENT_ACCESSIBILITY>();
using (var context = new OpletalovaDbEntities())
{
return context.EVENT_ACCESSIBILITY
.Where(a => accessibilityIds.Contains(a.Id))
.ToList();
}
}
public void UpdateRight2Group(List<GroupItemSetting> rights2Groups)
{
using (var context = new OpletalovaDbEntities())
{
foreach (var groupSetting in rights2Groups)
{
var dbGroup = context.GROUP.Include(r => r.RIGHT).FirstOrDefault(g => g.Id == groupSetting.GroupId);
if (dbGroup == null)
continue;
foreach (var rightSetting in groupSetting.GroupUserListSetting)
{
var dbRight = context.RIGHT.FirstOrDefault(r => r.Id == rightSetting.ItemId);
if (dbRight == null)
continue;
var isUserInGroup = dbGroup.RIGHT.FirstOrDefault(r => r.Id == dbRight.Id) != null;
if (rightSetting.IsInGroup)
{
if (isUserInGroup)
continue;
dbRight.GROUP.Add(dbGroup);
dbGroup.RIGHT.Add(dbRight);
}
else
{
if (!isUserInGroup)
continue;
dbRight.GROUP.Add(dbGroup);
dbGroup.RIGHT.Remove(dbRight);
}
}
}
context.SaveChanges();
}
}
