public static int fnGNTranRole(MRole objRole, string TransactionType, ref string Mensaje)
{
try
{
TextInfo ti = CultureInfo.CurrentCulture.TextInfo; //para capitalizar textos
MMEnterprisesEntities db = new MMEnterprisesEntities();
int IdRole;
int SqlResult;
SqlParameter paramOutIdRole = new SqlParameter();
paramOutIdRole.ParameterName = "@IdRole";
paramOutIdRole.SqlDbType = System.Data.SqlDbType.Int;
paramOutIdRole.Direction = System.Data.ParameterDirection.InputOutput;
paramOutIdRole.Value = objRole.IdRole;
SqlResult = db.Database.ExecuteSqlCommand("GNTranRole @TransactionType, @IdRole OUT, @NameRole, @Status ",
new SqlParameter[] {
new SqlParameter("@TransactionType", TransactionType),
paramOutIdRole,
new SqlParameter("@NameRole", ti.ToTitleCase(objRole.NameRole)),
new SqlParameter("@Status", objRole.Status)
}
);
IdRole = Int32.Parse(paramOutIdRole.Value.ToString());
if (IdRole != 0)
{
foreach (var item in objRole.RoleAcces)
{
SqlResult = db.Database.ExecuteSqlCommand("GNTranRoleAcces @IdRole, @IdOption, @blnVisible " +
", @blnCreate, @blnSearch, @blnEdit, @blnDelete ",
new SqlParameter[] {
new SqlParameter("@IdRole", IdRole),
new SqlParameter("@IdOption", item.IdOption),
new SqlParameter("@blnVisible", item.Visible),
new SqlParameter("@blnCreate", item.Create),
new SqlParameter("@blnSearch", item.Search),
new SqlParameter("@blnEdit", item.Edit),
new SqlParameter("@blnDelete", item.Delete)
}
);
}
//Mensaje = "Datos grabados exitosamente para el Código de empleado: (" + IdPerson + ").";
Mensaje = "Datos grabados exitosamente.";
}
else
{
Mensaje = "No se pudo realizar la transaccion, intente nuevamente.";
}
return(SqlResult);
}
catch (SqlException ex)
{
Mensaje = "Error al grabar datos: " + ex.Message;
return(0);
}
}
} // getOrgs
/// <summary>
/// Get Orgs - Add Summary Org
/// </summary>
/// <param name="list">list</param>
/// <param name="Summary_Org_ID">summary org</param>
/// <param name="Summary_Name">name</param>
/// <param name="role"></param>
private void GetOrgsAddSummary(List <KeyNamePair> list, int Summary_Org_ID, String Summary_Name, MRole role)
{
if (role == null)
{
return;
}
// Do we look for trees?
if (role.GetAD_Tree_Org_ID() == 0)
{
return;
}
// Summary Org - Get Dependents
MTree tree = MTree.Get(m_ctx, role.GetAD_Tree_Org_ID(), null);
String sql = "SELECT AD_Client_ID, AD_Org_ID, Name, IsSummary FROM AD_Org "
+ "WHERE IsActive='Y' AND AD_Org_ID IN (SELECT Node_ID FROM "
+ tree.GetNodeTableName()
+ " WHERE AD_Tree_ID='" + tree.GetAD_Tree_ID() + "' AND Parent_ID='" + Summary_Org_ID + "' AND IsActive='Y') "
+ "ORDER BY Name";
IDataReader dr = DataBase.DB.ExecuteReader(sql);
try
{
while (dr.Read())
{
// int AD_Client_ID = rs.getInt(1);
int AD_Org_ID = Utility.Util.GetValueOfInt(dr[1].ToString());
String Name = dr[2].ToString();
bool summary = "Y".Equals(dr[3].ToString());
//
if (summary)
{
GetOrgsAddSummary(list, AD_Org_ID, Name, role);
}
else
{
KeyNamePair p = new KeyNamePair(AD_Org_ID, Name);
if (!list.Contains(p))
{
list.Add(p);
}
}
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
finally
{
if (dr != null)
{
dr.Close();
}
}
} // getOrgAddSummary
} // getWarehouses
/* HTML5 */
/// <summary>
/// Load Preferences into Context for selected client.
/// <para>
/// Sets Org info in context and loads relevant field from
/// - AD_Client/Info,
/// - C_AcctSchema,
/// - C_AcctSchema_Elements
/// - AD_Preference
/// </para>
/// Assumes that the context is set for #AD_Client_ID, ##AD_User_ID, #AD_Role_ID
/// </summary>
/// <param name="org">org information</param>
/// <param name="warehouse">optional warehouse information</param>
/// <param name="timestamp">optional date</param>
/// <param name="printerName">optional printer info</param>
/// <returns>AD_Message of error (NoValidAcctInfo) or ""</returns>
public String LoadPreferences(string date, String printerName)
{
if (m_ctx.GetContext("#AD_Client_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Client_ID");
}
if (m_ctx.GetContext("##AD_User_ID").Length == 0)
{
throw new Exception("Missing Context ##AD_User_ID");
}
if (m_ctx.GetContext("#AD_Role_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Role_ID");
}
string dateS = m_ctx.GetContext("#Date");
DateTime dt = DateTime.Now;
long today = CommonFunctions.CurrentTimeMillis();
if (DateTime.TryParse(dateS, out dt))
{
today = CommonFunctions.CurrentTimeMillis(dt);
}
m_ctx.SetContext("#Date", today.ToString());
// Load User/Role Infos
MUser user = MUser.Get(m_ctx, m_ctx.GetAD_User_ID());
MUserPreference preference = user.GetPreference();
MRole role = MRole.GetDefault(m_ctx);
// Optional Printer
if (printerName == null)
{
printerName = "";
}
if (printerName.Length == 0 && preference.GetPrinterName() != null)
{
printerName = preference.GetPrinterName();
}
m_ctx.SetPrinterName(printerName);
if (preference.GetPrinterName() == null && printerName.Length > 0)
{
preference.SetPrinterName(printerName);
}
// Other
m_ctx.SetAutoCommit(preference.IsAutoCommit());
m_ctx.SetAutoNew(Ini.IsPropertyBool(Ini.P_A_NEW));
if (role.IsShowAcct())
{
m_ctx.SetContext("#ShowAcct", preference.IsShowAcct());
}
else
{
m_ctx.SetContext("#ShowAcct", "N");
}
m_ctx.SetContext("#ShowTrl", preference.IsShowTrl());
m_ctx.SetContext("#ShowAdvanced", preference.IsShowAdvanced());
String retValue = "";
int AD_Client_ID = m_ctx.GetAD_Client_ID();
// int AD_Org_ID = org.getKey();
// int AD_User_ID = Env.getAD_User_ID (m_ctx);
int AD_Role_ID = m_ctx.GetAD_Role_ID();
// Other Settings
m_ctx.SetContext("#YYYY", "Y");
// Report Page Size Element
m_ctx.SetContext("#REPORT_PAGE_SIZE", "500");
String sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE NAME = 'REPORT_PAGE_SIZE'";
IDataReader dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
Regex regex = new Regex(@"^[1-9]\d*$");
if (regex.IsMatch(dr[1].ToString()))
{
m_ctx.SetContext("#REPORT_PAGE_SIZE", (dr[1].ToString()));
}
}
}
dr.Close();
// Bulk Report Download
m_ctx.SetContext("#BULK_REPORT_DOWNLOAD", "N");
sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE NAME = 'BULK_REPORT_DOWNLOAD'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
Regex regex = new Regex(@"Y|N");
if (regex.IsMatch(dr[1].ToString()))
{
m_ctx.SetContext("#BULK_REPORT_DOWNLOAD", (dr[1].ToString()));
}
}
}
dr.Close();
// Set Default Value of System Config in Context
sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE ISACTIVE = 'Y' AND NAME NOT IN ('REPORT_PAGE_SIZE' , 'BULK_REPORT_DOWNLOAD')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
m_ctx.SetContext("#" + dr[0].ToString(), (dr[1].ToString()));
}
}
dr.Close();
// AccountSchema Info (first)
sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision "
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
dr = null;
try
{
int C_AcctSchema_ID = 0;
dr = DataBase.DB.ExecuteReader(sql);
if (!dr.Read())
{
// No Warning for System
if (AD_Role_ID != 0)
{
retValue = "NoValidAcctInfo";
}
}
else
{
// Accounting Info
C_AcctSchema_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
m_ctx.SetContext("$C_AcctSchema_ID", C_AcctSchema_ID);
m_ctx.SetContext("$C_Currency_ID", Utility.Util.GetValueOfInt(dr[1].ToString()));
m_ctx.SetContext("$HasAlias", dr[2].ToString());
m_ctx.SetContext("$CurrencyISO", dr[3].ToString());
m_ctx.SetStdPrecision(Utility.Util.GetValueOfInt(dr[4].ToString()));
}
dr.Close();
// Accounting Elements
sql = "SELECT ElementType "
+ "FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID='" + C_AcctSchema_ID + "'"
+ " AND IsActive='Y'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
m_ctx.SetContext("$Element_" + dr["ElementType"].ToString(), "Y");
}
dr.Close();
// This reads all relevant window neutral defaults
// overwriting superseeded ones. Window specific is read in Maintain
sql = "SELECT Attribute, Value, AD_Window_ID "
+ "FROM AD_Preference "
+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@##AD_User_ID@)"
+ " AND IsActive='Y' "
+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
// the last one overwrites - System - Client - User - Org - Window
sql = Utility.Env.ParseContext(m_ctx, 0, sql, false);
if (sql.Length == 0)
{
}
else
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
string AD_Window_ID = dr[2].ToString();
String at = "";
if (string.IsNullOrEmpty(AD_Window_ID))
{
at = "P|" + dr[0].ToString();
}
else
{
at = "P" + AD_Window_ID + "|" + dr[0].ToString();
}
String va = dr[1].ToString();
m_ctx.SetContext(at, va);
}
dr.Close();
}
// Default Values
sql = "SELECT t.TableName, c.ColumnName "
+ "FROM AD_Column c "
+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
+ " AND EXISTS (SELECT * FROM AD_Column cc "
+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
LoadDefault(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
//Ini.SaveProperties(Ini.IsClient());
// Country
m_ctx.SetContext("#C_Country_ID", MCountry.GetDefault(m_ctx).GetC_Country_ID());
m_ctx.SetShowClientOrg(Ini.IsShowClientOrg() ? "Y" : "N");
m_ctx.SetShowMiniGrid(Ini.GetProperty(Ini.P_Show_Mini_Grid));
return(retValue);
} // loadPreferences
} // getAD_Role_ID
/// <summary>
/// Load Preferences into Context for selected client.
/// <para>
/// Sets Org info in context and loads relevant field from
/// - AD_Client/Info,
/// - C_AcctSchema,
/// - C_AcctSchema_Elements
/// - AD_Preference
/// </para>
/// Assumes that the context is set for #AD_Client_ID, ##AD_User_ID, #AD_Role_ID
/// </summary>
/// <param name="org">org information</param>
/// <param name="warehouse">optional warehouse information</param>
/// <param name="timestamp">optional date</param>
/// <param name="printerName">optional printer info</param>
/// <returns>AD_Message of error (NoValidAcctInfo) or ""</returns>
public String LoadPreferences(KeyNamePair org,
KeyNamePair warehouse, DateTime timestamp, String printerName)
{
m_org = org;
if (m_ctx == null || org == null)
{
throw new ArgumentException("Required parameter missing");
}
if (m_ctx.GetContext("#AD_Client_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Client_ID");
}
if (m_ctx.GetContext("##AD_User_ID").Length == 0)
{
throw new Exception("Missing Context ##AD_User_ID");
}
if (m_ctx.GetContext("#AD_Role_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Role_ID");
}
// Org Info - assumes that it is valid
m_ctx.SetAD_Org_ID(org.GetKey());
m_ctx.SetContext("#AD_Org_Name", org.GetName());
Ini.SetProperty(Ini.P_ORG, org.GetName());
// Warehouse Info
if (warehouse != null)
{
m_ctx.SetContext("#M_Warehouse_ID", warehouse.GetKey());
Ini.SetProperty(Ini.P_WAREHOUSE, warehouse.GetName());
}
// Date (default today)
long today = CommonFunctions.CurrentTimeMillis();
if (timestamp != null)
{
today = CommonFunctions.CurrentTimeMillis(timestamp);
}
m_ctx.SetContext("#Date", today.ToString());
// Load User/Role Info
MUser user = MUser.Get(m_ctx, GetAD_User_ID());
MUserPreference preference = user.GetPreference();
MRole role = MRole.GetDefault(m_ctx, true);
// Optional Printer
if (printerName == null)
{
printerName = "";
}
if (printerName.Length == 0 && preference.GetPrinterName() != null)
{
printerName = preference.GetPrinterName();
}
m_ctx.SetPrinterName(printerName);
if (preference.GetPrinterName() == null && printerName.Length > 0)
{
preference.SetPrinterName(printerName);
}
// Other
m_ctx.SetAutoCommit(preference.IsAutoCommit());
m_ctx.SetAutoNew(Ini.IsPropertyBool(Ini.P_A_NEW));
if (role.IsShowAcct())
{
m_ctx.SetContext("#ShowAcct", preference.IsShowAcct());
}
else
{
m_ctx.SetContext("#ShowAcct", "N");
}
m_ctx.SetContext("#ShowTrl", preference.IsShowTrl());
m_ctx.SetContext("#ShowAdvanced", preference.IsShowAdvanced());
String retValue = "";
int AD_Client_ID = m_ctx.GetAD_Client_ID();
// int AD_Org_ID = org.getKey();
// int AD_User_ID = Env.getAD_User_ID (m_ctx);
int AD_Role_ID = m_ctx.GetAD_Role_ID();
// Other Settings
m_ctx.SetContext("#YYYY", "Y");
// AccountSchema Info (first)
String sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision "
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
IDataReader dr = null;
try
{
int C_AcctSchema_ID = 0;
dr = DataBase.DB.ExecuteReader(sql);
if (!dr.Read())
{
// No Warning for System
if (AD_Role_ID != 0)
{
retValue = "NoValidAcctInfo";
}
}
else
{
// Accounting Info
C_AcctSchema_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
m_ctx.SetContext("$C_AcctSchema_ID", C_AcctSchema_ID);
m_ctx.SetContext("$C_Currency_ID", Utility.Util.GetValueOfInt(dr[1].ToString()));
m_ctx.SetContext("$HasAlias", dr[2].ToString());
m_ctx.SetContext("$CurrencyISO", dr[3].ToString());
m_ctx.SetStdPrecision(Utility.Util.GetValueOfInt(dr[4].ToString()));
}
dr.Close();
// Accounting Elements
sql = "SELECT ElementType "
+ "FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID='" + C_AcctSchema_ID + "'"
+ " AND IsActive='Y'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
m_ctx.SetContext("$Element_" + dr["ElementType"].ToString(), "Y");
}
dr.Close();
// This reads all relevant window neutral defaults
// overwriting superseeded ones. Window specific is read in Maintain
sql = "SELECT Attribute, Value, AD_Window_ID "
+ "FROM AD_Preference "
+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@##AD_User_ID@)"
+ " AND IsActive='Y' "
+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
// the last one overwrites - System - Client - User - Org - Window
sql = Utility.Env.ParseContext(m_ctx, 0, sql, false);
if (sql.Length == 0)
{
}
else
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
string AD_Window_ID = dr[2].ToString();
String at = "";
if (string.IsNullOrEmpty(AD_Window_ID))
{
at = "P|" + dr[0].ToString();
}
else
{
at = "P" + AD_Window_ID + "|" + dr[0].ToString();
}
String va = dr[1].ToString();
m_ctx.SetContext(at, va);
}
dr.Close();
}
// Default Values
sql = "SELECT t.TableName, c.ColumnName "
+ "FROM AD_Column c "
+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
+ " AND EXISTS (SELECT * FROM AD_Column cc "
+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
LoadDefault(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
Ini.SaveProperties(Ini.IsClient());
// Country
m_ctx.SetContext("#C_Country_ID", MCountry.GetDefault(m_ctx).GetC_Country_ID());
m_ctx.SetShowClientOrg(Ini.IsShowClientOrg() ? "Y" : "N");
m_ctx.SetShowMiniGrid(Ini.GetProperty(Ini.P_Show_Mini_Grid));
return(retValue);
} // loadPreferences
/// <summary>
///
/// </summary>
/// <param name="client"></param>
/// <returns></returns>
public KeyNamePair[] GetOrgs(KeyNamePair client)
{
if (client == null)
{
throw new ArgumentException("Client missing");
}
// Web Store Login
if (m_store != null)
{
return new KeyNamePair[] { new KeyNamePair(m_store.GetAD_Org_ID(), m_store.GetName() + " Org") }
}
;
if (m_ctx.GetContext("#AD_Role_ID").Length == 0) // could be number 0
{
throw new Exception("Missing Context #AD_Role_ID");
}
int AD_Role_ID = m_ctx.GetAD_Role_ID();
int AD_User_ID = m_ctx.GetAD_User_ID();
// s_log.fine("Client: " + client.toStringX() + ", AD_Role_ID=" + AD_Role_ID);
// get Client details for role
List <KeyNamePair> list = new List <KeyNamePair>();
KeyNamePair[] retValue = null;
//
String sql = "SELECT o.AD_Org_ID,o.Name,o.IsSummary " // 1..3
+ "FROM AD_Role r, AD_Client c"
+ " INNER JOIN AD_Org o ON (c.AD_Client_ID=o.AD_Client_ID OR o.AD_Org_ID=0) "
+ "WHERE r.AD_Role_ID='" + AD_Role_ID + "'" // #1
+ " AND c.AD_Client_ID='" + client.GetKey() + "'" // #2
+ " AND o.IsActive='Y' AND o.IsSummary='N'"
+ " AND (r.IsAccessAllOrgs='Y' "
+ "OR (r.IsUseUserOrgAccess='N' AND o.AD_Org_ID IN (SELECT AD_Org_ID FROM AD_Role_OrgAccess ra "
+ "WHERE ra.AD_Role_ID=r.AD_Role_ID AND ra.IsActive='Y')) "
+ "OR (r.IsUseUserOrgAccess='Y' AND o.AD_Org_ID IN (SELECT AD_Org_ID FROM AD_User_OrgAccess ua "
+ "WHERE ua.AD_User_ID='" + AD_User_ID + "' AND ua.IsActive='Y'))" // #3
+ ") "
+ "ORDER BY o.Name";
//
MRole role = null;
IDataReader dr = null;
try
{
dr = DataBase.DB.ExecuteReader(sql);
// load Orgs
while (dr.Read())
{
int AD_Org_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
String Name = dr[1].ToString();
bool summary = "Y".Equals(dr[2].ToString());
if (summary)
{
if (role == null)
{
role = MRole.Get(m_ctx, AD_Role_ID, AD_User_ID, false);
}
GetOrgsAddSummary(list, AD_Org_ID, Name, role);
}
else
{
KeyNamePair p = new KeyNamePair(AD_Org_ID, Name);
if (!list.Contains(p))
{
list.Add(p);
}
}
}
dr.Close();
//
retValue = new KeyNamePair[list.Count];
retValue = list.ToArray();
}
catch
{
if (dr != null)
{
dr.Close();
}
retValue = null;
}
// No Orgs
if (retValue == null || retValue.Length == 0)
{
return(null);
}
// Client Info
m_ctx.SetContext("#AD_Client_ID", client.GetKey());
m_ctx.SetContext("#AD_Client_Name", client.GetName());
Ini.SetProperty(Ini.P_CLIENT, client.GetName());
return(retValue);
} // getOrgs
/**
* Get Sql to value for the bar chart
* @param restrictions array of goal restrictions
* @param MeasureDisplay scope of this value
* @param MeasureDataType data type
* @param startDate optional report start date
* @param role role
* @return sql for Bar Chart
*/
public String GetSqlBarChart(MGoalRestriction[] restrictions, String measureDisplay,
String measureDataType, DateTime?startDate, MRole role)
{
String dateColumn = "Created";
String orgColumn = "AD_Org_ID";
String bpColumn = "C_BPartner_ID";
String pColumn = "M_Product_ID";
//
StringBuilder sb = new StringBuilder("SELECT COUNT(*), ");
String groupBy = null;
String orderBy = null;
//
if (MMeasure.MEASUREDATATYPE_QtyAmountInTime.Equals(measureDataType) &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String trunc = "D";
if (MGoal.MEASUREDISPLAY_Year.Equals(measureDisplay))
{
trunc = "Y";
}
else if (MGoal.MEASUREDISPLAY_Quarter.Equals(measureDisplay))
{
trunc = "Q";
}
else if (MGoal.MEASUREDISPLAY_Month.Equals(measureDisplay))
{
trunc = "MM";
}
else if (MGoal.MEASUREDISPLAY_Week.Equals(measureDisplay))
{
trunc = "W";
}
// else if (MGoal.MEASUREDISPLAY_Day.equals(MeasureDisplay))
// ;
orderBy = "TRUNC(" + dateColumn + ",'" + trunc + "')";
//jz 0 is column position in EDB, Oracle doesn't take alias in group by
// groupBy = orderBy + ", 0 ";
// sb.append(groupBy)
groupBy = orderBy + ", CAST(0 AS INTEGER) ";
sb.Append(groupBy)
.Append("FROM R_Request ");
}
else
{
orderBy = "s.SeqNo";
groupBy = "COALESCE(s.Name,TO_NCHAR('-')), s.R_Status_ID, s.SeqNo ";
sb.Append(groupBy)
.Append("FROM R_Request LEFT OUTER JOIN R_Status s ON (R_Request.R_Status_ID=s.R_Status_ID) ");
}
// Where
sb.Append("WHERE R_Request.R_RequestType_ID=").Append(GetR_RequestType_ID())
.Append(" AND R_Request.Processed<>'Y'");
// Date Restriction
if (startDate != null &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String dateString = DataBase.DB.TO_DATE((DateTime?)startDate);
sb.Append(" AND ").Append(dateColumn)
.Append(">=").Append(dateString);
} // date
//
String sql = MMeasureCalc.AddRestrictions(sb.ToString(), false, restrictions, role,
"R_Request", orgColumn, bpColumn, pColumn, GetCtx());
if (groupBy != null)
{
sql += " GROUP BY " + groupBy + " ORDER BY " + orderBy;
}
//
log.Fine(sql);
return(sql);
}
/**
* Checks the access rights of the given role/client for the given document actions.
* @param clientId
* @param roleId
* @param docTypeId
* @param options
* @param maxIndex
* @return number of valid actions in the String[] options
*/
public static string[] checkActionAccess(Ctx ctx, int clientId, int roleId, int docTypeId, String[] options, ref int maxIndex)
{
return(MRole.Get(ctx, roleId).checkActionAccess(clientId, docTypeId, options, ref maxIndex));
}
/// <summary>
/// Create New Role
/// </summary>
/// <param name="Name"></param>
/// <param name="userLevel"></param>
/// <param name="OrgID"></param>
/// <returns></returns>
public String AddNewRole(string Name, string userLevel, List <int> OrgID)
{
string info = "";
string msg;
int AD_Role_Table_ID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName='AD_Role'", null, null));
try
{
string sql = @"SELECT AD_Column_ID,ColumnName,
defaultvalue
FROM AD_Column
WHERE AD_Table_ID =" + AD_Role_Table_ID + @"
AND isActive ='Y'
AND defaultvalue IS NOT NULL";
DataSet ds = DB.ExecuteDataset(sql); // Get Default Values
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return(VAdvantage.Utility.Msg.GetMsg(ctx, "DefaultValueNotFound"));
}
MRole role = new MRole(ctx, 0, null);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++) // Setting Default Values
{
string value = ds.Tables[0].Rows[i]["DefaultValue"].ToString();
if (value.StartsWith("@"))
{
value = value.Substring(0, value.Length - 1);
string columnName = value.Substring(value.IndexOf("@") + 1);
value = ctx.GetContext(columnName); // get global context
}
role.Set_Value(ds.Tables[0].Rows[i]["ColumnName"].ToString(), value);
}
role.SetIsManual(true);
role.SetName(Name);
role.SetUserLevel(userLevel);
if (role.Save())
{
if (OrgID != null)
{
for (int i = 0; i < OrgID.Count; i++) // Assigning org access to role
{
MOrg org = new MOrg(ctx, OrgID[i], null);
MRoleOrgAccess roles = new MRoleOrgAccess(org, role.GetAD_Role_ID());
roles.SetAD_Client_ID(ctx.GetAD_Client_ID());
roles.SetAD_Org_ID(OrgID[i]);
roles.SetIsReadOnly(false);
roles.Save();
}
}
}
else
{
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
if (ppE != null)
{
msg = ppE.GetValue();
info = ppE.GetName();
}
}
return(info);
}
catch (Exception ex)
{
return(ex.Message);
}
//rr.Set_Value(
}
/// <summary>
/// return org access list aginst client and role of user
/// </summary>
/// <param name="AD_Role_ID">role id </param>
/// <param name="AD_User_ID">user id</param>
/// <param name="AD_Client_ID"> client id</param>
/// <returns></returns>
public static List <KeyNamePair> GetOrgs(int AD_Role_ID, int AD_User_ID, int AD_Client_ID)
{
List <KeyNamePair> list = new List <KeyNamePair>();
String sql = "SELECT o.AD_Org_ID,o.Name,o.IsSummary " // 1..3
+ "FROM AD_Role r, AD_Client c"
+ " INNER JOIN AD_Org o ON (c.AD_Client_ID=o.AD_Client_ID OR o.AD_Org_ID=0) "
+ "WHERE r.AD_Role_ID='" + AD_Role_ID + "'" // #1
+ " AND c.AD_Client_ID='" + AD_Client_ID + "'" // #2
+ " AND o.IsActive='Y' AND o.IsSummary='N' AND o.IsCostCenter='N' AND o.IsProfitCenter='N' "
+ " AND (r.IsAccessAllOrgs='Y' "
+ "OR (r.IsUseUserOrgAccess='N' AND o.AD_Org_ID IN (SELECT AD_Org_ID FROM AD_Role_OrgAccess ra "
+ "WHERE ra.AD_Role_ID=r.AD_Role_ID AND ra.IsActive='Y')) "
+ "OR (r.IsUseUserOrgAccess='Y' AND o.AD_Org_ID IN (SELECT AD_Org_ID FROM AD_User_OrgAccess ua "
+ "WHERE ua.AD_User_ID='" + AD_User_ID + "' AND ua.IsActive='Y'))" // #3
+ ") "
+ "ORDER BY o.Name";
//
MRole role = null;
IDataReader dr = null;
//list.Add(new KeyNamePair(-1, "Select"));
try
{
dr = DB.ExecuteReader(sql);
// load Orgs
Ctx ctx = new Ctx();
while (dr.Read())
{
int AD_Org_ID = Util.GetValueOfInt(dr[0].ToString());
String Name = dr[1].ToString();
bool summary = "Y".Equals(dr[2].ToString());
if (summary)
{
if (role == null)
{
ctx.SetAD_Client_ID(AD_Client_ID);
role = MRole.Get(ctx, AD_Role_ID, AD_User_ID, false);
}
GetOrgsAddSummary(list, AD_Org_ID, Name, role, ctx);
}
else
{
KeyNamePair p = new KeyNamePair(AD_Org_ID, Name);
if (!list.Contains(p))
{
list.Add(p);
}
}
}
dr.Close();
//
//retValue = new KeyNamePair[list.Count];
// retValue = list.ToArray();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
// No Orgs
return(list);
} // getOrgs
/// <summary>
/// Get All Users of current client and org
/// </summary>
/// <param name="searchText"></param>
/// <param name="sortBy"></param>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <UserInfo> GetUserInfo(string searchText, int sortBy, int pageNo, int pageSize)
{
List <UserInfo> uInfo = new List <UserInfo>();
int UserTableID = MTable.Get_Table_ID("AD_User");
int UserWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='User'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(UserWindowID))
{
return(uInfo);
}
if (!MRole.GetDefault(ctx).IsTableAccess(UserTableID, false))
{
return(uInfo);
}
string sql = @"SELECT AD_User.Name,
AD_User.Email,
AD_User.AD_User_ID,
AD_User.IsActive,
AD_User.AD_Image_ID,
AD_User.AD_Client_ID,
AD_User.AD_Org_ID,
C_Country.Name as CName
FROM AD_User
LEFT OUTER JOIN C_LOcation
ON AD_User.C_Location_ID=C_Location.C_Location_ID
LEFT OUTER JOIN C_Country
ON C_Country.C_Country_ID=C_Location.C_Country_ID WHERE IsLoginUser='******' ";
if (!String.IsNullOrEmpty(searchText))
{
sql += " AND ( upper(AD_User.Value) like Upper('%" + searchText + "%') OR upper(AD_User.Name) like Upper('%" + searchText + "%') OR upper(AD_User.Email) like Upper('%" + searchText + "%'))";
}
sql += " ORDER BY AD_User.IsActive desc";
if (sortBy == -1 || sortBy == 1)
{
sql += " , upper(AD_User.Name) ASC";
}
else if (sortBy == 2)
{
sql += " , upper(AD_User.Value) ASC";
}
else if (sortBy == 3)
{
sql += " , upper(AD_User.Email) ASC";
}
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_User", true, false);
DataSet ds = DB.ExecuteDatasetPaging(sql, pageNo, pageSize);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserInfo userInfo = new UserInfo();
userInfo.HasAccess = MRole.GetDefault(ctx).IsRecordAccess(UserTableID, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]), true);
userInfo.Username = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
userInfo.Email = Convert.ToString(ds.Tables[0].Rows[i]["Email"]);
userInfo.AD_UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]);
userInfo.AD_OrgID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Org_ID"]);
userInfo.AD_ClientID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Client_ID"]);
userInfo.Country = Convert.ToString(ds.Tables[0].Rows[i]["CName"]);
userInfo.UserTableID = UserTableID;
userInfo.UserWindowID = UserWindowID;
userInfo.IsActive = ds.Tables[0].Rows[i]["IsActive"].ToString() == "Y" ? true : false;
userInfo.IsUpdate = MRole.GetDefault(ctx).CanUpdate(userInfo.AD_ClientID, userInfo.AD_OrgID, userInfo.UserTableID, userInfo.AD_UserID, false);
if (ds.Tables[0].Rows[i]["AD_Image_ID"] != DBNull.Value && ds.Tables[0].Rows[i]["AD_Image_ID"] != null && Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]) > 0)
{
MImage mimg = new MImage(ctx, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]), null);
var imgfll = mimg.GetThumbnailURL(46, 46);
userInfo.UserImage = imgfll;
if (userInfo.UserImage == "FileDoesn'tExist" || userInfo.UserImage == "NoRecordFound")
{
userInfo.UserImage = "";
}
}
else
{
userInfo.UserImage = "";
}
uInfo.Add(userInfo);
}
}
return(uInfo);
}
/// <summary>
/// Get Information of group like windows, forms, processes, workflows it contained.
/// </summary>
/// <param name="groupID"></param>
/// <returns></returns>
public GroupChildInfo GetGroupInfo(int groupID)
{
GroupChildInfo gInfo = new GroupChildInfo();
string sql = "SELECT Name, Description from AD_GroupInfo WHERE AD_GroupInfo_ID=" + groupID;
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_GroupInfo", true, false);
DataSet ds = DB.ExecuteDataset(sql);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return(gInfo);
}
if (ds.Tables[0].Rows[0]["Name"] != null)
{
gInfo.GroupName = ds.Tables[0].Rows[0]["Name"].ToString();
}
if (ds.Tables[0].Rows[0]["Description"] != null)
{
gInfo.Description = ds.Tables[0].Rows[0]["Description"].ToString();
}
sql = @"SELECT AD_WIndow.Name FROM AD_Group_Window JOIN AD_WIndow
ON AD_Group_Window.AD_Window_ID=AD_Window.AD_Window_ID
WHERE AD_Group_Window.IsActive='Y' AND AD_Group_Window.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_WIndow.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder windows = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (windows.Length > 0)
{
windows.Append(", ");
}
windows.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.WindowName = windows.ToString();
sql = @"SELECT AD_Form.Name FROM AD_Group_Form JOIN AD_Form
ON AD_Group_Form.AD_Form_ID=AD_Form.AD_Form_ID
WHERE AD_Group_Form.IsActive='Y' AND AD_Group_Form.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_Form.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder forms = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (forms.Length > 0)
{
forms.Append(", ");
}
forms.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.FormName = forms.ToString();
sql = @"SELECT AD_Process.Name FROM AD_Group_Process JOIN AD_Process
ON AD_Group_Process.AD_Process_ID=AD_Process.AD_Process_ID
WHERE AD_Group_Process.IsActive='Y' AND AD_Group_Process.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_Process.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder processes = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (processes.Length > 0)
{
processes.Append(", ");
}
processes.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.ProcessName = processes.ToString();
sql = @"SELECT AD_workflow.Name FROM AD_Group_workflow JOIN AD_workflow
ON AD_Group_workflow.AD_workflow_ID=AD_workflow.AD_workflow_ID
WHERE AD_Group_workflow.IsActive='Y' AND AD_Group_workflow.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_workflow.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder workflows = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (workflows.Length > 0)
{
workflows.Append(", ");
}
workflows.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.WorkflowName = workflows.ToString();
return(gInfo);
}
/// <summary>
/// Get All the groups of current client. If a group is assigned to current role then it will be checked otherwise unchecked.
/// </summary>
/// <param name="AD_Role_ID"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <GroupInfo> GetGroupInfo(int AD_Role_ID, string name)
{
List <GroupInfo> gInfo = new List <GroupInfo>();
int groupWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='Group Rights'", null, null));
if (MRole.GetDefault(ctx).GetWindowAccess(groupWindowID) == null || !(bool)MRole.GetDefault(ctx).GetWindowAccess(groupWindowID))
{
return(gInfo);
}
string sql = @"SELECT Name, AD_GroupInfo_ID FROM AD_GroupInfo WHERE IsActive='Y'";
if (!string.IsNullOrEmpty(name))
{
sql += " AND upper(Name) like ('%" + name.ToUpper() + "%')";
}
sql += " ORDER BY upper(name) ";
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_GroupInfo", true, false);
DataSet ds = DB.ExecuteDataset(sql); // get All Groups.
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
GroupInfo roleInfo = new GroupInfo();
roleInfo.AD_Group_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_GroupInfo_ID"]);
roleInfo.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
roleInfo.IsAssignedToUser = false;
roleInfo.GroupWindowID = groupWindowID;
gInfo.Add(roleInfo);
}
}
sql = "select ad_role_group.AD_GroupInfo_ID,ad_role_group.IsActive from ad_role_group join AD_GroupInfo on ad_role_group.AD_GroupInfo_ID=AD_GroupInfo.AD_GroupInfo_ID WHERE ad_role_group.AD_Role_ID=" + AD_Role_ID;
if (!string.IsNullOrEmpty(name))
{
sql += " AND upper(AD_GroupInfo.Name) like ('%" + name.ToUpper() + "%')";
}
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_GroupInfo", true, false);
DataSet dsURoles = DB.ExecuteDataset(sql); // Get All groups that are assigned to current Role...
if (dsURoles != null && dsURoles.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsURoles.Tables[0].Rows.Count; i++)
{
GroupInfo ginofs = gInfo.Where(a => a.AD_Group_ID == Convert.ToInt32(dsURoles.Tables[0].Rows[i]["AD_GroupInfo_ID"])).FirstOrDefault();
if (ginofs != null)
{
if (dsURoles.Tables[0].Rows[i]["IsActive"].ToString().Equals("Y")) // if a group is assigned to current role, then show it as checked
{
ginofs.IsAssignedToUser = true;
}
else
{
ginofs.IsAssignedToUser = false;
}
}
}
}
gInfo = gInfo.OrderBy(a => !a.IsAssignedToUser).ToList(); // Show assigned Users on the Top.
return(gInfo);
}
/// <summary>
/// Get all the roles of current User. if a role is assigned then it will be shown as checked otherwise unchecked
/// </summary>
/// <param name="AD_User_ID"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <RolesInfo> GetRoleInfo(int AD_User_ID, string name)
{
List <RolesInfo> rInfo = new List <RolesInfo>();
int RoleWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='Role'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(RoleWindowID))
{
return(rInfo);
}
int UserTableID = MTable.Get_Table_ID("AD_User");
bool IsUpdate = true;
String sql = "SELECT AD_Client_ID, AD_ORg_ID from AD_User WHERE AD_User_ID=" + AD_User_ID;
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
IsUpdate = MRole.GetDefault(ctx).CanUpdate(Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Client_ID"]), Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Org_ID"]), UserTableID, AD_User_ID, false);
}
sql = @"Select AD_Role.AD_Role_ID, AD_Role.Name from AD_Role WHERE AD_Role.AD_Client_ID=" + ctx.GetAD_Client_ID() + " AND AD_Role.AD_Role_ID > 0 AND IsActive='Y'";
// string sql = @"SELECT AD_Role.AD_Role_ID,
// AD_Role.Name
// FROM AD_Role
// JOIN ad_user_roles
//
// on AD_Role.AD_role_ID=ad_user_roles.AD_Role_ID
// WHERE AD_User_Roles.IsActive='Y' AND AD_Role.IsActive='Y' AND AD_User_Roles.AD_User_ID=" + ctx.GetAD_User_ID();
if (name != null && name.Length > 0)
{
sql += " AND upper(AD_Role.Name) like ('%" + name.ToUpper() + "%')";
}
sql += " ORDER BY upper(AD_Role.Name)";
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_Role", true, false);
ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
RolesInfo roleInfo = new RolesInfo();
roleInfo.AD_Role_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Role_ID"]);
roleInfo.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
roleInfo.IsAssignedToUser = false;
roleInfo.roleWindowID = RoleWindowID;
roleInfo.IsUpdate = IsUpdate;
rInfo.Add(roleInfo);
}
sql = "Select AD_Role_ID, IsActive from ad_user_roles where AD_User_ID=" + AD_User_ID;
DataSet dsURoles = DB.ExecuteDataset(sql);
if (dsURoles != null && dsURoles.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsURoles.Tables[0].Rows.Count; i++)
{
RolesInfo rrinfo = rInfo.Where(a => a.AD_Role_ID == Convert.ToInt32(dsURoles.Tables[0].Rows[i]["AD_Role_ID"])).FirstOrDefault();
if (rrinfo != null)
{
if (dsURoles.Tables[0].Rows[i]["IsActive"].ToString().Equals("Y"))
{
rrinfo.IsAssignedToUser = true;
}
else
{
rrinfo.IsAssignedToUser = false;
}
}
}
}
}
rInfo = rInfo.OrderBy(a => !a.IsAssignedToUser).ToList();
return(rInfo);
}
} // setParameterFromDB
/// <summary>
/// function will accept columnName and Ids selected. Will Fetch information from Default tree hierarchy and get child records accordingly.
/// </summary>
/// <param name="_ctx"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <param name="result"></param>
/// <returns>Name of orgs separated bY commas, and IDS in Reference Object</returns>
private static string GetRecursiveParameterValue(Ctx _ctx, string columnName, string value, ref string result, bool ShowChildOfSelected)
{
string tableName = columnName.Substring(0, columnName.Length - 3);
string[] values = value.Split(',');
String eSql = "";
string result1 = "";
StringBuilder finalResult = new StringBuilder();
StringBuilder nonSummaryResult = new StringBuilder();
if (values.Length > 0)
{
// Get Default Heirarchy
string sqla = @"SELECT PA_HIERARCHY_id FROM PA_Hierarchy WHERE ISACTIVE ='Y'
ORDER BY ISDEFAULT DESC ,PA_HIERARCHY_id ASC";
sqla = MRole.GetDefault(_ctx).AddAccessSQL(sqla, "PA_Hierarchy", true, true);
object ID = DB.ExecuteScalar(sqla);
int _PA_Hierarchy_ID = 0;
if (ID != null && ID != DBNull.Value)
{
_PA_Hierarchy_ID = Util.GetValueOfInt(ID);
}
Language _language = Language.GetLanguage(_ctx.GetAD_Language());
//Get Query to fetch identifier value from table based on column selected. it will be used to display identifires on for parameter in report.
eSql = VLookUpFactory.GetLookup_TableDirEmbed(_language, columnName, columnName.Substring(0, columnName.Length - 3));
for (int i = 0; i < values.Length; i++)
{
if (!string.IsNullOrEmpty(values[i]))
{
//try
//{
string sqlCheckSummary = "SELECT IsSummary FROM " + tableName + " WHERE " + columnName + "=" + values[i];
object val = DB.ExecuteScalar(sqlCheckSummary);
if (val != null && val != DBNull.Value)
{
if (val.ToString().Equals("N")) // If non-summary is selected then add it string and continue to next ID
{
if (nonSummaryResult.Length > 0)
{
nonSummaryResult.Append(", " + values[i]);
}
else
{
nonSummaryResult.Append(values[i]);
}
continue;
}
}
//}
//catch
//{
// result = "";
// continue;
//}
// Fetch child records from tree hierarchy based on ID selected.
if (columnName.Equals("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Organization, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_BPartner_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_BPartner, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("M_Product_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Product, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Project_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Project, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("AD_OrgTrx_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_OrgTrx, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("Account_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Account, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Campaign_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Campaign, Convert.ToInt32(values[i]));
}
if (result1.IndexOf("(") > -1)
{
result1 = result1.Substring(result1.IndexOf("(") + 1);
result1 = result1.Substring(0, result1.IndexOf(")"));
}
else
{
result1 = result1.Substring(result1.IndexOf("=") + 1);
}
//create list of sleected IDs in stringbuilder
if (result1 != values[i] && result1.Length > 0)
{
if (finalResult.Length > 0)
{
finalResult.Append(", " + result1);
}
else
{
finalResult.Append(result1);
}
}
}
}
}
StringBuilder identifiedsName = new StringBuilder();
if (finalResult.Length > 0)
{
if (finalResult.ToString().IndexOf(",") > -1)
{
eSql = eSql + " AND " + columnName + " IN (" + finalResult.ToString() + ")";
}
else
{
eSql = eSql + " AND " + columnName + " = " + finalResult.ToString();
}
//eSql = eSql + " AND " + result1;
//if (!string.IsNullOrEmpty(finalResult.ToString()))
//{
// result = result + "," + finalResult.ToString();
//}
DataSet dsIdeintifiers = DB.ExecuteDataset(eSql);
if (ShowChildOfSelected && (dsIdeintifiers != null && dsIdeintifiers.Tables[0].Rows.Count > 0))
{
for (int s = 0; s < dsIdeintifiers.Tables[0].Rows.Count; s++)
{
if (identifiedsName.Length > 0)
{
identifiedsName.Append(", ");
}
identifiedsName.Append(dsIdeintifiers.Tables[0].Rows[s][0]);
}
}
}
if (nonSummaryResult.Length > 0 || finalResult.Length > 0)
{
if (nonSummaryResult.Length > 0)
{
result = nonSummaryResult.ToString();
}
if (finalResult.Length > 0)
{
if (result.Length > 0)
{
result += ", ";
}
result += finalResult.ToString();
}
}
if (identifiedsName != null)
{
return(identifiedsName.ToString());
}
return("");
}
/**
* Update/save Goals with Projects
* @return true if updated
*/
private Boolean UpdateProjects()
{
if (!MEASURETYPE_Project.Equals(GetMeasureType()) ||
GetC_ProjectType_ID() == 0)
{
return(false);
}
MGoal[] goals = MGoal.GetMeasureGoals(GetCtx(), GetPA_Measure_ID());
for (int i = 0; i < goals.Length; i++)
{
MGoal goal = goals[i];
// Find Role
MRole role = null;
if (goal.GetAD_Role_ID() != 0)
{
role = MRole.Get(GetCtx(), goal.GetAD_Role_ID());
}
else if (goal.GetAD_User_ID() != 0)
{
MUser user = MUser.Get(GetCtx(), goal.GetAD_User_ID());
MRole[] roles = user.GetRoles(goal.GetAD_Org_ID());
if (roles.Length > 0)
{
role = roles[0];
}
}
if (role == null)
{
role = MRole.GetDefault(GetCtx(), false); // could result in wrong data
}
//
Decimal? ManualActual = null;
MProjectType pt = MProjectType.Get(GetCtx(), GetC_ProjectType_ID());
String sql = pt.GetSqlPI(goal.GetRestrictions(false),
goal.GetMeasureScope(), GetMeasureDataType(), null, role);
IDataReader idr = null;
try // SQL statement could be wrong
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
ManualActual = Utility.Util.GetValueOfDecimal(idr[0]);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
// SQL may return no rows or null
if (ManualActual == null)
{
ManualActual = Env.ZERO;
log.Fine("No Value = " + sql);
}
goal.SetMeasureActual(ManualActual);
goal.Save();
}
return(true);
}
/// <summary>
///Load Lookup
/// </summary>
private void Load()
{
// log.config("MLocatorLookup Loader.run " + m_AD_Column_ID);
// Set Info - see VLocator.actionText
int only_Warehouse_ID = GetOnly_Warehouse_ID();
int only_Product_ID = GetOnly_Product_ID();
bool?only_IsSOTrx = IsOnly_Outgoing();
//int sqlParaCount = 0;
StringBuilder sql = new StringBuilder("SELECT * FROM M_Locator ")
.Append(" WHERE IsActive='Y'");
if (only_Warehouse_ID != 0)
{
sql.Append(" AND M_Warehouse_ID=@w");
}
if (only_Product_ID != 0)
{
sql.Append(" AND (IsDefault='Y' "); // Default Locator
// Something already stored
sql.Append("OR EXISTS (SELECT * FROM M_Storage s ") // Storage Locator
.Append("WHERE s.M_Locator_ID=M_Locator.M_Locator_ID AND s.M_Product_ID=@p)");
if (only_IsSOTrx == null || !only_IsSOTrx.Value)
{
// Default Product
sql.Append("OR EXISTS (SELECT * FROM M_Product p ") // Default Product Locator
.Append("WHERE p.M_Locator_ID=M_Locator.M_Locator_ID AND p.M_Product_ID=@p)");
// Product Locators
sql.Append("OR EXISTS (SELECT * FROM M_ProductLocator pl ") // Product Locator
.Append("WHERE pl.M_Locator_ID=M_Locator.M_Locator_ID AND pl.M_Product_ID=@p)");
// No locators defined for the warehouse
sql.Append("OR 0 = (SELECT COUNT(*) ");
sql.Append("FROM M_ProductLocator pl");
sql.Append(" INNER JOIN M_Locator l2 ON (pl.M_Locator_ID=l2.M_Locator_ID) ");
sql.Append("WHERE pl.M_Product_ID=@p AND l2.M_Warehouse_ID=M_Locator.M_Warehouse_ID )");
}
sql.Append(" ) ");
}
String finalSql = MRole.GetDefault((Context)GetCtx(), false).AddAccessSQL(
sql.ToString(), "M_Locator", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
//if (_loader.ThreadState == ThreadState.Suspended)
//{
// log.log(Level.SEVERE, "Interrupted");
// return;
//}
// Reset
_lookup.Clear();
int rows = 0;
try
{
List <System.Data.SqlClient.SqlParameter> para = new List <System.Data.SqlClient.SqlParameter>();
// int index = 1;
if (only_Warehouse_ID != 0)
{
// pstmt.setInt(index++, only_Warehouse_ID);
para.Add(new System.Data.SqlClient.SqlParameter("@w", only_Warehouse_ID));
}
if (only_Product_ID != 0)
{
para.Add(new System.Data.SqlClient.SqlParameter("@p", only_Product_ID));
}
DataSet ds = DataBase.DB.ExecuteDataset(finalSql, para.ToArray(), null);
//
foreach (DataRow dr in ds.Tables[0].Rows)
{
// Max out
if (rows++ > _maxRows)
{
log.Warning("Over Max Rows - " + rows);
break;
}
MLocator loc = new MLocator((Context)GetCtx(), dr, null);
int M_Locator_ID = loc.GetM_Locator_ID();
KeyNamePair pp = new KeyNamePair(M_Locator_ID, loc.ToString());
_lookup.Add(M_Locator_ID, pp);
}
ds.Dispose();
ds = null;
}
catch (Exception e)
{
log.Log(Level.SEVERE, finalSql, e);
}
log.Fine("Complete #" + _lookup.Count);
if (_lookup.Count == 0)
{
log.Finer(finalSql);
}
}
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (text with variables)</returns>
protected override String DoIt()
{
log.Info("C_BPartner_ID=" + _C_BPartner_ID
+ ", AD_Org_ID=" + _AD_Org_ID
+ ", AD_OrgType_ID=" + _AD_OrgType_ID
+ ", AD_Role_ID=" + _AD_Role_ID);
if (_C_BPartner_ID == 0)
{
throw new Exception("No Business Partner ID");
}
MBPartner bp = new MBPartner(GetCtx(), _C_BPartner_ID, Get_Trx());
if (bp.Get_ID() == 0)
{
throw new Exception("Business Partner not found - C_BPartner_ID=" + _C_BPartner_ID);
}
// BP Location
MBPartnerLocation[] locs = bp.GetLocations(false);
if (locs == null || locs.Length == 0)
{
throw new ArgumentException("Business Partner has no Location");
}
// Location
int C_Location_ID = locs[0].GetC_Location_ID();
if (C_Location_ID == 0)
{
throw new ArgumentException("Business Partner Location has no Address");
}
// Create Org
Boolean newOrg = _AD_Org_ID == 0;
MOrg org = new MOrg(GetCtx(), _AD_Org_ID, Get_Trx());
if (newOrg)
{
org.SetValue(bp.GetValue());
org.SetName(bp.GetName());
org.SetDescription(bp.GetDescription());
if (!org.Save())
{
return(GetRetrievedError(org, "Organization not saved"));
//throw new Exception("Organization not saved");
}
}
else // check if linked to already
{
int C_BPartner_ID = org.GetLinkedC_BPartner_ID();
if (C_BPartner_ID > 0)
{
throw new ArgumentException("Organization '" + org.GetName()
+ "' already linked (to C_BPartner_ID=" + C_BPartner_ID + ")");
}
}
_AD_Org_ID = org.GetAD_Org_ID();
// Update Org Info
MOrgInfo oInfo = org.GetInfo();
oInfo.SetAD_OrgType_ID(_AD_OrgType_ID);
if (newOrg)
{
oInfo.SetC_Location_ID(C_Location_ID);
}
// Create Warehouse
MWarehouse wh = null;
if (!newOrg)
{
MWarehouse[] whs = MWarehouse.GetForOrg(GetCtx(), _AD_Org_ID);
if (whs != null && whs.Length > 0)
{
wh = whs[0]; // pick first
}
}
// New Warehouse
if (wh == null)
{
wh = new MWarehouse(org);
if (!wh.Save())
{
return(GetRetrievedError(wh, "Warehouse not saved"));
//throw new Exception("Warehouse not saved");
}
}
// Create Locator
MLocator mLoc = wh.GetDefaultLocator();
if (mLoc == null)
{
mLoc = new MLocator(wh, "Standard");
mLoc.SetIsDefault(true);
mLoc.Save();
}
// Update/Save Org Info
oInfo.SetM_Warehouse_ID(wh.GetM_Warehouse_ID());
if (!oInfo.Save(Get_Trx()))
{
return(GetRetrievedError(oInfo, "Organization Info not saved"));
//throw new Exception("Organization Info not saved");
}
// Update BPartner
bp.SetAD_OrgBP_ID(_AD_Org_ID);
if (bp.GetAD_Org_ID() != 0)
{
bp.SetClientOrg(bp.GetAD_Client_ID(), 0); // Shared BPartner
}
// Save BP
if (!bp.Save())
{
return(GetRetrievedError(bp, "Business Partner not updated"));
//throw new Exception("Business Partner not updated");
}
// Limit to specific Role
if (_AD_Role_ID != 0)
{
Boolean found = false;
MRoleOrgAccess[] orgAccesses = MRoleOrgAccess.GetOfOrg(GetCtx(), _AD_Org_ID);
// delete all accesses except the specific
for (int i = 0; i < orgAccesses.Length; i++)
{
if (orgAccesses[i].GetAD_Role_ID() == _AD_Role_ID)
{
found = true;
}
else
{
orgAccesses[i].Delete(true);
}
}
// create access
if (!found)
{
MRoleOrgAccess orgAccess = new MRoleOrgAccess(org, _AD_Role_ID);
orgAccess.Save();
}
}
// Reset Client Role
MRole.GetDefault(GetCtx(), true);
return("Business Partner - Organization Link created");
} // doIt
/// <summary>
/// Mehtod added to complete and reverse the document and execute the workflow as well
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="Record_ID">GL_Journal_ID</param>
/// <param name="Process_ID">Process</param>
/// <param name="DocAction">Document Action</param>
/// <returns>result of completion or reversal in a string array</returns>
public string CompleteOrReverse(Ctx ctx, int Record_ID, int Process_ID, string DocAction)
{
string result = "";
MRole role = MRole.Get(ctx, ctx.GetAD_Role_ID());
if (Util.GetValueOfBool(role.GetProcessAccess(Process_ID)))
{
DB.ExecuteQuery("UPDATE GL_Journal SET DocAction = '" + DocAction + "' WHERE GL_Journal_ID = " + Record_ID);
MProcess proc = new MProcess(ctx, Process_ID, null);
MPInstance pin = new MPInstance(proc, Record_ID);
if (!pin.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string errorMsg = "";
if (vnp != null)
{
errorMsg = vnp.GetName();
if (errorMsg == "")
{
errorMsg = vnp.GetValue();
}
}
if (errorMsg == "")
{
result = errorMsg = Msg.GetMsg(ctx, "DocNotCompleted");
}
return(result);
}
MPInstancePara para = new MPInstancePara(pin, 20);
para.setParameter("DocAction", DocAction);
if (!para.Save())
{
//String msg = "No DocAction Parameter added"; // not translated
}
ProcessInfo pi = new ProcessInfo("WF", Process_ID);
pi.SetAD_User_ID(ctx.GetAD_User_ID());
pi.SetAD_Client_ID(ctx.GetAD_Client_ID());
pi.SetAD_PInstance_ID(pin.GetAD_PInstance_ID());
pi.SetRecord_ID(Record_ID);
if (Process_ID == 169)
{
pi.SetTable_ID(224);
}
ProcessCtl worker = new ProcessCtl(ctx, null, pi, null);
worker.Run();
if (pi.IsError())
{
ValueNamePair vnp = VLogger.RetrieveError();
string errorMsg = "";
if (vnp != null)
{
errorMsg = vnp.GetName();
if (errorMsg == "")
{
errorMsg = vnp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = pi.GetSummary();
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(ctx, "DocNotCompleted");
}
result = errorMsg;
return(result);
}
else
{
result = "";
}
}
else
{
result = Msg.GetMsg(ctx, "NoAccess");
return(result);
}
return(result);
}
JsonResult IBase <MRole> .Save(MRole obj)
{
throw new NotImplementedException();
}
private void InitBPartner(int WinNo, int bPartnerID, string bpType)
{
C_BPartner_ID = bPartnerID;
bool ro = false;
DataSet ds = null;
log.Config("C_BPartner_ID=" + bPartnerID);
// New bpartner
if (bPartnerID == 0)
{
_partner = null;
_pLocation = null;
_user = null;
_bprelation = null;
_bpLocation = null;
_bpGroup = null;
//return true;
}
_partner = new MBPartner(Env.GetCtx(), bPartnerID, null);
if (_partner.Get_ID() != 0)
{
// Contact - Load values
_pLocation = _partner.GetLocation(
Env.GetCtx().GetContextAsInt(WinNo, "C_BPartner_Location_ID"));
_user = _partner.GetContact(
Env.GetCtx().GetContextAsInt(WinNo, "AD_User_ID"));
}
isCustomer = _partner.IsCustomer();
isVendor = _partner.IsVendor();
isEmployee = _partner.IsEmployee();
_readOnly = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MBPartner.Table_ID, 0, false);
log.Info("R/O=" + _readOnly);
// Get Data
_greeting = FillGreeting();
/************************************/
_bpGroup = FillBPGroup();
_bpRelation = FillBPRelation();
_bpLocation = FillBPLocation(0, ctx);
/************************************/
ro = _readOnly;
if (!ro)
{
ro = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MBPartnerLocation.Table_ID, 0, false);
}
if (!ro)
{
ro = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MLocation.Table_ID, 0, false);
}
ds = DB.ExecuteDataset("Select C_BPartnerRelation_ID, c_bpartnerrelation_location_id from C_BP_Relation where c_bpartner_id=" + _partner.GetC_BPartner_ID());
LoadBPartner(C_BPartner_ID, ds);
}
