private List <PostcodeData> GetPostcodeLocationDataFromWebAPI()
{
SQLiteDataReader dataReader = m_database.GetDataFromTable("postal", "contactInfo");
List <string> groupedPostCodes = PostCodeIOAPI.GetPostCodesInGroupsOfOneHundred(dataReader);
string[] filters = new string[] { "postcode", "eastings", "northings" };
List <PostcodeData> postcodeData = PostCodeIOAPI.GetDataFromWebAPI(groupedPostCodes, filters);
return(postcodeData);
}
//Choice of Interesting Data 2)
//Interested to see how companies broke down via Electorial district as data like this could be then used to inform political decisions
public void GroupCompaniesByTheirCountyElectoralDistrict()
{
SQLiteDataReader dataReader = m_database.GetDataFromTable("postal", "contactInfo");
List <string> groupedPostCodes = PostCodeIOAPI.GetPostCodesInGroupsOfOneHundred(dataReader);
string[] filters = new string[] { "postcode", "parliamentary_constituency" };
List <PostcodeData> locations = PostCodeIOAPI.GetDataFromWebAPI(groupedPostCodes, filters);
locations = locations.OrderBy(location => location.parliamentary_constituency).ToList();
List <int> IDs = new List <int>();
SQLiteCommand sqlCommand = new SQLiteCommand();
for (int i = 0; i < locations.Count; i++)
{
SQLiteDataReader reader = m_database.GetDataFromTable("ID", "contactInfo", "postal = @postcode", "postcode", locations[i].postcode, false);
while (reader.Read())
{
long ID = (long)reader["ID"];
IDs.Add((int)ID);
}
}
sqlCommand.Cancel();
List <string> companies = new List <string>();
for (int i = 0; i < IDs.Count; i++)
{
SQLiteDataReader reader = m_database.GetDataFromTable("company", "company", "ID = @ID", "ID", IDs[i].ToString(), true);
while (reader.Read())
{
string companyName = RemoveQuotesFromString(reader["company"].ToString());
companies.Add(companyName);
}
}
string[] columnNames = new string[] { "Company", "Constituency" };
m_reportHandler.CreateNewWorkSheet("Constituency Companies", columnNames);
for (int i = 0; i < companies.Count; i++)
{
m_reportHandler.WriteToCell(i + 1, 0, companies[i], "Constituency Companies");
m_reportHandler.WriteToCell(i + 1, 1, locations[i].parliamentary_constituency, "Constituency Companies");
}
}
