Java tutorial
package com.intuit.it.billing.data; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import oracle.jdbc.OracleTypes; import org.springframework.stereotype.Repository; import org.springframework.stereotype.Service; import java.sql.SQLException; import com.intuit.it.billing.model.Allocation; import com.intuit.it.billing.model.Customer; import com.intuit.it.billing.model.LineItem; import com.intuit.it.billing.model.PaymentInfoCC; import com.intuit.it.billing.model.PaymentInfoCheck; import com.intuit.it.billing.model.PaymentInfoEFT; import com.intuit.it.billing.util.JSONException; @Repository("billingDAO") public class BillingDAOImpl implements BillingDAO { /** * @section dao_section class BillingDAOImpl * getAllocation * * To be used in a caching method where we are pulling all of the allocations at once. The way we can do this * is to merge a date range based set of billing history records with a date range set of allocations. * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_item_allocations( * item_no IN VARCHAR2 ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ALLOCATION_DATE,</li> * <li>ALLOCATION_T, </li> * <li>ALLOCATION_AMT,</li> * <li>AR_ITEM_NO, </li> * <li>BILL_ITEM_NO, </li> * <li>ITEM_DESCRIPTION, </li> * <li>ITEM_CODE,</li> * <li>AR_ITEM_DATE, </li> * <li>BILL_ITEM_DATE </li> * <li>LICENSE</li> * </ul> * * @param customer : The Customer.accountNo account number of the customer who's allocations we need * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * * @return A list of Allocation objects. * @throws JSONException * * * */ @Override public List<Allocation> getAllocation(String itemNo) throws JSONException { List<Allocation> allocs = new ArrayList<Allocation>(); String query = "begin ? := billing_inquiry.fn_get_item_allocations( ? ); end;"; //TODO: configure ResultSet rs = null; Connection conn = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, itemNo); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { Allocation a = new Allocation(); a.setAllocatedFromItem(rs.getString("AR_ITEM_NO")); a.setAllocatedToItem(rs.getString("BILL_ITEM_NO")); a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT")); a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE")); a.setItemCode(rs.getString("ITEM_CODE")); a.setItemDescription(rs.getString("ITEM_DESCRIPTION")); a.setLicense(rs.getString("LICENSE")); allocs.add(a); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (allocs == null || allocs.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return allocs; } /** * getAllocationsList * * To be used in a caching method where we are pulling all of the allocations at once. The way we can do this * is to merge a date range based set of billing history records with a date range set of allocations. * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_allocations( * customer IN VARCHAR2, * start_date IN DATE, * end_date IN DATE ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ALLOCATION_DATE,</li> * <li>ALLOCATION_T, </li> * <li>ALLOCATION_AMT,</li> * <li>AR_ITEM_NO, </li> * <li>BILL_ITEM_NO, </li> * <li>ITEM_DESCRIPTION, </li> * <li>ITEM_CODE,</li> * <li>AR_ITEM_DATE, </li> * <li>BILL_ITEM_DATE </li> * <li>LICENSE</li> * </ul> * * @param customer : The Customer.accountNo account number of the customer who's allocations we need * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * * @return A list of Allocation objects. * */ @Override public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException { List<Allocation> allocs = new ArrayList<Allocation>(); java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime()); java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime()); String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, customer); stmt.setDate(3, sqlStartDate); stmt.setDate(4, sqlEndDate); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { Allocation a = new Allocation(); a.setAllocatedFromItem(rs.getString("AR_ITEM_NO")); a.setAllocatedToItem(rs.getString("BILL_ITEM_NO")); a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT")); a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE")); a.setItemCode(rs.getString("ITEM_CODE")); a.setItemDescription(rs.getString("ITEM_DESCRIPTION")); a.setLicense(rs.getString("LICENSE")); allocs.add(a); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (allocs == null || allocs.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return allocs; } /** * getBillingHistory is to be used to get get the billing history of a given customer. * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_history( * customer IN VARCHAR2, * start_date IN DATE, * end_date IN DATE, * page IN INTEGER, * records IN INTEGER ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ITEM_ID,</li> * <li>BILL_ITEM_NO,</li> * <li>AR_ACCOUNT_NO,</li> * <li>ACCOUNT_NO,</li> * <li>ORDER_NO,</li> * <li>ORDER_LINE_NO,</li> * <li>EVENT_TYPE,</li> * <li>CHARGE_TYPE,</li> * <li> CURRENCY,</li> * <li>CREATED_DATE,</li> * <li>BILL_DATE,</li> * <li>DUE_DATE,</li> * <li>STATUS,</li> * <li>REASON_CODE,</li> * <li>ITEM_TOTAL,</li> * <li>ITEM_DUE,</li> * <li>ITEM_DISPUTED,</li> * <li>ITEM_BASE,</li> * <li>ITEM_TAX,</li> * <li>ITEM_DESCRIPTION,</li> * <li>ITEM_CODE,</li> * <li>LICENSE,</li> * <li>PAY_TYPE,</li> * <li>PAY_DESCR,</li> * <li>PAY_ACCT_TYPE, * <li>PAY_PSON,</li> * <li>QUANTITY </li> * </ul> * * @param customer : The Customer.accountNo of the customer we want history for * @param startDate : The starting date of the allocation - to be merged with a billing history record set * @param endDate : The ending date of the allocation - to be merged with a billing history record set * @param skip : Starting record for server side paging * @param pageSize : How many records to retrieve * * @return A list of LineItem objects in reverse date order sort */ @Override public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage, Integer pageSize) throws JSONException { List<LineItem> history = new ArrayList<LineItem>(); java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime()); java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime()); String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, cust); stmt.setDate(3, sqlStartDate); stmt.setDate(4, sqlEndDate); stmt.setInt(5, startPage); stmt.setInt(6, pageSize); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { LineItem l = new LineItem(); l.setRowId(rs.getInt("ROW_ID")); l.setBaseAmount(rs.getBigDecimal("ITEM_BASE")); l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL")); l.setItemDue(rs.getBigDecimal("ITEM_DUE")); l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED")); l.setTaxAmount(rs.getBigDecimal("ITEM_TAX")); l.setBillDate(rs.getTimestamp("BILL_DATE")); l.setBillItemNo(rs.getString("BILL_ITEM_NO")); l.setBillTo(rs.getString("AR_ACCOUNT_NO")); l.setChargeType(rs.getString("CHARGE_TYPE")); l.setCreatedDate(rs.getTimestamp("CREATED_DATE")); l.setCurrency(rs.getString("CURRENCY")); l.setDueDate(rs.getTimestamp("DUE_DATE")); l.setEventType(rs.getString("EVENT_TYPE")); l.setItemCode(rs.getString("ITEM_CODE")); l.setItemDescription(rs.getString("ITEM_DESCRIPTION")); l.setLicense(rs.getString("LICENSE")); l.setItemID(rs.getString("ITEM_ID")); l.setOrderLine(rs.getString("ORDER_LINE_NO")); l.setOrderNo(rs.getString("ORDER_NO")); l.setPayAccountType(rs.getString("PAY_ACCT_TYPE")); l.setPayDescription(rs.getString("PAY_DESCR")); l.setPayType(rs.getString("PAY_TYPE")); l.setpSON(rs.getString("PAY_PSON")); l.setQuantity(rs.getInt("QUANTITY")); l.setReasonCode(rs.getString("REASON_CODE")); l.setStatus(rs.getInt("STATUS")); history.add(l); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (history == null || history.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return history; } /** * getCustomerInfo * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code * FUNCTION fn_get_customer * ( * customer IN VARCHAR2, * bill_item_no IN VARCHAR2, * order_no IN VARCHAR2, * pson IN VARCHAR2 * ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ACCOUNT_NO,</li> * <li>FIRST_NAME,</li> * <li>LAST_NAME,</li> * <li>COMPANY,</li> * <li>PHONE,</li> * <li>ADDRESS,</li> * <li>CITY,</li> * <li>STATE,</li> * <li>ZIP,</li> * <li>COUNTRY </li> * </ul> * * @param customer - The Customer.accountNo of the customer we want to find * @param billNo - A bill number (e.g. B1-1111) OR bill line item number (e.g. B1-111,5) of the customer we want to find - remove everything after the comma * @param orderNo - An order number (e.g. 200000011111) OR order line item number (e.g. 200000011111,5) of the customer we want to find - remove everything after the comma * @param pson - A BRM trans_id number (Paymenttech merchant number) of the payment event * * @return A Customer object * */ @Override public Customer getCustomerInfo(String customer, String billNo, String orderNo, String pson) throws JSONException { Customer c = null; String query = "begin ? := billing_inquiry.fn_get_customer( ?, ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, customer); stmt.setString(3, billNo); stmt.setString(4, orderNo); stmt.setString(5, pson); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { c = new Customer(); c.setAccountNo(rs.getString("ACCOUNT_NO")); c.setFirstName(rs.getString("FIRST_NAME")); c.setLastName(rs.getString("LAST_NAME")); c.setCompany(rs.getString("COMPANY")); c.setPhone(rs.getString("PHONE")); c.setAddress(rs.getString("ADDRESS")); c.setCity(rs.getString("CITY")); c.setState(rs.getString("STATE")); c.setZip(rs.getString("ZIP")); c.setCountry(rs.getString("COUNTRY")); break; } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (c == null) { throw JSONException.noDataFound("Null set returned - no data found"); } return c; } /** * getCustomerList * * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code *FUNCTION fn_search_customers * ( * phone IN VARCHAR2, * first_name IN VARCHAR2, * last_name IN VARCHAR2, * company IN VARCHAR2, * cc_num IN VARCHAR2, * eft_num IN VARCHAR2, * records IN INTEGER * ) * RETURN ref_cursor; * @endcode * * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>ACCOUNT_NO,</li> * <li>FIRST_NAME,</li> * <li>LAST_NAME,</li> * <li>COMPANY,</li> * <li>PHONE,</li> * <li>ADDRESS,</li> * <li>CITY,</li> * <li>STATE,</li> * <li>ZIP,</li> * <li>COUNTRY </li> * </ul> * * @param phone - The customer's phone number * @param firstName - A wild-carded first name * @param lastName - A wild-carded last name * @param company - A wild-carded company name * @param ccNum - A four digit last-four of a credit card * @param eftNum - A four digit last-four of a bank account number * @param pageSize : How many records to retrieve * * @return A list of Customer objects in lastname, company alphabetical order * - if rows returned is greater than pageSize will not return TOO MANY rows erros * */ @Override public List<Customer> getCustomerList(String phone, String firstName, String lastName, String company, String ccNum, String eftNum, Integer pageSize) throws JSONException { Integer startPage = 1; List<Customer> customers = new ArrayList<Customer>(); String query = "begin ? := billing_inquiry.fn_search_customers( ?, ?, ?, ?, ?, ?, ?, ? ); end;"; Connection conn = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, phone); stmt.setString(3, firstName); stmt.setString(4, lastName); stmt.setString(5, company); stmt.setString(6, ccNum); stmt.setString(7, eftNum); stmt.setInt(8, startPage); stmt.setInt(9, pageSize); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { Customer c = new Customer(); c.setAccountNo(rs.getString("ACCOUNT_NO")); c.setFirstName(rs.getString("FIRST_NAME")); c.setLastName(rs.getString("LAST_NAME")); c.setCompany(rs.getString("COMPANY")); c.setPhone(rs.getString("PHONE")); c.setAddress(rs.getString("ADDRESS")); c.setCity(rs.getString("CITY")); c.setState(rs.getString("STATE")); c.setZip(rs.getString("ZIP")); c.setCountry(rs.getString("COUNTRY")); customers.add(c); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (customers == null || customers.isEmpty()) { throw JSONException.noDataFound("Null set returned - no data found"); } return customers; } /** * PaymentInfoCC * * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code *FUNCTION fn_get_payinfo_cc * ( * item_no IN VARCHAR2 * ) * RETURN ref_cursor; * @endcode * * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>BILLING_PROFILE_ID,</li> * <li>BDOM,</li> * <li>PAYINFO_NAME,</li> * <li>CC_NUMBER,</li> * <li>EXPIRE_MM,</li> * <li>EXPIRE_YYYY,</li> * </ul> * * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for * * @return A single Payment Info record * * TODO * Decide how to determine the card type from the token * */ @Override public PaymentInfoCC getPayinfoCC(String itemNo) throws JSONException { // TODO Auto-generated method stub String query = "begin ? := billing_inquiry.fn_get_payinfo_cc( ? ); end;"; Connection conn = null; ResultSet rs = null; PaymentInfoCC c = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, itemNo); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { c = new PaymentInfoCC(); c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); c.setProfileBdom(rs.getInt("BDOM")); c.setCardholderName(rs.getString("PAYINFO_NAME")); c.setCcNumber(rs.getString("CC_NUMBER")); c.setExpiryMonth(rs.getString("EXPIRE_MM")); c.setExpiryYear(rs.getString("EXPIRE_YYYY")); c.setCardType("TODO"); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (c == null) { throw JSONException.noDataFound("Null set returned - no data found"); } return c; } /** * PaymentInfoEFT * * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code *FUNCTION fn_get_payinfo_eft * ( * item_no IN VARCHAR2 * ) * RETURN ref_cursor * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>BILLING_PROFILE_ID,</li> * <li>BDOM,</li> * <li>PAYINFO_NAME,</li> * <li>BANK_ACCOUNT_NO,</li> * <li>BANK_NUMBER,</li> * </ul> * * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for * * @return A single Payment Info record * */ @Override public PaymentInfoEFT getPayinfoEFT(String itemNo) throws JSONException { String query = "begin ? := billing_inquiry.fn_get_payinfo_eft( ? ); end;"; Connection conn = null; PaymentInfoEFT c = null; ResultSet rs = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, itemNo); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { c = new PaymentInfoEFT(); c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); c.setProfileBdom(rs.getInt("BDOM")); c.setCustomerName(rs.getString("PAYINFO_NAME")); c.setBankAccountNumber(rs.getString("BANK_ACCOUNT_NO")); c.setBankNumber(rs.getString("BANK_NUMBER")); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (c == null) { throw JSONException.noDataFound("Null set returned - no data found"); } return c; } /** * PaymentInfoCheck * <p/> * <p/> * <b>DATABASE PROCEDURE:</b> * * @code *FUNCTION fn_get_payinfo_check * ( * item_no IN VARCHAR2 * ) * RETURN ref_cursor; * @endcode * <p/> * <b>DATABASE RESULT SET:</b> * <ul> * <li>BILLING_PROFILE_ID,</li> * <li>BDOM,</li> * <li>PAYINFO_NAME,</li> * <li>NET_TERMS,</li> * <li>CHECK_NO,</li> * </ul> * * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for * * @return A single Payment Info record * */ @Override public PaymentInfoCheck getPayinfoCheck(String itemNo) throws JSONException { String query = "begin ? := billing_inquiry.fn_get_payinfo_check( ? ); end;"; // TODO: configure strings ResultSet rs = null; Connection conn = null; PaymentInfoCheck c = null; // DB Connection try { conn = this.getConnection(); } catch (SQLException e) { throw JSONException.sqlError(e); } catch (NamingException e) { throw JSONException.namingError(e.toString()); } try { CallableStatement stmt = conn.prepareCall(query); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.setString(2, itemNo); stmt.execute(); rs = (ResultSet) stmt.getObject(1); while (rs.next()) { c = new PaymentInfoCheck(); c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); // TODO: configure strings c.setProfileBdom(rs.getInt("BDOM")); c.setCustomerName(rs.getString("PAYINFO_NAME")); c.setNetTerms(rs.getString("NET_TERMS")); c.setCheckNumber(rs.getString("CHECK_NO")); } conn.close(); rs.close(); } catch (SQLException e) { throw JSONException.sqlError(e); } if (c == null) { throw JSONException.noDataFound("Null set returned - no data found"); } return c; } Connection getConnection() throws NamingException, SQLException { Context initContext = null; Context envContext = null; Connection conn = null; DataSource ds = null; initContext = new InitialContext(); envContext = (Context) initContext.lookup("java:/comp/env"); ds = (DataSource) envContext.lookup("datasources/OracleDS"); conn = ds.getConnection(); return conn; } }