Exemple #1
0
        }//	prepare

        /// <summary>
        /// Alert table
        /// </summary>
        /// <returns>int</returns>
        ///
        override protected string DoIt()
        {
            string exception = "";

            log.Info("C_Column_ID=" + p_AD_Column_ID);
            if (p_AD_Column_ID == 0)
            {
                //    return "";
                throw new Exception("@No@ @AD_Column_ID@");
            }
            //IDbTransaction trx = ExecuteQuery.GerServerTransaction();
            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());
            }
            //	Find Column in Database

            DatabaseMetaData md      = new DatabaseMetaData();
            String           catalog = "";
            String           schema  = DataBase.DB.GetSchema();

            //get table name
            string tableName = table.GetTableName();


            int    noColumns;
            string sql = null;
            //get columns of a table
            DataSet dt = md.GetColumns(catalog, schema, tableName);

            md.Dispose();
            //for each column
            for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
            {
                string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
                if (!columnName.Equals(column.GetColumnName().ToLower()))
                {
                    continue;
                }

                //check if column is null or not

                string dtColumnName = "is_nullable";
                string value        = "YES";
                //if database is oracle
                if (DatabaseType.IsOracle)
                {
                    dtColumnName = "NULLABLE";
                    value        = "Y";
                }
                bool notNull = false;
                //check if column is null
                if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
                {
                    notNull = false;
                }
                else
                {
                    notNull = true;
                }
                //............................

                //if column is virtual column then alter table and drop this column
                if (column.IsVirtualColumn())
                {
                    sql = "ALTER TABLE " + table.GetTableName()
                          + " DROP COLUMN " + columnName;
                }
                else
                {
                    sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
                    noColumns++;
                    break;
                }
            }
            dt = null;

            //while (rs.next())
            //{
            //    noColumns++;
            //    String columnName = rs.getString ("COLUMN_NAME");
            //    if (!columnName.equalsIgnoreCase(column.getColumnName()))
            //        continue;

            //    //	update existing column
            //    boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
            //    if (column.isVirtualColumn())
            //        sql = "ALTER TABLE " + table.getTableName()
            //            + " DROP COLUMN " + columnName;
            //    else
            //        sql = column.getSQLModify(table, column.isMandatory() != notNull);
            //    break;
            //}
            //rs.close();
            //rs = null;

            //	No Table
            if (noColumns == 0)
            {
                sql = table.GetSQLCreate();
            }
            //	No existing column
            else if (sql == null)
            {
                if (column.IsVirtualColumn())
                {
                    return("@IsVirtualColumn@");
                }
                sql = column.GetSQLAdd(table);
            }

            int no = 0;

            if (sql.IndexOf("; ") == -1)
            {
                //no =
                //ExecuteQuery.ExecuteNonQuery(sql, false, Get_Trx());
                try
                {
                    no = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());

                    AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
                }
                catch (Exception ex)
                {
                    exception = ex.Message;
                }
                //addLog (0, null, new BigDecimal(no), sql);
            }
            else
            {
                //string ss = "; ";
                string[] statements = sql.Split(';');
                for (int i = 0; i < statements.Length; i++)
                {
                    int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_Trx());
                    AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);

                    no += count;
                    //ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
                }
            }

            if (no == -1)
            {
                string        msg = "@Error@ ";
                ValueNamePair pp  = VAdvantage.Logging.VLogger.RetrieveError();
                if (pp != null)
                {
                    msg += exception + " - ";
                }
                msg += sql;
                throw new Exception(msg);
            }
            string r = createFK();

            return(sql + "; " + r);
        }       //	doIt
Exemple #2
0
        }       //	doIt

        private String createFK()
        {
            String returnMessage = "";

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

            String fk;

            if ((column.GetAD_Reference_ID() == DisplayType.Account) &&
                !(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
            {
                fk = "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 = "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 = "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 = "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";
            }

            SqlParameter[] pstmt = null;
            DataSet        ds    = null;

            try
            {
                /*Find foreign key relation in Database
                 * */
                //Trx trx = Trx.Get("getDatabaseMetaData");
                DatabaseMetaData md        = new DatabaseMetaData();
                String           catalog   = "";// DB.getCatalog();
                String           schema    = DB.GetSchema();
                String           tableName = table.GetTableName();



                String dropsql = null;
                int    no      = 0;

                String constraintNameDB = null;
                String PKTableNameDB    = null;
                String PKColumnNameDB   = null;
                int    constraintTypeDB = 0;

                /* Get foreign key information from DatabaseMetadata
                 * */
                ds = md.GetForeignKeys(catalog, schema, tableName);
                md.Dispose();
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Dictionary <String, String> fkcolumnDetail = md.GetForeignColumnDetail(ds.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    = fkcolumnDetail["PK_Table_Name"];                       //rs.GetString("PKTABLE_NAME");
                            PKColumnNameDB   = 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());

                ds = DB.ExecuteDataset(fk, pstmt, Get_Trx());

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    String TableName  = ds.Tables[0].Rows[0]["TableName"].ToString();
                    String ColumnName = ds.Tables[0].Rows[0]["ColumnName"].ToString();
                    //	int AD_Column_ID = rs.getInt (3);
                    //	int PK_Column_ID = rs.getInt (4);
                    String PKTableName    = ds.Tables[0].Rows[0]["TableName1"].ToString();
                    String PKColumnName   = ds.Tables[0].Rows[0]["ColumnName1"].ToString();
                    String ConstraintType = ds.Tables[0].Rows[0]["ConstraintType"].ToString();
                    String ConstraintName = 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.Equals(ConstraintName, StringComparison.OrdinalIgnoreCase)) &&
                             (PKTableNameDB != null) && (PKTableNameDB.Equals(PKTableName, StringComparison.OrdinalIgnoreCase)) &&
                             (PKColumnNameDB != null) && (PKColumnNameDB.Equals(PKColumnName, StringComparison.OrdinalIgnoreCase)) &&
                             ((constraintTypeDB == DatabaseMetaData.importedKeyRestrict) &&
                              (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
                               X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))))
                            ||
                            ((constraintTypeDB == DatabaseMetaData.importedKeyCascade) &&
                             (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
                              X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType)))
                            ||
                            ((constraintTypeDB == DatabaseMetaData.importedKeySetNull) &&
                             (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType)))

                            )
                        {
                            modified = false;
                        }

                        else
                        {
                            dropsql = "ALTER TABLE " + table.GetTableName()
                                      + " DROP CONSTRAINT " + constraintNameDB;
                        }
                    }
                    if (modified)
                    {
                        StringBuilder sql = null;
                        try
                        {
                            if (dropsql != null)
                            {
                                /* Drop the existing constraint */
                                //no = DB.executeUpdate(Get_Trx(), dropsql);

                                no = DB.ExecuteQuery(dropsql, null, Get_Trx());
                                AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
                            }
                            /* Now create the sql foreign key constraint */
                            sql = new StringBuilder("ALTER TABLE ")
                                  .Append(TableName)
                                  .Append(" ADD CONSTRAINT ").Append(ConstraintName)
                                  .Append(" FOREIGN KEY (").Append(ColumnName)
                                  .Append(") REFERENCES ").Append(PKTableName)
                                  .Append(" (").Append(PKColumnName).Append(")");
                            Boolean createfk = true;
                            if (!String.IsNullOrEmpty(ConstraintType))
                            {
                                if (X_AD_Column.CONSTRAINTTYPE_DoNOTCreate.Equals(ConstraintType))
                                {
                                    createfk = false;
                                }
                                else if (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
                                         X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))
                                {
                                    ;
                                }
                                else if (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
                                         X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType))
                                {
                                    sql.Append(" ON DELETE CASCADE");
                                }
                                else if (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType) ||
                                         X_AD_Column.CONSTRAINTTYPE_NullTrigger.Equals(ConstraintType))
                                {
                                    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 = sql.ToString();
                                }
                                else
                                {
                                    log.Info(ConstraintName + " - " + TableName + "." + ColumnName
                                             + " - ReturnCode=" + no);
                                    returnMessage = "FAILED:" + sql.ToString();
                                }
                            } //if createfk
                        }
                        catch (Exception e)
                        {
                            log.Log(Level.SEVERE, sql.ToString() + " - " + e.ToString());
                        }
                    } // modified
                }       //	rs.next
                else
                {
                    if (constraintNameDB != null && constraintNameDB.Equals("FK" + column.GetAD_Table_ID() + "_" + p_AD_Column_ID, StringComparison.OrdinalIgnoreCase))
                    {
                        dropsql = "ALTER TABLE " + table.GetTableName()
                                  + " DROP CONSTRAINT " + constraintNameDB;

                        /* Drop the existing constraint */
                        no = DB.ExecuteQuery(dropsql, null, Get_Trx());
                        AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
                        returnMessage = dropsql.ToString();
                    }
                }
            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, fk, e);
            }
            return(returnMessage);
        }
        }       //	prepare

        /**
         *  Process
         *	@return info
         *	@throws Exception
         */
        protected override String DoIt()// throws Exception
        {
            log.Info("AD_Column_ID=" + p_AD_Column_ID
                     + ", IsEncrypted=" + p_IsEncrypted
                     + ", ChangeSetting=" + p_ChangeSetting
                     + ", MaxLength=" + p_MaxLength);
            MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());

            if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
            {
                throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
            }
            //
            String columnName = column.GetColumnName();
            int    dt         = column.GetAD_Reference_ID();

            //	Can it be enabled?
            if (column.IsKey() ||
                column.IsParent() ||
                column.IsStandardColumn() ||
                column.IsVirtualColumn() ||
                column.IsIdentifier() ||
                column.IsTranslated() ||
                DisplayType.IsLookup(dt) ||
                DisplayType.IsLOB(dt) ||
                "DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
                "Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
                "Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
            {
                if (column.IsEncrypted())
                {
                    column.SetIsEncrypted(false);
                    column.Save(Get_Trx());
                }
                return(columnName + ": cannot be encrypted");
            }

            //	Start
            AddLog(0, null, null, "Encryption Class = " + SecureEngine.GetClassName());
            bool error = false;

            //	Test Value
            if (p_TestValue != null && p_TestValue.Length > 0)
            {
                String encString = SecureEngine.Encrypt(p_TestValue);
                AddLog(0, null, null, "Encrypted Test Value=" + encString);
                String clearString = SecureEngine.Decrypt(encString);
                if (p_TestValue.Equals(clearString))
                {
                    AddLog(0, null, null, "Decrypted=" + clearString
                           + " (same as test value)");
                }
                else
                {
                    AddLog(0, null, null, "Decrypted=" + clearString
                           + " (NOT the same as test value - check algorithm)");
                    error = true;
                }
                int encLength = encString.Length;
                AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
                if (encLength <= column.GetFieldLength())
                {
                    AddLog(0, null, null, "Encrypted Length (" + encLength
                           + ") fits into field (" + column.GetFieldLength() + ")");
                }
                else
                {
                    AddLog(0, null, null, "Encrypted Length (" + encLength
                           + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
                    error = true;
                }
            }

            //	Length Test
            if (p_MaxLength != 0)
            {
                String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
                while (testClear.Length < p_MaxLength)
                {
                    testClear += testClear;
                }
                testClear = testClear.Substring(0, p_MaxLength);
                log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
                //
                String encString = SecureEngine.Encrypt(testClear);
                int    encLength = encString.Length;
                AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
                if (encLength <= column.GetFieldLength())
                {
                    AddLog(0, null, null, "Encrypted Max Length (" + encLength
                           + ") fits into field (" + column.GetFieldLength() + ")");
                }
                else
                {
                    AddLog(0, null, null, "Encrypted Max Length (" + encLength
                           + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
                    error = true;
                }
            }

            if (p_IsEncrypted != column.IsEncrypted())
            {
                if (error || !p_ChangeSetting)
                {
                    AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
                }
                else
                {
                    column.SetIsEncrypted(p_IsEncrypted);
                    if (column.Save(Get_Trx()))
                    {
                        AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
                    }
                    else
                    {
                        AddLog(0, null, null, "Save Error");
                    }
                }
            }


            if (p_IsEncrypted == column.IsEncrypted() && !error)      // Done By Karan on 10-nov-2016, to encrypt/decrypt passwords according to settings.
            {
                //object colID = DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID =(SELECT AD_Table_ID From AD_Table WHERE TableName='AD_User') AND ColumnName='Password'", null, Get_Trx());



                // if (colID != null && colID != DBNull.Value && Convert.ToInt32(colID) == column.GetAD_Column_ID())
                //{

                string tableName = MTable.GetTableName(GetCtx(), column.GetAD_Table_ID());

                DataSet ds = DB.ExecuteDataset("SELECT " + column.GetColumnName() + "," + tableName
                                               + "_ID FROM " + tableName, null, Get_Trx());
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    if (p_IsEncrypted)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
                                !SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
                            {
                                //MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID()) + "_ID"]), Get_Trx());
                                //user.SetPassword(SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()));

                                int encLength = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()).Length;

                                if (encLength <= column.GetFieldLength())
                                {
                                    //PO tab = MTable.GetPO(GetCtx(), tableName,
                                    //    Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());

                                    //tab.Set_Value(column.GetColumnName(), (SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
                                    //if (!tab.Save(Get_Trx()))
                                    //{
                                    //    Rollback();
                                    //    return "Encryption=" + false;
                                    //}
                                    string p_NewPassword = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
                                    String sql           = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
                                    if (!string.IsNullOrEmpty(p_NewPassword))
                                    {
                                        sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
                                    }
                                    sql += " WHERE " + tableName + "_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
                                    int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
                                    if (iRes <= 0)
                                    {
                                        Rollback();
                                        return("Encryption=" + false);
                                    }
                                }
                                else
                                {
                                    Rollback();
                                    return("After Encryption some values may exceed the value of column length. Please exceed column Length.");
                                }
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
                                SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
                            {
                                // MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID())+"_ID"]), Get_Trx());

                                //PO tab = MTable.GetPO(GetCtx(), tableName,
                                //   Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());

                                //tab.Set_Value(column.GetColumnName(), (SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
                                //if (!tab.Save(Get_Trx()))
                                //{
                                //    Rollback();
                                //    return "Encryption=" + false;
                                //}

                                string p_NewPassword = SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
                                String sql           = "UPDATE " + tableName + "  SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
                                if (!string.IsNullOrEmpty(p_NewPassword))
                                {
                                    sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
                                }
                                sql += " WHERE " + tableName + "_ID  =" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
                                int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
                                if (iRes <= 0)
                                {
                                    Rollback();
                                    return("Encryption=" + false);
                                }
                            }
                        }
                    }
                }
                //}
            }
            return("Encryption=" + column.IsEncrypted());
        }
        }//	prepare

        /// <summary>
        /// Alert table
        /// </summary>
        /// <returns>int</returns>
        ///
        override protected string DoIt()
        {
            string exception = "";

            log.Info("C_Column_ID=" + p_AD_Column_ID);
            if (p_AD_Column_ID == 0)
            {
                //    return "";
                throw new Exception("@No@ @AD_Column_ID@");
            }
            //IDbTransaction trx = ExecuteQuery.GerServerTransaction();
            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());
            }
            //	Find Column in Database

            if (column.IsVirtualColumn())
            {
                return("@IsVirtualColumn@");
            }
            int    noColumns = 0;
            string sql       = CommonFunctions.SyncColumn(table, column, out noColumns);

            //DatabaseMetaData md = new DatabaseMetaData();
            //String catalog = "";
            //String schema = DataBase.DB.GetSchema();

            ////get table name
            //string tableName = table.GetTableName();


            //int noColumns;
            //string sql = null;
            ////get columns of a table
            //DataSet dt = md.GetColumns(catalog, schema, tableName);
            //md.Dispose();
            ////for each column
            //for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
            //{
            //    string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
            //    if (!columnName.Equals(column.GetColumnName().ToLower()))
            //        continue;

            //    //check if column is null or not

            //    string dtColumnName = "is_nullable";
            //    string value = "YES";
            //    //if database is oracle
            //    if (DatabaseType.IsOracle)
            //    {
            //        dtColumnName = "NULLABLE";
            //        value = "Y";
            //    }
            //    bool notNull = false;
            //    //check if column is null
            //    if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
            //        notNull = false;
            //    else
            //        notNull = true;
            //    //............................

            //    //if column is virtual column then alter table and drop this column
            //    if (column.IsVirtualColumn())
            //    {
            //        sql = "ALTER TABLE " + table.GetTableName()
            //       + " DROP COLUMN " + columnName;
            //    }
            //    else
            //    {
            //        sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
            //        noColumns++;
            //        break;
            //    }

            //}
            //dt = null;

            ////while (rs.next())
            ////{
            ////    noColumns++;
            ////    String columnName = rs.getString ("COLUMN_NAME");
            ////    if (!columnName.equalsIgnoreCase(column.getColumnName()))
            ////        continue;

            ////    //	update existing column
            ////    boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
            ////    if (column.isVirtualColumn())
            ////        sql = "ALTER TABLE " + table.getTableName()
            ////            + " DROP COLUMN " + columnName;
            ////    else
            ////        sql = column.getSQLModify(table, column.isMandatory() != notNull);
            ////    break;
            ////}
            ////rs.close();
            ////rs = null;

            ////	No Table
            //if (noColumns == 0)
            //{
            //    sql = table.GetSQLCreate();
            //}
            ////	No existing column
            //else if (sql == null)
            //{
            //    if (column.IsVirtualColumn())
            //    {
            //        return "@IsVirtualColumn@";
            //    }
            //    sql = column.GetSQLAdd(table);
            //}

            int no = 0;

            if (sql.IndexOf(";") == -1)
            {
                //no =
                //ExecuteQuery.ExecuteNonQuery(sql, false, Get_Trx());
                try
                {
                    no = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());

                    AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
                }
                catch (Exception ex)
                {
                    exception = ex.Message;
                }
                //addLog (0, null, new BigDecimal(no), sql);
            }
            else
            {
                //string ss = "; ";
                string[] statements = sql.Split(';');
                for (int i = 0; i < statements.Length; i++)
                {
                    int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_Trx());
                    AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);

                    no += count;
                    //ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
                }
            }

            if (no == -1)
            {
                string        msg = "@Error@ ";
                ValueNamePair pp  = VAdvantage.Logging.VLogger.RetrieveError();
                if (pp != null)
                {
                    msg += exception + " - ";
                }
                msg += sql;
                throw new Exception(msg);
            }
            string r = createFK(noColumns);

            // Change here for Master Data Versioning
            bool hasMainVerCol = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + column.GetAD_Table_ID() + " AND IsActive ='Y' AND IsMaintainVersions = 'Y'", null, Get_Trx())) > 0;

            // check whether there are any columns in the table
            // marked as "Maintain Versions", then proceed else return

            if (!hasMainVerCol)
            {
                hasMainVerCol = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + column.GetAD_Table_ID(), null, Get_Trx())) == "Y";
            }

            string versionMsg = "";

            if (hasMainVerCol)
            {
                // call CreateVersionInfo function of MasterVersions class
                // to create version table and all columns
                MasterVersions mv = new MasterVersions();
                versionMsg = mv.CreateVersionInfo(column.GetAD_Column_ID(), column.GetAD_Table_ID(), Get_Trx());
            }

            return(sql + "; " + r + "; " + versionMsg);
        }       //	doIt
        }       //	prepare

        /**
         *  Process
         *	@return info
         *	@throws Exception
         */
        protected override String DoIt()// throws Exception
        {
            log.Info("AD_Column_ID=" + p_AD_Column_ID
                     + ", IsEncrypted=" + p_IsEncrypted
                     + ", ChangeSetting=" + p_ChangeSetting
                     + ", MaxLength=" + p_MaxLength);
            MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, null);

            if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
            {
                throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
            }
            //
            String columnName = column.GetColumnName();
            int    dt         = column.GetAD_Reference_ID();

            //	Can it be enabled?
            if (column.IsKey() ||
                column.IsParent() ||
                column.IsStandardColumn() ||
                column.IsVirtualColumn() ||
                column.IsIdentifier() ||
                column.IsTranslated() ||
                DisplayType.IsLookup(dt) ||
                DisplayType.IsLOB(dt) ||
                "DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
                "Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
                "Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
            {
                if (column.IsEncrypted())
                {
                    column.SetIsEncrypted(false);
                    column.Save();
                }
                return(columnName + ": cannot be encrypted");
            }

            //	Start
            AddLog(0, null, null, "Encryption Class = " + SecureEngineUtility.SecureEngine.GetClassName());
            bool error = false;

            //	Test Value
            if (p_TestValue != null && p_TestValue.Length > 0)
            {
                String encString = SecureEngineUtility.SecureEngine.Encrypt(p_TestValue);
                AddLog(0, null, null, "Encrypted Test Value=" + encString);
                String clearString = SecureEngineUtility.SecureEngine.Decrypt(encString);
                if (p_TestValue.Equals(clearString))
                {
                    AddLog(0, null, null, "Decrypted=" + clearString
                           + " (same as test value)");
                }
                else
                {
                    AddLog(0, null, null, "Decrypted=" + clearString
                           + " (NOT the same as test value - check algorithm)");
                    error = true;
                }
                int encLength = encString.Length;
                AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
                if (encLength <= column.GetFieldLength())
                {
                    AddLog(0, null, null, "Encrypted Length (" + encLength
                           + ") fits into field (" + column.GetFieldLength() + ")");
                }
                else
                {
                    AddLog(0, null, null, "Encrypted Length (" + encLength
                           + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
                    error = true;
                }
            }

            //	Length Test
            if (p_MaxLength != 0)
            {
                String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
                while (testClear.Length < p_MaxLength)
                {
                    testClear += testClear;
                }
                testClear = testClear.Substring(0, p_MaxLength);
                log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
                //
                String encString = SecureEngineUtility.SecureEngine.Encrypt(testClear);
                int    encLength = encString.Length;
                AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
                if (encLength <= column.GetFieldLength())
                {
                    AddLog(0, null, null, "Encrypted Max Length (" + encLength
                           + ") fits into field (" + column.GetFieldLength() + ")");
                }
                else
                {
                    AddLog(0, null, null, "Encrypted Max Length (" + encLength
                           + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
                    error = true;
                }
            }

            if (p_IsEncrypted != column.IsEncrypted())
            {
                if (error || !p_ChangeSetting)
                {
                    AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
                }
                else
                {
                    column.SetIsEncrypted(p_IsEncrypted);
                    if (column.Save())
                    {
                        AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
                    }
                    else
                    {
                        AddLog(0, null, null, "Save Error");
                    }
                }
            }
            return("Encryption=" + column.IsEncrypted());
        }