using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Reportage_amministratore : System.Web.UI.Page
{
    string DataInizio = string.Empty;
    string DataFine = string.Empty;
    string idMarchio = string.Empty;
    string idCliente = string.Empty;
    string idSede = string.Empty;
    string idCollaboratore = string.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {
        if ((Session.Contents.Count == 0) || (Session["User"] == null))
        {
            Response.Redirect("login.aspx");
        }
        else
        {
            string sql = string.Empty;
            OleDbCommand cmd;
            OleDbDataReader dr;

            string strConn = "E:/Reportage/mdb-database/Reportage.mdb";
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strConn);

            conn.Open();

            if (!Page.IsPostBack)
            {
                ContentPlaceHolder mPage = (ContentPlaceHolder)this.Page.Master.FindControl("ContentPlaceHolder1");

                utente sUtente = (utente)Session["User"];


                sql = sUtente.VisiteSQL(strConn);

                cmd = new OleDbCommand(sql, conn);
                dr = cmd.ExecuteReader();

                HtmlGenericControl div = new HtmlGenericControl("div");
                div.ID = "whois";
                //div.InnerHtml += "<span class='info'>Sede: <b>" + sUtente.SedeNome + ", " + sUtente.SedeProvincia + "</b></span>";

                if (!dr.HasRows)
                {
                    HtmlGenericControl span = new HtmlGenericControl("span");
                    span.InnerHtml = "Al momento non ci sono visite";
                    visite.Controls.Add(span);
                }
                else
                {
                    int i = 1;

                    HtmlGenericControl tab = new HtmlGenericControl("table");
                    tab.Attributes.Add("id", "mit");
                    tab.Attributes.Add("style", "width: 100%;");
                    tab.InnerHtml += "<thead id=\"mit\"><tr><th class=\"n\">N.</th><th>Data visita</th><th>Marchio</th><th>Cliente</th><th>Sede</th><th>Creata da</th></thead>";
                    tab.InnerHtml += "<tbody id=\"mit\">";
                    while (dr.Read())
                    {
                        visita visita = new visita(conn, dr["id_visita"].ToString());
                        tab.InnerHtml += "<tr>";
                        tab.InnerHtml += "<td align=\"center\"><b>" + i.ToString() + "</b></td>";
                        string data = visita.DataVisita;
                        DateTime data2 = DateTime.Parse(data);
                        tab.InnerHtml += "<td align=\"center\"><a href='visita.aspx?id=" + visita.ID + "'><b>" + data2.ToString("D") + "</b></a></td>";
                        tab.InnerHtml += "<td align=\"center\">" + visita.Marchio + "</td>";
                        tab.InnerHtml += "<td align=\"center\">" + visita.Cliente + "</td>";
                        tab.InnerHtml += "<td align=\"center\">" + visita.Sede + ", " + visita.SedeProvincia + "</td>";
                        tab.InnerHtml += "<td align=\"center\"><i>" + visita.Utente + "</i></td>";
                        tab.InnerHtml += "</tr>";

                        i++; // incremento numero 
                    }
                    tab.InnerHtml += "</tbody>";

                    visite.Controls.Add(tab);
                    visite.Controls.Add(div);
                }
            }


                sql = "SELECT * FROM marchi ORDER BY nome";

                cmd = new OleDbCommand(sql, conn);
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    marchio.Items.Add("Tutti");
                    while (dr.Read())
                    {
                        ListItem marchi = new ListItem();
                        marchi.Text = dr["nome"].ToString();
                        marchi.Value = dr["id_marchio"].ToString();
                        marchio.Items.Add(marchi);
                    }
                }
                else
                {
                    marchio.Enabled = false;
                }
                sql = "SELECT * FROM clienti ORDER BY nome";

                cmd = new OleDbCommand(sql, conn);
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    cliente.Items.Add("Tutti");
                    while (dr.Read())
                    {
                        ListItem clienti = new ListItem();
                        clienti.Text = dr["nome"].ToString();
                        clienti.Value = dr["id_cliente"].ToString();
                        cliente.Items.Add(clienti);
                    }
                }
                else
                {
                    cliente.Enabled = false;
                }

                sql = "SELECT * FROM sedi ORDER BY nome";

                cmd = new OleDbCommand(sql, conn);
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    sede.Items.Add("Tutti");
                    while (dr.Read())
                    {
                        ListItem sedi = new ListItem();
                        sedi.Text = dr["nome"].ToString() + ", " + dr["provincia"].ToString();
                        sedi.Value = dr["id_sede"].ToString();
                        sede.Items.Add(sedi);
                    }
                }
                else
                {
                    sede.Enabled = false;
                }
                sql = "SELECT * FROM utenti WHERE tipo=1 ORDER BY user";

                cmd = new OleDbCommand(sql, conn);
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    collaboratore.Items.Add("Tutti");
                    while (dr.Read())
                    {
                        ListItem collaboratori = new ListItem();
                        collaboratori.Text = dr["user"].ToString();
                        collaboratori.Value = dr["id_utente"].ToString();
                        collaboratore.Items.Add(collaboratori);
                    }
                }
                else
                {
                    collaboratore.Enabled = false;
                }

                if (Page.IsPostBack)
                {
                    DataInizio = data_inizio.Value;
                    DataFine = data_fine.Value;
                    idMarchio = marchio.SelectedValue;
                    idCliente = cliente.SelectedValue;
                    idSede = sede.SelectedValue;
                    idCollaboratore = collaboratore.SelectedValue;
                }
                dr.Close();
        }
    }
    protected void button_query_ServerClick(object sender, EventArgs e)
    {

        string strConn = "E:/Reportage/mdb-database/Reportage.mdb";
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strConn);

        conn.Open();

        string sql = string.Empty;
        sql = "SELECT visite.id_visita, visite.data_inserimento, visite.data_visita, utenti.id_utente, utenti.user, marchi.id_marchio, marchi.nome, clienti.id_cliente, clienti.nome, sedi.id_sede, sedi.nome, sedi.provincia, visite.notes ";
        sql += "FROM (((visite ";
        sql += "INNER JOIN utenti ON visite.id_utente = utenti.id_utente) ";
        sql += "INNER JOIN sedi ON visite.id_sede = sedi.id_sede) ";
        sql += "INNER JOIN clienti ON visite.id_cliente = clienti.id_cliente) ";
        sql += "INNER JOIN marchi ON visite.id_marchio = marchi.id_marchio ";
        sql += "WHERE 1=1 ";
        if ((DataInizio != "") && (DataFine != ""))
        {
            sql += " AND (data_visita BETWEEN #" + DataInizio + "# AND #" + DataFine + "#) ";
        }
        if ((idMarchio != "Tutti") && (idMarchio != ""))
        {
            sql += "AND marchi.id_marchio=" + idMarchio + " ";
        }
        if ((idCliente != "Tutti") && (idCliente != ""))
        {
            sql += "AND clienti.id_cliente=" + idCliente + " ";
        }
        if ((idSede != "Tutti") && (idSede != ""))
        {
            sql += "AND sedi.id_sede=" + idSede + " ";
        }
        if ((idCollaboratore != "Tutti") && (idCollaboratore != ""))
        {
            sql += "AND utenti.id_utente=" + idCollaboratore + " ";
        }

        OleDbCommand cmd = new OleDbCommand(sql, conn);
        OleDbDataReader dr = cmd.ExecuteReader();

        HtmlGenericControl div = new HtmlGenericControl("div");
        div.ID = "whois";
        //div.InnerHtml += "<span class='info'>Sede: <b>" + sUtente.SedeNome + ", " + sUtente.SedeProvincia + "</b></span>";

        if (!dr.HasRows)
        {
            HtmlGenericControl span = new HtmlGenericControl("span");
            span.InnerHtml = "Nessun risultato trovato.";
            visite.Controls.Add(span);
        }
        else
        {
            int i = 1;

            HtmlGenericControl tab = new HtmlGenericControl("table");
            tab.Attributes.Add("id", "mit");
            tab.Attributes.Add("style", "width: 100%;");
            tab.InnerHtml += "<thead id=\"mit\"><tr><th class=\"n\">N.</th><th>Data visita</th><th>Marchio</th><th>Cliente</th><th>Sede</th><th>Creata da</th></thead>";
            tab.InnerHtml += "<tbody id=\"mit\">";
            while (dr.Read())
            {
                visita visita = new visita(conn, dr["id_visita"].ToString());
                tab.InnerHtml += "<tr>";
                tab.InnerHtml += "<td align=\"center\"><b>" + i.ToString() + "</b></td>";
                string data = visita.DataVisita;
                DateTime data2 = DateTime.Parse(data);
                tab.InnerHtml += "<td align=\"center\"><a href='visita.aspx?id=" + visita.ID + "'><b>" + data2.ToString("D") + "</b></a></td>";
                tab.InnerHtml += "<td align=\"center\">" + visita.Marchio + "</td>";
                tab.InnerHtml += "<td align=\"center\">" + visita.Cliente + "</td>";
                tab.InnerHtml += "<td align=\"center\">" + visita.Sede + ", " + visita.SedeProvincia + "</td>";
                tab.InnerHtml += "<td align=\"center\"><i>" + visita.Utente + "</i></td>";
                tab.InnerHtml += "</tr>";

                i++; // incremento numero 
            }
            tab.InnerHtml += "</tbody>";

            visite.Controls.Add(tab);
            visite.Controls.Add(div);
        }

    }
}