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<CancelledFailedLineReportDTO> viewCancelledFailedLineReport( CancelledFailedLineReportDTO objDto) { // Nagarjuna String methodName = "viewCancelledFailedLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w .j a va 2 s .c o m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<CancelledFailedLineReportDTO> listSearchDetails = new ArrayList<CancelledFailedLineReportDTO>(); CancelledFailedLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter2 = new SimpleDateFormat("MM-dd-yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetCancelledFailedLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { Date dateStr = formatter.parse(objDto.getFromDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { Date dateStr = formatter.parse(objDto.getToDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim().toUpperCase()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(4, pagingSorting.getSortByColumn());// columnName proc.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(6, pagingSorting.getStartRecordId());// start index proc.setInt(7, pagingSorting.getEndRecordId());// end index proc.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new CancelledFailedLineReportDTO(); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS")); objReportsDto.setLocation_to(rs.getString("TO_ADDRESS")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("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.setCreditPeriodName(rs.getString("CREDITPERIOD")); 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.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(BigDecimal.valueOf((rs.getDouble("TOTALPOAMOUNT"))).toPlainString()); if (objReportsDto.getTotalPoAmt() == null) { objReportsDto.setTotalPoAmt(" "); } objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } //objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE")); //objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto .setFrequencyAmt(BigDecimal.valueOf((rs.getDouble("CHARGEFREQUENCYAMT"))).toPlainString()); if (objReportsDto.getFrequencyAmt() == null) { objReportsDto.setFrequencyAmt(" "); } //objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); //objReportsDto.setAmt(rs.getLong("CHARGEAMOUNT"));//particular charge amount objReportsDto.setLineItemAmount(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objReportsDto.getLineItemAmount() == null) { objReportsDto.setLineItemAmount(" "); } objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setFx_status(rs.getString("CSTATE_START_DETAILS_FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("CSTATE_FX_CHARGE_START_STATUS")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (!(rs.getString("ORDERDATE") == null || rs.getString("ORDERDATE") == "")) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } //objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); //objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id")); objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID")); 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
/** * Create a Report to generate LEPM Order Detail Report //from w ww . ja v a2 s .co m * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<LempOrderDetailsReportDTO> viewLEPMOrderDetailReport(LempOrderDetailsReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewLEPMOrderDetailReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<LempOrderDetailsReportDTO> objUserList = new ArrayList<LempOrderDetailsReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; int countFlag = 0; LempOrderDetailsReportDTO objReportsDto = null; ArrayList<LempOrderDetailsReportDTO> listSearchDetails = new ArrayList<LempOrderDetailsReportDTO>(); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlLEPMOrderDetailReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } //[202020]START if (objDto.getCopcApproveFromDate() != null && !"".equals(objDto.getCopcApproveFromDate()) && objDto.getCopcApproveToDate() != null && !"".equals(objDto.getCopcApproveToDate())) { proc.setString(2, objDto.getCopcApproveFromDate().trim()); proc.setString(3, objDto.getCopcApproveToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); proc.setNull(3, java.sql.Types.VARCHAR); } //[202020]END if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(5, objDto.getFromOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(6, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); while (rs.next() != false) { objDto = new LempOrderDetailsReportDTO(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setPrimarylocation(rs.getString("FROM_SITE")); objDto.setSeclocation(rs.getString("TO_SITE")); objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setChargeAmount_String(rs.getString("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setPrjmgremail(rs.getString("PMEMAIL")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase()); } objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE")); if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) { objDto.setNio_approve_date( (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((Utility.showDate_Report((rs.getTimestamp("RFS_DATE")))).toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); if ("NULL".equals(rs.getString("SERVICETYPE"))) { objDto.setServiceType(""); } else { objDto.setServiceType(rs.getString("SERVICETYPE")); } objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); //Vijay [RPT7052013010] -start. //fetch the only city becoz of the FROM_CITY & TO_CITY has full adDress objDto.setFrom_city(rs.getString("FROM_CITY")); String frmCity = rs.getString("FROM_CITY"); try { if (frmCity != null) { String[] fcity = frmCity.split("~~"); objDto.setFrom_city(fcity[5]); } } catch (IndexOutOfBoundsException e) { //e.printStackTrace(); } objDto.setTo_city(rs.getString("TO_CITY")); String toCity = rs.getString("TO_CITY"); try { if (toCity != null) { String[] tcity = toCity.split("~~"); objDto.setTo_city(tcity[5]); } } catch (IndexOutOfBoundsException e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); } //Vijay. [RPT7052013010] - end objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setTotalPoAmt(rs.getString("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); //[404040] Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End //[505050] Start objDto.setOnnetOffnet((rs.getString("OFFNET_LABELATTVALUE"))); objDto.setMedia((rs.getString("MEDIA_LABELATTVALUE"))); objDto.setServOrderCategory((rs.getString("ORDERCATGRY_LABELATTVALUE"))); //[505050] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ZeroOrderValueReportDTO> viewZeroOrderValueReportDetails(ZeroOrderValueReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewZeroOrderValueReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end //from w ww .j av a2 s . com ArrayList<ZeroOrderValueReportDTO> objUserList = new ArrayList<ZeroOrderValueReportDTO>(); 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(sqlZeroOrdervalueReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(4, objDto.getFromAccountNo()); proc.setLong(5, objDto.getToAccountNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(6, objDto.getFromOrderNo()); proc.setLong(7, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getCustPoDetailNo() != null && !"".equals(objDto.getCustPoDetailNo())) { proc.setString(8, objDto.getCustPoDetailNo().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new ZeroOrderValueReportDTO(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setPoDetailNo(rs.getString("PODETAILNUMBER")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setEntityId(rs.getString("ENTITYCODE")); objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setPoAmounts(rs.getDouble("POAMOUNT")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { Date date = df.parse(objDto.getContractStartDate()); objDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objDto.getContractEndDate()); objDto.setContractEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objDto.getPoRecieveDate()); objDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setPoIssueBy(rs.getString("ISSUEDBY")); objDto.setPoEmailId(rs.getString("EMAILID")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setLineItemDescription(rs.getString("SERVICEPRODUCTID")); objDto.setRegionName(rs.getString("REGION")); objDto.setChargeAnnotation(rs.getString("ANNOTATION")); objDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objDto.setFromLocation(rs.getString("FROM_LOCATION")); objDto.setToLocation(rs.getString("TO_LOCATION")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setPoDemoContractPeriod(rs.getString("DEMOCONTRACTPERIOD")); objDto.setIsDefaultPO(rs.getInt("ISDEFAULTPO")); objDto.setCreatedBy(rs.getString("CREATEDBY")); objDto.setCreatedDate(rs.getString("CREATEDDATE")); objDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objDto.setMocn_no(rs.getString("MOCN_NUMBER")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { objDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objDto.setUom(rs.getString("UOM")); objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH")); objDto.setOrder_type(rs.getString("ORDER_TYPE")); objDto.setFxSiId(rs.getString("FX_SI_ID")); objDto.setSourceName("UNMIGRATED"); objDto.setTokenNO(rs.getString("TOKEN_NO")); objDto.setPoEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objDto.getPoEndDate()); objDto.setPoEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setLastUpdatedDate(""); objDto.setLastUpdatedBy(""); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ActiveLineDemoReportDTO> viewActiveLineDemoReportDetails(ActiveLineDemoReportDTO reportsDto) { AppConstants.IOES_LOGGER.info("ReportsDao: viewActiveLineDemoReportDetails is executing..."); Connection connection = null; ResultSet resultSet = null;/*from w w w . java 2 s.com*/ CallableStatement callableStatement = null; ArrayList<ActiveLineDemoReportDTO> activeLineDemoReportDTOsList = null; try { connection = DbConnection.getReportsConnectionObject(); callableStatement = connection.prepareCall(spGetActiveLineDemoReport); activeLineDemoReportDTOsList = new ArrayList<ActiveLineDemoReportDTO>(); String orderFromDate = reportsDto.getOrderDate(); String orderToDate = reportsDto.getOrderDate(); long fromOrderNo = reportsDto.getFromOrderNo(); long toOrderNo = reportsDto.getToOrderNo(); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM-dd-yyyy"); if (orderFromDate == null || orderFromDate.trim().equals("")) { callableStatement.setNull(1, java.sql.Types.VARCHAR); } else { orderFromDate = simpleDateFormat.format(orderFromDate); callableStatement.setString(1, Utility.getReportOrderDate(orderFromDate)); AppConstants.IOES_LOGGER .info("Date format is recomemded: " + simpleDateFormat.format(orderFromDate)); } if (orderToDate == null || orderToDate.trim().equals("")) { callableStatement.setNull(2, java.sql.Types.VARCHAR); } else { callableStatement.setString(2, Utility.getReportOrderDate(orderToDate)); } if (fromOrderNo != 0 && toOrderNo != 0) { callableStatement.setLong(3, fromOrderNo); callableStatement.setLong(4, toOrderNo); } else { callableStatement.setInt(3, java.sql.Types.BIGINT); callableStatement.setInt(4, java.sql.Types.BIGINT); } //AppConstants.IOES_LOGGER.info(Utility.getReportOrderDate(orderFromDate)+"Order numL "); Utility utility = new Utility(); PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index callableStatement.setString(5, pagingSorting.getSortByColumn());// columnName callableStatement.setString(6, PagingSorting.DB_Asc_Desc1(pagingSorting.getSortByOrder()));// sort order callableStatement.setInt(7, pagingSorting.getStartRecordId());// start index callableStatement.setInt(8, pagingSorting.getEndRecordId());// end index callableStatement.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end resultSet = callableStatement.executeQuery(); int countFlag = 0; int recordCount = 0; ActiveLineDemoReportDTO demoReportDTO = new ActiveLineDemoReportDTO(); while (resultSet.next() != false) { countFlag++; demoReportDTO.setPartyName(resultSet.getString("PARTYNAME")); demoReportDTO.setPartyNo(resultSet.getString("PARTY_NO")); demoReportDTO.setCrmAccNo(resultSet.getLong("CRMACCOUNTNO")); demoReportDTO.setCustomerSegment(resultSet.getString("CUSTOMER_SEGMENT")); demoReportDTO.setIndustrySegment(resultSet.getString("INDUSTRY_SEGMENT")); demoReportDTO.setRegionName(resultSet.getString("REGIONNAME")); demoReportDTO.setZoneName(resultSet.getString("ZONENNAME")); demoReportDTO.setAcctMgrName(resultSet.getString("ACCT_MGR_NAME")); demoReportDTO.setPrjMGRName(resultSet.getString("PRJ_MGR_NAME")); demoReportDTO.setDemoOrder(resultSet.getString("DEMO_ORDER")); demoReportDTO.setNoOfDays(resultSet.getLong("NO_OF_DAYS")); demoReportDTO.setOrderType(resultSet.getString("ORDERTYPE")); demoReportDTO.setChangeTypeName(resultSet.getString("CHANGETYPENAME")); demoReportDTO.setSubChangeType(resultSet.getString("ORDER_SUBCHANGETYPE")); if (!(resultSet.getString("ORDERDATE") == null || resultSet.getString("ORDERDATE") == "")) { demoReportDTO.setOrderDate((utility.showDate_Report(resultSet.getString("ORDERDATE")))); } demoReportDTO.setServiceId(resultSet.getLong("SERVICEID")); demoReportDTO.setOrderNo(resultSet.getLong("ORDERNO")); demoReportDTO.setLogicalSINo(resultSet.getString("LOGICAL_SI_NO")); demoReportDTO.setCustLogicalSINo(resultSet.getString("CUSTOMER_LOGICAL_SI_NO")); demoReportDTO.setCktId(resultSet.getString("CKTID")); demoReportDTO.setAnnotation(resultSet.getString("ANNOTATION")); demoReportDTO.setM6OrderNo(resultSet.getLong("M6ORDERNO")); demoReportDTO.setLocNo(resultSet.getString("LOCNO")); demoReportDTO.setLocDate(resultSet.getString("LOC_DATE")); demoReportDTO.setFromAddress(resultSet.getString("FROM_ADDRESS")); demoReportDTO.setToAddress(resultSet.getString("TO_ADDRESS")); demoReportDTO.setBillingBandWidth(resultSet.getString("BILLING_BANDWIDTH")); demoReportDTO.setBillingBandwidthUOM(resultSet.getString("BILLING_BANDWIDTH_UOM")); demoReportDTO.setCreateDate( utility.showDate_Report5((new Date(resultSet.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); demoReportDTO.setOrderApprovalDate(utility .showDate_Report5((new Date(resultSet.getTimestamp("ORDER_APPROVAL_DATE").getTime()))) .toUpperCase()); if (!(resultSet.getString("PUBLISHED_DATE") == null || resultSet.getString("PUBLISHED_DATE") == "")) { demoReportDTO .setPublishedDate((utility.showDate_Report(resultSet.getString("PUBLISHED_DATE")))); } if (!(resultSet.getString("SERVICE_CLOSURE_DATE") == null || resultSet.getString("SERVICE_CLOSURE_DATE") == "")) { demoReportDTO.setServiceClosureDate( (utility.showDate_Report(resultSet.getString("SERVICE_CLOSURE_DATE")))); } if (!(resultSet.getString("BILLING_TRIGGER_CREATEDATE") == null || resultSet.getString("BILLING_TRIGGER_CREATEDATE") == "")) { demoReportDTO.setBillingTriggerCreateDate( (utility.showDate_Report(resultSet.getString("BILLING_TRIGGER_CREATEDATE")))); } if (!(resultSet.getString("BILLINGTRIGGERDATE") == null || resultSet.getString("BILLINGTRIGGERDATE") == "")) { demoReportDTO.setBillingTriggerDate( (utility.showDate_Report(resultSet.getString("BILLINGTRIGGERDATE")))); } if (!(resultSet.getString("CHARGE_CURRENT_START_DATE") == null || resultSet.getString("CHARGE_CURRENT_START_DATE") == "")) { demoReportDTO.setChargeCurrentStartDate( (utility.showDate_Report(resultSet.getString("CHARGE_CURRENT_START_DATE")))); } if (!(resultSet.getString("CURRENT_END_DATE") == null || resultSet.getString("CURRENT_END_DATE") == "")) { demoReportDTO.setChargeCurrentEndDate( (utility.showDate_Report(resultSet.getString("CURRENT_END_DATE")))); } demoReportDTO.setMstChargeName(resultSet.getString("MST_CHARGENAME")); demoReportDTO.setProductName(resultSet.getString("PRODUCTNAME")); demoReportDTO.setSubTypeName(resultSet.getString("SERVICESUBTYPENAME")); demoReportDTO.setStage(resultSet.getString("STAGE")); demoReportDTO.setServiceTypeName(resultSet.getString("SERVICETYPENAME")); demoReportDTO.setCopcApprovalRemark(resultSet.getString("Copc_Approval_Remark")); demoReportDTO.setOrderEntryRemark(resultSet.getString("Order_Entry_Task_Remark")); demoReportDTO.setpMRemark(resultSet.getString("Pm_Approval_Task_Remark")); demoReportDTO.setTotalAmount(resultSet.getLong("TOT_AMOUNT")); demoReportDTO.setCurName(resultSet.getString("CURNAME")); demoReportDTO.setAnnualRate(resultSet.getLong("ANNUAL_RATE")); demoReportDTO.setPublished(resultSet.getString("PUBLISHED")); demoReportDTO.setServiceStage(resultSet.getString("SERVICE_STAGE")); demoReportDTO.setLsiDemoType(resultSet.getInt("IS_DEMO")); if (pagingSorting.isPagingToBeDone()) { recordCount = resultSet.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } activeLineDemoReportDTOsList.add(demoReportDTO); } pagingSorting.setRecordCount(recordCount); } catch (Exception exception) { exception.printStackTrace(); AppConstants.IOES_LOGGER .info("ReportsDao: viewActiveLineDemoReportDetails has problem in execution " + exception); } finally { try { DbConnection.closeResultset(resultSet); DbConnection.closeCallableStatement(callableStatement); DbConnection.freeConnection(connection); } catch (Exception exception2) { AppConstants.IOES_LOGGER.info("ReportsDao: Problem in closing connetion " + exception2); } } return activeLineDemoReportDTOsList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<LogicalSIDataReportDTO> viewLogicalSIDataReport(LogicalSIDataReportDTO objDto) { // Nagarjuna String methodName = "viewLogicalSIDataReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w. j av a 2 s . c om Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<LogicalSIDataReportDTO> listSearchDetails = new ArrayList<LogicalSIDataReportDTO>(); LogicalSIDataReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetLogicalSIDataReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getLogicalSINumber() != 0 && !"".equals(objDto.getLogicalSINumber())) { proc.setInt(6, objDto.getLogicalSINumber()); } else { proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new LogicalSIDataReportDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setRecordStatus(rs.getString("recordStatus")); objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setParent_name(rs.getString("PARENT_NAME"));//PARENT LINE NAME objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//Line Name objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setFrequencyAmt(rs.getString("FREQUENCY_AMT")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setChargeEndDate(rs.getString("END_DATE"));//Charge End Date if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount //Advance //Installment Rate objReportsDto.setDnd_Dispatch_But_Not_Delivered(rs.getString("Dnd_Dispatch_But_Not_Delivered")); objReportsDto.setDnd_Dispatch_And_Delivered(rs.getString("Dnd_Dispatch_And_Delivered")); objReportsDto.setDnd_Disp_Del_Not_Installed(rs.getString("Ddni_Disp_Del_Not_Installed")); objReportsDto.setDnd_Disp_Delivered_Installed(rs.getString("Ddni_Disp_Delivered_Installed")); objReportsDto.setPoExclude(rs.getString("PO_EXCLUDE"));//Po Valid Exclude objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//PO Contract Period objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { Date date = df.parse(objReportsDto.getContractStartDate()); objReportsDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objReportsDto.getContractEndDate()); objReportsDto.setContractEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeinfoID(rs.getString("CHARGEINFOID"));//need to add in view objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); //Pk Charges Id //M6 Product Id //Parent Product Id objReportsDto.setBillingInfoID(rs.getInt("CHARGE_HDR_ID"));//Charge Hdr Id //Ib Pk Charges Id //Ib Order Line Id //M6 Order Id //Order Line Si No objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setRemarks(rs.getString("REMARKS")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); String billAddress = rs.getString("BILLING_ADDRESS"); String[] billAddressArray = billAddress.split("~~ ", 14); objReportsDto.setCountyName(billAddressArray[8]);// rs.getString("COUNTRY_NAME") objReportsDto.setAddress1(billAddressArray[2]);//billing Address1 objReportsDto.setAddress2(billAddressArray[3]);//billing Address2 objReportsDto.setAddress3(billAddressArray[4]);//billing Address3 objReportsDto.setAddress4(billAddressArray[5]);//billing Address4 objReportsDto.setCityName(billAddressArray[6]);//need to add in view - rs.getString("CITY_NAME") objReportsDto.setPostalCode(billAddressArray[9]);//need to add in view -rs.getString("POSTAL_CODE") objReportsDto.setStateName(billAddressArray[7]);//need to add in view - rs.getString("STATE_NAME") //Active End Date objReportsDto.setContactName(billAddressArray[0] + " " + billAddressArray[1]);//Contact Person Name - rs.getString("BILL_CON_PER_NAME") objReportsDto.setDesignation(billAddressArray[13]);//Person Designation - rs.getString("DESIGNATION") objReportsDto.setTelePhoneNo(billAddressArray[10]);//Person Mobile - rs.getString("TELEPHONENO") objReportsDto.setEmailId(billAddressArray[12]);//Person Email - rs.getString("EMAIL_ID") objReportsDto.setFax(billAddressArray[11]);//Person Fax - rs.getString("FAX") objReportsDto.setLst_No(rs.getString("LST_NO"));//Lst No objReportsDto.setLstDate(rs.getString("LST_DATE"));//Lst Date if (rs.getString("LST_DATE") != null && !"".equals(rs.getString("LST_DATE"))) { objReportsDto .setLstDate((utility.showDate_Report(new Date(rs.getTimestamp("LST_DATE").getTime()))) .toUpperCase()); } //Billing Address Type //objReportsDto.setAttributeLabel(rs.getString("Attribute_Name")); //objReportsDto.setAttributeValue(rs.getString("Attribute_Value")); objReportsDto.setStoreName(rs.getString("STORENAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); //Principle Amt //Interest Rate objReportsDto.setWarrantyStartDateLogic(rs.getString("WARRENTY_START_DATE_LOGIC")); objReportsDto.setWarrantyPeriodMonths(rs.getString("WARRENTY_PERIOD_MONTHS")); objReportsDto.setWarrantyPeriodDays(rs.getString("WARRENTY_PERIOD_DAYS")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyStartDate()); objReportsDto.setWarrantyStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setWarrantyEndDateLogic(rs.getString("WARRENTY_END_DATE_LOGIC")); objReportsDto.setWarrantyEndPeriodMonths(rs.getString("WARRENTY_END_PERIOD_MONTHS"));//need objReportsDto.setWarrantyEndPeriodDays(rs.getString("WARRENTY_END_PERIOD_DAYS"));//need objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE"));//need if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyEndDate()); objReportsDto.setWarrantyEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setExtndSupportPeriodMonths(rs.getString("EXT_SUPPORT_PERIOD_MONTHS")); objReportsDto.setExtndSupportPeriodDays(rs.getString("EXT_SUPPORT_PERIOD_DAYS")); objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { Date date = df.parse(objReportsDto.getExtSuportEndDate()); objReportsDto.setExtSuportEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1"));//Dispatch Address1 objReportsDto.setDispatchAddress2(rs.getString("DISP_ADDRESS2"));//Dispatch Address2 objReportsDto.setDispatchAddress3(rs.getString("DISP_ADDRESS3"));//Dispatch Address3 objReportsDto.setDispatchCityName(rs.getString("DISP_CITY_NAME"));//Dispatch City objReportsDto.setDispatchPostalCode(rs.getString("DISP_POSTAL_CODE"));//Dispatch Postal Code objReportsDto.setDispatchStateName(rs.getString("DISP_STATE_NAME"));//Dispatch State objReportsDto.setDispatchPersonName(rs.getString("DISP_Con_Person_Name"));//Dispatch Conact Person Name objReportsDto.setDispatchPhoneNo(rs.getString("DISP_TELEPHONENO"));//Dispatch Contact Person Mobile objReportsDto.setDispatchLstNumber(rs.getString("DISP_LST_NO"));//Dispatch Lst Number objReportsDto.setDispatchLstDate(rs.getString("DISP_LST_DATE"));//Dispatch Lst Date if (rs.getString("DISP_LST_DATE") != null && !"".equals(rs.getString("DISP_LST_DATE"))) { objReportsDto.setDispatchLstDate( (utility.showDate_Report(new Date(rs.getTimestamp("DISP_LST_DATE").getTime()))) .toUpperCase()); } //Dispatch Address Type //New Service List Id //New Crm Order Id //Remrks objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<M6OrderCancelReportDTO> viewM6OrderCancelReport(M6OrderCancelReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewM6OrderCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from ww w . java 2 s .c o m*/ ArrayList<M6OrderCancelReportDTO> objUserList = new ArrayList<M6OrderCancelReportDTO>(); 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(sqlM6OrderCancelReport); /*if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(2, objDto.getToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ if (objDto.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(objDto.getFromDate()); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getToDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getToDate()); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new M6OrderCancelReportDTO(); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setServiceType(rs.getString("SERVICETYPE")); objDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { objDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objDto.setEffDate(rs.getString("EFFSTARTDATE")); if (rs.getString("EFFSTARTDATE") != null && !"".equals(rs.getString("EFFSTARTDATE"))) { Date date = df.parse(objDto.getEffDate()); objDto.setEffDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { Date date = df.parse(objDto.getRfs_date()); objDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setServiceStage(rs.getString("SERVICESTAGE")); objDto.setCrmAccountId(rs.getInt("CRMACCOUNTNO")); objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objDto.setCancelServiceReason(rs.getString("CANCEL_SERVICE_REASON")); objDto.setOrdertype_demo(rs.getString("order_type_DEMO")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setCanceldate(rs.getString("CANCEL_DATE")); objDto.setOldLsi(rs.getString("OLD_LSI_CRM")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCanceldate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } 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<AttributeDetailsReportDTO> viewAttributeDetailsReport(AttributeDetailsReportDTO objDto) { // Nagarjuna String methodName = "viewAttributeDetailsReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*ww w . j a va 2 s . c o m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<AttributeDetailsReportDTO> listAttributeDetailsReport = new ArrayList<AttributeDetailsReportDTO>(); AttributeDetailsReportDTO objReportsDto = null; SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter2 = new SimpleDateFormat("MM-dd-yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetAttributeDetailsReport); if (objDto.getCustLogicalSI() != 0 && !"".equals(objDto.getCustLogicalSI())) { proc.setInt(1, objDto.getCustLogicalSI()); } else { proc.setNull(1, java.sql.Types.BIGINT); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { Date dateStr = formatter.parse(objDto.getFromDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.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())) { Date dateStr = formatter.parse(objDto.getToDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(3, formattedDate); //proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getServiceTypeId() != 0 && !"".equals(objDto.getServiceTypeId())) { proc.setInt(4, objDto.getServiceTypeId()); } else { proc.setNull(4, java.sql.Types.BIGINT); } 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)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new AttributeDetailsReportDTO(); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCrm_att(rs.getString("CRM_ATT")); objReportsDto.setM6_label_name(rs.getString("LABEL_NAME")); objReportsDto.setM6_label_value(rs.getString("LABEL_VALUE")); objReportsDto.setCustLogicalSI(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setCrm_order_id(rs.getInt("LAST_CRM_ORDER_ID")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listAttributeDetailsReport.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 listAttributeDetailsReport; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<OBValueReportDTO> viewOBValueReport(OBValueReportDTO objDto) throws Exception { String methodName = "viewOBValueReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; ArrayList<OBValueReportDTO> objUserList = new ArrayList<OBValueReportDTO>(); Connection conn = null;//w ww. ja v a 2s. com CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OBValueReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOBValueReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OBValueReportDTO(); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); objDto.setLineType(rs.getString("LINETYPE")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setPrimaryLocation(""); } if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setSeclocation(""); } objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PRJ_MGR_NAME")); objDto.setPrjmgremail(rs.getString("PROJECTMGR_MAIL")); objDto.setActmngname(rs.getString("ACCT_MGR_NAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objDto.setServiceType(rs.getString("SERVICETYPE")); objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } } else { objDto.setTo_city(""); } objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACCOUNTMGR_EMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setLoc_date(rs.getString("LOC_DATE")); objDto.setActualOB(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB"))).toPlainString()); objDto.setActualOBINR(Double.valueOf(Utility.round(rs.getDouble("ACTUAL_OB_INR"), 2)).toString()); //objDto.setActualOBINR(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB_INR"))).toPlainString()); objDto.setFinalOB(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); objDto.setFinalOBINR(Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_INR"), 2)).toString()); //objDto.setFinalOBINR(Double.valueOf((rs.getDouble("OB_VALUE_INR"))).toString()); objDto.setChargeRemarks(rs.getString("REMARKS")); objDto.setCopcApprovedBy(rs.getString("COPC_APPROVER_NAME")); objDto.setPmApprovedby(rs.getString("PM_APPROVER_NAME")); objDto.setDemoFlag(rs.getString("ISDEMO")); objDto.setOffnet(rs.getString("OFFNET_LABELATTVALUE")); objDto.setMediaType(rs.getString("MEDIA_LABELATTVALUE")); objDto.setCancellationReason(rs.getString("CANCELLATION_REASON")); objDto.setrRDate(rs.getString("RR_DATE")); if (rs.getString("RR_DATE") != null && !"".equals(rs.getString("RR_DATE"))) { objDto.setrRDate((utility.showDate_Report(new Date(rs.getTimestamp("RR_DATE").getTime()))) .toUpperCase()); } objDto.setDiffDays(rs.getString("DIFF_DAYS")); objDto.setTotalDays(rs.getString("TOTALDAYS")); objDto.setEffectiveDays(rs.getString("EFFECTIVE_DAYS")); objDto.setMn(BigDecimal.valueOf((rs.getDouble("MN"))).toPlainString()); //using this value for ob value objDto.setOldPKChargeid(rs.getLong("OBLINKCHARGE")); objDto.setOldChargeAmount(BigDecimal.valueOf((rs.getDouble("OBLINKCHARGEAMOUNT"))).toPlainString()); objDto.setOrderEnteredBy(rs.getString("ORDER_CREATED_BY_NAME")); objDto.setExchangeRate(BigDecimal.valueOf((rs.getDouble("EXCHANGE_RATE"))).toPlainString()); objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE_TRANSACTION"))).toPlainString()); objDto.setObValueINR( Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_TRANSACTION_INR"), 2)).toString()); objDto.setCustomerSegment(rs.getString("CUST_SEGMENT_CODE")); //newly added objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE")); //newly added objDto.setServiceRemarks(rs.getString("SERVICE_REMARKS")); // newly added objDto.setObMonth(rs.getString("OB_MONTH")); objDto.setObYear(rs.getString("OB_YEAR")); //[129] objDto.setEntryType(rs.getString("ENTRY_TYPE")); objDto.setIsNfa(rs.getString("IS_NFA")); objDto.setChargeperiod(rs.getString("TOTAL_DAYS")); objDto.setCopcApproveDate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } //[130] Start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<TelemediaOrderReportDTO> getTelemediaOrderList(TelemediaOrderReportDTO objDto) { // Nagarjuna String methodName = "getTelemediaOrderList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from w w w .ja v a 2s . c o m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<TelemediaOrderReportDTO> listSearchDetails = new ArrayList<TelemediaOrderReportDTO>(); TelemediaOrderReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetTelemediaOrderReport); /*if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { proc.setString(1, objDto.getFromCopcApprovedDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate()!= null && !"".equals(objDto.getToCopcApprovedDate())) { proc.setString(2, objDto.getToCopcApprovedDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ 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("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, 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("yyyy-MM-dd"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new TelemediaOrderReportDTO(); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setOrderNo(rs.getString("ORDERNO")); objReportsDto.setCrmAccountNoString(rs.getString("PARENT_ACCOUNT_NUMBER")); objReportsDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER")); objReportsDto.setCopcApproveDate(Utility.showDate_Report(rs.getString("COPC_APPROVED_DATE"))); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<PerformanceReportDTO> viewPerformanceList(PerformanceReportDTO objDto) { // Nagarjuna String methodName = "viewPerformanceList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end// w w w . j a v a 2 s . c om Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<PerformanceReportDTO> listSearchDetails = new ArrayList<PerformanceReportDTO>(); PerformanceReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPerformanceReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(13, objDto.getOsp().trim()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new PerformanceReportDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setProductName(rs.getString("PRODUCT_NAME")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setZoneName(rs.getString("ZONENNAME")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setAccountId(rs.getLong("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setOrder_type(rs.getString("ORDER_TYPE")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoAmountSum(rs.getLong("POAMOUNT")); objReportsDto.setPo_recpt_delay(rs.getString("PO_RECPT_DELAY")); objReportsDto.setPo_logging_delay(rs.getString("PO_LOGGING_DELAY")); objReportsDto.setOrder_completion_date(rs.getString("ORDER_COMPLETION_DATE")); objReportsDto.setAppr_delay_in_region(rs.getString("APPR_DELAY_IN_REGION")); objReportsDto.setDays_in_copc(rs.getString("DAYS_IN_COPC")); objReportsDto.setDays_for_order(rs.getString("DAYS_FOR_ORDER")); objReportsDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objReportsDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objReportsDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); objReportsDto.setOsp(rs.getString("OSP")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setOrderStatus(rs.getString("APPROVAL_STATUS")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setGroupName(rs.getString("GROUPNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); //lawkush start objReportsDto.setCopcStartDate(rs.getString("COPC_START_DATE")); if (rs.getString("COPC_START_DATE") != null && !"".equals(rs.getString("COPC_START_DATE"))) { objReportsDto.setCopcStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcEndDate(rs.getString("COPC_END_DATE")); if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) { objReportsDto.setCopcEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime()))) .toUpperCase()); } //lawkush End //[007] Start objReportsDto.setStandardReason(rs.getString("STANDARDREASON")); //[007] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }