/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2007 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 General Public License as published by
 * the Free Software Foundation; either version 2 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 General Public License for more details.
 *
 * You should have received a copy of the GNU 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.plugins.paye;

import fr.gouv.finances.cp.utils.ui.DisplayExceptionDlg;
import fr.gouv.finances.cp.xemelios.common.config.ListeResultatModel;
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.mysql.MySqlDataResultSet;
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;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.SwingUtilities;
import org.apache.log4j.Logger;

/**
 *
 * @author chm
 */
public class SommeHeuresSup extends SearchPlugin {
    private static final Logger logger = Logger.getLogger(SommeHeuresSup.class);
    private String opNbHeures,start,end, nbHeures;
    
    /** Creates a new instance of SommeHeuresSup */
    public SommeHeuresSup() {
        super();
    }

    public void computeParameters() throws Exception {
        nbHeures = getParameters().get("VALEUR:nbHeures");
        opNbHeures = getParameters().get("OPERATEUR:nbHeures");
        String yearStart = getParameters().get("VALEUR:yearStart");
        String monthStart = getParameters().get("VALEUR:monthStart");
        String yearEnd = getParameters().get("VALEUR:yearEnd");
        String monthEnd = getParameters().get("VALEUR:monthEnd");
        
        start = yearStart+monthStart;
        end = yearEnd+monthEnd;
    }

    @Override
    public DataResultSet doInBackground() throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT AGENT_MATRICULE, SUM(HEURES_SUPP) FROM PAYE_ETATPAYE_INDEX_PAYE WHERE ANNEE_MOIS>=? AND ANNEE_MOIS<=? GROUP BY AGENT_MATRICULE HAVING SUM(HEURES_SUPP)").append(opNbHeures).append(nbHeures);
        Connection con = null;
        PreparedStatement ps = null;
        MySqlDataLayer dl = (MySqlDataLayer)DataLayerManager.getImplementation();
        TDocument pc = dl.getPersistenceConfig(getEtatModel().getParent()).getLayer("mysql").getDocument(getEtatModel().getParent().getId());
        TEtat sec = pc.getEtat(getEtatModel().getId());
        String tableName = sec.getIndexTable("T1").getBaseName();
        try {
            con = dl.getConnection();
logger.debug(sb.toString());
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,start);
            ps.setString(2,end);
            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) {
	            sb = new StringBuilder();
	            sb.append("SELECT SQL_CALC_FOUND_ROWS DOC_ID, AGENT_MATRICULE FROM ").append(tableName);
	            sb.append(" WHERE ANNEE_MOIS>=").append(start).append(" AND ANNEE_MOIS<=").append(end).append(" AND AGENT_MATRICULE IN (");
	            for(String s:matricules) sb.append("'").append(s).append("',");
	            sb.deleteCharAt(sb.length()-1);
	            sb.append(") AND COLLECTIVITE='").append(getCollectivite().key).append("' AND BUDGET='").append(getBudget().key).append("' ");
                    ListeResultatModel lrm = getElementModel().getListeResultat();
	            return new MySqlDataResultSet(getElementModel(),getCollectivite(),getBudget(),"/n:DocumentPaye/n:DonneesIndiv/n:PayeIndivMensuel",sb.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,getElementModel().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();
        }
    }
    
}
