/** * Returns query object to retrieve list of recently viewed records by * module. * * @param SugarBean $seed Instance of current bean. * @param array $options Prepared options. * @return SugarQuery query to execute. */ protected function getRecentlyViewedQueryObject($seed, $options) { $currentUser = $this->getUserBean(); $query = new SugarQuery(); $query->from($seed); // FIXME: FRM-226, logic for these needs to be moved to SugarQuery // Since tracker relationships don't actually exist, we're gonna have to add a direct join $query->joinRaw(sprintf(" JOIN tracker ON tracker.item_id=%s.id AND tracker.module_name='%s' AND tracker.user_id='%s' ", $query->from->getTableName(), $query->from->module_name, $currentUser->id), array('alias' => 'tracker')); // we need to set the linkName to hack around tracker not having real relationships /* TODO think about how to fix this so we can be less restrictive to raw joins that don't have a relationship */ $query->join['tracker']->linkName = 'tracker'; $query->select(array('id', array('tracker.module_name', 'module_name'))); if (!empty($options['date'])) { $td = new SugarDateTime(); $td->modify($options['date']); $query->where()->queryAnd()->gte('tracker.date_modified', $td->asDb()); } foreach ($query->select()->select as $v) { $query->groupBy($v->table . '.' . $v->field); } $query->select()->fieldRaw('MAX(tracker.date_modified)', 'last_viewed_date'); return $query; }
/** * Get the project Diagram data with a determined Project Id * @param string $prjID * @return array */ public function getProjectDiagram($prjID) { $diagramBean = BeanFactory::getBean('pmse_BpmnDiagram'); //new BpmnDiagram(); $diagramData = array(); $activityBean = BeanFactory::getBean('pmse_BpmnActivity'); //new BpmnActivity(); $activityData = array(); $artifactBean = BeanFactory::getBean('pmse_BpmnArtifact'); //new BpmnArtifact(); $artifactData = array(); $gatewayBean = BeanFactory::getBean('pmse_BpmnGateway'); //new BpmnGateway(); $gatewayData = array(); $eventBean = BeanFactory::getBean('pmse_BpmnEvent'); //new BpmnEvent(); $eventData = array(); $flowBean = BeanFactory::getBean('pmse_BpmnFlow'); //new BpmnFlow(); $flowData = array(); $rulesetBean = BeanFactory::getBean('pmse_Business_Rules'); //new BpmRuleSet(); $rulesetData = array(); $lanesetBean = BeanFactory::getBean('pmse_BpmnLaneset'); //new BpmnLaneset(); $lanesetData = array(); $laneBean = BeanFactory::getBean('pmse_BpmnLane'); //new BpmnLane(); $laneData = array(); // $participantBean = BeanFactory::getBean('pmse_BpmnParticipant'); //new BpmnParticipant(); // $participantData = array(); $processBean = BeanFactory::getBean('pmse_BpmnProcess'); //new BpmnProcess(); $processData = array(); $retrievedDataBean = BeanFactory::getBean('pmse_BpmnData'); //new BpmnData(); $retrievedData = array(); $documentationBean = BeanFactory::getBean('pmse_BpmnDocumentation'); //new BpmnDocumentation(); $documentationData = array(); $extensionBean = BeanFactory::getBean('pmse_BpmnExtension'); //new BpmnExtension(); $extensionData = array(); $conditions = array("prj_id" => $prjID); if ($diagramBean->retrieve_by_string_fields($conditions)) { $diagramBean->fetched_row = PMSEEngineUtils::unsetCommonFields($diagramBean->fetched_row); // list of activities based in the project id //$data = $activityBean->getSelectRows("", "bpmn_activity.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnActivity'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_activity.act_id=bpmn_bound.bou_element'), array('LEFT', 'bpm_activity_definition', 'bpmn_activity.act_id=bpm_activity_definition.act_id'))); $q = new SugarQuery(); $q->from($activityBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $q->joinRaw("LEFT JOIN pmse_bpm_activity_definition c ON (a.id=c.id)", array('alias' => 'c')); $fields = $this->getFields('pmse_BpmnActivity', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnActivity'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $fields_ad = $this->getFields('pmse_BpmActivityDefinition', array(), 'c'); foreach ($fields_ad as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $tmpObject = $flowBean->retrieve_by_string_fields(array("id" => $row['act_default_flow'])); $row['act_default_flow'] = isset($tmpObject->flo_uid) ? $tmpObject->flo_uid : ''; if ($row['act_task_type'] == "SCRIPTTASK" && $row['act_script_type'] == "BUSINESS_RULE") { $row['act_fields'] = isset($row['act_fields']) ? $row['act_fields'] : ''; $ruleset = $rulesetBean->retrieve_by_string_fields(array('id' => $row['act_fields'])); if ($ruleset) { $row['act_fields'] = $ruleset->rst_uid; $this->rulesetList[] = $ruleset->fetched_row; } } $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $activityData[] = $row; } } $diagramBean->fetched_row['activities'] = $activityData; // list of events based in the project id //$data = $eventBean->getSelectRows("", "bpmn_event.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnEvent'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_event.evn_id=bpmn_bound.bou_element'), array('LEFT', 'bpm_event_definition', 'bpmn_event.evn_id=bpm_event_definition.evn_id'))); $q = new SugarQuery(); $q->from($eventBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $q->joinRaw("LEFT JOIN pmse_bpm_event_definition c ON (a.id=c.id)", array('alias' => 'c')); $fields = $this->getFields('pmse_BpmnEvent', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnEvent'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $fields_ad = $this->getFields('pmse_BpmEventDefinition', array(), 'c'); foreach ($fields_ad as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_attached_to'])); $row['evn_attached_to'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : ''; $tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_cancel_activity'])); $row['evn_cancel_activity'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : ''; $tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_activity_ref'])); $row['evn_activity_ref'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : ''; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $eventData[] = $row; } } $diagramBean->fetched_row['events'] = $eventData; // list of gateways based in the project id //$data = $gatewayBean->getSelectRows("", "bpmn_gateway.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnGateway'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_gateway.gat_id=bpmn_bound.bou_element'))); $q = new SugarQuery(); $q->from($gatewayBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $fields = $this->getFields('pmse_BpmnGateway', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnGateway'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $flowObject = $flowBean->retrieve_by_string_fields(array("id" => $row['gat_default_flow'])); $row['gat_default_flow'] = isset($flowObject->flo_uid) ? $flowObject->flo_uid : ''; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $gatewayData[] = $row; } } $diagramBean->fetched_row['gateways'] = $gatewayData; // list of artifacts based in the project id //$data = $artifactBean->getSelectRows("", "bpmn_artifact.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnArtifact'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_artifact.art_id=bpmn_bound.bou_element'))); $q = new SugarQuery(); $q->from($artifactBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $fields = $this->getFields('pmse_BpmnArtifact', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnArtifact'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $artifactData[] = $row; } } $diagramBean->fetched_row['artifacts'] = $artifactData; // list of flows based in the project id //$data = $flowBean->getSelectRows("", "bpmn_flow.prj_id=" . $prjID, 0, -1, -1, array()); $rows = $flowBean->get_full_list('', "prj_id='" . $prjID . "'"); if (!empty($rows)) { foreach ($rows as $row) { $row = $row->fetched_row; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeKeyFields($row); $row['prj_id'] = $prjID; $row['flo_element_origin'] = PMSEEngineUtils::getElementUid($row['flo_element_origin'], $row['flo_element_origin_type'], PMSEEngineUtils::getEntityUid($row['flo_element_origin_type'])); $row['flo_element_dest'] = PMSEEngineUtils::getElementUid($row['flo_element_dest'], $row['flo_element_dest_type'], PMSEEngineUtils::getEntityUid($row['flo_element_dest_type'])); $row['flo_state'] = json_decode($row['flo_state']); $row['flo_condition'] = json_decode($row['flo_condition']); $row['flo_condition'] = !empty($row['flo_condition']) ? $this->processBusinessRulesData($row['flo_condition']) : ''; $flowData[] = $row; } } $diagramBean->fetched_row['flows'] = $flowData; // list of pools based in the project id //$data = $lanesetBean->getSelectRows("", "bpmn_laneset.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnLaneset'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_laneset.lns_id=bpmn_bound.bou_element'))); $q = new SugarQuery(); $q->from($lanesetBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $fields = $this->getFields('pmse_BpmnLaneset', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnLaneset'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $lanesetData[] = $row; } } $diagramBean->fetched_row['pools'] = $lanesetData; // list of lanes based in the project id //$data = $laneBean->getSelectRows("", "bpmn_lane.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnLane'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_lane.lan_id=bpmn_bound.bou_element'))); $q = new SugarQuery(); $q->from($laneBean, array('alias' => 'a')); $q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b')); $fields = $this->getFields('pmse_BpmnLane', array('name'), 'a'); $q->select($fields); $q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnLane'"); $fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b'); foreach ($fields_bound as $key => $value) { $q->select->fieldRaw($value); } $rows = $q->execute(); if (!empty($rows)) { foreach ($rows as $row) { $row['bou_element'] = $row['bou_uid']; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $row = PMSEEngineUtils::sanitizeFields($row); $laneData[] = $row; } } $diagramBean->fetched_row['lanes'] = $laneData; // list of participants based in the project id //$data = $participantBean->getSelectRows("", "bpmn_participant.prj_id=" . $prjID, 0, -1, -1, array(), array()); // $rows = $participantBean->get_full_list("", "prj_id='" . $prjID . "'"); // if (!empty($rows)) { // foreach ($rows as $row) { // $row = $row->fetched_row; // $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); // $row['bou_element'] = $row['bou_uid']; // $participantData[] = $row; // } // } // $diagramBean->fetched_row['participants'] = $participantData; $diagramBean->fetched_row['participants'] = array(); // data list based in the project id //$data = $retrievedDataBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array()); $rows = $retrievedDataBean->get_full_list("", "prj_id='" . $prjID . "'"); if (!empty($rows)) { foreach ($rows as $row) { $retrievedData[] = $row->fetched_row; } } $diagramBean->fetched_row['data'] = $retrievedData; // documentation list based in the project id //$data = $documentationBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array()); $rows = $documentationBean->get_full_list("", "prj_id='" . $prjID . "'"); if (!empty($rows)) { foreach ($rows as $row) { $row = $row->fetched_row; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $documentationData[] = $row; } } $diagramBean->fetched_row['documentation'] = $documentationData; // data list based in the project id //$data = $extensionBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array()); $rows = $extensionBean->get_full_list("", "prj_id='" . $prjID . "'"); if (!empty($rows)) { foreach ($rows as $row) { $row = $row->fetched_row; $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description')); $extensionData[] = $row; } } $diagramBean->fetched_row['extension'] = $extensionData; array_push($diagramData, $diagramBean->fetched_row); } return $diagramData; }
/** * {@inheritdoc} */ public function addVisibilityFromQuery(SugarQuery $sugarQuery, array $options = array()) { // We'll get it on where clause if ($this->getOption('where_condition')) { return $sugarQuery; } if ($this->useCondition()) { $table_alias = $this->getOption('table_alias'); if (empty($sugarQuery->join[$table_alias])) { return $sugarQuery; } $join = $sugarQuery->join[$table_alias]; $join->query = $sugarQuery; $add_join = ''; $this->addVisibility($add_join); if (!empty($add_join)) { $join->on()->queryAnd()->addRaw($add_join); } } else { $join = ''; $this->addVisibilityFrom($join, $options); if (!empty($join)) { $sugarQuery->joinRaw($join); } } return $sugarQuery; }
protected static function addTrackerFilter(SugarQuery $q, SugarQuery_Builder_Where $where, $interval) { global $db; $td = new SugarDateTime(); $td->modify($interval); $min_date = $td->asDb(); // Have to do a subselect because MAX() and GROUP BY don't get along with // databases other than MySQL $q->joinRaw(" INNER JOIN ( SELECT t.item_id item_id, MAX(t.date_modified) track_max " . " FROM tracker t " . " WHERE t.module_name = '" . $db->quote($q->from->module_name) . "' " . " AND t.user_id = '" . $db->quote($GLOBALS['current_user']->id) . "' " . " AND t.date_modified >= " . $db->convert("'" . $min_date . "'", 'datetime') . " " . " GROUP BY t.item_id " . " ) tracker ON tracker.item_id = " . $q->from->getTableName() . ".id ", array('alias' => 'tracker')); // Now, if they want tracker records, so let's order it by the tracker date_modified $q->order_by = array(); $q->orderByRaw('tracker.track_max', 'DESC'); $q->distinct(false); }
/** * Add Visibility to a SugarQuery Object * @param SugarQuery $sugarQuery * @param array $options * @return object|SugarQuery */ public function addVisibilityFromQuery(SugarQuery $sugarQuery, $options = array()) { $query = ''; $join = $this->addVisibilityPortal($query, 'from'); if (!empty($join)) { $sugarQuery->joinRaw($join); } return $sugarQuery; }
protected function createProcessStatusChartData($filter) { $seed_processes = BeanFactory::newBean('pmse_Project'); $qp = new SugarQuery(); $qp->from($seed_processes); $qp->select->field('id'); $qp->select->field('name'); $processes = $qp->execute(); $process_map = array(); for ($i = 0; $i < sizeof($processes); $i++) { $processes[$i]['total'] = 0; $processes[$i]['status'] = array('IN PROGRESS' => 0, 'COMPLETED' => 0, 'CANCELLED' => 0, 'ERROR' => 0, 'TERMINATED' => 0); $process_map[$processes[$i]['id']] = $i; } $seed = BeanFactory::newBean('pmse_Inbox'); // creating the sugar query object $q = new SugarQuery(); // adding the seed bean $q->from($seed); // joining the users table $q->joinRaw('INNER JOIN pmse_bpmn_process ON pmse_bpmn_process.id=pmse_inbox.pro_id'); $q->select->field("cas_status"); $q->select->fieldRaw("COUNT(*) as total"); $q->select->fieldRaw("prj_id"); $q->groupByRaw('pro_id, cas_status'); if ($filter !== 'all') { $q->where()->addRaw("pmse_project.id = '" . $filter . "'"); } $data_bean = $q->execute(); foreach ($data_bean as $row) { $index = $process_map[$row['prj_id']]; $processes[$index]['status'][$row['cas_status']] = (int) $row['total']; $processes[$index]['total'] += $row['total']; } $labels = array(); $values = array(); $in_progress = array(); $completed = array(); $cancelled = array(); $terminated = array(); $error = array(); for ($i = 0; $i < sizeof($processes); $i++) { $labels[] = array("group" => $i + 1, "l" => $processes[$i]['name']); $values[] = array("group" => $i + 1, "t" => $processes[$i]['total']); $in_progress[] = array("series" => 0, "x" => $i + 1, "y" => $processes[$i]['status']['IN PROGRESS']); $completed[] = array("series" => 1, "x" => $i + 1, "y" => $processes[$i]['status']['COMPLETED']); $cancelled[] = array("series" => 2, "x" => $i + 1, "y" => $processes[$i]['status']['CANCELLED']); $terminated[] = array("series" => 3, "x" => $i + 1, "y" => $processes[$i]['status']['TERMINATED']); $error[] = array("series" => 4, "x" => $i + 1, "y" => $processes[$i]['status']['ERROR']); } return array("properties" => array("labels" => $labels, "values" => $values), "data" => array(array("key" => translate("LBL_PMSE_IN_PROGESS_STATUS"), "type" => "bar", "color" => '#176de5', "values" => $in_progress), array("key" => translate("LBL_PMSE_COMPLETED_STATUS"), "type" => "bar", "color" => '#33800d', "values" => $completed), array("key" => translate("LBL_PMSE_CANCELLED_STATUS"), "type" => "bar", "color" => '#e5a117', "values" => $cancelled), array("key" => translate("LBL_PMSE_TERMINATED_STATUS"), "type" => "bar", "color" => '#6d17e5', "values" => $terminated), array("key" => translate("LBL_PMSE_ERROR_STATUS"), "type" => "bar", "color" => '#E61718', "values" => $error))); }
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; }