public string activateMember(Member member)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
SqlCommand cmd = new SqlCommand(string.Format(
@"UPDATE MEMBER SET STATUS=@STATUS, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED WHERE ID_MEMBER=@ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", member.id);
cmd.Parameters.AddWithValue("@STATUS", member.status);
cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Member has been activated sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
protected void removeMember(object sender, EventArgs e)
{
if (this.datepicker.Text.Equals(""))
{
this.errorDiv.Visible = true;
this.errorMsg.Text = "Please select a date.";
}
else
{
Member member = new Member();
member.id = this.searchMembers.SelectedRow.Cells[6].Text;
member.status = "INACTIVE";
member.dateRemoved = this.datepicker.Text;
MemberDAO memberDAO = new MemberDAO();
this.removeMemberForm.Visible = false;
this.responseForm.Visible = true;
this.responseText.Text = memberDAO.removeMember(member);
}
}
protected void activateMember(object sender, EventArgs e)
{
if(this.datepicker.Text.Equals("")){
this.errorDiv.Visible = true;
this.errorMsg.Text = "Please select the date of activation!";
}
else
{
Member member = new Member();
MemberDAO memberDAO = new MemberDAO();
member.id = this.searchMembers.SelectedRow.Cells[6].Text;
member.dateLastActivated = this.datepicker.Text;
member.status = "ACTIVE";
this.mainForm.Visible = false;
this.response.Visible = true;
this.responseText.Text = memberDAO.activateMember(member);
}
}
public Member getMemberData(string idMember)
{
Member member = new Member();
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
//SELECT SQL, using the ID passed as an argument
SqlCommand cmd = new SqlCommand(string.Format(@"SELECT * FROM MEMBER WHERE ID_MEMBER=@ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", idMember);
try
{
sqlConn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
//Sets the member's parameters using the reader..
member.id = reader.GetValue(0).ToString();
member.idGroup = reader.GetValue(1).ToString();
member.accountNumber = reader.GetValue(2).ToString();
member.name = reader.GetValue(3).ToString();
member.dateJoined = reader.GetValue(4).ToString();
member.status = reader.GetValue(5).ToString();
member.doctor = reader.GetValue(6).ToString();
member.address = reader.GetValue(7).ToString();
member.city = reader.GetValue(8).ToString();
member.province = reader.GetValue(9).ToString();
member.region = reader.GetValue(10).ToString();
member.postalCode = reader.GetValue(11).ToString();
member.website = reader.GetValue(12).ToString();
member.emailAddress = reader.GetValue(13).ToString();
member.phoneNumber = reader.GetValue(14).ToString();
member.faxNumber = reader.GetValue(15).ToString();
member.contactPerson = reader.GetValue(16).ToString();
reader.Close();
}
catch (Exception)
{
//MessageBox.Show("Some error has occured. Sorry =(");
}
finally
{
sqlConn.Close();
}
return member;
}
public string updateMember(Member member)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
if (member.idGroup == null || member.idGroup.Equals("") || member.idGroup.Equals(" "))
{
SqlCommand cmd = new SqlCommand(string.Format(@"UPDATE MEMBER
SET ACCOUNT_NUMBER=@ACCOUNT_NUMBER, NAME=@NAME, DOCTOR=@DOCTOR,
DATE_JOINED=@DATE_JOINED, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED, ADDRESS=@ADDRESS, CITY=@CITY, PROVINCE=@PROVINCE, REGION=@REGION,
POSTAL_CODE=@POSTAL_CODE, WEBSITE=@WEBSITE, EMAIL_ADDRESS=@EMAIL_ADDRESS,
PHONE_NUMBER=@PHONE_NUMBER, FAX=@FAX, CONTACT_PERSON=@CONTACT_PERSON
WHERE ID_MEMBER=@ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", member.id);
cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber);
cmd.Parameters.AddWithValue("@NAME", member.name);
cmd.Parameters.AddWithValue("@DOCTOR", member.doctor);
cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined);
cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated);
cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city);
cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region);
cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website);
cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber);
cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Member has been updated sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
else
{
SqlCommand cmd = new SqlCommand(string.Format(@"UPDATE MEMBER
SET ID_GROUP=@ID_GROUP, ACCOUNT_NUMBER=@ACCOUNT_NUMBER, NAME=@NAME, DOCTOR=@DOCTOR,
DATE_JOINED=@DATE_JOINED, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED, ADDRESS=@ADDRESS, CITY=@CITY, PROVINCE=@PROVINCE, REGION=@REGION,
POSTAL_CODE=@POSTAL_CODE, WEBSITE=@WEBSITE, EMAIL_ADDRESS=@EMAIL_ADDRESS,
PHONE_NUMBER=@PHONE_NUMBER, FAX=@FAX, CONTACT_PERSON=@CONTACT_PERSON
WHERE ID_MEMBER=@ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", member.id);
cmd.Parameters.AddWithValue("@ID_GROUP", member.idGroup);
cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber);
cmd.Parameters.AddWithValue("@NAME", member.name);
cmd.Parameters.AddWithValue("@DOCTOR", member.doctor);
cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined);
cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated);
cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city);
cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region);
cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website);
cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber);
cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Member has been updated sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
}
public string removeMember(Member member)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
string inactive = "INACTIVE";
SqlCommand cmd = new SqlCommand(string.Format(
@"UPDATE MEMBER SET STATUS=@STATUS, DATE_REMOVED=@DATE_REMOVED WHERE ID_MEMBER=@ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", member.id);
cmd.Parameters.AddWithValue("@STATUS", inactive);
cmd.Parameters.AddWithValue("@DATE_REMOVED", member.dateRemoved);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Member has been removed sucessfully from the list of Active members.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
public string insertNewMember(Member member)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
SqlCommand cmd = new SqlCommand(string.Format(@"INSERT INTO MEMBER
(ACCOUNT_NUMBER, NAME, DATE_JOINED, STATUS, DOCTOR, ADDRESS, CITY, PROVINCE, REGION,
POSTAL_CODE, WEBSITE, EMAIL_ADDRESS, PHONE_NUMBER, FAX, CONTACT_PERSON, DATE_LAST_ACTIVATED, DATE_MODIFIED, MODIFIED_BY, DATE_CREATED) VALUES
(@ACCOUNT_NUMBER, @NAME, @DATE_JOINED, @STATUS, @DOCTOR, @ADDRESS, @CITY, @PROVINCE, @REGION,
@POSTAL_CODE, @WEBSITE, @EMAIL_ADDRESS, @PHONE_NUMBER, @FAX, @CONTACT_PERSON, @DATE_LAST_ACTIVATED, @DATE_MODIFIED, @MODIFIED_BY, @DATE_CREATED)"), sqlConn);
cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber);
cmd.Parameters.AddWithValue("@NAME", member.name); cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined);
cmd.Parameters.AddWithValue("@STATUS", "ACTIVE"); cmd.Parameters.AddWithValue("@DOCTOR", member.doctor);
cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city);
cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region);
cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website);
cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber);
cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson);
cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated);
cmd.Parameters.AddWithValue("@DATE_MODIFIED", member.dateModified);
cmd.Parameters.AddWithValue("@MODIFIED_BY", member.modifiedBy);
cmd.Parameters.AddWithValue("@DATE_CREATED", member.dateCreated);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Member has been added to the Database sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
protected void updateMember(object sender, EventArgs e)
{
this.enableFieldsBtt.Visible = false;
Member member = new Member();
string databaseResponse = "";
MemberDAO memberDAO = new MemberDAO();
if (this.searchMembers.SelectedIndex == -1)
{
this.errorMsg.Text = "Select one member to be updated.";
this.errorDiv.Visible = true;
}
else
{
if (accountNumber.Text.ToString() == "" || memberName.Text.ToString() == "" || this.datepicker.Text.Equals("") || address.Text.ToString() == "" ||
city.Text.ToString() == "" || province.SelectedValue.ToString() == "Select..." || postalCode.Text.ToString() == "")
{
this.errorMsg.Text = "One or more of the required fields are blank!";
this.errorDiv.Visible = true;
}
else
{
if (memberDAO.alreadyExists(this.accountNumber.Text))
{
this.errorMsg.Text = "This account number already exists. Please choose another one.";
}
else
{
//Loads the form content into the member object
member.id = this.searchMembers.SelectedRow.Cells[6].Text;
member.idGroup = this.searchMembers.SelectedRow.Cells[7].Text;
member.accountNumber = accountNumber.Text.ToString().Trim();
member.dateJoined = this.datepicker.Text;
member.doctor = doctorName.Text.ToString().Trim();
member.name = memberName.Text.ToString().Trim();
member.address = address.Text.ToString().Trim();
member.city = city.Text.ToString().Trim();
member.province = province.SelectedItem.ToString();
member.region = region.Text.ToString();
member.postalCode = postalCode.Text.ToString();
member.website = website.Text.ToString().Trim();
member.emailAddress = emailAddress.Text.ToString().Trim();
member.phoneNumber = phoneNumber.Text.ToString();
member.faxNumber = faxNumber.Text.ToString();
member.contactPerson = contactPerson.Text.ToString().Trim();
member.dateLastActivated = this.datepicker.Text;
//Sends the member object to the updateMember method in MemberDAO..
databaseResponse = memberDAO.updateMember(member);
clearAllFields();
this.responseText.Text = databaseResponse;
this.editMemberForm.Visible = false;
this.response.Visible = true;
}
}
}
}
