Scheduling email depending on date attribute

Hello, I am a student working on a project in Cuba platform and was wondering how to schedule an email (reminder) depending on a training(entity) start-date attribute. So an employee in our system is reminded by mail a few days before his training starts.

I have the email part down and am able to send emails targeted to the manager of a certain training when an employee presses a request button to join the training.

Thanks in advance!

Hi.
Create a Managed Bean and add a method with the @Authenticated and @Transactional annotations to it. In this method create a query to select employees from the database with a necessary date and send emails to them using the EmailerAPI bean: https://doc.cuba-platform.com/manual-6.7/email_sending_methods.html

Create a scheduled task from the Scheduled Tasks browser (Administration → Scheduled Tasks).
Fill in the following fields:

  • Bean name. Set the name of the Managed Bean that you have created previously.
  • Method name. Set the name of the method that you have created previously.
  • Period, sec. Set the task execution interval in seconds.

Save the scheduled task and click the Activate button.

Note that cuba.schedulingActive application property should be set to true to enable tasks processing.

Remember, that this task sends emails every time it is run and you should prevent repeated email sending.

If you still have any questions, feel free to ask them here.

Regards,
Nataly.

Hi Natalia thanks for the reply!

I think I understand what you are telling me although I am still a little puzzeled as to where and how I make this managed bean do I make my current Training entity a managed bean as described on the explanation page by adding a particular import and some other tags? Or is it a new entity that I first need to add to the data model making this a managed bean? This is not really explained in the Making a managed bean dev manual page.

Kind Regards,
Wissam

Hi!
You have to create a Java class, that placed in the com.company.{project_name}.core package using IDE (Eclipse, IntelliJ Idea). To make this class a managed bean, add the @org.springframework.stereotype.Component annotation to the Java class. Add a method with the @Authenticated and @Transactional annotations to the class.

Hi,
Thanks for the support,

I found the correct query for my problem but am struggling to use the data the query returns.
My query returns the table of emails of users that have a training the next day with the “start date” of their training and the “description” of their training. I know how the email sending works. but how do I talk to that query data to place it in the email?

For instance if I get 3 rows back with each row containing an “EMAIL” (of the user who is attending a particular training), a “DESCRIPTION” (title of this particular training) & a “START_DATE” (start date of that particular training this employee is attending)

How do I make my emailer send seperate mails to each email address in the table with the mail containing info from that email addresses particular row.

My code:

package com.company.hrtrainingplatform.core;

import com.company.hrtrainingplatform.entity.Employee;
import com.haulmont.cuba.core.app.EmailService;
import com.haulmont.cuba.core.global.DataManager;
import com.haulmont.cuba.core.global.EmailInfo;
import com.haulmont.cuba.core.global.LoadContext;
import com.haulmont.cuba.gui.components.Table;
import com.haulmont.cuba.security.app.Authenticated;
import com.haulmont.cuba.security.entity.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetImpl;
import com.mysql.jdbc.Statement;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.inject.Inject;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

@Component(ReminderMailCheck.NAME)
public class ReminderMailCheck {
    public static final String NAME = "hrtrainingplatform_ReminderMailCheck";

    @Inject
    private DataManager dataManager;
    @Inject
    protected EmailService emailService;

    @Authenticated
    @Transactional
    public void checkReminders(Connection con){


//        LoadContext<> loadContext = LoadContext.createQuery("select s.email,e.user_id,l.employee_id,t.description,t.START_DATE\n" +
//                        "from HRTRAININGPLATFORM_TRAINING t,HRTRAININGPLATFORM_TRAINING_EMPLOYEE_LINK l,HRTRAININGPLATFORM_EMPLOYEE e,SEC_USER s\n" +
//                "where l.TRAINING_ID=t.ID and l.EMPLOYEE_ID=e.ID and e.USER_ID=s.ID and CURRENT_DATE=DATE_SUB(CAST(t.START_DATE AS DATE), INTERVAL 1 DAY)");
//                ^^^^^^^^^^^^^^^^^^^^^^^^^^ QUERRY THAT TAKES THE NECESSARY DATA FROM OUR DATABASE. DETAILS ARE IN THE POSTED QUESTION ABOVE.^^^^^^^^^^^^^^^^^
//
//        User user = dataManager.load(loadContext);
//
//     --------MAIL FUNCTIONALITY BELOW------
//
//    private void sendByEmail() {
//
//
//        String trainingDesc = ;--------------problem here (data from query needed)
//        UUID trainingUUID = ;--------------problem here (same as last)
//
//        EmailInfo emailInfo = new EmailInfo(
//
//                , // email address --------------problem here (same as last)
//                "Training Request: "+ trainingDescription, // subject --------------problem here (same as last)
//                null, // the "from" address will be taken from the "cuba.email.fromAddress" app property
//
//                "Reminder Mail boddy" // body template
//        );
//
//        sendByEmail();
//        emailService.sendEmailAsync(emailInfo);
    }
}

Not sure if I am on the right track but hope you can help me.
I also don’t really get how LoadContext works here but it was used in another Bean that I have so I thought it might somehow work in this file as wel but I don’t really know how it works.

Kind regards,
Wissam

Hey @nsrwissam!

You can find the code example in the following documentation Running SQL Queries. Use еhe first example.
Add create EmailInfo() and call sendEmailAsync() in a loop. And it is better to carry out creation and sending in separate methods and cause them in a cycle consistently.

Regards,
Kirill.

Thanks a lot. This solved my problem and worked like a charm.
Final implementation:

ReminderMailCheckBean.java

package com.company.hrtrainingplatform.core;

import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Query;
import com.haulmont.cuba.core.app.EmailService;
import com.haulmont.cuba.core.global.EmailInfo;
import com.haulmont.cuba.security.app.Authenticated;
import groovy.transform.ToString;
import org.eclipse.persistence.jpa.jpql.parser.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.ISODateTimeFormat;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.inject.Inject;
import java.text.DateFormat;
import java.util.Iterator;
import java.util.List;

@Component(ReminderMailCheckComponent.NAME)
public class ReminderMailCheckBean implements ReminderMailCheckComponent {

    @Inject
    protected EmailService emailService;
    @Inject
    private Persistence persistence;

    @Authenticated
    @Transactional
    @Override
    public void checkReminders(){
        Query query = persistence.getEntityManager().createNativeQuery(
                "select s.email,s.name,t.description,t.START_DATE,t.END_DATE\n" +
                        "from HRTRAININGPLATFORM_TRAINING t,HRTRAININGPLATFORM_TRAINING_EMPLOYEE_LINK l,HRTRAININGPLATFORM_EMPLOYEE e,SEC_USER s\n" +
                        "where l.TRAINING_ID=t.ID and l.EMPLOYEE_ID=e.ID and e.USER_ID=s.ID and CURRENT_DATE=DATE_SUB(CAST(t.START_DATE AS DATE), INTERVAL 1 DAY)");

        List list = query.getResultList();


        for (Iterator it = list.iterator(); it.hasNext(); ) {
            Object[] row = (Object[]) it.next();
            String email = (String) row[0];
            String empName = (String) row[1];
            String trainigDesc = (String) row[2];
            String start_date = row[3].toString();
            String end_date = row[4].toString();
            sendByEmail(email,empName,trainigDesc,start_date,end_date);
        }
    }

    // MAIL FUNCTIONALITY
    private void sendByEmail(String email, String empName, String trainingDesc, String start_date, String end_date) {

        EmailInfo emailInfo = new EmailInfo(
                email,
                "Training Reminder: "+ trainingDesc, // subject
                null, // the "from" address will be taken from the "cuba.email.fromAddress" app property

                "Hello "+empName+",\n\nThis is a reminder for your upcoming training: "+trainingDesc+"\nThis training takes place on: "
                        +start_date+"\nand ends on: "+end_date+".\n\nThis is a no-reply mail\nHave a nice day."// body template
        );
        emailService.sendEmailAsync(emailInfo);
    }
}

ReminderMailCheckComponent.java which is the interface so it is visible for task scheduler.

package com.company.hrtrainingplatform.core;

import com.haulmont.cuba.security.app.Authenticated;
import org.springframework.transaction.annotation.Transactional;


public interface ReminderMailCheckComponent {
    String NAME = "hrtrainingplatform_ReminderMailCheckComponent";

    @Authenticated
    @Transactional
    void checkReminders();
}
1 Like

&

TIP! Take out in message pack for i18n. See messages