示例#1
0
        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);
        }