@Override
public Answer updateSystemsByUser(User user, List<UserSystem> newGroups) {
Answer a = new Answer(new MessageEvent(MessageEventEnum.DATA_OPERATION_OK));
AnswerList an = this.readByUser(user.getLogin());
if (an.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {
List<UserSystem> oldGroups = an.getDataList();
// delete if don't exist in new
for (UserSystem old : oldGroups) {
if (!newGroups.contains(old)) {
Answer del = userSystemDAO.remove(old);
if (!del.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {
a = del;
}
}
}
// insert if don't exist in old
for (UserSystem group : newGroups) {
if (!oldGroups.contains(group)) {
Answer add = userSystemDAO.create(group);
if (!add.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {
a = add;
}
}
}
}
return a;
}
@Override
public AnswerList readDistinctValuesByCriteria(
String searchTerm, Map<String, List<String>> individualSearch, String columnName) {
AnswerList answer = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<String> distinctValues = new ArrayList<>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
query.append("SELECT distinct sol.");
query.append(columnName);
query.append(" as distinctValues FROM soaplibrary sol");
query.append(" where 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and sol.");
searchSQL.append(
SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
query.append(" group by ifnull(sol.").append(columnName).append(",'')");
query.append(" order by sol.").append(columnName).append(" asc");
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
try (Connection connection = databaseSpring.connect();
PreparedStatement preStat = connection.prepareStatement(query.toString())) {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
// gets the data
while (resultSet.next()) {
distinctValues.add(
resultSet.getString("distinctValues") == null
? ""
: resultSet.getString("distinctValues"));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (distinctValues.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That
// means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
answer = new AnswerList(distinctValues, nrTotalRows);
} else if (distinctValues.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
answer = new AnswerList(distinctValues, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
answer = new AnswerList(distinctValues, nrTotalRows);
}
} catch (Exception e) {
LOG.warn("Unable to execute query : " + e.toString());
msg =
new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED)
.resolveDescription("DESCRIPTION", e.toString());
} finally {
// We always set the result message
answer.setResultMessage(msg);
}
answer.setResultMessage(msg);
answer.setDataList(distinctValues);
return answer;
}
@Override
public AnswerList readByCriteria(
int start,
int amount,
String column,
String dir,
String searchTerm,
Map<String, List<String>> individualSearch) {
AnswerList response = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<SoapLibrary> objectList = new ArrayList<SoapLibrary>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
// SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
// clauses that
// were applied -- used for pagination p
query.append("SELECT SQL_CALC_FOUND_ROWS * FROM soaplibrary sol ");
query.append(" WHERE 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and ");
String key = "IFNULL(sol." + entry.getKey() + ",'')";
String q = SqlUtil.getInSQLClauseForPreparedStatement(key, entry.getValue());
if (q == null || q == "") {
q = "(sol." + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
}
searchSQL.append(q);
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
if (!StringUtil.isNullOrEmpty(column)) {
query.append(" order by sol.").append(column).append(" ").append(dir);
}
if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
} else {
query.append(" limit ").append(start).append(" , ").append(amount);
}
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
try {
// gets the data
while (resultSet.next()) {
objectList.add(this.loadFromResultSet(resultSet));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (objectList.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
// That means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
response = new AnswerList(objectList, nrTotalRows);
} else if (objectList.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
response = new AnswerList(objectList, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
response = new AnswerList(objectList, nrTotalRows);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (preStat != null) {
preStat.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
response.setResultMessage(msg);
response.setDataList(objectList);
return response;
}
@Override
public AnswerList readByVariousByCriteria(
String system,
String country,
String environment,
int start,
int amount,
String column,
String dir,
String searchTerm,
String individualSearch) {
AnswerList response = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<BuildRevisionBatch> resultList = new ArrayList<BuildRevisionBatch>();
StringBuilder searchSQL = new StringBuilder();
StringBuilder query = new StringBuilder();
// SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
// clauses that
// were applied -- used for pagination p
query.append("SELECT SQL_CALC_FOUND_ROWS * FROM buildrevisionbatch ");
searchSQL.append(" where 1=1 ");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (`id` like ?");
searchSQL.append(" or `system` like ?");
searchSQL.append(" or `Country` like ?");
searchSQL.append(" or `Environment` like ?");
searchSQL.append(" or `Build` like ?");
searchSQL.append(" or `Revision` like ?");
searchSQL.append(" or `Batch` like ?");
searchSQL.append(" or `DateBatch` like ?)");
}
if (!StringUtil.isNullOrEmpty(individualSearch)) {
searchSQL.append(" and ( ? )");
}
if (!StringUtil.isNullOrEmpty(system)) {
searchSQL.append(" and (`system` = ?)");
}
if (!StringUtil.isNullOrEmpty(country)) {
searchSQL.append(" and (`country` = ?)");
}
if (!StringUtil.isNullOrEmpty(environment)) {
searchSQL.append(" and (`environment` = ?)");
}
query.append(searchSQL);
if (!StringUtil.isNullOrEmpty(column)) {
query.append(" order by `").append(column).append("` ").append(dir);
}
if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
} else {
query.append(" limit ").append(start).append(" , ").append(amount);
}
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
int i = 1;
if (!Strings.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
if (!StringUtil.isNullOrEmpty(individualSearch)) {
preStat.setString(i++, individualSearch);
}
if (!StringUtil.isNullOrEmpty(system)) {
preStat.setString(i++, system);
}
if (!StringUtil.isNullOrEmpty(country)) {
preStat.setString(i++, country);
}
if (!StringUtil.isNullOrEmpty(environment)) {
preStat.setString(i++, environment);
}
ResultSet resultSet = preStat.executeQuery();
try {
// gets the data
while (resultSet.next()) {
resultList.add(this.loadFromResultSet(resultSet));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (resultList.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
// That means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
response = new AnswerList(resultList, nrTotalRows);
} else if (resultList.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
response = new AnswerList(resultList, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
response = new AnswerList(resultList, nrTotalRows);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
if (preStat != null) {
preStat.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to close connection : " + exception.toString());
}
}
response.setResultMessage(msg);
response.setDataList(resultList);
return response;
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
ApplicationContext appContext =
WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
ITestCaseService testService = appContext.getBean(ITestCaseService.class);
IInvariantService invariantService = appContext.getBean(IInvariantService.class);
ICampaignService campaignService = appContext.getBean(ICampaignService.class);
ITestBatteryService testBatteryService = appContext.getBean(ITestBatteryService.class);
IBuildRevisionInvariantService buildRevisionInvariantService =
appContext.getBean(IBuildRevisionInvariantService.class);
String system = req.getParameter("system");
JSONArray jsonResponse = new JSONArray();
String[] columns = {
"test",
"project",
"ticket",
"bugID",
"origine",
"creator",
"application",
"priority",
"status",
"group",
"activePROD",
"activeUAT",
"activeQA",
"tcActive"
};
try {
JSONObject data;
for (String s : columns) {
data = new JSONObject();
data.put("data", new JSONArray(testService.findUniqueDataOfColumn(s)));
data.put("name", s);
jsonResponse.put(data);
}
JSONArray build = new JSONArray();
for (BuildRevisionInvariant bri :
buildRevisionInvariantService.convert(
buildRevisionInvariantService.readBySystemLevel(system, 1))) {
build.put(bri.getVersionName());
}
data = new JSONObject();
data.put("data", build);
data.put("name", "fromBuild");
jsonResponse.put(data);
data = new JSONObject();
data.put("data", build);
data.put("name", "toBuild");
jsonResponse.put(data);
data = new JSONObject();
data.put("data", build);
data.put("name", "targetBuild");
jsonResponse.put(data);
JSONArray revision = new JSONArray();
for (BuildRevisionInvariant bri :
buildRevisionInvariantService.convert(
buildRevisionInvariantService.readBySystemLevel(system, 2))) {
revision.put(bri.getVersionName());
}
data = new JSONObject();
data.put("data", revision);
data.put("name", "fromRev");
jsonResponse.put(data);
data = new JSONObject();
data.put("data", revision);
data.put("name", "toRev");
jsonResponse.put(data);
data = new JSONObject();
data.put("data", revision);
data.put("name", "targetRev");
jsonResponse.put(data);
JSONArray env = new JSONArray();
AnswerList answer =
invariantService.readByIdname(
"ENVIRONMENT"); // TODO: handle if the response does not turn ok
for (Invariant i : (List<Invariant>) answer.getDataList()) {
env.put(i.getValue());
}
data = new JSONObject();
data.put("data", env);
data.put("name", "executionEnv");
jsonResponse.put(data);
JSONArray country = new JSONArray();
answer =
invariantService.readByIdname("COUNTRY"); // TODO: handle if the response does not turn ok
for (Invariant i : (List<Invariant>) answer.getDataList()) {
country.put(i.getValue());
}
data = new JSONObject();
data.put("data", country);
data.put("name", "executionCountry");
jsonResponse.put(data);
JSONArray campaign = new JSONArray();
for (Campaign c : campaignService.findAll()) {
campaign.put(c.getCampaign());
}
data = new JSONObject();
data.put("data", campaign);
data.put("name", "campaign");
jsonResponse.put(data);
JSONArray battery = new JSONArray();
for (TestBattery c : testBatteryService.findAll()) {
battery.put(c.getTestbattery());
}
data = new JSONObject();
data.put("data", battery);
data.put("name", "testBattery");
jsonResponse.put(data);
resp.setContentType("application/json");
resp.getWriter().print(jsonResponse.toString());
} catch (JSONException e) {
MyLogger.log(GetDataForTestCaseSearch.class.getName(), Level.FATAL, "" + e);
resp.setContentType("text/html");
resp.getWriter().print(e.getMessage());
} catch (CerberusException e) {
MyLogger.log(GetDataForTestCaseSearch.class.getName(), Level.FATAL, "" + e);
resp.setContentType("text/html");
resp.getWriter().print(e.getMessage());
}
}