API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 24th, 2016
97
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.31 KB | None | 0 0
raw download clone embed print report
  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2. pageEncoding="UTF-8"%>
  3. <%@ page import="java.sql.*, javax.sql.*, javax.naming.*"%>
  4.  
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  6. <%!
  7. private final int MAX_ROWS = 20;
  8. private final int MAX_COLS = 10;
  9. // set value of option and set selected if value == data from POST
  10. public static String selected (String name, String value) {
  11. String val = "value=\""+value+"\"";
  12. if (value.equals(name)) {return val+" selected";}
  13. else {return val;}
  14. }
  15. private final boolean DEBUG = false;
  16. // logging
  17. private final boolean LOGGING = true;
  18. private long start, used;
  19. %>
  20. <html>
  21. <head>
  22. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  23. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
  24. <title>CSE135 Project</title>
  25. </head>
  26. <body>
  27. <%@ page import="com.srk.pkg.Constants"%>
  28. <%
  29.  
  30. Connection conn = null;
  31. PreparedStatement pstmt = null;
  32. Statement stmt = null;
  33. ResultSet rs = null;
  34. try {
  35. Class.forName("org.postgresql.Driver");
  36. String url = "jdbc:postgresql:sample_db";
  37. String admin = "postgres";
  38. String password = "";
  39. conn = DriverManager.getConnection(url, admin, password);
  40. }
  41. catch (Exception e) {}
  42. %>
  43. <%-------------- Initialization -------------%>
  44. <%
  45. if (LOGGING) start = System.currentTimeMillis();
  46. String action = request.getParameter("action");
  47. String row, cid, state;
  48. int poffset = 0;
  49. int roffset = 0;
  50. if (request.getParameter("row") != null) {
  51. row = request.getParameter("row");
  52. cid = request.getParameter("cid");
  53. state = "all";
  54. }
  55. else {
  56. row = "Customers";
  57. cid = "all";
  58. state = "all";
  59. }
  60. // next access
  61. if (action != null) {
  62. poffset = Integer.parseInt(request.getParameter("poffset"));
  63. roffset = Integer.parseInt(request.getParameter("roffset"));
  64. if (action.equals("nextP"))
  65. poffset += MAX_COLS;
  66. else // nextR
  67. roffset += MAX_ROWS;
  68. }
  69. // create statement
  70. stmt = conn.createStatement();
  71. %>
  72.  
  73. <%------------ Building Queries --------------%>
  74. <%
  75. String stateFilter, catFilter;
  76.  
  77. // statefilter
  78. stateFilter = "state>0";
  79.  
  80. // category filter
  81. catFilter = cid.equals("all") ? "p.category_id>0" : "p.category_id="+cid;
  82.  
  83. // after filtering catgory the orders' user, price, quantity
  84. String salesWCatFilter = "(SELECT orders.user_id, orders.price, orders.quantity"
  85. +" FROM orders JOIN products p ON orders.product_id=p.id AND "+catFilter+") s";
  86.  
  87. // ----------------------------------------------------------------
  88. // PRODUCTS TEMP TABLE
  89. String productsTBSQL = "CREATE TEMP TABLE temp_products AS ("
  90. + "SELECT * FROM products p WHERE "+catFilter
  91. + " LIMIT "+(MAX_COLS+1)+" OFFSET "+poffset+");";
  92. stmt.executeUpdate("DROP TABLE IF EXISTS temp_products");
  93. stmt.executeUpdate(productsTBSQL);
  94.  
  95.  
  96. // ----------------------------------------------------------------
  97. // FULL TABLE QUERIES
  98. String productsInfoSQL, // product headers query
  99. rowsInfoSQL, // row headers query
  100. salesInfoSQL; // sale query with products x rows
  101. // product
  102. productsInfoSQL = "SELECT p.name, COALESCE(SUM(s.price*s.quantity), 0) AS sum"
  103. + " FROM temp_products p LEFT OUTER JOIN orders s ON p.id=s.product_id"
  104. + " LEFT OUTER JOIN users u ON u.id=s.user_id"
  105. + " GROUP BY p.name ORDER BY p.name;";
  106.  
  107. // states
  108. if (row.equals("States")) {
  109. String usersTBSQL;
  110.  
  111. stmt.executeUpdate("DROP TABLE IF EXISTS temp_users");
  112. // temp table sql
  113. usersTBSQL = "CREATE TEMP TABLE temp_users AS ("
  114. + "SELECT * FROM users LIMIT "+(MAX_ROWS+1)+" OFFSET "+roffset+");";
  115. stmt.executeUpdate(usersTBSQL);
  116.  
  117.  
  118. // new statefilter for states
  119. stateFilter = "LIMIT "+(MAX_ROWS+1)+" OFFSET "+roffset;
  120. if(!cid.equals("all")){
  121. rowsInfoSQL = "SELECT users.state AS name, users.id, COALESCE(SUM(orders.price),0) AS sum "
  122. +" FROM users "
  123. +" LEFT JOIN orders "
  124. +" ON users.id = orders.user_id "
  125. +" INNER JOIN products "
  126. +" ON orders.product_id = products.id "
  127. +" INNER JOIN categories "
  128. +" ON products.category_id = categories.id "
  129. +" WHERE categories.id = '"+cid+"'"
  130. +" GROUP BY users.id, users.state ORDER BY users.state";
  131.  
  132. salesInfoSQL = "SELECT u.id, COALESCE(SUM(p.price*o.quantity), 0) AS sum "
  133. +" FROM users u "
  134. +" LEFT JOIN orders o ON u.id = o.user_id "
  135. +" INNER JOIN products p ON o.product_id = p.id "
  136. +" INNER JOIN categories c ON p.category_id = c.id "
  137. +" WHERE c.id = '" +cid+ "'"
  138. +" GROUP BY u.id, p.name ORDER BY u.state, p.name ";
  139. }
  140. //ALLL
  141. else {
  142.  
  143. //rn gets the states and their sum totals but user id is ????? makes it skip
  144. rowsInfoSQL = "SELECT users.state AS name, users.id, SUM(orders.price) AS sum "
  145. +" FROM users "
  146. +" LEFT JOIN orders "
  147. +" ON users.id = orders.user_id "
  148. +" GROUP BY users.state, users.id ORDER BY users.state";
  149.  
  150. //should print out everything for all products
  151. salesInfoSQL = "SELECT u.id, COALESCE(SUM(p.price*o.quantity), 0) AS sum "
  152. +" FROM users u "
  153. +" LEFT JOIN orders o ON u.id = o.user_id "
  154. +" INNER JOIN products p ON o.product_id = p.id "
  155. +" INNER JOIN categories c ON p.category_id = c.id "
  156. +" GROUP BY u.id, p.name ORDER BY u.state, p.name ";
  157.  
  158. }
  159. }
  160. // customers
  161. else {
  162. String usersTBSQL;
  163.  
  164. stmt.executeUpdate("DROP TABLE IF EXISTS temp_users");
  165. // temp table sql
  166. usersTBSQL = "CREATE TEMP TABLE temp_users AS ("
  167. + "SELECT * FROM users LIMIT "+(MAX_ROWS+1)+" OFFSET "+roffset+");";
  168. stmt.executeUpdate(usersTBSQL);
  169.  
  170. rowsInfoSQL = "SELECT u.id, u.name, COALESCE(SUM(s.price*s.quantity), 0) AS sum"
  171. + " FROM temp_users u "
  172. + " LEFT OUTER JOIN "+salesWCatFilter+" ON u.id=s.user_id"
  173. + " GROUP BY u.id, u.name ORDER BY u.name";
  174.  
  175. salesInfoSQL = "SELECT u.id, COALESCE(SUM(s.price*s.quantity), 0) AS sum"
  176. + " FROM temp_users u"
  177. + " CROSS JOIN temp_products p"
  178. + " LEFT OUTER JOIN orders s ON s.product_id=p.id AND s.user_id=u.id"
  179. + " GROUP BY u.id, u.name, p.name"
  180. + " ORDER BY u.name, p.name;";
  181. }
  182. %>
  183.  
  184. <%
  185. if (action == null) {
  186. %>
  187.  
  188. <!-------------- Menu ------------->
  189. <div class="collapse navbar-collapse">
  190. <ul class="nav navbar-nav">
  191. <li><a href="index.jsp">Home</a></li>
  192. <li><a href="categories.jsp">Categories</a></li>
  193. <li><a href="products.jsp">Products</a></li>
  194. <li><a href="orders.jsp">Orders</a></li>
  195. <li><a href="login.jsp">Logout</a></li>
  196. </ul>
  197. </div>
  198.  
  199. <!------------ Filter -------------->
  200. <form action="" method="GET">
  201. <table class="table table-striped">
  202. <tr>
  203. <th>Row</th>
  204. <th>Order</th>
  205. <th>Category</th>
  206. <th>&nbsp;</th>
  207. </tr>
  208. <tr>
  209. <!-- Rows dropdown -->
  210. <td><select name="row">
  211. <option <%=selected(row, "Customers")%>>Customers</option>
  212. <option <%=selected(row, "States")%>>States</option>
  213. </select></td>
  214.  
  215. <!-- Order dropdown -->
  216. <td><select name="order">
  217. <option value="a">Alphabetical</option>
  218. <option value="t">Top-K</option>
  219. </select></td>
  220.  
  221. <!-- Product category dropdown -->
  222. <td><select name="cid">
  223. <option value="all">All categories</option>
  224. <%
  225. rs = stmt.executeQuery("SELECT * FROM categories");
  226. while(rs.next()) {
  227. %><option <%=selected(cid, rs.getString("id"))%>>
  228. <%=rs.getString("name")%></option><%
  229. }
  230. %>
  231. </select></td>
  232.  
  233. <!-- Submit query button -->
  234. <td><input class="btn btn-primary btn-block"
  235. type="submit" value="Run query" /></td>
  236. </tr>
  237. </table>
  238. </form>
  239. <%
  240. }
  241. %>
  242.  
  243.  
  244. <%--------------- Next Button -----------------%>
  245. <%
  246. String btnDBName, btnAction, btnItemName;
  247. int btnLimit, btnOffset;
  248. // next rows button
  249. btnDBName = "temp_users";
  250. btnAction = "nextR";
  251. btnItemName = row;
  252. btnLimit = MAX_ROWS;
  253. %>
  254. <%
  255. rs = stmt.executeQuery("SELECT count(*) AS cnt FROM "+btnDBName);
  256.  
  257. if (rs.next() && rs.getInt("cnt") == btnLimit + 1) {
  258. %>
  259. <form action="" method="POST">
  260. <input type="hidden" name="action" value="<%=btnAction %>" />
  261. <input type="hidden" name="row" value="<%=row %>"/>
  262. <input type="hidden" name="state" value="<%=state %>"/>
  263. <input type="hidden" name="cid" value="<%=cid %>"/>
  264. <input type="hidden" name="poffset" value="<%=poffset %>"/>
  265. <input type="hidden" name="roffset" value="<%=roffset %>"/>
  266. <input class="btn btn-default btn-sm" type="submit" value="Next <%=btnLimit %> <%=btnItemName %> >>" />
  267. </form>
  268. <%
  269. // remove the last row
  270. stmt.executeUpdate("DELETE FROM "+btnDBName
  271. +" WHERE id IN (SELECT MAX(id) FROM "+btnDBName+");");
  272. }
  273. %>
  274.  
  275. <%
  276. // next products button
  277. btnDBName = "temp_products";
  278. btnAction = "nextP";
  279. btnItemName = "Products";
  280. btnLimit = MAX_COLS;
  281. %>
  282. <%
  283. rs = stmt.executeQuery("SELECT count(*) AS cnt FROM "+btnDBName);
  284.  
  285. if (rs.next() && rs.getInt("cnt") == btnLimit + 1) {
  286. %>
  287. <form action="" method="POST">
  288. <input type="hidden" name="action" value="<%=btnAction %>" />
  289. <input type="hidden" name="row" value="<%=row %>"/>
  290. <input type="hidden" name="state" value="<%=state %>"/>
  291. <input type="hidden" name="cid" value="<%=cid %>"/>
  292. <input type="hidden" name="poffset" value="<%=poffset %>"/>
  293. <input type="hidden" name="roffset" value="<%=roffset %>"/>
  294. <input class="btn btn-default btn-sm" type="submit" value="Next <%=btnLimit %> <%=btnItemName %> >>" />
  295. </form>
  296. <%
  297. // remove the last row
  298. stmt.executeUpdate("DELETE FROM "+btnDBName
  299. +" WHERE id IN (SELECT MAX(id) FROM "+btnDBName+");");
  300. }
  301. %>
  302.  
  303. <!---------------- Table --------------->
  304. <table class="table table-striped">
  305. <!-- Prooduct header row -->
  306. <tr>
  307. <th>&nbsp;</th>
  308. <%
  309. rs = stmt.executeQuery(productsInfoSQL);
  310. String tempName; // for truncation
  311. for (int i=0; i<MAX_COLS; ++i) {
  312. if (!rs.next()) break;
  313.  
  314. tempName = rs.getString("name");
  315. tempName = tempName.substring(0, Math.min(tempName.length(), 10));
  316. out.println("<th>"+tempName+"<br>($"+rs.getInt("sum")+")</th>");
  317. }
  318. %>
  319. </tr>
  320.  
  321. <!-------------- Price ---------------->
  322. <%
  323. Statement salesStmt = conn.createStatement();
  324. ResultSet salesRs = null;
  325. rs = stmt.executeQuery(rowsInfoSQL);
  326. salesRs = salesStmt.executeQuery(salesInfoSQL);
  327. int tempId = -1; // used to insert first data after a row return
  328. for (int i=0; i<MAX_ROWS; ++i) {
  329. if (!rs.next()) break;
  330.  
  331. out.println("<tr>");
  332. out.println("<th>"+rs.getString("name")+"<br>($"+rs.getInt("sum")+")</th>");
  333. if (tempId == rs.getInt("id")) {
  334. out.println("<td>"+salesRs.getString("sum")+"</td>");
  335. }
  336. while(salesRs.next()) {
  337. if ((tempId=salesRs.getInt("id")) != rs.getInt("id")) {
  338. break;
  339. }
  340. out.println("<td>$"+salesRs.getString("sum")+"</td>");
  341. }
  342. out.println("</tr>");
  343. }
  344. salesRs.close();
  345. salesStmt.close();
  346.  
  347. if (LOGGING) {
  348. used = System.currentTimeMillis() - start;
  349. System.out.println("\nUsed " + used + " ms");
  350. System.out.println("Which is " + used/1000 + "sec\n");
  351. }
  352. %>
  353. </table>
  354.  
  355.  
  356.  
  357.  
  358.  
  359.  
  360. </body>
  361. </html>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!