mailautoduedatepassed.java.svn-base

远程学生作业提交系统,所用技术JSP,Servelet,Ajax,Web Services,MySql

import javax.mail.*;
import javax.mail.internet.*;
import java.util.Properties;
import java.sql.*;
import java.util.*;

/**
 * Program uses with cron to check every minutes if some users:
 * --> Still do not have submit their assignement 1 day before the due date
 * --> have passed the due date for their assignement
 * in each case an email is send to those users
 */
public class MailAutoDuedatePassed {
    /**
     * The database's connection
     */
    protected static Connection _con;

    /**
     * The database's location
     */
    protected static String _source = "jdbc:mysql://127.0.0.1/test";

    /**
     * The database's user
     */
    protected static String _dbmUser = "root";

    /**
    * The database's password
    */
    protected static String _dbmPassword = "admin";

    /**
     * The smtp server's host
     */
    protected static String _smtpServerHost = "127.0.0.1";

    /**
     * The user's login to connect to the smtp server
     */
    protected static String _smtpUserLogin = "";

    /**
     * The password to connect to the smtp server
     */
    protected static String _smtpPassword = "";

    /**
     * The author of the email
     */
    protected static String _mailAuthor = "[e-mail]";

    /**
     * The main program checking the two issues
     */
    public static void main(String[] args) {
        // Initialisation of the connection with the database
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            _con = DriverManager.getConnection(_source, _dbmUser, _dbmPassword);

            //if (!_con.isClosed())
              // System.out.println("Connected to " + _source);
        } catch(Exception e) {
            System.err.println("Unable to connect to the database");
            System.exit(1);
        }

        Vector<UserProjectInfo> infoImpend = new Vector();
        Vector<UserProjectInfo> infoLate = new Vector();
        String subjectImpend = "";
        String subjectLate = "";
        String contentImpend = "";
        String contentLate = "";

        checkDuedateImpend(infoImpend);
        checkDuedatePassed(infoLate);

        // Send all the emails for the impending projects
        for (int i = 0; i < infoImpend.size(); i++) {
            subjectImpend = "Project " + infoImpend.elementAt(i).getProjectName()+ " due date is impending";
            contentImpend = "Dear " + infoImpend.elementAt(i).getUserName() + "\n\n";
            contentImpend = contentImpend + "The project " + infoImpend.elementAt(i).getProjectName() + "(" + infoImpend.elementAt(i).getProjectId() + ")";
            contentImpend = contentImpend + " due date is impending.\nYou have now one day to submit your submission.";
            contentImpend = contentImpend + "\n\nPlease do not reply to this automatically send email.";
            try {
                send(subjectImpend, contentImpend, new String[]{infoImpend.elementAt(i).getUserEmail()});
            } catch(Exception e) {
                System.err.println(e);
            }
        }

        // Send all the emails for the late projects
        for (int i = 0; i < infoLate.size(); i++) {
            subjectLate = "Project " + infoLate.elementAt(i).getProjectName()+ " due date passed";
            contentLate = "Dear " + infoLate.elementAt(i).getUserName() + "\n\n";
            contentLate = contentLate + "The project " + infoLate.elementAt(i).getProjectName() + "(" + infoLate.elementAt(i).getProjectId() + ")";
            contentLate = contentLate + " due date is already passed.\nYou still do not have submit your submission.";
            contentLate = contentLate + "\n\nPlease do not reply to this automatically send email.";
            try {
                send(subjectLate, contentLate, new String[]{infoLate.elementAt(i).getUserEmail()});
                //send(subjectLate, contentLate, new String[]{"[e-mail]"});
            } catch(Exception e) {
                System.err.println(e);
            }
        }
    }

    /**
     * Collect the users who still don't have submit their assignement 1 day before the due date
     * @param infos The informations of the users and their project (out parameter)
     */
    protected static void checkDuedateImpend(Vector<UserProjectInfo> infos) {
        Statement stmt = null;
        ResultSet results = null;

        String projName; // The project's name
        String projId; // The project's identifier
        String projDesc; // The project's description
        String projUrl; // The project's url
        String userName; // The user's name
        String userEmail; // The user's email
        String userLogin; // The user's login name

        Vector<String> projectIdList = new Vector<String>();
        Vector<String> userIdList = new Vector<String>();
        int i = 0;

        try {
            stmt = _con.createStatement();

            // The SQL requests
            // To get the project id concern
            String request = "SELECT COUNT(*) AS nbRes FROM project p, usersubjects s, preventuser pu WHERE p.code = s.subjectId AND pu.studentId = s.userId AND pu.projectId = p.projectId AND pu.preventImpend = 0 AND DATEDIFF(p.closingDate, SYSDATE())<1 ";
            request = request + "AND (s.userId, p.projectId) NOT IN (SELECT sub.studentId, sub.projectId FROM submission sub)";
            //System.out.println(request);
            results = stmt.executeQuery(request);
            results.next();

            if (results.getInt("nbRes") == 0) {
                return;
            }

            request = "SELECT p.projectId AS pid, s.userId as uid FROM project p, usersubjects s, preventuser pu WHERE p.code = s.subjectId AND pu.studentId = s.userId AND pu.projectId = p.projectId AND pu.preventImpend = 0 AND DATEDIFF(p.closingDate, SYSDATE())<1 ";
            request = request + "AND (s.userId, p.projectId) NOT IN (SELECT sub.studentId, sub.projectId FROM submission sub)";
            //System.out.println(request);
            results = stmt.executeQuery(request);
            while (results.next()) {
                projectIdList.addElement(results.getString("pid"));
                //System.out.println("pid: " + results.getString("pid"));
                userIdList.addElement(results.getString("uid"));
                //System.out.println("uid: " + results.getString("uid"));
            }

            // To calculate the vector to return
            if (projectIdList.size() > 0) {
                request = "SELECT p.projectId AS id, p.name AS pname, p.url AS url, p.description AS descr, u.name AS uname, u.email AS email, u.loginName AS login FROM project p, usersubjects s, user u WHERE p.code = s.subjectId AND s.userId = u.loginName ";
                request = request + "AND ((p.projectId = " + projectIdList.elementAt(0) + " AND s.userId = " + "'" + userIdList.elementAt(0) + "'" +")";
            }
            for (i=1; i < projectIdList.size(); i++) {
                request = request + " OR (p.projectId = " + projectIdList.elementAt(i) + " AND s.userId = " + "'" + userIdList.elementAt(i) + "'" + ")";
            }

            request = request + ")";

            //System.out.println("1: " + request);
            results = stmt.executeQuery(request);
            while (results.next()) {
                projId = results.getString("id");
                projName = results.getString("pname");
                projUrl = results.getString("url");
                projDesc = results.getString("descr");
                userName = results.getString("uname");
                userEmail = results.getString("email");
                userLogin = results.getString("login");

                infos.addElement(new UserProjectInfo(userName, userLogin, userEmail, projId, projName));
            }

            request = "";

            // Update the database to avoid sending again and again these emails
            if (projectIdList.size() > 0) {
                request = "UPDATE preventuser SET preventImpend = 1 WHERE ";
                request = request + "projectId = " + projectIdList.elementAt(0) + " AND studentId = " + "'" + userIdList.elementAt(0) + "'";
            }
            for (i=1; i < projectIdList.size(); i++) {
                request = request + " OR projectId = " + projectIdList.elementAt(i) + " AND studentId = " + "'" + userIdList.elementAt(i) + "'";
            }
            //System.out.println("2: " + request);
            if (projectIdList.size() > 0) {
                stmt.executeUpdate(request);
            }

        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    /**
     * Collect the users who have passed the due date for their assignement
     * @param infos The informations of the users and their project
     */
    protected static void checkDuedatePassed(Vector<UserProjectInfo> infos) {
        Statement stmt = null;
        ResultSet results = null;

        String projName; // The project's name
        String projId; // The project's identifier
        String projDesc; // The project's description
        String projUrl; // The project's url
        String userName; // The user's name
        String userEmail; // The user's email
        String userLogin; // The user's login name

        Vector<String> projectIdList = new Vector<String>();
        Vector<String> userIdList = new Vector<String>();
        int i = 0;

        try {
            stmt = _con.createStatement();

            // The SQL requests
            // To get the project id concern
            // The SQL requests
            // To get the project id concern
            String request = "SELECT COUNT(*) AS nbRes FROM project p, usersubjects s, preventuser pu WHERE p.code = s.subjectId AND pu.studentId = s.userId AND pu.projectId = p.projectId AND pu.preventLate = 0 AND p.closingDate < SYSDATE() ";
            request = request + "AND (s.userId, p.projectId) NOT IN (SELECT sub.studentId, sub.projectId FROM submission sub)";
            //System.out.println(request);
            results = stmt.executeQuery(request);
            results.next();

            if (results.getInt("nbRes") == 0) {
                return;
            }

            request = "SELECT p.projectId AS pid, s.userId as uid FROM project p, usersubjects s, preventuser pu WHERE p.code = s.subjectId AND pu.studentId = s.userId AND pu.projectId = p.projectId AND pu.preventLate = 0 AND p.closingDate < SYSDATE() ";
            request = request + "AND (s.userId, p.projectId) NOT IN (SELECT sub.studentId, sub.projectId FROM submission sub)";
            results = stmt.executeQuery(request);
            while (results.next()) {
                projectIdList.addElement(results.getString("pid"));
                //System.out.println("pid: " + results.getString("pid"));
                userIdList.addElement(results.getString("uid"));
                //System.out.println("uid: " + results.getString("uid"));
            }

            // To calculate the vector to return
            if (projectIdList.size() > 0) {
                request = "SELECT p.projectId AS id, p.name AS pname, p.url AS url, p.description AS descr, u.name AS uname, u.email AS email, u.loginName AS login FROM project p, usersubjects s, user u WHERE p.code = s.subjectId AND s.userId = u.loginName ";
                request = request + "AND ((p.projectId = " + projectIdList.elementAt(0) + " AND s.userId = " + "'" + userIdList.elementAt(0) + "'" +")";
            }
            for (i=1; i < projectIdList.size(); i++) {
                request = request + " OR (p.projectId = " + projectIdList.elementAt(i) + " AND s.userId = " + "'" + userIdList.elementAt(i) + "'" + ")";
            }

            request = request + ")";


            //System.out.println("1: " + request);
            results = stmt.executeQuery(request);
            while (results.next()) {
                projId = results.getString("id");
                projName = results.getString("pname");
                projUrl = results.getString("url");
                projDesc = results.getString("descr");
                userName = results.getString("uname");
                userEmail = results.getString("email");
                userLogin = results.getString("login");

                infos.addElement(new UserProjectInfo(userName, userLogin, userEmail, projId, projName));
            }

            request = "";

            // Update the database to avoid sending again and again these emails
            if (projectIdList.size() > 0) {
                request = "UPDATE preventuser SET preventLate = 1 WHERE ";
                request = request + "projectId = " + projectIdList.elementAt(0) + " AND studentId = " + "'" + userIdList.elementAt(0) + "'";
            }
            for (i=1; i < projectIdList.size(); i++) {
                request = request + " OR projectId = " + projectIdList.elementAt(i) + " AND studentId = " + "'" + userIdList.elementAt(i) + "'";
            }

            //System.out.println("2: " + request);
            if (projectIdList.size() > 0) {
                stmt.executeUpdate(request);
            }

        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    /**
     * The method to send an email to a list of email addresses
     * @param subject The email's subject
     * @param content The email's content
     * @param emails The email addresses to send this email
     */
    protected static void send(String subject, String content, String[] emails) throws Exception {
        //System.out.println("email to send: " + emails[0] + " " + subject);
        Properties props = new Properties();
        props.setProperty("mail.transport.protocol", "smtp");
        props.setProperty("mail.host", _smtpServerHost);
        props.setProperty("mail.user", _smtpUserLogin);
        props.setProperty("mail.password", _smtpPassword);

        Session mailSession = Session.getDefaultInstance(props, null);
        Transport transport = mailSession.getTransport();

        MimeMessage message = new MimeMessage(mailSession);
        message.setSubject(subject);
        message.setContent(content, "text/plain");

        // set the from and to address
        InternetAddress addressFrom = new InternetAddress(_mailAuthor);
        message.setFrom(addressFrom);

        // We send the email to all the email addresses in "emails"
        for (int i=0; i < emails.length; i++) {
             message.addRecipient(Message.RecipientType.TO,
                 new InternetAddress(emails[i]));
        }

        transport.connect();
        transport.sendMessage(message,
            message.getRecipients(Message.RecipientType.TO));
        transport.close();
    }
}