📄 dataengine.java
字号:
orderName = m_synonym + "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(m_synonym).append(" ON (")
.append(tableName).append(".").append(ColumnName).append("=X")
.append(m_synonym).append(".Value AND X").append(m_synonym).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(m_synonym).append(" ON (X")
.append(m_synonym).append(".AD_Ref_List_ID=").append(m_synonym).append(".AD_Ref_List_ID")
.append(" AND ").append(m_synonym).append(".AD_Language='").append(m_language.getAD_Language()).append("')");
}
// TableName.ColumnName,
sqlSELECT.append(tableName).append(".").append(ColumnName).append(",");
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
synonymNext();
}
// -- Special Lookups --
else if (AD_Reference_ID == DisplayType.Location
|| AD_Reference_ID == DisplayType.Account
|| AD_Reference_ID == DisplayType.Locator
|| AD_Reference_ID == DisplayType.PAttribute
)
{
// TableName, DisplayColumn
String table = "";
String key = "";
String display = "";
String synonym = null;
//
if (AD_Reference_ID == DisplayType.Location)
{
table = "C_Location";
key = "C_Location_ID";
display = "City||'.'"; // in case City is empty
synonym = "Address";
}
else if (AD_Reference_ID == DisplayType.Account)
{
table = "C_ValidCombination";
key = "C_ValidCombination_ID";
display = "Combination";
}
else if (AD_Reference_ID == DisplayType.Locator)
{
table = "M_Locator";
key = "M_Locator_ID";
display = "Value";
}
else if (AD_Reference_ID == DisplayType.PAttribute)
{
table = "M_AttributeSetInstance";
key = "M_AttributeSetInstance_ID";
display = "Description";
if (CLogMgt.isLevelFine())
display += "||'{'||" + m_synonym + ".M_AttributeSetInstance_ID||'}'";
synonym = "Description";
}
if (synonym == null)
synonym = display;
// => A.Name AS AName, table.ID,
sqlSELECT.append(m_synonym).append(".").append(display).append(" AS ")
.append(m_synonym).append(synonym).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
sqlGROUP.append(m_synonym).append(".").append(synonym).append(",")
.append(tableName).append(".").append(ColumnName).append(",");
orderName = m_synonym + synonym;
// => 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(m_synonym).append(" ON (")
.append(tableName).append(".").append(ColumnName).append("=")
.append(m_synonym).append(".").append(key).append(")");
//
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
synonymNext();
}
// -- Standard Column --
else
{
int index = FunctionColumn.indexOf("@");
StringBuffer sb = new StringBuffer();
if (ColumnSQL != null && ColumnSQL.length() > 0)
{
// => ColumnSQL AS ColumnName
sb.append(ColumnSQL);
sqlSELECT.append(sb).append(" AS ").append(ColumnName).append(",");
if (!IsGroupFunction)
sqlGROUP.append(sb).append(",");
orderName = ColumnName; // no prefix for synonym
}
else if (index == -1)
{
// => Table.Column,
sb.append(tableName).append(".").append(ColumnName).append(",");
sqlSELECT.append(sb);
if (!IsGroupFunction)
sqlGROUP.append(sb).append(",");
}
else
{
// => Function(Table.Column) AS Column -- function has @ where column name goes
sb.append(FunctionColumn.substring(0, index))
// If I eg entered sum(amount) as function column in the report view the query would look like:
// Tablename.amountsum(amount), after removing the line below I get the wanted result. The original query column (tablename.column) is replaced by the function column entered in the report view window.
// .append(tableName).append(".").append(ColumnName) // xxxxxx
.append(FunctionColumn.substring(index+1));
sqlSELECT.append(sb).append(" AS ").append(ColumnName).append(",");
if (!IsGroupFunction)
sqlGROUP.append(sb).append(",");
orderName = ColumnName; // no prefix for synonym
}
pdc = new PrintDataColumn(AD_Column_ID, ColumnName,
AD_Reference_ID, FieldLength, ColumnName, isPageBreak);
}
// Order Sequence - Overwrite order column name
for (int i = 0; i < orderAD_Column_IDs.length; i++)
{
if (AD_Column_ID == orderAD_Column_IDs[i])
{
orderColumns.set(i, orderName);
break;
}
}
//
if (pdc == null || (!IsPrinted && !IsKey))
continue;
columns.add(pdc);
} // for all Fields in Tab
rs.close();
}
catch (SQLException e)
{
log.log(Level.SEVERE, "SQL=" + sql + " - ID=" + format.get_ID(), e);
}
if (columns.size() == 0)
{
log.log(Level.SEVERE, "No Colums - Delete Report Format " + reportName + " and start again");
log.finest("No Colums - SQL=" + sql + " - ID=" + format.get_ID());
return null;
}
boolean hasLevelNo = false;
if (tableName.startsWith("T_Report"))
{
hasLevelNo = true;
if (sqlSELECT.indexOf("LevelNo") == -1)
sqlSELECT.append("LevelNo,");
}
/**
* Assemble final SQL - delete last SELECT ','
*/
StringBuffer finalSQL = new StringBuffer();
finalSQL.append(sqlSELECT.substring(0, sqlSELECT.length()-1))
.append(sqlFROM);
// WHERE clause
if (tableName.startsWith("T_Report"))
{
finalSQL.append(" WHERE ");
for (int i = 0; i < query.getRestrictionCount(); i++)
{
String q = query.getWhereClause (i);
if (q.indexOf("AD_PInstance_ID") != -1) // ignore all other Parameters
finalSQL.append (q);
} // for all restrictions
}
else
{
// User supplied Where Clause
if (query != null && query.isActive ())
{
finalSQL.append (" WHERE ");
if (!query.getTableName ().equals (tableName))
query.setTableName (tableName);
finalSQL.append (query.getWhereClause (true));
}
// Access Restriction
MRole role = MRole.getDefault(ctx, false);
if (role.getAD_Role_ID() == 0 && !Ini.isClient())
; // System Access
else
finalSQL = new StringBuffer (role.addAccessSQL (finalSQL.toString (),
tableName, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO));
}
// Group By
if (IsGroupedBy)
finalSQL.append(sqlGROUP.substring(0, sqlGROUP.length()-1)); // last ,
// Add ORDER BY clause
if (orderColumns != null)
{
for (int i = 0; i < orderColumns.size(); i++)
{
if (i == 0)
finalSQL.append(" ORDER BY ");
else
finalSQL.append(",");
String by = (String)orderColumns.get(i);
if (by == null || by.length() == 0)
by = String.valueOf(i+1);
finalSQL.append(by);
}
} // order by
// Print Data
PrintData pd = new PrintData (ctx, reportName);
PrintDataColumn[] info = new PrintDataColumn [columns.size()];
columns.toArray(info); // column order is is m_synonymc with SELECT column position
pd.setColumnInfo(info);
pd.setTableName(tableName);
pd.setSQL(finalSQL.toString());
pd.setHasLevelNo(hasLevelNo);
log.finest (finalSQL.toString ());
log.finest ("Group=" + m_group);
return pd;
} // getPrintDataInfo
/**
* Next Synonym.
* Creates next synonym A..Z AA..ZZ AAA..ZZZ
*/
private void synonymNext()
{
int length = m_synonym.length();
char cc = m_synonym.charAt(0);
if (cc == 'Z')
{
cc = 'A';
length++;
}
else
cc++;
//
m_synonym = String.valueOf(cc);
if (length == 1)
return;
m_synonym += String.valueOf(cc);
if (length == 2)
return;
m_synonym += String.valueOf(cc);
} // synonymNext
/**
* Get TableName and ColumnName for Reference Tables.
* @param AD_Reference_Value_ID reference value
* @return 0=TableName, 1=KeyColumn, 2=DisplayColumn
*/
public static TableReference getTableReference (int AD_Reference_Value_ID)
{
TableReference tr = new TableReference();
//
String SQL = "SELECT t.TableName, ck.ColumnName AS KeyColumn," // 1..2
+ " cd.ColumnName AS DisplayColumn, rt.IsValueDisplayed, cd.IsTranslated "
+ "FROM AD_Ref_Table rt"
+ " INNER JOIN AD_Table t ON (rt.AD_Table_ID = t.AD_Table_ID)"
+ " INNER JOIN AD_Column ck ON (rt.AD_Key = ck.AD_Column_ID)"
+ " INNER JOIN AD_Column cd ON (rt.AD_Display = cd.AD_Column_ID) "
+ "WHERE rt.AD_Reference_ID=?" // 1
+ " AND rt.IsActive = 'Y' AND t.IsActive = 'Y'";
try
{
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
pstmt.setInt (1, AD_Reference_Value_ID);
ResultSet rs = pstmt.executeQuery();
if (rs.next())
{
tr.TableName = rs.getString(1);
tr.KeyColumn = rs.getString(2);
tr.DisplayColumn = rs.getString(3);
tr.IsValueDisplayed = "Y".equals(rs.getString(4));
tr.IsTranslated = "Y".equals(rs.getString(5));
}
rs.close();
pstmt.close();
}
catch (SQLException ex)
{
log.log(Level.SEVERE, SQL, ex);
}
return tr;
} // getTableReference
/**************************************************************************
* Load Data into PrintData
* @param pd print data with SQL and ColumnInfo set
* @param format print format
*/
private void loadPrintData (PrintData pd, MPrintFormat format)
{
// Translate Spool Output
boolean translateSpool = pd.getTableName().equals("T_Spool");
m_runningTotalString = Msg.getMsg(format.getLanguage(), "RunningTotal");
int rowNo = 0;
PrintDataColumn pdc = null;
boolean hasLevelNo = pd.hasLevelNo();
int levelNo = 0;
//
try
{
PreparedStatement pstmt = DB.prepareStatement(pd.getSQL(), null);
ResultSet rs = pstmt.executeQuery();
// Row Loop
while (rs.next())
{
if (hasLevelNo)
levelNo = rs.getInt("LevelNo");
else
levelNo = 0;
// Check Group Change ----------------------------------------
if (m_group.getGroupColumnCount() > 1) // one is GRANDTOTAL_
{
// Check Columns for Function Columns
for (int i = pd.getColumnInfo().length-1; i >= 0; i--) // backwards (leaset group first)
{
PrintDataColumn group_pdc = pd.getColumnInfo()[i];
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -