﻿﻿using System;
using System.Drawing;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Odbc;
using System.Data;
using Telerik.Web.UI;
using System.Configuration;
using System.IO;
using Telerik.Web.UI.Upload;
using InfoSoftGlobal;

public partial class _Lax: System.Web.UI.Page 
{
    protected string ODBC = "Dsn=Chat24Miami";
    protected string ID_ODBC;
    static DateTime FechaInicio = DateTime.Today;
    static DateTime FechaFin = DateTime.Today;
    static DateTime FechaFin1 = DateTime.Today;

    protected void Page_Load(object sender, EventArgs e)
    {

        //////////////////////////
        // Finalizo la sesion?
        //////////////////////////
        if (Convert.ToString(Session["user"]) == "")
        {
            Session.Abandon();
            Response.Redirect("Login.aspx");
        }

        FilterMenu_To_Spanish();
    
        RadTreeNode myNode = (RadTreeNode)Tree.FindNodeByUrl(Request.Url.PathAndQuery);
        if (myNode != null )
        {
            myNode.ExpandParentNodes();
            myNode.Selected = true;
        }

        myNode = (RadTreeNode)Tree.FindNodeByText("Por Programas");
        if (myNode != null && Convert.ToInt32(Session["admin"]) == 2 )
        {
            myNode.Visible = false;
        }

    
        if (Page.IsPostBack == false)
        {
            Leer_Resumen();
        }
    }

    override protected void OnInit(EventArgs e)
    {
        this.Load += new System.EventHandler(this.Page_Load);
        base.OnInit(e);
    }

    protected void FilterMenu_To_Spanish()
    {
    }

    protected void Procesos_ItemCreated(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridPagerItem)
        {
            Label lblPageSize = (Label)e.Item.FindControl("ChangePageSizeLabel");
            RadComboBox PageSize = (RadComboBox)e.Item.FindControl("PageSizeComboBox");
            lblPageSize.Visible = false;
            PageSize.Visible = false;
        }
    }

    protected void Menu_ItemClick(object sender, RadMenuEventArgs e)
    {
    }

    protected void Procesos_ItemCommand(object source, GridCommandEventArgs e)
    {
    }

    protected void Procesos_NeedDataSource(object source,
           Telerik.Web.UI.GridNeedDataSourceEventArgs e)
    {
    }

    protected void Procesos_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
    {
    }

    ////////////////////////////////////////////////////////////////////////////////////////
    /// Share
    ////////////////////////////////////////////////////////////////////////////////////////

    public string ChartShare()
    {
#if NADA
        //In this example, we show how to connect FusionCharts to a database.
        //xmlData will be used to store the entire XML document generated

        int i = 0;
        string xmlData;
        string Query = "";
        int cliente = Convert.ToInt32(Session["cliente"]);

        //Generate the chart element
        xmlData = "<chart showFCMenuItem='0' shownames='1' showvalues='1' showSum='1' decimals='2' formatNumberScale='0' enableSmartLabels='1'  showPercentageValues='1' showPercentInToolTip='1' pieSliceDepth='20' baseFontSize='9' use3DLighting='1' captionPadding='30' showLegend='1' numberSufix='' skipOverlapLabels='1' showToolTip='0' showBorder='0' borderColor='000000' ";
        //      xmlData += "paletteColors='B53E3E,FCCC00' bgColor='EDEEEF' ";
        xmlData += "bgColor='CACACA' ";
        xmlData += "caption='Votaciones' useEllipsesWhenOverflow='1' manageLabelOverflow='1' >";
        string consorcio = Convert.ToString(Session["partner"]);

        // imageSave='1' imageSaveURL='FusionChartsSave.aspx'

        Query = "select sql_no_cache nombre, descripcion, votaciones_totales.id_accion as accion, votaciones_totales.Nro_Accion as programa, cast( CONCAT('660',opcion) as decimal ) as telefono, votos, (votos + votos_olleros ) as totales from votaciones_totales left join votaciones_habilitar ON votaciones_habilitar.ID_Accion = votaciones_totales.ID_Accion left join votaciones_programas ON ( votaciones_programas.Accion = votaciones_totales.ID_Accion and votaciones_programas.Programa = votaciones_totales.Nro_Accion and votaciones_programas.Participante = votaciones_totales.participante ) left join votaciones_participantes on votaciones_participantes.participante = votaciones_totales.participante  where votaciones_totales.ID_Accion=" + Accion + " and telefono <> 0 and habilitada >= 0 and votaciones_totales.participante > 0 and ( votos > 0 or votos_olleros > 0 ) and votaciones_totales.Nro_Accion=" + NroAccion;

        OdbcConnection connection = new OdbcConnection(ODBC);
        OdbcCommand command = new OdbcCommand(Query, connection);
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);

        foreach (DataRow row in table.Rows)
        {
            string value1 = row["totales"].ToString();
            string value2 = row["nombre"].ToString();
            string value3 = value1.Replace(',', '.');

            xmlData += "<set label='" + value2 + "' value='" + value3 + "' />";
        }

        xmlData += "<styles>";
        xmlData += "<definition>";
        xmlData += "<style name='CaptionFont' type='font' face='segoe ui' size='13' underline='1' bold='0' color='444444'/>";
        xmlData += "<style name='subtitleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='0' color='444444' />";
        xmlData += "<style name='LabelsFont' type='font' face='arial' size='8' underline='0' bold='0' color='444444'/>";
        xmlData += "</definition>";
        xmlData += "<application>";
        xmlData += "<apply toObject='Caption' styles='CaptionFont' />";
        xmlData += "<apply toObject='subCaption' styles='subtitleFont' />";
        xmlData += "<apply toObject='DataValues' styles='LabelsFont' />";
        xmlData += "</application>";
        xmlData += "</styles>";
        xmlData += "</chart>";
        connection.Close();

//        SharePie.Visible = true;

        //Create the chart - Pie 3D Chart with data from xmlData
        //          return FusionCharts.RenderChart("./Charts/StackedColumn3D.swf?XMLLoadingText=", "", xmlData, "Llamados", "800", "400", false, false);
        //            return FusionCharts.RenderChart("./Charts/MSColumn3D.swf?ChartNoDataText=No hay datos para graficar.&PBarLoadingText=Cargando Grafico. Por favor espere...&XMLLoadingText=", "", xmlData, "Llamados", "900", "400", false, false);
//        FusionCharts.SetRenderer("javascript");
        return FusionCharts.RenderChart("Charts/Doughnut2D.swf?ChartNoDataText=No hay datos para graficar.&PBarLoadingText=Cargando Grafico. Por favor espere...&XMLLoadingText=", "", xmlData, "Total", "808", "330", false, false);
#endif
        return "";
    }


    ////////////////////////////////////////////////////////////////////////////////////////////////////
    // Llamados
    ////////////////////////////////////////////////////////////////////////////////////////////////////

    public string Llamados()
    {
        string xmlData;
        string Query = "";
        string Series1 = "";


        string fecha1 = FechaInicio.Year + "/" + FechaInicio.Month.ToString("00") + "/" + FechaInicio.Day.ToString("00");
        string fecha2 = FechaFin.Year + "/" + FechaFin.Month.ToString("00") + "/" + FechaFin.Day.ToString("00");

        //Generate the chart element
        xmlData = "<chart showFCMenuItem='0' palette='1' shownames='1' showvalues='0' decimals='0' formatNumberScale='0' enableSmartLabels='1' yAxisName='Llamados' rotateNames='1' labelDisplay='ROTATE' slantLabels='1' showBorder='0' ";
        xmlData += "bgColor='CACACA' overlapColumns='1' ";
        xmlData += "caption='Llamados en el Periodo'";
        xmlData += " >";

        Query = "select sql_no_cache count(id) as llamados, date(calldate) as fecha from cdr where date(calldate) >= '" + fecha1 + "' and date(calldate) <='" + fecha2 + "' and dst = '13233161111' group by date(calldate) order by calldate";

        OdbcConnection connection = new OdbcConnection(ODBC);
        OdbcCommand command = new OdbcCommand(Query, connection);
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);

        foreach (DataRow row in table.Rows)
        {
            DateTime Fecha = Convert.ToDateTime(row["fecha"]);

            string value1 = row["llamados"].ToString();
            Series1 = Convert.ToDouble(value1).ToString("N2").Replace(',', '.');
            xmlData += "<set label='" + Fecha.Day + "/" + Fecha.Month + "' value='" + Series1 + "' />";
        }

        xmlData += "<trendlines>";
        xmlData += "<line startValue='42000' color='91C728' displayValue='Target'/>";
        xmlData += "</trendlines>";

        connection.Close();
        xmlData += "<styles>";
        xmlData += "<definition>";
        xmlData += "<style name='titleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='1' color='444444' />";
        xmlData += "<style name='subtitleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='0' color='444444' />";
        xmlData += "<style name='myBevel' type='Bevel' angle='0' />";
        xmlData += "<style name='myShadow' type='Shadow' distance='5' angle='45'/>";
        xmlData += "</definition>";
        xmlData += "<application>";
        xmlData += "<apply toObject='Caption' styles='titleFont' />";
        xmlData += "<apply toObject='subCaption' styles='subtitleFont' />";
        xmlData += "<apply toObject='Canvas' styles='myBevel,myShadow' />";
        xmlData += "</application>";
        xmlData += "</styles>";
        xmlData += "</chart>";

        return FusionCharts.RenderChart("./Charts/Column3D.swf?ChartNoDataText=No hay datos para graficar.&PBarLoadingText=Cargando Grafico. Por favor espere...&XMLLoadingText=", "", xmlData, "Grafico_Llamados", "808", "280", false, false);
    }


    ////////////////////////////////////////////////////////////////////////////////////////////////////
    // Mesnajes
    ////////////////////////////////////////////////////////////////////////////////////////////////////

    public string Mensajes()
    {
        string xmlData;
        string Query = "";
        string Series1 = "";

        string fecha1 = FechaInicio.Year + "/" + FechaInicio.Month.ToString("00") + "/" + FechaInicio.Day.ToString("00");
        string fecha2 = FechaFin.Year + "/" + FechaFin.Month.ToString("00") + "/" + FechaFin.Day.ToString("00");

        //Generate the chart element
        xmlData = "<chart showFCMenuItem='0' palette='1' shownames='1' showvalues='0' decimals='0' formatNumberScale='0' enableSmartLabels='1' yAxisName='Mensajes' rotateNames='1' labelDisplay='ROTATE' slantLabels='1' showBorder='0' ";
        xmlData += "bgColor='CACACA' overlapColumns='1' ";
        xmlData += "caption='Totales de Mensajes'";
        xmlData += " >";

        Query = "select mensajes, fecha from chat_ivr_estadisticas where fecha >= '" + fecha1 + "' and fecha <='" + fecha2 + "' and ( dnis='3161111' or dnis='3233161111' ) order by fecha";

        OdbcConnection connection = new OdbcConnection(ODBC);
        OdbcCommand command = new OdbcCommand(Query, connection);
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);


        foreach (DataRow row in table.Rows)
        {
            DateTime Fecha = Convert.ToDateTime(row["fecha"]);
            string value1 = row["mensajes"].ToString();
            Series1 = Convert.ToDouble(value1).ToString("N2").Replace(',', '.');
            xmlData += "<set label='" + Fecha.Day + "/" + Fecha.Month + "' value='" + Series1 + "' />";
        }

        xmlData += "<trendlines>";
        xmlData += "<line startValue='42000' color='91C728' displayValue='Target'/>";
        xmlData += "</trendlines>";

        connection.Close();
        xmlData += "<styles>";
        xmlData += "<definition>";
        xmlData += "<style name='titleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='1' color='444444' />";
        xmlData += "<style name='subtitleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='0' color='444444' />";
        xmlData += "<style name='myBevel' type='Bevel' angle='0' />";
        xmlData += "<style name='myShadow' type='Shadow' distance='5' angle='45'/>";
        xmlData += "</definition>";
        xmlData += "<application>";
        xmlData += "<apply toObject='Caption' styles='titleFont' />";
        xmlData += "<apply toObject='subCaption' styles='subtitleFont' />";
        xmlData += "<apply toObject='Canvas' styles='myBevel,myShadow' />";
        xmlData += "</application>";
        xmlData += "</styles>";
        xmlData += "</chart>";

//        Grafico_Mensajes.Visible = true;

        return FusionCharts.RenderChart("./Charts/Column3D.swf?ChartNoDataText=No hay datos para graficar.&PBarLoadingText=Cargando Grafico. Por favor espere...&XMLLoadingText=", "", xmlData, "Grafico_Mensajes", "808", "280", false, false);
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////////
    // Inscriptos
    ////////////////////////////////////////////////////////////////////////////////////////////////////

    public string Inscriptos()
    {
        string xmlData;
        string Query = "";
        string Series1 = "";
        string Series2 = "";


        string fecha1 = FechaInicio.Year + "/" + FechaInicio.Month.ToString("00") + "/" + FechaInicio.Day.ToString("00");
        string fecha2 = FechaFin.Year + "/" + FechaFin.Month.ToString("00") + "/" + FechaFin.Day.ToString("00");

        //Generate the chart element
        xmlData = "<chart showFCMenuItem='0' palette='1' shownames='1' showvalues='0' decimals='0' formatNumberScale='0' enableSmartLabels='1' yAxisName='Inscriptos' rotateNames='1' labelDisplay='ROTATE' slantLabels='1' showBorder='0' ";
        xmlData += "bgColor='CACACA' overlapColumns='1' ";
        xmlData += "caption='Totales de Inscriptos'";
        xmlData += " >";

        Query = "select count(id) as inscriptos, (sum( case when sexo =  1 then 1 else 0 end )) as hombres, (sum( case when sexo =  2 then 1 else 0 end )) as mujeres, date(Inscripto) as fecha from chat_ivr_usuarios where date(inscripto) >= '" + fecha1 + "' and date(inscripto) <='" + fecha2 + "' and (llamo_al='3161111' or llamo_al='3233161111' ) and estado <> 2 group by date(inscripto) order by inscripto";
//       Query = "select * from estadisticas where fecha >= '" + fecha1 + "' and fecha <='" + fecha2 + "' order by fecha";

        xmlData += "<categories>";
        Series1 = "<dataset seriesName='Hombres' >";
        Series2 = "<dataset seriesName='Mujeres' >";

        /*
        Series3 = "<dataset seriesName='Canal 10' color='9ACC68' >";
        Series4 = "<dataset seriesName='Teledoce' color='4674AC' >";
        Series5 = "<dataset seriesName='Cable' color='DB95DB' >"; */


        OdbcConnection connection = new OdbcConnection(ODBC);
        OdbcCommand command = new OdbcCommand(Query, connection);
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);


        foreach (DataRow row in table.Rows)
        {
            DateTime Fecha = Convert.ToDateTime(row["fecha"]);
            string value1 = row["hombres"].ToString();
            string value2 = row["mujeres"].ToString();

            if (value1 == "") value1 = "0";
            if (value2 == "") value2 = "0";
//            Series1 = Convert.ToDouble(value1).ToString("N2").Replace(',', '.');
            //Series2 = Convert.ToDouble(value2).ToString("N2").Replace(',', '.');
            
            xmlData += "<category label='" + Fecha.Day + "/"  + Fecha.Month + "' />";
            Series1 += "<set value='" + Convert.ToDouble(value1).ToString("N2").Replace(',', '.') + "' />";
            Series2 += "<set value='" + Convert.ToDouble(value2).ToString("N2").Replace(',', '.') + "' />";
        }

        Series1 += "</dataset>";
        Series2 += "</dataset>";
        xmlData += "</categories>";
        xmlData += Series1;
        xmlData += Series2;

        xmlData += "<trendlines>";
        xmlData += "<line startValue='42000' color='91C728' displayValue='Target'/>";
        xmlData += "</trendlines>";

        connection.Close();
        xmlData += "<styles>";
        xmlData += "<definition>";
        xmlData += "<style name='titleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='1' color='444444' />";
        xmlData += "<style name='subtitleFont' type='font' font='segoe ui,Arial' bold='0' size='13' underline='0' color='444444' />";
        xmlData += "<style name='myBevel' type='Bevel' angle='0' />";
        xmlData += "<style name='myShadow' type='Shadow' distance='5' angle='45'/>";
        xmlData += "</definition>";
        xmlData += "<application>";
        xmlData += "<apply toObject='Caption' styles='titleFont' />";
        xmlData += "<apply toObject='subCaption' styles='subtitleFont' />";
        xmlData += "<apply toObject='Canvas' styles='myBevel,myShadow' />";
        xmlData += "</application>";
        xmlData += "</styles>";
        xmlData += "</chart>";

        return FusionCharts.RenderChart("./Charts/StackedColumn3D.swf?XMLLoadingText=", "", xmlData, "Graph_Inscriptos", "808", "280", false, false);
    }

    protected void Visualizar_Command(object sender, CommandEventArgs e)
    {
        Leer_Resumen();
    }


    protected void Fecha_Comienzo()
    {
        switch( Periodo.SelectedIndex )
        {
            case 0:
                FechaInicio = FechaFin1 = DateTime.Today.AddDays(-15);
                FechaFin = DateTime.Today;
                break;
            
            case 1:
                FechaInicio = FechaFin1 = DateTime.Today.AddDays(-30);
                FechaFin = DateTime.Today;
                break;

            case 2:
                FechaInicio = FechaFin1 = Convert.ToDateTime("01/" + DateTime.Today.Month + "/" + DateTime.Today.Year);
                FechaFin = Convert.ToDateTime("31/" + DateTime.Today.Month + "/" + DateTime.Today.Year);
                break;

            case 3:
                FechaInicio = FechaFin1 = Convert.ToDateTime("01/" + DateTime.Today.AddDays(-30).Month + "/" + DateTime.Today.AddDays(-30).Year);
                FechaFin = Convert.ToDateTime("31/" + DateTime.Today.AddDays(-30).Month + "/" + DateTime.Today.AddDays(-30).Year);
                break;

            case 4:
                FechaFin1 = DateTime.Today.AddDays(-36500);
                FechaInicio = DateTime.Today.AddDays(-30);
                FechaFin = DateTime.Today;
                break;
        }
    }


//#if NADA
    protected void Leer_Resumen()
    {
            Fecha_Comienzo();
            string fecha2 = FechaFin.Year + "/" + FechaFin.Month.ToString("00") + "/" + FechaFin.Day.ToString("00");
            string fecha1 = FechaFin1.Year + "/" + FechaFin1.Month.ToString("00") + "/" + FechaFin1.Day.ToString("00");
            string value = "";

//          string Query = "select count(id) as llamados from voip.cdr where date(calldate) >= '" + fecha1 + "' and date(calldate) <='" + fecha2 + "' and dst = '13057401215'";
            string Query = "select sql_no_cache count(id) as llamados from cdr where date(calldate) >= '" + fecha1 + "' and date(calldate) <='" + fecha2 + "' and dst = '13233161111'";


            OdbcConnection connection = new OdbcConnection(ODBC);
            OdbcCommand command = new OdbcCommand(Query, connection);
            OdbcDataAdapter adapter = new OdbcDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
//          connection.Close();

            foreach (DataRow row in table.Rows)
            {
                value = row["llamados"].ToString();
            }

            tLlamados.Text = value;


#if OLD
            Query = "select count(id) as mensajes from chat_ivr_mensajes where date(fecha) >= '" + fecha1 + "' and date(fecha) <='" + fecha2 + "' ";

            OdbcCommand command1 = new OdbcCommand(Query, connection);
            OdbcDataAdapter adapter1 = new OdbcDataAdapter(command1);
            DataTable table1 = new DataTable();
            adapter1.Fill(table1);
            connection.Close();

            foreach (DataRow row in table1.Rows)
            {
                value = row["mensajes"].ToString();
            }

            tMensajes.Text = value;
#endif

            Query = "select count(id) as inscriptos, (sum( case when ( sexo =  1 or sexo = 3 ) then 1 else 0 end )) as hombres, (sum( case when ( sexo =  2 or sexo = 4 ) then 1 else 0 end )) as mujeres, (sum( case when ( ( sexo = 1 or sexo = 3 ) and edad >= 18 and edad < 23 ) then 1 else 0 end )) as edad1, (sum( case when ( ( sexo = 1 or sexo = 3 ) and edad >= 23 and edad < 30 ) then 1 else 0 end )) as edad2, (sum( case when ( ( sexo = 1 or sexo = 3) and edad >= 30 and edad <= 40 ) then 1 else 0 end )) as edad3, (sum( case when ( (sexo = 1 or sexo = 3) and edad > 40 ) then 1 else 0 end )) as edad4, ";
            Query += "(sum( case when ( (sexo = 2 or sexo = 4) and edad >= 18 and edad < 23 ) then 1 else 0 end )) as edad5, (sum( case when ( (sexo = 2 or sexo = 4) and edad >= 23 and edad < 30 ) then 1 else 0 end )) as edad6, (sum( case when ( (sexo = 2 or sexo = 4)  and edad >= 30 and edad <= 40 ) then 1 else 0 end )) as edad7, (sum( case when ( (sexo = 2 or sexo = 4)  and edad > 40 ) then 1 else 0 end )) as edad8 ";
            Query += "from chat_ivr_usuarios where estado <> 2 and date(inscripto) >= '" + fecha1 + "' and date(inscripto) <='" + fecha2 + "' and (llamo_al = '3161111' or llamo_al='3233161111') ";
        
            command = new OdbcCommand(Query, connection);
            adapter = new OdbcDataAdapter(command);
            table = new DataTable();
            adapter.Fill(table);
            connection.Close();

            foreach (DataRow row in table.Rows)
            {
                value = row["hombres"].ToString();
                Hombres.Text = value;

                value = row["mujeres"].ToString();
                Mujeres.Text = value;

                value = row["edad1"].ToString();
                Edad1.Text = value;

                value = row["edad2"].ToString();
                Edad2.Text = value;

                value = row["edad3"].ToString();
                Edad3.Text = value;

                value = row["edad4"].ToString();
                Edad4.Text = value;

                value = row["edad5"].ToString();
                Edad5.Text = value;

                value = row["edad6"].ToString();
                Edad6.Text = value;

                value = row["edad7"].ToString();
                Edad7.Text = value;

                value = row["edad8"].ToString();
                Edad8.Text = value;
            }

        
            Query = "select sql_no_cache sum(Mensajes) as total from chat_ivr_estadisticas where fecha >= '" + fecha1 + "' and fecha <='" + fecha2 + "' and ( dnis='3161111' or dnis='3233161111' ) order by fecha";

            OdbcCommand command1 = new OdbcCommand(Query, connection);
            OdbcDataAdapter adapter1 = new OdbcDataAdapter(command1);
            DataTable table1 = new DataTable();
 
            adapter1.Fill(table1);
            connection.Close();

            foreach (DataRow row in table1.Rows)
            {
                value = row["total"].ToString();
                tMensajes.Text = value;
            }
         

    }
//#endif
}


