public async static Task GetTopics(string accessToken, string filePath)
{
clientHttp.BaseAddress = new Uri(ConfigurationManager.AppSettings["apiUrl"]);
clientHttp.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
clientHttp.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
try
{
InsertTopics obj = new InsertTopics();
List <dynamic> topicsList = new List <dynamic>();
var topics = obj.CreateJsonFromCSV(filePath);
if (topics == null || topics.Count == 0)
{
MessageBox.Show("Please check Error log file to correct errors");
}
else
{
foreach (var topicList in topics)
{
var serializedResult = JsonConvert.SerializeObject(topicList);
JObject jsonResult = (JObject)JsonConvert.DeserializeObject(serializedResult);
topicsList.Add(jsonResult);
}
foreach (var topicList in topicsList)
{
if (topicList.parentTopicId != null)
{
foreach (var topicData in topicsList)
{
for (int iterator = 0; iterator < topicList.parentTopicId.Count; iterator++)
{
if (topicList.parentTopicId[iterator].id == topicData.name)
{
for (int locationIteratortopicList = 0; locationIteratortopicList < topicList.location.Count; locationIteratortopicList++)
{
for (int locationIteratortopicData = 0; locationIteratortopicData < topicData.location.Count; locationIteratortopicData++)
{
if (topicList.location[locationIteratortopicList].state == topicData.location[locationIteratortopicData].state)
{
topicList.parentTopicId[iterator].id = topicData.id;
break;
}
}
}
}
}
}
}
}
var serializedTopics = JsonConvert.SerializeObject(topicsList);
StringContent content = new StringContent(serializedTopics, Encoding.UTF8, "application/json");
var response = await clientHttp.PostAsync("api/topics-resources/topics/documents/upsert", content).ConfigureAwait(false);
var json = response.Content.ReadAsStringAsync().Result;
response.EnsureSuccessStatusCode();
if (response.IsSuccessStatusCode == true)
{
string fileName = $@"Topic{DateTime.Now.Ticks}.txt";
LogHelper.DataLogging(json, fileName);
MessageBox.Show("File got processed");
}
else
{
MessageBox.Show("Please correct errors" + "\n" + response);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void UpdateFormData(IEnumerable <string> keyValue, string cellActualValue, string resourceType)
{
string val = keyValue.First();
cellActualValue = cellActualValue.Trim();
#region Common field mapping
if (val.EndsWith("Id", StringComparison.CurrentCultureIgnoreCase))
{
id = cellActualValue;
}
if (val.EndsWith("Name*", StringComparison.CurrentCultureIgnoreCase))
{
name = cellActualValue;
}
else if (val.EndsWith("Description*", StringComparison.CurrentCultureIgnoreCase))
{
description = InsertTopics.FormatData(cellActualValue);
}
else if (val.Equals("Resource_Type*", StringComparison.CurrentCultureIgnoreCase))
{
resourceType = cellActualValue;
}
else if (val.EndsWith("URL*", StringComparison.CurrentCultureIgnoreCase))
{
url = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Topic*", StringComparison.CurrentCultureIgnoreCase))
{
string topicTag = cellActualValue;
topicTagIds = GetTopicTags(topicTag);
}
else if (val.EndsWith("Organizational_Unit*", StringComparison.CurrentCultureIgnoreCase))
{
organizationalUnit = cellActualValue;
}
else if (val.EndsWith("Location_State*", StringComparison.CurrentCultureIgnoreCase))
{
state = cellActualValue;
}
else if (val.EndsWith("Location_County", StringComparison.CurrentCultureIgnoreCase))
{
county = cellActualValue;
}
else if (val.EndsWith("Location_City", StringComparison.CurrentCultureIgnoreCase))
{
city = cellActualValue;
}
else if (val.EndsWith("Location_Zip", StringComparison.CurrentCultureIgnoreCase))
{
zipcode = cellActualValue;
}
else if (val.Equals("Resource_Category", StringComparison.CurrentCultureIgnoreCase))
{
resourceCategory = cellActualValue;
}
#endregion Common field mapping
if (resourceType == Constants.FormResourceType)
{
if (val.EndsWith("Overview", StringComparison.CurrentCultureIgnoreCase))
{
overview = InsertTopics.FormatData(cellActualValue);
}
}
if (resourceType == Constants.OrganizationResourceType)
{
if (val.EndsWith("Org_Address*", StringComparison.CurrentCultureIgnoreCase))
{
address = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Phone*", StringComparison.CurrentCultureIgnoreCase))
{
telephone = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Overview", StringComparison.CurrentCultureIgnoreCase))
{
overview = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Specialties", StringComparison.CurrentCultureIgnoreCase))
{
specialties = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Eligibility_Information", StringComparison.CurrentCultureIgnoreCase))
{
eligibilityInformation = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Qualifications", StringComparison.CurrentCultureIgnoreCase))
{
qualifications = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Business_Hours", StringComparison.CurrentCultureIgnoreCase))
{
businessHours = InsertTopics.FormatData(cellActualValue);
}
}
if (resourceType == Constants.ArticleResourceType)
{
if (val.EndsWith("Overview", StringComparison.CurrentCultureIgnoreCase))
{
overview = InsertTopics.FormatData(cellActualValue);
}
}
if (resourceType == Constants.ArticleContent)
{
if (val.EndsWith("Article*", StringComparison.CurrentCultureIgnoreCase))
{
articleName = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Section_Headline", StringComparison.CurrentCultureIgnoreCase))
{
headline = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Section_Content", StringComparison.CurrentCultureIgnoreCase))
{
content = InsertTopics.FormatData(cellActualValue);
}
}
if (resourceType == Constants.VideoResourceType)
{
if (val.EndsWith("Overview", StringComparison.CurrentCultureIgnoreCase))
{
overview = InsertTopics.FormatData(cellActualValue);
}
}
if (resourceType == Constants.OrganizationReview)
{
if (val.EndsWith("Organization*", StringComparison.CurrentCultureIgnoreCase))
{
organizationName = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Reviewer_Full_Name*", StringComparison.CurrentCultureIgnoreCase))
{
reviewerFullName = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Reviewer_Title", StringComparison.CurrentCultureIgnoreCase))
{
reviewerTitle = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Review_Text*", StringComparison.CurrentCultureIgnoreCase))
{
reviewText = InsertTopics.FormatData(cellActualValue);
}
else if (val.EndsWith("Reviewer_Image_URL", StringComparison.CurrentCultureIgnoreCase))
{
reviewerImage = InsertTopics.FormatData(cellActualValue);
}
}
}
public static bool ValidateHeader(string[] header, int recordNumber, string resourceType)
{
bool correctHeader = false;
IStructuralEquatable actualHeader = header;
string[] expectedFormHeader = { "Id", "Name*", "Description*", "Resource_Type*", "URL*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip" };
string[] expectedOrganizationHeader = { "Id", "Name*", "Description*", "Resource_Type*", "URL*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip", "Org_Address*", "Phone*", "Overview", "Specialties", "Eligibility_Information", "Qualifications", "Business_Hours", "Resource_Category" };
string[] expectedArticleHeader = { "Id", "Name*", "Description*", "Resource_Type*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip", "Overview" };
string[] expectedVideoHeader = { "Id", "Name*", "Description*", "Resource_Type*", "URL*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip", "Resource_Category", "Overview" };
string[] expectedAdditionalReadingHeader = { "Id", "Name*", "Resource_Type*", "URL*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip" };
string[] expectedOrganizationReviewHeader = { "Organization*", "Reviewer_Full_Name*", "Reviewer_Title", "Review_Text*", "Reviewer_Image_URL" };
string[] expectedArticleContentsHeader = { "Article*", "Section_Headline", "Section_Content" };
string[] expectedRelatedLinkHeader = { "Id", "Name*", "Description*", "Resource_Type*", "URL*", "Topic*", "Organizational_Unit*", "Location_State*", "Location_County", "Location_City",
"Location_Zip" };
try
{
if (resourceType == Constants.FormResourceType)
{
correctHeader = HeaderValidation(header, expectedFormHeader, Constants.FormResourceType);
}
else if (resourceType == Constants.OrganizationResourceType)
{
correctHeader = HeaderValidation(header, expectedOrganizationHeader, Constants.OrganizationResourceType);
}
else if (resourceType == Constants.ArticleResourceType)
{
correctHeader = HeaderValidation(header, expectedArticleHeader, Constants.ArticleResourceType);
}
else if (resourceType == Constants.ArticleContent)
{
correctHeader = HeaderValidation(header, expectedArticleContentsHeader, Constants.ArticleContent);
}
else if (resourceType == Constants.VideoResourceType)
{
correctHeader = HeaderValidation(header, expectedVideoHeader, Constants.VideoResourceType);
}
else if (resourceType == Constants.AdditionalReadingResourceType)
{
correctHeader = HeaderValidation(header, expectedAdditionalReadingHeader, Constants.AdditionalReadingResourceType);
}
else if (resourceType == Constants.OrganizationReview)
{
correctHeader = HeaderValidation(header, expectedOrganizationReviewHeader, Constants.OrganizationReview);
}
else if (resourceType == Constants.RelatedLinkResourceType)
{
correctHeader = HeaderValidation(header, expectedRelatedLinkHeader, Constants.RelatedLinkResourceType);
}
}
catch (Exception ex)
{
InsertTopics.ErrorLogging(ex, recordNumber);
}
return(correctHeader);
}
public dynamic CreateJsonFromCSV(string filePath)
{
int recordNumber = 1;
Resource resource = new Resource();
List <dynamic> ResourcesList = new List <dynamic>();
List <dynamic> organizationsList = new List <dynamic>();
List <dynamic> articlesList = new List <dynamic>();
List <dynamic> organizationReviewsList = new List <dynamic>();
List <dynamic> Resources = new List <dynamic>();
List <string> sheetNames = new List <string>()
{
Constants.ArticleSheetName, Constants.ArticleSectionSheetName, Constants.VideoSheetName, Constants.AdditionalReadingSheetName, Constants.FormSheetName, Constants.OrganizationSheetName, Constants.OrganizationReviewSheetName, Constants.RelatedLinkSheetName
};
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Spreadsheet.Sheets sheets = workbookPart.Workbook.GetFirstChild <Spreadsheet.Sheets>();
foreach (Spreadsheet.Sheet sheet in sheets)
{
if (sheet.Name.HasValue && sheetNames.Find(a => a == sheet.Name.Value) != null)
{
Spreadsheet.Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
Spreadsheet.SheetData sheetData = worksheet.Elements <Spreadsheet.SheetData>().First();
Spreadsheet.SharedStringTable sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
Dictionary <string, string> keyValuePairs = new Dictionary <string, string>();
string cellValue;
int counter = 0;
bool isValidated = false;
ClearVariableData();
topicTagIds = new List <TopicTag>();
locations = new List <Shared.Models.Location>();
string resourceIdCell = string.Empty;
string resourceType = GetResourceType(sheet.Name.Value);
foreach (Spreadsheet.Row row in sheetData.Elements <Spreadsheet.Row>())
{
if (counter == 1)
{
var resourceIdColumn = from a in keyValuePairs where a.Key == "Id" select a.Value.First().ToString();
if (resourceIdColumn.Count() > 0)
{
resourceIdCell = resourceIdColumn.First();
}
}
foreach (Spreadsheet.Cell cell in row.Elements <Spreadsheet.Cell>())
{
cellValue = cell.InnerText;
if (string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(resourceIdCell) && cell.CellReference == string.Concat(resourceIdCell + row.RowIndex))
{
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
workbookPart.Workbook.Save();
}
}
else if (!string.IsNullOrEmpty(cellValue))
{
string cellActualValue = string.Empty;
if (cell.DataType != null && cell.DataType == Spreadsheet.CellValues.SharedString)
{
cellActualValue = sharedStringTable.ElementAt(Int32.Parse(cellValue, CultureInfo.InvariantCulture)).InnerText;
}
else
{
cellActualValue = cellValue;
}
if (counter == 0)
{
keyValuePairs.Add(cellActualValue, cell.CellReference);
}
else
{
var headerValues = from a in keyValuePairs select a.Key;
if (!isValidated)
{
if (!ValidateHeader(headerValues.ToArray <string>(), recordNumber, resourceType))
{
break;
}
else
{
isValidated = true;
}
}
IEnumerable <string> keyValue = null;
if (cell.CellReference.Value.Length == 2)
{
keyValue = from a in keyValuePairs where a.Value.Take(1).First() == cell.CellReference.Value.Take(1).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 3)
{
keyValue = from a in keyValuePairs where a.Value.Take(2).First() == cell.CellReference.Value.Take(2).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 4)
{
keyValue = from a in keyValuePairs where a.Value.Take(3).First() == cell.CellReference.Value.Take(3).First() select a.Key;
}
if (keyValue.Count() > 0)
{
UpdateFormData(keyValue, cellActualValue, resourceType);
}
}
}
}
if (counter > 0)
{
InsertTopics topic = new InsertTopics();
locations = topic.GetLocations(state, county, city, zipcode);
if (resourceType == Constants.FormResourceType)
{
Form form = new Form()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
form.Validate();
ResourcesList.Add(form);
ClearVariableData();
}
if (resourceType == Constants.OrganizationResourceType)
{
Organization organization = new Organization()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Address = address,
Telephone = telephone,
Overview = overview,
Specialties = specialties,
EligibilityInformation = eligibilityInformation,
Qualifications = qualifications,
BusinessHours = businessHours,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
organization.Validate();
organizationsList.Add(organization);
ClearVariableData();
}
if (resourceType == Constants.OrganizationReview)
{
orgNameList.Add(organizationName);
orgFullNameList.Add(reviewerFullName);
orgTitleList.Add(reviewerTitle);
orgReviewTextList.Add(reviewText);
orgReviewerImageList.Add(reviewerImage);
ClearVariableData();
}
if (resourceType == Constants.ArticleResourceType)
{
Article article = new Article()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
article.Validate();
articlesList.Add(article);
ClearVariableData();
}
if (resourceType == Constants.ArticleContent)
{
articleNameList.Add(articleName);
headlineList.Add(headline);
contentList.Add(content);
ClearVariableData();
}
if (resourceType == Constants.VideoResourceType)
{
Video video = new Video()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
video.Validate();
ResourcesList.Add(video);
ClearVariableData();
}
if (resourceType == Constants.AdditionalReadingResourceType)
{
AdditionalReading additionalReading = new AdditionalReading()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
additionalReading.Validate();
ResourcesList.Add(additionalReading);
ClearVariableData();
}
if (resourceType == Constants.RelatedLinkResourceType)
{
RelatedLink relatedLink = new RelatedLink()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
relatedLink.Validate();
ResourcesList.Add(relatedLink);
ClearVariableData();
}
}
counter++;
recordNumber++;
}
}
}
}
foreach (var resourceList in organizationsList)
{
List <OrganizationReviewer> organizationReviewer = new List <OrganizationReviewer>();
OrganizationReviewer orgReviewer = new OrganizationReviewer();
for (int iterator = 0; iterator < orgNameList.Count; iterator++)
{
var na = orgNameList[iterator];
if (resourceList.Name == orgNameList[iterator])
{
orgReviewer = new OrganizationReviewer
{
ReviewerFullName = orgFullNameList[iterator],
ReviewerTitle = orgTitleList[iterator],
ReviewText = orgReviewTextList[iterator],
ReviewerImage = orgReviewerImageList[iterator]
};
organizationReviewer.Add(orgReviewer);
}
}
var serializedResult = JsonConvert.SerializeObject(organizationReviewer);
var orgReviewData = JsonConvert.DeserializeObject(serializedResult);
resourceList.Reviewer = organizationReviewer;
ResourcesList.Add(resourceList);
}
foreach (var articleList in articlesList)
{
List <ArticleContent> articleContentList = new List <ArticleContent>();
ArticleContent articleContents = new ArticleContent();
for (int iterator = 0; iterator < articleNameList.Count; iterator++)
{
var na = articleNameList[iterator];
if (articleList.Name == articleNameList[iterator])
{
articleContents = new ArticleContent
{
Headline = headlineList[iterator],
Content = contentList[iterator],
};
articleContentList.Add(articleContents);
}
}
var serializedResult = JsonConvert.SerializeObject(articleContentList);
var articleContentData = JsonConvert.DeserializeObject(serializedResult);
articleList.Contents = articleContentList;
ResourcesList.Add(articleList);
}
}
catch (Exception ex)
{
InsertTopics.ErrorLogging(ex, recordNumber);
Resources = null;
}
Resources = ResourcesList;
return(Resources);
}
