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
/** * Create a Report to generate LEPM Order Detail Report //from ww w.j a v a2 s.c o m * @param obj a DTO which consist all the search parameters * @return a ArrayList of dto which consist all the data of reports * @exception Sql Exception * */ public ArrayList<ReportsDto> viewLEPMOrderDetailReport(ReportsDto objDto) throws Exception { // Nagarjuna String methodName = "viewLEPMOrderDetailReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; int countFlag = 0; ReportsDto objReportsDto = null; ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>(); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlLEPMOrderDetailReport); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getCopcApproveDate() != null && !"".equals(objDto.getCopcApproveDate())) { proc.setString(2, objDto.getCopcApproveDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(3, objDto.getVerticalDetails().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); } 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));// end rs = proc.executeQuery(); while (rs.next() != false) { objDto = new ReportsDto(); objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderNo(rs.getString("ORDERNO")); objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objDto.setCopcApproveDate( (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase()); } objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objDto.setServiceId(rs.getInt("SERVICENO")); objDto.setQuoteNo(rs.getString("QUOTENO")); objDto.setProductName(rs.getString("PRODUCTNAME")); objDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); objDto.setPrimarylocation(rs.getString("FROM_SITE")); objDto.setSeclocation(rs.getString("TO_SITE")); objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); objDto.setBandwaidth(rs.getString("BANDWIDTH")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objDto.setPrjmngname(rs.getString("PMNAME")); objDto.setPrjmgremail(rs.getString("PMEMAIL")); objDto.setActmngname(rs.getString("ACTMNAME")); objDto.setZoneName(rs.getString("ZONENNAME")); objDto.setRegionName(rs.getString("REGIONNAME")); objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objDto.setCustPoDate(rs.getString("CUSTPODATE")); if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) { objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase()); } objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase()); } objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE")); if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) { objDto.setPmApproveDate( (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase()); } objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE")); if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) { objDto.setAmApproveDate( (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase()); } objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE")); if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) { objDto.setNio_approve_date( (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase()); } objDto.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((rs.getTimestamp("RFS_DATE")))).toUpperCase()); } objDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objDto.setOrderStatus(rs.getString("STATUS")); objDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); objDto.setLinename(rs.getString("LINENAME")); objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objDto.setServiceProductID(rs.getInt("LINENO")); objDto.setServiceName(rs.getString("SERVICENAME")); objDto.setAccountID(rs.getInt("ACCOUNTID")); objDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objDto.setEntity(rs.getString("COMPANYNAME")); objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objDto.setFrequencyName(rs.getString("PAYMENTTERM")); objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); if ("NULL".equals(rs.getString("SERVICETYPE"))) { objDto.setServiceType(""); } else { objDto.setServiceType(rs.getString("SERVICETYPE")); } objDto.setUom(rs.getString("UOM")); objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objDto.setFrom_city(rs.getString("FROM_CITY")); objDto.setTo_city(rs.getString("TO_CITY")); objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objDto.setRatio(rs.getString("RATIO")); objDto.setTaxation(rs.getString("TAXATIONVALUE")); objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setDistance(rs.getString("DISTANCE")); objDto.setAccountManager(rs.getString("ACTMEMAIL")); objDto.setCurrencyCode(rs.getString("CURNAME")); objDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); objDto.setPoAmount(rs.getString("POAMOUNT")); objDto.setBisource(rs.getString("BISOURCE")); objDto.setOrderType(rs.getString("ORDERTYPE")); objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objDto.setParent_name(rs.getString("PARENTNAME")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<ReportsDto> viewZeroOrderValueReportDetails(ReportsDto objDto) throws Exception { // Nagarjuna String methodName = "viewZeroOrderValueReportDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>(); Connection conn = null;/*from w w w .j av a 2s. c om*/ CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(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 ReportsDto(); 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")); 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:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param treeDef/*from w w w . j a va 2s . c o m*/ * @throws SQLException */ public void convertLithoStratGeneral(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName) throws SQLException { Statement stmt = null; ResultSet rs = null; String s = ""; try { // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName); if (count < 1) return; if (hasFrame) { setProcess(0, count); } // create an ID mapper for the geography table (mainly for use in converting localities) IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID", true); if (lithoStratIdMapper == null) { UIRegistry.showError("The lithoStratIdMapper was null."); return; } IdMapperIFace gtpIdMapper = IdMapperMgr.getInstance().get("geologictimeperiod", "GeologicTimePeriodID"); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } String sql = String.format( "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + "ORDER BY s.StratigraphyID", srcTableName); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(sql); Map<Integer, Pair<Integer, Integer>> stratHash = new HashMap<Integer, Pair<Integer, Integer>>(); int stratsWithNoGTP = 0; int stratsWithNoMappedGTP = 0; int missingCEMapping = 0; int lithoCnt = 0; int counter = 0; // for each old record, convert the record while (rs.next()) { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } } // grab the important data fields from the old record int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent String superGroup = rs.getString(2); String lithoGroup = rs.getString(3); String formation = rs.getString(4); String member = rs.getString(5); String bed = rs.getString(6); String remarks = escapeStringLiterals(rs.getString(7)); String text1 = escapeStringLiterals(rs.getString(8)); String text2 = escapeStringLiterals(rs.getString(9)); Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null; Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null; Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null; Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null; Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null; // Check to see if there is any Litho information OR an GTP Id // If both are missing then skip the record. boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation) || isNotEmpty(member); if (!hasLithoFields && oldGTPId == null) { continue; } Integer gtpId = null; if (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); if (gtpId == null) { tblWriter.logError("Old GTPID[" + gtpId + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]"); stratsWithNoMappedGTP++; } } else { stratsWithNoGTP++; } // There may not be any Litho information to add to the LithoStrat tree, // but it did have GTP Information if we got here Integer lithoStratID = null; if (hasLithoFields) { // create a new Geography object from the old data LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed, remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession); LithoStrat newStrat = getLastLithoStrat(newStrats); counter++; lithoCnt += newStrats.length; // Map Old LithoStrat ID to the new Tree Id //System.out.println(oldStratId + " " + newStrat.getLithoStratId()); if (newStrat != null) { lithoStratID = newStrat.getLithoStratId(); lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } if (lithoStratID != null || gtpId != null) { Integer newCEId = ceMapper.get(oldStratId); if (newCEId == null) { String msg = String.format("No CE mapping for Old StratId %d, when they are a one-to-one.", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } else { stratHash.put(newCEId, new Pair<Integer, Integer>(gtpId, lithoStratID)); } } } stmt.close(); System.out.println("lithoCnt: " + lithoCnt); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " Stratigraphy records"); rs.close(); Statement updateStatement = newDBConn.createStatement(); int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT Count(CollectingEventID) FROM collectingevent"); int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format("SELECT Count(CollectingEventID) FROM collectingevent " + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName)); String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt, (ceCnt - stratCnt)); tblWriter.log(msg); log.debug(msg); // Create a PaleoContext for each ColObj stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); int processCnt = BasicSQLUtils .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL"); if (frame != null) { frame.setDesc("Converting PaleoContext..."); frame.setProcess(0, processCnt); } TreeSet<Integer> missingStratIds = new TreeSet<Integer>(); int missingStrat = 0; int missingGTP = 0; int coUpdateCnt = 0; int cnt = 0; sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID"; rs = stmt.executeQuery(sql); while (rs.next()) { int coId = rs.getInt(1); // New CO Id Integer ceId = rs.getInt(2); // New CE Id Pair<Integer, Integer> strat = stratHash.get(ceId); Integer newLithoId = null; Integer gtpId = null; if (strat != null) { gtpId = strat.getFirst(); newLithoId = strat.getSecond(); } if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); if (gtpId == null) continue; } try { String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) " + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + "," + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL") + ")"; updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS); Integer paleoContextID = getInsertedId(updateStatement); if (paleoContextID == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID + " WHERE CollectionObjectID = " + coId; updateStatement.executeUpdate(sqlUpdate); coUpdateCnt++; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.getMessage()); throw new RuntimeException(e); } processCnt++; if (frame != null && cnt % 100 == 0) frame.setProcess(cnt); } rs.close(); stmt.close(); if (frame != null) frame.setProcess(processCnt); msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.", missingStrat, missingGTP); tblWriter.log(msg); log.debug(msg); msg = String.format("There are %d CO records updated.", coUpdateCnt); tblWriter.log(msg); log.debug(msg); updateStatement.close(); msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d", lithoStratIdMapper.size()); tblWriter.logError(msg); log.error(msg); StringBuilder sb = new StringBuilder(); sb.append("Missing New Strat: "); if (missingStratIds.size() == 0) sb.append("None"); for (Integer id : missingStratIds) { sb.append(String.format("%d, ", id)); } tblWriter.logError(sb.toString()); log.error(sb.toString()); } catch (Exception ex) { ex.printStackTrace(); } // Now in this Step we Add the PaleoContext to the Collecting Events }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<ReportsDto> ViewpendingOrderBillandHardwareList(ReportsDto objDto) throws Exception { // Nagarjuna String methodName = "ViewpendingOrderBillandHardwareList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>(); Connection conn = null;//w w w . ja v a2 s . c o m 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())) { proc.setString(4, objDto.getFromOrderDate().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getToOrderDate() != null && !"".equals(objDto.getToOrderDate())) { proc.setString(5, objDto.getToOrderDate().trim()); } 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 ReportsDto(); 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.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objDto.setLinename(rs.getString("LINENAME")); objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); 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"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objDto.setChallendate(s5); } objDto.setPartyName(rs.getString("PARTYNAME")); objDto.setOrderType(rs.getString("ORDERTYPE")); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } objUserList.add(objDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ArchivalReportDto> cancelledReportOrder(ArchivalReportDto reportsDto) { String methodName = "cancelledReportOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null; ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportDto dto = null;//w ww . j a v a 2 s .c om Utility utility = new Utility(); SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetCancelledtReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_id()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getLine_it_no()); cstmt.setString(7, reportsDto.getCkt_id()); cstmt.setString(8, reportsDto.getM6orderno()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportDto(); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER")); dto.setAccount_number(rs.getString("ACCOUNT_NUMBER")); dto.setPo_amount(rs.getDouble("PO_AMOUNT")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY")); dto.setVertical(rs.getString("VERTICAL")); dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE")); if (rs.getString("BILLING_CHARGE_START_DATE") != null && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) { dto.setBilling_charge_start_date( (utility.showDate_Report(dto.getBilling_charge_start_date())).toUpperCase()); } dto.setService_name(rs.getString("SERVICE_NAME")); dto.setOrder_line_no(rs.getString("ORDER_LINE_NO")); dto.setLine_name(rs.getString("LINE_NAME")); dto.setCancel_flag(rs.getString("CANCEL_FLAG")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setUom(rs.getString("UOM")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setStore_address(rs.getString("STORE_ADDRESS")); dto.setBill_uom(rs.getString("BILL_UOM")); dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER")); dto.setContract_period(rs.getString("CONTRACT_PERIOD")); dto.setCompany_name(rs.getString("COMPANY_NAME")); dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE")); if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) { dto.setCustomer_rfs_date((utility.showDate_Report3(dto.getCustomer_rfs_date())).toUpperCase());// issuee gui } dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) { dto.setCustomer_service_rfs_date( (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase()); // issue GUI } dto.setCurrency(rs.getString("CURRENCY")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE")); if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) { dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase()); } dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER")); dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC")); dto.setChallen_no(rs.getString("CHALLEN_NO")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setFrom_site(rs.getString("FROM_SITE")); dto.setTo_site(rs.getString("TO_SITE")); dto.setItem_quantity(rs.getString("ITEM_QUANTITY")); dto.setKms_distance(rs.getString("KMS_DISTANCE")); dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT")); dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { dto.setCopc_approved_date( utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))); } dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION")); dto.setLoc_date(rs.getString("LOC_DATE")); if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) { dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase()); } dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE")); if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) { dto.setAccount_manager_receive_date( (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase()); } dto.setOrder_total(rs.getDouble("ORDER_TOTAL")); dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE")); if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) { dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase()); } dto.setTaxation(rs.getString("TAXATION")); dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setPayment_term(rs.getString("PAYMENT_TERM")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setParty_name(rs.getString("PARTY_NAME")); dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION")); dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) { dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase()); } dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK")); dto.setRemarks(rs.getString("REMARKS")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setOsp(rs.getString("OSP")); dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setCkt_id(rs.getString("CKT_ID")); dto.setM6orderno(rs.getString("M6_ORDER_NO")); dto.setService_id(rs.getString("SERVICEID")); dto.setOldLsi(rs.getString("OLD_LSI")); dto.setCancel_reason(rs.getString("CANCEL_REASON")); dto.setCancel_date(rs.getString("CANCEL_DATE")); if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) { dto.setCancel_date((utility.showDate_Report2(dto.getCancel_date())).toUpperCase()); } dto.setProduct(rs.getString("PRODUCT")); dto.setSub_product(rs.getString("SUB_PRODUCT")); dto.setEffective_start_data(rs.getString("EFFECTIVE_START_DATA")); if (rs.getString("EFFECTIVE_START_DATA") != null && !"".equals(rs.getString("EFFECTIVE_START_DATA"))) { dto.setEffective_start_data( (utility.showDate_Report(dto.getEffective_start_data())).toUpperCase()); } dto.getInterfaceid(); docListDetails.add(dto); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { } } return docListDetails; }
From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ArchivalReportDto> reportDraftOrder(ArchivalReportDto reportsDto) { String methodName = "reportDraftOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null; ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportDto dto = null;/* w w w . java 2s.com*/ Utility utility = new Utility(); SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetDraftReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_number()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getLine_it_no()); cstmt.setString(7, reportsDto.getCkt_id()); cstmt.setString(8, reportsDto.getM6orderno()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportDto(); dto.setService_stage_description(rs.getString("SERVICE_STAGE_DESCRIPTION")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setAccount_manager(rs.getString("ACCOUNT_MANAGER")); dto.setAccount_number(rs.getString("ACCOUNT_NUMBER")); dto.setPo_amount(rs.getDouble("PO_AMOUNT")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setAccount_category(rs.getString("ACCOUNT_CATEGORY")); dto.setVertical(rs.getString("VERTICAL")); dto.setBilling_charge_start_date(rs.getString("BILLING_CHARGE_START_DATE")); if (rs.getString("BILLING_CHARGE_START_DATE") != null && !"".equals(rs.getString("BILLING_CHARGE_START_DATE"))) { dto.setBilling_charge_start_date( (utility.showDate_Report2(dto.getBilling_charge_start_date())).toUpperCase()); } dto.setLine_name(rs.getString("SERVICE_NAME")); dto.setOrder_line_no(rs.getString("ORDER_LINE_NO")); dto.setCharge_name(rs.getString("LINE_NAME")); dto.setCancel_flag(rs.getString("CANCEL_FLAG")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setUom(rs.getString("UOM")); dto.setBill_uom(rs.getString("BILL_UOM")); dto.setCategory_of_order(rs.getString("CATEGORY_OF_ORDER")); dto.setContract_period(rs.getString("CONTRACT_PERIOD")); dto.setCompany_name(rs.getString("COMPANY_NAME")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); if (rs.getString("CUSTOMER_SERVICE_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_SERVICE_RFS_DATE"))) { dto.setCustomer_service_rfs_date( (utility.showDate_Report2(dto.getCustomer_service_rfs_date())).toUpperCase()); } dto.setCurrency(rs.getString("CURRENCY")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCustomer_po_date(rs.getString("CUSTOMER_PO_DATE")); if (rs.getString("CUSTOMER_PO_DATE") != null && !"".equals(rs.getString("CUSTOMER_PO_DATE"))) { dto.setCustomer_po_date((utility.showDate_Report2(dto.getCustomer_po_date())).toUpperCase()); } dto.setCustomer_po_number(rs.getString("CUSTOMER_PO_NUMBER")); dto.setCyclic_or_non_cyclic(rs.getString("CYCLIC_OR_NON_CYCLIC")); dto.setChallen_no(rs.getString("CHALLEN_NO")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setFrom_site(rs.getString("FROM_SITE")); dto.setTo_site(rs.getString("TO_SITE")); dto.setItem_quantity(rs.getString("ITEM_QUANTITY")); dto.setKms_distance(rs.getString("KMS_DISTANCE")); dto.setLine_item_amount(rs.getDouble("LINE_ITEM_AMOUNT")); dto.setCopc_approved_date(rs.getString("COPC_APPROVED_DATE")); if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) { dto.setCopc_approved_date( utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime()))); } dto.setLine_item_description(rs.getString("LINE_ITEM_DESCRIPTION")); dto.setLoc_date(rs.getString("LOC_Date")); if (rs.getString("LOC_Date") != null && !"".equals(rs.getString("LOC_Date"))) { dto.setLoc_date((utility.showDate_Report2(dto.getLoc_date())).toUpperCase()); } dto.setAccount_manager_receive_date(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE")); if (rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE") != null && !"".equals(rs.getString("ACCOUNT_MANAGER_RECEIVE_DATE"))) { dto.setAccount_manager_receive_date( (utility.showDate_Report2(dto.getAccount_manager_receive_date())).toUpperCase()); } dto.setOrder_total(rs.getDouble("ORDER_TOTAL")); dto.setTaxation(rs.getString("TAXATION")); dto.setTaxexemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setPayment_term(rs.getString("PAYMENT_TERM")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setOld_line_item_amount(rs.getString("OLD_LINE_ITEM_AMOUNT")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setParty_name(rs.getString("PARTY_NAME")); dto.setOrder_stage_description(rs.getString("ORDER_STAGE_DESCRIPTION")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); if (rs.getString("CHARGE_END_DATE") != null && !"".equals(rs.getString("CHARGE_END_DATE"))) { dto.setCharge_end_date((utility.showDate_Report2(dto.getCharge_end_date())).toUpperCase()); } dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setNew_order_remark(rs.getString("NEW_ORDER_REMARK")); dto.setRemarks(rs.getString("REMARKS")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setOsp(rs.getString("OSP")); dto.setOpportunity_id(rs.getString("OPPORTUNITY_ID")); dto.setStore_address(rs.getString("STORE_ADDRESS")); dto.setCustomer_rfs_date(rs.getString("CUSTOMER_RFS_DATE")); if (rs.getString("CUSTOMER_RFS_DATE") != null && !"".equals(rs.getString("CUSTOMER_RFS_DATE"))) { dto.setCustomer_rfs_date((utility.showDate_Report2(dto.getCustomer_rfs_date())).toUpperCase()); } dto.setOrder_entry_date(rs.getString("ORDER_ENTRY_DATE")); if (rs.getString("ORDER_ENTRY_DATE") != null && !"".equals(rs.getString("ORDER_ENTRY_DATE"))) { dto.setOrder_entry_date((utility.showDate_Report2(dto.getOrder_entry_date())).toUpperCase()); } dto.setCkt_id(rs.getString("CKT_ID")); dto.getInterfaceid(); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { System.out.println(">>>>>>>>>>>."); } } System.out.println("DTO_>>>>>>>>>>>>>>>end>>>>>>>>>>>>>"); return docListDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<ReportsDto> viewPerformanceDetailList(ReportsDto objDto) { // Nagarjuna String methodName = "viewPerformanceDetailList", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>(); ReportsDto objReportsDto = null;/*from w w w . j a v a 2s . co m*/ int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetPerformanceDetailReport); 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.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(13, objDto.getOsp().trim()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new ReportsDto(); objReportsDto.setPartyNo(rs.getInt("PARTY_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setZoneName(rs.getString("ZONE")); objReportsDto.setCrmACcountNO(rs.getString("CRMACCOUNTNO")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setIndustrySegment(rs.getString("INDUSTRYSEGMENT")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setOrdersubtype(rs.getString("ORDER_SUBTYPE")); objReportsDto.setOrderStatus(rs.getString("STAGE")); objReportsDto.setOrderDate(rs.getString("ORDERDATE")); if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) { objReportsDto.setOrderDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDERDATE").getTime()))) .toUpperCase()); } objReportsDto.setOrderApproveDate(rs.getString("ORDER_APPROVED_DATE")); if (rs.getString("ORDER_APPROVED_DATE") != null && !"".equals(rs.getString("ORDER_APPROVED_DATE"))) { objReportsDto.setOrderApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setPublishedDate(rs.getString("PUBLISHED_DATE")); if (rs.getString("PUBLISHED_DATE") != null && !"".equals(rs.getString("PUBLISHED_DATE"))) { objReportsDto.setPublishedDate( (utility.showDate_Report(new Date(rs.getTimestamp("PUBLISHED_DATE").getTime()))) .toUpperCase()); } objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); objReportsDto.setVertical(rs.getString("VERTICAL_DETAILS")); objReportsDto.setOrderTotal(rs.getDouble("ORDER_TOTAL")); objReportsDto.setTaskName(rs.getString("TASK_NAME")); objReportsDto.setActualStartDate(rs.getString("TASKSTARTDATE")); if (rs.getString("TASKSTARTDATE") != null && !"".equals(rs.getString("TASKSTARTDATE"))) { objReportsDto.setActualStartDate( (utility.showDate_Report(new Date(rs.getTimestamp("TASKSTARTDATE").getTime()))) .toUpperCase()); } objReportsDto.setActualEndDate(rs.getString("TASKENDDATE")); if (rs.getString("TASKENDDATE") != null && !"".equals(rs.getString("TASKENDDATE"))) { objReportsDto.setActualEndDate( (utility.showDate_Report(new Date(rs.getTimestamp("TASKENDDATE").getTime()))) .toUpperCase()); } objReportsDto.setTaskNumber(rs.getInt("TASKID")); objReportsDto.setOwner(rs.getString("OWNER_NAME")); objReportsDto.setAccountMgrPhoneNo(rs.getString("PHONENO"));//changed by kalpana from long to string for bug id HYPR11042013001 objReportsDto.setEmailId(rs.getString("EMAILID")); objReportsDto.setUserName(rs.getString("USER_NAME")); objReportsDto.setTotalDays(rs.getString("TASK_DAYS")); objReportsDto.setRemarks(rs.getString("ACTION_REMARKS")); objReportsDto.setOutCome(rs.getString("OUTCOME")); objReportsDto.setOsp(rs.getString("OSP")); 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
/** * @param treeDef//from ww w .ja v a2 s .c o m * @throws SQLException */ public void convertLithoStratCustom(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName, final boolean doMapGTPIds) throws SQLException { Statement stmt = null; ResultSet rs = null; String s = ""; try { // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName); if (count < 1) return; if (hasFrame) { setProcess(0, count); } // create an ID mapper for the geography table (mainly for use in converting localities) IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID", true); if (lithoStratIdMapper == null) { UIRegistry.showError("The lithoStratIdMapper was null."); return; } IdTableMapper gtpIdMapper = IdMapperMgr.getInstance().addTableMapper("geologictimeperiod", "GeologicTimePeriodID", null, false); if (doMapGTPIds) { gtpIdMapper.clearRecords(); gtpIdMapper.mapAllIds(); } Hashtable<Integer, Integer> stratGTPIdHash = new Hashtable<Integer, Integer>(); //Hashtable<Integer, Integer> newCEIdToNewStratIdHash = new Hashtable<Integer, Integer>(); // stratigraphy2 goes here. IdHashMapper newCEIdToNewStratIdHash = IdMapperMgr.getInstance() .addHashMapper("stratigraphy_StratigraphyID_2", true); newCEIdToNewStratIdHash.setShowLogErrors(false); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } // get all of the old records // String sql = String.format("SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + // "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + // "WHERE s.SuperGroup IS NOT NULL OR s.Group IS NOT NULL OR s.Formation IS NOT NULL OR " + // "s.Member IS NOT NULL OR s.Bed IS NOT NULL ORDER BY s.StratigraphyID", srcTableName); String sql = String.format( "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + "ORDER BY s.StratigraphyID", srcTableName); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(sql); int stratsWithNoGTP = 0; int stratsWithNoMappedGTP = 0; int missingCEMapping = 0; int lithoCnt = 0; int counter = 0; // for each old record, convert the record while (rs.next()) { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } } // grab the important data fields from the old record int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent String superGroup = rs.getString(2); String lithoGroup = rs.getString(3); String formation = rs.getString(4); String member = rs.getString(5); String bed = rs.getString(6); String remarks = escapeStringLiterals(rs.getString(7)); String text1 = escapeStringLiterals(rs.getString(8)); String text2 = escapeStringLiterals(rs.getString(9)); Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null; Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null; Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null; Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null; Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null; // Check to see if there is any Litho information OR an GTP Id // If both are missing then skip the record. boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation) || isNotEmpty(member); if (!hasLithoFields && oldGTPId == null) { continue; } Integer gtpId = null; if (doMapGTPIds) { if (oldGTPId != null) { gtpId = oldGTPId; } } else { gtpId = oldStratId; } if (gtpId != null) { gtpId = gtpIdMapper.get(gtpId); if (gtpId == null) { tblWriter.logError("Old GTPID[" + gtpId + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]"); stratsWithNoMappedGTP++; } } else { stratsWithNoGTP++; } // There may not be any Litho information to add to the LithoStrat tree, // but it did have GTP Information if we got here if (hasLithoFields) { // create a new Geography object from the old data LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed, remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession); LithoStrat newStrat = getLastLithoStrat(newStrats); counter++; lithoCnt += newStrats.length; // Map Old LithoStrat ID to the new Tree Id //System.out.println(oldStratId + " " + newStrat.getLithoStratId()); if (newStrat != null) { lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); // Convert Old CEId (StratID) to new CEId, then map the new CEId -> new StratId Integer newCEId = ceMapper.get(oldStratId); if (newCEId != null) { newCEIdToNewStratIdHash.put(newCEId, newStrat.getLithoStratId()); } else { String msg = String.format( "No CE mapping for Old StratId %d, when they are a one-to-one.", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } // Map the New StratId to the new GTP Id if (gtpId != null && stratGTPIdHash.get(newStrat.getLithoStratId()) == null) { stratGTPIdHash.put(newStrat.getLithoStratId(), gtpId); // new ID to new ID } } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } } stmt.close(); System.out.println("lithoCnt: " + lithoCnt); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " Stratigraphy records"); rs.close(); Statement updateStatement = newDBConn.createStatement(); //Hashtable<Integer, Integer> ceToPCHash = new Hashtable<Integer, Integer>(); int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT Count(CollectingEventID) FROM collectingevent"); int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format("SELECT Count(CollectingEventID) FROM collectingevent " + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName)); String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt, (ceCnt - stratCnt)); tblWriter.log(msg); log.debug(msg); // Create a PaleoContext for each ColObj stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); int processCnt = BasicSQLUtils .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL"); if (frame != null) { frame.setDesc("Converting PaleoContext..."); frame.setProcess(0, processCnt); } TreeSet<Integer> missingStratIds = new TreeSet<Integer>(); int missingStrat = 0; int missingGTP = 0; int coUpdateCnt = 0; int cnt = 0; sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID"; rs = stmt.executeQuery(sql); while (rs.next()) { int coId = rs.getInt(1); // New CO Id Integer ceId = rs.getInt(2); // New CE Id // Use the new CE ID to get the new Strat Id Integer newLithoId = newCEIdToNewStratIdHash.get(ceId); Integer gtpId = null; if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); Integer oldStratID = ceMapper.reverseGet(ceId); if (oldStratID != null) { sql = "SELECT GeologicTimePeriodID FROM stratigraphy WHERE StratigraphyID = " + oldStratID; Integer oldGTPId = BasicSQLUtils.getCount(oldDBConn, sql); if (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); } } if (gtpId == null) continue; } // Use the new StratID to get the new GTP Id (ChronosStratigraphy) if (gtpId == null) { gtpId = stratGTPIdHash.get(newLithoId); if (gtpId == null) { missingGTP++; if (newLithoId == null) continue; } } try { String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) " + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + "," + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL") + ")"; updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS); Integer paleoContextID = getInsertedId(updateStatement); if (paleoContextID == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID + " WHERE CollectionObjectID = " + coId; updateStatement.executeUpdate(sqlUpdate); coUpdateCnt++; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.getMessage()); throw new RuntimeException(e); } processCnt++; if (frame != null && cnt % 100 == 0) frame.setProcess(cnt); } rs.close(); stmt.close(); if (frame != null) frame.setProcess(processCnt); msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.", missingStrat, missingGTP); tblWriter.log(msg); log.debug(msg); msg = String.format("There are %d CO records updated.", coUpdateCnt); tblWriter.log(msg); log.debug(msg); updateStatement.close(); msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d", lithoStratIdMapper.size()); tblWriter.logError(msg); log.error(msg); StringBuilder sb = new StringBuilder(); sb.append("Missing New Strat: "); if (missingStratIds.size() == 0) sb.append("None"); for (Integer id : missingStratIds) { sb.append(String.format("%d, ", id)); } tblWriter.logError(sb.toString()); log.error(sb.toString()); } catch (Exception ex) { ex.printStackTrace(); } // Now in this Step we Add the PaleoContext to the Collecting Events }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Item> getCategoryItems(long categoryID, int page, int itemsPP, int sortCol, Boolean sortDec, Boolean getImages, int numImages, String[] hasItems, long lastSeenID) throws Exception { ArrayList<Item> items = new ArrayList<Item>(); if (categoryID < 0 || page < 0 || itemsPP <= 0 || sortCol < 0) return items; if (sortDec == null || getImages == null || hasItems == null) return items; Connection conn = this.getConnection(); Date nowdate = new Date(System.currentTimeMillis()); if (conn != null) { try {//from w w w . j a v a2s . c o m PreparedStatement getPIKey = null; ResultSet pikeyrs = null; PreparedStatement getItemIDs = null; ResultSet itemids = null; PreparedStatement getItems = null; ResultSet itemsrs = null; TreeMap<Long, Long> notIn = new TreeMap<Long, Long>(); for (String not : hasItems) { try { Long l = Long.valueOf(not); notIn.put(l, l); } catch (Exception e) { } } /* * 1. get item ids to get * 2. make the IN statement * 3. get the items * 4. sort to correct order */ int factor = 1; int newitemsPP = itemsPP; int gotGood = 0; while (factor < 5 && gotGood < itemsPP) { try { String CQL = ""; newitemsPP = newitemsPP * (factor * factor); factor++; switch (sortCol) { case 1: { /* * 1. get the pikey for the last seen item * 2. get the items that are after that pikey */ long lastPIKey = 1; if (lastSeenID > 0) { getPIKey = conn.prepareStatement("SELECT pikey FROM items WHERE KEY=" + lastSeenID); pikeyrs = getPIKey.executeQuery(); if (pikeyrs.next()) { try { Long temp = pikeyrs.getLong("pikey"); if (temp != null) lastPIKey = temp; } catch (Exception e) { } } } long now = System.currentTimeMillis() * shortback; // Get items by bid price if (sortDec) { // If there is no lastPIkey for reverse price, we want to make it max so that we // get the most expensive item (but cassandra can't handle that, so make it 1 less) if (lastPIKey == 1) lastPIKey = (Long.MAX_VALUE - 1); else lastPIKey--; // price bigger first (smallest key in rev keys, smallest key=biggest price) if (categoryID > 0) { CQL = "SELECT itemid FROM revpriceitems WHERE categoryid = '" + categoryID + "' AND pikey<=" + lastPIKey + " AND itemid>" + now + " LIMIT " + newitemsPP; } else { CQL = "SELECT itemid FROM revpriceitems WHERE catzero=0 AND pikey<=" + lastPIKey + " AND itemid>" + now + " LIMIT " + newitemsPP; } } else { // lastPIKey has a min value of Long priceKey = ((long) (price*1000000000000l)) + (itemID % 10000000000l); // for an item if (lastPIKey == 1) lastPIKey = tenzero - 1; else lastPIKey++; // get the lowest priced items first (by current bid, smallest key = smallest price) if (categoryID > 0) { CQL = "SELECT itemid FROM priceitems WHERE categoryid = '" + categoryID + "' AND pikey>=" + lastPIKey + " AND itemid>=" + now + " LIMIT " + newitemsPP; } else { CQL = "SELECT itemid FROM priceitems WHERE catzero=0 AND pikey>=" + lastPIKey + " AND itemid>=" + now + " LIMIT " + newitemsPP; } } break; } default: { if (!sortDec) { // Make only current items be returned long now = System.currentTimeMillis() * shortback; if (lastSeenID < now) lastSeenID = now; else lastSeenID++; // end date getting larger, i.e. item that expires earliest is selected first if (categoryID > 0) { CQL = "SELECT itemid FROM items WHERE categoryid = '" + categoryID + "' AND itemid>=" + lastSeenID + " LIMIT " + newitemsPP; } else { CQL = "SELECT itemid FROM items WHERE KEY>=" + lastSeenID + " LIMIT " + newitemsPP; } } else { if (lastSeenID == 0) lastSeenID = (Long.MAX_VALUE - 1); else lastSeenID--; long now = System.currentTimeMillis() * shortback; // end date getting smaller, i.e. item that expires last is first to be selected if (categoryID > 0) { CQL = "SELECT itemid FROM revtimeitems WHERE categoryid = '" + categoryID + "' AND itemid<" + lastSeenID + " AND itemid>" + now + " LIMIT " + newitemsPP; } else { CQL = "SELECT itemid FROM revtimeitems WHERE catzero=0 AND KEY>" + (Long.MAX_VALUE - lastSeenID) + " AND itemid>" + now + " LIMIT " + newitemsPP; } } break; } } getItemIDs = conn.prepareStatement(CQL); itemids = getItemIDs.executeQuery(); StringBuilder ids = new StringBuilder(); ids.append("('0'"); while (itemids.next()) { Long itemid = null; try { itemid = itemids.getLong("itemid"); } catch (Exception e) { } ; if (itemid != null && !notIn.containsKey(itemid)) { ids.append(",'"); ids.append(itemid); ids.append("'"); } } ids.append(");"); getItems = conn.prepareStatement("SELECT * FROM items WHERE KEY IN " + ids.toString()); itemsrs = getItems.executeQuery(); int imgCount = 0; while (itemsrs.next() && gotGood < newitemsPP) { try { // Cassandra can fail because items don't have all the info required Item currentItem = null; try { ArrayList<Image> images = new ArrayList<Image>(); if (getImages && imgCount < numImages) { images = this.getItemImages(itemsrs.getLong("KEY")); imgCount++; } currentItem = new Item(itemsrs.getLong("KEY"), itemsrs.getString("name"), itemsrs.getString("description"), itemsrs.getInt("quantity"), itemsrs.getDouble("startprice"), itemsrs.getDouble("reserveprice"), itemsrs.getDouble("buynowprice"), itemsrs.getDouble("curbid"), itemsrs.getDouble("maxbid"), itemsrs.getInt("noofbids"), new Date(itemsrs.getLong("startdate")), new Date(itemsrs.getLong("enddate")), itemsrs.getLong("sellerid"), itemsrs.getLong("categoryid"), itemsrs.getString("thumbnail"), images); } catch (Exception e) { } if (currentItem != null) { if (!items.contains(currentItem) && currentItem.getEndDate().after(nowdate)) { items.add(currentItem); gotGood++; } } } catch (NullPointerException e) { } } // We now need to sort the items switch (sortCol) { case 1: { // lowest price first if (!sortDec) { Collections.sort(items, new Comparator<Item>() { public int compare(Item i1, Item i2) { return i1.getCurrentBid() < i2.getCurrentBid() ? -1 : 1; } }); } // highest price first, we have reversed the comparator operator else { Collections.sort(items, new Comparator<Item>() { public int compare(Item i1, Item i2) { return i1.getCurrentBid() < i2.getCurrentBid() ? 1 : -1; } }); } break; } default: { // Earliest expiration first if (!sortDec) { Collections.sort(items, new Comparator<Item>() { public int compare(Item i1, Item i2) { return i1.getEndDate().before(i2.getEndDate()) ? -1 : 1; } }); } // Latest expiration first else { Collections.sort(items, new Comparator<Item>() { public int compare(Item i1, Item i2) { return i1.getEndDate().before(i2.getEndDate()) ? 1 : -1; } }); } break; } } } catch (Exception e) { System.err.println("CassandraQuery (getCategoryItems): Could not get the items"); e.printStackTrace(); throw e; } finally { this.closeSmt(getPIKey); this.close(pikeyrs); this.closeSmt(getItemIDs); this.close(itemids); this.closeSmt(getItems); this.close(itemsrs); } } } catch (Exception e) { System.err.println("CassandraQuery (getCategoryItems): Could not get the items"); e.printStackTrace(); throw e; } finally { this.closeConnection(conn); } } return items; }
From source file:com.l2jfree.gameserver.gameobjects.L2Player.java
/** * Retrieve a L2Player from the characters table of the database and add it in _allObjects of the L2world.<BR><BR> * * <B><U> Actions</U> :</B><BR><BR> * <li>Retrieve the L2Player from the characters table of the database </li> * <li>Add the L2Player object in _allObjects </li> * <li>Set the x,y,z position of the L2Player and make it invisible</li> * <li>Update the overloaded status of the L2Player</li><BR><BR> * * @param objectId Identifier of the object to initialized * * @return The L2Player loaded from the database * *//*w w w . ja v a2s . c o m*/ public static L2Player load(int objectId) { disconnectIfOnline(objectId); L2Player player = null; Connection con = null; try { // Retrieve the L2Player from the characters table of the database con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement = con.prepareStatement(RESTORE_CHARACTER); statement.setInt(1, objectId); ResultSet rset = statement.executeQuery(); double currentHp = 1, currentMp = 1, currentCp = 1; if (rset.next()) { final int activeClassId = rset.getInt("classid"); final boolean female = rset.getInt("sex") != 0; final L2PlayerTemplate template = CharTemplateTable.getInstance().getTemplate(activeClassId); PlayerAppearance app = new PlayerAppearance(rset.getByte("face"), rset.getByte("hairColor"), rset.getByte("hairStyle"), female); player = new L2Player(objectId, template, rset.getString("account_name"), app); player.setName(rset.getString("char_name")); player._lastAccess = rset.getLong("lastAccess"); player.getStat().setExp(rset.getLong("exp")); player.setExpBeforeDeath(rset.getLong("expBeforeDeath")); player.getStat().setLevel(rset.getByte("level")); player.getStat().setSp(rset.getInt("sp")); player.setWantsPeace(rset.getInt("wantspeace")); player.setHeading(rset.getInt("heading")); player.setKarma(rset.getInt("karma")); player.setFame(rset.getInt("fame")); player.setPvpKills(rset.getInt("pvpkills")); player.setPkKills(rset.getInt("pkkills")); player.setClanJoinExpiryTime(rset.getLong("clan_join_expiry_time")); if (player.getClanJoinExpiryTime() < System.currentTimeMillis()) { player.setClanJoinExpiryTime(0); } player.setClanCreateExpiryTime(rset.getLong("clan_create_expiry_time")); if (player.getClanCreateExpiryTime() < System.currentTimeMillis()) { player.setClanCreateExpiryTime(0); } int clanId = rset.getInt("clanid"); if (clanId > 0) { player.setClan(ClanTable.getInstance().getClan(clanId)); } player.setDeleteTimer(rset.getLong("deletetime")); player.setOnlineTime(rset.getLong("onlinetime")); player.setNewbie(rset.getInt("newbie")); player.setNoble(rset.getInt("nobless") == 1); player.setTitle(rset.getString("title")); player.setAccessLevel(rset.getInt("accesslevel")); player.setFistsWeaponItem(player.findFistsWeaponItem(activeClassId)); player.setUptime(System.currentTimeMillis()); // Only 1 line needed for each and their values only have to be set once as long as you don't die before it's set. currentHp = rset.getDouble("curHp"); currentMp = rset.getDouble("curMp"); currentCp = rset.getDouble("curCp"); player._classIndex = 0; try { player.setBaseClass(rset.getInt("base_class")); } catch (Exception e) { player.setBaseClass(activeClassId); } // Restore Subclass Data (cannot be done earlier in function) if (restoreSubClassData(player)) { if (activeClassId != player.getBaseClass()) { for (SubClass subClass : player.getSubClasses().values()) if (subClass.getClassId() == activeClassId) player._classIndex = subClass.getClassIndex(); } } if (player.getClassIndex() == 0 && activeClassId != player.getBaseClass()) { // Subclass in use but doesn't exist in DB - // a possible restart-while-modifysubclass cheat has been attempted. // Switching to use base class player.setClassId(player.getBaseClass()); _log.warn("Player " + player.getName() + " reverted to base class. Possibly has tried a relogin exploit while subclassing."); } else player._activeClass = activeClassId; player.setIsIn7sDungeon(rset.getInt("isin7sdungeon") == 1); player.setInJail(rset.getInt("in_jail") == 1); player.setJailTimer(rset.getLong("jail_timer")); player.setBanChatTimer(rset.getLong("banchat_timer")); if (player.isInJail()) player.setJailTimer(rset.getLong("jail_timer")); else player.setJailTimer(0); CursedWeaponsManager.getInstance().onEnter(player); player.setNoble(rset.getBoolean("nobless")); player.setCharViP((rset.getInt("charViP") == 1)); player.setSubPledgeType(rset.getInt("subpledge")); player.setPledgeRank(rset.getInt("pledge_rank")); player.setApprentice(rset.getInt("apprentice")); player.setSponsor(rset.getInt("sponsor")); if (player.getClan() != null) { if (player.getClan().getLeaderId() != player.getObjectId()) { if (player.getPledgeRank() == 0) { player.setPledgeRank(5); } player.setClanPrivileges(player.getClan().getRankPrivs(player.getPledgeRank())); } else { player.setClanPrivileges(L2Clan.CP_ALL); player.setPledgeRank(1); } } else { player.setClanPrivileges(L2Clan.CP_NOTHING); } player.setLvlJoinedAcademy(rset.getInt("lvl_joined_academy")); player.setAllianceWithVarkaKetra(rset.getInt("varka_ketra_ally")); player.setDeathPenaltyBuffLevel(rset.getInt("death_penalty_level")); player.setVitalityPoints(rset.getInt("vitality_points"), true); // Add the L2Player object in _allObjects // L2World.getInstance().storeObject(player); // Set the x,y,z position of the L2Player and make it invisible player.getPosition().setXYZInvisible(rset.getInt("x"), rset.getInt("y"), rset.getInt("z")); // Set Teleport Bookmark Slot player.setBookMarkSlot(rset.getInt("BookmarkSlot")); } rset.close(); statement.close(); if (player == null) return null; // Retrieve from the database all secondary data of this L2Player // and reward expertise/lucky skills if necessary. player.restoreCharData(); player.rewardSkills(); // Buff and status icons player.getEffects().restoreEffects(); player.restoreSkillReuses(); player.stopEffects(L2EffectType.HEAL_OVER_TIME); player.stopEffects(L2EffectType.COMBAT_POINT_HEAL_OVER_TIME); // Restore current Cp, HP and MP values player.getStatus().setCurrentCp(currentCp); player.getStatus().setCurrentHp(currentHp); player.getStatus().setCurrentMp(currentMp); if (currentHp < 0.5) { player.setIsDead(true); player.getStatus().stopHpMpRegeneration(); } // Restore pet if exists in the world player.setPet(L2World.getInstance().getPet(player.getObjectId())); if (player.getPet() != null) player.getPet().setOwner(player); // refresh overloaded already done when loading inventory // Update the expertise status of the L2Player player.refreshExpertisePenalty(); } catch (Exception e) { _log.error("Failed loading character.", e); } finally { L2DatabaseFactory.close(con); } return player; }