public StatisticsPageData fetchStatisticsPageData(final Long userId, final String discipline)
throws UserNotFoundException {
final User user = fetchUser(userId);
final String fromWhereClause =
" from WorkoutImpl w where :user MEMBER OF w.participants "
+ (discipline != null ? " and discipline = :discipline " : " ");
final Query disciplineQuery =
query(
"select w.discipline, count(*) from WorkoutImpl w "
+ "WHERE :user MEMBER OF w.participants GROUP BY w.discipline ORDER BY count(*) DESC");
disciplineQuery.setParameter("user", user);
final Query query = query("select sum(w.distance) " + fromWhereClause);
bindQuery(query, user, discipline);
final Query query2 =
query(
"select year(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ " GROUP BY year(w.date) ORDER BY year(w.date) DESC");
bindQuery(query2, user, discipline);
final Query query3 =
query(
"select year(w.date), month(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ "GROUP BY year(w.date), month(w.date) "
+ "ORDER BY year(w.date) DESC, month(w.date) DESC");
bindQuery(query3, user, discipline);
return new StatisticsPageData(
user,
disciplineQuery.getResultList(),
query.getSingleResult(),
query2.getResultList(),
query3.getResultList());
}
private Double fetchGlobalDistance(final Group group) {
final Query query =
query(
"select sum(w.distance) from GroupImpl g left join g.members u left join u.workouts w where g=:group");
query.setParameter("group", group);
return (Double) query.getSingleResult();
}
@Override
public Cidade cidadePeloNome(String nomeCidade) {
Query query =
em.createQuery("SELECT c FROM Cidade c WHERE c.cidadeNome = " + "'" + nomeCidade + "'");
cidade = (Cidade) query.getSingleResult();
return cidade;
// "SELECT e FROM Estado e WHERE e.nomeEstado = :nomeEstado"
}
public User fetchUser(final String name) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.name=:name");
query.setParameter("name", name);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
public User fetchRememberedUser(final String rememberCookie) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.rememberToken =:rememberCookie");
query.setParameter("rememberCookie", rememberCookie);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
public User fetchUser(final Long id) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.id=:id");
query.setParameter("id", id);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
private Double fetchGlobalDistance(final User user) {
final Query query =
query(
"select sum(w.distance) from WorkoutImpl w where 1=1"
+ (user != null ? " and :user MEMBER OF w.participants" : ""));
if (user != null) query.setParameter("user", user);
final Double result = (Double) query.getSingleResult();
return result == null ? Double.valueOf(0) : result;
}
public static Customer findCustomerByIndexedParameter(
EntityManager entityManager, String first, String last) {
Query query =
entityManager.createQuery(
"from Customer c " + " where c.firstName=?1" + " and c.lastName=?2");
query.setParameter(1, first);
query.setParameter(2, last);
return (Customer) query.getSingleResult();
}
public User facebookLogin(final Long facebookId) {
final Query query = query("select u from UserImpl u where u.facebookId=:facebookId");
query.setParameter("facebookId", facebookId);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
return null;
}
}
public <S> S findSingle(String ql, Object... args) {
Query query = createQuery(ql, args);
try {
@SuppressWarnings("unchecked")
S result = (S) query.getSingleResult();
return result;
} catch (NoResultException ignore) {
return null;
}
}
public int getUsuarioCount() {
EntityManager em = getEntityManager();
try {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<Usuario> rt = cq.from(Usuario.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
} finally {
em.close();
}
}
public ApiToken findValidToken(Customer customer) {
String hql = "from ApiToken tkn where tkn.customer=:customer and tkn.expiration > :dateTime";
Query query =
entityManager
.createQuery(hql, ApiToken.class)
.setParameter("customer", customer)
.setParameter("dateTime", new Date(), TemporalType.TIMESTAMP);
try {
return (ApiToken) query.getSingleResult();
} catch (NoResultException e) {
return null;
}
}
public Customer findCustomer(String email, String password) {
String hql = "from Customer cst where cst.email=:email and cst.password=:password";
Query query =
entityManager
.createQuery(hql)
.setParameter("email", email)
.setParameter("password", password);
try {
return (Customer) query.getSingleResult();
} catch (NoResultException e) {
return null;
}
}
@Override
public Company getCompany(long cvr)
{
EntityManager em = getEntityManager();
try {
Query query = em.createNamedQuery("Company.findByCvr");
query.setParameter("cvr", cvr);
//insert exception handling here
return (Company) query.getSingleResult();
} finally {
em.close();
}
}
// El número de factura para una factura nueva
private String getCodigo() {
// Usamos inicialización vaga
if (codigo == null) {
// Una consulta JPA para obtener el último número
Query query =
getManager().createQuery("select max(f.codigo) from Factura f where f.ano = :ano");
// Dates es una utilidad de OpenXava
query.setParameter("ano", Dates.getYear(new Date()));
Integer lastNumber = (Integer) query.getSingleResult();
if (lastNumber == null) lastNumber = 0;
// Añadimos 1 al último número de factura
codigo = Integer.toString(lastNumber + 1);
}
return codigo;
}
/** @return null if auth failed, user otherwise */
public User authenticate(final String login, final String password, final boolean rememberMe) {
final Query query = query("select u from UserImpl u where u.name=:user_login");
query.setParameter("user_login", login);
try {
final UserImpl user = (UserImpl) query.getSingleResult();
if (rememberMe && user.getRememberToken() == null) {
final String token = generateToken();
user.setRememberToken(token);
entityManager.merge(user);
}
return user.checkPassword(password) ? user : null;
} catch (NoResultException e) {
return null;
}
}
public void testConverterWithMetaSets() throws Exception {
// Creating new
execute("CRUD.new");
setValue("invoice.year", "2002");
setValue("invoice.number", "1");
setValue("type.number", "1");
setValue("number", "66");
setValue("description", "JUNIT");
setValue("distance", usesAnnotatedPOJO() ? "1" : "2"); // National, in database 'N'
execute("CRUD.save");
assertNoErrors();
assertValue("invoice.year", "");
assertValue("invoice.number", "");
assertValue("type.number", "");
assertValue("number", "");
assertValue("description", "");
assertValue("distance", usesAnnotatedPOJO() ? "" : "0");
// Search just created
setValue("invoice.year", "2002");
setValue("invoice.number", "1");
setValue("type.number", "1");
setValue("number", "66");
execute("CRUD.refresh");
assertNoErrors();
assertValue("invoice.year", "2002");
assertValue("invoice.number", "1");
assertValue("type.number", "1");
assertValue("number", "66");
assertValue("description", "JUNIT");
assertValue("distance", usesAnnotatedPOJO() ? "1" : "2");
assertNoErrors();
// Verifying database value
Query query =
XPersistence.getManager()
.createNativeQuery(
"select d.distance from XAVATEST.Delivery as d where "
+ "invoice_year=2002 and invoice_number=1 and type=1 and number=66");
String distanceDB = (String) query.getSingleResult();
assertEquals("distance in database incorrect", "N", distanceDB);
// Delete
execute("CRUD.delete");
assertNoErrors();
assertMessage("Delivery deleted successfully");
}
public static String getEstadoById(Long idSolicitud) {
Query query = JPA.em().createQuery("select estado from Solicitud s where s.id=" + idSolicitud);
return (String) query.getSingleResult();
}
public static Product2 findByNumber(long number) throws NoResultException {
Query query =
XPersistence.getManager().createQuery("from Product2 as o where o.number = :number");
query.setParameter("number", new Long(number));
return (Product2) query.getSingleResult();
}
