public static bool DeleteByProfile(Guid siteGuid, string profileId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM sts_ga_Goal ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// gets the oldest row where we have not yet updated group 4 as indicated by Group4Starts = -1
/// </summary>
/// <param name="siteGuid"></param>
/// <param name="profileId"></param>
/// <returns></returns>
public static IDataReader GetOldestUnprocessedGroup4(Guid siteGuid, string profileId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_ga_GoalData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
sqlCommand.Append("AND Goal4Starts = -1 ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ADate ");
sqlCommand.Append("LIMIT 1");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetBasicStatsReport(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("(AVG(Bounces * 1.0)/AVG(Visits * 1.0)) As BounceRate, ");
sqlCommand.Append("SUM(Entrances) As Entrances, ");
sqlCommand.Append("SUM(Exits) As Exits, ");
sqlCommand.Append("(AVG(TimeOnPage)/AVG(Visits)) As TimeOnPage, ");
sqlCommand.Append("(AVG(TimeOnSite)/AVG(Visits)) As TimeOnSite, ");
sqlCommand.Append("AVG(PagesPerVisit) As PagesPerVisit ");
sqlCommand.Append("FROM sts_ga_MediumData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("PageViews > 0 ");
sqlCommand.Append("AND ");
sqlCommand.Append("Visits > 0 ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static void Truncate()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("TRUNCATE TABLE sts_ga_SiteSearchData ");
sqlCommand.Append(";");
MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
null);
}
public static void ReIndex()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("OPTIMIZE TABLE sts_ga_CountryData ");
sqlCommand.Append(";");
MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
null);
}
public static int GetCount(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT COALESCE(Country,'') As Country, Count(*) As TheCount ");
sqlCommand.Append("FROM sts_ga_CountryData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("COALESCE(Country,'') ");
sqlCommand.Append(") s ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static bool EntryExists(Guid siteGuid, string profileId, string browser, string browserVersion, string operatingSystem, DateTime analyticsDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_ga_BrowserData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate = ?ADate ");
sqlCommand.Append("AND ");
sqlCommand.Append("Browser = ?Browser ");
sqlCommand.Append("AND ");
sqlCommand.Append("BrowserVersion = ?BrowserVersion ");
sqlCommand.Append("AND ");
sqlCommand.Append("OperatingSystem = ?OperatingSystem ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[6];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(analyticsDate);
arParams[3] = new MySqlParameter("?Browser", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = browser;
arParams[4] = new MySqlParameter("?BrowserVersion", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = browserVersion;
arParams[5] = new MySqlParameter("?OperatingSystem", MySqlDbType.VarChar, 50);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = operatingSystem;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Updates a row in the sts_ga_Segments table. Returns true if row updated.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="captureStats"> captureStats </param>
/// <param name="sortRank"> sortRank </param>
/// <returns>bool</returns>
public static bool Update(
Guid rowGuid,
bool captureStats,
int sortRank)
{
#region Bit Conversion
int intCaptureStats = 0;
if (captureStats)
{
intCaptureStats = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE sts_ga_Segments ");
sqlCommand.Append("SET ");
sqlCommand.Append("CaptureStats = ?CaptureStats, ");
sqlCommand.Append("SortRank = ?SortRank ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowGuid = ?RowGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CaptureStats", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = intCaptureStats;
arParams[2] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = sortRank;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool EntryExists(Guid siteGuid, string profileId, string searchTerm, DateTime analyticsDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_ga_SiteSearchData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate = ?ADate ");
sqlCommand.Append("AND ");
sqlCommand.Append("SearchTerm = ?SearchTerm ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(analyticsDate);
if (searchTerm.Length > 255)
{
searchTerm = searchTerm.Substring(0, 255);
}
arParams[3] = new MySqlParameter("?SearchTerm", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = searchTerm;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Gets an IDataReader with one row from the sts_ga_Segments table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetOne(Guid rowGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_ga_Segments ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowGuid = ?RowGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a count of rows in the sts_ga_ApiQueryDefinition table.
/// </summary>
public static int GetCount(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_ga_ApiQueryDefinition ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetCount(bool tracking1ProfileOnly, Guid siteGuid, string profileId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT COALESCE(SearchTerm,'') As SearchTerm, Count(*) As TheCount ");
sqlCommand.Append("FROM sts_ga_ExternalSearchData ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("COALESCE(SearchTerm,'') ");
sqlCommand.Append(") s ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Deletes a row from the sts_ga_ApiQueryDefinition table. Returns true if row deleted.
/// </summary>
/// <param name="queryDefId"> queryDefId </param>
/// <returns>bool</returns>
public static bool Delete(Guid queryDefId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM sts_ga_ApiQueryDefinition ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QueryDefId = ?QueryDefId ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?QueryDefId", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = queryDefId.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static bool DeleteBySite(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM sts_ga_ReferringSiteData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static bool Exists(Guid siteGuid, string analyticsUser, string segmentId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_ga_Segments ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("AnalyticsUser = ?AnalyticsUser ");
sqlCommand.Append("AND ");
sqlCommand.Append("SegmentId = ?SegmentId ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?AnalyticsUser", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = analyticsUser;
arParams[2] = new MySqlParameter("?SegmentId", MySqlDbType.VarChar, 50);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = segmentId;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static IDataReader GetMostRecent(Guid siteGuid, string profileId, string segmentId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_ga_SiteData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
sqlCommand.Append("AND ");
sqlCommand.Append("SegmentId = ?SegmentId ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ADate DESC ");
sqlCommand.Append("LIMIT 1");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?SegmentId", MySqlDbType.VarChar, 50);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = segmentId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
bool tracking1ProfileOnly,
Guid siteGuid,
string profileId,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(tracking1ProfileOnly, siteGuid, profileId);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Country, ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("AVG(BounceRate) As BounceRate, ");
sqlCommand.Append("AVG(PagesPerVisit) As PagesPerVisit ");
sqlCommand.Append("FROM sts_ga_CountryData ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("Country ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(PageViews) DESC ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetTopCountryMapPoints(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("City, ");
sqlCommand.Append("Latitude, ");
sqlCommand.Append("Longitude, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(PageViews) As PageViews ");
sqlCommand.Append("FROM sts_ga_CountryData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
sqlCommand.Append("AND City <> '(not set)' ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("City, ");
sqlCommand.Append("Latitude, ");
sqlCommand.Append("Longitude ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(PageViews) DESC ");
sqlCommand.Append("LIMIT 300 ");
sqlCommand.Append(";");
//throw new Exception(sqlCommand.ToString());
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Inserts a row in the sts_ga_CountryData table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="country"> country </param>
/// <param name="region"> region </param>
/// <param name="city"> city </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string country,
string region,
string city,
decimal latitude,
decimal longitude,
int pageViews,
int visits,
int newVisits,
decimal pagesPerVisit,
decimal bounceRate,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_CountryData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("Country, ");
sqlCommand.Append("Region, ");
sqlCommand.Append("City, ");
sqlCommand.Append("Latitude, ");
sqlCommand.Append("Longitude, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("PagesPerVisit, ");
sqlCommand.Append("BounceRate, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?Country, ");
sqlCommand.Append("?Region, ");
sqlCommand.Append("?City, ");
sqlCommand.Append("?Latitude, ");
sqlCommand.Append("?Longitude, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?PagesPerVisit, ");
sqlCommand.Append("?BounceRate, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[15];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
arParams[3] = new MySqlParameter("?Country", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = country;
arParams[4] = new MySqlParameter("?Region", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = region;
arParams[5] = new MySqlParameter("?City", MySqlDbType.VarChar, 50);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = city;
arParams[6] = new MySqlParameter("?Latitude", MySqlDbType.Decimal, 9);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = latitude;
arParams[7] = new MySqlParameter("?Longitude", MySqlDbType.Decimal, 9);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = longitude;
arParams[8] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = pageViews;
arParams[9] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = visits;
arParams[10] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = newVisits;
arParams[11] = new MySqlParameter("?PagesPerVisit", MySqlDbType.Decimal, 9);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = pagesPerVisit;
arParams[12] = new MySqlParameter("?BounceRate", MySqlDbType.Decimal, 7);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = bounceRate;
arParams[13] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = capturedUtc;
arParams[14] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = Utility.DateTonInteger(analyticsDate);
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static IDataReader GetTopCountriesReport(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
//sqlCommand.Append("Continent, ");
//sqlCommand.Append("SubContinent, ");
sqlCommand.Append("Country, ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("AVG(BounceRate) As BounceRate, ");
sqlCommand.Append("AVG(PagesPerVisit) As PagesPerVisit ");
sqlCommand.Append("FROM sts_ga_CountryData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
//sqlCommand.Append("Continent, ");
//sqlCommand.Append("SubContinent, ");
sqlCommand.Append("Country ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(PageViews) DESC ");
sqlCommand.Append("LIMIT 20 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool EntryExists(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string country,
string region,
string city,
decimal latitude,
decimal longitude)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_ga_CountryData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate = ?ADate ");
sqlCommand.Append("AND ");
sqlCommand.Append("Country = ?Country ");
sqlCommand.Append("AND ");
sqlCommand.Append("Region = ?Region ");
sqlCommand.Append("AND ");
sqlCommand.Append("City = ?City ");
sqlCommand.Append("AND ");
sqlCommand.Append("Latitude = ?Latitude ");
sqlCommand.Append("AND ");
sqlCommand.Append("Longitude = ?Longitude ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[8];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(analyticsDate);
arParams[3] = new MySqlParameter("?Country", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = country;
arParams[4] = new MySqlParameter("?Region", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = region;
arParams[5] = new MySqlParameter("?City", MySqlDbType.VarChar, 50);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = city;
arParams[6] = new MySqlParameter("?Latitude", MySqlDbType.Decimal, 9);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = latitude;
arParams[7] = new MySqlParameter("?Longitude", MySqlDbType.Decimal, 9);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = longitude;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Inserts a row in the sts_ga_ApiQueryDefinition table. Returns rows affected count.
/// </summary>
/// <param name="queryDefId"> queryDefId </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="title"> title </param>
/// <param name="segmentId"> segmentId </param>
/// <param name="metrics"> metrics </param>
/// <param name="dimensions"> dimensions </param>
/// <param name="filters"> filters </param>
/// <param name="sort"> sort </param>
/// <param name="beginDate"> beginDate </param>
/// <param name="endDate"> endDate </param>
/// <param name="maxRows"> maxRows </param>
/// <param name="sartIndex"> sartIndex </param>
/// <param name="about"> about </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="createdUtc"> createdUtc </param>
/// <returns>int</returns>
public static int Create(
Guid queryDefId,
Guid siteGuid,
string profileId,
string title,
string segmentId,
string metrics,
string dimensions,
string filters,
string sort,
DateTime beginDate,
DateTime endDate,
byte dateMode,
int beginDateOffset,
int endDateOffset,
int maxRows,
int startIndex,
string about,
Guid createdBy,
DateTime createdUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_ApiQueryDefinition (");
sqlCommand.Append("QueryDefId, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("SegmentId, ");
sqlCommand.Append("Metrics, ");
sqlCommand.Append("Dimensions, ");
sqlCommand.Append("Filters, ");
sqlCommand.Append("Sort, ");
sqlCommand.Append("BeginDate, ");
sqlCommand.Append("EndDate, ");
sqlCommand.Append("DateMode, ");
sqlCommand.Append("BeginDateOffset, ");
sqlCommand.Append("EndDateOffset, ");
sqlCommand.Append("MaxRows, ");
sqlCommand.Append("StartIndex, ");
sqlCommand.Append("About, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("LastModBy, ");
sqlCommand.Append("LastModUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?QueryDefId, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?Title, ");
sqlCommand.Append("?SegmentId, ");
sqlCommand.Append("?Metrics, ");
sqlCommand.Append("?Dimensions, ");
sqlCommand.Append("?Filters, ");
sqlCommand.Append("?Sort, ");
sqlCommand.Append("?BeginDate, ");
sqlCommand.Append("?EndDate, ");
sqlCommand.Append("?DateMode, ");
sqlCommand.Append("?BeginDateOffset, ");
sqlCommand.Append("?EndDateOffset, ");
sqlCommand.Append("?MaxRows, ");
sqlCommand.Append("?StartIndex, ");
sqlCommand.Append("?About, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?LastModBy, ");
sqlCommand.Append("?LastModUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[21];
arParams[0] = new MySqlParameter("?QueryDefId", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = queryDefId.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = profileId;
arParams[3] = new MySqlParameter("?Title", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = title;
arParams[4] = new MySqlParameter("?SegmentId", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = segmentId;
arParams[5] = new MySqlParameter("?Metrics", MySqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = metrics;
arParams[6] = new MySqlParameter("?Dimensions", MySqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = dimensions;
arParams[7] = new MySqlParameter("?Filters", MySqlDbType.Text);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = filters;
arParams[8] = new MySqlParameter("?Sort", MySqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = sort;
arParams[9] = new MySqlParameter("?BeginDate", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = beginDate;
arParams[10] = new MySqlParameter("?EndDate", MySqlDbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = endDate;
arParams[11] = new MySqlParameter("?MaxRows", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = maxRows;
arParams[12] = new MySqlParameter("?StartIndex", MySqlDbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = startIndex;
arParams[13] = new MySqlParameter("?About", MySqlDbType.Text);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = about;
arParams[14] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = createdBy.ToString();
arParams[15] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = createdUtc;
arParams[16] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = createdBy.ToString();
arParams[17] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = createdUtc;
arParams[18] = new MySqlParameter("?DateMode", MySqlDbType.Int16);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = dateMode;
arParams[19] = new MySqlParameter("?BeginDateOffset", MySqlDbType.Int32);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = beginDateOffset;
arParams[20] = new MySqlParameter("?EndDateOffset", MySqlDbType.Int32);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = endDateOffset;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static IDataReader GetPage(
bool tracking1ProfileOnly,
Guid siteGuid,
string profileId,
DateTime beginDate,
DateTime endDate,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(tracking1ProfileOnly, siteGuid, profileId, beginDate, endDate);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("SearchTerm, ");
sqlCommand.Append("SUM(UniqueSearches) As UniqueSearches, ");
sqlCommand.Append("AVG(SearchDepth) As SearchDepth, ");
sqlCommand.Append("SUM(Exits) As Exits, ");
sqlCommand.Append("SUM(Refinements) As Refinements ");
sqlCommand.Append("FROM sts_ga_SiteSearchData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("SearchTerm ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(UniqueSearches) DESC ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[6];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
arParams[4] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageSize;
arParams[5] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Inserts a row in the sts_ga_SiteSearchData table. Returns new integer id.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="searchTerm"> searchTerm </param>
/// <param name="uniqueSearches"> uniqueSearches </param>
/// <param name="searchDepth"> searchDepth </param>
/// <param name="exits"> exits </param>
/// <param name="refinements"> refinements </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string searchTerm,
int uniqueSearches,
int searchDepth,
int exits,
int refinements,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_SiteSearchData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("SearchTerm, ");
sqlCommand.Append("UniqueSearches, ");
sqlCommand.Append("SearchDepth, ");
sqlCommand.Append("Exits, ");
sqlCommand.Append("Refinements, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?SearchTerm, ");
sqlCommand.Append("?UniqueSearches, ");
sqlCommand.Append("?SearchDepth, ");
sqlCommand.Append("?Exits, ");
sqlCommand.Append("?Refinements, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[10];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
if (searchTerm.Length > 255)
{
searchTerm = searchTerm.Substring(0, 255);
}
arParams[3] = new MySqlParameter("?SearchTerm", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = searchTerm;
arParams[4] = new MySqlParameter("?UniqueSearches", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = uniqueSearches;
arParams[5] = new MySqlParameter("?SearchDepth", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = searchDepth;
arParams[6] = new MySqlParameter("?Exits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = exits;
arParams[7] = new MySqlParameter("?Refinements", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = refinements;
arParams[8] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = capturedUtc;
arParams[9] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = Utility.DateTonInteger(analyticsDate);
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static IDataReader GetTopReport(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("SearchTerm, ");
sqlCommand.Append("SUM(UniqueSearches) As UniqueSearches, ");
sqlCommand.Append("SUM(SearchDepth) As SearchDepth, ");
sqlCommand.Append("SUM(Exits) As Exits, ");
sqlCommand.Append("SUM(Refinements) As Refinements ");
sqlCommand.Append("FROM sts_ga_SiteSearchData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("SearchTerm ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(UniqueSearches) DESC ");
sqlCommand.Append("LIMIT 20 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
Guid siteGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM sts_ga_ApiQueryDefinition ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Title ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Inserts a row in the sts_ga_Goal table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="goal1Name"> goal1Name </param>
/// <param name="goal1Value"> goal1Value </param>
/// <param name="goal1IsActtive"> goal1IsActtive </param>
/// <param name="goal2Name"> goal2Name </param>
/// <param name="goal2Value"> goal2Value </param>
/// <param name="goal2IsActive"> goal2IsActive </param>
/// <param name="goal3Name"> goal3Name </param>
/// <param name="goal3Value"> goal3Value </param>
/// <param name="goal3IsActive"> goal3IsActive </param>
/// <param name="goal4Name"> goal4Name </param>
/// <param name="goal4Value"> goal4Value </param>
/// <param name="gaol4IsActive"> gaol4IsActive </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
string profileId,
string goal1Name,
decimal goal1Value,
bool goal1IsActtive,
string goal2Name,
decimal goal2Value,
bool goal2IsActive,
string goal3Name,
decimal goal3Value,
bool goal3IsActive,
string goal4Name,
decimal goal4Value,
bool gaol4IsActive,
DateTime capturedUtc)
{
#region Bit Conversion
int intGoal1IsActtive = 0;
if (goal1IsActtive)
{
intGoal1IsActtive = 1;
}
int intGoal2IsActive = 0;
if (goal2IsActive)
{
intGoal2IsActive = 1;
}
int intGoal3IsActive = 0;
if (goal3IsActive)
{
intGoal3IsActive = 1;
}
int intGaol4IsActive = 0;
if (gaol4IsActive)
{
intGaol4IsActive = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_Goal (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("Goal1Name, ");
sqlCommand.Append("Goal1Value, ");
sqlCommand.Append("Goal1IsActtive, ");
sqlCommand.Append("Goal2Name, ");
sqlCommand.Append("Goal2Value, ");
sqlCommand.Append("Goal2IsActive, ");
sqlCommand.Append("Goal3Name, ");
sqlCommand.Append("Goal3Value, ");
sqlCommand.Append("Goal3IsActive, ");
sqlCommand.Append("Goal4Name, ");
sqlCommand.Append("Goal4Value, ");
sqlCommand.Append("Gaol4IsActive, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?Goal1Name, ");
sqlCommand.Append("?Goal1Value, ");
sqlCommand.Append("?Goal1IsActtive, ");
sqlCommand.Append("?Goal2Name, ");
sqlCommand.Append("?Goal2Value, ");
sqlCommand.Append("?Goal2IsActive, ");
sqlCommand.Append("?Goal3Name, ");
sqlCommand.Append("?Goal3Value, ");
sqlCommand.Append("?Goal3IsActive, ");
sqlCommand.Append("?Goal4Name, ");
sqlCommand.Append("?Goal4Value, ");
sqlCommand.Append("?Gaol4IsActive, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[16];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = profileId;
arParams[3] = new MySqlParameter("?Goal1Name", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = goal1Name;
arParams[4] = new MySqlParameter("?Goal1Value", MySqlDbType.Decimal);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = goal1Value;
arParams[5] = new MySqlParameter("?Goal1IsActtive", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = intGoal1IsActtive;
arParams[6] = new MySqlParameter("?Goal2Name", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = goal2Name;
arParams[7] = new MySqlParameter("?Goal2Value", MySqlDbType.Decimal);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = goal2Value;
arParams[8] = new MySqlParameter("?Goal2IsActive", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = intGoal2IsActive;
arParams[9] = new MySqlParameter("?Goal3Name", MySqlDbType.VarChar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = goal3Name;
arParams[10] = new MySqlParameter("?Goal3Value", MySqlDbType.Decimal);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = goal3Value;
arParams[11] = new MySqlParameter("?Goal3IsActive", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = intGoal3IsActive;
arParams[12] = new MySqlParameter("?Goal4Name", MySqlDbType.VarChar, 255);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = goal4Name;
arParams[13] = new MySqlParameter("?Goal4Value", MySqlDbType.Decimal);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = goal4Value;
arParams[14] = new MySqlParameter("?Gaol4IsActive", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = intGaol4IsActive;
arParams[15] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = capturedUtc;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the sts_ga_MediumData table. Returns rows affected count.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="medium"> medium </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="entrances"> entrances </param>
/// <param name="exits"> exits </param>
/// <param name="timeOnPage"> timeOnPage </param>
/// <param name="timeOnSite"> timeOnSite </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string medium,
int pageViews,
int visits,
int newVisits,
int bounces,
int entrances,
int exits,
decimal pagesPerVisit,
decimal timeOnPage,
decimal timeOnSite,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_MediumData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("Medium, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("Bounces, ");
sqlCommand.Append("Entrances, ");
sqlCommand.Append("Exits, ");
sqlCommand.Append("PagesPerVisit, ");
sqlCommand.Append("TimeOnPage, ");
sqlCommand.Append("TimeOnSite, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?Medium, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?Bounces, ");
sqlCommand.Append("?Entrances, ");
sqlCommand.Append("?Exits, ");
sqlCommand.Append("?PagesPerVisit, ");
sqlCommand.Append("?TimeOnPage, ");
sqlCommand.Append("?TimeOnSite, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[15];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
arParams[3] = new MySqlParameter("?Medium", MySqlDbType.VarChar, 15);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = medium;
arParams[4] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageViews;
arParams[5] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = visits;
arParams[6] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = newVisits;
arParams[7] = new MySqlParameter("?Bounces", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bounces;
arParams[8] = new MySqlParameter("?Entrances", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = entrances;
arParams[9] = new MySqlParameter("?Exits", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = exits;
arParams[10] = new MySqlParameter("?TimeOnPage", MySqlDbType.Decimal);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = timeOnPage;
arParams[11] = new MySqlParameter("?TimeOnSite", MySqlDbType.Decimal);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = timeOnSite;
arParams[12] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = capturedUtc;
arParams[13] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = Utility.DateTonInteger(analyticsDate);
arParams[14] = new MySqlParameter("?PagesPerVisit", MySqlDbType.Decimal);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = pagesPerVisit;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the sts_ga_ReferringSiteData table. Returns new integer id.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="source"> source </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="pagesPerVisit"> pagesPerVisit </param>
/// <param name="timeOnPage"> timeOnPage </param>
/// <param name="timeOnSite"> timeOnSite </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string source,
int pageViews,
int visits,
int newVisits,
decimal bounceRate,
decimal pagesPerVisit,
decimal timeOnPage,
decimal timeOnSite,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_ReferringSiteData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("Source, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("BounceRate, ");
sqlCommand.Append("PagesPerVisit, ");
sqlCommand.Append("TimeOnPage, ");
sqlCommand.Append("TimeOnSite, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?Source, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?BounceRate, ");
sqlCommand.Append("?PagesPerVisit, ");
sqlCommand.Append("?TimeOnPage, ");
sqlCommand.Append("?TimeOnSite, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[13];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
arParams[3] = new MySqlParameter("?Source", MySqlDbType.VarChar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = source;
arParams[4] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageViews;
arParams[5] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = visits;
arParams[6] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = newVisits;
arParams[7] = new MySqlParameter("?BounceRate", MySqlDbType.Decimal);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bounceRate;
arParams[8] = new MySqlParameter("?PagesPerVisit", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = pagesPerVisit;
arParams[9] = new MySqlParameter("?TimeOnPage", MySqlDbType.Decimal);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = timeOnPage;
arParams[10] = new MySqlParameter("?TimeOnSite", MySqlDbType.Decimal);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = timeOnSite;
arParams[11] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = capturedUtc;
arParams[12] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = Utility.DateTonInteger(analyticsDate);
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
/// <summary>
/// Inserts a row in the sts_ga_Segments table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="analyticsUser"> analyticsUser </param>
/// <param name="segmentId"> segmentId </param>
/// <param name="segmentName"> segmentName </param>
/// <param name="definition"> definition </param>
/// <param name="captureStats"> captureStats </param>
/// <param name="sortRank"> sortRank </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
string analyticsUser,
string segmentId,
string segmentName,
string definition,
bool captureStats,
int sortRank)
{
#region Bit Conversion
int intCaptureStats = 0;
if (captureStats)
{
intCaptureStats = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_Segments (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("AnalyticsUser, ");
sqlCommand.Append("SegmentId, ");
sqlCommand.Append("SegmentName, ");
sqlCommand.Append("Definition, ");
sqlCommand.Append("CaptureStats, ");
sqlCommand.Append("SortRank )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?AnalyticsUser, ");
sqlCommand.Append("?SegmentId, ");
sqlCommand.Append("?SegmentName, ");
sqlCommand.Append("?Definition, ");
sqlCommand.Append("?CaptureStats, ");
sqlCommand.Append("?SortRank )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[8];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?AnalyticsUser", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsUser;
arParams[3] = new MySqlParameter("?SegmentId", MySqlDbType.VarChar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = segmentId;
arParams[4] = new MySqlParameter("?SegmentName", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = segmentName;
arParams[5] = new MySqlParameter("?Definition", MySqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = definition;
arParams[6] = new MySqlParameter("?CaptureStats", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = intCaptureStats;
arParams[7] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = sortRank;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}