/** * 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; }