public List<User> Select(UserFilter filter)
{
if (filter == null) return null;
List<User> list = null;
using (var db = new DozpContext())
{
list = (from u in db.Users.Include(e => e.Roles)
where (String.IsNullOrEmpty(filter.UserName) || u.UserName == filter.UserName) &&
(0 == filter.InstitutionID || u.InstitutionID == filter.InstitutionID) &&
(0 == filter.CatalogueID || u.Institution.Catalogues.Count(c => c.CatalogueID == filter.CatalogueID) > 0) &&
(String.IsNullOrEmpty(filter.RoleName) || u.Roles.Count(r => r.RoleName == filter.RoleName) == 1) &&
(!filter.IsApproved.HasValue || u.IsApproved == filter.IsApproved.Value) &&
(u.UserName != "system")
orderby u.FullName
select u).ToList();
}
if (list != null)
{
foreach (var item in list)
{
SetUserRoleName(item);
}
}
return list;
}
public List<Book> Select(BookFilter filter)
{
if (filter == null) return null;
List<Book> list = null;
using (var db = new DozpContext())
{
list = (from b in db.Books.Include(e => e.Catalogue).Include(e => e.ScanFiles)
where (0 == filter.BookID || b.BookID == filter.BookID) &&
(0 == filter.CatalogueID || b.CatalogueID == filter.CatalogueID) &&
(String.IsNullOrEmpty(filter.SysNo) || b.SysNo == filter.SysNo) &&
(String.IsNullOrEmpty(filter.ISBN) || b.Barcode == filter.ISBN) &&
(String.IsNullOrEmpty(filter.Barcode) || b.Barcode == filter.Barcode) &&
(!filter.Modified.From.HasValue || b.Modified >= filter.Modified.From.Value) &&
(!filter.Modified.To.HasValue || b.Modified <= filter.Modified.To.Value) &&
//(!filter.Modified.From.HasValue || b.ScanFiles.Count(f => f.Modified >= filter.Modified.From.Value) > 0) &&
//(!filter.Modified.To.HasValue || b.ScanFiles.Count(f => f.Modified <= filter.Modified.To.Value) > 0) &&
(!filter.PartOfBook.HasValue || b.ScanFiles.Count(f => f.PartOfBook == filter.PartOfBook.Value) > 0) &&
(!filter.UseOCR.HasValue || b.ScanFiles.Count(f => f.UseOCR == filter.UseOCR.Value) > 0) &&
(!filter.Status.HasValue || b.ScanFiles.Count(f => f.Status == filter.Status.Value) > 0)
select b).ToList();
}
return list;
}
public List<Institution> Select()
{
List<Institution> list = null;
using (var db = new DozpContext())
{
list = db.Institutions.Include(e => e.Catalogues).ToList();
}
return list;
}
public List<EventLog> Select()
{
List<EventLog> list = null;
using (var db = new DozpContext())
{
list = db.EventLogs.ToList();
}
return list;
}
public Operation Create(Operation operation)
{
if (operation == null) return null;
using (var db = new DozpContext())
{
db.Entry(operation).State = EntityState.Added;
db.SaveChanges();
}
return operation;
}
public ScanFile Create(ScanFile scanFile)
{
if (scanFile == null) return null;
using (var db = new DozpContext())
{
db.Entry(scanFile).State = EntityState.Added;
db.SaveChanges();
}
return scanFile;
}
public bool Delete(Book book)
{
if (book == null) return false;
using (var db = new DozpContext())
{
db.Entry(book).State = EntityState.Deleted;
db.SaveChanges();
}
return true;
}
public Institution Create(Institution institution)
{
if (institution == null) return null;
using (var db = new DozpContext())
{
db.Entry(institution).State = EntityState.Added;
db.SaveChanges();
}
return institution;
}
public EventLog Create(EventLog eventLog)
{
if (eventLog == null) return null;
using (var db = new DozpContext())
{
db.Entry(eventLog).State = EntityState.Added;
db.SaveChanges();
}
return eventLog;
}
public Book Create(Book book)
{
if (book == null) return null;
using (var db = new DozpContext())
{
db.Entry(book).State = EntityState.Added;
db.SaveChanges();
}
return book;
}
public bool Delete(EventLog eventLog)
{
if (eventLog == null) return false;
using (var db = new DozpContext())
{
db.Entry(eventLog).State = EntityState.Deleted;
db.SaveChanges();
}
return true;
}
public bool Delete(Institution institution)
{
if (institution == null) return false;
using (var db = new DozpContext())
{
db.Entry(institution).State = EntityState.Deleted;
db.SaveChanges();
}
return true;
}
public bool Delete(ScanFile scanFile)
{
if (scanFile == null) return false;
using (var db = new DozpContext())
{
db.ScanFiles.Attach(scanFile);
db.ScanFiles.Remove(scanFile);
db.SaveChanges();
}
return true;
}
public int GetCount(string userName)
{
int count = 0;
if (!String.IsNullOrEmpty(userName))
{
using (var db = new DozpContext())
{
count = db.Operations.Count(o => o.UserName == userName);
}
}
return count;
}
public EventLog Select(int eventLogID)
{
if (eventLogID == 0) return null;
EventLog item = null;
using (var db = new DozpContext())
{
item = db.EventLogs
.Where(pk => pk.EventLogID == eventLogID)
.SingleOrDefault();
}
return item;
}
public Institution Select(int institutionID)
{
if (institutionID == 0) return null;
Institution item = null;
using (var db = new DozpContext())
{
item = db.Institutions
.Include(e => e.Catalogues)
.Where(pk => pk.InstitutionID == institutionID)
.SingleOrDefault();
}
return item;
}
public List<Institution> Select(InstitutionFilter filter)
{
if (filter == null) return null;
List<Institution> list = null;
using (var db = new DozpContext())
{
list = (from i in db.Institutions.Include(e => e.Catalogues)
where (0 == filter.InstitutionID || i.InstitutionID == filter.InstitutionID) &&
(String.IsNullOrEmpty(filter.UserName) || i.Users.Count(r => r.UserName == filter.UserName) > 0) &&
(!filter.Active || i.Enabled == true)
select i).ToList();
}
return list;
}
public ScanFile Select(int scanFileID)
{
if (scanFileID == 0) return null;
ScanFile item = null;
using (var db = new DozpContext())
{
item = db.ScanFiles
.Include(e => e.Book.Catalogue)
.Include(e => e.Operations)
.Where(pk => pk.ScanFileID == scanFileID)
.SingleOrDefault();
}
return item;
}
public Book Select(int bookID)
{
if (bookID == 0) return null;
Book item = null;
using (var db = new DozpContext())
{
item = db.Books
.Include(e => e.Catalogue)
.Include(e => e.ScanFiles)
.Where(pk => pk.BookID == bookID)
.SingleOrDefault();
}
return item;
}
public User Select(string userName)
{
if (String.IsNullOrEmpty(userName)) return null;
User item = null;
using (var db = new DozpContext())
{
item = db.Users
.Include(e => e.Roles)
.Where(pk => pk.UserName == userName)
.SingleOrDefault();
}
SetUserRoleName(item);
return item;
}
public List<User> Select()
{
List<User> list = null;
using (var db = new DozpContext())
{
list = db.Users.Include(e => e.Roles).OrderBy(o => o.FullName).ToList();
}
if (list != null)
{
foreach (var item in list)
{
SetUserRoleName(item);
}
}
return list;
}
public List<ScanFile> Select(ScanFileFilter filter)
{
if (filter == null) return null;
List<ScanFile> list = null;
using (var db = new DozpContext())
{
list = (from f in db.ScanFiles.Include(e => e.Book.Catalogue)
where (0 == filter.ScanFileID || f.ScanFileID == filter.ScanFileID) &&
(0 == filter.BookID || f.BookID == filter.BookID) &&
(0 == filter.CatalogueID || f.Book.CatalogueID == filter.CatalogueID) &&
(String.IsNullOrEmpty(filter.SysNo) || f.Book.SysNo == filter.SysNo) &&
(!filter.PartOfBook.HasValue || f.PartOfBook == filter.PartOfBook.Value) &&
(!filter.UseOCR.HasValue || f.UseOCR == filter.UseOCR.Value) &&
(!filter.Modified.From.HasValue || f.Modified >= filter.Modified.From.Value) &&
(!filter.Modified.From.HasValue || f.Modified <= filter.Modified.To.Value) &&
(!filter.Status.HasValue || f.Status == filter.Status.Value) &&
(String.IsNullOrEmpty(filter.UserName) || f.Operations.Count(o => o.UserName == filter.UserName) > 0)
select f).ToList();
}
return list;
}
/*
SELECT * FROM ScanFile WHERE contains(OcrText, 'Masarykova AND Evropa')
SELECT * FROM ScanFile WHERE freetext(OcrText, 'Stát národ')
SELECT * FROM freetexttable(ScanFile, OcrText, 'Masarykova Evropa') AS t JOIN ScanFile a ON t.[KEY] = a.ScanFileID ORDER BY t.[RANK] Desc
*/
public List<ScanFile> Select(int institutionID, string text)
{
if (String.IsNullOrEmpty(text)) return null;
List<ScanFile> list = null;
//DbInterception.Add(new FtsInterceptor());
//string s = FtsInterceptor.Fts(text);
using (var db = new DozpContext())
{
list = (from f in db.ScanFiles.Include(e => e.Book.Catalogue)
where (f.Book.SysNo == text) ||
(f.Book.ISBN == text) ||
(f.Book.Barcode == text) ||
(f.Book.Author.Contains(text)) ||
(f.Book.Title.Contains(text)) ||
(f.OcrText.Contains(text))
select f).ToList();
}
return list;
}
internal bool Delete(Operation operation)
{
if (operation == null) return false;
using (var db = new DozpContext())
{
db.Entry(operation).State = EntityState.Deleted;
db.SaveChanges();
}
return true;
}
internal Operation Update(Operation operation)
{
if (operation == null) return null;
using (var db = new DozpContext())
{
db.Entry(operation).State = EntityState.Modified;
db.SaveChanges();
}
return operation;
}
public FileSumList GetTimeStatistics(StatisticsFilter filter)
{
if (filter == null) return null;
FileSumList list = new FileSumList();
using (var db = new DozpContext())
{
if (!filter.Year.HasValue && !filter.Month.HasValue && !filter.Day.HasValue)
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value)
group o by new { o.ScanFile.PartOfBook, o.Status, o.Executed.Year } into g
orderby g.Key.Year, g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
year = g.Key.Year,
count = g.Count()
};
foreach (var o in stat)
{
FileSumItem item = list.SingleOrDefault(s => s.Year == o.year);
if (item == null)
{
item = new FileSumItem(o.year);
list.Add(item);
}
item.SetVaule(o.partOfBook, o.status, o.count);
}
}
else if (filter.Year.HasValue && !filter.Month.HasValue && !filter.Day.HasValue)
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value) &&
(filter.Year.Value == o.Executed.Year)
group o by new { o.ScanFile.PartOfBook, o.Status, o.Executed.Year, o.Executed.Month } into g
orderby g.Key.Year, g.Key.Month, g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
year = g.Key.Year,
month = g.Key.Month,
count = g.Count()
};
for (int month = 1; month <= 12; month++)
{
if (filter.Year.Value < DateTime.Now.Year || month <= DateTime.Now.Month)
{
list.Add(new FileSumItem(filter.Year.Value, month));
}
}
foreach (var o in stat)
{
FileSumItem item = list.SingleOrDefault(s => s.Year == o.year && s.Month == o.month);
if (item == null)
{
item = new FileSumItem(o.year, o.month);
list.Add(item);
}
item.SetVaule(o.partOfBook, o.status, o.count);
}
}
else if (filter.Year.HasValue && filter.Month.HasValue && !filter.Day.HasValue)
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value) &&
(filter.Year.Value == o.Executed.Year && filter.Month.Value == o.Executed.Month)
group o by new { o.ScanFile.PartOfBook, o.Status, o.Executed.Year, o.Executed.Month, o.Executed.Day } into g
orderby g.Key.Year, g.Key.Month, g.Key.Day, g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
year = g.Key.Year,
month = g.Key.Month,
day = g.Key.Day,
count = g.Count()
};
for (int day = 1; day <= DateTime.DaysInMonth(filter.Year.Value, filter.Month.Value); day++)
{
if (new DateTime(filter.Year.Value, filter.Month.Value, day) <= DateTime.Now)
{
list.Add(new FileSumItem(filter.Year.Value, filter.Month.Value, day));
}
}
foreach (var o in stat)
{
FileSumItem item = list.SingleOrDefault(s => s.Year == o.year && s.Month == o.month && s.Day == o.day);
if (item == null)
{
item = new FileSumItem(o.year, o.month, o.day);
list.Add(item);
}
item.SetVaule(o.partOfBook, o.status, o.count);
}
}
else if (filter.Year.HasValue && filter.Month.HasValue && filter.Day.HasValue)
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value) &&
(filter.Year.Value == o.Executed.Year && filter.Month.Value == o.Executed.Month && filter.Day.Value == o.Executed.Day)
group o by new { o.ScanFile.PartOfBook, o.Status, o.Executed.Year, o.Executed.Month, o.Executed.Day } into g
orderby g.Key.Year, g.Key.Month, g.Key.Day, g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
year = g.Key.Year,
month = g.Key.Month,
day = g.Key.Day,
count = g.Count()
};
foreach (var o in stat)
{
FileSumItem item = list.SingleOrDefault(s => s.Year == o.year && s.Month == o.month && s.Day == o.day);
if (item == null)
{
item = new FileSumItem(o.year, o.month, o.day);
list.Add(item);
}
item.SetVaule(o.partOfBook, o.status, o.count);
}
}
else
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.PartOfBook.HasValue || o.ScanFile.PartOfBook == filter.PartOfBook.Value) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value)
group o by new { o.ScanFile.PartOfBook, o.Status } into g
orderby g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
count = g.Count()
};
FileSumItem item = new FileSumItem("summary", "Celkem");
foreach (var o in stat)
{
item.SetVaule(o.partOfBook, o.status, o.count);
list.Add(item);
}
}
}
return list;
}
public Institution Update(Institution institution)
{
if (institution == null) return null;
//try
//{
using (var db = new DozpContext())
{
db.Entry(institution).State = EntityState.Modified;
db.SaveChanges();
}
//}
//catch (DbEntityValidationException ex)
//{
// StringBuilder sb = new StringBuilder();
// foreach (var result in ex.EntityValidationErrors)
// {
// sb.AppendLine(string.Format(ErrorMessage.ENTITY_VALIDATION_ERROR, result.Entry.Entity.GetType().Name, result.Entry.State));
// foreach (var error in result.ValidationErrors)
// {
// sb.AppendLine(String.Format(ErrorMessage.PROPERTY_VALIDATION_ERROR, error.PropertyName, error.ErrorMessage));
// }
// }
// throw new DbEntityValidationException(sb.ToString(), ex);
//}
return institution;
}
public List<Operation> Select(int scanFileID)
{
List<Operation> list = null;
using (var db = new DozpContext())
{
list = (from f in db.Operations
where f.ScanFileID == scanFileID
select f).ToList();
}
return list;
}
public User Update(User user)
{
if (user == null) return null;
using (var db = new DozpContext())
{
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
}
return user;
}
public FileSumList GetUserStatistics(StatisticsFilter filter)
{
if (filter == null) return null;
FileSumList list = new FileSumList();
using (var db = new DozpContext())
{
var stat = from o in db.Operations
where (0 == filter.CatalogueID || o.ScanFile.Book.CatalogueID == filter.CatalogueID) &&
(!filter.PartOfBook.HasValue || o.ScanFile.PartOfBook == filter.PartOfBook.Value) &&
(!filter.UseOCR.HasValue || o.ScanFile.UseOCR == filter.UseOCR.Value) &&
(!filter.Year.HasValue || o.Executed.Year == filter.Year.Value) &&
(!filter.Month.HasValue || o.Executed.Month == filter.Month.Value) &&
(!filter.Day.HasValue || o.Executed.Day == filter.Day.Value) &&
(String.IsNullOrEmpty(filter.UserName) || o.UserName == filter.UserName) &&
(!filter.Status.HasValue || o.Status == filter.Status.Value)
group o by new { o.ScanFile.PartOfBook, o.Status, o.UserName, o.User.FullName, o.User.Comment } into g
orderby g.Key.FullName, g.Key.PartOfBook, g.Key.Status
select new
{
partOfBook = g.Key.PartOfBook,
status = g.Key.Status,
username = g.Key.UserName,
fullname = g.Key.FullName,
comment = g.Key.Comment,
pages = g.Sum(e => e.ScanFile.PageCount),
seconds = g.Sum(e => e.ScanFile.OcrTime),
count = g.Count()
};
foreach (var o in stat)
{
FileSumItem item = list.SingleOrDefault(s => s.Key == o.username);
if (item == null)
{
item = new FileSumItem(o.fullname, o.username, o.comment);
list.Add(item);
}
item.SetVaule(o.partOfBook, o.status, o.count, o.pages, o.seconds);
}
}
return list;
}
