Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

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

Usage

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

public ArrayList<ArchivalReportDto> reportDraftOrder(ArchivalReportDto reportsDto) {

    String methodName = "reportDraftOrder";
    String className = this.getClass().getName();
    String msg = "";
    boolean logToFile = true, logToConsole = true;

    Connection connection = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;/*from   w  w  w  . j  ava2  s.  c o  m*/
    ArrayList docListDetails = new ArrayList();
    int recordCount = 0;
    ArchivalReportDto dto = null;
    Utility utility = new Utility();
    SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
    String fromDate = reportsDto.getFromdate();
    String toDate = reportsDto.getTodate();

    try {
        PagingSorting pagingSorting = reportsDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        connection = DbConnection.getConnectionObject();
        cstmt = connection.prepareCall(sqlGetDraftReport);

        if (fromDate != null && !"".equals(fromDate)) {
            Date fDate = df.parse(fromDate);
            cstmt.setDate(1, new java.sql.Date(fDate.getTime()));
        } else {
            cstmt.setNull(1, java.sql.Types.DATE);
        }

        if (toDate != null && !"".equals(toDate)) {
            Date tDate = df.parse(toDate);
            cstmt.setDate(2, new java.sql.Date(tDate.getTime()));
        } else {
            cstmt.setNull(2, java.sql.Types.DATE);
        }
        cstmt.setString(3, reportsDto.getAccount_number());
        cstmt.setString(4, reportsDto.getOrder_no());
        cstmt.setString(5, reportsDto.getLogical_si_no());
        cstmt.setString(6, reportsDto.getLine_it_no());
        cstmt.setString(7, reportsDto.getCkt_id());
        cstmt.setString(8, reportsDto.getM6orderno());
        cstmt.setString(9, pagingSorting.getSortByColumn());
        cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));
        cstmt.setInt(11, pagingSorting.getStartRecordId());
        cstmt.setInt(12, pagingSorting.getEndRecordId());
        cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));

        rs = cstmt.executeQuery();
        while (rs.next()) {
            dto = new ArchivalReportDto();
            dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION"));
            dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO"));
            dto.setAccount_id(rs.getString("ACCOUNT_ID"));
            dto.setLine_it_no(rs.getString("LINE_IT_NO"));
            dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER"));
            dto.setAccount_number(rs.getString("ACCOUNT_NUMBER"));
            dto.setPo_amount(rs.getDouble("PO_AMOUNT"));
            dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT"));
            dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY"));
            dto.setVertical(rs.getString("VERTICAL"));
            dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE"));

            if (rs.getString("BILLING_CHARGE_START_DATE") != null
                    && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) {
                dto.setBilling_charge_start_date(
                        (utility.showDate_Report2(dto.getBilling_charge_start_date())).toUpperCase());
            }

            dto.setLine_name(rs.getString("SERVICE_NAME"));
            dto.setOrder_line_no(rs.getString("ORDER_LINE_NO"));
            dto.setCharge_name(rs.getString("LINE_NAME"));
            dto.setCancel_flag(rs.getString("CANCEL_FLAG"));
            dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH"));
            dto.setUom(rs.getString("UOM"));
            dto.setBill_uom(rs.getString("BILL_UOM"));
            dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER"));
            dto.setContract_period(rs.getString("CONTRACT_PERIOD"));
            dto.setCompany_name(rs.getString("COMPANY_NAME"));
            dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE"));

            if (rs.getString("ORDER_CREATION_DATE") != null
                    && !"".equals(rs.getString("ORDER_CREATION_DATE"))) {

                dto.setOrder_creation_date(
                        (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase());
            }

            dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE"));

            if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null
                    && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) {
                dto.setCustomer_service_rfs_date(
                        (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase());
            }

            dto.setCurrency(rs.getString("CURRENCY"));
            dto.setCharge_name(rs.getString("CHARGE_NAME"));
            dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE"));

            if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) {

                dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase());
            }

            dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER"));
            dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC"));
            dto.setChallen_no(rs.getString("CHALLEN_NO"));
            dto.setOrder_no(rs.getString("ORDER_NO"));
            dto.setFrom_site(rs.getString("FROM_SITE"));
            dto.setTo_site(rs.getString("TO_SITE"));
            dto.setItem_quantity(rs.getString("ITEM_QUANTITY"));
            dto.setKms_distance(rs.getString("KMS_DISTANCE"));
            dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT"));
            dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE"));

            if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                dto.setCopc_approved_date(
                        utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())));
            }

            dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION"));
            dto.setLoc_date(rs.getString("LOC_Date"));

            if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) {
                dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase());
            }

            dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"));
            if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null
                    && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) {
                dto.setAccount_manager_receive_date(
                        (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase());
            }

            dto.setOrder_total(rs.getDouble("ORDER_TOTAL"));
            dto.setTaxation(rs.getString("TAXATION"));
            dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON"));
            dto.setLicence_company(rs.getString("LICENCE_COMPANY"));
            dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID"));
            dto.setOrder_type(rs.getString("ORDER_TYPE"));
            dto.setPayment_term(rs.getString("PAYMENT_TERM"));
            dto.setProject_mgr(rs.getString("PROJECT_MGR"));
            dto.setRegion_name(rs.getString("REGION_NAME"));
            dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT"));
            dto.setDemo_type(rs.getString("DEMO_TYPE"));
            dto.setParty_name(rs.getString("PARTY_NAME"));
            dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION"));
            dto.setCharge_end_date(rs.getString("CHARGE_END_DATE"));

            if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) {
                dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase());
            }

            dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC"));
            dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK"));
            dto.setRemarks(rs.getString("REMARKS"));
            dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE"));
            dto.setOsp(rs.getString("OSP"));
            dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID"));
            dto.setStore_address(rs.getString("STORE_ADDRESS"));
            dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE"));
            if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) {
                dto.setCustomer_rfs_date((utility.showDate_Report2(dto.getCustomer_rfs_date())).toUpperCase());
            }

            dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE"));
            if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) {
                dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase());
            }

            dto.setCkt_id(rs.getString("CKT_ID"));
            dto.getInterfaceid();

            if (pagingSorting.isPagingToBeDone() && recordCount == 0) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            docListDetails.add(dto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception e) {

        Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(cstmt);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            System.out.println(">>>>>>>>>>>.");
        }
    }

    System.out.println("DTO_>>>>>>>>>>>>>>>end>>>>>>>>>>>>>");
    return docListDetails;

}

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

public ArrayList<ArchivalReportDto> cancelledReportOrder(ArchivalReportDto reportsDto) {

    String methodName = "cancelledReportOrder";
    String className = this.getClass().getName();
    String msg = "";
    boolean logToFile = true, logToConsole = true;

    Connection connection = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;//  w w  w  .  ja va  2  s .com
    ArrayList docListDetails = new ArrayList();
    int recordCount = 0;
    ArchivalReportDto dto = null;
    Utility utility = new Utility();
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    String fromDate = reportsDto.getFromdate();
    String toDate = reportsDto.getTodate();

    try {

        PagingSorting pagingSorting = reportsDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        connection = DbConnection.getConnectionObject();
        cstmt = connection.prepareCall(sqlGetCancelledtReport);

        if (fromDate != null && !"".equals(fromDate)) {
            Date fDate = df.parse(fromDate);
            cstmt.setDate(1, new java.sql.Date(fDate.getTime()));
        } else {
            cstmt.setNull(1, java.sql.Types.DATE);
        }
        if (toDate != null && !"".equals(toDate)) {
            Date tDate = df.parse(toDate);
            cstmt.setDate(2, new java.sql.Date(tDate.getTime()));
        } else {
            cstmt.setNull(2, java.sql.Types.DATE);
        }

        cstmt.setString(3, reportsDto.getAccount_id());
        cstmt.setString(4, reportsDto.getOrder_no());
        cstmt.setString(5, reportsDto.getLogical_si_no());
        cstmt.setString(6, reportsDto.getLine_it_no());
        cstmt.setString(7, reportsDto.getCkt_id());
        cstmt.setString(8, reportsDto.getM6orderno());
        cstmt.setString(9, pagingSorting.getSortByColumn());
        cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));
        cstmt.setInt(11, pagingSorting.getStartRecordId());
        cstmt.setInt(12, pagingSorting.getEndRecordId());
        cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));

        rs = cstmt.executeQuery();
        while (rs.next()) {
            dto = new ArchivalReportDto();

            dto.setAccount_id(rs.getString("ACCOUNT_ID"));
            dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER"));
            dto.setAccount_number(rs.getString("ACCOUNT_NUMBER"));
            dto.setPo_amount(rs.getDouble("PO_AMOUNT"));
            dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT"));
            dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY"));
            dto.setVertical(rs.getString("VERTICAL"));
            dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE"));
            if (rs.getString("BILLING_CHARGE_START_DATE") != null
                    && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) {
                dto.setBilling_charge_start_date(
                        (utility.showDate_Report(dto.getBilling_charge_start_date())).toUpperCase());
            }

            dto.setService_name(rs.getString("SERVICE_NAME"));
            dto.setOrder_line_no(rs.getString("ORDER_LINE_NO"));
            dto.setLine_name(rs.getString("LINE_NAME"));
            dto.setCancel_flag(rs.getString("CANCEL_FLAG"));
            dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH"));
            dto.setUom(rs.getString("UOM"));
            dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            dto.setStore_address(rs.getString("STORE_ADDRESS"));
            dto.setBill_uom(rs.getString("BILL_UOM"));
            dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER"));
            dto.setContract_period(rs.getString("CONTRACT_PERIOD"));
            dto.setCompany_name(rs.getString("COMPANY_NAME"));
            dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE"));

            if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) {
                dto.setCustomer_rfs_date((utility.showDate_Report3(dto.getCustomer_rfs_date())).toUpperCase());// issuee gui
            }

            dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE"));

            if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null
                    && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) {
                dto.setCustomer_service_rfs_date(
                        (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase()); // issue GUI
            }

            dto.setCurrency(rs.getString("CURRENCY"));
            dto.setCharge_name(rs.getString("CHARGE_NAME"));
            dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE"));

            if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) {

                dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase());
            }

            dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER"));
            dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC"));
            dto.setChallen_no(rs.getString("CHALLEN_NO"));
            dto.setOrder_no(rs.getString("ORDER_NO"));
            dto.setFrom_site(rs.getString("FROM_SITE"));
            dto.setTo_site(rs.getString("TO_SITE"));
            dto.setItem_quantity(rs.getString("ITEM_QUANTITY"));
            dto.setKms_distance(rs.getString("KMS_DISTANCE"));
            dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT"));
            dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE"));

            if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                dto.setCopc_approved_date(
                        utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())));
            }

            dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION"));
            dto.setLoc_date(rs.getString("LOC_DATE"));

            if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) {
                dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase());
            }

            dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"));

            if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null
                    && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) {
                dto.setAccount_manager_receive_date(
                        (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase());
            }

            dto.setOrder_total(rs.getDouble("ORDER_TOTAL"));
            dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE"));

            if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) {
                dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase());
            }

            dto.setTaxation(rs.getString("TAXATION"));
            dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON"));
            dto.setLicence_company(rs.getString("LICENCE_COMPANY"));
            dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID"));
            dto.setOrder_type(rs.getString("ORDER_TYPE"));
            dto.setPayment_term(rs.getString("PAYMENT_TERM"));
            dto.setProject_mgr(rs.getString("PROJECT_MGR"));
            dto.setRegion_name(rs.getString("REGION_NAME"));
            dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT"));
            dto.setDemo_type(rs.getString("DEMO_TYPE"));
            dto.setParty_name(rs.getString("PARTY_NAME"));
            dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION"));
            dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION"));
            dto.setCharge_end_date(rs.getString("CHARGE_END_DATE"));

            if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) {
                dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase());
            }

            dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC"));
            dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK"));
            dto.setRemarks(rs.getString("REMARKS"));
            dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE"));
            dto.setOsp(rs.getString("OSP"));
            dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID"));
            dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO"));
            dto.setLine_it_no(rs.getString("LINE_IT_NO"));
            dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE"));
            if (rs.getString("ORDER_CREATION_DATE") != null
                    && !"".equals(rs.getString("ORDER_CREATION_DATE"))) {
                dto.setOrder_creation_date(
                        (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase());
            }
            dto.setCkt_id(rs.getString("CKT_ID"));
            dto.setM6orderno(rs.getString("M6_ORDER_NO"));
            dto.setService_id(rs.getString("SERVICEID"));
            dto.setOldLsi(rs.getString("OLD_LSI"));
            dto.setCancel_reason(rs.getString("CANCEL_REASON"));
            dto.setCancel_date(rs.getString("CANCEL_DATE"));

            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {
                dto.setCancel_date((utility.showDate_Report2(dto.getCancel_date())).toUpperCase());
            }

            dto.setProduct(rs.getString("PRODUCT"));
            dto.setSub_product(rs.getString("SUB_PRODUCT"));
            dto.setEffective_start_data(rs.getString("EFFECTIVE_START_DATA"));

            if (rs.getString("EFFECTIVE_START_DATA") != null
                    && !"".equals(rs.getString("EFFECTIVE_START_DATA"))) {
                dto.setEffective_start_data(
                        (utility.showDate_Report(dto.getEffective_start_data())).toUpperCase());
            }
            dto.getInterfaceid();
            docListDetails.add(dto);
            if (pagingSorting.isPagingToBeDone() && recordCount == 0) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            docListDetails.add(dto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception e) {

        Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(cstmt);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
        }
    }

    return docListDetails;

}

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

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

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

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

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(3, pagingSorting.getSortByColumn());// columnName
        proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(5, pagingSorting.getStartRecordId());// start index
        proc.setInt(6, pagingSorting.getEndRecordId());// end index
        proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) {
            proc.setString(8, objDto.getOrderyear().trim());
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new MigratedApprovedNewOrderDetailReportDTO();
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setM6OrderNo2(rs.getString("M6ORDERNO"));
            objReportsDto.setServiceName(rs.getString("SERVICESTAGE"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETTYPE"));//LineName
            objReportsDto.setServiceOrderType(rs.getString("SERVICETYPE"));
            objReportsDto.setLogicalCircuitId(rs.getString("CKTID"));
            objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS"));
            objReportsDto.setToLocation(rs.getString("TO_ADDRESS"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setCurrencyName(rs.getString("CURNAME"));
            objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
            objReportsDto.setStorename(rs.getString("STORENAME"));
            objReportsDto.setSaleType(rs.getString("SALETYPENAME"));//Type Of Sale
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setPonum(rs.getLong("PODETAILNUMBER"));
            objReportsDto.setPoDate(rs.getString("PODATE"));
            if (!(rs.getString("PODATE") == null || rs.getString("PODATE") == "")) {

                //Date date=df.parse(objReportsDto.getPoDate());
                objReportsDto.setPoDate((utility.showDate_Report(rs.getString("PODATE"))).toUpperCase());

            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month
            objReportsDto.setTotalPoAmt(rs.getString("POAMOUNT"));
            objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE"));
            if (!(rs.getString("PORECEIVEDATE") == null || rs.getString("PORECEIVEDATE") == "")) {

                //Date date=df.parse(objReportsDto.getPoRecieveDate());
                objReportsDto.setPoRecieveDate(
                        (utility.showDate_Report(rs.getString("PORECEIVEDATE"))).toUpperCase());

            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objReportsDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (!(rs.getString("CUSTPODATE") == null || rs.getString("CUSTPODATE") == "")) {

                //Date date=df.parse(objReportsDto.getCustPoDate());
                objReportsDto
                        .setCustPoDate((utility.showDate_Report(rs.getString("CUSTPODATE"))).toUpperCase());

            }
            objReportsDto.setLOC_No(rs.getString("LOCNO"));
            objReportsDto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {
                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
                Date dateStr = formatter.parse(rs.getString("LOCDATE"));
                String formattedDate = formatter.format(dateStr);
                Date date1 = formatter.parse(formattedDate);
                formatter = new SimpleDateFormat("dd-MMM-yy");
                formattedDate = formatter.format(date1);
                objReportsDto.setLocDate(formattedDate);
            }
            objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {
                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
                Date dateStr = formatter.parse(rs.getString("BILLINGTRIGGERDATE"));
                String formattedDate = formatter.format(dateStr);
                Date date1 = formatter.parse(formattedDate);
                formatter = new SimpleDateFormat("dd-MMM-yy");
                formattedDate = formatter.format(date1);
                objReportsDto.setBillingTriggerDate(formattedDate);
            }
            objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
            if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null
                    || rs.getString("BILLINGTRIGGERDATE") == "")) {

                //Date date=df2.parse(objReportsDto.getBillingtrigger_createdate());
                objReportsDto.setBillingtrigger_createdate(
                        (utility.showDate_Report(rs.getString("BILLING_TRIGGER_CREATEDATE"))).toUpperCase());

            }
            objReportsDto.setPmApproveDate(rs.getString("LOC_DATE"));
            if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) {
                String s1 = rs.getString("LOC_DATE");
                String s3 = s1.substring(0, 7);
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objReportsDto.setPmApproveDate(s5);
            }
            objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_STATUS"));
            objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setKmsDistance(rs.getString("DISTANCE"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setTokenNO(rs.getString("START_DETAILS_FX_TOKEN_NO"));//Token_No
            objReportsDto.setFx_St_Chg_Status(rs.getString("FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status
            objReportsDto.setFxStatus(rs.getString("START_DETAILS_FX_STATUS"));//FX_STATUS
            objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
            objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setChargeAmount_String(rs.getString("INV_AMT"));
            objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt
            objReportsDto.setStartDate(rs.getString("START_DATE"));

            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {
                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
                Date dateStr = formatter.parse(rs.getString("START_DATE"));
                String formattedDate = formatter.format(dateStr);
                Date date1 = formatter.parse(formattedDate);
                formatter = new SimpleDateFormat("dd-MMM-yy");
                formattedDate = formatter.format(date1);
                objReportsDto.setStartDate(formattedDate);
            } else {
                objReportsDto.setStartDate("");
            }
            objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION"));
            objReportsDto.setOrderLineNumber(rs.getInt("LINENUMBER"));//Lineitemnumber   
            objReportsDto.setOrdermonth(rs.getString("ORDERMONTH"));
            /*objReportsDto.setBlSource(rs.getString("BL_SOURCE"));
            objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO"));
            objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));*/
            objReportsDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO"));
            objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));
            objReportsDto.setOpms_lineItemNumber(rs.getString("OPMS_LINEITEMNUMBER"));
            objReportsDto.setRatio(rs.getString("RATIO"));
            objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG"));
            if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("PRIMARYLOCATION").split("~~");
                    objReportsDto.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("PRIMARYLOCATION").split("~~");
                    objReportsDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objReportsDto.setPrimaryLocation("");
            }
            if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("SECONDARYLOCATION").split("~~");
                    objReportsDto.setSecondaryLocation(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("SECONDARYLOCATION").split("~~");
                    objReportsDto.setSecondaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objReportsDto.setSecondaryLocation("");
            }

            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

public ArrayList<ArchivalReportDto> pdReportOrder(ArchivalReportDto reportsDto) {

    String methodName = "pdReportOrder";
    String className = this.getClass().getName();
    String msg = "";
    boolean logToFile = true, logToConsole = true;

    Connection connection = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;//from  w w  w  .j  a v  a2  s . com
    ArrayList docListDetails = new ArrayList();
    int recordCount = 0;
    ArchivalReportBean dto = null;
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    String fromDate = reportsDto.getFromdate();
    String toDate = reportsDto.getTodate();
    Utility utility = new Utility();

    try {

        PagingSorting pagingSorting = reportsDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        connection = DbConnection.getConnectionObject();
        cstmt = connection.prepareCall(sqlGetPDReport);

        if (fromDate != null && !"".equals(fromDate)) {
            Date fDate = df.parse(fromDate);
            cstmt.setDate(1, new java.sql.Date(fDate.getTime()));
        } else {
            cstmt.setNull(1, java.sql.Types.DATE);
        }
        if (toDate != null && !"".equals(toDate)) {
            Date tDate = df.parse(toDate);
            cstmt.setDate(2, new java.sql.Date(tDate.getTime()));
        } else {
            cstmt.setNull(2, java.sql.Types.DATE);
        }

        cstmt.setString(3, reportsDto.getAccount_id());
        cstmt.setString(4, reportsDto.getOrder_no());
        cstmt.setString(5, reportsDto.getLogical_si_no());
        //System.out.println("reportsDto.getLogical_si_no()>>>>"+reportsDto.getLogical_si_no());
        cstmt.setString(6, reportsDto.getM6orderno());
        cstmt.setString(7, reportsDto.getCircuit_id());
        cstmt.setString(8, reportsDto.getOrder_line_no());
        cstmt.setString(9, pagingSorting.getSortByColumn());
        cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));
        cstmt.setInt(11, pagingSorting.getStartRecordId());
        cstmt.setInt(12, pagingSorting.getEndRecordId());
        cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));

        rs = cstmt.executeQuery();
        while (rs.next()) {
            dto = new ArchivalReportBean();
            dto.setAccount_mgr(rs.getString("ACCOUNT_MGR"));
            dto.setAccount_no(rs.getString("ACCOUNT_NO"));
            dto.setAmt(rs.getString("AMT"));
            dto.setAnnotation(rs.getString("ANNOTATION"));
            dto.setAnnual_rate(rs.getString("ANNUAL_RATE"));
            dto.setBandwidth(rs.getString("BANDWIDTH"));
            dto.setBandwidth_uom(rs.getString("BANDWIDTH_UOM"));
            dto.setBill_format(rs.getString("BILL_FORMAT"));
            dto.setBill_period(rs.getString("BILL_PERIOD"));
            dto.setBill_trg_Create_date(rs.getString("BILL_TRG_CREATE_DATE"));
            dto.setBill_type(rs.getString("BILL_TYPE"));
            dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            dto.setBilling_bandwidth_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            dto.setBilling_level(rs.getString("BILLING_LEVEL"));
            dto.setBilling_level_number(rs.getString("BILLING_LEVEL_NUMBER"));
            dto.setBilling_location_from(rs.getString("BILLING_LOCATION_FROM"));
            dto.setBilling_location_to(rs.getString("BILLING_LOCATION_TO"));
            dto.setBilling_mode(rs.getString("BILLING_MODE"));
            dto.setBilling_trig_flag(rs.getString("BILLING_TRIG_FLAG"));
            dto.setChallen_date(rs.getString("CHALLEN_DATE"));
            dto.setCharge_end_date(rs.getString("CHARGE_END_DATE"));
            dto.setCharge_hdr_id(rs.getString("CHARGE_HDR_ID"));
            dto.setCharge_name(rs.getString("CHARGE_NAME"));
            dto.setCharge_start_date(rs.getString("CHARGE_START_DATE"));
            dto.setCharge_status(rs.getString("CHARGE_STATUS"));
            dto.setCharge_type(rs.getString("CHARGE_TYPE"));
            dto.setCharge_type_id(rs.getString("CHARGE_TYPE_ID"));
            dto.setChargeable_distance(rs.getString("CHARGEABLE_DISTANCE"));
            dto.setChild_acc_fx_status(rs.getString("CHILD_ACC_FX_STATUS"));
            dto.setChild_acc_no(rs.getString("CHILD_ACC_NO"));
            dto.setCircuit_id(rs.getString("CIRCUIT_ID"));
            dto.setCommitment_period(rs.getString("COMMITMENT_PERIOD"));
            dto.setContract_period_months(rs.getString("CONTRACT_PERIOD_MONTHS"));
            dto.setCopc_approval_date(rs.getString("COPC_APPROVAL_DATE"));
            dto.setCredit_period(rs.getString("CREDIT_PERIOD"));
            dto.setCurrency(rs.getString("CURRENCY"));
            dto.setCust_acc_id(rs.getString("CUST_ACC_ID"));
            dto.setCust_logical_si_no(rs.getString("CUST_LOGICAL_SI_NO"));
            dto.setCust_po_date(rs.getString("CUST_PO_DATE"));
            dto.setCust_po_number(rs.getString("CUST_PO_NUMBER"));
            dto.setCust_po_receive_date(rs.getString("CUST_PO_RECEIVE_DATE"));
            dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT"));
            dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE"));
            dto.setDemo_type(rs.getString("DEMO_TYPE"));
            dto.setDisconnection_remark(rs.getString("DISCONNECTION_REMARK"));
            dto.setEnd_date_days(rs.getString("END_DATE_DAYS"));
            dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC"));
            dto.setEnd_date_months(rs.getString("END_DATE_MONTHS"));
            dto.setForm_c_available(rs.getString("FORM_C_AVAILABLE"));
            dto.setFrequency(rs.getString("FREQUENCY"));
            dto.setHardware_flag(rs.getString("HARDWARE_FLAG"));
            dto.setHardware_type(rs.getString("HARDWARE_TYPE"));
            dto.setIndicative_value(rs.getString("INDICATIVE_VALUE"));
            dto.setInv_amt(rs.getString("INV_AMT"));
            dto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA"));
            dto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER"));
            dto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS"));
            dto.setLegal_entity(rs.getString("LEGAL_ENTITY"));
            dto.setLicence_company(rs.getString("LICENCE_COMPANY"));
            dto.setLoc_date(rs.getString("LOC_DATE"));
            dto.setLoc_number(rs.getString("LOC_NUMBER"));
            dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID"));
            dto.setM6_order_id(rs.getString("M6_ORDER_ID"));
            dto.setNature_of_sale(rs.getString("NATURE_OF_SALE"));
            dto.setNew_order_remarks(rs.getString("NEW_ORDER_REMARKS"));
            dto.setNotice_period(rs.getString("NOTICE_PERIOD"));
            dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE"));
            if (rs.getString("ORDER_CREATION_DATE") != null
                    && !"".equals(rs.getString("ORDER_CREATION_DATE"))) {
                dto.setOrder_creation_date(
                        (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase());
            }
            dto.setOrder_date(rs.getString("ORDER_DATE"));
            dto.setOrder_line_id(rs.getString("ORDER_LINE_ID"));
            dto.setOrder_month(rs.getString("ORDER_MONTH"));
            dto.setOrder_number(rs.getString("ORDER_NUMBER"));
            dto.setOrder_stage(rs.getString("ORDER_STAGE"));
            dto.setOrder_type(rs.getString("ORDER_TYPE"));
            dto.setParty(rs.getString("PARTY"));
            dto.setParty_id(rs.getString("PARTY_ID"));
            dto.setPenalty_clause(rs.getString("PENALTY_CLAUSE"));
            dto.setPeriod_in_month(rs.getString("PERIOD_IN_MONTH"));
            dto.setPk_chageges_id(rs.getString("PK_CHAGEGES_ID"));
            dto.setPm_prov_date(rs.getString("PM_PROV_DATE"));
            dto.setPo_date(rs.getString("PO_DATE"));
            dto.setPre_crm_order_id(rs.getString("PRE_CRM_ORDER_ID"));
            dto.setProduct(rs.getString("PRODUCT"));
            dto.setProduct_name(rs.getString("PRODUCT_NAME"));
            dto.setRate_code(rs.getString("RATE_CODE"));
            dto.setRegion(rs.getString("REGION"));
            dto.setRequest_received_date(rs.getString("REQUEST_RECEIVED_DATE"));
            dto.setSec_loc(rs.getString("SEC_LOC"));
            dto.setService_no(rs.getString("SERVICE_NO"));
            dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE"));
            dto.setService_stage(rs.getString("SERVICE_STAGE"));
            dto.setSr_number(rs.getString("SR_NUMBER"));
            dto.setStandard_reason(rs.getString("STANDARD_REASON"));
            dto.setStart_date_days(rs.getString("START_DATE_DAYS"));
            dto.setStart_date_logic(rs.getString("START_DATE_LOGIC"));
            dto.setStart_date_months(rs.getString("START_DATE_MONTHS"));
            dto.setStore(rs.getString("STORE"));
            dto.setSub_product(rs.getString("SUB_PRODUCT"));
            dto.setTaxation(rs.getString("TAXATION"));
            dto.setToken_no(rs.getString("TOKEN_NO"));
            dto.setTot_po_amt(rs.getString("TOT_PO_AMT"));
            dto.setTotal_amount(rs.getString("TOTAL_AMOUNT"));
            dto.setType_of_sale(rs.getString("TYPE_OF_SALE"));
            dto.setVertical(rs.getString("VERTICAL"));
            dto.setProject_mgr(rs.getString("PROJECT_MGR"));
            dto.setProject_mgr_email(rs.getString("PROJECT_MGR_EMAIL"));
            dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH"));
            dto.setQuote_no(rs.getString("QUOTE_NO"));
            dto.setRatio(rs.getString("RATIO"));
            dto.setRegion_name(rs.getString("REGION_NAME"));
            dto.setRe_logged_lsi_no(rs.getString("RE_LOGGED_LSI_NO"));
            dto.setService_name(rs.getString("SERVICE_NAME"));
            dto.setService_number(rs.getString("SERVICE_NUMBER"));
            dto.setSub_product_type(rs.getString("SUB_PRODUCT_TYPE"));
            dto.setTo_city(rs.getString("TO_CITY"));
            dto.setTo_site(rs.getString("TO_SITE"));
            dto.setUom(rs.getString("UOM"));
            dto.setZone(rs.getString("ZONE"));
            dto.setDis_sr(rs.getString("DIS_SR"));
            dto.setDod(rs.getString("DOD"));
            dto.setOrder_no(rs.getString("ORDER_NO"));
            dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO"));
            dto.setLine_it_no(rs.getString("LINE_IT_NO"));
            dto.setAccount_id(rs.getString("ACCOUNT_ID"));
            dto.setCkt_id(rs.getString("CKT_ID"));
            dto.setPackage_id(rs.getString("PACKAGE_ID"));
            dto.setPackage_name(rs.getString("PACKAGE_NAME"));
            dto.setComponentinfoid(rs.getString("COMPONENTINFOID"));
            dto.setComponent_id(rs.getString("COMPONENT_ID"));
            dto.setComponent_name(rs.getString("COMPONENT_NAME"));
            dto.setComponent_status(rs.getString("COMPONENT_STATUS"));
            dto.setComponent_start_logic(rs.getString("COMPONENT_START_LOGIC"));
            dto.setComponent_start_date(rs.getString("COMPONENT_START_DATE"));
            dto.setComponent_end_logic(rs.getString("COMPONENT_END_LOGIC"));
            dto.setComponent_end_date(rs.getString("COMPONENT_END_DATE"));
            dto.setComp_start_days(rs.getString("COMP_START_DAYS"));
            dto.setComp_start_days(rs.getString("COMP_START_MONTHS"));
            dto.setComp_start_months(rs.getString("COMP_END_MONTHS"));
            dto.setComp_end_days(rs.getString("COMP_END_DAYS"));
            dto.setComp_end_months(rs.getString("COMP_END_MONTHS"));
            dto.setComponent_type(rs.getString("COMPONENT_TYPE"));
            dto.setComponent_instance_id(rs.getString("COMPONENT_INSTANCE_ID"));
            dto.setStart_component_token_no(rs.getString("START_COMPONENT_TOKEN_NO"));
            dto.setEnd_component_token_no(rs.getString("END_COMPONENT_TOKEN_NO"));
            dto.setHardware_type(rs.getString("HARDWARE_TYPE"));
            dto.setLink_type(rs.getString("LINK_TYPE"));
            dto.setTaxexcemption_reason(rs.getString("TAXEXEMPTION_REASON"));
            dto.setRate_code(rs.getString("RATE_CODE"));
            dto.setPri_loc(rs.getString("PRI_LOC"));
            dto.setSub_change_type(rs.getString("SUB_CHANGE_TYPE"));
            dto.setCHALLEN_NO(rs.getString("CHALLEN_NO"));
            dto.setLINE_NAME(rs.getString("LINE_NAME"));
            dto.setBilling_trig_date(rs.getString("BILLING_TRIG_DATE"));
            dto.setDispatch_address(rs.getString("DISPATCH_ADDRESS"));
            dto.setPo_number(rs.getString("PO_NUMBER"));
            dto.getInterfaceId();
            if (pagingSorting.isPagingToBeDone() && recordCount == 0) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            docListDetails.add(dto);

        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception e) {

        Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(cstmt);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
        }
    }

    return docListDetails;

}

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

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

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

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

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

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

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

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

        rs = proc.executeQuery();

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

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

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

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

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

        conn = DbConnection.getReportsConnectionObject();

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

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

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

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

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

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

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

            }
            objDto.setSourceName(rs.getString("SOURCE"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setCurrencyName(rs.getString("CURNAME"));
            // objDto.setStatus(new
            // Integer(rs.getString("STATUS")).intValue());
            objDto.setStageName(rs.getString("STAGE"));
            //[131] START
            objDto.setPartnerCode(rs.getString("PARTNER_CODE"));
            objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            //[131] END

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

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

    return objUserList;
}

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

public ArrayList<CopyCancelReportDTO> cancelCopyReport(CopyCancelReportDTO objDto) throws Exception {

    //   Nagarjuna
    String methodName = "cancelCopyReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end/*from  ww w. ja v  a2s .  c  o m*/
    ArrayList<CopyCancelReportDTO> objUserList = new ArrayList<CopyCancelReportDTO>();
    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(sqlCancelCopyReport);

        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(objDto.getFromDate());
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(1, formattedDate);
            //proc.setString(1, objDto.getFromDate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getToDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(objDto.getToDate());
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
            //proc.setString(2, objDto.getToDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

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

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

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

            objDto = new CopyCancelReportDTO();

            objDto.setOldOrderNo(rs.getInt("OLDORDERNO"));
            objDto.setNewOrderNo(rs.getInt("NEWORDERNO"));
            objDto.setRootOrderNo(rs.getInt("ROOTORDERNO"));
            objDto.setOldServiceNo(rs.getInt("OLDSERVICENO"));
            objDto.setNewServiceNo(rs.getInt("NEWSERVICENO"));
            objDto.setCreatedBy(rs.getString("CREATEDBY"));
            objDto.setCreatedDate(rs.getString("CREATEDDATE"));
            if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) {
                //Date date=df.parse(objDto.getCreatedDate());
                //objDto.setCreatedDate((utility.showDate_Report(date)).toUpperCase());
                objDto.setCreatedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime())))
                                .toUpperCase());
            }

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

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

    return objUserList;
}

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

/**
 * Create a Report to generate Pending Billing Permanent Disconnection Report
         /*from w  w  w .jav a 2 s .c o m*/
 * @param obj   a DTO which consist all the search parameters
 * @return      a ArrayList of dto which consist all the data of reports 
 * @exception   Sql Exception
 *            
 */

public ArrayList<ReportsDto> viewPendingBillingPDOrderList(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewPendingBillingPDOrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    ArrayList<ReportsDto> objOrderList = new ArrayList<ReportsDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getOrder = null;

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

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

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

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

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

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

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

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

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

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

            }

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

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

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

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

public ArrayList<RateRenewalReportDTO> viewRateRenewalReport(RateRenewalReportDTO objDto) throws Exception {

    //   Nagarjuna
    String methodName = "viewRateRenewalReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<RateRenewalReportDTO> objUserList = new ArrayList<RateRenewalReportDTO>();
    Connection conn = null;/* w w w . j av  a2s  . c  o  m*/
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    RateRenewalReportDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;
    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlRateRenewalReportforUsage);

        /*   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(1, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(1, 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(2, formattedDate1);
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        /*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(3, objDto.getFromAccountNo());
            proc.setLong(4, objDto.getToAccountNo());
        } else {
            proc.setNull(3, java.sql.Types.BIGINT);
            proc.setNull(4, java.sql.Types.BIGINT);
        }

        if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) {
            proc.setLong(5, objDto.getFromOrderNo());
            proc.setLong(6, objDto.getToOrderNo());
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
            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
        // index
        proc.setInt(12, (objDto.getIsUsage()));// end
        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(13, formattedDate);
        } else {
            proc.setNull(13, 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(14, formattedDate1);
        } else {
            proc.setNull(14, java.sql.Types.VARCHAR);
        }

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

            objRDto = new RateRenewalReportDTO();
            objRDto.setPartyNo(rs.getInt("PARTY_NO"));
            objRDto.setPartyName(rs.getString("PARTYNAME"));
            objRDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objRDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objRDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objRDto.setServiceSegment(rs.getString("SERVICESEGMENT"));//Added in View
            objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objRDto.setRegionName(rs.getString("REGION"));
            //objRDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));PARENT_NAME
            objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            //objRDto.setChangeTypeName(rs.getString("NAME_SUBTYPE"));SERVICE_ORDER_TYPE_DESC
            objRDto.setOrderType(rs.getString("ORDERTYPE"));
            //objRDto.setCompanyName(rs.getString("COMPANYNAME"));ENTITYNAME

            //objRDto.setCurrencyCode(rs.getString("CURNAME"));CURRENCYNAME
            /*objRDto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE")))
            {                        
               Date date=df.parse(objRDto.getPoDate());
               objRDto.setPoDate((utility.showDate_Report(date)).toUpperCase());
               //objDto.setPoDate((utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase());                     
               objRDto.setPoDate(utility.showDate_Report(df.parse(rs.getString("PODATE"))).toUpperCase());
            }*/

            tempDate = rs.getDate("PODATE");
            objRDto.setPoDate(rs.getString("PODATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objRDto.setPoDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));

            //objRDto.setFromLocation(rs.getString("PRIMARYLOCATION"));
            //objRDto.setToLocation(rs.getString("SECONDARYLOCATION"));
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            objRDto.setFromLocation(VAR_PRIMARYLOCATION);
            //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            objRDto.setToLocation(VAR_SECONDARYLOCATION);
            objRDto.setDistance(rs.getString("DISTANCE"));
            objRDto.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION"));//Changed Column Name :AKS
            objRDto.setLOC_Date(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

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

            objRDto.setLogicalCircuitNumber(rs.getString("CKTID"));

            objRDto.setTaxationName(rs.getString("TAXATIONVALUE"));
            objRDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objRDto.setOrder_type(rs.getString("DEMO_TYPE"));
            objRDto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objRDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS"));
            objRDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {
                Date date = df.parse(objRDto.getBilling_trigger_date());
                objRDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase());
            }
            //objRDto.setBillingTriggerFlag(rs.getString("BILLINGTRIGGERFLAG"));Billing_Trigger_Flag
            //objRDto.setZoneName(rs.getString("ZONE"));ZONENNAME
            objRDto.setSalesCoordinator(rs.getString("SALESCOORDINATOR"));//Column Added in View :AKS
            //objRDto.setPoAmounts(rs.getDouble("POAMOUNT"));ORDERTOTAL
            objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objRDto.setItemQuantity(1);//Need to Ask Ravneet : AKS
            //objRDto.setServiceId(rs.getInt("SERVICEID"));SERVICE_NO 
            objRDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objRDto.setCustSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO"));
            objRDto.setM6cktid(rs.getString("CKTID"));
            //objRDto.setServiceProductID(rs.getInt("SERVICEPRODUCTID"));Order_Line_Id
            objRDto.setOrderNo(rs.getString("ORDERNO"));
            if (objDto.getIsUsage() == 0) {
                objRDto.setCreatedDate(rs.getString("CREATEDDATE"));
                if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) {

                    objRDto.setCreatedDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setChargeName(rs.getString("CHARGE_NAME"));
                objRDto.setChargeTypeName(rs.getString("CHARGENAME"));
                objRDto.setPaymentTerm(rs.getString("PAYMENTTERM"));
                objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
                objRDto.setStartDate(rs.getString("START_DATE"));
                if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {

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

                }
                objRDto.setEndDate(rs.getString("END_DATE"));
                if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) {

                    Date date = df.parse(objRDto.getEndDate());
                    objRDto.setEndDate((utility.showDate_Report(date)).toUpperCase());

                }
                objRDto.setEndHWDateLogic(rs.getString("ENDDATELOGIC"));
                objRDto.setCharge_status(rs.getString("CHARGES_STATUS"));
                objRDto.setStartDaysLogic(rs.getString("CHARGESTARTDAYSLOGIC"));
                objRDto.setCurrencyCode(rs.getString("CURNAME"));
                objRDto.setStartMonthsLogic(rs.getString("CHARGESTARTMONTHSLOGIC"));
                objRDto.setOrderTotal(rs.getDouble("POAMOUNT"));
                objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
                objRDto.setOldLineitemAmount(rs.getString("CHARGEAMOUNT"));
                objRDto.setChargePeriod(rs.getInt("CHARGEPERIOD"));
                objRDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE"));
                objRDto.setTxtStartDays(rs.getInt("START_DATE_DAYS"));
                objRDto.setTxtStartMonth(rs.getInt("START_DATE_MONTH"));
                objRDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));
                objRDto.setChangeTypeName(rs.getString("NAME_SUBTYPE"));
                objRDto.setCompanyName(rs.getString("COMPANYNAME"));
                objRDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
                if (rs.getString("COPC_APPROVAL_DATE") != null
                        && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                    objRDto.setCopcApproveDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setStageName(rs.getString("ORDER_STAGE"));
                objRDto.setRemarks(rs.getString("REMARKS"));
                objRDto.setProductName(rs.getString("SERVICESTAGE"));
                objRDto.setSubProductName(rs.getString("SERVICESUBTYPENAME"));
                objRDto.setZoneName(rs.getString("ZONE"));
                objRDto.setPoAmounts(rs.getDouble("POAMOUNT"));
                objRDto.setServiceId(rs.getInt("SERVICEID"));
                objRDto.setServiceProductID(rs.getInt("SERVICEPRODUCTID"));
                objRDto.setBillingTriggerFlag(rs.getString("BILLINGTRIGGERFLAG"));
                objRDto.setOldOrderNo(rs.getInt("OLDORDERNO"));
            }
            if (objDto.getIsUsage() == 1) {
                objRDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO"));
                objRDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
                objRDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));//FX_ACCOUNT_EXTERNAL_ID
                objRDto.setFxInternalId(rs.getInt("INTERNAL_ID"));
                objRDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));//CHILD_ACCOUNT_FX_STATUS
                objRDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objRDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objRDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objRDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                ComponentsDto dto = new ComponentsDto();
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));//RC_NRC_COMP_AMOUNT
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC"));
                dto.setStartDateDays(rs.getInt("COMP_START_DAYS"));//START_DAYS
                dto.setStartDateMonth(rs.getInt("COMP_START_MONTHS"));//START_MONTHS
                dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC"));
                dto.setEndDateDays(rs.getInt("COMP_END_DAYS"));//END_DAYS
                dto.setEndDateMonth(rs.getInt("COMP_END_MONTHS"));//END_MONTHS
                objRDto.setServiceDetDescription(rs.getString("PARENT_NAME"));
                objRDto.setChangeTypeName(rs.getString("SERVICE_ORDER_TYPE_DESC"));
                objRDto.setCompanyName(rs.getString("ENTITYNAME"));
                objRDto.setCopcApproveDate(rs.getString("COPC_APPROVED_DATE"));
                if (rs.getString("COPC_APPROVED_DATE") != null
                        && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                    objRDto.setCopcApproveDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setStageName(rs.getString("ORDERSTAGE"));
                objRDto.setRemarks(rs.getString("DISCONNECTION_REMARKS"));
                objRDto.setProductName(rs.getString("SERVICENAME"));
                objRDto.setCurrencyCode(rs.getString("CURRENCYNAME"));
                objRDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
                objRDto.setZoneName(rs.getString("ZONENNAME"));
                objRDto.setPoAmounts(rs.getDouble("TOTALPOAMOUNT"));
                objRDto.setServiceId(rs.getInt("SERVICE_NO"));
                objRDto.setServiceProductID(rs.getInt("Order_Line_Id"));
                objRDto.setBillingTriggerFlag(rs.getString("Billing_Trigger_Flag"));
                objRDto.setOldOrderNo(rs.getInt("Pre_Crm_orderNo"));
                dto.setStartDate(rs.getString("SYSTEM_START_DATE"));
                if (rs.getString("SYSTEM_START_DATE") != null
                        && !"".equals(rs.getString("SYSTEM_START_DATE"))) {
                    Date date = df.parse(dto.getStartDate());
                    dto.setStartDate((utility.showDate_Report(date)).toUpperCase());
                }

                dto.setEnd_date(rs.getString("COMPONENT_END_DATE"));
                if (rs.getString("COMPONENT_END_DATE") != null
                        && !"".equals(rs.getString("COMPONENT_END_DATE"))) {
                    Date date = df.parse(dto.getEnd_date());
                    dto.setEnd_date((utility.showDate_Report(date)).toUpperCase());
                }
                objRDto.setComponentDto(dto);
                //NANCY START
                objRDto.setIsDifferential(rs.getString("IS_DIFFERENTIAL"));
                objRDto.setCopcApproverName(rs.getString("COPC_APPROVER_NAME"));
                objRDto.setEffectiveDate(rs.getString("EFFECTIVEDATE"));
                if (rs.getString("EFFECTIVEDATE") != null && !"".equals(rs.getString("EFFECTIVEDATE"))) {
                    objRDto.setEffectiveDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("EFFECTIVEDATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
                if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null
                        && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) {
                    objRDto.setBillingTriggerCreateDate((utility
                            .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setIsTriggerRequired(rs.getString("IsTriggerRequired"));
                objRDto.setLineTriggered(rs.getString("LineTriggered"));
                objRDto.setTriggerProcess(rs.getString("TriggerProcess"));
                objRDto.setTriggerDoneBy(rs.getString("TriggerDoneBy"));
                objRDto.setAutomaticTriggerError(rs.getString("AutomaticTriggerError"));
                //NANCY END
            }

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
                //recordCount = countFlag;
            }
            objUserList.add(objRDto);
        }
        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//from   w  w  w  . jav  a 2 s  . com
 * @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;
}