public void insertGrandprizePoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting inserting Grand Prize point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[INSERT GRANDPRIZE] Started: " + getCurrentTimeStamp());
// INSERT MONTHLY - Supposed to be executed on INIT w/CREATE TABLE function
// Binding a single input stream CSV reader & its buffer
CSVReader grandprizeReader =
new CSVReader(
new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
String[] grandprize;
String insertGrandprizeQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((grandprize = grandprizeReader.readNext()) != null) {
// System.out.println(monthly[0] + monthly[1] + monthly[2]);
insertGrandprizeQuery =
"INSERT INTO tbl_points_grandprize (point_cardno, point_grandprize) VALUES ('"
+ grandprize[1].trim()
+ "', "
+ grandprize[2].trim()
+ ");";
stm.addBatch(insertGrandprizeQuery);
// stm = con.createStatement();
// affectedRow = stm.executeUpdate(updateGrandprizeQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statGrandprize = true;
System.out.println("Grand Prize Records Inserted:" + recordCounter);
System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[INSERT GRANDPRIZE] Finished: " + getCurrentTimeStamp());
}
public void updateGrandprizePoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting updating grand prize point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE GRANDPRIZE] Started: " + getCurrentTimeStamp());
// UPDATE GRANDPRIZE
CSVReader grandprizeReader =
new CSVReader(
new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
String[] grandprize;
String updateGrandprizeQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((grandprize = grandprizeReader.readNext()) != null) {
// System.out.println(grandprize[0] + grandprize[1] + grandprize[2]);
updateGrandprizeQuery =
"UPDATE tbl_points SET point_grandprize = "
+ grandprize[2].trim()
+ " WHERE point_cardno = '"
+ grandprize[1].trim()
+ "';";
stm.addBatch(updateGrandprizeQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statGrandprize = true;
System.out.println("Updated Grand Prize Records: " + recordCounter);
System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE GRANDPRIZE] Finished: " + getCurrentTimeStamp());
}
public void updateMonthlyPoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting updating monthly point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE MONTHLY] Started: " + getCurrentTimeStamp());
// UPDATE MONTHLY
// Binding a single input stream CSV reader & its buffer
CSVReader monthlyReader =
new CSVReader(new FileReader(workingDir + monthlyFile), recSeparator, escChar, headerLine);
String[] monthly;
String updateMonthlyQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((monthly = monthlyReader.readNext()) != null) {
// System.out.println(monthly[0] + monthly[1] + monthly[2]);
updateMonthlyQuery =
"UPDATE tbl_points SET point_monthly = "
+ monthly[2].trim()
+ " WHERE point_cardno = '"
+ monthly[1].trim()
+ "';";
stm.addBatch(updateMonthlyQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statMonthly = true;
System.out.println("Updated Monthly Records:" + recordCounter);
System.out.println("Monthly Records Update Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE MONTHLY] Finished: " + getCurrentTimeStamp());
}
public void createTruncateUnitedPointTable() {
System.out.println(
"Starting creating a table if not exists yet or truncating non-empty table..");
System.out.println("[TABLE] Started: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(), "[TABLE] Started: " + getCurrentTimeStamp());
// CREATE TABLE & TRUNCATE AL PREV. DATA
String createPointTableQuery =
"CREATE TABLE IF NOT EXISTS `tbl_points` ("
+ " `point_id` int(11) NOT NULL NOT NULL DEFAULT '0',"
+ " `point_no` int(7) NOT NULL DEFAULT '0',"
+ " `point_accnum` varchar(20) NOT NULL DEFAULT '0',"
+ " `point_cardno` varchar(16) NOT NULL DEFAULT '0',"
+ " `point_monthly` int(6) NOT NULL DEFAULT '0',"
+ " `point_quarterly` int(6) NOT NULL DEFAULT '0',"
+ " `point_grandprize` int(6) NOT NULL DEFAULT '0',"
+ " `deleted` int(1) NOT NULL DEFAULT '0',"
+ " `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP"
+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"
+ "ALTER TABLE `tbl_points` ADD PRIMARY KEY (`point_id`);"
+ "ALTER TABLE `tbl_points` MODIFY `point_id` int(11) NOT NULL AUTO_INCREMENT;";
String truncatePointTableQuery = "TRUNCATE TABLE `tbl_points`;";
try {
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
stm = con.createStatement();
affectedRow = stm.executeUpdate(createPointTableQuery);
affectedRow = stm.executeUpdate(truncatePointTableQuery);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
statCreate = true;
}
System.out.println("[TABLE] Finished: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(), "[TABLE] Finished: " + getCurrentTimeStamp());
}
public PointUpdaterDaemon() {
con = null;
stm = null;
// rs = null;
// affectedRow = 0;
recordCounter = 0;
statCreate = false;
statMonthly = false;
statQuarterly = false;
statGrandprize = false;
workingDir = System.getProperty("user.dir");
monthlyFile = PropertiesLoader.getProperty("POINT_MONTHLY_FILE");
quarterlyFile = PropertiesLoader.getProperty("POINT_QUARTERLY_FILE");
grandprizeFile = PropertiesLoader.getProperty("POINT_GRANDPRIZE_FILE");
recSeparator = '|';
escChar = '\'';
headerLine = 1;
// Establishing a Single DB Connection.
// This Connection is usable across app life-cycle.
try {
db_object = new MysqlConnect();
con = db_object.getConnection();
} catch (ClassNotFoundException nfe) {
nfe.printStackTrace();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}
