List of usage examples for java.sql CallableStatement executeQuery
ResultSet executeQuery() throws SQLException;
PreparedStatement object and returns the ResultSet object generated by the query. From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<NetworkLocationDto> viewNetworkLocsList(NetworkLocationDto objDto) throws Exception { //Nagarjuna/*from ww w . j a v a2 s.c o m*/ String methodName = "viewNetworkLocsList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<NetworkLocationDto> objUserList = new ArrayList<NetworkLocationDto>(); Connection conn = null; ResultSet rs = null; CallableStatement getNetworkLocs = null; try { String userName = ""; conn = DbConnection.getReportsConnectionObject(); getNetworkLocs = conn.prepareCall(sqlGetNetworkLocReport); String networkLocationIdStr = objDto.getSearchNetworkLocationIdStr(); String contactNameStr = objDto.getSearchContactNameStr(); if (networkLocationIdStr == null || networkLocationIdStr.trim().equals("")) { getNetworkLocs.setNull(1, java.sql.Types.BIGINT); } else { getNetworkLocs.setLong(1, Long.parseLong(networkLocationIdStr)); } if (contactNameStr == null || contactNameStr.trim().equals("")) { getNetworkLocs.setNull(2, java.sql.Types.VARCHAR); } else { getNetworkLocs.setString(2, contactNameStr); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index getNetworkLocs.setString(3, pagingSorting.getSortByColumn());// columnName getNetworkLocs.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order getNetworkLocs.setInt(5, pagingSorting.getStartRecordId());// start // index getNetworkLocs.setInt(6, pagingSorting.getEndRecordId());// end // index getNetworkLocs.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = getNetworkLocs.executeQuery(); int countFlag = 0; int recordCount = 0; while (rs.next() != false) { countFlag++; userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME")); objDto = new NetworkLocationDto(); objDto.setCustomerName(userName); objDto.setNetworkLocationId("" + rs.getInt("LOCATION_CODE")); objDto.setFirstname(rs.getString("FNAME")); objDto.setLastName(rs.getString("LNAME")); objDto.setTelephonePhno(rs.getString("TELEPHONENO")); objDto.setEmail_Id(rs.getString("EMAIL_ID")); objDto.setAddress1(rs.getString("ADDRESS1")); objDto.setAddress2(rs.getString("ADDRESS2")); objDto.setAddress3(rs.getString("ADDRESS3")); objDto.setAddress4(rs.getString("ADDRESS4")); objDto.setFax(rs.getString("FAX")); // objDto.setPin(rs.getString("PIN")); objDto.setTitle(rs.getString("TITLE")); objDto.setPostalCode(rs.getString("POSTAL_CODE")); objDto.setCityName(rs.getString("CITY_NAME")); objDto.setStateName(rs.getString("STATE_NAME")); objDto.setCountryName(rs.getString("COUNTRY_NAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(getNetworkLocs); DbConnection.freeConnection(conn); } catch (Exception e) { //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<PendingOrderAndBillingReportDTO> viewPendingOrderAndBillingList( PendingOrderAndBillingReportDTO objDto) throws Exception { //Nagarjuna//from w w w . j a v a 2 s . c om String methodName = "viewPendingOrderAndBillingList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<PendingOrderAndBillingReportDTO> objUserList = new ArrayList<PendingOrderAndBillingReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; PendingOrderAndBillingReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlPendingOrderAndBillingReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getLOC_Date() != null && !"".equals(objDto.getLOC_Date())) { proc.setString(2, objDto.getLOC_Date()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(3, objDto.getFromAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(4, objDto.getToAccountNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(5, formattedDate); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(6, formattedDate1); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(7, objDto.getFromOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(8, objDto.getToOrderNo()); } else { proc.setNull(8, java.sql.Types.BIGINT); } if (objDto.getActmngname() != null && !"".equals(objDto.getActmngname())) { proc.setString(9, objDto.getActmngname().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(10, pagingSorting.getSortByColumn());// columnName proc.setString(11, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(12, pagingSorting.getStartRecordId());// start index proc.setInt(13, pagingSorting.getEndRecordId());// end index proc.setInt(14, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index proc.setInt(15, objDto.getIsUsage());// end if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(16, objDto.getOsp().trim()); } else { proc.setNull(16, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objRDto = new PendingOrderAndBillingReportDTO(); objRDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objRDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objRDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objRDto.setOrderNumber(rs.getInt("ORDERNO")); objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objRDto.setCustPoDate(rs.getString("CUSTPODATE")); /*if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date=df.parse(objRDto.getCustPoDate()); objRDto.setCustPoDate((Utility.showDate_Report(date)).toUpperCase()); }*/ tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null && !"".equals(tempDate)) { objRDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objRDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objRDto.setCurrencyName(rs.getString("CURRENCYNAME")); objRDto.setOrderType(rs.getString("ORDERTYPE")); objRDto.setLOC_Date(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objRDto.getLOC_Date()); objRDto.setLOC_Date((Utility.showDate_Report(date)).toUpperCase()); } objRDto.setPm_pro_date(rs.getString("PM_PROV_DATE")); if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) { String s1 = rs.getString("PM_PROV_DATE"); String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objRDto.setPm_pro_date(s5); } tempDate = rs.getDate("ORDERDATE"); objRDto.setOrderDate(rs.getString("ORDERDATE")); if (tempDate != null && !"".equals(tempDate)) { objRDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setPoNumber(rs.getInt("PONUMBER")); objRDto.setUom(rs.getString("UOM")); objRDto.setBandwaidth(rs.getString("BANDWIDTH")); objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); // Meenakshi : Changes for Usage if (objDto.getIsUsage() == 1) { objRDto.setRegionName(rs.getString("REGION")); objRDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objRDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objRDto.setLine_desc(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setActmngname(rs.getString("ACCOUNTMANAGER")); objRDto.setPrjmngname(rs.getString("PROJECTMANAGER")); objRDto.setCopcApproveDate(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objRDto.setCopcApproveDate( (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } objRDto.setTaxation(rs.getString("TAXATIONVALUE")); objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objRDto.setComponentID(rs.getInt("COMPONENT_ID")); objRDto.setComponentName(rs.getString("COMPONENT_NAME")); objRDto.setComponentType(rs.getString("COMPONENT_TYPE")); objRDto.setComponentRCNRCAmount(rs.getDouble("COMP_AMOUNT")); objRDto.setPackageID(rs.getInt("PACKAGE_ID")); objRDto.setPackageName(rs.getString("PACKAGE_NAME")); objRDto.setChild_act_no(rs.getString("Child_Account_Number")); } else { objRDto.setRegionName(rs.getString("REGIONNAME")); objRDto.setAccountID(rs.getInt("ACCOUNTID")); objRDto.setCrm_productname(rs.getString("CRM_PRODUCT_NAME")); objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objRDto.setCompanyName(rs.getString("COMPANYNAME")); objRDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objRDto.setLinename(rs.getString("LINENAME")); objRDto.setLcompanyname(rs.getString("LICENCECOMPANYNAME")); objRDto.setStorename(rs.getString("STORENAME")); objRDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objRDto.setActmngname(rs.getString("ACTMNAME")); objRDto.setPrjmngname(rs.getString("PMNAME")); objRDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objRDto.setCopcApproveDate( (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objRDto.setTaxation(rs.getString("TAXATION")); objRDto.setOsp(rs.getString("OSP")); objRDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objRDto.setChargeName(rs.getString("CHARGE_NAME")); objRDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objRDto.setFrequencyName(rs.getString("PAYMENTTERM")); objRDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NO")); } /// End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objRDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<M6OrderStatusDto> viewM6OrderList(M6OrderStatusDto objDto) throws Exception { //Nagarjuna//from w ww. j a v a 2 s . com String methodName = "viewM6OrderList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<M6OrderStatusDto> objUserList = new ArrayList<M6OrderStatusDto>(); Connection conn = null; ResultSet rs = null; CallableStatement getOrder = null; try { conn = DbConnection.getReportsConnectionObject(); getOrder = conn.prepareCall(sqlGetM6OrderStatusReport); String accountIdStr = objDto.getSearchAccountIdStr(); String orderNoStr = objDto.getSearchOrderNoStr(); // String m6OrderNoStr = objDto.getSearchM6OrderNoStr(); String accountNameStr = objDto.getSearchAccountNameStr(); if (accountIdStr == null || accountIdStr.trim().equals("")) { getOrder.setNull(1, java.sql.Types.BIGINT); } else { getOrder.setLong(1, Long.parseLong(accountIdStr)); } if (orderNoStr == null || orderNoStr.trim().equals("")) { getOrder.setNull(2, java.sql.Types.BIGINT); } else { getOrder.setLong(2, Long.parseLong(orderNoStr)); } if (accountNameStr == null || accountNameStr.trim().equals("")) { getOrder.setNull(3, java.sql.Types.VARCHAR); } else { getOrder.setString(3, accountNameStr); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index getOrder.setString(4, pagingSorting.getSortByColumn());// columnName getOrder.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order getOrder.setInt(6, pagingSorting.getStartRecordId());// start index getOrder.setInt(7, pagingSorting.getEndRecordId());// end index getOrder.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = getOrder.executeQuery(); int countFlag = 0; int recordCount = 0; while (rs.next() != false) { countFlag++; objDto = new M6OrderStatusDto(); objDto.setAccountID("" + rs.getInt("ACCOUNTID")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setOrderNo("" + rs.getInt("OrderNO")); objDto.setM6OrderNo("" + rs.getInt("M6OrderNO")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(getOrder); 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<UserAccessMatrixDto> viewAccessMatrixReport(UserAccessMatrixDto objDto) { //start//from w w w. ja va2 s .c o m 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<DispatchAddressDto> viewDispatchAddressList(DispatchAddressDto objDto) throws Exception { //Nagarjuna//from w w w .j a va2 s . c om String methodName = "viewDispatchAddressList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<DispatchAddressDto> objUserList = new ArrayList<DispatchAddressDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlDispatchList); if (objDto.getDispatchAddressId() != null && !"".equals(objDto.getDispatchAddressId())) { proc.setLong(2, Long.parseLong(objDto.getDispatchAddressId())); } else { proc.setNull(2, java.sql.Types.BIGINT); } if (objDto.getAccountID() != null && !"".equals(objDto.getAccountID())) { proc.setLong(1, Long.parseLong(objDto.getAccountID())); } else { proc.setNull(1, java.sql.Types.BIGINT); } if (objDto.getDispatchAddressName() != null && !"".equals(objDto.getDispatchAddressName())) { proc.setString(3, objDto.getDispatchAddressName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getAccountName() != null || !objDto.getAccountName().trim().equals("")) { proc.setString(4, objDto.getAccountName()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; int recordCount = 0; while (rs.next() != false) { countFlag++; String userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME")); objDto = new DispatchAddressDto(); objDto.setCustomerName(userName); objDto.setAccountID(String.valueOf(rs.getInt("ACCOUNTID"))); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setDispatchAddressId(String.valueOf(rs.getInt("DISPATCH_ADDRESS_CODE"))); objDto.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objDto.setTelephonePhno(rs.getString("TELEPHONENO")); objDto.setEmail_Id(rs.getString("EMAIL_ID")); objDto.setAddress1(rs.getString("ADDRESS1")); objDto.setAddress2(rs.getString("ADDRESS2")); objDto.setAddress3(rs.getString("ADDRESS3")); objDto.setAddress4(rs.getString("ADDRESS4")); objDto.setFax(rs.getString("FAX")); objDto.setPin(rs.getString("PIN")); objDto.setTitle(rs.getString("TITLE")); objDto.setPostalCode(rs.getString("POSTAL_CODE")); objDto.setCountryName(rs.getString("COUNTRY_NAME")); objDto.setStateName(rs.getString("STATE_NAME")); objDto.setCityName(rs.getString("CITY_NAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<LocationDetailDto> viewCustomerLocationList(LocationDetailDto objDto) throws Exception { //Nagarjuna/*ww w . j ava2 s . c om*/ String methodName = "viewCustomerLocationList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<LocationDetailDto> objUserList = new ArrayList<LocationDetailDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; try { String userName = ""; conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlGetCustomerLocReport); if (objDto.getLocationId() != null && !"".equals(objDto.getLocationId())) { proc.setLong(2, Long.parseLong(objDto.getLocationId())); } else { proc.setNull(2, java.sql.Types.BIGINT); } if (objDto.getAccountID() != null && !"".equals(objDto.getAccountID())) { proc.setLong(1, Long.parseLong(objDto.getAccountID())); } else { proc.setNull(1, java.sql.Types.BIGINT); } if (objDto.getLocationName() != null && !"".equals(objDto.getLocationName())) { proc.setString(3, objDto.getLocationName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getAccountName() != null && !"".equals(objDto.getAccountName())) { proc.setString(4, objDto.getAccountName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME")); objDto = new LocationDetailDto(); objDto.setCustomerName(userName); objDto.setAccountID(String.valueOf(rs.getInt("ACCOUNTID"))); objDto.setLocationId(String.valueOf(rs.getInt("LOCATION_CODE"))); objDto.setLocationName(rs.getString("LOCATION_NAME")); objDto.setTelephonePhno(rs.getString("TELEPHONENO")); objDto.setEmail_Id(rs.getString("EMAIL_ID")); objDto.setAddress1(rs.getString("ADDRESS1")); objDto.setAddress2(rs.getString("ADDRESS2")); objDto.setAddress3(rs.getString("ADDRESS3")); objDto.setAddress4(rs.getString("ADDRESS4")); objDto.setFax(rs.getString("FAX")); objDto.setPin(rs.getString("PIN")); objDto.setTitle(rs.getString("TITLE")); objDto.setCountryName(rs.getString("COUNTRY_NAME")); objDto.setPostalCode(rs.getString("POSTAL_CODE")); objDto.setStateName(rs.getString("STATE_NAME")); objDto.setCityName(rs.getString("CITY_NAME")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
/** * //from w w w .j av a2 s . c o m * @param objDto * @return * @throws Exception */ public ArrayList<OrderReportNewDetailCwnDTO> viewOrderReportNew(OrderReportNewDetailCwnDTO objDto) throws Exception { //Nagarjuna String methodName = "viewOrderReportNew", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<OrderReportNewDetailCwnDTO> objUserList = new ArrayList<OrderReportNewDetailCwnDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OrderReportNewDetailCwnDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOrderReportNewForUsage); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index proc.setInt(10, (objDto.getIsUsage()));// end if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(11, objDto.getOsp().trim()); } else { proc.setNull(11, java.sql.Types.VARCHAR); } if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(12, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(12, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(13, formattedDate1); } else { proc.setNull(13, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objRDto = new OrderReportNewDetailCwnDTO(); objRDto.setPartyName(rs.getString("PARTYNAME")); objRDto.setOrderNo(rs.getString("ORDERNO")); //objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));LOGICAL_SI_NO(Commented By :AKS) //objRDto.setServiceId(rs.getInt("SERVICENO"));SERVICEID(Commented By :AKS) objRDto.setQuoteNo(rs.getString("QUOTENO")); objRDto.setProductName(rs.getString("PRODUCTNAME")); objRDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); // objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION // objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION //objRDto.setPrjmngname(rs.getString("PMNAME"));(Commented By :AKS)PROJECTMANAGER objRDto.setPrjmgremail(rs.getString("PMEMAIL"));//PMEMAIL:same:(AKS)Need To Add in Component View //objRDto.setActmngname(rs.getString("ACTMNAME"));ACCOUNTMANAGER objRDto.setZoneName(rs.getString("ZONENNAME"));//(AKS)Need To Add in Component View //objRDto.setRegionName(rs.getString("REGIONNAME"));REGION objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objRDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); //change tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objRDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("AM_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setAmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("PM_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setPmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("NIO_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setNio_approve_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objRDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); /*objRDto.setRfs_date(rs.getString("RFS_DATE"));SERVICE_RFS_DATE if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objRDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))).toUpperCase()); }*/ //objRDto.setOrdercategory(rs.getString("ORDERCATEGORY"));ORDERTYPE(Commented By :AKS) objRDto.setOrderStatus(rs.getString("STATUS")); //objRDto.setLinename(rs.getString("LINENAME"));SERVICEDETDESCRIPTION(Commented By :AKS) //objRDto.setServiceProductID(rs.getInt("LINENO"));Order_Line_Id(Commented By :AKS) objRDto.setServiceName(rs.getString("SERVICENAME")); //objRDto.setAccountID(rs.getInt("ACCOUNTID"));CRMACCOUNTNO(Commented By :AKS) objRDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); //objRDto.setEntity(rs.getString("COMPANYNAME"));ENTITYNAME objRDto.setServiceType(rs.getString("SERVICETYPE")); objRDto.setUom(rs.getString("UOM")); objRDto.setBandwaidth(rs.getString("BANDWIDTH")); objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objRDto.setDistance(rs.getString("DISTANCE")); //objRDto.setFrom_city(rs.getString("FROM_CITY"));//(AKS)Need To Add in Component View if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { String ss[] = rs.getString("FROM_CITY").split("~~"); objRDto.setFrom_city(ss[8]); } else { objRDto.setFrom_city(rs.getString("FROM_CITY")); } //objRDto.setTo_city(rs.getString("TO_CITY"));//(AKS)Need To Add in Component View if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { String ss[] = rs.getString("TO_CITY").split("~~"); objRDto.setTo_city(ss[8]); } else { objRDto.setTo_city(rs.getString("TO_CITY")); } objRDto.setRatio(rs.getString("RATIO")); objRDto.setTaxation(rs.getString("TAXATIONVALUE")); objRDto.setAccountManager(rs.getString("ACTMEMAIL"));//(AKS)Need To Add in Component View //objRDto.setCurrencyCode(rs.getString("CURNAME"));CURRENCYNAME objRDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); //objRDto.setPoAmount(rs.getString("POAMOUNT"));TOTALPOAMOUNT objRDto.setBisource(rs.getString("BISOURCE")); objRDto.setOrderType(rs.getString("ORDERTYPE")); //objRDto.setParent_name(rs.getString("PARENTNAME"));PARENT_NAME objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objRDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); //Saurabh : Changes to separate charge related specific column from common if (objDto.getIsUsage() == 0) { objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objRDto.setFrequencyName(rs.getString("PAYMENTTERM")); objRDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objRDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objRDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objRDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objRDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objRDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objRDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objRDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objRDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); //Start: AKS- Adding Column in Chanrge Section Which are not in View objRDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objRDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objRDto.setServiceId(rs.getInt("SERVICENO")); objRDto.setPrjmngname(rs.getString("PMNAME")); objRDto.setActmngname(rs.getString("ACTMNAME")); objRDto.setRegionName(rs.getString("REGIONNAME")); objRDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objRDto.setRfs_date( (utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objRDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objRDto.setLinename(rs.getString("LINENAME")); objRDto.setServiceProductID(rs.getInt("LINENO")); objRDto.setAccountID(rs.getInt("ACCOUNTID")); objRDto.setEntity(rs.getString("COMPANYNAME")); objRDto.setCurrencyCode(rs.getString("CURNAME")); objRDto.setPoAmount(rs.getString("POAMOUNT")); objRDto.setParent_name(rs.getString("PARENTNAME")); objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION objRDto.setCustPoDate(rs.getString("CUST_PODATE")); if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) { objRDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUST_PODATE").getTime()))) .toUpperCase()); } //End AKS } //Meenakshi : Changes for Usage if (objDto.getIsUsage() == 1) { objRDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));//FX_ACCOUNT_EXTERNAL_ID objRDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objRDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));//CHILD_ACCOUNT_FX_STATUS objRDto.setPackageID(rs.getInt("PACKAGE_ID")); objRDto.setPackageName(rs.getString("PACKAGE_NAME")); objRDto.setComponentID(rs.getInt("COMPONENT_ID")); objRDto.setComponentName(rs.getString("COMPONENT_NAME")); objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); //Start Adding Column in Component Section Which are present in View ts = rs.getTimestamp("COPC_APPROVED_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setCopcApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO")); objRDto.setServiceId(rs.getInt("SERVICE_NO")); objRDto.setPrjmngname(rs.getString("PROJECTMANAGER")); objRDto.setActmngname(rs.getString("ACCOUNTMANAGER")); objRDto.setRegionName(rs.getString("REGION")); tempDate = rs.getDate("SERVICE_RFS_DATE"); if (tempDate != null) { objRDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setOrdercategory(rs.getString("ORDERTYPE")); objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setServiceProductID(rs.getInt("Order_Line_Id")); objRDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objRDto.setEntity(rs.getString("ENTITYNAME")); objRDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objRDto.setPoAmount(rs.getString("TOTALPOAMOUNT")); objRDto.setParent_name(rs.getString("PARENT_NAME")); objRDto.setPrimarylocation(VAR_PRIMARYLOCATION);//AKS:Need To Add in Component View:PRIMARYLOCATION objRDto.setSeclocation(VAR_SECONDARYLOCATION);//AKS:Need To Add in Component View:SECONDARYLOCATION objRDto.setRE_LOGGED_LSI_NO(rs.getString("RE_LOGGED_LSI_NO")); objRDto.setPARALLEL_UPGRADE_LSI_NO(rs.getString("PARALLEL_UPGRADE_LSI_NO")); objRDto.setCHARGEDISCONNECTIONSTATUS(rs.getString("CHARGEDISCONNECTIONSTATUS")); objRDto.setSubchange_type(rs.getString("NAME_SUBTYPE")); objRDto.setFxAccountExternalId(rs.getString("CHILD_ACCOUNT_NUMBER")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objRDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } //End : AKS ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); //nagarjuna OB Value Usage objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); objDto.setObValueLastUpdateDate(rs.getString("OB_VALUE_LAST_UPDATE_DATE")); //nagarjuna OB Value Usage END //<!--GlobalDataBillingEfficiency BFR7 --> objRDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); objRDto.setLastApprovalRemarks(rs.getString("LAST_APPROVAL_REMARKS")); //NANCY objRDto.setePCNNo(rs.getString("EPCN_NO")); objRDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objRDto.setBillingTriggerCreateDate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objRDto.setComponentDto(dto); } objRDto.setOsp(rs.getString("OSP")); objRDto.setCustSeg_Description(rs.getString("DESCRIPTION")); /// End objRDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objRDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<BCPAddressDto> viewBCPList(BCPAddressDto objDto) throws Exception { //Nagarjuna/* w w w . ja v a 2s .c om*/ String methodName = "viewBCPList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<BCPAddressDto> objUserList = new ArrayList<BCPAddressDto>(); Connection conn = null; ResultSet rs = null; CallableStatement getBCP = null; try { String userName = ""; conn = DbConnection.getReportsConnectionObject(); getBCP = conn.prepareCall(sqlGetBCPReport); String accountIdStr = objDto.getSearchAccountIdStr(); String bcpIdStr = objDto.getSearchBcpIdStr(); String bcpNameStr = objDto.getSearchBcpNameStr(); String accountNameStr = objDto.getSearchAccountNameStr(); if (accountIdStr == null || accountIdStr.trim().equals("")) { getBCP.setNull(1, java.sql.Types.BIGINT); } else { getBCP.setLong(1, Long.parseLong(accountIdStr)); } if (bcpIdStr == null || bcpIdStr.trim().equals("")) { getBCP.setNull(2, java.sql.Types.BIGINT); } else { getBCP.setLong(2, Long.parseLong(bcpIdStr)); } if (bcpNameStr == null || bcpNameStr.trim().equals("")) { getBCP.setNull(3, java.sql.Types.VARCHAR); } else { getBCP.setString(3, bcpNameStr); } if (accountNameStr == null || accountNameStr.trim().equals("")) { getBCP.setNull(4, java.sql.Types.VARCHAR); } else { getBCP.setString(4, accountNameStr); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index getBCP.setString(5, pagingSorting.getSortByColumn());// columnName getBCP.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order getBCP.setInt(7, pagingSorting.getStartRecordId());// start index getBCP.setInt(8, pagingSorting.getEndRecordId());// end index getBCP.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = getBCP.executeQuery(); int countFlag = 0; int recordCount = 0; while (rs.next() != false) { countFlag++; userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME")); objDto = new BCPAddressDto(); objDto.setCustomerName(userName); objDto.setAccountID("" + rs.getInt("ACCOUNTID")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setBCPId("" + rs.getInt("BCP_ID")); objDto.setFirstname(rs.getString("FNAME")); objDto.setLastName(rs.getString("LNAME")); objDto.setTelephonePhno(rs.getString("TELEPHONENO")); objDto.setEmail_Id(rs.getString("EMAIL_ID")); objDto.setAddress1(rs.getString("ADDRESS1")); objDto.setAddress2(rs.getString("ADDRESS2")); objDto.setAddress3(rs.getString("ADDRESS3")); objDto.setAddress4(rs.getString("ADDRESS4")); objDto.setFax(rs.getString("FAX")); // objDto.setPin(rs.getString("PIN")); objDto.setTitle(rs.getString("TITLE")); objDto.setPostalCode(rs.getString("POSTAL_CODE")); objDto.setCityName(rs.getString("CITY_NAME")); objDto.setStateName(rs.getString("STATE_NAME")); objDto.setCountryName(rs.getString("COUNTRY_NAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(getBCP); 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<NewOrderDto> viewContactList(NewOrderDto objDto) throws Exception { //Nagarjuna//from www .j a va2 s . c o m String methodName = "viewContactList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<NewOrderDto> objUserList = new ArrayList<NewOrderDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; try { String userName = ""; conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlContactList); if (objDto.getAccountID() != 0) { proc.setLong(1, (objDto.getAccountID())); } else { proc.setNull(1, java.sql.Types.BIGINT); } if (objDto.getContactType() != null && !"".equals(objDto.getContactType())) { proc.setString(2, (objDto.getContactType())); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getContactName() != null && !"".equals(objDto.getContactName())) { proc.setString(3, objDto.getContactName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getAccountName() != null && !"".equals(objDto.getAccountName())) { proc.setString(4, objDto.getAccountName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getOrderNumber() != 0) { proc.setLong(5, (objDto.getOrderNumber())); } else { proc.setNull(5, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(6, pagingSorting.getSortByColumn());// columnName proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(8, pagingSorting.getStartRecordId());// start index proc.setInt(9, pagingSorting.getEndRecordId());// end index proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; userName = (rs.getString("FIRSTNAME")) + " " + (rs.getString("LASTNAME")); objDto = new NewOrderDto(); objDto.setContactName(userName); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setContactId(rs.getLong("CONTACTID")); objDto.setContactType(rs.getString("CONTACTTYPE")); objDto.setSaluation(rs.getString("SALUATION")); objDto.setCntEmail(rs.getString("EMAIL")); objDto.setContactCell(rs.getString("CELLNO")); objDto.setContactFax(rs.getString("FAXNO")); objDto.setAddress1(rs.getString("ADDRESS1")); objDto.setAddress2(rs.getString("ADDRESS2")); objDto.setAddress3(rs.getString("ADDRESS3")); objDto.setCityName(rs.getString("CITY")); objDto.setStateName(rs.getString("STATE")); objDto.setCountyName(rs.getString("COUNTRY")); objDto.setPinNo(rs.getString("PINCODE")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * Method::viewDocumentMatrixReport/* ww w . ja v a 2s. 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; }