/// <summary>
/// return the box or its first ancestor box where the predicate is true; or null
/// </summary>
public Box NavigateToParentBoxWhere(Box box, Func <Box, bool> predicate)
{
if (predicate(box))
{
return(box);
}
if (box.ParentId == null)
{
return(null);
}
using (var db = new SystematizerContext())
{
for (int i = 0; i < 10; ++i)
{
box = db.Box.Find(box.ParentId);
if (box == null)
{
return(null);
}
if (predicate(box))
{
return(box);
}
if (box.ParentId == null)
{
return(null);
}
}
}
return(null);
}
/// <summary>
/// add and delete PersonCat records so they match the given list for the person
/// </summary>
internal static void SavePersonCats(SystematizerContext db, long personId, long[] selectedCatIds)
{
var existing = db.PersonCat.Where(r => r.PersonId == personId).ToArray();
//remove deleted ones
foreach (var pc in existing)
{
if (!selectedCatIds.Contains(pc.CatId))
{
db.PersonCat.Remove(pc);
}
}
//add new ones
foreach (long id in selectedCatIds)
{
if (!existing.Any(r => r.CatId == id))
{
db.PersonCat.Add(new PersonCat {
PersonId = personId, CatId = id
});
}
}
db.SaveChanges();
}
/// <summary>
/// Get a warning message about deleting a category; call this before calling
/// DeleteCat. Returns null string if no warning needed.
/// </summary>
/// <returns>true if allowed to delete, and message to show user</returns>
public (bool, string) GetCategoryDeleteWarning(long rowId)
{
var cat = Globals.AllCats.Find(rowId);
if (cat == null)
{
return(false, "No such category");
}
if (cat.Children != null && cat.Children.Any())
{
return(false, "Category cannot be deleted because it has sub-categories. Delete the sub-categories first.");
}
using var db = new SystematizerContext();
int n = db.PersonCat.Count(r => r.CatId == rowId);
if (n == 0)
{
return(true, null);
}
if (cat.Parent == null)
{
return(true, $"Deleting top level. {n} records will have the category removed.");
}
else
{
return(true, $"Deleting sub-category. {n} records will be promoted to the containing category.");
}
}
/// <summary>
/// Reload the links in a box; to be called after the UI records changes in links
/// </summary>
public void UpdateLinks(ExtPerson ep)
{
using var db = new SystematizerContext();
var links = DBUtil.LoadLinksFor(db, ep.Person).ToList();
ep.Links = links;
}
/// <summary>
/// True if the box given by boxId,parentId has circular parentage or is nested more than 20 levels
/// </summary>
internal static bool HasCircularParentage(SystematizerContext db, long boxId, long?parentId)
{
if (parentId == null)
{
return(false);
}
if (parentId.Value == boxId)
{
return(true);
}
var encountered = new HashSet <long>
{
boxId,
parentId.Value
};
for (int i = 0; i < 20; ++i)
{
parentId = db.Box.Where(r => r.RowId == parentId.Value).Select(r => r.ParentId).FirstOrDefault();
if (parentId == null)
{
return(false);
}
if (encountered.Contains(parentId.Value))
{
return(true);
}
encountered.Add(parentId.Value);
}
return(true);
}
/// <summary>
/// Reload the links in a box; to be called after the UI records changes in links
/// </summary>
public void UpdateLinks(ExtBox ebox)
{
using var db = new SystematizerContext();
var links = DBUtil.LoadLinksFor(db, ebox.Box).ToList();
ebox.Links = links;
}
/// <summary>
/// Get all kinds of links from the given person.
/// </summary>
internal static IEnumerable <LinkRecord> LoadLinksFor(SystematizerContext db, Person person)
{
var ret = new List <LinkRecord>();
//boxes
var boxes = db.Box.FromSqlRaw("select Box.RowId,Title from Box inner join BoxPerson on Box.RowId=BoxPerson.BoxId where DoneDate is null and PersonId=" + person.RowId)
.Select(r => new { r.RowId, r.Title });
foreach (var b2 in boxes)
{
ret.Add(new LinkRecord
{
Link = LinkType.FromPersonToBox,
OtherId = b2.RowId,
Description = b2.Title
});
}
//persons
var persons = db.Person.FromSqlRaw("select Person.RowId,Name from Person inner join PersonPerson on Person.RowId=PersonPerson.Person1Id where Person2Id=" + person.RowId)
.Select(r => new { r.RowId, r.Name });
foreach (var person2 in persons)
{
ret.Add(new LinkRecord
{
Link = LinkType.FromPersonToPerson,
OtherId = person2.RowId,
Description = person2.Name
});
}
return(ret);
}
/// <summary>
/// Delete a box which was loaded in the given context, cascading to child records
/// </summary>
internal static void DeleteBox(SystematizerContext db, Box box)
{
db.Database.ExecuteSqlRaw($"delete from BoxPerson where BoxId={box.RowId}");
db.Database.ExecuteSqlRaw($"delete from Word where Kind=0 and ParentId={box.RowId}");
db.Box.Remove(box);
db.SaveChanges();
}
/// <summary>
/// Mark any tasks done that are low priority and were scheduled in the past; save changes
/// </summary>
internal void AutoCompleteTasks()
{
DateTime cutoff = DateTime.Today;
string cutoffS = DateUtil.ToYMD(cutoff);
var toDelete = new List <long>();
for (int i = ScheduledBoxes.Count - 1; i >= 0; --i)
{
var box = ScheduledBoxes[i];
if (box.Importance == Constants.IMPORTANCE_LOW && box.BoxTime != null && DateUtil.IsBefore(box.BoxTime, cutoffS))
{
ScheduledBoxes.RemoveAt(i);
toDelete.Add(box.RowId);
}
}
if (toDelete.Count == 0)
{
return;
}
using var db = new SystematizerContext();
string rowids = string.Join(',', toDelete);
db.Database.ExecuteSqlRaw($"update Box set DoneDate='{cutoffS}' where RowId in ({rowids})");
}
/// <summary>
/// Shortcut for saving settings; caller provides function to change the needed values
/// </summary>
public static void WriteSettings(Action <Setting> modify)
{
using var db = new SystematizerContext();
var settings = db.Setting.First();
modify(settings);
db.SaveChanges();
}
/// <summary>
/// Delete a person, cascading to child records
/// </summary>
internal static void DeletePerson(SystematizerContext db, long personId)
{
db.Database.ExecuteSqlRaw($"delete from PersonPerson where Person1Id={personId}");
db.Database.ExecuteSqlRaw($"delete from PersonPerson where Person2Id={personId}");
db.Database.ExecuteSqlRaw($"delete from PersonCat where PersonId={personId}");
db.Database.ExecuteSqlRaw($"delete from BoxPerson where PersonId={personId}");
db.Database.ExecuteSqlRaw($"delete from Word where Kind=1 and ParentId={personId}");
db.Database.ExecuteSqlRaw($"delete from Person where RowId={personId}");
}
/// <summary>
/// Return a subset of rowIds given in the argument, including only those Box ids that have child boxes
/// </summary>
public long[] BoxesWithChildren(long[] ids, bool onlyNotDone)
{
if (ids.Length == 0)
{
return(new long[0]);
}
using var db = new SystematizerContext();
return(DBUtil.BoxesWithChildren(db, ids, onlyNotDone));
}
/// <summary>
/// Get persons for export
/// </summary>
/// <param name="ids">either null to include all or a specific list</param>
public Person[] LoadPersonsForExport(long[] ids)
{
using var db = new SystematizerContext();
if (ids == null)
{
return(db.Person.ToArray());
}
return(db.Person.Where(r => ids.Contains(r.RowId)).ToArray());
}
/// <summary>
/// Get boxes for export
/// </summary>
/// <param name="ids">either null to include all non-done boxes or a specific list</param>
public Box[] LoadBoxesForExport(long[] ids)
{
using var db = new SystematizerContext();
if (ids == null)
{
return(db.Box.Where(r => r.DoneDate == null).ToArray());
}
return(db.Box.Where(r => ids.Contains(r.RowId)).ToArray());
}
/// <summary>
/// write full text index for a box; does not save changes
/// </summary>
internal static void WriteBoxIndex(SystematizerContext db, Box box)
{
var fullTextIndex = new FullTextManager();
fullTextIndex.TitleToIndex.AddUserField(box.Title);
fullTextIndex.DetailsToIndex.AddUserField(box.Notes);
fullTextIndex.DetailsToIndex.AddUserField(box.RawEmail);
fullTextIndex.WriteIndex(db, 0, box.RowId);
}
internal static IEnumerable <CachedBox> LoadBoxesByParent(SystematizerContext db, long parentId, bool onlyNotDone)
{
var q = db.Box.Where(r => r.ParentId == parentId);
if (onlyNotDone)
{
q = q.Where(r => r.DoneDate == null);
}
return(LoadForCaching(q).OrderBy(r => r.Title));
}
/// <summary>
/// Delete boxes older than one year after they are done
/// </summary>
internal static void DeleteVeryOldBoxes()
{
using var db = new SystematizerContext();
DateTime cutoff = DateTime.Today.AddYears(-1);
string cutoffS = DateUtil.ToYMD(cutoff);
var boxes = db.Box.FromSqlRaw($"select RowId,* from Box where DoneDate is not null and DoneDate < '{cutoffS}'");
db.Box.RemoveRange(boxes);
db.SaveChanges();
}
/// <summary>
/// Get boxes for done/search blocks; see comments in DBUtil
/// </summary>
public CachedBox[] LoadBoxesByKeyword(string term, bool includeDetails, string doneSince)
{
using var db = new SystematizerContext();
var boxes = DBUtil.BoxesByKeyword(db, term, includeDetails, doneSince);
if (boxes == null)
{
return(null);
}
return(boxes.ToArray());
}
/// <summary>
/// Get persons for search block; see comments in DBUtil
/// </summary>
/// <param name="catIds">null or catIds to match</param>
public Person[] LoadFilteredPersons(string term, bool includeDetails, long[] catIds, bool forExport)
{
using var db = new SystematizerContext();
var a = DBUtil.LoadFilteredPersons(db, term, includeDetails, catIds, allowLoadUnfiltered: true, limit100: !forExport);
if (a == null)
{
a = new Person[0];
}
return(a.ToArray());
}
/// <summary>
/// read settings and categories into globals
/// </summary>
internal static void ReadSettings()
{
using var db = new SystematizerContext();
var settings = db.Setting.First();
Globals.PersonCustomLabels = new[] { settings.Custom1Label, settings.Custom2Label, settings.Custom3Label, settings.Custom4Label, settings.Custom5Label };
Globals.DayChunks = new MultiDayChunkSet();
Globals.DayChunks.Initialize(settings.ChunkInfo ?? "");
Globals.AllowTasks = settings.AllowTasks != 0;
Globals.AllCats = new CatCache(db.Cat);
}
internal static long[] BoxesWithChildren(SystematizerContext db, long[] ids, bool onlyNotDone)
{
var q = db.Box.Where(b => ids.Contains(b.RowId) && db.Box.Any(b2 => b2.ParentId == b.RowId));
if (onlyNotDone)
{
q = db.Box.Where(b => ids.Contains(b.RowId) && db.Box.Any(b2 => b2.ParentId == b.RowId && b2.DoneDate == null));
}
var boxIdsWithChildren = q.Select(b => b.RowId).ToArray();
return(boxIdsWithChildren);
}
/// <summary>
/// In a new context, attach a record (detecting if new or modified) and save it.
/// Caller may also inject other actions on the context.
/// </summary>
/// <param name="beforeWrite">optional action to run before writing record; this can return false to abandon changes</param>
/// <param name="afterWrite">optional action to run after writing record; any context changes are saved again after this is run</param>
internal static void WriteAny(BaseTable record, Func <SystematizerContext, bool> beforeWrite, Action <SystematizerContext> afterWrite)
{
using var db = new SystematizerContext();
if (beforeWrite?.Invoke(db) == false)
{
return;
}
var entry = db.Attach(record);
entry.State = record.RowId == 0 ? EntityState.Added : EntityState.Modified;
db.SaveChanges();
afterWrite?.Invoke(db);
db.SaveChanges();
}
/// <summary>
/// Get box for detail view or editing; caller should call SaveBox or AbandonBox when the pane/window is closed
/// </summary>
/// <returns>null if not found</returns>
public ExtBox LoadBoxForEditing(long boxId)
{
using var db = new SystematizerContext();
var box = db.Box.Find(boxId);
if (box == null)
{
return(null);
}
Globals.BoxEditingPool.CheckOut(box);
var links = DBUtil.LoadLinksFor(db, box).ToList();
return(new ExtBox(box, links));
}
/// <summary>
/// Load person detail for editing
/// </summary>
/// <returns>null if not found</returns>
public ExtPerson LoadPerson(long id)
{
using var db = new SystematizerContext();
var person = db.Person.Find(id);
if (person == null)
{
return(null);
}
var links = DBUtil.LoadLinksFor(db, person).ToList();
var catIds = db.PersonCat.Where(r => r.PersonId == id).Select(r => r.CatId);
return(new ExtPerson(person, links, catIds.ToArray()));
}
/// <summary>
/// Check if boxes exist matching the criteria given.
/// </summary>
/// <param name="parentRowId">matches on parent ID - required</param>
public bool CheckBoxesExist(long parentRowId = 0, bool filterByNotDone = false)
{
if (parentRowId == 0)
{
throw new Exception("invalid arguments");
}
using var db = new SystematizerContext();
IQueryable <Box> boxes = db.Box.Where(r => r.ParentId == parentRowId);
if (filterByNotDone)
{
boxes = boxes.Where(r => r.DoneDate == null);
}
return(boxes.Any());
}
/// <summary>
/// Get all kinds of links from the given box.
/// </summary>
internal static IEnumerable <LinkRecord> LoadLinksFor(SystematizerContext db, Box box)
{
var ret = new List <LinkRecord>();
//parent box
if (box.ParentId != null)
{
var parent = db.Box.Find(box.ParentId);
if (parent != null && parent.DoneDate == null)
{
ret.Add(new LinkRecord
{
Link = LinkType.FromBoxToParentBox,
OtherId = box.ParentId.Value,
Description = parent.Title
});
}
}
//child boxes
var childBoxes = db.Box.Where(r => r.ParentId == box.RowId && r.DoneDate == null).Select(r => new { r.RowId, r.Title });
foreach (var b2 in childBoxes)
{
ret.Add(new LinkRecord
{
Link = LinkType.FromBoxToChildBox,
OtherId = b2.RowId,
Description = b2.Title
});
}
//linked persons
var persons = db.Person.FromSqlRaw("select Person.RowId,Name from Person inner join BoxPerson on Person.RowId=BoxPerson.PersonId where BoxId=" + box.RowId)
.Select(r => new { r.RowId, r.Name });
foreach (var person in persons)
{
ret.Add(new LinkRecord
{
Link = LinkType.FromBoxToPerson,
OtherId = person.RowId,
Description = person.Name
});
}
return(ret);
}
static void WriteNoteBoxWithChildren(SystematizerContext db, int recurLevel, bool inclPasswords, StreamWriter w, Box box)
{
w.WriteHeading(Math.Min(recurLevel, 5), box.Title);
WriteBoxDetail(inclPasswords, w, box);
var children = db.Box.Where(r => r.ParentId == box.RowId).OrderBy(r => r.Title).ToArray();
if (children.Any())
{
w.Write("<div style=\"margin-left:8px;border-left:solid black 1px\">");
foreach (var child in children)
{
WriteNoteBoxWithChildren(db, recurLevel + 1, inclPasswords, w, child);
}
w.Write("</div>");
}
}
/// <summary>
/// write full text index for a person; does not save changes
/// </summary>
internal static void WritePersonIndex(SystematizerContext db, Person person)
{
var fullTextIndex = new FullTextManager();
fullTextIndex.TitleToIndex.AddUserField(person.Name);
fullTextIndex.DetailsToIndex.AddUserField(person.MainEmail);
fullTextIndex.DetailsToIndex.AddUserField(person.MainPhone);
fullTextIndex.DetailsToIndex.AddUserField(person.Address);
fullTextIndex.DetailsToIndex.AddUserField(person.Notes);
fullTextIndex.DetailsToIndex.AddUserField(person.Custom1);
fullTextIndex.DetailsToIndex.AddUserField(person.Custom2);
fullTextIndex.DetailsToIndex.AddUserField(person.Custom3);
fullTextIndex.DetailsToIndex.AddUserField(person.Custom4);
fullTextIndex.DetailsToIndex.AddUserField(person.Custom5);
fullTextIndex.WriteIndex(db, 1, person.RowId);
}
/// <summary>
/// Get boxes for detail view or editing; caller should call SaveBox or AbandonBox when the pane/window is closed
/// </summary>
public IEnumerable <ExtBox> LoadUnclassBoxesForEditing()
{
var ret = new List <ExtBox>();
using (var db = new SystematizerContext())
{
var boxes = db.Box.Where(r => r.IsUnclass != 0 && r.DoneDate == null);
foreach (var box in boxes)
{
Globals.BoxEditingPool.CheckOut(box);
var links = DBUtil.LoadLinksFor(db, box).ToList();
ret.Add(new ExtBox(box, links));
}
}
return(ret);
}
/// <summary>
/// If list has elements, return one and remove, else create one and add to context
/// </summary>
static Word CreateOrReuseRecord(SystematizerContext db, List <Word> records)
{
int lastRecIdx = records.Count - 1;
if (lastRecIdx >= 0)
{
var record = records[lastRecIdx];
records.RemoveAt(lastRecIdx);
return(record);
}
else
{
var record = new Word();
db.Word.Add(record);
return(record);
}
}
