/// <summary> /// Returns Search Keywords returned from GWT /// </summary> /// <param name="websiteIds">List of websiteids</param> /// <param name="limit">Min Limit is 10, Max Limit is 50</param> /// <param name="offset"></param> /// <returns></returns> public static List <DetailedSearchAnalytics> GetSearchAnalytics(List <string> websiteIds, int limit = 10, int offset = 0) { if (websiteIds == null || websiteIds.Count() == 0) { return(null); } if (limit > 50) { limit = 50; } try { #region Joining the websiteids var websiteIdsKey = new StringBuilder(String.Empty); foreach (var websiteId in websiteIds) { if (!String.IsNullOrEmpty(websiteIdsKey.ToString())) { websiteIdsKey.Append(", '"); websiteIdsKey.Append(websiteId); websiteIdsKey.Append("'"); } else { websiteIdsKey.Append("'"); websiteIdsKey.Append(websiteId); websiteIdsKey.Append("'"); } } #endregion string query = $"select id, date, websiteId, keyword, pageurl, country, device, avgposition, impressions, clicks, feedback from searchAnalytics.gwtSearchAnalytics_kitsune where websiteId IN ({websiteIdsKey}) ORDER BY date DESC LIMIT {limit} OFFSET {offset}"; using (MySqlConnection mysqlConnectionObj = new MySqlConnection(GWTConnectionString)) { mysqlConnectionObj.Open(); using (MySqlCommand command = new MySqlCommand(query, mysqlConnectionObj)) { List <DetailedSearchAnalytics> detailedSearchAnalyticsList = new List <DetailedSearchAnalytics>(); using (var mysqlDataReaderObj = command.ExecuteReader()) { while (mysqlDataReaderObj.Read()) { try { string id = mysqlDataReaderObj.IsDBNull(0) ? "" : Convert.ToString(mysqlDataReaderObj["id"]); DateTime date = Convert.ToDateTime(mysqlDataReaderObj["date"]); string identifierVal = mysqlDataReaderObj.IsDBNull(2) ? "" : mysqlDataReaderObj.GetString(2); string keyword = mysqlDataReaderObj.IsDBNull(3) ? "" : Convert.ToString(mysqlDataReaderObj["keyword"]); string pageurl = mysqlDataReaderObj.IsDBNull(4) ? "" : Convert.ToString(mysqlDataReaderObj["pageurl"]); string country = mysqlDataReaderObj.IsDBNull(5) ? "" : Convert.ToString(mysqlDataReaderObj["country"]); string device = mysqlDataReaderObj.IsDBNull(6) ? "" : Convert.ToString(mysqlDataReaderObj["device"]); int avgposition = Convert.ToInt32(mysqlDataReaderObj["avgposition"]); int impressions = Convert.ToInt32(mysqlDataReaderObj["impressions"]); int clicks = Convert.ToInt32(mysqlDataReaderObj["clicks"]); string feedback = mysqlDataReaderObj.IsDBNull(10) ? "" : Convert.ToString(mysqlDataReaderObj["feedback"]); if (string.IsNullOrWhiteSpace(identifierVal) || string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(keyword)) { continue; } DetailedSearchAnalytics detailedSearchAnalytics = new KitsuneDetailedSearchAnalytics() { WebsiteId = identifierVal, Id = id, Date = date, Keyword = keyword, PageURL = pageurl, Country = country, Device = device, AveragePosition = avgposition, Impressions = impressions, Clicks = clicks, Feedback = null }; if (detailedSearchAnalytics != null) { if (String.Compare(feedback, ("relevant"), true) == 0) { detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.RELEVANT; } else if (String.Compare(feedback, ("spam"), true) == 0) { detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.SPAM; } detailedSearchAnalyticsList.Add(detailedSearchAnalytics); } } catch (Exception e) { Console.WriteLine(e.ToString()); } } if (detailedSearchAnalyticsList?.Count > 0) { return(detailedSearchAnalyticsList); } } } mysqlConnectionObj.Close(); } } catch (Exception e) { Console.WriteLine(e.ToString()); } return(null); }
public static List <DetailedSearchAnalytics> GetDetailedSearchAnalytics(string websiteId, DateTime startDate, DateTime endDate) { if (string.IsNullOrWhiteSpace(websiteId)) { return(null); } try { string query = $"select id, date, websiteId, keyword, pageurl, country, device, avgposition, impressions, clicks, feedback from searchAnalytics.gwtSearchAnalytics_kitsune where websiteId = @identifier and date>=@startDate and date<@endDate"; using (MySqlConnection mysqlConnectionObj = new MySqlConnection(GWTConnectionString)) { mysqlConnectionObj.Open(); using (MySqlCommand command = new MySqlCommand(query, mysqlConnectionObj)) { command.Parameters.AddWithValue("@identifier", websiteId); command.Parameters.AddWithValue("@startDate", startDate.ToString("yyyy-MM-dd")); command.Parameters.AddWithValue("@endDate", endDate.ToString("yyyy-MM-dd")); List <DetailedSearchAnalytics> detailedSearchAnalyticsList = new List <DetailedSearchAnalytics>(); using (var mysqlDataReaderObj = command.ExecuteReader()) { while (mysqlDataReaderObj.Read()) { try { string id = mysqlDataReaderObj.IsDBNull(0) ? "" : Convert.ToString(mysqlDataReaderObj["id"]); DateTime date = Convert.ToDateTime(mysqlDataReaderObj["date"]); string identifierVal = mysqlDataReaderObj.IsDBNull(2) ? "" : mysqlDataReaderObj.GetString(2); string keyword = mysqlDataReaderObj.IsDBNull(3) ? "" : Convert.ToString(mysqlDataReaderObj["keyword"]); string pageurl = mysqlDataReaderObj.IsDBNull(4) ? "" : Convert.ToString(mysqlDataReaderObj["pageurl"]); string country = mysqlDataReaderObj.IsDBNull(5) ? "" : Convert.ToString(mysqlDataReaderObj["country"]); string device = mysqlDataReaderObj.IsDBNull(6) ? "" : Convert.ToString(mysqlDataReaderObj["device"]); int avgposition = Convert.ToInt32(mysqlDataReaderObj["avgposition"]); int impressions = Convert.ToInt32(mysqlDataReaderObj["impressions"]); int clicks = Convert.ToInt32(mysqlDataReaderObj["clicks"]); string feedback = mysqlDataReaderObj.IsDBNull(10) ? "" : Convert.ToString(mysqlDataReaderObj["feedback"]); if (string.IsNullOrWhiteSpace(identifierVal) || string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(keyword)) { continue; } DetailedSearchAnalytics detailedSearchAnalytics = new KitsuneDetailedSearchAnalytics() { WebsiteId = identifierVal, Id = id, Date = date, Keyword = keyword, PageURL = pageurl, Country = country, Device = device, AveragePosition = avgposition, Impressions = impressions, Clicks = clicks, Feedback = null }; if (detailedSearchAnalytics != null) { if (String.Compare(feedback, ("relevant"), true) == 0) { detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.RELEVANT; } else if (String.Compare(feedback, ("spam"), true) == 0) { detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.SPAM; } detailedSearchAnalyticsList.Add(detailedSearchAnalytics); } } catch (Exception e) { Console.WriteLine(e.ToString()); } } if (detailedSearchAnalyticsList?.Count > 0) { return(detailedSearchAnalyticsList); } } } mysqlConnectionObj.Close(); } } catch (Exception e) { Console.WriteLine(e.ToString()); } return(null); }