Returns the IDs of all orphaned Calls in the system. Orphaned calls are calls with no related Lead, Contact or Parent ID.

OrphanedCallsAPI.php

<?php
//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.
class OrphanedCallsApi extends SugarApi
{
    // This function is only called whenever the rest service cache file is deleted.
    // This shoud return an array of arrays that define how different paths map to different functions
    public function registerApiRest() {
        return array(
            'getOrphanedCalls' => array(
                // What type of HTTP request to match against, we support GET/PUT/POST/DELETE
                'reqType' => 'GET',
                // This is the path you are hoping to match, it also accepts wildcards of ? and <module>
                'path' => array('Calls', 'orphaned_calls'),
                // These take elements from the path and use them to populate $args
                'pathVars' => array('', ''),
                // This is the method name in this class that the url maps to
                'method' => 'getOrphanedCalls',
                // The shortHelp is vital, without it you will not see your endpoint in the /help
                'shortHelp' => 'Lists all orphaned Calls in the system',
                // The longHelp points to an HTML file and will be there on /help for people to expand and show
                'longHelp' => 'Returns the IDs of all orphaned Calls in the system.  Orphaned calls are calls with no related Lead, Contact or Parent ID.',
            ),
        );
    }
    
    function getOrphanedCalls($api, $args)
    {
        $seed = BeanFactory::newBean('Calls');
        $q = new SugarQuery();
        // Set from to the bean first so SugarQuery can figure out joins and fields on the first try
        $q->from($seed);
        // Adding the ID field so we can validate the results from the select
        $q->select('id');
        $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 = '')");
        //$GLOBALS['log']->fatal("q: " . $q->compileSql());

	// Let's parse the field array like formatBeans down below
	if (empty($args['fields'])) {
		$args['fields'] = array();
	} else if (!is_array($args['fields'])) {
		$args['fields'] = explode(',', $args['fields']);
	}

	// Run the new ->fetchFromQuery() call to get beans out of a query, get the raw rows for non-vardef fields
	$callBeans = $seed->fetchFromQuery($q, $args['fields'], array('returnRawRows' => true));

	// The normal beans are in there by id, the raw rows are returned in their own element
	// Let's strip that out so we don't try to apply sugarbean code to it.
	$rows = $callBeans['_rows'];
	unset($callBeans['_rows']);
	//In this case, we saved the raw rows data in $rows, but we are not using it.
	//If we had needed some data from the raw row data, we could have used it now.

	// Part of SugarApi, this will format our list of beans like all of the rest of the API's
	// Consistency is good
	$calls = $this->formatBeans($api, $args, $callBeans);

	return $calls;
    }
}