private void frmExtra_Load(object sender, EventArgs e)
{
//Calculate popularity and stars
Popul p = new Popul();
p.BookCalcAll();
p.MemCalcAll();
//Adding Top Members
{
//Declarations
string topMemID, topMPop;
//Count Members
string sqlCntMem = string.Format("SELECT COUNT(MemberID) as CntMem FROM Member");
OleDbCommand cmdCntMem = new OleDbCommand(sqlCntMem, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drCntMem = cmdCntMem.ExecuteReader(); drCntMem.Read();
int No = int.Parse(drCntMem["CntMem"].ToString());
//Write SQL for top members
string sqlTopMem = string.Format("SELECT MemberID, Stars FROM Member WHERE Stars NOT IN (0) ORDER BY Stars DESC");
OleDbCommand cmdTopMem = new OleDbCommand(sqlTopMem, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drTopMem = cmdTopMem.ExecuteReader(); drTopMem.Read();
int n = 5; int i = 0;
if (No < n)
{
n = No;
}
while (drTopMem.Read() && i < n)
{
topMemID = drTopMem["MemberID"].ToString(); topMPop = drTopMem["Stars"].ToString();
topMPop = Math.Round(decimal.Parse(topMPop.ToString()), 2).ToString();
lboxTopMem.Items.Add(string.Format("{0} [{1}]", topMemID, topMPop));
i++;
}
}
//Adding Top Titles
{
//Declarations
string topTitleID, topTPop;
//Count Titles
string sqlCntTitle = string.Format("SELECT COUNT(TitleID) as CntTitle FROM Title");
OleDbCommand cmdCntTitle = new OleDbCommand(sqlCntTitle, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drCntTitle = cmdCntTitle.ExecuteReader(); drCntTitle.Read();
int No = int.Parse(drCntTitle["CntTitle"].ToString());
//Write SQL for top Titles
string sqlTopTitle = string.Format("SELECT TitleID, BTitle, Popularity FROM Title WHERE Popularity ORDER BY Popularity DESC");
OleDbCommand cmdTopTitle = new OleDbCommand(sqlTopTitle, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drTopTitle = cmdTopTitle.ExecuteReader();
int n = 5; int i = 0;
if (No < n)
{
n = No;
}
while (drTopTitle.Read() && i < n)
{
topTitleID = drTopTitle["BTitle"].ToString(); topTPop = drTopTitle["Popularity"].ToString();
topTPop = Math.Round(decimal.Parse(topTPop.ToString()), 2).ToString();
lboxTitlePop.Items.Add(string.Format("[{0}] {1}", topTPop, topTitleID));
Title.Add(drTopTitle["TitleID"].ToString());
i++;
}
}
//Adding Top Authors
{
//Declarations
string topAuthor, topAPop;
//Count Authors
string sqlCntAuthor = "SELECT COUNT(Author) AS CntAuthor FROM (SELECT DISTINCT Author FROM Title)";
OleDbCommand cmdCntAuthor = new OleDbCommand(sqlCntAuthor, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drCntAuthor = cmdCntAuthor.ExecuteReader(); drCntAuthor.Read();
int No = int.Parse(drCntAuthor["CntAuthor"].ToString());
//Write SQL for top Titles
string sqlTopAuthor = string.Format("SELECT Author, SUM(Popularity) AS TotPop FROM Title GROUP BY Author ORDER BY SUM(Popularity) DESC");
OleDbCommand cmdTopAuthor = new OleDbCommand(sqlTopAuthor, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drTopAuthor = cmdTopAuthor.ExecuteReader();
int n = 5; int i = 0;
if (No < n)
{
n = No;
}
while (drTopAuthor.Read() && i < n)
{
topAuthor = drTopAuthor["Author"].ToString(); topAPop = drTopAuthor["TotPop"].ToString();
topAPop = Math.Round(decimal.Parse(topAPop.ToString()), 2).ToString();
if (topAPop != "0")
{
lboxAuthorPop.Items.Add(string.Format("[{0}] {1}", topAPop, topAuthor));
}
i++;
}
}
//Adding Top Genres
{
//Declarations
string TopGenre, topGPop;
//Count Genres
string sqlCntGenre = string.Format("SELECT COUNT(Genre) as CntGenre FROM (SELECT DISTINCT Genre FROM Title)");
OleDbCommand cmdCntGenre = new OleDbCommand(sqlCntGenre, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drCntGenre = cmdCntGenre.ExecuteReader(); drCntGenre.Read();
int No = int.Parse(drCntGenre["CntGenre"].ToString());
//Write SQL for top Titles
string sqlTopGenre = string.Format("SELECT Genre, SUM(Popularity) AS TotPop FROM Title GROUP BY Genre ORDER BY SUM(Popularity) DESC");
OleDbCommand cmdTopGenre = new OleDbCommand(sqlTopGenre, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drTopGenre = cmdTopGenre.ExecuteReader();
int n = 5; int i = 0;
if (No < n)
{
n = No;
}
while (drTopGenre.Read() && i < n)
{
TopGenre = drTopGenre["Genre"].ToString(); topGPop = drTopGenre["TotPop"].ToString();
topGPop = Math.Round(decimal.Parse(topGPop.ToString()), 2).ToString();
if (topGPop != "0")
{
lboxGenrePop.Items.Add(string.Format("[{0}] {1}", topGPop, TopGenre));
}
i++;
}
}
//Fill Blacklist
{
//Write SQL for top Titles
string sqlBlack = "SELECT MStatus, MemberID FROM Member";
OleDbCommand cmdBlack = new OleDbCommand(sqlBlack, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drBlack = cmdBlack.ExecuteReader(); drBlack.Read();
while (drBlack.Read())
{
if (drBlack["MStatus"].ToString() == "Blocked")
{
lboxBlack.Items.Add(drBlack["MemberID"].ToString());
}
}
}
//Fill all common stats in method
FillAllStats();
if (db.con.State.Equals(ConnectionState.Open))
{
db.con.Close();
}
}
private void btnRenew_Click(object sender, EventArgs e)
{
//Remove single & double quotes which cause sql troubles
foreach (Control c in this.Controls)
{
if (c is TextBox || c is ComboBox || c is RichTextBox)
{
c.Text = c.Text.Replace("'", ""); c.Text = c.Text.Replace("\"", "");
}
}
// Check if any books borrowed
DialogResult two = MessageBox.Show("Are you sure you want to renew this membership?", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (two == DialogResult.No)
{
goto End;
}
if (lboxBookID.Items.Count != 0 || lblFine.Text != "0/-")
{
MessageBox.Show("The books borrowed by the member must be returned and all fines should be paid before attempting a membership renewal", "Return books and pay fines", MessageBoxButtons.OK, MessageBoxIcon.Error); goto End;
}
if (txtFName.Text == "" || txtLName.Text == "" || cboxType.Text == "" || txtAddr.Text == "" || txtNIC.Text == "" || txtWork.Text == "" || txtTP.Text == "" || txtDobDate.Text == "" || txtDobMon.Text == "" || txtDobYear.Text == "" || txtDobDate.Text == "Date" || txtDobMon.Text == "Month" || txtDobYear.Text == "Month")
{
MessageBox.Show("One or many of the required fields are left blank. Please fill them and try again", "Field(s) left blank", MessageBoxButtons.OK, MessageBoxIcon.Error); goto End;
}
if (txtNIC.Text.Length != 10)
{
MessageBox.Show("NIC number invalid", "Invalid NIC", MessageBoxButtons.OK, MessageBoxIcon.Error); goto End;
}
if (cboxType.Text == "Child Membership" && txtGuardian.Text == "")
{
MessageBox.Show("A guardian should be specified for a Child Member", "Specify Guardian", MessageBoxButtons.OK, MessageBoxIcon.Error); goto End;
}
bool isDate = DateTime.TryParse((txtDobMon.Text + "/" + txtDobDate.Text + "/" + txtDobYear.Text), out DoB);
if (!isDate)
{
MessageBox.Show("Date of Birth is invalid", "Invalid Date of Birth", MessageBoxButtons.OK, MessageBoxIcon.Error); goto End;
}
if (lblStatus.Text == "Valid")
{
DialogResult one = MessageBox.Show("The membership is not yet expired. Beginning of new membership will be calculated from the end of present membership. Do you want to continue?", "Membership still valid", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (one == DialogResult.No)
{
goto End;
}
}
DateR = DateTime.Now;
if (lblStatus.Text == "Blocked")
{
MessageBox.Show("This member is blocked and marked in Blacklist, maybe due to book theft or any other illegal activity. This membership cannot be renewed. \r\n\r\nPlease take nessasary actions.", "Member in Blacklist", MessageBoxButtons.OK, MessageBoxIcon.Warning); goto End;
}
// Disclaimers End
//Declarations
FName = txtFName.Text; LName = txtLName.Text; Guard = txtGuardian.Text; NIC = txtNIC.Text; Work = txtWork.Text; Addr = txtAddr.Text; TP = txtTP.Text; Email = txtEmail.Text; Status = "Valid";
Fine = 0; DoB = DateTime.Parse(txtDobMon.Text + "/" + txtDobDate.Text + "/" + txtDobYear.Text); Popul p = new Popul(); Stars = p.MemCalc(MemID, false);
if (cboxType.Text == "Child Member")
{
MType = "Child";
}
else if (cboxType.Text == "Adult Member")
{
MType = "Adult";
}
if (lblStatus.Text == "Valid")
{
DateR = DateJ.AddMonths(set.Expire[MTi]);
}
//Check age and type
DateTime now = DateTime.Today;
int age = now.Year - DoB.Year; if (DoB > now.AddYears(-age))
{
age--;
}
if (age >= set.AgeMin && MType == "Child")
{
DialogResult r = MessageBox.Show("This Child member is now old enough to be an Adult Member. Continue creating an adult membership?", "Adult Member", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (r == DialogResult.Yes)
{
MType = "Adult"; cboxType.Text = "Adult Member";
}
else
{
goto End;
}
}
if (MType == "Child")
{
MTi = 0;
}
else
{
MTi = 1;
}
Renewed = int.Parse(lblRenew.Text) + 1;
// Declaring MemID
{
string MaxID;
// Getting the MaxID from DB
string sqlMaxID = "SELECT MAX(MemberID) as MaxID FROM Member"; OleDbCommand cmdMaxID = new OleDbCommand(sqlMaxID, db.con);
if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drMaxID = cmdMaxID.ExecuteReader();
if (drMaxID.Read())
{
MaxID = drMaxID["MaxID"].ToString().Substring(1, 4);
}
else
{
goto End;
}
// Increment MaxID to PresentID
int iID = int.Parse(MaxID) + 1;
string sID = iID.ToString();
//Loop to add enough zeros for the 5-character format (M0001)
for (int i = sID.Length; i != 4; i++)
{
sID = "0" + sID;
}
NewMemID = "M" + sID;
}
// Declarations End
//Request paying
fee = set.NewC[MTi];
DialogResult receive = MessageBox.Show(string.Format("Rs. {0}/- should be received for renewal of a {1} membership. Receive the amount and continue.", fee, MType), "Receive Fees", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (receive == DialogResult.Cancel)
{
goto End;
}
// Adding Transaction details
string sqlTrans = string.Format("INSERT INTO Cash (TDate, Amount, TDetail, Event) VALUES('{0}', {1}, '{2}', 'RenewMem')", DateTime.Now.ToString(), fee, "MemberID = " + MemID);
OleDbCommand cmdTrans = new OleDbCommand(sqlTrans, db.con);
cmdTrans.ExecuteNonQuery();
// Transactions added.
// Add all to Relation 'Member' in DB.
string sqlNew = string.Format("INSERT INTO Member (MemberID, FName, LName, MType, MStatus, DateJoined, Email, DateofBirth, TP, Guardian, NIC, Address, MWork, Renewed, TimesBorrow, Stars) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', {13}, {14}, {15})", NewMemID, FName, LName, MType, Status, DateR.ToShortDateString(), Email, DoB.ToShortDateString(), TP, Guard, NIC, Addr, Work, Renewed, TimesBorrow, Stars);
OleDbCommand cmdNew = new OleDbCommand(sqlNew, db.con); cmdNew.ExecuteNonQuery();
//Delete Old Member
string sqlDel = string.Format("DELETE FROM Member WHERE MemberID = '{0}'", MemID);
OleDbCommand cmdDel = new OleDbCommand(sqlDel, db.con); cmdDel.ExecuteNonQuery();
MessageBox.Show(string.Format("Membership of Member({0}) has been successfully renewed as ID '{1}'", MemID, NewMemID), "Renewal Successful", MessageBoxButtons.OK, MessageBoxIcon.Information); // Message
// Reset Form
Reset();
End : if (db.con.State.Equals(ConnectionState.Open))
{
db.con.Close();
}
}
//Get Title Details of TitleID
public void TitleInfo(string TitleID, out bool IsValidID, out string Title, out string Author, out string Genre, out string Publish, out string Pg, out string ISBN, out string Ty, out string Price, out string Popula, out string Times, out int NoAvail, out string NoBooks)
{
//Temporary Declarations
Title = Author = Genre = Publish = Pg = ISBN = Ty = Price = Popula = Times = NoBooks = ""; NoAvail = 0;
//Get Details from Title Table
string sqlTitle = string.Format("SELECT * FROM Title WHERE TitleID = '{0}'", TitleID);
OleDbCommand cmdTitle = new OleDbCommand(sqlTitle, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drTitle = cmdTitle.ExecuteReader(); drTitle.Read();
//Disclaimer: If Invalid TitleID
if (drTitle.HasRows)
{
IsValidID = true;
}
else
{
IsValidID = false; goto End;
}
//Declarations of Title Information
Title = drTitle["BTitle"].ToString();
Author = drTitle["Author"].ToString();
Genre = drTitle["Genre"].ToString();
Publish = drTitle["Publisher"].ToString();
Pg = drTitle["Pg"].ToString();
ISBN = drTitle["ISBN"].ToString();
Ty = drTitle["BType"].ToString();
if (Ty == "ALend")
{
Ty = "Adult Lending";
}
else if (Ty == "CLend")
{
Ty = "Children Lending";
}
else
{
Ty = "Reference";
}
Price = drTitle["Price"].ToString();
Popul p = new Popul();
Popula = p.BookCalc(null, TitleID, false).ToString();
Times = drTitle["TimesBorrowed"].ToString();
//Get Associated Book Details
string sqlBookD = string.Format("SELECT COUNT(BookID) as BCount FROM Book WHERE TitleID = '{0}'", TitleID);
OleDbCommand cmdBookD = new OleDbCommand(sqlBookD, db.con);
OleDbDataReader drBookD = cmdBookD.ExecuteReader(); drBookD.Read();
NoBooks = drBookD["BCount"].ToString();
NoAvail = int.Parse(NoBooks);
//Get BooksIDs associated with Title
string sqlEachBookID = string.Format("SELECT BookID FROM Book WHERE TitleID = '{0}'", TitleID);
OleDbCommand cmdEachBookID = new OleDbCommand(sqlEachBookID, db.con);
OleDbDataReader drEachBookID = cmdEachBookID.ExecuteReader();
//For Each BookID
while (drEachBookID.Read())
{
string BookID = drEachBookID["BookID"].ToString();
//Check wheather each book has been lent.
string sqlLent = string.Format("SELECT BookID FROM LendStatus WHERE BookID = '{0}'", BookID);
OleDbCommand cmdLent = new OleDbCommand(sqlLent, db.con);
OleDbDataReader drLent = cmdLent.ExecuteReader(); drLent.Read();
//IF lent, decrement the no. of available books
if (drLent.HasRows)
{
NoAvail = NoAvail - 1;
}
}
End :;
}
private void btnGet_Click(object sender, EventArgs e)
{
//Remove single & double quotes which cause sql troubles
foreach (Control c in this.Controls)
{
if (c is TextBox || c is ComboBox || c is RichTextBox)
{
c.Text = c.Text.Replace("'", ""); c.Text = c.Text.Replace("\"", "");
}
}
//Declare MemID based on Input Type
if (cvar.InsertID && txtMemID.Text == "")
{
MemID = cvar.MemID; txtMemID.Text = MemID;
} // If input from outside.
else if (txtMemID.Text == "")
{
goto End; // If Form opened anew.
}
else
{
MemID = txtMemID.Text; // If Value entered in textbox.
}
//Access Database for Details
string sqlMemD = string.Format("SELECT * FROM Member WHERE MemberID = '{0}'", MemID);
OleDbCommand cmdMemD = new OleDbCommand(sqlMemD, db.con); if (db.con.State.Equals(ConnectionState.Closed))
{
db.con.Open();
}
OleDbDataReader drMemD = cmdMemD.ExecuteReader();
if (drMemD.Read())
{
txtMemID.Enabled = false; MakeWide(); //Make the form wider.
txtFName.Text = drMemD["FName"].ToString();
txtLName.Text = drMemD["LName"].ToString();
txtAddr.Text = drMemD["Address"].ToString();
txtEmail.Text = drMemD["Email"].ToString();
txtGuardian.Text = drMemD["Guardian"].ToString();
txtNIC.Text = drMemD["NIC"].ToString();
txtTP.Text = drMemD["TP"].ToString();
txtWork.Text = drMemD["MWork"].ToString();
lblStatus.Text = drMemD["MStatus"].ToString();
Fine = double.Parse(drMemD["Fine"].ToString());
lblFine.Text = Fine.ToString() + "/-";
lblRenew.Text = drMemD["Renewed"].ToString();
Popul p = new Popul();
TimesBorrow = int.Parse(drMemD["TimesBorrow"].ToString());
if (Fine == 0)
{
btnFine.Enabled = false;
}
else
{
btnFine.Enabled = true;
}
DateTime now = DateTime.Today;
DateTime Dob = DateTime.Parse(drMemD["DateOfBirth"].ToString());
int age = now.Year - Dob.Year; if (Dob > now.AddYears(-age))
{
age--;
}
lblAge.Text = age.ToString();
MType = drMemD["MType"].ToString();
if (MType == "Child")
{
cboxType.SelectedIndex = 0; lblLLg.Visible = txtGuardian.Visible = true; lblLLNic.Text = "Guardian's NIC";
}
else if (MType == "Adult")
{
cboxType.SelectedIndex = 1; lblLLg.Visible = txtGuardian.Visible = false; lblLLNic.Text = "NIC Number"; cboxType.Enabled = false;
}
if (lblStatus.Text == "Blocked")
{
btnBlack.Text = "Remove from Blacklist";
}
else
{
btnBlack.Text = "Add to Blacklist";
}
//Split & Use Date of Birth
string Fulldob = drMemD["DateOfBirth"].ToString();
string[] DoBSpace = Fulldob.Split(' '); // Split Date from Time
string[] DoBSlash = DoBSpace[0].Split('/'); // SpilitDate components
txtDobMon.Text = DoBSlash[0]; txtDobDate.Text = DoBSlash[1]; txtDobYear.Text = DoBSlash[2];
//Split & Use Date Joined
DateJ = DateTime.Parse(drMemD["DateJoined"].ToString());
FullDate = DateJ.ToString();
string[] DateSpace = FullDate.Split(' '); // Split Date from Time
string[] DateSlash = DateSpace[0].Split('/'); // SpilitDate components
lblDate.Text = DateSlash[1] + "-" + DateSlash[0] + "-" + DateSlash[2];
// To Retrive BookIDs
string sqlBookID = string.Format("SELECT BookID FROM LendStatus WHERE MemberID = '{0}'", MemID);
OleDbCommand cmdBookID = new OleDbCommand(sqlBookID, db.con); OleDbDataReader drBookID = cmdBookID.ExecuteReader();
while (drBookID.Read()) // For Each bookID
{
string BookID = drBookID["BookID"].ToString();
string sqlBook = string.Format("SELECT TitleID FROM Book WHERE BookID = '{0}'", BookID); //Get TitleID from BookID
OleDbCommand cmdBook = new OleDbCommand(sqlBook, db.con); OleDbDataReader drBook = cmdBook.ExecuteReader();
if (drBook.Read())
{
string TitleID = drBook["TitleID"].ToString();
string sqlTitle = string.Format("SELECT BTitle, Author, Genre FROM Title WHERE TitleID = '{0}'", TitleID); // Get Title info from TitleID
OleDbCommand cmdTitle = new OleDbCommand(sqlTitle, db.con); OleDbDataReader drTitle = cmdTitle.ExecuteReader();
if (drTitle.Read())
{
lboxBookID.Items.Add(BookID); lboxTitle.Items.Add(drTitle["BTitle"].ToString()); lboxAuthor.Items.Add(drTitle["Author"].ToString()); lboxGenre.Items.Add(drTitle["Genre"].ToString());
}
}
}
//To Get last Check In
string sqlCheckIn = string.Format("SELECT Max(CDate) as MaxCheckIn FROM CheckInOut WHERE MemberID = '{0}' AND EVENT = 'In' GROUP BY MemberID, Event", MemID);
OleDbCommand cmdCheckIn = new OleDbCommand(sqlCheckIn, db.con); OleDbDataReader drCheckIn = cmdCheckIn.ExecuteReader();
if (drCheckIn.Read() & drCheckIn.HasRows)
{
DateTime CheckIn = DateTime.Parse(drCheckIn["MaxCheckIn"].ToString()); lblCheckIn.Text = CheckIn.ToString("dd-MM-yyyy");
}
//Calculate Recent Star Points Via method & Display
Popul star = new Popul();
fullStars = star.MemCalc(MemID, false);
Stars = double.Parse(Math.Round(decimal.Parse(fullStars.ToString()), 2).ToString());
lblStars.Text = Stars.ToString();
btnGet.Enabled = false;
}
else
{
MessageBox.Show("MemberID does not match with any of the members. Try again", "Invalid MemberID", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
cvar.MemID = ""; cvar.InsertID = false;
if (db.con.State.Equals(ConnectionState.Open))
{
db.con.Close();
}
End : if (db.con.State.Equals(ConnectionState.Open))
{
db.con.Close();
}
}