String hql = "select a.*,a.measure,b.productId,b.product from (select code,name,measure,";
int count = 0;
if(mm==2){
count=30;
}else if(mm==1||mm==3||mm==5||mm==7||mm==8||mm==10||mm==12){
count=32;
}else{
count=31;
}
for(int i=1;i<count;i++){
hql = hql + "sum(case dd when '"+i+"' then rejectQuantity end) AS 'rejectQuantity"+i+"',";
hql = hql + "sum(case dd when '"+i+"' then changeQuantity end) AS 'changeQuantity"+i+"',";
}
hql = hql + "SUM(rejectQuantity) AS rejectTotal,SUM(changeQuantity) AS changeTotal from reject where yy = "+yy+" and mm = "+mm+" group by code) a LEFT JOIN bom b on a.code = b.code order by b.productId,a.code";
Query query = this.getSession().createSQLQuery(hql);
query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
List<?> lists = query.list();
return lists;
int count = 0;
if(mm==2){
count=30;
}else if(mm==1||mm==3||mm==5||mm==7||mm==8||mm==10||mm==12){
count=32;
}else{
count=31;
}
for(int i=1;i<count;i++){
hql = hql + "sum(case dd when '"+i+"' then rejectQuantity end) AS 'rejectQuantity"+i+"',";
hql = hql + "sum(case dd when '"+i+"' then changeQuantity end) AS 'changeQuantity"+i+"',";
}
hql = hql + "SUM(rejectQuantity) AS rejectTotal,SUM(changeQuantity) AS changeTotal from reject where yy = "+yy+" and mm = "+mm+" group by code) a LEFT JOIN bom b on a.code = b.code order by b.productId,a.code";
Query query = this.getSession().createSQLQuery(hql);
query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
List<?> lists = query.list();
return lists;