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); }