/*
 * Created on Jan 12, 2005
 *
 */
package net.sourceforge.fenixedu.persistenceTierOracle.Oracle;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import net.sourceforge.fenixedu.domain.projectsManagement.GeneratedOverheadsReportLine;
import net.sourceforge.fenixedu.domain.projectsManagement.IGeneratedOverheadsReportLine;
import net.sourceforge.fenixedu.persistenceTier.ExcepcaoPersistencia;
import net.sourceforge.fenixedu.util.projectsManagement.ReportType;

import com.linkare.commons.metainfo.Linkare;

/**
 * @author Susana Fernandes
 * 
 */
public class PersistentGeneratedOverheadsReport extends PersistentReport {

    @Linkare(author = "Paulo Zenida", comments = "Used Integer.valueOf() instead of new Integer()")
    public List<IGeneratedOverheadsReportLine> getCompleteReport(ReportType reportType, Integer costCenterCoordinatorId,
	    Boolean it) throws ExcepcaoPersistencia {
	List<IGeneratedOverheadsReportLine> result = new ArrayList<IGeneratedOverheadsReportLine>();
	try {
	    PersistentSuportOracle p = PersistentSuportOracle.getProjectDBInstance(it);
	    p.startTransaction();
	    String tableOrView = getTableOrViewName(p, reportType);
	    StringBuilder stringBuffer = new StringBuilder();
	    stringBuffer
		    .append("select \"UNID_EXPL\", \"ID_PROJ\", \"ACRONIMO\", \"ID_COORD\", \"NOME\", \"TIPO_OVH\", \"DATE_AUTOR\", \"DESCRICAO\", \"VALOR_RECEITA\", \"PCT_OVH\", \"VALOR_OVH\" from ");
	    stringBuffer.append(tableOrView);
	    stringBuffer.append(" where cc_coord='");
	    stringBuffer.append(costCenterCoordinatorId);
	    stringBuffer.append("' order by \"DATE_AUTOR\", \"UNID_EXPL\", \"ID_PROJ\"");
	    PreparedStatement stmt = p.prepareStatement(stringBuffer.toString());
	    ResultSet rs = stmt.executeQuery();
	    while (rs.next()) {
		IGeneratedOverheadsReportLine report = new GeneratedOverheadsReportLine();
		report.setExplorationUnit(Integer.valueOf(rs.getInt("UNID_EXPL")));
		report.setProjectNumber(Integer.valueOf(rs.getInt("ID_PROJ")));
		report.setAcronim(rs.getString("ACRONIMO"));
		report.setCoordinatorName(rs.getString("NOME"));
		report.setCoordinatorNumber(Integer.valueOf(rs.getInt("ID_COORD")));
		report.setType(rs.getString("TIPO_OVH"));
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		Date date = rs.getDate("DATE_AUTOR");
		report.setDate(formatter.format(date));
		report.setDescription(rs.getString("DESCRICAO"));
		report.setRevenue(Double.valueOf(rs.getDouble("VALOR_RECEITA")));
		report.setOverheadPerscentage(Double.valueOf(rs.getDouble("PCT_OVH")));
		report.setOverheadValue(Double.valueOf(rs.getDouble("VALOR_OVH")));
		result.add(report);
	    }
	    rs.close();
	    p.commitTransaction();
	} catch (SQLException e) {
	    throw new ExcepcaoPersistencia();
	}
	return result;
    }
}