📄 dataengine.java
字号:
/******************************************************************************
* 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. Portions created by Jorg Janke
* are Copyright (C) 1999-2005 Jorg Janke.
* All parts are Copyright (C) 1999-2005 ComPiere, Inc. All Rights Reserved.
* Contributor(s): ______________________________________.
*****************************************************************************/
package org.compiere.print;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
import org.compiere.model.*;
import org.compiere.util.*;
/**
* Data Engine.
* Creates SQL and laods data into PrintData (including totals/etc.)
*
* @author Jorg Janke
* @version $Id: DataEngine.java,v 1.52 2006/01/09 19:44:27 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 static CLogger log = CLogger.getCLogger (DataEngine.class);
/** Synonym */
private String m_synonym = "A";
/** Default Language */
private Language m_language = Language.getLoginLanguage();
/** Break & Column Funcations */
private PrintDataGroup m_group = new PrintDataGroup();
/** Start Time */
private long m_startTime = System.currentTimeMillis();
/** Running Total after .. lines */
private int m_runningTotalLines = -1;
/** Print String */
private String m_runningTotalString = null;
/** Key Indicator in Report */
public static final String KEY = "*";
/**************************************************************************
* Load Data
*
* @param format print format
* @param query query
* @param ctx context
* @return PrintData or null
*/
public PrintData getPrintData (Properties ctx, MPrintFormat format, MQuery query)
{
if (format == null)
throw new IllegalStateException ("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, null);
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.log(Level.SEVERE, sql, e);
return null;
}
}
else
{
String sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?"; // #1
try
{
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
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.log(Level.SEVERE, sql, e1);
return null;
}
}
if (tableName == null)
{
log.log(Level.SEVERE, "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);
if (pd == null)
return null;
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 or null
*/
private PrintData getPrintDataInfo (Properties ctx, MPrintFormat format, MQuery query,
String reportName, String tableName)
{
m_startTime = System.currentTimeMillis();
log.info(reportName + " - " + m_language.getAD_Language());
log.fine("TableName=" + tableName + ", Query=" + query);
log.fine("Format=" + format);
ArrayList<PrintDataColumn> columns = new ArrayList<PrintDataColumn>();
m_group = new PrintDataGroup();
// Order Columns (identifed by non zero/null SortNo)
int[] orderAD_Column_IDs = format.getOrderAD_Column_IDs();
ArrayList<String> orderColumns = new ArrayList<String>(orderAD_Column_IDs.length);
for (int i = 0; i < orderAD_Column_IDs.length; i++)
{
log.finest("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
+ "pfi.IsMinCalc,pfi.IsMaxCalc, " // 18..19
+ "pfi.isRunningTotal,pfi.RunningTotalLines, " // 20..21
+ "pfi.IsVarianceCalc, pfi.IsDeviationCalc, " // 22..23
+ "c.ColumnSQL " // 24
+ "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, null);
pstmt.setInt(1, format.get_ID());
ResultSet rs = pstmt.executeQuery();
m_synonym = "A"; // synonym
while (rs.next())
{
// get Values from record
int AD_Column_ID = rs.getInt(1);
String ColumnName = rs.getString(2);
String ColumnSQL = rs.getString(24);
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_MEAN);
if ("Y".equals(rs.getString(14)))
m_group.addFunction(ColumnName, PrintDataFunction.F_COUNT);
if ("Y".equals(rs.getString(18))) // IsMinCalc
m_group.addFunction(ColumnName, PrintDataFunction.F_MIN);
if ("Y".equals(rs.getString(19))) // IsMaxCalc
m_group.addFunction(ColumnName, PrintDataFunction.F_MAX);
if ("Y".equals(rs.getString(22))) // IsVarianceCalc
m_group.addFunction(ColumnName, PrintDataFunction.F_VARIANCE);
if ("Y".equals(rs.getString(23))) // IsDeviationCalc
m_group.addFunction(ColumnName, PrintDataFunction.F_DEVIATION);
if ("Y".equals(rs.getString(20))) // isRunningTotal
// RunningTotalLines only once - use max
m_runningTotalLines = Math.max(m_runningTotalLines, rs.getInt(21));
// 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(m_synonym).append(display).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
sqlGROUP.append(m_synonym).append(display).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
orderName = m_synonym + display;
//
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
synonymNext();
}
// -- Table --
else if (AD_Reference_ID == DisplayType.Table
|| (AD_Reference_ID == DisplayType.Search && AD_Reference_Value_ID != 0)
)
{
TableReference tr = getTableReference(AD_Reference_Value_ID);
String display = tr.DisplayColumn;
// => A.Name AS AName, Table.ID,
if (tr.IsValueDisplayed)
sqlSELECT.append(m_synonym).append(".Value||'-'||");
sqlSELECT.append(m_synonym).append(".").append(display);
sqlSELECT.append(" AS ").append(m_synonym).append(display).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
sqlGROUP.append(m_synonym).append(".").append(display).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
orderName = m_synonym + display;
// => x JOIN table A ON (x.KeyColumn=A.Key)
if (IsMandatory)
sqlFROM.append(" INNER JOIN ");
else
sqlFROM.append(" LEFT OUTER JOIN ");
sqlFROM.append(tr.TableName).append(" ").append(m_synonym).append(" ON (")
.append(tableName).append(".").append(ColumnName).append("=")
.append(m_synonym).append(".").append(tr.KeyColumn).append(")");
//
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
synonymNext();
}
// -- 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(m_synonym).append(".Name AS ").append(m_synonym).append("Name,");
sqlGROUP.append(m_synonym).append(".Name,");
orderName = m_synonym + "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(m_synonym).append(" ON (")
.append(tableName).append(".").append(ColumnName).append("=").append(m_synonym).append(".Value")
.append(" AND ").append(m_synonym).append(".AD_Reference_ID=").append(AD_Reference_Value_ID).append(")");
}
else
{
// => A.Name AS AName,
sqlSELECT.append(m_synonym).append(".Name AS ").append(m_synonym).append("Name,");
sqlGROUP.append(m_synonym).append(".Name,");
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -