using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using System.Windows.Forms;

namespace Baza_Reklam
{
    class DBBindings
    {
        public static void bindujTytuly(ToolStripComboBox toolStripComboBox)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);

            command.CommandText = "select 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(reader.GetValue(0).ToString());
                }
            }
            command.Connection.Close();
        }

        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)
        {
            /*
            SLOWNIKDataSetTableAdapters.LISTA_TYPOW_REKLAMYTableAdapter ta =
                new SLOWNIKDataSetTableAdapters.LISTA_TYPOW_REKLAMYTableAdapter();

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

            ((ComboBox)toolStripComboBox.Control).DataSource = ta.GetData();
            ((ComboBox)toolStripComboBox.Control).DisplayMember = "SYMB";
            ((ComboBox)toolStripComboBox.Control).ValueMember = "SYMB";
            toolStripComboBox.SelectedIndex = -1;
             */

            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 bindujAgencje(ToolStripComboBox toolStripComboBox)
        {
            /*
            SLOWNIKDataSetTableAdapters.AGENCJETableAdapter ta =
                new SLOWNIKDataSetTableAdapters.AGENCJETableAdapter();

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

            ((ComboBox)toolStripComboBox.Control).DataSource = ta.GetData();
            ((ComboBox)toolStripComboBox.Control).DisplayMember = "Symbol";
            ((ComboBox)toolStripComboBox.Control).ValueMember = "Id_agencji";
            toolStripComboBox.SelectedIndex = -1;
             * */

            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();

        }

        //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();
        }

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

            ta.Connection.ConnectionString = ConnString.getConnString().Value;
            
            BindingSource bs2 = new BindingSource();
            bs2.DataSource = ta.GetData();
            ((ComboBox)toolStripComboBox.Control).DataSource = bs2;
            ((ComboBox)toolStripComboBox.Control).DisplayMember = "MOD_TYP";
            ((ComboBox)toolStripComboBox.Control).ValueMember = "MOD_TYP";
            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 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();
        }

    }
}