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
/** * Method:fetchServiceTypeName/*from ww w .j a v a 2s . com*/ * @author Anil Kumar * @return */ public ArrayList<DummyLinesDetailsReportDTO> fetchServiceTypeName() { Connection connection = null; CallableStatement csServiceTypeName = null; ResultSet rsServiceTypeName = null; ArrayList<DummyLinesDetailsReportDTO> listServicetypename = new ArrayList<DummyLinesDetailsReportDTO>(); DummyLinesDetailsReportDTO objDto = null; String sqlFetchServiceTypeName = "SELECT SERVICETYPEID,SERVICETYPENAME FROM ioe.TSERVICETYPE where ISACTIVE=1 and SERVICETYPEID in(122,411,412,413,381,431,221)"; try { connection = DbConnection.getConnectionObject(); csServiceTypeName = connection.prepareCall(sqlFetchServiceTypeName); rsServiceTypeName = csServiceTypeName.executeQuery(); while (rsServiceTypeName.next()) { objDto = new DummyLinesDetailsReportDTO(); objDto.setServicetypeid(rsServiceTypeName.getString("SERVICETYPEID")); objDto.setServicename(rsServiceTypeName.getString("SERVICETYPENAME")); listServicetypename.add(objDto); } return listServicetypename; } catch (Exception ex) { Utility.LOG(true, false, ex, "::Exception occured while fetching service type name in method fetchServiceTypeName::block1"); } finally { try { DbConnection.closeResultset(rsServiceTypeName); DbConnection.closeCallableStatement(csServiceTypeName); DbConnection.freeConnection(connection); } catch (SQLException e) { // TODO Auto-generated catch block Utility.LOG(true, false, e, "::Exception occured while fetching service type name in method fetchServiceTypeName::block2"); } catch (Exception ex) { Utility.LOG((ex.getMessage() + " Exception occured while fetching service type name in method fetchServiceTypeName::block3")); } } return listServicetypename; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<DisconnectChangeOrdeReportDTO> facthCustomerSegmentForDisconChaneReportDetails() { Connection connection = null; CallableStatement customerSegment = null; ResultSet rsCustomerSegment = null; ArrayList<DisconnectChangeOrdeReportDTO> listCustomerSegmrntforDisconChangeOdr = new ArrayList<DisconnectChangeOrdeReportDTO>(); DisconnectChangeOrdeReportDTO objDtoo = null; String customer_segment = ""; String sqlFetchServiceTypeName = "SELECT CUST_SEGMENT_ID,CUST_SEGMENT_CODE FROM IOE.TM_CUSTOMER_SEGMENT_MASTER"; try {// ww w .j av a 2s . c o m connection = DbConnection.getConnectionObject(); customerSegment = connection.prepareCall(sqlFetchServiceTypeName); rsCustomerSegment = customerSegment.executeQuery(); while (rsCustomerSegment.next()) { objDtoo = new DisconnectChangeOrdeReportDTO(); objDtoo.setCustomerSegmentId(rsCustomerSegment.getInt("CUST_SEGMENT_ID")); objDtoo.setCus_segment(rsCustomerSegment.getString("CUST_SEGMENT_CODE")); listCustomerSegmrntforDisconChangeOdr.add(objDtoo); } return listCustomerSegmrntforDisconChangeOdr; } catch (Exception ex) { Utility.LOG(true, false, ex, "::Exception occured while fetching customer Sigment name in method facthCustomerSegmentDetails::block1"); } finally { try { DbConnection.closeResultset(rsCustomerSegment); DbConnection.closeCallableStatement(customerSegment); DbConnection.freeConnection(connection); } catch (SQLException e) { // TODO Auto-generated catch block Utility.LOG(true, false, e, "::Exception occured while fetching customer Sigment name in method facthCustomerSegmentDetails::block2"); } catch (Exception ex) { Utility.LOG((ex.getMessage() + " Exception occured while fetching customer Sigment name in method facthCustomerSegmentDetails::block3")); } } return listCustomerSegmrntforDisconChangeOdr; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ReportsDto> getRequestHistory(ReportsDto objDto) //Method used to display Hardware Line items for Cancelation { // Nagarjuna String methodName = "getRequestHistory", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*from w w w . j ava2 s. c o m*/ Connection connection = null; CallableStatement getRequestHistory = null; ResultSet rsRequestHistory = null; ArrayList<ReportsDto> requestHistoryList = new ArrayList<ReportsDto>(); ReportsDto objReportsDTO = null; int recordCount = 0; try { connection = DbConnection.getConnectionObject(); getRequestHistory = connection.prepareCall(sqlRequestHistory); //[505053] start try { getRequestHistory.setInt(1, Integer.parseInt(objDto.getSrno())); } catch (Exception exp) { //It's okay to ignore 'exp' here because setting a default value getRequestHistory.setInt(1, AppConstants.INACTIVE_FLAG); //set 0 } rsRequestHistory = getRequestHistory.executeQuery(); while (rsRequestHistory.next()) { objReportsDTO = new ReportsDto(); objReportsDTO.setSrno(rsRequestHistory.getString("REQUESTID")); objReportsDTO.setLineno(rsRequestHistory.getInt("LINEITEMNO")); objReportsDTO.setOrderNumber(rsRequestHistory.getInt("ORDERNO")); objReportsDTO.setM6OrderNumber(rsRequestHistory.getInt("M6ORDERNO")); objReportsDTO.setNeworder_remarks(rsRequestHistory.getString("REMARKS")); objReportsDTO.setSrDate(rsRequestHistory.getString("CREATEDATE")); requestHistoryList.add(objReportsDTO); } } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rsRequestHistory); DbConnection.closeCallableStatement(getRequestHistory); 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 requestHistoryList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<NonAPP_APPChangeOrderDetailsDTO> viewNonMigAppUnappNewOrderDetails( NonAPP_APPChangeOrderDetailsDTO objDto) { //Nagarjuna/*from w ww .j a va 2 s.c om*/ String methodName = "viewNonMigAppUnappNewOrderDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<NonAPP_APPChangeOrderDetailsDTO> listSearchDetails = new ArrayList<NonAPP_APPChangeOrderDetailsDTO>(); NonAPP_APPChangeOrderDetailsDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetNonMigAppUnappNewOrderDetails); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getApprovalType() != null && !"".equals(objDto.getApprovalType())) { proc.setString(2, objDto.getApprovalType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceOrderType() != null && !"".equals(objDto.getServiceOrderType())) { proc.setInt(3, Integer.parseInt(objDto.getServiceOrderType().trim())); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(4, objDto.getOrdermonth().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(5, objDto.getVerticalDetails().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(6, objDto.getServiceName().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(7, objDto.getFromOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(8, objDto.getToOrderNo()); } else { proc.setNull(8, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(14, objDto.getIsUsage()); if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) { proc.setString(15, objDto.getOrderyear().trim()); } else { proc.setNull(15, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); while (rs.next()) { setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objReportsDto = new NonAPP_APPChangeOrderDetailsDTO(); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//LineName objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setKmsDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); // change objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION); objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PONUMBER")); // change tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // change tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setPoReceiveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); // change tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase()); } //Bill Trg Create Date objReportsDto.setPmApproveDate(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); //Business Serial No //Opms Account Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber //Order Month objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); if (objDto.getIsUsage() == 1) { objReportsDto.setOrderStage(rs.getString("ORDERSTAGE")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); ComponentsDto dto = new ComponentsDto(); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("COMPONENT_END_DATE")); if (rs.getString("COMPONENT_END_DATE") != null && !"".equals(rs.getString("COMPONENT_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((Utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); //dto.setEndFxStatus(rs.getString("FX_END_COMPONENT_STATUS")); //dto.setComponentFXStatus(rs.getString("FX_STATUS")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); objReportsDto.setComponentDto(dto); } else { objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE"));//Type Of Sale objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<M6OrderStatusDto> viewM6ResponseHistory(M6OrderStatusDto objDto, long m6OrderNo) { //Nagarjuna// w ww . j a va 2 s . co m String methodName = "viewM6ResponseHistory", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Connection connection = null; CallableStatement getM6ResponseHistory = null; ResultSet rsM6ResponseHistory = null; ArrayList<M6OrderStatusDto> listM6ResponseHistory = new ArrayList<M6OrderStatusDto>(); M6OrderStatusDto objNewOrderDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); getM6ResponseHistory = connection.prepareCall(sqlGetM6ResponseHistoryReport); if (m6OrderNo != 0) { getM6ResponseHistory.setLong(1, m6OrderNo); } else { getM6ResponseHistory.setNull(1, java.sql.Types.BIGINT); } // getM6ResponseHistory.setLong(1, Intm6OrderNo); rsM6ResponseHistory = getM6ResponseHistory.executeQuery(); while (rsM6ResponseHistory.next()) { objNewOrderDto = new M6OrderStatusDto(); objNewOrderDto.setOrderNo(rsM6ResponseHistory.getString("orderNo")); objNewOrderDto.setM6OrderNo(String.valueOf(m6OrderNo)); objNewOrderDto.setRemarks(rsM6ResponseHistory.getString("Reason")); objNewOrderDto.setCreatedDate((rsM6ResponseHistory.getString("Created_Date"))); if (!(rsM6ResponseHistory.getString("Created_Date") == null || rsM6ResponseHistory.getString("Created_Date") == "")) { objNewOrderDto.setCreatedDate((utility .showDate_Report(new Date(rsM6ResponseHistory.getTimestamp("Created_Date").getTime()))) .toUpperCase()); } listM6ResponseHistory.add(objNewOrderDto); } } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rsM6ResponseHistory); DbConnection.closeCallableStatement(getM6ResponseHistory); 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 listM6ResponseHistory; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<OrderClepReportDTO> viewClepOrderReport(OrderClepReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewClepOrderReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w . j a v a 2s .c om ArrayList<OrderClepReportDTO> objUserList = new ArrayList<OrderClepReportDTO>(); 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(sqlClepOrderReport); PagingSorting pagingSorting = objDto.getPagingSorting(); proc.setInt(1, pagingSorting.getStartRecordId());// start index proc.setInt(2, pagingSorting.getEndRecordId());// end index //proc.setInt(3, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(3, (pagingSorting.isPagingToBeDone() ? 1 : 0)); System.out.println("pagingSorting.isPagingToBeDone() :" + pagingSorting.isPagingToBeDone()); rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; recordCount++; objDto = new OrderClepReportDTO(); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setOrderStage(rs.getString("STAGE")); objDto.setM6OrderDate(rs.getString("CREATEDDATE")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objUserList.add(objDto); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } } objUserList.add(objDto); System.out.println("Total records for clep reports :" + recordCount); pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //System.out.println("SQL Exception : " + ex.getMessage()); //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<BillingWorkQueueReportDTO> viewBillingWorkQueueList(BillingWorkQueueReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewBillingWorkQueueList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<BillingWorkQueueReportDTO> objUserList = new ArrayList<BillingWorkQueueReportDTO>(); Connection conn = null;// w w w. j av a 2 s. c om CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlBillingWorkQueue); proc.setInt(1, java.sql.Types.BIGINT); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(2, objDto.getOrderType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(3, objDto.getFromDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(4, objDto.getToDate().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(5, objDto.getFromOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(6, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } /* * add party no and party name */ if (objDto.getParty_no() != 0 && !"".equals(objDto.getParty_no())) { proc.setInt(7, objDto.getParty_no()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getPartyName() != null && !"".equals(objDto.getPartyName())) { proc.setString(8, objDto.getPartyName().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } if (objDto.getOrderStage() != null && !"".equals(objDto.getOrderStage())) { proc.setString(9, objDto.getOrderStage().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } if (objDto.getHardwareType() != null && !"".equals(objDto.getHardwareType())) { proc.setString(10, objDto.getHardwareType().trim()); } else { proc.setNull(10, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(11, pagingSorting.getSortByColumn());// columnName proc.setString(12, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(13, pagingSorting.getStartRecordId());// start index proc.setInt(14, pagingSorting.getEndRecordId());// end index proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end //proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 0 : 0));// end // index //System.out.println("sqlBillingWorkQueue :"+sqlBillingWorkQueue); rs = proc.executeQuery(); int countFlag = 0; BillingWorkQueueReportDTO objdto; while (rs.next() != false) { countFlag++; // System.out.println("in while roop of rs"); setBlank(); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMLOC")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECLOC")); objdto = new BillingWorkQueueReportDTO(); objdto.setLogicalSINo(rs.getString("LOGICAL_CIRCUIT_ID")); objdto.setCustSINo(rs.getString("CUST_LOGICAL_SI_ID")); objdto.setServiceName(rs.getString("SERVICE_NAME")); objdto.setLinename(rs.getString("LINE_NAME")); // objdto.setChargeTypeName(rs.getString("CHARGE_TYPE")); // objdto.setChargeTypeID(rs.getInt("CHARGE_TYPE_ID")); // objdto.setChargeName(rs.getString("CHARGE_NAME")); // objdto.setChargeFrequency(rs.getString("FREQUENCY")); // objdto.setBillPeriod(rs.getString("BILL_PERIOD")); /* * newly added fields in code */ // objdto.setEndDateLogic(rs.getString("ENDDATELOGIC")); // if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) // { // objdto.setStartDate(rs.getString("CHARGE_START_DATE")); //Date date=df.parse(objdto.getStartDate()); //objdto.setStartDate((utility.showDate_Report(date)).toUpperCase()); // } // if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) // { // objdto.setChargeEndDate(rs.getString("CHARGE_END_DATE")); // } // objdto.setAdvance(rs.getString("ADVANCE")); // objdto.setRate_code(rs.getString("TRAI_RATE")); // objdto.setDiscount(rs.getString("DISCOUNT")); // objdto.setInstallRate(rs.getString("INSTALRATE")); // objdto.setInterestRate(rs.getInt("INTREST_RATE")); // objdto.setPrincipalAmount(rs.getInt("PRINCIPAL_AMOUNT")); objdto.setNoticePeriod(rs.getLong("NOTICEPERIOD")); objdto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objdto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); // if (rs.getString("WARRANTY_START_DATE") != null && !"".equals(rs.getString("WARRANTY_START_DATE"))) // { // objdto.setWarrantyStartDate(rs.getString("WARRANTY_START_DATE")); // objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyStartDate())).toUpperCase()); // } // if (rs.getString("WARRANTY_END_DATE") != null && !"".equals(rs.getString("WARRANTY_END_DATE"))) // { // objdto.setWarrantyEndDate(rs.getString("WARRANTY_END_DATE")); // objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyEndDate())).toUpperCase()); // } // objdto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); objdto.setContractStartDate(rs.getString("CONTRACT_START_DATE")); objdto.setContractEndDate(rs.getString("CONTRACT_END_DATE")); // objdto.setStartDateLogic(rs.getString("STARTDATELOGIC")); // objdto.setDnd_Dispatch_And_Delivered(rs.getString("DND_DISPATCH_AND_DELIVERED")); // objdto.setDnd_Dispatch_But_Not_Delivered(rs.getString("DND_DISPATCH_BUT_NOT_DELIVERED")); objdto.setBilling_address(VAR_BILLING_ADDRESS); objdto.setServiceTypeDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); objdto.setCancelBy(rs.getString("CANCEL_BY")); objdto.setCanceldate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objdto.setCanceldate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } objdto.setCancelReason(rs.getString("CANCEL_REASON")); objdto.setDemo(rs.getString("DEMO_TYPE")); /* * end of newly ended code */ // start_date = ? objdto.setAccountID(rs.getInt("ACCOUNT_NUMBER")); objdto.setCreditPeriodName(rs.getString("CREDIT_PERIOD")); //objdto.setCurrencyName(rs.getString("CURNAME")); // is it currency objdto.setCurrencyName(rs.getString("CURRENCY")); //objdto.setEntity(rs.getString("ENTITYNAME")); // is it legal entity objdto.setEntity(rs.getString("LEGAL_ENTITY")); objdto.setBillingMode(rs.getString("BILLING_MODE_NAME")); // text of BILLINGMODE objdto.setBillingTypeName(rs.getString("BILL_TYPE")); objdto.setBillingformat(rs.getString("BILL_FORMAT")); objdto.setLicCompanyName(rs.getString("LICENSE_COMP")); objdto.setTaxation(rs.getString("TAXATION_NAME")); // is it TAXATION // objdto.setTaxation(rs.getString("TAXATION")); objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); // is it BILLINGLEVEL // objdto.setBillingLevelName(rs.getString("BILLINGLEVEL")); //objdto.setStore(rs.getString("STORENAME")); // is it STORE // objdto.setStore(rs.getString("STORE")); // objdto.setHardwaretypeName(rs.getString("HARDWARETYPE")); objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); // objdto.setSaleNature(rs.getString("NATURE_OF_SALE")); // objdto.setSaleTypeName(rs.getString("TYPE_OF_SALE")); objdto.setPrimaryLocation(VAR_PRIMARYLOCATION); objdto.setSeclocation(VAR_SECONDARYLOCATION); objdto.setPoNumber(rs.getInt("PODETAILNUMBER")); objdto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = rs.getDate("PODATE"); objdto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objdto.setParty_num(rs.getString("PARTY_NO")); // objdto.setChargeAnnotation(rs.getString("ANNOTATION")); // objdto.setFx_sd_charge_status(rs.getString("FX_SD_CHG_STATUS")); // objdto.setFx_charge_status(rs.getString("FX_STATUS")); // objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHG_STATUS")); //objdto.setTokenID(rs.getInt("TOKEN_ID")); // is it TOKEN_NO // objdto.setTokenno(rs.getString("TOKEN_NO")); objdto.setModifiedDate(rs.getString("LAST_UPDATE_DATE")); if (rs.getString("LAST_UPDATE_DATE") != null && !"".equals(rs.getString("LAST_UPDATE_DATE"))) { objdto.setModifiedDate( (utility.showDate_Report(new Date(rs.getTimestamp("LAST_UPDATE_DATE").getTime()))) .toUpperCase()); } objdto.setBillingTriggerFlag(rs.getString("BILLING_TRIG_FLAGNAME")); // text of BILLING_TRIG_FLAG if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) { objdto.setPm_pro_date(rs.getString("PM_PROV_DATE")); /* String s1=rs.getString("PM_PROVISIONING_DATE"); String s3=s1.substring(0,7).toUpperCase(); String s4=s1.substring(9,11); String s5=s3.concat(s4); objdto.setPm_pro_date(s5);*/ } if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) { objdto.setLocDate(rs.getString("LOC_DATE")); Date date = df.parse(objdto.getLocDate()); objdto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } if (rs.getString("BILLING_TRIG_DATE") != null && !"".equals(rs.getString("BILLING_TRIG_DATE"))) { objdto.setBilling_trigger_date(rs.getString("BILLING_TRIG_DATE")); Date date = df.parse(objdto.getBilling_trigger_date()); objdto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } // objdto.setChallenno(rs.getString("CHALLEN_NO")); // objdto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objdto.setChallendate(s5); } //FX_ACCOUNT_EXTERNAL_ID = ? //objdto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID")); //objdto.setChild_account_creation_status(rs.getString("CHILD_ACCOUNT_FX_STATUS")); objdto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER")); objdto.setChild_ac_fxstatus(rs.getString("CHILD_ACCOUNT_STATUS_NAME")); // text of CHILD_ACCOUNT_FX_STATUS if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objdto.setOrderDate(rs.getString("ORDERDATE")); //Date date=df.parse(objdto.getOrderDate()); objdto.setOrderDate((utility.showDate_Report(objdto.getOrderDate())).toUpperCase()); } if (rs.getString("APPROVED_DATE") != null && !"".equals(rs.getString("APPROVED_DATE"))) { objdto.setCopcapprovaldate(rs.getString("APPROVED_DATE")); objdto.setCopcapprovaldate( (utility.showDate_Report(objdto.getCopcapprovaldate())).toUpperCase()); } objdto.setOrderType(rs.getString("ORDERTYPE")); if (rs.getString("BILL_TRG_CREATE_DATE") != null && !"".equals(rs.getString("BILL_TRG_CREATE_DATE"))) { objdto.setBillingtrigger_createdate(rs.getString("BILL_TRG_CREATE_DATE")); objdto.setBillingtrigger_createdate( (utility.showDate_Report(new Date(rs.getTimestamp("BILL_TRG_CREATE_DATE").getTime()))) .toUpperCase()); } objdto.setRatio(rs.getString("RATIO")); objdto.setProductName(rs.getString("PRODUCT")); objdto.setSubProductName(rs.getString("SUBPRODUCT")); objdto.setHardwareType(rs.getString("HARDWARE_FLAG")); objdto.setServiceStage(rs.getString("SERVICE_STAGE")); objdto.setOrderStage(rs.getString("ORDER_STAGE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objdto.setRfsDate(rs.getString("RFS_DATE")); objdto.setRfsDate((utility.showDate_Report(objdto.getRfsDate())).toUpperCase()); } // PORECEIVEDATE = ? /*objdto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date=df.parse(objdto.getPoReceiveDate()); objdto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); }*/ objdto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objdto.setCustPoDate(rs.getString("CUSTPODATE")); //Date date=df.parse(objdto.getCustPoDate()); objdto.setCustPoDate((utility.showDate_Report(objdto.getCustPoDate())).toUpperCase()); } // objdto.setCharge_status(rs.getString("CHARGES_STATUS")); objdto.setLOC_No(rs.getString("LOC_NUMBER")); //objdto.setAddress1(rs.getString("ADDRESS")); // is it BILLING_ADDRESS objdto.setAddress1(VAR_BILLING_ADDRESS); objdto.setM6cktid(rs.getString("CIRCUIT_ID")); objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objdto.setRegion(rs.getString("REGION")); objdto.setBandwaidth(rs.getString("BILLING_BANDWIDTH")); objdto.setVertical(rs.getString("VERTICAL")); objdto.setAccountManager(rs.getString("ACCOUNT_MGR")); objdto.setProjectManager(rs.getString("PROJECT_MGR")); objdto.setDistance(rs.getString("CHARGEABLE_DISTANCE")); // objdto.setDispatchAddress1(rs.getString("DISPATCH_ADDRESS")); objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); // productname objdto.setPartyName(rs.getString("PARTY_NAME")); //objdto.setBilling_location_from(rs.getString("BILLING_ADDRESS")); // DEMO_ORDER = ? //objdto.setDemo(rs.getString("DEMO_ORDER")); // CRM_PRODUCTNAME = ? //objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME")); objdto.setToLocation(rs.getString("TO_ADDRESS")); objdto.setFromLocation(rs.getString("FROM_ADDRESS")); objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); //BILLING_BANDWIDTH_UOM = ? //remove this column //objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); //BL_SOURCE = ? //objdto.setBlSource(rs.getString("BL_SOURCE")); objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID")); objdto.setOrderNumber(rs.getInt("ORDERID")); // objdto.setChargeAmount(rs.getDouble("INV_AMT")); // LINEITEMAMOUNT = ? //objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT")); //TOTAL_CHARGE_AMT = ? //objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT")); // objdto.setContractPeriod(rs.getInt("CONTRACT_PERIOD_MNTHS")); //objdto.setTotalPoAmt(""+rs.getDouble("TOTAL_POAMOUNT")); // is it POAMOUNT objdto.setTotalPoAmt("" + rs.getDouble("POAMOUNT")); //PARTY_ID = ? //objdto.setParty_id(rs.getInt("PARTY_ID")); // is it party no // CRMACCOUNTNO = ? //objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); //m6 productid objdto.setM6OrderNo(rs.getInt("M6_ORDER_ID")); // CUST_TOT_PO_AMT = ? objdto.setCust_total_poamt(rs.getDouble("tot_amount")); // old value from CUST_TOT_PO_AMT //m6 order,business // PK_CHARGE_ID = ? //objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12 // objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); // CONTRACT_PERIOD_MNTHS is using above // objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objdto.setServiceId(rs.getInt("SERVICE_NO")); if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) { objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE")); objdto.setPoExpiryDate((utility.showDate_Report(objdto.getPoExpiryDate())).toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objdto.setFxInternalId(rs.getInt("INTERNAL_ID")); // objdto.setMinimum_bandwidth(rs.getString("MINIMUM_BANDWIDTH"));//Need to add in View : AKS(Added) // objdto.setMinimum_bandwidth_UOM(rs.getString("MINIMUM_BANDWIDTH_UOM"));//Need to add in View : AKS(Added) objdto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objdto.setComponentID(rs.getInt("COMPONENT_ID")); objdto.setComponentName(rs.getString("COMPONENT_NAME")); objdto.setPackageID(rs.getInt("PACKAGE_ID")); objdto.setPackageName(rs.getString("PACKAGE_NAME")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));//RC_NRC_COMP_AMOUNT : AKS dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("SYSTEM_START_DATE")); // COMPONENT_START_DATE if (rs.getString("SYSTEM_START_DATE") != null && !"".equals(rs.getString("SYSTEM_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("SYSTEM_END_DATE")); // COMPONENT_END_DATE if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setEndTokenNo(rs.getString("END_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); //dto.setChargesSumOfLineitem(rs.getString("FX_START_COMPONENT_STATUS")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));//COMP_FX_INSTANCE_ID : AKS dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS"));//FX_END_COMPONENT_STATUS :AKS //dto.setStartStatus(rs.getString("FX_ST_COMPONENT_STATUS")); objdto.setComponentDto(dto); objUserList.add(objdto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<DisconnectLineReportDTO> viewDisconnectionLineReport(DisconnectLineReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null;/* w w w . j av a 2 s .co m*/ int recordCount = 0; ArrayList<DisconnectLineReportDTO> listSearchDetails = new ArrayList<DisconnectLineReportDTO>(); DisconnectLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionLineReportForUsage); if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(1, objDto.getOrdermonth().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(4, objDto.getServiceName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) { proc.setString(5, objDto.getOrdersubtype().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(6, objDto.getVerticalDetails().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(12, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new DisconnectLineReportDTO(); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setCust_name(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS")); objReportsDto.setLocation_to(rs.getString("TO_ADDRESS")); //objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objReportsDto.setSeclocation(VAR_SECONDARYLOCATION); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); if ((rs.getString("CONFIG_ID")) != null || !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null || !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBill_period(tBillPeriod); } objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { //Date date=df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(objReportsDto.getPoDate())).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); tempDate = rs.getDate("PORECEIVEDATE"); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setPoReceiveDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); tempDate = rs.getDate("CUSTPODATE"); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBilling_trigger_date()); objReportsDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_PROV_DATE")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = (s1.substring(0, 7)).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_FLAG")); objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo")); objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS")); objReportsDto.setStageName(rs.getString("STAGE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setNeworder_remarks(rs.getString("NEWORDER_REMARKS")); objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE")); objReportsDto.setStandard_reason(rs.getString("STANDARDREASON")); tempDate = rs.getDate("ORDERDATE"); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase()); } if (objDto.getIsUsage() == 0) { objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE")); if (rs.getString("CSTATE_CHARGE_CURRENT_START_DATE") != null && !"".equals(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objReportsDto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setFx_status(rs.getString("CSTATE_START_DETAILS_FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("CSTATE_FX_CHARGE_START_STATUS")); objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id")); } if (objDto.getIsUsage() == 1) { objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartDate(rs.getString("SYSTEM_START_DATE")); if (rs.getString("SYSTEM_START_DATE") != null && !"".equals(rs.getString("SYSTEM_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setEnd_date(rs.getString("SYSTEM_END_DATE")); if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((utility.showDate_Report(date)).toUpperCase()); } dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS")); objReportsDto.setComponentDto(dto); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ReportsDto> viewMasterHistory(ReportsDto objDto) throws Exception { // Nagarjuna String methodName = "viewMasterHistory", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end// w w w. j av a 2 s. co m Connection connection = null; CallableStatement callstmt = null; ResultSet rs = null; ReportsDto objConDto = new ReportsDto(); ArrayList<ReportsDto> historyList = new ArrayList<ReportsDto>(); Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); callstmt = connection.prepareCall(sqlGetMasterReportStatus); callstmt.setString(1, objDto.getMasterValue()); PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index callstmt.setString(2, pagingSorting.getSortByColumn());// columnName callstmt.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order callstmt.setInt(4, pagingSorting.getStartRecordId());// start index callstmt.setInt(5, pagingSorting.getEndRecordId());// end index callstmt.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = callstmt.executeQuery(); int recordCount = 0; while (rs.next()) { objConDto = new ReportsDto(); objConDto.setMasterName(rs.getString("MASTERNAME")); objConDto.setColumnName(rs.getString("COLUMNS")); objConDto.setOldValues(rs.getString("OLD_VALUES")); objConDto.setNewValues(rs.getString("NEW_VALUES")); objConDto.setOperationName(rs.getString("OPERATION_NAME")); objConDto.setModifiedDate(rs.getString("MODIFIED_DATE")); if (rs.getString("MODIFIED_DATE") != null && !"".equals(rs.getString("MODIFIED_DATE"))) { objConDto.setModifiedDate( (utility.showDate_Report(new Date(rs.getTimestamp("MODIFIED_DATE").getTime()))) .toUpperCase()); } objConDto.setModifiedBy(rs.getString("MODIFIED_BY")); objConDto.setAttribiuteId(rs.getString("ATTRIBUTE_ID")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } historyList.add(objConDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(callstmt); DbConnection.freeConnection(connection); } catch (SQLException e) { //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return historyList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<LineItemDTO> getLSIMappingDetails(LineItemDTO objDto) { // Nagarjuna String methodName = "getLSIMappingDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*from w ww. j ava 2 s .co m*/ Connection connection = null; CallableStatement getLSIMappingDetails = null; ResultSet rsLSIMappingDetails = null; ArrayList<LineItemDTO> LSIMappingList = new ArrayList<LineItemDTO>(); LineItemDTO objLineItemDTO = null; int recordCount = 0; try { connection = DbConnection.getReportsConnectionObject(); getLSIMappingDetails = connection.prepareCall(sqlGetLSIMappingDetails); //PagingSorting pagingSorting = objDto.getPagingSorting(); getLSIMappingDetails.setString(1, objDto.getLsiNO()); getLSIMappingDetails.setString(2, objDto.getRedirectedLSINo()); getLSIMappingDetails.setString(3, objDto.getMappedLSINo()); if (objDto.getAccountNo().equals("")) { getLSIMappingDetails.setString(4, null); } else { getLSIMappingDetails.setString(4, objDto.getAccountNo()); } //getLSIMappingDetails.setString(4,objDto.getAccountNo()); getLSIMappingDetails.setString(5, objDto.getAccountName()); getLSIMappingDetails.setString(6, objDto.getPagingDto().getSortBycolumn()); getLSIMappingDetails.setString(7, objDto.getPagingDto().getSortByOrder()); getLSIMappingDetails.setInt(8, objDto.getPagingDto().getStartIndex()); getLSIMappingDetails.setInt(9, objDto.getPagingDto().getEndIndex()); getLSIMappingDetails.setInt(10, objDto.getPagingRequired());// end rsLSIMappingDetails = getLSIMappingDetails.executeQuery(); while (rsLSIMappingDetails.next()) { objLineItemDTO = new LineItemDTO(); objLineItemDTO.setAccountNo(rsLSIMappingDetails.getString("CRMACCOUNTNO")); objLineItemDTO.setAccountName(rsLSIMappingDetails.getString("ACCOUNTNAME")); objLineItemDTO.setLsiNO(rsLSIMappingDetails.getString("LSINO")); objLineItemDTO.setProductName(rsLSIMappingDetails.getString("PRODUCTNAME")); objLineItemDTO.setLineNo(rsLSIMappingDetails.getString("LINEITEMNO")); objLineItemDTO.setLineName(rsLSIMappingDetails.getString("LINEITEMNAME")); objLineItemDTO.setRedirectedLSINo(rsLSIMappingDetails.getString("REDLSINO")); objLineItemDTO.setRedirectedProductName(rsLSIMappingDetails.getString("REDPRODUCTNAME")); objLineItemDTO.setRedirectedLineNo(rsLSIMappingDetails.getString("REDLINENO")); objLineItemDTO.setRedirectedLineName(rsLSIMappingDetails.getString("REDLINENAME")); objLineItemDTO.setMappedLSINo(rsLSIMappingDetails.getString("MAPPEDLSINO")); objLineItemDTO.setMappedProductName(rsLSIMappingDetails.getString("MAPPEDSERVICENAME")); objLineItemDTO.setMappedLineNo(rsLSIMappingDetails.getString("MAPPEDLINENO")); objLineItemDTO.setMappedLineName(rsLSIMappingDetails.getString("MAPPEDLINEITEMNAME")); if (objDto.getPagingRequired() == 1) { recordCount = rsLSIMappingDetails.getInt("FULL_REC_COUNT"); objLineItemDTO.getPagingDto().setRecordCount(recordCount); } objLineItemDTO.setMaxPageNo(objLineItemDTO.getPagingDto().getMaxPageNumber()); LSIMappingList.add(objLineItemDTO); } } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rsLSIMappingDetails); DbConnection.closeCallableStatement(getLSIMappingDetails); 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 LSIMappingList; }