/// <summary> /// Reads the given spreadsheet and updates database changes asynchronous. /// </summary> /// <param name="accessToken">Access token..</param> /// <param name="spreadSheetId">The spread sheet identifier.</param> /// <param name="sheetName">Name of the sheet to read.</param> /// <param name="sheetRange">The sheet range to read.</param> /// <param name="googleApiKey">The Google Api Key.</param> public async Task UpdateSynonymChanges(string accessToken, string spreadSheetId, string sheetName, string sheetRange, string googleApiKey) { LoggingHandler.LogInfo("Get data from source."); IDictionary <string, string[]> synonymGroups = await ReadSpreadsheetAsync(spreadSheetId, sheetName, sheetRange, googleApiKey).ConfigureAwait(false); if (synonymGroups == null || synonymGroups.Count == 0) { LoggingHandler.LogInfo("There is no synonym data from the google spreadsheet."); return; } //compare groups with database results and delete anything that has been removed. SynonymGroupSearchRequest requestParams = new SynonymGroupSearchRequest() { Direction = "asc", Search = string.Empty, Sort = "name" }; LoggingHandler.LogInfo("Received synonyms from source. Getting current data to update."); SynonymGroupResponseList synonymGroupsResponseList = ExecuteGet(requestParams); try { LoggingHandler.LogInfo("Deleting any items removed."); await LoopDatabaseAndDeleteAnythingRemovedFromSpreadsheet(accessToken, synonymGroupsResponseList, synonymGroups).ConfigureAwait(false); LoggingHandler.LogInfo("Updating any changed items."); await LoopSpreadsheetAndUpdateDatabaseWithChanges(accessToken, synonymGroupsResponseList, synonymGroups).ConfigureAwait(false); } catch (Exception e) { LoggingHandler.LogError("Error updating data."); LoggingHandler.LogError(e.Message); Debugger.Break(); } LoggingHandler.LogInfo("Synonyms sync process complete."); }
private async Task LoopDatabaseAndDeleteAnythingRemovedFromSpreadsheet(string accessToken, SynonymGroupResponseList synonymGroupsResponseList, IDictionary <string, string[]> synonymGroups) { if (synonymGroupsResponseList != null && synonymGroupsResponseList.SynonymGroups != null) { foreach (var databaseSg in synonymGroupsResponseList.SynonymGroups) { string databaseGroupStr = databaseSg.Name.ToLower(); var dbGroupInSpreadsheet = synonymGroups .Where(s => s.Key.ToLower().Trim() == databaseGroupStr); //Get all words from database SynonymWordSearchRequest requestWordParams = new SynonymWordSearchRequest() { Direction = "asc", GroupId = databaseSg.Id, Sort = "word" }; //Get all words from database SynonymWordResponseList synonymWordResponseList = ExecuteGetWord(requestWordParams); foreach (var databaseWd in synonymWordResponseList.SynonymWords) { //If db word is not in spreadsheet then delete if (!dbGroupInSpreadsheet.Any(s => s.Value.Contains(databaseWd.Word.Trim()))) { ExecuteDeleteWord(accessToken, databaseWd.Id); } } } } }
private async Task LoopSpreadsheetAndUpdateDatabaseWithChanges(string accessToken, SynonymGroupResponseList synonymGroupsResponseList, IDictionary <string, string[]> synonymGroups) { //Loop spreadsheet list and update changes if required DateTime today = DateTime.Now; if (synonymGroupsResponseList == null) { synonymGroupsResponseList = new SynonymGroupResponseList() { SynonymGroups = new List <SynonymGroupResponse>() } } ; foreach (var sg in synonymGroups) { if (sg.Value.Length > 0) { int groupId = 0; var synonymGroup = synonymGroupsResponseList.SynonymGroups.Find(s => s.Name.ToLower() == sg.Key.ToLower()); if (synonymGroup == null) //Add { SynonymGroupRequest synonymGroupRequest = new SynonymGroupRequest() { Name = sg.Key, CreatedAt = today }; var response = ExecuteCreate(accessToken, synonymGroupRequest); groupId = response.Id; } else { groupId = synonymGroup.Id; } SynonymWordSearchRequest requestWordParams = new SynonymWordSearchRequest() { Direction = "asc", GroupId = groupId, Sort = "word" }; SynonymWordResponseList synonymWordResponseList = ExecuteGetWord(requestWordParams); if (synonymWordResponseList == null) { synonymWordResponseList = new SynonymWordResponseList() { SynonymWords = new List <SynonymWordResponse>() } } ; List <SynonymWord> words = new List <SynonymWord>(); foreach (string word in sg.Value) { if (!string.IsNullOrEmpty(word)) { var synonymWord = synonymWordResponseList.SynonymWords.Find(w => w.Word.ToLower() == word.ToLower()); if (synonymWord == null)//Add { SynonymWordRequest synonymWordRequest = new SynonymWordRequest() { Word = word, CreatedAt = today, GroupId = groupId }; var response = ExecuteCreateWord(accessToken, synonymWordRequest); synonymWordResponseList = ExecuteGetWord(requestWordParams);//Refresh list } } } } } }