/// <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"));
}
} // executeStatement
/// <summary>
/// Set Customization Flag
/// </summary>
/// <returns>summary</returns>
private String SetCustomization()
{
log.Info("");
String sql = "UPDATE AD_ChangeLog SET IsCustomization='N' WHERE IsCustomization='Y'";
int resetNo = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());
int updateNo = 0;
// Get Tables
sql = "SELECT * FROM AD_Table t "
// Table with EntityType
+ "WHERE EXISTS (SELECT * FROM AD_Column c "
+ "WHERE t.AD_Table_ID=c.AD_Table_ID AND c.ColumnName='EntityType')"
// Changed Tables
+ " AND EXISTS (SELECT * FROM AD_ChangeLog l "
+ "WHERE t.AD_Table_ID=l.AD_Table_ID)";
StringBuilder update = null;
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql, Get_Trx());
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql, null, Get_Trx());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
MTable table = new MTable(GetCtx(), dr, Get_Trx());
String tableName = table.GetTableName();
String columnName = tableName + "_ID";
if (tableName.Equals("AD_Ref_Table"))
{
columnName = "AD_Reference_ID";
}
update = new StringBuilder("UPDATE AD_ChangeLog SET IsCustomization='Y' "
+ "WHERE AD_Table_ID=").Append(table.GetAD_Table_ID());
update.Append(" AND Record_ID IN (SELECT ")
.Append(columnName)
.Append(" FROM ").Append(tableName)
.Append(" WHERE EntityType IN ('D','C'))");
int no = DataBase.DB.ExecuteQuery(update.ToString(), null, Get_Trx());
log.Config(table.GetTableName() + " = " + no);
updateNo += no;
}
dt = null;
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
if (dt != null)
{
dt = null;
}
log.Log(Level.SEVERE, sql + " --- " + update, e);
}
return("@Reset@: " + resetNo + " - @Updated@: " + updateNo);
} // setCustomization
/// <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("");
}
} // 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
