package org.esupportail.portal.channels.gestion.CConsultHarpege;

import java.sql.SQLException;
import org.apache.log4j.Priority;
import org.jasig.portal.ChannelRuntimeData;
import org.jasig.portal.PortalException;
import org.esupportail.portal.utils.channels.FrameWorkException;
import org.esupportail.portal.utils.channels.MainChannel;
import org.esupportail.portal.utils.channels.SubChannel;
import org.esupportail.portal.utils.database.Query;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * DetailCarriere<br>
 * <br>
 * Gestion de l'affichage du détail d'une période de carrière de l'agent <br>
 * <br>
 * (c)Copyright <a href="http://www.esup-portail.org">ESup-Portail 2004</a><br>
 * @author <a href="mailto:jean-marc.gullung@uhp-nancy.fr">Jean-marc GULLUNG</a>
 * @version 1.0
 * 
 */
public class DetailCarriere extends SubChannel  {

	protected static final Log log = LogFactory.getLog(DetailCarriere.class);

	// Classe principale de la channel
	private CConsultHarpege owner;
	// nationalité

	// flux xml : calculé qu'une seul fois, réaffiché ensuite
	private StringBuffer xml; 

	//
	private String num_carriere;
	private String num_element;
	private String num_chevron;
	private String num_echelon;

	/**
	 * Contructeur de la classe
	 * @param main
	 */
	public DetailCarriere(MainChannel main) {
		super(main);
		owner = (CConsultHarpege)main;
	}

	public Boolean setXML() throws FrameWorkException {
		
		if (xml != null) {
           xml = null;
		}
			xml = new StringBuffer(START_XML);
			xml.append(owner.getXmlIndividu());
			xml.append("<avoir>");
			if (this.getRuntimeData().getParameter("carriere") != null 
					&& this.getRuntimeData().getParameter("element") != null
					&& this.getRuntimeData().getParameter("echelon") != null
					&& this.getRuntimeData().getParameter("chevron") != null) 
			{
				num_carriere=this.getRuntimeData().getParameter("carriere");
				num_element=this.getRuntimeData().getParameter("element");
				num_echelon=this.getRuntimeData().getParameter("echelon");
				num_chevron=this.getRuntimeData().getParameter("chevron");
				xml.append(getXmlDetailCarriere());
				xml.append(getXmlOccupation());
				xml.append(getXmlAffectation());
			}
			xml.append("</avoir>");
			xml.append(END_XML);		

		setXML(xml.toString());
			
		return Boolean.TRUE;
	}
	
	public Boolean init(ChannelRuntimeData rd) throws PortalException, FrameWorkException {
		Boolean res = super.init(rd);
		return res;	
	}

	public StringBuffer getXmlDetailCarriere() throws FrameWorkException {
		
		StringBuffer res = new StringBuffer("<carriere>");
		String requete;
		Query qry = null;
		boolean error = false;

		if (owner.getNoIndividu() != null) { // la personne connectée à un no_individu
			try {
				qry = Config.getInstance().getConnexionDefault();
				requete="select vpc.no_seq_carriere,vpc.no_seq_element,vpc.c_echelon,vpc.c_chevron,to_char(decode(vpc.D_EFFET_CHEVRON,'',vpc.D_EFFET_ELEMENT,vpc.D_EFFET_CHEVRON),'DD/MM/YYYY') date_effet,ta.ll_type_acces, c.ll_corps, c.c_categorie, g.ll_grade, decode(pe.c_indice_brut,'',vpc.c_chevron,pe.c_indice_brut) indice_brut, i.c_indice_majore indice_majore, br.c_bap_referens, br.ll_bap_referens, ca.lc_categorie from v_periode_carriere vpc, element_carriere ec, type_acces ta, corps c, grade g, passage_echelon pe, indice i, carriere car, bap_referens br, categorie ca where vpc.no_dossier_pers=? and vpc.no_seq_carriere=? and vpc.no_seq_element=? and vpc.c_echelon=? ";
				/*
				 * S'il n'y a pas de chevron pour cette période de carrière
				 */
				if (num_chevron.compareTo("null") == 0) {
					requete+="and vpc.c_chevron is null ";
				}
				else {
					requete+="and vpc.c_chevron=? ";
				}
				requete+="and ec.no_dossier_pers=vpc.no_dossier_pers and ec.no_seq_carriere=vpc.no_seq_carriere and ec.no_seq_element=vpc.no_seq_element and car.no_dossier_pers=vpc.no_dossier_pers and car.no_seq_carriere=vpc.no_seq_carriere and br.c_bap_referens(+)=car.c_bap_referens and ta.c_type_acces=ec.c_type_acces and ca.c_categorie=c.c_categorie and c.c_corps=vpc.c_corps and g.c_grade=vpc.c_grade and pe.c_grade=vpc.c_grade and pe.c_echelon=vpc.c_echelon and i.c_indice_brut=decode(pe.c_indice_brut,'',vpc.c_chevron,pe.c_indice_brut)";
				qry.setSql(requete);
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, num_carriere);
				qry.getStmt().setString(3, num_element);
				qry.getStmt().setString(4, num_echelon);
				/*
				 * S'il y a un chevron, on passe le paramètre à la requête
				 */
				if (num_chevron.compareTo("null") != 0) {
					qry.getStmt().setString(5, num_chevron);
				}
				qry.select();
				while (qry.getRs().next()){
					res.append("<bap>").
					append(qry.getRs().getString("c_bap_referens")).
					append("</bap>");
					res.append("<lib_bap>").
					append(qry.getRs().getString("ll_bap_referens")).
					append("</lib_bap>");
					res.append("<categorie>").
					append(qry.getRs().getString("lc_categorie")).
					append("</categorie>");
					res.append("<typeacces>").
					append(qry.getRs().getString("ll_type_acces")).
					append("</typeacces>");
					res.append("<dateeffet>").
					append(qry.getRs().getString("date_effet")).
					append("</dateeffet>");
					res.append("<echelon>").
					append(qry.getRs().getString("c_echelon")).
					append("</echelon>");
					res.append("<chevron>").
					append(qry.getRs().getString("c_chevron")).
					append("</chevron>");
					res.append("<corps>").
					append(qry.getRs().getString("ll_corps")).
					append("</corps>");
					res.append("<indicebrut>").
					append(qry.getRs().getString("indice_brut")).
					append("</indicebrut>");
					res.append("<grade>").
					append(qry.getRs().getString("ll_grade")).
					append("</grade>");
					res.append("<indicemajore>").
					append(qry.getRs().getString("indice_majore")).
					append("</indicemajore>");
				}
			}
			catch (SQLException e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlDetailCarriere() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlDetailCarriere() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				if (error)
					throw new FrameWorkException();
		}		}

		res.append("</carriere>");

		return res;
	}

	public StringBuffer getXmlOccupation() throws FrameWorkException {
		
		StringBuffer res = new StringBuffer("<occupations>");
		Query qry = null;
		boolean error = false;
		String occupationfin = null;

		if (owner.getNoIndividu() != null) { // la personne connectée à un no_individu
			try {
				qry = Config.getInstance().getConnexionDefault();
				qry.setSql("select o.no_poste, to_char(o.d_deb_occupation,'DD/MM/YYYY') date_debut, to_char(o.d_fin_occupation,'DD/MM/YYYY') date_fin, c.no_poste_national, profil_poste from occupation o, poste c, element_carriere ec where o.no_dossier_pers=? and o.no_seq_carriere=? and c.no_poste=o.no_poste and ec.no_dossier_pers=o.no_dossier_pers and ec.no_seq_carriere=o.no_seq_carriere and ec.no_seq_element=? and (ec.d_effet_element between o.d_deb_occupation and decode(o.d_fin_occupation,'',sysdate,o.d_fin_occupation) or ec.d_fin_element between o.d_deb_occupation and decode(o.d_fin_occupation,'',sysdate,o.d_fin_occupation))");
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, num_carriere);
				qry.getStmt().setString(3, num_element);
				qry.select();
				while (qry.getRs().next()){
					res.append("<occupation>");
					res.append("<poste>").
					append(qry.getRs().getString("no_poste")).
					append("</poste>");
					occupationfin=(qry.getRs().getString("date_fin") == null) ? "" : qry.getRs().getString("date_fin");
					if (occupationfin != "") {
						res.append("<periodeoccupation>Du ").
						append(qry.getRs().getString("date_debut")).
						append(" au ").
						append(qry.getRs().getString("date_fin")).
						append("</periodeoccupation>");
					}
					else {
						res.append("<periodeoccupation>A partir du ").
						append(qry.getRs().getString("date_debut")).
						append("</periodeoccupation>");
					}
					res.append("<postenational>").
					append(qry.getRs().getString("no_poste_national")).
					append("</postenational>");
					res.append("<profil>").
					append(qry.getRs().getString("profil_poste")).
					append("</profil>");
					res.append("</occupation>");
				}
			}
			catch (SQLException e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlOccupation() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlOccupation() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				if (error)
					throw new FrameWorkException();
		}		}

		res.append("</occupations>");

		return res;
	}

	public StringBuffer getXmlAffectation() throws FrameWorkException {
		
		StringBuffer res = new StringBuffer("<affectations>");
		String periodefin = null;
		Query qry = null;
		boolean error = false;

		if (owner.getNoIndividu() != null) { // la personne connectée à un no_individu
			try {
				qry = Config.getInstance().getConnexionDefault();
				qry.setSql("select to_char(a.d_deb_affectation,'DD/MM/YYYY') datedebut, to_char(a.d_fin_affectation,'DD/MM/YYYY') datefin, s.ll_structure, o.no_poste from affectation a, structure s, occupation o,element_carriere ec where a.no_dossier_pers=? and ec.no_dossier_pers=a.no_dossier_pers and o.no_occupation=a.no_occupation and s.c_structure=a.c_structure and ec.no_seq_carriere=? and ec.no_seq_element=? and ec.d_effet_element between a.d_deb_affectation and decode(a.d_fin_affectation,'',sysdate,a.d_fin_affectation) and a.no_seq_carriere=ec.no_seq_carriere");
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, num_carriere);
				qry.getStmt().setString(3, num_element);
				qry.select();
				while (qry.getRs().next()){
					res.append("<affectation>");
					periodefin=(qry.getRs().getString("datefin") == null) ? "" : qry.getRs().getString("datefin");
					res.append("<structureaff>").
					append(qry.getRs().getString("ll_structure")).
					append("</structureaff>");

					if (periodefin != "") {
						res.append("<periodeaff>Du ").
						append(qry.getRs().getString("datedebut")).
						append(" au ").
						append(periodefin).
						append("</periodeaff>");
					}
					else {
						res.append("<periodeaff>A partir du ").
						append(qry.getRs().getString("datedebut")).
						append("</periodeaff>");
					}
					res.append("<posteaff>").
					append(qry.getRs().getString("no_poste")).
					append("</posteaff>");
					res.append("</affectation>");
				}
			}
			catch (SQLException e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlAffectation() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailCarriere::getXmlAffectation() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				if (error)
					throw new FrameWorkException();
			}
		}

		res.append("</affectations>");

		return res;
	}
}
