private static bool HasUpdated(DelayeredResult delayeredResult, HashSet <int> previousCodes)
{
foreach (int hash in delayeredResult.Hashes)
{
if (!previousCodes.Contains(hash))
{
return(true);
}
}
return(false);
}
private static void ProcessEndPoint(BaseURLElement baseUrl, dynamic resources, List <string> resourceNames, HashDatabase hashDatabase)
{
try
{
Console.WriteLine("Starting to process: " + baseUrl.URL);
while (IsServerKeyUnique(baseUrl.ServerKey))
{
Console.WriteLine("Pausing: " + baseUrl.URL);
Thread.Sleep(30000);
}
lock (locker)
{
CurrentServerKeys.Add(baseUrl.ServerKey);
}
Dictionary <string, Dictionary <string, string> > keyReWrite = new Dictionary <string, Dictionary <string, string> >();
DelayeredResult delayeredResult = Delayering.DelayerPaginatedData(baseUrl.URL, new APIValidatorSettings()
{
RequestRate = baseUrl.RequestRate
}).GetAwaiter().GetResult();
if (!HasUpdated(delayeredResult, hashDatabase.Get(baseUrl.URL)))
{
Console.WriteLine("Data not changed skipping: " + baseUrl.URL);
return;
}
List <Row> rows = new List <Row>();
foreach (KeyValuePair <string, Dictionary <string, dynamic> > kvp in delayeredResult.Collection)
{
dynamic resource = GetResource(resources, kvp.Key);
string resourceName = Resources.FindResourceName(kvp.Key, resourceNames);
foreach (KeyValuePair <string, dynamic> vals in kvp.Value)
{
if (resource == null)
{
continue;
}
Row row = new Row(resourceName);
foreach (KeyValuePair <string, dynamic> fieldKvp in vals.Value)
{
string columnName = fieldKvp.Key.ToLower();
FieldStatus fieldStatus = ResourceFieldStatus(resource, columnName);
if (fieldKvp.Value == null || !fieldStatus.IsVisible)
{
continue;
}
row.Fields.Add(COLUMN_ESCAPE + columnName + COLUMN_ESCAPE);
if (fieldKvp.Value is String)
{
if (string.IsNullOrEmpty(fieldKvp.Value) && fieldStatus.IsNullByDefault)
{
row.Values.Add(NULL);
continue;
}
row.Values.Add(QUOTATION + fieldKvp.Value + QUOTATION);
continue;
}
if (fieldKvp.Value.Type == Newtonsoft.Json.Linq.JTokenType.String)
{
if (string.IsNullOrEmpty(fieldKvp.Value.Value) && fieldStatus.IsNullByDefault)
{
row.Values.Add(NULL);
continue;
}
row.Values.Add(QUOTATION + MySqlHelper.EscapeString(Convert.ToString(fieldKvp.Value.Value)) + QUOTATION);
continue;
}
if (fieldKvp.Value.Type == Newtonsoft.Json.Linq.JTokenType.Date)
{
row.Values.Add(QUOTATION + MySqlHelper.EscapeString(Convert.ToString(fieldKvp.Value.Value)) + QUOTATION);
continue;
}
row.Values.Add(QUOTATION + Convert.ToString(fieldKvp.Value) + QUOTATION);
}
if (!row.Fields.Contains(ID_COLUMN))
{
row.Fields.Add(ID_COLUMN);
row.Values.Add(QUOTATION + Guid.NewGuid().ToString() + QUOTATION);
}
else
{
int index = row.Fields.IndexOf(ID_COLUMN);
if (index > -1 && resourceName != "service" && resourceName != "organization" && resourceName != "location" && resourceName != "taxonomy")
{
if (!keyReWrite.ContainsKey(resourceName))
{
keyReWrite.Add(resourceName, new Dictionary <string, string>());
}
string originalKey = row.Values[index].Replace(QUOTATION, string.Empty);
if (!keyReWrite[resourceName].ContainsKey(originalKey))
{
keyReWrite[resourceName].Add(originalKey, Guid.NewGuid().ToString());
}
row.Values[index] = QUOTATION + keyReWrite[resourceName][originalKey] + QUOTATION;
}
}
Console.WriteLine("Reading: " + resourceName);
rows.Add(row);
}
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
conn.Open();
try
{
ClearDatabase(conn, baseUrl);
List <Row> retryRows = new List <Row>();
int retries = 0;
do
{
retryRows = new List <Row>();
foreach (Row row in rows)
{
try
{
Console.WriteLine("Executing 1 row for " + baseUrl.URL);
RunSQL(row.ToSQL(keyReWrite, baseUrl.ID), conn);
}
catch (MySqlException e)
{
if (e.Number != 1062)
{
retryRows.Add(row);
}
}
catch (Exception e)
{
if (!e.Message.StartsWith("Cannot add or update a child row: a foreign key constraint fails"))
{
retryRows.Add(row);
}
}
}
retries++;
if (retryRows.Count > 0)
{
Console.WriteLine("Retrying: " + retryRows.Count);
}
}while (retryRows.Count > 0 && retries <= 5);
}
finally
{
RunSQL("INSERT IGNORE INTO link_taxonomy SELECT id, 'service_type', service_id, taxonomy_id, api_id FROM service_taxonomy;", conn);
RunSQL("UPDATE location INNER JOIN physical_address ON location.id = physical_address.location_id AND postal_code IS NOT NULL AND postal_code <> '' AND (location.latitude IS NULL OR location.longitude IS NULL) INNER JOIN esd_postcode ON REPLACE(`postal_code`, ' ', '') = esd_postcode.code SET location.latitude = esd_postcode.latitude, location.longitude = esd_postcode.longitude; ", conn);
}
hashDatabase.Hashes[baseUrl.URL] = delayeredResult.Hashes;
}
hashDatabase.Save();
}
finally
{
CurrentServerKeys.Remove(baseUrl.ServerKey);
}
}
public async static System.Threading.Tasks.Task <bool> WriteToSpreadsheetAsync(string spreadsheetId, IConfigurableHttpClientInitializer credential, string apiBaseUrl, string configPath)
{
SheetsService service = CreateService(credential);
try
{
SheetConfig config = JsonConvert.DeserializeObject <SheetConfig>(File.ReadAllText(configPath));
//move text to config
await AddUpdateMessage(spreadsheetId, "Please wait while your export is generated, once completed this sheet will be deleted.", service).ConfigureAwait(false);
DelayeredResult result = await Delayering.DelayerPaginatedData(apiBaseUrl).ConfigureAwait(false);
if (result.Collection.Count == 0)
{
await AddUpdateMessage(spreadsheetId, "ERROR: service directory data not found at the given URL", service);
return(false);
}
Dictionary <string, string> columnToSheetIndex = new Dictionary <string, string>();
List <ForeignKeyAssociaion> foreignKeys = new List <ForeignKeyAssociaion>();
ResourceReader resourceReader = new ResourceReader();
dynamic resources = await resourceReader.GetResources().ConfigureAwait(false);
foreach (dynamic resource in resources)
{
if (!Resources.ShowItem(resource))
{
continue;
}
string sheetName = resource.name;
if (!result.Collection.ContainsKey(sheetName))
{
continue;
}
Console.WriteLine("Create Sheet: " + sheetName);
await CreateSheetAsync(service, spreadsheetId, sheetName, config).ConfigureAwait(false);
int sheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, sheetName).ConfigureAwait(false);
List <ForeignKeyAssociaion> localForeignKeys = new List <ForeignKeyAssociaion>();
if (resource.schema.foreignKeys != null)
{
foreach (dynamic fk in resource.schema.foreignKeys)
{
localForeignKeys.Add(new ForeignKeyAssociaion(sheetId, sheetName, fk.fields.Value, fk.reference.resource.Value, fk.reference.fields.Value));
}
}
int index = 0;
int columnNo = -1;
foreach (dynamic field in resource.schema.fields)
{
if (!Resources.ShowItem(field))
{
continue;
}
string columnName = field.name.Value;
if (columnName == "extent")
{
continue;
}
if (config.IsColumnHidden(sheetName, columnName))
{
await HideColumnAsync(service, spreadsheetId, sheetName, index, config).ConfigureAwait(false);
}
Console.WriteLine("Create Column Name: " + columnName);
columnNo++;
await InsertColumnLineAsync(service, spreadsheetId, sheetName + "!" + GetColumnName(index) + "1", columnName).ConfigureAwait(false);;
string comment = config.GetComment(sheetName, columnName);
if (!string.IsNullOrEmpty(comment))
{
await InsertColumnNoteAsync(service, spreadsheetId, await GetSheetIdFromSheetNameAsync(service, spreadsheetId, sheetName).ConfigureAwait(false), columnNo, comment);
}
List <object> columnValues = new List <object>();
foreach (dynamic obj in result.Collection[sheetName].Values)
{
if (!((IDictionary <String, dynamic>)obj).ContainsKey(columnName))
{
columnValues.Add(string.Empty);
}
else
{
columnValues.Add(((IDictionary <String, dynamic>)obj)[columnName]);
}
}
Console.WriteLine("Load Column Values: " + columnName);
List <List <object> > columnBatches = SplitList(columnValues, 1000);
int rowNumber = 2;
foreach (List <object> columnBatch in columnBatches)
{
string colRange = sheetName + "!" + GetColumnName(index) + rowNumber;
foreach (ForeignKeyAssociaion localForeignKey in localForeignKeys)
{
if (localForeignKey.Field == columnName)
{
localForeignKey.StartColumnIndex = index;
localForeignKey.Found = true;
}
}
SaveColumnRange(columnToSheetIndex, sheetName, columnName, colRange + ":" + GetColumnName(index) + columnValues.Count);
await InsertColumnLineAsync(service, spreadsheetId, colRange, columnBatch.ToArray()).ConfigureAwait(false);
rowNumber += columnBatch.Count;
}
index++;
}
foreignKeys.AddRange(localForeignKeys);
}
await AddForiegnKeysAsync(service, foreignKeys, columnToSheetIndex, spreadsheetId).ConfigureAwait(false);
await AddExtraColumnsAsync(service, config, columnToSheetIndex, spreadsheetId).ConfigureAwait(false);
await DeleteOriginalSheet(service, spreadsheetId).ConfigureAwait(false);
return(true);
}
catch (Exception e)
{
await AddUpdateMessage(spreadsheetId, "ERROR: service directory data not found at the given URL", service);
throw e;
}
}