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.sfs.whichdoctor.analysis.RevenueAnalysisDAOImpl.java
/** * Load debit./*from www .j a v a 2 s . c om*/ * * @param rs the rs * * @return the debit bean */ private DebitBean loadDebit(final ResultSet rs) { DebitBean debit = null; try { debit = new DebitBean(); debit.setGUID(rs.getInt("InvoiceGUID")); debit.setNumber(rs.getString("InvoiceNo")); debit.setAbbreviation(rs.getString("InvoiceAbbreviation")); debit.setDescription(rs.getString("InvoiceDescription")); try { debit.setIssued(rs.getDate("InvoiceIssued")); } catch (SQLException e) { debit.setIssued(null); } if (rs.getInt("InvoicePersonGUID") > 0) { // Member exists so load details PersonBean person = new PersonBean(); person.setGUID(rs.getInt("InvoicePersonGUID")); person.setPersonIdentifier(rs.getInt("InvoicePersonIdentifier")); person.setPreferredName(rs.getString("InvoicePreferredName")); person.setLastName(rs.getString("InvoiceLastName")); debit.setPerson(person); } if (rs.getInt("InvoiceOrganisationGUID") > 0) { // Organisation exists so load details OrganisationBean organisation = new OrganisationBean(); organisation.setGUID(rs.getInt("InvoiceOrganisationGUID")); organisation.setName(rs.getString("InvoiceOrganisation")); debit.setOrganisation(organisation); } debit.setValue(rs.getDouble("InvoiceValue")); debit.setNetValue(rs.getDouble("InvoiceNetValue")); debit.setGSTRate(rs.getDouble("InvoiceGSTRate")); debit.setCancelled(rs.getBoolean("InvoiceCancelled")); } catch (SQLException sqe) { dataLogger.error("Error loading debit details: " + sqe.getMessage()); } return debit; }
From source file:com.exploringspatial.com.exploringspatial.dao.mapping.ConflictRowMapper.java
@Override public Conflict mapRow(ResultSet resultSet, int i) throws SQLException { final Conflict conflict = new Conflict(); conflict.setActor1(resultSet.getString(ConflictColumns.ACTOR1.name())); conflict.setActor2(resultSet.getString(ConflictColumns.ACTOR2.name())); conflict.setAdmin1(resultSet.getString(ConflictColumns.ADMIN1.name())); conflict.setAdmin2(resultSet.getString(ConflictColumns.ADMIN2.name())); conflict.setAdmin3(resultSet.getString(ConflictColumns.ADMIN3.name())); conflict.setAllyActor1(resultSet.getString(ConflictColumns.ALLY_ACTOR_1.name())); conflict.setAllyActor2(resultSet.getString(ConflictColumns.ALLY_ACTOR_2.name())); conflict.setCountry(resultSet.getString(ConflictColumns.COUNTRY.name())); final String date = resultSet.getString(ConflictColumns.EVENT_DATE.name()); if (date != null && !date.isEmpty()) { final DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy"); try {// w w w. jav a 2 s. c om conflict.setEventDate(dateFormat.parse(date)); } catch (ParseException e) { log.warn("Could not parse Date: '" + date + "'"); } } conflict.setEventIdCountry(resultSet.getString(ConflictColumns.EVENT_ID_CNTY.name())); conflict.setEventPk(resultSet.getLong(ConflictColumns.EVENT_ID_NO_CNTY.name())); conflict.setEventType(resultSet.getString(ConflictColumns.EVENT_TYPE.name())); conflict.setFatalities(resultSet.getInt(ConflictColumns.FATALITIES.name())); conflict.setGeoPrecision(resultSet.getInt(ConflictColumns.GEO_PRECIS.name())); conflict.setGwno(resultSet.getInt(ConflictColumns.GWNO.name())); conflict.setInter1(resultSet.getInt(ConflictColumns.INTER1.name())); conflict.setInter2(resultSet.getInt(ConflictColumns.INTER2.name())); conflict.setInteraction(resultSet.getInt(ConflictColumns.INTERACTION.name())); conflict.setLatitude(resultSet.getDouble(ConflictColumns.LATITUDE.name())); conflict.setLongitude(resultSet.getDouble(ConflictColumns.LONGITUDE.name())); conflict.setLocation(resultSet.getString(ConflictColumns.LOCATION.name())); conflict.setNotes(resultSet.getString(ConflictColumns.NOTES.name())); conflict.setSource(resultSet.getString(ConflictColumns.SOURCE.name())); conflict.setTimePrecision(resultSet.getInt(ConflictColumns.TIME_PRECISION.name())); conflict.setYear(resultSet.getInt(ConflictColumns.YEAR.name())); return conflict; }
From source file:librec.data.DataDAO.java
/** * Read data from the database. Note that we don't take care of duplicate lines. * /* ww w .j ava 2 s . com*/ * @return a sparse matrix storing all the relevant data */ public SparseMatrix[] loadData(double binThold, int maxIds) throws Exception { Logs.info("Dataset: From database"); // Table {row-id, col-id, rate} Table<Integer, Integer, Double> dataTable = HashBasedTable.create(); // Table {row-id, col-id, timestamp} Table<Integer, Integer, Long> timeTable = null; // Map {col-id, multiple row-id}: used to fast build a rating matrix Multimap<Integer, Integer> colMap = HashMultimap.create(); DatabaseManager dbm = new DatabaseManager(); Connection conn = null; PreparedStatement stmnt = null; ResultSet rs = null; minTimestamp = Long.MAX_VALUE; maxTimestamp = Long.MIN_VALUE; try { conn = dbm.getConnection(); if (maxIds > 0) { stmnt = conn.prepareStatement( "SELECT UserId, ItemId, Time, Rating FROM Rating WHERE ItemId < ? AND UserId < ?;"); stmnt.setInt(1, maxIds); stmnt.setInt(2, maxIds); } else { stmnt = conn.prepareStatement("SELECT UserId, ItemId, Time, Rating FROM Rating;"); } // Logs.info("Executing statement: {}", stmnt); rs = stmnt.executeQuery(); while (rs.next()) { int user = rs.getInt("UserId"); int item = rs.getInt("ItemId"); // convert time to milliseconds long mms = rs.getTimestamp("Time").getTime(); long timestamp = timeUnit.toMillis(mms); Double rate = rs.getDouble("Rating"); // binarize the rating for item recommendation task if (binThold >= 0) rate = rate > binThold ? 1.0 : 0.0; scaleDist.add(rate); // inner id starting from 0 int row = userIds.containsKey(user) ? userIds.get(user) : userIds.size(); userIds.put(user, row); int col = itemIds.containsKey(item) ? itemIds.get(item) : itemIds.size(); itemIds.put(item, col); dataTable.put(row, col, rate); colMap.put(col, row); // record rating's issuing time if (timeTable == null) timeTable = HashBasedTable.create(); if (minTimestamp > timestamp) minTimestamp = timestamp; if (maxTimestamp < timestamp) maxTimestamp = timestamp; timeTable.put(row, col, timestamp); // if(dataTable.size() % 100000 == 0) // Logs.info("Ratings loaded into dataTable: {}", dataTable.size()); } } catch (SQLException e) { e.printStackTrace(); } finally { DbUtils.closeQuietly(stmnt); DbUtils.closeQuietly(conn); DbUtils.closeQuietly(rs); } // Logs.info("All ratings loaded into dataTable"); numRatings = scaleDist.size(); ratingScale = new ArrayList<>(scaleDist.elementSet()); Collections.sort(ratingScale); int numRows = numUsers(), numCols = numItems(); // if min-rate = 0.0, shift upper a scale double minRate = ratingScale.get(0).doubleValue(); double epsilon = minRate == 0.0 ? ratingScale.get(1).doubleValue() - minRate : 0; if (epsilon > 0) { // shift upper a scale for (int i = 0, im = ratingScale.size(); i < im; i++) { double val = ratingScale.get(i); ratingScale.set(i, val + epsilon); } // update data table for (int row = 0; row < numRows; row++) { for (int col = 0; col < numCols; col++) { if (dataTable.contains(row, col)) dataTable.put(row, col, dataTable.get(row, col) + epsilon); } } } String dateRange = ""; dateRange = String.format(", Timestamps = {%s, %s}", Dates.toString(minTimestamp), Dates.toString(maxTimestamp)); Logs.debug("With Specs: {Users, {}} = {{}, {}, {}}, Scale = {{}}{}", (isItemAsUser ? "Users, Links" : "Items, Ratings"), numRows, numCols, numRatings, Strings.toString(ratingScale), dateRange); // build rating matrix rateMatrix = new SparseMatrix(numRows, numCols, dataTable, colMap); if (timeTable != null) timeMatrix = new SparseMatrix(numRows, numCols, timeTable, colMap); // release memory of data table dataTable = null; timeTable = null; return new SparseMatrix[] { rateMatrix, timeMatrix }; }
From source file:gsn.wrappers.JDBCWrapper.java
public void run() { DataEnumerator data;/* w w w. j a va 2s .c o m*/ try { Thread.sleep(2000); } catch (InterruptedException e) { logger.error(e.getMessage(), e); } Connection conn = null; ResultSet resultSet = null; while (isActive()) { try { conn = sm.getConnection(); StringBuilder query = new StringBuilder("select * from ").append(table_name) .append(" where timed > " + latest_timed + " limit 0," + buffer_size); resultSet = sm.executeQueryWithResultSet(query, conn); //logger.debug(query); while (resultSet.next()) { Serializable[] output = new Serializable[this.getOutputFormat().length]; long pk = resultSet.getLong(1); long timed = resultSet.getLong(2); //logger.warn("pk => "+ pk); //logger.warn("timed => "+ timed); for (int i = 0; i < dataFieldsLength; i++) { switch (dataFieldTypes[i]) { case DataTypes.VARCHAR: case DataTypes.CHAR: output[i] = resultSet.getString(i + 3); break; case DataTypes.INTEGER: output[i] = resultSet.getInt(i + 3); break; case DataTypes.TINYINT: output[i] = resultSet.getByte(i + 3); break; case DataTypes.SMALLINT: output[i] = resultSet.getShort(i + 3); break; case DataTypes.DOUBLE: output[i] = resultSet.getDouble(i + 3); break; case DataTypes.FLOAT: output[i] = resultSet.getFloat(i + 3); break; case DataTypes.BIGINT: output[i] = resultSet.getLong(i + 3); break; case DataTypes.BINARY: output[i] = resultSet.getBytes(i + 3); break; } //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]); } StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed); latest_timed = se.getTimeStamp(); //logger.warn(" Latest => " + latest_timed); this.postStreamElement(se); updateCheckPointFile(latest_timed); //logger.warn(se); } } catch (java.io.IOException e) { logger.error(e.getMessage(), e); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { sm.close(resultSet); sm.close(conn); } try { Thread.sleep(rate); } catch (InterruptedException e) { logger.error(e.getMessage(), e); } } }
From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public Item getItem(final int uid) { Item obj = null;//from w w w. j av a 2s.c o m Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETITEM (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID")) .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER")) .setName(rs.getString("NAME")).setDescription(rs.getString("DESCRIPTION")) .setCategory(rs.getString("CATEGORY")).setSeller(rs.getString("SELLER")) .setValPrice(rs.getDouble("VALPRICE")).setMinPrice(rs.getDouble("MINPRICE")) .setIncPrice(rs.getDouble("INCPRICE")).sertCurPrice(rs.getDouble("CURPRICE")) .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT")) .setWatchCount(rs.getInt("WATCHCOUNT")).setUrl(rs.getString("URL")) .setMultiSale(rs.getBoolean("MULTI")); obj = builder.build(); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("ITEM [method:{} result:{}]", new Object[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; }
From source file:com.l2jfree.gameserver.model.sevensigns.SevenSigns.java
/** * Restores all Seven Signs data and settings, usually called at server startup. * /*ww w. ja va 2 s . c om*/ * @throws Exception */ protected void restoreSevenSignsData() { Connection con = null; try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement = con .prepareStatement("SELECT charId, cabal, seal, red_stones, green_stones, blue_stones, " + "ancient_adena_amount, contribution_score FROM seven_signs"); ResultSet rset = statement.executeQuery(); while (rset.next()) { int charObjId = rset.getInt("charId"); StatsSet sevenDat = new StatsSet(); sevenDat.set("charId", charObjId); sevenDat.set("cabal", rset.getString("cabal")); sevenDat.set("seal", rset.getInt("seal")); sevenDat.set("red_stones", rset.getInt("red_stones")); sevenDat.set("green_stones", rset.getInt("green_stones")); sevenDat.set("blue_stones", rset.getInt("blue_stones")); sevenDat.set("ancient_adena_amount", rset.getDouble("ancient_adena_amount")); sevenDat.set("contribution_score", rset.getDouble("contribution_score")); if (_log.isDebugEnabled()) _log.info("SevenSigns: Loaded data from DB for char ID " + charObjId + " (" + sevenDat.getString("cabal") + ")"); _signsPlayerData.put(charObjId, sevenDat); } rset.close(); statement.close(); statement = con.prepareStatement("SELECT * FROM seven_signs_status WHERE id=0"); rset = statement.executeQuery(); while (rset.next()) { _currentCycle = rset.getInt("current_cycle"); _activePeriod = rset.getInt("active_period"); _previousWinner = rset.getInt("previous_winner"); _dawnStoneScore = rset.getDouble("dawn_stone_score"); _dawnFestivalScore = rset.getInt("dawn_festival_score"); _duskStoneScore = rset.getDouble("dusk_stone_score"); _duskFestivalScore = rset.getInt("dusk_festival_score"); _signsSealOwners.put(SEAL_AVARICE, rset.getInt("avarice_owner")); _signsSealOwners.put(SEAL_GNOSIS, rset.getInt("gnosis_owner")); _signsSealOwners.put(SEAL_STRIFE, rset.getInt("strife_owner")); _signsDawnSealTotals.put(SEAL_AVARICE, rset.getInt("avarice_dawn_score")); _signsDawnSealTotals.put(SEAL_GNOSIS, rset.getInt("gnosis_dawn_score")); _signsDawnSealTotals.put(SEAL_STRIFE, rset.getInt("strife_dawn_score")); _signsDuskSealTotals.put(SEAL_AVARICE, rset.getInt("avarice_dusk_score")); _signsDuskSealTotals.put(SEAL_GNOSIS, rset.getInt("gnosis_dusk_score")); _signsDuskSealTotals.put(SEAL_STRIFE, rset.getInt("strife_dusk_score")); } rset.close(); statement.close(); statement = con.prepareStatement("UPDATE seven_signs_status SET date=? WHERE id=0"); statement.setInt(1, Calendar.getInstance().get(Calendar.DAY_OF_WEEK)); statement.execute(); statement.close(); } catch (SQLException e) { _log.fatal("SevenSigns: Unable to load Seven Signs data from database: ", e); } finally { L2DatabaseFactory.close(con); } // Festival data is loaded now after the Seven Signs engine data. }
From source file:gsn.wrappers.TetraedreNMCWrapper.java
public void run() { DataEnumerator data;/*from w w w . j a v a 2 s. c o m*/ try { Thread.sleep(2000); } catch (InterruptedException e) { logger.error(e.getMessage(), e); } Connection conn = null; ResultSet resultSet = null; while (isActive()) { try { conn = sm.getConnection(); StringBuilder query = new StringBuilder("select * from ").append(table_name).append( " where timestamp*1000 > " + latest_timed + " order by timestamp limit 0," + buffer_size); resultSet = sm.executeQueryWithResultSet(query, conn); //logger.debug(query); while (resultSet.next()) { Serializable[] output = new Serializable[this.getOutputFormat().length]; //long pk = resultSet.getLong(1); long timed = resultSet.getLong(1) * 1000; //logger.warn("pk => "+ pk); //logger.warn("timed => "+ timed); for (int i = 0; i < dataFieldsLength; i++) { switch (dataFieldTypes[i]) { case DataTypes.VARCHAR: case DataTypes.CHAR: output[i] = resultSet.getString(i + 1); break; case DataTypes.INTEGER: output[i] = resultSet.getInt(i + 1); break; case DataTypes.TINYINT: output[i] = resultSet.getByte(i + 1); break; case DataTypes.SMALLINT: output[i] = resultSet.getShort(i + 1); break; case DataTypes.DOUBLE: output[i] = resultSet.getDouble(i + 1); break; case DataTypes.FLOAT: output[i] = resultSet.getFloat(i + 1); break; case DataTypes.BIGINT: output[i] = resultSet.getLong(i + 1); break; case DataTypes.BINARY: output[i] = resultSet.getBytes(i + 1); break; } //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]); } StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed); latest_timed = se.getTimeStamp(); //logger.warn(" Latest => " + latest_timed); this.postStreamElement(se); updateCheckPointFile(latest_timed); //logger.warn(se); } } catch (IOException e) { logger.error(e.getMessage(), e); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { sm.close(resultSet); sm.close(conn); } try { Thread.sleep(rate); } catch (InterruptedException e) { logger.error(e.getMessage(), e); } } }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;/* w w w . j a va 2 s. c o m*/ try { // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:Report_PRCR_New_EPF_Excel_File_Generator.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {// www . ja v a 2 s . c o m DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location; if (mv.SoftwareVersion() == 1) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } else if (mv.SoftwareVersion() == 2) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "12", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "11", "location")), new File(employee_detail_file_location)); } else { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String epf_backup_year_month = year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0) / 100.0; double employer_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; double tot_contribution = 0; double employers_contribution = 0; double member_contribution = 0; double tot_earnings = 0; String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } ResultSet query = dbm.query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 "); // AND `total_pay` > 0"); while (query.next()) { if (query.getDouble("total_pay") <= 0 && dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", query.getInt("code")) != 1) { continue; } ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); d_o_code = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no) == 1) { tot_earnings = tot_earnings + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no, "total_pay")); } employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; tot_contribution = employers_contribution + member_contribution; total_member_contribution = total_member_contribution + tot_contribution; normal_days = query.getInt("normal_days"); sundays = query.getInt("sundays"); /* ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } */ no_of_days_worked = normal_days + sundays; if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } Row row = sheet.getRow(1 + count); if (row == null) { row = sheet.createRow(1 + count); } for (int k = 0; k < 16; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employers_contribution); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; case 7: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_earnings); break; case 8: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(member_status); break; case 9: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 10: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 11: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 12: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 13: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(no_of_days_worked); break; case 14: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(occupation_classification_grade); break; case 15: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(d_o_code); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:de.ingrid.importer.udk.strategy.v32.IDCStrategy3_2_0.java
private void updateDQDatendefizit() throws Exception { log.info("\nUpdating object_data_quality 'Datendefizit'..."); log.info(/*w ww .ja v a 2 s . c om*/ "Transfer 'Datendefizit' value from DQ table (object_data_quality) to DQ field (t011_obj_geo.rec_grade) if field is empty ..."); // NOTICE: We do NOT update search index due to same values. // select all relevant entries in DQ Table String sqlSelectDQTable = "select obj_id, result_value from object_data_quality where dq_element_id = 110"; // select according value in DQ Field PreparedStatement psSelectDQField = jdbc .prepareStatement("SELECT rec_grade FROM t011_obj_geo WHERE obj_id = ?"); // update according value in DQ Field PreparedStatement psUpdateDQField = jdbc .prepareStatement("UPDATE t011_obj_geo SET " + "rec_grade = ? " + "WHERE obj_id = ?"); Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sqlSelectDQTable, st); int numProcessed = 0; while (rs.next()) { long objId = rs.getLong("obj_id"); String dqTableValue = rs.getString("result_value"); if (dqTableValue != null) { // read according value from field psSelectDQField.setLong(1, objId); ResultSet rs2 = psSelectDQField.executeQuery(); if (rs2.next()) { // just read it to check if null ! double fieldValue = rs2.getDouble("rec_grade"); boolean fieldValueWasNull = rs2.wasNull(); log.debug("Object id=" + objId + " -> read DQ table value=" + dqTableValue + " / value in field Datendefizit=" + (fieldValueWasNull ? null : fieldValue)); if (fieldValueWasNull) { try { psUpdateDQField.setDouble(1, new Double(dqTableValue)); psUpdateDQField.setLong(2, objId); psUpdateDQField.executeUpdate(); numProcessed++; log.debug("Transferred 'Datendefizit' value '" + dqTableValue + "' from DQ table to field (was empty), obj_id:" + objId); } catch (Exception ex) { String msg = "Problems transferring 'Datendefizit' value '" + dqTableValue + "' from DQ table as DOUBLE to field, value is lost ! obj_id:" + objId; log.error(msg, ex); System.out.println(msg); } } } rs2.close(); } } rs.close(); st.close(); psSelectDQField.close(); psUpdateDQField.close(); log.info("Transferred " + numProcessed + " entries... done"); log.info("Delete 'Datendefizit' values from DQ table (object_data_quality) ..."); sqlStr = "DELETE FROM object_data_quality where dq_element_id = 110"; int numDeleted = jdbc.executeUpdate(sqlStr); log.debug("Deleted " + numDeleted + " entries."); log.info("Updating object_data_quality 'Datendefizit' ... done\n"); }