public int GenerateBudgetDimensionMonthPreview(BudgetDimensionYearPreviewViewModel model) { int budgetDimensionKey = 0; bool projectHasFixedPart = false; try { BudgetBase budgetBase = db.BudgetBases .FirstOrDefault(f => f.iBudgetBaseKey == model.iBudgetBaseKey); BudgetSetting budgetSettings = db.BudgetSettings .Include(i => i.BudgetSectionIndex) .Include(i => i.BudgetSectionIndex.Select(s => s.ReportingStructure)) .Include(i => i.MonthDegreeDayIndex) .OrderByDescending(o => o.iBudgetSettingKey) .FirstOrDefault(); // Create the BudgetDimension BudgetDimension budgetDimension = new BudgetDimension { iBudgetBaseKey = model.iBudgetBaseKey, bDraft = true, BudgetSetting = budgetSettings, iBudgetDimensionTypeKey = model.iBudgetDimensionTypeKey, iProjectKey = model.iProjectKey, iYearBudget = model.iYearBudget, iYearPreview = model.iYearPreview, iEndPeriodPreview = model.iEndPeriodPreview, sBudgetDimensionDescription = model.sDescription, dtLastModified = DateTime.UtcNow, BudgetDimensionRules = new Collection <BudgetDimensionRule>() }; if (model.iProjectKey.HasValue) { ProjectInfo projectInfo = db.ProjectInfo.FirstOrDefault(f => f.iFinProjectKey == model.iProjectKey); if (projectInfo != null) { int[] gasKeys = db.TechnicalPrincipals.Where(w => w.bIsGas).Select(s => s.iTechnicalPrincipalKey).ToArray(); projectHasFixedPart = projectInfo.bHotWater || (projectInfo.iTechnicalPrincipalMainKey.HasValue && gasKeys.Contains(projectInfo.iTechnicalPrincipalMainKey.GetValueOrDefault())); projectHasFixedPart = !projectHasFixedPart?projectInfo.iTechnicalPrincipalSub1Key.HasValue?gasKeys.Contains(projectInfo.iTechnicalPrincipalSub1Key.Value) : false : projectHasFixedPart; projectHasFixedPart = !projectHasFixedPart?projectInfo.iTechnicalPrincipalSub2Key.HasValue?gasKeys.Contains(projectInfo.iTechnicalPrincipalSub2Key.Value) : false : projectHasFixedPart; } } //Check of er gekozen is voor een DCF if (budgetBase.iBudgetBaseTypeKey == 1) { //Hierbij is gekozen voor een DCF Hierbij hoeft geen verdeling over maanden plaats te vinden foreach (BudgetSectionYearPreviewViewModel item in model.BudgetSections) { BudgetDimensionRule budgetDimensionRule = new BudgetDimensionRule { bSpatie = item.bSpatie, bSubtotaal = item.bSubtotaal, dJanuary = item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, dFebruary = 0, dMarch = 0, dApril = 0, dMay = 0, dJune = 0, dJuly = 0, dAugust = 0, dSeptember = 0, dOctober = 0, dNovember = 0, dDecember = 0, dTotal = !item.bSpatie && !item.bSubtotaal ? item.dSuggestion.HasValue ? item.dSuggestion.Value : 0 : 0, iRecNo = item.iRecNo, iReportingStructureKey = item.iReportingStructureKey, sComment = item.sComment }; budgetDimension.BudgetDimensionRules.Add(budgetDimensionRule); } } else { // Hier wordt het bedrag over de maanden uitgesmeerd! Niet nodig voor begroting van DCF. foreach (BudgetSectionYearPreviewViewModel item in model.BudgetSections) { BudgetDimensionRule budgetDimensionRule = new BudgetDimensionRule { bSpatie = item.bSpatie, bSubtotaal = item.bSubtotaal, dJanuary = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 1, budgetSettings.MonthDegreeDayIndex), dFebruary = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 2, budgetSettings.MonthDegreeDayIndex), dMarch = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 3, budgetSettings.MonthDegreeDayIndex), dApril = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 4, budgetSettings.MonthDegreeDayIndex), dMay = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 5, budgetSettings.MonthDegreeDayIndex), dJune = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 6, budgetSettings.MonthDegreeDayIndex), dJuly = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 7, budgetSettings.MonthDegreeDayIndex), dAugust = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 8, budgetSettings.MonthDegreeDayIndex), dSeptember = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 9, budgetSettings.MonthDegreeDayIndex), dOctober = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 10, budgetSettings.MonthDegreeDayIndex), dNovember = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 11, budgetSettings.MonthDegreeDayIndex), dDecember = CalculateMonthSuggestion(item.dSuggestion.HasValue ? item.dSuggestion.Value : 0, budgetSettings.BudgetSectionIndex.FirstOrDefault(f => f.iBudgetSectionIndexKey == item.iBudgetSectionIndexKey), projectHasFixedPart, 12, budgetSettings.MonthDegreeDayIndex), dTotal = !item.bSpatie && !item.bSubtotaal ? item.dSuggestion.HasValue ? item.dSuggestion.Value : 0 : 0, iRecNo = item.iRecNo, iReportingStructureKey = item.iReportingStructureKey, sComment = item.sComment }; budgetDimensionRule.dDecember = item.dSuggestion.GetValueOrDefault() - budgetDimensionRule.dJanuary - budgetDimensionRule.dFebruary - budgetDimensionRule.dMarch - budgetDimensionRule.dApril - budgetDimensionRule.dMay - budgetDimensionRule.dJune - budgetDimensionRule.dJuly - budgetDimensionRule.dAugust - budgetDimensionRule.dSeptember - budgetDimensionRule.dOctober - budgetDimensionRule.dNovember; budgetDimension.BudgetDimensionRules.Add(budgetDimensionRule); } } db.BudgetDimensions.Add(budgetDimension); db.SaveChanges(); budgetDimensionKey = budgetDimension.iBudgetDimensionKey; } catch (Exception e) { throw e; } return(budgetDimensionKey); }
public BudgetDimensionYearPreviewViewModel GenerateBudgetDimensionYearPreview(BudgetDimensionCreateViewModel model) { BudgetDimensionYearPreviewViewModel preview = new BudgetDimensionYearPreviewViewModel(); bool projectHasFixedPart = false; try { BudgetBase budgetBase = db.BudgetBases .FirstOrDefault(f => f.iBudgetBaseKey == model.iBudgetBaseKey); BudgetSetting budgetSettings = db.BudgetSettings .Include(i => i.BudgetSectionIndex) .Include(i => i.BudgetSectionIndex.Select(s => s.ReportingStructure)) .Include(i => i.YearDegreeDayIndex) .OrderByDescending(o => o.iBudgetSettingKey) .FirstOrDefault(); IQueryable <Resultaatoverzicht> financialTransactions = db.Resultaatoverzicht .Where(w => w.iCustomerID == budgetBase.iCustomerID && w.iYear == model.iYearPreview && w.iPeriod <= model.iEndPeriodPreview); IQueryable <Resultaatoverzicht> budgetTransactions = db.Resultaatoverzicht .Where(w => w.iCustomerID == budgetBase.iCustomerID && w.iYear == model.iYearPreview && w.iPeriod > model.iEndPeriodPreview); if (model.iProjectKey.HasValue) { financialTransactions = financialTransactions .Where(w => w.iProjectKey == model.iProjectKey); budgetTransactions = budgetTransactions .Where(w => w.iProjectKey == model.iProjectKey); ProjectInfo projectInfo = db.ProjectInfo.FirstOrDefault(f => f.iFinProjectKey == model.iProjectKey); if (projectInfo != null) { int[] gasKeys = db.TechnicalPrincipals.Where(w => w.bIsGas).Select(s => s.iTechnicalPrincipalKey).ToArray(); projectHasFixedPart = projectInfo.bHotWater || (projectInfo.iTechnicalPrincipalMainKey.HasValue && gasKeys.Contains(projectInfo.iTechnicalPrincipalMainKey.GetValueOrDefault())); projectHasFixedPart = !projectHasFixedPart?projectInfo.iTechnicalPrincipalSub1Key.HasValue?gasKeys.Contains(projectInfo.iTechnicalPrincipalSub1Key.Value) : false : projectHasFixedPart; projectHasFixedPart = !projectHasFixedPart?projectInfo.iTechnicalPrincipalSub2Key.HasValue?gasKeys.Contains(projectInfo.iTechnicalPrincipalSub2Key.Value) : false : projectHasFixedPart; } } preview.iBudgetDimensionTypeKey = model.iBudgetDimensionTypeKey; preview.iBudgetBaseKey = model.iBudgetBaseKey; preview.iProjectKey = model.iProjectKey; preview.sDescription = model.sDescription; preview.iYearPreview = model.iYearPreview; preview.iEndPeriodPreview = model.iEndPeriodPreview; preview.iYearBudget = budgetBase.iYear; preview.BudgetSections = new List <BudgetSectionYearPreviewViewModel>(); foreach (BudgetSectionIndex item in budgetSettings.BudgetSectionIndex) { decimal budgetSaldo = budgetTransactions.Where(w => w.RecNo == item.iRecNo).Count() > 0 ? budgetTransactions.Where(w => w.RecNo == item.iRecNo && w.SaldoBudget.HasValue).Sum(sm => sm.SaldoBudget.Value) : 0; decimal realisedSaldo = financialTransactions.Where(w => w.RecNo == item.iRecNo).Count() > 0 ? financialTransactions.Where(w => w.RecNo == item.iRecNo && w.SaldoRealisatie.HasValue).Sum(sm => sm.SaldoRealisatie.Value) : 0; decimal totalSaldo = budgetSaldo + realisedSaldo; BudgetSectionYearPreviewViewModel previewSection = new BudgetSectionYearPreviewViewModel() { iRecNo = item.iRecNo, iBudgetSectionIndexKey = item.iBudgetSectionIndexKey, iReportingStructureKey = item.iReportingStructureKey, sReportingStructureName = item.ReportingStructure.sDescription, bSpatie = item.bSpatie, bSubtotaal = item.bSubtotaal, bVariable = item.bVariable, bFixedPart = item.bFixedPart, dRealised = realisedSaldo, dBudget = budgetSaldo, dTotal = realisedSaldo + budgetSaldo, dSuggestion = CalculateYearSuggestion(realisedSaldo + budgetSaldo, item, projectHasFixedPart, model.iYearPreview, budgetBase.iYear).Value }; preview.BudgetSections.Add(previewSection); } } catch (Exception e) { throw e; } return(preview); }