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;

/**
 * DetailContrat<br>
 * <br>
 * Gestion de l'affichage du détail d'une période de contrat 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 DetailContrat 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; 

	private String numero_contrat;
	private String avenant_contrat;

	/**
	 * Contructeur de la classe
	 * @param main
	 */
	public DetailContrat(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());
		if (this.getRuntimeData().getParameter("contrat") != null 
				&& this.getRuntimeData().getParameter("avenant") != null) 
		{
			numero_contrat=this.getRuntimeData().getParameter("contrat");
			avenant_contrat=this.getRuntimeData().getParameter("avenant");
			xml.append(getXmlDetailContrat());
			xml.append(getXmlOccupation());
			xml.append(getXmlAffectation());
		}
		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 contrats d'un individu sous forme d'un flux xml 
	 * 
	 */
	public StringBuffer getXmlDetailContrat() throws FrameWorkException {
		
		StringBuffer res = new StringBuffer("<contrat>");
		Query qry = null;
		boolean error = false;
		String datefin = null;
		String datesignature = null;
		String datefinexecution = null;
		String grade = null;
		String categorie = null;
		String fonctions = null;

		if (owner.getNoIndividu() != null) { // la personne connectée à un no_individu
			try {
				qry = Config.getInstance().getConnexionDefault();
				qry.setSql("select ct.no_contrat_travail, ct.c_type_contrat_trav,tct.ll_type_contrat_trav from contrat_travail ct, type_contrat_travail tct where ct.no_dossier_pers=? and ct.no_contrat_travail=? and tct.c_type_contrat_trav=ct.c_type_contrat_trav");
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, numero_contrat);
				qry.select();
				while (qry.getRs().next()){
					res.append("<numero>").
					append(Utils.xmlEscape(qry.getRs().getString("no_contrat_travail"))).
					append("</numero>");
					res.append("<typecontrat>").
					append(Utils.xmlEscape(qry.getRs().getString("ll_type_contrat_trav"))).
					append("</typecontrat>");
				}

				qry.setSql("select ca.no_avenant, ca.reference_contrat, to_char(ca.d_ref_ctr_avenant,'DD/MM/YYYY') dateavenant, to_char(ca.d_deb_contrat_trav,'DD/MM/YYYY') datedebutcontrat, to_char(ca.d_fin_contrat_trav,'DD/MM/YYYY') datefincontrat, ca.fonction_ctr_trav, to_char(ca.d_fin_execution,'DD/MM/YYYY') datefinexecution, ca.c_categorie, g.ll_grade from contrat_avenant ca, grade g where ca.no_dossier_pers=? and ca.no_contrat_travail=? and ca.no_avenant=? and g.c_grade=ca.c_grade");
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, numero_contrat);
				qry.getStmt().setString(3, avenant_contrat);
				qry.select();

				while (qry.getRs().next()){
					res.append("<avenant>").
					append(Utils.xmlEscape(qry.getRs().getString("no_avenant"))).
					append("</avenant>");
					res.append("<reference>").
					append(Utils.xmlEscape(qry.getRs().getString("reference_contrat"))).
					append("</reference>");
					datesignature=(qry.getRs().getString("dateavenant") == null) ? "" : qry.getRs().getString("dateavenant");
					res.append("<datesignature>").
					append(Utils.xmlEscape(datesignature)).
					append("</datesignature>");
					datefin=(qry.getRs().getString("datefincontrat") == null) ? "" : qry.getRs().getString("datefincontrat");
					if (datefin != "") {
						res.append("<periode>Du ").
						append(Utils.xmlEscape(qry.getRs().getString("datedebutcontrat"))).
						append(" au ").
						append(Utils.xmlEscape(datefin)).
						append("</periode>");
					}
					else {
						res.append("<periode>A partir du ").
						append(Utils.xmlEscape(qry.getRs().getString("datedebutcontrat"))).
						append("</periode>");
					}
					fonctions=(qry.getRs().getString("fonction_ctr_trav") == null) ? "" : qry.getRs().getString("fonction_ctr_trav");
					res.append("<fonction>").
					append(Utils.xmlEscape(fonctions)).
					append("</fonction>");
					datefinexecution=(qry.getRs().getString("datefinexecution") == null) ? "" : qry.getRs().getString("datefinexecution");
					res.append("<datefinexecution>").
					append(Utils.xmlEscape(datefinexecution)).
					append("</datefinexecution>");
					res.append("<categorie>").
					append(Utils.xmlEscape(qry.getRs().getString("c_categorie"))).
					append("</categorie>");
					res.append("<grade>").
					append(Utils.xmlEscape(qry.getRs().getString("ll_grade"))).
					append("</grade>");
				}
			}
			catch (SQLException e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailContrat::getXmlDetailContrat() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailContrat::getXmlDetailContrat() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				if (error)
					throw new FrameWorkException();
			}
		}

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

		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, contrat_travail ct where o.no_dossier_pers=? and o.no_contrat_travail=? and c.no_poste=o.no_poste and ct.no_dossier_pers=o.no_dossier_pers and ct.no_contrat_travail=o.no_contrat_travail and ct.D_DERN_AVENANT 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, numero_contrat);
				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("DetailContrat::getXmlDetailContrat() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailContrat::getXmlDetailContrat() : 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();

				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,contrat_avenant ca where a.no_dossier_pers=? and ca.no_dossier_pers=a.no_dossier_pers and o.no_occupation=a.no_occupation and s.c_structure=a.c_structure and ca.no_contrat_travail=? and ca.no_avenant=? and ca.d_deb_contrat_trav between a.d_deb_affectation and decode(a.d_fin_affectation,null,sysdate,a.d_fin_affectation) and a.no_contrat_travail=ca.no_contrat_travail");
				qryAdr = Config.getInstance().getConnexionDefault();
				qry.getStmt().setString(1, owner.getNoIndividu());
				qry.getStmt().setString(2, numero_contrat);
				qry.getStmt().setString(3, avenant_contrat);
				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("DetailContrat::getXmlDetailContrat() : Erreur SQL " + e);
		        }
				error = true;
				throw new FrameWorkException();
			}
			catch (Exception e) {
				if (log.isDebugEnabled()){
		        	log.error("DetailContrat::getXmlDetailContrat() : Erreur SQL " + e);
		        }
				error = true;
			}
			finally {
				qry.close();
				qryAdr.close();
				if (error)
					throw new FrameWorkException();
			}
		}

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

		return res;
	}
}
