imp Queris in Promotion dao

package com.meijer.foodlab.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import com.meijer.foodlab.dao.IPromotionDao;
import com.meijer.foodlab.model.domain.Coupon;
import com.meijer.foodlab.model.domain.CouponListResult;
import com.meijer.foodlab.model.domain.IngredientItemPromotion;
import com.meijer.foodlab.model.domain.Promotion;
import com.meijer.foodlab.model.domain.PromotionCategory;
import com.meijer.foodlab.model.domain.PromotionList;
import com.meijer.foodlab.model.domain.Special;
import com.meijer.foodlab.model.domain.UpcPromotion;

/**
* @author kiranreddy
*
*/
public class PromotionDao extends BaseSpringDao implements IPromotionDao {
private static final Logger LOG = Logger.getLogger(PromotionDao.class);

protected JdbcTemplate m_jdbcTemplate;

public PromotionDao() {
super(Promotion.class, "Promotion");
}

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

public List findByIds(final List pPromotionIds) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
try {
List lPromotion = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Promotion as this where this.promotionId in (:pIds)")
.setParameterList("pIds", pPromotionIds)
.list();
}
});

if (null != lPromotion) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
}
return lPromotion;

} catch (RuntimeException lRe) {
LOG.error("finding Promotions by Ids", lRe);
throw lRe;
}

}

public List findSpecialsByStoreId( final Integer pStoreId){
List lList = new ArrayList();

if(pStoreId == null){
lList = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery("select this from Promotion this left join this.stores store" +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=true" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate))" +
" order by this.promotionId")
.list();
}
});
}else{
lList = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery("select this from Promotion this left join this.stores store" +
" where (store.storeId = :pStoreId or this.store.storeId is NULL) and this.status='ACTIVE' and this.special=true" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate))" +
" order by this.promotionId")
.setInteger("pStoreId", pStoreId)
.list();
}
});
}
return lList;
}


public CouponListResult findCouponsByStoreId( final Integer pChainId, final Integer pStoreId,final Integer pOffset,final Integer pMaxResults){
CouponListResult lList = new CouponListResult();

String join = " left join this.brand br ";
String where = " and (br.chain.id IS NULL";
if(null != pChainId){
where += " or br.chain.id = "+pChainId+") ";
} else {
where += ")";
}
final String lChainJoinCriteria = join;
final String lChainWhereCriteria = where;

if(pStoreId == null){
lList = (CouponListResult)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long lCount = (Long)pSession.createQuery("select count(distinct this) from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store " + lChainJoinCriteria +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" order by ings.category desc")
.uniqueResult();

List lPromotions = pSession.createQuery("select distinct this,ings.category from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store " + lChainJoinCriteria +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" group by this order by ings.category desc")
.setFirstResult(pOffset.intValue())
.setMaxResults(pMaxResults.intValue())
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lPromotions){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

CouponListResult lList = new CouponListResult();
lList.setList(lPromos);
lList.setMaxResults(pMaxResults);
lList.setOffset(pOffset);
lList.setTotalCount(lCount.intValue());
return lList;
}
});
} else {

lList = (CouponListResult)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long lCount = (Long)pSession.createQuery("select count(distinct this) from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store" + lChainJoinCriteria +
" where (store.unitId = :pStoreId or store.storeId is NULL) and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" order by ings.category")
.setInteger("pStoreId", pStoreId)
.uniqueResult();

List lPromotions = pSession.createQuery("select distinct this,ings.category from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store" + lChainJoinCriteria +
" where (store.unitId = :pStoreId or store.storeId is NULL) and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" group by this order by ings.category desc")
.setInteger("pStoreId", pStoreId)
.setFirstResult(pOffset.intValue())
.setMaxResults(pMaxResults.intValue())
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lPromotions){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

CouponListResult lList = new CouponListResult();
lList.setList(lPromos);
lList.setMaxResults(pMaxResults);
lList.setOffset(pOffset);
lList.setTotalCount(lCount.intValue());
return lList;
}
});
}

return lList;
}

public PromotionList findByCategory(final Integer pCategoryId, final int pOffset, final int pMaxResults) {

if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category id");
}
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long count = (Long)pSession.createQuery(
"select count(promotions) from PromotionCategory this" +
" join fetch this.promotions as promotions" +
" where this.promotionCategoryId = :id" +
" group by this")
.setInteger("id", pCategoryId)
.uniqueResult();

List lResult = (List)pSession.createQuery(
"select p" +
" from PromotionCategory this join fetch this.promotions p" +
" where this.promotionCategoryId = :id")
.setInteger("id", pCategoryId)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());


if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category id successful, found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;

} catch (RuntimeException lRe) {
LOG.error("find promotions by category id failed", lRe);
throw lRe;
}

}

public PromotionList findActivePromotionsByCategory(final Integer pChainId, final String pCategoryName,final Integer pStoreId, final int pOffset, final int pMaxResults) {

if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category name");
}
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String join = " left join store.chain ch ";
String where = " and (ch IS NULL";
if(null != pChainId){
where += " or ch.id = "+pChainId+") ";
} else {
where += ")";
}

Long count = (Long)pSession.createQuery("select count(distinct promo) from PromotionCategory cat " +
" inner join cat.promotions promo left join promo.ingredientPromotions ingpromos left join ingpromos.ingredientItem ingitem left join ingitem.ingredients ings " +
" inner join promo.stores store " + join +
" where store.unitId = :pStoreId and promo.status='ACTIVE'" +
" and cat.name = :pCatName " +
" and cat.active = :pStatus" +
" and ( :date between date(promo.activationDate) and date(promo.expirationDate) ) " + where +
" order by ings.category desc")
.setString("pCatName", pCategoryName)
.setDate("date", new Date())
.setInteger("pStoreId", pStoreId)
.setBoolean("pStatus", true)
.uniqueResult();

List lResult = (List)pSession.createQuery("select promo,ings.category from PromotionCategory cat " +
" inner join cat.promotions promo left join promo.ingredientPromotions ingpromos left join ingpromos.ingredientItem ingitem left join ingitem.ingredients ings " +
" inner join promo.stores store" + join +
" where store.unitId = :pStoreId and promo.status='ACTIVE'" +
" and cat.name = :pCatName " +
" and cat.active = :pStatus" +
" and ( :date between date(promo.activationDate) and date(promo.expirationDate) )" + where +
" group by promo order by ings.category desc")
.setDate("date", new Date())
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setString("pCatName", pCategoryName)
.setInteger("pStoreId", pStoreId)
.setBoolean("pStatus", true)
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lResult){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lPromos);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());


if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category name successful, found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;

} catch (RuntimeException lRe) {
LOG.error("find promotions by category name failed", lRe);
throw lRe;
}
}
/**
* Returns the OneStops on given date range with support to paging.
*
* Finds promotions by date range irrespective of status, used for admin services.
*/
public PromotionList findOneStopsByDateRange(final Date pStateDate,final Date pEndDate,final int pOffSet,final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ( DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or ( DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd)) and promotion.special = true "+
"order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ( DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or ( DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd)) and promotion.special = true group by promotion.promotionId "+
"order by promotion.activationDate";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findOneStopsByDateRange failed", e);
throw e;
}
}

/**
* Returns the promotions on given date range with support to paging.
*
* Finds promotions by date range irrespective of status, used for admin services.
*/
public PromotionList findPromotionsByDateRange(final Date pStateDate,final Date pEndDate,final int pOffSet,final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) "+
"order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
"group by promotion.promotionId "+
"order by promotion.activationDate";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public PromotionList findPendingPromotionsByDateRange(final int pOffSet,final int pMaxResults,final Integer pUnitId) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lBeforeCriteria = "";
String lCriteria = "";
if(null != pUnitId) {
lBeforeCriteria = " left join promotion.stores store ";
lCriteria = " and store.unitId = "+pUnitId.intValue();
}
//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
lBeforeCriteria +
" where promotion.status='PENDING' " +
lCriteria +
" order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
lBeforeCriteria +
" where promotion.status='PENDING' " +
lCriteria +
" group by promotion.promotionId " +
" order by promotion.activationDate";

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

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public List findPromotionByExternalId(final String pExternalId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by ExternalId");
}
try {
List lPromotion = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Promotion as this where this.externalId = :pExternalId")
.setString("pExternalId", pExternalId)
.list();
}
});

if (null != lPromotion) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
}
return lPromotion;

} catch (RuntimeException lRe) {
LOG.error("finding Promotions by Ids", lRe);
throw lRe;
}
}
/**
* Finds promotions by store id irrespective of status and date range, used for admin services.
*/
public PromotionList findPromotionsByStoreId(final Integer pId, final Integer pOffset,final Integer pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(this) from Promotion this left join this.stores store" +
" where (store.unitId = :pStoreId) " +
" order by this.promotionId";

String lQueryForList = "select this from Promotion this left join this.stores store" +
" where (store.unitId = :pStoreId) " +
" order by this.promotionId";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setInteger("pStoreId", pId)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForList)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setInteger("pStoreId", pId)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByStoreId failed", e);
throw e;
}
}

public Integer deletePromotionByBatchId(final Integer pBatchId){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_onestop_promotion_membership cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_upc_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_store_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_ingredient_item_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_shopping_list_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete p from fl_promotion p where p.batch_id = " + pBatchId};


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

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

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);
getHibernateTemplate().getSessionFactory().evict(Promotion.class);
getHibernateTemplate().getSessionFactory().evict(IngredientItemPromotion.class);
getHibernateTemplate().getSessionFactory().evict(Special.class);
getHibernateTemplate().getSessionFactory().evict(UpcPromotion.class);

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

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

public Integer deleteSpecials(){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting specials");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_onestop_promotion_membership cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_upc_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_store_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_ingredient_item_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_shopping_list_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete p from fl_promotion p where p.special = 1"};


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

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

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);
getHibernateTemplate().getSessionFactory().evict(Promotion.class);
getHibernateTemplate().getSessionFactory().evict(IngredientItemPromotion.class);
getHibernateTemplate().getSessionFactory().evict(Special.class);
getHibernateTemplate().getSessionFactory().evict(UpcPromotion.class);

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

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





public List deleteExpiredPromotions(){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

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

List lExternalIds = new ArrayList();

ScrollableResults lPromotions = pSession.createQuery("select this from Promotion this where now() > this.expirationDate")
.scroll();

if(lPromotions.first()) {
do {
Promotion lPromotion = (Promotion)lPromotions.get(0);
lExternalIds.add(lPromotion.getImage());
lPromotion.getIngredientPromotions().clear();
pSession.delete(lPromotion);
} while (lPromotions.next());
}

return lExternalIds;

}
});

List lResult = (List)lQueryResult;

if (LOG.isDebugEnabled()) {
LOG.debug("Deleting promotions["+lResult+"] by BatchId");
}
return lResult;
}catch (RuntimeException e) {
LOG.error("deletePromotionByBatchId failed", e);
throw e;
}
}

public List getPromotionImagesByBatchId(final Integer pBatchId){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

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


List lImages = pSession.createQuery("select distinct this.image from Promotion this where this.batchId=:pBatchId")
.setInteger("pBatchId", pBatchId)
.list();
return lImages;

}
});

if (LOG.isDebugEnabled()) {
LOG.debug("Found Promotion Images["+lResult+"] for BatchId:"+pBatchId);
}
return lResult;
}catch (RuntimeException e) {
LOG.error("getPromotionImagesByBatchId failed", e);
throw e;
}
}

public PromotionList findPromotionByDateRangeTypeStatus(final Date pStartDate, final Date pEndDate,final String pType,final boolean pStatus,final String pSortField, final String pSortDirection,final int pOffset,final int pMaxResults,final String pServeType){
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pStatus){
lStatusCriteria = "and promotion.status='ACTIVE' ";
} else {
lStatusCriteria = "and (promotion.status='ACTIVE' or promotion.status='DISABLED')";
}

String lSortField = pSortField;
if(null == pSortField){
lSortField = "promotionId";
}
String lSortDirection = pSortDirection;
if(null == pSortDirection){
lSortDirection = "asc";
}

String lServeType = "and promotion.special = false ";
if(null != pServeType && "specials".equals(pServeType)) {
lServeType = "and promotion.special=true ";
}

String lType="";
if(null != pType && !"".equals(pType)) {
lType = "and promotion.sourceType = '"+pType+"'";
}
//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
lType +
lStatusCriteria +
lServeType +
"order by promotion."+lSortField+" "+lSortDirection;
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
lType +
lStatusCriteria +
lServeType +
"group by promotion.promotionId "+
"order by promotion."+lSortField+" "+lSortDirection;

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStartDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setDate("pStart", pStartDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());
lPromotionResults.setSortField(pSortField);
lPromotionResults.setSortDirection(pSortDirection);

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public PromotionList findPromotionByActiveDateTypeStatus(final Date pActiveDate,final String pType,final String pDescription, final boolean pStatus,final boolean pExpired, final String pSortField,final String pSortDirection,final int pOffset,final int pMaxResults, final String pServeType){
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pStatus){
lStatusCriteria = " promotion.status='ACTIVE' ";
} else {
lStatusCriteria = " (promotion.status='ACTIVE' or promotion.status='DISABLED')";
}

String lSortField = pSortField;
if(null == pSortField || "".equals(pSortField)){
lSortField = "promotionId";
}
String lSortDirection = pSortDirection;
if(null == pSortDirection){
lSortDirection = "asc";
}

String lServeType = " promotion.special = false ";
if(null != pServeType && "specials".equals(pServeType)) {
lServeType = " promotion.special=true ";
}

String lDateCriteria = "";
if (null != pActiveDate) {
lDateCriteria = "((DATE(:pActiveDate) between DATE(promotion.activationDate) and DATE(promotion.expirationDate)))";
}

String lExpiryCriteria = "";
if(!pExpired) {
lExpiryCriteria = "((DATE(now()) <= DATE(promotion.expirationDate)))";
}

String lType="";
String lJoinCriteria = "";
if(null != pType && !"".equals(pType)) {
Integer lBrandId = Integer.valueOf(pType);
lJoinCriteria = "left join promotion.brand brand";
lType = " brand.id = "+lBrandId+"";
}

String lDescCriteria = "";
if(null != pDescription && !"".equals(pDescription)) {
String lDescription = pDescription;
if(pDescription.contains("'")){
lDescription = pDescription.replaceAll("'", "_");
}
lDescCriteria = " promotion.description like '%"+lDescription+"%'";
}

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion "+ lJoinCriteria +" where "+
lDateCriteria + (!"".equals(lDateCriteria) ? " and " : "") +
lType + (!"".equals(lType) ? " and " : "") +
lStatusCriteria + (!"".equals(lStatusCriteria) ? " and " : "") +
lExpiryCriteria + (!"".equals(lExpiryCriteria) ? " and " : "") +
lDescCriteria + (!"".equals(lDescCriteria) ? " and " : "") +
lServeType +
"order by promotion."+lSortField+" "+lSortDirection;

String lQueryForOneStops = "select promotion from Promotion promotion "+ lJoinCriteria +" where "+
lDateCriteria + (!"".equals(lDateCriteria) ? " and " : "") +
lType + (!"".equals(lType) ? " and " : "") +
lStatusCriteria + (!"".equals(lStatusCriteria) ? " and " : "") +
lExpiryCriteria + (!"".equals(lExpiryCriteria) ? " and " : "") +
lDescCriteria + (!"".equals(lDescCriteria) ? " and " : "") +
lServeType +
"group by promotion.promotionId "+
"order by promotion."+lSortField+" "+lSortDirection;

//execute the query for the entity count
Query lCountQuery = pSession.createQuery(lQueryforCount);
if (!"".equals(lDateCriteria)) {
lCountQuery.setDate("pActiveDate", pActiveDate);
}
Long count = (Long)lCountQuery.uniqueResult();

//execute the query for the entities
Query lResultsQuery = pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults);

if (!"".equals(lDateCriteria)) {
lResultsQuery.setDate("pActiveDate", pActiveDate);
}
List lResult = (List)lResultsQuery.list();


//transform the results in the ListResult
PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());
lPromotionResults.setSortField(pSortField);
lPromotionResults.setSortDirection(pSortDirection);

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

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionByActiveDateTypeStatus failed", e);
throw e;
}
}

public PromotionList findMostViewedCoupons(final Integer pOffSet,final Integer pMaxResults){
PromotionList lList = null;
try{
lList = (PromotionList) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession)throws HibernateException, SQLException{

String lIdsQuery = "select ref_id as refId from fl_event where category='COUPON' and event='View' and date >= DATE_SUB(now(),INTERVAL 7 DAY) " +
"group by ref_id order by count(ref_id) desc";

List lIds = pSession.createSQLQuery(lIdsQuery).addScalar("refId", Hibernate.INTEGER)
.list();

String lIdsSepByComma = lIds.toString();
lIdsSepByComma = lIdsSepByComma.substring(1,lIdsSepByComma.length()-1);

List lList = null;
if(null != lIds && !lIds.isEmpty()){

String lStringQuery = "select pro.promotion_id as id," +
" pro.code as code," +
" pro.title as title," +
" pro.description as description," +
" pro.activation_date as activationDate," +
" pro.expiration_date as expirationDate," +
" pro.image as image," +
" pro.source_type as sourceType," +
" pro.special as special" +
" from fl_promotion as pro where promotion_id in" +
"("+lIdsSepByComma+") order by field(promotion_id, "+lIdsSepByComma+") ";

Query lQuery = pSession.createSQLQuery(lStringQuery)
.addScalar("id", Hibernate.INTEGER)
.addScalar("code", Hibernate.STRING)
.addScalar("title", Hibernate.STRING)
.addScalar("description", Hibernate.STRING)
.addScalar("activationDate",Hibernate.DATE)
.addScalar("expirationDate",Hibernate.DATE)
.addScalar("image",Hibernate.STRING)
.addScalar("sourceType", Hibernate.STRING)
.addScalar("special", Hibernate.BOOLEAN);

lList = lQuery.setFirstResult(pOffSet)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setResultTransformer(Transformers.aliasToBean(Coupon.class))
.list();
}

PromotionList lTmpList = new PromotionList();
lTmpList.setMaxResults(pMaxResults);
lTmpList.setOffset(pOffSet);
lTmpList.setList(lList);
return lTmpList;
}
});
return lList;
}catch(RuntimeException e){
LOG.error("findMostViewedCoupons failed, "+e);
throw e;
}
}


/**
* Get the promotion based on imageName.
*/
public Promotion findByImageName(final String pPromotionImage) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotion by ImageName ["+(pPromotionImage == null ? "NA" : pPromotionImage)+"]");
}
try {
List lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Special as this where this.image = :pImage" +
" and (this.status='ACTIVE' or this.status='PENDING')")
.setString("pImage", pPromotionImage)
.list();
}
});

if (null != lPromotions) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by ImageName successful. Numbers of finding ["+lPromotions.size()+"]");
}
}
if(lPromotions == null || lPromotions.isEmpty()) {
return null;
} else {
return lPromotions.get(0);
}

} catch (RuntimeException lRe) {
LOG.error("Error finding Promotions by ImageName", lRe);
throw lRe;
}

}

/**
* Find the number of promotion map with given store
* @return int
*/
public int findStorePromotionByIds(final Integer pStoreId, final Integer pPromotionId){

if (LOG.isDebugEnabled()) {
LOG.debug("finding StorePromotion by storeId and promotionId");
}
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_store_promotion this where" +
" this.store_id = "+pStoreId+" 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("Successfully found StorePromotion by Ids. Count ["+(count == null ? 0 : count.intValue())+"]");
}
if(count == null) {
return 0;
} else {
return count.intValue();
}
}
});

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

public List findByUpcId(final Long pUpcItemId, final Integer pStoreId) {
List lPromotions = null;
try {
lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession) throws SQLException, HibernateException {
//current date
Date date = new Date();

if(null == pStoreId) {
String lQuery = "select promo from IngredientItemPromotion ingItemPromo left join ingItemPromo.promotion promo left join promo.stores as store , UpcIngredient upcIng " +
" where upcIng.ingredientItem = ingItemPromo.ingredientItem" +
" and upcIng.upc.code = :code" +
" and promo.activationDate <= :actDate" +
" and promo.expirationDate >= :expDate" +
" and promo.status = 'ACTIVE'" +
" and store.storeId IS NULL" +
" and promo.special is not true";
List lPromos = pSession.createQuery(lQuery).setLong("code", pUpcItemId)
.setDate("actDate", date)
.setDate("expDate", date)
.list();
return lPromos;
} else {

String lQuery = "select promo from IngredientItemPromotion ingItemPromo left join ingItemPromo.promotion promo left join promo.stores as store , UpcIngredient upcIng " +
" where upcIng.ingredientItem = ingItemPromo.ingredientItem" +
" and upcIng.upc.code = :code" +
" and promo.activationDate <= :actDate" +
" and promo.expirationDate >= :expDate" +
" and promo.status = 'ACTIVE'" +
" and ((promo.special is true and store.unitId = :pStoreId) or "+
" (promo.special is not true and (store.unitId = :pStoreId OR store.storeId IS NULL))) ";

List lPromos = pSession.createQuery(lQuery).setLong("code", pUpcItemId)
.setDate("actDate", date)
.setDate("expDate", date)
.setInteger("pStoreId", pStoreId)
.list();
return lPromos;
}


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

public List findByBrandId(final Integer pBrandId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding promotions with brand id");
}
List lPromotions = null;

try {
lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession) throws SQLException, HibernateException {
List lPromotions = pSession.createQuery("from Coupon coupon where coupon.brand.id = :pBrandId ").setInteger("pBrandId",pBrandId).list();

if(null == lPromotions || lPromotions.isEmpty()){
return null;
}
return lPromotions;
}
});
} catch(RuntimeException lRe){
LOG.error("finding promotions with brand id failed.", lRe);
throw lRe;
}

return lPromotions;
}

public void bulkUpdateByBrand(final Integer pBrandId){

if (LOG.isDebugEnabled()) {
LOG.debug("Update promotions with brand id");
}

Integer rowCount=null;
try{
rowCount =(Integer)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession) throws HibernateException, SQLException {

String hql = "update Promotion promotion set promotion.brand = :brand where promotion.brand.id = :brandId";
Query lQuery = pSession.createQuery(hql);
lQuery.setString("brand",null);
lQuery.setInteger("brandId",pBrandId);
return lQuery.executeUpdate();
}
});
}catch(RuntimeException e) {
LOG.error("fail to update the promotions :", e);
throw e;
}
if (LOG.isDebugEnabled()) {
LOG.debug("updated Promotions :"+rowCount);
}
}

public Collection findNonTargetedCouponsIncCoupons() {
return findByNamedQuery("promotion.non_targeted_coupons_inc_coupons", null);
}

public Collection findTargetedCouponsIncCoupons(String zipCode) {
return findByNamedQueryAndNamedParams("promotion.targeted_coupons_inc_coupons", new String[] { "zipCode" }, new String[] { zipCode });
}

public void setJdbcTemplate(JdbcTemplate pJdbcTemplate) {
m_jdbcTemplate = pJdbcTemplate;
}

public PromotionList findByStoreRecipeCategoryIngredientIds(final List storeIds,final List recipeIds,final List categoryIds,
final List ingredientIds,final String type,final Integer offset,final Integer maxResults) {
PromotionList promotionList = null;
try {
promotionList = (PromotionList) getHibernateTemplate().execute(new HibernateCallback(){

public Object doInHibernate(Session session) throws HibernateException, SQLException {
PromotionList list = new PromotionList();

StringBuffer selectForCount = new StringBuffer("select count(distinct promotion) ");
StringBuffer select = new StringBuffer("select promotion ");
StringBuffer from = new StringBuffer(" from ");
StringBuffer where = new StringBuffer(" where ");
boolean recipeIdsExist = false, storeIdsExist = false, categoryIdsExist = false, ingredientIdsExist = false;

String fromIncaseRecipeIdsIngredientIds = " Recipe recipe inner join recipe.recipeIngredients recipeIngredient join recipeIngredient.ingredient ingredient" +
" join ingredient.ingredientItem ingredientItem inner join ingredientItem.ingredientItemPromotions ingredientItemPromotion" +
" join ingredientItemPromotion.promotion promotion";

//check if recipeId exist.
if(recipeIds != null && !recipeIds.isEmpty()) {
from.append(fromIncaseRecipeIdsIngredientIds);
String tmpWhere = " recipe.recipeId in (:recipeIds) and recipe.status = 'ACTIVE' ";
select = new StringBuffer("select promotion, ingredient.ingredientId ");
where.append(tmpWhere);
recipeIdsExist = true;
}

//check if ingredientIds exist
if(ingredientIds != null && !ingredientIds.isEmpty()) {
String tmpWhere = checkifcontains(where.toString(), "and", " and ");
tmpWhere += " ingredient.ingredientId in (:ingredientIds) ";
if(!recipeIdsExist) {
from.append(fromIncaseRecipeIdsIngredientIds);
tmpWhere += " and recipe.status = 'ACTIVE' ";
}
select = new StringBuffer("select promotion, ingredient.ingredientId ");
where.append(tmpWhere);
ingredientIdsExist = true;
}

//check if storeId exist
if(storeIds != null && !storeIds.isEmpty()) {
String tmpWhere = " ";
if(ingredientIdsExist || recipeIdsExist) {
String tmpFrom = " left join promotion.stores store";
from.append(tmpFrom);
} else {
select = new StringBuffer("select promotion ");
String tmpFrom = " Promotion promotion inner join promotion.stores store";
from.append(tmpFrom);
}
where.append(tmpWhere);
storeIdsExist = true;
}

//check if categoryIds exist
if(categoryIds != null && !categoryIds.isEmpty()){
String tmpWhere = checkifcontains(where.toString(), "and", " and ");
where.trimToSize();
if(where.indexOf("where")>-1 && where.toString().contains("and") && !where.toString().endsWith("and")){
tmpWhere = " and ";
}
tmpWhere += " promotionCategory.promotionCategoryId in (:categoryIds) and promotionCategory.active = 1";
if(storeIdsExist || ingredientIdsExist) {
String tmpFrom = " inner join promotion.promotionCategory promotionCategory";
from.append(tmpFrom);
} else {
select = new StringBuffer("select promotion ");
String tmpFrom = " Promotion promotion inner join promotion.promotionCategory promotionCategory";
from.append(tmpFrom);
}
where.append(tmpWhere);
categoryIdsExist = true;
}

if(!from.toString().contains("promotion")) {
String tmpFrom = " Promotion promotion ";
from.append(tmpFrom);
}

String tmpWhere = checkifcontains(where.toString(), "and", " and ");;
if(tmpWhere.equals("")) {
tmpWhere = " and ";
}
tmpWhere += " (now() between date(promotion.activationDate) and date(promotion.expirationDate)) ";
tmpWhere += " and promotion.status = 'ACTIVE'";
if(type.equals("SPECIAL")) {
tmpWhere += " and promotion.special=1 ";
if(from.indexOf("store")>-1){
tmpWhere += " and store.unitId in (:storeIds) ";
}
} else if (type.equals("COUPON")) {
tmpWhere += " and promotion.special=0 ";
if(from.indexOf("store")>-1){
tmpWhere += " and store.storeId IS NULL ";
}
} else {
if(from.indexOf("store")>-1){
tmpWhere += " and ((promotion.special is true and store.unitId in (:storeIds)) or " +
" (promotion.special is not true and (store.unitId in (:storeIds) or store.storeId is NULL)))";
}
}
where.append(tmpWhere);

String stringCountQuery = selectForCount.append(from).append(where).toString();
String stringMainQuery = select.append(from).append(where).toString();

Query queryForCount = session.createQuery(stringCountQuery);
updateQueryWithParameterList(queryForCount, "recipeIds", recipeIds, recipeIdsExist);
updateQueryWithParameterList(queryForCount, "categoryIds", categoryIds, categoryIdsExist);
if(stringCountQuery.indexOf(":storeIds")>-1){
updateQueryWithParameterList(queryForCount, "storeIds", storeIds, storeIdsExist);
}
updateQueryWithParameterList(queryForCount, "ingredientIds", ingredientIds, ingredientIdsExist);

//getting counts
Long promotionCount = (Long)queryForCount.uniqueResult();

Query query = session.createQuery(stringMainQuery);
updateQueryWithParameterList(query, "recipeIds", recipeIds, recipeIdsExist);
updateQueryWithParameterList(query, "categoryIds", categoryIds, categoryIdsExist);
if(stringMainQuery.indexOf(":storeIds")>-1){
updateQueryWithParameterList(query, "storeIds", storeIds, storeIdsExist);
}
updateQueryWithParameterList(query, "ingredientIds", ingredientIds, ingredientIdsExist);

//getting list
List promotionList = null;
if(select.indexOf("ingredientId")>-1){
List objects = query.setFirstResult(offset.intValue())
.setMaxResults(maxResults.intValue())
.list();

if(objects !=null && !objects.isEmpty()) {
promotionList = new ArrayList();
Map map = new HashMap();
for(Object[] object: objects) {
Promotion promotion = (Promotion)object[0];
Integer ingredientId = (Integer)object[1];
if(map.containsKey(promotion.getPromotionId())){
promotion = map.get(promotion.getPromotionId());
promotion.addIngredientId(ingredientId);
continue;
}
promotion.addIngredientId(ingredientId);
promotionList.add(promotion);
map.put(promotion.getPromotionId(), promotion);
}
}

} else {
promotionList = query.setFirstResult(offset.intValue())
.setMaxResults(maxResults.intValue())
.list();
}


list.setOffset(offset);
list.setMaxResults(maxResults);
list.setTotalCount(promotionCount.intValue());

if(promotionList !=null && !promotionList.isEmpty()) {
list.setList(promotionList);
}

return list;
}

private String checkifcontains(String originalString, String searchString, String returnString){
if(originalString.contains(searchString)) {
return returnString;
}
return "";
}

private void updateQueryWithParameterList(Query hibernateQuery, String parameterName, List list, boolean flag) {
if(flag) {
hibernateQuery.setParameterList(parameterName, list);
}
}
});

if(LOG.isDebugEnabled()) {
LOG.debug("success");
}

return promotionList;
} catch (RuntimeException ex) {
LOG.error("findByStoreRecipeCategoryIngredientIds failed", ex);
throw ex;
}
}
}