/// <summary>
/// Get SQL for Role
/// </summary>
/// <param name="role">role</param>
/// <returns>statement</returns>
public String GetSQL(MRole role)
{
if (_lines == null)
{
GetLines(true);
}
StringBuilder sql = new StringBuilder("SELECT ");
for (int i = 0; i < _lines.Length; i++)
{
MInfoColumn col = _lines[i];
if (i > 0)
{
sql.Append(",");
}
sql.Append(col.GetSelectClause());
}
sql.Append(" FROM ").Append(GetFromClause());
// Access
if (role == null)
{
role = MRole.GetDefault(GetCtx(), false);
}
String finalSQL = role.AddAccessSQL(sql.ToString(), GetTableName(), MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
log.Finer(finalSQL);
return(finalSQL);
}
//END
// Added by Manjot 20-8-2015 VAMFG
public MProduct[] GetProductsofCategory(String WhereClause, Trx trx)
{
List <MProduct> list = new List <MProduct>();
StringBuilder sql = new StringBuilder(" SELECT * FROM M_Product WHERE M_Product_Category_ID = @param1 ");
if (WhereClause != null && WhereClause.Length != 0)
{
sql.Append(WhereClause);
}
MRole role = MRole.GetDefault(GetCtx(), false);
String stmt = role.AddAccessSQL(sql.ToString(), "M_Product", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
SqlParameter[] param = null;
IDataReader idr = null;
DataTable dt = new DataTable();
try
{
param = new SqlParameter[1];
param[0] = new SqlParameter("@param1", GetM_Product_Category_ID());
idr = DB.ExecuteReader(sql.ToString(), param, null);
dt.Load(idr);
if (idr != null)
{
idr.Close();
idr = null;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
MProduct product = new MProduct(GetCtx(), dt.Rows[i], Get_TrxName());
list.Add(product);
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
MProduct[] retVal = new MProduct[list.Count];
retVal = list.ToArray();
return(retVal);
}
} // doWork
/**
* Process Alert
* @param alert alert
* @return true if processed
*/
private bool ProcessAlert(MAlert alert)
{
if (!alert.IsValid())
{
log.Info("Invalid: " + alert);
return(false);
}
log.Info("" + alert);
m_recipients.Clear();
StringBuilder message = new StringBuilder(alert.GetAlertMessage())
.Append(Env.NL);
// Context
Ctx ctx = alert.GetCtx();
ctx.SetAD_Client_ID(alert.GetAD_Client_ID());
ctx.SetAD_Org_ID(alert.GetAD_Org_ID());
//
bool valid = true;
bool processed = false;
MAlertRule[] rules = alert.GetRules(false);
for (int i = 0; i < rules.Length; i++)
{
if (i > 0)
{
message.Append(Env.NL).Append("================================").Append(Env.NL);
}
//Trx trx = null; // assume r/o
MAlertRule rule = rules[i];
if (!rule.IsValid())
{
log.Config("Invalid: " + rule);
continue;
}
log.Fine("" + rule);
// Pre
String sql = rule.GetPreProcessing();
if (sql != null && sql.Length > 0)
{
int no = DB.ExecuteQuery(sql);
if (no == -1)
{
ValueNamePair error = VLogger.RetrieveError();
rule.SetErrorMsg("Pre=" + error.GetName());
m_errors.Append("Pre=" + error.GetName());
rule.SetIsValid(false);
rule.Save();
valid = false;
break;
}
} // Pre
// The processing
ctx.SetAD_Role_ID(0);
ctx.SetAD_User_ID(0);
sql = rule.GetSql();
if (alert.IsEnforceRoleSecurity() ||
alert.IsEnforceClientSecurity())
{
int AD_Role_ID = alert.GetFirstAD_Role_ID();
if (AD_Role_ID == -1)
{
AD_Role_ID = alert.GetFirstUserAD_Role_ID();
}
if (AD_Role_ID != -1)
{
String tableName = rule.GetTableName();
bool fullyQualified = MRole.SQL_FULLYQUALIFIED;
if (Util.IsEmpty(tableName))
{
fullyQualified = MRole.SQL_NOTQUALIFIED;
}
MRole role = MRole.Get(ctx, AD_Role_ID, 0, false);
sql = role.AddAccessSQL(sql, tableName,
fullyQualified, MRole.SQL_RO);
ctx.SetAD_Role_ID(AD_Role_ID);
}
if (alert.GetFirstAD_User_ID() != -1)
{
ctx.SetAD_User_ID(alert.GetFirstAD_User_ID());
}
}
try
{
String text = ListSqlSelect(sql);
if (text != null && text.Length > 0)
{
message.Append(text);
processed = true;
int index = text.IndexOf(":");
if (index > 0 && index < 5)
{
m_summary.Append(text.Substring(0, index));
}
}
}
catch (Exception e)
{
rule.SetErrorMsg("Select=" + e.Message);
m_errors.Append("Select=" + e.Message);
rule.SetIsValid(false);
rule.Save();
valid = false;
break;
}
// Post
sql = rule.GetPostProcessing();
if (sql != null && sql.Length > 0)
{
int no = DB.ExecuteQuery(sql);
if (no == -1)
{
ValueNamePair error = VLogger.RetrieveError();
rule.SetErrorMsg("Post=" + error.GetName());
m_errors.Append("Post=" + error.GetName());
rule.SetIsValid(false);
rule.Save();
valid = false;
break;
}
} // Post
} // for all rules
// Update header if error
if (!valid)
{
alert.SetIsValid(false);
alert.Save();
return(false);
}
// Nothing to report
if (!processed)
{
m_summary.Append(alert.GetName()).Append("=No Result - ");
return(true);
}
// Send Message
int countRecipient = 0;
MAlertRecipient[] recipients = alert.GetRecipients(false);
for (int i = 0; i < recipients.Length; i++)
{
MAlertRecipient recipient = recipients[i];
if (recipient.GetAD_User_ID() >= 0) // System == 0
{
if (SendInfo(recipient.GetAD_User_ID(), alert, message.ToString()))
{
countRecipient++;
}
}
if (recipient.GetAD_Role_ID() >= 0) // SystemAdministrator == 0
{
MUserRoles[] urs = MUserRoles.GetOfRole(GetCtx(), recipient.GetAD_Role_ID());
for (int j = 0; j < urs.Length; j++)
{
MUserRoles ur = urs[j];
if (!ur.IsActive())
{
continue;
}
if (SendInfo(ur.GetAD_User_ID(), alert, message.ToString()))
{
countRecipient++;
}
}
}
}
m_summary.Append(alert.GetName()).Append(" (Recipients=").Append(countRecipient).Append(") - ");
return(valid);
} // processAlert
/**
* Add Restrictions to SQL
* @param sql orig sql
* @param queryOnly incomplete sql for query restriction
* @param restrictions restrictions
* @param role role
* @param tableName table name
* @param orgColumn org column
* @param bpColumn bpartner column
* @param pColumn product column
* @return updated sql
*/
public static String AddRestrictions(String sql, Boolean queryOnly,
MGoalRestriction[] restrictions, MRole role,
String tableName, String orgColumn, String bpColumn, String pColumn, Ctx ctx)
{
StringBuilder sb = new StringBuilder(sql);
// Org Restrictions
if (orgColumn != null)
{
List <int> list = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_Organization.Equals(restrictions[i].GetGoalRestrictionType()))
{
list.Add(restrictions[i].GetOrg_ID());
}
// Hierarchy comes here
}
if (list.Count == 1)
{
sb.Append(" AND ").Append(orgColumn)
.Append("=").Append(list[0]);
}
else if (list.Count > 1)
{
sb.Append(" AND ").Append(orgColumn).Append(" IN (");
for (int i = 0; i < list.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(list[i]);
}
sb.Append(")");
}
} // org
// BPartner Restrictions
if (bpColumn != null)
{
List <int> listBP = new List <int>();
List <int> listBPG = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_BusinessPartner.Equals(restrictions[i].GetGoalRestrictionType()))
{
listBP.Add(restrictions[i].GetC_BPartner_ID());
}
// Hierarchy comes here
if (MGoalRestriction.GOALRESTRICTIONTYPE_BusPartnerGroup.Equals(restrictions[i].GetGoalRestrictionType()))
{
listBPG.Add(restrictions[i].GetC_BP_Group_ID());
}
}
// BP
if (listBP.Count == 1)
{
sb.Append(" AND ").Append(bpColumn)
.Append("=").Append(listBP[0]);
}
else if (listBP.Count > 1)
{
sb.Append(" AND ").Append(bpColumn).Append(" IN (");
for (int i = 0; i < listBP.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listBP[i]);
}
sb.Append(")");
}
// BPG
if (bpColumn.IndexOf(".") == -1)
{
bpColumn = tableName + "." + bpColumn;
}
if (listBPG.Count == 1)
{
sb.Append(" AND EXISTS (SELECT * FROM C_BPartner bpx WHERE ")
.Append(bpColumn)
.Append("=bpx.C_BPartner_ID AND bpx.C_BP_GROUP_ID=")
.Append(listBPG[0]).Append(")");
}
else if (listBPG.Count > 1)
{
sb.Append(" AND EXISTS (SELECT * FROM C_BPartner bpx WHERE ")
.Append(bpColumn)
.Append("=bpx.C_BPartner_ID AND bpx.C_BP_GROUP_ID IN (");
for (int i = 0; i < listBPG.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listBPG[i]);
}
sb.Append("))");
}
} // bp
// Product Restrictions
if (pColumn != null)
{
List <int> listP = new List <int>();
List <int> listPC = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_Product.Equals(restrictions[i].GetGoalRestrictionType()))
{
listP.Add(restrictions[i].GetM_Product_ID());
}
// Hierarchy comes here
if (MGoalRestriction.GOALRESTRICTIONTYPE_ProductCategory.Equals(restrictions[i].GetGoalRestrictionType()))
{
listPC.Add(restrictions[i].GetM_Product_Category_ID());
}
}
// Product
if (listP.Count == 1)
{
sb.Append(" AND ").Append(pColumn)
.Append("=").Append(listP[0]);
}
else if (listP.Count > 1)
{
sb.Append(" AND ").Append(pColumn).Append(" IN (");
for (int i = 0; i < listP.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listP[i]);
}
sb.Append(")");
}
// Category
if (pColumn.IndexOf(".") == -1)
{
pColumn = tableName + "." + pColumn;
}
if (listPC.Count == 1)
{
sb.Append(" AND EXISTS (SELECT * FROM M_Product px WHERE ")
.Append(pColumn)
.Append("=px.M_Product_ID AND px.M_Product_Category_ID=")
.Append(listPC[0]).Append(")");
}
else if (listPC.Count > 1)
{
sb.Append(" AND EXISTS (SELECT * FROM M_Product px WHERE ")
.Append(pColumn)
.Append("=px.M_Product_ID AND px.M_Product_Category_ID IN (");
for (int i = 0; i < listPC.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listPC[i]);
}
sb.Append("))");
}
} // product
String finalSQL = sb.ToString();
if (queryOnly)
{
return(finalSQL);
}
if (role == null)
{
role = MRole.GetDefault(ctx);
}
String retValue = role.AddAccessSQL(finalSQL, tableName, true, false);
return(retValue);
}