Get all info from Tracking Events into User Details

I’d like to know if it’s possible for me to click in customers/account/contact details and get the information about this user using tracking events data. In other words, get all the data from Tracking Events using customer id as a reference.

Thanks!

Topic

[Alexandr SmagaParticipant]

Hello !

It’s required additional development. But it’s not so hard to implement, as suggestion I can recommend use following code to find events in DB.

/** @var EntityManager $em */ $em = $this->getDoctrine()->getManager(); $expr = $em->getExpressionBuilder(); $customerId = $em->getRepository('OroCRMMagentoBundle:Customer') ->createQueryBuilder('c') ->select('c.originId') ->where($expr->eq('c.account', $account)) ->getQuery() ->getSingleScalarResult(); $events = $em->getRepository('OroTrackingBundle:TrackingEvent') ->createQueryBuilder('te') ->innerJoin('te.website', 'tw') ->andWhere('tw.identifier = :identifier') ->andWhere($expr->like('te.userIdentifier', sprintf('id=%d;%%', $customerId))) ->setParameter('identifier', '***YOUR_TRACKING_ID_HERE***') ->getQuery() ->getResult();

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

/** @var EntityManager $em */         $em = $this->getDoctrine()->getManager();         $expr = $em->getExpressionBuilder();         $customerId = $em->getRepository('OroCRMMagentoBundle:Customer')             ->createQueryBuilder('c')             ->select('c.originId')             ->where($expr->eq('c.account', $account))             ->getQuery()             ->getSingleScalarResult();           $events = $em->getRepository('OroTrackingBundle:TrackingEvent')             ->createQueryBuilder('te')             ->innerJoin('te.website', 'tw')             ->andWhere('tw.identifier = :identifier')             ->andWhere($expr->like('te.userIdentifier', sprintf('id=%d;%%', $customerId)))             ->setParameter('identifier', '***YOUR_TRACKING_ID_HERE***')             ->getQuery()             ->getResult();

[RodolfoParticipant]

Thank you for your answer Alexandr,
I just need some help to convert your suggestion to yml format and I still have other question.
How can I add the mysql ‘like’ in yml?
For example: “userIdentifier like ‘%***MY_USER_ORIGIN_ID_HERE***%’

By now, I’m getting this error:
“An exception has been thrown during the rendering of a template (“Unrecognized options “bind_parameters” under “query””) in”

tracking-events-grid-to-webcustomer: source: type: orm acl_resource: oro_tracking_website_view query: select: - e.id - e.name - e.value - e.userIdentifier - e.url - e.title - e.code - e.loggedAt from: - { table: Oro\Bundle\TrackingBundle\Entity\TrackingEvent, alias: e } where: and: - e.userIdentifier = :origin_id bind_parameters: origin_id: originId website-tracking-events-grid-to-webcustomer: extends: tracking-events-grid-to-webcustomer source: query: join: inner: - { join: e.website, alias: w } where: and: - w.identifier = :website_id bind_parameters: website_id: websiteId

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

tracking-events-grid-to-webcustomer:         source:             type: orm             acl_resource: oro_tracking_website_view             query:                 select:                     - e.id                     - e.name                     - e.value                     - e.userIdentifier                     - e.url                     - e.title                     - e.code                     - e.loggedAt                 from:                     - { table: Oro\Bundle\TrackingBundle\Entity\TrackingEvent, alias: e }                 where:                     and:                         - e.userIdentifier = :origin_id                 bind_parameters:                     origin_id: originId     website-tracking-events-grid-to-webcustomer:         extends: tracking-events-grid-to-webcustomer         source:             query:                 join:                     inner:                         - { join: e.website, alias: w }                 where:                     and:                         - w.identifier = :website_id                 bind_parameters:                     website_id: websiteId

And this what I put on my twig:

{% set trackingEventsGrid %} {{dataGrid.renderGrid('website-tracking-events-grid-to-webcustomer', { 'originId': 34015, 'websiteId': 1 }) }} {% endset %}

1 2 3 4 5 6 7 8

{% set trackingEventsGrid %}         {{dataGrid.renderGrid('website-tracking-events-grid-to-webcustomer',             {                 'originId': 34015,                 'websiteId': 1             })         }}     {% endset %}

{% set dataBlocks = [ { 'title': 'General Information'|trans, 'class': 'active', 'subblocks': generalSubBlocks }, { 'title': 'orocrm.magento.customer.sales.label'|trans, 'subblocks': [ {'data' : [salesTabsWidget] } ] } , { 'title': 'oro.tracking.block.events'|trans, 'subblocks': [ {'data' : [trackingEventsGrid]} ] } ] %}

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

{% set dataBlocks = [     {     'title': 'General Information'|trans,     'class': 'active',     'subblocks': generalSubBlocks     },     {     'title': 'orocrm.magento.customer.sales.label'|trans,     'subblocks': [     {'data' : [salesTabsWidget] }     ]     }           ,         {         'title': 'oro.tracking.block.events'|trans,             'subblocks': [                 {'data' : [trackingEventsGrid]}             ]         }       ] %}

[RodolfoParticipant]

I fixed here! Removed the first bind_parameter and changed the datagrid.yml to use this:

where: and: - e.userIdentifier LIKE :originId

1 2 3

where:     and:         - e.userIdentifier LIKE :originId

and

{% set entityConcat = '%' ~ entity.originId ~ '%' %}

1	{% set entityConcat = '%' ~ entity.originId ~ '%' %}

[Author]

Replies