private string InsertORUpdateFields(int AD_Tab_ID, MModuleTab mTab)
{
MTab tab = new MTab(GetCtx(), AD_Tab_ID, null);
MField[] fields = tab.GetFields(true, null);
if (fields == null || fields.Length == 0)
{
return(Msg.GetMsg(GetCtx(), "VIS_FieldsNotFound" + " " + tab.GetName()));
}
string sql = "select AD_Field_ID, AD_MOduleField_ID FROM AD_MOduleField where IsActive='Y' AND ad_moduletab_id=" + mTab.GetAD_ModuleTab_ID();
IDataReader idr = DB.ExecuteReader(sql);
DataTable dt = new DataTable();
dt.Load(idr);
idr.Close();
Dictionary <int, int> existingFields = new Dictionary <int, int>();
foreach (DataRow dr in dt.Rows)
{
existingFields[Convert.ToInt32(dr["AD_Field_ID"])] = Convert.ToInt32(dr["AD_MOduleField_ID"]);
}
for (int i = 0; i < fields.Length; i++)
{
if (!fields[i].IsDisplayed())
{
continue;
}
MModuleField mField = null;
if (existingFields.ContainsKey(fields[i].GetAD_Field_ID()))
{
mField = new MModuleField(GetCtx(), existingFields[fields[i].GetAD_Field_ID()], null);
}
else
{
mField = new MModuleField(GetCtx(), 0, null);
mField.SetAD_Field_ID(fields[i].GetAD_Field_ID());
mField.SetAD_ModuleTab_ID(mTab.GetAD_ModuleTab_ID());
}
mField.SetName(fields[i].GetName());
mField.SetDescription(fields[i].GetDescription());
if (mField.Save())
{
}
}
return("");
}
/// <summary>
/// function to create tab against Version Window
/// </summary>
/// <param name="tab">Object of MTab</param>
/// <param name="ver_AD_Window_ID">Version Window ID</param>
/// <param name="Ver_AD_Table_ID">Version Table ID</param>
/// <returns>int (Version Tab ID)</returns>
private int CreateVerTab(MTab tab, int ver_AD_Window_ID, int Ver_AD_Table_ID)
{
MTab verTab = new MTab(GetCtx(), 0, Get_TrxName());
// copy Master table tab to Version tab
tab.CopyTo(verTab);
verTab.SetAD_Window_ID(ver_AD_Window_ID);
verTab.SetAD_Table_ID(Ver_AD_Table_ID);
verTab.SetIsReadOnly(false);
verTab.SetIsInsertRecord(false);
verTab.SetIsSingleRow(true);
verTab.SetWhereClause(null);
verTab.SetSeqNo(10);
verTab.SetAD_Column_ID(0);
verTab.SetTabLevel(0);
verTab.SetExport_ID(null);
verTab.SetDescription("Version tab for " + tab.GetName());
verTab.SetHelp("Version tab for " + tab.GetName() + ", to display versions for current record");
// set order by on Version Window's tab on "Version Valid From column"
verTab.SetOrderByClause("VersionValidFrom DESC, RecordVersion DESC");
if (!verTab.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 Tab";
}
log.Log(Level.SEVERE, "Version Tab not Created :: " + tab.GetName() + " :: " + error);
Get_TrxName().Rollback();
return(0);
}
else
{
CreateVerTabPanel(verTab);
}
return(verTab.GetAD_Tab_ID());
}
/// <summary>
/// Create tab panel for Version window's Tab
/// </summary>
/// <param name="tab"></param>
public void CreateVerTabPanel(MTab tab)
{
int AD_TabPanel_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_TabPanel_ID FROM AD_TabPanel WHERE Classname = '" + className + "' AND AD_Tab_ID = " + tab.GetAD_Tab_ID(), null, null));
if (AD_TabPanel_ID <= 0)
{
MTabPanel tbPnl = new MTabPanel(GetCtx(), 0, Get_TrxName());
tbPnl.SetAD_Client_ID(tab.GetAD_Client_ID());
tbPnl.SetAD_Org_ID(tab.GetAD_Org_ID());
tbPnl.SetAD_Tab_ID(tab.GetAD_Tab_ID());
tbPnl.SetAD_Window_ID(tab.GetAD_Window_ID());
tbPnl.SetClassname(className);
tbPnl.SetName(tab.GetName() + " " + Msg.GetMsg(GetCtx(), "VersionHistory"));
tbPnl.SetIsDefault(true);
tbPnl.SetSeqNo(Util.GetValueOfInt(DB.ExecuteScalar("SELECT (NVL(MAX(SeqNo), 0) + 10) FROM AD_TabPanel WHERE AD_Tab_ID = " + tab.GetAD_Tab_ID(), null, null)));
if (!tbPnl.Save())
{
log.SaveError("MasterVerField", "Tab Panel data not created");
}
}
}
/// <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);
}
/// <summary>
/// process logic (to create master version window and Tab)
/// </summary>
/// <returns></returns>
protected override string DoIt()
{
if (_AD_Tab_ID <= 0)
{
_AD_Tab_ID = GetRecord_ID();
}
if (_AD_Tab_ID <= 0)
{
return(Msg.GetMsg(GetCtx(), "TabNotFound"));
}
int Ver_AD_Window_ID = 0;
int Ver_AD_Tab_ID = 0;
// Created object of MTab with Tab ID (either selected from parameter or from Record ID)
MTab tab = new MTab(GetCtx(), _AD_Tab_ID, Get_TrxName());
if (!(Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + tab.GetAD_Table_ID(), null, Get_TrxName())) == "Y"))
{
// Checked whether there are any columns with the table which is marked as "Maintain Versions"
// if no such column found then return message
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_Column_ID) FROM AD_Column WHERE AD_Table_ID = " + tab.GetAD_Table_ID() + " AND IsMaintainVersions = 'Y'", null, Get_TrxName())) <= 0)
{
return(Msg.GetMsg(GetCtx(), "NoVerColFound"));
}
}
// Always called this function so that in case if new column is added
// to table this function creates new columns to table
CreateMasterVersionTable(0, tab.GetAD_Table_ID());
int Ver_AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = ((SELECT TableName FROM AD_Table WHERE AD_Table_ID = " + tab.GetAD_Table_ID() + ") || '_Ver')"));
if (Ver_AD_Table_ID <= 0)
{
return(Msg.GetMsg(GetCtx(), "VerTableNotFound"));
}
// Get Display Name of Window linked with the tab
string displayName = Util.GetValueOfString(DB.ExecuteScalar("SELECT Name FROM AD_Window WHERE AD_Window_ID = " + tab.GetAD_Window_ID(), null, Get_TrxName())) + " Version";
Ver_AD_Window_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Window_ID FROM AD_Window WHERE Name = '" + displayName + "_" + tab.GetName() + "'", null, Get_TrxName()));
// Check if Version window do not exist, then create new version window
if (Ver_AD_Window_ID <= 0)
{
Ver_AD_Window_ID = CreateVerWindow(displayName, tab.GetName());
// if window not created then return message
if (Ver_AD_Window_ID <= 0)
{
return(Msg.GetMsg(GetCtx(), "VersionWinNotCreated"));
}
else
{
// Update Version Window ID on Version Table
DB.ExecuteQuery("UPDATE AD_Table SET AD_Window_ID = " + Ver_AD_Window_ID + " WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, Get_Trx());
}
}
// check access for Version window
int countAcc = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_Role_ID) FROM AD_Window_Access WHERE AD_Window_ID = " + Ver_AD_Window_ID + " AND IsReadWrite = 'Y'", null, Get_Trx()));
if (countAcc <= 0)
{
// Provide access to Version window, for the roles which parent (Master window) has
countAcc = Util.GetValueOfInt(DB.ExecuteQuery(@"UPDATE AD_Window_Access SET IsReadWrite = 'Y' WHERE AD_Window_ID = " + Ver_AD_Window_ID + @"
AND AD_Role_ID IN (SELECT AD_Role_ID FROM AD_Window_Access WHERE AD_Window_ID = " + tab.GetAD_Window_ID() + @" AND IsReadWrite = 'Y')", null, Get_Trx()));
}
// check for version tab
Ver_AD_Tab_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Tab_ID FROM AD_Tab WHERE AD_Window_ID = " + Ver_AD_Window_ID, null, Get_TrxName()));
// if version tab do not exist, then create new version tab
if (Ver_AD_Tab_ID <= 0)
{
Ver_AD_Tab_ID = CreateVerTab(tab, Ver_AD_Window_ID, Ver_AD_Table_ID);
}
else
{
MTab verTab = new MTab(GetCtx(), Ver_AD_Tab_ID, Get_TrxName());
CreateVerTabPanel(verTab);
}
// if version tab not found then return message
if (Ver_AD_Tab_ID <= 0)
{
return(Msg.GetMsg(GetCtx(), "VersionTabNotCreated"));
}
// Create fields against version Tab
string retMsg = CreateVerFields(tab, Ver_AD_Tab_ID, Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
return(Msg.GetMsg(GetCtx(), "VersionWindowCreated"));
}
/// <summary>
/// Create version fields against version tab
/// </summary>
/// <param name="tab"> Object of MTab </param>
/// <param name="Ver_AD_Tab_ID"> Tab ID of Version window </param>
/// <param name="Ver_AD_Table_ID"> Table ID of Version </param>
/// <returns>string (Message)</returns>
private string CreateVerFields(MTab tab, int Ver_AD_Tab_ID, int Ver_AD_Table_ID)
{
// Get all fields from Master Tab
int[] fields = MTable.GetAllIDs("AD_Field", "AD_Tab_ID = " + tab.GetAD_Tab_ID(), Get_TrxName());
bool hasOrigCols = false;
bool hasVerFields = false;
bool hasVerCols = false;
// Get columns from Master table
DataSet origColDS = DB.ExecuteDataset("SELECT AD_Column_ID, Name,ColumnSql, ColumnName FROM AD_Column WHERE AD_Table_ID = " + tab.GetAD_Table_ID(), null, Get_TrxName());
if (origColDS != null && origColDS.Tables[0].Rows.Count > 0)
{
hasOrigCols = true;
}
// Get fields from Version Tab
DataSet verFieldDS = DB.ExecuteDataset("SELECT f.AD_Column_ID, f.Name, f.AD_Field_ID, (SELECT c.AD_Element_ID FROM AD_Column c WHERE c.AD_Column_ID = f.AD_Column_ID) AS AD_Element_ID FROM AD_Field f WHERE f.AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName());
if (verFieldDS != null && verFieldDS.Tables[0].Rows.Count > 0)
{
hasVerFields = true;
}
// Get Columns from Version Table
DataSet verColumnsDS = DB.ExecuteDataset("SELECT AD_Column_ID, Name, ColumnName FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, Get_TrxName());
if (verColumnsDS != null && verColumnsDS.Tables[0].Rows.Count > 0)
{
hasVerCols = true;
}
StringBuilder sbColName = new StringBuilder("");
foreach (int fld in fields)
{
bool createNew = true;
MField origFld = new MField(GetCtx(), fld, Get_TrxName());
// check if Field already exist for Version Tab else create new
if (hasVerFields)
{
DataRow[] drFld = verFieldDS.Tables[0].Select("Name = '" + origFld.GetName() + "'");
if (drFld.Length > 0)
{
createNew = false;
}
}
// if Field do not exist on Version tab
if (createNew)
{
sbColName.Clear();
int VerColID = 0;
// Get column Info from Column ID of Master Table
DataRow[] drOrigColName = origColDS.Tables[0].Select("AD_Column_ID = " + origFld.GetAD_Column_ID());
if (drOrigColName.Length > 0)
{
if (Util.GetValueOfString(drOrigColName[0]["ColumnSQL"]).Trim() != "")
{
continue;
}
sbColName.Append(Util.GetValueOfString(drOrigColName[0]["ColumnName"]));
// check whether Column exist in Version table with column name of Master Table
// if column not found return with Message
DataRow[] drVerCol = verColumnsDS.Tables[0].Select("ColumnName = '" + sbColName.ToString() + "'");
if (drVerCol.Length > 0)
{
VerColID = Util.GetValueOfInt(drVerCol[0]["AD_Column_ID"]);
}
else
{
log.Log(Level.SEVERE, "Version Column Not Found :: " + sbColName.ToString());
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNameNotFound") + " :: " + sbColName.ToString());
}
}
else
{
log.Log(Level.SEVERE, "Column ID not found in Original table :: " + origFld.GetAD_Column_ID());
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNameNotFound") + " :: " + origFld.GetAD_Column_ID());
}
// Only create Version Field if Version Column found
if (VerColID > 0)
{
// check if field is already created with column
// else skip creating field
int fldID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Field_ID FROM AD_Field WHERE AD_Column_ID = " + VerColID + " AND AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName()));
if (fldID <= 0)
{
// Create Field for Column, copy field from Master tables Field tab on Version Field against Version Tab
MField verFld = new MField(GetCtx(), 0, Get_TrxName());
origFld.CopyTo(verFld);
verFld.SetAD_Tab_ID(Ver_AD_Tab_ID);
verFld.SetAD_Column_ID(VerColID);
verFld.SetExport_ID(null);
if (!verFld.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 Field";
}
log.Log(Level.SEVERE, "Version Field not saved :: " + verFld.GetName() + " :: " + error);
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "FieldNotSaved") + " :: " + verFld.GetName());
}
}
}
}
}
// Fill Dataset again from Version Field
verFieldDS = DB.ExecuteDataset("SELECT f.AD_Column_ID, f.Name, f.AD_Field_ID, (SELECT c.AD_Element_ID FROM AD_Column c WHERE c.AD_Column_ID = f.AD_Column_ID) AS AD_Element_ID FROM AD_Field f WHERE f.AD_Tab_ID = " + Ver_AD_Tab_ID, null, Get_TrxName());
// Create Default Fields against default Columns for Version tab
string retMsg = CreateDefaultFields(Ver_AD_Tab_ID, verFieldDS, hasVerFields, verColumnsDS, Ver_AD_Table_ID);
if (retMsg != "")
{
Get_TrxName().Rollback();
return(retMsg);
}
return("");
}
