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

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

import net.sourceforge.fenixedu.domain.projectsManagement.ISummaryReportLine;
import net.sourceforge.fenixedu.domain.projectsManagement.SummaryReportLine;
import net.sourceforge.fenixedu.persistenceTier.ExcepcaoPersistencia;
import net.sourceforge.fenixedu.util.projectsManagement.ReportType;

import com.linkare.commons.metainfo.Linkare;

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

    @Linkare(author = "Paulo Zenida", comments = "Used Integer.valueOf() instead of new Integer()")
    public List readByCoordinatorCode(ReportType reportType, Integer coordinatorCode, Boolean it) throws ExcepcaoPersistencia {
	List result = new ArrayList();

	try {
	    PersistentSuportOracle p = PersistentSuportOracle.getProjectDBInstance(it);
	    p.startTransaction();
	    String tableOrView = getTableOrViewName(p, reportType);

	    String query = new String(
		    "select \"NºProj\", \"Acrónimo\", \"Unid Expl\", \"Tipo\", \"Orçamento\", \"Máximo Financiável\", \"Receita\", \"Despesa\", \"Adiantamentos por Justificar\" ,\"Saldo Tesouraria\", \"Cabimentos por Executar\", \"Saldo Orçamental\" from "
			    + tableOrView + " where IDCOORD='" + coordinatorCode + "'");
	    PreparedStatement stmt = p.prepareStatement(query);
	    ResultSet rs = stmt.executeQuery(query);
	    while (rs.next()) {
		ISummaryReportLine report = new SummaryReportLine();
		report.setCoordinatorCode(coordinatorCode);
		report.setProjectCode(Integer.valueOf(rs.getInt("NºProj")));
		report.setAcronym(rs.getString("Acrónimo"));
		report.setExplorationUnit(Integer.valueOf(rs.getInt("Unid Expl")));
		report.setType(rs.getString("Tipo"));
		report.setBudget(Double.valueOf(rs.getDouble("Orçamento")));
		report.setMaxFinance(Double.valueOf(rs.getDouble("Máximo Financiável")));
		report.setRevenue(Double.valueOf(rs.getDouble("Receita")));
		report.setExpense(Double.valueOf(rs.getDouble("Despesa")));
		report.setAdiantamentosPorJustificar(Double.valueOf(rs.getDouble("Adiantamentos por Justificar")));
		report.setTreasuryBalance(Double.valueOf(rs.getDouble("Saldo Tesouraria")));
		report.setCabimentoPorExecutar(Double.valueOf(rs.getDouble("Cabimentos por Executar")));
		report.setBudgetBalance(Double.valueOf(rs.getDouble("Saldo Orçamental")));

		result.add(report);
	    }

	    rs.close();
	    p.commitTransaction();
	} catch (SQLException e) {
	    throw new ExcepcaoPersistencia();
	}
	return result;
    }

    @Linkare(author = "Paulo Zenida", comments = "Used Integer.valueOf() instead of new Integer()")
    public List readByCoordinatorAndProjectCodes(ReportType reportType, Integer coordinatorCode, List projectCodes, Boolean it)
	    throws ExcepcaoPersistencia {
	List result = new ArrayList();
	try {
	    PersistentSuportOracle p = PersistentSuportOracle.getProjectDBInstance(it);
	    p.startTransaction();
	    String tableOrView = getTableOrViewName(p, reportType);
	    StringBuilder queryBuffer = new StringBuilder();
	    queryBuffer
		    .append("select \"NºProj\", \"Acrónimo\", \"Unid Expl\", \"Tipo\", \"Orçamento\", \"Máximo Financiável\", \"Receita\", \"Despesa\", \"Adiantamentos por Justificar\" ,\"Saldo Tesouraria\", \"Cabimentos por Executar\", \"Saldo Orçamental\" from ");
	    queryBuffer.append(tableOrView);
	    queryBuffer.append(" where IDCOORD='");
	    queryBuffer.append(coordinatorCode);
	    queryBuffer.append("'");
	    if (projectCodes != null && projectCodes.size() != 0) {
		queryBuffer.append(" and \"NºProj\" IN (");
		for (int i = 0; i < projectCodes.size(); i++) {
		    if (i != 0)
			queryBuffer.append(", ");
		    queryBuffer.append(projectCodes.get(i));
		}
		queryBuffer.append(")");
	    }
	    queryBuffer.append(" order by \"NºProj\"");
	    String query = queryBuffer.toString();
	    PreparedStatement stmt = p.prepareStatement(query);
	    ResultSet rs = stmt.executeQuery(query);
	    while (rs.next()) {
		ISummaryReportLine report = new SummaryReportLine();
		report.setCoordinatorCode(coordinatorCode);
		report.setProjectCode(Integer.valueOf(rs.getInt("NºProj")));
		report.setAcronym(rs.getString("Acrónimo"));
		report.setExplorationUnit(Integer.valueOf(rs.getInt("Unid Expl")));
		report.setType(rs.getString("Tipo"));
		report.setBudget(Double.valueOf(rs.getDouble("Orçamento")));
		report.setMaxFinance(Double.valueOf(rs.getDouble("Máximo Financiável")));
		report.setRevenue(Double.valueOf(rs.getDouble("Receita")));
		report.setExpense(Double.valueOf(rs.getDouble("Despesa")));
		report.setAdiantamentosPorJustificar(Double.valueOf(rs.getDouble("Adiantamentos por Justificar")));
		report.setTreasuryBalance(Double.valueOf(rs.getDouble("Saldo Tesouraria")));
		report.setCabimentoPorExecutar(Double.valueOf(rs.getDouble("Cabimentos por Executar")));
		report.setBudgetBalance(Double.valueOf(rs.getDouble("Saldo Orçamental")));

		result.add(report);
	    }
	    rs.close();
	    p.commitTransaction();
	} catch (SQLException e) {
	    throw new ExcepcaoPersistencia();
	}
	return result;
    }
}