import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
public class DataUpdater {
private static String driverClassName;
private static String url;
private static String user;
private static String password;
private static int upsellDays;
private static Connection conn;
public static void main( String[] args )
{
if (args.length < 2) {
System.out.println("Usage: DataUpdater propertyFile(path) restaurantIds(comma separated list)");
System.exit(1);
}
//System.out.println("args[0]: "+args[0] + "; args[1]: " + args[1]);
Properties prop = new Properties();
Date start = new Date();
String configFile = args[0];
String[] ids = args[1].split(",");
System.out.println("---------------------------------------");
System.out.println("- Updating data warehouse and upsells -");
System.out.println("---------------------------------------");
System.out.println("Beginning update process at " + start.toString());
try {
//load properties file to get db connection info
prop.load(new FileInputStream(configFile));
driverClassName = prop.getProperty("driverClassName");
url = prop.getProperty("url");
user = prop.getProperty("username");
password = prop.getProperty("password");
upsellDays = Integer.parseInt(prop.getProperty("upsellDays"));
} catch (IOException ex) {
ex.printStackTrace();
}
// get the current date, to determine what date to start calculating the upsells
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -upsellDays);
String startDate = dateFormat.format(cal.getTime());
try {
conn = DriverManager.getConnection(
"jdbc:"+url, user, password); // MySQL
Statement stmt = conn.createStatement();
System.out.println("Updating data warehouse");
updateDataWarehouse();
System.out.println("Data Warehouse Tables updated");
// iterate though list of restaurants (passed as arg[1]
for (int i = 0; i < ids.length; i++ ) {
int id = Integer.parseInt(ids[i]);
System.out.println("------------------------------");
System.out.println("Updating for restaurant id "+id);
updateRestaurant(id, startDate);
System.out.println(" ... completed\n");
}
} catch(SQLException ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception e) {
}
}
printElapsedTime(start, " beginning of program.");
System.out.println("---------------------------------------");
System.out.println("- Process Complete -");
System.out.println("---------------------------------------");
}
private static void printElapsedTime(Date start, String from) {
long seconds = (new Date().getTime()-start.getTime())/1000;
long sec = seconds % 60;
long min = seconds / 60;
System.out.println("Elapsed time from "+from+": "+ min+ " minutes, "+sec+" seconds.");
System.out.println();
}
private static void updateDataWarehouse() {
String sql = "{call sp_dw_update_data_warehouse()}";
try {
Statement stmt = conn.createStatement();
stmt.execute(sql);
} catch(SQLException ex) {
ex.printStackTrace();
}
}
private static void updateRestaurant(int restId, String startDate) {
Date start = new Date();
try {
// setup the regular upsells table and get the ids
String sql = "{call sp_setup_upsell_table("+restId+",'"+startDate+"')}";
System.out.println("sql: "+sql);
PreparedStatement stmt = conn.prepareStatement(sql);
ArrayList<Integer> ids = new ArrayList<Integer>();
ResultSet rset = stmt.executeQuery(sql);
int idCount = 0;
while (rset.next()) {
ids.add(rset.getInt("menu_item_id"));
idCount++;
//System.out.println("added item "+idCount);
}
System.out.println("-- Set up upsell table and got "+idCount+" ids");
updateUpsells(ids, "{call sp_update_upsells(?)}");
// iterate through the regular items to set the upsells
/* int counter = 0;
Date ups = new Date();
for (Iterator<Integer> i = ids.iterator(); i.hasNext(); ) {
Integer id = i.next();
sql = "{call sp_update_upsells("+id+")}";
stmt = conn.prepareStatement(sql);
stmt.execute();
System.out.print(".");
if (++counter%40 == 0) System.out.print("\n");
}
*/
// remove the category inappropriate upsells
System.out.println("\n-- Completed dynamic restaurant upsells");
sql = "{call sp_clean_upsell_categories()}";
PreparedStatement stmt2 = conn.prepareStatement(sql);
stmt2.execute();
System.out.println("-- Cleaned bad categories");
// setup the regular upsells table and get the ids
sql = "{call sp_setup_manual_upsell_table(" +restId + ")}";
System.out.println("sql: "+sql);
stmt2 = conn.prepareStatement(sql);
rset = stmt2.executeQuery();
ArrayList<Integer> manualIds = new ArrayList<Integer>();
while (rset.next()) {
manualIds.add(rset.getInt("menu_item_id"));
}
System.out.println("-- Set up manual upsell table and got "+manualIds.size()+" ids");
// iterate through the regular items to set the upsells
updateUpsells(manualIds, "{call sp_update_manual_upsells(?)}");
/* counter = 0;
for (Iterator<Integer> i = manualIds.iterator(); i.hasNext(); ) {
Integer id = i.next();
sql = "{call sp_update_manual_upsells("+id+")}";
stmt2 = conn.prepareStatement(sql);
stmt2.execute();
System.out.print(".");
if (++counter%40 == 0) System.out.print("\n");
}
*/
System.out.println("\n-- Completed manual upsells");
} catch (Exception e) {
e.printStackTrace();
}
printElapsedTime(start, "the start of processing Restaurant id"+restId);
}
private static void testBatchCall(List<Integer> ids, String sql) {
Date start = new Date();
System.out.println("\n--Running batched calls for "+sql);
try {
CallableStatement stmt = conn.prepareCall(sql);
for (Iterator<Integer> i = ids.iterator(); i.hasNext(); ) {
Integer id = i.next();
stmt.setInt(1, id);
if (i.hasNext())
stmt.addBatch();
}
int[] returns = stmt.executeBatch();
System.out.println("returns was "+returns.length+" items long");
}catch (SQLException s) {
s.printStackTrace();
}
long seconds = (new Date().getTime()-start.getTime())/1000;
long sec = seconds % 60;
long min = seconds / 60;
System.out.println("Completed {"+sql+"}\nElapsed time: "+ min+ "minutes, "+sec+" seconds.\n");
}
private static void updateUpsells(List<Integer> ids, String sql) {
Date start = new Date();
int counter = 0;
for (Iterator<Integer> i = ids.iterator(); i.hasNext(); ) {
Integer id = i.next();
try {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
stmt.execute();
System.out.print(".");
if (++counter%40 == 0) System.out.print("\n");
} catch (SQLException s) {
s.printStackTrace();
}
}
System.out.println();
printElapsedTime(start, " setup upsells");
}
}