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_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);
}
/// <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);
}
/// <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_GoalData 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="goal1Starts"> goal1Starts </param>
/// <param name="goal1Completions"> goal1Completions </param>
/// <param name="goal1Value"> goal1Value </param>
/// <param name="goal2Starts"> goal2Starts </param>
/// <param name="goal2Completions"> goal2Completions </param>
/// <param name="goal2Value"> goal2Value </param>
/// <param name="goal3Starts"> goal3Starts </param>
/// <param name="goal3Completions"> goal3Completions </param>
/// <param name="goal3Value"> goal3Value </param>
/// <param name="goal4Starts"> goal4Starts </param>
/// <param name="goal4Completions"> goal4Completions </param>
/// <param name="goal4Value"> goal4Value </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string medium,
int goal1Starts,
int goal1Completions,
decimal goal1Value,
int goal2Starts,
int goal2Completions,
decimal goal2Value,
int goal3Starts,
int goal3Completions,
decimal goal3Value,
int goal4Starts,
int goal4Completions,
decimal goal4Value,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_GoalData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("Medium, ");
sqlCommand.Append("Goal1Starts, ");
sqlCommand.Append("Goal1Completions, ");
sqlCommand.Append("Goal1Value, ");
sqlCommand.Append("Goal2Starts, ");
sqlCommand.Append("Goal2Completions, ");
sqlCommand.Append("Goal2Value, ");
sqlCommand.Append("Goal3Starts, ");
sqlCommand.Append("Goal3Completions, ");
sqlCommand.Append("Goal3Value, ");
sqlCommand.Append("Goal4Starts, ");
sqlCommand.Append("Goal4Completions, ");
sqlCommand.Append("Goal4Value, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?Medium, ");
sqlCommand.Append("?Goal1Starts, ");
sqlCommand.Append("?Goal1Completions, ");
sqlCommand.Append("?Goal1Value, ");
sqlCommand.Append("?Goal2Starts, ");
sqlCommand.Append("?Goal2Completions, ");
sqlCommand.Append("?Goal2Value, ");
sqlCommand.Append("?Goal3Starts, ");
sqlCommand.Append("?Goal3Completions, ");
sqlCommand.Append("?Goal3Value, ");
sqlCommand.Append("?Goal4Starts, ");
sqlCommand.Append("?Goal4Completions, ");
sqlCommand.Append("?Goal4Value, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[18];
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("?Goal1Starts", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = goal1Starts;
arParams[5] = new MySqlParameter("?Goal1Completions", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = goal1Completions;
arParams[6] = new MySqlParameter("?Goal1Value", MySqlDbType.Decimal);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = goal1Value;
arParams[7] = new MySqlParameter("?Goal2Starts", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = goal2Starts;
arParams[8] = new MySqlParameter("?Goal2Completions", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = goal2Completions;
arParams[9] = new MySqlParameter("?Goal2Value", MySqlDbType.Decimal);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = goal2Value;
arParams[10] = new MySqlParameter("?Goal3Starts", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = goal3Starts;
arParams[11] = new MySqlParameter("?Goal3Completions", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = goal3Completions;
arParams[12] = new MySqlParameter("?Goal3Value", MySqlDbType.Decimal);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = goal3Value;
arParams[13] = new MySqlParameter("?Goal4Starts", MySqlDbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = goal4Starts;
arParams[14] = new MySqlParameter("?Goal4Completions", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = goal4Completions;
arParams[15] = new MySqlParameter("?Goal4Value", MySqlDbType.Decimal);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = goal4Value;
arParams[16] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = capturedUtc;
arParams[17] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = Utility.DateTonInteger(analyticsDate);
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static bool UpdateGoal4(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string medium,
int goal4Starts,
int goal4Completions,
decimal goal4Value)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE sts_ga_GoalData ");
sqlCommand.Append("SET ");
sqlCommand.Append("Goal4Starts = ?Goal4Starts, ");
sqlCommand.Append("Goal4Completions = ?Goal4Completions, ");
sqlCommand.Append("Goal4Value = ?Goal4Value ");
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("Medium = ?Medium ");
sqlCommand.Append("AND ");
sqlCommand.Append("Goal4Starts = -1 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[7];
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("?Medium", MySqlDbType.VarChar, 15);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = medium;
arParams[4] = new MySqlParameter("?Goal4Starts", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = goal4Starts;
arParams[5] = new MySqlParameter("?Goal4Completions", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = goal4Completions;
arParams[6] = new MySqlParameter("?Goal4Value", MySqlDbType.Decimal);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = goal4Value;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static IDataReader GetReport(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Medium, ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("SUM(Bounces) As Bounces, ");
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 ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("Medium ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Medium ");
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));
}
/// <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);
}