package qa.qcri.aidr.manager.repository.impl;
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.net.URLDecoder;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
//import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.LogicalExpression;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import qa.qcri.aidr.common.values.UsageType;
import qa.qcri.aidr.manager.dto.CollectionSummaryInfo;
import qa.qcri.aidr.manager.persistence.entities.Collection;
import qa.qcri.aidr.manager.persistence.entities.UserAccount;
import qa.qcri.aidr.manager.repository.CollectionRepository;
import qa.qcri.aidr.manager.service.CollectionLogService;
import qa.qcri.aidr.manager.service.TaggerService;
import qa.qcri.aidr.manager.util.CollectionStatus;
import qa.qcri.aidr.manager.util.CollectionType;
@Repository("collectionRepository")
@Transactional
public class CollectionRepositoryImpl extends GenericRepositoryImpl<Collection, Serializable> implements CollectionRepository{
private final Logger logger = Logger.getLogger(CollectionRepositoryImpl.class);
@Autowired
private CollectionLogService collectionLogService;
@Autowired
private TaggerService taggerService;
@SuppressWarnings("unchecked")
@Override
public List<Collection> searchByName(String query, Long userId) throws Exception {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.ilike("name", query, MatchMode.ANYWHERE));
criteria.add(Restrictions.eq("owner.id", userId));
return criteria.list();
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> getPaginatedDataForPublic(final Integer start, final Integer limit, final Enum statusValue) {
// Workaround as criteria query gets result for different managers and in the end we get less then limit records.
List<BigInteger> collectionIds = (List<BigInteger>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = " SELECT DISTINCT c.id FROM collection c" +
" WHERE (c.publicly_listed = 1 and c.status = :statusValue) " +
" order by c.start_date DESC, c.created_at DESC LIMIT :start, :limit ";
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setParameter("start", start);
sqlQuery.setParameter("limit", limit);
sqlQuery.setParameter("statusValue", statusValue.ordinal());
List<BigInteger> ids = sqlQuery.list();
return ids != null ? ids : Collections.emptyList();
}
});
List<Collection> a = new ArrayList<Collection>();
for(int i =0; i < collectionIds.size(); i++){
Collection collection = this.findById(collectionIds.get(i).longValue());
a.add(collection) ;
}
return a;
}
@Override
public Integer getPublicCollectionsCount(final Enum statusValue) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = " SELECT count(distinct c.id) FROM collection c" +
" WHERE (c.publicly_listed = 1 and c.status = :statusValue) " ;
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setParameter("statusValue", statusValue.ordinal());
BigInteger total = (BigInteger) sqlQuery.uniqueResult();
return total != null ? total.intValue() : 0;
}
});
}
@Override
public Long getTotalCollectionsCount() {
return (Long) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = " SELECT count(distinct c.id) FROM collection c";
SQLQuery sqlQuery = session.createSQLQuery(sql);
BigInteger total = (BigInteger) sqlQuery.uniqueResult();
return total != null ? total.longValue() : 0;
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> getPaginatedData(final Integer start, final Integer limit, final UserAccount user, final boolean onlyTrashed) {
final Long userId = user.getId();
final String conditionTrashed;
if (onlyTrashed) {
conditionTrashed = "=";
} else {
conditionTrashed = "!=";
}
List<Object[]> collections = (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = " SELECT DISTINCT c.id, c.status FROM collection c " +
" LEFT OUTER JOIN collection_collaborator c_m " +
" ON c.id = c_m.collection_id " +
" WHERE ((c.owner_id =:userId OR c_m.account_id = :userId) AND c.status " + conditionTrashed + " :statusValue) " +
" ORDER BY Case c.status When :status1 Then 1 When :status2 Then 1 Else 3 End, " +
" Case c.start_date When null Then 1 Else c.start_date*-1 End " +
" LIMIT :start, :limit ";
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setParameter("userId", userId);
sqlQuery.setParameter("start", start);
sqlQuery.setParameter("limit", limit);
sqlQuery.setParameter("status1", CollectionStatus.RUNNING.ordinal());
sqlQuery.setParameter("status2", CollectionStatus.RUNNING_WARNING.ordinal());
sqlQuery.setParameter("statusValue", CollectionStatus.TRASHED.ordinal());
List<Object[]> ids = sqlQuery.list();
return ids != null ? ids : Collections.emptyList();
}
});
//MEGHNA: To prevent multiple db calls, we get collection id and status from db and update AidrCollection status
List<Collection> result = new ArrayList<Collection>();
BigInteger id;
for(Object[] col : collections)
{
id = (BigInteger)col[0];
Collection collection = this.findById(id.longValue());
collection.setStatus(CollectionStatus.values()[(Integer)col[1]]);
result.add(collection) ;
}
return result;
}
@Override
public Integer getCollectionsCount(final UserAccount user, final boolean onlyTrashed) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
Long userId = user.getId();
final String conditionTrashed;
if (onlyTrashed) {
conditionTrashed = "=";
} else {
conditionTrashed = "!=";
}
String sql = " select count(distinct c.id) " +
" FROM collection c " +
" LEFT OUTER JOIN collection_collaborator c_m " +
" ON c.id = c_m.collection_id " +
" WHERE (c.status " + conditionTrashed + " :statusValue and (c.owner_id = :userId or c_m.account_id = :userId)) ";
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setParameter("userId", userId);
sqlQuery.setParameter("statusValue", CollectionStatus.TRASHED.ordinal());
BigInteger total = (BigInteger) sqlQuery.uniqueResult();
return total != null ? total.intValue() : 0;
}
});
}
@Override
public Boolean exist(String code) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("code", code));
criteria.add(Restrictions.ne("status", CollectionStatus.TRASHED));
Collection collection = (Collection) criteria.uniqueResult();
return collection != null;
}
@Override
public Boolean existName(String name) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("name", name));
Collection collection = (Collection) criteria.uniqueResult();
return collection != null;
}
@Override
public Collection getRunningCollectionStatusByUser(Long userId) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
//criteria.add(Restrictions.eq("user.id", userId));
//criteria.add(Restrictions.eq("status", CollectionStatus.RUNNING));
LogicalExpression or = Restrictions.or(
Restrictions.eq("status", CollectionStatus.RUNNING),
Restrictions.eq("status", CollectionStatus.RUNNING_WARNING)
);
LogicalExpression orAll = Restrictions.or(
or,
Restrictions.eq("status", CollectionStatus.WARNING)
);
/*Is this check needed?
*
* LogicalExpression and = Restrictions.and(
orAll,
Restrictions.ne("status", CollectionStatus.TRASHED)
);*/
LogicalExpression andAll = Restrictions.and(
orAll,
Restrictions.eq("owner.id", userId)
);
criteria.add(andAll);
//criteria.add(Restrictions.ne("status", CollectionStatus.TRASHED));
return (Collection) criteria.uniqueResult();
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> getAllCollectionByUser(Long userId) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("owner.id", userId));
return criteria.list();
}
@Override
public List<Collection> getRunningCollections() {
return getRunningCollections(null, null, null, null, null);
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> getRunningCollections(Integer start, Integer limit, String terms, String sortColumn, String sortDirection) {
Criteria criteriaIds = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteriaIds.setProjection(Projections.projectionList()
.add(Projections.property("id"), "id"));
LogicalExpression or = Restrictions.or(
Restrictions.eq("status", CollectionStatus.RUNNING),
Restrictions.eq("status", CollectionStatus.RUNNING_WARNING)
);
LogicalExpression or2 = Restrictions.or(
or,
Restrictions.eq("status", CollectionStatus.INITIALIZING)
);
LogicalExpression orAll = Restrictions.or(
or2,
Restrictions.eq("status", CollectionStatus.WARNING)
);
LogicalExpression andAll = Restrictions.and(
orAll,
Restrictions.ne("status", CollectionStatus.TRASHED)
);
criteriaIds.add(andAll);
addCollectionSearchCriteria(terms, criteriaIds);
searchCollectionsAddOrder(sortColumn, sortDirection, criteriaIds);
if (start != null) {
criteriaIds.setFirstResult(start);
}
if (limit != null) {
criteriaIds.setMaxResults(limit);
}
List<Integer> ids = criteriaIds.list();
if (ids.size() == 0){
return Collections.emptyList();
}
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.add(Restrictions.in("id", ids));
searchCollectionsAddOrder(sortColumn, sortDirection, criteria);
return criteria.list();
}
@Override
public Long getRunningCollectionsCount(String terms) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.setProjection(Projections.projectionList()
.add(Projections.property("id"), "id"));
LogicalExpression or = Restrictions.or(
Restrictions.eq("status", CollectionStatus.RUNNING),
Restrictions.eq("status", CollectionStatus.RUNNING_WARNING)
);
LogicalExpression or2 = Restrictions.or(
or,
Restrictions.eq("status", CollectionStatus.INITIALIZING)
);
LogicalExpression orAll = Restrictions.or(
or2,
Restrictions.eq("status", CollectionStatus.WARNING)
);
LogicalExpression andAll = Restrictions.and(
orAll,
Restrictions.ne("status", CollectionStatus.TRASHED)
);
criteria.add(andAll);
addCollectionSearchCriteria(terms, criteria);
ScrollableResults scroll = criteria.scroll();
int i = scroll.last() ? scroll.getRowNumber() + 1 : 0;
return Long.valueOf(i);
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> getStoppedCollections(Integer start, Integer limit, String terms, String sortColumn, String sortDirection) {
Criteria criteriaIds = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteriaIds.setProjection(Projections.projectionList()
.add(Projections.property("id"), "id"));
LogicalExpression or = Restrictions.or(
Restrictions.eq("status", CollectionStatus.STOPPED),
Restrictions.eq("status", CollectionStatus.NOT_RUNNING)
);
LogicalExpression orAll = Restrictions.or(
or,
Restrictions.eq("status", CollectionStatus.FATAL_ERROR)
);
criteriaIds.add(orAll);
addCollectionSearchCriteria(terms, criteriaIds);
searchCollectionsAddOrder(sortColumn, sortDirection, criteriaIds);
if (start != null) {
criteriaIds.setFirstResult(start);
}
if (limit != null) {
criteriaIds.setMaxResults(limit);
}
List<Integer> ids = criteriaIds.list();
if (ids.size() == 0){
return Collections.emptyList();
}
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.add(Restrictions.in("id", ids));
searchCollectionsAddOrder(sortColumn, sortDirection, criteria);
return criteria.list();
}
@Override
public Long getStoppedCollectionsCount(String terms) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.setProjection(Projections.projectionList()
.add(Projections.property("id"), "id"));
LogicalExpression or = Restrictions.or(
Restrictions.eq("status", CollectionStatus.STOPPED),
Restrictions.eq("status", CollectionStatus.NOT_RUNNING)
);
LogicalExpression orAll = Restrictions.or(
or,
Restrictions.eq("status", CollectionStatus.FATAL_ERROR)
);
criteria.add(orAll);
addCollectionSearchCriteria(terms, criteria);
ScrollableResults scroll = criteria.scroll();
int i = scroll.last() ? scroll.getRowNumber() + 1 : 0;
return Long.valueOf(i);
}
private void addCollectionSearchCriteria(String terms, Criteria criteria) {
if (StringUtils.hasText(terms)){
String wildcard ='%'+ URLDecoder.decode(terms.trim())+'%';
LogicalExpression orNameCode = Restrictions.or(
Restrictions.ilike("name", wildcard),
Restrictions.ilike("code", wildcard)
);
LogicalExpression orAll = Restrictions.or(
orNameCode,
Restrictions.ilike("track", wildcard)
);
criteria.add(orAll);
}
}
private void searchCollectionsAddOrder(String sortColumn, String sortDirection, Criteria criteria) {
if (StringUtils.hasText(sortColumn)) {
if ("owner".equals(sortColumn)){
sortColumn = "owner.userName";
criteria.createAlias("owner", "owner");
}
Order order;
if ("ASC".equals(sortDirection)){
order = Order.asc(sortColumn);
} else {
order = Order.desc(sortColumn);
}
criteria.addOrder(order);
}
}
@Override
public Collection getInitializingCollectionStatusByUser(Long userId) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("owner.id", userId));
criteria.add(Restrictions.eq("status", CollectionStatus.INITIALIZING));
return (Collection) criteria.uniqueResult();
}
@Override
public Collection start(Long collectionId) {
Collection collection = this.findById(collectionId);
Calendar now = Calendar.getInstance();
collection.setStartDate(now.getTime());
// collection.setEndDate(null);
collection.setStatus(CollectionStatus.RUNNING);
this.update(collection);
return collection;
}
@Override
public Collection stop(Long collectionId) {
Collection collection = this.findById(collectionId);
Calendar now = Calendar.getInstance();
collection.setEndDate(now.getTime());
collection.setStatus(CollectionStatus.STOPPED);
this.update(collection);
return collection;
}
@Override
public Collection findByCode(String code) {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("code", code));
try {
return (Collection) criteria.uniqueResult();
} catch (HibernateException e) {
logger.error("Hibernate exception while finding a collection by code: "+code + "/t"+e.getStackTrace());
return null;
}
}
@Override
public Collection trashCollectionById(Long collectionId) {
Collection collection = stop(collectionId);
collection.setStatus(CollectionStatus.TRASHED);
this.update(collection);
return collection;
}
@Override
public void update(Collection collection) {
collection.setUpdatedAt(new Timestamp(System.currentTimeMillis()));
super.update(collection);
}
@Override
public void save(Collection collection) {
Timestamp now = new Timestamp(System.currentTimeMillis());
collection.setUpdatedAt(now);
collection.setCreatedAt(now);
super.save(collection);
}
@Override
public List<Collection> getAllCollectionsByUsage(UsageType usageType) {
List<Collection> collections = new ArrayList<Collection>();
try {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("usageType", usageType));
collections = criteria.list();
} catch (HibernateException e) {
logger.error("Exception in fetching list of collections.", e);
}
return collections;
}
@SuppressWarnings("unchecked")
@Override
public List<CollectionSummaryInfo> getAllCollectionForAidrData() {
List<CollectionSummaryInfo> listOfCollectionSummaryInfos = new ArrayList<>();
Map<Long, Long> humanTagCountMap = new HashMap<>();
List<Object[]> humangTagCounts = (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = "SELECT c.id, COALESCE(count(1),0) as humantagcount"
+" FROM collection c, document d, document_nominal_label dnl"
+" WHERE d.documentID = dnl.documentID AND d.crisisID = c.id"
+" group by c.id;";
SQLQuery sqlQuery = session.createSQLQuery(sql);
List<Object[]> data = sqlQuery.list();
return data != null ? data : Collections.emptyList();
}
});
for (Object[] objects : humangTagCounts) {
Long collectionId = ((BigInteger) objects[0]).longValue();
Long humanTagCount = ((BigInteger) objects[1]).longValue();
humanTagCountMap.put(collectionId, humanTagCount);
}
List<Object[]> collections = (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = "SELECT"
+" c.id, c.code, c.name, c.start_date, c.end_date, c.created_at, c.count, c.status, c.lang_filters, c.track, c.geo, c.publicly_listed, c.provider,"
+" SUM(aa.classify) AS machinetagcount,"
+" account.user_name,"
+" ct.name as crisis_type"
+" FROM"
+" account,"
+" collection c"
+" LEFT JOIN"
+" (SELECT "
+" mnl.classifiedDocumentCount AS classify, mf.crisisID AS dd"
+" FROM"
+" model_family mf, model m, model_nominal_label mnl"
+" WHERE"
+" mf.modelFamilyID = m.modelFamilyID"
+" AND m.modelID = mnl.modelID) AS aa ON c.id = aa.dd,"
+" collection c2 LEFT JOIN crisis_type ct ON c2.crisis_type = ct.id"
+" WHERE"
+" account.id = c.owner_id"
+" and c2.id = c.id"
+" GROUP BY c.id;";
SQLQuery sqlQuery = session.createSQLQuery(sql);
List<Object[]> data = sqlQuery.list();
return data != null ? data : Collections.emptyList();
}
});
CollectionStatus[] collectionStatus = CollectionStatus.values();
for (Object[] objects : collections) {
CollectionSummaryInfo collectionSummaryInfo = new CollectionSummaryInfo();
Long collectionId = ((BigInteger) objects[0]).longValue();
collectionSummaryInfo.setCode((String) objects[1]);
collectionSummaryInfo.setName((String) objects[2]);
collectionSummaryInfo.setStartDate((Date) objects[3]);
collectionSummaryInfo.setEndDate((Date) objects[4]);
collectionSummaryInfo.setCollectionCreationDate((Date) objects[5]);
try {
collectionSummaryInfo.setTotalCount(collectionLogService.countTotalDownloadedItemsForCollection(collectionId));
} catch (Exception e) {
logger.warn("Error in fetch count from collection log.", e);
collectionSummaryInfo.setTotalCount((Integer) objects[6]);
}
collectionSummaryInfo.setStatus(collectionStatus[(Integer) objects[7]].getStatus());
collectionSummaryInfo.setLanguage((String) objects[8]);
collectionSummaryInfo.setKeywords((String) objects[9]);
collectionSummaryInfo.setGeo((String) objects[10]);
collectionSummaryInfo.setLabelCount(taggerService.getLabelCount(collectionId));
collectionSummaryInfo.setPubliclyListed((Boolean) objects[11]);
collectionSummaryInfo.setProvider((String) objects[12]);
if(objects[13] == null) objects[13] = new BigDecimal(0);
collectionSummaryInfo.setMachineTagCount(((BigDecimal) objects[13]).longValue());
collectionSummaryInfo.setCurator((String) objects[14]);
collectionSummaryInfo.setCrisisType((String) objects[15]);
Long humanTagCount = humanTagCountMap.get(collectionId) != null ? humanTagCountMap.get(collectionId) : 0;
collectionSummaryInfo.setHumanTaggedCount(humanTagCount);
listOfCollectionSummaryInfos.add(collectionSummaryInfo);
}
return listOfCollectionSummaryInfos;
}
@SuppressWarnings("unchecked")
@Override
public List<Collection> findMicromappersFilteredCollections(boolean micromappersEnabled) {
List<Collection> collections = new ArrayList<Collection>();
try {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.eq("micromappersEnabled", micromappersEnabled));
criteria.add(Restrictions.eq("provider", CollectionType.Twitter));
collections = criteria.list();
} catch (HibernateException e) {
logger.error("Exception in fetching list of collections.", e);
}
return collections;
}
@Override
public List<String> getEligibleFacebookCollectionsToReRun() {
List<String> collectionCodes = new ArrayList<String>();
@SuppressWarnings("unchecked")
List<Object[]> collections = (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException {
String sql = " SELECT c.code FROM collection c " +
" WHERE c.provider = 'Facebook' AND (c.status = 0 OR c.status = 2 OR c.status = 5 OR c.status = 8) "
+ "AND date_add(c.last_execution_time, interval c.fetch_interval hour) <= now()";
SQLQuery sqlQuery = session.createSQLQuery(sql);
List<Object[]> codes = sqlQuery.list();
return codes != null ? codes : Collections.emptyList();
}
});
if(collections != null && collections.size() > 0) {
for(Object col : collections) {
collectionCodes.add((String) col);
}
}
return collectionCodes;
}
@Override
public List<Collection> getUnexpectedlyStoppedCollections(Date today) {
List<Collection> collections = new ArrayList<Collection>();
try {
Criteria criteria = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Collection.class);
criteria.add(Restrictions.gt("updatedAt", today))
.add(Restrictions.geProperty("startDate", "endDate"));
collections = criteria.list();
} catch (HibernateException e) {
logger.error("Exception in fetching list of collections.", e);
}
return collections;
}
}