/*
 * The Kuali Financial System, a comprehensive financial management system for higher education.
 *
 * Copyright 2005-2016 The Kuali Foundation
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.kuali.kfs.module.bc.document.dataaccess.impl;

import org.kuali.kfs.module.bc.BCConstants;
import org.kuali.kfs.module.bc.batch.dataaccess.impl.SQLForStep;
import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSynchronizationProblemsReportDao;
import org.kuali.kfs.sys.KFSConstants.BudgetConstructionPositionConstants;

import java.util.ArrayList;

/**
 * builds a report table of people whose salaries are budgeted in the wrong object class or have had a position change that merits
 * an object code validity check
 */

public class BudgetConstructionSynchronizationProblemsReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionSynchronizationProblemsReportDao {
    private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionSynchronizationProblemsReportDaoJdbc.class);

    protected static ArrayList<SQLForStep> updateReportsSynchronizationProblemsTable = new ArrayList<SQLForStep>(2);

    public BudgetConstructionSynchronizationProblemsReportDaoJdbc() {

        ArrayList<Integer> insertionPoints = new ArrayList<Integer>(2);

        // builds and updates SynchronizationProblemsReports
        // builds the salary and default object check MT table
        StringBuilder sqlText = new StringBuilder(1500);
        sqlText.append("INSERT INTO LD_BCN_POS_FND_T \n");
        sqlText.append("(PERSON_UNVL_ID, SEL_ORG_FIN_COA, SEL_ORG_CD, PERSON_NM, EMPLID, POSITION_NBR, \n");
        sqlText.append(" UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD, FIN_SUB_OBJ_CD) \n");
        sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.PERSON_NM,'Name not Found'), bcaf.emplid, bcaf.position_nbr, \n");
        sqlText.append(" bcaf.univ_fiscal_yr, bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
        sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl \n");
        sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
        sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
        sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
        sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
        sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
        sqlText.append(" AND bcaf.appt_fnd_dlt_cd = 'N' \n");
        sqlText.append(" AND (bcaf.pos_obj_chg_ind = 'Y' OR bcaf.pos_sal_chg_ind = 'Y') \n");
        sqlText.append(" UNION ALL\n");
        sqlText.append("SELECT ?, ctrl.sel_org_fin_coa, ctrl.sel_org_cd, COALESCE(iinc.PERSON_NM,'Name not Found'), bcaf.emplid, bcaf.position_nbr, \n");
        sqlText.append(" bcaf.univ_fiscal_yr, bcaf.fin_coa_cd, bcaf.account_nbr, bcaf.sub_acct_nbr, bcaf.fin_object_cd, bcaf.fin_sub_obj_cd \n");
        sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_INTINCBNT_T iinc ON (bcaf.emplid = iinc.emplid)), LD_BCN_POS_T bp, LD_BCN_CTRL_LIST_T ctrl \n");
        sqlText.append("WHERE ctrl.person_unvl_id = ? \n");
        sqlText.append(" AND bcaf.univ_fiscal_yr = ctrl.univ_fiscal_yr \n");
        sqlText.append(" AND bcaf.fin_coa_cd = ctrl.fin_coa_cd \n");
        sqlText.append(" AND bcaf.account_nbr = ctrl.account_nbr \n");
        sqlText.append(" AND bcaf.sub_acct_nbr = ctrl.sub_acct_nbr \n");
        sqlText.append(" AND bcaf.appt_fnd_dlt_cd = 'N' \n");
        sqlText.append(" AND bcaf.pos_obj_chg_ind <> 'Y' \n");
        sqlText.append(" AND bcaf.pos_sal_chg_ind <> 'Y' \n");
        sqlText.append(" AND bcaf.univ_fiscal_yr = bp.univ_fiscal_yr \n");
        sqlText.append(" AND bcaf.position_nbr = bp.position_nbr \n");
        sqlText.append(" AND (bp.pos_eff_status <> '");
        // active effective status
        insertionPoints.add(sqlText.length());
        sqlText.append("' OR bp.budgeted_posn <> 'Y') \n");

        updateReportsSynchronizationProblemsTable.add(new SQLForStep(sqlText, insertionPoints));
        sqlText.delete(0, sqlText.length());
        insertionPoints.clear();

        sqlText.append("UPDATE LD_BCN_POS_FND_T \n");
        sqlText.append("SET PERSON_NM = '");
        // the string indicating a vacant EMPLID
        insertionPoints.add(sqlText.length());
        sqlText.append("' \n");
        sqlText.append("WHERE (PERSON_UNVL_ID = ?) \n");
        sqlText.append("AND (EMPLID = '");
        // the string indicating a vacant EMPLID
        insertionPoints.add(sqlText.length());
        sqlText.append("')");

        updateReportsSynchronizationProblemsTable.add(new SQLForStep(sqlText, insertionPoints));
        sqlText.delete(0, sqlText.length());
        insertionPoints.clear();

    }

    /**
     * removes any rows from a previous report for this uear
     *
     * @param principalName--the user requesting the report
     */
    protected void cleanReportsSynchronizationProblemsTable(String principalName) {
        clearTempTableByUnvlId("LD_BCN_POS_FND_T", "PERSON_UNVL_ID", principalName);
    }

    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionSynchronizationProblemsReportDao#updateReportsSynchronizationProblemsTable(java.lang.String)
     */
    public void updateReportsSynchronizationProblemsTable(String principalName) {
        ArrayList<String> stringsToInsert = new ArrayList<String>(2);
        // get rid of any old reports sitting around for this user
        cleanReportsSynchronizationProblemsTable(principalName);
        // insert the code for an active position
        stringsToInsert.add(BudgetConstructionPositionConstants.POSITION_EFFECTIVE_STATUS_ACTIVE);
        // insert into the report table filled or vacant lines with an object code change, a position change, or an inactive
        // position
        getSimpleJdbcTemplate().update(updateReportsSynchronizationProblemsTable.get(0).getSQL(stringsToInsert), principalName, principalName, principalName, principalName);
        // change the name field for any line with a vacant position
        stringsToInsert.clear();
        stringsToInsert.add(BCConstants.VACANT_EMPLID);
        stringsToInsert.add(BCConstants.VACANT_EMPLID);
        getSimpleJdbcTemplate().update(updateReportsSynchronizationProblemsTable.get(1).getSQL(stringsToInsert), principalName);
    }

}
