using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Baza_Reklam
{
    internal class ComboItem
    {
        private string _display;
        private int _value;

        public ComboItem(int value, string display)
        {
            _display = display;
            _value = value;
        }

        public string Display
        {
            get { return _display; }
        }

        public int Value
        {
            get { return _value; }
        }
    }

    internal class DBBindings
    {
        public static void bindujTytuly(ToolStripComboBox toolStripComboBox)
        {
            List<ComboItem> comboItems = new List<ComboItem>();
            
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select Id, SYMB from [LISTA TYTUŁÓW] where Active=1";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    comboItems.Add(new ComboItem(reader.GetInt32(0), reader.GetString(1)));
                }
            }
            command.Connection.Close();

            toolStripComboBox.ComboBox.DataSource = comboItems.ToArray();
            toolStripComboBox.ComboBox.DisplayMember = "Display";
            toolStripComboBox.ComboBox.ValueMember = "Value";
        }

        public static void bindujTytuly2(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select id,SYMB from [LISTA TYTUŁÓW] where st_aktywny=-1";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    toolStripComboBox.Items.Add(new BoundItem(Convert.ToInt32(reader.GetValue(0))
                                                              , reader.GetValue(1).ToString()));
                }
            }
            command.Connection.Close();
        }

        public static void bindujTypyReklam(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "SELECT SYMB FROM [LISTA TYPÓW REKLAMY]";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    toolStripComboBox.Items.Add(reader.GetValue(0).ToString());
                }
            }
            command.Connection.Close();
        }


        public static void BindujGrzbiety(ToolStripComboBox toolStripComboBox)
        {
            SLOWNIKDataSetTableAdapters.GRZBIETYTableAdapter ta =
                new SLOWNIKDataSetTableAdapters.GRZBIETYTableAdapter();

            ta.Connection.ConnectionString = ConnString.getConnString().Value;

            ((ComboBox) toolStripComboBox.Control).DataSource = ta.GetData();
            ((ComboBox) toolStripComboBox.Control).DisplayMember = "Nazwa";
            ((ComboBox) toolStripComboBox.Control).ValueMember = "ID";
            toolStripComboBox.SelectedIndex = -1;
        }

        public static void bindujGrzbiety2(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "SELECT id,Nazwa FROM GRZBIETY";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    toolStripComboBox.Items.Add(new BoundItem(reader.GetInt32(0), reader.GetString(1)));
                }
            }
            command.Connection.Close();
        }

        public static void BindujGrzbiety(ToolStripComboBox toolStripComboBox, int tytulId)
        {
            List<ComboItem> comboItems = new List<ComboItem>();

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = string.Format("SELECT Id, Nazwa FROM dbo.Grzbiety WHERE TytulId={0} AND Active=1",
                                                tytulId);
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                while (reader.Read())
                    comboItems.Add(new ComboItem(reader.GetInt32(0), reader.GetString(1)));
            }
            command.Connection.Close();

            toolStripComboBox.ComboBox.DataSource = comboItems.ToArray();
            toolStripComboBox.ComboBox.DisplayMember = "Display";
            toolStripComboBox.ComboBox.ValueMember = "Value";
        }

        public static void bindujAgencje(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select Symbol from dbo.AGENCJE where aktywna = 1 order by Symbol";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    toolStripComboBox.Items.Add(reader.GetValue(0).ToString());
                }
            }
            command.Connection.Close();
        }


        public static void BindKontaktTyp(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "SELECT Id, Name from dbo.KontaktTyp";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();
            ArrayList kontakTypList = new ArrayList();
            kontakTypList.Add(new ComboItem(0, string.Empty));


            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    kontakTypList.Add(new ComboItem(Convert.ToInt32(reader.GetValue(0)), reader.GetValue(1).ToString()));
                }
            }
            command.Connection.Close();

            toolStripComboBox.ComboBox.DataSource = kontakTypList;
            toolStripComboBox.ComboBox.DisplayMember = "Display";
            toolStripComboBox.ComboBox.ValueMember = "Value";
        }

        //Przez BindingSource by bylo filtrowanie
        public static void bindujAgentow(ToolStripComboBox toolStripComboBox)
        {
            /*
            SLOWNIKDataSetTableAdapters.AGENCITableAdapter ta =
                new SLOWNIKDataSetTableAdapters.AGENCITableAdapter();

            ta.Connection.ConnectionString = ConnString.getConnString().Value;
            
            BindingSource bs = new BindingSource();
            bs.DataSource = ta.GetDataByAktywny();
            ((ComboBox)toolStripComboBox.Control).DataSource = bs;
            ((ComboBox)toolStripComboBox.Control).DisplayMember = "Symbol";
            ((ComboBox)toolStripComboBox.Control).ValueMember = "Id_agenta";
            toolStripComboBox.SelectedIndex = -1;
             * */

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select Symbol from dbo.AGENCI where aktywny=1 order by Symbol";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    toolStripComboBox.Items.Add(reader.GetValue(0).ToString());
                }
            }
            command.Connection.Close();
        }

        public static void BindujModuly(ToolStripComboBox toolStripComboBox, string title)
        {

            List<ComboItem> comboItems = new List<ComboItem>();

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = string.Format("SELECT Id, MOD_TYP FROM dbo.[Nazwy Modułów] WHERE Tytul='{0}' AND Active=1 ORDER BY MOD_TYP",
                                                title);
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                while (reader.Read())
                    comboItems.Add(new ComboItem(reader.GetInt32(0), reader.GetString(1)));
            }
            command.Connection.Close();

            toolStripComboBox.ComboBox.DataSource = comboItems.ToArray();
            toolStripComboBox.ComboBox.DisplayMember = "Display";
            toolStripComboBox.ComboBox.ValueMember = "Value";
        }

        ///////////////////////////////////////////////////////////////////////////////////

        public static void dodajAgencjeDoWezla(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select ID_AGENCJI,Symbol from dbo.AGENCJE where aktywna = 1";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            TreeNode newNode;

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(1).ToString());
                newNode.Name = reader.GetValue(0).ToString();
                newNode.Nodes.Add(new TreeNode());
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void dodajAgentowDoWezla(TreeNode node, string idAgencji)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select Symbol from dbo.AGENCI where aktywny=1 and ID_AGENCJI=@param order by Symbol";
            command.Parameters.AddWithValue("@param", idAgencji);
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();
            TreeNode newNode;

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(0).ToString();
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void dodajTytulyDoWezla(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select id, SYMB from [LISTA TYTUŁÓW] where st_aktywny=-1";
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();
            TreeNode newNode;

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(1).ToString());
                newNode.Name = reader.GetValue(1).ToString();
                newNode.Nodes.Add(new TreeNode());
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void dodajKlasyfikacjePoziom1(TreeView treeView)
        {
            treeView.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select KL_1,ID_KL_1 from dbo.KL_1  order by KL_1";

            TreeNode newNode;
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(1).ToString();
                newNode.Nodes.Add(new TreeNode());
                treeView.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void bindujDatyWydan(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText =
                "select distinct datepart(year,DATA_W) as rok from dbo.NR where datepart(year,DATA_W) is not null order by rok desc";

            TreeNode newNode;
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(0).ToString();

                for (int i = 12; i >= 1; i--)
                {
                    TreeNode node2 = new TreeNode(i.ToString());
                    node2.Name = i.ToString();
                    //node2.Nodes.Add(new TreeNode());
                    newNode.Nodes.Add(node2);
                }
                node.Nodes.Add(node);
            }

            command.Connection.Close();
        }

        public static void dodajKlasyfikacjePoziom1(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select KL_1,ID_KL_1 from dbo.KL_1  order by KL_1";

            TreeNode newNode;
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(1).ToString();
                newNode.Nodes.Add(new TreeNode());
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void dodajKlasyfikacjePoziom2(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select KL_2,ID_KL_2 from dbo.KL_2 where ID_KL_1=@param  order by KL_2";
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@param", node.Name);

            TreeNode newNode;
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(1).ToString();
                newNode.Nodes.Add(new TreeNode());
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void dodajKlasyfikacjePoziom3(TreeNode node)
        {
            node.Nodes.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText =
                "select KL_3,ID_KL_3 from dbo.KL_3 where ID_KL_1=@param1 and ID_KL_2=@param2  order by KL_3";
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@param1", node.Parent.Name);
            command.Parameters.AddWithValue("@param2", node.Name);

            TreeNode newNode;
            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newNode = new TreeNode(reader.GetValue(0).ToString());
                newNode.Name = reader.GetValue(1).ToString();
                node.Nodes.Add(newNode);
            }

            command.Connection.Close();
        }

        public static void BindujPromocje(ComboBox comboBox, DataTable dt)
        {
            comboBox.Items.Add(string.Empty);
            comboBox.DisplayMember = "Nazwa";
            comboBox.ValueMember = "Id";

            foreach (DataRow row in dt.Rows)
            {
                comboBox.Items.Add(row);
            }

            //comboBox.DataSource = dt;
            //comboBox.DisplayMember = "Nazwa";
            //comboBox.ValueMember = "Id";
            //comboBox.Items.Insert(0, string.Empty);
        }
    }
}