Perl Cd Bookshelf [Electronic resources] نسخه متنی

Creating an Administration Page

The user administration page needs to be able to list the users and their status as well as provide a method to retrieve a specific user record and update it. Figure 15-4 shows a listing of users and their status retrieved by passing users as a pagename parameter to index.xsql linked from Approve Application.

Figure 15-4: Listing of XDK users and their status

This users.xsql page uses the same paging techniques as discussed earlier but executes the following query:

<xsql:query skip-rows="{@paging-skip}" max-rows="{@paging-max}">
<![CDATA[
SELECT user_id, first_name,last_name, role,status,last_visit,
TO_CHAR(register_date,'Month DD,YYYY') as register_date,email
FROM users
ORDER BY status desc, first_name
]]>
</xsql:query>

The results are returned in XML to be transformed into a list with user record links as follows:

<xsl:template name ="news_content" match="/">
<page title="XDK News Updates">
<table width="100%" border="0">
<tr> 
<td width="10" align="right" valign="top">
<img src="/image/library/english/10158_d_arrow.gif" width="10" height="10"/>
</td>
<td> 
<b>XDK Users:</b>
</td>
<td> <xsl:apply-templates select="page/paging"/>
</td>
</tr>
<xsl:for-each select="page/ROWSET/ROW">
<tr> 
<td width="10" align="right" valign="top">
<img src="/image/library/english/10158_r_arrow.gif" width="10" height="10"/>
</td>
<td colspan="2" width="600" align="left" class="fbox">
<a href=" table=USERS&amp;updateid={USER_ID}&amp;u
datecolmn=user_id" class="fbox">
<xsl:value-of select="FIRST_NAME"/>,
<xsl:value-of select="LAST_NAME"/></a>
(<xsl:value-of select="ROLE"/>):<xsl:value-of select="EMAIL"/>
<xsl:text> (</xsl:text><xsl:value-of
select="STATUS"/><xsl:text>)</xsl:text>
</td>
</tr>
<tr> 
<td width="10"><xsl:text> </xsl:text></td>
<td colspan="2" width="600" class="footnt">
Registered on:<xsl:value-of select="REGISTER_DATE"/>.
<xsl:value-of select="COMMENTS"/>
</td>
</tr>
</xsl:for-each>
</table>
</page>
</xsl:template>
</xsl:stylesheet>

After this list is retrieved, the administrator can then select a pending application and be presented with a new form that has all the user information ready for update, as shown in Figure 15-5.

Figure 15-5: Update form for users

This form is retrieved by the following link calling the generateForm.xsql page:

http://localhost:8988/xdkus/admin/index.xsql?pagename=generateForm
&table=USERS&updateid=440&updatecolmn=user_id

This link demonstrates the ability to pass in parameters at the same time you are passing in a pagename value. The form created by this page is as follows:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="mozilla" href="?>
<page title="Insert New XDK Release" connection="xdkadmin"
xmlns:xsql="urn:oracle-xsql">
<xsql:include-param name="table" value="{@table}"/>
<xsql:include-param name="updateid" value="{@updateid}"/>
<xsql:include-param name="updatecolmn" value="{@updatecolmn}"/>
<xsql:query>select column_name as name, data_type as type, data_length as 
length,
NULLABLE
from user_tab_columns
where table_name=upper('{@table}')
</xsql:query>
<xsql:if-param name="updateid" exists="yes">
<data>
<xsql:query date-format="MM/dd/yyyy">
select * from {@table}
where {@updatecolmn}='{@updateid}'
</xsql:query>
</data>
</xsql:if-param>
</page>

Note that this page uses a different database connection alias, xdkadmin, because you need to have administrative database privileges to perform updates and maintenance. The query also returns an XML document that is transformed to HTML by generateForm.xsl in the same way as the user application form, with the distinction that the fields are populated with the results of the query. This allows the administrator to assign a username and a password.

Once again, the form submission can not only insert the data but also deliver an e-mail to the user that his application was accepted and include the login information using an INSERT trigger.