/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements. See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership. The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.fineract.organisation.teller.service;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.Collection;
import java.util.Iterator;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
import org.apache.fineract.infrastructure.core.exception.UnrecognizedQueryParamException;
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.PaginationHelper;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
import org.apache.fineract.organisation.monetary.data.CurrencyData;
import org.apache.fineract.organisation.monetary.service.CurrencyReadPlatformService;
import org.apache.fineract.organisation.office.data.OfficeData;
import org.apache.fineract.organisation.office.service.OfficeReadPlatformService;
import org.apache.fineract.organisation.staff.data.StaffData;
import org.apache.fineract.organisation.staff.exception.StaffNotFoundException;
import org.apache.fineract.organisation.staff.service.StaffReadPlatformService;
import org.apache.fineract.organisation.teller.data.CashierData;
import org.apache.fineract.organisation.teller.data.CashierTransactionData;
import org.apache.fineract.organisation.teller.data.CashierTransactionTypeTotalsData;
import org.apache.fineract.organisation.teller.data.CashierTransactionsWithSummaryData;
import org.apache.fineract.organisation.teller.data.TellerData;
import org.apache.fineract.organisation.teller.data.TellerJournalData;
import org.apache.fineract.organisation.teller.data.TellerTransactionData;
import org.apache.fineract.organisation.teller.domain.CashierTxnType;
import org.apache.fineract.organisation.teller.domain.TellerStatus;
import org.apache.fineract.useradministration.domain.AppUser;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.CollectionUtils;

@RequiredArgsConstructor
public class TellerManagementReadPlatformServiceImpl implements TellerManagementReadPlatformService {

    private final JdbcTemplate jdbcTemplate;
    private final PlatformSecurityContext context;
    private final TellerLookupMapper lookupMapper = new TellerLookupMapper();
    private final OfficeReadPlatformService officeReadPlatformService;
    private final StaffReadPlatformService staffReadPlatformService;
    private final CurrencyReadPlatformService currencyReadPlatformService;
    private final DatabaseSpecificSQLGenerator sqlGenerator;
    private final PaginationHelper paginationHelper;
    private final ColumnValidator columnValidator;

    private static final class TellerMapper implements RowMapper<TellerData> {

        public String schema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append("t.id as id,t.office_id as office_id, t.name as teller_name, t.description as description, ");
            sqlBuilder.append("t.valid_from as start_date, t.valid_to as end_date, t.state as status, o.name as office_name, ");
            sqlBuilder.append("t.debit_account_id as debit_account_id, t.credit_account_id as credit_account_id ");
            sqlBuilder.append("from m_tellers t ");
            sqlBuilder.append("join m_office o on o.id = t.office_id ");

            return sqlBuilder.toString();
        }

        @Override
        public TellerData mapRow(final ResultSet rs, final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final Long officeId = rs.getLong("office_id");
            final String tellerName = rs.getString("teller_name");
            final String description = rs.getString("description");
            final String officeName = rs.getString("office_name");
            TellerStatus tellerStatus = null;
            final Integer status = rs.getInt("status");
            if (status != null) {
                tellerStatus = TellerStatus.fromInt(status);
            }
            final Long debitAccountId = rs.getLong("debit_account_id");
            final Long creditAccountId = rs.getLong("credit_account_id");

            final LocalDate startDate = JdbcSupport.getLocalDate(rs, "start_date");
            final LocalDate endDate = JdbcSupport.getLocalDate(rs, "end_date");

            return TellerData.instance(id, officeId, debitAccountId, creditAccountId, tellerName, description, startDate, endDate,
                    tellerStatus, officeName, null, null);
        }
    }

    private static final class TellerLookupMapper implements RowMapper<TellerData> {

        private final String schemaSql;

        TellerLookupMapper() {

            final StringBuilder sqlBuilder = new StringBuilder(100);
            sqlBuilder.append("t.id as id, t.name as teller_name ");
            sqlBuilder.append("from m_tellers t ");

            this.schemaSql = sqlBuilder.toString();
        }

        public String schema() {
            return this.schemaSql;
        }

        @Override
        public TellerData mapRow(final ResultSet rs, final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final String tellerName = rs.getString("teller_name");
            return TellerData.lookup(id, tellerName);
        }
    }

    @Override
    public Collection<TellerData> retrieveAllTellersForDropdown(final Long officeId) {

        final Long defaultOfficeId = defaultToUsersOfficeIfNull(officeId);

        final String sql = "select " + this.lookupMapper.schema() + " where s.office_id = ? and s.is_active=true ";

        return this.jdbcTemplate.query(sql, this.lookupMapper, new Object[] { defaultOfficeId }); // NOSONAR
    }

    private Long defaultToUsersOfficeIfNull(final Long officeId) {
        Long defaultOfficeId = officeId;
        if (defaultOfficeId == null) {
            defaultOfficeId = this.context.authenticatedUser().getOffice().getId();
        }
        return defaultOfficeId;
    }

    @Override
    public TellerData findTeller(final Long tellerId) {

        try {
            final TellerMapper tm = new TellerMapper();
            final String sql = "select " + tm.schema() + " where t.id = ?";

            return this.jdbcTemplate.queryForObject(sql, tm, new Object[] { tellerId }); // NOSONAR
        } catch (final EmptyResultDataAccessException e) {
            throw new StaffNotFoundException(tellerId, e);
        }
    }

    @Override
    public Collection<TellerData> retrieveAllTellers(final String sqlSearch, final Long officeId, final String status) {
        final String extraCriteria = getTellerCriteria(sqlSearch, officeId, status);
        return retrieveAllTeller(extraCriteria, officeId);
    }

    private Collection<TellerData> retrieveAllTeller(final String extraCriteria, final Long officeId) {

        final TellerMapper tm = new TellerMapper();
        String sql = "select " + tm.schema();
        if (StringUtils.isNotBlank(extraCriteria)) {
            sql += " where " + extraCriteria;
        }
        sql = sql + " order by t.teller_name";
        if (officeId != null) {
            return this.jdbcTemplate.query(sql, tm, new Object[] { officeId }); // NOSONAR
        }
        return this.jdbcTemplate.query(sql, tm); // NOSONAR
    }

    private String getTellerCriteria(final String sqlSearch, final Long officeId, final String status) {

        final StringBuilder extraCriteria = new StringBuilder(200);

        if (sqlSearch != null) {
            extraCriteria.append(" and (").append(sqlSearch).append(")");
            final TellerMapper tm = new TellerMapper();
            this.columnValidator.validateSqlInjection(tm.schema(), sqlSearch);
        }
        if (officeId != null) {
            extraCriteria.append(" and office_id = ? ");
        }
        // Passing status parameter to get ACTIVE (By Default), INACTIVE or ALL
        // (Both active and Inactive) employees
        if (status.equalsIgnoreCase("active")) {
            extraCriteria.append(" and status = 300 ");
        } else if (status.equalsIgnoreCase("inActive")) {
            extraCriteria.append(" and status = 0 ");
        } else {
            if (!status.equalsIgnoreCase("all")) {
                throw new UnrecognizedQueryParamException("status", status, new Object[] { "all", "active", "inactive" });
            }
        }

        if (StringUtils.isNotBlank(extraCriteria.toString())) {
            extraCriteria.delete(0, 4);
        }

        // remove begin four letter including a space from the string.
        return extraCriteria.toString();
    }

    @Override
    public Collection<TellerData> getTellers(Long officeId) {
        return retrieveAllTellers(false);
    }

    @Override
    public Collection<CashierData> getCashiersForTeller(Long tellerId, LocalDate fromDate, LocalDate toDate) {
        return retrieveCashiersForTellers(null, tellerId);
    }

    @Override
    public Collection<CashierData> retrieveCashiersForTellers(final String sqlSearch, final Long tellerId) {
        final String extraCriteria = getTellerCriteria(sqlSearch, tellerId);
        return fetchCashiers(extraCriteria);
    }

    private String getTellerCriteria(final String sqlSearch, final Long tellerId) {

        final StringBuilder extraCriteria = new StringBuilder(200);

        if (sqlSearch != null) {
            extraCriteria.append(" and (").append(sqlSearch).append(")");
            final CashierMapper cm = new CashierMapper();
            this.columnValidator.validateSqlInjection(cm.schema(), sqlSearch);

        }
        if (tellerId != null) {
            extraCriteria.append(" and teller_id = ").append(tellerId).append(" ");
        }

        // remove begin four letter including a space from the string.
        if (StringUtils.isNotBlank(extraCriteria.toString())) {
            extraCriteria.delete(0, 4);
        }

        return extraCriteria.toString();
    }

    private Collection<CashierData> fetchCashiers(final String extraCriteria) {

        final CashierMapper cm = new CashierMapper();
        String sql = "select " + cm.schema();
        if (StringUtils.isNotBlank(extraCriteria)) {
            sql += " where " + extraCriteria;
        }
        sql = sql + " order by teller_name";
        return this.jdbcTemplate.query(sql, cm); // NOSONAR
    }

    @Override
    public CashierData findCashier(Long cashierId) {
        try {
            final CashierMapper cm = new CashierMapper();
            final String sql = "select " + cm.schema() + " where c.id = ?";

            return this.jdbcTemplate.queryForObject(sql, cm, new Object[] { cashierId }); // NOSONAR
        } catch (final EmptyResultDataAccessException e) {
            throw new StaffNotFoundException(cashierId, e);
        }
    }

    @Override
    public Collection<CashierData> getCashierData(Long officeId, Long tellerId, Long staffId, LocalDate date) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Collection<CashierData> getTellerCashiers(Long tellerId, LocalDate date) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public TellerTransactionData findTellerTransaction(Long transactionId) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Collection<TellerTransactionData> fetchTellerTransactionsByTellerId(Long tellerId, LocalDate fromDate, LocalDate toDate) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Collection<TellerJournalData> getJournals(Long officeId, Long tellerId, Long cashierId, LocalDate dateFrom, LocalDate dateTo) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Collection<TellerJournalData> fetchTellerJournals(Long tellerId, Long cashierId, LocalDate fromDate, LocalDate toDate) {
        // TODO Auto-generated method stub
        return null;
    }

    @Cacheable(value = "tellers", key = "T(org.apache.fineract.infrastructure.core.service.ThreadLocalContextUtil).getTenant().getTenantIdentifier().concat(#root.target.context.authenticatedUser().getOffice().getHierarchy()+'of')")
    public Collection<TellerData> retrieveAllTellers(final boolean includeAllTellers) {
        final AppUser currentUser = this.context.authenticatedUser();
        final String hierarchy = currentUser.getOffice().getHierarchy();
        String hierarchySearchString = null;
        if (includeAllTellers) {
            hierarchySearchString = "." + "%";
        } else {
            hierarchySearchString = hierarchy + "%";
        }
        final TellerMapper tm = new TellerMapper();
        final String sql = "select " + tm.schema() + "where o.hierarchy like ? order by o.hierarchy";

        return this.jdbcTemplate.query(sql, tm, new Object[] { hierarchySearchString }); // NOSONAR
    }

    @Override
    public CashierData retrieveCashierTemplate(Long officeId, Long tellerId, boolean staffInSelectedOfficeOnly) {
        final Long defaultOfficeId = defaultToUsersOfficeIfNull(officeId);

        final OfficeData officeData = this.officeReadPlatformService.retrieveOffice(defaultOfficeId);
        String officeName = "";
        if (officeData != null) {
            officeName = officeData.getName();
        }

        TellerData tellerData = findTeller(tellerId);
        String tellerName = "";
        if (tellerData != null) {
            tellerName = tellerData.getName();
        }

        Collection<StaffData> staffOptions = null;

        final boolean loanOfficersOnly = false;
        if (staffInSelectedOfficeOnly) {
            staffOptions = this.staffReadPlatformService.retrieveAllStaffForDropdown(defaultOfficeId);
        } else {
            staffOptions = this.staffReadPlatformService.retrieveAllStaffInOfficeAndItsParentOfficeHierarchy(defaultOfficeId,
                    loanOfficersOnly);
        }
        if (CollectionUtils.isEmpty(staffOptions)) {
            staffOptions = null;
        }

        return CashierData.template(officeId, officeName, tellerId, tellerName, staffOptions);
    }

    @Override
    public CashierTransactionData retrieveCashierTxnTemplate(Long cashierId) {
        String officeName = "";
        String tellerName = "";
        String cashierName = "";
        Long officeId = null;
        Long tellerId = null;
        LocalDate startDate = null;
        LocalDate endDate = null;

        CashierData cashierData = findCashier(cashierId);
        if (cashierData != null) {
            cashierName = cashierData.getStaffName();
            tellerId = cashierData.getTellerId();
            if (tellerId != null) {
                TellerData tellerData = findTeller(tellerId);
                if (tellerData != null) {
                    tellerName = tellerData.getName();
                    officeName = tellerData.getOfficeName();
                }
            }
            startDate = cashierData.getStartDate();
            endDate = cashierData.getEndDate();
        }
        // Fetching all currency type from m_organisation_currency table
        final Collection<CurrencyData> currencyOptions = this.currencyReadPlatformService.retrieveAllowedCurrencies();

        return CashierTransactionData.template(cashierId, tellerId, tellerName, officeId, officeName, cashierName, cashierData, startDate,
                endDate, currencyOptions);
    }

    @Override
    public CashierTransactionsWithSummaryData retrieveCashierTransactionsWithSummary(final Long cashierId, final boolean includeAllTellers,
            final LocalDate fromDate, final LocalDate toDate, final String currencyCode, final SearchParameters searchParameters) {
        CashierData cashierData = findCashier(cashierId);
        Long staffId = cashierData.getStaffId();
        StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
        OfficeData officeData = officeReadPlatformService.retrieveOffice(staffData.getOfficeId());
        final String hierarchy = officeData.getHierarchy();
        String hierarchySearchString;
        if (includeAllTellers) {
            hierarchySearchString = "." + "%";
        } else {
            hierarchySearchString = hierarchy;
        }
        final CashierTransactionSummaryMapper ctsm = new CashierTransactionSummaryMapper();
        final String sql = "select " + ctsm.cashierTxnSummarySchema() + " limit 1000";

        Collection<CashierTransactionTypeTotalsData> cashierTxnTypeTotals = this.jdbcTemplate.query(sql, ctsm, // NOSONAR
                new Object[] { cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString, cashierId,
                        currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString });

        Iterator<CashierTransactionTypeTotalsData> itr = cashierTxnTypeTotals.iterator();
        BigDecimal allocAmount = new BigDecimal(0);
        BigDecimal cashInAmount = new BigDecimal(0);
        BigDecimal cashOutAmount = new BigDecimal(0);
        BigDecimal settleAmount = new BigDecimal(0);

        while (itr.hasNext()) {
            CashierTransactionTypeTotalsData total = itr.next();
            if (total != null) {
                if (total.getCashierTxnType().equals(CashierTxnType.ALLOCATE.getId())) {
                    allocAmount = total.getCashTotal();
                } else if (total.getCashierTxnType().equals(CashierTxnType.SETTLE.getId())) {
                    settleAmount = total.getCashTotal();
                } else if (total.getCashierTxnType().equals(CashierTxnType.INWARD_CASH_TXN.getId())) {
                    cashInAmount = total.getCashTotal();
                } else if (total.getCashierTxnType().equals(CashierTxnType.OUTWARD_CASH_TXN.getId())) {
                    cashOutAmount = total.getCashTotal();
                }
            }
        }

        final Page<CashierTransactionData> cashierTransactions = retrieveCashierTransactions(cashierId, includeAllTellers, fromDate, toDate,
                currencyCode, searchParameters);

        CashierTransactionData cashierTxnTemplate = retrieveCashierTxnTemplate(cashierId);

        CashierTransactionsWithSummaryData txnsWithSummary = CashierTransactionsWithSummaryData.instance(cashierTransactions, allocAmount,
                cashInAmount, cashOutAmount, settleAmount, cashierTxnTemplate.getOfficeName(), cashierTxnTemplate.getTellerId(),
                cashierTxnTemplate.getTellerName(), cashierTxnTemplate.getCashierId(), cashierTxnTemplate.getCashierName());
        return txnsWithSummary;
    }

    @Override
    public Page<CashierTransactionData> retrieveCashierTransactions(final Long cashierId, final boolean includeAllTellers,
            final LocalDate fromDate, final LocalDate toDate, final String currencyCode, final SearchParameters searchParameters) {
        CashierData cashierData = findCashier(cashierId);
        Long staffId = cashierData.getStaffId();
        StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
        OfficeData officeData = officeReadPlatformService.retrieveOffice(staffData.getOfficeId());
        final String hierarchy = officeData.getHierarchy();
        String hierarchySearchString = null;
        if (includeAllTellers) {
            hierarchySearchString = "." + "%";
        } else {
            hierarchySearchString = hierarchy;
        }

        final CashierTransactionMapper ctm = new CashierTransactionMapper();

        String sql = "select * from (select " + ctm.cashierTxnSchema()
                + " where txn.cashier_id = ? and txn.currency_code = ? and o.hierarchy like ? "
                + "AND ((case when c.full_day then Date(txn.created_date) between c.start_date AND c.end_date else ( Date(txn.created_date) between c.start_date AND c.end_date"
                + " ) and ( TIME(txn.created_date) between TIME(c.start_time) AND TIME(c.end_time)) end) or txn.txn_type = 101))  cashier_txns "
                + " union (select " + ctm.savingsTxnSchema()
                + " where sav_txn.is_reversed = false and c.id = ? and sav.currency_code = ? and o.hierarchy like ? and "
                + " sav_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) "
                + " and renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'withdrawal', 'Annual Fee', 'Waive Charge', 'Interest Posting', 'Overdraft Interest') "
                + " and (sav_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) " + " AND acnttrans.id IS NULL ) "
                + " union (select " + ctm.loansTxnSchema()
                + " where loan_txn.is_reversed = false and c.id = ? and loan.currency_code = ? and o.hierarchy like ? and "
                + " loan_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) "
                + " and renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT','DISBURSEMENT', 'CHARGE_PAYMENT', 'WAIVE_CHARGES', 'WAIVE_INTEREST', 'WRITEOFF') "
                + " and (loan_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) " + " AND acnttrans.id IS NULL ) "
                + " union (select " + ctm.clientTxnSchema()
                + " where cli_txn.is_reversed = false and c.id = ? and cli_txn.currency_code = ? and o.hierarchy like ? and cli_txn.transaction_date "
                + " between c.start_date and date_add(c.end_date, interval 1 day) "
                + " and renum.enum_value in ('PAY_CHARGE', 'WAIVE_CHARGE') "
                + " and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) ) " + " order by created_date ";

        if (searchParameters.hasLimit()) {
            sql += " ";
            if (searchParameters.hasOffset()) {
                sql += sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset());
            } else {
                sql += sqlGenerator.limit(searchParameters.getLimit());
            }
        }
        // return this.jdbcTemplate.query(sql, ctm, new Object[] { cashierId,
        // currencyCode, hierarchySearchString, cashierId, currencyCode,
        // hierarchySearchString, cashierId, currencyCode,
        // hierarchySearchString, cashierId, currencyCode, hierarchySearchString
        // });
        Object[] params = new Object[] { cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString,
                cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString };
        return this.paginationHelper.fetchPage(this.jdbcTemplate, sql, params, ctm);
    }

    private static final class CashierMapper implements RowMapper<CashierData> {

        public String schema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append("c.id as id,c.teller_id as teller_id, t.name as teller_name, c.description as description, ");
            sqlBuilder.append("c.staff_id as staff_id, s.display_name as staff_name,  ");
            sqlBuilder.append("c.start_date as start_date, c.end_date as end_date,  ");
            sqlBuilder.append("c.full_day as full_day, c.start_time as start_time, c.end_time as end_time ");
            sqlBuilder.append("from m_cashiers c ");
            sqlBuilder.append("join m_tellers t on t.id = c.teller_id ");
            sqlBuilder.append("join m_staff s on s.id = c.staff_id ");

            return sqlBuilder.toString();
        }

        @Override
        public CashierData mapRow(final ResultSet rs, final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final Long tellerId = rs.getLong("teller_id");
            final String tellerName = rs.getString("teller_name");
            final Long staffId = rs.getLong("staff_id");
            final String staffName = rs.getString("staff_name");
            final String description = rs.getString("description");

            final LocalDate startDate = JdbcSupport.getLocalDate(rs, "start_date");
            final LocalDate endDate = JdbcSupport.getLocalDate(rs, "end_date");
            final Boolean fullDay = rs.getBoolean("full_day");
            final String startTime = rs.getString("start_time");
            final String endTime = rs.getString("end_time");

            return CashierData.instance(id, null, null, staffId, staffName, tellerId, tellerName, description, startDate, endDate, fullDay,
                    startTime, endTime);
        }
    }

    private static final class CashierTransactionMapper implements RowMapper<CashierTransactionData> {

        public String cashierTxnSchema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append(" txn.id as txn_id, txn.cashier_id as cashier_id, ");
            sqlBuilder.append(" txn.txn_type as txn_type, ");
            sqlBuilder.append(" txn.txn_amount as txn_amount, txn.txn_date as txn_date, txn.txn_note as txn_note, ");
            sqlBuilder.append(" txn.entity_type as entity_type, txn.entity_id as entity_id, txn.created_date as created_date, ");
            sqlBuilder.append(
                    " o.id as office_id, o.name as office_name, t.id as teller_id, t.name as teller_name, s.display_name as cashier_name ");
            sqlBuilder.append(" from m_cashier_transactions txn ");
            sqlBuilder.append(" left join m_cashiers c on c.id = txn.cashier_id ");
            sqlBuilder.append(" left join m_tellers t on t.id = c.teller_id ");
            sqlBuilder.append(" left join m_office o on o.id = t.office_id ");
            sqlBuilder.append(" left join m_staff s on s.id = c.staff_id ");

            return sqlBuilder.toString();
        }

        public String savingsTxnSchema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append(" sav_txn.id as txn_id, null as cashier_id, ");
            sqlBuilder.append(" case ");
            sqlBuilder.append("     when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'Annual Fee') ");
            sqlBuilder.append("         then 103 ");
            sqlBuilder
                    .append("     when renum.enum_value in ('withdrawal', 'Waive Charge', 'Interest Posting', 'Overdraft Interest', '') ");
            sqlBuilder.append("         then 104 ");
            sqlBuilder.append("     else ");
            sqlBuilder.append("         105 ");
            sqlBuilder.append(" end as txn_type, ");
            sqlBuilder.append(" sav_txn.amount as txn_amount, sav_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    " concat (renum.enum_value, ', Sav:', sav.id, '-', sav.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append(" 'savings' as entity_type, sav.id as entity_id, sav_txn.created_date as created_date, ");
            sqlBuilder.append(
                    " o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append(" from m_savings_account_transaction sav_txn ");
            sqlBuilder.append(
                    " left join r_enum_value renum on sav_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'savings_transaction_type_enum' ");
            sqlBuilder.append(" left join m_savings_account sav on sav_txn.savings_account_id = sav.id ");
            sqlBuilder.append(" left join m_client cl on sav.client_id = cl.id ");
            sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
            sqlBuilder.append(" left join m_appuser user on sav_txn.appuser_id = user.id ");
            sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
            sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = sav_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
            sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
            sqlBuilder.append(" on (acnttrans.from_savings_transaction_id = sav_txn.id ");
            sqlBuilder.append(" or acnttrans.to_savings_transaction_id = sav_txn.id) ");

            return sqlBuilder.toString();
        }

        public String loansTxnSchema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append(" loan_txn.id as txn_id, c.id as cashier_id, ");
            sqlBuilder.append(" case ");
            sqlBuilder.append(
                    "     when renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT', 'CHARGE_PAYMENT') ");
            sqlBuilder.append("         then 103 ");
            sqlBuilder.append("     when renum.enum_value in ('DISBURSEMENT', 'WAIVE_INTEREST', 'WRITEOFF', 'WAIVE_CHARGES') ");
            sqlBuilder.append("         then 104 ");
            sqlBuilder.append("     else ");
            sqlBuilder.append("         105 ");
            sqlBuilder.append(" end as cash_txn_type, ");
            sqlBuilder.append(" loan_txn.amount as txn_amount, loan_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    " concat (renum.enum_value, ', Loan:', loan.id, '-', loan.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append(" 'loans' as entity_type, loan.id as entity_id, loan_txn.created_date as created_date, ");
            sqlBuilder.append(
                    " o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append(" from m_loan_transaction loan_txn ");
            sqlBuilder.append(
                    " left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'loan_transaction_type_enum' ");
            sqlBuilder.append(" left join m_loan loan on loan_txn.loan_id = loan.id ");
            sqlBuilder.append(" left join m_client cl on loan.client_id = cl.id ");
            sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
            sqlBuilder.append(" left join m_appuser user on loan_txn.created_by = user.id ");
            sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
            sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = loan_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
            sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
            sqlBuilder.append(" on (acnttrans.from_loan_transaction_id = loan_txn.id ");
            sqlBuilder.append(" or acnttrans.to_loan_transaction_id = loan_txn.id) ");

            return sqlBuilder.toString();
        }

        public String clientTxnSchema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append(" cli_txn.id AS txn_id, c.id AS cashier_id, ");
            sqlBuilder.append(" case ");
            sqlBuilder.append(" when renum.enum_value in ('PAY_CHARGE') ");
            sqlBuilder.append(" then 103 ");
            sqlBuilder.append(" when renum.enum_value in ('WAIVE_CHARGE') ");
            sqlBuilder.append(" then 104 ");
            sqlBuilder.append(" else ");
            sqlBuilder.append(" 105 ");
            sqlBuilder.append(" end as cash_txn_type, ");
            sqlBuilder.append(" cli_txn.amount as txn_amount, cli_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    " concat (renum.enum_value, ', Client:', cl.id, '-', cl.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append(" 'client' as entity_type, cl.id as entity_id, cli_txn.created_date as created_date, ");
            sqlBuilder.append(
                    " o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append(" from m_client_transaction cli_txn ");
            sqlBuilder.append(
                    " left join r_enum_value renum on cli_txn.transaction_type_enum = renum.enum_id AND renum.enum_name = 'client_transaction_type_enum' ");
            sqlBuilder.append(" left join m_client cl on cli_txn.client_id = cl.id ");
            sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
            sqlBuilder.append(" left join m_appuser user on cli_txn.created_by = user.id ");
            sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
            sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = cli_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");

            return sqlBuilder.toString();
        }

        @Override
        public CashierTransactionData mapRow(final ResultSet rs, final int rowNum) throws SQLException {

            final Long id = rs.getLong("txn_id");
            final Long cashierId = rs.getLong("cashier_id");
            final Integer tType = rs.getInt("txn_type");
            final CashierTxnType txnType = CashierTxnType.getCashierTxnType(tType);
            final BigDecimal txnAmount = rs.getBigDecimal("txn_amount");
            final LocalDate txnLocalDate = JdbcSupport.getLocalDate(rs, "txn_date");
            final String txnNote = rs.getString("txn_note");
            final String entityType = rs.getString("entity_type");
            final Long entityId = rs.getLong("entity_id");
            final OffsetDateTime createdLocalDate = JdbcSupport.getOffsetDateTime(rs, "created_date");

            LocalDate txnDate = null;
            if (txnLocalDate != null) {
                txnDate = txnLocalDate;
            }
            OffsetDateTime createdDate = null;
            if (createdLocalDate != null) {
                createdDate = createdLocalDate;
            }

            final Long officeId = rs.getLong("office_id");
            final String officeName = rs.getString("office_name");
            final Long tellerId = rs.getLong("teller_id");
            final String tellerName = rs.getString("teller_name");
            final String cashierName = rs.getString("cashier_name");

            return CashierTransactionData.instance(id, cashierId, txnType, txnAmount, txnDate, txnNote, entityType, entityId, createdDate,
                    officeId, officeName, tellerId, tellerName, cashierName, null, null, null);
        }
    }

    private static final class CashierTransactionSummaryMapper implements RowMapper<CashierTransactionTypeTotalsData> {

        public String cashierTxnSummarySchema() {

            final StringBuilder sqlBuilder = new StringBuilder(400);

            sqlBuilder.append(" cash_txn_type, sum(txn_amount) as txn_total from ");
            sqlBuilder.append(" (select * from ");
            sqlBuilder.append(" (select txn.id as txn_id, txn.cashier_id as cashier_id, ");
            sqlBuilder.append("    txn.txn_type as cash_txn_type, ");
            sqlBuilder.append("    txn.txn_amount as txn_amount, txn.txn_date as txn_date, txn.txn_note as txn_note, ");
            sqlBuilder.append("    txn.entity_type as entity_type, txn.entity_id as entity_id, txn.created_date as created_date, ");
            sqlBuilder.append(
                    "    o.id as office_id, o.name as office_name, t.id as teller_id, t.name as teller_name, s.display_name as cashier_name ");
            sqlBuilder.append("    from m_cashier_transactions txn ");
            sqlBuilder.append("    left join m_cashiers c on c.id = txn.cashier_id ");
            sqlBuilder.append("    left join m_tellers t on t.id = c.teller_id ");
            sqlBuilder.append("    left join m_office o on o.id = t.office_id ");
            sqlBuilder.append("    left join m_staff s on s.id = c.staff_id ");
            sqlBuilder.append("    where txn.cashier_id = ? ");
            sqlBuilder.append(" AND (( case when c.full_day then Date(txn.created_date) between c.start_date AND c.end_date ");
            sqlBuilder.append(
                    " else ( Date(txn.created_date) between c.start_date AND c.end_date) and  ( TIME(txn.created_date) between TIME(c.start_time) AND TIME(c.end_time))  end) or txn.txn_type = 101) ");
            sqlBuilder.append(" and   txn.currency_code = ? ");
            sqlBuilder.append("    and o.hierarchy like ?  ) cashier_txns ");
            sqlBuilder.append("    UNION ");
            sqlBuilder.append("    (select sav_txn.id as txn_id, c.id as cashier_id, ");
            sqlBuilder.append("    case ");
            sqlBuilder.append("        when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'Annual Fee') ");
            sqlBuilder.append("            then 103 ");
            sqlBuilder.append("        when renum.enum_value in ('withdrawal', 'Waive Charge', 'Interest Posting', 'Overdraft Interest') ");
            sqlBuilder.append("            then 104 ");
            sqlBuilder.append("        else ");
            sqlBuilder.append("            105 ");
            sqlBuilder.append("    end as cash_txn_type, ");
            sqlBuilder.append("    sav_txn.amount as txn_amount, sav_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    "    concat (renum.enum_value, ', Sav:', sav.id, '-', sav.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append("    'savings' as entity_type, sav.id as entity_id, sav_txn.created_date as created_date, ");
            sqlBuilder.append(
                    "    o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append("    from m_savings_account_transaction sav_txn ");
            sqlBuilder.append(
                    "    left join r_enum_value renum on sav_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'savings_transaction_type_enum' ");
            sqlBuilder.append("    left join m_savings_account sav on sav_txn.savings_account_id = sav.id ");
            sqlBuilder.append("    left join m_client cl on sav.client_id = cl.id ");
            sqlBuilder.append("    left join m_office o on cl.office_id = o.id ");
            sqlBuilder.append("    left join m_appuser user on sav_txn.appuser_id = user.id ");
            sqlBuilder.append("    left join m_staff staff on user.staff_id = staff.id ");
            sqlBuilder.append("    left join m_cashiers c on c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = sav_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
            sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
            sqlBuilder.append(" on (acnttrans.from_savings_transaction_id = sav_txn.id ");
            sqlBuilder.append(" or acnttrans.to_savings_transaction_id = sav_txn.id) ");
            sqlBuilder.append("    where sav_txn.is_reversed = false and c.id = ? ");
            sqlBuilder.append(" and sav.currency_code = ? ");
            sqlBuilder.append("    and o.hierarchy like ? ");
            sqlBuilder.append("    and sav_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
            sqlBuilder.append("    and (sav_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) ");
            sqlBuilder.append("    AND acnttrans.id IS NULL  ");
            sqlBuilder.append("    ) ");
            sqlBuilder.append("    UNION ");
            sqlBuilder.append("    ( ");
            sqlBuilder.append("    select loan_txn.id as txn_id, c.id as cashier_id, ");
            sqlBuilder.append("    case ");
            sqlBuilder.append(
                    "        when renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT', 'CHARGE_PAYMENT') ");
            sqlBuilder.append("            then 103 ");
            sqlBuilder.append("        when renum.enum_value in ('DISBURSEMENT', 'WAIVE_INTEREST', 'WRITEOFF', 'WAIVE_CHARGES') ");
            sqlBuilder.append("            then 104 ");
            sqlBuilder.append("        else ");
            sqlBuilder.append("            105 ");
            sqlBuilder.append("    end as cash_txn_type, ");
            sqlBuilder.append("    loan_txn.amount as txn_amount, loan_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    "    concat (renum.enum_value, ', Loan:', loan.id, '-', loan.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append("    'loans' as entity_type, loan.id as entity_id, loan_txn.created_date as created_date, ");
            sqlBuilder.append(
                    "    o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append("    from m_loan_transaction loan_txn ");
            sqlBuilder.append(
                    "    left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'loan_transaction_type_enum' ");
            sqlBuilder.append("    left join m_loan loan on loan_txn.loan_id = loan.id ");
            sqlBuilder.append("    left join m_client cl on loan.client_id = cl.id ");
            sqlBuilder.append("    left join m_office o on cl.office_id = o.id ");
            sqlBuilder.append("    left join m_appuser user on loan_txn.created_by = user.id ");
            sqlBuilder.append("    left join m_staff staff on user.staff_id = staff.id ");
            sqlBuilder.append("    left join m_cashiers c on c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = loan_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
            sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
            sqlBuilder.append(" on (acnttrans.from_loan_transaction_id = loan_txn.id ");
            sqlBuilder.append(" or acnttrans.to_loan_transaction_id = loan_txn.id) ");
            sqlBuilder.append("    where loan_txn.is_reversed = false and c.id = ? ");
            sqlBuilder.append(" and loan.currency_code = ? ");
            sqlBuilder.append("    and o.hierarchy like ? ");
            sqlBuilder.append("    and loan_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
            sqlBuilder.append("    and (loan_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) ");
            sqlBuilder.append("    AND acnttrans.id IS NULL  ");
            sqlBuilder.append("    ) ");
            sqlBuilder.append("    UNION ");
            sqlBuilder.append("    ( ");
            sqlBuilder.append("    SELECT cli_txn.id AS txn_id, c.id AS cashier_id, ");
            sqlBuilder.append("    case ");
            sqlBuilder.append("        WHEN renum.enum_value IN ('PAY_CHARGE') ");
            sqlBuilder.append("            then 103 ");
            sqlBuilder.append("        WHEN renum.enum_value IN ('WAIVE_CHARGE') ");
            sqlBuilder.append("            then 104 ");
            sqlBuilder.append("        else ");
            sqlBuilder.append("            105 ");
            sqlBuilder.append("    end as cash_txn_type, ");
            sqlBuilder.append("    cli_txn.amount as txn_amount, cli_txn.transaction_date as txn_date, ");
            sqlBuilder.append(
                    "    concat (renum.enum_value, ', Client:', cl.id, '-', cl.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
            sqlBuilder.append("    'client' as entity_type, cl.id as entity_id, cli_txn.created_date as created_date, ");
            sqlBuilder.append(
                    "    o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
            sqlBuilder.append("    from m_client_transaction cli_txn ");
            sqlBuilder.append(
                    "    left join r_enum_value renum ON cli_txn.transaction_type_enum = renum.enum_id AND renum.enum_name = 'client_transaction_type_enum' ");
            sqlBuilder.append("    left join m_client cl ON cli_txn.client_id = cl.id ");
            sqlBuilder.append("    left join m_office o ON cl.office_id = o.id ");
            sqlBuilder.append("    left join m_appuser user ON cli_txn.created_by = user.id ");
            sqlBuilder.append("    left join m_staff staff ON user.staff_id = staff.id ");
            sqlBuilder.append("    left join m_cashiers c ON c.staff_id = staff.id ");
            sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = cli_txn.payment_detail_id ");
            sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
            sqlBuilder.append("    where cli_txn.is_reversed = false AND c.id = ?    ");
            sqlBuilder.append(" and cli_txn.currency_code = ? ");
            sqlBuilder.append("    and o.hierarchy LIKE ? ");
            sqlBuilder.append("    and cli_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
            sqlBuilder.append(" and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true)  ");
            sqlBuilder.append("    ) ");
            sqlBuilder.append("    ) txns ");
            sqlBuilder.append("    group by cash_txn_type ");

            return sqlBuilder.toString();
        }

        @Override
        public CashierTransactionTypeTotalsData mapRow(final ResultSet rs, final int rowNum) throws SQLException {

            final Integer cashierTxnType = rs.getInt("cash_txn_type");
            final BigDecimal txnTotal = rs.getBigDecimal("txn_total");

            return CashierTransactionTypeTotalsData.instance(cashierTxnType, txnTotal);
        }
    }

}