public List <MColumn> GetColumnList(IDbConnection conn, string tableName)
{
string sql = string.Format(@"SELECT NAME,TBNAME,TBCREATOR,REMARKS,COLTYPE,LENGTH, NULLS,DEFAULT,KEYSEQ
FROM sysibm.syscolumns where tbname ='{0}'", tableName);
DB2Command cmd = new DB2Command(sql, (DB2Connection)conn);
List <MColumn> ret = new List <MColumn>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MColumn model = new MColumn();
model.ColumnName = DbUtil.GetDBValueStr(reader, "NAME");
model.Creator = DbUtil.GetDBValueStr(reader, "TBCREATOR");
model.Remarks = DbUtil.GetDBValueStr(reader, "REMARKS");
model.ColType = DbUtil.GetDBValueStr(reader, "COLTYPE");
model.Length = DbUtil.GetDBValueInt(reader, "LENGTH");
model.IsNullable = DbUtil.GetDBValueBool(reader, "NULLS");
model.DefaultValue = DbUtil.GetDBValueStr(reader, "DEFAULT");
model.KeySeq = DbUtil.GetDBValueInt(reader, "KEYSEQ");
ret.Add(model);
}
}
return(ret);
}
public List<MColumn> GetColumnList(IDbConnection conn, string tableName)
{
string sql = string.Format(@"SELECT NAME,TBNAME,TBCREATOR,REMARKS,COLTYPE,LENGTH, NULLS,DEFAULT,KEYSEQ
FROM sysibm.syscolumns where tbname ='{0}'", tableName);
DB2Command cmd = new DB2Command(sql, (DB2Connection)conn);
List<MColumn> ret = new List<MColumn>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MColumn model = new MColumn();
model.ColumnName = DBUtil.GetDBValueStr(reader, "NAME");
model.Creator = DBUtil.GetDBValueStr(reader, "TBCREATOR");
model.Remarks = DBUtil.GetDBValueStr(reader, "REMARKS");
model.ColType = DBUtil.GetDBValueStr(reader, "COLTYPE");
model.Length = DBUtil.GetDBValueInt(reader, "LENGTH");
model.IsNullable = DBUtil.GetDBValueBool(reader, "NULLS");
model.DefaultValue = DBUtil.GetDBValueStr(reader, "DEFAULT");
model.KeySeq = DBUtil.GetDBValueInt(reader, "KEYSEQ");
ret.Add(model);
}
}
return ret;
}
/// <summary>
/// Sync Version table in database
/// </summary>
/// <param name="table"></param>
/// <param name="AD_Column_ID"></param>
/// <returns>Message (String)</returns>
private string SyncVersionTable(MTable table, int AD_Column_ID, out bool Success)
{
// create object of Column passed in parameter
Success = true;
MColumn column = new MColumn(GetCtx(), AD_Column_ID, _trx);
int noColumns = 0;
// sync table in database
string sql = CommonFunctions.SyncColumn(table, column, out noColumns);
string exception = "";
int no = 0;
if (sql.IndexOf(";") == -1)
{
try
{
no = DataBase.DB.ExecuteQuery(sql, null, _trx);
AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
}
catch (Exception ex)
{
Success = false;
exception = ex.Message;
return(exception);
}
//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, _trx);
AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);
no += count;
}
}
if (no == -1)
{
Success = false;
string msg = "@Error@ ";
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
msg += exception + " - ";
}
msg += sql;
return(msg);
}
// Apply constraints on columns for Version table
ColumnSync colSync = new ColumnSync();
colSync.SetAD_Column_ID(AD_Column_ID);
string r = colSync.createFK(noColumns);
return(r);
}
/// <summary>
///Get Column
/// </summary>
/// <returns>column if valid</returns>
public MColumn GetColumn()
{
if (GetAD_Column_ID() == 0)
{
return(null);
}
if (_column == null)
{
_column = MColumn.Get(GetCtx(), GetAD_Column_ID());
}
return(_column);
}
/// <summary>
/// Create default columns for Master Data Version Table
/// e.g. Processed, Processing, IsApproved etc.
/// </summary>
/// <param name="Ver_AD_Table_ID"></param>
/// <returns></returns>
private string CreateDefaultVerCols(int Ver_AD_Table_ID)
{
for (int i = 0; i < listDefVerCols.Count; i++)
{
MColumn colVer = new MColumn(GetCtx(), 0, _trx);
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
colVer.SetColumnName(listDefVerCols[i]);
colVer.SetAD_Element_ID(_listDefVerElements[i]);
colVer.SetAD_Reference_ID(listDefVerRef[i]);
//if (listDefVerCols[i] == "VersionValidFrom")
// colVer.SetIsParent(true);
if (listDefVerRef[i] == 10)
{
colVer.SetFieldLength(10);
}
if (listDefVerRef[i] == 14)
{
colVer.SetFieldLength(2000);
}
if (listDefVerRef[i] == 13)
{
colVer.SetIsKey(true);
colVer.SetIsMandatory(true);
colVer.SetIsMandatoryUI(true);
}
if (!colVer.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 Version Column " + listDefVerCols[i];
}
log.Log(Level.SEVERE, "Version Column not created :: " + listDefVerCols[i] + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
}
return("");
}
} // getIndex
/// <summary>
/// Get Index of Node in Structure (not recursing) row
/// </summary>
/// <param name="AD_Column_ID">AD_Column_ID</param>
/// <returns>index or -1</returns>
public int GetIndex(int AD_Column_ID)
{
if (_columnInfo == null)
{
return(-1);
}
for (int i = 0; i < _columnInfo.Length; i++)
{
if (_columnInfo[i].GetAD_Column_ID() == AD_Column_ID)
{
return(GetIndex(_columnInfo[i].GetColumnName()));
}
}
// OK for virtual Columns with TableDirect, Search
MColumn col = MColumn.Get(GetCtx(), AD_Column_ID);
if (col != null && col.IsVirtualColumn())
{
return(-1); // not found, but OK
}
return(-1);
} // getIndex
} // doIt
/// <summary>
/// Process Translation Table
/// </summary>
/// <param name="table">table</param>
/// <param name="AD_Client_ID">AD_Client_ID</param>
private void ProcessTable(MTable table, int AD_Client_ID)
{
StringBuilder sql = new StringBuilder();
MColumn[] columns = table.GetColumns(false);
for (int i = 0; i < columns.Length; i++)
{
MColumn column = columns[i];
if (column.GetAD_Reference_ID() == DisplayType.String ||
column.GetAD_Reference_ID() == DisplayType.Text)
{
String columnName = column.GetColumnName();
if (sql.Length != 0)
{
sql.Append(",");
}
sql.Append(columnName);
}
}
String baseTable = table.GetTableName();
baseTable = baseTable.Substring(0, baseTable.Length - 4);
log.Config(baseTable + ": " + sql);
String columnNames = sql.ToString();
sql = new StringBuilder();
sql.Append("UPDATE ").Append(table.GetTableName()).Append(" t SET (")
.Append(columnNames).Append(") = (SELECT ").Append(columnNames)
.Append(" FROM ").Append(baseTable).Append(" b WHERE t.")
.Append(baseTable).Append("_ID=b.").Append(baseTable).Append("_ID) WHERE AD_Client_ID=")
.Append(AD_Client_ID);
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
AddLog(0, null, new Decimal(no), baseTable);
} // processTable
protected override string DoIt()
{
if (p_AD_User_ID == -1)
{
p_AD_User_ID = GetAD_User_ID();
}
MUser user = MUser.Get(GetCtx(), p_AD_User_ID);
MUser current = MUser.Get(GetCtx(), GetAD_User_ID());
if (!current.IsAdministrator() && p_AD_User_ID != GetAD_User_ID() && user.HasRole())
{
throw new ArgumentException("@UserCannotUpdate@");
}
// SuperUser and System passwords can only be updated by themselves
if (user.IsSystemAdministrator() && p_AD_User_ID != GetAD_User_ID())
{
throw new ArgumentException("@UserCannotUpdate@");
}
if (string.IsNullOrEmpty(p_CurrentPassword))
{
if (string.IsNullOrEmpty(p_OldPassword))
{
throw new ArgumentException("@OldPasswordMandatory@");
}
else if (!p_OldPassword.Equals(user.GetPassword()))
{
if (!SecureEngineUtility.SecureEngine.Encrypt(p_OldPassword).Equals(user.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
}
}
else if (!p_CurrentPassword.Equals(current.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
String originalPwd = p_NewPassword;
String sql = "UPDATE AD_User SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
MColumn column = MColumn.Get(GetCtx(), 417); // Password Column
if (column.IsEncrypted())
{
p_NewPassword = SecureEngineUtility.SecureEngine.Encrypt(p_NewPassword);
}
sql += ", Password="******", Email=" + GlobalVariable.TO_STRING(p_NewEMail);
}
if (!string.IsNullOrEmpty(p_NewEMailUser))
{
sql += ", EmailUser="******", EmailUserPW=" + GlobalVariable.TO_STRING(p_NewEMailUserPW);
}
sql += " WHERE AD_User_ID=" + p_AD_User_ID;
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes > 0)
{
//user.SetPassword(p_NewPassword);
user.SetPassword(originalPwd);
return("OK");
}
else
{
return("@Error@");
}
}
/// <summary>
/// Create version table and window based on the parent table \
/// where Maintain Version field is marked as true
/// </summary>
/// <returns> Message (String) </returns>
public string CreateVersionInfo(int AD_Column_ID, int AD_Table_ID, Trx trx)
{
_trx = trx;
_AD_Table_ID = AD_Table_ID;
_AD_Column_ID = AD_Column_ID;
bool hasMainVerCol = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + _AD_Table_ID + " AND IsActive ='Y' AND IsMaintainVersions = 'Y'", null, _trx)) > 0;
if (!hasMainVerCol)
{
hasMainVerCol = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + _AD_Table_ID, null, _trx)) == "Y";
}
// check whether there are any columns in the table
// marked as "Maintain Versions", then proceed else return
if (hasMainVerCol)
{
MTable tbl = new MTable(GetCtx(), _AD_Table_ID, _trx);
string VerTblName = tbl.GetTableName() + "_Ver";
// Create/Get System Elements for Version Table Columns
string retMsg = GetSystemElements(VerTblName);
if (retMsg != "")
{
return(retMsg);
}
int Ver_AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = '" + VerTblName + "'", null, _trx));
// check whether version table is already present in system
// if not present then create table
MTable tblVer = null;
if (Ver_AD_Table_ID <= 0)
{
string tableName = tbl.GetTableName();
// create new Version table for parent table
tblVer = new MTable(GetCtx(), 0, _trx);
tbl.CopyTo(tblVer);
tblVer.SetTableName(tableName + "_Ver");
tblVer.SetName(tableName + " Ver");
tblVer.Set_Value("Export_ID", null);
tblVer.Set_Value("AD_Window_ID", null);
tblVer.SetIsDeleteable(true);
tblVer.SetDescription("Table for maintaining versions of " + tableName);
tblVer.SetHelp("Table for maintaining versions of " + tableName);
tblVer.SetIsMaintainVersions(false);
//tblVer.SetAD_Window_ID(Ver_AD_Window_ID);
if (!tblVer.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 Version Table";
}
log.Log(Level.SEVERE, "Version table not created :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionTblNotCreated"));
}
else
{
Ver_AD_Table_ID = tblVer.GetAD_Table_ID();
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
}
else
{
tblVer = new MTable(GetCtx(), Ver_AD_Table_ID, _trx);
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
int VerTableColID = 0;
// if Version table successfully created, then check columns, if not found then create new
if (Ver_AD_Table_ID > 0)
{
// Get all columns from Version Table
int[] ColIDs = MColumn.GetAllIDs("AD_Column", "AD_Table_ID = " + _AD_Table_ID, _trx);
bool hasCols = false;
DataSet dsDestCols = DB.ExecuteDataset("SELECT ColumnName, AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx);
if (dsDestCols != null && dsDestCols.Tables[0].Rows.Count > 0)
{
hasCols = true;
}
// loop through all columns
foreach (int columnID in ColIDs)
{
bool createNew = true;
// object of Column from source table (Master Table)
MColumn sCol = new MColumn(GetCtx(), columnID, _trx);
// check if source column is not Virtual Column, proceed in that case only
if (!sCol.IsVirtualColumn())
{
DataRow[] dr = null;
if (hasCols)
{
dr = dsDestCols.Tables[0].Select("ColumnName = '" + sCol.GetColumnName() + "'");
if (dr.Length > 0)
{
createNew = false;
}
}
// Version Column object
MColumn colVer = null;
int AD_Col_ID = 0;
// if column not present in Version table then create new
if (createNew)
{
colVer = new MColumn(GetCtx(), AD_Col_ID, _trx);
}
// if column already present and user pressed sync button on same column of Master table
// then create object of existing column (in case of change in any column fields)
else if (!createNew && (_AD_Column_ID == columnID))
{
AD_Col_ID = Util.GetValueOfInt(dr[0]["AD_Column_ID"]);
colVer = new MColumn(GetCtx(), Util.GetValueOfInt(dr[0]["AD_Column_ID"]), _trx);
}
if (colVer != null)
{
sCol.CopyTo(colVer);
if (AD_Col_ID > 0)
{
colVer.SetAD_Column_ID(AD_Col_ID);
}
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
// set key column to false
colVer.SetIsKey(false);
// check if source column is key column
// then set Restrict Constraint and set Reference as Table Direct
if (sCol.IsKey())
{
colVer.SetConstraintType("R");
colVer.SetAD_Reference_ID(19);
}
//if (sCol.IsKey())
// colVer.SetIsParent(true);
//else
colVer.SetIsParent(false);
colVer.SetIsMaintainVersions(false);
colVer.SetIsMandatory(false);
colVer.SetIsMandatoryUI(false);
if (!colVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Version Column not created";
}
log.Log(Level.SEVERE, "Version Column not created :: " + sCol.GetColumnName() + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
else
{
VerTableColID = colVer.GetAD_Column_ID();
}
}
}
}
// Get one column to sync table in database from Version Table
if (VerTableColID <= 0)
{
VerTableColID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx));
}
// Get newly Created Column
if (oldVerCol > 0)
{
VerTableColID = oldVerCol;
}
// Sync Version table in database
bool success = true;
retMsg = SyncVersionTable(tblVer, VerTableColID, out success);
// if any error and there is message in return then return and rollback transaction
if (!success && retMsg != "")
{
log.Log(Level.SEVERE, "Column not sync :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNotSync"));
}
else
{
// if table has single key
if (tbl.IsSingleKey())
{
// get column names from parent table
string colNameStrings = GetColumnNameString(tbl.GetAD_Table_ID());
// get default columns string from Version Table columns list
string defColString = GetDefaultColString(colNameStrings);
// Insert data in version table from Master table
InsertVersionData(colNameStrings, defColString, tblVer.GetTableName());
}
// Cases where single key is not present in Master table
else
{
// Insert data in version table against Master Table
retMsg = InsertMKVersionData(tbl, tbl.GetKeyColumns(), tblVer);
if (retMsg != "")
{
log.Log(Level.SEVERE, "Data not Inserted :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "DataInsertionErrorMultikey"));
}
}
}
}
}
return(Msg.GetMsg(GetCtx(), "ProcessCompletedSuccessfully"));
}
} // prepare
/**
* Process
* @return info
* @throws Exception
*/
protected override String DoIt()// throws Exception
{
log.Info("AD_Column_ID=" + p_AD_Column_ID
+ ", IsEncrypted=" + p_IsEncrypted
+ ", ChangeSetting=" + p_ChangeSetting
+ ", MaxLength=" + p_MaxLength);
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, null);
if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
{
throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
}
//
String columnName = column.GetColumnName();
int dt = column.GetAD_Reference_ID();
// Can it be enabled?
if (column.IsKey() ||
column.IsParent() ||
column.IsStandardColumn() ||
column.IsVirtualColumn() ||
column.IsIdentifier() ||
column.IsTranslated() ||
DisplayType.IsLookup(dt) ||
DisplayType.IsLOB(dt) ||
"DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
if (column.IsEncrypted())
{
column.SetIsEncrypted(false);
column.Save();
}
return(columnName + ": cannot be encrypted");
}
// Start
AddLog(0, null, null, "Encryption Class = " + SecureEngineUtility.SecureEngine.GetClassName());
bool error = false;
// Test Value
if (p_TestValue != null && p_TestValue.Length > 0)
{
String encString = SecureEngineUtility.SecureEngine.Encrypt(p_TestValue);
AddLog(0, null, null, "Encrypted Test Value=" + encString);
String clearString = SecureEngineUtility.SecureEngine.Decrypt(encString);
if (p_TestValue.Equals(clearString))
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (same as test value)");
}
else
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (NOT the same as test value - check algorithm)");
error = true;
}
int encLength = encString.Length;
AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
// Length Test
if (p_MaxLength != 0)
{
String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
while (testClear.Length < p_MaxLength)
{
testClear += testClear;
}
testClear = testClear.Substring(0, p_MaxLength);
log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
//
String encString = SecureEngineUtility.SecureEngine.Encrypt(testClear);
int encLength = encString.Length;
AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
if (p_IsEncrypted != column.IsEncrypted())
{
if (error || !p_ChangeSetting)
{
AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
}
else
{
column.SetIsEncrypted(p_IsEncrypted);
if (column.Save())
{
AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
}
else
{
AddLog(0, null, null, "Save Error");
}
}
}
return("Encryption=" + column.IsEncrypted());
}
/// <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 static 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("");
}
POInfo _poInfo = POInfo.GetPOInfo(po.GetCtx(), po.Get_Table_ID());
MColumn column = (new MTable(po.GetCtx(), po.Get_Table_ID(), null)).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());
}
protected override string DoIt()
{
VLogger log = VLogger.GetVLogger(this.GetType().FullName);
log.Log(Level.SEVERE, "UserPassword Change Log=>" + Convert.ToString(p_AD_User_ID));
if (p_AD_User_ID == -1)
{
p_AD_User_ID = GetAD_User_ID();
}
MUser user = MUser.Get(GetCtx(), p_AD_User_ID);
MUser current = MUser.Get(GetCtx(), GetAD_User_ID());
if (!current.IsAdministrator() && p_AD_User_ID != GetAD_User_ID() && user.HasRole())
{
throw new ArgumentException("@UserCannotUpdate@");
}
// SuperUser and System passwords can only be updated by themselves
if (user.IsSystemAdministrator() && p_AD_User_ID != GetAD_User_ID())
{
throw new ArgumentException("@UserCannotUpdate@");
}
log.Log(Level.SEVERE, "UserPassword Change Log Step Check for valid user=>" + Convert.ToString(p_AD_User_ID));
if (string.IsNullOrEmpty(p_CurrentPassword))
{
if (string.IsNullOrEmpty(p_OldPassword))
{
throw new ArgumentException("@OldPasswordMandatory@");
}
else if (!p_OldPassword.Equals(user.GetPassword()))
{
if (!SecureEngine.Encrypt(p_OldPassword).Equals(user.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
}
}
else if (!p_CurrentPassword.Equals(current.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
log.Log(Level.SEVERE, "UserPassword Change Log Step Password Change=>" + Convert.ToString(p_AD_User_ID));
String originalPwd = p_NewPassword;
String sql = "UPDATE AD_User SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
MColumn column = MColumn.Get(GetCtx(), 417); // Password Column
if (column.IsEncrypted())
{
p_NewPassword = SecureEngine.Encrypt(p_NewPassword);
}
sql += ", Password="******", Email=" + GlobalVariable.TO_STRING(p_NewEMail);
}
if (!string.IsNullOrEmpty(p_NewEMailUser))
{
sql += ", EmailUser="******", EmailUserPW=" + GlobalVariable.TO_STRING(p_NewEMailUserPW);
}
sql += " WHERE AD_User_ID=" + p_AD_User_ID;
log.Log(Level.SEVERE, "UserPassword Change Log=>" + sql);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes > 0)
{
bool error = false;
//Check for yellowFin user password change if BI user is true..................
object ModuleId = DB.ExecuteScalar("select ad_moduleinfo_id from ad_moduleinfo where prefix='VA037_' and IsActive = 'Y'"); // is active check by vinay bhatt on 18 oct 2018
if (ModuleId != null && ModuleId != DBNull.Value)
{
if (user.IsVA037_BIUser())
{
var Dll = Assembly.Load("VA037");
var BIUser = Dll.GetType("VA037.BIProcess.BIUsers");
var objBIUser = Activator.CreateInstance(BIUser);
var ChangeBIPassword = BIUser.GetMethod("ChangeBIPassword");
bool value = (bool)ChangeBIPassword.Invoke(objBIUser, new object[] { GetCtx(), GetAD_Client_ID(), Convert.ToString(user.GetVA037_BIUserName()), originalPwd });
if (value)
{
//user.SetPassword(p_NewPassword);
error = false;
user.SetPassword(originalPwd);
//return "OK";
}
else
{
error = true;
// return "@Error@";
}
}
else
{
error = false;
user.SetPassword(originalPwd);
// return "OK";
}
}
ModuleId = DB.ExecuteScalar("select ad_moduleinfo_id from ad_moduleinfo where prefix='VA039_' and IsActive = 'Y'"); // is active check by vinay bhatt
if (ModuleId != null && ModuleId != DBNull.Value)
{
MUser obj = new MUser(GetCtx(), p_AD_User_ID, null);
if (obj.IsVA039_IsJasperUser() == true)
{
var Dll = Assembly.Load("VA039");
var JasperUser = Dll.GetType("VA039.Classes.Users");
var objJasperUser = Activator.CreateInstance(JasperUser);
var BICreateUser = JasperUser.GetMethod("ModifyUserPassword");
object[] args = new object[] { GetCtx(), originalPwd };
bool value = (bool)BICreateUser.Invoke(objJasperUser, args);
if (value)
{
error = false;
user.SetPassword(originalPwd);
//return "@Error@";
}
else
{
error = true;
goto PasswordError;
// return "OK";
}
}
}
else
{
error = false;
user.SetPassword(originalPwd);
// return "OK";
}
PasswordError:
if (error)
{
return("@Error@");
}
else
{
return("OK");
}
}
else
{
return("@Error@");
}
}
public string ApproveIt(int nodeID, int activityID, string textMsg, object forward, object answer, Ctx ctx)
{
MWFActivity activity = new MWFActivity(ctx, activityID, null);
MWFNode node = activity.GetNode();
int approvalLevel = node.GetApprovalLeval();
int AD_User_ID = ctx.GetAD_User_ID();
MColumn column = node.GetColumn();
if (forward != null) // Prefer Forward
{
int fw = int.Parse(forward.ToString());
if (fw == AD_User_ID || fw == 0)
{
return("");
}
if (!activity.ForwardTo(fw, textMsg, true))
{
return("CannotForward");
}
}
// User Choice - Answer
else if (MWFNode.ACTION_UserChoice.Equals(node.GetAction()))
{
if (column == null)
{
column = node.GetColumn();
}
// Do we have an answer?
int dt = column.GetAD_Reference_ID();
String value = null;
value = answer != null?answer.ToString() : null;
//if (dt == DisplayType.YesNo || dt == DisplayType.List || dt == DisplayType.TableDir)
if (!node.IsMultiApproval() &&
(dt == DisplayType.YesNo || dt == DisplayType.List || dt == DisplayType.TableDir))
{
if (value == null || value.Length == 0)
{
return("FillMandatory");
}
//
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
//Genral Attribute Instance
//else if (column.GetColumnName().ToUpper().Equals("C_GENATTRIBUTESETINSTANCE_ID"))
//{
// if (attrib == null)
// {
// Dispatcher.BeginInvoke(delegate
// {
// SetBusy(false);
// ShowMessage.Error("FillMandatory", true, Msg.GetMsg(Envs.GetContext(), "Answer", true));
// //log.Config("Answer=" + value + " - " + textMsg);
// return;
// });
// return;
// }
// SetUserChoice(AD_User_ID, attrib.GetAttributeSetInstance().ToString(), 0, textMsg, activity, node);
//}
else if (forward == null && node.IsMultiApproval() && approvalLevel > 0 && answer.ToString().Equals("Y"))
{
int eventCount = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(WFE.AD_WF_EventAudit_ID) FROM AD_WF_EventAudit WFE
INNER JOIN AD_WF_Process WFP ON (WFP.AD_WF_Process_ID=WFE.AD_WF_Process_ID)
INNER JOIN AD_WF_Activity WFA ON (WFA.AD_WF_Process_ID=WFP.AD_WF_Process_ID)
WHERE WFE.AD_WF_Node_ID=" + node.GetAD_WF_Node_ID() + " AND WFA.AD_WF_Activity_ID=" + activity.GetAD_WF_Activity_ID()));
if (eventCount < approvalLevel) //Forward Activity
{
int superVisiorID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Supervisor_ID FROM AD_User WHERE IsActive='Y' AND AD_User_ID=" + activity.GetAD_User_ID()));
if (superVisiorID == 0)//Approve
{
//SetUserConfirmation(AD_User_ID, textMsg, activity, node);
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
else //forward
{
if (!activity.ForwardTo(superVisiorID, textMsg, true))
{
//Dispatcher.BeginInvoke(delegate
//{
// SetBusy(false);
// ShowMessage.Error("CannotForward", true);
// return;
//});
return("CannotForward");
}
}
}
else //Approve
{
//SetUserConfirmation(AD_User_ID, textMsg, activity, node);
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
}
else
{
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
}
// User Action
else
{
// log.Config("Action=" + node.GetAction() + " - " + textMsg);
//try
//{
// activity.SetUserConfirmation(AD_User_ID, textMsg);
//}
//catch (Exception exx)
//{
// Dispatcher.BeginInvoke(delegate
// {
// SetBusy(false);
// log.Log(Level.SEVERE, node.GetName(), exx);
// ShowMessage.Error("Error", true, exx.ToString());
// return;
// });
// return;
//}
activity.SetUserConfirmation(AD_User_ID, textMsg);
}
return("");
}
protected override string DoIt()
{
VLogger log = VLogger.GetVLogger(this.GetType().FullName);
log.Log(Level.SEVERE, "UserPassword Change Log=>" + Convert.ToString(p_AD_User_ID));
if (p_AD_User_ID == -1)
{
p_AD_User_ID = GetAD_User_ID();
}
MUser user = MUser.Get(GetCtx(), p_AD_User_ID);
MUser current = MUser.Get(GetCtx(), GetAD_User_ID());
if (!current.IsAdministrator() && p_AD_User_ID != GetAD_User_ID() && user.HasRole())
{
throw new ArgumentException("@UserCannotUpdate@");
}
// SuperUser and System passwords can only be updated by themselves
if (user.IsSystemAdministrator() && p_AD_User_ID != GetAD_User_ID() && GetAD_User_ID() != 100)
{
throw new ArgumentException("@UserCannotUpdate@");
}
log.Log(Level.SEVERE, "UserPassword Change Log Step Check for valid user=>" + Convert.ToString(p_AD_User_ID));
if (string.IsNullOrEmpty(p_CurrentPassword))
{
if (string.IsNullOrEmpty(p_OldPassword))
{
throw new ArgumentException("@OldPasswordMandatory@");
}
else if (!p_OldPassword.Equals(user.GetPassword()))
{
if (!SecureEngine.Encrypt(p_OldPassword).Equals(user.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
}
}
else if (!p_CurrentPassword.Equals(current.GetPassword()))
{
throw new ArgumentException("@OldPasswordNoMatch@");
}
string validatePwd = Common.Common.ValidatePassword(null, p_NewPassword, p_NewPassword);
if (validatePwd.Length > 0)
{
throw new ArgumentException(Msg.GetMsg(GetCtx(), validatePwd));
}
log.Log(Level.SEVERE, "UserPassword Change Log Step Password Change=>" + Convert.ToString(p_AD_User_ID));
String originalPwd = p_NewPassword;
String sql = "UPDATE AD_User SET Updated=SYSDATE,FailedloginCount=0, UpdatedBy=" + GetAD_User_ID();
if (user.GetAD_User_ID() == current.GetAD_User_ID())
{
Common.Common.UpdatePasswordAndValidity(p_NewPassword, p_AD_User_ID, GetAD_User_ID(), -1, GetCtx());
}
else
{
sql += ", PasswordExpireOn = null";
}
if (!string.IsNullOrEmpty(p_NewPassword))
{
MColumn column = MColumn.Get(GetCtx(), 417); // Password Column
if (column.IsEncrypted())
{
p_NewPassword = SecureEngine.Encrypt(p_NewPassword);
}
sql += ", Password="******", Email=" + GlobalVariable.TO_STRING(p_NewEMail);
}
if (!string.IsNullOrEmpty(p_NewEMailUser))
{
sql += ", EmailUser="******", EmailUserPW=" + GlobalVariable.TO_STRING(p_NewEMailUserPW);
}
sql += " WHERE AD_User_ID=" + p_AD_User_ID;
log.Log(Level.SEVERE, "UserPassword Change Log=>" + sql);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes > 0)
{
return("@OK@");
}
else
{
return("@Error@");
}
}
/// <summary>
/// Gets the parent record for every row (if exists)
/// </summary>
/// <param name="currentTable">Current table to be parsed</param>
/// <param name="exportdata">Exportdata info</param>
private string GetForeignData(MTable currentTable, ExportDataRecords exportdata)
{
try
{
//check if the record is already exported.
var res = _ExecutedRecordList.Where((a) => a.AD_Table_ID == exportdata.AD_Table_ID)
.Where((a) => a.Record_ID == exportdata.Record_ID)
.Where((a) => a.AD_ColOne_ID == exportdata.AD_ColOne_ID);
//if (res.Count() <= 0)
{
_ExecutedRecordList.Add(exportdata);
String tableName = currentTable.GetTableName();
if (!_ExceptionTables.Contains(tableName))
{
if (exportdata.AD_ColOne_ID == 0)
{
File.AppendAllText(HostingEnvironment.ApplicationPhysicalPath + "\\log\\XMLLog.txt", tableName + " : " + exportdata.Record_ID);
int found = 0;
if (ds.Tables[tableName] != null)
{
found = ds.Tables[tableName].Select(tableName + "_ID = " + exportdata.Record_ID).Count();
}
MColumn[] columns = currentTable.GetColumns(true); //Fetch column details
if (columns.Length > 0)
{
string sql = GetSql(currentTable.GetAD_Table_ID(), currentTable.GetTableName(), exportdata);
DataSet tmpDS = DB.ExecuteDataset(sql, null);
if (tmpDS == null || tmpDS.Tables[0].Rows.Count <= 0)
{
//sql = sql.Substring(sql.IndexOf("WHERE"));
if (tmpDS != null)
{
deleteSqlExp.Add("delete from AD_ExportData Where record_ID = " + exportdata.Record_ID + " and ad_table_id = " + exportdata.AD_Table_ID + " and ad_Moduleinfo_id = " + _AD_ModuleInfo_ID);
}
return("");
}
if (tmpDS.Tables[0].Rows[0]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[0]["Export_ID"] = ManageExportID(exportdata.Record_ID, tableName);
}
ds.AddOrCopy(tmpDS, tableName, exportdata.Record_ID, 0, null, rowNum++); //add or copy
for (int cols = 0; cols <= columns.Length - 1; cols++)
{
string colName = columns[cols].GetColumnName();
int refVID = columns[cols].GetAD_Reference_Value_ID();
int refID = columns[cols].GetAD_Reference_ID();
// Special case applied for workflow table to bypass the start node on workflow- asked by mukesh sir- done by mohit- 1 February 2019.
if (tableName == "AD_Workflow" && colName == "AD_WF_Node_ID")
{
continue;
}
if (!columns[cols].IsStandardColumn() && !columns[cols].IsKey())
{
if (colName.EndsWith("_ID")) //only columns ending with _ID to be processed (indicated Foreign Key )
{
if (!columns[cols].GetColumnName().Equals("Export_ID"))
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
if (colValue != null)
{
if (!String.IsNullOrEmpty(colValue.ToString()))
{
MTable fkTable = columns[cols].GetFKTable(); //Get the Parent table of the FK Column
if (fkTable != null)
{
GetForeignData(fkTable, new ExportDataRecords()
{
AD_Table_ID = fkTable.GetAD_Table_ID(), Record_ID = Convert.ToInt32(colValue)
});
}
}
}
}
}
else if (refID == DisplayType.List || refID == DisplayType.Table)
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
MRefTable refTable = CheckReference(new ExportDataRecords()
{
Record_ID = refVID, AD_Table_ID = MTable.Get_Table_ID("AD_Reference")
}, "AD_Reference");
if (refTable != null && colValue != null && colValue.ToString() != "")
{
try
{
MTable tbl = MTable.Get(GetCtx(), refTable.GetAD_Table_ID());
//string tName = MTable.GetTableName(GetCtx(), refTable.GetAD_Table_ID());
string cName = MColumn.GetColumnName(GetCtx(), refTable.GetColumn_Key_ID());
int recordId;
if (int.TryParse(colValue.ToString(), out recordId)) //If Value is type of int
{
;
}
else
{
recordId = Convert.ToInt32(DB.ExecuteScalar("SELECT " + tbl.GetTableName() + "_ID FROM " + tbl.GetTableName() + " WHERE " + cName + " = '" + colValue.ToString() + "'"));
cName = tbl.GetTableName() + "_ID";
}
DataSet temp = DB.ExecuteDataset("SELECT * FROM " + tbl.GetTableName() + " WHERE " + cName + " = " + recordId);
ds.AddOrCopy(temp, tbl.GetTableName(), recordId, 0, null, rowNum++);
GetForeignData(tbl, new ExportDataRecords()
{
AD_Table_ID = tbl.GetAD_Table_ID(), Record_ID = recordId
});
}
catch (Exception ex)
{
log.Severe("Table Reference =>" + ex.Message);
}
}
}
else
{
;
}
}
}
} //column length #if
}
else
{
MColumn[] columns = currentTable.GetColumns(true); //Fetch column details
if (columns.Length > 0)
{
string sql = GetSql(currentTable.GetAD_Table_ID(), currentTable.GetTableName(), exportdata);
DataSet tmpDS = DB.ExecuteDataset(sql, null);
if (tmpDS == null || tmpDS.Tables[0].Rows.Count <= 0)
{
//sql = sql.Substring(sql.IndexOf("WHERE"));
if (tmpDS != null)
{
deleteSqlExp.Add("delete from AD_ExportData Where record_ID = " + exportdata.Record_ID + " and ad_table_id = " + exportdata.AD_Table_ID + " and ad_Moduleinfo_id = " + _AD_ModuleInfo_ID);
}
return("");
}
if (tmpDS.Tables[0].Rows[0]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[0]["Export_ID"] = ManageExportID(exportdata.Record_ID, exportdata.AD_ColOne_ID, tableName, currentTable.GetAD_Table_ID());
}
ds.AddOrCopy(tmpDS, tableName, exportdata.Record_ID, exportdata.AD_ColOne_ID, GetParentColumns(currentTable.GetAD_Table_ID()), rowNum++); //add or copy
for (int cols = 0; cols <= columns.Length - 1; cols++)
{
string colName = columns[cols].GetColumnName();
int refVID = columns[cols].GetAD_Reference_Value_ID();
int refID = columns[cols].GetAD_Reference_ID();
if (!columns[cols].IsStandardColumn() && !columns[cols].IsKey())
{
if (colName.EndsWith("_ID")) //only columns ending with _ID to be processed (indicated Foreign Key )
{
if (!columns[cols].GetColumnName().Equals("Export_ID"))
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
if (colValue != null)
{
if (!String.IsNullOrEmpty(colValue.ToString()))
{
MTable fkTable = columns[cols].GetFKTable(); //Get the Parent table of the FK Column
if (fkTable != null)
{
GetForeignData(fkTable, new ExportDataRecords()
{
AD_Table_ID = fkTable.GetAD_Table_ID(), Record_ID = Convert.ToInt32(colValue)
});
}
}
}
}
}
else if (refID == DisplayType.List || refID == DisplayType.Table)
{
CheckReference(new ExportDataRecords()
{
Record_ID = refVID, AD_Table_ID = MTable.Get_Table_ID("AD_Reference")
}, "AD_Reference");
}
else
{
;
}
}
}
} //c
}
} //exception table #if
}
//else
//{
//}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, ex.Message);
return(ex.Message.ToString());
}
return("");
}
public ActivityInfo GetActivityInfo(int activityID, int nodeID, int wfProcessID, Ctx ctx)
{
ActivityInfo info = new ActivityInfo();
try
{
MWFNode node = new MWFNode(ctx, nodeID, null);
info.NodeAction = node.GetAction();
info.NodeName = node.GetName();
if (MWFNode.ACTION_UserChoice.Equals(node.GetAction()))
{
MColumn col = node.GetColumn();
info.ColID = col.GetAD_Column_ID();
info.ColReference = col.GetAD_Reference_ID();
info.ColReferenceValue = col.GetAD_Reference_Value_ID();
info.ColName = col.GetColumnName();
}
else if (MWFNode.ACTION_UserWindow.Equals(node.GetAction()))
{
info.AD_Window_ID = node.GetAD_Window_ID();
MWFActivity activity = new MWFActivity(ctx, activityID, null);
info.KeyCol = activity.GetPO().Get_TableName() + "_ID";
}
else if (MWFNode.ACTION_UserForm.Equals(node.GetAction()))
{
info.AD_Form_ID = node.GetAD_Form_ID();
}
string sql = @"SELECT node.ad_wf_node_ID,
node.Name AS NodeName,
usr.Name AS UserName,
wfea.wfstate,
wfea.TextMsg
FROM ad_wf_eventaudit wfea
INNER JOIN Ad_WF_Node node
ON (node.Ad_Wf_node_ID=wfea.AD_Wf_Node_id)
INNER JOIN AD_User usr
ON (usr.Ad_User_ID =wfea.ad_User_ID)
WHERE wfea.AD_WF_Process_ID=" + wfProcessID + @"
Order By wfea.ad_wf_eventaudit_id desc";
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
List <NodeInfo> nodeInfo = new List <NodeInfo>();
List <int> nodes = new List <int>();
NodeInfo ni = null;
NodeHistory nh = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (!nodes.Contains(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"])))
{
ni = new NodeInfo();
ni.Name = Util.GetValueOfString(ds.Tables[0].Rows[i]["NodeName"]);
nh = new NodeHistory();
nh.State = Util.GetValueOfString(ds.Tables[0].Rows[i]["WFState"]);
nh.ApprovedBy = Util.GetValueOfString(ds.Tables[0].Rows[i]["UserName"]);
ni.History = new List <NodeHistory>();
if (ds.Tables[0].Rows[i]["TextMsg"] == null || ds.Tables[0].Rows[i]["TextMsg"] == DBNull.Value)
{
nh.TextMsg = string.Empty;
}
else
{
nh.TextMsg = ds.Tables[0].Rows[i]["TextMsg"].ToString();
}
ni.History.Add(nh);
nodes.Add(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"]));
nodeInfo.Add(ni);
}
else
{
int index = nodes.IndexOf(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"]));
nh = new NodeHistory();
nh.State = Util.GetValueOfString(ds.Tables[0].Rows[i]["WFState"]);
nh.ApprovedBy = Util.GetValueOfString(ds.Tables[0].Rows[i]["UserName"]);
if (ds.Tables[0].Rows[i]["TextMsg"] == null || ds.Tables[0].Rows[i]["TextMsg"] == DBNull.Value)
{
nh.TextMsg = string.Empty;
}
else
{
nh.TextMsg = ds.Tables[0].Rows[i]["TextMsg"].ToString();
}
nodeInfo[index].History.Add(nh);
}
}
info.Node = nodeInfo;
}
return(info);
}
catch
{
return(info);
}
}
}// 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
if (column.IsVirtualColumn())
{
return("@IsVirtualColumn@");
}
int noColumns = 0;
string sql = CommonFunctions.SyncColumn(table, column, out noColumns);
//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(noColumns);
// Change here for Master Data Versioning
bool hasMainVerCol = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + column.GetAD_Table_ID() + " AND IsActive ='Y' AND IsMaintainVersions = 'Y'", null, Get_Trx())) > 0;
// check whether there are any columns in the table
// marked as "Maintain Versions", then proceed else return
if (!hasMainVerCol)
{
hasMainVerCol = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + column.GetAD_Table_ID(), null, Get_Trx())) == "Y";
}
string versionMsg = "";
if (hasMainVerCol)
{
// call CreateVersionInfo function of MasterVersions class
// to create version table and all columns
MasterVersions mv = new MasterVersions();
versionMsg = mv.CreateVersionInfo(column.GetAD_Column_ID(), column.GetAD_Table_ID(), Get_Trx());
}
return(sql + "; " + r + "; " + versionMsg);
} // doIt
} // prepare
/**
* Process
* @return info
* @throws Exception
*/
protected override String DoIt()// throws Exception
{
log.Info("AD_Column_ID=" + p_AD_Column_ID
+ ", IsEncrypted=" + p_IsEncrypted
+ ", ChangeSetting=" + p_ChangeSetting
+ ", MaxLength=" + p_MaxLength);
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
{
throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
}
//
String columnName = column.GetColumnName();
int dt = column.GetAD_Reference_ID();
// Can it be enabled?
if (column.IsKey() ||
column.IsParent() ||
column.IsStandardColumn() ||
column.IsVirtualColumn() ||
column.IsIdentifier() ||
column.IsTranslated() ||
DisplayType.IsLookup(dt) ||
DisplayType.IsLOB(dt) ||
"DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
if (column.IsEncrypted())
{
column.SetIsEncrypted(false);
column.Save(Get_Trx());
}
return(columnName + ": cannot be encrypted");
}
// Start
AddLog(0, null, null, "Encryption Class = " + SecureEngine.GetClassName());
bool error = false;
// Test Value
if (p_TestValue != null && p_TestValue.Length > 0)
{
String encString = SecureEngine.Encrypt(p_TestValue);
AddLog(0, null, null, "Encrypted Test Value=" + encString);
String clearString = SecureEngine.Decrypt(encString);
if (p_TestValue.Equals(clearString))
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (same as test value)");
}
else
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (NOT the same as test value - check algorithm)");
error = true;
}
int encLength = encString.Length;
AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
// Length Test
if (p_MaxLength != 0)
{
String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
while (testClear.Length < p_MaxLength)
{
testClear += testClear;
}
testClear = testClear.Substring(0, p_MaxLength);
log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
//
String encString = SecureEngine.Encrypt(testClear);
int encLength = encString.Length;
AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
if (p_IsEncrypted != column.IsEncrypted())
{
if (error || !p_ChangeSetting)
{
AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
}
else
{
column.SetIsEncrypted(p_IsEncrypted);
if (column.Save(Get_Trx()))
{
AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
}
else
{
AddLog(0, null, null, "Save Error");
}
}
}
if (p_IsEncrypted == column.IsEncrypted() && !error) // Done By Karan on 10-nov-2016, to encrypt/decrypt passwords according to settings.
{
//object colID = DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID =(SELECT AD_Table_ID From AD_Table WHERE TableName='AD_User') AND ColumnName='Password'", null, Get_Trx());
// if (colID != null && colID != DBNull.Value && Convert.ToInt32(colID) == column.GetAD_Column_ID())
//{
string tableName = MTable.GetTableName(GetCtx(), column.GetAD_Table_ID());
DataSet ds = DB.ExecuteDataset("SELECT " + column.GetColumnName() + "," + tableName
+ "_ID FROM " + tableName, null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (p_IsEncrypted)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
!SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
{
//MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID()) + "_ID"]), Get_Trx());
//user.SetPassword(SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()));
int encLength = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()).Length;
if (encLength <= column.GetFieldLength())
{
//PO tab = MTable.GetPO(GetCtx(), tableName,
// Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());
//tab.Set_Value(column.GetColumnName(), (SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
//if (!tab.Save(Get_Trx()))
//{
// Rollback();
// return "Encryption=" + false;
//}
string p_NewPassword = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
}
sql += " WHERE " + tableName + "_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes <= 0)
{
Rollback();
return("Encryption=" + false);
}
}
else
{
Rollback();
return("After Encryption some values may exceed the value of column length. Please exceed column Length.");
}
}
}
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
{
// MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID())+"_ID"]), Get_Trx());
//PO tab = MTable.GetPO(GetCtx(), tableName,
// Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());
//tab.Set_Value(column.GetColumnName(), (SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
//if (!tab.Save(Get_Trx()))
//{
// Rollback();
// return "Encryption=" + false;
//}
string p_NewPassword = SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
}
sql += " WHERE " + tableName + "_ID =" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes <= 0)
{
Rollback();
return("Encryption=" + false);
}
}
}
}
}
//}
}
return("Encryption=" + column.IsEncrypted());
}
} // 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
/// <summary>
/// Create default columns for Master Data Version Table
/// e.g. Processed, Processing, IsApproved etc.
/// </summary>
/// <param name="Ver_AD_Table_ID"></param>
/// <returns></returns>
private string CreateDefaultVerCols(int Ver_AD_Table_ID)
{
DataSet dstblCols = DB.ExecuteDataset("SELECT ColumnName FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, null);
for (int i = 0; i < listDefVerCols.Count; i++)
{
bool hasCol = false;
if (dstblCols != null && dstblCols.Tables[0].Rows.Count > 0)
{
DataRow[] dr = dstblCols.Tables[0].Select("ColumnName = '" + listDefVerCols[i] + "'");
if (dr != null && dr.Length > 0)
{
hasCol = true;
}
}
if (hasCol)
{
continue;
}
MColumn colVer = new MColumn(GetCtx(), 0, _trx);
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
colVer.SetColumnName(listDefVerCols[i]);
colVer.SetAD_Element_ID(_listDefVerElements[i]);
colVer.SetAD_Reference_ID(listDefVerRef[i]);
//if (listDefVerCols[i] == "VersionValidFrom")
// colVer.SetIsParent(true);
if (listDefVerRef[i] == 10)
{
colVer.SetFieldLength(10);
}
if (listDefVerRef[i] == 14)
{
colVer.SetFieldLength(2000);
}
if (listDefVerRef[i] == 13)
{
colVer.SetIsKey(true);
colVer.SetIsMandatory(true);
colVer.SetIsMandatoryUI(true);
}
if (!colVer.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 Version Column " + listDefVerCols[i];
}
log.Log(Level.SEVERE, "Version Column not created :: " + listDefVerCols[i] + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
else
{
oldVerCol = colVer.GetAD_Column_ID();
}
}
return("");
}
}// 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);
}
/// <summary>
/// Evaluate Condition
/// </summary>
/// <param name="activity">activity</param>
/// <returns>true if true</returns>
public bool Evaluate(MWFActivity activity)
{
AD_WF_Activity_ID = activity.GetAD_WF_Activity_ID();
if (GetAD_Column_ID() == 0)
{
//throw new IllegalStateException("No Column defined - " + this);
throw new Exception("No Column defined - " + this);
}
PO po = activity.GetPO();
if (po == null || po.Get_ID() == 0)
{
//throw new IllegalStateException("Could not evaluate " + po + " - " + this);
throw new Exception("Could not evaluate " + po + " - " + this);
}
//
Object valueObj = po.Get_ValueOfColumn(GetAD_Column_ID());
if (valueObj == null)
{
valueObj = "";
}
String value1 = GetValue();
if (value1 == null)
{
value1 = "";
}
String value2 = GetValue2();
if (value2 == null)
{
value2 = "";
}
// If column is of bool type and user insert y or n in condition, then convert them to true or false to match with value got from PO.
if (MColumn.Get(GetCtx(), GetAD_Column_ID()).GetAD_Reference_ID().Equals(DisplayType.YesNo))
{
if (value1.ToLower().Equals("y"))
{
value1 = "true";
}
else if (value1.ToLower().Equals("n"))
{
value1 = "false";
}
if (value2.ToLower().Equals("y"))
{
value2 = "true";
}
else if (value2.ToLower().Equals("n"))
{
value2 = "false";
}
}
String resultStr = "PO:{" + valueObj + "} " + GetOperation() + " Condition:{" + value1 + "}";
if (GetOperation().Equals(OPERATION_Sql))
{
throw new ArgumentException("SQL Operator not implemented yet: " + resultStr);
}
if (GetOperation().Equals(OPERATION_X))
{
resultStr += "{" + value2 + "}";
}
bool result = false;
//Lakhwinder
if (MColumn.Get(GetCtx(), GetAD_Column_ID()).GetColumnName().ToUpper().Equals("C_GENATTRIBUTESETINSTANCE_ID"))
{
return(EvaluateAttributeCondition(po));
}
//if (valueObj instanceof Number)
if (valueObj != null && CommonFunctions.IsNumeric(valueObj.ToString()))
{
result = CompareNumber(valueObj, value1, value2);
}
else
{
result = CompareString(valueObj, value1, value2);
}
//
log.Fine(resultStr + " -> " + result + (_numeric ? " (#)" : " ($)"));
return(result);
}
} // doIt
/// <summary>
/// Create + execute Statement
/// </summary>
/// <param name="cLog">log</param>
/// <param name="trxName">trx</param>
private void CreateStatement(MChangeLog cLog, Trx trxName)
{
// New Table
if (_table != null)
{
if (cLog.GetAD_Table_ID() != _table.GetAD_Table_ID())
{
ExecuteStatement();
_table = null;
}
}
if (_table == null)
{
_table = new MTable(GetCtx(), cLog.GetAD_Table_ID(), trxName);
}
// New Record
if (_sqlUpdate != null &&
(cLog.GetRecord_ID() != _oldRecord_ID ||
!cLog.GetRecord2_ID().Equals(_oldRecord2_ID)))
{
ExecuteStatement();
}
// Column Info
_column = new MColumn(GetCtx(), cLog.GetAD_Column_ID(), Get_Trx());
// Same Column twice
if (_columns.Contains(_column.GetColumnName()))
{
ExecuteStatement();
}
_columns.Add(_column.GetColumnName());
// Create new Statement
if (_sqlUpdate == null)
{
String tableName = _table.GetTableName();
_keyColumn = _table.GetTableName() + "_ID";
if (tableName.Equals("AD_Ref_Table"))
{
_keyColumn = "AD_Reference_ID";
}
//
_sqlUpdate = new StringBuilder("UPDATE ")
.Append(tableName)
.Append(" SET ");
// Key
_sqlUpdateWhere = new StringBuilder(" WHERE ");
if (cLog.GetRecord_ID() != 0)
{
_sqlUpdateWhere.Append(_keyColumn).Append("=").Append(cLog.GetRecord_ID());
}
else
{
_sqlUpdateWhere.Append(cLog.GetRecord2_ID());
}
_oldRecord_ID = cLog.GetRecord_ID();
_oldRecord2_ID = cLog.GetRecord2_ID();
// Insert - new value is null and UnDo only
_isInsert = cLog.IsNewNull() && _CheckNewValue != null;
if (_isInsert)
{
_sqlInsert = new StringBuilder("INSERT INTO ")
.Append(tableName).Append("(");
_sqlInsertValue = new StringBuilder(") VALUES (");
if (cLog.GetRecord_ID() != 0)
{
_sqlInsert.Append(_keyColumn);
_sqlInsertValue.Append(cLog.GetRecord_ID());
if (!_keyColumn.Equals(_column.GetColumnName()))
{
_sqlInsert.Append(",").Append(_column.GetColumnName());
_sqlInsertValue.Append(",").Append(GetSQLValue(cLog.GetOldValue()));
}
}
}
_numberColumns = 1;
}
// Just new Column
else
{
_sqlUpdate.Append(", ");
// Insert
if (_isInsert)
{
_isInsert = cLog.IsNewNull();
}
if (_isInsert && !_keyColumn.Equals(_column.GetColumnName()))
{
_sqlInsert.Append(",").Append(_column.GetColumnName());
_sqlInsertValue.Append(",").Append(GetSQLValue(cLog.GetOldValue()));
}
_numberColumns++;
}
// Update Set clause -- columnName=value
_sqlUpdate.Append(_column.GetColumnName())
.Append("=");
// UnDo a <- (b)
if (_CheckNewValue != null)
{
_sqlUpdate.Append(GetSQLValue(cLog.GetOldValue()));
//if (_CheckNewValue.booleanValue())
if (Utility.Util.GetValueOfBool(_CheckNewValue))
{
_sqlUpdateWhere.Append(" AND ").Append(_column.GetColumnName());
String newValue = GetSQLValue(cLog.GetNewValue());
if (newValue == null || "NULL".Equals(newValue))
{
_sqlUpdateWhere.Append(" IS NULL");
}
else
{
_sqlUpdateWhere.Append("=").Append(newValue);
}
}
}
// ReDo (a) -> b
else if (_CheckOldValue != null)
{
_sqlUpdate.Append(GetSQLValue(cLog.GetNewValue()));
//if (_CheckOldValue.booleanValue())
if (Utility.Util.GetValueOfBool(_CheckOldValue))
{
String newValue = GetSQLValue(cLog.GetOldValue());
_sqlUpdateWhere.Append(" AND ").Append(_column.GetColumnName());
if (newValue == null || "NULL".Equals(newValue))
{
_sqlUpdateWhere.Append(" IS NULL");
}
else
{
_sqlUpdateWhere.Append("=").Append(newValue);
}
}
}
} // createStatement
/// <summary>
/// Process
/// throws Exception
/// </summary>
/// <returns>info</returns>
override protected string DoIt()
{
//IDbTransaction trx = ExecuteQuery.GerServerTransaction();
MTab tab = new MTab(GetCtx(), p_AD_Tab_ID, Get_Trx());
//MTab tab = new MTab(GetCtx(), p_AD_Tab_ID, trx.ToString());
if (p_AD_Tab_ID == 0 || tab == null || tab.Get_ID() == 0)
{
throw new Exception("@NotFound@: @AD_Tab_ID@ " + p_AD_Tab_ID);
}
//log.info(tab.toString());
int count = 0;
string sql = "SELECT * FROM AD_Column c "
+ "WHERE NOT EXISTS (SELECT * FROM AD_Field f "
+ "WHERE c.AD_Column_ID=f.AD_Column_ID"
+ " AND c.AD_Table_ID=@AD_Table_Id" // #1
+ " AND f.AD_Tab_ID=@AD_Tab_Id)" // #2
+ " AND AD_Table_ID=@AD_Table_Id1" // #3
+ " AND NOT (Name LIKE 'Created%' OR Name LIKE 'Updated%')"
+ " AND IsActive='Y' "
+ "ORDER BY Name desc";
try
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@AD_Table_Id", tab.GetAD_Table_ID());
param[1] = new SqlParameter("@AD_Tab_Id", tab.GetAD_Tab_ID());
param[2] = new SqlParameter("@AD_Table_Id1", tab.GetAD_Table_ID());
DataSet ds = DataBase.DB.ExecuteDataset(sql, param, Get_Trx());
//DataSet ds1 = ExecuteQuery.ExecuteDataset(sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
//MColumn column = new MColumn (getCtx(),dr, Get_Trx());
//MColumn column = new MColumn(GetCtx(), dr, null);
MColumn column = new MColumn(GetCtx(), dr, Get_Trx());
//
MField field = new MField(tab);
field.SetColumn(column);
if (column.IsKey())
{
field.SetIsDisplayed(false);
}
if (column.GetColumnName().ToString() == "AD_Client_ID")
{
field.SetSeqNo(10);
}
if (column.GetColumnName().ToString() == "AD_Org_ID")
{
field.SetIsSameLine(true);
field.SetSeqNo(20);
}
//Export_ID Check [Hide Export Field]
if (column.GetColumnName().ToString() == "Export_ID")
{
field.SetIsDisplayed(false);
}
if (field.Save())
{
AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), column.GetName());
//AddLog(0, DateTime.MinValue, null, );
count++;
}
}
ds = null;
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
}
return("@Created@ #" + count);
}
} // doIt
private String createFK()
{
String returnMessage = "";
if (p_AD_Column_ID == 0)
{
throw new Exception("@No@ @AD_Column_ID@");
}
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());
}
String fk;
if ((column.GetAD_Reference_ID() == DisplayType.Account) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=1014)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=25 AND C.AD_Column_ID= @param" // Acct
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if ((column.GetAD_Reference_ID() == DisplayType.PAttribute) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=8472)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=35 AND C.AD_Column_ID=@param" // Product Attribute
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if (((column.GetAD_Reference_ID() == DisplayType.TableDir) || (column.GetAD_Reference_ID() == DisplayType.Search)) &&
(column.GetAD_Reference_Value_ID() == 0))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (c.AD_Element_ID=cPK.AD_Element_ID AND cPK.IsKey='Y')"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID AND tPK.IsView='N') "
+ "WHERE c.IsKey='N' AND c.AD_Reference_Value_ID IS NULL AND C.AD_Column_ID=@param"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL "
+ " ORDER BY t.TableName, c.ColumnName";
}
else // Table
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID AND c.AD_Reference_Value_ID IS NOT NULL)"
+ " INNER JOIN AD_Ref_Table rt ON (c.AD_Reference_Value_ID=rt.AD_Reference_ID)"
+ " INNER JOIN AD_Column cPK ON (rt.Column_Key_ID=cPK.AD_Column_ID)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N'"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL AND C.AD_Column_ID=@param"
+ " ORDER BY t.TableName, c.ColumnName";
}
SqlParameter[] pstmt = null;
DataSet ds = null;
try
{
/*Find foreign key relation in Database
* */
//Trx trx = Trx.Get("getDatabaseMetaData");
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";// DB.getCatalog();
String schema = DB.GetSchema();
String tableName = table.GetTableName();
String dropsql = null;
int no = 0;
String constraintNameDB = null;
String PKTableNameDB = null;
String PKColumnNameDB = null;
int constraintTypeDB = 0;
/* Get foreign key information from DatabaseMetadata
* */
ds = md.GetForeignKeys(catalog, schema, tableName);
md.Dispose();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Dictionary <String, String> fkcolumnDetail = md.GetForeignColumnDetail(ds.Tables[0].Rows[i]);
// string sql = "SELECT column_name FROM user_cons_columns WHERE constraint_name='" + ds.Tables[0].Rows[i]["FOREIGN_KEY_CONSTRAINT_NAME"].ToString() + "'";
//string fkcolumnName = Util.GetValueOfString(DB.ExecuteScalar(sql));
if (fkcolumnDetail["FK_Column_Name"].Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
constraintNameDB = fkcolumnDetail["ConstraintNameDB"];
PKTableNameDB = fkcolumnDetail["PK_Table_Name"]; //rs.GetString("PKTABLE_NAME");
PKColumnNameDB = fkcolumnDetail["PK_Column_Name"]; //rs.GetString("PKCOLUMN_NAME");
constraintTypeDB = md.GetConstraintTypeDB(fkcolumnDetail["Delete_Rule"]); //rs.getShort("DELETE_RULE");
break;
}
}
}
pstmt = new SqlParameter[1];
pstmt[0] = new SqlParameter("@param", column.Get_ID());
ds = DB.ExecuteDataset(fk, pstmt, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
String TableName = ds.Tables[0].Rows[0]["TableName"].ToString();
String ColumnName = ds.Tables[0].Rows[0]["ColumnName"].ToString();
// int AD_Column_ID = rs.getInt (3);
// int PK_Column_ID = rs.getInt (4);
String PKTableName = ds.Tables[0].Rows[0]["TableName1"].ToString();
String PKColumnName = ds.Tables[0].Rows[0]["ColumnName1"].ToString();
String ConstraintType = ds.Tables[0].Rows[0]["ConstraintType"].ToString();
String ConstraintName = ds.Tables[0].Rows[0]["ConstraintName"].ToString();
/* verify if the constraint in DB is different than the one to be created */
Boolean modified = true;
if (constraintNameDB != null)
{
if (((constraintNameDB.Equals(ConstraintName, StringComparison.OrdinalIgnoreCase)) &&
(PKTableNameDB != null) && (PKTableNameDB.Equals(PKTableName, StringComparison.OrdinalIgnoreCase)) &&
(PKColumnNameDB != null) && (PKColumnNameDB.Equals(PKColumnName, StringComparison.OrdinalIgnoreCase)) &&
((constraintTypeDB == DatabaseMetaData.importedKeyRestrict) &&
(X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))))
||
((constraintTypeDB == DatabaseMetaData.importedKeyCascade) &&
(X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType)))
||
((constraintTypeDB == DatabaseMetaData.importedKeySetNull) &&
(X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType)))
)
{
modified = false;
}
else
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
}
}
if (modified)
{
StringBuilder sql = null;
try
{
if (dropsql != null)
{
/* Drop the existing constraint */
//no = DB.executeUpdate(Get_Trx(), dropsql);
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
}
/* Now create the sql foreign key constraint */
sql = new StringBuilder("ALTER TABLE ")
.Append(TableName)
.Append(" ADD CONSTRAINT ").Append(ConstraintName)
.Append(" FOREIGN KEY (").Append(ColumnName)
.Append(") REFERENCES ").Append(PKTableName)
.Append(" (").Append(PKColumnName).Append(")");
Boolean createfk = true;
if (!String.IsNullOrEmpty(ConstraintType))
{
if (X_AD_Column.CONSTRAINTTYPE_DoNOTCreate.Equals(ConstraintType))
{
createfk = false;
}
else if (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))
{
;
}
else if (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE CASCADE");
}
else if (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_NullTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE SET NULL");
}
}
else
{
createfk = false;
}
/* Create the constraint */
if (createfk)
{
// no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx(), false, true);
AddLog(0, null, Decimal.Parse(no.ToString()), sql.ToString());
if (no != -1)
{
log.Finer(ConstraintName + " - " + TableName + "." + ColumnName);
returnMessage = sql.ToString();
}
else
{
log.Info(ConstraintName + " - " + TableName + "." + ColumnName
+ " - ReturnCode=" + no);
returnMessage = "FAILED:" + sql.ToString();
}
} //if createfk
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString() + " - " + e.ToString());
}
} // modified
} // rs.next
else
{
if (constraintNameDB != null && constraintNameDB.Equals("FK" + column.GetAD_Table_ID() + "_" + p_AD_Column_ID, StringComparison.OrdinalIgnoreCase))
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
/* Drop the existing constraint */
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
returnMessage = dropsql.ToString();
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, fk, e);
}
return(returnMessage);
}
public RefVector4f this[MColumn col] => ColumnVectorRef((int)col);
