finreport.java

Java写的ERP系统

/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is             Compiere  ERP & CRM Smart Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.report;

import java.sql.*;
import java.util.*;
import java.math.*;

import org.compiere.process.*;
import org.compiere.model.*;
import org.compiere.print.*;
import org.compiere.util.*;

/**
 *  Financial Report Engine
 *
 *  @author Jorg Janke
 *  @version $Id: FinReport.java,v 1.10 2003/04/30 06:24:22 jjanke Exp $
 */
public class FinReport extends SvrProcess
{
	/**
	 * 	Financial Report Constructor
	 */
	public FinReport()
	{
		super();
		log.info(" ");
	}	//	FinReport

	/**	Period Parameter				*/
	private int					m_C_Period_ID = 0;
	/**	Org Parameter					*/
	private int					m_Org_ID = 0;
	/**	BPartner Parameter				*/
	private int					m_C_BPartner_ID = 0;
	/**	Product Parameter				*/
	private int					m_M_Product_ID = 0;
	/**	Project Parameter				*/
	private int					m_C_Project_ID = 0;
	/**	Activity Parameter				*/
	private int					m_C_Activity_ID = 0;
	/**	SalesRegion Parameter				*/
	private int					m_C_SalesRegion_ID = 0;
	/**	Campaign Parameter				*/
	private int					m_C_Campaign_ID = 0;

	/**	Report Definition				*/
	private MReport				m_report = null;
	/**	Periods in Calendar				*/
	private FinReportPeriod[]	m_periods = null;
	/**	Index of m_C_Period_ID in m_periods		**/
	private int					m_reportPeriod = -1;
	/**	Parameter Where Clause			*/
	private StringBuffer		m_parameterWhere = new StringBuffer();
	/**	The Report Columns				*/
	private MReportColumn[] 	m_columns;
	/** The Report Lines				*/
	private MReportLine[] 		m_lines;


	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		StringBuffer sb = new StringBuffer ("FinReport Prepare - Record_ID=")
			.append(getRecord_ID());
		//	Parameter
		Parameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].ParameterName;
			if (para[i].Parameter == null)
				;
			else if (name.equals("C_Period_ID"))
				m_C_Period_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("Org_ID"))
				m_Org_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_BPartner_ID"))
				m_C_BPartner_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("M_Product_ID"))
				m_M_Product_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_Project_ID"))
				m_C_Project_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_Activity_ID"))
				m_C_Activity_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_SalesRegion_ID"))
				m_C_SalesRegion_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_Campaign_ID"))
				m_C_Campaign_ID = ((BigDecimal)para[i].Parameter).intValue();
			else
				log.error("prepare - Unknown Parameter: " + name);
		}
		//	Optional Org
		if (m_Org_ID != 0)
			m_parameterWhere.append(" AND AD_Org_ID=").append(m_Org_ID);
		//	Optional BPartner
		if (m_C_BPartner_ID != 0)
			m_parameterWhere.append(" AND C_BPartner_ID=").append(m_C_BPartner_ID);
		//	Optional Product
		if (m_M_Product_ID != 0)
			m_parameterWhere.append(" AND M_Product_ID=").append(m_M_Product_ID);
		//	Optional Project
		if (m_C_Project_ID != 0)
			m_parameterWhere.append(" AND C_Project_ID=").append(m_C_Project_ID);
		//	Optional Activity
		if (m_C_Activity_ID != 0)
			m_parameterWhere.append(" AND C_Activity_ID=").append(m_C_Activity_ID);
		//	Optional Campaign
		if (m_C_Campaign_ID != 0)
			m_parameterWhere.append(" AND C_Campaign_ID=").append(m_C_Campaign_ID);
		//	Optional Sales Region
		if (m_C_SalesRegion_ID != 0)
			m_parameterWhere.append(" AND C_SalesRegion_ID=").append(m_C_SalesRegion_ID);

		//	Load Report Definition
		m_report = new MReport (getCtx(), getRecord_ID());
		sb.append(" - ").append(m_report);
		//
		setPeriods();
		sb.append(" - C_Period_ID=").append(m_C_Period_ID)
			.append(" - ").append(m_parameterWhere);
		//
		log.debug(sb.toString());
	//	m_report.list();
	}	//	prepare

	/**
	 * 	Set Periods
	 */
	private void setPeriods()
	{
		log.info("setPeriods - C_Calendar_ID=" + m_report.getC_Calendar_ID());
		Timestamp today = TimeUtil.getDay(System.currentTimeMillis());
		ArrayList list = new ArrayList();

		String sql = "SELECT p.C_Period_ID, p.Name, p.StartDate, p.EndDate, MIN(p1.StartDate) "
			+ "FROM C_Period p "
			+ " INNER JOIN C_Year y ON (p.C_Year_ID=y.C_Year_ID),"
			+ " C_Period p1 "
			+ "WHERE y.C_Calendar_ID=?"
			+ " AND p.PeriodType='S' "
			+ " AND p1.C_Year_ID=y.C_Year_ID AND p1.PeriodType='S' "
			+ "GROUP BY p.C_Period_ID, p.Name, p.StartDate, p.EndDate "
			+ "ORDER BY p.StartDate";

		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, m_report.getC_Calendar_ID());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				FinReportPeriod frp = new FinReportPeriod (rs.getInt(1), rs.getString(2),
					rs.getTimestamp(3), rs.getTimestamp(4), rs.getTimestamp(5));
				list.add(frp);
				if (m_C_Period_ID == 0 && frp.inPeriod(today))
					m_C_Period_ID = frp.getC_Period_ID();
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.error("setPeriods", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		//	convert to Array
		m_periods = new FinReportPeriod[list.size()];
		list.toArray(m_periods);
		//	today after latest period
		if (m_C_Period_ID == 0)
		{
			m_reportPeriod = m_periods.length - 1;
			m_C_Period_ID = m_periods[m_reportPeriod].getC_Period_ID ();
		}
	}	//	setPeriods

	/*************************************************************************/

	/**
	 *  Perform process.
	 *  @return Message to be translated
	 *  @throws Exception
	 */
	protected String doIt()
	{
		long start = System.currentTimeMillis();

		//	** Create Report Lines
		//	- AD_PInstance_ID, PA_ReportLine_ID, 0, 0
		int PA_ReportLineSet_ID = m_report.getLineSet().getPA_ReportLineSet_ID();
		StringBuffer sql = new StringBuffer ("INSERT INTO T_Report "
			+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID, SeqNo,LevelNo, Name,Description) "
			+ "SELECT ").append(getAD_PInstance_ID()).append(", PA_ReportLine_ID, 0,0, SeqNo,0, Name,Description "
			+ "FROM PA_ReportLine "
			+ "WHERE PA_ReportLineSet_ID=").append(PA_ReportLineSet_ID);

		int no = DB.executeUpdate(sql.toString());
		log.debug("doIt - Report Lines = " + no);
		int C_AcctSchema_ID = 0;	//	unknown
		FinBalance.updateBalance (C_AcctSchema_ID, false);

		//	** Get Data	** Segment Values
		m_columns = m_report.getColumnSet().getColumns();
		m_lines = m_report.getLineSet().getLiness();
		//	for all lines
		for (int line = 0; line < m_lines.length; line++)
		{
			//	Line Segment Value (i.e. not calculation)
			if (m_lines[line].isLineTypeSegmentValue())
			{
				//	for all Columns in line with Relative Period
				lineRelativePeriod (line);
				//	Column Segment Value
				lineSegmentValue (line);
			}
		}	//	for all lines

		doCalculations();
		setDetailLines();

		//	Create Report
		getProcessInfo().Data = getPrintFormat();

		log.debug("doIt - " + (System.currentTimeMillis() - start) + " ms");
		return "";
	}	//	doIt

	/*************************************************************************/

	/**
	 * 	For all columns (in a line) with relative period access
	 * 	@param line line
	 */
	private void lineRelativePeriod (int line)
	{
		log.debug("lineRelativePeriod - " + m_lines[line]);

		//	No source lines
		if (m_lines[line] == null || m_lines[line].getSources().length == 0)
		{
			log.warn("lineRelativePeriod - no Source lines: " + m_lines[line]);
			return;
		}

		StringBuffer update = new StringBuffer();
		//	for all columns
		for (int col = 0; col < m_columns.length; col++)
		{
			//	Only relative Period (not calculation or segment value)
			if (!m_columns[col].isColumnTypeRelativePeriod ())
				continue;

			//	SELECT SUM()
			StringBuffer select = new StringBuffer ("SELECT ");
			if (m_lines[line].getAmountType() != null)				//	line amount type overwrites column
				select.append (m_lines[line].getSelectClause (true));
			else if (m_columns[col].getAmountType() != null)
				select.append (m_columns[col].getSelectClause (true));
			else
			{
				log.warn("lineRelativePeriod - no Amount Type in line: " + m_lines[line] + " or column: " + m_columns[col]);
				continue;
			}

			//	Get Period info
			select.append(" FROM Fact_Acct_Balance WHERE DateAcct ");
			FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
			if (m_lines[line].getAmountType() != null)			//	line amount type overwrites column
			{
				if (m_lines[line].isPeriodBalance())
					select.append(frp.getPeriodBalanceWhere());
				else if (m_lines[line].isYearBalance())
					select.append(frp.getYearBalanceWhere());
				else
					select.append(frp.getTotalBalanceWhere());
			}
			else if (m_columns[col].getAmountType() != null)
			{
				if (m_columns[col].isPeriodBalance())
					select.append(frp.getPeriodBalanceWhere());
				else if (m_columns[col].isYearBalance())
					select.append(frp.getYearBalanceWhere());
				else
					select.append(frp.getTotalBalanceWhere());
			}
			else
				continue;
			//
			String s = m_lines[line].getWhereClause();
			if (s != null && s.length() > 0)
				select.append(" AND ").append(s);
			s = m_report.getWhereClause();
			if (s != null && s.length() > 0)
				select.append(" AND ").append(s);
			select.append(m_parameterWhere);
		//	System.out.println("    c=" + col + ", l=" + line + ": " + select);

			//	Update SET portion
			if (update.length() > 0)
				update.append(", ");
			update.append("Col_").append(col)
				.append(" = (").append(select).append(")");
		}
		//	Update Line Values
		if (update.length() > 0)
		{
			update.insert (0, "UPDATE T_Report SET ");
			update.append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
				.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())