using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
namespace Project_management_system
{
    public partial class EmployeeRecord : Form
    {
       
        DataTable dtable = new DataTable();
        SqlConnection con = null;
        SqlCommand cmd = null;
        ConnectionString cs = new ConnectionString();
        SqlDataAdapter adp;
        DataSet ds;
        public EmployeeRecord()
        {
            InitializeComponent();
        }

        private void EmployeeName_TextChanged(object sender, EventArgs e)
        {
            try
            {
              
                con = new SqlConnection(cs.ConDB);    
                con.Open();
                cmd = new SqlCommand("SELECT RTRIM(ID) as [Employee ID],RTRIM(Name) as [Employee Name],RTRIM(FatherName) as [Father's Name],RTRIM(City) as [City],RTRIM(Address) as [Address],RTRIM(ContactNo) as [Contact No.],RTRIM(Email_id) as [Email ID],RTRIM(Designation) as [Designation],RTRIM(Department) as [Department],RTRIM(BloodGroup) as [Blood Group],RTRIM(DateOfJoining) as [Joining Date],RTRIM(DOB) as [DOB],RTRIM(Experience_Years) as [Exp. Year(s)],RTRIM(Experience_Months) as [Exp. Month(s)],RTRIM(PastexperienceDetails) as [Past Exp. Details],Photo from Employee where Name like '" + EmployeeName.Text + "%' order by Name", con);
                SqlDataAdapter myDA = new SqlDataAdapter(cmd);
                DataSet myDataSet = new DataSet();
                myDA.Fill(myDataSet, "Employee");
                dataGridView1.DataSource = myDataSet.Tables["Employee"].DefaultView;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void Department_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
            
                con = new SqlConnection(cs.ConDB);
                con.Open();
                cmd = new SqlCommand("SELECT RTRIM(ID) as [Employee ID],RTRIM(Name) as [Employee Name],RTRIM(FatherName) as [Father's Name],RTRIM(City) as [City],RTRIM(Address) as [Address],RTRIM(ContactNo) as [Contact No.],RTRIM(Email_id) as [Email ID],RTRIM(Designation) as [Designation],RTRIM(Department) as [Department],RTRIM(BloodGroup) as [Blood Group],RTRIM(DateOfJoining) as [Joining Date],RTRIM(DOB) as [DOB],RTRIM(Experience_Years) as [Exp. Year(s)],RTRIM(Experience_Months) as [Exp. Month(s)],RTRIM(PastexperienceDetails) as [Past Exp. Details],Photo from Employee where Department= '" + Department.Text + "' order by Name");
                SqlDataAdapter myDA = new SqlDataAdapter(cmd);
            
                dataGridView2.DataSource = myDataSet.Tables["Employee"].DefaultView;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public void FillDepartment()    
        {

            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                adp = new SqlDataAdapter();
                adp.SelectCommand = new SqlCommand("SELECT distinct RTRIM(Department) FROM Employee", con);
                ds = new DataSet("ds");
                adp.Fill(ds);
                dtable = ds.Tables[0];
                Department.Items.Clear();
                foreach (DataRow drow in dtable.Rows)
                {
                    Department.Items.Add(drow[0].ToString());

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        public void FillDesignation()
        {

            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                adp = new SqlDataAdapter();
                adp.SelectCommand = new SqlCommand("SELECT distinct RTRIM(Designation) FROM Employee", con);
                ds = new DataSet("ds");
                adp.Fill(ds);
                dtable = ds.Tables[0];
                Designation.Items.Clear();
                foreach (DataRow drow in dtable.Rows)
                {
                    Designation.Items.Add(drow[0].ToString());

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        public void Fillcity()
        {

            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                adp = new SqlDataAdapter();
                adp.SelectCommand = new SqlCommand("SELECT distinct RTRIM(City) FROM Employee", con);
                ds = new DataSet("ds");
                adp.Fill(ds);
                dtable = ds.Tables[0];
                City.Items.Clear();
                foreach (DataRow drow in dtable.Rows)
                {
                    City.Items.Add(drow[0].ToString());

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        private void EmployeeRecord_Load(object sender, EventArgs e)  
        {
            FillDepartment();
            FillDesignation();
            Fillcity();
        }

        private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try{
                DataGridViewRow dr = dataGridView1.SelectedRows[0];
                this.Hide();
                EmployeeDetails frm = new EmployeeDetails();
                // or simply use column name instead of index
                // dr.Cells["id"].Value.ToString();
              frm.Show();
              frm.ID.Text = dr.Cells[0].Value.ToString();
              frm.txtEmployeeName.Text = dr.Cells[1].Value.ToString();
              frm.fatherName.Text = dr.Cells[2].Value.ToString();
              frm.City.Text = dr.Cells[3].Value.ToString();
              frm.Address.Text = dr.Cells[4].Value.ToString();
              frm.ContactNo.Text = dr.Cells[5].Value.ToString();
              frm.EmailId.Text = dr.Cells[6].Value.ToString();
              frm.Designation.Text = dr.Cells[7].Value.ToString();
              frm.cmbDepartment.Text = dr.Cells[8].Value.ToString();
              frm.BloodGroup.Text = dr.Cells[9].Value.ToString();
              frm.joiningDate.Text = dr.Cells[10].Value.ToString();
            frm.DOB.Text = dr.Cells[11].Value.ToString();
              frm.Years.Text = dr.Cells[12].Value.ToString();
             frm.Months.Text = dr.Cells[13].Value.ToString();
              frm.PastExperience.Text = dr.Cells[14].Value.ToString();
              byte[] data = (byte[])dr.Cells[15].Value;
              MemoryStream ms = new MemoryStream(data);
              frm.pictureBox1.Image = Image.FromStream(ms);
              frm.lblUser.Text = lblUser.Text;
              frm.btnUpdate.Enabled = true;
              frm.Delete.Enabled = true;
             
              frm.Save.Enabled = false;
            
             }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView2_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)     
        {
            try
            {
                DataGridViewRow dr = dataGridView2.SelectedRows[0];
                this.Hide();
                EmployeeDetails frm = new EmployeeDetails();
                // or simply use column name instead of index
                // dr.Cells["id"].Value.ToString();
                frm.Show();
                frm.ID.Text = dr.Cells[0].Value.ToString();
                frm.txtEmployeeName.Text = dr.Cells[1].Value.ToString();
                frm.fatherName.Text = dr.Cells[2].Value.ToString();
                frm.City.Text = dr.Cells[3].Value.ToString();
         
                frm.BloodGroup.Text = dr.Cells[9].Value.ToString();
                frm.joiningDate.Text = dr.Cells[10].Value.ToString();
                frm.DOB.Text = dr.Cells[11].Value.ToString();
                frm.Years.Text = dr.Cells[12].Value.ToString();
                frm.Months.Text = dr.Cells[13].Value.ToString();
                frm.PastExperience.Text = dr.Cells[14].Value.ToString();
                byte[] data = (byte[])dr.Cells[15].Value;
                MemoryStream ms = new MemoryStream(data);
                frm.pictureBox1.Image = Image.FromStream(ms);
                frm.lblUser.Text = lblUser.Text;
                frm.btnUpdate.Enabled = true;
                frm.Delete.Enabled = true;
       
                frm.Save.Enabled = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)   
        {
            string strRowNumber = (e.RowIndex + 1).ToString();
            SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
            if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
            {
                dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
            }
            Brush b = SystemBrushes.ControlText;
            e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
     
        }

        private void dataGridView2_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)   
        {
            string strRowNumber = (e.RowIndex + 1).ToString();
            SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
            if (dataGridView2.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
            {
                dataGridView2.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
            }
            Brush b = SystemBrushes.ControlText;
            e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
     
        }

        private void NewRecord_Click(object sender, EventArgs e)
        {
            EmployeeName.Text = "";
            dataGridView1.DataSource = null;
        }

       

        private void Designation_SelectedIndexChanged(object sender, EventArgs e)  
        {
            try
            {

                con = new SqlConnection(cs.ConDB);
                con.Open();
                cmd = new SqlCommand("SELECT RTRIM(ID) as [Employee ID],RTRIM(Name) as [Employee Name],RTRIM(FatherName) as [Father's Name],RTRIM(City) as [City],RTRIM(Address) as [Address],RTRIM(ContactNo) as [Contact No.],RTRIM(Email_id) as [Email ID],RTRIM(Designation) as [Designation],RTRIM(Department) as [Department],RTRIM(BloodGroup) as [Blood Group],RTRIM(DateOfJoining) as [Joining Date],RTRIM(DOB) as [DOB],RTRIM(Experience_Years) as [Exp. Year(s)],RTRIM(Experience_Months) as [Exp. Month(s)],RTRIM(PastexperienceDetails) as [Past Exp. Details],Photo from Employee where Designation= '" + Designation.Text + "' order by Name", con);
                SqlDataAdapter myDA = new SqlDataAdapter(cmd);
                DataSet myDataSet = new DataSet();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView3_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) 
        {
            try
            {
                DataGridViewRow dr = dataGridView3.SelectedRows[0];
                this.Hide();
                EmployeeDetails frm = new EmployeeDetails();
                // or simply use column name instead of index
                // dr.Cells["id"].Value.ToString();
                frm.Show();
                frm.ID.Text = dr.Cells[0].Value.ToString();
                frm.txtEmployeeName.Text = dr.Cells[1].Value.ToString();
           
                frm.joiningDate.Text = dr.Cells[10].Value.ToString();
                frm.DOB.Text = dr.Cells[11].Value.ToString();
                frm.Years.Text = dr.Cells[12].Value.ToString();
                frm.Months.Text = dr.Cells[13].Value.ToString();
                frm.PastExperience.Text = dr.Cells[14].Value.ToString();
                byte[] data = (byte[])dr.Cells[15].Value;
                MemoryStream ms = new MemoryStream(data);
                frm.pictureBox1.Image = Image.FromStream(ms);
                frm.lblUser.Text = lblUser.Text;
                frm.btnUpdate.Enabled = true;
                frm.Delete.Enabled = true;
                //frm.Print.Enabled = true;
                frm.Save.Enabled = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView3_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)  //on designation for header=number
        {
            string strRowNumber = (e.RowIndex + 1).ToString();
            SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
            if (dataGridView3.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
            {
                dataGridView3.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
            }
            Brush b = SystemBrushes.ControlText;
            e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
     
        }

       
        private void City_SelectedIndexChanged(object sender, EventArgs e)  
        {
            try
            {

                con = new SqlConnection(cs.ConDB);
                con.Open();
        
                cmd = new SqlCommand("SELECT RTRIM(ID) as [Employee ID],RTRIM(Name) as [Employee Name],RTRIM(FatherName) as [Father's Name],RTRIM(City) as [City],RTRIM(Address) as [Address],RTRIM(ContactNo) as [Contact No.],RTRIM(Email_id) as [Email ID],RTRIM(Designation) as [Designation],RTRIM(Department) as [Department],RTRIM(BloodGroup) as [Blood Group],RTRIM(DateOfJoining) as [Joining Date],RTRIM(DOB) as [DOB],RTRIM(Experience_Years) as [Exp. Year(s)],RTRIM(Experience_Months) as [Exp. Month(s)],RTRIM(PastexperienceDetails) as [Past Exp. Details],Photo from Employee where city= '" + City.Text + "' order by Name", con);
                SqlDataAdapter myDA = new SqlDataAdapter(cmd);   
                DataSet myDataSet = new DataSet();
                myDA.Fill(myDataSet, "Employee");
                dataGridView4.DataSource = myDataSet.Tables["Employee"].DefaultView;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView4_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {
                DataGridViewRow dr = dataGridView4.SelectedRows[0];
                this.Hide();
                EmployeeDetails frm = new EmployeeDetails();
                // or simply use column name instead of index
                // dr.Cells["id"].Value.ToString();
                frm.Show();
                frm.ID.Text = dr.Cells[0].Value.ToString();
                frm.txtEmployeeName.Text = dr.Cells[1].Value.ToString();
                frm.fatherName.Text = dr.Cells[2].Value.ToString();
                frm.City.Text = dr.Cells[3].Value.ToString();
                frm.Address.Text = dr.Cells[4].Value.ToString();
                frm.ContactNo.Text = dr.Cells[5].Value.ToString();
                frm.EmailId.Text = dr.Cells[6].Value.ToString();
                frm.Designation.Text = dr.Cells[7].Value.ToString();
                frm.cmbDepartment.Text = dr.Cells[8].Value.ToString();
                frm.BloodGroup.Text = dr.Cells[9].Value.ToString();
                frm.joiningDate.Text = dr.Cells[10].Value.ToString();
                frm.DOB.Text = dr.Cells[11].Value.ToString();
                frm.Years.Text = dr.Cells[12].Value.ToString();
              
                frm.Save.Enabled = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView4_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            string strRowNumber = (e.RowIndex + 1).ToString();
            SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
            if (dataGridView4.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
            {
                dataGridView4.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
            }
            Brush b = SystemBrushes.ControlText;
            e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
     
        }


        private void button9_Click(object sender, EventArgs e)
        {
            dataGridView5.DataSource = null;
            DateFrom.Text = System.DateTime.Today.ToString();
            DateTo.Text = System.DateTime.Today.ToString();
        }
        public void Reset()
        {
            dataGridView5.DataSource = null;
            DateFrom.Text = System.DateTime.Today.ToString();
            DateTo.Text = System.DateTime.Today.ToString();
            EmployeeName.Text = "";
            dataGridView1.DataSource = null;
            Department.SelectedIndex = -1;
            dataGridView2.Text = "";
            City.Text = "";
            dataGridView4.DataSource = null;
            Designation.SelectedIndex = -1;
            dataGridView3.DataSource = null;
        }
        private void tabControl1_Click(object sender, EventArgs e)
        {
            Reset();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Department.SelectedIndex= -1;
            dataGridView2.Text = "";
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Designation.SelectedIndex=-1;
            dataGridView3.DataSource = null;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            City.Text = "";
            dataGridView4.DataSource = null;
        }

       
       

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {

                con = new SqlConnection(cs.ConDB);
                con.Open();
                cmd = new SqlCommand("SELECT RTRIM(ID) as [Employee ID],RTRIM(Name) as [Employee Name],RTRIM(FatherName) as [Father's Name],RTRIM(City) as [City],RTRIM(Address) as [Address],RTRIM(ContactNo) as [Contact No.],RTRIM(Email_id) as [Email ID],RTRIM(Designation) as [Designation],RTRIM(Department) as [Department],RTRIM(BloodGroup) as [Blood Group],RTRIM(DateOfJoining) as [Joining Date],RTRIM(DOB) as [DOB],RTRIM(Experience_Years) as [Exp. Year(s)],RTRIM(Experience_Months) as [Exp. Month(s)],RTRIM(PastexperienceDetails) as [Past Exp. Details],Photo from Employee where DateOfJoining between @d1 and @d2 order by Name", con);
                cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateOfJoining").Value = DateFrom.Value.Date;
                cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateOfJoining").Value = DateTo.Value.Date;
             
                dataGridView5.DataSource = myDataSet.Tables["Employee"].DefaultView;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView5_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {
                DataGridViewRow dr = dataGridView5.SelectedRows[0];
                this.Hide();
                EmployeeDetails frm = new EmployeeDetails();
                // or simply use column name instead of index
                // dr.Cells["id"].Value.ToString();
                frm.Show();
                frm.ID.Text = dr.Cells[0].Value.ToString();
                frm.txtEmployeeName.Text = dr.Cells[1].Value.ToString();
                frm.fatherName.Text = dr.Cells[2].Value.ToString();
                frm.City.Text = dr.Cells[3].Value.ToString();
                frm.Address.Text = dr.Cells[4].Value.ToString();
                frm.ContactNo.Text = dr.Cells[5].Value.ToString();
                frm.EmailId.Text = dr.Cells[6].Value.ToString();
                frm.Designation.Text = dr.Cells[7].Value.ToString();
                frm.cmbDepartment.Text = dr.Cells[8].Value.ToString();
                frm.BloodGroup.Text = dr.Cells[9].Value.ToString();
                frm.joiningDate.Text = dr.Cells[10].Value.ToString();
                frm.DOB.Text = dr.Cells[11].Value.ToString();
                frm.Years.Text = dr.Cells[12].Value.ToString();
                frm.Months.Text = dr.Cells[13].Value.ToString();
  
                frm.btnUpdate.Enabled = true;
                frm.Delete.Enabled = true;
                //frm.Print.Enabled = true;
                frm.Save.Enabled = false;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView5_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            string strRowNumber = (e.RowIndex + 1).ToString();
            SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
            if (dataGridView5.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
            {
                dataGridView5.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
            }
            Brush b = SystemBrushes.ControlText;
            e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
     
        }
    }
}
