/**
* Create Source table for Compare CSV Files Data
* @param filePath
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void getFileContentSRC(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
CSVParser parser = new CSVParser(new FileReader(filePath), CSVFormat.DEFAULT.withSkipHeaderRecord());
/*sql = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_SRC’;EXCEPTION WHEN OTHERS THEN NULL;END; “;
ps = conn.prepareStatement(sql);
ps.executeUpdate(sql);*/
sql = “CREATE TABLE ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”(MSISDN VARCHAR2(20)) ” ;
try(PreparedStatement ps = conn.prepareStatement(sql)){
ps.executeUpdate(sql);
}
sql = “INSERT INTO ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”(MSISDN) VALUES (?)”;
try(PreparedStatement ps = conn.prepareStatement(sql)){
final int batchSize = 1000;
int count = 0;
for (CSVRecord record : parser) {
ps.setString(1, record.get(0));
ps.addBatch();
if(++count % batchSize == 0) {
ps.executeBatch();
}
}
parser.close();
ps.executeBatch(); // insert remaining records
ps.close();
//conn.close();
}
}
/**
* Create Destination Table for Compare CSV files Data
* @param filePath
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void getFileContentDST(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
CSVParser parser = new CSVParser(new FileReader(filePath), CSVFormat.DEFAULT.withSkipHeaderRecord());
/*sql = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_DST’;EXCEPTION WHEN OTHERS THEN NULL;END; “;
ps = conn.prepareStatement(sql);
ps.executeUpdate(sql);*/
sql = “CREATE TABLE ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”(MSISDN VARCHAR2(20)) ” ;
try(PreparedStatement ps = conn.prepareStatement(sql)){
ps.executeUpdate(sql);
}
sql = “INSERT INTO ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”(MSISDN) VALUES (?)”;
try(PreparedStatement ps = conn.prepareStatement(sql)){
final int batchSize = 1000;
int count = 0;
for (CSVRecord record : parser) {
ps.setString(1, record.get(0));
ps.addBatch();
if(++count % batchSize == 0) {
ps.executeBatch();
}
}
parser.close();
ps.executeBatch(); // insert remaining records*/
ps.close();
}
//conn.close();
}
/**
* Generate Log Files
* @param filePath
* @param conn
* @throws Exception
*/
public void generateLogFiles(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
//Statement ps=null;
String sql=””;
ResultSet resultSet = null;
sql = “SELECT ROWNUM AS ROWNR,’OK’ AS STATUS,S.MSISDN FROM ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+” S,ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+” D WHERE S.MSISDN=D.MSISDN ORDER BY ROWNR “;
try(Statement ps = conn.createStatement()){
resultSet = ps.executeQuery(sql);
ResultSetMetaData meta = resultSet.getMetaData() ;
int numberOfColumns = meta.getColumnCount() ;
String dataHeaders = “” + meta.getColumnName(1) + “” ;
for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
dataHeaders += “,” + meta.getColumnName(i) + “” ;
}
String[] tableHeader = dataHeaders.split(“,”) ;
CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator(“\n”);
String[] headers = format.getHeader();
String FILE_NAME = filePath;
File file = new File(FILE_NAME);
FileWriter fw = new FileWriter(file);
CSVPrinter printer = new CSVPrinter(fw, format.withFirstRecordAsHeader());
for (int i = 0; i < tableHeader.length; i++) {
printer.print(tableHeader[i]);
}
printer.println();
printer.printRecords(resultSet);
fw.close();
printer.close();
}
//conn.close();
}
/**
* Generate Bad Files
* @param filePath
* @param conn
* @throws Exception
*/
public void generateBadFiles(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
ResultSet resultSet = null;
sql = “SELECT ROWNUM AS ROWNR,’2627′ AS ERROR_CODE,’NOT_LOADED’ AS ERROR_REASON,MSISDN FROM ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+” WHERE MSISDN NOT IN (SELECT MSISDN FROM ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”) ORDER BY ROWNR”;
try(Statement ps = conn.createStatement()){
resultSet = ps.executeQuery(sql);
ResultSetMetaData meta = resultSet.getMetaData() ;
int numberOfColumns = meta.getColumnCount() ;
String dataHeaders = “” + meta.getColumnName(1) + “” ;
for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
dataHeaders += “,” + meta.getColumnName(i) + “” ;
}
String[] tableHeader = dataHeaders.split(“,”) ;
CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator(“\n”);
String[] headers = format.getHeader();
String FILE_NAME = filePath;
File file = new File(FILE_NAME);
FileWriter fw = new FileWriter(file);
CSVPrinter printer = new CSVPrinter(fw, format.withFirstRecordAsHeader());
for (int i = 0; i < tableHeader.length; i++) {
printer.print(tableHeader[i]);
}
printer.println();
printer.printRecords(resultSet);
fw.close();
printer.close();
//conn.close();
}
}
/**
* This will drop the compare tables Source and Destination tables
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void dropComparisonTables(Connection conn,String chunkId,String subsetId) throws Exception
{
String sqlSource=””;
String sqlDestination=””;
sqlSource = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”‘;EXCEPTION WHEN OTHERS THEN NULL;END; “;
try(PreparedStatement psSource = conn.prepareStatement(sqlSource)){
psSource.executeUpdate(sqlSource);
}
sqlDestination = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”‘;EXCEPTION WHEN OTHERS THEN NULL;END; “;
try(PreparedStatement psDestination = conn.prepareStatement(sqlDestination)){
psDestination.executeUpdate(sqlDestination);
}
//conn.close();
}