internal ReturnData updateBookDetails()
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "update bookDetails set isbnCode=@isbnCode, author=@author,bookTitle=@bookTitle,bookCategory=@bookCategory,publisher=@publisher,bookURL=@bookURL,bookImage=@bookImage,bookPDF=@bookPDF,bookExternalURL=@bookExternalURL,price=@price,qty=@qty,bookDescription=@bookDescription where bookID=@bookID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", this.bookID);
cmd.Parameters.AddWithValue("@bookTitle", this.bookTitle);
cmd.Parameters.AddWithValue("@isbnCode", this.isbnCode);
cmd.Parameters.AddWithValue("@bookCategory", this.bookCategory);
cmd.Parameters.AddWithValue("@author", this.author);
cmd.Parameters.AddWithValue("@publisher", this.publisher);
cmd.Parameters.AddWithValue("@price", this.price);
cmd.Parameters.AddWithValue("@bookURL", this.bookURL);
cmd.Parameters.AddWithValue("@bookImage", this.bookImage);
cmd.Parameters.AddWithValue("@bookPDF", this.bookPDF);
cmd.Parameters.AddWithValue("@bookExternalURL", this.bookExternalURL);
cmd.Parameters.AddWithValue("@qty", this.qty);
cmd.Parameters.AddWithValue("@bookDescription", this.bookDescription);
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)
{
//string bookID = getBookIDFromISBNCode(isbnCode);
//string bookCode = this.bookCategory.Substring(0, 4);
//int bookQty = int.Parse(this.qty);
//BookCodeDetails bookCodeDetails = new BookCodeDetails();
//bookCodeDetails.addBookCode(bookID, bookCode, bookQty);
rd.status = 1;
rd.message = "OK";
// rd.para1 = temp_invoice_id;
}
return(rd);
}
internal ReturnData addStudentDetails()
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into studentDetails (studentID,first_name,last_name,email,course_name,year_name,phone,address,city,state,zip,role,note_on_user) values (@studentID,@first_name,@last_name,@email,@course_name,@year_name,@phone,@address,@city,@state,@zip,@role,@note_on_user) ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@studentID", this.studentID);
cmd.Parameters.AddWithValue("@first_name", this.first_name);
cmd.Parameters.AddWithValue("@last_name", this.last_name);
cmd.Parameters.AddWithValue("@email", this.email);
cmd.Parameters.AddWithValue("@course_name", this.course_name);
cmd.Parameters.AddWithValue("@year_name", this.year_name);
cmd.Parameters.AddWithValue("@phone", this.phone);
cmd.Parameters.AddWithValue("@address", this.address);
cmd.Parameters.AddWithValue("@city", this.city);
cmd.Parameters.AddWithValue("@state", this.state);
cmd.Parameters.AddWithValue("@zip", this.zip);
cmd.Parameters.AddWithValue("@role", this.role);
cmd.Parameters.AddWithValue("@note_on_user", this.note_on_user);
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";
UserAccess userAccess = new UserAccess();
userAccess.createUser(this.studentID, this.first_name, this.last_name, this.email, this.phone, "Student");
// rd.para1 = temp_invoice_id;
}
return(rd);
}
internal ReturnData addRequestBook()
{
ReturnData rd = new ReturnData();
string isBookAvailabe = getRequestID(this.bookName);
if (isBookAvailabe == "")
{
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into requestedBook (bookName,bookURL,BookDesc,requestBy) values (@bookName,@bookURL,@BookDesc,@studentID) ";
SqlCommand cmd = new SqlCommand(sql, con);
//byte[] imageData = File.ReadAllBytes(this.imageofBook);
cmd.Parameters.AddWithValue("@bookName", this.bookName);
cmd.Parameters.AddWithValue("@bookURL", this.bookURL);
cmd.Parameters.AddWithValue("@BookDesc", this.BookDesc);
cmd.Parameters.AddWithValue("@studentID", this.studentID);
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";
addLike(getRequestID(this.bookName), this.studentID);
}
}
else
{
rd.status = 1;
rd.message = "This book already requested";
}
return(rd);
}
internal ReturnData addLike(string requestID, string studentID)
{
ReturnData rd = new ReturnData();
string isAlreadyLike = getstudentID(requestID, studentID);
if (isAlreadyLike == "")
{
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into requestedBookLikeDetails (requestID,studentID) values (@requestID,@studentID) ";
SqlCommand cmd = new SqlCommand(sql, con);
//byte[] imageData = File.ReadAllBytes(this.imageofBook);
cmd.Parameters.AddWithValue("@requestID", requestID);
cmd.Parameters.AddWithValue("@studentID", studentID);
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";
}
}
else
{
rd.status = 1;
rd.message = "Already Liked";
}
return(rd);
}
internal ReturnData forgetPassword()
{
ReturnData rd = new ReturnData();
if (this.user_id == null || this.user_id == "")
{
rd.status = 0;
rd.message = "Not a valid username of password";
return(rd);
}
string sql = "select * from user_profile where user_id=@para_user_id and status = 'Active' ";
SqlConnection con = new SqlConnection(user_db_connection_string);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@para_user_id", this.user_id);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
/*rdr.Read();
* rd.status = 1;
* rd.message = "OK";
* rd.para1 = get_token(rdr["user_id"].ToString());*/
email = rdr["email"].ToString();
sendRandomNumber(email);
rd.status = 0;
rd.message = "Check your email";
return(rd);
}
if (!rdr.HasRows)
{
rd.status = 0;
rd.message = "Not a valid username";
return(rd);
}
con.Close();
return(rd);
}
internal ReturnData makePayments(string studentID, double totalFineAmount, string reason, string TransactionID, string branchName, bool PaymentApproved)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into paymentDetails (studentID,paidAmount,reason,paidDate,TransactionID, branchName, PaymentApproved) values (@studentID,@paidAmount,@reason,@paidDate,@TransactionID,@branchName, @PaymentApproved) ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@studentID", studentID);
cmd.Parameters.AddWithValue("@paidAmount", totalFineAmount);
cmd.Parameters.AddWithValue("@reason", reason);
CommonFunctions c = new CommonFunctions();
cmd.Parameters.AddWithValue("@paidDate", c.getServerDate());
cmd.Parameters.AddWithValue("@TransactionID", TransactionID);
cmd.Parameters.AddWithValue("@branchName", branchName);
cmd.Parameters.AddWithValue("@PaymentApproved", PaymentApproved);
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";
}
return(rd);
}
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);
}
private ReturnData insertReserveDetails(string bookCode, string studentID)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into bookReserveDetails (bookCode,studentID,reservedDate,releseBeforeDate) values (@bookCode,@studentID,@reservedDate,@releseBeforeDate) ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookCode", bookCode);
cmd.Parameters.AddWithValue("@studentID", studentID);
cmd.Parameters.AddWithValue("@reservedDate", new CommonFunctions().getCurrentDate());
cmd.Parameters.AddWithValue("@releseBeforeDate", getReleseBeforeDateDate());
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);
}
public ReturnData addBookIssuingDetails()
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into bookIssuingDetails (bookCode,studentID,issueDate,returnDate) values (@bookID,@studentID,@issueDate,@returnDate) ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", this.bookID);
cmd.Parameters.AddWithValue("@studentID", this.studentID);
cmd.Parameters.AddWithValue("@issueDate", this.issueDate);
cmd.Parameters.AddWithValue("@returnDate", this.returnDate);
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 ReturnData changeForgetPassword()
{
ReturnData rd = new ReturnData();
rd.status = validateCode(this.code, this.email);
if (rd.status > 0)
{
SqlConnection con = new SqlConnection(user_db_connection_string);
string sql = "update user_profile set password=@password where email=@email";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@password", this.password);
cmd.Parameters.AddWithValue("@email", this.email);
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)
{
deleteCode(this.email);
rd.status = 1;
rd.message = "updated";
// rd.para1 = temp_invoice_id;
}
}
else
{
rd.status = 0;
rd.message = "This code is not valid for given email";
}
return(rd);
}
internal ReturnData updateBookDueDate(string studentID)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "Update bookIssuingDetails set returnDate=@returnDate where studentID=@studentID and returnDate<@backTo14Days";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@studentID", studentID);
CommonFunctions cf = new CommonFunctions();
cmd.Parameters.AddWithValue("@returnDate", cf.getServerDate());
cmd.Parameters.AddWithValue("@backTo14Days", getbackTo14Days());
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";
new BookCodeDetails().updateBookCodeDetails(bookID);
// rd.para1 = temp_invoice_id;
}
return(rd);
}
internal void addBookCode(string bookID, string bookCode, int bookQty)
{
ReturnData rd = new ReturnData();
int bookCountForThisBookID = getBookCountForThisBookID(bookID);
for (int i = bookCountForThisBookID; i < (bookQty + bookCountForThisBookID); i++)
{
string PaddedResult = (i + 1).ToString().PadLeft(3, '0');
string newBookCode = bookCode + PaddedResult;
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into bookCodeDetails (bookID,bookCode) values (@bookID,@bookCode) ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", bookID);
cmd.Parameters.AddWithValue("@bookCode", newBookCode);
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";
// rd.para1 = temp_invoice_id;
}
}
}
internal ReturnData isBookNotReserved(string bookID)
{
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "select * from bookReserveDetails where bookCode=@bookCode ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookCode", bookID);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
ReturnData rd = new ReturnData();
if (rdr.HasRows)
{
rd.status = 0;
}
else
{
rd.status = 1;
}
con.Close();
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 ReturnData validateBookID(string bookID)
{
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "select * from bookDetails where bookID=@bookID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", bookID);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
ReturnData rd = new ReturnData();
if (rdr.HasRows)
{
rd.status = 1;
}
else
{
rd.message = "Book Not found for this ID!";
rd.status = 0;
}
con.Close();
return(rd);
}
public ReturnData login()
{
ReturnData rd = new ReturnData();
if (this.user_id == null || this.password == null || this.user_id == "" || this.password == "")
{
rd.status = 0;
rd.message = "Not a valid username of password";
return(rd);
}
string sql = "select * from user_profile where user_id=@para_user_id and password=@para_password and status = 'Active' ";
SqlConnection con = new SqlConnection(user_db_connection_string);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@para_user_id", this.user_id);
cmd.Parameters.AddWithValue("@para_password", this.password);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
rd.status = 1;
rd.message = "OK";
rd.para1 = get_token(rdr["user_id"].ToString());
}
else
{
rd.status = 0;
rd.message = "Not a valid username of password";
}
con.Close();
return(rd);
}
internal ReturnData addCourse(Courses courses)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into courseDetails (CourseName,departmentID) values (@CourseName,@departmentID) ";
SqlCommand cmd = new SqlCommand(sql, con);
//byte[] imageData = File.ReadAllBytes(this.imageofBook);
cmd.Parameters.AddWithValue("@CourseName", courses.CourseName);
cmd.Parameters.AddWithValue("@departmentID", courses.departmentID);
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";
// rd.para1 = temp_invoice_id;
}
return(rd);
}
internal ReturnData ReserveBook(bookReserveDetails bookReserveDetails)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "select bookCode from bookCodeDetails where bookID=@bookID and Borrowed='0' and Active='1' and reserved='0'";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", bookReserveDetails.bookID);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
bookCode = rdr["bookCode"].ToString();
rd = checkCountOfBooksReserved(bookReserveDetails.studentID);
if (rd.status > 0)
{
rd = changeReserveStatus(bookCode);
if (rd.status > 0)
{
rd = insertReserveDetails(bookReserveDetails.bookCode, bookReserveDetails.studentID);
}
}
}
else
{
rd.message = "No Book to Reserve";
rd.status = 0;
}
con.Close();
return(rd);
}
internal void deleteFromBookIssungDetails(string bookID, string studentID)
{
ReturnData rd = new ReturnData();
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "Delete from bookIssuingDetails where bookCode=@bookID and studentID=@studentID";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@bookID", bookID);
cmd.Parameters.AddWithValue("@studentID", studentID);
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";
new BookCodeDetails().updateBookCodeDetails(bookID);
// rd.para1 = temp_invoice_id;
}
}
public ReturnData addBookDetails()
{
ReturnData rd = new ReturnData();
string isBookAvailabe = getBookIDFromISBNCode(isbnCode);
if (isBookAvailabe == "")
{
SqlConnection con = new SqlConnection(db_connection_string);
string sql = "";
sql = "insert into bookDetails (isbnCode,author,bookTitle,bookCategory,publisher,bookURL,bookImage,bookPDF,bookExternalURL,price,qty,bookDescription) values (@isbnCode,@author,@bookTitle,@bookCategory,@publisher,@bookURL,@bookImage,@bookPDF,@bookExternalURL,@price,@qty,@bookDescription) ";
SqlCommand cmd = new SqlCommand(sql, con);
//byte[] imageData = File.ReadAllBytes(this.imageofBook);
cmd.Parameters.AddWithValue("@bookTitle", this.bookTitle);
cmd.Parameters.AddWithValue("@isbnCode", this.isbnCode);
cmd.Parameters.AddWithValue("@bookCategory", this.bookCategory);
cmd.Parameters.AddWithValue("@author", this.author);
cmd.Parameters.AddWithValue("@publisher", this.publisher);
cmd.Parameters.AddWithValue("@price", this.price);
cmd.Parameters.AddWithValue("@bookURL", this.bookURL);
cmd.Parameters.AddWithValue("@bookImage", this.bookImage);
cmd.Parameters.AddWithValue("@bookPDF", this.bookPDF);
cmd.Parameters.AddWithValue("@bookExternalURL", this.bookExternalURL);
cmd.Parameters.AddWithValue("@qty", this.qty);
cmd.Parameters.AddWithValue("@bookDescription", this.bookDescription);
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)
{
string bookID = getBookIDFromISBNCode(isbnCode);
string bookCode = this.bookCategory.Substring(0, 4);
int bookQty = int.Parse(this.qty);
BookCodeDetails bookCodeDetails = new BookCodeDetails();
bookCodeDetails.addBookCode(bookID, bookCode, bookQty);
rd.status = 1;
rd.message = "OK";
// rd.para1 = temp_invoice_id;
}
}
else
{
string bookID = getBookIDFromISBNCode(isbnCode);
string bookCode = this.bookCategory.Substring(0, 4);
int bookQty = int.Parse(this.qty);
BookCodeDetails bookCodeDetails = new BookCodeDetails();
bookCodeDetails.addBookCode(bookID, bookCode, bookQty);
rd.status = 1;
rd.message = "OK";
}
return(rd);
}