Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package agileinterop; import com.agile.api.*; import com.dexcom.agile.Agile; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.*; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.json.simple.parser.ParseException; /** * * @author mburny */ public class AgileCrawler { private final Agile agile; private final List<String> psrNumbers; private List<String> logMessages; private Map<String, Map<String, Object>> psrsData; private int currentProgress; Connection connection; JSONObject map; public AgileCrawler(Agile agile, List<String> psrNumbers, File database, File map) throws SQLException, IOException, ParseException { this.agile = agile; this.psrNumbers = psrNumbers; currentProgress = 0; connection = DriverManager.getConnection("jdbc:sqlite:" + database.getAbsolutePath()); connection.setAutoCommit(false); this.map = (JSONObject) (new JSONParser()).parse(new FileReader(map)); } public List<String> getPsrNumbers() { return psrNumbers; } public List<String> getLogMessages() { return logMessages; } public void setCurrentProgress(int currentProgress) { this.currentProgress = currentProgress; } public double getCurrentProgressPercentage() { return (double) currentProgress / psrNumbers.size(); } public Map<String, Map<String, Object>> getPsrsData() { return psrsData; } class GetPSRDataThread implements Runnable { private final String psrNumber; private final IServiceRequest psr; private Map<String, Object> psrData; private Statement stmt; public GetPSRDataThread(String psrNumber) throws APIException, InterruptedException, SQLException { this.psrNumber = psrNumber; psr = (IServiceRequest) Agile.session.getObject(IServiceRequest.OBJECT_TYPE, psrNumber); stmt = connection.createStatement(); } @Override public void run() { psrData.put(psrNumber, new HashMap<String, Object>()); try { if (psr != null) { downloadCellValues(); downloadAttachments(); downloadHistory(); updateDatabase(); } } catch (APIException | SQLException ex) { String logMessage = String.format("%s: An error occurred (%s)", psrNumber, ex.getMessage()); System.out.println(logMessage); } } private String downloadPSRType() throws APIException { return psr.getCell(4857).toString(); } private void downloadCellValues() throws APIException { Map<String, String> cellValues = new HashMap<>(); long startTime = System.currentTimeMillis(); // Get cell values ICell[] cells = psr.getCells(); for (ICell cell : cells) { String agileCellName = cell.getName(); JSONObject psr_attributes_maps = (JSONObject) map.get("psr_attributes_maps"); JSONObject psrTypeAttributesMap = (JSONObject) psr_attributes_maps.get(downloadPSRType()); String databaseCellName = psrTypeAttributesMap.get(agileCellName).toString(); if (cell.getDataType() == DataTypeConstants.TYPE_DATE) { if (cell.getValue() != null) { SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a zz"); sdf.setTimeZone(TimeZone.getTimeZone("Europe/London")); cellValues.put(databaseCellName, sdf.format((Date) cell.getValue())); } else { cellValues.put(databaseCellName, cell.toString()); } } else { cellValues.put(databaseCellName, cell.toString()); } } long endTime = System.currentTimeMillis(); String logMessage = String.format("%s: getCellValues executed in %d milliseconds", psrNumber, endTime - startTime); System.out.println(logMessage); psrData.put("cellValues", cellValues); } private void downloadAttachments() throws APIException { List<Map<String, String>> attachments = new ArrayList<>(); long startTime = System.currentTimeMillis(); // Get attachments information ITable table = psr.getTable("Attachments"); ITwoWayIterator tableIterator = table.getTableIterator(); while (tableIterator.hasNext()) { IRow row = (IRow) tableIterator.next(); Map<String, String> attachment = new HashMap<>(); ICell[] cells = row.getCells(); for (ICell cell : cells) { String agileCellName = cell.getName(); String databaseCellName = ((JSONObject) map.get("attachments_attributes_map")) .get(agileCellName).toString(); if (cell.getDataType() == DataTypeConstants.TYPE_DATE) { if (cell.getValue() != null) { SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a zz"); sdf.setTimeZone(TimeZone.getTimeZone("Europe/London")); attachment.put(databaseCellName, sdf.format((Date) cell.getValue())); } else { attachment.put(databaseCellName, cell.toString()); } } else { attachment.put(databaseCellName, cell.toString()); } } attachments.add(attachment); } long endTime = System.currentTimeMillis(); String logMessage = String.format("%s: getAttachments executed in %d milliseconds", psrNumber, endTime - startTime); System.out.println(logMessage); psrData.put("attachments", attachments); } private void downloadHistory() throws APIException { List<Map<String, String>> histories = new ArrayList<>(); long startTime = System.currentTimeMillis(); // Get history information ITable table = psr.getTable("History"); ITwoWayIterator tableIterator = table.getTableIterator(); while (tableIterator.hasNext()) { IRow row = (IRow) tableIterator.next(); Map<String, String> history = new HashMap<>(); ICell[] cells = row.getCells(); for (ICell cell : cells) { String agileCellName = cell.getName(); String databaseCellName = ((JSONObject) map.get("history_attributes_map")).get(agileCellName) .toString(); if (cell.getDataType() == DataTypeConstants.TYPE_DATE) { if (cell.getValue() != null) { SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a zz"); sdf.setTimeZone(TimeZone.getTimeZone("Europe/London")); history.put(databaseCellName, sdf.format((Date) cell.getValue())); } else { history.put(databaseCellName, cell.toString()); } } else { history.put(databaseCellName, cell.toString()); } } histories.add(history); } long endTime = System.currentTimeMillis(); String logMessage = String.format("%s: getHistory executed in %d milliseconds", psrNumber, endTime - startTime); System.out.println(logMessage); psrData.put("history", histories); } private synchronized void updateDatabase() throws APIException, SQLException { Long startTime = System.currentTimeMillis(); deleteExistingDataFromDatabase(); insertNewDataIntoDatabase(); stmt.executeBatch(); connection.commit(); Long endTime = System.currentTimeMillis(); String logMessage = String.format("%s: updateDatabase executed in %d milliseconds", psrNumber, endTime - startTime); System.out.println(logMessage); } private void deleteExistingDataFromDatabase() throws SQLException { stmt.addBatch(String.format("DELETE FROM problem_reports WHERE psr_number = %s;", psrNumber)); stmt.addBatch(String.format("DELETE FROM decision_trees WHERE psr_number = %s;", psrNumber)); stmt.addBatch(String.format("DELETE FROM investigations WHERE psr_number = %s;", psrNumber)); stmt.addBatch(String.format("DELETE FROM attachments WHERE psr_number = %s;", psrNumber)); stmt.addBatch(String.format("DELETE FROM history WHERE psr_number = %s;", psrNumber)); } private void insertNewDataIntoDatabase() throws APIException, SQLException { stmt.addBatch(generateInsertTableSQL("problem_reports")); stmt.addBatch(generateInsertTableSQL("attachments")); stmt.addBatch(generateInsertTableSQL("history")); } /** * Gets the name of the database table associated with this PSR Type. * e.g. PSR Type "MDR Decision Tree" is associated with "decision_trees" * PSR Type "Customer Complaint" is associated with "problem_reports" */ private String getPSRTableName() throws APIException { return ((JSONObject) map.get("psr_tables")).get(downloadPSRType()).toString(); } private String generateInsertTableSQL(String tableName) { JSONArray attributeNames = (JSONArray) map.get(tableName); List<String> attributeValues = new ArrayList<String>(); // Build attributeValues Map<String, Object> attributeContainer; switch (tableName) { case "problem_reports": attributeContainer = (Map<String, Object>) psrData.get("cellValues"); break; case "attachments": attributeContainer = (Map<String, Object>) psrData.get("attachments"); break; case "history": attributeContainer = (Map<String, Object>) psrData.get("history"); break; default: throw new AssertionError(); } for (Object attributeName : attributeNames) { attributeValues.add((String) attributeContainer.get(attributeName)); } // Join attributeNames StringBuilder attributeNamesStringBuilder = new StringBuilder(); Iterator attributeNamesIterator = attributeNames.iterator(); while (attributeNamesIterator.hasNext()) { String attributeName = (String) attributeNamesIterator.next(); attributeNamesStringBuilder.append(attributeName); if (attributeNamesIterator.hasNext()) { attributeNamesStringBuilder.append(", "); } } // Join attributeValues StringBuilder attributeValuesStringBuilder = new StringBuilder(); Iterator attributeValuesIterator = attributeValues.iterator(); while (attributeValuesIterator.hasNext()) { String attributeValue = (String) attributeValuesIterator.next(); attributeValuesStringBuilder.append(attributeValue); if (attributeValuesIterator.hasNext()) { attributeValuesStringBuilder.append(", "); } } // Return the INSERT SQL statement return String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, attributeNamesStringBuilder.toString(), attributeValuesStringBuilder.toString()); } } }