public static string GetBarData()
{
//string constring = @"Data Source=(localdb)\v11.0;Initial Catalog=WebAnalytics;Persist Security Info=True;Integrated Security=true;";
string retval = "test";
DataHelper dh = new DataHelper();
DataSet ds = new DataSet();
dh.AddParameterToSQLCommand("@compapp1", SqlDbType.VarChar, 100);
dh.AddParameterToSQLCommand("@compapp2", SqlDbType.VarChar, 100);
string qrytxt = "select Count(*) AS [COUNT],AppID, CONVERT(VARCHAR(3),DateName( month , DateAdd( month , DATEPART(mm,HitDateTime) , 0 ) - 1 )) + '-'+CONVERT(VARCHAR(10),DATEPART(year,HitDateTime)) AS [TIMELINE] FROM Pagestats WHERE Action = 'load' GROUP BY APPID,CONVERT(VARCHAR(3),DateName( month , DateAdd( month , DATEPART(mm,HitDateTime) , 0 ) - 1 ))+'-'+CONVERT(VARCHAR(10),DATEPART(year,HitDateTime))";
ds = dh.GetDatasetByCommand("AppCompareBar", CommandType.StoredProcedure);
retval = JsonConvert.SerializeObject(ds);
return retval;
}
public static string GetDoughNutData()
{
//string constring = @"Data Source=(localdb)\v11.0;Initial Catalog=WebAnalytics;Persist Security Info=True;Integrated Security=true;";
string retval = "";
DataHelper dh = new DataHelper();
DataSet ds = new DataSet();
//dh.AddParameterToSQLCommand("@compapp1", SqlDbType.VarChar, 100);
//dh.AddParameterToSQLCommand("@compapp2", SqlDbType.VarChar, 100);
string qrytxt = "select Count(*) AS value,Region As label FROM Pagestats WHERE Country='US' AND Action = 'load' AND AppID='VzPortal' GROUP BY Region";
ds = dh.GetDatasetByCommand(qrytxt, CommandType.Text);
retval = JsonConvert.SerializeObject(ds);
return retval;
}
public static string GetLineData()
{
//string constring = @"Data Source=(localdb)\v11.0;Initial Catalog=WebAnalytics;Persist Security Info=True;Integrated Security=true;";
string retval = "test";
DataHelper dh = new DataHelper();
DataSet ds = new DataSet();
dh.AddParameterToSQLCommand("@compapp1", SqlDbType.VarChar, 100);
dh.AddParameterToSQLCommand("@compapp2", SqlDbType.VarChar, 100);
ds = dh.GetDatasetByCommand("AreaChartComapreApps", CommandType.StoredProcedure);
retval = JsonConvert.SerializeObject(ds);
return retval;
}
public static string GetMapData()
{
//string constring = @"Data Source=(localdb)\v11.0;Initial Catalog=WebAnalytics;Persist Security Info=True;Integrated Security=true;";
string retval = "";
DataHelper dh = new DataHelper();
DataSet ds = new DataSet();
//dh.AddParameterToSQLCommand("@compapp1", SqlDbType.VarChar, 100);
//dh.AddParameterToSQLCommand("@compapp2", SqlDbType.VarChar, 100);
string qrytxt = "select TOP 1 AVG(CONVERT(INT,ISNULL(LoadTime,'0'))) [AvgTimeinms],SubString(latlang,0,CHARINDEX(',',latlang)) AS latitude ,SubString(latlang,CHARINDEX(',',latlang)+1,len(latlang)) AS longitude from pagestats Group BY SubString(latlang,0,CHARINDEX(',',latlang)),SubString(latlang,CHARINDEX(',',latlang)+1,len(latlang)) ORDER BY [AvgTimeinms] DESC";
ds = dh.GetDatasetByCommand(qrytxt, CommandType.Text);
retval = JsonConvert.SerializeObject(ds);
return retval;
}
public static string GetGridData()
{
//string constring = @"Data Source=(localdb)\v11.0;Initial Catalog=WebAnalytics;Persist Security Info=True;Integrated Security=true;";
string retval = "";
DataHelper dh = new DataHelper();
DataSet ds = new DataSet();
//dh.AddParameterToSQLCommand("@compapp1", SqlDbType.VarChar, 100);
//dh.AddParameterToSQLCommand("@compapp2", SqlDbType.VarChar, 100);
string qrytxt = "select Top 100 AppID,PageID,Action,ActionName,Count(*) AS Hits,AVG(CONVERT(INT,ISNULL(LoadTime,'0'))) [AvgTime in ms] from pagestats Group by Action,ActionName,PageID,AppID ORDER BY Hits DESC";
ds = dh.GetDatasetByCommand(qrytxt, CommandType.Text);
retval = JsonConvert.SerializeObject(ConvertDataTableToHtml(ds.Tables[0]));
return retval;
}