internal ReturnData validateIssueingBook(bookIssuingDetails bookIssuingDetails) { ReturnData rd = new ReturnData(); ReturnData isStudent = new studentDetails().validateStudentID(bookIssuingDetails.studentID); if (isStudent.status == 1) { ReturnData isStudentEligibleToBorrowBook = studentBookRecodeCount(bookIssuingDetails.studentID); if (isStudentEligibleToBorrowBook.status == 1) { ReturnData isBookAlreadyBorrowed = new BookCodeDetails().isBookAlreadyBorrowed(bookIssuingDetails.bookID); if (isBookAlreadyBorrowed.status == 1) { addBookIssuingDetails(); } else { rd.message = "Check the BookCode."; } } else { rd.message = "Student Has borrowed 2 Books"; } } else { rd.message = "Student ID is not Valid"; } return(rd); }
internal List <bookIssuingDetails> getBookIssuedDetails() { bookIssuingDetails bookIssuingDetails = new bookIssuingDetails(); List <bookIssuingDetails> list = new List <bookIssuingDetails>(); string sql = ""; SqlCommand cmd = new SqlCommand(); sql = "select * from bookIssuingDetails BID,bookDetails BD, studentDetails SD, bookCodeDetails BCD where BD.bookID=BCD.bookID and BCD.bookCode=BID.bookCode and BID.studentID=SD.studentID"; SqlConnection con = new SqlConnection(db_connection_string); cmd.CommandText = sql; cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { bookIssuingDetails c = new bookIssuingDetails(); c.bookCode = rdr["bookCode"].ToString(); c.studentID = rdr["studentID"].ToString(); c.first_name = rdr["first_name"].ToString(); c.last_name = rdr["last_name"].ToString(); c.bookTitle = rdr["bookTitle"].ToString(); c.returnDate = DateTime.Parse(rdr["returnDate"].ToString()); c.issueDate = DateTime.Parse(rdr["issueDate"].ToString()); c.fineAmount = getfineAmount(studentID, c.returnDate); c.returnDateString = String.Format("{0:yyyy - MM - dd}", c.returnDate); c.issueDateString = String.Format("{0:yyyy - MM - dd}", c.issueDate); list.Add(c); } con.Close(); return(list); }
internal double getfineAmount(string studentID, DateTime returnDate) { bookIssuingDetails bookIssuingDetails = new bookIssuingDetails(); List <bookIssuingDetails> list = new List <bookIssuingDetails>(); string sql = ""; SqlCommand cmd = new SqlCommand(); sql = "select * from systemData"; SqlConnection con = new SqlConnection(db_connection_string); cmd.CommandText = sql; cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); double fine = 0; while (rdr.Read()) { fine = Convert.ToDouble(rdr["fineAmountPerDay"]); CommonFunctions c = new CommonFunctions(); dateCount = (DateTime.Parse(c.getServerDate()) - returnDate).TotalDays; fine = fine * dateCount; if (fine < 0) { fine = 0; } } con.Close(); return(fine); }
internal List <bookIssuingDetails> getTransactionDetails(string studentID) { bookIssuingDetails bookIssuingDetails = new bookIssuingDetails(); List <bookIssuingDetails> list = new List <bookIssuingDetails>(); string sql = ""; SqlCommand cmd = new SqlCommand(); sql = "select * from bookIssuingDetails BID,bookDetails BD where BD.bookID=BID.bookID and BID.studentID=@studentID"; SqlConnection con = new SqlConnection(db_connection_string); cmd.Parameters.AddWithValue("@studentID", studentID); cmd.CommandText = sql; cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { bookIssuingDetails c = new bookIssuingDetails(); c.bookID = rdr["bookID"].ToString(); c.bookTitle = rdr["bookTitle"].ToString(); c.returnDate = DateTime.Parse(rdr["returnDate"].ToString()); c.fineAmount = getfineAmount(studentID, c.returnDate); c.returnDateString = String.Format("{0:yyyy - MM - dd}", c.returnDate); list.Add(c); } con.Close(); return(list); }
private List <bookIssuingDetails> getDelayedBooksBrrowedStudentList() { List <bookIssuingDetails> list = new List <bookIssuingDetails>(); string sql = ""; SqlCommand cmd = new SqlCommand(); sql = "select BCD.bookCode as bookCode, SD.studentID as studentID, SD.first_name as first_name, SD.last_name as last_name, SD.email as email, BD.bookTitle as bookTitle, BID.returnDate as returnDate from bookIssuingDetails BID, bookCodeDetails BCD, bookDetails BD, studentDetails SD where SD.studentID=BID.studentID and BD.bookID=BCD.bookID and BCD.bookCode=BID.bookCode and BID.returnDate< CAST(CURRENT_TIMESTAMP AS DATE)"; SqlConnection con = new SqlConnection(db_connection_string); cmd.CommandText = sql; cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { bookIssuingDetails c = new bookIssuingDetails(); c.bookCode = rdr["bookCode"].ToString(); c.studentID = rdr["studentID"].ToString(); c.first_name = rdr["first_name"].ToString(); c.last_name = rdr["last_name"].ToString(); c.bookTitle = rdr["bookTitle"].ToString(); c.email = rdr["email"].ToString(); c.returnDate = DateTime.Parse(rdr["returnDate"].ToString()); c.returnDateString = String.Format("{0:yyyy - MM - dd}", c.returnDate); list.Add(c); } con.Close(); return(list); }
public ReturnData releaseTheBook(bookIssuingDetailsHistory detailsHistory) { ReturnData rd = new ReturnData(); SqlConnection con = new SqlConnection(db_connection_string); string sql = ""; sql = "insert into bookIssuingDetailsHistory (bookID,studentID,returnDate,issueDate) values (@bookID,@studentID,@returnDate,@issueDate) "; SqlCommand cmd = new SqlCommand(sql, con); string returnDate = new CommonFunctions().getCurrentDate(); string issuedDate = new bookIssuingDetails().getBookIssuedDate(detailsHistory.studentID, detailsHistory.bookID); cmd.Parameters.AddWithValue("@bookID", detailsHistory.bookID); cmd.Parameters.AddWithValue("@studentID", detailsHistory.studentID); cmd.Parameters.AddWithValue("@returnDate", returnDate); cmd.Parameters.AddWithValue("@issueDate", issuedDate); int count = 0; con.Open(); try { count = (int)cmd.ExecuteNonQuery(); } catch (Exception Ex) { rd.status = 0; rd.message = Ex.Message; } con.Close(); if (count > 0) { new bookIssuingDetails().deleteFromBookIssungDetails(detailsHistory.bookID, detailsHistory.studentID); rd.status = 1; rd.message = "OK"; // rd.para1 = temp_invoice_id; } return(rd); }
internal ReturnData extendTheBook(bookIssuingDetails bookIssuing) { ReturnData rd = new ReturnData(); SqlConnection con = new SqlConnection(db_connection_string); string sql = ""; sql = "update bookIssuingDetails set returnDate=@returnDate where bookCode=@bookID"; SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.AddWithValue("@bookID", bookIssuing.bookID); cmd.Parameters.AddWithValue("@returnDate", getBookReturnDate()); int count = 0; con.Open(); try { count = (int)cmd.ExecuteNonQuery(); } catch (Exception Ex) { rd.status = 0; rd.message = Ex.Message; } con.Close(); if (count > 0) { rd.status = 1; rd.message = "OK"; BookCodeDetails bookCodeDetails = new BookCodeDetails(); bookCodeDetails.updateBorrowedStatus(bookID); // rd.para1 = temp_invoice_id; } return(rd); }
internal List <bookIssuingDetails> getBookIssuedDetailsFromStudentID(string student_ID) { bookIssuingDetails bookIssuingDetails = new bookIssuingDetails(); List <bookIssuingDetails> list = new List <bookIssuingDetails>(); CommonFunctions cf = new CommonFunctions(); student_ID = "%" + student_ID + "%"; string sql = ""; SqlCommand cmd = new SqlCommand(); sql = "select * from bookIssuingDetails BID,bookDetails BD, studentDetails SD, bookCodeDetails BCD where BD.bookID=BCD.bookID and BCD.bookCode=BID.bookCode and BID.studentID=SD.studentID and SD.studentID Like @student_ID"; SqlConnection con = new SqlConnection(db_connection_string); cmd.Parameters.AddWithValue("@student_ID", student_ID); cmd.CommandText = sql; cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { bookIssuingDetails c = new bookIssuingDetails(); c.bookCode = rdr["bookCode"].ToString(); c.studentID = rdr["studentID"].ToString(); c.first_name = rdr["first_name"].ToString(); c.last_name = rdr["last_name"].ToString(); c.bookTitle = rdr["bookTitle"].ToString(); c.bookDescription = rdr["bookDescription"].ToString(); c.returnDate = DateTime.Parse(rdr["returnDate"].ToString()); c.issueDate = DateTime.Parse(rdr["issueDate"].ToString()); c.dateCount = (c.returnDate - DateTime.Parse(cf.getServerDate())).TotalDays; c.fineAmount = getfineAmount(studentID, c.returnDate); c.returnDateString = String.Format("{0:yyyy - MM - dd}", c.returnDate); c.issueDateString = String.Format("{0:yyyy - MM - dd}", c.issueDate); list.Add(c); } con.Close(); return(list); }