}// prepare
/// <summary>
/// Alert table
/// </summary>
/// <returns>int</returns>
///
override protected string DoIt()
{
string exception = "";
log.Info("C_Column_ID=" + p_AD_Column_ID);
if (p_AD_Column_ID == 0)
{
// return "";
throw new Exception("@No@ @AD_Column_ID@");
}
//IDbTransaction trx = ExecuteQuery.GerServerTransaction();
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Column_ID@" + p_AD_Column_ID);
}
MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());
if (table.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@" + column.GetAD_Table_ID());
}
// Find Column in Database
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
int noColumns;
string sql = null;
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
md.Dispose();
//for each column
for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
{
string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
if (!columnName.Equals(column.GetColumnName().ToLower()))
{
continue;
}
//check if column is null or not
string dtColumnName = "is_nullable";
string value = "YES";
//if database is oracle
if (DatabaseType.IsOracle)
{
dtColumnName = "NULLABLE";
value = "Y";
}
bool notNull = false;
//check if column is null
if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
{
notNull = false;
}
else
{
notNull = true;
}
//............................
//if column is virtual column then alter table and drop this column
if (column.IsVirtualColumn())
{
sql = "ALTER TABLE " + table.GetTableName()
+ " DROP COLUMN " + columnName;
}
else
{
sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
noColumns++;
break;
}
}
dt = null;
//while (rs.next())
//{
// noColumns++;
// String columnName = rs.getString ("COLUMN_NAME");
// if (!columnName.equalsIgnoreCase(column.getColumnName()))
// continue;
// // update existing column
// boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
// if (column.isVirtualColumn())
// sql = "ALTER TABLE " + table.getTableName()
// + " DROP COLUMN " + columnName;
// else
// sql = column.getSQLModify(table, column.isMandatory() != notNull);
// break;
//}
//rs.close();
//rs = null;
// No Table
if (noColumns == 0)
{
sql = table.GetSQLCreate();
}
// No existing column
else if (sql == null)
{
if (column.IsVirtualColumn())
{
return("@IsVirtualColumn@");
}
sql = column.GetSQLAdd(table);
}
int no = 0;
if (sql.IndexOf("; ") == -1)
{
//no =
//ExecuteQuery.ExecuteNonQuery(sql, false, Get_Trx());
try
{
no = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());
AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
}
catch (Exception ex)
{
exception = ex.Message;
}
//addLog (0, null, new BigDecimal(no), sql);
}
else
{
//string ss = "; ";
string[] statements = sql.Split(';');
for (int i = 0; i < statements.Length; i++)
{
int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_Trx());
AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);
no += count;
//ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
}
}
if (no == -1)
{
string msg = "@Error@ ";
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
msg += exception + " - ";
}
msg += sql;
throw new Exception(msg);
}
string r = createFK();
return(sql + "; " + r);
} // doIt
/// <summary>
/// Sync column or table passed in the parameter
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <param name="noColumns">OUT parameter, returns 0 if table is being synched for the first time</param>
/// <returns>string message</returns>
public static string SyncColumn(MTable table, MColumn column, out int noColumns)
{
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
noColumns = 0;
string sql = null;
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
md.Dispose();
//for each column
for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
{
string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
if (!columnName.Equals(column.GetColumnName().ToLower()))
{
continue;
}
//check if column is null or not
string dtColumnName = "is_nullable";
string value = "YES";
//if database is oracle
if (DatabaseType.IsOracle)
{
dtColumnName = "NULLABLE";
value = "Y";
}
bool notNull = false;
//check if column is null
if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
{
notNull = false;
}
else
{
notNull = true;
}
//............................
//if column is virtual column then alter table and drop this column
if (column.IsVirtualColumn())
{
sql = "ALTER TABLE " + table.GetTableName()
+ " DROP COLUMN " + columnName;
}
else
{
sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
noColumns++;
break;
}
}
dt = null;
//while (rs.next())
//{
// noColumns++;
// String columnName = rs.getString ("COLUMN_NAME");
// if (!columnName.equalsIgnoreCase(column.getColumnName()))
// continue;
// // update existing column
// boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
// if (column.isVirtualColumn())
// sql = "ALTER TABLE " + table.getTableName()
// + " DROP COLUMN " + columnName;
// else
// sql = column.getSQLModify(table, column.isMandatory() != notNull);
// break;
//}
//rs.close();
//rs = null;
// No Table
if (noColumns == 0)
{
sql = table.GetSQLCreate();
}
// No existing column
else if (sql == null)
{
if (column.IsVirtualColumn())
{
return("@IsVirtualColumn@");
}
sql = column.GetSQLAdd(table);
}
return(sql);
}
} // 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