<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
 http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
 http://www.springframework.org/schema/context
 http://www.springframework.org/schema/context/spring-context-3.2.xsd">

	<!-- RFT-REPORT -->
	<bean id = "retrieveELPnsDetailsRepoImpl" class = "gov.bsp.lcmis.collaterals.report.dao.RetrieveELPnsDetailsRepoImpl">
		<property name = "dataSource" ref="dataSource"/>
		<property name = "endDateSelector" ref="endDateSelector"/>
		<property name="QUERY_RETRIEVE_PN_DETAILS" value="SELECT bank_name, date_close, pnt.pn_number, pn_status, pnt.date_granted AS pn_date_granted, pnt.maturity_date AS pn_maturity_date,
                    lt.loan_interest_rate AS bsp_pn_rate, lt.date_granted AS loan_date_granted, lt.maturity_date AS loan_maturity_date, reprice_rate_flag,
                    lt.amount_granted AS loan_amount_granted, collateral_number, entity_name, full_name, outstanding_principal, loan_term, cwt_flag, loan_application_number
                    , payment_date
                    FROM bank_tbl bt
                    INNER JOIN loan_tbl lt ON bt.bank_id = lt.ref_bank_id
                    INNER JOIN pn_tbl pnt ON lt.loan_id = pnt.ref_loan_id
                    LEFT OUTER JOIN pn_collateral pnc ON pnt.pn_id = pnc.ref_pn_id
                    RIGHT OUTER JOIN COLLATERAL_TBL ct ON pnc.ref_collateral_id = ct.collateral_id
                    LEFT OUTER JOIN end_user_borrower_tbl eubt ON pnt.ref_borrower_id = eubt.borrower_id
                    LEFT OUTER JOIN balance_tbl balt ON pnt.ref_balance_id = balt.balance_id
                    LEFT OUTER JOIN (select MAX(ppt.payment_date) AS payment_date,ppt.pn_number,pn_id from payment_pending_tbl ppt
                            join transaction_tbl txn on txn.transaction_id = ppt.ref_transaction_id
                            join pn_tbl pn on pn.pn_number = ppt.pn_number
                            join approval_status_tbl ast on ast.approval_status_id = txn.ref_approval_status_id
                            WHERE  UPPER(ast.approval_status) IN (:APPROVAL_STATUS)
                            AND txn.transaction_type NOT IN (:TRANSACTION_TYPE)
                            GROUP BY pn_id,ppt.pn_number) ppt
                            ON ppt.pn_number = pnt.pn_number
                    WHERE pnt.pn_id IN (:PN_ID)"></property>
	</bean>




</beans>



--------------------------

package gov.bsp.lcmis.collaterals.report.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import gov.bsp.lcmis.collaterals.report.repo.RetrieveELPnsRepo;
import gov.bsp.lcmis.internal.constants.ApprovalStatusConstants;
import gov.bsp.lcmis.internal.constants.TransactionType;
import gov.bsp.lcmis.internal.report.dao.constants.Constants;
import gov.bsp.lcmis.internal.report.dao.constants.QueryConstants;
import gov.bsp.lcmis.internal.report.domain.PromissoryNote;
import gov.bsp.lcmis.internal.report.domain.RvcReport;
import gov.bsp.lcmis.internal.utilities.EndDateSelector;

/**
 * #FSM-EL-RVC-006 Generate EL RVC Report
 */
public class RetrieveELPnsDetailsRepoImpl implements
        RetrieveELPnsRepo<RvcReport>,
        Constants,
        QueryConstants,
        ApplicationContextAware{

    private static final Logger log = LoggerFactory.getLogger(RetrieveELPnsDetailsRepoImpl.class);

    private NamedParameterJdbcTemplate namedJdbc;

    private ApplicationContext applicationContext;

    private EndDateSelector endDateSelector;

    private static String QUERY_RETRIEVE_PN_DETAILS;

    /**
     * Retrieve EL PN info
     */
	public List<PromissoryNote> getPnDetails(List<Integer> pnIds) throws DataAccessException {
    	if (log.isDebugEnabled()) {
            log.debug("MCI >> retrieveELPnsDetails");
        }
    	List <PromissoryNote> elPnDetails = null;
        MapSqlParameterSource data = new MapSqlParameterSource();


        //TRANSACTION TYPES AND TRANSACTION STATUSES
        //FOR THE RETRIEVAL OF LAST PAYMENT DATE OF EACH PN
        List<String> includedTransactionTypes = new ArrayList<>();
        includedTransactionTypes.add(TransactionType.REVERSAL.getValue());
        includedTransactionTypes.add(TransactionType.CXS.getValue());
        includedTransactionTypes.add(TransactionType.RXS.getValue());
        includedTransactionTypes.add(TransactionType.ADJUSTMENT.getValue());

        List<String> includedTransactionStatuses = new ArrayList<>();
        includedTransactionStatuses.add(ApprovalStatusConstants.COMPLETED.getValue().toUpperCase());
        includedTransactionStatuses.add(ApprovalStatusConstants.BOOKED.getValue().toUpperCase());
        includedTransactionStatuses.add(ApprovalStatusConstants.POSTED.getValue().toUpperCase());
        includedTransactionStatuses.add(ApprovalStatusConstants.POLLING_TO_CFAS.getValue().toUpperCase());


        data.addValue(COLUMN_PN_ID, pnIds);
        data.addValue(APPROVAL_STATUS, includedTransactionStatuses);
        data.addValue(COLUMN_SAP_TRANSACTION_TYPE, includedTransactionTypes);


        try {
        	elPnDetails = namedJdbc.query(QUERY_RETRIEVE_PN_DETAILS, data, new RowMapper<PromissoryNote>() {

                public PromissoryNote mapRow(ResultSet rs, int arg1) throws SQLException {
                    PromissoryNote pnDetails = applicationContext.getBean(PromissoryNote.class);
                    pnDetails.setPnId(rs.getInt(COLUMN_PN_ID));
                    pnDetails.setBankName(rs.getString(COLUMN_BANK_NAME));
                    pnDetails.setCwtFlag(rs.getString(COLUMN_BANK_CWT_FLAG));
                    pnDetails.setBankClosureDate(rs.getDate(COLUMN_DATE_CLOSE));
                    pnDetails.setPnNumber(rs.getString(COLUMN_PN_NUMBER));
                    pnDetails.setPnStatus(rs.getString(COLUMN_PN_STATUS));
                    pnDetails.setPnGrantedDate(rs.getDate(COLUMN_PN_DATE_GRANTED));
                    pnDetails.setPnMaturityDate(rs.getDate(COLUMN_PN_MATURITY_DATE));
                    pnDetails.setLoanNumber(rs.getInt(COLUMN_LOAN_NUMBER));
                    pnDetails.setLoanAmount(rs.getBigDecimal(COLUMN_LOAN_AMOUNT));
                    pnDetails.setLoanRate(rs.getBigDecimal(COLUMN_LOAN_INTEREST_RATE));
                    pnDetails.setLoanGrantedDate(rs.getDate(COLUMN_LOAN_DATE_GRANTED));
                    pnDetails.setLoanMaturityDate(rs.getDate(COLUMN_LOAN_MATURITY_DATE));
                    pnDetails.setLoanTerm(rs.getBigDecimal(COLUMN_LOAN_TERM));
                    pnDetails.setRepriceRateFlag(rs.getInt(COLUMN_REPRICE_RATE_FLAG));
                    pnDetails.setCollateralNumber(rs.getString(COLUMN_COLLATERAL_NUMBER));
                    pnDetails.setFullName(rs.getString(COLUMN_FULL_NAME));
                    pnDetails.setCompanyName(rs.getString(COLUMN_ENTITY_NAME));
                    pnDetails.setOutstandingPrincipal(rs.getBigDecimal(COLUMN_OUTSTANDING_PRINCIPAL));

                    return pnDetails;
                }

            });
        } catch (EmptyResultDataAccessException e) {
            elPnDetails = null;
            if (log.isDebugEnabled()) {
                log.debug("Null elPnDetails object.");
            }
        } catch (DataAccessException e) {
            throw e;
        }
        if (log.isDebugEnabled()) {
            log.debug("MCO >> retrieveELPnsDetails");
        }

        return elPnDetails;
	}

    public void setDataSource(DataSource dataSource) {
        this.namedJdbc = new NamedParameterJdbcTemplate(dataSource);
    }

    public void setApplicationContext(ApplicationContext appcontext) throws BeansException {
        this.applicationContext =appcontext;
    }

    public void setEndDateSelector(EndDateSelector endDateSelector) {
		this.endDateSelector = endDateSelector;
	}

    public static void setQUERY_RETRIEVE_PN_DETAILS(String qUERY_RETRIEVE_PN_DETAILS) {
		QUERY_RETRIEVE_PN_DETAILS = qUERY_RETRIEVE_PN_DETAILS;
	}

}
---------------

	<import resource="classpath:collaterals-domain/*.xml" />