Beispiel #1
0
 private static bool HasUpdated(DelayeredResult delayeredResult, HashSet <int> previousCodes)
 {
     foreach (int hash in delayeredResult.Hashes)
     {
         if (!previousCodes.Contains(hash))
         {
             return(true);
         }
     }
     return(false);
 }
Beispiel #2
0
        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;
            }
        }