Example usage for java.sql CallableStatement setNull

Introduction

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

Prototype

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

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

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

public ArrayList<CustomerBaseReportsDTO> viewCustomerBaseReport(CustomerBaseReportsDTO objDto) {
    //   Nagarjuna
    String methodName = "viewCustomerBaseReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;//from w ww. j  a  v a2s.co m
    int recordCount = 0;
    ArrayList<CustomerBaseReportsDTO> listSearchDetails = new ArrayList<CustomerBaseReportsDTO>();
    CustomerBaseReportsDTO objReportsDto = null;
    Utility utility = new Utility();
    Date tempDate = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetCustomerbaseReport);
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

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

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

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        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(7, formattedDate);
        } else {
            proc.setNull(7, 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(8, formattedDate1);
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new CustomerBaseReportsDTO();
            replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS"));
            objReportsDto.setFxSiId(rs.getString("FX_SI_ID"));
            objReportsDto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));
            tempDate = rs.getDate("SERVICEACTIVEDT");
            if (tempDate != null) {
                objReportsDto.setActiveDate(utility.showDate_Report(tempDate).toUpperCase());
            }
            objReportsDto.setInActiveDate(rs.getString("DISCONNECTION_DATE"));
            if (!(rs.getString("DISCONNECTION_DATE") == null || rs.getString("DISCONNECTION_DATE") == "")) {
                objReportsDto.setInActiveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("DISCONNECTION_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setOrderNo((rs.getString("ORDERNO")));
            objReportsDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setAccountno(rs.getString("INTERNAL_ID"));
            objReportsDto.setAccountSegment(rs.getString("ACCOUNT_SEGMENT"));
            objReportsDto.setParent_name(rs.getString("PARENT_ID"));
            objReportsDto.setBillingFormatName(VAR_BILL_FNAME);
            objReportsDto.setBillCompany(rs.getString("BILL_COMPANY"));
            objReportsDto.setBillingAddress(VAR_BILL_ADDRESS1);
            objReportsDto.setBilling_address2(VAR_BILL_ADDRESS2);
            objReportsDto.setBilling_address(VAR_BILL_ADDRESS3);
            objReportsDto.setBillCity(VAR_BCP_CITY_NAME);
            objReportsDto.setBillState(VAR_BCP_STATE_NAME);
            objReportsDto.setAccountManager(rs.getString("ACCOUNT_MANAGER_NAME"));
            objReportsDto.setAcmgrEmail(rs.getString("ACCOUNT_MANAGER_EMAILID"));
            objReportsDto.setAccountMgrPhoneNo(rs.getString("ACCOUNT_MANAGER_PHONENO"));
            objReportsDto.setContact1_Phone(VAR_BILL_PHONE);
            objReportsDto.setContact2_Phone(rs.getString("CONTACT2_PHONE"));
            objReportsDto.setBillZip(VAR_BCP_PIN);
            objReportsDto.setOrder_type(rs.getString("ORDER_TYPE"));
            objReportsDto.setContactName(VAR_BILL_CON_PER_NAME);
            objReportsDto.setContactPersonEmail(VAR_EMAIL_ID);
            objReportsDto.setChairPersonName(rs.getString("CHAIRPERSON_NAME"));
            objReportsDto.setChairPersonPhone(rs.getString("CHAIRPERSON_PHONE"));
            objReportsDto.setChairPersonEmail(rs.getString("CHAIRPERSON_EMAIL"));
            objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
            objReportsDto.setComponentActiveDate(rs.getString("COMP_ACTIVE_DATE"));
            if (!(rs.getString("COMP_ACTIVE_DATE") == null || rs.getString("COMP_ACTIVE_DATE") == "")) {
                objReportsDto.setComponentActiveDate(
                        (utility.showDate_Report(sdf.parse(rs.getString("COMP_ACTIVE_DATE")))).toUpperCase());
            }
            objReportsDto.setBusinessSegment(rs.getString("BUSINESS_SEGMENT"));
            if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID"))
                    && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) {
                String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"),
                        rs.getString("ENTITYID"));
                objReportsDto.setBill_period(tBillPeriod);
            }
            objReportsDto.setFrequencyName(rs.getString("BILL_FREQUENCY"));
            objReportsDto.setProductName(rs.getString("PRODUCT"));
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

---

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

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

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlBillingWorkQueue);
        proc.setInt(1, java.sql.Types.BIGINT);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(2, objDto.getOrderType().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(3, objDto.getFromDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(4, objDto.getToDate().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

        /*
         * add party no and party name
         */
        if (objDto.getParty_no() != 0 && !"".equals(objDto.getParty_no())) {
            proc.setInt(7, objDto.getParty_no());
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        if (objDto.getPartyName() != null && !"".equals(objDto.getPartyName())) {
            proc.setString(8, objDto.getPartyName().trim());
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }
        if (objDto.getOrderStage() != null && !"".equals(objDto.getOrderStage())) {
            proc.setString(9, objDto.getOrderStage().trim());
        } else {
            proc.setNull(9, java.sql.Types.VARCHAR);
        }
        if (objDto.getHardwareType() != null && !"".equals(objDto.getHardwareType())) {
            proc.setString(10, objDto.getHardwareType().trim());
        } else {
            proc.setNull(10, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(11, pagingSorting.getSortByColumn());// columnName
        proc.setString(12, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(13, pagingSorting.getStartRecordId());// start index
        proc.setInt(14, pagingSorting.getEndRecordId());// end index
        proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        //proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 0 : 0));// end
        // index
        //System.out.println("sqlBillingWorkQueue :"+sqlBillingWorkQueue);
        rs = proc.executeQuery();
        int countFlag = 0;
        BillingWorkQueueReportDTO objdto;

        while (rs.next() != false) {
            countFlag++;
            //   System.out.println("in while roop of rs");
            setBlank();
            replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS"));
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMLOC"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECLOC"));
            objdto = new BillingWorkQueueReportDTO();
            objdto.setLogicalSINo(rs.getString("LOGICAL_CIRCUIT_ID"));
            objdto.setCustSINo(rs.getString("CUST_LOGICAL_SI_ID"));
            objdto.setServiceName(rs.getString("SERVICE_NAME"));
            objdto.setLinename(rs.getString("LINE_NAME"));
            //            objdto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
            //            objdto.setChargeTypeID(rs.getInt("CHARGE_TYPE_ID")); 
            //            objdto.setChargeName(rs.getString("CHARGE_NAME"));
            //            objdto.setChargeFrequency(rs.getString("FREQUENCY"));
            //            objdto.setBillPeriod(rs.getString("BILL_PERIOD"));
            /*
             * newly added fields in code
             */
            //            objdto.setEndDateLogic(rs.getString("ENDDATELOGIC"));
            //            if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE")))
            //            {
            //               objdto.setStartDate(rs.getString("CHARGE_START_DATE"));
            //Date date=df.parse(objdto.getStartDate());
            //objdto.setStartDate((utility.showDate_Report(date)).toUpperCase());
            //            }
            //            if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE")))
            //            {
            //               objdto.setChargeEndDate(rs.getString("CHARGE_END_DATE"));
            //            }
            //            objdto.setAdvance(rs.getString("ADVANCE"));
            //            objdto.setRate_code(rs.getString("TRAI_RATE"));
            //            objdto.setDiscount(rs.getString("DISCOUNT"));
            //            objdto.setInstallRate(rs.getString("INSTALRATE"));
            //            objdto.setInterestRate(rs.getInt("INTREST_RATE"));
            //            objdto.setPrincipalAmount(rs.getInt("PRINCIPAL_AMOUNT"));
            objdto.setNoticePeriod(rs.getLong("NOTICEPERIOD"));
            objdto.setPenaltyClause(rs.getString("PENALTYCLAUSE"));
            objdto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD"));

            //            if (rs.getString("WARRANTY_START_DATE") != null && !"".equals(rs.getString("WARRANTY_START_DATE")))
            //            {
            //               objdto.setWarrantyStartDate(rs.getString("WARRANTY_START_DATE"));
            //               objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyStartDate())).toUpperCase());
            //            }
            //            if (rs.getString("WARRANTY_END_DATE") != null && !"".equals(rs.getString("WARRANTY_END_DATE")))
            //            {
            //               objdto.setWarrantyEndDate(rs.getString("WARRANTY_END_DATE"));
            //               objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyEndDate())).toUpperCase());
            //            }
            //            objdto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE"));
            objdto.setContractStartDate(rs.getString("CONTRACT_START_DATE"));
            objdto.setContractEndDate(rs.getString("CONTRACT_END_DATE"));
            //            objdto.setStartDateLogic(rs.getString("STARTDATELOGIC"));
            //            objdto.setDnd_Dispatch_And_Delivered(rs.getString("DND_DISPATCH_AND_DELIVERED"));
            //            objdto.setDnd_Dispatch_But_Not_Delivered(rs.getString("DND_DISPATCH_BUT_NOT_DELIVERED"));
            objdto.setBilling_address(VAR_BILLING_ADDRESS);
            objdto.setServiceTypeDescription(rs.getString("SERVICE_ORDER_TYPE_DESC"));
            objdto.setCancelBy(rs.getString("CANCEL_BY"));
            objdto.setCanceldate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {
                objdto.setCanceldate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setCancelReason(rs.getString("CANCEL_REASON"));
            objdto.setDemo(rs.getString("DEMO_TYPE"));
            /*
             * end of newly ended code
             */
            // start_date = ?
            objdto.setAccountID(rs.getInt("ACCOUNT_NUMBER"));
            objdto.setCreditPeriodName(rs.getString("CREDIT_PERIOD"));
            //objdto.setCurrencyName(rs.getString("CURNAME")); // is it currency 
            objdto.setCurrencyName(rs.getString("CURRENCY"));
            //objdto.setEntity(rs.getString("ENTITYNAME")); // is it legal entity
            objdto.setEntity(rs.getString("LEGAL_ENTITY"));
            objdto.setBillingMode(rs.getString("BILLING_MODE_NAME")); // text of BILLINGMODE
            objdto.setBillingTypeName(rs.getString("BILL_TYPE"));
            objdto.setBillingformat(rs.getString("BILL_FORMAT"));
            objdto.setLicCompanyName(rs.getString("LICENSE_COMP"));
            objdto.setTaxation(rs.getString("TAXATION_NAME")); // is it TAXATION
            //            objdto.setTaxation(rs.getString("TAXATION"));
            objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); // is it BILLINGLEVEL
            //            objdto.setBillingLevelName(rs.getString("BILLINGLEVEL"));
            //objdto.setStore(rs.getString("STORENAME")); // is it STORE 
            //            objdto.setStore(rs.getString("STORE"));
            //            objdto.setHardwaretypeName(rs.getString("HARDWARETYPE"));
            objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE"));
            //            objdto.setSaleNature(rs.getString("NATURE_OF_SALE"));
            //            objdto.setSaleTypeName(rs.getString("TYPE_OF_SALE"));
            objdto.setPrimaryLocation(VAR_PRIMARYLOCATION);
            objdto.setSeclocation(VAR_SECONDARYLOCATION);
            objdto.setPoNumber(rs.getInt("PODETAILNUMBER"));
            objdto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {
                Date date = rs.getDate("PODATE");
                objdto.setPoDate((utility.showDate_Report(date)).toUpperCase());
            }

            objdto.setParty_num(rs.getString("PARTY_NO"));
            //            objdto.setChargeAnnotation(rs.getString("ANNOTATION"));
            //            objdto.setFx_sd_charge_status(rs.getString("FX_SD_CHG_STATUS"));

            //            objdto.setFx_charge_status(rs.getString("FX_STATUS"));
            //            objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHG_STATUS"));
            //objdto.setTokenID(rs.getInt("TOKEN_ID")); // is it TOKEN_NO
            //            objdto.setTokenno(rs.getString("TOKEN_NO"));
            objdto.setModifiedDate(rs.getString("LAST_UPDATE_DATE"));
            if (rs.getString("LAST_UPDATE_DATE") != null && !"".equals(rs.getString("LAST_UPDATE_DATE"))) {
                objdto.setModifiedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("LAST_UPDATE_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setBillingTriggerFlag(rs.getString("BILLING_TRIG_FLAGNAME")); // text of BILLING_TRIG_FLAG

            if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) {
                objdto.setPm_pro_date(rs.getString("PM_PROV_DATE"));
                /* String s1=rs.getString("PM_PROVISIONING_DATE");
                 String s3=s1.substring(0,7).toUpperCase();
                 String s4=s1.substring(9,11);
                 String s5=s3.concat(s4);
                 objdto.setPm_pro_date(s5);*/
            }
            if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) {
                objdto.setLocDate(rs.getString("LOC_DATE"));
                Date date = df.parse(objdto.getLocDate());
                objdto.setLocDate((utility.showDate_Report(date)).toUpperCase());
            }
            if (rs.getString("BILLING_TRIG_DATE") != null && !"".equals(rs.getString("BILLING_TRIG_DATE"))) {
                objdto.setBilling_trigger_date(rs.getString("BILLING_TRIG_DATE"));
                Date date = df.parse(objdto.getBilling_trigger_date());
                objdto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase());
            }
            //            objdto.setChallenno(rs.getString("CHALLEN_NO"));
            //            objdto.setChallendate(rs.getString("CHALLEN_DATE"));

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

            //FX_ACCOUNT_EXTERNAL_ID = ?
            //objdto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));
            //objdto.setChild_account_creation_status(rs.getString("CHILD_ACCOUNT_FX_STATUS"));
            objdto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER"));
            objdto.setChild_ac_fxstatus(rs.getString("CHILD_ACCOUNT_STATUS_NAME")); // text of CHILD_ACCOUNT_FX_STATUS

            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {
                objdto.setOrderDate(rs.getString("ORDERDATE"));
                //Date date=df.parse(objdto.getOrderDate());
                objdto.setOrderDate((utility.showDate_Report(objdto.getOrderDate())).toUpperCase());

            }
            if (rs.getString("APPROVED_DATE") != null && !"".equals(rs.getString("APPROVED_DATE"))) {
                objdto.setCopcapprovaldate(rs.getString("APPROVED_DATE"));
                objdto.setCopcapprovaldate(
                        (utility.showDate_Report(objdto.getCopcapprovaldate())).toUpperCase());
            }
            objdto.setOrderType(rs.getString("ORDERTYPE"));
            if (rs.getString("BILL_TRG_CREATE_DATE") != null
                    && !"".equals(rs.getString("BILL_TRG_CREATE_DATE"))) {
                objdto.setBillingtrigger_createdate(rs.getString("BILL_TRG_CREATE_DATE"));
                objdto.setBillingtrigger_createdate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("BILL_TRG_CREATE_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setRatio(rs.getString("RATIO"));
            objdto.setProductName(rs.getString("PRODUCT"));
            objdto.setSubProductName(rs.getString("SUBPRODUCT"));
            objdto.setHardwareType(rs.getString("HARDWARE_FLAG"));
            objdto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objdto.setOrderStage(rs.getString("ORDER_STAGE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                objdto.setRfsDate(rs.getString("RFS_DATE"));
                objdto.setRfsDate((utility.showDate_Report(objdto.getRfsDate())).toUpperCase());

            }
            // PORECEIVEDATE = ?
            /*objdto.setPoReceiveDate(rs.getString("PORECEIVEDATE"));
            if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE")))
            {
                       
               Date date=df.parse(objdto.getPoReceiveDate());
               objdto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase());
                       
            }*/
            objdto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {
                objdto.setCustPoDate(rs.getString("CUSTPODATE"));
                //Date date=df.parse(objdto.getCustPoDate());
                objdto.setCustPoDate((utility.showDate_Report(objdto.getCustPoDate())).toUpperCase());
            }
            //            objdto.setCharge_status(rs.getString("CHARGES_STATUS"));
            objdto.setLOC_No(rs.getString("LOC_NUMBER"));
            //objdto.setAddress1(rs.getString("ADDRESS")); // is it BILLING_ADDRESS
            objdto.setAddress1(VAR_BILLING_ADDRESS);
            objdto.setM6cktid(rs.getString("CIRCUIT_ID"));
            objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objdto.setRegion(rs.getString("REGION"));
            objdto.setBandwaidth(rs.getString("BILLING_BANDWIDTH"));
            objdto.setVertical(rs.getString("VERTICAL"));
            objdto.setAccountManager(rs.getString("ACCOUNT_MGR"));
            objdto.setProjectManager(rs.getString("PROJECT_MGR"));
            objdto.setDistance(rs.getString("CHARGEABLE_DISTANCE"));
            //            objdto.setDispatchAddress1(rs.getString("DISPATCH_ADDRESS"));
            objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE"));
            // productname
            objdto.setPartyName(rs.getString("PARTY_NAME"));
            //objdto.setBilling_location_from(rs.getString("BILLING_ADDRESS"));

            // DEMO_ORDER = ?
            //objdto.setDemo(rs.getString("DEMO_ORDER"));

            // CRM_PRODUCTNAME = ?
            //objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME"));

            objdto.setToLocation(rs.getString("TO_ADDRESS"));
            objdto.setFromLocation(rs.getString("FROM_ADDRESS"));
            objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));

            //BILLING_BANDWIDTH_UOM = ?   //remove this column
            //objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));

            //BL_SOURCE = ?
            //objdto.setBlSource(rs.getString("BL_SOURCE"));
            objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID"));
            objdto.setOrderNumber(rs.getInt("ORDERID"));
            //            objdto.setChargeAmount(rs.getDouble("INV_AMT"));
            // LINEITEMAMOUNT = ?
            //objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT"));
            //TOTAL_CHARGE_AMT = ?
            //objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT"));
            //            objdto.setContractPeriod(rs.getInt("CONTRACT_PERIOD_MNTHS"));

            //objdto.setTotalPoAmt(""+rs.getDouble("TOTAL_POAMOUNT")); // is it POAMOUNT
            objdto.setTotalPoAmt("" + rs.getDouble("POAMOUNT"));
            //PARTY_ID = ?
            //objdto.setParty_id(rs.getInt("PARTY_ID")); // is it party no

            // CRMACCOUNTNO = ?
            //objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            //m6 productid
            objdto.setM6OrderNo(rs.getInt("M6_ORDER_ID"));

            // CUST_TOT_PO_AMT = ?
            objdto.setCust_total_poamt(rs.getDouble("tot_amount")); // old value from CUST_TOT_PO_AMT

            //m6 order,business
            // PK_CHARGE_ID = ?
            //objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12

            // objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); // CONTRACT_PERIOD_MNTHS is using above 
            //              objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE"));
            objdto.setServiceId(rs.getInt("SERVICE_NO"));
            if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) {
                objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE"));
                objdto.setPoExpiryDate((utility.showDate_Report(objdto.getPoExpiryDate())).toUpperCase());
            }
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
                //recordCount = countFlag;
            }
            objdto.setFxInternalId(rs.getInt("INTERNAL_ID"));
            //            objdto.setMinimum_bandwidth(rs.getString("MINIMUM_BANDWIDTH"));//Need to add in View : AKS(Added)
            //            objdto.setMinimum_bandwidth_UOM(rs.getString("MINIMUM_BANDWIDTH_UOM"));//Need to add in View : AKS(Added)
            objdto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
            objdto.setComponentID(rs.getInt("COMPONENT_ID"));
            objdto.setComponentName(rs.getString("COMPONENT_NAME"));
            objdto.setPackageID(rs.getInt("PACKAGE_ID"));
            objdto.setPackageName(rs.getString("PACKAGE_NAME"));
            ComponentsDto dto = new ComponentsDto();
            dto.setComponentType(rs.getString("COMPONENT_TYPE"));
            dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));//RC_NRC_COMP_AMOUNT : AKS
            dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
            dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC"));
            dto.setStartDate(rs.getString("SYSTEM_START_DATE")); // COMPONENT_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.setEndLogic(rs.getString("COMPONENT_END_LOGIC"));
            dto.setEnd_date(rs.getString("SYSTEM_END_DATE")); // COMPONENT_END_DATE
            if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) {
                Date date = df.parse(dto.getEnd_date());
                dto.setEnd_date((utility.showDate_Report(date)).toUpperCase());
            }
            dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
            dto.setEndTokenNo(rs.getString("END_COMPONENT_TOKEN_NO"));
            dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS"));
            //dto.setChargesSumOfLineitem(rs.getString("FX_START_COMPONENT_STATUS"));
            dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));//COMP_FX_INSTANCE_ID : AKS
            dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS"));//FX_END_COMPONENT_STATUS :AKS
            //dto.setStartStatus(rs.getString("FX_ST_COMPONENT_STATUS"));
            objdto.setComponentDto(dto);

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

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

    return objUserList;
}

---

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                }

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

            /// End

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

---

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

/**
 * /*from w ww . j a va  2s. co  m*/
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<OrderReportNewDetailCwnDTO> viewOrderReportNew(OrderReportNewDetailCwnDTO objDto)
        throws Exception {
    //Nagarjuna
    String methodName = "viewOrderReportNew", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<OrderReportNewDetailCwnDTO> objUserList = new ArrayList<OrderReportNewDetailCwnDTO>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    OrderReportNewDetailCwnDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;
    Timestamp ts = null;

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlOrderReportNewForUsage);

        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }

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

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

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

        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(12, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(12, 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(13, formattedDate1);
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));

            objRDto = new OrderReportNewDetailCwnDTO();
            objRDto.setPartyName(rs.getString("PARTYNAME"));
            objRDto.setOrderNo(rs.getString("ORDERNO"));
            //objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));LOGICAL_SI_NO(Commented By :AKS)
            //objRDto.setServiceId(rs.getInt("SERVICENO"));SERVICEID(Commented By :AKS)
            objRDto.setQuoteNo(rs.getString("QUOTENO"));
            objRDto.setProductName(rs.getString("PRODUCTNAME"));
            objRDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            //         objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION
            //         objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION

            //objRDto.setPrjmngname(rs.getString("PMNAME"));(Commented By :AKS)PROJECTMANAGER
            objRDto.setPrjmgremail(rs.getString("PMEMAIL"));//PMEMAIL:same:(AKS)Need To Add in Component View
            //objRDto.setActmngname(rs.getString("ACTMNAME"));ACCOUNTMANAGER
            objRDto.setZoneName(rs.getString("ZONENNAME"));//(AKS)Need To Add in Component View
            //objRDto.setRegionName(rs.getString("REGIONNAME"));REGION
            objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objRDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));

            objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            //change
            tempDate = rs.getDate("ORDERDATE");
            if (tempDate != null) {
                objRDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            ts = rs.getTimestamp("AM_APPROVAL_DATE");
            if (ts != null) {
                tempDate = new Date(ts.getTime());
                objRDto.setAmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }

            ts = rs.getTimestamp("PM_APPROVAL_DATE");
            if (ts != null) {
                tempDate = new Date(ts.getTime());
                objRDto.setPmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }

            ts = rs.getTimestamp("NIO_APPROVAL_DATE");
            if (ts != null) {
                tempDate = new Date(ts.getTime());
                objRDto.setNio_approve_date((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objRDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE"));

            objRDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE"));

            /*objRDto.setRfs_date(rs.getString("RFS_DATE"));SERVICE_RFS_DATE
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE")))
            {
                       
               objRDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))).toUpperCase());
                       
            }*/
            //objRDto.setOrdercategory(rs.getString("ORDERCATEGORY"));ORDERTYPE(Commented By :AKS)
            objRDto.setOrderStatus(rs.getString("STATUS"));

            //objRDto.setLinename(rs.getString("LINENAME"));SERVICEDETDESCRIPTION(Commented By :AKS)

            //objRDto.setServiceProductID(rs.getInt("LINENO"));Order_Line_Id(Commented By :AKS)
            objRDto.setServiceName(rs.getString("SERVICENAME"));
            //objRDto.setAccountID(rs.getInt("ACCOUNTID"));CRMACCOUNTNO(Commented By :AKS)
            objRDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            //objRDto.setEntity(rs.getString("COMPANYNAME"));ENTITYNAME

            objRDto.setServiceType(rs.getString("SERVICETYPE"));
            objRDto.setUom(rs.getString("UOM"));
            objRDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objRDto.setDistance(rs.getString("DISTANCE"));
            //objRDto.setFrom_city(rs.getString("FROM_CITY"));//(AKS)Need To Add in Component View
            if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) {
                String ss[] = rs.getString("FROM_CITY").split("~~");
                objRDto.setFrom_city(ss[8]);
            } else {
                objRDto.setFrom_city(rs.getString("FROM_CITY"));
            }
            //objRDto.setTo_city(rs.getString("TO_CITY"));//(AKS)Need To Add in Component View
            if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) {
                String ss[] = rs.getString("TO_CITY").split("~~");
                objRDto.setTo_city(ss[8]);
            } else {
                objRDto.setTo_city(rs.getString("TO_CITY"));
            }

            objRDto.setRatio(rs.getString("RATIO"));
            objRDto.setTaxation(rs.getString("TAXATIONVALUE"));

            objRDto.setAccountManager(rs.getString("ACTMEMAIL"));//(AKS)Need To Add in Component View
            //objRDto.setCurrencyCode(rs.getString("CURNAME"));CURRENCYNAME
            objRDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
            //objRDto.setPoAmount(rs.getString("POAMOUNT"));TOTALPOAMOUNT
            objRDto.setBisource(rs.getString("BISOURCE"));
            objRDto.setOrderType(rs.getString("ORDERTYPE"));

            //objRDto.setParent_name(rs.getString("PARENTNAME"));PARENT_NAME
            objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objRDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            //Saurabh : Changes to separate charge related specific column from common
            if (objDto.getIsUsage() == 0) {

                objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));

                objRDto.setFrequencyName(rs.getString("PAYMENTTERM"));
                objRDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));

                objRDto.setSub_linename(rs.getString("ORDER_SUBLINENAME"));
                objRDto.setChargeName(rs.getString("CHARGE_NAME"));
                objDto.setChargeinfoID(rs.getString("CHARGEINFOID"));

                objRDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL"));
                objRDto.setOldlineamt(rs.getString("OLD_LINE_AMT"));
                objRDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD"));

                objRDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));

                objRDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));

                objRDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
                //Start: AKS- Adding Column in Chanrge Section Which are not in View
                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.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
                objRDto.setServiceId(rs.getInt("SERVICENO"));
                objRDto.setPrjmngname(rs.getString("PMNAME"));
                objRDto.setActmngname(rs.getString("ACTMNAME"));
                objRDto.setRegionName(rs.getString("REGIONNAME"));
                objRDto.setRfs_date(rs.getString("RFS_DATE"));
                if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                    objRDto.setRfs_date(
                            (utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setOrdercategory(rs.getString("ORDERCATEGORY"));
                objRDto.setLinename(rs.getString("LINENAME"));
                objRDto.setServiceProductID(rs.getInt("LINENO"));
                objRDto.setAccountID(rs.getInt("ACCOUNTID"));
                objRDto.setEntity(rs.getString("COMPANYNAME"));
                objRDto.setCurrencyCode(rs.getString("CURNAME"));
                objRDto.setPoAmount(rs.getString("POAMOUNT"));
                objRDto.setParent_name(rs.getString("PARENTNAME"));
                objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION
                objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION
                objRDto.setCustPoDate(rs.getString("CUST_PODATE"));
                if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) {

                    objRDto.setCustPoDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("CUST_PODATE").getTime())))
                                    .toUpperCase());

                }
                //End AKS
            }

            //Meenakshi : Changes for Usage
            if (objDto.getIsUsage() == 1) {

                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"));
                //Start Adding Column in Component Section Which are present in View
                ts = rs.getTimestamp("COPC_APPROVED_DATE");
                if (ts != null) {
                    tempDate = new Date(ts.getTime());
                    objRDto.setCopcApproveDate((Utility.showDate_Report(tempDate)).toUpperCase());
                }
                objRDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO"));
                objRDto.setServiceId(rs.getInt("SERVICE_NO"));
                objRDto.setPrjmngname(rs.getString("PROJECTMANAGER"));
                objRDto.setActmngname(rs.getString("ACCOUNTMANAGER"));
                objRDto.setRegionName(rs.getString("REGION"));
                tempDate = rs.getDate("SERVICE_RFS_DATE");
                if (tempDate != null) {
                    objRDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase());
                }
                objRDto.setOrdercategory(rs.getString("ORDERTYPE"));
                objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
                objRDto.setServiceProductID(rs.getInt("Order_Line_Id"));
                objRDto.setAccountID(rs.getInt("CRMACCOUNTNO"));
                objRDto.setEntity(rs.getString("ENTITYNAME"));
                objRDto.setCurrencyCode(rs.getString("CURRENCYNAME"));
                objRDto.setPoAmount(rs.getString("TOTALPOAMOUNT"));
                objRDto.setParent_name(rs.getString("PARENT_NAME"));
                objRDto.setPrimarylocation(VAR_PRIMARYLOCATION);//AKS:Need To Add in Component View:PRIMARYLOCATION
                objRDto.setSeclocation(VAR_SECONDARYLOCATION);//AKS:Need To Add in Component View:SECONDARYLOCATION
                objRDto.setRE_LOGGED_LSI_NO(rs.getString("RE_LOGGED_LSI_NO"));
                objRDto.setPARALLEL_UPGRADE_LSI_NO(rs.getString("PARALLEL_UPGRADE_LSI_NO"));
                objRDto.setCHARGEDISCONNECTIONSTATUS(rs.getString("CHARGEDISCONNECTIONSTATUS"));
                objRDto.setSubchange_type(rs.getString("NAME_SUBTYPE"));
                objRDto.setFxAccountExternalId(rs.getString("CHILD_ACCOUNT_NUMBER"));
                tempDate = rs.getDate("CUSTPODATE");
                if (tempDate != null) {
                    objRDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
                }
                //End : AKS

                ComponentsDto dto = new ComponentsDto();
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                //nagarjuna OB Value Usage
                objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString());
                objDto.setObValueLastUpdateDate(rs.getString("OB_VALUE_LAST_UPDATE_DATE"));
                //nagarjuna OB Value Usage END
                //<!--GlobalDataBillingEfficiency BFR7  -->
                objRDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON"));
                objRDto.setLastApprovalRemarks(rs.getString("LAST_APPROVAL_REMARKS"));
                //NANCY
                objRDto.setePCNNo(rs.getString("EPCN_NO"));
                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.setComponentDto(dto);
            }

            objRDto.setOsp(rs.getString("OSP"));
            objRDto.setCustSeg_Description(rs.getString("DESCRIPTION"));
            /// End
            objRDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            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.freeConnection(conn);

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

    return objUserList;
}

---

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

public ArrayList<NonAPP_APPChangeOrderDetailsDTO> viewNonMigAppUnappNewOrderDetails(
        NonAPP_APPChangeOrderDetailsDTO objDto) {
    //Nagarjuna//from  w ww.  j av  a 2  s.  c om
    String methodName = "viewNonMigAppUnappNewOrderDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<NonAPP_APPChangeOrderDetailsDTO> listSearchDetails = new ArrayList<NonAPP_APPChangeOrderDetailsDTO>();
    NonAPP_APPChangeOrderDetailsDTO objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetNonMigAppUnappNewOrderDetails);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getApprovalType() != null && !"".equals(objDto.getApprovalType())) {
            proc.setString(2, objDto.getApprovalType().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getServiceOrderType() != null && !"".equals(objDto.getServiceOrderType())) {
            proc.setInt(3, Integer.parseInt(objDto.getServiceOrderType().trim()));
        } else {
            proc.setNull(3, java.sql.Types.BIGINT);
        }
        if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) {
            proc.setString(4, objDto.getOrdermonth().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(5, objDto.getVerticalDetails().trim());
        } else {
            proc.setNull(5, java.sql.Types.VARCHAR);
        }
        if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) {
            proc.setString(6, objDto.getServiceName().trim());
        } else {
            proc.setNull(6, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(7, objDto.getFromOrderNo());
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) {
            proc.setInt(8, objDto.getToOrderNo());
        } else {
            proc.setNull(8, java.sql.Types.BIGINT);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(9, pagingSorting.getSortByColumn());// columnName
        proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(11, pagingSorting.getStartRecordId());// start index
        proc.setInt(12, pagingSorting.getEndRecordId());// end index
        proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        proc.setInt(14, objDto.getIsUsage());
        if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) {
            proc.setString(15, objDto.getOrderyear().trim());
        } else {
            proc.setNull(15, java.sql.Types.VARCHAR);
        }
        rs = proc.executeQuery();
        while (rs.next()) {
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));

            objReportsDto = new NonAPP_APPChangeOrderDetailsDTO();
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//LineName
            objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC"));
            objReportsDto.setLogicalCircuitId(rs.getString("CKTID"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setKmsDistance(rs.getString("DISTANCE"));
            objReportsDto.setRatio(rs.getString("RATIO"));
            objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS"));
            objReportsDto.setToLocation(rs.getString("TO_ADDRESS"));
            // change
            objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION);
            objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION);
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setPonum(rs.getLong("PONUMBER"));
            // change
            tempDate = rs.getDate("PODATE");
            if (tempDate != null) {
                objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month
            objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT"));
            // change
            tempDate = rs.getDate("PORECEIVEDATE");
            if (tempDate != null) {
                objReportsDto.setPoReceiveDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            // change
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null) {
                objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT"));

            objReportsDto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

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

            }
            objReportsDto.setLOC_No(rs.getString("LOCNO"));
            objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {

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

            }
            //Bill Trg Create Date
            objReportsDto.setPmApproveDate(rs.getString("Pm_Prov_Date"));
            if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) {
                String s1 = rs.getString("Pm_Prov_Date");
                String s3 = s1.substring(0, 7);
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objReportsDto.setPmApproveDate(s5);

            }
            objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag"));

            //Business Serial No   
            //Opms Account Id   
            objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber   
            //Order Month   
            objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));
            objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus"));
            if (objDto.getIsUsage() == 1) {
                objReportsDto.setOrderStage(rs.getString("ORDERSTAGE"));
                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID"));
                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));

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

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

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

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

                }
                dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
                dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS"));
                //dto.setEndFxStatus(rs.getString("FX_END_COMPONENT_STATUS"));
                //dto.setComponentFXStatus(rs.getString("FX_STATUS"));               
                dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));

                objReportsDto.setComponentDto(dto);
            } else {
                objReportsDto.setOrderStage(rs.getString("STAGE"));
                objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS
                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setStorename(rs.getString("STORENAME"));
                objReportsDto.setSaleType(rs.getString("SALETYPE"));//Type Of Sale      
                objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setStartDate(rs.getString("START_DATE"));
                if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {

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

                }
                objReportsDto.setChargeAmount_String(rs.getString("INV_AMT"));
                objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt
                objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION"));
                objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No
                objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status
                objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS
            }

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

---

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

public ArrayList<DisconnectLineReportDTO> viewDisconnectionLineReport(DisconnectLineReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewDisconnectionLineReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;/*  www .  j a  v  a 2s . co  m*/
    int recordCount = 0;
    ArrayList<DisconnectLineReportDTO> listSearchDetails = new ArrayList<DisconnectLineReportDTO>();
    DisconnectLineReportDTO objReportsDto = null;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;

    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetDisconnectionLineReportForUsage);

        if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) {
            proc.setString(1, objDto.getOrdermonth().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
            //proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) {
            proc.setString(4, objDto.getServiceName().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(6, objDto.getVerticalDetails().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));
        proc.setInt(12, objDto.getIsUsage());
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new DisconnectLineReportDTO();
            objReportsDto.setPartyNo(rs.getInt("PARTY_NO"));
            objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO"));
            objReportsDto.setCust_name(rs.getString("PARTYNAME"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setOrder_type(rs.getString("ORDERTYPE"));
            objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
            objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objReportsDto.setCkt_id(rs.getString("CKTID"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setUom(rs.getString("UOM"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setDistance(rs.getString("DISTANCE"));
            objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS"));
            objReportsDto.setLocation_to(rs.getString("TO_ADDRESS"));
            //objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION"));
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION);
            //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            objReportsDto.setSeclocation(VAR_SECONDARYLOCATION);
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));

            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setBill_period(rs.getString("BILL_PERIOD"));
            if ((rs.getString("CONFIG_ID")) != null
                    || !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null
                    || !"".equals(rs.getString("ENTITYID"))) {
                String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"),
                        rs.getString("ENTITYID"));
                objReportsDto.setBill_period(tBillPeriod);
            }
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setPoNumber(rs.getInt("PONUMBER"));
            objReportsDto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {
                //Date date=df.parse(objReportsDto.getPoDate());
                objReportsDto.setPoDate((utility.showDate_Report(objReportsDto.getPoDate())).toUpperCase());
            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT"));
            objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE"));
            tempDate = rs.getDate("PORECEIVEDATE");
            objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setPoReceiveDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            tempDate = rs.getDate("CUSTPODATE");
            objReportsDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {
                Date date = df.parse(objReportsDto.getLocDate());
                objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setLocno(rs.getString("LOCNO"));
            objReportsDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {
                Date date = df.parse(objReportsDto.getBilling_trigger_date());
                objReportsDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setPmapprovaldate(rs.getString("PM_PROV_DATE"));
            if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) {
                String s1 = rs.getString("Pm_Prov_Date");
                String s3 = (s1.substring(0, 7)).toUpperCase();
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objReportsDto.setPmApproveDate(s5);
            }
            objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_FLAG"));
            objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID"));
            objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));
            objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo"));
            objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS"));
            objReportsDto.setStageName(rs.getString("STAGE"));
            objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objReportsDto.setNeworder_remarks(rs.getString("NEWORDER_REMARKS"));
            objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE"));
            if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                objReportsDto.setCopcapprovaldate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE"));

            objReportsDto.setStandard_reason(rs.getString("STANDARDREASON"));
            tempDate = rs.getDate("ORDERDATE");
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            if (objDto.getIsUsage() == 0) {
                objReportsDto.setRatio(rs.getString("RATIO"));
                objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD"));
                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setStorename(rs.getString("STORENAME"));
                objReportsDto.setSaleType(rs.getString("SALETYPE"));
                objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE"));
                if (rs.getString("CSTATE_CHARGE_CURRENT_START_DATE") != null
                        && !"".equals(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE"))) {
                    Date date = df.parse(objReportsDto.getChargeEndDate());
                    objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase());
                }
                objReportsDto.setChargeAmount(rs.getDouble("INV_AMT"));
                objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT"));
                objReportsDto.setAnnitation(rs.getString("ANNOTATION"));
                objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
                if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null
                        && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) {
                    objReportsDto.setBillingtrigger_createdate((utility
                            .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime())))
                                    .toUpperCase());
                }
                objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));
                objReportsDto.setFx_status(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));
                objReportsDto.setFx_sd_status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));
                objReportsDto.setBusiness_serial_no(rs.getString("Business_No"));
                objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id"));
            }
            if (objDto.getIsUsage() == 1) {
                objReportsDto.setRatio(rs.getString("RATIO"));
                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
                objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));
                objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID"));
                objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                ComponentsDto dto = new ComponentsDto();
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                dto.setStartDate(rs.getString("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.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
                dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));
                dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS"));
                dto.setEnd_date(rs.getString("SYSTEM_END_DATE"));
                if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) {
                    Date date = df.parse(dto.getEnd_date());
                    dto.setEnd_date((utility.showDate_Report(date)).toUpperCase());
                }
                dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS"));
                objReportsDto.setComponentDto(dto);
            }

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

---

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                objReportsDto.setComponentDto(dto);

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

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

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

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

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

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

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

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

---

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

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

        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(4, objDto.getFromOrderNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }
        if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) {
            proc.setInt(5, objDto.getToOrderNo());
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }
        if (objDto.getLogicalSINumber() != 0 && !"".equals(objDto.getLogicalSINumber())) {
            proc.setInt(6, objDto.getLogicalSINumber());
        } else {
            proc.setNull(6, java.sql.Types.BIGINT);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(7, pagingSorting.getSortByColumn());// columnName
        proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(9, pagingSorting.getStartRecordId());// start index
        proc.setInt(10, pagingSorting.getEndRecordId());// end index
        proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        proc.setInt(12, objDto.getIsUsage());
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            setBlank();
            replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS"));
            objReportsDto = new LogicalSIDataReportDTO();
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));

            objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setParent_name(rs.getString("PARENT_NAME"));//PARENT LINE NAME
            objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//Line Name

            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT"));

            objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {

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

            }

            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            // change
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null) {
                objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//PO Contract Period
            tempDate = rs.getDate("CONTRACTSTARTDATE");
            if (tempDate != null) {
                objReportsDto.setContractStartDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            tempDate = rs.getDate("CONTRACTENDDATE");
            if (tempDate != null) {
                objReportsDto.setContractEndDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            tempDate = rs.getDate("PORECEIVEDATE");
            if (tempDate != null) {
                objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }

            objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));
            objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));

            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));

            objReportsDto.setContactName(VAR_BILL_CON_PER_NAME);//Contact Person Name   
            objReportsDto.setDesignation(VAR_DESIGNATION);//Person Designation   
            objReportsDto.setTelePhoneNo(VAR_TELEPHONENO);//Person Mobile   
            objReportsDto.setEmailId(VAR_EMAIL_ID);//Person Email   
            objReportsDto.setFax(VAR_FAX);//Person Fax   

            //Remrks   
            objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD"));
            objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD"));
            objReportsDto.setLogicalCircuitId(rs.getString("CKTID"));

            if (objDto.getIsUsage() == 0) {
                objReportsDto.setRecordStatus(rs.getString("recordStatus"));

                objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD"));

                objReportsDto.setCountyName(rs.getString("COUNTRY_NAME"));
                objReportsDto.setAddress1(rs.getString("BILL_ADDRESS1"));//billing Address1
                objReportsDto.setAddress2(rs.getString("BILL_ADDRESS2"));//billing Address2   
                objReportsDto.setAddress3(rs.getString("BILL_ADDRESS3"));//billing Address3   
                objReportsDto.setAddress4(rs.getString("BILL_ADDRESS4"));//billing Address4
                objReportsDto.setCityName(rs.getString("CITY_NAME"));//need to add in view
                objReportsDto.setPostalCode(rs.getString("POSTAL_CODE"));//need to add in view
                objReportsDto.setStateName(rs.getString("STATE_NAME"));//need to add in view

                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS"));

                objReportsDto.setFrequencyName(rs.getString("FREQUENCY"));
                objReportsDto.setFrequencyAmt(rs.getString("FREQUENCY_AMT"));
                objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC"));
                objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS"));
                objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH"));

                objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC"));
                objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH"));
                objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS"));
                objReportsDto.setChargeEndDate(rs.getString("END_DATE"));//Charge End Date
                if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) {

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

                }
                objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE"));
                //--Trai Rate
                //--Discount
                //Advance
                //Installment Rate
                objReportsDto.setDnd_Dispatch_But_Not_Delivered(rs.getString("Dnd_Dispatch_But_Not_Delivered"));
                objReportsDto.setDnd_Dispatch_And_Delivered(rs.getString("Dnd_Dispatch_And_Delivered"));
                objReportsDto.setDnd_Disp_Del_Not_Installed(rs.getString("Ddni_Disp_Del_Not_Installed"));
                objReportsDto.setDnd_Disp_Delivered_Installed(rs.getString("Ddni_Disp_Delivered_Installed"));
                objReportsDto.setPoExclude(rs.getString("PO_EXCLUDE"));//Po Valid Exclude

                objReportsDto.setChargeinfoID(rs.getString("CHARGEINFOID"));//need to add in view

                //M6 Order Id   

                //remarks

                //Pk Charges Id   
                //M6 Product Id   
                //Parent Product Id   
                objReportsDto.setBillingInfoID(rs.getInt("CHARGE_HDR_ID"));//Charge Hdr Id   
                //Ib Pk Charges Id   
                //Ib Order Line Id

                //Order Line Si No   
                objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION"));

                //               Active End Date

                objReportsDto.setLst_No(rs.getString("LST_NO"));//Lst No   
                objReportsDto.setLstDate(rs.getString("LST_DATE"));//Lst Date   
                if (rs.getString("LST_DATE") != null && !"".equals(rs.getString("LST_DATE"))) {
                    objReportsDto.setLstDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("LST_DATE").getTime())))
                                    .toUpperCase());

                }
                //Billing Address Type
                //objReportsDto.setAttributeLabel(rs.getString("Attribute_Name"));
                //objReportsDto.setAttributeValue(rs.getString("Attribute_Value"));
                objReportsDto.setStoreName(rs.getString("STORENAME"));
                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setSaleNature(rs.getString("SALENATURE"));
                objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE"));
                objReportsDto.setSaleType(rs.getString("SALETYPE"));
                //Principle Amt   
                //Interest Rate   
                objReportsDto.setWarrantyStartDateLogic(rs.getString("WARRENTY_START_DATE_LOGIC"));
                objReportsDto.setWarrantyPeriodMonths(rs.getString("WARRENTY_PERIOD_MONTHS"));
                objReportsDto.setWarrantyPeriodDays(rs.getString("WARRENTY_PERIOD_DAYS"));
                objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE"));
                if (rs.getString("WARRENTY_START_DATE") != null
                        && !"".equals(rs.getString("WARRENTY_START_DATE"))) {

                    Date date = df.parse(objReportsDto.getWarrantyStartDate());
                    objReportsDto.setWarrantyStartDate((utility.showDate_Report(date)).toUpperCase());

                }
                objReportsDto.setWarrantyEndDateLogic(rs.getString("WARRENTY_END_DATE_LOGIC"));
                objReportsDto.setWarrantyEndPeriodMonths(rs.getString("WARRENTY_END_PERIOD_MONTHS"));//need
                objReportsDto.setWarrantyEndPeriodDays(rs.getString("WARRENTY_END_PERIOD_DAYS"));//need
                objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE"));//need
                if (rs.getString("WARRENTY_END_DATE") != null
                        && !"".equals(rs.getString("WARRENTY_END_DATE"))) {

                    Date date = df.parse(objReportsDto.getWarrantyEndDate());
                    objReportsDto.setWarrantyEndDate((utility.showDate_Report(date)).toUpperCase());

                }
                objReportsDto.setExtndSupportPeriodMonths(rs.getString("EXT_SUPPORT_PERIOD_MONTHS"));
                objReportsDto.setExtndSupportPeriodDays(rs.getString("EXT_SUPPORT_PERIOD_DAYS"));
                objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE"));
                if (rs.getString("EXT_SUPPORT_END_DATE") != null
                        && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) {

                    Date date = df.parse(objReportsDto.getExtSuportEndDate());
                    objReportsDto.setExtSuportEndDate((utility.showDate_Report(date)).toUpperCase());

                }
                objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1"));//Dispatch Address1   
                objReportsDto.setDispatchAddress2(rs.getString("DISP_ADDRESS2"));//Dispatch Address2   
                objReportsDto.setDispatchAddress3(rs.getString("DISP_ADDRESS3"));//Dispatch Address3   
                objReportsDto.setDispatchCityName(rs.getString("DISP_CITY_NAME"));//Dispatch City   
                objReportsDto.setDispatchPostalCode(rs.getString("DISP_POSTAL_CODE"));//Dispatch Postal Code   
                objReportsDto.setDispatchStateName(rs.getString("DISP_STATE_NAME"));//Dispatch State   
                objReportsDto.setDispatchPersonName(rs.getString("DISP_Con_Person_Name"));//Dispatch Conact Person Name   
                objReportsDto.setDispatchPhoneNo(rs.getString("DISP_TELEPHONENO"));//Dispatch Contact Person Mobile   
                objReportsDto.setDispatchLstNumber(rs.getString("DISP_LST_NO"));//Dispatch Lst Number   
                objReportsDto.setDispatchLstDate(rs.getString("DISP_LST_DATE"));//Dispatch Lst Date   
                if (rs.getString("DISP_LST_DATE") != null && !"".equals(rs.getString("DISP_LST_DATE"))) {

                    objReportsDto.setDispatchLstDate(
                            (utility.showDate_Report(new Date(rs.getTimestamp("DISP_LST_DATE").getTime())))
                                    .toUpperCase());

                }
                //Dispatch Address Type   
                //New Service List Id   
                //New Crm Order Id   

            }

            if (objDto.getIsUsage() == 1) {

                objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS"));
                objReportsDto.setNeworder_remarks(rs.getString("NEWORDER_REMARKS"));
                objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO"));

                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
                objReportsDto.setCountyName(VAR_BCP_COUNTRY_NAME);
                objReportsDto.setAddress1(VAR_BILL_ADDRESS1);//billing Address1
                objReportsDto.setAddress2(VAR_BILL_ADDRESS2);//billing Address2   
                objReportsDto.setAddress3(VAR_BILL_ADDRESS3);//billing Address3   
                objReportsDto.setAddress4(VAR_BILL_ADDRESS4);//billing Address4
                objReportsDto.setCityName(VAR_BCP_CITY_NAME);//need to add in view
                objReportsDto.setPostalCode(VAR_BCP_PIN);//need to add in view
                objReportsDto.setStateName(VAR_BCP_STATE_NAME);//need to add in view

                objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));
                objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID"));
                objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));

                ComponentsDto dto = new ComponentsDto();
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC"));
                dto.setStartDateDays(rs.getInt("COMP_START_DAYS"));
                dto.setStartDateMonth(rs.getInt("COMP_START_MONTHS"));
                dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC"));
                dto.setEndDateDays(rs.getInt("COMP_END_DAYS"));
                dto.setEndDateMonth(rs.getInt("COMP_END_MONTHS"));

                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("SYSTEM_END_DATE"));
                if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) {

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

                }
                objReportsDto.setComponentDto(dto);
            }

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

---

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

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

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

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

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

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

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

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

        proc.setString(6, pagingSorting.getSortByColumn());// columnName
        proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(8, pagingSorting.getStartRecordId());// start index
        proc.setInt(9, pagingSorting.getEndRecordId());// end index
        proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        // index
        proc.setInt(11, objDto.getIsUsage());
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new RestPendingLineReportDTO();
            objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setPartyNo(rs.getInt("PARTY_NO"));
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            tempDate = rs.getDate("ORDERDATE");
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            /*          objReportsDto.setPoDate(rs.getString("PODATE"));
                      if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE")))
                        {
                        Date date=df.parse(objReportsDto.getPoDate());
                        objReportsDto.setPoDate((Utility.showDate_Report(date)).toUpperCase());
                        }*/
            tempDate = rs.getDate("PODATE");
            objReportsDto.setPoDate(rs.getString("PODATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setPoDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            /*objReportsDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE")))
               {
               Date date=df.parse(objReportsDto.getCustPoDate());
               objReportsDto.setCustPoDate((Utility.showDate_Report(date)).toUpperCase());
               }*/
            tempDate = rs.getDate("CUSTPODATE");
            objReportsDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setRate_code(rs.getString("RATECODE"));
            objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA"));
            objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS"));
            objReportsDto.setLink_type(rs.getString("LINK_TYPE"));
            objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE"));
            objReportsDto.setUom(rs.getString("UOM"));
            objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER"));
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objReportsDto.setPoNumber(rs.getInt("PONUMBER"));
            //objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION"));
            setBlank();
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION);
            //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            objReportsDto.setSeclocation(VAR_SECONDARYLOCATION);

            /*objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE"));
            if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE")))
            {
               Date date=df.parse(objReportsDto.getRfs_date());
               objReportsDto.setRfs_date((Utility.showDate_Report(date)).toUpperCase());
                    
            }*/
            tempDate = rs.getDate("SERVICE_RFS_DATE");
            objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objReportsDto.setRfs_date((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setProductName(rs.getString("PRODUCTNAME"));
            objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setMocn_no(rs.getString("MOCN_NO"));
            objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objReportsDto.setLogicalCircuitId(rs.getString("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setCkt_id(rs.getString("CKTID"));
            objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setDemoType(rs.getString("Demo_Type"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setDistance(rs.getString("DISTANCE"));
            objReportsDto.setStageName(rs.getString("ORDERSTAGE"));
            objReportsDto.setOrder_type(rs.getString("ORDERTYPE"));
            objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));

            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT"));
            objReportsDto.setParty_id(rs.getInt("PARTY_ID"));
            objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO"));
            objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID"));
            objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT"));

            if (objDto.getIsUsage() == 1) {
                objReportsDto.setCancelBy(rs.getString("CANCEL_BY"));
                //objReportsDto.setCanceldate(rs.getString("CANCELDATE"));

                tempDate = rs.getDate("CANCEL_DATE");
                if (tempDate != null) {
                    objReportsDto.setCanceldate((Utility.showDate_Report(tempDate)).toUpperCase());
                }

                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
                objReportsDto.setM6_prod_id(rs.getString("CHILDSPECID"));
                objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));
                objReportsDto.setServiceproductid(rs.getInt("Order_Line_Id"));
                objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
                objReportsDto.setCancelServiceReason(rs.getString("CANCEL_RESION"));

                ComponentsDto dto = new ComponentsDto();
                objReportsDto.setRegionName(rs.getString("REGION"));
                objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
                objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER"));
                objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE"));
                if (rs.getString("COPC_APPROVED_DATE") != null
                        && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                    objReportsDto.setCopcapprovaldate(
                            (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                    .toUpperCase());
                }
                /*objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE"));
                if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE")))
                   {
                   Date date=df.parse(objReportsDto.getPoReceiveDate());
                   objReportsDto.setPoReceiveDate((Utility.showDate_Report(date)).toUpperCase());
                   }*/
                tempDate = rs.getDate("PORECEIVEDATE");
                objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE"));
                if (tempDate != null && !"".equals(tempDate)) {
                    objReportsDto.setPoReceiveDate((utility.showDate_Report(tempDate)).toUpperCase());
                }
                //objReportsDto.setBilling_address(rs.getString("BILLING_ADDRESS"));
                replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS"));
                objReportsDto.setBilling_address(VAR_BILLING_ADDRESS);
                objReportsDto.setLineno(rs.getInt("Order_Line_Id"));
                objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS"));
                objReportsDto.setLocation_to(rs.getString("TO_ADDRESS"));
                objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE"));
                objReportsDto.setChild_act_no(rs.getString("Child_Account_Number"));
                objReportsDto.setCrm_productname(rs.getString("SERVICEDETDESCRIPTION"));

                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID"));
                dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));
                dto.setComponentType(rs.getString("COMPONENT_TYPE"));
                dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));
                dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO"));
                dto.setStartDate(rs.getString("SYSTEM_START_DATE"));
                if (rs.getString("SYSTEM_START_DATE") != null
                        && !"".equalsIgnoreCase(rs.getString("SYSTEM_START_DATE"))) {
                    Date date = df.parse(dto.getStartDate());
                    dto.setStartDate((Utility.showDate_Report(date)).toUpperCase());
                }
                dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS"));
                dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS"));
                //dto.setComponentFXStatus(rs.getString("FX_STATUS"));
                // <!--GlobalDataBillingEfficiency BFR5  -->
                objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON"));

                objReportsDto.setComponentDto(dto);
            } else {
                objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD"));
                objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID"));
                objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID"));
                objReportsDto.setServiceId(rs.getInt("SERVICEID"));
                objReportsDto.setPoAmountSum(rs.getLong("ORDERAMOUNT"));
                objReportsDto.setRegionName(rs.getString("REGIONNAME"));
                objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON"));
                objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO"));
                objReportsDto.setBisource(rs.getString("BISOURCE"));
                objReportsDto.setServiceStage(rs.getString("SERVICESTAGE"));
                objReportsDto.setLocation_from(rs.getString("FROM_LOCATION"));
                objReportsDto.setLocation_to(rs.getString("TO_LOCATION"));
                objReportsDto.setLinename(rs.getString("LINENAME"));
                objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME"));
                objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID"));
                objReportsDto.setAccountManager(rs.getString("ACTMNAME"));
                objReportsDto.setProjectManager(rs.getString("PMNAME"));
                objReportsDto.setAmapprovaldate(rs.getString("AM_APPROVAL_DATE"));
                if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                    objReportsDto.setAmapprovaldate(
                            (Utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime())))
                                    .toUpperCase());
                }
                objReportsDto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE"));
                if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                    objReportsDto.setPmapprovaldate(
                            (Utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime())))
                                    .toUpperCase());
                }

                objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVAL_DATE"));
                if (rs.getString("COPC_APPROVAL_DATE") != null
                        && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                    objReportsDto.setCopcApproveDate(
                            (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                    .toUpperCase());
                }
                objReportsDto.setOrderDate(rs.getString("ORDERCREATEDATE"));
                if (rs.getString("ORDERCREATEDATE") != null && !"".equals(rs.getString("ORDERCREATEDATE"))) {
                    Date date = df.parse(objReportsDto.getOrderDate());
                    objReportsDto.setOrderDate((Utility.showDate_Report(date)).toUpperCase());
                }
                objReportsDto.setPoReceiveDate(rs.getString("CUSTPORECDATE"));
                if (rs.getString("CUSTPORECDATE") != null && !"".equals(rs.getString("CUSTPORECDATE"))) {
                    Date date = df.parse(objReportsDto.getPoReceiveDate());
                    objReportsDto.setPoReceiveDate((Utility.showDate_Report(date)).toUpperCase());
                }

                objReportsDto.setChargeEndDate(rs.getString("CHARGE_START_DATE"));
                if (rs.getString("CHARGE_START_DATE") != null
                        && !"".equals(rs.getString("CHARGE_START_DATE"))) {
                    Date date = df.parse(objReportsDto.getChargeEndDate());
                    objReportsDto.setChargeEndDate((Utility.showDate_Report(date)).toUpperCase());
                }
                objReportsDto.setLineno(rs.getInt("LINEITEMNO"));
                objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID"));
                objReportsDto.setAddress1(rs.getString("ADDRESS"));
                objReportsDto.setCancelflag(rs.getString("CANCELBY"));
                objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION"));
                objReportsDto.setCanceldate(rs.getString("CANCELDATE"));

                objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));
                objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPEID"));
                objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS"));
                objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setFx_status(rs.getString("FX_STATUS"));
                objReportsDto.setFx_sd_status(rs.getString("Fx_St_Chg_Status"));
                objReportsDto.setFx_ed_status(rs.getString("Fx_Ed_Chg_Status"));
                objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus"));
                objReportsDto.setFrequencyName(rs.getString("FREQUENCY"));
                objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setTokenno(rs.getString("TOKENNO"));
                objReportsDto.setSaleNature(rs.getString("SALENATURENAME"));
                objReportsDto.setSaleType(rs.getString("SALETYPENAME"));
                objReportsDto.setRatio(rs.getString("RATIO"));
                objReportsDto.setAnnitation(rs.getString("ANNOTATION"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setStorename(rs.getString("STORENAME"));
                objReportsDto.setBill_period(rs.getString("BILL_PERIOD"));
                if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID"))
                        && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) {
                    String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"),
                            rs.getString("ENTITYID"));
                    objReportsDto.setBill_period(tBillPeriod);

                }
                objReportsDto.setChargeAmount(rs.getDouble("INV_AMT"));
                objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT"));
                objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO"));
                objReportsDto.setLb_pk_charge_id(rs.getString("LB_PK_CHARGE_ID"));
                objReportsDto.setChargeinfoID(rs.getString("PK_CHARGE_ID"));
                objReportsDto.setAnnual_rate(rs.getInt("ANNUAL_RATE"));
                // <!--GlobalDataBillingEfficiency BFR5  -->
                objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON"));
            }
            //[005] Start
            objReportsDto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY"));
            objReportsDto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY"));
            objReportsDto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE"));
            objReportsDto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE"));
            objReportsDto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME"));
            objReportsDto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER"));
            objReportsDto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID"));
            //[005] End

            //[006] Start
            objReportsDto.setStandardReason(rs.getString("STANDARDREASON"));
            //[006] End

            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);//nagarjuna
        //ex.printStackTrace();   
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

---

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

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

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

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

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

        if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) {
            proc.setInt(5, Integer.parseInt(objDto.getOrdersubtype().trim()));
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }

        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(6, objDto.getVerticalDetails().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));
        proc.setInt(12, objDto.getIsUsage());
        if (objDto.getSrrequest() != null && !"".equals(objDto.getSrrequest())) {
            proc.setString(13, objDto.getSrrequest());
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new DisconnectChangeOrdeReportDTO();
            //[270513]Start : Added by Ashutosh for Billing Address
            setBlank();
            //replaceSeperator("BILLING_LOCATION",rs.getString("BILLING_ADDRESS"));
            replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION"));
            replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION"));
            //[270513]Start
            objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
            objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO"));
            objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD"));
            objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            objReportsDto.setBillingMode(rs.getString("BILLINGMODE"));
            objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingformat(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE"));
            objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL"));
            objReportsDto.setBillingLevelId(rs.getInt("BILLING_LEVEL_NO"));
            objReportsDto.setPoNumber(rs.getInt("PONUMBER"));
            tempDate = rs.getDate("PODATE");
            if (tempDate != null) {
                objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setParty(rs.getString("PARTYNAME"));
            objReportsDto.setPm_pro_date(rs.getString("Pm_Prov_Date"));
            objReportsDto.setLocDate(rs.getString("LOCDATE"));
            if (!(rs.getString("LOCDATE") == null || rs.getString("LOCDATE") == "")) {
                Date date = df.parse(objReportsDto.getLocDate());
                objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));

            objReportsDto.setChild_act_no(rs.getString("Child_Account_Number"));
            objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus"));
            tempDate = rs.getDate("ORDERDATE");
            if (tempDate != null) {
                objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setOrder_type(rs.getString("ORDERTYPE"));
            objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC"));
            ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE");
            if (ts != null) {
                tempDate = new Date(ts.getTime());
                objReportsDto.setBillingtrigger_createdate(Utility.showDate_Report(tempDate).toUpperCase());
            }
            ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE");
            if (ts != null) {
                tempDate = new Date(ts.getTime());
                objReportsDto.setBillingtrigger_createdate((utility.showDate_Report(tempDate)).toUpperCase());

            }
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objReportsDto.setOrderStage(rs.getString("STAGE"));
            objReportsDto.setActmgrname(rs.getString("ACCOUNTMANAGER"));
            objReportsDto.setPrjmngname(rs.getString("PROJECTMANAGER"));
            tempDate = rs.getDate("ORDERDATE");
            if (tempDate != null) {
                objReportsDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            tempDate = rs.getDate("SERVICE_RFS_DATE");
            if (!(rs.getString("SERVICE_RFS_DATE") == null || rs.getString("SERVICE_RFS_DATE") == "")) {
                objReportsDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            tempDate = rs.getDate("PORECEIVEDATE");
            if (tempDate != null) {
                objReportsDto.setCust_po_rec_date((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setRegion(rs.getString("REGION"));
            objReportsDto.setDemo(rs.getString("Demo_Type"));
            objReportsDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS"));
            objReportsDto.setOrderStageDescription(rs.getString("STAGE"));
            objReportsDto.setMocn_no(rs.getString("MOCN_NO"));
            objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS"));
            objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE"));
            if (!(rs.getString("DISCONNECTION_RECEIVE_DATE") == null
                    || rs.getString("DISCONNECTION_RECEIVE_DATE") == "")) {

                //Date date=df.parse(objReportsDto.getRequest_rec_date());
                //objReportsDto.setRequest_rec_date((utility.showDate_Report(date)).toUpperCase());

            }
            objReportsDto.setLineno(rs.getInt("Order_Line_Id"));

            objReportsDto.setOrdermonth(rs.getString("ORDERMONTH"));
            objReportsDto.setCkt_id(rs.getString("CKTID"));
            objReportsDto.setStandard_reason(rs.getString("STANDARDREASON"));
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objReportsDto.setBandwidth_att(rs.getString("BANDWIDTH_ATT"));
            objReportsDto.setUom(rs.getString("UOM"));
            objReportsDto.setRate_code(rs.getString("RATECODE"));
            objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA"));
            objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS"));
            objReportsDto.setChargeable_Distance(rs.getString("DISTANCE"));
            objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER"));
            objReportsDto.setLink_type(rs.getString("LINK_TYPE"));
            objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE"));
            objReportsDto.setProductName(rs.getString("SERVICEDETDESCRIPTION"));
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null) {
                objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase());
            }
            objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objReportsDto.setLocno(rs.getString("LOCNO"));
            objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION);
            objReportsDto.setProdAlisName(rs.getString("PRODUCTNAME"));
            objReportsDto.setRatio(rs.getString("RATIO"));
            objReportsDto.setSeclocation(VAR_SECONDARYLOCATION);
            objReportsDto.setSub_linename(rs.getString("SUBPRODUCTNAME"));
            objReportsDto.setOrderNo(rs.getString("ORDERNO"));
            objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));
            objReportsDto.setAmt(rs.getLong("CUST_TOT_PO_AMT"));
            objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT"));
            objReportsDto.setAdvance(rs.getString("ADVANCE"));
            objReportsDto.setContractMonths(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD"));
            objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD"));
            objReportsDto.setPeriodsInMonths(rs.getString("PERIODS_IN_MONTH"));
            objReportsDto.setTotalPoAmt(rs.getString("CUST_TOT_PO_AMT"));
            objReportsDto.setParty_id(rs.getInt("PARTY_NO"));
            objReportsDto.setCust_act_id(rs.getString("CUSTACCOUNTID"));
            objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID"));
            objReportsDto.setM6_order_id(rs.getString("M6ORDERNO"));
            objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo"));
            objReportsDto.setM6cktid(rs.getString("M6_CKTID"));
            objReportsDto.setBilling_location_from(rs.getString("FROM_ADDRESS"));
            objReportsDto.setBilling_location_to(rs.getString("TO_ADDRESS"));

            if (objDto.getIsUsage() == 1) {
                objReportsDto.setOrderStage(rs.getString("ORDERSTAGE"));
                objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
                objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
                objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME"));
                objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE"));
                if (rs.getString("BILLINGTRIGGERDATE") != null
                        && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {

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

                }
                objReportsDto.setLineno(rs.getInt("Order_Line_Id"));

                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                ComponentsDto dto = new ComponentsDto();
                dto.setComponentStatus(rs.getString("COMPONENT_STATUS"));
                dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC"));
                dto.setStartDate(rs.getString("COMPONENT_START_DATE"));
                if (rs.getString("COMPONENT_START_DATE") != null
                        && !"".equals(rs.getString("COMPONENT_START_DATE"))) {

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

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

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

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

                objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objReportsDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objReportsDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objReportsDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objReportsDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE"));
                ts = rs.getTimestamp("COPC_APPROVED_DATE");
                if (ts != null) {
                    tempDate = new Date(ts.getTime());
                    objReportsDto.setCopcapprovaldate((utility.showDate_Report(tempDate)).toUpperCase());

                }
                objReportsDto.setSrno(rs.getString("SR_NO"));
                objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));

                objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE"));
                objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE"));

                objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)"));
                objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)"));
                objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS"));
                objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY"));
                objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR"));

                /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/
                objReportsDto.setComponentDto(dto);
            } else {
                objReportsDto.setOrderStage(rs.getString("STAGE"));
                objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE"));
                objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
                objReportsDto.setFrequencyName(rs.getString("FREQUENCY"));
                if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID"))
                        && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) {
                    String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"),
                            rs.getString("ENTITYID"));
                    objReportsDto.setBill_period(tBillPeriod);
                }
                objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC"));
                objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC"));
                objReportsDto.setChargeEndDate(rs.getString("END_DATE"));
                if (!(rs.getString("END_DATE") == null || rs.getString("END_DATE") == "")) {

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

                }

                objReportsDto.setStartDate(rs.getString("START_DATE"));
                if (!(rs.getString("START_DATE") == null || rs.getString("START_DATE") == "")) {

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

                }
                objReportsDto.setStore(rs.getString("STORENAME"));
                objReportsDto.setHardwareType(rs.getString("HARDWARETYPENAME"));
                objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE"));
                objReportsDto.setNature_sale(rs.getString("SALENATURE"));
                objReportsDto.setType_sale(rs.getString("SALETYPE"));
                objReportsDto.setAnnitation(rs.getString("ANNOTATION"));
                objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));
                objReportsDto.setChallenno(rs.getString("CHALLEN_NO"));
                objReportsDto.setChallendate(rs.getString("CHALLEN_DATE"));
                if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) {
                    Date date = df.parse(objReportsDto.getChallendate());
                    objReportsDto.setChallendate((utility.showDate_Report(date)).toUpperCase());
                }
                objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG"));
                objReportsDto.setChargeTypeID(rs.getInt("CHARGETYPEID"));
                objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS"));
                objReportsDto.setSrno(rs.getString("SR_NO"));
                objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1"));
                objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));
                objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID"));
                objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));
                objReportsDto.setInstallation_addressaa1(rs.getString("INSTALLEMENT"));
                objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS"));
                objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH"));
                objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS"));
                objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH"));
                objReportsDto.setAnnualRate(rs.getString("ANNUAL_RATE"));
                objReportsDto.setPk_charge_id(rs.getString("CHARGEINFOID"));
                objReportsDto.setBusiness_serial_no(rs.getString("Business_No"));
                /*20151224-R1-021980 - Changes in Disconnection Report || ROM 
                 *  CHanged by :satish Start*/
                objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE"));

                objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE"));

                objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)"));
                objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)"));
                objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS"));
                objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY"));
                objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR"));

                /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/
            }

            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);//nagarjuna
        //ex.printStackTrace();   
    } 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;
}

---

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