List of usage examples for java.sql CallableStatement setNull
void setNull(String parameterName, int sqlType) throws SQLException;
NULL. From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<CopyCancelReportDTO> cancelCopyReport(CopyCancelReportDTO objDto) throws Exception { // Nagarjuna String methodName = "cancelCopyReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*ww w .ja v a 2s.com*/ ArrayList<CopyCancelReportDTO> objUserList = new ArrayList<CopyCancelReportDTO>(); 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(sqlCancelCopyReport); 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); //proc.setString(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getToDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getToDate()); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new CopyCancelReportDTO(); objDto.setOldOrderNo(rs.getInt("OLDORDERNO")); objDto.setNewOrderNo(rs.getInt("NEWORDERNO")); objDto.setRootOrderNo(rs.getInt("ROOTORDERNO")); objDto.setOldServiceNo(rs.getInt("OLDSERVICENO")); objDto.setNewServiceNo(rs.getInt("NEWSERVICENO")); objDto.setCreatedBy(rs.getString("CREATEDBY")); objDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { //Date date=df.parse(objDto.getCreatedDate()); //objDto.setCreatedDate((utility.showDate_Report(date)).toUpperCase()); objDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } 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<ArchivalReportDto> reportDraftOrder(ArchivalReportDto reportsDto) { String methodName = "reportDraftOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null;//from w w w .j a va 2 s .c o m ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportDto dto = null; Utility utility = new Utility(); SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetDraftReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_number()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getLine_it_no()); cstmt.setString(7, reportsDto.getCkt_id()); cstmt.setString(8, reportsDto.getM6orderno()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportDto(); dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER")); dto.setAccount_number(rs.getString("ACCOUNT_NUMBER")); dto.setPo_amount(rs.getDouble("PO_AMOUNT")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY")); dto.setVertical(rs.getString("VERTICAL")); dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE")); if (rs.getString("BILLING_CHARGE_START_DATE") != null && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) { dto.setBilling_charge_start_date( (utility.showDate_Report2(dto.getBilling_charge_start_date())).toUpperCase()); } dto.setLine_name(rs.getString("SERVICE_NAME")); dto.setOrder_line_no(rs.getString("ORDER_LINE_NO")); dto.setCharge_name(rs.getString("LINE_NAME")); dto.setCancel_flag(rs.getString("CANCEL_FLAG")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setUom(rs.getString("UOM")); dto.setBill_uom(rs.getString("BILL_UOM")); dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER")); dto.setContract_period(rs.getString("CONTRACT_PERIOD")); dto.setCompany_name(rs.getString("COMPANY_NAME")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) { dto.setCustomer_service_rfs_date( (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase()); } dto.setCurrency(rs.getString("CURRENCY")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE")); if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) { dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase()); } dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER")); dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC")); dto.setChallen_no(rs.getString("CHALLEN_NO")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setFrom_site(rs.getString("FROM_SITE")); dto.setTo_site(rs.getString("TO_SITE")); dto.setItem_quantity(rs.getString("ITEM_QUANTITY")); dto.setKms_distance(rs.getString("KMS_DISTANCE")); dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT")); dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { dto.setCopc_approved_date( utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))); } dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION")); dto.setLoc_date(rs.getString("LOC_Date")); if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) { dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase()); } dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE")); if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) { dto.setAccount_manager_receive_date( (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase()); } dto.setOrder_total(rs.getDouble("ORDER_TOTAL")); dto.setTaxation(rs.getString("TAXATION")); dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setPayment_term(rs.getString("PAYMENT_TERM")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setParty_name(rs.getString("PARTY_NAME")); dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) { dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase()); } dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK")); dto.setRemarks(rs.getString("REMARKS")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setOsp(rs.getString("OSP")); dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID")); dto.setStore_address(rs.getString("STORE_ADDRESS")); dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE")); if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) { dto.setCustomer_rfs_date((utility.showDate_Report2(dto.getCustomer_rfs_date())).toUpperCase()); } dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE")); if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) { dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase()); } dto.setCkt_id(rs.getString("CKT_ID")); dto.getInterfaceid(); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { System.out.println(">>>>>>>>>>>."); } } System.out.println("DTO_>>>>>>>>>>>>>>>end>>>>>>>>>>>>>"); return docListDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<StartChargeNotPushedInFXDTO> viewStartChargeNotPushedInFx(StartChargeNotPushedInFXDTO objDto) { // Nagarjuna String methodName = "viewStartChargeNotPushedInFx", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from ww w. ja v a2 s . c o m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<StartChargeNotPushedInFXDTO> listSearchDetails = new ArrayList<StartChargeNotPushedInFXDTO>(); StartChargeNotPushedInFXDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetStartChargeNotPushedInFx); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new StartChargeNotPushedInFXDTO(); objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); //objReportsDto.setDnd_Dispatch_But_Not_Delivered(rs.getString("Dnd_Dispatch_But_Not_Delivered")); //objReportsDto.setDnd_Dispatch_And_Delivered(rs.getString("Dnd_Dispatch_And_Delivered")); //objReportsDto.setDnd_Disp_Del_Not_Installed(rs.getString("Ddni_Disp_Del_Not_Installed")); //objReportsDto.setDnd_Disp_Delivered_Installed(rs.getString("Ddni_Disp_Delivered_Installed")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { objReportsDto.setStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { objReportsDto .setEndDate((utility.showDate_Report(new Date(rs.getTimestamp("END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { objReportsDto.setContractStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTSTARTDATE").getTime()))) .toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { objReportsDto.setContractEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTENDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo1(rs.getString("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setPonum1(rs.getString("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { objReportsDto.setPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFx_Ed_Chg_Status(rs.getString("CSTATE_FX_CHARGE_END_STATUS"));//Fx_Ed_Chg_Status objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setLOC_Date(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setLOC_Date(s5); } objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objReportsDto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setChallendate(s5); } objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { objReportsDto.setWarrantyStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { objReportsDto.setWarrantyEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { objReportsDto.setExtSuportEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("EXT_SUPPORT_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setServiceStageDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGESTYPE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE objReportsDto.setRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { objReportsDto.setRfsDate( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_RFS_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime()))) .toUpperCase()); } objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed //--Fx Status Ed objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objReportsDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(rs.getString("BILLING_ADDRESS")); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); objReportsDto.setCanceldate(rs.getString("CANCEL_DATE")); objReportsDto.setCancelReason(rs.getString("CANCEL_REASON")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setColl_Manager(rs.getString("COLL_MANAGER")); objReportsDto.setColl_Manager_Mail(rs.getString("COLL_MANAGER_MAIL")); objReportsDto.setColl_Manager_Phone(rs.getString("COLL_MANAGER_PHONE")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); //--CRM ORDER ID objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); //--Charge Hdr Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setChargeInfoID(rs.getInt("CHARGEINFOID")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); // --Cust Account id objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setStart_fx_no(rs.getString("START_DETAILS_FX_NO")); objReportsDto.setEnd_fx_no(rs.getString("END_DETAILS_FX_NO")); // --M6 Order Id // --Ib Order Line Id // --Ib Service List Id // --Ib Pk Charges Id // --Fx Sno // --Fx Sno Ed // --Cust Tot Po Amt // --M6 Order No // --Business Serial No // --Bus Serial // --Advance if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ArchivalReportDto> cancelledReportOrder(ArchivalReportDto reportsDto) { String methodName = "cancelledReportOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null;/*from ww w. j av a 2 s .c o m*/ ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportDto dto = null; Utility utility = new Utility(); SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetCancelledtReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_id()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getLine_it_no()); cstmt.setString(7, reportsDto.getCkt_id()); cstmt.setString(8, reportsDto.getM6orderno()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportDto(); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER")); dto.setAccount_number(rs.getString("ACCOUNT_NUMBER")); dto.setPo_amount(rs.getDouble("PO_AMOUNT")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY")); dto.setVertical(rs.getString("VERTICAL")); dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE")); if (rs.getString("BILLING_CHARGE_START_DATE") != null && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) { dto.setBilling_charge_start_date( (utility.showDate_Report(dto.getBilling_charge_start_date())).toUpperCase()); } dto.setService_name(rs.getString("SERVICE_NAME")); dto.setOrder_line_no(rs.getString("ORDER_LINE_NO")); dto.setLine_name(rs.getString("LINE_NAME")); dto.setCancel_flag(rs.getString("CANCEL_FLAG")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setUom(rs.getString("UOM")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setStore_address(rs.getString("STORE_ADDRESS")); dto.setBill_uom(rs.getString("BILL_UOM")); dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER")); dto.setContract_period(rs.getString("CONTRACT_PERIOD")); dto.setCompany_name(rs.getString("COMPANY_NAME")); dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE")); if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) { dto.setCustomer_rfs_date((utility.showDate_Report3(dto.getCustomer_rfs_date())).toUpperCase());// issuee gui } dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) { dto.setCustomer_service_rfs_date( (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase()); // issue GUI } dto.setCurrency(rs.getString("CURRENCY")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE")); if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) { dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase()); } dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER")); dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC")); dto.setChallen_no(rs.getString("CHALLEN_NO")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setFrom_site(rs.getString("FROM_SITE")); dto.setTo_site(rs.getString("TO_SITE")); dto.setItem_quantity(rs.getString("ITEM_QUANTITY")); dto.setKms_distance(rs.getString("KMS_DISTANCE")); dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT")); dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { dto.setCopc_approved_date( utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))); } dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION")); dto.setLoc_date(rs.getString("LOC_DATE")); if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) { dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase()); } dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE")); if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) { dto.setAccount_manager_receive_date( (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase()); } dto.setOrder_total(rs.getDouble("ORDER_TOTAL")); dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE")); if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) { dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase()); } dto.setTaxation(rs.getString("TAXATION")); dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setPayment_term(rs.getString("PAYMENT_TERM")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setParty_name(rs.getString("PARTY_NAME")); dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION")); dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) { dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase()); } dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK")); dto.setRemarks(rs.getString("REMARKS")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setOsp(rs.getString("OSP")); dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setCkt_id(rs.getString("CKT_ID")); dto.setM6orderno(rs.getString("M6_ORDER_NO")); dto.setService_id(rs.getString("SERVICEID")); dto.setOldLsi(rs.getString("OLD_LSI")); dto.setCancel_reason(rs.getString("CANCEL_REASON")); dto.setCancel_date(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { dto.setCancel_date((utility.showDate_Report2(dto.getCancel_date())).toUpperCase()); } dto.setProduct(rs.getString("PRODUCT")); dto.setSub_product(rs.getString("SUB_PRODUCT")); dto.setEffective_start_data(rs.getString("EFFECTIVE_START_DATA")); if (rs.getString("EFFECTIVE_START_DATA") != null && !"".equals(rs.getString("EFFECTIVE_START_DATA"))) { dto.setEffective_start_data( (utility.showDate_Report(dto.getEffective_start_data())).toUpperCase()); } dto.getInterfaceid(); docListDetails.add(dto); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { } } return docListDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<BillingTriggerDoneButFailedInFXDTO> viewBillingTriggerDoneButFailedInFX( BillingTriggerDoneButFailedInFXDTO objDto) { // Nagarjuna String methodName = "viewBillingTriggerDoneButFailedInFX", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*from w ww . ja v a2 s . c om*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<BillingTriggerDoneButFailedInFXDTO> listSearchDetails = new ArrayList<BillingTriggerDoneButFailedInFXDTO>(); BillingTriggerDoneButFailedInFXDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetBillingTriggerDoneButFailedInFX); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new BillingTriggerDoneButFailedInFXDTO(); objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); //objReportsDto.setDnd_Dispatch_But_Not_Delivered(rs.getString("Dnd_Dispatch_But_Not_Delivered")); //objReportsDto.setDnd_Dispatch_And_Delivered(rs.getString("Dnd_Dispatch_And_Delivered")); //objReportsDto.setDnd_Disp_Del_Not_Installed(rs.getString("Ddni_Disp_Del_Not_Installed")); //objReportsDto.setDnd_Disp_Delivered_Installed(rs.getString("Ddni_Disp_Delivered_Installed")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { objReportsDto.setStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { objReportsDto .setEndDate((utility.showDate_Report(new Date(rs.getTimestamp("END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { objReportsDto.setContractStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTSTARTDATE").getTime()))) .toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { objReportsDto.setContractEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("CONTRACTENDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo1(rs.getString("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setPonum1(rs.getString("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { objReportsDto.setPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("PODATE").getTime()))).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFx_Ed_Chg_Status(rs.getString("CSTATE_FX_CHARGE_END_STATUS"));//Fx_Ed_Chg_Status objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setLOC_Date(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setLOC_Date(s5); } objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objReportsDto.setBillingtrigger_createdate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setChallendate(s5); } objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { objReportsDto.setWarrantyStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_START_DATE").getTime()))) .toUpperCase()); } objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { objReportsDto.setWarrantyEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("WARRENTY_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { objReportsDto.setExtSuportEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("EXT_SUPPORT_END_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVED_DATE"));//Copc date if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setServiceStageDescription(rs.getString("SERVICE_ORDER_TYPE_DESC")); // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGESTYPE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE objReportsDto.setRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { objReportsDto.setRfsDate( (utility.showDate_Report(new Date(rs.getTimestamp("SERVICE_RFS_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PORECEIVEDATE").getTime()))) .toUpperCase()); } objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed //--Fx Status Ed objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objReportsDto.setCreatedDate(rs.getString("CREATEDDATE")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(rs.getString("BILLING_ADDRESS")); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); objReportsDto.setCanceldate(rs.getString("CANCEL_DATE")); objReportsDto.setCancelReason(rs.getString("CANCEL_REASON")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setColl_Manager(rs.getString("COLL_MANAGER")); objReportsDto.setColl_Manager_Mail(rs.getString("COLL_MANAGER_MAIL")); objReportsDto.setColl_Manager_Phone(rs.getString("COLL_MANAGER_PHONE")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); //--CRM ORDER ID objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); //--Charge Hdr Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setChargeInfoID(rs.getInt("CHARGEINFOID")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); // --Cust Account id objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setStart_fx_no(rs.getString("START_DETAILS_FX_NO")); objReportsDto.setEnd_fx_no(rs.getString("END_DETAILS_FX_NO")); // --M6 Order Id // --Ib Order Line Id // --Ib Service List Id // --Ib Pk Charges Id // --Fx Sno // --Fx Sno Ed // --Cust Tot Po Amt // --M6 Order No // --Business Serial No // --Bus Serial // --Advance if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ArchivalReportDto> pdReportOrder(ArchivalReportDto reportsDto) { String methodName = "pdReportOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null;// w w w .j av a 2 s .co m ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportBean dto = null; SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); Utility utility = new Utility(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetPDReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_id()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); //System.out.println("reportsDto.getLogical_si_no()>>>>"+reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getM6orderno()); cstmt.setString(7, reportsDto.getCircuit_id()); cstmt.setString(8, reportsDto.getOrder_line_no()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportBean(); dto.setAccount_mgr(rs.getString("ACCOUNT_MGR")); dto.setAccount_no(rs.getString("ACCOUNT_NO")); dto.setAmt(rs.getString("AMT")); dto.setAnnotation(rs.getString("ANNOTATION")); dto.setAnnual_rate(rs.getString("ANNUAL_RATE")); dto.setBandwidth(rs.getString("BANDWIDTH")); dto.setBandwidth_uom(rs.getString("BANDWIDTH_UOM")); dto.setBill_format(rs.getString("BILL_FORMAT")); dto.setBill_period(rs.getString("BILL_PERIOD")); dto.setBill_trg_Create_date(rs.getString("BILL_TRG_CREATE_DATE")); dto.setBill_type(rs.getString("BILL_TYPE")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setBilling_bandwidth_uom(rs.getString("BILLING_BANDWIDTH_UOM")); dto.setBilling_level(rs.getString("BILLING_LEVEL")); dto.setBilling_level_number(rs.getString("BILLING_LEVEL_NUMBER")); dto.setBilling_location_from(rs.getString("BILLING_LOCATION_FROM")); dto.setBilling_location_to(rs.getString("BILLING_LOCATION_TO")); dto.setBilling_mode(rs.getString("BILLING_MODE")); dto.setBilling_trig_flag(rs.getString("BILLING_TRIG_FLAG")); dto.setChallen_date(rs.getString("CHALLEN_DATE")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); dto.setCharge_hdr_id(rs.getString("CHARGE_HDR_ID")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCharge_start_date(rs.getString("CHARGE_START_DATE")); dto.setCharge_status(rs.getString("CHARGE_STATUS")); dto.setCharge_type(rs.getString("CHARGE_TYPE")); dto.setCharge_type_id(rs.getString("CHARGE_TYPE_ID")); dto.setChargeable_distance(rs.getString("CHARGEABLE_DISTANCE")); dto.setChild_acc_fx_status(rs.getString("CHILD_ACC_FX_STATUS")); dto.setChild_acc_no(rs.getString("CHILD_ACC_NO")); dto.setCircuit_id(rs.getString("CIRCUIT_ID")); dto.setCommitment_period(rs.getString("COMMITMENT_PERIOD")); dto.setContract_period_months(rs.getString("CONTRACT_PERIOD_MONTHS")); dto.setCopc_approval_date(rs.getString("COPC_APPROVAL_DATE")); dto.setCredit_period(rs.getString("CREDIT_PERIOD")); dto.setCurrency(rs.getString("CURRENCY")); dto.setCust_acc_id(rs.getString("CUST_ACC_ID")); dto.setCust_logical_si_no(rs.getString("CUST_LOGICAL_SI_NO")); dto.setCust_po_date(rs.getString("CUST_PO_DATE")); dto.setCust_po_number(rs.getString("CUST_PO_NUMBER")); dto.setCust_po_receive_date(rs.getString("CUST_PO_RECEIVE_DATE")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setDisconnection_remark(rs.getString("DISCONNECTION_REMARK")); dto.setEnd_date_days(rs.getString("END_DATE_DAYS")); dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setEnd_date_months(rs.getString("END_DATE_MONTHS")); dto.setForm_c_available(rs.getString("FORM_C_AVAILABLE")); dto.setFrequency(rs.getString("FREQUENCY")); dto.setHardware_flag(rs.getString("HARDWARE_FLAG")); dto.setHardware_type(rs.getString("HARDWARE_TYPE")); dto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); dto.setInv_amt(rs.getString("INV_AMT")); dto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); dto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); dto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); dto.setLegal_entity(rs.getString("LEGAL_ENTITY")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLoc_date(rs.getString("LOC_DATE")); dto.setLoc_number(rs.getString("LOC_NUMBER")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setM6_order_id(rs.getString("M6_ORDER_ID")); dto.setNature_of_sale(rs.getString("NATURE_OF_SALE")); dto.setNew_order_remarks(rs.getString("NEW_ORDER_REMARKS")); dto.setNotice_period(rs.getString("NOTICE_PERIOD")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setOrder_date(rs.getString("ORDER_DATE")); dto.setOrder_line_id(rs.getString("ORDER_LINE_ID")); dto.setOrder_month(rs.getString("ORDER_MONTH")); dto.setOrder_number(rs.getString("ORDER_NUMBER")); dto.setOrder_stage(rs.getString("ORDER_STAGE")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setParty(rs.getString("PARTY")); dto.setParty_id(rs.getString("PARTY_ID")); dto.setPenalty_clause(rs.getString("PENALTY_CLAUSE")); dto.setPeriod_in_month(rs.getString("PERIOD_IN_MONTH")); dto.setPk_chageges_id(rs.getString("PK_CHAGEGES_ID")); dto.setPm_prov_date(rs.getString("PM_PROV_DATE")); dto.setPo_date(rs.getString("PO_DATE")); dto.setPre_crm_order_id(rs.getString("PRE_CRM_ORDER_ID")); dto.setProduct(rs.getString("PRODUCT")); dto.setProduct_name(rs.getString("PRODUCT_NAME")); dto.setRate_code(rs.getString("RATE_CODE")); dto.setRegion(rs.getString("REGION")); dto.setRequest_received_date(rs.getString("REQUEST_RECEIVED_DATE")); dto.setSec_loc(rs.getString("SEC_LOC")); dto.setService_no(rs.getString("SERVICE_NO")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setService_stage(rs.getString("SERVICE_STAGE")); dto.setSr_number(rs.getString("SR_NUMBER")); dto.setStandard_reason(rs.getString("STANDARD_REASON")); dto.setStart_date_days(rs.getString("START_DATE_DAYS")); dto.setStart_date_logic(rs.getString("START_DATE_LOGIC")); dto.setStart_date_months(rs.getString("START_DATE_MONTHS")); dto.setStore(rs.getString("STORE")); dto.setSub_product(rs.getString("SUB_PRODUCT")); dto.setTaxation(rs.getString("TAXATION")); dto.setToken_no(rs.getString("TOKEN_NO")); dto.setTot_po_amt(rs.getString("TOT_PO_AMT")); dto.setTotal_amount(rs.getString("TOTAL_AMOUNT")); dto.setType_of_sale(rs.getString("TYPE_OF_SALE")); dto.setVertical(rs.getString("VERTICAL")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setProject_mgr_email(rs.getString("PROJECT_MGR_EMAIL")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setQuote_no(rs.getString("QUOTE_NO")); dto.setRatio(rs.getString("RATIO")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setRe_logged_lsi_no(rs.getString("RE_LOGGED_LSI_NO")); dto.setService_name(rs.getString("SERVICE_NAME")); dto.setService_number(rs.getString("SERVICE_NUMBER")); dto.setSub_product_type(rs.getString("SUB_PRODUCT_TYPE")); dto.setTo_city(rs.getString("TO_CITY")); dto.setTo_site(rs.getString("TO_SITE")); dto.setUom(rs.getString("UOM")); dto.setZone(rs.getString("ZONE")); dto.setDis_sr(rs.getString("DIS_SR")); dto.setDod(rs.getString("DOD")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setCkt_id(rs.getString("CKT_ID")); dto.setPackage_id(rs.getString("PACKAGE_ID")); dto.setPackage_name(rs.getString("PACKAGE_NAME")); dto.setComponentinfoid(rs.getString("COMPONENTINFOID")); dto.setComponent_id(rs.getString("COMPONENT_ID")); dto.setComponent_name(rs.getString("COMPONENT_NAME")); dto.setComponent_status(rs.getString("COMPONENT_STATUS")); dto.setComponent_start_logic(rs.getString("COMPONENT_START_LOGIC")); dto.setComponent_start_date(rs.getString("COMPONENT_START_DATE")); dto.setComponent_end_logic(rs.getString("COMPONENT_END_LOGIC")); dto.setComponent_end_date(rs.getString("COMPONENT_END_DATE")); dto.setComp_start_days(rs.getString("COMP_START_DAYS")); dto.setComp_start_days(rs.getString("COMP_START_MONTHS")); dto.setComp_start_months(rs.getString("COMP_END_MONTHS")); dto.setComp_end_days(rs.getString("COMP_END_DAYS")); dto.setComp_end_months(rs.getString("COMP_END_MONTHS")); dto.setComponent_type(rs.getString("COMPONENT_TYPE")); dto.setComponent_instance_id(rs.getString("COMPONENT_INSTANCE_ID")); dto.setStart_component_token_no(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setEnd_component_token_no(rs.getString("END_COMPONENT_TOKEN_NO")); dto.setHardware_type(rs.getString("HARDWARE_TYPE")); dto.setLink_type(rs.getString("LINK_TYPE")); dto.setTaxexcemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setRate_code(rs.getString("RATE_CODE")); dto.setPri_loc(rs.getString("PRI_LOC")); dto.setSub_change_type(rs.getString("SUB_CHANGE_TYPE")); dto.setCHALLEN_NO(rs.getString("CHALLEN_NO")); dto.setLINE_NAME(rs.getString("LINE_NAME")); dto.setBilling_trig_date(rs.getString("BILLING_TRIG_DATE")); dto.setDispatch_address(rs.getString("DISPATCH_ADDRESS")); dto.setPo_number(rs.getString("PO_NUMBER")); dto.getInterfaceId(); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { } } return docListDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO> viewNonMigAppUnappNewOrderDetails( NonMigratedAPP_UNAPPNewOrderDetailsDTO objDto) { // Nagarjuna String methodName = "viewNonMigAppUnappNewOrderDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/*from w w w . ja v a2 s.c om*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO> listSearchDetails = new ArrayList<NonMigratedAPP_UNAPPNewOrderDetailsDTO>(); NonMigratedAPP_UNAPPNewOrderDetailsDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetNonMigAppUnappNewOrderDetails); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getApprovalType() != null && !"".equals(objDto.getApprovalType())) { proc.setString(2, objDto.getApprovalType().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceOrderType() != null && !"".equals(objDto.getServiceOrderType())) { proc.setInt(3, Integer.parseInt(objDto.getServiceOrderType().trim())); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(4, objDto.getOrdermonth().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(5, objDto.getVerticalDetails().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(6, objDto.getServiceName().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(7, objDto.getFromOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(8, objDto.getToOrderNo()); } else { proc.setNull(8, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) { proc.setString(14, objDto.getOrderyear().trim()); } else { proc.setNull(14, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); /*while(rs.next()) { objReportsDto = new ReportsDto(); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//LineName objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setKmsDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setPrimaryLocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSecondaryLocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE"));//Type Of Sale objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PONUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date=df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date=df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date=df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date=df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date=df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((utility.showDate_Report(date)).toUpperCase()); } //Bill Trg Create Date objReportsDto.setPmApproveDate(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1=rs.getString("Pm_Prov_Date"); String s3=s1.substring(0,7); String s4=s1.substring(9,11); String s5=s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS //Business Serial No //Opms Account Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber //Order Month objReportsDto.setOrderStage(rs.getString("STAGE")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); }*/ while (rs.next()) { objReportsDto = new NonMigratedAPP_UNAPPNewOrderDetailsDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setM6OrderNo2(rs.getString("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICESTAGE")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETTYPE"));//LineName objReportsDto.setServiceOrderType(rs.getString("SERVICETYPE")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPENAME"));//Type Of Sale objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PODETAILNUMBER")); objReportsDto.setPoDate(rs.getString("PODATE")); if (!(rs.getString("PODATE") == null || rs.getString("PODATE") == "")) { //Date date=df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(rs.getString("PODATE"))).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("POAMOUNT")); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (!(rs.getString("PORECEIVEDATE") == null || rs.getString("PORECEIVEDATE") == "")) { //Date date=df.parse(objReportsDto.getPoRecieveDate()); objReportsDto.setPoRecieveDate( (utility.showDate_Report(rs.getString("PORECEIVEDATE"))).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (!(rs.getString("CUSTPODATE") == null || rs.getString("CUSTPODATE") == "")) { //Date date=df.parse(objReportsDto.getCustPoDate()); objReportsDto .setCustPoDate((utility.showDate_Report(rs.getString("CUSTPODATE"))).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("LOCDATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setLocDate(formattedDate); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("BILLINGTRIGGERDATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setBillingTriggerDate(formattedDate); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) { //Date date=df2.parse(objReportsDto.getBillingtrigger_createdate()); objReportsDto.setBillingtrigger_createdate( (utility.showDate_Report(rs.getString("BILLING_TRIGGER_CREATEDATE"))).toUpperCase()); } objReportsDto.setPmApproveDate(rs.getString("LOC_DATE")); if (rs.getString("LOC_DATE") != null && !"".equals(rs.getString("LOC_DATE"))) { String s1 = rs.getString("LOC_DATE"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_STATUS")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setKmsDistance(rs.getString("DISTANCE")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setTokenNO(rs.getString("START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setFx_St_Chg_Status(rs.getString("FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFxStatus(rs.getString("START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); //objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); //[707070] Start objReportsDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objReportsDto.setOpms_lineItemNumber(rs.getString("OPMS_LINEITEMNUMBER")); // [707070] End if (rs.getString("INV_AMT") != null && !"".equals(rs.getString("INV_AMT"))) { objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); //Double d2=Double.parseDouble(rs.getString("INV_AMT")); //objReportsDto.setChargeAmount_Double((Math.round(d2 * 100.0) / 100.0)); } else { objReportsDto.setChargeAmount_Double(0.0); } objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(rs.getString("START_DATE")); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("dd-MMM-yy"); formattedDate = formatter.format(date1); objReportsDto.setStartDate(formattedDate); } else { objReportsDto.setStartDate(""); } objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setOrderLineNumber(rs.getInt("LINENUMBER"));//Lineitemnumber objReportsDto.setOrdermonth(rs.getString("ORDERMONTH")); /*objReportsDto.setBlSource(rs.getString("BL_SOURCE")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID"));*/ objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); if (rs.getString("PRIMARYLOCATION") != null && !"".equals(rs.getString("PRIMARYLOCATION"))) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("PRIMARYLOCATION").split("~~"); objReportsDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objReportsDto.setPrimaryLocation(""); } if (rs.getString("SECONDARYLOCATION") != null && !"".equals(rs.getString("SECONDARYLOCATION"))) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setSecondaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("SECONDARYLOCATION").split("~~"); objReportsDto.setSecondaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objReportsDto.setSecondaryLocation(""); } objReportsDto.setOrderStage(rs.getString("STAGE")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<CancelledFailedLineReportDTO> viewCancelledFailedLineReport( CancelledFailedLineReportDTO objDto) { // Nagarjuna String methodName = "viewCancelledFailedLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end//from w w w . j a v a 2s . c om Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<CancelledFailedLineReportDTO> listSearchDetails = new ArrayList<CancelledFailedLineReportDTO>(); CancelledFailedLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat formatter2 = new SimpleDateFormat("MM-dd-yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetCancelledFailedLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { Date dateStr = formatter.parse(objDto.getFromDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { Date dateStr = formatter.parse(objDto.getToDate().trim()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formattedDate = formatter2.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim().toUpperCase()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(4, pagingSorting.getSortByColumn());// columnName proc.setString(5, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(6, pagingSorting.getStartRecordId());// start index proc.setInt(7, pagingSorting.getEndRecordId());// end index proc.setInt(8, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new CancelledFailedLineReportDTO(); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS")); objReportsDto.setLocation_to(rs.getString("TO_ADDRESS")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(BigDecimal.valueOf((rs.getDouble("TOTALPOAMOUNT"))).toPlainString()); if (objReportsDto.getTotalPoAmt() == null) { objReportsDto.setTotalPoAmt(" "); } objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } //objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE")); //objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto .setFrequencyAmt(BigDecimal.valueOf((rs.getDouble("CHARGEFREQUENCYAMT"))).toPlainString()); if (objReportsDto.getFrequencyAmt() == null) { objReportsDto.setFrequencyAmt(" "); } //objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); //objReportsDto.setAmt(rs.getLong("CHARGEAMOUNT"));//particular charge amount objReportsDto.setLineItemAmount(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objReportsDto.getLineItemAmount() == null) { objReportsDto.setLineItemAmount(" "); } objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setFx_status(rs.getString("CSTATE_START_DETAILS_FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("CSTATE_FX_CHARGE_START_STATUS")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (!(rs.getString("ORDERDATE") == null || rs.getString("ORDERDATE") == "")) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } //objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); //objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id")); objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block // e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<RestPendingLineReportDTO> viewRestPendingLineReport(RestPendingLineReportDTO objDto) { // Nagarjuna String methodName = "viewRestPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end //from ww w .j a v a2s .co m Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<RestPendingLineReportDTO> listSearchDetails = new ArrayList<RestPendingLineReportDTO>(); RestPendingLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetRestPendingLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(objDto.getFromDate()); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(1, formattedDate); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(2, formattedDate1); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getSubChangeTypeName() != null && !"".equals(objDto.getSubChangeTypeName())) { proc.setString(4, objDto.getSubChangeTypeName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(5, objDto.getVerticalDetails().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(6, pagingSorting.getSortByColumn());// columnName proc.setString(7, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(8, pagingSorting.getStartRecordId());// start index proc.setInt(9, pagingSorting.getEndRecordId());// end index proc.setInt(10, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new RestPendingLineReportDTO(); objReportsDto.setAccountID(rs.getInt("ACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountManager(rs.getString("ACTMNAME")); objReportsDto.setProjectManager(rs.getString("PMNAME")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objReportsDto.setChargeEndDate(rs.getString("CHARGE_START_DATE")); if (rs.getString("CHARGE_START_DATE") != null && !"".equals(rs.getString("CHARGE_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objReportsDto.getPoDate()); objReportsDto.setPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAmapprovaldate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objReportsDto.setAmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objReportsDto.setPmapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderDate(rs.getString("ORDERCREATEDATE")); if (rs.getString("ORDERCREATEDATE") != null && !"".equals(rs.getString("ORDERCREATEDATE"))) { Date date = df.parse(objReportsDto.getOrderDate()); objReportsDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { Date date = df.parse(objReportsDto.getCustPoDate()); objReportsDto.setCustPoDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPoReceiveDate(rs.getString("CUSTPORECDATE")); if (rs.getString("CUSTPORECDATE") != null && !"".equals(rs.getString("CUSTPORECDATE"))) { Date date = df.parse(objReportsDto.getPoReceiveDate()); objReportsDto.setPoReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setCanceldate(rs.getString("CANCELDATE")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGE_TYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setBillingMode(rs.getString("BILLMODE_NAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); objReportsDto.setCancelflag(rs.getString("CANCELBY")); objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); objReportsDto.setAddress1(rs.getString("ADDRESS")); objReportsDto.setFx_status(rs.getString("FX_STATUS")); objReportsDto.setFx_sd_status(rs.getString("Fx_St_Chg_Status")); objReportsDto.setFx_ed_status(rs.getString("Fx_Ed_Chg_Status")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objReportsDto.getRfs_date()); objReportsDto.setRfs_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setLineno(rs.getInt("LINEITEMNO")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setLocation_from(rs.getString("FROM_LOCATION")); objReportsDto.setLocation_to(rs.getString("TO_LOCATION")); objReportsDto.setServiceStage(rs.getString("SERVICESTAGE")); objReportsDto.setBisource(rs.getString("BISOURCE")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setTokenno(rs.getString("TOKENNO")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setSaleNature(rs.getString("SALENATURENAME")); objReportsDto.setDemoType(rs.getString("Demo_Type")); objReportsDto.setSaleType(rs.getString("SALETYPENAME")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACT_ID")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setStageName(rs.getString("ORDERSTAGE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO")); objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setPoAmountSum(rs.getLong("ORDERAMOUNT")); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setParty_id(rs.getInt("PARTY_ID")); objReportsDto.setCrmAccountNo(rs.getInt("CRMACCOUNTNO")); objReportsDto.setM6cktid(rs.getString("M6_PRODUCT_ID")); objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID")); objReportsDto.setLb_pk_charge_id(rs.getString("LB_PK_CHARGE_ID")); objReportsDto.setChargeinfoID(rs.getString("PK_CHARGE_ID")); objReportsDto.setAnnual_rate(rs.getInt("ANNUAL_RATE")); objReportsDto.setBandWidth((rs.getString("BANDWIDTH"))); //[007] Start objReportsDto.setStandardReason(rs.getString("STANDARDREASON")); //[007] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
/** * Create a Report to generate LEPM Order Cancel Report //from w w w . ja v a 2 s .co m * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<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; }