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