/// <summary> /// Get FK Table - also for lists /// </summary> /// <returns>FK Table</returns> public MTable GetFKTable() { if (!IsFK()) { return(null); } int displayType = GetAD_Reference_ID(); // Lists if (displayType == DisplayType.List) { return(MTable.Get(GetCtx(), MRefList.Table_ID)); } // Account //if (displayType == DisplayType.Account) // return MTable.Get(GetCtx(), MAccount.Table_ID); // C_ValidCombination // Table, TableDir, ... String FKTableName = GetFKColumnName(); if (FKTableName.EndsWith("_ID")) { FKTableName = FKTableName.Substring(0, FKTableName.Length - 3); } return(MTable.Get(GetCtx(), FKTableName)); }
/// <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); } }
} // SubTableUtil /// <summary> /// Generate Delta Infrastructure /// </summary> /// <param name="Context">context</param> /// <param name="tableName">table name</param> /// <param name="userDef">user defined</param> /// <param name="history">history</param> private SubTableUtil(Context Context, String tableName, bool userDef, bool history) { m_ctx = Context; m_baseTable = MTable.Get(Context, tableName); if (m_baseTable == null) { throw new ArgumentException("Not found: " + tableName); } m_userDef = userDef; m_history = history; int index = tableName.IndexOf("_"); m_dTableName = tableName.Substring(0, index) + (m_userDef ? "_UserDef" : "_SysDef") + tableName.Substring(index); if (history) // Default=Each { m_dTableName = tableName + "H"; } else { m_derivedTableType = userDef ? X_AD_Table.SUBTABLETYPE_Delta_User : X_AD_Table.SUBTABLETYPE_Delta_System; } m_vTableName = tableName + "_v"; log.Info(tableName + " + " + m_dTableName + " = " + m_vTableName); } // SubTableUtil
} // 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
} // isOrgLevelOnly /// <summary> /// Get Table model /// </summary> /// <returns>talble</returns> public MTable GetTable() { if (_table == null) { _table = MTable.Get(GetCtx(), GetAD_Table_ID()); } return(_table); } // getTable
/// <summary> /// function to check if user try to either delete or remove /// last column which is marked as Maintain Version to false /// then return false, if there is data in respective Version Table /// </summary> /// <param name="delete"></param> /// <returns>bool (true/false)</returns> public bool CheckVersions(bool delete) { bool check = true; StringBuilder sb = new StringBuilder("SELECT COUNT(AD_Column_ID) FROM AD_Column WHERE AD_Table_ID = " + GetAD_Table_ID() + " AND IsMaintainVersions = 'Y' AND AD_Column_ID != " + GetAD_Column_ID()); if (!delete) { if (!(Is_ValueChanged("IsMaintainVersions") && (Util.GetValueOfBool(Get_ValueOld("IsMaintainVersions")) && !Util.GetValueOfBool(Get_Value("IsMaintainVersions"))))) { check = false; } } else { if (!(Util.GetValueOfBool(Get_ValueOld("IsMaintainVersions")))) { check = false; } } if (check) { int countVerCols = Util.GetValueOfInt(DB.ExecuteScalar(sb.ToString(), null, Get_Trx())); if (countVerCols == 0) { sb.Clear(); sb.Append("SELECT TableName FROM AD_Table WHERE AD_Table_ID = " + GetAD_Table_ID()); string tableName = Util.GetValueOfString(DB.ExecuteScalar(sb.ToString(), null, Get_Trx())); sb.Clear(); DatabaseMetaData md = new DatabaseMetaData(); //get columns of a table DataSet dt = md.GetColumns("", DataBase.DB.GetSchema(), tableName + "_Ver"); md.Dispose(); // check whether version table exists in database if (dt != null && dt.Tables[0] != null && dt.Tables[0].Rows.Count > 0) { MTable tblVer = MTable.Get(GetCtx(), tableName + "_Ver"); if (tblVer.HasVersionData(tableName + "_Ver")) { // if Maintain Version is marked on Table Level then allow to remove Maintain Version from Column if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + GetAD_Table_ID(), null, Get_Trx())) == "Y") { return(true); } else { log.SaveError("VersionDataExists", Utility.Msg.GetElement(GetCtx(), "VersionDataExists")); return(false); } } } } } return(true); }
/// <summary> /// Get Table Name /// </summary> /// <returns>table name</returns> protected String GetTableName() { if (_tableName == null) { MTable table = MTable.Get(GetCtx(), GetAD_Table_ID()); _tableName = table.Get_TableName(); } return(_tableName); }
/// <summary> /// Get the Parent Table /// </summary> /// <param name="tab"></param> /// <returns></returns> public MTable GetParentTable(MWindow win, MTab currentTab) { MTab[] tabs = win.GetTabs(false, null); MTab tab = tabs[currentTab.GetTabLevel()]; if (currentTab.GetTabLevel() == 0) { return(null); } return(MTable.Get(GetCtx(), tab.GetAD_Table_ID())); }
} // getSql public String GetTableName() { int AD_Table_ID = GetAD_Table_ID(); if (AD_Table_ID != 0) { MTable table = MTable.Get(GetCtx(), AD_Table_ID); String tableName = table.GetTableName(); if (!VAdvantage.Utility.Util.IsEmpty(tableName)) { return(tableName); } } // FROM clause String from = GetFromClause().Trim(); StringTokenizer st = new StringTokenizer(from, " ,\t\n\r\f", false); int tokens = st.CountTokens(); if (tokens == 0) { return(null); } if (tokens == 1) { return(st.NextToken()); } String mainTable = st.NextToken(); if (st.HasMoreTokens()) { String next = st.NextToken(); if (next.Equals("RIGHT", StringComparison.OrdinalIgnoreCase) || next.Equals("LEFT", StringComparison.OrdinalIgnoreCase) || next.Equals("INNER", StringComparison.OrdinalIgnoreCase) || next.Equals("FULL", StringComparison.OrdinalIgnoreCase)) { return(mainTable); } return(next); } return(mainTable); } // getTableName
} // SyncMColumns /// <summary> /// Check Existence of Std columns and create them in AD and DB /// </summary> /// <param name="Context">context</param> /// <param name="tableName">name of the table</param> static void CheckStandardColumns(Context Context, String tableName) { MTable table = MTable.Get(Context, tableName); CheckStandardColumns(table, null); }
} // 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
/// <summary> /// Get Table Name /// </summary> /// <param name="ctx">context</param> /// <param name="AD_Table_ID">table id</param> /// <returns>table name</returns> public static String GetTableName(Ctx ctx, int AD_Table_ID) { return(MTable.Get(ctx, AD_Table_ID).GetTableName()); }
// // Change By Arpit to Create Parameters24th of August,2017 //On the Basis of User defined % for each quantity of Product to verify public static void CreateParameters(List <int> _ProductList, List <int> _ProductQty, int M_MoveConfirm_ID, int VA010_QUalityPlan_ID, int CurrentQty, List <int> M_MoveConfirmLine_ID, Ctx ctx, Trx Trx_Name) { StringBuilder _sql = new StringBuilder(); DataSet _ds = null; decimal _qtyPercentToVerify = 0; decimal _qtyFrom, _qtyTo, _qtyPercent = 0; try { _sql.Clear(); _sql.Append(@"SELECT NVL(VA010_PercentQtyToVerify,0)VA010_PercentQtyToVerify, NVL(VA010_ReceiptQtyFrom,0) VA010_ReceiptQtyFrom, NVL(VA010_ReceiptQtyTo,0) VA010_ReceiptQtyTo FROM VA010_CheckingQty WHERE IsActive='Y' AND VA010_Qualityplan_ID=" + VA010_QUalityPlan_ID + " AND AD_Client_ID=" + ctx.GetAD_Client_ID()); _ds = DB.ExecuteDataset(_sql.ToString(), null, Trx_Name); if (_ds != null && _ds.Tables[0].Rows.Count > 0) { for (Int32 k = 0; k < _ds.Tables[0].Rows.Count; k++) { _qtyFrom = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_ReceiptQtyFrom"]); _qtyTo = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_ReceiptQtyTo"]); _qtyPercent = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_PercentQtyToVerify"]); if (CurrentQty >= _qtyFrom && _qtyTo == 0) { _qtyPercentToVerify = _qtyPercent; k = _ds.Tables[0].Rows.Count; } else if (CurrentQty >= _qtyFrom && CurrentQty <= _qtyTo) { _qtyPercentToVerify = _qtyPercent; k = _ds.Tables[0].Rows.Count; } else { _qtyPercentToVerify = 100; } } } else { _qtyPercentToVerify = 100; } _sql.Clear(); _sql.Append(@"SELECT VA010_QualityParameters_ID, VA010_TestPrmtrList_ID FROM va010_AssgndParameters WHERE" + " VA010_QualityPlan_ID=" + VA010_QUalityPlan_ID + " AND IsActive='Y'"); _ds.Clear(); _ds = DB.ExecuteDataset(_sql.ToString(), null, Trx_Name); int _qty = 0; if (_ds != null) { if (_ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < _ProductList.Count; i++) { _qty = 0; _qty = (int)Math.Round((_ProductQty[i] * _qtyPercentToVerify) / 100, MidpointRounding.AwayFromZero); if (_qty == 0) { _qty = _ProductQty[i]; } if (_qty > _ProductQty[i]) { _qty = _ProductQty[i]; } for (int j = 0; j < _ds.Tables[0].Rows.Count; j++) { //Created Table object because not to use Mclass of Quality Control Module in our Base MTable table = MTable.Get(ctx, "VA010_MoveConfParameters"); PO pos = table.GetPO(ctx, 0, Trx_Name); pos.Set_ValueNoCheck("M_Product_ID", Util.GetValueOfInt(_ProductList[i])); pos.Set_ValueNoCheck("VA010_QualityParameters_ID", Util.GetValueOfInt(_ds.Tables[0].Rows[j]["VA010_QualityParameters_ID"])); pos.Set_ValueNoCheck("M_MovementLineConfirm_ID", Util.GetValueOfInt(M_MoveConfirmLine_ID[i])); pos.Set_ValueNoCheck("VA010_TestPrmtrList_ID", Util.GetValueOfInt(_ds.Tables[0].Rows[j]["VA010_TestPrmtrList_ID"])); pos.Set_ValueNoCheck("VA010_QuantityToVerify", Util.GetValueOfDecimal(_qty)); pos.Set_ValueNoCheck("AD_Client_ID", ctx.GetAD_Client_ID()); pos.Set_ValueNoCheck("AD_Org_ID", ctx.GetAD_Org_ID()); if (pos.Save(Trx_Name)) { ; } else { Trx_Name.Rollback(); Trx_Name.Close(); } } DB.ExecuteQuery(" UPDATE M_MovementLineConfirm SET VA010_QualCheckMark ='Y' WHERE M_MovementLineConfirm_ID=" + M_MoveConfirmLine_ID[i], null, Trx_Name); } } } } catch (Exception ex) { _log.Log(Level.SEVERE, _sql.ToString(), ex); } finally { _sql.Clear(); _ds.Dispose(); _qtyPercentToVerify = _qtyFrom = _qtyTo = _qtyPercent = 0; } }
/// <summary> /// Before Save /// </summary> /// <param name="newRecord"></param> /// <returns></returns> protected override Boolean BeforeSave(Boolean newRecord) { /* Disallow Negative Inventory cannot be checked if there are storage records * with negative onhand. */ if (Is_ValueChanged("IsDisallowNegativeInv") && IsDisallowNegativeInv()) { //String sql = "SELECT M_Product_ID FROM M_StorageDetail s " + // "WHERE s.QtyType = 'H' AND s.M_Locator_ID IN (SELECT M_Locator_ID FROM M_Locator l " + // "WHERE M_Warehouse_ID=" + GetM_Warehouse_ID() + " )" + // " GROUP BY M_Product_ID, M_Locator_ID " + // " HAVING SUM(s.Qty) < 0 "; String sql = "SELECT M_Product_ID FROM M_Storage s " + "WHERE s.M_Locator_ID IN (SELECT M_Locator_ID FROM M_Locator l " + "WHERE M_Warehouse_ID=" + GetM_Warehouse_ID() + " )" + " GROUP BY M_Product_ID, M_Locator_ID " + " HAVING SUM(s.Qty) < 0 "; IDataReader idr = null; Boolean negInvExists = false; try { idr = DB.ExecuteReader(sql, null, Get_TrxName()); if (idr.Read()) { negInvExists = true; } } catch (Exception e) { log.Log(Level.SEVERE, sql, e); } finally { if (idr != null) { idr.Close(); idr = null; } } if (negInvExists) { log.SaveError("Error", Msg.Translate(GetCtx(), "NegativeOnhandExists")); return(false); } } // Added by Vivek on 21/09/2017 suggested by Ravikant // Check if Drop ship warehouse already exist for the same organization // Check Column exist in DB -- Vivek on 17/10/2017 by Mukesh sir MTable tab = MTable.Get(GetCtx(), "M_Warehouse"); if (tab.Get_ColumnIndex("IsDropShip") > 0) { if (Util.GetValueOfInt(DB.ExecuteScalar("Select Count(*) From M_WareHouse Where AD_Org_ID=" + GetAD_Org_ID() + " AND IsDropShip='Y' AND IsActive='Y'")) > 0) { log.SaveError("Error", Msg.Translate(GetCtx(), "DropShipWarehouse")); return(false); } } if (GetAD_Org_ID() == 0) { int context_AD_Org_ID = GetCtx().GetAD_Org_ID(); if (context_AD_Org_ID != 0) { SetAD_Org_ID(context_AD_Org_ID); log.Warning("Changed Org to Context=" + context_AD_Org_ID); } else { log.SaveError("Error", Msg.Translate(GetCtx(), "Org0NotAllowed")); return(false); } } if ((newRecord || Is_ValueChanged("IsWMSEnabled") || Is_ValueChanged("IsDisallowNegativeInv")) && IsWMSEnabled() && !IsDisallowNegativeInv()) { log.SaveError("Error", Msg.Translate(GetCtx(), "NegativeInventoryDisallowedWMS")); return(false); } if (newRecord || Is_ValueChanged("Separator")) { if (GetSeparator() == null || GetSeparator().Length <= 0) { SetSeparator("*"); } } // JID_1888 Checks for the duplicate search key int count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(Value) FROM M_Warehouse WHERE Value= '" + GetValue() + "' AND M_Warehouse_ID !=" + GetM_Warehouse_ID() + " AND AD_Org_ID = " + GetAD_Org_ID())); if (count > 0) { log.SaveError("", Msg.GetMsg(GetCtx(), "SearchKeyUnique")); return(false); } //JID_1888 checks for the duplicate name int countName = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(Name) FROM M_Warehouse WHERE Name= '" + GetName() + "' AND M_Warehouse_ID !=" + GetM_Warehouse_ID() + " AND AD_Org_ID = " + GetAD_Org_ID())); if (countName > 0) { log.SaveError("", Msg.GetMsg(GetCtx(), "RequiredUniqueName")); return(false); } return(true); }