public void Should_Succes_Instantiate_validateService()
{
var serviceProviderMock = new Mock <IServiceProvider>();
var service = new Lib.Services.ValidateService.ValidateService(serviceProviderMock.Object);
CreditBalanceViewModel viewModel = new CreditBalanceViewModel();
service.Validate(viewModel);
Assert.NotNull(service);
}
public List <CreditBalanceViewModel> GetReport(bool isImport, string suplierName, int month, int year, int offSet, bool isForeignCurrency)
{
IQueryable <CreditorAccountModel> query = DbContext.CreditorAccounts.Where(x => x.SupplierIsImport == isImport).AsQueryable();
List <CreditBalanceViewModel> result = new List <CreditBalanceViewModel>();
int previousMonth = month - 1;
int previousYear = year;
if (previousMonth == 0)
{
previousMonth = 12;
previousYear = year - 1;
}
if (isForeignCurrency)
{
query = query.Where(entity => entity.CurrencyCode != "IDR");
}
query = query.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == month && x.UnitReceiptNoteDate.Value.Year == year);
if (!string.IsNullOrEmpty(suplierName))
{
query = query.Where(x => x.SupplierName == suplierName);
}
foreach (var item in query.GroupBy(x => x.SupplierCode).ToList())
{
var productsUnion = string.Join("\n", item.Select(x => x.Products).ToList());
var uniqueProducts = string.Join("\n", productsUnion.Split("\n").Distinct());
var creditBalance = new CreditBalanceViewModel()
{
StartBalance = DbSet.AsQueryable().Where(x => x.SupplierCode == item.Key &&
x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == previousMonth &&
x.UnitReceiptNoteDate.Value.Year == previousYear).ToList().Sum(x => x.FinalBalance),
Products = uniqueProducts,
Purchase = item.Sum(x => x.UnitReceiptMutation),
Payment = item.Sum(x => x.BankExpenditureNoteMutation),
FinalBalance = item.Sum(x => x.FinalBalance),
SupplierName = item.FirstOrDefault() == null ? "" : item.FirstOrDefault().SupplierName ?? "",
Currency = item.FirstOrDefault() == null ? "" : item.FirstOrDefault().CurrencyCode ?? "",
CurrencyRate = item.FirstOrDefault() == null ? 1 : item.FirstOrDefault().CurrencyRate
};
creditBalance.FinalBalance = creditBalance.StartBalance + creditBalance.Purchase - creditBalance.Payment;
result.Add(creditBalance);
}
return(result.OrderBy(x => x.Currency).ThenBy(x => x.Products).ThenBy(x => x.SupplierName).ToList());
}
public List <CreditBalanceViewModel> GetReportv2(bool isImport, string suplierName, int month, int year, int offSet, bool isForeignCurrency, int divisionId)
{
var query = DbContext.CreditorAccounts.Where(entity => entity.SupplierIsImport == isImport);
if (isForeignCurrency)
{
query = query.Where(entity => entity.CurrencyCode != "IDR");
}
else if (!isImport && !isForeignCurrency)
{
query = query.Where(entity => entity.CurrencyCode == "IDR");
}
DateTimeOffset firstDayOfMonth = new DateTime(year, month, 1);
DateTimeOffset lastDayOfMonth = firstDayOfMonth.AddMonths(1);
query = query.Where(entity => (entity.UnitReceiptNoteDate.HasValue && entity.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).DateTime < lastDayOfMonth.DateTime) || (entity.MemoDate.HasValue && entity.MemoDate.GetValueOrDefault().AddHours(offSet).DateTime < lastDayOfMonth.DateTime) || (entity.UnitPaymentCorrectionDate.HasValue && entity.UnitPaymentCorrectionDate.GetValueOrDefault().AddHours(offSet).DateTime < lastDayOfMonth.DateTime) || (entity.BankExpenditureNoteDate.HasValue && entity.BankExpenditureNoteDate.GetValueOrDefault().AddHours(offSet).DateTime < lastDayOfMonth.DateTime) || (entity.PurchasingMemoDate.HasValue && entity.PurchasingMemoDate.GetValueOrDefault().AddHours(offSet).DateTime < lastDayOfMonth.DateTime));
if (divisionId > 0)
{
query = query.Where(entity => entity.DivisionId == divisionId);
}
if (!string.IsNullOrWhiteSpace(suplierName))
{
query = query.Where(entity => entity.SupplierName == suplierName);
}
var queryResult = query.OrderBy(entity => entity.SupplierName).ToList();
var result = new List <CreditBalanceViewModel>();
//var initialBalance =
foreach (var item in queryResult)
{
var itemResult = new CreditBalanceViewModel()
{
Currency = item.CurrencyCode,
CurrencyRate = item.CurrencyRate,
DivisionId = item.DivisionId,
DivisionName = item.DivisionName,
Products = item.Products,
SupplierCode = item.SupplierCode,
SupplierName = item.SupplierName
};
if (item.UnitPaymentCorrectionId == 0)
{
if (item.UnitReceiptNoteDate.HasValue && item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).DateTime < firstDayOfMonth.DateTime && (item.IsStartBalance || item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Year >= 2021))
{
itemResult.StartBalance = item.UnitReceiptNoteDPP - item.IncomeTaxAmount;
itemResult.StartBalanceCurrency = item.DPPCurrency - (item.IncomeTaxAmount / item.CurrencyRate);
}
if (item.UnitReceiptNoteDate.HasValue && item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Year == year && item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Month == month)
{
itemResult.Purchase = item.UnitReceiptNoteDPP - item.IncomeTaxAmount;
itemResult.PurchaseCurrency = item.DPPCurrency - (item.IncomeTaxAmount / item.CurrencyRate);
}
if (item.MemoDate.HasValue && item.MemoDate.GetValueOrDefault().AddHours(offSet).DateTime < firstDayOfMonth.DateTime && (item.IsStartBalance || item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Year >= 2021))
{
itemResult.StartBalance += item.UnitReceiptNotePPN;
itemResult.StartBalanceCurrency += item.VATAmount;
}
if (item.MemoDate.HasValue && item.MemoDate.GetValueOrDefault().AddHours(offSet).Year == year && item.MemoDate.GetValueOrDefault().AddHours(offSet).Month == month)
{
itemResult.Purchase += item.UnitReceiptNotePPN;
itemResult.PurchaseCurrency += item.VATAmount;
}
if (item.BankExpenditureNoteDate.HasValue && item.BankExpenditureNoteDate.GetValueOrDefault().AddHours(offSet).DateTime < firstDayOfMonth.DateTime && (item.IsStartBalance || item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Year >= 2021))
{
itemResult.StartBalance -= item.BankExpenditureNoteMutation;
//itemResult.StartBalance -= item.UnitReceiptNoteDPP;
itemResult.StartBalanceCurrency -= item.DPPCurrency;
itemResult.StartBalanceCurrency -= item.VATAmount;
}
if (item.BankExpenditureNoteDate.HasValue && item.BankExpenditureNoteDate.GetValueOrDefault().AddHours(offSet).Year == year && item.BankExpenditureNoteDate.GetValueOrDefault().AddHours(offSet).Month == month)
{
itemResult.Payment = item.BankExpenditureNoteMutation;
itemResult.PaymentCurrency = item.DPPCurrency + item.VATAmount - (item.IncomeTaxAmount / item.CurrencyRate);
}
if (item.PurchasingMemoDate.HasValue && item.PurchasingMemoDate.GetValueOrDefault().AddHours(offSet).Year == year && item.PurchasingMemoDate.GetValueOrDefault().AddHours(offSet).Month == month)
{
itemResult.Payment = (decimal)item.PurchasingMemoAmount;
itemResult.PaymentCurrency = (decimal)item.PurchasingMemoAmount / item.CurrencyRate;
}
}
else
{
if (item.UnitPaymentCorrectionDate.HasValue && item.UnitPaymentCorrectionDate.GetValueOrDefault().AddHours(offSet).DateTime < firstDayOfMonth.DateTime && (item.IsStartBalance || item.UnitReceiptNoteDate.GetValueOrDefault().AddHours(offSet).Year >= 2021))
{
itemResult.StartBalance += (item.UnitPaymentCorrectionMutation);
itemResult.StartBalanceCurrency += (item.UnitPaymentCorrectionMutation / item.CurrencyRate);
}
if (item.UnitPaymentCorrectionDate.HasValue && item.UnitPaymentCorrectionDate.GetValueOrDefault().AddHours(offSet).Year == year && item.UnitPaymentCorrectionDate.GetValueOrDefault().AddHours(offSet).Month == month)
{
itemResult.Purchase = (item.UnitPaymentCorrectionMutation);
itemResult.PurchaseCurrency = (item.UnitPaymentCorrectionMutation / item.CurrencyRate);
}
}
result.Add(itemResult);
}
if (result.Count > 0)
{
result = result
.GroupBy(element => new { element.SupplierName, element.DivisionId, element.Currency })
.Select(element => new CreditBalanceViewModel()
{
Currency = element.Key.Currency,
CurrencyRate = element.FirstOrDefault().CurrencyRate,
DivisionId = element.Key.DivisionId,
DivisionName = element.FirstOrDefault().DivisionName,
FinalBalance = element.Sum(sum => sum.StartBalance + sum.Purchase - sum.Payment),
FinalBalanceCurrency = element.Sum(sum => sum.StartBalanceCurrency + sum.PurchaseCurrency - sum.PaymentCurrency),
PaidAmount = element.Sum(sum => sum.PaidAmount),
PaidAmountCurrency = element.Sum(sum => sum.PaidAmountCurrency),
Payment = element.Sum(sum => sum.Payment),
PaymentCurrency = element.Sum(sum => sum.PaymentCurrency),
Products = string.Join('\n', element.Select(select => select.Products)),
Purchase = element.Sum(sum => sum.Purchase),
PurchaseCurrency = element.Sum(sum => sum.PurchaseCurrency),
StartBalance = element.Sum(sum => sum.StartBalance),
StartBalanceCurrency = element.Sum(sum => sum.StartBalanceCurrency),
SupplierCode = element.FirstOrDefault().SupplierCode,
SupplierName = element.FirstOrDefault().SupplierName
})
.ToList();
}
return(result.OrderBy(element => element.Currency).ThenBy(element => element.SupplierName).ToList());
}
public List <CreditBalanceViewModel> GetReport(bool isImport, string suplierName, int month, int year, int offSet, bool isForeignCurrency, int divisionId)
{
var firstDayOfMonth = new DateTime(year, month, 1);
IQueryable <CreditorAccountModel> query = DbContext.CreditorAccounts.Where(x => x.SupplierIsImport == isImport).AsQueryable();
List <CreditBalanceViewModel> result = new List <CreditBalanceViewModel>();
int previousMonth = month - 1;
int previousYear = year;
if (previousMonth == 0)
{
previousMonth = 12;
previousYear = year - 1;
}
if (isForeignCurrency)
{
query = query.Where(entity => entity.CurrencyCode != "IDR");
}
//else
if (!isImport && !isForeignCurrency)
{
query = query.Where(entity => entity.CurrencyCode == "IDR");
}
if (divisionId > 0)
{
query = query.Where(entity => entity.DivisionId == divisionId);
}
var queryRemainingBalance = query;
if (!string.IsNullOrEmpty(suplierName))
{
query = query.Where(x => x.SupplierName == suplierName);
}
else
{
queryRemainingBalance = query.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.DateTime < firstDayOfMonth);
}
query = query.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == month && x.UnitReceiptNoteDate.Value.Year == year);
var data = query.ToList();
if (string.IsNullOrEmpty(suplierName))
{
data.AddRange(queryRemainingBalance.ToList());
}
var grouppedData = data.GroupBy(x => new { x.SupplierCode, x.DivisionCode, x.CurrencyCode }).ToList();
foreach (var item in grouppedData)
{
var productsUnion = string.Join("\n", item.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == month && x.UnitReceiptNoteDate.Value.Year == year).Select(x => x.Products).ToList());
var uniqueProducts = string.Join("\n", productsUnion.Split("\n").Distinct());
//var now = DateTimeOffset.Now;
var creditBalance = new CreditBalanceViewModel()
{
StartBalance = DbSet
.AsQueryable()
.Where(x => x.SupplierCode == item.Key.SupplierCode && x.DivisionCode == item.Key.DivisionCode && x.CurrencyCode == item.Key.CurrencyCode && x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.DateTime < firstDayOfMonth)
.ToList().Sum(x => x.UnitReceiptMutation - x.BankExpenditureNoteMutation),
Products = uniqueProducts,
Purchase = item.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == month && x.UnitReceiptNoteDate.Value.Year == year).Sum(x => x.UnitReceiptMutation),
Payment = item.Where(x => x.UnitReceiptNoteDate.HasValue && x.UnitReceiptNoteDate.Value.Month == month && x.UnitReceiptNoteDate.Value.Year == year).Sum(x => x.BankExpenditureNoteMutation),
FinalBalance = item.Sum(x => x.FinalBalance),
SupplierName = item.FirstOrDefault() == null ? "" : item.FirstOrDefault().SupplierName ?? "",
Currency = item.FirstOrDefault() == null ? "" : item.FirstOrDefault().CurrencyCode ?? "",
CurrencyRate = item.FirstOrDefault() == null ? 1 : item.FirstOrDefault().CurrencyRate,
DivisionName = item.FirstOrDefault() == null ? "" : item.FirstOrDefault().DivisionName ?? "",
};
creditBalance.FinalBalance = creditBalance.StartBalance + creditBalance.Purchase - creditBalance.Payment;
result.Add(creditBalance);
}
return(result.OrderBy(x => x.SupplierName).ToList());
}
