public BenchmarkChartListViewModel(List <ChartViewModel> modelList, SchoolComparisonListModel comparisonList, List <ChartViewModel> chartGroups,
ComparisonType comparisonType, BenchmarkCriteria advancedCriteria, SimpleCriteria simpleCriteria, BestInClassCriteria bicCriteria,
FinancialDataModel benchmarkSchoolData, EstablishmentType estabType, EstablishmentType searchedEstabType, string schoolArea, string selectedArea,
string latestTermAcademies, string latestTermMaintained, ComparisonArea areaType, string laCode, long?urn, int basketSize,
TrustComparisonListModel trustComparisonList = null, List <EstablishmentViewModelBase> comparisonSchools = null, bool excludePartial = false)
: base(modelList, comparisonList)
{
this.ChartGroups = chartGroups;
this.AdvancedCriteria = advancedCriteria;
this.SimpleCriteria = simpleCriteria;
this.BicCriteria = bicCriteria;
this.ComparisonType = comparisonType;
this.BenchmarkSchoolData = benchmarkSchoolData;
this.EstablishmentType = estabType;
this.SearchedEstablishmentType = searchedEstabType;
this.SchoolArea = schoolArea;
this.SelectedArea = selectedArea;
this.TrustComparisonList = trustComparisonList;
this.LatestTermAcademies = latestTermAcademies;
this.LatestTermMaintained = latestTermMaintained;
this.AreaType = areaType;
this.LaCode = laCode;
this.URN = urn;
this.BasketSize = basketSize;
this.ComparisonSchools = comparisonSchools;
this.ExcludePartial = excludePartial;
}
private decimal?CalculateAmountPerUnit(FinancialDataModel dataModel, string fieldName, UnitType unit)
{
var rawAmount = GetFinancialDataValueForChartField(fieldName, dataModel.FinancialDataObjectModel);
var pupilCount = dataModel.PupilCount;
var teacherCount = dataModel.TeacherCount;
if (rawAmount == null)
{
return(null);
}
switch (unit)
{
case UnitType.AbsoluteMoney:
case UnitType.PercentageTeachers:
return(rawAmount);
case UnitType.PerTeacher:
return((teacherCount == 0) ? null : (rawAmount / (decimal)teacherCount));
case UnitType.PerPupil:
return((pupilCount == 0) ? null : (rawAmount / (decimal)pupilCount));
case UnitType.PercentageOfTotalExpenditure:
return((dataModel.TotalExpenditure == 0) ? 0 : ((rawAmount / dataModel.TotalExpenditure) * 100));
case UnitType.PercentageOfTotalIncome:
return((dataModel.TotalIncome == 0) ? 0 : ((rawAmount / dataModel.TotalIncome) * 100));
default:
return(rawAmount);
}
}
/// <summary>
/// GetFinancialDataByFilter
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public List <FinancialDataModel> GetFinancialDataByFilter(FinancialDataModel filter)
{
return(CurrentConnectStringContext.StoredProcedure("FinancialData_GetByFilter")
.Parameter("v_ExcelRecordId", filter.ExcelRecordId)
.Parameter("v_XiangMuIdId", filter.XiangMuIdId)
.Parameter("v_XingZhiId", filter.XingZhiId)
.Parameter("v_PianQuId", filter.PianQuId)
.Parameter("v_ShiYeBuId", filter.ShiYeBuId)
.Parameter("v_AccountCode", filter.AccountCode)
.Parameter("v_QiJianTypeId", filter.QiJianTypeId)
.QueryMany <FinancialDataModel>());
}
// Mapping function for multiple table queries.
private static CustomerDataModel MapResults(CustomerDataModel customer, FinancialDataModel financial)
{
if (financial != null)
{
customer.FinancialData = financial;
return(customer);
}
else
{
FinancialDataModel generateModel = new FinancialDataModel();
customer.FinancialData = generateModel;
return(customer);
}
}
public BenchmarkCriteria BuildFromSimpleComparisonCriteria(FinancialDataModel benchmarkSchoolData, bool includeFsm, bool includeSen, bool includeEal, bool includeLa, int percentageMargin = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new [] { benchmarkSchoolData.SchoolOverallPhase };
if (benchmarkSchoolData.UrbanRural != null)
{
criteria.UrbanRural = new[] { benchmarkSchoolData.UrbanRural };
}
var minMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
var maxMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
criteria.MinNoPupil = WithinPositiveLimits(benchmarkSchoolData.PupilCount - CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * minMarginFactor;
criteria.MaxNoPupil = (benchmarkSchoolData.PupilCount + CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * maxMarginFactor;
if (includeFsm)
{
var fsm = benchmarkSchoolData.PercentageOfEligibleFreeSchoolMeals;
criteria.MinPerFSM = WithinPercentLimits(fsm - percentageMargin);
criteria.MaxPerFSM = WithinPercentLimits(fsm + percentageMargin);
}
if (includeSen)
{
var sen = benchmarkSchoolData.PercentageOfPupilsWithSen;
criteria.MinPerSEN = WithinPercentLimits(sen - percentageMargin);
criteria.MaxPerSEN = WithinPercentLimits(sen + percentageMargin);
}
if (includeEal)
{
var eal = benchmarkSchoolData.PercentageOfPupilsWithEal;
criteria.MinPerEAL = WithinPercentLimits(eal - percentageMargin);
criteria.MaxPerEAL = WithinPercentLimits(eal + percentageMargin);
}
if (includeLa)
{
criteria.LocalAuthorityCode = benchmarkSchoolData.LaNumber;
}
criteria.PeriodCoveredByReturn = 12;
return(criteria);
}
private SchoolCharacteristic BuildPhaseCharacteristic(FinancialDataModel latestSchoolData)
{
var phaseText = string.Empty;
if (latestSchoolData != null)
{
if ((latestSchoolData.SchoolOverallPhase == "Primary" || latestSchoolData.SchoolOverallPhase == "Secondary") && (latestSchoolData.SchoolOverallPhase != latestSchoolData.SchoolPhase))
{
phaseText = $"{latestSchoolData.SchoolOverallPhase} ({latestSchoolData.SchoolPhase})";
}
else
{
phaseText = latestSchoolData.SchoolPhase;
}
}
return(new SchoolCharacteristic()
{
Question = SchoolCharacteristicsQuestions.SCHOOL_PHASE, Value = phaseText
});
}
public BenchmarkCriteria BuildFromOneClickComparisonCriteria(FinancialDataModel benchmarkSchoolData, int percentageMargin = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new[] { benchmarkSchoolData.SchoolOverallPhase };
criteria.UrbanRural = new[] { benchmarkSchoolData.UrbanRural };
var minPcMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
var maxPcMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.PC_DEFAULT_MARGIN) / 100m);
criteria.MinNoPupil = WithinPositiveLimits(benchmarkSchoolData.PupilCount - CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * minPcMarginFactor;
criteria.MaxNoPupil = (benchmarkSchoolData.PupilCount + CriteriaSearchConfig.QC_DEFAULT_CONSTANT_PUPIL_COUNT_TOPUP) * maxPcMarginFactor;
var fsm = benchmarkSchoolData.PercentageOfEligibleFreeSchoolMeals;
criteria.MinPerFSM = WithinPercentLimits(fsm - percentageMargin);
criteria.MaxPerFSM = WithinPercentLimits(fsm + percentageMargin);
var sen = benchmarkSchoolData.PercentageOfPupilsWithSen;
criteria.MinPerSEN = WithinPercentLimits(sen - percentageMargin);
criteria.MaxPerSEN = WithinPercentLimits(sen + percentageMargin);
var eal = benchmarkSchoolData.PercentageOfPupilsWithEal;
criteria.MinPerEAL = WithinPercentLimits(eal - percentageMargin);
criteria.MaxPerEAL = WithinPercentLimits(eal + percentageMargin);
var ppGrantFunding = benchmarkSchoolData.PerPupilGrantFunding;
var minGfMarginFactor = 1 - ((percentageMargin + CriteriaSearchConfig.GF_DEFAULT_MARGIN) / 100m);
var maxGfMarginFactor = 1 + ((percentageMargin + CriteriaSearchConfig.GF_DEFAULT_MARGIN) / 100m);
criteria.MinPerPupilGrantFunding = ppGrantFunding * minGfMarginFactor;
criteria.MaxPerPupilGrantFunding = ppGrantFunding * maxGfMarginFactor;
criteria.LondonWeighting = benchmarkSchoolData.LondonWeighting == "Neither" ? new[] { "Neither" } : new[] { "Inner", "Outer" };
criteria.PeriodCoveredByReturn = 12;
return(criteria);
}
public BenchmarkCriteria BuildFromSpecialComparisonCriteria(FinancialDataModel benchmarkSchoolData, SpecialCriteria specialCriteria, int tryCount = 0)
{
var criteria = new BenchmarkCriteria();
criteria.SchoolOverallPhase = new[] { "Special" };
if (specialCriteria.SimilarPupils.GetValueOrDefault())
{
criteria.MinLowestAgePupils = benchmarkSchoolData.LowestAgePupils - CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MaxLowestAgePupils = benchmarkSchoolData.LowestAgePupils + CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MinHighestAgePupils = benchmarkSchoolData.HighestAgePupils - CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
criteria.MaxHighestAgePupils = benchmarkSchoolData.HighestAgePupils + CriteriaSearchConfig.SPECIALS_AGE_EXP_RANGE;
}
foreach (var sen in specialCriteria.TopSenCriteria)
{
criteria.FindAndSetMaxMinSenInCriteria(sen, tryCount);
}
return(criteria);
}
public BenchmarkCriteria BuildFromBicComparisonCriteria(FinancialDataModel benchmarkSchoolData, BestInClassCriteria bicCriteria, int percentageMargin = 0)
{
var bmCriteria = new BenchmarkCriteria()
{
SchoolOverallPhase = new[] { bicCriteria.OverallPhase },
SchoolPhase = CriteriaSearchConfig.BIC_ALLOWED_PHASES,
MinKs2Progress = bicCriteria.Ks2ProgressScoreMin,
MaxKs2Progress = bicCriteria.Ks2ProgressScoreMax,
MinP8Mea = bicCriteria.Ks4ProgressScoreMin,
MaxP8Mea = bicCriteria.Ks4ProgressScoreMax,
MinRRToIncome = bicCriteria.RRPerIncomeMin,
MinNoPupil = WithinPositiveLimits(bicCriteria.NoPupilsMin - (bicCriteria.NoPupilsMin * percentageMargin / 100)),
MaxNoPupil = bicCriteria.NoPupilsMax + (bicCriteria.NoPupilsMax * percentageMargin / 100),
MinPerPupilExp = bicCriteria.PerPupilExpMin,
MaxPerPupilExp = bicCriteria.PerPupilExpMax + (bicCriteria.PerPupilExpMax * percentageMargin / 100),
MinPerFSM = WithinPercentLimits(bicCriteria.PercentageFSMMin - (bicCriteria.PercentageFSMMin * percentageMargin / 100)),
MaxPerFSM = WithinPercentLimits(bicCriteria.PercentageFSMMax + (bicCriteria.PercentageFSMMax * percentageMargin / 100)),
LondonWeighting = bicCriteria.LondonWeighting
};
if (bicCriteria.OverallPhase == "All-through")
{
bmCriteria.SchoolPhase = new[] { "All-through" };
}
if (bicCriteria.SENEnabled)
{
bmCriteria.MinPerSEN = WithinPercentLimits(bicCriteria.PercentageSENMin - (bicCriteria.PercentageSENMin * percentageMargin / 100));
bmCriteria.MaxPerSEN = WithinPercentLimits(bicCriteria.PercentageSENMax + (bicCriteria.PercentageSENMax * percentageMargin / 100));
}
if (bicCriteria.UREnabled)
{
bmCriteria.UrbanRural = new[] { bicCriteria.UrbanRural };
}
return(bmCriteria);
}
public BenchmarkCriteria BuildFromSimpleComparisonCriteria(FinancialDataModel benchmarkSchoolData, SimpleCriteria simpleCriteria, int percentageMargin = 0)
{
return(BuildFromSimpleComparisonCriteria(benchmarkSchoolData, simpleCriteria.IncludeFsm.GetValueOrDefault(),
simpleCriteria.IncludeSen.GetValueOrDefault(), simpleCriteria.IncludeEal.GetValueOrDefault(),
simpleCriteria.IncludeLa.GetValueOrDefault(), percentageMargin));
}
public async Task <ComparisonResult> GenerateBenchmarkListWithSpecialComparisonAsync(BenchmarkCriteria benchmarkCriteria, SpecialCriteria specialCriteria, FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with prefilled criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, EstablishmentType.All, true);
if (benchmarkSchools.Count > ComparisonListLimit.SPECIALS) //Original query returns more than required. Clip to closest schools to the top SEN criteria.
{
var highestSENName = specialCriteria.TopSenCriteria[0].DataName;
var highestSENValue = specialCriteria.TopSenCriteria[0].Original.GetValueOrDefault();
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.getValueByCriteriaName(highestSENName).GetValueOrDefault() - highestSENValue)).Take(ComparisonListLimit.SPECIALS).ToList();
//Updating the criteria to reflect the final min and max used values used on top SEN
var minValue = benchmarkSchools.Min(s => s.getValueByCriteriaName(highestSENName));
var maxValue = benchmarkSchools.Max(s => s.getValueByCriteriaName(highestSENName));
benchmarkCriteria.FindAndSetMaxMinSenInCriteria(highestSENName, minValue, maxValue);
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < ComparisonListLimit.SPECIALS)
{
if (++tryCount > CriteriaSearchConfig.MAX_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromSpecialComparisonCriteria(defaultSchoolFinancialDataModel, specialCriteria, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, EstablishmentType.All, true);
if (benchmarkSchools.Count > ComparisonListLimit.SPECIALS) //Number jumping to more than ideal. Clip to closest schools to the top SEN criteria.
{
var highestSENName = specialCriteria.TopSenCriteria[0].DataName;
var highestSENValue = specialCriteria.TopSenCriteria[0].Original.GetValueOrDefault();
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.getValueByCriteriaName(highestSENName).GetValueOrDefault() - highestSENValue)).Take(ComparisonListLimit.SPECIALS).ToList();
//Updating the criteria to reflect the final min and max used values used on top SEN
var minValue = benchmarkSchools.Min(s => s.getValueByCriteriaName(highestSENName));
var maxValue = benchmarkSchools.Max(s => s.getValueByCriteriaName(highestSENName));
benchmarkCriteria.FindAndSetMaxMinSenInCriteria(highestSENName, minValue, maxValue); break;
}
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
public async Task <ComparisonResult> GenerateBenchmarkListWithBestInClassComparisonAsync(EstablishmentType estType,
BenchmarkCriteria benchmarkCriteria, BestInClassCriteria bicCriteria,
FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with prefilled criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType, true);
if (benchmarkSchools.Count > CriteriaSearchConfig.BIC_TARGET_POOL_COUNT) //Original query returns more than required. Clip from top by per people expenditure proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.PerPupilTotalExpenditure.GetValueOrDefault() - defaultSchoolFinancialDataModel.PerPupilTotalExpenditure.GetValueOrDefault()))
.Take(CriteriaSearchConfig.BIC_TARGET_POOL_COUNT).ToList();
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < CriteriaSearchConfig.BIC_TARGET_POOL_COUNT)
{
if (++tryCount > CriteriaSearchConfig.MAX_BIC_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromBicComparisonCriteria(defaultSchoolFinancialDataModel, bicCriteria, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType, true);
if (benchmarkSchools.Count > CriteriaSearchConfig.BIC_TARGET_POOL_COUNT) //Number jumping to more than ideal. Clip from top by per people expenditure proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.PerPupilTotalExpenditure.GetValueOrDefault() - defaultSchoolFinancialDataModel.PerPupilTotalExpenditure.GetValueOrDefault()))
.Take(CriteriaSearchConfig.BIC_TARGET_POOL_COUNT).ToList();
break;
}
}
//STEP 3: Further reduce pool of 50 (or less) to target 15 by highest progress measure
if (benchmarkSchools.Count > ComparisonListLimit.BIC)
{
benchmarkSchools = benchmarkSchools
.OrderByDescending(b => b.OverallPhase == "Secondary" || b.OverallPhase == "All-through" ? b.Progress8Measure : b.Ks2Progress)
.Take(ComparisonListLimit.BIC)
.ToList();
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
public async Task <ComparisonResult> GenerateBenchmarkListWithOneClickComparisonAsync(BenchmarkCriteria benchmarkCriteria, EstablishmentType estType,
int basketSize, FinancialDataModel defaultSchoolFinancialDataModel)
{
//STEP 1: Straight search with predefined criteria
var benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Original query returns more than required. Clip from top by people count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the used criteria to reflect the max and min pupil count of the found schools
}
//STEP 2: Original query returns less than required. Expand criteria values gradually and try this max 10 times
var tryCount = 0;
while (benchmarkSchools.Count < basketSize)
{
if (++tryCount > CriteriaSearchConfig.MAX_TRY_LIMIT) //Max query try reached. Return whatever is found.
{
break;
}
benchmarkCriteria = _benchmarkCriteriaBuilderService.BuildFromOneClickComparisonCriteria(defaultSchoolFinancialDataModel, tryCount);
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Number jumping to more than ideal. Cut from top by pupil count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the criteria to reflect the max and min pupil count of the found schools
break;
}
}
//STEP 3: Query return is still less than required. Flex the Urban/Rural criteria gradually.
tryCount = 1;
while (benchmarkSchools.Count < basketSize)
{
var urbanRuralDefault = defaultSchoolFinancialDataModel.UrbanRural;
var urbanRuralKey = Dictionaries.UrbanRuralDictionary.First(d => d.Value == urbanRuralDefault).Key;
var urbanRuralQuery = Dictionaries.UrbanRuralDictionary.Where(d =>
d.Key >= urbanRuralKey - tryCount && d.Key <= urbanRuralKey + tryCount).Select(d => d.Value).ToArray();
benchmarkCriteria.UrbanRural = urbanRuralQuery;
benchmarkSchools = await _financialDataService.SearchSchoolsByCriteriaAsync(benchmarkCriteria, estType);
if (benchmarkSchools.Count > basketSize) //Number jumping to more than ideal. Cut from top by pupil count proximity.
{
benchmarkSchools = benchmarkSchools.OrderBy(b => Math.Abs(b.NoPupils.GetValueOrDefault() - defaultSchoolFinancialDataModel.PupilCount.GetValueOrDefault())).Take(basketSize).ToList();
benchmarkCriteria.MinNoPupil = benchmarkSchools.Min(s => s.NoPupils);
benchmarkCriteria.MaxNoPupil = benchmarkSchools.Max(s => s.NoPupils); //Update the criteria to reflect the max and min pupil count of the found schools
break;
}
if (urbanRuralQuery.Length == Dictionaries.UrbanRuralDictionary.Count)
{
break;
}
tryCount++;
}
return(new ComparisonResult()
{
BenchmarkSchools = benchmarkSchools,
BenchmarkCriteria = benchmarkCriteria
});
}
/// <summary>
/// GetFinancialDataByFilter
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public List <FinancialDataModel> GetFinancialDataByFilter(FinancialDataModel filter)
{
return(_financialDataDal.GetFinancialDataByFilter(filter));
}
/// <summary>
/// 处理项目数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="excelId"></param>
private void ProcessDatas(Worksheet sheet, int excelId)
{
var excelDatas = new ExcelDataModel()
{
Datas = new List <XiangMuForExcelDataModel>()
};
for (int i = 3; i < sheet.Cells.MaxDataColumn + 200; i++)
{
if (string.IsNullOrEmpty(GetCellValue(sheet, 6, i)))
{
break;
}
var xiangMuForExcelDataModel = new XiangMuForExcelDataModel()
{
ShiYeBuName = GetCellValue(sheet, 2, i),
PianQuName = GetCellValue(sheet, 1, i),
XingZhiName = string.IsNullOrEmpty(GetCellValue(sheet, 3, i)) ? GetCellValue(sheet, 4, i) : GetCellValue(sheet, 3, i),
XiangMuName = GetCellValue(sheet, 6, i),
QiJianTypeName = GetCellValue(sheet, 5, i),
Datas = new List <DataDetailForExcelDataModel>()
};
for (int j = 8; j < sheet.Cells.MaxDataRow + 200; j++)
{
if (string.IsNullOrEmpty(GetCellValue(sheet, j, 0)))
{
break;
}
var dataDetailForExcelDataModel = new DataDetailForExcelDataModel()
{
AccountCode = GetCellValue(sheet, j, 0),
Data = string.IsNullOrEmpty(GetCellValue(sheet, j, i)) ? 0.0 : Convert.ToDouble(GetCellValue(sheet, j, i))
};
xiangMuForExcelDataModel.Datas.Add(dataDetailForExcelDataModel);
}
excelDatas.Datas.Add(xiangMuForExcelDataModel);
}
foreach (var data in excelDatas.Datas)
{
if (string.IsNullOrEmpty(data.ShiYeBuName))
{
data.ShiYeBuName = data.XiangMuName;
}
}
ProcessXiangMu(excelDatas.Datas, excelId);
var financialDataModels = new List <FinancialDataModel>();
foreach (var excelXiangMuData in excelDatas.Datas)
{
foreach (var detailData in excelXiangMuData.Datas)
{
var financialDataModel = new FinancialDataModel()
{
ExcelRecordId = excelId,
XiangMuIdId = excelXiangMuData.XiangMuId,
XingZhiId = excelXiangMuData.XingZhiId,
PianQuId = excelXiangMuData.PianQuId,
ShiYeBuId = excelXiangMuData.ShiYeBuId,
AccountCode = detailData.AccountCode,
QiJianTypeId = excelXiangMuData.QiJianTypeName.Contains("年") ? 1 : 2,
Data = detailData.Data
};
financialDataModels.Add(financialDataModel);
}
}
_financialDataBll.BatchInsertFinancialData(financialDataModels);
}
private decimal?CalculateWFAmount(FinancialDataModel schoolData, string fieldName, UnitType unit)
{
decimal?amount = null;
decimal?rawAmount;
switch (unit)
{
case UnitType.AbsoluteCount:
amount = GetFinancialDataValueForChartField(fieldName, schoolData.FinancialDataObjectModel);
break;
case UnitType.NoOfPupilsPerMeasure:
rawAmount = GetFinancialDataValueForChartField(fieldName, schoolData.FinancialDataObjectModel);
if (rawAmount == null || rawAmount == 0)
{
break;
}
amount = (schoolData.PupilCount == 0) ? null : ((decimal)schoolData.PupilCount / rawAmount);
if (amount.HasValue)
{
amount = decimal.Round(amount.GetValueOrDefault(), 2, MidpointRounding.AwayFromZero);
}
break;
case UnitType.HeadcountPerFTE:
string fieldNameBase = fieldName.Contains("FullTimeEquivalent")
? fieldName.Substring(0, fieldName.Length - 18)
: fieldName.Substring(0, fieldName.Length - 9);
var total = GetFinancialDataValueForChartField(fieldNameBase + "Headcount", schoolData.FinancialDataObjectModel);
rawAmount = GetFinancialDataValueForChartField(fieldNameBase + "FullTimeEquivalent", schoolData.FinancialDataObjectModel);
if (rawAmount == null || rawAmount == 0)
{
break;
}
if (total == 0)
{
amount = 0;
}
else
{
amount = (total == 0) ? 0 : (total / rawAmount);
amount = decimal.Round(amount.GetValueOrDefault(), 2, MidpointRounding.AwayFromZero);
}
break;
case UnitType.FTERatioToTotalFTE:
total = GetFinancialDataValueForChartField(SchoolTrustFinanceDataFieldNames.WORKFORCE_TOTAL, schoolData.FinancialDataObjectModel);
rawAmount = GetFinancialDataValueForChartField(fieldName, schoolData.FinancialDataObjectModel);
if (rawAmount == null)
{
break;
}
if (total == 0)
{
amount = 0;
}
else
{
amount = (total == 0) ? 0 : (rawAmount / total) * 100;
amount = decimal.Round(amount.GetValueOrDefault(), 2, MidpointRounding.AwayFromZero);
}
break;
}
return(amount);
}
