acctviewerdata.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  Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.acct;

import java.util.*;
import javax.swing.*;
import java.sql.*;

import org.compiere.util.*;
import org.compiere.report.core.*;
import org.compiere.model.*;


/**
 *  Account Viewer State - maintaines State information for the Account Viewer
 *
 *  @author Jorg Janke
 *  @version  $Id: AcctViewerData.java,v 1.12 2003/01/27 06:14:49 jjanke Exp $
 */
class AcctViewerData
{
	/**
	 *  Constructor
	 *
	 *  @param windowNo window no
	 *  @param ad_Client_ID client
	 */
	public AcctViewerData (int windowNo, int ad_Client_ID)
	{
		WindowNo = windowNo;
		AD_Client_ID = ad_Client_ID;
		if (AD_Client_ID == 0)
			AD_Client_ID = Env.getContextAsInt(Env.getCtx(), WindowNo, "AD_Client_ID");
		if (AD_Client_ID == 0)
			AD_Client_ID = Env.getContextAsInt(Env.getCtx(), "AD_Client_ID");
		//
		ASchemas = AcctSchema.getAcctSchemaArray(AD_Client_ID);
		AS = ASchemas[0];
	}   //  AcctViewerData

	/** Window              */
	public int              WindowNo;
	public int              AD_Client_ID;

	public AcctSchema[]     ASchemas = null;
	public AcctSchema       AS = null;

	//  Selection Info
	public boolean          documentQuery = false;
	public int              C_AcctSchema_ID = 0;
	public int              AD_Org_ID = 0;
	public Timestamp        DateFrom = null;
	public Timestamp        DateTo = null;

	//  Table Selection Info
	public int              AD_Table_ID;
	public int              Record_ID;

	/** Containing Column and Query     */
	public HashMap          whereInfo = new HashMap();
	/** Containing TableName and AD_Table_ID    */
	public HashMap          tableInfo = new HashMap();

	//  Display Info
	public boolean          displayQty = false;
	public boolean          displaySourceAmt = false;
	public boolean          displayDocumentInfo = false;
	//
	public String           sortBy1 = "";
	public String           sortBy2 = "";
	public String           sortBy3 = "";
	public String           sortBy4 = "";
	//
	public boolean          group1 = false;
	public boolean          group2 = false;
	public boolean          group3 = false;
	public boolean          group4 = false;


	/**
	 *  Dispose
	 */
	public void dispose()
	{
		ASchemas = null;
		AS = null;
		//
		whereInfo.clear();
		whereInfo = null;
		//
		Env.clearWinContext(WindowNo);
	}   //  dispose

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

	/**
	 *  Fill Accounting Schema
	 *  @param cb JComboBox to be filled
	 */
	protected void fillAcctSchema (JComboBox cb)
	{
		KeyNamePair pp = new KeyNamePair(0, "");
		cb.addItem(pp);
		for (int i = 0; i < ASchemas.length; i++)
			cb.addItem(new KeyNamePair(ASchemas[i].getC_AcctSchema_ID(),
				ASchemas[i].getName()));
	}   //  fillAcctSchema

	/**
	 *  Fill Table with
	 *      ValueNamePair (TableName, translatedKeyColumnName)
	 *  and tableInfo with (TableName, AD_Table_ID)
	 *  and select the entry for AD_Table_ID
	 *
	 *  @param cb JComboBox to be filled
	 */
	protected void fillTable (JComboBox cb)
	{
		ValueNamePair select = null;
		//
		String sql = "SELECT AD_Table_ID, TableName FROM AD_Table t "
			+ "WHERE EXISTS (SELECT * FROM AD_Column c"
			+ " WHERE t.AD_Table_ID=c.AD_Table_ID AND c.ColumnName='Posted')"
			+ " AND IsView='N'";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt(1);
				String tableName = rs.getString(2);
				String name = Msg.translate(Env.getCtx(), tableName+"_ID");
				//
				ValueNamePair pp = new ValueNamePair(tableName, name);
				cb.addItem(pp);
				tableInfo.put (tableName, new Integer(id));
				if (id == AD_Table_ID)
					select = pp;
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error("AcctViewerData.fillTable", e);
		}
		if (select != null)
			cb.setSelectedItem(select);
	}   //  fillTable

	/**
	 *  Fill Org
	 *
	 *  @param cb JComboBox to be filled
	 */
	protected void fillOrg (JComboBox cb)
	{
		KeyNamePair pp = new KeyNamePair(0, "");
		cb.addItem(pp);
		String sql = "SELECT AD_Org_ID, Name FROM AD_Org WHERE AD_Client_ID=? ORDER BY Value";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
				cb.addItem(new KeyNamePair(rs.getInt(1), rs.getString(2)));
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error("AcctViewerData.fillOrg", e);
		}
	}   //  fillOrg

	/**
	 *  Get Button Text
	 *
	 *  @param tableName table
	 *  @param columnName column
	 *  @param selectSQL sql
	 *  @return Text on button
	 */
	protected String getButtonText (String tableName, String columnName, String selectSQL)
	{
		//  SELECT (<embedded>) FROM tableName avd WHERE avd.<selectSQL>
		StringBuffer sql = new StringBuffer ("SELECT (");
		Language language = Env.getLanguage(Env.getCtx());
		sql.append(MLookupFactory.getLookup_TableDirEmbed(language, columnName, "avd"))
			.append(") FROM ").append(tableName).append(" avd WHERE avd.").append(selectSQL);
		String retValue = "<" + selectSQL + ">";
		try
		{
			Statement stmt = DB.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			if (rs.next())
				retValue = rs.getString(1);
			rs.close();
			stmt.close();
		}
		catch (SQLException e)
		{
			Log.error("AcctViewerData.actionButton", e);
		}
		return retValue;
	}   //  getButtonText

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

	/**
	 *  Create Query and submit
	 *  @return Report Model
	 */
	protected RModel query()
	{
		//  Set Where Clause
		StringBuffer whereClause = new StringBuffer();
		if (documentQuery)
			whereClause.append(RModel.TABLE_ALIAS).append(".AD_Table_ID=").append(AD_Table_ID)
				.append(" AND ").append(RModel.TABLE_ALIAS).append(".Record_ID=").append(Record_ID);
		else
		{
			//  get values (Queries)
			Iterator it = whereInfo.values().iterator();
			while (it.hasNext())
			{
				String where = (String)it.next();
				if (where != null && where.length() > 0)    //  add only if not empty
				{
					if (whereClause.length() > 0)
						whereClause.append(" AND ");
					whereClause.append(RModel.TABLE_ALIAS).append(".").append(where);
				}
			}
			if (DateFrom != null || DateTo != null)
			{
				if (whereClause.length() > 0)
					whereClause.append(" AND ");
				if (DateFrom != null && DateTo != null)
					whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) BETWEEN ")
						.append(DB.TO_DATE(DateFrom)).append(" AND ").append(DB.TO_DATE(DateTo));
				else if (DateFrom != null)
					whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) >= ")
						.append(DB.TO_DATE(DateFrom));
				else    //  DateTo != null
					whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) <= ")
						.append(DB.TO_DATE(DateTo));
			}
			//  Add Organization
			if (AD_Org_ID != 0)
			{
				if (whereClause.length() > 0)
					whereClause.append(" AND ");
				whereClause.append(RModel.TABLE_ALIAS).append(".AD_Org_ID=").append(AD_Org_ID);
			}
		}

		//  Set Order By Clause
		StringBuffer orderClause = new StringBuffer();
		if (sortBy1.length() > 0)
			orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy1);
		if (sortBy2.length() > 0)
		{
			if (orderClause.length() > 0)
				orderClause.append(",");
			orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy2);
		}
		if (sortBy3.length() > 0)
		{
			if (orderClause.length() > 0)
				orderClause.append(",");
			orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy3);
		}
		if (sortBy4.length() > 0)
		{
			if (orderClause.length() > 0)
				orderClause.append(",");
			orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy4);
		}
		if (orderClause.length() == 0)
			orderClause.append(RModel.TABLE_ALIAS).append(".Fact_Acct_ID");

		RModel rm = getRModel();

		//  Groups
		if (group1 && sortBy1.length() > 0)
			rm.setGroup(sortBy1);
		if (group2 && sortBy2.length() > 0)
			rm.setGroup(sortBy2);
		if (group3 && sortBy3.length() > 0)
			rm.setGroup(sortBy3);
		if (group4 && sortBy4.length() > 0)
			rm.setGroup(sortBy4);

		//  Totals
		rm.setFunction("AmtAcctDR", RModel.FUNCTION_SUM);
		rm.setFunction("AmtAcctCR", RModel.FUNCTION_SUM);

		rm.query (Env.getCtx(), whereClause.toString(), orderClause.toString());

		return rm;
	}   //  query

	/**
	 *  Create Report Model (Columns)
	 *  @return Report Model
	 */
	private RModel getRModel()
	{
		Properties ctx = Env.getCtx();
		RModel rm = new RModel("Fact_Acct");
		//  Add Key
		ArrayList keys = createKeyColumns();
		for (int i = 0; i < keys.size(); i++)
		{
			String column = (String)keys.get(i);
			if (column != null && column.startsWith("Date"))
				rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
			else if (column != null && column.endsWith("_ID"))
				rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
		}
		//  Info
		if (!keys.contains("DateAcct"))
			rm.addColumn(new RColumn(ctx, "DateAcct", DisplayType.Date));
		if (!keys.contains("C_Period_ID"))
			rm.addColumn(new RColumn(ctx, "C_Period_ID", DisplayType.TableDir));
		rm.addColumn(new RColumn(ctx, "AmtAcctDR", DisplayType.Amount));
		rm.addColumn(new RColumn(ctx, "AmtAcctCR", DisplayType.Amount));
		if (displaySourceAmt)
		{
			if (!keys.contains("DateTrx"))
				rm.addColumn(new RColumn(ctx, "DateTrx", DisplayType.Date));
			rm.addColumn(new RColumn(ctx, "C_Currency_ID", DisplayType.TableDir));
			rm.addColumn(new RColumn(ctx, "AmtSourceDR", DisplayType.Amount));
			rm.addColumn(new RColumn(ctx, "AmtSourceCR", DisplayType.Amount));
		}
		if (displayQty)
		{
			rm.addColumn(new RColumn(ctx, "C_UOM_ID", DisplayType.TableDir));
			rm.addColumn(new RColumn(ctx, "Qty", DisplayType.Quantity));
		}
		if (displayDocumentInfo)
		{
			rm.addColumn(new RColumn(ctx, "AD_Table_ID", DisplayType.TableDir));
		}
		return rm;
	}   //  createRModel

	/**
	 *  Create the key columns in sequence
	 *  @return List of Key Columns
	 */
	private ArrayList createKeyColumns()
	{
		ArrayList columns = new ArrayList();
		//  Sorting Fields
		columns.add(sortBy1);               //  may add ""
		if (!columns.contains(sortBy2))
			columns.add(sortBy2);
		if (!columns.contains(sortBy3))
			columns.add(sortBy3);
		if (!columns.contains(sortBy4))
			columns.add(sortBy4);

		//  Add Account Segments
		ArrayList elements = AS.getAcctSchemaElementList();
		for (int i = 0; i < elements.size(); i++)
		{
			AcctSchemaElement ase = (AcctSchemaElement)elements.get(i);
			String columnName = ase.getColumnName();
			if (!columns.contains(columnName))
				columns.add(columnName);
		}
		//
		return columns;
	}   //  createKeyColumns

}   //  AcctViewerData