/// <summary>
///
/// </summary>
/// <param name="VerTblName"></param>
public void GetSystemElements(string VerTblName)
{
if (_listDefVerElements.Count == listDefVerCols.Count)
{
return;
}
_listDefVerElements.Clear();
if (!listDefVerCols.Contains(VerTblName + "_ID"))
{
listDefVerCols.Add(VerTblName + "_ID");
}
string DefSysEle = string.Join(",", listDefVerCols
.Select(x => string.Format("'{0}'", x)));
DataSet dsDefVerCols = DB.ExecuteDataset("SELECT AD_Element_ID, ColumnName FROM AD_Element WHERE ColumnName IN (" + DefSysEle + ")", null, Get_TrxName());
if (dsDefVerCols != null && dsDefVerCols.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < listDefVerCols.Count; i++)
{
DataRow[] drSysEle = dsDefVerCols.Tables[0].Select("ColumnName='" + listDefVerCols[i] + "'");
if (drSysEle.Length > 0)
{
if (!_listDefVerElements.Contains(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"])))
{
_listDefVerElements.Add(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"]));
}
}
else
{
M_Element ele = new M_Element(GetCtx(), 0, Get_Trx());
ele.SetAD_Client_ID(0);
ele.SetAD_Org_ID(0);
ele.SetName(listDefVerCols[i]);
ele.SetColumnName(listDefVerCols[i]);
ele.SetPrintName(listDefVerCols[i]);
if (!ele.Save())
{
}
else
{
_listDefVerElements.Add(ele.GetAD_Element_ID());
if (ele.GetColumnName() == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
}
}
}
}
/// <summary>
/// Get System Elements for Default Columns
/// </summary>
/// <param name="VerTblName">Table Name</param>
public string GetSystemElements(string VerTblName)
{
// check if count in list is equal to default version columns
if (_listDefVerElements.Count == listDefVerCols.Count)
{
return("");
}
// Clear values from list
_listDefVerElements.Clear();
// check if Primary key column is present in Columns list, if not present then add Primary Key column
if (!listDefVerCols.Contains(VerTblName + "_ID"))
{
listDefVerCols.Add(VerTblName + "_ID");
}
// Create comma separated string of all default version columns
string DefSysEle = string.Join(",", listDefVerCols
.Select(x => string.Format("'{0}'", x)));
// Get System Elements and Column Names for all Version table columns
DataSet dsDefVerCols = DB.ExecuteDataset("SELECT AD_Element_ID, ColumnName FROM AD_Element WHERE ColumnName IN (" + DefSysEle + ")", null, _trx);
if (dsDefVerCols != null && dsDefVerCols.Tables[0].Rows.Count > 0)
{
// loop through all columns of version table to get System Elements
// if not found then create new
for (int i = 0; i < listDefVerCols.Count; i++)
{
DataRow[] drSysEle = dsDefVerCols.Tables[0].Select("ColumnName='" + listDefVerCols[i] + "'");
if (drSysEle.Length > 0)
{
if (!_listDefVerElements.Contains(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"])))
{
_listDefVerElements.Add(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"]));
}
if (listDefVerCols[i] == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
else
{
M_Element ele = new M_Element(GetCtx(), 0, _trx);
ele.SetAD_Client_ID(0);
ele.SetAD_Org_ID(0);
ele.SetName(listDefVerCols[i]);
ele.SetColumnName(listDefVerCols[i]);
ele.SetPrintName(listDefVerCols[i]);
if (!ele.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating System Element";
}
log.Log(Level.SEVERE, error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ElementNotSaved"));
}
else
{
_listDefVerElements.Add(ele.GetAD_Element_ID());
if (ele.GetColumnName() == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
}
}
}
return("");
}
} // addTable
/// <summary>
/// Add Table columns in DB
/// </summary>
/// <param name="ctx">Ctx</param>
/// <param name="rs">Dataset</param>
/// <param name="table">Table Object</param>
/// <param name="entityType">Entity type</param>
/// <returns></returns>
protected List <String> AddTableColumn(Ctx ctx, DataSet rs, MTable table, String entityType)
{
//MClientShare
List <String> colName = new List <String>();
String tableName = table.GetTableName();
if (DatabaseType.IsOracle)
{
tableName = tableName.ToUpper();
//
for (int i = 0; i <= rs.Tables[0].Rows.Count - 1; i++)
{
String tn = rs.Tables[0].Rows[i]["TABLE_NAME"].ToString();
if (!tableName.Equals(tn, StringComparison.OrdinalIgnoreCase))
{
continue;
}
String columnName = rs.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
colName.Add(columnName);
MColumn column = table.GetColumn(columnName);
if (column != null)
{
continue;
}
//int dataType = Utility.Util.GetValueOfInt(rs.Tables[0].Rows[i]["DATATYPE"].ToString());
String typeName = rs.Tables[0].Rows[i]["DATATYPE"].ToString();
String nullable = rs.Tables[0].Rows[i]["NULLABLE"].ToString();
int size = Utility.Util.GetValueOfInt(rs.Tables[0].Rows[i]["LENGTH"]);
int digits = Utility.Util.GetValueOfInt(rs.Tables[0].Rows[i]["PRECISION"]);
//
log.Config(columnName + " - DataType=" + " " + typeName
+ ", Nullable=" + nullable + ", Size=" + size + ", Digits="
+ digits);
//
column = new MColumn(table);
column.SetEntityType(entityType);
// Element
M_Element element = M_Element.Get(ctx, columnName, Get_Trx());
if (element == null)
{
element = new M_Element(ctx, columnName, entityType, Get_Trx());
element.Save();
}
// Column Sync
column.SetColumnName(element.GetColumnName());
column.SetName(element.GetName());
column.SetDescription(element.GetDescription());
column.SetHelp(element.GetHelp());
column.SetAD_Element_ID(element.GetAD_Element_ID());
// Other
column.SetIsMandatory("NO".Equals(nullable));
column.SetIsMandatoryUI(column.IsMandatory());
// Key
if (columnName.Equals(tableName + "_ID", StringComparison.OrdinalIgnoreCase))
{
column.SetIsKey(true);
column.SetAD_Reference_ID(DisplayType.ID);
column.SetIsUpdateable(false);
}
// Account
else if ((columnName.ToUpper().IndexOf("ACCT") != -1) &&
(size == 10))
{
column.SetAD_Reference_ID(DisplayType.Account);
}
// Location
else if (columnName.Equals("C_Location_ID", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.Location);
}
// Product Attribute
else if (columnName.Equals("M_AttributeSetInstance_ID"))
{
column.SetAD_Reference_ID(DisplayType.PAttribute);
}
// SalesRep_ID (=User)
else if (columnName.Equals("SalesRep_ID", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.Table);
column.SetAD_Reference_Value_ID(190);
}
// ID
else if (columnName.EndsWith("_ID"))
{
column.SetAD_Reference_ID(DisplayType.TableDir);
}
// Date
else if ((typeName == Types.DATE) || (typeName == Types.TIME) ||
(typeName == Types.TIMESTAMP)
// || columnName.toUpperCase().indexOf("DATE") != -1
|| columnName.Equals("Created", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("Updated", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.DateTime);
column.SetIsUpdateable(false);
}
// CreatedBy/UpdatedBy (=User)
else if (columnName.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("UpdatedBy", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.Table);
column.SetAD_Reference_Value_ID(110);
column.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_DoNOTCreate);
column.SetIsUpdateable(false);
}
// Entity Type
else if (columnName.Equals("EntityType", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.Table);
column.SetAD_Reference_Value_ID(389);
column.SetDefaultValue("U");
column.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_Restrict);
column.SetReadOnlyLogic("@EntityType@=D");
}
// CLOB
else if (typeName == Types.CLOB)
{
column.SetAD_Reference_ID(DisplayType.TextLong);
}
// BLOB
else if (typeName == Types.BLOB)
{
column.SetAD_Reference_ID(DisplayType.Binary);
}
// Amount
else if (columnName.ToUpper().IndexOf("AMT") != -1)
{
column.SetAD_Reference_ID(DisplayType.Amount);
}
// Qty
else if (columnName.ToUpper().IndexOf("QTY") != -1)
{
column.SetAD_Reference_ID(DisplayType.Quantity);
}
// Boolean
else if ((size == 1) &&
(columnName.ToUpper().StartsWith("IS") || (typeName == Types.CHAR)))
{
column.SetAD_Reference_ID(DisplayType.YesNo);
}
// List
else if ((size < 4) && (typeName == Types.CHAR))
{
column.SetAD_Reference_ID(DisplayType.List);
}
// Name, DocumentNo
else if (columnName.Equals("Name", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("DocumentNo", StringComparison.OrdinalIgnoreCase))
{
column.SetAD_Reference_ID(DisplayType.String);
column.SetIsIdentifier(true);
column.SetSeqNo(1);
}
// String, Text
else if ((typeName == Types.CHAR) || (typeName == Types.VARCHAR) ||
typeName.StartsWith("NVAR") ||
typeName.StartsWith("NCHAR"))
{
if (typeName.StartsWith("N")) // MultiByte
{
size /= 2;
}
if (size > 255)
{
column.SetAD_Reference_ID(DisplayType.Text);
}
else
{
column.SetAD_Reference_ID(DisplayType.String);
}
}
// Number
else if ((typeName == Types.INTEGER) || (typeName == Types.SMALLINT) ||
(typeName == Types.DECIMAL) || (typeName == Types.NUMERIC))
{
if (size == 10)
{
column.SetAD_Reference_ID(DisplayType.Integer);
}
else
{
column.SetAD_Reference_ID(DisplayType.Number);
}
}
// ??
else
{
column.SetAD_Reference_ID(DisplayType.String);
}
// General Defaults
if (columnName.EndsWith("_ID"))
{
column.SetConstraintType(X_AD_Column.CONSTRAINTTYPE_Restrict);
}
if (columnName.Equals("AD_Client_ID"))
{
column.SetAD_Val_Rule_ID(116); // Client Login
column.SetDefaultValue("@#AD_Client_ID@");
column.SetIsUpdateable(false);
}
else if (columnName.Equals("AD_Org_ID"))
{
column.SetAD_Val_Rule_ID(104); // Org Security
column.SetDefaultValue("@#AD_Org_ID@");
column.SetIsUpdateable(false);
}
else if (columnName.Equals("Processed"))
{
column.SetAD_Reference_ID(DisplayType.YesNo);
column.SetDefaultValue("N");
column.SetIsUpdateable(false);
}
else if (columnName.Equals("Posted"))
{
column.SetAD_Reference_ID(DisplayType.Button);
column.SetAD_Reference_Value_ID(234); // _PostedStatus
column.SetDefaultValue("N");
column.SetIsUpdateable(false);
}
// General
column.SetFieldLength(size);
if (column.IsUpdateable() && table.IsView())
{
column.SetIsUpdateable(false);
}
// Done
if (column.Save())
{
AddLog(0, DateTime.Now, null, table.GetTableName() + "." + column.GetColumnName());
m_count++;
}
} // while columns
}
return(colName);
} // addTableColumn
