/**
* Gets all the data connection names in the application specified by its appid
*
* @param lAppID
* @return a Vector of database connection names stored in the Vector as Strings
*/
public Vector getAllDataConnectionNames(long lAppID) {
Vector vReturn = new Vector();
Connection cxSys = null;
Statement stmt = null;
ResultSet rs = null;
m_SysCtx.doDebug(pmaLog.DEBUGLEVEL_FULL, "getAllDataConnectionNames(appid)", this);
try {
String szQuery =
"SELECT DBConnectionName FROM APPLICATION,DBCONNECTION WHERE APPLICATION.AppID="
+ lAppID
+ " AND APPLICATION.AppID=DBCONNECTION.AppID";
cxSys = m_SysCtx.getSystemConnection();
stmt = cxSys.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(szQuery);
while (rs.next()) {
String sConnName = rs.getString(1);
if (sConnName != null && sConnName.length() > 0) vReturn.add(sConnName);
}
} catch (Exception de) {
m_SysCtx.doError("pmaSystem.getDataConnectionError", new String[] {de.toString()}, m_SessCtx);
} finally {
Util.closeJDBC(rs);
Util.closeJDBC(stmt);
m_SysCtx.releaseSystemConnection(cxSys);
}
return vReturn;
}
/**
* Gets all keywords matching sKey (case insensitive).
*
* @param sKey the keyword name
* @param bSortByValue Pass true to have the results put in the vector in sorted Data order.
* @return null if no match
*/
public Vector getAllKeywordValues(String sKey, boolean bSortByValue) {
Vector vReturn = null;
Connection cxSys = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sAppGroup = m_rPath.Group, sAppName = m_rPath.Application;
String sOrderClause = " ORDER BY KeywordOrder,Data";
// m_SysCtx.doDebug(pmaLog.DEBUGLEVEL_FULL, "getAllKeywordValues()", m_SessCtx);
if (sKey == null || sKey.length() == 0) return null;
if (sAppName == null || sAppName.length() == 0) return null;
boolean bHasGroup = true;
if (sAppGroup == null || sAppGroup.length() == 0) bHasGroup = false;
if (bSortByValue) sOrderClause = " ORDER BY Data";
String sQuery =
"SELECT Data FROM APPLICATION,KEYWORD,KEYWORDDATA WHERE UPPER(APPLICATION.AppName)=? AND (APPLICATION.AppGroup='' OR APPLICATION.AppGroup IS NULL) AND APPLICATION.AppID=KEYWORD.AppID AND KEYWORD.KeywordID=KEYWORDDATA.KeywordID AND UPPER(KEYWORD.Name)=?"
+ sOrderClause;
if (bHasGroup) {
sQuery =
"SELECT Data FROM APPLICATION,KEYWORD,KEYWORDDATA WHERE UPPER(APPLICATION.AppName)=? AND (UPPER(APPLICATION.AppGroup)=? OR APPLICATION.AppGroup='*') AND APPLICATION.AppID=KEYWORD.AppID AND KEYWORD.KeywordID=KEYWORDDATA.KeywordID AND UPPER(KEYWORD.Name)=?"
+ sOrderClause;
}
try {
// System.out.println("[" + szQuery + "]");
cxSys = m_SysCtx.getSystemConnection();
stmt =
cxSys.prepareStatement(sQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, sAppName.toUpperCase());
if (bHasGroup) {
stmt.setString(2, sAppGroup.toUpperCase());
stmt.setString(3, sKey.toUpperCase());
} else stmt.setString(2, sKey.toUpperCase());
rs = stmt.executeQuery();
while (rs.next()) {
if (vReturn == null) vReturn = new Vector();
vReturn.add(rs.getString("Data"));
}
rs.close();
stmt.close();
} catch (Exception de) {
m_SysCtx.doError(
"HTTPRequest.getKeywordError", new String[] {sKey, de.toString()}, m_SessCtx);
} finally {
Util.closeJDBC(rs);
Util.closeJDBC(stmt);
m_SysCtx.releaseSystemConnection(cxSys);
}
return vReturn;
}
/**
* Gets the first keyword matching szKey (case insensitive). Note that if there are multiple
* keywords, there is no guarantee which will be returned.
*
* @param sKey
* @return null if no match
*/
public String getKeywordValue(String sKey) {
Connection cxSys = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sReturn = null;
String sAppGroup = m_rPath.Group, sAppName = m_rPath.Application;
m_SysCtx.doDebug(pmaLog.DEBUGLEVEL_FULL, "getKeywordValue()", m_SessCtx);
if (sAppName == null || sAppName.length() == 0) return null;
boolean bHasGroup = true;
if (sAppGroup == null || sAppGroup.length() == 0) bHasGroup = false;
String sQuery =
"SELECT Data FROM APPLICATION,KEYWORD,KEYWORDDATA WHERE UPPER(APPLICATION.AppName)=? AND (APPLICATION.AppGroup='' OR APPLICATION.AppGroup IS NULL) AND APPLICATION.AppID=KEYWORD.AppID AND KEYWORD.KeywordID=KEYWORDDATA.KeywordID AND UPPER(KEYWORD.Name)=?";
if (bHasGroup) {
sQuery =
"SELECT Data FROM APPLICATION,KEYWORD,KEYWORDDATA WHERE UPPER(APPLICATION.AppName)=? AND (UPPER(APPLICATION.AppGroup)=? OR APPLICATION.AppGroup='*') AND APPLICATION.AppID=KEYWORD.AppID AND KEYWORD.KeywordID=KEYWORDDATA.KeywordID AND UPPER(KEYWORD.Name)=?";
}
try {
cxSys = m_SysCtx.getSystemConnection();
stmt =
cxSys.prepareStatement(sQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, sAppName.toUpperCase());
if (bHasGroup) {
stmt.setString(2, sAppGroup.toUpperCase());
stmt.setString(3, sKey.toUpperCase());
} else stmt.setString(2, sKey.toUpperCase());
rs = stmt.executeQuery();
if (rs.next()) {
sReturn = rs.getString(1);
}
} catch (Exception de) {
m_SysCtx.doError(
"HTTPRequest.getKeywordError", new String[] {sKey, de.toString()}, m_SessCtx);
} finally {
Util.closeJDBC(rs);
Util.closeJDBC(stmt);
m_SysCtx.releaseSystemConnection(cxSys);
}
return sReturn;
}
/**
* Stores a new xsl resource based on the column names and design name
*
* @param sDesignName
* @param sColumnNames
* @return
*/
private boolean saveXSLDesignElement(String sDesignName, String sColumnNames[]) {
if (sDesignName == null
|| sDesignName.length() == 0
|| sColumnNames == null
|| sColumnNames.length == 0) {
m_SysCtx.doError("HTTPSessionContext.NoXML", new String[] {sDesignName}, this);
return false;
}
boolean bOK = true;
Connection cx = null;
PreparedStatement prepStmt = null;
String sQuery;
StringBuilder sbXSL = new StringBuilder(4096);
sbXSL.append("<?xml version=\"1.0\"?>\r\n");
sbXSL.append(
"<xsl:stylesheet xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" version=\"1.0\">\r\n\r\n");
sbXSL.append("<xsl:output method=\"html\" indent=\"yes\"/>\r\n");
sbXSL.append("<xsl:template match=\"/\">\r\n");
sbXSL.append("\t<xsl:apply-templates/>\r\n");
sbXSL.append("</xsl:template>\r\n\r\n");
sbXSL.append("<xsl:template match=\"data\">\r\n");
sbXSL.append("<table class=\"listTable\">\r\n");
sbXSL.append("<thead>\r\n");
sbXSL.append("<tr>\r\n");
sbXSL.append("\t<th> </th>\r\n");
// for each column, show the header
for (int i = 0; i < sColumnNames.length; i++) {
sbXSL.append("\t<th>");
sbXSL.append(sColumnNames[i]);
sbXSL.append("</th>\r\n");
}
sbXSL.append("</tr>\r\n");
sbXSL.append("</thead>\r\n\r\n");
sbXSL.append("<tbody>\r\n");
sbXSL.append("<xsl:for-each select=\"row\">\r\n");
sbXSL.append("<tr>\r\n");
sbXSL.append(
"\r\n\t<!-- Decimals: <xsl:value-of select=\"format-number(item[@name='numbercolumn']/value, '###,###.00')\" /> -->\r\n");
sbXSL.append(
"\t<!-- Totals: <xsl:value-of select=\"format-number(sum(/data/row/item[@name='numbercolumn']/value), '###,###.00')\" /> -->\r\n");
sbXSL.append(
"\t<!-- Dates: <xsl:value-of select=\"item[@name='datecolumn']/value/year\" /> (year,month,day,hour,minute,second) -->\r\n\r\n");
// show an example of a delete column
sbXSL.append("\t<td>\r\n");
sbXSL.append(
"\t\t<a><xsl:attribute name=\"href\">JavaScript:deleteRecord('<xsl:value-of select=\"item[@name='columnid']/value\"/>');</xsl:attribute>\r\n");
sbXSL.append(
"\t\t<img border=\"0\" alt=\"Delete this record\"><xsl:attribute name=\"src\"><xsl:value-of select=\"/data/@path\"/>/recyclebin.gif?OpenResource</xsl:attribute></img>\r\n");
sbXSL.append("\t\t</a>\r\n");
sbXSL.append("\t</td>\r\n");
// for each column
for (int i = 0; i < sColumnNames.length; i++) {
sbXSL.append("\t<td>\r\n");
if (i == 0)
sbXSL.append(
"\t\t<a><xsl:attribute name=\"href\"><xsl:value-of select=\"/data/@path\"/><xsl:text>/EditPageName?OpenPage&id=</xsl:text><xsl:value-of select=\"item[@name='idcolumn']/value\"/></xsl:attribute>\r\n");
sbXSL.append(
"\t\t<xsl:value-of select=\"item[@name='"
+ sColumnNames[i].toLowerCase()
+ "']/value\"/>\r\n");
if (i == 0) sbXSL.append("\t\t</a>\r\n");
sbXSL.append("\t</td>\r\n");
}
sbXSL.append("</tr>\r\n");
sbXSL.append("</xsl:for-each>\r\n");
sbXSL.append("</tbody>\r\n\r\n");
sbXSL.append("<!-- sum a column or count all the rows\r\n");
sbXSL.append("<tr>\r\n");
sbXSL.append(
"\t<td>$<xsl:value-of select=\"format-number(sum(/data/row/item[@name='numbercolumn']/value), '###,###.00')\"/></td>\r\n");
sbXSL.append("\t<td><xsl:value-of select=\"count(/data/row)\"/></td>\r\n");
sbXSL.append("</tr>\r\n");
sbXSL.append("-->\r\n\r\n");
sbXSL.append("</table>\r\n");
sbXSL.append("</xsl:template>\r\n");
sbXSL.append("</xsl:stylesheet>\r\n");
TornadoServerInstance tsi = TornadoServer.getInstance(m_SysCtx);
try {
cx = m_SysCtx.getSystemConnection();
sQuery =
"INSERT INTO DESIGNBUCKET(AppID,Name,DesignType,ContentType,Updated,UpdatedBy,Comment,Options,InheritFrom,DesignData) VALUES(?,?,?,?,?,?,?,?,?,?)";
prepStmt = cx.prepareStatement(sQuery);
long lAppID =
tsi.getApplicationID(
m_rPath.Group,
m_rPath.Application); // HTTPServer.getAppID(m_SysCtx, m_rPath.Application,
// m_rPath.Group);
if (lAppID > 0) {
prepStmt.setLong(1, lAppID);
prepStmt.setString(2, sDesignName);
prepStmt.setInt(3, DesignElement.DESIGN_TYPE_RESOURCE); // designtype
// if(docDesign.getItemValue("Type").equals(String.valueOf(DesignElement.DESIGN_TYPE_PAGE)))
// szMimeType = "text/html";
prepStmt.setString(4, "text/xml"); // contenttype
prepStmt.setTimestamp(5, new Timestamp((new java.util.Date()).getTime())); // updated
prepStmt.setString(6, pmaSystem.SYSTEM_ACCOUNT); // updatedby
prepStmt.setString(7, ""); // comment
prepStmt.setString(8, "");
prepStmt.setString(9, ""); // inherit
prepStmt.setBinaryStream(
10,
new ByteArrayInputStream(sbXSL.toString().getBytes()),
(int) sbXSL.length()); // designdata
prepStmt.execute();
} else bOK = false;
} catch (Exception sqle) {
m_SysCtx.doError(sqle.getMessage(), m_SessCtx);
} finally {
Util.closeJDBC(prepStmt);
m_SysCtx.releaseSystemConnection(cx);
}
return bOK;
}