/// <summary>
/// Fügt einen neuen Kassenabschlussbeleg der Datenbank hinzu
/// </summary>
/// <param name="cashClosureID">Kassenabschluss ID</param>
/// <param name="printDone">Wurde gedruckt</param>
/// <param name="printUserAccountID">Druckuser</param>
/// <param name="printDate">Druckdatum</param>
/// <param name="done">Erledigt</param>
/// <param name="doneDate">Erledigungsdatum</param>
/// <param name="doneUserAccountID">Erledigungs User</param>
/// <param name="revenues">Einnahmen</param>
/// <param name="expenses">Ausgaben</param>
/// <param name="sum">Summe</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(int cashClosureID, bool printDone, DateTime? printDate, bool done,
DateTime? doneDate, int? printUserAccountID = null, int? doneUserAccountID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var cashClosureReport = new CashClosureReport
{
CashClosure = db.CashClosures.Single(cc => cc.CashClosureID == cashClosureID),
PrintDone = printDone,
PrintDate = printDate,
Done = done,
DoneDate = doneDate,
};
if (printUserAccountID.HasValue)
{
cashClosureReport.PrintUserAccount = db.UserAccounts.Single(ua => ua.UserAccountID == printUserAccountID.Value);
}
if (doneUserAccountID.HasValue)
cashClosureReport.DoneUserAccount = db.UserAccounts.Single(ua => ua.UserAccountID == doneUserAccountID.Value);
db.CashClosureReports.AddObject(cashClosureReport);
db.SaveChanges();
return cashClosureReport.CashClosureReportID;
}
}
/// <summary>
/// Fügt ein neues Teammitglied der Datenbank hinzu
/// </summary>
/// <param name="firstname">Vorname</param>
/// <param name="lastname">Nachnamen</param>
/// <param name="street">Strasse</param>
/// <param name="zipcode">Postleitzahl</param>
/// <param name="city">Stadt</param>
/// <param name="mobileNo">Handynummer</param>
/// <param name="phoneNo">Telefonnummer</param>
/// <param name="commercialPhoneNo">Telefonnummer Arbeit</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(int titleID, int teamFunctionID, string firstname, string lastname, string street, int zipcode, string city, DateTime dateOfBirth, string mobileNo = null, string phoneNo = null, string email = null, bool isFormLetterAllowed = false)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var title = db.Titles.Single(t => t.TitleID == titleID);
var teamFunction = db.TeamFunctions.Single(t => t.TeamFunctionID == teamFunctionID);
Team team = new Team()
{
FirstName = firstname,
LastName = lastname,
IsActive = true,
Street = street,
ZipCode = zipcode,
City = city,
MobileNo = mobileNo,
DateOfBirth = dateOfBirth,
IsFormLetterAllowed = isFormLetterAllowed,
PhoneNo = phoneNo,
Title = title,
TeamFunction = teamFunction,
Email = email,
};
db.Teams.AddObject(team);
db.SaveChanges();
return team.TeamID;
}
}
/// <summary>
/// Löscht einen bestehenden Einkommen Eintrag aus der Datenbank
/// </summary>
/// <param name="revenueID">Die ID des Eintrags in der Datenbank</param>
public static void Delete(int revenueID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var revenue = db.Revenues.Single(r => r.RevenueID == revenueID);
db.Revenues.DeleteObject(revenue);
db.SaveChanges();
}
}
/// <summary>
/// Löscht einen bestehenden Eintrag aus der Datenbank
/// </summary>
/// <param name="cashClosureID">Die benötigte Kassenabschluss ID</param>
public static void Delete(int cashClosureID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var cashClosure = db.CashClosures.Single(cc => cc.CashClosureID == cashClosureID);
db.CashClosures.DeleteObject(cashClosure);
db.SaveChanges();
}
}
/// <summary>
/// Methode zum löschen eines Eintrags in der Datenbank
/// </summary>
/// <param name="formletterPatternID">Die ID des Serienbrief Musters</param>
public static void Delete(int formletterPatternID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var formletterPattern = db.FormletterPatterns.Single(fp => fp.FormletterPatternID == formletterPatternID);
db.FormletterPatterns.DeleteObject(formletterPattern);
db.SaveChanges();
}
}
/// <summary>
/// Löscht einen bestehenden Eintrag in der Datenbank
/// </summary>
/// <param name="columnAssignmentID"></param>
public static void Delete(int columnAssignmentID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var columnAssignment = db.ColumnAssignments.Single(ca => ca.ColumnAssignmentID == columnAssignmentID);
db.ColumnAssignments.DeleteObject(columnAssignment);
db.SaveChanges();
}
}
/// <summary>
/// Funktion zum löschen eines vorhandenen Kontos
/// </summary>
/// <param name="accountID">Datenbank ID des Accounts</param>
public static void Delete(int accountID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var account = db.Accounts.Single(a => a.AccountID == accountID);
db.Accounts.DeleteObject(account);
db.SaveChanges();
}
}
/// <summary>
/// Methode zum löschen eines Eintrags in der Datenbank
/// </summary>
/// <param name="filterSetID"></param>
public static void Delete(int filterSetID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var filterSet = db.FilterSets.Single(fs => fs.FilterSetID == filterSetID);
db.FilterSets.DeleteObject(filterSet);
db.SaveChanges();
}
}
/// <summary>
/// Löscht einen bestehenden Eintrag aus der Datenbank
/// </summary>
/// <param name="cashClosureReportID"></param>
public static void Delete(int cashClosureReportID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var report = db.CashClosureReports.Single(ccr => ccr.CashClosureReportID == cashClosureReportID);
db.CashClosureReports.DeleteObject(report);
db.SaveChanges();
}
}
/// <summary>
/// Aktualisiert alle Teamfunktionen
/// </summary>
private static void RefreshTeamFunctionList()
{
if (_TeamFunctions == null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
_TeamFunctions = db.TeamFunctions.ToList();
}
}
}
/// <summary>
/// Löscht einen bestehenden Eintrag aus der Datenbank
/// </summary>
/// <param name="bookingId">Die ID des Eintrags in der Datenbank</param>
public static void Delete(int bookingID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var booking = db.Bookings.Single(a => a.BookingID == bookingID);
db.Bookings.DeleteObject(booking);
db.SaveChanges();
}
}
/// <summary>
/// Statische Methode zum holen von Einkommensarten -> Singleton Pattern um Datenbank nicht permanent zu konnektieren
/// </summary>
/// <returns></returns>
private static IEnumerable<RevenueType> GetRevenueTypeList()
{
if (_RevenueTypes == null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
_RevenueTypes = db.RevenueTypes.ToList();
}
}
return _RevenueTypes;
}
/// <summary>
/// Löscht einen bestehenden Eintrag aus der Datenbank
/// </summary>
/// <param name="userAccountID">Die benötigte BenutzerkontenID</param>
public static void Delete(int userAccountID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var userAccount = db.UserAccounts.Single(u => u.UserAccountID == userAccountID);
db.UserAccounts.DeleteObject(userAccount);
db.SaveChanges();
}
}
/// <summary>
/// Methode zum holen der Titel -> Singleton Pattern um häufiges konnektieren zur Datenbank zu vermeiden
/// </summary>
/// <returns></returns>
private static IEnumerable<Title> GetTitleList()
{
if (_Titles == null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
_Titles = db.Titles.ToList();
}
}
return _Titles;
}
/// <summary>
/// Statische Klasse um mehrfaches Laden aus der Datenbank zu verhindern
/// </summary>
/// <returns></returns>
private static IEnumerable<FundingType> GetFundingTypeList()
{
if (_FundingTypes == null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
_FundingTypes = db.FundingTypes.ToList();
}
}
return _FundingTypes;
}
/// <summary>
/// Fügt einen neuen finanziellen Typ der Datenbank hinzu
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static int Add(string name)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var fundingType = new FundingType { Name = name };
db.FundingTypes.AddObject(fundingType);
db.SaveChanges();
return fundingType.FundingTypeID;
}
}
/// <summary>
/// Gibt alle Notizen zurück
/// </summary>
/// <param name="noteID">Filterung nach Notiz ID</param>
/// <returns>Liste aller Notizen</returns>
public static IEnumerable<Note> GetNotes(int? noteID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var notes = db.Notes.AsQueryable();
if (noteID.HasValue)
notes = notes.Where(n => n.NoteID == noteID);
return notes.ToList();
}
}
/// <summary>
/// Löscht eine Notiz
/// </summary>
/// <param name="noteID">Die zu löschende Notiz ID</param>
public static void Delete(int noteID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var note = db.Notes.Single(n => n.NoteID == noteID);
db.Notes.DeleteObject(note);
db.SaveChanges();
}
RefreshNoteList();
}
/// <summary>
/// Fügt einen neuen Titel der Datenbank hinzu
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static int Add(string name)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var title = new Title
{
Name=name
};
db.Titles.AddObject(title);
db.SaveChanges();
return title.TitleID;
}
}
/// <summary>
/// Gibt alle Serienbrief Muster zurück
/// </summary>
/// <param name="formletterPatternID">Die ID nach welchem Serienbrief gefiltert werden soll</param>
/// <returns>Liste alle Serienbrief Muster</returns>
public static IEnumerable<FormletterPattern> GetFormletterPatterns(int? formletterPatternID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var formletterPatterns = db.FormletterPatterns
.Include("ColumnAssignment")
.Include("FilterSet")
.AsQueryable();
if (formletterPatternID.HasValue)
formletterPatterns = formletterPatterns.Where(fp => fp.FormletterPatternID == formletterPatternID.Value);
return formletterPatterns.ToList();
}
}
/// <summary>
/// Fügt eine neue Teamfunktion der Datenbank hinzu
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static int Add(string name)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var teamFunction = new TeamFunction
{
Name = name
};
db.TeamFunctions.AddObject(teamFunction);
db.SaveChanges();
return teamFunction.TeamFunctionID;
}
}
/// <summary>
/// Gibt die Summe der Wertbeträge bei den Buchungen zurück
/// </summary>
/// <param name="isRevenue">Boolean Wert ob es sich um eine Ein- oder Ausgabe handelt</param>
/// <param name="yearType">Typ ob es ein gegenwärtiger oder vergangener Wert ist</param>
/// <returns>Summe aller Ein- oder Ausgaben</returns>
public static double GetAmountSum(YearType yearType = YearType.Current)
{
using (TafelModelContainer db = new TafelModelContainer())
{
int year = (yearType == YearType.Current) ? DateTime.Now.Year : DateTime.Now.Year - 1;
var startDate = new DateTime(year, 1, 1);
var endDate = new DateTime(year, 12, 31);
var additionalRecords = db.Bookings.Where(b => b.Date >= startDate
&& b.Date <= endDate);
if (additionalRecords.Any())
return additionalRecords.Sum(ar => ar.Amount);
return 0;
}
}
/// <summary>
/// Methode zum hinzufügen eines neuen Eintrags in der Datenbank
/// </summary>
/// <param name="filterID">Die ID des Filters</param>
/// <param name="name">Name des Filters</param>
/// <param name="linking">Linking</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(string name, string linking, int? formletterPatternID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var filterSet = new FilterSet()
{
FormletterPattern = (formletterPatternID.HasValue) ? db.FormletterPatterns.Single(fp => fp.FormletterPatternID == formletterPatternID) : null,
Name = name,
Linking = linking
};
db.FilterSets.AddObject(filterSet);
db.SaveChanges();
return filterSet.FilterSetID;
}
}
/// <summary>
/// Gibt alle ColumnAssignments zurück
/// </summary>
/// <param name="columnAssignmentID">Nach welcher ID in der Datenbank gefiltert werden soll</param>
/// <returns>Liste aller ColumnAssignments</returns>
public static IEnumerable<ColumnAssignment> GetColumnAssignments(int? columnAssignmentID = null, int? formletterPatternID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var columnAssignments = db.ColumnAssignments
.Include("FormletterPattern")
.AsQueryable();
if (columnAssignmentID.HasValue)
columnAssignments = columnAssignments.Where(ca => ca.ColumnAssignmentID == columnAssignmentID.Value);
if (formletterPatternID.HasValue)
columnAssignments = columnAssignments.Where(ca => ca.FormletterPattern.FormletterPatternID == formletterPatternID.Value);
return columnAssignments.ToList();
}
}
/// <summary>
/// Gibt eine Liste mit Kassenabrechnungen zurück
/// </summary>
/// <param name="cashClosureReceiptID">Optionale Filterung nach KassenabrechnungsID</param>
/// <returns>Liste mit Kassenabrechnungen</returns>
public static IEnumerable<CashClosureReceipt> GetCashClosureReceipts(int? cashClosureReceiptID = null, int? cashClosureID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var receipts = db.CashClosureReceipts
.Include("CashClosure")
.Include("PrintUserAccount")
.AsQueryable();
if (cashClosureReceiptID.HasValue)
receipts = receipts.Where(r => r.CashClosureReceiptID == cashClosureReceiptID.Value);
if (cashClosureID.HasValue)
receipts = receipts.Where(r => r.CashClosure.CashClosureID == cashClosureID.Value);
return receipts.ToList();
}
}
/// <summary>
/// Fügt ein neues ColumnAssignment in der Datenbank hinzu
/// </summary>
/// <param name="csvColumn">Die CSV Spalte</param>
/// <param name="databaseTable">Datenbank Tabelle</param>
/// <param name="databaseColumn">Datenbank Spalte</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(int formletterPatternID, string csvColumn, string databaseTable, string databaseColumn)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var columnAssignment = new ColumnAssignment()
{
FormletterPattern = db.FormletterPatterns.Single(fp => fp.FormletterPatternID == formletterPatternID),
CsvColumn = csvColumn,
DatabaseTable = databaseTable,
DatabaseColumn = databaseColumn
};
db.ColumnAssignments.AddObject(columnAssignment);
db.SaveChanges();
return columnAssignment.ColumnAssignmentID;
}
}
/// <summary>
/// Erstellt ein neues Benutzerkonto
/// </summary>
/// <param name="username">Benutzername</param>
/// <param name="password">Passwort</param>
/// <param name="isAdmin">Ob der Benutzer Administrator Rechte hat</param>
/// <param name="imageName">Optionales Bild</param>
/// <returns>Gibt die ID des Eintrags in der Datenbank zurück</returns>
public static int Add(string username, string password, bool isAdmin, string imageName = null, bool isActive = true)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var userAccount = new UserAccount
{
Username = username,
Password = password,
IsAdmin = isAdmin,
ImageName = imageName,
IsActive = isActive,
};
db.UserAccounts.AddObject(userAccount);
db.SaveChanges();
return userAccount.UserAccountID;
}
}
/// <summary>
/// Methode zum einfügen einer neuen Einkommensart
/// </summary>
/// <param name="name">Der Name der Einkommensart</param>
/// <returns>Der ID des Eintrags in der Datenbank</returns>
public static int Add(string name)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var revenueType = new RevenueType
{
Name = name
};
db.RevenueTypes.AddObject(revenueType);
db.SaveChanges();
// Liste aktualisieren
_RevenueTypes = null;
GetRevenueTypeList();
return revenueType.RevenueTypeID;
}
}
/// <summary>
/// Gibt eine Liste mit Kassenabschlüssen zurück
/// </summary>
/// <param name="cashClosureID">Optionale Filterung nach Kassenabschluss ID</param>
/// <param name="closureUserAccountID">Optionale Filterung nach Benutzerkonto</param>
/// <returns>Liste mit Kassenabschlüssen</returns>
public static IEnumerable<CashClosure> GetCashClosures(int? cashClosureID = null, int? closureUserAccountID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var cashClosure = db.CashClosures
.Include("ClosureUserAccount")
.Include("CashClosureReport")
.Include("CashClosureReceipt")
.AsQueryable();
if (cashClosureID.HasValue)
cashClosure = cashClosure.Where(cc => cc.CashClosureID == cashClosureID.Value);
if (closureUserAccountID.HasValue)
cashClosure = cashClosure.Where(cc => cc.ClosureUserAccount.UserAccountID == closureUserAccountID.Value);
return cashClosure.ToList();
}
}
/// <summary>
/// Fügt einen neuen Sponsor der Datenbank hinzu
/// </summary>
/// <param name="fundingTypeId">Die ID der Finanzierungsart</param>
/// <param name="titleId">Die ID des Titels</param>
/// <param name="firstname">Vorname</param>
/// <param name="lastname">Nachname</param>
/// <param name="city">Stadt</param>
/// <param name="street">Strasse</param>
/// <param name="zipCode">Postleitzahl</param>
/// <param name="isFormLetterAllowed">Serienbrief erlaubt</param>
/// <param name="contactPerson">Kontaktperson</param>
/// <param name="email">Emailadresse</param>
/// <param name="faxNo">Faxnummer</param>
/// <param name="mobileNo">Handynumer</param>
/// <param name="phoneNo">Telefonnummer</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(int fundingTypeId, int titleId, string firstname, string lastname, string city, string street, int zipCode,
bool isFormLetterAllowed, string companyName = null, string contactPerson = null, string email = null, string faxNo = null,
string mobileNo = null, string phoneNo = null,bool isCompany=false)
{
using (TafelModelContainer db = new TafelModelContainer())
{
Sponsor sponsor = new Sponsor()
{
CompanyName = companyName,
ContactPerson = contactPerson,
Email = email,
FaxNo = faxNo,
FirstName = firstname,
LastName = lastname,
MobileNo = mobileNo,
PhoneNo = phoneNo,
Street = street,
ZipCode = zipCode,
_IsFormLetterAllowed = isFormLetterAllowed,
City = city,
IsActive = true,
IsCompany=isCompany,
Title = db.Titles.Single(t => t.TitleID == titleId),
FundingType = db.FundingTypes.Single(f => f.FundingTypeID == fundingTypeId),
};
db.Sponsors.AddObject(sponsor);
db.SaveChanges();
return sponsor.SponsorID;
}
}