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