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 FacturesFormNEW : Form
    {
        string query = "SELECT top 2000 * from VIEW_ZESTAWIENIE_FAKTUR_NOWE ";

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

        private bool commandExecuted = false;

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

        private FacturesFormNEW(MDIBazaReklam parent)
        {
            InitializeComponent();

            this.MdiParent = parent;

            //podmiana connstringa
            zestawienieFakturNEWTableAdapter.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(10, new ToolStripLabel("nieuregulowane:"));
            wyszukajToolStrip.Items.Insert(11, 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.bindujAgencje(agencjaToolStripComboBox);
         
            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.ZestawienieFakturNEW.Clear();

                                        command.CommandText += " WHERE YEAR(dataWplaty) = @rok AND (suma_zaplat is null OR (CONVERT(int, suma_zaplat * 100) <> CONVERT(int, BRUTTO * 100))) order by dataWplaty";

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

                                        this.Cursor = Cursors.Default;

                                        break;

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

                                        rEKLAMADataSet.ZestawienieFakturNEW.Clear();
                                        command.CommandText += " WHERE YEAR(DATA_SPRZEDAZY) = @rok AND (suma_zaplat is not null AND suma_zaplat<>0) AND (dataWplaty is null) order by DATA_SPRZEDAZY";
                                        command.Parameters.AddWithValue("@rok", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFakturNEW);
                                        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.ZestawienieFakturNEW.Clear();
                                        command.CommandText += " WHERE YEAR([dataWplaty]) = @rok AND MONTH(dataWplaty) = @miesiac AND (suma_zaplat is null OR (CONVERT(int, suma_zaplat * 100) <> CONVERT(int, BRUTTO * 100))) order by dataWplaty";
                                        command.Parameters.AddWithValue("@rok", e.Node.Parent.Name);
                                        command.Parameters.AddWithValue("@miesiac", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFakturNEW);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;
                                        break;

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

                                        rEKLAMADataSet.ZestawienieFakturNEW.Clear();
                                        command.CommandText += " WHERE YEAR(DATA_SPRZEDAZY) = @rok AND MONTH(DATA_SPRZEDAZY) = @miesiac AND (suma_zaplat is not null AND suma_zaplat<>0) AND (dataWplaty is null) order by DATA_SPRZEDAZY";
                                        command.Parameters.AddWithValue("@rok", e.Node.Parent.Name);
                                        command.Parameters.AddWithValue("@miesiac", e.Node.Name);
                                        sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFakturNEW);
                                        commandExecuted = true;
                                        fakturyDataGridView.Refresh();

                                        this.Cursor = Cursors.Default;
                                        break;
                                }
                                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)
        {
            zestawienieFakturNEWBindingSource.CancelEdit();
        }
       
        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.ZestawienieFakturNEW.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 NR_FK like '%' + @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 ";
            }
         
            //MessageBox.Show(command.CommandText + nrFakturyToolStripTextBox.Text);
            sqlDataAdapter.SelectCommand = command;

            this.Cursor = Cursors.WaitCursor;

            try
            {
                int t = sqlDataAdapter.Fill(this.rEKLAMADataSet.ZestawienieFakturNEW);
                commandExecuted = true;
            }
            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 (zestawienieFakturNEWBindingSource.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);
            zaplataTextBox.Text = String.Format("{0:C}", netto);
            }
            else
            {
            bruttoTextBox.Clear();
            zaplataTextBox.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;
        }

        private void podgladToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturNEWBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturNEWBindingSource.Current;
                REKLAMADataSet.ZestawienieFakturNEWRow faktura = (REKLAMADataSet.ZestawienieFakturNEWRow)row.Row;

                this.Cursor = Cursors.WaitCursor;
                FactureViewer fv = new FactureViewer(faktura.ID_FAKTURY);
                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 (zestawienieFakturNEWBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturNEWBindingSource.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 fakturyDataGridView_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            if (e.ListChangedType == ListChangedType.Reset)
            {
                foreach (DataGridViewRow r in fakturyDataGridView.Rows)
                {
                    if (r.Cells[7].Value != DBNull.Value && r.Cells[8].Value != DBNull.Value)
                    {
                        if (Convert.ToDecimal(r.Cells[7].Value) != Convert.ToDecimal(r.Cells[8].Value))
                        {
                            r.DefaultCellStyle.ForeColor = Color.Red;
                        }
                    }
                }
            }
        }

        private void wplata2toolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturNEWBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturNEWBindingSource.Current;
                REKLAMADataSet.ZestawienieFakturNEWRow f = (REKLAMADataSet.ZestawienieFakturNEWRow)row.Row;
                
                PaymentForm2 pf2;

                if (f.IsidWplatyNull())
                {
                    pf2 = new PaymentForm2(0,f.ID_FAKTURY);
                }
                else
                {
                    pf2 = new PaymentForm2(f.idWplaty, f.ID_FAKTURY);
                }

                if (pf2.ShowDialog() == DialogResult.OK)
                {
                    f.idWplaty = pf2.IdWplaty;
                    f.EndEdit();
                    fakturyDataGridView.Refresh();
                }
            }
    
        }

        private void zamowieniaToolStripButton_Click(object sender, EventArgs e)
        {
            if (zestawienieFakturNEWBindingSource.Current != null)
            {
                DataRowView row = (DataRowView)zestawienieFakturNEWBindingSource.Current;
                REKLAMADataSet.ZestawienieFakturNEWRow f = (REKLAMADataSet.ZestawienieFakturNEWRow)row.Row;

                ZamowieniaForm zf = new ZamowieniaForm(f.ID_NABYWCY,f.idZamowienia);
                zf.ShowDialog();
            }
        }
    }
}