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.bluemix.samples.PostgreSQLClient.java
/** * Grab text from PostgreSQL/*from w w w .ja v a2 s .c o m*/ * * @return List of Strings of text from PostgreSQL * @throws Exception */ public List<EntityProfile> getProfileList() throws Exception { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.append("SELECT * FROM profile"); sqlBuilder.append(" order by NotesID"); Connection connection = null; PreparedStatement statement = null; ResultSet results = null; EntityProfile profile = null; List<EntityProfile> profileList = new ArrayList<EntityProfile>(); try { connection = getConnection(); statement = connection.prepareStatement(sqlBuilder.toString()); results = statement.executeQuery(); while (results.next()) { profile = new EntityProfile(); if (results.getString("NotesID") != null) { profile.setNotesID(results.getString("NotesID")); } if (results.getString("Name") != null) { profile.setName(results.getString("Name")); } if (results.getString("PeMID") != null) { profile.setPemDisp(CommMethod.changePem(results.getString("PeMID"))); } if (results.getString("ILID") != null) { profile.setIlDisp(results.getString("ILID")); } if (results.getString("TechDomain") != null) { profile.setTechDisp(CommMethod.changeTechnology(results.getString("TechDomain"), results.getString("TechOther"))); } if (results.getString("Utilization") != null) { profile.setUtilization(results.getDouble("Utilization")); } if (results.getString("Location") != null) { profile.setLocation(results.getString("Location")); } if (results.getString("OnSiteFlag") != null) { profile.setOnSiteFlagDisp(CommMethod.changeFlag(results.getString("OnSiteFlag"))); } if (results.getString("OnBenchFlag") != null) { profile.setOnBenchFlagDisp(CommMethod.changeFlag(results.getString("OnBenchFlag"))); } if (results.getString("RegiesteredFlag") != null) { profile.setRegiesteredFlagDisp(CommMethod.changeFlag(results.getString("RegiesteredFlag"))); } profileList.add(profile); } return profileList; } catch (SQLException e) { throw e; } finally { if (results != null) { results.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } }
From source file:com.facebook.presto.AbstractTestQueries.java
private static ResultSetMapper<Tuple> tupleMapper(final TupleInfo tupleInfo) { return new ResultSetMapper<Tuple>() { @Override//from w w w . jav a 2 s. c om public Tuple map(int index, ResultSet resultSet, StatementContext ctx) throws SQLException { List<TupleInfo.Type> types = tupleInfo.getTypes(); int count = resultSet.getMetaData().getColumnCount(); checkArgument(types.size() == count, "tuple info does not match result"); TupleInfo.Builder builder = tupleInfo.builder(); for (int i = 1; i <= count; i++) { TupleInfo.Type type = types.get(i - 1); switch (type) { case BOOLEAN: boolean booleanValue = resultSet.getBoolean(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(booleanValue); } break; case FIXED_INT_64: long longValue = resultSet.getLong(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(longValue); } break; case DOUBLE: double doubleValue = resultSet.getDouble(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(doubleValue); } break; case VARIABLE_BINARY: String value = resultSet.getString(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(Slices.wrappedBuffer(value.getBytes(UTF_8))); } break; default: throw new AssertionError("unhandled type: " + type); } } return builder.build(); } }; }
From source file:ManagerQuery.java
public boolean checkConstantDataForTooMuchTime() { String metricData = ""; int oldDataInt = -1; int newDataInt = -1; double oldDataDouble = -1; double newDataDouble = -1; String oldDataString = ""; String newDataString = ""; Properties conf2 = new Properties(); FileInputStream in = null;/*from w w w . j a v a 2 s. c o m*/ try { in = new FileInputStream("./config.properties"); conf2.load(in); in.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); } Connection conn; try { conn = DriverManager.getConnection(conf2.getProperty("url"), conf2.getProperty("user"), conf2.getProperty("psw")); Statement stm = conn.createStatement(); //SELEZIONA IL METRIC TYPE E, IN BASE AD ESSO, USA IL CAMPO GIUSTO DELLA TABELLA DEI DATI. switch (this.metricType) { case "Intero": metricData = "value_num"; break; case "Float": metricData = "value_num"; break; case "Testuale": metricData = "value_text"; break; case "Series": metricData = "series"; break; } if (this.metricType.contains("Percentuale")) { metricData = "value_perc1"; } String localQuery1 = "SELECT Descriptions.sameDataAlarmCount FROM Dashboard.Descriptions WHERE Descriptions.IdMetric = '" + this.idProc + "' AND sameDataAlarmCount IS NOT NULL"; ResultSet rs1; try { rs1 = stm.executeQuery(localQuery1); if (rs1 != null) { try { if (rs1.next()) { this.sameDataAlarmCount = rs1.getLong("sameDataAlarmCount"); String localQuery2 = "SELECT Data." + metricData + " " + "FROM Dashboard.Data " + "WHERE Data.IdMetric_data = '" + this.idProc + "' " + "ORDER BY Data.computationDate DESC " + "LIMIT " + this.sameDataAlarmCount; ResultSet rs2 = stm.executeQuery(localQuery2); if (rs2 != null) { try { while (rs2.next()) { if (rs2.isFirst()) { switch (this.metricType) { case "Intero": oldDataInt = rs2.getInt(metricData); newDataInt = rs2.getInt(metricData); break; case "Float": oldDataDouble = rs2.getDouble(metricData); newDataDouble = rs2.getDouble(metricData); break; case "Testuale": oldDataString = rs2.getString(metricData); newDataString = rs2.getString(metricData); break; case "Series": oldDataString = rs2.getString(metricData); newDataString = rs2.getString(metricData); break; } if (this.metricType.contains("Percentuale")) { oldDataDouble = rs2.getDouble(metricData); newDataDouble = rs2.getDouble(metricData); } } else { switch (this.metricType) { case "Intero": oldDataInt = newDataInt; newDataInt = rs2.getInt(metricData); if (oldDataInt != newDataInt) { return false; } break; case "Float": oldDataDouble = newDataDouble; newDataDouble = rs2.getDouble(metricData); if (oldDataDouble != newDataDouble) { return false; } break; case "Testuale": oldDataString = newDataString; newDataString = rs2.getString(metricData); if (!oldDataString.equals(newDataString)) { return false; } break; case "Series": oldDataString = newDataString; newDataString = rs2.getString(metricData); if (!oldDataString.equals(newDataString)) { return false; } break; } if (this.metricType.contains("Percentuale")) { oldDataDouble = newDataDouble; newDataDouble = rs2.getDouble(metricData); if (oldDataDouble != newDataDouble) { return false; } } } } //Se si esce dal ciclo senza aver mai ritornato, allora i dati sono costanti return true; } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } else { return false; } } else { return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } else { return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } }
From source file:com.nway.spring.jdbc.bean.JavassistBeanProcessor.java
private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, StringBuilder handler) throws SQLException { if (propType.equals(String.class)) { handler.append("bean.").append(writer).append("(").append("$1.getString(").append(index).append("));"); return rs.getString(index); } else if (propType.equals(Integer.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getInt(").append(index).append("));"); return rs.getInt(index); } else if (propType.equals(Integer.class)) { handler.append("bean.").append(writer).append("(").append("integerValue($1.getInt(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Integer.class); } else if (propType.equals(Long.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getLong(").append(index).append("));"); return rs.getLong(index); } else if (propType.equals(Long.class)) { handler.append("bean.").append(writer).append("(").append("longValue($1.getLong(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Long.class); } else if (propType.equals(java.sql.Date.class)) { handler.append("bean.").append(writer).append("(").append("$1.getDate(").append(index).append("));"); return rs.getDate(index); } else if (propType.equals(java.util.Date.class) || propType.equals(Timestamp.class)) { handler.append("bean.").append(writer).append("(").append("$1.getTimestamp(").append(index) .append("));"); return rs.getTimestamp(index); } else if (propType.equals(Double.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getDouble(").append(index).append("));"); return rs.getDouble(index); } else if (propType.equals(Double.class)) { handler.append("bean.").append(writer).append("(").append("doubleValue($1.getDouble(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Double.class); } else if (propType.equals(Float.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getFloat(").append(index).append("));"); return rs.getFloat(index); } else if (propType.equals(Float.class)) { handler.append("bean.").append(writer).append("(").append("floatValue($1.getFloat(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Float.class); } else if (propType.equals(Time.class)) { handler.append("bean.").append(writer).append("(").append("$1.getTime(").append(index).append("));"); return rs.getTime(index); } else if (propType.equals(Boolean.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getBoolean(").append(index).append("));"); return rs.getBoolean(index); } else if (propType.equals(Boolean.class)) { handler.append("bean.").append(writer).append("(").append("booleanValue($1.getBoolean(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Boolean.class); } else if (propType.equals(byte[].class)) { handler.append("bean.").append(writer).append("(").append("$1.getBytes(").append(index).append("));"); return rs.getBytes(index); } else if (BigDecimal.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getBigDecimal(").append(index) .append("));"); return rs.getBigDecimal(index); } else if (Blob.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getBlob(").append(index).append("));"); return rs.getBlob(index); } else if (Clob.class.equals(propType)) { handler.append("bean.").append(writer).append("(").append("$1.getClob(").append(index).append("));"); return rs.getClob(index); } else if (propType.equals(Short.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getShort(").append(index).append("));"); return rs.getShort(index); } else if (propType.equals(Short.class)) { handler.append("bean.").append(writer).append("(").append("shortValue($1.getShort(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Short.class); } else if (propType.equals(Byte.TYPE)) { handler.append("bean.").append(writer).append("(").append("$1.getByte(").append(index).append("));"); return rs.getByte(index); } else if (propType.equals(Byte.class)) { handler.append("bean.").append(writer).append("(").append("byteValue($1.getByte(").append(index) .append("),$1.wasNull()));"); return JdbcUtils.getResultSetValue(rs, index, Byte.class); } else {//from w ww.j a v a 2 s .c o m handler.append("bean.").append(writer).append("(").append("(").append(propType.getName()).append(")") .append("$1.getObject(").append(index).append("));"); return rs.getObject(index); } }
From source file:data.services.ParseBaseService.java
private void updateCars() throws SQLException, ClassNotFoundException, Exception { List<Car> carsForSaveList = new ArrayList(); List<Car> carsForUpdateList = new ArrayList(); List<Car> carList = carDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Car> ourOldIdCarMap = new HashMap(); for (Car car : carList) { ourOldIdCarMap.put(car.getCmqId(), car); }/*from ww w . j ava 2 s . co m*/ ResultSet resSet = getFromQutoBase( "SELECT c.*,cmc.title,cmsg.car_model_sub_id cms_id,cmsg.car_model_generation_id cmg_id,cmc.title completion_title,cmg.car_model_id model_id FROM car_modification c LEFT JOIN car_modification_completion cmc ON c.car_modification_completion_id=cmc.id LEFT JOIN car_model_sub_generation cmsg ON c.car_model_sub_generation_id=cmsg.id LEFT JOIN car_model_generation cmg ON cmsg.car_model_generation_id=cmg.id WHERE c.usage='ad_archive_catalog'"); while (resSet.next()) { Long cmqId = resSet.getLong("id"); actualQutoIdList.add(cmqId); Long modelQutoId = resSet.getLong("model_id"); String completionTitle = StringAdapter.getString(resSet.getString("completion_title")).trim(); Long cmsId = resSet.getLong("cms_id"); Long cmgId = resSet.getLong("cmg_id"); String title = StringAdapter.getString(resSet.getString("title")).trim(); Long cmsgId = resSet.getLong("car_model_sub_generation_id"); Long mediaId = resSet.getLong("media_id"); String url = StringAdapter.getString(resSet.getString("url")).trim(); Double price = resSet.getDouble("dt_price_min"); Model model = new Model(); model.setQutoId(modelQutoId); List<Model> supModelList = modelDao.find(model); if (!supModelList.isEmpty() && modelQutoId != 0) { model = supModelList.get(0); if (!ourOldIdCarMap.keySet().contains(cmqId)) { Car car = new Car(); car.setCmqId(cmqId); car.setModel(model); ; car.setTitle(title); car.setMediaId(mediaId); car.setUrl(url); car.setCmPrice(price); car.setCmgqId(cmgId); car.setCmsgqId(cmsgId); car.setCmsqId(cmsId); car.setCompletionTitle(completionTitle); if (validate(car, " , TroubleQutoId=" + car.getCmqId() + "; ")) { carsForSaveList.add(car); } } else { Car car = ourOldIdCarMap.get(cmqId); if (!cmgId.equals(car.getCmgqId()) || !title.equals(car.getTitle()) || !price.equals(car.getCmPrice()) || !url.equals(car.getUrl()) || !mediaId.equals(car.getMediaId()) || !completionTitle.equals(car.getCompletionTitle()) || !cmsgId.equals(car.getCmsgqId()) || !cmsId.equals(car.getCmsqId())) { car.setCmqId(cmqId); car.setModel(model); ; car.setTitle(title); car.setMediaId(mediaId); car.setUrl(url); car.setCmPrice(price); car.setCmgqId(cmgId); car.setCmsgqId(cmsgId); car.setCmsqId(cmsId); car.setCompletionTitle(completionTitle); ; if (validate(car, " , TroubleQutoId=" + car.getCmqId() + "; ")) { carsForUpdateList.add(car); } } } } } int s = 0; int u = 0; int d = 0; for (Car car : carsForSaveList) { carService.create(car); s++; } for (Car car : carsForUpdateList) { carDao.update(car); u++; } for (Long qutoId : ourOldIdCarMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; carService.delete(ourOldIdCarMap.get(qutoId)); } } addError(" : " + s + " ? ?, " + u + " , " + d + " ."); }
From source file:Query7Servlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w w w . j a v a 2 s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Connection connection = null; Statement stmt = null; String patientsWithAllSqlQuery = null; String patientsWithoutAllSqlQuery = null; ResultSet expWithAllResultSet = null; ResultSet expWithoutAllResultSet = null; String geneID = null; double expValue = 0; List<Double> exprValuesForGeneID = null; Map<String, List<Double>> geneToExpValueALL = new HashMap<String, List<Double>>(); Map<String, List<Double>> geneToExpValueNOTALL = new HashMap<String, List<Double>>(); Set<String> geneIDSet = null; String[] geneIDs = null; List<Double> listOneForGeneId = null; List<Double> listTwoForGeneId = null; Double[] listOneForGeneIdArr = null; Double[] listTwoForGeneIdArr = null; double p_value = 0; JSONArray informativeGenes = new JSONArray(); JSONObject informativeGene = null; response.setContentType("text/html;charset=UTF-8"); try (PrintWriter out = response.getWriter()) { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@VIVEK:1521/ORCL", "system", "vivek"); stmt = connection.createStatement(); patientsWithAllSqlQuery = "SELECT P.USER_ID, M.EXPRESSION FROM MICROARRAY_FACT M, PROBE P, CLINICAL_FACT C " + "WHERE M.PB_ID = P.PB_ID " + "AND M.S_ID = C.S_ID " + "AND C.P_ID IN (SELECT P_ID FROM CLINICAL_FACT, DISEASE " + "WHERE CLINICAL_FACT.DS_ID = DISEASE.DS_ID AND NAME = 'ALL')"; patientsWithoutAllSqlQuery = "SELECT P.USER_ID, M.EXPRESSION FROM MICROARRAY_FACT M, PROBE P, CLINICAL_FACT C " + "WHERE M.PB_ID = P.PB_ID " + "AND M.S_ID = C.S_ID " + "AND C.P_ID IN (SELECT P_ID FROM CLINICAL_FACT, DISEASE " + "WHERE CLINICAL_FACT.DS_ID = DISEASE.DS_ID AND NAME <> 'ALL')"; //Expression Values for patients with "ALL" expWithAllResultSet = stmt.executeQuery(patientsWithAllSqlQuery); //Converting the result set for "ALL" to the map while (expWithAllResultSet.next()) { geneID = expWithAllResultSet.getString(1); expValue = expWithAllResultSet.getDouble(2); if (geneToExpValueALL.get(geneID) != null) { exprValuesForGeneID = geneToExpValueALL.get(geneID); } else { exprValuesForGeneID = new ArrayList<Double>(); } exprValuesForGeneID.add(expValue); geneToExpValueALL.put(geneID, exprValuesForGeneID); } //Expression Values for patients without "ALL" expWithoutAllResultSet = stmt.executeQuery(patientsWithoutAllSqlQuery); while (expWithoutAllResultSet.next()) { geneID = expWithoutAllResultSet.getString(1); expValue = expWithoutAllResultSet.getDouble(2); if (geneToExpValueNOTALL.get(geneID) != null) { exprValuesForGeneID = geneToExpValueNOTALL.get(geneID); } else { exprValuesForGeneID = new ArrayList<Double>(); } exprValuesForGeneID.add(expValue); geneToExpValueNOTALL.put(geneID, exprValuesForGeneID); } //Calculating the p value for genes between two groups geneIDSet = geneToExpValueALL.keySet(); geneIDs = new String[geneIDSet.size()]; geneIDSet.toArray(geneIDs); for (int i = 0; i < geneIDs.length; i++) { listOneForGeneId = geneToExpValueALL.get(geneIDs[i]); listTwoForGeneId = geneToExpValueNOTALL.get(geneIDs[i]); listOneForGeneIdArr = new Double[listOneForGeneId.size()]; listTwoForGeneIdArr = new Double[listTwoForGeneId.size()]; listOneForGeneId.toArray(listOneForGeneIdArr); listTwoForGeneId.toArray(listTwoForGeneIdArr); p_value = new TTest().tTest(ArrayUtils.toPrimitive(listOneForGeneIdArr), ArrayUtils.toPrimitive(listTwoForGeneIdArr)); if (p_value < 0.01) { informativeGene = new JSONObject(); informativeGene.put("infogene", geneIDs[i]); informativeGenes.put(informativeGene); } } out.print(informativeGenes); //Closing the statement and connection stmt.close(); connection.close(); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); return; } catch (ClassNotFoundException e) { System.out.println("Where is your Oracle JDBC Driver?"); e.printStackTrace(); return; } catch (JSONException e) { System.out.println("Where is your Oracle JDBC Driver?"); e.printStackTrace(); return; } }
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 . ja v a 2 s . com 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); }
From source file:database.DataLoader.java
private void moveAuthorSalary() throws Exception { try {/*from w w w . j a v a 2 s .co m*/ final String tableName = Z_BID; final ResultSet set = getFromOldBase(getSelectAll(tableName)); while (set.next()) { TransactionTemplate temp = new TransactionTemplate(transactionManager); temp.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus ts) { Long oldId = 0L; try { oldId = set.getLong("id"); Long oldOrderId = set.getLong("order_id"); Long oldAuthorId = set.getLong("author_id"); Double cost = set.getDouble("reward"); Long newOrderId = getNewId(oldOrderId, ORDER); Long newAuthorId = getNewId(oldAuthorId, USERS); Order order = orderDao.find(newOrderId); Author author = authorDao.find(newAuthorId); AuthorSalary authorSalary = new AuthorSalary(); authorSalary.setOrder(order); authorSalary.setAuthor(author); authorSalary.setCost(cost); saveObjectAndLink(authorSalary, oldId, tableName); } catch (Exception e) { ts.setRollbackOnly(); addErrorMessage("authorSalary: " + oldId + " " + StringAdapter.getStackExeption(e)); log.warn("authorSalary: " + oldId + " " + StringAdapter.getStackExeption(e)); } } }); } } catch (Throwable th) { log.warn("moveAuthorToDirections " + StringAdapter.getStackExeption(th)); } }
From source file:com.skilrock.lms.web.scratchService.orderMgmt.common.RequestApproveAction.java
/** * /* w w w . j a va 2 s .c o m*/ * This method is used to approve the request by the BO Admin * * @author SkilRock Technologies * @Param takes * the parameters coming from jsp Page * @Return String throws LMSException */ @Override public String execute() throws LMSException { Connection conn = null; PreparedStatement pstmt = null; PreparedStatement pstmt1 = null; String orgQuery = null; PreparedStatement orgPstmt = null; ResultSet resultSet = null; double currCreditAmt = 0.0; double creditLimit = 0.0; String stt = null; HttpSession session = getRequest().getSession(); orderId = ((Integer) session.getAttribute("OrgId")).intValue(); System.out.println("gameNumber" + gameNumber.length); if (gameNumber.length > 0) { totalApproved = new int[gameNumber.length]; } else { totalApproved = new int[1]; } // From the front end allowed books for each game will come in the // allowedBooks[] array. If total approved books are greater than the // min boks b/w the total no of books at the BO and the remaining books. for (int i = 0; i < gameNumber.length; i++) { System.out.println("Game Number" + gameNumber[i]); if (allowedBooks[i] > findMin(nbrOfBooksAtBO[i], differenceBtBOndApprBooks[i])) { addActionError("Enter valid Alloted book value for game" + gameName[i]); setRequestApproval("No"); System.out.println("There is ERROR"); return ERROR; } System.out.println("allowedBooks[i] " + allowedBooks[i]); totalApproved[i] = allowedBooks[i]; System.out.println("Approved for game" + totalApproved[i]); } try { conn = DBConnect.getConnection(); conn.setAutoCommit(false); /* * check if available credit is <0 */ orgQuery = QueryManager.getST1OrgCreditQuery(); orgPstmt = conn.prepareStatement(orgQuery); orgPstmt.setInt(1, orderId); resultSet = orgPstmt.executeQuery(); while (resultSet.next()) { currCreditAmt = resultSet.getDouble(TableConstants.SOM_CURR_CREDIT_AMT); creditLimit = resultSet.getDouble(TableConstants.SOM_CREDIT_LIMIT); } /* * end */ String query1 = QueryManager.getST5OrderRequest4Query(); // pstmt1=conn.prepareStatement("update st_se_bo_order set // order_status='APPROVED' WHERE order_id=?"); // / if agent is having the less credit amt than he wont be allowed // for the order approved if (currCreditAmt > creditLimit) { stt = Deny(); addActionError("You Do not have enough Credit Available "); } else { pstmt1 = conn.prepareStatement(query1); // String query2 ="update st_se_bo_ordered_games set // nbr_of_books_appr="+totalApproved+" WHERE // order_id="+orderId+"and game_id="; System.out.println("Query1 from Request Aprove Action " + query1); System.out.println("OrderId>>>>" + orderId); pstmt1.setInt(1, orderId); pstmt1.executeUpdate(); String query2 = QueryManager.getST5OrderRequest3Query(); // pstmt = conn.prepareStatement("UPDATE st_se_bo_ordered_games // SET nbr_of_books_appr = ? WHERE order_id ="+orderId+" and // game_id=?"); pstmt = conn.prepareStatement(query2); for (int i = 0; i < gameNumber.length; i++) { System.out.println("gameId" + gameId[i]); pstmt.setInt(1, totalApproved[i]); pstmt.setInt(2, orderId); pstmt.setInt(3, gameId[i]); pstmt.executeUpdate(); System.out.println("gameId" + gameId[i]); } conn.commit(); setRequestApproval("Yes"); stt = "SUCCESS"; } return stt; } catch (SQLException se) { System.out.println("We got an exception while preparing a statement:" + "Probably bad SQL."); se.printStackTrace(); setRequestApproval("No"); throw new LMSException(se); } finally { try { if (pstmt != null) { pstmt.close(); } if (pstmt1 != null) { pstmt1.close(); } if (conn != null) { conn.close(); } } catch (SQLException se) { throw new LMSException(se); } } }
From source file:edu.umd.cs.marmoset.modelClasses.Project.java
/** * Populate a Submission from a ResultSet that is positioned * at a row of the submissions table.//from www. j a va2 s . c om * * @param resultSet the ResultSet containing the row data * @param startingFrom index specifying where to start fetching attributes from; * useful if the row contains attributes from multiple tables */ public void fetchValues(ResultSet resultSet, int startingFrom) throws SQLException { setProjectPK(Project.asPK(SqlUtilities.getInteger(resultSet, startingFrom++))); setCoursePK(Course.asPK(resultSet.getInt(startingFrom++))); setTestSetupPK(resultSet.getInt(startingFrom++)); setDiffAgainst(Project.asPK(resultSet.getInt(startingFrom++))); setProjectNumber(resultSet.getString(startingFrom++)); setOntime(resultSet.getTimestamp(startingFrom++)); setLate(resultSet.getTimestamp(startingFrom++)); setTitle(resultSet.getString(startingFrom++)); setUrl(resultSet.getString(startingFrom++)); setDescription(resultSet.getString(startingFrom++)); setReleaseTokens(resultSet.getInt(startingFrom++)); setRegenerationTime(resultSet.getInt(startingFrom++)); setIsTested(resultSet.getBoolean(startingFrom++)); setPair(resultSet.getBoolean(startingFrom++)); setVisibleToStudents(resultSet.getBoolean(startingFrom++)); setPostDeadlineOutcomeVisibility(resultSet.getString(startingFrom++)); setKindOfLatePenalty(resultSet.getString(startingFrom++)); setLateMultiplier(resultSet.getDouble(startingFrom++)); setLateConstant(resultSet.getInt(startingFrom++)); setCanonicalStudentRegistrationPK(StudentRegistration.asPK(resultSet.getInt(startingFrom++))); setBestSubmissionPolicy(resultSet.getString(startingFrom++)); setReleasePolicy(resultSet.getString(startingFrom++)); setStackTracePolicy(resultSet.getString(startingFrom++)); // Using -1 to represent infinity int num = resultSet.getInt(startingFrom++); if (num == -1) num = Integer.MAX_VALUE; setNumReleaseTestsRevealed(num); setArchivePK(SqlUtilities.getInteger(resultSet, startingFrom++)); setBrowserEditing(BrowserEditing.valueOfAnyCase(resultSet.getString(startingFrom++))); }