protected void btnReturn_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] iBook;
try
{
SqlDataReader sdr;
Profile pro = new Profile();
//Check if any book is issued for this member.
string sql = "SELECT Count(1) FROM Book_Transaction WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Book_ID = '" + LstBook.SelectedItem.Text + "' And Actual_Return_Date is Null";
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int iB = (int)sdr[0];
if (iB == 0)
{
lblEMsg.Visible = true;
lblEMsg.Text = "No Book is issued for this member, please select the correct member";
return;
}
sdr.Close();
//Insert the data.
iBook = new string[4];
iBook[0] = LstBook.SelectedItem.Text;
iBook[1] = lstMember.SelectedItem.Text;
iBook[2] = txtARDate.Text;
iBook[3] = txtLFee.Text;
bool status;
status = pro.ReturnBook(iBook);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Book Returning Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Book Returned.";
lstMember.Text = "";
LstBook.Text = "";
txtBTitle.Text = "";
txtAuthor.Text = "";
txtPub.Text = "";
txtCat.Text = "";
txtSup.Text = "";
txtRDate.Text = "";
txtIDate.Text = "";
lblName.Text = "";
txtLFee.Text = "";
return;
}
catch (Exception ex)
{
lblEMsg.Visible = true;
lblEMsg.Text = ex.Message;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
Profile pro = new Profile();
try
{
if (lstid.SelectedItem.ToString() == "")
{
lblEMsg.Text = "Please select Publisher ID";
lblEMsg.Visible = true;
}
else
{
bool status;
status = pro.DeletePublisher(lstid.SelectedItem.ToString());
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Publisher deletion Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Publisher deleted.";
lstid.Items.Clear();
//Load all the Publisher First.
string sql = "SELECT Publisher_ID FROM Publisher where Status = 'A'";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
while (sdr.Read())
{
lstid.Items.Add(sdr["Publisher_ID"].ToString().Trim());
}
sdr.Close();
txtPName.Text = "";
txtPYear.Text = "";
txtPHouse.Text = "";
txtPEdition.Text = "";
txtAdd.Text = "";
txtPhone.Text = "";
txtFax.Text = "";
txtemail.Text = "";
chkStatus.Checked = true;
return;
//txtCName.Text = "";
//txtcDesc.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Catg;
try
{
string sql = "SELECT COUNT(*) FROM CATEGORY WHERE CATEGORY_NAME = '" + txtCName.Text + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int RegNo = (int)sdr[0];
if (RegNo > 0)
{
lblEMsg.Text = "Category Name already exists.";
lblEMsg.Visible = true;
sdr.Close();
return;
}
else
{
Catg = new string[15];
Catg[0] = txtCategory.Text;
Catg[1] = txtCName.Text;
Catg[2] = txtcDesc.Text;
if (chkStatus.Checked == true)
{
Catg[3] = "A";
}
else
{
Catg[3] = "I";
}
bool status;
status = pro.AddCategory(Catg);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Category creation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Category added.";
ReadCategory();
txtCName.Text = "";
txtcDesc.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
Profile pro = new Profile();
try
{
if (lstbook.SelectedItem.ToString() == "")
{
lblEMsg.Text = "Please select Book No";
lblEMsg.Visible = true;
}
else
{
bool status;
status = pro.DeleteBook(lstbook.SelectedItem.ToString());
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Book deletion Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Book deleted.";
lstbook.Items.Clear();
//Load all the Publisher First.
string sql = "SELECT Book_ID FROM Book_Master where Status = 'A'";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
lstbook.Items.Add("");
while (sdr.Read())
{
lstbook.Items.Add(sdr["Book_ID"].ToString().Trim());
}
sdr.Close();
txtBTitle.Text = "";
LstAuthor.Text = "";
LstCategory.Text = "";
LstPublisher.Text = "";
LstSupplier.Text = "";
txtKey.Text = "";
txtIsbn.Text = "";
txtQuantity.Text = "";
txtPrice.Text = "";
chkStatus.Checked = false;
lblEMsg.Visible = false;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void LoginButton_Click(object sender, EventArgs e)
{
try
{
string strRole;
string sql = "SELECT MEMBER_ID, USER_NAME, PASSWORD, FIRST_NAME, MIDDLE_NAME, LAST_NAME, STATUS, USER_TYPE FROM MEMBER WHERE USER_NAME = '" + UserName.Text + "' AND PASSWORD = '******'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
FormsAuthentication.Initialize();
if(sdr["USER_TYPE"].ToString().Trim() == "A")
{
strRole = "Admin";
}
else
{
strRole = "Member";
}
//The AddMinutes determines how long the user will be logged in after leaving
//the site if he doesn't log off.
//FormsAuthenticationTicket fat = new FormsAuthenticationTicket(1, UserName.Text, DateTime.Now,
// DateTime.Now.AddMinutes(30), false, strRole, FormsAuthentication.FormsCookiePath);
//Response.Cookies.Add(new HttpCookie(FormsAuthentication.FormsCookieName,
// FormsAuthentication.Encrypt(fat)));
Session["MEMBER_ID"] = sdr["MEMBER_ID"].ToString().Trim();
Session["USER_NAME"] = sdr["USER_NAME"].ToString().Trim();
Session["PASSWORD"] = sdr["PASSWORD"].ToString().Trim();
Session["NAME"] = sdr["FIRST_NAME"].ToString().Trim() + " " + sdr["MIDDLE_NAME"].ToString().Trim() + " " + sdr["LAST_NAME"].ToString().Trim();
Session["STATUS"] = sdr["STATUS"].ToString().Trim();
Session["USER_TYPE"] = sdr["USER_TYPE"].ToString().Trim();
//Response.Redirect(FormsAuthentication.GetRedirectUrl("Home.aspx", false));
Response.Redirect("Home.aspx");
}
else
{
lblErrorMsg.Visible = true;
lblErrorMsg.Text = "Invalid User Name/Password.";
}
sdr.Close();
}
catch(Exception err)
{
lblErrorMsg.Visible = true;
lblErrorMsg.Text = err.Message;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
Profile pro = new Profile();
try
{
if (lstid.SelectedItem.Text == "")
{
lblEMsg.Text = "Please select Author ID";
lblEMsg.Visible = true;
}
else
{
bool status;
status = pro.DeleteAuthor(lstid.SelectedItem.Text);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Author deletion Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Author deleted.";
lstid.Items.Clear();
//Load all the Category First.
string sql = "SELECT Author_ID FROM Author Where Status ='A'";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
while (sdr.Read())
{
lstid.Items.Add(sdr["Author_ID"].ToString().Trim());
}
sdr.Close();
txtAName.Text = "";
txtAdd.Text = "";
txtPhone.Text = "";
txtFax.Text = "";
txtEmail.Text = "";
chkStatus.Checked = false;
return;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
Profile pro = new Profile();
try
{
if (lstid.Text == "")
{
lblEMsg.Text = "Please select Category ID";
lblEMsg.Visible = true;
}
else
{
bool status;
status = pro.DeleteCategory(lstid.SelectedItem.ToString());
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Category deletion Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Category deleted.";
lstid.Items.Clear();
//Load all the Category First.
string sql = "SELECT Category_ID FROM CATEGORY Where Status = 'A'";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
while (sdr.Read())
{
lstid.Items.Add(sdr["Category_ID"].ToString().Trim());
}
sdr.Close();
txtCName.Text = "";
txtcDesc.Text = "";
return;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["USER_TYPE"] == null)
{
Response.Redirect("Logout.aspx");
}
if (Page.IsPostBack == false)
{
if (Session["USER_TYPE"].ToString() == "A")
{
Panel2.Visible = false;
}
else
{
Panel3.Visible = false;
}
try
{
string sql = "SELECT Distinct Book_Title FROM Book_Master Where Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
lstBook.Items.Clear();
lstBook.Items.Add("");
while (sdr.Read())
{
lstBook.Items.Add(sdr["Book_Title"].ToString().Trim());
}
sdr.Close();
sql = "SELECT DISTINCT BM.BOOK_ID AS 'Book ID', BM.BOOK_TITLE AS 'Book Title', A.AUTHOR_NAME AS 'Author Name', P.PUBLISHER_NAME AS 'Pulisher Name', ";
sql = sql + "P.PUBLISHING_YEAR AS 'Publishing Year', P.PUBLISHING_HOUSE AS 'Publishing House', P.PUBLISHER_EDITION AS 'Publisher Edition', C.CATEGORY_NAME AS 'Category Name', ";
sql = sql + "S.SUPPLIER_NAME AS 'Supplier Name', BM.ISBN, BM.PRICE FROM BOOK_MASTER BM INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID ";
sql = sql + "INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID INNER JOIN CATEGORY C ON BM.CATEGORY_ID = C.CATEGORY_ID ";
sql = sql + "INNER JOIN SUPPLIER S ON BM.SUPPLIER_ID = S.SUPPLIER_ID WHERE BM.BOOK_TITLE IS NULL ORDER BY BM.BOOK_ID";
ReadBook(sql);
}
catch (Exception ex)
{
throw ex;
}
}
}
protected void ChangePasswordPushButton_Click(object sender, EventArgs e)
{
try
{
string sql = "SELECT USER_NAME, PASSWORD FROM MEMBER WHERE USER_NAME = '" + txtUserName.Text + "' AND PASSWORD = '******'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
//Update New Password.
Session["PASSWORD"] = txtNPassword.Text;
bool status;
status = pro.UpdatePassword(txtUserName.Text,txtNPassword.Text);
if (status == false)
{
lblMsg.Visible = true;
lblMsg.Text = "Password updation Failed";
return;
}
lblMsg.Visible = true;
lblMsg.Text = "Password updated.";
Response.Redirect("Home.aspx");
}
else
{
lblMsg.Visible = true;
lblMsg.Text = "Invalid Password.";
return;
}
sdr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
protected void lstid_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
status = true;
if (lstid.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Category";
return;
}
string sql = "SELECT Category_Name, Category_Description, Status FROM CATEGORY WHERE Category_ID = '" + lstid.SelectedItem.ToString() + "'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtCName.Text = sdr["Category_Name"].ToString().Trim();
txtcDesc.Text = sdr["Category_Description"].ToString().Trim();
chkStatus.Checked = true;
}
sdr.Close();
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Auth;
Profile pro = new Profile();
try
{
if (lstid.SelectedItem.Text == "")
{
lblEMsg.Text = "Please select Author ID";
lblEMsg.Visible = true;
}
else
{
Auth = new string[15];
Auth[0] = lstid.Text;
Auth[1] = txtAName.Text;
Auth[2] = txtAdd.Text;
Auth[3] = txtPhone.Text;
Auth[4] = txtFax.Text;
Auth[5] = txtEmail.Text;
if (chkStatus.Checked == true)
{
Auth[6] = "A";
}
else
{
Auth[6] = "I";
}
bool status;
status = pro.UpdateAuthor(Auth);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Author updation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Author updated.";
//Load all the Category First.
string sql = "SELECT Author_ID FROM Author";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
lstid.Items.Clear();
while (sdr.Read())
{
lstid.Items.Add(sdr["Author_ID"].ToString().Trim());
}
sdr.Close();
txtAName.Text = "";
txtAdd.Text = "";
txtPhone.Text = "";
txtFax.Text = "";
txtEmail.Text = "";
chkStatus.Checked = false;
return;
}
}
catch (Exception ex)
{
throw ex;
}
}
private void ReadCategory()
{
try
{
string sql = "SELECT COUNT(*) FROM CATEGORY";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int Cate = (int)sdr[0] + 1;
if (Cate < 10)
{
txtCategory.Text = string.Format("C000{0}", Cate);
}
else if (Cate > 10 && Cate < 100)
{
txtCategory.Text = string.Format("C00{0}", Cate);
}
else
{
txtCategory.Text = string.Format("C0{0}", Cate);
}
sdr.Close();
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Supp;
try
{
string sql = "SELECT COUNT(*) FROM SUPPLIER WHERE SUPPLIER_NAME = '" + txtSName.Text + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int RegNo = (int)sdr[0];
if (RegNo > 0)
{
lblEMsg.Text = "Supplier Name already exists.";
lblEMsg.Visible = true;
sdr.Close();
return;
}
else
{
Supp = new string[15];
Supp[0] = txtSupplier.Text;
Supp[1] = txtSName.Text;
Supp[2] = txtAdd.Text;
Supp[3] = txtPhone.Text;
Supp[4] = txtFax.Text;
Supp[5] = txtemail.Text;
if (chkStatus.Checked == true)
{
Supp[6] = "A";
}
else
{
Supp[7] = "I";
}
bool status;
status = pro.AddSupplier(Supp);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Supplier creation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Supplier added.";
ReadSupplier();
txtSName.Text = "";
txtAdd.Text = "";
txtPhone.Text = "";
txtFax.Text = "";
txtemail.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
private void ReadBook()
{
try
{
string sql = "SELECT Distinct Book_ID FROM Book_Master Where Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
lstbook.Items.Clear();
lstbook.Items.Add("");
while (sdr.Read())
{
lstbook.Items.Add(sdr["Book_ID"].ToString().Trim());
}
sdr.Close();
//Retrieve Author Details
sql = "SELECT Distinct Author_Name FROM Author Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstAuthor.Items.Clear();
LstAuthor.Items.Add("");
while (sdr.Read())
{
LstAuthor.Items.Add(sdr["Author_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Publisher Details
sql = "SELECT Distinct Publisher_Name FROM Publisher Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstPublisher.Items.Clear();
LstPublisher.Items.Add("");
while (sdr.Read())
{
LstPublisher.Items.Add(sdr["Publisher_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Category Details
sql = "SELECT Distinct Category_Name FROM Category Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstCategory.Items.Clear();
LstCategory.Items.Add("");
while (sdr.Read())
{
LstCategory.Items.Add(sdr["Category_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Supplier Details
sql = "SELECT Distinct Supplier_Name FROM Supplier Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstSupplier.Items.Clear();
LstSupplier.Items.Add("");
while (sdr.Read())
{
LstSupplier.Items.Add(sdr["Supplier_Name"].ToString().Trim());
}
sdr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["USER_TYPE"] == null)
{
Response.Redirect("Logout.aspx");
}
if (this.IsPostBack == false)
{
txtReg.Text = (string)Session["MEMBER_ID"];
UserName.Text = (string)Session["USER_NAME"];
Password.Text = (string)Session["PASSWORD"];
ConfirmPassword.Text = (string)Session["PASSWORD"];
try
{
string sql = "SELECT USER_NAME, PASSWORD, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, E_MAIL_ID, LAND_LINE, MOBILE, SEX, SECURITY_Q, SECURITY_A FROM MEMBER WHERE MEMBER_ID = '" + txtReg.Text + "'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
UserName.Text = sdr["USER_NAME"].ToString().Trim();
Password.Text = sdr["PASSWORD"].ToString().Trim();
ConfirmPassword.Text = sdr["PASSWORD"].ToString().Trim();
txtFName.Text = sdr["FIRST_NAME"].ToString().Trim();
txtMName.Text = sdr["MIDDLE_NAME"].ToString().Trim();
txtLName.Text = sdr["LAST_NAME"].ToString().Trim();
txtAddress.Text = sdr["ADDRESS"].ToString().Trim();
Email.Text = sdr["E_MAIL_ID"].ToString().Trim();
txtLline.Text = sdr["LAND_LINE"].ToString().Trim();
txtMobile.Text = sdr["MOBILE"].ToString().Trim();
if (sdr["SEX"].ToString().Trim() == "M")
{
optMale.Checked = true;
}
else
{
optFemale.Checked = true;
}
Question.Text = sdr["SECURITY_Q"].ToString().Trim();
Answer.Text = sdr["SECURITY_A"].ToString().Trim();
}
sdr.Close();
}
catch (Exception err)
{
throw err;
}
}
if (Session["USER_TYPE"].ToString() == "A")
{
Panel2.Visible = false;
}
else
{
Panel3.Visible = false;
}
}
protected void btnIssue_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] iBook;
try
{
SqlDataReader sdr;
Profile pro = new Profile();
int aQty = Convert.ToInt32(txtQuantity.Text.ToString());
if (aQty == 0)
{
lblEMsg.Visible = true;
lblEMsg.Text = "This book is Out of stock, please select a different book.";
return;
}
string sql = "SELECT Count(1) FROM Book_Transaction WHERE Book_ID = '" + LstBook.SelectedItem.Text + "' And Member_ID = '" + DropDownList1.SelectedItem.Text + "' And Actual_Return_Date Is Null";
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int iB = (int)sdr[0];
if (iB > 0)
{
lblEMsg.Visible = true;
lblEMsg.Text = "This book is already issued, please select a different book.";
return;
}
sdr.Close();
//Insert the data.
iBook = new string[4];
iBook[0] = LstBook.SelectedItem.Text;
iBook[1] = DropDownList1.SelectedItem.Text;
iBook[2] = txtIDate.Text;
iBook[3] = txtRDate.Text;
bool status;
status = pro.IssueBook(iBook);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Book Issuing Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Book Issued.";
DropDownList1.Text = "";
LstBook.Text = "";
txtBTitle.Text = "";
txtAuthor.Text = "";
txtPub.Text = "";
txtCat.Text = "";
txtSup.Text = "";
txtQuantity.Text = "";
txtPrice.Text = "";
txtRDate.Text = "";
Label9.Text = "";
return;
}
catch (Exception ex)
{
lblEMsg.Visible = true;
lblEMsg.Text = ex.Message;
}
}
private void ReadBook()
{
try
{
SqlDataReader sdr;
Profile pro = new Profile();
string sql = "SELECT Distinct Member_ID FROM Member where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
DropDownList1.Items.Add("");
while (sdr.Read())
{
DropDownList1.Items.Add(sdr["Member_ID"].ToString().Trim());
}
sdr.Close();
sql = "SELECT Distinct Book_ID FROM Book_Master where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstBook.Items.Add("");
while (sdr.Read() )
{
LstBook.Items.Add(sdr["Book_ID"].ToString().Trim());
}
sdr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
protected void LstBook_SelectedIndexChanged(object sender, EventArgs e)
{
lblEMsg.Visible = false;
/*if (status == false)
{
btnGo_Click(sender, e);
status = true;
}*/
//if (Page.IsPostBack == false)
//{
try
{
status = true;
string Author, Publisher, Category, Supplier;
int AQuantity = 0;
if (LstBook.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
string sql = "SELECT Book_Title, Author_ID, Publisher_ID, Category_ID, Supplier_ID, Quantity, Price FROM Book_Master WHERE Book_ID = '" + LstBook.SelectedItem.ToString() + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtBTitle.Text = sdr["Book_Title"].ToString().Trim();
Author = sdr["Author_ID"].ToString().Trim();
Publisher = sdr["Publisher_ID"].ToString().Trim();
Category = sdr["Category_ID"].ToString().Trim();
Supplier = sdr["Supplier_ID"].ToString().Trim();
AQuantity = Convert.ToInt32(sdr["Quantity"]);
txtPrice.Text = sdr["Price"].ToString().Trim();
//Get Author Name.
sql = "Select Distinct Author_Name From Author Where Author_ID = '" + Author + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtAuthor.Text = sdr["Author_Name"].ToString().Trim();
}
//sdr.Close;
//Get Publisher_Name
sql = "Select Distinct Publisher_Name From Publisher Where Publisher_ID = '" + Publisher + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtPub.Text = sdr["Publisher_Name"].ToString().Trim();
}
//sdr.Close;
//Get Category Name
sql = "Select Distinct Category_Name From Category Where Category_ID = '" + Category + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtCat.Text = sdr["Category_Name"].ToString().Trim();
}
//sdr.Close;
//Get Supplier Name
sql = "Select Distinct Supplier_Name From Supplier Where Supplier_ID = '" + Supplier + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtSup.Text = sdr["Supplier_Name"].ToString().Trim();
}
//sdr.Close;
sql = "Select Count(1) From Book_Transaction Where Book_ID = '" + LstBook.SelectedItem.Text + "' And Actual_Return_Date Is Null";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
AQuantity -= (int)sdr[0];
}
}
sdr.Close();
txtQuantity.Text = Convert.ToString(AQuantity);
return;
}
catch (NullReferenceException nex)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
catch (Exception ex)
{
throw ex;
}
// }
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
lblEMsg.Visible = false;
Label9.Visible = false;
try
{
if (DropDownList1.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Member";
return;
}
string sql = "SELECT First_Name, Middle_Name, Last_Name FROM Member WHERE Member_ID = '" + DropDownList1.SelectedItem.ToString() + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
Label9.Visible = true;
Label9.Text = sdr["First_Name"].ToString().Trim() + " " + sdr["Middle_Name"].ToString().Trim() + " " + sdr["Last_Name"].ToString().Trim();
}
sdr.Close();
return;
}
catch (NullReferenceException nex)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Member";
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void lstMember_SelectedIndexChanged(object sender, EventArgs e)
{
lblEMsg.Visible = false;
lblName.Visible = false;
try
{
if (lstMember.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Member";
return;
}
string sql = "SELECT First_Name, Middle_Name, Last_Name FROM Member WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
lblName.Visible = true;
lblName.Text = sdr["First_Name"].ToString().Trim() + " " + sdr["Middle_Name"].ToString().Trim() + " " + sdr["Last_Name"].ToString().Trim();
}
sdr.Close();
//Check if any book is issued for this member.
sql = "SELECT Count(1) FROM Book_Transaction WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Actual_Return_Date is Null";
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int iB = (int)sdr[0];
if (iB == 0)
{
lblEMsg.Visible = true;
lblEMsg.Text = "No Book is issued for this member, please select the correct member";
lstMember.Text = "";
lblName.Text = "";
return;
}
sdr.Close();
return;
}
catch (NullReferenceException nex)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Member";
return;
}
catch (Exception ex)
{
throw ex;
}
}
private void ReadSupplier()
{
try
{
string sql = "SELECT COUNT(*) FROM SUPPLIER";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int Supp = (int)sdr[0] + 1;
if (Supp < 10)
{
txtSupplier.Text = string.Format("S000{0}", Supp);
}
else if (Supp > 10 && Supp < 100)
{
txtSupplier.Text = string.Format("S00{0}", Supp);
}
else
{
txtSupplier.Text = string.Format("S0{0}", Supp);
}
sdr.Close();
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["USER_TYPE"] == null)
{
Response.Redirect("Logout.aspx");
}
if (Page.IsPostBack == false & status == false)
{
try
{
if (Session["USER_TYPE"].ToString() == "A")
{
Panel2.Visible = false;
}
else
{
Panel3.Visible = false;
}
string sql = "SELECT Supplier_ID FROM Supplier WHERE STATUS = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
while (sdr.Read())
{
lstid.Items.Add(sdr["Supplier_ID"].ToString().Trim());
}
sdr.Close();
return;
}
catch (Exception ex)
{
throw ex;
}
}
}
protected void lstbook_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
status = true;
string Author, Publisher, Category, Supplier;
if (lstbook.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
string sql = "SELECT Book_Title, Author_ID, Publisher_ID, Category_ID, Supplier_ID, Key_Word, ISBN, Quantity, Price, Status FROM Book_Master WHERE Book_ID = '" + lstbook.SelectedItem.ToString() + "'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtBTitle.Text = sdr["Book_Title"].ToString().Trim();
Author = sdr["Author_ID"].ToString().Trim();
Publisher = sdr["Publisher_ID"].ToString().Trim();
Category = sdr["Category_ID"].ToString().Trim();
Supplier = sdr["Supplier_ID"].ToString().Trim();
txtKey.Text = sdr["Key_Word"].ToString().Trim();
txtIsbn.Text = sdr["ISBN"].ToString().Trim();
txtQuantity.Text = sdr["Quantity"].ToString().Trim();
txtPrice.Text = sdr["Price"].ToString().Trim();
chkStatus.Checked = true;
//Get Author Name.
sql = "Select Distinct Author_Name From Author Where Author_ID = '" + Author + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
LstAuthor.Text = sdr["Author_Name"].ToString().Trim();
}
//sdr.Close;
//Get Publisher_Name
sql = "Select Distinct Publisher_Name From Publisher Where Publisher_ID = '" + Publisher + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
LstPublisher.Text = sdr["Publisher_Name"].ToString().Trim();
}
//sdr.Close;
//Get Category Name
sql = "Select Distinct Category_Name From Category Where Category_ID = '" + Category + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
LstCategory.Text = sdr["Category_Name"].ToString().Trim();
}
//sdr.Close;
//Get Supplier Name
sql = "Select Distinct Supplier_Name From Supplier Where Supplier_ID = '" + Supplier + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
LstSupplier.Text = sdr["Supplier_Name"].ToString().Trim();
}
//sdr.Close;
}
sdr.Close();
return;
}
catch (NullReferenceException nex)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void LstBook_SelectedIndexChanged(object sender, EventArgs e)
{
lblEMsg.Visible = false;
try
{
string Author, Publisher, Category, Supplier;
if (LstBook.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
string sql = "SELECT Book_Title, Author_ID, Publisher_ID, Category_ID, Supplier_ID FROM Book_Master WHERE Book_ID = '" + LstBook.SelectedItem.ToString() + "' And Status = 'A'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtBTitle.Text = sdr["Book_Title"].ToString().Trim();
Author = sdr["Author_ID"].ToString().Trim();
Publisher = sdr["Publisher_ID"].ToString().Trim();
Category = sdr["Category_ID"].ToString().Trim();
Supplier = sdr["Supplier_ID"].ToString().Trim();
//Get Author Name.
sql = "Select Distinct Author_Name From Author Where Author_ID = '" + Author + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtAuthor.Text = sdr["Author_Name"].ToString().Trim();
}
//sdr.Close;
//Get Publisher_Name
sql = "Select Distinct Publisher_Name From Publisher Where Publisher_ID = '" + Publisher + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtPub.Text = sdr["Publisher_Name"].ToString().Trim();
}
//sdr.Close;
//Get Category Name
sql = "Select Distinct Category_Name From Category Where Category_ID = '" + Category + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtCat.Text = sdr["Category_Name"].ToString().Trim();
}
//Get Supplier Name
sql = "Select Distinct Supplier_Name From Supplier Where Supplier_ID = '" + Supplier + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtSup.Text = sdr["Supplier_Name"].ToString().Trim();
}
}
sdr.Close();
//Get Issue Date & Return Date.
DateTime rDate, arDate;
sql = "SELECT Issue_Date, Return_Date FROM Book_Transaction WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Book_ID = '" + LstBook.SelectedItem.Text + "' And Actual_Return_Date is Null";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtIDate.Text = sdr["Issue_Date"].ToString().Trim();
txtRDate.Text = sdr["Return_Date"].ToString().Trim();
rDate = Convert.ToDateTime(sdr["Return_Date"].ToString().Trim());
arDate = Convert.ToDateTime(txtARDate.Text);
if (rDate < arDate)
{
TimeSpan dDiff;
dDiff = arDate.Subtract(rDate);
txtLFee.Text = Convert.ToString(Convert.ToInt32(dDiff.Days) * 10);
}
else
{
txtLFee.Text = "0";
}
}
sdr.Close();
//Check if any book is issued for this member.
sql = "SELECT Count(1) FROM Book_Transaction WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Book_ID = '" + LstBook.SelectedItem.Text + "' And Actual_Return_Date is Null";
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int iB = (int)sdr[0];
if (iB == 0)
{
lblEMsg.Visible = true;
lblEMsg.Text = "No Book is issued for this member, please select the correct member";
return;
}
sdr.Close();
return;
}
catch (NullReferenceException nex)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Book";
return;
}
catch (Exception ex)
{
throw ex;
}
}
private string ReturnData(string sql)
{
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
return sdr[0].ToString();
}
else
{
return "";
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Pub;
Profile pro = new Profile();
try
{
if (lstid.SelectedItem.Text == "")
{
lblEMsg.Text = "Please select Publisher ID";
lblEMsg.Visible = true;
}
else
{
Pub = new string[15];
Pub[0] = lstid.Text;
Pub[1] = txtPName.Text;
Pub[2] = txtPYear.Text;
Pub[3] = txtPHouse.Text;
Pub[4] = txtPEdition.Text;
Pub[5] = txtAdd.Text;
Pub[6] = txtPhone.Text;
Pub[7] = txtFax.Text;
Pub[8] = txtemail.Text;
if (chkStatus.Checked == true)
{
Pub[9] = "A";
}
else
{
Pub[9] = "D";
}
bool status;
status = pro.UpdatePublisher(Pub);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Publisher updation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Publisher updated.";
//Load all the Category First.
string sql = "SELECT Publisher_ID FROM Publisher";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
lstid.Items.Clear();
while (sdr.Read())
{
lstid.Items.Add(sdr["Publisher_ID"].ToString().Trim());
}
sdr.Close();
return;
//txtCName.Text = "";
//txtcDesc.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void lstid_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
status = true;
if (lstid.SelectedItem.ToString() == "")
{
lblEMsg.Visible = true;
lblEMsg.Text = "Please select the Supplier";
return;
}
string sql = "SELECT Supplier_Name, Address, Phone, Fax, EMail, Status FROM Supplier WHERE Supplier_ID = '" + lstid.SelectedItem.ToString() + "'";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
txtSName.Text = sdr["Supplier_Name"].ToString().Trim();
txtAdd.Text = sdr["Address"].ToString().Trim();
txtPhone.Text = sdr["Phone"].ToString().Trim();
txtFax.Text = sdr["Fax"].ToString().Trim();
txtEmail.Text = sdr["EMail"].ToString().Trim();
chkStatus.Checked = true;
}
sdr.Close();
return;
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Book;
string Author_ID, Publisher_ID, Category_ID, Supplier_ID;
try
{
string sql;
SqlDataReader sdr;
Profile pro = new Profile();
//Retrieve Author ID.
sql = "SELECT Author_ID FROM Author WHERE Author_Name = '" + LstAuthor.SelectedItem.Text + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
Author_ID = sdr["Author_ID"].ToString().Trim();
}
else
{
Author_ID = "";
}
sdr.Close();
//Retrieve Publisher ID.
sql = "SELECT Publisher_ID FROM Publisher WHERE Publisher_Name = '" + LstPublisher.SelectedItem.Text + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
Publisher_ID = sdr["Publisher_ID"].ToString().Trim();
}
else
{
Publisher_ID = "";
}
sdr.Close();
//Retrieve Category ID.
sql = "SELECT Category_ID FROM Category WHERE Category_Name = '" + LstCategory.SelectedItem.Text + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
Category_ID = sdr["Category_ID"].ToString().Trim();
}
else
{
Category_ID = "";
}
sdr.Close();
//Retrieve Supplier ID.
sql = "SELECT Supplier_ID FROM Supplier WHERE Supplier_Name = '" + LstSupplier.SelectedItem.Text + "'";
sdr = pro.ReturnMDetails(sql);
if (sdr.Read() == true)
{
Supplier_ID = sdr["Supplier_ID"].ToString().Trim();
}
else
{
Supplier_ID= "";
}
sdr.Close();
sql = "SELECT COUNT(*) FROM Book_Master WHERE Book_Title = '" + txtBTitle.Text + "' And Author_ID ='" + Author_ID + "' And Publisher_ID = '" + Publisher_ID + "' And Status = 'A'";
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int RegNo = (int)sdr[0];
if (RegNo > 0)
{
lblEMsg.Text = "Book Name already exists.";
lblEMsg.Visible = true;
sdr.Close();
return;
}
else
{
Book = new string[15];
Book[0] = txtBook.Text;
Book[1] = txtBTitle.Text;
Book[2] = Author_ID;
Book[3] = Publisher_ID;
Book[4] = Category_ID;
Book[5] = Supplier_ID;
Book[6] = txtKey.Text;
Book[7] = txtIsbn.Text;
Book[8] = txtQuantity.Text;
Book[9] = txtPrice.Text;
if (chkStatus.Checked == true)
{
Book[10] = "A";
}
else
{
Book[10] = "I";
}
bool status;
status = pro.Books(Book,"AddBook");
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Book creation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Book added.";
ReadBook();
txtBTitle.Text = "";
LstAuthor.Text = "";
LstCategory.Text = "";
LstPublisher.Text = "";
LstSupplier.Text = "";
txtKey.Text = "";
txtIsbn.Text = "";
txtQuantity.Text = "";
txtPrice.Text = "";
chkStatus.Checked = false;
lblEMsg.Visible = false;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
lblEMsg.Visible = false;
string[] Supp;
Profile pro = new Profile();
try
{
if (lstid.SelectedItem.Text == "")
{
lblEMsg.Text = "Please select Supplier ID";
lblEMsg.Visible = true;
}
else
{
Supp = new string[15];
Supp[0] = lstid.Text;
Supp[1] = txtSName.Text;
Supp[2] = txtAdd.Text;
Supp[3] = txtPhone.Text;
Supp[4] = txtFax.Text;
Supp[5] = txtEmail.Text;
if (chkStatus.Checked == true)
{
Supp[6] = "A";
}
else
{
Supp[6] = "I";
}
bool status;
status = pro.UpdateSupplier(Supp);
if (status == false)
{
lblEMsg.Visible = true;
lblEMsg.Text = "Supplier updation Failed";
}
lblEMsg.Visible = true;
lblEMsg.Text = "Supplier updated.";
//Load all the Category First.
string sql = "SELECT Supplier_ID FROM Supplier";
SqlDataReader sdr;
sdr = pro.ReturnMDetails(sql);
lstid.Items.Clear();
while (sdr.Read())
{
lstid.Items.Add(sdr["Supplier_ID"].ToString().Trim());
}
sdr.Close();
return;
//txtCName.Text = "";
//txtcDesc.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
private void ReadBook()
{
try
{
string sql = "SELECT COUNT(*) FROM Book_Master";
SqlDataReader sdr;
Profile pro = new Profile();
sdr = pro.ReturnMDetails(sql);
sdr.Read();
int Book = (int)sdr[0] + 1;
if (Book < 10)
{
txtBook.Text = string.Format("B000{0}", Book);
}
else if (Book >= 10 && Book < 100)
{
txtBook.Text = string.Format("B00{0}", Book);
}
else
{
txtBook.Text = string.Format("B0{0}", Book);
}
sdr.Close();
//Retrieve Author Details
sql = "SELECT Distinct Author_Name FROM Author Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstAuthor.Items.Clear();
LstAuthor.Items.Add("");
while (sdr.Read())
{
LstAuthor.Items.Add(sdr["Author_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Publisher Details
sql = "SELECT Distinct Publisher_Name FROM Publisher Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstPublisher.Items.Clear();
LstPublisher.Items.Add("");
while (sdr.Read())
{
LstPublisher.Items.Add(sdr["Publisher_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Category Details
sql = "SELECT Distinct Category_Name FROM Category Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstCategory.Items.Clear();
LstCategory.Items.Add("");
while (sdr.Read())
{
LstCategory.Items.Add(sdr["Category_Name"].ToString().Trim());
}
sdr.Close();
//Retrieve Supplier Details
sql = "SELECT Distinct Supplier_Name FROM Supplier Where Status = 'A'";
sdr = pro.ReturnMDetails(sql);
LstSupplier.Items.Clear();
LstSupplier.Items.Add("");
while (sdr.Read())
{
LstSupplier.Items.Add(sdr["Supplier_Name"].ToString().Trim());
}
sdr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
