} // checkStandardColumns /// <summary> /// Create Column in AD and DB /// </summary> /// <param name="col">Column Object</param> /// <param name="table">Table Object</param> /// <param name="alsoInDB">Also in DB</param> /// <returns></returns> private static bool CreateColumn(MColumn col, MTable table, bool alsoInDB) { // Element M_Element element = M_Element.Get(col.GetCtx(), col.GetColumnName(), col.Get_TrxName()); if (element == null) { element = new M_Element(col.GetCtx(), col.GetColumnName(), col.GetEntityType(), null); if (!element.Save()) { return(false); } log.Info("Created Element: " + element.GetColumnName()); } // Column Sync col.SetColumnName(element.GetColumnName()); col.SetName(element.GetName()); col.SetDescription(element.GetDescription()); col.SetHelp(element.GetHelp()); col.SetAD_Element_ID(element.GetAD_Element_ID()); // if (!col.Save()) { return(false); } // DB if (!alsoInDB) { return(true); } // String sql = col.GetSQLAdd(table); bool success = DataBase.DB.ExecuteUpdateMultiple(sql, false, table.Get_TrxName()) >= 0; if (success) { log.Info("Created: " + table.GetTableName() + "." + col.GetColumnName()); } else { log.Warning("NOT Created: " + table.GetTableName() + "." + col.GetColumnName()); } return(success); } // createColumn
} // generateTable /// <summary> /// Generate Delta View /// </summary> /// <returns>true, if created</returns> private bool GenerateView() { // View m_viewTable = MTable.Get(m_ctx, m_vTableName); if (m_viewTable == null) { m_viewTable = new MTable(m_ctx, 0, null); } PO.CopyValues(m_baseTable, m_viewTable); m_viewTable.SetTableName(m_vTableName); m_viewTable.SetName(m_baseTable.GetName() + " View"); m_viewTable.SetIsView(true); if (!m_viewTable.Save()) { throw new Exception("Cannot save " + m_vTableName); } MColumn[] vCols = SyncMColumns(false); //ColumnSync // Create View StringBuilder sql = new StringBuilder("CREATE OR REPLACE VIEW ") .Append(m_vTableName) .Append(" AS SELECT "); for (int i = 0; i < vCols.Length; i++) { if (i > 0) { sql.Append(","); } MColumn column = vCols[i]; String columnName = column.GetColumnName(); if (column.IsStandardColumn() || column.IsKey()) { sql.Append("b.").Append(columnName); } else { sql.Append(",COALESCE(b.").Append(columnName) .Append("d.").Append(columnName).Append(") AS ").Append(columnName); } } // From String keyColumnName = m_baseTable.GetTableName() + "_ID"; sql.Append(" FROM ").Append(m_baseTable.GetTableName()) .Append(" b LEFT OUTER JOIN ").Append(m_dTableName) .Append(" d ON (b.").Append(keyColumnName) .Append("=d.").Append(keyColumnName).Append(")"); // log.Info(sql.ToString()); if (DataBase.DB.ExecuteQuery(sql.ToString(), null) < 0) { return(false); } return(true); } // generateView
/// <summary> ///Add Translation Columns /// </summary> private void InitColumns() { //MTable table = null; //MTable table = MTable.get(Env.getCtx(), m_trlTableName); MTable table = MTable.Get(Utility.Env.GetContext(), _trlTableName); if (table == null) { throw new ArgumentException("Table Not found=" + _trlTableName); } MColumn[] columns = table.GetColumns(false); for (int i = 0; i < columns.Length; i++) { MColumn column = columns[i]; if (column.IsStandardColumn()) { continue; } String columnName = column.GetColumnName(); if (columnName.EndsWith("_ID") || columnName.StartsWith("AD_Language") || columnName.Equals("IsTranslated")) { continue; } // _columns.Add(columnName); } if (_columns.Count == 0) { throw new ArgumentException("No Columns found=" + _trlTableName); } }
/// <summary> /// Get Column Name /// </summary> /// <param name="ctx">context</param> /// <param name="AD_Column_ID">column id</param> /// <returns>Column Name or null</returns> public static string GetColumnName(Ctx ctx, int AD_Column_ID) { MColumn col = MColumn.Get(ctx, AD_Column_ID); if (col.Get_ID() == 0) { return(null); } return(col.GetColumnName()); }
} // isSingleKey /// <summary> ///Get Key Columns of Table /// </summary> /// <returns>key columns</returns> public String[] GetKeyColumns() { List <String> list = new List <String>(); GetColumns(false); for (int i = 0; i < m_columns.Length; i++) { MColumn column = m_columns[i]; if (column.IsKey()) { return new String[] { column.GetColumnName() } } ; if (column.IsParent()) { list.Add(column.GetColumnName()); } } String[] retValue; retValue = list.ToArray(); return(retValue); } // getKeyColumns
/// <summary> /// Get Display ColumnName /// </summary> /// <returns>column name</returns> public String GetDisplayColumnName() { String et = GetElementType(); if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et)) { if (_ColumnName == null) { _ColumnName = MColumn.GetColumnName(GetCtx(), GetAD_Column_ID()); } return(_ColumnName); } return(GetColumnName(et)); }
} // /// <summary> ///Get list of columns for SELECT statement. /// Handles virtual columns /// </summary> /// <returns>select columns</returns> public String GetSelectColumns() { GetColumns(false); StringBuilder sb = new StringBuilder(); for (int i = 0; i < m_columns.Length; i++) { MColumn col = m_columns[i]; if (i > 0) { sb.Append(","); } if (col.IsVirtualColumn()) { sb.Append(col.GetColumnSQL()).Append(" AS "); } sb.Append(col.GetColumnName()); } return(sb.ToString()); } // getSelectColum
/// <summary> /// Get Display ColumnName /// </summary> /// <returns>column name</returns> public String GetDisplayColumnName() { String et = GetElementType(); // Changed By Amit 23-3-2016 //if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et)) if (ELEMENTTYPE_UserElement1.Equals(et) || ELEMENTTYPE_UserElement2.Equals(et) || ELEMENTTYPE_UserElement3.Equals(et) || ELEMENTTYPE_UserElement4.Equals(et) || ELEMENTTYPE_UserElement5.Equals(et) || ELEMENTTYPE_UserElement6.Equals(et) || ELEMENTTYPE_UserElement7.Equals(et) || ELEMENTTYPE_UserElement8.Equals(et) || ELEMENTTYPE_UserElement9.Equals(et)) { if (_ColumnName == null) { _ColumnName = MColumn.GetColumnName(GetCtx(), GetAD_Column_ID()); } return(_ColumnName); } return(GetColumnName(et)); }
} // generate /// <summary> /// Generate Delta and History Table /// </summary> /// <returns></returns> private bool GenerateTable() { // Table m_derivedTable = MTable.Get(m_ctx, m_dTableName); if (m_derivedTable == null) { m_derivedTable = new MTable(m_ctx, 0, null); } PO.CopyValues(m_baseTable, m_derivedTable); m_derivedTable.SetTableName(m_dTableName); m_derivedTable.SetName(m_derivedTable.GetName() + " SubTable"); m_derivedTable.SetSubTableType(m_derivedTableType); m_derivedTable.SetBase_Table_ID(m_baseTable.GetAD_Table_ID()); if (!m_derivedTable.Save()) { throw new Exception("Cannot save " + m_dTableName); } MColumn[] dCols = SyncMColumns(true); // Sync Columns in Database List <MColumn> list = new List <MColumn>(dCols.Length); foreach (MColumn element in dCols) { list.Add(element); } Trx trx = Trx.Get("getDatabaseMetaData"); // String catalog = ""; String schema = DataBase.DB.GetSchema(); String tableName = m_dTableName; tableName = tableName.ToUpper(); int noColumns = 0; // DataSet rs = null; using (DatabaseMetaData md = new DatabaseMetaData()) { rs = md.GetColumns(catalog, schema, tableName); } for (int rscount = 0; rscount <= rs.Tables[0].Rows.Count - 1; rscount++) { noColumns++; String columnName = rs.Tables[0].Rows[rscount]["COLUMN_NAME"].ToString(); bool found = false; for (int i = 0; i < list.Count; i++) { MColumn dCol = list[i]; if (columnName.Equals(dCol.GetColumnName(), StringComparison.OrdinalIgnoreCase)) { String sql = dCol.GetSQLModify(m_derivedTable, false); DataBase.DB.ExecuteUpdateMultiple(sql, false, null); found = true; list.Remove(list[i]); break; } } if (!found) { String sql = "ALTER TABLE " + m_dTableName + " DROP COLUMN " + columnName; DataBase.DB.ExecuteQuery(sql, null); } } //rs.close(); trx.Close(); // No Columns if (noColumns == 0) { String sql = m_derivedTable.GetSQLCreate(); return(DataBase.DB.ExecuteUpdateMultiple(sql, false, null) >= 0); } // New Columns for (int i = 0; i < list.Count(); i++) { MColumn dCol = list[i]; if (dCol.IsVirtualColumn()) { continue; } String sql = dCol.GetSQLAdd(m_derivedTable); DataBase.DB.ExecuteUpdateMultiple(sql, false, null); } return(true); } // generateTable
} // getSQLCrete // getSQLCrete /** * Get SQL Create * @param requery refresh columns * @return create table DDL */ public String GetSQLCreate(bool requery) { StringBuilder sb = new StringBuilder("CREATE TABLE ") .Append(GetTableName()).Append(" ("); // bool hasPK = false; bool hasParents = false; bool firstColumn = true; StringBuilder constraints = new StringBuilder(); StringBuilder unqConstraints = new StringBuilder(); GetColumns(requery); for (int i = 0; i < m_columns.Length; i++) { MColumn column = m_columns[i]; if (column.IsVirtualColumn()) { continue; } if (firstColumn) { firstColumn = false; } else { sb.Append(", "); } sb.Append(column.GetSQLDDL()); // if (column.IsKey()) { constraints.Append(", CONSTRAINT PK").Append(GetAD_Table_ID()) .Append(" PRIMARY KEY (").Append(column.GetColumnName()).Append(")"); hasPK = true; } if (column.IsParent()) { hasParents = true; } if (column.IsUnique()) { if (unqConstraints.Length == 0) { unqConstraints.Append(", CONSTRAINT UK").Append(GetAD_Table_ID()) .Append(" UNIQUE (").Append(column.GetColumnName()); } else { unqConstraints.Append(",").Append(column.GetColumnName()); } } } // Multi Column PK if (!hasPK && hasParents) { StringBuilder cols = new StringBuilder(); for (int i = 0; i < m_columns.Length; i++) { MColumn column = m_columns[i]; if (!column.IsParent()) { continue; } if (cols.Length > 0) { cols.Append(", "); } cols.Append(column.GetColumnName()); } sb.Append(", CONSTRAINT PK").Append(GetAD_Table_ID()) .Append(" PRIMARY KEY (").Append(cols).Append(")"); } if (unqConstraints.Length > 0) { unqConstraints.Append(")"); } sb.Append(constraints); sb.Append(unqConstraints); sb.Append(")"); return(sb.ToString()); } // getSQLCreate
} // GetSourceColumn /** * Is Criteria Met * @param po po * @return true if criteria is met */ public bool IsMet(PO po) { MColumn column = GetSourceColumn(); String columnName = column.GetColumnName(); int index = po.Get_ColumnIndex(columnName); if (index == -1) { throw new Exception(ToString() + ": AD_Column_ID not found"); } // Get Value Object value = po.Get_Value(index); String op = GetOperation(); // Compare Value String compareString = GetValueString(); if (op.Equals(OPERATION_Sql)) { compareString = GetSQLValue(); op = OPERATION_Eq; } // NULL handling if (value == null) { if (compareString == null || compareString.Length == 0 || compareString.Equals("NULL", StringComparison.OrdinalIgnoreCase)) { if (op.Equals(OPERATION_Eq)) { return(true); } } else { if (!op.Equals(OPERATION_Eq)) { return(true); } } return(false); } if (GetRecord_ID() == 0 && // no value to compare to (compareString == null || compareString.Length == 0)) { return(false); } // Like - String if (op.Equals(OPERATION_Like)) { String s = value.ToString(); String cmp = compareString; if (cmp.IndexOf('%') != -1) // SQL Like { log.Warning(ToString() + ": SQL LIKE not supported yet"); //TODO: SQL Like } return(s.ToUpper() .IndexOf(cmp.ToUpper()) != 0); // substring } try { if (value is int) { int ii = (int)value; int?cmp = null; if (GetRecord_ID() > 0) { cmp = GetRecord_ID(); } else { cmp = (int)int.Parse(compareString); } // Tree Handling bool?treeOp = (bool?)TreeOperation(columnName, cmp, op, ii, po.GetAD_Client_ID()); if (treeOp != null) { return(treeOp.Value); } // if (op.Equals(OPERATION_Eq)) { return(ii.Equals(cmp)); } else if (op.Equals(OPERATION_NotEq)) { return(!ii.Equals(cmp)); } else if (op.Equals(OPERATION_Gt)) { return(ii.CompareTo(cmp) > 0); } else if (op.Equals(OPERATION_GtEq)) { return(ii.CompareTo(cmp) >= 0); } else if (op.Equals(OPERATION_Le)) { return(ii.CompareTo(cmp) < 0); } else if (op.Equals(OPERATION_LeEq)) { return(ii.CompareTo(cmp) <= 0); } } else if (value is Decimal) { Decimal bd = (Decimal)value; Decimal cmp = decimal.Parse(compareString); if (op.Equals(OPERATION_Eq)) { return(bd.Equals(cmp)); } else if (op.Equals(OPERATION_NotEq)) { return(!bd.Equals(cmp)); } else if (op.Equals(OPERATION_Gt)) { return(bd.CompareTo(cmp) > 0); } else if (op.Equals(OPERATION_GtEq)) { return(bd.CompareTo(cmp) >= 0); } else if (op.Equals(OPERATION_Le)) { return(bd.CompareTo(cmp) < 0); } else if (op.Equals(OPERATION_LeEq)) { return(bd.CompareTo(cmp) <= 0); } } else if (value is DateTime) { DateTime?ts = (DateTime?)value; DateTime cmp = DateTime.Parse(compareString); if (op.Equals(OPERATION_Eq)) { return(ts.Equals(cmp)); } else if (op.Equals(OPERATION_NotEq)) { return(!ts.Equals(cmp)); } else if (op.Equals(OPERATION_Gt)) { return(ts.Value.CompareTo(cmp) > 0); } else if (op.Equals(OPERATION_GtEq)) { return(ts.Value.CompareTo(cmp) >= 0); } else if (op.Equals(OPERATION_Le)) { return(ts.Value.CompareTo(cmp) < 0); } else if (op.Equals(OPERATION_LeEq)) { return(ts.Value.CompareTo(cmp) <= 0); } } else // String { String s = value.ToString(); String cmp = compareString; if (op.Equals(OPERATION_Eq)) { return(s.Equals(cmp)); } else if (op.Equals(OPERATION_NotEq)) { return(!s.Equals(cmp)); } else if (op.Equals(OPERATION_Gt)) { return(s.CompareTo(cmp) > 0); } else if (op.Equals(OPERATION_GtEq)) { return(s.CompareTo(cmp) >= 0); } else if (op.Equals(OPERATION_Le)) { return(s.CompareTo(cmp) < 0); } else if (op.Equals(OPERATION_LeEq)) { return(s.CompareTo(cmp) <= 0); } } } catch (Exception e) { log.Warning(ToString() + ": " + e); } return(false); } // isMet
} // getTargetColumn /// <summary> /// Execute Auto Assignment /// </summary> /// <param name="po">PO to be modified</param> /// <returns>true if modified</returns> public bool ExecuteIt(PO po) { // Check Column MColumn column = GetTargetColumn(); String columnName = column.GetColumnName(); int index = po.Get_ColumnIndex(columnName); if (index == -1) { throw new Exception(ToString() + ": AD_Column_ID not found"); } // Check Value Object value = po.Get_Value(index); String assignRule = GetAssignRule(); if (value == null && assignRule.Equals(ASSIGNRULE_OnlyIfNOTNULL)) { return(false); } else if (value != null && assignRule.Equals(ASSIGNRULE_OnlyIfNULL)) { return(false); } // Check Criteria if (m_criteria == null) { GetCriteria(false); } bool modified = false; for (int i = 0; i < m_criteria.Length; i++) { MAssignCriteria criteria = m_criteria[i]; if (criteria.IsMet(po)) { modified = true; break; } } if (!modified) { return(false); } // Assignment String methodName = "set" + columnName; Type parameterType = null; Object parameter = null; int displayType = column.GetAD_Reference_ID(); String valueString = GetValueString(); if (DisplayType.IsText(displayType) || displayType == DisplayType.List) { parameterType = typeof(string); parameter = valueString; } else if (DisplayType.IsID(displayType) || displayType == DisplayType.Integer) { parameterType = typeof(int); if (GetRecord_ID() != 0) { parameter = GetRecord_ID(); } else if (valueString != null && valueString.Length > 0) { try { parameter = int.Parse(valueString); } catch (Exception e) { log.Warning(ToString() + " " + e); return(false); } } } else if (DisplayType.IsNumeric(displayType)) { parameterType = typeof(Decimal); if (valueString != null && valueString.Length > 0) { try { parameter = Decimal.Parse(valueString); } catch (Exception e) { log.Warning(ToString() + " " + e); return(false); } } } else if (DisplayType.IsDate(displayType)) { parameterType = typeof(DateTime); if (valueString != null && valueString.Length > 0) { try { parameter = DateTime.Parse(valueString); } catch (Exception e) { log.Warning(ToString() + " " + e); return(false); } } } else if (displayType == DisplayType.YesNo) { parameterType = typeof(bool); parameter = "Y".Equals(valueString); } else if (displayType == DisplayType.Button) { parameterType = typeof(string); parameter = GetValueString(); } else if (DisplayType.IsLOB(displayType)) // CLOB is String { parameterType = typeof(byte[]); // parameter = getValueString(); } // Assignment try { Type clazz = po.GetType(); System.Reflection.MethodInfo method = clazz.GetMethod(methodName, new Type[] { parameterType }); method.Invoke(po, new Object[] { parameter }); } catch (Exception e) { log.Log(Level.WARNING, ToString(), e); // fallback if (parameter is Boolean) { po.Set_Value(index, valueString); } else { po.Set_Value(index, parameter); } // modified = false; } return(modified); } // executeIt
/// <summary> /// Get Key ColumnName /// </summary> /// <returns>Key Column Name</returns> public String GetKeyColumnName() { int AD_Column_ID = GetColumn_Key_ID(); return(MColumn.GetColumnName(GetCtx(), AD_Column_ID)); }
/// <summary> /// Get Display ColumnName /// </summary> /// <returns>Display Column Name</returns> public String GetDisplayColumnName() { int AD_Column_ID = GetColumn_Display_ID(); return(MColumn.GetColumnName(GetCtx(), AD_Column_ID)); }