Examples with Select db.Select used on opensource projects

Example 11 with Select

use of db.Select in project common by zenlunatics.

the class MailList method buildDigest.

// --------------------------------------------------------------------------
MimeMessage buildDigest(LocalDate date, Session session, Site site, DBConnection db) {
    ArrayList<Object[]> messages = db.readRowsObjects(new Select("id,sender,subject,arrived").from("ml_" + m_id).where("arrived::date='" + DBConnection.formatDate(date) + "'").orderBy("arrived"));
    if (messages.size() == 0)
        return null;
    MimeMessage digest = new MimeMessage(session);
    MimeMultipart body = new MimeMultipart();
    StringBuilder html = new StringBuilder();
    DateFormat df = DateFormat.getDateTimeInstance();
    html.append(messages.size()).append(" message");
    if (messages.size() != 1)
        html.append('s');
    try {
        for (int i = 0; i < messages.size(); i++) {
            Object[] message = messages.get(i);
            // only works with "store on disk"
            String text = loadMessage((Integer) message[0], (ResultSet) null);
            if (text == null)
                continue;
            InputStream is = new ByteArrayInputStream(text.getBytes());
            MimeMessage m = new MimeMessage(session, is);
            String reply_subject = (String) message[2];
            if (!reply_subject.startsWith("Re:"))
                reply_subject = "Re: " + reply_subject;
            html.append("<div style=\"line-height:19px;width:100%;background-color:white;padding:5px;border-bottom:solid 1px #ddd;\"><a href=\"#m").append(i).append("\" style=\"text-decoration:none\"><span style=\"width:30px;text-align:right;font-family:arial,helvetica,clean,sans-serif;color:rgb(34,34,34);font-size:12px;font-style:normal;font-weight:bold;\">").append(i + 1).append("</span><span style=\"padding-left:10px;color:#16a085;\">").append(message[2]).append("</span> posted by ").append(message[1]).append("</a></div>");
            MimeBodyPart body_part = new MimeBodyPart();
            Timestamp arrived = (Timestamp) message[3];
            String from = m.getFrom()[0].toString();
            InternetAddress ia = new InternetAddress(from);
            from = ia.getAddress();
            StringBuilder message_head = new StringBuilder();
            message_head.append("<div style=\"background-color:#eee;border:solid 1px #ccc;display:flex;flex-wrap:wrap;margin-bottom:20px;margin-top:20px;padding:5px;width:100%;\"><div><a name=\"m").append(i).append("\"><span style=\"color:rgb(34,34,34);font-weight:bold;font-family:arial,helvetica,clean,sans-serif;text-align:right;font-size:18px;font-style:normal;width:30px;\">").append(i + 1).append("</span></a><span style=\"padding-left:10px;font-size:18px;\">").append(message[2]).append("</span><br>").append(df.format(new Date(arrived.getTime()))).append(" posted by ").append(message[1]).append("</div><div style=\"flex-grow:1;padding-right:10px;text-align:right;\"><a href=\"mailto:").append(m_name).append("@").append(m_site.getDomain()).append("?subject=").append(reply_subject).append("\" style=\"float:right;margin-left:20px;text-decoration:none;\">reply to ").append(m_name).append("</a><a href=\"mailto:").append(from).append("?subject=").append(reply_subject).append("\" style=\"float:right;margin-left:20px;text-decoration:none;\">reply to ").append(message[1]).append("</a></div></div>");
            body_part.setContent(message_head.toString(), "text/html");
            body.addBodyPart(body_part);
            body_part = new MimeBodyPart();
            body_part.setContent(m.getContent(), m.getContentType());
            body.addBodyPart(body_part);
        }
        MimeBodyPart header = new MimeBodyPart();
        header.setContent(html.toString(), "text/html");
        body.addBodyPart(header, 0);
        digest.setContent(body);
        digest.setFrom(m_name + "@" + m_site.getDomain());
        DateTimeFormatter dtf = DateTimeFormatter.ofLocalizedDate(FormatStyle.MEDIUM);
        Locale locale = site.getLocale();
        if (locale != null)
            dtf = dtf.withLocale(locale);
        digest.setSubject("[" + m_name + "] " + date.format(dtf) + " Digest");
    } catch (SQLException e) {
        System.out.println(e.toString());
    } catch (MessagingException e) {
        System.out.println(e.toString());
    } catch (IOException e) {
        System.out.println(e.toString());
    }
    return digest;
}

Example 12 with Select

use of db.Select in project common by zenlunatics.

the class MailLists method writeLists.

// --------------------------------------------------------------------------
public void writeLists(Request request) throws IOException {
    Person user = request.getUser();
    int user_id = user.getId();
    Select query = new Select("mail_lists.id,mail_lists.name,mail_lists_people.mail_lists_id,mail_lists_digest.mail_lists_id").from("mail_lists LEFT JOIN mail_lists_people ON (mail_lists_people.mail_lists_id=mail_lists.id AND mail_lists_people.people_id=" + user_id + ") LEFT JOIN mail_lists_digest ON (mail_lists_digest.mail_lists_id=mail_lists.id AND mail_lists_digest.people_id=" + user_id + ")").where(request.testSessionFlag("show inactive lists") ? null : "mail_lists.active").orderBy("LOWER(mail_lists.name)");
    ResultSet rs = request.db.select(query);
    HTMLWriter writer = request.writer;
    writer.write("<table class=\"table table-hover\"><thead><tr><th>send email to list</th><th>archives</th><th>latest post</th><th>subscribed</th><th>nightly digest</th></tr></thead>");
    try {
        while (rs.next()) {
            int list_id = rs.getInt(1);
            String list = rs.getString(2);
            MailHandler mail_handler = getMailHandler(list, request.db);
            boolean subscribed = mail_handler != null ? mail_handler.isSubscribed(user, request) : rs.getString(3) != null;
            boolean archives_public = new MailList(list_id, request.site, request.db).archivesPublic();
            if (mail_handler != null && !archives_public && !subscribed)
                continue;
            writer.write("<tr class=\"lists\"><td>");
            String address = list + "@" + request.site.getSettings().getString("mail list domain");
            writer.a(m_show_full_address_on_email_page ? address : list, "mailto:" + address);
            writer.write("</td><td style=\"text-align:center\">");
            if (!archives_public && !subscribed)
                writer.setAttribute("style", "display:none");
            writer.setAttribute("id", "view" + list_id);
            writer.aButtonOnClick("view", "document.location=context+'/MailLists/" + list_id + "'");
            writer.write("</td><td>");
            if (!archives_public && !subscribed)
                writer.setAttribute("style", "display:none");
            writer.setAttribute("id", "date" + list_id);
            writer.tagOpen("span");
            writer.writeDate(request.db.lookupDate(new Select("max(arrived)").from("ml_" + list_id)));
            writer.tagClose();
            writer.write("</td><td style=\"text-align:center\">");
            if (mail_handler != null) {
                if (subscribed)
                    writer.write("&#10003;");
            } else {
                StringBuilder on_change = new StringBuilder("$('digest").append(list_id).append("').style.display=this.checked?'':'none';");
                if (!archives_public) {
                    on_change.append("$('view").append(list_id).append("').style.display=this.checked?'':'none';");
                    on_change.append("$('date").append(list_id).append("').style.display=this.checked?'':'none';");
                }
                on_change.append("toggle_subscription(this,").append(list_id).append(");return false");
                writer.setAttribute("onchange", on_change.toString());
                writer.checkbox(null, null, null, subscribed, true);
            }
            writer.write("</td><td style=\"text-align:center\">");
            writer.setAttribute("id", "digest" + list_id);
            writer.setAttribute("onchange", "toggle_digest(this," + list_id + ");return false");
            if (!subscribed)
                writer.setAttribute("style", "display:none");
            writer.checkbox(null, null, null, rs.getString(4) != null, true);
            writer.write("</td></tr>");
        }
        rs.getStatement().close();
    } catch (SQLException e) {
        request.abort(e);
    }
    writer.write("</table>");
}

Example 13 with Select

use of db.Select in project common by zenlunatics.

the class MailLists method getMailHandler.

// --------------------------------------------------------------------------
public MailHandler getMailHandler(String list, DBConnection db) {
    MailHandler mail_handler = m_mail_handlers.get(list.toLowerCase());
    if (mail_handler != null)
        return mail_handler;
    String send_to = db.lookupString(new Select("send_to").from("mail_lists").where("name ILIKE '" + HTMLWriter.escapeJSString(list) + "'"));
    if (send_to != null)
        for (MailHandlerFactory mail_handler_factory : m_mail_handler_factories) {
            mail_handler = mail_handler_factory.getHandler(send_to, db);
            if (mail_handler != null) {
                m_mail_handlers.put(list.toLowerCase(), mail_handler);
                return mail_handler;
            }
        }
    return null;
}

Example 14 with Select

use of db.Select in project common by zenlunatics.

the class MailLists method checkAll.

// --------------------------------------------------------------------------
@AdminTask
public void checkAll(Request request) throws IOException {
    HTMLWriter writer = request.writer;
    List<String[]> rows = request.db.readRows(new Select("host,username,password,id,name").from("mail_lists").where("active"));
    Session session = Session.getInstance(new Properties(), null);
    for (String[] row : rows) if (row.length == 5 && row[0] != null && row[2] != null)
        try {
            Store store = session.getStore(((MailLists) request.site.getModule("MailLists")).m_store_protocol);
            String account = accountName(row[1], Integer.parseInt(row[3]));
            writer.h4(row[4]);
            store.connect(row[0], account, row[2]);
            Folder folder = store.getFolder("INBOX");
            folder.open(Folder.READ_ONLY);
            Message[] messages = folder.getMessages();
            if (messages.length > 0) {
                writer.write("<table border=\"1\">");
                for (Message message : messages) {
                    writer.write("<tr><td>");
                    Date date = message.getReceivedDate();
                    if (date != null)
                        writer.write(date);
                    writer.write("</td><td>");
                    writer.write(message.getFrom()[0]);
                    writer.write("</td><td>");
                    writer.write(message.getSubject());
                    writer.write("</td><td>");
                    writer.write(message.getSize());
                    writer.write("</td></tr>");
                }
                writer.write("</table>");
            } else
                writer.write("0 messages");
            folder.close(false);
            store.close();
        } catch (MessagingException e) {
            writer.write(e.toString());
        } catch (IOException e) {
            writer.write(e.toString());
        }
}

Example 15 with Select

use of db.Select in project common by zenlunatics.

the class MailLists method writeMessage.

// --------------------------------------------------------------------------
private void writeMessage(String list, int message_id, Request request) throws IOException {
    HTMLWriter writer = request.writer;
    try {
        ResultSet rs = request.db.select(new Select("sender,arrived,message").from(list).whereIdEquals(message_id));
        if (rs.next()) {
            MailList mail_list = new MailList(Integer.parseInt(list.substring(3)), request.site, request.db);
            String text = mail_list.loadMessage(message_id, rs);
            if (text == null)
                writer.write("Message file not found.");
            else {
                String sender = rs.getString(1);
                writer.write("<div id=\"ml_message\">From: ").write(sender).write(" - ").write(DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).format(rs.getTimestamp(2))).br();
                if (m_show_reply_links_in_archives) {
                    writer.aButtonOnClick("reply to " + mail_list.getName(), "$('ml_message').replace(context+'/MailLists?list=" + list + "&cmd=reply_list&db_key_value=" + message_id + "');");
                    writer.space();
                    writer.aButtonOnClick("reply to " + sender, "$('ml_message').replace(context+'/MailLists?list=" + list + "&cmd=reply_sender&db_key_value=" + message_id + "');");
                }
                writer.hr();
                try {
                    InputStream is = new ByteArrayInputStream(text.getBytes());
                    MimeMessage message = new MimeMessage(getSession(), is);
                    Object content = message.getContent();
                    if (content instanceof String)
                        writer.write(content);
                    else
                        WebMail.writePart(message, message_id, (MimeMultipart) content, new ArrayList<Integer>(), writer);
                } catch (MessagingException e) {
                    sendError(list, "writeMessage", null, null, e);
                }
                writer.write("</div>");
            }
        } else
            writer.write("message not found");
        rs.getStatement().close();
    } catch (SQLException e) {
        sendError(list, "writeMessage", null, null, e);
    }
}