Technology Advisors is working on cleaning up our internal SugarCRM instance and I was tasked with finding a way to remove all orphaned records so they can be purged. An orphaned record is a record that is not related to a parent record. For example, a Call that is not related to an Account, Contact, Opportunity or Case. This is simple enough to do with a SQL statement in an on-premise instance, but I wanted a way to easily do this in an ondemand instance. I could have created a Scheduler that would mark orphaned records as orphaned or even delete them outright but I wanted to try something new so I created a Custom REST Endpoint that will return the ID of all orphaned Calls. I can then use Starfish ETL to delete or update the records as needed.

I was surprised by how easy it is to create a custom endpoint. All it takes is a single file you place in the right spot in the custom directory and a Quick Repair. The instructions for this are found on Sugar's SugarCRM Cookbook. See this page on creating a custom endpoint. In order to run the query, I created a SugarQuery. What is SugarQuery? SugarQuery is a SQL query builder for retrieving data directly from the database. It is used extensively within the core of the application. For instance, the FilterAPI uses it. It uses a bean, the beans relationships, and visibility models to build a SQL query that can be used to retrieve data. See this page on creating a SugarQuery.

The end result can be found here:

  1. <?php
  2. //Drop this file into ../custom/modules/Calls/clients/base/api/OrphanedCallsAPI.php and you've got yourself a custom API end point that will return the IDs of all orphaned Calls in the system.
  3. class OrphanedCallsApi extends SugarApi
  4. {
  5. // This function is only called whenever the rest service cache file is deleted.
  6. // This shoud return an array of arrays that define how different paths map to different functions
  7. public function registerApiRest() {
  8. return array(
  9. 'getOrphanedCalls' => array(
  10. // What type of HTTP request to match against, we support GET/PUT/POST/DELETE
  11. 'reqType' => 'GET',
  12. // This is the path you are hoping to match, it also accepts wildcards of ? and <module>
  13. 'path' => array('Calls', 'orphaned_calls'),
  14. // These take elements from the path and use them to populate $args
  15. 'pathVars' => array('', ''),
  16. // This is the method name in this class that the url maps to
  17. 'method' => 'getOrphanedCalls',
  18. // The shortHelp is vital, without it you will not see your endpoint in the /help
  19. 'shortHelp' => 'Lists all orphaned Calls in the system',
  20. // The longHelp points to an HTML file and will be there on /help for people to expand and show
  21. 'longHelp' => 'Returns the IDs of all orphaned Calls in the system. Orphaned calls are calls with no related Lead, Contact or Parent ID.',
  22. ),
  23. );
  24. }
  25.  
  26. function getOrphanedCalls($api, $args)
  27. {
  28. $seed = BeanFactory::newBean('Calls');
  29. $q = new SugarQuery();
  30. // Set from to the bean first so SugarQuery can figure out joins and fields on the first try
  31. $q->from($seed);
  32. // Adding the ID field so we can validate the results from the select
  33. $q->select('id');
  34. $q->whereRaw("not exists (select * from calls_contacts cc where calls.id = cc.call_id) and not exists (select * from calls_leads cl where calls.id = cl.call_id) and (calls.parent_id is null or calls.parent_id = '')");
  35. //$GLOBALS['log']->fatal("q: " . $q->compileSql());
  36.  
  37. // Let's parse the field array like formatBeans down below
  38. if (empty($args['fields'])) {
  39. $args['fields'] = array();
  40. } else if (!is_array($args['fields'])) {
  41. $args['fields'] = explode(',', $args['fields']);
  42. }
  43.  
  44. // Run the new ->fetchFromQuery() call to get beans out of a query, get the raw rows for non-vardef fields
  45. $callBeans = $seed->fetchFromQuery($q, $args['fields'], array('returnRawRows' => true));
  46.  
  47. // The normal beans are in there by id, the raw rows are returned in their own element
  48. // Let's strip that out so we don't try to apply sugarbean code to it.
  49. $rows = $callBeans['_rows'];
  50. unset($callBeans['_rows']);
  51. //In this case, we saved the raw rows data in $rows, but we are not using it.
  52. //If we had needed some data from the raw row data, we could have used it now.
  53.  
  54. // Part of SugarApi, this will format our list of beans like all of the rest of the API's
  55. // Consistency is good
  56. $calls = $this->formatBeans($api, $args, $callBeans);
  57.  
  58. return $calls;
  59. }
  60. }
Posted in:

Looking for SugarCRM help?

We do training, customization, integration, and much more. Contact us today.