/*
 * Copyright
 *   2007 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 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.plugin.generatorEdmn;

import java.io.File;
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.dgfip.xemelios.data.impl.pool.PoolManager;

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

    private static final Logger logger = Logger.getLogger(EdmnImporter.class);
    private static final String TABLE_INDEX = EdmnImporteurWorker.TABLE_INDEX;
    
    private static final String TABLE_BUDGETS = EdmnImporteurWorker.TABLE_BUDGETS;
    private static final String TABLE_ORDOS = EdmnImporteurWorker.TABLE_ORDOS;
    private static final String TABLE_COMPTABLES = EdmnImporteurWorker.TABLE_COMPTABLES;

    private static final String SCRIPT_BUDGET = "CREATE TEMPORARY TABLE IF NOT EXISTS "+TABLE_BUDGETS+" (\n"+    //
        "BUD_ANNEE VARCHAR(4) not null,\n"+
        "BUD_TYPE_ENREGISTREMENT VARCHAR(2) not null,\n"+
        "BUD_MINISTERE VARCHAR(2) not null,\n"+
        "BUD_PROGRAMME VARCHAR(4) not null,\n"+
        "BUD_ACTION VARCHAR(2) not null,\n"+
        "BUD_ARTICLE VARCHAR(2) not null,\n"+
        "BUD_LIBELLE VARCHAR(500),\n"+
        "INDEX IX_BUDGETS_1 (BUD_TYPE_ENREGISTREMENT),\n"+
        "INDEX IX_BUDGETS_2 (BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE),\n"+
        "INDEX IX_BUDGETS_3 (BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE,BUD_ANNEE),\n"+
        "INDEX IX_BUDGETS_5 (BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE,BUD_ANNEE,BUD_PROGRAMME),\n"+
        "INDEX IX_BUDGETS_6 (BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE,BUD_ANNEE,BUD_PROGRAMME,BUD_ACTION),\n"+
        "INDEX IX_BUDGETS_7 (BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE,BUD_ANNEE,BUD_PROGRAMME,BUD_ACTION,BUD_ARTICLE)\n"+
    ")";
    private static final String SCRIPT_ORDOS = "CREATE TEMPORARY TABLE IF NOT EXISTS "+TABLE_ORDOS+" (\n"+   //
        "ORD_ORNO3 VARCHAR(3) not null,\n"+
        "ORD_RCCST VARCHAR(5),\n"+
        "ORD_IMSB VARCHAR(2),\n"+
        "ORD_LIORDO VARCHAR(500),\n"+
        "ORD_TRIMESTRE VARCHAR(1) not null,\n"+
        "ORD_ANNEE VARCHAR(4) not null,\n"+
        "INDEX IX_ORDOS (ORD_ORNO3,ORD_RCCST,ORD_IMSB, ORD_TRIMESTRE, ORD_ANNEE)\n"+
    ")";
    private static final String SCRIPTS_COMPTABLES = "CREATE TEMPORARY TABLE IF NOT EXISTS "+TABLE_COMPTABLES+" (\n"+
        "CMP_CODE VARCHAR(6) not null,\n"+
        "CMP_LIBELLE VARCHAR(75),\n"+
        "INDEX IX_COMPTA (CMP_CODE)\n"+
    ")";
    private static final String SCRIPT_INDEX = "CREATE TEMPORARY TABLE IF NOT EXISTS "+TABLE_INDEX+" (\n"+
        "IND_ANNEE_GEST VARCHAR(2) not null,\n"+
        "IND_TRI_GEST VARCHAR(1) not null,\n"+
        "IND_COMPTABLE VARCHAR(6) not null,\n"+
        "IND_IMPUTATION VARCHAR(1) not null,\n"+
        "IND_MINISTERE VARCHAR(2) not null,\n"+
        "IND_COMPTESPE VARCHAR(5) not null,\n"+
        "IND_LOCALE VARCHAR(4) not null,\n"+
        "IND_ANNEE_TRSP VARCHAR(2) not null,\n"+
        "IND_TRI_TRSP VARCHAR(1) not null,\n"+
        "IND_SECTION_BUDG VARCHAR(2),\n"+
        "IND_CPTESPENAT VARCHAR(5) not null,\n"+
        "IND_NATIONALE VARCHAR(6),\n"+
        "INDEX IX_INDEX (IND_ANNEE_GEST,IND_TRI_GEST,IND_COMPTABLE,IND_IMPUTATION,IND_MINISTERE,IND_LOCALE)\n"+
    ")";
    // 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;
            }
        });*/
        super();
    }

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

    /**
     * @author jp.tessier
     * @param f : file EDMN
     * @param index : index of correspondance NumLiasseLocale <=> NumLiasseNationnale
     * @param budgets
     * @param ordos
     * @param comptas
     * @param output 
     * @throws Exception
     */
    public void importFile(File f, File index, File budgets, File ordos,File comptas,File output, String anneeGestion) 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,anneeGestion,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 {
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(SCRIPT_INDEX);
            ps.execute();
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_INDEX);
            ps.execute();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseBudgets(Connection con) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(SCRIPT_BUDGET);
            ps.execute();
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_BUDGETS);
            ps.execute();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseOrdos(Connection con) throws SQLException {
        PreparedStatement ps = null;
        try {
            // Creation de la table si inexistente
            ps = con.prepareStatement(SCRIPT_ORDOS);
            ps.execute();
            // Vidage de la table d'index budgets
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_ORDOS);
            ps.execute();
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
    
    private void createDataBaseComptables(Connection con) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(SCRIPTS_COMPTABLES);
            ps.execute();    
            ps = con.prepareStatement("TRUNCATE TABLE " + TABLE_COMPTABLES);
            ps.execute();        
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }
}
