List of usage examples for java.sql CallableStatement executeQuery
ResultSet executeQuery() throws SQLException;
PreparedStatement object and returns the ResultSet object generated by the query. From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO> viewNonMigAppUnappNewOrderDetails( NonMigratedAPP_UNAPPNewOrderDetailsDTO objDto) { // Nagarjuna String methodName = "viewNonMigAppUnappNewOrderDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*w ww. ja va 2 s . com*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO> listSearchDetails = new ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO>(); NonMigratedAPP_UNAPPNewOrderDetailsDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); 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)); if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) { proc.setString(14, objDto.getOrderyear().trim()); } else { proc.setNull(14, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); /*while(rs.next()) { objReportsDto = new ReportsDto(); 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")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("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.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.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date=df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date=df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.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.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); 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.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")); 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 //Business Serial No //Opms Account Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber //Order Month objReportsDto.setOrderStage(rs.getString("STAGE")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); }*/ while (rs.next()) { objReportsDto = new NonMigratedAPP_UNAPPNewOrderDetailsDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setM6OrderNo2(rs.getString("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICESTAGE")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETTYPE"));//LineName objReportsDto.setServiceOrderType(rs.getString("SERVICETYPE")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); 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.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPENAME"));//Type Of Sale objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PODETAILNUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (!(rs.getString("PODATE") == null || rs.getString("PODATE") == "")) { //Date date=df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(rs.getString("PODATE"))).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("POAMOUNT")); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (!(rs.getString("PORECEIVEDATE") == null || rs.getString("PORECEIVEDATE") == "")) { //Date date=df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate( (utility.showDate_Report(rs.getString("PORECEIVEDATE"))).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (!(rs.getString("CUSTPODATE") == null || rs.getString("CUSTPODATE") == "")) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto .setCustPoDate((utility.showDate_Report(rs.getString("CUSTPODATE"))).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("LOCDATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setLocDate(formattedDate); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("BILLINGTRIGGERDATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setBillingTriggerDate(formattedDate); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) { //Date date=df2.parse(objReportsDto.getBillingtrigger_createdate()); objReportsDto.setBillingtrigger_createdate( (utility.showDate_Report(rs.getString("BILLING_TRIGGER_CREATEDATE"))).toUpperCase()); } objReportsDto.setPmApproveDate(rs.getString("LOC_DATE")); if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) { String s1 = rs.getString("LOC_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_STATUS")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setKmsDistance(rs.getString("DISTANCE")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setTokenNO(rs.getString("START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setFx_St_Chg_Status(rs.getString("FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFxStatus(rs.getString("START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); //objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); //[707070] Start objReportsDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objReportsDto.setOpms_lineItemNumber(rs.getString("OPMS_LINEITEMNUMBER")); // [707070] End if (rs.getString("INV_AMT") != null && !"".equals(rs.getString("INV_AMT"))) { objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); //Double d2=Double.parseDouble(rs.getString("INV_AMT")); //objReportsDto.setChargeAmount_Double((Math.round(d2 * 100.0) / 100.0)); } else { objReportsDto.setChargeAmount_Double(0.0); } objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("START_DATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setStartDate(formattedDate); } else { objReportsDto.setStartDate(""); } objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setOrderLineNumber(rs.getInt("LINENUMBER"));//Lineitemnumber objReportsDto.setOrdermonth(rs.getString("ORDERMONTH")); /*objReportsDto.setBlSource(rs.getString("BL_SOURCE")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));*/ objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.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("PRIMARYLOCATION").split("~~"); objReportsDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objReportsDto.setPrimaryLocation(""); } if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setSecondaryLocation(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("SECONDARYLOCATION").split("~~"); objReportsDto.setSecondaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objReportsDto.setSecondaryLocation(""); } objReportsDto.setOrderStage(rs.getString("STAGE")); 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.java
public ArrayList<BillingWorkQueueReportDTO> viewBillingWorkQueueList(BillingWorkQueueReportDTO objDto2) throws Exception { // Nagarjuna String methodName = "viewBillingWorkQueueList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w . j a v a 2 s .c o m ArrayList<BillingWorkQueueReportDTO> objUserList = new ArrayList<BillingWorkQueueReportDTO>(); 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(sqlBillingWorkQueue); proc.setInt(1, java.sql.Types.BIGINT); if (objDto2.getOrderType() != null && !"".equals(objDto2.getOrderType())) { proc.setString(2, objDto2.getOrderType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto2.getFromDate() != null && !"".equals(objDto2.getFromDate())) { proc.setString(3, objDto2.getFromDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto2.getToDate() != null && !"".equals(objDto2.getToDate())) { proc.setString(4, objDto2.getToDate().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto2.getFromOrderNo() != 0 && !"".equals(objDto2.getFromOrderNo())) { proc.setInt(5, objDto2.getFromOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto2.getToOrderNo() != 0 && !"".equals(objDto2.getToOrderNo())) { proc.setInt(6, objDto2.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } /* * add party no and party name */ if (objDto2.getParty_no() != 0 && !"".equals(objDto2.getParty_no())) { proc.setInt(7, objDto2.getParty_no()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto2.getPartyName() != null && !"".equals(objDto2.getPartyName())) { proc.setString(8, objDto2.getPartyName().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } if (objDto2.getOrderStage() != null && !"".equals(objDto2.getOrderStage())) { proc.setString(9, objDto2.getOrderStage().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } if (objDto2.getHardwareType() != null && !"".equals(objDto2.getHardwareType())) { proc.setString(10, objDto2.getHardwareType().trim()); } else { proc.setNull(10, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto2.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(11, pagingSorting.getSortByColumn());// columnName proc.setString(12, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(13, pagingSorting.getStartRecordId());// start index proc.setInt(14, pagingSorting.getEndRecordId());// end index proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end //proc.setInt(15, (pagingSorting.isPagingToBeDone() ? 0 : 0));// end // index System.out.println("sqlBillingWorkQueue :" + sqlBillingWorkQueue); rs = proc.executeQuery(); int countFlag = 0; BillingWorkQueueReportDTO objdto; while (rs.next() != false) { countFlag++; // System.out.println("in while roop of rs"); objdto = new BillingWorkQueueReportDTO(); objdto.setLogicalSINo(rs.getString("LOGICAL_CIRCUIT_ID")); objdto.setCustSINo(rs.getString("CUST_LOGICAL_SI_ID")); objdto.setServiceName(rs.getString("SERVICE_NAME")); objdto.setLinename(rs.getString("LINE_NAME")); objdto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objdto.setChargeTypeID(rs.getInt("CHARGE_TYPE_ID")); objdto.setChargeName(rs.getString("CHARGE_NAME")); objdto.setChargeFrequency(rs.getString("FREQUENCY")); objdto.setBillPeriod(rs.getString("BILL_PERIOD")); /* * newly added fields in code */ objdto.setEndDateLogic(rs.getString("ENDDATELOGIC")); if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) { objdto.setStartDate(rs.getString("CHARGE_START_DATE")); //Date date=df.parse(objdto.getStartDate()); //objdto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) { objdto.setChargeEndDate(rs.getString("CHARGE_END_DATE")); } objdto.setAdvance(rs.getString("ADVANCE")); objdto.setRate_code(rs.getString("TRAI_RATE")); objdto.setDiscount(rs.getString("DISCOUNT")); objdto.setInstallRate(rs.getString("INSTALRATE")); objdto.setInterestRate(rs.getInt("INTREST_RATE")); objdto.setPrincipalAmount(rs.getInt("PRINCIPAL_AMOUNT")); objdto.setNoticePeriod(rs.getLong("NOTICEPERIOD")); objdto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objdto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); if (rs.getString("WARRANTY_START_DATE") != null && !"".equals(rs.getString("WARRANTY_START_DATE"))) { objdto.setWarrantyStartDate(rs.getString("WARRANTY_START_DATE")); objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyStartDate())).toUpperCase()); } if (rs.getString("WARRANTY_END_DATE") != null && !"".equals(rs.getString("WARRANTY_END_DATE"))) { objdto.setWarrantyEndDate(rs.getString("WARRANTY_END_DATE")); objdto.setPoDate((utility.showDate_Report(objdto.getWarrantyEndDate())).toUpperCase()); } objdto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); objdto.setContractStartDate(rs.getString("CONTRACT_START_DATE")); objdto.setContractEndDate(rs.getString("CONTRACT_END_DATE")); objdto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objdto.setDnd_Dispatch_And_Delivered(rs.getString("DND_DISPATCH_AND_DELIVERED")); objdto.setDnd_Dispatch_But_Not_Delivered(rs.getString("DND_DISPATCH_BUT_NOT_DELIVERED")); objdto.setBilling_address(rs.getString("BILLING_ADDRESS")); objdto.setServiceTypeDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); objdto.setCancelBy(rs.getString("CANCEL_BY")); objdto.setCanceldate(rs.getString("CANCEL_DATE")); objdto.setCancelReason(rs.getString("CANCEL_REASON")); objdto.setDemo(rs.getString("DEMO_TYPE")); /* * end of newly ended code */ // start_date = ? objdto.setAccountID(rs.getInt("ACCOUNT_NUMBER")); objdto.setCreditPeriodName(rs.getString("CREDIT_PERIOD")); //objdto.setCurrencyName(rs.getString("CURNAME")); // is it currency objdto.setCurrencyName(rs.getString("CURRENCY")); //objdto.setEntity(rs.getString("ENTITYNAME")); // is it legal entity objdto.setEntity(rs.getString("LEGAL_ENTITY")); objdto.setBillingMode(rs.getString("BILLINGMODE")); objdto.setBillingTypeName(rs.getString("BILL_TYPE")); objdto.setBillingformat(rs.getString("BILL_FORMAT")); objdto.setLicCompanyName(rs.getString("LICENSE_COMP")); //objdto.setTaxation(rs.getString("TAXATIONVALUE")); // is it TAXATION objdto.setTaxation(rs.getString("TAXATION")); objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); //objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); // is it BILLINGLEVEL objdto.setBillingLevelName(rs.getString("BILLINGLEVEL")); //objdto.setStore(rs.getString("STORENAME")); // is it STORE objdto.setStore(rs.getString("STORE")); objdto.setHardwaretypeName(rs.getString("HARDWARETYPE")); objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objdto.setSaleNature(rs.getString("NATURE_OF_SALE")); objdto.setSaleTypeName(rs.getString("TYPE_OF_SALE")); objdto.setPrimaryLocation(rs.getString("PRIMLOC")); objdto.setSeclocation(rs.getString("SECLOC")); objdto.setPoNumber(rs.getInt("PODETAILNUMBER")); objdto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { //Date date=df.parse(objdto.getPoDate()); objdto.setPoDate((utility.showDate_Report(objdto.getPoDate())).toUpperCase()); } objdto.setParty_num(rs.getString("PARTY_NO")); objdto.setChargeAnnotation(rs.getString("ANNOTATION")); objdto.setFx_sd_charge_status(rs.getString("FX_SD_CHG_STATUS")); objdto.setFx_charge_status(rs.getString("FX_STATUS")); objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHG_STATUS")); //objdto.setTokenID(rs.getInt("TOKEN_ID")); // is it TOKEN_NO objdto.setTokenno(rs.getString("TOKEN_NO")); objdto.setModifiedDate(rs.getString("LAST_UPDATE_DATE")); if (rs.getString("LAST_UPDATE_DATE") != null && !"".equals(rs.getString("LAST_UPDATE_DATE"))) { objdto.setModifiedDate( (utility.showDate_Report(new Date(rs.getTimestamp("LAST_UPDATE_DATE").getTime()))) .toUpperCase()); } objdto.setBillingTriggerFlag(rs.getString("BILLING_TRIG_FLAG")); if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) { objdto.setPm_pro_date(rs.getString("PM_PROV_DATE")); /* String s1=rs.getString("PM_PROVISIONING_DATE"); String s3=s1.substring(0,7).toUpperCase(); String s4=s1.substring(9,11); String s5=s3.concat(s4); objdto.setPm_pro_date(s5);*/ } if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) { objdto.setLocDate(rs.getString("LOC_DATE")); Date date = df.parse(objdto.getLocDate()); objdto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } if (rs.getString("BILLING_TRIG_DATE") != null && !"".equals(rs.getString("BILLING_TRIG_DATE"))) { objdto.setBilling_trigger_date(rs.getString("BILLING_TRIG_DATE")); Date date = df.parse(objdto.getBilling_trigger_date()); objdto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } objdto.setChallenno(rs.getString("CHALLEN_NO")); objdto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objdto.setChallendate(s5); } //FX_ACCOUNT_EXTERNAL_ID = ? //objdto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID")); //objdto.setChild_account_creation_status(rs.getString("CHILD_ACCOUNT_FX_STATUS")); objdto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER")); objdto.setChild_ac_fxstatus(rs.getString("CHILD_ACCOUNT_FX_STATUS")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objdto.setOrderDate(rs.getString("ORDERDATE")); //Date date=df.parse(objdto.getOrderDate()); objdto.setOrderDate((utility.showDate_Report(objdto.getOrderDate())).toUpperCase()); } if (rs.getString("APPROVED_DATE") != null && !"".equals(rs.getString("APPROVED_DATE"))) { objdto.setCopcapprovaldate(rs.getString("APPROVED_DATE")); objdto.setCopcapprovaldate( (utility.showDate_Report(objdto.getCopcapprovaldate())).toUpperCase()); } objdto.setOrderType(rs.getString("ORDERTYPE")); if (rs.getString("BILL_TRG_CREATE_DATE") != null && !"".equals(rs.getString("BILL_TRG_CREATE_DATE"))) { objdto.setBillingtrigger_createdate(rs.getString("BILL_TRG_CREATE_DATE")); objdto.setBillingtrigger_createdate( (utility.showDate_Report(new Date(rs.getTimestamp("BILL_TRG_CREATE_DATE").getTime()))) .toUpperCase()); } objdto.setRatio(rs.getString("RATIO")); objdto.setProductName(rs.getString("PRODUCT")); objdto.setSubProductName(rs.getString("SUBPRODUCT")); objdto.setHardwareType(rs.getString("HARDWARE_FLAG")); objdto.setServiceStage(rs.getString("SERVICE_STAGE")); objdto.setOrderStage(rs.getString("ORDER_STAGE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objdto.setRfsDate(rs.getString("RFS_DATE")); objdto.setRfsDate((utility.showDate_Report(objdto.getRfsDate())).toUpperCase()); } // PORECEIVEDATE = ? /*objdto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date=df.parse(objdto.getPoReceiveDate()); objdto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); }*/ objdto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objdto.setCustPoDate(rs.getString("CUSTPODATE")); //Date date=df.parse(objdto.getCustPoDate()); objdto.setCustPoDate((utility.showDate_Report(objdto.getCustPoDate())).toUpperCase()); } objdto.setCharge_status(rs.getString("CHARGES_STATUS")); objdto.setLOC_No(rs.getString("LOC_NUMBER")); //objdto.setAddress1(rs.getString("ADDRESS")); // is it BILLING_ADDRESS objdto.setAddress1(rs.getString("BILLING_ADDRESS")); objdto.setM6cktid(rs.getString("CIRCUIT_ID")); objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objdto.setRegion(rs.getString("REGION")); objdto.setBandwaidth(rs.getString("BILLING_BANDWIDTH")); objdto.setVertical(rs.getString("VERTICAL")); objdto.setAccountManager(rs.getString("ACCOUNT_MGR")); objdto.setProjectManager(rs.getString("PROJECT_MGR")); objdto.setDistance(rs.getString("CHARGEABLE_DISTANCE")); objdto.setDispatchAddress1(rs.getString("DISPATCH_ADDRESS")); objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); // productname objdto.setPartyName(rs.getString("PARTY_NAME")); //objdto.setBilling_location_from(rs.getString("BILLING_ADDRESS")); // DEMO_ORDER = ? //objdto.setDemo(rs.getString("DEMO_ORDER")); // CRM_PRODUCTNAME = ? //objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME")); objdto.setToLocation(rs.getString("TO_ADDRESS")); objdto.setFromLocation(rs.getString("FROM_ADDRESS")); objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); //BILLING_BANDWIDTH_UOM = ? //remove this column //objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); //BL_SOURCE = ? //objdto.setBlSource(rs.getString("BL_SOURCE")); objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID")); objdto.setOrderNumber(rs.getInt("ORDERID")); objdto.setChargeAmount(rs.getDouble("INV_AMT")); // LINEITEMAMOUNT = ? //objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT")); //TOTAL_CHARGE_AMT = ? //objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT")); objdto.setContractPeriod(rs.getInt("CONTRACT_PERIOD_MNTHS")); //objdto.setTotalPoAmt(""+rs.getDouble("TOTAL_POAMOUNT")); // is it POAMOUNT objdto.setTotalPoAmt("" + rs.getDouble("POAMOUNT")); //PARTY_ID = ? //objdto.setParty_id(rs.getInt("PARTY_ID")); // is it party no // CRMACCOUNTNO = ? //objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); //m6 productid objdto.setM6OrderNo(rs.getInt("M6_ORDER_ID")); // CUST_TOT_PO_AMT = ? //objdto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); //m6 order,business // PK_CHARGE_ID = ? //objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12 // objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); // CONTRACT_PERIOD_MNTHS is using above objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objdto.setServiceId(rs.getInt("SERVICE_NO")); if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) { objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE")); objdto.setPoExpiryDate((utility.showDate_Report(objdto.getPoExpiryDate())).toUpperCase()); } //lawkush start objdto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //lawkush 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<PendingServicesReportDTO> viewPendingServicesReport(PendingServicesReportDTO objDto) { Connection connection = null; CallableStatement proc = null; ResultSet rs = null;//w ww .j a v a 2 s . co m ArrayList<PendingServicesReportDTO> listSearchDetails = new ArrayList<PendingServicesReportDTO>(); PendingServicesReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); // Nagarjuna String methodName = "viewPendingServicesReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPendingServicesReport); 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.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)); rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new PendingServicesReportDTO(); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); //vijay replace CRMACCOUNTNO from ACCOUNTNAME objReportsDto.setCrmAccountNoString(rs.getString("ACCOUNTNAME")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); 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.setAmApproveDate(rs.getString("AM_APPROVED_DATE")); if (rs.getString("AM_APPROVED_DATE") != null && !"".equals(rs.getString("AM_APPROVED_DATE"))) { objReportsDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setAmName(rs.getString("AM_APPROVED_BY")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); objReportsDto.setPmApproveDate(rs.getString("PM_APPROVED_DATE")); if (rs.getString("PM_APPROVED_DATE") != null && !"".equals(rs.getString("PM_APPROVED_DATE"))) { objReportsDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmName(rs.getString("PM_APPROVED_BY")); objReportsDto.setCopcApproveDate(rs.getString("FINAL_APPROVED_DATE"));//Copc date if (rs.getString("FINAL_APPROVED_DATE") != null && !"".equals(rs.getString("FINAL_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("FINAL_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcName(rs.getString("COPC_APPROVED_BY")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setOrderStage(rs.getString("APPROVED_STATUS")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setM6OrderDate(rs.getString("M6_ORDER_DATE")); if (rs.getString("M6_ORDER_DATE") != null && !"".equals(rs.getString("M6_ORDER_DATE"))) { objReportsDto.setM6OrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("M6_ORDER_DATE").getTime()))) .toUpperCase()); } objReportsDto.setBillingStatus(rs.getString("BILLING_STATUS")); objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objReportsDto.setPublishedDate( (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setEffStartDate(rs.getString("EFFECTIVE_START_DATE")); if (rs.getString("EFFECTIVE_START_DATE") != null && !"".equals(rs.getString("EFFECTIVE_START_DATE"))) { objReportsDto.setEffStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("EFFECTIVE_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderStatus(rs.getString("ORDER_STATUS")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setCircuitStatus(rs.getString("CIRCUIT_STATUS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setZoneName(rs.getString("ZONE")); objReportsDto.setStandardReason(rs.getString("STANDARD_REASON")); objReportsDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setServiceIdString(rs.getString("SERVICEID")); objReportsDto.setAccountId(rs.getLong("ACCOUNTID")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setCurrencyName(rs.getString("CURNAME")); 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<PendingOrdersAndBillingHardwaresDTO> ViewpendingOrderBillandHardwareList( PendingOrdersAndBillingHardwaresDTO objDto) throws Exception { // Nagarjuna String methodName = "ViewpendingOrderBillandHardwareList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from w w w. j a va 2 s . co m*/ ArrayList<PendingOrdersAndBillingHardwaresDTO> objUserList = new ArrayList<PendingOrdersAndBillingHardwaresDTO>(); 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(sqlPendingOrderBillHardware); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(2, objDto.getFromAccountNo()); } else { proc.setNull(2, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(3, objDto.getToAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromOrderDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(4, formattedDate); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToOrderDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(5, formattedDate1); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getFromCrmOrderid() != null && !"".equals(objDto.getFromCrmOrderid())) { proc.setLong(6, new Long(objDto.getFromCrmOrderid().trim())); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToCrmOrderid() != null && !"".equals(objDto.getToCrmOrderid())) { proc.setLong(7, new Long(objDto.getToCrmOrderid().trim())); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getParty() != null && !"".equals(objDto.getParty())) { proc.setString(8, objDto.getParty().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new PendingOrdersAndBillingHardwaresDTO(); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setAccountID(rs.getInt("CRMACCOUNTNO")); 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.setOrderNo(rs.getString("ORDERNO")); objDto.setPoNumber(rs.getInt("PODETAILNO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO")); objDto.setLinename(rs.getString("LINENAME")); //objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objDto.getChargeAmount_String() == null) { objDto.setChargeAmount_String(" "); } 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.setChallenno(rs.getString("CHALLEN_NO")); objDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderType(rs.getString("ORDERTYPE")); //[606060] Start objDto.setServiceNumber(rs.getInt("SERVICE")); objDto.setCurrency(rs.getString("CURRENCY")); //[606060] 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);//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<StartChargeNotPushedInFXDTO> viewStartChargeNotPushedInFx(StartChargeNotPushedInFXDTO objDto) { // Nagarjuna String methodName = "viewStartChargeNotPushedInFx", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end// w ww. j a v a 2 s .c o m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<StartChargeNotPushedInFXDTO> listSearchDetails = new ArrayList<StartChargeNotPushedInFXDTO>(); StartChargeNotPushedInFXDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetStartChargeNotPushedInFx); 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.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)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new StartChargeNotPushedInFXDTO(); 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")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); //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"))) { objReportsDto.setStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { objReportsDto .setEndDate((utility.showDate_Report(new Date(rs.getTimestamp("END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { objReportsDto.setContractStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTSTARTDATE").getTime()))) .toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { objReportsDto.setContractEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTENDDATE").getTime()))) .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.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo1(rs.getString("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setPonum1(rs.getString("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { objReportsDto.setPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); 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.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()); } 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.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.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); objReportsDto.setChallendate(s5); } 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"))) { objReportsDto.setWarrantyStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { objReportsDto.setWarrantyEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { objReportsDto.setExtSuportEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("EXT_SUPPORT_END_DATE").getTime()))) .toUpperCase()); } 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.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setServiceStageDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGESTYPE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE objReportsDto.setRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { objReportsDto.setRfsDate( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_RFS_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime()))) .toUpperCase()); } objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed //--Fx Status Ed 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(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objReportsDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(rs.getString("BILLING_ADDRESS")); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); objReportsDto.setCanceldate(rs.getString("CANCEL_DATE")); objReportsDto.setCancelReason(rs.getString("CANCEL_REASON")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setFromLocation(rs.getString("FROM_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.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate 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.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setChargeInfoID(rs.getInt("CHARGEINFOID")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); // --Cust Account id objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setStart_fx_no(rs.getString("START_DETAILS_FX_NO")); objReportsDto.setEnd_fx_no(rs.getString("END_DETAILS_FX_NO")); // --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 (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<PerformanceSummaryReportDTO> viewPerformanceSummaryReport(PerformanceSummaryReportDTO objDto) { // Nagarjuna String methodName = "viewPerformanceSummaryReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//w w w. j a v a 2s .c om Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<PerformanceSummaryReportDTO> listSearchDetails = new ArrayList<PerformanceSummaryReportDTO>(); PerformanceSummaryReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPerformanceSummaryReport); 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.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(13, objDto.getOsp().trim()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new PerformanceSummaryReportDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setParty_no(rs.getInt("Party_NO")); //objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setZoneName(rs.getString("ZONE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); //objReportsDto.setAccountId(rs.getLong("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setIndustrySegment(rs.getString("INDUSTRYSEGMENT")); //objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); objReportsDto.setSubChangeTypeName(rs.getString("ORDER_SUBTYPE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("AM_DELAY_REASON") != null) { objReportsDto.setAmDelayReason(rs.getString("AM_DELAY_REASON")); } else { objReportsDto.setAmDelayReason(""); } if (rs.getString("COPC_DELAY_REASON") != null) { objReportsDto.setCopcDelayReason(rs.getString("COPC_DELAY_REASON")); } else { objReportsDto.setCopcDelayReason(""); } if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } //objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); objReportsDto.setPoAmountSum(rs.getLong("POAMOUNT")); objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPublishedDate(rs.getString("Published_Date")); if (rs.getString("Published_Date") != null && !"".equals(rs.getString("Published_Date"))) { objReportsDto.setPublishedDate( (utility.showDate_Report(new Date(rs.getTimestamp("Published_Date").getTime()))) .toUpperCase()); } //objReportsDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); objReportsDto.setDayInAM(rs.getString("DAYS_IN_AM")); objReportsDto.setDayInPM(rs.getString("DAYS_IN_PM")); objReportsDto.setDayInCOPC(rs.getString("DAYS_IN_COPC")); objReportsDto.setDayInSED(rs.getString("DAYS_IN_SED")); objReportsDto.setTotalDays(rs.getString("TOTAL_DAYS")); objReportsDto.setOsp(rs.getString("OSP")); // [404040] Start objReportsDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End objReportsDto.setGroupName((rs.getString("GROUPNAME"))); //lawkush start objReportsDto.setCopcStartDate(rs.getString("COPC_START_DATE")); if (rs.getString("COPC_START_DATE") != null && !"".equals(rs.getString("COPC_START_DATE"))) { objReportsDto.setCopcStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcEndDate(rs.getString("COPC_END_DATE")); if (rs.getString("COPC_END_DATE") != null && !"".equals(rs.getString("COPC_END_DATE"))) { objReportsDto.setCopcEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_END_DATE").getTime()))) .toUpperCase()); } //lawkush End //[606060] Start objReportsDto.setCancelDate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objReportsDto.setCancelDate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); objReportsDto.setCancelMonth( (new SimpleDateFormat("MMM").format(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } // [606060] 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<BillingTriggerDoneButFailedInFXDTO> viewBillingTriggerDoneButFailedInFX( BillingTriggerDoneButFailedInFXDTO objDto) { // Nagarjuna String methodName = "viewBillingTriggerDoneButFailedInFX", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*w ww .j ava 2 s . co m*/ 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"); 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())) { 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.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)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new BillingTriggerDoneButFailedInFXDTO(); 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")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); //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"))) { objReportsDto.setStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { objReportsDto .setEndDate((utility.showDate_Report(new Date(rs.getTimestamp("END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { objReportsDto.setContractStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTSTARTDATE").getTime()))) .toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { objReportsDto.setContractEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTENDDATE").getTime()))) .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.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo1(rs.getString("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setPonum1(rs.getString("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { objReportsDto.setPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); 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.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()); } 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.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.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); objReportsDto.setChallendate(s5); } 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"))) { objReportsDto.setWarrantyStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { objReportsDto.setWarrantyEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { objReportsDto.setExtSuportEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("EXT_SUPPORT_END_DATE").getTime()))) .toUpperCase()); } 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.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setServiceStageDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGESTYPE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE objReportsDto.setRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { objReportsDto.setRfsDate( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_RFS_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime()))) .toUpperCase()); } objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed //--Fx Status Ed 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(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objReportsDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(rs.getString("BILLING_ADDRESS")); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); objReportsDto.setCanceldate(rs.getString("CANCEL_DATE")); objReportsDto.setCancelReason(rs.getString("CANCEL_REASON")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setFromLocation(rs.getString("FROM_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.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate 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.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setChargeInfoID(rs.getInt("CHARGEINFOID")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); // --Cust Account id objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setStart_fx_no(rs.getString("START_DETAILS_FX_NO")); objReportsDto.setEnd_fx_no(rs.getString("END_DETAILS_FX_NO")); // --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 (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 av a 2 s . c o 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<PendingOrderAndBillingReportDTO> viewPendingOrderAndBillingList( PendingOrderAndBillingReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewPendingOrderAndBillingList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end // w ww .jav a 2 s. co m ArrayList<PendingOrderAndBillingReportDTO> objUserList = new ArrayList<PendingOrderAndBillingReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; PendingOrderAndBillingReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlPendingOrderAndBillingReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getLOC_Date() != null && !"".equals(objDto.getLOC_Date())) { proc.setString(2, objDto.getLOC_Date()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(3, objDto.getFromAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(4, objDto.getToAccountNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(5, formattedDate); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(6, formattedDate1); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(7, objDto.getFromOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(8, objDto.getToOrderNo()); } else { proc.setNull(8, java.sql.Types.BIGINT); } if (objDto.getActmngname() != null && !"".equals(objDto.getActmngname())) { proc.setString(9, objDto.getActmngname().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(10, pagingSorting.getSortByColumn());// columnName proc.setString(11, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(12, pagingSorting.getStartRecordId());// start index proc.setInt(13, pagingSorting.getEndRecordId());// end index proc.setInt(14, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(15, objDto.getOsp().trim()); } else { proc.setNull(15, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new PendingOrderAndBillingReportDTO(); objDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setCrm_productname(rs.getString("CRM_PRODUCT_NAME")); objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setCompanyName(rs.getString("COMPANYNAME")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objDto.getCustPoDate()); objDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setLinename(rs.getString("LINENAME")); objDto.setLcompanyname(rs.getString("LICENCECOMPANYNAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objDto.setStorename(rs.getString("STORENAME")); objDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setCurrencyName(rs.getString("CURRENCYNAME")); objDto.setAnnualRate(rs.getString("ANNUAL_RATE")); if (objDto.getAnnualRate() == null) { objDto.setAnnualRate(" "); } objDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objDto.getChargeAmount_String() == null) { objDto.setChargeAmount_String(" "); } objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); 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.setPm_pro_date(rs.getString("PM_PROV_DATE")); if (rs.getString("PM_PROV_DATE") != null && !"".equals(rs.getString("PM_PROV_DATE"))) { String s1 = rs.getString("PM_PROV_DATE"); String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setPm_pro_date(s5); } objDto.setOrderDate(rs.getString("POCREATEDATE")); if (rs.getString("POCREATEDATE") != null && !"".equals(rs.getString("POCREATEDATE"))) { objDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("POCREATEDATE").getTime()))) .toUpperCase()); } objDto.setPoNumber(rs.getInt("PONUMBER")); objDto.setPrjmngname(rs.getString("PMNAME")); 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.setTaxation(rs.getString("TAXATION")); objDto.setUom(rs.getString("UOM")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); //objDto.setOsp(rs.getString("OSP")); objDto.setServiceProductId(rs.getString("SERVICEPRODUCTID")); // StartChange by Anadi's Change objDto.setBillingTriggStatus(rs.getString("BILLING_TRIGGER_STATUS")); objDto.setChargeInfoId(rs.getString("CHARGEINFOID")); objDto.setChangeServiceId(rs.getString("CHANGE_SERVICEID")); // EndChange By Anadi 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<RateRenewalReportDTO> viewRateRenewalReport(RateRenewalReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewRateRenewalReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end //from ww w . j a v a 2s.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; }