Last active
August 29, 2015 13:57
-
-
Save SugarCRMExamples/9409848 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
function getAtRisk($api, $args) | |
{ | |
$seed = BeanFactory::newBean('Accounts'); | |
$q = new SugarQuery(); | |
// Set from to the bean first so SugarQuery can figure out joins and fields on the first try | |
$q->from($seed); | |
// We need to save the alias of any join because they are auto-generated (and different if you are on some databases) | |
$caseAlias = $q->join('cases')->joinName(); | |
// Adding the ID field so we can validate the results from the select | |
$q->select('id'); | |
// fieldRaw will let us run raw SQL in the select, be sure to quote anything are getting from users. | |
// The second argument is the field alias so we can tell what it will be coming out the other end of the query | |
$q->select->fieldRaw("COUNT(".$caseAlias.".id)","case_count"); | |
// We need to use orderByRaw here instead of just orderBy because case_count isn't part of any vardefs | |
$q->orderByRaw('case_count'); | |
$q->groupBy('id'); | |
// the ->where() gives us a where object, there are a lot of operators that work on that (including ->and() and ->or()) | |
$q->where()->notIn($caseAlias.'.status', array('Closed', 'Rejected')); | |
$q->limit(3); | |
// 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 | |
$accountBeans = $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 = $accountBeans['_rows']; | |
unset($accountBeans['_rows']); | |
// Part of SugarApi, this will format our list of beans like all of the rest of the API's | |
// Consistency is good | |
$accounts = $this->formatBeans($api, $args, $accountBeans); | |
// Since case_count isn't part of the vardefs, we have to populate it manually | |
foreach ($accounts as &$account) { | |
$account['case_count'] = (int)$rows[$account['id']]['case_count']; | |
} | |
return $accounts; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment