Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Unanswered: Convert this class to SQL

    Guys, I'm not that smart but I am trying to convert this Java class to SQL... it's killing me... any help would be appreciated...

    Thanks,

    public class CaseStateComponentImpl implements CaseStateComponent {

    private static Log log = LogFactory.getLog(CaseStateComponentImpl.class);
    private HoldComponent holdComponent = null;
    private CaseComponent caseComponent = null;

    public String getCaseState(String caseId) {

    final String METHOD = "getCaseState ---> ";

    String caseClosedState = getCaseClosedState(caseId);
    log.debug(METHOD + "caseClosedState = " + caseClosedState);
    String caseCollectionPartnerState = getCaseCollectionPartnorState(caseId);
    log.debug(METHOD + "caseCollectionPartnerState = " + caseCollectionPartnerState);

    String caseTOPState = getCaseTOPState(caseId);
    log.debug(METHOD + "caseTOPState = " + caseTOPState);

    String caseCollectionActivitiesState = getCaseCollectionActivitiesState(caseId);
    log.debug(METHOD + "caseCollectionActivitiesState = " + caseCollectionActivitiesState);

    String debtPendingDOJState = getDebtPendingDOJState(caseId);
    log.debug(METHOD + "debtPendingDOJState = " + debtPendingDOJState);

    String caseGeneralHoldState = getCaseGeneralHoldState(caseId);
    log.debug(METHOD + "caseGeneralHoldState = " + caseGeneralHoldState);


    String caseState =
    caseCollectionPartnerState
    + " "
    + caseTOPState
    + " "
    + caseGeneralHoldState
    + " "
    + caseCollectionActivitiesState
    + " "
    + debtPendingDOJState
    + " "
    + caseClosedState;


    return caseState.trim();
    }

    public String getCaseCollectionPartnorState(String caseId) {
    String state = "";

    String query =
    "SELECT a.LOCATION_TYPE, a.PCA_ID FROM D_C_O_I a WHERE a.DEBT_OBJECTID = "
    + "(SELECT b.DEBT_OBJECTID FROM FDBT.CASE_DEBT_DEBTOR b WHERE b.CASE_ID = '"
    + caseId
    + "') ORDER BY EFFECTIVEDATE DESC";
    String resultCode = "LOCATION_TYPE";

    log.debug("getCaseCollectionPartnorState(): " + query);
    String result = doStringQuery(query, resultCode);
    if (result != null) {
    state = "At " + result;
    }

    return state;
    }

    public String getCaseTOPState(String caseId) {
    String state = "";

    String query =

    "SELECT a.AT_TOP, a.STATUS_AT_TOP FROM CDD a WHERE a.CASE_ID = '"
    + caseId
    + "'";
    String resultCode = "AT_TOP";

    String result = doStringQuery(query, resultCode);
    if (result != null && !result.equalsIgnoreCase("N")) {

    state=result;
    }

    return state;
    }

    public String getCaseGeneralHoldState(String caseId) {
    String state = "";

    try {
    Case theCase = getCaseComponent().findCaseByFeddebtCaseId(caseId) ;
    List holds =
    getHoldComponent().findActiveHoldByFeddebtDebtId(
    theCase.getDebt().getFeddebtDebtId());
    if (holds != null && holds.size() > 0) {
    Iterator it = holds.iterator();
    while (it.hasNext()) {
    Hold thisHold = (Hold) it.next();
    if (thisHold.getHoldType().equals(HoldType.SYSTEM)
    || thisHold.getHoldType().equals(HoldType.MANUAL)) {
    state = "Held";
    break;
    }
    }
    }
    } catch (Exception e) {
    log.debug("getCaseGeneralHoldState(): " + e);
    }

    return state;
    }

    public String getCaseCollectionActivitiesState(String caseId) {
    String state = "";

    String result = getAWGState(caseId);
    if (result != null && result.trim().length() != 0) {
    state += result + " ";
    }

    result = getPAState(caseId);
    if (result != null && result.trim().length() != 0) {
    state += result + " ";
    }

    result = getARState(caseId);
    if (result != null && result.trim().length() != 0) {
    state += result + " ";
    }

    result = getDisputeState(caseId);
    if (result != null && result.trim().length() != 0) {
    state += result + " ";
    }

    result = getRecallState(caseId);
    if (result != null && result.trim().length() != 0) {
    state += result;
    }

    return state;
    }

    private String getDebtPendingDOJState(String caseId) {
    String state = "";

    String query =
    "SELECT a.state FROM Drtdoj a WHERE a.state = '0' and a. unmarked_date is null and a.DEBT_OBJECTID = "
    + "(SELECT b.DEBT_OBJECTID FROM CDD b WHERE b.CASE_ID = '"
    + caseId
    + "') ";
    String resultCode = "STATE";

    log.debug("getCaseCollectionPartnorState(): " + query);
    String result = doStringQuery(query, resultCode);
    if (result != null) {
    state = "Pending DOJ";
    }

    return state;
    }

    private String doStringQuery(String query, String resultCode) {
    String result = null;
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rslt = null;
    String currPcaId = null;
    String status_At_TOP = null;

    try {
    con = ConnectionHelper.getConnection();
    stmt = con.prepareStatement(query);
    rslt = stmt.executeQuery();

    if (rslt.next())
    {
    result = rslt.getString(resultCode);

    if (resultCode.equalsIgnoreCase("AT_TOP"))
    {
    if (result.equalsIgnoreCase("Y"))
    {
    status_At_TOP=rslt.getString("STATUS_AT_TOP");
    result = "At TOP("+status_At_TOP+")";
    }
    }

    if (result.equalsIgnoreCase("PCA"))
    {

    currPcaId=rslt.getString("PCA_ID");
    result = "PCA1-"+currPcaId;

    while (rslt.next())
    {
    if (rslt.getString(resultCode).equalsIgnoreCase("PCA" ))
    {
    "+rslt.getString("PCA_ID");
    result = "PCA2-"+currPcaId;

    break;
    }
    }
    }
    }
    } catch (SQLException se) {
    // SQL exception
    log.error(se.toString());
    log.error(se.getMessage());
    } finally {

    try { if (rslt != null) rslt.close(); } catch (SQLException se) { }
    try { if (stmt != null) stmt.close(); } catch (SQLException se) { }
    try { if (con != null) con.close(); } catch (SQLException se) { }
    }

    return result;
    }

    private String getAWGState(String caseId) {
    String result = null;

    Calendar current = DateTimeUtil.getToday();

    String query =
    "SELECT * FROM AWGC a WHERE a.CASE_ID='"
    + caseId

    + "' AND a.AWG_STAT_CD<>'T'";

    String resultCode = "OBJECTID";

    log.debug("getAWGState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "AWG";
    } else {

    query =
    "SELECT * FROM CAWG a WHERE a.AWG_END_DATE IS NULL"
    + " AND a.THECASE_OBJECTID = "
    + "(SELECT b.OBJECTID FROM CDD b WHERE b.CASE_ID = '"
    + caseId
    + "')";

    log.debug("getAWGState() from old awg table: " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "AWG";
    }
    }

    return result;
    }

    private String getPAState(String caseId) {
    String result = null;
    String query =
    "SELECT * FROM PACASE_INCLD a WHERE a.CASE_ID = '"
    + caseId
    + "' AND a.PAYMENTAGREEMENT_OBJECTID IN "
    + "(SELECT b.OBJECTID FROM PAINFO b WHERE b.AGR_STATUS IN ('A', 'P', 'W'))";
    String resultCode = "OBJECTID";

    log.debug("getPAState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "P/A";
    }

    return result;
    }

    private String getARState(String caseId) {
    String result = null;

    Calendar current = DateTimeUtil.getToday();
    Date currentDate = new Date(current.getTimeInMillis());
    String query =
    "SELECT * FROM A_RSL_CASE a WHERE a.CASE_ID = '"
    + caseId
    + "' AND a.ADMINRESOLUTION_OBJECTID IN "
    + "(SELECT b.OBJECTID FROM A_RSL b WHERE b.ADMIN_RES_STATE_DESC = 'P')";
    String resultCode = "OBJECTID";

    log.debug("getARState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "A/R";
    }

    return result;
    }

    private String getDisputeState(String caseId) {
    String result = null;
    //Calendar current = Calendar.getInstance();
    Calendar current = DateTimeUtil.getToday();
    Date currentDate = new Date(current.getTimeInMillis());
    String query =
    "SELECT * FROM C_DISPT a WHERE a.CASE_ID = '"
    + caseId
    + "' AND a.DISPUTE_STATUS IN ('PENDING', 'PENDING_CC')";
    String resultCode = "OBJECTID";

    log.debug("getDisputeState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "Dispute";
    }

    return result;
    }

    private String getRecallState(String caseId) {
    String result = null;

    Calendar current = DateTimeUtil.getToday();
    Date currentDate = new Date(current.getTimeInMillis());
    String query =
    "SELECT * FROM C_R_I a WHERE a.CASE_ID = '"
    + caseId
    + "' AND a.RECALL_STATUS = 'P'";
    String resultCode = "OBJECTID";

    log.debug("getRecallState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    result = "Recall";
    }

    return result;
    }
    /*

    */
    private String getCaseClosedState(String caseId) {
    String result = null;

    String query =
    "SELECT a.IS_CLOSED FROM CDD a WHERE a.CASE_ID = '"
    + caseId
    + "'";
    String resultCode = "IS_CLOSED";

    log.debug("getCaseCloseState() : " + query);
    result = doStringQuery(query, resultCode);
    if (result != null) {
    if ("Y".equals(result.trim()))
    {
    result = "CLOSED";
    }
    else {
    result ="";
    }
    }
    return result;
    }
    /**
    * @return
    */

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kjjaxson View Post
    I am trying to convert this Java class to SQL... it's killing me... any help would be appreciated...
    Would it help if I told you that the problem as stated does not have a solution? It's like trying to convert a toaster into a vacuum cleaner.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •