PageType Usage Plugin with Recent Pages for each PageType
To get more insight in the production environment of the client, we needed some information on the usage of PageTypes and the number of pages for each PageType. Further, in case of troubleshooting or support we needed a way to quickly retrieve the URL of a page for a given PageType. The simplest approach was to build a small admin plugin that summarizes this information in an extensive but generic way without the need to look up or manually query for certain pages. This post will show you how the plugin was set up and what the end result looks like.
The main part of the plugin is a GuiPlugin that targets the PlugInArea.AdminMenu. I’ll show you how the page layout was setup and how the code behind looks like. Further we need a few helper classes that retrieve the actual data from the EPiServer database.
End result
Let’s start with the end result. I’ve used the AlloyTech templates for this demo. Below you’ll see two screenshots. The first displays the statistics for each PageType and the second displays the page URLs for each PageType.
Figure 1: Page Type statistics
Figure 2: Recent pages for each PageType
Page Layout
Now you’ve seen the end result, we’ll get into how this plugin is created. We’ll start with the page layout first.
1: <asp:Content ContentPlaceHolderID="FullRegion" runat="server">
2:
3: <EPiServerUI:TabStrip runat="server" id="actionTab" GeneratesPostBack="False" targetid="tabView">
4: <EPiServerUI:Tab Text="Page Type Usage" runat="server" ID="TabUsage" Sticky="true"/>
5: <EPiServerUI:Tab Text="Pages" runat="server" ID="TabPages" Sticky="true"/>
6: </EPiServerUI:TabStrip>
7:
8: <asp:Panel runat="server" id="tabView">
9: <asp:Panel runat="server" ID="Usage" CssClass="epi-contentContainer epi-padding">
10:
11: <h1>Page Type Usage</h1>
12: <asp:GridView ID="PageTypeGridView" runat="server"
13: AutoGenerateColumns="false" AllowSorting="true" AllowPaging="false"
14: Width="100%" OnSorting="PageTypeGridViewSorting">
15: <Columns>
16: <asp:BoundField DataField="PageTypeId" HeaderText="PageTypeId" SortExpression="PageTypeId" />
17: <asp:BoundField DataField="PageTypeName" HeaderText="PageTypeName" SortExpression="PageTypeName" />
18: <asp:BoundField DataField="CountPages" HeaderText="CountPages" SortExpression="CountPages" />
19: <asp:BoundField DataField="CountWorkPages" HeaderText="CountWorkPages" SortExpression="CountWorkPages" />
20: </Columns>
21: </asp:GridView>
22: </asp:Panel>
23:
24: <asp:Panel runat="server" ID="Pages" CssClass="epi-padding">
25: <h1>Pages by PageType</h1>
26: <asp:Literal runat="server" ID="PageUrlsLiteral" />
27: </asp:Panel>
28:
29: </asp:Panel>
30: </asp:Content>
The page layout consists of a TabStrip with two Tabs. We’ll use the first tab for the statistical part and the second tab for the page URLs part. In order to get the Tabs working we’ll need two Panels. The first panel, for our statistical part, uses a GridView with four columns for PageTypeId, PageTypeName, the number of pages, and the number of work pages. Of course the amount of information of each PageType could be extended, but for now this already provides some insight in the way PageTypes are being used in the production environment.
The second part lists the most recent pages for each PageType. We’ll construct the complete listing from code behind which will be places in the PageUrlsLiteral. Building html from a code behind of course is never the best approach, but it’ll do for now. I’ll get into the page URLs in more detail later on.
Plugin code behind
The second part is setting up our code behind for the GuiPlugin.
1: [GuiPlugIn(
2: DisplayName = "Page Type Usage",
3: Description = "Displays statistics and actual pages for each page type",
4: Area = PlugInArea.AdminMenu,
5: Url = "~/Business/PageTypeStats/PageTypeStatsPlugin.aspx")]
6: public partial class PageTypeStatsPlugin : SystemPageBase
7: {
8: private readonly EPiServerSqlHelper _ePiServerSqlHelper = new EPiServerSqlHelper();
9:
10: protected override void OnPreInit(EventArgs e)
11: {
12: base.OnPreInit(e);
13: // Set system masterpage
14: this.MasterPageFile = ResolveUrlFromUI("MasterPages/EPiServerUI.master");
15: }
16:
17: protected override void OnInit(EventArgs e)
18: {
19: if (!EPiServer.Security.PrincipalInfo.HasEditAccess)
20: {
21: Response.Write("You do not have access rights to this page");
22: Response.End();
23: }
24: }
25:
26: protected override void OnLoad(EventArgs e)
27: {
28: base.OnLoad(e);
29: BindPageTypes();
30: }
31:
32: protected void BindPageTypes()
33: {
34: List<PageTypeUsage> list = _ePiServerSqlHelper.GetPageTypeUsage();
35:
36: PageTypeGridView.DataSource = list;
37: PageTypeGridView.AllowPaging = false;
38: PageTypeGridView.DataBind();
39:
40: PageUrlsLiteral.Text = GetPageUrls();
41: }
42:
43: protected string GetPageUrls()
44: {
45: List<PageUrlByPageType> list = _ePiServerSqlHelper.GetPageUrlByPageType();
46:
47: const string colgroupFormat = "<colgroup><col /><col style='width:50px;' /><col style='width:50px;' /></colgroup>";
48: const string headerFormat = "<tr><th>{0}</th><th>{1}</th><th>{2}</th></tr>";
49: const string pageTypeFormat = "<tr style='background-color:#DEDEDE;'><td>{0}</td><td>{1}</td><td>{2}</td></tr>";
50: const string pageFormat = "<tr><td colspan='3'>{0}</td></tr>";
51:
52: StringBuilder sb = new StringBuilder();
53: sb.Append("<table cellspacing='0' border='0' style='border-style:None;width:100%;border-collapse:collapse;' class='epi-default epi-default-legacy'>");
54: sb.Append(colgroupFormat);
55: sb.AppendFormat(headerFormat, "Name", "PageTypeID", "CountPages");
56:
57: foreach (var pageUrlByPageType in list)
58: {
59: sb.AppendFormat(pageTypeFormat, pageUrlByPageType.PageTypeName,
60: pageUrlByPageType.PageTypeId, pageUrlByPageType.CountPages);
61:
62: foreach (var pageUrls in pageUrlByPageType.PageUrls)
63: {
64: sb.AppendFormat(pageFormat, pageUrls.PageUrl);
65: }
66: }
67: sb.Append("</table>");
68:
69: return sb.ToString();
70: }
71: }
There is probably more in depth information available on how to set up a GuiPlugin, so I won’t be covering all the basics here but I’ll try to keep this short. Limited to five steps actually.
- First you add the GuiPlugin attribute on the class.
- In the OnPreInit method you assign the correct masterpage.
- In the OnInit method we’ll limit access to the plugin to edit rights.
- The BindPageTypes method is called from the OnLoad method and retrieves the data and assigns the data to the GridView.
- The GetPageUrls method retrieves the data for our second tab, the actual URLs for the pages. This retrieves a collection, iterates over the results and build the html for our list which is then assigned to our literal placeholder.
While our GuiPlugin has been set up with these five steps, we still need to cover the actual retrieval of the data required for the PageType information we want to expose.
Query the database
There are three basic queries needed to retrieve all the information needed. The three queries are mentioned below.
1: private const string SqlPageTypeUsage = @"SELECT pt.pkID AS Id, pt.Name AS Name,
2: pt.Filename AS FileName, COUNT(p.pkID) AS PageCount
3: FROM tblPage AS p RIGHT OUTER JOIN tblPageType AS pt ON p.fkPageTypeID = pt.pkID
4: GROUP BY pt.Name, pt.Filename, pt.SortOrder, pt.pkID
5: ORDER BY pt.Name, pt.SortOrder";
6:
7: private const string SqlCountWorkPages = @"SELECT COUNT(*) AS PageCount FROM tblWorkPage
8: INNER JOIN tblPage ON tblWorkPage.fkPageID = tblPage.pkID
9: WHERE (tblPage.fkPageTypeID = {0})";
10:
11: private const string SqlTop10PagesByPageType = @"SELECT TOP 10 [pkID] AS Id
12: FROM tblPage WHERE Deleted='0' AND PendingPublish='0' AND fkPageTypeID = '{0}'
13: ORDER BY pkID DESC";
14:
15:
The first query ‘SqlPageTypeUsage’ selects the information from tblPage and tblPageType and groups this on PageType. At this point we have most of the information on the first tab, the statistical information.
Further the information is extended with the number of workPages. The second query ‘SqlCountWorkPages’ will retrieve this information from tblWorkPage.
The third query ‘SqlTop10PagesByPageType’ retrieves the top 10 pages from tblPage on the given PageType. I’ve ordered this on the Page ID, since the most recent pages will have the newest ID. Of course you could order it on change date or publish date of the page version, but at this point ordering on ID gives us a good enough insight in the recent pages.
Domain objects
Next we defined three simple objects to hold the data which we can use in our plugin. These are PageTypeUsage which will hold the statistical tab, PageUrlByPageType and PageUrls to hold the recent pages for each PageType.
1: public class PageTypeUsage
2: {
3: public string PageTypeName { get; set; }
4: public int PageTypeId { get; set; }
5: public string FileName { get; set; }
6: public int CountPages { get; set; }
7: public int CountWorkPages { get; set; }
8: }
9:
10: public class PageUrlByPageType
11: {
12: public string PageTypeName { get; set; }
13: public int PageTypeId { get; set; }
14: public int CountPages { get; set; }
15: public List<PageUrls> PageUrls { get; set; }
16: }
17:
18: public class PageUrls
19: {
20: public int PageId { get; set; }
21: public string PageUrl { get; set; }
22: }
23:
Retrieve the data
The next part is the method GetPageTypeUsage which executes the SQL statement and parses en returns the results. We check if the PageType has a filename and exclude the sysrecylebin and sysroot. Further we call the SQL statement to count the number of WorkPages.
1: /// <summary>
2: /// Gets the usage of pagetypes.
3: /// </summary>
4: /// <returns></returns>
5: public List<PageTypeUsage> GetPageTypeUsage()
6: {
7: List<PageTypeUsage> results = new List<PageTypeUsage>();
8: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString);
9: try
10: {
11: SqlCommand cmd = conn.CreateCommand();
12: cmd.CommandType = CommandType.Text;
13: cmd.CommandText = SqlPageTypeUsage;
14: conn.Open();
15:
16: using (IDataReader dataReader = cmd.ExecuteReader())
17: {
18: while (dataReader.Read())
19: {
20: int id;
21: if (!int.TryParse(dataReader["Id"].ToString(), out id))
22: {
23: Logger.Error("Error parsing Id");
24: continue;
25: }
26:
27: int pageCount;
28: if (!int.TryParse(dataReader["PageCount"].ToString(), out pageCount))
29: {
30: Logger.Error("Error parsing PageCount");
31: continue;
32: }
33:
34: if (dataReader["FileName"] == null)
35: {
36: Logger.Error("Error parsing FileName");
37: continue;
38: }
39:
40: if (dataReader["name"].ToString().ToLower() == "sysrecyclebin"
41: || dataReader["name"].ToString().ToLower() == "sysroot")
42: {
43: continue;
44: }
45:
46: PageTypeUsage pageTypeUsage = new PageTypeUsage
47: {
48: PageTypeId = id,
49: PageTypeName = (string)dataReader["Name"],
50: FileName = (string)dataReader["FileName"],
51: CountPages = pageCount,
52: CountWorkPages = GetNumberOfPagesForPageTypeFromDatabase(id, SqlCountWorkPages)
53: };
54:
55: results.Add(pageTypeUsage);
56: }
57: }
58: }
59: catch (Exception ex)
60: {
61: const string errorMessage = "Error occured retrieving pagetype usage";
62: Logger.Error(errorMessage);
63: throw new Exception(errorMessage, ex);
64: }
65: finally
66: {
67: conn.Close();
68: }
69:
70: return results;
71: }
72:
73: /// <summary>
74: /// Gets the number of pages a for page type from database.
75: /// </summary>
76: /// <param name="pageTypeId">Page type id to count pages for</param>
77: /// <param name="query">Query to run against database</param>
78: /// <returns></returns>
79: private static int GetNumberOfPagesForPageTypeFromDatabase(int pageTypeId, string query)
80: {
81: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString);
82: SqlCommand cmd = conn.CreateCommand();
83: cmd.CommandType = CommandType.Text;
84: cmd.CommandText = string.Format(query, pageTypeId);
85: conn.Open();
86: object pageCount = cmd.ExecuteScalar();
87: conn.Close();
88:
89: return (int)pageCount;
90: }
91:
Finally to retrieve the pages for each PageType we also execute the SQL statement and return the results. The first is similar to the above code when retrieving the PageTypes. For each PageType we call the GetTop10PagesByPageType method that collects the recent pages for the PageType. For each retrieved Page we also need to construct the absolute URL. In the method ‘GetUrlFromPageId’ we take in a page id and convert that to the absolute URL of the page.
1: public List<PageUrlByPageType> GetPageUrlByPageType()
2: {
3: List<PageUrlByPageType> results = new List<PageUrlByPageType>();
4: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString);
5: try
6: {
7: SqlCommand cmd = conn.CreateCommand();
8: cmd.CommandType = CommandType.Text;
9: cmd.CommandText = SqlPageTypeUsage;
10: conn.Open();
11:
12: using (IDataReader dataReader = cmd.ExecuteReader())
13: {
14: while (dataReader.Read())
15: {
16: int id;
17: if (!int.TryParse(dataReader["Id"].ToString(), out id))
18: {
19: Logger.Error("Error parsing Id");
20: continue;
21: }
22:
23: int pageCount;
24: if (!int.TryParse(dataReader["PageCount"].ToString(), out pageCount))
25: {
26: Logger.Error("Error parsing PageCount");
27: continue;
28: }
29:
30: if (dataReader["name"] == null)
31: {
32: Logger.Error("Error parsing Name");
33: continue;
34: }
35:
36: if (dataReader["name"].ToString().ToLower() == "sysrecyclebin"
37: || dataReader["name"].ToString().ToLower() == "sysroot")
38: {
39: continue;
40: }
41:
42: PageUrlByPageType pageUrlByPageType = new PageUrlByPageType
43: {
44: PageTypeId = id,
45: PageTypeName = (string)dataReader["name"],
46: CountPages = pageCount,
47: PageUrls = GetTop10PagesByPageType(id)
48: };
49:
50: results.Add(pageUrlByPageType);
51: }
52: }
53: }
54: catch (Exception ex)
55: {
56: const string errorMessage = "Error occured retrieving pagetype usage";
57: Logger.Error(errorMessage);
58: throw new Exception(errorMessage, ex);
59: }
60: finally
61: {
62: conn.Close();
63: }
64:
65: return results;
66: }
67:
68: /// <summary>
69: /// Gets the top 10 pages for a pagetype
70: /// </summary>
71: /// <returns></returns>
72: public List<PageUrls> GetTop10PagesByPageType(int pageTypeId)
73: {
74: List<PageUrls> results = new List<PageUrls>();
75: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString);
76: try
77: {
78: SqlCommand cmd = conn.CreateCommand();
79: cmd.CommandType = CommandType.Text;
80: cmd.CommandText = string.Format(SqlTop10PagesByPageType, pageTypeId);
81: conn.Open();
82:
83: using (IDataReader dataReader = cmd.ExecuteReader())
84: {
85: while (dataReader.Read())
86: {
87: int id;
88: if (!int.TryParse(dataReader["Id"].ToString(), out id))
89: {
90: Logger.Error("Error parsing Id");
91: continue;
92: }
93:
94: string url = GetUrlFromPageId(id);
95: if (string.IsNullOrEmpty(url))
96: {
97: continue;
98: }
99: PageUrls pageUrls = new PageUrls
100: {
101: PageId = id,
102: PageUrl = url
103: };
104:
105: results.Add(pageUrls);
106: }
107: }
108: }
109: catch (Exception ex)
110: {
111: const string errorMessage = "Error occured retrieving Top10PagesByPageType";
112: Logger.Error(errorMessage);
113: throw new Exception(errorMessage, ex);
114: }
115: finally
116: {
117: conn.Close();
118: }
119:
120: return results;
121: }
122:
123: protected string GetUrlFromPageId(int id)
124: {
125: PageData page = DataFactory.Instance.GetPage(new PageReference(id));
126: if (page.LinkType == PageShortcutType.External)
127: {
128: return null;
129: }
130:
131: UrlBuilder ub = new UrlBuilder(page.LinkURL);
132:
133: EPiServer.Web.FriendlyUrlRewriteProvider urm = new EPiServer.Web.FriendlyUrlRewriteProvider();
134: urm.ConvertToExternal(ub, page.PageLink, Encoding.UTF8);
135:
136: string siteUrl = EPiServer.Configuration.Settings.Instance.SiteUrl.AbsoluteUri;
137: if (siteUrl.EndsWith("/"))
138: {
139: siteUrl = siteUrl.TrimEnd('/');
140: }
141: return string.Concat(siteUrl, ub.Uri.ToString());
142: }
143:
Conclusion
That’s it. In the above post I’ve explained the use of the plugin and how you can develop it. This plugin has proven to be very useful in support for any client question on pages, and for debugging purposes as well. This plugin provides a good insight in the way your editors are using the defined PageTypes and how they use them to create pages. Hopefully this post has helped you to understand the basics and explained the use of it in everyday production environments.
Great plugin. Would be nice to expand/collape in "Pages" tab and also open particular page type settings and page instance preview mode :)