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