Ejemplo n.º 1
0
        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);
        }