long dCountRec(java.sql.Statement stat, String table, String sWhere) {
long lNumRecs = 0;
try {
java.sql.ResultSet rs =
stat.executeQuery("select count(*) from " + table + " where " + sWhere);
if (rs != null && rs.next()) {
lNumRecs = rs.getLong(1);
}
rs.close();
} catch (Exception e) {
}
;
return lNumRecs;
}
String[] getFieldsName(java.sql.ResultSet rs) throws java.sql.SQLException {
java.sql.ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();
String[] aFields = new String[count];
for (int j = 0; j < count; j++) {
aFields[j] = metaData.getColumnLabel(j + 1);
}
return aFields;
}
String getValue(java.sql.ResultSet rs, String strFieldName) {
if ((rs == null) || (isEmpty(strFieldName)) || ("".equals(strFieldName))) return "";
try {
String sValue = rs.getString(strFieldName);
if (sValue == null) sValue = "";
return sValue;
} catch (Exception e) {
return "";
}
}
String getValueHTML(java.sql.ResultSet rs, String fieldName) {
try {
String value = rs.getString(fieldName);
if (value != null) {
return toHTML(value);
}
} catch (java.sql.SQLException sqle) {
}
return "";
}
String dLookUp(java.sql.Statement stat, String table, String fName, String where) {
java.sql.Connection conn1 = null;
java.sql.Statement stat1 = null;
try {
conn1 = cn();
stat1 = conn1.createStatement();
java.sql.ResultSet rsLookUp =
openrs(stat1, "SELECT " + fName + " FROM " + table + " WHERE " + where);
if (!rsLookUp.next()) {
rsLookUp.close();
stat1.close();
conn1.close();
return "";
}
String res = rsLookUp.getString(1);
rsLookUp.close();
stat1.close();
conn1.close();
return (res == null ? "" : res);
} catch (Exception e) {
return "";
}
}
String getOptions(
java.sql.Connection conn,
String sql,
boolean isSearch,
boolean isRequired,
String selectedValue) {
String sOptions = "";
String sSel = "";
if (isSearch) {
sOptions += "<option value=\"\">All</option>";
} else {
if (!isRequired) {
sOptions += "<option value=\"\"></option>";
}
}
try {
java.sql.Statement stat = conn.createStatement();
java.sql.ResultSet rs = null;
rs = openrs(stat, sql);
while (rs.next()) {
String id = toHTML(rs.getString(1));
String val = toHTML(rs.getString(2));
if (id.compareTo(selectedValue) == 0) {
sSel = "SELECTED";
} else {
sSel = "";
}
sOptions += "<option value=\"" + id + "\" " + sSel + ">" + val + "</option>";
}
rs.close();
stat.close();
} catch (Exception e) {
}
return sOptions;
}
public static void absolute(java.sql.ResultSet rs, int row) throws java.sql.SQLException {
for (int x = 1; x < row; x++) rs.next();
}
void editorials_Show(
javax.servlet.http.HttpServletRequest request,
javax.servlet.http.HttpServletResponse response,
javax.servlet.http.HttpSession session,
javax.servlet.jsp.JspWriter out,
String seditorialsErr,
String sForm,
String sAction,
java.sql.Connection conn,
java.sql.Statement stat)
throws java.io.IOException {
String sWhere = "";
int iCounter = 0;
int iPage = 0;
boolean bIsScroll = true;
boolean hasParam = false;
String sOrder = "";
String sSQL = "";
String transitParams = "";
String sQueryString = "";
String sPage = "";
int RecordsPerPage = 20;
String sSortParams = "";
String formParams = "";
// Build WHERE statement
// Build ORDER statement
sOrder = " order by e.article_title Asc";
String sSort = getParam(request, "Formeditorials_Sorting");
String sSorted = getParam(request, "Formeditorials_Sorted");
String sDirection = "";
String sForm_Sorting = "";
int iSort = 0;
try {
iSort = SymString.parseInt(sSort);
} catch (NumberFormatException e) {
sSort = "";
}
if (iSort == 0) {
sForm_Sorting = "";
} else {
if (sSort.equals(sSorted)) {
sSorted = "0";
sForm_Sorting = "";
sDirection = " DESC";
sSortParams = "Formeditorials_Sorting=" + sSort + "&Formeditorials_Sorted=" + sSort + "&";
} else {
sSorted = sSort;
sForm_Sorting = sSort;
sDirection = " ASC";
sSortParams = "Formeditorials_Sorting=" + sSort + "&Formeditorials_Sorted=" + "&";
}
if (iSort == 1) {
sOrder = " order by e.article_title" + sDirection;
}
if (iSort == 2) {
sOrder = " order by e1.editorial_cat_name" + sDirection;
}
if (iSort == 3) {
sOrder = " order by i.name" + sDirection;
}
}
// Build full SQL statement
sSQL =
"select e.article_id as e_article_id, "
+ "e.article_title as e_article_title, "
+ "e.editorial_cat_id as e_editorial_cat_id, "
+ "e.item_id as e_item_id, "
+ "e1.editorial_cat_id as e1_editorial_cat_id, "
+ "e1.editorial_cat_name as e1_editorial_cat_name, "
+ "i.item_id as i_item_id, "
+ "i.name as i_name "
+ " from editorials e, editorial_categories e1, items i"
+ " where e1.editorial_cat_id=e.editorial_cat_id and i.item_id=e.item_id ";
sSQL = sSQL + sWhere + sOrder;
String sNoRecords =
" <tr>\n <td colspan=\"3\" style=\"background-color: #FFFFFF; border-width: 1\"><font style=\"font-size: 10pt; color: #000000\">No records</font></td>\n </tr>";
String tableHeader = "";
tableHeader =
" <tr>\n <td style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><a href=\""
+ sFileName
+ "?"
+ formParams
+ "Formeditorials_Sorting=1&Formeditorials_Sorted="
+ sSorted
+ "&\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Title</font></a></td>\n <td style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><a href=\""
+ sFileName
+ "?"
+ formParams
+ "Formeditorials_Sorting=2&Formeditorials_Sorted="
+ sSorted
+ "&\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Editorial Category</font></a></td>\n <td style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><a href=\""
+ sFileName
+ "?"
+ formParams
+ "Formeditorials_Sorting=3&Formeditorials_Sorted="
+ sSorted
+ "&\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Item</font></a></td>\n </tr>";
try {
out.println(" <table style=\"\">");
out.println(
" <tr>\n <td style=\"background-color: #336699; text-align: Center; border-style: outset; border-width: 1\" colspan=\"3\"><a name=\"editorials\"><font style=\"font-size: 12pt; color: #FFFFFF; font-weight: bold\">Editorials</font></a></td>\n </tr>");
out.println(tableHeader);
} catch (Exception e) {
}
try {
// Select current page
iPage = SymString.parseInt(getParam(request, "Formeditorials_Page"));
} catch (NumberFormatException e) {
iPage = 0;
}
if (iPage == 0) {
iPage = 1;
}
RecordsPerPage = 20;
try {
java.sql.ResultSet rs = null;
// Open recordset
rs = openrs(stat, sSQL);
iCounter = 0;
absolute(rs, (iPage - 1) * RecordsPerPage + 1);
java.util.Hashtable rsHash = new java.util.Hashtable();
String[] aFields = getFieldsName(rs);
// Show main table based on recordset
while ((iCounter < RecordsPerPage) && rs.next()) {
getRecordToHash(rs, rsHash, aFields);
String fldarticle_id = (String) rsHash.get("e_article_id");
String fldarticle_title = (String) rsHash.get("e_article_title");
String fldeditorial_cat_id = (String) rsHash.get("e1_editorial_cat_name");
String flditem_id = (String) rsHash.get("i_name");
out.println(" <tr>");
out.print(" <td style=\"background-color: #FFFFFF; border-width: 1\">");
out.print(
"<a href=\"EditorialsRecord.jsp?"
+ transitParams
+ "article_id="
+ toURL((String) rsHash.get("e_article_id"))
+ "&\"><font style=\"font-size: 10pt; color: #000000\">"
+ toHTML(fldarticle_title)
+ "</font></a>");
out.println("</td>");
out.print(" <td style=\"background-color: #FFFFFF; border-width: 1\">");
out.print(
"<font style=\"font-size: 10pt; color: #000000\">"
+ toHTML(fldeditorial_cat_id)
+ " </font>");
out.println("</td>");
out.print(" <td style=\"background-color: #FFFFFF; border-width: 1\">");
out.print(
"<font style=\"font-size: 10pt; color: #000000\">"
+ toHTML(flditem_id)
+ " </font>");
out.println("</td>");
out.println(" </tr>");
iCounter++;
}
if (iCounter == 0) {
// Recordset is empty
out.println(sNoRecords);
out.print(
" <tr>\n <td colspan=\"3\" style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">");
out.print(
"<a href=\"EditorialsRecord.jsp?"
+ formParams
+ "\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Insert</font></a> ");
out.println("</td>\n </tr>");
iCounter = RecordsPerPage + 1;
bIsScroll = false;
} else {
// Parse scroller
boolean bInsert = false;
boolean bNext = rs.next();
if (!bNext && iPage == 1) {
out.print(
" <tr>\n <td colspan=\"3\" style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\">\n <font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">");
out.print(
"\n <a href=\"EditorialsRecord.jsp?"
+ formParams
+ "\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Insert</font></a> ");
out.println("\n </td>\n </tr>");
} else {
out.print(
" <tr>\n <td colspan=\"3\" style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">");
out.print(
"\n <a href=\"EditorialsRecord.jsp?"
+ formParams
+ "\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Insert</font></a> ");
bInsert = true;
if (iPage == 1) {
out.print(
"\n <a href_=\"#\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Previous</font></a>");
} else {
out.print(
"\n <a href=\""
+ sFileName
+ "?"
+ formParams
+ sSortParams
+ "Formeditorials_Page="
+ (iPage - 1)
+ "#Form\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Previous</font></a>");
}
out.print("\n [ " + iPage + " ]");
if (!bNext) {
out.print(
"\n <a href_=\"#\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Next</font></a><br>");
} else {
out.print(
"\n <a href=\""
+ sFileName
+ "?"
+ formParams
+ sSortParams
+ "Formeditorials_Page="
+ (iPage + 1)
+ "#Form\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Next</font></a><br>");
}
if (!bInsert) {
out.print(
" <tr>\n <td colspan=\"3\" style=\"background-color: #FFFFFF; border-style: inset; border-width: 0\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">");
out.print(
"\n <a href=\"EditorialsRecord.jsp?"
+ formParams
+ "\"><font style=\"font-size: 10pt; color: #CE7E00; font-weight: bold\">Insert</font></a> ");
}
out.println("</td>\n </tr>");
}
}
if (rs != null) rs.close();
out.println(" </table>");
} catch (Exception e) {
out.println(e.toString());
}
}