package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bFileLog;


public class m05bOrganismLoad{
	   m05bFileLog miLog = new m05bFileLog();

	//Método para cargar los datos de la tabla de sanciones 
	//a la tabla de organismos
    public static String cargar() {
    	String sql = "";
    	Connection conn = null;
    	m05bConexion miConex = new m05bConexion();
 	    PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql = "SELECT " + m05bConstantes.COL_45_COD_ORG + ", " + m05bConstantes.COL_45_DESC_ORG;
    	sql+= " FROM "+ m05bConstantes.TAB_SANCIONES + " GROUP BY " + m05bConstantes.COL_45_COD_ORG;
    	sql+= ", " + m05bConstantes.COL_45_DESC_ORG +" ORDER BY "+ m05bConstantes.COL_45_COD_ORG;
	    try {
	    	conn = miConex.getConnection();
	    	conn.setAutoCommit(true);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			//Recorro cada uno de los diferentes organismos de 
			//la tabla de sanciones
			String codOrg = "";
			String descOrg = "";
			String codOrgInOrg = "";
			String aux = "";
			while (rs.next()){ 		
				aux = "";
				codOrg = rs.getString(m05bConstantes.COL_45_COD_ORG);
				descOrg = rs.getString(m05bConstantes.COL_45_DESC_ORG);
				if((codOrg==null || codOrg.trim().equals(""))
					&& (descOrg==null || descOrg.trim().equals("")))
				{
					System.out.println("No creo nada");
				}else{
					if(!existeCodigoInOrganismosTable(conn,codOrg)){
						if(codOrg==null || codOrg.trim().equals("")){
							codOrg="";
						}
						if((descOrg==null || descOrg.trim().equals("")) && codOrg!=null && !codOrg.equals("")){
							aux = codOrg;
							codOrg = (descOrg==null?"":descOrg);
							descOrg = aux;
						}
						codOrgInOrg = existeInOrganismosTable(conn,descOrg);
						if(codOrgInOrg.equals("-1")){
							//No existe ningún elemento igual en la tabla organismos
							//Inserto en la tabla Organismos y obetngo el nuevo código
							codOrgInOrg = insertInOrganismosTable(conn,descOrg);
						}
						//Hago update en el campo código con el valor recuperado
						if(aux.equals(""))
							updateInSancionesTable(conn,codOrg,descOrg,codOrgInOrg,descOrg);
						else
							updateInSancionesTable2(conn,aux,codOrg,codOrgInOrg,codOrg);
					}
				
				}
			} 
			conn.commit();
			return "Realizado con éxito";
	    } catch (SQLException e) {
	    	   m05bFileLog miLog = new m05bFileLog();
	    	miLog.addTraza(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				miLog.addTraza(e1);
			}
			return "Se ha producido un error";
		}
	    catch (Exception e) {
	    	   m05bFileLog miLog = new m05bFileLog();
			miLog.addTraza(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				miLog.addTraza(e1);
			}
			return "Se ha producido un error";
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e1) {
					m05bFileLog miLog = new m05bFileLog();
					miLog.addTraza(e1);
				}
			}
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e1) {
					m05bFileLog miLog = new m05bFileLog();
					miLog.addTraza(e1);
				}
			}
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e1) {
					m05bFileLog miLog = new m05bFileLog();
					miLog.addTraza(e1);
				}
			}
		}
    }
    
    //Método para unir valores de organismos
    public static String unir(String lista) {
    	String registroPpal="";
    	String registro="";
    	String registroPrimero="";
    	String result ="";
    	if(lista!=null){
    		java.util.StringTokenizer stPpal = new java.util.StringTokenizer(lista,"-");
    		while (stPpal.hasMoreTokens()){
    			registroPpal = stPpal.nextToken();
	    		java.util.StringTokenizer st = new java.util.StringTokenizer(registroPpal,"#");
	    		while (st.hasMoreTokens()){
	    			registro = st.nextToken();
	    			if(registroPrimero.equals("")){
	    				registroPrimero=registro;
	    			}else{
	    				result=union(registro,registroPrimero);
	    			}
	    		}
    		}
    	}
		return result;
	    
    }
    
    private static String existeInOrganismosTable(Connection conn,String descripcion) throws SQLException{
    	String sql = "";
    	PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql = "SELECT COD_ORG_48";
    	sql+= " FROM M0548T00 WHERE DESCRIP_48 = '"+descripcion+"' OR "; 
    	sql+= "E_DESCRIP_48 = '"+descripcion+"'";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		//Recorro cada uno de los diferentes organismos de 
		//la tabla de sanciones
		String codOrg = "-1";
		if (rs.next()){ 		
			codOrg = rs.getString(1);
		} 
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return codOrg;
    }
    
    private static boolean existeCodigoInOrganismosTable(Connection conn,String codigo) throws SQLException{
    	String sql = "";
    	boolean result = false;
    	PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql = "SELECT COD_ORG_48";
    	sql+= " FROM M0548T00 WHERE COD_ORG_48 = '"+codigo+"'"; 
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if (rs.next()){ 		
			result = true;
		} 
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		return result;
    }
    
    private static String union(String codigo, String codigoPpal){
    	Connection conn = null;
    	m05bConexion miConex = new m05bConexion();
    	PreparedStatement pstmt = null;
    	String sql = "";
    	try {
	    	conn = miConex.getConnection();
	    	//Parte I
	    	sql = "UPDATE M0545T00";
	    	sql+= " SET COD_ORG_45 ='"+codigoPpal+"' WHERE COD_ORG_45='"+codigo+"'";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate(); 
			sql = "DELETE FROM M0548T00 WHERE COD_ORG_48 = '"+codigo+"'";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate(); 
			return "Realizado con éxito";
 	    }catch(Exception e){
 	       m05bFileLog miLog = new m05bFileLog();
 	    	miLog.addTraza(e);
 	    	return "Se ha producido un error";
 	    }finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e1) {
					m05bFileLog miLog = new m05bFileLog();
					miLog.addTraza(e1);
				}
			}
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e1) {
					m05bFileLog miLog = new m05bFileLog();
					miLog.addTraza(e1);
				}
			}
		}
    }
	
    private static String insertInOrganismosTable(Connection conn,String descripcion) throws SQLException{
    	//Parte I
    	String sql = "";
    	PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql = "SELECT max(TO_NUMBER(COD_ORG_48)) FROM M0548T00";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		//Recorro cada uno de los diferentes organismos de 
		//la tabla de sanciones
		String codOrg = "1";
		if (rs.next()){ 		
			codOrg = rs.getString(1);
			if(codOrg==null || codOrg.equals(""))
				codOrg = "1";
			else
				codOrg = (Integer.parseInt(codOrg)+1)+"";
		} 
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
		
		//Parte II
		String sql2 = "";
    	PreparedStatement pstmt2 = null;
 	    ResultSet rs2 = null;
    	sql = "INSERT INTO M0548T00";
    	sql+= " VALUES('"+codOrg+"','"+((descripcion==null || descripcion.equals(""))?"":descripcion)+"','e-"+((descripcion==null || descripcion.equals(""))?"":descripcion)+"')";
		pstmt = conn.prepareStatement(sql);
		pstmt.executeUpdate(); 
		if(pstmt2!=null){
			pstmt2.close();
		}
		if(rs2!=null){
			rs2.close();
		}
		return codOrg;
    }
    
    private static void updateInSancionesTable(Connection conn,String codigoV, String descripcionV,String códigoN, String descripcionN) throws SQLException{
    	//Parte I
    	String sql = "";
    	PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql=  "UPDATE M0545T00 SET DESC_ORG_45='"+codigoV+"', COD_ORG_45 ='"+códigoN+"' WHERE ";
    	sql+= "DESC_ORG_45='"+descripcionV+"'";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		pstmt.executeUpdate();
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
    }

    private static void updateInSancionesTable2(Connection conn,String codigoV, String descripcionV,String códigoN, String descripcionN) throws SQLException{
    	//Parte I
    	String sql = "";
    	PreparedStatement pstmt = null;
 	    ResultSet rs = null;
    	sql=  "UPDATE M0545T00 SET DESC_ORG_45='"+codigoV+"', COD_ORG_45 ='"+códigoN+"' WHERE ";
    	sql+= "COD_ORG_45='"+codigoV+"'";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		pstmt.executeUpdate();
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
    }

}