package javaUDF;
import java.sql.*;
public class Java_UDFs {
private static jdbcAccess datasource;
private static Connection conn;
private static Statement sqlstmt;
private static String library = "CMLIB.";
private static String library2 = "LANDREWS.";
public static void addCourtCosts(String CasPre, String CasNum) {
try {
datasource = new jdbcAccess();
datasource.connect();
conn = datasource.getConnection();
sqlstmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String stmt = "SELECT COUNT(*) As DocketFee FROM " + library + "CMPSNTNC WHERE DKTTYP = 'CC' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;
ResultSet rs = sqlstmt.executeQuery(stmt);
rs.next();
if (rs.getString("DocketFee").equalsIgnoreCase("0")) //add check for felony charge exit if found
{
System.out.println(CasPre + CasNum);
System.out.println("No fee assessed - checking next criteria");
stmt = "SELECT COUNT(*) As NonPAChgs FROM " + library + "CMLDKTCC C INNER JOIN " + library + "CMPSTAMFE E ON C.STATUT = E.STATUT AND C.CHGABV = E.CHGABV WHERE PENALTY_ASSESS <> 'Y' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;
rs = sqlstmt.executeQuery(stmt);
rs.next();
if (!rs.getString("NonPAChgs").equalsIgnoreCase("0")) {
AssessFee(CasPre, CasNum);
return;
}
else {
stmt = "SELECT * FROM " + library + "CMPCHGMF C INNER JOIN " + library + "CMPSTAMFE E ON C.STATUT = E.STATUT AND C.CHGABV = E.CHGABV WHERE PENALTY_ASSESS = 'Y' AND MADEUPCHG <> 'Y' AND CASPRE = '" + CasPre + "' AND CASNUM = " + CasNum;
rs = sqlstmt.executeQuery(stmt);
while (rs.next())
{
System.out.println("Checking all charge records");
if ((!rs.getString("TRFIND").equalsIgnoreCase("DI")) && (!rs.getString("TRFIND").equalsIgnoreCase("NP")) && (!rs.getString("TRFIND").equalsIgnoreCase("")) && (!rs.getString("TRFIND").equalsIgnoreCase("NA"))){
if (rs.getString("PENALTY_ASSESS").equalsIgnoreCase("Y")) {
if (rs.getString("PLEACD").equalsIgnoreCase("NG")){
AssessFee(CasPre, CasNum);
return;
}
}
else {
if (!rs.getString("TRFIND").equalsIgnoreCase("")) {
AssessFee(CasPre, CasNum);
}
}
}
}
}
}
else {
System.out.println("Fee already assessed on case");
return;
}
}
catch (SQLException sql)
{
System.out.println("addCourtCosts - Sql Exception: " + sql.getMessage());
}
}
private static void AssessFee(String CasPre, String CasNum) {
try {
System.out.println("Going to AssessFee to " + CasPre+CasNum);
String stmt = "SELECT DISTINCT CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, 'CC', 20, NDDATE, " +
"LUPDAT, 'JAVA_CC', LUPDATE, HERNGDAT, STATSDAT, ENDDATE FROM " + library + "CMPSNTNC WHERE CASPRE = '" +
CasPre + "' AND CASNUM = " + CasNum + " AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || SUBSTRING" +
"(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE()";
ResultSet rs = sqlstmt.executeQuery(stmt);
if (!rs.last()){
stmt = "INSERT INTO " + library2 + "CMPSNTNC (CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, DKTTYP, AMTSET, NDDATE, LUPDAT, TUSER, LUPDATE, HERNGDAT, STATSDAT, ENDDATE) (SELECT DISTINCT CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, STSDAT, 'CC', 20, NDDATE, " +
"LUPDAT, 'JAVA_CC', LUPDATE, HERNGDAT, STATSDAT, ENDDATE FROM " + library + "CMPSNTNC WHERE CASPRE = '" +
CasPre + "' AND CASNUM = " + CasNum + " AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || SUBSTRING" +
"(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE())";
sqlstmt.executeUpdate(stmt);
}
else {
stmt = "INSERT INTO " + library2 + "CMPSNTNC (CASPRE, CASNUM, DEFSEQ, CHGSEQ, HERTYP ,HERDAT, STSCOD, " +
"STSDAT, DKTTYP, AMTSET, NDDATE, LUPDAT, TUSER, LUPDATE, HERNGDAT, STATSDAT, ENDDATE) " +
"(SELECT CASPRE, CASNUM, DEFSEQ, MIN(CHGSEQ), HERTYP, HERDAT, 1, HERDAT, 'CC', 20, 0, HERDAT, " +
"'JAVA_CC', HERNGDAT, HERNGDAT, HERNGDAT, 0 FROM " + library + "CMPHERMF WHERE CASPRE = '" +
CasPre + "' AND CASNUM = " + CasNum + "AND DATE(SUBSTRING(HERNGDAT, 5, 2) || '/' || " +
"SUBSTRING(HERNGDAT, 7, 2) || '/' || SUBSTRING(HERNGDAT, 1, 4)) = CURDATE() GROUP BY CASPRE, " +
"CASNUM, DEFSEQ, HERTYP, HERDAT, 1, HERDAT, 'CC', 20, 0, HERDAT, 'JAVA_CC', HERNGDAT, HERNGDAT" +
", HERNGDAT, 0) ";
sqlstmt.executeUpdate(stmt);
}
}
catch (SQLException sql)
{
System.out.println("AssesingFee - Sql Exception: " + sql.getMessage());
}
}
}
