/// <summary>
/// Get Button Text
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="selectSQL"></param>
/// <returns>Text on button</returns>
public String GetButtonText(String tableName, String columnName, String selectSQL)
{
// SELECT (<embedded>) FROM tableName avd WHERE avd.<selectSQL>
StringBuilder sql = new StringBuilder("SELECT (");
Language language = Env.GetLanguage(_ctx);
sql.Append(VLookUpFactory.GetLookup_TableDirEmbed(language, columnName, "avd"))
.Append(") FROM ").Append(tableName).Append(" avd WHERE avd.").Append(selectSQL);
String retValue = "<" + selectSQL + ">";
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, null);
if (idr.Read())
{
retValue = Utility.Util.GetValueOfString(idr[0]);
}
idr.Close();
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString(), e);
}
return(retValue);
}
/// <summary>
/// Get Lookup
/// </summary>
/// <param name="index"></param>
/// <returns></returns>
public Lookup GetColumnLookup(int index)
{
if (!IsColumnLookup(index))
{
return(null);
}
//
int WindowNo = 0;
// List, Table, TableDir
Lookup lookup = null;
try
{
lookup = VLookUpFactory.Get(m_ctx, WindowNo, m_columns[index].AD_Column_ID,
m_columns[index].DisplayType,
m_columns[index].ColumnName,
m_columns[index].AD_Reference_Value_ID,
m_columns[index].IsParent, m_columns[index].ValidationCode);
}
catch
{
lookup = null; // cannot create Lookup
}
return(lookup);
}
/// <summary>
///Validate Fields and create LookupInfo if required
/// </summary>
public void InitFinish()
{
// Not null fields
if (DisplayLogic == null)
{
DisplayLogic = "";
}
if (DefaultValue == null)
{
DefaultValue = "";
}
if (FieldGroup == null)
{
FieldGroup = "";
}
if (Description == null)
{
Description = "";
}
if (Help == null)
{
Help = "";
}
if (Callout == null)
{
Callout = "";
}
if (ReadOnlyLogic == null)
{
ReadOnlyLogic = "";
}
// Create Lookup, if not ID
if (ctx.SkipLookup) //No need if call from Visual editor
{
return;
}
if (DisplayType.IsLookup(displayType))
{
try
{
lookupInfo = VLookUpFactory.GetLookUpInfo(ctx, windowNo, displayType,
AD_Column_ID, Env.GetLanguage(ctx), ColumnName, AD_Reference_Value_ID,
IsParent, ValidationCode);
}
catch (Exception e) // Cannot create Lookup
{
VLogger.Get().Log(Level.SEVERE, "No LookupInfo for " + ColumnName, e);
displayType = DisplayType.ID;
}
}
}
public static Lookup GetLookup(Ctx ctx, int windowNo, int Column_ID, int AD_Reference_ID,
String columnName, int AD_Reference_Value_ID,
bool IsParent, String ValidationCode)
{
return(VLookUpFactory.Get(ctx, windowNo, Column_ID, AD_Reference_ID, columnName, AD_Reference_Value_ID, IsParent, ValidationCode));
}
/// <summary>
/// Create Report Column
/// </summary>
/// <param name="ctx">context </param>
/// <param name="columnName">column name</param>
/// <param name="displayType">display type</param>
/// <param name="sql">sql (if null then columnName is used). </param>
/// <param name="AD_Reference_Value_ID">List/Table Reference</param>
/// <param name="refColumnName">UserReference column name
/// Will be overwritten if TableDir or Search</param>
public RColumn(Ctx ctx, String columnName, int displayType,
String sql, int AD_Reference_Value_ID, String refColumnName)
{
_colHeader = Msg.Translate(ctx, columnName);
if (refColumnName != null)
{
_colHeader = Msg.Translate(ctx, refColumnName);
}
_displayType = displayType;
_colSQL = sql;
if (_colSQL == null || _colSQL.Length == 0)
{
_colSQL = columnName;
}
// Strings
if (DisplayType.IsText(displayType))
{
_colClass = typeof(String);// String.class; // default size=30
}
// Amounts
else if (displayType == DisplayType.Amount)
{
_colClass = typeof(Decimal);// BigDecimal.class;
_colSize = 70;
}
// Boolean
else if (displayType == DisplayType.YesNo)
{
_colClass = typeof(Boolean);// Boolean.class;
}
// Date
else if (DisplayType.IsDate(displayType))
{
_colClass = typeof(DateTime);// Timestamp.class;
}
// Number
else if (displayType == DisplayType.Quantity ||
displayType == DisplayType.Number ||
displayType == DisplayType.CostPrice)
{
_colClass = typeof(Double);// Double.class;
_colSize = 70;
}
// Integer
else if (displayType == DisplayType.Integer)
{
_colClass = typeof(int);// Integer.class;
}
// List
else if (displayType == DisplayType.List)
{
Language language = Language.GetLanguage(Env.GetAD_Language(ctx));
_colSQL = "(" + VLookUpFactory.GetLookup_ListEmbed(
language, AD_Reference_Value_ID, columnName) + ")";
_colClass = typeof(String);// String.class;
_isIDcol = false;
}
/** Table
* else if (displayType == DisplayType.Table)
* {
* Language language = Language.getLanguage(Env.getAD_Language(ctx));
* _colSQL += ",(" + MLookupFactory.getLookup_TableEmbed(
* language, columnName, RModel.TABLE_ALIAS, AD_Reference_Value_ID) + ")";
* _colClass = String.class;
* _isIDcol = false;
* } **/
// TableDir, Search,...
else
{
_colClass = typeof(String);// String.class;
Language language = Language.GetLanguage(Env.GetAD_Language(ctx));
if (columnName.Equals("Account_ID") ||
columnName.Equals("User1_ID") || columnName.Equals("User2_ID"))
{
_colSQL += ",(" + VLookUpFactory.GetLookup_TableDirEmbed(
language, "C_ElementValue_ID", RModel.TABLE_ALIAS, columnName) + ")";
_isIDcol = true;
}
else if (columnName.StartsWith("UserElement") && refColumnName != null)
{
_colSQL += ",(" + VLookUpFactory.GetLookup_TableDirEmbed(
language, refColumnName, RModel.TABLE_ALIAS, columnName) + ")";
_isIDcol = true;
}
else if (columnName.Equals("C_LocFrom_ID") || columnName.Equals("C_LocTo_ID"))
{
_colSQL += ",(" + VLookUpFactory.GetLookup_TableDirEmbed(
language, "C_Location_ID", RModel.TABLE_ALIAS, columnName) + ")";
_isIDcol = true;
}
else if (columnName.Equals("AD_OrgTrx_ID"))
{
_colSQL += ",(" + VLookUpFactory.GetLookup_TableDirEmbed(
language, "AD_Org_ID", RModel.TABLE_ALIAS, columnName) + ")";
_isIDcol = true;
}
else if (displayType == DisplayType.TableDir)
{
_colSQL += ",(" + VLookUpFactory.GetLookup_TableDirEmbed(
language, columnName, RModel.TABLE_ALIAS) + ")";
_isIDcol = true;
}
}
}
/// <summary>
/// function will accept columnName and Ids selected. Will Fetch information from Default tree hierarchy and get child records accordingly.
/// </summary>
/// <param name="_ctx"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <param name="result"></param>
/// <returns>Name of orgs separated bY commas, and IDS in Reference Object</returns>
private static string GetRecursiveParameterValue(Ctx _ctx, string columnName, string value, ref string result, bool ShowChildOfSelected)
{
string tableName = columnName.Substring(0, columnName.Length - 3);
string[] values = value.Split(',');
String eSql = "";
string result1 = "";
StringBuilder finalResult = new StringBuilder();
StringBuilder nonSummaryResult = new StringBuilder();
if (values.Length > 0)
{
// Get Default Heirarchy
string sqla = @"SELECT PA_HIERARCHY_id FROM PA_Hierarchy WHERE ISACTIVE ='Y'
ORDER BY ISDEFAULT DESC ,PA_HIERARCHY_id ASC";
sqla = MRole.GetDefault(_ctx).AddAccessSQL(sqla, "PA_Hierarchy", true, true);
object ID = DB.ExecuteScalar(sqla);
int _PA_Hierarchy_ID = 0;
if (ID != null && ID != DBNull.Value)
{
_PA_Hierarchy_ID = Util.GetValueOfInt(ID);
}
Language _language = Language.GetLanguage(_ctx.GetAD_Language());
//Get Query to fetch identifier value from table based on column selected. it will be used to display identifires on for parameter in report.
eSql = VLookUpFactory.GetLookup_TableDirEmbed(_language, columnName, columnName.Substring(0, columnName.Length - 3));
for (int i = 0; i < values.Length; i++)
{
if (!string.IsNullOrEmpty(values[i]))
{
//try
//{
string sqlCheckSummary = "SELECT IsSummary FROM " + tableName + " WHERE " + columnName + "=" + values[i];
object val = DB.ExecuteScalar(sqlCheckSummary);
if (val != null && val != DBNull.Value)
{
if (val.ToString().Equals("N")) // If non-summary is selected then add it string and continue to next ID
{
if (nonSummaryResult.Length > 0)
{
nonSummaryResult.Append("," + values[i]);
}
else
{
nonSummaryResult.Append(values[i]);
}
continue;
}
}
//}
//catch
//{
// result = "";
// continue;
//}
// Fetch child records from tree hierarchy based on ID selected.
if (columnName.Equals("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Organization, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_BPartner_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_BPartner, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("M_Product_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Product, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Project_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Project, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("AD_OrgTrx_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_OrgTrx, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("Account_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Account, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Campaign_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Campaign, Convert.ToInt32(values[i]));
}
if (result1.IndexOf("(") > -1)
{
result1 = result1.Substring(result1.IndexOf("(") + 1);
result1 = result1.Substring(0, result1.IndexOf(")"));
}
else
{
result1 = result1.Substring(result1.IndexOf("=") + 1);
}
//create list of sleected IDs in stringbuilder
if (result1 != values[i] && result1.Length > 0)
{
if (finalResult.Length > 0)
{
finalResult.Append("," + result1);
}
else
{
finalResult.Append(result1);
}
}
}
}
}
StringBuilder identifiedsName = new StringBuilder();
if (finalResult.Length > 0)
{
if (finalResult.ToString().IndexOf(",") > -1)
{
eSql = eSql + " AND " + columnName + " IN (" + finalResult.ToString() + ")";
}
else
{
eSql = eSql + " AND " + columnName + " = " + finalResult.ToString();
}
//eSql = eSql + " AND " + result1;
//if (!string.IsNullOrEmpty(finalResult.ToString()))
//{
// result = result + "," + finalResult.ToString();
//}
DataSet dsIdeintifiers = DB.ExecuteDataset(eSql);
if (ShowChildOfSelected && (dsIdeintifiers != null && dsIdeintifiers.Tables[0].Rows.Count > 0))
{
for (int s = 0; s < dsIdeintifiers.Tables[0].Rows.Count; s++)
{
if (identifiedsName.Length > 0)
{
identifiedsName.Append(",");
}
identifiedsName.Append(dsIdeintifiers.Tables[0].Rows[s][0]);
}
}
}
if (nonSummaryResult.Length > 0 || finalResult.Length > 0)
{
if (nonSummaryResult.Length > 0)
{
result = nonSummaryResult.ToString();
}
if (finalResult.Length > 0)
{
if (result.Length > 0)
{
result += ",";
}
result += finalResult.ToString();
}
}
if (identifiedsName != null)
{
return(identifiedsName.ToString());
}
return("");
}
} // getPOInfo
/// <summary>
/// Get SQL Query for table
/// </summary>
/// <returns>SQL Query (String)</returns>
public string GetSQLQuery()
{
StringBuilder _querySQL = new StringBuilder("");
if (m_columns.Length > 0)
{
_querySQL.Append("SELECT ");
MTable tbl = new MTable(m_ctx, _AD_Table_ID, null);
// append all columns from table and get comma separated string
_querySQL.Append(tbl.GetSelectColumns());
foreach (var column in m_columns)
{
// check if column name length is less than 26, then only add this column in selection column
// else only ID will be displayed
// as limitation in oracle to restrict column name to 30 characters
if ((column.ColumnName.Length + 4) < 30)
{
// for Lookup type of columns
if (DisplayType.IsLookup(column.DisplayType))
{
VLookUpInfo lookupInfo = VLookUpFactory.GetLookUpInfo(m_ctx, 0, column.DisplayType,
column.AD_Column_ID, Env.GetLanguage(m_ctx), column.ColumnName, column.AD_Reference_Value_ID,
column.IsParent, column.ValidationCode);
if (lookupInfo != null && lookupInfo.displayColSubQ != null && lookupInfo.displayColSubQ.Trim() != "")
{
if (lookupInfo.queryDirect.Length > 0)
{
// create columnname as columnname_TXT for lookup type of columns
lookupInfo.displayColSubQ = " (SELECT MAX(" + lookupInfo.displayColSubQ + ") " + lookupInfo.queryDirect.Substring(lookupInfo.queryDirect.LastIndexOf(" FROM " + lookupInfo.tableName + " "), lookupInfo.queryDirect.Length - (lookupInfo.queryDirect.LastIndexOf(" FROM " + lookupInfo.tableName + " "))) + ") AS " + column.ColumnName + "_TXT";
lookupInfo.displayColSubQ = lookupInfo.displayColSubQ.Replace("@key", tbl.GetTableName() + "." + column.ColumnName);
}
_querySQL.Append(", " + lookupInfo.displayColSubQ);
}
}
// case for Location type of columns
else if (column.DisplayType == DisplayType.Location)
{
_querySQL.Append(", " + column.ColumnName + " AS " + column.ColumnName + "_LOC");
}
// case for Locator type of columns
else if (column.DisplayType == DisplayType.Locator)
{
_querySQL.Append(", " + column.ColumnName + " AS " + column.ColumnName + "_LTR");
}
// case for Attribute Set Instance & General Attribute columns
else if (column.DisplayType == DisplayType.PAttribute || column.DisplayType == DisplayType.GAttribute)
{
_querySQL.Append(", " + column.ColumnName + " AS " + column.ColumnName + "_ASI");
}
// case for Account type of columns
else if (column.DisplayType == DisplayType.Account)
{
_querySQL.Append(", " + column.ColumnName + " AS " + column.ColumnName + "_ACT");
}
}
}
// Append FROM table name to query
_querySQL.Append(" FROM " + tbl.GetTableName());
}
return(_querySQL.ToString());
}
