/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.fp.general.rules.query.trans;

import java.sql.Date;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import com.fp.common.helper.Constant;
import com.fp.dto.Response;
import com.fp.dto.query.QueryBean;
import com.fp.dto.query.QueryRequest;
import com.fp.dto.rules.QueryRule;
import com.fp.persistence.commondb.PersistenceHelper;
import com.fp.persistence.pcustomer.balance.TcustPersonBalance;
import com.fp.persistence.pcustomer.gene.TcustPersonDetail;

/**
 *Clase que genera el patrimonio tecnico de una persona dado su codigo
 * @author hinga
 */
public class ObtainTechnicalPatrimony extends QueryRule {

    @Override
    public QueryRequest process(QueryRequest pQueryRequest) throws Exception {
        Response response = pQueryRequest.getResponse();
        com.fp.bpm.query.Query query = new com.fp.bpm.query.Query();
        query.process(pQueryRequest);
        QueryBean queryBean = (QueryBean) pQueryRequest.get("TCUSTPERSONDETAIL");
        TcustPersonDetail tcustPersonDetail = (TcustPersonDetail) response.get("TCUSTPERSONDETAIL");
        Integer personcode = Integer.valueOf(queryBean.getCriteriaValue("pk.personcode").toString());
        TcustPersonBalance tcustPersonBalance = this.getBalancePerPerson(personcode);
        if (tcustPersonBalance != null) {
            Object patri = this.getPatrimony(personcode, tcustPersonBalance.getPk().getBalancedate(), tcustPersonBalance.getPk().getBalancecatalog());
            tcustPersonDetail.addAddtionalInfo("patrimony", patri);
        } else {
            tcustPersonDetail.addAddtionalInfo("patrimony", 0);
        }

        return pQueryRequest;
    }
    /** Sentencia que devuelve el balance dado el código de persona.*/
    public static final String JPQL_BALANCE_PER_PERSON = "from TcustPersonBalance p where p.pk.personcode = :personcode "
            + "and   p.statuscatalog = '1' "
            + "and   p.pk.dateto = :dateto "
            + "and   p.pk.balancedate = (select max(c.pk.balancedate) from TcustPersonBalance c where c.pk.personcode = p.pk.personcode and  c.statuscatalog = '1' and p.pk.dateto = c.pk.dateto)";

    /**
     * Metodo que entrega un balance dado un codigo de persona.
     * @param personcode Codigo de la persona
     * @return TcustPersonBalance
     */
    public TcustPersonBalance getBalancePerPerson(Integer personcode) throws Exception {
        TcustPersonBalance tcustPersonBalance = null;
        Query qry = PersistenceHelper.getEntityManager().createQuery(ObtainTechnicalPatrimony.JPQL_BALANCE_PER_PERSON);
        qry.setParameter("personcode", personcode);
        qry.setParameter("dateto", Constant.getDefaultExpiryTimestamp());
        List<Object> tBalance = qry.getResultList();
        if (!tBalance.isEmpty()) {
            tcustPersonBalance = (TcustPersonBalance)tBalance.get(0);
        }
        return tcustPersonBalance;
    }

    /**Sentencia que devuelve el patrimonio técnico del último balance del tipo BG */
    public static final String HQL_TECHNICAL_PATRIMONY_BG = "select sum(t.valueaccount) "
            + "from TcustBalanceFinancial t"
            + " where t.pk.personcode = :pPersonCode "
            + "and t.pk.dateto = :pDateto "
            + "and t.pk.balancecatalog = :pBalancecatalog "
            + "and t.pk.balancecatalogcode = 'BALANCETYPE' "
            + "and t.pk.accountcode in ('51','52','53','54','55','56','57','58') "
            + "and t.pk.balancedate = :pBalancedate";

    /**Sentencia que devuelve el patrimonio técnico del último balance del tipo IFI */
    public static final String HQL_TECHNICAL_PATRIMONY_IFIS = "select t.valueaccount "
            + "from TcustBalanceFinancial t "
            + "where t.pk.personcode = :pPersonCode "
            + "and t.pk.dateto = :pDateto "
            + "and t.pk.balancecatalog = :pBalancecatalog "
            + "and t.pk.balancecatalogcode = 'BALANCETYPE' "
            + "and t.pk.accountcode = '3' "
            + "and t.pk.balancedate = :pBalancedate";

    /**
     * 
     * @param personcode Código de la persona
     * @param balanceDate Date balanceDate
     * @return Object
     * @throws Exception
     */
    public Object getPatrimony(Integer personcode, Date balanceDate, String balanceCatalog) throws Exception {
        Query qry = null;
        Object obj = null;
        if (balanceCatalog.compareTo("BG") == 0) {
            qry = PersistenceHelper.getEntityManager().createQuery(ObtainTechnicalPatrimony.HQL_TECHNICAL_PATRIMONY_BG);
        } else if (balanceCatalog.compareTo("BG(IFIS)") == 0) {
            qry = PersistenceHelper.getEntityManager().createQuery(ObtainTechnicalPatrimony.HQL_TECHNICAL_PATRIMONY_IFIS);
        }
        qry.setParameter("pPersonCode", personcode);
        qry.setParameter("pDateto", Constant.getDefaultExpiryTimestamp());
        qry.setParameter("pBalancecatalog", balanceCatalog);
        qry.setParameter("pBalancedate", balanceDate);
        try {
            obj = qry.getSingleResult();
        } catch (NoResultException e) {
        }
        return obj;
    }
}