/// <summary>
/// Laden der KundenID, und des Ausleih- und Rückgabedatums eines Buches
/// </summary>
public void LoadInfo(int copyId, bool loadFullCostumerInfo = false)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT aus_leihdatum, aus_rückgabedatum, aus_kundenid, aus_buchid FROM t_bd_ausgeliehen WHERE aus_buchid = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, copyId);
while (dr.Read())
{
Costumer = new Costumer();
if (loadFullCostumerInfo)
{
Costumer = new Costumer(int.Parse(dr["aus_kundenid"].ToString()));
}
else
{
Costumer.CostumerId = int.Parse(dr["aus_kundenid"].ToString());
}
Copy = new Copy(int.Parse(dr["aus_buchid"].ToString()));
BorrowDate = (DateTime)dr["aus_leihdatum"];
ReturnDate = (DateTime)dr["aus_rückgabedatum"];
}
dr.Close();
con.Close();
}
public List <Copy> GetUnprintedCopies(string isbn)
{
List <Copy> copies = new List <Copy>();
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(copies);
}
string command = "SELECT bu_id, bu_isbn, " +
"bu_zustandsid, isnull(bu_aufnahmedatum, '01.01.1990') as 'verified_aufnahmedatum', " +
"bu_activated, bu_printed from t_s_buchid left join t_s_zustand on bu_zustandsid = zu_id where bu_isbn = @0 AND bu_printed='0'";
SqlDataReader dr = connection.ExcecuteCommand(command, isbn);
while (dr.Read())
{
Copy copy = new Copy();
copy.CopyId = int.Parse(dr["bu_id"].ToString());
copy.CopyIsbn = dr["bu_isbn"].ToString();
//copy.Condition = new Condition(int.Parse(dr["bu_zustandsid"].ToString()));
//copy.DateRegistration = (DateTime)dr["verified_aufnahmedatum"];
copy.CopyActivated = dr["bu_activated"].ToString().Equals(0) ? false : true;
copy.CopyPrinted = dr["bu_printed"].ToString().Equals(0) ? false : true;
copies.Add(copy);
}
dr.Close();
connection.Close();
return(copies);
}
/// <summary>
/// loads all the data for the copy object
/// </summary>
private void LoadCopy()
{
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return;
}
string command = "SELECT bu_id, bu_isbn, buch_titel, " +
"bu_zustandsid, isnull(bu_aufnahmedatum, '01.01.1990') as 'verified_aufnahmedatum', " +
"bu_activated, bu_printed from t_s_buchid left join t_s_zustand on bu_zustandsid = zu_id " +
"left join t_s_buecher on bu_isbn = buch_isbn where bu_id = @0";
SqlDataReader dr = connection.ExcecuteCommand(command, CopyId);
while (dr.Read())
{
CopyId = int.Parse(dr["bu_id"].ToString());
CopyIsbn = dr["bu_isbn"].ToString();
CopyTitle = dr["buch_titel"].ToString();
Condition = new Condition(int.Parse(dr["bu_zustandsid"].ToString()));
DateRegistration = (DateTime)dr["verified_aufnahmedatum"];
CopyActivated = Convert.ToBoolean(dr["bu_activated"].ToString());
CopyPrinted = dr["bu_printed"].ToString().Equals(0) ? false : true;
}
dr.Close();
connection.Close();
}
/// <summary>
/// loads the subjects of the costumer
/// </summary>
private void LoadCostumerSubjects()
{
CostumerSubjects.Clear();
CostumerAdvancedSubjects.Clear();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "use greenLib Select f_kurzform, f_langform, f_id from t_s_fach_kunde " +
"left join t_s_faecher on f_id = fs_fachid WHERE fs_kundenid = @0 and fs_lk = 1";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, CostumerId);
while (dr.Read())
{
Subject subject = new Subject();
subject.SubjectId = int.Parse(dr["f_id"].ToString());
subject.SubjectNameLong = dr["f_langform"].ToString();
subject.SubjectNameShort = dr["f_kurzform"].ToString();
CostumerAdvancedSubjects.Add(subject);
}
dr.Close();
RawCommand = "use greenLib Select f_kurzform, f_langform, f_id from t_s_fach_kunde " +
"left join t_s_faecher on f_id = fs_fachid WHERE fs_kundenid = @0";
dr = con.ExcecuteCommand(RawCommand, CostumerId);
while (dr.Read())
{
Subject subject = new Subject();
subject.SubjectId = int.Parse(dr["f_id"].ToString());
subject.SubjectNameLong = dr["f_langform"].ToString();
subject.SubjectNameShort = dr["f_kurzform"].ToString();
CostumerSubjects.Add(subject);
}
//for (int i = CostumerAdvancedSubjects.Count; i < 2; i++)
//{
// CostumerAdvancedSubjects.Add(new Subject());
//}
dr.Close();
con.Close();
}
/// <summary>
/// loads the costumer id if firstname, surname and birth date are filled with data
/// </summary>
public void LoadCostumerId()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
CostumerId = -1;
string command = "SELECT kunde_id FROM [dbo].[t_s_kunden] WHERE kunde_vorname = @0 and kunde_nachname = @1 and kunde_geburtsdatum = @2";
SqlDataReader dr = con.ExcecuteCommand(command, CostumerFirstName, CostumerSurname, CostumerBirthDate.Date);
while (dr.Read())
{
CostumerId = int.Parse(dr["kunde_id"].ToString());
}
}
/// <summary>
/// loads the book information by a given id of one of its copies
/// </summary>
/// <param name="copyId"></param>
public void LoadBookByCopyId(int copyId)
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT bu_isbn FROM [dbo].[t_s_buchid] WHERE bu_id = @0";
SqlDataReader dr = con.ExcecuteCommand(command, copyId);
while (dr.Read())
{
BookIsbn = dr["bu_isbn"].ToString();
}
LoadBook();
}
/// <summary>
/// loads all the authors of a book
/// </summary>
private void LoadBookAuthors()
{
BookAuthors.Clear();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT * FROM [dbo].[t_s_buch_autor] WHERE ba_isbn = @0";
SqlDataReader dr = con.ExcecuteCommand(command, BookIsbn);
while (dr.Read())
{
Author author = new Author(int.Parse(dr["ba_autorid"].ToString()));
BookAuthors.Add(author);
}
dr.Close();
}
/// <summary>
/// already exists in disabled mode
/// </summary>
private bool BookAlreadyExistsDisabled()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(false);
}
string command = "SELECT buch_isbn, buch_titel, buch_activated FROM [dbo].[t_s_buecher] WHERE buch_isbn = @0";
SqlDataReader dr = con.ExcecuteCommand(command, BookIsbn);
while (dr.Read())
{
return(true);
}
dr.Close();
con.Close();
return(false);
}
/// <summary>
/// only loads the title of the book
/// </summary>
private void LoadBookShort()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT buch_isbn, buch_titel FROM [dbo].[t_s_buecher] WHERE buch_isbn = @0";
SqlDataReader dr = con.ExcecuteCommand(command, bookIsbn);
while (dr.Read())
{
BookIsbn = dr["buch_isbn"].ToString();
BookTitle = dr["buch_titel"].ToString();
}
dr.Close();
con.Close();
}
public int GetNumberOfPrinted(string isbn)
{
int printedCount = 0;
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(printedCount);
}
string command = "SELECT COUNT(bu_printed) as 'printed' FROM t_s_buchid WHERE bu_isbn = @0 AND bu_printed='1'";
SqlDataReader dr = connection.ExcecuteCommand(command, isbn);
while (dr.Read())
{
printedCount = int.Parse(dr["printed"].ToString());
}
dr.Close();
connection.Close();
return(printedCount);
}
public bool SuggestedBookAlreadyBorrowed(string bookIsbn, int costumerId)
{
bool alreadyBorrowed = false;
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(alreadyBorrowed);
}
string command = "select count(*) as 'Anzahl' from t_bd_ausgeliehen WHERE aus_kundenid=@0 and aus_buchid in (select c.bu_id from t_s_buchid c WHERE c.bu_isbn=@1)";
SqlDataReader dr = con.ExcecuteCommand(command, costumerId, bookIsbn);
while (dr.Read())
{
alreadyBorrowed = Convert.ToInt32(dr["Anzahl"]) > 0;
}
dr.Close();
con.Close();
return(alreadyBorrowed);
}
/// <summary>
/// checks whether the costumer has borrowed a book or not
/// </summary>
/// <returns></returns>
public bool HasBorrowedSomething()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(true);
}
int number = 0;
string command = "SELECT Count(aus_leihnummer) as 'Anzahl' from t_bd_ausgeliehen WHERE aus_kundenid = @0";
SqlDataReader dr = con.ExcecuteCommand(command, CostumerId);
while (dr.Read())
{
number = int.Parse(dr["Anzahl"].ToString());
}
dr.Close();
con.Close();
return(number > 0 ? true : false);
}
public bool AreCopiesAvailable(string isbn)
{
bool areAvailable = false;
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(areAvailable);
}
string command = "SELECT COUNT(*) as 'num' FROM t_s_buchid JOIN t_bd_ausgeliehen on bu_id=aus_buchid WHERE bu_isbn=@0";
SqlDataReader dr = connection.ExcecuteCommand(command, isbn);
while (dr.Read())
{
areAvailable = int.Parse(dr["num"].ToString()) > 0 ? false : true;
}
dr.Close();
connection.Close();
return(areAvailable);
}
/// <summary>
/// loads the information of the subject
/// </summary>
private void LoadSubject()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT * FROM t_s_faecher WHERE f_id = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, SubjectId);
while (dr.Read())
{
SubjectId = int.Parse(dr["f_id"].ToString());
SubjectNameShort = dr["f_kurzform"].ToString();
SubjectNameLong = dr["f_langform"].ToString();
}
dr.Close();
con.Close();
}
/// <summary>
/// searches for a copy by the isbn
/// </summary>
/// <param name="bookIsbn"></param>
/// <returns>returns the last entry of the result entity</returns>
public int FindIdByIsbn(string bookIsbn)
{
int id = -1;
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(id);
}
string command = "SELECT bu_id from t_s_buchid where bu_isbn = @0";
SqlDataReader dr = connection.ExcecuteCommand(command, bookIsbn);
while (dr.Read())
{
id = int.Parse(dr["bu_id"].ToString());
}
dr.Close();
connection.Close();
return(id);
}
private void LoadAssignment(int grade)
{
SubjectList = new List <Subject>();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT * FROM [dbo].[t_s_fach_stufe] WHERE bf_klassenstufe = @0";
SqlDataReader dr = con.ExcecuteCommand(command, grade);
while (dr.Read())
{
Subject s = new Subject(int.Parse(dr["bf_fachid"].ToString()));
SubjectList.Add(s);
}
dr.Close();
con.Close();
}
////TODO
//private void createExcelFile(DataGridView grid)
//{
// try
// {
// SaveFileDialog dialog = new SaveFileDialog();
// dialog.Filter = "CSV|*.csv";
// dialog.Title = "Als Tabelle abspeichern";
// dialog.ShowDialog();
// //Überprüfen, ob Filename vergeben wurde
// if (dialog.FileName != "")
// {
// //Überprüfen, ob Rows vorhanden sind
// if (grid.RowCount > 0)
// {
// string value = "";
// DataGridViewRow dr = new DataGridViewRow();
// StreamWriter sw = new StreamWriter(dialog.FileName);
// //Head Rows schreiben
// for (int i = 0; i <= grid.Columns.Count - 1; i++)
// {
// if (i > 0)
// {
// sw.Write(";");
// }
// sw.Write(grid.Columns[i].HeaderText);
// }
// sw.WriteLine();
// //Rows aus grid in csv
// for (int j = 0; j <= grid.Rows.Count - 1; j++)
// {
// if (j > 0)
// {
// sw.WriteLine();
// }
// dr = grid.Rows[j];
// for (int i = 0; i <= grid.Columns.Count - 1; i++)
// {
// if (i > 0)
// {
// sw.Write(";");
// }
// value = dr.Cells[i].Value.ToString();
// //Replace
// value = value.Replace(',', ' ');
// value = value.Replace(Environment.NewLine, " ");
// sw.Write(value);
// }
// }
// sw.Close();
// MetroMessageBox.Show(this,"Export erfolgreich abgeschlossen", "Datenbank Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// }
// }
// catch
// {
// MetroMessageBox.Show(this,"Beim Exportvorgang ist ein unbekannter Fehler aufgetreten!", "Datenbank Export", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
//}
//public void executeExport(ref DataGridView grid)
//{
// createExcelFile(grid);
//}
/// <summary>
/// Ruft die Spaltennamen aus der Datenbank ab, wird aktuell nicht verwendet
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public List <string> GetSQLColumns(string tableName)
{
List <string> cols = new List <string>();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(cols);
}
string RawCommand = "SELECT column_name FROM information_schema.columns WHERE table_name = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, tableName);
while (dr.Read())
{
cols.Add(dr["column_name"].ToString());
}
cols.RemoveAt(0);
dr.Close();
con.Close();
return(cols);
}
/// <summary>
/// loads the information of the user
/// </summary>
private void LoadUser()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string RawCommand = "SELECT * FROM [dbo].[t_s_benutzer] WHERE b_name = @0";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, userName);
while (dr.Read())
{
UserName = dr["b_name"].ToString();
byte[] binaryString = (byte[])dr["b_password"];
UserPassword = Encoding.UTF8.GetString(binaryString);
PermissionId = int.Parse(dr["b_rechte"].ToString());
PermissionName = GetPermissionById(permissionId);
}
dr.Close();
con.Close();
}
/// <summary>
/// TODO
/// </summary>
/// <param name="isbn"></param>
public List <Copy> SelectAddedCopies(int addedAmount)
{
List <Copy> copies = new List <Copy>();
CustomSqlConnection connection = new CustomSqlConnection();
if (connection.ConnectError())
{
return(copies);
}
string RawCommand = "Select top(@0) bu_id from t_s_buchid where bu_isbn = @1 order by bu_id desc";
SqlDataReader dr = connection.ExcecuteCommand(RawCommand, addedAmount, CopyIsbn);
while (dr.Read())
{
Copy copy = new Copy();
copy.CopyId = Convert.ToInt32(dr["bu_id"]);
copies.Add(copy);
}
dr.Close();
connection.Close();
return(copies);
}
/// <summary>
/// loads all the costumer data
/// </summary>
private void LoadCostumer()
{
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
LoadCostumerSubjects();
string command = "SELECT * FROM [dbo].[t_s_kunden] WHERE kunde_id = @0";
SqlDataReader dr = con.ExcecuteCommand(command, costumerId);
while (dr.Read())
{
CostumerId = int.Parse(dr["kunde_ID"].ToString());
CostumerFirstName = dr["kunde_vorname"].ToString();
CostumerSurname = dr["kunde_nachname"].ToString();
CostumerBirthDate = (DateTime)dr["kunde_geburtsdatum"];
CostumerCity = dr["kunde_ort"].ToString();
CostumerZipcode = dr["kunde_postleitzahl"].ToString();
CostumerStreet = dr["kunde_strasse"].ToString();
CostumerTelephone = dr["kunde_telefonnummer"].ToString();
CostumerHouseNumber = dr["kunde_hausnummer"].ToString();
CostumerEmail = dr["kunde_mail"].ToString();
string schoolClassId = dr["kunde_klasse"].ToString();
if (schoolClassId == "")
{
CostumerSchoolClass = new SchoolClass();
}
else
{
CostumerSchoolClass = new SchoolClass(int.Parse(schoolClassId));
}
CostumerActivated = dr["kunde_activated"].ToString().Equals("1") ? true : false;
}
dr.Close();
con.Close();
}
/// <summary>
/// returns the grade of the costumer
/// </summary>
/// <returns></returns>
public int ReturnGrade()
{
int grade = -1;
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(grade);
}
string command = "SELECT ks_klassenstufe FROM [dbo].t_s_klasse_stufe WHERE ks_klasse = @0";
SqlDataReader dr = con.ExcecuteCommand(command, CostumerSchoolClass.SchoolClassId);
while (dr.Read())
{
if (dr.HasRows)
{
grade = int.Parse(dr["ks_klassenstufe"].ToString());
}
}
dr.Close();
con.Close();
return(grade);
}
/// <summary>
/// loads all the book information
/// </summary>
private void LoadBook()
{
LoadBookAuthors();
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return;
}
string command = "SELECT *, isnull(buch_erscheinungsdatum, '01.01.1990') as 'verified_erscheinungsdatum' " +
"FROM [dbo].[t_s_buecher] left join [dbo].[t_s_genre] on buch_genre_id = ger_id " +
"left join [dbo].[t_s_verlag] on buch_verlag_id = ver_id left join [dbo].[t_s_sprache] on buch_sprache_id = sprach_id " +
"WHERE buch_isbn = @0";
SqlDataReader dr = con.ExcecuteCommand(command, bookIsbn);
while (dr.Read())
{
BookIsbn = dr["buch_isbn"].ToString();
BookTitle = dr["buch_titel"].ToString();
BookGenre = new Genre(int.Parse(dr["buch_genre_id"].ToString()));
BookPublisher = new Publisher(int.Parse(dr["buch_verlag_id"].ToString()));
BookDatePublication = (DateTime)dr["verified_erscheinungsdatum"];
BookLanguage = new Language(int.Parse(dr["buch_sprache_id"].ToString()));
BookEdition = dr["buch_auflage"].ToString();
if (dr["buch_image"] != null && dr["buch_image"].ToString() != "")
{
using (var ms = new MemoryStream((byte[])(dr["buch_image"])))
{
BookImage = Image.FromStream(ms);
}
}
BookPrice = Convert.ToDecimal(dr["buch_neupreis"].ToString().Replace(".", ","));
bookActivated = dr["buch_activated"].ToString().Equals("1");
}
dr.Close();
con.Close();
}
/// <summary>
/// Checks whether the current costumer already exists in database (first name, surname and birthdate).
/// If you want to update a costumer use this method in edit mode
/// </summary>
/// <param name="edit"></param>
/// <returns></returns>
public bool AlreadyExists(bool edit)
{
int newID = -1;
if (!edit)
{
CostumerId = -1;
}
CustomSqlConnection con = new CustomSqlConnection();
if (con.ConnectError())
{
return(false);
}
string RawCommand = "SELECT * FROM [dbo].[t_s_kunden] WHERE kunde_vorname = @0 and kunde_nachname = @1 and kunde_geburtsdatum = @2";
SqlDataReader dr = con.ExcecuteCommand(RawCommand, CostumerFirstName, CostumerSurname, CostumerBirthDate.Date);
while (dr.Read())
{
newID = int.Parse(dr["kunde_id"].ToString());
}
dr.Close();
con.Close();
if (newID == -1)
{
return(false);
}
else if (edit && newID == CostumerId)
{
return(false);
}
else
{
CostumerId = newID;
return(true);
}
}