private void ResetProperty_Click(object sender, EventArgs e)
 {
     Agent_sproperty_agent.Clear();
     Agent_sproperty_ID.Clear();
     Agent_sproperty_custIDFN.Clear();
     Agent_sproperty_type.ResetText();
     Agent_sproperty_livarea.Clear();
     Agent_sproperty_be.ResetText();
     Agent_sproperty_ba.ResetText();
     Agent_sproperty_year.Clear();
     Agent_sproperty_loc.Clear();
     Agent_sproperty_price.Clear();
     issold.Checked = false;
     datesold.Clear();
     propsearchdatagrid.Rows.Clear();
 }
        private void propsearchdatagridcell(object sender, DataGridViewCellEventArgs e)
        {
            /////////////////just to clean the form
            Agent_sproperty_agent.Clear();
            Agent_sproperty_ID.Clear();
            Agent_sproperty_custIDFN.Clear();
            Agent_sproperty_type.ResetText();
            Agent_sproperty_livarea.Clear();
            Agent_sproperty_be.ResetText();
            Agent_sproperty_ba.ResetText();
            Agent_sproperty_year.Clear();
            Agent_sproperty_loc.Clear();
            Agent_sproperty_price.Clear();
            issold.Checked = false;
            datesold.Clear();
            /////////////// end of clean form
            ///start populating here
            try
            {
                if (e.RowIndex >= 0)
                {
                    Agent_sproperty_ID.Text = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyID"].Value.ToString();

                    string custidfn = "ID: " + propsearchdatagrid.Rows[e.RowIndex].Cells["outputcustomerID"].Value.ToString() + " Name: "
                                      + propsearchdatagrid.Rows[e.RowIndex].Cells["CustomerFN"].Value.ToString() + " "
                                      + propsearchdatagrid.Rows[e.RowIndex].Cells["CustomerLN"].Value.ToString();

                    Agent_sproperty_custIDFN.Text = custidfn;

                    Agent_sproperty_type.Text    = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyType"].Value.ToString();
                    Agent_sproperty_livarea.Text = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyArea"].Value.ToString();
                    Agent_sproperty_be.Text      = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyBedrooms"].Value.ToString();
                    Agent_sproperty_ba.Text      = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyBathrooms"].Value.ToString();
                    Agent_sproperty_year.Text    = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyYear"].Value.ToString();
                    Agent_sproperty_loc.Text     = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyLocation"].Value.ToString();
                    Agent_sproperty_price.Text   = propsearchdatagrid.Rows[e.RowIndex].Cells["PropertyPrice"].Value.ToString();

                    string agentidfirstlastname = "ID: " + propsearchdatagrid.Rows[e.RowIndex].Cells["outputAgentID"].Value.ToString() + " Name: "
                                                  + propsearchdatagrid.Rows[e.RowIndex].Cells["AgentFN"].Value.ToString() + " "
                                                  + propsearchdatagrid.Rows[e.RowIndex].Cells["AgentLN"].Value.ToString();
                    Agent_sproperty_agent.Text = agentidfirstlastname;
                    // end of populating

                    //start filling sold status and date

                    if (String.IsNullOrEmpty(propsearchdatagrid.Rows[e.RowIndex].Cells["TransactionID"].Value.ToString()))
                    {
                        issold.Checked = false;
                        datesold.Clear();
                    }
                    else
                    {
                        issold.Checked = true;

                        string query = "select Trans_date from Transaction where Transaction_ID = \"" + propsearchdatagrid.Rows[e.RowIndex].Cells["TransactionID"].Value.ToString() + "\"";

                        try
                        {
                            connection.Open();
                            MySqlCommand     cmd  = new MySqlCommand(query, connection);
                            MySqlDataAdapter mcmd = new MySqlDataAdapter();
                            MySqlDataReader  reader;
                            mcmd.SelectCommand = cmd;

                            reader = cmd.ExecuteReader();

                            if (reader.Read())
                            {
                                datesold.Text = ((DateTime)reader["Trans_date"]).ToString("d");
                            }
                        }
                        catch
                        {
                            MessageBox.Show("Error Occured!");
                        }
                        connection.Close();
                    }
                }
            }
            catch
            {
            }
            //end of filling sold status and date
        }
        //////////////////////////      Property TAB //////////////////////////////////////////////////////



        private void Searchproperty_Click(object sender, EventArgs e)
        {
            int searchparamcount = 0;

            string query = "Select P.*,  A.Agent_fname , A.Agent_lname, C.Customer_ID, C.Cust_fname, C.Cust_lname ";

            query += "from Property P inner join Agent A on A.Agent_ID = P.Agent_ID ";
            query += "inner join Registration R on R.Property_ID = P.Property_ID inner join Customer C on C.Customer_ID = R.Customer_ID where ";

            if (!String.IsNullOrEmpty(Agent_sproperty_ID.Text))
            {
                query += "Property_ID =" + "\"" + Agent_sproperty_ID.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_type.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_type =" + "\"" + Agent_sproperty_type.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_livarea.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_area =" + "\"" + Agent_sproperty_livarea.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_be.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_Br =" + "\"" + Agent_sproperty_be.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_ba.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_Ba =" + "\"" + Agent_sproperty_ba.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_year.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_Yr =" + "\"" + Agent_sproperty_year.Text.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_loc.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_Loc =" + "\"" + Agent_sproperty_loc.ToString() + "\"";
                searchparamcount++;
            }
            if (!String.IsNullOrEmpty(Agent_sproperty_price.Text))
            {
                if (searchparamcount > 0)
                {
                    query += " and ";
                }
                query += "Prop_price =" + "\"" + Agent_sproperty_price.ToString() + "\"";
                searchparamcount++;
            }


            if (searchparamcount == 0)
            {
                query  = "Select P.*,T.Transaction_ID ,A.Agent_fname , A.Agent_lname, C.Customer_ID, C.Cust_fname, C.Cust_lname ";
                query += "from Property P inner join Agent A on A.Agent_ID = P.Agent_ID ";
                query += "inner join Registration R on R.Property_ID = P.Property_ID ";
                query += "inner join Customer C on C.Customer_ID = R.Customer_ID ";
                query += "left join Transaction T on T.Property_ID = P.Property_ID";
            }

            //end validation + query composing

            try
            {
                connection.Open();
                MySqlCommand     cmd  = new MySqlCommand(query, connection);
                MySqlDataAdapter mcmd = new MySqlDataAdapter();
                MySqlDataReader  reader;
                mcmd.SelectCommand = cmd;

                reader = cmd.ExecuteReader();

                propsearchdatagrid.Rows.Clear();

                while (reader.Read())
                {
                    this.propsearchdatagrid.Rows.Add(
                        reader["Property_ID"].ToString(),
                        reader["Prop_type"].ToString(),
                        reader["Prop_area"].ToString(),
                        reader["Prop_Br"].ToString(),
                        reader["Prop_Ba"].ToString(),
                        reader["Prop_Yr"].ToString(),
                        reader["Prop_Loc"].ToString(),
                        reader["Prop_price"].ToString(),
                        reader["Transaction_ID"].ToString(),
                        reader["Customer_ID"].ToString(),
                        reader["Cust_fname"].ToString(),
                        reader["Cust_lname"].ToString(),
                        reader["Agent_ID"].ToString(),
                        reader["Agent_fname"].ToString(),
                        reader["Agent_lname"].ToString()
                        );
                }
            }
            catch
            {
                MessageBox.Show("Insert less parameters for more results");
            }
            connection.Close();
        }