public Loan DeleteLoan(int loanId, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
Loan loan = new Loan();
if (loanId != 0)
{
try
{
con.Open();
loan = FindLoanById(loanId, studentSql, bookSql, typeSql);
cmd.CommandText = "DELETE FROM student WHERE student_id= @ID";
cmd.Parameters.AddWithValue("@ID", loanId);
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{
log.Error("Delete Loan(id)", e);
}
finally
{
con.Close();
}
}
return(loan);
}
public List <Loan> GetAllLoans(StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
List <Loan> matchedLoans = new List <Loan>();
con.Open();
cmd.CommandText = "SELECT * FROM loan";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Loan loan = new Loan();
loan.Id = rdr.GetInt32(0);
loan.Pupil = studentSql.FindStudentById(rdr.GetInt32(1));
loan.Tome = bookSql.FindBookById(rdr.GetInt32(2), typeSql);
loan.LoanDate = rdr.GetDateTime(3);
loan.LoanDue = rdr.GetDateTime(4);
loan.Active = rdr.GetBoolean(5);
loan.Accrued = rdr.GetDecimal(6);
if (loan.Active == true)
{
TimeSpan late = DateTime.Now - loan.LoanDue;
loan.Accrued = late.Days * loan.Tome.TomeType.Penalty;
}
matchedLoans.Add(loan);
}
return(matchedLoans);
}
public List <Loan> GetLoanByAccrued(Decimal accrued, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
List <Loan> matchedLoans = new List <Loan>();
if (accrued != 0)
{
try
{
con.Open();
cmd.CommandText = "SELECT * FROM loan WHERE loan_accrued=" + accrued;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Loan loan = new Loan();
loan.Id = rdr.GetInt32(0);
loan.Pupil = studentSql.FindStudentById(rdr.GetInt32(1));
loan.Tome = bookSql.FindBookById(rdr.GetInt32(2), typeSql);
loan.LoanDate = rdr.GetDateTime(3);
loan.LoanDue = rdr.GetDateTime(4);
loan.Active = rdr.GetBoolean(5);
loan.Accrued = rdr.GetDecimal(6);
matchedLoans.Add(loan);
}
}
catch (MySqlException e)
{
log.Error("Get Loans(accrued) query Failure", e);
}
}
return(matchedLoans);
}
/************************************************************ Loan Getters **/
public Loan FindLoanById(int loanId, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
Loan loan = new Loan();
if (loanId != 0)
{
try
{
con.Open();
cmd.CommandText = "SELECT * FROM loan WHERE loan_id=" + loanId;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
loan.Id = rdr.GetInt32(0);
loan.Pupil = studentSql.FindStudentById(rdr.GetInt32(1));
loan.Tome = bookSql.FindBookById(rdr.GetInt32(2), typeSql);
loan.LoanDate = rdr.GetDateTime(3);
loan.LoanDue = rdr.GetDateTime(4);
loan.Active = rdr.GetBoolean(5);
loan.Accrued = rdr.GetDecimal(6);
}
}
catch (MySqlException e)
{
log.Error("Find Loan(id)", e);
}
finally
{
con.Close();
}
}
return(loan);
}
public List <Loan> GetLoansById(int loanId, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
List <Loan> matchedLoans = new List <Loan>();
if (loanId != 0)
{
try
{
con.Open();
cmd.CommandText = "SELECT * FROM loan WHERE loan_id=" + loanId;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Loan loan = new Loan();
loan.Id = rdr.GetInt32(0);
loan.Pupil = studentSql.FindStudentById(rdr.GetInt32(1));
loan.Tome = bookSql.FindBookById(rdr.GetInt32(2), typeSql);
loan.LoanDate = rdr.GetDateTime(3);
loan.LoanDue = rdr.GetDateTime(4);
loan.Active = rdr.GetBoolean(5);
loan.Accrued = rdr.GetDecimal(6);
if (loan.Active == true)
{
TimeSpan late = DateTime.Now - loan.LoanDue;
loan.Accrued = late.Days * loan.Tome.TomeType.Penalty;
}
matchedLoans.Add(loan);
}
}
catch (MySqlException e)
{
log.Error("Get Loans(id) Query Failure", e);
}
finally
{
con.Close();
}
}
return(matchedLoans);
}
public Loan InsertLoan(int studentId, int bookId, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
Loan loan = new Loan();
if (studentId != 0 && bookId != 0)
{
try
{
DateTime loanDate = DateTime.Now;
Book book = bookSql.FindBookById(bookId, typeSql);
TimeSpan duration = book.TomeType.Duration;
DateTime loanDue = loanDate + duration;
Boolean active = true;
Decimal accrued = 0.0m;
con.Open();
cmd.CommandText = "INSERT INTO loan (student_id, book_id, loan_date, loan_due, loan_active, loan_accrued) VALUES(@STUDENT, @BOOK, @DATE, @DUE, @ACTIVE, @ACCRUED)";
cmd.Parameters.AddWithValue("@STUDENT", studentId);
cmd.Parameters.AddWithValue("@BOOK", bookId);
cmd.Parameters.AddWithValue("@DATE", loanDate);
cmd.Parameters.AddWithValue("@DUE", loanDue);
cmd.Parameters.AddWithValue("@ACTIVE", active);
cmd.Parameters.AddWithValue("@ACCRUED", accrued);
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{
log.Error("Insert Loan Query Failure", e);
}
finally
{
con.Close();
}
}
return(loan);
}
public Loan UpdateLoan(int loanId, int studentId, int bookId, DateTime loanDate, DateTime loanDue, Boolean active, Decimal accrued, StudentSql studentSql, BookSql bookSql, TypeSql typeSql)
{
Loan loan = new Loan();
if (loanId != 0 && studentId != 0 && bookId != 0 && loanDate != null && loanDue != null && active != false && accrued != 0)
{
try
{
con.Open();
cmd.CommandText = "UPDATE loan SET student_id=@STUDENT, book_id=@BOOK, loan_date=@DATE, loan_due=@DUE, loan_active=@ACTIVE, loan_accrued=@ACCRUED WHERE loan_id=@LOAN";
cmd.Parameters.AddWithValue("@LOAN", loanId);
cmd.Parameters.AddWithValue("@STUDENT", studentId);
cmd.Parameters.AddWithValue("@BOOK", bookId);
cmd.Parameters.AddWithValue("@DATE", loanDate);
cmd.Parameters.AddWithValue("@DUE", loanDue);
cmd.Parameters.AddWithValue("@ACTIVE", active);
cmd.Parameters.AddWithValue("@ACCRUED", accrued);
cmd.ExecuteNonQuery();
loan = FindLoanById(loanId, studentSql, bookSql, typeSql);
}
catch (MySqlException e)
{
log.Error("Update Loan(*) Query Failure", e);
}
finally
{
con.Close();
}
}
return(loan);
}