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);
        }

        //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)
        {

        }

        //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;

                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);

                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;
            }
        }
    }
}