/// <summary>
/// Get the end grade of a group
/// </summary>
/// <param name="studentNumber", "GroupId">The student you want the grade from, the group he belonged to during the project</param>
/// <returns>Returns the final group grade as an integer</returns>
public int getEndGradeGroup(int studentNumber, int groupId)
{
if (studentNumber > 0 && groupId >= 0)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
var endGroupGrade =
(from u in context.Users
join pm in context.ProjectMembers on u.id equals pm.student_user_id
join pg in context.ProjectGroups on pm.project_group_id equals pg.project_id
join p in context.Projects on pg.project_id equals p.id
where u.student_number == studentNumber && pg.id == groupId
select pg.group_end_grade).SingleOrDefault();
if (endGroupGrade != null)
{
return((int)endGroupGrade);
}
else
{
return(0);
}
}
else
{
throw new ArgumentOutOfRangeException();
}
}
//Dient als voorbeeld! Moet nog gevuld worden
/// <summary>
/// Get all grades belonging to a student in a project
/// </summary>
/// <param name="studentNumber">The student you want to query</param>
/// <param name="projectId">The project of which you like to see the grades</param>
/// <returns></returns>
public IQueryable <Report> getStudentGrades(int studentNumber, int projectId)
{
if (studentNumber > 0 && projectId > 0)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
var report =
from r in context.Reports
join prd in context.ProjectReportDates on r.project_report_date_id equals prd.id
join u in context.Users on r.reporter_id equals u.id
where u.student_number == studentNumber && prd.project_id_int == projectId
select r;
if (report.ToList().Count > 0)
{
return(report);
}
else
{
throw new DataException("No data found for valid argument");
}
}
else
{
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Get all grades belonging to a student in a project
/// </summary>
/// <param name="studentNumber">The student you want to query</param>
/// <param name="projectId">The project of which you like to see the grades</param>
/// <returns></returns>
public IQueryable <BaroTemplate> getTemplate(int templateNumber)
{
if (templateNumber > 0)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
var template =
from bt in context.BaroTemplates
join ba in context.BaroAspects on bt.id equals ba.baro_template_id
where bt.id == templateNumber
select bt;
if (template.ToList().Count > 0)
{
return(template);
}
else
{
throw new DataException("No data found for valid argument");
}
}
else
{
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Get the results of the student of a single project
/// </summary>
/// <param name="studentNumber">The student and the project you want to querry</param>
/// <returns></returns>
public IQueryable <ProjectGroup> getReportResults(int projectID)
{
if (projectID >= 0)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
var results =
from pg in context.ProjectGroups
join pm in context.ProjectMembers on pg.id equals pm.project_group_id
join u in context.Users on pm.student_user_id equals u.id
join p in context.Projects on pg.project_id equals p.id
where p.id == projectID &&
p.status_name == "Closed"
select pg;
//if (results.ToList().Count > 0)
//{
return(results);
//}
//else
//{
// throw new DataException("No data found for valid argument");
//}
}
else
{
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Singleton pattern to get the DataContext Object
/// </summary>
/// <returns>DatabaseClassesDataContext</returns>
public DatabaseClassesDataContext getDataContext()
{
if (DataContext == null)
{
DataContext = new DatabaseClassesDataContext();
}
return(DataContext);
}
/// <summary>
/// Singleton pattern to get the DataContext Object
/// </summary>
/// <returns>DatabaseClassesDataContext</returns>
public DatabaseClassesDataContext getDataContext()
{
if (DataContext == null)
{
DataContext = new DatabaseClassesDataContext();
}
return DataContext;
}
public void test_02_datacontext_is_singleton()
{
DatabaseFactory d = DatabaseFactory.getInstance();
DatabaseClassesDataContext a = d.getDataContext();
DatabaseClassesDataContext b = d.getDataContext();
Assert.AreEqual(a, b);
}
public void CreateUser(User userToCreate)
{
using (var context = new DatabaseClassesDataContext(DatabaseConnectionString))
{
context.Users.InsertOnSubmit(userToCreate);
context.SubmitChanges();
}
}
public User getStudentInfo(int studentNumber)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
var student =
from u in context.Users
where u.student_number == studentNumber
select u;
return(student.FirstOrDefault());
}
//проверяет сущуствование файла имени
public bool Exist(int directoryId, string fileName, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from f in db.Files
where f.Name == fileName && f.DirectoryId == directoryId && f.UserId == userId && f.IsActual == true
select f;
if (result.Count<File>() > 0)
return true;
return false;
}
//проверяет сущуствование файла по FileId
public bool ExistById(int fileId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from f in db.Files
where f.FileId == fileId && f.UserId == userId
select f;
if (result.Count<File>() > 0)
return true;
return false;
}
//проверяет сущуствование файла по имени
public bool ExistByName(string name, int directoryId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Files
where d.DirectoryId == directoryId && d.Name == name && d.UserId == userId && d.IsActual == true
select d;
if (result.Count<File>() > 0)
return true;
return false;
}
//добавляет новое событие
public void CreateEvent(int userId, string description, int fileId = 0)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
Event newEvent = new Event();
newEvent.UserId = userId;
newEvent.FileId = fileId;
newEvent.Description = description;
newEvent.Created = DateTime.Now;
db.Events.InsertOnSubmit(newEvent);
db.SubmitChanges();
}
//-------------------------------------------------------------------
//Проверяет существование пользователя
public bool Exist(string Email, string Passwd)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from u in db.Users
where u.Email == Email && u.Passwd == Passwd
select u;
if (result.Count<User>() > 0)
return true;
return false;
}
public User Get(int userId)
{
using (var context = new DatabaseClassesDataContext(DatabaseConnectionString))
{
var user = context.Users
.FirstOrDefault(x => x.Id == userId);
return user;
}
}
//Возвращает данные пользователя
public User GetUser(string Email, string Passwd)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from u in db.Users
where u.Email == Email && u.Passwd == Passwd
select u;
if (result.Count<User>() > 0)
return (result).First<User>();
throw new Exception("UserNotExist");
}
//удаляет файл
public void DeleteFile(int fileId, int userId)
{
try
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var file = (from f in db.Files
where f.FileId == fileId && f.UserId == userId && f.IsActual == true
select f).Single();
db.Files.DeleteOnSubmit(file);
db.SubmitChanges();
}
catch { };
}
//создает файл
public void CreateFile(int userId, string name, string physicalPath, int directoryId, bool isPublic, long size)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
File newFile = new File();
newFile.UserId = userId;
newFile.Name = name;
newFile.PhysicalPath = physicalPath;
newFile.IsPublic = isPublic;
newFile.IsActual = true;
newFile.DirectoryId = directoryId;
newFile.LastWrite = DateTime.Now;
newFile.Size = size;
db.Files.InsertOnSubmit(newFile);
db.SubmitChanges();
}
//создает директорию по имени и parentId
public int CreateDirectoryByName(int userId, string name, int parentId, bool isPublic)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
Directory newDirectory = new Directory();
newDirectory.UserId = userId;
newDirectory.Name = name;
newDirectory.IsPublic = isPublic;
newDirectory.IsActual = true;
newDirectory.ParentId = parentId;
newDirectory.Created = DateTime.Now;
db.Directories.InsertOnSubmit(newDirectory);
db.SubmitChanges();
return newDirectory.DirectoryId;
}
//создает директорию
public int CreateDirectory(int userId, string patch, bool isPublic)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
Directory parentDirectory = GetParentDirectory(patch, userId);
Directory newDirectory = new Directory();
newDirectory.UserId = userId;
newDirectory.Name = patch.Remove(0, patch.LastIndexOf('\\')+1);
newDirectory.IsPublic = isPublic;
newDirectory.IsActual = true;
newDirectory.ParentId = parentDirectory.DirectoryId;
newDirectory.Created = DateTime.Now;
db.Directories.InsertOnSubmit(newDirectory);
db.SubmitChanges();
return newDirectory.DirectoryId;
}
//----------------------------------------------------------------------------------
//возвращает все события пользователя
public List<EventInfo> GetEvents(int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var userEvents = from f in db.Events
where f.UserId == userId
select f;
List<EventInfo> changes = new List<EventInfo>();
foreach (Event userEvent in userEvents)
changes.Add(new EventInfo {
FileId = userEvent.FileId,
Description=userEvent.Description,
Path = DirectoryModel.Instance.GetDirectoryPath(userEvent.File.DirectoryId, userId),
FileSize=userEvent.File.Size,
Created=userEvent.Created,
FileName=userEvent.File.Name
});
return changes;
}
// каталог
public int Copy(int directoryId, int outDirectoryId, int userId)
{
Directory directoryInfo = DirectoryModel.Instance.GetDirectoryById(directoryId, userId);
Directory parentDirectoryInfo = DirectoryModel.Instance.GetDirectoryById(outDirectoryId, userId);
string directoryName = directoryInfo.Name;
int i = 1;
while (ExistByName(directoryName, outDirectoryId, userId))
{
directoryName = directoryInfo.Name + string.Format("({0})", ++i);
}
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existDirectory = (from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId
select d).Single();
return CreateDirectoryByName(userId, directoryName, outDirectoryId,false);
}
public Dictionary <string, string> getName(int studentNumber)
{
if (studentNumber > 0)
{
DatabaseClassesDataContext context = DatabaseFactory.getInstance().getDataContext();
Dictionary <string, string> fullName = new Dictionary <string, string>();
var name =
from u in context.Users
where u.student_number == studentNumber
select new
{
u.firstname,
u.lastname
};
foreach (var v in name)
{
if (v.firstname != null && v.lastname != null)
{
fullName.Add(v.firstname, v.lastname);
}
else
{
fullName.Add("Naam", "Onbekend");
}
}
if (fullName.Count > 0)
{
return(fullName);
}
else
{
throw new DataException("No data found for valid argument");
}
}
else
{
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Get the projectgroup(s) the student belongs to
/// </summary>
/// <param name="studentNumber">The student you want to query</param>
/// <returns></returns>
public IQueryable <ProjectGroup> getStudentGroup(int studentNumber)
{
if (studentNumber > 0)
{
DatabaseFactory factory = DatabaseFactory.getInstance();
DatabaseClassesDataContext context = factory.getDataContext();
var projectGroup =
from pg in context.ProjectGroups
join pm in context.ProjectMembers on pg.id equals pm.project_group_id
join u in context.Users on pm.student_user_id equals u.id
where u.student_number == studentNumber
select pg;
if (projectGroup.ToList().Count > 0)
{
return(projectGroup);
}
return(null);
}
else
{
throw new ArgumentOutOfRangeException();
}
}
public void putStudentInDatabase(int studentNumber, string email)
{
DatabaseFactory factory = DatabaseFactory.getInstance();
DatabaseClassesDataContext context = factory.getDataContext();
User newUser;
bool userExists = false;
var usertest = from u in context.Users
where u.student_number == studentNumber
select u;
if (usertest.Count() == 0)
{
newUser = new User();
newUser.student_number = studentNumber;
newUser.email = email + "@avans.nl";
newUser.rol_name = "user";
}
else
{
userExists = true;
newUser = usertest.First();
}
CurrentUser.getInstance().Studentnummer = studentNumber;
CurrentUser.getInstance().Role = newUser.rol_name;
if (!userExists)
{
context.Users.InsertOnSubmit(newUser);
}
context.SubmitChanges();
CurrentUser.getInstance().StudentId = newUser.id;
}
//проверяет существование главной диретории
public bool ExistRootDirectory(int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Directories
where d.ParentId == 0 && d.Name == "" && d.UserId == userId
select d;
if (result.Count<Directory>() > 0)
return true;
return false;
}
//возвращает все файлы каталога
public File GetFileById(int fileId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from f in db.Files
where f.FileId == fileId && f.UserId == userId
select f;
if (result.Count<File>() > 0)
return (result).First<File>();
throw new Exception("FileNotExist");
}
//делает файл не активным
public void MakeFileNotActive(int fileId, int userId)
{
try
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from f in db.Files
where f.FileId == fileId && f.UserId == userId && f.IsActual == true
select f).Single();
existFile.IsActual = false;
db.SubmitChanges();
}
catch { };
}
//удаляет директорию
public void DeleteDirectory(int direstoryId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var deleteQuerry = from d in db.Directories
where d.DirectoryId == direstoryId && d.UserId == userId
select d;
if (deleteQuerry.Count<Directory>() > 0)
{
db.Directories.DeleteOnSubmit(deleteQuerry.First<Directory>());
db.SubmitChanges();
}
}
//перемещает файл в другой каталог
public void Move(int fileId, int outDirectoryId, int userId)
{
try
{
File fileInfo = GetFileById(fileId, userId);
string fileName = fileInfo.Name;
int i=1;
while (Exist(outDirectoryId, fileName, userId))
{
fileName = fileInfo.Name + string.Format("({0})", ++i);
}
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from f in db.Files
where f.FileId == fileId && f.UserId == userId && f.IsActual == true
select f).Single();
existFile.Name = fileName;
existFile.DirectoryId = outDirectoryId;
db.SubmitChanges();
}
catch { };
}
//переименование файла
public void Rename(int fileId, string newName, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from f in db.Files
where f.FileId == fileId && f.UserId == userId && f.IsActual == true
select f).Single();
existFile.Name = newName;
db.SubmitChanges();
}
//переименование каталога
public void Rename(int directoryId, string newName, int userId)
{
try
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId && d.ParentId != 0
select d).Single();
existFile.Name = newName;
db.SubmitChanges();
}
catch { };
}
//перемещает каталог
public void Move(int directoryId, int outDirectoryId, int userId)
{
try
{
Directory directoryInfo = DirectoryModel.Instance.GetDirectoryById(directoryId, userId);
Directory parentDirectoryInfo = DirectoryModel.Instance.GetDirectoryById(outDirectoryId, userId);
string directoryName = directoryInfo.Name;
int i = 1;
while (ExistByName(directoryName, outDirectoryId, userId))
{
directoryName = directoryInfo.Name + string.Format("({0})", ++i);
}
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId
select d).Single();
existFile.ParentId = outDirectoryId;
existFile.Name = directoryName;
db.SubmitChanges();
}
catch { };
}
//делает каталог не активным
public void MakeFileNotActive(int directoryId, int userId)
{
try
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var existFile = (from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId && d.ParentId!=0
select d).Single();
existFile.IsActual = false;
db.SubmitChanges();
}
catch { };
}
//возвращает главную директорию
public Directory GetRootDirectory(int userId)
{
if (!ExistRootDirectory(userId))
CreateDirectory(userId, "\\", false);
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Directories
where d.ParentId == 0 && d.UserId == userId && d.IsActual == true
select d;
if (result.Count<Directory>() > 0)
return (result).First<Directory>();
throw new Exception("DirectoryNotFound");
}
//возвращает все файлы каталога
public List<File> GetDirectoryFiles(int directoryId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var files = from f in db.Files
where f.DirectoryId == directoryId && f.UserId == userId && f.IsActual == true
select f;
List<File> childFiles = new List<File>();
foreach (File file in files)
childFiles.Add(file);
return childFiles;
}
//возвращает диреторию по ParentId
public Directory GetDirectoryByName(string name, int parentId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Directories
where d.ParentId == parentId && d.Name == name && d.UserId == userId && d.IsActual == true
select d;
if (result.Count<Directory>() > 0)
return (result).First<Directory>();
throw new Exception("DirectoryNotFound");
}
//возвращает директорию по DirectoryId
public Directory GetDirectoryById(int directoryId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId
select d;
if (result.Count<Directory>() > 0)
return (result).First<Directory>();
throw new Exception("DirectoryNotFound");
}
//возвращает дочерние директории
public List<Directory> GetChildDirectories(int parentId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var directories = from d in db.Directories
where d.ParentId == parentId && d.UserId == userId && d.IsActual == true
select d;
List<Directory> childDirectories = new List<Directory>();
foreach (Directory directory in directories)
childDirectories.Add(directory);
return childDirectories;
}
//проверяет сущуствование директории
public bool ExistById(int directoryId, int userId)
{
DatabaseClassesDataContext db = new DatabaseClassesDataContext();
var result = from d in db.Directories
where d.DirectoryId == directoryId && d.UserId == userId
select d;
if (result.Count<Directory>() > 0)
return true;
return false;
}
