using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.OleDb;
using System.Data;
using System.Text;
using System.Threading;
using System.Globalization;
using System.Configuration;

public partial class SearchAdmin : System.Web.UI.Page
{
    private int ViewStateAccountID
    {
        get { return (int)ViewState["AccountID"]; }
        set { ViewState["AccountID"] = value; }
    }

   private DataSet ds = new DataSet();

   protected void Page_Load(object sender, EventArgs e)
   {
      if (Request.IsAuthenticated == false)
      {
         Response.Redirect("~/Account/Login.aspx");
      }

      if (!Page.IsPostBack)
      {
         LoadDefaultValuesFromDatabase();
         this.pnlReassignPrimary.Visible = false;
         this.btnRenew.Visible = false;
      }  
   }

   private void LoadDefaultValuesFromDatabase()
   {
      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sql = "SELECT curSingleRate, " +
                    "curCoupleRate, " +
                    "curFamilyRate, " +
                    "intBatchRenewalYear " +
                    "FROM BERPPlanAdministration";

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      oleDBConn.Open();

      OleDbDataReader reader = cmd.ExecuteReader();

      while (reader.Read())
      {
         Session["SingleRate"] = (decimal)reader[0];
         Session["CoupleRate"] = (decimal)reader[1];
         Session["FamilyRate"] = (decimal)reader[2];
         Session["RenewalYear"] = (Int16)reader[3];
      }

      reader.Close();

      oleDBConn.Close();
   }

   protected void btnSearch_Click(object sender, EventArgs e)
   {
      Page.Validate();

      if (Page.IsValid)
      {
         this.gvAccountSearch.SelectedIndex = -1;
         this.gvAccountSearch.PageIndex = 0;
      }

      this.pnlReassignPrimary.Visible = false;
      this.btnRenew.Visible = false;
      this.lblRenew.Text = string.Empty;
   }

   protected void btnClear_Click(object sender, EventArgs e)
   {
      txtBoxAccountID.Text = "";
      txtBoxLastName.Text = "";
      txtBoxFirstName.Text = "";
      txtBoxTelephone.Text = "";
      ddlJamatkhana.SelectedIndex = -1;
      this.gvAccountSearch.PageIndex = 0;

      if (Request.QueryString.Get("id") != null)
      {
         Response.Redirect("~/Admin/SearchAdmin.aspx"); ;
      }
      
      this.lblError.Visible = false;
      this.pnlReassignPrimary.Visible = false;
      this.btnRenew.Visible = false;
      this.lblRenew.Text = string.Empty;
   }

   protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
   {
      if (txtBoxAccountID.Text != string.Empty)
      {
         args.IsValid = true;
      }
      else if ((txtBoxLastName.Text != string.Empty) || (txtBoxFirstName.Text != string.Empty) || (ddlJamatkhana.SelectedIndex != 0))
      {
         args.IsValid = true;
      }
      else if (txtBoxTelephone.Text != string.Empty)
      {
         args.IsValid = true;
      }
      else
      {
         args.IsValid = false;
      }
   }

   protected void gvAccountSearch_PageIndexChanging(object sender, GridViewPageEventArgs e)
   {
      this.btnSearch_Click(sender, e);
      this.gvAccountSearch.PageIndex = e.NewPageIndex;
   }

   protected string FormatPostalCode(object objPostalCode, object objProvID)
   {
       //For all non-Canadian provinces, do not reformat the postalcodes.
       int iProvID = (int)objProvID;
       //Assume all Provincial IDs greater than 13 are non-Canadian
       if (iProvID > 13)
           return (string)objPostalCode;
      
      //Else if this is a Canadian postal code, then reformat with hyphen
      StringBuilder sb = new StringBuilder();

      try
      {
         string sPostalCode = (string)objPostalCode;
         sb.Append(sPostalCode[0]);
         sb.Append(sPostalCode[1]);
         sb.Append(sPostalCode[2]);
         sb.Append("-");
         sb.Append(sPostalCode[3]);
         sb.Append(sPostalCode[4]);
         sb.Append(sPostalCode[5]);
      }
      catch
      {
         sb.Append("");
      }

      return sb.ToString();
   }

   protected string FormatPhoneNumber(object objPhoneNumber)
   {
      StringBuilder sb = new StringBuilder();

      try
      {
         string sPhoneNumber = (string)objPhoneNumber;
         sb.Append("(");
         sb.Append(sPhoneNumber[0]);
         sb.Append(sPhoneNumber[1]);
         sb.Append(sPhoneNumber[2]);
         sb.Append(") ");
         sb.Append(sPhoneNumber[3]);
         sb.Append(sPhoneNumber[4]);
         sb.Append(sPhoneNumber[5]);
         sb.Append("-");
         sb.Append(sPhoneNumber[6]);
         sb.Append(sPhoneNumber[7]);
         sb.Append(sPhoneNumber[8]);
         sb.Append(sPhoneNumber[9]);
      }catch
      {
         sb.Append("");
      }

      return sb.ToString();
   }

   protected void dvAccountDetails_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
   {
      e.NewValues["AccountUpdateDate"] = DateTime.Now;
      e.NewValues["AccountUpdatedUser"] = User.Identity.Name;

      string strAddress = (string)e.NewValues["AccountStreetAddress"];

      e.NewValues["AccountStreetAddress"] = ProperCase.ToTitleCase(strAddress.Trim());


      string strPostalCode = (string)e.NewValues["AccountPostalCode"];

      e.NewValues["AccountPostalCode"] = strPostalCode.Trim().ToUpper();


      string strComments = (string)e.NewValues["AccountComments"];

      if ((strComments != null) && (strComments.Length != 0))
      {
          e.NewValues["AccountComments"] = ProperCase.ToTitleCase(strComments.Trim());
      }
   }

   protected void adsAccount_Updated(object sender, SqlDataSourceStatusEventArgs e)
   {
      lblError.Visible = true;

      if (e.AffectedRows > 0)
      {
         lblError.Text = "Account details have been successfully updated.";
      }
      else
      {
         lblError.Text = "ERROR: Failed to update the the Account details. Please try again.";
      }
   }

   protected void gvAccountSearch_SelectedIndexChanged(object sender, EventArgs e)
   {
      lblError.Visible = false;

      int selectedIndex = gvAccountSearch.SelectedIndex;

      // You can retrieve the key field from the SelectedDataKey property
      int accountID = (int)gvAccountSearch.SelectedDataKey.Values["AccountID"];

      ViewStateAccountID = accountID;

      int previousRenewalYear = GetPreviousRenewalYearForSelectedAccount(accountID);

      if (previousRenewalYear.ToString() == ConfigurationManager.AppSettings["RegistrationYear"])
      {
          btnRenew.Visible = false;
      }

      int count = GetFamilyCountForSelectedAccount(accountID);

      // Disable the Re-assignment of Primary if Single
      if (count == 1)
      {
         this.pnlReassignPrimary.Visible = false;
      }
      else
      {
         this.pnlReassignPrimary.Visible = true;
      }

      /*
      if (count == 1)
      {
         this.lblCategory.Text = "SINGLE";
         if (Session["SingleRate"] == null)
         {
            this.lblAmountDue.Text = "0";
         }
         else
         {
            this.lblAmountDue.Text = Convert.ToDecimal(Session["SingleRate"]).ToString(); ; //Convert.ToDecimal(Session["SingleRate"]).ToString("C");
         }

      }
      else if (count == 2)
      {
         this.lblCategory.Text = "COUPLE";
         if (Session["CoupleRate"] == null)
         {
            this.lblAmountDue.Text = "0";
         }
         else
         {
            this.lblAmountDue.Text = Convert.ToDecimal(Session["CoupleRate"]).ToString();
         }
      }
      else if (count > 2)
      {
         this.lblCategory.Text = "FAMILY";
         if (Session["FamilyRate"] == null)
         {
            this.lblAmountDue.Text = "0";
         }
         else
            this.lblAmountDue.Text = Convert.ToDecimal(Session["FamilyRate"]).ToString();
      }
      else
      {
         this.lblCategory.Text = "Unknown";
         if (Session["SingleRate"] == null)
         {
            this.lblAmountDue.Text = "0";
         }
         else
            this.lblAmountDue.Text = Convert.ToDecimal(Session["SingleRate"]).ToString();
      }

      this.lblRenewalYear.Text = Convert.ToInt16(Session["RenewalYear"]).ToString();
      this.lblPreviousRenewalYear.Text = previousRenewalYear.ToString();
      this.txtBoxAmountPaidByCash.Text = "0"; // Convert.ToDecimal(0).ToString("C");
      this.txtBoxAmountPaidByCheque.Text = "0"; // Convert.ToDecimal(0).ToString("C");
      this.txtBoxChequeNumber.Text = "";
       */

      this.lblError.Text = "";
   }

   private int GetFamilyCountForSelectedAccount(int accountID)
   {
      int familyCount = 0;

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sql = "SELECT COUNT(*) FROM Members WHERE AccountID = " + accountID.ToString() + " AND Members.MemberStatusID = 1";

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      oleDBConn.Open();

      familyCount = (int)cmd.ExecuteScalar();

      oleDBConn.Close();

      return familyCount;
   }

   private int GetPreviousRenewalYearForSelectedAccount(int accountID)
   {
      int renewalYear = 0;

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sql = "SELECT AccountRenewalYear FROM Accounts WHERE AccountID = " + accountID.ToString();

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      oleDBConn.Open();

      renewalYear = (short)cmd.ExecuteScalar();

      oleDBConn.Close();

      return renewalYear;
   }

   protected void btnAddToCart_Click(object sender, EventArgs e)
   {
      Page.Validate();

      if (Page.IsValid)
      {
         /*
         Account selectedAccount = new Account();

         selectedAccount.iAccountID = (int)gvAccountSearch.SelectedDataKey.Values["AccountID"];
         selectedAccount.dAmountDue = Convert.ToDecimal(this.lblAmountDue.Text);
         selectedAccount.dAmountPaidCash = Convert.ToDecimal(this.txtBoxAmountPaidByCash.Text);
         selectedAccount.dAmountPaidCheque = Convert.ToDecimal(this.txtBoxAmountPaidByCheque.Text);

         // You can retrieve other data directly from the Cells coll
         // as long as you know the column offset.
         if (gvAccountSearch.SelectedRow.Cells[2].Text != " ")
            selectedAccount.mLastName = gvAccountSearch.SelectedRow.Cells[2].Text;
         else
            gvAccountSearch.SelectedRow.Cells[2].Text = "";

         if (gvAccountSearch.SelectedRow.Cells[3].Text != " ")
            selectedAccount.mMiddleName = gvAccountSearch.SelectedRow.Cells[3].Text;
         else
            selectedAccount.mMiddleName = "";

         if (gvAccountSearch.SelectedRow.Cells[4].Text != " ")
            selectedAccount.mFirstName = gvAccountSearch.SelectedRow.Cells[4].Text;
         else
            selectedAccount.mFirstName = "";

         if (this.txtBoxChequeNumber.Text != string.Empty)
         {
            selectedAccount.iChequeNumber = Convert.ToInt32(this.txtBoxChequeNumber.Text);
         }

         CartItem cartitem = new CartItem();
         
         cartitem.cartAccount = selectedAccount;

         this.AddToCart(cartitem);
         */
      }
   }

   private void AddToCart(CartItem cartitem)
   {
      SortedList cart = this.GetCart();
      int iAccountID = cartitem.cartAccount.iAccountID;

      if (cart.ContainsKey(iAccountID))
      {
         // Account already exists in the batch
         this.lblError.Text = "Account ID " + iAccountID.ToString() + " was not added to the batch as it already exists.";
      }
      else
      {
         cart.Add(iAccountID, cartitem);
         this.lblError.Text = "Account ID " + iAccountID.ToString() + " was successfully added to the batch.";
      }
   }

   private SortedList GetCart()
   {
      if (Session["Cart"] == null)
      {
         Session.Add("Cart", new SortedList());
      }
      return (SortedList)Session["Cart"];
   }

   protected void adsMembers_Updated(object sender, SqlDataSourceStatusEventArgs e)
   {
      lblError.Visible = true;

      if (e.AffectedRows > 0)
      {
         lblError.Text = "Member details have been successfully updated.";
      }
      else
      {
         lblError.Text = "ERROR: Failed to update the the Member details. Please try again.";
      }
   }

   protected void gvAccountMembers_RowDataBound(object sender, GridViewRowEventArgs e)
   {
      if (e.Row.DataItem != null)
      {
         if ((e.Row.RowState == DataControlRowState.Edit) ||
              (e.Row.RowState == (DataControlRowState.Alternate | DataControlRowState.Edit)))
         {
            //DataRowView drv = (DataRowView)e.Row.DataItem;

            RangeValidator rv = (RangeValidator)e.Row.FindControl("RangeValidatorMemberBirthYear");

            StringBuilder sb = new StringBuilder();
            sb.Append("Birth Year ranges from ");
            int start = DateTime.Now.Year - 110;
            sb.Append(start.ToString());
            sb.Append(" to ");
            sb.Append(DateTime.Now.Year);
            rv.ErrorMessage = sb.ToString();
         }
      }
   }

   protected void gvAccountMembers_RowUpdating(object sender, GridViewUpdateEventArgs e)
   {

   }

   protected void dvMemberDetails_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
   {
      string strLast = (string)e.NewValues["MemberLastName"];

      e.NewValues["MemberLastName"] = ProperCase.ToTitleCase(strLast.Trim());

      string strMiddle = (string)e.NewValues["MemberMiddleName"];

      if ((strMiddle != null) && (strMiddle.Length != 0))
          e.NewValues["MemberMiddleName"] = ProperCase.ToTitleCase(strMiddle.Trim());

      string strFirst = (string)e.NewValues["MemberFirstName"];

      e.NewValues["MemberFirstName"] = ProperCase.ToTitleCase(strFirst.Trim());

      e.NewValues["MemberStatusChangeDate"] = DateTime.Now;
      e.NewValues["MemberStatusChangeBy"] = User.Identity.Name;

      string strComments = (string)e.NewValues["MemberStatusChangeComments"];

      if ((strComments != null) && (strComments.Length != 0))
      {
          e.NewValues["MemberStatusChangeComments"] = ProperCase.ToTitleCase(strComments.Trim());
      }
      else
      {
         e.NewValues["MemberStatusChangeComments"] = "";
      }
   }

   protected void adsMemberEdit_Updated(object sender, SqlDataSourceStatusEventArgs e)
   {
      if (e.AffectedRows > 0)
      {
         //this.gvAccountMembers.DataBind();

         lblError.Text = "Member details have been successfully updated.";
      }
      else
      {
         lblError.Text = "ERROR: Failed to update the the Member details. Please try again.";
      }

      lblError.Visible = true;
   }

   protected void gvAccountMembers_SelectedIndexChanged(object sender, EventArgs e)
   {
      this.lblError.Visible = false;
      this.dvMemberDetails.ChangeMode(DetailsViewMode.ReadOnly);
   }

   protected void dvMemberDetails_DataBound(object sender, EventArgs e)
   {
      //lblError.Visible = false;

      if (dvMemberDetails.CurrentMode == DetailsViewMode.Edit)
      {
         RangeValidator rv = (RangeValidator)dvMemberDetails.FindControl("RangeValidatorMemberEditBirthYear");

         StringBuilder sb = new StringBuilder();
         sb.Append("Birth Year ranges from ");
         int start = DateTime.Now.Year - 110;
         sb.Append(start.ToString());
         sb.Append(" to ");
         sb.Append(DateTime.Now.Year);
         rv.ErrorMessage = sb.ToString();


         int selectedIndex = gvAccountSearch.SelectedIndex;

         // You can retrieve the key field from the SelectedDataKey property
         int accountID = (int)gvAccountSearch.SelectedDataKey.Values["AccountID"];

         int count = GetFamilyCountForSelectedAccount(accountID);

         if (count > 1) // Only applicable for Couples and Family categories
         {
            if ((DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "True") ||
                 (DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "true") ||
                 (DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "1"))
            {
               TextBox txtBoxDeceasedDate = (TextBox)dvMemberDetails.FindControl("txtBoxMemberDeceasedDate");
               txtBoxDeceasedDate.Enabled = false;

               DropDownList ddlMemberStatus = (DropDownList)dvMemberDetails.FindControl("ddlMemberStatus");
               ddlMemberStatus.Enabled = false;

               DropDownList ddlMemberRelation = (DropDownList)dvMemberDetails.FindControl("ddlMemberEditRelationships");
               ddlMemberRelation.Enabled = false;
            }
            else
            {
               TextBox txtBoxDeceasedDate = (TextBox)dvMemberDetails.FindControl("txtBoxMemberDeceasedDate");
               txtBoxDeceasedDate.Enabled = false;

               DropDownList ddlMemberRelation = (DropDownList)dvMemberDetails.FindControl("ddlMemberEditRelationships");
               ddlMemberRelation.Items.RemoveAt(0); // Remove the Self option for the Primary relationship.
            }
         }
         else if (count == 1) // If Singles category, do not allow change of Relationship status
         {
            if ((DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "True") ||
                 (DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "true") ||
                 (DataBinder.Eval(dvMemberDetails.DataItem, "MemberPrimary").ToString() == "1"))
            {
               DropDownList ddlMemberRelation = (DropDownList)dvMemberDetails.FindControl("ddlMemberEditRelationships");
               ddlMemberRelation.Enabled = false;
            }
         }
      }

   }

   protected void ddlMemberStatus_SelectedIndexChanged(object sender, EventArgs e)
   {
      TextBox tbDeceasedDate = (TextBox)dvMemberDetails.FindControl("txtBoxMemberDeceasedDate");
      RequiredFieldValidator rfvDeceasedDate = (RequiredFieldValidator)dvMemberDetails.FindControl("RequiredFieldValidationMemberEditDeceasedDate");

      if (tbDeceasedDate != null)
      {

         if (((DropDownList)sender).SelectedItem.Text == "Deceased")
         {
            tbDeceasedDate.Enabled = true;
            rfvDeceasedDate.Enabled = true;
         }

         else
         {
            tbDeceasedDate.Enabled = false;
            rfvDeceasedDate.Enabled = false;
         }

      }

   }
   
   protected void dvMemberDetails_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e)
   {
      if (e.AffectedRows > 0)
      {
         this.gvAccountMembers.DataBind();

         lblError.Text = "Member details have been successfully updated.";
      }
      else
      {
         lblError.Text = "ERROR: Failed to update the the Member details. Please try again.";
      }

      lblError.Visible = true;
   }

   protected void dvMemberDetails_ItemInserting(object sender, DetailsViewInsertEventArgs e)
   {
      bool blnPrimary = false;

      int accountID = (int)gvAccountSearch.SelectedDataKey.Values["AccountID"];
      e.Values["AccountID"] = accountID;

      string strLast = (string)e.Values["MemberLastName"];
      e.Values["MemberLastName"] = ProperCase.ToTitleCase(strLast.Trim());

      string strMiddle = (string)e.Values["MemberMiddleName"];
      if ((strMiddle != null) && (strMiddle.Length != 0))
          e.Values["MemberMiddleName"] = ProperCase.ToTitleCase(strMiddle.Trim());

      string strFirst = (string)e.Values["MemberFirstName"];
      e.Values["MemberFirstName"] = ProperCase.ToTitleCase(strFirst.Trim());

      e.Values["MemberStatusChangeDate"] = DateTime.Now;
      e.Values["MemberStatusChangeBy"] = User.Identity.Name;
      e.Values["MemberStatusID"] = 1; // 1 = Active
      e.Values["MemberPrimary"] = blnPrimary;

      string strComments = (string)e.Values["MemberStatusChangeComments"];
      if ((strComments != null) && (strComments.Length != 0))
      {
          e.Values["MemberStatusChangeComments"] = ProperCase.ToTitleCase(strComments.Trim());
      }
      else
      {
         e.Values["MemberStatusChangeComments"] = "Initial member creation.";
      }
   }
   
   protected void dvMemberDetails_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
   {
      if (e.AffectedRows > 0)
      {
         this.gvAccountMembers.DataBind();

         lblError.Text = "New member has been successfully added to the account.";
      }
      else
      {
         lblError.Text = "ERROR: Failed to add a new member to the account. Please try again.";
      }

      lblError.Visible = true;
   }

   protected void adsSearchAdmin_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
   {
      if (!IsPostBack)
      {
         if (Request.QueryString.Get("id") == null)
         {
            e.Cancel = true;
         }
      }
      else if ((txtBoxAccountID.Text == "") &&
               (txtBoxLastName.Text == "") &&
               (txtBoxFirstName.Text == "") &&
               (txtBoxTelephone.Text == "") &&
               (ddlJamatkhana.SelectedIndex == 0) )
      {
         e.Cancel = true;
      }
   }
   
   protected void btnReassign_Click(object sender, EventArgs e)
   {
      int selectedIndex = gvAccountSearch.SelectedIndex;

      // You can retrieve the key field from the SelectedDataKey property
      int accountID = (int)gvAccountSearch.SelectedDataKey.Values["AccountID"];

      try
      {
         // 1. Select the current primary member of the selected account
         int memberID = SelectCurrentPrimary(accountID);

         // 2. Clear the Primary value and the Relationship value of the current primary member
         ResetCurrentPrimary(accountID, memberID);

         int newMemberID = Convert.ToInt32(ddlNonPrimaryMembers.SelectedValue);

         // 3. Set the new Primary member
         SetNewPrimary(accountID, newMemberID);

         // 4. Refresh the gridviews
         this.gvAccountSearch.DataBind();
         this.gvAccountMembers.DataBind();
         this.ddlNonPrimaryMembers.Items.Clear();
         this.ddlNonPrimaryMembers.Items.Add(new ListItem("- Select new primary member -", "0"));
         this.ddlNonPrimaryMembers.DataBind();

         lblError.Text = "Primary head for this account has been successfully changed. Please update the Relationships of ALL members to the new primary head.";

         lblError.Visible = true;
      }
      catch
      {
         lblError.Text = "ERROR: Failed to change the primary head of this account. Please try again.";

         lblError.Visible = true;
      }
   }

   private int SelectCurrentPrimary(int accountID)
   {
      int memberID = 0;

      //Set the MemberPrimary value to NO and set the Relationship to UNKNOWN (Id = 21)
      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sqlSelect = "SELECT Members.MemberID FROM Members WHERE (Members.MemberStatusID = 1) AND (Members.MemberPrimary = TRUE) AND (Members.AccountID = @paramAccountID)";

      OleDbCommand cmd = new OleDbCommand(sqlSelect, oleDBConn);

      cmd.Parameters.AddWithValue("@paramAccountID", accountID);

      cmd.CommandType = CommandType.Text;

      oleDBConn.Open();

      memberID = (int)cmd.ExecuteScalar();

      oleDBConn.Close();

      return memberID;
   }

   private void ResetCurrentPrimary(int accountID, int memberID)
   {
      //Set the MemberPrimary value to NO and set the Relationship to UNKNOWN (Id = 21)
      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sqlUpdateCurrentPrimary = "UPDATE Members SET MemberPrimary = FALSE, MemberRelationID = 21 WHERE (Members.MemberID = @paramMemberID) AND (Members.AccountID = @paramAccountID)";

      OleDbCommand cmd = new OleDbCommand(sqlUpdateCurrentPrimary, oleDBConn);

      cmd.Parameters.AddWithValue("@paramMemberID", memberID);
      cmd.Parameters.AddWithValue("@paramAccountID", accountID);

      cmd.CommandType = CommandType.Text;

      oleDBConn.Open();

      cmd.ExecuteNonQuery();

      oleDBConn.Close();
   }

   private void SetNewPrimary(int accountID, int newMemberID)
   {
      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sqlUpdateCurrentPrimary = "UPDATE Members SET MemberPrimary = TRUE, MemberRelationID = 1 WHERE (Members.MemberID = @paramMemberID) AND (Members.AccountID = @paramAccountID)";

      OleDbCommand cmd = new OleDbCommand(sqlUpdateCurrentPrimary, oleDBConn);

      cmd.Parameters.AddWithValue("@paramMemberID", newMemberID);
      cmd.Parameters.AddWithValue("@paramAccountID", accountID);

      cmd.CommandType = CommandType.Text;

      oleDBConn.Open();

      cmd.ExecuteNonQuery();

      oleDBConn.Close();
   }

   protected void gvAccountSearch_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
   {
      this.ddlNonPrimaryMembers.Items.Clear();
      this.ddlNonPrimaryMembers.Items.Add(new ListItem("- Select new primary member -", "0"));
      this.btnRenew.Visible = true;
   }

   protected void btnRenew_Click(object sender, EventArgs e)
   {
       int accountID = ViewStateAccountID;
       string registrationYear = ConfigurationManager.AppSettings["RegistrationYear"];

       string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

       OleDbConnection oleDBConn = new OleDbConnection(connectionString);

       string sql = "UPDATE Accounts SET AccountRenewalYear = ?, AccountDateRenewed = ?, AccountUpdatedUser = ?, AccountUpdateDate = ? WHERE AccountID IN ({0})";

       string newsql = String.Format(sql, accountID);

       OleDbCommand cmd = new OleDbCommand(newsql, oleDBConn);

       cmd.CommandType = CommandType.Text;

       cmd.Parameters.AddWithValue("@RenewalYear", Convert.ToInt32(registrationYear));
       cmd.Parameters.AddWithValue("@DateRenewed", DateTime.Now.ToOADate());
       cmd.Parameters.AddWithValue("@UpdatedBy", ProperCase.ToTitleCase(User.Identity.Name.Trim()));
       cmd.Parameters.AddWithValue("@AccountUpdateDate", DateTime.Now.ToOADate());

       try
       {
           oleDBConn.Open();

           cmd.ExecuteNonQuery();

           lblRenew.Text = String.Format("Account {0} is now renewed.", accountID);

           this.txtBoxAccountID.Text = accountID.ToString();

           this.btnRenew.Visible = false;
           this.pnlReassignPrimary.Visible = false;
       }
       catch (Exception)
       {
           this.lblRenew.Text = String.Format("DB error updating Account {0}.", accountID);
       }
       finally
       {
           oleDBConn.Close();
       }
   }

   private static TextInfo ProperCase
   {
       get
       {
           //Get the culture property of the thread.
           CultureInfo ci = Thread.CurrentThread.CurrentCulture;
           //Create TextInfo object.
           TextInfo ti = ci.TextInfo;
           return ti;
       }
   }

}