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