using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Baza_Reklam
{
    public partial class FacturesForm : Form
    {
        string query= "SELECT TOP 2000 * FROM VIEW_ZESTAWIENIE_FAKTUR_STARE ";

        private static FacturesForm fakturesForm;
        private SqlCommand command;
        private SqlDataAdapter sqlDataAdapter;

        private bool commandExecuted = false;

        public static FacturesForm getFacturesForm(MDIBazaReklam parent)
        {
            if (fakturesForm == null){
                fakturesForm = new FacturesForm(parent);
            }
            return fakturesForm;
        }

        private FacturesForm(MDIBazaReklam parent)
        {
            InitializeComponent();

            this.MdiParent = parent;

            //podmiana connstringa
            zestawienieFakturTableAdapter.Connection.ConnectionString = ConnString.getConnString().Value;
            reklamaTableAdapter.Connection.ConnectionString = ConnString.getConnString().Value;
         
            //obiekty wykorzytywane przy wyszukiwaniu
            SqlConnection conn = new SqlConnection(ConnString.getConnString().Value);
           
            command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.Connection = conn;
            
            this.sqlDataAdapter = new SqlDataAdapter();

            //generuje piersze węzły w drzewie
            generateNodes();

            //ustawia domyslny rok i miesiąc w pasku wyszukiwania
            rokToolStripTextBox.Text = DateTime.Today.Year.ToString();
            miesiacToolStripTextBox.Text = DateTime.Today.Month.ToString();

            wyszukajToolStrip.Items.Insert(14, new ToolStripLabel("nieuregulowane:"));
            wyszukajToolStrip.Items.Insert(15, new ToolStripControlHost(new CheckBox(), "zalegleCheckBox"));
            ((CheckBox)((ToolStripControlHost)wyszukajToolStrip.Items["zalegleCheckBox"]).Control).ThreeState = true;
            ((CheckBox)((ToolStripControlHost)wyszukajToolStrip.Items["zalegleCheckBox"]).Control).CheckState = CheckState.Indeterminate;           
        }

        private void FacturesForm_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;

            DBBindings.bindujGrzbiety2(grzbietToolStripComboBox);
            DBBindings.bindujAgencje(agencjaToolStripComboBox);
            DBBindings.bindujTytuly2(tytulToolStripComboBox);

            command.CommandText = this.query;
            sqlDataAdapter.SelectCommand = command;
        }

        private void generateNodes()
        {
            TreeNode node;

            node = new TreeNode("Wpłaty - księgowość");
            node.Name = "WplatyKsiegowosc";
            treeView1.Nodes.Add(node);

            node = new TreeNode("Różnice wpłat");
            node.Nodes.Add(new TreeNode());
            node.Name = "BrakWKsiegowosci";

            treeView1.Nodes["WplatyKsiegowosc"].Nodes.Add(node);

            node = new TreeNode("Brak w reklamie");
            node.Nodes.Add(new TreeNode());
            node.Name = "BrakWReklamie";

            treeView1.Nodes["WplatyKsiegowosc"].Nodes.Add(node);
            
            command.Connection.Close();                         
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (e.Node != null)
            {
                command.CommandText = query;
                command.Parameters.Clear();

                switch (e.Node.Level)
                {
                    case 0:
                        break;
                    case 1:
                        switch (e.Node.Parent.Name)
                        {
                            case "WplatyKsiegowosc":
                                break;
                            default:
                                break;
                        }
                        break;
                    case 2:
                        switch (e.Node.Parent.Parent.Name)
                        {
                            case "WplatyKsiegowosc":
                                switch (e.Node.Parent.Name)
                                {
                                    case "BrakWKsiegowosci":
                                        this.Cursor = Cursors.WaitCursor;

                                        rEKLAMADataSet.ZestawienieFaktur.Clear();

                                        command.CommandText += " HAVING YEAR(reklama.[DATA ZAPŁATY]) = @rok AND (FAKTURY.suma_zaplat is null OR (CONVERT(int,  FAKTURY.suma_zaplat * 100) <> CONVERT(int, SUM(FAKTURA_DETAILS.BRUTTO) * 100))) order by reklama.[DATA ZAPŁATY]";

                                        command.Parameters.AddWithValue("@rok", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;

                                        break;

                                    case "BrakWReklamie":
                                        this.Cursor = Cursors.WaitCursor;

                                        rEKLAMADataSet.ZestawienieFaktur.Clear();
                                        command.CommandText += " HAVING YEAR(FAKTURY.DATA_SPRZEDAZY) = @rok AND (FAKTURY.suma_zaplat is not null AND FAKTURY.suma_zaplat<>0) AND (reklama.[DATA ZAPŁATY] is null) order by FAKTURY.DATA_SPRZEDAZY";
                                        command.Parameters.AddWithValue("@rok", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;

                                        break;
                                    default:
                                        break;
                                }
                                break;
                            default:
                                break;
                        }
                        break;
                    case 3:
                        switch (e.Node.Parent.Parent.Parent.Name)
                        {
                            case "WplatyKsiegowosc":
                                switch (e.Node.Parent.Parent.Name)
                                {
                                    case "BrakWKsiegowosci":
                                        this.Cursor = Cursors.WaitCursor;

                                        rEKLAMADataSet.ZestawienieFaktur.Clear();
                                        command.CommandText += " HAVING YEAR(reklama.[DATA ZAPŁATY]) = @rok AND MONTH(reklama.[DATA ZAPŁATY]) = @miesiac AND (FAKTURY.suma_zaplat is null OR (CONVERT(int,  FAKTURY.suma_zaplat * 100) <> CONVERT(int, SUM(FAKTURA_DETAILS.BRUTTO) * 100))) order by reklama.[DATA ZAPŁATY]";
                                        command.Parameters.AddWithValue("@rok", e.Node.Parent.Name);
                                        command.Parameters.AddWithValue("@miesiac", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;
                                        break;

                                    case "BrakWReklamie":
                                        this.Cursor = Cursors.WaitCursor;

                                        rEKLAMADataSet.ZestawienieFaktur.Clear();
                                        command.CommandText += " HAVING YEAR(FAKTURY.DATA_SPRZEDAZY) = @rok AND MONTH(FAKTURY.DATA_SPRZEDAZY) = @miesiac AND (FAKTURY.suma_zaplat is not null AND FAKTURY.suma_zaplat<>0) AND (reklama.[DATA ZAPŁATY] is null) order by FAKTURY.DATA_SPRZEDAZY";
                                        command.Parameters.AddWithValue("@rok", e.Node.Parent.Name);
                                        command.Parameters.AddWithValue("@miesiac", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;
                                        break;
                                }
                                break;
                            default:
                                break;
                        }
                        break;
                    case 4:
                        switch (e.Node.Parent.Parent.Parent.Parent.Name)
                        {
                            case "WplatyKsiegowosc":
                                break;
                            default:
                                break;                             
                        }
                        break;
                    default:
                        break;
                }           
                }  
        }

        private void treeView1_AfterExpand(object sender, TreeViewEventArgs e)
        {
            TreeNode node;
            SqlDataReader reader;

            if (e.Node != null)
            {
               switch (e.Node.Level)
                {
                    case 0:                       
                        break;
                    case 1:
                        switch (e.Node.Parent.Name)
                        {
                            case "WplatyKsiegowosc":
                                switch (e.Node.Name)
                                {
                                    case "BrakWKsiegowosci":
                                        e.Node.Nodes.Clear();

                                        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";
                                        command.Connection.Open();
                                        reader = command.ExecuteReader();

                                        while (reader.Read())
                                        {
                                            node = new TreeNode(reader.GetValue(0).ToString());
                                            node.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());
                                                node.Nodes.Add(node2);
                                            }
                                            e.Node.Nodes.Add(node);
                                        }

                                        command.Connection.Close();

                                        break;
                                    case "BrakWReklamie":
                                        e.Node.Nodes.Clear();

                                        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";
                                        command.Connection.Open();
                                        reader = command.ExecuteReader();

                                        while (reader.Read())
                                        {
                                            node = new TreeNode(reader.GetValue(0).ToString());
                                            node.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());
                                                node.Nodes.Add(node2);
                                            }
                                            e.Node.Nodes.Add(node);
                                        }

                                        command.Connection.Close();

                                        break;
                                }
                                break;
                            default:
                                e.Node.Nodes.Clear();

                                node = new TreeNode("Biura");
                                node.Name = "Biura";
                                node.Nodes.Add(new TreeNode());
                                e.Node.Nodes.Add(node);
                                break;                      
                        }
                        break;
                    case 2:
                
                        switch (e.Node.Name) { 
                            case "Biura":
                                DBBindings.dodajAgencjeDoWezla(e.Node);
                                break;
                            default:
                                break;
                        }

                        break;
                    case 3:
                        switch (e.Node.Parent.Name)
                        {
                            case "Biura":
                                DBBindings.dodajAgentowDoWezla(e.Node, e.Node.Name);
                                break;
                            default:
                                break;
                        }
                        break;
                   case 4:
                       break;                            
                    default:
                        break;
                }
            }
        }

        private void fakturyDataGridView_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            zestawienieFakturBindingSource.CancelEdit();
        }
        
        private void zamToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturBindingSource.Current != null)
            {              
                DataRowView row = (DataRowView)zestawienieFakturBindingSource.Current;

                if (row["reklamaId"] != DBNull.Value)
                {
                    int idRek = Convert.ToInt32(row["reklamaId"]);
                    
                    OrderDetails.getOrderDetails().pokazSzczegolyZamowienia(idRek);
                    OrderDetails.getOrderDetails().ShowDialog();

                    /*
                    OrderDetails od = new OrderDetails();
                    od.pokazSzczegolyZamowienia(idRek);
                    DialogResult result = od.ShowDialog();*/
                }
            }
        }

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

                if (row["reklamaId"] != DBNull.Value)
                {
                    int idRek = Convert.ToInt32(row["reklamaId"]);
                    Facturer f = new Facturer(idRek);
                    DialogResult result = f.ShowDialog();
                }
            }
        }

        private void odswiezToolStripButton_Click(object sender, EventArgs e)
        {
            if (commandExecuted)
            {
                this.Cursor = Cursors.WaitCursor;

                rEKLAMADataSet.ZestawienieFaktur.Clear();
                sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                fakturyDataGridView.Refresh();

                this.Cursor = Cursors.Default;
            }
        }

        private void wyszukajToolStripButton_Click(object sender, EventArgs e)
        {
            wyszukajToolStrip.Visible = wyszukajToolStrip.Visible ? false : true;
        }

        private void szukajToolStripButton_Click(object sender, EventArgs e)
        {
            command.CommandText = query;

            this.rEKLAMADataSet.ZestawienieFaktur.Clear();

            command.CommandText += " where 1=1 ";

            command.Parameters.Clear();

            if (nrFakturyToolStripTextBox.Text.Trim() != "")
            {
                int i;
                if (!Int32.TryParse(nrFakturyToolStripTextBox.Text, out i))
                {
                    MessageBox.Show("Podaj prawidłowy numer faktury.");
                    return;
                }

                command.CommandText += " AND NUMER=@nr";
                command.Parameters.AddWithValue("@nr", nrFakturyToolStripTextBox.Text.Trim());
            }

            if (kodRozliczeniowyToolStripTextBox.Text.Trim() != "")
            {
                command.CommandText += " AND NUMER_ROZ=@kod";
                command.Parameters.AddWithValue("@kod", kodRozliczeniowyToolStripTextBox.Text.Trim());
            }

            if (rokToolStripTextBox.Text.Trim() != "")
            {
                int i;
                if (!Int32.TryParse(rokToolStripTextBox.Text.Trim(), out i))
                {
                    MessageBox.Show("Podaj prawidłowy rok.");
                    return;
                }

                command.CommandText += " AND YEAR(DATA_SPRZEDAZY)=@rok";
                command.Parameters.AddWithValue("@rok", rokToolStripTextBox.Text.Trim());
            }

            if (miesiacToolStripTextBox.Text.Trim() != "")
            {
                int i;
                if (!Int32.TryParse(miesiacToolStripTextBox.Text.Trim(), out i))
                {
                    MessageBox.Show("Podaj prawidłowy miesiąc.");
                    return;
                }

                command.CommandText += " AND MONTH(DATA_SPRZEDAZY)=@miesiac";
                command.Parameters.AddWithValue("@miesiac", miesiacToolStripTextBox.Text.Trim());
            }

            if (agencjaToolStripComboBox.Text.Trim() != "")
            {
                command.CommandText += " AND Agencja=@agencja";
                command.Parameters.AddWithValue("@agencja", agencjaToolStripComboBox.Text.Trim());
            }
            
            if (((CheckBox)((ToolStripControlHost)wyszukajToolStrip.Items["zalegleCheckBox"]).Control).CheckState == CheckState.Checked)
            {
                command.CommandText += " AND (BRUTTO - suma_zaplat) > 0";
            }
            else if (((CheckBox)((ToolStripControlHost)wyszukajToolStrip.Items["zalegleCheckBox"]).Control).CheckState == CheckState.Unchecked)
            {
                command.CommandText += " AND (BRUTTO - suma_zaplat) <= 0 ";
            }

            if (((ComboBox)grzbietToolStripComboBox.Control).SelectedItem != null)
            {
                command.CommandText += " AND (GRZBIET = @grzbiet) ";
                command.Parameters.AddWithValue("@grzbiet", (((BoundItem)(grzbietToolStripComboBox.SelectedItem)).IDEvalue1));
            }

            if (tytulToolStripComboBox.SelectedItem != null)
            {
                command.CommandText += " AND (ID_TYTUL= @tytul) ";
                command.Parameters.AddWithValue("@tytul", ((BoundItem)tytulToolStripComboBox.SelectedItem).IDEvalue1);
            }

            //MessageBox.Show(command.CommandText + nrFakturyToolStripTextBox.Text);
            sqlDataAdapter.SelectCommand = command;

            this.Cursor = Cursors.WaitCursor;

            try
            {
                int t = sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFaktur);
                commandExecuted = true;
                //MessageBox.Show(t.ToString());
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);
            }

            treeView1.CollapseAll();
            treeView1.SelectedNode = null;

            this.Cursor = Cursors.Default;
        }

        private void fakturyDataGridView_Leave(object sender, EventArgs e)
        {
            fakturyDataGridView.EndEdit();
        }

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

        /// <summary>
        /// Podsumowuje brutto i zaplate z wyswietlonych faktur
        /// </summary>
        private void podsumuj() {

            if (zestawienieFakturBindingSource.List.Count != 0)
            {
            decimal brutto = Convert.ToDecimal(rEKLAMADataSet.ZestawienieFaktur.Compute("Sum(BRUTTO)",""));
            decimal netto = Convert.ToDecimal(rEKLAMADataSet.ZestawienieFaktur.Compute("Sum(netto)", ""));

            bruttoTextBox.Text = String.Format("{0:C}", brutto);
            nettoTextBox.Text = String.Format("{0:C}", netto);
            }
            else
            {
            bruttoTextBox.Clear();
            nettoTextBox.Clear();            
            }
        }

        private void zestawienieFakturBindingSource_ListChanged(object sender, ListChangedEventArgs e)
        {          
           podsumuj();
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            PrintDGV.Print_DataGridView(fakturyDataGridView,50);
        }

        private void wyczyscPolaToolStripButton_Click(object sender, EventArgs e)
        {
            rEKLAMADataSet.ZestawienieFaktur.Clear();

            nrFakturyToolStripTextBox.Clear();
            kodRozliczeniowyToolStripTextBox.Clear();
            rokToolStripTextBox.Clear();
            miesiacToolStripTextBox.Clear();
            agencjaToolStripComboBox.SelectedIndex = -1;
            agencjaToolStripComboBox.Text = "";
            ((CheckBox)((ToolStripControlHost)wyszukajToolStrip.Items["zalegleCheckBox"]).Control).CheckState = CheckState.Indeterminate;
            grzbietToolStripComboBox.SelectedIndex = -1;
            grzbietToolStripComboBox.Text = "";

            tytulToolStripComboBox.SelectedIndex = -1;
        }

        private void wplataToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturBindingSource.Current;
                REKLAMADataSet.ZestawienieFakturRow fakt =
                    (REKLAMADataSet.ZestawienieFakturRow)row.Row;

                if (!fakt.IsreklamaIdNull())
                {
                    PaymentForm p = new PaymentForm(fakt.reklamaId);
                    p.ShowDialog();
                }
            }
        }

        private void podgladToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturBindingSource.Current;
                 
                if (row["id_faktury"] != DBNull.Value)
                {
                    this.Cursor = Cursors.WaitCursor;

                    int idFaktury = Convert.ToInt32(row["id_faktury"]);
               
                    FactureViewer fv = new FactureViewer(idFaktury, true, 0);
                    fv.ShowDialog();
               
                    this.Cursor = Cursors.Default;                    
                }
            }
        }

        /// <summary>
        /// Przechodzi do okna KLIENCI i wyświetla dane klienta związanego z  fakturą.
        /// </summary>
        private void klientToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturBindingSource.Current;

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

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

                this.Hide();

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

            }   
        }

        private void FacturesForm_Shown(object sender, EventArgs e)
        {
            if (User.getUser().St_kierownik)
            {
                agencjaToolStripComboBox.Text = User.getUser().SymbolAgencji;
            }
            else if (User.getUser().St_handlowiec | User.getUser().St_subhandlowiec)
            {
                kodRozliczeniowyToolStripTextBox.Text = User.getUser().Kod_agenta;
            }            
        }

        private void excelToolStripButton_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            ExcelHandler ex = new ExcelHandler();
            ex.exportToExcel(fakturyDataGridView);

            this.Cursor = Cursors.Default;
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturBindingSource.Current;
                REKLAMADataSet.ZestawienieFakturRow fakt =
                    (REKLAMADataSet.ZestawienieFakturRow)row.Row;

                if (!fakt.IsidWplatyNull())
                {
                    PaymentForm2 p = new PaymentForm2(fakt.idWplaty, fakt.ID_FAKTURY);
                    p.ShowDialog();
                }
            }
        }
    }
}