/*
 * NutsEUWS.java
 *
 * Created on 11 ������� 2007, 1:54 ��
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.ellectra.eunuts.wservice;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
import java.util.Vector;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.jws.WebService;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;
import javax.transaction.UserTransaction;
//import org.ellectra.eunuts.csvload.NUTSLoad;
import org.ellectra.eunuts.entity.NutsEU;

/**
 *
 * @author tdi
 */
@WebService()
@Stateless
public class NutsEUWS {
    @Resource
    private UserTransaction utx;

    @PersistenceUnit(unitName = "NutsPU")
    private EntityManagerFactory emf;
    protected BufferedReader in;
    private EntityManager getEntityManager() {
        return emf.createEntityManager();
    }
    
    public String LoadCsvOnDB(String szPath,String szlang) 
    {
        
        return LoadFileCC(szPath,szlang);  
    }
    
    public String LoadFileCC(String szPath,String szlang) 
    {
        
        int iloop=0;
        String szCodes="";
        String F1="";
        String F2="";
        String F3="";
        String F4="";
        String F5="";
        try {
            
            in = new BufferedReader(new FileReader(szPath));
        
            String record1 = null; 
            Vector<String> fields=null;
            try { 

               while ( (record1=in.readLine()) != null ) { 
                    iloop=iloop+1;   
                    
                    //look to found f1 and f2
                    
                    int j = record1.indexOf(";", 0);
                    int j1 = record1.indexOf(";", j+1);
                    int j2 = record1.indexOf(";", j1+1);
                    int j3 = record1.indexOf(";", j2+1);
                    int j4 = record1.indexOf(";", j3+1);
                    
                  //  F1=String.valueOf(j);
                  //  F2=String.valueOf(j1);
                  //  F3=String.valueOf(j2);
                  //  F4=String.valueOf(j3);
                  //  F5=String.valueOf(j4);
               
                    F1=record1.substring(0,j);
                    
                  //  if (j+1>j1) F2="?";
                  //  else if (j+1==j1) F2="?";
                 //   else
                        F2=record1.substring(j+1,j1);
                 //   
                 //   if (j1+1>j2) F3="?";
                 //   else if (j1+1==j2) F3="?";
                 //   else
                    F3=record1.substring(j1+1,j2);
                    
                 //   if (j2+1>j3) F4="?";
                 //   else if (j2+1==j3) F4="?";
                 //   else
                    F4=record1.substring(j2+1,j3);
                    
                  //  if (j3+1>record1.length()) F5="?";
                  //  else if (j3+1==record1.length()) F5="?";
                  //  else
                    F5=record1.substring(j3+1,record1.length());
                    
                    
                    
                        // TODO initialize WS operation arguments here
                        java.lang.String arg0 = F1;
                        java.lang.String arg1 = F2;
                        java.lang.String arg2 = F3;
                        java.lang.String arg3 = F4;
                        java.lang.String arg4 = F5;
                        // TODO process result here
                        AddNutsCode(arg0, arg1, arg2, arg3, arg4,szlang);
                   
                   
               } 

            } catch (IOException e) { 
               return "Error on read CSV File";
            }  
        } catch (FileNotFoundException ex) {
            return "Error on Open File";
        }
        try {
            in.close();
        } catch (IOException ex) {
            return "Error on Close File";
        }
        return "OK";
    }
    
    public List<NutsEU> GetAllNutsCodes(String szlang) {
        assert emf != null;  //Make sure injection went through correctly.
        EntityManager em = null;
        em = emf.createEntityManager();
        return em.createQuery("select object(o) from NutsEU as o where o.languagenuts=:uv order by o.code").setParameter("uv",szlang).getResultList();
    }
     public String DeleteCodeForLang(String szlang)
    {
        assert emf != null;  //Make sure injection went through correctly.
        EntityManager em = null;
        em = emf.createEntityManager();
        List<NutsEU> dList= em.createQuery("select c from NutsEU c where c.languagenuts=:uv").setParameter("uv",szlang).getResultList();
        for (int i=0;i<dList.size();i++)
        {
            NutsEU deleted = (NutsEU)dList.get(i);
            em.remove(deleted);
        }
        return "success";
    }
    /**
     *Get All users already in the database
     *Returns : List type of UserAccount
      *where u.Username=:Uservar").setParameter("Uservar",szUsername).getResultList();
     */
     public List<NutsEU> GetDetailsForNutsCode(String szCode,String szlang) {
        assert emf != null;  //Make sure injection went through correctly.
        EntityManager em = null;
        em = emf.createEntityManager();
        return em.createQuery("select object(o) from NutsEU as o where o.code=:Uservar and o.languagenuts=:uv").setParameter("Uservar",szCode).setParameter("uv",szlang).getResultList();
    }
     
       /**
     *Get selected user from the database
     *Parameter : username type of string
     *Returns: List type of userAccount
     */
    public List<NutsEU> SearchForNutsCodes(String szCode,String szlang)
    {
        assert emf != null;  //Make sure injection went through correctly.
        String szQuery="select object(o) from NutsEU as o where o.country LIKE '%"+szCode+"%' and o.languagenuts='"+szlang+"'";//%"+szCode+"%";
        EntityManager em = null;
        em = emf.createEntityManager();
        return em.createQuery(szQuery).getResultList();
        
    }
    
     /**
     *Add a new user in the database
     *Parameters : user Fields
     *Return a string with the message
     */
    public String AddNutsCode(String NUTSCode,String NUTSCountry,String L1,String L2,String L3,String szlang)
    {
        EntityManager em = null;
        em = emf.createEntityManager();
                    
        NutsEU pNutsCode;
        pNutsCode = new NutsEU();

        
        if (NUTSCode==null) NUTSCode="?";
        if (NUTSCountry==null) NUTSCountry="?";
        if (L1==null) L1="?";
        if (L2==null) L2="?";
        if (L3==null) L3="?";

        if (NUTSCode.length()<2) NUTSCode="?";
        if (NUTSCountry.length()<2) NUTSCountry="?";
        if (L1.length()<2) L1="?";
        if (L2.length()<2) L2="?";
        if (L3.length()<2) L3="?";
        
        pNutsCode.setCode(NUTSCode);
        pNutsCode.setCountry(NUTSCountry);
        pNutsCode.setLevel1(L1);
        pNutsCode.setLevel2(L2);
        pNutsCode.setLevel3(L3);        
        pNutsCode.setLanguagenuts(szlang);
        
        try {
          //  utx.begin();
            em.persist(pNutsCode);
         //   utx.commit();
            return "Nuts Code Added";
        } catch (Exception e) {               

            return "Error on Add Nuts Code - Platform Error";
        }
            
    }
}
