Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql CallableStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<M6OrderStatusDto> viewM6OrderList(M6OrderStatusDto objDto) throws Exception {
    //Nagarjuna/*w  ww . j a  v a 2 s  .c o  m*/
    String methodName = "viewM6OrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<M6OrderStatusDto> objUserList = new ArrayList<M6OrderStatusDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getOrder = null;

    try {
        conn = DbConnection.getReportsConnectionObject();
        getOrder = conn.prepareCall(sqlGetM6OrderStatusReport);
        String accountIdStr = objDto.getSearchAccountIdStr();
        String orderNoStr = objDto.getSearchOrderNoStr();
        //   String m6OrderNoStr = objDto.getSearchM6OrderNoStr();
        String accountNameStr = objDto.getSearchAccountNameStr();

        if (accountIdStr == null || accountIdStr.trim().equals("")) {
            getOrder.setNull(1, java.sql.Types.BIGINT);
        } else {
            getOrder.setLong(1, Long.parseLong(accountIdStr));
        }

        if (orderNoStr == null || orderNoStr.trim().equals("")) {
            getOrder.setNull(2, java.sql.Types.BIGINT);
        } else {
            getOrder.setLong(2, Long.parseLong(orderNoStr));
        }

        if (accountNameStr == null || accountNameStr.trim().equals("")) {
            getOrder.setNull(3, java.sql.Types.VARCHAR);
        } else {
            getOrder.setString(3, accountNameStr);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        getOrder.setString(4, pagingSorting.getSortByColumn());// columnName
        getOrder.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        getOrder.setInt(6, pagingSorting.getStartRecordId());// start index
        getOrder.setInt(7, pagingSorting.getEndRecordId());// end index
        getOrder.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = getOrder.executeQuery();

        int countFlag = 0;
        int recordCount = 0;
        while (rs.next() != false) {
            countFlag++;

            objDto = new M6OrderStatusDto();
            objDto.setAccountID("" + rs.getInt("ACCOUNTID"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setOrderNo("" + rs.getInt("OrderNO"));
            objDto.setM6OrderNo("" + rs.getInt("M6OrderNO"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }

            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(getOrder);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<DispatchAddressDto> viewDispatchAddressList(DispatchAddressDto objDto) throws Exception {
    //Nagarjuna//from   www  .j av a  2 s  .  c o  m
    String methodName = "viewDispatchAddressList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<DispatchAddressDto> objUserList = new ArrayList<DispatchAddressDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;

    try {
        conn = DbConnection.getReportsConnectionObject();
        proc = conn.prepareCall(sqlDispatchList);

        if (objDto.getDispatchAddressId() != null && !"".equals(objDto.getDispatchAddressId())) {
            proc.setLong(2, Long.parseLong(objDto.getDispatchAddressId()));
        } else {
            proc.setNull(2, java.sql.Types.BIGINT);
        }
        if (objDto.getAccountID() != null && !"".equals(objDto.getAccountID())) {
            proc.setLong(1, Long.parseLong(objDto.getAccountID()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);

        }

        if (objDto.getDispatchAddressName() != null && !"".equals(objDto.getDispatchAddressName())) {
            proc.setString(3, objDto.getDispatchAddressName().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        if (objDto.getAccountName() != null || !objDto.getAccountName().trim().equals("")) {
            proc.setString(4, objDto.getAccountName());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(5, pagingSorting.getSortByColumn());// columnName
        proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(7, pagingSorting.getStartRecordId());// start index
        proc.setInt(8, pagingSorting.getEndRecordId());// end index
        proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = proc.executeQuery();
        int countFlag = 0;
        int recordCount = 0;

        while (rs.next() != false) {
            countFlag++;

            String userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME"));
            objDto = new DispatchAddressDto();
            objDto.setCustomerName(userName);
            objDto.setAccountID(String.valueOf(rs.getInt("ACCOUNTID")));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setDispatchAddressId(String.valueOf(rs.getInt("DISPATCH_ADDRESS_CODE")));
            objDto.setDispatchAddressName(rs.getString("DISPATCHADDNAME"));
            objDto.setTelephonePhno(rs.getString("TELEPHONENO"));
            objDto.setEmail_Id(rs.getString("EMAIL_ID"));
            objDto.setAddress1(rs.getString("ADDRESS1"));
            objDto.setAddress2(rs.getString("ADDRESS2"));
            objDto.setAddress3(rs.getString("ADDRESS3"));
            objDto.setAddress4(rs.getString("ADDRESS4"));

            objDto.setFax(rs.getString("FAX"));
            objDto.setPin(rs.getString("PIN"));
            objDto.setTitle(rs.getString("TITLE"));
            objDto.setPostalCode(rs.getString("POSTAL_CODE"));
            objDto.setCountryName(rs.getString("COUNTRY_NAME"));
            objDto.setStateName(rs.getString("STATE_NAME"));
            objDto.setCityName(rs.getString("CITY_NAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<LocationDetailDto> viewCustomerLocationList(LocationDetailDto objDto) throws Exception {
    //Nagarjuna/*w  w  w .j  a  v  a2s . c  o m*/
    String methodName = "viewCustomerLocationList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<LocationDetailDto> objUserList = new ArrayList<LocationDetailDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;

    try {
        String userName = "";

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlGetCustomerLocReport);
        if (objDto.getLocationId() != null && !"".equals(objDto.getLocationId())) {
            proc.setLong(2, Long.parseLong(objDto.getLocationId()));
        } else {
            proc.setNull(2, java.sql.Types.BIGINT);
        }
        if (objDto.getAccountID() != null && !"".equals(objDto.getAccountID())) {
            proc.setLong(1, Long.parseLong(objDto.getAccountID()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }
        if (objDto.getLocationName() != null && !"".equals(objDto.getLocationName())) {
            proc.setString(3, objDto.getLocationName().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getAccountName() != null && !"".equals(objDto.getAccountName())) {
            proc.setString(4, objDto.getAccountName().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(5, pagingSorting.getSortByColumn());// columnName
        proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(7, pagingSorting.getStartRecordId());// start index
        proc.setInt(8, pagingSorting.getEndRecordId());// end index
        proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME"));
            objDto = new LocationDetailDto();
            objDto.setCustomerName(userName);
            objDto.setAccountID(String.valueOf(rs.getInt("ACCOUNTID")));
            objDto.setLocationId(String.valueOf(rs.getInt("LOCATION_CODE")));
            objDto.setLocationName(rs.getString("LOCATION_NAME"));
            objDto.setTelephonePhno(rs.getString("TELEPHONENO"));
            objDto.setEmail_Id(rs.getString("EMAIL_ID"));
            objDto.setAddress1(rs.getString("ADDRESS1"));
            objDto.setAddress2(rs.getString("ADDRESS2"));
            objDto.setAddress3(rs.getString("ADDRESS3"));
            objDto.setAddress4(rs.getString("ADDRESS4"));
            objDto.setFax(rs.getString("FAX"));
            objDto.setPin(rs.getString("PIN"));
            objDto.setTitle(rs.getString("TITLE"));
            objDto.setCountryName(rs.getString("COUNTRY_NAME"));
            objDto.setPostalCode(rs.getString("POSTAL_CODE"));
            objDto.setStateName(rs.getString("STATE_NAME"));
            objDto.setCityName(rs.getString("CITY_NAME"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<ReportsDto> viewOrderStageList(ReportsDto objDto) throws Exception {
    //Nagarjuna/*from   w w w .  j av a 2  s .com*/
    String methodName = "viewOrderStageList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        conn = DbConnection.getReportsConnectionObject();
        proc = conn.prepareCall(sqlOrderStage);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(4, pagingSorting.getSortByColumn());// columnName
        proc.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(6, pagingSorting.getStartRecordId());// start index
        proc.setInt(7, pagingSorting.getEndRecordId());// end index
        proc.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(9, objDto.getOsp().trim());
        } else {
            proc.setNull(9, java.sql.Types.VARCHAR);
        }

        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;
            objDto = new ReportsDto();
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setOrderStatus(rs.getString("STATUS"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {
                objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime())))
                        .toUpperCase());
            }

            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objDto.setServiceId(rs.getInt("SERVICEID"));
            objDto.setEffStartDate(rs.getString("EFFSTARTDATE"));
            if (rs.getString("EFFSTARTDATE") != null && !"".equals(rs.getString("EFFSTARTDATE"))) {

                Date date = df.parse(objDto.getEffStartDate());
                objDto.setEffStartDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setM6OrderNumber(rs.getInt("M6ORDERNO"));
            objDto.setCustLogicalSI(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objDto.setPublishedDate(rs.getString("PUBLISHED_DATE"));
            if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) {
                objDto.setPublishedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setServiceDetDescription(rs.getString("SERVICESTAGE"));
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setPartyNo(rs.getInt("PARTY_NO"));
            objDto.setBillingStatus(rs.getString("BILLING_STATUS"));
            objDto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objDto.setParentOrderSubType(rs.getString("PARENTORDERSUBTYPE"));
            objDto.setAmName(rs.getString("AMNAME"));
            objDto.setPmName(rs.getString("PMNAME"));
            objDto.setCopcName(rs.getString("COPCNAME"));
            objDto.setStandardReason(rs.getString("STANDARDREASON"));
            objDto.setM6OrderDate(rs.getString("CREATED_DATE"));
            if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) {
                objDto.setM6OrderDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CREATED_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objDto.setAmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setCircuitStatus(rs.getString("CIRCUIT_STATUS"));
            objDto.setOrderProvision(rs.getString("ORDER_PROVISION"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setOsp(rs.getString("OSP"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);
        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<BCPAddressDto> viewBCPList(BCPAddressDto objDto) throws Exception {
    //Nagarjuna/*from  w  ww .  j a v a 2  s. c  o m*/
    String methodName = "viewBCPList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<BCPAddressDto> objUserList = new ArrayList<BCPAddressDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getBCP = null;

    try {
        String userName = "";
        conn = DbConnection.getReportsConnectionObject();
        getBCP = conn.prepareCall(sqlGetBCPReport);
        String accountIdStr = objDto.getSearchAccountIdStr();
        String bcpIdStr = objDto.getSearchBcpIdStr();
        String bcpNameStr = objDto.getSearchBcpNameStr();
        String accountNameStr = objDto.getSearchAccountNameStr();

        if (accountIdStr == null || accountIdStr.trim().equals("")) {
            getBCP.setNull(1, java.sql.Types.BIGINT);
        } else {
            getBCP.setLong(1, Long.parseLong(accountIdStr));
        }

        if (bcpIdStr == null || bcpIdStr.trim().equals("")) {
            getBCP.setNull(2, java.sql.Types.BIGINT);
        } else {
            getBCP.setLong(2, Long.parseLong(bcpIdStr));
        }

        if (bcpNameStr == null || bcpNameStr.trim().equals("")) {
            getBCP.setNull(3, java.sql.Types.VARCHAR);
        } else {
            getBCP.setString(3, bcpNameStr);
        }

        if (accountNameStr == null || accountNameStr.trim().equals("")) {
            getBCP.setNull(4, java.sql.Types.VARCHAR);
        } else {
            getBCP.setString(4, accountNameStr);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        getBCP.setString(5, pagingSorting.getSortByColumn());// columnName
        getBCP.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        getBCP.setInt(7, pagingSorting.getStartRecordId());// start index
        getBCP.setInt(8, pagingSorting.getEndRecordId());// end index
        getBCP.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = getBCP.executeQuery();

        int countFlag = 0;
        int recordCount = 0;
        while (rs.next() != false) {
            countFlag++;

            userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME"));
            objDto = new BCPAddressDto();
            objDto.setCustomerName(userName);
            objDto.setAccountID("" + rs.getInt("ACCOUNTID"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setBCPId("" + rs.getInt("BCP_ID"));
            objDto.setFirstname(rs.getString("FNAME"));
            objDto.setLastName(rs.getString("LNAME"));
            objDto.setTelephonePhno(rs.getString("TELEPHONENO"));
            objDto.setEmail_Id(rs.getString("EMAIL_ID"));
            objDto.setAddress1(rs.getString("ADDRESS1"));
            objDto.setAddress2(rs.getString("ADDRESS2"));
            objDto.setAddress3(rs.getString("ADDRESS3"));
            objDto.setAddress4(rs.getString("ADDRESS4"));
            objDto.setFax(rs.getString("FAX"));
            // objDto.setPin(rs.getString("PIN"));
            objDto.setTitle(rs.getString("TITLE"));
            objDto.setPostalCode(rs.getString("POSTAL_CODE"));
            objDto.setCityName(rs.getString("CITY_NAME"));
            objDto.setStateName(rs.getString("STATE_NAME"));
            objDto.setCountryName(rs.getString("COUNTRY_NAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }

            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        //throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(getBCP);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            //throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<NewOrderDto> viewContactList(NewOrderDto objDto) throws Exception {
    //Nagarjuna//from  ww w .ja  v  a 2  s.com
    String methodName = "viewContactList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<NewOrderDto> objUserList = new ArrayList<NewOrderDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;

    try {
        String userName = "";

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlContactList);
        if (objDto.getAccountID() != 0) {
            proc.setLong(1, (objDto.getAccountID()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }
        if (objDto.getContactType() != null && !"".equals(objDto.getContactType())) {
            proc.setString(2, (objDto.getContactType()));
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

        if (objDto.getContactName() != null && !"".equals(objDto.getContactName())) {
            proc.setString(3, objDto.getContactName().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getAccountName() != null && !"".equals(objDto.getAccountName())) {
            proc.setString(4, objDto.getAccountName().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        if (objDto.getOrderNumber() != 0) {
            proc.setLong(5, (objDto.getOrderNumber()));
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(6, pagingSorting.getSortByColumn());// columnName
        proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(8, pagingSorting.getStartRecordId());// start index
        proc.setInt(9, pagingSorting.getEndRecordId());// end index
        proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            userName = (rs.getString("FIRSTNAME")) + " " + (rs.getString("LASTNAME"));
            objDto = new NewOrderDto();
            objDto.setContactName(userName);
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setContactId(rs.getLong("CONTACTID"));
            objDto.setContactType(rs.getString("CONTACTTYPE"));
            objDto.setSaluation(rs.getString("SALUATION"));
            objDto.setCntEmail(rs.getString("EMAIL"));
            objDto.setContactCell(rs.getString("CELLNO"));
            objDto.setContactFax(rs.getString("FAXNO"));
            objDto.setAddress1(rs.getString("ADDRESS1"));
            objDto.setAddress2(rs.getString("ADDRESS2"));
            objDto.setAddress3(rs.getString("ADDRESS3"));
            objDto.setCityName(rs.getString("CITY"));
            objDto.setStateName(rs.getString("STATE"));
            objDto.setCountyName(rs.getString("COUNTRY"));
            objDto.setPinNo(rs.getString("PINCODE"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<CancelledFailedLineReportDTO> viewCancelledFailedLineReport(
        CancelledFailedLineReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewCancelledFailedLineReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;/*from  ww  w  .j a  va 2 s. co m*/
    int recordCount = 0;
    ArrayList<CancelledFailedLineReportDTO> listSearchDetails = new ArrayList<CancelledFailedLineReportDTO>();
    CancelledFailedLineReportDTO objReportsDto = null;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;
    Timestamp ts = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetCancelledFailedLineReportsForUsage);

        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(1, objDto.getFromDate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(2, objDto.getToDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

        if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) {
            proc.setString(3, objDto.getServiceName().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(4, pagingSorting.getSortByColumn());// columnName
        proc.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(6, pagingSorting.getStartRecordId());// start index
        proc.setInt(7, pagingSorting.getEndRecordId());// end index
        proc.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        proc.setInt(9, objDto.getIsUsage());
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            objReportsDto = new CancelledFailedLineReportDTO();
            objReportsDto.setPartyNo(rs.getInt("PARTY_NO"));
            objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrder_type(rs.getString("ORDERTYPE"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
            objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objReportsDto.setCkt_id(rs.getString("CKTID"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setUom(rs.getString("UOM"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setDistance(rs.getString("DISTANCE"));
            objReportsDto.setRatio(rs.getString("RATIO"));
            objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS"));
            objReportsDto.setLocation_to(rs.getString("TO_ADDRESS"));
            // change
            objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION);
            objReportsDto.setSeclocation(VAR_SECONDARYLOCATION);
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));

            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));

            //objReportsDto.setBill_period(rs.getString("BILL_PERIOD"));
            if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID"))
                    && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) {
                String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"),
                        rs.getString("ENTITYID"));
                objReportsDto.setBill_period(tBillPeriod);
            }
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setPoNumber(rs.getInt("PONUMBER"));
            // change
            tempDate = rs.getDate("PODATE");
            if (tempDate != null) {
                objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT"));
            // change
            tempDate = rs.getDate("PORECEIVEDATE");
            if (tempDate != null) {
                objReportsDto.setPoReceiveDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            // change
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null) {
                objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }

            objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID"));

            //         Saurabh : Changes to separate charge related specific column from common
            if (objDto.getIsUsage() == 0) {

                objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD"));

                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setStorename(rs.getString("STORENAME"));
                objReportsDto.setSaleType(rs.getString("SALETYPE"));

                objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE"));
                objReportsDto.setChargeAmount(rs.getDouble("INV_AMT"));
                objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT"));
                objReportsDto.setAnnitation(rs.getString("ANNOTATION"));
                objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));
                objReportsDto.setFx_status(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));
                objReportsDto.setFx_sd_status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));
                objReportsDto.setBusiness_serial_no(rs.getString("Business_No"));
                objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id"));
            }

            //Meenakshi : Changes for Usage
            if (objDto.getIsUsage() == 1) {
                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));

                objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));
                objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID"));
                objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));
                objReportsDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
                ComponentsDto dto = new ComponentsDto();

                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));

                dto.setStartDate(rs.getString("COMPONENT_START_DATE"));
                if (rs.getString("COMPONENT_START_DATE") != null
                        && !"".equals(rs.getString("COMPONENT_START_DATE"))) {

                    Date date = df.parse(dto.getStartDate());
                    dto.setStartDate((utility.showDate_Report(date)).toUpperCase());

                }

                dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
                dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS"));
                dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));
                objReportsDto.setComponentDto(dto);
            }

            /// End

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<MigratedApprovedNewOrderDetailReportDTO> viewMigAppNewOrderDetails(
        MigratedApprovedNewOrderDetailReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewMigAppNewOrderDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;/*from   w  w  w .j  a va2 s  . co m*/
    ArrayList<MigratedApprovedNewOrderDetailReportDTO> listSearchDetails = new ArrayList<MigratedApprovedNewOrderDetailReportDTO>();
    MigratedApprovedNewOrderDetailReportDTO objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;
    Timestamp ts = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetMigAppNewOrderDetails);

        if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) {
            proc.setString(1, objDto.getOrdermonth().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) {
            proc.setString(2, objDto.getServiceName().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(3, pagingSorting.getSortByColumn());// columnName
        proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(5, pagingSorting.getStartRecordId());// start index
        proc.setInt(6, pagingSorting.getEndRecordId());// end index
        proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        proc.setInt(8, objDto.getIsUsage());
        if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) {
            proc.setString(9, objDto.getOrderyear().trim());
        } else {
            proc.setNull(9, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new MigratedApprovedNewOrderDetailReportDTO();
            //[270513]Start : Added by Ashutosh for Billing Address
            setBlank();
            replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS"));
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//LineName
            //objReportsDto.setServiceOrderType(rs.getString("SERVICETYPE"));
            objReportsDto.setLogicalCircuitId(rs.getString("CKTID"));
            objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS"));
            objReportsDto.setToLocation(rs.getString("TO_ADDRESS"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setPonum(rs.getLong("PONUMBER"));
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            tempDate = rs.getDate("PODATE");
            if (tempDate != null) {
                objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month
            objReportsDto.setTotalPoAmt(rs.getString("CUST_TOT_PO_AMT"));
            tempDate = rs.getDate("PORECEIVEDATE");
            if (tempDate != null) {
                objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase());

            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null) {
                objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setLOC_No(rs.getString("LOCNO"));
            objReportsDto.setLocDate(rs.getString("LOCDATE"));
            if (!(rs.getString("LOCDATE") == null || rs.getString("LOCDATE") == "")) {

                Date date = df.parse(objReportsDto.getLocDate());
                objReportsDto.setLocDate((Utility.showDate_Report(date)).toUpperCase());

            }
            objReportsDto.setPmApproveDate(rs.getString("Pm_Prov_Date"));
            if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) {
                String s1 = rs.getString("Pm_Prov_Date");
                String s3 = s1.substring(0, 7);
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objReportsDto.setPmApproveDate(s5);
            }
            objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setKmsDistance(rs.getString("DISTANCE"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber   
            objReportsDto.setOrdermonth(rs.getString("ORDERMONTH"));
            //objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));
            objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION);
            objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION);
            objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));
            objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus"));
            if (objDto.getIsUsage() == 1) {
                objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
                if (!(rs.getString("BILLINGTRIGGERDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) {

                    Date date = df.parse(objReportsDto.getBillingTriggerDate());
                    objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase());

                }
                ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE");
                if (ts != null) {
                    tempDate = new Date(ts.getTime());
                    objReportsDto
                            .setBillingtrigger_createdate((Utility.showDate_Report(tempDate)).toUpperCase());

                }
                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
                objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC"));

                ComponentsDto dto = new ComponentsDto();
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC"));
                dto.setStartDate(rs.getString("COMPONENT_START_DATE"));
                if (rs.getString("COMPONENT_START_DATE") != null
                        && !"".equals(rs.getString("COMPONENT_START_DATE"))) {

                    Date date = df.parse(dto.getStartDate());
                    dto.setStartDate((Utility.showDate_Report(date)).toUpperCase());

                }
                dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC"));
                dto.setEnd_date(rs.getString("COMPONENT_END_DATE"));
                if (rs.getString("COMPONENT_END_DATE") != null
                        && !"".equals(rs.getString("COMPONENT_END_DATE"))) {

                    Date date = df.parse(dto.getEnd_date());
                    dto.setEnd_date((Utility.showDate_Report(date)).toUpperCase());

                }
                dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
                dto.setFxStartStatus(rs.getString("FX_START_STATUS"));
                dto.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO"));
                dto.setEndFxStatus(rs.getString("FX_END_STATUS"));
                //dto.setComponentFXStatus(rs.getString("FX_STATUS"));
                objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS"));
                objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS"));
                objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS"));
                objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS"));
                objReportsDto.setSourcePartyID(rs.getLong("PARTY_ID"));
                objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID"));
                objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID"));
                dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));

                objReportsDto.setComponentDto(dto);

            } else {
                objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
                if (!(rs.getString("BILLINGTRIGGERDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) {

                    Date date = df.parse(objReportsDto.getBillingTriggerDate());
                    objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase());

                }
                objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
                if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null
                        || rs.getString("BILLING_TRIGGER_CREATEDATE") == "")) {

                    //Date date=df.parse(objReportsDto.getBillingtrigger_createdate());
                    objReportsDto.setBillingtrigger_createdate(
                            (Utility.showDate_Report(rs.getDate("BILLING_TRIGGER_CREATEDATE"))).toUpperCase());

                }
                objReportsDto.setRatio(rs.getString("RATIO"));
                objReportsDto.setBlSource(rs.getString("BL_SOURCE"));
                objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status
                objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeAmount_String(rs.getString("INV_AMT"));
                objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt
                objReportsDto.setStartDate(rs.getString("START_DATE"));
                if (!(rs.getString("START_DATE") == null || rs.getString("START_DATE") == "")) {

                    Date date = df.parse(objReportsDto.getStartDate());
                    objReportsDto.setStartDate((Utility.showDate_Report(date)).toUpperCase());

                }
                objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION"));
                objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No
                objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setStorename(rs.getString("STORENAME"));
                objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD"));
                objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO"));
                //objReportsDto.setSaleType(rs.getString("SALETYPENAME"));//Type Of Sale
            }

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

/**
 * Create a Report to generate LEPM Order Detail Report
         //w  w w .  ja  va  2s .c o  m
 * @param obj   a DTO which consist all the search parameters
 * @return      a ArrayList of dto which consist all the data of reports 
 * @exception   Sql Exception
 *            
 */
public ArrayList<ReportsDto> viewLEPMOrderDetailReport(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewLEPMOrderDetailReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    int countFlag = 0;
    ReportsDto objReportsDto = null;
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlLEPMOrderDetailReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getCopcApproveDate() != null && !"".equals(objDto.getCopcApproveDate())) {
            proc.setString(2, objDto.getCopcApproveDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(3, objDto.getVerticalDetails().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(4, objDto.getFromOrderNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }

        if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) {
            proc.setInt(5, objDto.getToOrderNo());
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(6, pagingSorting.getSortByColumn());// columnName
        proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(8, pagingSorting.getStartRecordId());// start index
        proc.setInt(9, pagingSorting.getEndRecordId());// end index
        proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        rs = proc.executeQuery();

        while (rs.next() != false) {
            objDto = new ReportsDto();
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setServiceId(rs.getInt("SERVICENO"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objDto.setPrimarylocation(rs.getString("FROM_SITE"));
            objDto.setSeclocation(rs.getString("TO_SITE"));
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            objDto.setPrjmngname(rs.getString("PMNAME"));
            objDto.setPrjmgremail(rs.getString("PMEMAIL"));
            objDto.setActmngname(rs.getString("ACTMNAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {

                objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase());

            }
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

                objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase());

            }
            objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objDto.setPmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objDto.setAmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE"));
            if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) {
                objDto.setNio_approve_date(
                        (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE"));
            objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE"));
            objDto.setRfs_date(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {

                objDto.setRfs_date((Utility.showDate_Report((rs.getTimestamp("RFS_DATE")))).toUpperCase());

            }
            objDto.setOrdercategory(rs.getString("ORDERCATEGORY"));
            objDto.setOrderStatus(rs.getString("STATUS"));
            objDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME"));
            objDto.setChargeName(rs.getString("CHARGE_NAME"));
            objDto.setChargeinfoID(rs.getString("CHARGEINFOID"));
            objDto.setServiceProductID(rs.getInt("LINENO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setEntity(rs.getString("COMPANYNAME"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setFrequencyName(rs.getString("PAYMENTTERM"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            if ("NULL".equals(rs.getString("SERVICETYPE"))) {
                objDto.setServiceType("");
            } else {
                objDto.setServiceType(rs.getString("SERVICETYPE"));
            }
            objDto.setUom(rs.getString("UOM"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setFrom_city(rs.getString("FROM_CITY"));
            objDto.setTo_city(rs.getString("TO_CITY"));
            objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL"));
            objDto.setOldlineamt(rs.getString("OLD_LINE_AMT"));
            objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD"));
            objDto.setRatio(rs.getString("RATIO"));
            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setDistance(rs.getString("DISTANCE"));
            objDto.setAccountManager(rs.getString("ACTMEMAIL"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
            objDto.setPoAmount(rs.getString("POAMOUNT"));
            objDto.setBisource(rs.getString("BISOURCE"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));
            objDto.setParent_name(rs.getString("PARENTNAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);

    }

    catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return listSearchDetails;

}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<NewOrderDto> viewOrderStatusList(NewOrderDto objDto) throws Exception {
    //Nagarjuna//from  w w  w  .j  a  va 2s .  c  o m
    String methodName = "viewOrderStatusList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<NewOrderDto> objUserList = new ArrayList<NewOrderDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlOrderList);
        if (objDto.getAccountID() != 0) {
            proc.setLong(1, (objDto.getAccountID()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }
        if (objDto.getAccountName() != null && !"".equals(objDto.getAccountName())) {
            proc.setString(2, objDto.getAccountName().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(3, objDto.getOrderType().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        if (objDto.getOrderNumber() != 0) {
            proc.setLong(4, objDto.getOrderNumber());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(5, objDto.getFromDate().trim());
        } else {
            proc.setNull(5, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(6, objDto.getToDate().trim());
        } else {
            proc.setNull(6, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(7, pagingSorting.getSortByColumn());// columnName
        proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(9, pagingSorting.getStartRecordId());// start index
        proc.setInt(10, pagingSorting.getEndRecordId());// end index
        proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            objDto = new NewOrderDto();
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

                Date date = df.parse(objDto.getOrderDate());
                objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setSourceName(rs.getString("SOURCE"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setCurrencyName(rs.getString("CURNAME"));
            // objDto.setStatus(new
            // Integer(rs.getString("STATUS")).intValue());
            objDto.setStageName(rs.getString("STAGE"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}