dataengine.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-2002 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.print;

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

import org.apache.log4j.Logger;

import org.compiere.util.Language;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.KeyNamePair;
import org.compiere.util.ValueNamePair;
import org.compiere.util.Msg;
import org.compiere.util.Env;
import org.compiere.util.Access;
import org.compiere.util.Log;

import org.compiere.model.*;

/**
 *	Data Engine.
 * 	Creates SQL and laods data into PrintData (including totals/etc.)
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: DataEngine.java,v 1.24 2003/04/30 06:25:48 jjanke Exp $
 */
public class DataEngine
{
	/**
	 *	Constructor
	 *	@param language Language of the data (for translation)
	 */
	public DataEngine (Language language)
	{
		if (language != null)
			m_language = language;
	}	//	DataEngine

	/**	Logger							*/
	private Logger			log = Logger.getLogger (getClass());
	private static Logger	s_log = Logger.getLogger (DataEngine.class);

	/**	Default Language			*/
	private Language		m_language = Language.getLanguage();
	/** Break & Column Funcations	*/
	private PrintDataGroup 	m_group = new PrintDataGroup();
	/**	Start Time					*/
	private long			m_startTime = System.currentTimeMillis();

	public static final String KEY = "*";	//	Key Indicator in Report


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

	/**
	 * 	Load Data
	 *
	 * 	@param format print format
	 * 	@param query query
	 * 	@parameter ctx context
	 * 	@return PrintData
	 */
	public PrintData getPrintData (Properties ctx, MPrintFormat format, MQuery query)
	{
		if (format == null)
			throw new IllegalStateException ("DataEngine.getPrintData - no print format");
		String tableName = null;
		String reportName = format.getName();
		//
		if (format.getAD_ReportView_ID() != 0)
		{
			String sql = "SELECT t.AD_Table_ID, t.TableName, rv.Name "
				+ "FROM AD_Table t"
				+ " INNER JOIN AD_ReportView rv ON (t.AD_Table_ID=rv.AD_Table_ID) "
				+ "WHERE rv.AD_ReportView_ID=?";	//	1
			try
			{
				PreparedStatement pstmt = DB.prepareStatement(sql);
				pstmt.setInt(1, format.getAD_ReportView_ID());
				ResultSet rs = pstmt.executeQuery();
				if (rs.next())
				{
					tableName = rs.getString(2);  	//	TableName
					reportName = rs.getString(3);
				}
				rs.close();
				pstmt.close();
			}
			catch (SQLException e)
			{
				log.error("getPrintData - ReportView", e);
				return null;
			}
		}
		else
		{
			String sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?";	//	#1
			try
			{
				PreparedStatement pstmt = DB.prepareStatement(sql.toString());
				pstmt.setInt(1, format.getAD_Table_ID());
				ResultSet rs = pstmt.executeQuery();
				if (rs.next())
					tableName = rs.getString(1);		//  TableName
				rs.close();
				pstmt.close();
			}
			catch (SQLException e1)
			{
				log.error("getPrintData - Table", e1);
				return null;
			}
		}
		if (tableName == null)
		{
			log.error("getPrintData - Not found Format=" + format);
			return null;
		}
		if (format.isTranslationView() && tableName.toLowerCase().endsWith("_v"))	//	_vt not just _v
			tableName += "t";
		format.setTranslationViewQuery (query);
		//
		PrintData pd = getPrintDataInfo (ctx, format, query, reportName, tableName);
		loadPrintData(pd, format);
		return pd;
	}	//	getPrintData

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

	/**
	 * 	Create Load SQL and update PrintData Info
	 *
	 * 	@param ctx context
	 * 	@param format print format
	 * 	@param query query
	 *  @param reportName report name
	 *  @param tableName table name
	 * 	@return PrintData
	 */
	private PrintData getPrintDataInfo (Properties ctx, MPrintFormat format, MQuery query,
		String reportName, String tableName)
	{
		m_startTime = System.currentTimeMillis();
		log.info("getPrintDataInfo - " + reportName + " - " + m_language.getAD_Language());
		log.debug("TableName=" + tableName + ", Query=" + query + ", Format=" + format);
		ArrayList columns = new ArrayList();
		m_group = new PrintDataGroup();

		//	Order Columns (identifed by non zero/null SortNo)
		int[] orderAD_Column_IDs = format.getOrderAD_Column_IDs();
		ArrayList orderColumns = new ArrayList(orderAD_Column_IDs.length);
		for (int i = 0; i < orderAD_Column_IDs.length; i++)
		{
			if (Log.isTraceLevel(9))
				log.debug("Order AD_Column_ID=" + orderAD_Column_IDs[i]);
			orderColumns.add("");		//	initial value overwritten with fully qualified name
		}

		//	Direct SQL w/o Reference Info
		StringBuffer sqlSELECT = new StringBuffer("SELECT ");
		StringBuffer sqlFROM = new StringBuffer(" FROM ");
		sqlFROM.append(tableName);
		StringBuffer sqlGROUP = new StringBuffer(" GROUP BY ");
		//
		boolean IsGroupedBy = false;
		//
		String sql = "SELECT c.AD_Column_ID,c.ColumnName,"				//	1..2
			+ "c.AD_Reference_ID,c.AD_Reference_Value_ID,"				//	3..4
			+ "c.FieldLength,c.IsMandatory,c.IsKey,c.IsParent,"			//	5..8
			+ "COALESCE(rvc.IsGroupFunction,'N'),rvc.FunctionColumn,"	//	9..10
			+ "pfi.IsGroupBy,pfi.IsSummarized,pfi.IsAveraged,pfi.IsCounted, "	//	11..14
			+ "pfi.IsPrinted,pfi.SortNo,pfi.IsPageBreak "						//	15..17
			+ "FROM AD_PrintFormat pf"
			+ " INNER JOIN AD_PrintFormatItem pfi ON (pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID)"
			+ " INNER JOIN AD_Column c ON (pfi.AD_Column_ID=c.AD_Column_ID)"
			+ " LEFT OUTER JOIN AD_ReportView_Col rvc ON (pf.AD_ReportView_ID=rvc.AD_ReportView_ID AND c.AD_Column_ID=rvc.AD_Column_ID) "
			+ "WHERE pf.AD_PrintFormat_ID=?"					//	#1
			+ " AND pfi.IsActive='Y' AND (pfi.IsPrinted='Y' OR c.IsKey='Y' OR pfi.SortNo > 0) "
			+ "ORDER BY pfi.IsPrinted DESC, pfi.SeqNo";			//	Functions are put in first column
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, format.getID());
			ResultSet rs = pstmt.executeQuery();

			char syn = 'A';		//	synonym
			while (rs.next())
			{
				//	get Values from record
				int AD_Column_ID = rs.getInt(1);
				String ColumnName = rs.getString(2);
				int AD_Reference_ID = rs.getInt(3);
				int AD_Reference_Value_ID = rs.getInt(4);
				//  ColumnInfo
				int FieldLength = rs.getInt(5);
				boolean IsMandatory = "Y".equals(rs.getString(6));
				boolean IsKey = "Y".equals(rs.getString(7));
				boolean IsParent = "Y".equals(rs.getString(8));
				//  SQL GroupBy
				boolean IsGroupFunction = "Y".equals(rs.getString(9));
				if (IsGroupFunction)
					IsGroupedBy = true;
				String FunctionColumn = rs.getString(10);
				if (FunctionColumn == null)
					FunctionColumn = "";
				//	Breaks/Column Functions
				if ("Y".equals(rs.getString(11)))
					m_group.addGroupColumn(ColumnName);
				if ("Y".equals(rs.getString(12)))
					m_group.addFunction(ColumnName, PrintDataFunction.F_SUM);
				if ("Y".equals(rs.getString(13)))
					m_group.addFunction(ColumnName, PrintDataFunction.F_AVERAGE);
				if ("Y".equals(rs.getString(14)))
					m_group.addFunction(ColumnName, PrintDataFunction.F_COUNT);
				//	General Info
				boolean IsPrinted = "Y".equals(rs.getString(15));
				int SortNo = rs.getInt(16);
				boolean isPageBreak = "Y".equals(rs.getString(17));

				//	Fully qualified Table.Column for ordering
				String orderName = tableName + "." + ColumnName;
				PrintDataColumn pdc = null;

				//  -- Key --
				if (IsKey)
				{
					//	=>	Table.Column,
					sqlSELECT.append(tableName).append(".").append(ColumnName).append(",");
					sqlGROUP.append(tableName).append(".").append(ColumnName).append(",");
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, KEY, isPageBreak);	//	KeyColumn
				}
				else if (!IsPrinted)	//	not printed Sort Columns
					;
				//	-- Parent, TableDir (and unqualified Search) --
				else if (IsParent || AD_Reference_ID == DisplayType.TableDir
						|| (AD_Reference_ID == DisplayType.Search && AD_Reference_Value_ID == 0)
					)
				{
					//  Creates Embedded SQL in the form
					//  SELECT ColumnTable.Name FROM ColumnTable WHERE TableName.ColumnName=ColumnTable.ColumnName
					String eSql = MLookupFactory.getLookup_TableDirEmbed(m_language, ColumnName, tableName);

					//	TableName
					String table = ColumnName;
					if (table.endsWith("_ID"))
						table = table.substring(0, table.length()-3);
					//  DisplayColumn
					String display = ColumnName;
					//	=> (..) AS AName, Table.ID,
					sqlSELECT.append("(").append(eSql).append(") AS ").append(syn).append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					sqlGROUP.append(syn).append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					orderName = syn + display;
					//
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
					syn++;
				}

				//	-- Table --
				else if (AD_Reference_ID == DisplayType.Table
						|| (AD_Reference_ID == DisplayType.Search && AD_Reference_Value_ID != 0)
					)
				{
					String rInfo[] = getTableReference(AD_Reference_Value_ID);
					//	TableName
					String table = rInfo[0];
					//	DisplayColumn
					String display = rInfo[2];
					//	=> A.Name AS AName, Table.ID,
					sqlSELECT.append(syn).append(".").append(display).append(" AS ").append(syn).append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					sqlGROUP.append(syn).append(".").append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					orderName = syn + display;

					//	=> x JOIN table A ON (x.KeyColumn=A.Key)
					if (IsMandatory)
						sqlFROM.append(" INNER JOIN ");
					else
						sqlFROM.append(" LEFT OUTER JOIN ");
					sqlFROM.append(table).append(" ").append(syn).append(" ON (")
						.append(tableName).append(".").append(ColumnName).append("=")
						.append(syn).append(".").append(rInfo[1]).append(")");
					//
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
					syn++;
				}

				//	-- List or Button with ReferenceValue --
				else if (AD_Reference_ID == DisplayType.List || (AD_Reference_ID == DisplayType.Button && AD_Reference_Value_ID != 0))
				{
					if (Env.isBaseLanguage(m_language, "AD_Ref_List"))
					{
						//	=> A.Name AS AName,
						sqlSELECT.append(syn).append(".Name AS ").append(syn).append("Name,");
						sqlGROUP.append(syn).append(".Name,");
						orderName = syn + "Name";
						//	=> x JOIN AD_Ref_List A ON (x.KeyColumn=A.Value AND A.AD_Reference_ID=123)
						if (IsMandatory)
							sqlFROM.append(" INNER JOIN ");
						else
							sqlFROM.append(" LEFT OUTER JOIN ");
						sqlFROM.append(" AD_Ref_List ").append(syn).append(" ON (")
							.append(tableName).append(".").append(ColumnName).append("=").append(syn).append(".Value")
							.append(" AND ").append(syn).append(".AD_Reference_ID=").append(AD_Reference_Value_ID).append(")");
					}
					else
					{
						//	=> A.Name AS AName,
						sqlSELECT.append(syn).append(".Name AS ").append(syn).append("Name,");
						sqlGROUP.append(syn).append(".Name,");
						orderName = syn + "Name";

						//	LEFT OUTER JOIN AD_Ref_List XA ON (AD_Table.EntityType=XA.Value AND XA.AD_Reference_ID=245)
						//	LEFT OUTER JOIN AD_Ref_List_Trl A ON (XA.AD_Ref_List_ID=A.AD_Ref_List_ID AND A.AD_Language='de_DE')
						if (IsMandatory)
							sqlFROM.append(" INNER JOIN ");
						else
							sqlFROM.append(" LEFT OUTER JOIN ");
						sqlFROM.append(" AD_Ref_List X").append(syn).append(" ON (")
							.append(tableName).append(".").append(ColumnName).append("=X")
							.append(syn).append(".Value AND X").append(syn).append(".AD_Reference_ID=").append(AD_Reference_Value_ID)
							.append(")");
						if (IsMandatory)
							sqlFROM.append(" INNER JOIN ");
						else
							sqlFROM.append(" LEFT OUTER JOIN ");
						sqlFROM.append(" AD_Ref_List_Trl ").append(syn).append(" ON (X")
							.append(syn).append(".AD_Ref_List_ID=").append(syn).append(".AD_Ref_List_ID")
							.append(" AND ").append(syn).append(".AD_Language='").append(m_language.getAD_Language()).append("')");
					}
					// 	ColumnName,
					sqlSELECT.append(ColumnName).append(",");
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
					syn++;
				}

				//  -- Special Lookups --
				else if (AD_Reference_ID == DisplayType.Location
					|| AD_Reference_ID == DisplayType.Account
					|| AD_Reference_ID == DisplayType.Locator)
				{
					//	TableName, DisplayColumn
					String table = "", key = "", display = "";
					//
					if (AD_Reference_ID == DisplayType.Location)
					{
						table = "C_Location";
						key = "C_Location_ID";
						display = "City";
					}
					else if (AD_Reference_ID == DisplayType.Account)
					{
						table = "C_ValidCompination";
						key = "C_ValidCombination_ID";
						display = "Combination";
					}
					else if (AD_Reference_ID == DisplayType.Locator)
					{
						table = "M_Locator";
						key = "M_Locator_ID";
						display = "Value";
					}

					//	=> A.Name AS AName, table.ID,
					sqlSELECT.append(syn).append(".").append(display).append(" AS ")
						.append(syn).append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					sqlGROUP.append(syn).append(".").append(display).append(",")
						.append(tableName).append(".").append(ColumnName).append(",");
					orderName = syn + display;
					//	=> x JOIN table A ON (table.ID=A.Key)
					if (IsMandatory)
						sqlFROM.append(" INNER JOIN ");
					else
						sqlFROM.append(" LEFT OUTER JOIN ");
					sqlFROM.append(table).append(" ").append(syn).append(" ON (")
						.append(tableName).append(".").append(ColumnName).append("=")
						.append(syn).append(".").append(key).append(")");
					//
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
					syn++;
				}

				//	-- Standard Column --
				else
				{
					int index = FunctionColumn.indexOf("@");
					StringBuffer sb = new StringBuffer();
					if (index == -1)
					{
					//	=> Table.Column,
						sb.append(tableName).append(".").append(ColumnName).append(",");
						sqlSELECT.append(sb.toString());
						if (!IsGroupFunction)
							sqlGROUP.append(sb.toString());
					}
					else
					{
					//  => Function(Table.Column) AS Column   -- function has @ where column name goes
						sb.append(FunctionColumn.substring(0, index))
							.append(tableName).append(".").append(ColumnName)
							.append(FunctionColumn.substring(index+1));
						sqlSELECT.append(sb.toString()).append(" AS ").append(ColumnName).append(",");
						if (!IsGroupFunction)
							sqlGROUP.append(sb.toString()).append(",");
					}
					pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, ColumnName, isPageBreak);