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); }