package com.foshanshop.ejb3.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.foshanshop.ejb3.QueryDAO;
import com.foshanshop.ejb3.bean.Order;
import com.foshanshop.ejb3.bean.OrderItem;
import com.foshanshop.ejb3.bean.Person;
import com.foshanshop.ejb3.bean.SimplePerson;

@Stateless
@Remote ({QueryDAO.class})
public class QueryDAOBean implements QueryDAO {
    @PersistenceContext
    protected EntityManager em;

    public void initdate() {
        try {
            Query query = em.createQuery("select count(*) from Person p");
            Object result = query.getSingleResult();
            if (result == null || Integer.parseInt(result.toString()) == 0) {
                // 沒有數據時，插入幾條數據用作測試
                // ===================================
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                Person person = new Person("liujun", true, new Short("26"),
                        formatter.parse("1980-9-30"));
                Set<Order> orders = new HashSet<Order>();

                Order order1 = new Order(new Float("105.5"), person, new Date());
                order1.addOrderItem(new OrderItem("U盤", new Float("105.5")));

                Order order2 = new Order(new Float("780"), person, new Date());
                order2.addOrderItem(new OrderItem("MP4", new Float("778")));
                order2.addOrderItem(new OrderItem("礦泉水", new Float("2")));
                orders.add(order1);
                orders.add(order2);
                person.setOrders(orders);

                Person person1 = new Person("yunxiaoyi", false,
                        new Short("23"), formatter.parse("1983-10-20"));
                orders = new HashSet<Order>();
                order1 = new Order(new Float("360"), person1, new Date());
                order1.addOrderItem(new OrderItem("香水", new Float("360")));

                order2 = new Order(new Float("1806"), person1, new Date());
                order2.addOrderItem(new OrderItem("照相機", new Float("1800")));
                order2.addOrderItem(new OrderItem("5號電池", new Float("6")));
                orders.add(order1);
                orders.add(order2);
                person1.setOrders(orders);

                // =====================================
                Person person2 = new Person("zhangming", false,
                        new Short("21"), formatter.parse("1985-11-25"));
                orders = new HashSet<Order>();

                order1 = new Order(new Float("620"), person2, new Date());
                order1.addOrderItem(new OrderItem("棉被", new Float("620")));

                order2 = new Order(new Float("3"), person2, new Date());
                order2.addOrderItem(new OrderItem("可樂", new Float("3")));
                orders.add(order1);
                orders.add(order2);
                person2.setOrders(orders);

                em.persist(person2);
                em.persist(person1);
                em.persist(person);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    
    
    public String ExecuteQuery(int index) {
        String result = "";
        switch(index){      
            case 1:
                result = this.NameQuery();
                break;
            case 2:
                result = this.PositionQuery();
                break;
            case 3:
                result = this.QueryOrderBy();
                break;
            case 4:
                result = this.QueryPartAttribute();
                break;
            case 5:
                result = this.QueryConstructor();
                break;
            case 6:
                result = this.QueryAggregation();
                break;
            case 7:
                result = this.QueryGroupBy();
                break;
            case 8:
                result = this.QueryGroupByHaving();
                break;
            case 9:
                result = this.QueryLeftJoin();
                break;
            case 10:
                result = this.QueryInnerJoin();
                break;   
            case 11:
                result = this.QueryInnerJoinLazyLoad();
                break; 
            case 12:
                result = this.QueryJoinFetch();
                break; 
            case 13:
                result = this.QueryEntityParameter();
                break; 
            case 14:
                result = this.QueryBatchUpdate();
                break; 
            case 15:
                result = this.QueryBatchRemove();
                break;  
            case 16:
                result = this.QueryNOTOperate();
                break; 
            case 17:
                result = this.QueryBETWEENOperate();
                break;  
            case 18:
                result = this.QueryINOperate();
                break; 
            case 19:
                result = this.QueryLIKEOperate();
                break;           
            case 20:
                result = this.QueryISNULLOperate();
                break;  
            case 21:
                result = this.QueryISEMPTYOperate();
                break; 
            case 22:
                result = this.QueryEXISTSOperate();
                break;  
            case 23:
                result = this.QueryStringOperate();
                break;  
            case 24:
                result = this.QueryMathLOperate();
                break; 
            case 25:
                result = this.QuerySubQueryOperate();
                break; 
            case 26:
                result = this.QueryNoneReturnValueStoreProcedure();
                break;     
            case 27:
                result = this.QuerySingleObjectStoreProcedure();
                break;   
            case 28:
                result = this.QueryStoreProcedure();
                break;                
            case 29:
                result = this.QueryPartColumnStoreProcedure();
                break;                 
        }        
        return result;
    }
    
    private String NameQuery(){
        //獲取指定personid的人員
        Query query = em.createQuery("select p from Person p where p.personid=:Id");
        query.setParameter("Id",new Integer(1));
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** NameQuery 結果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }
        return out.toString();
    }
    
    private String PositionQuery(){
        //獲取指定personid的人員
        Query query = em.createQuery("select p from Person p where p.personid=?1");
        query.setParameter(1,new Integer(1));
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** PositionQuery 結果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }
        return out.toString();
    }
    
    private String QueryOrderBy(){
        //先按年齡降序排序，然后按出生日期升序排序
        Query query = em.createQuery("select p from Person p order by p.age desc, p.birthday asc");
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryOrderBy 結果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                Person person= (Person)iterator.next();            
                out.append(person.getName()+ "<BR>");            
            }
        }
        return out.toString();
    }
    
    private String QueryPartAttribute(){
        //直接查詢我們感興趣的屬性(列)
        Query query = em.createQuery("select p.personid, p.name from Person p order by p.personid desc ");
        //集合中的元素不再是Person,而是一個Object[]對象數組
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryPartAttribute 結果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();            
            while( iterator.hasNext() ){
                //取每一行
                Object[] row = ( Object[]) iterator.next();
                //數組中的第一個值是personid
                int personid = Integer.parseInt(row[0].toString());
                String PersonName = row[1].toString(); 
                out.append("personid="+ personid+ "; Person Name="+PersonName+ "<BR>");    
            }
        }
        return out.toString();
    }
    
    private String QueryConstructor(){
        //我們把需要的兩個屬性作為SimplePerson的構造器參數，并使用new函數。
        Query query = em.createQuery("select new com.foshanshop.ejb3.bean.SimplePerson(p.name,p.sex) from Person p order by p.personid desc");
        //集合中的元素是SimplePerson對象
        List result = query.getResultList();
        StringBuffer out = new StringBuffer("*************** QueryConstructor 結果打印 ****************<BR>");
        if (result!=null){
            Iterator iterator = result.iterator();
            while( iterator.hasNext() ){
                SimplePerson simpleperson = (SimplePerson) iterator.next();
                out.append("人員介紹："+ simpleperson.getDescription()+ "<BR>");    
            }
        }
        return out.toString();     
    }
    
    private String QueryAggregation(){
        //獲取最大年齡
        Query query = em.createQuery("select max(p.age) from Person p");
        Object result = query.getSingleResult();