public static string getContabilidadEstadoResultadosSubGroups(string sGroup)
{
// if (currentMayorizadoDataset == null)
// getContabilidadEstadoResultados();
DataTable table = currentMayorizadoDataset.Tables[0];
var groupedData = from b in table.AsEnumerable()
where b.Field <string>("NombreFormatoBalance") == sGroup
group b by b.Field <string>("NombreFormatoBalance") into g
select new
{
NombreFormatoBalance = g.Key
};
string json = null;
List <ReporteEstadoResultado> fields = new List <ReporteEstadoResultado>();
try
{
Int32 id = 1;
foreach (var row in groupedData)
{
ReporteEstadoResultado field = new ReporteEstadoResultado();
field.id = id.ToString();
//field.NombreTipoCuenta = row.NombreFormatoBalance;
fields.Add(field);
id++;
}
json = JsonConvert.SerializeObject(fields);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(json);
}
public string getMayorizadoExcelFile(ReporteEstadoResultado reporte)
{
string tmpFile = "Mayorizado_" + Utils.Global.GenerateName(10) + ".xlsx";
//@"d:\ShiolFramework\tmp"
DirectoryInfo outputDir = new DirectoryInfo(ShiolConfiguration.Instance.Config.DirPlantillas);
FileInfo newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile));
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile));
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Mayorizado");
//Add the headers
int col = 1;
int init_row = 4, row = 4;
foreach (Column column in reporte.columns)
{
if (column.Header.ToUpper() != "TODOS")
{
worksheet.Cells[row, col++].Value = column.Header;
}
}
worksheet.Cells[row, col++].Value = "Adjustments \n (+) &(-)";
worksheet.Cells[row, col++].Value = "Consolidado Total";
worksheet.Cells[row, col++].Value = "Bdg 2017 TTD";
worksheet.Cells[row, col++].Value = "Exe 2016 TTD";
worksheet.Cells[row, col++].Value = "Var % Exe vs Bgd 2017";
worksheet.Cells[row, col++].Value = "Var % Exe 2017 vs 2016";
worksheet.Cells[row, col++].Value = "Unit Analysis";
worksheet.Cells[row, col++].Value = "% of Sales";
worksheet.Cells[row, col++].Value = "Unit Analysis";
worksheet.Cells[row, col++].Value = "% of Sales";
/*
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
*/
//worksheet.Cells["A1:J1"].Style.Fill.PatternType = ExcelFillStyle.LightGray;
worksheet.Cells["A" + row + ":" + "R" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//ws.Cells["B24"].Style.Font.Bold = true;
//ws.Cells["B24:X24"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
//worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
//worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
//worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";
//worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";
int numberWidth = 15;
worksheet.Column(1).Width = 45;
worksheet.Column(2).Width = numberWidth;
worksheet.Column(3).Width = numberWidth;
worksheet.Column(4).Width = numberWidth;
worksheet.Column(5).Width = numberWidth;
worksheet.Column(6).Width = numberWidth;
worksheet.Column(7).Width = numberWidth;
worksheet.Column(8).Width = numberWidth;
worksheet.Column(9).Width = numberWidth;
row++;
foreach (ReporteEstadoResultadoGroup group in reporte.groups)
{
worksheet.Cells["A" + row].Value = group.Descripcion;
// worksheet.Cells[row, 2].Value = group.Todos;
worksheet.Cells[row, 2].Value = group.H1;
worksheet.Cells[row, 3].Value = group.H2;
worksheet.Cells[row, 4].Value = group.H3;
worksheet.Cells[row, 5].Value = group.H4;
worksheet.Cells[row, 6].Value = group.H5;
worksheet.Cells[row, 7].Value = group.H6;
worksheet.Cells[row, 8].Value = group.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["A" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true;
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
foreach (ReporteEstadoResultadoSubGroup sgroup in group.subGroups)
{
worksheet.Cells[row, 1].Value = " " + sgroup.Descripcion;
//worksheet.Cells[row, 2].Value = sgroup.Todos;
worksheet.Cells[row, 2].Value = sgroup.H1;
worksheet.Cells[row, 3].Value = sgroup.H2;
worksheet.Cells[row, 4].Value = sgroup.H3;
worksheet.Cells[row, 5].Value = sgroup.H4;
worksheet.Cells[row, 6].Value = sgroup.H5;
worksheet.Cells[row, 7].Value = sgroup.H6;
worksheet.Cells[row, 8].Value = sgroup.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["B" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true;
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
int rowsg = row;
foreach (ReporteEstadoResultadoSubGroupChild sgroupc in sgroup.subGroupChilds)
{
worksheet.Cells[row, 1].Value = " " + " " + sgroupc.Descripcion;
//worksheet.Cells[row, 2].Value = sgroupc.Todos;
worksheet.Cells[row, 2].Value = sgroupc.H1;
worksheet.Cells[row, 3].Value = sgroupc.H2;
worksheet.Cells[row, 4].Value = sgroupc.H3;
worksheet.Cells[row, 5].Value = sgroupc.H4;
worksheet.Cells[row, 6].Value = sgroupc.H5;
worksheet.Cells[row, 7].Value = sgroupc.H6;
worksheet.Cells[row, 8].Value = sgroupc.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
}
worksheet.Cells["B" + rowsg + ":" + "J" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
}
}
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
worksheet.Cells["A" + init_row + ":" + "I" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//Add a formula for the value-column
// worksheet.Cells["E2:E4"].Formula = "C2*D2";
/*
* //Ok now format the values;
* using (var range = worksheet.Cells[1, 1, 1, 5])
* {
* range.Style.Font.Bold = true;
* range.Style.Fill.PatternType = ExcelFillStyle.Solid;
* range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
* range.Style.Font.Color.SetColor(Color.White);
* }
*
* worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
* worksheet.Cells["A5:E5"].Style.Font.Bold = true;
*
* worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
* worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
* worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
*
* //Create an autofilter for the range
* worksheet.Cells["A1:E4"].AutoFilter = true;
*
* worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text
*
* //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
* //For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
* //you want to use the result of a formula in your program.
* worksheet.Calculate();
*
* worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
*
* // lets set the header text
* worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
* // add the page number to the footer plus the total number of pages
* worksheet.HeaderFooter.OddFooter.RightAlignedText =
* string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
* // add the sheet name to the footer
* worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
* // add the file path to the footer
* worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
*
* worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
* worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
*
* // Change the sheet view to show it in page layout mode
* worksheet.View.PageLayoutView = true;
*/
// set some document properties
package.Workbook.Properties.Title = "Mayorizado";
package.Workbook.Properties.Author = "Alvaro Medina";
package.Workbook.Properties.Comments = "Mayorización...";
// set some extended property values
package.Workbook.Properties.Company = "Shiol Sistemas";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Alvaro Medina");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "SHG Sistemas");
// save our new workbook and we are done!
package.Save();
}
return(newFile.FullName);
}
public string getMayorizadoExcelFile1(DataTable dt, ReporteEstadoResultado reporte)
{
//@"d:\ShiolFramework\tmp"
DirectoryInfo outputDir = new DirectoryInfo(ShiolConfiguration.Instance.Config.DirPlantillas);
ExcelPackage pck = new ExcelPackage();
//Create a datatable with the directories and files from the root directory...
// DataTable dt = GetDataTable(outputDir.Root);
var wsDt = pck.Workbook.Worksheets.Add("FromDataTable");
//Load the datatable and set the number formats...
wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9);
wsDt.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
wsDt.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
/*
*
* //Select Name and Created-time...
* var collection = (from row in dt.Select() select new { Name = row["Name"], Created_time = (DateTime)row["Created"] });
*
*
*
* var wsEnum = pck.Workbook.Worksheets.Add("FromAnonymous");
*
* //Load the collection starting from cell A1...
* wsEnum.Cells["A1"].LoadFromCollection(collection, true, TableStyles.Medium9);
*
* //Add some formating...
* wsEnum.Cells[2, 2, dt.Rows.Count - 1, 2].Style.Numberformat.Format = "mm-dd-yy";
* wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
*
* //Load a list of FileDTO objects from the datatable...
* var wsList = pck.Workbook.Worksheets.Add("FromList");
* List<FileDTO> list = (from row in dt.Select()
* select new FileDTO
* {
* Name = row["Name"].ToString(),
* Size = row["Size"].GetType() == typeof(long) ? (long)row["Size"] : 0,
* Created = (DateTime)row["Created"],
* LastModified = (DateTime)row["Modified"],
* IsDirectory = (row["Size"] == DBNull.Value)
* }).ToList<FileDTO>();
*
* //Load files ordered by size...
* wsList.Cells["A1"].LoadFromCollection(from file in list
* orderby file.Size descending
* where file.IsDirectory == false
* select file, true, TableStyles.Medium9);
*
* wsList.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
* wsList.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
*
*
* //Load directories ordered by Name...
* wsList.Cells["F1"].LoadFromCollection(from file in list
* orderby file.Name ascending
* where file.IsDirectory == true
* select new
* {
* Name = file.Name,
* Created = file.Created,
* Last_modified = file.LastModified
* }, //Use an underscore in the property name to get a space in the title.
* true, TableStyles.Medium11);
*
* wsList.Cells[2, 7, dt.Rows.Count + 1, 8].Style.Numberformat.Format = "mm-dd-yy";
*
* //Load the list using a specified array of MemberInfo objects. Properties, fields and methods are supported.
* var rng = wsList.Cells["J1"].LoadFromCollection(list,
* true,
* TableStyles.Medium10,
* BindingFlags.Instance | BindingFlags.Public,
* new MemberInfo[] {
* typeof(FileDTO).GetProperty("Name"),
* typeof(FileDTO).GetField("IsDirectory"),
* typeof(FileDTO).GetMethod("ToString")}
* );
*
* wsList.Tables.GetFromRange(rng).Columns[2].Name = "Description";
*
* wsList.Cells[wsList.Dimension.Address].AutoFitColumns();
*/
//...and save
var fi = new FileInfo(outputDir.FullName + @"\Mayorizado.xlsx");
if (fi.Exists)
{
fi.Delete();
}
pck.SaveAs(fi);
return(fi.FullName);
}
public bool getContabilidadEstadoResultados(Request req, Response res, FileType type)
{
ReporteEstadoResultado reporte = new ReporteEstadoResultado();
DataTable dt = getContabilidadEstadoResultadosDataTable(req, res);
reporte._columnsGroups = _columns;
reporte._columnsSubGroups = _columns;
reporte._columns = _columns;
reporte.columns = columns;
reporte.Descripcion = "Total General";
string Moneda = req.PathVariables["moneda"] != null ? req.PathVariables["moneda"] : "USD"; //Soles
bool isSoles = (Moneda == "USD" ? false : true);
string json = null;
try
{
string NombreTipoCuenta = "";
string NombreFormatoBalance = "";
List <ReporteEstadoResultadoGroup> listGroups = new List <ReporteEstadoResultadoGroup>();
ReporteEstadoResultadoSubGroup subGroup = new ReporteEstadoResultadoSubGroup();
ReporteEstadoResultadoGroup group = new ReporteEstadoResultadoGroup();
Int32 idGroup = -1;
Int32 idSubGroup = -1;
Int32 idDetail = -1;
foreach (DataRow row in dt.Rows)
{
if (NombreTipoCuenta != row["NombreTipoCuenta"].ToString())
{
idGroup++;
idSubGroup = -1;
group = new ReporteEstadoResultadoGroup();
NombreFormatoBalance = "";
NombreTipoCuenta = row["NombreTipoCuenta"].ToString();
group.Descripcion = "Cuenta: " + row["NombreTipoCuenta"].ToString().Trim();
group.id = idGroup.ToString();
listGroups.Add(group);
}
if (NombreFormatoBalance != row["NombreFormatoBalance"].ToString())
{
idSubGroup++;
idDetail = -1;
subGroup = new ReporteEstadoResultadoSubGroup();
NombreFormatoBalance = row["NombreFormatoBalance"].ToString();
subGroup.Descripcion = " Tipo: " + row["NombreFormatoBalance"].ToString().Trim();
subGroup.id = idSubGroup.ToString();
listGroups[idGroup].subGroups.Add(subGroup);
}
idDetail++;
ReporteEstadoResultadoSubGroupChild field = new ReporteEstadoResultadoSubGroupChild();
field.Descripcion = row["NombreFormatoGanaciasPerdidas"].ToString().Trim();
field.H1.value = isSoles ? Convert.ToDouble(row["H1Soles"].ToString()) : Convert.ToDouble(row["H1Dolares"].ToString());
field.H2.value = isSoles ? Convert.ToDouble(row["H2Soles"].ToString()) : Convert.ToDouble(row["H2Dolares"].ToString());
field.H3.value = isSoles ? Convert.ToDouble(row["H3Soles"].ToString()) : Convert.ToDouble(row["H3Dolares"].ToString());
field.H4.value = isSoles ? Convert.ToDouble(row["H4Soles"].ToString()) : Convert.ToDouble(row["H4Dolares"].ToString());
field.H5.value = isSoles ? Convert.ToDouble(row["H5Soles"].ToString()) : Convert.ToDouble(row["H5Dolares"].ToString());
field.H6.value = isSoles ? Convert.ToDouble(row["H6Soles"].ToString()) : Convert.ToDouble(row["H6Dolares"].ToString());
field.H7.value = isSoles ? Convert.ToDouble(row["H7Soles"].ToString()) : Convert.ToDouble(row["H7Dolares"].ToString());
field.H8.value = isSoles ? Convert.ToDouble(row["H8Soles"].ToString()) : Convert.ToDouble(row["H8Dolares"].ToString());
field.Todos.value = field.H1.value + field.H2.value + field.H3.value + field.H4.value + field.H5.value + field.H6.value + field.H7.value;
listGroups[idGroup].subGroups[idSubGroup].subGroupChilds.Add(field);
listGroups[idGroup].subGroups[idSubGroup].H1 += field.H1.value;
listGroups[idGroup].subGroups[idSubGroup].H2 += field.H2.value;
listGroups[idGroup].subGroups[idSubGroup].H3 += field.H3.value;
listGroups[idGroup].subGroups[idSubGroup].H4 += field.H4.value;
listGroups[idGroup].subGroups[idSubGroup].H5 += field.H5.value;
listGroups[idGroup].subGroups[idSubGroup].H6 += field.H6.value;
listGroups[idGroup].subGroups[idSubGroup].H7 += field.H7.value;
listGroups[idGroup].subGroups[idSubGroup].Todos += field.Todos.value;
listGroups[idGroup].H1 += field.H1.value;
listGroups[idGroup].H2 += field.H2.value;
listGroups[idGroup].H3 += field.H3.value;
listGroups[idGroup].H4 += field.H4.value;
listGroups[idGroup].H5 += field.H5.value;
listGroups[idGroup].H6 += field.H6.value;
listGroups[idGroup].H7 += field.H7.value;
listGroups[idGroup].Todos += field.Todos.value;
reporte.H1.value += field.H1.value;
reporte.H2.value += field.H2.value;
reporte.H3.value += field.H3.value;
reporte.H4.value += field.H4.value;
reporte.H5.value += field.H5.value;
reporte.H6.value += field.H6.value;
reporte.H7.value += field.H7.value;
reporte.Todos.value += field.Todos.value;
}
reporte.groups = listGroups;
List <ReporteEstadoResultado> report = new List <ReporteEstadoResultado>();
report.Add(reporte);
res.httpListenerResponse.Headers.Add("Access-Control-Expose-Headers", "X-Total-Count");
res.httpListenerResponse.Headers.Add("X-Total-Count", dt.Rows.Count.ToString());
if (type == FileType.Json)
{
json = JsonConvert.SerializeObject(report);
res.Content = json;
res.ContentType = "application/json";
res.httpListenerResponse.StatusCode = 200; //401 for error;
}
if (type == FileType.Xlsx | type == FileType.Pdf)
{
string excelPath = getMayorizadoExcelFile(reporte);
if (type == FileType.Pdf)
{
string pdfFile = ExportWorkbookToPdf(excelPath, ShiolConfiguration.Instance.Config.DirPlantillas + @"\Mayorizado.pdf", false);
WriteFile(res, pdfFile, "Mayorizado.pdf");
}
else
{
WriteFile(res, excelPath, "Mayorizado.xlsx");
}
//int converted = ExcelConverter.Convert(excelPath, ShiolConfiguration.Instance.Config.DirPlantillas + "\\Test.pdf", null);
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
