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