public LocationHierarchyAnalysis GetIndexedHierarchyAnalysis(string countryCode, string pName)
{
var analysis = new LocationHierarchyAnalysis();
using (var conn = new SqlConnection(_extDataConnectionString.ToString()))
{
using (var command = conn.CreateCommand())
{
conn.Open();
command.CommandTimeout = 4000;
command.CommandText = string.Format("SELECT [ID],[POPULATION],[PNAME],[COUNTRYCODE],[MAPPEDTOID] FROM [ExternalData].[GeoNames].[LocationHierarchyAnalysisBk] WHERE COUNTRYCODE = '{0}' and PNAME = N'{1}' and MAPPEDTOID is not null", countryCode, pName);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["ID"] != DBNull.Value && reader["POPULATION"] != DBNull.Value && reader["PNAME"] != DBNull.Value && reader["MAPPEDTOID"] != DBNull.Value)
{
analysis = new LocationHierarchyAnalysis
{
Id = (long)reader["ID"],
Population = (long)reader["POPULATION"],
PName = reader["PNAME"].ToString(),
CountryCode = reader["COUNTRYCODE"].ToString(),
MappedToId = (long)reader["MAPPEDTOID"]
}
}
;
}
reader.Close();
}
}
}
return(analysis);
}
public void Resolve()
{
LocationHierarchyAnalysis previousProcessedLocation = null;
LocationHierarchyAnalysis indexedLocation = null;
var pName = string.Empty;
var cnt = 0;
var uCnt = 0;
Console.WriteLine("STARTING...");
var countries = _adoClientService.GetCountryCodes();
foreach (var country in countries)
{
Console.WriteLine(string.Format("Country being processed: {0}.", country));
var notMappedDuplicateLocations = _adoClientService.GetDuplicatesWithoutMappedToId(country);
Console.WriteLine(string.Format("Unmapped locations: {0}.", notMappedDuplicateLocations.Count));
foreach (var location in notMappedDuplicateLocations)
{
cnt++;
if (previousProcessedLocation == null || !previousProcessedLocation.PName.Equals(location.PName))
{
if (location.PName.Contains("'"))
{
pName = location.PName.Replace("'", "''");
}
else
{
pName = location.PName;
}
indexedLocation = _adoClientService.GetIndexedHierarchyAnalysis(location.CountryCode, pName);
if (indexedLocation != null)
{
uCnt++;
_adoClientService.UpdateHierarchyAnalysis(location.Id, indexedLocation.Id);
previousProcessedLocation = indexedLocation;
}
else
{
Console.WriteLine("-------------- > NOT INDEXED: {0}, {1}", location.Id, location.PName);
}
}
else
{
uCnt++;
_adoClientService.UpdateHierarchyAnalysis(location.Id, previousProcessedLocation.Id);
}
}
}
Console.WriteLine("{0} locations processed. {1} updated.", cnt, uCnt);
Console.WriteLine("FINISHED...");
}
