/// <summary>
///Add Translation Columns
/// </summary>
private void InitColumns()
{
//MTable table = null;
//MTable table = MTable.get(Env.getCtx(), m_trlTableName);
MTable table = MTable.Get(Utility.Env.GetContext(), _trlTableName);
if (table == null)
{
throw new ArgumentException("Table Not found=" + _trlTableName);
}
MColumn[] columns = table.GetColumns(false);
for (int i = 0; i < columns.Length; i++)
{
MColumn column = columns[i];
if (column.IsStandardColumn())
{
continue;
}
String columnName = column.GetColumnName();
if (columnName.EndsWith("_ID") ||
columnName.StartsWith("AD_Language") ||
columnName.Equals("IsTranslated"))
{
continue;
}
//
_columns.Add(columnName);
}
if (_columns.Count == 0)
{
throw new ArgumentException("No Columns found=" + _trlTableName);
}
}
} // getTargets
/// <summary>
/// Execute Auto Assignment
/// </summary>
/// <param name="po">PO to be modified</param>
/// <returns>true if modified</returns>
public bool ExecuteIt(PO po)
{
if (m_targets == null)
{
m_targets = GetTargets(false);
}
bool modified = false;
for (int i = 0; i < m_targets.Length; i++)
{
MAssignTarget target = m_targets[i];
if (!target.IsActive())
{
continue;
}
// Chck consistency
MColumn tColumn = target.GetTargetColumn();
if (tColumn.GetAD_Table_ID() != GetAD_Table_ID())
{
throw new Exception(ToString()
+ ": AD_Table_ID inconsistent for " + target);
}
//
try
{
modified = target.ExecuteIt(po);
}
catch (Exception e)
{
log.Severe(e.ToString());
modified = false;
}
}
return(modified);
} // execute
} // generateTable
/// <summary>
/// Generate Delta View
/// </summary>
/// <returns>true, if created</returns>
private bool GenerateView()
{
// View
m_viewTable = MTable.Get(m_ctx, m_vTableName);
if (m_viewTable == null)
{
m_viewTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_viewTable);
m_viewTable.SetTableName(m_vTableName);
m_viewTable.SetName(m_baseTable.GetName() + " View");
m_viewTable.SetIsView(true);
if (!m_viewTable.Save())
{
throw new Exception("Cannot save " + m_vTableName);
}
MColumn[] vCols = SyncMColumns(false);
//ColumnSync
// Create View
StringBuilder sql = new StringBuilder("CREATE OR REPLACE VIEW ")
.Append(m_vTableName)
.Append(" AS SELECT ");
for (int i = 0; i < vCols.Length; i++)
{
if (i > 0)
{
sql.Append(",");
}
MColumn column = vCols[i];
String columnName = column.GetColumnName();
if (column.IsStandardColumn() ||
column.IsKey())
{
sql.Append("b.").Append(columnName);
}
else
{
sql.Append(",COALESCE(b.").Append(columnName)
.Append("d.").Append(columnName).Append(") AS ").Append(columnName);
}
}
// From
String keyColumnName = m_baseTable.GetTableName() + "_ID";
sql.Append(" FROM ").Append(m_baseTable.GetTableName())
.Append(" b LEFT OUTER JOIN ").Append(m_dTableName)
.Append(" d ON (b.").Append(keyColumnName)
.Append("=d.").Append(keyColumnName).Append(")");
//
log.Info(sql.ToString());
if (DataBase.DB.ExecuteQuery(sql.ToString(), null) < 0)
{
return(false);
}
return(true);
} // generateView
/// <summary>
/// Set AD_Column_ID
/// </summary>
/// <param name="AD_Column_ID">column</param>
public new void SetAD_Column_ID(int AD_Column_ID)
{
if (_column != null && _column.GetAD_Column_ID() != AD_Column_ID)
{
_column = null;
}
base.SetAD_Column_ID(AD_Column_ID);
}
/**
* Get Source Column
* @return source column
*/
public MColumn GetSourceColumn()
{
if (m_column == null || m_column.GetAD_Column_ID() != GetAD_SourceColumn_ID())
{
m_column = MColumn.Get(GetCtx(), GetAD_SourceColumn_ID());
}
return(m_column);
} // GetSourceColumn
} // getCriteria
/// <summary>
/// Get Target Column
/// </summary>
/// <returns>target column</returns>
public MColumn GetTargetColumn()
{
if (m_column == null || m_column.GetAD_Column_ID() != GetAD_TargetColumn_ID())
{
m_column = MColumn.Get(GetCtx(), GetAD_TargetColumn_ID());
}
return(m_column);
} // getTargetColumn
/// <summary>
/// Get Column
/// </summary>
/// <returns>column</returns>
public MColumn GetColumn()
{
if (_column == null ||
_column.GetAD_Column_ID() != GetAD_Column_ID())
{
_column = MColumn.Get(GetCtx(), GetAD_Column_ID());
}
return(_column);
}
/// <summary>
/// Set Column Values
/// </summary>
/// <param name="column">column column</param>
public void SetColumn(MColumn column)
{
_column = column;
SetAD_Column_ID(column.GetAD_Column_ID());
SetName(column.GetName());
SetDescription(column.GetDescription());
SetHelp(column.GetHelp());
SetDisplayLength(column.GetFieldLength());
SetEntityType(column.GetEntityType());
}
/// <summary>
/// Get Column Name
/// </summary>
/// <param name="ctx">context</param>
/// <param name="AD_Column_ID">column id</param>
/// <returns>Column Name or null</returns>
public static string GetColumnName(Ctx ctx, int AD_Column_ID)
{
MColumn col = MColumn.Get(ctx, AD_Column_ID);
if (col.Get_ID() == 0)
{
return(null);
}
return(col.GetColumnName());
}
/// <summary>
/// Get Display ColumnName
/// </summary>
/// <returns>column name</returns>
public String GetDisplayColumnName()
{
String et = GetElementType();
if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et))
{
if (_ColumnName == null)
{
_ColumnName = MColumn.GetColumnName(GetCtx(), GetAD_Column_ID());
}
return(_ColumnName);
}
return(GetColumnName(et));
}
} // checkStandardColumns
/// <summary>
/// Create Column in AD and DB
/// </summary>
/// <param name="col">Column Object</param>
/// <param name="table">Table Object</param>
/// <param name="alsoInDB">Also in DB</param>
/// <returns></returns>
private static bool CreateColumn(MColumn col, MTable table, bool alsoInDB)
{
// Element
M_Element element = M_Element.Get(col.GetCtx(), col.GetColumnName(), col.Get_TrxName());
if (element == null)
{
element = new M_Element(col.GetCtx(), col.GetColumnName(), col.GetEntityType(), null);
if (!element.Save())
{
return(false);
}
log.Info("Created Element: " + element.GetColumnName());
}
// Column Sync
col.SetColumnName(element.GetColumnName());
col.SetName(element.GetName());
col.SetDescription(element.GetDescription());
col.SetHelp(element.GetHelp());
col.SetAD_Element_ID(element.GetAD_Element_ID());
//
if (!col.Save())
{
return(false);
}
// DB
if (!alsoInDB)
{
return(true);
}
//
String sql = col.GetSQLAdd(table);
bool success = DataBase.DB.ExecuteUpdateMultiple(sql, false, table.Get_TrxName()) >= 0;
if (success)
{
log.Info("Created: " + table.GetTableName()
+ "." + col.GetColumnName());
}
else
{
log.Warning("NOT Created: " + table.GetTableName()
+ "." + col.GetColumnName());
}
return(success);
} // createColumn
/// <summary>
/// Get M_Column from Cache
/// </summary>
/// <param name="ctx">context</param>
/// <param name="AD_Column_ID">column id</param>
/// <returns>MColumn</returns>
public static MColumn Get(Ctx ctx, int AD_Column_ID)
{
int key = AD_Column_ID;
MColumn retValue = null;
retValue = s_cache[key];
if (retValue != null)
{
return(retValue);
}
retValue = new MColumn(ctx, AD_Column_ID, null);
if (retValue.Get_ID() != 0)
{
s_cache.Add(key, retValue);
}
return(retValue);
}
/**
* Exclude SerNo creation
* @param AD_Column_ID column
* @param isSOTrx SO
* @return true if excluded
*/
public bool IsExcludeSerNo(int AD_Column_ID, bool isSOTrx)
{
if (GetM_SerNoCtl_ID() == 0)
{
return(true);
}
if (_excludeSerNos == null)
{
List <X_M_SerNoCtlExclude> list = new List <X_M_SerNoCtlExclude>();
String sql = "SELECT * FROM M_SerNoCtlExclude WHERE IsActive='Y' AND M_SerNoCtl_ID=" + GetM_SerNoCtl_ID();
DataSet ds = null;
try
{
ds = ExecuteQuery.ExecuteDataset(sql, null);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
list.Add(new X_M_SerNoCtlExclude(GetCtx(), dr, null));
}
ds = null;
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
}
_excludeSerNos = new X_M_SerNoCtlExclude[list.Count];
_excludeSerNos = list.ToArray();
}
// Find it
if (_excludeSerNos != null && _excludeSerNos.Length > 0)
{
MColumn column = MColumn.Get(GetCtx(), AD_Column_ID);
for (int i = 0; i < _excludeSerNos.Length; i++)
{
if (_excludeSerNos[i].GetAD_Table_ID() == column.GetAD_Table_ID() &&
_excludeSerNos[i].IsSOTrx() == isSOTrx)
{
return(true);
}
}
}
return(false);
}
} //
/// <summary>
///Get list of columns for SELECT statement.
/// Handles virtual columns
/// </summary>
/// <returns>select columns</returns>
public String GetSelectColumns()
{
GetColumns(false);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < m_columns.Length; i++)
{
MColumn col = m_columns[i];
if (i > 0)
{
sb.Append(",");
}
if (col.IsVirtualColumn())
{
sb.Append(col.GetColumnSQL()).Append(" AS ");
}
sb.Append(col.GetColumnName());
}
return(sb.ToString());
} // getSelectColum
/// <summary>
/// Get Display ColumnName
/// </summary>
/// <returns>column name</returns>
public String GetDisplayColumnName()
{
String et = GetElementType();
// Changed By Amit 23-3-2016
//if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et))
if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et) ||
ELEMENTTYPE_UserElement3.Equals(et) || ELEMENTTYPE_UserElement4.Equals(et) ||
ELEMENTTYPE_UserElement5.Equals(et) || ELEMENTTYPE_UserElement6.Equals(et) ||
ELEMENTTYPE_UserElement7.Equals(et) || ELEMENTTYPE_UserElement8.Equals(et) ||
ELEMENTTYPE_UserElement9.Equals(et))
{
if (_ColumnName == null)
{
_ColumnName = MColumn.GetColumnName(GetCtx(), GetAD_Column_ID());
}
return(_ColumnName);
}
return(GetColumnName(et));
}
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord">newRecord new</param>
/// <param name="success">success</param>
/// <returns>success</returns>
protected override bool AfterSave(bool newRecord, bool success)
{
if (!success)
{
return(false);
}
// Sync Table ID
if (newRecord)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else
{
MSequence seq = MSequence.Get(GetCtx(), GetTableName(), Get_TrxName());
if (seq == null || seq.Get_ID() == 0)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else if (!seq.GetName().Equals(GetTableName()))
{
seq.SetName(GetTableName());
seq.Save();
}
}
// checked if value is changed in Maintain Version
if (Is_ValueChanged("IsMaintainVersions"))
{
if (!newRecord && IsMaintainVersions())
{
int ColID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT AD_Column_ID FROM AD_Column WHERE IsActive = 'Y' AND AD_Table_ID = " + GetAD_Table_ID() + " ORDER BY ColumnName", null, Get_Trx()));
if (ColID > 0)
{
MColumn column = new MColumn(GetCtx(), ColID, Get_Trx());
MasterVersions mv = new MasterVersions();
string versionMsg = mv.CreateVersionInfo(column.GetAD_Column_ID(), column.GetAD_Table_ID(), Get_Trx());
}
}
}
return(success);
} // afterSave
} // isSingleKey
/// <summary>
///Get Key Columns of Table
/// </summary>
/// <returns>key columns</returns>
public String[] GetKeyColumns()
{
List <String> list = new List <String>();
GetColumns(false);
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (column.IsKey())
{
return new String[] { column.GetColumnName() }
}
;
if (column.IsParent())
{
list.Add(column.GetColumnName());
}
}
String[] retValue;
retValue = list.ToArray();
return(retValue);
} // getKeyColumns
} // getTargetColumn
/// <summary>
/// Execute Auto Assignment
/// </summary>
/// <param name="po">PO to be modified</param>
/// <returns>true if modified</returns>
public bool ExecuteIt(PO po)
{
// Check Column
MColumn column = GetTargetColumn();
String columnName = column.GetColumnName();
int index = po.Get_ColumnIndex(columnName);
if (index == -1)
{
throw new Exception(ToString() + ": AD_Column_ID not found");
}
// Check Value
Object value = po.Get_Value(index);
String assignRule = GetAssignRule();
if (value == null && assignRule.Equals(ASSIGNRULE_OnlyIfNOTNULL))
{
return(false);
}
else if (value != null && assignRule.Equals(ASSIGNRULE_OnlyIfNULL))
{
return(false);
}
// Check Criteria
if (m_criteria == null)
{
GetCriteria(false);
}
bool modified = false;
for (int i = 0; i < m_criteria.Length; i++)
{
MAssignCriteria criteria = m_criteria[i];
if (criteria.IsMet(po))
{
modified = true;
break;
}
}
if (!modified)
{
return(false);
}
// Assignment
String methodName = "set" + columnName;
Type parameterType = null;
Object parameter = null;
int displayType = column.GetAD_Reference_ID();
String valueString = GetValueString();
if (DisplayType.IsText(displayType) || displayType == DisplayType.List)
{
parameterType = typeof(string);
parameter = valueString;
}
else if (DisplayType.IsID(displayType) || displayType == DisplayType.Integer)
{
parameterType = typeof(int);
if (GetRecord_ID() != 0)
{
parameter = GetRecord_ID();
}
else if (valueString != null && valueString.Length > 0)
{
try
{
parameter = int.Parse(valueString);
}
catch (Exception e)
{
log.Warning(ToString() + " " + e);
return(false);
}
}
}
else if (DisplayType.IsNumeric(displayType))
{
parameterType = typeof(Decimal);
if (valueString != null && valueString.Length > 0)
{
try
{
parameter = Decimal.Parse(valueString);
}
catch (Exception e)
{
log.Warning(ToString() + " " + e);
return(false);
}
}
}
else if (DisplayType.IsDate(displayType))
{
parameterType = typeof(DateTime);
if (valueString != null && valueString.Length > 0)
{
try
{
parameter = DateTime.Parse(valueString);
}
catch (Exception e)
{
log.Warning(ToString() + " " + e);
return(false);
}
}
}
else if (displayType == DisplayType.YesNo)
{
parameterType = typeof(bool);
parameter = "Y".Equals(valueString);
}
else if (displayType == DisplayType.Button)
{
parameterType = typeof(string);
parameter = GetValueString();
}
else if (DisplayType.IsLOB(displayType)) // CLOB is String
{
parameterType = typeof(byte[]);
// parameter = getValueString();
}
// Assignment
try
{
Type clazz = po.GetType();
System.Reflection.MethodInfo method = clazz.GetMethod(methodName, new Type[] { parameterType });
method.Invoke(po, new Object[] { parameter });
}
catch (Exception e)
{
log.Log(Level.WARNING, ToString(), e);
// fallback
if (parameter is Boolean)
{
po.Set_Value(index, valueString);
}
else
{
po.Set_Value(index, parameter);
}
// modified = false;
}
return(modified);
} // executeIt
/// <summary>
/// Get Display ColumnName
/// </summary>
/// <returns>Display Column Name</returns>
public String GetDisplayColumnName()
{
int AD_Column_ID = GetColumn_Display_ID();
return(MColumn.GetColumnName(GetCtx(), AD_Column_ID));
}
} // getSQLCrete
// getSQLCrete
/**
* Get SQL Create
* @param requery refresh columns
* @return create table DDL
*/
public String GetSQLCreate(bool requery)
{
StringBuilder sb = new StringBuilder("CREATE TABLE ")
.Append(GetTableName()).Append(" (");
//
bool hasPK = false;
bool hasParents = false;
bool firstColumn = true;
StringBuilder constraints = new StringBuilder();
StringBuilder unqConstraints = new StringBuilder();
GetColumns(requery);
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (column.IsVirtualColumn())
{
continue;
}
if (firstColumn)
{
firstColumn = false;
}
else
{
sb.Append(", ");
}
sb.Append(column.GetSQLDDL());
//
if (column.IsKey())
{
constraints.Append(", CONSTRAINT PK").Append(GetAD_Table_ID())
.Append(" PRIMARY KEY (").Append(column.GetColumnName()).Append(")");
hasPK = true;
}
if (column.IsParent())
{
hasParents = true;
}
if (column.IsUnique())
{
if (unqConstraints.Length == 0)
{
unqConstraints.Append(", CONSTRAINT UK").Append(GetAD_Table_ID())
.Append(" UNIQUE (").Append(column.GetColumnName());
}
else
{
unqConstraints.Append(",").Append(column.GetColumnName());
}
}
}
// Multi Column PK
if (!hasPK && hasParents)
{
StringBuilder cols = new StringBuilder();
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (!column.IsParent())
{
continue;
}
if (cols.Length > 0)
{
cols.Append(", ");
}
cols.Append(column.GetColumnName());
}
sb.Append(", CONSTRAINT PK").Append(GetAD_Table_ID())
.Append(" PRIMARY KEY (").Append(cols).Append(")");
}
if (unqConstraints.Length > 0)
{
unqConstraints.Append(")");
}
sb.Append(constraints);
sb.Append(unqConstraints);
sb.Append(")");
return(sb.ToString());
} // getSQLCreate
} // GetSourceColumn
/**
* Is Criteria Met
* @param po po
* @return true if criteria is met
*/
public bool IsMet(PO po)
{
MColumn column = GetSourceColumn();
String columnName = column.GetColumnName();
int index = po.Get_ColumnIndex(columnName);
if (index == -1)
{
throw new Exception(ToString() + ": AD_Column_ID not found");
}
// Get Value
Object value = po.Get_Value(index);
String op = GetOperation();
// Compare Value
String compareString = GetValueString();
if (op.Equals(OPERATION_Sql))
{
compareString = GetSQLValue();
op = OPERATION_Eq;
}
// NULL handling
if (value == null)
{
if (compareString == null ||
compareString.Length == 0 ||
compareString.Equals("NULL", StringComparison.OrdinalIgnoreCase))
{
if (op.Equals(OPERATION_Eq))
{
return(true);
}
}
else
{
if (!op.Equals(OPERATION_Eq))
{
return(true);
}
}
return(false);
}
if (GetRecord_ID() == 0 && // no value to compare to
(compareString == null || compareString.Length == 0))
{
return(false);
}
// Like - String
if (op.Equals(OPERATION_Like))
{
String s = value.ToString();
String cmp = compareString;
if (cmp.IndexOf('%') != -1) // SQL Like
{
log.Warning(ToString() + ": SQL LIKE not supported yet");
//TODO: SQL Like
}
return(s.ToUpper()
.IndexOf(cmp.ToUpper()) != 0); // substring
}
try
{
if (value is int)
{
int ii = (int)value;
int?cmp = null;
if (GetRecord_ID() > 0)
{
cmp = GetRecord_ID();
}
else
{
cmp = (int)int.Parse(compareString);
}
// Tree Handling
bool?treeOp = (bool?)TreeOperation(columnName, cmp, op, ii, po.GetAD_Client_ID());
if (treeOp != null)
{
return(treeOp.Value);
}
//
if (op.Equals(OPERATION_Eq))
{
return(ii.Equals(cmp));
}
else if (op.Equals(OPERATION_NotEq))
{
return(!ii.Equals(cmp));
}
else if (op.Equals(OPERATION_Gt))
{
return(ii.CompareTo(cmp) > 0);
}
else if (op.Equals(OPERATION_GtEq))
{
return(ii.CompareTo(cmp) >= 0);
}
else if (op.Equals(OPERATION_Le))
{
return(ii.CompareTo(cmp) < 0);
}
else if (op.Equals(OPERATION_LeEq))
{
return(ii.CompareTo(cmp) <= 0);
}
}
else if (value is Decimal)
{
Decimal bd = (Decimal)value;
Decimal cmp = decimal.Parse(compareString);
if (op.Equals(OPERATION_Eq))
{
return(bd.Equals(cmp));
}
else if (op.Equals(OPERATION_NotEq))
{
return(!bd.Equals(cmp));
}
else if (op.Equals(OPERATION_Gt))
{
return(bd.CompareTo(cmp) > 0);
}
else if (op.Equals(OPERATION_GtEq))
{
return(bd.CompareTo(cmp) >= 0);
}
else if (op.Equals(OPERATION_Le))
{
return(bd.CompareTo(cmp) < 0);
}
else if (op.Equals(OPERATION_LeEq))
{
return(bd.CompareTo(cmp) <= 0);
}
}
else if (value is DateTime)
{
DateTime?ts = (DateTime?)value;
DateTime cmp = DateTime.Parse(compareString);
if (op.Equals(OPERATION_Eq))
{
return(ts.Equals(cmp));
}
else if (op.Equals(OPERATION_NotEq))
{
return(!ts.Equals(cmp));
}
else if (op.Equals(OPERATION_Gt))
{
return(ts.Value.CompareTo(cmp) > 0);
}
else if (op.Equals(OPERATION_GtEq))
{
return(ts.Value.CompareTo(cmp) >= 0);
}
else if (op.Equals(OPERATION_Le))
{
return(ts.Value.CompareTo(cmp) < 0);
}
else if (op.Equals(OPERATION_LeEq))
{
return(ts.Value.CompareTo(cmp) <= 0);
}
}
else
// String
{
String s = value.ToString();
String cmp = compareString;
if (op.Equals(OPERATION_Eq))
{
return(s.Equals(cmp));
}
else if (op.Equals(OPERATION_NotEq))
{
return(!s.Equals(cmp));
}
else if (op.Equals(OPERATION_Gt))
{
return(s.CompareTo(cmp) > 0);
}
else if (op.Equals(OPERATION_GtEq))
{
return(s.CompareTo(cmp) >= 0);
}
else if (op.Equals(OPERATION_Le))
{
return(s.CompareTo(cmp) < 0);
}
else if (op.Equals(OPERATION_LeEq))
{
return(s.CompareTo(cmp) <= 0);
}
}
}
catch (Exception e)
{
log.Warning(ToString() + ": " + e);
}
return(false);
} // isMet
/// <summary>
/// Get Key ColumnName
/// </summary>
/// <returns>Key Column Name</returns>
public String GetKeyColumnName()
{
int AD_Column_ID = GetColumn_Key_ID();
return(MColumn.GetColumnName(GetCtx(), AD_Column_ID));
}
/// <summary>
/// Parse Variable
/// </summary>
/// <param name="variable">variable</param>
/// <param name="po">po object</param>
/// <returns>translated variable or if not found the original tag</returns>
private String ParseVariable(String variable, PO po)
{
int index = po.Get_ColumnIndex(variable);
if (index == -1)
{
return("@" + variable + "@"); // keep for next
}
//
Object value = po.Get_Value(index);
if (value == null)
{
return("");
}
MColumn column = (new MTable(GetCtx(), po.Get_Table_ID(), Get_Trx())).GetColumn(variable);
if (column.GetAD_Reference_ID() == DisplayType.Location)
{
StringBuilder sb = new StringBuilder();
DataSet ds = DB.ExecuteDataset(@"SELECT l.address1,
l.address2,
l.address3,
l.address4,
l.city,
CASE
WHEN l.C_City_ID IS NOT NULL
THEN
( SELECT NAME FROM C_City ct WHERE ct.C_City_ID=l.C_City_ID
)
ELSE NULL
END CityName,
(SELECT NAME FROM C_Country c WHERE c.C_Country_ID=l.C_Country_ID
) AS CountryName
FROM C_Location l WHERE l.C_Location_ID=" + value);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["address1"] != null && ds.Tables[0].Rows[0]["address1"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address1"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address2"] != null && ds.Tables[0].Rows[0]["address2"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address2"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address3"] != null && ds.Tables[0].Rows[0]["address3"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address3"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address4"] != null && ds.Tables[0].Rows[0]["address4"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address4"]).Append(",");
}
if (ds.Tables[0].Rows[0]["city"] != null && ds.Tables[0].Rows[0]["city"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["city"]).Append(",");
}
if (ds.Tables[0].Rows[0]["CityName"] != null && ds.Tables[0].Rows[0]["CityName"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["CityName"]).Append(",");
}
if (ds.Tables[0].Rows[0]["CountryName"] != null && ds.Tables[0].Rows[0]["CountryName"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["CountryName"]).Append(",");
}
return(sb.ToString().TrimEnd(','));
}
else
{
return("");
}
}
//Get lookup display column name for ID
if (_poInfo != null && _poInfo.getAD_Table_ID() == po.Get_Table_ID() && _poInfo.IsColumnLookup(index) && value != null)
{
VLookUpInfo lookup = _poInfo.GetColumnLookupInfo(index); //create lookup info for column
DataSet ds = DB.ExecuteDataset(lookup.queryDirect.Replace("@key", DB.TO_STRING(value.ToString())), null); //Get Name from data
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
value = ds.Tables[0].Rows[0][2]; //Name Value
}
}
if (column.GetAD_Reference_ID() == DisplayType.Date)
{
return(Util.GetValueOfDateTime(value).Value.Date.ToShortDateString());
}
return(value.ToString());
}
} // generate
/// <summary>
/// Generate Delta and History Table
/// </summary>
/// <returns></returns>
private bool GenerateTable()
{
// Table
m_derivedTable = MTable.Get(m_ctx, m_dTableName);
if (m_derivedTable == null)
{
m_derivedTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_derivedTable);
m_derivedTable.SetTableName(m_dTableName);
m_derivedTable.SetName(m_derivedTable.GetName() + " SubTable");
m_derivedTable.SetSubTableType(m_derivedTableType);
m_derivedTable.SetBase_Table_ID(m_baseTable.GetAD_Table_ID());
if (!m_derivedTable.Save())
{
throw new Exception("Cannot save " + m_dTableName);
}
MColumn[] dCols = SyncMColumns(true);
// Sync Columns in Database
List <MColumn> list = new List <MColumn>(dCols.Length);
foreach (MColumn element in dCols)
{
list.Add(element);
}
Trx trx = Trx.Get("getDatabaseMetaData");
//
String catalog = "";
String schema = DataBase.DB.GetSchema();
String tableName = m_dTableName;
tableName = tableName.ToUpper();
int noColumns = 0;
//
DataSet rs = null;
using (DatabaseMetaData md = new DatabaseMetaData())
{
rs = md.GetColumns(catalog, schema, tableName);
}
for (int rscount = 0; rscount <= rs.Tables[0].Rows.Count - 1; rscount++)
{
noColumns++;
String columnName = rs.Tables[0].Rows[rscount]["COLUMN_NAME"].ToString();
bool found = false;
for (int i = 0; i < list.Count; i++)
{
MColumn dCol = list[i];
if (columnName.Equals(dCol.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
String sql = dCol.GetSQLModify(m_derivedTable, false);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
found = true;
list.Remove(list[i]);
break;
}
}
if (!found)
{
String sql = "ALTER TABLE " + m_dTableName + " DROP COLUMN " + columnName;
DataBase.DB.ExecuteQuery(sql, null);
}
}
//rs.close();
trx.Close();
// No Columns
if (noColumns == 0)
{
String sql = m_derivedTable.GetSQLCreate();
return(DataBase.DB.ExecuteUpdateMultiple(sql, false, null) >= 0);
}
// New Columns
for (int i = 0; i < list.Count(); i++)
{
MColumn dCol = list[i];
if (dCol.IsVirtualColumn())
{
continue;
}
String sql = dCol.GetSQLAdd(m_derivedTable);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
}
return(true);
} // generateTable
/// <summary>
/// Check Existence of Std columns and create them in AD and DB
/// </summary>
/// <param name="table">name of the table</param>
/// <param name="EntityType">Entity Type</param>
public static void CheckStandardColumns(MTable table, String EntityType)
{
if (table == null)
{
return;
}
if (Utility.Util.IsEmpty(EntityType))
{
EntityType = table.GetEntityType();
}
table.GetColumns(true); // get new columns
// Key Column
String colName = table.GetTableName() + "_ID";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.ID);
col.SetIsKey(true);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "AD_Client_ID";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.TableDir);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetAD_Val_Rule_ID(116); // Client Login
col.SetDefaultValue("@#AD_Client_ID@");
col.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_Restrict);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "AD_Org_ID";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.TableDir);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetAD_Val_Rule_ID(104); // Org Security
col.SetDefaultValue("@#AD_Org_ID@");
col.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_Restrict);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "Created";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.DateTime);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "Updated";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.DateTime);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "CreatedBy";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.Table);
col.SetAD_Reference_Value_ID(110);
col.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_DoNOTCreate);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "UpdatedBy";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.Table);
col.SetAD_Reference_Value_ID(110);
col.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_DoNOTCreate);
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "IsActive";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.YesNo);
col.SetDefaultValue("Y");
col.SetIsUpdateable(true);
col.SetIsMandatory(true);
col.SetEntityType(EntityType);
CreateColumn(col, table, true);
}
colName = "Export_ID";
if (table.GetColumn(colName) == null)
{
MColumn col = new MColumn(table);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.String);
col.SetIsUpdateable(true);
col.SetIsMandatory(false);
col.SetEntityType(EntityType);
col.SetFieldLength(50);
CreateColumn(col, table, true);
}
} // checkStandardColumns
} // generateView
/// <summary>
/// Synchronize target table with base table in dictionary
/// </summary>
/// <param name="derived">is derived</param>
/// <returns></returns>
private MColumn[] SyncMColumns(bool derived)
{
MTable target = derived ? m_derivedTable : m_viewTable;
MTable source = derived ? m_baseTable : m_derivedTable;
MColumn[] sCols = source.GetColumns(false);
MColumn[] tCols = target.GetColumns(false);
// Base Columns
foreach (MColumn sCol in sCols)
{
MColumn tCol = null;
foreach (MColumn column in tCols)
{
if (sCol.GetColumnName().Equals(column.GetColumnName()))
{
tCol = column;
break;
}
}
if (tCol == null)
{
tCol = new MColumn(target);
}
PO.CopyValues(sCol, tCol);
tCol.SetAD_Table_ID(target.GetAD_Table_ID()); // reset parent
tCol.SetIsCallout(false);
tCol.SetCallout(null);
tCol.SetIsMandatory(false);
tCol.SetIsMandatoryUI(false);
tCol.SetIsTranslated(false);
// tCol.SetIsUpdateable(true);
if (tCol.IsKey())
{
tCol.SetIsKey(false);
tCol.SetAD_Reference_ID(DisplayType.TableDir);
}
if (tCol.Save())
{
throw new Exception("Cannot save column " + sCol.GetColumnName());
}
}
//
tCols = target.GetColumns(true);
List <String> addlColumns = new List <String>();
if (derived && !m_history) // delta only
{
// KeyColumn
String keyColumnName = target.GetTableName() + "_ID";
MColumn key = target.GetColumn(keyColumnName);
if (key == null)
{
key = new MColumn(target);
M_Element ele = M_Element.Get(m_ctx, keyColumnName, target.Get_TrxName());
if (ele == null)
{
ele = new M_Element(m_ctx, keyColumnName, target.GetEntityType(), null);
ele.Save();
}
key.SetAD_Element_ID(ele.GetAD_Element_ID());
key.SetAD_Reference_ID(DisplayType.ID);
key.Save();
}
addlColumns.Add(keyColumnName);
// Addl References
if (m_userDef)
{
String colName = "AD_Role_ID";
addlColumns.Add(colName);
if (target.GetColumn(colName) == null)
{
MColumn col = new MColumn(target);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.TableDir);
CreateColumn(col, target, false);
col.SetIsUpdateable(false);
col.SetIsMandatory(false);
}
colName = "AD_User_ID";
addlColumns.Add(colName);
if (target.GetColumn(colName) == null)
{
MColumn col = new MColumn(target);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.TableDir);
col.SetIsUpdateable(false);
col.SetIsMandatory(false);
CreateColumn(col, target, false);
}
}
else // System
{
String colName = "IsSystemDefault";
addlColumns.Add(colName);
if (target.GetColumn(colName) == null)
{
MColumn col = new MColumn(target);
col.SetColumnName(colName);
col.SetAD_Reference_ID(DisplayType.YesNo);
col.SetDefaultValue("N");
col.SetIsUpdateable(false);
col.SetIsMandatory(true);
CreateColumn(col, target, false);
}
}
}
// Delete
foreach (MColumn tCol in tCols)
{
MColumn sCol = null;
foreach (MColumn column in sCols)
{
if (tCol.GetColumnName().Equals(column.GetColumnName()))
{
sCol = column;
break;
}
}
if (sCol == null)
{
if (!addlColumns.Contains(tCol.GetColumnName()))
{
if (!tCol.Delete(true))
{
throw new Exception("Cannot delete column "
+ tCol.GetColumnName());
}
}
}
}
return(tCols);
} // SyncMColumns
