List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<CancelledFailedLineReportDTO> viewCancelledFailedLineReport( CancelledFailedLineReportDTO objDto) { // Nagarjuna String methodName = "viewCancelledFailedLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<CancelledFailedLineReportDTO> listSearchDetails = new ArrayList<CancelledFailedLineReportDTO>(); CancelledFailedLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null;//from w ww . j a v a2s . co m Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetCancelledFailedLineReportsForUsage); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(2, objDto.getToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } 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)); proc.setInt(9, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); 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")); // change objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); objReportsDto.setSeclocation(VAR_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.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.setBill_period(rs.getString("BILL_PERIOD")); if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBill_period(tBillPeriod); } objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); // change tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); // change tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setPoReceiveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); // change tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID")); // Saurabh : Changes to separate charge related specific column from common if (objDto.getIsUsage() == 0) { objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE")); 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.setLineamt(rs.getLong("LINEITEMAMOUNT")); 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.setBusiness_serial_no(rs.getString("Business_No")); objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id")); } //Meenakshi : Changes for Usage if (objDto.getIsUsage() == 1) { objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); objReportsDto.setComponentDto(dto); } /// 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:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the CollectionObject and CollectionObjectCatalog Records into the new schema * CollectionObject table. All "logical" records are moved to the CollectionObject table and all * "physical" records are moved to the Preparation table. * @return true if no errors//from w ww. ja v a2s.co m */ @SuppressWarnings("cast") public boolean convertCollectionObjects(final boolean useNumericCatNumbers, final boolean usePrefix) { final String ZEROES = "000000000"; UIFieldFormatterIFace formatter0 = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumber"); log.debug(formatter0); UIFieldFormatterIFace formatter = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumberNumeric"); log.debug(formatter); DisciplineType dt; Discipline discipline = (Discipline) AppContextMgr.getInstance().getClassObject(Discipline.class); if (discipline != null) { System.out.println("discipline.getType()[" + discipline.getType() + "]"); dt = DisciplineType.getDiscipline(discipline.getType()); } else { Vector<Object[]> list = query(newDBConn, "SELECT Type FROM discipline"); String typeStr = (String) list.get(0)[0]; System.out.println("typeStr[" + typeStr + "]"); dt = DisciplineType.getDiscipline(typeStr); } Pair<Integer, Boolean> objTypePair = dispToObjTypeHash.get(dt.getDisciplineType()); if (objTypePair == null) { System.out.println("objTypePair is null dt[" + dt.getName() + "][" + dt.getTitle() + "]"); for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) { Pair<Integer, Boolean> p = dispToObjTypeHash.get(key); System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]"); } } else if (objTypePair.first == null) { System.out.println("objTypePair.first is null dt[" + dt + "]"); for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) { Pair<Integer, Boolean> p = dispToObjTypeHash.get(key); System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]"); } } //int objTypeId = objTypePair.first; //boolean isEmbedded = objTypePair.second; idMapperMgr.dumpKeys(); IdHashMapper colObjTaxonMapper = (IdHashMapper) idMapperMgr.get("ColObjCatToTaxonType".toLowerCase()); IdHashMapper colObjAttrMapper = (IdHashMapper) idMapperMgr .get("biologicalobjectattributes_BiologicalObjectAttributesID"); IdHashMapper colObjMapper = (IdHashMapper) idMapperMgr .get("collectionobjectcatalog_CollectionObjectCatalogID"); colObjTaxonMapper.setShowLogErrors(false); // NOTE: TURN THIS ON FOR DEBUGGING or running new Databases through it colObjAttrMapper.setShowLogErrors(false); //IdHashMapper stratMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_StratigraphyID"); //IdHashMapper stratGTPMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_GeologicTimePeriodID"); String[] fieldsToSkip = { "ContainerID", "ContainerItemID", "AltCatalogNumber", "GUID", "ContainerOwnerID", "RepositoryAgreementID", "GroupPermittedToView", // this may change when converting Specify 5.x "CollectionObjectID", "VisibilitySetBy", "ContainerOwnerID", "InventoryDate", "ObjectCondition", "Notifications", "ProjectNumber", "Restrictions", "YesNo3", "YesNo4", "YesNo5", "YesNo6", "FieldNotebookPageID", "ColObjAttributesID", "DNASequenceID", "AppraisalID", "TotalValue", "Description", "SGRStatus", "OCR", "ReservedText", "Text3" }; HashSet<String> fieldsToSkipHash = new HashSet<String>(); for (String fName : fieldsToSkip) { fieldsToSkipHash.add(fName); } TableWriter tblWriter = convLogger.getWriter("convertCollectionObjects.html", "Collection Objects"); String msg = "colObjTaxonMapper: " + colObjTaxonMapper.size(); log.info(msg); tblWriter.log(msg); setIdentityInsertONCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); // automatically closes the connection TreeSet<String> badSubNumberCatNumsSet = new TreeSet<String>(); TimeLogger timeLogger = new TimeLogger(); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); StringBuilder str = new StringBuilder(); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("select "); List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject"); sql.append(buildSelectFieldList(names, "collectionobject")); sql.append(", "); oldFieldNames.addAll(names); names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog"); sql.append(buildSelectFieldList(names, "collectionobjectcatalog")); oldFieldNames.addAll(names); String fromClause = " FROM collectionobject Inner Join collectionobjectcatalog ON " + "collectionobject.CollectionObjectID = collectionobjectcatalog.CollectionObjectCatalogID " + "WHERE (collectionobject.DerivedFromID IS NULL) AND collectionobjectcatalog.CollectionObjectCatalogID = "; sql.append(fromClause); log.info(sql); String sqlStr = sql.toString(); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "collectionobject"); log.info("Number of Fields in New CollectionObject " + newFieldMetaData.size()); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; log.info("---- Old Names ----"); for (String name : oldFieldNames) { log.info("[" + name + "][" + inx + "]"); oldNameIndex.put(name, inx++); } log.info("---- New Names ----"); for (FieldMetaData fmd : newFieldMetaData) { log.info("[" + fmd.getName() + "]"); } String tableName = "collectionobject"; Statement newStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); newStmt.setFetchSize(Integer.MIN_VALUE); ResultSet rsLooping = newStmt.executeQuery( "SELECT OldID, NewID FROM collectionobjectcatalog_CollectionObjectCatalogID ORDER BY OldID"); if (hasFrame) { if (rsLooping.last()) { setProcess(0, rsLooping.getRow()); rsLooping.first(); } else { rsLooping.close(); stmt.close(); return true; } } else { if (!rsLooping.first()) { rsLooping.close(); stmt.close(); return true; } } int boaCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM biologicalobjectattributes"); // ZZZ PartialDateConv partialDateConv = new PartialDateConv(); Statement stmt2 = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt2.setFetchSize(Integer.MIN_VALUE); int catNumInx = oldNameIndex.get("CatalogNumber"); int catDateInx = oldNameIndex.get("CatalogedDate"); int catSeriesIdInx = oldNameIndex.get("CatalogSeriesID"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); /*int grpPrmtViewInx = -1; Integer grpPrmtViewInxObj = oldNameIndex.get("GroupPermittedToView"); if (grpPrmtViewInxObj != null) { grpPrmtViewInx = grpPrmtViewInxObj + 1; }*/ Hashtable<Integer, CollectionInfo> oldCatSeriesIDToCollInfo = new Hashtable<Integer, CollectionInfo>(); for (CollectionInfo ci : collectionInfoShortList) { if (ci.getCatSeriesId() != null) { oldCatSeriesIDToCollInfo.put(ci.getCatSeriesId(), ci); } } String insertStmtStr = null; /*String catIdTaxIdStrBase = "SELECT cc.CollectionObjectCatalogID, cc.CatalogSeriesID, ct.TaxonomyTypeID " + "FROM collectionobjectcatalog AS cc " + "Inner Join collectionobject AS co ON cc.CollectionObjectCatalogID = co.CollectionObjectID " + "Inner Join collectiontaxonomytypes as ct ON co.CollectionObjectTypeID = ct.BiologicalObjectTypeID " + "where cc.CollectionObjectCatalogID = ";*/ int colObjAttrsNotMapped = 0; int count = 0; boolean skipRecord = false; do { String catSQL = sqlStr + rsLooping.getInt(1); //log.debug(catSQL); ResultSet rs = stmt.executeQuery(catSQL); if (!rs.next()) { log.error("Couldn't find CO with old id[" + rsLooping.getInt(1) + "] " + catSQL); continue; } partialDateConv.nullAll(); skipRecord = false; CollectionInfo collInfo = oldCatSeriesIDToCollInfo.get(rs.getInt(catSeriesIdInx)); /*String catIdTaxIdStr = catIdTaxIdStrBase + rs.getInt(1); //log.info(catIdTaxIdStr); ResultSet rs2 = stmt2.executeQuery(catIdTaxIdStr); if (!rs2.next()) { log.info("QUERY failed to return results:\n"+catIdTaxIdStr+"\n"); continue; } Integer catalogSeriesID = rs2.getInt(2); Integer taxonomyTypeID = rs2.getInt(3); Integer newCatSeriesId = collectionHash.get(catalogSeriesID + "_" + taxonomyTypeID); String prefix = prefixHash.get(catalogSeriesID + "_" + taxonomyTypeID); rs2.close(); if (newCatSeriesId == null) { msg = "Can't find " + catalogSeriesID + "_" + taxonomyTypeID; log.info(msg); tblWriter.logError(msg); continue; }*/ /*if (false) { String stratGTPIdStr = "SELECT co.CollectionObjectID, ce.CollectingEventID, s.StratigraphyID, g.GeologicTimePeriodID FROM collectionobject co " + "LEFT JOIN collectingevent ce ON co.CollectingEventID = ce.CollectingEventID " + "LEFT JOIN stratigraphy s ON ce.CollectingEventID = s.StratigraphyID " + "LEFT JOIN geologictimeperiod g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID " + "WHERE co.CollectionObjectID = " + rs.getInt(1); log.info(stratGTPIdStr); rs2 = stmt2.executeQuery(stratGTPIdStr); Integer coId = null; Integer ceId = null; Integer stId = null; Integer gtpId = null; if (rs2.next()) { coId = rs2.getInt(1); ceId = rs2.getInt(2); stId = rs2.getInt(3); gtpId = rs2.getInt(4); } rs2.close(); }*/ String catalogNumber = null; String colObjId = null; str.setLength(0); if (insertStmtStr == null) { StringBuffer fieldList = new StringBuffer(); fieldList.append("( "); for (int i = 0; i < newFieldMetaData.size(); i++) { if ((i > 0) && (i < newFieldMetaData.size())) { fieldList.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName + " "); } fieldList.append(")"); insertStmtStr = "INSERT INTO collectionobject " + fieldList + " VALUES ("; } str.append(insertStmtStr); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { str.append(", "); } String newFieldName = newFieldMetaData.get(i).getName(); if (i == 0) { Integer oldColObjId = rs.getInt(1); Integer newColObjId = colObjMapper.get(oldColObjId); if (newColObjId == null) { msg = "Couldn't find new ColObj Id for old [" + oldColObjId + "]"; tblWriter.logError(msg); showError(msg); throw new RuntimeException(msg); } colObjId = getStrValue(newColObjId); if (contains(colObjId, '.')) { String msgStr = String.format("CatalogNumber '%d' contains a decimal point.", colObjId); log.debug(msgStr); tblWriter.logError(msgStr); skipRecord = true; break; } str.append(colObjId); if (useNumericCatNumbers) { catalogNumber = rs.getString(catNumInx); if (catalogNumber != null) { int catNumInt = (int) Math.abs(rs.getDouble(catNumInx)); catalogNumber = Integer.toString(catNumInt); if (catalogNumber.length() > 0 && catalogNumber.length() < ZEROES.length()) { catalogNumber = "\"" + ZEROES.substring(catalogNumber.length()) + catalogNumber + "\""; } else if (catalogNumber.length() > ZEROES.length()) { showError( "Catalog Number[" + catalogNumber + "] is too long for formatter of 9"); } } else { String mssg = "Empty catalog number."; log.debug(mssg); //showError(msg); tblWriter.logError(mssg); } } else { String prefix = collInfo.getCatSeriesPrefix(); float catNum = rs.getFloat(catNumInx); catalogNumber = "\"" + (usePrefix && isNotEmpty(prefix) ? (prefix + "-") : "") + String.format("%9.0f", catNum).trim() + "\""; } int subNumber = rs.getInt(oldNameIndex.get("SubNumber")); if (subNumber < 0 || rs.wasNull()) { badSubNumberCatNumsSet.add(catalogNumber); skipRecord = true; //msg = "Collection Object is being skipped because SubNumber is less than zero CatalogNumber["+ catalogNumber + "]"; //log.error(msg); //tblWriter.logError(msg); //showError(msg); break; } } else if (fieldsToSkipHash.contains(newFieldName)) { str.append("NULL"); } else if (newFieldName.equals("CollectionID")) // User/Security changes { str.append(collInfo.getCollectionId()); } else if (newFieldName.equals("Version")) // User/Security changes { str.append("0"); } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes { str.append(getCreatorAgentId(null)); } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes { String lastEditedByStr = rs.getString(lastEditedByInx); str.append(getModifiedByAgentId(lastEditedByStr)); } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes { str.append(collInfo.getCollectionId()); } else if (newFieldName.equals("PaleoContextID")) { str.append("NULL");// newCatSeriesId); } else if (newFieldName.equals("CollectionObjectAttributeID")) // User/Security changes { Object idObj = rs.getObject(1); if (idObj != null) { Integer coId = rs.getInt(1); Integer newId = colObjAttrMapper.get(coId); if (newId != null) { str.append(getStrValue(newId)); } else { if (boaCnt > 0) colObjAttrsNotMapped++; str.append("NULL"); } } else { str.append("NULL"); } } else if (newFieldName.equals("CatalogedDate")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getDateStr()); } else if (newFieldName.equals("CatalogedDatePrecision")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getPartial()); } else if (newFieldName.equals("CatalogedDateVerbatim")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(catDateInx), partialDateConv); } str.append(partialDateConv.getVerbatim()); } else if (newFieldName.equals("Availability")) { str.append("NULL"); } else if (newFieldName.equals("CatalogNumber")) { str.append(catalogNumber); } else if (newFieldName.equals("Visibility")) // User/Security changes { //str.append(grpPrmtViewInx > -1 ? rs.getObject(grpPrmtViewInx) : "NULL"); str.append("0"); } else if (newFieldName.equals("VisibilitySetByID")) // User/Security changes { str.append("NULL"); } else if (newFieldName.equals("CountAmt")) { Integer index = oldNameIndex.get("Count1"); if (index == null) { index = oldNameIndex.get("Count"); } Object countObj = rs.getObject(index); if (countObj != null) { str.append(getStrValue(countObj, newFieldMetaData.get(i).getType())); } else { str.append("NULL"); } } else { Integer index = oldNameIndex.get(newFieldName); Object data; if (index == null) { msg = "convertCollectionObjects - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // tblWriter.logError(msg); // showError(msg); data = null; // for (String key : oldNameIndex.keySet()) // { // log.info("["+key+"]["+oldNameIndex.get(key)+"]"); // } //stmt.close(); //throw new RuntimeException(msg); } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { IdMapperIFace idMapper = idMapperMgr.get(tableName, newFieldName); if (idMapper != null) { Integer origValue = rs.getInt(index); data = idMapper.get(origValue); if (data == null) { msg = "No value [" + origValue + "] in map [" + tableName + "][" + newFieldName + "]"; log.error(msg); tblWriter.logError(msg); //showError(msg); } } else { msg = "No Map for [" + tableName + "][" + newFieldName + "]"; log.error(msg); tblWriter.logError(msg); //showError(msg); } } } str.append(getStrValue(data, newFieldMetaData.get(i).getType())); } } if (!skipRecord) { str.append(")"); // log.info("\n"+str.toString()); if (hasFrame) { if (count % 500 == 0) { setProcess(count); } if (count % 5000 == 0) { log.info("CollectionObject Records: " + count); } } else { if (count % 2000 == 0) { log.info("CollectionObject Records: " + count); } } try { Statement updateStatement = newDBConn.createStatement(); if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) { removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); } // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0"); //if (count < 50) System.err.println(str.toString()); updateStatement.executeUpdate(str.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; } catch (SQLException e) { log.error("Count: " + count); log.error("Key: [" + colObjId + "][" + catalogNumber + "]"); log.error("SQL: " + str.toString()); e.printStackTrace(); log.error(e); showError(e.getMessage()); rs.close(); stmt.close(); throw new RuntimeException(e); } count++; } else { tblWriter.logError("Skipping - CatNo:" + catalogNumber); } // if (count > 10) break; rs.close(); } while (rsLooping.next()); /*if (boaCnt > 0) { msg = "CollectionObjectAttributes not mapped: " + colObjAttrsNotMapped + " out of "+boaCnt; log.info(msg); tblWriter.logError(msg); }*/ stmt2.close(); if (hasFrame) { setProcess(count); } else { log.info("Processed CollectionObject " + count + " records."); } tblWriter.log(String.format("Collection Objects Processing Time: %s", timeLogger.end())); tblWriter.log("Processed CollectionObject " + count + " records."); rsLooping.close(); newStmt.close(); stmt.close(); tblWriter.append( "<br><br><b>Catalog Numbers rejected because the SubNumber was NULL or less than Zero</b><br>"); tblWriter.startTable(); tblWriter.logHdr("Catalog Number"); for (String catNum : badSubNumberCatNumsSet) { tblWriter.log(catNum); } tblWriter.endTable(); } catch (SQLException e) { setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); e.printStackTrace(); log.error(e); tblWriter.logError(e.getMessage()); showError(e.getMessage()); throw new RuntimeException(e); } finally { tblWriter.close(); } setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); return true; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ZeroOrderValueReportDTO> viewZeroOrderValueReportDetails(ZeroOrderValueReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewZeroOrderValueReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from w w w . j a va 2 s . c o m*/ ArrayList<ZeroOrderValueReportDTO> objUserList = new ArrayList<ZeroOrderValueReportDTO>(); 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(sqlZeroOrdervalueReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(4, objDto.getFromAccountNo()); proc.setLong(5, objDto.getToAccountNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(6, objDto.getFromOrderNo()); proc.setLong(7, objDto.getToOrderNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getCustPoDetailNo() != null && !"".equals(objDto.getCustPoDetailNo())) { proc.setString(8, objDto.getCustPoDetailNo().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new ZeroOrderValueReportDTO(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setPoDetailNo(rs.getString("PODETAILNUMBER")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setEntityId(rs.getString("ENTITYCODE")); objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setPoAmounts(rs.getDouble("POAMOUNT")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { Date date = df.parse(objDto.getContractStartDate()); objDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objDto.getContractEndDate()); objDto.setContractEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { Date date = df.parse(objDto.getPoRecieveDate()); objDto.setPoRecieveDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setPoIssueBy(rs.getString("ISSUEDBY")); objDto.setPoEmailId(rs.getString("EMAILID")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setLineItemDescription(rs.getString("SERVICEPRODUCTID")); objDto.setRegionName(rs.getString("REGION")); objDto.setChargeAnnotation(rs.getString("ANNOTATION")); objDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objDto.setFromLocation(rs.getString("FROM_LOCATION")); objDto.setToLocation(rs.getString("TO_LOCATION")); objDto.setServiceId(rs.getInt("SERVICEID")); objDto.setPoDemoContractPeriod(rs.getString("DEMOCONTRACTPERIOD")); objDto.setIsDefaultPO(rs.getInt("ISDEFAULTPO")); objDto.setCreatedBy(rs.getString("CREATEDBY")); objDto.setCreatedDate(rs.getString("CREATEDDATE")); objDto.setOldLsi(rs.getString("OLD_LSI_CRM")); objDto.setBusiness_serial_n(rs.getInt("BUSINESS_SERIAL_NO")); objDto.setMocn_no(rs.getString("MOCN_NUMBER")); if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) { objDto.setCreatedDate( (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime()))) .toUpperCase()); } objDto.setUom(rs.getString("UOM")); objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH")); objDto.setOrder_type(rs.getString("ORDER_TYPE")); objDto.setFxSiId(rs.getString("FX_SI_ID")); objDto.setSourceName("UNMIGRATED"); objDto.setTokenNO(rs.getString("TOKEN_NO")); objDto.setPoEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objDto.getPoEndDate()); objDto.setPoEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setLastUpdatedDate(""); objDto.setLastUpdatedBy(""); 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
/** * // w w w . ja va 2 s. c om * @param objDto * @return * @throws Exception */ public ArrayList<OrderDetailReportDTO> viewOrderReportDetails(OrderDetailReportDTO objDto) throws Exception { // Nagarjuna String methodName = "viewOrderReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end ArrayList<OrderDetailReportDTO> objUserList = new ArrayList<OrderDetailReportDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOrderReportDetail); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } //Company Name //proc.setNull(4, java.sql.Types.VARCHAR); // Logical Si no if (objDto.getDemo() != null && !"".equals(objDto.getDemo())) { proc.setString(4, objDto.getDemo().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && objDto.getToAccountNo() != 0) { proc.setLong(5, objDto.getFromAccountNo()); proc.setLong(6, objDto.getToAccountNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getFromOrderNo() != 0 && objDto.getToOrderNo() != 0) { proc.setLong(7, objDto.getFromOrderNo()); proc.setLong(8, objDto.getToOrderNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); proc.setNull(8, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(14, objDto.getOsp().trim()); } else { proc.setNull(14, java.sql.Types.VARCHAR); } if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate()) && objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) { proc.setString(15, objDto.getFromOrderDate()); proc.setString(16, objDto.getToOrderDate()); } else { proc.setNull(15, java.sql.Types.VARCHAR); proc.setNull(16, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OrderDetailReportDTO(); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objDto.setPoAmounts(rs.getDouble("POAMOUNT")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setBillingPODate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); DateFormat dformat2 = new SimpleDateFormat("dd-MM-yyyy"); Date date; if (objDto.getBillingPODate().length() > 10) { date = dformat.parse(objDto.getBillingPODate()); } else if (objDto.getBillingPODate().indexOf('-') != -1) { date = dformat2.parse(objDto.getBillingPODate()); } else { date = df.parse(objDto.getBillingPODate()); } objDto.setBillingPODate((utility.showDate_Report(date)).toUpperCase()); } objDto.setServiceDetDescription(rs.getString("SERVICESTAGE")); objDto.setOrderLineNumber(rs.getInt("SERVICEPRODUCTID")); objDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objDto.setCancelflag(rs.getString("CANCELFLAG")); objDto.setProvisionBandWidth(rs.getString("BANDWIDTH")); objDto.setUom(rs.getString("UOM")); objDto.setBillingBandWidth(rs.getString("BILLING_BANDWIDTH")); objDto.setStoreName(rs.getString("STORENAME")); objDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setCategoryOfOrder(rs.getString("ORDERCATEGORY")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setCompanyName(rs.getString("COMPANYNAME")); objDto.setOrderDate(rs.getString("ORDERCREATIONDATE")); if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) { Date date = rs.getDate("ORDERCREATIONDATE"); objDto.setOrderDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerRfsDate(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd"); Date date; if (objDto.getCustomerRfsDate().indexOf('-') != -1) { date = dformat.parse(objDto.getCustomerRfsDate()); } else { date = df.parse(objDto.getCustomerRfsDate()); } objDto.setCustomerRfsDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerServiceRfsDate(rs.getString("SERVICE_RFS_DATE")); if (rs.getString("SERVICE_RFS_DATE") != null && !"".equals(rs.getString("SERVICE_RFS_DATE"))) { Date date = df.parse(objDto.getCustomerServiceRfsDate()); objDto.setCustomerServiceRfsDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setCustomerPoDate(rs.getString("PODATE")); if (rs.getString("PODATE") != null && !"".equals(rs.getString("PODATE"))) { Date date = df.parse(objDto.getCustomerPoDate()); objDto.setCustomerPoDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setCustomerPoNumber(rs.getString("CUSTPONUMBER")); objDto.setCyclicNonCyclic(rs.getString("CYCLIC_NONCYCLIC")); objDto.setChallenno(rs.getString("CHALLEN_NO")); objDto.setOrderNumber(rs.getInt("ORDERNO")); objDto.setFromSite(rs.getString("PRIMARYLOCATION")); objDto.setFromSite(objDto.getFromSite() == null ? null : objDto.getFromSite().replaceAll("~", "")); objDto.setToSite(rs.getString("SECONDARYLOCATION")); objDto.setToSite(objDto.getToSite() == null ? null : objDto.getToSite().replaceAll("~", "")); objDto.setItemQuantity(1); objDto.setKmsDistance(rs.getString("DISTANCE")); objDto.setChargeAmount(rs.getDouble("LINEITEMAMOUNT")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLineItemDescription(rs.getString("SERVICEDETDESCRIPTION")); objDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objDto.getLocDate()); objDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setAmReceiveDate(rs.getString("AMRECEIVEDATE")); if (rs.getString("AMRECEIVEDATE") != null && !"".equals(rs.getString("AMRECEIVEDATE"))) { Date date = df.parse(objDto.getAmReceiveDate()); objDto.setAmReceiveDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setOrderTotal(rs.getDouble("POAMOUNT")); objDto.setOrderEntryDate(rs.getString("ORDERCREATIONDATE")); if (rs.getDate("ORDERCREATIONDATE") != null && !"".equals(rs.getDate("ORDERCREATIONDATE"))) { Date date = rs.getDate("ORDERCREATIONDATE"); objDto.setOrderEntryDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setLicenceCoName(rs.getString("LCOMPANYNAME")); objDto.setLogicalCircuitNumber(rs.getString("LOGICAL_CIRCUITID")); objDto.setOrderType(rs.getString("ORDERCATEGORY")); objDto.setPaymentTerm(rs.getString("PAYMENTTERM")); objDto.setProjectManager(rs.getString("PROJECTMANAGER")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setOldLineitemAmount(""); objDto.setDemoType(rs.getString("DEMO_TYPE")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderStageDescription(rs.getString("STAGE")); objDto.setServiceStageDescription(rs.getString("SERVICE_STAGE")); objDto.setChargeEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objDto.getChargeEndDate()); objDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objDto.setRemarks(rs.getString("REMARKS")); objDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE")); objDto.setOsp(rs.getString("OSP")); // [404040] Start objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); //[404040] End //<!--GlobalDataBillingEfficiency BFR6 --> objDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); //[130] start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<DisconnectLineReportDTO> viewDisconnectionLineReport(DisconnectLineReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<DisconnectLineReportDTO> listSearchDetails = new ArrayList<DisconnectLineReportDTO>(); DisconnectLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null;//www . j a v a 2 s .c o m try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionLineReportForUsage); if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(1, objDto.getOrdermonth().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(4, objDto.getServiceName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) { proc.setString(5, objDto.getOrdersubtype().trim()); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(6, objDto.getVerticalDetails().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(12, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new DisconnectLineReportDTO(); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setCust_name(rs.getString("PARTYNAME")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS")); objReportsDto.setLocation_to(rs.getString("TO_ADDRESS")); //objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objReportsDto.setSeclocation(VAR_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.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.setBill_period(rs.getString("BILL_PERIOD")); if ((rs.getString("CONFIG_ID")) != null || !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null || !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBill_period(tBillPeriod); } objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPoNumber(rs.getInt("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(objReportsDto.getPoDate())).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); tempDate = rs.getDate("PORECEIVEDATE"); objReportsDto.setPoRecieveDate(rs.getString("PORECEIVEDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setPoReceiveDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); tempDate = rs.getDate("CUSTPODATE"); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setBilling_trigger_date(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBilling_trigger_date()); objReportsDto.setBilling_trigger_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPmapprovaldate(rs.getString("PM_PROV_DATE")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = (s1.substring(0, 7)).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("BILLING_TRIGGER_FLAG")); objReportsDto.setLineno(rs.getInt("ORDER_LINE_ID")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo")); objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS")); objReportsDto.setStageName(rs.getString("STAGE")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setNeworder_remarks(rs.getString("NEWORDER_REMARKS")); objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objReportsDto.setCopcapprovaldate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE")); objReportsDto.setStandard_reason(rs.getString("STANDARDREASON")); tempDate = rs.getDate("ORDERDATE"); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase()); } if (objDto.getIsUsage() == 0) { objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setCust_total_poamt(rs.getDouble("CUST_TOT_PO_AMT")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeEndDate(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE")); if (rs.getString("CSTATE_CHARGE_CURRENT_START_DATE") != null && !"".equals(rs.getString("CSTATE_CHARGE_CURRENT_START_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); 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.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.setBusiness_serial_no(rs.getString("Business_No")); objReportsDto.setOpms_act_id(rs.getString("Opms_Account_Id")); } if (objDto.getIsUsage() == 1) { objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartDate(rs.getString("SYSTEM_START_DATE")); if (rs.getString("SYSTEM_START_DATE") != null && !"".equals(rs.getString("SYSTEM_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setEnd_date(rs.getString("SYSTEM_END_DATE")); if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((utility.showDate_Report(date)).toUpperCase()); } dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS")); objReportsDto.setComponentDto(dto); } 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<LogicalSIDataReportDTO> viewLogicalSIDataReport(LogicalSIDataReportDTO objDto) { // Nagarjuna String methodName = "viewLogicalSIDataReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end/* w w w.ja va 2 s. c o m*/ Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<LogicalSIDataReportDTO> listSearchDetails = new ArrayList<LogicalSIDataReportDTO>(); LogicalSIDataReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetLogicalSIDataReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getLogicalSINumber() != 0 && !"".equals(objDto.getLogicalSINumber())) { proc.setInt(6, objDto.getLogicalSINumber()); } else { proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new LogicalSIDataReportDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setRecordStatus(rs.getString("recordStatus")); objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setParent_name(rs.getString("PARENT_NAME"));//PARENT LINE NAME objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//Line Name objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setFrequencyAmt(rs.getString("FREQUENCY_AMT")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); 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.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setChargeEndDate(rs.getString("END_DATE"));//Charge End Date if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount //Advance //Installment Rate 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.setPoExclude(rs.getString("PO_EXCLUDE"));//Po Valid Exclude objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); 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.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//PO Contract Period objReportsDto.setContractStartDate(rs.getString("CONTRACTSTARTDATE")); if (rs.getString("CONTRACTSTARTDATE") != null && !"".equals(rs.getString("CONTRACTSTARTDATE"))) { Date date = df.parse(objReportsDto.getContractStartDate()); objReportsDto.setContractStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setContractEndDate(rs.getString("CONTRACTENDDATE")); if (rs.getString("CONTRACTENDDATE") != null && !"".equals(rs.getString("CONTRACTENDDATE"))) { Date date = df.parse(objReportsDto.getContractEndDate()); objReportsDto.setContractEndDate((utility.showDate_Report(date)).toUpperCase()); } 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.setChargeinfoID(rs.getString("CHARGEINFOID"));//need to add in view objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); //Pk Charges Id //M6 Product Id //Parent Product Id objReportsDto.setBillingInfoID(rs.getInt("CHARGE_HDR_ID"));//Charge Hdr Id //Ib Pk Charges Id //Ib Order Line Id //M6 Order Id //Order Line Si No objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setRemarks(rs.getString("REMARKS")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); String billAddress = rs.getString("BILLING_ADDRESS"); String[] billAddressArray = billAddress.split("~~ ", 14); objReportsDto.setCountyName(billAddressArray[8]);// rs.getString("COUNTRY_NAME") objReportsDto.setAddress1(billAddressArray[2]);//billing Address1 objReportsDto.setAddress2(billAddressArray[3]);//billing Address2 objReportsDto.setAddress3(billAddressArray[4]);//billing Address3 objReportsDto.setAddress4(billAddressArray[5]);//billing Address4 objReportsDto.setCityName(billAddressArray[6]);//need to add in view - rs.getString("CITY_NAME") objReportsDto.setPostalCode(billAddressArray[9]);//need to add in view -rs.getString("POSTAL_CODE") objReportsDto.setStateName(billAddressArray[7]);//need to add in view - rs.getString("STATE_NAME") //Active End Date objReportsDto.setContactName(billAddressArray[0] + " " + billAddressArray[1]);//Contact Person Name - rs.getString("BILL_CON_PER_NAME") objReportsDto.setDesignation(billAddressArray[13]);//Person Designation - rs.getString("DESIGNATION") objReportsDto.setTelePhoneNo(billAddressArray[10]);//Person Mobile - rs.getString("TELEPHONENO") objReportsDto.setEmailId(billAddressArray[12]);//Person Email - rs.getString("EMAIL_ID") objReportsDto.setFax(billAddressArray[11]);//Person Fax - rs.getString("FAX") objReportsDto.setLst_No(rs.getString("LST_NO"));//Lst No objReportsDto.setLstDate(rs.getString("LST_DATE"));//Lst Date if (rs.getString("LST_DATE") != null && !"".equals(rs.getString("LST_DATE"))) { objReportsDto .setLstDate((utility.showDate_Report(new Date(rs.getTimestamp("LST_DATE").getTime()))) .toUpperCase()); } //Billing Address Type //objReportsDto.setAttributeLabel(rs.getString("Attribute_Name")); //objReportsDto.setAttributeValue(rs.getString("Attribute_Value")); objReportsDto.setStoreName(rs.getString("STORENAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); //Principle Amt //Interest Rate objReportsDto.setWarrantyStartDateLogic(rs.getString("WARRENTY_START_DATE_LOGIC")); objReportsDto.setWarrantyPeriodMonths(rs.getString("WARRENTY_PERIOD_MONTHS")); objReportsDto.setWarrantyPeriodDays(rs.getString("WARRENTY_PERIOD_DAYS")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyStartDate()); objReportsDto.setWarrantyStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setWarrantyEndDateLogic(rs.getString("WARRENTY_END_DATE_LOGIC")); objReportsDto.setWarrantyEndPeriodMonths(rs.getString("WARRENTY_END_PERIOD_MONTHS"));//need objReportsDto.setWarrantyEndPeriodDays(rs.getString("WARRENTY_END_PERIOD_DAYS"));//need objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE"));//need if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyEndDate()); objReportsDto.setWarrantyEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setExtndSupportPeriodMonths(rs.getString("EXT_SUPPORT_PERIOD_MONTHS")); objReportsDto.setExtndSupportPeriodDays(rs.getString("EXT_SUPPORT_PERIOD_DAYS")); objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { Date date = df.parse(objReportsDto.getExtSuportEndDate()); objReportsDto.setExtSuportEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1"));//Dispatch Address1 objReportsDto.setDispatchAddress2(rs.getString("DISP_ADDRESS2"));//Dispatch Address2 objReportsDto.setDispatchAddress3(rs.getString("DISP_ADDRESS3"));//Dispatch Address3 objReportsDto.setDispatchCityName(rs.getString("DISP_CITY_NAME"));//Dispatch City objReportsDto.setDispatchPostalCode(rs.getString("DISP_POSTAL_CODE"));//Dispatch Postal Code objReportsDto.setDispatchStateName(rs.getString("DISP_STATE_NAME"));//Dispatch State objReportsDto.setDispatchPersonName(rs.getString("DISP_Con_Person_Name"));//Dispatch Conact Person Name objReportsDto.setDispatchPhoneNo(rs.getString("DISP_TELEPHONENO"));//Dispatch Contact Person Mobile objReportsDto.setDispatchLstNumber(rs.getString("DISP_LST_NO"));//Dispatch Lst Number objReportsDto.setDispatchLstDate(rs.getString("DISP_LST_DATE"));//Dispatch Lst Date if (rs.getString("DISP_LST_DATE") != null && !"".equals(rs.getString("DISP_LST_DATE"))) { objReportsDto.setDispatchLstDate( (utility.showDate_Report(new Date(rs.getTimestamp("DISP_LST_DATE").getTime()))) .toUpperCase()); } //Dispatch Address Type //New Service List Id //New Crm Order Id //Remrks objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<PendingOrdersAndBillingHardwaresDTO> ViewpendingOrderBillandHardwareList( PendingOrdersAndBillingHardwaresDTO objDto) throws Exception { // Nagarjuna String methodName = "ViewpendingOrderBillandHardwareList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end /*from www. j a va 2s . c om*/ ArrayList<PendingOrdersAndBillingHardwaresDTO> objUserList = new ArrayList<PendingOrdersAndBillingHardwaresDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlPendingOrderBillHardware); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(2, objDto.getFromAccountNo()); } else { proc.setNull(2, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(3, objDto.getToAccountNo()); } else { proc.setNull(3, java.sql.Types.BIGINT); } if (objDto.getFromOrderDate() != null && !"".equals(objDto.getFromOrderDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromOrderDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(4, formattedDate); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToOrderDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(5, formattedDate1); } else { proc.setNull(5, java.sql.Types.VARCHAR); } if (objDto.getFromCrmOrderid() != null && !"".equals(objDto.getFromCrmOrderid())) { proc.setLong(6, new Long(objDto.getFromCrmOrderid().trim())); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToCrmOrderid() != null && !"".equals(objDto.getToCrmOrderid())) { proc.setLong(7, new Long(objDto.getToCrmOrderid().trim())); } else { proc.setNull(7, java.sql.Types.BIGINT); } if (objDto.getParty() != null && !"".equals(objDto.getParty())) { proc.setString(8, objDto.getParty().trim()); } else { proc.setNull(8, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(9, pagingSorting.getSortByColumn());// columnName proc.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(11, pagingSorting.getStartRecordId());// start index proc.setInt(12, pagingSorting.getEndRecordId());// end index proc.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new PendingOrdersAndBillingHardwaresDTO(); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setPoNumber(rs.getInt("PODETAILNO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO")); objDto.setLinename(rs.getString("LINENAME")); //objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setChargeAmount_String(BigDecimal.valueOf((rs.getDouble("CHARGEAMOUNT"))).toPlainString()); if (objDto.getChargeAmount_String() == null) { objDto.setChargeAmount_String(" "); } objDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objDto.getStartDate()); objDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objDto.setChallenno(rs.getString("CHALLEN_NO")); objDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { String s1 = rs.getString("CHALLEN_DATE"); if (s1.length() == 10) { s1 = "0" + s1; } String s3 = s1.substring(0, 7).toUpperCase(); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderType(rs.getString("ORDERTYPE")); //[606060] Start objDto.setServiceNumber(rs.getInt("SERVICE")); objDto.setCurrency(rs.getString("CURRENCY")); //[606060] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<RestPendingLineReportDTO> viewRestPendingLineReport(RestPendingLineReportDTO objDto) { // Nagarjuna String methodName = "viewRestPendingLineReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; ArrayList<RestPendingLineReportDTO> listSearchDetails = new ArrayList<RestPendingLineReportDTO>(); RestPendingLineReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null;//from w w w . jav a2 s. c om try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetRestPendingLineReports); if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(1, objDto.getFromDate().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(2, objDto.getToDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(3, objDto.getServiceName().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } 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 proc.setInt(11, objDto.getIsUsage()); rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new RestPendingLineReportDTO(); objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); tempDate = rs.getDate("ORDERDATE"); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).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()); }*/ tempDate = rs.getDate("PODATE"); objReportsDto.setPoDate(rs.getString("PODATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setPoDate((utility.showDate_Report(tempDate)).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()); }*/ tempDate = rs.getDate("CUSTPODATE"); objReportsDto.setCustPoDate(rs.getString("CUSTPODATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setCustPoDate((utility.showDate_Report(tempDate)).toUpperCase()); } 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.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); //objReportsDto.setPrimarylocation(rs.getString("PRIMARYLOCATION")); setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); //objReportsDto.setSeclocation(rs.getString("SECONDARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objReportsDto.setSeclocation(VAR_SECONDARYLOCATION); /*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()); }*/ tempDate = rs.getDate("SERVICE_RFS_DATE"); objReportsDto.setRfs_date(rs.getString("SERVICE_RFS_DATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setRfs_date((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setLogicalCircuitId(rs.getString("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setDemoType(rs.getString("Demo_Type")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setDistance(rs.getString("DISTANCE")); objReportsDto.setStageName(rs.getString("ORDERSTAGE")); objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); 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.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); 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")); if (objDto.getIsUsage() == 1) { objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); //objReportsDto.setCanceldate(rs.getString("CANCELDATE")); tempDate = rs.getDate("CANCEL_DATE"); if (tempDate != null) { objReportsDto.setCanceldate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setM6_prod_id(rs.getString("CHILDSPECID")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setServiceproductid(rs.getInt("Order_Line_Id")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setCancelServiceReason(rs.getString("CANCEL_RESION")); ComponentsDto dto = new ComponentsDto(); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { objReportsDto.setCopcapprovaldate( (Utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))) .toUpperCase()); } /*objReportsDto.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()); }*/ tempDate = rs.getDate("PORECEIVEDATE"); objReportsDto.setPoReceiveDate(rs.getString("PORECEIVEDATE")); if (tempDate != null && !"".equals(tempDate)) { objReportsDto.setPoReceiveDate((utility.showDate_Report(tempDate)).toUpperCase()); } //objReportsDto.setBilling_address(rs.getString("BILLING_ADDRESS")); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); objReportsDto.setBilling_address(VAR_BILLING_ADDRESS); objReportsDto.setLineno(rs.getInt("Order_Line_Id")); objReportsDto.setLocation_from(rs.getString("FROM_ADDRESS")); objReportsDto.setLocation_to(rs.getString("TO_ADDRESS")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setChild_act_no(rs.getString("Child_Account_Number")); objReportsDto.setCrm_productname(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setStartDate(rs.getString("SYSTEM_START_DATE")); if (rs.getString("SYSTEM_START_DATE") != null && !"".equalsIgnoreCase(rs.getString("SYSTEM_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setEndFxStatus(rs.getString("SYSTEM_END_STATUS")); //dto.setComponentFXStatus(rs.getString("FX_STATUS")); // <!--GlobalDataBillingEfficiency BFR5 --> objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); objReportsDto.setComponentDto(dto); } else { objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setLb_service_id(rs.getString("LB_SERVICE_LIST_ID")); objReportsDto.setServiceproductid(rs.getInt("SERVICEPRODUCTID")); objReportsDto.setServiceId(rs.getInt("SERVICEID")); objReportsDto.setPoAmountSum(rs.getLong("ORDERAMOUNT")); objReportsDto.setRegionName(rs.getString("REGIONNAME")); objReportsDto.setCancelServiceReason(rs.getString("CANCELREASON")); objReportsDto.setChild_act_no(rs.getString("CHILD_AC_NO")); objReportsDto.setBisource(rs.getString("BISOURCE")); objReportsDto.setServiceStage(rs.getString("SERVICESTAGE")); objReportsDto.setLocation_from(rs.getString("FROM_LOCATION")); objReportsDto.setLocation_to(rs.getString("TO_LOCATION")); objReportsDto.setLinename(rs.getString("LINENAME")); objReportsDto.setCrm_productname(rs.getString("CRMPRODUCTNAME")); objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objReportsDto.setAccountManager(rs.getString("ACTMNAME")); objReportsDto.setProjectManager(rs.getString("PMNAME")); 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.setCopcApproveDate( (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.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.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.setLineno(rs.getInt("LINEITEMNO")); objReportsDto.setOpms_act_id(rs.getString("OPMS_ACT_ID")); objReportsDto.setAddress1(rs.getString("ADDRESS")); objReportsDto.setCancelflag(rs.getString("CANCELBY")); objReportsDto.setBilling_address(rs.getString("BILLING_LOCATION")); objReportsDto.setCanceldate(rs.getString("CANCELDATE")); objReportsDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); 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.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setTokenno(rs.getString("TOKENNO")); objReportsDto.setSaleNature(rs.getString("SALENATURENAME")); objReportsDto.setSaleType(rs.getString("SALETYPENAME")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setBill_period(rs.getString("BILL_PERIOD")); if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBill_period(tBillPeriod); } objReportsDto.setChargeAmount(rs.getDouble("INV_AMT")); objReportsDto.setLineamt(rs.getLong("LINEITEMAMOUNT")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); 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")); // <!--GlobalDataBillingEfficiency BFR5 --> objReportsDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); } //[005] Start objReportsDto.setInstallationFromCity(rs.getString("INSTALLATION_FROM_CITY")); objReportsDto.setInstallationToCity(rs.getString("INSTALLATION_TO_CITY")); objReportsDto.setInstallationFromState(rs.getString("INSTALLATION_FROM_STATE")); objReportsDto.setInstallationToState(rs.getString("INSTALLATION_TO_STATE")); objReportsDto.setBillingContactName(rs.getString("BILLING_CONTACT_NAME")); objReportsDto.setBillingContactNumber(rs.getString("BILLING_CONTACT_NUMBER")); objReportsDto.setBillingEmailId(rs.getString("BILLING_EMAIL_ID")); //[005] End //[006] Start objReportsDto.setStandardReason(rs.getString("STANDARDREASON")); //[006] End if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<LogicalSIDataReportDTO> viewLogicalSIDataReport(LogicalSIDataReportDTO objDto) { //Nagarjuna//from w ww . j ava2s .c om String methodName = "viewLogicalSIDataReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<LogicalSIDataReportDTO> listSearchDetails = new ArrayList<LogicalSIDataReportDTO>(); LogicalSIDataReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetLogicalSIDataReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getLogicalSINumber() != 0 && !"".equals(objDto.getLogicalSINumber())) { proc.setInt(6, objDto.getLogicalSINumber()); } else { proc.setNull(6, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(12, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { setBlank(); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); objReportsDto = new LogicalSIDataReportDTO(); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setParent_name(rs.getString("PARENT_NAME"));//PARENT LINE NAME objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//Line Name objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); 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.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); // change tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//PO Contract Period tempDate = rs.getDate("CONTRACTSTARTDATE"); if (tempDate != null) { objReportsDto.setContractStartDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("CONTRACTENDDATE"); if (tempDate != null) { objReportsDto.setContractEndDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setContactName(VAR_BILL_CON_PER_NAME);//Contact Person Name objReportsDto.setDesignation(VAR_DESIGNATION);//Person Designation objReportsDto.setTelePhoneNo(VAR_TELEPHONENO);//Person Mobile objReportsDto.setEmailId(VAR_EMAIL_ID);//Person Email objReportsDto.setFax(VAR_FAX);//Person Fax //Remrks objReportsDto.setBillingLevel(rs.getString("BILLING_LEVELNAME")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); if (objDto.getIsUsage() == 0) { objReportsDto.setRecordStatus(rs.getString("recordStatus")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCountyName(rs.getString("COUNTRY_NAME")); objReportsDto.setAddress1(rs.getString("BILL_ADDRESS1"));//billing Address1 objReportsDto.setAddress2(rs.getString("BILL_ADDRESS2"));//billing Address2 objReportsDto.setAddress3(rs.getString("BILL_ADDRESS3"));//billing Address3 objReportsDto.setAddress4(rs.getString("BILL_ADDRESS4"));//billing Address4 objReportsDto.setCityName(rs.getString("CITY_NAME"));//need to add in view objReportsDto.setPostalCode(rs.getString("POSTAL_CODE"));//need to add in view objReportsDto.setStateName(rs.getString("STATE_NAME"));//need to add in view objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setFrequencyAmt(rs.getString("FREQUENCY_AMT")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setChargeEndDate(rs.getString("END_DATE"));//Charge End Date if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setAnnual_rate(rs.getDouble("ANNUAL_RATE")); //--Trai Rate //--Discount //Advance //Installment Rate 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.setPoExclude(rs.getString("PO_EXCLUDE"));//Po Valid Exclude objReportsDto.setChargeinfoID(rs.getString("CHARGEINFOID"));//need to add in view //M6 Order Id //remarks //Pk Charges Id //M6 Product Id //Parent Product Id objReportsDto.setBillingInfoID(rs.getInt("CHARGE_HDR_ID"));//Charge Hdr Id //Ib Pk Charges Id //Ib Order Line Id //Order Line Si No objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); // Active End Date objReportsDto.setLst_No(rs.getString("LST_NO"));//Lst No objReportsDto.setLstDate(rs.getString("LST_DATE"));//Lst Date if (rs.getString("LST_DATE") != null && !"".equals(rs.getString("LST_DATE"))) { objReportsDto.setLstDate( (utility.showDate_Report(new Date(rs.getTimestamp("LST_DATE").getTime()))) .toUpperCase()); } //Billing Address Type //objReportsDto.setAttributeLabel(rs.getString("Attribute_Name")); //objReportsDto.setAttributeValue(rs.getString("Attribute_Value")); objReportsDto.setStoreName(rs.getString("STORENAME")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); //Principle Amt //Interest Rate objReportsDto.setWarrantyStartDateLogic(rs.getString("WARRENTY_START_DATE_LOGIC")); objReportsDto.setWarrantyPeriodMonths(rs.getString("WARRENTY_PERIOD_MONTHS")); objReportsDto.setWarrantyPeriodDays(rs.getString("WARRENTY_PERIOD_DAYS")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyStartDate()); objReportsDto.setWarrantyStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setWarrantyEndDateLogic(rs.getString("WARRENTY_END_DATE_LOGIC")); objReportsDto.setWarrantyEndPeriodMonths(rs.getString("WARRENTY_END_PERIOD_MONTHS"));//need objReportsDto.setWarrantyEndPeriodDays(rs.getString("WARRENTY_END_PERIOD_DAYS"));//need objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE"));//need if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyEndDate()); objReportsDto.setWarrantyEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setExtndSupportPeriodMonths(rs.getString("EXT_SUPPORT_PERIOD_MONTHS")); objReportsDto.setExtndSupportPeriodDays(rs.getString("EXT_SUPPORT_PERIOD_DAYS")); objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { Date date = df.parse(objReportsDto.getExtSuportEndDate()); objReportsDto.setExtSuportEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1"));//Dispatch Address1 objReportsDto.setDispatchAddress2(rs.getString("DISP_ADDRESS2"));//Dispatch Address2 objReportsDto.setDispatchAddress3(rs.getString("DISP_ADDRESS3"));//Dispatch Address3 objReportsDto.setDispatchCityName(rs.getString("DISP_CITY_NAME"));//Dispatch City objReportsDto.setDispatchPostalCode(rs.getString("DISP_POSTAL_CODE"));//Dispatch Postal Code objReportsDto.setDispatchStateName(rs.getString("DISP_STATE_NAME"));//Dispatch State objReportsDto.setDispatchPersonName(rs.getString("DISP_Con_Person_Name"));//Dispatch Conact Person Name objReportsDto.setDispatchPhoneNo(rs.getString("DISP_TELEPHONENO"));//Dispatch Contact Person Mobile objReportsDto.setDispatchLstNumber(rs.getString("DISP_LST_NO"));//Dispatch Lst Number objReportsDto.setDispatchLstDate(rs.getString("DISP_LST_DATE"));//Dispatch Lst Date if (rs.getString("DISP_LST_DATE") != null && !"".equals(rs.getString("DISP_LST_DATE"))) { objReportsDto.setDispatchLstDate( (utility.showDate_Report(new Date(rs.getTimestamp("DISP_LST_DATE").getTime()))) .toUpperCase()); } //Dispatch Address Type //New Service List Id //New Crm Order Id } if (objDto.getIsUsage() == 1) { objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS")); objReportsDto.setNeworder_remarks(rs.getString("NEWORDER_REMARKS")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setCountyName(VAR_BCP_COUNTRY_NAME); objReportsDto.setAddress1(VAR_BILL_ADDRESS1);//billing Address1 objReportsDto.setAddress2(VAR_BILL_ADDRESS2);//billing Address2 objReportsDto.setAddress3(VAR_BILL_ADDRESS3);//billing Address3 objReportsDto.setAddress4(VAR_BILL_ADDRESS4);//billing Address4 objReportsDto.setCityName(VAR_BCP_CITY_NAME);//need to add in view objReportsDto.setPostalCode(VAR_BCP_PIN);//need to add in view objReportsDto.setStateName(VAR_BCP_STATE_NAME);//need to add in view objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objReportsDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDateDays(rs.getInt("COMP_START_DAYS")); dto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEndDateDays(rs.getInt("COMP_END_DAYS")); dto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); dto.setStartDate(rs.getString("SYSTEM_START_DATE")); if (rs.getString("SYSTEM_START_DATE") != null && !"".equals(rs.getString("SYSTEM_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } dto.setEnd_date(rs.getString("SYSTEM_END_DATE")); if (rs.getString("SYSTEM_END_DATE") != null && !"".equals(rs.getString("SYSTEM_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setComponentDto(dto); } 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<OBValueReportDTO> viewOBValueReport(OBValueReportDTO objDto) throws Exception { String methodName = "viewOBValueReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; ArrayList<OBValueReportDTO> objUserList = new ArrayList<OBValueReportDTO>(); Connection conn = null;/*from w w w . j a v a2s . c o m*/ CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OBValueReportDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOBValueReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; objDto = new OBValueReportDTO(); objDto.setPartyNo(rs.getInt("PARTY_NO")); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); objDto.setLineType(rs.getString("LINETYPE")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); if (rs.getString("FROM_SITE") != null && !"".equals(rs.getString("FROM_SITE")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("FROM_SITE").split("~~"); objDto.setPrimaryLocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setPrimaryLocation(""); } if (rs.getString("TO_SITE") != null && !"".equals(rs.getString("TO_SITE")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[4] + " " + ss[5] + " " + ss[6] + " " + ss[7] + " " + ss[8] + " " + ss[9] + " " + ss[10] + " " + ss[2]); } else { String ss[] = rs.getString("TO_SITE").split("~~"); objDto.setSeclocation(ss[0] + " " + ss[1] + " " + ss[5] + " " + ss[3]); } } else { objDto.setSeclocation(""); } objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PRJ_MGR_NAME")); objDto.setPrjmgremail(rs.getString("PROJECTMGR_MAIL")); objDto.setActmngname(rs.getString("ACCT_MGR_NAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUSTPODATE").getTime()))) .toUpperCase()); } objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("PM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("AM_APPROVAL_DATE").getTime()))) .toUpperCase()); } objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); objDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objDto.setServiceType(rs.getString("SERVICETYPE")); objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY")) && rs.getString("PRIMARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("PRIMARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("FROM_CITY").split("~~"); objDto.setFrom_city(ss[8]); } else { objDto.setFrom_city(" "); } } else { objDto.setFrom_city(""); } if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY")) && rs.getString("SECONDARYLOCATIONTYPE") != null) { if (Integer.parseInt(rs.getString("SECONDARYLOCATIONTYPE")) == 1) { String ss[] = rs.getString("TO_CITY").split("~~"); objDto.setTo_city(ss[8]); } else { objDto.setTo_city(" "); } } else { objDto.setTo_city(""); } objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACCOUNTMGR_EMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); objDto.setAccountName(rs.getString("ACCOUNTNAME")); objDto.setLoc_date(rs.getString("LOC_DATE")); objDto.setActualOB(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB"))).toPlainString()); objDto.setActualOBINR(Double.valueOf(Utility.round(rs.getDouble("ACTUAL_OB_INR"), 2)).toString()); //objDto.setActualOBINR(BigDecimal.valueOf((rs.getDouble("ACTUAL_OB_INR"))).toPlainString()); objDto.setFinalOB(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); objDto.setFinalOBINR(Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_INR"), 2)).toString()); //objDto.setFinalOBINR(Double.valueOf((rs.getDouble("OB_VALUE_INR"))).toString()); objDto.setChargeRemarks(rs.getString("REMARKS")); objDto.setCopcApprovedBy(rs.getString("COPC_APPROVER_NAME")); objDto.setPmApprovedby(rs.getString("PM_APPROVER_NAME")); objDto.setDemoFlag(rs.getString("ISDEMO")); objDto.setOffnet(rs.getString("OFFNET_LABELATTVALUE")); objDto.setMediaType(rs.getString("MEDIA_LABELATTVALUE")); objDto.setCancellationReason(rs.getString("CANCELLATION_REASON")); objDto.setrRDate(rs.getString("RR_DATE")); if (rs.getString("RR_DATE") != null && !"".equals(rs.getString("RR_DATE"))) { objDto.setrRDate((utility.showDate_Report(new Date(rs.getTimestamp("RR_DATE").getTime()))) .toUpperCase()); } objDto.setDiffDays(rs.getString("DIFF_DAYS")); objDto.setTotalDays(rs.getString("TOTALDAYS")); objDto.setEffectiveDays(rs.getString("EFFECTIVE_DAYS")); objDto.setMn(BigDecimal.valueOf((rs.getDouble("MN"))).toPlainString()); //using this value for ob value objDto.setOldPKChargeid(rs.getLong("OBLINKCHARGE")); objDto.setOldChargeAmount(BigDecimal.valueOf((rs.getDouble("OBLINKCHARGEAMOUNT"))).toPlainString()); objDto.setOrderEnteredBy(rs.getString("ORDER_CREATED_BY_NAME")); objDto.setExchangeRate(BigDecimal.valueOf((rs.getDouble("EXCHANGE_RATE"))).toPlainString()); objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE_TRANSACTION"))).toPlainString()); objDto.setObValueINR( Double.valueOf(Utility.round(rs.getDouble("OB_VALUE_TRANSACTION_INR"), 2)).toString()); objDto.setCustomerSegment(rs.getString("CUST_SEGMENT_CODE")); //newly added objDto.setProjectCategory(rs.getString("ORDERCATGRY_LABELATTVALUE")); //newly added objDto.setServiceRemarks(rs.getString("SERVICE_REMARKS")); // newly added objDto.setObMonth(rs.getString("OB_MONTH")); objDto.setObYear(rs.getString("OB_YEAR")); //[129] objDto.setEntryType(rs.getString("ENTRY_TYPE")); objDto.setIsNfa(rs.getString("IS_NFA")); objDto.setChargeperiod(rs.getString("TOTAL_DAYS")); objDto.setCopcApproveDate(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { objDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime()))) .toUpperCase()); } //[130] Start objDto.setSalesForceOpportunityNumber(rs.getString("SF_OPP_ID")); objDto.setChannelPartner(rs.getString("PARTNER_NAME")); objDto.setNetworkType(rs.getString("NETWORK_SERVICE_TYPE")); objDto.setPartnerId(rs.getString("PARTNER_ID")); //[130] End //[131] start objDto.setPartnerCode(rs.getString("PARTNER_CODE")); objDto.setFieldEngineer(rs.getString("FIELD_ENGINEER")); //[131] end if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }