Skip to content

Instantly share code, notes, and snippets.

@SugarCRMExamples
Last active August 29, 2015 13:57
Show Gist options
  • Save SugarCRMExamples/9409848 to your computer and use it in GitHub Desktop.
Save SugarCRMExamples/9409848 to your computer and use it in GitHub Desktop.
<?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