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_Usage.java

public ArrayList<ReportsDto> viewAttributeDetailsReport(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "viewAttributeDetailsReport", 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 av a 2s . com*/
    int recordCount = 0;
    ArrayList<ReportsDto> listAttributeDetailsReport = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetAttributeDetailsReport);

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

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

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();

            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setLinename(rs.getString("LINENAME"));
            objReportsDto.setCrm_att(rs.getString("CRM_ATT"));
            objReportsDto.setM6_label_name(rs.getString("LABEL_NAME"));
            objReportsDto.setM6_label_value(rs.getString("LABEL_VALUE"));
            objReportsDto.setCustLogicalSI(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setCrm_order_id(rs.getInt("LAST_CRM_ORDER_ID"));

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

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

public ArrayList<ReportsDto> viewOrderStageList(ReportsDto objDto) throws Exception {
    //Nagarjuna//from   www  .  j a v a2s.  co  m
    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  www  . j  a v  a 2s . 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

/**
 * /*ww w .  j  av a2s  . c  o  m*/
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<NewOrderDto> viewOrderReportChange(NewOrderDto objDto) throws Exception {
    //Nagarjuna
    String methodName = "viewOrderReportChange", 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(sqlOrderReportChange);

        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);
        }
        //Company Name
        proc.setNull(4, java.sql.Types.VARCHAR);
        // Logical Si no
        if (objDto.getLogicalSINo() != null && !"".equals(objDto.getLogicalSINo())) {
            proc.setLong(5, new Long(objDto.getLogicalSINo().trim()));
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }

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

        if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) {
            proc.setLong(8, objDto.getFromOrderNo());
            proc.setLong(9, objDto.getToOrderNo());
        } else {
            proc.setNull(8, java.sql.Types.BIGINT);
            proc.setNull(9, java.sql.Types.BIGINT);
        }

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

        proc.setString(10, pagingSorting.getSortByColumn());// columnName
        proc.setString(11, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(12, pagingSorting.getStartRecordId());// start index
        proc.setInt(13, pagingSorting.getEndRecordId());// end index
        proc.setInt(14, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(15, objDto.getOsp().trim());
        } else {
            proc.setNull(15, java.sql.Types.VARCHAR);
        }

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

            objDto = new NewOrderDto();
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objDto.setServiceSubTypeName(rs.getString("SERVICESTAGE"));
            objDto.setServiceId(rs.getInt("SERVICEID"));
            objDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID"));
            objDto.setLOC_Date(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

                Date date = df.parse(objDto.getLOC_Date());
                objDto.setLOC_Date((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setPoAmount(rs.getString("POAMOUNT"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setFrequencyName(rs.getString("FREQUENCYNAME"));
            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.setAmApproveDate(rs.getString("AM_RECEIVE_DATE"));

            objDto.setOsp(rs.getString("OSP"));

            if (rs.getString("AM_RECEIVE_DATE") != null && !"".equals(rs.getString("AM_RECEIVE_DATE"))) {
                objDto.setAmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("AM_RECEIVE_DATE").getTime())))
                                .toUpperCase());
            }
            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_Usage.java

public ArrayList<ReportsDto> ViewpendingOrderBillandHardwareList(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "ViewpendingOrderBillandHardwareList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;//from w ww  .  j ava2 s .  co m
    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())) {
            proc.setString(4, objDto.getFromOrderDate().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        if (objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) {
            proc.setString(5, objDto.getToOrderDate().trim());
        } 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 ReportsDto();
            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.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            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");
                String s3 = s1.substring(0, 7);
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objDto.setChallendate(s5);
            }

            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));

            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_Usage.java

/**
 * Create a Report to generate Pending Billing Permanent Disconnection Report
         //from   www  .j av a  2  s . 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> viewPendingBillingPDOrderList(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewPendingBillingPDOrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    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_Usage.java

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

    //   Nagarjuna
    String methodName = "viewZeroOrderValueReportDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;/*from w  w  w.  ja  v a  2 s. co  m*/
    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 ReportsDto();

            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"));
            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_Usage.java

public ArrayList<M6OrderStatusDto> viewM6ResponseHistory(M6OrderStatusDto objDto, long m6OrderNo) {
    //Nagarjuna//from  ww  w  .j av a  2s.  c o m
    String methodName = "viewM6ResponseHistory", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    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) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
    } 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);
        }
    }
    return listM6ResponseHistory;
}

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

/**
 * Create a Report to generate LEPM Order Cancel Report
         //from  w w w  .  ja  v  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> viewLEPMOrderCancelReport(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewLEPMOrderCancelReport", 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(sqlLEPMOrderCancelReport);
        if (objDto.getCanceldate() != null && !"".equals(objDto.getCanceldate())) {
            proc.setString(1, objDto.getCanceldate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

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

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (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"));
            objDto.setServiceStage(rs.getString("STAGE"));
            objDto.setCanceldate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {

                objDto.setCanceldate((Utility.showDate_Report((rs.getTimestamp("CANCEL_DATE")))).toUpperCase());

            }
            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

/**
 * method to fetch data for LEPM Owner report.
 * @param objDto/*from  w  w  w.  j  a v  a2s  .c  om*/
 * @return
 */
public ArrayList<ReportsDto> viewLEPMOwnerReport(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "viewLEPMOwnerReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    ArrayList<ReportsDto> listLEPMOwnerReport = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetLEPMOwnerReport);

        if (objDto.getOrderNo() != null && !"".equals(objDto.getOrderNo())) {
            proc.setInt(1, new Integer(objDto.getOrderNo()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }
        if (objDto.getCopcApproveDate() != null && !"".equals(objDto.getCopcApproveDate())) {
            proc.setString(2, objDto.getCopcApproveDate());
        } 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));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setPm_pro_date(rs.getString("ACTUAL_START_DATE"));
            if (!(rs.getString("ACTUAL_START_DATE") == null || rs.getString("ACTUAL_START_DATE") == "")) {

                objReportsDto.setPm_pro_date(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ACTUAL_START_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setPmapprovaldate(rs.getString("PLANNED_END_DATE"));
            if (!(rs.getString("PLANNED_END_DATE") == null || rs.getString("PLANNED_END_DATE") == "")) {

                objReportsDto.setPmapprovaldate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PLANNED_END_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setPmApproveDate(rs.getString("ACTUAL_END_DATE"));
            if (!(rs.getString("ACTUAL_END_DATE") == null || rs.getString("ACTUAL_END_DATE") == "")) {

                objReportsDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ACTUAL_END_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVAL_DATE"));
            if (!(rs.getString("ORDER_APPROVAL_DATE") == null || rs.getString("ORDER_APPROVAL_DATE") == "")) {

                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }

            objReportsDto.setOrderNo(rs.getString("CRM_ORDER_ID"));
            objReportsDto.setPmName(rs.getString("OWNER_NAME"));
            objReportsDto.setContactCell(rs.getString("OWNER_PHONE"));
            objReportsDto.setEmailId(rs.getString("OWNER_EMAILID"));
            objReportsDto.setUserName(rs.getString("USER_NAME"));
            objReportsDto.setTaskNumber(rs.getInt("TASK_NUMBER"));
            objReportsDto.setTaskName(rs.getString("TASK_NAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listLEPMOwnerReport.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();   
    } finally {
        try {
            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 listLEPMOwnerReport;
}