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;

namespace Project_management_system
{
    public partial class ClientRecords : Form
    {
        SqlDataReader rdr = null;
        DataTable dtable = new DataTable();
        SqlConnection con = null;
        SqlCommand cmd = null;
        DataTable dt = new DataTable();
        ConnectionString cs = new ConnectionString();
        public ClientRecords()
        {
            InitializeComponent();
        }

       

        public void GetData()
        {
            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                String sql = "SELECT RTRIM(client.id),RTRIM(Client.CategoryID), RTRIM(ClientName),RTRIM(Address),RTRIM(Email),RTRIM(ContactNo) ,RTRIM(ProjectName),RTRIM(Category.CategoryName),Convert(Datetime,DueDate,103),RTRIM(Cost),RTrim(GrandTotal),RTRIM(TotalPaid),RTRIM(PaymentDue),RTRIM(ModeOfPayment), RTRIM(PaymentModeDetails),Convert(Datetime,PaymentDate,103) FROM  Client,Category Where Category.ID=Client.CategoryID order by ClientName";
                cmd = new SqlCommand(sql, con);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dataGridView1.Rows.Clear();
                while (rdr.Read() == true)
                {
                    dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13],rdr[14],rdr[15]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void ClientRecords_Load(object sender, EventArgs e)
        {
            GetData();
        }

        private void dataGridView1_RowHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {

        }

       
        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 dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {

                DataGridViewRow dr = dataGridView1.SelectedRows[0];
                this.Hide();
                frmClientProfile frm = new frmClientProfile();
                frm.Show();
                frm.ID.Text = dr.Cells[0].Value.ToString();
                frm.txtCategoryID.Text = dr.Cells[1].Value.ToString();
                frm.txtClientName.Text = dr.Cells[2].Value.ToString();
                frm.txtAddress.Text = dr.Cells[3].Value.ToString();
                frm.txtEmail.Text = dr.Cells[4].Value.ToString();
                frm.txtContactNo.Text = dr.Cells[5].Value.ToString();
                frm.txtProjectName.Text = dr.Cells[6].Value.ToString();
                frm.cmbProjectType.Text = dr.Cells[7].Value.ToString();
                frm.txtDueDate.Text = dr.Cells[8].Value.ToString();
                frm.txtProjectCost.Text = dr.Cells[9].Value.ToString();
                frm.txtGrandTotal.Text = dr.Cells[10].Value.ToString();
                frm.txtTotalPaid.Text = dr.Cells[11].Value.ToString();
                frm.txtPreviousDue.Text = dr.Cells[12].Value.ToString();
                frm.cmbPaymentMode.Text = dr.Cells[13].Value.ToString();
                frm.txtPaymentModeDetails.Text = dr.Cells[14].Value.ToString();
                frm.dtpPaymentDate.Text = dr.Cells[15].Value.ToString();
                frm.lblUser.Text = lblUser.Text;
                frm.btnUpdate_record.Enabled = true;
                frm.btnSave.Enabled = false;
                frm.btnDelete.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void EmployeeName_TextChanged(object sender, EventArgs e)
        {
            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                String sql = "SELECT RTRIM(client.id),RTRIM(Client.CategoryID), RTRIM(ClientName),RTRIM(Address),RTRIM(Email),RTRIM(ContactNo) ,RTRIM(ProjectName),RTRIM(Category.CategoryName),Convert(Datetime,DueDate,103),RTRIM(Cost),RTrim(GrandTotal),RTRIM(TotalPaid),RTRIM(PaymentDue), RTRIM(PaymentModeDetails),RTRIM(ModeOfPayment),Convert(Datetime,PaymentDate,103) FROM  Client,Category Where category.id=client.CategoryID and ClientName Like '"+txtClientName.Text+"%' order by ClientName";
                cmd = new SqlCommand(sql, con);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dataGridView1.Rows.Clear();
                while (rdr.Read() == true)
                {
                    dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13], rdr[14],rdr[15]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                String sql = "SELECT RTRIM(client.id),Rtrim(client.CategoryID), RTRIM(ClientName),RTRIM(Address),RTRIM(Email),RTRIM(ContactNo) ,RTRIM(ProjectName),RTRIM(Category.CategoryName),Convert(Datetime,DueDate,103),RTRIM(Cost),RTrim(GrandTotal),RTRIM(TotalPaid),RTRIM(PaymentDue), RTRIM(PaymentModeDetails),RTRIM(ModeOfPayment),Convert(Datetime,PaymentDate,103) FROM  Client,Category  Where category.id=client.categoryID and ProjectName Like '" + txtprojectName.Text + "%' order by ClientName";
                cmd = new SqlCommand(sql, con);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dataGridView1.Rows.Clear();
                while (rdr.Read() == true)
                {
                    dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13], rdr[14],rdr[15]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                String sql = "SELECT RTRIM(client.id),Rtrim(Client.CategoryID), RTRIM(ClientName),RTRIM(Address),RTRIM(Email),RTRIM(ContactNo) ,RTRIM(ProjectName),RTRIM(Category.categoryName),Rtrim(DueDate),RTRIM(Cost),RTrim(GrandTotal),RTRIM(TotalPaid),RTRIM(PaymentDue), RTRIM(PaymentModeDetails),RTRIM(ModeOfPayment),Convert(Datetime,PaymentDate,103) FROM  Client,category Where category.id=client.categoryid and DueDate Between '"+DateFrom.Value.Date+"' and '"+DateTo.Value.Date+"' order by DueDate";
         
                cmd = new SqlCommand(sql, con);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dataGridView1.Rows.Clear();
                while (rdr.Read() == true)
                {
                    dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13], rdr[14],rdr[15]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                con = new SqlConnection(cs.ConDB);
                con.Open();
                String sql = "SELECT RTRIM(client.id),Rtrim(client.CategoryID), RTRIM(ClientName),RTRIM(Address),RTRIM(Email),RTRIM(ContactNo) ,RTRIM(ProjectName),RTRIM(Category.CategoryName),Convert(Datetime,DueDate,103),RTRIM(Cost),RTrim(GrandTotal),RTRIM(TotalPaid),RTRIM(PaymentDue), RTRIM(PaymentModeDetails),RTRIM(ModeOfPayment),Convert(Datetime,PaymentDate,103) FROM  Client,Category  Where category.id=client.categoryID and PaymentDue>0  order by PaymentDue";
                cmd = new SqlCommand(sql, con);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dataGridView1.Rows.Clear();
                while (rdr.Read() == true)
                {
                    dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13], rdr[14], rdr[15]);
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}
