public override string GetShortDescription(string VariableName)
{
//This is a merger between the base table type and the specific column referenced by the unique variable name.
//The table is extracted from the first part of the variable, with KS and QS 7 digits and CT 6 digits.
//The text reads as:
//[VariableText] from the 2011 Census table [TableName] [TableDescription]
//NOTE: this is limited to 256 chars, but isn't a problem with the way the description is built.
const string Text = "{0} ({1}) from the 2011 Census table {2} ({3})";
DataRow VRow = VariableMetaData.Rows.Find(VariableName);
string VariableText = VRow["ColumnVariableDescription"] as string;
string TableName = VariableName.Substring(0, 7);
if (TableName.StartsWith("C"))
{
TableName = TableName.Substring(0, 6);
}
//no primary key on catalogue, so have to do it the hard way
//var TRow = from DataRow myRow in Catalogue.Rows
// where (string)myRow["TableName"] == TableName
// select myRow;
//or
DataRow TRow = Catalogue.AsEnumerable().Where(t => t.Field <string>("TableName") == TableName).FirstOrDefault();
string TableDescription = TRow["Description"] as string;
return(string.Format(Text, VariableText, VariableName, TableDescription, TableName));
}
public override string GetLongDescription(string VariableName)
{
//This is a merger between the base table type and the specific column referenced by the unique variable name.
//The table is extracted from the first part of the variable, with KS and QS 7 digits and CT 6 digits.
//The data we have is TableName (KS101EW), TableDescripton text (Usual resident pop), Variable (KS101EW001), VariableDescription (Males),
//OA Url (link to data), ColumnVariableMeasurementUnit (count), ColumnVariableStatisticalUnit (people)
//URL to NOMIS table description: https://www.nomisweb.co.uk/census/2011/ks101ew and pdf here: https://www.nomisweb.co.uk/census/2011/ks101ew.pdf
//Bulk download is here: https://www.nomisweb.co.uk/census/2011/bulk/r2_2
//NOTE: the long description is an unlimited db varchar, so length is not a problem.
const string Text =
"<h1>{0} ({1})</h1>"
+ "<ul>"
+ "<li>Table ID: {2}</li>"
+ "<li>Variable ID: {3}</li>"
+ "<li>Source: Census 2011</li>"
+ "<li>Units: {4}</li>"
+ "<li>Keywords: {5}</li>"
+ "<li>Coverage: England and Wales</li>"
+ "<li>Geography: Middle layer super output area (MSOA)</li>"
+ "<li>NOMIS Website: <a href=\"{6}\">{7}</a></li>"
+ "</ul>"
+ "<p><a href=\"{8}\">Download full description (PDF)</a></p>"
+ "<p>Census 2011 data from NOMIS using the field \"{9}\" from the table \"{10}\" ({11})</p>";
string NOMISWebsite = "https://www.nomisweb.co.uk/census/2011/bulk/r2_2";
DataRow VRow = VariableMetaData.Rows.Find(VariableName);
string VariableText = VRow["ColumnVariableDescription"] as string;
string VariableUnits = VRow["ColumnVariableStatisticalUnit"] as string;
string TableName = VariableName.Substring(0, 7);
if (TableName.StartsWith("C"))
{
TableName = TableName.Substring(0, 6);
}
string FullDescriptionURL = "https://www.nomisweb.co.uk/census/2011/" + TableName.ToLower() + ".pdf";
DataRow TRow = Catalogue.AsEnumerable().Where(t => t.Field <string>("TableName") == TableName).FirstOrDefault();
string TableDescription = TRow["Description"] as string;
string Keywords = VariableText + " " + TableDescription;
return(string.Format(Text, VariableText, VariableName, TableName, VariableName, VariableUnits, Keywords, NOMISWebsite, NOMISWebsite, FullDescriptionURL, VariableText, TableDescription, TableName));
}
/// <summary>
/// Return the keywords for this data
/// </summary>
/// <returns></returns>
public override string GetKeywords(string VariableName)
{
DataRow VRow = VariableMetaData.Rows.Find(VariableName);
string VariableText = VRow["ColumnVariableDescription"] as string;
string TableName = VariableName.Substring(0, 7);
if (TableName.StartsWith("C"))
{
TableName = TableName.Substring(0, 6);
}
DataRow TRow = Catalogue.AsEnumerable().Where(t => t.Field <string>("TableName") == TableName).FirstOrDefault();
string TableDescription = TRow["Description"] as string;
string Keywords = "census 2011 NOMIS r2.2 " + VariableText + " " + TableDescription + " " + VariableName;
//remove duplicate words and process out colons
string[] split = Keywords.Split(new char[] { ' ' });
HashSet <string> words = new HashSet <string>();
foreach (string s in split)
{
words.Add(s);
}
Keywords = "";
foreach (string s in words)
{
if (s.Length < 4)
{
continue;
}
string s2 = s.Replace(':', ' ');
Keywords += s2.ToLower() + " ";
}
return(Keywords);
}
public override string GetTitle(string VariableName)
{
//NOTE: title needs to be no more than 64 chars
string TableName = VariableName.Substring(0, 7);
if (TableName.StartsWith("C"))
{
TableName = TableName.Substring(0, 6);
}
DataRow TRow = Catalogue.AsEnumerable().Where(t => t.Field <string>("TableName") == TableName).FirstOrDefault();
string TableDescription = TRow["Description"] as string;
DataRow VRow = VariableMetaData.Rows.Find(VariableName);
string VariableText = VRow["ColumnVariableDescription"] as string;
//string Title = TableDescription + " : " + VariableText; //results in over 1100 which are over 64 chars
//string Title = VariableText; //results in 327 which are over 64 chars
//we're going to have to resort to some heuristics
string Title = VariableText;
if (Title.Length > 64)
{
//heuristic 1, look for colon separated chunks and take the last bit of text before the final colon e.g. Medical and care establishment: Other: Children's home (including secure units)
int PosColon = Title.LastIndexOf(':');
if (PosColon > 0)
{
Title = Title.Substring(PosColon + 2);
}
//still results in 197 which are over, so heuristic 2, do some semi colon splitting e.g. Managers, directors and senior officials; Corporate managers and directors; Health and Social Services Managers and Directors
if (Title.Length > 64)
{
int PosSemi = Title.LastIndexOf(';');
if (PosSemi > 0)
{
Title = Title.Substring(PosSemi + 2);
}
//still got 71 which are too long at this point, so going to have to use the final fallback of truncating
//heuristic 3, drop words off the end until it's short enough
if (Title.Length > 64)
{
while (Title.Length > 60)
{
int LastPos = Title.LastIndexOf(' ');
Title = Title.Substring(0, LastPos);
Title = Title.Trim();
}
Title = Title + "...";
}
}
}
Title = Title.Trim();
if (Title.Length > 64)
{
System.Diagnostics.Debug.WriteLine("Error: Title>64 chars: " + Title);
}
return(Title);
}
