dataengine.java

Java写的ERP系统

				}

				//	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.error("getPrintDataInfo - SQL=" + sql, e);
		}

		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
		{
			//	Access Restriction - adds WHERE clause
			finalSQL = new StringBuffer (Access.addROAccessSQL (ctx,
				finalSQL.toString (), tableName, true));
			//	User supplied Where Clause
			if (query != null && query.isActive ())
			{
				finalSQL.append (" AND "); //	will have a where from the access
				if (!query.getTableName ().equals (tableName))
					query.setTableName (tableName);
				finalSQL.append (query.getWhereClause (true));
			} //	where clause
		}

		//  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 sync with SELECT column position
		pd.setColumnInfo(info);
		pd.setTableName(tableName);
		pd.setSQL(finalSQL.toString());
		pd.setHasLevelNo(hasLevelNo);

		if (Log.isTraceLevel(9))
		{
			log.debug (finalSQL.toString ());
			log.debug (m_group);
		}
		return pd;
	}	//	getPrintDataInfo

	/**
	 *	Get TableName and ColumnName for Reference Tables.
	 *  @param AD_Reference_Value_ID reference value
	 *	@return 0=TableName, 1=KeyColumn, 2=DisplayColumn
	 */
	public static String[] getTableReference (int AD_Reference_Value_ID)
	{
		/** @todo Translation of Table Reference */
		String retStr[] = new String[3];
		//
		String SQL = "SELECT t.TableName, ck.ColumnName AS KeyColumn,"
			+ " cd.ColumnName AS DisplayColumn, 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);
			pstmt.setInt (1, AD_Reference_Value_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				retStr[0] = rs.getString("TableName");
				retStr[1] = rs.getString("KeyColumn");
				retStr[2] = rs.getString("DisplayColumn");
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException ex)
		{
			s_log.error("getTableReference", ex);
		}
		return retStr;
	}	//  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");
		PrintDataColumn pdc = null;
		boolean hasLevelNo = pd.hasLevelNo();
		int levelNo = 0;
		//
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(pd.getSQL());
			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 TOTAL
				{
					for (int i = pd.getColumnInfo().length-1; i >= 0; i--)	//	backwards (leaset group first)
					{
						PrintDataColumn group_pdc = pd.getColumnInfo()[i];
						if (!m_group.isGroupColumn(group_pdc.getColumnName()))
							continue;
						Object value = m_group.groupChange(group_pdc.getColumnName(), rs.getObject(group_pdc.getAlias()));
						if (value != null)	//	Group change
						{
							char[] functions = m_group.getFunctions(group_pdc.getColumnName());
							for (int f = 0; f < functions.length; f++)
							{
								pd.addRow(true, levelNo);
								//	get columns
								for (int c = 0; c < pd.getColumnInfo().length; c++)
								{
									pdc = pd.getColumnInfo()[c];
									log.debug("loadPrintData - PageBreak = " + pdc.isPageBreak());

									if (group_pdc.getColumnName().equals(pdc.getColumnName()))
									{
										String valueString = value.toString();
										if (value instanceof Timestamp)
											valueString = DisplayType.getDateFormat(pdc.getDisplayType(), m_language).format(value);
										valueString	+= PrintDataFunction.getFunctionSymbol(functions[f]);
										pd.addNode(new PrintDataElement(pdc.getColumnName(),
											valueString, DisplayType.String, false, pdc.isPageBreak()));
									}
									else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
									{
										pd.addNode(new PrintDataElement(pdc.getColumnName(),
											m_group.getValue(group_pdc.getColumnName(), pdc.getColumnName(),
												functions[f], (f+1 == functions.length)),
											PrintDataFunction.getFunctionDisplayType(functions[f]), false, pdc.isPageBreak()));
									}
								}
							}	//	for all functions
						}	//	Group change
					}
				}	//	group change

				//	new row
				pd.addRow(false, levelNo);
				int counter = 1;
				//	get columns
				for (int i = 0; i < pd.getColumnInfo().length; i++)
				{
					pdc = pd.getColumnInfo()[i];
					PrintDataElement pde = null;

					//	Key Column - No DisplayColumn
					if (pdc.getAlias().equals(KEY))
					{
						if (pdc.getColumnName().endsWith("_ID"))
						{
						//	int id = rs.getInt(pdc.getColumnIDName());
							int id = rs.getInt(counter++);
							if (!rs.wasNull())
							{
								KeyNamePair pp = new KeyNamePair(id, KEY);	//	Key
								pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType(), true, pdc.isPageBreak());
							}
						}
						else
						{
						//	String id = rs.getString(pdc.getColumnIDName());
							String id = rs.getString(counter++);
							if (!rs.wasNull())
							{
								ValueNamePair pp = new ValueNamePair(id, KEY);	//	Key
								pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType(), true, pdc.isPageBreak());
							}
						}
					}
					//	Non-Key Column
					else
					{
						//	Display and Value Column
						if (pdc.hasAlias())
						{
							//	DisplayColumn first
							String display = rs.getString(counter++);
							if (pdc.getColumnName().endsWith("_ID"))
							{
								int id = rs.getInt(counter++);
								if (display != null && !rs.wasNull())
								{
									KeyNamePair pp = new KeyNamePair(id, display);
									pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType());
								}
							}
							else
							{
								String id = rs.getString(counter++);
								if (display != null && !rs.wasNull())
								{
									ValueNamePair pp = new ValueNamePair(id, display);
									pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType());
								}
							}
						}
						//	Display Value only
						else
						{
							//	Transformation for Booleans
							if (pdc.getDisplayType() == DisplayType.YesNo)
							{
								String s = rs.getString(counter++);
								if (!rs.wasNull())
								{
									boolean b = s.equals("Y");
									pde = new PrintDataElement(pdc.getColumnName(), new Boolean(b), pdc.getDisplayType());
								}
							}
							else
							//	The general case
							{
								Object obj = rs.getObject(counter++);
								if (obj != null && obj instanceof String)
								{
									obj = ((String)obj).trim();
									if (((String)obj).length() == 0)
										obj = null;
								}
								if (obj != null)
								{
									//	Translate Spool Output
									if (translateSpool  && obj instanceof String)
									{
										String s = (String)obj;
										s = Msg.parseTranslation(pd.getCtx(), s);
										pde = new PrintDataElement(pdc.getColumnName(), s, pdc.getDisplayType());
									}
									else
										pde = new PrintDataElement(pdc.getColumnName(), obj, pdc.getDisplayType());
								}
							}
						}	//	Value only
					}	//	Non-Key Column
					if (pde != null)
					{
						pd.addNode(pde);
						m_group.addValue(pde.getColumnName(), pde.getFunctionValue());
					}
				}	//	for all columns

			}	//	for all rows
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error("loadPrintData " + pdc + " - " + e.getMessage() + "\nSQL=" + pd.getSQL());
		}

		//	Check last Group Change
		if (m_group.getGroupColumnCount() > 1)	//	one is TOTAL
		{
			for (int i = pd.getColumnInfo().length-1; i >= 0; i--)	//	backwards (leaset group first)
			{
				PrintDataColumn group_pdc = pd.getColumnInfo()[i];
				if (!m_group.isGroupColumn(group_pdc.getColumnName()))
					continue;
				Object value = m_group.groupChange(group_pdc.getColumnName(), new Object());
				if (value != null)	//	Group change
				{
					char[] functions = m_group.getFunctions(group_pdc.getColumnName());
					for (int f = 0; f < functions.length; f++)
					{
						pd.addRow(true, levelNo);
						//	get columns
						for (int c = 0; c < pd.getColumnInfo().length; c++)
						{
							pdc = pd.getColumnInfo()[c];
							if (group_pdc.getColumnName().equals(pdc.getColumnName()))
							{
								String valueString = value.toString();
								if (value instanceof Timestamp)
									valueString = DisplayType.getDateFormat(pdc.getDisplayType(), m_language).format(value);
								valueString	+= PrintDataFunction.getFunctionSymbol(functions[f]);
								pd.addNode(new PrintDataElement(pdc.getColumnName(),
									valueString, DisplayType.String));
							}
							else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
							{
								pd.addNode(new PrintDataElement(pdc.getColumnName(),
									m_group.getValue(group_pdc.getColumnName(), pdc.getColumnName(),
										functions[f], (f+1 == functions.length)),
									PrintDataFunction.getFunctionDisplayType(functions[f])));
							}
						}
					}	//	for all functions
				}	//	Group change
			}
		}	//	last group change

		//	Add Total Line
		if (m_group.isGroupColumn(PrintDataGroup.TOTAL))
		{
			char[] functions = m_group.getFunctions(PrintDataGroup.TOTAL);
			for (int f = 0; f < functions.length; f++)
			{
				pd.addRow(true, levelNo);
				//	get columns
				for (int c = 0; c < pd.getColumnInfo().length; c++)
				{
					pdc = pd.getColumnInfo()[c];
					if (c == 0)		//	put Function in first Column
					{
						String name = PrintDataFunction.getFunctionSymbol(functions[f]);	//	Symbol
						if (!format.getTableFormat().isPrintFunctionSymbols())				//	Translate Sum, etc.
							name = Msg.getMsg(format.getLanguage(), PrintDataFunction.getFunctionName(functions[f]));
						pd.addNode(new PrintDataElement(pdc.getColumnName(), name, DisplayType.String));
					}
					else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
					{
						pd.addNode(new PrintDataElement(pdc.getColumnName(),
							m_group.getValue(PrintDataGroup.TOTAL, pdc.getColumnName(),
								functions[f], (f+1 == functions.length)),
							PrintDataFunction.getFunctionDisplayType(functions[f])));
					}
				}
			}	//	for all functions
		}	//	TotalLine

		if (pd.getRowCount() == 0)
			log.warn("loadPrintData - NO Rows ms=" + (System.currentTimeMillis()-m_startTime));
		else
			log.info("loadPrintData - Rows=" + pd.getRowCount()
				+ " ms=" + (System.currentTimeMillis()-m_startTime));
	}	//	loadPrintData

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

	/**
	 * 	Test
	 * 	@param args args
	 */
	public static void main(String[] args)
	{
		org.compiere.Compiere.startupClient();

	//	DataEngine de = new DataEngine(null);
		DataEngine de = new DataEngine(Language.getLanguage("de_DE"));
		MQuery query = new MQuery();
		query.addRestriction("AD_Table_ID", MQuery.LESS, 105);
	//	PrintData pd = de.load_fromTable(100, query, null, null, false);
	//	pd.dump();
	//	pd.createXML(new javax.xml.transform.stream.StreamResult(System.out));
	}
}