/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2005 C.Marchand - christophe.marchand@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package fr.gouv.finances.cp.xemelios.quittancement.plugins;

import fr.gouv.finances.cp.xemelios.data.impl.mysql.MySqlDataResultSet;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.SwingUtilities;

import org.apache.log4j.Logger;

import fr.gouv.finances.cp.utils.ui.DisplayExceptionDlg;
import fr.gouv.finances.cp.xemelios.common.config.ListeResultatModel;
import fr.gouv.finances.cp.xemelios.common.config.PropertyModel;
import fr.gouv.finances.cp.xemelios.data.DataLayerManager;
import fr.gouv.finances.cp.xemelios.data.DataResultSet;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.cp.xemelios.plugins.SearchPlugin;
import fr.gouv.finances.cp.xemelios.ui.search.ResultDisplayerThread;

/**
 * Plugin permettant de rechercher les agents dont le revenu a varie de plus
 * d'un certain seuil entre 2 periodes
 * @author chm
 */
public class Variations extends SearchPlugin {
    private static final Logger logger = Logger.getLogger(Variations.class);
    protected String year1, year2, month1, month2, delta, typeVariation, tag, attribute, valeurSql=null;
    protected int compareMethod = 0;
    protected static final int METHOD_PERCENT = 1;
    protected static final int METHOD_VALUE = 2;
    protected double limite=0.0;
//    private String mtNet, op_mtNet;
//    private String mtBrut, op_mtBrut;
//    private String agenStatut, op_agenStatut;
//    private String emploi, op_emploi;
//    private String service, op_service;
//    private String grade, op_grade;
//    private String indice, op_indice;

    /**
     * 
     */
    public Variations() {
        super();
    }

    @Override
	public void computeParameters() throws Exception {
        tag="MtNet";
        attribute="V";
        Vector<PropertyModel> props = getPluginModel().getProperties();
        for(PropertyModel property:props) {
            if(property.getName().equals("attribute")) {
                attribute = property.getValue();
            } else if(property.getName().equals("valeur-sql")) {
                valeurSql = property.getValue();
            }
        }
        year1 = getParameters().get("VALEUR:year1");
        month1 = getParameters().get("VALEUR:month1");
        year2 = getParameters().get("VALEUR:year2");
        month2 = getParameters().get("VALEUR:month2");
        delta = getParameters().get("VALEUR:delta");
        limite = Double.parseDouble(delta); 
        typeVariation = getParameters().get("VALEUR:typeVariation");
        if(typeVariation.equals("percent")) compareMethod=METHOD_PERCENT;
        else if(typeVariation.equals("euro")) compareMethod=METHOD_VALUE;
        if(getParameters().get("VALEUR:tag")!=null) tag=getParameters().get("VALEUR:tag");
//        mtNet = getParameters().get("VALEUR:mtnet");
//        op_mtNet = getParameters().get("OPERATEUR:mtnet");
//        mtBrut = getParameters().get("VALEUR:mtbrut");
//        op_mtBrut = getParameters().get("OPERATEUR:mtbrut");
//        agenStatut = getParameters().get("VALEUR:agent_statut");
//        op_agenStatut = "xem:StringEquals";
//        emploi = getParameters().get("VALEUR:emploi");
//        op_emploi = getParameters().get("OPERATEUR:emploi");
//        service = getParameters().get("VALEUR:service");
//        op_service = getParameters().get("OPERATEUR:service");
//        grade = getParameters().get("VALEUR:grade");
//        op_grade = getParameters().get("OPERATEUR:grade");
//        indice = getParameters().get("VALEUR:indice");
//        op_indice = getParameters().get("OPERATEUR:indice");        
        StringBuffer error = new StringBuffer();
        boolean hasError = false;
//        try {
//        	if (mtNet != null && !"".equals(mtNet)) {
//        		new BigDecimal (mtNet);
//        	}
//        } catch (NumberFormatException nfe) {
//        	error.append("Le champ montant net ne contient pas un montant valide\n");
//        	hasError = true;
//        }
//        try {
//        	if (mtBrut != null && !"".equals(mtBrut)) {
//        		new BigDecimal (mtBrut);
//        	}
//        } catch (NumberFormatException nfe) {
//        	error.append("Le champ montant brut ne contient pas un montant valide\n");
//        	hasError = true;
//        }
//        try {
//        	if (indice != null && !"".equals(indice)) {
//        		new BigDecimal (indice);
//        	}
//        } catch (NumberFormatException nfe) {
//        	error.append("Le champ montant indice ne contient pas un nombre valide\n");
//        	hasError = true;
//        }
        if(hasError) {
            logger.error(error.toString());
            throw new Exception(error.toString());
        }
    }
    @Override
	public DataResultSet doInBackground() throws Exception {
        computeParameters();
        MySqlDataLayer dl = (MySqlDataLayer)DataLayerManager.getImplementation();
        TDocument pc = dl.getPersistenceConfig(getEtatModel().getParent()).getLayer("mysql").getDocument(getEtatModel().getParent().getId());
        TEtat sec = pc.getEtat(getEtatModel().getId());

        StringBuilder request = new StringBuilder();
        String tableName = sec.getIndexTable("PES_FACTURE_PESFACTURE_FACTUREINDIV_IX").getBaseName();
        
        request.append("SELECT Q1.IDTIERS||'-'||Q1.NUMEROCONTRAT FROM ").append(tableName).append(" Q1, ").append(tableName).append(" Q2 ");
        request.append(" WHERE Q1.COLLECTIVITE='").append(getCollectivite().key).append("' AND Q1.BUDGET='").append(getBudget().key).append("' AND Q2.COLLECTIVITE=Q1.COLLECTIVITE AND Q2.BUDGET=Q1.BUDGET ");
        request.append("AND Q1.ANNEE_DATE_EMISSION=").append(year1).append(" AND Q1.MOIS_DATE_EMISSION='").append(month1).append("' ");
        request.append("AND Q2.ANNEE_DATE_EMISSION=").append(year2).append(" AND Q2.MOIS_DATE_EMISSION='").append(month2).append("' ");
        request.append("AND Q1.IDTIERS=Q2.IDTIERS AND Q1.NUMEROCONTRAT=Q2.NUMEROCONTRAT ");
        /* MONTANT NET */
//        if (mtNet!=null && !"".equals(mtNet)) {
//        	request.append (" AND i1.MT_NET ").append (op_mtNet).append (mtNet).append (" ");
//        }
//        /* MONTANT BRUT */
//        if (mtBrut!=null && !"".equals(mtBrut)) {
//        	request.append (" AND i1.MT_BRUT ").append (op_mtBrut).append (mtBrut).append (" ");
//        }
//        /* Grade */
//        if (grade!=null && !"".equals(grade)) {
//        	request.append (" AND i1.AGENT_GRADE ").append (convertOpString (op_grade, grade)).append (" ");
//        }
//        /* Statut */
//        if (agenStatut!=null && !"".equals(agenStatut)) {
//        	request.append (" AND i1.AGENT_STATUT ").append (convertOpString (op_agenStatut, agenStatut)).append (" ");
//        }
//        /* Emploi */
//        if (emploi!=null && !"".equals(emploi)) {
//        	request.append (" AND i1.AGENT_METIER ").append (convertOpString (op_emploi, emploi)).append (" ");
//        }
//        /* Service */
//        if (service!=null && !"".equals(service)) {
//        	request.append (" AND i1.SERVICE ").append (convertOpString (op_service, service)).append (" ");
//        }
//        /* Indice */
//        if (indice!=null && !"".equals(indice)) {
//        	request.append (" AND i1.AGENT_INDICE ").append (op_indice).append (indice).append (" ");
//        }

        String column=("MtTotalHT".equals(tag)?"MTTOTALHT":"MTTOTALTTC");
        if(valeurSql!=null) column=valeurSql;
        if("percent".equals(typeVariation)) {
            request.append("AND ABS((Q2.").append(column).append("-Q1.").append(column).append(")/Q1.").append(column).append("*100)>").append(limite);

        } else {
            request.append("AND ABS(Q2.").append(column).append("-Q1.").append(column).append(")>").append(limite);
        }
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = dl.getConnection();
logger.debug(request.toString());
            ps = con.prepareStatement(request.toString());
            ResultSet rs = ps.executeQuery();
            ArrayList<String> matricules = new ArrayList<String>();
            while(rs.next()) {
                String matr = rs.getString(1);
                matricules.add(matr);
            }
            if(matricules.size()>0) {
	            request = new StringBuilder();
	            request.append("SELECT SQL_CALC_FOUND_ROWS DOC_ID, AGENT_MATRICULE FROM ").append(tableName);
	            request.append(" WHERE (( ANNEE_DATE_EMISSION=").append(year1).append(" AND MOIS_DATE_EMISSION='").append(month1).append("') OR (ANNEE_DATE_EMISSION=").append(year2).append(" AND MOIS_DATE_EMISSION='").append(month2).append("')) AND IDTIERS||'-'||NUMEROCONTRAT IN (");
	            for(String s:matricules) request.append("'").append(s).append("',");
	            request.deleteCharAt(request.length()-1);
	            request.append(") AND COLLECTIVITE='").append(getCollectivite().key).append("' AND BUDGET='").append(getBudget().key).append("' ");
	            request.append("ORDER BY IDTIERS ASC, NUMEROCONTRAT ASC, ANNEE_DATE_EMISSION ASC, MOIS_DATE_EMISSION ASC");
                    ListeResultatModel lrm = getListeResultat();
	            return new MySqlDataResultSet(getElementModel(),getCollectivite(),getBudget(),"/n:DOC_PES_Facture/n:PESFacture/n:FactureIndiv",request.toString(),lrm,dl);
            }
        } catch(SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if(con!=null) dl.releaseConnection(con);
        }
        return DataLayerManager.getImplementation().getEmptyDataResultSet();
    }
    @Override
	public void done() {
        try {
            DataResultSet rs = (DataResultSet)get();
            Runnable code = new ResultDisplayerThread(rs,getListeResultat(),getStart(),getElementModel(),getSearchWindow());
            if(SwingUtilities.isEventDispatchThread()) code.run();
            else try { SwingUtilities.invokeAndWait(code); } catch(Throwable t) {}
            firePropertyChange("COMPLETED",Boolean.FALSE,Boolean.TRUE);
        } catch(Throwable t) {
            DisplayExceptionDlg.displayException(getOwner(),t);
        } finally {
            setProgressVisible(false);
            super.done();
        }
    }

}
