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); }