public void SaveAction()
{
LogManager.GetLog(GetType()).Info("Save");
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Events.ShowDialog());
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var child = new Child();
Detail.GetData(child);
child.Updated = DateTime.Now;
db.Children.Insert(child);
_account.Children.Add(child);
_account.Updated = DateTime.Now;
db.Accounts.Update(_account);
}
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Core.Events.SwitchTask
{
Task = new EditAccountViewModel(_account)
});
}
public void SaveAction()
{
LogManager.GetLog(GetType()).Info("Save");
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Events.ShowDialog());
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var member = new Member
{
FirstName = this.FirstName,
LastName = this.LastName,
Phone = this.Phone,
Updated = DateTime.Now
};
db.Members.Insert(member);
_account.Members.Add(member);
_account.Updated = DateTime.Now;
db.Accounts.Update(_account);
}
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Core.Events.SwitchTask
{
Task = new EditAccountViewModel(_account)
});
}
public void CloseAction()
{
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
db.Presences.Update(_info.Tag);
}
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Events.ShowDialog());
}
public EditPrecenseViewModel(Presence presence)
{
LogManager.GetLog(GetType()).Info("Create");
this._presence = presence;
Name = _presence.Child.GetFullName();
CurrentState = State.NotArrivedYet;
if (_presence.BroughtBy != null)
{
CurrentState = State.HasArrived;
BroughtByName = _presence.BroughtBy.GetFullName();
_broughtAt = _presence.BroughtAt;
}
if (_presence.TakenBy != null)
{
CurrentState = State.HasLeft;
TakenByName = _presence.TakenBy.GetFullName();
_takenAt = _presence.TakenAt;
}
CurrentTime = DateTime.Now;
if (HasLeft == false)
{
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
// find account
var account = GetAccount(db.Accounts.FindAll());
if (account != null)
{
Resposibles = (from m in account.Members
select new MemberUI
{
Name = m.GetFullName(),
Tag = m
}).ToList();
Resposibles.AddRange(from m in db.Members.Find(mem => mem.Phone == "GrandParents")
select new MemberUI
{
Name = m.FirstName,
Tag = m
});
}
}
}
}
public AddPresenceDialogViewModel()
{
LogManager.GetLog(GetType()).Info("Create");
var today = DateTimeProvider.Now().Date;
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var all = db.Children.FindAll().ToList();
var exceptions = (from p in db.Presences.FindAll()
select p.Child.Id).ToList();
exceptions.ForEach(e => all.RemoveAll(c => c.Id == e));
Children = (from c in all
select new ChildPresenceUI
{
Name = c.GetFullName(),
Tag = c
}).ToList();
}
}
public override void YesAction()
{
LogManager.GetLog(GetType()).Info("YesAction");
if (SelectedItem != null)
{
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var presence = new Presence
{
Child = SelectedItem.Tag,
Date = DateTimeProvider.Now().Date,
Updated = DateTimeProvider.Now(),
TimeCode = 9
};
db.Presences.Insert(presence);
};
}
base.YesAction();
}
private void ImportPresence(string filename)
{
var log = LogManager.GetLog(GetType());
var importTime = DateTime.Now;
log.Info("Start administration import ({0}) : {1}.{2:D3}", filename, importTime, importTime.Millisecond);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
using (var import = new PetoeterDb(filename))
{
var presences = import.GetCollection<Presence>(PetoeterDb.TablePresence).FindAll().ToList();
log.Info("Import presences (#{0})", presences.Count);
foreach (var presence in presences)
{
log.Info("Import presence: {0}. [{1}] {2})", presence.Id, presence.Date.ToShortDateString(), presence.Child.GetFullName());
presence.Updated = importTime;
db.Presences.Insert(presence);
}
import.DropAll();
}
}
}
private void SaveHolidays()
{
LogManager.GetLog(GetType()).Info("Save");
// Remove all scheduled
_child.Schedule.RemoveAll(d => _startDate <= d.Day && d.Day <= _endDate);
var query = from d in Days
where d.Afternoon || d.Morning
select new DayCare.Model.Lite.Date
{
Morning = d.Morning,
Afternoon = d.Afternoon,
Day = d.Day
};
_child.Schedule.AddRange(query);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
_child.Updated = DateTime.Now;
db.Children.Update(_child);
}
}
public static void Create(DateTime date)
{
var Months = new List<string> { "Januari", "Februari", "Maart", "April",
"Mei", "Juni", "Juli", "Augustus",
"September", "Oktober", "November", "December" };
var Days = new List<string> { "Zondag", "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag" };
var weekdays = GetDays(date).ToList();
int month = date.Month;
int year = date.Year;
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Aanwezigheid");
ws.Style.Font.FontName = "Calibri";
ws.Style.Font.FontSize = 10;
var color = XLColor.FromArgb(0, 51, 102);
var cell = ws.Cell(1, "D");
cell.Value = "Aanwezigheidregister";
cell.Style.Font.FontSize = 14;
cell.Style.Font.FontColor = color;
cell.Style.Font.Bold = true;
cell = ws.Cell(1, "M");
cell.Value = Months[month - 1];
cell.Style.Font.FontSize = 14;
cell.Style.Font.FontColor = color;
cell.Style.Font.Bold = true;
cell = ws.Cell(1, "P");
cell.Value = string.Format("'{0}", year);
cell.Style.Font.FontSize = 14;
cell.Style.Font.FontColor = color;
cell.Style.Font.Bold = true;
ColumnId colid = new ColumnId('D');
uint columns = 0;
int dayCol = 4;
foreach (var day in weekdays)
{
var daycell = ws.Range(2, dayCol, 2, dayCol + 2).Merge();
daycell.Value = string.Format("'{0} {1}", Days[(int)(day.Date.DayOfWeek)], day.Date.ToShortDateString());
daycell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
daycell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
Enbox(daycell.Style);
cell = ws.Cell(3, colid.ToString());
cell.Value = "uur";
Enbox(cell.Style);
colid.Increment();
cell = ws.Cell(3, colid.ToString());
cell.Value = "wie";
Enbox(cell.Style);
colid.Increment();
cell = ws.Cell(3, colid.ToString());
cell.Value = "paraaf";
Enbox(cell.Style);
colid.Increment();
dayCol += 3;
columns++;
}
int rowIndex = 4;
uint childIndex = 1;
var startDate = weekdays.Min(d => d.Date);
var endDate = weekdays.Max(d => d.Date);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var presenceData = db.Presences.Find(p => p.Date >= startDate.Date && p.Date <= endDate.Date).ToList();
if (presenceData != null && presenceData.Count != 0)
{
var presences = from p in presenceData
orderby p.Child.BirthDay
group p by p.Child.Id into presencegroup
//orderby presencegroup.Key.BirthDay
select presencegroup;
var count = presences.Count();
var rowSize = count > 30 ? 24.75 : 27.0;
foreach (var presence in presences)
{
var nr = ws.Range(rowIndex, 1, rowIndex + 1, 1).Merge();
nr.Value = string.Format("{0}.", childIndex);
nr.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
nr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
Enbox(nr.Style);
var cname = ws.Range(rowIndex, 2, rowIndex + 1, 2).Merge();
cname.Value = presence.First().Child.GetFullName();
//Ch.GetFullName(); // string.Format("{0} {1}", child.LastName, child.FirstName);
cname.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cname.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cname.Style.Alignment.WrapText = true;
Enbox(cname.Style);
cell = ws.Cell(rowIndex, "C");
cell.Value = "Aankomst";
Enbox(cell.Style);
ws.Row(rowIndex).Height = rowSize;
cell = ws.Cell(rowIndex + 1, "C");
cell.Value = "Vertrek";
Enbox(cell.Style);
ws.Row(rowIndex + 1).Height = rowSize;
foreach (var day in from p in presence orderby p.Date select p)
{
int dateindex = (day.Date - startDate).Days;
if (day.BroughtBy != null)
{
colid = new ColumnId('D');
colid.Increment(dateindex * 3);
cell = ws.Cell(rowIndex, colid.ToString());
cell.SetValue<string>(day.BroughtAt.ToString("HH:MM"));
colid.Increment(1);
cell = ws.Cell(rowIndex, colid.ToString());
cell.Value = day.BroughtBy.GetFullName();
}
if (day.TakenBy != null)
{
colid = new ColumnId('D');
colid.Increment(dateindex * 3);
cell = ws.Cell(rowIndex + 1, colid.ToString());
cell.SetValue<string>(day.TakenAt.ToString("HH:MM"));
colid.Increment(1);
cell = ws.Cell(rowIndex + 1, colid.ToString());
cell.Value = day.TakenBy.GetFullName();
}
}
childIndex++;
rowIndex += 2;
}
}
else
{
//var query = from c in db.Children.FindAll()
// orderby c.BirthDay descending
// select c;
var children = from c in db.Children.FindAll()
where c.Schedule.Any(d => weekdays.Any(wd => wd.Date == d.Day))
orderby c.BirthDay ascending
select c;
var rowSize = children.Count() > 30 ? 24.75 : 27.0;
foreach (var child in children)
{
var nr = ws.Range(rowIndex, 1, rowIndex + 1, 1).Merge();
nr.Value = string.Format("{0}.", childIndex);
nr.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
nr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
Enbox(nr.Style);
var cname = ws.Range(rowIndex, 2, rowIndex + 1, 2).Merge();
cname.Value = string.Format("{0} {1}", child.LastName, child.FirstName);
cname.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cname.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cname.Style.Alignment.WrapText = true;
Enbox(cname.Style);
cell = ws.Cell(rowIndex, "C");
cell.Value = "Aankomst";
Enbox(cell.Style);
ws.Row(rowIndex).Height = rowSize;
cell = ws.Cell(rowIndex + 1, "C");
cell.Value = "Vertrek";
Enbox(cell.Style);
ws.Row(rowIndex + 1).Height = rowSize;
colid = new ColumnId('D');
for (int index = 0; index < 15; index++)
{
cell = ws.Cell(rowIndex, colid.ToString());
Enbox(cell.Style);
cell = ws.Cell(rowIndex + 1, colid.ToString());
Enbox(cell.Style);
colid.Increment();
}
childIndex++;
rowIndex += 2;
}
}
}
//var model = ServiceProvider.Instance.GetService<Petoeter>();
//var thisWeek = DatePeriod.MakePeriod(month, year);
var mergedcell = ws.Range(2, 1, 3, 3).Merge();
mergedcell.Value = "Naam";
mergedcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
mergedcell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
mergedcell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
mergedcell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
mergedcell.Style.Border.TopBorder = XLBorderStyleValues.Thin;
mergedcell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
// styling
ws.Column(1).Width = 2.0;
ws.Column(2).Width = 13.0;
ws.Column(3).Width = 6.43;
ws.Column(3).Style.Font.FontSize = 8;
for (int day = 0; day < 5; day++)
{
ws.Column(4 + (day * 3)).Width = 5.43;
ws.Column((int)(4 + (day * 3) + 1)).Width = 10.14;
ws.Column((int)(4 + (day * 3) + 2)).Width = 5.43;
}
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
ws.PageSetup.VerticalDpi = 600;
ws.PageSetup.HorizontalDpi = 600;
ws.PageSetup.Margins.Bottom = 0;
ws.PageSetup.Margins.Top = 0;
ws.PageSetup.Margins.Left = 0.25;
ws.PageSetup.Margins.Right = 0.25;
ws.PageSetup.SetColumnsToRepeatAtLeft(1, 3);
ws.PageSetup.SetRowsToRepeatAtTop(1, 3);
var file = GetTempFilePathWithExtension("xlsx");
wb.SaveAs(file);
System.Diagnostics.Process.Start(file);
}
private static void SaveHoliday(DayUI ui)
{
LogManager.GetLog(typeof(CalendarMainViewModel)).Info("Save Holiday");
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var day = db.Holidays.FindOne(d => d.Day == ui.Day);
if (day != null)
{
// update
day.Afternoon = ui.Afternoon;
day.Morning = ui.Morning;
db.Holidays.Update(day);
}
else
{
if (ui.Morning || ui.Afternoon)
{
db.Holidays.Insert(new Model.Lite.Date
{
Day = ui.Day,
Morning = ui.Morning,
Afternoon = ui.Afternoon
});
}
}
}
}
protected void LoadItems()
{
LogManager.GetLog(GetType()).Info("Load items");
CalculateCalendarRegion();
var days = new List<DayUI>();
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var children_presence = new List<DayCare.Model.Lite.Date>();
foreach (var child in db.Children.FindAll())
{
children_presence.AddRange(child.Schedule);
}
var date = _startDate;
while (date <= _endDate)
{
// var scheduled = _child.Schedule.Where(d => d.Day == date).FirstOrDefault();
var day = new DayUI
{
Day = date,
ActiveMonth = date.Month == _currentDate.Month,
Active = (date.DayOfWeek != DayOfWeek.Saturday) &&
(date.DayOfWeek != DayOfWeek.Sunday)
};
var holiday = db.Holidays.FindOne(d => d.Day == date);
if (holiday != null)
{
day.Morning = holiday.Morning;
day.Afternoon = holiday.Afternoon;
}
// count children
day.MorningCount = children_presence.Count(d => d.Day == date && d.Morning);
day.AfternoonCount = children_presence.Count(d => d.Day == date && d.Afternoon);
days.Add(day);
date = date.AddDays(1);
}
}
Days = days;
}
public static System.Windows.Media.Imaging.BitmapImage GetImage(string fileId)
{
if (string.IsNullOrWhiteSpace(fileId))
{
if (_olaf == null)
{
_olaf = GetImage(Olaf);
}
return _olaf;
}
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var file = db.FileStorage.FindById(fileId);
if (file == null)
{
if (fileId == Olaf)
{
SaveFile(Olaf, @"olaf.png");
}
return null;
}
using(var reader = file.OpenRead())
{
System.IO.MemoryStream mem = new System.IO.MemoryStream();
mem.SetLength(file.Length);
reader.Read(mem.GetBuffer(), 0, (int)file.Length);
mem.Flush();
System.Windows.Media.Imaging.BitmapImage image = new System.Windows.Media.Imaging.BitmapImage();
image.BeginInit();
image.StreamSource = mem;
image.EndInit();
image.Freeze();
return image;
}
}
}
public void ConfirmAction()
{
LogManager.GetLog(GetType()).Info("Confirm");
ServiceProvider.Instance.GetService<EventAggregator>().PublishOnUIThread(
new Events.ShowDialog());
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
if (CurrentState == State.NotArrivedYet)
{
_presence.BroughtBy = SelectedResponsible.Tag;
_presence.BroughtAt = _broughtAt = CurrentTime;
}
else if (CurrentState == State.HasArrived)
{
_presence.TakenBy = SelectedResponsible.Tag;
_presence.TakenAt = _takenAt = CurrentTime;
}
_presence.Updated = DateTime.Now;
db.Presences.Update(_presence);
}
//var model = ServiceProvider.Instance.GetService<Petoeter>();
// TODO
//if (CurrentState == State.NotArrivedYet)
//{
// _presence.Arriving = SelectedResponsible.Tag;
// _presence.ArrivingTime = _arrivalTime;
// _presence.Updated = true;
// model.Save();
//}
//else if (CurrentState == State.Arrived)
//{
// _presence.DepartureMember_Id = SelectedResponsible.Tag.Id;
// _presence.DepartureTime = _leaveTime;
// model.UpdatePresence(_presence);
//}
}
public static void SaveFile(string fileId, string fileName)
{
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
db.FileStorage.Upload(fileId, fileName);
}
}
private void ImportAdministration(string filename)
{
var log = LogManager.GetLog(GetType());
var importTime = DateTime.Now;
log.Info("Start administration import ({0}) : {1}.{2:D3}", filename, importTime, importTime.Millisecond);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
using (var import = new PetoeterDb(filename))
{
if (import.Children.Count() != 0)
{
var children = (from c in import.Children.FindAll() select c).ToList();
log.Info("Import children (#{0})", children.Count);
foreach (var child in children)
{
var update_child = db.Children.FindById(child.Id);
if (update_child == null)
{
log.Info("Import child: {0}. {1}", child.Id, child.GetFullName());
child.Updated = importTime;
db.Children.Insert(child);
}
else
{
log.Info("Update child: {0}. {1}", child.Id, child.GetFullName());
child.Updated = importTime;
db.Children.Update(child);
}
if (string.IsNullOrEmpty(child.FileId) == false)
{
var file = import.FileStorage.FindById(child.FileId);
if (file != null)
{
if (db.FileStorage.Exists(child.FileId))
{
log.Info("Update child: remove picture");
db.FileStorage.Delete(child.FileId);
}
log.Info("Update child: upload picture");
db.FileStorage.Upload(child.FileId, file.OpenRead());
}
}
}
}
if (import.Members.Count() != 0)
{
var members = (from m in import.Members.FindAll() select m).ToList();
log.Info("Import members (#{0})", members.Count);
foreach (var member in members)
{
var update_member = db.Members.FindById(member.Id);
if (update_member == null)
{
log.Info("Import member: {0}. {1}", member.Id, member.GetFullName());
member.Updated = importTime;
db.Members.Insert(member);
}
else
{
log.Info("Update member: {0}. {1}", member.Id, member.GetFullName());
member.Updated = importTime;
db.Members.Update(member);
}
}
}
if (import.Accounts.Count() != 0)
{
//var accounts = (from a in import.Accounts.FindAll() select a).ToList();
var accounts = (from a in import.GetCollection<Account>(PetoeterDb.TableAccount).FindAll() select a).ToList();
log.Info("Import accounts (#{0})", accounts.Count);
foreach (var account in accounts)
{
// check if account exist
var updateaccount = db.Accounts.FindById(account.Id);
if (updateaccount == null)
{
log.Info("Import account: #{0}. {1}", account.Id, account.Name);
account.Updated = importTime;
db.Accounts.Insert(account);
}
else
{
log.Info("Update account: #{0}. {1}", account.Id, account.Name);
account.Updated = importTime;
db.Accounts.Update(account);
}
}
}
if (import.Holidays.Count() != 0)
{
var holidays = (from h in import.Holidays.FindAll() select h).ToList();
log.Info("Import holidays (#{0})", holidays.Count);
foreach (var holiday in holidays)
{
// check if account exist
var updateholiday = db.Accounts.FindById(holiday.Id);
if (updateholiday == null)
{
log.Info("Import holiday: #{0}. {1}", holiday.Id, holiday.Day);
holiday.Updated = importTime;
db.Holidays.Insert(holiday);
}
else
{
log.Info("Update holiday: #{0}. {1}", holiday.Id, holiday.Day);
holiday.Updated = importTime;
db.Holidays.Update(holiday);
}
}
}
log.Info("Drop all");
import.DropAll();
}
}
}
private void ExportPresence(string filename)
{
var log = LogManager.GetLog(GetType());
var exportTime = DateTime.Now;
log.Info("Start presence export ({0}) : {1}.{2:D3}", filename, exportTime, exportTime.Millisecond);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var settings = db.GetSettings();
var lastExportTime = settings.ExporTimeStamp;
using (var export = new PetoeterDb(filename))
{
var presence = (from p in db.Presences.Find(p => p.Updated < lastExportTime) select p).ToList();
presence.ForEach(p =>
{
p.Updated = exportTime;
log.Info("Export presence: {0}. [{1}] {2}", p.Id, p.Date.ToShortDateString(), p.Child.GetFullName());
//export.Children.Insert(p.Child);
//if (export.Members.Exists(m => m.Id == p.BroughtBy.Id) == false)
//{
// export.Members.Insert(p.BroughtBy);
//}
//if (export.Members.Exists(m => m.Id == p.TakenBy.Id) == false)
//{
// export.Members.Insert(p.TakenBy);
//}
});
export.Presences.Insert(presence);
}
settings.ExporTimeStamp = exportTime;
db.UpdateSystemSettings();
}
}
private void ExportAdministration(string filename)
{
var log = LogManager.GetLog(GetType());
var exportTime = DateTime.Now;
log.Info("Start administration export ({0}) : {1}.{2:D3}", filename, exportTime, exportTime.Millisecond);
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var settings = db.GetSettings();
var lastExportTime = settings.ExporTimeStamp;
using (var export = new PetoeterDb(filename))
{
var children = (from c in db.Children.FindAll()
where c.Updated < lastExportTime
select c).ToList();
children.ForEach(c =>
{
c.Updated = exportTime;
log.Info("Export child: {0}. {1}", c.Id, c.GetFullName());
});
db.Children.Update(children);
export.Children.Insert(children);
children.ForEach(c =>
{
if (c.FileId != null)
{
var file = db.FileStorage.FindById(c.FileId);
if (file != null)
{
export.FileStorage.Upload(c.FileId, file.OpenRead());
}
}
});
var members = (from m in db.Members.FindAll()
where m.Updated < lastExportTime
select m).ToList();
members.ForEach(m =>
{
m.Updated = exportTime;
log.Info("Export member: {0}. {1}", m.Id, m.GetFullName());
});
db.Members.Update(members);
export.Members.Insert(members);
var accounts = (from a in db.Accounts.FindAll()
where a.Updated < lastExportTime
select a).ToList();
accounts.ForEach(a =>
{
a.Updated = exportTime;
log.Info("Export account: {0}. {1}", a.Id, a.Name);
});
db.Accounts.Update(accounts);
export.Accounts.Insert(accounts);
var holidays = (from h in db.Holidays.FindAll()
where h.Updated < lastExportTime
select h).ToList();
holidays.ForEach(h =>
{
h.Updated = exportTime;
log.Info("Export holiday: {0}. {1}", h.Id, h.Day);
});
db.Holidays.Update(holidays);
export.Holidays.Insert(holidays);
}
settings.ExporTimeStamp = exportTime;
db.UpdateSystemSettings();
}
}
internal static void RemoveFile(string fileId)
{
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
db.FileStorage.Delete(fileId);
}
}
internal static void Create()
{
try
{
var model = ServiceProvider.Instance.GetService<Petoeter>();
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Telefoon lijst");
ws.Style.Font.FontName = "Calibri";
ws.Style.Font.FontSize = 10;
var title = ws.Range(1, 1, 1, 6).Merge();
title.Value = "Overzichtlijst kinderen";
title.Style.Font.Bold = true;
title.Style.Font.FontSize = 14;
title.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(2, 1).Value = "Naam";
ws.Cell(2, 2).Value = "Geb. Datum";
ws.Cell(2, 3).Value = "Ouder";
ws.Cell(2, 4).Value = "Telefoon";
ws.Cell(2, 5).Value = "Ouder";
ws.Cell(2, 6).Value = "Telefoon";
int row = 3;
int maxcolumn = 3;
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var accounts = from a in db.Accounts.FindAll()
where a.Deleted == false
orderby a.Name ascending
select a;
foreach (var account in accounts)
{
var children = from c in account.Children
where c.Deleted == false
orderby c.FirstName
select c;
foreach (var child in children)
{
var cell = ws.Cell(row, 1);
cell.Value = string.Format("{0} {1}", child.LastName, child.FirstName);
cell = ws.Cell(row, 2);
cell.Value = child.BirthDay;
int column = 3;
var members = from m in account.Members
where m.Deleted == false && !string.IsNullOrWhiteSpace(m.Phone)
select m;
foreach (var member in members.Take(2))
{
cell = ws.Cell(row, column++);
cell.Value = string.Format("{0} {1}", member.FirstName, member.LastName);
cell = ws.Cell(row, column++);
cell.Value = member.Phone;
cell.SetDataType(XLCellValues.Text);
}
row++;
}
}
}
//foreach (var account in from m in model.GetAccounts()
// where m.Deleted == false
// orderby m.Name ascending
// select m)
//{
// foreach (var child in from c in account.Children
// where c.Deleted == false
// orderby c.FirstName
// select c)
// {
// var cell = ws.Cell(row, 1);
// cell.Value = string.Format("{0} {1}", child.LastName, child.FirstName);
// cell = ws.Cell(row, 2);
// cell.Value = child.BirthDay;
// int column = 3;
// var members = from m in account.Members
// where m.Deleted == false && !string.IsNullOrWhiteSpace(m.Phone)
// select m;
// foreach (var member in members.Take(2))
// {
// cell = ws.Cell(row, column++);
// cell.Value = string.Format("{0} {1}", member.FirstName, member.LastName);
// cell = ws.Cell(row, column++);
// cell.Value = member.Phone;
// cell.SetDataType(XLCellValues.Text);
// }
// row++;
// }
//}
// style
ws.Column(1).Width = 20.0;
ws.Column(2).Width = 13.0;
ws.Column(3).Width = 20.0;
ws.Column(4).Width = 13.0;
ws.Column(5).Width = 20.0;
ws.Column(6).Width = 13.0;
ws.Column(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
var header = ws.Range(2, 1, 2, 6).Style;
header.Font.Bold = true;
header.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
var file = GetTempFilePathWithExtension("xlsx");
wb.SaveAs(file);
System.Diagnostics.Process.Start(file);
}
catch (Exception)
{
}
}
private void VerifyDatabase()
{
// Add Oma and Opa Generic members
//
using (var db = new PetoeterDb(PetoeterDb.FileName))
{
var member = db.Members.Find(m => m.Phone == "GrandParents");
if (member.Count() == 0)
{
db.Members.Insert(new Lite.Member { FirstName = "Oma", Phone = "GrandParents" });
db.Members.Insert(new Lite.Member { FirstName = "Opa", Phone = "GrandParents" });
}
}
}