 /*
 * Copyright 
 *   2008 axYus - www.axyus.com
 *   2008 jp.tessier - jean-philippe.tessier@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.plugin.generatorEdmn;

import fr.gouv.finances.dgfip.xemelios.common.config.DocumentModel;
import java.io.File;
import java.io.FilenameFilter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import fr.gouv.finances.cp.utils.ui.DisplayExceptionDlg;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.dgfip.xemelios.auth.XemeliosUser;
import fr.gouv.finances.dgfip.xemelios.data.DataConfigurationException;
import fr.gouv.finances.dgfip.xemelios.data.DataLayerManager;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import fr.gouv.finances.dgfip.xemelios.importers.DefaultImporter;
import java.lang.reflect.Constructor;

/**
 *
 * @author chm
 */
public class EdmnImporter extends DefaultImporter {

    private static final Logger logger = Logger.getLogger(EdmnImporter.class);
    private static String TABLE_INDEX = EdmnImporteurWorker.TABLE_INDEX;
    
    private static String TABLE_BUDGETS = EdmnImporteurWorker.TABLE_BUDGETS;
    private static String TABLE_ORDOS = EdmnImporteurWorker.TABLE_ORDOS;
    private static String TABLE_COMPTABLES = EdmnImporteurWorker.TABLE_COMPTABLES;
    private static String TABLE_MINISTERES = EdmnImporteurWorker.TABLE_MINISTERES;
    
    // Variables de progression d'avancement de la progrsse barre
    private static ProgressFrame progress;
    private DlgGenEdmn dlg;

    public EdmnImporter() {
        super(new XemeliosUser() {

            public String getId() {
                return "edmn importer user";
            }

            public String getDisplayName() {
                return getId();
            }

            public boolean hasRole(String role) {
                return true;
            }

            public boolean hasDocument(String document) {
                return true;
            }

            public boolean hasCollectivite(String collectivite, DocumentModel dm) {
                return true;
            }
        });
    }

    public EdmnImporter(DlgGenEdmn dlg) {
        this();
        this.dlg = dlg;
    }

    /**
     * @author jp.tessier
     * @param f : file EDMN
     * @param index : index of correspondance NumLiasseLocale <=> NumLiasseNationnale
     * @throws Exception
     */
    public void importFile(File f, File index, File budgets, File ordos,File comptas,File output) throws Exception {
        Connection con = null;
        try {
//            MySqlDataLayer msdl = (MySqlDataLayer) DataLayerManager.getImplementation();
            con = PoolManager.getInstance().getConnection();
            createDataBase(con);
            createDataBaseBudgets(con);
            createDataBaseOrdos(con);
            createDataBaseComptables(con);

            EdmnImporteurWorker eiw = new EdmnImporteurWorker(con,budgets,ordos,comptas,output,dlg);
         	
            File[] tabF = {f, index,budgets,ordos,comptas};
            progress = new ProgressFrame(dlg, eiw, tabF);
            progress.setDisplayFeedback(true);

            eiw.setProgressDlg(progress);
            eiw.setFilesToTreat(tabF);
            eiw.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
            logger.error("doImport", ex);
            new DisplayExceptionDlg(dlg, ex);
        } finally {
            if(progress!=null && progress.pg!=null)
                progress.pg.setVisible(false);
        }
    }
        

    private void createDataBase(Connection con) throws SQLException {
        StringBuilder sb = new StringBuilder();
        PreparedStatement ps = null;
        try {
            // Creation de la table si inexistente
            sb.append("CREATE TEMPORARY TABLE IF NOT EXISTS " + TABLE_INDEX + " ( ");    //TEMPORARY
            sb.append("ANNEE VARCHAR(2) NOT NULL, ");
            sb.append("TRIMESTRE VARCHAR(1) NOT NULL, ");
            sb.append("COMPTABLE VARCHAR(6) NOT NULL, ");
            sb.append("MINISTERE VARCHAR(3) NOT NULL, ");
            sb.append("LOCALE VARCHAR(4) NOT NULL, ");
            sb.append("GESTION VARCHAR(3) NOT NULL, ");
            sb.append("SECTION VARCHAR(3) NOT NULL, ");
            sb.append("NATIONALE VARCHAR(6) NOT NULL, ");
            sb.append("INDEX toto (ANNEE,TRIMESTRE,COMPTABLE,MINISTERE,LOCALE)");
            sb.append(")");
            ps = con.prepareStatement(sb.toString());
            ps.execute();
            // Vidage de la table d'index
            sb = new StringBuilder();
            sb.append("TRUNCATE TABLE " + TABLE_INDEX);
            ps = con.prepareStatement(sb.toString());
            ps.execute();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseBudgets(Connection con) throws SQLException {
        StringBuilder sb = new StringBuilder();
        PreparedStatement ps = null;
        try {
        	// Creation de la table si inexistente
            sb.append("CREATE TEMPORARY TABLE IF NOT EXISTS " + TABLE_BUDGETS + " ( ");  //TEMPORARY
            sb.append("CODE VARCHAR(16) NOT NULL, ");
            sb.append("LIBELLE VARCHAR(250) NOT NULL, ");
            sb.append("INDEX budgets (CODE)");
            sb.append(")");
            ps = con.prepareStatement(sb.toString());
            ps.execute();
            // Vidage de la table d'index budgets
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_BUDGETS);
            ps.execute();

        	// Creation de la table si inexistente
            sb = new StringBuilder();
            sb.append("CREATE TEMPORARY TABLE IF NOT EXISTS " + TABLE_MINISTERES + " ( ");  //
            sb.append("CODE VARCHAR(2) NOT NULL, ");
            sb.append("LIBELLE VARCHAR(250) NOT NULL, ");
            sb.append("INDEX ministere (CODE)");
            sb.append(")");
            ps = con.prepareStatement(sb.toString());
            ps.execute();
            // Vidage de la table d'index budgets
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_MINISTERES);
            ps.execute();

        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseOrdos(Connection con) throws SQLException {
        StringBuilder sb = new StringBuilder();
        PreparedStatement ps = null;
        try {
            // Creation de la table si inexistente
            sb.append("CREATE TEMPORARY TABLE IF NOT EXISTS " + TABLE_ORDOS + " ( ");    //TEMPORARY
            sb.append("CODE VARCHAR(10) NOT NULL, ");
            sb.append("LIBELLE VARCHAR(250) NOT NULL, ");
            sb.append("INDEX ordos (CODE)");
            sb.append(")");
            ps = con.prepareStatement(sb.toString());
            ps.execute();
            // Vidage de la table d'index budgets
            sb = new StringBuilder();
            sb.append("TRUNCATE TABLE " + TABLE_ORDOS);
            ps = con.prepareStatement(sb.toString());
            ps.execute();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseComptables(Connection con) throws SQLException {
        StringBuilder sb = new StringBuilder();
        PreparedStatement ps = null;
        try {
            // Creation de la table si inexistente
            sb.append("CREATE TEMPORARY TABLE IF NOT EXISTS " + TABLE_COMPTABLES + " ( ");   //TEMPORARY
            sb.append("CODE VARCHAR(6) NOT NULL, ");
            sb.append("LIBELLE VARCHAR(75) NOT NULL, ");
            sb.append("INDEX comptas (CODE)");
            sb.append(")");
            ps = con.prepareStatement(sb.toString());
            ps.execute();    
            // Vidage de la table d'index budgets
            sb = new StringBuilder();
            sb.append("TRUNCATE TABLE " + TABLE_COMPTABLES);
            ps = con.prepareStatement(sb.toString());
            ps.execute();        
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
}
