private void GetDebitCreditBalance(int ledgerAccountId, out decimal debitbalance, out decimal creditbalance)
{
debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == ledgerAccountId)
.Where(b => b.JournalEntryDate.Date < FromDate).Sum(b => b.Debit);
creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == ledgerAccountId)
.Where(b => b.JournalEntryDate.Date < FromDate).Sum(b => b.Credit);
}
private string ResolveBalance(string StartDate, string EndDate, int transactionId, int ledgerAccountId)
{
var v = Ledgergenerals
.Where(a => a.JournalEntryDate >= DateTime.Parse("10/11/2017") && a.JournalEntryDate <= DateTime.Parse("10/12/2017"))
.Where(a => a.LedgerAccountId == 1);
List <Ledgergeneral> ldd = new List <Ledgergeneral>();
List <OppAccount> oppacounts = new List <ViewModels.OppAccount>();
foreach (var item in v)
{
ldd.Add(item);
}
decimal creditsum = ldd.Sum(a => a.Credit);
decimal debitsum = ldd.Sum(a => a.Debit);
string balance = "";
if (creditsum > debitsum)
{
balance = "cBalance b/d" + (creditsum - debitsum).ToString();
}
if (debitsum > creditsum)
{
balance = "cBalance b/d" + (debitsum - creditsum).ToString();
}
return(balance);
}
private void SaveCommand_Execute(object obj)
{
SelectedLedgerAccount.CompanyId = 1;
if (SelectedLedgerAccount.IsSystemLedger)
{
////MessageBox.Show("Sorry Cannot Modify System Ledgers");
return;
}
SelectedLedgerAccount.IsSystemLedger = false;
if (Service.SaveLedgerAccount(SelectedLedgerAccount) > 0)
{
var parentaccount = SelectedLedgerAccount.parentLedgerAccount;
if (parentaccount != null)
{
var lgs = Ledgergenerals.Where(a => a.LedgerAccountId == parentaccount.LedgerAccountId);
foreach (var item in lgs)
{
item.LedgerAccountId = SelectedLedgerAccount.LedgerAccountId;
Service.SaveLedgerGeneral(item);
}
var ltds = LedgerTransactionDetails.Where(a => a.LedgerAccountId == parentaccount.LedgerAccountId);
foreach (var item in ltds)
{
item.LedgerAccountId = SelectedLedgerAccount.LedgerAccountId;
Service.SaveLedgerTransactionDetail(item);
}
}
}
Task.Run(() => Init());
SelectedLedgerAccount = null;
}
private void GetDebitCreditTotal(LedgerAccount LedgerAccount, out decimal debitbalance, out decimal creditbalance)
{
debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
//.Where(a => a.JournalEntryDate.Date <= IncomeStatementDate.Date)
.Sum(b => b.Debit);
creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
//.Where(a => a.JournalEntryDate.Date <= IncomeStatementDate.Date)
.Sum(b => b.Credit);
}
public ObservableCollection <BalanceViewModel> CalculateTotals()
{
BalanceViewModels = new ObservableCollection <BalanceViewModel>();
decimal debitbalance = 0;
decimal creditbalance = 0;
var balance = 0m;
string suffix = "";
debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == SelectedLedgerAccount.LedgerAccountId)
.Where(b => b.JournalEntryDate.Date <= ToDate.Date).Sum(b => b.Debit);
creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == SelectedLedgerAccount.LedgerAccountId)
.Where(b => b.JournalEntryDate.Date <= ToDate.Date).Sum(b => b.Credit);
var debitbalancetotal = Ledgergenerals.Where(b => b.LedgerAccountId == SelectedLedgerAccount.LedgerAccountId)
.Sum(b => b.Debit);
var creditbalancetotal = Ledgergenerals.Where(b => b.LedgerAccountId == SelectedLedgerAccount.LedgerAccountId)
.Sum(b => b.Credit);
if (debitbalance > creditbalance)
{
balance = debitbalance - creditbalance;
suffix = "dr";
}
if (debitbalance < creditbalance)
{
balance = creditbalance - debitbalance;
suffix = "cr";
}
var balancetotal = 0m;
if (debitbalancetotal > creditbalancetotal)
{
balancetotal = debitbalancetotal;
}
if (debitbalancetotal < creditbalancetotal)
{
balancetotal = creditbalancetotal;
}
BalanceViewModel balancevm1 = new BalanceViewModel
{
BalanceId = 1,
TotalString = "Total " + balancetotal.ToString("C")
};
BalanceViewModel balancevm2 = new BalanceViewModel
{
BalanceId = 2,
TotalString = " Balance c/d " + balance.ToString("C") + suffix
};
BalanceViewModels.Add(balancevm1);
BalanceViewModels.Add(balancevm2);
return(BalanceViewModels);
}
public IEnumerable <LedgerJournalVm> GetLedgerReport(int LedgerAccountId = 1)
{
var ledger = Ledgergenerals.Where(a => a.LedgerAccountId == LedgerAccountId).FirstOrDefault();
if (ledger == null)
{
LedgerName = "No Ledger Found";
return(null);
}
else
{
LedgerName = Ledgergenerals.Where(a => a.LedgerAccountId == LedgerAccountId).FirstOrDefault().LedgerAccount.AccountName + " Ledger";
}
var data = Ledgergenerals.Where(a => a.LedgerAccountId == LedgerAccountId)
.Where(a => a.JournalEntryDate.Date >= FromDate && a.JournalEntryDate.Date <= ToDate)
.OrderBy(a => a.JournalEntryDate.Date)
.Select(a => new LedgerJournalVm
{
TransactionId = a.LedgerTransactionId,
Date = a.JournalEntryDate.Date,
LedgerAccount = a.LedgerAccount.AccountName,
DebitString = a.Debit > 0 ? ResolveOpp(a.LedgerTransactionId, a.LedgerAccountId) : "",
CreditString = a.Credit > 0 ? ResolveOpp(a.LedgerTransactionId, a.LedgerAccountId) : "",
Particulars = LedgerTransactions.Where(b => b.LedgerTransactionId == a.LedgerTransactionId).Select(b => b.Description).FirstOrDefault(),
// OtherLedgerAccount = ResolveOpp(a.LedgerTransactionId, a.LedgerAccountId),
Debit = a.Debit,
Credit = a.Credit,
StartDate = FromDate,
EndDate = ToDate,
// Balance=ResolveBalance("10/11/2017", "21/12/2017", a.LedgerTransactionId, a.LedgerAccountId)
Balance = GetBalance(a)
}).ToList();
data.Insert(0, new LedgerJournalVm {
Balance = ResolveBalanceBroughtDown(LedgerAccountId)
});
// data.Insert(0, new LedgerJournalVm { Balance = ResolveBalanceBroughtDown(LedgerAccountId) });
JournalVms = new ObservableCollection <LedgerJournalVm>(data);
CalculateTotals();
return(JournalVms);
}
private void GetJournalEntries(DateTime fromDate, DateTime toDate)
{
// var data = Ledgergenerals.GroupBy(a => a.LedgerTransactionId);
// var data = Ledgergenerals.Where(a => a.JournalEntryDate.Date == FromDate.Date).ToList();
var data =
Ledgergenerals.Join(LedgerTransactions, b => b.LedgerTransactionId, a => a.LedgerTransactionId, (a, b) =>
new
{
LedgerTransactionId = a.LedgerTransactionId,
JournalEntryDate = a.JournalEntryDate,
AccountName = a.LedgerAccount.AccountName,
Particulars = a.Credit == 0? b.Description:"",
Debit = a.Debit,
Credit = a.Credit
}
).OrderBy(a => a.JournalEntryDate).Where(a => a.JournalEntryDate.Date >= FromDate).OrderBy(a => a.Debit)
.Where(a => a.JournalEntryDate.Date <= ToDate).ToList();
}
public IEnumerable <LedgerJournalVm> GetLedgerReport(int LedgerAccountId = 1)
{
CultureInfo info = new CultureInfo("ne-NP");
info.NumberFormat.CurrencySymbol = "Rs";
info.DateTimeFormat = new DateTimeFormatInfo();
info.DateTimeFormat.Calendar = new GregorianCalendar(GregorianCalendarTypes.Localized);
info.DateTimeFormat.AMDesignator = "AM";
info.DateTimeFormat.PMDesignator = "PM";
System.Threading.Thread.CurrentThread.CurrentCulture = info;
Thread.CurrentThread.CurrentUICulture = info;
var data = Ledgergenerals.Where(a => a.LedgerAccountId == LedgerAccountId)
.Where(a => a.JournalEntryDate.Date >= FromDate.Date && a.JournalEntryDate.Date <= ToDate.Date)
.OrderBy(a => a.JournalEntryDate.Date)
.Select(a => new LedgerJournalVm
{
TransactionId = a.LedgerTransactionId,
Date = a.JournalEntryDate.Date,
LedgerAccount = a.LedgerAccount.AccountName,
Debit = a.Debit, //== 0 ? "" : a.Debit.ToString(),
Credit = a.Credit, // == 0 ? "" : a.Credit.ToString(),
Particulars = LedgerTransactions.Where(b => b.LedgerTransactionId == a.LedgerTransactionId).Select(b => b.Description).FirstOrDefault(),
OtherLedgerAccount = ResolveOpp(a.LedgerTransactionId, a.LedgerAccountId),
StartDate = FromDate.Date,
EndDate = ToDate.Date,
Balance = GetBalance(a),
}).ToList();
data.Insert(0, new LedgerJournalVm
{
Date = DateTime.Parse("01/01/2001"),
StartDate = FromDate,
EndDate = ToDate,
LedgerAccount = SelectedLedgerAccount.AccountName,
Balance = ResolveBalanceBroughtDown(LedgerAccountId)
});
JournalVms = new ObservableCollection <LedgerJournalVm>(data);
JournalVms.Add(new Rms.Reports.LedgerJournalVm {
Date = ToDate, BalanceCarriedDown = ResolveBalanceCarriedDown()
});
return(JournalVms);
}
private string GetBalance(LedgerGeneral a)
{
var debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == a.LedgerAccountId).Where(b => b.JournalEntryDate <= a.JournalEntryDate).Sum(b => b.Debit);
var creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == a.LedgerAccountId).Where(b => b.JournalEntryDate <= a.JournalEntryDate).Sum(b => b.Credit);
decimal balance = 0;
string suffix = "";
if (debitbalance > creditbalance)
{
balance = debitbalance - creditbalance;
suffix = "Dr";
}
if (debitbalance < creditbalance)
{
balance = creditbalance - debitbalance;
suffix = "Cr";
}
return(balance.ToString() + " " + suffix);
}
private TrialBalanceVm GetBalanceForTrialBalance(LedgerAccount LedgerAccount, DateTime Date)
{
var debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= ToTrialBalanceDate.Date && a.JournalEntryDate.Date >= FromTrialBalanceDate)
.Sum(b => b.Debit);
var creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= ToTrialBalanceDate.Date && a.JournalEntryDate.Date >= FromTrialBalanceDate)
.Sum(b => b.Credit);
if (debitbalance == 0 && creditbalance == 0)
{
return(null);
}
decimal balance = 0;
string suffix = "";
TrialBalanceVm tbm = new Rms.Classes.TrialBalanceVm();
tbm.LedgerAccountName = LedgerAccount.AccountName;
//if (debitbalance > creditbalance)
//{
if (LedgerAccount.AccountClassId == 1 || LedgerAccount.AccountClassId == 2)
{
balance = debitbalance - creditbalance;
tbm.DebitSide = balance;
TotalDebit += balance;
suffix = "Dr";
}
else
{
balance = creditbalance - debitbalance;
tbm.CreditSide = balance;
TotalCredit += balance;
suffix = "Cr";
}
tbm.Date = TrialBalanceDate.Date;
// return balance.ToString() + " " + suffix;
suffix = suffix + "q";
return(tbm);
}
public IEnumerable <LedgerJournalVm> GetLedgerReport(int LedgerAccountId)
{
ResolveBalance("ds", "sds", 1, 1);
return(Ledgergenerals.Where(a => a.LedgerAccountId == LedgerAccountId)
.Where(a => a.JournalEntryDate >= DateTime.Parse("10/11/2017", DateTimeFormatInfo.InvariantInfo) && a.JournalEntryDate <= DateTime.Parse("12/20/2018", DateTimeFormatInfo.InvariantInfo))
.OrderBy(a => a.JournalEntryDate)
.Select(a => new LedgerJournalVm
{
TransactionId = a.LedgerTransactionId,
Date = a.JournalEntryDate.Date,
LedgerAccount = a.LedgerAccount.AccountName,
Debit = a.Debit, //== 0 ? "" : a.Debit.ToString(),
Credit = a.Credit, // == 0 ? "" : a.Credit.ToString(),
Particulars = LedgerTransactions.Where(b => b.LedgerTransactionId == a.LedgerTransactionId).Select(b => b.Description).FirstOrDefault(),
OtherLedgerAccount = ResolveOpp(a.LedgerTransactionId, a.LedgerAccountId),
StartDate = DateTime.Parse("10/11/2017", DateTimeFormatInfo.InvariantInfo),
EndDate = DateTime.Parse("12/21/2018", DateTimeFormatInfo.InvariantInfo),
// Balance=ResolveBalance("10/11/2017", "21/12/2017", a.LedgerTransactionId, a.LedgerAccountId)
}).ToList());
}
private void GetDebitCreditTotal(LedgerAccount LedgerAccount, bool ForBalanceSheet, out decimal debitbalance, out decimal creditbalance)
{
if (ForBalanceSheet)
{
debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Debit);
creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Credit);
}
else
{
debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date >= IncomeStatementDate.Date && a.JournalEntryDate.Date <= IncomeStatementDate1.Date)
.Sum(b => b.Debit);
creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date >= IncomeStatementDate.Date && a.JournalEntryDate.Date <= IncomeStatementDate1.Date)
.Sum(b => b.Credit);
}
}
private void GetJournalEntries(DateTime fromDate, DateTime toDate)
{
// var data = Ledgergenerals.GroupBy(a => a.LedgerTransactionId);
// var data = Ledgergenerals.Where(a => a.JournalEntryDate.Date == FromDate.Date).ToList();
var data =
Ledgergenerals.Join(LedgerTransactions, b => b.LedgerTransactionId, a => a.LedgerTransactionId, (a, b) =>
new
{
LedgerTransactionId = a.LedgerTransactionId,
JournalEntryDate = a.JournalEntryDate,
AccountName = a.LedgerAccount.AccountName,
Particulars = a.Credit == 0? b.Description:"",
Debit = a.Debit,
Credit = a.Credit
}
).OrderBy(a => a.JournalEntryDate).Where(a => a.JournalEntryDate.Date >= FromDate).OrderBy(a => a.Debit)
.Where(a => a.JournalEntryDate.Date <= ToDate).ToList();
JournalListRVIew = CollectionViewSource.GetDefaultView(data);
JournalListRVIew.GroupDescriptions.Add(new PropertyGroupDescription("LedgerTransactionId"));
}
public void getJournalsBydate()
{
if (Ledgergenerals == null)
{
return;
}
var data =
Ledgergenerals.OrderByDescending(a => a.JournalEntryDate).ThenBy(a => a.JournalEntryDate.TimeOfDay).Join(LedgerTransactions, b => b.LedgerTransactionId, a => a.LedgerTransactionId, (a, b) =>
new
{
LedgerTransactionId = a.LedgerTransactionId,
JournalEntryDate = a.JournalEntryDate,
AccountName = a.LedgerAccount.AccountName,
Particulars = a.Credit == 0 ? b.Description : "",
Debit = a.Debit,
Credit = a.Credit
}
).Where(a => a.JournalEntryDate.Date == SelectedDate.Date)
.ToList();
// RView = new CollectionView(data);
}
private AssetsVm GetAssets(LedgerAccount LedgerAccount)
{
var debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Debit);
var creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Credit);
if (debitbalance == 0 && creditbalance == 0)
{
return(null);
}
decimal balance = 0;
AssetsVm avm = new AssetsVm();
avm.LedgerAccountName = LedgerAccount.AccountName;
balance = debitbalance - creditbalance;
avm.Amount = balance;
TotalAssetsBalance += balance;
return(avm);
}
private LiabilitiesVm GetLiabilities(LedgerAccount LedgerAccount)
{
var debitbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Debit);
var creditbalance = Ledgergenerals.Where(b => b.LedgerAccountId == LedgerAccount.LedgerAccountId)
.Where(a => a.JournalEntryDate.Date <= BalanceSheetDate.Date)
.Sum(b => b.Credit);
if (debitbalance == 0 && creditbalance == 0)
{
return(null);
}
decimal balance = 0;
LiabilitiesVm lvm = new LiabilitiesVm();
lvm.LedgerAccountName = LedgerAccount.AccountName;
balance = creditbalance - debitbalance;
lvm.Amount = balance;
TotalLiabilitiesBalance += balance;
return(lvm);
}
