imp queries PromotionCategorydao

package com.meijer.foodlab.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.meijer.foodlab.dao.IPromotionCategoryDao;
import com.meijer.foodlab.model.domain.PromotionCategory;
import com.meijer.foodlab.model.domain.PromotionCategoryList;

/**
* @author dbusch
*/
public class PromotionCategoryDaoImpl extends BaseSpringDao implements IPromotionCategoryDao {
private static final Logger LOG = Logger.getLogger(PromotionCategoryDaoImpl.class);

protected JdbcTemplate m_jdbcTemplate;

public PromotionCategoryDaoImpl() {
super(PromotionCategory.class, "PromotionCategory");
}

public PromotionCategoryDaoImpl(Class pClazz, String pEntityName) {
super(pClazz, pEntityName);
}

public PromotionCategory findByRefName(final String pName) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by refName");
}
try {
PromotionCategory lCategory = (PromotionCategory)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from PromotionCategory as this join fetch this.promotions where this.refName = :refName")
.setString("refName", pName)
.uniqueResult();
}
});

if (null != lCategory) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by refName successful");
}
}
return lCategory;

} catch (RuntimeException lRe) {
LOG.error("finding PromotionCategory by refName failed", lRe);
throw lRe;
}

}

public List getActivePromotionsForStore(final Integer pStoreId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store");
}
try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
List lCounts = pSession.createQuery(
"select cat.name, count(distinct promo.promotionId) as qty, cat.image " +
" from PromotionCategory cat" +
" inner join cat.promotions as promo " +
" inner join promo.stores store" +
" where cat.active = true " +
" and promo.special = true" +
" and promo.status = 'ACTIVE'" +
" and store.unitId = :pStoreId" +
" and store.status = 'ACTIVE'" +
" and ( now() between date(promo.activationDate) and date(promo.expirationDate) )" +
" group by cat.name " +
" having count(distinct promo.promotionId) > 0 " +
" order by cat.sortIdx")
.setInteger("pStoreId", pStoreId)
.list();
return lCounts;

}
});
if (null != lResult) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store successful, found: " + lResult.size());
}
}

return lResult;

} catch (RuntimeException lRe) {
LOG.error("finding active promotions by store failed", lRe);
throw lRe;
}
}

/**
* Find the number of promotion map with the category.
* @return int
*/
public int findByPromotionCategoryByIds(final Integer pPromotionId, final Integer pPromotionCategoryId) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategoryByIds for Promotion Id ["+pPromotionId+"] And PromotionCategoryId ["+pPromotionCategoryId+"]");
}
Integer count = new Integer(0);
try {
count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(1) as count from fl_promotion_category_membership this where" +
" this.promotion_category_id = "+pPromotionCategoryId+" and this.promotion_id = "+pPromotionId;

//execute the query for the entity count
Query lCountQuery = pSession.createSQLQuery(lQueryforCount).addScalar("count", Hibernate.INTEGER);

Integer count = (Integer)lCountQuery.uniqueResult();


if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionCategoryByIds successful. Found count ["+(count == null ? 0 : count.intValue())+"]");
}
if(count == null) {
return 0;
} else {
return count.intValue();
}
}
});

return count.intValue();
} catch (RuntimeException e) {
LOG.error("findPromotionCategoryByIds failed", e);
throw e;
}
}

/**
* Find PromotionCategory based on 'name' field value.
*/
public PromotionCategory findByName(final String pName) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by Name ["+(pName == null ? "NA" : pName)+"]");
}
try {
PromotionCategory lCategory = (PromotionCategory)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
List lList = pSession.createQuery(
"select this from PromotionCategory as this where this.name = :pName")
.setString("pName", pName)
.setMaxResults(1)
.list();

if(lList == null || lList.isEmpty()) {
return null;
}
return lList.get(0);
}
});

if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by Name successful");
}
return lCategory;

} catch (RuntimeException lRe) {
LOG.error("finding PromotionCategory by Name failed", lRe);
throw lRe;
}

}

public PromotionCategoryList searchCategory(final String pName, final boolean pActive, final int pOffset, final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pActive){
lStatusCriteria = " where category.active= 1 ";
} else {
lStatusCriteria = " where (category.active=0 OR category.active=1)";
}

String lNameCriteria = "", lName = pName;
if(pName != null) {
if(pName.indexOf("'")!=-1){
lName = pName.replaceAll("'", "_");
}
lNameCriteria = "category.name like '%"+ lName +"%'";
}

String lChainCriteria = "";

//forming queries
String lQueryforCount = "select count(category) from PromotionCategory category" +
lStatusCriteria + (lNameCriteria == "" ? "" : " and ") +
lNameCriteria + (lChainCriteria == "" ? "" : " and ") +
lChainCriteria + " order by category.promotionCategoryId asc" ;

String lQueryForCategory = "select category from PromotionCategory category" +
lStatusCriteria + (lNameCriteria == "" ? "" : " and ") +
lNameCriteria + (lChainCriteria == "" ? "" : " and ") +
lChainCriteria + " order by category.sortIdx asc" ;

Long count = (Long)pSession.createQuery(lQueryforCount).uniqueResult();


List lResult = pSession.createQuery(lQueryForCategory)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.list();

PromotionCategoryList lResults = new PromotionCategoryList();
lResults.setCategories(lResult);
lResults.setMaxResults(pMaxResults);
lResults.setOffset(pOffset);
lResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("searchCategory successfull. found " + lResult.size() + " PromotionCategory");
}

return lResults;
}
});

PromotionCategoryList lPromotionCategories = (PromotionCategoryList)lResult;
return lPromotionCategories;

} catch (RuntimeException e) {
LOG.error("searchCategory failed", e);
throw e;
}
}

/**
* Delete the PromotionCategory and associated membership of the category
* @param pCategoryId
* @return
*/
public Integer deleteCategory(final Integer pCategoryId) {
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting PromotionCategory by CategoryId");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_category_id = " +pCategoryId ,

"delete p from fl_promotion_category p where p.promotion_category_id = " + pCategoryId};


int[] lCounts = m_jdbcTemplate.batchUpdate(lDeleteQueries);

//last result
Integer lResult = lCounts[lCounts.length -1];

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);

if (LOG.isDebugEnabled()) {
LOG.debug("deleted " + lResult + " promotioncategory");
}

return lResult;
}catch (RuntimeException e) {
LOG.error("deleteCategory failed", e);
throw e;
}
}

public List getActiveCategories() {
return getHibernateTemplate().find("from PromotionCategory where active is true order by sortIdx asc");
}

@SuppressWarnings("unchecked")
public List findCategoriesByName(final String pName) {
List lResult = new ArrayList();
try {
lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lNameCriteria = "";
if(pName != null) {
lNameCriteria = " category.name = :pName ";
}

//forming queries
String lQueryForCategory =
"select category from PromotionCategory category " +
"where" + lNameCriteria +
" order by category.promotionCategoryId asc";

Query lQuery = pSession.createQuery(lQueryForCategory);
if(pName != null){
lQuery.setString("pName", pName);
}

List lResult = (List) lQuery.list();

if (LOG.isDebugEnabled()) {
LOG.debug("findCategoriesByNameChain successfull. found " + lResult.size() + " PromotionCategory");
}

return lResult;
}
});

return lResult;

} catch (RuntimeException e) {
LOG.error("findCategoriesByNameChain failed", e);
throw e;
}
}

public int[] updatePromotionCategories(Integer pPromotionId,List pCategoryIds){

try{
String lQueryString="delete cm from fl_promotion_category_membership cm where cm.promotion_id ="+pPromotionId;
m_jdbcTemplate.update(lQueryString);

String[] lDeleteQueries = new String[pCategoryIds.size()];

for(int i=0;i lDeleteQueries[i]= "insert into fl_promotion_category_membership values ("+ pCategoryIds.get(i)+","+pPromotionId+")";
}

int[] upadtes=m_jdbcTemplate.batchUpdate(lDeleteQueries);

return upadtes;
}catch(RuntimeException ex){
LOG.error("error while updating the Promotion Categories :"+ex.getMessage());
throw ex;
}
}

public List getPromotionCategoriesWithPromotionId(final Integer pPomotionIds){

try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryForCategory ="select promotion_category_id from fl_promotion_category_membership where promotion_id ="+pPomotionIds;

Query lQuery = pSession.createSQLQuery(lQueryForCategory);
return lQuery.list();
}
});
return lResult;
}catch(Exception ex){
LOG.error("error while getting the Promotion CategoryIds :"+ex.getMessage());
}
return null;
}

public List getActivePromotionsForStoreIds(final List storeIds,final String promotionType){
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store");
}
try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lSelectClause="select cat, count(distinct promo.promotionId) as qty, cat.image " +
" from PromotionCategory cat" +
" inner join cat.promotions as promo " +
" inner join promo.stores store" ;

String lWhereClause=" where cat.active = true " +
" and promo.status = 'ACTIVE'" +
" and store.status = 'ACTIVE'" +
" and ( now() between date(promo.activationDate) and date(promo.expirationDate))" +
" and store.unitId in (:ids) " ;

if (null!=promotionType){
if ("SPECIAL".equals(promotionType))
lWhereClause+=" and promo.special = true";
else if("COUPON".equals(promotionType))
lWhereClause+=" and promo.special = false";
}

lWhereClause+=" group by cat.name " +
" having count(distinct promo.promotionId) > 0 " +
" order by cat.sortIdx";

List lCounts = pSession.createQuery(lSelectClause+lWhereClause)
.setParameterList("ids", storeIds)
.list();
return lCounts;

}
});

if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store ids successful, found: " + lResult.size());
}
return lResult;
} catch (RuntimeException lRe) {
LOG.error("finding active promotions by store failed", lRe);
throw lRe;
}
}

public JdbcTemplate getJdbcTemplate() {
return this.m_jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate pJdbcTemplate) {
this.m_jdbcTemplate = pJdbcTemplate;
}


}