public static async Task <bool> PopulateAll(string sqlConn, string schema, string cognitiveKey, string client, string secret, string date)
{
string token = await FacebookUtility.GetAccessTokenAsync(client, secret);
string[] pages = SqlUtility.GetPages(sqlConn, schema);
List <JObject> posts = new List <JObject>();
foreach (var pageToSearch in pages)
{
string page = pageToSearch.Replace(" ", "");
try
{
var pageObj = await FacebookUtility.GetPage(page, token);
// Get Facebook Posts
posts = await FacebookUtility.GetPostsAsync(page, date, token);
// Get All Data Tables
var commentsDataTable = DataTableUtility.GetCommentsDataTable();
var hashTagDataTable = DataTableUtility.GetHashTagDataTable();
var keyPhraseDataTable = DataTableUtility.GetKeyPhraseDataTable();
var postDataTable = DataTableUtility.GetPostsDataTable();
var reactionsDataTable = DataTableUtility.GetReactionsDataTable();
var sentimentDataTable = DataTableUtility.GetSentimentDataTable();
PopulatePostCommentsAndReactions(postDataTable, commentsDataTable, reactionsDataTable, posts, page, pageObj);
// Populate Sentiment
Dictionary <string, string> items = new Dictionary <string, string>();
CognitiveUtility.PopulateDictionary(items, postDataTable);
CognitiveUtility.PopulateDictionary(items, commentsDataTable);
var payloads = CognitiveUtility.GetPayloads(items);
await CognitiveUtility.GetSentimentAsync(payloads, sentimentDataTable, cognitiveKey);
await CognitiveUtility.GetKeyPhraseAsync(payloads, keyPhraseDataTable, cognitiveKey);
CognitiveUtility.GetHashTags(postDataTable, hashTagDataTable);
CognitiveUtility.GetHashTags(commentsDataTable, hashTagDataTable);
// Bulk Insert
SqlUtility.BulkInsert(sqlConn, postDataTable, schema + "." + "StagingPosts");
SqlUtility.BulkInsert(sqlConn, sentimentDataTable, schema + "." + "StagingSentiment");
SqlUtility.BulkInsert(sqlConn, commentsDataTable, schema + "." + "StagingComments");
SqlUtility.BulkInsert(sqlConn, keyPhraseDataTable, schema + "." + "StagingKeyPhrase");
SqlUtility.BulkInsert(sqlConn, reactionsDataTable, schema + "." + "StagingReactions");
SqlUtility.BulkInsert(sqlConn, hashTagDataTable, schema + "." + "StagingHashTags");
// Debugging
var errorDataTable = DataTableUtility.GetErrorDataTable();
DataRow errorRow = errorDataTable.NewRow();
errorRow["Date"] = date;
errorRow["Error"] = "";
errorRow["Posts"] = page + ":" + JToken.FromObject(posts).ToString();
errorDataTable.Rows.Add(errorRow);
SqlUtility.BulkInsert(sqlConn, errorDataTable, schema + "." + "StagingError");
}
catch (Exception e)
{
var errorDataTable = DataTableUtility.GetErrorDataTable();
DataRow errorRow = errorDataTable.NewRow();
errorRow["Date"] = date;
errorRow["Error"] = e.ToString();
errorRow["Posts"] = page + ":" + JToken.FromObject(posts).ToString();
errorDataTable.Rows.Add(errorRow);
SqlUtility.BulkInsert(sqlConn, errorDataTable, schema + "." + "StagingError");
throw;
}
}
return(true);
}
public static async Task <bool> PopulateMeasures(string pageId, string pageAccessToken, string sqlConnection, string sqlSchema, string getDataUntil)
{
string page = pageId;
string accessToken = pageAccessToken;
string sqlConn = sqlConnection;
string schema = sqlSchema;
string until = getDataUntil;
try
{
var pageContentTable = DataTableUtility.GetPageContentTable();
var pageEngagementTable = DataTableUtility.GetPageEngagementTable();
var pageImpressionsTable = DataTableUtility.GePageImpressionsTable();
var pagePostsTable = DataTableUtility.GetPagePostsTable();
var pagePostEngagement = DataTableUtility.GetPagePostEngagementTable();
var pagePostImpressions = DataTableUtility.GetPagePostImpressionsTable();
var pagePostReactionsTable = DataTableUtility.GetPagePostReactionsTable();
var pagePostStoriesAndPeopleTalkingAboutThisTable = DataTableUtility.GetPagePostStoriesAndPeopleTalkingAboutThisTable();
var pageReactionsTable = DataTableUtility.GetPageReactionsTable();
var pageUserDemographicsTable = DataTableUtility.GetPageUserDemographicsTable();
var pageVideoPosts = DataTableUtility.GetPageVideoPostsTable();
var pageVideoViews = DataTableUtility.GetPageVideoViewsTable();
var pageViewsTable = DataTableUtility.GetPageViewsTable();
var clicksTable = DataTableUtility.GetPageClicksDataTable();
var postsInfoTable = DataTableUtility.GetPostsInfoTable();
var postsToTable = DataTableUtility.GetPostsToTable();
var content = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageContent);
PopulateNestedValues(pageContentTable, content, page);
var engagement = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageEngagement);
PopulateNestedValues(pageEngagementTable, engagement, page);
var impressions = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageImpressions);
PopulateNestedValues(pageImpressionsTable, impressions, page);
var pagePosts = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PagePosts);
PopulateNestedValues(pagePostsTable, pagePosts, page);
var pagePostsEngagement = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostEngagement);
PopulateNestedValues(pagePostEngagement, pagePostsEngagement, page);
var pagePostsReactions = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostReactions);
PopulateNestedValues(pagePostReactionsTable, pagePostsReactions, page);
var pageReactions = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageReactions);
PopulateNestedValues(pageReactionsTable, pageReactions, page);
var pagePostStories = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostStoriesAndPeopleTalkingAboutThis);
PopulateNestedValues(pagePostStoriesAndPeopleTalkingAboutThisTable, pagePostStories, page);
var pageUserDemographics = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageUserDemographics);
PopulateNestedValues(pageUserDemographicsTable, pageUserDemographics, page);
var pageVideoViewsObj = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageVideoViews);
PopulateNestedValues(pageVideoViews, pageVideoViewsObj, page);
var pageViews = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageViews);
PopulateNestedValues(pageViewsTable, pageViews, page);
var clicks = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PageCtaClicks);
PopulateNestedValues(clicksTable, clicks, page);
var pagePostIds = await FacebookUtility.GetPageMetricAnalytics(page, until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostIds);
PopulatePostsInfo(postsInfoTable, pagePostIds, page);
PopulatePostsTo(postsToTable, pagePostIds, page);
List <JObject> pagePostReactions = new List <JObject>();
List <JObject> pageVideoPostsObj = new List <JObject>();
List <JObject> pagePostsImpressions = new List <JObject>();
if (pagePostIds != null)
{
foreach (var entry in pagePostIds)
{
if (entry?["data"] != null)
{
foreach (var obj in entry["data"])
{
pagePostsImpressions.AddRange(await FacebookUtility.GetPageMetricAnalytics(obj["id"].ToString(), until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostImpressions));
pageVideoPostsObj.AddRange(await FacebookUtility.GetPageMetricAnalytics(obj["id"].ToString(), until, accessToken, FacebookPageAnalyticsMetricGroups.PageVideoPosts));
pagePostReactions.AddRange(await FacebookUtility.GetPageMetricAnalytics(obj["id"].ToString(), until, accessToken, FacebookPageAnalyticsMetricGroups.PagePostReactions));
}
}
}
}
PopulateNestedValues(pagePostReactionsTable, pagePostReactions, page);
PopulateNestedValues(pageVideoPosts, pageVideoPostsObj, page);
PopulateNestedValues(pagePostImpressions, pagePostsImpressions, page);
SqlUtility.BulkInsert(sqlConn, pageContentTable, schema + "." + "STAGING_PageContent");
SqlUtility.BulkInsert(sqlConn, pageEngagementTable, schema + "." + "STAGING_PageEngagement");
SqlUtility.BulkInsert(sqlConn, pageImpressionsTable, schema + "." + "STAGING_PageImpressions");
SqlUtility.BulkInsert(sqlConn, pagePostsTable, schema + "." + "STAGING_PagePost");
SqlUtility.BulkInsert(sqlConn, pagePostEngagement, schema + "." + "STAGING_PagePostEngagement");
SqlUtility.BulkInsert(sqlConn, pagePostImpressions, schema + "." + "STAGING_PagePostImpressions");
SqlUtility.BulkInsert(sqlConn, pagePostReactionsTable, schema + "." + "STAGING_PagePostReactions");
SqlUtility.BulkInsert(sqlConn, pagePostStoriesAndPeopleTalkingAboutThisTable, schema + "." + "STAGING_PagePostStoriesAndPeopleTalkingAboutThis");
SqlUtility.BulkInsert(sqlConn, pageReactionsTable, schema + "." + "STAGING_PageReactions");
SqlUtility.BulkInsert(sqlConn, pageUserDemographicsTable, schema + "." + "STAGING_PageUserDemographics");
SqlUtility.BulkInsert(sqlConn, pageVideoPosts, schema + "." + "STAGING_PageVideoPosts");
SqlUtility.BulkInsert(sqlConn, pageVideoViews, schema + "." + "STAGING_PageVideoViews");
SqlUtility.BulkInsert(sqlConn, pageViewsTable, schema + "." + "STAGING_PageViews");
SqlUtility.BulkInsert(sqlConn, clicksTable, schema + "." + "STAGING_Clicks");
SqlUtility.BulkInsert(sqlConn, postsInfoTable, schema + "." + "STAGING_PagePostsInfo");
SqlUtility.BulkInsert(sqlConn, postsToTable, schema + "." + "STAGING_PagePostsTo");
}
catch (Exception e)
{
var errorDataTable = DataTableUtility.GetErrorDataTable();
DataRow errorRow = errorDataTable.NewRow();
errorRow["Date"] = getDataUntil == string.Empty ? DateTime.UtcNow.ToString("o") : getDataUntil;
errorRow["Error"] = e.ToString();
errorDataTable.Rows.Add(errorRow);
SqlUtility.BulkInsert(sqlConn, errorDataTable, schema + "." + "Error");
throw;
}
return(true);
}
