using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace OrionGames
{
    public partial class OrionGames : Form
    {
        SqlConnection sqlConnection;
        SqlDataReader myReader;

        public OrionGames()
        {
            InitializeComponent();

            //connect to the SQL Database
            String connectionString = "Data Source=localhost;" + 
                "database=orion;" +
                "Integrated Security = true;" +
                "connection timeout=15;";
            sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
        }

        //closes SQL Connection if form is closed
        private void OrionGames_FormClosed(object sender, FormClosedEventArgs e)
        {
            sqlConnection.Close();
        }

        //used to enable or disable the member information controls
        private void EnableMemControls(bool enabled)
        {
            txtMemFName.Enabled = enabled;
            txtMemLName.Enabled = enabled;
            txtMemAddress.Enabled = enabled;
            txtMemEmail.Enabled = enabled;
            txtMemPhone.Enabled = enabled;
            txtMemMobile.Enabled = enabled;
            dateMemBDae.Enabled = enabled;
            dateMemJoin.Enabled = enabled;
            chkMemIsActive.Enabled = enabled;
            numMemOverdue.Enabled = enabled;
        }

        //used to reset the members controls to the next empty "mid" in the database
        private void ResetControls()
        {
            SqlCommand findID;
            List<string> emptyID = new List<string>();
            myReader = null;
            string strCommand;

            //gets the ID number of members from database
            strCommand = "select mid from member";
            findID = new SqlCommand(strCommand, sqlConnection);
            myReader = findID.ExecuteReader();

            if (myReader.HasRows)
            {
                while (myReader.Read())
                {
                    emptyID.Add(myReader["mid"].ToString());
                }

                int lastID = 0;

                //checks the next empty Id number in the database
                foreach (string id in emptyID)
                {
                    int intID = Convert.ToInt32(id);

                    if (intID > lastID)
                    {
                        lastID = intID;
                    }
                }

                lblMemIDNum.Text = Convert.ToString(lastID + 1);

                myReader.Close();
            }

            txtMemFName.Text = "";
            txtMemLName.Text = "";
            txtMemAddress.Text = "";
            txtMemEmail.Text = "";
            txtMemPhone.Text = "";
            txtMemMobile.Text = "";
            dateMemBDae.Value = DateTime.Now;
            dateMemJoin.Value = DateTime.Now;
        }

        //handler to enable the controls for a new member with the next empty ID number
        private void btnMemNew_Click(object sender, EventArgs e)
        {
            ResetControls();

            EnableMemControls(true);

            txtMemFName.Focus();
        }

        //hadler to enable controls and update the existing member in the database
        private void btnEditMem_Click(object sender, EventArgs e)
        {
            EnableMemControls(true);
        }

        //handler to search through the database for matches with the given information
        private void btnMemSearch_Click(object sender, EventArgs e)
        {
            listMembers.Items.Clear();

            SqlCommand findMember;
            string[] member = new string[11];
            string strCommand;
            myReader = null;

            if (txtSearchFName.Text == "" && txtSearchLName.Text == ""
                && txtSearchEmail.Text == "" && chkSearchMemIsActive.Checked == false)
            {
                strCommand = "select * from member";
            }
            else
            {
                string strFName = "";
                string strLName = "";
                string strEMail = "";
                string strActive = "";

                if (txtSearchFName.Text != "")
                {
                    strFName = "mfirst = " + "'" + txtSearchFName.Text + "'" + "and ";
                }
                if (txtSearchLName.Text != "")
                {
                    strLName = "mlast = " + "'" + txtSearchLName.Text + "'" + "and ";
                }
                if (txtSearchEmail.Text != "")
                {
                    strEMail = "memail = " + "'" + txtSearchEmail.Text + "'" + "and ";
                }
                if (chkSearchMemIsActive.Checked)
                {
                    strActive = "mstatus = 1 and ";
                }
                if (!chkSearchMemIsActive.Checked)
                {
                    strActive = "mstatus = 0 and ";
                }

                strCommand = "select * from member where " + strFName + strLName + strEMail + strActive;

                //deletes the last four characters that aren't needed for the query
                strCommand = strCommand.Remove(strCommand.Length - 4, 4); ;
            }

            findMember = new SqlCommand(strCommand, sqlConnection);
            myReader = findMember.ExecuteReader();

            //if there are results from the query, will be placed in the listview control
            if (myReader.HasRows)
            {
                while(myReader.Read())
                {
                    member[0] = myReader["mid"].ToString();
                    member[1] = myReader["mfirst"].ToString();
                    member[2] = myReader["mlast"].ToString();
                    member[3] = myReader["madd"].ToString();
                    member[4] = myReader["mhomephone"].ToString();
                    member[5] = myReader["mmobile"].ToString();
                    member[6] = myReader["mdob"].ToString();
                    member[7] = myReader["memail"].ToString();
                    member[8] = myReader["mjoindate"].ToString();
                    member[9] = myReader["mstatus"].ToString();
                    member[10] = myReader["mnum_overdue"].ToString();

                    listMembers.Items.Add(new ListViewItem(member));
                }

                listMembers.Focus();
                myReader.Close();
            }
            else
            {
                MessageBox.Show("No data matches your search.");

                txtSearchFName.Focus();
                myReader.Close();
            }
        }

        //handler to add row into the database member table
        private void btnSaveMem_Click(object sender, EventArgs e)
        {
            if (txtMemFName.Enabled)
            {
                SqlCommand saveMember;
                string strCommand;

                //create member sql parameters
                SqlParameter mfname = new SqlParameter("@mfname", SqlDbType.VarChar, 30);
                SqlParameter mlname = new SqlParameter("@mlname", SqlDbType.VarChar, 30);
                SqlParameter maddress = new SqlParameter("@maddress", SqlDbType.VarChar, 100);
                SqlParameter mphone = new SqlParameter("@mphone", SqlDbType.Char, 12);
                SqlParameter mmobile = new SqlParameter("@mmobile", SqlDbType.Char, 11);
                SqlParameter mbdae = new SqlParameter("@mbdae", SqlDbType.Char, 10);
                SqlParameter memail = new SqlParameter("@memail", SqlDbType.VarChar);
                SqlParameter mjoin = new SqlParameter("@mjoin", SqlDbType.Char, 10);
                SqlParameter mactive = new SqlParameter("@mactive", SqlDbType.Bit);
                SqlParameter moverdue = new SqlParameter("@moverdue", SqlDbType.Int);


                //arrange the dates into the appropriate format for the database DD/MM/YYYY
                DateTime dateBdae = dateMemBDae.Value;
                DateTime dateJoin = dateMemJoin.Value;

                string bdaeMonth = dateBdae.Month.ToString();
                string joinMonth = dateJoin.Month.ToString();

                if (bdaeMonth.Length != 2)
                {
                    bdaeMonth = "0" + bdaeMonth;
                }
                if (joinMonth.Length != 2)
                {
                    joinMonth = "0" + joinMonth;
                }

                string bDae = dateBdae.Day.ToString() + "/" + bdaeMonth +
                    "/" + dateBdae.Year.ToString();
                string join = dateJoin.Day.ToString() + "/" + joinMonth +
                    "/" + dateJoin.Year.ToString();

                int active = Convert.ToInt32(chkMemIsActive.Checked);

                mfname.Value = txtMemFName.Text;
                mlname.Value = txtMemLName.Text;
                maddress.Value = txtMemAddress.Text;
                mphone.Value = txtMemPhone.Text;
                mmobile.Value = txtMemMobile.Text;
                mbdae.Value = bDae;
                memail.Value = txtMemEmail.Text;
                mjoin.Value = join;
                mactive.Value = active;
                moverdue.Value = numMemOverdue.Value;

                try
                {
                    //saves data into the database
                    strCommand = "INSERT INTO member VALUES (@mfname,@mlname,@maddress,@mphone," +
                        "@mmobile,@mbdae,@memail,@mjoin,@mactive,@moverdue)";

                    saveMember = new SqlCommand(strCommand, sqlConnection);

                    saveMember.Parameters.Add(mfname);
                    saveMember.Parameters.Add(mlname);
                    saveMember.Parameters.Add(maddress);
                    saveMember.Parameters.Add(mphone);
                    saveMember.Parameters.Add(mmobile);
                    saveMember.Parameters.Add(mbdae);
                    saveMember.Parameters.Add(memail);
                    saveMember.Parameters.Add(mjoin);
                    saveMember.Parameters.Add(mactive);
                    saveMember.Parameters.Add(moverdue);

                    saveMember.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.ToString());
                }

                MessageBox.Show("Data was saved successfully");

                EnableMemControls(false);
            }
            else
            {
                MessageBox.Show("Cannot save. No members are being added or edited.");
            }
        }

        //a mousedown handler on the listview control to view the information
        //into the members controls if needed to be edited
        private void listMembers_MouseDown(object sender, MouseEventArgs e)
        {
            ListViewItem selection = listMembers.GetItemAt(e.X, e.Y);

            //if there is data selected in the listview control
            if (selection != null)
            {
                lblMemIDNum.Text = selection.SubItems[0].Text;
                txtMemFName.Text = selection.SubItems[1].Text;
                txtMemLName.Text = selection.SubItems[2].Text;
                txtMemAddress.Text = selection.SubItems[3].Text;
                txtMemPhone.Text = selection.SubItems[4].Text;
                txtMemMobile.Text = selection.SubItems[5].Text;
                txtMemEmail.Text = selection.SubItems[7].Text;

                //arranges the bday date into an appropriate format for program MM/DD/YYYY
                string[] date = new string[3];

                string bdae = selection.SubItems[6].Text;
                string newbdaedate;

                date = bdae.Split('/');
                newbdaedate = date[1] + "/" + date[0] + "/" + date[2];
                dateMemBDae.Value = Convert.ToDateTime(newbdaedate);

                //arranges the join date into an aapropriate format for progran MM/DD/YYYY
                date = new string[3];

                string join = selection.SubItems[8].Text;
                string newjoindate;

                date = join.Split('/');
                newjoindate = date[1] + "/" + date[0] + "/" + date[2];
                dateMemJoin.Value = Convert.ToDateTime(newjoindate);

                numMemOverdue.Value = Convert.ToDecimal(selection.SubItems[10].Text);

                bool active = Convert.ToBoolean(selection.SubItems[9].Text);
                chkMemIsActive.Checked = active;

                MessageBox.Show(dateMemBDae.Value.Month.ToString());
            }
        }
    }
}