private function get_running_elements() { $q = new SugarQuery(); $fields = array('id', 'cas_id', 'cas_index', 'pro_id', 'bpmn_id', 'bpmn_type', 'cas_user_id', 'cas_thread', 'cas_flow_status', 'cas_sugar_module', 'cas_sugar_object_id'); $flowBean = BeanFactory::newBean('pmse_BpmFlow'); $q->select($fields); $q->from($flowBean); //$q->select()->fieldRaw('count(bpmn_id)', 'total'); $q->where()->equals('cas_id', $this->cas_id); //$q->groupBy('bpmn_id'); $q->orderBy('cas_index'); $rows = $q->execute(); $arrElements = array(); $auxArray = array(); if (is_array($rows) && !empty($rows)) { $this->pro_id = $rows[0]['pro_id']; foreach ($rows as $key => $value) { if (!array_key_exists($value['bpmn_id'], $auxArray)) { $element = new stdClass(); $element->usr_id = $value['cas_user_id']; $element->bpmn_shape = $value['bpmn_type']; $element->running = $value['cas_flow_status'] == 'FORM' || $value['cas_flow_status'] == 'SLEEPING' ? true : false; $element->terminated = $value['cas_flow_status'] == 'TERMINATED' ? true : false; $element->count = 1; $auxArray[$value['bpmn_id']] = $element; } else { $element_aux = $auxArray[$value['bpmn_id']]; $element_aux->count++; $auxArray[$value['bpmn_id']] = $element_aux; } } $arrElements = $auxArray; } return $arrElements; }
/** * Retrieves all saved reports that meet args-driven criteria * * @param $api ServiceBase The API class of the request * @param $args array The arguments array passed in from the API * @return array */ public function getSavedReports($api, $args) { // Make sure the user isn't seeing reports they don't have access to require_once 'modules/Reports/SavedReport.php'; $modules = array_keys(getACLDisAllowedModules()); $fieldList = array('id', 'name', 'module', 'report_type', 'content', 'chart_type', 'assigned_user_id'); $sq = new SugarQuery(); $sq->from(BeanFactory::getBean('Reports')); $sq->select($fieldList); $sq->orderBy('name', 'asc'); // if there were restricted modules, add those to the query if (count($modules)) { $sq->where()->notIn('module', $modules); } if (isset($args['has_charts']) && $args['has_charts'] == 'true') { $sq->where()->notEquals('chart_type', 'none'); } if (isset($args['module']) && $args['module'] !== '') { $sq->where()->in('module', array($args['module'])); } $result = $sq->execute(); // check acls foreach ($result as $key => &$row) { $savedReport = $this->getSavedReportFromData($row); if ($savedReport->ACLAccess('list')) { // for front-end to check acls $row['_acl'] = ApiHelper::getHelper($api, $savedReport)->getBeanAcl($savedReport, $fieldList); } else { unset($result[$key]); } } return $result; }
/** * Process to get an array of Timeperiods based on system configurations. It will return the n number * of backward timeperiods + current set of timeperiod + n number of future timeperiods. * * @return array id/name of TimePeriods */ public function process() { $admin = BeanFactory::getBean('Administration'); $settings = $admin->getConfigForModule('Forecasts', 'base'); $forward = $settings['timeperiod_shown_forward']; $backward = $settings['timeperiod_shown_backward']; $type = $settings['timeperiod_interval']; $leafType = $settings['timeperiod_leaf_interval']; $timedate = TimeDate::getInstance(); $timePeriods = array(); $current = TimePeriod::getCurrentTimePeriod($type); //If the current TimePeriod cannot be found for the type, just create one using the current date as a reference point if (empty($current)) { $current = TimePeriod::getByType($type); $current->setStartDate($timedate->getNow()->asDbDate()); } $startDate = $timedate->fromDbDate($current->start_date); //Move back for the number of backward TimePeriod(s) while ($backward-- > 0) { $startDate->modify($current->previous_date_modifier); } $endDate = $timedate->fromDbDate($current->end_date); //Increment for the number of forward TimePeriod(s) while ($forward-- > 0) { $endDate->modify($current->next_date_modifier); } $db = DBManagerFactory::getInstance(); $sq = new SugarQuery(); $sq->from(BeanFactory::getBean('TimePeriods')); $sq->select(array('id', 'name')); $sq->where()->notNull('parent_id')->gte('start_date', $startDate->asDbDate())->lte('start_date', $endDate->asDbDate())->addRaw("coalesce({$db->convert('type', 'length')},0) > 0"); $sq->orderBy('start_date', 'ASC'); $beans = $sq->execute(); foreach ($beans as $row) { $timePeriods[$row['id']] = $row['name']; } return $timePeriods; }
/** * This function creates a job for to run the SugarJobUpdateOpportunities class * @param integer $perJob * @returns array|string An array of the jobs that were created, unless there * is one, then just that job's id */ public static function updateOpportunitiesForForecasting($perJob = 100) { $sq = new SugarQuery(); $sq->select(array('id')); $sq->from(BeanFactory::getBean('Opportunities')); $sq->orderBy('date_closed'); $rows = $sq->execute(); if (empty($rows)) { return false; } $chunks = array_chunk($rows, $perJob); $jobs = array(); // process the first job now $job = static::createJob($chunks[0], true); $jobs[] = $job->id; // run the first job $self = new self(); $self->setJob($job); $self->sendNotifications = false; $self->run($job->data); $job_group = md5(microtime()); for ($i = 1; $i < count($chunks); $i++) { $jobs[] = static::createJob($chunks[$i], false, $job_group); } // if only one job was created, just return that id if (count($jobs) == 1) { return array_shift($jobs); } return $jobs; }
/** * Start the Commit Process for a Sales Rep * * @param string $user_id * @param string $timeperiod * @param int $chunk_size How big to make the chunks of data * @return bool */ public function commitWorksheet($user_id, $timeperiod, $chunk_size = 50) { /* @var $admin Administration */ $admin = BeanFactory::getBean('Administration'); $settings = $admin->getConfigForModule('Forecasts'); if ($settings['is_setup'] == false) { $GLOBALS['log']->fatal("Forecast Module is not setup. " . __CLASS__ . " should not be running"); return false; } /* @var $tp TimePeriod */ $tp = BeanFactory::getBean('TimePeriods', $timeperiod); if (empty($tp->id)) { $GLOBALS['log']->fatal("Unable to load TimePeriod for id: " . $timeperiod); return false; } $type = $settings['forecast_by']; $sq = new SugarQuery(); // we want the deleted records /* @var $bean_obj SugarBean */ $bean_obj = BeanFactory::getBean($type); $sq->select(array($bean_obj->getTableName() . '.*')); $sq->from($bean_obj, array('add_deleted' => false))->where()->equals('assigned_user_id', $user_id)->queryAnd()->gte('date_closed_timestamp', $tp->start_date_timestamp)->lte('date_closed_timestamp', $tp->end_date_timestamp); $sq->orderBy('date_modified', 'DESC'); $link_name = $type == 'RevenueLineItems' ? 'account_link' : 'accounts'; $bean_obj->load_relationship($link_name); $bean_obj->{$link_name}->buildJoinSugarQuery($sq, array('joinTableAlias' => 'account')); $sq->select(array(array('account.id', 'account_id'))); $beans = $sq->execute(); if (empty($beans)) { return false; } $bean_chunks = array_chunk($beans, $chunk_size); // process the first chunk self::processWorksheetDataChunk($type, $bean_chunks[0]); // process any remaining in the background for ($x = 1; $x < count($bean_chunks); $x++) { $this->createUpdateForecastWorksheetJob($type, $bean_chunks[$x], $user_id); } return true; }
/** * Do the repair * * @throws SugarQueryException */ public function repairReportsToStructure() { $mgr_worksheet = BeanFactory::getBean('ForecastManagerWorksheets'); $db = DBManagerFactory::getInstance(); //Iterate through the list of users foreach ($this->userData as $id => $data) { $reports_to_id = $data['reports_to_id']; $status = $data['status']; $deleted = $data['deleted']; //Get all the worksheets for this user for this timeperiod $query = new SugarQuery(); $query->select(array('id', 'name', 'user_id', 'assigned_user_id', 'timeperiod_id')); $query->from($mgr_worksheet); $query->where()->equals('timeperiod_id', $this->currentTimePeriod['current']); $query->where()->equals('user_id', $id); $query->where()->equals('deleted', 0); $query->orderBy('date_modified', 'DESC'); $rows = $query->execute(); //Only the first worksheet (by date_modified DESC) is kept $firstOne = true; $timePeriodName = $this->currentTimePeriod['list'][$this->currentTimePeriod['current']]['name']; //now iterate through the list of worksheets and check the assigned_user_id against the reports_to_id foreach ($rows as $row) { if ($firstOne) { $firstOne = false; if ($row['assigned_user_id'] != $reports_to_id) { //This record needs updating $mgr_worksheet->retrieve($row['id']); $mgr_worksheet->assigned_user_id = $reports_to_id; if ($deleted == 1 || $status != 'Active' || empty($reports_to_id)) { //If the user has been deleted or marked as Inactive or has no reports_to_id then mark the sheets as deleted $mgr_worksheet->deleted = 1; $mgr_worksheet->save(); if ($deleted == 1 || $status != 'Active') { $reason = "as the user is no longer active"; } elseif (empty($reports_to_id)) { $reason = "as the user has no reports_to_is set"; } $this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was DELETED {$reason}"; } else { //It just needs to be reassigned to the new manager $mgr_worksheet->save(); $timePeriodName = $this->currentTimePeriod['list'][$this->currentTimePeriod['current']]['name']; $from = $this->getUserName($row['assigned_user_id']); $to = $this->getUserName($reports_to_id); $this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was reassigned from '{$from}' to '{$to}'"; } } else { //Nothing needs to be changed $this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} correct and not changed"; } } else { //This is a duplicate commit (IT SEEMS, maybe, possibly, more than likely, NOT SURE OF THIS ONE YET) $mgr_worksheet->retrieve($row['id']); $mgr_worksheet->deleted = 1; $mgr_worksheet->save(); $this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was DELETED as a DUPLICATE"; } } } }
/** * Retrieve a user's quota using the rollup value, if available. This method is useful for * fetching user quota data when you're unsure about whether or not the given user is a manager. * If you would like to force a direct quota, pass a false value to $should_rollup. * * @param $timeperiod_id String id of the TimePeriod to retrieve quota for * @param $user_id String value of the user id to retrieve. If NULL, the $current_user is used * @param $should_rollup boolean value indicating whether or not the quota should be a rollup calculation; false by default * * @return array [currency_id => int, amount => number, formatted_amount => String] */ public function getRollupQuota($timeperiod_id, $user_id = null, $should_rollup = false) { if (is_null($user_id)) { global $current_user; $user_id = $current_user->id; } // figure out the timeperiod // if we didn't find a time period, set the time period to be the current time period if (!is_guid($timeperiod_id) && is_numeric($timeperiod_id) && $timeperiod_id != 0) { // we have a timestamp, find timeperiod it belongs in $timeperiod_id = TimePeriod::getIdFromTimestamp($timeperiod_id); } if (!is_guid($timeperiod_id)) { $timeperiod_id = TimePeriod::getCurrentId(); } $sq = new SugarQuery(); $sq->select(array('quotas.currency_id', 'quotas.amount')); $sq->from(BeanFactory::getBean('Quotas')); $sq->where()->equals('user_id', $user_id)->equals('quota_type', $should_rollup ? 'Rollup' : 'Direct')->equals('timeperiod_id', $timeperiod_id); $sq->orderBy('date_modified', 'DESC'); $sq->limit(1); // since there is only ever one row, just shift the value off the results $row = array_shift($sq->execute()); if (empty($row)) { // This is to prevent return value of false when a given timeperiod has no quota. $row = array('currency_id' => -99, 'amount' => 0); } $row['formatted_amount'] = SugarCurrency::formatAmountUserLocale($row['amount'], $row['currency_id']); return $row; }
/** * Creates a SugarQuery instance according PA relationships * @param SugarBean $seed * @param array $options * @return SugarQuery * @throws SugarQueryException */ protected static function getQueryObjectPA(SugarBean $seed, array $options) { 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(); $q->from($seed, $queryOptions); $q->distinct(false); $fields = array(); foreach ($options['select'] as $field) { // fields that aren't in field defs are removed, since we don't know // what to do with them if (!empty($seed->field_defs[$field])) { // Set the field into the field list $fields[] = $field; } } //INNER JOIN BPM INBOX TABLE $fields[] = array("date_entered", 'date_entered'); $fields[] = array("cas_id", 'cas_id'); $fields[] = array("cas_sugar_module", 'cas_sugar_module'); $fields[] = array("cas_sugar_object_id", 'cas_sugar_object_id'); $fields[] = array("cas_user_id", 'cas_user_id'); $q->joinTable('pmse_inbox', array('alias' => 'inbox', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('inbox.cas_id', 'cas_id')->equals('inbox.deleted', 0); $fields[] = array("inbox.id", 'inbox_id'); $fields[] = array("inbox.cas_title", 'cas_title'); $q->where()->equals('cas_flow_status', 'FORM'); //INNER JOIN BPMN ACTIVITY DEFINITION $q->joinTable('pmse_bpmn_activity', array('alias' => 'activity', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('activity.id', 'bpmn_id')->equals('activity.deleted', 0); $fields[] = array("activity.name", 'act_name'); //INNER JOIN BPMN ACTIVITY DEFINTION $q->joinTable('pmse_bpm_activity_definition', array('alias' => 'activity_definition', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('activity_definition.id', 'activity.id')->equals('activity_definition.deleted', 0); $fields[] = array("activity_definition.act_assignment_method", 'act_assignment_method'); //INNER JOIN BPMN PROCESS DEFINTION $q->joinTable('pmse_bpmn_process', array('alias' => 'process', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('process.id', 'inbox.pro_id')->equals('process.deleted', 0); $fields[] = array("process.name", 'pro_title'); $fields[] = array("process.prj_id", 'prj_id'); $fields[] = array("process.created_by", 'prj_created_by'); //INNER JOIN USER_DATA DEFINTION $q->joinTable('users', array('alias' => 'user_data', 'joinType' => 'LEFT', 'linkingTable' => true))->on()->equalsField('user_data.id', 'cas_user_id')->equals('user_data.deleted', 0); $fields[] = array("user_data.first_name", 'first_name'); $fields[] = array("user_data.last_name", 'last_name'); //INNER JOIN TEAM_DATA DEFINTION $q->joinTable('teams', array('alias' => 'team_data', 'joinType' => 'LEFT', 'linkingTable' => true))->on()->equalsField('team_data.id', 'cas_user_id')->equals('team_data.deleted', 0); $fields[] = array("team.name", 'team_name'); $q->select($fields)->fieldRaw('user_data.last_name', 'assigned_user_name'); foreach ($options['order_by'] as $orderBy) { if ($orderBy[0] == 'pro_title') { $orderBy[0] = 'process.name'; } if ($orderBy[0] == 'task_name') { $orderBy[0] = 'activity.name'; } if ($orderBy[0] == 'cas_title') { $orderBy[0] = 'inbox.cas_title'; } if ($orderBy[0] == 'cas_user_id_full_name') { $orderBy[0] = 'cas_user_id'; } if ($orderBy[0] == 'prj_user_id_full_name') { $orderBy[0] = 'prj_created_by'; } if ($orderBy[0] == 'assigned_user_name') { $orderBy[0] = 'assigned_user_name'; } $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']); return $q; }
/** * Returns query to ask for email addresses for specific bean type * @param string $module * @return SugarQuery */ public function getEmailsQuery($module) { $q = new SugarQuery(); $q->from($this); $q->select(array('email_address', 'opt_out', 'invalid_email', 'ear.primary_address', 'ear.reply_to_address')); $q->joinTable("email_addr_bean_rel", array('alias' => "ear", 'joinType' => "LEFT", "linkingTable" => true))->on()->equalsField('id', 'ear.email_address_id', $this)->equals('ear.deleted', 0); $q->where()->equals('deleted', 0)->equals('ear.bean_module', $this->getCorrectedModule($module)); $q->orderBy('ear.primary_address', 'DESC'); return $q; }
/** * * Moved function from BpmInboxViewShowHistoryEntries class [view.showhistoryentries.php] * Using variable members and some fields added. */ public function assemblyEntries() { $entries = array(); $queryOptions = array('add_deleted' => true); $beanFlow = BeanFactory::getBean('pmse_BpmFlow'); $fields = array('id', 'date_entered', 'date_modified', 'cas_id', 'cas_index', 'pro_id', 'cas_previous', 'cas_reassign_level', 'bpmn_id', 'bpmn_type', 'cas_user_id', 'cas_thread', 'cas_flow_status', 'cas_sugar_module', 'cas_sugar_object_id', 'cas_sugar_action', 'cas_adhoc_type', 'cas_adhoc_parent_id', 'cas_task_start_date', 'cas_delegate_date', 'cas_start_date', 'cas_finish_date', 'cas_due_date', 'cas_queue_duration', 'cas_duration', 'cas_delay_duration', 'cas_started', 'cas_finished', 'cas_delayed'); $q = new SugarQuery(); $q->from($beanFlow, $queryOptions); $q->distinct(false); $q->where()->equals('cas_id', $this->case_id); $q->orderBy('cas_index', 'ASC'); $q->select($fields); $caseDerivations = $q->execute(); foreach ($caseDerivations as $key => $caseData) { $entry = $this->fetchUserType($caseData); $currentDate = new DateTime(); $entry['due_date'] = !empty($caseData['cas_due_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_due_date'], 'datetime') : ''; $entry['end_date'] = !empty($caseData['cas_finish_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_finish_date'], 'datetime') : ''; $entry['current_date'] = PMSEEngineUtils::getDateToFE(TimeDate::getInstance()->nowDb(), 'datetime'); $entry['delegate_date'] = !empty($caseData['cas_delegate_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_delegate_date'], 'datetime') : ''; $entry['start_date'] = !empty($caseData['cas_start_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_start_date'], 'datetime') : ''; $entry['var_values'] = ''; $entry['completed'] = true; $entry['cas_user_id'] = $caseData['cas_user_id']; if ($caseData['cas_previous'] == 0) { //cas_flow_status field should set something instead be empty. $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_CREATED_CASE', 'pmse_Inbox'), $caseData['cas_id']); } else { if ($caseData['cas_flow_status'] == 'CLOSED') { $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_DERIVATED_CASE', 'pmse_Inbox'), $caseData['bpmn_id']); } else { $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_CURRENTLY_HAS_CASE', 'pmse_Inbox'), $caseData['bpmn_id']); } } $action = ''; if ($caseData['bpmn_type'] == 'bpmnActivity') { $currentCaseState = $this->getActionStatusAndAction($caseData['cas_flow_status'], $caseData['cas_sugar_action']); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getActivityName($caseData['bpmn_id'])); if ($caseData['cas_flow_status'] != 'FORM') { $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); $res = $this->formAction->retrieve_by_string_fields(array('cas_id' => $caseData['cas_id'], 'act_id' => $caseData['bpmn_id'], 'user_id' => $caseData['cas_user_id'])); if (isset($this->formAction->frm_action) && !empty($this->formAction->frm_action)) { $action = strtoupper($this->formAction->frm_action); } else { $action = translate('LBL_PMSE_HISTORY_LOG_NOT_REGISTED_ACTION', 'pmse_Inbox'); } $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTION_PERFORMED', 'pmse_Inbox'), $action); if (isset($this->formAction->cas_pre_data)) { $logdata = unserialize($this->formAction->cas_pre_data); $entry['var_values'] = $logdata; } } else { $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTION_STILL_ASSIGNED', 'pmse_Inbox')); $entry['completed'] = false; } } else { if ($caseData['bpmn_type'] == 'bpmnEvent') { $name = sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getEventName($caseData['bpmn_id'])); $currentCaseState = sprintf(translate('LBL_PMSE_HISTORY_LOG_WITH_EVENT', 'pmse_Inbox'), $name); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); } else { if ($caseData['bpmn_type'] == 'bpmnGateway') { $name = sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getEventName($caseData['bpmn_id'])); $currentCaseState = sprintf(translate('LBL_PMSE_HISTORY_LOG_WITH_GATEWAY', 'pmse_Inbox'), $name); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); } } } $entry['data_info'] = $dataString; $entries[] = $entry; } return $entries; }
/** * @param $api * @param $seed * @param $tp * @param $amount_field * @param string $type * @return SugarQuery * @throws SugarQueryException */ protected function buildQuery($api, $seed, $tp, $amount_field, $type = 'user') { // build out the query $sq = new SugarQuery(); $sq->select(array('id', 'sales_stage', $amount_field, 'base_rate')); $sq->from($seed)->where()->gte('date_closed_timestamp', $tp->start_date_timestamp)->lte('date_closed_timestamp', $tp->end_date_timestamp); $sq->orderBy('probability', 'DESC'); // determine the type we need to fetch if ($type == 'user') { // we are only looking at our pipeline $sq->where()->equals('assigned_user_id', $api->user->id); } else { // we need to fetch ours + everyone under us (the whole tree) // get the reporting users $users = $this->getReportingUsers($api->user->id); // add current_user to the users_list array_unshift($users, $api->user->id); $sq->where()->in('assigned_user_id', array_values($users)); } return $sq; }
/** * Find all the Opportunities and Create RLI's for them, this will process the last 100 modified Opportunities * right away, and schedule the rest in chunks of 100 for the Scheduler to Take care of * * @throws SugarQueryException */ protected function createRevenueLineItems() { // get the list of opps that are not deleted $sq = new SugarQuery(); $sq->select(array('id')); $sq->from($this->bean); $sq->orderBy('date_modified', 'DESC'); $opps = $sq->execute(); if (empty($opps)) { return false; } $bean_chunks = array_chunk($opps, 100); // process the first chunk $this->processOpportunityIds($bean_chunks[0]); $job_group = md5(microtime()); // process any remaining in the background for ($x = 1; $x < count($bean_chunks); $x++) { $this->createRevenueLineItemJob($bean_chunks[$x], $job_group); } }
public function getUnattendedCases($api, $args) { global $db; $this->checkACL($api, $args); $queryOptions = array('add_deleted' => true); $arrayUnattendedCases = $this->getUnattendedCasesByFlow(); //Get Cases IN TODO $beanInbox = BeanFactory::getBean('pmse_Inbox'); $fields = array('id', 'assigned_user_id', 'date_modified', 'date_entered', 'name', 'cas_id', 'cas_title', 'cas_status', 'pro_title', 'pro_id', 'cas_init_user'); $q = new SugarQuery(); $q->from($beanInbox, $queryOptions); $q->distinct(false); $q->where()->equals('cas_status', 'IN PROGRESS'); $enabledQuery = true; $q->select($fields); if ($args['module_list'] == 'all' && !empty($args['q'])) { $q->where()->queryAnd()->addRaw("pmse_inbox.cas_title LIKE '%" . $args['q'] . "%' OR pmse_inbox.pro_title LIKE '%" . $args['q'] . "%' "); } else { if (!empty($args['q'])) { switch ($args['module_list']) { case translate('LBL_CAS_ID', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("pmse_inbox.cas_id = " . $db->quoted($args['q'])); break; case translate('LBL_PROCESS_DEFINITION_NAME', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("pmse_inbox.pro_title LIKE '%" . $args['q'] . "%'"); break; case translate('LBL_RECORD_NAME', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("pmse_inbox.cas_title LIKE '%" . $args['q'] . "%'"); break; case translate('LBL_OWNER', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("pmse_inbox.cas_init_user LIKE '%" . $args['q'] . "%'"); break; } } } if (isset($args['order_by'])) { $columnToSort = explode(":", $args["order_by"]); $q->orderBy($columnToSort[0], empty($columnToSort[1]) ? "asc" : $columnToSort[1]); } $rows = $q->execute(); $rows_aux = array(); $result = array(); foreach ($arrayUnattendedCases as $key => $row) { $result[] = $row['cas_id']; } foreach ($rows as $key => $row) { $arrayId = array_search($row['cas_id'], $result); if ($arrayId !== false) { $usersBean = BeanFactory::getBean('Users', $arrayUnattendedCases[$arrayId]['cas_user_id']); $row['cas_user_full_name'] = $usersBean->full_name; $processBean = BeanFactory::getBean('pmse_BpmnProcess', $row['pro_id']); $row['prj_id'] = $processBean->prj_id; $prjUsersBean = BeanFactory::getBean('Users', $processBean->created_by); $row['prj_user_id_full_name'] = $prjUsersBean->full_name; $row['cas_sugar_object_id'] = $arrayUnattendedCases[$arrayId]['cas_sugar_object_id']; $row['cas_sugar_module'] = $arrayUnattendedCases[$arrayId]['cas_sugar_module']; $assignedBean = BeanFactory::getBean($row['cas_sugar_module'], $row['cas_sugar_object_id']); $assignedUsersBean = BeanFactory::getBean('Users', $assignedBean->assigned_user_id); $row['assigned_user_name'] = $assignedUsersBean->full_name; $row['date_entered'] = PMSEEngineUtils::getDateToFE($row['date_entered'], 'datetime'); $rows_aux[] = $row; } } return array('next_offset' => '-1', 'records' => $rows_aux); }
public function selectCasesList($api, $args) { $this->checkACL($api, $args); $q = new SugarQuery(); $inboxBean = BeanFactory::getBean('pmse_Inbox'); if ($args['order_by'] == 'cas_due_date:asc') { $args['order_by'] = 'cas_create_date:asc'; } $options = self::parseArguments($api, $args, $inboxBean); $fields = array('a.*'); $q->from($inboxBean, array('alias' => 'a')); //INNER USER TABLE $q->joinTable('users', array('alias' => 'u', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('u.id', 'a.created_by')->equals('u.deleted', 0); $fields[] = array("u.last_name", 'assigned_user_name'); //INNER PROCESS TABLE $q->joinTable('pmse_bpmn_process', array('alias' => 'pr', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('pr.id', 'a.pro_id')->equals('pr.deleted', 0); $fields[] = array("pr.prj_id", 'prj_id'); //INNER PROJECT TABLE $q->joinTable('pmse_project', array('alias' => 'prj', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('prj.id', 'pr.prj_id')->equals('prj.deleted', 0); $fields[] = array("prj.assigned_user_id", 'prj_created_by'); $fields[] = array("prj.prj_module", 'prj_module'); $q->select($fields); $q->where()->in('prj.prj_module', PMSEEngineUtils::getSupportedModules()); if (!empty($args['q'])) { $q->where()->queryAnd()->addRaw("a.cas_title LIKE '%" . $args['q'] . "%' OR a.pro_title LIKE '%" . $args['q'] . "%' OR a.cas_status LIKE '%" . $args['q'] . "%' OR prj.assigned_user_id LIKE '%" . $args['q'] . "%' OR pr.prj_id LIKE '%" . $args['q'] . "%' OR last_name LIKE '%" . $args['q'] . "%'"); } if (!empty($args['module_list'])) { switch ($args['module_list']) { case translate('LBL_STATUS_COMPLETED', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("cas_status = 'COMPLETED'"); break; case translate('LBL_STATUS_TERMINATED', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("cas_status = 'TERMINATED'"); break; case translate('LBL_STATUS_IN_PROGRESS', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("cas_status = 'IN PROGRESS'"); break; case translate('LBL_STATUS_CANCELLED', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("cas_status = 'CANCELLED'"); break; case translate('LBL_STATUS_ERROR', 'pmse_Inbox'): $q->where()->queryAnd()->addRaw("cas_status = 'ERROR'"); break; } } 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']); $q->offset($options['offset']); $offset = $options['offset'] + $options['limit']; $count = 0; $list = $q->execute(); foreach ($list as $key => $value) { if ($value["cas_status"] === 'IN PROGRESS') { $list[$key]["cas_status"] = '<data class="label label-Leads">' . $value["cas_status"] . '</data>'; } elseif ($value["cas_status"] === 'COMPLETED' || $value["cas_status"] === 'TERMINATED') { $list[$key]["cas_status"] = '<data class="label label-success">' . $value["cas_status"] . '</data>'; } elseif ($value["cas_status"] === 'CANCELLED') { $list[$key]["cas_status"] = '<data class="label label-warning">' . $value["cas_status"] . '</data>'; } else { $list[$key]["cas_status"] = '<data class="label label-important">' . $value["cas_status"] . '</data>'; } $list[$key]['cas_create_date'] = PMSEEngineUtils::getDateToFE($value['cas_create_date'], 'datetime'); $list[$key]['date_entered'] = PMSEEngineUtils::getDateToFE($value['date_entered'], 'datetime'); $list[$key]['date_modified'] = PMSEEngineUtils::getDateToFE($value['date_modified'], 'datetime'); $prjUsersBean = BeanFactory::getBean('Users', $list[$key]['prj_created_by']); $list[$key]['prj_user_id_full_name'] = $prjUsersBean->full_name; $qA = new SugarQuery(); $flowBean = BeanFactory::getBean('pmse_BpmFlow'); $qA->select->fieldRaw('*'); $qA->from($flowBean); $qA->where()->equals('cas_id', $list[$key]['cas_id']); $processUsers = $qA->execute(); $processUsersNames = array(); foreach ($processUsers as $k => $v) { if ($processUsers[$k]['cas_flow_status'] != 'CLOSED') { $casUsersBean = BeanFactory::getBean('Users', $processUsers[$k]['cas_user_id']); $processUsersNames[] = !empty($casUsersBean->full_name) ? $casUsersBean->full_name : ''; } $cas_sugar_module = $processUsers[$k]['cas_sugar_module']; $cas_sugar_object_id = $processUsers[$k]['cas_sugar_object_id']; } if (empty($processUsersNames)) { $userNames = ''; } else { $processUsersNames = array_unique($processUsersNames); $userNames = implode(', ', $processUsersNames); } $list[$key]['cas_user_id_full_name'] = $userNames; $assignedBean = BeanFactory::getBean($cas_sugar_module, $cas_sugar_object_id); $assignedUsersBean = BeanFactory::getBean('Users', $assignedBean->assigned_user_id); $list[$key]['assigned_user_name'] = $assignedUsersBean->full_name; $count++; } if ($count == $options['limit']) { $offset = $options['offset'] + $options['limit']; } else { $offset = -1; } $data = array(); $data['next_offset'] = $offset; $data['records'] = $list; 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; }
/** * Generates query for fetching non-primary emails for the given beans * * @param SugarBean $bean Bean instance * @param array $ids Bean IDs * * @return SugarQuery */ function getNonPrimaryEmailsExportQuery(SugarBean $bean, array $ids) { $query = new SugarQuery(); $query->from(BeanFactory::newBean('EmailAddresses'), 'ea'); $query->joinTable('email_addr_bean_rel', array('joinType' => 'LEFT', 'alias' => 'eabr', 'linkingTable' => true))->on()->equalsField('eabr.email_address_id', 'id'); $query->select('eabr.bean_id', 'email_address', 'invalid_email', 'opt_out'); $query->where()->in('eabr.bean_id', $ids)->equals('eabr.bean_module', $bean->module_dir)->notEquals('eabr.primary_address', 1)->notEquals('eabr.deleted', 1); $query->orderBy('eabr.bean_id', 'ASC')->orderBy('eabr.reply_to_address', 'ASC')->orderBy('email_address', 'ASC'); return $query; }