Conversion of Reporting Starter Kit to use Mono/MySQL

<%@ Page language="c#" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="ASPNET.StarterKit.Reports.Components" %>

<script runat="server">
	
	//*********************************************************************
	//
	// Hierarchical.aspx
	//
	// The Hierarchical.aspx page shows a basic way to filter related data 
	// in a grid based on information selected from another grid, which
	// in turn was filtered and grouped based on information selected from 
	// yet another grid.  This is a very common representation of a hierarchical
	// data structure.
	//
	// This is accomplished by displaying three different datagrids, each bound 
	// by separate stored procedures.  The second and third stored procedures
	// take in input parameters to filter the data, which are determined by 
	// which data item is selected in the prior, or parent, datagrid.
	//
	//*********************************************************************
	
	protected string _styleSheet;
	protected Int32 _currentPageNumber_Terr = 1;
	protected Int32 _currentPageNumber_EmpTerr = 1;

	//****************
	//	PROPERTIES
	//****************
	string SortField_Terr
	{
		// SortField property is tracked in ViewState for both the
		// TerritoryGrid and the EmployeeTerritoryGrid	
		get 
		{
			object o = ViewState["SortField_Terr"];
			if (o == null) 
			{
				return String.Empty;
			}
			return (string)o;
		}
		set 
		{
			if (value == SortField_Terr) 
			{
				// same as current sort file, toggle sort direction
				SortAscending_Terr = !SortAscending_Terr;
			}
			ViewState["SortField_Terr"] = value;
		}
	}
	string SortField_EmpTerr 
	{
		get 
		{
			object o = ViewState["SortField_EmpTerr"];
			if (o == null) 
			{
				return String.Empty;
			}
			return (string)o;
		}
		set 
		{
			if (value == SortField_EmpTerr) 
			{
				// same as current sort file, toggle sort direction
				SortAscending_EmpTerr = !SortAscending_EmpTerr;
			}
			ViewState["SortField_EmpTerr"] = value;
		}
	}

	// SortAscending property is tracked in ViewState for both the
	// TerritoryGrid and the EmployeeTerritoryGrid			
	bool SortAscending_Terr 
	{
		get 
		{
			object o = ViewState["SortAscending_Terr"];

			if (o == null) 
			{
				return true;
			}
			return (bool)o;
		}
		set 
		{
			ViewState["SortAscending_Terr"] = value;
		}
	}
	bool SortAscending_EmpTerr 
	{
		get 
		{
			object o = ViewState["SortAscending_EmpTerr"];

			if (o == null) 
			{
				return true;
			}
			return (bool)o;
		}
		set 
		{
			ViewState["SortAscending_EmpTerr"] = value;
		}
	}

	//****************
	//	PAGE_LOAD
	//****************
	private void Page_Load(object sender, System.EventArgs e)
	{
		if (!IsPostBack)
		{
			// default sort will be by Territory Name for Territory Grid, and 
			// EmployeeName for Employee Grid
			if (SortField_Terr == "") 
				SortField_Terr = "Territory";

			if (SortField_EmpTerr == "") 
				SortField_EmpTerr = "EmployeeName";

			BindList_Terr();
		}

		// switches the style sheet based on printer friendly view or not
		if (Request.QueryString["Print"]=="true")
		{
			_styleSheet = "stylesPrint.css";
			PrintButton.Visible = true;
		}
		else 
		{
			_styleSheet = "styles.css";
		}
	}

	//****************
	//	DATA BINDING
	//****************

	//*********************************************************************
	//
	// The BindList_Terr method retrieves the list of Sales per Territory
	// and then databinds them to the Territory Grid control (1st control)
	//
	//*********************************************************************
	private void BindList_Terr()
	{
		HierarchicalReportCollection territoryList = HierarchicalReport.GetSalesByTerritory(Convert.ToInt32(ddlYear.SelectedItem.Value));
		
		// do the sorting if there are data returned
		if (territoryList.Count > 0) 
			SortGridData(territoryList, SortField_Terr, SortAscending_Terr);

		TerritoryGrid.DataSource = territoryList;
		TerritoryGrid.DataBind();

		// Update paging labels
		CurrPage_Terr.Text = Convert.ToString(TerritoryGrid.CurrentPageIndex + 1);
		TotPages_Terr.Text = TerritoryGrid.PageCount.ToString();
	}

	//*********************************************************************
	//
	// The BindList_EmpTerr method retrieves the list of Sales per 
	// Employee within a certain Territory, and then databinds them to the 
	// EmployeeTerritory Grid control (2nd control)
	//
	//*********************************************************************
	private void BindList_EmpTerr(string territoryName)
	{
		HierarchicalReportCollection empTerritoryList = HierarchicalReport.GetEmployeeSalesByTerritory(territoryName, Convert.ToInt32(ddlYear.SelectedItem.Value));
		
		// do the sorting if there are data returned
		if (empTerritoryList.Count > 0) 
		{
			SortGridData(empTerritoryList, SortField_EmpTerr, SortAscending_EmpTerr);

			EmployeeTerritoryGrid.DataSource = empTerritoryList;
			EmployeeTerritoryGrid.DataBind();
		}

		// Update paging labels
		CurrPage_EmpTerr.Text = Convert.ToString(EmployeeTerritoryGrid.CurrentPageIndex + 1);
		TotPages_EmpTerr.Text = EmployeeTerritoryGrid.PageCount.ToString();
	}

	//**************************************************************************
	//
	// The BindList_Emp method retrieves the information for a single Employee, 
	// and then databinds them to the Employee grid control (3rd control).
	//
	// There is no paging or sorting in this control.
	//
	//**************************************************************************
	private void BindList_Emp(int employeeID)
	{
		HierarchicalReportCollection employeeInfo = HierarchicalReport.GetEmployeeInfo(employeeID);
		
		EmployeeGrid.DataSource = employeeInfo;
		EmployeeGrid.DataBind();
	}

	//****************
	//	PAGING
	//****************

	//**************************************************************************
	//
	// The Page_* methods move the CurrentPageIndex to the appropriate
	// previous or next page for the 2 grid controls. 
	//
	//**************************************************************************

	protected void Page_Terr(Object sender,  DataGridPageChangedEventArgs e)
	{
		// Hide the 2nd and 3rd table if they were visible
		ETTableColumn.Visible = false;
		EmpTableColumn.Visible = false;

		TerritoryGrid.CurrentPageIndex = e.NewPageIndex;
		BindList_Terr();
	}

	protected void Page_EmpTerr(Object sender, DataGridPageChangedEventArgs e)
	{
		// Hide the 3rd table if it was visible
		EmpTableColumn.Visible = false;

		EmployeeTerritoryGrid.CurrentPageIndex = e.NewPageIndex;
		BindList_EmpTerr(ETHeader.Text);
	}


	//****************
	//	SORTING
	//****************

	//***********************************************************************
	//
	// The SortGridData methods sorts the array list bound to the datagrid 
	// based on which sort field is being selected.  This also handles reverse 
	// sorting based on the boolean.
	//
	// This method is used for both the Territory Grid control and the 
	// EmployeeTerritory Grid control.
	//
	//***********************************************************************
	private void SortGridData(HierarchicalReportCollection list, string sortField, bool asc)
	{
		HierarchicalReportCollection.HierarchicalReportFields sortCol = HierarchicalReportCollection.HierarchicalReportFields.InitValue;

		switch(sortField)
		{
			case "Territory":
				sortCol = HierarchicalReportCollection.HierarchicalReportFields.Territory;
				break;
			case "SalesTotals":
				sortCol = HierarchicalReportCollection.HierarchicalReportFields.SalesTotals;
				break;
			case "EmployeeName":
				sortCol = HierarchicalReportCollection.HierarchicalReportFields.EmployeeName;
				break;
		}

		list.Sort(sortCol, asc);
	}

	//*********************************************************************
	//
	// The SortGrid event handlers change the sortfield for the Territory  
	// grid and the EmployeeTerritory grid, respectively, and re-binds them.
	//
	//*********************************************************************
	protected void SortGrid_Terr(Object sender, DataGridSortCommandEventArgs e) 
	{
		// Hide the 2nd and 3rd table if they were visible
		ETTableColumn.Visible = false;
		EmpTableColumn.Visible = false;

		// change sort field
		SortField_Terr = (string)e.SortExpression;

		// move back to the first page
		TerritoryGrid.CurrentPageIndex = 0;

		// re-bind to display new sorting
		BindList_Terr();
	}

	protected void SortGrid_EmpTerr(Object sender, DataGridSortCommandEventArgs e) 
	{
		// Hide the 3rd table if it was visible
		EmpTableColumn.Visible = false;
		
		// change sort field
		SortField_EmpTerr = (string)e.SortExpression;

		// move back to the first page
		EmployeeTerritoryGrid.CurrentPageIndex = 0;

		// re-bind to display new sorting
		BindList_EmpTerr(ETHeader.Text);
	}


	//****************
	//	DRILLING DOWN
	//****************
	protected void TerritoryGrid_Click(Object sender, CommandEventArgs e)
	{
		string territoryName = e.CommandArgument.ToString().Trim();

		// Put the second grid back to the first page
		EmployeeTerritoryGrid.CurrentPageIndex = 0;

		// Show the table with the second grid, and update the headers
		ETTableColumn.Visible = true;
		ETHeader.Text = territoryName;
		BindList_EmpTerr(territoryName);

		// Hide the 3rd table if it was visible
		EmpTableColumn.Visible = false;
	}
	
	protected void ETGrid_Click(Object sender, CommandEventArgs e)
	{
		string[] cmdArgs =  e.CommandArgument.ToString().Split(',');

		string employeeName = cmdArgs[0];
		int employeeID = Convert.ToInt32(cmdArgs[1]);

		// Show the table with the 3rd grid, and update the headers
		EmpTableColumn.Visible = true;
		EmployeeName.Text = employeeName;
		BindList_Emp(employeeID);			
	}

	private void ddlYear_SelectedIndexChanged(object sender, System.EventArgs e)
	{
		// Put the first grid back to the first page
		TerritoryGrid.CurrentPageIndex = 0;

		// Hide the 2nd and 3rd table if they were visible
		ETTableColumn.Visible = false;
		EmpTableColumn.Visible = false;
		
		BindList_Terr();
	}
</script>

<!doctype html public "-//w3c//dtd html 4.0 transitional//en" >
<html>
	<head>
		<title>Hierarchical</title>
		<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
		<link href="<%= _styleSheet %>" type=text/css rel=stylesheet>
		<script src="scripts.js"></script>
	</head>
	<body class="Report" leftmargin="0" topmargin="0" marginheight="0" marginwidth="0">
		<form id="Tabular" method="post" runat="server">
			<table cellspacing="0" cellpadding="0" width="950" border="0">
				<tr height="20">
					<td colspan="3"></td>
				</tr>
				<tr height="20">
					<td width="20"></td>
					<td colspan="1"><asp:dropdownlist runat="server" id="ddlYear" OnSelectedIndexChanged="ddlYear_SelectedIndexChanged" width="105px" autopostback="True">
							<asp:listitem value="1996" selected="True">1996</asp:listitem>
							<asp:listitem value="1997">1997</asp:listitem>
							<asp:listitem value="1998">1998</asp:listitem>
						</asp:dropdownlist>
					</td>
					<td align="right" width="50">
						<asp:hyperlink id="PrintButton" navigateurl="javascript:Print()" cssclass="printbutton" runat="server" visible="False">Print</asp:hyperlink>
					</td>
				</tr>
				<tr height="20">
					<td colspan="4"></td>
				</tr>
				<tr>
					<td width="20"></td>
					<td colspan="2" width="225" valign="top">
						<table cellspacing="0" cellpadding="0" width="225" border="0">
							<tr>
								<td class="CategoryHeaderHier" width="5">&nbsp;</td>
								<td class="CategoryHeaderHier" valign="top">Sales by Territory
								</td>
								<td class="CategoryHeaderHier" width="5">&nbsp;</td>
							</tr>
							<tr>
								<td class="Content"></td>
								<td class="Content"><asp:datagrid id="TerritoryGrid" runat="server" autogeneratecolumns="False" allowpaging="True" allowsorting="True" onsortcommand="SortGrid_Terr" onpageindexchanged="Page_Terr" gridlines="None" borderwidth="0px" pagesize="5">
										<itemstyle height="15px"></itemstyle>
										<headerstyle cssclass="ReportTitle"></headerstyle>
										<columns>
											<asp:templatecolumn sortexpression="Territory" headertext="Territory Name">
												<itemtemplate>
													<asp:linkbutton oncommand="TerritoryGrid_Click" commandname="TerritoryDrill" commandargument='<%# (String)DataBinder.Eval(Container, "DataItem.TerritoryDescription")%>' cssclass="hier" runat="server" id="Territorylnk" Text='<%# DataBinder.Eval(Container, "DataItem.TerritoryDescription") %>'>
													</asp:linkbutton>
												</itemtemplate>
												<itemstyle width="150px"></itemstyle>
											</asp:templatecolumn>
											<asp:boundcolumn datafield="SalesTotals" sortexpression="SalesTotals" headertext="Sales" dataformatstring="{0:c}">
												<headerstyle cssclass="ProductHeaderRight"></headerstyle>
												<itemstyle width="100px" cssclass="ItemStyleRight"></itemstyle>
											</asp:boundcolumn>
										</columns>
										<pagerstyle cssclass="PagerHier" prevpagetext="&lt; Prev" nextpagetext="Next &gt;" height="30px" verticalalign="middle"></pagerstyle>
									</asp:datagrid></td>
								<td class="Content"></td>
							</tr>
							<tr height="10">
								<td class="CategoryFooterHier">&nbsp;</td>
								<td class="CategoryFooterHier" style="LINE-HEIGHT: 20px" valign="center" colspan="2">
									&nbsp;<asp:label id="CurrPage_Terr" runat="server"></asp:label>&nbsp;of&nbsp;<asp:label id="TotPages_Terr" runat="server"></asp:label></td>
							</tr>
						</table>
					</td>
					<td width="20"></td>
					<td width="225" valign="top">
						<br>
						<table id="ETTableColumn" runat="server" visible="false" cellspacing="0" cellpadding="0" width="225" border="0">
							<tr>
								<td class="CategoryHeaderHierGold" width="5">&nbsp;</td>
								<td class="CategoryHeaderHierGold" valign="top">Sales for '<asp:label id="ETHeader" runat="server"></asp:label>'
								</td>
								<td class="CategoryHeaderHierGold" width="5">&nbsp;</td>
							</tr>
							<tr>
								<td></td>
								<td><asp:datagrid id="EmployeeTerritoryGrid" runat="server" autogeneratecolumns="False" allowpaging="True" allowsorting="True" onsortcommand="SortGrid_EmpTerr" onpageindexchanged="Page_EmpTerr" gridlines="None" borderwidth="0px" pagesize="3">
										<itemstyle height="15px"></itemstyle>
										<headerstyle cssclass="ReportTitle"></headerstyle>
										<columns>
											<asp:templatecolumn sortexpression="EmployeeName" headertext="Employee Name">
												<itemtemplate>
													<asp:linkbutton oncommand="ETGrid_Click" commandargument='<%# (String) DataBinder.Eval(Container, "DataItem.EmployeeName") + "," + DataBinder.Eval(Container, "DataItem.EmployeeID").ToString() %>' cssclass="hier" runat="server" id="lnkEmployeeTerritory" text='<%# DataBinder.Eval(Container, "DataItem.EmployeeName") %>'>
													</asp:linkbutton>
												</itemtemplate>
												<itemstyle width="150px"></itemstyle>
											</asp:templatecolumn>
											<asp:boundcolumn datafield="SalesTotals" sortexpression="SalesTotals" headertext="Sales" dataformatstring="{0:c}">
												<headerstyle cssclass="ProductHeaderRight"></headerstyle>
												<itemstyle width="100px" cssclass="ItemStyleRight"></itemstyle>
											</asp:boundcolumn>
										</columns>
										<pagerstyle cssclass="PagerHier" prevpagetext="&lt; Prev" nextpagetext="Next &gt;" height="30px" verticalalign="middle"></pagerstyle>
									</asp:datagrid></td>
								<td></td>
							</tr>
							<tr height="10">
								<td class="CategoryFooterHier">&nbsp;</td>
								<td class="CategoryFooterHier" style="LINE-HEIGHT: 20px" valign="center" colspan="2"><asp:label id="CurrPage_EmpTerr" runat="server"></asp:label>of
									<asp:label id="TotPages_EmpTerr" runat="server"></asp:label>&nbsp;&nbsp;</td>
							</tr>
						</table>
					</td>
					<td width="20"></td>
					<td width="425" valign="top">
						<p>&nbsp;</p>
						<table runat="server" id="EmpTableColumn" visible="false" cellspacing="0" cellpadding="0" width="425" border="0">
							<tr>
								<td class="CategoryHeaderHierGold" width="5">&nbsp;</td>
								<td class="CategoryHeaderHierGold" valign="top">Employee Info for '<asp:label id="EmployeeName" runat="server"></asp:label>'
								</td>
								<td class="CategoryHeaderHierGold" width="5">&nbsp;</td>
							</tr>
							<tr>
								<td></td>
								<td><asp:datagrid id="EmployeeGrid" runat="server" autogeneratecolumns="False" gridlines="None" borderwidth="0px">
										<itemstyle height="15px"></itemstyle>
										<headerstyle cssclass="ReportTitle"></headerstyle>
										<columns>
											<asp:boundcolumn datafield="EmployeeID" headertext="ID">
												<itemstyle width="25px"></itemstyle>
											</asp:boundcolumn>
											<asp:boundcolumn datafield="EmployeeTitle" headertext="Title">
												<itemstyle width="140px"></itemstyle>
											</asp:boundcolumn>
											<asp:templatecolumn headertext="Address">
												<itemtemplate>
													<%# DataBinder.Eval(Container, "DataItem.EmployeeAddress") %>
													<br>
													<%# DataBinder.Eval(Container, "DataItem.EmployeeCity") %>
													,
													<%# DataBinder.Eval(Container, "DataItem.EmployeeRegion") %>
													&nbsp;&nbsp;<%# DataBinder.Eval(Container, "DataItem.EmployeePostalCode") %><br>
													<%# DataBinder.Eval(Container, "DataItem.EmployeeCountry") %>
												</itemtemplate>
												<itemstyle width="160px"></itemstyle>
											</asp:templatecolumn>
											<asp:boundcolumn datafield="EmployeePhone" headertext="Home Phone">
												<itemstyle width="100px"></itemstyle>
											</asp:boundcolumn>
										</columns>
										<pagerstyle visible="False"></pagerstyle>
									</asp:datagrid></td>
								<td></td>
							</tr>
						</table>
					</td>
					<td width="*"></td>
				</tr>
			</table>
			<p>&nbsp;</p>
		</form>
	</body>
</html>