public dynamic UpsertResourcesAdditionalReadings(dynamic resourceObject)
{
AdditionalReading additionalReadings = new AdditionalReading();
List <TopicTag> topicTags = new List <TopicTag>();
List <Location> locations = new List <Location>();
dynamic references = GetReferences(resourceObject);
topicTags = references[0];
locations = references[1];
additionalReadings = new AdditionalReading()
{
ResourceId = resourceObject.id == "" ? Guid.NewGuid() : resourceObject.id,
Name = resourceObject.name,
ResourceCategory = resourceObject.resourceCategory,
Description = resourceObject.description,
ResourceType = resourceObject.resourceType,
Url = resourceObject.url,
TopicTags = topicTags,
OrganizationalUnit = resourceObject.organizationalUnit,
Location = locations,
CreatedBy = resourceObject.createdBy,
ModifiedBy = resourceObject.modifiedBy
};
additionalReadings.Validate();
return(additionalReadings);
}
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);
}
public async Task <IEnumerable <object> > UpsertResourceDocumentAsync(dynamic resource)
{
List <dynamic> results = new List <dynamic>();
List <dynamic> resources = new List <dynamic>();
var resourceObjects = JsonUtilities.DeserializeDynamicObject <List <dynamic> >(resource);
Form forms = new Form();
ActionPlan actionPlans = new ActionPlan();
Article articles = new Article();
Video videos = new Video();
Organization organizations = new Organization();
AdditionalReading additionalReadings = new AdditionalReading();
RelatedLink relatedLinks = new RelatedLink();
foreach (var resourceObject in resourceObjects)
{
string id = resourceObject.id;
string resourceType = resourceObject.resourceType;
if (resourceObject.resourceType == "Forms")
{
forms = UpsertResourcesForms(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(forms);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Action Plans")
{
actionPlans = UpsertResourcesActionPlans(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(actionPlans);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Articles")
{
articles = UpsertResourcesArticles(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(articles);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Videos")
{
videos = UpsertResourcesVideos(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(videos);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Organizations")
{
organizations = UpsertResourcesOrganizations(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(organizations);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Additional Readings")
{
additionalReadings = UpsertResourcesAdditionalReadings(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(additionalReadings);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
else if (resourceObject.resourceType == "Related Links")
{
relatedLinks = UpsertResourcesRelatedLinks(resourceObject);
var resourceDocument = JsonUtilities.DeserializeDynamicObject <object>(relatedLinks);
List <string> propertyNames = new List <string>()
{
Constants.Id, Constants.ResourceType
};
List <string> values = new List <string>()
{
id, resourceType
};
var resourceDBData = await dbClient.FindItemsWhereAsync(dbSettings.ResourcesCollectionId, propertyNames, values);
if (resourceDBData.Count == 0)
{
var result = await dbService.CreateItemAsync(resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
else
{
var result = await dbService.UpdateItemAsync(id, resourceDocument, dbSettings.ResourcesCollectionId);
resources.Add(result);
}
}
}
return(resources);
}
