/**
* 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;
}
/**
* Build a Join Query with a SugarQuery Object
*
* @param Link2 $link
* @param SugarQuery $sugar_query
* @param Array $options array of additional paramters. Possible parameters include
* - 'myAlias' String name of starting table alias
* - 'joinTableAlias' String alias to use for the related table in the final result
* - 'reverse' Boolean true if this join should be built in reverse for subpanel style queries where the select is
* on the related table
* - 'ignoreRole' Boolean true if the role column of the relationship should be ignored for this join .
*
* @return SugarQuery
*/
public function buildJoinSugarQuery(Link2 $link, $sugar_query, $options)
{
$linkIsLHS = $link->getSide() == REL_LHS;
if (!empty($options['reverse'])) {
$linkIsLHS = !$linkIsLHS;
}
$startingTable = $linkIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table'];
$startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
$targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table'];
$targetModule = $linkIsLHS ? $this->def['rhs_module'] : $this->def['lhs_module'];
$targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
$join_type = isset($options['joinType']) ? $options['joinType'] : 'INNER';
$joinParams = array('joinType' => $join_type, 'bean' => BeanFactory::newBean($targetModule));
$jta = $targetTable;
if (!empty($options['joinTableAlias'])) {
$jta = $joinParams['alias'] = $options['joinTableAlias'];
}
$joinTable = $sugar_query->joinTable($targetTable, $joinParams);
$joinTable->on()->equalsField("{$startingTable}.{$startingKey}", "{$jta}.{$targetKey}")->equals("{$jta}.deleted", "0");
$relTable = $linkIsLHS ? $jta : $startingTable;
if (empty($options['ignoreRole'])) {
foreach ($this->getRelationshipRoleColumns() as $column => $value) {
$sugar_query->where()->equals("{$relTable}.{$column}", $value);
}
}
$this->addCustomToSugarQuery($sugar_query, $options, $linkIsLHS, $jta);
return $sugar_query->join[$jta];
}
private function get_project_elements($prj_id, $dia_id, $table)
{
$boundFields = array('bound.bou_element', 'bound.bou_element_type', 'bound.bou_x', 'bound.bou_y', 'bound.bou_width', 'bound.bou_height', 'bound.bou_rel_position', 'bound.bou_size_identical', 'bound.bou_container');
$elementFields = array();
switch ($table) {
case 'bpmn_activity':
$bean = BeanFactory::newBean('pmse_BpmnActivity');
$elementFields = array('id', 'name', 'act_type', 'act_is_for_compensation', 'act_start_quantity', 'act_completion_quantity', 'act_task_type', 'act_implementation', 'act_instantiate', 'act_script_type', 'act_script', 'act_loop_type', 'act_test_before', 'act_loop_maximum', 'act_loop_condition', 'act_loop_cardinality', 'act_loop_behavior', 'act_is_adhoc', 'act_is_collapsed', 'act_completion_condition', 'act_ordering', 'act_cancel_remaining_instances', 'act_protocol', 'act_method', 'act_is_global', 'act_referer', 'act_default_flow', 'act_master_diagram');
break;
case 'bpmn_event':
$bean = BeanFactory::newBean('pmse_BpmnEvent');
$elementFields = array('id', 'name', 'evn_type', 'evn_marker', 'evn_is_interrupting', 'evn_attached_to', 'evn_cancel_activity', 'evn_activity_ref', 'evn_wait_for_completion', 'evn_error_name', 'evn_error_code', 'evn_escalation_name', 'evn_escalation_code', 'evn_condition', 'evn_message', 'evn_operation_name', 'evn_operation_implementation', 'evn_time_date', 'evn_time_cycle', 'evn_time_duration', 'evn_behavior');
break;
case 'bpmn_gateway':
$bean = BeanFactory::newBean('pmse_BpmnGateway');
$elementFields = array('id', 'name', 'gat_type', 'gat_direction', 'gat_instantiate', 'gat_event_gateway_type', 'gat_activation_count', 'gat_waiting_for_start', 'gat_default_flow');
break;
case 'bpmn_artifact':
$bean = BeanFactory::newBean('pmse_BpmnArtifact');
$elementFields = array('id', 'name', 'art_type', 'art_category_ref');
break;
}
$q = new SugarQuery();
//add fields to select
$fields = array_merge($elementFields, $boundFields);
$q->select($fields);
$q->from($bean);
$q->joinTable('pmse_bpmn_bound', array('alias' => 'bound', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('bound.bou_element', 'id')->equals('bound.deleted', 0);
$q->select()->field($boundFields);
$q->where()->equals('prj_id', $prj_id);
$rows = $q->execute();
return $rows;
}
/**
* 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;
}
/**
* Build a Join using an existing SugarQuery Object
* @param Link2 $link
* @param SugarQuery $sugar_query
* @param Array $options array of additional paramters. Possible parameters include
* - 'myAlias' String name of starting table alias
* - 'joinTableAlias' String alias to use for the related table in the final result
* - 'reverse' Boolean true if this join should be built in reverse for subpanel style queries where the select is
* on the related table
* - 'ignoreRole' Boolean true if the role column of the relationship should be ignored for this join .
* @return SugarQuery
*/
public function buildJoinSugarQuery(Link2 $link, $sugar_query, $options)
{
$linkIsLHS = $link->getSide() == REL_LHS;
if (!empty($options['reverse'])) {
$linkIsLHS = !$linkIsLHS;
}
$startingTable = $linkIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table'];
if (!empty($options['myAlias'])) {
$startingTable = $options['myAlias'];
}
$startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key'];
$startingJoinKey = $linkIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs'];
$joinTable = $this->getRelationshipTable();
$joinKey = $linkIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs'];
$targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table'];
$targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key'];
$targetModule = $linkIsLHS ? $this->def['rhs_module'] : $this->def['lhs_module'];
$join_type = isset($options['joinType']) ? $options['joinType'] : 'INNER';
$joinTable_alias = $sugar_query->getJoinTableAlias($joinTable, false, false);
$targetTable_alias = !empty($options['joinTableAlias']) ? $options['joinTableAlias'] : $targetTable;
$relTableJoin = $sugar_query->joinTable($joinTable, array('alias' => $joinTable_alias, 'joinType' => $join_type, 'linkingTable' => true))->on()->equalsField("{$startingTable}.{$startingKey}", "{$joinTable_alias}.{$startingJoinKey}")->equals("{$joinTable_alias}.deleted", "0");
$targetTableJoin = $sugar_query->joinTable($targetTable, array('alias' => $targetTable_alias, 'joinType' => $join_type, 'bean' => BeanFactory::newBean($targetModule)))->on()->equalsField("{$targetTable_alias}.{$targetKey}", "{$joinTable_alias}.{$joinKey}")->equals("{$targetTable_alias}.deleted", "0");
$sugar_query->join[$targetTable_alias]->relationshipTableAlias = $joinTable_alias;
if (empty($options['ignoreRole'])) {
$this->buildSugarQueryRoleWhere($sugar_query, $joinTable_alias);
}
$this->addCustomToSugarQuery($sugar_query, $options, $linkIsLHS, $targetTable_alias);
return array($joinTable_alias => $relTableJoin, $targetTable_alias => $targetTableJoin);
}
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;
}
/**
* Add a Favorites block to the SugarQuery Object to fetch favorites for a specific [default to current] user
* @param SugarQuery $sugar_query
* @param bool $joinTo
* @param string $join_type
* @param bool|guid $user_id
* @return string
*/
public function addToSugarQuery(SugarQuery $sugar_query, $options = array())
{
$alias = '';
$user_id = !isset($options['current_user_id']) ? $GLOBALS['current_user']->id : $options['current_user_id'];
$joinTo = !isset($options['joinTo']) ? false : $options['joinTo'];
$joinType = !isset($options['joinType']) ? 'INNER' : $options['joinType'];
if (!$joinTo) {
if (is_array($sugar_query->from)) {
list($bean, $alias) = $sugar_query->from;
} else {
$bean = $sugar_query->from;
$alias = $bean->getTableName();
}
} else {
$alias = $joinTo->joinName();
$bean = $sugar_query->getTableBean($alias);
}
if (empty($bean)) {
return false;
}
$sfAlias = "sf_" . $bean->getTableName();
$sugar_query->joinTable(self::getTableName(), array('alias' => $sfAlias, 'joinType' => $joinType, 'linkingTable' => true))->on()->equals("{$sfAlias}.module", $bean->module_name, $this)->equalsField("{$sfAlias}.record_id", "{$alias}.id", $this)->equals("{$sfAlias}.assigned_user_id", $user_id, $this)->equals("{$sfAlias}.deleted", 0, $this);
return $sfAlias;
}
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();
}
private function getUnattendedCasesByFlow()
{
$queryOptions = array('add_deleted' => true);
//GET CASES ID WITH INACTIVE USERS
$beanFlow = BeanFactory::getBean('pmse_BpmFlow');
$q = new SugarQuery();
$q->from($beanFlow, $queryOptions);
$q->distinct(true);
$fields = array('cas_id', 'cas_sugar_module', 'cas_sugar_object_id', 'cas_user_id');
//INNER JOIN USERS TABLE
$q->joinTable('users', array('alias' => 'users', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('users.id', 'cas_user_id')->equals('users.deleted', 0);
$q->where()->equals('cas_flow_status', 'FORM')->in('cas_sugar_module', PMSEEngineUtils::getSupportedModules());
$q->where()->queryOr()->notequals('users.status', 'Active')->notequals('users.employee_status', 'Active');
$q->select($fields);
$rows = $q->execute();
return $rows;
}
/**
* 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;
}
