public async Task <GarmentShippingLocalSalesNote> GetDataLocalSalesNote(string type, int month, int year)
        {
            GarmentShippingLocalSalesNote garmentShippingLocalSalesNote = new GarmentShippingLocalSalesNote();
            var http     = _serviceProvider.GetService <IHttpClientService>();
            var uri      = APIEndpoint.PackingInventory + $"garment-shipping/local-sales-notes/localSalesDebtor?type={type}&month={month}&year={year}";
            var response = await http.GetAsync(uri);


            var result = new BaseResponse <string>();

            if (response.IsSuccessStatusCode)
            {
                if (response.Content != null)
                {
                    var contentString = await response.Content.ReadAsStringAsync();

                    Dictionary <string, object> content = JsonConvert.DeserializeObject <Dictionary <string, object> >(contentString);
                    var dataString = content.GetValueOrDefault("data").ToString();
                    var listData   = JsonConvert.DeserializeObject <List <ShippingLocalSalesNoteDto> >(dataString);
                    garmentShippingLocalSalesNote.data = listData;
                }
            }
            else
            {
                var err = await response.Content.ReadAsStringAsync();
            }


            return(garmentShippingLocalSalesNote);
        }
        public async Task <List <LocalSalesDebtorReportViewModel> > GetReportQuery(int month, int year)
        {
            GarmentShippingLocalSalesNote salesBalanceNow = await GetDataLocalSalesNote("now", month, year);

            GarmentShippingLocalSalesNote salesBalance = await GetDataLocalSalesNote("balance", month, year);

            List <LocalSalesDebtorReportViewModel> data = new List <LocalSalesDebtorReportViewModel>();

            var _sales        = salesBalance.data.Union(salesBalanceNow.data);
            var querytimeSpan = from aa in _sales
                                select new timeSpanInvoice
            {
                buyerCode = aa.buyer.Code,
                amount    = Convert.ToDecimal(aa.amount),
                type      = (aa.tempo <= 0) ? "normal" :
                            (aa.tempo > 0 && aa.tempo < 31) ? "oneThirty" :
                            (aa.tempo > 30 && aa.tempo < 61) ? "thirtySixty" :
                            (aa.tempo > 60 && aa.tempo < 91) ? "sixtyNinety" :
                            "moreThanNinety",
                localSalesNoteId = Convert.ToInt32(aa.Id)
            };

            var querySumInvoice = querytimeSpan.ToList()
                                  .GroupBy(x => new { x.buyerCode, x.type }, (key, group) => new
            {
                buyerCode = key.buyerCode,
                type      = key.type,
                amount    = group.Sum(s => s.amount)
            });

            var receiptBankCashReceiptDetailLocal = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetailLocals where aa.BankCashReceiptDate.AddHours(7).Month == month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
                                                    join b in _dbContext.GarmentFinanceBankCashReceiptDetailLocalItems on a.Id equals b.BankCashReceiptDetailLocalId

                                                    select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = b.BuyerCode.TrimEnd(),
                buyerName       = b.BuyerName.TrimEnd(),
                beginingBalance = 0,
                receipt         = Convert.ToDouble(b.Amount),
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var receiptMemorialDetailLocal = from a in (from aa in _dbContext.GarmentFinanceMemorialDetailLocals where aa.MemorialDate.AddHours(7).Month == month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
                                             join c in _dbContext.GarmentFinanceMemorialDetailLocalItems on a.Id equals c.MemorialDetailLocalId

                                             select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = c.BuyerCode.TrimEnd(),
                buyerName       = c.BuyerName.TrimEnd(),
                beginingBalance = 0,
                receipt         = Convert.ToDouble(c.Amount),
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var salesBalanceLocal = from a in salesBalanceNow.data

                                    select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = a.buyer.Code.TrimEnd(),
                buyerName       = a.buyer.Name.TrimEnd(),
                beginingBalance = 0,
                receipt         = 0,
                sales           = a.amount,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var localDebtorBalance = from a in _dbContext.GarmentLocalDebiturBalances

                                     select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = a.BuyerCode.TrimEnd(),
                buyerName       = a.BuyerName.TrimEnd(),
                beginingBalance = Convert.ToDecimal(a.BalanceAmount),
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var beginningBalanceSales = from a in salesBalance.data

                                        select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = a.buyer.Code.TrimEnd(),
                buyerName       = a.buyer.Name.TrimEnd(),
                beginingBalance = Convert.ToDecimal(a.amount),
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var beginningBalanceReceiptBankCashReceiptDetailLocal = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetailLocals where aa.BankCashReceiptDate.AddHours(7).Month < month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
                                                                    join b in _dbContext.GarmentFinanceBankCashReceiptDetailLocalItems on a.Id equals b.BankCashReceiptDetailLocalId

                                                                    select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = b.BuyerCode.TrimEnd(),
                buyerName       = b.BuyerName.TrimEnd(),
                beginingBalance = -b.Amount,
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var beginningBalanceReceiptMemorialDetailLocal = from a in (from aa in _dbContext.GarmentFinanceMemorialDetailLocals where aa.MemorialDate.AddHours(7).Month < month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
                                                             join c in _dbContext.GarmentFinanceMemorialDetailLocalItems on a.Id equals c.MemorialDetailLocalId

                                                             select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = c.BuyerCode.TrimEnd(),
                buyerName       = c.BuyerName.TrimEnd(),
                beginingBalance = -c.Amount,
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = 0,
                oneThirty       = 0,
                thirtySixty     = 0,
                sixtyNinety     = 0,
                moreThanNinety  = 0
            };

            var periodeMemoLocal = from a in (from aa in _dbContext.GarmentFinanceMemorialDetailLocals where aa.MemorialDate.AddHours(7).Month <= month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
                                   join c in _dbContext.GarmentFinanceMemorialDetailLocalItems on a.Id equals c.MemorialDetailLocalId

                                   select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = c.BuyerCode.TrimEnd(),
                buyerName       = c.BuyerName.TrimEnd(),
                beginingBalance = 0,
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = (from aa in querytimeSpan where aa.localSalesNoteId == c.LocalSalesNoteId select aa.type).FirstOrDefault() == "normal" ? Convert.ToDouble(-c.Amount) : 0,
                oneThirty       = (from aa in querytimeSpan where aa.localSalesNoteId == c.LocalSalesNoteId select aa.type).FirstOrDefault() == "oneThirty" ? Convert.ToDouble(-c.Amount) : 0,
                thirtySixty     = (from aa in querytimeSpan where aa.localSalesNoteId == c.LocalSalesNoteId select aa.type).FirstOrDefault() == "thirtySixty" ? Convert.ToDouble(-c.Amount) : 0,
                sixtyNinety     = (from aa in querytimeSpan where aa.localSalesNoteId == c.LocalSalesNoteId select aa.type).FirstOrDefault() == "sixtyNinety" ? Convert.ToDouble(-c.Amount) : 0,
                moreThanNinety  = (from aa in querytimeSpan where aa.localSalesNoteId == c.LocalSalesNoteId select aa.type).FirstOrDefault() == "moreThanNinety" ? Convert.ToDouble(-c.Amount) : 0,
            };

            var periodeBankCashReceiptLocal = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetailLocals where aa.BankCashReceiptDate.AddHours(7).Month <= month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
                                              join b in _dbContext.GarmentFinanceBankCashReceiptDetailLocalItems on a.Id equals b.BankCashReceiptDetailLocalId

                                              select new LocalSalesDebtorReportViewModel
            {
                buyerCode       = b.BuyerCode.TrimEnd(),
                buyerName       = b.BuyerName.TrimEnd(),
                beginingBalance = 0,
                receipt         = 0,
                sales           = 0,
                endBalance      = 0,
                normal          = (from aa in querytimeSpan where aa.localSalesNoteId == b.LocalSalesNoteId select aa.type).FirstOrDefault() == "normal" ? Convert.ToDouble(-b.Amount) : 0,
                oneThirty       = (from aa in querytimeSpan where aa.localSalesNoteId == b.LocalSalesNoteId select aa.type).FirstOrDefault() == "oneThirty" ? Convert.ToDouble(-b.Amount) : 0,
                thirtySixty     = (from aa in querytimeSpan where aa.localSalesNoteId == b.LocalSalesNoteId select aa.type).FirstOrDefault() == "thirtySixty" ? Convert.ToDouble(-b.Amount) : 0,
                sixtyNinety     = (from aa in querytimeSpan where aa.localSalesNoteId == b.LocalSalesNoteId select aa.type).FirstOrDefault() == "sixtyNinety" ? Convert.ToDouble(-b.Amount) : 0,
                moreThanNinety  = (from aa in querytimeSpan where aa.localSalesNoteId == b.LocalSalesNoteId select aa.type).FirstOrDefault() == "moreThanNinety" ? Convert.ToDouble(-b.Amount) : 0,
            };

            var queryUnion = localDebtorBalance
                             .Union(salesBalanceLocal)
                             .Union(receiptMemorialDetailLocal)
                             .Union(receiptBankCashReceiptDetailLocal)
                             .Union(beginningBalanceSales)
                             .Union(beginningBalanceReceiptBankCashReceiptDetailLocal)
                             .Union(beginningBalanceReceiptMemorialDetailLocal)
                             .Union(periodeMemoLocal)
                             .Union(periodeBankCashReceiptLocal);

            var querySum = queryUnion.ToList()
                           .GroupBy(x => new { x.buyerCode, x.buyerName }, (key, group) => new
            {
                buyerCode       = key.buyerCode,
                buyerName       = key.buyerName,
                beginingBalance = group.Sum(s => s.beginingBalance),
                receipt         = group.Sum(s => s.receipt),
                sales           = group.Sum(s => s.sales),
                endBalance      = group.Sum(s => s.endBalance),
                normal          = group.Sum(s => s.normal),
                oneThirty       = group.Sum(s => s.oneThirty),
                thirtySixty     = group.Sum(s => s.thirtySixty),
                sixtyNinety     = group.Sum(s => s.sixtyNinety),
                moreThanNinety  = group.Sum(s => s.moreThanNinety)
            }).OrderByDescending(s => s.buyerName);
            int index = 1;

            foreach (var item in querySum.OrderBy(a => a.buyerName))
            {
                LocalSalesDebtorReportViewModel model = new LocalSalesDebtorReportViewModel
                {
                    index           = index.ToString(),
                    buyerCode       = item.buyerCode,
                    buyerName       = item.buyerName,
                    beginingBalance = item.beginingBalance,
                    receipt         = item.receipt,
                    sales           = item.sales,
                    endBalance      = Convert.ToDouble(item.beginingBalance) + item.sales - item.receipt,
                    normal          = item.normal + Convert.ToDouble((from aa in querySumInvoice where aa.buyerCode == item.buyerCode && aa.type == "normal" select aa.amount).FirstOrDefault()),
                    oneThirty       = item.oneThirty + Convert.ToDouble((from aa in querySumInvoice where aa.buyerCode == item.buyerCode && (aa.type == "oneThirty") select aa.amount).FirstOrDefault()),
                    thirtySixty     = item.thirtySixty + Convert.ToDouble((from aa in querySumInvoice where aa.buyerCode == item.buyerCode && (aa.type == "thirtySixty") select aa.amount).FirstOrDefault()),
                    sixtyNinety     = item.sixtyNinety + Convert.ToDouble((from aa in querySumInvoice where aa.buyerCode == item.buyerCode && (aa.type == "sixtyNinety") select aa.amount).FirstOrDefault()),
                    moreThanNinety  = item.moreThanNinety + Convert.ToDouble((from aa in querySumInvoice where aa.buyerCode == item.buyerCode && (aa.type == "moreThanNinety") select aa.amount).FirstOrDefault()),
                    total           = "TOTAL"
                };
                data.Add(model);
                index++;
            }

            var queryTOTAL = data.ToList()
                             .GroupBy(x => new { x.total }, (key, group) => new
            {
                beginingBalance = group.Sum(s => s.beginingBalance),
                receipt         = group.Sum(s => s.receipt),
                sales           = group.Sum(s => s.sales),
                endBalance      = group.Sum(s => s.endBalance),
                normal          = group.Sum(s => s.normal),
                oneThirty       = group.Sum(s => s.oneThirty),
                thirtySixty     = group.Sum(s => s.thirtySixty),
                sixtyNinety     = group.Sum(s => s.sixtyNinety),
                moreThanNinety  = group.Sum(s => s.moreThanNinety)
            });

            foreach (var item in queryTOTAL)
            {
                LocalSalesDebtorReportViewModel model = new LocalSalesDebtorReportViewModel
                {
                    index           = "",
                    buyerCode       = "TOTAL",
                    buyerName       = "",
                    beginingBalance = item.beginingBalance,
                    receipt         = item.receipt,
                    sales           = item.sales,
                    endBalance      = item.endBalance,
                    normal          = item.normal,
                    oneThirty       = item.oneThirty,
                    thirtySixty     = item.thirtySixty,
                    sixtyNinety     = item.sixtyNinety,
                    moreThanNinety  = item.moreThanNinety
                };
                data.Add(model);
            }

            return(data);
        }