• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

0
melwong
Asked:
melwong
  • 4
  • 4
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now