public DataTable?GetDataTableBySchemaAction(DataSet dataSet, MissingSchemaAction schemaAction)
{
if (null == dataSet)
{
throw ADP.ArgumentNull(nameof(dataSet));
}
string dataSetTable = DataSetTable;
if (string.IsNullOrEmpty(dataSetTable))
{
return(null);
}
DataTableCollection tables = dataSet.Tables;
int index = tables.IndexOf(dataSetTable);
if ((0 <= index) && (index < tables.Count))
{
return(tables[index]);
}
switch (schemaAction)
{
case MissingSchemaAction.Add:
case MissingSchemaAction.AddWithKey:
return(new DataTable(dataSetTable));
case MissingSchemaAction.Ignore:
return(null);
case MissingSchemaAction.Error:
throw ADP.MissingTableSchema(dataSetTable, SourceTable);
}
throw ADP.InvalidMissingSchemaAction(schemaAction);
}
/// <summary>
/// Reads the excel file and returns a table results
/// </summary>
/// <param name="fileName">The file path of the excel file that will be used in the </param>
/// <returns></returns>
private static DataTable ExcelToDataTable(string fileName, string sheet)
{
//open file and returns as Stream
using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelReader;
//Createopenxmlreader via ExcelReaderFactory
if (fileName.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Return as DataSet
var result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the Tables
DataTableCollection table = result.Tables;
//Store it in DataTable
int changeToSheet = table.IndexOf(sheet);
DataTable resultTable = table[changeToSheet];
return(resultTable);
}
}
static List <PESA2010Entry> ParsePESADBs2010Chap10()
{
string ExcelFilePath = @"Assets/pesa_2010_database_tables_chapter10.xlsx";
string SheetName = @"CRA 2010 Chapter 10 DB final ";
List <PESA2010Entry> PESA2010Entries = new List <PESA2010Entry>();
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <string> FinancialYears = new List <string>()
{
"2004-05",
"2005-06",
"2006-07",
"2007-08",
"2008-09",
"2009-10"
};
FinancialYears.Remove("2009-10"); // already covered in a later dataset
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2010Entry pesaEntry = new PESA2010Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
pesaEntry.DepartmentCode = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[0]);
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.COFOGLevel2 = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.ProgrammeObjectGroup = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[6]);
pesaEntry.ProgrammeObjectGroupAlias = SafeConvertObjectToString(CURRENTTABLE.Rows[i].ItemArray[7]);
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[9];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[10];
PESA2010Entries.Add(pesaEntry);
}
}
return(PESA2010Entries);
}
static List <PESA2018Entry> ParsePESADB2018()
{
string ExcelFilePath = @"Assets/CRA_2018_Database_for_Publication_rvsd.xlsx";
string SheetName = @"CRA 2018 database";
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <PESA2018Entry> PESA2018Entries = new List <PESA2018Entry>();
List <string> FinancialYears = new List <string>()
{
"2013-14",
"2014-15",
"2015-16",
"2016-17",
"2017-18"
};
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2018Entry pesaEntry = new PESA2018Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[0];
pesaEntry.OrganisationName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.CRASegmentCode = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.CRASegmentName = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.COFOGLevel0 = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[6];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[7];
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[9];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[10];
pesaEntry.HMTorDept = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[12];
pesaEntry.Country = (string)CURRENTTABLE.Rows[i].ItemArray[13];
PESA2018Entries.Add(pesaEntry);
}
}
return(PESA2018Entries);
}
// <Snippet1>
private void GetIndexes()
{
// Get the DataSet of a DataGrid.
DataSet thisDataSet = (DataSet)DataGrid1.DataSource;
// Get the DataTableCollection through the Tables property.
DataTableCollection tables = thisDataSet.Tables;
// Get the index of each table in the collection.
foreach (DataTable table in tables)
{
System.Diagnostics.Debug.WriteLine(tables.IndexOf(table));
}
}
// <Snippet1>
private void GetIndexes()
{
// Get the DataSet of a DataGrid.
DataSet thisDataSet = (DataSet)DataGrid1.DataSource;
// Get the DataTableCollection through the Tables property.
DataTableCollection tables = thisDataSet.Tables;
// Get the index of the table named "Authors", if it exists.
if (tables.Contains("Authors"))
{
System.Diagnostics.Debug.WriteLine(tables.IndexOf("Authors"));
}
}
public void IndexOf()
{
DataTableCollection tbcol = _dataset[0].Tables;
tbcol.Add(_tables[0]);
tbcol.Add("table1");
tbcol.Add("table2");
AssertEquals("test#1", 0, tbcol.IndexOf(_tables[0]));
AssertEquals("test#2", -1, tbcol.IndexOf(_tables[1]));
AssertEquals("test#3", 1, tbcol.IndexOf("table1"));
AssertEquals("test#4", 2, tbcol.IndexOf("table2"));
AssertEquals("test#5", 0, tbcol.IndexOf(tbcol[0]));
AssertEquals("test#6", 1, tbcol.IndexOf(tbcol[1]));
AssertEquals("test#7", -1, tbcol.IndexOf("_noTable_"));
DataTable tb = new DataTable("new_table");
AssertEquals("test#8", -1, tbcol.IndexOf(tb));
}
public void IndexOf()
{
DataTableCollection tbcol = _dataset[0].Tables;
tbcol.Add(_tables[0]);
tbcol.Add("table1");
tbcol.Add("table2");
Assert.AreEqual(0, tbcol.IndexOf(_tables[0]), "test#1");
Assert.AreEqual(-1, tbcol.IndexOf(_tables[1]), "test#2");
Assert.AreEqual(1, tbcol.IndexOf("table1"), "test#3");
Assert.AreEqual(2, tbcol.IndexOf("table2"), "test#4");
Assert.AreEqual(0, tbcol.IndexOf(tbcol[0]), "test#5");
Assert.AreEqual(1, tbcol.IndexOf(tbcol[1]), "test#6");
Assert.AreEqual(-1, tbcol.IndexOf("_noTable_"), "test#7");
DataTable tb = new DataTable("new_table");
Assert.AreEqual(-1, tbcol.IndexOf(tb), "test#8");
}
public void IndexOf()
{
DataTableCollection tbcol = _dataset[0].Tables;
tbcol.Add(_tables[0]);
tbcol.Add("table1");
tbcol.Add("table2");
Assert.Equal(0, tbcol.IndexOf(_tables[0]));
Assert.Equal(-1, tbcol.IndexOf(_tables[1]));
Assert.Equal(1, tbcol.IndexOf("table1"));
Assert.Equal(2, tbcol.IndexOf("table2"));
Assert.Equal(0, tbcol.IndexOf(tbcol[0]));
Assert.Equal(1, tbcol.IndexOf(tbcol[1]));
Assert.Equal(-1, tbcol.IndexOf("_noTable_"));
DataTable tb = new DataTable("new_table");
Assert.Equal(-1, tbcol.IndexOf(tb));
}
internal void BuildIntervalTables()
{
string[] intervals = IntervalList();
foreach (string interval in intervals)
{
NormalizeDates(interval);
}
// build table for each interval.
// using interval name for table name
for (int j = 0; j < intervals.Length; j++)
{
if (Tables.IndexOf(intervals[j]) >= 0)
{
Tables.Remove(intervals[j]);
}
MultipleSeriesDataTable msTable = new MultipleSeriesDataTable(this, intervals[j]);
Tables.Add(msTable);
}
}
public DataTable GetWorksheet(string filePath, string worksheetName)
{
DataTable table;
using (Stream fileStream = _fileSystem.ReadIntoFileStream(filePath))
{
DataTableCollection workbookData = _excelWorkbookReader.GetWorkbookData(fileStream).Tables;
if (workbookData.IndexOf(worksheetName) > -1)
{
table = workbookData[worksheetName];
modifyColumnNames(table);
}
else
{
string message = string.Format("The workbook is missing worksheet named '{0}'", worksheetName);
throw new ApplicationException(message);
}
}
return(table);
}
private void btnBrowse_Click(object sender, EventArgs e)
{
if (ofd.ShowDialog() == DialogResult.OK)
{
txtfilePath.Text = ofd.FileName;
//Save the uploaded Excel file.
string filePath = txtfilePath.Text;
DataSet ds = Common.OleDBParse(filePath);//SpreadsheetDocument.Open(ofd.FileName, false).GetDataTableFromSpreadSheet();
// Get the DataTableCollection through the Tables property.
DataTableCollection tables = ds.Tables;
cmbSheets.Items.Clear();
cmbSheets.Items.Add(ComboboxItem.DefaultItem);
// Get the index of the table named "Authors", if it exists.
foreach (DataTable dt in tables)
{
cmbSheets.Items.Add(new ComboboxItem()
{
Text = dt.TableName, Value = tables.IndexOf(dt.TableName)
});
}
cmbSheets.SelectedIndex = 0;
dgvData.Tag = ds;
}
}
public DataTable GetDataTableBySchemaAction(DataSet dataSet, MissingSchemaAction schemaAction)
{
if (null == dataSet)
{
throw ADP.ArgumentNull(nameof(dataSet));
}
string dataSetTable = DataSetTable;
if (string.IsNullOrEmpty(dataSetTable))
{
#if DEBUG
if (AdapterSwitches.DataSchema.TraceWarning)
{
Debug.WriteLine("explicit filtering of SourceTable \"" + SourceTable + "\"");
}
#endif
return(null);
}
DataTableCollection tables = dataSet.Tables;
int index = tables.IndexOf(dataSetTable);
if ((0 <= index) && (index < tables.Count))
{
#if DEBUG
if (AdapterSwitches.DataSchema.TraceInfo)
{
Debug.WriteLine("schema match on DataTable \"" + dataSetTable);
}
#endif
return(tables[index]);
}
switch (schemaAction)
{
case MissingSchemaAction.Add:
case MissingSchemaAction.AddWithKey:
#if DEBUG
if (AdapterSwitches.DataSchema.TraceInfo)
{
Debug.WriteLine("schema add of DataTable \"" + dataSetTable + "\"");
}
#endif
return(new DataTable(dataSetTable));
case MissingSchemaAction.Ignore:
#if DEBUG
if (AdapterSwitches.DataSchema.TraceWarning)
{
Debug.WriteLine("schema filter of DataTable \"" + dataSetTable + "\"");
}
#endif
return(null);
case MissingSchemaAction.Error:
#if DEBUG
if (AdapterSwitches.DataSchema.TraceError)
{
Debug.WriteLine("schema error on DataTable \"" + dataSetTable + "\"");
}
#endif
throw ADP.MissingTableSchema(dataSetTable, SourceTable);
}
throw ADP.InvalidMissingSchemaAction(schemaAction);
}
public Int32 IndexOf(FeatureDataTable item)
{
return(_tables.IndexOf(item));
}
static List <PESA2005Entry> ParsePESA2005()
{
string ExcelFilePath = @"Assets/pesa2005_chapter8_tablesv3.xls";
//List<string> RegionNames = new List<string>() { "North East", "North West", "Yorkshire and Humberside", "East Midlands", "West Midlands", "South West", "Eastern", "London", "South East", "Total England", "Scotland", "Wales", "Northern Ireland", "UK Identifiable expenditure", "Outside UK", "Total Identifiable expenditure" };
List <string> ListOfSheetsToParse = new List <string>()
{
"8.5a", "8.5b", "8.6a", "8.6b", "8.7a", "8.7b", "8.8a", "8.8b", "8.9a", "8.9b", "8.10a", "8.10b"
};
List <PESA2005Entry> PESAEntries = new List <PESA2005Entry>();
foreach (string SheetName in ListOfSheetsToParse)
{
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
string Title = (string)CURRENTTABLE.Rows[0].ItemArray[0];
List <string> Units = CURRENTTABLE.Rows[2].ItemArray.OfType <string>().ToList();
string Unit = Units.Last();
List <string> Headers = CURRENTTABLE.Rows[3].ItemArray.OfType <string>().ToList();
List <PESA2005Entry> PESAEntriesForThisSheet = new List <PESA2005Entry>();
for (int i = 4; i < 22; i++)
{
foreach (string function in Headers)
{
if (CURRENTTABLE.Rows[i].ItemArray[0] != System.DBNull.Value)
{
PESA2005Entry pesaEntry = new PESA2005Entry();
pesaEntry.Geography = (string)CURRENTTABLE.Rows[i].ItemArray[0];
pesaEntry.Function = function.Trim();
pesaEntry.TableTitle = Title;
pesaEntry.Unit = Unit;
pesaEntry.FinancialYear = Title.Split(",").Last().Trim();
pesaEntry.YearEnd = int.Parse(string.Join("", pesaEntry.FinancialYear.Take(4))) + 1;
pesaEntry.Value = SafeConvertObjectToInt(CURRENTTABLE.Rows[i].ItemArray[Headers.IndexOf(function) + 1]);
if (Title.Contains("current"))
{
pesaEntry.CAPorCUR = "CUR";
}
if (Title.Contains("capital"))
{
pesaEntry.CAPorCUR = "CAP";
}
PESAEntriesForThisSheet.Add(pesaEntry);
}
}
}
PESAEntries.AddRange(PESAEntriesForThisSheet);
}
return(PESAEntries);
}
static List <PESA2014Entry> ParsePESADB2014()
{
string ExcelFilePath = @"Assets/CRA_2014_Combined_Database_for_Publication.xlsx";
string SheetName = @"CRA14 combined database";
DataTable CURRENTTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
CURRENTTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
List <PESA2014Entry> PESA2014Entries = new List <PESA2014Entry>();
List <string> FinancialYears = new List <string>()
{
"2009-10",
"2010-11",
"2011-12",
"2012-13",
"2013-14"
};
FinancialYears.Remove("2013-14"); // already covered in a later dataset
for (int i = 1; i < CURRENTTABLE.Rows.Count; i++)
{
foreach (string FinancialYear in FinancialYears)
{
PESA2014Entry pesaEntry = new PESA2014Entry();
pesaEntry.FinancialYear = FinancialYear;
pesaEntry.YearEnd = int.Parse(FinancialYear.Substring(0, 4)) + 1;
pesaEntry.Value = SafeConvertObjectToDouble(CURRENTTABLE.Rows[i].ItemArray[CURRENTTABLE.Rows[0].ItemArray.OfType <string>().ToList().IndexOf(FinancialYear)]);
// Department COFOG Level 0 HMT Function COFOG Level 1 HMT Subfunction CRA Segment CRA Segment Description ID/ non - ID CAP or CUR CG, LG or PC NUTS Region Country
pesaEntry.DepartmentName = (string)CURRENTTABLE.Rows[i].ItemArray[0];
//pesaEntry.OrganisationName = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.CRASegmentCode = (string)CURRENTTABLE.Rows[i].ItemArray[5];
pesaEntry.CRASegmentName = (string)CURRENTTABLE.Rows[i].ItemArray[6];
pesaEntry.COFOGLevel0 = (string)CURRENTTABLE.Rows[i].ItemArray[1];
pesaEntry.HMTFunction = (string)CURRENTTABLE.Rows[i].ItemArray[2];
pesaEntry.COFOGLevel1 = (string)CURRENTTABLE.Rows[i].ItemArray[3];
pesaEntry.HMTSubfunction = (string)CURRENTTABLE.Rows[i].ItemArray[4];
pesaEntry.IDNonID = (string)CURRENTTABLE.Rows[i].ItemArray[7];
pesaEntry.CAPorCUR = (string)CURRENTTABLE.Rows[i].ItemArray[8];
pesaEntry.CGorLGorPCorBOE = (string)CURRENTTABLE.Rows[i].ItemArray[9];
//pesaEntry.HMTorDept = (string)CURRENTTABLE.Rows[i].ItemArray[11];
pesaEntry.NUTSRegion = (string)CURRENTTABLE.Rows[i].ItemArray[10];
pesaEntry.Country = (string)CURRENTTABLE.Rows[i].ItemArray[11];
PESA2014Entries.Add(pesaEntry);
}
}
return(PESA2014Entries);
}
static void Main(string[] args)
{
// Needed to make ExcelReader work
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
// This methodology is largey a port from R to C# .NET of the excellent code at https://github.com/oscci/REFvsWellcome
// Additionally I assign Univerisities to UK NUTS1 regions and create regional sum scores
// The REF results spreadsheet is available at https://results.ref.ac.uk/(S(5azlsbi3iciltmzxzr4wfez5))/DownloadResults
List <UniversityRegion> UniversityRegions = new List <UniversityRegion>();
using (StreamReader textReader = new StreamReader("Assets/UniversityRegionsLink.csv"))
{
using (CsvReader csvReader = new CsvReader(textReader))
{
UniversityRegions = csvReader.GetRecords <UniversityRegion>().ToList();
}
}
// Load panel weightings
Dictionary <string, double> PanelWeightings = new Dictionary <string, double>();
using (StreamReader textReader = new StreamReader("Assets/PanelWeightings.csv"))
{
using (CsvReader csvReader = new CsvReader(textReader))
{
PanelWeightings = csvReader.GetRecords <PanelWeighting>().ToDictionary(x => x.Panel, x => x.Weighting);
}
}
// Load the Excel sheet's data
string ExcelFilePath = @"Assets/REF2014 Results.xlsx";
string SheetName = @"REF2014 Profiles";
DataTable REFEXCELTABLE;
using (var stream = File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
DataTableCollection Worksheets = result.Tables;
REFEXCELTABLE = Worksheets[Worksheets.IndexOf(SheetName)];
}
}
//
List <REFRow> REFRows = new List <REFRow>();
for (int i = 8; i < REFEXCELTABLE.Rows.Count; i++)
{
REFRow refRow = new REFRow();
refRow.InstitutionCode_UKPRN = (string)REFEXCELTABLE.Rows[i].ItemArray[0];
refRow.InstitutionName = (string)REFEXCELTABLE.Rows[i].ItemArray[1];
refRow.MainPanel = (string)REFEXCELTABLE.Rows[i].ItemArray[3];
refRow.UnitOfAssessmentNumber = (double)REFEXCELTABLE.Rows[i].ItemArray[4];
refRow.UnitOfAssessmentName = (string)REFEXCELTABLE.Rows[i].ItemArray[5];
refRow.MultipleSubmissionLetter = (string)REFEXCELTABLE.Rows[i].ItemArray[6];
refRow.MultipleSubmissionName = (string)REFEXCELTABLE.Rows[i].ItemArray[7];
refRow.Profile = (string)REFEXCELTABLE.Rows[i].ItemArray[9];
refRow.FTECategoryAStaffSubmitted = (double)REFEXCELTABLE.Rows[i].ItemArray[10];
if (REFEXCELTABLE.Rows[i].ItemArray[11].GetType() != typeof(string))
{
refRow.percent4star = (double)REFEXCELTABLE.Rows[i].ItemArray[11];
}
if (REFEXCELTABLE.Rows[i].ItemArray[12].GetType() != typeof(string))
{
refRow.percent3star = (double)REFEXCELTABLE.Rows[i].ItemArray[12];
}
if (REFEXCELTABLE.Rows[i].ItemArray[13].GetType() != typeof(string))
{
refRow.percent2star = (double)REFEXCELTABLE.Rows[i].ItemArray[13];
}
if (REFEXCELTABLE.Rows[i].ItemArray[14].GetType() != typeof(string))
{
refRow.percent1star = (double)REFEXCELTABLE.Rows[i].ItemArray[14];
}
if (REFEXCELTABLE.Rows[i].ItemArray[15].GetType() != typeof(string))
{
refRow.unclassified = (double)REFEXCELTABLE.Rows[i].ItemArray[15];
}
REFRows.Add(refRow);
}
// Now calculate the strength measure
// This is weighted as 4x4*+1x3*+0x2*+0x1*+0xNC
// How QR is assigned is here -- https://re.ukri.org/research/how-we-fund-research/
// Document showing this is at https://www.hefcw.ac.uk/documents/policy_areas/research/Explanation%20of%20QR%20Funding%20Method.pdf
// Weightings by profile are "65 per cent for outputs, 20 per cent for impact and 15 per cent for environment"
// But we care about excellence so we're just going to look at outputs
List <StrengthByRegion> StrengthByRegions = new List <StrengthByRegion>();
foreach (REFRow refrow in REFRows.Where(x => x.Profile == "Outputs"))
{
if (null != UniversityRegions.Where(x => x.University == refrow.InstitutionName).FirstOrDefault())
{
// I'm not taking London weighting into accounts, we're supposed to be funding excellence not regional distribution. But weights could be added here
string UniversityRegion = UniversityRegions.Where(x => x.University == refrow.InstitutionName).FirstOrDefault().Region;
double ScoreAltA = ((refrow.percent4star * 3 + refrow.percent3star * 2) * refrow.FTECategoryAStaffSubmitted.Value) * PanelWeightings[refrow.MainPanel];
double ExistingScore = ((refrow.percent4star * 4 + refrow.percent3star * 1) * refrow.FTECategoryAStaffSubmitted.Value) * PanelWeightings[refrow.MainPanel];
double ScoreAltB = ((refrow.percent4star * 5 + refrow.percent3star * 0) * refrow.FTECategoryAStaffSubmitted.Value) * PanelWeightings[refrow.MainPanel];
if (StrengthByRegions.Where(x => x.Region == UniversityRegion).FirstOrDefault() == null)
{
StrengthByRegion strengthByRegion = new StrengthByRegion()
{
Region = UniversityRegion
};
StrengthByRegions.Add(strengthByRegion);
}
StrengthByRegions.Where(x => x.Region == UniversityRegion).FirstOrDefault().ExistingStrength += ExistingScore;
StrengthByRegions.Where(x => x.Region == UniversityRegion).FirstOrDefault().StrenghtAltA += ScoreAltA;
StrengthByRegions.Where(x => x.Region == UniversityRegion).FirstOrDefault().StrenghtAltB += ScoreAltB;
if (refrow.MainPanel == "A")
{
StrengthByRegions.Where(x => x.Region == UniversityRegion).FirstOrDefault().PanelAOnly += ExistingScore;
}
}
else
{
Console.WriteLine($"No region was found for {refrow.InstitutionName} in UniversityRegionsLink.csv");
}
}
using (TextWriter TextWriter = File.CreateText(@"REFOutputStrengthByRegion.csv"))
{
CsvWriter CSVwriter = new CsvWriter(TextWriter);
CSVwriter.WriteRecords(StrengthByRegions);
}
}
