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;
import org.esupportail.portal.channels.gestion.CConsultHarpege.Utils;

/**
 * Carriere<br>
 * <br>
 * Gestion de l'affichage de la 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 Carriere extends SubChannel  {

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

	// Classe principale de la channel
	private CConsultHarpege owner;

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

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

	public Boolean setXML() throws FrameWorkException {

		if (xml == null) {
			xml = new StringBuffer(START_XML);
			xml.append(owner.getXmlIndividu());
			xml.append(getXmlCarriere());

			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;	
	}

	/**
	 * Méthode qui retourne les éléments de carrière sous forme d'un flux xml 
	 * 
	 */
	public StringBuffer getXmlCarriere() throws FrameWorkException {

		StringBuffer res = new StringBuffer("<carrieres>");
		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 vpc.no_seq_carriere,vpc.no_seq_element,vpc.c_echelon,vpc.c_chevron, to_char(decode(vpc.D_EFFET_CHEVRON,null,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,null,vpc.c_chevron,pe.c_indice_brut) indice_brut, decode(i.c_indice_majore, null, i2.c_indice_majore, i.c_indice_majore) indice_majore from v_periode_carriere vpc, element_carriere ec, type_acces ta, corps c, grade g, passage_echelon pe, indice i, indice i2 where vpc.no_dossier_pers=? 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 ta.c_type_acces(+)=ec.c_type_acces 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 (pe.c_indice_brut=i.c_indice_brut(+)) and (vpc.c_chevron=i2.c_indice_brut(+)) and decode(vpc.d_effet_chevron,null,vpc.d_effet_element,vpc.d_effet_chevron) <= sysdate order by vpc.d_arrete");
				qry.setSql("select vpc.no_seq_carriere,vpc.no_seq_element,vpc.c_echelon,vpc.c_chevron, to_char(decode(vpc.D_EFFET_CHEVRON,null,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(gie.c_indice_brut,null,vpc.c_chevron,gie.c_indice_brut) indice_brut, decode(gie.c_indice_brut, null, mi.c_indice_majore, mi2.c_indice_majore) indice_majore from v_periode_carriere vpc, element_carriere ec, type_acces ta, corps c, grade g, indice_brut i, majoration_indice mi, majoration_indice mi2, grade_grille_indiciaire ggi, grille_indiciaire_echelon gie where vpc.no_dossier_pers=? 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 ta.c_type_acces(+)=ec.c_type_acces and c.c_corps=vpc.c_corps and g.c_grade=vpc.c_grade and ggi.c_grade=vpc.c_grade and gie.c_echelon=vpc.c_echelon and (gie.c_indice_brut=i.c_indice_brut(+)) and (vpc.c_chevron=mi.c_indice_chevron(+)) and (gie.c_indice_brut=mi2.c_indice_chevron(+)) and decode(vpc.d_effet_chevron,null,vpc.d_effet_element,vpc.d_effet_chevron) <= sysdate and ggi.no_grille_indiciaire=gie.no_grille_indiciaire and ggi.d_fin_validite is null and mi.d_fin_validite is null and mi2.d_fin_validite is null order by vpc.d_arrete"); 

				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.select();
				while (qry.getRs().next()){
					res.append("<carriere>");
					res.append("<no_carriere>").
					append(Utils.xmlEscape(qry.getRs().getString("no_seq_carriere"))).
					append("</no_carriere>");
					res.append("<no_element>").
					append(Utils.xmlEscape(qry.getRs().getString("no_seq_element"))).
					append("</no_element>");
					res.append("<date>").
					append(Utils.xmlEscape(qry.getRs().getString("date_effet"))).
					append("</date>");
					res.append("<type>").
					append(Utils.xmlEscape(qry.getRs().getString("ll_type_acces"))).
					append("</type>");
					res.append("<corps>").
					append(Utils.xmlEscape(qry.getRs().getString("ll_corps"))).
					append("</corps>");
					res.append("<grade>").
					append(Utils.xmlEscape(qry.getRs().getString("ll_grade"))).
					append("</grade>");
					res.append("<categorie>").
					append(Utils.xmlEscape(qry.getRs().getString("c_categorie"))).
					append("</categorie>");
					res.append("<echelon>").
					append(Utils.xmlEscape(qry.getRs().getString("c_echelon"))).
					append("</echelon>");
					res.append("<chevron>").
					append(qry.getRs().getString("c_chevron")).
					append("</chevron>");
					res.append("<indice>").
					append(Utils.xmlEscape(qry.getRs().getString("indice_majore"))).
					append("</indice>");
					res.append("</carriere>");
				}
			}
			catch (SQLException e) {
				if (log.isDebugEnabled()){
		        	log.error("Carriere::getXmlCarriere() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("Carriere::getXmlCarriere() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				if (error)
					throw new FrameWorkException();
			}
		}

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

		return res;
	}
}
