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