q=ses.createSQLQuery("SELECT master_shelfitems.shelf_itemid,master_shelfitems.shelf_id,master_shelf.shelf_name,master_shelfitems.product_id,SUM(daily_productstock.productstock) FROM master_shelfitems INNER JOIN master_shelf ON (master_shelfitems.shelf_id=master_shelf.shelf_id) INNER JOIN master_product ON (master_shelfitems.product_id=master_product.product_id) INNER JOIN master_productbatch ON (master_product.product_id=master_productbatch.product_id)INNER JOIN daily_productstock ON (master_productbatch.productbatch_code=daily_productstock.productbatch_code) where productstock_date between '"+start+"' and '"+end+"' and master_shelfitems.is_deleted=0 GROUP BY master_shelfitems.shelf_id,master_shelf.shelf_name,master_shelfitems.product_id");
for(Iterator it=q.iterate();it.hasNext();)
{
ShelfItems shelfItems=new ShelfItems();
                    Shelf shelf1=new Shelf();
                    Product product1=new Product();
                    Object o[]=(Object[])it.next();
                    shelfItems.setShelf_itemid(Integer.parseInt(o[0].toString()));
                    String shelfid=o[1].toString();
                    shelf1=(Shelf)getHibernateTemplate().load(Shelf.class,Integer.parseInt(shelfid));
                    shelfItems.setShelf_id(shelf1);
                    shelfItems.setShelf_name1(o[2].toString());
                    String pid=o[3].toString();
                    logger.info("Hibernate...Product id is"+pid+"and shelf id is"+shelfid);
                    product1=(Product)getHibernateTemplate().load(Product.class,Integer.parseInt(pid));
                    shelfItems.setProduct_id(product1);                   
                    shelfItems.setStock(Double.parseDouble(o[4].toString()));
                    shelfItems.setRemarks(o[3].toString());
                    arr.add(shelfItems);
}