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