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);
}
}
