Example usage for java.sql CallableStatement setNull

List of usage examples for java.sql CallableStatement setNull

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

Introduction

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

Prototype

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

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

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

/**
 * // w  w  w .  j  a  va2s  .c om
 * @param objDto
 * @return
 * @throws Exception
 */
public ArrayList<ReportsDto> viewOrderReportDetails(ReportsDto objDto) throws Exception {
    //Nagarjuna
    String methodName = "viewOrderReportDetails", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna

    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlOrderReportDetail);

        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        //Company Name
        //proc.setNull(4, java.sql.Types.VARCHAR);
        // Logical Si no
        if (objDto.getDemo() != null && !"".equals(objDto.getDemo())) {
            proc.setString(4, objDto.getDemo().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

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

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

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

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

            objDto = new ReportsDto();
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objDto.setPoAmounts(rs.getDouble("POAMOUNT"));
            objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setBillingPODate(rs.getString("START_DATE"));
            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {

                Date date = df.parse(objDto.getBillingPODate());
                objDto.setBillingPODate((utility.showDate_Report(date)).toUpperCase());

            }
            objDto.setServiceDetDescription(rs.getString("SERVICESTAGE"));
            objDto.setOrderLineNumber(rs.getInt("SERVICEPRODUCTID"));
            objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
            objDto.setCancelflag(rs.getString("CANCELFLAG"));
            objDto.setProvisionBandWidth(rs.getString("BANDWIDTH"));
            objDto.setUom(rs.getString("UOM"));
            objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setStoreName(rs.getString("STORENAME"));
            objDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setCategoryOfOrder(rs.getString("ORDERCATEGORY"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setCompanyName(rs.getString("COMPANYNAME"));
            objDto.setOrderDate(rs.getString("ORDERCREATIONDATE"));
            if (rs.getString("ORDERCREATIONDATE") != null && !"".equals(rs.getString("ORDERCREATIONDATE"))) {

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

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

                Date date = df.parse(objDto.getCustomerRfsDate());
                objDto.setCustomerRfsDate((utility.showDate_Report(date)).toUpperCase().toString());

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

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

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

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

            }
            objDto.setCustomerPoNumber(rs.getString("CUSTPONUMBER"));
            objDto.setCyclicNonCyclic(rs.getString("CYCLIC_NONCYCLIC"));
            objDto.setChallenno(rs.getString("CHALLEN_NO"));
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setFromSite(rs.getString("PRIMARYLOCATION"));
            objDto.setToSite(rs.getString("SECONDARYLOCATION"));
            objDto.setItemQuantity(1);
            objDto.setKmsDistance(rs.getString("DISTANCE"));
            objDto.setChargeAmount(rs.getDouble("LINEITEMAMOUNT"));
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION"));
            objDto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

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

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

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

            }
            objDto.setOrderTotal(rs.getDouble("POAMOUNT"));
            objDto.setOrderEntryDate(rs.getString("ORDERCREATIONDATE"));
            if (rs.getString("ORDERCREATIONDATE") != null && !"".equals(rs.getString("ORDERCREATIONDATE"))) {

                Date date = df.parse(objDto.getOrderEntryDate());
                objDto.setOrderEntryDate((utility.showDate_Report(date)).toUpperCase().toString());

            }

            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setLicenceCoName(rs.getString("LCOMPANYNAME"));
            objDto.setLogicalCircuitNumber(rs.getString("LOGICAL_CIRCUITID"));
            objDto.setOrderType(rs.getString("ORDERCATEGORY"));
            objDto.setPaymentTerm(rs.getString("PAYMENTTERM"));
            objDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setOldLineitemAmount("");
            objDto.setDemoType(rs.getString("DEMO_TYPE"));
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderStageDescription(rs.getString("STAGE"));
            objDto.setServiceStageDescription(rs.getString("SERVICE_STAGE"));
            objDto.setChargeEndDate(rs.getString("END_DATE"));
            if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) {

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

            }
            objDto.setEndDateLogic(rs.getString("ENDDATELOGIC"));
            objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS"));
            objDto.setRemarks(rs.getString("REMARKS"));
            objDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE"));
            objDto.setOsp(rs.getString("OSP"));
            if (objDto.getIsUsage() == 1) {
                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"));
            }

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

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

    return objUserList;
}

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

public ArrayList<ReportsDto> getTelemediaOrderList(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "getTelemediaOrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;//from ww  w.j a v a  2s .com
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetTelemediaOrderReport);
        if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) {
            proc.setString(1, objDto.getFromCopcApprovedDate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) {
            proc.setString(2, objDto.getToCopcApprovedDate().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));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setOrderNo(rs.getString("ORDERNO"));
            objReportsDto.setCrmAccountNoString(rs.getString("PARENT_ACCOUNT_NUMBER"));
            objReportsDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER"));
            objReportsDto.setCopcApproveDate(Utility.showDate_Report(rs.getString("COPC_APPROVED_DATE")));
            if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }

            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

/**
 * Create a Report to generate LEPM Order Detail Report
         /*  w  w w .j  a v  a  2 s  . c  om*/
 * @param obj   a DTO which consist all the search parameters
 * @return      a ArrayList of dto which consist all the data of reports 
 * @exception   Sql Exception
 *            
 */
public ArrayList<ReportsDto> viewLEPMOrderDetailReport(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewLEPMOrderDetailReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    int countFlag = 0;
    ReportsDto objReportsDto = null;
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlLEPMOrderDetailReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getCopcApproveDate() != null && !"".equals(objDto.getCopcApproveDate())) {
            proc.setString(2, objDto.getCopcApproveDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }

        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(3, objDto.getVerticalDetails().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);
        }

        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));// end
        rs = proc.executeQuery();

        while (rs.next() != false) {
            objDto = new ReportsDto();
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setServiceId(rs.getInt("SERVICENO"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objDto.setPrimarylocation(rs.getString("FROM_SITE"));
            objDto.setSeclocation(rs.getString("TO_SITE"));
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            objDto.setPrjmngname(rs.getString("PMNAME"));
            objDto.setPrjmgremail(rs.getString("PMEMAIL"));
            objDto.setActmngname(rs.getString("ACTMNAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {

                objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase());

            }
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

                objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase());

            }
            objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objDto.setPmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objDto.setAmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE"));
            if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) {
                objDto.setNio_approve_date(
                        (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE"));
            objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE"));
            objDto.setRfs_date(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {

                objDto.setRfs_date((Utility.showDate_Report((rs.getTimestamp("RFS_DATE")))).toUpperCase());

            }
            objDto.setOrdercategory(rs.getString("ORDERCATEGORY"));
            objDto.setOrderStatus(rs.getString("STATUS"));
            objDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME"));
            objDto.setChargeName(rs.getString("CHARGE_NAME"));
            objDto.setChargeinfoID(rs.getString("CHARGEINFOID"));
            objDto.setServiceProductID(rs.getInt("LINENO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setEntity(rs.getString("COMPANYNAME"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setFrequencyName(rs.getString("PAYMENTTERM"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            if ("NULL".equals(rs.getString("SERVICETYPE"))) {
                objDto.setServiceType("");
            } else {
                objDto.setServiceType(rs.getString("SERVICETYPE"));
            }
            objDto.setUom(rs.getString("UOM"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setFrom_city(rs.getString("FROM_CITY"));
            objDto.setTo_city(rs.getString("TO_CITY"));
            objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL"));
            objDto.setOldlineamt(rs.getString("OLD_LINE_AMT"));
            objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD"));
            objDto.setRatio(rs.getString("RATIO"));
            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setDistance(rs.getString("DISTANCE"));
            objDto.setAccountManager(rs.getString("ACTMEMAIL"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
            objDto.setPoAmount(rs.getString("POAMOUNT"));
            objDto.setBisource(rs.getString("BISOURCE"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));
            objDto.setParent_name(rs.getString("PARENTNAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);

    }

    catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.freeConnection(conn);

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

}

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

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

        if (objDto.getDate_of_inst() != null && !"".equals(objDto.getDate_of_inst())) {
            proc.setString(1, objDto.getDate_of_inst().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);
        }

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

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

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

        if (objDto.getLinename() != null && !"".equals(objDto.getLinename())) {
            proc.setString(6, objDto.getLinename().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));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objReportsDto.setOrderNumber(rs.getInt("CRM_ORDER_MOCN_NO"));
            objReportsDto.setCrm_service_opms_id(rs.getString("CRM_SERVICE_OPMS_ID"));
            objReportsDto.setServiceName(rs.getString("SERVICETYPE"));
            objReportsDto.setInstallation_addressaa1(rs.getString("INSTALLATION_ADDRESSA1"));
            objReportsDto.setInstallation_addressaa2(rs.getString("INSTALLATION_ADDRESSA2"));
            objReportsDto.setInstallation_addressaa3(rs.getString("INSTALLATION_ADDRESSA3"));
            objReportsDto.setFrom_city(rs.getString("FROM_CITY"));
            objReportsDto.setFrom_state(rs.getString("FROM_STATE"));
            objReportsDto.setFrom_country(rs.getString("FROM_COUNTRY"));
            objReportsDto.setInstallation_addressab1(rs.getString("INSTALLATION_ADDRESSB1"));
            objReportsDto.setInstallation_addressab2(rs.getString("INSTALLATION_ADDRESSB2"));
            objReportsDto.setInstallation_addressab3(rs.getString("INSTALLATION_ADDRESSB3"));
            objReportsDto.setTo_city(rs.getString("TO_CITY"));
            objReportsDto.setTo_state(rs.getString("TO_STATE"));
            objReportsDto.setTo_country(rs.getString("TO_COUNTRY"));
            objReportsDto.setDate_of_inst(rs.getString("DATE_OF_INST"));
            objReportsDto.setDate_of_act(rs.getString("DATE_OF_ACT"));
            if (rs.getString("DATE_OF_ACT") != null && !"".equals(rs.getString("DATE_OF_ACT"))) {
                Date date = df.parse(objReportsDto.getDate_of_act());
                objReportsDto.setDate_of_act((utility.showDate_Report(date)).toUpperCase());
            }
            objReportsDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objReportsDto.setUom(rs.getString("UOM"));
            objReportsDto.setParent_name(rs.getString("PARENT_PRODUCT"));
            objReportsDto.setParent_circuit(rs.getString("PARENT_CIRCUIT"));
            objReportsDto.setLob(rs.getString("LOB"));
            objReportsDto.setCircle(rs.getString("CIRCLE"));
            objReportsDto.setZone(rs.getString("ZONE"));
            objReportsDto.setLocation_from(rs.getString("SUPPORT_LOCATION_A"));
            objReportsDto.setLocation_to(rs.getString("SUPPORT_LOCATION_B"));
            objReportsDto.setCommited_sla(rs.getString("COMMITED_SLA"));
            objReportsDto.setHub_location(rs.getString("UB_LOCATION"));
            objReportsDto.setPlatform(rs.getString("PLATFORM"));
            objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setSi_id(rs.getInt("SIID"));
            objReportsDto.setIpls(rs.getString("IPLC"));
            objReportsDto.setManaged_yes_no(rs.getString("MANAGED_YES_NO"));
            objReportsDto.setActmngname(rs.getString("ACTMNAME"));
            objReportsDto.setPrjmngname(rs.getString("PMNAME"));
            objReportsDto.setTl(rs.getString("TL"));
            objReportsDto.setService_provider(rs.getString("LAST_MILE_PROVIDER"));

            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<ReportsDto> viewPerformanceSummaryReport(ReportsDto objDto) {
    //Nagarjuna/*from w w  w  .  j a va 2 s.  co m*/
    String methodName = "viewPerformanceSummaryReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetPerformanceSummaryReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(4, objDto.getFromOrderNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }
        if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) {
            proc.setInt(5, objDto.getToOrderNo());
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }
        if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) {
            proc.setInt(6, objDto.getFromAccountNo());
        } else {
            proc.setNull(6, java.sql.Types.BIGINT);
        }
        if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) {
            proc.setInt(7, objDto.getToAccountNo());
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(8, pagingSorting.getSortByColumn());// columnName
        proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(10, pagingSorting.getStartRecordId());// start index
        proc.setInt(11, pagingSorting.getEndRecordId());// end index
        proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(13, objDto.getOsp().trim());
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            //objReportsDto.setServiceId(rs.getInt("SERVICEID"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setZoneName(rs.getString("ZONE"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            //objReportsDto.setAccountId(rs.getLong("ACCOUNTID"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objReportsDto.setIndustrySegment(rs.getString("INDUSTRYSEGMENT"));

            //objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setOrder_type(rs.getString("DEMO_TYPE"));
            objReportsDto.setSubChangeTypeName(rs.getString("ORDER_SUBTYPE"));
            objReportsDto.setOrderStage(rs.getString("STAGE"));
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

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

            }

            //objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE"));
            objReportsDto.setPoAmountSum(rs.getLong("POAMOUNT"));
            objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date
            if (rs.getString("ORDER_APPROVED_DATE") != null
                    && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setPublishedDate(rs.getString("Published_Date"));
            if (rs.getString("Published_Date") != null && !"".equals(rs.getString("Published_Date"))) {
                objReportsDto.setPublishedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("Published_Date").getTime())))
                                .toUpperCase());
            }
            //objReportsDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            objReportsDto.setDayInAM(rs.getString("DAYS_IN_AM"));
            objReportsDto.setDayInPM(rs.getString("DAYS_IN_PM"));
            objReportsDto.setDayInCOPC(rs.getString("DAYS_IN_COPC"));
            objReportsDto.setDayInSED(rs.getString("DAYS_IN_SED"));
            objReportsDto.setTotalDays(rs.getString("TOTAL_DAYS"));
            objReportsDto.setOsp(rs.getString("OSP"));
            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<ReportsDto> viewPendingServicesReport(ReportsDto objDto) {
    //Nagarjuna//from w  ww  .  j  ava 2s  .co m
    String methodName = "viewPendingServicesReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetPendingServicesReport);
        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            proc.setString(2, objDto.getFromDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            proc.setString(3, objDto.getToDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) {
            proc.setInt(4, objDto.getFromOrderNo());
        } else {
            proc.setNull(4, java.sql.Types.BIGINT);
        }
        if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) {
            proc.setInt(5, objDto.getToOrderNo());
        } else {
            proc.setNull(5, java.sql.Types.BIGINT);
        }
        if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) {
            proc.setInt(6, objDto.getFromAccountNo());
        } else {
            proc.setNull(6, java.sql.Types.BIGINT);
        }
        if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) {
            proc.setInt(7, objDto.getToAccountNo());
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(8, pagingSorting.getSortByColumn());// columnName
        proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(10, pagingSorting.getStartRecordId());// start index
        proc.setInt(11, pagingSorting.getEndRecordId());// end index
        proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {
                objReportsDto.setOrderDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setAmApproveDate(rs.getString("AM_APPROVED_DATE"));
            if (rs.getString("AM_APPROVED_DATE") != null && !"".equals(rs.getString("AM_APPROVED_DATE"))) {
                objReportsDto.setAmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setAmName(rs.getString("AM_APPROVED_BY"));
            objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objReportsDto.setPmApproveDate(rs.getString("PM_APPROVED_DATE"));
            if (rs.getString("PM_APPROVED_DATE") != null && !"".equals(rs.getString("PM_APPROVED_DATE"))) {
                objReportsDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setPmName(rs.getString("PM_APPROVED_BY"));
            objReportsDto.setCopcApproveDate(rs.getString("FINAL_APPROVED_DATE"));//Copc date
            if (rs.getString("FINAL_APPROVED_DATE") != null
                    && !"".equals(rs.getString("FINAL_APPROVED_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("FINAL_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcName(rs.getString("COPC_APPROVED_BY"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setOrderStage(rs.getString("APPROVED_STATUS"));
            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setM6OrderDate(rs.getString("M6_ORDER_DATE"));
            if (rs.getString("M6_ORDER_DATE") != null && !"".equals(rs.getString("M6_ORDER_DATE"))) {
                objReportsDto.setM6OrderDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("M6_ORDER_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setBillingStatus(rs.getString("BILLING_STATUS"));
            objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE"));
            if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) {
                objReportsDto.setPublishedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setEffStartDate(rs.getString("EFFECTIVE_START_DATE"));
            if (rs.getString("EFFECTIVE_START_DATE") != null
                    && !"".equals(rs.getString("EFFECTIVE_START_DATE"))) {
                objReportsDto.setEffStartDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("EFFECTIVE_START_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setOrderStatus(rs.getString("ORDER_STATUS"));
            objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objReportsDto.setCircuitStatus(rs.getString("CIRCUIT_STATUS"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setZoneName(rs.getString("ZONE"));
            objReportsDto.setStandardReason(rs.getString("STANDARD_REASON"));
            objReportsDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setServiceIdString(rs.getString("SERVICEID"));
            objReportsDto.setAccountId(rs.getLong("ACCOUNTID"));
            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<OBValueReportDTO> viewOBValueReport_Usage(OBValueReportDTO objDto) throws Exception {

    String methodName = "viewOBValueReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    ArrayList<OBValueReportDTO> objUserList = new ArrayList<OBValueReportDTO>();
    Connection conn = null;/*from www. j  ava  2s  .c  o m*/
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    OBValueReportDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        conn = DbConnection.getReportsConnectionObject();
        proc = conn.prepareCall(sqlOBValueReport_Usage);

        if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) {
            proc.setString(1, objDto.getOrderType().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr = formatter.parse(objDto.getFromDate());
            String formattedDate = formatter.format(dateStr);
            Date date1 = formatter.parse(formattedDate);
            formatter = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate = formatter.format(date1);
            proc.setString(2, formattedDate);
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) {
            SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy");
            Date dateStr1 = formatter1.parse(objDto.getToDate());
            String formattedDate1 = formatter1.format(dateStr1);
            Date date2 = formatter1.parse(formattedDate1);
            formatter1 = new SimpleDateFormat("MM-dd-yyyy");
            formattedDate1 = formatter1.format(date2);
            proc.setString(3, formattedDate1);
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) {
            proc.setString(4, objDto.getVerticalDetails().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(5, pagingSorting.getSortByColumn());// columnName
        proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(7, pagingSorting.getStartRecordId());// start index
        proc.setInt(8, pagingSorting.getEndRecordId());// end index
        proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            objDto = new OBValueReportDTO();
            objDto.setPartyNo(rs.getInt("PARTY_NO"));
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            objDto.setLineType(rs.getString("LINETYPE"));
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setServiceId(rs.getInt("SERVICENO"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6]
                            + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("FROM_SITE").split("~~");
                    objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objDto.setPrimaryLocation("");
            }
            if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " "
                            + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]);
                } else {
                    String ss[] = rs.getString("TO_SITE").split("~~");
                    objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]);
                }
            } else {
                objDto.setSeclocation("");
            }
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setPrjmngname(rs.getString("PRJ_MGR_NAME"));
            objDto.setPrjmgremail(rs.getString("PROJECTMGR_MAIL"));
            objDto.setActmngname(rs.getString("ACCT_MGR_NAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {
                objDto.setCustPoDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime())))
                                .toUpperCase());
            }
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {
                objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime())))
                        .toUpperCase());
            }
            objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objDto.setAmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE"));
            objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE"));
            objDto.setRfs_date(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                objDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime())))
                        .toUpperCase());
            }
            objDto.setOrdercategory(rs.getString("ORDERCATEGORY"));
            objDto.setOrderStatus(rs.getString("STATUS"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME"));
            objDto.setComponentName(rs.getString("COMPONENT_NAME"));
            objDto.setComponentID(rs.getInt("COMPONENTINFOID"));
            objDto.setServiceProductID(rs.getInt("LINENO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setEntity(rs.getString("COMPANYNAME"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setServiceType(rs.getString("SERVICETYPE"));
            objDto.setUom(rs.getString("UOM"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))
                    && rs.getString("PRIMARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("FROM_CITY").split("~~");
                    objDto.setFrom_city(ss[8]);
                } else {
                    objDto.setFrom_city(" ");
                }
            } else {
                objDto.setFrom_city("");
            }
            if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))
                    && rs.getString("SECONDARYLOCATIONTYPE") != null) {
                if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) {
                    String ss[] = rs.getString("TO_CITY").split("~~");
                    objDto.setTo_city(ss[8]);
                } else {
                    objDto.setTo_city(" ");
                }
            } else {
                objDto.setTo_city("");
            }
            objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL"));
            objDto.setOldlineamt(rs.getString("OLD_LINE_AMT"));
            objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD"));
            objDto.setRatio(rs.getString("RATIO"));
            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setDistance(rs.getString("DISTANCE"));
            objDto.setAccountManager(rs.getString("ACCOUNTMGR_EMAIL"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
            objDto.setPoAmount(rs.getString("POAMOUNT"));
            objDto.setBisource(rs.getString("BISOURCE"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));
            objDto.setParent_name(rs.getString("PARENTNAME"));
            objDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objDto.setLoc_date(rs.getString("LOC_DATE"));
            objDto.setFinalOB(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString());
            objDto.setFinalOBINR(Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_INR"), 2)).toString());
            objDto.setCopcApprovedBy(rs.getString("COPC_APPROVER_NAME"));
            objDto.setPmApprovedby(rs.getString("PM_APPROVER_NAME"));
            objDto.setDemoFlag(rs.getString("ISDEMO"));
            objDto.setOffnet(rs.getString("OFFNET_LABELATTVALUE"));
            objDto.setMediaType(rs.getString("MEDIA_LABELATTVALUE"));
            objDto.setCancellationReason(rs.getString("CANCELLATION_REASON"));
            objDto.setrRDate(rs.getString("RR_DATE"));
            if (rs.getString("RR_DATE") != null && !"".equals(rs.getString("RR_DATE"))) {
                objDto.setrRDate((utility.showDate_Report(new Date(rs.getTimestamp("RR_DATE").getTime())))
                        .toUpperCase());
            }
            objDto.setDiffDays(rs.getString("DIFF_DAYS"));
            objDto.setOrderEnteredBy(rs.getString("ORDER_CREATED_BY_NAME"));
            objDto.setExchangeRate(BigDecimal.valueOf((rs.getDouble("EXCHANGE_RATE"))).toPlainString());
            objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE_TRANSACTION"))).toPlainString());
            objDto.setObValueINR(
                    Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_TRANSACTION_INR"), 2)).toString());

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

            objDto.setObMonth(rs.getString("OB_MONTH"));
            objDto.setObYear(rs.getString("OB_YEAR")); //pankaj

            objDto.setEntryType(rs.getString("ENTRY_TYPE"));
            objDto.setIsNfa("NFA");
            //[007] start
            objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE"));
            objDto.setPartnerId(rs.getString("PARTNER_ID"));
            //[007] End
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);
        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }

    return objUserList;
}

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

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

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

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

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

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

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

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

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

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

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

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

            }

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

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

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

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

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

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

            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            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<ReportsDto> viewPerformanceDetailList(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "viewPerformanceDetailList", 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  a 2  s .  c  o m
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetPerformanceDetailReport);

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

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

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

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

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

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

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

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

        proc.setString(8, pagingSorting.getSortByColumn());// columnName
        proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(10, pagingSorting.getStartRecordId());// start index
        proc.setInt(11, pagingSorting.getEndRecordId());// end index
        proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) {
            proc.setString(13, objDto.getOsp().trim());
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setPartyNo(rs.getInt("PARTY_NO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setRegionName(rs.getString("REGION"));
            objReportsDto.setZoneName(rs.getString("ZONE"));
            objReportsDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objReportsDto.setIndustrySegment(rs.getString("INDUSTRYSEGMENT"));
            objReportsDto.setOrder_type(rs.getString("DEMO_TYPE"));
            objReportsDto.setOrderType(rs.getString("ORDERTYPE"));
            objReportsDto.setOrdersubtype(rs.getString("ORDER_SUBTYPE"));
            objReportsDto.setOrderStatus(rs.getString("STAGE"));
            objReportsDto.setOrderDate(rs.getString("ORDERDATE"));

            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {
                objReportsDto.setOrderDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setOrderApproveDate(rs.getString("ORDER_APPROVED_DATE"));

            if (rs.getString("ORDER_APPROVED_DATE") != null
                    && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) {
                objReportsDto.setOrderApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE"));

            if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) {
                objReportsDto.setPublishedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objReportsDto.setVertical(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setOrderTotal(rs.getDouble("ORDER_TOTAL"));
            objReportsDto.setTaskName(rs.getString("TASK_NAME"));
            objReportsDto.setActualStartDate(rs.getString("TASKSTARTDATE"));

            if (rs.getString("TASKSTARTDATE") != null && !"".equals(rs.getString("TASKSTARTDATE"))) {
                objReportsDto.setActualStartDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("TASKSTARTDATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setActualEndDate(rs.getString("TASKENDDATE"));

            if (rs.getString("TASKENDDATE") != null && !"".equals(rs.getString("TASKENDDATE"))) {
                objReportsDto.setActualEndDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("TASKENDDATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setTaskNumber(rs.getInt("TASKID"));
            objReportsDto.setOwner(rs.getString("OWNER_NAME"));
            objReportsDto.setAccountMgrPhoneNo(rs.getString("PHONENO"));//changed by kalpana from long to string for bug id HYPR11042013001
            objReportsDto.setEmailId(rs.getString("EMAILID"));
            objReportsDto.setUserName(rs.getString("USER_NAME"));
            objReportsDto.setTotalDays(rs.getString("TASK_DAYS"));
            objReportsDto.setRemarks(rs.getString("ACTION_REMARKS"));
            objReportsDto.setOutCome(rs.getString("OUTCOME"));
            objReportsDto.setOsp(rs.getString("OSP"));
            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<PendingOrderAndBillingReportDTO> viewPendingOrderAndBillingList(
        PendingOrderAndBillingReportDTO objDto) throws Exception {
    //Nagarjuna/*  w  w w .  j ava 2 s .c  o  m*/
    String methodName = "viewPendingOrderAndBillingList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<PendingOrderAndBillingReportDTO> objUserList = new ArrayList<PendingOrderAndBillingReportDTO>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    PendingOrderAndBillingReportDTO objRDto;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date tempDate = null;

    try {

        conn = DbConnection.getReportsConnectionObject();

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

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

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

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

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

            objRDto = new PendingOrderAndBillingReportDTO();
            objRDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO"));
            objRDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE"));
            objRDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objRDto.setOrderNumber(rs.getInt("ORDERNO"));
            objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objRDto.setCustPoDate(rs.getString("CUSTPODATE"));
            /*if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE")))
            {            
               Date date=df.parse(objRDto.getCustPoDate());
               objRDto.setCustPoDate((Utility.showDate_Report(date)).toUpperCase());         
            }*/
            tempDate = rs.getDate("CUSTPODATE");
            if (tempDate != null && !"".equals(tempDate)) {
                objRDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase());
            }

            objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objRDto.setM6OrderNo(rs.getInt("M6ORDERNO"));
            objRDto.setCurrencyName(rs.getString("CURRENCYNAME"));
            objRDto.setOrderType(rs.getString("ORDERTYPE"));
            objRDto.setLOC_Date(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {
                Date date = df.parse(objRDto.getLOC_Date());
                objRDto.setLOC_Date((Utility.showDate_Report(date)).toUpperCase());
            }
            objRDto.setPm_pro_date(rs.getString("PM_PROV_DATE"));
            if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) {
                String s1 = rs.getString("PM_PROV_DATE");
                String s3 = s1.substring(0, 7).toUpperCase();
                String s4 = s1.substring(9, 11);
                String s5 = s3.concat(s4);
                objRDto.setPm_pro_date(s5);
            }
            tempDate = rs.getDate("ORDERDATE");
            objRDto.setOrderDate(rs.getString("ORDERDATE"));
            if (tempDate != null && !"".equals(tempDate)) {
                objRDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase());
            }
            objRDto.setPoNumber(rs.getInt("PONUMBER"));
            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.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));

            //          Meenakshi : Changes for Usage
            if (objDto.getIsUsage() == 1) {
                objRDto.setRegionName(rs.getString("REGION"));
                objRDto.setAccountID(rs.getInt("CUSTACCOUNTID"));
                objRDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
                objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION"));
                objRDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
                objRDto.setLine_desc(rs.getString("SERVICEDETDESCRIPTION"));
                objRDto.setActmngname(rs.getString("ACCOUNTMANAGER"));
                objRDto.setPrjmngname(rs.getString("PROJECTMANAGER"));
                objRDto.setCopcApproveDate(rs.getString("COPC_APPROVED_DATE"));
                if (rs.getString("COPC_APPROVED_DATE") != null
                        && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                    objRDto.setCopcApproveDate(
                            (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                    .toUpperCase());
                }
                objRDto.setTaxation(rs.getString("TAXATIONVALUE"));

                objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID"));
                objRDto.setComponentID(rs.getInt("COMPONENT_ID"));
                objRDto.setComponentName(rs.getString("COMPONENT_NAME"));
                objRDto.setComponentType(rs.getString("COMPONENT_TYPE"));
                objRDto.setComponentRCNRCAmount(rs.getDouble("COMP_AMOUNT"));
                objRDto.setPackageID(rs.getInt("PACKAGE_ID"));
                objRDto.setPackageName(rs.getString("PACKAGE_NAME"));
                objRDto.setChild_act_no(rs.getString("Child_Account_Number"));

            } else {
                objRDto.setRegionName(rs.getString("REGIONNAME"));
                objRDto.setAccountID(rs.getInt("ACCOUNTID"));
                objRDto.setCrm_productname(rs.getString("CRM_PRODUCT_NAME"));
                objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
                objRDto.setCompanyName(rs.getString("COMPANYNAME"));
                objRDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
                objRDto.setLinename(rs.getString("LINENAME"));
                objRDto.setLcompanyname(rs.getString("LICENCECOMPANYNAME"));
                objRDto.setStorename(rs.getString("STORENAME"));
                objRDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
                objRDto.setActmngname(rs.getString("ACTMNAME"));
                objRDto.setPrjmngname(rs.getString("PMNAME"));
                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.setTaxation(rs.getString("TAXATION"));
                objRDto.setOsp(rs.getString("OSP"));

                objRDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE"));
                objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
                objRDto.setChargeName(rs.getString("CHARGE_NAME"));
                objRDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
                objRDto.setFrequencyName(rs.getString("PAYMENTTERM"));
                objRDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NO"));
            }

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

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