List of usage examples for java.sql CallableStatement setNull
void setNull(String parameterName, int sqlType) throws SQLException;
NULL. From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ReportsDto> viewPendingLineReport(ReportsDto objDto) { // Nagarjuna String methodName = "viewPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /* ww w .j av a2s .c o m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>(); ReportsDto objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPendingLineReports); 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 ReportsDto(); 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_Report5(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_Report5(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_Report5(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.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.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACT_ID")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); //objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); //here getting only digit if (rs.getString("CREDIT_PERIODNAME") != null) objReportsDto.setCreditPeriod( Integer.parseInt(rs.getString("CREDIT_PERIODNAME").replaceAll("[\\D]", ""))); else objReportsDto.setCreditPeriod(0); 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_String(BigDecimal.valueOf((rs.getDouble("INV_AMT"))).toPlainString()); objReportsDto .setLineamtString(BigDecimal.valueOf((rs.getDouble("LINEITEMAMOUNT"))).toPlainString()); objReportsDto .setPoAmountSumString(BigDecimal.valueOf((rs.getDouble("ORDERAMOUNT"))).toPlainString()); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrentString( BigDecimal.valueOf((rs.getDouble("TOTALPOAMOUNT"))).toPlainString()); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID")); objReportsDto.setCust_total_poamtString( BigDecimal.valueOf((rs.getDouble("CUST_TOT_PO_AMT"))).toPlainString()); 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_rateString(BigDecimal.valueOf((rs.getDouble("ANNUAL_RATE"))).toPlainString()); 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.setBilling_Trigger_Flag(rs.getString("BT_FLAG")); objReportsDto.setLocDate(rs.getString("LOCDATE")); objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) { objDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } } /*Vijay add few more columns*/ objReportsDto.setAsiteAdd1(rs.getString("ASITE_ADD1")); objReportsDto.setAsiteAdd2(rs.getString("ASITE_ADD2")); objReportsDto.setAsiteAdd3(rs.getString("ASITE_ADD3")); objReportsDto.setBsiteName(rs.getString("BSITE_NAME")); objReportsDto.setBsiteLineAdd1(rs.getString("BSITE_ADD1")); objReportsDto.setBsiteLineAdd2(rs.getString("BSITE_ADD2")); //vijay end objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); //[505054] Start objReportsDto.setServiceSegment(rs.getString("SERVICESEGMENT")); //[505054] End //[004] Start objReportsDto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY")); objReportsDto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY")); objReportsDto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE")); objReportsDto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE")); objReportsDto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME")); objReportsDto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER")); objReportsDto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID")); //[004] End //[008] Start objReportsDto.setOrderCreationDate(rs.getString("CREATED_DATE")); if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) { objReportsDto.setOrderCreationDate( (utility.showDate_Report5(new Date(rs.getTimestamp("CREATED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objReportsDto.setPublishedDate( (utility.showDate_Report5(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } //[008] End //[131] start objReportsDto.setChannelPartner(rs.getString("PARTNER_NAME")); objReportsDto.setPartnerCode(rs.getString("PARTNER_CODE")); objReportsDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<DisconnectChangeOrdeReportDTO> viewDisconnectionChangeOrderReport( DisconnectChangeOrdeReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionChangeOrderReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/* ww w . ja v a 2 s .c o m*/ 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"); SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter2 = new SimpleDateFormat("MM-dd-yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionChangeOrderReport); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { Date dateStr = formatter.parse(objDto.getFromDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { Date dateStr = formatter.parse(objDto.getToDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } 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 if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(8, objDto.getServiceName().trim().toUpperCase()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) { proc.setInt(9, Integer.parseInt(objDto.getOrdersubtype().trim())); } else { proc.setNull(9, java.sql.Types.BIGINT); } proc.setNull(10, java.sql.Types.VARCHAR); if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(11, objDto.getVerticalDetails().trim().toUpperCase()); } else { proc.setNull(11, java.sql.Types.VARCHAR); } 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); } if (objDto.getSrrequest() != null && !"".equals(objDto.getSrrequest())) { proc.setString(13, objDto.getSrrequest()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } /*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())) { Date dateStr = formatter.parse(objDto.getFromDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { Date dateStr = formatter.parse(objDto.getToDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(3, formattedDate); } 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)); */ // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new DisconnectChangeOrdeReportDTO(); objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); 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.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBilling_Trigger_Status(rs.getString("BILLING_TRIGGER_STATUS")); 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("BILLING_LEVELNAME")); objReportsDto.setBillingLevelId(rs.getInt("BILLING_LEVEL_NO")); 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.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (!(rs.getString("PODATE") == null || rs.getString("PODATE") == "")) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setParty(rs.getString("PARTYNAME")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); 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")); if (!(rs.getString("BILLINGTRIGGERDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) { objDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } } objReportsDto.setChild_act_no(rs.getString("Child_Account_Number")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (!(rs.getString("ORDERDATE") == null || rs.getString("ORDERDATE") == "")) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); if (!(rs.getString("COPC_APPROVED_DATE") == null || rs.getString("COPC_APPROVED_DATE") == "")) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null || rs.getString("BILLING_TRIGGER_CREATEDATE") == "")) { //Date date=df.parse(objReportsDto.getBillingtrigger_createdate()); //objReportsDto.setBillingtrigger_createdate((utility.showDate_Report(date)).toUpperCase()); objReportsDto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeinfoID(rs.getString("CHARGETYPEID")); if (objReportsDto.getChargeinfoID() == null) { objReportsDto.setChargeinfoID(" "); } objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setActmgrname(rs.getString("ACCOUNTMANAGER")); objReportsDto.setPrjmngname(rs.getString("PROJECTMANAGER")); /*objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if(!(rs.getString("ORDERDATE")==null || rs.getString("ORDERDATE")=="")) { objReportsDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))).toUpperCase()); }*/ objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (!(rs.getString("SERVICE_RFS_DATE") == null || rs.getString("SERVICE_RFS_DATE") == "")) { //objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); //Date date=df.parse(objReportsDto.getRfs_date().trim()); //objReportsDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); objReportsDto.setRfs_date( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_RFS_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCust_po_rec_date(rs.getString("PORECEIVEDATE")); if (!(rs.getString("PORECEIVEDATE") == null || rs.getString("PORECEIVEDATE") == "")) { Date date = df.parse(objReportsDto.getCust_po_rec_date()); objReportsDto.setCust_po_rec_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); 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.setSrno(rs.getString("SR_NO")); objReportsDto.setRequest_rec_date(rs.getString("SRDATE")); //objReportsDto.setLineno(rs.getInt("M6_CKTID")); //objReportsDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))).toUpperCase()); String tmpDate = objReportsDto.getOrderDate().substring( (objReportsDto.getOrderDate().indexOf("-") + 1), objReportsDto.getOrderDate().length()); objReportsDto.setOrdermonth(tmpDate); 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.setDispatchAddress1(rs.getString("DISP_ADDRESS1")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setProductName(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (!(rs.getString("CUSTPODATE") == null || rs.getString("CUSTPODATE") == "")) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setProdAlisName(rs.getString("PRODUCTNAME")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setSub_linename(rs.getString("SUBPRODUCTNAME")); //objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setOrderNo(rs.getString("ORDERNO")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); //objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); //objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto .setFrequencyAmt(BigDecimal.valueOf((rs.getDouble("CHARGEFREQUENCYAMT"))).toPlainString()); if (objReportsDto.getFrequencyAmt() == null) { objReportsDto.setFrequencyAmt(" "); } //objReportsDto.setAmt(rs.getLong("CHARGEAMOUNT"));//particular charge amount objReportsDto.setLineItemAmount(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objReportsDto.getLineItemAmount() == null) { objReportsDto.setLineItemAmount(" "); } //objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setChargeAmount_String(objReportsDto.getLineItemAmount()); //objReportsDto.setAdvance(rs.getString("ADVANCE")); //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.setContractMonths(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setPeriodsInMonths(rs.getString("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(BigDecimal.valueOf((rs.getDouble("POAMOUNT"))).toPlainString()); if (objReportsDto.getTotalPoAmt() == null) { objReportsDto.setTotalPoAmt(" "); } 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.setPk_charge_id(rs.getString("CHARGEINFOID")); //objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); objReportsDto.setBilling_location_from(rs.getString("FROM_ADDRESS")); objReportsDto.setBilling_location_to(rs.getString("TO_ADDRESS")); /*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) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<M6OrderCancelReportDTO> viewM6OrderCancelReport(M6OrderCancelReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewM6OrderCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end // w ww . jav a 2 s . co m ArrayList<M6OrderCancelReportDTO> objUserList = new ArrayList<M6OrderCancelReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlM6OrderCancelReport); /*if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(2, objDto.getToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getFromDate()); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getToDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getToDate()); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new M6OrderCancelReportDTO(); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setServiceType(rs.getString("SERVICETYPE")); objDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { objDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objDto.setEffDate(rs.getString("EFFSTARTDATE")); if (rs.getString("EFFSTARTDATE") != null && !"".equals(rs.getString("EFFSTARTDATE"))) { Date date = df.parse(objDto.getEffDate()); objDto.setEffDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { Date date = df.parse(objDto.getRfs_date()); objDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setServiceStage(rs.getString("SERVICESTAGE")); objDto.setCrmAccountId(rs.getInt("CRMACCOUNTNO")); objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objDto.setCancelServiceReason(rs.getString("CANCEL_SERVICE_REASON")); objDto.setOrdertype_demo(rs.getString("order_type_DEMO")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setCanceldate(rs.getString("CANCEL_DATE")); objDto.setOldLsi(rs.getString("OLD_LSI_CRM")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCanceldate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<TelemediaOrderReportDTO> getTelemediaOrderList(TelemediaOrderReportDTO objDto) { // Nagarjuna String methodName = "getTelemediaOrderList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end //from w w w . ja va 2s .c o m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<TelemediaOrderReportDTO> listSearchDetails = new ArrayList<TelemediaOrderReportDTO>(); TelemediaOrderReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetTelemediaOrderReport); /*if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { proc.setString(1, objDto.getFromCopcApprovedDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate()!= null && !"".equals(objDto.getToCopcApprovedDate())) { proc.setString(2, objDto.getToCopcApprovedDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("yyyy-MM-dd"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new TelemediaOrderReportDTO(); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setOrderNo(rs.getString("ORDERNO")); objReportsDto.setCrmAccountNoString(rs.getString("PARENT_ACCOUNT_NUMBER")); objReportsDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER")); objReportsDto.setCopcApproveDate(Utility.showDate_Report(rs.getString("COPC_APPROVED_DATE"))); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ParallelUpgradeReportDto> viewParallelUpgradeReport(ParallelUpgradeReportDto objDto, String inExcel, HttpServletResponse response) { String methodName = "viewParallelUpgradeReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w . j a v a 2s . co m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<ParallelUpgradeReportDto> listSearchDetails = new ArrayList<ParallelUpgradeReportDto>(); ParallelUpgradeReportDto objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetParallelUpgradeReport); proc.setInt(1, objDto.getChangeTypeId()); /*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.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(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("yyyy-MM-dd"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromServiceNo() != 0 && !"".equals(objDto.getFromServiceNo())) { proc.setInt(4, objDto.getFromServiceNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToServiceNo() != 0 && !"".equals(objDto.getToServiceNo())) { proc.setInt(5, objDto.getToServiceNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getCustomerSegment() != null && !"".equals(objDto.getCustomerSegment()) && !objDto.getCustomerSegment().equals("0")) { proc.setString(6, objDto.getCustomerSegment()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getExclude_comp_orders() != null && !"".equals(objDto.getExclude_comp_orders())) { proc.setString(7, objDto.getExclude_comp_orders().trim()); } else { proc.setNull(7, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and End 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)); // index rs = proc.executeQuery(); StringBuffer row = new StringBuffer(); if ("true".equals(inExcel)) { setResponseHeaderForParallelUpgrade(response); startTableHeader(row); columnHeadeForParallelUpgrade(response, row); } while (rs.next()) { objReportsDto = new ParallelUpgradeReportDto(); objReportsDto.setCustomerSegment(Utility.fnCheckNull(rs.getString("CUST_SEGMENT_CODE"))); objReportsDto.setOrderType(Utility.fnCheckNull(rs.getString("ORDERTYPE"))); objReportsDto.setChangeType(Utility.fnCheckNull(rs.getString("CHANGETYPENAME"))); objReportsDto.setCustomername(Utility.fnCheckNull(rs.getString("PARTYNAME"))); objReportsDto.setCrm_order_id(rs.getInt("ORDERNO")); objReportsDto.setOrderDate(Utility.fnCheckNull(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.setCopcApprovalDate(Utility.fnCheckNull(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.setLogical_si_no(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setServiceNo(rs.getInt("SERVICEID")); objReportsDto.setChangeReason(Utility.fnCheckNull(rs.getString("CHANGE_REASON"))); objReportsDto.setLsi_disconnection_SRNO(Utility.fnCheckNull(rs.getString("SRNO"))); objReportsDto.setService_stage(Utility.fnCheckNull(rs.getString("service_stage"))); objReportsDto.setServiceBTActionDate(Utility.fnCheckNull(rs.getString("SERVICE_BT_ACTION_DATE"))); if (rs.getString("SERVICE_BT_ACTION_DATE") != null && !"".equals(rs.getString("SERVICE_BT_ACTION_DATE"))) { objReportsDto.setServiceBTActionDate( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_BT_ACTION_DATE").getTime()))) .toUpperCase()); } objReportsDto.setAttribute_remarks(Utility.fnCheckNull(rs.getString("SERVICE_REMARKS"))); objReportsDto.setBin(Utility.fnCheckNull(rs.getString("BIN"))); objReportsDto.setOld_lsi(rs.getInt("OLD_LSI")); objReportsDto.setOld_lsi_lateststage(Utility.fnCheckNull(rs.getString("OLD_LSI_LATEST_STAGE"))); objReportsDto.setOld_lsi_BT_ActionDate(Utility.fnCheckNull(rs.getString("Old_Lsi_BT_Action_Date"))); if (rs.getString("Old_Lsi_BT_Action_Date") != null && !"".equals(rs.getString("Old_Lsi_BT_Action_Date"))) { objReportsDto.setOld_lsi_BT_ActionDate( (utility.showDate_Report(new Date(rs.getTimestamp("Old_Lsi_BT_Action_Date").getTime()))) .toUpperCase()); } objReportsDto.setOld_lsi_disconnection_SRno( Utility.fnCheckNull(rs.getString("OLD_LSI_Disconnetion_SRNO"))); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } if ("true".equals(inExcel)) { savedatainexcel(objReportsDto, response); } else { listSearchDetails.add(objReportsDto); } } if ("true".equals(inExcel)) { endTable(response); closePrintWriterForParallelUpgrade(response); } 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 } } if ("true".equals(inExcel)) { return null; } else { 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 ww . j a v a 2 s. co 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.java
public ArrayList<BulkSIUploadReportDto> viewBulkSIUploadReport(BulkSIUploadReportDto objDto) { // Nagarjuna String methodName = "viewBulkSIUploadReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end// w w w . j av a2 s . c o m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<BulkSIUploadReportDto> listSearchDetails = new ArrayList<BulkSIUploadReportDto>(); BulkSIUploadReportDto objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetBulkUploadReports); /*if (objDto.getDate_of_inst_from() != null && !"".equals(objDto.getDate_of_inst_from())) { proc.setString(1, objDto.getDate_of_inst_from().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getDate_of_inst_to() != null && !"".equals(objDto.getDate_of_inst_to())) { proc.setString(2, objDto.getDate_of_inst_to().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ if (objDto.getDate_of_installation_from() != null && !"".equals(objDto.getDate_of_installation_from())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getDate_of_installation_from()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getDate_of_installation_to() != null && !"".equals(objDto.getDate_of_installation_to())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getDate_of_installation_to()); 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.getParty_no() != 0 && !"".equals(objDto.getParty_no())) { proc.setInt(4, objDto.getParty_no()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getOrderNo() != null && !"".equals(objDto.getOrderNo())) { proc.setString(5, objDto.getOrderNo().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getLogicalSINo() != null && !"".equals(objDto.getLogicalSINo())) { proc.setString(6, objDto.getLogicalSINo().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getLinename() != null && !"".equals(objDto.getLinename())) { proc.setString(7, objDto.getLinename().trim()); } else { proc.setNull(7, java.sql.Types.VARCHAR); } 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)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new BulkSIUploadReportDto(); objReportsDto.setAccountName(rs.getString("ACCOUNTNAME")); objReportsDto.setOrderNumber(rs.getInt("CRM_ORDER_MOCN_NO")); objReportsDto.setCrm_service_opms_id(rs.getString("CRM_SERVICE_OPMS_ID")); objReportsDto.setServiceName(rs.getString("SERVICETYPE")); // get city,state,country if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objReportsDto.setFrom_city(ss[8]); } else { objReportsDto.setFrom_city(" "); } //objDto.setFrom_city(rs.getString("FROM_CITY")); } else { objReportsDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objReportsDto.setTo_city(ss[8]); } else { objReportsDto.setTo_city(" "); } //objDto.setTo_city(rs.getString("TO_CITY")); } else { objReportsDto.setTo_city(""); } if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objReportsDto.setFrom_state(ss[9]); } else { objReportsDto.setFrom_state(" "); } //objDto.setFrom_city(rs.getString("FROM_CITY")); } else { objReportsDto.setFrom_state(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objReportsDto.setTo_state(ss[9]); } else { objReportsDto.setTo_state(" "); } //objDto.setTo_city(rs.getString("TO_CITY")); } else { objReportsDto.setTo_state(""); } if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objReportsDto.setFrom_country(ss[10]); } else { objReportsDto.setFrom_country(" "); } //objDto.setFrom_city(rs.getString("FROM_CITY")); } else { objReportsDto.setFrom_country(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objReportsDto.setTo_country(ss[10]); } else { objReportsDto.setTo_country(" "); } //objDto.setTo_city(rs.getString("TO_CITY")); } else { objReportsDto.setTo_country(""); } // get city,state,country end // get addresses start if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setInstallation_addressaa1(ss[4]); } else { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setInstallation_addressaa1(ss[5]); } } else { objReportsDto.setInstallation_addressaa1(""); } if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setInstallation_addressab1(ss[4]); } else { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setInstallation_addressab1(ss[5]); } } else { objReportsDto.setInstallation_addressab1(""); } if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setInstallation_addressaa2(ss[5]); } else { objReportsDto.setInstallation_addressaa2(""); } } else { objReportsDto.setInstallation_addressaa2(""); } if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setInstallation_addressaa3(ss[6]); } else { objReportsDto.setInstallation_addressaa3(""); } } else { objReportsDto.setInstallation_addressaa3(""); } if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setInstallation_addressab2(ss[5]); } else { objReportsDto.setInstallation_addressab2(""); } } else { objReportsDto.setInstallation_addressab2(""); } if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setInstallation_addressab3(ss[6]); } else { objReportsDto.setInstallation_addressab3(""); } } else { objReportsDto.setInstallation_addressab3(""); } objReportsDto.setDate_of_inst(rs.getString("DATE_OF_INST")); //left objReportsDto.setDate_of_act(rs.getString("DATE_OF_ACT")); if (rs.getString("DATE_OF_ACT") != null && !"".equals(rs.getString("DATE_OF_ACT"))) { Date date = df.parse(objReportsDto.getDate_of_act()); objReportsDto.setDate_of_act((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setLob(rs.getString("LOB")); objReportsDto.setCircle(rs.getString("CIRCLE")); objReportsDto.setZone(rs.getString("ZONE")); objReportsDto.setLocation_from(rs.getString("SUPPORT_LOCATION_A")); objReportsDto.setLocation_to(rs.getString("SUPPORT_LOCATION_B")); objReportsDto.setCommited_sla(rs.getString("COMMITED_SLA")); objReportsDto.setHub_location(rs.getString("UB_LOCATION")); objReportsDto.setPlatform(rs.getString("PLATFORM")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setFxSiId(rs.getString("SIID")); objReportsDto.setIpls(rs.getString("IPLC")); objReportsDto.setManaged_yes_no(rs.getString("MANAGED_YES_NO")); objReportsDto.setActmngname(rs.getString("ACTMNAME")); objReportsDto.setPrjmngname(rs.getString("PMNAME")); objReportsDto.setService_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setLineItemDescription(rs.getString("LINENAME")); 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 /*from w w w. j a va 2 s. com*/ 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:net.sourceforge.msscodefactory.cfcore.v2_0.CFGenKbOracle.CFGenKbOracleGelExpansionTable.java
public void createGelExpansion(CFGenKbAuthorization Authorization, CFGenKbGelExpansionBuff Buff) { final String S_ProcName = "createGelExpansion"; ResultSet resultSet = null;//from w ww.j a va2 s .c om CallableStatement stmtCreateByPKey = null; try { String ClassCode = Buff.getClassCode(); long TenantId = Buff.getRequiredTenantId(); long CartridgeId = Buff.getRequiredCartridgeId(); Integer CallerId = Buff.getOptionalCallerId(); Integer PrevId = Buff.getOptionalPrevId(); Integer NextId = Buff.getOptionalNextId(); String SourceText = Buff.getRequiredSourceText(); String MacroName = Buff.getRequiredMacroName(); Connection cnx = schema.getCnx(); stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName() + ".crt_gelexpansion( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;"); int argIdx = 1; stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtCreateByPKey.setString(argIdx++, ClassCode); stmtCreateByPKey.setLong(argIdx++, TenantId); stmtCreateByPKey.setLong(argIdx++, CartridgeId); if (CallerId != null) { stmtCreateByPKey.setInt(argIdx++, CallerId.intValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.INTEGER); } if (PrevId != null) { stmtCreateByPKey.setInt(argIdx++, PrevId.intValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.INTEGER); } if (NextId != null) { stmtCreateByPKey.setInt(argIdx++, NextId.intValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.INTEGER); } stmtCreateByPKey.setString(argIdx++, SourceText); stmtCreateByPKey.setString(argIdx++, MacroName); stmtCreateByPKey.execute(); resultSet = (ResultSet) stmtCreateByPKey.getObject(1); if (resultSet == null) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_gelexpansion() did not return a result set"); } try { if (resultSet.next()) { CFGenKbGelExpansionBuff createdBuff = unpackGelExpansionResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredTenantId(createdBuff.getRequiredTenantId()); Buff.setRequiredCartridgeId(createdBuff.getRequiredCartridgeId()); Buff.setRequiredGelInstId(createdBuff.getRequiredGelInstId()); Buff.setOptionalCallerId(createdBuff.getOptionalCallerId()); Buff.setOptionalPrevId(createdBuff.getOptionalPrevId()); Buff.setOptionalNextId(createdBuff.getOptionalNextId()); Buff.setRequiredSourceText(createdBuff.getRequiredSourceText()); Buff.setRequiredRevision(createdBuff.getRequiredRevision()); Buff.setRequiredMacroName(createdBuff.getRequiredMacroName()); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_gelexpansion() did not return a valid result set"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtCreateByPKey != null) { try { stmtCreateByPKey.close(); } catch (SQLException e) { } stmtCreateByPKey = null; } } }
From source file:net.sourceforge.msscodefactory.cfcore.v2_0.CFGenKbOracle.CFGenKbOracleGelExpansionTable.java
public CFGenKbGelExpansionBuff[] readBuffByCallerIdx(CFGenKbAuthorization Authorization, long TenantId, long CartridgeId, Integer CallerId) { final String S_ProcName = "readBuffByCallerIdx"; ResultSet resultSet = null;// w ww .j av a2s . co m Connection cnx = schema.getCnx(); CallableStatement stmtReadBuffByCallerIdx = null; List<CFGenKbGelExpansionBuff> buffList = new LinkedList<CFGenKbGelExpansionBuff>(); try { stmtReadBuffByCallerIdx = cnx.prepareCall( "begin " + schema.getLowerSchemaDbName() + ".rd_gelexpansionbycalleridx( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;"); int argIdx = 1; stmtReadBuffByCallerIdx.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtReadBuffByCallerIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByCallerIdx.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtReadBuffByCallerIdx.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtReadBuffByCallerIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByCallerIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtReadBuffByCallerIdx.setLong(argIdx++, TenantId); stmtReadBuffByCallerIdx.setLong(argIdx++, CartridgeId); if (CallerId != null) { stmtReadBuffByCallerIdx.setInt(argIdx++, CallerId.intValue()); } else { stmtReadBuffByCallerIdx.setNull(argIdx++, java.sql.Types.INTEGER); } stmtReadBuffByCallerIdx.execute(); resultSet = (ResultSet) stmtReadBuffByCallerIdx.getObject(1); if (resultSet != null) { try { while (resultSet.next()) { CFGenKbGelExpansionBuff buff = unpackGelExpansionResultSetToBuff(resultSet); buffList.add(buff); } try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } catch (SQLException e) { } } int idx = 0; CFGenKbGelExpansionBuff[] retBuff = new CFGenKbGelExpansionBuff[buffList.size()]; Iterator<CFGenKbGelExpansionBuff> iter = buffList.iterator(); while (iter.hasNext()) { retBuff[idx++] = iter.next(); } return (retBuff); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtReadBuffByCallerIdx != null) { try { stmtReadBuffByCallerIdx.close(); } catch (SQLException e) { } stmtReadBuffByCallerIdx = null; } } }