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