public async Task <GarmentShippingPackingList> GetDataShippingInvoiceNow(int month, int year)
{
GarmentShippingPackingList garmentShipping = new GarmentShippingPackingList();
var http = _serviceProvider.GetService <IHttpClientService>();
var uri = APIEndpoint.PackingInventory + $"garment-shipping/invoices/exportSalesDebtorNow?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 <ShippingPackingDto> >(dataString);
garmentShipping.data = listData;
}
}
else
{
var err = await response.Content.ReadAsStringAsync();
}
return(garmentShipping);
}
public async Task <List <ExportSalesDebtorReportViewModel> > GetReportQuery(int month, int year, string type)
{
GarmentShippingPackingList invoicePackingListBalance = await GetDataShippingInvoice(month, year);
GarmentShippingPackingList invoicePackingListNow = await GetDataShippingInvoiceNow(month, year);
GarmentShippingPackingList balance = await GetDataBalance();
List <ExportSalesDebtorReportViewModel> data = new List <ExportSalesDebtorReportViewModel>();
GarmentCurrency garmentCurrency = await GetCurrency();
foreach (var item in invoicePackingListBalance.data)
{
GarmentCurrency currency = await GetCurrencyPEBDate(item.pebDate.Date.ToShortDateString());
item.rate = Convert.ToDouble(currency.Rate);
}
foreach (var item in invoicePackingListNow.data)
{
GarmentCurrency currency = await GetCurrencyPEBDate(item.pebDate.Date.ToShortDateString());
item.rate = Convert.ToDouble(currency.Rate);
}
var _invoice = invoicePackingListBalance.data.Union(invoicePackingListNow.data);
var querytimeSpan = from aa in _invoice
select new timeSpanInvoice
{
buyerCode = aa.buyerAgentCode,
amount = type == "IDR" ? Convert.ToDecimal(aa.amount * aa.rate) : Convert.ToDecimal(aa.amount),
day = (aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days,
type = (aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days <= 0 ? "normal" :
(aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days > 0 && (aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days < 31 ? "oneThirty" :
(aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days > 30 && (aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days < 61 ? "thirtySixty" :
(aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days > 60 && (aa.truckingDate.AddDays(aa.paymentdue) - aa.truckingDate).Days < 91 ? "sixtyNinety" :
"moreThanNinety",
invoiceId = Convert.ToInt32(aa.invoiceId)
};
var querySumInvoice = querytimeSpan.ToList()
.GroupBy(x => new { x.buyerCode, x.type }, (key, group) => new
{
buyerCode = key.buyerCode.TrimEnd(),
type = key.type,
amount = group.Sum(s => s.amount)
});
var beginingMemo = from a in (from aa in _dbContext.GarmentFinanceMemorialDetails where aa.MemorialDate.AddHours(7).Month < month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
join c in _dbContext.GarmentFinanceMemorialDetailItems on a.Id equals c.MemorialDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = c.BuyerCode.TrimEnd(),
buyerName = c.BuyerName.TrimEnd(),
beginingBalance = type == "IDR" ? Convert.ToDecimal(-c.Amount) * Convert.ToDecimal(garmentCurrency.Rate) : Convert.ToDecimal(-c.Amount),
receipt = 0,
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var beginingBankCashReceipt = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetails where aa.BankCashReceiptDate.AddHours(7).Month < month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
join b in _dbContext.GarmentFinanceBankCashReceiptDetailItems on a.Id equals b.BankCashReceiptDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = b.BuyerCode.TrimEnd(),
buyerName = b.BuyerName.TrimEnd(),
beginingBalance = type == "IDR" ? Convert.ToDecimal(-b.CurrencyRate * b.Amount) : Convert.ToDecimal(-b.Amount),
receipt = 0,
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var beginingInvoice = from a in invoicePackingListBalance.data
select new ExportSalesDebtorReportViewModel
{
buyerCode = a.buyerAgentCode.TrimEnd(),
buyerName = a.buyerAgentName.TrimEnd(),
beginingBalance = type == "IDR" ? Convert.ToDecimal(a.amount * a.rate) : Convert.ToDecimal(a.amount),
receipt = 0,
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var beginingBalance = from a in balance.data
select new ExportSalesDebtorReportViewModel
{
buyerCode = a.buyerAgentCode.TrimEnd(),
buyerName = a.buyerAgentName.TrimEnd(),
beginingBalance = type == "IDR" ? Convert.ToDecimal(a.balanceAmountIDR) : Convert.ToDecimal(a.balanceAmount),
receipt = 0,
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var salesNow = from a in invoicePackingListNow.data
select new ExportSalesDebtorReportViewModel
{
buyerCode = a.buyerAgentCode.TrimEnd(),
buyerName = a.buyerAgentName.TrimEnd(),
beginingBalance = 0,
receipt = 0,
sales = type == "IDR" ? Convert.ToDouble(a.amount * a.rate) : Convert.ToDouble(a.amount),
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var memoNow = from a in (from aa in _dbContext.GarmentFinanceMemorialDetails where aa.MemorialDate.AddHours(7).Month == month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
join c in _dbContext.GarmentFinanceMemorialDetailItems on a.Id equals c.MemorialDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = c.BuyerCode.TrimEnd(),
buyerName = c.BuyerName.TrimEnd(),
beginingBalance = 0,
receipt = type == "IDR" ? Convert.ToDouble(c.Amount * c.CurrencyRate) : Convert.ToDouble(c.Amount),
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var bankCashReceiptNow = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetails where aa.BankCashReceiptDate.AddHours(7).Month == month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
join b in _dbContext.GarmentFinanceBankCashReceiptDetailItems on a.Id equals b.BankCashReceiptDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = b.BuyerCode.TrimEnd(),
buyerName = b.BuyerName.TrimEnd(),
beginingBalance = 0,
receipt = type == "IDR" ? Convert.ToDouble(b.Amount * b.CurrencyRate) : Convert.ToDouble(b.Amount),
sales = 0,
endBalance = 0,
normal = 0,
oneThirty = 0,
thirtySixty = 0,
sixtyNinety = 0,
moreThanNinety = 0
};
var periodeMemo = from a in (from aa in _dbContext.GarmentFinanceMemorialDetails where aa.MemorialDate.AddHours(7).Month <= month && aa.MemorialDate.AddHours(7).Year == year select new { aa.Id })
join c in _dbContext.GarmentFinanceMemorialDetailItems on a.Id equals c.MemorialDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = c.BuyerCode.TrimEnd(),
buyerName = c.BuyerName.TrimEnd(),
beginingBalance = 0,
receipt = 0,
sales = 0,
endBalance = 0,
normal = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "normal"? -c.Amount * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "normal" ? -c.Amount :0,
oneThirty = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "oneThirty" ? -c.Amount * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "oneThirty" ? -c.Amount : 0,
thirtySixty = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "thirtySixty" ? -c.Amount * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "thirtySixty" ? -c.Amount : 0,
sixtyNinety = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "sixtyNinety" ? -c.Amount * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "sixtyNinety" ? -c.Amount : 0,
moreThanNinety = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "moreThanNinety" ? -c.Amount * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == c.InvoiceId select aa.type).FirstOrDefault() == "moreThanNinety" ? -c.Amount : 0,
};
var periodeBankCashReceipt = from a in (from aa in _dbContext.GarmentFinanceBankCashReceiptDetails where aa.BankCashReceiptDate.AddHours(7).Month <= month && aa.BankCashReceiptDate.AddHours(7).Year == year select new { aa.Id })
join b in _dbContext.GarmentFinanceBankCashReceiptDetailItems on a.Id equals b.BankCashReceiptDetailId
select new ExportSalesDebtorReportViewModel
{
buyerCode = b.BuyerCode.TrimEnd(),
buyerName = b.BuyerName.TrimEnd(),
beginingBalance = 0,
receipt = 0,
sales = 0,
endBalance = 0,
normal = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "normal" ? Convert.ToDouble(-b.Amount) * Convert.ToDouble(garmentCurrency.Rate): (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "normal" ? Convert.ToDouble(-b.Amount) : 0,
oneThirty = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "oneThirty" ? Convert.ToDouble(-b.Amount) * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "oneThirty" ? Convert.ToDouble(-b.Amount) : 0,
thirtySixty = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "thirtySixty" ? Convert.ToDouble(-b.Amount) * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "thirtySixty" ? Convert.ToDouble(-b.Amount) : 0,
sixtyNinety = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "sixtyNinety" ? Convert.ToDouble(-b.Amount) * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "sixtyNinety" ? Convert.ToDouble(-b.Amount) : 0,
moreThanNinety = type == "IDR" && (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "moreThanNinety" ? Convert.ToDouble(-b.Amount) * Convert.ToDouble(garmentCurrency.Rate) : (from aa in querytimeSpan where aa.invoiceId == b.InvoiceId select aa.type).FirstOrDefault() == "moreThanNinety" ? Convert.ToDouble(-b.Amount) : 0,
};
var queryUnion = beginingBalance.Union(beginingMemo).Union(beginingBankCashReceipt).Union(beginingInvoice)
.Union(memoNow).Union(bankCashReceiptNow).Union(salesNow)
.Union(periodeBankCashReceipt).Union(periodeMemo);
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))
{
ExportSalesDebtorReportViewModel model = new ExportSalesDebtorReportViewModel
{
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)
{
ExportSalesDebtorReportViewModel model = new ExportSalesDebtorReportViewModel
{
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);
}
