public async Task <ApiResponse> Handle(GetDetailOfNotesQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
List <DetailOfNotesSPModel> spNotesDetail = await _dbContext.LoadStoredProc("get_detailofnote_pdf")
.WithSqlParam("to_currency_id", request.CurrencyId)
.WithSqlParam("till_date", request.TillDate.ToString())
.ExecuteStoredProc <DetailOfNotesSPModel>();
List <DetailOfNotesSummaryModel> notesDetail = spNotesDetail.GroupBy(x => new { x.NoteId, x.NoteName })
.Select(x => new DetailOfNotesSummaryModel
{
NoteName = x.First().NoteName,
TotalDebits = Math.Round(x.Sum(y => y.Debit), 3),
TotalCredits = Math.Round(x.Sum(y => y.Credit), 3),
Balance = Math.Round(x.Sum(y => y.Debit) - x.Sum(y => y.Credit), 3),
AccountSummary = x.Select(s => new DetailOfNotesModel
{
AccountCode = s.AccountCode,
AccountName = s.AccountName,
Debit = Math.Round(s.Debit, 3),
Credit = Math.Round(s.Credit, 3)
}).ToList()
}).ToList();
response.data.DetailsOfNotesFinalList = notesDetail;
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetProjectProposalReportQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
List <SPProjectProposalReportModel> proposalReport = new List <SPProjectProposalReportModel>();
try
{
string startDate = request.StartDate == null ? string.Empty : request.StartDate.ToString();
string dueDate = request.DueDate == null ? string.Empty : request.DueDate.ToString();
//get Project Proposal Report from sp get_projectproposalreport by passing parameters
var spProposalReport = await _dbContext.LoadStoredProc("get_projectproposalreport")
.WithSqlParam("projectname", request.ProjectName)
.WithSqlParam("startdate", startDate)
.WithSqlParam("enddate", dueDate)
.WithSqlParam("startdatefilteroption", request.StartDateFilterOption)
.WithSqlParam("duedatefilteroption", request.DueDateFilterOption)
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("amount", request.Amount)
.WithSqlParam("amountfilteroption", request.AmountFilterOption)
.WithSqlParam("iscompleted", request.IsCompleted)
.WithSqlParam("islate", request.IsLate)
.ExecuteStoredProc <SPProjectProposalReportModel>();
var total = spProposalReport.Count();
response.data.TotalCount = total;
response.data.ProjectProposalReportList = spProposalReport.Skip(request.PageIndex.Value * request.PageSize.Value).Take(request.PageSize.Value).ToList();
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetJournalVoucherDetailsQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
int voucherDetailsCount = 0;
List <JournalVoucherViewModel> listJournalView = new List <JournalVoucherViewModel>();
if (request != null)
{
//get Journal Report from sp get_journal_report by passing parameters
var spJournalReport = await _dbContext.LoadStoredProc("get_journal_report")
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("recordtype", request.RecordType)
.WithSqlParam("fromdate", request.fromdate.ToString())
.WithSqlParam("todate", request.todate.ToString())
.WithSqlParam("officelist", request.OfficesList)
.WithSqlParam("journalno", request.JournalCode)
.WithSqlParam("accountslist", request.AccountLists)
.ExecuteStoredProc <SPJournalReport>();
listJournalView = spJournalReport.Select(x => new JournalVoucherViewModel
{
AccountCode = x.AccountCode,
ChartOfAccountNewId = x.ChartOfAccountNewId,
JournalCode = x.JournalCode,
CreditAmount = x.CreditAmount,
CurrencyId = x.Currency,
DebitAmount = x.DebitAmount,
ReferenceNo = x.ReferenceNo,
TransactionDate = x.TransactionDate,
TransactionDescription = x.TransactionDescription,
VoucherNo = x.VoucherNo,
AccountName = x.AccountName
}).ToList();
var journalReport = spJournalReport.GroupBy(x => x.ChartOfAccountNewId).ToList();
List <JournalReportViewModel> journalReportList = new List <JournalReportViewModel>();
foreach (var accountItem in journalReport)
{
journalReportList.Add(new JournalReportViewModel
{
ChartOfAccountNewId = accountItem.Key,
AccountCode = accountItem.FirstOrDefault(x => x.ChartOfAccountNewId == accountItem.Key).AccountCode,
AccountName = accountItem.FirstOrDefault().AccountName,
DebitAmount = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.DebitAmount)), 4),
CreditAmount = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.CreditAmount)), 4),
Balance = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.DebitAmount) - accountItem.Sum(x => x.CreditAmount)), 4)
});
}
response.data.JournalVoucherViewList = listJournalView;
response.data.JournalReportList = journalReportList; //Report
response.data.TotalCount = voucherDetailsCount;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
else
{
response.data.JournalVoucherViewList = null;
response.data.TotalCount = voucherDetailsCount;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(FilterBudgetLineBreakdownQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
//get Budget line Breakdown from sp get_budgetlinebreakdown by passing parameters
var spBudgetLineBreakdown = await _dbContext.LoadStoredProc("get_budgetlinebreakdown")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("projectid", request.ProjectId)
.WithSqlParam("budgetlinestartdate", request.BudgetLineStartDate.ToString())
.WithSqlParam("budgetlineenddate", request.BudgetLineEndDate.ToString())
.WithSqlParam("budgetlineids", request.BudgetLineId)
.ExecuteStoredProc <SPBudgetLineBeakdown>();
if (spBudgetLineBreakdown.Any())
{
response.data.BudgetLineBreakdownModel = new BudgetLineBreakdownModel();
response.data.BudgetLineBreakdownModel.Expenditure = new List <double>();
response.data.BudgetLineBreakdownModel.Date = new List <DateTime>();
response.data.BudgetLineBreakdownModel.TotalExpectedBudget = new List <double?>();
List <long> projects = new List <long>
{
request.ProjectId
};
var projectsExpectedBudget = await _dbContext.LoadStoredProc("get_totalexpectedprojectbudget")
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("projectid", projects)
.WithSqlParam("comparisiondate", DateTime.UtcNow.ToString())
.ExecuteStoredProc <ProjectExpectedBudget>();
double?totalExpectedBudget = 0.0;
if (projectsExpectedBudget.Any())
{
totalExpectedBudget = projectsExpectedBudget.FirstOrDefault().TotalExpectedProjectBudget;
}
DateTime budgetLineStartDate = request.BudgetLineStartDate;
DateTime budgetLineEndDate = request.BudgetLineEndDate;
List <DateTime> regularIntervalDates = AccountingUtility.GetRegularIntervalDates(budgetLineStartDate, budgetLineEndDate, 6);
if (regularIntervalDates.Any())
{
foreach (var item in regularIntervalDates)
{
response.data.BudgetLineBreakdownModel.Expenditure.Add(spBudgetLineBreakdown.Where(x => x.VoucherDate < item).Sum(x => x.Expenditure));
response.data.BudgetLineBreakdownModel.TotalExpectedBudget.Add(totalExpectedBudget);
response.data.BudgetLineBreakdownModel.Date.Add(item);
}
}
}
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetProjectProposalAmountSummaryQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
List <ProjectProposalAmountSummary> projectProposalAmountSummary = new List <ProjectProposalAmountSummary>();
try
{
string startDate = request.StartDate == null ? string.Empty : request.StartDate.ToString();
string dueDate = request.DueDate == null ? string.Empty : request.DueDate.ToString();
//get GetProjectProposalAmountSummary from sp get_projectproposalreport by passing parameters
var spAmountSummaryInCommonCurrency = await _dbContext.LoadStoredProc("get_projectproposalreportamountsummary")
.WithSqlParam("projectname", request.ProjectName)
.WithSqlParam("startdate", startDate)
.WithSqlParam("enddate", dueDate)
.WithSqlParam("startdatefilteroption", request.StartDateFilterOption)
.WithSqlParam("duedatefilteroption", request.DueDateFilterOption)
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("amount", request.Amount)
.WithSqlParam("amountfilteroption", request.AmountFilterOption)
.WithSqlParam("iscompleted", request.IsCompleted)
.WithSqlParam("islate", request.IsLate)
.ExecuteStoredProc <SPProjectProposalReportAmountSummaryModel>();
var currencyTask = _dbContext.CurrencyDetails.ToListAsync();
if (spAmountSummaryInCommonCurrency.Any())
{
int amountSummaryCurrencyId = spAmountSummaryInCommonCurrency.FirstOrDefault().ProjectCurrency;
double totalAmount = spAmountSummaryInCommonCurrency.Sum(x => x.ProjectAmount);
List <CurrencyDetails> currencies = await currencyTask;
foreach (CurrencyDetails currency in currencies)
{
ExchangeRateDetail exchangeRate = await _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).Where(x => x.FromCurrency == amountSummaryCurrencyId && x.ToCurrency == currency.CurrencyId).FirstOrDefaultAsync();
if (exchangeRate != null)
{
ProjectProposalAmountSummary amountSummary = new ProjectProposalAmountSummary
{
CurrencyId = currency.CurrencyId,
ProposalAmount = totalAmount * (double)exchangeRate.Rate
};
projectProposalAmountSummary.Add(amountSummary);
}
else
{
throw new Exception("Exchange Rate not defined");
}
}
response.data.ProjectProposalAmountSummary = projectProposalAmountSummary;
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
}
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex;
}
return(response);
}
public async Task <ApiResponse> Handle(FilterProjectCashFlowQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
List <VoucherTransactions> transList = new List <VoucherTransactions>();
try
{
//get Project Cashflow Report from sp get_projectcashflow by passing parameters
var spProjectCashFlow = await _dbContext.LoadStoredProc("get_projectcashflow")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("projectid", request.ProjectId)
.WithSqlParam("startdate", request.ProjectStartDate.ToString())
.WithSqlParam("enddate", request.ProjectEndDate.ToString())
.WithSqlParam("donorid", request.DonorID)
.ExecuteStoredProc <SPProjectCashFlowModel>();
if (spProjectCashFlow.Any())
{
response.data.ProjectCashFlowModel = new ProjectCashFlowModel();
response.data.ProjectCashFlowModel.Expenditure = new List <double>();
response.data.ProjectCashFlowModel.Income = new List <double>();
response.data.ProjectCashFlowModel.Date = new List <DateTime>();
response.data.ProjectCashFlowModel.TotalExpectedBudget = new List <double?>();
var projectsExpectedBudget = await _dbContext.LoadStoredProc("get_totalexpectedprojectbudget")
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("projectid", request.ProjectId)
.WithSqlParam("comparisiondate", DateTime.UtcNow.ToString())
.ExecuteStoredProc <ProjectExpectedBudget>();
double?totalExpectedBudget = 0.0;
if (projectsExpectedBudget.Any())
{
totalExpectedBudget = projectsExpectedBudget.FirstOrDefault().TotalExpectedProjectBudget;
}
DateTime startDate = request.ProjectStartDate;
DateTime endDate = request.ProjectEndDate;
List <DateTime> regularIntervalDates = AccountingUtility.GetRegularIntervalDates(startDate, endDate, 6);
if (regularIntervalDates != null && regularIntervalDates.Any())
{
foreach (var item in regularIntervalDates)
{
response.data.ProjectCashFlowModel.TotalExpectedBudget.Add(totalExpectedBudget);
response.data.ProjectCashFlowModel.Expenditure.Add(spProjectCashFlow.Where(x => x.VoucherDate <= item).Sum(x => x.Expenditure));
response.data.ProjectCashFlowModel.Income.Add(spProjectCashFlow.Where(x => x.BudgetLineDate <= item).Sum(x => x.Income));
response.data.ProjectCashFlowModel.Date.Add(item);
}
}
}
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetTrialBalanceReportQuery model, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
DateTime defaultdate = new DateTime(DateTime.UtcNow.Year, 1, 1);
if (model.fromdate == null && model.todate == null)
{
model.fromdate = new DateTime(DateTime.UtcNow.Year, 1, 1);
model.todate = DateTime.UtcNow;
}
if (model != null)
{
List <LedgerModel> finalTrialBalanceList = new List <LedgerModel>();
ICollection <CurrencyDetails> allCurrencies = await _dbContext.CurrencyDetails.Where(x => x.IsDeleted == false).ToListAsync();
//CurrencyDetails baseCurrency = allCurrencies.FirstOrDefault(x => x.Status == true);
//ICollection<ChartOfAccountNew> accountDetail = await _uow.ChartOfAccountNewRepository.FindAllAsync(x => model.accountLists.Contains(x.ChartOfAccountNewId));
#region Commented code for selecting 4th level accounts from level 1, 2, 3 Accounts when UI dropdown contains All Accounts(Level 1, 2, 3, 4)
//List<long> accountLevel4 = new List<long>(); //level 4
//foreach (var accountItem in accountDetail)
//{
// if (accountItem.AccountLevelId == 4)
// {
// // Gets the fourth level accounts
// var fourL = await _uow.GetDbContext().ChartAccountDetail.Where(x => accountItem.AccountCode == x.AccountCode && x.AccountLevelId == 4).Select(x => x.ChartOfAccountCode).ToListAsync();
// accountLevel4.AddRange(fourL);
// }
// else if (accountItem.AccountLevelId == 3)
// {
// var threeL = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.ParentID == accountItem.AccountCode && x.AccountLevelId == 4).Select(x => x.ChartOfAccountCode).ToListAsync();
// accountLevel4.AddRange(threeL);
// }
// else if (accountItem.AccountLevelId == 2)
// {
// // Gets the third level accounts
// var thirdL = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.ParentID == accountItem.AccountCode && x.AccountLevelId == 3).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the fourth level accounts
// var fourL = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.AccountLevelId == 4 && thirdL.Contains(x.ParentID)).Select(x => x.ChartOfAccountCode).ToListAsync();
// accountLevel4.AddRange(fourL);
// }
// else if (accountItem.AccountLevelId == 1)
// {
// // Gets the second level accounts
// var secondL = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.ParentID == accountItem.AccountCode && x.AccountLevelId == 2).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the level 3rd accounts
// var thirdL = await _uow.GetDbContext().ChartAccountDetail.Where(x => secondL.Contains(x.ParentID) && x.AccountLevelId == 3).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the fourth level accounts
// var fourthL = await _uow.GetDbContext().ChartAccountDetail.Where(x => thirdL.Contains(x.ParentID) && x.AccountLevelId == 4).Select(x => x.ChartOfAccountCode).ToListAsync();
// accountLevel4.AddRange(fourthL);
// }
//}
#endregion
var accountFourthLevel = model.accountLists;
// Single
if (model.RecordType == 1)
{
//get trialbalance report from Stored Procedure get_trialbalance_report
var spTrialBalanceReport = await _dbContext.LoadStoredProc("get_trialbalance_report")
.WithSqlParam("currency", model.CurrencyId)
.WithSqlParam("recordtype", model.RecordType)
.WithSqlParam("fromdate", model.fromdate.ToString("MM/dd/yyyy"))
.WithSqlParam("todate", model.todate.ToString("MM/dd/yyyy"))
.WithSqlParam("officelist", model.OfficesList)
.WithSqlParam("accountslist", model.accountLists)
.ExecuteStoredProc <SP_TrialBalanceModel>();
var transactionDetail = spTrialBalanceReport.Select(x => new LedgerModel
{
AccountName = x.AccountName,
ChartOfAccountNewId = x.ChartOfAccountNewId,
Description = x.Description,
CurrencyName = x.CurrencyName,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode
}).ToList();
List <LedgerModel> transactionDetail1 = new List <LedgerModel>();
var accountGroup = transactionDetail.GroupBy(x => x.ChartOfAccountNewId);
foreach (var item in accountGroup)
{
LedgerModel obj = new LedgerModel();
obj = item.FirstOrDefault();
var debit = Math.Round((decimal)item.Sum(x => x.DebitAmount), 4);
var credit = Math.Round((decimal)item.Sum(x => x.CreditAmount), 4);
if (debit > credit)
{
obj.DebitAmount = Convert.ToDouble(Math.Round((debit - credit), 4));
obj.CreditAmount = 0;
}
else if (debit < credit)
{
obj.DebitAmount = 0;
obj.CreditAmount = Convert.ToDouble(Math.Round((credit - debit), 4));
}
else if (debit == credit)
{
obj.DebitAmount = 0;
obj.CreditAmount = 0;
}
finalTrialBalanceList.Add(obj);
}
var noTransactionAccounts = accountFourthLevel.Except(accountGroup.Select(x => (x.Key)));
var allAccountDetails = await _dbContext.ChartOfAccountNew.Where(x => x.IsDeleted == false).ToListAsync();
foreach (var detail in noTransactionAccounts)
{
LedgerModel obj = new LedgerModel();
var noTransactionAccount = allAccountDetails.FirstOrDefault(x => x.ChartOfAccountNewId == detail);
obj.ChartOfAccountNewId = noTransactionAccount.ChartOfAccountNewId;
obj.AccountName = noTransactionAccount.AccountName;
obj.ChartAccountName = noTransactionAccount.AccountName;
obj.Description = "";
obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == model.CurrencyId)?.CurrencyName;
obj.TransactionDate = null;
obj.DebitAmount = 0;
obj.CreditAmount = 0;
obj.ChartOfAccountNewCode = noTransactionAccount.ChartOfAccountNewCode;
finalTrialBalanceList.Add(obj);
}
}
else
{
var accountFourthLevelNotNull = accountFourthLevel.ConvertAll(x => x);
List <LedgerModel> trialBalanceList = new List <LedgerModel>();
finalTrialBalanceList = new List <LedgerModel>();
var spTrialbalanceReport = await _dbContext.LoadStoredProc("get_trialbalance_report")
.WithSqlParam("currency", model.CurrencyId)
.WithSqlParam("recordtype", model.RecordType)
.WithSqlParam("fromdate", model.fromdate.ToString("MM/dd/yyyy"))
.WithSqlParam("todate", model.todate.ToString("MM/dd/yyyy"))
.WithSqlParam("officelist", model.OfficesList)
.WithSqlParam("accountslist", model.accountLists)
.ExecuteStoredProc <SP_TrialBalanceModel>();
trialBalanceList = spTrialbalanceReport.Select(x => new LedgerModel
{
ChartOfAccountNewId = x.ChartOfAccountNewId,
AccountName = x.AccountName,
ChartAccountName = x.AccountName,
Description = x.Description,
CurrencyName = x.CurrencyName,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount
}).ToList();
var accountGroup = trialBalanceList.GroupBy(x => x.ChartOfAccountNewId);
var noTransactionAccounts = accountFourthLevelNotNull.Except(accountGroup.Select(x => (x.Key)));
foreach (var item in accountGroup)
{
LedgerModel obj = new LedgerModel();
obj = item.FirstOrDefault();
var debit = Math.Round((decimal)item.Sum(x => x.DebitAmount), 4);
var credit = Math.Round((decimal)item.Sum(x => x.CreditAmount), 4);
if (debit > credit)
{
obj.DebitAmount = Convert.ToDouble(Math.Round((debit - credit), 4));
obj.CreditAmount = 0;
}
else if (debit < credit)
{
obj.DebitAmount = 0;
obj.CreditAmount = Convert.ToDouble(Math.Round((credit - debit), 4));
}
else if (debit == credit)
{
obj.DebitAmount = 0;
obj.CreditAmount = 0;
}
finalTrialBalanceList.Add(obj);
}
var allAccountDetails = _dbContext.ChartOfAccountNew.Where(x => x.IsDeleted == false);
foreach (var detail in noTransactionAccounts)
{
LedgerModel obj = new LedgerModel();
var noTransactionAccount = allAccountDetails.FirstOrDefault(x => x.ChartOfAccountNewId == detail);
obj.ChartOfAccountNewId = noTransactionAccount.ChartOfAccountNewId;
obj.AccountName = noTransactionAccount.AccountName;
obj.ChartAccountName = noTransactionAccount.AccountName;
obj.Description = "";
obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == model.CurrencyId)?.CurrencyName;
obj.TransactionDate = null;
obj.DebitAmount = 0;
obj.CreditAmount = 0;
obj.ChartOfAccountNewCode = noTransactionAccount.ChartOfAccountNewCode;
finalTrialBalanceList.Add(obj);
}
}
response.data.TrialBalanceList = finalTrialBalanceList;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
else
{
response.data.TrialBalanceList = null;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "No data Found";
}
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetProjectActivityAdvanceFilterListQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
var spActivityList = await _dbContext.LoadStoredProc("get_project_projectactivitylist_filter")
.WithSqlParam("project_id", request.ProjectId)
.WithSqlParam("activity_description", request.ActivityDescription == null ? string.Empty : request.ActivityDescription)
.WithSqlParam("planned_start_date", request.PlannedStartDate == null ? string.Empty : request.PlannedStartDate.Value.ToString())
.WithSqlParam("planned_end_date", request.PlannedEndDate == null ? string.Empty : request.PlannedEndDate.Value.ToString())
.WithSqlParam("actual_start_date", request.ActualStartDate == null ? string.Empty : request.ActualStartDate.Value.ToString())
.WithSqlParam("actual_end_date", request.ActualEndDate == null ? string.Empty : request.ActualEndDate.Value.ToString())
.WithSqlParam("assignee_id", request.AssigneeId)
.WithSqlParam("budget_line_id", request.BudgetLineId)
.WithSqlParam("planning", request.Planning)
.WithSqlParam("implementations", request.Implementation)
.WithSqlParam("completed", request.Completed)
.WithSqlParam("progress_range_min", request.ProgressRangeMin)
.WithSqlParam("progress_range_max", request.ProgressRangeMax)
.WithSqlParam("sleepage_min", request.SleepageMin)
.WithSqlParam("sleepage_max", request.SleepageMax)
.WithSqlParam("duration_min", request.DurationMin)
.WithSqlParam("duration_max", request.DurationMax)
.WithSqlParam("late_start", request.LateStart)
.WithSqlParam("late_end", request.LateEnd)
.WithSqlParam("on_schedule", request.OnSchedule)
.ExecuteStoredProc <SPProjectActivityDetail>();
var activityList = spActivityList.Select(x => new ProjectActivityModel
{
ActivityId = x.ActivityId,
ActivityName = x.ActivityName,
ActivityDescription = x.ActivityDescription,
PlannedStartDate = x.PlannedStartDate,
PlannedEndDate = x.PlannedEndDate,
BudgetLineId = x.BudgetLineId,
BudgetName = x.BudgetName,
EmployeeID = x.EmployeeID,
EmployeeName = x.EmployeeName,
StatusId = x.StatusId,
StatusName = x.StatusName,
Recurring = x.Recurring,
RecurringCount = x.RecurringCount,
RecurrinTypeId = x.RecurrinTypeId,
Progress = Math.Round(x.Progress, 2),
Slippage = x.Sleepage
}).ToList();
response.data.ProjectActivityList = activityList;
response.data.TotalCount = activityList.Count();
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = ex.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetAllLedgerDetailsQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
#region "new"
try
{
List <LedgerModel> closingLedgerList = new List <LedgerModel>();
List <LedgerModel> openingLedgerList = new List <LedgerModel>();
if (request != null)
{
var allCurrencies = await _dbContext.CurrencyDetails.Where(x => x.IsDeleted == false).ToListAsync();
Boolean isRecordPresenntForOffice = await _dbContext.VoucherDetail
.AnyAsync(x => x.IsDeleted == false &&
request.OfficeIdList.Contains(x.OfficeId.Value) &&
x.VoucherDate.Date >= request.fromdate.Date &&
x.VoucherDate.Date <= request.todate.Date);
if (isRecordPresenntForOffice)
{
if (request.RecordType == 1)
{
var spLedgerReportOpening = await _dbContext.LoadStoredProc("get_ledger_report")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("recordtype", request.RecordType)
.WithSqlParam("fromdate", request.fromdate.ToString())
.WithSqlParam("todate", request.todate.ToString())
.WithSqlParam("officelist", request.OfficeIdList)
.WithSqlParam("accountslist", request.accountLists)
.WithSqlParam("openingbalance", true)
.ExecuteStoredProc <SPLedgerReport>();
//Opening Calculation
openingLedgerList = spLedgerReportOpening.Select(x => new LedgerModel
{
ChartOfAccountNewId = x.ChartOfAccountNewId,
AccountName = x.AccountName,
VoucherNo = x.VoucherNo.ToString(),
ChartAccountName = x.AccountName,
Description = x.Description,
VoucherReferenceNo = x.VoucherReferenceNo,
CurrencyName = x.CurrencyName,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount
}).ToList();
var spLedgerReportClosing = await _dbContext.LoadStoredProc("get_ledger_report")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("recordtype", request.RecordType)
.WithSqlParam("fromdate", request.fromdate.ToString())
.WithSqlParam("todate", request.todate.ToString())
.WithSqlParam("officelist", request.OfficeIdList)
.WithSqlParam("accountslist", request.accountLists)
.WithSqlParam("openingbalance", false)
.ExecuteStoredProc <SPLedgerReport>();
closingLedgerList = spLedgerReportClosing.Select(x => new LedgerModel
{
ChartOfAccountNewId = x.ChartOfAccountNewId,
AccountName = x.AccountName,
VoucherNo = x.VoucherNo.ToString(),
ChartAccountName = x.AccountName,
Description = x.Description,
VoucherReferenceNo = x.VoucherReferenceNo,
CurrencyName = x.CurrencyName,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount
}).ToList();
#region old code for single
//// ICollection<ChartAccountDetail> accountLevel4 = null; //level 4
// List<long> accountLevel3 = new List<long>(); //level 3
// List<long> accountLevel2 = new List<long>(); //level 2
// //ICollection<ChartAccountDetail> accountLevel2 = null; //level 2
//foreach (var accountItem in accountDetail)
//{
// if (accountItem.AccountLevelId == 4)
// {
// openingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == accountItem.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date < model.fromdate.Date);
// closingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == accountItem.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date >= model.fromdate.Date &&
// x.TransactionDate.Value.Date <= model.todate.Date);
// //Opening calculation
// foreach (var item in openingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// obj.AccountName = accountItem.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// openingLedgerList.Add(obj);
// }
// }
// //Closing calculation
// foreach (var item in closingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// obj.AccountName = accountItem.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// closingLedgerList.Add(obj);
// }
// }
// }
// else if (accountItem.AccountLevelId == 3)
// {
// // Gets the fourth level accounts
// accountLevel4 = await _uow.ChartAccountDetailRepository.FindAllAsync(x => x.IsDeleted == false && x.ParentID == accountItem.AccountCode && x.AccountLevelId == 4);
// foreach (var elements in accountLevel4)
// {
// openingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date < model.fromdate.Date);
// closingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date >= model.fromdate.Date &&
// x.TransactionDate.Value.Date <= model.todate.Date);
// //Opening calculation
// foreach (var item in openingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// openingLedgerList.Add(obj);
// }
// }
// //Closing calculation
// foreach (var item in closingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// closingLedgerList.Add(obj);
// }
// }
// }
// }
// else if (accountItem.AccountLevelId == 2)
// {
// // Gets the third level accounts
// accountLevel3 = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.IsDeleted == false && x.ParentID == accountItem.AccountCode && x.AccountLevelId == 3).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the fourth level accounts
// accountLevel4 = await _uow.ChartAccountDetailRepository.FindAllAsync(x => x.IsDeleted == false && x.AccountLevelId == 4 && accountLevel3.Contains(x.ParentID));
// foreach (var elements in accountLevel4)
// {
// openingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date < model.fromdate.Date);
// closingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date >= model.fromdate.Date &&
// x.TransactionDate.Value.Date <= model.todate.Date);
// //Opening calculation
// foreach (var item in openingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// openingLedgerList.Add(obj);
// }
// }
// //Closing calculation
// foreach (var item in closingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// closingLedgerList.Add(obj);
// }
// }
// }
// }
// else if (accountItem.AccountLevelId == 1)
// {
// // Gets the second level accounts
// accountLevel2 = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.IsDeleted == false && x.ParentID == accountItem.AccountCode && x.AccountLevelId == 2).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the level 3rd accounts
// accountLevel3 = await _uow.GetDbContext().ChartAccountDetail.Where(x => x.IsDeleted == false && accountLevel2.Contains(x.ParentID) && x.AccountLevelId == 3).Select(x => x.ChartOfAccountCode).ToListAsync();
// // Gets the fourth level accounts
// accountLevel4 = await _uow.ChartAccountDetailRepository.FindAllAsync(x => x.IsDeleted == false && accountLevel3.Contains(x.ParentID) && x.AccountLevelId == 4);
// foreach (var elements in accountLevel4)
// {
// openingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date < model.fromdate.Date);
// closingTransactionDetail = await _uow.VoucherTransactionsRepository
// .FindAllAsync(x => x.IsDeleted == false &&
// x.AccountNo == elements.AccountCode &&
// model.OfficeIdList.Contains(x.OfficeId.Value) &&
// x.CurrencyId == model.CurrencyId &&
// x.TransactionDate.Value.Date >= model.fromdate.Date &&
// x.TransactionDate.Value.Date <= model.todate.Date);
// //Opening Calculation
// foreach (var item in openingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// openingLedgerList.Add(obj);
// }
// }
// //Closing Calculation
// foreach (var item in closingTransactionDetail)
// {
// if (model.CurrencyId == item.CurrencyId)
// {
// LedgerModel obj = new LedgerModel();
// obj.AccountCode = item.AccountNo.Value;
// //obj.AccountName = accountItem.AccountName;
// obj.AccountName = elements.AccountName;
// obj.VoucherNo = item.VoucherNo.ToString();
// obj.ChartAccountName = accountItem.AccountName;
// obj.Description = item.Description;
// obj.CurrencyName = allCurrencies.FirstOrDefault(x => x.CurrencyId == item.CurrencyId)?.CurrencyName;
// obj.CreditAmount = Math.Round(Convert.ToDouble(item.Credit));
// obj.DebitAmount = Math.Round(Convert.ToDouble(item.Debit));
// obj.TransactionDate = item.TransactionDate;
// closingLedgerList.Add(obj);
// }
// }
// }
// }
//}
#endregion
response.data.AccountOpendingAndClosingBL = new AccountOpendingAndClosingBL
{
OpeningBalance = Math.Round(Convert.ToDouble(openingLedgerList.Sum(x => x.DebitAmount) - openingLedgerList.Sum(x => x.CreditAmount))),
//ClosingBalance = opening + closing
ClosingBalance = Math.Round(Convert.ToDouble(openingLedgerList.Sum(x => x.DebitAmount) - openingLedgerList.Sum(x => x.CreditAmount) + (closingLedgerList.Sum(x => x.DebitAmount) - closingLedgerList.Sum(x => x.CreditAmount))))
};
}
else
{
//Consolidate
var spLedgerReportOpening = await _dbContext.LoadStoredProc("get_ledger_report")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("recordtype", request.RecordType)
.WithSqlParam("fromdate", request.fromdate.ToString("MM/dd/yyyy"))
.WithSqlParam("todate", "")
.WithSqlParam("officelist", request.OfficeIdList)
.WithSqlParam("accountslist", request.accountLists)
.WithSqlParam("openingbalance", true)
.ExecuteStoredProc <SPLedgerReport>();
openingLedgerList = spLedgerReportOpening.Select(x => new LedgerModel
{
ChartOfAccountNewId = x.ChartOfAccountNewId,
AccountName = x.AccountName,
VoucherNo = x.VoucherNo.ToString(),
ChartAccountName = x.AccountName,
Description = x.Description,
VoucherReferenceNo = x.VoucherReferenceNo,
CurrencyName = x.CurrencyName,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount
}).ToList();
var spLedgerReportClosing = await _dbContext.LoadStoredProc("get_ledger_report")
.WithSqlParam("currency", request.CurrencyId)
.WithSqlParam("recordtype", request.RecordType)
.WithSqlParam("fromdate", request.fromdate.ToString("MM/dd/yyyy"))
.WithSqlParam("todate", request.todate.ToString("MM/dd/yyyy"))
.WithSqlParam("officelist", request.OfficeIdList)
.WithSqlParam("accountslist", request.accountLists)
.WithSqlParam("openingbalance", false)
.ExecuteStoredProc <SPLedgerReport>();
closingLedgerList = spLedgerReportClosing.Select(x => new LedgerModel
{
ChartOfAccountNewId = x.ChartOfAccountNewId,
AccountName = x.AccountName,
VoucherNo = x.VoucherNo.ToString(),
ChartAccountName = x.AccountName,
Description = x.Description,
VoucherReferenceNo = x.VoucherReferenceNo,
CurrencyName = x.CurrencyName,
TransactionDate = x.TransactionDate,
ChartOfAccountNewCode = x.ChartOfAccountNewCode,
CreditAmount = x.CreditAmount,
DebitAmount = x.DebitAmount
}).ToList();
response.data.AccountOpendingAndClosingBL = new AccountOpendingAndClosingBL
{
OpeningBalance = Math.Round(Convert.ToDouble(openingLedgerList.Sum(x => x.DebitAmount) - openingLedgerList.Sum(x => x.CreditAmount))),
//ClosingBalance = opening + closing
ClosingBalance = Math.Round(Convert.ToDouble(openingLedgerList.Sum(x => x.DebitAmount) - openingLedgerList.Sum(x => x.CreditAmount) + (closingLedgerList.Sum(x => x.DebitAmount) - closingLedgerList.Sum(x => x.CreditAmount))))
//ClosingBalance = debitSum - creditSum + lst.Sum(x => x.TotalDebits) - lst.Sum(x => x.TotalCredits)
};
}
}
}
#region "report data"
var ledgerByAccount = closingLedgerList.GroupBy(x => x.ChartOfAccountNewId).ToList();
List <LedgerReportViewModel> ledgerReportFinal = new List <LedgerReportViewModel>();
foreach (var accountItem in ledgerByAccount)
{
ledgerReportFinal.Add(new LedgerReportViewModel
{
AccountName = accountItem.FirstOrDefault().AccountName,
LedgerList = accountItem.ToList(),
DebitAmount = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.DebitAmount)), 4),
CreditAmount = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.CreditAmount)), 4),
Balance = Math.Round(Convert.ToDecimal(accountItem.Sum(x => x.DebitAmount) - accountItem.Sum(x => x.CreditAmount)), 4)
});
}
#endregion
response.data.LedgerList = closingLedgerList;
response.data.ledgerReportFinal = ledgerReportFinal;
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = ex.Message;
}
#endregion
return(response);
}