Example usage for java.sql CallableStatement 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

/**
 * /*from  ww  w.j  a v a 2s .co  m*/
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<OrderDetailChangeReportDTO> viewOrderReportChange(OrderDetailChangeReportDTO objDto)
        throws Exception {
    //   Nagarjuna
    String methodName = "viewOrderReportChange", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   
    ArrayList<OrderDetailChangeReportDTO> objUserList = new ArrayList<OrderDetailChangeReportDTO>();
    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())) {
            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);
        }*/
        //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);
        }
        if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate())
                && objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) {
            proc.setString(16, objDto.getFromOrderDate());
            proc.setString(17, objDto.getToOrderDate());
        } else {
            proc.setNull(16, java.sql.Types.VARCHAR);
            proc.setNull(17, java.sql.Types.VARCHAR);
        }

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

            objDto = new OrderDetailChangeReportDTO();
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setAccountManager(rs.getString("ACCT_MGR_NAME"));
            objDto.setAccountno(rs.getString("CRMACCOUNTNO"));
            objDto.setCustSegmentCode(rs.getString("CUST_SEGMENT_CODE"));
            objDto.setAccountCategory(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setServiceSubTypeName(rs.getString("SERVICESTAGE"));
            objDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));
            objDto.setChargeStartDate(rs.getString("START_DATE"));
            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {
                Date date = df.parse(objDto.getChargeStartDate());
                objDto.setChargeStartDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setCancelFlag(rs.getString("CANCELFLAG"));
            objDto.setEntity(rs.getString("ENTITYNAME"));
            objDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getDate("ORDERDATE") != null && !"".equals(rs.getDate("ORDERDATE"))) {
                Date date = rs.getDate("ORDERDATE");
                objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {
                Date date = df.parse(objDto.getCustPoDate());
                objDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setLineItemQuantity(rs.getString("ITEM_QUANTITY"));
            objDto.setBillingBandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBillingBandwidthUOM(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setDistance(rs.getString("DISTANCE"));
            objDto.setLineItemAmount(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString());
            objDto.setChargeAnnotation(rs.getString("ANNOTATION"));
            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.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.setRfsDate(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                Date date = df.parse(objDto.getRfsDate());
                objDto.setRfsDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setRfs_date(rs.getString("CUST_RFS_DATE"));
            if (rs.getString("CUST_RFS_DATE") != null && !"".equals(rs.getString("CUST_RFS_DATE"))) {
                Date date = df.parse(objDto.getRfs_date());
                objDto.setRfs_date((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setPoAmount(BigDecimal.valueOf((rs.getDouble("POAMOUNT"))).toPlainString());
            objDto.setOrderTotalAmount(BigDecimal.valueOf((rs.getDouble("TOT_AMOUNT"))).toPlainString());
            objDto.setAmApproveDate(rs.getString("AM_RECEIVE_DATE"));
            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());
            }
            objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objDto.setStartDate(rs.getString("CHARGE_CRT_ORDERNO"));
            objDto.setEndDate(rs.getString("END_ORDER_NO"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setFrequencyName(rs.getString("FREQUENCYNAME"));
            objDto.setProjectManager(rs.getString("PRJ_MGR_NAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setRatio(rs.getString("RATIO"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setOsp(rs.getString("OSP"));
            // Anadi's   
            objDto.setCopcEndDate(rs.getString("COPC_END_DATE"));

            if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) {
                objDto.setCopcEndDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime())))
                                .toUpperCase());

                //Date date=df.parse(objDto.getCopcEndDate());
                //objDto.setCopcEndDate((utility.showDate_Report(date)).toUpperCase());
            }
            // End : Anadi   
            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

/**
 * //ww w  .  j a  v  a2  s.  c om
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<OrderDetailReportDTO> viewOrderReportDetails(OrderDetailReportDTO objDto) throws Exception {

    //   Nagarjuna
    String methodName = "viewOrderReportDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   
    ArrayList<OrderDetailReportDTO> objUserList = new ArrayList<OrderDetailReportDTO>();
    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(sqlOrderReportDetail);

        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.getDemo() != null && !"".equals(objDto.getDemo())) {
            proc.setString(4, objDto.getDemo().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

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

        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
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(14, objDto.getOsp().trim());
        } else {
            proc.setNull(14, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate())
                && objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) {
            proc.setString(15, objDto.getFromOrderDate());
            proc.setString(16, objDto.getToOrderDate());
        } else {
            proc.setNull(15, java.sql.Types.VARCHAR);
            proc.setNull(16, java.sql.Types.VARCHAR);
        }

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

            objDto = new OrderDetailReportDTO();
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objDto.setPoAmounts(rs.getDouble("POAMOUNT"));
            objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setBillingPODate(rs.getString("START_DATE"));
            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {
                DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                DateFormat dformat2 = new SimpleDateFormat("dd-MM-yyyy");
                Date date;
                if (objDto.getBillingPODate().length() > 10) {
                    date = dformat.parse(objDto.getBillingPODate());
                } else if (objDto.getBillingPODate().indexOf('-') != -1) {
                    date = dformat2.parse(objDto.getBillingPODate());
                } else {
                    date = df.parse(objDto.getBillingPODate());
                }
                objDto.setBillingPODate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setServiceDetDescription(rs.getString("SERVICESTAGE"));
            objDto.setOrderLineNumber(rs.getInt("SERVICEPRODUCTID"));
            objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
            objDto.setCancelflag(rs.getString("CANCELFLAG"));
            objDto.setProvisionBandWidth(rs.getString("BANDWIDTH"));
            objDto.setUom(rs.getString("UOM"));
            objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setStoreName(rs.getString("STORENAME"));
            objDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setCategoryOfOrder(rs.getString("ORDERCATEGORY"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setCompanyName(rs.getString("COMPANYNAME"));
            objDto.setOrderDate(rs.getString("ORDERCREATIONDATE"));
            if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) {
                Date date = rs.getDate("ORDERCREATIONDATE");
                objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setCustomerRfsDate(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                Date date;
                if (objDto.getCustomerRfsDate().indexOf('-') != -1) {
                    date = dformat.parse(objDto.getCustomerRfsDate());
                } else {
                    date = df.parse(objDto.getCustomerRfsDate());
                }
                objDto.setCustomerRfsDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setCustomerServiceRfsDate(rs.getString("SERVICE_RFS_DATE"));
            if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) {

                Date date = df.parse(objDto.getCustomerServiceRfsDate());
                objDto.setCustomerServiceRfsDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setCurrencyCode(rs.getString("CURRENCYNAME"));
            objDto.setChargeName(rs.getString("CHARGE_NAME"));
            objDto.setCustomerPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {

                Date date = df.parse(objDto.getCustomerPoDate());
                objDto.setCustomerPoDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setCustomerPoNumber(rs.getString("CUSTPONUMBER"));
            objDto.setCyclicNonCyclic(rs.getString("CYCLIC_NONCYCLIC"));
            objDto.setChallenno(rs.getString("CHALLEN_NO"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setFromSite(rs.getString("PRIMARYLOCATION"));
            objDto.setFromSite(objDto.getFromSite() == null ? null : objDto.getFromSite().replaceAll("~", ""));
            objDto.setToSite(rs.getString("SECONDARYLOCATION"));
            objDto.setToSite(objDto.getToSite() == null ? null : objDto.getToSite().replaceAll("~", ""));
            objDto.setItemQuantity(1);
            objDto.setKmsDistance(rs.getString("DISTANCE"));
            objDto.setChargeAmount(rs.getDouble("LINEITEMAMOUNT"));
            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.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION"));
            objDto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

                Date date = df.parse(objDto.getLocDate());
                objDto.setLocDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setAmReceiveDate(rs.getString("AMRECEIVEDATE"));
            if (rs.getString("AMRECEIVEDATE") != null && !"".equals(rs.getString("AMRECEIVEDATE"))) {

                Date date = df.parse(objDto.getAmReceiveDate());
                objDto.setAmReceiveDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setOrderTotal(rs.getDouble("POAMOUNT"));
            objDto.setOrderEntryDate(rs.getString("ORDERCREATIONDATE"));
            if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) {
                Date date = rs.getDate("ORDERCREATIONDATE");
                objDto.setOrderEntryDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setLicenceCoName(rs.getString("LCOMPANYNAME"));
            objDto.setLogicalCircuitNumber(rs.getString("LOGICAL_CIRCUITID"));
            objDto.setOrderType(rs.getString("ORDERCATEGORY"));
            objDto.setPaymentTerm(rs.getString("PAYMENTTERM"));
            objDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setOldLineitemAmount("");
            objDto.setDemoType(rs.getString("DEMO_TYPE"));
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderStageDescription(rs.getString("STAGE"));
            objDto.setServiceStageDescription(rs.getString("SERVICE_STAGE"));
            objDto.setChargeEndDate(rs.getString("END_DATE"));
            if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) {

                Date date = df.parse(objDto.getChargeEndDate());
                objDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setEndDateLogic(rs.getString("ENDDATELOGIC"));
            objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS"));
            objDto.setRemarks(rs.getString("REMARKS"));
            objDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE"));
            objDto.setOsp(rs.getString("OSP"));

            //         [404040] Start 
            objDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            //<!--GlobalDataBillingEfficiency BFR6  -->
            objDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON"));
            //[130] start
            objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE"));
            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");
                //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

/**
 * method to fetch data for LEPM Owner report.
 * @param objDto/*  w  ww  . j  a  v  a 2 s.c o  m*/
 * @return
 */
public ArrayList<LempOwnerReportDTO> viewLEPMOwnerReport(LempOwnerReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewLEPMOwnerReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    ArrayList<LempOwnerReportDTO> listLEPMOwnerReport = new ArrayList<LempOwnerReportDTO>();
    LempOwnerReportDTO 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);
        }
        //[303030]START
        if (objDto.getCopcApproveFromDate() != null && !"".equals(objDto.getCopcApproveFromDate())
                && objDto.getCopcApproveToDate() != null && !"".equals(objDto.getCopcApproveToDate())) {
            //proc.setString(2, objDto.getCopcApproveDate());
            proc.setString(2, objDto.getCopcApproveFromDate());
            proc.setString(3, objDto.getCopcApproveToDate());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        //[303030]END
        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));
        // index
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate()) && objDto.getToDate() != null
                && !"".equals(objDto.getToDate())) {
            //proc.setString(2, objDto.getCopcApproveDate());
            proc.setString(9, objDto.getFromDate());
            proc.setString(10, objDto.getToDate());
        } else {
            proc.setNull(9, java.sql.Types.VARCHAR);
            proc.setNull(10, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new LempOwnerReportDTO();
            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) {
        //ex.printStackTrace();
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } 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;
}

---

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

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

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlPendingOrderAndBillingReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getLOC_Date() != null && !"".equals(objDto.getLOC_Date())) {
            proc.setString(2, objDto.getLOC_Date());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) {
            proc.setInt(3, objDto.getFromAccountNo());
        } else {
            proc.setNull(3, java.sql.Types.BIGINT);
        }
        if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) {
            proc.setInt(4, objDto.getToAccountNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }
        if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(5, formattedDate);
        } else {
            proc.setNull(5, java.sql.Types.VARCHAR);
        }
        if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(6, formattedDate1);
        } else {
            proc.setNull(6, java.sql.Types.VARCHAR);
        }

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

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

        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 PendingOrderAndBillingReportDTO();
            objDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setCrm_productname(rs.getString("CRM_PRODUCT_NAME"));
            objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setCompanyName(rs.getString("COMPANYNAME"));
            objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {

                Date date = df.parse(objDto.getCustPoDate());
                objDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setLcompanyname(rs.getString("LICENCECOMPANYNAME"));
            objDto.setChargeName(rs.getString("CHARGE_NAME"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            objDto.setStorename(rs.getString("STORENAME"));
            objDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objDto.setAnnualRate(rs.getString("ANNUAL_RATE"));
            if (objDto.getAnnualRate() == null) {
                objDto.setAnnualRate(" ");
            }
            objDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString());

            if (objDto.getChargeAmount_String() == null) {
                objDto.setChargeAmount_String(" ");
            }
            objDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
            objDto.setActmngname(rs.getString("ACTMNAME"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setFrequencyName(rs.getString("PAYMENTTERM"));
            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.setPm_pro_date(rs.getString("PM_PROV_DATE"));
            if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) {
                String s1 = rs.getString("PM_PROV_DATE");
                String s3 = s1.substring(0, 7).toUpperCase();
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objDto.setPm_pro_date(s5);
            }
            objDto.setOrderDate(rs.getString("POCREATEDATE"));
            if (rs.getString("POCREATEDATE") != null && !"".equals(rs.getString("POCREATEDATE"))) {

                objDto.setOrderDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("POCREATEDATE").getTime())))
                                .toUpperCase());

            }

            objDto.setPoNumber(rs.getInt("PONUMBER"));
            objDto.setPrjmngname(rs.getString("PMNAME"));
            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.setTaxation(rs.getString("TAXATION"));
            objDto.setUom(rs.getString("UOM"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            //objDto.setOsp(rs.getString("OSP"));

            objDto.setServiceProductId(rs.getString("SERVICEPRODUCTID")); //  StartChange by Anadi's Change
            objDto.setBillingTriggStatus(rs.getString("BILLING_TRIGGER_STATUS"));
            objDto.setChargeInfoId(rs.getString("CHARGEINFOID"));
            objDto.setChangeServiceId(rs.getString("CHANGE_SERVICEID")); //  EndChange By Anadi 

            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<OBValueReportDTO> viewOBValueReport(OBValueReportDTO objDto) throws Exception {

    String methodName = "viewOBValueReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    ArrayList<OBValueReportDTO> objUserList = new ArrayList<OBValueReportDTO>();
    Connection conn = null;//  ww w.  jav  a 2s  . c om
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    OBValueReportDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        conn = DbConnection.getReportsConnectionObject();
        proc = conn.prepareCall(sqlOBValueReport);

        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("MM-dd-yyyy");
            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("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(4, objDto.getVerticalDetails().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(5, pagingSorting.getSortByColumn());// columnName
        proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(7, pagingSorting.getStartRecordId());// start index
        proc.setInt(8, pagingSorting.getEndRecordId());// end index
        proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            objDto = new OBValueReportDTO();
            objDto.setPartyNo(rs.getInt("PARTY_NO"));
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            objDto.setLineType(rs.getString("LINETYPE"));
            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.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setServiceId(rs.getInt("SERVICENO"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6]
                            + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objDto.setPrimaryLocation("");
            }
            if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " "
                            + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objDto.setSeclocation("");
            }
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            objDto.setPrjmngname(rs.getString("PRJ_MGR_NAME"));
            objDto.setPrjmgremail(rs.getString("PROJECTMGR_MAIL"));
            objDto.setActmngname(rs.getString("ACCT_MGR_NAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            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.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.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.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.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(new Date(rs.getTimestamp("RFS_DATE").getTime())))
                        .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.setEntity(rs.getString("COMPANYNAME"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setFrequencyName(rs.getString("PAYMENTTERM"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            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"));
            if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_CITY").split("~~");
                    objDto.setFrom_city(ss[8]);
                } else {
                    objDto.setFrom_city(" ");
                }
            } else {
                objDto.setFrom_city("");
            }
            if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_CITY").split("~~");
                    objDto.setTo_city(ss[8]);
                } else {
                    objDto.setTo_city(" ");
                }
            } else {
                objDto.setTo_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("ACCOUNTMGR_EMAIL"));
            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.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setLoc_date(rs.getString("LOC_DATE"));
            objDto.setActualOB(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB"))).toPlainString());
            objDto.setActualOBINR(Double.valueOf(Utility.round(rs.getDouble("ACTUAL_OB_INR"), 2)).toString());
            //objDto.setActualOBINR(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB_INR"))).toPlainString());

            objDto.setFinalOB(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString());
            objDto.setFinalOBINR(Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_INR"), 2)).toString());
            //objDto.setFinalOBINR(Double.valueOf((rs.getDouble("OB_VALUE_INR"))).toString());

            objDto.setChargeRemarks(rs.getString("REMARKS"));
            objDto.setCopcApprovedBy(rs.getString("COPC_APPROVER_NAME"));
            objDto.setPmApprovedby(rs.getString("PM_APPROVER_NAME"));
            objDto.setDemoFlag(rs.getString("ISDEMO"));
            objDto.setOffnet(rs.getString("OFFNET_LABELATTVALUE"));
            objDto.setMediaType(rs.getString("MEDIA_LABELATTVALUE"));
            objDto.setCancellationReason(rs.getString("CANCELLATION_REASON"));
            objDto.setrRDate(rs.getString("RR_DATE"));
            if (rs.getString("RR_DATE") != null && !"".equals(rs.getString("RR_DATE"))) {
                objDto.setrRDate((utility.showDate_Report(new Date(rs.getTimestamp("RR_DATE").getTime())))
                        .toUpperCase());
            }

            objDto.setDiffDays(rs.getString("DIFF_DAYS"));
            objDto.setTotalDays(rs.getString("TOTALDAYS"));
            objDto.setEffectiveDays(rs.getString("EFFECTIVE_DAYS"));
            objDto.setMn(BigDecimal.valueOf((rs.getDouble("MN"))).toPlainString()); //using this value for ob value
            objDto.setOldPKChargeid(rs.getLong("OBLINKCHARGE"));
            objDto.setOldChargeAmount(BigDecimal.valueOf((rs.getDouble("OBLINKCHARGEAMOUNT"))).toPlainString());
            objDto.setOrderEnteredBy(rs.getString("ORDER_CREATED_BY_NAME"));
            objDto.setExchangeRate(BigDecimal.valueOf((rs.getDouble("EXCHANGE_RATE"))).toPlainString());
            objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE_TRANSACTION"))).toPlainString());
            objDto.setObValueINR(
                    Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_TRANSACTION_INR"), 2)).toString());

            objDto.setCustomerSegment(rs.getString("CUST_SEGMENT_CODE")); //newly added
            objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE")); //newly added
            objDto.setServiceRemarks(rs.getString("SERVICE_REMARKS")); // newly added

            objDto.setObMonth(rs.getString("OB_MONTH"));
            objDto.setObYear(rs.getString("OB_YEAR")); //[129]

            objDto.setEntryType(rs.getString("ENTRY_TYPE"));
            objDto.setIsNfa(rs.getString("IS_NFA"));
            objDto.setChargeperiod(rs.getString("TOTAL_DAYS"));
            objDto.setCopcApproveDate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {
                objDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime())))
                                .toUpperCase());
            }
            //[130] Start
            objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE"));
            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);
        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);
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}

---

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

/**
 * Create a Report to generate LEPM Order Detail Report
         /*  w w  w  .  ja  va2  s  .  c om*/
 * @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<LempOrderDetailsReportDTO> viewLEPMOrderDetailReport(LempOrderDetailsReportDTO objDto)
        throws Exception {
    //   Nagarjuna
    String methodName = "viewLEPMOrderDetailReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    ArrayList<LempOrderDetailsReportDTO> objUserList = new ArrayList<LempOrderDetailsReportDTO>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    int countFlag = 0;
    LempOrderDetailsReportDTO objReportsDto = null;
    ArrayList<LempOrderDetailsReportDTO> listSearchDetails = new ArrayList<LempOrderDetailsReportDTO>();

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlLEPMOrderDetailReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        //[202020]START
        if (objDto.getCopcApproveFromDate() != null && !"".equals(objDto.getCopcApproveFromDate())
                && objDto.getCopcApproveToDate() != null && !"".equals(objDto.getCopcApproveToDate())) {
            proc.setString(2, objDto.getCopcApproveFromDate().trim());
            proc.setString(3, objDto.getCopcApproveToDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        //[202020]END
        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(4, objDto.getVerticalDetails().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

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

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

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

        while (rs.next() != false) {
            objDto = new LempOrderDetailsReportDTO();
            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_String(rs.getString("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"));
            //Vijay [RPT7052013010] -start. 
            //fetch the only city becoz of the FROM_CITY & TO_CITY has full adDress

            objDto.setFrom_city(rs.getString("FROM_CITY"));
            String frmCity = rs.getString("FROM_CITY");
            try {
                if (frmCity != null) {
                    String[] fcity = frmCity.split("~~");
                    objDto.setFrom_city(fcity[5]);
                }
            } catch (IndexOutOfBoundsException e) {
                //e.printStackTrace();
            }

            objDto.setTo_city(rs.getString("TO_CITY"));
            String toCity = rs.getString("TO_CITY");
            try {
                if (toCity != null) {
                    String[] tcity = toCity.split("~~");
                    objDto.setTo_city(tcity[5]);
                }
            } catch (IndexOutOfBoundsException e) {
                Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
                //e.printStackTrace();
            }
            //Vijay. [RPT7052013010] - end 

            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.setTotalPoAmt(rs.getString("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"));
            //[404040] Start 
            objDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            //[505050] Start
            objDto.setOnnetOffnet((rs.getString("OFFNET_LABELATTVALUE")));
            objDto.setMedia((rs.getString("MEDIA_LABELATTVALUE")));
            objDto.setServOrderCategory((rs.getString("ORDERCATGRY_LABELATTVALUE")));
            //[505050] End

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

public ArrayList<AttributeDetailsReportDTO> viewAttributeDetailsReport(AttributeDetailsReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewAttributeDetailsReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end/*  w  w  w . j  ava2s .  c  o m*/
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    ArrayList<AttributeDetailsReportDTO> listAttributeDetailsReport = new ArrayList<AttributeDetailsReportDTO>();
    AttributeDetailsReportDTO objReportsDto = null;
    SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    SimpleDateFormat formatter2 = new SimpleDateFormat("MM-dd-yyyy");

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

        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            Date dateStr = formatter.parse(objDto.getFromDate().trim());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formattedDate = formatter2.format(date1);
            proc.setString(2, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            Date dateStr = formatter.parse(objDto.getToDate().trim());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formattedDate = formatter2.format(date1);
            proc.setString(3, formattedDate);
            //proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

        if (objDto.getServiceTypeId() != 0 && !"".equals(objDto.getServiceTypeId())) {
            proc.setInt(4, 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 AttributeDetailsReportDTO();

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

public ArrayList<HardwareLineItemCancelReportDTO> viewHardwareCancelReport(
        HardwareLineItemCancelReportDTO objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewHardwareCancelReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end//from  www . j  ava 2  s .  c  o  m
    ArrayList<HardwareLineItemCancelReportDTO> objHardwareLineItemList = new ArrayList<HardwareLineItemCancelReportDTO>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getHardwareLineItemList = null;
    try {
        conn = DbConnection.getConnectionObject();
        getHardwareLineItemList = conn.prepareCall(SPGETCANCELHARDWARELINEITEMREPORT);
        String searchLineItemNo = objDto.getSearchLineNo();
        String searchfromDate = objDto.getSearchfromDate();
        String searchToDate = objDto.getSearchToDate();
        String searchSrno = objDto.getSearchSRNO();
        String searchLSI = objDto.getSearchLSI();
        String searchAccountNo = objDto.getSearchAccount();
        String searchAccountName = objDto.getSearchAccountName();

        if (searchAccountNo == null || searchAccountNo.trim().equals("")) {
            getHardwareLineItemList.setNull(1, java.sql.Types.VARCHAR);
        } else {
            getHardwareLineItemList.setString(1, searchAccountNo);
        }
        if (searchAccountName == null || searchAccountName.trim().equals("")) {
            getHardwareLineItemList.setNull(2, java.sql.Types.VARCHAR);
        } else {
            getHardwareLineItemList.setString(2, searchAccountName);
        }
        if (searchfromDate == null || searchfromDate.trim().equals("")) {
            getHardwareLineItemList.setNull(3, java.sql.Types.VARCHAR);
        } else {
            getHardwareLineItemList.setString(3, searchfromDate);
        }
        if (searchToDate == null || searchToDate.trim().equals("")) {
            getHardwareLineItemList.setNull(4, java.sql.Types.VARCHAR);
        } else {
            getHardwareLineItemList.setString(4, searchToDate);
        }

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

        if (searchLSI == null || searchLSI.trim().equals("")) {
            getHardwareLineItemList.setNull(6, java.sql.Types.VARCHAR);
        } else {
            getHardwareLineItemList.setString(6, searchLSI);
        }
        if (searchLineItemNo == null || searchLineItemNo.trim().equals("")) {
            getHardwareLineItemList.setNull(7, java.sql.Types.BIGINT);
        } else {
            getHardwareLineItemList.setString(7, searchLineItemNo);
        }

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

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

        int countFlag = 0;
        int recordCount = 0;
        while (rs.next() != false) {
            countFlag++;
            objDto = new HardwareLineItemCancelReportDTO();
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setSrno(rs.getString("REQUESTID"));
            objDto.setOrderLineNumber(rs.getInt("LINEITEMNO"));
            objDto.setLineItemName(rs.getString("LINEITEMNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setLogicalSINo(rs.getString("LSINO"));
            objDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            if (rs.getInt("REQUESTSTATUS") == 0 || rs.getInt("REQUESTSTATUS") == 1) {
                objDto.setRemarks("Open");
            } else if (rs.getInt("REQUESTSTATUS") == 2) {
                objDto.setRemarks("Failed in M6");
            } else if (rs.getInt("REQUESTSTATUS") == 3) {
                objDto.setRemarks("Closed");
            }
            objDto.setCreatedBy(rs.getString("REQUESTEDBY"));
            objDto.setUserId(rs.getString("USER_ID"));
            objDto.setSrDate(rs.getString("REQUESTDATE"));
            if (rs.getString("REQUESTDATE") != null && !"".equals(rs.getString("REQUESTDATE"))) {
                objDto.setSrDate((Utility.showDate_Report((rs.getTimestamp("REQUESTDATE")))).toUpperCase());
            }
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objHardwareLineItemList.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();
            getHardwareLineItemList.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 objHardwareLineItemList;
}

---

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

public ArrayList<PerformanceReportDTO> viewPerformanceList(PerformanceReportDTO objDto) {
    //      Nagarjuna
    String methodName = "viewPerformanceList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end//from ww  w  .  jav  a2 s  .c om
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<PerformanceReportDTO> listSearchDetails = new ArrayList<PerformanceReportDTO>();
    PerformanceReportDTO objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetPerformanceReport);

        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.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(4, objDto.getFromOrderNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }

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

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

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

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

        proc.setString(8, pagingSorting.getSortByColumn());// columnName
        proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(10, pagingSorting.getStartRecordId());// start index
        proc.setInt(11, pagingSorting.getEndRecordId());// end index
        proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(13, objDto.getOsp().trim());
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new PerformanceReportDTO();
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

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

            }

            objReportsDto.setProductName(rs.getString("PRODUCT_NAME"));
            objReportsDto.setRegionName(rs.getString("REGIONNAME"));
            objReportsDto.setZoneName(rs.getString("ZONENNAME"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setAccountId(rs.getLong("ACCOUNTID"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setOrder_type(rs.getString("ORDER_TYPE"));
            objReportsDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {

                Date date = df.parse(objReportsDto.getCustPoDate());
                objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase());

            }
            objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE"));
            if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) {

                Date date = df.parse(objReportsDto.getPoRecieveDate());
                objReportsDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase());

            }
            objReportsDto.setPoAmountSum(rs.getLong("POAMOUNT"));
            objReportsDto.setPo_recpt_delay(rs.getString("PO_RECPT_DELAY"));
            objReportsDto.setPo_logging_delay(rs.getString("PO_LOGGING_DELAY"));
            objReportsDto.setOrder_completion_date(rs.getString("ORDER_COMPLETION_DATE"));
            objReportsDto.setAppr_delay_in_region(rs.getString("APPR_DELAY_IN_REGION"));
            objReportsDto.setDays_in_copc(rs.getString("DAYS_IN_COPC"));
            objReportsDto.setDays_for_order(rs.getString("DAYS_FOR_ORDER"));
            objReportsDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objReportsDto.setAmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objReportsDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));

            objReportsDto.setOsp(rs.getString("OSP"));
            objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objReportsDto.setOrderStatus(rs.getString("APPROVAL_STATUS"));

            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setGroupName(rs.getString("GROUPNAME"));
            objReportsDto.setPartyNo(rs.getInt("PARTY_NO"));
            //lawkush start
            objReportsDto.setCopcStartDate(rs.getString("COPC_START_DATE"));
            if (rs.getString("COPC_START_DATE") != null && !"".equals(rs.getString("COPC_START_DATE"))) {
                objReportsDto.setCopcStartDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_START_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcEndDate(rs.getString("COPC_END_DATE"));
            if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) {
                objReportsDto.setCopcEndDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime())))
                                .toUpperCase());
            }
            //lawkush End
            //[007] Start
            objReportsDto.setStandardReason(rs.getString("STANDARDREASON"));
            //[007] End

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

---

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

/**
 * /*from   w w w  . j a  v  a  2  s. c o m*/
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<OrderReportNewDetailCwnDTO> viewOrderReportNew(OrderReportNewDetailCwnDTO objDto)
        throws Exception {

    //   Nagarjuna
    String methodName = "viewOrderReportNew", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   
    ArrayList<OrderReportNewDetailCwnDTO> objUserList = new ArrayList<OrderReportNewDetailCwnDTO>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    OrderReportNewDetailCwnDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlOrderReportNew);

        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("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } 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("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

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

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

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

            objDto = new OrderReportNewDetailCwnDTO();
            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(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .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"));
            if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimarylocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6]
                            + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimarylocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }

                //objDto.setPrimarylocation(rs.getString("FROM_SITE"));
            } else {
                //String ss[] =("A ~~B ~~C ~~D ~~E ~~F ~~G ~~H ~~I").split("~~");
                //objDto.setPrimarylocation(ss[0]+" "+ss[1]+" "+ss[4]+" "+ss[5]+" "+ss[6]+" "+ss[7]+" "+ss[8]+" "+ss[9]+" "+ss[10]+" "+ss[2]);
                objDto.setPrimarylocation("");
            }
            if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " "
                            + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
                //objDto.setSeclocation(rs.getString("TO_SITE"));
            } else {
                objDto.setSeclocation("");
            }
            //objDto.setPrimarylocation(rs.getString("FROM_SITE"));
            //objDto.setSeclocation(rs.getString("TO_SITE"));
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            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(new Date(rs.getTimestamp("CUSTPODATE").getTime())))
                                .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(new Date(rs.getTimestamp("ORDERDATE").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.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(new Date(rs.getTimestamp("NIO_APPROVAL_DATE").getTime())))
                                .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(new Date(rs.getTimestamp("RFS_DATE").getTime())))
                        .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.setFrequencyName(rs.getString("PAYMENTTERM"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            objDto.setServiceType(rs.getString("SERVICETYPE"));
            objDto.setUom(rs.getString("UOM"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setDistance(rs.getString("DISTANCE"));
            if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_CITY").split("~~");
                    objDto.setFrom_city(ss[8]);
                } else {
                    objDto.setFrom_city(" ");
                }
                //objDto.setFrom_city(rs.getString("FROM_CITY"));
            } else {
                objDto.setFrom_city("");
            }
            if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_CITY").split("~~");
                    objDto.setTo_city(ss[8]);
                } else {
                    objDto.setTo_city(" ");
                }
                //objDto.setTo_city(rs.getString("TO_CITY"));
            } else {
                objDto.setTo_city("");
            }
            //objDto.setTo_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.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.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setOsp(rs.getString("OSP"));
            //            [404040] Start 
            objDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            objDto.setChargeRemarks(rs.getString("REMARKS"));

            //shourya start
            objDto.setRE_LOGGED_LSI_NO(rs.getString("RE_LOGGED_LSI_NO"));
            //[003] start
            /*objDto.setPARALLEL_UPGRADE_LSI_NO(rs.getString("PARALLEL_UPGRADE_LSI_NO"));*/
            objDto.setParallellUpgradeLSINo1(rs.getString("PARALLELUPGRADELSINO1"));
            objDto.setParallellUpgradeLSINo2(rs.getString("PARALLELUPGRADELSINO2"));
            objDto.setParallellUpgradeLSINo3(rs.getString("PARALLELUPGRADELSINO3"));
            //[003] end
            objDto.setCHARGEDISCONNECTIONSTATUS(rs.getString("CHARGEDISCONNECTIONSTATUS"));
            objDto.setSubchange_type(rs.getString("NAME_SUBTYPE"));
            objDto.setFxAccountExternalId(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));
            objDto.setOldLsi(rs.getString("OLD_LSI_CRM"));
            //lawkush Start

            objDto.setPartyNo(rs.getInt("PARTY_NO"));
            objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString());
            //lawkush End         
            objDto.setCustSeg_Description(rs.getString("DESCRIPTION"));
            objDto.setObValueLastUpdateDate(rs.getString("OB_VALUE_LAST_UPDATE_DATE"));
            //[130] start
            objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE"));
            objDto.setPartnerId(rs.getString("PARTNER_ID"));
            //[130] End
            //[131] start
            objDto.setPartnerCode(rs.getString("PARTNER_CODE"));
            objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER"));
            //[131] end
            //nancy start
            objDto.setePCNNo(rs.getString("EPCN_NO"));
            //nancy end
            objDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
            if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null
                    && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) {
                objDto.setBillingTriggerCreateDate((utility
                        .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime())))
                                .toUpperCase());
            }
            //RAHEEM start
            objDto.setIsDifferential(rs.getString("Is_Differential"));
            objDto.setLinkedOldCharge(rs.getLong("OLD_PK_CHARGEID"));
            //RAHEEM END
            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;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• »