List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:edu.jhuapl.openessence.datasource.jdbc.JdbcOeDataSource.java
protected void setArguments(List<Object> arguments, PreparedStatement pStmt) throws SQLException { int argCount = 1; for (Object o : arguments) { // TODO NEED TO ADDRESS THE USE CASES FOR THIS null...POKUAM1...what if not nullable column? if (o == null) { pStmt.setObject(argCount, null); } else if (o instanceof java.sql.Timestamp) { pStmt.setTimestamp(argCount, (java.sql.Timestamp) o); } else if (o instanceof java.util.Date) { pStmt.setTimestamp(argCount, new java.sql.Timestamp(((java.util.Date) o).getTime())); } else if (o instanceof Integer) { pStmt.setInt(argCount, (Integer) o); } else if (o instanceof Long) { pStmt.setLong(argCount, (Long) o); } else if (o instanceof Float) { pStmt.setFloat(argCount, (Float) o); } else if (o instanceof Double) { pStmt.setDouble(argCount, (Double) o); } else if (o instanceof String) { pStmt.setString(argCount, (String) o); } else if (o instanceof Boolean) { pStmt.setBoolean(argCount, (Boolean) o); } else {/* w w w .ja v a 2 s . co m*/ throw new AssertionError("Unexpected object " + o + " " + o.getClass()); } argCount += 1; } }
From source file:edu.ku.brc.specify.toycode.mexconabio.MexConvToSQLNew.java
/** * /* www. j a va 2 s . c o m*/ */ public void convertFROMCSV(final ConfigureCSV config, final CsvReader csv) { String str = ""; int strLen = 0; int inx = 0; CsvReader csvObj = null; Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/mex?characterEncoding=UTF-8&autoReconnect=true", "root", "root"); stmt = conn.createStatement(); StringBuilder pStmtStr = new StringBuilder(); StringBuilder sb = new StringBuilder(); StringBuilder ques = new StringBuilder(); int[] fieldLengths = null; BasicSQLUtils.deleteAllRecordsFromTable(conn, "mex", SERVERTYPE.MySQL); Vector<Integer> types = new Vector<Integer>(); Vector<String> names = new Vector<String>(); File file = new File("/Users/rods/Documents/Untitled.mer"); Element root = XMLHelper.readFileToDOM4J(new File("/Users/rods/Documents/Acer.xml")); if (root != null) { fieldLengths = new int[csv.getColumnCount()]; for (int i = 0; i < fieldLengths.length; i++) { fieldLengths[i] = 0; } int row = 0; while (csv.readRecord()) { row++; for (int col = 0; col < csv.getColumnCount(); col++) { String dataObj = csv.get(col); int len = dataObj.length() + 1; if (len > fieldLengths[inx]) { fieldLengths[inx] = len; } inx++; } if (row % 10000 == 0) System.out.println(row); } System.out.println("--------------"); HashMap<String, Integer> hashSize = new HashMap<String, Integer>(); for (int i = 0; i < names.size(); i++) { hashSize.put(names.get(i), fieldLengths[i]); System.out.println(names.get(i) + " -> " + fieldLengths[i]); } sb.append("CREATE TABLE mex ("); List<?> items = root.selectNodes("/FIELDS/FIELD"); //$NON-NLS-1$ System.out.println(items.size()); inx = 0; for (Iterator<?> capIter = items.iterator(); capIter.hasNext();) { Element fieldNode = (Element) capIter.next(); String nullOK = fieldNode.attributeValue("EMPTYOK"); //$NON-NLS-1$ String fldName = fixName(fieldNode.attributeValue("NAME").trim()); //$NON-NLS-1$ String type = fieldNode.attributeValue("TYPE"); //$NON-NLS-1$ sb.append("`"); sb.append(fldName); sb.append("` "); System.err.println("[" + fldName + "]"); int len = hashSize.get(fldName); if (pStmtStr.length() > 0) pStmtStr.append(','); pStmtStr.append("`" + fldName + "`"); if (ques.length() > 0) ques.append(','); ques.append("?"); if (type.equals("TEXT")) { if (StringUtils.contains(fldName, "img folder")) { sb.append("longtext "); } else { sb.append("VARCHAR(" + len + ") CHARACTER SET utf8 "); } types.add(DataType.TEXT.ordinal()); } else if (type.equals("NUMBER")) { sb.append("DOUBLE "); types.add(DataType.NUMBER.ordinal()); } else if (type.equals("DATE")) { sb.append("DATE "); types.add(DataType.DATE.ordinal()); } else if (type.equals("TIME")) { sb.append("VARCHAR(16) "); types.add(DataType.TIME.ordinal()); } else { System.err.println("Unhandled Type[" + type + "]"); } sb.append(nullOK.equals("YES") ? "DEFAULT NULL," : ","); sb.append("\n"); inx++; } sb.setLength(sb.length() - 2); sb.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8;"); } SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); int rowCnt = 0; try { String stmtStr = "INSERT INTO mex (" + pStmtStr + ") VALUES(" + ques + ")"; System.out.println(stmtStr); try { stmt.executeUpdate("DROP TABLE mex"); } catch (SQLException e) { } System.err.println(sb.toString()); stmt.executeUpdate(sb.toString()); PreparedStatement pStmt = conn.prepareStatement(stmtStr); csv.close(); csvObj = new CsvReader(new FileInputStream(config.getFile()), config.getDelimiter(), config.getCharset()); csvObj.readRecord(); // skip header int row = 0; while (csvObj.readRecord()) { row++; for (int col = 0; col < csvObj.getColumnCount(); col++) { String dataStr = csvObj.get(col); strLen = dataStr.length(); switch (types.get(inx)) { case 3: case 0: { if (strLen > 0) { if (strLen <= fieldLengths[inx]) { pStmt.setString(col, dataStr); } else { System.err.println(String.format("The data for `%s` (%d) is too big %d", names.get(inx), fieldLengths[inx], strLen)); pStmt.setString(col, null); } } else { pStmt.setString(col, null); } } break; case 1: { if (StringUtils.isNotEmpty(dataStr)) { if (StringUtils.isNumeric(dataStr)) { pStmt.setDouble(col, strLen > 0 ? Double.parseDouble(dataStr) : null); } else { System.err.println(col + " Bad Number[" + dataStr + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case 2: { try { if (StringUtils.isNotEmpty(dataStr)) { if (StringUtils.contains(dataStr, "/")) { dataStr = StringUtils.replace(dataStr, "/", "-"); } else if (StringUtils.contains(dataStr, " ")) { dataStr = StringUtils.replace(dataStr, " ", "-"); } pStmt.setDate(col, strLen > 0 ? new java.sql.Date(sdf.parse(dataStr).getTime()) : null); } else { pStmt.setDate(col, null); } } catch (Exception ex) { System.err.println(col + " Bad Date[" + dataStr + "]\n" + str); pStmt.setDate(col, null); } } break; default: { System.err.println("Error - " + types.get(inx)); } } inx++; col++; } pStmt.execute(); row++; } } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { System.err.println("Row: " + rowCnt); System.err.println(str); System.err.println(inx + " " + fieldLengths[inx] + " - Field Len: " + strLen); e.printStackTrace(); } finally { if (csvObj != null) { csvObj.close(); } } } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
From source file:com.act.lcms.db.model.MS1ScanForWellAndMassCharge.java
protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow, Integer plateColumn, Boolean useSNR, String lcmsScanFileDir, String chemicalName, List<String> metlinIons, Map<String, List<XZ>> ionsToSpectra, Map<String, Double> ionsToIntegral, Map<String, Double> ionsToMax, Map<String, Double> ionsToLogSNR, Map<String, Double> ionsToAvgSignal, Map<String, Double> ionsToAvgAmbient, Map<String, Double> individualMaxIntensities, Double maxYAxis) throws SQLException, IOException { stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId); stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow); stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn); stmt.setBoolean(DB_FIELD.USE_SNR.getInsertUpdateOffset(), useSNR); stmt.setString(DB_FIELD.SCAN_FILE.getInsertUpdateOffset(), lcmsScanFileDir); stmt.setString(DB_FIELD.CHEMICAL_NAME.getInsertUpdateOffset(), chemicalName); stmt.setString(DB_FIELD.METLIN_IONS.getInsertUpdateOffset(), OBJECT_MAPPER.writeValueAsString(metlinIons)); stmt.setBytes(DB_FIELD.IONS_TO_SPECTRA.getInsertUpdateOffset(), serialize(ionsToSpectra)); stmt.setBytes(DB_FIELD.IONS_TO_INTEGRAL.getInsertUpdateOffset(), serialize(ionsToIntegral)); stmt.setBytes(DB_FIELD.IONS_TO_LOG_SNR.getInsertUpdateOffset(), serialize(ionsToLogSNR)); stmt.setBytes(DB_FIELD.IONS_TO_AVG_AMBIENT.getInsertUpdateOffset(), serialize(ionsToAvgAmbient)); stmt.setBytes(DB_FIELD.IONS_TO_AVG_SIGNAL.getInsertUpdateOffset(), serialize(ionsToAvgSignal)); stmt.setBytes(DB_FIELD.INDIVIDUAL_MAX_INTENSITIES.getInsertUpdateOffset(), serialize(individualMaxIntensities)); stmt.setBytes(DB_FIELD.IONS_TO_MAX.getInsertUpdateOffset(), serialize(ionsToMax)); stmt.setDouble(DB_FIELD.MAX_Y_AXIS.getInsertUpdateOffset(), maxYAxis); }
From source file:org.infoglue.cms.util.workflow.InfoGlueJDBCPropertySet.java
private void setValues(PreparedStatement ps, int type, String key, Object value) throws SQLException, PropertyException { // Patched by Edson Richter for MS SQL Server JDBC Support! String driverName;//from w w w . j a v a 2 s . c om try { driverName = ps.getConnection().getMetaData().getDriverName().toUpperCase(); } catch (Exception e) { driverName = ""; } ps.setNull(1, Types.VARCHAR); ps.setNull(2, Types.TIMESTAMP); // Patched by Edson Richter for MS SQL Server JDBC Support! // Oracle support suggestion also Michael G. Slack if ((driverName.indexOf("SQLSERVER") >= 0) || (driverName.indexOf("ORACLE") >= 0)) { ps.setNull(3, Types.BINARY); } else { ps.setNull(3, Types.BLOB); } ps.setNull(4, Types.FLOAT); ps.setNull(5, Types.NUMERIC); ps.setInt(6, type); ps.setString(7, globalKey); ps.setString(8, key); switch (type) { case PropertySet.BOOLEAN: Boolean boolVal = (Boolean) value; ps.setInt(5, boolVal.booleanValue() ? 1 : 0); break; case PropertySet.DATA: Data data = (Data) value; ps.setBytes(3, data.getBytes()); break; case PropertySet.DATE: Date date = (Date) value; ps.setTimestamp(2, new Timestamp(date.getTime())); break; case PropertySet.DOUBLE: Double d = (Double) value; ps.setDouble(4, d.doubleValue()); break; case PropertySet.INT: Integer i = (Integer) value; ps.setInt(5, i.intValue()); break; case PropertySet.LONG: Long l = (Long) value; ps.setLong(5, l.longValue()); break; case PropertySet.STRING: ps.setString(1, (String) value); break; default: throw new PropertyException("This type isn't supported!"); } if (valueMap == null) valueMap = new HashMap(); if (typeMap == null) typeMap = new HashMap(); valueMap.put(key, value); typeMap.put(key, new Integer(type)); }
From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java
protected int loadWhse(int whseKount) { try {//from w ww . j a va 2 s .com PreparedStatement whsePrepStmt = getInsertStatement(TPCCConstants.TABLENAME_WAREHOUSE); now = new java.util.Date(); LOG.debug("\nStart Whse Load for " + whseKount + " Whses @ " + now + " ..."); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "warehouse.csv")); LOG.debug("\nWriting Warehouse file to: " + fileLocation + "warehouse.csv"); } Warehouse warehouse = new Warehouse(); for (int i = 1; i <= whseKount; i++) { warehouse.w_id = i; warehouse.w_ytd = 300000; // random within [0.0000 .. 0.2000] warehouse.w_tax = (float) ((TPCCUtil.randomNumber(0, 2000, gen)) / 10000.0); warehouse.w_name = TPCCUtil.randomStr(TPCCUtil.randomNumber(6, 10, gen)); warehouse.w_street_1 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); warehouse.w_street_2 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); warehouse.w_city = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); warehouse.w_state = TPCCUtil.randomStr(3).toUpperCase(); warehouse.w_zip = "123456789"; if (outputFiles == false) { whsePrepStmt.setLong(1, warehouse.w_id); whsePrepStmt.setDouble(2, warehouse.w_ytd); whsePrepStmt.setDouble(3, warehouse.w_tax); whsePrepStmt.setString(4, warehouse.w_name); whsePrepStmt.setString(5, warehouse.w_street_1); whsePrepStmt.setString(6, warehouse.w_street_2); whsePrepStmt.setString(7, warehouse.w_city); whsePrepStmt.setString(8, warehouse.w_state); whsePrepStmt.setString(9, warehouse.w_zip); whsePrepStmt.executeUpdate(); } else { String str = ""; str = str + warehouse.w_id + ","; str = str + warehouse.w_ytd + ","; str = str + warehouse.w_tax + ","; str = str + warehouse.w_name + ","; str = str + warehouse.w_street_1 + ","; str = str + warehouse.w_street_2 + ","; str = str + warehouse.w_city + ","; str = str + warehouse.w_state + ","; str = str + warehouse.w_zip; out.println(str); } } // end for transCommit(); now = new java.util.Date(); long tmpTime = new java.util.Date().getTime(); LOG.debug("Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000)); lastTimeMS = tmpTime; LOG.debug("End Whse Load @ " + now); } catch (SQLException se) { LOG.debug(se.getMessage()); transRollback(); } catch (Exception e) { e.printStackTrace(); transRollback(); } return (whseKount); }
From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java
/** * Register player//w w w . j a v a 2 s . c o m * * @param encryptedPassword player's encrypted password * @return a LoginResponse to send the CommandSender ("LOGIN_WENT_ASYNCHRONOUS_SUCCESSFULLY" is a success) * @see Util#encryptString(String) for password encrypting */ public OnlinePlayer.LoginResponse registerPlayer(@NotNull final OnlinePlayer onlinePlayer, @NotNull final String encryptedPassword) { final OfflinePlayer offlinePlayer = onlinePlayer.getOfflinePlayer(); // Check if player is registered if (offlinePlayer.isRegistered()) return OnlinePlayer.LoginResponse.ALREADY_REGISTERED; Bukkit.getScheduler().runTaskAsynchronously(LobsterCraft.plugin, () -> { try { // Set offline player's attributes (lastIp is just set on login) offlinePlayer.lastIp = onlinePlayer.getPlayer().getAddress().getAddress().getHostAddress(); offlinePlayer.encryptedPassword = encryptedPassword; offlinePlayer.databaseState = DatabaseState.INSERT_TO_DATABASE; // Register player on database Connection connection = LobsterCraft.dataSource.getConnection(); // Prepare statement PreparedStatement preparedStatement = connection .prepareStatement("INSERT INTO `minecraft`.`user_profiles`" + "(`playerName`, `password`, `moneyAmount`, `city_cityId`, `cityOccupation`, `lastTimeOnline`, `timePlayed`, `lastIp`)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS); // Set variables preparedStatement.setString(1, offlinePlayer.getPlayerName().toLowerCase()); // Lower case it just to make sure preparedStatement.setString(2, offlinePlayer.getEncryptedPassword()); preparedStatement.setDouble(3, offlinePlayer.getMoneyAmount()); preparedStatement.setObject(4, offlinePlayer.getCityId(), Types.SMALLINT); // Will write null if is null preparedStatement.setObject(5, offlinePlayer.getCityOccupation() != null ? offlinePlayer.getCityOccupation().getOccupationId() : null, Types.TINYINT); preparedStatement.setLong(6, offlinePlayer.getLastTimeOnline()); preparedStatement.setLong(7, offlinePlayer.getTimePlayed()); preparedStatement.setString(8, offlinePlayer.getLastIp()); // Execute statement preparedStatement.execute(); // Retrieve generated keys ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); // Check if key exists if (!generatedKeys.next()) throw new SQLException("Query didn't return any generated key"); offlinePlayer.playerId = generatedKeys.getInt("playerId"); // Close everything generatedKeys.close(); preparedStatement.close(); connection.close(); // Check if was successful if (offlinePlayer.getPlayerId() == null || offlinePlayer.getPlayerId() <= 0) throw new IllegalStateException(Util.appendStrings("Failed to register player: playerId is ", offlinePlayer.getPlayerId())); // Change player's instance location synchronized (playerMapsLock) { unregisteredOfflinePlayers_name.remove(offlinePlayer.getPlayerName(), offlinePlayer); registeredOfflinePlayers_name.put(offlinePlayer.getPlayerName(), offlinePlayer); registeredOfflinePlayers_id.put(offlinePlayer.getPlayerId(), offlinePlayer); // Check if player has a city (even though he just registered...) if (offlinePlayer.getCityId() != null) { if (!registeredOfflinePlayers_cityId.containsKey(offlinePlayer.getCityId())) registeredOfflinePlayers_cityId.put(offlinePlayer.getCityId(), new HashSet<>()); registeredOfflinePlayers_cityId.get(offlinePlayer.getCityId()).add(offlinePlayer); } } // Update database state offlinePlayer.databaseState = DatabaseState.ON_DATABASE; onlinePlayer.onlineState = OnlinePlayer.OnlineState.PRE_LOGIN; // Force login forceLoginPlayer(onlinePlayer); } catch (Exception exception) { exception.printStackTrace(); onlinePlayer.getPlayer().kickPlayer("4Um erro ocorreu ao registrar!"); } }); return OnlinePlayer.LoginResponse.LOGIN_WENT_ASYNCHRONOUS_SUCCESSFULLY; }
From source file:org.apache.qpid.disttest.db.ResultsDbWriter.java
private void writeParticipantResult(Connection connection, ParticipantResult participantResult) throws SQLException { if (_logger.isDebugEnabled()) { _logger.debug("About to write to DB the following participant result: " + participantResult); }/*from w w w. j a va 2s .co m*/ PreparedStatement statement = null; try { String sqlTemplate = String.format( "INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) " + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", RESULTS_TABLE_NAME, TEST_NAME.getDisplayName(), ITERATION_NUMBER.getDisplayName(), PARTICIPANT_NAME.getDisplayName(), THROUGHPUT.getDisplayName(), AVERAGE_LATENCY.getDisplayName(), CONFIGURED_CLIENT_NAME.getDisplayName(), NUMBER_OF_MESSAGES_PROCESSED.getDisplayName(), PAYLOAD_SIZE.getDisplayName(), PRIORITY.getDisplayName(), TIME_TO_LIVE.getDisplayName(), ACKNOWLEDGE_MODE.getDisplayName(), DELIVERY_MODE.getDisplayName(), BATCH_SIZE.getDisplayName(), MAXIMUM_DURATION.getDisplayName(), PRODUCER_START_DELAY.getDisplayName(), PRODUCER_INTERVAL.getDisplayName(), IS_TOPIC.getDisplayName(), IS_DURABLE_SUBSCRIPTION.getDisplayName(), IS_BROWSING_SUBSCRIPTION.getDisplayName(), IS_SELECTOR.getDisplayName(), IS_NO_LOCAL.getDisplayName(), IS_SYNCHRONOUS_CONSUMER.getDisplayName(), TOTAL_NUMBER_OF_CONSUMERS.getDisplayName(), TOTAL_NUMBER_OF_PRODUCERS.getDisplayName(), TOTAL_PAYLOAD_PROCESSED.getDisplayName(), TIME_TAKEN.getDisplayName(), ERROR_MESSAGE.getDisplayName(), MIN_LATENCY.getDisplayName(), MAX_LATENCY.getDisplayName(), LATENCY_STANDARD_DEVIATION.getDisplayName(), RUN_ID, INSERTED_TIMESTAMP); statement = connection.prepareStatement(sqlTemplate); int columnIndex = 1; statement.setString(columnIndex++, participantResult.getTestName()); statement.setInt(columnIndex++, participantResult.getIterationNumber()); statement.setString(columnIndex++, participantResult.getParticipantName()); statement.setDouble(columnIndex++, participantResult.getThroughput()); statement.setDouble(columnIndex++, participantResult.getAverageLatency()); statement.setString(columnIndex++, participantResult.getConfiguredClientName()); statement.setLong(columnIndex++, participantResult.getNumberOfMessagesProcessed()); statement.setLong(columnIndex++, participantResult.getPayloadSize()); statement.setLong(columnIndex++, participantResult.getPriority()); statement.setLong(columnIndex++, participantResult.getTimeToLive()); statement.setLong(columnIndex++, participantResult.getAcknowledgeMode()); statement.setLong(columnIndex++, participantResult.getDeliveryMode()); statement.setLong(columnIndex++, participantResult.getBatchSize()); statement.setLong(columnIndex++, participantResult.getMaximumDuration()); statement.setLong(columnIndex++, 0 /* TODO PRODUCER_START_DELAY*/); statement.setLong(columnIndex++, 0 /* TODO PRODUCER_INTERVAL*/); statement.setLong(columnIndex++, 0 /* TODO IS_TOPIC*/); statement.setLong(columnIndex++, 0 /* TODO IS_DURABLE_SUBSCRIPTION*/); statement.setLong(columnIndex++, 0 /* TODO IS_BROWSING_SUBSCRIPTION*/); statement.setLong(columnIndex++, 0 /* TODO IS_SELECTOR*/); statement.setLong(columnIndex++, 0 /* TODO IS_NO_LOCAL*/); statement.setLong(columnIndex++, 0 /* TODO IS_SYNCHRONOUS_CONSUMER*/); statement.setLong(columnIndex++, participantResult.getTotalNumberOfConsumers()); statement.setLong(columnIndex++, participantResult.getTotalNumberOfProducers()); statement.setLong(columnIndex++, participantResult.getTotalPayloadProcessed()); statement.setLong(columnIndex++, participantResult.getTimeTaken()); statement.setString(columnIndex++, participantResult.getErrorMessage()); statement.setLong(columnIndex++, participantResult.getMinLatency()); statement.setLong(columnIndex++, participantResult.getMaxLatency()); statement.setDouble(columnIndex++, participantResult.getLatencyStandardDeviation()); statement.setString(columnIndex++, _runId); statement.setTimestamp(columnIndex++, new Timestamp(_clock.currentTimeMillis())); statement.execute(); connection.commit(); } catch (SQLException e) { _logger.error("Couldn't write " + participantResult, e); } finally { if (statement != null) { statement.close(); } } }
From source file:at.alladin.rmbt.statisticServer.StatisticsResource.java
private static PreparedStatement selectDevices(final Connection conn, final boolean group, final float quantile, final int durationDays, final double accuracy, final String country, final boolean useMobileProvider, final String where, final int maxDevices) throws SQLException { PreparedStatement ps; String sql = String.format("SELECT" + (group ? " COALESCE(adm.fullname, t.model) model," : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping" + " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " WHERE %s" + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND time > NOW() - CAST(? AS INTERVAL)" + (useMobileProvider ? " AND t.mobile_provider_id IS NOT NULL" : "") + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY COALESCE(adm.fullname, t.model) HAVING count(t.uid) > 10" : "") + " ORDER BY count DESC" + " LIMIT %d", where, maxDevices); if (country != null) { sql = String.format("SELECT" + (group ? " COALESCE(adm.fullname, t.model) model," : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping" + " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + (useMobileProvider ? " LEFT JOIN mccmnc2name p ON p.uid = t.mobile_sim_id" : "") + " WHERE %s" + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND time > NOW() - CAST(? AS INTERVAL)" + " AND " + (useMobileProvider/*from ww w. j a va 2 s . c o m*/ ? "p.country = ? AND ((t.country_location IS NULL OR t.country_location = ?) AND (NOT t.roaming_type = 2))" : "t.country_geoip = ? ") + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY COALESCE(adm.fullname, t.model) HAVING count(t.uid) > 10" : "") + " ORDER BY count DESC" + " LIMIT %d", where, maxDevices); } ps = conn.prepareStatement(sql); System.out.println(ps); int i = 1; for (int j = 0; j < 2; j++) ps.setFloat(i++, quantile); ps.setFloat(i++, 1 - quantile); // inverse for ping ps.setString(i++, String.format("%d days", durationDays)); if (country != null) { if (useMobileProvider) { ps.setString(i++, country.toLowerCase()); //mccmnc2name.country ps.setString(i++, country.toUpperCase()); //country_location } else { ps.setString(i++, country.toUpperCase()); } } if (accuracy > 0) { ps.setDouble(i++, accuracy); } return ps; }
From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java
/** * This should run on server close, so we don't need to synchronize as every player join is denied before. * * @param connection MySQL connection//from w w w . j a va 2s .c om * @throws SQLException in case of something going wrong */ private void saveChangedPlayers(@NotNull Connection connection) throws SQLException { long start = System.nanoTime(); int numberOfPlayersUpdated = 0; // Prepare statement PreparedStatement preparedStatement = connection.prepareStatement( "UPDATE `minecraft`.`user_profiles` SET `playerName` = ?, `password` = ?, `moneyAmount` = ?, `city_cityId` = ?," + " `cityOccupation` = ?, `lastTimeOnline` = ?, `timePlayed` = ?, `lastIp` = ? WHERE `playerId` = ?;"); // Iterate through all players for (OfflinePlayer offlinePlayer : registeredOfflinePlayers_id.values()) // Filter the ones needing updates => REGISTERED PLAYERS: they have money amount, passwords, last time online, last IP if (offlinePlayer.getDatabaseState() == DatabaseState.UPDATE_DATABASE) { preparedStatement.setString(1, offlinePlayer.getPlayerName()); preparedStatement.setString(2, offlinePlayer.getEncryptedPassword()); preparedStatement.setDouble(3, offlinePlayer.getMoneyAmount()); preparedStatement.setObject(4, offlinePlayer.getCityId(), Types.INTEGER); // Will write null if is null preparedStatement.setObject(5, offlinePlayer.getCityOccupation() != null ? offlinePlayer.getCityOccupation().getOccupationId() : null, Types.TINYINT); preparedStatement.setLong(6, offlinePlayer.getLastTimeOnline()); preparedStatement.setLong(7, offlinePlayer.getTimePlayed()); preparedStatement.setString(8, offlinePlayer.getLastIp()); preparedStatement.setLong(9, offlinePlayer.getPlayerId()); // Add batch preparedStatement.addBatch(); // Update their database state offlinePlayer.databaseState = DatabaseState.ON_DATABASE; numberOfPlayersUpdated++; } // Execute and announce if (numberOfPlayersUpdated > 0) { preparedStatement.executeBatch(); LobsterCraft.logger.info(Util.appendStrings("Took us ", Util.formatDecimal( (double) (System.nanoTime() - start) / (double) TimeUnit.MILLISECONDS.toNanos(1)), "ms to update ", numberOfPlayersUpdated, " players.")); } // Close statement preparedStatement.close(); }
From source file:com.skilrock.lms.coreEngine.scratchService.inventoryMgmt.common.SalesReturnHelper.java
public String doTransaction(int game_id, int org_id, String orgName, List<PackBean> packlist, List<BookBean> booklist, String rootPath, int userOrgId, int userId, String newBookStatus, Connection conn) throws LMSException { int receipt_id = 0; int transaction_id = 0; double ticket_price = 0, book_price = 0; int nbr_of_tickets_per_book = 0, nbr_of_books_per_pack = 0; double agent_sale_comm_rate = 0; double prizePayOutPer = 0.0; double vat = 0.0; double govtComm = 0.0; double vatAmt = 0.0; double bookTaxableSale = 0.0; double govtCommAmt = 0.0; double commAmt = 0.0; double netAmt = 0.0; double taxableSale = 0.0; double vatBalance = 0.0; long ticketsInScheme = 0; String govtCommRule = null;//w w w . j a va 2s . co m double fixedAmt = 0.0; double netAmtOrg = 0.0; int DCId = 0; String bookNumber = ""; boolean isBookActivated = true; List<Integer> trnIdList = new ArrayList<Integer>(); try { // get books list from packlist and copy to booklist // new book status on sales return // String newBookStatus = "INACTIVE"; logger.info("***Return Book Status Should be**************" + newBookStatus); if (packlist.size() != 0) { PackBean packbean = null; BookBean bookbean = null; Iterator<PackBean> packListItr = packlist.iterator(); while (packListItr.hasNext()) { packbean = packListItr.next(); String packNbr = packbean.getPackNumber(); String querytoGetBookFrmPack = QueryManager.getST4BookNbrOfPackNbr(); PreparedStatement stmtbookFrmPack = conn.prepareStatement(querytoGetBookFrmPack); stmtbookFrmPack.setString(1, packNbr); ResultSet set = stmtbookFrmPack.executeQuery(); while (set.next()) { String bookNbrfromPack = set.getString("book_nbr"); bookbean = new BookBean(); bookbean.setBookNumber(bookNbrfromPack); bookbean.setValid(true); bookbean.setStatus("Book Is Valid"); booklist.add(bookbean); } } } // Getting Game Details using game id String querytoGameDetail = QueryManager.getST4GameDetailsUsingGameId(); PreparedStatement stmtgamedeatil = conn.prepareStatement(querytoGameDetail); stmtgamedeatil.setInt(1, game_id); ResultSet rsGameDetail = stmtgamedeatil.executeQuery(); while (rsGameDetail.next()) { ticket_price = rsGameDetail.getDouble("ticket_price"); nbr_of_tickets_per_book = rsGameDetail.getInt("nbr_of_tickets_per_book"); nbr_of_books_per_pack = rsGameDetail.getInt("nbr_of_books_per_pack"); agent_sale_comm_rate = rsGameDetail.getDouble("agent_sale_comm_rate"); prizePayOutPer = rsGameDetail.getDouble("prize_payout_ratio"); vat = rsGameDetail.getDouble("vat_amt"); govtComm = rsGameDetail.getDouble("govt_comm_rate"); vatBalance = rsGameDetail.getDouble("vat_balance"); ticketsInScheme = rsGameDetail.getLong("tickets_in_scheme"); govtCommRule = rsGameDetail.getString("govt_comm_type"); fixedAmt = rsGameDetail.getDouble("fixed_amt"); } book_price = ticket_price * nbr_of_tickets_per_book; BookSaleReturnBean bookSaleRetBean; ArrayList<BookSaleReturnBean> bookSaleReturnList = new ArrayList<BookSaleReturnBean>(); String bookNbr, packNbr = null; double commVariance = 0.0; double govtCommRate = 0.0; ResultSet rsCommVar; Iterator iteratorCommVar = booklist.iterator(); while (iteratorCommVar.hasNext()) { bookSaleRetBean = new BookSaleReturnBean(); bookNbr = ((BookBean) iteratorCommVar.next()).getBookNumber(); String commVarianceQuery = "select transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_date from st_se_game_inv_detail where current_owner=? and current_owner_id=? and game_id=? and book_nbr=? and transaction_at=? order by transaction_date desc limit 1 "; PreparedStatement commVariaceStmt = conn.prepareStatement(commVarianceQuery); commVariaceStmt.setString(1, "AGENT"); commVariaceStmt.setInt(2, org_id); commVariaceStmt.setInt(3, game_id); commVariaceStmt.setString(4, bookNbr); commVariaceStmt.setString(5, "BO"); rsCommVar = commVariaceStmt.executeQuery(); while (rsCommVar.next()) { commVariance = rsCommVar.getDouble("transacrion_sale_comm_rate"); govtCommRate = rsCommVar.getDouble("transaction_gov_comm_rate"); } commAmt = commAmt + book_price * commVariance * 0.01; netAmt = netAmt + book_price * (1 - commVariance * 0.01); govtCommAmt = book_price * govtCommRate * .01; vatAmt = vatAmt + CommonMethods.calculateVat(book_price, commVariance, prizePayOutPer, govtCommRate, vat, govtCommRule, fixedAmt, ticketsInScheme); /* * bookTaxableSale = (((book_price * (100 - (commVariance + * prizePayOutPer + govtCommRate))) / 100) * 100) / (100 + vat); */ // bookTaxableSale=vatAmt/(vat * 0.01); bookTaxableSale = CommonMethods.calTaxableSale(book_price, commVariance, prizePayOutPer, govtCommRate, vat); bookSaleRetBean.setBookNumber(bookNbr); bookSaleRetBean.setBookCommVariance(commVariance); bookSaleRetBean.setDefaultCommVariance(agent_sale_comm_rate); bookSaleRetBean.setTotalSaleComm(commVariance); bookSaleRetBean.setTotalSaleGovtComm(govtCommRate); bookSaleRetBean.setGovtCommAmt(govtCommAmt); bookSaleRetBean.setBookVatAmt(vatAmt); bookSaleRetBean.setBookCommAmt(commAmt); bookSaleRetBean.setBookNetAmt(netAmt); bookSaleRetBean.setBookTaxableSale(bookTaxableSale); bookSaleReturnList.add(bookSaleRetBean); commVariance = 0.0; govtCommRate = 0.0; bookTaxableSale = 0.0; govtCommAmt = 0.0; vatAmt = 0.0; commAmt = 0.0; netAmt = 0.0; } // get comm variance history List<CommVarGovtCommBean> commVariancelist = new ArrayList<CommVarGovtCommBean>(); CommVarGovtCommBean commVarGovtCommBean; String queryCommVarHistory = "select DISTINCT transacrion_sale_comm_rate,transaction_gov_comm_rate from st_se_game_inv_detail where current_owner_id=" + org_id + " and game_id=" + game_id; PreparedStatement stmtCommVarHistory = conn.prepareStatement(queryCommVarHistory); ResultSet rsCommVarHistory = stmtCommVarHistory.executeQuery(); while (rsCommVarHistory.next()) { commVarGovtCommBean = new CommVarGovtCommBean(); commVarGovtCommBean.setCommVariance(rsCommVarHistory.getDouble("transacrion_sale_comm_rate")); commVarGovtCommBean.setGovtComm(rsCommVarHistory.getDouble("transaction_gov_comm_rate")); commVariancelist.add(commVarGovtCommBean); // commVarianceSet.add(rsCommVarHistory.getDouble("transacrion_sale_comm_rate")); } System.out.println(" 22222222222222222222222size for comm var history " + commVariancelist.size() + "pstmt " + stmtCommVarHistory); Iterator iteratorBookSaleReturn; // Iterator iteratorCommVarHistory= commVarianceSet.iterator(); Iterator iteratorCommVarHistory = commVariancelist.iterator(); while (iteratorCommVarHistory.hasNext()) { boolean bookCommVarMatch = false; // logger.info("comm var from history-------------------- // "); Double totCommAmt = 0.0; Double totVatAmt = 0.0; Double totNetAmt = 0.0; Double totTaxableSale = 0.0; Double totGovtComm = 0.0; List bookListforSingleTrn = null; bookListforSingleTrn = new ArrayList<String>(); double commFromHistory = 0.0; double govtCommFromHistory = 0.0; // commFromHistory=(Double)iteratorCommVarHistory.next(); CommVarGovtCommBean commBean = (CommVarGovtCommBean) iteratorCommVarHistory.next(); commFromHistory = commBean.getCommVariance(); govtCommFromHistory = commBean.getGovtComm(); // logger.info("comm var from history-------------------- // "+commFromHistory); iteratorBookSaleReturn = bookSaleReturnList.iterator(); while (iteratorBookSaleReturn.hasNext()) { bookSaleRetBean = (BookSaleReturnBean) iteratorBookSaleReturn.next(); double bookCommVariance = 0.0; double bookGovtCommVariance = 0.0; bookCommVariance = bookSaleRetBean.getTotalSaleComm(); bookGovtCommVariance = bookSaleRetBean.getTotalSaleGovtComm(); // logger.info("commFromHistory " + commFromHistory + // "bookCommVariance " + bookCommVariance); // logger.info("GovtcommFromHistory " + // govtCommFromHistory + "bookGovtCommVariance " + // bookGovtCommVariance); if (commFromHistory == bookCommVariance && govtCommFromHistory == bookGovtCommVariance) { // logger.info("inside boolean isSaleTransactionExist = true; // if%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"); if ("YES".equals(Utility.getPropertyValue("IS_SCRATCH_NEW_FLOW_ENABLED"))) { bookNumber = bookSaleRetBean.getBookNumber(); String saleTransactionQuery = "select current_owner_id from st_se_game_inv_status where book_nbr = '" + bookSaleRetBean.getBookNumber() + "' and agent_invoice_id IS NOT NULL"; Statement saleTransactionQueryStmt = conn.createStatement(); ResultSet saleTransactionQueryResultSet = saleTransactionQueryStmt .executeQuery(saleTransactionQuery); if (saleTransactionQueryResultSet.next()) { isSaleTransactionExist = true; } else { isSaleTransactionExist = false; } } if (isSaleTransactionExist) { bookCommVarMatch = true; isBookActivated = false; totCommAmt = totCommAmt + bookSaleRetBean.getBookCommAmt(); totVatAmt = totVatAmt + bookSaleRetBean.getBookVatAmt(); totTaxableSale = totTaxableSale + bookSaleRetBean.getBookTaxableSale(); // logger.info("hello :::::::: " + totTaxableSale // + "history detail " // +bookSaleRetBean.getBookTaxableSale()); totGovtComm = totGovtComm + bookSaleRetBean.getGovtCommAmt(); totNetAmt = totNetAmt + bookSaleRetBean.getBookNetAmt(); bookListforSingleTrn.add(bookSaleRetBean.getBookNumber()); } } } netAmtOrg = netAmtOrg + totNetAmt; if (bookCommVarMatch) { // insert in LMS transaction master String QueryLMSTransMaster = QueryManager.insertInLMSTransactionMaster(); PreparedStatement stmtLMSTransmas = conn.prepareStatement(QueryLMSTransMaster); stmtLMSTransmas.setString(1, "BO"); stmtLMSTransmas.executeUpdate(); // Get Transaction Id From the table. ResultSet rsTransId = stmtLMSTransmas.getGeneratedKeys(); while (rsTransId.next()) { transaction_id = rsTransId.getInt(1); trnIdList.add(transaction_id); } logger.info("transaction_id: " + transaction_id); // 1. Insert Entry in st_lms_bo_transaction_master Table. String queryTranMas = QueryManager.insertInBOTransactionMaster(); PreparedStatement stmtTransmas = conn.prepareStatement(queryTranMas); stmtTransmas.setInt(1, transaction_id); stmtTransmas.setInt(2, userId); stmtTransmas.setInt(3, userOrgId); stmtTransmas.setString(4, "AGENT"); stmtTransmas.setInt(5, org_id); stmtTransmas.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime())); stmtTransmas.setString(7, "SALE_RET"); /* * stmtTransmas.setString(1, "AGENT"); * stmtTransmas.setInt(2, org_id); * stmtTransmas.setTimestamp(3, new java.sql.Timestamp(new * java.util.Date().getTime())); stmtTransmas.setString(4, * "SALE_RET"); */ stmtTransmas.executeUpdate(); // 2. Insert Entry in st_se_bo_agent_transaction table. String queryBoAgtTrans = QueryManager.getST4InsertBoAgentTransaction(); PreparedStatement stmtBoAgtTrans = conn.prepareStatement(queryBoAgtTrans); stmtBoAgtTrans.setInt(1, transaction_id); stmtBoAgtTrans.setInt(2, bookListforSingleTrn.size()); stmtBoAgtTrans.setInt(3, game_id); stmtBoAgtTrans.setInt(4, org_id); stmtBoAgtTrans.setDouble(5, book_price * bookListforSingleTrn.size()); stmtBoAgtTrans.setDouble(6, totCommAmt); stmtBoAgtTrans.setDouble(7, totNetAmt); stmtBoAgtTrans.setString(8, "SALE_RET"); stmtBoAgtTrans.setDouble(9, totVatAmt); stmtBoAgtTrans.setDouble(10, totTaxableSale); stmtBoAgtTrans.setDouble(11, totGovtComm); stmtBoAgtTrans.executeUpdate(); String detHistoryInsQuery = "insert into st_se_game_ticket_inv_history(game_id, book_nbr, " + " current_owner, current_owner_id, date, done_by_oid, done_by_uid, cur_rem_tickets, " + " active_tickets_upto, sold_tickets, status) values (?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement detHistoryInsPstmt = conn.prepareStatement(detHistoryInsQuery); // fetch game details from game master String fetchGameDetQuery = "select nbr_of_tickets_per_book from st_se_game_master where game_id =" + game_id; Statement fetchGameDetStmt = conn.createStatement(); ResultSet fetchGameDetRs = fetchGameDetStmt.executeQuery(fetchGameDetQuery); int noOfTktPerBooks = -1; if (fetchGameDetRs.next()) { noOfTktPerBooks = fetchGameDetRs.getInt("nbr_of_tickets_per_book"); } // 6. Insert in to st_se_game_inv_detail table. for (int i = 0; i < bookListforSingleTrn.size(); i++) { String bknbr = (String) bookListforSingleTrn.get(i); // logger.info("//Get the pack number of book // number. "); // Get the pack number of book number. String pknbr = null; String queryTogetPackNbr = QueryManager.getST4PackNbrOfBookNbr(); PreparedStatement stm = conn.prepareStatement(queryTogetPackNbr); stm.setString(1, bknbr); ResultSet resultSet = stm.executeQuery(); while (resultSet.next()) { pknbr = resultSet.getString("pack_nbr"); } String queryGameInvDtl = "insert into st_se_game_inv_detail (transaction_id,game_id,pack_nbr, book_nbr,current_owner,current_owner_id,transaction_date,transaction_at,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status) select ?,?,?,?,?,?,?,?,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status from st_se_game_inv_detail where book_nbr=? and transaction_at=? order by transaction_date desc limit 1"; PreparedStatement stmtGameInvDtl = conn.prepareStatement(queryGameInvDtl); stmtGameInvDtl.setInt(1, transaction_id); stmtGameInvDtl.setInt(2, game_id); stmtGameInvDtl.setString(3, pknbr); stmtGameInvDtl.setString(4, bknbr); stmtGameInvDtl.setString(5, "BO"); stmtGameInvDtl.setInt(6, userOrgId); stmtGameInvDtl.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime())); stmtGameInvDtl.setString(8, "BO"); stmtGameInvDtl.setString(9, bknbr); stmtGameInvDtl.setString(10, "BO"); stmtGameInvDtl.executeUpdate(); // insert into detail history table Added by arun detHistoryInsPstmt.setInt(1, game_id); detHistoryInsPstmt.setString(2, bknbr); detHistoryInsPstmt.setString(3, "BO"); detHistoryInsPstmt.setInt(4, userOrgId); detHistoryInsPstmt.setTimestamp(5, new java.sql.Timestamp(new java.util.Date().getTime())); detHistoryInsPstmt.setInt(6, userOrgId); detHistoryInsPstmt.setInt(7, userId); detHistoryInsPstmt.setInt(8, noOfTktPerBooks); // logger.info("detHistoryInsPstmt == // "+detHistoryInsPstmt); if ("ACTIVE".equalsIgnoreCase(newBookStatus.trim())) { detHistoryInsPstmt.setInt(9, noOfTktPerBooks); } else { detHistoryInsPstmt.setInt(9, 0); } detHistoryInsPstmt.setInt(10, 0); detHistoryInsPstmt.setString(11, newBookStatus); detHistoryInsPstmt.execute(); // --------------------- } } if (isBookActivated) { String bknbr = bookNumber; // System.out.println("//Get the pack number of book // number. "); // Get the pack number of book number. String pknbr = null; String queryTogetPackNbr = QueryManager.getST4PackNbrOfBookNbr(); PreparedStatement stm = conn.prepareStatement(queryTogetPackNbr); stm.setString(1, bknbr); ResultSet resultSet = stm.executeQuery(); while (resultSet.next()) { pknbr = resultSet.getString("pack_nbr"); } String queryGameInvDtl = "insert into st_se_game_inv_detail (transaction_id,game_id,pack_nbr, book_nbr,current_owner,current_owner_id,transaction_date,transaction_at,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status) select ?,?,?,?,?,?,?,?,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status from st_se_game_inv_detail where book_nbr=? and transaction_at=? order by transaction_date desc limit 1"; PreparedStatement stmtGameInvDtl = conn.prepareStatement(queryGameInvDtl); stmtGameInvDtl.setInt(1, transaction_id); stmtGameInvDtl.setInt(2, game_id); stmtGameInvDtl.setString(3, pknbr); stmtGameInvDtl.setString(4, bknbr); stmtGameInvDtl.setString(5, "BO"); stmtGameInvDtl.setInt(6, userOrgId); stmtGameInvDtl.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime())); stmtGameInvDtl.setString(8, "BO"); stmtGameInvDtl.setString(9, bknbr); stmtGameInvDtl.setString(10, "BO"); stmtGameInvDtl.executeUpdate(); } } logger.info("5. Update st_se_game_inv_status Table. "); // 5. Update st_se_game_inv_status Table. for (int i = 0; i < bookSaleReturnList.size(); i++) { String bknbr = ((BookSaleReturnBean) bookSaleReturnList.get(i)).getBookNumber(); String queryGameInvStatus = QueryManager.getST4UdateGameInvStatusForBook(); PreparedStatement stmtGameInvStatus = conn.prepareStatement(queryGameInvStatus); stmtGameInvStatus.setString(1, newBookStatus); stmtGameInvStatus.setInt(2, userOrgId); stmtGameInvStatus.setString(3, null); stmtGameInvStatus.setInt(4, game_id); stmtGameInvStatus.setString(5, bknbr); stmtGameInvStatus.executeUpdate(); } // get auto generated treciept number Statement autoGenRecptPstmtBOCRNote = null; // String getLatestRecieptNumberBO="SELECT * from // st_bo_receipt_gen_mapping where receipt_type=? ORDER BY // generated_id DESC LIMIT 1 "; String queryRcpt = "SELECT * from st_lms_bo_receipts where receipt_type like ('CR_NOTE%') ORDER BY generated_id DESC LIMIT 1"; autoGenRecptPstmtBOCRNote = conn.createStatement(); // autoGenRecptPstmtBO.setString(1, "CR_NOTE"); logger.info("queryRcpt--" + queryRcpt); ResultSet recieptRsBO = autoGenRecptPstmtBOCRNote.executeQuery(queryRcpt); String lastRecieptNoGeneratedBO = null; while (recieptRsBO.next()) { lastRecieptNoGeneratedBO = recieptRsBO.getString("generated_id"); } String autoGeneRecieptNoBO = GenerateRecieptNo.getRecieptNo("CR_NOTE", lastRecieptNoGeneratedBO, "BO"); // get auto generated delivery Challan number // String getLatestDSRCNumber="SELECT * from // st_bo_receipt_gen_mapping where receipt_type=? ORDER BY // generated_id DESC LIMIT 1 "; // autoGenRecptPstmtBO=conn.prepareStatement(getLatestDSRCNumber); PreparedStatement autoGenRecptPstmtBO = null; autoGenRecptPstmtBO = conn.prepareStatement(QueryManager.getBOLatestReceiptNb()); autoGenRecptPstmtBO.setString(1, "DSRCHALLAN"); ResultSet DCRs = autoGenRecptPstmtBO.executeQuery(); String lastDSRCNoGenerated = null; while (DCRs.next()) { lastDSRCNoGenerated = DCRs.getString("generated_id"); } String autoGeneDCNo = null; autoGeneDCNo = GenerateRecieptNo.getRecieptNo("DSRCHALLAN", lastDSRCNoGenerated, "BO"); // insert into receipts master PreparedStatement stmtRecptId = conn.prepareStatement(QueryManager.insertInReceiptMaster()); stmtRecptId.setString(1, "BO"); stmtRecptId.executeUpdate(); ResultSet rsRecptId = stmtRecptId.getGeneratedKeys(); while (rsRecptId.next()) { receipt_id = rsRecptId.getInt(1); } // Insert Entry in st_bo_receipt table. // String queryRecptId=QueryManager.getST4InsertBoReceipts(); stmtRecptId = conn.prepareStatement(QueryManager.insertInBOReceipts()); stmtRecptId.setInt(1, receipt_id); stmtRecptId.setString(2, "CR_NOTE"); stmtRecptId.setInt(3, org_id); stmtRecptId.setString(4, "AGENT"); stmtRecptId.setString(5, autoGeneRecieptNoBO); stmtRecptId.setTimestamp(6, Util.getCurrentTimeStamp()); /* * stmtRecptId.setString(1, "CR_NOTE"); stmtRecptId.setInt(2, * org_id); */ stmtRecptId.executeUpdate(); // insert reciept id for delivery challan stmtRecptId = conn.prepareStatement(QueryManager.insertInReceiptMaster()); stmtRecptId.setString(1, "BO"); stmtRecptId.executeUpdate(); ResultSet rsDC = stmtRecptId.getGeneratedKeys(); while (rsDC.next()) { DCId = rsDC.getInt(1); } stmtRecptId = conn.prepareStatement(QueryManager.insertInBOReceipts()); stmtRecptId.setInt(1, DCId); stmtRecptId.setString(2, "DSRCHALLAN"); stmtRecptId.setInt(3, org_id); stmtRecptId.setString(4, "AGENT"); stmtRecptId.setString(5, autoGeneDCNo); stmtRecptId.setTimestamp(6, Util.getCurrentTimeStamp()); stmtRecptId.execute(); // 4. Insert Entry in st_lms_bo_receipts_trn_mapping table. PreparedStatement stmtRcptTrnMapping = conn.prepareStatement(QueryManager.insertBOReceiptTrnMapping()); for (int i = 0; i < trnIdList.size(); i++) { stmtRcptTrnMapping.setInt(1, receipt_id); stmtRcptTrnMapping.setInt(2, (Integer) trnIdList.get(i)); stmtRcptTrnMapping.executeUpdate(); } // 4. Insert Entry in st_lms_bo_receipts_trn_mapping table for // delivery // challan for (int i = 0; i < trnIdList.size(); i++) { stmtRcptTrnMapping.setInt(1, DCId); stmtRcptTrnMapping.setInt(2, (Integer) trnIdList.get(i)); stmtRcptTrnMapping.executeUpdate(); } // insert into invoice and delivery challan mapping table String insertCRNoteDCMapping = "insert into st_se_bo_invoice_delchallan_mapping(id,generated_invoice_id,generated_del_challan_id) values(?,?,?)"; PreparedStatement boCRNoteDCMappingStmt = conn.prepareStatement(insertCRNoteDCMapping); boCRNoteDCMappingStmt.setInt(1, receipt_id); boCRNoteDCMappingStmt.setString(2, autoGeneRecieptNoBO); boCRNoteDCMappingStmt.setString(3, autoGeneDCNo); boCRNoteDCMappingStmt.executeUpdate(); boolean isValid = OrgCreditUpdation.updateOrganizationBalWithValidate(netAmtOrg, "TRANSACTION", "SALE_RET", org_id, 0, "AGENT", 0, conn); if (!isValid) throw new LMSException(); /*boolean isUpdateDone = OrgCreditUpdation.updateCreditLimitForAgent( org_id, "SALE_RET", netAmtOrg, conn);*/ // session.setAttribute("DEL_CHALLAN_ID", DCId); // if (receipt_id > 0) { // GraphReportHelper graphReportHelper = new GraphReportHelper(); // graphReportHelper.createTextReportBO(receipt_id, orgName, // userOrgId, rootPath); // } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new LMSException(e); } return String.valueOf(DCId) + "Nxt" + String.valueOf(receipt_id); }