V1_0.java [src/java/m/svap/svap07a_writerefstream] Revision: default Date:
/*
* $Id$
*
* This file is part of the Cloud Services Integration Platform (CSIP),
* a Model-as-a-Service framework, API, and application suite.
*
* 2012-2017, OMSLab, Colorado State University.
*
* OMSLab licenses this file to you under the MIT license.
* See the LICENSE file in the project root for more information.
*/
package m.svap.svap07a_writerefstream;
import csip.ModelDataService;
import csip.ServiceException;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Map;
import java.util.Set;
import javax.ws.rs.Path;
import csip.annotations.Description;
import csip.annotations.Name;
import java.util.Collection;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import svap.utils.DBQueries;
import svap.utils.DBResources;
import svap.utils.D_Ref_Stream_Photos_Table;
import svap.utils.D_Ref_Stream_Scores_Table;
import svap.utils.D_Reference_Stream_Table;
import utils.EvalResult;
import utils.ExternalData;
import utils.ExternalFileData;
import utils.TableException;
/**
* SVAP-07a: Add or Update Reference Stream Record
*
* This service writes watershed and stream reach description information,
* assessment scores, and photos to domain tables for a SVAP reference stream
*
* @author Rumpal Sidhu
* @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
*
* @version 1.0
*/
@Name("SVAP-07a: Add or Update Reference Stream Record")
@Description("This service writes watershed and stream reach description "
+ "information, assessment scores, and photos to domain tables for a SVAP reference stream.")
@Path("m/svap/writerefstream/1.0")
@Resource(from = DBResources.class)
public class V1_0 extends ModelDataService {
private static final String METHOD_ADD = "add";
private static final String METHOD_UPDATE = "update";
private static final String METHOD_DELETE = "delete";
private static final String METHOD_EXPIRE = "expire";
private static final String SCORE_LIST = "Element List";
private static final String PHOTO_LIST = "Reference stream photos";
D_Ref_Stream_Scores_Table refScores;
D_Reference_Stream_Table refStream;
D_Ref_Stream_Photos_Table refPhotos;
private String edit_action, request_date;
private int ref_stream_id;
private JSONArray scores;
private JSONArray photos;
private JSONArray stream;
@Override
protected void preProcess() throws ServiceException, JSONException, SQLException {
boolean mustExist = false;
edit_action = parameter().getString("edit_action");
validateEditAction(edit_action);
request_date = parameter().getString("request_date");
ref_stream_id = parameter().getInt("ref_stream_id", -1);
switch (edit_action.toLowerCase()) {
case METHOD_UPDATE:
case METHOD_DELETE:
case METHOD_EXPIRE:
mustExist = true;
break;
}
validateStreamId(ref_stream_id, mustExist);
stream = buildRefStreamJSON();
refScores = new D_Ref_Stream_Scores_Table(null);
refStream = new D_Reference_Stream_Table(null);
refPhotos = new D_Ref_Stream_Photos_Table(null);
}
@Override
protected void doProcess() throws ServiceException, SQLException, TableException, JSONException {
try {
refStream.readAllValuesFromJSON(stream);
} catch (JSONException | ServiceException ex) {
throw new ServiceException("Cannot parse the input data for the reference stream: " + ex.getMessage(), ex);
}
switch (edit_action.toLowerCase()) {
case METHOD_ADD:
try (Connection connection = resources().getJDBC(DBResources.SVAP_RW)) {
refStream.setConnection(connection);
refScores.setConnection(connection);
refPhotos.setConnection(connection);
if (refStream.rowCount() > 0) {
int id = -1;
refStream.setRowIdKey(D_Reference_Stream_Table.ID);
refStream.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Reference_Stream_Table.ID)));
refStream.insertTableToDb();
refStream.setRowFirst();
id = refStream.getInteger(D_Reference_Stream_Table.ID);
readScores(id);
readPhotos(id);
if (refScores.rowCount() > 0) {
refScores.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Ref_Stream_Scores_Table.ID)));
refScores.insertTableToDb();
}
if (refPhotos.rowCount() > 0) {
refPhotos.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Ref_Stream_Photos_Table.ID)));
refPhotos.insertTableToDb();
}
}
}
break;
case METHOD_UPDATE:
try (Connection connection = resources().getJDBC(DBResources.SVAP_RW)) {
refStream.setConnection(connection);
refScores.setConnection(connection);
refPhotos.setConnection(connection);
if (refStream.rowCount() > 0) {
refStream.setWhereKeys(new ArrayList<>(Arrays.asList(D_Reference_Stream_Table.ID)));
refStream.updateTableToDb();
readScores(ref_stream_id);
readPhotos(ref_stream_id);
if (refScores.rowCount() > 0) {
refScores.setWhereKeys(new ArrayList<>(Arrays.asList(D_Ref_Stream_Scores_Table.ID)));
refScores.updateTableToDb();
}
if (refPhotos.rowCount() > 0) {
refPhotos.setWhereKeys(new ArrayList<>(Arrays.asList(D_Ref_Stream_Photos_Table.ID, D_Ref_Stream_Photos_Table.REF_STREAM_ID)));
refPhotos.updateTableToDb();
}
}
}
break;
case METHOD_DELETE: //Delete in reverse order because of FK requirements.
try (Connection connection = resources().getJDBC(DBResources.SVAP_DELETE)) {
refStream.setConnection(connection);
refScores.setConnection(connection);
refPhotos.setConnection(connection);
if (refStream.rowCount() > 0) {
readScores(ref_stream_id);
readPhotos(ref_stream_id);
if (refScores.rowCount() > 0) {
refScores.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Ref_Stream_Scores_Table.ELEMENT_ID, D_Ref_Stream_Scores_Table.SCORE, D_Ref_Stream_Scores_Table.COMMENT)));
refScores.deleteRowsFromTableDb();
}
if (refPhotos.rowCount() > 0) {
refPhotos.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Ref_Stream_Photos_Table.DESCRIPTION, D_Ref_Stream_Photos_Table.NAME, D_Ref_Stream_Photos_Table.PHOTO_DATE, D_Ref_Stream_Photos_Table.PHOTO_FILE, D_Ref_Stream_Photos_Table.PHOTO_ID)));
refPhotos.deleteRowsFromTableDb();
}
if (refStream.rowCount() > 0) {
refStream.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Reference_Stream_Table.NAME, D_Reference_Stream_Table.DESCRIPTION, D_Reference_Stream_Table.LAST_UPDATE, D_Reference_Stream_Table.EXPIRED_DATE, D_Reference_Stream_Table.LOCATION)));
refStream.deleteRowsFromTableDb();
}
}
}
break;
case METHOD_EXPIRE:
try (Connection connection = resources().getJDBC(DBResources.SVAP_DELETE)) {
refStream.setConnection(connection);
refStream.setWhereKeys(new ArrayList<>(Arrays.asList(D_Reference_Stream_Table.ID)));
refStream.setUnusedColumnsSQL(new ArrayList<>(Arrays.asList(D_Reference_Stream_Table.NAME, D_Reference_Stream_Table.DESCRIPTION, D_Reference_Stream_Table.LOCATION)));
refStream.updateTableToDb();
}
break;
default:
throw new RuntimeException("Unknown edit method.");
}
}
@Override
protected void postProcess() throws JSONException {
JSONArray outArray = new JSONArray();
refStream.toJSONAll(outArray);
if (refScores.rowCount() > 0) {
JSONArray scoreArray = new JSONArray();
refScores.toJSONAll(scoreArray);
JSONUtils.data("Stream Scores", scoreArray);
}
if (refPhotos.rowCount() > 0) {
JSONArray photoArray = new JSONArray();
refPhotos.toJSON(photoArray);
JSONUtils.data("Stream Photos", photoArray);
}
results().put("Streams", outArray);
}
private void readScores(int id) throws ServiceException {
try {
scores = buildStreamScoreJSON(id);
refScores.readAllValuesFromJSON(scores);
} catch (JSONException | ServiceException ex) {
throw new ServiceException("Cannot parse the input data for the reference stream scores: " + ex.getMessage(), ex);
}
}
private void readPhotos(int id) throws ServiceException {
try {
photos = buildStreamPhotoJSON(id);
Collection<File> files = attachments().getFiles();
File[] fileArray = files.toArray(new File[files.size()]);
ExternalData extData = new ExternalFileData(fileArray);
refPhotos.readAllValuesFromJSON(photos);
if (refPhotos.rowCount() > 0) {
refPhotos.readAllExternalData(extData, EvalResult.DEFAULT_FILE_TYPE);
}
} catch (JSONException | TableException | ServiceException ex) {
throw new ServiceException("Cannot parse the input data for the reference stream photo files: " + ex.getMessage(), ex);
}
}
private void validateEditAction(String action) throws ServiceException {
if (null != action) {
switch (action.toLowerCase()) {
case METHOD_ADD:
case METHOD_UPDATE:
case METHOD_DELETE:
case METHOD_EXPIRE:
return;
}
}
throw new ServiceException("Invalid edit_action specified: " + action + ". Action must be: " + METHOD_ADD + ", " + METHOD_UPDATE + ", " + METHOD_DELETE + ", " + METHOD_EXPIRE);
}
private void validateStreamId(int sId, boolean mustExist) throws ServiceException, SQLException {
try (Connection conn = resources().getJDBC(DBResources.SVAP_RW); PreparedStatement statement = conn.prepareStatement(DBQueries.SVAP7aQuery01())) {
statement.setInt(1, sId);
ResultSet resultSet = statement.executeQuery();
if (!resultSet.next()) {
if (mustExist) {
throw new ServiceException("A valid ref_stream_id must be present for update action.");
}
} else {
if (!mustExist) {
throw new ServiceException("Cannot add that ref_stream_id. It already exists.");
}
}
}
}
private JSONArray buildRefStreamJSON() throws JSONException, ServiceException {
JSONArray tableData = new JSONArray();
JSONArray tableDataArr = new JSONArray();
tableDataArr.put(JSONUtils.data("id", ref_stream_id));
tableDataArr.put(JSONUtils.data("name", parameter().getString("ref_stream_name")));
tableDataArr.put(JSONUtils.data("description", parameter().getString("ref_stream_description")));
tableDataArr.put(JSONUtils.data("last_update", LocalDate.now()));
if (edit_action.equalsIgnoreCase(METHOD_EXPIRE)) {
tableDataArr.put(JSONUtils.data("expired_date", LocalDate.parse(request_date)));
} else {
tableDataArr.put(JSONUtils.data("expired_date", null));
}
tableDataArr.put(parameter().getParamJSON("location"));
tableData.put(tableDataArr);
return tableData;
}
private JSONArray buildStreamScoreJSON(int ref_id) throws JSONException, ServiceException {
JSONArray tableData = new JSONArray();
if (parameter().has(SCORE_LIST)) {
JSONArray elementArray = parameter().getJSONArray(SCORE_LIST);
for (int i = 0; i < elementArray.length(); i++) {
Map<String, JSONObject> element = JSONUtils.preprocess(elementArray.getJSONArray(i));
JSONArray tableDataArr = new JSONArray();
tableDataArr.put(JSONUtils.data("ref_stream_id", ref_id));
tableDataArr.put(JSONUtils.data("element_id", JSONUtils.getIntParam(element, "svap_element_id", 0)));
tableDataArr.put(JSONUtils.data("score", JSONUtils.getIntParam(element, "svap_score", 0)));
tableDataArr.put(JSONUtils.data("comment", JSONUtils.getStringParam(element, "comment", null)));
tableDataArr.put(JSONUtils.data("comment", JSONUtils.getStringParam(element, "comment", null)));
tableDataArr.put(JSONUtils.data("id", JSONUtils.getIntParam(element, "id", 0)));
tableData.put(tableDataArr);
}
}
return tableData;
}
private JSONArray buildStreamPhotoJSON(int ref_id) throws JSONException, ServiceException {
JSONArray tableData = new JSONArray();
if (parameter().has(PHOTO_LIST)) {
JSONArray refStreamPhotoArray = parameter().getJSONArray(PHOTO_LIST);
for (int i = 0; i < refStreamPhotoArray.length(); i++) {
Map<String, JSONObject> item = JSONUtils.preprocess(refStreamPhotoArray.getJSONArray(i));
JSONArray tableDataArr = new JSONArray();
tableDataArr.put(JSONUtils.data("ref_stream_id", ref_id));
tableDataArr.put(JSONUtils.data("photo_id", JSONUtils.getIntParam(item, "photo_id", 0)));
tableDataArr.put(JSONUtils.data("photo_date", JSONUtils.getStringParam(item, "photo_date", null)));
tableDataArr.put(JSONUtils.data("name", JSONUtils.getStringParam(item, "name", null)));
tableDataArr.put(JSONUtils.data("description", JSONUtils.getStringParam(item, "description", null)));
tableDataArr.put(JSONUtils.data("id", JSONUtils.getIntParam(item, "id", 0)));
JSONObject photoFile = item.get("photo_file");
tableDataArr.put(photoFile);
tableData.put(tableDataArr);
}
}
return tableData;
}
}