/*
 * 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.gl.dataaccess.impl;

import org.apache.commons.lang.StringUtils;
import org.kuali.kfs.gl.dataaccess.IcrEncumbranceDao;
import org.kuali.rice.core.api.util.type.KualiDecimal;
import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

public class IcrEncumbranceDaoJdbc extends PlatformAwareDaoBaseJdbc implements IcrEncumbranceDao {

    /**
     * @see org.kuali.kfs.gl.dataaccess.IcrEncumbranceDao#buildIcrEncumbranceFeed()
     */
    @Override
    public void buildIcrEncumbranceFeed(Integer fiscalYear, final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final Collection<String> icrCostTypes, final String[] expenseObjectTypes, final String costShareSubAccountType, final Writer fw) throws IOException {
        final String rateSql = "select distinct t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, "
            + getDbPlatform().getIsNullFunction("t3.fin_series_id", "t2.fin_series_id") + " fin_series_id, " + getDbPlatform().getIsNullFunction("t3.icr_typ_cd", "t2.acct_icr_typ_cd") + " acct_icr_typ_cd "
            + "from gl_encumbrance_t t1 join ca_account_t t2 on (t1.fin_coa_cd = t2.fin_coa_cd and t1.account_nbr = t2.account_nbr) "
            + "left join ca_a21_sub_acct_t t3 on (t1.fin_coa_cd = t3.fin_coa_cd and t1.account_nbr = t3.account_nbr and t1.sub_acct_nbr = t3.sub_acct_nbr) "
            + "where t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? "
            + "and t1.univ_fiscal_yr >= ? "
            + "and (t3.sub_acct_typ_cd is null or t3.sub_acct_typ_cd <> ?) "
            + "and acct_icr_typ_cd not in (" + inString(icrCostTypes.size()) + ")";

        List<Object> queryArguments = new ArrayList<Object>();
        for (String balanceType : icrEncumbBalanceTypes) {
            queryArguments.add(balanceType);
        }
        queryArguments.add(icrEncumbOriginCode);
        queryArguments.add(fiscalYear);
        queryArguments.add(costShareSubAccountType);
        for (String icrCostType : icrCostTypes) {
            queryArguments.add(icrCostType);
        }
        //prevent SQL errors in the event that the INDIRECT_COST_TYPES parameter contains no value
        if (icrCostTypes.size() < 1) {
            queryArguments.add("1");
        }


        getJdbcTemplate().query(rateSql, queryArguments.toArray(), new ResultSetExtractor() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    String newLine = System.getProperty("line.separator");
                    while (rs.next()) {
                        String fin_series_id = rs.getString("fin_series_id");
                        String acct_icr_typ_cd = rs.getString("acct_icr_typ_cd");
                        String fiscalYear = rs.getString("univ_fiscal_yr");
                        String chartCode = rs.getString("fin_coa_cd");
                        String accountNbr = rs.getString("account_nbr");
                        String subAccountNbr = rs.getString("sub_acct_nbr");

                        List<Object> encArgs = new ArrayList<Object>();
                        encArgs.add(fin_series_id);
                        encArgs.add(acct_icr_typ_cd);
                        encArgs.add(fiscalYear);
                        encArgs.add(chartCode);
                        encArgs.add(accountNbr);
                        encArgs.add(subAccountNbr);
                        for (String balanceType : icrEncumbBalanceTypes) {
                            encArgs.add(balanceType);
                        }
                        encArgs.add(icrEncumbOriginCode);
                        for (String expenseObjectType : expenseObjectTypes) {
                            encArgs.add(expenseObjectType);
                        }

                        executeEncumbranceSql(fiscalPeriod, icrEncumbOriginCode, icrEncumbBalanceTypes, expenseObjectTypes, encArgs.toArray(), fw);
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                } catch (DataAccessException ed) {
                    throw new RuntimeException(ed);
                }

                return null;
            }
        });
    }

    /**
     * Retrieves and formats ICR Encumbrance information and writes output records to the file writer
     *
     * @param fiscalPeriod          the current fiscal period
     * @param icrEncumbOriginCode   the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION
     * @param icrEncumbBalanceTypes a list of balance types - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_BALANCE_TYPES
     * @param expenseObjectTypes    a list of expense object types
     * @param encArgs               a list of query arguments
     * @param fw                    the file writer
     */
    protected void executeEncumbranceSql(final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final String[] expenseObjectTypes, Object[] encArgs, final Writer fw) {
        final String encumbSql = "select t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, "
            + "t1.fdoc_typ_cd, t1.fdoc_nbr, " + "sum(" + getDbPlatform().getIsNullFunction("t1.acln_encum_amt - t1.acln_encum_cls_amt", "0") + " * "
            + getDbPlatform().getIsNullFunction("t5.awrd_icr_rate_pct", "0") + " * .01) encumb_amt  " + "from gl_encumbrance_t t1 "
            + "join ca_icr_auto_entr_t t5 on t5.fin_series_id = ? and t5.univ_fiscal_yr = t1.univ_fiscal_yr "
            + "and t5.trn_debit_crdt_cd = 'D' "
            + "join ca_object_code_t t4 on t4.univ_fiscal_yr = t1.univ_fiscal_yr and t4.fin_coa_cd = t1.fin_coa_cd and t4.fin_object_cd = t1.fin_object_cd "
            + "where not exists (select 1 from ca_icr_excl_type_t where acct_icr_typ_cd = ? "
            + "and acct_icr_excl_typ_actv_ind = 'Y' and fin_object_cd = t1.fin_object_cd) "
            + "and t1.univ_fiscal_yr = ? and t1.fin_coa_cd = ? and t1.account_nbr = ? and t1.sub_acct_nbr = ? "
            + "and t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? "
            + "and t4.fin_obj_typ_cd in (" + inString(expenseObjectTypes.length)
            + ") group by t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, "
            + "t1.fdoc_typ_cd, t1.fdoc_nbr";

        getJdbcTemplate().query(encumbSql, encArgs, new ResultSetExtractor() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    String newLine = System.getProperty("line.separator");
                    while (rs.next()) {
                        String fiscalYear = rs.getString("univ_fiscal_yr");
                        String chartCode = rs.getString("fin_coa_cd");
                        String accountNbr = rs.getString("account_nbr");
                        String subAccountNbr = rs.getString("sub_acct_nbr");
                        String objectCode = rs.getString("fin_object_cd");
                        String balanceType = rs.getString("fin_balance_typ_cd");
                        String docType = rs.getString("fdoc_typ_cd");
                        String docNbr = rs.getString("fdoc_nbr");

                        KualiDecimal encumb_amt = new KualiDecimal(rs.getDouble("encumb_amt"));
                        KualiDecimal current_amt = KualiDecimal.ZERO;

                        Object[] icrArgs = new String[9];
                        icrArgs[0] = fiscalYear;
                        icrArgs[1] = chartCode;
                        icrArgs[2] = accountNbr;
                        icrArgs[3] = subAccountNbr;
                        icrArgs[4] = objectCode;
                        icrArgs[5] = balanceType;
                        icrArgs[6] = docType;
                        icrArgs[7] = docNbr;
                        icrArgs[8] = icrEncumbOriginCode;

                        Double icrAmount = getCurrentEncumbranceAmount(icrArgs);

                        if (icrAmount != null) {
                            current_amt = new KualiDecimal(icrAmount);
                        }

                        KualiDecimal new_encumb_amt = encumb_amt.subtract(current_amt);
                        if (new_encumb_amt.isZero()) {
                            // ignore zero dollar amounts
                            continue;
                        }

                        icrArgs = new String[3];
                        icrArgs[0] = fiscalYear;
                        icrArgs[1] = chartCode;
                        icrArgs[2] = objectCode;

                        String objectTypeCode = getICRObjectTypeCode(icrArgs);

                        String desc = "ICR Encumbrance " + docType + " " + docNbr;
                        String debitCreditInd = "D";
                        if (new_encumb_amt.isNegative()) {
                            debitCreditInd = "C";
                        }

                        fw.write("" + fiscalYear // Fiscal year 1-4
                            + chartCode // Chart code 5-6
                            + accountNbr // Account Number 7-13
                            + StringUtils.rightPad(subAccountNbr, 5)// Sub Account 14-18
                            + objectCode // Object Code 19-22
                            + "---" // Sub Object 23-25
                            + balanceType // balance type code
                            + objectTypeCode // Object Type 28-29
                            + fiscalPeriod // Fiscal Period 30-31
                            + StringUtils.rightPad(docType, 4) // Document Type 32-35
                            + icrEncumbOriginCode // Origin Code 36-37
                            + StringUtils.rightPad(docNbr, 14) // Doc Number 38-51
                            + StringUtils.rightPad("", 5, '0') // Entry Seq Nbr 52-56
                            + StringUtils.rightPad(StringUtils.substring(desc, 0, 40), 40) // Description 57-96
                            + StringUtils.leftPad(new_encumb_amt.abs().toString(), 21, '0') // Amount 97-116
                            + debitCreditInd // Debit/Credit 117-117
                            + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) // Trans Date 118-127
                            + "          " // Org Doc Nbr 128-137
                            + "          " // Project Code 138-147
                            + "        " // orig ref id 148-155
                            + "    " // ref doc type 156-159
                            + "  " // ref origin code 160-161
                            + "              " // ref doc number 162-175
                            + "          " // reversal date 176-185
                            + "D" // Enc update code 186-186
                        );

                        fw.write(newLine);
                        fw.flush();
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                } catch (DataAccessException ed) {
                    throw new RuntimeException(ed);
                } catch (IOException ex) {
                    throw new RuntimeException(ex);
                }

                return null;
            }
        });
    }

    /**
     * Returns the current encumbrance amount
     *
     * @param icrEncumbOriginCode the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION
     * @param icrArgs             a list of query arguments
     * @return the current encumbrance amount if found, null otherwise
     */
    protected Double getCurrentEncumbranceAmount(Object[] icrArgs) {
        final String icrSql = "select sum(" + getDbPlatform().getIsNullFunction("acln_encum_amt - acln_encum_cls_amt", "0") + ") current_amt "
            + "from gl_encumbrance_t where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? and fin_object_cd = ? "
            + "and fin_balance_typ_cd = ? and fdoc_typ_cd = ? and fdoc_nbr = ? and fs_origin_cd = ?";

        Double icrAmount = (Double) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    if (rs.next()) {
                        return rs.getDouble("current_amt");
                    }

                    return null;
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                } catch (DataAccessException ed) {
                    throw new RuntimeException(ed);
                }
            }
        });

        return icrAmount;
    }

    /**
     * Returns the object type code for the object code associated with the ICR Rate
     *
     * @param icrArgs a list of query arguments
     * @return the object type code if found, null otherwise
     */
    protected String getICRObjectTypeCode(Object[] icrArgs) {
        final String icrSql = "select fin_obj_typ_cd "
            + "from ca_object_code_t where univ_fiscal_yr = ? and fin_coa_cd = ? and fin_object_cd = ?";

        String objectTypeCode = (String) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    if (rs.next()) {
                        return rs.getString("fin_obj_typ_cd");
                    }

                    return null;
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                } catch (DataAccessException ed) {
                    throw new RuntimeException(ed);
                }
            }
        });

        return objectTypeCode;
    }

    /**
     * Creates a String of SQL parameters markers specified by the size of an array or collection
     *
     * @param int the number of parameter markers to include in an SQL in clause
     * @return the resulting String
     */
    protected String inString(int arraySize) {
        final String inClause;
        //prevent SQL errors in the event of an empty array
        if (arraySize < 1) {
            inClause = "?";
        } else {
            inClause = StringUtils.repeat("?", ",", arraySize);
        }
        return inClause;
    }
}
