processmanagerbean.java

THIS IS A FINANCIAL BANK APPLICATION

package Pcrd.Subscription.DB;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.CHAR;
import oracle.sql.DATE;
import oracle.sql.NUMBER;

public class ProcessManagerBean implements ProcessManager{
	


public Connection openConnection() {

		DataSource ds = null;
		Connection conn = null;

		try {

			InitialContext ic = new InitialContext();

			ds = (DataSource) ic.lookup("PcardgDS");

		} catch (NamingException ex) {

			throw new IllegalStateException(ex);

		}

		try {
			if (ds != null) {
				System.out.println("ds non null");
				conn = ds.getConnection();
				System.out.println("connexion ok");

			} else
				System.out.println("ds  null");

			return conn;

		} catch (SQLException ex) {

			throw new IllegalStateException(ex);

		}

	}

public List<String> GET_CARD_INFO(String  p_card_number) throws NamingException{
		
		int returnVal=-3;
		String buffer=null;
		String p_code_reponse=null;
		String p_datcrt=null;
		String p_codbqe=null;
		String p_prp_bqecpt=null;
		String p_codagence=null;
		String p_nomprenom=null;
		String p_adrs1=null;
		String p_adrs2=null;
		String p_adrs3=null;
		String p_datdebut=null;
		String p_datfin =null;
		String p_etat=null;
		String p_etatlsn=null;
		String p_ribport=null;
		String p_vip=null;
		String p_renew=null;
		String p_typcrt=null;
		String p_typecarte=null;
		String p_catsoc=null;
		String p_nordr=null;
		String p_upstat=null;
		String p_titre=null;
		String p_ville=null;
		String jour=null;
		String mois=null;
		String annee=null;
        Connection con = null;
        CallableStatement oraPreStat=null;
        
        try {
        	
        	InitialContext ic = new InitialContext();
      		
         System.out.println("avant con");
      		
      		DataSource ds = (DataSource) ic.lookup("PcardgDS");
        	System.out.println("avant con");
      		//con = openConnection();
      		
          	con=ds.getConnection();
          	System.out.println("apres con");
            oraPreStat =  con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_CARD_INFO(?,?);END;");
            
            oraPreStat.setString(2,p_card_number);
            oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
            
            oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
            
            
            oraPreStat.executeUpdate();
            
           buffer = (String) oraPreStat.getString(3);
           if(buffer.length()==225){
            p_code_reponse=buffer.substring(0,3);
            mois=buffer.substring(3,5);
            jour=buffer.substring(5,7);
            annee=buffer.substring(7,11);
            p_datcrt= jour+"/"+mois+"/"+annee;
            p_codbqe=buffer.substring(11,16);
            p_prp_bqecpt =buffer.substring(16,21);
            p_codagence= buffer.substring(21,27);
            p_nomprenom=buffer.substring(27,52);
            p_adrs1=buffer.substring(52,87);
            p_adrs2=buffer.substring(87,122);
            p_adrs3=buffer.substring(122,157);
            mois=buffer.substring(157,159);
            jour=buffer.substring(159,161);
            annee=buffer.substring(161,165);
            p_datdebut=jour+"/"+mois+"/"+annee;
            System.out.println("la date="+p_datdebut);
            mois=buffer.substring(165,167);
            jour=buffer.substring(167,169);
            annee=buffer.substring(169,173);
            p_datfin =jour+"/"+mois+"/"+annee;
            p_etat =buffer.substring(173,175);
            p_etatlsn=buffer.substring(175,176);
            p_ribport=buffer.substring(176,196);
            p_vip =buffer.substring(196,197);
            p_renew=buffer.substring(197,198);
            p_typcrt=buffer.substring(198,202);
            p_typecarte=buffer.substring(202,204);
            p_catsoc=buffer.substring(204,206);
            p_nordr=buffer.substring(206,211);
            p_upstat=buffer.substring(211,212);
            p_titre=buffer.substring(212,222);
            p_ville =buffer.substring(222,225);
            returnVal =oraPreStat.getInt(1);
            System.out.println("La valeur de retour est: "+returnVal);}
           else{
        	   returnVal=-1;
           }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try { con.close(); } 
            catch (Exception e) {
            e.printStackTrace();}}
        /*
         * remplire la liste de retour
         */
		List<String> retour=new ArrayList<String>();
		//0
		retour.add(""+returnVal);
		//1
		retour.add(p_code_reponse.trim());
		//2
		retour.add(p_datcrt.trim());
		//3
		retour.add(p_codbqe.trim());
		//4
		retour.add(p_prp_bqecpt.trim());
		//5
		retour.add(p_codagence.trim());
		//6:nomprenom
		retour.add(p_nomprenom.trim());
		//7
		retour.add(p_adrs1.trim());
		//8
		retour.add(p_adrs2.trim());
		//9
		retour.add(p_adrs3.trim());
		//10: date de debut 
		retour.add(p_datdebut.trim());
		//11: date de fin
		retour.add(p_datfin.trim());
		//12: statut carte
		retour.add(p_etat.trim());
		//13
		retour.add(p_etatlsn.trim());
		//14
		retour.add(p_ribport.trim());
		//15
		retour.add(p_vip.trim());
		//16
		retour.add(p_renew.trim());
		//17
		retour.add(p_typcrt.trim());
		//18: type carte
		retour.add(p_typecarte.trim());
		//19
		retour.add(p_catsoc.trim());
		//20
		retour.add(p_nordr.trim());
		//21
		retour.add(p_upstat.trim());
		//22
		retour.add(p_titre.trim());
		//23
		retour.add(p_ville.trim());
		
		
		return retour;
		
	}

public List<String> GET_ACCOUNT_INFO(String  p_account_number) throws NamingException{
    String buffer=null;
	String p_code_reponse =null;
    String p_devise=null;
    String p_date_ouverture=null;
    String p_raison_sociale=null;
    String p_complement_rs=null;
    String p_adresse1_residence=null;
    String p_adresse2_residence=null;
    String p_ville_residence=null;
    String p_separateur=null;
    String p_pays_courrier=null;
    String p_courrier_garde=null;
    String p_tel_domicile =null;
    String p_code_postal=null;
    String p_ville_courrier=null;
    String p_espace=null;
    String p_racine_charge_clientele=null;
    String p_situation_matrimoniale=null;
    String p_num_ide=null;
    String p_attribut_res=null;
    String p_type_client=null;
    String p_capital=null;
    String p_dirigeant=null;
    String p_fonction_dirigeant=null;
    String p_statut_compte=null;
    String p_code_secteur_act=null;
    String p_code_segment =null;
    String p_contentieux=null;
    String p_espace_1=null;
    int returnVal=-3;
    
    Connection con = null;
    
    CallableStatement oraPreStat=null;
    try {
    	InitialContext ic = new InitialContext();
		
    	System.out.println("avant con");
		
		DataSource ds = (DataSource) ic.lookup("PcardgDS");
		
		con = ds.getConnection();
        oraPreStat =con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_ACCOUNT_INFO(?,?);END;");
        oraPreStat.setString(2,p_account_number);
        
        oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
        oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
        
        
        oraPreStat.executeUpdate();
        
       buffer = (String) oraPreStat.getString(3);
       if(buffer.length()==319){
      
       System.out.println("la longueur du buffer est"+buffer.length());
       p_code_reponse = buffer.substring(0,3);
       p_devise = buffer.substring(3,5);
       p_date_ouverture=buffer.substring(5,13);
       System.out.println("la date est : "+p_date_ouverture);
       p_raison_sociale = buffer.substring(13,43);
       System.out.println("raison soc : "+p_raison_sociale);
       p_complement_rs = buffer.substring(43,63);
       p_adresse1_residence = buffer.substring(63,93);
       System.out.println("adresse1 : "+p_adresse1_residence);
       
       p_adresse2_residence = buffer.substring(93,123);
       p_ville_residence = buffer.substring(123,149);
       p_separateur= buffer.substring(149,150);
       p_pays_courrier = buffer.substring(150,153);
       p_courrier_garde= buffer.substring(153,154);
       p_tel_domicile  = buffer.substring(154,169);
       p_code_postal = buffer.substring(169,184);
       p_ville_courrier = buffer.substring(184,187);
       p_espace = buffer.substring(187,202);
       p_racine_charge_clientele = buffer.substring(202,207);
       p_situation_matrimoniale = buffer.substring(207,208);
       p_num_ide = buffer.substring(208,220);
       p_attribut_res= buffer.substring(220,221);
       p_type_client = buffer.substring(221,223);
      
       p_capital = buffer.substring(223,232);
       p_dirigeant = buffer.substring(232,262);
       p_fonction_dirigeant = buffer.substring(262,312);
       System.out.println("f dirigeant : "+p_fonction_dirigeant);
       
       p_statut_compte= buffer.substring(312,313);
       System.out.println("stat_compte="+p_statut_compte);
       p_code_secteur_act = buffer.substring(313,316);
       p_code_segment  = buffer.substring(316,317);
       p_contentieux= buffer.substring(317,318);
       System.out.println("contentieux="+p_contentieux);
       p_espace_1 = buffer.substring(318,319);
        returnVal =oraPreStat.getInt(1);
       
        System.out.println("La valeur de retour est: "+returnVal);
       }
       else{
    	   returnVal=-1;
    	  
       }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        try { con.close(); } 
        catch (Exception e) {
        e.printStackTrace();}}
    List<String> retour=new ArrayList<String>();
    retour.add(""+returnVal);
    retour.add(p_code_reponse.trim());
    retour.add(p_devise.trim());
    retour.add(""+p_date_ouverture.trim());
    retour.add(p_raison_sociale.trim());
    retour.add( p_complement_rs.trim());
    retour.add(p_adresse1_residence.trim());
    retour.add(p_adresse2_residence.trim());
    retour.add(p_ville_residence.trim());
    retour.add(""+p_separateur.trim());
    retour.add(p_pays_courrier.trim());
    retour.add(""+p_courrier_garde.trim());
    retour.add(p_tel_domicile.trim());
    retour.add(p_code_postal.trim());
    retour.add(p_ville_courrier.trim());
    retour.add(p_espace.trim());
    retour.add(p_racine_charge_clientele.trim());
    retour.add(""+p_situation_matrimoniale.trim());
    retour.add(p_num_ide.trim());
    retour.add(""+p_attribut_res.trim());
    retour.add(p_type_client.trim());
    retour.add( p_capital.trim());
    retour.add(p_dirigeant.trim());
    retour.add(p_fonction_dirigeant.trim());
    retour.add(p_statut_compte.trim());
    retour.add(p_code_secteur_act.trim());
    retour.add(""+p_code_segment.trim());
    retour.add(p_contentieux.trim());
    retour.add(""+p_espace_1);
   
    return retour;}
	

public List<String> GET_CARD_LIST(String  p_account_number) throws NamingException{
	
	int returnVal=-3;
	String buffer=null;
	String p_code_reponse=null;
	String p_nombre_carte = null;

	Connection con = null;
    CallableStatement oraPreStat=null;
    List<String> retour=new ArrayList<String>();
    
    try {
    	
    	 InitialContext ic = new InitialContext();
 		
     	System.out.println("avant con");
 		
 		DataSource ds = (DataSource) ic.lookup("PcardgDS");
 		
 		con = ds.getConnection();

        oraPreStat = con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_CARD_LIST(?,?);END;");
        oraPreStat.setString(2,p_account_number);
        oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
        
        oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
        
        oraPreStat.executeUpdate();
        
        buffer = (String) oraPreStat.getString(3);
        if(buffer.length()==100){
     
     
        p_code_reponse = buffer.substring(0,3);
        //p_nombre_carte=buffer.substring(3,5);
        p_nombre_carte="05";
        returnVal=oraPreStat.getInt(1);
        
        
        retour.add(""+returnVal);
        System.out.println(returnVal);
        retour.add(p_code_reponse.trim());
        retour.add(p_nombre_carte.trim());
        System.out.println("p rep"+p_code_reponse);
        System.out.println("p nombre"+p_nombre_carte);
        
      //  int nombre_carte=Integer.parseInt(p_nombre_carte);
        for(int i=0;i<5;i++){
        	String p_card_number=buffer.substring(5+(19*i),5+(19*(i+1)));
        	retour.add(p_card_number);
        }
        
        }
        else{
        	returnVal=-1;
        }
        
       
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        try { con.close(); } 
        catch (Exception e) {
        e.printStackTrace();}}
   
    
    
    

  
  
    return retour;
}

}