/*
 * 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.BudgetConstructionDocumentAccountObjectDetailReportDao;

import java.util.ArrayList;


public class BudgetConstructionDocumentAccountObjectDetailReportDaoJdbc extends BudgetConstructionDaoJdbcBase implements BudgetConstructionDocumentAccountObjectDetailReportDao {

    protected SQLForStep initialInsert;
    protected SQLForStep setNonLeaveCSFFTE;
    protected SQLForStep setLeaveCSFFTE;

    protected ArrayList<Integer> insertionPoints = new ArrayList<Integer>(4);

    public BudgetConstructionDocumentAccountObjectDetailReportDaoJdbc() {
        StringBuilder sqlText = new StringBuilder(750);
        sqlText.append("INSERT INTO LD_BCN_BAL_BY_ACCT_T\n");
        sqlText.append("(PERSON_UNVL_ID, UNIV_FISCAL_YR, FIN_COA_CD, ACCOUNT_NBR, SUB_ACCT_NBR, FIN_OBJECT_CD,\n");
        sqlText.append(" FIN_SUB_OBJ_CD, FIN_OBJ_TYP_CD, FIN_OBJ_LEVEL_CD, TYP_FIN_REPORT_SORT_CD, FIN_CONS_SORT_CD,\n");
        sqlText.append(" LEV_FIN_REPORT_SORT_CD, APPT_RQST_FTE_QTY, APPT_RQCSF_FTE_QTY, POS_CSF_FTE_QTY,\n");
        sqlText.append(" ACLN_ANNL_BAL_AMT, FIN_BEG_BAL_LN_AMT, POS_CSF_LV_FTE_QTY)\n");
        sqlText.append("SELECT ?,\n");
        sqlText.append("    a.univ_fiscal_yr,\n");
        sqlText.append("    a.fin_coa_cd,\n");
        sqlText.append("    a.account_nbr,\n");
        sqlText.append("    a.sub_acct_nbr,\n");
        sqlText.append("    a.fin_object_cd,\n");
        sqlText.append("    a.fin_sub_obj_cd,\n");
        sqlText.append("    a.fin_obj_typ_cd,\n");
        sqlText.append("    l.fin_obj_level_cd,\n");
        // DB-specific substring format (first character of t.fin_report_sort_cd)
        insertionPoints.add(sqlText.length());
        sqlText.append(",\n");
        sqlText.append("    c.fin_report_sort_cd,\n");
        sqlText.append("    l.fin_report_sort_cd,\n");
        sqlText.append("    COALESCE(SUM(p.appt_rqst_fte_qty),0),\n");
        sqlText.append("    COALESCE(SUM(p.appt_rqcsf_fte_qty),0),\n");
        sqlText.append("    0, a.acln_annl_bal_amt, a.fin_beg_bal_ln_amt, 0\n");
        sqlText.append("FROM (LD_PND_BCNSTR_GL_T a LEFT OUTER JOIN LD_PNDBC_APPTFND_T p\n");
        sqlText.append("      ON ((a.univ_fiscal_yr = p.univ_fiscal_yr) AND\n");
        sqlText.append("          (a.fin_coa_cd = p.fin_coa_cd) AND\n");
        sqlText.append("          (a.account_nbr = p.account_nbr) AND\n");
        sqlText.append("          (a.sub_acct_nbr = p.sub_acct_nbr) AND\n");
        sqlText.append("          (a.fin_object_cd = p.fin_object_cd) AND\n");
        sqlText.append("          (a.fin_sub_obj_cd = p.fin_sub_obj_cd))),\n");
        sqlText.append("    CA_OBJECT_CODE_T o,\n");
        sqlText.append("    CA_OBJ_TYPE_T t,\n");
        sqlText.append("    CA_OBJ_LEVEL_T l,\n");
        sqlText.append("    CA_OBJ_CONSOLDTN_T c\n");
        sqlText.append("WHERE a.fdoc_nbr = ?\n");
        sqlText.append("  AND a.univ_fiscal_yr = ?\n");
        sqlText.append("  AND a.fin_coa_cd = ?\n");
        sqlText.append("  AND a.account_nbr = ?\n");
        sqlText.append("  AND a.sub_acct_nbr = ?\n");
        sqlText.append("  AND a.univ_fiscal_yr = o.univ_fiscal_yr\n");
        sqlText.append("  AND a.fin_coa_cd = o.fin_coa_cd\n");
        sqlText.append("  AND a.fin_object_cd = o.fin_object_cd\n");
        sqlText.append("  AND o.fin_obj_typ_cd = t.fin_obj_typ_cd\n");
        sqlText.append("  AND o.fin_coa_cd = l.fin_coa_cd\n");
        sqlText.append("  AND o.fin_obj_level_cd = l.fin_obj_level_cd\n");
        sqlText.append("  AND c.fin_coa_cd = l.fin_coa_cd\n");
        sqlText.append("  AND c.fin_cons_obj_cd = l.fin_cons_obj_cd\n");
        sqlText.append("GROUP BY a.univ_fiscal_yr,\n");
        sqlText.append("    a.fin_coa_cd,\n");
        sqlText.append("    a.account_nbr,\n");
        sqlText.append("    a.sub_acct_nbr,\n");
        sqlText.append("    a.fin_object_cd,\n");
        sqlText.append("    a.fin_sub_obj_cd,\n");
        sqlText.append("    a.fin_obj_typ_cd,\n");
        sqlText.append("    l.fin_obj_level_cd,\n");
        sqlText.append("    t.fin_report_sort_cd,\n");
        sqlText.append("    c.fin_report_sort_cd,\n");
        sqlText.append("    l.fin_report_sort_cd,\n");
        sqlText.append("    a.acln_annl_bal_amt,\n");
        sqlText.append("    a.fin_beg_bal_ln_amt");
        // initial insertion into the table
        initialInsert = new SQLForStep(sqlText, insertionPoints);
        sqlText.delete(0, sqlText.length());
        insertionPoints.clear();
        //
        // update non-leave CSF FTE
        sqlText.append("UPDATE LD_BCN_BAL_BY_ACCT_T\n");
        sqlText.append("SET pos_csf_fte_qty =\n");
        sqlText.append("    (SELECT sum(pos_csf_fte_qty)\n");
        sqlText.append("    FROM LD_BCN_CSF_TRCKR_T c\n");
        sqlText.append("    WHERE person_unvl_id = ?\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c.univ_fiscal_yr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c.fin_coa_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.account_nbr = c.account_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c.sub_acct_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c.fin_object_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c.fin_sub_obj_cd\n");
        sqlText.append("      and c.pos_csf_fndstat_cd <> '");
        // CSF code for a leave
        insertionPoints.add(sqlText.length());
        sqlText.append("')\n");
        sqlText.append("WHERE person_unvl_id = ?\n");
        sqlText.append("  AND EXISTS (SELECT 1\n");
        sqlText.append("    FROM LD_BCN_CSF_TRCKR_T c1\n");
        sqlText.append("    WHERE LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c1.univ_fiscal_yr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c1.fin_coa_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.account_nbr = c1.account_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c1.sub_acct_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c1.fin_object_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c1.fin_sub_obj_cd\n");
        sqlText.append("      and c1.pos_csf_fndstat_cd <> '");
        // CSF code for a leave
        insertionPoints.add(sqlText.length());
        sqlText.append("')");
        // update non-leave CSF FTE
        setNonLeaveCSFFTE = new SQLForStep(sqlText, insertionPoints);
        sqlText.delete(0, sqlText.length());
        insertionPoints.clear();
        //
        // update leave CSF FTE
        sqlText.append("UPDATE LD_BCN_BAL_BY_ACCT_T\n");
        sqlText.append("SET pos_csf_lv_fte_qty = \n");
        sqlText.append("    (SELECT sum(pos_csf_fte_qty)\n");
        sqlText.append("    FROM LD_BCN_CSF_TRCKR_T c\n");
        sqlText.append("    WHERE person_unvl_id = ?\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c.univ_fiscal_yr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c.fin_coa_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.account_nbr = c.account_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c.sub_acct_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c.fin_object_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c.fin_sub_obj_cd\n");
        sqlText.append("      and c.pos_csf_fndstat_cd = '");
        // CSF code for a leave
        insertionPoints.add(sqlText.length());
        sqlText.append("')\n");
        sqlText.append("WHERE person_unvl_id = ?\n");
        sqlText.append("  AND EXISTS (SELECT 1\n");
        sqlText.append("    FROM LD_BCN_CSF_TRCKR_T c1\n");
        sqlText.append("    WHERE LD_BCN_BAL_BY_ACCT_T.univ_fiscal_yr = c1.univ_fiscal_yr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_coa_cd = c1.fin_coa_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.account_nbr = c1.account_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.sub_acct_nbr = c1.sub_acct_nbr\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_object_cd = c1.fin_object_cd\n");
        sqlText.append("      and LD_BCN_BAL_BY_ACCT_T.fin_sub_obj_cd = c1.fin_sub_obj_cd\n");
        sqlText.append("      and c1.pos_csf_fndstat_cd = '");
        // CSF code for a leave
        insertionPoints.add(sqlText.length());
        sqlText.append("')");
        // update leave CSF FTE
        setLeaveCSFFTE = new SQLForStep(sqlText, insertionPoints);
        sqlText.delete(0, sqlText.length());
        insertionPoints.clear();
    }

    /**
     * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionDocumentAccountObjectDetailReportDao#updateDocumentAccountObjectDetailReportTable(java.lang.String,
     * java.lang.String, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String)
     */
    public void updateDocumentAccountObjectDetailReportTable(String principalName, String documentNumber, Integer universityFiscalYear, String chartOfAccountsCode, String accountNumber, String subAccountNumber) {
        // eliminate any rows already extant in the table for this user
        this.clearTempTableByUnvlId("LD_BCN_BAL_BY_ACCT_T", "PERSON_UNVL_ID", principalName);
        // insert the substring function into the SQL string
        StringBuilder sqlText = this.getSqlSubStringFunction("t.fin_report_sort_cd", 1, 1);
        ArrayList<String> stringsToInsert = new ArrayList<String>(1);
        stringsToInsert.add(sqlText.toString());
        getSimpleJdbcTemplate().update(initialInsert.getSQL(stringsToInsert), principalName, documentNumber, universityFiscalYear, chartOfAccountsCode, accountNumber, subAccountNumber);
        // set the non-leave CSF FTE
        stringsToInsert.clear();
        stringsToInsert.add(new String(BCConstants.csfFundingStatusFlag.LEAVE.getFlagValue()));
        stringsToInsert.add(stringsToInsert.get(0));
        getSimpleJdbcTemplate().update(setNonLeaveCSFFTE.getSQL(stringsToInsert), principalName, principalName);
        // set the CSF FTE for people on leave
        // (we are inserting the same set of leave flags as in the previous step)
        getSimpleJdbcTemplate().update(setLeaveCSFFTE.getSQL(stringsToInsert), principalName, principalName);
    }

}
