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_Usage.java
/** * * @param objDto/* ww w.j a v a2 s . c o m*/ * @return * @throws Exception */ public ArrayList<ActiveLineItemReportsDTO> viewActiveLineItemsList(ActiveLineItemReportsDTO objDto) throws Exception { //Nagarjuna String methodName = "viewActiveLineItemsList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ActiveLineItemReportsDTO> objUserList = new ArrayList<ActiveLineItemReportsDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlActiveLineItemsForUsage); proc.setInt(1, java.sql.Types.BIGINT); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(2, objDto.getOrderType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { 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(3, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(3, 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(4, formattedDate1); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(5, objDto.getFromOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(6, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index proc.setInt(12, (objDto.getIsUsage()));// end if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(13, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(14, formattedDate1); } else { proc.setNull(14, java.sql.Types.VARCHAR); } if (objDto.getPartyNo() != 0 && !"".equals(objDto.getPartyNo())) { proc.setInt(15, objDto.getPartyNo()); } else { proc.setNull(15, java.sql.Types.BIGINT); } System.out.println("Dao" + objDto.getPartyNo()); if (objDto.getCustomerSegment() != null && !"".equals(objDto.getCustomerSegment()) && !objDto.getCustomerSegment().equals("0")) { proc.setString(16, objDto.getCustomerSegment()); } else { proc.setNull(16, java.sql.Types.VARCHAR); } System.out.println("Dao cust " + objDto.getCustomerSegment()); if (objDto.getProductName() != null && !"".equals(objDto.getProductName()) && !objDto.getProductName().equals("0")) { proc.setString(17, objDto.getProductName()); } else { proc.setNull(17, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; ActiveLineItemReportsDTO objdto; while (rs.next() != false) { countFlag++; //Added by Ashutosh for Billing Address //[270513]Start setBlank(); //replaceSeperator("BILLING_LOCATION",rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objdto = new ActiveLineItemReportsDTO(); objdto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objdto.setCustSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO")); objdto.setServiceName(rs.getString("SERVICENAME")); //objdto.setLinename(rs.getString("LINENAME"));//PARENT_NAME //objdto.setAccountID(rs.getInt("ACCOUNTID"));//CUSTACCOUNTID objdto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); //objdto.setCurrencyName(rs.getString("CURNAME"));//CURRENCYNAME objdto.setEntity(rs.getString("ENTITYNAME")); objdto.setBillingMode(rs.getString("BILLINGMODE")); objdto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objdto.setBillingformat(rs.getString("BILLING_FORMATNAME")); objdto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objdto.setTaxation(rs.getString("TAXATIONVALUE")); objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); //objdto.setPoNumber(rs.getInt("PODETAILNUMBER"));//PONUMBER Date temDate = rs.getDate("PODATE"); if (temDate != null) { objdto.setPoDate((Utility.showDate_Report(temDate)).toUpperCase()); } objdto.setParty_num(rs.getString("PARTY_NO")); objdto.setBillingTriggerFlag(rs.getString("BILLING_TRIGGER_FLAG")); /*objdto.setPm_pro_date(rs.getString("PM_PROVISIONING_DATE"));//Pm_Prov_Date if (rs.getString("PM_PROVISIONING_DATE") != null && !"".equals(rs.getString("PM_PROVISIONING_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); }*/ objdto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objdto.getLocDate()); objdto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } 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.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));//Child_Account_Number objdto.setChild_account_creation_status(rs.getString("Child_account_FX_sataus")); tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objdto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } /*objdto.setCopcapprovaldate(rs.getString("APPROVAL_DATE"));//ORDER_APPROVED_DATE if (rs.getString("APPROVAL_DATE") != null && !"".equals(rs.getString("APPROVAL_DATE"))) { objdto.setCopcapprovaldate((utility.showDate_Report(new Date(rs.getTimestamp("APPROVAL_DATE").getTime()))).toUpperCase()); }*/ objdto.setOrderType(rs.getString("ORDERTYPE")); ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objdto.setBillingtrigger_createdate(Utility.showDate_Report(tempDate).toUpperCase()); } objdto.setProductName(rs.getString("PRODUCTNAME")); objdto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objdto.setServiceStage(rs.getString("SERVICE_STAGE")); objdto.setOrderStage(rs.getString("STAGE")); /*objdto.setRfsDate(rs.getString("RFS_DATE"));//SERVICE_RFS_DATE if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objdto.setRfsDate((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))).toUpperCase()); }*/ tempDate = rs.getDate("PORECEIVEDATE"); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objdto.setPoReceiveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objdto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); //objdto.setCustPoDate(rs.getString("CUST_PODATE"));//PORECEIVEDATE /*if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) { Date date=df.parse(objdto.getCustPoDate()); objdto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); }*/ objdto.setM6cktid(rs.getString("CKTID")); objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objdto.setRegion(rs.getString("REGIONNAME"));//Added in View Componet :AKS objdto.setBandwaidth(rs.getString("BANDWIDTH")); objdto.setVertical(rs.getString("VERTICAL_DETAILS")); objdto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objdto.setProjectManager(rs.getString("PROJECTMANAGER")); objdto.setPartyName(rs.getString("PARTYNAME")); //objdto.setBilling_location_from(rs.getString("BILLING_LOCATION"));//BILLING_ADDRESS //objdto.setDemo(rs.getString("DEMO_ORDER"));//Demo_Type //objdto.setToLocation(rs.getString("FROM_LOCATION"));//FROM_ADDRESS //objdto.setFromLocation(rs.getString("TO_LOCATION"));//TO_ADDRESS objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID")); objdto.setOrderNumber(rs.getInt("ORDERNO")); objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); //objdto.setTotalPoAmt(""+rs.getDouble("TOTAL_POAMOUNT"));//ORDERTOTAL objdto.setParty_id(rs.getInt("PARTY_ID")); objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); //objdto.setServiceId(rs.getInt("SERVICEID"));//SERVICE_NO //[111] START objdto.setMainSpid(rs.getLong("MAIN_SERVICEPRODUCTID")); objdto.setTaxExemptReasonName(rs.getString("REASONNAME")); //[111] end //[003] Start objdto.setServiceSegment(rs.getString("ACCOUNTCATEGORY_DETAILS")); //[003] end //[004] rahul Start objdto.setChargeRedirectionLSI(rs.getString("FX_REDIRECTION_LSI")); //[004] rahul end //[005] Start objdto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY")); objdto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY")); objdto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE")); objdto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE")); objdto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME")); objdto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER")); objdto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID")); //[005] End //[006] Start objdto.setStandardReason(rs.getString("STANDARDREASON")); objdto.setLdClause(rs.getString("LDCLAUSE")); //[006] End //Saurabh : Changes for Usage for storing charge data if (objDto.getIsUsage() == 0) { objdto.setChargeTypeName(rs.getString("CHARGETYPE")); objdto.setChargeTypeID(rs.getInt("CHARGETYPEID")); objdto.setChargeName(rs.getString("CHARGE_NAME")); objdto.setChargeFrequency(rs.getString("FREQUENCYNAME")); objdto.setBillPeriod(rs.getString("BILL_PERIOD")); 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.setStore(rs.getString("STORENAME")); objdto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objdto.setSaleNature(rs.getString("SALENATURENAME")); objdto.setSaleTypeName(rs.getString("SALETYPENAME")); objdto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objdto.setSeclocation(rs.getString("SECONDARYLOCATION")); objdto.setChargeAnnotation(rs.getString("ANNOTATION")); objdto.setFx_sd_charge_status(rs.getString("FX_SD_CHG_SATATUS")); objdto.setFx_charge_status(rs.getString("FX_STATUS")); objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHARGE_STATUS")); objdto.setTokenID(rs.getInt("TOKEN_ID")); 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.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"); String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objdto.setChallendate(s5); } objdto.setRatio(rs.getString("RATIO")); objdto.setHardwareType(rs.getString("HARDWARE_FLAG")); objdto.setCharge_status(rs.getString("CHARGES_STATUS")); objdto.setLOC_No(rs.getString("LOCNO")); objdto.setAddress1(rs.getString("ADDRESS")); objdto.setRate_code(rs.getString("RATECODE")); objdto.setDistance(rs.getString("CHARGEABLE_DISTANCE")); objdto.setDispatchAddress1(rs.getString("DISPATCH_DETAILS")); objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME")); objdto.setBlSource(rs.getString("BL_SOURCE")); objdto.setChargeAmount(rs.getDouble("INV_AMT")); objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT")); objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT")); objdto.setM6OrderNo(rs.getInt("M6ORDERID")); objdto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12 objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //Start: Adding Changes objdto.setLinename(rs.getString("LINENAME"));//PARENT_NAME objdto.setAccountID(rs.getInt("ACCOUNTID"));//CUSTACCOUNTID objdto.setCurrencyName(rs.getString("CURNAME"));//CURRENCYNAME objdto.setPoNumber(rs.getInt("PODETAILNUMBER"));//PONUMBER objdto.setPm_pro_date(rs.getString("PM_PROVISIONING_DATE"));//Pm_Prov_Date if (rs.getString("PM_PROVISIONING_DATE") != null && !"".equals(rs.getString("PM_PROVISIONING_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); } objdto.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID"));//Child_Account_Number objdto.setCopcapprovaldate(rs.getString("APPROVAL_DATE"));//ORDER_APPROVED_DATE if (rs.getString("APPROVAL_DATE") != null && !"".equals(rs.getString("APPROVAL_DATE"))) { objdto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("APPROVAL_DATE").getTime()))) .toUpperCase()); } tempDate = rs.getDate("RFS_DATE"); if (tempDate != null) { objdto.setRfsDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objdto.setCustPoDate(rs.getString("CUST_PODATE"));//PORECEIVEDATE if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) { Date date = df.parse(objdto.getCustPoDate()); objdto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objdto.setBilling_location_from(rs.getString("BILLING_LOCATION"));//BILLING_ADDRESS objdto.setDemo(rs.getString("DEMO_ORDER"));//Demo_Type objdto.setToLocation(rs.getString("FROM_LOCATION"));//FROM_ADDRESS objdto.setFromLocation(rs.getString("TO_LOCATION"));//TO_ADDRESS objdto.setTotalPoAmt("" + rs.getDouble("TOTAL_POAMOUNT"));//ORDERTOTAL objdto.setServiceId(rs.getInt("SERVICEID"));//SERVICE_NO objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE")); if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) { //Date date=df.parse(objdto.getPoExpiryDate()); //objdto.setPoExpiryDate((utility.showDate_Report(date)).toUpperCase()); objdto.setPoExpiryDate( (utility.showDate_Report(new Date(rs.getTimestamp("PO_EXPIRY_DATE").getTime()))) .toUpperCase()); } //END } // Meenakshi : Changes for Usage for storing component data else if (objDto.getIsUsage() == 1) { objdto.setFxInternalId(rs.getInt("INTERNAL_ID")); objdto.setMinimum_bandwidth(rs.getString("MINIMUM_BANDWIDTH"));//Need to add in View : AKS(Added) objdto.setMinimum_bandwidth_UOM(rs.getString("MINIMUM_BANDWIDTH_UOM"));//Need to add in View : AKS(Added) objdto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objdto.setComponentID(rs.getInt("COMPONENT_ID")); objdto.setComponentName(rs.getString("COMPONENT_NAME")); objdto.setPackageID(rs.getInt("PACKAGE_ID")); objdto.setPackageName(rs.getString("PACKAGE_NAME")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT"));//RC_NRC_COMP_AMOUNT : AKS dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("COMPONENT_END_DATE")); if (rs.getString("COMPONENT_END_DATE") != null && !"".equals(rs.getString("COMPONENT_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); //dto.setChargesSumOfLineitem(rs.getString("FX_START_COMPONENT_STATUS")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID"));//COMP_FX_INSTANCE_ID : AKS dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS"));//FX_END_COMPONENT_STATUS :AKS //dto.setStartStatus(rs.getString("FX_ST_COMPONENT_STATUS")); objdto.setExternalId(rs.getString("FX_SI_ID")); //Start: Adding Changes objdto.setM6OrderNo(rs.getInt("M6ORDERNO")); objdto.setLinename(rs.getString("PARENT_NAME")); objdto.setAccountID(rs.getInt("CUSTACCOUNTID")); objdto.setCurrencyName(rs.getString("CURRENCYNAME")); objdto.setPoNumber(rs.getInt("PONUMBER")); 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.setFx_external_acc_id(rs.getString("Child_Account_Number")); /* objdto.setCopcapprovaldate(rs.getString("ORDER_APPROVED_DATE")); if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objdto.setCopcapprovaldate((utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))).toUpperCase()); }*/ objdto.setOrderApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date tempDate = rs.getDate("ORDER_APPROVED_DATE"); if (tempDate != null && !"".equals(tempDate)) { objdto.setOrderApproveDate((utility.showDate_Report(tempDate)).toUpperCase()); } objdto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); ts = rs.getTimestamp("COPC_APPROVED_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objdto.setCopcapprovaldate((utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("SERVICE_RFS_DATE"); if (tempDate != null) { objdto.setRfsDate(Utility.showDate_Report(tempDate).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objdto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objdto.setBilling_location_from(VAR_PRIMARYLOCATION); objdto.setBilling_location_to(VAR_SECONDARYLOCATION); objdto.setDemo(rs.getString("Demo_Type")); objdto.setToLocation(rs.getString("TO_ADDRESS")); objdto.setFromLocation(rs.getString("FROM_ADDRESS")); objdto.setTotalPoAmt("" + rs.getDouble("ORDERTOTAL")); objdto.setServiceId(rs.getInt("SERVICE_NO")); //nagarjuna PoExpiryDate Modified objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE")); if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) { //Date date=df.parse(objdto.getPoExpiryDate()); //objdto.setPoExpiryDate((utility.showDate_Report(date)).toUpperCase()); objdto.setPoExpiryDate( (utility.showDate_Report(new Date(rs.getTimestamp("PO_EXPIRY_DATE").getTime()))) .toUpperCase()); } //nagarjuna PoExpiryDate Modified End //END //satyapan OSP/ISP objdto.setIsOspRequired(rs.getString("IS_OSP")); if (rs.getString("OSP_REG_NO") != null && !"".equals(rs.getString("OSP_REG_NO"))) { objdto.setOspRegistrationNo(rs.getString("OSP_REG_NO")); } else { objdto.setOspRegistrationNo(""); } objdto.setOspRegistrationDate(rs.getString("OSP_REG_DATE")); if (rs.getString("OSP_REG_DATE") != null && !"".equals(rs.getString("OSP_REG_DATE"))) { objdto.setOspRegistrationDate( (Utility.showDate_Report(new Date(rs.getTimestamp("OSP_REG_DATE").getTime()))) .toUpperCase()); } objdto.setIsIspRequired(rs.getString("ISP_TAGGING")); if (rs.getString("ISP_LIC_CTGRY") != null && !"".equals(rs.getString("ISP_LIC_CTGRY")) && !"0".equals(rs.getString("ISP_LIC_CTGRY"))) { objdto.setIspCatageory(rs.getString("ISP_LIC_CTGRY")); } else { objdto.setIspCatageory(""); } if (rs.getString("ISP_LIC_NO") != null && !"".equals(rs.getString("ISP_LIC_NO"))) { objdto.setIspLicenseNo(rs.getString("ISP_LIC_NO")); } else { objdto.setIspLicenseNo(""); } objdto.setIspLicenseDate(rs.getString("ISP_LIC_DATE")); if (rs.getString("ISP_LIC_DATE") != null && !"".equals(rs.getString("ISP_LIC_DATE"))) { objdto.setIspLicenseDate( (Utility.showDate_Report(new Date(rs.getTimestamp("ISP_LIC_DATE").getTime()))) .toUpperCase()); } //End of satyapan OSP/ISP objdto.setComponentDto(dto); } /// End //[007] start objdto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objdto.setChannelPartner(rs.getString("PARTNER_NAME")); objdto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objdto.setPartnerId(rs.getString("PARTNER_ID")); //[007] End //[008] start objdto.setPartnerCode(rs.getString("PARTNER_CODE")); objdto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //NANCY objdto.setePCNNo(rs.getString("EPCN_NO")); //NANCY //[008] 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) { //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<AdvancePaymentReportDTO> viewAdvancePaymentReport(AdvancePaymentReportDTO objDto) throws Exception { String methodName = "viewAdvancePaymentReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; ArrayList<AdvancePaymentReportDTO> objUserList = new ArrayList<AdvancePaymentReportDTO>(); AdvancePaymentReportDTO objReportsDto = null; Connection conn = null;/*from ww w .j a v a2 s . c om*/ CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlAdvancePayementReport); if (objDto.getCrmAccountNo() != null && !"".equals(objDto.getCrmAccountNo())) { proc.setString(1, objDto.getCrmAccountNo().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getArcChqNo() != null && !"".equals(objDto.getArcChqNo())) { proc.setString(2, objDto.getArcChqNo().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getOtcChqNo() != null && !"".equals(objDto.getOtcChqNo())) { proc.setString(3, objDto.getOtcChqNo().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromorderCreationDate() != null && !"".equals(objDto.getFromorderCreationDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); proc.setString(4, objDto.getFromorderCreationDate().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getToorderCreationDate() != null && !"".equals(objDto.getToorderCreationDate())) { proc.setString(5, objDto.getToorderCreationDate().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getFromChqDate() != null && !"".equals(objDto.getFromChqDate())) { proc.setString(6, objDto.getFromChqDate().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getToChqDate() != null && !"".equals(objDto.getToChqDate())) { proc.setString(7, objDto.getToChqDate().trim()); } else { proc.setNull(7, java.sql.Types.VARCHAR); } if (objDto.getDatetype() != null && !"".equals(objDto.getDatetype())) { proc.setString(8, objDto.getDatetype().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); 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_Desc1(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 rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objReportsDto = new AdvancePaymentReportDTO(); objReportsDto.setOrderNo(rs.getInt("ORDERNO")); objReportsDto.setOrderStatus(rs.getString("ORDER_STATUS")); objReportsDto.setOrderCreationDate(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { objReportsDto.setOrderCreationDate( (utility.showDate_Report(objReportsDto.getOrderCreationDate())).toUpperCase()); } objReportsDto.setCrmAccountNo(rs.getString("CRMACCOUNTNO")); objReportsDto.setCustomerSegment(rs.getString("CUSTOMERSEGMENT")); //objReportsDto.setCircle(rs.getString("CIRCLE")); objReportsDto.setLicenseCompany(rs.getString("LICENCE_COMPANY")); objReportsDto.setCurrencyofOrder(rs.getString("CURRENCY_ORDER")); objReportsDto.setAmApprovalDate(rs.getString("AM_APPROVALS")); if (rs.getString("AM_APPROVALS") != null && !"".equals(rs.getString("AM_APPROVALS"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setAmApprovalDate( utility.showDate_Report(objReportsDto.getAmApprovalDate()).toUpperCase()); } objReportsDto.setPmApprovalDate(rs.getString("PM_APPROVALS")); if (rs.getString("PM_APPROVALS") != null && !"".equals(rs.getString("PM_APPROVALS"))) { objReportsDto.setPmApprovalDate((utility.showDate_Report(objReportsDto.getPmApprovalDate()))); } objReportsDto.setOrderApprovalDate(rs.getString("COPC_APPROVALS")); if (rs.getString("COPC_APPROVALS") != null && !"".equals(rs.getString("COPC_APPROVALS"))) { objReportsDto .setOrderApprovalDate((utility.showDate_Report(objReportsDto.getOrderApprovalDate()))); } objReportsDto.setServiceNo(rs.getInt("SERVICE_NO")); objReportsDto.setProduct(rs.getString("PRODUCT")); objReportsDto.setLsi(rs.getInt("LSI")); objReportsDto.setFxChildAccount(rs.getString("FX_CHILD_ACCOUNT")); if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) { Date date = df.parse(rs.getString("LOC_DATE")); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(rs.getString("BILLINGTRIGGERDATE")); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setArcChqNo(rs.getString("ARCCHQNO")); objReportsDto.setArcChqDate(rs.getString("ARCCHQDATE")); if (rs.getString("ARCCHQDATE") != null && !"".equals(rs.getString("ARCCHQDATE"))) { Date date = df.parse(objReportsDto.getArcChqDate()); objReportsDto.setArcChqDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setArcChqAmt(rs.getDouble("ARCCHQAMT")); objReportsDto.setArcBankName(rs.getString("ARCBANKNAME")); objReportsDto.setArcAmtAjd(rs.getDouble("ARCAMTAJD")); //objReportsDto.setArcAmtAjd(rs.getString("ARCAMTAJD")); objReportsDto.setOtcChqNo(rs.getString("OTCCHQNO")); objReportsDto.setArcExempted(rs.getString("ARCEXEMPTED")); objReportsDto.setArcExpreason(rs.getString("ARCEXPREASON")); objReportsDto.setOtcExempted(rs.getString("OTCEXEMPTED")); objReportsDto.setOtcExpreason(rs.getString("OTCEXPREASON")); objReportsDto.setOtcChqDate(rs.getString("OTCCHQDATE")); //Start [128] objReportsDto.setArcReEnterCheckamount(rs.getDouble("ARCRECHQAMT")); objReportsDto.setArcReEnterCheckNumber(rs.getString("ARCRECHQNO")); objReportsDto.setArcBankAccountNumber(rs.getString("ARCBANKACNO")); objReportsDto.setArcReEnterBankAccountNumber(rs.getString("ARCREBANKACNO")); objReportsDto.setArcIfscCode(rs.getString("ARCIFSCCODE")); objReportsDto.setArcReEnterIfscCode(rs.getString("ARCREIFSCCODE")); objReportsDto.setOtcReEnterCheckamount(rs.getDouble("OTCRECHQAMT")); objReportsDto.setOtcReEnterCheckNumber(rs.getString("OTCRECHQNO")); objReportsDto.setOtcBankAccountNumber(rs.getString("OTCBANKACNO")); objReportsDto.setOtcReEnterBankAccountNumber(rs.getString("OTCREBANKACNO")); objReportsDto.setOtcIfscCode(rs.getString("OTCIFSCCODE")); objReportsDto.setOtcReEnterIfscCode(rs.getString("OTCREIFSCCODE")); //End [128] objReportsDto.setLineNo(rs.getString("SPID")); objReportsDto.setLineName(rs.getString("LineName")); if (rs.getString("OTCCHQDATE") != null && !"".equals(rs.getString("OTCCHQDATE"))) { Date date = df.parse(objReportsDto.getOtcChqDate()); objReportsDto.setOtcChqDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setOtcChqAmt(rs.getDouble("OTCCHQAMT")); objReportsDto.setOtcBankName(rs.getString("OTCBANKNAME")); objReportsDto.setOtcAmtAjd(rs.getDouble("OTCAMTAJD")); objReportsDto.setPartyNo(rs.getString("PARTY_NO")); objReportsDto.setAccountName(rs.getString("ACCOUNTNAME")); objReportsDto.setBillingTriggerActionDate(rs.getTimestamp("BILLINGTRIGGERDATEACTION")); String BillActionDate_String = utility.showDate_Report(objReportsDto.getBillingTriggerActionDate()); objReportsDto.setBillingTriggerActionDate_string(BillActionDate_String); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubproductName(rs.getString("SERVICESUBTYPENAME")); objReportsDto.setParty_id(rs.getString("PARTY_ID")); objReportsDto.setPodate(rs.getDate("PODATE")); String poDate_String = utility.showDate_Report(objReportsDto.getPodate()); objReportsDto.setPodate_String(poDate_String); objReportsDto.setPoRecieveDate(rs.getDate("PORECEIVEDATE")); String poRDate_String = utility.showDate_Report(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate_String(poRDate_String); objReportsDto.setZoneName(rs.getString("ZONENNAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objReportsDto); } 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<OrderStageReportDTO> viewOrderStageList(OrderStageReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewOrderStageList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end// w w w . ja v a2s .c om ArrayList<OrderStageReportDTO> objUserList = new ArrayList<OrderStageReportDTO>(); 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(sqlOrderStage); 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("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.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); }*/ PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(4, pagingSorting.getSortByColumn());// columnName proc.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(6, pagingSorting.getStartRecordId());// start index proc.setInt(7, pagingSorting.getEndRecordId());// end index proc.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0)); if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(9, objDto.getOsp().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OrderStageReportDTO(); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setProjectManager(rs.getString("PROJECTMANAGER")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setEffStartDate(rs.getString("EFFSTARTDATE")); if (rs.getString("EFFSTARTDATE") != null && !"".equals(rs.getString("EFFSTARTDATE"))) { Date date = df.parse(objDto.getEffStartDate()); objDto.setEffStartDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setM6OrderNumber(rs.getInt("M6ORDERNO")); objDto.setCustLogicalSI(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objDto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objDto.setPublishedDate( (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } objDto.setServiceDetDescription(rs.getString("SERVICESTAGE")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setBillingStatus(rs.getString("BILLING_STATUS")); objDto.setServiceStage(rs.getString("SERVICE_STAGE")); objDto.setParentOrderSubType(rs.getString("PARENTORDERSUBTYPE")); objDto.setAmName(rs.getString("AMNAME")); objDto.setPmName(rs.getString("PMNAME")); objDto.setCopcName(rs.getString("COPCNAME")); objDto.setStandardReason(rs.getString("STANDARDREASON")); objDto.setM6OrderDate(rs.getString("CREATED_DATE")); if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) { objDto.setM6OrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATED_DATE").getTime()))) .toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.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.setCircuitStatus(rs.getString("CIRCUIT_STATUS")); objDto.setOrderProvision(rs.getString("ORDER_PROVISION")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setOsp(rs.getString("OSP")); //lawkush start objDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objDto.setPoReceiveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime()))) .toUpperCase()); } 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()); } //lawkush End //[505052] START objDto.setDemoType(rs.getString("DEMO_TYPE")); objDto.setCustPoNumber(rs.getString("CUSTPONUMBER")); objDto.setPoContractCnd(rs.getString("PO_CONTRACT_CND")); if (rs.getString("PO_CONTRACT_CND") != null && !"".equals(rs.getString("PO_CONTRACT_CND"))) { objDto.setPoContractCnd( (utility.showDate_Report(new Date(rs.getTimestamp("PO_CONTRACT_CND").getTime()))) .toUpperCase()); } objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setCustomerSegment(rs.getString("CUSTOMER_SEGMENT")); objDto.setServiceStatus(rs.getString("SERVICE_STATUS")); objDto.setPoDetailNumber(rs.getInt("PO_ID")); objDto.setOnnetOffnet(rs.getString("OFFNET_LABELATTVALUE")); objDto.setMedia(rs.getString("MEDIA_LABELATTVALUE")); objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE")); objDto.setPmRemarks(rs.getString("PM_REMARKS")); objDto.setServSubTypeName(rs.getString("SUBPRODUCT_NAME")); objDto.setOrderStage(rs.getString("ORDER_STAGE")); objDto.setTotalChargeAmount(rs.getString("TOTAL_CHARGE_AMT")); //[505052] END //[007] Start objDto.setLdClause(rs.getString("LDCLAUSE")); //[007] End // [130] start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setPartnerId(rs.getString("PARTNER_ID")); // [130] end //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//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<HardwareLineItemCancelReportDTO> viewHardwareCancelReport( HardwareLineItemCancelReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewHardwareCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*ww w . j a v a2 s .c om*/ ArrayList<HardwareLineItemCancelReportDTO> objHardwareLineItemList = new ArrayList<HardwareLineItemCancelReportDTO>(); Connection conn = null; ResultSet rs = null; CallableStatement getHardwareLineItemList = null; try { conn = DbConnection.getConnectionObject(); getHardwareLineItemList = conn.prepareCall(SPGETCANCELHARDWARELINEITEMREPORT); String searchLineItemNo = objDto.getSearchLineNo(); String searchfromDate = objDto.getSearchfromDate(); String searchToDate = objDto.getSearchToDate(); String searchSrno = objDto.getSearchSRNO(); String searchLSI = objDto.getSearchLSI(); String searchAccountNo = objDto.getSearchAccount(); String searchAccountName = objDto.getSearchAccountName(); if (searchAccountNo == null || searchAccountNo.trim().equals("")) { getHardwareLineItemList.setNull(1, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(1, searchAccountNo); } if (searchAccountName == null || searchAccountName.trim().equals("")) { getHardwareLineItemList.setNull(2, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(2, searchAccountName); } if (searchfromDate == null || searchfromDate.trim().equals("")) { getHardwareLineItemList.setNull(3, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(3, searchfromDate); } if (searchToDate == null || searchToDate.trim().equals("")) { getHardwareLineItemList.setNull(4, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(4, searchToDate); } if (searchSrno == null || searchSrno.trim().equals("")) { getHardwareLineItemList.setNull(5, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(5, searchSrno); } if (searchLSI == null || searchLSI.trim().equals("")) { getHardwareLineItemList.setNull(6, java.sql.Types.VARCHAR); } else { getHardwareLineItemList.setString(6, searchLSI); } if (searchLineItemNo == null || searchLineItemNo.trim().equals("")) { getHardwareLineItemList.setNull(7, java.sql.Types.BIGINT); } else { getHardwareLineItemList.setString(7, searchLineItemNo); } SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index getHardwareLineItemList.setString(8, pagingSorting.getSortByColumn());// columnName getHardwareLineItemList.setString(9, PagingSorting.DB_Asc_Desc1(pagingSorting.getSortByOrder()));// sort order getHardwareLineItemList.setInt(10, pagingSorting.getStartRecordId());// start index getHardwareLineItemList.setInt(11, pagingSorting.getEndRecordId());// end index getHardwareLineItemList.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = getHardwareLineItemList.executeQuery(); int countFlag = 0; int recordCount = 0; while (rs.next() != false) { countFlag++; objDto = new HardwareLineItemCancelReportDTO(); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setSrno(rs.getString("REQUESTID")); objDto.setOrderLineNumber(rs.getInt("LINEITEMNO")); objDto.setLineItemName(rs.getString("LINEITEMNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setLogicalSINo(rs.getString("LSINO")); objDto.setM6OrderNo(rs.getInt("M6ORDERNO")); if (rs.getInt("REQUESTSTATUS") == 0 || rs.getInt("REQUESTSTATUS") == 1) { objDto.setRemarks("Open"); } else if (rs.getInt("REQUESTSTATUS") == 2) { objDto.setRemarks("Failed in M6"); } else if (rs.getInt("REQUESTSTATUS") == 3) { objDto.setRemarks("Closed"); } objDto.setCreatedBy(rs.getString("REQUESTEDBY")); objDto.setUserId(rs.getString("USER_ID")); objDto.setSrDate(rs.getString("REQUESTDATE")); if (rs.getString("REQUESTDATE") != null && !"".equals(rs.getString("REQUESTDATE"))) { objDto.setSrDate((Utility.showDate_Report((rs.getTimestamp("REQUESTDATE")))).toUpperCase()); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objHardwareLineItemList.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 { rs.close(); getHardwareLineItemList.close(); 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 objHardwareLineItemList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * // www . j av a 2s . c om * @param objDto * @return * @throws Exception */ public ArrayList<OrderReportNewDetailCwnDTO> viewOrderReportNew(OrderReportNewDetailCwnDTO objDto) throws Exception { // Nagarjuna String methodName = "viewOrderReportNew", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<OrderReportNewDetailCwnDTO> objUserList = new ArrayList<OrderReportNewDetailCwnDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OrderReportNewDetailCwnDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOrderReportNew); 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); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(10, objDto.getOsp().trim()); } else { proc.setNull(10, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OrderReportNewDetailCwnDTO(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimarylocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimarylocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } //objDto.setPrimarylocation(rs.getString("FROM_SITE")); } else { //String ss[] =("A ~~B ~~C ~~D ~~E ~~F ~~G ~~H ~~I").split("~~"); //objDto.setPrimarylocation(ss[0]+" "+ss[1]+" "+ss[4]+" "+ss[5]+" "+ss[6]+" "+ss[7]+" "+ss[8]+" "+ss[9]+" "+ss[10]+" "+ss[2]); objDto.setPrimarylocation(""); } if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } //objDto.setSeclocation(rs.getString("TO_SITE")); } else { objDto.setSeclocation(""); } //objDto.setPrimarylocation(rs.getString("FROM_SITE")); //objDto.setSeclocation(rs.getString("TO_SITE")); objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setPrjmgremail(rs.getString("PMEMAIL")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE")); if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) { objDto.setNio_approve_date( (utility.showDate_Report(new Date(rs.getTimestamp("NIO_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objDto.setServiceType(rs.getString("SERVICETYPE")); 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.setDistance(rs.getString("DISTANCE")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } //objDto.setFrom_city(rs.getString("FROM_CITY")); } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } //objDto.setTo_city(rs.getString("TO_CITY")); } else { objDto.setTo_city(""); } //objDto.setTo_city(""); //objDto.setTo_city(rs.getString("TO_CITY")); objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setOsp(rs.getString("OSP")); // [404040] Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End objDto.setChargeRemarks(rs.getString("REMARKS")); //shourya start objDto.setRE_LOGGED_LSI_NO(rs.getString("RE_LOGGED_LSI_NO")); //[003] start /*objDto.setPARALLEL_UPGRADE_LSI_NO(rs.getString("PARALLEL_UPGRADE_LSI_NO"));*/ objDto.setParallellUpgradeLSINo1(rs.getString("PARALLELUPGRADELSINO1")); objDto.setParallellUpgradeLSINo2(rs.getString("PARALLELUPGRADELSINO2")); objDto.setParallellUpgradeLSINo3(rs.getString("PARALLELUPGRADELSINO3")); //[003] end objDto.setCHARGEDISCONNECTIONSTATUS(rs.getString("CHARGEDISCONNECTIONSTATUS")); objDto.setSubchange_type(rs.getString("NAME_SUBTYPE")); objDto.setFxAccountExternalId(rs.getString("FX_ACCOUNT_EXTERNAL_ID")); objDto.setOldLsi(rs.getString("OLD_LSI_CRM")); //lawkush Start objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); //lawkush End objDto.setCustSeg_Description(rs.getString("DESCRIPTION")); objDto.setObValueLastUpdateDate(rs.getString("OB_VALUE_LAST_UPDATE_DATE")); //[130] start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end //nancy start objDto.setePCNNo(rs.getString("EPCN_NO")); //nancy end 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()); } //RAHEEM start objDto.setIsDifferential(rs.getString("Is_Differential")); objDto.setLinkedOldCharge(rs.getLong("OLD_PK_CHARGEID")); //RAHEEM 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<RestPendingLineReportDTO> viewRestPendingLineReport(RestPendingLineReportDTO objDto) { // Nagarjuna String methodName = "viewRestPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /* ww w . jav a 2s.com*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<RestPendingLineReportDTO> listSearchDetails = new ArrayList<RestPendingLineReportDTO>(); RestPendingLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetRestPendingLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getFromDate()); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getSubChangeTypeName() != null && !"".equals(objDto.getSubChangeTypeName())) { proc.setString(4, objDto.getSubChangeTypeName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(5, objDto.getVerticalDetails().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(6, pagingSorting.getSortByColumn());// columnName proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(8, pagingSorting.getStartRecordId());// start index proc.setInt(9, pagingSorting.getEndRecordId());// end index proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new RestPendingLineReportDTO(); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountManager(rs.getString("ACTMNAME")); objReportsDto.setProjectManager(rs.getString("PMNAME")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objReportsDto.setChargeEndDate(rs.getString("CHARGE_START_DATE")); if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAmapprovaldate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objReportsDto.setAmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objReportsDto.setPmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERCREATEDATE")); if (rs.getString("ORDERCREATEDATE") != null && !"".equals(rs.getString("ORDERCREATEDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoReceiveDate(rs.getString("CUSTPORECDATE")); if (rs.getString("CUSTPORECDATE") != null && !"".equals(rs.getString("CUSTPORECDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCanceldate(rs.getString("CANCELDATE")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGE_TYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setBillingMode(rs.getString("BILLMODE_NAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setCancelflag(rs.getString("CANCELBY")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setAddress1(rs.getString("ADDRESS")); objReportsDto.setFx_status(rs.getString("FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("Fx_St_Chg_Status")); objReportsDto.setFx_ed_status(rs.getString("Fx_Ed_Chg_Status")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objReportsDto.getRfs_date()); objReportsDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setLineno(rs.getInt("LINEITEMNO")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setLocation_from(rs.getString("FROM_LOCATION")); objReportsDto.setLocation_to(rs.getString("TO_LOCATION")); objReportsDto.setServiceStage(rs.getString("SERVICESTAGE")); objReportsDto.setBisource(rs.getString("BISOURCE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setTokenno(rs.getString("TOKENNO")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setSaleNature(rs.getString("SALENATURENAME")); objReportsDto.setDemoType(rs.getString("Demo_Type")); objReportsDto.setSaleType(rs.getString("SALETYPENAME")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACT_ID")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setStageName(rs.getString("ORDERSTAGE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO")); objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setPoAmountSum(rs.getLong("ORDERAMOUNT")); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID")); objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID")); objReportsDto.setLb_pk_charge_id(rs.getString("LB_PK_CHARGE_ID")); objReportsDto.setChargeinfoID(rs.getString("PK_CHARGE_ID")); objReportsDto.setAnnual_rate(rs.getInt("ANNUAL_RATE")); objReportsDto.setBandWidth((rs.getString("BANDWIDTH"))); //[007] Start objReportsDto.setStandardReason(rs.getString("STANDARDREASON")); //[007] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ReportsDto> viewPendingLineReport(ReportsDto objDto) { // Nagarjuna String methodName = "viewPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /* w ww.j a v a2s. co m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>(); ReportsDto objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPendingLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getFromDate()); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getSubChangeTypeName() != null && !"".equals(objDto.getSubChangeTypeName())) { proc.setString(4, objDto.getSubChangeTypeName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(5, objDto.getVerticalDetails().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(6, pagingSorting.getSortByColumn());// columnName proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(8, pagingSorting.getStartRecordId());// start index proc.setInt(9, pagingSorting.getEndRecordId());// end index proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new ReportsDto(); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountManager(rs.getString("ACTMNAME")); objReportsDto.setProjectManager(rs.getString("PMNAME")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objReportsDto.setChargeEndDate(rs.getString("CHARGE_START_DATE")); if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAmapprovaldate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objReportsDto.setAmapprovaldate( (utility.showDate_Report5(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objReportsDto.setPmapprovaldate( (utility.showDate_Report5(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report5(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERCREATEDATE")); if (rs.getString("ORDERCREATEDATE") != null && !"".equals(rs.getString("ORDERCREATEDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoReceiveDate(rs.getString("CUSTPORECDATE")); if (rs.getString("CUSTPORECDATE") != null && !"".equals(rs.getString("CUSTPORECDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCanceldate(rs.getString("CANCELDATE")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGE_TYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setBillingMode(rs.getString("BILLMODE_NAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setCancelflag(rs.getString("CANCELBY")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setAddress1(rs.getString("ADDRESS")); objReportsDto.setFx_status(rs.getString("FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("Fx_St_Chg_Status")); objReportsDto.setFx_ed_status(rs.getString("Fx_Ed_Chg_Status")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objReportsDto.getRfs_date()); objReportsDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setLineno(rs.getInt("LINEITEMNO")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setLocation_from(rs.getString("FROM_LOCATION")); objReportsDto.setLocation_to(rs.getString("TO_LOCATION")); objReportsDto.setServiceStage(rs.getString("SERVICESTAGE")); objReportsDto.setBisource(rs.getString("BISOURCE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setTokenno(rs.getString("TOKENNO")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setSaleNature(rs.getString("SALENATURENAME")); objReportsDto.setDemoType(rs.getString("Demo_Type")); objReportsDto.setSaleType(rs.getString("SALETYPENAME")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setStageName(rs.getString("ORDERSTAGE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO")); objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACT_ID")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); //objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); //here getting only digit if (rs.getString("CREDIT_PERIODNAME") != null) objReportsDto.setCreditPeriod( Integer.parseInt(rs.getString("CREDIT_PERIODNAME").replaceAll("[\\D]", ""))); else objReportsDto.setCreditPeriod(0); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("INV_AMT"))).toPlainString()); objReportsDto .setLineamtString(BigDecimal.valueOf((rs.getDouble("LINEITEMAMOUNT"))).toPlainString()); objReportsDto .setPoAmountSumString(BigDecimal.valueOf((rs.getDouble("ORDERAMOUNT"))).toPlainString()); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrentString( BigDecimal.valueOf((rs.getDouble("TOTALPOAMOUNT"))).toPlainString()); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID")); objReportsDto.setCust_total_poamtString( BigDecimal.valueOf((rs.getDouble("CUST_TOT_PO_AMT"))).toPlainString()); objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID")); objReportsDto.setLb_pk_charge_id(rs.getString("LB_PK_CHARGE_ID")); objReportsDto.setChargeinfoID(rs.getString("PK_CHARGE_ID")); objReportsDto .setAnnual_rateString(BigDecimal.valueOf((rs.getDouble("ANNUAL_RATE"))).toPlainString()); objReportsDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBilling_trigger_date()); objReportsDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objReportsDto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objReportsDto.setBilling_Trigger_Flag(rs.getString("BT_FLAG")); objReportsDto.setLocDate(rs.getString("LOCDATE")); objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) { objDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } } /*Vijay add few more columns*/ objReportsDto.setAsiteAdd1(rs.getString("ASITE_ADD1")); objReportsDto.setAsiteAdd2(rs.getString("ASITE_ADD2")); objReportsDto.setAsiteAdd3(rs.getString("ASITE_ADD3")); objReportsDto.setBsiteName(rs.getString("BSITE_NAME")); objReportsDto.setBsiteLineAdd1(rs.getString("BSITE_ADD1")); objReportsDto.setBsiteLineAdd2(rs.getString("BSITE_ADD2")); //vijay end objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); //[505054] Start objReportsDto.setServiceSegment(rs.getString("SERVICESEGMENT")); //[505054] End //[004] Start objReportsDto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY")); objReportsDto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY")); objReportsDto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE")); objReportsDto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE")); objReportsDto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME")); objReportsDto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER")); objReportsDto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID")); //[004] End //[008] Start objReportsDto.setOrderCreationDate(rs.getString("CREATED_DATE")); if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) { objReportsDto.setOrderCreationDate( (utility.showDate_Report5(new Date(rs.getTimestamp("CREATED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objReportsDto.setPublishedDate( (utility.showDate_Report5(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } //[008] End //[131] start objReportsDto.setChannelPartner(rs.getString("PARTNER_NAME")); objReportsDto.setPartnerCode(rs.getString("PARTNER_CODE")); objReportsDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * * @param objDto//from ww w. ja v a2s . c o m * @return * @throws Exception */ public ArrayList<ActiveLineItemReportsDTO> viewActiveLineItemsList(ActiveLineItemReportsDTO objDto) throws Exception { // Nagarjuna String methodName = "viewActiveLineItemsList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<ActiveLineItemReportsDTO> objUserList = new ArrayList<ActiveLineItemReportsDTO>(); 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(sqlActiveLineItems); proc.setInt(1, java.sql.Types.BIGINT); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(2, objDto.getOrderType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(3, objDto.getFromDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(4, objDto.getToDate().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(5, objDto.getFromOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(6, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } 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 if (objDto.getPartyNo() != 0 && !"".equals(objDto.getPartyNo())) { proc.setInt(12, objDto.getPartyNo()); } else { proc.setNull(12, java.sql.Types.BIGINT); } System.out.println("Pankaj CustSeg" + objDto.getCustomerSegment()); if (objDto.getCustomerSegment() != null && !"".equals(objDto.getCustomerSegment()) && !objDto.getCustomerSegment().equals("0")) { proc.setString(13, objDto.getCustomerSegment()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } System.out.println("Pankaj productname" + objDto.getProductName()); if (objDto.getProductName() != null && !"".equals(objDto.getProductName()) && !objDto.getProductName().equals("0")) { proc.setString(14, objDto.getProductName()); } else { proc.setNull(14, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; ActiveLineItemReportsDTO objdto; while (rs.next() != false) { countFlag++; objdto = new ActiveLineItemReportsDTO(); objdto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objdto.setCustSINo(rs.getString("CUSTOMER_LOGICAL_SI_NO")); objdto.setServiceName(rs.getString("SERVICENAME")); objdto.setLinename(rs.getString("LINENAME")); objdto.setChargeTypeName(rs.getString("CHARGETYPE")); objdto.setChargeTypeID(rs.getInt("CHARGETYPEID")); objdto.setChargeName(rs.getString("CHARGE_NAME")); objdto.setChargeFrequency(rs.getString("FREQUENCYNAME")); objdto.setBillPeriod(rs.getString("BILL_PERIOD")); 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.setAccountID(rs.getInt("ACCOUNTID")); objdto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objdto.setCurrencyName(rs.getString("CURNAME")); objdto.setEntity(rs.getString("ENTITYNAME")); objdto.setBillingMode(rs.getString("BILLINGMODE")); objdto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objdto.setBillingformat(rs.getString("BILLING_FORMATNAME")); objdto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objdto.setTaxation(rs.getString("TAXATIONVALUE")); objdto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objdto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objdto.setStore(rs.getString("STORENAME")); objdto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objdto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objdto.setSaleNature(rs.getString("SALENATURENAME")); objdto.setSaleTypeName(rs.getString("SALETYPENAME")); objdto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objdto.setSeclocation(rs.getString("SECONDARYLOCATION")); 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(date)).toUpperCase()); } objdto.setParty_num(rs.getString("PARTY_NO")); objdto.setChargeAnnotation(rs.getString("ANNOTATION")); objdto.setFx_sd_charge_status(rs.getString("FX_SD_CHG_SATATUS")); objdto.setFx_charge_status(rs.getString("FX_STATUS")); objdto.setFx_Ed_Chg_Status(rs.getString("FX_ED_CHARGE_STATUS")); //Vijay start //objdto.setTokenID(rs.getInt("TOKEN_ID")); objdto.setToken_ID(rs.getString("TOKEN_ID")); //vijay end 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_TRIGGER_FLAG")); objdto.setPm_pro_date(rs.getString("PM_PROVISIONING_DATE")); if (rs.getString("PM_PROVISIONING_DATE") != null && !"".equals(rs.getString("PM_PROVISIONING_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); } objdto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objdto.getLocDate()); objdto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } 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.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.setFx_external_acc_id(rs.getString("FX_ACCOUNT_EXTERNAL_ID")); objdto.setChild_account_creation_status(rs.getString("Child_account_FX_sataus")); objdto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { Date date = df.parse(objdto.getOrderDate()); objdto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objdto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objdto.setPmapprovaldate( (utility.showDate_Report5(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objdto.setCopcapprovaldate(rs.getString("APPROVAL_DATE")); if (rs.getString("APPROVAL_DATE") != null && !"".equals(rs.getString("APPROVAL_DATE"))) { objdto.setCopcapprovaldate( (utility.showDate_Report5(new Date(rs.getTimestamp("APPROVAL_DATE").getTime()))) .toUpperCase()); } objdto.setOrderType(rs.getString("ORDERTYPE")); objdto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objdto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objdto.setRatio(rs.getString("RATIO")); objdto.setProductName(rs.getString("PRODUCTNAME")); objdto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objdto.setHardwareType(rs.getString("HARDWARE_FLAG")); objdto.setServiceStage(rs.getString("SERVICE_STAGE")); objdto.setOrderStage(rs.getString("STAGE")); objdto.setRfsDate(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objdto.setRfsDate((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } 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")); objdto.setCustPoDate(rs.getString("CUST_PODATE")); if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) { Date date = df.parse(objdto.getCustPoDate()); objdto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objdto.setCharge_status(rs.getString("CHARGES_STATUS")); objdto.setLOC_No(rs.getString("LOCNO")); objdto.setAddress1(rs.getString("ADDRESS")); objdto.setM6cktid(rs.getString("CKTID")); objdto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objdto.setRegion(rs.getString("REGIONNAME")); objdto.setBandwaidth(rs.getString("BANDWIDTH")); objdto.setVertical(rs.getString("VERTICAL_DETAILS")); objdto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objdto.setProjectManager(rs.getString("PROJECTMANAGER")); objdto.setRate_code(rs.getString("RATECODE")); objdto.setLmRemarks(rs.getString("LM_REMARKS")); objdto.setLmMedia(rs.getString("LM_MEDIA")); // last objdto.setChargeable_Distance(rs.getString("CHARGEABLE_DISTANCE")); // link objdto.setDispatchAddress1(rs.getString("DISPATCH_DETAILS")); objdto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); // productname objdto.setPartyName(rs.getString("PARTYNAME")); objdto.setBilling_location_from(rs.getString("BILLING_LOCATION")); objdto.setDemo(rs.getString("DEMO_ORDER")); objdto.setCrm_productname(rs.getString("CRM_PRODUCTNAME")); objdto.setToLocation(rs.getString("FROM_LOCATION")); objdto.setFromLocation(rs.getString("TO_LOCATION")); objdto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objdto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objdto.setBlSource(rs.getString("BL_SOURCE")); objdto.setServiceproductid(rs.getInt("ORDER_LINE_ID")); objdto.setOrderNumber(rs.getInt("ORDERNO")); objdto.setChargeAmount(rs.getDouble("INV_AMT")); objdto.setLineamt(rs.getDouble("LINEITEMAMOUNT")); objdto.setChargesSumOfLineitem(rs.getDouble("TOTAL_CHARGE_AMT")); objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objdto.setTotalPoAmt("" + rs.getDouble("TOTAL_POAMOUNT")); objdto.setParty_id(rs.getInt("PARTY_ID")); objdto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); //m6 productid objdto.setM6OrderNo(rs.getInt("M6ORDERID")); objdto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); //m6 order,business objdto.setPk_charge_id(rs.getString("PK_CHARGE_ID"));//Added by Ashutosh as on 26-Jun-12 objdto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objdto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); objdto.setServiceId(rs.getInt("SERVICEID")); objdto.setPoExpiryDate(rs.getString("PO_EXPIRY_DATE")); if (rs.getString("PO_EXPIRY_DATE") != null && !"".equals(rs.getString("PO_EXPIRY_DATE"))) { objdto.setPoExpiryDate((utility.showDate_Report(objdto.getPoExpiryDate())).toUpperCase()); } //[404040] Start objdto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End objdto.setGroupName((rs.getString("GROUPNAME"))); //[505050] Start objdto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objdto.setMocn_no(rs.getString("MOCN_NUMBER")); objdto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objdto.setOldLsi(rs.getString("OLD_LSI_CRM")); objdto.setOpms_lineItemNumber(rs.getString("OPMS_LINEITEMNUMBER")); //[505050] End /*Vijay add few more columns*/ objdto.setAsiteAdd1(rs.getString("ASITE_ADD1")); objdto.setAsiteAdd2(rs.getString("ASITE_ADD2")); objdto.setAsiteAdd3(rs.getString("ASITE_ADD3")); objdto.setBsiteName(rs.getString("BSITE_NAME")); objdto.setBsiteLineAdd1(rs.getString("BSITE_ADD1")); objdto.setBsiteLineAdd2(rs.getString("BSITE_ADD2")); //vijay end //[111] start objdto.setMainSpid(rs.getLong("MAIN_SERVICEPRODUCTID")); objdto.setTaxExemptReasonName(rs.getString("REASONNAME")); //[111] end //[505054] Start objdto.setServiceSegment(rs.getString("SERVICESEGMENT")); //[505054] End //[004] Start objdto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY")); objdto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY")); objdto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE")); objdto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE")); objdto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME")); objdto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER")); objdto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID")); //[004] End //satyapan OSP/ISP objdto.setIsOspRequired(rs.getString("IS_OSP")); if (rs.getString("OSP_REG_NO") != null && !"".equals(rs.getString("OSP_REG_NO"))) { objdto.setOspRegistrationNo(rs.getString("OSP_REG_NO")); } else { objdto.setOspRegistrationNo(""); } objdto.setOspRegistrationDate(rs.getString("OSP_REG_DATE")); if (rs.getString("OSP_REG_DATE") != null && !"".equals(rs.getString("OSP_REG_DATE"))) { objdto.setOspRegistrationDate( (Utility.showDate_Report(new Date(rs.getTimestamp("OSP_REG_DATE").getTime()))) .toUpperCase()); } objdto.setIsIspRequired(rs.getString("ISP_TAGGING")); if (rs.getString("ISP_LIC_CTGRY") != null && !"".equals(rs.getString("ISP_LIC_CTGRY")) && !"0".equals(rs.getString("ISP_LIC_CTGRY"))) { objdto.setIspCatageory(rs.getString("ISP_LIC_CTGRY")); } else { objdto.setIspCatageory(""); } if (rs.getString("ISP_LIC_NO") != null && !"".equals(rs.getString("ISP_LIC_NO"))) { objdto.setIspLicenseNo(rs.getString("ISP_LIC_NO")); } else { objdto.setIspLicenseNo(""); } objdto.setIspLicenseDate(rs.getString("ISP_LIC_DATE")); if (rs.getString("ISP_LIC_DATE") != null && !"".equals(rs.getString("ISP_LIC_DATE"))) { objdto.setIspLicenseDate( (Utility.showDate_Report(new Date(rs.getTimestamp("ISP_LIC_DATE").getTime()))) .toUpperCase()); } //End of satyapan OSP/ISP //[007] Start objdto.setStandardReason(rs.getString("STANDARDREASON")); objdto.setLdClause(rs.getString("LDCLAUSE")); //[007] End //[008] Start objdto.setOrderCreationDate(rs.getString("CREATED_DATE")); if (rs.getString("CREATED_DATE") != null && !"".equals(rs.getString("CREATED_DATE"))) { objdto.setOrderCreationDate( (utility.showDate_Report5(new Date(rs.getTimestamp("CREATED_DATE").getTime()))) .toUpperCase()); } objdto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objdto.setPublishedDate( (utility.showDate_Report5(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } //[008] End //[130] start objdto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objdto.setChannelPartner(rs.getString("PARTNER_NAME")); objdto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objdto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objdto.setPartnerCode(rs.getString("PARTNER_CODE")); objdto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end // nancy start objdto.setePCNNo(rs.getString("EPCN_NO")); //nancy end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objdto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * /*from ww w.j a va2 s . c o m*/ * @param objDto * @return * @throws Exception */ public ArrayList<OrderDetailReportDTO> viewOrderReportDetails(OrderDetailReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewOrderReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<OrderDetailReportDTO> objUserList = new ArrayList<OrderDetailReportDTO>(); 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(sqlOrderReportDetail); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } //Company Name //proc.setNull(4, java.sql.Types.VARCHAR); // Logical Si no 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(5, objDto.getFromAccountNo()); proc.setLong(6, objDto.getToAccountNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(7, objDto.getFromOrderNo()); proc.setLong(8, objDto.getToOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); 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));// end // index if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(14, objDto.getOsp().trim()); } else { proc.setNull(14, java.sql.Types.VARCHAR); } if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate()) && objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) { proc.setString(15, objDto.getFromOrderDate()); proc.setString(16, objDto.getToOrderDate()); } else { proc.setNull(15, java.sql.Types.VARCHAR); proc.setNull(16, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OrderDetailReportDTO(); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setPoAmounts(rs.getDouble("POAMOUNT")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setBillingPODate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); DateFormat dformat2 = new SimpleDateFormat("dd-MM-yyyy"); Date date; if (objDto.getBillingPODate().length() > 10) { date = dformat.parse(objDto.getBillingPODate()); } else if (objDto.getBillingPODate().indexOf('-') != -1) { date = dformat2.parse(objDto.getBillingPODate()); } else { date = df.parse(objDto.getBillingPODate()); } objDto.setBillingPODate((utility.showDate_Report(date)).toUpperCase()); } objDto.setServiceDetDescription(rs.getString("SERVICESTAGE")); objDto.setOrderLineNumber(rs.getInt("SERVICEPRODUCTID")); objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objDto.setCancelflag(rs.getString("CANCELFLAG")); objDto.setProvisionBandWidth(rs.getString("BANDWIDTH")); objDto.setUom(rs.getString("UOM")); objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH")); objDto.setStoreName(rs.getString("STORENAME")); objDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setCategoryOfOrder(rs.getString("ORDERCATEGORY")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setCompanyName(rs.getString("COMPANYNAME")); objDto.setOrderDate(rs.getString("ORDERCREATIONDATE")); if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) { Date date = rs.getDate("ORDERCREATIONDATE"); objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerRfsDate(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd"); Date date; if (objDto.getCustomerRfsDate().indexOf('-') != -1) { date = dformat.parse(objDto.getCustomerRfsDate()); } else { date = df.parse(objDto.getCustomerRfsDate()); } objDto.setCustomerRfsDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerServiceRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objDto.getCustomerServiceRfsDate()); objDto.setCustomerServiceRfsDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setCustomerPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objDto.getCustomerPoDate()); objDto.setCustomerPoDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerPoNumber(rs.getString("CUSTPONUMBER")); objDto.setCyclicNonCyclic(rs.getString("CYCLIC_NONCYCLIC")); objDto.setChallenno(rs.getString("CHALLEN_NO")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setFromSite(rs.getString("PRIMARYLOCATION")); objDto.setFromSite(objDto.getFromSite() == null ? null : objDto.getFromSite().replaceAll("~", "")); objDto.setToSite(rs.getString("SECONDARYLOCATION")); objDto.setToSite(objDto.getToSite() == null ? null : objDto.getToSite().replaceAll("~", "")); objDto.setItemQuantity(1); objDto.setKmsDistance(rs.getString("DISTANCE")); objDto.setChargeAmount(rs.getDouble("LINEITEMAMOUNT")); 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.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION")); objDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objDto.getLocDate()); objDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setAmReceiveDate(rs.getString("AMRECEIVEDATE")); if (rs.getString("AMRECEIVEDATE") != null && !"".equals(rs.getString("AMRECEIVEDATE"))) { Date date = df.parse(objDto.getAmReceiveDate()); objDto.setAmReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setOrderTotal(rs.getDouble("POAMOUNT")); objDto.setOrderEntryDate(rs.getString("ORDERCREATIONDATE")); if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) { Date date = rs.getDate("ORDERCREATIONDATE"); objDto.setOrderEntryDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setLicenceCoName(rs.getString("LCOMPANYNAME")); objDto.setLogicalCircuitNumber(rs.getString("LOGICAL_CIRCUITID")); objDto.setOrderType(rs.getString("ORDERCATEGORY")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setProjectManager(rs.getString("PROJECTMANAGER")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setOldLineitemAmount(""); objDto.setDemoType(rs.getString("DEMO_TYPE")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderStageDescription(rs.getString("STAGE")); objDto.setServiceStageDescription(rs.getString("SERVICE_STAGE")); objDto.setChargeEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objDto.getChargeEndDate()); objDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objDto.setRemarks(rs.getString("REMARKS")); objDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE")); objDto.setOsp(rs.getString("OSP")); // [404040] Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End //<!--GlobalDataBillingEfficiency BFR6 --> objDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); //[130] start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //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
/** * Create a Report to generate LEPM Order Cancel Report //from ww w . ja v a 2s .co m * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<LempCancelOrderReportDTO> viewLEPMOrderCancelReport(LempCancelOrderReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewLEPMOrderCancelReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<LempCancelOrderReportDTO> objUserList = new ArrayList<LempCancelOrderReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; int countFlag = 0; LempCancelOrderReportDTO objReportsDto = null; DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); ArrayList<LempCancelOrderReportDTO> listSearchDetails = new ArrayList<LempCancelOrderReportDTO>(); try { //[101010]START conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlLEPMOrderCancelReport); if (objDto.getCanceldatefrom() != null && !"".equals(objDto.getCanceldatefrom())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getCanceldatefrom()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("yyyy-MM-dd"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getCanceldateto() != null && !"".equals(objDto.getCanceldateto())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getCanceldateto()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("yyyy-MM-dd"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } /*if (objDto.getCanceldatefrom() != null && !"".equals(objDto.getCanceldatefrom())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter.parse(objDto.getCanceldatefrom()); String formattedDate1 = formatter.format(dateStr1); proc.setString(1, formattedDate1); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getCanceldateto() != null && !"".equals(objDto.getCanceldateto())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr2 = formatter1.parse(objDto.getCanceldateto()); String formattedDate2 = formatter1.format(dateStr2); proc.setString(1, formattedDate2); proc.setString(2, objDto.getCanceldateto().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); }*/ //[101010]END PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); while (rs.next() != false) { objDto = new LempCancelOrderReportDTO(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setPrimarylocation(rs.getString("FROM_SITE")); objDto.setSeclocation(rs.getString("TO_SITE")); objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setLineItemAmount(rs.getString("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setPrjmgremail(rs.getString("PMEMAIL")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase()); } objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE")); if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) { objDto.setNio_approve_date( (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase()); } objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { Date date = df.parse(objDto.getRfs_date()); objDto.setRfs_date((Utility.showDate_Report(date)).toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setPartyid(rs.getString("PARTY_ID")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); if ("NULL".equals(rs.getString("SERVICETYPE"))) { objDto.setServiceType(""); } else { objDto.setServiceType(rs.getString("SERVICETYPE")); } objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { //[505053] start try { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } } catch (Exception exp) { //It's okay to ignore 'exp' here because setting a default value objDto.setFrom_city(" "); } //[505053] end } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { //[505053] start try { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } } catch (Exception exp) { //It's okay to ignore 'exp' here because setting a default value objDto.setTo_city(" "); } //[505053] end } else { objDto.setTo_city(""); } objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); //objDto.setAm_approval_remarks(rs.getString("AM_APPROVAL_TASK_REMARKS")); //objDto.setPm_approval_remarks(rs.getString("PM_APPROVAL_TASK_REMARKS")); //objDto.setCopc_approval_remarks(rs.getString("COPC_APPROVAL_TASK_REMARKS")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); //nagarjuna objDto.setCharge_Disconnection_Status(rs.getString("CHARGES_STATUS")); objDto.setSubchange_type(rs.getString("NAME_SUBTYPE")); //nagarjuna end objDto.setServiceStage(rs.getString("STAGE")); //[909090] objDto.setOrd_cancel_reason(rs.getString("ORD_CANCEL_REASON")); //Start [10990] objDto.setServ_cancel_reson(rs.getString("SERV_CANCEL_REASON")); objDto.setService_cancelledby(rs.getString("SERVICE_CANCELLEDBY")); objDto.setServ_cancel_remarks(rs.getString("SERV_CANCEL_REMARKS")); objDto.setService_cancl_date(rs.getString("SERVICE_CANCEL_DATE")); if (rs.getString("SERVICE_CANCEL_DATE") != null && !"".equals(rs.getString("SERVICE_CANCEL_DATE")))//service_cancel_Date for IB2B cancelation { objDto.setService_cancl_date( (Utility.showDate_Report((rs.getDate("SERVICE_CANCEL_DATE")))).toUpperCase()); } //end [10990] objDto.setCanceldate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCanceldate((Utility.showDate_Report((rs.getDate("CANCEL_DATE")))).toUpperCase()); } //lawkush Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //lawkush End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return listSearchDetails; }