Skip to content

Instantly share code, notes, and snippets.

@johnlpage
Created March 28, 2023 07:50
Show Gist options
  • Save johnlpage/76ed945e565a10258d02a29460e9e916 to your computer and use it in GitHub Desktop.
Save johnlpage/76ed945e565a10258d02a29460e9e916 to your computer and use it in GitHub Desktop.
//Query From https://data.dft.gov.uk/anonymised-mot-test/MOT_user_guide_v4.docx
private final String getlatestByVehicleSQL = "select " +
"tr.*, " +
"ft.FUEL_TYPE, " +
"tt.TESTTYPE AS TYPENAME, " +
"to2.RESULT, " +
"ti.*, " +
"fl.*, " + "tid.MINORITEM,tid.RFRDESC,tid.RFRLOCMARKER,tid.RFRINSPMANDESC,tid.RFRADVISORYTEXT,tid.TSTITMSETSECID, " +
"b.ITEMNAME AS LEVEL1, " +
"c.ITEMNAME AS LEVEL2, " +
"d.ITEMNAME AS LEVEL3, " +
"e.ITEMNAME AS LEVEL4, " +
"f.ITEMNAME AS LEVEL5 " +
"from TESTRESULT tr " +
"LEFT JOIN TESTITEM ti on ti.TESTID = tr.TESTID " +
"LEFT JOIN FUEL_TYPES ft on ft.TYPECODE = tr.FUELTYPE " +
"LEFT JOIN TEST_TYPES tt on tt.TYPECODE = tr.TESTTYPE " +
"LEFT JOIN TEST_OUTCOME to2 on to2.RESULTCODE = tr.TESTRESULT " +
"LEFT JOIN FAILURE_LOCATION fl on ti.LOCATIONID = fl.FAILURELOCATIONID " +
"LEFT JOIN TESTITEM_DETAIL AS tid ON ti.RFRID = tid.RFRID AND tid.TESTCLASSID = tr.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS b ON tid.TSTITMID = b.TSTITMID AND tid.TESTCLASSID = b.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS c ON b.PARENTID = c.TSTITMID AND b.TESTCLASSID = c.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS d ON c.PARENTID = d.TSTITMID AND c.TESTCLASSID = d.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS e ON d.PARENTID = e.TSTITMID AND d.TESTCLASSID = e.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS f ON e.PARENTID = f.TSTITMID AND e.TESTCLASSID = f.TESTCLASSID " +
"WHERE tr.TESTID = (SELECT TESTID FROM TESTRESULT WHERE VEHICLEID=? LIMIT 1)";
public String getMOTResultInJSON(String identifier) {
long identifierLong;
jsonObj = new JSONObject();
// Check we aren't a new thread - if we are we need a new conneciton.
try {
identifierLong = Long.valueOf(identifier);
//Pick a prepared statement from out list of readers randomly
PreparedStatement getTestStmt = readConnections.get(ThreadLocalRandom.current().nextInt(0, readConnections.size()));
getTestStmt.setLong(1, identifierLong);
ResultSet testResult = getTestStmt.executeQuery();
ResultSetMetaData metaData = testResult.getMetaData();
// Create JSON from a set of Rows
String[] topFieldNames = { "TESTID", "VEHICLEID", "TESTTYPE", "TESTRESULT", "TESTDATE", "TESTCLASSID", "TYPENAME","TESTMILEAGE", "POSTCODEREGION", "MAKE", "MODEL", "COLOUR", "FUELTYPE", "FUEL_TYPE", "CYLCPCTY","FIRSTUSEDATE","RESULT" };
String[] itemFieldNames = { "RFRID", "RFRTYPE", "DMARK", "LOCATIONID", "LAT", "LONGITUDINAL", "VERTICAL","MINORITEM", "RFRDESC","RFRLOCMARKER", "RFRINSPMANDESC", "RFRADVISORYTEXT", "LEVEL1", "LEVEL2", "LEVEL3", "LEVEL4", "LEVEL5" };
boolean firstRow = true;
JSONArray itemsJSON = new JSONArray();
while (testResult.next()) {
;
JSONObject itemJSON = new JSONObject();
for (int col = 1; col <= metaData.getColumnCount(); col++) {
String label = metaData.getColumnLabel(col);
if (firstRow && Arrays.asList(topFieldNames)
.contains(label.toUpperCase())) {
Object val = testResult.getObject(col);
jsonObj.put(label.toLowerCase(), val);
}
// All Rows add to the Items array - this is a simple JSON structure
// Wiith just one top level array of objects
if (Arrays.asList(itemFieldNames).contains(label.toUpperCase())) {
Object val = testResult.getObject(col);
itemJSON.put(label.toLowerCase(), val);
}
}
/* If our item isnt blank add it to the items JSONArray */
if (itemJSON.optInt("rfrid", -1) != -1) {
itemsJSON.put(itemJSON);
}
firstRow = false;
}
jsonObj.put("testitems", itemsJSON);
testResult.close();
return jsonObj.toString();
} catch (Exception e) {
e.printStackTrace();
logger.error(e.toString());
}
return jsonObj.toString();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment