147 lines
5.1 KiB
Java
147 lines
5.1 KiB
Java
package loadreportfile.dao;
|
|
|
|
import java.sql.CallableStatement;
|
|
import java.sql.Connection;
|
|
import java.sql.DriverManager;
|
|
import java.sql.PreparedStatement;
|
|
import java.sql.SQLException;
|
|
import java.util.List;
|
|
|
|
public class ReportFileProcessing {
|
|
public String insertBulkAccMap(List<String> accntdetailsList, String pacsId, String tellerId, String dbUrl,
|
|
String dbUsr, String dbPass, String dbSchema, String fileName) {
|
|
Connection con = null;
|
|
PreparedStatement ps = null;
|
|
int i = 0;
|
|
String[] accNo = null;
|
|
CallableStatement proc = null;
|
|
CallableStatement procall = null;
|
|
String outMsg = "";
|
|
String res = "";
|
|
System.out.println("File list: " + fileName);
|
|
|
|
try {
|
|
Class.forName("oracle.jdbc.driver.OracleDriver");
|
|
con = DriverManager.getConnection(dbUrl, dbUsr, dbPass);
|
|
con.setAutoCommit(false);
|
|
|
|
try {
|
|
while (i <= accntdetailsList.size() - 1) {
|
|
accNo = ((String) accntdetailsList.get(i)).split("\\:");
|
|
String acn1 = accNo[0];
|
|
String acn2 = accNo[1];
|
|
String acn3 = accNo[2];
|
|
|
|
ps = con.prepareStatement("insert into " + dbSchema
|
|
+ ".bulk_acct_map (pacs_id, key_1, cbs_acct, teller_id, map_dt, map_tym, status, id_typ, id_no, bank_cif, mark_type) values (?,?,?,?,(select system_date from system_date),to_char(SYSTIMESTAMP, 'DD-MON-RRHH12:MI:SS'),'PENDING','','',?,'')");
|
|
|
|
ps.setString(1, pacsId);
|
|
ps.setString(2, accNo[0]);
|
|
ps.setString(3, accNo[1]);
|
|
ps.setString(4, tellerId);
|
|
ps.setString(5, accNo[2]);
|
|
|
|
ps.executeQuery();
|
|
con.commit();
|
|
ps.close();
|
|
i++;
|
|
}
|
|
|
|
} catch (SQLException ex) {
|
|
ex.printStackTrace();
|
|
try {
|
|
if (ps != null) {
|
|
ps.close();
|
|
}
|
|
} catch (SQLException ep) {
|
|
ex.printStackTrace();
|
|
} catch (Exception e) {
|
|
ex.printStackTrace();
|
|
}
|
|
String msg = "SQL error in inserting bulk_acct_map";
|
|
|
|
try {
|
|
procall = con.prepareCall("{ call operations2.cbs_to_ipks(?,?,?) }");
|
|
|
|
procall.setString(1, fileName);
|
|
procall.setString(2, msg);
|
|
procall.registerOutParameter(3, 12);
|
|
|
|
procall.executeUpdate();
|
|
|
|
res = procall.getString(3);
|
|
|
|
System.out.println(res);
|
|
System.out.println("proc executed");
|
|
con.commit();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
} finally {
|
|
|
|
try {
|
|
if (procall != null) {
|
|
procall.close();
|
|
}
|
|
} catch (SQLException ep) {
|
|
ep.printStackTrace();
|
|
} catch (Exception e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
try {
|
|
proc = con.prepareCall("{ call " + dbSchema + ".operations1.BULK_ACCT_MAPPING_auto(?,?,?,?) }");
|
|
|
|
proc.setString(1, pacsId);
|
|
proc.setString(2, tellerId);
|
|
proc.registerOutParameter(3, 12);
|
|
proc.registerOutParameter(4, 2);
|
|
|
|
proc.executeUpdate();
|
|
|
|
System.out.println("Procedure executed");
|
|
|
|
outMsg = proc.getString(3);
|
|
con.commit();
|
|
} catch (SQLException ex) {
|
|
ex.printStackTrace();
|
|
try {
|
|
if (ps != null) {
|
|
ps.close();
|
|
}
|
|
if (proc != null) {
|
|
proc.close();
|
|
}
|
|
} catch (SQLException ep) {
|
|
ex.printStackTrace();
|
|
} catch (Exception e) {
|
|
ex.printStackTrace();
|
|
}
|
|
|
|
return "Procedure error in bulk account mapping";
|
|
}
|
|
} catch (Exception e) {
|
|
e.printStackTrace();
|
|
} finally {
|
|
try {
|
|
if (proc != null) {
|
|
proc.close();
|
|
}
|
|
if (ps != null) {
|
|
ps.close();
|
|
}
|
|
if (con != null) {
|
|
con.close();
|
|
}
|
|
} catch (SQLException ex) {
|
|
ex.printStackTrace();
|
|
} catch (Exception e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
return outMsg;
|
|
}
|
|
}
|