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;

/**
 * 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,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, ca.lc_categorie, 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,br.c_bap_referens,br.ll_bap_referens from v_periode_carriere vpc, element_carriere ec, type_acces ta, corps c, grade g, categorie ca, passage_echelon pe, indice i, indice i2, bap_referens br, carriere car where vpc.no_dossier_pers=? and vpc.no_seq_carriere=? and vpc.no_seq_element=? and vpc.c_echelon=? ";
				 requete="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, ca.lc_categorie, 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,br.c_bap_referens,br.ll_bap_referens from v_periode_carriere vpc, element_carriere ec, type_acces ta, corps c, grade g, categorie ca, indice_brut i, majoration_indice mi, majoration_indice mi2, bap_referens br, carriere car, grade_grille_indiciaire ggi, grille_indiciaire_echelon gie 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 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 (pe.c_indice_brut=i.c_indice_brut(+)) and (vpc.c_chevron=i2.c_indice_brut(+)) 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";
				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 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 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 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 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";

				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,null,sysdate,o.d_fin_occupation) or ec.d_fin_element between o.d_deb_occupation and decode(o.d_fin_occupation,null,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(Utils.xmlEscape(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(Utils.xmlEscape(qry.getRs().getString("date_debut"))).
						append(" au ").
						append(Utils.xmlEscape(qry.getRs().getString("date_fin"))).
						append("</periodeoccupation>");
					}
					else {
						res.append("<periodeoccupation>A partir du ").
						append(Utils.xmlEscape(qry.getRs().getString("date_debut"))).
						append("</periodeoccupation>");
					}
					res.append("<postenational>").
					append(Utils.xmlEscape(qry.getRs().getString("no_poste_national"))).
					append("</postenational>");
					res.append("<profil>").
					append(Utils.xmlEscape(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;
		Query qryAdr = null;
		boolean error = false;
		String codeStructure = null;

		if (owner.getNoIndividu() != null) { // la personne connectée à un no_individu
			try {
				qry = Config.getInstance().getConnexionDefault();
				qryAdr = 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, s.c_structure, o.no_poste, decode(a.num_quot_affectation,null,'100',a.num_quot_affectation) quotite 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,null,trunc(sysdate),a.d_fin_affectation)) or (ec.d_fin_element between a.d_deb_affectation and decode(a.d_fin_affectation,null,trunc(sysdate),a.d_fin_affectation)))");

				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(Utils.xmlEscape(qry.getRs().getString("ll_structure"))).
					append("</structureaff>");
					codeStructure=qry.getRs().getString("c_structure");
					if (periodefin != "") {
						res.append("<periodeaff>Du ").
						append(Utils.xmlEscape(qry.getRs().getString("datedebut"))).
						append(" au ").
						append(Utils.xmlEscape(periodefin)).
						append("</periodeaff>");
					}
					else {
						res.append("<periodeaff>A partir du ").
						append(Utils.xmlEscape(qry.getRs().getString("datedebut"))).
						append("</periodeaff>");
					}
					res.append("<posteaff>").
					append(Utils.xmlEscape(qry.getRs().getString("no_poste"))).
					append("</posteaff>");
					res.append("<quotite>").
					append(Utils.xmlEscape(qry.getRs().getString("quotite"))).
					append("</quotite>");
					qryAdr.setSql("select no_voie_a,voirie.l_voie,nom_voie_a,localite_a,code_postal_a,ville_a from voirie,adresse_administrat,localisation_structure,local where voirie.c_voie(+)=adresse_administrat.c_voie and local.id_adresse_admin=adresse_administrat.id_adresse_admin and localisation_structure.c_local=local.c_local and localisation_structure.c_structure=?");
					qryAdr.getStmt().setString(1, codeStructure);
					qryAdr.select();
					while (qryAdr.getRs().next()){
						res.append("<rueaff>").
						append(Utils.xmlEscape(qryAdr.getRs().getString("no_voie_a"))).append(" ").
						append(Utils.xmlEscape(qryAdr.getRs().getString("l_voie"))).append(" ").
						append(Utils.xmlEscape(qryAdr.getRs().getString("nom_voie_a"))).
						append("</rueaff>");
						res.append("<localiteaff>").
						append(Utils.xmlEscape(qryAdr.getRs().getString("localite_a"))).
						append("</localiteaff>");
						res.append("<villeaff>").
						append(Utils.xmlEscape(qryAdr.getRs().getString("code_postal_a"))).append(" ").
						append(Utils.xmlEscape(qryAdr.getRs().getString("ville_a"))).
						append("</villeaff>");
					}
					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();
				qryAdr.close();
				if (error)
					throw new FrameWorkException();
			}
		}

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

		return res;
	}
}
