001/*
002 * Copyright (c) 2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: welker $'
006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 
007 * '$Revision: 24234 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.geon;
031
032import java.io.BufferedReader;
033import java.io.FileReader;
034import java.sql.Connection;
035import java.sql.DriverManager;
036import java.sql.ResultSet;
037import java.sql.Statement;
038import java.util.HashMap;
039import java.util.Map;
040import java.util.Properties;
041import java.util.StringTokenizer;
042import java.util.Vector;
043
044import javax.mail.Message;
045import javax.mail.MessagingException;
046import javax.mail.Session;
047import javax.mail.Transport;
048import javax.mail.internet.InternetAddress;
049import javax.mail.internet.MimeMessage;
050import javax.servlet.http.HttpServletRequest;
051
052//////////////////////////////////////////////////////////////////////////
053//// LidarUtitilities
054/**
055 * Thread for executing the Lidar processing.
056 * 
057 * @author Efrat Jaeger
058 */
059public class LidarUtilities {
060
061        private static final String METADATATABLE = "NSAF.META";
062        public final long PROCESSLIMIT = 1600000;
063        public final long QUERYLIMIT = 20000000;
064        public final long PROCESSLIMITNOACC = 1000000;
065        public final long QUERYLIMITNOACC = 5000000;
066        private final static String COMMENT_CHAR = "#";
067
068        public LidarUtilities(StringBuffer threadResp, String header,
069                        String footer, String srid) {
070                this.threadResp = threadResp;
071                this.header = header;
072                this.footer = footer;
073                this.srid = srid;
074        }
075
076        public StringBuffer threadResp;
077        private String header;
078        private String footer;
079        private String srid;
080        private Map propsMap = new HashMap();
081        private String dbclassname;
082        private String dburl;
083        private String username;
084        private String password;
085
086        /**
087         * Sets or resets the property file parameters.
088         */
089        public boolean setProperties(String propsFile) {
090                try {
091                        BufferedReader br = new BufferedReader(new FileReader(propsFile));
092                        String line = br.readLine();
093                        while (line != null) {
094                                line = line.trim();
095                                if (!(line.startsWith(COMMENT_CHAR) || line.equals(""))) {
096                                        StringTokenizer st = new StringTokenizer(line, "=");
097                                        propsMap.put(st.nextToken(), st.nextToken());
098                                }
099                                line = br.readLine();
100                        }
101                        DBsetupvars();
102                        return true;
103                } catch (Exception ex) {
104                        System.out.println("unable to set up config properties");
105                        ex.printStackTrace();
106                        threadResp.append(header);
107                        threadResp.append("<tr><td><h2>Error!<h2></td></tr>");
108                        threadResp
109                                        .append("<tr><td>Unable to connect to the lidar database");
110                        threadResp.append("</td></tr>");
111                        threadResp.append(footer);
112                        return false;
113                }
114        }
115
116        public long calculateNumRows(String MinX, String MinY, String MaxX,
117                        String MaxY, String[] classification, String download) {
118                Connection con = null;
119                try {
120                        con = connect();
121
122                } catch (Exception ex) {
123                        System.out.println("unable to connect to lidar database");
124                        ex.printStackTrace();
125                        threadResp.append(header);
126                        threadResp.append("<tr><td><h2>Error!<h2></td></tr>");
127                        threadResp
128                                        .append("<tr><td>Unable to connect to the lidar database");
129                        threadResp.append("</td></tr>");
130                        threadResp.append(footer);
131                        disconnect(con);
132                        return -1;
133                }
134                // Get all tables within the bounding box.
135                tableNames = new Vector();
136                tableNames = getResidingTableNames(con, MinX, MaxX, MinY, MaxY);
137                if (tableNames.size() == 0) {
138                        disconnect(con);
139                        return -1;
140                }
141                // create the query constraint.
142                StringBuffer constraint = createConstraint(classification, MinX, MinY,
143                                MaxX, MaxY);
144
145                // sum the number of rows by each table
146                long count = countAcrossTables(con, tableNames, constraint, download);
147
148                disconnect(con);
149                return count;
150        }
151
152        public Vector getTableNames(String MinX, String MinY, String MaxX,
153                        String MaxY) {
154                Vector tNames = new Vector();
155                Connection con = null;
156                try {
157                        con = connect();
158
159                } catch (Exception ex) {
160                        System.out.println("unable to connect to lidar database");
161                        ex.printStackTrace();
162                        threadResp.append(header);
163                        threadResp.append("<tr><td><h2>Error!<h2></td></tr>");
164                        threadResp
165                                        .append("<tr><td>Unable to connect to the lidar database");
166                        threadResp.append("</td></tr>");
167                        threadResp.append(footer);
168                        disconnect(con);
169                        return tNames;
170                }
171                // Get all tables within the bounding box.
172                tNames = getResidingTableNames(con, MinX, MaxX, MinY, MaxY);
173                disconnect(con);
174                return tNames;
175        }
176
177        private long countAcrossTables(Connection con, Vector tableNames,
178                        StringBuffer constraint, String download) {
179                long count = 0;
180                for (int i = 0; i < tableNames.size(); i++) {
181                        long tmpCount = countQuery(con, constraint.toString(),
182                                        (String) tableNames.get(i));
183                        if (tmpCount == -1) {
184                                return -1;
185                        }
186                        count += tmpCount;
187                        // If the user selected some processing and the count is more than
188                        // the processing limit.
189                        if (count > PROCESSLIMIT && download.equals("1"))
190                                return count;
191                        // If the user selected to just download raw data and the query
192                        // limit has been reached.
193                        if (count > QUERYLIMIT)
194                                return count;
195                }
196                return count;
197        }
198
199        public void DBsetupvars() {
200                dbclassname = (String) propsMap.get("dbc.classname");
201                dburl = (String) propsMap.get("dbc.url");
202                username = (String) propsMap.get("dbc.username");
203                password = (String) propsMap.get("dbc.password");
204        }
205
206        private Connection connect() throws Exception {
207                Connection con = null;
208                Class.forName(dbclassname).newInstance();
209                con = DriverManager.getConnection(dburl, username, password);
210                return con;
211        }
212
213        private void disconnect(Connection con) {
214                try {
215                        con.close();
216                } catch (Exception ex) {
217                        con = null;
218                }
219        }
220
221        private Vector getResidingTableNames(Connection con, String X1, String X2,
222                        String Y1, String Y2) {
223                String query = "select table_name from " + METADATATABLE;
224                query += "\nwhere ";
225                query += "(((" + X1 + " <= X_MIN) and (X_MIN <= " + X2 + ")) "
226                                + "or ((" + X1 + " <= X_MAX) and (X_MAX <= " + X2 + ")) "
227                                + "or ((X_MIN <= " + X1 + ") and (" + X2 + " <= X_MAX))) \n"
228                                + "and (((" + Y1 + " <= Y_MIN) and (Y_MIN <= " + Y2 + ")) "
229                                + "or ((" + Y1 + " <= Y_MAX) and (Y_MAX <= " + Y2 + ")) "
230                                + "or ((Y_MIN <= " + Y1 + ") and (" + Y2 + " <= Y_MAX))) ";
231                Vector tablesVec = new Vector();
232                try {
233                        Statement st = con.createStatement();
234                        ResultSet rs = st.executeQuery(query);
235
236                        while (rs.next()) {
237                                String val = rs.getString(1);
238                                if (val != null && !val.equals("")) {
239                                        System.out.println("tableName  = " + val);
240                                        tablesVec.add(val);
241                                }
242                        }
243                        rs.close();
244                        if (tablesVec.size() > 0) {
245                                return tablesVec;
246                        }
247                } catch (Exception e1) {
248                        System.out.println("Unable to query lidar database table "
249                                        + METADATATABLE);
250                        e1.printStackTrace();
251                        threadResp.append(header);
252                        threadResp.append("<tr><td><h2>Error!<h2></td></tr>");
253                        threadResp.append("<tr><td>Unable to query the lidar database");
254                        threadResp.append("</td></tr>");
255                        threadResp.append(footer);
256                        return tablesVec;
257                }
258                // tablesVec size is empty.
259                threadResp.append(header);
260                threadResp.append("<tr><td><h2>Empty Query Response!<h2></td></tr>");
261                threadResp.append("<tr><td>Querying for ");
262                threadResp.append("bounding box selection: MinX = " + X1 + ", MaxX = "
263                                + X2 + ", ");
264                threadResp.append("MinY = " + Y1 + ", MaxY = " + Y2
265                                + " returned no result!</td></tr>");
266                threadResp.append(footer);
267                return tablesVec;
268        }
269
270        public StringBuffer createConstraint(String[] classification, String MinX,
271                        String MinY, String MaxX, String MaxY) {
272                StringBuffer constraint = new StringBuffer();
273                constraint.append("WHERE ");
274                if (classification != null) {
275                        if (classification.length > 0 && classification.length < 4) {// something
276                                                                                                                                                        // was
277                                                                                                                                                        // selected
278                                                                                                                                                        // but
279                                                                                                                                                        // not
280                                                                                                                                                        // all
281                                constraint.append("(CLASSIFICATION = '" + classification[0]
282                                                + "'");
283                                for (int i = 1; i < classification.length; i++) {
284                                        constraint.append(" OR CLASSIFICATION = '"
285                                                        + classification[i] + "'");
286                                }
287                                constraint.append(") ");
288                                constraint.append("AND ");
289                        }
290                }
291                constraint.append("db2gse.EnvelopesIntersect ( geometry, ");
292                constraint.append(MinX + ", " + MinY + ", " + MaxX + ", " + MaxY + ", "
293                                + srid + " ) = 1");
294                System.out.println("constraint in LidarUtil: " + constraint.toString());
295                return constraint;
296        }
297
298        private long countQuery(Connection con, String constraint, String tableName) {
299
300                String query = "select count(*) from " + tableName + "\n" + constraint;
301                System.out.println("count query ==> " + query);
302                try {
303                        Statement st = con.createStatement();
304                        ResultSet rs = st.executeQuery(query);
305
306                        while (rs.next()) {
307                                String val = rs.getString(1);
308                                if (val == null || val.equals("")) {
309                                        throw new Exception("query value is null");
310                                } else {
311                                        long count = Long.parseLong(val);
312                                        System.out.println("number of rows per table " + tableName
313                                                        + " ==> " + count);
314                                        return count;
315                                }
316                        }
317                        rs.close();
318
319                } catch (Exception e1) {
320                        System.out.println("unable to query lidar database table "
321                                        + tableName);
322                        e1.printStackTrace();
323                        threadResp.append(header);
324                        threadResp.append("<tr><td><h2>Error!<h2></td></tr>");
325                        threadResp.append("<tr><td>Unable to query the lidar database");
326                        threadResp.append("</td></tr>");
327                        threadResp.append(footer);
328                        return -1;
329                }
330                return -1;
331        }
332
333        public boolean processAccessRequest(HttpServletRequest request,
334                        String configFile) {
335
336                String firstName = request.getParameter("firstName");
337                String lastName = request.getParameter("lastName");
338                String org = request.getParameter("institution");
339                String email = request.getParameter("reqEmail");
340                String user = request.getParameter("user");
341                String interest = request.getParameter("interest");
342
343                // Add user to Lidar Pending List table.
344                LidarJobDB lidarJobDB = new LidarJobDB(configFile);
345                boolean added = lidarJobDB.addUser("PENDINGACCESSLIST", user,
346                                firstName, lastName, org, email);
347
348                String fromAddress = "GLW Support <efrat@geon01.sdsc.edu>";
349                String[] toAddress = { "ramon.arrowsmith@asu.edu" };
350                String[] ccAddress = { "chris.crosby@asu.edu" };
351                String[] bccAddress = { "efrat@sdsc.edu" };
352                String subject = "Request to Run LiDAR Jobs";
353                String body = "username:     " + user;
354                body += "\nfirst name:   " + firstName;
355                body += "\nlast name:    " + lastName;
356                body += "\norganization: " + org;
357                body += "\nemail:        " + email;
358                body += "\ninterest:\n" + interest;
359                System.out.println(body);
360                return sendEmail(fromAddress, toAddress, ccAddress, bccAddress,
361                                subject, body);
362        }
363
364        public boolean approveAccesssRequest(HttpServletRequest request,
365                        String configFile) {
366
367                String newUser = request.getParameter("newUser");
368                String firstName = request.getParameter("firstName");
369                String lastName = request.getParameter("lastName");
370                String org = request.getParameter("institution");
371                String email = request.getParameter("reqEmail");
372
373                // Add user to Lidar Access List table.
374                LidarJobDB lidarJobDB = new LidarJobDB(configFile);
375                boolean added = lidarJobDB.addUser("LIDARACCESSLIST", newUser,
376                                firstName, lastName, org, email);
377                if (!added) {
378                        threadResp
379                                        .append("<tr><td>Unable to connect to the LiDAR DB to add user "
380                                                        + newUser + " . Please try again later</td></tr>");
381                        return false;
382                }
383                threadResp
384                                .append("<tr><td>User "
385                                                + newUser
386                                                + " was successfully added to the LiDAR access list.</td></tr>");
387                boolean removed = lidarJobDB.removePendingUser(newUser);
388                if (!removed) {
389                        threadResp
390                                        .append("<tr><td>Unable to remove user from pending access list. Please remove manually.</td></tr>");
391                }
392                // Email notification to user.
393                String fromAddress = "GLW Support <efrat@geon01.sdsc.edu>";
394                String[] toAddress = { newUser };
395                String[] ccAddress = { "ramon.arrowsmith@asu.edu",
396                                "chris.crosby@asu.edu" };
397                String[] bccAddress = { "efrat@sdsc.edu" };
398                String subject = "Request to Run LiDAR Jobs";
399                String body = "Dear " + firstName.trim() + ",\n\n";
400                body += "Your request to submit LiDAR jobs through the GEON LiDAR Workflow has been approved. \n\n";
401                body += "Thank you,\n";
402                body += "The LiDAR team\n";
403                body += "http://www.geongrid.org";
404                System.out.println(body);
405                boolean emailSent = sendEmail(fromAddress, toAddress, ccAddress,
406                                bccAddress, subject, body);
407                if (!emailSent) {
408                        threadResp
409                                        .append("<tr><td>Reason: Unable to send user a nofication email.</td></tr>");
410                        return false;
411                }
412                return true;
413        }
414
415        protected boolean sendEmail(String fromAddress, String[] toAddress,
416                        String[] ccAddress, String[] bccAddress, String subject, String body) {
417                String host = "localhost";
418                Properties props = new Properties();
419                props.put("mail.smtp.host", host);
420                props.put("mail.debug", "false");
421                Session session = Session.getInstance(props);
422                try {
423                        Message msg = new MimeMessage(session);
424                        msg.setFrom(new InternetAddress(fromAddress));
425                        InternetAddress[] address;
426                        if (toAddress != null) {
427                                address = new InternetAddress[toAddress.length];
428                                for (int i = 0; i < toAddress.length; i++) {
429                                        address[i] = new InternetAddress(toAddress[i]);
430                                }
431                                msg.setRecipients(Message.RecipientType.TO, address);
432                        }
433                        if (ccAddress != null) {
434                                address = new InternetAddress[ccAddress.length];
435                                for (int i = 0; i < ccAddress.length; i++) {
436                                        address[i] = new InternetAddress(ccAddress[i]);
437                                }
438                                msg.setRecipients(Message.RecipientType.CC, address);
439                        }
440                        if (bccAddress != null) {
441                                address = new InternetAddress[bccAddress.length];
442                                for (int i = 0; i < bccAddress.length; i++) {
443                                        address[i] = new InternetAddress(bccAddress[i]);
444                                }
445                                msg.setRecipients(Message.RecipientType.BCC, address);
446                        }
447                        msg.setSubject(subject);
448                        msg.setSentDate(new java.util.Date());
449                        msg.setText(body);
450                        Transport.send(msg);
451                        return true;
452                } catch (MessagingException mex) {
453                        mex.printStackTrace();
454                        threadResp
455                                        .append("<tr><td>We're sorry, we're unable to process your request at this point. ");
456                        threadResp.append("Please try again later.</td></tr>");
457                        return false;
458                }
459        }
460
461        static public void main(String args[]) {
462                String[] classification = { "G" };
463                StringBuffer threadResp = new StringBuffer();
464                LidarUtilities et = new LidarUtilities(threadResp, "", "", "1005");
465                long count = et.calculateNumRows("6101117", "1971306", "6207459",
466                                "1991991", classification, "1");
467                for (int i = 0; i < et.tableNames.size(); i++) {
468                        System.out.println((String) et.tableNames.get(i));
469                }
470                System.out.println("number of rows ==> " + count);
471        }
472
473        public long estimateTime(long nPoints) {
474                double q = queryTime(nPoints);
475                double p = processTime(nPoints);
476                System.out.println(nPoints + " points, query time " + q
477                                + ", process time " + p);
478                return Math.round(p + q);
479                // return Math.round(queryTime(nPoints) + processTime(nPoints));
480        }
481
482        public double processTime(long nPoints) {
483                return Math.pow(10, -9) * Math.pow(nPoints, 2) + 0.0003 * nPoints
484                                + 50.492;
485        }
486
487        public double queryTime(long nPoints) {
488                return -8 * Math.pow(10, -12) * Math.pow(nPoints, 2) + 5
489                                * Math.pow(10, -5) * nPoints + 15.399;
490        }
491
492        public Vector tableNames;
493}