using System;
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.Globalization;
using System.Threading;
using System.Configuration;

public partial class NewAccount : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       if (Request.IsAuthenticated == false)
       {
          Response.Redirect("~/Account/Login.aspx");
       }

       if (!Page.IsPostBack)
       {
          this.rangeValidatorBirthYear.MinimumValue = (DateTime.Now.Year - 110).ToString();
          this.rangeValidatorBirthYear.MaximumValue = DateTime.Now.Year.ToString();

          StringBuilder sb = new StringBuilder();
          sb.Append("Birth Year must range from ");
          int start = DateTime.Now.Year - 110;
          sb.Append(start.ToString());
          sb.Append(" to ");
          sb.Append(DateTime.Now.Year);
          sb.Append(".");
          this.rangeValidatorBirthYear.ErrorMessage = sb.ToString();
       }
    }

    protected void btnView3Next_Click(object sender, EventArgs e)
    {
        DateTime dtAccountCreated = new DateTime();
        dtAccountCreated = DateTime.Now;
        int accountID = 0;

        Page.Validate();

        if (Page.IsValid)
        {
            accountID = InsertAccount(dtAccountCreated);

            if (accountID != 0)
            {
                InsertPrimaryMember(dtAccountCreated, accountID);

                Response.Redirect("~/Admin/SearchAdmin.aspx?id=" + accountID.ToString());
            }
        }
    }

    private int InsertAccount(DateTime dt)
    {
        //Get the culture property of the thread.
        CultureInfo ci = Thread.CurrentThread.CurrentCulture;
        //Create TextInfo object.
        TextInfo ti = ci.TextInfo;

        string registrationYear = ConfigurationManager.AppSettings["RegistrationYear"];

        int accountID = 0;

        string strNewAccountComment = "Initial new account creation.";

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

        OleDbConnection oleDBConn = new OleDbConnection(connectionString);

        StringBuilder sql = new StringBuilder();

        string sql2 = "SELECT @@Identity";

        sql.Append("INSERT INTO Accounts (AccountJamatkhanaID, AccountStreetAddress, AccountCityID, AccountProvinceID, AccountPostalCode, AccountPhoneNumber, ");

        sql.Append("AccountStatusID, AccountDateRenewed, AccountRenewalYear, AccountCreationYear, AccountUpdateDate, AccountUpdatedUser, AccountComments) ");

        sql.Append(" VALUES (@JamatkhanaID, @StreetAddress, @CityID, @ProvinceID, @PostalCode, @PhoneNumber, ");

        sql.Append("@StatusID, @DateRenewed, @RenewalYear, @CreationYear, @UpdateDate, @UpdatedUser, @Comments) ");

        OleDbCommand cmd = new OleDbCommand(sql.ToString(), oleDBConn);

        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@JamatkhanaID", Convert.ToInt32(ddlJamatkhana.SelectedValue));

        if (txtboxStreetAddress.Text != "")
            cmd.Parameters.AddWithValue("@StreetAddress", ti.ToTitleCase(txtboxStreetAddress.Text.Trim()));
        else
            cmd.Parameters.AddWithValue("@StreetAddress", "");

        if (ddlCity.SelectedIndex != 0)
            cmd.Parameters.AddWithValue("@CityID", Convert.ToInt32(ddlCity.SelectedValue));
        else
            cmd.Parameters.AddWithValue("@CityID", 415); // UNDECLARED / UNKNOWN CITY

        if (ddlProvince.SelectedIndex != 0)
            cmd.Parameters.AddWithValue("@ProvinceID", Convert.ToInt32(ddlProvince.SelectedValue));
        else
            cmd.Parameters.AddWithValue("@ProvinceID", 16); // UNDECLARED / UNKNOWN PROVINCE

        if (txtboxPostalCode.Text != "")
            cmd.Parameters.AddWithValue("@PostalCode", ti.ToUpper(txtboxPostalCode.Text.Trim()));
        else
            cmd.Parameters.AddWithValue("@PostalCode", "");

        cmd.Parameters.AddWithValue("@PhoneNumber", txtboxTelephone.Text.Trim());
        cmd.Parameters.AddWithValue("@StatusID", 1); // 1 = Active
        cmd.Parameters.AddWithValue("@DateRenewed", dt.ToOADate());
        cmd.Parameters.AddWithValue("@RenewalYear", registrationYear);
        cmd.Parameters.AddWithValue("@CreationYear", dt.Year);
        cmd.Parameters.AddWithValue("@UpdateDate", dt.ToOADate());
        cmd.Parameters.AddWithValue("@UpdatedUser", User.Identity.Name);
        cmd.Parameters.AddWithValue("@Comments", strNewAccountComment);

        oleDBConn.Open();

        cmd.ExecuteNonQuery();

        cmd.CommandText = sql2;

        accountID = (int)cmd.ExecuteScalar();

        oleDBConn.Close();

        return accountID;
    }

    private void InsertPrimaryMember(DateTime dt, int accountID)
    {
       //Get the culture property of the thread.
       CultureInfo ci = Thread.CurrentThread.CurrentCulture;
       //Create TextInfo object.
       TextInfo ti = ci.TextInfo;

       string strNewMemberComment = "Initial new member creation.";
       bool blnPrimary = true;

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

       OleDbConnection oleDBConn = new OleDbConnection(connectionString);

       StringBuilder sql = new StringBuilder();

       sql.Append("INSERT INTO Members (AccountID, MemberLastName, MemberMiddleName, MemberFirstName, MemberBirthYear, MemberGenderID, ");

       sql.Append("MemberStatusID, MemberRelationID, MemberStatusChangeDate, MemberStatusChangeBy, MemberPrimary, MemberStatusChangeComments, MemberJamatiTitleID) ");

       sql.Append(" VALUES (@AccountID, @LastName, @MiddleName, @FirstName, @BirthYear, @GenderID, ");

       sql.Append("@StatusID, @RelationID, @StatusChangeDate, @StatusChangeBy, @Primary, @Comments, @MemberJamatiTitleID) ");

       OleDbCommand cmd = new OleDbCommand(sql.ToString(), oleDBConn);

       cmd.CommandType = CommandType.Text;
       cmd.Parameters.AddWithValue("@AccountID", accountID);

       cmd.Parameters.AddWithValue("@LastName", ti.ToTitleCase(txtboxLastName.Text.Trim()) );

       if (txtboxMiddleName.Text != "")
          cmd.Parameters.AddWithValue("@MiddleName", ti.ToTitleCase(txtboxMiddleName.Text.Trim()) );
       else
          cmd.Parameters.AddWithValue("@MiddleName", "");

       cmd.Parameters.AddWithValue("@FirstName", ti.ToTitleCase(txtboxFirstName.Text.Trim()) );

       cmd.Parameters.AddWithValue("@BirthYear", Convert.ToInt32(txtboxBirthYear.Text.Trim()));
       cmd.Parameters.AddWithValue("@GenderID", Convert.ToInt32(ddlGender.SelectedValue));
       cmd.Parameters.AddWithValue("@StatusID", 1); // 1 = Active
       cmd.Parameters.AddWithValue("@RelationID", 1); // 1 = Self
       cmd.Parameters.AddWithValue("@StatusChangeDate", dt.ToOADate());
       cmd.Parameters.AddWithValue("@StatusChangeBy", User.Identity.Name);
       cmd.Parameters.AddWithValue("@Primary", blnPrimary);
       cmd.Parameters.AddWithValue("@Comments", strNewMemberComment);
       cmd.Parameters.AddWithValue("@MemberJamatiTitleID", Convert.ToInt32(ddlJamatiTitle.SelectedValue));

       oleDBConn.Open();

       cmd.ExecuteNonQuery();

       oleDBConn.Close();

    }

    protected void btnCancel_Click(object sender, EventArgs e)
    {
       Response.Redirect("~/Admin/SearchAdmin.aspx");
    }
}