/*
 * 
 */
package com.fp.bpmlib.report;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

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

import com.fp.common.properties.PropertiesHandler;
import com.fp.excel.XLSXUtil;
import com.fp.persistence.commondb.PersistenceHelper;

// TODO: Auto-generated Javadoc
/**
 * Class ExportFlows encargada de.
 * 
 * @author gfiallos
 */
public class ExportFlows extends DownloadFileQuery {

    /** serialVersionUID. */
    private static final long serialVersionUID = 1L;

    /** Constante SQL. */
    private static final String SQL = "SELECT  l.solicitudenumber, (  SELECT z.description FROM "
            + "          tgenebranch z  WHERE  z.branchcode=l.branchcode "
            + "          AND z.companycode=l.companycode) branch, (  SELECT  z.description FROM "
            + "          tgeneoffice z WHERE z.officecode=l.officecode AND z.branchcode=l.branchcode "
            + "          AND z.companycode=l.companycode) office, l.terminalcode originterminal,l.usercode orginuser, ( SELECT "
            + "          z.description FROM tgenemodule z WHERE " + "          z.modulecode=l.transactionmodule ) module, ( SELECT z.transactionmodule||'-'||z.transactioncode||'-'||z.transactionversion||' '||z.name FROM "
            + "          tgenetransaction z WHERE z.transactionmodule=l.transactionmodule " + "          AND z.transactioncode=l.transactioncode "
            + "          AND z.transactionversion=l.transactionversion) origen ,a.activityname,a.sequence,a.usercode,"
            + " a.groupcode,a.created,a.started,a.completed,a.expectedtime/1000,0 ,a.response , ( SELECT z.name "
            + "        FROM tgenetransaction z WHERE z.transactionmodule=a.module "
            + "          AND z.transactioncode=a.transaction AND z.transactionversion=a.version) tarea FROM "
            + "    tbpmprocessinstance i LEFT OUTER JOIN tbpmactivities a ON a.journalid=i.journalid , tgenetransactionlog l "
            + "  WHERE l.journalid=i.journalid AND i.finalized='Y' " + " and i.creationdate between :init and :end";

    /** Constante ORDER. */
    private static final String ORDER = "  ORDER BY l.aditionaldata, a.created";

    /**
     * Obtiene el valor de content.
     * 
     * @return Valor de content
     * @throws Exception la exception
     */
    @Override
    public byte[] getContent() throws Exception {
        //Session s = PersistenceHelper.getSession();
        //SQLQuery sql = s.createSQLQuery(ExportFlows.SQL + ExportFlows.ORDER);
        //sql.setDate("init", this.queryRequest.getDate("creationdate"));
        //sql.setDate("end", this.queryRequest.getDate("enddate"));
    	PropertiesHandler ph = new PropertiesHandler("bpmmessages");
        EntityManager em = PersistenceHelper.getEntityManager();
        Query qry = em.createNativeQuery(ExportFlows.SQL + ExportFlows.ORDER);
        qry.setParameter("init", this.queryRequest.getDate("creationdate"));
        qry.setParameter("end", this.queryRequest.getDate("enddate"));
        
        @SuppressWarnings("unchecked")
		List<Object> results = qry.getResultList();
        
        XLSXUtil xlsx = new XLSXUtil();
        
        
        
        //ScrollableResults cursor = sql.scroll();
        try {
            List<Object> label = new ArrayList<Object>();
            label.add(ph.getStringValue("lbl_description"));
            label.add(ph.getStringValue("lbl_branch"));
            label.add(ph.getStringValue("lbl_office"));
            label.add(ph.getStringValue("lbl_terminal"));
            label.add(ph.getStringValue("lbl_user"));
            label.add(ph.getStringValue("lbl_module"));
            label.add(ph.getStringValue("lbl_transflow"));
            label.add(ph.getStringValue("lbl_activityname"));
            label.add(ph.getStringValue("lbl_sequence"));
            label.add(ph.getStringValue("lbl_userasigned"));
            label.add(ph.getStringValue("lbl_groupAsign"));
            label.add(ph.getStringValue("lbl_created"));
            label.add(ph.getStringValue("lbl_started"));
            label.add(ph.getStringValue("lbl_completed"));
            label.add(ph.getStringValue("lbl_expectedtime"));
            label.add(ph.getStringValue("lbl_duracion"));
            label.add(ph.getStringValue("lbl_response"));
            label.add(ph.getStringValue("lbl_task"));
            xlsx.addRecord(label.toArray(), "H");
            for(Object object: results){
            	Object[] obj = (Object[])object;
            	if(obj[12] instanceof Date && obj[13] instanceof Date){
		            Calendar c1  =Calendar.getInstance();
		            c1.setTime((Date)obj[12]); 
		            Calendar c2  =Calendar.getInstance();
		            c2.setTime((Date)obj[13]);
		            obj[15] = (c2.getTimeInMillis() - c1.getTimeInMillis())/1000;
            	}
            	xlsx.addRecord(obj,"B");
            }
//            while (cursor.next()) {
//                xlsx.addRecord(cursor.get());
//            }
        } finally {
            //cursor.close();
        }
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            xlsx.save(out);
        } finally {
            out.close();
        }
        return out.toByteArray();
    }

    /**
     * Obtiene el valor de extension.
     * 
     * @return Valor de extension
     * @throws Exception la exception
     */
    @Override
    public String getExtension() throws Exception {
        return "xlsx";
    }

    /**
     * Obtiene el valor de content type.
     * 
     * @return Valor de content type
     * @throws Exception la exception
     */
    @Override
    public String getContentType() throws Exception {
        return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    }

}