List of usage examples for org.apache.commons.lang3.math NumberUtils isNumber
public static boolean isNumber(final String str)
Checks whether the String a valid Java number.
Valid numbers include hexadecimal marked with the 0x or 0X qualifier, octal numbers, scientific notation and numbers marked with a type qualifier (e.g.
From source file:sopho.Ofeloumenoi.EditOfeloumenoiController.java
@FXML public void Save(ActionEvent event) throws IOException { if (barcode.getText().isEmpty() || onoma.getText().isEmpty() || eponimo.getText().isEmpty() || patronimo.getText().isEmpty()) { //checking if the user has filled the required fields sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? . ? ? Barcode, , ? ? ? ?", "error"); cm.showAndWait();// w w w .j a v a2 s . com } else if (!NumberUtils.isNumber(barcode.getText()) && !barcode.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " barcode ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(eisodima.getText()) && !eisodima.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else {//the user has filled the required fields. We can proceed. sopho.DBClass db = new sopho.DBClass(); Connection conn = null; PreparedStatement pst = null; ResultSet rset = null; String teknaDB = ""; //we create a var to push data to db. for (int i = 0; i < tekna.getItems().size(); i++) {//we are converting the table rows to a single comma separated string to push it to the database in a single entry. tableManager tbl = (tableManager) tekna.getItems().get(i); if (!tbl.getEtos().equals("? ")) { //we are checking if the user has actually entered a number teknaDB += tbl.getEtos() + ","; //we have to call getEtos from the tableManager class to get the actual value. We add the value to teknaDB and seperate with comma. arithmosTeknon++; } } if (arithmosTeknon > 0) {// we need to catch the case that the user has not added any data to the table. teknaDB = teknaDB.substring(0, teknaDB.length() - 1); // we have to remove the last comma. } conn = db.ConnectDB(); try { // we can push the data to database... String sql = "UPDATE ofeloumenoi SET barcode=?, eponimo=?, onoma=?, patronimo=?, mitronimo=?, imGennisis=?, dieuthinsi=?, dimos=?, tilefono=?, anergos=?, epaggelma=?, eisodima=?, eksartiseis=?, photoID=?, afm=?, tautotita=?, ethnikotita=?, metanastis=?, roma=?, oikKatastasi=?, hasTekna=?, arithmosTeknon=?, ilikiesTeknon=?, politeknos=?, monogoneiki=?, mellousaMama=?, amea=?, asfForeas=?, xronios=?, pathisi=?, anoTon60=?, monaxikos=?, emfiliVia=?, spoudastis=?, anenergos=?, loipa=? WHERE barcode=?"; pst = conn.prepareStatement(sql); //now we will set the values to the sql statement pst.setString(1, barcode.getText()); pst.setString(2, eponimo.getText()); pst.setString(3, onoma.getText()); pst.setString(4, patronimo.getText()); pst.setString(5, mitronimo.getText()); //now we have to convert the imGennisis to a suitable format to be able to push it to the database if (imGennisis.getValue() != null) { Date date = Date.from(imGennisis.getValue().atStartOfDay(ZoneId.systemDefault()).toInstant()); java.sql.Date sqlDate = new java.sql.Date(date.getTime()); pst.setDate(6, sqlDate); } else { pst.setDate(6, null); } pst.setString(7, dieuthinsi.getText()); pst.setString(8, dimos.getText()); pst.setString(9, tilefono.getText()); pst.setInt(10, anergos.isSelected() ? 1 : 0); //set 1 if selected and 0 if not. We will use this method for all the checkboxes. pst.setString(11, epaggelma.getText()); pst.setString(12, eisodima.getText()); pst.setString(13, eksartiseis.getText()); pst.setString(14, PhotoID); pst.setString(15, afm.getText()); pst.setString(16, tautotita.getText()); pst.setString(17, ethnikotita.getText()); pst.setInt(18, metanastis.isSelected() ? 1 : 0); pst.setInt(19, roma.isSelected() ? 1 : 0); pst.setInt(20, (int) oikKatastasi.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(21, arithmosTeknon > 0 ? 1 : 0); //checking number of tekna. if >0 has tekna gets 1 pst.setInt(22, arithmosTeknon); pst.setString(23, teknaDB); //here we use the converted to comma separated values variable in order to save the tableView data using only one field in database. pst.setInt(24, politeknos.isSelected() ? 1 : 0); pst.setInt(25, monogoneiki.isSelected() ? 1 : 0); pst.setInt(26, mellousaMama.isSelected() ? 1 : 0); pst.setInt(27, amea.isSelected() ? 1 : 0); pst.setInt(28, (int) asfForeas.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(29, xronios.isSelected() ? 1 : 0); pst.setString(30, pathisi.getText()); pst.setInt(31, monaxiko.isSelected() ? 1 : 0); pst.setInt(32, anoTon60.isSelected() ? 1 : 0); pst.setInt(33, emfiliVia.isSelected() ? 1 : 0); pst.setInt(34, spoudastis.isSelected() ? 1 : 0); pst.setInt(35, anenergos.isSelected() ? 1 : 0); pst.setString(36, loipa.getText()); pst.setString(37, oldBarcode); // we update the values to the database table where barcode = oldBarcode System.out.println("the query is:" + pst.toString()); int linesAffected = pst.executeUpdate(); //checking if the data were inserted to the database successfully if (linesAffected > 0) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "!", " ? ? , ? .", "confirm"); cm.showAndWait(); if (le.LockEditing(false, selID, "ofeloumenoi")) { Stage stage = (Stage) barcode.getScene().getWindow(); sl.StageLoad("/sopho/Ofeloumenoi/OfeloumenoiMain.fxml", stage, true, false); //resizable true, utility false } else { sopho.Messages.CustomMessageController cm2 = new sopho.Messages.CustomMessageController( null, "?", " ? ? ?. ? ? . ? ? ? ??.", "error"); cm2.showAndWait(); } } else {//problem inserting data... sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ?? . ? ...", "error"); cm.showAndWait(); } } catch (SQLException e) { System.out.println( "? ? ? !" + e); } } }
From source file:sopho.Ofeloumenoi.SearchOfeloumenoiController.java
@FXML public void Search(ActionEvent event) { //System.out.println("oikKatastasi sel index =" + oikKatastasi.getSelectionModel().getSelectedIndex()); //now we must check if we have at least one field filled with data or one checkbox selected if (startAge.getText().isEmpty() && endAge.getText().isEmpty() && dimos.getText().isEmpty() && !anergos.isSelected() && epaggelma.getText().isEmpty() && eisodima.getText().isEmpty() && eksartiseis.getText().isEmpty() && ethnikotita.getText().isEmpty() && !metanastis.isSelected() && !roma.isSelected() && oikKatastasi.getSelectionModel().getSelectedIndex() == -1 && arTeknon.getText().isEmpty() && !mellousaMama.isSelected() && !monogoneiki.isSelected() && !politeknos.isSelected() && asfForeas.getSelectionModel().getSelectedIndex() == -1 && !amea.isSelected() && !xronios.isSelected() && pathisi.getText().isEmpty() && !monaxiko.isSelected() && !emfiliVia.isSelected() && !spoudastis.isSelected() && !anenergos.isSelected()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", "? ? ? .", "error"); cm.showAndWait();/*from w ww . java 2s.c o m*/ } else if ((!NumberUtils.isNumber(startAge.getText()) && !startAge.getText().isEmpty()) && (!NumberUtils.isNumber(endAge.getText()) && !endAge.getText().isEmpty())) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(eisodima.getText()) && !eisodima.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(arTeknon.getText()) && !arTeknon.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ? ??. ? ? .", "error"); cm.showAndWait(); } else {//the user has filled at least one field or checked a checkbox if ((!startAge.getText().isEmpty() && !endAge.getText().isEmpty()) && (Integer.parseInt(startAge.getText()) > Integer.parseInt(endAge.getText()))) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? . '' ? ?? ''", "error"); cm.showAndWait(); } else { try { String sql = "SELECT * FROM ofeloumenoi WHERE "; if (!startAge.getText().isEmpty()) { sql += "FLOOR(TIMESTAMPDIFF(hour,imGennisis,CURRENT_TIMESTAMP())/8766)>=" + Integer.parseInt(startAge.getText()) + " AND "; } if (!endAge.getText().isEmpty()) { sql += "FLOOR(TIMESTAMPDIFF(hour,imGennisis,CURRENT_TIMESTAMP())/8766)<=" + Integer.parseInt(endAge.getText()) + " AND "; } if (!dimos.getText().isEmpty()) { sql += "dimos = '" + dimos.getText() + "' AND "; } if (anergos.isSelected()) { sql += "anergos=1 AND "; } if (!epaggelma.getText().isEmpty()) { sql += "epaggelma='" + epaggelma.getText() + "' AND "; } if (!eisodima.getText().isEmpty()) { sql += "eisodima='" + eisodima.getText() + "' AND "; } if (!eksartiseis.getText().isEmpty()) { sql += "eksartiseis='" + eksartiseis.getText() + "' AND "; } if (!ethnikotita.getText().isEmpty()) { sql += "ethnikotita='" + ethnikotita.getText() + "' AND "; } if (metanastis.isSelected()) { sql += "metanastis=1 AND "; } if (roma.isSelected()) { sql += "roma=1 AND "; } int oikSelected = oikKatastasi.getSelectionModel().getSelectedIndex(); if (oikSelected > 0) { sql += "oikKatastasi=" + oikSelected + " AND "; } if (!arTeknon.getText().isEmpty()) { sql += "arithmosTeknon='" + arTeknon.getText() + "' AND "; } if (mellousaMama.isSelected()) { sql += "mellousaMama=1 AND "; } if (monogoneiki.isSelected()) { sql += "monogoneiki=1 AND "; } if (politeknos.isSelected()) { sql += "politeknos=1 AND "; } int asfSelected = asfForeas.getSelectionModel().getSelectedIndex(); if (asfSelected > 0) { sql += "asfForeas=" + asfSelected + " AND "; } if (amea.isSelected()) { sql += "amea=1 AND "; } if (xronios.isSelected()) { sql += "xronios=1 AND "; } if (!pathisi.getText().isEmpty()) { sql += "pathisi='" + pathisi.getText() + "' AND "; } if (monaxiko.isSelected()) { sql += "monaxikos=1 AND "; } if (emfiliVia.isSelected()) { sql += "emfiliVia=1 AND "; } if (spoudastis.isSelected()) { sql += "spoudastis=1 AND "; } if (anenergos.isSelected()) { sql += "anenergos=1 AND "; } sql = sql.substring(0, sql.length() - 4); //we have to remove the AND form the end of the string System.out.println("bare sql :" + sql); conn = db.ConnectDB(); pst = conn.prepareStatement(sql); System.out.println("pst sql :" + pst); rs = pst.executeQuery(); rs.last();//we go to the last line to get its number if (rs.getRow() > 0) {//we have results sopho.ResultKeeper.rs = rs; // we keep the results to a static var to access them later. if (sopho.StageLoader.lastStage.equals("/sopho/Ofeloumenoi/OfeloumenoiMain.fxml")) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController( null, "!", "? ...", "confirm"); cm.showAndWait(); Stage stage = (Stage) startAge.getScene().getWindow(); try { sl.StageLoad("/sopho/Ofeloumenoi/SearchOfeloumenoiResults.fxml", stage, true, false); //resizable true, utility false } catch (IOException ex) { Logger.getLogger(SearchOfeloumenoiController.class.getName()).log(Level.SEVERE, null, ex); } } else { Stage stage = (Stage) startAge.getScene().getWindow(); try { sl.StageLoad("/sopho/Ofeloumenoi/FiltersStatistika.fxml", stage, true, false); //resizable true, utility false } catch (IOException ex) { Logger.getLogger(SearchOfeloumenoiController.class.getName()).log(Level.SEVERE, null, ex); } } } else {//we don't have results sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?...", " ? ?? ?. ??.", "error"); cm.showAndWait(); } } catch (SQLException ex) { Logger.getLogger(SearchOfeloumenoiController.class.getName()).log(Level.SEVERE, null, ex); } } } }
From source file:sopho.Ofeloumenoi.SearchToEditOfeloumenoiController.java
@FXML public void Search(ActionEvent event) throws IOException, SQLException { if (CheckEmpty()) { if (!NumberUtils.isNumber(barcode.getText()) && !barcode.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " barcode ? ? ??. ? ? .", "error"); cm.showAndWait();/* w ww . j a v a2 s. co m*/ } else { //we have at least one field filled String sql = "SELECT * FROM ofeloumenoi WHERE "; if (!barcode.getText().isEmpty()) { sql = sql + "barcode = " + barcode.getText() + " AND "; } if (!onoma.getText().isEmpty()) { sql = sql + "onoma = '" + onoma.getText() + "' AND "; } if (!eponimo.getText().isEmpty()) { sql = sql + "eponimo = '" + eponimo.getText() + "' AND "; } if (!tilefono.getText().isEmpty()) { sql = sql + "tilefono = '" + tilefono.getText() + "' AND "; } if (!afm.getText().isEmpty()) { sql = sql + "afm = '" + afm.getText() + "' AND "; } if (!tautotita.getText().isEmpty()) { sql = sql + "tautotita = '" + tautotita.getText() + "'"; } else { // we need to remove the AND from the end of the sql string sql = sql.substring(0, sql.length() - 4); } System.out.println("Search sql: " + sql); conn = db.ConnectDB(); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); // checking if there is at least one result rs.last(); // setting the resultSet to the last line to get its index number. if (rs.getRow() > 0) { sopho.ResultKeeper.rs = rs; //we are keeping the resultSet to a static var to be able to get the data at the next scene. Stage stage = (Stage) onoma.getScene().getWindow(); if (rs.getRow() == 1) { // there is only one result sopho.ResultKeeper.selectedIndex = 0; sopho.ResultKeeper.multipleResults = false; //we have to know if there are multiple results to set properly the backButton at EditOfeloumenoiController sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "!", "? ?: " + rs.getString("eponimo") + " " + rs.getString("onoma"), "confirm"); cm.showAndWait(); if (StageLoader.lastStage.equals("/sopho/Ofeloumenoi/OfeloumenoiMain.fxml")) { int id = rs.getInt("id"); if (!le.CheckLock(id, "ofeloumenoi")) {//check if editing is locked because another user is currently editing the data. if (!le.LockEditing(true, id, "ofeloumenoi")) {//check if lock editing is successful else display message about it sopho.Messages.CustomMessageController cm2 = new sopho.Messages.CustomMessageController( null, "?", " ? ? ?. ? ? ? ? ?? ? ?. ? ? .", "error"); cm2.showAndWait(); } sl.StageLoad("/sopho/Ofeloumenoi/EditOfeloumenoi.fxml", stage, true, false); //resizable true, utility false. } else { sopho.Messages.CustomMessageController cm3 = new sopho.Messages.CustomMessageController( null, "?!", " ? ? ?. ? ? ? .", "error"); cm3.showAndWait(); } } else if (StageLoader.lastStage.equals("/sopho/Eidi/EidiMain.fxml")) { sl.StageLoad("/sopho/Eidi/EidiDothikan.fxml", stage, true, false); //resizable true, utility false. } } else { //there are more than one results. We need to choose from them sopho.ResultKeeper.multipleResults = true; //we have to know if there are multiple results to set properly the backButton at EditOfeloumenoiController sl.StageLoad("/sopho/Ofeloumenoi/MultipleSearchResults.fxml", stage, true, false); //resizable true, utility false. } } else { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?...", " ? ? ?. .", "error"); cm.showAndWait(); } } } }
From source file:tasly.greathealth.jd.order.service.impl.JdOrderRetrieveServiceImpl.java
private Double safe2Double(final String str) { return StringUtils.isNotBlank(str) && NumberUtils.isNumber(str) ? Double.parseDouble(str) : 0d; }
From source file:tds.dll.mssql.CommonDLL.java
public void _FormatMessage_SP(SQLConnection connection, String clientname, String language, String context, String appkey, _Ref<String> errmsg, String argstring, Character delimiter, String subject, String grade) throws ReturnStatusException { String[] rows = null;//from w w w . ja v a 2 s. co m String msg = null; Integer msgId = null; if (argstring != null) { if (delimiter == null) delimiter = ','; rows = _BuildTableAsArray(argstring, delimiter.toString(), -1); } String msgkey = TDS_GetMessagekey_FN(connection, clientname, "database", "database", context, appkey, language, grade, subject); if (msgkey == null) { msg = String.format("%s [-----]", appkey); try { final String SQL_QUERY1 = "select top 1 application from _MissingMessages where application ='database' and context = ${context} " + " and contextType = 'database' and appkey = ${appkey} and message = ${msg}"; SqlParametersMaps parms1 = (new SqlParametersMaps()).put("context", context).put("appkey", appkey) .put("msg", msg); if (exists(executeStatement(connection, SQL_QUERY1, parms1, false)) == false) { final String SQL_INSERT2 = "insert into _MissingMessages(application,contextType,context, appkey,message) " + " values ('database', 'database', ${context}, ${appkey}, ${msg})"; SqlParametersMaps parms2 = parms1; executeStatement(connection, SQL_INSERT2, parms2, false); } } catch (ReturnStatusException e) { _logger.error(String.format("Failed inserting rec into _MissingMessages: %s", e.getMessage())); } errmsg.set(msg); return; } if (NumberUtils.isNumber(msgkey)) { final String SQL_QUERY3 = "select message, messageID from ${ConfigDB}.TDS_CoreMessageObject where _Key = ${msgkey}"; SqlParametersMaps parms3 = (new SqlParametersMaps()).put("msgkey", msgkey); SingleDataResultSet result = executeStatement(connection, fixDataBaseNames(SQL_QUERY3), parms3, false) .getResultSets().next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { msg = record.<String>get("message"); msgId = record.<Integer>get("messageID"); } } else { final String SQL_QUERY4 = " select T.message, messageID from ${ConfigDB}.TDS_CoreMessageObject O, ${ConfigDB}.Client_MessageTranslation T " + " where T._Key = ${msgkey} and O._Key = T._fk_CoreMessageObject"; SqlParametersMaps parms4 = (new SqlParametersMaps()).put("msgkey", msgkey); SingleDataResultSet result = executeStatement(connection, fixDataBaseNames(SQL_QUERY4), parms4, false) .getResultSets().next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { msg = record.<String>get("message"); msgId = record.<Integer>get("messageID"); } } if (rows != null && msg != null) { for (int counter1 = 0; counter1 < rows.length; ++counter1) { Object value = rows[counter1]; if (value != null) { Pattern p = Pattern.compile("\\{" + counter1 + "\\}"); Matcher m = p.matcher(msg); msg = m.replaceAll(value.toString()); } } } msg = String.format("%s [%d]", msg, msgId); errmsg.set(msg); }
From source file:tds.dll.mssql.StudentDLL.java
public void _InsertTestoppScores_SP(SQLConnection connection, UUID testoppkey, String scoreString, Character coldelim, Character rowdelim, _Ref<String> msgRef) throws ReturnStatusException { String clientname = null, test = null, subject = null; // @scores table (strand varchar(max), measure varchar(max), val // varchar(100), se float, ability bigint default 0); DataBaseTable scoresTbl = getDataBaseTable("scores") .addColumn("strand", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("measure", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("val", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 50).addColumn("se", SQL_TYPE_To_JAVA_TYPE.FLOAT) .addColumn("ability", SQL_TYPE_To_JAVA_TYPE.BIGINT); connection.createTemporaryTable(scoresTbl); try {/*from w w w. ja va2s. c om*/ final String SQL_QUERY1 = "select clientname, _efk_TestID as test, Subject from TestOpportunity where _Key = ${testoppkey}"; SqlParametersMaps parms1 = (new SqlParametersMaps()).put("testoppkey", testoppkey); SingleDataResultSet result = executeStatement(connection, SQL_QUERY1, parms1, false).getResultSets() .next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { clientname = record.<String>get("clientname"); test = record.<String>get("test"); subject = record.<String>get("subject"); } // -- check the string for rowdelim termination. If not, make it so. boolean isOfficial; final String SQL_QUERY2 = "select top 1 _fk_TestOpportunity from TesteeResponse " + " where _fk_TestOpportunity = ${testoppkey} and _efk_ITSItem is not null and score = -1 and IsFieldTest = 0"; SqlParametersMaps parms2 = parms1; if (exists(executeStatement(connection, SQL_QUERY1, parms1, false))) isOfficial = false; else isOfficial = true; String[] rows = _commonDll._BuildTableAsArray(scoreString, rowdelim.toString(), -1); for (String row : rows) { String[] cols = _commonDll._BuildTableAsArray(row, coldelim.toString(), -1); String strand = (cols.length >= 1 ? cols[0] : null); String measure = (cols.length >= 2 ? cols[1] : null); String val = (cols.length >= 3 ? cols[2] : null); String seStr = (cols.length >= 4 ? cols[3] : null); Float se = null; if (seStr != null && NumberUtils.isNumber(seStr)) se = Float.parseFloat(seStr); Long ability = 0L; final String SQL_INSERT3 = "insert into ${scoresTblName} (strand, measure, val, se, ability) values (${strand}, ${measure}, ${val}, ${se}, ${ability})"; SqlParametersMaps parms3 = (new SqlParametersMaps()).put("strand", strand).put("measure", measure) .put("val", val).put("se", se).put("ability", ability); Map<String, String> unquotedParms3 = new HashMap<>(); unquotedParms3.put("scoresTblName", scoresTbl.getTableName()); int insertedCnt = executeStatement(connection, fixDataBaseNames(SQL_INSERT3, unquotedParms3), parms3, false).getUpdateCount(); } final String SQL_QUERY4 = "select top 1 _fk_TestOpportunity from TestOpportunityScores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms4 = parms1; if (exists(executeStatement(connection, SQL_QUERY4, parms4, false))) { if (AuditScores_FN(connection, clientname)) { final String SQL_INSERT5 = "insert into ${ArchiveDB}.TestOpportunityScores_Audit (_fk_TestOpportunity, subject, MeasureOf, MeasureLabel, value, standardError)" + " select ${testoppkey}, subject, MeasureOf, MeasureLabel, value, standardError " + " from TestOpportunityScores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms5 = parms1; int insertedCnt = executeStatement(connection, fixDataBaseNames(SQL_INSERT5), parms5, false) .getUpdateCount(); } final String SQL_DELETE6 = "delete from TestOpportunityScores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms6 = parms1; int deletedCnt = executeStatement(connection, SQL_DELETE6, parms6, false).getUpdateCount(); } final String SQL_UPDATE7 = "update ${scoresTblName} set ability = UseForAbility from ${ConfigDB}.Client_TestscoreFeatures F " + " where F.ClientName = ${clientname} and (F.TestID = ${test} or F.TestID = '*') and F.MeasureOf = strand and F.MeasureLabel = measure"; Map<String, String> unquotedParms7 = new HashMap<>(); unquotedParms7.put("scoresTblName", scoresTbl.getTableName()); SqlParametersMaps parms7 = (new SqlParametersMaps()).put("clientname", clientname).put("test", test); final String query7 = fixDataBaseNames(SQL_UPDATE7); int updatedCnt = executeStatement(connection, fixDataBaseNames(query7, unquotedParms7), parms7, false) .getUpdateCount(); final String SQL_INSERT8 = "insert into TestOpportunityScores " + "(_fk_TestOpportunity, subject, UseforAbility, MeasureOf, MeasureLabel, value, standardError) " + " select ${testoppkey}, ${subject}, ability, strand, measure, val, se from ${scoresTblName}"; Map<String, String> unquotedParms8 = unquotedParms7; SqlParametersMaps parms8 = (new SqlParametersMaps()).put("testoppkey", testoppkey).put("subject", subject); int insertedCnt = executeStatement(connection, fixDataBaseNames(SQL_INSERT8, unquotedParms8), parms8, false).getUpdateCount(); connection.dropTemporaryTable(scoresTbl); } catch (ReturnStatusException re) { _commonDll._LogDBError_SP(connection, "_InsertTestoppScores", re.getMessage(), null, null, null, testoppkey); msgRef.set(re.getMessage()); connection.dropTemporaryTable(scoresTbl); } }
From source file:tds.dll.mssql.StudentDLL.java
/** * Description of Isgradeequiv(connection, grade1, grade2) Checks if a strings * - Grade1 & Grade2 are numerics// w w w . j av a 2 s . co m * * @param connection * - connection object * @param grade1 * - grade1(String) * @param grade2 * - grade2(String) * @return result - Boolean */ public boolean __IsGradeEquiv_FN(String grade1, String grade2) { boolean result = false; if (NumberUtils.isNumber(grade1) && NumberUtils.isNumber(grade2)) { if (Float.parseFloat(grade1) == Float.parseFloat(grade2)) { result = true; } } return result; }
From source file:tds.dll.mysql.CommonDLL.java
public void _FormatMessage_SP(SQLConnection connection, String clientname, String language, String context, String appkey, _Ref<String> errmsg, String argstring, Character delimiter, String subject, String grade) throws ReturnStatusException { String[] rows = null;//from w w w. j a v a 2s . c o m String msg = null; Integer msgId = null; if (argstring != null) { if (delimiter == null) delimiter = ','; rows = _BuildTableAsArray(argstring, delimiter.toString(), -1); } String msgkey = TDS_GetMessagekey_FN(connection, clientname, "database", "database", context, appkey, language, grade, subject); if (msgkey == null) { msg = String.format("%s [-----]", appkey); try { final String SQL_QUERY1 = "select application from _missingmessages where application ='database' and context = ${context} " + " and contextType = 'database' and appkey = ${appkey} and message = ${msg} limit 1"; SqlParametersMaps parms1 = (new SqlParametersMaps()).put("context", context).put("appkey", appkey) .put("msg", msg); if (exists(executeStatement(connection, SQL_QUERY1, parms1, false)) == false) { final String SQL_INSERT2 = "insert into _missingmessages(application,contextType,context, appkey,message) " + " values ('database', 'database', ${context}, ${appkey}, ${msg})"; SqlParametersMaps parms2 = parms1; executeStatement(connection, SQL_INSERT2, parms2, false); } } catch (ReturnStatusException e) { _logger.error(String.format("Failed inserting rec into _missingmessages: %s", e.getMessage())); } errmsg.set(msg); return; } if (NumberUtils.isNumber(msgkey)) { final String SQL_QUERY3 = "select message, messageID from ${ConfigDB}.tds_coremessageobject where _Key = ${msgkey}"; SqlParametersMaps parms3 = (new SqlParametersMaps()).put("msgkey", msgkey); SingleDataResultSet result = executeStatement(connection, fixDataBaseNames(SQL_QUERY3), parms3, false) .getResultSets().next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { msg = record.<String>get("message"); msgId = record.<Integer>get("messageID"); } } else { try { UUID msgkeyUuid = UUID.fromString(msgkey); final String SQL_QUERY4 = " select T.message, messageID from ${ConfigDB}.tds_coremessageobject O, ${ConfigDB}.client_messagetranslation T " + " where T._Key = ${msgkeyuuid} and O._Key = T._fk_CoreMessageObject"; SqlParametersMaps parms4 = (new SqlParametersMaps()).put("msgkeyuuid", msgkeyUuid); SingleDataResultSet result = executeStatement(connection, fixDataBaseNames(SQL_QUERY4), parms4, false).getResultSets().next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { msg = record.<String>get("message"); msgId = record.<Integer>get("messageID"); } } catch (IllegalArgumentException ie) { _logger.error(String.format("MesageKey is not of UUID format: %s", msgkey)); } } if (rows != null && msg != null) { for (int counter1 = 0; counter1 < rows.length; ++counter1) { Object value = rows[counter1]; if (value != null) { Pattern p = Pattern.compile("\\{" + counter1 + "\\}"); Matcher m = p.matcher(msg); msg = m.replaceAll(value.toString()); } } } msg = String.format("%s [%d]", msg, msgId); errmsg.set(msg); }
From source file:tds.dll.mysql.StudentDLL.java
public void _InsertTestoppScores_SPV1(SQLConnection connection, UUID testoppkey, String scoreString, Character coldelim, Character rowdelim, _Ref<String> msgRef) throws ReturnStatusException { String clientname = null, test = null, subject = null; // @scores table (strand varchar(max), measure varchar(max), val // varchar(100), se float, ability bigint default 0); DataBaseTable scoresTbl = getDataBaseTable("scores") .addColumn("strand", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("measure", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("val", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 50).addColumn("se", SQL_TYPE_To_JAVA_TYPE.FLOAT) .addColumn("ability", SQL_TYPE_To_JAVA_TYPE.BIGINT); connection.createTemporaryTable(scoresTbl); try {//from ww w . j av a2 s. com final String SQL_QUERY1 = "select clientname, _efk_TestID as test, Subject from testopportunity where _Key = ${testoppkey}"; SqlParametersMaps parms1 = (new SqlParametersMaps()).put("testoppkey", testoppkey); SingleDataResultSet result = executeStatement(connection, SQL_QUERY1, parms1, false).getResultSets() .next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { clientname = record.<String>get("clientname"); test = record.<String>get("test"); subject = record.<String>get("subject"); } // -- check the string for rowdelim termination. If not, make it so. boolean isOfficial; final String SQL_QUERY2 = "select _fk_TestOpportunity from testeeresponse " + " where _fk_TestOpportunity = ${testoppkey} and _efk_ITSItem is not null and score = -1 and IsFieldTest = 0 limit 1"; SqlParametersMaps parms2 = parms1; if (exists(executeStatement(connection, SQL_QUERY1, parms1, false))) isOfficial = false; else isOfficial = true; String[] rows = _commonDll._BuildTableAsArray(scoreString, rowdelim.toString(), -1); for (String row : rows) { String[] cols = _commonDll._BuildTableAsArray(row, coldelim.toString(), -1); String strand = (cols.length >= 1 ? cols[0] : null); String measure = (cols.length >= 2 ? cols[1] : null); String val = (cols.length >= 3 ? cols[2] : null); String seStr = (cols.length >= 4 ? cols[3] : null); Float se = null; if (seStr != null && NumberUtils.isNumber(seStr)) se = Float.parseFloat(seStr); Long ability = 0L; final String SQL_INSERT3 = "insert into ${scoresTblName} (strand, measure, val, se, ability) values (${strand}, ${measure}, ${val}, ${se}, ${ability})"; SqlParametersMaps parms3 = (new SqlParametersMaps()).put("strand", strand).put("measure", measure) .put("val", val).put("se", se).put("ability", ability); Map<String, String> unquotedParms3 = new HashMap<>(); unquotedParms3.put("scoresTblName", scoresTbl.getTableName()); executeStatement(connection, fixDataBaseNames(SQL_INSERT3, unquotedParms3), parms3, false) .getUpdateCount(); } final String SQL_QUERY4 = "select _fk_TestOpportunity from testopportunityscores where _fk_TestOpportunity = ${testoppkey} limit 1"; SqlParametersMaps parms4 = parms1; if (exists(executeStatement(connection, SQL_QUERY4, parms4, false))) { if (AuditScores_FN(connection, clientname)) { // String sessionDB = getAppSettings ().get ("TDSSessionDBName"); String sessionDB = getTdsSettings().getTDSSessionDBName(); final String SQL_INSERT5 = "insert into ${ArchiveDB}.testopportunityscores_audit (_fk_TestOpportunity, subject, MeasureOf, MeasureLabel, value, standardError, _date, dbname)" + " select ${testoppkey}, subject, MeasureOf, MeasureLabel, value, standardError, now(3), ${dbname} " + " from testopportunityscores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms5 = (new SqlParametersMaps()).put("testoppkey", testoppkey).put("dbname", sessionDB); executeStatement(connection, fixDataBaseNames(SQL_INSERT5), parms5, false).getUpdateCount(); } final String SQL_DELETE6 = "delete from testopportunityscores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms6 = parms1; executeStatement(connection, SQL_DELETE6, parms6, false).getUpdateCount(); } final String SQL_UPDATE7 = "update ${scoresTblName} S, ${ConfigDB}.client_testscorefeatures F set S.ability = F.UseForAbility " + " where F.ClientName = ${clientname} and (F.TestID = ${test} or F.TestID = '*') and F.MeasureOf = strand and F.MeasureLabel = measure"; Map<String, String> unquotedParms7 = new HashMap<>(); unquotedParms7.put("scoresTblName", scoresTbl.getTableName()); SqlParametersMaps parms7 = (new SqlParametersMaps()).put("clientname", clientname).put("test", test); final String query7 = fixDataBaseNames(SQL_UPDATE7); executeStatement(connection, fixDataBaseNames(query7, unquotedParms7), parms7, false).getUpdateCount(); final String SQL_INSERT8 = "insert into testopportunityscores " + "(_fk_TestOpportunity, subject, UseforAbility, MeasureOf, MeasureLabel, value, standardError, _date, hostname) " + " select ${testoppkey}, ${subject}, ability, strand, measure, val, se, now(3), ${localhost} from ${scoresTblName}"; Map<String, String> unquotedParms8 = unquotedParms7; SqlParametersMaps parms8 = (new SqlParametersMaps()).put("testoppkey", testoppkey) .put("subject", subject).put("localhost", _commonDll.getLocalhostName()); executeStatement(connection, fixDataBaseNames(SQL_INSERT8, unquotedParms8), parms8, false) .getUpdateCount(); connection.dropTemporaryTable(scoresTbl); } catch (ReturnStatusException re) { _commonDll._LogDBError_SP(connection, "_InsertTestoppScores", re.getMessage(), null, null, null, testoppkey); msgRef.set(re.getMessage()); connection.dropTemporaryTable(scoresTbl); } }
From source file:tds.dll.mysql.StudentDLL.java
public void _InsertTestoppScores_SP(SQLConnection connection, UUID testoppkey, String scoreString, Character coldelim, Character rowdelim, _Ref<String> msgRef) throws ReturnStatusException { Date now = _dateUtil.getDateWRetStatus(connection); String clientname = null, test = null, subject = null; // @scores table (strand varchar(max), measure varchar(max), val // varchar(100), se float, ability bigint default 0); DataBaseTable scoresTbl = getDataBaseTable("scores") .addColumn("strand", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("measure", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 2000) .addColumn("val", SQL_TYPE_To_JAVA_TYPE.VARCHAR, 50).addColumn("se", SQL_TYPE_To_JAVA_TYPE.FLOAT) .addColumn("ability", SQL_TYPE_To_JAVA_TYPE.BIGINT); connection.createTemporaryTable(scoresTbl); try {/*from w w w .j a v a2s. c om*/ final String SQL_QUERY1 = "select clientname, _efk_TestID as test, Subject from testopportunity where _Key = ${testoppkey}"; SqlParametersMaps parms1 = (new SqlParametersMaps()).put("testoppkey", testoppkey); SingleDataResultSet result = executeStatement(connection, SQL_QUERY1, parms1, false).getResultSets() .next(); DbResultRecord record = (result.getCount() > 0 ? result.getRecords().next() : null); if (record != null) { clientname = record.<String>get("clientname"); test = record.<String>get("test"); subject = record.<String>get("subject"); } // -- check the string for rowdelim termination. If not, make it so. boolean isOfficial; final String SQL_QUERY2 = "select _fk_TestOpportunity from testeeresponse " + " where _fk_TestOpportunity = ${testoppkey} and _efk_ITSItem is not null and score = -1 and IsFieldTest = 0 limit 1"; SqlParametersMaps parms2 = parms1; if (exists(executeStatement(connection, SQL_QUERY1, parms1, false))) isOfficial = false; else isOfficial = true; List<CaseInsensitiveMap<Object>> resultList = new ArrayList<CaseInsensitiveMap<Object>>(); // Just sanity check to avoid exceptionon looping thru rows if (scoreString == null) scoreString = ""; String[] rows = _commonDll._BuildTableAsArray(scoreString, rowdelim.toString(), -1); for (String row : rows) { String[] cols = _commonDll._BuildTableAsArray(row, coldelim.toString(), -1); String strand = (cols.length >= 1 ? cols[0] : null); String measure = (cols.length >= 2 ? cols[1] : null); String val = (cols.length >= 3 ? cols[2] : null); String seStr = (cols.length >= 4 ? cols[3] : null); Float se = null; if (seStr != null && NumberUtils.isNumber(seStr)) se = Float.parseFloat(seStr); Long ability = 0L; CaseInsensitiveMap<Object> rcd = new CaseInsensitiveMap<Object>(); rcd.put("strand", strand); rcd.put("measure", measure); rcd.put("val", val); rcd.put("se", se); rcd.put("ability", ability); resultList.add(rcd); } SingleDataResultSet result1 = new SingleDataResultSet(); result1.addColumn("strand", SQL_TYPE_To_JAVA_TYPE.VARCHAR); result1.addColumn("measure", SQL_TYPE_To_JAVA_TYPE.VARCHAR); result1.addColumn("val", SQL_TYPE_To_JAVA_TYPE.VARCHAR); result1.addColumn("se", SQL_TYPE_To_JAVA_TYPE.FLOAT); result1.addColumn("ability", SQL_TYPE_To_JAVA_TYPE.BIGINT); result1.addRecords(resultList); insertBatch(connection, scoresTbl.generateInsertStatement(), result1, null); final String SQL_QUERY4 = "select _fk_TestOpportunity from testopportunityscores where _fk_TestOpportunity = ${testoppkey} limit 1"; SqlParametersMaps parms4 = parms1; if (exists(executeStatement(connection, SQL_QUERY4, parms4, false))) { if (AuditScores_FN(connection, clientname)) { // String sessionDB = getAppSettings ().get ("TDSSessionDBName"); String sessionDB = getTdsSettings().getTDSSessionDBName(); final String SQL_INSERT5 = "insert into ${ArchiveDB}.testopportunityscores_audit (_fk_TestOpportunity, subject, MeasureOf, MeasureLabel, value, standardError, _date, dbname)" + " select ${testoppkey}, subject, MeasureOf, MeasureLabel, value, standardError, now(3), ${dbname} " + " from testopportunityscores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms5 = (new SqlParametersMaps()).put("testoppkey", testoppkey).put("dbname", sessionDB); executeStatement(connection, fixDataBaseNames(SQL_INSERT5), parms5, false).getUpdateCount(); } final String SQL_DELETE6 = "delete from testopportunityscores where _fk_TestOpportunity = ${testoppkey}"; SqlParametersMaps parms6 = parms1; executeStatement(connection, SQL_DELETE6, parms6, false).getUpdateCount(); } final String SQL_UPDATE7 = "update ${scoresTblName} S, ${ConfigDB}.client_testscorefeatures F set S.ability = F.UseForAbility " + " where F.ClientName = ${clientname} and (F.TestID = ${test} or F.TestID = '*') and F.MeasureOf = strand and F.MeasureLabel = measure"; Map<String, String> unquotedParms7 = new HashMap<>(); unquotedParms7.put("scoresTblName", scoresTbl.getTableName()); SqlParametersMaps parms7 = (new SqlParametersMaps()).put("clientname", clientname).put("test", test); final String query7 = fixDataBaseNames(SQL_UPDATE7); executeStatement(connection, fixDataBaseNames(query7, unquotedParms7), parms7, false).getUpdateCount(); final String SQL_INSERT8 = "insert into testopportunityscores " + "(_fk_TestOpportunity, subject, UseforAbility, MeasureOf, MeasureLabel, value, standardError, _date, hostname) " + " select ${testoppkey}, ${subject}, ability, strand, measure, val, se, now(3), ${localhost} from ${scoresTblName}"; Map<String, String> unquotedParms8 = unquotedParms7; SqlParametersMaps parms8 = (new SqlParametersMaps()).put("testoppkey", testoppkey) .put("subject", subject).put("localhost", _commonDll.getLocalhostName()); executeStatement(connection, fixDataBaseNames(SQL_INSERT8, unquotedParms8), parms8, false) .getUpdateCount(); connection.dropTemporaryTable(scoresTbl); } catch (ReturnStatusException re) { _commonDll._LogDBError_SP(connection, "_InsertTestoppScores", re.getMessage(), null, null, null, testoppkey); msgRef.set(re.getMessage()); connection.dropTemporaryTable(scoresTbl); } _commonDll._LogDBLatency_SP(connection, "_InsertTestoppScores", now, null, true, null, testoppkey); }