public DailyPerformanceResponse GetPerformance(int accountId, string fromDate, string toDate, string themes, string countries) { var r = new Random(); var list = new List <DailyPerformance>(); for (var i = 0; i < 10; i++) { var performance = new DailyPerformance { Acq1 = (double)r.Next(1, 1000) / 100, Acq2 = (double)r.Next(1, 1000) / 100, CPA = (double)r.Next(1, 1000) / 100, CPR = (double)r.Next(1, 1000) / 100, Clicks = r.Next(0, 10), Cost = r.Next(0, 10), Date = DateTime.Now.ToString("dd/MM/yy") }; list.Add(performance); } var response = new DailyPerformanceResponse { PerformanceList = list, Acq1FieldName = "Acq1 name", Acq2FieldName = "Acq2 name", CPAFieldName = "CPA name", CPRFieldName = "CPR name", PerformanceStatistics = new Dictionary <string, double>() }; response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalClicks, Math.Round(response.PerformanceList.Select(x => x.Clicks).Sum(), 1)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCost, Math.Round(response.PerformanceList.Select(x => x.Cost).Sum(), 1)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalAcq1, Math.Round(response.PerformanceList.Select(x => x.Acq1).Sum(), 1)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalAcq2, Math.Round(response.PerformanceList.Select(x => x.Acq2).Sum(), 1)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCPA, Math.Round(response.PerformanceList.Select(x => x.CPA).Sum(), 1)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCPR, Math.Round(response.PerformanceList.Select(x => x.CPR).Sum(), 1)); return(response); }
public DailyPerformanceResponse GetPerformance(int accountId, string from, string to, string themes, string countries) { var perfParam = new PerfromanceParams(accountId, from, to, themes, countries, PerformanceReportType.DailyPerformance); using (var connection = new SqlConnection(AppSettings.GetConnectionString("Edge.Core.Data.DataManager.Connection", "String"))) { connection.Open(); #region Prepare MDX // get cube name and relevant measure config for preparing MDX command var cubeName = GetAccountCubeName(accountId, connection); if (String.IsNullOrWhiteSpace(cubeName)) { throw new MobileApiException(String.Format("Cannot retrieve cube name for account {0}", accountId), String.Format("Your account {0} is not configured correctly, please contact [email protected] (Error: 'Cube').", accountId)); } var measureList = GetMeasures(accountId, connection); if (measureList.Count == 0 || measureList.Any(x => String.IsNullOrEmpty(x.MdxFieldName))) { throw new MobileApiException(String.Format("Measures are not defined properly for account {0}, check if MDX name is defined", accountId), String.Format("Your account {0} is not configured correctly, please contact [email protected] (Error: 'Measures').", accountId)); } // prepare SELECT and FROM var selectClause = String.Format(@"SELECT NONEMPTY({{[Time Dim].[Time Dim].[Day]}}) ON ROWS,( {{ [Measures].[Cost],[Measures].[Clicks],[Measures].[{0}], [Measures].[{1}],[Measures].[{2}],[Measures].[{3}]}} ) ON COLUMNS", measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ1).MdxFieldName, measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ2).MdxFieldName, measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ1_CPA).MdxFieldName, measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ2_CPA).MdxFieldName); var fromClause = String.Format(@"FROM [{0}] WHERE ([Accounts Dim].[Accounts].[Account].&[{1}],{4}{5}{{[Time Dim].[DayCode].&[{2}]:[Time Dim].[DayCode].&[{3}] }})", cubeName, accountId, perfParam.FromDate.ToString("yyyyMMdd"), perfParam.ToDate.ToString("yyyyMMdd"), GetWhereClause(perfParam.Themes, "Theme"), GetWhereClause(perfParam.Countries, "Country")); Log.Write("Mobile API", String.Format("Execute Daily MDX: SELECT='{0}', FROM='{1}'", selectClause, fromClause), LogMessageType.Debug); #endregion try { #region Execute MDX using (var cmd = DataManager.CreateCommand("SP_ExecuteMDX", CommandType.StoredProcedure)) { cmd.Parameters.AddWithValue("@WithMDX", " "); cmd.Parameters.AddWithValue("@SelectMDX", selectClause); cmd.Parameters.AddWithValue("@FromMDX", fromClause); cmd.Connection = connection; cmd.CommandTimeout = GetSqlTimeout(); // execute MDX using (var reader = cmd.ExecuteReader()) { var list = new List <DailyPerformance>(); while (reader.Read()) { list.Add(new DailyPerformance { Date = reader[2] != DBNull.Value ? DateTime.ParseExact(reader[2].ToString(), "dd/MM/yyyy", null, DateTimeStyles.None).ToString("dd/MM/yy") : String.Empty, Cost = reader[3] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[3]), 0) : 0, Clicks = reader[4] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[4]), 0) : 0, Acq1 = reader[5] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[5]), 0) : 0, Acq2 = reader[6] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[6]), 0) : 0, CPA = reader[7] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[7]), 0) : 0, CPR = reader[8] != DBNull.Value ? Math.Round(Convert.ToDouble(reader[8]), 0) : 0, }); } // prepare response var response = new DailyPerformanceResponse { PerformanceList = list, Acq1FieldName = measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ1).MeasureDisplayName, Acq2FieldName = measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ2).MeasureDisplayName, CPAFieldName = measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ1_CPA).MeasureDisplayName, CPRFieldName = measureList.First(x => x.MeasureBaseID == BaseMeasure.ACQ2_CPA).MeasureDisplayName, PerformanceStatistics = new Dictionary <string, double>() }; // calculate statistics values (Sum and Average) response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalClicks, Math.Round(response.PerformanceList.Select(x => x.Clicks).Sum(), 0)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCost, Math.Round(response.PerformanceList.Select(x => x.Cost).Sum(), 0)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalAcq1, Math.Round(response.PerformanceList.Select(x => x.Acq1).Sum(), 0)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalAcq2, Math.Round(response.PerformanceList.Select(x => x.Acq2).Sum(), 0)); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCPA, response.PerformanceList.Select(x => x.Acq1).Sum() > 0 ? Math.Round(response.PerformanceList.Select(x => x.Cost).Sum() / response.PerformanceList.Select(x => x.Acq1).Sum()) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.TotalCPR, response.PerformanceList.Select(x => x.Acq2).Sum() > 0 ? Math.Round(response.PerformanceList.Select(x => x.Cost).Sum() / response.PerformanceList.Select(x => x.Acq2).Sum()) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgClicks, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Clicks).Average(), 0) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgCost, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Cost).Average(), 0) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgAcq1, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Acq1).Average(), 0) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgAcq2, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Acq2).Average(), 0) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgCPA, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Cost).Sum() / response.PerformanceList.Select(x => x.Acq2).Sum(), 0) : 0); response.PerformanceStatistics.Add(PerformanceStatisticsKeys.AvgCPR, response.PerformanceList.Count > 0 ? Math.Round(response.PerformanceList.Select(x => x.Cost).Sum() / response.PerformanceList.Select(x => x.Acq1).Sum(), 0) : 0); return(response); } } #endregion } catch (Exception ex) { throw new MobileApiException(String.Format("Failure in Daily performance MDX, ex: {0}. SELECT='{1}', FROM='{2}'", ex.Message, selectClause, fromClause), "Failed to generate Daily performance report, please contact [email protected] (Error: 'MDX')."); } } }