/** * Test to see if the all the Jobs in the group are done. * * @return bool * @throws SugarQueryException */ protected function isJobGroupDone() { // when we don't have a job_group, return true if (empty($this->job->job_group)) { return true; } $sq = new SugarQuery(); $sq->select(array('job_group'))->fieldRaw('count(0)', 'total_jobs')->fieldRaw('sum(case when status = \'done\' AND resolution = \'success\' then 1 else 0 END)', 'total_done'); $sq->from(BeanFactory::getBean('SchedulersJobs')); $sq->where()->equals('job_group', $this->job->job_group); $sq->groupBy('job_group'); $results = $sq->execute(); $result = array_shift($results); return $result['total_jobs'] === $result['total_done']; }
/** * Create a select statement * * @param SugarQuery_Builder_Select $selectObj * * @return string */ protected function compileSelect(SugarQuery_Builder_Select $selectObj) { $return = array(); $addedFields = array(); if ($selectObj->getCountQuery() === true) { $return['count(0) AS record_count'] = 'count(0) AS record_count'; } foreach ($selectObj->select as $field) { if ($field->isNonDb()) { continue; } $compiledField = $this->compileField($field); $return[$compiledField] = $compiledField; if ($selectObj->getCountQuery() === true) { $this->sugar_query->groupBy("{$field->table}.{$field->field}"); } } return implode(", ", $return); }
/** * Fix the Opportunity Data to have the correct data once we go back from having RLI's to only have Opps * * - Takes the lowest sales_stage from all the RLIs * - Takes the lowest date_closed from all the RLIs * - Sets commit_stage to empty * - Sets sales_status to empty * * This is all done via a Query since we delete all the RLI's and we didn't want to keep any of them around. * * @throws SugarQueryException */ protected function setOpportunityDataFromRevenueLineItems() { // need to figure out the best way to roll this up before truncating the table. $app_list_strings = return_app_list_strings_language($GLOBALS['current_language']); // get the sales_stage from the RLI module /* @var $rli RevenueLineItem */ $rli = BeanFactory::getBean('RevenueLineItems'); $def = $rli->getFieldDefinition('sales_stage'); $db = DBManagerFactory::getInstance(); $list_value = array(); // get the `options` param so we make sure if they customized it to use their custom version $sqlCase = ''; $list = $def['options']; if (!empty($list) && isset($app_list_strings[$list])) { $i = 0; $order_by_arr = array(); foreach ($app_list_strings[$list] as $key => $value) { $list_value[$i] = $key; if ($key == '') { $order_by_arr[] = "WHEN (sales_stage='' OR sales_stage IS NULL) THEN " . $i++; } else { $order_by_arr[] = "WHEN sales_stage=" . $db->quoted($key) . " THEN " . $i++; } } $sqlCase = "min(CASE " . implode("\n", $order_by_arr) . " ELSE {$i} END)"; } $fcsettings = Forecast::getSettings(); $stage_cases = array(); $closed_stages = array_merge($fcsettings['sales_stage_won'], $fcsettings['sales_stage_lost']); foreach ($closed_stages as $stage) { $stage_cases[] = $db->quoted($stage); } $stage_cases = implode(',', $stage_cases); $sq = new SugarQuery(); $sq->select(array('opportunity_id'))->fieldRaw($sqlCase, 'sales_stage')->fieldRaw($this->dateClosedMigration . '(CASE when sales_stage IN (' . $stage_cases . ') THEN date_closed END)', 'dc_closed')->fieldRaw($this->dateClosedMigration . '(CASE when sales_stage NOT IN (' . $stage_cases . ') THEN date_closed END)', 'dc_open')->fieldRaw($this->dateClosedMigration . '(CASE when sales_stage IN (' . $stage_cases . ') THEN date_closed_timestamp END)', 'dct_closed')->fieldRaw($this->dateClosedMigration . '(CASE when sales_stage NOT IN (' . $stage_cases . ') THEN date_closed_timestamp END)', 'dct_open'); $sq->from($rli); $sq->groupBy('opportunity_id'); $results = $sq->execute(); foreach ($results as $result) { $sql = 'UPDATE opportunities SET date_closed = ' . $db->quoted(!empty($result['dc_open']) ? $result['dc_open'] : $result['dc_closed']) . ', date_closed_timestamp = ' . $db->quoted(!empty($result['dct_open']) ? $result['dct_open'] : $result['dct_closed']) . ', sales_stage = ' . $db->quoted($list_value[$result['sales_stage']]) . ', probability = ' . $db->quoted($app_list_strings['sales_probability_dom'][$list_value[$result['sales_stage']]]) . ', sales_status = ' . $db->quoted('') . ', commit_stage = ' . $db->quoted('') . ' WHERE id = ' . $db->quoted($result['opportunity_id']); $db->query($sql); } if ($this->isForecastSetup()) { SugarAutoLoader::load('include/SugarQueue/jobs/SugarJobUpdateOpportunities.php'); SugarJobUpdateOpportunities::updateOpportunitiesForForecasting(); } }
/** * 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; }