<telerik:RadGrid ID="mkgtTotalLevels" runat="server" Skin="Outlook" OnItemDataBound="mkgtTotalLevels_ItemDataBound"
OnNeedDataSource="mkgtTotalLevels_NeedDataSource" ondetailtabledatabind="mkgtTotalLevels_DetailTableDataBind"
OnItemCommand="mkgtTotalLevels_ItemCommand" DataSourcePersistenceMode="NoPersistence"
AllowPaging="True" AllowSorting="true" PageSize="20" HeaderStyle-Font-Bold="true"
Width="100%" AutoGenerateColumns="true">
<MasterTableView Name="Level1" DataKeyNames="MarketingSourceID" CommandItemDisplay="Top">
<DetailTables>
<telerik:GridTableView Name="Level2" DataKeyNames="MarketingSourceSubID" CommandItemDisplay="Top" HierarchyLoadMode="ServerOnDemand"
AutoGenerateColumns="true" Width="100%" BackColor="#EFEFFB" >
<DetailTables>
<telerik:GridTableView Name="Level3" DataKeyNames="" CommandItemDisplay="Top" HierarchyLoadMode="ServerOnDemand"
AutoGenerateColumns="true" Width="100%" BackColor="#FFFFCC" >
</telerik:GridTableView>
</DetailTables>
</telerik:GridTableView>
</DetailTables>
</MasterTableView>
==============================================================================================
protected void mkgtTotalLevels_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
BindDataGrid();
}
private void BindDataGrid()
{
SetRangeDates();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TargetInfoNetConnectionString"].ConnectionString);
try
{
// Get a Pivot table with the Marketing Level 1 - Total of Providers
string str = "dbo.spPivot_MkgtLevel1_Totals";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DateFrom", SqlDbType.Date);
cmd.Parameters["@DateFrom"].Value = StartDate;
cmd.Parameters.Add("@DateTo", SqlDbType.Date);
cmd.Parameters["@DateTo"].Value = EndDate;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
mkgtTotalLevels.DataSource = dt;
}
finally
{
con.Close();
}
}
protected void mkgtTotalLevels_DetailTableDataBind(object source, Telerik.Web.UI.GridDetailTableDataBindEventArgs e)
{
GridDataItem dataItem = (GridDataItem)e.DetailTableView.ParentItem;
//**** Level 2 ****
if (e.DetailTableView.Name == "Level2")
{
DataTable dt2 = new DataTable();
Int32 Level1 = 0;
Int32.TryParse(dataItem.GetDataKeyValue("MarketingSourceID").ToString(), out Level1);
dt2 = GetLevel2Data(Level1);
e.DetailTableView.DataSource = dt2;
}
//**** Level 3 ****
if (e.DetailTableView.Name == "Level3")
{
DataTable dt3 = new DataTable();
Int32 Level2 = 0;
Int32.TryParse(dataItem.GetDataKeyValue("MarketingSourceSubID").ToString(), out Level2);
dt3 = GetLevel3Data(Level2);
e.DetailTableView.DataSource = dt3;
}
}
private DataTable GetLevel2Data(int Level1)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TargetInfoNetConnectionString"].ConnectionString);
try
{
SetRangeDates();
// Get a Pivot table with the Marketing Level 2 - Total of Providers
string str = "dbo.spPivot_MkgtLevel2_Totals";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DateFrom", SqlDbType.Date);
cmd.Parameters["@DateFrom"].Value = StartDate;
cmd.Parameters.Add("@DateTo", SqlDbType.Date);
cmd.Parameters["@DateTo"].Value = EndDate;
cmd.Parameters.Add("@MarketingSourceID", SqlDbType.Int);
cmd.Parameters["@MarketingSourceID"].Value = Level1;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
finally
{
con.Close();
}
}
private DataTable GetLevel3Data(int Level2)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TargetInfoNetConnectionString"].ConnectionString);
try
{
SetRangeDates();
// Get a Pivot table with the Marketing Level 3 - Total of Providers
string str = "dbo.spPivot_MkgtLevel3_Totals";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DateFrom", SqlDbType.Date);
cmd.Parameters["@DateFrom"].Value = StartDate;
cmd.Parameters.Add("@DateTo", SqlDbType.Date);
cmd.Parameters["@DateTo"].Value = EndDate;
cmd.Parameters.Add("@MarketingSourceSubID", SqlDbType.Int);
cmd.Parameters["@MarketingSourceSubID"].Value = Level2;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
finally
{
con.Close();
}
}
private void SetRangeDates()
{
DateTime tmpDate;
int intYear = 0;
int intMonth = 0;
int intDay = 0;
// **** Start Date ****
intYear = int.Parse(ddlFromYear.SelectedValue.ToString());
intMonth = int.Parse(ddlFromMonth.SelectedValue.ToString());
intDay = 1;
tmpDate = new DateTime(intYear, intMonth, intDay);
StartDate = tmpDate;
// **** End Date ****
intYear = int.Parse(ddlToYear.SelectedValue.ToString());
intMonth = int.Parse(ddlToMonth.SelectedValue.ToString());
tmpDate = new DateTime(intYear, intMonth, 1);
tmpDate = tmpDate.AddMonths(1);
tmpDate = tmpDate.AddDays(-1);
intDay = tmpDate.Day;
tmpDate = new DateTime(intYear, intMonth, intDay);
EndDate = tmpDate;
}