Example usage for java.sql CallableStatement setNull

List of usage examples for java.sql CallableStatement setNull

Introduction

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

Prototype

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

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

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

public ArrayList<UserAccessMatrixDto> viewAccessMatrixReport(UserAccessMatrixDto objDto) {
    //start//ww w. j  a v a 2 s . c om
    String methodName = "viewAccessMatrixReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    Connection connection = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    ArrayList<UserAccessMatrixDto> docListDetails = new ArrayList<UserAccessMatrixDto>();
    int recordCount = 0;
    UserAccessMatrixDto objReportsDto = null;
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    String copcFromDate = objDto.getFromCOPCDate();
    String cocpToDate = objDto.getToCOPCDate();

    try {
        connection = DbConnection.getReportsConnectionObject();
        cstmt = connection.prepareCall(sqlGetAccesstMatrixReport);

        if (copcFromDate != null && !"".equals(copcFromDate)) {

            Date fromDate = df.parse(copcFromDate);
            cstmt.setDate(1, new java.sql.Date(fromDate.getTime()));

        } else {
            cstmt.setNull(1, java.sql.Types.DATE);
        }

        if (cocpToDate != null && !"".equals(cocpToDate)) {

            Date toDate = df.parse(cocpToDate);
            cstmt.setDate(2, new java.sql.Date(toDate.getTime()));

        } else {
            cstmt.setNull(2, java.sql.Types.DATE);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();
        // To calculate start index and Enc Index
        /*
                 cstmt.setString(3,  pagingSorting.getSortByColumn());// columnName
                 cstmt.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting
                       .getSortByOrder()));// sort order
        */ cstmt.setInt(3, pagingSorting.getStartRecordId());// start index
        cstmt.setInt(4, pagingSorting.getEndRecordId());// end index
        cstmt.setInt(5, (pagingSorting.isPagingToBeDone() ? 1 : 0));

        rs = cstmt.executeQuery();
        long i = 1;
        while (rs.next()) {
            objReportsDto = new UserAccessMatrixDto();
            objReportsDto.setSrno(i++);
            objReportsDto.setUserId(rs.getString("USERID"));
            objReportsDto.setUserName(rs.getString("USERNAME"));
            objReportsDto.setRoleId(rs.getLong("ROLEID"));
            objReportsDto.setRoleName(rs.getString("ROLENAME"));
            objReportsDto.setAccessOrDenied(rs.getString("STATUS"));
            objReportsDto.setDateOfmofification(rs.getString("DATEOFMODIFICATION"));
            objReportsDto.setModifiedByUserId(rs.getString("MODIFIEDBYUSERID"));
            objReportsDto.setModifiedByUserName(rs.getString("MODIFIEDBYUSERNAME"));
            objReportsDto.setOldCustSegmentName(rs.getString("OLDCUSTOMERSEGMENT"));
            objReportsDto.setCus_segment(rs.getString("NEWCUSTOMERSEGMENT"));

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

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

            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
        }
    }

    return docListDetails;

}

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

public ArrayList<PerformanceSummaryReportDTO> viewPerformanceSummaryReport(PerformanceSummaryReportDTO objDto) {
    //      Nagarjuna
    String methodName = "viewPerformanceSummaryReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end//from  w  w  w  . j av  a  2  s. co m
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<PerformanceSummaryReportDTO> listSearchDetails = new ArrayList<PerformanceSummaryReportDTO>();
    PerformanceSummaryReportDTO 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())) {
            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.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 PerformanceSummaryReportDTO();
            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("AM_DELAY_REASON") != null) {
                objReportsDto.setAmDelayReason(rs.getString("AM_DELAY_REASON"));
            } else {
                objReportsDto.setAmDelayReason("");
            }
            if (rs.getString("COPC_DELAY_REASON") != null) {
                objReportsDto.setCopcDelayReason(rs.getString("COPC_DELAY_REASON"));
            } else {
                objReportsDto.setCopcDelayReason("");
            }
            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"));
            //            [404040] Start 
            objReportsDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            objReportsDto.setGroupName((rs.getString("GROUPNAME")));
            //lawkush start
            objReportsDto.setCopcStartDate(rs.getString("COPC_START_DATE"));
            if (rs.getString("COPC_START_DATE") != null && !"".equals(rs.getString("COPC_START_DATE"))) {
                objReportsDto.setCopcStartDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_START_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcEndDate(rs.getString("COPC_END_DATE"));
            if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) {
                objReportsDto.setCopcEndDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime())))
                                .toUpperCase());
            }
            //lawkush End
            //[606060] Start
            objReportsDto.setCancelDate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {
                objReportsDto.setCancelDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime())))
                                .toUpperCase());
                objReportsDto.setCancelMonth(
                        (new SimpleDateFormat("MMM").format(new Date(rs.getTimestamp("CANCEL_DATE").getTime())))
                                .toUpperCase());
            }

            // [606060] END
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try

        {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

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

public ArrayList<PerformanceDetailReportDTO> viewPerformanceDetailList(PerformanceDetailReportDTO objDto) {
    //   Nagarjuna
    String methodName = "viewPerformanceDetailList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end   //from w  w  w.j  a  va 2 s.com
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    ArrayList<PerformanceDetailReportDTO> listSearchDetails = new ArrayList<PerformanceDetailReportDTO>();
    PerformanceDetailReportDTO 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())) {
            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.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 PerformanceDetailReportDTO();
            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"));
            objReportsDto.setPoNumber(rs.getInt("PODETAILNUMBER"));
            //         [404040] Start 
            objReportsDto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM"));
            objReportsDto.setGroupName((rs.getString("GROUPNAME")));
            //lawkush start
            objReportsDto.setCopcStartDate(rs.getString("COPC_START_DATE"));
            if (rs.getString("COPC_START_DATE") != null && !"".equals(rs.getString("COPC_START_DATE"))) {
                objReportsDto.setCopcStartDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_START_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcEndDate(rs.getString("COPC_END_DATE"));
            if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) {
                objReportsDto.setCopcEndDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime())))
                                .toUpperCase());
            }
            //lawkush End
            //[130] start
            objReportsDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objReportsDto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objReportsDto.setPartnerId(rs.getString("PARTNER_ID"));
            //[130] End
            //[131] start
            objReportsDto.setPartnerCode(rs.getString("PARTNER_CODE"));
            objReportsDto.setFieldEngineer(rs.getString("FIELD_ENGINEER"));
            //[131] end
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

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

public ArrayList<BillingWorkQueueReportDTO> viewBillingWorkQueueList(BillingWorkQueueReportDTO objDto2)
        throws Exception {
    //   Nagarjuna
    String methodName = "viewBillingWorkQueueList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end//  w w  w  .ja v a 2 s .c  o m
    ArrayList<BillingWorkQueueReportDTO> objUserList = new ArrayList<BillingWorkQueueReportDTO>();
    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(sqlBillingWorkQueue);
        proc.setInt(1, java.sql.Types.BIGINT);
        if (objDto2.getOrderType() != null && !"".equals(objDto2.getOrderType())) {
            proc.setString(2, objDto2.getOrderType().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        if (objDto2.getFromDate() != null && !"".equals(objDto2.getFromDate())) {
            proc.setString(3, objDto2.getFromDate().trim());
        } else {
            proc.setNull(3, java.sql.Types.VARCHAR);
        }
        if (objDto2.getToDate() != null && !"".equals(objDto2.getToDate())) {
            proc.setString(4, objDto2.getToDate().trim());
        } else {
            proc.setNull(4, java.sql.Types.VARCHAR);
        }

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

        /*
         * add party no and party name
         */
        if (objDto2.getParty_no() != 0 && !"".equals(objDto2.getParty_no())) {
            proc.setInt(7, objDto2.getParty_no());
        } else {
            proc.setNull(7, java.sql.Types.BIGINT);
        }
        if (objDto2.getPartyName() != null && !"".equals(objDto2.getPartyName())) {
            proc.setString(8, objDto2.getPartyName().trim());
        } else {
            proc.setNull(8, java.sql.Types.VARCHAR);
        }
        if (objDto2.getOrderStage() != null && !"".equals(objDto2.getOrderStage())) {
            proc.setString(9, objDto2.getOrderStage().trim());
        } else {
            proc.setNull(9, java.sql.Types.VARCHAR);
        }
        if (objDto2.getHardwareType() != null && !"".equals(objDto2.getHardwareType())) {
            proc.setString(10, objDto2.getHardwareType().trim());
        } else {
            proc.setNull(10, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto2.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");
            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(rs.getString("BILLING_ADDRESS"));
            objdto.setServiceTypeDescription(rs.getString("SERVICE_ORDER_TYPE_DESC"));
            objdto.setCancelBy(rs.getString("CANCEL_BY"));
            objdto.setCanceldate(rs.getString("CANCEL_DATE"));
            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("BILLINGMODE"));
            objdto.setBillingTypeName(rs.getString("BILL_TYPE"));
            objdto.setBillingformat(rs.getString("BILL_FORMAT"));
            objdto.setLicCompanyName(rs.getString("LICENSE_COMP"));
            //objdto.setTaxation(rs.getString("TAXATIONVALUE")); // 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(rs.getString("PRIMLOC"));
            objdto.setSeclocation(rs.getString("SECLOC"));
            objdto.setPoNumber(rs.getInt("PODETAILNUMBER"));
            objdto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {

                //Date date=df.parse(objdto.getPoDate());
                objdto.setPoDate((utility.showDate_Report(objdto.getPoDate())).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_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_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(rs.getString("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("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());
            }
            //lawkush start
            objdto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //lawkush end

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

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

    return objUserList;
}

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

public ArrayList<LogicalSIDataReportDTO> viewLogicalSIDataReport(LogicalSIDataReportDTO objDto) {
    //      Nagarjuna
    String methodName = "viewLogicalSIDataReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end/*  www.  jav a  2  s  . co m*/
    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");
    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));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new LogicalSIDataReportDTO();
            objReportsDto.setOrderNumber(rs.getInt("ORDERNO"));
            objReportsDto.setRecordStatus(rs.getString("recordStatus"));
            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.setChargeTypeName(rs.getString("CHARGE_TYPE"));
            objReportsDto.setChargeName(rs.getString("CHARGE_NAME"));
            objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS"));
            objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT"));
            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.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.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.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT"));
            objReportsDto.setEntity(rs.getString("ENTITYNAME"));
            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.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//PO Contract Period
            objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE"));
            if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) {

                Date date = df.parse(objReportsDto.getContractStartDate());
                objReportsDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase());

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

                Date date = df.parse(objReportsDto.getContractEndDate());
                objReportsDto.setContractEndDate((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.setChargeinfoID(rs.getString("CHARGEINFOID"));//need to add in view
            objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));
            objReportsDto.setServiceId(rs.getInt("SERVICE_NO"));
            //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   
            //M6 Order Id   
            //Order Line Si No   
            objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION"));
            objReportsDto.setRemarks(rs.getString("REMARKS"));
            objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD"));
            objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME"));
            objReportsDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME"));
            String billAddress = rs.getString("BILLING_ADDRESS");
            String[] billAddressArray = billAddress.split("~~ ", 14);
            objReportsDto.setCountyName(billAddressArray[8]);// rs.getString("COUNTRY_NAME")
            objReportsDto.setAddress1(billAddressArray[2]);//billing Address1
            objReportsDto.setAddress2(billAddressArray[3]);//billing Address2   
            objReportsDto.setAddress3(billAddressArray[4]);//billing Address3   
            objReportsDto.setAddress4(billAddressArray[5]);//billing Address4
            objReportsDto.setCityName(billAddressArray[6]);//need to add in view - rs.getString("CITY_NAME")
            objReportsDto.setPostalCode(billAddressArray[9]);//need to add in view -rs.getString("POSTAL_CODE")
            objReportsDto.setStateName(billAddressArray[7]);//need to add in view - rs.getString("STATE_NAME")
            //Active End Date
            objReportsDto.setContactName(billAddressArray[0] + " " + billAddressArray[1]);//Contact Person Name - rs.getString("BILL_CON_PER_NAME")   
            objReportsDto.setDesignation(billAddressArray[13]);//Person Designation   - rs.getString("DESIGNATION")
            objReportsDto.setTelePhoneNo(billAddressArray[10]);//Person Mobile   - rs.getString("TELEPHONENO")
            objReportsDto.setEmailId(billAddressArray[12]);//Person Email   - rs.getString("EMAIL_ID")
            objReportsDto.setFax(billAddressArray[11]);//Person Fax   - rs.getString("FAX")
            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   
            //Remrks   
            objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME"));
            objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD"));
            objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD"));
            objReportsDto.setLogicalCircuitId(rs.getString("CKTID"));

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

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

public ArrayList<ActiveLineDemoReportDTO> viewActiveLineDemoReportDetails(ActiveLineDemoReportDTO reportsDto) {

    AppConstants.IOES_LOGGER.info("ReportsDao: viewActiveLineDemoReportDetails is executing...");
    Connection connection = null;
    ResultSet resultSet = null;//from  www. ja v a  2 s.c o  m
    CallableStatement callableStatement = null;
    ArrayList<ActiveLineDemoReportDTO> activeLineDemoReportDTOsList = null;
    try {

        connection = DbConnection.getReportsConnectionObject();
        callableStatement = connection.prepareCall(spGetActiveLineDemoReport);
        activeLineDemoReportDTOsList = new ArrayList<ActiveLineDemoReportDTO>();
        String orderFromDate = reportsDto.getOrderDate();
        String orderToDate = reportsDto.getOrderDate();
        long fromOrderNo = reportsDto.getFromOrderNo();
        long toOrderNo = reportsDto.getToOrderNo();

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM-dd-yyyy");

        if (orderFromDate == null || orderFromDate.trim().equals("")) {
            callableStatement.setNull(1, java.sql.Types.VARCHAR);
        } else {
            orderFromDate = simpleDateFormat.format(orderFromDate);
            callableStatement.setString(1, Utility.getReportOrderDate(orderFromDate));
            AppConstants.IOES_LOGGER
                    .info("Date format is recomemded: " + simpleDateFormat.format(orderFromDate));
        }
        if (orderToDate == null || orderToDate.trim().equals("")) {
            callableStatement.setNull(2, java.sql.Types.VARCHAR);
        } else {

            callableStatement.setString(2, Utility.getReportOrderDate(orderToDate));
        }
        if (fromOrderNo != 0 && toOrderNo != 0) {
            callableStatement.setLong(3, fromOrderNo);
            callableStatement.setLong(4, toOrderNo);

        } else {
            callableStatement.setInt(3, java.sql.Types.BIGINT);
            callableStatement.setInt(4, java.sql.Types.BIGINT);

        }
        //AppConstants.IOES_LOGGER.info(Utility.getReportOrderDate(orderFromDate)+"Order numL  ");
        Utility utility = new Utility();
        PagingSorting pagingSorting = reportsDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

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

        resultSet = callableStatement.executeQuery();

        int countFlag = 0;
        int recordCount = 0;
        ActiveLineDemoReportDTO demoReportDTO = new ActiveLineDemoReportDTO();

        while (resultSet.next() != false) {

            countFlag++;

            demoReportDTO.setPartyName(resultSet.getString("PARTYNAME"));
            demoReportDTO.setPartyNo(resultSet.getString("PARTY_NO"));
            demoReportDTO.setCrmAccNo(resultSet.getLong("CRMACCOUNTNO"));
            demoReportDTO.setCustomerSegment(resultSet.getString("CUSTOMER_SEGMENT"));
            demoReportDTO.setIndustrySegment(resultSet.getString("INDUSTRY_SEGMENT"));
            demoReportDTO.setRegionName(resultSet.getString("REGIONNAME"));
            demoReportDTO.setZoneName(resultSet.getString("ZONENNAME"));
            demoReportDTO.setAcctMgrName(resultSet.getString("ACCT_MGR_NAME"));
            demoReportDTO.setPrjMGRName(resultSet.getString("PRJ_MGR_NAME"));
            demoReportDTO.setDemoOrder(resultSet.getString("DEMO_ORDER"));
            demoReportDTO.setNoOfDays(resultSet.getLong("NO_OF_DAYS"));
            demoReportDTO.setOrderType(resultSet.getString("ORDERTYPE"));
            demoReportDTO.setChangeTypeName(resultSet.getString("CHANGETYPENAME"));
            demoReportDTO.setSubChangeType(resultSet.getString("ORDER_SUBCHANGETYPE"));
            if (!(resultSet.getString("ORDERDATE") == null || resultSet.getString("ORDERDATE") == "")) {
                demoReportDTO.setOrderDate((utility.showDate_Report(resultSet.getString("ORDERDATE"))));
            }
            demoReportDTO.setServiceId(resultSet.getLong("SERVICEID"));
            demoReportDTO.setOrderNo(resultSet.getLong("ORDERNO"));
            demoReportDTO.setLogicalSINo(resultSet.getString("LOGICAL_SI_NO"));
            demoReportDTO.setCustLogicalSINo(resultSet.getString("CUSTOMER_LOGICAL_SI_NO"));
            demoReportDTO.setCktId(resultSet.getString("CKTID"));
            demoReportDTO.setAnnotation(resultSet.getString("ANNOTATION"));
            demoReportDTO.setM6OrderNo(resultSet.getLong("M6ORDERNO"));
            demoReportDTO.setLocNo(resultSet.getString("LOCNO"));
            demoReportDTO.setLocDate(resultSet.getString("LOC_DATE"));
            demoReportDTO.setFromAddress(resultSet.getString("FROM_ADDRESS"));
            demoReportDTO.setToAddress(resultSet.getString("TO_ADDRESS"));
            demoReportDTO.setBillingBandWidth(resultSet.getString("BILLING_BANDWIDTH"));

            demoReportDTO.setBillingBandwidthUOM(resultSet.getString("BILLING_BANDWIDTH_UOM"));
            demoReportDTO.setCreateDate(
                    utility.showDate_Report5((new Date(resultSet.getTimestamp("CREATEDDATE").getTime())))
                            .toUpperCase());
            demoReportDTO.setOrderApprovalDate(utility
                    .showDate_Report5((new Date(resultSet.getTimestamp("ORDER_APPROVAL_DATE").getTime())))
                    .toUpperCase());

            if (!(resultSet.getString("PUBLISHED_DATE") == null
                    || resultSet.getString("PUBLISHED_DATE") == "")) {
                demoReportDTO
                        .setPublishedDate((utility.showDate_Report(resultSet.getString("PUBLISHED_DATE"))));
            }

            if (!(resultSet.getString("SERVICE_CLOSURE_DATE") == null
                    || resultSet.getString("SERVICE_CLOSURE_DATE") == "")) {
                demoReportDTO.setServiceClosureDate(
                        (utility.showDate_Report(resultSet.getString("SERVICE_CLOSURE_DATE"))));
            }

            if (!(resultSet.getString("BILLING_TRIGGER_CREATEDATE") == null
                    || resultSet.getString("BILLING_TRIGGER_CREATEDATE") == "")) {
                demoReportDTO.setBillingTriggerCreateDate(
                        (utility.showDate_Report(resultSet.getString("BILLING_TRIGGER_CREATEDATE"))));
            }
            if (!(resultSet.getString("BILLINGTRIGGERDATE") == null
                    || resultSet.getString("BILLINGTRIGGERDATE") == "")) {
                demoReportDTO.setBillingTriggerDate(
                        (utility.showDate_Report(resultSet.getString("BILLINGTRIGGERDATE"))));
            }
            if (!(resultSet.getString("CHARGE_CURRENT_START_DATE") == null
                    || resultSet.getString("CHARGE_CURRENT_START_DATE") == "")) {
                demoReportDTO.setChargeCurrentStartDate(
                        (utility.showDate_Report(resultSet.getString("CHARGE_CURRENT_START_DATE"))));
            }

            if (!(resultSet.getString("CURRENT_END_DATE") == null
                    || resultSet.getString("CURRENT_END_DATE") == "")) {
                demoReportDTO.setChargeCurrentEndDate(
                        (utility.showDate_Report(resultSet.getString("CURRENT_END_DATE"))));
            }

            demoReportDTO.setMstChargeName(resultSet.getString("MST_CHARGENAME"));
            demoReportDTO.setProductName(resultSet.getString("PRODUCTNAME"));
            demoReportDTO.setSubTypeName(resultSet.getString("SERVICESUBTYPENAME"));
            demoReportDTO.setStage(resultSet.getString("STAGE"));
            demoReportDTO.setServiceTypeName(resultSet.getString("SERVICETYPENAME"));
            demoReportDTO.setCopcApprovalRemark(resultSet.getString("Copc_Approval_Remark"));
            demoReportDTO.setOrderEntryRemark(resultSet.getString("Order_Entry_Task_Remark"));
            demoReportDTO.setpMRemark(resultSet.getString("Pm_Approval_Task_Remark"));
            demoReportDTO.setTotalAmount(resultSet.getLong("TOT_AMOUNT"));
            demoReportDTO.setCurName(resultSet.getString("CURNAME"));
            demoReportDTO.setAnnualRate(resultSet.getLong("ANNUAL_RATE"));
            demoReportDTO.setPublished(resultSet.getString("PUBLISHED"));
            demoReportDTO.setServiceStage(resultSet.getString("SERVICE_STAGE"));
            demoReportDTO.setLsiDemoType(resultSet.getInt("IS_DEMO"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = resultSet.getInt("FULL_REC_COUNT");
                //recordCount = countFlag;
            }
            activeLineDemoReportDTOsList.add(demoReportDTO);
        }
        pagingSorting.setRecordCount(recordCount);

    } catch (Exception exception) {

        exception.printStackTrace();
        AppConstants.IOES_LOGGER
                .info("ReportsDao: viewActiveLineDemoReportDetails has problem in execution " + exception);

    } finally {
        try {

            DbConnection.closeResultset(resultSet);
            DbConnection.closeCallableStatement(callableStatement);
            DbConnection.freeConnection(connection);

        } catch (Exception exception2) {
            AppConstants.IOES_LOGGER.info("ReportsDao: Problem in closing connetion " + exception2);
        }
    }
    return activeLineDemoReportDTOsList;
}

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

/**
* 
* @param objDto/*from  ww w  .  jav a  2s  . c  om*/
* @return
* @throws Exception
*/
public ArrayList<ActiveLineItemReportsDTO> viewActiveLineItemsList(ActiveLineItemReportsDTO objDto)
        throws Exception {

    //   Nagarjuna
    String methodName = "viewActiveLineItemsList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    ArrayList<ActiveLineItemReportsDTO> objUserList = new ArrayList<ActiveLineItemReportsDTO>();
    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(sqlActiveLineItems);
        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);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(7, pagingSorting.getSortByColumn());// columnName
        proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(9, pagingSorting.getStartRecordId());// start index
        proc.setInt(10, pagingSorting.getEndRecordId());// end index
        proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index
        if (objDto.getPartyNo() != 0 && !"".equals(objDto.getPartyNo())) {
            proc.setInt(12, objDto.getPartyNo());
        } else {
            proc.setNull(12, java.sql.Types.BIGINT);
        }
        System.out.println("Pankaj CustSeg" + objDto.getCustomerSegment());
        if (objDto.getCustomerSegment() != null && !"".equals(objDto.getCustomerSegment())
                && !objDto.getCustomerSegment().equals("0")) {
            proc.setString(13, objDto.getCustomerSegment());
        } else {
            proc.setNull(13, java.sql.Types.VARCHAR);
        }
        System.out.println("Pankaj productname" + objDto.getProductName());
        if (objDto.getProductName() != null && !"".equals(objDto.getProductName())
                && !objDto.getProductName().equals("0")) {
            proc.setString(14, objDto.getProductName());
        } else {
            proc.setNull(14, java.sql.Types.VARCHAR);
        }

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

            objdto = new ActiveLineItemReportsDTO();
            objdto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objdto.setCustSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO"));
            objdto.setServiceName(rs.getString("SERVICENAME"));
            objdto.setLinename(rs.getString("LINENAME"));
            objdto.setChargeTypeName(rs.getString("CHARGETYPE"));
            objdto.setChargeTypeID(rs.getInt("CHARGETYPEID"));
            objdto.setChargeName(rs.getString("CHARGE_NAME"));
            objdto.setChargeFrequency(rs.getString("FREQUENCYNAME"));
            objdto.setBillPeriod(rs.getString("BILL_PERIOD"));
            objdto.setStartDate(rs.getString("START_DATE"));
            if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) {

                Date date = df.parse(objdto.getStartDate());
                objdto.setStartDate((utility.showDate_Report(date)).toUpperCase());
            }
            objdto.setAccountID(rs.getInt("ACCOUNTID"));
            objdto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME"));
            objdto.setCurrencyName(rs.getString("CURNAME"));
            objdto.setEntity(rs.getString("ENTITYNAME"));
            objdto.setBillingMode(rs.getString("BILLINGMODE"));
            objdto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));
            objdto.setBillingformat(rs.getString("BILLING_FORMATNAME"));
            objdto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objdto.setTaxation(rs.getString("TAXATIONVALUE"));
            objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO"));
            objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME"));
            objdto.setStore(rs.getString("STORENAME"));
            objdto.setHardwaretypeName(rs.getString("HARDWARETYPENAME"));
            objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE"));
            objdto.setSaleNature(rs.getString("SALENATURENAME"));
            objdto.setSaleTypeName(rs.getString("SALETYPENAME"));
            objdto.setPrimaryLocation(rs.getString("PRIMARYLOCATION"));
            objdto.setSeclocation(rs.getString("SECONDARYLOCATION"));
            objdto.setPoNumber(rs.getInt("PODETAILNUMBER"));
            objdto.setPoDate(rs.getString("PODATE"));
            if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) {

                Date date = df.parse(objdto.getPoDate());
                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_SATATUS"));
            objdto.setFx_charge_status(rs.getString("FX_STATUS"));
            objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHARGE_STATUS"));
            //Vijay start
            //objdto.setTokenID(rs.getInt("TOKEN_ID"));
            objdto.setToken_ID(rs.getString("TOKEN_ID"));
            //vijay end
            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_TRIGGER_FLAG"));
            objdto.setPm_pro_date(rs.getString("PM_PROVISIONING_DATE"));
            if (rs.getString("PM_PROVISIONING_DATE") != null
                    && !"".equals(rs.getString("PM_PROVISIONING_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);
            }
            objdto.setLocDate(rs.getString("LOCDATE"));
            if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) {

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

            }

            objdto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE"));
            if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) {

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

            objdto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));
            objdto.setChild_account_creation_status(rs.getString("Child_account_FX_sataus"));
            objdto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

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

            }

            objdto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objdto.setPmapprovaldate(
                        (utility.showDate_Report5(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }

            objdto.setCopcapprovaldate(rs.getString("APPROVAL_DATE"));
            if (rs.getString("APPROVAL_DATE") != null && !"".equals(rs.getString("APPROVAL_DATE"))) {
                objdto.setCopcapprovaldate(
                        (utility.showDate_Report5(new Date(rs.getTimestamp("APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setOrderType(rs.getString("ORDERTYPE"));

            objdto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE"));
            if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null
                    && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) {

                objdto.setBillingtrigger_createdate((utility
                        .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime())))
                                .toUpperCase());

            }
            objdto.setRatio(rs.getString("RATIO"));
            objdto.setProductName(rs.getString("PRODUCTNAME"));
            objdto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objdto.setHardwareType(rs.getString("HARDWARE_FLAG"));
            objdto.setServiceStage(rs.getString("SERVICE_STAGE"));
            objdto.setOrderStage(rs.getString("STAGE"));
            objdto.setRfsDate(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {

                objdto.setRfsDate((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime())))
                        .toUpperCase());

            }

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

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

            }
            objdto.setCharge_status(rs.getString("CHARGES_STATUS"));
            objdto.setLOC_No(rs.getString("LOCNO"));
            objdto.setAddress1(rs.getString("ADDRESS"));
            objdto.setM6cktid(rs.getString("CKTID"));
            objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objdto.setRegion(rs.getString("REGIONNAME"));
            objdto.setBandwaidth(rs.getString("BANDWIDTH"));
            objdto.setVertical(rs.getString("VERTICAL_DETAILS"));
            objdto.setAccountManager(rs.getString("ACCOUNTMANAGER"));
            objdto.setProjectManager(rs.getString("PROJECTMANAGER"));
            objdto.setRate_code(rs.getString("RATECODE"));
            objdto.setLmRemarks(rs.getString("LM_REMARKS"));
            objdto.setLmMedia(rs.getString("LM_MEDIA"));
            //            last
            objdto.setChargeable_Distance(rs.getString("CHARGEABLE_DISTANCE"));
            //            link
            objdto.setDispatchAddress1(rs.getString("DISPATCH_DETAILS"));
            objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE"));
            // productname
            objdto.setPartyName(rs.getString("PARTYNAME"));
            objdto.setBilling_location_from(rs.getString("BILLING_LOCATION"));
            objdto.setDemo(rs.getString("DEMO_ORDER"));
            objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME"));
            objdto.setToLocation(rs.getString("FROM_LOCATION"));
            objdto.setFromLocation(rs.getString("TO_LOCATION"));
            objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objdto.setBlSource(rs.getString("BL_SOURCE"));
            objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID"));
            objdto.setOrderNumber(rs.getInt("ORDERNO"));
            objdto.setChargeAmount(rs.getDouble("INV_AMT"));
            objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT"));
            objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT"));
            objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objdto.setTotalPoAmt("" + rs.getDouble("TOTAL_POAMOUNT"));
            objdto.setParty_id(rs.getInt("PARTY_ID"));
            objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO"));
            //m6 productid
            objdto.setM6OrderNo(rs.getInt("M6ORDERID"));
            objdto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT"));
            //m6 order,business
            objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12
            objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE"));
            objdto.setServiceId(rs.getInt("SERVICEID"));
            objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE"));
            if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) {
                objdto.setPoExpiryDate((utility.showDate_Report(objdto.getPoExpiryDate())).toUpperCase());
            }

            //[404040] Start 
            objdto.setOpportunityId((rs.getString("OPPORTUNITYID")));
            //[404040] End 
            objdto.setGroupName((rs.getString("GROUPNAME")));
            //[505050] Start 
            objdto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO"));
            objdto.setMocn_no(rs.getString("MOCN_NUMBER"));
            objdto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));
            objdto.setOldLsi(rs.getString("OLD_LSI_CRM"));
            objdto.setOpms_lineItemNumber(rs.getString("OPMS_LINEITEMNUMBER"));

            //[505050] End
            /*Vijay add few more columns*/
            objdto.setAsiteAdd1(rs.getString("ASITE_ADD1"));
            objdto.setAsiteAdd2(rs.getString("ASITE_ADD2"));
            objdto.setAsiteAdd3(rs.getString("ASITE_ADD3"));
            objdto.setBsiteName(rs.getString("BSITE_NAME"));
            objdto.setBsiteLineAdd1(rs.getString("BSITE_ADD1"));
            objdto.setBsiteLineAdd2(rs.getString("BSITE_ADD2"));
            //vijay end
            //[111] start
            objdto.setMainSpid(rs.getLong("MAIN_SERVICEPRODUCTID"));
            objdto.setTaxExemptReasonName(rs.getString("REASONNAME"));
            //[111] end
            //[505054] Start
            objdto.setServiceSegment(rs.getString("SERVICESEGMENT"));
            //[505054] End

            //[004] Start
            objdto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY"));
            objdto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY"));
            objdto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE"));
            objdto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE"));
            objdto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME"));
            objdto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER"));
            objdto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID"));

            //[004] End
            //satyapan OSP/ISP
            objdto.setIsOspRequired(rs.getString("IS_OSP"));
            if (rs.getString("OSP_REG_NO") != null && !"".equals(rs.getString("OSP_REG_NO"))) {
                objdto.setOspRegistrationNo(rs.getString("OSP_REG_NO"));
            } else {
                objdto.setOspRegistrationNo("");
            }
            objdto.setOspRegistrationDate(rs.getString("OSP_REG_DATE"));
            if (rs.getString("OSP_REG_DATE") != null && !"".equals(rs.getString("OSP_REG_DATE"))) {
                objdto.setOspRegistrationDate(
                        (Utility.showDate_Report(new Date(rs.getTimestamp("OSP_REG_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setIsIspRequired(rs.getString("ISP_TAGGING"));
            if (rs.getString("ISP_LIC_CTGRY") != null && !"".equals(rs.getString("ISP_LIC_CTGRY"))
                    && !"0".equals(rs.getString("ISP_LIC_CTGRY"))) {
                objdto.setIspCatageory(rs.getString("ISP_LIC_CTGRY"));
            } else {
                objdto.setIspCatageory("");
            }
            if (rs.getString("ISP_LIC_NO") != null && !"".equals(rs.getString("ISP_LIC_NO"))) {
                objdto.setIspLicenseNo(rs.getString("ISP_LIC_NO"));
            } else {
                objdto.setIspLicenseNo("");
            }
            objdto.setIspLicenseDate(rs.getString("ISP_LIC_DATE"));
            if (rs.getString("ISP_LIC_DATE") != null && !"".equals(rs.getString("ISP_LIC_DATE"))) {
                objdto.setIspLicenseDate(
                        (Utility.showDate_Report(new Date(rs.getTimestamp("ISP_LIC_DATE").getTime())))
                                .toUpperCase());
            }

            //End of satyapan OSP/ISP
            //[007] Start
            objdto.setStandardReason(rs.getString("STANDARDREASON"));
            objdto.setLdClause(rs.getString("LDCLAUSE"));
            //[007] End

            //[008] Start
            objdto.setOrderCreationDate(rs.getString("CREATED_DATE"));
            if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) {
                objdto.setOrderCreationDate(
                        (utility.showDate_Report5(new Date(rs.getTimestamp("CREATED_DATE").getTime())))
                                .toUpperCase());
            }
            objdto.setPublishedDate(rs.getString("PUBLISHED_DATE"));
            if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) {
                objdto.setPublishedDate(
                        (utility.showDate_Report5(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime())))
                                .toUpperCase());
            }

            //[008] End
            //[130] start
            objdto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID"));
            objdto.setChannelPartner(rs.getString("PARTNER_NAME"));
            objdto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE"));
            objdto.setPartnerId(rs.getString("PARTNER_ID"));
            //[130] End
            //[131] start
            objdto.setPartnerCode(rs.getString("PARTNER_CODE"));
            objdto.setFieldEngineer(rs.getString("FIELD_ENGINEER"));
            //[131] end
            // nancy start
            objdto.setePCNNo(rs.getString("EPCN_NO"));
            //nancy end
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
                //recordCount = countFlag;
            }
            objUserList.add(objdto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

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

    return objUserList;
}

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

/**
 * Method::viewDocumentMatrixReport/*  ww w  . ja v  a2 s. c  o  m*/
 * @param DocumentMatrixReportDTO
 * @author Gunjan Singla
 * @return ArrayList
 */
public ArrayList<DocumentMatrixReportDTO> viewDocumentMatrixReport(DocumentMatrixReportDTO objDto) {
    //start
    String methodName = "viewDocumentMatrixReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end
    Connection connection = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    int recordCount = 0;
    ArrayList<DocumentMatrixReportDTO> docListDetails = new ArrayList<DocumentMatrixReportDTO>();
    DocumentMatrixReportDTO objReportsDto = null;
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    String copcFromDate = objDto.getFromCOPCDate();
    String cocpToDate = objDto.getToCOPCDate();
    String custSegment = objDto.getCustSegment();

    try {
        connection = DbConnection.getReportsConnectionObject();
        cstmt = connection.prepareCall(sqlGetDocumentMatrixReport);

        if (copcFromDate != null && !"".equals(copcFromDate)) {

            Date fromDate = df.parse(copcFromDate);
            cstmt.setDate(1, new java.sql.Date(fromDate.getTime()));

        } else {
            cstmt.setNull(1, java.sql.Types.DATE);
        }

        if (cocpToDate != null && !"".equals(cocpToDate)) {

            Date toDate = df.parse(cocpToDate);
            cstmt.setDate(2, new java.sql.Date(toDate.getTime()));

        } else {
            cstmt.setNull(2, java.sql.Types.DATE);
        }
        if (custSegment != null && !"".equals(custSegment)) {
            cstmt.setString(3, custSegment.trim().toUpperCase());
        } else {
            cstmt.setNull(3, java.sql.Types.VARCHAR);
        }

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

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

        rs = cstmt.executeQuery();
        while (rs.next()) {
            objReportsDto = new DocumentMatrixReportDTO();
            objReportsDto.setAccountNo(rs.getString("CRMACCOUNTNO"));
            objReportsDto.setAccountName(rs.getString("ACCOUNTNAME"));
            objReportsDto.setAccntMgr(rs.getString("ACCT_MGR_NAME"));
            objReportsDto.setCopcApprovr(rs.getString("COPC_APPROVER_NAME"));
            objReportsDto.setCopcApprovrID(rs.getString("COPC_APPROVER_ID"));
            objReportsDto.setCopcAprDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objReportsDto.setCopcAprDate(
                        (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }
            objReportsDto.setCopcApprovrRemarks(rs.getString("COPC_APPROVER_REMARKS"));
            objReportsDto.setCustSegment(rs.getString("CUST_SEGMENT_CODE"));
            objReportsDto.setLsiNo(rs.getString("LOGICAL_SI_NO"));
            objReportsDto.setOrderNo(rs.getString("ORDERNO"));
            objReportsDto.setOrdSubType(rs.getString("NAME_SUBTYPE"));
            objReportsDto.setOrdType(rs.getString("ORDERTYPE"));
            objReportsDto.setRegion(rs.getString("REGIONNAME"));
            objReportsDto.setSalesCo(rs.getString("SALES_CORD_NAME"));
            objReportsDto.setServiceNo(rs.getString("SERVICEID"));
            objReportsDto.setStage(rs.getString("STAGE"));
            objReportsDto.setDocCAF((rs.getBoolean("CAF")) ? "Yes" : "No");
            objReportsDto.setDocCustAgreemnt((rs.getBoolean("Customer_Agreement")) ? "Yes" : "No");
            objReportsDto.setDocFeasibility((rs.getBoolean("Feasibility")) ? "Yes" : "No");
            objReportsDto.setDocISP((rs.getBoolean("ISP")) ? "Yes" : "No");
            objReportsDto.setDocNtwrk((rs.getBoolean("Network_Diagram")) ? "Yes" : "No");
            objReportsDto.setDocOFS((rs.getBoolean("OFS")) ? "Yes" : "No");
            objReportsDto.setDocOther((rs.getBoolean("OTHERS")) ? "Yes" : "No");
            objReportsDto.setDocPCD((rs.getBoolean("PCD")) ? "Yes" : "No");
            objReportsDto.setDocPCN((rs.getBoolean("e_PCN_NFA")) ? "Yes" : "No");
            objReportsDto.setDocPO((rs.getBoolean("PO_Order_form")) ? "Yes" : "No");
            objReportsDto.setDocRFP((rs.getBoolean("RFP")) ? "Yes" : "No");
            objReportsDto.setDocSOW((rs.getBoolean("SOW")) ? "Yes" : "No");
            objReportsDto.setDocThirdParty((rs.getBoolean("PI_Third_Party_Related")) ? "Yes" : "No");
            objReportsDto.setDocTnC((rs.getBoolean("TandC")) ? "Yes" : "No");
            objReportsDto.setDocLOU((rs.getBoolean("LOU")) ? "Yes" : "No");
            objReportsDto.setDocAnyDeviation((rs.getBoolean("Any_deviation")) ? "Yes" : "No");
            objReportsDto.setDocNonIndiaTouchLink((rs.getBoolean("NonIndia_touch_link")) ? "Yes" : "No");
            //[132]
            objReportsDto.setDocPMTA((rs.getBoolean("PMTA")) ? "Yes" : "No");

            docListDetails.add(objReportsDto);
            if (pagingSorting.isPagingToBeDone() && recordCount == 0) {
                recordCount = rs.getInt("FULL_REC_COUNT");

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

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

            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
        }
    }

    return docListDetails;

}

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

public ArrayList<PendingServicesReportDTO> viewPendingServicesReport(PendingServicesReportDTO objDto) {
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;/*from   ww  w.  j a  v a  2 s.c o m*/
    ArrayList<PendingServicesReportDTO> listSearchDetails = new ArrayList<PendingServicesReportDTO>();
    PendingServicesReportDTO objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    //      Nagarjuna
    String methodName = "viewPendingServicesReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end

    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())) {
            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.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 PendingServicesReportDTO();
            objReportsDto.setParty_no(rs.getInt("Party_NO"));
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            //vijay replace CRMACCOUNTNO from  ACCOUNTNAME
            objReportsDto.setCrmAccountNoString(rs.getString("ACCOUNTNAME"));
            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"));
            objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objReportsDto.setCurrencyName(rs.getString("CURNAME"));
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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