} // 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
} // 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>
///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);
}
}
/// <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());
}
} // 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
/// <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));
}
} //
/// <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));
}
} // 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
} // 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
} // 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 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>
/// 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));
}