public static MemoryStream Generate(LocalPurchasingBookReportViewModel viewModel, int timezoneOffset, DateTime?dateFrom, DateTime?dateTo)
{
var d1 = dateFrom.GetValueOrDefault().ToUniversalTime();
var d2 = (dateTo.HasValue ? dateTo.Value : DateTime.Now).ToUniversalTime();
var document = new Document(PageSize.A4.Rotate(), 5, 5, 25, 25);
var stream = new MemoryStream();
PdfWriter.GetInstance(document, stream);
document.Open();
SetHeader(document, d1, d2, timezoneOffset);
SetReportTable(document, viewModel.Reports, viewModel.GrandTotal, timezoneOffset);
document.Add(new Paragraph("\n"));
SetCategoryCurrencySummaryTable(document, viewModel.CategorySummaries, viewModel.CategorySummaryTotal, viewModel.CurrencySummaries);
//SetCategoryTable(document, viewModel.CategorySummaries, viewModel.CategorySummaryTotal);
//SetCurrencyTable(document, viewModel.CurrencySummaries);
SetFooter(document);
document.Close();
byte[] byteInfo = stream.ToArray();
stream.Write(byteInfo, 0, byteInfo.Length);
stream.Position = 0;
return(stream);
}
public async Task <LocalPurchasingBookReportViewModel> GetReportData(string no, string unit, string categoryCode, DateTime?dateFrom, DateTime?dateTo)
{
DateTime d1 = dateFrom.GetValueOrDefault();
DateTime d2 = dateTo.HasValue ? dateTo.Value : DateTime.Now;
var query = dbSet
.Where(urn => urn.ReceiptDate >= d1.ToUniversalTime() && urn.ReceiptDate.ToUniversalTime() <= d2 && urn.SupplierIsImport);
if (!string.IsNullOrWhiteSpace(no))
{
query = query.Where(urn => urn.URNNo.Equals(no));
}
if (!string.IsNullOrWhiteSpace(unit))
{
query = query.Where(urn => urn.UnitCode.Equals(unit));
}
var prIds = query.SelectMany(urn => urn.Items.Select(s => s.PRId)).ToList();
if (prIds.Count > 0)
{
var purchaseRequestQuery = dbContext.PurchaseRequests.AsQueryable();
if (!string.IsNullOrWhiteSpace(categoryCode))
{
purchaseRequestQuery = dbContext.PurchaseRequests.Where(pr => pr.CategoryCode.Equals(categoryCode) && prIds.Contains(pr.Id));
}
if (purchaseRequestQuery.Count() > 0)
{
var purchaseRequests = purchaseRequestQuery.Select(pr => new { pr.Id, pr.CategoryName, pr.CategoryCode }).ToList();
prIds = purchaseRequests.Select(pr => pr.Id).ToList();
var categories = purchaseRequests.Select(pr => pr.CategoryCode).Distinct().ToList();
var urnIds = query.Select(urn => urn.Id).ToList();
var urnItems = dbContext.UnitReceiptNoteItems
.Include(urnItem => urnItem.UnitReceiptNote)
.Where(urnItem => urnIds.Contains(urnItem.URNId) && prIds.Contains(urnItem.PRId))
.Select(urnItem => new
{
urnItem.PRId,
urnItem.UnitReceiptNote.DOId,
urnItem.UnitReceiptNote.DONo,
urnItem.UnitReceiptNote.URNNo,
URNId = urnItem.UnitReceiptNote.Id,
urnItem.ProductName,
urnItem.UnitReceiptNote.ReceiptDate,
urnItem.UnitReceiptNote.SupplierName,
urnItem.UnitReceiptNote.UnitCode,
urnItem.EPODetailId,
urnItem.PricePerDealUnit,
urnItem.ReceiptQuantity
})
.ToList();
var epoDetailIds = urnItems.Select(urnItem => urnItem.EPODetailId).ToList();
var epoItemIds = dbContext.ExternalPurchaseOrderDetails
.Include(epoDetail => epoDetail.ExternalPurchaseOrderItem)
.Where(epoDetail => epoDetailIds.Contains(epoDetail.Id))
.Select(epoDetail => epoDetail.ExternalPurchaseOrderItem.Id)
.ToList();
var epoItems = dbContext.ExternalPurchaseOrderItems
.Include(epoItem => epoItem.ExternalPurchaseOrder)
.Where(epoItem => epoItemIds.Contains(epoItem.Id))
.Select(epoItem => new
{
epoItem.PONo,
epoDetailIds = epoItem.Details.Select(epoDetail => epoDetail.Id).ToList(),
epoItem.ExternalPurchaseOrder.CurrencyCode,
epoItem.ExternalPurchaseOrder.UseVat,
Details = epoItem.Details.Select(epoDetail => new { epoDetail.PricePerDealUnit, epoDetail.Id }).ToList()
})
.ToList();
var unitPaymentOrders = dbContext.UnitPaymentOrderItems
.Include(upoItem => upoItem.UnitPaymentOrder)
.Where(upoItem => urnIds.Contains(upoItem.URNId))
.Select(upoItem => new
{
upoItem.URNId,
upoItem.UnitPaymentOrder.InvoiceNo,
upoItem.UnitPaymentOrder.UPONo,
upoItem.UnitPaymentOrder.VatNo
});
var currencyCodes = epoItems.Select(epoItem => epoItem.CurrencyCode).Distinct().ToList();
var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeList(currencyCodes);
var reportResult = new LocalPurchasingBookReportViewModel();
foreach (var urnItem in urnItems)
{
var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
var currency = currencies.FirstOrDefault(f => f.Code.Equals(epoItem.CurrencyCode));
decimal dpp = 0;
decimal dppCurrency = 0;
decimal ppn = 0;
//default IDR
double currencyRate = 1;
var currencyCode = "IDR";
if (currency != null && !currency.Code.Equals("IDR"))
{
dppCurrency = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
currencyRate = currency.Rate.GetValueOrDefault();
currencyCode = currency.Code;
}
else
{
dpp = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
}
if (epoItem.UseVat)
{
ppn = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity * 0.1);
}
var reportItem = new PurchasingReport()
{
CategoryName = purchaseRequest.CategoryName,
CategoryCode = purchaseRequest.CategoryCode,
CurrencyRate = (decimal)currencyRate,
DONo = urnItem.DONo,
DPP = dpp,
DPPCurrency = dppCurrency,
InvoiceNo = unitPaymentOrder?.InvoiceNo,
VATNo = unitPaymentOrder?.VatNo,
IPONo = epoItem.PONo,
VAT = ppn,
Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
ProductName = urnItem.ProductName,
ReceiptDate = urnItem.ReceiptDate,
SupplierName = urnItem.SupplierName,
UnitName = urnItem.UnitCode,
UPONo = unitPaymentOrder?.UPONo,
URNNo = urnItem.URNNo,
IsUseVat = epoItem.UseVat,
CurrencyCode = currencyCode
};
reportResult.Reports.Add(reportItem);
}
reportResult.CategorySummaries = reportResult.Reports
.GroupBy(report => new { report.CategoryCode })
.Select(report => new Summary()
{
Category = report.Key.CategoryCode,
SubTotal = report.Sum(sum => sum.Total)
}).OrderBy(order => order.Category).ToList();
reportResult.CurrencySummaries = reportResult.Reports
.GroupBy(report => new { report.CurrencyCode })
.Select(report => new Summary()
{
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
}).OrderBy(order => order.CurrencyCode).ToList();
reportResult.Reports = reportResult.Reports.OrderByDescending(order => order.ReceiptDate).ToList();
reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
return(reportResult);
}
}
return(new LocalPurchasingBookReportViewModel());
}
private static void SetReportTable(Document document, LocalPurchasingBookReportViewModel viewModel, int timezoneOffset)
{
var table = new PdfPTable(14)
{
WidthPercentage = 95
};
var widths = new List <float>();
for (var i = 0; i < 14; i++)
{
if (i == 1)
{
widths.Add(1f);
continue;
}
if (i == 2 || i == 3)
{
widths.Add(3f);
continue;
}
widths.Add(2f);
}
table.SetWidths(widths.ToArray());
SetReportTableHeader(table);
var listCategoryReports = viewModel.Reports.Where(x => x.AccountingUnitName != null).OrderBy(order => order.AccountingLayoutIndex).GroupBy(x => x.AccountingCategoryName).ToList();
var cell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_CENTER
};
var cellColspan2NoBorderLeft = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE,
Colspan = 2,
BorderWidthLeft = 0
};
var cellColspan8NoBorderRight = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_CENTER,
Colspan = 8,
BorderWidthRight = 0
};
var cellAlignRight = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_CENTER
};
var cellAlignRightColspan9 = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_CENTER,
Colspan = 9
};
var categoryCell = new PdfPCell()
{
BorderWidthTop = 0,
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_CENTER
};
var totalUnitCell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_CENTER
};
var summaryUnit = new Dictionary <string, decimal>();
foreach (var cat in listCategoryReports)
{
var categoryName = cat.Select(x => x.AccountingCategoryName).FirstOrDefault();
categoryCell.Phrase = new Phrase(categoryName, _smallBoldFont);
categoryCell.Colspan = 15;
table.AddCell(categoryCell);
decimal total = 0;
decimal totalDPP = 0;
decimal totalPPN = 0;
decimal totalPPH = 0;
var totalUnit = new Dictionary <string, Dictionary <string, decimal> >();
foreach (var data in cat)
{
cell.Phrase = new Phrase(data.ReceiptDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("yyyy-dd-MM"), _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.SupplierCode, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.SupplierName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.Remark, _smallerFont);
table.AddCell(cell);
//cell.Phrase = new Phrase(data.IPONo, _smallerFont);
//table.AddCell(cell);
//cell.Phrase = new Phrase(data.DONo, _smallerFont);
//table.AddCell(cell);
cell.Phrase = new Phrase(data.URNNo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.InvoiceNo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.VATNo, _smallerFont);
table.AddCell(cell);
if (data.DataSourceSort == 1)
{
cell.Phrase = new Phrase(data.UPONo, _smallerFont);
}
else
{
cell.Phrase = new Phrase(data.CorrectionNo, _smallerFont);
}
table.AddCell(cell);
cell.Phrase = new Phrase(data.AccountingCategoryName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.AccountingUnitName, _smallerFont);
table.AddCell(cell);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.DPP), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.VAT), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.IncomeTax), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.Total), _smallerBoldFont);
table.AddCell(cellAlignRight);
if (totalUnit.ContainsKey(data.AccountingUnitName))
{
totalUnit[data.AccountingUnitName]["DPP"] += data.DPP;
totalUnit[data.AccountingUnitName]["VAT"] += data.VAT * data.CurrencyRate;
totalUnit[data.AccountingUnitName]["TAX"] += data.IncomeTax * data.CurrencyRate;
totalUnit[data.AccountingUnitName]["TOTAL"] += data.Total;
}
else
{
totalUnit.Add(data.AccountingUnitName, new Dictionary <string, decimal>()
{
{ "DPP", data.DPP },
{ "VAT", data.VAT * data.CurrencyRate },
{ "TAX", data.IncomeTax * data.CurrencyRate },
{ "TOTAL", data.Total }
});
}
if (summaryUnit.ContainsKey(data.AccountingUnitName))
{
summaryUnit[data.AccountingUnitName] += data.Total;
}
else
{
summaryUnit.Add(data.AccountingUnitName, data.Total);
}
totalDPP += data.DPP;
totalPPN += data.VAT;
totalPPH += data.IncomeTax;
total += data.Total;
}
//var cellGrandTotal = new PdfPCell()
//{
// HorizontalAlignment = Element.ALIGN_RIGHT,
// VerticalAlignment = Element.ALIGN_CENTER,
// Colspan = 16
//};
//cellGrandTotal.Phrase = new Phrase("Grand Total", _smallerBoldFont);
//table.AddCell(cellGrandTotal);
//cellGrandTotal.Phrase = new Phrase(string.Format("{0:n}", grandTotal), _smallerBoldFont);
//table.AddCell(cellGrandTotal);
cellColspan8NoBorderRight.Phrase = new Phrase();
table.AddCell(cellColspan8NoBorderRight);
cellColspan2NoBorderLeft.Phrase = new Phrase($"TOTAL {categoryName}", _smallBoldFont);
table.AddCell(cellColspan2NoBorderLeft);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalDPP), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalPPN), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalPPH), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", total), _smallerBoldFont);
table.AddCell(cellAlignRight);
if (totalUnit.Count() > 0)
{
foreach (var v in totalUnit)
{
cellAlignRightColspan9.Phrase = new Phrase($"{categoryName}", _smallBoldFont);
table.AddCell(cellAlignRightColspan9);
cell.Phrase = new Phrase($"{v.Key} ", _smallerFont);
table.AddCell(cell);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["DPP"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["VAT"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["TAX"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["TOTAL"]), _smallerBoldFont);
table.AddCell(cellAlignRight);
//totalUnitCell.Phrase = new Phrase(string.Format("{0:n}", v.Value), _smallFont);
//totalUnitCell.Colspan = 4;
//table.AddCell(totalUnitCell);
}
}
}
document.Add(table);
document.Add(new Paragraph("\n"));
var summaryTable = new PdfPTable(5)
{
WidthPercentage = 95,
};
var widthSummaryTable = new List <float>()
{
3f, 1f, 3f, 1f, 2f
};
summaryTable.SetWidths(widthSummaryTable.ToArray());
summaryTable.AddCell(GetCategorySummaryTable(viewModel.CategorySummaries, viewModel.CategorySummaryTotal));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetUnitSummaryTable(summaryUnit));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetCurrencySummaryTable(viewModel.CurrencySummaries));
document.Add(summaryTable);
}
public async Task <LocalPurchasingBookReportViewModel> GetReportData(string no, string unit, string categoryCode, DateTime?dateFrom, DateTime?dateTo, bool isValas)
{
var d1 = dateFrom.GetValueOrDefault().ToUniversalTime();
var d2 = (dateTo.HasValue ? dateTo.Value : DateTime.Now).ToUniversalTime();
var query = from urnWithItem in dbContext.UnitReceiptNoteItems
join pr in dbContext.PurchaseRequests on urnWithItem.PRId equals pr.Id into joinPurchaseRequest
from urnPR in joinPurchaseRequest.DefaultIfEmpty()
join epoDetail in dbContext.ExternalPurchaseOrderDetails on urnWithItem.EPODetailId equals epoDetail.Id into joinExternalPurchaseOrder
from urnEPODetail in joinExternalPurchaseOrder.DefaultIfEmpty()
join upoItem in dbContext.UnitPaymentOrderItems on urnWithItem.URNId equals upoItem.URNId into joinUnitPaymentOrder
from urnUPOItem in joinUnitPaymentOrder.DefaultIfEmpty()
where urnWithItem.UnitReceiptNote.ReceiptDate >= d1 && urnWithItem.UnitReceiptNote.ReceiptDate <= d2 && !urnWithItem.UnitReceiptNote.SupplierIsImport
select new
{
// PR Info
urnPR.CategoryCode,
urnPR.CategoryName,
urnWithItem.PRId,
urnWithItem.UnitReceiptNote.DOId,
urnWithItem.UnitReceiptNote.DONo,
urnWithItem.UnitReceiptNote.URNNo,
URNId = urnWithItem.UnitReceiptNote.Id,
urnWithItem.ProductName,
urnWithItem.UnitReceiptNote.ReceiptDate,
urnWithItem.UnitReceiptNote.SupplierName,
urnWithItem.UnitReceiptNote.SupplierIsImport,
urnWithItem.UnitReceiptNote.UnitCode,
urnWithItem.UnitReceiptNote.UnitName,
urnWithItem.EPODetailId,
urnWithItem.PricePerDealUnit,
urnWithItem.ReceiptQuantity,
urnWithItem.Uom,
// EPO Info
urnEPODetail.ExternalPurchaseOrderItem.PONo,
urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.UseVat,
EPOPricePerDealUnit = urnEPODetail.PricePerDealUnit,
urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.CurrencyCode,
// UPO Info
urnUPOItem.UnitPaymentOrder.InvoiceNo,
urnUPOItem.UnitPaymentOrder.UPONo,
urnUPOItem.UnitPaymentOrder.VatNo
};
//var query = dbSet
// .Where(urn => urn.ReceiptDate >= d1.ToUniversalTime() && urn.ReceiptDate.ToUniversalTime() <= d2 && !urn.SupplierIsImport);
if (isValas)
{
query = query.Where(urn => urn.CurrencyCode != IDRCurrencyCode);
}
else
{
query = query.Where(urn => urn.CurrencyCode == IDRCurrencyCode);
}
if (!string.IsNullOrWhiteSpace(no))
{
query = query.Where(urn => urn.URNNo == no);
}
if (!string.IsNullOrWhiteSpace(unit))
{
query = query.Where(urn => urn.UnitCode == unit);
}
//var prIds = query.SelectMany(urn => urn.Items.Select(s => s.PRId)).ToList();
if (!string.IsNullOrWhiteSpace(categoryCode))
{
query = query.Where(urn => urn.CategoryCode == categoryCode);
}
var queryResult = query.OrderByDescending(item => item.ReceiptDate).ToList();
//var currencyCodes = queryResult.Select(item => item.CurrencyCode).ToList();
var currencyTuples = queryResult.Select(item => new Tuple <string, DateTimeOffset>(item.CurrencyCode, item.ReceiptDate));
var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeDateList(currencyTuples);
var reportResult = new LocalPurchasingBookReportViewModel();
foreach (var item in queryResult)
{
//var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
//var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
//var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
//var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
var currency = currencies.FirstOrDefault(f => f.Code.Equals(item.CurrencyCode));
decimal dpp = 0;
decimal dppCurrency = 0;
decimal ppn = 0;
//default IDR
double currencyRate = 1;
var currencyCode = "IDR";
if (currency != null && !currency.Code.Equals("IDR"))
{
dppCurrency = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
currencyRate = currency.Rate.GetValueOrDefault();
currencyCode = currency.Code;
}
else
{
dpp = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
}
if (item.UseVat)
{
ppn = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity * 0.1);
}
var reportItem = new PurchasingReport()
{
CategoryName = item.CategoryName,
CategoryCode = item.CategoryCode,
CurrencyRate = (decimal)currencyRate,
DONo = item.DONo,
DPP = dpp,
DPPCurrency = dppCurrency,
InvoiceNo = item.InvoiceNo,
VATNo = item.VatNo,
IPONo = item.PONo,
VAT = ppn,
Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
ProductName = item.ProductName,
ReceiptDate = item.ReceiptDate,
SupplierName = item.SupplierName,
UnitName = item.UnitName,
UPONo = item.UPONo,
URNNo = item.URNNo,
IsUseVat = item.UseVat,
CurrencyCode = currencyCode,
Quantity = item.ReceiptQuantity,
Uom = item.Uom
};
reportResult.Reports.Add(reportItem);
}
reportResult.CategorySummaries = reportResult.Reports
.GroupBy(report => new { report.CategoryCode })
.Select(report => new Summary()
{
Category = report.Key.CategoryCode,
SubTotal = report.Sum(sum => sum.Total)
}).OrderBy(order => order.Category).ToList();
reportResult.CurrencySummaries = reportResult.Reports
.GroupBy(report => new { report.CurrencyCode })
.Select(report => new Summary()
{
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
}).OrderBy(order => order.CurrencyCode).ToList();
reportResult.Reports = reportResult.Reports;
reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
#region Old Query
//if (prIds.Count > 0)
//{
// var purchaseRequestQuery = dbContext.PurchaseRequests.AsQueryable();
// if (purchaseRequestQuery.Count() > 0)
// {
// //var purchaseRequests = purchaseRequestQuery.Select(pr => new { pr.Id, pr.CategoryName, pr.CategoryCode }).ToList();
// //prIds = purchaseRequests.Select(pr => pr.Id).ToList();
// //var categories = purchaseRequests.Select(pr => pr.CategoryCode).Distinct().ToList();
// //var urnIds = query.Select(urn => urn.Id).ToList();
// //var urnItems = dbContext.UnitReceiptNoteItems
// // .Include(urnItem => urnItem.UnitReceiptNote)
// // .Where(urnItem => urnIds.Contains(urnItem.URNId) && prIds.Contains(urnItem.PRId))
// // .Select(urnItem => new
// // {
// // urnItem.PRId,
// // urnItem.UnitReceiptNote.DOId,
// // urnItem.UnitReceiptNote.DONo,
// // urnItem.UnitReceiptNote.URNNo,
// // URNId = urnItem.UnitReceiptNote.Id,
// // urnItem.ProductName,
// // urnItem.UnitReceiptNote.ReceiptDate,
// // urnItem.UnitReceiptNote.SupplierName,
// // urnItem.UnitReceiptNote.UnitCode,
// // urnItem.EPODetailId,
// // urnItem.PricePerDealUnit,
// // urnItem.ReceiptQuantity,
// // urnItem.Uom
// // })
// // .ToList();
// //var epoDetailIds = urnItems.Select(urnItem => urnItem.EPODetailId).ToList();
// //var epoItemIds = dbContext.ExternalPurchaseOrderDetails
// // .Include(epoDetail => epoDetail.ExternalPurchaseOrderItem)
// // .Where(epoDetail => epoDetailIds.Contains(epoDetail.Id))
// // .Select(epoDetail => epoDetail.ExternalPurchaseOrderItem.Id)
// // .ToList();
// var epoItems = dbContext.ExternalPurchaseOrderItems
// .Include(epoItem => epoItem.ExternalPurchaseOrder)
// .Where(epoItem => epoItemIds.Contains(epoItem.Id))
// .Select(epoItem => new
// {
// epoItem.PONo,
// epoDetailIds = epoItem.Details.Select(epoDetail => epoDetail.Id).ToList(),
// epoItem.ExternalPurchaseOrder.CurrencyCode,
// epoItem.ExternalPurchaseOrder.UseVat,
// Details = epoItem.Details.Select(epoDetail => new { epoDetail.PricePerDealUnit, epoDetail.Id }).ToList()
// })
// .ToList();
// var unitPaymentOrders = dbContext.UnitPaymentOrderItems
// .Include(upoItem => upoItem.UnitPaymentOrder)
// .Where(upoItem => urnIds.Contains(upoItem.URNId))
// .Select(upoItem => new
// {
// upoItem.URNId,
// upoItem.UnitPaymentOrder.InvoiceNo,
// upoItem.UnitPaymentOrder.UPONo,
// upoItem.UnitPaymentOrder.VatNo
// });
// var currencyCodes = epoItems.Select(epoItem => epoItem.CurrencyCode).Distinct().ToList();
// var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeList(currencyCodes);
// var reportResult = new LocalPurchasingBookReportViewModel();
// foreach (var urnItem in urnItems)
// {
// var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
// var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
// var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
// var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
// var currency = currencies.FirstOrDefault(f => f.Code.Equals(epoItem.CurrencyCode));
// decimal dpp = 0;
// decimal dppCurrency = 0;
// decimal ppn = 0;
// //default IDR
// double currencyRate = 1;
// var currencyCode = "IDR";
// if (currency != null && !currency.Code.Equals("IDR"))
// {
// dppCurrency = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
// currencyRate = currency.Rate.GetValueOrDefault();
// currencyCode = currency.Code;
// }
// else
// dpp = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
// if (epoItem.UseVat)
// ppn = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity * 0.1);
// var reportItem = new PurchasingReport()
// {
// CategoryName = purchaseRequest.CategoryName,
// CategoryCode = purchaseRequest.CategoryCode,
// CurrencyRate = (decimal)currencyRate,
// DONo = urnItem.DONo,
// DPP = dpp,
// DPPCurrency = dppCurrency,
// InvoiceNo = unitPaymentOrder?.InvoiceNo,
// VATNo = unitPaymentOrder?.VatNo,
// IPONo = epoItem.PONo,
// VAT = ppn,
// Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
// ProductName = urnItem.ProductName,
// ReceiptDate = urnItem.ReceiptDate,
// SupplierName = urnItem.SupplierName,
// UnitName = urnItem.UnitCode,
// UPONo = unitPaymentOrder?.UPONo,
// URNNo = urnItem.URNNo,
// IsUseVat = epoItem.UseVat,
// CurrencyCode = currencyCode,
// Quantity = urnItem.ReceiptQuantity,
// Uom = urnItem.Uom
// };
// reportResult.Reports.Add(reportItem);
// }
// reportResult.CategorySummaries = reportResult.Reports
// .GroupBy(report => new { report.CategoryCode })
// .Select(report => new Summary()
// {
// Category = report.Key.CategoryCode,
// SubTotal = report.Sum(sum => sum.Total)
// }).OrderBy(order => order.Category).ToList();
// reportResult.CurrencySummaries = reportResult.Reports
// .GroupBy(report => new { report.CurrencyCode })
// .Select(report => new Summary()
// {
// CurrencyCode = report.Key.CurrencyCode,
// SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
// }).OrderBy(order => order.CurrencyCode).ToList();
// reportResult.Reports = reportResult.Reports.OrderByDescending(order => order.ReceiptDate).ToList();
// reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
// reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
// return reportResult;
// }
//}
#endregion
return(reportResult);
}
private static void SetReportTable(Document document, LocalPurchasingBookReportViewModel viewModel, int timezoneOffset)
{
var table = new PdfPTable(18)
{
WidthPercentage = 97
};
table.SetWidths(new float[] { 5f, 5f, 7f, 6f, 6f, 5f, 5f, 7f, 6f, 5f, 5f, 5f, 5f, 5f, 5f, 6f, 7f, 9f, });
SetReportTableHeader(table);
var cellNoBorderRight = new PdfPCell()
{
BorderWidthRight = 0,
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellNoBorderLeft = new PdfPCell()
{
BorderWidthLeft = 0,
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellAlignRight = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellAlignLeft = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var listCategoryReports = viewModel.Reports.Where(x => x.AccountingUnitName != null).OrderBy(order => order.AccountingLayoutIndex).GroupBy(x => x.AccountingCategoryName).ToList();
var summaryUnit = new Dictionary <string, decimal>();
foreach (var cat in listCategoryReports)
{
var categoryName = cat.Select(x => x.AccountingCategoryName).FirstOrDefault();
cellAlignLeft.Phrase = new Phrase(categoryName, _smallerBoldFont);
cellAlignLeft.Colspan = 18;
table.AddCell(cellAlignLeft);
var totalUnit = new Dictionary <string, decimal>();
var totalCurrency = new Dictionary <string, Dictionary <string, decimal> >();
decimal total = 0;
foreach (var element in cat)
{
cell.Phrase = new Phrase(element.ReceiptDate.AddHours(timezoneOffset).ToString("yyyy-dd-MM"), _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.SupplierCode, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.SupplierName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.Remark, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.URNNo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.InvoiceNo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.UPONo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.CategoryCode + " - " + element.CategoryName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.AccountingUnitName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.PIBDate.AddHours(timezoneOffset).ToString("yyyy-dd-MM"), _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(element.PIBNo, _smallerFont);
table.AddCell(cell);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", element.PIBBM), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", element.PIBIncomeTax), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", element.PIBVat), _smallerFont);
table.AddCell(cellAlignRight);
cell.Phrase = new Phrase(element.PIBImportInfo, _smallerFont);
table.AddCell(cell);
cellNoBorderRight.Phrase = new Phrase(element.CurrencyCode, _smallerFont);
table.AddCell(cellNoBorderRight);
cellNoBorderLeft.Phrase = new Phrase(string.Format("{0:n}", element.DPP), _smallerFont);
table.AddCell(cellNoBorderLeft);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", element.Total), _smallerFont);
table.AddCell(cellAlignRight);
if (totalUnit.ContainsKey(element.AccountingUnitName))
{
totalUnit[element.AccountingUnitName] += element.Total;
}
else
{
totalUnit.Add(element.AccountingUnitName, element.Total);
}
if (totalCurrency.ContainsKey(element.CurrencyCode))
{
totalCurrency[element.CurrencyCode]["DPP"] += element.DPP;
totalCurrency[element.CurrencyCode]["Total"] += element.Total;
}
else
{
totalCurrency.Add(element.CurrencyCode, new Dictionary <string, decimal>()
{
{ "DPP", element.DPP },
{ "Total", element.Total }
});
}
total += element.Total;
}
if (totalCurrency.Count() > 0)
{
cellAlignRight.Phrase = new Phrase("TOTAL " + categoryName, _smallerBoldFont);
}
cellAlignRight.Colspan = 15;
cellAlignRight.Rowspan = totalCurrency.Count();
table.AddCell(cellAlignRight);
cellAlignRight.Colspan = 1;
cellAlignRight.Rowspan = 1;
foreach (var v in totalCurrency)
{
cellNoBorderRight.Phrase = new Phrase(v.Key, _smallerBoldFont);
table.AddCell(cellNoBorderRight);
foreach (var x in v.Value)
{
cellNoBorderLeft.Phrase = new Phrase(string.Format("{0:n}", x.Value), _smallerBoldFont);
table.AddCell(cellNoBorderLeft);
}
}
cellAlignRight.Phrase = new Phrase("", _smallerBoldFont);
cellAlignRight.Colspan = 17;
table.AddCell(cellAlignRight);
cellAlignRight.Colspan = 1;
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", total), _smallerBoldFont);
table.AddCell(cellAlignRight);
if (totalUnit.Count() > 0)
{
foreach (var v in totalUnit)
{
cellAlignRight.Phrase = new Phrase(categoryName, _smallerBoldFont);
cellAlignRight.Colspan = 14;
table.AddCell(cellAlignRight);
cellAlignRight.Colspan = 1;
cell.Colspan = 1;
cell.Phrase = new Phrase(v.Key, _smallerBoldFont);
table.AddCell(cell);
cellNoBorderRight.Phrase = new Phrase("", _smallerBoldFont);
table.AddCell(cellNoBorderRight);
cellNoBorderLeft.Phrase = new Phrase("", _smallerBoldFont);
table.AddCell(cellNoBorderLeft);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value), _smallerBoldFont);
table.AddCell(cellAlignRight);
if (summaryUnit.ContainsKey(v.Key))
{
summaryUnit[v.Key] += v.Value;
}
else
{
summaryUnit.Add(v.Key, v.Value);
}
}
}
}
document.Add(table);
document.Add(new Paragraph("\n"));
var summaryTable = new PdfPTable(5)
{
WidthPercentage = 95,
};
var widthSummaryTable = new List <float>()
{
2f, 1f, 2f, 1f, 3f
};
summaryTable.SetWidths(widthSummaryTable.ToArray());
summaryTable.AddCell(GetCategorySummaryTable(viewModel.CategorySummaries, viewModel.CategorySummaryTotal));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetUnitSummaryTable(summaryUnit));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetCurrencySummaryTable(viewModel.CurrencySummaries));
document.Add(summaryTable);
}
//private static void SetFooter(Document document)
//{
//}
//private static void SetCurrencyTable(Document document, List<Summary> currencySummaries)
//{
//}
//private static void SetCategoryTable(Document document, List<Summary> categorySummaries, decimal categorySummaryTotal)
//{
//}
private static void SetReportTable(Document document, LocalPurchasingBookReportViewModel viewModel, int timezoneOffset)
{
var table = new PdfPTable(16)
{
WidthPercentage = 95
};
var widths = new List <float>();
for (var i = 0; i < 16; i++)
{
if (i == 1 || i == 10)
{
widths.Add(1f);
continue;
}
if (i == 3)
{
widths.Add(3f);
continue;
}
widths.Add(2f);
}
table.SetWidths(widths.ToArray());
SetReportTableHeader(table);
var grouppedByAccountingCategoriNames = viewModel.Reports.Where(x => x.AccountingUnitName != null).OrderBy(order => order.AccountingLayoutIndex).GroupBy(x => x.AccountingCategoryName).ToList();
var cell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellColspan2 = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE,
Colspan = 2
};
var cellAlignRight = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellAlignRightColspan3 = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE,
Colspan = 3
};
var categoryCell = new PdfPCell()
{
BorderWidthTop = 0,
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var totalCell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE,
BorderWidthBottom = 0
};
var totalCellNoBorderTopAndBot = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE,
BorderWidthBottom = 0,
BorderWidthTop = 0
};
var cellNoBorderRight = new PdfPCell()
{
BorderWidthRight = 0,
HorizontalAlignment = Element.ALIGN_CENTER,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var cellNoBorderLeft = new PdfPCell()
{
BorderWidthLeft = 0,
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var totalDPPCell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var totalCategoryUnitCell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_RIGHT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var totalUnitCell = new PdfPCell()
{
HorizontalAlignment = Element.ALIGN_LEFT,
VerticalAlignment = Element.ALIGN_MIDDLE
};
var summaryUnit = new Dictionary <string, decimal>();
foreach (var grouppedAccountingCategory in grouppedByAccountingCategoriNames)
{
var accountingCategoryName = grouppedAccountingCategory.Select(x => x.AccountingCategoryName).FirstOrDefault();
categoryCell.Phrase = new Phrase(accountingCategoryName, _smallBoldFont);
categoryCell.Colspan = 18;
table.AddCell(categoryCell);
var totalUnit = new Dictionary <string, Dictionary <string, decimal> >();
var totalCurrencies = new Dictionary <string, Dictionary <string, decimal> >();
decimal totalIdrDpp = 0;
decimal totalIdr = 0;
decimal totalIdrVat = 0;
decimal totalIdrTax = 0;
foreach (var data in grouppedAccountingCategory)
{
cell.Phrase = new Phrase(data.ReceiptDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("yyyy-dd-MM"), _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.SupplierCode, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.SupplierName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.Remark, _smallerFont);
table.AddCell(cell);
//cell.Phrase = new Phrase(data.IPONo, _smallerFont);
//table.AddCell(cell);
cell.Phrase = new Phrase(data.URNNo, _smallerFont);
table.AddCell(cell);
//cell.Phrase = new Phrase(data.DONo, _smallerFont);
//table.AddCell(cell);
cell.Phrase = new Phrase(data.InvoiceNo, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.VATNo, _smallerFont);
table.AddCell(cell);
//cell.Phrase = new Phrase(data.UPONo, _smallerFont);
if (data.DataSourceSort == 1)
{
cell.Phrase = new Phrase(data.UPONo, _smallerFont);
}
else
{
cell.Phrase = new Phrase(data.CorrectionNo, _smallerFont);
}
table.AddCell(cell);
cell.Phrase = new Phrase(data.CategoryCode + " - " + data.CategoryName, _smallerFont);
table.AddCell(cell);
cell.Phrase = new Phrase(data.AccountingUnitName, _smallerFont);
table.AddCell(cell);
cellNoBorderRight.Phrase = new Phrase(data.CurrencyCode, _smallerFont);
table.AddCell(cellNoBorderRight);
cellNoBorderLeft.Phrase = new Phrase(string.Format("{0:n}", data.DPP), _smallerFont);
table.AddCell(cellNoBorderLeft);
//dppCell.Phrase = new Phrase(data.CurrencyCode + " " + string.Format("{0:n}", data.DPP), _smallerFont);
//table.AddCell(dppCell);
//cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.CurrencyRate), _smallerFont);
//table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.DPPCurrency), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.VAT * data.CurrencyRate), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.IncomeTax * data.CurrencyRate), _smallerFont);
table.AddCell(cellAlignRight);
//var subTotalIdr = data.IncomeTaxBy == "Supplier" ? data.Total + (data.IncomeTax * data.CurrencyRate) : data.Total;
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", data.TotalCurrency), _smallerFont);
table.AddCell(cellAlignRight);
// Units summary
if (totalUnit.ContainsKey(data.AccountingUnitName))
{
totalUnit[data.AccountingUnitName]["DPP"] += data.DPP;
totalUnit[data.AccountingUnitName]["VAT"] += data.VAT * data.CurrencyRate;
totalUnit[data.AccountingUnitName]["TAX"] += data.IncomeTax * data.CurrencyRate;
totalUnit[data.AccountingUnitName]["DPPCurrency"] += data.DPPCurrency;
totalUnit[data.AccountingUnitName]["TOTAL"] += data.TotalCurrency;
}
else
{
totalUnit.Add(data.AccountingUnitName, new Dictionary <string, decimal>()
{
{ "DPP", data.DPP },
{ "VAT", data.VAT * data.CurrencyRate },
{ "TAX", data.IncomeTax * data.CurrencyRate },
{ "DPPCurrency", data.DPPCurrency },
{ "TOTAL", data.TotalCurrency }
});
}
if (summaryUnit.ContainsKey(data.AccountingUnitName))
{
summaryUnit[data.AccountingUnitName] += data.TotalCurrency;
}
else
{
summaryUnit.Add(data.AccountingUnitName, data.TotalCurrency);
}
//var dpp = data.IncomeTaxBy == "Supplier" ? data.DPP + data.IncomeTax : data.DPP;
//var dppCurrency = data.IncomeTaxBy == "Supplier" ? data.DPPCurrency + (data.IncomeTax * data.CurrencyRate) : data.DPPCurrency;
// Currencies summary
if (totalCurrencies.ContainsKey(data.CurrencyCode))
{
totalCurrencies[data.CurrencyCode]["DPP"] += data.DPP;
totalCurrencies[data.CurrencyCode]["VAT"] += data.VAT * data.CurrencyRate;
totalCurrencies[data.CurrencyCode]["TAX"] += data.IncomeTax * data.CurrencyRate;
totalCurrencies[data.CurrencyCode]["DPPCurrency"] += data.DPPCurrency;
totalCurrencies[data.CurrencyCode]["TOTAL"] += data.TotalCurrency;
}
else
{
totalCurrencies.Add(data.CurrencyCode, new Dictionary <string, decimal>()
{
{ "DPP", data.DPP },
{ "VAT", data.VAT * data.CurrencyRate },
{ "TAX", data.IncomeTax * data.CurrencyRate },
{ "DPPCurrency", data.DPPCurrency },
{ "TOTAL", data.TotalCurrency }
});
}
totalIdr += data.TotalCurrency;
totalIdrDpp += data.DPPCurrency;
totalIdrTax += data.IncomeTax * data.CurrencyRate;
totalIdrVat += data.VAT * data.CurrencyRate;
}
//var cellGrandTotal = new PdfPCell()
//{
// HorizontalAlignment = Element.ALIGN_RIGHT,
// VerticalAlignment = Element.ALIGN_CENTER,
// Colspan = 16
//};
//cellGrandTotal.Phrase = new Phrase("Grand Total", _smallerBoldFont);
//table.AddCell(cellGrandTotal);
//cellGrandTotal.Phrase = new Phrase(string.Format("{0:n}", grandTotal), _smallerBoldFont);
//table.AddCell(cellGrandTotal);
totalCell.Phrase = new Phrase($"TOTAL {accountingCategoryName}", _smallBoldFont);
totalCell.Colspan = 10;
table.AddCell(totalCell);
foreach (var totalCurrency in totalCurrencies)
{
cellNoBorderRight.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Key), _smallerFont);
table.AddCell(cellNoBorderRight);
cellNoBorderLeft.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Value["DPP"]), _smallerFont);
table.AddCell(cellNoBorderLeft);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Value["DPPCurrency"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Value["VAT"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Value["TAX"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalCurrency.Value["TOTAL"]), _smallerBoldFont);
table.AddCell(cellAlignRight);
totalCellNoBorderTopAndBot.Phrase = new Phrase();
totalCellNoBorderTopAndBot.Colspan = 10;
table.AddCell(totalCellNoBorderTopAndBot);
}
cellAlignRightColspan3.Phrase = new Phrase(string.Format("{0:n}", totalIdrDpp), _smallerFont);
table.AddCell(cellAlignRightColspan3);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalIdrVat), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalIdrTax), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", totalIdr), _smallerBoldFont);
table.AddCell(cellAlignRight);
if (totalUnit.Count() > 0)
{
foreach (var v in totalUnit)
{
totalCategoryUnitCell.Phrase = new Phrase($"{accountingCategoryName}", _smallBoldFont);
totalCategoryUnitCell.Colspan = 8;
table.AddCell(totalCategoryUnitCell);
cellColspan2.Phrase = new Phrase($"{v.Key} ", _smallBoldFont);
table.AddCell(cellColspan2);
cellAlignRightColspan3.Phrase = new Phrase(string.Format("{0:n}", v.Value["DPPCurrency"]), _smallerFont);
table.AddCell(cellAlignRightColspan3);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["VAT"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["TAX"]), _smallerFont);
table.AddCell(cellAlignRight);
cellAlignRight.Phrase = new Phrase(string.Format("{0:n}", v.Value["TOTAL"]), _smallerBoldFont);
table.AddCell(cellAlignRight);
//totalUnitCell.Phrase = new Phrase(string.Format("{0:n}", v.Value), _smallerFont);
//totalUnitCell.Colspan = 6;
//table.AddCell(totalUnitCell);
}
}
}
document.Add(table);
document.Add(new Paragraph("\n"));
var summaryTable = new PdfPTable(5)
{
WidthPercentage = 95,
};
var widthSummaryTable = new List <float>()
{
2f, 1f, 2f, 1f, 4f
};
summaryTable.SetWidths(widthSummaryTable.ToArray());
summaryTable.AddCell(GetCategorySummaryTable(viewModel.CategorySummaries, viewModel.CategorySummaryTotal));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetUnitSummaryTable(summaryUnit));
summaryTable.AddCell(new PdfPCell()
{
Border = Rectangle.NO_BORDER
});
summaryTable.AddCell(GetCurrencySummaryTable(viewModel.CurrencySummaries));
document.Add(summaryTable);
}
public Tuple <List <LocalPurchasingBookReportViewModel>, int> GetReport(string no, string unit, string category, DateTime?dateFrom, DateTime?dateTo)
{
DateTime d1 = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime d2 = dateTo == null ? DateTime.Now : (DateTime)dateTo;
string DateFrom = d1.ToString("yyyy-MM-dd");
string DateTo = d2.ToString("yyyy-MM-dd");
string _cat, _no, _unit = "";
if (category != null)
{
_cat = " and a.categorycode= '" + category + "'";
}
else
{
_cat = "";
}
if (unit != null)
{
_unit = " and g.unitcode= '" + unit + "'";
}
else
{
_unit = "";
}
if (no != null)
{
_no = " and URNNo= '" + no + "'";
}
else
{
_no = "";
}
List <LocalPurchasingBookReportViewModel> reportData = new List <LocalPurchasingBookReportViewModel>();
string connectionString = APIEndpoint.ConnectionString;
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(
"declare @EndDate datetime = '" + DateTo + "' " +
"declare @StartDate datetime = '" + DateFrom + "' " +
"select URNNo,ReceiptDate,ProductName,UnitName,CategoryName,useVat,VatNo,sum(dpp)Dpp,sum(ppn) Ppn from(select g.URNNo,g.ReceiptDate,h.productname,g.unitname,a.CategoryName,c.UseVat, " +
" case when ispaid = 0 then '-' else (select top(1)VatNo from UnitPaymentOrders o join unitpaymentorderItems uo on o.id = uo.UPOId where uo.urnid = g.Id) end as VatNo," +
" h.priceperdealunit* h.receiptquantity* c.CurrencyRate as dpp,h.priceperdealunit * h.receiptquantity* c.CurrencyRate* 10/100 as ppn" +
" from " +
" internalpurchaseorders a " +
" join ExternalPurchaseOrderItems b on a.id = b.POId " +
" join ExternalPurchaseOrders c on c.Id = b.EPOId " +
" join Externalpurchaseorderdetails d on b.id = d.epoitemid " +
" join deliveryorderitems e on c.id = e.epoid " +
" join deliveryorders f on f.id = e.DOId " +
" join UnitReceiptNotes g on f.id = g.DOId " +
" join UnitReceiptNoteItems h on g.id = h.urnid and h.EPODetailId=d.Id " +
" where g.IsDeleted = 0 and URNNo like '%BPL%' and receiptdate between @StartDate and @EndDate " + _cat + _no + _unit +
" ) as data " +
" group by URNNo, ReceiptDate, ProductName, UnitName, CategoryName, useVat, VatNo, DPP, PPN", conn))
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow data in dSet.Tables[0].Rows)
{
LocalPurchasingBookReportViewModel view = new LocalPurchasingBookReportViewModel
{
uRNNo = data["URNNo"].ToString(),
receiptDate = (DateTimeOffset)data["ReceiptDate"],
productName = data["ProductName"].ToString(),
unitName = data["UnitName"].ToString(),
categoryName = data["CategoryName"].ToString(),
invoiceNo = data["VatNo"].ToString(),
dpp = Convert.ToDecimal(data["DPP"].ToString()),
ppn = Convert.ToDecimal(data["PPN"].ToString()),
useVat = (bool)data["useVat"],
};
reportData.Add(view);
}
}
conn.Close();
}
return(Tuple.Create(reportData, reportData.Count));
}
//public async Task<LocalPurchasingBookReportViewModel> GetReportData(string no, string unit, string categoryCode, DateTime? dateFrom, DateTime? dateTo)
//{
// var d1 = dateFrom.GetValueOrDefault().ToUniversalTime();
// var d2 = (dateTo.HasValue ? dateTo.Value : DateTime.Now).ToUniversalTime();
// var query = from urnWithItem in dbContext.UnitReceiptNoteItems
// join pr in dbContext.PurchaseRequests on urnWithItem.PRId equals pr.Id into joinPurchaseRequest
// from urnPR in joinPurchaseRequest.DefaultIfEmpty()
// join epoDetail in dbContext.ExternalPurchaseOrderDetails on urnWithItem.EPODetailId equals epoDetail.Id into joinExternalPurchaseOrder
// from urnEPODetail in joinExternalPurchaseOrder.DefaultIfEmpty()
// join upoItem in dbContext.UnitPaymentOrderItems on urnWithItem.URNId equals upoItem.URNId into joinUnitPaymentOrder
// from urnUPOItem in joinUnitPaymentOrder.DefaultIfEmpty()
// where urnWithItem.UnitReceiptNote.ReceiptDate >= d1 && urnWithItem.UnitReceiptNote.ReceiptDate <= d2 && urnWithItem.UnitReceiptNote.SupplierIsImport
// select new
// {
// // PR Info
// urnPR.CategoryCode,
// urnPR.CategoryName,
// urnWithItem.PRId,
// urnWithItem.UnitReceiptNote.DOId,
// urnWithItem.UnitReceiptNote.DONo,
// urnWithItem.UnitReceiptNote.URNNo,
// URNId = urnWithItem.UnitReceiptNote.Id,
// urnWithItem.ProductName,
// urnWithItem.UnitReceiptNote.ReceiptDate,
// urnWithItem.UnitReceiptNote.SupplierName,
// urnWithItem.UnitReceiptNote.SupplierCode,
// urnWithItem.UnitReceiptNote.UnitCode,
// urnWithItem.UnitReceiptNote.UnitName,
// urnWithItem.EPODetailId,
// urnWithItem.PricePerDealUnit,
// urnWithItem.ReceiptQuantity,
// urnWithItem.Uom,
// // EPO Info
// urnEPODetail.ExternalPurchaseOrderItem.PONo,
// urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.UseVat,
// EPOPricePerDealUnit = urnEPODetail.PricePerDealUnit,
// urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.CurrencyCode,
// // UPO Info
// urnUPOItem.UnitPaymentOrder.InvoiceNo,
// urnUPOItem.UnitPaymentOrder.UPONo,
// urnUPOItem.UnitPaymentOrder.VatNo,
// urnUPOItem.UnitPaymentOrder.PibNo
// };
// //var query = dbSet
// // .Where(urn => urn.ReceiptDate >= d1.ToUniversalTime() && urn.ReceiptDate.ToUniversalTime() <= d2 && !urn.SupplierIsImport);
// if (!string.IsNullOrWhiteSpace(no))
// query = query.Where(urn => urn.URNNo == no);
// if (!string.IsNullOrWhiteSpace(unit))
// query = query.Where(urn => urn.UnitCode == unit);
// //var prIds = query.SelectMany(urn => urn.Items.Select(s => s.PRId)).ToList();
// if (!string.IsNullOrWhiteSpace(categoryCode))
// query = query.Where(urn => urn.CategoryCode == categoryCode);
// var queryResult = query.OrderByDescending(item => item.ReceiptDate).ToList();
// //var currencyCodes = queryResult.Select(item => item.CurrencyCode).ToList();
// //var receiptDates = queryResult.Select(item => item.ReceiptDate).ToList();
// var currencyTuples = queryResult.Select(item => new Tuple<string, DateTimeOffset>(item.CurrencyCode, item.ReceiptDate));
// var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeDateList(currencyTuples);
// var reportResult = new LocalPurchasingBookReportViewModel();
// foreach (var item in queryResult)
// {
// //var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
// //var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
// //var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
// //var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
// var currency = currencies.FirstOrDefault(f => f.Code == item.CurrencyCode);
// decimal dpp = 0;
// decimal dppCurrency = 0;
// decimal ppn = 0;
// //default IDR
// double currencyRate = 1;
// var currencyCode = "IDR";
// if (currency != null && !currency.Code.Equals("IDR"))
// {
// dppCurrency = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
// currencyRate = currency.Rate.GetValueOrDefault();
// currencyCode = currency.Code;
// }
// else
// dpp = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
// if (item.UseVat)
// ppn = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity * 0.1);
// var reportItem = new PurchasingReport()
// {
// CategoryName = item.CategoryName,
// CategoryCode = item.CategoryCode,
// CurrencyRate = (decimal)currencyRate,
// DONo = item.DONo,
// DPP = dpp,
// DPPCurrency = dppCurrency,
// InvoiceNo = item.InvoiceNo,
// VATNo = item.VatNo,
// IPONo = item.PONo,
// VAT = ppn,
// Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
// ProductName = item.ProductName,
// ReceiptDate = item.ReceiptDate,
// SupplierName = item.SupplierCode + " - " + item.SupplierName,
// UnitName = item.UnitName,
// UPONo = item.UPONo,
// URNNo = item.URNNo,
// IsUseVat = item.UseVat,
// CurrencyCode = currencyCode,
// PIBNo = item.PibNo
// };
// reportItem.PIBBM = reportItem.Total * 0.1m;
// reportResult.Reports.Add(reportItem);
// }
// reportResult.CategorySummaries = reportResult.Reports
// .GroupBy(report => new { report.CategoryCode })
// .Select(report => new Summary()
// {
// Category = report.Key.CategoryCode,
// SubTotal = report.Sum(sum => sum.Total)
// }).OrderBy(order => order.Category).ToList();
// reportResult.CurrencySummaries = reportResult.Reports
// .GroupBy(report => new { report.CurrencyCode })
// .Select(report => new Summary()
// {
// CurrencyCode = report.Key.CurrencyCode,
// SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
// }).OrderBy(order => order.CurrencyCode).ToList();
// reportResult.Reports = reportResult.Reports;
// reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
// reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
// #region Old Query
// //if (prIds.Count > 0)
// //{
// // var purchaseRequestQuery = dbContext.PurchaseRequests.AsQueryable();
// // if (purchaseRequestQuery.Count() > 0)
// // {
// // //var purchaseRequests = purchaseRequestQuery.Select(pr => new { pr.Id, pr.CategoryName, pr.CategoryCode }).ToList();
// // //prIds = purchaseRequests.Select(pr => pr.Id).ToList();
// // //var categories = purchaseRequests.Select(pr => pr.CategoryCode).Distinct().ToList();
// // //var urnIds = query.Select(urn => urn.Id).ToList();
// // //var urnItems = dbContext.UnitReceiptNoteItems
// // // .Include(urnItem => urnItem.UnitReceiptNote)
// // // .Where(urnItem => urnIds.Contains(urnItem.URNId) && prIds.Contains(urnItem.PRId))
// // // .Select(urnItem => new
// // // {
// // // urnItem.PRId,
// // // urnItem.UnitReceiptNote.DOId,
// // // urnItem.UnitReceiptNote.DONo,
// // // urnItem.UnitReceiptNote.URNNo,
// // // URNId = urnItem.UnitReceiptNote.Id,
// // // urnItem.ProductName,
// // // urnItem.UnitReceiptNote.ReceiptDate,
// // // urnItem.UnitReceiptNote.SupplierName,
// // // urnItem.UnitReceiptNote.UnitCode,
// // // urnItem.EPODetailId,
// // // urnItem.PricePerDealUnit,
// // // urnItem.ReceiptQuantity,
// // // urnItem.Uom
// // // })
// // // .ToList();
// // //var epoDetailIds = urnItems.Select(urnItem => urnItem.EPODetailId).ToList();
// // //var epoItemIds = dbContext.ExternalPurchaseOrderDetails
// // // .Include(epoDetail => epoDetail.ExternalPurchaseOrderItem)
// // // .Where(epoDetail => epoDetailIds.Contains(epoDetail.Id))
// // // .Select(epoDetail => epoDetail.ExternalPurchaseOrderItem.Id)
// // // .ToList();
// // var epoItems = dbContext.ExternalPurchaseOrderItems
// // .Include(epoItem => epoItem.ExternalPurchaseOrder)
// // .Where(epoItem => epoItemIds.Contains(epoItem.Id))
// // .Select(epoItem => new
// // {
// // epoItem.PONo,
// // epoDetailIds = epoItem.Details.Select(epoDetail => epoDetail.Id).ToList(),
// // epoItem.ExternalPurchaseOrder.CurrencyCode,
// // epoItem.ExternalPurchaseOrder.UseVat,
// // Details = epoItem.Details.Select(epoDetail => new { epoDetail.PricePerDealUnit, epoDetail.Id }).ToList()
// // })
// // .ToList();
// // var unitPaymentOrders = dbContext.UnitPaymentOrderItems
// // .Include(upoItem => upoItem.UnitPaymentOrder)
// // .Where(upoItem => urnIds.Contains(upoItem.URNId))
// // .Select(upoItem => new
// // {
// // upoItem.URNId,
// // upoItem.UnitPaymentOrder.InvoiceNo,
// // upoItem.UnitPaymentOrder.UPONo,
// // upoItem.UnitPaymentOrder.VatNo
// // });
// // var currencyCodes = epoItems.Select(epoItem => epoItem.CurrencyCode).Distinct().ToList();
// // var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeList(currencyCodes);
// // var reportResult = new LocalPurchasingBookReportViewModel();
// // foreach (var urnItem in urnItems)
// // {
// // var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
// // var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
// // var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
// // var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
// // var currency = currencies.FirstOrDefault(f => f.Code.Equals(epoItem.CurrencyCode));
// // decimal dpp = 0;
// // decimal dppCurrency = 0;
// // decimal ppn = 0;
// // //default IDR
// // double currencyRate = 1;
// // var currencyCode = "IDR";
// // if (currency != null && !currency.Code.Equals("IDR"))
// // {
// // dppCurrency = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
// // currencyRate = currency.Rate.GetValueOrDefault();
// // currencyCode = currency.Code;
// // }
// // else
// // dpp = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity);
// // if (epoItem.UseVat)
// // ppn = (decimal)(epoDetail.PricePerDealUnit * urnItem.ReceiptQuantity * 0.1);
// // var reportItem = new PurchasingReport()
// // {
// // CategoryName = purchaseRequest.CategoryName,
// // CategoryCode = purchaseRequest.CategoryCode,
// // CurrencyRate = (decimal)currencyRate,
// // DONo = urnItem.DONo,
// // DPP = dpp,
// // DPPCurrency = dppCurrency,
// // InvoiceNo = unitPaymentOrder?.InvoiceNo,
// // VATNo = unitPaymentOrder?.VatNo,
// // IPONo = epoItem.PONo,
// // VAT = ppn,
// // Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
// // ProductName = urnItem.ProductName,
// // ReceiptDate = urnItem.ReceiptDate,
// // SupplierName = urnItem.SupplierName,
// // UnitName = urnItem.UnitCode,
// // UPONo = unitPaymentOrder?.UPONo,
// // URNNo = urnItem.URNNo,
// // IsUseVat = epoItem.UseVat,
// // CurrencyCode = currencyCode,
// // Quantity = urnItem.ReceiptQuantity,
// // Uom = urnItem.Uom
// // };
// // reportResult.Reports.Add(reportItem);
// // }
// // reportResult.CategorySummaries = reportResult.Reports
// // .GroupBy(report => new { report.CategoryCode })
// // .Select(report => new Summary()
// // {
// // Category = report.Key.CategoryCode,
// // SubTotal = report.Sum(sum => sum.Total)
// // }).OrderBy(order => order.Category).ToList();
// // reportResult.CurrencySummaries = reportResult.Reports
// // .GroupBy(report => new { report.CurrencyCode })
// // .Select(report => new Summary()
// // {
// // CurrencyCode = report.Key.CurrencyCode,
// // SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
// // }).OrderBy(order => order.CurrencyCode).ToList();
// // reportResult.Reports = reportResult.Reports.OrderByDescending(order => order.ReceiptDate).ToList();
// // reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
// // reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
// // return reportResult;
// // }
// //}
// #endregion
// return reportResult;
//}
public async Task <LocalPurchasingBookReportViewModel> GetReportData(string no, string unit, string categoryCode, DateTime?dateFrom, DateTime?dateTo)
{
var d1 = dateFrom.GetValueOrDefault().ToUniversalTime();
var d2 = (dateTo.HasValue ? dateTo.Value : DateTime.Now).ToUniversalTime();
var query = from urnWithItem in dbContext.UnitReceiptNoteItems
join pr in dbContext.PurchaseRequests on urnWithItem.PRId equals pr.Id into joinPurchaseRequest
from urnPR in joinPurchaseRequest.DefaultIfEmpty()
join epoDetail in dbContext.ExternalPurchaseOrderDetails on urnWithItem.EPODetailId equals epoDetail.Id into joinExternalPurchaseOrder
from urnEPODetail in joinExternalPurchaseOrder.DefaultIfEmpty()
join upoItem in dbContext.UnitPaymentOrderItems on urnWithItem.URNId equals upoItem.URNId into joinUnitPaymentOrder
from urnUPOItem in joinUnitPaymentOrder.DefaultIfEmpty()
where urnWithItem.UnitReceiptNote.ReceiptDate >= d1 && urnWithItem.UnitReceiptNote.ReceiptDate <= d2 && urnWithItem.UnitReceiptNote.SupplierIsImport
select new
{
// PR Info
urnPR.CategoryCode,
urnPR.CategoryName,
urnWithItem.PRId,
urnWithItem.UnitReceiptNote.DOId,
urnWithItem.UnitReceiptNote.DONo,
urnWithItem.UnitReceiptNote.URNNo,
URNId = urnWithItem.UnitReceiptNote.Id,
urnWithItem.ProductName,
urnWithItem.UnitReceiptNote.ReceiptDate,
urnWithItem.UnitReceiptNote.SupplierName,
urnWithItem.UnitReceiptNote.SupplierCode,
urnWithItem.UnitReceiptNote.UnitCode,
urnWithItem.UnitReceiptNote.UnitName,
urnWithItem.EPODetailId,
urnWithItem.PricePerDealUnit,
urnWithItem.ReceiptQuantity,
urnWithItem.Uom,
// EPO Info
urnEPODetail.ExternalPurchaseOrderItem.PONo,
urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.UseVat,
EPOPricePerDealUnit = urnEPODetail.PricePerDealUnit,
urnEPODetail.ExternalPurchaseOrderItem.ExternalPurchaseOrder.CurrencyCode,
// UPO Info
urnUPOItem.UnitPaymentOrder.InvoiceNo,
urnUPOItem.UnitPaymentOrder.UPONo,
urnUPOItem.UnitPaymentOrder.VatNo,
urnUPOItem.UnitPaymentOrder.PibNo,
urnUPOItem.UnitPaymentOrder.PibDate,
urnPR.Remark
};
//var query = dbSet
// .Where(urn => urn.ReceiptDate >= d1.ToUniversalTime() && urn.ReceiptDate.ToUniversalTime() <= d2 && !urn.SupplierIsImport);
query = query.Where(urn => urn.CurrencyCode != "IDR");
if (!string.IsNullOrWhiteSpace(no))
{
query = query.Where(urn => urn.URNNo == no);
}
if (!string.IsNullOrWhiteSpace(unit))
{
query = query.Where(urn => urn.UnitCode == unit);
}
//var prIds = query.SelectMany(urn => urn.Items.Select(s => s.PRId)).ToList();
if (!string.IsNullOrWhiteSpace(categoryCode))
{
query = query.Where(urn => urn.CategoryCode == categoryCode);
}
var queryResult = query.OrderByDescending(item => item.ReceiptDate).ToList();
//var currencyCodes = queryResult.Select(item => item.CurrencyCode).ToList();
//var receiptDates = queryResult.Select(item => item.ReceiptDate).ToList();
var currencyTuples = queryResult.GroupBy(item => new { item.CurrencyCode, item.ReceiptDate }).Select(item => new Tuple <string, DateTimeOffset>(item.Key.CurrencyCode, item.Key.ReceiptDate));
var currencies = await _currencyProvider.GetCurrencyByCurrencyCodeDateList(currencyTuples);
var reportResult = new LocalPurchasingBookReportViewModel();
foreach (var item in queryResult)
{
//var purchaseRequest = purchaseRequests.FirstOrDefault(f => f.Id.Equals(urnItem.PRId));
//var unitPaymentOrder = unitPaymentOrders.FirstOrDefault(f => f.URNId.Equals(urnItem.URNId));
//var epoItem = epoItems.FirstOrDefault(f => f.epoDetailIds.Contains(urnItem.EPODetailId));
//var epoDetail = epoItem.Details.FirstOrDefault(f => f.Id.Equals(urnItem.EPODetailId));
var currency = currencies.FirstOrDefault(f => f.Code == item.CurrencyCode);
decimal dpp = 0;
decimal dppCurrency = 0;
decimal ppn = 0;
//default IDR
double currencyRate = 1;
var currencyCode = "IDR";
if (currency != null && !currency.Code.Equals("IDR"))
{
currencyRate = currency.Rate.GetValueOrDefault();
dpp = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
dppCurrency = dpp * (decimal)currencyRate;
currencyCode = currency.Code;
}
else
{
dpp = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity);
}
if (item.UseVat)
{
ppn = (decimal)(item.EPOPricePerDealUnit * item.ReceiptQuantity * 0.1);
}
var reportItem = new PurchasingReport()
{
CategoryName = item.CategoryName,
CategoryCode = item.CategoryCode,
CurrencyRate = (decimal)currencyRate,
DONo = item.DONo,
DPP = dpp,
DPPCurrency = dppCurrency,
InvoiceNo = item.InvoiceNo,
VATNo = item.VatNo,
IPONo = item.PONo,
VAT = ppn,
Total = (dpp + dppCurrency + ppn) * (decimal)currencyRate,
ProductName = item.ProductName,
ReceiptDate = item.ReceiptDate,
SupplierName = item.SupplierCode + " - " + item.SupplierName,
UnitName = item.UnitName,
UnitCode = item.UnitCode,
UPONo = item.UPONo,
URNNo = item.URNNo,
IsUseVat = item.UseVat,
CurrencyCode = currencyCode,
PIBNo = item.PibNo,
PIBDate = item.PibDate,
Remark = item.Remark,
Quantity = item.ReceiptQuantity
};
reportItem.PIBBM = reportItem.Total * 0.1m;
reportResult.Reports.Add(reportItem);
}
reportResult.CategorySummaries = reportResult.Reports
.GroupBy(report => new { report.CategoryCode })
.Select(report => new Summary()
{
Category = report.Key.CategoryCode,
SubTotal = report.Sum(sum => sum.Total)
}).OrderBy(order => order.Category).ToList();
reportResult.CurrencySummaries = reportResult.Reports
.GroupBy(report => new { report.CurrencyCode })
.Select(report => new Summary()
{
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.DPP + sum.DPPCurrency + sum.VAT)
}).OrderBy(order => order.CurrencyCode).ToList();
reportResult.Reports = reportResult.Reports;
reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.Total);
reportResult.CategorySummaryTotal = reportResult.CategorySummaries.Sum(categorySummary => categorySummary.SubTotal);
return(reportResult);
}
