/*
|
* Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
|
* Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
|
*/
|
package com.megatim.apifdxweb.dao.impl.tmp;
|
|
import com.megatim.apifdxweb.dao.ifaces.tmp.TypeFichierDataTmpDAO;
|
import com.megatim.apifdxweb.model.tmp.TypeFichierDataTmp;
|
import com.megatim.fdxcommons.model.integration.ColumnDefinition;
|
import java.util.ArrayList;
|
import java.util.Collections;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Optional;
|
import javax.ejb.Stateless;
|
import javax.persistence.EntityManager;
|
import javax.persistence.PersistenceContext;
|
import javax.persistence.Query;
|
import javax.persistence.Tuple;
|
|
/**
|
*
|
* @author ASUS
|
*/
|
@Stateless
|
public class TypeFichierDataTmpDAOImpl implements TypeFichierDataTmpDAO {
|
|
@PersistenceContext(unitName = "fdxPU")
|
protected EntityManager em;
|
|
@Override
|
public Class<TypeFichierDataTmp> getManagedEntityClass() {
|
return TypeFichierDataTmp.class;
|
}
|
|
@Override
|
public EntityManager getEntityManager() {
|
return em;
|
}
|
|
@Override
|
public TypeFichierDataTmp getById(Long id) {
|
Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.id = :id");
|
query.setParameter("id", id);
|
|
Optional<TypeFichierDataTmp> optionalTypeFichierDataTmp = query.getResultList().stream().findFirst();
|
return optionalTypeFichierDataTmp.isPresent() ? optionalTypeFichierDataTmp.get() : null;
|
}
|
|
@Override
|
public void deleteByToken(String token) {
|
Query query = em.createQuery("DELETE TypeFichierDataTmp t WHERE t.token = :token");
|
query.setParameter("token", token);
|
|
query.executeUpdate();
|
}
|
|
@Override
|
public List<TypeFichierDataTmp> getByToken(String token) {
|
// WITH NumberedValues AS (
|
// SELECT
|
// colonne,
|
// datum,
|
// ROW_NUMBER() OVER (PARTITION BY colonne ORDER BY numeroligne) AS row_num
|
// FROM
|
// typefichierdatatmp
|
// where token='dd6ed96a-0bfc-38ce-bdf4-5d9716325f4a'
|
//)
|
//SELECT
|
// MAX(CASE WHEN colonne = 'niu' THEN datum END) AS NIU,
|
// MAX(CASE WHEN colonne = 'codecentre' THEN datum END) AS codecentre,
|
// MAX(CASE WHEN colonne = 'codecm' THEN datum END) AS codecm,
|
// MAX(CASE WHEN colonne = 'regime' THEN datum END) AS regime,
|
// MAX(CASE WHEN colonne = 'registre_commerce' THEN datum END) AS registre_commerce,
|
// MAX(CASE WHEN colonne = 'raisoc' THEN datum END) AS raisoc,
|
// MAX(CASE WHEN colonne = 'nom_etablisement' THEN datum END) AS nom_etablisement
|
//
|
//FROM
|
// NumberedValues
|
//GROUP BY
|
// row_num
|
//ORDER BY
|
// row_num;
|
|
Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token");
|
query.setParameter("token", token);
|
|
return query.getResultList();
|
}
|
|
@Override
|
public List<TypeFichierDataTmp> getByTokenAndByNumeroLigne(String token, int minNumeroLigne, int maxNumeroLigne) {
|
Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token AND t.numeroLigne >= : minNumeroLigne AND t.numeroLigne < :maxNumeroLigne");
|
query.setParameter("token", token);
|
query.setParameter("minNumeroLigne", minNumeroLigne);
|
query.setParameter("maxNumeroLigne", maxNumeroLigne);
|
|
return query.getResultList();
|
}
|
|
@Override
|
public boolean hasBatchAlreadyBeenReceived(String token, int batchNumber) {
|
|
Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token and t.batchNumber = :batchNumber");
|
query.setParameter("token", token);
|
query.setParameter("batchNumber", batchNumber);
|
|
return query.getResultList().stream().findFirst().isPresent();
|
}
|
|
@Override
|
public List<LinkedHashMap<String, Object>> getByStructuredLines(String token, List<ColumnDefinition> columnsDef) {
|
Collections.sort(columnsDef, (ColumnDefinition c1, ColumnDefinition c2) -> Integer.valueOf(c1.getPosition()).compareTo(c2.getPosition()));
|
List<LinkedHashMap<String, Object>> results = new ArrayList<>();
|
|
String query = constructQuery(token, columnsDef);
|
List<Tuple> tuples = em.createNativeQuery(query, Tuple.class).getResultList();
|
|
tuples.stream().forEach(t -> {
|
LinkedHashMap<String, Object> map = new LinkedHashMap<>();
|
for (int i = 0; i < columnsDef.size(); i++) {
|
map.put(columnsDef.get(i).getName(), t.get(i, String.class));
|
}
|
results.add(map);
|
});
|
return results;
|
}
|
|
private String constructQuery(String token, List<ColumnDefinition> columnsDef) {
|
StringBuilder builder = new StringBuilder("WITH Lines AS ("
|
+ " SELECT colonne, datum, ROW_NUMBER() OVER (PARTITION BY colonne ORDER BY numeroligne) AS row_num"
|
+ " FROM typefichierdatatmp"
|
+ " WHERE token='" + token + "'"
|
+ ") SELECT ");
|
for (ColumnDefinition c : columnsDef) {
|
builder.append(" MAX(CASE WHEN colonne = '").append(c.getName()).append("' THEN datum END) AS ").append(c.getName()).append(",");
|
}
|
builder.replace(builder.length() - 1, builder.length(), "");
|
builder.append(" FROM Lines GROUP BY row_num ORDER BY row_num");
|
|
return builder.toString();
|
}
|
}
|
