/*
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
* This file is part of Entando software.
* JAPS and its source-code is licensed under the terms of the
* GNU General Public License as published by the Free Software
* Foundation (http://www.fsf.org/licensing/licenses/gpl.txt).
*
* You may copy, adapt, and redistribute this file for commercial
* or non-commercial use.
* When copying, adapting, or redistributing this document you
* are required to provide proper attribution to AgileTec, using
* the following attribution line:
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
*/
package com.agiletec.plugins.jpwtt.aps.system.services.ticket;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import com.agiletec.aps.system.common.AbstractDAO;
import com.agiletec.aps.system.exception.ApsSystemException;
import com.agiletec.plugins.jpwtt.aps.system.services.ticket.model.ITicketSearchBean;
import com.agiletec.plugins.jpwtt.aps.system.services.ticket.model.Ticket;
import com.agiletec.plugins.jpwtt.aps.system.services.ticket.model.TicketOperation;
/**
* Data Access Object per gli oggetti Ticket .
* @version 1.1
* @author S.Didaci - G.Cocco
*/
public class TicketDAO extends AbstractDAO implements ITicketDAO {
/**
* Carica l' elenco dei ticket (chiamate all'assistenza tecnica) inserite nel sistema.
* @param conn la connessione al db.
* @param ctx Il contesto del sistema.
* @return La mappa dei ticket
* @throws ApsSystemException in caso di errore nell'accesso al db.
*/
@Override
public List<Ticket> loadTickets() throws ApsSystemException {
Connection conn = null;
Statement stat = null;
ResultSet res = null;
List<Ticket> tickets = new ArrayList<Ticket>();
try {
conn = this.getConnection();
stat = conn.createStatement();
res = stat.executeQuery(LOAD_ALL_TICKETS);
while(res.next()) {
Ticket ticket = this.loadTicket(res);
tickets.add(ticket);
}
} catch (Throwable t) {
processDaoException(t, "Errore in caricamento tickets", "loadTickets");
} finally {
closeDaoResources(res, stat,conn);
}
return tickets;
}
@Override
public List<String> searchTicketIds(ITicketSearchBean searchBean) throws ApsSystemException {
List<String> tickets = new ArrayList<String>();
PreparedStatement stat = null;
ResultSet res = null;
Connection conn = null;
try {
conn = this.getConnection();
String query = this.createQueryString(searchBean);
stat = conn.prepareStatement(query);
this.buildStatement(searchBean, stat);
res = stat.executeQuery();
this.flowResult(tickets, searchBean, res);
} catch (Throwable t) {
processDaoException(t, "Errore in caricamento lista id ticket", "searchTicketIds");
} finally {
closeDaoResources(res, stat, conn);
}
return tickets;
}
protected void flowResult(List<String> tickets, ITicketSearchBean searchBean, ResultSet res) throws Throwable {
if (searchBean!=null) {
String message = searchBean.getMessage();
if (message!=null && message.length()>0) {
while (res.next()) {
String ticketId = res.getString(1);
String ticketMessage = res.getString(2);
if (this.checkText(message, ticketMessage)) {
tickets.add(ticketId);
}
}
return;
}
}
while (res.next()) {
String ticketId = res.getString(1);
tickets.add(ticketId);
}
}
/**
* This utility method checks if the given Text matches or is contained inside
* another one.
* @param insertedText The text to look for
* @param text The text to search in
* @return True if an occurrence of 'insertedText' is found in 'text'.
*/
protected boolean checkText(String insertedText, String text) {
if (insertedText.trim().length() == 0 ||
(text != null && text.toLowerCase().indexOf(insertedText.trim().toLowerCase()) != -1)) {
return true;
}
return false;
}
/**
* Carica unno specifico ticket dato il suo identificativo (ticketNumber)
* @param res Il resultset da leggere
* @param ctx Il contesto di sistema
* @return Il ticket.
* @throws ApsSystemException
*/
@Override
public Ticket loadTicket(String code) throws ApsSystemException {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
Ticket ticket = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(LOAD_TICKET);
stat.setString(1, code);
res = stat.executeQuery();
if (res.next()) {
ticket = this.loadTicket(res);
}
} catch (Throwable t) {
processDaoException(t, "Errore in caricamento ticket", "loadTicket");
} finally {
closeDaoResources(res, stat, conn);
}
return ticket;
}
@Override
public List<TicketOperation> loadTicketOperations(String code) throws ApsSystemException {
List<TicketOperation> operations = new ArrayList<TicketOperation>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(LOAD_TICKETOPERATIONS);
stat.setString(1, code);
res = stat.executeQuery();
while (res.next()) {
TicketOperation operation = this.loadTicketOperation(res);
operations.add(operation);
}
} catch (Throwable t) {
processDaoException(t, "Errore in caricamento ticket operations", "loadTicketOperations");
} finally {
closeDaoResources(res, stat, conn);
}
return operations;
}
/**
* Costruisce e restituisce un ticket leggendo una riga di recordset.
* @param res Il resultset da leggere
* @param ctx Il contesto di sistema
* @return il ticket.
* @throws ApsSystemException
*/
@Override
public void addTicket(Ticket ticket) throws ApsSystemException {
PreparedStatement stat = null;
Connection conn = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(ADD_TICKET);
stat.setString(1, ticket.getCode());
stat.setTimestamp(2, new Timestamp(ticket.getCreationDate().getTime()));
stat.setString(3, ticket.getNome());
stat.setString(4, ticket.getCognome());
stat.setString(5, ticket.getCodFisc());
stat.setString(6, ticket.getComune());
stat.setString(7, ticket.getLocalita());
stat.setString(8, ticket.getTipoIndirizzo());
stat.setString(9, ticket.getIndirizzo());
stat.setString(10, ticket.getNumeroIndirizzo());
stat.setString(11, ticket.getTelefono());
stat.setString(12, ticket.getEmail());
stat.setString(13, ticket.getMessage());
stat.setString(14, ticket.getAuthor());
stat.setInt(15, ticket.getUserInterventionType());
stat.setInt(16, ticket.getOpInterventionType());
stat.setInt(17, ticket.getPriority());
stat.setString(18, ticket.getWttRole());
stat.setString(19, ticket.getWttOperator());
stat.setInt(20, ticket.getStatus());
Timestamp closingTime = (ticket.getClosingDate()!=null) ? new Timestamp(ticket.getClosingDate().getTime()) : null;
stat.setTimestamp(21, closingTime);
int resolved = ticket.isResolved() ? 1 : 0;
stat.setInt(22, resolved);
stat.executeUpdate();
} catch (Throwable t) {
this.executeRollback(conn);
processDaoException(t, "Error updating ticket", "addTicket");
} finally{
this.closeDaoResources(null, stat, conn);
}
}
public void updateTicketWithOperation(Ticket ticket, TicketOperation operation) throws ApsSystemException {
Connection conn = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
this.addTicketOperation(operation, conn);
this.updateTicket(ticket, conn);
conn.commit();
} catch (Throwable t) {
this.executeRollback(conn);
processDaoException(t, "Error updating Ticket with operation", "updateTicketWithOperation");
} finally {
closeConnection(conn);
}
}
protected void updateTicket(Ticket ticket, Connection conn) throws ApsSystemException {
PreparedStatement stat = null;
try {
stat = conn.prepareStatement(UPDATE_TICKET);
stat.setTimestamp(1, new Timestamp(ticket.getCreationDate().getTime()));
stat.setString(2, ticket.getNome());
stat.setString(3, ticket.getCognome());
stat.setString(4, ticket.getCodFisc());
stat.setString(5, ticket.getComune());
stat.setString(6, ticket.getLocalita());
stat.setString(7, ticket.getTipoIndirizzo());
stat.setString(8, ticket.getIndirizzo());
stat.setString(9, ticket.getNumeroIndirizzo());
stat.setString(10, ticket.getTelefono());
stat.setString(11, ticket.getEmail());
stat.setString(12, ticket.getMessage());
stat.setString(13, ticket.getAuthor());
stat.setInt(14, ticket.getUserInterventionType());
stat.setInt(15, ticket.getOpInterventionType());
stat.setInt(16, ticket.getPriority());
stat.setString(17, ticket.getWttRole());
stat.setString(18, ticket.getWttOperator());
stat.setInt(19, ticket.getStatus());
Timestamp closingTime = (ticket.getClosingDate()!=null) ? new Timestamp(ticket.getClosingDate().getTime()) : null;
stat.setTimestamp(20, closingTime);
int resolved = ticket.isResolved() ? 1 : 0;
stat.setInt(21, resolved);
stat.setString(22, ticket.getCode());
stat.executeUpdate();
} catch (Throwable t) {
processDaoException(t, "Error updating ticket", "updateTicket");
} finally {
closeDaoResources(null, stat);
}
}
protected void addTicketOperation(TicketOperation operation, Connection conn) throws ApsSystemException {
PreparedStatement stat = null;
try {
stat = conn.prepareStatement(ADD_TICKETOPERATION);
stat.setInt(1, operation.getId());
stat.setString(2, operation.getTicketCode());
stat.setString(3, operation.getOperator());
stat.setInt(4, operation.getOperationCode());
stat.setInt(5, operation.getInterventionType());
stat.setInt(6, operation.getPriority());
stat.setString(7, operation.getWttRole());
stat.setString(8, operation.getNote());
Timestamp time = (operation.getDate()!=null) ? new Timestamp(operation.getDate().getTime()) : null;
stat.setTimestamp(9, time);
stat.executeUpdate();
} catch (Throwable t) {
processDaoException(t, "Error adding ticket operation", "addTicketOperation");
} finally{
this.closeDaoResources(null, stat);
}
}
protected String createQueryString(ITicketSearchBean searchBean) {
StringBuffer query = new StringBuffer();
if (searchBean!=null) {
String message = searchBean.getMessage();
if (message!=null && message.length()>0) {
query.append(SELECT_TICKET_CODES_FLOWRESULT);
} else {
query.append(SELECT_TICKET_CODES);
}
boolean appendWhere = true;
String author = searchBean.getAuthor();
if (author!=null && author.length()>0) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_AUTHOR_CLAUSE);
appendWhere = false;
}
int[] states = searchBean.getStates();
if (states!=null && states.length>0) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
int size = states.length;
if (size==1) {
query.append(APPEND_STATUS_CLAUSE);
} else {
query.append(APPEND_STATUSIN_STARTCLAUSE);
query.append(APPEND_IN_FIRSTVALUECLAUSE);
while (size>1) {
query.append(APPEND_IN_OTHERVALUECLAUSE);
size--;
}
query.append(APPEND_IN_ENDCLAUSE);
}
appendWhere = false;
}
if (searchBean.getPriority()!=null) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_PRIORITY_CLAUSE);
appendWhere = false;
}
if (searchBean.getUserInterventionType()!=null) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_USER_INTERVENTIONTYPE_CLAUSE);
appendWhere = false;
}
if (searchBean.getAssignedInterventionType()!=null) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_OP_INTERVENTIONTYPE_CLAUSE);
appendWhere = false;
}
if (searchBean.getResolved()!=null) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_RESOLVED_CLAUSE);
appendWhere = false;
}
String operator = searchBean.getOperator();
if (operator!=null && operator.length()>0) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
query.append(APPEND_OPERATOR_CLAUSE);
appendWhere = false;
}
String[] roles = searchBean.getWttRoles();
if (roles!=null && roles.length>0) {
query.append(appendWhere ? APPEND_WHERE : APPEND_AND);
int size = roles.length;
if (size==1) {
query.append(APPEND_ROLES_CLAUSE);
} else {
query.append(APPEND_ROLESIN_STARTCLAUSE);
query.append(APPEND_IN_FIRSTVALUECLAUSE);
while (size>1) {
query.append(APPEND_IN_OTHERVALUECLAUSE);
size--;
}
query.append(APPEND_IN_ENDCLAUSE);
}
appendWhere = false;
}
} else {
query.append(SELECT_TICKET_CODES);
}
query.append(APPEND_ORDERBY_CLAUSE);
return query.toString();
}
protected int buildStatement(ITicketSearchBean searchBean, PreparedStatement stat) throws SQLException {
int pos = 1;
if (searchBean!=null) {
String author = searchBean.getAuthor();
if (author!=null && author.length()>0) {
stat.setString(pos++, author);
}
int[] states = searchBean.getStates();
if (states!=null && states.length>0) {
for (int status : states) {
stat.setInt(pos++, status);
}
}
Integer priority = searchBean.getPriority();
if (priority!=null) {
stat.setInt(pos++, priority.intValue());
}
Integer userInterventionType = searchBean.getUserInterventionType();
if (userInterventionType!=null) {
stat.setInt(pos++, userInterventionType.intValue());
}
Integer opInterventionType = searchBean.getAssignedInterventionType();
if (opInterventionType!=null) {
stat.setInt(pos++, opInterventionType.intValue());
}
Boolean resolved = searchBean.getResolved();
if (resolved!=null) {
int resolvedValue = resolved.booleanValue() ? 1 : 0;
stat.setInt(pos++, resolvedValue);
}
String operator = searchBean.getOperator();
if (operator!=null && operator.length()>0) {
stat.setString(pos++, operator);
}
String[] roles = searchBean.getWttRoles();
if (roles!=null && roles.length>0) {
for (String role : roles) {
stat.setString(pos++, role);
}
}
}
return pos;
}
/**
* Costruisce e restituisce un ticket leggendo una riga di recordset.
* @param res Il resultset da leggere
* @param ctx Il contesto di sistema
* @return Il ticket.
* @throws ApsSystemException
*/
private Ticket loadTicket(ResultSet res) throws ApsSystemException {
Ticket ticket = new Ticket();
try {
ticket.setCode(res.getString(1));
ticket.setCreationDate(res.getTimestamp(2));
ticket.setNome(res.getString(3));
ticket.setCognome(res.getString(4));
ticket.setCodFisc(res.getString(5));
ticket.setComune(res.getString(6));
ticket.setLocalita(res.getString(7));
ticket.setTipoIndirizzo(res.getString(8));
ticket.setIndirizzo(res.getString(9));
ticket.setNumeroIndirizzo(res.getString(10));
ticket.setTelefono(res.getString(11));
ticket.setEmail(res.getString(12));
ticket.setMessage(res.getString(13));
ticket.setAuthor(res.getString(14));
ticket.setUserInterventionType(res.getInt(15));
ticket.setOpInterventionType(res.getInt(16));
ticket.setPriority(res.getInt(17));
ticket.setWttRole(res.getString(18));
ticket.setWttOperator(res.getString(19));
ticket.setStatus(res.getInt(20));
ticket.setClosingDate(res.getTimestamp(21));
int resolved = res.getInt(22);
ticket.setResolved(resolved==1);
} catch (Throwable t) {
throw new ApsSystemException("Errore in caricamento ticket", t);
}
return ticket;
}
private TicketOperation loadTicketOperation(ResultSet res) throws SQLException {
TicketOperation ticketOperation = new TicketOperation();
ticketOperation.setId(res.getInt(1));
ticketOperation.setTicketCode(res.getString(2));
ticketOperation.setOperator(res.getString(3));
ticketOperation.setOperationCode(res.getInt(4));
ticketOperation.setInterventionType(res.getInt(5));
ticketOperation.setPriority(res.getInt(6));
ticketOperation.setWttRole(res.getString(7));
ticketOperation.setNote(res.getString(8));
ticketOperation.setDate(res.getTimestamp(9));
return ticketOperation;
}
private final String SELECT_TICKET_CODES =
"SELECT code FROM jpwtt_tickets ";
private final String SELECT_TICKET_CODES_FLOWRESULT =
"SELECT code, message FROM jpwtt_tickets ";
private final String APPEND_WHERE = "WHERE ";
private final String APPEND_AND = "AND ";
private final String APPEND_AUTHOR_CLAUSE = "author = ? ";
private final String APPEND_STATUS_CLAUSE = "status = ? ";
private final String APPEND_STATUSIN_STARTCLAUSE = "status IN ( ";
private final String APPEND_IN_ENDCLAUSE = ") ";
private final String APPEND_IN_FIRSTVALUECLAUSE = "? ";
private final String APPEND_IN_OTHERVALUECLAUSE = ", ? ";
private final String APPEND_ROLES_CLAUSE = "wttrole = ? ";
private final String APPEND_ROLESIN_STARTCLAUSE = "wttrole IN ( ";
private final String APPEND_PRIORITY_CLAUSE = "priority = ? ";
private final String APPEND_USER_INTERVENTIONTYPE_CLAUSE = "user_interventiontype = ? ";
private final String APPEND_OP_INTERVENTIONTYPE_CLAUSE = "op_interventiontype = ? ";
private final String APPEND_RESOLVED_CLAUSE = "resolved = ? ";
private final String APPEND_ORDERBY_CLAUSE = "ORDER BY creationdate DESC ";
private final String APPEND_OPERATOR_CLAUSE = "wttoperator = ? ";
private final String LOAD_ALL_TICKETS =
"SELECT code, creationdate, nome, cognome, codfisc, comune, localita, tipoindirizzo, indirizzo, " +
"numeroindirizzo, telefono, email, message, author, user_interventiontype, op_interventiontype, priority, " +
"wttrole, wttoperator, status, closingdate, resolved FROM jpwtt_tickets ORDER BY code desc";
private final String LOAD_TICKET =
"SELECT code, creationdate, nome, cognome, codfisc, comune, localita, tipoindirizzo, indirizzo, " +
"numeroindirizzo, telefono, email, message, author, user_interventiontype, op_interventiontype, priority, " +
"wttrole, wttoperator, status, closingdate, resolved FROM jpwtt_tickets WHERE code = ? ";
private final String LOAD_TICKETOPERATIONS =
"SELECT id, ticketcode, operator, operationcode, interventiontype, " +
"priority, wttrole, note, date FROM jpwtt_ticketoperations WHERE ticketcode = ? ";
private final String ADD_TICKET =
"INSERT INTO jpwtt_tickets ( code, creationdate, nome, cognome, codfisc, comune, localita, tipoindirizzo, indirizzo, " +
"numeroindirizzo, telefono, email, message, author, user_interventiontype, op_interventiontype, priority, wttrole, " +
"wttoperator, status, closingdate, resolved ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ? )";
private final String UPDATE_TICKET =
"UPDATE jpwtt_tickets SET creationdate = ?, nome = ?, cognome = ?, codfisc = ?, comune = ?, localita = ?, tipoindirizzo = ?, " +
"indirizzo = ?, numeroindirizzo = ?, telefono = ?, email = ?, message = ?, author = ?, user_interventiontype = ?, " +
"op_interventiontype = ?, priority = ?, wttrole = ?, wttoperator = ?, status = ?, closingdate = ?, resolved = ? WHERE code = ?";
private final String ADD_TICKETOPERATION =
"INSERT INTO jpwtt_ticketoperations ( id, ticketcode, operator, operationcode, interventiontype, " +
"priority, wttrole, note, date ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
}