package com.fp.general.rules.query.code;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.ScrollableResults;

import com.fp.dto.Response;
import com.fp.dto.query.QueryRequest;
import com.fp.dto.rules.QueryRule;
import com.fp.general.db.DataHelper;
import com.fp.persistence.commondb.PersistenceHelper;
import com.fp.persistence.pgeneral.auth.TgeneSolicitudeClientdata;

/**
 * Clase que se encarga de consultar los valores para verificar los clientes de una solicitud
 * 
 * @author scastillo
 */
public class VerifyClientData extends QueryRule {

    String transactionmodule;

    Integer transactioncode;

    String solicitudnumber;

    Integer solicitudsequence;

    Integer personcode;

    String module;

    String productcode;

    String subproductcode;

    String persontype;

    String relationshipcode;

    /**
     * Sentencia SQL para obtener los datos de la página a cargar
     */
    private static final String SQL = "select tgt.TITLE t, tgt.PAGE p, tgt.QUERYFUNCTION q, tgt.SAVEFUNCTION s" + "  from TGENETRANSACTIONPAGES tgt"
            + " where tgt.TRANSACTIONMODULE = :transactionmodule" + "   and tgt.TRANSACTIONCODE = :transactioncode and tgt.ACTIVE='Y' "
            + "   and tgt.TITLE in (select tgs.title" + "                       from TGENESOLICITUDECLIENTDATA tgs"
            + "                      where tgs.SOLICITUDNUMBER = :solicitudnumber"
            + "                        and tgs.SOLICITUDSEQUENCE = :solicitudsequence" + "                        and tgs.PERSONCODE = :personcode)";

    /**
     * Metodo que se encarga de
     * 
     * @param pQueryRequest
     * @return
     * @throws Exception
     */
    @Override
    public QueryRequest process(QueryRequest pQueryRequest) throws Exception {
        Response response = pQueryRequest.getResponse();
        this.transactionmodule = (String) pQueryRequest.get("transactionmodule");
        this.transactioncode = (Integer) pQueryRequest.get("transactioncode");
        this.solicitudnumber = (String) pQueryRequest.get("solicitudnumber");
        this.solicitudsequence = (Integer) pQueryRequest.get("solicitudsequence");
        this.personcode = (Integer) pQueryRequest.get("personcode");
        this.module = (String) pQueryRequest.get("module");
        this.productcode = (String) pQueryRequest.get("productcode");
        this.subproductcode = (String) pQueryRequest.get("subproductcode");
        this.persontype = (String) pQueryRequest.get("persontype");
        this.relationshipcode = (String) pQueryRequest.get("relationshipcode");
        response.put("transactions", this.getTransactionInfo());
        return pQueryRequest;
    }

    /**
     * Metodo que ejecuta la sentencia sql para obtener los datos de la base de datos
     * 
     * @param pQueryRequest
     * @return
     * @throws Exception
     */
    private ScrollableResults executeQuery() throws Exception {
        ScrollableResults rSet = null;
        SQLQuery sql = PersistenceHelper.getSession().createSQLQuery(VerifyClientData.SQL);
        sql.setString("transactionmodule", this.transactionmodule);
        sql.setInteger("transactioncode", this.transactioncode);
        sql.setString("solicitudnumber", this.solicitudnumber);
        sql.setInteger("solicitudsequence", this.solicitudsequence);
        sql.setInteger("personcode", this.personcode);
        rSet = sql.scroll();
        return rSet;
    }

    /**
     * Metodo que se encarga de armar la estructura para la verificacion de clientes
     * 
     * @return
     * @throws Exception
     */
    private List<Map<String, Object>> getTransactionInfo() throws Exception {
        ScrollableResults rSet = null;
        List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
        try {
            rSet = this.executeQuery();
            while (rSet.next()) {
                Object[] obj = rSet.get();
                Map<String, Object> mData = new HashMap<String, Object>();
                mData.put("t", obj[0]);// titulo
                mData.put("p", obj[1]);// pagina
                mData.put("q", obj[2]);// funcion query
                mData.put("s", obj[3]);// funcion save
                TgeneSolicitudeClientdata tgeneSolicitudeClientdata = DataHelper.getInstance().getTgeneSolicitudeClientdata(this.solicitudnumber,
                        this.solicitudsequence, (String) obj[0], this.personcode);
                mData.put("r", tgeneSolicitudeClientdata.getRecordversion());// Record version
                mData.put("c", tgeneSolicitudeClientdata.getChecked());// Checked
                mData.put("o", tgeneSolicitudeClientdata.getPresentationorder());// Orden
                mData.put("m", "N");
                ldata.add(mData);
            }
        } finally {
            rSet.close();
        }
        return ldata;
    }
}