Example usage for java.sql CallableStatement setNull

List of usage examples for java.sql CallableStatement setNull

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

Introduction

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

Prototype

void setNull(String parameterName, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

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

public ArrayList<NetworkLocationDto> viewNetworkLocsList(NetworkLocationDto objDto) throws Exception {
    //Nagarjuna//from  w w  w  .  ja va2 s.  c o  m
    String methodName = "viewNetworkLocsList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    ArrayList<NetworkLocationDto> objUserList = new ArrayList<NetworkLocationDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getNetworkLocs = null;

    try {
        String userName = "";
        conn = DbConnection.getReportsConnectionObject();
        getNetworkLocs = conn.prepareCall(sqlGetNetworkLocReport);
        String networkLocationIdStr = objDto.getSearchNetworkLocationIdStr();
        String contactNameStr = objDto.getSearchContactNameStr();

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

        if (contactNameStr == null || contactNameStr.trim().equals("")) {
            getNetworkLocs.setNull(2, java.sql.Types.VARCHAR);
        } else {
            getNetworkLocs.setString(2, contactNameStr);
        }

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

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

        rs = getNetworkLocs.executeQuery();

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

            userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME"));
            objDto = new NetworkLocationDto();
            objDto.setCustomerName(userName);
            objDto.setNetworkLocationId("" + rs.getInt("LOCATION_CODE"));
            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);//nagarjuna
        //ex.printStackTrace();
        //throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(getNetworkLocs);
            DbConnection.freeConnection(conn);

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

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

public ArrayList<M6OrderStatusDto> viewM6OrderList(M6OrderStatusDto objDto) throws Exception {
    //Nagarjuna// w w w  .j av a 2 s .c  o m
    String methodName = "viewM6OrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    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);//nagarjuna
        //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);//nagarjuna
            //e.printStackTrace();
            //throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

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

public ArrayList<BCPAddressDto> viewBCPList(BCPAddressDto objDto) throws Exception {
    //Nagarjuna//from  www .  ja  va 2s.c om
    String methodName = "viewBCPList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    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);//nagarjuna
        //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);//nagarjuna
            //e.printStackTrace();
            //throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

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

/**
 * Create a Report to generate Pending Billing Permanent Disconnection Report
         //from w  ww . jav a 2 s . co 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> viewPendingBillingPDOrderList(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewPendingBillingPDOrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    ArrayList<ReportsDto> objOrderList = new ArrayList<ReportsDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getOrder = null;

    try {
        conn = DbConnection.getReportsConnectionObject();
        getOrder = conn.prepareCall(SPGETSRORDERREPORT);
        String searchCRMOrder = objDto.getSearchCRMOrder();
        String searchfromDate = objDto.getSearchfromDate();
        String searchToDate = objDto.getSearchToDate();
        String searchSrno = objDto.getSearchSRNO();
        String searchLSI = objDto.getSearchLSI();
        if (searchCRMOrder == null || searchCRMOrder.trim().equals("")) {
            getOrder.setNull(1, java.sql.Types.BIGINT);
        } else {
            getOrder.setLong(1, Long.parseLong(searchCRMOrder));
        }
        if (searchfromDate == null || searchfromDate.trim().equals("")) {
            getOrder.setNull(2, java.sql.Types.VARCHAR);
        } else {
            getOrder.setString(2, searchfromDate);
        }

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

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

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

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

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

        int countFlag = 0;
        int recordCount = 0;
        while (rs.next() != false) {
            countFlag++;
            objDto = new ReportsDto();
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setSrno(rs.getString("SRNO"));
            objDto.setSrDate(rs.getString("SR_CREATION_DATE"));
            if (rs.getString("SR_CREATION_DATE") != null && !"".equals(rs.getString("SR_CREATION_DATE"))) {

                objDto.setSrDate(
                        (Utility.showDate_Report((rs.getTimestamp("SR_CREATION_DATE")))).toUpperCase());

            }
            objDto.setLogicalSINo(rs.getString("LSINO"));
            objDto.setOrderStatus(rs.getString("ORDER_STATUS"));
            objDto.setNeworder_remarks(rs.getString("REMARKS"));
            objDto.setProductName(rs.getString("SR_RAISED_BY"));
            objDto.setDisdate(rs.getString("DATE_DIS"));
            if (rs.getString("DATE_DIS") != null && !"".equals(rs.getString("DATE_DIS"))) {

                objDto.setDisdate((Utility.showDate_Report((rs.getTimestamp("DATE_DIS")))).toUpperCase());

            }

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

            objOrderList.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 {
            rs.close();
            getOrder.close();
            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 objOrderList;
}

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

public ArrayList<PendingOrdersAndBillingHardwaresDTO> ViewpendingOrderBillandHardwareList(
        PendingOrdersAndBillingHardwaresDTO objDto) throws Exception {
    //   Nagarjuna
    String methodName = "ViewpendingOrderBillandHardwareList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   //from  w w  w  .  j a v a 2s  .c o  m
    ArrayList<PendingOrdersAndBillingHardwaresDTO> objUserList = new ArrayList<PendingOrdersAndBillingHardwaresDTO>();
    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(sqlPendingOrderBillHardware);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) {
            proc.setInt(2, objDto.getFromAccountNo());
        } else {
            proc.setNull(2, java.sql.Types.BIGINT);
        }
        if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) {
            proc.setInt(3, objDto.getToAccountNo());
        } else {
            proc.setNull(3, java.sql.Types.BIGINT);
        }
        if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromOrderDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);

            proc.setString(4, formattedDate);
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        if (objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToOrderDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(5, formattedDate1);
        } else {
            proc.setNull(5, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromCrmOrderid() != null && !"".equals(objDto.getFromCrmOrderid())) {
            proc.setLong(6, new Long(objDto.getFromCrmOrderid().trim()));
        } else {
            proc.setNull(6, java.sql.Types.BIGINT);
        }
        if (objDto.getToCrmOrderid() != null && !"".equals(objDto.getToCrmOrderid())) {
            proc.setLong(7, new Long(objDto.getToCrmOrderid().trim()));
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }

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

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

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

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

            objDto = new PendingOrdersAndBillingHardwaresDTO();
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setActmngname(rs.getString("ACTMNAME"));
            objDto.setPrjmngname(rs.getString("PMNAME"));
            objDto.setAccountID(rs.getInt("CRMACCOUNTNO"));
            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.setOrderNo(rs.getString("ORDERNO"));
            objDto.setPoNumber(rs.getInt("PODETAILNO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO"));
            objDto.setLinename(rs.getString("LINENAME"));
            //objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            objDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString());

            if (objDto.getChargeAmount_String() == null) {
                objDto.setChargeAmount_String(" ");
            }
            objDto.setStartDate(rs.getString("START_DATE"));
            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {
                Date date = df.parse(objDto.getStartDate());
                objDto.setStartDate((utility.showDate_Report(date)).toUpperCase());
            }

            objDto.setChallenno(rs.getString("CHALLEN_NO"));
            objDto.setChallendate(rs.getString("CHALLEN_DATE"));
            if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) {
                String s1 = rs.getString("CHALLEN_DATE");
                if (s1.length() == 10) {
                    s1 = "0" + s1;
                }
                String s3 = s1.substring(0, 7).toUpperCase();
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objDto.setChallendate(s5);
            }

            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            //[606060] Start
            objDto.setServiceNumber(rs.getInt("SERVICE"));
            objDto.setCurrency(rs.getString("CURRENCY"));
            //[606060] End

            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);//nagarjuna
        //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.java

public ArrayList<ZeroOrderValueReportDTO> viewZeroOrderValueReportDetails(ZeroOrderValueReportDTO objDto)
        throws Exception {

    //   Nagarjuna
    String methodName = "viewZeroOrderValueReportDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   /* ww  w.j  a va  2 s .c  o  m*/
    ArrayList<ZeroOrderValueReportDTO> objUserList = new ArrayList<ZeroOrderValueReportDTO>();
    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(sqlZeroOrdervalueReport);

        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);
        }

        if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) {
            proc.setLong(4, objDto.getFromAccountNo());
            proc.setLong(5, objDto.getToAccountNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
            proc.setNull(5, java.sql.Types.BIGINT);
        }

        if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) {
            proc.setLong(6, objDto.getFromOrderNo());
            proc.setLong(7, objDto.getToOrderNo());
        } else {
            proc.setNull(6, java.sql.Types.BIGINT);
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        if (objDto.getCustPoDetailNo() != null && !"".equals(objDto.getCustPoDetailNo())) {
            proc.setString(8, objDto.getCustPoDetailNo().trim());
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }

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

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

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

            objDto = new ZeroOrderValueReportDTO();

            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setPoDetailNo(rs.getString("PODETAILNUMBER"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setEntityId(rs.getString("ENTITYCODE"));
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));

            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {
                objDto.setCustPoDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime())))
                                .toUpperCase());
            }
            objDto.setPoAmounts(rs.getDouble("POAMOUNT"));
            objDto.setPaymentTerm(rs.getString("PAYMENTTERM"));
            objDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE"));
            if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) {

                Date date = df.parse(objDto.getContractStartDate());
                objDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setContractEndDate(rs.getString("CONTRACTENDDATE"));
            if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) {

                Date date = df.parse(objDto.getContractEndDate());
                objDto.setContractEndDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setPoRecieveDate(rs.getString("PORECEIVEDATE"));
            if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) {

                Date date = df.parse(objDto.getPoRecieveDate());
                objDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setPoIssueBy(rs.getString("ISSUEDBY"));
            objDto.setPoEmailId(rs.getString("EMAILID"));
            objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objDto.setPartyNo(rs.getInt("PARTY_NO"));
            objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objDto.setLineItemDescription(rs.getString("SERVICEPRODUCTID"));
            objDto.setRegionName(rs.getString("REGION"));
            objDto.setChargeAnnotation(rs.getString("ANNOTATION"));
            objDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objDto.setFromLocation(rs.getString("FROM_LOCATION"));
            objDto.setToLocation(rs.getString("TO_LOCATION"));
            objDto.setServiceId(rs.getInt("SERVICEID"));
            objDto.setPoDemoContractPeriod(rs.getString("DEMOCONTRACTPERIOD"));
            objDto.setIsDefaultPO(rs.getInt("ISDEFAULTPO"));
            objDto.setCreatedBy(rs.getString("CREATEDBY"));
            objDto.setCreatedDate(rs.getString("CREATEDDATE"));
            objDto.setOldLsi(rs.getString("OLD_LSI_CRM"));
            objDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO"));
            objDto.setMocn_no(rs.getString("MOCN_NUMBER"));
            if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) {
                objDto.setCreatedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime())))
                                .toUpperCase());
            }
            objDto.setUom(rs.getString("UOM"));
            objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setOrder_type(rs.getString("ORDER_TYPE"));
            objDto.setFxSiId(rs.getString("FX_SI_ID"));
            objDto.setSourceName("UNMIGRATED");
            objDto.setTokenNO(rs.getString("TOKEN_NO"));
            objDto.setPoEndDate(rs.getString("CONTRACTENDDATE"));
            if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) {

                Date date = df.parse(objDto.getPoEndDate());
                objDto.setPoEndDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setLastUpdatedDate("");
            objDto.setLastUpdatedBy("");

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
                //recordCount = countFlag;
            }
            objUserList.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.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.java

public ArrayList<M6OrderStatusDto> viewM6ResponseHistory(M6OrderStatusDto objDto, long m6OrderNo) {
    //Nagarjuna/*from   w w  w .j  av  a2s . co  m*/
    String methodName = "viewM6ResponseHistory", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    Connection connection = null;
    CallableStatement getM6ResponseHistory = null;
    ResultSet rsM6ResponseHistory = null;
    ArrayList<M6OrderStatusDto> listM6ResponseHistory = new ArrayList<M6OrderStatusDto>();
    M6OrderStatusDto objNewOrderDto = null;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

    try {
        connection = DbConnection.getReportsConnectionObject();
        getM6ResponseHistory = connection.prepareCall(sqlGetM6ResponseHistoryReport);

        if (m6OrderNo != 0) {
            getM6ResponseHistory.setLong(1, m6OrderNo);
        } else {
            getM6ResponseHistory.setNull(1, java.sql.Types.BIGINT);
        }
        //   getM6ResponseHistory.setLong(1, Intm6OrderNo);
        rsM6ResponseHistory = getM6ResponseHistory.executeQuery();
        while (rsM6ResponseHistory.next()) {
            objNewOrderDto = new M6OrderStatusDto();
            objNewOrderDto.setOrderNo(rsM6ResponseHistory.getString("orderNo"));
            objNewOrderDto.setM6OrderNo(String.valueOf(m6OrderNo));
            objNewOrderDto.setRemarks(rsM6ResponseHistory.getString("Reason"));
            objNewOrderDto.setCreatedDate((rsM6ResponseHistory.getString("Created_Date")));
            if (!(rsM6ResponseHistory.getString("Created_Date") == null
                    || rsM6ResponseHistory.getString("Created_Date") == "")) {
                objNewOrderDto.setCreatedDate((utility
                        .showDate_Report(new Date(rsM6ResponseHistory.getTimestamp("Created_Date").getTime())))
                                .toUpperCase());
            }

            listM6ResponseHistory.add(objNewOrderDto);
        }
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();   
    } finally {
        try {
            DbConnection.closeResultset(rsM6ResponseHistory);
            DbConnection.closeCallableStatement(getM6ResponseHistory);
            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 listM6ResponseHistory;
}

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

public ArrayList<OrderStageReportDTO> viewOrderStageList(OrderStageReportDTO objDto) throws Exception {
    //      Nagarjuna
    String methodName = "viewOrderStageList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end/*from ww w  .j  a  v a2s . c o  m*/
    ArrayList<OrderStageReportDTO> objUserList = new ArrayList<OrderStageReportDTO>();
    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())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("yyyy-MM-dd");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("yyyy-MM-dd");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, 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 OrderStageReportDTO();
            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"));
            //lawkush start
            objDto.setPoReceiveDate(rs.getString("PORECEIVEDATE"));
            if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) {
                objDto.setPoReceiveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime())))
                                .toUpperCase());
            }
            objDto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {
                objDto.setPoDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase());
            }
            //lawkush End
            //[505052] START
            objDto.setDemoType(rs.getString("DEMO_TYPE"));
            objDto.setCustPoNumber(rs.getString("CUSTPONUMBER"));
            objDto.setPoContractCnd(rs.getString("PO_CONTRACT_CND"));
            if (rs.getString("PO_CONTRACT_CND") != null && !"".equals(rs.getString("PO_CONTRACT_CND"))) {
                objDto.setPoContractCnd(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PO_CONTRACT_CND").getTime())))
                                .toUpperCase());
            }
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setCustomerSegment(rs.getString("CUSTOMER_SEGMENT"));
            objDto.setServiceStatus(rs.getString("SERVICE_STATUS"));
            objDto.setPoDetailNumber(rs.getInt("PO_ID"));
            objDto.setOnnetOffnet(rs.getString("OFFNET_LABELATTVALUE"));
            objDto.setMedia(rs.getString("MEDIA_LABELATTVALUE"));
            objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE"));
            objDto.setPmRemarks(rs.getString("PM_REMARKS"));
            objDto.setServSubTypeName(rs.getString("SUBPRODUCT_NAME"));
            objDto.setOrderStage(rs.getString("ORDER_STAGE"));
            objDto.setTotalChargeAmount(rs.getString("TOTAL_CHARGE_AMT"));
            //[505052] END
            //[007] Start
            objDto.setLdClause(rs.getString("LDCLAUSE"));
            //[007] End
            // [130] start
            objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objDto.setPartnerId(rs.getString("PARTNER_ID"));
            // [130] end
            //[131] start
            objDto.setPartnerCode(rs.getString("PARTNER_CODE"));
            objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER"));
            //[131] end
            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);//nagarjuna
        //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.java

/**
 * Method::viewDummyLineDetailsReport//from w ww  .  j  a  v a2 s.  c o  m
 * @param DummyLinesDetailsReportDTO
 * @author Anil Kumar
 * @return
 */
public ArrayList<DummyLinesDetailsReportDTO> viewDummyLineDetailsReport(DummyLinesDetailsReportDTO objDto) {
    String methodName = "viewDummyLineDetailsReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<DummyLinesDetailsReportDTO> listSearchDetails = new ArrayList<DummyLinesDetailsReportDTO>();
    DummyLinesDetailsReportDTO objReportsDto = null;
    int recordCount = 0;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetDummyLineDetailsReport);

        if (!"0".equals(objDto.getLogical_si_no()) && !"".equals(objDto.getLogical_si_no())) {
            proc.setLong(1, Long.valueOf(objDto.getLogical_si_no()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }

        if (!"0".equals(objDto.getCrmaccountno()) && !"".equals(objDto.getCrmaccountno())
                && objDto.getCrmaccountno() != null) {
            proc.setLong(2, Long.valueOf(objDto.getCrmaccountno()));
        } else {
            proc.setNull(2, java.sql.Types.BIGINT);
        }

        if (!"0".equals(objDto.getLineitemid()) && !"".equals(objDto.getLineitemid())
                && objDto.getLineitemid() != null) {
            proc.setLong(3, Long.valueOf(objDto.getLineitemid()));
        } else {
            proc.setNull(3, java.sql.Types.BIGINT);
        }

        if (!"0".equals(objDto.getServicetypeid()) && !"".equals(objDto.getServicetypeid())
                && objDto.getServicetypeid() != null) {
            proc.setLong(4, Long.valueOf(objDto.getServicetypeid()));
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }

        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));// index

        rs = proc.executeQuery();

        while (rs.next()) {
            objReportsDto = new DummyLinesDetailsReportDTO();

            objReportsDto.setOrderno(rs.getString("ORDERNO"));
            objReportsDto.setLogical_si_no(rs.getString("LOGICAL_SI_NO"));
            objReportsDto.setCust_logical_si_no(rs.getString("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setProductname(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubproductname(rs.getString("SERVICESUBTYPENAME"));
            objReportsDto.setPartyname(rs.getString("PARTYNAME"));
            objReportsDto.setAccountid(rs.getString("ACCOUNTID"));
            objReportsDto.setCrmaccountno(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setRegionname(rs.getString("REGIONNAME"));
            objReportsDto.setCust_seg_code(rs.getString("CUST_SEGMENT_CODE"));
            objReportsDto.setLineitemid(rs.getString("SER_SERVICEPRODUCTID"));
            objReportsDto.setCktid(rs.getString("CKTID"));
            objReportsDto.setFx_si_id(rs.getString("FX_SI_ID"));
            objReportsDto.setFx_account_external_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));
            objReportsDto.setFx_account_internal_id(rs.getString("FX_ACCOUNT_INTERNAL_ID"));
            objReportsDto.setLine_status(rs.getString("LINE_STATUS"));
            objReportsDto.setVerticalname(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setServiceid(rs.getString("SERVICEID"));
            objReportsDto.setServicename(rs.getString("SERVICETYPENAME"));
            objReportsDto.setLineitemname(rs.getString("SERVICEDETDESCRIPTION"));
            objReportsDto.setService_stage(rs.getString("DISC_SERVICE_STAGE"));

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

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

public ArrayList<AdvancePaymentReportDTO> viewAdvancePaymentReport(AdvancePaymentReportDTO objDto)
        throws Exception {
    String methodName = "viewAdvancePaymentReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;

    ArrayList<AdvancePaymentReportDTO> objUserList = new ArrayList<AdvancePaymentReportDTO>();
    AdvancePaymentReportDTO objReportsDto = null;
    Connection conn = null;/*from  ww  w  . j a  v a  2s . c  om*/
    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(sqlAdvancePayementReport);
        if (objDto.getCrmAccountNo() != null && !"".equals(objDto.getCrmAccountNo())) {
            proc.setString(1, objDto.getCrmAccountNo().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        if (objDto.getArcChqNo() != null && !"".equals(objDto.getArcChqNo())) {
            proc.setString(2, objDto.getArcChqNo().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getOtcChqNo() != null && !"".equals(objDto.getOtcChqNo())) {
            proc.setString(3, objDto.getOtcChqNo().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromorderCreationDate() != null && !"".equals(objDto.getFromorderCreationDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            proc.setString(4, objDto.getFromorderCreationDate().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        if (objDto.getToorderCreationDate() != null && !"".equals(objDto.getToorderCreationDate())) {
            proc.setString(5, objDto.getToorderCreationDate().trim());
        } else {
            proc.setNull(5, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromChqDate() != null && !"".equals(objDto.getFromChqDate())) {
            proc.setString(6, objDto.getFromChqDate().trim());
        } else {
            proc.setNull(6, java.sql.Types.VARCHAR);
        }
        if (objDto.getToChqDate() != null && !"".equals(objDto.getToChqDate())) {
            proc.setString(7, objDto.getToChqDate().trim());
        } else {
            proc.setNull(7, java.sql.Types.VARCHAR);
        }
        if (objDto.getDatetype() != null && !"".equals(objDto.getDatetype())) {
            proc.setString(8, objDto.getDatetype().trim());
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

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

        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;
            objReportsDto = new AdvancePaymentReportDTO();
            objReportsDto.setOrderNo(rs.getInt("ORDERNO"));
            objReportsDto.setOrderStatus(rs.getString("ORDER_STATUS"));
            objReportsDto.setOrderCreationDate(rs.getString("ORDER_CREATION_DATE"));
            if (rs.getString("ORDER_CREATION_DATE") != null
                    && !"".equals(rs.getString("ORDER_CREATION_DATE"))) {
                objReportsDto.setOrderCreationDate(
                        (utility.showDate_Report(objReportsDto.getOrderCreationDate())).toUpperCase());
            }

            objReportsDto.setCrmAccountNo(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setCustomerSegment(rs.getString("CUSTOMERSEGMENT"));
            //objReportsDto.setCircle(rs.getString("CIRCLE"));
            objReportsDto.setLicenseCompany(rs.getString("LICENCE_COMPANY"));
            objReportsDto.setCurrencyofOrder(rs.getString("CURRENCY_ORDER"));
            objReportsDto.setAmApprovalDate(rs.getString("AM_APPROVALS"));
            if (rs.getString("AM_APPROVALS") != null && !"".equals(rs.getString("AM_APPROVALS"))) {
                //Date date=df.parse(objReportsDto.getCustPoDate());
                objReportsDto.setAmApprovalDate(
                        utility.showDate_Report(objReportsDto.getAmApprovalDate()).toUpperCase());
            }

            objReportsDto.setPmApprovalDate(rs.getString("PM_APPROVALS"));
            if (rs.getString("PM_APPROVALS") != null && !"".equals(rs.getString("PM_APPROVALS"))) {
                objReportsDto.setPmApprovalDate((utility.showDate_Report(objReportsDto.getPmApprovalDate())));
            }

            objReportsDto.setOrderApprovalDate(rs.getString("COPC_APPROVALS"));
            if (rs.getString("COPC_APPROVALS") != null && !"".equals(rs.getString("COPC_APPROVALS"))) {
                objReportsDto
                        .setOrderApprovalDate((utility.showDate_Report(objReportsDto.getOrderApprovalDate())));
            }
            objReportsDto.setServiceNo(rs.getInt("SERVICE_NO"));
            objReportsDto.setProduct(rs.getString("PRODUCT"));
            objReportsDto.setLsi(rs.getInt("LSI"));
            objReportsDto.setFxChildAccount(rs.getString("FX_CHILD_ACCOUNT"));
            if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) {
                Date date = df.parse(rs.getString("LOC_DATE"));
                objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase());
            }
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {
                Date date = df.parse(rs.getString("BILLINGTRIGGERDATE"));
                objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setArcChqNo(rs.getString("ARCCHQNO"));
            objReportsDto.setArcChqDate(rs.getString("ARCCHQDATE"));

            if (rs.getString("ARCCHQDATE") != null && !"".equals(rs.getString("ARCCHQDATE"))) {
                Date date = df.parse(objReportsDto.getArcChqDate());
                objReportsDto.setArcChqDate((utility.showDate_Report(date)).toUpperCase());
            }

            objReportsDto.setArcChqAmt(rs.getDouble("ARCCHQAMT"));
            objReportsDto.setArcBankName(rs.getString("ARCBANKNAME"));
            objReportsDto.setArcAmtAjd(rs.getDouble("ARCAMTAJD"));
            //objReportsDto.setArcAmtAjd(rs.getString("ARCAMTAJD"));
            objReportsDto.setOtcChqNo(rs.getString("OTCCHQNO"));
            objReportsDto.setArcExempted(rs.getString("ARCEXEMPTED"));
            objReportsDto.setArcExpreason(rs.getString("ARCEXPREASON"));
            objReportsDto.setOtcExempted(rs.getString("OTCEXEMPTED"));
            objReportsDto.setOtcExpreason(rs.getString("OTCEXPREASON"));
            objReportsDto.setOtcChqDate(rs.getString("OTCCHQDATE"));

            //Start [128]
            objReportsDto.setArcReEnterCheckamount(rs.getDouble("ARCRECHQAMT"));
            objReportsDto.setArcReEnterCheckNumber(rs.getString("ARCRECHQNO"));
            objReportsDto.setArcBankAccountNumber(rs.getString("ARCBANKACNO"));
            objReportsDto.setArcReEnterBankAccountNumber(rs.getString("ARCREBANKACNO"));
            objReportsDto.setArcIfscCode(rs.getString("ARCIFSCCODE"));
            objReportsDto.setArcReEnterIfscCode(rs.getString("ARCREIFSCCODE"));

            objReportsDto.setOtcReEnterCheckamount(rs.getDouble("OTCRECHQAMT"));
            objReportsDto.setOtcReEnterCheckNumber(rs.getString("OTCRECHQNO"));
            objReportsDto.setOtcBankAccountNumber(rs.getString("OTCBANKACNO"));
            objReportsDto.setOtcReEnterBankAccountNumber(rs.getString("OTCREBANKACNO"));
            objReportsDto.setOtcIfscCode(rs.getString("OTCIFSCCODE"));
            objReportsDto.setOtcReEnterIfscCode(rs.getString("OTCREIFSCCODE"));
            //End [128]

            objReportsDto.setLineNo(rs.getString("SPID"));
            objReportsDto.setLineName(rs.getString("LineName"));
            if (rs.getString("OTCCHQDATE") != null && !"".equals(rs.getString("OTCCHQDATE"))) {
                Date date = df.parse(objReportsDto.getOtcChqDate());
                objReportsDto.setOtcChqDate((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setOtcChqAmt(rs.getDouble("OTCCHQAMT"));
            objReportsDto.setOtcBankName(rs.getString("OTCBANKNAME"));
            objReportsDto.setOtcAmtAjd(rs.getDouble("OTCAMTAJD"));
            objReportsDto.setPartyNo(rs.getString("PARTY_NO"));
            objReportsDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objReportsDto.setBillingTriggerActionDate(rs.getTimestamp("BILLINGTRIGGERDATEACTION"));
            String BillActionDate_String = utility.showDate_Report(objReportsDto.getBillingTriggerActionDate());
            objReportsDto.setBillingTriggerActionDate_string(BillActionDate_String);
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubproductName(rs.getString("SERVICESUBTYPENAME"));
            objReportsDto.setParty_id(rs.getString("PARTY_ID"));
            objReportsDto.setPodate(rs.getDate("PODATE"));
            String poDate_String = utility.showDate_Report(objReportsDto.getPodate());
            objReportsDto.setPodate_String(poDate_String);
            objReportsDto.setPoRecieveDate(rs.getDate("PORECEIVEDATE"));
            String poRDate_String = utility.showDate_Report(objReportsDto.getPoRecieveDate());
            objReportsDto.setPoRecieveDate_String(poRDate_String);
            objReportsDto.setZoneName(rs.getString("ZONENNAME"));
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }

            objUserList.add(objReportsDto);
        }
        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.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;
}