public List <BE_Diet> GetDiet()
{
BE_Diet bE_Diet = new BE_Diet();
_database = new MySQLDatabase(connectionString);
return(GetDietGeneral(bE_Diet));
}
public List <BE_Diet> GetDietByName(string name)
{
BE_Diet bE_Diet = new BE_Diet();
bE_Diet.name = name;
_database = new MySQLDatabase(connectionString);
return(GetDietGeneral(bE_Diet));
}
public List <BE_Diet> GetDietById(int dietId)
{
BE_Diet bE_Diet = new BE_Diet();
bE_Diet.dietId = dietId;
_database = new MySQLDatabase(connectionString);
return(GetDietGeneral(bE_Diet));
}
public List <BE_Diet> GetDietGeneral(BE_Diet bE_Diet)
{
BE_Diet _bE_Diet = null;
List <BE_Diet> _lstDiet = new List <BE_Diet>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("_dietId", (bE_Diet.dietId == 0) ? DBNull.Value : (object)bE_Diet.dietId);
parameters.Add("_name", bE_Diet.name);
var rows = _database.QuerySP("sp_getDietGeneral", parameters);
foreach (var row in rows)
{
_bE_Diet = new BE_Diet();
_bE_Diet.dietId = string.IsNullOrEmpty(row["dietId"]) ? 0 : int.Parse(row["dietId"]);
_bE_Diet.name = row["name"];
_bE_Diet.isEnabled = string.IsNullOrEmpty(row["isEnabled"]) ? false : row["isEnabled"].Equals("1") ? true : false;
_lstDiet.Add(_bE_Diet);
}
return(_lstDiet);
}
private List <BE_Diet> GetDietGeneral(BE_Diet bE_Diet)
{
_database = new MySQLDatabase(connectionString);
return(new DA_Diet(_database).GetDietGeneral(bE_Diet));
}
public MemoryStream GetExcelData(int year, int month, int headquartersId, int dietId, string userName)
{
MemoryStream ms = new MemoryStream();
List <ProgramationResumenByWeekOfMonth> listResumenByWeekOfMonth = GetProgramationResumen(year, month, headquartersId, dietId);
BL_Headquarters bL_Headquarters = new BL_Headquarters();
bL_Headquarters.connectionString = connectionString;
var headquartersList = bL_Headquarters.GetHeadquartersById(headquartersId);
BE_Headquarters headquarters = new BE_Headquarters();
if (headquartersList.Count > 0)
{
headquarters = headquartersList[0];
}
BL_Diet bL_Diet = new BL_Diet();
bL_Diet.connectionString = connectionString;
var dietList = bL_Diet.GetDietById(dietId);
BE_Diet diet = new BE_Diet();
if (dietList.Count > 0)
{
diet = dietList[0];
}
using (var p = new ExcelPackage())
{
var styleTitle = p.Workbook.Styles.CreateNamedStyle("styleTitle");
styleTitle.Style.Font.Bold = true;
styleTitle.Style.Border.Left.Style = ExcelBorderStyle.Thin;
styleTitle.Style.Border.Left.Color.SetColor(Color.Crimson);
styleTitle.Style.Border.Top.Style = ExcelBorderStyle.Thin;
styleTitle.Style.Border.Top.Color.SetColor(Color.Crimson);
styleTitle.Style.Border.Right.Style = ExcelBorderStyle.Thin;
styleTitle.Style.Border.Right.Color.SetColor(Color.Crimson);
styleTitle.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
styleTitle.Style.Border.Bottom.Color.SetColor(Color.Crimson);
//Estilo de la cabecera
var styleHeader = p.Workbook.Styles.CreateNamedStyle("styleHeader");
styleHeader.Style.Font.Bold = true;
styleHeader.Style.Fill.PatternType = ExcelFillStyle.Solid;
styleHeader.Style.Fill.BackgroundColor.SetColor(Color.Crimson);
styleHeader.Style.Font.Color.SetColor(Color.White);
//Estilo del contenido
var styleContent = p.Workbook.Styles.CreateNamedStyle("styleContent");
styleContent.Style.Fill.PatternType = ExcelFillStyle.Solid;
styleContent.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
styleContent.Style.Border.Left.Style = ExcelBorderStyle.Thin;
styleContent.Style.Border.Left.Color.SetColor(Color.Crimson);
styleContent.Style.Border.Top.Style = ExcelBorderStyle.Thin;
styleContent.Style.Border.Top.Color.SetColor(Color.Crimson);
styleContent.Style.Border.Right.Style = ExcelBorderStyle.Thin;
styleContent.Style.Border.Right.Color.SetColor(Color.Crimson);
styleContent.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
styleContent.Style.Border.Bottom.Color.SetColor(Color.Crimson);
var ws = p.Workbook.Worksheets.Add("Promagacion");
ws.Cells["B1:F1"].Merge = true;
ws.Cells["B1"].Value = "Polo and Sons";
ws.Cells["B1"].Style.Font.Size = 48;
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["F2"].Value = "Fecha: " + DateTime.Now.ToShortDateString();
ws.Cells["F3"].Value = "Sede: " + userName;
ws.Cells["B4"].Value = "Sede: " + headquarters.name;
ws.Cells["C4"].Value = "Dieta: " + diet.name;
ws.Cells["D4"].Value = "Mes: " + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month));
ws.Cells[4, 2, 4, 4].StyleName = "styleTitle";
ws.Cells[2, 6, 3, 6].StyleName = "styleTitle";
//Contenidos de las tablas
List <List <string[]> > contents = new List <List <string[]> >();
foreach (var resumenByWeekOfMonth in listResumenByWeekOfMonth)
{
List <string[]> content = new List <string[]>();
List <string> contentHeader = new List <string>();
contentHeader.Add("Tiempo de alimentación");
var saturdayHeader = (resumenByWeekOfMonth.WeekOfMonth.SaturdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.SaturdayDate.Day.ToString();
contentHeader.Add("Sabado " + saturdayHeader);
var sundayHeader = (resumenByWeekOfMonth.WeekOfMonth.SundayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.SundayDate.Day.ToString();
contentHeader.Add("Domingo " + sundayHeader);
var mondayHeader = (resumenByWeekOfMonth.WeekOfMonth.MondayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.MondayDate.Day.ToString();
contentHeader.Add("Lunes " + mondayHeader);
var tuesdayHeader = (resumenByWeekOfMonth.WeekOfMonth.TuesdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.TuesdayDate.Day.ToString();
contentHeader.Add("Martes " + tuesdayHeader);
var wednesdayHeader = (resumenByWeekOfMonth.WeekOfMonth.WednesdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.WednesdayDate.Day.ToString();
contentHeader.Add("Miercoles " + wednesdayHeader);
var thursdayHeader = (resumenByWeekOfMonth.WeekOfMonth.ThursdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.ThursdayDate.Day.ToString();
contentHeader.Add("Jueves " + thursdayHeader);
var fridayHeader = (resumenByWeekOfMonth.WeekOfMonth.FridayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.FridayDate.Day.ToString();
contentHeader.Add("Viernes " + fridayHeader);
string[] headerRowStringArray = new string[contentHeader.Count()];
headerRowStringArray = contentHeader.ToArray();
content.Add(headerRowStringArray);
foreach (var resumen in resumenByWeekOfMonth.listProgramation)
{
List <string> contentRow = new List <string>();
contentRow.Add(resumen.feedingTimeName);
StringBuilder saturday = new StringBuilder();
foreach (var r in resumen.saturday.programation)
{
saturday.AppendLine(r.productName);
}
contentRow.Add(saturday.ToString());
StringBuilder sunday = new StringBuilder();
foreach (var r in resumen.sunday.programation)
{
sunday.AppendLine(r.productName);
}
contentRow.Add(sunday.ToString());
StringBuilder monday = new StringBuilder();
foreach (var r in resumen.monday.programation)
{
monday.AppendLine(r.productName);
}
contentRow.Add(monday.ToString());
StringBuilder tuesday = new StringBuilder();
foreach (var r in resumen.tuesday.programation)
{
tuesday.AppendLine(r.productName);
}
contentRow.Add(tuesday.ToString());
StringBuilder wednesday = new StringBuilder();
foreach (var r in resumen.wednesday.programation)
{
wednesday.AppendLine(r.productName);
}
contentRow.Add(wednesday.ToString());
StringBuilder thursday = new StringBuilder();
foreach (var r in resumen.thursday.programation)
{
thursday.AppendLine(r.productName);
}
contentRow.Add(thursday.ToString());
StringBuilder friday = new StringBuilder();
foreach (var r in resumen.friday.programation)
{
friday.AppendLine(r.productName);
}
contentRow.Add(friday.ToString());
string[] contentRowStringArray = new string[contentRow.Count()];
contentRowStringArray = contentRow.ToArray();
content.Add(contentRowStringArray);
}
contents.Add(content);
}
var num = 6;
foreach (var a in contents)
{
//Tabla1
ws.Cells["B" + num + ":I" + num].StyleName = "styleHeader";
ws.Cells["B" + (num + 1) + ":I" + (num + a.Count() - 1)].StyleName = "styleContent";
ws.Cells["B" + num].LoadFromArrays(a);
num += (a.Count() + 1);
}
byte[] b = p.GetAsByteArray();
ms = new MemoryStream(b);
}
return(ms);
}
public string GetPdfData(int year, int month, int headquartersId, int dietId, string userName, List <ProgramationResumenByWeekOfMonth> listResumenByWeekOfMonth)
{
if (listResumenByWeekOfMonth == null)
{
listResumenByWeekOfMonth = GetProgramationResumen(year, month, headquartersId, dietId);
}
BL_Headquarters bL_Headquarters = new BL_Headquarters();
bL_Headquarters.connectionString = connectionString;
var headquartersList = bL_Headquarters.GetHeadquartersById(headquartersId);
BE_Headquarters headquarters = new BE_Headquarters();
if (headquartersList.Count > 0)
{
headquarters = headquartersList[0];
}
BL_Diet bL_Diet = new BL_Diet();
bL_Diet.connectionString = connectionString;
var dietList = bL_Diet.GetDietById(dietId);
BE_Diet diet = new BE_Diet();
if (dietList.Count > 0)
{
diet = dietList[0];
}
StringBuilder html = new StringBuilder();
html.AppendLine("<!DOCTYPE html>");
html.AppendLine("<html lang=\"en\">");
html.AppendLine("<head>");
html.AppendLine("<meta charset=\"UTF - 8\">");
html.AppendLine("<meta name=\"viewport\" content=\"width = device - width, initial - scale = 1.0\">");
html.AppendLine("<meta http-equiv=\"X - UA - Compatible\" content=\"ie = edge\">");
html.AppendLine("</head>");
html.AppendLine("<body style=\"background-color: crimson; \">");
html.AppendLine("<header>");
html.AppendLine("<table style=\"border - color: black; width: 100 %; margin - top: 10px; \">");
html.AppendLine("<tr>");
html.AppendLine("<td><img src=\"http://polo.dev.erp.suit.pe/assets/logo-polo.png\" width=\"200px\"></td>");
html.AppendLine("<td align=\"right\"><h2 style=\"color: black; font - family: 'Franklin Gothic Medium', 'Arial Narrow', Arial, sans - serif;font-size: 10px \">Fecha: @Fecha<br/>Usuario: @Usuario</h2></td>");
html.AppendLine("</tr>");
html.AppendLine("<tr>");
html.AppendLine("<td><h1 style=\"color: crimson; font - family: 'Franklin Gothic Medium', 'Arial Narrow', Arial, sans - serif; \">Reporte programación</h1></td>");
html.AppendLine("<td></td>");
html.AppendLine("</tr>");
html.AppendLine("</table>");
html.AppendLine("<br/>");
html.AppendLine("<table border=\"1\" style=\"border - color: black; width: 100 % \"><tr><td> </td></tr></table>");
html.AppendLine("<br/>");
html.AppendLine("<table style=\"border - color: black; width: 100 %; margin - top: 10px; \">");
html.AppendLine("<tr>");
html.AppendLine("<th style=\"color: black; \">Sede: @Sede</th>");
html.AppendLine("<th style=\"color: black; \">Dieta: @Dieta</th>");
html.AppendLine("<th style=\"color: black; \">Mes: @Mes</th>");
html.AppendLine("</tr>");
html.AppendLine("</table>");
html.AppendLine("<br/>");
html.AppendLine("</header>");
foreach (var resumenByWeekOfMonth in listResumenByWeekOfMonth)
{
html.AppendLine("<table border=\"1\" style=\"border-color: black; width: 100 %;\">");
html.AppendLine("<tr>");
html.AppendLine("<th>T.A.</th>");
var sabado = (resumenByWeekOfMonth.WeekOfMonth.SaturdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.SaturdayDate.Day.ToString();
html.AppendLine("<th>Sabado " + sabado + "</th>");
var domingo = (resumenByWeekOfMonth.WeekOfMonth.SundayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.SundayDate.Day.ToString();
html.AppendLine("<th>Domingo " + domingo + "</th>");
var lunes = (resumenByWeekOfMonth.WeekOfMonth.MondayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.MondayDate.Day.ToString();
html.AppendLine("<th>Lunes " + lunes + "</th>");
var martes = (resumenByWeekOfMonth.WeekOfMonth.TuesdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.TuesdayDate.Day.ToString();
html.AppendLine("<th>Martes " + martes + "</th>");
var miercoles = (resumenByWeekOfMonth.WeekOfMonth.WednesdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.WednesdayDate.Day.ToString();
html.AppendLine("<th>Miercoles " + miercoles + "</th>");
var jueves = (resumenByWeekOfMonth.WeekOfMonth.ThursdayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.ThursdayDate.Day.ToString();
html.AppendLine("<th>Jueves " + jueves + "</th>");
var viernes = (resumenByWeekOfMonth.WeekOfMonth.FridayDate.Year == 1) ? "" : resumenByWeekOfMonth.WeekOfMonth.FridayDate.Day.ToString();
html.AppendLine("<th>Viernes " + viernes + "</th>");
html.AppendLine("</tr>");
foreach (var resumen in resumenByWeekOfMonth.listProgramation)
{
html.AppendLine("<tr>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "|Tiempo" + resumen.feedingTimeId + "</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Sabado</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Domingo</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Lunes</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Martes</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Miercoles</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Jueves</td>");
html.AppendLine("<td>@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Viernes</td>");
html.AppendLine("</tr>");
}
html.AppendLine("</table>");
html.AppendLine("<br/>");
}
html.AppendLine("</body>");
html.AppendLine("</html>");
html.Replace("@Sede", headquarters.name);
html.Replace("@Dieta", diet.name);
html.Replace("@Mes", CultureInfo.CurrentCulture.TextInfo.ToTitleCase(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month)));
html.Replace("@Fecha", DateTime.Now.ToShortDateString());
html.Replace("@Usuario", userName);
foreach (var resumenByWeekOfMonth in listResumenByWeekOfMonth)
{
foreach (var resumen in resumenByWeekOfMonth.listProgramation)
{
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "|Tiempo" + resumen.feedingTimeId, resumen.feedingTimeName);
StringBuilder monday = new StringBuilder();
foreach (var r in resumen.monday.programation)
{
monday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Lunes", monday.ToString());
StringBuilder tuesday = new StringBuilder();
foreach (var r in resumen.tuesday.programation)
{
tuesday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Martes", tuesday.ToString());
StringBuilder wednesday = new StringBuilder();
foreach (var r in resumen.wednesday.programation)
{
wednesday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Miercoles", wednesday.ToString());
StringBuilder thursday = new StringBuilder();
foreach (var r in resumen.thursday.programation)
{
thursday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Jueves", thursday.ToString());
StringBuilder friday = new StringBuilder();
foreach (var r in resumen.friday.programation)
{
friday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Viernes", friday.ToString());
StringBuilder saturday = new StringBuilder();
foreach (var r in resumen.saturday.programation)
{
saturday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Sabado", saturday.ToString());
StringBuilder sunday = new StringBuilder();
foreach (var r in resumen.sunday.programation)
{
sunday.AppendLine("<p>" + r.productName + "</p>");
}
html.Replace("@Rango" + resumenByWeekOfMonth.WeekOfMonth.WeekOfMonth + "-Tiempo" + resumen.feedingTimeId + "|Domingo", sunday.ToString());
}
}
return(html.ToString());
}
