/// <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"));
}
public String createFK(int noColumns)
{
StringBuilder returnMessage = new StringBuilder("");
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());
}
// List of columns to be synched
List <int> cols = new List <int>();
// Add current column on which user pressed Synchronize
cols.Add(p_AD_Column_ID);
// if new table is being created and column synch is being done for the first time
// then get all columns of table and add to list
if (noColumns == 0)
{
int[] AllCols = MColumn.GetAllIDs("AD_Column", "AD_Table_ID = " + column.GetAD_Table_ID(), Get_Trx());
cols = AllCols.ToList();
}
#region variables
StringBuilder fk = new StringBuilder("");
StringBuilder dropsql = new StringBuilder("");
String constraintNameDB = "";
StringBuilder PKTableNameDB = new StringBuilder("");
StringBuilder PKColumnNameDB = new StringBuilder("");
StringBuilder TableName = new StringBuilder("");
StringBuilder ColumnName = new StringBuilder("");
StringBuilder PKTableName = new StringBuilder("");
StringBuilder PKColumnName = new StringBuilder("");
StringBuilder ConstraintType = new StringBuilder("");
StringBuilder ConstraintName = new StringBuilder("");
#endregion variables
String schema = DB.GetSchema();
String tableName = table.GetTableName();
try
{
// Loop through all columns
DatabaseMetaData md = new DatabaseMetaData();
if (dsMetaData == null)
{
dsMetaData = md.GetForeignKeys("", schema, tableName);
}
SqlParameter[] pstmt = null;
int no = 0;
int constraintTypeDB = 0;
foreach (int col in cols)
{
column = new MColumn(GetCtx(), col, Get_Trx());
dropsql.Clear();
fk.Clear();
PKTableNameDB.Clear();
PKColumnNameDB.Clear();
TableName.Clear();
ColumnName.Clear();
PKTableName.Clear();
PKColumnName.Clear();
ConstraintType.Clear();
ConstraintName.Clear();
if ((column.GetAD_Reference_ID() == DisplayType.Account) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk.Append("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.Append("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.Append("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.Append("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");
}
/*Find foreign key relation in Database
* */
//Trx trx = Trx.Get("getDatabaseMetaData");
//String catalog = "";// DB.getCatalog();
no = 0;
constraintTypeDB = 0;
/* Get foreign key information from DatabaseMetadata
* */
if (dsMetaData != null && dsMetaData.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsMetaData.Tables[0].Rows.Count; i++)
{
Dictionary <String, String> fkcolumnDetail = md.GetForeignColumnDetail(dsMetaData.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.Append(fkcolumnDetail["PK_Table_Name"]); //rs.GetString("PKTABLE_NAME");
PKColumnNameDB.Append(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());
DataSet ds = DB.ExecuteDataset(fk.ToString(), pstmt, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
TableName.Append(ds.Tables[0].Rows[0]["TableName"].ToString());
ColumnName.Append(ds.Tables[0].Rows[0]["ColumnName"].ToString());
// int AD_Column_ID = rs.getInt (3);
// int PK_Column_ID = rs.getInt (4);
PKTableName.Append(ds.Tables[0].Rows[0]["TableName1"].ToString());
PKColumnName.Append(ds.Tables[0].Rows[0]["ColumnName1"].ToString());
ConstraintType.Append(ds.Tables[0].Rows[0]["ConstraintType"].ToString());
ConstraintName.Append(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.ToString().Equals(ConstraintName.ToString(), StringComparison.OrdinalIgnoreCase)) &&
(PKTableNameDB != null) && (PKTableNameDB.ToString().Equals(PKTableName.ToString(), StringComparison.OrdinalIgnoreCase)) &&
(PKColumnNameDB != null) && (PKColumnNameDB.ToString().Equals(PKColumnName.ToString(), StringComparison.OrdinalIgnoreCase)) &&
((constraintTypeDB == DatabaseMetaData.importedKeyRestrict) &&
(X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType.ToString()) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType.ToString()))))
||
((constraintTypeDB == DatabaseMetaData.importedKeyCascade) &&
(X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType.ToString()) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType.ToString())))
||
((constraintTypeDB == DatabaseMetaData.importedKeySetNull) &&
(X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType.ToString())))
)
{
modified = false;
}
else
{
dropsql.Append("ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB.ToString());
}
}
if (modified)
{
StringBuilder sql = null;
try
{
if (dropsql != null && dropsql.ToString().Trim() != "")
{
/* Drop the existing constraint */
//no = DB.executeUpdate(Get_Trx(), dropsql);
no = DB.ExecuteQuery(dropsql.ToString(), null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql.ToString());
}
/* Now create the sql foreign key constraint */
sql = new StringBuilder("ALTER TABLE ")
.Append(TableName)
.Append(" ADD CONSTRAINT ").Append(ConstraintName.ToString())
.Append(" FOREIGN KEY (").Append(ColumnName.ToString())
.Append(") REFERENCES ").Append(PKTableName.ToString())
.Append(" (").Append(PKColumnName.ToString()).Append(")");
Boolean createfk = true;
if (!String.IsNullOrEmpty(ConstraintType.ToString()))
{
if (X_AD_Column.CONSTRAINTTYPE_DoNOTCreate.Equals(ConstraintType.ToString()))
{
createfk = false;
}
else if (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType.ToString()) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType.ToString()))
{
;
}
else if (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType.ToString()) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType.ToString()))
{
sql.Append(" ON DELETE CASCADE");
}
else if (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType.ToString()) ||
X_AD_Column.CONSTRAINTTYPE_NullTrigger.Equals(ConstraintType.ToString()))
{
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.Append(", " + sql.ToString());
}
else
{
log.Info(ConstraintName + " - " + TableName + "." + ColumnName
+ " - ReturnCode=" + no);
returnMessage.Append(", FAILED:" + sql.ToString());
}
} //if createfk
}
catch (Exception e)
{
if (returnMessage.Length > 0)
{
returnMessage.Append(", " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
AddLog(0, null, Decimal.Parse(no.ToString()), ". " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
}
else
{
returnMessage.Append(", " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
AddLog(0, null, Decimal.Parse(no.ToString()), Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
}
log.Log(Level.SEVERE, sql.ToString() + " - " + e.ToString());
}
} // modified
} // rs.next
else
{
if (constraintNameDB != null && constraintNameDB.ToString().Equals("FK" + column.GetAD_Table_ID() + "_" + p_AD_Column_ID, StringComparison.OrdinalIgnoreCase))
{
dropsql.Append("ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB);
/* Drop the existing constraint */
no = DB.ExecuteQuery(dropsql.ToString(), null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql.ToString());
returnMessage.Append(", " + dropsql.ToString());
}
}
}
}
catch (Exception e)
{
if (returnMessage.Length > 0)
{
returnMessage.Append(", " + ". " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
AddLog(0, null, Decimal.Parse("0"), ". " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
}
else
{
returnMessage.Append(", " + Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
AddLog(0, null, Decimal.Parse("0"), Msg.GetMsg(GetCtx(), "VIS_ConstraintsError") + e.Message);
}
log.Log(Level.SEVERE, fk.ToString(), e);
}
return(returnMessage.ToString());
}