IntegrationMappingSearchRepositoryImpl.java
package com.tradecloud.repository.base.impl;
import com.tradecloud.dto.base.IntegrationMappingDto;
import com.tradecloud.dto.base.IntegrationMappingSearch;
import com.tradecloud.repository.base.IntegrationMappingSearchRepository;
import org.hibernate.Query;
import org.hibernate.transform.ResultTransformer;
import org.springframework.stereotype.Repository;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IntelliJ IDEA.
* User: ds
* Date: 2014/07/29
* Time: 2:56 PM
* To change this template use File | Settings | File Templates.
*/
@Repository(value = "integrationMappingSearchRepository")
public class IntegrationMappingSearchRepositoryImpl extends RepositoryBaseImpl<IntegrationMappingDto, IntegrationMappingSearch>
implements IntegrationMappingSearchRepository {
@Override
public List<IntegrationMappingDto> integrationMappingsSearch(IntegrationMappingSearch search) {
return getDtoList(search);
}
private List<IntegrationMappingDto> getDtoList(IntegrationMappingSearch search) {
List<IntegrationMappingDto> dtoList = new ArrayList<>();
// Static integrated reference key
for (String s : staticClassNames()) {
Query query = createTransformationQuery(createStaticSQLCommand(s, search), s);
for (Object o : query.list()) {
dtoList.add((IntegrationMappingDto) o);
}
}
// integrated Reference key, using ID as primary key
for (String s : classNamesUseID()) {
Query query = createTransformationQuery(createSQLCommand(s, search), s);
for (Object o : query.list()) {
dtoList.add((IntegrationMappingDto) o);
}
}
// integrated Reference key, using code as primary key
for (String s : classNamesUseCode()) {
Query query = createTransformationQuery(createSQLCommandUseCodePrimarykey(s, search), s);
for (Object o : query.list()) {
dtoList.add((IntegrationMappingDto) o);
}
}
//integrated Reference key Person
for (String s : classNamesPerson()) {
Query query = createPersonTransformationQuery(createSQLCommandPerson(s, search), s);
for (Object o : query.list()) {
dtoList.add((IntegrationMappingDto) o);
}
}
// integrated Reference key, OrganisationaUnitSupplier
for (String s : classOrgNames()) {
Query query = createTransformationQuery(createOrgUnitSupSQLCommand(s, search), s);
for (Object o : query.list()) {
dtoList.add((IntegrationMappingDto) o);
}
}
return dtoList;
}
@Override
public long integrationMappingsSearchCount(IntegrationMappingSearch search) {
return 0; //To change body of implemented methods use File | Settings | File Templates.
}
private String createStaticSQLCommand(String entity, IntegrationMappingSearch search) {
String sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.name "
+ " FROM " + entity + " as fd "
+ " JOIN externalreferencewrapper erw on fd.externalreferencewrapper_id = erw.id "
+ " JOIN externalreferencewrapper_externalreference ee on erw.id = ee.externalreferencewrapper_id "
+ " JOIN externalreference ex on ee.externalreferences_id = ex.id "
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id "
+ " WHERE i.id = " + search.getId()
+ " AND fd.externalreferencewrapper_id is not null "
+ " AND ex.integratedsystem_id = " + search.getId();
// + " GROUP BY ex.referencevalue, i.code,fd.name";
return sqlQuery;
}
private String createSQLCommand(String entity, IntegrationMappingSearch search) {
//Other tables
String sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.name "
+ " FROM " + entity + " as fd "
+ " JOIN " + entity + "_externalreference erw on fd.id = erw." + entity + "_id "
+ " JOIN externalreference ex on ex.id = erw.externalreferences_id "
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id "
+ " WHERE i.id = " + search.getId()
+ " AND ex.integratedsystem_id = " + search.getId();
// + " GROUP BY ex.referencevalue, i.code,fd.name";
return sqlQuery;
}
private String createSQLCommandUseCodePrimarykey(String entity, IntegrationMappingSearch search) {
//Other tables
String sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.name "
+ " FROM " + entity + " as fd "
+ " JOIN " + entity + "_externalreference erw on fd.code = erw." + entity + "_code "
+ " JOIN externalreference ex on ex.id = erw.externalreferences_id "
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id "
+ " WHERE i.id = " + search.getId()
+ " AND ex.integratedsystem_id = " + search.getId();
// + " GROUP BY ex.referencevalue, i.code,fd.name";
return sqlQuery;
}
private String createOrgUnitSupSQLCommand(String entity, IntegrationMappingSearch search) {
//Organisational Unit Supplier tables
String sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.name"
+ " FROM " + entity + " as fd "
+ " JOIN organisationalunitsupplier ous on fd.id = ous." + entity + "_id"
+ " JOIN organisationalunitsupplier_externalreference erw on ous.id = erw.organisationalunitsupplier_id"
+ " JOIN externalreference ex on ex.id = erw.externalreferences_id"
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id"
+ " WHERE i.id = " + search.getId()
+ " AND ex.integratedsystem_id = " + search.getId();
// + " GROUP BY ex.referencevalue, i.code,fd.name";
return sqlQuery;
}
private Query createTransformationQuery(String sqlQuery, final String entity) {
Query query = getSession().createSQLQuery(sqlQuery);
query.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
IntegrationMappingDto result = new IntegrationMappingDto();
result.setReferenceKey("" + objects[0]);
result.setIntegrationSystem("" + objects[1]);
result.setName("" + objects[2]);
result.setType(entity);
return result; //To change body of implemented methods use File | Settings | File Templates.
}
@Override
public List transformList(List list) {
return list; //To change body of implemented methods use File | Settings | File Templates.
}
});
//--------------------------------------------
return query;
}
private List<String> staticClassNames() {
List<String> staticClassNames = new ArrayList<String>();
staticClassNames.add("City");
//staticClassNames.add("Colour");
staticClassNames.add("ContainerType");
staticClassNames.add("Country");
staticClassNames.add("Currency");
staticClassNames.add("Depot");
staticClassNames.add("PaymentBasis");
staticClassNames.add("FinalDestination");
staticClassNames.add("Icon");
staticClassNames.add("Incoterm");
staticClassNames.add("ItemCategory");
staticClassNames.add("NamedPlace");
staticClassNames.add("PackageType");
staticClassNames.add("PaymentMethod");
staticClassNames.add("PaymentTerm");
staticClassNames.add("PlaceOfDischarge");
staticClassNames.add("PlaceOfLoading");
staticClassNames.add("Region");
staticClassNames.add("UnitType");
staticClassNames.add("VolumeUnitOfMeasure");
staticClassNames.add("WeightUnitOfMeasure");
return staticClassNames;
}
private List<String> classNamesUseID() {
List<String> classNames = new ArrayList<String>();
//classNames.add("OrganisationalUnitAgent");
classNames.add("Agent");
classNames.add("Supplier");
classNames.add("IntegratedSupplier");
classNames.add("Bank");
classNames.add("Consignee");
classNames.add("SaleCustomer");
classNames.add("ServiceProvider");
classNames.add("OrganisationalUnit");
return classNames;
}
private List<String> classNamesUseCode() {
List<String> classNames = new ArrayList<String>();
classNames.add("AgentCommissionBasedOn");
classNames.add("Airport");
// classNames.add("Colour");
classNames.add("ContainerType");
classNames.add("CostLineTemplate");
classNames.add("Country");
classNames.add("Currency");
classNames.add("EmployeeRole");
classNames.add("PackageType");
classNames.add("PaymentBasis");
classNames.add("RailTerminal");
classNames.add("SaleCustomerType");
classNames.add("UnitType");
classNames.add("VolumeUnitOfMeasure");
classNames.add("WeightUnitOfMeasure");
classNames.add("PlaceOfExpiry");
return classNames;
}
private List<String> classNamesPerson() {
List<String> classNames = new ArrayList<String>();
classNames.add("Employee");
classNames.add("Contact");
return classNames;
}
private List<String> classOrgNames() {
List<String> classNames = new ArrayList<String>();
classNames.add("Supplier");
return classNames;
}
private String createSQLCommandPerson(String entity, IntegrationMappingSearch search) {
String sqlQuery = "";
if (entity.equals("Employee")) {
sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.firstName, fd.lastName, eer.employee_role"
+ " FROM " + entity + " as fd "
+ " JOIN employee_employeerole eer on fd.id = eer." + entity + "_id"
+ " JOIN " + entity + "_externalreference erw on fd.id = erw." + entity + "_id "
+ " JOIN externalreference ex on ex.id = erw.externalreferences_id "
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id "
+ " WHERE i.id = " + search.getId()
+ " AND ex.integratedsystem_id = " + search.getId();
} else {
sqlQuery = "SELECT DISTINCT ex.referencevalue, i.code, fd.firstName, fd.lastName"
+ " FROM " + entity + " as fd "
+ " JOIN " + entity + "_externalreference erw on fd.id = erw." + entity + "_id "
+ " JOIN externalreference ex on ex.id = erw.externalreferences_id "
+ " JOIN integratedsystem i on ex.integratedsystem_id = i.id "
+ " WHERE i.id = " + search.getId()
+ " AND ex.integratedsystem_id = " + search.getId();
}
return sqlQuery;
}
private Query createPersonTransformationQuery(String sqlQuery, final String entity) {
Query query = getSession().createSQLQuery(sqlQuery);
if (entity.equals("Employee")) {
query.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
IntegrationMappingDto result = new IntegrationMappingDto();
result.setReferenceKey("" + objects[0]);
result.setIntegrationSystem("" + objects[1]);
result.setName("" + objects[3] + ", " + objects[2]);
result.setType("" + objects[4]);
return result; //To change body of implemented methods use File | Settings | File Templates.
}
@Override
public List transformList(List list) {
return list; //To change body of implemented methods use File | Settings | File Templates.
}
});
} else {
query.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
IntegrationMappingDto result = new IntegrationMappingDto();
result.setReferenceKey("" + objects[0]);
result.setIntegrationSystem("" + objects[1]);
result.setName("" + objects[2]);
result.setType(entity);
return result; //To change body of implemented methods use File | Settings | File Templates.
}
@Override
public List transformList(List list) {
return list; //To change body of implemented methods use File | Settings | File Templates.
}
});
}
return query;
}
}