/// <summary>
/// Process logic to verify database columns and Columns in Application Dictionary
/// </summary>
/// <returns></returns>
protected override string DoIt()
{
// if table not found, in case if run from menu
// or from table, then return with message
if (p_AD_Table_ID.Trim() == "")
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotSelected"));
}
DataSet dsDT = DB.ExecuteDataset("SELECT AD_Reference_ID, Name FROM AD_Reference WHERE IsActive = 'Y' AND ValidationType = 'D'");
string[] tableIDs = p_AD_Table_ID.Split(',');
// loop through tables selected in parameter or
// if run from window then single table ID
for (int i = 0; i < tableIDs.Length; i++)
{
int AD_Table_ID = Util.GetValueOfInt(tableIDs[i]);
MTable table = MTable.Get(GetCtx(), AD_Table_ID);
// Check on table whether it has single or multiple keys
bool hasSingleKey = true;
if (!table.IsSingleKey())
{
hasSingleKey = false;
}
if (table == null || table.Get_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotFound"));
}
// create HTML for tables
sbHTML.Append("<div class='vis-val-tc-parCtr'> "
+ "<div class='vis-val-tc-hdr'><label>" + Msg.Translate(GetCtx(), "AD_Table_ID") + ": </label>" + table.GetTableName() + "</div>"
+ "<div class='vis-val-tc-colCtr'>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_ADCols") + "</div>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_DBCols") + "</div>"
+ "</div>");
// Find Column in Database
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
bool hasDBCols = false;
if (dt.Tables[0].Rows.Count > 0)
{
hasDBCols = true;
}
// get all columns from table
MColumn[] columnsAD = table.GetColumns(true);
// variables to create HTML as string for both AD and in DB
StringBuilder adColName = new StringBuilder("");
StringBuilder dbColName = new StringBuilder("");
List <string> ADCols = new List <string>();
// check whether process is running from menu or from table and column Window
// if process is executing from window then apply style
string mainMenuWrap = "";
if (!fromMenu)
{
mainMenuWrap = "style='max-height: 450px;'";
}
sbHTML.Append("<div class='vis-val-tc-mainwrap' " + mainMenuWrap + "> ");
if (columnsAD.Length > 0)
{
// loop through Columns present in Application dictionary
for (int c = 0; c < columnsAD.Length; c++)
{
var col = columnsAD[c];
adColName.Clear();
adColName.Append(col.GetColumnName().ToUpper());
DataRow[] dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Select("COLUMN_NAME = '" + adColName.ToString() + "'");
}
DataRow[] drRef = dsDT.Tables[0].Select("AD_Reference_ID = " + col.GetAD_Reference_ID());
// if column is virtual, then add style
var style = " color: red; font-style: italic;";
if (col.IsVirtualColumn() || (dr != null && dr.Length > 0))
{
style = " color: green;";
}
// add different style for key column
string keyCol = "";
// Condition for Multikey columns and Single key to mark as Primary key columns
if ((hasSingleKey && col.GetAD_Reference_ID() == 13) || (!hasSingleKey && col.IsParent()))
{
keyCol = " * ";
style += " font-weight: bold; font-size: initial;";
}
sbHTML.Append("<div class='vis-val-tc-colWid'>"
+ "<div class='vis-val-tc-col-l' style='" + style + "'> " + keyCol + " " + col.GetColumnName() + " (" + drRef[0]["Name"] + " (" + col.GetFieldLength() + ") " + " ) " + "</div>");
if (dr != null && dr.Length > 0)
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Util.GetValueOfString(dr[0]["Column_Name"]) + " (" + Util.GetValueOfString(dr[0]["DATATYPE"]) + " (" + Util.GetValueOfInt(dr[0]["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
if (col.IsVirtualColumn())
{
sbHTML.Append("<div class='vis-val-tc-virCol' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_VirtualCol") + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_DBNotFound") + "</div>");
}
}
sbHTML.Append("</div>");
ADCols.Add(adColName.ToString());
}
}
// if columns present in database
if (hasDBCols)
{
// loop through columns in DB
for (int d = 0; d < dt.Tables[0].Rows.Count; d++)
{
dbColName.Clear();
dbColName.Append(dt.Tables[0].Rows[d]["Column_Name"]);
if (ADCols.Contains(dbColName.ToString()))
{
continue;
}
else
{
sbHTML.Append("<div class='vis-val-tc-colWid'>");
DataRow dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Rows[d];
}
if (dr != null)
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Util.GetValueOfString(dr["Column_Name"]) + " (" + Util.GetValueOfString(dr["DATATYPE"]) + " (" + Util.GetValueOfInt(dr["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
}
sbHTML.Append("<div class='vis-val-tc-col-red-l'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
sbHTML.Append("</div>");
}
}
}
sbHTML.Append("</div>");
sbHTML.Append("</div>");
md.Dispose();
ProcessInfo pi = GetProcessInfo();
pi.SetCustomHTML(sbHTML.ToString());
}
return("");
}
/// <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"));
}
/// <summary>
/// Function to update data in Master Table based on the versions saved
/// </summary>
/// <param name="dsRec">All Records which need to be updated</param>
/// <param name="TableName">Version Table Name</param>
/// <param name="VerTableID">Version Table ID</param>
/// <returns>True/False based on the Updation of data in parent table</returns>
private bool UpdateRecords(DataSet dsRec, string TableName, int VerTableID)
{
// Get Master table name from Version table
string BaseTblName = TableName;
if (TableName.EndsWith("_Ver"))
{
BaseTblName = BaseTblName.Substring(0, TableName.Length - 4);
}
// Master Table ID from Table name
int BaseTableID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = '" + BaseTblName + "'"));
// Get Column information of Master Table
DataSet dsDBColNames = DB.ExecuteDataset("SELECT ColumnName, AD_Reference_ID, IsUpdateable, IsAlwaysUpdateable FROM AD_Column WHERE AD_Table_ID = " + BaseTableID);
if (dsDBColNames != null && dsDBColNames.Tables[0].Rows.Count > 0)
{
log.Info("Processing for " + TableName + " :: ");
StringBuilder sqlSB = new StringBuilder("");
bool recordProcessed = false;
// create object of Master Table
MTable tbl = new MTable(GetCtx(), BaseTableID, null);
// create object of Version table
MTable tblVer = new MTable(GetCtx(), VerTableID, null);
// check whether master table has Single key
bool isSingleKey = tbl.IsSingleKey();
// List of records which were not processed by process in case of any error
List <string> keys = new List <string>();
StringBuilder sbKey = new StringBuilder("");
// Loop through the records which need to be updated on Master Table
for (int i = 0; i < dsRec.Tables[0].Rows.Count; i++)
{
DataRow dr = dsRec.Tables[0].Rows[i];
sbKey.Clear();
PO poDest = null;
PO poSource = tblVer.GetPO(GetCtx(), dr, null);
// if table has single key
if (isSingleKey)
{
// Create object of PO Class from TableName and Record ID
poDest = MTable.GetPO(GetCtx(), BaseTblName, Util.GetValueOfInt(dr[BaseTblName + "_ID"]), null);
sbKey.Append(Util.GetValueOfInt(dr[BaseTblName + "_ID"]));
}
else
{
// Create object of PO Class from combination of key columns
string[] keyCols = tbl.GetKeyColumns();
StringBuilder whereCond = new StringBuilder("");
for (int w = 0; w < keyCols.Length; w++)
{
if (w == 0)
{
if (keyCols[w] != null)
{
whereCond.Append(keyCols[w] + " = " + poSource.Get_Value(keyCols[w]));
}
else
{
whereCond.Append(" NVL(" + keyCols[w] + ",0) = 0");
}
}
else
{
if (keyCols[w] != null)
{
whereCond.Append(" AND " + keyCols[w] + " = " + poSource.Get_Value(keyCols[w]));
}
else
{
whereCond.Append(" AND NVL(" + keyCols[w] + ",0) = 0");
}
}
}
poDest = tbl.GetPO(GetCtx(), whereCond.ToString(), null);
sbKey.Append(whereCond);
}
// check if there is any error in processing record, then continue and do not process next versions
if (keys.Contains(sbKey.ToString()))
{
continue;
}
// Check whether Master Table contains "Processing" Column
if (poDest.Get_ColumnIndex("Processing") >= 0)
{
// if "Processing" column found then return, do not update in Master Table,
// because transaction might be in approval process
if (Util.GetValueOfString(poSource.Get_Value("Processing")) == "Y")
{
keys.Add(sbKey.ToString());
continue;
}
}
// Check whether Master Table contains "Processed" Column
if (poDest.Get_ColumnIndex("Processed") >= 0)
{
if (Util.GetValueOfString(poSource.Get_Value("Processed")) == "Y")
{
recordProcessed = true;
}
}
// set client and Organization ID from Version table to Master
// as copy PO set these ID's as 0
poDest.SetAD_Client_ID(poSource.GetAD_Client_ID());
poDest.SetAD_Org_ID(poSource.GetAD_Org_ID());
StringBuilder sbColName = new StringBuilder("");
// Loop through all the columns in Master Table
for (int j = 0; j < dsDBColNames.Tables[0].Rows.Count; j++)
{
sbColName.Clear();
// Get Name of Column
sbColName.Append(dsDBColNames.Tables[0].Rows[j]["ColumnName"]);
// check if column exist in Default columns list, in that case do not update and continue to next column
if (defcolNames.Contains(sbColName.ToString()))
{
continue;
}
// No need to update Primary key column, continue to next column
if (sbColName.ToString().Equals(BaseTblName + "_ID"))
{
continue;
}
// if column is of "Yes-No" type i.e. Reference ID is 20 (Fixed) then set True/False
if (Util.GetValueOfInt(dsDBColNames.Tables[0].Rows[j]["AD_Reference_ID"]) == 20)
{
Object val = false;
if (poSource.Get_Value(sbColName.ToString()) != null)
{
val = poSource.Get_Value(sbColName.ToString());
}
poDest.Set_Value(sbColName.ToString(), val);
}
else
{
poDest.Set_ValueNoCheck(sbColName.ToString(), poSource.Get_Value(sbColName.ToString()));
}
// Check if Master record is Processed and Always Updatable is false then check whether any value updated in such column
// if value updated then return false, can't change data in Processed record if it's not Always Updatable
if (recordProcessed && Util.GetValueOfString(dsDBColNames.Tables[0].Rows[j]["IsAlwaysUpdateable"]) == "N")
{
bool upd = poDest.Is_ValueChanged(sbColName.ToString());
if (upd)
{
msg.Append("Can't update " + sbColName.ToString() + " in " + TableName);
log.SaveError("ERROR", "Can not update processed record for column ==>> " + sbColName.ToString());
// Add record to the list of unprocessed records
keys.Add(sbKey.ToString());
continue;
}
}
}
// Save Master Record
if (!poDest.Save())
{
// Add record to the list of unprocessed records
keys.Add(sbKey.ToString());
// Check for Errors
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 updating Version";
}
msg.Append("Save error in " + TableName + " ==>> " + error);
log.SaveError("ERROR", error);
sqlSB.Clear();
sqlSB.Append("UPDATE " + TableName + " SET VersionLog = '" + error + "' WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
int count = DB.ExecuteQuery(sqlSB.ToString(), null, null);
continue;
}
else
{
// Update Version table, Set "ProcessedVersion" to "Y", so that it don't consider when process runs next time
sqlSB.Clear();
// Update against record id in case of Single key, and update Key column in version table as well in case of new record
if (isSingleKey)
{
sqlSB.Append("UPDATE " + TableName + " SET ProcessedVersion = 'Y', " + BaseTblName + "_ID = " + poDest.Get_ID() + " WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
}
// else
else
{
sqlSB.Append("UPDATE " + TableName + " SET ProcessedVersion = 'Y' WHERE " + TableName + "_ID = " + dr[TableName + "_ID"]);
}
int count = DB.ExecuteQuery(sqlSB.ToString(), null, null);
if (count <= 0)
{
log.Info(TableName + " not updated ==>> " + sqlSB.ToString());
}
}
}
}
return(true);
}
