public void GetSummaryAndTotalByCurrency_Return_OK()
{
//Setup
Mock <IServiceProvider> serviceProviderMock = GetServiceProvider();
var service = new Mock <IGarmentDebtBalanceService>();
GarmentDebtBalanceSummaryDto summaryDto = new GarmentDebtBalanceSummaryDto(1, "supplierCode", "supplierName", true, 1, "IDR", 1, 1, 1, 1, 1, 1, 1, 1);
var summaryAndTotalCurrencydto = new GarmentDebtBalanceSummaryAndTotalCurrencyDto()
{
Data = new List <GarmentDebtBalanceSummaryDto>()
{
summaryDto
},
GroupTotalCurrency = new List <GarmentDebtBalanceSummaryTotalByCurrencyDto>()
{
new GarmentDebtBalanceSummaryTotalByCurrencyDto()
}
};
service
.Setup(s => s.GetDebtBalanceSummaryAndTotalCurrency(It.IsAny <int>(), It.IsAny <int>(), It.IsAny <int>(), It.IsAny <bool>(), It.IsAny <bool>()))
.Returns(summaryAndTotalCurrencydto);
serviceProviderMock
.Setup(serviceProvider => serviceProvider.GetService(typeof(IGarmentDebtBalanceService)))
.Returns(service.Object);
//Act
IActionResult response = GetController(serviceProviderMock).GetSummaryAndTotalByCurrency(1, 1, DateTime.Now.Year, true, true);
//Assert
int statusCode = this.GetStatusCode(response);
Assert.Equal((int)HttpStatusCode.OK, statusCode);
}
public static MemoryStream Generate(GarmentDebtBalanceSummaryAndTotalCurrencyDto report, int month, int year, bool isImport, int timezoneOffset)
{
var document = new Document(PageSize.A4.Rotate(), 20, 20, 20, 20);
var stream = new MemoryStream();
var writer = PdfWriter.GetInstance(document, stream);
document.Open();
SetTitle(document, month, year, isImport, timezoneOffset);
SetTable(document, report, month, year, timezoneOffset);
document.Close();
byte[] byteInfo = stream.ToArray();
stream.Write(byteInfo, 0, byteInfo.Length);
stream.Position = 0;
return(stream);
}
private static void SetTable(Document document, GarmentDebtBalanceSummaryAndTotalCurrencyDto report, int month, int year, int timezoneOffset)
{
var table = new PdfPTable(10)
{
WidthPercentage = 100,
HorizontalAlignment = Element.ALIGN_LEFT
};
var cellCenter = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_CENTER
};
var cellLeft = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_CENTER
};
var cellRight = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_CENTER
};
cellCenter.Rowspan = 2;
cellCenter.Phrase = new Phrase("Supplier", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Mata Uang", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Saldo Awal", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Pembelian", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Pembayaran", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Saldo Akhir", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Rowspan = 1;
cellCenter.Colspan = 4;
cellCenter.Phrase = new Phrase("Dalam Rupiah", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase("Saldo Awal", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Pembelian", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Pembayaran", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Saldo Akhir", _subHeaderFont);
table.AddCell(cellCenter);
foreach (var item in report.Data)
{
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase(item.SupplierName, _normalFont);
table.AddCell(cellCenter);
cellLeft.Phrase = new Phrase(item.CurrencyCode, _normalFont);
table.AddCell(cellLeft);
cellCenter.Phrase = new Phrase(item.CurrencyInitialBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.CurrencyPurchaseAmount.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.CurrencyPaymentAmount.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.CurrencyCurrentBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.InitialBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.PurchaseAmount.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.PaymentAmount.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellRight.Phrase = new Phrase(item.CurrentBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellRight);
}
foreach (var item in report.GroupTotalCurrency)
{
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase("Total", _normalBoldFont);
table.AddCell(cellCenter);
cellLeft.Phrase = new Phrase(item.CurrencyCode, _normalBoldFont);
table.AddCell(cellLeft);
cellCenter.Phrase = new Phrase(item.TotalCurrencyInitialBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalCurrencyPurchase.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalCurrencyPayment.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalCurrencyCurrentBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalInitialBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalPurchase.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.TotalPayment.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellCenter);
cellRight.Phrase = new Phrase(item.TotalCurrentBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalBoldFont);
table.AddCell(cellRight);
}
document.Add(table);
document.Add(new Paragraph("\n"));
}
public static MemoryStream GenerateExcel(GarmentDebtBalanceSummaryAndTotalCurrencyDto data, int month, int year, string supplierName, bool isImport, int timeZone)
{
CultureInfo ci = new CultureInfo("en-us");
var result = data;
var reportDataTable = new DataTable();
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Supplier", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Mata Uang", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "<Currency>Saldo Awal", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "<Currency>Pembelian", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "<Currency>Pembayaran", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "<Currency>Saldo Akhir", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Awal", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembelian", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembayaran", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Akhir", DataType = typeof(string)
});
if (result.Data.Count > 0)
{
foreach (var report in result.Data)
{
reportDataTable.Rows.Add(
report.SupplierName,
report.CurrencyCode,
report.CurrencyInitialBalance.ToString("N2", ci),
report.CurrencyPurchaseAmount.ToString("N2", ci),
report.CurrencyPaymentAmount.ToString("N2", ci),
report.CurrencyCurrentBalance.ToString("N2", ci),
report.InitialBalance.ToString("N2", ci),
report.PurchaseAmount.ToString("N2", ci),
report.PaymentAmount.ToString("N2", ci),
report.CurrentBalance.ToString("N2", ci)
);
}
}
//adding total
foreach (var summary in result.GroupTotalCurrency)
{
reportDataTable.Rows.Add(
"Total",
summary.CurrencyCode,
summary.TotalCurrencyInitialBalance.ToString("N2", ci),
summary.TotalCurrencyPurchase.ToString("N2", ci),
summary.TotalCurrencyPayment.ToString("N2", ci),
summary.TotalCurrencyCurrentBalance.ToString("N2", ci),
summary.TotalInitialBalance.ToString("N2", ci),
summary.TotalPurchase.ToString("N2", ci),
summary.TotalPayment.ToString("N2", ci),
summary.TotalCurrentBalance.ToString("N2", ci)
);
}
using (var package = new ExcelPackage())
{
var company = "PT DAN LIRIS";
var title = "LEDGER HUTANG LOKAL VALAS";
var monthYear = new DateTime(year, month, DateTime.DaysInMonth(year, month));
var monthYearStr = monthYear.ToString("dd MMMM yyyy");
var period = monthYearStr;
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
worksheet.Cells["A1"].Value = company;
worksheet.Cells["A2"].Value = title;
//worksheet.Cells["A3"].Value = supplierName;
worksheet.Cells["A3"].Value = $"PER {period}";
#region PrintHeaderExcel
var rowStartHeader = 5;
var colStartHeader = 1;
foreach (var columns in reportDataTable.Columns)
{
DataColumn column = (DataColumn)columns;
if (column.ColumnName == "Saldo Awal")
{
var rowStartHeaderSpan = rowStartHeader + 1;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Value = column.ColumnName;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.Font.Bold = true;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader].Value = "Dalam Rupiah";
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 3].Merge = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 3].Style.Font.Bold = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 3].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
else if (column.ColumnName == "Pembelian" || column.ColumnName == "Pembayaran" || column.ColumnName == "Saldo Akhir")
{
var rowStartHeaderSpan = rowStartHeader + 1;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Value = column.ColumnName;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.Font.Bold = true;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowStartHeaderSpan, colStartHeader].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
else
{
worksheet.Cells[rowStartHeader, colStartHeader].Value = column.ColumnName.Replace("<Currency>", string.Empty);
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader + 1, colStartHeader].Merge = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader + 1, colStartHeader].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader + 1, colStartHeader].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader + 1, colStartHeader].Style.Font.Bold = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader + 1, colStartHeader].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
colStartHeader++;
}
#endregion
worksheet.Cells["A7"].LoadFromDataTable(reportDataTable, false);
for (int i = 7; i < result.Data.Count + 7; i++)
{
for (int j = 1; j <= reportDataTable.Columns.Count; j++)
{
worksheet.Cells[i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
}
for (int i = 7 + result.Data.Count; i < result.GroupTotalCurrency.Count + 7 + result.Data.Count; i++)
{
for (int j = 1; j <= reportDataTable.Columns.Count; j++)
{
worksheet.Cells[i, j].Style.Font.Bold = true;
worksheet.Cells[i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
}
var stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
}
