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