001: /*
002: * (C) Copyright 2000 - 2005 Nabh Information Systems, Inc.
003: *
004: * This program is free software; you can redistribute it and/or
005: * modify it under the terms of the GNU General Public License
006: * as published by the Free Software Foundation; either version 2
007: * of the License, or (at your option) any later version.
008: *
009: * This program is distributed in the hope that it will be useful,
010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: * GNU General Public License for more details.
013: *
014: * You should have received a copy of the GNU General Public License
015: * along with this program; if not, write to the Free Software
016: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: *
018: */
019: package com.nabhinc.portlet.news;
020:
021: import java.sql.Connection;
022: import java.sql.PreparedStatement;
023: import java.sql.ResultSet;
024: import java.sql.SQLException;
025: import java.sql.Timestamp;
026: import java.util.Date;
027: import java.util.HashMap;
028: import java.util.Vector;
029:
030: import javax.mail.MessagingException;
031: import javax.mail.SendFailedException;
032: import javax.sql.DataSource;
033:
034: import org.apache.commons.logging.Log;
035: import org.apache.commons.logging.LogFactory;
036:
037: import com.nabhinc.util.EmailUtil;
038: import com.nabhinc.util.db.DBUtil;
039: import com.nabhinc.util.i18n.DateTimeFormatUtil;
040:
041: /**
042: *
043: *
044: * @author Padmanabh Dabke
045: * (c) 2005 Nabh Information Systems, Inc. All Rights Reserved.
046: */
047: public class NewsEmailSender implements Runnable {
048:
049: public static final int DAILY = 0;
050: public static final int WEEKLY = 1;
051: public static final int MONTHLY = 2;
052: private static final long[] DURATIONS = new long[] { 24 * 3600000L,
053: 7 * 24 * 3600000L, 31 * 24 * 3600000L };
054:
055: private Log nesLogger = LogFactory.getLog(NewsEmailSender.class);
056: private DataSource nesDataSource = null;
057: private int nesPeriod = DAILY;
058: private String nesFromEmail = null;
059: private String nesEmailSubject = null;
060: private String nesEmailHeader = null;
061: private String nesEmailHeadlines = null;
062: private String nesEmailFooter = null;
063: private String nesStoriesSQL = "SELECT SB_NEWS_STORIES.id,SB_NEWS_STORIES.headline,SB_NEWS_STORIES.summary,SB_NEWS_STORIES.reported, SB_NEWS_STORIES.channelid, SB_NEWS_CHANNELS.cname"
064: + " FROM SB_NEWS_STORIES, SB_NEWS_CHANNELS WHERE SB_NEWS_STORIES.reported > ? AND SB_NEWS_STORIES.sdisplay = TRUE AND SB_NEWS_STORIES.channelid = SB_NEWS_CHANNELS.id";
065: private String nesSubscriberSQL = "SELECT userid, channelid FROM SB_NEWS_INTEREST WHERE userid in ( SELECT userid FROM SB_NEWS_SUB_PERIOD WHERE subperiod = ? ) ORDER BY userid";
066: private String nesEmailSQL = "SELECT oemail FROM SB_USERS WHERE userid = ?";
067:
068: private String nesNewsBaseURL = null;
069:
070: public NewsEmailSender(DataSource ds, int period, String fromEmail,
071: String subject, String template, String storiesSQL,
072: String subSQL, String emailSQL, String newsBaseURL) {
073: nesDataSource = ds;
074: if (period < 0 || period > 2)
075: throw new IllegalArgumentException(
076: "Email sending period must be 0,1, or 2");
077: nesPeriod = period;
078: nesFromEmail = fromEmail;
079: nesEmailSubject = subject;
080: if (newsBaseURL == null)
081: throw new IllegalArgumentException(
082: "News base url cannot be null.");
083: nesNewsBaseURL = newsBaseURL;
084:
085: if (storiesSQL != null)
086: nesStoriesSQL = storiesSQL;
087: if (subSQL != null)
088: nesSubscriberSQL = subSQL;
089: if (emailSQL != null)
090: nesEmailSQL = emailSQL;
091:
092: // Split the template into three pieces
093: int startPos = template.indexOf("<headlines>");
094: int endPos = template.indexOf("</headlines>");
095:
096: if (startPos < 0)
097: throw new IllegalArgumentException(
098: "Email template does not contain <headlines> tag.");
099: if (endPos < 0)
100: throw new IllegalArgumentException(
101: "Email template does not contain </headlines> tag.");
102: nesEmailHeader = template.substring(0, startPos);
103: nesEmailFooter = template.substring(endPos
104: + "</headlines>".length());
105: nesEmailHeadlines = template.substring(startPos
106: + "<headlines>".length(), endPos);
107: }
108:
109: private class NewsInfo {
110: public String id;
111: public String headline;
112: public String summary;
113: public Timestamp lastupdate;
114: public String channelid;
115: public String channelName;
116:
117: public NewsInfo(String i, String hd, String su, Timestamp lup,
118: String cid, String cname) {
119: id = i;
120: headline = hd;
121: summary = su;
122: lastupdate = lup;
123: channelid = cid;
124: channelName = cname;
125: }
126: }
127:
128: /* (non-Javadoc)
129: * @see java.lang.Runnable#run()
130: */
131: public void run() {
132: // Collect news headlines that have been updated over
133: // last 24 hours and email it to daily subscribers.
134: Connection conn = null;
135: ResultSet results = null;
136: PreparedStatement st = null;
137: Date today = new Date();
138: String dateStr = DateTimeFormatUtil.getDateFormat(null).format(
139: today);
140: String subject = nesEmailSubject.replaceAll("\\$currentDate",
141: dateStr);
142: try {
143: conn = nesDataSource.getConnection();
144: st = conn.prepareStatement(nesStoriesSQL);
145: long threshold = System.currentTimeMillis()
146: - DURATIONS[nesPeriod];
147: st.setTimestamp(1, new Timestamp(threshold));
148: results = st.executeQuery();
149: Vector storyVec = new Vector(10);
150: while (results.next()) {
151: NewsInfo nInfo = new NewsInfo(results.getString(1),
152: results.getString(2), results.getString(3),
153: results.getTimestamp(4), results.getString(5),
154: results.getString(6));
155: storyVec.addElement(nInfo);
156: }
157: if (storyVec.size() == 0) {
158: // No stories to email, just return.
159: return;
160: }
161:
162: NewsInfo[] stories = new NewsInfo[storyVec.size()];
163: storyVec.copyInto(stories);
164: results.close();
165: results = null;
166: st.close();
167: st = null;
168:
169: // Look up subscribers
170: st = conn.prepareStatement(nesSubscriberSQL);
171: st.setInt(1, nesPeriod);
172: results = st.executeQuery();
173: if (!results.next())
174: return;
175: String currentUserID = results.getString(1);
176: String lastUserID = currentUserID;
177: HashMap channelMap = new HashMap(5);
178: channelMap.put(results.getString(2), "");
179:
180: while (results.next()) {
181: currentUserID = results.getString(1);
182: if (currentUserID.equals(lastUserID)) {
183: channelMap.put(results.getString(2), "");
184: } else {
185: sendEmail(currentUserID, stories, channelMap,
186: subject);
187: channelMap.clear();
188: channelMap.put(results.getString(2), "");
189: lastUserID = currentUserID;
190: }
191: }
192:
193: // Process last user
194: sendEmail(currentUserID, stories, channelMap, subject);
195:
196: } catch (SQLException ex) {
197: nesLogger
198: .error(
199: "Failed to retrieve updated news stories from the database.",
200: ex);
201: return;
202: } finally {
203: DBUtil.close(results);
204: DBUtil.close(st);
205: DBUtil.close(conn);
206: }
207: }
208:
209: private void sendEmail(String userID, NewsInfo[] stories,
210: HashMap channelMap, String subject) {
211: boolean foundHeadline = false;
212: StringBuffer sb = new StringBuffer(nesEmailHeader.replaceAll(
213: "\\$subject", subject));
214: String anchorEnd = "</a>";
215:
216: // Check if the user has subscribed to the super-channel
217: boolean super ChannelSubscription = channelMap.get("1") == null ? false
218: : true;
219: for (int i = 0; i < stories.length; i++) {
220: if (super ChannelSubscription
221: || channelMap.get(stories[i].channelid) != null) {
222: foundHeadline = true;
223: StringBuffer sbHeadline = new StringBuffer(
224: nesEmailHeadlines);
225: String anchorStart = "<a href=\"" + nesNewsBaseURL
226: + "/"
227: + stories[i].channelName.replaceAll(" ", "_")
228: + "/" + stories[i].id
229: + ".html\" target=\"_blank\">";
230: int index = sbHeadline.indexOf("$headline");
231: sbHeadline.insert(index + 9, anchorEnd);
232: sbHeadline.insert(index, anchorStart);
233: String headline = sbHeadline.toString().replaceFirst(
234: "\\$headline", stories[i].headline);
235: headline = headline.replaceFirst("\\$summary",
236: stories[i].summary);
237: sb.append(headline);
238: }
239:
240: }
241: // Send email
242: if (foundHeadline) {
243: sb.append(nesEmailFooter);
244: Connection conn = null;
245: ResultSet results = null;
246: PreparedStatement st = null;
247: try {
248: conn = nesDataSource.getConnection();
249: st = conn.prepareStatement(nesEmailSQL);
250: st.setInt(1, Integer.parseInt(userID));
251: results = st.executeQuery();
252: if (results.next()) {
253: String userEmail = results.getString(1);
254: EmailUtil.send(nesFromEmail, userEmail, subject, sb
255: .toString(), "text/html");
256:
257: } else {
258: nesLogger
259: .error("Could not find a user email record for user ID "
260: + userID + ".");
261: }
262: } catch (SQLException ex) {
263: nesLogger.error("Failed to look up user email.", ex);
264: } catch (SendFailedException sfx) {
265: nesLogger.error("Failed to send news headlines.", sfx);
266: } catch (javax.mail.internet.AddressException ex) {
267: nesLogger.error("Email address is invalid.", ex);
268: } catch (MessagingException me) {
269: nesLogger.error("Faild to send news headlines.", me);
270: } finally {
271: DBUtil.close(results);
272: DBUtil.close(st);
273: DBUtil.close(conn);
274: }
275: }
276:
277: }
278:
279: }
|