/*
 * 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.batch.dataaccess.impl.SQLForStep;
import org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionPositionFundingDetailReportDao;
import org.kuali.rice.core.api.util.type.KualiDecimal;

import java.util.ArrayList;
import java.util.UUID;


/**
 * populates the report table for positon funding detail for a given user
 */

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

    protected static ArrayList<SQLForStep> updateReportsPositionFundingDetailTable = new ArrayList<SQLForStep>(5);

    public BudgetConstructionPositionFundingDetailReportDaoJdbc() {

        /* populate the table based on threshold settings */
        /* sum the request and base by ssn */
        StringBuilder sqlText = new StringBuilder(1500);
        sqlText.append("INSERT INTO LD_BCN_BUILD_POSLIST01_MT \n");
        sqlText.append("(SESID, EMPLID, POS_CSF_AMT, POS_CSF_FTE_QTY, APPT_RQST_AMT, APPT_RQST_FTE_QTY) \n");
        sqlText.append("SELECT ?, bcaf.emplid, SUM(COALESCE(bcsf.pos_csf_amt,0)), SUM(COALESCE(bcsf.pos_csf_fte_qty,0)), SUM(bcaf.appt_rqst_amt), SUM(bcaf.appt_rqst_fte_qty) \n");
        sqlText.append("FROM (LD_PNDBC_APPTFND_T bcaf LEFT OUTER JOIN LD_BCN_CSF_TRCKR_T bcsf ON \n");
        sqlText.append(" ((bcaf.univ_fiscal_yr = bcsf.univ_fiscal_yr) AND \n");
        sqlText.append(" (bcaf.fin_coa_cd = bcsf.fin_coa_cd) AND \n");
        sqlText.append(" (bcaf.account_nbr = bcsf.account_nbr) AND \n");
        sqlText.append(" (bcaf.sub_acct_nbr = bcsf.sub_acct_nbr) AND \n");
        sqlText.append(" (bcaf.fin_object_cd = bcsf.fin_object_cd) AND \n");
        sqlText.append(" (bcaf.fin_sub_obj_cd = bcsf.fin_sub_obj_cd) AND \n");
        sqlText.append(" (bcaf.position_nbr = bcsf.position_nbr) AND \n");
        sqlText.append(" (bcaf.emplid = bcsf.emplid))), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick \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_rqst_amt <> 0 \n");
        sqlText.append(" AND bcaf.fin_object_cd = pick.fin_object_cd \n");
        sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
        sqlText.append(" AND pick.select_flag > 0 \n");
        sqlText.append("GROUP BY bcaf.emplid \n");

        updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
        sqlText.delete(0, sqlText.length());

        /* get the set where percent change (fraction * 100) in the salary exceeds the threshold */
        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, UNIV_FISCAL_YR, \n");
        sqlText.append(" 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,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr,\n");
        sqlText.append(" 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 \n");
        sqlText.append(" ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_POSLIST01_MT tssn \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.fin_object_cd = pick.fin_object_cd \n");
        sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
        sqlText.append(" AND pick.select_flag > 0 \n");
        sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
        sqlText.append(" AND tssn.appt_rqst_fte_qty = tssn.pos_csf_fte_qty \n");
        sqlText.append(" AND ROUND((((tssn.appt_rqst_amt - tssn.pos_csf_amt) / tssn.pos_csf_amt) * 100),1) >= ? \n");
        sqlText.append(" AND tssn.pos_csf_amt <> 0 \n");
        sqlText.append(" AND tssn.sesid = ? \n");

        updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
        sqlText.delete(0, sqlText.length());

        /* get the set where the percent change (fraction * 100) in the salary is less than or equal to the threshold */
        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, UNIV_FISCAL_YR, \n");
        sqlText.append(" 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,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr, \n");
        sqlText.append(" 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)), \n");
        sqlText.append(" LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick, LD_BCN_BUILD_POSLIST01_MT tssn \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.fin_object_cd = pick.fin_object_cd \n");
        sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
        sqlText.append(" AND pick.select_flag > 0 \n");
        sqlText.append(" AND bcaf.emplid = tssn.emplid \n");
        sqlText.append(" AND tssn.appt_rqst_fte_qty = tssn.pos_csf_fte_qty \n");
        sqlText.append(" AND ROUND((((tssn.appt_rqst_amt - tssn.pos_csf_amt) / tssn.pos_csf_amt) * 100),1) <= ? \n");
        sqlText.append(" AND tssn.pos_csf_amt <> 0 \n");
        sqlText.append(" AND tssn.sesid = ? \n");

        updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
        sqlText.delete(0, sqlText.length());

        /* populate the table using the full set--no check on the percent change in the salary */
        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, UNIV_FISCAL_YR, \n");
        sqlText.append(" 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,'VACANT'), bcaf.emplid, bcaf.position_nbr, bcaf.univ_fiscal_yr, \n");
        sqlText.append(" 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 \n");
        sqlText.append(" ON (bcaf.emplid = iinc.emplid)), LD_BCN_CTRL_LIST_T ctrl, LD_BCN_OBJ_PICK_T pick \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.fin_object_cd = pick.fin_object_cd \n");
        sqlText.append(" AND pick.person_unvl_id = ctrl.person_unvl_id \n");
        sqlText.append(" AND pick.select_flag > 0 \n");

        updateReportsPositionFundingDetailTable.add(new SQLForStep(sqlText));
        sqlText.delete(0, sqlText.length());
    }

    @Override
    public void cleanReportsPositionFundingDetailTable(String principalName) {
        clearTempTableByUnvlId("LD_BCN_POS_FND_T", "PERSON_UNVL_ID", principalName);
    }

    /**
     * build a list of people with salaries at or above the threshold
     *
     * @param principalName--the    user requesting the list
     * @param thresholdPercent--the percent marking the threshold
     */
    protected void updateReportsPositionFundingDetailTableAboveThreshold(String principalName, KualiDecimal thresholdPercent) {
        String idForSession = UUID.randomUUID().toString();
        // get rid of any previous reporting data from this user
        cleanReportsPositionFundingDetailTable(principalName);
        // sum the FTE and amounts into a temporary table
        getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(0).getSQL(), idForSession, principalName);
        // fill the reporting table with only those people who are at or above the threshold
        // (jdbcTemplate will apparenlty not accept a parameter of type KualiDecimal, and a cast when we pass the parameter doesn't
        // help: 04/09/2008)
        // (apparently, creating a new value from a cast doesn't help either)
        Number thresholdValue = thresholdPercent.floatValue();
        getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(1).getSQL(), principalName, principalName, thresholdValue, idForSession);
        // remove the data for this user's session from the temporary table for total amounts and FTE
        this.clearTempTableBySesId("LD_BCN_BUILD_POSLIST01_MT", "SESID", idForSession);
    }

    /**
     * build a list of people with salaries at or below the threshhold
     *
     * @param principalName--the    user requesting the list
     * @param thresholdPercent--the percent marking the threshold
     */
    protected void updateReportsPositionFundingDetailTableBelowThreshold(String principalName, KualiDecimal thresholdPercent) {
        String idForSession = UUID.randomUUID().toString();
        // get rid of any previous reporting data from this user
        cleanReportsPositionFundingDetailTable(principalName);
        // sum the FTE and amounts into a temporary table
        getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(0).getSQL(), idForSession, principalName);
        // fill the reporting table with only those people who are at or below the threshold
        // (jdbcTemplate will apparenlty not accept a parameter of type KualiDecimal, and a cast when we pass the parameter doesn't
        // help: 04/09/2008)
        // (apparently, creating a new value from a cast doesn't help either)
        Number thresholdValue = thresholdPercent.floatValue();
        getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(2).getSQL(), principalName, principalName, thresholdValue, idForSession);
        // remove the data for this user's session from the temporary table for total amounts and FTE
        this.clearTempTableBySesId("LD_BCN_BUILD_POSLIST01_MT", "SESID", idForSession);
    }

    /**
     * build a list of all salaries which this user can see
     *
     * @param principalName--the user requesting the list
     */
    protected void updateReportsPositionFundingDetailTableWithAllData(String principalName) {
        // get rid of any previous reporting data from this user
        cleanReportsPositionFundingDetailTable(principalName);
        // dump all the data this user is authorized to report on into the reporting table
        getSimpleJdbcTemplate().update(updateReportsPositionFundingDetailTable.get(3).getSQL(), principalName, principalName);
    }

    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionPositionFundingDetailReportDao#updateReportsPositionFundingDetailTable(java.lang.String,
     * boolean, boolean, java.lang.Number)
     */
    @Override
    public void updateReportsPositionFundingDetailTable(String principalName, boolean applyAThreshold, boolean selectOnlyGreaterThanOrEqualToThreshold, KualiDecimal thresholdPercent) {
        // if there is no threshold, just dump everything in and return
        if (!applyAThreshold) {
            updateReportsPositionFundingDetailTableWithAllData(principalName);
            return;
        }
        // the user wants a threshold--list above or below?
        if (selectOnlyGreaterThanOrEqualToThreshold) {
            updateReportsPositionFundingDetailTableAboveThreshold(principalName, thresholdPercent);
        } else {
            updateReportsPositionFundingDetailTableBelowThreshold(principalName, thresholdPercent);
        }
    }

}
