Create SQL statement for monthly subscription

Hello,

I need to do this,
-send an SMS msg (text msg) to my subscribers to inform them of their monthly subscription
-I need to send only once every month

My current code in Java / MySQL is attached. As you can see my code only works for 1st month as i used SUB_PERIOD_DAYS = 31 variable.

How may i change my SQL statement to allow this code to work for every month? I do not wish to add another dateTime column that states the last time an SMS msg was sent out.

My SQL statement,
sqlStr = "SELECT DISTINCT phoneNumber FROM "
                              + tablePrefix
                              + "_ffl_users WHERE inactive=0 AND TO_DAYS(NOW()) - TO_DAYS(registrationTime) = "
                              + SUB_PERIOD_DAYS;


/*
 * Classname: SubscriptionRenewalDelivery.java Description: Class to
 * renew subscribers' subscription 
 * Author: melwong Date started: 11-Mar-2004
 * 
 * Version: 1.0
 */
 
package com.jm.ffl;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.apache.commons.dbutils.DbUtils;
 
import com.jm.config.DbConfig;
import com.jm.config.FileConfig;
import com.jm.config.PropertyFileReader;
import com.jm.fortuneteller.FortuneTellerException;
import com.jm.gateway.OutboundResponseMaxis;
import com.jm.util.KeywordProcessor;
 
public class RenewSubscription {
 
	public static final int SUB_PERIOD_DAYS = 31;
 
	private Connection conn = null;
 
	public RenewSubscription(Connection conn) {
		this.conn = conn;
 
	}
 
	public void processDelivery(String message) {
 
		int serviceId = 0;
		int renewMsgId = 0;
		String phoneNumber = "";
		String sqlStr = "";
		String tablePrefix = "";
 
		PreparedStatement statement = null;
		ResultSet rs = null;
		KeywordProcessor keyPro = null;
		OutboundResponseMaxis outRes = null;
		FflUtils fflUtils = null;
 
		keyPro = new KeywordProcessor();
		outRes = new OutboundResponseMaxis();
		fflUtils = new FflUtils();
 
		serviceId = keyPro.getServiceId(message);
		tablePrefix = fflUtils.getTablePrefix(serviceId);
		renewMsgId = fflUtils.getRenewalMsgId(serviceId);
 
		try {
 
			sqlStr = "SELECT DISTINCT phoneNumber FROM "
					+ tablePrefix
					+ "_ffl_users WHERE inactive=0 AND TO_DAYS(NOW()) - TO_DAYS(registrationTime) = "
					+ SUB_PERIOD_DAYS;
 
			statement = conn.prepareStatement(sqlStr);
			rs = statement.executeQuery();
 
			while (rs.next()) {
				phoneNumber = rs.getString(1);
 
				outRes.sendMessage("12345", renewMsgId, phoneNumber);
 
			}
 
		} catch (SQLException e) {
			throw new FflException(this.getClass().toString(), e);
		} finally {
			DbUtils.closeQuietly(rs);
			DbUtils.closeQuietly(statement);
		}
 
	}
 
	public static void main(String args[]) {
 
		String message = "";
		String absPath = "";
		PropertyFileReader readProp = null;
		DbConfig initDbConfig = null;
		RenewSubscription renewSubscription = null;
 
		Connection conn = null;
 
		if (args.length != 0) {
 
			absPath = args[0];
 
			for (int i = 1; i < args.length; i++) {
				message += args[i];
				message += " ";
			}
			message = message.trim();
 
			System.out.println("absPath=" + absPath);
			System.out.println("message=" + message);
 
		} else {
			System.err
					.println("RenewSubscription: ERROR: Invalid command line arguments.");
			System.err.println("\tRenewSubscription: Usage:");
			System.err
					.println("\tjava RenewSubscription <full webapp path> <full SMS message>");
			System.err
					.println("\tE.g: java RenewSubscription C:\\tomcat\\webapps\\jm FF");
			System.exit(1);
		}
 
		// Read properties must process first, then initialize db
		readProp = new PropertyFileReader(absPath);
 
		// To initialize the db stuffs
		initDbConfig = new DbConfig();
 
		// To get URL for WAP Push and app full path
		FileConfig.setSysConfig();
 
		try {
			DbUtils.loadDriver(DbConfig.dbDriver);
			conn = DriverManager.getConnection(DbConfig.dbUrl);
			renewSubscription = new RenewSubscription(conn);
			renewSubscription.processDelivery(message);
		} catch (SQLException e) {
			throw new FortuneTellerException("RenewSubscription", e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
	}
 
}

Open in new window

melwongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ajay-SinghCommented:
sqlStr = "SELECT DISTINCT phoneNumber FROM "
                                        + tablePrefix
                                        + "_ffl_users WHERE inactive=0 AND TO_DAYS(NOW()) - TO_DAYS(registrationTime) = "
                                        + SUB_PERIOD_DAYS;

change that to

sqlStr = "SELECT DISTINCT phoneNumber FROM "
                                        + tablePrefix
                                        + "_ffl_users WHERE inactive=0 AND TO_DAYS(NOW()) - TO_DAYS(registrationTime) = "
                                        + Calendar.getInstance().getMaximum(Calendar.DAY_OF_MONTH);
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the column of when you send the "last" sms would be very helpful for logging purposes later on anyhow, and make your query very easy actually.

do you run this every day or just once a month?

0
melwongAuthor Commented:
I prefer to run this every day, angell.

Cos diff ppl subscribe that diff day oft the month, so we only send him the msg 30 days after the day he sub and so on...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

melwongAuthor Commented:
ajay,

can u elaborate on this Calendar.getInstance().getMaximum(Calendar.DAY_OF_MONTH);. How does this work on my case?
0
Ajay-SinghCommented:
> Calendar.getInstance().getMaximum(Calendar.DAY_OF_MONTH)

Tells you max number of a given month - I believe you are looking for that. For example, for Jan month it would be 31, for April, it would be 30 and so on
0
melwongAuthor Commented:
Ajay,

No, because the registration time to today's time would grow from month to month. Using ur way would only work for 1st month.  
0
Ajay-SinghCommented:
There is another (and very simple way) to do this:

sqlStr = "SELECT DISTINCT phoneNumber FROM "
                                        + tablePrefix
                                        + "_ffl_users WHERE inactive=0 AND DAYOFMONTH(registrationTime) = "
                                        + Calendar.getInstance().get(Calendar.DAY_OF_MONTH);
0
melwongAuthor Commented:
hmmm, but then what about months that have diff days? like 31 days?
0
Ajay-SinghCommented:
> hmmm, but then what about months that have diff days? like 31 days?

Its here

+ Calendar.getInstance().get(Calendar.DAY_OF_MONTH);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.