Beispiel #1
0
        public ActionResult ReporteAvance()
        {
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnSimple"].ConnectionString))
            {
                var db      = new edayRoomEntities();
                var metrics = db.GetGeneralMetricsForReport().First();


                var package = new ExcelPackage();



                package.Workbook.Worksheets.Add("E-day");
                package.Workbook.Worksheets.Add("Cruce");
                package.Workbook.Worksheets.Add("QC");
                package.Workbook.Worksheets.Add("Manuelita");

                var edayWorksheet = package.Workbook.Worksheets[1];

                edayWorksheet.Column(1).Width = 20;
                edayWorksheet.Column(2).Width = 43;
                edayWorksheet.Column(3).Width = 10;
                edayWorksheet.Column(4).Width = 5;

                edayWorksheet.Column(5).Width = 43;
                edayWorksheet.Column(6).Width = 10;

                edayWorksheet.Cells["B3"].Value           = "Reporte por Hora";
                edayWorksheet.Cells["B3"].Style.Font.Bold = true;
                edayWorksheet.Cells["B3"].Style.Font.Size = 18;

                edayWorksheet.Cells["A1"].Value = DateTime.Now.ToString("dd/MM/yyyy hh:mm tt");

                edayWorksheet.Cells["B4"].Value           = "Sala Situacional Integral 14-A";
                edayWorksheet.Cells["B4"].Style.Font.Bold = true;
                edayWorksheet.Cells["B4"].Style.Font.Size = 18;

                edayWorksheet.Cells["B6"].Value           = "Indicadores generales";
                edayWorksheet.Cells["B6"].Style.Font.Bold = true;
                edayWorksheet.Cells["B6"].Style.Font.Size = 14;

                edayWorksheet.Cells["B8"].Value = "Porcentaje de contactabilidad";
                edayWorksheet.Cells["C8"].Value = Math.Round((decimal)metrics.mesasContactadas2hr, 2) + "%";
                edayWorksheet.Cells["B9"].Value = "Variación vs reporte previo";
                edayWorksheet.Cells["C9"].Value = "xx%";

                edayWorksheet.Cells["B11"].Value = "Mesas abiertas (sobre muestra)";
                edayWorksheet.Cells["C11"].Value = Math.Round((decimal)metrics.mesasAbiertas, 2) + "%";

                edayWorksheet.Cells["B13"].Value = "Votantes contabilizados";
                edayWorksheet.Cells["C13"].Value = metrics.votantesContabilizados + "";
                edayWorksheet.Cells["B14"].Value = "Variación vs reporte previo";
                edayWorksheet.Cells["C14"].Value = "xx%";

                edayWorksheet.Cells["B16"].Value = "Proyección de votantes nacional";
                edayWorksheet.Cells["C16"].Value = metrics.proyeccionNacional + "";
                edayWorksheet.Cells["C16"].Style.Numberformat.Format = "#,#0.0";
                edayWorksheet.Cells["B17"].Value = "Variación vs reporte previo";
                edayWorksheet.Cells["C17"].Value = "xx%";


                edayWorksheet.Cells["B19"].Value = "Pareto de participación por tendencia";
                edayWorksheet.Cells["B20"].Value = "Centros opositores";
                edayWorksheet.Cells["C20"].Value = metrics.capriles + "%";
                edayWorksheet.Cells["B21"].Value = "Centros chavistas";
                edayWorksheet.Cells["C21"].Value = metrics.chavista + "%";
                edayWorksheet.Cells["B22"].Value = "Centros ni-ni";
                edayWorksheet.Cells["C22"].Value = metrics.nini + "%";

                edayWorksheet.Cells["B26"].Value           = "Participación y Proyecciones (por estado)";
                edayWorksheet.Cells["B26"].Style.Font.Bold = true;
                edayWorksheet.Cells["B26"].Style.Font.Size = 14;


                //Inicio del calculo de Participacion y proyecciones
                var ugs = db.Centroes.Select(c => c.unidadGeografica1).Distinct();

                bool   even = false;
                string colA, colB;
                int    row = 28;
                foreach (var u in ugs)
                {
                    if (even)
                    {
                        colA = "E";
                        colB = "F";
                    }
                    else
                    {
                        colA = "B";
                        colB = "C";
                    }
                    even = !even;
                    var dataUG = db.GetMetricsReportForUG(u).First();

                    edayWorksheet.Cells[colA + row].Value           = u;
                    edayWorksheet.Cells[colA + row].Style.Font.Bold = true;
                    edayWorksheet.Cells[colA + row].Style.Font.Size = 12;

                    edayWorksheet.Cells[colA + (row + 2)].Value = "Mesas a contactar";
                    edayWorksheet.Cells[colB + (row + 2)].Value = dataUG.mesas;

                    edayWorksheet.Cells[colA + (row + 3)].Value = "Mesas contactadas";
                    edayWorksheet.Cells[colB + (row + 3)].Value = dataUG.mesasContactadas;

                    edayWorksheet.Cells[colA + (row + 5)].Value = "Votantes cuantificados";
                    edayWorksheet.Cells[colB + (row + 5)].Value = dataUG.votantesCuantificados;
                    edayWorksheet.Cells[colA + (row + 6)].Value = "Proyección de votantes regional";
                    edayWorksheet.Cells[colB + (row + 6)].Value = dataUG.proyeccionRegional;
                    edayWorksheet.Cells[colA + (row + 7)].Value = "Crecimiento vs reporte previo";
                    edayWorksheet.Cells[colB + (row + 7)].Value = "xx%";

                    edayWorksheet.Cells[colA + (row + 9)].Value  = "Pareto de participación por tendencia";
                    edayWorksheet.Cells[colA + (row + 10)].Value = "Centros opositores";
                    edayWorksheet.Cells[colB + (row + 10)].Value = dataUG.capriles;
                    edayWorksheet.Cells[colA + (row + 11)].Value = "Centros chavistas";
                    edayWorksheet.Cells[colB + (row + 11)].Value = dataUG.chavista;
                    edayWorksheet.Cells[colA + (row + 12)].Value = "Centros ni-ni";
                    edayWorksheet.Cells[colB + (row + 12)].Value = dataUG.nini;

                    edayWorksheet.Cells[colA + (row + 14)].Value = "Proyección de participación al cierre";
                    edayWorksheet.Cells[colB + (row + 14)].Value = dataUG.proyeccionFin;
                    edayWorksheet.Cells[colA + (row + 15)].Value = "Referencia 7O";
                    edayWorksheet.Cells[colB + (row + 15)].Value = "xx%";
                    edayWorksheet.Cells[colA + (row + 16)].Value = "Referencia 2007-2010";
                    edayWorksheet.Cells[colB + (row + 16)].Value = "xx%";
                    edayWorksheet.Cells[colA + (row + 17)].Value = "Participación óptima";
                    edayWorksheet.Cells[colB + (row + 17)].Value = "xx%";



                    if (!even)
                    {
                        row += 19;
                    }
                }

                var stream = new MemoryStream();
                package.SaveAs(stream);

                string       fileName    = "reporte" + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss") + ".xlsx";
                const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                stream.Position = 0;
                return(File(stream, contentType, fileName));
            }
        }