public GeneralLedgerDetail GetAllDetails(string fund, string department, string program, string project, string @base, int fiscalYear, out int resultCount, int pageNumber = 1, int pageSize = 50)
{
GeneralLedgerDetail results = null;
var query = (from pa
in _context.AcctGlperiodAmounts
where pa.FiscalYear == fiscalYear && pa.Base == @base && pa.Fund == fund && pa.Department == department &&
pa.Program == program && pa.Project == project
orderby pa.Year
orderby pa.Month
select GeneralLedgerMapper.Map(pa)).Distinct();
if (pageNumber > 0)
{
resultCount = query.Count();
results = new GeneralLedgerDetail(query.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList(), "", "");
}
else
{
results = new GeneralLedgerDetail(query.ToList(), "", "");
resultCount = results.GeneralLedgers.Count();
}
return(results);
}
void UpdateGeneralLedger(PurchaseModel purchaseModel, int purchaseId, int APKey)
{
GeneralLedger generalLedger = new GeneralLedger
{
SubsidiaryLedgerAccountId = purchaseModel.Vendor.Value,
GeneralLedgerReferenceNo = purchaseModel.ReferenceNo,
GeneralLedgerInvoiceNo = "",
GeneralLedgerDate = purchaseModel.Date,
GeneralLedgerType = "PUR",
PurchaseId = purchaseId
};
_serverContext.GeneralLedgers.Add(generalLedger);
_serverContext.SaveChanges();
//var invoiceDetail = customerInvoiceModel.Items.GroupBy(x => x.SalesItem.Value).Select(g => new { Id = g.Key, Value = g.Sum(s => Convert.ToDecimal(s.Amount)) });
foreach (PurchaseItemModel item in purchaseModel.Items)
{
GeneralLedgerDetail generalLedgerDetail = new GeneralLedgerDetail();
//var temp = _serverContext.Inventories.Where(x => x.Id == item.SalesItem.Value).FirstOrDefault();
//generalLedgerDetail.ChartOfAccountId = Convert.ToInt32(temp.InventoryProductServiceIncomeAccountId);
generalLedgerDetail.ChartOfAccountId = item.ChartOfAccountItem.Value;
generalLedgerDetail.GeneralLedgerDetailMode = "D";
generalLedgerDetail.GeneralLedgerId = generalLedger.Id;
generalLedgerDetail.GeneralLedgerDetailAmount = Convert.ToDecimal(item.Amount);
generalLedgerDetail.GeneralLedgerDetailDescription = item.Description;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetail);
_serverContext.SaveChanges();
}
if (purchaseModel.TotalTaxes > 0)
{
GeneralLedgerDetail generalLedgerDetail = new GeneralLedgerDetail();
generalLedgerDetail.ChartOfAccountId = SalesTaxKey;
generalLedgerDetail.GeneralLedgerDetailMode = "D";
generalLedgerDetail.GeneralLedgerId = generalLedger.Id;
generalLedgerDetail.GeneralLedgerDetailAmount = purchaseModel.TotalTaxes;
generalLedgerDetail.GeneralLedgerDetailDescription = "";
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetail);
_serverContext.SaveChanges();
}
var APAmount = purchaseModel.Amount;
GeneralLedgerDetail APGeneralLedgerDetail = new GeneralLedgerDetail();
APGeneralLedgerDetail.GeneralLedgerId = generalLedger.Id;
APGeneralLedgerDetail.GeneralLedgerDetailDescription = "";
APGeneralLedgerDetail.ChartOfAccountId = purchaseModel.ModeOfPayment == "PY" ? APKey : (int)purchaseModel.ChartOfAccounts.Value;
APGeneralLedgerDetail.GeneralLedgerDetailMode = "C";
APGeneralLedgerDetail.GeneralLedgerId = generalLedger.Id;
APGeneralLedgerDetail.GeneralLedgerDetailAmount = APAmount;
_serverContext.GeneralLedgerDetails.Add(APGeneralLedgerDetail);
_serverContext.SaveChanges();
}
public int Payment(CustomerInvoicePaymentModel customerInvoicePaymentModel)
{
int Id = 0;
_serverContext.Database.BeginTransaction();
var Invoice = (from a in _serverContext.LedgerMasters
where a.SubsidiaryLedgerAccountId == customerInvoicePaymentModel.CustomerId
select new { a.SubsidiaryLedgerAccountId });
try
{
InvoicePayment invoice = new InvoicePayment();
//invoice.LedgerMasterId = customerInvoicePaymentModel.LedgerMasterId;
invoice.SubsidiaryLedgerAccountId = customerInvoicePaymentModel.CustomerId;
invoice.InvoicePaymentAmount = customerInvoicePaymentModel.InvoiceAmount;
invoice.ChartOfAccountId = customerInvoicePaymentModel.ChartOfAccountId;
invoice.InvoicePaymentReferenceNo = customerInvoicePaymentModel.ReferenceNo;
invoice.InvoicePaymentDate = customerInvoicePaymentModel.PaymentDate;
invoice.InvoicePaymentCreatedDate = DateTime.Now;
_serverContext.InvoicePayments.Add(invoice);
invoice.InvoicePaymentModifiedDate = DateTime.Now;
_serverContext.SaveChanges();
Id = invoice.Id;
foreach (CustomerInvoicePostPaymentItemModel item in customerInvoicePaymentModel.Items)
{
InvoicePaymentDetail invoicePaymentDetail = new InvoicePaymentDetail();
invoicePaymentDetail.InvoicePaymentId = Id;
invoicePaymentDetail.LedgerMasterId = item.Id;
invoicePaymentDetail.InvoicePaymentDetailAmount = item.Amount;
_serverContext.InvoicePaymentDetails.Add(invoicePaymentDetail);
_serverContext.SaveChanges();
}
foreach (var item in customerInvoicePaymentModel.Items)
{
GeneralLedger generalLedger = new GeneralLedger
{
SubsidiaryLedgerAccountId = customerInvoicePaymentModel.CustomerId,
GeneralLedgerInvoiceNo = string.Empty,
GeneralLedgerDate = customerInvoicePaymentModel.PaymentDate,
GeneralLedgerReferenceNo = customerInvoicePaymentModel.ReferenceNo,
GeneralLedgerType = "PI",
LedgerMasterId = item.Id
};
_serverContext.GeneralLedgers.Add(generalLedger);
_serverContext.SaveChanges();
GeneralLedgerDetail generalLedgerDetailDebit = new GeneralLedgerDetail();
generalLedgerDetailDebit.ChartOfAccountId = customerInvoicePaymentModel.ChartOfAccountId;
generalLedgerDetailDebit.GeneralLedgerDetailMode = "D";
generalLedgerDetailDebit.GeneralLedgerId = generalLedger.Id;
generalLedgerDetailDebit.GeneralLedgerDetailAmount = item.Amount;
generalLedgerDetailDebit.GeneralLedgerDetailDescription = string.Empty;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetailDebit);
_serverContext.SaveChanges();
GeneralLedgerDetail generalLedgerDetailCredit = new GeneralLedgerDetail();
generalLedgerDetailCredit.ChartOfAccountId = ARTradeKey;
generalLedgerDetailCredit.GeneralLedgerDetailMode = "C";
generalLedgerDetailCredit.GeneralLedgerId = generalLedger.Id;
generalLedgerDetailCredit.GeneralLedgerDetailAmount = item.Amount;
generalLedgerDetailCredit.GeneralLedgerDetailDescription = string.Empty;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetailCredit);
_serverContext.SaveChanges();
}
_serverContext.Database.CommitTransaction();
}
catch (Exception ex)
{
_serverContext.Database.RollbackTransaction();
}
return(Id);
}
void UpdateGeneralLedger(CustomerInvoiceModel customerInvoiceModel, int ledgerMasterId, int ARTradeKey, int SalesTaxKey)
{
GeneralLedger generalLedger = new GeneralLedger
{
SubsidiaryLedgerAccountId = customerInvoiceModel.Customer.Value,
GeneralLedgerInvoiceNo = customerInvoiceModel.InvoiceNo,
GeneralLedgerDate = customerInvoiceModel.Date,
GeneralLedgerReferenceNo = customerInvoiceModel.Reference,
GeneralLedgerType = "INV",
LedgerMasterId = ledgerMasterId
};
_serverContext.GeneralLedgers.Add(generalLedger);
_serverContext.SaveChanges();
var ARAmount = customerInvoiceModel.Amount;
GeneralLedgerDetail ARGeneralLedgerDetail = new GeneralLedgerDetail();
ARGeneralLedgerDetail.GeneralLedgerId = generalLedger.Id;
ARGeneralLedgerDetail.GeneralLedgerDetailDescription = "";
ARGeneralLedgerDetail.ChartOfAccountId = ARTradeKey;
ARGeneralLedgerDetail.GeneralLedgerDetailMode = "D";
ARGeneralLedgerDetail.GeneralLedgerId = generalLedger.Id;
ARGeneralLedgerDetail.GeneralLedgerDetailAmount = ARAmount;
_serverContext.GeneralLedgerDetails.Add(ARGeneralLedgerDetail);
_serverContext.SaveChanges();
//var invoiceDetail = customerInvoiceModel.Items.GroupBy(x => x.SalesItem.Value).Select(g => new { Id = g.Key, Value = g.Sum(s => Convert.ToDecimal(s.Amount)) });
foreach (CustomerInvoiceItemModel item in customerInvoiceModel.Items)
{
GeneralLedgerDetail generalLedgerDetail = new GeneralLedgerDetail();
var temp = _serverContext.Inventories.Where(x => x.Id == item.SalesItem.Value).FirstOrDefault();
generalLedgerDetail.ChartOfAccountId = Convert.ToInt32(temp.InventoryProductServiceIncomeAccountId);
generalLedgerDetail.GeneralLedgerDetailMode = "C";
generalLedgerDetail.GeneralLedgerId = generalLedger.Id;
generalLedgerDetail.GeneralLedgerDetailAmount = Convert.ToDecimal(item.Amount);
generalLedgerDetail.GeneralLedgerDetailDescription = item.Description;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetail);
_serverContext.SaveChanges();
}
if (customerInvoiceModel.TotalTaxes > 0)
{
GeneralLedgerDetail generalLedgerDetail = new GeneralLedgerDetail();
generalLedgerDetail.ChartOfAccountId = SalesTaxKey;
generalLedgerDetail.GeneralLedgerDetailMode = "C";
generalLedgerDetail.GeneralLedgerId = generalLedger.Id;
generalLedgerDetail.GeneralLedgerDetailAmount = customerInvoiceModel.TotalTaxes;
generalLedgerDetail.GeneralLedgerDetailDescription = "";
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetail);
_serverContext.SaveChanges();
}
//if (StocksAvailable(customerInvoiceModel))
//{
var results = customerInvoiceModel.Items;
// collectively sum all qty per itemid inorder to check items availability
//var results = customerInvoiceModel.Items.GroupBy(x => x.SalesItem.Value).Select(g => new { Id = g.Key, Value = g.Sum(s => s.Qty) });
//decimal costs = 0;
foreach (var item in results)
{
var inventoryProductType = (_serverContext.Inventories.Where(x => x.Id == item.SalesItem.Value && x.InventoryProductServiceType == "P").Select(x => new { x.InventoryProductServiceType, x.Id }).SingleOrDefault());
if (inventoryProductType.InventoryProductServiceType == "P")
{
/*var balance = (from a in _serverContext.ViewInventoryBalances
* where a.Id == item.SalesItem.Value
* select new { balance = a.Balance }).FirstOrDefault();
*
* var unitCost = (from a in _serverContext.ViewInventoryBalances
* where a.Id == item.SalesItem.Value
* select new { unitCost = a.UnitCost }).FirstOrDefault();*/
//decimal costs = Math.Round(Convert.ToDecimal(balance) * Convert.ToDecimal(unitCost), 7);
//var costOfSalesAccountId = _serverContext.Inventories.Where(x => x.Id == item.Id).Select(x => x.InventoryProductServiceExpenseAccountId).SingleOrDefault();
//var inventoryAccountId = _serverContext.Inventories.Where(x => x.Id == item.Id).Select(x => x.InventoryProductServiceAssetAccountId).SingleOrDefault();
var costs = (from a in _serverContext.ViewInventoryBalances
where a.Id == item.SalesItem.Value
select new { costBalance = a.Balance * a.UnitCost }).FirstOrDefault();
GeneralLedgerDetail generalLedgerDetailDebit = new GeneralLedgerDetail();
var tempDebit = _serverContext.Inventories.Where(x => x.Id == item.SalesItem.Value).FirstOrDefault();
generalLedgerDetailDebit.ChartOfAccountId = Convert.ToInt32(tempDebit.InventoryProductServiceExpenseAccountId);
generalLedgerDetailDebit.GeneralLedgerDetailMode = "D";
generalLedgerDetailDebit.GeneralLedgerId = generalLedger.Id;
generalLedgerDetailDebit.GeneralLedgerDetailAmount = Convert.ToDecimal(costs.costBalance);
generalLedgerDetailDebit.GeneralLedgerDetailDescription = item.Description;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetailDebit);
_serverContext.SaveChanges();
GeneralLedgerDetail generalLedgerDetailCredit = new GeneralLedgerDetail();
var tempCredit = _serverContext.Inventories.Where(x => x.Id == item.SalesItem.Value).FirstOrDefault();
generalLedgerDetailCredit.ChartOfAccountId = Convert.ToInt32(tempCredit.InventoryProductServiceAssetAccountId);
generalLedgerDetailCredit.GeneralLedgerDetailMode = "C";
generalLedgerDetailCredit.GeneralLedgerId = generalLedger.Id;
generalLedgerDetailCredit.GeneralLedgerDetailAmount = Convert.ToDecimal(costs.costBalance);
generalLedgerDetailDebit.GeneralLedgerDetailDescription = item.Description;
_serverContext.GeneralLedgerDetails.Add(generalLedgerDetailCredit);
_serverContext.SaveChanges();
}
}
}
