List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<OBValueReportDTO> viewOBValueReport_Usage(OBValueReportDTO objDto) throws Exception { String methodName = "viewOBValueReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; ArrayList<OBValueReportDTO> objUserList = new ArrayList<OBValueReportDTO>(); Connection conn = null;/*from w w w .j av a 2s . co m*/ CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OBValueReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOBValueReport_Usage); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OBValueReportDTO(); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); objDto.setLineType(rs.getString("LINETYPE")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setPrimaryLocation(""); } if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setSeclocation(""); } objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setPrjmngname(rs.getString("PRJ_MGR_NAME")); objDto.setPrjmgremail(rs.getString("PROJECTMGR_MAIL")); objDto.setActmngname(rs.getString("ACCT_MGR_NAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setComponentName(rs.getString("COMPONENT_NAME")); objDto.setComponentID(rs.getInt("COMPONENTINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setServiceType(rs.getString("SERVICETYPE")); objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } } else { objDto.setTo_city(""); } objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACCOUNTMGR_EMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setLoc_date(rs.getString("LOC_DATE")); objDto.setFinalOB(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); objDto.setFinalOBINR(Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_INR"), 2)).toString()); objDto.setCopcApprovedBy(rs.getString("COPC_APPROVER_NAME")); objDto.setPmApprovedby(rs.getString("PM_APPROVER_NAME")); objDto.setDemoFlag(rs.getString("ISDEMO")); objDto.setOffnet(rs.getString("OFFNET_LABELATTVALUE")); objDto.setMediaType(rs.getString("MEDIA_LABELATTVALUE")); objDto.setCancellationReason(rs.getString("CANCELLATION_REASON")); objDto.setrRDate(rs.getString("RR_DATE")); if (rs.getString("RR_DATE") != null && !"".equals(rs.getString("RR_DATE"))) { objDto.setrRDate((utility.showDate_Report(new Date(rs.getTimestamp("RR_DATE").getTime()))) .toUpperCase()); } objDto.setDiffDays(rs.getString("DIFF_DAYS")); objDto.setOrderEnteredBy(rs.getString("ORDER_CREATED_BY_NAME")); objDto.setExchangeRate(BigDecimal.valueOf((rs.getDouble("EXCHANGE_RATE"))).toPlainString()); objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE_TRANSACTION"))).toPlainString()); objDto.setObValueINR( Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_TRANSACTION_INR"), 2)).toString()); objDto.setCustomerSegment(rs.getString("CUST_SEGMENT_CODE")); //newly added objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE")); //newly added objDto.setServiceRemarks(rs.getString("SERVICE_REMARKS")); // newly added objDto.setObMonth(rs.getString("OB_MONTH")); objDto.setObYear(rs.getString("OB_YEAR")); //pankaj objDto.setEntryType(rs.getString("ENTRY_TYPE")); objDto.setIsNfa("NFA"); //[007] start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[007] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<DisconnectChangeOrdeReportDTO> viewDisconnectionChangeOrderReport( DisconnectChangeOrdeReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionChangeOrderReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<DisconnectChangeOrdeReportDTO> listSearchDetails = new ArrayList<DisconnectChangeOrdeReportDTO>(); DisconnectChangeOrdeReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null;//from w w w . j a v a 2 s. c o m Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionChangeOrderReport); 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())) { 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.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.setInt(5, Integer.parseInt(objDto.getOrdersubtype().trim())); } else { proc.setNull(5, java.sql.Types.BIGINT); } 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()); if (objDto.getSrrequest() != null && !"".equals(objDto.getSrrequest())) { proc.setString(13, objDto.getSrrequest()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new DisconnectChangeOrdeReportDTO(); //[270513]Start : Added by Ashutosh for Billing Address setBlank(); //replaceSeperator("BILLING_LOCATION",rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); //[270513]Start objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingformat(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelId(rs.getInt("BILLING_LEVEL_NO")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setParty(rs.getString("PARTYNAME")); objReportsDto.setPm_pro_date(rs.getString("Pm_Prov_Date")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (!(rs.getString("LOCDATE") == null || rs.getString("LOCDATE") == "")) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); objReportsDto.setChild_act_no(rs.getString("Child_Account_Number")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setBillingtrigger_createdate(Utility.showDate_Report(tempDate).toUpperCase()); } ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setBillingtrigger_createdate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setActmgrname(rs.getString("ACCOUNTMANAGER")); objReportsDto.setPrjmngname(rs.getString("PROJECTMANAGER")); tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("SERVICE_RFS_DATE"); if (!(rs.getString("SERVICE_RFS_DATE") == null || rs.getString("SERVICE_RFS_DATE") == "")) { objReportsDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setCust_po_rec_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegion(rs.getString("REGION")); objReportsDto.setDemo(rs.getString("Demo_Type")); objReportsDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objReportsDto.setOrderStageDescription(rs.getString("STAGE")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS")); objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE")); if (!(rs.getString("DISCONNECTION_RECEIVE_DATE") == null || rs.getString("DISCONNECTION_RECEIVE_DATE") == "")) { //Date date=df.parse(objReportsDto.getRequest_rec_date()); //objReportsDto.setRequest_rec_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLineno(rs.getInt("Order_Line_Id")); objReportsDto.setOrdermonth(rs.getString("ORDERMONTH")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setStandard_reason(rs.getString("STANDARDREASON")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setBandwidth_att(rs.getString("BANDWIDTH_ATT")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setChargeable_Distance(rs.getString("DISTANCE")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setProductName(rs.getString("SERVICEDETDESCRIPTION")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); objReportsDto.setProdAlisName(rs.getString("PRODUCTNAME")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setSeclocation(VAR_SECONDARYLOCATION); objReportsDto.setSub_linename(rs.getString("SUBPRODUCTNAME")); objReportsDto.setOrderNo(rs.getString("ORDERNO")); objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setAmt(rs.getLong("CUST_TOT_PO_AMT")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setAdvance(rs.getString("ADVANCE")); objReportsDto.setContractMonths(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setPeriodsInMonths(rs.getString("PERIODS_IN_MONTH")); objReportsDto.setTotalPoAmt(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_NO")); objReportsDto.setCust_act_id(rs.getString("CUSTACCOUNTID")); objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo")); objReportsDto.setM6cktid(rs.getString("M6_CKTID")); objReportsDto.setBilling_location_from(rs.getString("FROM_ADDRESS")); objReportsDto.setBilling_location_to(rs.getString("TO_ADDRESS")); if (objDto.getIsUsage() == 1) { objReportsDto.setOrderStage(rs.getString("ORDERSTAGE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); 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.setLineno(rs.getInt("Order_Line_Id")); 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")); 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_STATUS")); dto.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO")); //dto.setComponentFXStatus(rs.getString("FX_STATUS")); objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); objReportsDto.setSourcePartyID(rs.getLong("PARTY_ID")); objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); 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.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); ts = rs.getTimestamp("COPC_APPROVED_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setCopcapprovaldate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setSrno(rs.getString("SR_NO")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE")); objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE")); objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)")); objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)")); objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS")); objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY")); objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR")); /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/ objReportsDto.setComponentDto(dto); } else { objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); 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.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setChargeEndDate(rs.getString("END_DATE")); if (!(rs.getString("END_DATE") == null || rs.getString("END_DATE") == "")) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setStartDate(rs.getString("START_DATE")); if (!(rs.getString("START_DATE") == null || rs.getString("START_DATE") == "")) { Date date = df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setHardwareType(rs.getString("HARDWARETYPENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setNature_sale(rs.getString("SALENATURE")); objReportsDto.setType_sale(rs.getString("SALETYPE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) { Date date = df.parse(objReportsDto.getChallendate()); objReportsDto.setChallendate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGETYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setSrno(rs.getString("SR_NO")); objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1")); objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setInstallation_addressaa1(rs.getString("INSTALLEMENT")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnualRate(rs.getString("ANNUAL_RATE")); objReportsDto.setPk_charge_id(rs.getString("CHARGEINFOID")); objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); /*20151224-R1-021980 - Changes in Disconnection Report || ROM * CHanged by :satish Start*/ objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE")); objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE")); objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)")); objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)")); objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS")); objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY")); objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR")); /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/ } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<NonAPP_APPChangeOrderDetailsDTO> viewNonMigAppUnappNewOrderDetails( NonAPP_APPChangeOrderDetailsDTO objDto) { //Nagarjuna/* ww w .j a va 2 s . c o m*/ 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<DisconnectLineReportDTO> viewDisconnectionLineReport(DisconnectLineReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end // w ww . j av a 2 s . c om Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<DisconnectLineReportDTO> listSearchDetails = new ArrayList<DisconnectLineReportDTO>(); DisconnectLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionLineReport); /*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())) { 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.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } System.out.println("Vijay 2"); if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) { proc.setString(4, objDto.getOrdersubtype().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); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index System.out.println("Vijay 3"); proc.setString(3, pagingSorting.getSortByColumn());// columnName System.out.println("Vijay sort column name :"+pagingSorting.getSortByColumn()); proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting .getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));*/ if (objDto.getOrderNo() != null && !"".equals(objDto.getOrderNo())) { proc.setInt(1, new Integer(objDto.getOrderNo())); } else { proc.setNull(1, java.sql.Types.BIGINT); } //[303030]START if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate()) && objDto.getToDate() != null && !"".equals(objDto.getToDate())) { //proc.setString(2, objDto.getCopcApproveDate()); 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("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getFromDate()); formatter = new SimpleDateFormat("dd/MM/yyyy"); dateStr = formatter.parse(objDto.getToDate()); formattedDate = formatter.format(dateStr); date1 = formatter.parse(objDto.getToDate()); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(3, formattedDate); ///proc.setString(3, objDto.getToDate()); } else { proc.setNull(2, java.sql.Types.VARCHAR); proc.setNull(3, java.sql.Types.VARCHAR); } //[303030]END 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 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")); 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.setCreditPeriod(rs.getInt("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.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); 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")); 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()); } //lawkush Start objReportsDto.setFrequencyAmount(rs.getDouble("INV_AMT")); objReportsDto.setChargeAmount(rs.getDouble("CUST_TOT_PO_AMT")); //lawkush End // objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); //do objReportsDto.setLineamt(0); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); 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.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.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); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_FLAG")); 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.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")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setChild_account_number(rs.getString("CHILD_ACCOUNT_NUMBER")); objReportsDto.setOrdermonth(rs.getString("ORDER_MONTH")); objReportsDto.setCkt_id(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.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<RateRenewalReportDTO> viewRateRenewalReport(RateRenewalReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewRateRenewalReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<RateRenewalReportDTO> objUserList = new ArrayList<RateRenewalReportDTO>(); Connection conn = null;//from w ww. java2 s . c o m CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; RateRenewalReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlRateRenewalReportforUsage); /* 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(1, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(1, 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(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } /*if (objDto.getDemo() != null && !"".equals(objDto.getDemo())) { proc.setString(4, objDto.getDemo().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } */ if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(3, objDto.getFromAccountNo()); proc.setLong(4, objDto.getToAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(5, objDto.getFromOrderNo()); proc.setLong(6, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index proc.setInt(12, (objDto.getIsUsage()));// end if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(13, formattedDate); } else { proc.setNull(13, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(14, formattedDate1); } else { proc.setNull(14, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objRDto = new RateRenewalReportDTO(); objRDto.setPartyNo(rs.getInt("PARTY_NO")); objRDto.setPartyName(rs.getString("PARTYNAME")); objRDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objRDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objRDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objRDto.setServiceSegment(rs.getString("SERVICESEGMENT"));//Added in View objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objRDto.setRegionName(rs.getString("REGION")); //objRDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));PARENT_NAME objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); //objRDto.setChangeTypeName(rs.getString("NAME_SUBTYPE"));SERVICE_ORDER_TYPE_DESC objRDto.setOrderType(rs.getString("ORDERTYPE")); //objRDto.setCompanyName(rs.getString("COMPANYNAME"));ENTITYNAME //objRDto.setCurrencyCode(rs.getString("CURNAME"));CURRENCYNAME /*objRDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date=df.parse(objRDto.getPoDate()); objRDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); //objDto.setPoDate((utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); objRDto.setPoDate(utility.showDate_Report(df.parse(rs.getString("PODATE"))).toUpperCase()); }*/ tempDate = rs.getDate("PODATE"); objRDto.setPoDate(rs.getString("PODATE")); if (tempDate != null && !"".equals(tempDate)) { objRDto.setPoDate((utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); //objRDto.setFromLocation(rs.getString("PRIMARYLOCATION")); //objRDto.setToLocation(rs.getString("SECONDARYLOCATION")); setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); objRDto.setFromLocation(VAR_PRIMARYLOCATION); //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objRDto.setToLocation(VAR_SECONDARYLOCATION); objRDto.setDistance(rs.getString("DISTANCE")); objRDto.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION"));//Changed Column Name :AKS objRDto.setLOC_Date(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objRDto.getLOC_Date()); objRDto.setLOC_Date((utility.showDate_Report(date)).toUpperCase()); } objRDto.setLogicalCircuitNumber(rs.getString("CKTID")); objRDto.setTaxationName(rs.getString("TAXATIONVALUE")); objRDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objRDto.setOrder_type(rs.getString("DEMO_TYPE")); objRDto.setServiceStage(rs.getString("SERVICE_STAGE")); objRDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objRDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objRDto.getBilling_trigger_date()); objRDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } //objRDto.setBillingTriggerFlag(rs.getString("BILLINGTRIGGERFLAG"));Billing_Trigger_Flag //objRDto.setZoneName(rs.getString("ZONE"));ZONENNAME objRDto.setSalesCoordinator(rs.getString("SALESCOORDINATOR"));//Column Added in View :AKS //objRDto.setPoAmounts(rs.getDouble("POAMOUNT"));ORDERTOTAL objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objRDto.setItemQuantity(1);//Need to Ask Ravneet : AKS //objRDto.setServiceId(rs.getInt("SERVICEID"));SERVICE_NO objRDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objRDto.setCustSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO")); objRDto.setM6cktid(rs.getString("CKTID")); //objRDto.setServiceProductID(rs.getInt("SERVICEPRODUCTID"));Order_Line_Id objRDto.setOrderNo(rs.getString("ORDERNO")); if (objDto.getIsUsage() == 0) { objRDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { objRDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objRDto.setChargeName(rs.getString("CHARGE_NAME")); objRDto.setChargeTypeName(rs.getString("CHARGENAME")); objRDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objRDto.getStartDate()); objRDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objRDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objRDto.getEndDate()); objRDto.setEndDate((utility.showDate_Report(date)).toUpperCase()); } objRDto.setEndHWDateLogic(rs.getString("ENDDATELOGIC")); objRDto.setCharge_status(rs.getString("CHARGES_STATUS")); objRDto.setStartDaysLogic(rs.getString("CHARGESTARTDAYSLOGIC")); objRDto.setCurrencyCode(rs.getString("CURNAME")); objRDto.setStartMonthsLogic(rs.getString("CHARGESTARTMONTHSLOGIC")); objRDto.setOrderTotal(rs.getDouble("POAMOUNT")); objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objRDto.setOldLineitemAmount(rs.getString("CHARGEAMOUNT")); objRDto.setChargePeriod(rs.getInt("CHARGEPERIOD")); objRDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objRDto.setTxtStartDays(rs.getInt("START_DATE_DAYS")); objRDto.setTxtStartMonth(rs.getInt("START_DATE_MONTH")); objRDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setChangeTypeName(rs.getString("NAME_SUBTYPE")); objRDto.setCompanyName(rs.getString("COMPANYNAME")); objRDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objRDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objRDto.setStageName(rs.getString("ORDER_STAGE")); objRDto.setRemarks(rs.getString("REMARKS")); objRDto.setProductName(rs.getString("SERVICESTAGE")); objRDto.setSubProductName(rs.getString("SERVICESUBTYPENAME")); objRDto.setZoneName(rs.getString("ZONE")); objRDto.setPoAmounts(rs.getDouble("POAMOUNT")); objRDto.setServiceId(rs.getInt("SERVICEID")); objRDto.setServiceProductID(rs.getInt("SERVICEPRODUCTID")); objRDto.setBillingTriggerFlag(rs.getString("BILLINGTRIGGERFLAG")); objRDto.setOldOrderNo(rs.getInt("OLDORDERNO")); } if (objDto.getIsUsage() == 1) { objRDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objRDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objRDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));//FX_ACCOUNT_EXTERNAL_ID objRDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objRDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));//CHILD_ACCOUNT_FX_STATUS objRDto.setPackageID(rs.getInt("PACKAGE_ID")); objRDto.setPackageName(rs.getString("PACKAGE_NAME")); objRDto.setComponentID(rs.getInt("COMPONENT_ID")); objRDto.setComponentName(rs.getString("COMPONENT_NAME")); objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));//RC_NRC_COMP_AMOUNT dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDateDays(rs.getInt("COMP_START_DAYS"));//START_DAYS dto.setStartDateMonth(rs.getInt("COMP_START_MONTHS"));//START_MONTHS dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEndDateDays(rs.getInt("COMP_END_DAYS"));//END_DAYS dto.setEndDateMonth(rs.getInt("COMP_END_MONTHS"));//END_MONTHS objRDto.setServiceDetDescription(rs.getString("PARENT_NAME")); objRDto.setChangeTypeName(rs.getString("SERVICE_ORDER_TYPE_DESC")); objRDto.setCompanyName(rs.getString("ENTITYNAME")); objRDto.setCopcApproveDate(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objRDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } objRDto.setStageName(rs.getString("ORDERSTAGE")); objRDto.setRemarks(rs.getString("DISCONNECTION_REMARKS")); objRDto.setProductName(rs.getString("SERVICENAME")); objRDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objRDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objRDto.setZoneName(rs.getString("ZONENNAME")); objRDto.setPoAmounts(rs.getDouble("TOTALPOAMOUNT")); objRDto.setServiceId(rs.getInt("SERVICE_NO")); objRDto.setServiceProductID(rs.getInt("Order_Line_Id")); objRDto.setBillingTriggerFlag(rs.getString("Billing_Trigger_Flag")); objRDto.setOldOrderNo(rs.getInt("Pre_Crm_orderNo")); 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.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()); } objRDto.setComponentDto(dto); //NANCY START objRDto.setIsDifferential(rs.getString("IS_DIFFERENTIAL")); objRDto.setCopcApproverName(rs.getString("COPC_APPROVER_NAME")); objRDto.setEffectiveDate(rs.getString("EFFECTIVEDATE")); if (rs.getString("EFFECTIVEDATE") != null && !"".equals(rs.getString("EFFECTIVEDATE"))) { objRDto.setEffectiveDate( (utility.showDate_Report(new Date(rs.getTimestamp("EFFECTIVEDATE").getTime()))) .toUpperCase()); } objRDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objRDto.setBillingTriggerCreateDate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objRDto.setIsTriggerRequired(rs.getString("IsTriggerRequired")); objRDto.setLineTriggered(rs.getString("LineTriggered")); objRDto.setTriggerProcess(rs.getString("TriggerProcess")); objRDto.setTriggerDoneBy(rs.getString("TriggerDoneBy")); objRDto.setAutomaticTriggerError(rs.getString("AutomaticTriggerError")); //NANCY END } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objRDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.cmart.DB.CassandraDBQuery.java
public long moveEndedItemsDB() throws Exception { int itemsMoved = 0; int attemptsRemaining = 5; /**/*from w w w .j av a 2 s . c o m*/ * 1. Get the ids of all the items that have ended * 2. delete from items, ritems, rprice items * 3. Move all of the bids for the items * 4. delete from bids * 5. move all of the max bids * 6. delete max bids * 7. create the purchase rows for people who have won items (must have beat reserve) */ do { Connection conn = this.getConnection(); PreparedStatement getItems = null; ResultSet itemRS = null; PreparedStatement copyItems = null; PreparedStatement deleteItems = null; PreparedStatement deleteRItems = null; PreparedStatement deletePIItems = null; PreparedStatement deleteRPIItems = null; PreparedStatement getBids = null; ResultSet bidsrs = null; PreparedStatement copyBids = null; PreparedStatement deleteBids = null; PreparedStatement getMaxBid = null; ResultSet maxbidsrs = null; PreparedStatement copyMaxBids = null; PreparedStatement deleteMaxBids = null; if (conn != null) { try { // Get the current time so we only move items before this fixed time (make the end of the key 9999...) Long KEY = ((System.currentTimeMillis() * shortback) * 10) - 1; Boolean multi = false; Boolean exists = false; StringBuilder CQL = new StringBuilder(2048); /* * Copy the items */ getItems = conn.prepareStatement("SELECT * FROM items WHERE KEY>1 AND KEY < " + KEY.toString()); itemRS = getItems.executeQuery(); CQL = new StringBuilder(2048); // The keys for reverse price and items to delete StringBuilder itemids = new StringBuilder(1024); itemids.append("('0'"); StringBuilder pikeys = new StringBuilder(1024); pikeys.append("('0'"); StringBuilder rpikeys = new StringBuilder(1024); rpikeys.append("('0'"); StringBuilder ritem = new StringBuilder(1024); ritem.append("('0'"); ArrayList<Long> itemIDarr = new ArrayList<Long>(); // Get the first item if (itemRS.next()) { Long sellerid = null; try { sellerid = itemRS.getLong("sellerid"); } catch (Exception e) { } // If the item really exists if (sellerid != null) { exists = true; itemids.append(",'" + itemRS.getString("KEY") + "'"); pikeys.append(",'" + itemRS.getString("pikey") + "'"); rpikeys.append( ",'" + (Long.MAX_VALUE - Long.parseLong(itemRS.getString("pikey"))) + "'"); ritem.append(",'" + (Long.MAX_VALUE - Long.parseLong(itemRS.getString("KEY"))) + "'"); itemIDarr.add(Long.parseLong(itemRS.getString("KEY"))); itemsMoved++; // keep count to return CQL.append( "INSERT INTO olditems (KEY,itemid,name,description,quantity,startprice,reserveprice,buynowprice,curbid,maxbid,noofbids,startdate,enddate,sellerid,categoryid,thumbnail) VALUES (" + itemRS.getString("KEY") + "," + itemRS.getLong("itemid") + ",'" + itemRS.getString("name") + "','" + itemRS.getString("description") + "'," + itemRS.getLong("quantity") + ",'" + itemRS.getString("startprice") + "','" + itemRS.getString("reserveprice") + "','" + itemRS.getString("buynowprice") + "','" + itemRS.getString("curbid") + "','" + itemRS.getString("maxbid") + "'," + itemRS.getLong("noofbids") + "," + itemRS.getLong("startdate") + "," + itemRS.getLong("enddate") + "," + itemRS.getLong("sellerid") + "," + itemRS.getLong("categoryid") + ",'" + itemRS.getString("thumbnail") + "');"); } } // Get all of the other items while (itemRS.next()) { Long sellerid = null; try { sellerid = itemRS.getLong("sellerid"); } catch (Exception e) { } if (sellerid != null) { exists = true; if (!multi) { multi = true; exists = true; itemids.append(",'" + KEY + "'"); itemIDarr.add(Long.parseLong(itemRS.getString("KEY"))); itemsMoved++; String temp = CQL.toString(); CQL = new StringBuilder(2048); CQL.append("BEGIN BATCH\n "); CQL.append(temp); CQL.append( "INSERT INTO olditems (KEY,itemid,name,description,quantity,startprice,reserveprice,buynowprice,curbid,maxbid,noofbids,startdate,enddate,sellerid,categoryid,thumbnail) VALUES (" + itemRS.getString("KEY") + "," + itemRS.getLong("itemid") + ",'" + itemRS.getString("name") + "','" + itemRS.getString("description") + "'," + itemRS.getLong("quantity") + ",'" + itemRS.getString("startprice") + "','" + itemRS.getString("reserveprice") + "','" + itemRS.getString("buynowprice") + "','" + itemRS.getString("curbid") + "','" + itemRS.getString("maxbid") + "'," + itemRS.getLong("noofbids") + "," + itemRS.getLong("startdate") + "," + itemRS.getLong("enddate") + "," + itemRS.getLong("sellerid") + "," + itemRS.getLong("categoryid") + ",'" + itemRS.getString("thumbnail") + "');"); } else { exists = true; itemids.append(",'" + KEY + "'"); itemIDarr.add(Long.parseLong(itemRS.getString("KEY"))); itemsMoved++; CQL.append( "INSERT INTO olditems (KEY,itemid,name,description,quantity,startprice,reserveprice,buynowprice,curbid,maxbid,noofbids,startdate,enddate,sellerid,categoryid,thumbnail) VALUES (" + itemRS.getString("KEY") + "," + itemRS.getLong("itemid") + ",'" + itemRS.getString("name") + "','" + itemRS.getString("description") + "'," + itemRS.getLong("quantity") + ",'" + itemRS.getString("startprice") + "','" + itemRS.getString("reserveprice") + "','" + itemRS.getString("buynowprice") + "','" + itemRS.getString("curbid") + "','" + itemRS.getString("maxbid") + "'," + itemRS.getLong("noofbids") + "," + itemRS.getLong("startdate") + "," + itemRS.getLong("enddate") + "," + itemRS.getLong("sellerid") + "," + itemRS.getLong("categoryid") + ",'" + itemRS.getString("thumbnail") + "');"); } } } // If there are multiple items end batch if (multi) { CQL.append("APPLY BATCH;"); } // copy the old items to the old items table if (exists) { System.out.println(CQL.toString()); copyItems = conn.prepareStatement(CQL.toString()); copyItems.executeUpdate(); deleteItems = conn .prepareStatement("DELETE FROM items WHERE KEY IN " + itemids.toString() + ");"); deleteItems.executeUpdate(); deleteRItems = conn.prepareStatement( "DELETE FROM revtimeitems WHERE KEY IN " + ritem.toString() + ");"); deleteRItems.executeUpdate(); deletePIItems = conn.prepareStatement( "DELETE FROM priceitems WHERE KEY IN " + pikeys.toString() + ");"); deletePIItems.executeUpdate(); deleteRPIItems = conn.prepareStatement( "DELETE FROM revpriceitems WHERE KEY IN " + rpikeys.toString() + ");"); deleteRPIItems.executeUpdate(); } /* * Copy the bids */ StringBuilder delbidids = new StringBuilder(1024); delbidids.append("('0'"); // copy the old bids to the old bids table for (Long itemID : itemIDarr) { getBids = conn.prepareStatement("SELECT * FROM bids WHERE itemid=" + itemID); bidsrs = getBids.executeQuery(); multi = false; exists = false; CQL = new StringBuilder(2048); // Get the first result and mark that we have some CQL to execute if (bidsrs.next()) { // Make sure row is real Long userid = null; try { userid = bidsrs.getLong("userid"); } catch (Exception e) { } // Add row if (userid != null) { exists = true; delbidids.append(",'" + bidsrs.getString("KEY") + "'"); CQL.append( "INSERT INTO oldbids (KEY,bid,biddate,itemid,maxbid,quantity,userid) VALUES ('" + bidsrs.getString("KEY") + "','" + bidsrs.getString("bid") + "'," + bidsrs.getLong("biddate") + "," + bidsrs.getLong("itemid") + ",'" + bidsrs.getString("maxbid") + "','" + bidsrs.getString("quantity") + "'," + bidsrs.getLong("userid") + ");"); } } // Add the other rows while (bidsrs.next()) { Long userid = null; try { userid = bidsrs.getLong("userid"); } catch (Exception e) { } if (userid != null) { exists = true; // If there are multiple rows then we'll batch it if (!multi) { exists = true; multi = true; delbidids.append(",'" + bidsrs.getString("KEY") + "'"); String temp = CQL.toString(); CQL = new StringBuilder(2048); CQL.append("BEGIN BATCH\n "); CQL.append(temp); CQL.append( "INSERT INTO oldbids (KEY,bid,biddate,itemid,maxbid,quantity,userid) VALUES ('" + bidsrs.getString("KEY") + "','" + bidsrs.getString("bid") + "'," + bidsrs.getLong("biddate") + "," + bidsrs.getLong("itemid") + ",'" + bidsrs.getString("maxbid") + "','" + bidsrs.getString("quantity") + "'," + bidsrs.getLong("userid") + ");"); } else { exists = true; delbidids.append(",'" + bidsrs.getString("KEY") + "'"); CQL.append( "INSERT INTO oldbids (KEY,bid,biddate,itemid,maxbid,quantity,userid) VALUES ('" + bidsrs.getString("KEY") + "','" + bidsrs.getString("bid") + "'," + bidsrs.getLong("biddate") + "," + bidsrs.getLong("itemid") + ",'" + bidsrs.getString("maxbid") + "','" + bidsrs.getString("quantity") + "'," + bidsrs.getLong("userid") + ");"); } } } // if we batched it, end the batch if (multi) { CQL.append("APPLY BATCH;"); } if (exists) { copyBids = conn.prepareStatement(CQL.toString()); copyBids.executeUpdate(); copyBids.close(); } bidsrs.close(); getBids.close(); } // Delete the replica bids from the bids table deleteBids = conn .prepareStatement("DELETE FROM bids WHERE KEY IN " + delbidids.toString() + ");"); deleteBids.executeUpdate(); // Copy the max bids StringBuilder maxbidids = new StringBuilder(1024); maxbidids.append("('0'"); for (Long id : itemIDarr) { // Get the max bids getMaxBid = conn.prepareStatement("SELECT * FROM maxbids WHERE itemid=" + id); maxbidsrs = getMaxBid.executeQuery(); CQL = new StringBuilder(2048); multi = false; exists = false; // Get the first item if (maxbidsrs.next()) { Long bidkey = null; try { bidkey = maxbidsrs.getLong("bidkey"); } catch (Exception e) { } // If the item really exists if (bidkey != null) { exists = true; maxbidids.append(",'" + maxbidsrs.getString("KEY") + "'"); CQL.append( "INSERT INTO maxoldbids (KEY, bidkey, userid, itemid, quantity, bid, maxbid, biddate, ts) " + "VALUES ('" + maxbidsrs.getString("KEY") + "','" + bidkey + "','" + maxbidsrs.getLong("userid") + "','" + maxbidsrs.getLong("itemid") + "','" + maxbidsrs.getLong("quantity") + "','" + maxbidsrs.getDouble("bid") + "','" + maxbidsrs.getDouble("maxbid") + "','" + maxbidsrs.getLong("biddate") + "'," + maxbidsrs.getLong("ts") + ");"); } } // Get all of the other items while (maxbidsrs.next()) { Long bidkey = null; try { bidkey = maxbidsrs.getLong("bidkey"); } catch (Exception e) { } if (bidkey != null) { exists = true; if (!multi) { multi = true; exists = true; maxbidids.append(",'" + maxbidsrs.getString("KEY") + "'"); String temp = CQL.toString(); CQL = new StringBuilder(2048); CQL.append("BEGIN BATCH\n "); CQL.append(temp); CQL.append( "INSERT INTO maxoldbids (KEY, bidkey, userid, itemid, quantity, bid, maxbid, biddate, ts) " + "VALUES ('" + maxbidsrs.getString("KEY") + "','" + bidkey + "','" + maxbidsrs.getLong("userid") + "','" + maxbidsrs.getLong("itemid") + "','" + maxbidsrs.getLong("quantity") + "','" + maxbidsrs.getDouble("bid") + "','" + maxbidsrs.getDouble("maxbid") + "','" + maxbidsrs.getLong("biddate") + "'," + maxbidsrs.getLong("ts") + ");"); } else { exists = true; maxbidids.append(",'" + maxbidsrs.getString("KEY") + "'"); CQL.append( "INSERT INTO maxoldbids (KEY, bidkey, userid, itemid, quantity, bid, maxbid, biddate, ts) " + "VALUES ('" + maxbidsrs.getString("KEY") + "','" + bidkey + "','" + maxbidsrs.getLong("userid") + "','" + maxbidsrs.getLong("itemid") + "','" + maxbidsrs.getLong("quantity") + "','" + maxbidsrs.getDouble("bid") + "','" + maxbidsrs.getDouble("maxbid") + "','" + maxbidsrs.getLong("biddate") + "'," + maxbidsrs.getLong("ts") + ");"); } } } // If there are multiple items end batch if (multi) { CQL.append("APPLY BATCH;"); } if (exists) { copyMaxBids = conn.prepareStatement(CQL.toString()); copyMaxBids.executeUpdate(); copyMaxBids.close(); } maxbidsrs.close(); getMaxBid.close(); } // Delete the max bids deleteMaxBids = conn .prepareStatement("DELETE FROM maxbids WHERE KEY IN " + maxbidids.toString() + ");"); deleteMaxBids.executeUpdate(); attemptsRemaining = 0; } catch (Exception e) { System.out.println("CassandraDBQuery (moveEndedItems): Could not read results set"); e.printStackTrace(); throw e; } finally { this.close(itemRS); this.closeSmt(getItems); this.closeSmt(copyItems); this.closeSmt(deleteItems); this.closeSmt(deleteRItems); this.closeSmt(deletePIItems); this.closeSmt(deleteRPIItems); this.closeSmt(deleteBids); if (bidsrs != null) this.close(bidsrs); if (maxbidsrs != null) this.close(maxbidsrs); this.closeSmt(deleteMaxBids); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining > 0); return itemsMoved; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * Create a Report to generate LEPM Order Cancel Report //from w w w . ja v a2 s .c om * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<LempCancelOrderReportDTO> viewLEPMOrderCancelReport(LempCancelOrderReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewLEPMOrderCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<LempCancelOrderReportDTO> objUserList = new ArrayList<LempCancelOrderReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; int countFlag = 0; LempCancelOrderReportDTO objReportsDto = null; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); ArrayList<LempCancelOrderReportDTO> listSearchDetails = new ArrayList<LempCancelOrderReportDTO>(); try { //[101010]START conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlLEPMOrderCancelReport); if (objDto.getCanceldatefrom() != null && !"".equals(objDto.getCanceldatefrom())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getCanceldatefrom()); 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.getCanceldateto() != null && !"".equals(objDto.getCanceldateto())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getCanceldateto()); 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); } /*if (objDto.getCanceldatefrom() != null && !"".equals(objDto.getCanceldatefrom())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter.parse(objDto.getCanceldatefrom()); String formattedDate1 = formatter.format(dateStr1); proc.setString(1, formattedDate1); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getCanceldateto() != null && !"".equals(objDto.getCanceldateto())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr2 = formatter1.parse(objDto.getCanceldateto()); String formattedDate2 = formatter1.format(dateStr2); proc.setString(1, formattedDate2); proc.setString(2, objDto.getCanceldateto().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ //[101010]END 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 rs = proc.executeQuery(); while (rs.next() != false) { objDto = new LempCancelOrderReportDTO(); 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.setLineItemAmount(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.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.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.setPartyid(rs.getString("PARTY_ID")); 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")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { //[505053] start try { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } } catch (Exception exp) { //It's okay to ignore 'exp' here because setting a default value objDto.setFrom_city(" "); } //[505053] end } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { //[505053] start try { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } } catch (Exception exp) { //It's okay to ignore 'exp' here because setting a default value objDto.setTo_city(" "); } //[505053] end } else { objDto.setTo_city(""); } objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); //objDto.setAm_approval_remarks(rs.getString("AM_APPROVAL_TASK_REMARKS")); //objDto.setPm_approval_remarks(rs.getString("PM_APPROVAL_TASK_REMARKS")); //objDto.setCopc_approval_remarks(rs.getString("COPC_APPROVAL_TASK_REMARKS")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); //nagarjuna objDto.setCharge_Disconnection_Status(rs.getString("CHARGES_STATUS")); objDto.setSubchange_type(rs.getString("NAME_SUBTYPE")); //nagarjuna end objDto.setServiceStage(rs.getString("STAGE")); //[909090] objDto.setOrd_cancel_reason(rs.getString("ORD_CANCEL_REASON")); //Start [10990] objDto.setServ_cancel_reson(rs.getString("SERV_CANCEL_REASON")); objDto.setService_cancelledby(rs.getString("SERVICE_CANCELLEDBY")); objDto.setServ_cancel_remarks(rs.getString("SERV_CANCEL_REMARKS")); objDto.setService_cancl_date(rs.getString("SERVICE_CANCEL_DATE")); if (rs.getString("SERVICE_CANCEL_DATE") != null && !"".equals(rs.getString("SERVICE_CANCEL_DATE")))//service_cancel_Date for IB2B cancelation { objDto.setService_cancl_date( (Utility.showDate_Report((rs.getDate("SERVICE_CANCEL_DATE")))).toUpperCase()); } //end [10990] objDto.setCanceldate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCanceldate((Utility.showDate_Report((rs.getDate("CANCEL_DATE")))).toUpperCase()); } //lawkush Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //lawkush 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<RateRenewalReportDTO> viewRateRenewalReport(RateRenewalReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewRateRenewalReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from w w w. j a v a 2s . c o m*/ ArrayList<RateRenewalReportDTO> objUserList = new ArrayList<RateRenewalReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; RateRenewalReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlRateRenewalReport); /* 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(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(2, objDto.ge tToDate().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(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.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("yyyy-MM-dd"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } /*if (objDto.getDemo() != null && !"".equals(objDto.getDemo())) { proc.setString(4, objDto.getDemo().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } */ if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(3, objDto.getFromAccountNo()); proc.setLong(4, objDto.getToAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(5, objDto.getFromOrderNo()); proc.setLong(6, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index //sada if (objDto.getCus_segment() != null && !"".equals(objDto.getCus_segment())) { proc.setString(12, objDto.getCus_segment().trim().toUpperCase()); } else { proc.setNull(12, java.sql.Types.VARCHAR); } //sada rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new RateRenewalReportDTO(); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setServiceSegment(rs.getString("SERVICESEGMENT")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setRegionName(rs.getString("REGION")); objDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setChangeTypeName(rs.getString("NAME_SUBTYPE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setCompanyName(rs.getString("COMPANYNAME")); 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.setCurrencyCode(rs.getString("CURNAME")); objDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { objDto.setPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeTypeName(rs.getString("CHARGENAME")); objDto.setFromLocation(rs.getString("PRIMARYLOCATION")); objDto.setToLocation(rs.getString("SECONDARYLOCATION")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setLineItemDescription(rs.getString("LINE_ITEM_DESCRIPTION")); objDto.setLOC_Date(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objDto.getLOC_Date()); objDto.setLOC_Date((utility.showDate_Report(date)).toUpperCase()); } 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.setLogicalCircuitNumber(rs.getString("CKTID")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setTaxationName(rs.getString("TAXATIONVALUE")); objDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objDto.setOrder_type(rs.getString("DEMO_TYPE")); objDto.setServiceStage(rs.getString("SERVICE_STAGE")); objDto.setStageName(rs.getString("ORDER_STAGE")); objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objDto.setMocn_no(rs.getString("MOCN_NUMBER")); objDto.setRemarks(rs.getString("REMARKS")); //nagarjuna objDto.setProductName(rs.getString("PRODUCTNAME")); //nagarjuna objDto.setSubProductName(rs.getString("SERVICESUBTYPENAME")); objDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objDto.getBilling_trigger_date()); objDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } objDto.setBillingTriggerFlag(rs.getString("BILLINGTRIGGERFLAG")); objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objDto.getStartDate()); objDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objDto.getEndDate()); objDto.setEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setEndHWDateLogic(rs.getString("ENDDATELOGIC")); /*[RPT7052013027]- start - change TD to Till Disconnection and BTD to Billing Trigger Done */ if (rs.getString("ENDDATELOGIC") != null && rs.getString("ENDDATELOGIC").equalsIgnoreCase("TD")) { objDto.setEndHWDateLogic("TILL DISCONNECTION"); } else if (rs.getString("ENDDATELOGIC") != null && rs.getString("ENDDATELOGIC").equalsIgnoreCase("BTD")) { objDto.setEndHWDateLogic("Billing Trigger Done"); } /*[RPT7052013027] - end */ objDto.setCharge_status(rs.getString("CHARGES_STATUS")); objDto.setStartDaysLogic(rs.getString("CHARGESTARTDAYSLOGIC")); objDto.setStartMonthsLogic(rs.getString("CHARGESTARTMONTHSLOGIC")); objDto.setZoneName(rs.getString("ZONE")); objDto.setSalesCoordinator(rs.getString("SALESCOORDINATOR")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setChargePeriod(rs.getInt("CHARGEPERIOD")); objDto.setItemQuantity(1); objDto.setTotalPoAmt(rs.getString("POAMOUNT")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objDto.setLogicalSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO")); objDto.setM6cktid(rs.getString("CKTID")); objDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objDto.setServiceProductID(rs.getInt("SERVICEPRODUCTID")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setOldOrderNo(rs.getInt("OLDORDERNO")); objDto.setChargeAmount_String(rs.getString("CHARGEAMOUNT")); objDto.setOldLineitemAmount(rs.getString("OLDCHARGEAMOUNT")); objDto.setTxtStartDays(rs.getInt("START_DATE_DAYS")); objDto.setTxtStartMonth(rs.getInt("START_DATE_MONTH")); /* [RPT7052013027]- start --add some fields */ objDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO")); objDto.setRatio(rs.getString("ratio")); objDto.setPk_charge_id(rs.getString("CHARGEINFOID")); /* [RPT7052013027] -end*/ objDto.setStandardReason(rs.getString("STANDARDREASON")); //NANCY START objDto.setIsDifferential(rs.getString("IS_DIFFERENTIAL")); objDto.setOldPkChargeId(rs.getLong("OLD_PK_CHARGEID")); objDto.setCopcApproverName(rs.getString("COPC_APPROVER_NAME")); objDto.setEffectiveDate(rs.getString("EFFECTIVEDATE")); if (rs.getString("EFFECTIVEDATE") != null && !"".equals(rs.getString("EFFECTIVEDATE"))) { objDto.setEffectiveDate( (utility.showDate_Report(new Date(rs.getTimestamp("EFFECTIVEDATE").getTime()))) .toUpperCase()); } objDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objDto.setBillingTriggerCreateDate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objDto.setIsTriggerRequired(rs.getString("IsTriggerRequired")); objDto.setLineTriggered(rs.getString("LineTriggered")); objDto.setTriggerProcess(rs.getString("TriggerProcess")); objDto.setTriggerDoneBy(rs.getString("TriggerDoneBy")); objDto.setAutomaticTriggerError(rs.getString("AutomaticTriggerError")); //NANCY END if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<BillingTriggerDoneButFailedInFXDTO> viewBillingTriggerDoneButFailedInFX( BillingTriggerDoneButFailedInFXDTO objDto) { //Nagarjuna/*from w ww . j av a 2s .c o m*/ String methodName = "viewBillingTriggerDoneButFailedInFX", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<BillingTriggerDoneButFailedInFXDTO> listSearchDetails = new ArrayList<BillingTriggerDoneButFailedInFXDTO>(); BillingTriggerDoneButFailedInFXDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetBillingTriggerDoneButFailedInFX); 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)); proc.setInt(13, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new BillingTriggerDoneButFailedInFXDTO(); //[270513]Start : Added by Ashutosh for Billing Address setBlank(); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); //[270513]Start objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); //orderStageDescription tempDate = rs.getDate("CONTRACTSTARTDATE"); if (tempDate != null) { objReportsDto.setContractStartDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("CONTRACTENDDATE"); if (tempDate != null) { objReportsDto.setContractEndDate(Utility.showDate_Report(tempDate).toUpperCase()); } objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION); objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION); objReportsDto.setPonum(rs.getLong("PONUMBER")); tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setLOC_Date(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setLOC_Date(s5); } 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.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()); } tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("ORDER_APPROVED_DATE"); if (tempDate != null) { objReportsDto.setOrderApproveDate(Utility.showDate_Report(tempDate).toUpperCase()); } ts = rs.getTimestamp("COPC_APPROVED_DATE");//Copc date if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setCopcApproveDate(Utility.showDate_Report(tempDate).toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id // --Service Order Type // ''SERVICE ORDER TYPE DESC'' AS SERVICE_ORDER_TYPE_DESC, // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) // --Charge Type Id objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE tempDate = rs.getDate("SERVICE_RFS_DATE"); if (tempDate != null) { objReportsDto.setRfsDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } //--Fx Status Ed objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(VAR_BILLING_ADDRESS); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); //objReportsDto.setCanceldate(rs.getString("CANCEL_DATE"));//CANCEL_DATE tempDate = rs.getDate("CANCEL_DATE"); if (tempDate != null) { objReportsDto.setCanceldate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCancelReason(rs.getString("CANCEL_RESION")); objReportsDto.setOpms_Account_Id(rs.getString("Opms_Account_Id")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setFromLocation(rs.getString("TO_ADDRESS")); objReportsDto.setColl_Manager(rs.getString("COLL_MANAGER")); objReportsDto.setColl_Manager_Mail(rs.getString("COLL_MANAGER_MAIL")); objReportsDto.setColl_Manager_Phone(rs.getString("COLL_MANAGER_PHONE")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); //--CRM ORDER ID objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); //--Charge Hdr Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); // --party Id // --Cust Account id // --M6 Product Id // --M6 Order Id // --Ib Order Line Id // --Ib Service List Id // --Ib Pk Charges Id // --Fx Sno // --Fx Sno Ed // --Cust Tot Po Amt // --M6 Order No // --Business Serial No // --Bus Serial // --Advance 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.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setCancelServiceReason(rs.getString("CANCEL_SERVICE_REASON")); //objReportsDto.setCancelBy(rs.getString("CANCELBY")); //objReportsDto.setCanceldate(rs.getString("CANCELDATE")); 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.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO")); dto.setEndFxStatus(rs.getString("FX_END_STATUS")); objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); 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.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFx_Ed_Chg_Status(rs.getString("CSTATE_FX_CHARGE_END_STATUS"));//Fx_Ed_Chg_Status objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { Date date = df.parse(objReportsDto.getChallendate()); objReportsDto.setChallendate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); 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.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); 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.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.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); 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.setBillPeriod(tBillPeriod); } 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.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); 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.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objReportsDto.getEndDate()); objReportsDto.setEndDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnualRate(rs.getString("ANNUAL_RATE")); } 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<RestPendingLineReportDTO> viewRestPendingLineReport(RestPendingLineReportDTO objDto) { // Nagarjuna String methodName = "viewRestPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*w ww .j a v a2 s . co m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<RestPendingLineReportDTO> listSearchDetails = new ArrayList<RestPendingLineReportDTO>(); RestPendingLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetRestPendingLineReports); 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("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, 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(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getSubChangeTypeName() != null && !"".equals(objDto.getSubChangeTypeName())) { proc.setString(4, objDto.getSubChangeTypeName().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); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(6, pagingSorting.getSortByColumn());// columnName proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(8, pagingSorting.getStartRecordId());// start index proc.setInt(9, pagingSorting.getEndRecordId());// end index proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new RestPendingLineReportDTO(); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountManager(rs.getString("ACTMNAME")); objReportsDto.setProjectManager(rs.getString("PMNAME")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objReportsDto.setChargeEndDate(rs.getString("CHARGE_START_DATE")); if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } 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.setAmapprovaldate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objReportsDto.setAmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objReportsDto.setPmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERCREATEDATE")); if (rs.getString("ORDERCREATEDATE") != null && !"".equals(rs.getString("ORDERCREATEDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } 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.setPoReceiveDate(rs.getString("CUSTPORECDATE")); if (rs.getString("CUSTPORECDATE") != null && !"".equals(rs.getString("CUSTPORECDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCanceldate(rs.getString("CANCELDATE")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGE_TYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setBillingMode(rs.getString("BILLMODE_NAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setCancelflag(rs.getString("CANCELBY")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setAddress1(rs.getString("ADDRESS")); objReportsDto.setFx_status(rs.getString("FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("Fx_St_Chg_Status")); objReportsDto.setFx_ed_status(rs.getString("Fx_Ed_Chg_Status")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objReportsDto.getRfs_date()); objReportsDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setLineno(rs.getInt("LINEITEMNO")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setLocation_from(rs.getString("FROM_LOCATION")); objReportsDto.setLocation_to(rs.getString("TO_LOCATION")); objReportsDto.setServiceStage(rs.getString("SERVICESTAGE")); objReportsDto.setBisource(rs.getString("BISOURCE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setTokenno(rs.getString("TOKENNO")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setSaleNature(rs.getString("SALENATURENAME")); objReportsDto.setDemoType(rs.getString("Demo_Type")); objReportsDto.setSaleType(rs.getString("SALETYPENAME")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACT_ID")); 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.setStageName(rs.getString("ORDERSTAGE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO")); objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setPoAmountSum(rs.getLong("ORDERAMOUNT")); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID")); objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID")); objReportsDto.setLb_pk_charge_id(rs.getString("LB_PK_CHARGE_ID")); objReportsDto.setChargeinfoID(rs.getString("PK_CHARGE_ID")); objReportsDto.setAnnual_rate(rs.getInt("ANNUAL_RATE")); objReportsDto.setBandWidth((rs.getString("BANDWIDTH"))); //[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; }