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 GridView
{
    public partial class Form1 : Form
    {
        private bool odwrotnaKolejnosc;
        int indexPoprzedniegoWiersza = 0;
        private Bitmap strzalkaGora;
        private Bitmap strzalkaDol;
        private double srednia;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection polaczenie = new SqlConnection();
            polaczenie.ConnectionString = 
                @"Data Source = DAWID-HP\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security = true";

            try
            {
                polaczenie.Open();
                // Czy uzyskano połączenie z bazą danych ?
                if (polaczenie.State == ConnectionState.Open)
                {
                    SqlCommand kwerenda = new SqlCommand();
                    kwerenda.CommandText = "SELECT top 10 * FROM Customers";
                    kwerenda.Connection = polaczenie;
                    SqlDataReader wynik = kwerenda.ExecuteReader();

                    using (wynik)
                    {
                        if (wynik.HasRows)
                        {
                            DataTable dt = new DataTable();
                            dt.Load(wynik);
                            DataColumn kolumnaObliczeniowa = 
                                new DataColumn("Długość", typeof(Int32));
                            kolumnaObliczeniowa.Expression = 
                                "LEN(CompanyName)";
                            dt.Columns.Add(kolumnaObliczeniowa);
                            srednia = Convert.ToDouble(dt.Compute("AVG(Długość)", null));

                            dataGridView1.DataSource = dt;
                            dataGridView1.AutoGenerateColumns = true;
                            dataGridView1.Dock = DockStyle.Bottom;
                            
                            // parametry okreslajace pozycje kontrolek typu CheckBox
                            int offset = 5;
                            int posX = 0;
                            int posY = 20;
                            this.Height += 2 * posY;                            

                            foreach (DataColumn dc in dt.Columns)
                            {                                
                                CheckBox cb = new CheckBox();                                
                                cb.AutoSize = true;
                                cb.Text = dc.ColumnName;                                
                                cb.Location = new System.Drawing.Point(posX += offset, 0);
                                cb.Parent = this;
                                cb.Checked = true;
                                cb.Click += new EventHandler(cb_Click);

                                posX = cb.Location.X + cb.Width + offset;                                                                
                            }                            
                        }
                    }
                }

                odwrotnaKolejnosc = false;
                dataGridView1.Click += new EventHandler(dataGridView1_Click);
                dataGridView1.MouseMove += new MouseEventHandler(dataGridView1_MouseMove);
                dataGridView1.DefaultCellStyle.SelectionBackColor = Color.Beige;
                dataGridView1.DefaultCellStyle.SelectionForeColor = Color.Brown;
                dataGridView1.DefaultCellStyle.NullValue = "Brak danych";
                dataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
                dataGridView1.DefaultCellStyle.Font = new Font("Consolas", 12);

                dataGridView1.AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells);
                dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
                                
                try
                {
                    strzalkaGora = new Bitmap("StrzalkaGora.bmp");
                    strzalkaDol = new Bitmap("StrzalkaDol.bmp");
                }
                catch (ArgumentException)
                {
                    MessageBox.Show("Nie odnaleziono plików z ikonami", 
                        "DataGridView", MessageBoxButtons.OK);
                }
                dataGridView1.Columns.Add(new DataGridViewImageColumn());
                dataGridView1.Columns[dataGridView1.ColumnCount - 1].Name = "Ikony";               
                dataGridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(dataGridView1_CellFormatting);
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show("Błąd SQL! " + sqlEx.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Inny błąd! " + ex.Message);
            }
        }

        void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if(dataGridView1.Columns[e.ColumnIndex].Name.Equals("Długość"))
            {                
                if(Convert.ToDouble(e.Value) >= srednia)
                    dataGridView1.Rows[e.RowIndex].Cells["Ikony"].Value = 
                        strzalkaGora;             
                else
                    dataGridView1.Rows[e.RowIndex].Cells["Ikony"].Value =
                        strzalkaDol;                    
            }
        }

        void dataGridView1_MouseMove(object sender, MouseEventArgs e)
        {
            int indexWiersza = dataGridView1.HitTest(e.X, e.Y).RowIndex;
            
            if (indexWiersza != -1 && indexWiersza < dataGridView1.RowCount 
                && indexWiersza != indexPoprzedniegoWiersza)
            {
                dataGridView1.Rows[indexPoprzedniegoWiersza].DefaultCellStyle.BackColor = 
                    Color.White;
                dataGridView1.Rows[indexWiersza].DefaultCellStyle.BackColor = 
                    Color.SkyBlue;
                indexPoprzedniegoWiersza = indexWiersza;
            }                        
        }

        void dataGridView1_Click(object sender, EventArgs e)
        {            
            for (int i = 0; i < dataGridView1.Columns.Count; i++)                          
                dataGridView1.Columns[i].DisplayIndex = 
                    !odwrotnaKolejnosc ? dataGridView1.ColumnCount - i - 1 : i;            
            
            odwrotnaKolejnosc = !odwrotnaKolejnosc;            
            
        }

        void cb_Click(object sender, EventArgs e)
        {
            CheckBox cb = sender as CheckBox;
            dataGridView1.Columns[cb.Text].Visible = cb.Checked;
        }
    }
}
