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