/**
* Lists Records already present in sugar db using the $columnName & $moduleName parameters
* @param $columnName
* @param $moduleName
* @param $recordIds array (array of id to be used in the in clause of the query)
* @return array
*/
private function getExistingRecords($columnName, $moduleName, $recordIds = null)
{
$seed = BeanFactory::newBean($moduleName);
//if duns_num, format all duns to be 0 padded
// for 9 digits
if ($columnName == 'duns_num') {
foreach ($recordIds as &$duns) {
$duns = str_pad($duns, 9, "0", STR_PAD_LEFT);
}
}
$options = array();
$options['offset'] = 0;
$options['order_by'] = array(array('date_modified', 'DESC'));
$options['add_deleted'] = true;
$options['offset'] = 'end';
$options['module'] = $seed->module_name;
$options['team_security'] = false;
$q = new SugarQuery();
$q->from($seed, $options);
$fields = array($columnName);
$q->select($fields);
$where = $q->where();
$where->in($columnName, $recordIds);
$where = $where->queryAnd();
$where->equals('deleted', 0);
$q->compileSql();
$queryResults = $q->execute('json');
return $queryResults;
}
protected function generateFTSQuery($module, $fieldDefs)
{
$queueTableName = self::QUEUE_TABLE;
$bean = BeanFactory::getBean($module);
$ftsQuery = new SugarQuery();
$ftsQuery->from($bean);
// add fts enabled fields to the filter
$fieldsFilter = array('id');
foreach ($fieldDefs as $value) {
// skip nondb fields
if (!empty($value['source']) && $value['source'] == 'non-db') {
continue;
}
// filter email1 field and add the join.
if ($value['name'] == 'email1') {
$ftsQuery->join('email_addresses_primary', array('alias' => 'email1'));
$fieldsFilter[] = 'email1.email_address';
} else {
$fieldsFilter[] = $value['name'];
}
}
// need to get the doc owner to be indexed
if (isset($bean->field_defs['assigned_user_id'])) {
$docOwnerField = 'assigned_user_id';
} else {
if (isset($bean->field_defs['created_by'])) {
$docOwnerField = 'created_by';
}
}
if (!empty($docOwnerField)) {
$fieldsFilter[] = $docOwnerField;
}
$ftsQuery->select($fieldsFilter);
// join fts_queue table
$ftsQuery->joinTable($queueTableName)->on()->equalsField($queueTableName . '.bean_id', 'id');
// additional fts_queue fields
$ftsQueueFields = array(array($queueTableName . '.id', 'fts_id'), array($queueTableName . '.processed', 'fts_processed'));
$ftsQuery->select($ftsQueueFields);
return $ftsQuery->compileSql();
}
protected function runQuery(ServiceBase $api, array $args, SugarQuery $q, array $options)
{
$GLOBALS['log']->info("Filter SQL: " . $q->compileSql());
$beans = array('_rows' => array());
foreach ($q->execute() as $row) {
$beans[$row['id']] = BeanFactory::getBean($row['module'], $row['id']);
$beans['_rows'][$row['id']] = $row;
}
$rows = $beans['_rows'];
unset($beans['_rows']);
$data = array();
$data['next_offset'] = -1;
$i = 0;
foreach ($beans as $bean_id => $bean) {
if ($i == $options['limit']) {
unset($beans[$bean_id]);
$data['next_offset'] = (int) ($options['limit'] + $options['offset']);
continue;
}
$i++;
$this->populateRelatedFields($bean, $rows[$bean_id]);
}
// add on the contact_id and contact_name fields so we get those
// returned in the response
$args['fields'] .= ',contact_id,contact_name';
$data['records'] = $this->formatBeans($api, $args, $beans);
foreach ($data['records'] as $id => $record) {
$data['records'][$id]['moduleNameSingular'] = $GLOBALS['app_list_strings']['moduleListSingular'][$record['_module']];
$data['records'][$id]['moduleName'] = $GLOBALS['app_list_strings']['moduleList'][$record['_module']];
// Have to tack on from/to/description here due to not all modules
// having all these fields
if ($record['_module'] == 'Emails') {
/* @var $q SugarQuery */
$q = new SugarQuery();
$q->select(array('description', 'from_addr', 'to_addrs'));
$q->from(BeanFactory::getBean('EmailText'));
$q->where()->equals('email_id', $data['records'][$id]['id']);
foreach ($q->execute() as $row) {
$data['records'][$id]['description'] = $row['description'];
$data['records'][$id]['from_addr'] = $row['from_addr'];
$data['records'][$id]['to_addrs'] = $row['to_addrs'];
}
}
}
return $data;
}
public function retrieveCases($api, $args, $custom = false)
{
$_idRows = array();
//echo $args;
global $current_user;
//Current user
$userLogged = $current_user->id;
//Current teams
$team = BeanFactory::getBean('Teams');
$teamsForThisUser = $team->get_teams_for_user($userLogged);
$inTeams = "(";
foreach ($teamsForThisUser as $key => $teamRow) {
if ($inTeams == "(") {
$inTeams .= "'" . $teamRow->id . "'";
} else {
$inTeams .= ", '" . $teamRow->id . "'";
}
}
$inTeams .= ")";
// Init the pmse_BpmFlow bean
$flowBean = BeanFactory::getBean('pmse_BpmFlow');
$inboxBean = BeanFactory::getBean('pmse_Inbox');
$options = self::parseArguments($api, $args, $inboxBean);
if (empty($options['select'])) {
$options['select'] = self::$mandatory_fields;
}
$queryOptions = array('add_deleted' => !isset($options['add_deleted']) || $options['add_deleted'] ? true : false);
if ($queryOptions['add_deleted'] == false) {
$options['select'][] = 'deleted';
}
$q = new SugarQuery();
// $fields will store the fields required
$fields = array();
foreach ($options['select'] as $field) {
$fields[] = $field;
}
$fields = array('a.*');
//$q->from($flowBean, $queryOptions);
$q->select($fields);
$q->from($inboxBean, array('alias' => 'a'));
// Add raw joins to combine other tables
//TODO Update this way to declare joins when SugarQuery will accept them.
//$q->joinRaw('INNER JOIN pmse_inbox ON pmse_inbox.cas_id=pmse_bpm_flow.cas_id', array('alias'=>'pmse_inbox'));
//$q->joinRaw('INNER JOIN pmse_bpmn_activity ON pmse_bpm_flow.bpmn_id=pmse_bpmn_activity.id', array('alias'=>'pmse_bpmn_activity'));
$q->joinRaw("LEFT JOIN pmse_bpm_flow b ON (a.cas_id = b.cas_id)");
$q->joinRaw("LEFT JOIN pmse_bpmn_activity c ON (b.bpmn_id = c.id and b.bpmn_type = 'bpmnActivity')");
$q->joinRaw("INNER JOIN pmse_bpm_activity_definition d ON (c.id = d.id)");
//$q->joinRaw("INNER JOIN pmse_bpmn_process ON(e.id = a.pro_id)", array('alias'=>'e'));
// Add external fields using fieldRaw method
//$q->select->fieldRaw('pmse_inbox.id','inbox_id');
//$q->select->fieldRaw('pmse_inbox.name','cas_name');
//$q->select->fieldRaw('pmse_inbox.pro_title','pro_title');
//$q->select->fieldRaw('pmse_bpmn_activity.name','task_name');
if ($auxValue = $this->closeFieldFilter($args['filter'])) {
$data = array();
$data['records'] = '';
return $data;
}
$_filter_array = $this->preProcessFilters($args['filter']);
if ($this->hasStaticFilter($args['filter'])) {
$_filter_array[] = array('b.cas_user_id' => array('$equals' => array($userLogged)));
//$_filter_array[] = array('b.cas_started'=> array('$equals' => 1));
//AND (b.cas_user_id='$userLogged'
} else {
// AND (d.act_assign_team IN $inTeams AND b.cas_start_date IS NULL))
$_filter_array[] = array('d.act_assign_team' => array('$in' => $inTeams));
$_filter_array[] = array('b.cas_start_date' => array('$is_null' => ''));
}
$q->where()->queryAnd()->addRaw("b.cas_flow_status='FORM' AND a.cas_status <> 'DELETED' " . self::_filter_aux($_filter_array) . "");
//->addRaw("b.cas_flow_status='FORM' AND (b.cas_user_id='$userLogged' OR (d.act_assign_team IN $inTeams ".self::_filter_aux($_filter_array)." AND b.cas_start_date IS NULL)) AND a.cas_status <> 'DELETED'");
////->addRaw("b.cas_flow_status='FORM' AND (b.cas_user_id='$userLogged' OR (d.act_assign_team IN $inTeams AND d.act_assignment_method='selfservice' AND b.cas_start_date IS NULL)) AND a.cas_status <> 'DELETED' ".self::_filtritos($_infoFiltro)."");
//addRaw("b.cas_flow_status='FORM' AND (b.cas_user_id='$userLogged' OR (d.act_assign_team IN $inTeams AND b.cas_start_date IS NULL)) AND a.cas_status <> 'DELETED'");
$q->select->fieldRaw('b.id', 'flow_id');
$q->select->fieldRaw('b.cas_delegate_date', 'cas_delegate_date');
$q->select->fieldRaw('b.cas_start_date', 'cas_start_date');
$q->select->fieldRaw('b.cas_task_start_date', 'cas_task_start_date');
$q->select->fieldRaw('b.cas_sugar_module', 'cas_sugar_module');
$q->select->fieldRaw('c.name', 'task_name');
$q->select->fieldRaw('d.act_assignment_method', 'act_assignment_method');
$q->select->fieldRaw('d.act_expected_time', 'act_expected_time');
$q->select->fieldRaw("'true' as", 'in_time');
//$q->distinct(true);
foreach ($options['order_by'] as $orderBy) {
$q->orderBy($orderBy[0], $orderBy[1]);
}
// Add an extra record to the limit so we can detect if there are more records to be found
// $q->limit($options['limit'] + 1);
// $q->offset($options['offset']);
//remove limit for test
$data_aux = new stdClass();
$idRows = $q->execute();
$cont_aux = 1;
foreach ($idRows as $key => $value) {
$data_aux->cas_task_start_date = $value['cas_task_start_date'];
// $data_aux->cas_task_start_date = $value['cas_start_date'];
$data_aux->cas_delegate_date = $value['cas_delegate_date'];
//-----
$idRows[$key]["id2"] = $value["id"];
$idRows[$key]["id"] = $value["id"] . '_' . $cont_aux++;
//-----
$expected = $this->expectedTime($value['act_expected_time'], $data_aux);
$idRows[$key]["expected_time_warning"] = $expected["expected_time_warning"];
$idRows[$key]["expected_time_message"] = $expected["expected_time_message"];
$idRows[$key]["expected_time_view"] = $expected["expected_time_view"];
$idRows[$key]["expected_time"] = $expected["expected_time"];
//loading values
unset($idRows[$key]["in_time"]);
if ($expected["expected_time_warning"] == self::_FilterTime($_filter_array)) {
$idRows[$key]["in_time"] = false;
unset($idRows[$key]);
} else {
$idRows[$key]["in_time"] = true;
}
}
//reorganizing the record
if (count($idRows) > 0) {
foreach ($idRows as $key => $row) {
$auxRows[$key] = $row['cas_delegate_date'];
}
array_multisort($auxRows, SORT_DESC, $idRows);
}
// sort($idRows);
//loading record for limit
if (!isset($args['offset']) || (int) $args['offset'] == -1 || empty($args['offset'])) {
$_offset = 0;
} elseif ((int) $args['offset'] > 0) {
$_offset = (int) $args['offset'];
}
$_auxCont = 0;
$i = $_offset;
while ($i < $_offset + (int) $args['max_num'] && $i < count($idRows)) {
$_idRows[] = $idRows[$i++];
}
// }while($i<$_offset+(int)$args['max_num'] && $i<count($idRows));
// for($i=$_offset;$i<$_offset+(int)$args['max_num'];$i++)
// {
// $_idRows[]=$idRows[$i];
// }
if (count($idRows) > (int) $_offset + (int) $args['max_num']) {
$_nextOffset = (int) $_offset + (int) $args['max_num'];
} else {
$_nextOffset = -1;
}
//TODO Count record to calculate next_offset value
//reload $options['offset'] and $options['limit']
$options['limit'] = $args['max_num'];
if (!empty($args['offset'])) {
if ($args['offset'] == 'end') {
$options['offset'] = 'end';
} else {
$options['offset'] = $_nextOffset;
}
}
$data = array();
$data['next_offset'] = $_nextOffset;
$data['records'] = $_idRows;
// $data['records'] = $idRows;
$data['options'] = $options;
$data['args'] = $args;
$data['sql'] = $q->compileSql();
//$data['expected'] = $expected;
return $data;
}
