public void Should_Success_Instantiate()
{
LocalSalesDebtorReportViewModel viewmodel = new LocalSalesDebtorReportViewModel()
{
beginingBalance = 1,
buyerCode = "code",
buyerName = "name",
endBalance = 1,
index = "1",
moreThanNinety = 1,
normal = 1,
oneThirty = 1,
receipt = 1,
sales = 1,
sixtyNinety = 1,
thirtySixty = 1,
timeSpan = TimeSpan.MinValue,
total = "total",
};
Assert.Equal(1, viewmodel.beginingBalance);
Assert.Equal("code", viewmodel.buyerCode);
Assert.Equal("name", viewmodel.buyerName);
Assert.Equal(1, viewmodel.endBalance);
Assert.Equal("1", viewmodel.index);
Assert.Equal(1, viewmodel.moreThanNinety);
Assert.Equal(1, viewmodel.normal);
Assert.Equal(1, viewmodel.oneThirty);
Assert.Equal(1, viewmodel.receipt);
Assert.Equal(1, viewmodel.sales);
Assert.Equal(1, viewmodel.sixtyNinety);
Assert.Equal(1, viewmodel.thirtySixty);
Assert.Equal(TimeSpan.MinValue, viewmodel.timeSpan);
Assert.Equal("total", viewmodel.total);
}
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);
}