mlookupfactory.java

Java写的ERP系统

			//	We have no context
			if (where.length() != 0)
			{
				realSQL.append(" WHERE ").append(where);
				if (where.indexOf(".") == -1)
					s_log.error("getLookup_Table - " + TableName
						+ ": WHERE should be fully qualified: " + WhereClause);
			}
		}

		//	Order By qualified term or by Name
		if (OrderByClause != null)
		{
			realSQL.append(" ORDER BY ").append(OrderByClause);
			if (OrderByClause.indexOf(".") == -1)
				s_log.error("getLookup_Table - " + TableName
					+ ": ORDER BY must fully qualified: " + OrderByClause);
		}
		else
			realSQL.append(" ORDER BY 2");

		return new MLookupInfo(Access.addROAccessSQL(ctx, realSQL.toString(), TableName, true),
			TableName + "." + KeyColumn, ZoomWindow, null);	/** @todo parse WhereClause to query */
	}	//	getLookup_Table

	/**
	 *	Get Embedded Lookup SQL for Table Lookup
	 *  @param language report language
	 * 	@param BaseColumn base column name
	 * 	@param BaseTable base table name
	 *  @param AD_Reference_Value_ID reference value
	 *	@return	SELECT Name FROM Table
	 */
	static public String getLookup_TableEmbed (Language language,
		String BaseColumn, String BaseTable, int AD_Reference_Value_ID)
	{
		String sql = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"
			+ "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=?"
			+ " AND rt.IsActive='Y' AND t.IsActive='Y'";
		//
		String	KeyColumn, DisplayColumn, TableName;
		boolean IsTranslated, isValueDisplayed;

		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Reference_Value_ID);
			ResultSet rs = pstmt.executeQuery();
			if (!rs.next())
			{
				s_log.error("getLookup_TableEmbed - Cannot find Reference Table, ID=" + AD_Reference_Value_ID
					+ ", Base=" + BaseTable + "." + BaseColumn);
				rs.close();
				pstmt.close();
				return null;
			}

			TableName = rs.getString(1);
			KeyColumn = rs.getString(2);
			DisplayColumn = rs.getString(3);
			isValueDisplayed = rs.getString(4).equals("Y");
			IsTranslated = rs.getString(5).equals("Y");

			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("getLookup_TableEmbed", e);
			return null;
		}

		StringBuffer embedSQL = new StringBuffer("SELECT ");

		//	Translated
		if (IsTranslated && !Env.isBaseLanguage(language, TableName))
		{
			if (isValueDisplayed)
				embedSQL.append(TableName).append(".Value||'-'||");
			embedSQL.append(TableName).append("_Trl.").append(DisplayColumn);
			//
			embedSQL.append(" FROM ").append(TableName)
				.append(" INNER JOIN ").append(TableName).append("_TRL ON (")
				.append(TableName).append(".").append(KeyColumn)
				.append("=").append(TableName).append("_Trl.").append(KeyColumn)
				.append(" AND ").append(TableName).append("_Trl.AD_Language='")
				.append(language.getAD_Language()).append("')");
		}
		//	Not Translated
		else
		{
			if (isValueDisplayed)
				embedSQL.append(TableName).append(".Value||'-'||");
			embedSQL.append(TableName).append(".").append(DisplayColumn);
			//
			embedSQL.append(" FROM ").append(TableName);
		}

		embedSQL.append(" WHERE ").append(BaseTable).append(".").append(BaseColumn);
		embedSQL.append("=").append(TableName).append(".").append(KeyColumn);

		return embedSQL.toString();
	}	//	getLookup_TableEmbed


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

	/**
	 *	Get Lookup SQL for direct Table Lookup
	 *  @param ctx context for access
	 *  @param language report language
	 *  @param ColumnName column name
	 *	@return SELECT Key, NULL, Name, IsActive from Table (fully qualified)
	 */
	static public MLookupInfo getLookup_TableDir (Properties ctx, Language language, String ColumnName)
	{
		if (!ColumnName.endsWith("_ID"))
		{
			s_log.error("getLookup_TableDir - Key does not end with '_ID': " + ColumnName);
			return null;
		}

		String TableName = ColumnName.substring(0,ColumnName.length()-3);
		int ZoomWindow = 0;

		//	get display column names
		String SQL = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID,t.AD_Window_ID "
			+ "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
			+ "WHERE TableName=?"
			+ " AND c.IsIdentifier='Y' "
			+ "ORDER BY c.SeqNo";
		//
		String KeyColumn = ColumnName;
		//
		ArrayList list = new ArrayList();
		boolean isTranslated = false;
		//
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(SQL);
			pstmt.setString(1, TableName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				LookupDisplayColumn ldc = new LookupDisplayColumn (rs.getString(1),
					"Y".equals(rs.getString(2)), rs.getInt(3), rs.getInt(4));
				list.add (ldc);
				s_log.debug("getLookup_TableDir: " + ColumnName + " - " + ldc);
				//
				if (!isTranslated && ldc.IsTranslated)
					isTranslated = true;
				ZoomWindow = rs.getInt(5);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("getLookup_TableDir", e);
			return null;
		}
		//  Do we have columns ?
		if (list.size() == 0)
		{
			s_log.error("getLookup_TableDir - No Identifier records found: " + ColumnName);
			return null;
		}

		StringBuffer realSQL = new StringBuffer("SELECT ");
		realSQL.append(TableName).append(".").append(KeyColumn).append(",NULL,");

		StringBuffer displayColumn = new StringBuffer();
		int size = list.size();
		//  Get Display Column
		for (int i = 0; i < size; i++)
		{
			if (i > 0)
				displayColumn.append(" ||'_'|| " );
			LookupDisplayColumn ldc = (LookupDisplayColumn)list.get(i);

			//  translated
			if (ldc.IsTranslated && !Env.isBaseLanguage(language, TableName))
				displayColumn.append(TableName).append("_Trl.").append(ldc.ColumnName);
			//  date
			else if (DisplayType.isDate(ldc.DisplayType))
			{
				displayColumn.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
			}
			//  TableDir
			else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
				&& ldc.ColumnName.endsWith("_ID"))
			{
				String embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName);
				if (embeddedSQL != null)
					displayColumn.append("(").append(embeddedSQL).append(")");
			}
			//	Table
			else if (ldc.DisplayType == DisplayType.Table && ldc.AD_Reference_ID != 0)
			{
				String embeddedSQL = getLookup_TableEmbed (language, ldc.ColumnName, TableName, ldc.AD_Reference_ID);
				if (embeddedSQL != null)
					displayColumn.append("(").append(embeddedSQL).append(")");
			}
			//  number
			else if (DisplayType.isNumeric(ldc.DisplayType))
			{
				displayColumn.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
			}
			//  String
			else
				displayColumn.append(TableName).append(".").append(ldc.ColumnName);
		}
		realSQL.append(displayColumn.toString());
		realSQL.append(",").append(TableName).append(".IsActive");

		//  Translation
		if (isTranslated && !Env.isBaseLanguage(language, TableName))
		{
			realSQL.append(" FROM ").append(TableName)
				.append(" INNER JOIN ").append(TableName).append("_TRL ON (")
				.append(TableName).append(".").append(KeyColumn)
				.append("=").append(TableName).append("_Trl.").append(KeyColumn)
				.append(" AND ").append(TableName).append("_Trl.AD_Language='")
				.append(language.getAD_Language()).append("')");
		}
		else	//	no translation
		{
			realSQL.append(" FROM ").append(TableName);
		}

		//	Order by Display
		realSQL.append(" ORDER BY 3");

		s_log.debug("getLookup_TableDir: " + ColumnName + " - " + realSQL);
		return new MLookupInfo(Access.addROAccessSQL(ctx, realSQL.toString(), TableName, true),
			TableName + "." + KeyColumn, ZoomWindow, null);
	}	//	getLookup_TableDir


	/**
	 *  Get embedded SQL for TableDir Lookup (no translation)
	 *
	 *  @param language report language
	 *  @param ColumnName column name
	 *  @param BaseTable base table
	 *  @return SELECT Column FROM TableName WHERE BaseTable.ColumnName=TableName.ColumnName
	 */
	static public String getLookup_TableDirEmbed (Language language, String ColumnName, String BaseTable)
	{
		return getLookup_TableDirEmbed (language, ColumnName, BaseTable, ColumnName);
	}   //  getLookup_TableDirEmbed

	/**
	 *  Get embedded SQL for TableDir Lookup (no translation)
	 *
	 *  @param language report language
	 *  @param ColumnName column name
	 *  @param BaseTable base table
	 *  @param BaseColumn base column
	 *  @return SELECT Column FROM TableName WHERE BaseTable.BaseColumn=TableName.ColumnName
	 */
	static public String getLookup_TableDirEmbed (Language language,
		String ColumnName, String BaseTable, String BaseColumn)
	{
		String TableName = ColumnName.substring(0,ColumnName.length()-3);

		//	get display column name (first identifier column)
		String SQL = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID "
			+ "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
			+ "WHERE TableName=?"
			+ " AND c.IsIdentifier='Y' "
			+ "ORDER BY c.SeqNo";
		//
		ArrayList list = new ArrayList();
		//
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(SQL);
			pstmt.setString(1, TableName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				LookupDisplayColumn ldc = new LookupDisplayColumn (rs.getString(1),
					"Y".equals(rs.getString(2)), rs.getInt(3), rs.getInt(4));
				list.add (ldc);
				s_log.debug("getLookup_TableDirEmbed: " + ColumnName + " - " + ldc);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("getLookup_TableDirEmbed", e);
			return "";
		}
		//  Do we have columns ?
		if (list.size() == 0)
		{
			s_log.error("getLookup_TableDirEmbed - No Identifier records found: " + ColumnName);
			return "";
		}

		//
		StringBuffer embedSQL = new StringBuffer("SELECT ");

		int size = list.size();
		for (int i = 0; i < size; i++)
		{
			if (i > 0)
				embedSQL.append("||' - '||" );
			LookupDisplayColumn ldc = (LookupDisplayColumn)list.get(i);

			//  date, number
			if (DisplayType.isDate(ldc.DisplayType) || DisplayType.isNumeric(ldc.DisplayType))
			{
				embedSQL.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
			}
			//  TableDir
			else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
			  && ldc.ColumnName.endsWith("_ID"))
			{
				String embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName);
				embedSQL.append("(").append(embeddedSQL).append(")");
			}
			//  String
			else
				embedSQL.append(TableName).append(".").append(ldc.ColumnName);
		}

		embedSQL.append(" FROM ").append(TableName);
		embedSQL.append(" WHERE ").append(BaseTable).append(".").append(BaseColumn);
		embedSQL.append("=").append(TableName).append(".").append(ColumnName);
		//
		return embedSQL.toString();
	}	//  getLookup_TableDirEmbed

}   //  MLookupFactory