/// <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>
/// 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>
/// 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>
/// 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>
/// 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>
/// 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>
/// 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="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>
/// 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 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>
/// 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>
/// 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>
/// 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>
/// 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>
/// Fügt ein neues Kind in der Datenbank hinzu
/// </summary>
/// <param name="personId">Die Personen ID</param>
/// <param name="firstname">Vorname</param>
/// <param name="lastname">Nachname</param>
/// <param name="dateOfBirth">Geburtsdatum</param>
/// <param name="isFemale">Wert ob es sich um ein Junge oder Mädchen handelt</param>
/// <returns>Die ID des Eintrags in der Datenbank</returns>
public static int Add(int personId, string firstname, string lastname, DateTime dateOfBirth, bool isFemale)
{
using (TafelModelContainer db = new TafelModelContainer())
{
Child child = new Child()
{
FirstName = firstname,
LastName = lastname,
DateOfBirth = dateOfBirth,
Person = db.Persons.Single(p => p.PersonID == personId),
IsFemale = isFemale,
};
db.Children.AddObject(child);
db.SaveChanges();
return child.ChildID;
}
}
/// <summary>
/// Fügt eine neue Notiz der Datenbank hinzu
/// </summary>
/// <param name="name">Der Name der Notiz</param>
/// <param name="description">Die Beschreibung der Notiz</param>
/// <returns>Gibt die ID des Eintrags aus der Datenbank zurück</returns>
public static int Add(string name, string description)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var note = new Note()
{
Name = name,
Description = description, // max. Länge liegt bei 4000 Zeichen -> DB spezifisch
CreationDate = DateTime.Now
};
db.Notes.AddObject(note);
db.SaveChanges();
RefreshNoteList();
return note.NoteID;
}
}
/// <summary>
/// Methode zum hinzufügen eines neuen Filters
/// </summary>
/// <param name="table">Tabelle des Filters</param>
/// <param name="column">Spalte des Filters</param>
/// <param name="type">Typ des Filters</param>
/// <param name="operation">Operation des Filters</param>
/// <param name="value">Wert des Filters</param>
/// <returns>Die ID in der Datenbank</returns>
public static int Add(int filtersetID, string table, string type, string operation, string value)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var filter = new Filter()
{
FilterSet = db.FilterSets.Single(fs => fs.FilterSetID == filtersetID),
Table = table,
Type = type,
Operation = operation,
Value = value
};
db.Filters.AddObject(filter);
db.SaveChanges();
return filter.FilterID;
}
}
/// <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>
/// Fügt eine neue Kassenabrechnung der Datenbank hinzu
/// </summary>
/// <param name="cashClosureID">Die benötigte Kassenabschluss ID</param>
/// <param name="printDone">Druck beendet</param>
/// <param name="printDate">Druckdatum</param>
/// <param name="printUserAccountID">Druck Benutzer</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,
int? printUserAccountID = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var receipt = new CashClosureReceipt
{
CashClosure = db.CashClosures.Single(cc => cc.CashClosureID == cashClosureID),
PrintDone = printDone,
PrintDate = printDate,
PrintUserAccount = (printUserAccountID.HasValue) ? db.UserAccounts.Single(ua => ua.UserAccountID == printUserAccountID.Value) : null,
};
db.CashClosureReceipts.AddObject(receipt);
db.SaveChanges();
return receipt.CashClosureReceiptID;
}
}
/// <summary>
/// Fügt einen Einkommenseintrag der Datenbank hinzu
/// </summary>
/// <param name="personID">Die benötigte Personen ID</param>
/// <param name="revenueTypeID">Die Einkommensart ID</param>
/// <param name="amount">Der Betrag</param>
/// <param name="description">Optionale Beschreibung</param>
/// <returns>Gibt die ID des Eintrags aus der Datenbank zurück</returns>
public static int Add(int personID, int revenueTypeID, double amount, DateTime startDate, DateTime? endDate = null, string description = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var revenue = new Revenue
{
Amount = amount,
Description = description,
RevenueType = db.RevenueTypes.Single(r => r.RevenueTypeID == revenueTypeID),
Person = db.Persons.Single(p => p.PersonID == personID),
StartDate = startDate,
EndDate = endDate
};
db.Revenues.AddObject(revenue);
db.SaveChanges();
return revenue.RevenueID;
}
}
/// <summary>
/// Methode zum hinzufügen eines neuen Serienbrief Musters
/// </summary>
/// <param name="filterSetID">Die FilterSet ID in der Datenbank</param>
/// <param name="columnAssignmentID">Die ColumnAssignment ID in der Datenbank</param>
/// <param name="name">Name des Musters</param>
/// <param name="saluationM"></param>
/// <param name="saluationF"></param>
/// <param name="saluationNT"></param>
/// <param name="filename">Dateiname des Serienbriefs</param>
/// <param name="text">Text des Serienbriefs</param>
/// <returns></returns>
public static int Add(string name, string saluationM, string saluationF, string saluationNT, string filename, string text)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var formletterPattern = new FormletterPattern()
{
Name = name,
SaluationM = saluationM,
SaluationF = saluationF,
SaluationNT = saluationNT,
FileName = filename,
Text = text
};
db.FormletterPatterns.AddObject(formletterPattern);
db.SaveChanges();
return formletterPattern.FormletterPatternID;
}
}
/// <summary>
/// Fügt einen neuen Kassenabschluss der Datenbank hinzu
/// </summary>
/// <param name="cashClosureUserAccountID">Die UserAccount ID</param>
/// <param name="closureDate">Datum des Kassenabschlusses</param>
/// <param name="comment">Kommentar</param>
/// <returns></returns>
public static int Add(int cashClosureUserAccountID, DateTime closureDate, double revenue, double expense, string comment = null)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var cashClosure = new CashClosure
{
ClosureDate = closureDate,
ClosureUserAccount = db.UserAccounts.Single(u => u.UserAccountID == cashClosureUserAccountID),
Comment = comment,
Sum = (revenue - expense),
Expense = expense,
Revenue = revenue
};
db.CashClosures.AddObject(cashClosure);
db.SaveChanges();
return cashClosure.CashClosureID;
}
}
/// <summary>
/// Speichert einen neuen Eintrag in der Datenbank
/// </summary>
/// <param name="accountID">Konto ID welche benötigt wird</param>
/// <param name="date">Datum des Eintrags</param>
/// <param name="amount">Betrag des Eintrags</param>
/// <param name="purchase"></param>
/// <param name="srcAccountNumber">Quell Kontonummer</param>
/// <param name="targetAccountNumber">Ziel Kontonummer</param>
/// <param name="teamId">Optionale Team ID</param>
/// <param name="sponsorId">Optionale Sponsor ID</param>
/// <param name="description">Optionale Beschreibung</param>
/// <param name="isRevenue">Boolean Wert ob es sich um ein Einkommen oder Ausgabe handelt</param>
/// <returns>Gibt die ID des gespeicherten Eintrags in der Datenbank zurück</returns>
public static int Add(int srcAccountID, int targetAccountID,
double amount, int? personID = null, int? userAccountID = null, string description = null, bool isCorrection = false)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var booking = new Booking()
{
Date = DateTime.Now,
Description = description,
SourceAccount = db.Accounts.Single(a => a.AccountID == srcAccountID),
TargetAccount = db.Accounts.Single(a => a.AccountID == targetAccountID),
UserAccount = (userAccountID.HasValue) ? db.UserAccounts.Single(u => u.UserAccountID == userAccountID.Value) : null,
Amount = amount,
IsCorrection = isCorrection,
Person = (personID.HasValue) ? db.Persons.Single(p => p.PersonID == personID.Value) : null,
};
db.Bookings.AddObject(booking);
db.SaveChanges();
return booking.BookingID;
}
}
/// <summary>
/// Methode zum hinzufügen eines neuen Kontos
/// </summary>
/// <param name="name">Kontoname</param>
/// <param name="accountNumber">Kontonummer</param>
/// <param name="image">Bild</param>
/// <param name="description">Optionale Beschreibung</param>
/// <returns>Gibt den ID Wert des gespeicherten Eintrags in der Datenbank zurück</returns>
public static int Add(string name, int accountNumber, bool isOfficial, ZeroPeriod zeroPeriod,
bool isFixed, double? latestBalance = null, string description = null, bool isCapital = false)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var account = new Account()
{
Name = name,
Number = accountNumber,
Description = description,
IsOfficial = isOfficial,
ZeroPeriod = (int)zeroPeriod,
IsFixed = isFixed,
LatestBalance = ((latestBalance.HasValue) ? latestBalance.Value : 0),
IsCapital=isCapital
};
db.Accounts.AddObject(account);
db.SaveChanges();
return account.AccountID;
}
}
/// <summary>
/// Aktualisiert einen bestehenden Einkommen Eintrag in der Datenbank
/// </summary>
/// <param name="revenueID">Die benötigte Einkommens ID</param>
/// <param name="revenueTypeID">Einkommensart ID</param>
/// <param name="amount">Der Betrag</param>
/// <param name="description">Eine Beschreibung</param>
public static void Update(int revenueID, int revenueTypeID, double amount, string description, DateTime startDate, DateTime? endDate)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var revenue = db.Revenues.Single(r => r.RevenueID == revenueID);
revenue.RevenueType = db.RevenueTypes.Single(r => r.RevenueTypeID == revenueTypeID);
revenue.Amount = amount;
revenue.Description = description;
revenue.StartDate = startDate;
revenue.EndDate = endDate;
db.SaveChanges();
}
}
/// <summary>
/// Aktualisiert einen bestehenden Eintrag in der Datenbank
/// </summary>
/// <param name="cashClosureReportID"></param>
/// <param name="cashClosureID">Kassenabschluss ID</param>
/// <param name="printDone">Wurde gedruckt</param>
/// <param name="printDate">Druckdatum</param>
/// <param name="printUserAccountID">Druckuser</param>
/// <param name="done"></param>
/// <param name="doneDate"></param>
/// <param name="doneUserAccountID"></param>
/// <param name="revenues"></param>
/// <param name="expenses"></param>
/// <param name="sum"></param>
public static void Update(int cashClosureReportID, int cashClosureID, bool printDone, DateTime printDate,
int? printUserAccountID, bool done, DateTime? doneDate, int? doneUserAccountID)
{
using (TafelModelContainer db = new TafelModelContainer())
{
var cashClosureReport = db.CashClosureReports.Single(ccr => ccr.CashClosureReportID == cashClosureReportID);
cashClosureReport.CashClosure = db.CashClosures.Single(cc => cc.CashClosureID == cashClosureID);
cashClosureReport.PrintDone = printDone;
cashClosureReport.PrintDate = printDate;
cashClosureReport.PrintUserAccount = (printUserAccountID.HasValue) ? db.UserAccounts.Single(ua => ua.UserAccountID == printUserAccountID.Value) : null;
cashClosureReport.Done = done;
cashClosureReport.DoneDate = doneDate;
cashClosureReport.DoneUserAccount = (doneUserAccountID.HasValue) ? db.UserAccounts.Single(ua => ua.UserAccountID == doneUserAccountID.Value) : null;
db.SaveChanges();
}
}
/// <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;
}
}
