8.3R02 Contact Sentry and MailLink Auto-recognition

Hi there,

i noticed that when you use the ModifySelect method in the contact sentry and no matter which SQL Restriction you put in, the sentry works fine and hides the companies in the web client of 83R02, but the auto-recognition of the email in the MailLink stops working. I still can select the company and person there, but the MailLink dialog shows that no email was found. When i turn off sentry the auto-recognition is working fine.

here is a simple example code of the sentry (but you can use any type of rectrictionAnd so that the issue will occur):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using SuperOffice.CRM.Services;
using SuperOffice.CRM.Data;
using SuperOffice.CRM.Security;
using SuperOffice.Data;
using SuperOffice.Data.SQL;

namespace Sentry_Plugin_Contact
{
    [SentryPlugin("contact")]
    public class ContactSentryPlugin : ISentryPlugin
    {
        SuperOffice.CRM.Security.Sentry _sentry = null;

        public ContactSentryPlugin() { }

        #region ISentryPlugin Members

        public void Init(SuperOffice.CRM.Security.Sentry sentry)
        {
            _sentry = sentry;
        }

        public void ModifyFieldRights(FieldRights rights)
        {                
        }

        public void ModifyTableRights(TableRights rights)
        {
        }

        #endregion

        #region helpers

        private ContactSentryQueryInfo QueryInfo
        {
            get
            {
                return
                (ContactSentryQueryInfo)_sentry.SentryQueryInfo;
            }
        }

        object GetFieldValue(FieldInfo fieldInfo)
        {
            using (_sentry.Lookups.BeginIgnoreSentryCheck())
            {
                return _sentry.Lookups.GetFieldValue(fieldInfo);
            }
        }

        #endregion

        #region checks
        bool CheckField(FieldInfo fieldInfo, FieldRights rights)
        {
            foreach (FieldInfo fi in rights.Keys)
            {
                if (fieldInfo.Definition == fi.Definition)
                    return true;
            }
            return false;
        }

        bool CheckTable(TableInfo tableInfo, TableRights rights)
        {
            foreach (TableInfo ti in rights.Keys)
            {
                if (tableInfo.Definition == ti.Definition)
                    return true;
            }
            return false;
        }

        #endregion
    }

    [SentryPluginQueryTableUpdater("contact")]
    public class SentryPluginQueryTableUpdaterContact :
ISentryPluginQueryTableUpdater
    {
        #region ISentryPluginQueryTableUpdater Members

        public void ModifySelect(Select sql, TableInfo tableInfo)
        {
                ContactTableInfo contactTable = (ContactTableInfo)tableInfo;
                    sql.RestrictionAnd(contactTable.Nomailing.Equal(S.Parameter(0)));        
       }          
     }
}
        #endregion
    

and here is the screenshot of the maillink dialog showing auto-recognition not working:

and here the screenshot with sentry turned off:

 

i tried it with several types of sql.RestrictionAnd and also sql.JoinRestriction is causing this.

Is it a bug? We have two customers complaining about that and i hope to find a solution.

Best,

Dennis

I have a similar sentry bug with Projects, which I am just about to log, where if you have a project relation in a ticket, no fields are showing on the ticket details screen, if no project is selected, or a project the user does not have access to.

Trevor

Av: Trevor Sharp 27. jul 2018

Bug 60006

Av: Christian Mogensen 30. jul 2018

The Mail Link calls the NetServer, which runs the following query:

/* Default | TJE4 (27) | EMailFindAddress |  */
SELECT T0."email_address", T0."contact_id", T0."person_id", T1."firstname", T1."middleName", T1."lastname", T1."contact_id", T1."associate_id", T2."name", T3."name", T0."email_id", T1."retired", T4."associate_id", T1."person_id", T1."group_id", T1."registered", T1."registered_associate_id", T1."activeErpLinks", T2."contact_id", T2."associate_id", T2."business_idx", T2."group_id", T2."registered", T2."registered_associate_id", T2."xstop", T2."activeErpLinks", T3."contact_id", T3."associate_id", T3."business_idx", T3."group_id", T3."registered", T3."registered_associate_id", T3."xstop", T3."activeErpLinks", T4."deleted", T4."type" 
FROM crm7."EMAIL" T0 
LEFT OUTER JOIN crm7."PERSON" T1 ON (T0."person_id" = T1."person_id" AND ((T1."DeletedDate" IS NULL) OR(T1."DeletedDate" = @P0))) 
LEFT OUTER JOIN crm7."CONTACT" T2 ON (T1."contact_id" = T2."contact_id" AND ((T2."DeletedDate" IS NULL) OR(T2."DeletedDate" = @P1))) LEFT OUTER JOIN crm7."CONTACT" T3 ON (T0."contact_id" = T3."contact_id" AND ((T3."DeletedDate" IS NULL) OR(T3."DeletedDate" = @P2))) LEFT OUTER JOIN crm7."ASSOCIATE" T4 ON (T1."person_id" = T4."person_id") 
WHERE 
((T0."email_address" = @P3) AND
(T2."name" NOT LIKE @P4)) AND
(T3."name" NOT LIKE @P5) 
ORDER BY T0."email_address" ASC, T4."associate_id" DESC, T1."retired" ASC, T0."person_id" DESC

As you can see, the contact info is left-outer-joined to the e-mail table. Adding a restriction directly on the contact table causes this to collapse to an inner join.

You need to make a sentry plugin that treats OuterJoins properly, and place the restriction on the join, rather than on the result.

 

        public void ModifySelect(Select sql, TableInfo tableInfo)
        {
                ContactTableInfo contactTable = (ContactTableInfo)tableInfo;
                if( sql.IsTableOuterJoined(contactTable) )
                {
                    var rels = sql.GetRelations(contactTable);
                    var outerJoin = rels.FirstOrDefault();
                    if( outerJoin != null )
                        outerJoin.OriginalJoin.RestrictionAnd(contactTable.Name.NotLike("%Hide!%"));
                }
                else
                {
                    sql.RestrictionAnd(contactTable.Name.NotLike("%Hide!%"));
                }
        }
Av: Christian Mogensen 31. jul 2018

Hi, 

 

Cannont find the bug (60006).

Ive see a couple of places where Roles is restricting contacts in Other groups, Mail Link cannont find the e-mail registered when archiving.

You can add the e-mail to the person, and you can find the person and e-mail thru search in the client. If you change the contact owner to yourself, the person is found as expected.

Is this the same cause?

Av: Frode Pettersen 4. dec 2018

Link to the bug report and status...

Av: Tony Yates 6. dec 2018