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

namespace Baza_Reklam
{
    public partial class StartForm : Form
    {

        private bool pierwszaAktywacja;

        public static StartForm startForm;

        public static StartForm getStartForm(MDIBazaReklam parent)
        {
            if (startForm == null) startForm = new StartForm(parent);
            return startForm;
        }

        private StartForm(Form parent)
        {
            InitializeComponent();

            MdiParent = parent;
            
            shedulerTableAdapter.Connection.ConnectionString = ConnString.getConnString().Value;
            zastawienieKontaktowTableAdapter.Connection.ConnectionString = ConnString.getConnString().Value;
            view_Podsumowanie_Wykonania_PlanuTableAdapter1.Connection.ConnectionString = ConnString.getConnString().Value;
            view_Podsumowanie_Wykonania_Planu_AgencjaTableAdapter1.Connection.ConnectionString = ConnString.getConnString().Value;
            kontaktTypTableAdapter.Connection.ConnectionString = ConnString.getConnString().Value;

            if (User.getUser().St_kierownik) 
            {
                wynikAgencji();
            }
            else if (User.getUser().St_handlowiec | User.getUser().St_subhandlowiec) 
            {
                wynikAgenta();                   
            }
        }

        private void zrobioneToolStripButton_Click(object sender, EventArgs e)
        {
            if (shedulerBindingSource.Current != null) {
                DataRowView row = (DataRowView)shedulerBindingSource.Current;
                row["zalatwione"] = false;
                shedulerBindingSource.EndEdit();
                shedulerTableAdapter.Update(rEKLAMADataSet.sheduler);
                shedulerDataGridView.Refresh();
            }
        }

        private void niezrobioneToolStripButton_Click(object sender, EventArgs e)
        {
            if (shedulerBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)shedulerBindingSource.Current;
                row["zalatwione"] = true;
                shedulerBindingSource.EndEdit();
                shedulerTableAdapter.Update(rEKLAMADataSet.sheduler);
                shedulerDataGridView.Refresh();
            }
        }

        private void filtrujToolStripComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            filtruj();
        }

        private void StartForm_Activated(object sender, EventArgs e)
        {
            if (pierwszaAktywacja)
            {
                wypelnijShedulerWyniki();
            }
        }

        private void StartForm_Load(object sender, EventArgs e)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(ConnString.getConnString().Value);
            SqlDataReader reader;

            if (User.getUser().St_kierownik)
            {            
                command.CommandText = "select Symbol from dbo.AGENCI where aktywny=1 and ID_AGENCJI=@param order by Symbol";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@param", User.getUser().IdAgencji);
                command.Connection.Open();

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    agenciToolStripComboBox.Items.Add(reader.GetValue(0).ToString());

                }
                agenciToolStripComboBox.Visible = true;
            }

            command.Connection.Close();

            DBBindings.bindujAgencje(agencjaToolStripComboBox);
            DBBindings.bindujAgentow(agentToolStripComboBox);
   
            filtrujToolStripComboBox.SelectedIndex = 0;
            agenciToolStripComboBox.SelectedIndex = 0;

            kontaktTypTableAdapter.Fill(sLOWNIKDataSet.KontaktTyp);
            cbKontaktTyp.ComboBox.DataSource = sLOWNIKDataSet.KontaktTyp;

            DBBindings.BindKontaktTyp(cbKontaktTyp);
        }

        private void agenciToolStripComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            filtruj();   
        }

        private void filtruj() {

            if ((filtrujToolStripComboBox.SelectedItem != null) & (agenciToolStripComboBox.SelectedItem != null))
            {
                DateTime ostatniDzienMiesiaca = DateTime.Today.AddMonths(1).AddDays(-(DateTime.Today.Day));
                DateTime ostatniDzienTygodnia = DateTime.Today.AddDays(7).AddDays(-((int)DateTime.Today.DayOfWeek));

                DateTime pierwszyDzienMiesiaca = DateTime.Today.AddDays(-DateTime.DaysInMonth(DateTime.Today.Year,DateTime
                    .Today.Month)).AddDays(DateTime.DaysInMonth(DateTime.Today.Year,DateTime.Today.Month) - DateTime.Today.Day);
                DateTime pierwszyDzienTygodnia = DateTime.Today.AddDays(-7).AddDays(((int)DateTime.Today.DayOfWeek));

                switch (filtrujToolStripComboBox.SelectedItem.ToString())
                {

                    case "NA DZISIAJ":
                        shedulerBindingSource.Filter = "[DataPrzypomnienia] >= '" + DateTime.Today.Date + "' AND [DataPrzypomnienia] < '" + DateTime.Today.Date.AddDays(1) + "'";
                        break;
                    case "NA JUTRO":
                        shedulerBindingSource.Filter = "[DataPrzypomnienia] >= '" + DateTime.Today.Date.AddDays(1) + "' AND [DataPrzypomnienia] < '" + DateTime.Today.Date.AddDays(2) + "'";
                        break;
                    case "NA TYDZIEŃ":
                        shedulerBindingSource.Filter = "[DataPrzypomnienia] >= '" +  pierwszyDzienTygodnia + "' AND [DataPrzypomnienia] <= '" + ostatniDzienTygodnia + "'";
                        break;
                    case "NA MIESIĄC":
                        shedulerBindingSource.Filter = "[DataPrzypomnienia] >= '" + pierwszyDzienMiesiaca + "' AND [DataPrzypomnienia] <= '" + ostatniDzienMiesiaca + "'";
                        break;
                    case "WSZYSTKIE":
                        shedulerBindingSource.Filter = " 1 = 1";
                        break;
                    case "NIEZAŁATWIONE ":
                        shedulerBindingSource.Filter = "zalatwione = 1";
                        break;
                }

                if (agenciToolStripComboBox.SelectedItem.ToString() != "WSZYSCY")
                {
                    shedulerBindingSource.Filter += " AND [akwizytor]='" + agenciToolStripComboBox.SelectedItem + "'";
                }
            }
        }

        private void klientToolStripButton_Click(object sender, EventArgs e)
        {
            if (shedulerBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)shedulerBindingSource.Current;

                int custID = Convert.ToInt32(row["CustomerId"]);

                ClientsForm.getClientsForm((MDIBazaReklam)MdiParent).pokazKlienta(custID);

                Hide();

                ClientsForm.getClientsForm((MDIBazaReklam)MdiParent).Show();
            }
        }

        private void usunToolStripButton_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Czy na pewno chcesz usunąć rekord?", "", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                if (shedulerBindingSource.Current != null)
                {
                    shedulerBindingSource.RemoveCurrent();
                    shedulerBindingSource.EndEdit();
                    shedulerTableAdapter.Update(rEKLAMADataSet.sheduler);
                }
            }
        }

        private void shedulerDataGridView_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show("Wprowadź poprawnie dane");
        }

        private void zapiszToolStripButton_Click(object sender, EventArgs e)
        {
            shedulerDataGridView.EndEdit();
            shedulerBindingSource.EndEdit();
            shedulerTableAdapter.Update(rEKLAMADataSet.sheduler);            
            MessageBox.Show("Dane zapisane");  
        }

        private void shedulerDataGridView_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            shedulerDataGridView.EndEdit();
        }


        private void shedulerDataGridView_Leave(object sender, EventArgs e)
        {
            shedulerDataGridView.EndEdit();
        }

        private void sprDoZalatwieniaTabPage_Leave(object sender, EventArgs e)
        {
            shedulerBindingSource.EndEdit();

            REKLAMADataSet.shedulerDataTable changes = rEKLAMADataSet.sheduler.GetChanges(DataRowState.Modified)
                as REKLAMADataSet.shedulerDataTable;

            if (changes != null)
            {
                if (MessageBox.Show("Czy zapisać zmiany?", "", MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    shedulerTableAdapter.Update(rEKLAMADataSet.sheduler);
                }
                else
                {
                    rEKLAMADataSet.sheduler.RejectChanges();
                }
            }
        }

        private void klient2ToolStripButton_Click(object sender, EventArgs e)
        {
            if (zastawienieKontaktowBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zastawienieKontaktowBindingSource.Current;

                int custID = Convert.ToInt32(row["CustomerId"]);

                ClientsForm.getClientsForm((MDIBazaReklam)MdiParent).pokazKlienta(custID);

                Hide();

                ClientsForm.getClientsForm((MDIBazaReklam)MdiParent).Show();
            }
        }

        private void szukajToolStripButton_Click(object sender, EventArgs e)
        {
            string query = "SELECT TOP 1000 KLIENCI.FirstName, KONTAKTY.symbol_agenta, KONTAKTY.data, KONTAKTY.rodzaj,";
            query += " KONTAKTY.opis, KLIENCI.CustomerID, KLIENCI.UserName FROM KONTAKTY LEFT OUTER JOIN KLIENCI ON KONTAKTY.customerId = KLIENCI.CustomerID ";
            query += "left outer join AGENCI on KONTAKTY.symbol_agenta = AGENCI.Symbol left outer join ";
            query += "AGENCJE on AGENCI.ID_AGENCJI = AGENCJE.Id_agencji where 1=1 ";

            SqlConnection conn = new SqlConnection(ConnString.getConnString().Value);      
   
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.Connection = conn;        
            command.CommandText = query;
            command.Parameters.Clear();

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);


            if (rokToolStripTextBox.Text.Trim() != "")
            {
                int i;
                if (!Int32.TryParse(rokToolStripTextBox.Text.Trim(), out i))
                {
                    MessageBox.Show("Podaj prawidłowy rok.");
                    return;
                }
                command.CommandText += " AND datepart(year,KONTAKTY.data) = @rok ";
                command.Parameters.AddWithValue("@rok", rokToolStripTextBox.Text.Trim());
            }

            if (msToolStripTextBox.Text.Trim() != "")
            {
                int i;
                if (!Int32.TryParse(msToolStripTextBox.Text.Trim(), out i))
                {
                    MessageBox.Show("Podaj prawidłowy miesiąc.");
                    return;
                }                
                command.CommandText += " AND datepart(month,KONTAKTY.data) = @ms ";
                command.Parameters.AddWithValue("@ms", msToolStripTextBox.Text.Trim());
            }

            if (agencjaToolStripComboBox.Text.Trim() != "") 
            {
                command.CommandText += " AND AGENCJE.Symbol = @agencja ";
                command.Parameters.AddWithValue("@agencja", agencjaToolStripComboBox.Text.Trim());
            }

            if (agentToolStripComboBox.Text.Trim() != "")
            {
                command.CommandText += " AND AGENCI.Symbol = @agent ";
                command.Parameters.AddWithValue("@agent", agentToolStripComboBox.Text.Trim());
            }

            if (rodzajToolStripComboBox.Text.Trim() != "")
            {
                command.CommandText += " AND KONTAKTY.rodzaj = @rodzaj ";
                command.Parameters.AddWithValue("@rodzaj", rodzajToolStripComboBox.Text.Trim());
            }

            if (klientToolStripTextBox.Text.Trim() != "")
            {
                command.CommandText += " AND KLIENCI.FirstName like '%' + @klient + '%' ";
                command.Parameters.AddWithValue("@klient", klientToolStripTextBox.Text.Trim());               
            }

            if (cbKontaktTyp.SelectedItem != null)
            {
                ComboItem comboItem = (ComboItem) cbKontaktTyp.SelectedItem;
                if (comboItem.Value > 0)
                {
                    command.CommandText += " AND KONTAKTY.KontaktTypId=@kontaktTyp ";
                    command.Parameters.AddWithValue("@kontaktTyp", comboItem.Value);
                }
            }

            command.CommandText += " order by KONTAKTY.data";

            sqlDataAdapter.SelectCommand = command;

            Cursor = Cursors.WaitCursor;

            rEKLAMADataSet.zastawienieKontaktow.Clear();

            try
            {
                sqlDataAdapter.Fill(rEKLAMADataSet.zastawienieKontaktow);
            }
            finally
            {
                Cursor = Cursors.Default;                
            }
        }

        private void wyczyscToolStripButton_Click(object sender, EventArgs e)
        {
            rEKLAMADataSet.KONTAKTY.Clear();

            rokToolStripTextBox.Clear();
            msToolStripTextBox.Clear();
            klientToolStripTextBox.Clear();               
        
            agencjaToolStripComboBox.SelectedIndex = -1;
            agenciToolStripComboBox.Text = "";

            agentToolStripComboBox.SelectedIndex = -1;
            agentToolStripComboBox.Text = "";
            
            rodzajToolStripComboBox.SelectedIndex = -1;
            rodzajToolStripComboBox.Text = "";
        }

        private void zastawienieKontaktowDataGridView_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            zastawienieKontaktowBindingSource.CancelEdit();
        }

        private void wynikAgenta() {

            rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu.Clear();
            view_Podsumowanie_Wykonania_PlanuTableAdapter1.Fill(rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu, User.getUser().Login, (short)DateTime.Today.Month, (short)DateTime.Today.Year);

            ShowMonthAndYearResuts(rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu);
        }

        private void wynikAgencji()
        {
            rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu_Agencja.Clear();
            view_Podsumowanie_Wykonania_Planu_AgencjaTableAdapter1.Fill(
                rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu_Agencja,
                User.getUser().SymbolAgencji, (short)DateTime.Today.Month, (short)DateTime.Today.Year);

            ShowMonthAndYearResuts(rEKLAMADataSet.View_Podsumowanie_Wykonania_Planu_Agencja);

        }

        private void ShowMonthAndYearResuts(DataTable results)
        {
            if(results.Rows.Count <= 0) return;

            lblPlanBudzet.Text = string.Format("{0:C2}", results.Rows[0]["BUDZET"]);
            lblWykBudzet.Text = string.Format("{0:C2}", results.Rows[0]["W_BUDZET"]);

            lblPlanSpotkania.Text = string.Format("{0}", results.Rows[0]["SPOTKANIA"]);
            lblWykSpotkania.Text = string.Format("{0}", results.Rows[0]["W_SPOTKANIA"]);

            lblPlanKontakty.Text = string.Format("{0}", results.Rows[0]["KONTAKTY"]);
            lblWykKontakty.Text = string.Format("{0}", results.Rows[0]["W_KONTAKTY"]);

            lblPlanKlienci.Text = string.Format("{0}", results.Rows[0]["N_KLIENCI"]);
            lblWykKlienci.Text = string.Format("{0}", results.Rows[0]["W_N_KLIENCI"]);

            lblPlanReklamy.Text = string.Format("{0}", results.Rows[0]["REKLAMY"]);
            lblWykReklamy.Text = string.Format("{0}", results.Rows[0]["W_REKLAMY"]);


            //wynik roczny
            lblPlanBudzetR.Text = string.Format("{0:C2}", results.Rows[0]["R_BUDZET"]);
            lblWykBudzetR.Text = string.Format("{0:C2}", results.Rows[0]["R_W_BUDZET"]);

            lblPlanSpotkaniaR.Text = string.Format("{0}", results.Rows[0]["R_SPOTKANIA"]);
            lblWykSpotkaniaR.Text = string.Format("{0}", results.Rows[0]["R_W_SPOTKANIA"]);

            lblPlanKontaktyR.Text = string.Format("{0}", results.Rows[0]["R_KONTAKTY"]);
            lblWykKontaktyR.Text = string.Format("{0}", results.Rows[0]["R_W_KONTAKTY"]);

            lblPlanKlienciR.Text = string.Format("{0}", results.Rows[0]["R_N_KLIENCI"]);
            lblWykKlienciR.Text = string.Format("{0}", results.Rows[0]["R_W_N_KLIENCI"]);

            lblPlanReklamyR.Text = string.Format("{0}", results.Rows[0]["R_REKLAMY"]);
            lblWykReklamyR.Text = string.Format("{0}", results.Rows[0]["R_W_REKLAMY"]);
        }

        private void kryteriumWyszukiwania_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                szukajToolStripButton.PerformClick();
            }
        }

        private void StartForm_Shown(object sender, EventArgs e)
        {
           // MessageBox.Show("ddd");

            if (User.getUser().St_kierownik)
            {
                agencjaToolStripComboBox.Text = User.getUser().SymbolAgencji;
            }
            else if (User.getUser().St_handlowiec | User.getUser().St_subhandlowiec)
            {
                agentToolStripComboBox.Text = User.getUser().Login;
            }

            pierwszaAktywacja = true;

            rokToolStripTextBox.Text = DateTime.Now.Year.ToString();
            msToolStripTextBox.Text = DateTime.Now.Month.ToString();
            
            wypelnijShedulerWyniki();
        }

        private void wypelnijShedulerWyniki()
        {          

            if (User.getUser().St_kierownik)
            {
                shedulerTableAdapter.FillByAgencjaWgDaty(rEKLAMADataSet.sheduler, User.getUser().IdAgencji);
            }
            else
            {
                shedulerTableAdapter.FillByAgentWgDaty(rEKLAMADataSet.sheduler, User.getUser().Login);
            }

            if (pierwszaAktywacja) return;
            
            if (User.getUser().St_kierownik)
            {
                wynikAgencji();
            }
            else if (User.getUser().St_handlowiec | User.getUser().St_subhandlowiec)
            {
                wynikAgenta();
            }
        }
    }
}