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.skilrock.lms.web.scratchService.orderMgmt.common.RequestApproveAction.java
public String executeAgent() throws LMSException { Connection conn = null;//from ww w . j a v a2s.c om 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]); System.out.println("ssssss " + getAllowedBooks()); System.out.println("11 " + allowedBooks[i] + "22 " + findMin(nbrOfBooksAtAgent[i], differenceBtAgentandApprBooks[i])); if (allowedBooks[i] > findMin(nbrOfBooksAtAgent[i], differenceBtAgentandApprBooks[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.getST5RetailerOrderRequest4Query(); // 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.getST5RetailerOrderRequest3Query(); // 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:com.flexive.core.storage.GenericDivisionExporter.java
/** * Dump a generic table to XML// w w w .j a va 2 s . c om * * @param tableName name of the table * @param stmt an open statement * @param out output stream * @param sb an available and valid StringBuilder * @param xmlTag name of the xml tag to write per row * @param idColumn (optional) id column to sort results * @param onlyBinaries process binary fields (else these will be ignored) * @throws SQLException on errors * @throws IOException on errors */ private void dumpTable(String tableName, Statement stmt, OutputStream out, StringBuilder sb, String xmlTag, String idColumn, boolean onlyBinaries) throws SQLException, IOException { ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + (StringUtils.isEmpty(idColumn) ? "" : " ORDER BY " + idColumn + " ASC")); final ResultSetMetaData md = rs.getMetaData(); String value, att; boolean hasSubTags; while (rs.next()) { hasSubTags = false; if (!onlyBinaries) { sb.setLength(0); sb.append(" <").append(xmlTag); } for (int i = 1; i <= md.getColumnCount(); i++) { value = null; att = md.getColumnName(i).toLowerCase(); switch (md.getColumnType(i)) { case java.sql.Types.DECIMAL: case java.sql.Types.NUMERIC: case java.sql.Types.BIGINT: if (!onlyBinaries) { value = String.valueOf(rs.getBigDecimal(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.INTEGER: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (!onlyBinaries) { value = String.valueOf(rs.getLong(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (!onlyBinaries) { value = String.valueOf(rs.getDouble(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (!onlyBinaries) { final Timestamp ts = rs.getTimestamp(i); if (rs.wasNull()) value = null; else value = FxFormatUtils.getDateTimeFormat().format(ts); } break; case java.sql.Types.BIT: case java.sql.Types.CHAR: case java.sql.Types.BOOLEAN: if (!onlyBinaries) { value = rs.getBoolean(i) ? "1" : "0"; if (rs.wasNull()) value = null; } break; case java.sql.Types.CLOB: case java.sql.Types.BLOB: case java.sql.Types.LONGVARBINARY: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARBINARY: case java.sql.Types.VARCHAR: case java.sql.Types.BINARY: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: hasSubTags = true; break; default: LOG.warn("Unhandled type [" + md.getColumnType(i) + "] for [" + tableName + "." + att + "]"); } if (value != null && !onlyBinaries) sb.append(' ').append(att).append("=\"").append(value).append("\""); } if (hasSubTags) { if (!onlyBinaries) sb.append(">\n"); for (int i = 1; i <= md.getColumnCount(); i++) { switch (md.getColumnType(i)) { case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: case java.sql.Types.BINARY: if (idColumn == null) throw new IllegalArgumentException("Id column required to process binaries!"); String binFile = FOLDER_BINARY + "/BIN_" + String.valueOf(rs.getLong(idColumn)) + "_" + i + ".blob"; att = md.getColumnName(i).toLowerCase(); if (onlyBinaries) { if (!(out instanceof ZipOutputStream)) throw new IllegalArgumentException( "out has to be a ZipOutputStream to store binaries!"); ZipOutputStream zip = (ZipOutputStream) out; InputStream in = rs.getBinaryStream(i); if (rs.wasNull()) break; ZipEntry ze = new ZipEntry(binFile); zip.putNextEntry(ze); byte[] buffer = new byte[4096]; int read; while ((read = in.read(buffer)) != -1) zip.write(buffer, 0, read); in.close(); zip.closeEntry(); zip.flush(); } else { InputStream in = rs.getBinaryStream(i); //need to fetch to see if it is empty if (rs.wasNull()) break; in.close(); sb.append(" <").append(att).append(">").append(binFile).append("</").append(att) .append(">\n"); } break; case java.sql.Types.CLOB: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: if (!onlyBinaries) { value = rs.getString(i); if (rs.wasNull()) break; att = md.getColumnName(i).toLowerCase(); sb.append(" <").append(att).append('>'); escape(sb, value); sb.append("</").append(att).append(">\n"); } break; } } if (!onlyBinaries) sb.append(" </").append(xmlTag).append(">\n"); } else { if (!onlyBinaries) sb.append("/>\n"); } if (!onlyBinaries) write(out, sb); } }
From source file:jeeves.resources.dbms.Dbms.java
private Element buildElement(ResultSet rs, int col, String name, int type, Hashtable<String, String> formats) throws SQLException { String value = null;//from w ww . j a va 2 s . c o m switch (type) { case Types.DATE: Date date = rs.getDate(col + 1); if (date == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_DATE_FORMAT) : new SimpleDateFormat(format); value = df.format(date); } break; case Types.TIME: Time time = rs.getTime(col + 1); if (time == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_TIME_FORMAT) : new SimpleDateFormat(format); value = df.format(time); } break; case Types.TIMESTAMP: Timestamp timestamp = rs.getTimestamp(col + 1); if (timestamp == null) value = null; else { String format = formats.get(name); SimpleDateFormat df = (format == null) ? new SimpleDateFormat(DEFAULT_TIMESTAMP_FORMAT) : new SimpleDateFormat(format); value = df.format(timestamp); } break; case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: long l = rs.getLong(col + 1); if (rs.wasNull()) value = null; else { String format = formats.get(name); if (format == null) value = l + ""; else { DecimalFormat df = new DecimalFormat(format); value = df.format(l); } } break; case Types.DECIMAL: case Types.FLOAT: case Types.DOUBLE: case Types.REAL: case Types.NUMERIC: double n = rs.getDouble(col + 1); if (rs.wasNull()) value = null; else { String format = formats.get(name); if (format == null) { value = n + ""; // --- this fix is mandatory for oracle // --- that shit returns integers like xxx.0 if (value.endsWith(".0")) value = value.substring(0, value.length() - 2); } else { DecimalFormat df = new DecimalFormat(format); value = df.format(n); } } break; default: value = rs.getString(col + 1); if (value != null) { value = stripIllegalChars(value); } break; } return new Element(name).setText(value); }
From source file:com.rapid.actions.Database.java
public JSONObject doQuery(RapidRequest rapidRequest, JSONObject jsonAction, Application application, DataFactory df) throws Exception { // place holder for the object we're going to return JSONObject jsonData = null;//from ww w . j a va 2 s . c om // retrieve the sql String sql = _query.getSQL(); // only if there is some sql is it worth going further if (sql != null) { // get any json inputs JSONObject jsonInputData = jsonAction.optJSONObject("data"); // initialise the parameters list ArrayList<Parameters> parametersList = new ArrayList<Parameters>(); // populate the parameters from the inputs collection (we do this first as we use them as the cache key due to getting values from the session) if (_query.getInputs() == null) { // just add an empty parameters member if no inputs parametersList.add(new Parameters()); } else { // if there is input data if (jsonInputData != null) { // get any input fields JSONArray jsonFields = jsonInputData.optJSONArray("fields"); // get any input rows JSONArray jsonRows = jsonInputData.optJSONArray("rows"); // if we have fields and rows if (jsonFields != null && jsonRows != null) { // loop the input rows (only the top row if not multirow) for (int i = 0; i < jsonRows.length() && (_query.getMultiRow() || i == 0); i++) { // get this jsonRow JSONArray jsonRow = jsonRows.getJSONArray(i); // make the parameters for this row Parameters parameters = new Parameters(); // loop the query inputs for (Parameter input : _query.getInputs()) { // get the input id String id = input.getItemId(); // get the input field String field = input.getField(); // add field to id if present if (field != null && !"".equals(field)) id += "." + field; // retain the value String value = null; // if it looks like a control, or a system value (bit of extra safety checking) if ("P".equals(id.substring(0, 1)) && id.indexOf("_C") > 0 || id.indexOf("System.") == 0) { // loop the json inputs looking for the value if (jsonInputData != null) { for (int j = 0; j < jsonFields.length(); j++) { // get the id from the fields String jsonId = jsonFields.optString(j); // if the id we want matches this one if (id.toLowerCase().equals(jsonId.toLowerCase())) { // get the value value = jsonRow.optString(j, null); // no need to keep looking break; } } } } // if still null try the session if (value == null) value = (String) rapidRequest.getSessionAttribute(input.getItemId()); // add the parameter parameters.add(value); } // add the parameters to the list parametersList.add(parameters); } // row loop } // input fields and rows check } // input data check } // query inputs check // placeholder for the action cache ActionCache actionCache = rapidRequest.getRapidServlet().getActionCache(); // if an action cache was found if (actionCache != null) { // log that we found action cache _logger.debug("Database action cache found"); // attempt to fetch data from the cache jsonData = actionCache.get(application.getId(), getId(), parametersList.toString()); } // if there isn't a cache or no data was retrieved if (jsonData == null) { try { // instantiate jsonData jsonData = new JSONObject(); // fields collection JSONArray jsonFields = new JSONArray(); // rows collection can start initialised JSONArray jsonRows = new JSONArray(); // trim the sql sql = sql.trim(); // check the verb if (sql.toLowerCase().startsWith("select") || sql.toLowerCase().startsWith("with")) { // set readonly to true (makes for faster querying) df.setReadOnly(true); // loop the parameterList getting a result set for each parameters (input row) for (Parameters parameters : parametersList) { // get the result set! ResultSet rs = df.getPreparedResultSet(rapidRequest, sql, parameters); // get it's meta data for the field names ResultSetMetaData rsmd = rs.getMetaData(); // got fields indicator boolean gotFields = false; // loop the result set while (rs.next()) { // initialise the row JSONArray jsonRow = new JSONArray(); // loop the columns for (int i = 0; i < rsmd.getColumnCount(); i++) { // add the field name to the fields collection if not done yet if (!gotFields) jsonFields.put(rsmd.getColumnLabel(i + 1)); // get the column type int columnType = rsmd.getColumnType(i + 1); // add the data to the row according to it's type switch (columnType) { case (Types.NUMERIC): jsonRow.put(rs.getDouble(i + 1)); break; case (Types.INTEGER): jsonRow.put(rs.getInt(i + 1)); break; case (Types.BIGINT): jsonRow.put(rs.getLong(i + 1)); break; case (Types.FLOAT): jsonRow.put(rs.getFloat(i + 1)); break; case (Types.DOUBLE): jsonRow.put(rs.getDouble(i + 1)); break; default: jsonRow.put(rs.getString(i + 1)); } } // add the row to the rows collection jsonRows.put(jsonRow); // remember we now have our fields gotFields = true; } // close the record set rs.close(); } } else { // assume rows affected is 0 int rows = 0; // sql check if (sql.length() > 0) { // perform update for all incoming parameters (one parameters collection for each row) for (Parameters parameters : parametersList) { rows += df.getPreparedUpdate(rapidRequest, sql, parameters); } // add a psuedo field jsonFields.put("rows"); // create a row array JSONArray jsonRow = new JSONArray(); // add the rows updated jsonRow.put(rows); // add the row we just made jsonRows.put(jsonRow); } } // add the fields to the data object jsonData.put("fields", jsonFields); // add the rows to the data object jsonData.put("rows", jsonRows); // check for any child database actions if (_childDatabaseActions != null) { // if there really are some if (_childDatabaseActions.size() > 0) { // get any child data JSONArray jsonChildQueries = jsonAction.optJSONArray("childQueries"); // if there was some if (jsonChildQueries != null) { // loop for (int i = 0; i < jsonChildQueries.length(); i++) { // fetch the data JSONObject jsonChildAction = jsonChildQueries.getJSONObject(i); // read the index (the position of the child this related to int index = jsonChildAction.getInt("index"); // get the relevant child action Database childDatabaseAction = _childDatabaseActions.get(index); // get the resultant child data JSONObject jsonChildData = childDatabaseAction.doQuery(rapidRequest, jsonChildAction, application, df); // a map for indexes of matching fields between our parent and child Map<Integer, Integer> fieldsMap = new HashMap<Integer, Integer>(); // the child fields JSONArray jsonChildFields = jsonChildData.getJSONArray("fields"); if (jsonChildFields != null) { // loop the parent fields for (int j = 0; j < jsonFields.length(); j++) { // loop the child fields for (int k = 0; k < jsonChildFields.length(); k++) { // get parent field String field = jsonFields.getString(j); // get child field String childField = jsonChildFields.getString(k); // if both not null if (field != null && childField != null) { // check for match if (field.toLowerCase().equals(childField.toLowerCase())) fieldsMap.put(j, k); } } } } // add a field for the results of this child action jsonFields.put("childAction" + (i + 1)); // if matching fields if (fieldsMap.size() > 0) { // an object with a null value for when there is no match Object nullObject = null; // get the child rows JSONArray jsonChildRows = jsonChildData.getJSONArray("rows"); // if we had some if (jsonChildRows != null) { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the parent row JSONArray jsonRow = jsonRows.getJSONArray(j); // make a new rows collection for the child subset JSONArray jsonChildRowsSubset = new JSONArray(); // loop the child rows for (int k = 0; k < jsonChildRows.length(); k++) { // get the child row JSONArray jsonChildRow = jsonChildRows.getJSONArray(k); // assume no matches int matches = 0; // loop the fields map for (Integer l : fieldsMap.keySet()) { // parent value Object parentValue = null; // get the value if there are enough if (jsonRow.length() > l) parentValue = jsonRow.get(l); // child value Object childValue = null; if (jsonChildRow.length() > l) childValue = jsonChildRow.get(fieldsMap.get(l)); // non null check if (parentValue != null && childValue != null) { // a string we will concert the child value to String parentString = null; // check the parent value type if (parentValue.getClass() == String.class) { parentString = (String) parentValue; } else if (parentValue.getClass() == Integer.class) { parentString = Integer .toString((Integer) parentValue); } else if (parentValue.getClass() == Long.class) { parentString = Long.toString((Long) parentValue); } else if (parentValue.getClass() == Double.class) { parentString = Double .toString((Double) parentValue); } else if (parentValue.getClass() == Boolean.class) { parentString = Boolean .toString((Boolean) parentValue); } // a string we will convert the child value to String childString = null; // check the parent value type if (childValue.getClass() == String.class) { childString = (String) childValue; } else if (childValue.getClass() == Integer.class) { childString = Integer .toString((Integer) childValue); } else if (childValue.getClass() == Long.class) { childString = Long.toString((Long) childValue); } else if (childValue.getClass() == Double.class) { childString = Double.toString((Double) childValue); } else if (childValue.getClass() == Boolean.class) { childString = Boolean .toString((Boolean) childValue); } // non null check if (parentString != null && childString != null) { // do the match! if (parentString.equals(childString)) matches++; } } // values non null } // field map loop // if we got some matches for all the fields add this row to the subset if (matches == fieldsMap.size()) jsonChildRowsSubset.put(jsonChildRow); } // child row loop // if our child subset has rows in it if (jsonChildRowsSubset.length() > 0) { // create a new childSubset object JSONObject jsonChildDataSubset = new JSONObject(); // add the fields jsonChildDataSubset.put("fields", jsonChildFields); // add the subset of rows jsonChildDataSubset.put("rows", jsonChildRowsSubset); // add the child database action data subset jsonRow.put(jsonChildDataSubset); } else { // add an empty cell jsonRow.put(nullObject); } } // parent row loop } // jsonChildRows null check } else { // loop the parent rows for (int j = 0; j < jsonRows.length(); j++) { // get the row JSONArray jsonRow = jsonRows.getJSONArray(j); // add the child database action data jsonRow.put(jsonChildData); } } // matching fields check } // jsonChildQueries loop } // jsonChildQueries null check } // _childDatabaseActions size > 0 } // _childDatabaseActions not null // cache if in use if (actionCache != null) actionCache.put(application.getId(), getId(), parametersList.toString(), jsonData); } catch (Exception ex) { // log the error _logger.error(ex); // close the data factory and silently fail try { df.close(); } catch (Exception ex2) { } // only throw if no action cache if (actionCache == null) { throw ex; } else { _logger.debug("Error not shown to user due to cache : " + ex.getMessage()); } } // jsonData not null } // jsonData == null } // got sql return jsonData; }
From source file:Report_PRCR_New_EPF_Excel_File_Generator.java
private void view1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_view1ActionPerformed try {//from w ww. j a v a 2 s . c o m DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "7", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO(new File(dbm.checknReturnData("file_locations", "id", "6", "location")), new File(employee_detail_file_location)); InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String epf_backup_year_month = year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0) / 100.0; double employer_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; double tot_contribution = 0; double employers_contribution = 0; double member_contribution = 0; double tot_earnings = 0; String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } ResultSet query = dbm .query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 AND `total_pay` > 0"); while (query.next()) { ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no) == 1) { tot_earnings = tot_earnings + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no, "total_pay")); } employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; tot_contribution = employers_contribution + member_contribution; total_member_contribution = total_member_contribution + tot_contribution; normal_days = query.getInt("normal_days"); sundays = query.getInt("sundays"); ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } Row row = sheet.getRow(4 + count); if (row == null) { row = sheet.createRow(4 + count); } for (int k = 0; k < 15; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employers_contribution); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; case 7: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_earnings); break; case 8: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(member_status); break; case 9: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 10: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 11: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 12: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 13: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(no_of_days_worked); break; case 14: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(occupation_classification_grade); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); if (need_both_reports == 1) { if (Integer.parseInt(dayfield.getText()) < 10) { payment_date_year_month_date = yearfield1.getText() + dt.return_month_as_num(monthfield1.getText()) + "0" + dayfield.getText(); } else { payment_date_year_month_date = yearfield1.getText() + dt.return_month_as_num(monthfield1.getText()) + dayfield.getText(); } payment_date = Integer.parseInt(payment_date_year_month_date); payment_mode = payment_mode_combo.getSelectedIndex() + 1; payment_reference = payment_referrence_textFiield.getText(); d_o_code = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); String total_contribution_file_location = dbm.checknReturnData("file_locations", "id", "9", "location") + "/" + year + month + "_total_contribution.xls"; copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "8", "location")), new File(total_contribution_file_location)); InputStream inp2 = new FileInputStream(total_contribution_file_location); Workbook wb2 = WorkbookFactory.create(inp2); org.apache.poi.ss.usermodel.Sheet sheet2 = wb2.getSheetAt(0); Row row = sheet2.getRow(17); if (row == null) { row = sheet.createRow(17); } for (int k = 0; k < 10; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 1: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 2: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(total_member_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(count); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(payment_mode); break; case 7: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(payment_reference); break; case 8: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(payment_date); break; case 9: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(d_o_code); break; default: break; } } FileOutputStream fileOut2 = new FileOutputStream(total_contribution_file_location); wb2.write(fileOut2); fileOut2.close(); Desktop.getDesktop().open(new File(total_contribution_file_location)); } } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override// www.j a v a 2 s .com
protected final void doGet(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
final ServletContext application = getServletContext();
final DataSource datasource = ApplicationAttributes.getDataSource(application);
Connection connection = null;
PreparedStatement prep = null;
ResultSet rs = null;
try {
connection = datasource.getConnection();
final int currentTournament = Queries.getCurrentTournament(connection);
// category->judge->teamNumber->score
final Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = new HashMap<String, Map<String, Map<Integer, SubjectiveScore>>>();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
for (final ScoreCategory sc : challengeDescription.getSubjectiveCategories()) {
// judge->teamNumber->score
final Map<String, Map<Integer, SubjectiveScore>> categoryScores = new HashMap<String, Map<Integer, SubjectiveScore>>();
prep = connection.prepareStatement("SELECT * FROM " + sc.getName() + " WHERE Tournament = ?");
prep.setInt(1, currentTournament);
rs = prep.executeQuery();
while (rs.next()) {
final SubjectiveScore score = new SubjectiveScore();
score.setScoreOnServer(true);
final String judge = rs.getString("Judge");
final Map<Integer, SubjectiveScore> judgeScores;
if (categoryScores.containsKey(judge)) {
judgeScores = categoryScores.get(judge);
} else {
judgeScores = new HashMap<Integer, SubjectiveScore>();
categoryScores.put(judge, judgeScores);
}
score.setTeamNumber(rs.getInt("TeamNumber"));
score.setJudge(judge);
score.setNoShow(rs.getBoolean("NoShow"));
score.setNote(rs.getString("note"));
final Map<String, Double> standardSubScores = new HashMap<String, Double>();
final Map<String, String> enumSubScores = new HashMap<String, String>();
for (final AbstractGoal goal : sc.getGoals()) {
if (goal.isEnumerated()) {
final String value = rs.getString(goal.getName());
enumSubScores.put(goal.getName(), value);
} else {
final double value = rs.getDouble(goal.getName());
standardSubScores.put(goal.getName(), value);
}
}
score.setStandardSubScores(standardSubScores);
score.setEnumSubScores(enumSubScores);
judgeScores.put(score.getTeamNumber(), score);
}
allScores.put(sc.getName(), categoryScores);
SQLFunctions.close(rs);
rs = null;
SQLFunctions.close(prep);
prep = null;
}
final ObjectMapper jsonMapper = new ObjectMapper();
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, allScores);
} catch (final SQLException e) {
throw new RuntimeException(e);
} finally {
SQLFunctions.close(rs);
SQLFunctions.close(prep);
SQLFunctions.close(connection);
}
}
From source file:com.l2jfree.gameserver.datatables.CharTemplateTable.java
private CharTemplateTable() { Connection con = null;//from w w w.j ava 2s . co m try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement = con.prepareStatement("SELECT * FROM class_list, char_templates, lvlupgain" + " WHERE class_list.id = char_templates.classId" + " AND class_list.id = lvlupgain.classId" + " ORDER BY class_list.id"); ResultSet rset = statement.executeQuery(); int size = 0; while (rset.next()) { StatsSet set = new StatsSet(); set.set("classId", rset.getInt("id")); set.set("className", rset.getString("className")); set.set("raceId", rset.getInt("raceId")); set.set("baseSTR", rset.getInt("STR")); set.set("baseCON", rset.getInt("CON")); set.set("baseDEX", rset.getInt("DEX")); set.set("baseINT", rset.getInt("_INT")); set.set("baseWIT", rset.getInt("WIT")); set.set("baseMEN", rset.getInt("MEN")); set.set("baseHpMax", rset.getFloat("defaultHpBase")); set.set("lvlHpAdd", rset.getFloat("defaultHpAdd")); set.set("lvlHpMod", rset.getFloat("defaultHpMod")); set.set("baseMpMax", rset.getFloat("defaultMpBase")); set.set("baseCpMax", rset.getFloat("defaultCpBase")); set.set("lvlCpAdd", rset.getFloat("defaultCpAdd")); set.set("lvlCpMod", rset.getFloat("defaultCpMod")); set.set("lvlMpAdd", rset.getFloat("defaultMpAdd")); set.set("lvlMpMod", rset.getFloat("defaultMpMod")); set.set("baseHpReg", 1.5); set.set("baseMpReg", 0.9); set.set("basePAtk", rset.getInt("p_atk")); set.set("basePDef", /*classId.isMage()? 77 : 129*/rset.getInt("p_def")); set.set("baseMAtk", rset.getInt("m_atk")); set.set("baseMDef", rset.getInt("char_templates.m_def")); set.set("classBaseLevel", rset.getInt("class_lvl")); set.set("basePAtkSpd", rset.getInt("p_spd")); set.set("baseMAtkSpd", /*classId.isMage()? 166 : 333*/rset.getInt("char_templates.m_spd")); set.set("baseCritRate", rset.getInt("char_templates.critical") / 10); set.set("baseRunSpd", rset.getInt("move_spd") * Config.RATE_RUN_SPEED); set.set("baseWalkSpd", 0); set.set("baseShldDef", 0); set.set("baseShldRate", 0); set.set("baseAtkRange", 40); /* Not a single point set.set("spawnX", rset.getInt("x")); set.set("spawnY", rset.getInt("y")); set.set("spawnZ", rset.getInt("z")); */ L2PlayerTemplate ct; set.set("collision_radius", rset.getDouble("m_col_r")); set.set("collision_height", rset.getDouble("m_col_h")); // Add-on for females set.set("fcollision_radius", rset.getDouble("f_col_r")); set.set("fcollision_height", rset.getDouble("f_col_h")); ct = new L2PlayerTemplate(set); _templates[ct.getClassId().getId()] = ct; size++; } rset.close(); statement.close(); _log.info("CharTemplateTable: Loaded " + size + " Character Templates."); } catch (SQLException e) { _log.fatal("Failed loading char templates", e); } try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement = con .prepareStatement("SELECT classId, itemId, amount, equipped FROM char_creation_items"); ResultSet rset = statement.executeQuery(); int classId, itemId, amount; boolean equipped; while (rset.next()) { classId = rset.getInt("classId"); itemId = rset.getInt("itemId"); amount = rset.getInt("amount"); equipped = rset.getString("equipped").equals("true"); if (ItemTable.getInstance().getTemplate(itemId) != null) { if (classId == -1) { for (L2PlayerTemplate pct : _templates) { if (pct == null) continue; pct.addItem(itemId, amount, equipped); } } else { L2PlayerTemplate pct = _templates[classId]; if (pct != null) { pct.addItem(itemId, amount, equipped); } else { _log.warn("char_creation_items: Entry for undefined class, classId: " + classId); } } } else { _log.warn("char_creation_items: No data for itemId: " + itemId + " defined for classId " + classId); } } rset.close(); statement.close(); } catch (SQLException e) { _log.fatal("Failed loading char creation items.", e); } finally { L2DatabaseFactory.close(con); } }
From source file:database.DataLoader.java
private void moveOrders() throws SQLException, ClassNotFoundException, Exception { // /* w ww .j a v a2 s . c o m*/ final String tableName = ORDER; final ResultSet orderSet = getFromOldBase(getSelectAll(tableName, "id")); while (orderSet.next()) { // ? TransactionTemplate temp = new TransactionTemplate(transactionManager); temp.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus ts) { Long oldId = 0L; try { oldId = orderSet.getLong("id"); String subject = orderSet.getString("theme"); if (subject.length() > 250) { subject = subject.substring(0, 250); } Long oldBranchId = orderSet.getLong("branch_id"); Long oldOrderTypeId = orderSet.getLong("type_id"); String city = orderSet.getString("university"); Double price = orderSet.getDouble("price"); Double authorSalary = orderSet.getDouble("reward"); Date redline = orderSet.getDate("redline"); Date deadline = orderSet.getDate("deadline"); Long oldAuthorId = orderSet.getLong("author_id"); Long clientId = orderSet.getLong("client_id"); String rewardComment = orderSet.getString("reward_comment"); Boolean flag_0 = orderSet.getBoolean("flag_0"); Boolean flag_1 = orderSet.getBoolean("flag_1"); Timestamp timestamp = orderSet.getTimestamp("ready_date"); String readyComment = orderSet.getString("ready_comment"); String comment = orderSet.getString("description"); Date readyDate = null; if (timestamp != null) { readyDate = new Date(timestamp.getTime()); } Long newBranchId = getNewId(oldBranchId, BRANCH); Branch branch = branchDao.find(newBranchId); Long newAuthorId = getNewId(oldAuthorId, USERS); Author author = authorDao.find(newAuthorId); Long newOrderTypeId = getNewId(oldOrderTypeId, TYPE); OrderType orderType = orderTypeDao.find(newOrderTypeId); Integer statusInt = orderSet.getInt("status"); OrderStatus status = getStatus(statusInt); if (status == null) { status = OrderStatus.NEW; } Date orderDate = getDateOrToday(orderSet, "created"); Order order = new Order(); order.setStatus(status); order.setOrderDate(orderDate); order.setAuthor(author); order.setBranch(branch); order.setOrderType(orderType); order.setSubject(subject); order.setCity(city); order.setCost(price); order.setAuthor_salary(authorSalary); // ? ? , ? order.setRealDate(deadline); order.setDeadlineDate(redline); order.setAuthorComment(readyComment); order.setComment(comment); order.setReadyDate(readyDate); order.setFirstFlag(flag_0); order.setSecondFlag(flag_1); order.setCommentToAuthorSalary(rewardComment); order.setOldId(StringAdapter.getString(oldId)); ResultSet clientSet = getFromOldBase("select * from " + CLIENT + " where id = " + clientId); String clientEmail = null; String clientFio = null; String clientPhone = null; while (clientSet.next()) { clientEmail = trim(clientSet.getString("email")); clientFio = clientSet.getString("firstname") + " " + clientSet.getString("fathername") + " " + clientSet.getString("lastname"); clientPhone = clientSet.getString("phone"); } if (ValidatorUtils.isEmail(clientEmail)) { order.setClientEmail(clientEmail); } order.setClientFio(clientFio); order.setClientPhone(clientPhone); saveObjectAndLink(order, oldId, tableName); } catch (Throwable e) { ts.setRollbackOnly(); addErrorMessage("order: " + oldId + " " + StringAdapter.getStackExeption(e)); log.warn("order: " + oldId + " " + StringAdapter.getStackExeption(e)); } } }); } }
From source file:com.sfs.whichdoctor.dao.PaymentDAOImpl.java
/** * Load payment./* w w w . ja v a2 s .c om*/ * * @param rs the rs * * @return the payment bean * * @throws SQLException the SQL exception */ private PaymentBean loadPayment(final ResultSet rs) throws SQLException { PaymentBean payment = new PaymentBean(); payment.setId(rs.getInt("PaymentId")); payment.setGUID(rs.getInt("GUID")); payment.setReferenceGUID(rs.getInt("ReferenceGUID")); if (rs.getInt("PersonId") != 0) { PersonBean person = new PersonBean(); person.setGUID(rs.getInt("PersonId")); person.setPersonIdentifier(rs.getInt("PersonIdentifier")); person.setPreferredName(rs.getString("PreferredName")); person.setFirstName(rs.getString("FirstName")); person.setLastName(rs.getString("LastName")); person.setTitle(rs.getString("Title")); payment.setPerson(person); } if (rs.getInt("OrganisationId") > 0) { OrganisationBean organisation = new OrganisationBean(); organisation.setGUID(rs.getInt("OrganisationId")); organisation.setName(rs.getString("OrganisationName")); payment.setOrganisation(organisation); } if (rs.getInt("IncomeStreamId") > 0) { /* Payment is a negative payment */ payment.setNegativePayment(true); /* Create a 'positive' value */ payment.setIncomeStream(rs.getString("IncomeStream")); payment.setIncomeStreamId(rs.getInt("IncomeStreamId")); } else { payment.setNegativePayment(false); /* See if 'positive' payment has attributed invoice */ if (rs.getInt("InvoiceId") > 0) { DebitBean debit = new DebitBean(); debit.setGUID(rs.getInt("InvoiceId")); debit.setDescription(rs.getString("Description")); debit.setTypeName(rs.getString("DebitType")); debit.setNumber(rs.getString("InvoiceNo")); debit.setAbbreviation(rs.getString("Abbreviation")); try { debit.setIssued(rs.getDate("Issued")); } catch (Exception sqe) { dataLogger.debug("Error parsing Issued value: " + sqe.getMessage()); } debit.setGSTRate(rs.getDouble("InvoiceGSTRate")); debit.setCancelled(rs.getBoolean("InvoiceCancelled")); debit.setValue(rs.getDouble("InvoiceValue")); debit.setCreditValue(rs.getDouble("InvoiceCreditValue")); debit.setNetValue(rs.getDouble("InvoiceNetValue")); payment.setDebit(debit); } } payment.setValue(rs.getDouble("Value")); payment.setNetValue(rs.getDouble("NetValue")); payment.setGSTRate(rs.getDouble("GSTRate")); payment.setActive(rs.getBoolean("Active")); try { payment.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage()); } payment.setCreatedBy(rs.getString("CreatedBy")); try { payment.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage()); } payment.setModifiedBy(rs.getString("ModifiedBy")); try { payment.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage()); } payment.setExportedBy(rs.getString("ExportedBy")); return payment; }
From source file:com.wso2telco.core.dbutils.DbService.java
public SpendLimitDAO getGroupTotalMonthAmount(String groupName, String operator, String msisdn) throws DBUtilException { Connection con = null;/*from w ww. j av a 2 s. c o m*/ PreparedStatement ps = null; ResultSet rs = null; SpendLimitDAO spendLimitDAO = null; try { con = DbUtils.getDBConnection(); Calendar calendarFrom = GregorianCalendar.getInstance(); calendarFrom.set(Calendar.DAY_OF_MONTH, calendarFrom.getActualMinimum(Calendar.DAY_OF_MONTH)); calendarFrom.set(Calendar.HOUR_OF_DAY, 0); calendarFrom.set(Calendar.MINUTE, 0); calendarFrom.set(Calendar.SECOND, 0); calendarFrom.set(Calendar.MILLISECOND, 0); Calendar calendarTo = GregorianCalendar.getInstance(); calendarTo.setTime(new Date()); calendarTo.set(Calendar.DAY_OF_MONTH, calendarTo.getActualMaximum(Calendar.DAY_OF_MONTH)); calendarTo.set(Calendar.HOUR_OF_DAY, 23); calendarTo.set(Calendar.MINUTE, 59); calendarTo.set(Calendar.SECOND, 59); calendarTo.set(Calendar.MILLISECOND, 999); String sql = "SELECT SUM(amount) AS amount " + "FROM spendlimitdata " + "where effectiveTime between ? and ? " + "and groupName=? " + "and operatorId=? " + "and msisdn=? " + "group by groupName, operatorId, msisdn"; ps = con.prepareStatement(sql); ps.setLong(1, calendarFrom.getTimeInMillis()); ps.setLong(2, calendarTo.getTimeInMillis()); ps.setString(3, groupName); ps.setString(4, operator); ps.setString(5, msisdn); rs = ps.executeQuery(); rs = ps.executeQuery(); if (rs.next()) { spendLimitDAO = new SpendLimitDAO(); spendLimitDAO.setAmount(rs.getDouble("amount")); } } catch (Exception e) { DbUtils.handleException("Error while checking Operator Spend Limit. ", e); } finally { DbUtils.closeAllConnections(ps, con, rs); } return spendLimitDAO; }