public static String chkProvAllowed(String provId, String billSysId) { L.info( "Entered into CheckAccount.chkProvAllowed(),ProvId=" + provId + " ,billSysId=" + billSysId); String errMsg = SPProps.getBillProvPropObject().getProperty(provId.trim() + "-" + billSysId.trim()); if (errMsg == null || errMsg.trim().equals("")) return null; else return errMsg.trim(); }
private static boolean getAccountFromGeneva( String logTag, String number, int selParam, AccountVO acctVO) throws ProcessingException { L.debug( "getAccountFromGeneva():" + logTag + "Entering with number=" + number + "; type=" + selParam); InitialContext ic = null; DataSource ds = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rset = null; boolean retVal = false; String dgtID = SPProps.getProdAttrProps().getProperty("PROD_DGT"); String subID = SPProps.getProdAttrProps().getProperty("SUB_PREPAY"); if ((dgtID == null || dgtID.trim().length() < 1) || (subID == null || subID.trim().length() < 1)) { L.error( "getAccountFromGeneva():" + logTag + "During getAccountFromGeneva(): Cannot get Product ID for DGT or Sub ID for Prepay"); throw new ProcessingException( ProcessingException.GLOBAL, "During getAccountFromGeneva(): Cannot get Product ID for DGT or Sub ID for Prepay"); } String mdnQuery = "select a.customer_ref, a.event_source as mdn, b.account_num, c.bill_handling_code, " + "d.event_source as min from pvcusteventsource4 a, pvaccount11 b, pvaccountdetails4 c, " + "pvcusteventsource4 d where a.end_dtm is null and a.event_type_id = ? and " + "a.event_source = ? and a.customer_ref = b.customer_ref and b.account_num = c.account_num and " + "c.end_dat is null and a.customer_ref = d.customer_ref and d.event_type_id = ? and d.end_dtm is null"; String minQuery = "select a.customer_ref, a.event_source as min, b.account_num, c.bill_handling_code, " + "d.event_source as mdn from pvcusteventsource4 a, pvaccount11 b, pvaccountdetails4 c, " + "pvcusteventsource4 d where a.end_dtm is null and a.event_type_id = ? and " + "a.event_source = ? and a.customer_ref = b.customer_ref and b.account_num = c.account_num and " + "c.end_dat is null and a.customer_ref = d.customer_ref and d.event_type_id = ? and d.end_dtm is null"; // PrePay RBT Change // String checkPrepay = "select attribute_value from custproductattrdetails where product_id=" + // dgtID + // " and product_attribute_subid=" + subID + " and customer_ref = ?"; String checkPrepay = "select /*+ INDEX(CUSTPRODUCTATTRDETAILS CUSTPRODUCTATTRDETAILS_PK) */ count(*) from custproductattrdetails where product_id=" + dgtID + " and product_attribute_subid=" + subID + " and customer_ref = ? and attribute_value='Y' " + "and start_dat<sysdate and (end_dat is null or end_dat>sysdate)"; try { ic = new InitialContext(); ds = (DataSource) ic.lookup(dsNameG); conn = ds.getConnection(); // String mdnEventType = SPProps.getProperty("MDN_EVENT_TYPE"); // String minEventType = SPProps.getProperty("MIN_EVENT_TYPE"); // if (mdnEventType == null || mdnEventType.equals("")) // mdnEventType = "8"; // if (minEventType == null || minEventType.equals("")) // minEventType = "9"; // StringBuffer sb = new StringBuffer(); // sb.append("select a.customer_ref, a.event_source as "); // if (selParam == MDN) // sb.append("mdn,"); // else // sb.append("min,"); // sb.append( // "b.account_num, c.bill_handling_code, d.event_source as "); // if (selParam == MDN) // sb.append("min"); // else // sb.append("mdn"); // sb.append(" from pvcusteventsource4 a, pvaccount11 b,"); // sb.append("pvaccountdetails4 c, pvcusteventsource4 d where "); // sb.append("a.end_dtm is null and "); // sb.append("a.event_type_id = '"); // if (selParam == MDN) // sb.append(mdnEventType); // else // sb.append(minEventType); // sb.append("' and "); // sb.append("a.event_source = '" + number + "' and "); // sb.append("a.customer_ref = b.customer_ref and "); // sb.append("b.account_num = c.account_num and "); // sb.append("c.end_dat is null and "); // sb.append("a.customer_ref = d.customer_ref and "); // sb.append("d.event_type_id = '"); // if (selParam == MDN) // sb.append(minEventType); // else // sb.append(mdnEventType); // sb.append("' and d.end_dtm is null"); String query = null; if (selParam == MDN) query = mdnQuery; else query = minQuery; L.debug("getAccountFromGeneva():" + logTag + "SQL Statement is: " + query); pstmt = conn.prepareStatement(query); if (selParam == MDN) pstmt.setInt(1, SPProps.mdnEventTypeId); else pstmt.setInt(1, SPProps.minEventTypeId); pstmt.setString(2, number); if (selParam == MDN) pstmt.setInt(3, SPProps.minEventTypeId); else pstmt.setInt(3, SPProps.mdnEventTypeId); rset = pstmt.executeQuery(); if (rset.next()) { String cust_ref = rset.getString(1); acctVO.cust_ref = cust_ref; if (selParam == MDN) acctVO.mdn = rset.getString(2); else acctVO.min = rset.getString(2); acctVO.unid = rset.getString(3); acctVO.billSysId = rset.getString(4); if (selParam == MDN) acctVO.min = rset.getString(5); else acctVO.mdn = rset.getString(5); acctVO.esn = ""; acctVO.marketId = ""; pstmt = conn.prepareStatement(checkPrepay); pstmt.setString(1, cust_ref); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { if (rs.getInt(1) > 0) acctVO.prepay_ind = "Y"; else acctVO.prepay_ind = "N"; } L.debug( "getAccountFromGeneva():" + logTag + "Customer Ref=" + cust_ref + ";MDN = " + acctVO.mdn + ";MIN = " + acctVO.min + ";PREPAY_IND = " + acctVO.prepay_ind); // PrePay RBT Change retVal = true; } } catch (Exception e) { L.error("getAccountFromGeneva():" + logTag + "During getAccountFromGeneva(): ", e); throw new ProcessingException(ProcessingException.GLOBAL, e.getMessage()); } finally { try { if (rset != null) rset.close(); if (pstmt != null) pstmt.close(); if (conn != null) conn.close(); } catch (Exception eat) { } } return retVal; }
/** * @author Anil * <p>To change this generated comment edit the template variable "typecomment": * Window>Preferences>Java>Templates. To enable and disable the creation of type comments go to * Window>Preferences>Java>Code Generation. */ public class CheckAccount { private static Logger L = Logger.getLogger(SPProps.getBELogName(CheckAccount.class)); private static int MDN = 1; private static int MIN = 2; private static final String dsNameG = "java:comp/env/jdbc/SelfProvDSGeneva"; public static boolean getAccount(String logTag, String mdn, int selParam, AccountVO accVO) throws ProcessingException { // Get the account details from geneva if (!getAccountFromGeneva(logTag, mdn, selParam, accVO)) return false; else return true; } private static boolean getAccountFromGeneva( String logTag, String number, int selParam, AccountVO acctVO) throws ProcessingException { L.debug( "getAccountFromGeneva():" + logTag + "Entering with number=" + number + "; type=" + selParam); InitialContext ic = null; DataSource ds = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rset = null; boolean retVal = false; String dgtID = SPProps.getProdAttrProps().getProperty("PROD_DGT"); String subID = SPProps.getProdAttrProps().getProperty("SUB_PREPAY"); if ((dgtID == null || dgtID.trim().length() < 1) || (subID == null || subID.trim().length() < 1)) { L.error( "getAccountFromGeneva():" + logTag + "During getAccountFromGeneva(): Cannot get Product ID for DGT or Sub ID for Prepay"); throw new ProcessingException( ProcessingException.GLOBAL, "During getAccountFromGeneva(): Cannot get Product ID for DGT or Sub ID for Prepay"); } String mdnQuery = "select a.customer_ref, a.event_source as mdn, b.account_num, c.bill_handling_code, " + "d.event_source as min from pvcusteventsource4 a, pvaccount11 b, pvaccountdetails4 c, " + "pvcusteventsource4 d where a.end_dtm is null and a.event_type_id = ? and " + "a.event_source = ? and a.customer_ref = b.customer_ref and b.account_num = c.account_num and " + "c.end_dat is null and a.customer_ref = d.customer_ref and d.event_type_id = ? and d.end_dtm is null"; String minQuery = "select a.customer_ref, a.event_source as min, b.account_num, c.bill_handling_code, " + "d.event_source as mdn from pvcusteventsource4 a, pvaccount11 b, pvaccountdetails4 c, " + "pvcusteventsource4 d where a.end_dtm is null and a.event_type_id = ? and " + "a.event_source = ? and a.customer_ref = b.customer_ref and b.account_num = c.account_num and " + "c.end_dat is null and a.customer_ref = d.customer_ref and d.event_type_id = ? and d.end_dtm is null"; // PrePay RBT Change // String checkPrepay = "select attribute_value from custproductattrdetails where product_id=" + // dgtID + // " and product_attribute_subid=" + subID + " and customer_ref = ?"; String checkPrepay = "select /*+ INDEX(CUSTPRODUCTATTRDETAILS CUSTPRODUCTATTRDETAILS_PK) */ count(*) from custproductattrdetails where product_id=" + dgtID + " and product_attribute_subid=" + subID + " and customer_ref = ? and attribute_value='Y' " + "and start_dat<sysdate and (end_dat is null or end_dat>sysdate)"; try { ic = new InitialContext(); ds = (DataSource) ic.lookup(dsNameG); conn = ds.getConnection(); // String mdnEventType = SPProps.getProperty("MDN_EVENT_TYPE"); // String minEventType = SPProps.getProperty("MIN_EVENT_TYPE"); // if (mdnEventType == null || mdnEventType.equals("")) // mdnEventType = "8"; // if (minEventType == null || minEventType.equals("")) // minEventType = "9"; // StringBuffer sb = new StringBuffer(); // sb.append("select a.customer_ref, a.event_source as "); // if (selParam == MDN) // sb.append("mdn,"); // else // sb.append("min,"); // sb.append( // "b.account_num, c.bill_handling_code, d.event_source as "); // if (selParam == MDN) // sb.append("min"); // else // sb.append("mdn"); // sb.append(" from pvcusteventsource4 a, pvaccount11 b,"); // sb.append("pvaccountdetails4 c, pvcusteventsource4 d where "); // sb.append("a.end_dtm is null and "); // sb.append("a.event_type_id = '"); // if (selParam == MDN) // sb.append(mdnEventType); // else // sb.append(minEventType); // sb.append("' and "); // sb.append("a.event_source = '" + number + "' and "); // sb.append("a.customer_ref = b.customer_ref and "); // sb.append("b.account_num = c.account_num and "); // sb.append("c.end_dat is null and "); // sb.append("a.customer_ref = d.customer_ref and "); // sb.append("d.event_type_id = '"); // if (selParam == MDN) // sb.append(minEventType); // else // sb.append(mdnEventType); // sb.append("' and d.end_dtm is null"); String query = null; if (selParam == MDN) query = mdnQuery; else query = minQuery; L.debug("getAccountFromGeneva():" + logTag + "SQL Statement is: " + query); pstmt = conn.prepareStatement(query); if (selParam == MDN) pstmt.setInt(1, SPProps.mdnEventTypeId); else pstmt.setInt(1, SPProps.minEventTypeId); pstmt.setString(2, number); if (selParam == MDN) pstmt.setInt(3, SPProps.minEventTypeId); else pstmt.setInt(3, SPProps.mdnEventTypeId); rset = pstmt.executeQuery(); if (rset.next()) { String cust_ref = rset.getString(1); acctVO.cust_ref = cust_ref; if (selParam == MDN) acctVO.mdn = rset.getString(2); else acctVO.min = rset.getString(2); acctVO.unid = rset.getString(3); acctVO.billSysId = rset.getString(4); if (selParam == MDN) acctVO.min = rset.getString(5); else acctVO.mdn = rset.getString(5); acctVO.esn = ""; acctVO.marketId = ""; pstmt = conn.prepareStatement(checkPrepay); pstmt.setString(1, cust_ref); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { if (rs.getInt(1) > 0) acctVO.prepay_ind = "Y"; else acctVO.prepay_ind = "N"; } L.debug( "getAccountFromGeneva():" + logTag + "Customer Ref=" + cust_ref + ";MDN = " + acctVO.mdn + ";MIN = " + acctVO.min + ";PREPAY_IND = " + acctVO.prepay_ind); // PrePay RBT Change retVal = true; } } catch (Exception e) { L.error("getAccountFromGeneva():" + logTag + "During getAccountFromGeneva(): ", e); throw new ProcessingException(ProcessingException.GLOBAL, e.getMessage()); } finally { try { if (rset != null) rset.close(); if (pstmt != null) pstmt.close(); if (conn != null) conn.close(); } catch (Exception eat) { } } return retVal; } public static String getServiceStatusFromGeneva( String logTag, String custRef, String billTariffId) throws ProcessingException { L.debug( "getServiceStatusFromGeneva():" + logTag + "Entering with CustRef=" + custRef + "; Billing_Tariff_Id=" + billTariffId); InitialContext ic = null; DataSource ds = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rset = null; String servStatus = null; String query = "SELECT /*+ INDEX(pvcustproducttariffdetails CUSTPRODUCTTARIFFDETAILS_PK) */ count(*) " + "FROM pvcustproducttariffdetails WHERE customer_ref = ? and tariff_id = ? and end_dat is null"; try { ic = new InitialContext(); ds = (DataSource) ic.lookup(dsNameG); conn = ds.getConnection(); // StringBuffer sb = new StringBuffer(); // sb.append( // "SELECT /*+ INDEX(pvcustproducttariffdetails CUSTPRODUCTTARIFFDETAILS_PK) */ " + // "count(*) FROM pvcustproducttariffdetails WHERE customer_ref='"); // sb.append(custRef); // sb.append("' and tariff_id='"); // sb.append(billTariffId); // sb.append("' and end_dat is null"); L.debug("getServiceStatusFromGeneva():" + logTag + "SQL Statement is: " + query); pstmt = conn.prepareStatement(query); pstmt.setString(1, custRef); pstmt.setString(2, billTariffId); rset = pstmt.executeQuery(); int rowCount = 0; if (rset.next()) { rowCount = rset.getInt(1); L.debug( "getServiceStatusFromGeneva():" + logTag + "Row Count from pvcustproducttariffdetails=" + rowCount); } else L.debug( "getServiceStatusFromGeneva():" + logTag + "Customer Service is not active, no row found"); if (rowCount > 0) servStatus = "OK"; } catch (Exception e) { L.error("getAccountFromGeneva():" + logTag + "During getAccountFromGeneva(): ", e); throw new ProcessingException(ProcessingException.GLOBAL, e.getMessage()); } finally { try { if (rset != null) rset.close(); if (pstmt != null) pstmt.close(); if (conn != null) conn.close(); } catch (Exception eat) { } } return servStatus; } public static String chkProvAllowed(String provId, String billSysId) { L.info( "Entered into CheckAccount.chkProvAllowed(),ProvId=" + provId + " ,billSysId=" + billSysId); String errMsg = SPProps.getBillProvPropObject().getProperty(provId.trim() + "-" + billSysId.trim()); if (errMsg == null || errMsg.trim().equals("")) return null; else return errMsg.trim(); } }