finstatement.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.util.*;
import java.sql.*;
import java.math.*;
import java.io.Serializable;

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

/**
 *  Statement of Account
 *
 *  @author Jorg Janke
 *  @version $Id: FinStatement.java,v 1.4 2003/04/01 05:54:09 jjanke Exp $
 */
public class FinStatement extends SvrProcess
{
	/**
	 *
	 */
	public FinStatement()
	{
		super();
		log.info(" ");
	}	//	FinStatement

	/** AcctSchame Parameter			*/
	private int					m_C_AcctSchema_ID = 0;
	/**	Period Parameter				*/
	private int					m_C_Period_ID = 0;
	private Timestamp			m_DateAcct_From = null;
	private Timestamp			m_DateAcct_To = null;
	/**	Org Parameter					*/
	private int					m_AD_Org_ID = 0;
	/**	Account Parameter				*/
	private int					m_Account_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;

	/**	Parameter Where Clause			*/
	private StringBuffer		m_parameterWhere = new StringBuffer();


	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		StringBuffer sb = new StringBuffer ("FinStatement 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_AcctSchema_ID"))
				m_C_AcctSchema_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("C_Period_ID"))
				m_C_Period_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("DateAcct"))
			{
				m_DateAcct_From = (Timestamp)para[i].Parameter;
				m_DateAcct_To = (Timestamp)para[i].Parameter_To;
			}
			else if (name.equals("AD_Org_ID"))
				m_AD_Org_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("Account_ID"))
				m_Account_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);
		}
		//	Mandatory C_AcctSchema_ID
		m_parameterWhere.append("C_AcctSchema_ID=").append(m_C_AcctSchema_ID);
		//	Optional Org
		if (m_AD_Org_ID != 0)
			m_parameterWhere.append(" AND AD_Org_ID=").append(m_AD_Org_ID);
		//	Optional Account
		if (m_Account_ID != 0)
			m_parameterWhere.append(" AND Account_ID=").append(m_Account_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);
		//
		setDateAcct();
		sb.append(" - DateAcct ").append(m_DateAcct_From).append("-").append(m_DateAcct_To);
		sb.append(" - ").append(m_parameterWhere);
		log.debug(sb.toString());
	}	//	prepare

	/**
	 * 	Set Start/End Date of Report - if not defined current Month
	 */
	private void setDateAcct()
	{
		//	Date defined
		if (m_DateAcct_From != null)
		{
			if (m_DateAcct_To == null)
				m_DateAcct_To = new Timestamp (System.currentTimeMillis());
			return;
		}
		//	Get Date from Period
		if (m_C_Period_ID == 0)
		{
		   GregorianCalendar cal = new GregorianCalendar(Language.getLanguage().getLocale());
		   cal.setTimeInMillis(System.currentTimeMillis());
		   cal.set(Calendar.HOUR_OF_DAY, 0);
		   cal.set(Calendar.MINUTE, 0);
		   cal.set(Calendar.SECOND, 0);
		   cal.set(Calendar.MILLISECOND, 0);
		   cal.set(Calendar.DAY_OF_MONTH, 1);		//	set to first of month
		   m_DateAcct_From = new Timestamp (cal.getTimeInMillis());
		   cal.add(Calendar.MONTH, 1);
		   cal.add(Calendar.DAY_OF_YEAR, -1);		//	last of month
		   m_DateAcct_To = new Timestamp (cal.getTimeInMillis());
		   return;
		}

		String sql = "SELECT StartDate, EndDate FROM C_Period WHERE C_Period_ID=?";
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, m_C_Period_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				m_DateAcct_From = rs.getTimestamp(1);
				m_DateAcct_To = rs.getTimestamp(2);
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.error("setDateAcct", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
	}	//	setDateAcct

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

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

		createBalanceLine();
		createDetailLines();

		int AD_PrintFormat_ID = 134;
		getProcessInfo().Data = MPrintFormat.get (AD_PrintFormat_ID, false);

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

	/**
	 * 	Create Beginning Balance Line
	 */
	private void createBalanceLine()
	{
		StringBuffer sb = new StringBuffer ("INSERT INTO T_ReportStatement "
			+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
			+ "DateAcct, Name, Description,"
			+ "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
		sb.append("SELECT ").append(getAD_PInstance_ID()).append(",0,0,")
			.append(DB.TO_DATE(m_DateAcct_From, true)).append(",")
			.append(DB.TO_STRING(Msg.getMsg(Env.getCtx(), "BeginningBalance"))).append(",NULL,"
			+ "COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(AmtAcctDr-AmtAcctCr),0), COALESCE(SUM(Qty),0) "
			+ "FROM Fact_Acct_Balance "
			+ "WHERE ").append(m_parameterWhere)
			.append(" AND DateAcct <= ").append(DB.TO_DATE(m_DateAcct_From));
		int no = DB.executeUpdate(sb.toString());
		log.debug("createBalanceLine #" + no);
	}	//	createBalanceLine

	/**
	 * 	Create Beginning Balance Line
	 */
	private void createDetailLines()
	{
		StringBuffer sb = new StringBuffer ("INSERT INTO T_ReportStatement "
			+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
			+ "DateAcct, Name, Description,"
			+ "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
		sb.append("SELECT ").append(getAD_PInstance_ID()).append(",Fact_Acct_ID,1,")
			.append("DateAcct,NULL,NULL,"
			+ "AmtAcctDr, AmtAcctCr, AmtAcctDr-AmtAcctCr, Qty "
			+ "FROM Fact_Acct "
			+ "WHERE ").append(m_parameterWhere)
			.append(" AND DateAcct BETWEEN ").append(DB.TO_DATE(m_DateAcct_From))
			.append(" AND ").append(DB.TO_DATE(m_DateAcct_To));
		int no = DB.executeUpdate(sb.toString());
		log.debug("createDetailLine #" + no);

		//	Set Name,Description
		String sql_select = "SELECT e.Name, fa.Description "
			+ "FROM Fact_Acct fa"
			+ " INNER JOIN AD_Table t ON (fa.AD_Table_ID=t.AD_Table_ID)"
			+ " INNER JOIN AD_Element e ON (t.TableName||'_ID'=e.ColumnName) "
			+ "WHERE r.Fact_Acct_ID=fa.Fact_Acct_ID";
		//	Translated Version ...
		sb = new StringBuffer ("UPDATE T_ReportStatement r SET (Name,Description)=(")
			.append(sql_select).append(") "
			+ "WHERE Fact_Acct_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID());
	   no = DB.executeUpdate(sb.toString());
	   log.debug("Trx Name #=" + no);//, sql.toString());
	}	//	createBalanceLine

}	//	FinStatement