public IActionResult GetLoaderByPaymentBills(int page = 1, int size = 25, string order = "{}", [Bind(Prefix = "Select[]")] List <string> select = null, string keyword = null, string filter = "{}")
{
try
{
GarmentDebtBalanceIndexDto data = Service.GetDebtBalanceCardWithBalanceBeforeAndRemainBalanceIndex("PaymentBills", page, size, order, select, keyword, filter);
//List<GarmentInvoicePurchasingDispositionViewModel> dataVM = Mapper.Map<List<GarmentInvoicePurchasingDispositionViewModel>>(read.Data);
//Dictionary<string, object> Result =
// new ResultFormatter(ApiVersion, General.OK_STATUS_CODE, General.OK_MESSAGE)
// .Ok(Mapper, dataVM, page, size, read.Count, dataVM.Count, read.Order, read.Selected);
//return Ok(Result);
return(Ok(new
{
apiVersion = ApiVersion,
data = data.Data,
info = new
{
data.Count,
data.Order,
data.Selected
},
message = General.OK_MESSAGE,
statusCode = General.OK_STATUS_CODE
}));
}
catch (Exception e)
{
Dictionary <string, object> Result =
new ResultFormatter(ApiVersion, General.INTERNAL_ERROR_STATUS_CODE, e.Message)
.Fail();
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, Result));
}
}
public static MemoryStream Generate(GarmentDebtBalanceIndexDto 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);
}
public void Get_Succes_Return_OK()
{
//Setup
var mocks = GetMocks();
GarmentDebtBalanceModel model = new GarmentDebtBalanceModel(1, "purchasingCategoryName", "billsNo", "paymentBilss", 1, "garmentDeliveryOrderNo", 1, "suplierCode", "suplierName", false, 1, "IDR", 1, "productNames", DateTimeOffset.UtcNow, 1, 1, "paymentType");
GarmentDebtBalanceIndexDto dto = new GarmentDebtBalanceIndexDto()
{
Count = 1,
Data = new List <GarmentDebtBalanceCardDto>()
{
new GarmentDebtBalanceCardDto(model)
},
Order = new List <string>()
{
""
},
Selected = new List <string>()
{
""
}
};
mocks.Service
.Setup(s => s.GetDebtBalanceCardWithBalanceBeforeIndex(It.IsAny <int>(), It.IsAny <int>(), It.IsAny <int>()))
.Returns(dto);
//Act
IActionResult response = GetController(mocks).Get(new GarmentDebtBalanceFilterViewModel());
//Assert
int statusCode = this.GetStatusCode(response);
Assert.Equal((int)HttpStatusCode.OK, statusCode);
}
public void GetPdf_Return_FilePdf()
{
//Setup
var mocks = GetMocks();
GarmentDebtBalanceModel model = new GarmentDebtBalanceModel(1, "purchasingCategoryName", "bilssNo", "paymentBills", 1, "garmentDeliveryOrderNumber", 1, "supplierCode", "supplierName", true, 1, "IDR", 1, "productNames", DateTimeOffset.Now, 1, 1, "paymentType");
GarmentDebtBalanceIndexDto dto = new GarmentDebtBalanceIndexDto()
{
Data = new List <GarmentDebtBalanceCardDto>()
{
new GarmentDebtBalanceCardDto(model)
},
Count = 1,
Order = new List <string>()
{
},
};
mocks.Service
.Setup(s => s.GetDebtBalanceCardWithBalanceBeforeIndex(It.IsAny <int>(), It.IsAny <int>(), It.IsAny <int>()))
.Returns(dto);
//Act
var filter = new GarmentDebtBalanceFilterViewModel()
{
month = 1,
import = true,
supplierId = 1,
supplierName = "supplierName",
year = DateTimeOffset.Now.Year
};
IActionResult response = GetController(mocks).GetPDF(filter);
//Assert
Assert.Equal("application/pdf", response.GetType().GetProperty("ContentType").GetValue(response, null));
}
private static void SetTable(Document document, GarmentDebtBalanceIndexDto report, int month, int year, int timezoneOffset)
{
var table = new PdfPTable(12)
{
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("Tanggal Bon", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Nama Barang", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Kategori Pembukuan", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. BP Besar", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. BP Kecil", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. SJ", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. Bukti Pengeluaran Bank", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. NI", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("No. Invoice", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Rowspan = 1;
cellCenter.Colspan = 3;
cellCenter.Phrase = new Phrase("Mutasi", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase("Pembelian", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Pembayaran", _subHeaderFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase("Saldo", _subHeaderFont);
table.AddCell(cellCenter);
foreach (var item in report.Data)
{
if (item.ProductNames != "<<saldo awal>>" && item.ProductNames != "<<total>>")
{
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase(item.InvoiceDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy"), _normalFont);
table.AddCell(cellCenter);
cellLeft.Phrase = new Phrase(item.ProductNames, _normalFont);
table.AddCell(cellLeft);
cellCenter.Phrase = new Phrase(item.PurchasingCategoryName, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.PaymentBills, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.BillsNo, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.GarmentDeliveryOrderNo, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.BankExpenditureNoteNo, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.InternalNoteNo, _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.InvoiceNo, _normalFont);
table.AddCell(cellCenter);
cellRight.Phrase = new Phrase(item.MutationPurchase.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellRight);
cellRight.Phrase = new Phrase(item.MutationPayment.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellRight);
cellRight.Phrase = new Phrase(item.RemainBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellRight);
}
else if (item.ProductNames != "<<saldo awal>>")
{
cellCenter.Rowspan = 1;
cellCenter.Colspan = 11;
cellCenter.Phrase = new Phrase("SALDO AWAL", _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(item.RemainBalance.ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
}
else
{
cellCenter.Rowspan = 1;
cellCenter.Colspan = 9;
cellCenter.Phrase = new Phrase("TOTAL", _normalFont);
table.AddCell(cellCenter);
cellCenter.Rowspan = 1;
cellCenter.Colspan = 1;
cellCenter.Phrase = new Phrase(report.Data.Sum(s => s.MutationPurchase).ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(report.Data.Sum(s => s.MutationPayment).ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
cellCenter.Phrase = new Phrase(report.Data.Sum(s => s.RemainBalance).ToString("0,0.00", CultureInfo.InvariantCulture), _normalFont);
table.AddCell(cellCenter);
}
}
document.Add(table);
document.Add(new Paragraph("\n"));
}
public static MemoryStream GenerateExcel(GarmentDebtBalanceIndexDto 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 = "Tanggal", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Nama Barang", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Kategori Pembelian", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No BP Besar", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No BP Kecil", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No SJ", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No Bukti Pengeluaran Bank", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No NI", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "No Invoice", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "DPP", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "DPP Valas", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "PPN", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "PPH", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Total", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembelian", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembelian Valas", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembayaran", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Pembayaran Valas", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Saldo Akhir", DataType = typeof(string)
});
var reportDataTableOnly = reportDataTable.Clone();
if (result.Data.Count > 0)
{
foreach (var report in result.Data)
{
reportDataTable.Rows.Add(
report.ArrivalDate != DateTimeOffset.MaxValue? report.ArrivalDate.AddHours(timeZone).ToString("dd/MM/yyyy"):report.ArrivalDate.ToString("dd/MM/yyyy"),
report.ProductNames,
report.PurchasingCategoryName,
report.BillsNo,
report.PaymentBills,
report.GarmentDeliveryOrderNo,
report.BankExpenditureNoteNo,
report.InternalNoteNo,
report.InvoiceNo,
report.DPPAmount.ToString("N2", ci),
report.CurrencyDPPAmount.ToString("N2", ci),
report.VATAmount.ToString("N2", ci),
report.IncomeTaxAmount.ToString("N2", ci),
report.TotalInvoice.ToString("N2", ci),
report.MutationPurchase.ToString("N2", ci),
report.CurrencyMutationPurchase.ToString("N2", ci),
report.MutationPayment.ToString("N2", ci),
report.CurrencyMutationPayment.ToString("N2", ci),
report.RemainBalance.ToString("N2", ci)
);
if (!report.IsInitialBalance && !report.IsTotalBalance)
{
reportDataTableOnly.Rows.Add(
report.ArrivalDate != DateTimeOffset.MaxValue ? report.ArrivalDate.AddHours(timeZone).ToString("dd/MM/yyyy") : report.ArrivalDate.ToString("dd/MM/yyyy"),
report.ProductNames,
report.PurchasingCategoryName,
report.BillsNo,
report.PaymentBills,
report.GarmentDeliveryOrderNo,
report.BankExpenditureNoteNo,
report.InternalNoteNo,
report.InvoiceNo,
report.DPPAmount.ToString("N2", ci),
report.CurrencyDPPAmount.ToString("N2", ci),
report.VATAmount.ToString("N2", ci),
report.IncomeTaxAmount.ToString("N2", ci),
report.TotalInvoice.ToString("N2", ci),
report.MutationPurchase.ToString("N2", ci),
report.CurrencyMutationPurchase.ToString("N2", ci),
report.MutationPayment.ToString("N2", ci),
report.CurrencyMutationPayment.ToString("N2", ci),
report.RemainBalance.ToString("N2", ci)
);
}
}
}
using (var package = new ExcelPackage())
{
var company = "PT DAN LIRIS";
var title = "KARTU HUTANG";
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["A4"].Value = $"Periode : {period}";
#region PrintHeaderExcel
var rowStartHeader = 5;
var colStartHeader = 1;
foreach (var columns in reportDataTable.Columns)
{
DataColumn column = (DataColumn)columns;
if (column.ColumnName == "DPP")
{
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 = "Nilai Invoice";
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 4].Merge = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 4].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 4].Style.Font.Bold = true;
worksheet.Cells[rowStartHeader, colStartHeader, rowStartHeader, colStartHeader + 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
else if (column.ColumnName == "Pembelian")
{
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 = "Mutasi";
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 == "DPP Valas" || column.ColumnName == "PPN" || column.ColumnName == "PPH" || column.ColumnName == "Total" || column.ColumnName == "Pembayaran" || column.ColumnName == "Pembayaran Valas" || column.ColumnName == "Pembelian Valas")
{
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;
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);
bool isDataLoaded = false;
for (int i = 7; i < result.Data.Count + 7; i++)
{
for (int j = 1; j <= reportDataTable.Columns.Count; j++)
{
var item = result.Data[i - 7];
var checkIfSaldoOrTotal = item.IsInitialBalance || item.IsTotalBalance;
if (checkIfSaldoOrTotal)
{
var replaceCharacter = item.ProductNames.Replace("<", string.Empty).Replace(">", string.Empty);
var numberCellMerge = reportDataTable.Columns.Count - 1;
worksheet.Cells[i, j].Value = ci.TextInfo.ToTitleCase(replaceCharacter);
worksheet.Cells[i, j, i, numberCellMerge].Merge = true;
worksheet.Cells[i, j, i, numberCellMerge].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
worksheet.Cells[i, numberCellMerge + 1].Value = item.RemainBalance.ToString("N2", ci);
worksheet.Cells[i, numberCellMerge + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
j = reportDataTable.Columns.Count + 1; // exit column loop
}
else
{
if (!isDataLoaded)
{
worksheet.Cells[i, j].LoadFromDataTable(reportDataTableOnly, false);
isDataLoaded = true;
}
worksheet.Cells[i, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
}
}
var stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
}
