private async Task <UnpaidDispositionReportDetailViewModel> GetReportData(int categoryId, int accountingUnitId, int divisionId, DateTimeOffset?dateTo, bool isImport, bool isForeignCurrency)
{
//var dateStart = dateFrom.GetValueOrDefault().ToUniversalTime();
var dateEnd = (dateTo.HasValue ? dateTo.Value : DateTime.MaxValue).ToUniversalTime();
var query = from pdItems in _dbContext.PurchasingDispositionItems
join pds in _dbContext.PurchasingDispositions on pdItems.PurchasingDispositionId equals pds.Id into joinPurchasingDispositions
from pd in joinPurchasingDispositions.DefaultIfEmpty()
join pdDetailItems in _dbContext.PurchasingDispositionDetails on pdItems.Id equals pdDetailItems.PurchasingDispositionItemId into joinPurchasingDispositionDetails
from pdDetailItem in joinPurchasingDispositionDetails.DefaultIfEmpty()
join urnItems in _dbContext.UnitReceiptNoteItems on pdItems.EPOId equals urnItems.EPOId.ToString() into joinUnitReceiptNoteItems
from urnItem in joinUnitReceiptNoteItems.DefaultIfEmpty()
join upoItems in _dbContext.UnitPaymentOrderItems on urnItem.Id equals upoItems.URNId into joinUnitPaymentOrderItems
from upoItem in joinUnitPaymentOrderItems.DefaultIfEmpty()
join upos in _dbContext.UnitPaymentOrders on upoItem.UPOId equals upos.Id into joinUnitPaymentOrders
from upo in joinUnitPaymentOrders.DefaultIfEmpty()
join epos in _dbContext.ExternalPurchaseOrders on pdItems.EPOId equals epos.Id.ToString() into joinExternalPurchaseOrders
from epo in joinExternalPurchaseOrders.DefaultIfEmpty()
where pd.PaymentDueDate <= dateEnd && pd.IsPaid == false && epo.SupplierIsImport == isImport
select new
{
pdItems.Id,
pd.CreatedUtc,
pd.IsPaid,
pd.DispositionNo,
pd.PaymentDueDate,
pd.DivisionId,
pd.DivisionCode,
pd.DivisionName,
pd.CurrencyId,
pd.CurrencyCode,
pd.CurrencyRate,
pd.CategoryId,
pd.CategoryCode,
pd.CategoryName,
pd.SupplierName,
pd.DPP,
pd.IncomeTaxBy,
pd.IncomeTaxValue,
pd.VatValue,
pdDetailItem.UnitId,
pdDetailItem.UnitCode,
pdDetailItem.UnitName,
urnItem.UnitReceiptNote.URNNo,
upoItem.UnitPaymentOrder.UPONo,
upoItem.UnitPaymentOrder.InvoiceNo,
epo.SupplierIsImport
};
query = query.GroupBy(x => x.Id).Select(y => y.First());
if (!isForeignCurrency && !isImport)
{
query = query.Where(x => x.CurrencyCode == "IDR");
}
else if (isForeignCurrency)
{
query = query.Where(x => x.CurrencyCode != "IDR");
}
if (accountingUnitId > 0)
{
var unitFilterIds = await _currencyProvider.GetUnitsIdsByAccountingUnitId(accountingUnitId);
if (unitFilterIds.Count() > 0)
{
query = query.Where(x => unitFilterIds.Contains(x.UnitId));
}
}
//var categoryFilterIds = await _currencyProvider.GetCategoryIdsByAccountingCategoryId(accountingCategoryId);
//if (categoryFilterIds.Count() > 0)
// query = query.Where(x => categoryFilterIds.Contains(x.CategoryId));
if (categoryId > 0)
{
query = query.Where(x => x.CategoryId == categoryId.ToString());
}
if (divisionId > 0)
{
query = query.Where(x => x.DivisionId == divisionId.ToString());
}
var queryResult = query.OrderByDescending(x => x.PaymentDueDate).ToList();
var unitIds = queryResult.Select(item =>
{
int.TryParse(item.UnitId, out var unitId);
return(unitId);
}).Distinct().ToList();
var units = await _currencyProvider.GetUnitsByUnitIds(unitIds);
var accountingUnits = await _currencyProvider.GetAccountingUnitsByUnitIds(unitIds);
var itemCategoryIds = queryResult.Select(item =>
{
int.TryParse(item.CategoryId, out var itemCategoryId);
return(itemCategoryId);
}).Distinct().ToList();
var categories = await _currencyProvider.GetCategoriesByCategoryIds(itemCategoryIds);
var accountingCategories = await _currencyProvider.GetAccountingCategoriesByCategoryIds(itemCategoryIds);
var reportResult = new UnpaidDispositionReportDetailViewModel();
foreach (var item in queryResult)
{
int.TryParse(item.UnitId, out var unitId);
var unit = units.FirstOrDefault(element => element.Id == unitId);
var accountingUnit = new AccountingUnit();
if (unit != null)
{
accountingUnit = accountingUnits.FirstOrDefault(element => element.Id == unit.AccountingUnitId);
}
//int.TryParse(item.CategoryId, out var itemCategoryId);
//var category = categories.FirstOrDefault(element => element.Id == itemCategoryId);
//var accountingCategory = new AccountingCategory();
//if (category != null)
//{
// accountingCategory = accountingCategories.FirstOrDefault(element => element.Id == category.AccountingCategoryId);
//}
var category = _categories.FirstOrDefault(_category => _category.Id.ToString() == item.CategoryId);
var categoryLayoutIndex = 0;
if (category != null)
{
categoryLayoutIndex = category.ReportLayoutIndex;
}
double total = 0;
double totalCurrency = 0;
if (item.IncomeTaxBy == "Supplier")
{
total = item.DPP + item.VatValue - item.IncomeTaxValue;
totalCurrency = (item.DPP + item.VatValue - item.IncomeTaxValue) * item.CurrencyRate;
}
else
{
total = item.DPP + item.VatValue;
totalCurrency = (item.DPP + item.VatValue) * item.CurrencyRate;
}
var reportItem = new DispositionReport()
{
DispositionNo = item.DispositionNo,
DispositionDate = item.CreatedUtc,
CategoryId = item.CategoryId,
CategoryName = item.CategoryName,
CategoryCode = item.CategoryCode,
CurrencyId = item.CurrencyId,
CurrencyCode = item.CurrencyCode,
CurrencyRate = (decimal)item.CurrencyRate,
//AccountingCategoryName = accountingCategory.Name,
//AccountingCategoryCode = accountingCategory.Code,
//AccountingLayoutIndex = accountingCategory.AccountingLayoutIndex,
DPP = (decimal)item.DPP,
DPPCurrency = (decimal)(item.DPP * item.CurrencyRate),
InvoiceNo = item.InvoiceNo,
VAT = (decimal)item.VatValue,
Total = (decimal)total,
TotalCurrency = (decimal)totalCurrency,
SupplierName = item.SupplierName,
UnitId = item.UnitId,
UnitName = item.UnitName,
UnitCode = item.UnitCode,
AccountingUnitName = accountingUnit.Name,
AccountingUnitCode = accountingUnit.Code,
UPONo = item.UPONo,
URNNo = item.URNNo,
IncomeTax = (decimal)item.IncomeTaxValue,
IncomeTaxBy = item.IncomeTaxBy,
PaymentDueDate = item.PaymentDueDate.Date,
DivisionName = item.DivisionName,
CategoryLayoutIndex = categoryLayoutIndex,
};
reportResult.Reports.Add(reportItem);
}
reportResult.UnitSummaries = reportResult.Reports
.GroupBy(report => new { report.AccountingUnitName, report.CurrencyCode })
.Select(report => new Summary()
{
Name = report.Key.AccountingUnitName,
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.Total),
SubTotalCurrency = report.Sum(sum => sum.TotalCurrency),
//AccountingLayoutIndex = report.Select(item => item.AccountingLayoutIndex).FirstOrDefault()
})
.OrderBy(report => report.Name).ToList();
reportResult.CurrencySummaries = reportResult.Reports
.GroupBy(report => new { report.CurrencyCode })
.Select(report => new Summary()
{
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.Total),
SubTotalCurrency = report.Sum(sum => sum.TotalCurrency)
}).OrderBy(order => order.CurrencyCode).ToList();
reportResult.CategorySummaries = reportResult.Reports
.GroupBy(report => new { report.CategoryName, report.CategoryId, report.CurrencyCode })
.Select(report => new Summary()
{
CategoryName = report.Key.CategoryName,
CategoryId = report.Key.CategoryId,
CurrencyCode = report.Key.CurrencyCode,
SubTotal = report.Sum(sum => sum.Total)
})
.OrderBy(order => order.CategoryName).ToList();
reportResult.Reports = reportResult.Reports.OrderBy(order => order.CategoryLayoutIndex).ToList();
reportResult.GrandTotal = reportResult.Reports.Sum(sum => sum.TotalCurrency);
reportResult.UnitSummaryTotal = reportResult.UnitSummaries.Sum(categorySummary => categorySummary.SubTotalCurrency);
return(reportResult);
}