/** * Get all beans from link * @see Link2::query() */ public function query($params) { unset($params['return_as_array']); $query = $this->getQuery($params); $result = $this->db->query($query); $rows = array(); while ($row = $this->db->fetchByAssoc($result, false)) { $rows[$row['id']] = $row; } return array("rows" => $rows); }
/** * @param string $table_name * @param array $join_key_values * * @return bool */ function relationship_exists($table_name, $join_key_values) { // find the key values for the table. $dup_keys = $this->_get_alternate_key_fields($table_name); if (empty($dup_keys)) { Log::debug("No alternate key define, skipping duplicate check.."); return false; } $delimiter = ''; $this->_duplicate_where = ' WHERE '; foreach ($dup_keys as $field) { //look for key in $join_key_values, if found add to filter criteria else abort duplicate checking. if (isset($join_key_values[$field])) { $this->_duplicate_where .= $delimiter . ' ' . $field . "='" . $join_key_values[$field] . "'"; $delimiter = 'AND'; } else { Log::error('Duplicate checking aborted, Please supply a value for this column ' . $field); return false; } } //add deleted check. $this->_duplicate_where .= $delimiter . ' deleted=0'; $query = 'SELECT id FROM ' . $table_name . $this->_duplicate_where; Log::debug("relationship_exists query(" . $query . ')'); $result = $this->_db->query($query, true); $row = $this->_db->fetchByAssoc($result); if ($row == null) { return false; } else { $this->_duplicate_key = $row['id']; return true; } }
/** * Returns report schedule properties * * @param string $id Report schedule ID * * @return array */ public function getInfo($id) { $query = "SELECT report_id, next_run, time_interval\n FROM {$this->table_name}\n WHERE id = " . $this->db->quoted($id); $result = $this->db->query($query); $row = $this->db->fetchByAssoc($result); $row = $this->fromConvertReportScheduleDBRow($row); return $row; }
public function testFetchByAssoc() { $beanIds = $this->_createRecords(1); $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'"); $row = $this->_db->fetchByAssoc($result); $this->assertTrue(is_array($row)); $this->assertEquals($row['id'], $beanIds[0]); $this->_removeRecords($beanIds); }
/** * Remove old jobs that still are marked as running * @return bool true if no failed job discovered, false if some job were failed */ public function cleanup() { // fail jobs that are too old $ret = true; $date = $this->db->convert($this->db->quoted($GLOBALS['timedate']->getNow()->modify("+{$this->timeout} seconds")->asDb()), 'datetime'); $res = $this->db->query("SELECT id FROM {$this->job_queue_table} WHERE status='" . SchedulersJob::JOB_STATUS_RUNNING . "' AND date_modified <= {$date}"); while ($row = $this->db->fetchByAssoc($res)) { $this->resolveJob($row["id"], SchedulersJob::JOB_FAILURE, translate('ERR_TIMEOUT', 'SchedulersJobs')); $ret = false; } // TODO: soft-delete old done jobs? return $ret; }
function retrieve_relationships($table, $values, $select_id) { $query = "SELECT {$select_id} FROM {$table} WHERE deleted = 0 "; foreach ($values as $name => $value) { $query .= " AND {$name} = '{$value}' "; } $query .= " ORDER BY {$select_id} "; $result = $this->db->query($query, false, "Retrieving Relationship:" . $query); $ids = array(); while ($row = $this->db->fetchByAssoc($result)) { $ids[] = $row; } return $ids; }
/** * Get one value result from the query * @return false|string */ public function getOne() { if (empty($this->limit)) { $this->offset(0)->limit(1); } $result = $this->runQuery(); if (empty($result)) { return false; } $row = $this->db->fetchByAssoc($result); if (!empty($row)) { return array_shift($row); } return false; }
/** * DEPRECATED. */ public function retrieve() { if (!isset($this->bean)) { $GLOBALS['log']->fatal('DynamicField retrieve, bean not instantiated'); return false; } if (!$this->bean->hasCustomFields()) { return false; } $query = 'SELECT * FROM ' . $this->bean->table_name . "_cstm WHERE id_c='" . $this->bean->id . "'"; $result = $this->db->query($query); $row = $this->db->fetchByAssoc($result); if ($row) { foreach ($row as $name => $value) { // originally in pre-r30895 we checked if this field was in avail_fields i.e., in fields_meta_data and not deleted // with the removal of avail_fields post-r30895 we have simplified this - we now retrieve every custom field even if previously deleted // this is considered harmless as the value although set in the bean will not otherwise be used (nothing else works off the list of fields in the bean) $this->bean->{$name} = $value; } } return true; }
/** * @return mixed */ public function fetch() { return $this->_db->fetchByAssoc($this->result, false); }
/** * Return the module name on the other side of the relationship * @param string $lhs_module Left side module * @param string $rhs_module Right side module * @param DBManager $db * @return string|null */ public function retrieve_by_sides($lhs_module, $rhs_module, $db) { //give it the relationship_name and base module //it will return the module name on the other side of the relationship $query = "SELECT * FROM relationships WHERE deleted=0 AND lhs_module = '" . $lhs_module . "' AND rhs_module = '" . $rhs_module . "'"; $result = $db->query($query, true, " Error searching relationships table."); $row = $db->fetchByAssoc($result); if ($row != null) { return $this->convertRow($row); } return null; }
/** * @param string $lhs_module * @param string $rhs_module * @param DBManager $db * @param string $type * * @return array|null */ function retrieve_by_modules($lhs_module, $rhs_module, &$db, $type = '') { //give it the relationship_name and base module //it will return the module name on the other side of the relationship $query = "\tSELECT * FROM relationships\n\t\t\t\t\tWHERE deleted=0\n\t\t\t\t\tAND (\n\t\t\t\t\t(lhs_module = '" . $lhs_module . "' AND rhs_module = '" . $rhs_module . "')\n\t\t\t\t\tOR\n\t\t\t\t\t(lhs_module = '" . $rhs_module . "' AND rhs_module = '" . $lhs_module . "')\n\t\t\t\t\t)\n\t\t\t\t\t"; if (!empty($type)) { $query .= " AND relationship_type='{$type}'"; } $result = $db->query($query, true, " Error searching relationships table.."); $row = $db->fetchByAssoc($result); if ($row != null) { return $row['relationship_name']; } return null; }
function get_next_row($result_field_name = 'result', $column_field_name = 'display_columns', $skip_non_summary_columns = false, $exporting = false) { global $current_user; $chart_cells = array(); if ($this->do_export) { $db_row = $this->db->fetchByAssoc($this->{$result_field_name}, false); } else { $db_row = $this->db->fetchByAssoc($this->{$result_field_name}); } if ($db_row == 0 || sizeof($db_row) == 0) { return 0; } // Call custom hooks if (isset($this->full_bean_list) && is_array($this->full_bean_list) && array_key_exists('self', $this->full_bean_list) && is_object($this->full_bean_list['self']) && method_exists($this->full_bean_list['self'], 'call_custom_logic')) { $this->full_bean_list['self']->call_custom_logic('process_report_row', array('row' => &$db_row, 'reporter' => $this)); } if ($result_field_name == 'summary_result') { if (!empty($this->child_filter) && !empty($db_row[$this->child_filter_name])) { $this->child_filter_by = $db_row[$this->child_filter_name]; } else { $this->child_filter = ''; $this->child_filter_by = ''; $this->child_filter_name = ''; } } $row = array(); $cells = array(); $fields = array(); foreach ($db_row as $key => $value) { //if value is null or not set, then change to empty string. This prevents array index errors while processing $fields[strtoupper($key)] = is_null($value) ? '' : $value; } // here we want to make copies, so use foreach foreach ($this->report_def[$column_field_name] as $display_column) { $display_column['table_alias'] = $this->getTableFromField($display_column); $this->register_field_for_query($display_column); if ($skip_non_summary_columns && empty($display_column['group_function'])) { if ($exporting || $this->plain_text_output) { array_push($cells, ' '); } else { array_push($cells, ' '); } continue; } $display_column['fields'] = $fields; if ($this->plain_text_output == true) { /*nsingh: bug 13554- date and time fields must be displayed using user's locale settings. * Since to_pdf uses plain_text_output=true, we handle the date and time case here by using the 'List' context of the layout_manager */ if ($display_column['type'] == 'date' || $display_column['type'] == 'time' || $display_column['type'] == 'datetimecombo') { $this->layout_manager->setAttribute('context', 'List'); } else { $this->layout_manager->setAttribute('context', 'ListPlain'); } } else { $this->layout_manager->setAttribute('context', 'List'); } // Make sure 'AVG' aggregate is shown as float, regardless of the original field type if (!empty($display_column['group_function']) && strtolower($display_column['group_function']) === 'avg' && $display_column['type'] != 'currency') { $display_column['type'] = 'float'; } if ($display_column['type'] != 'currency' || substr_count($display_column['name'], '_usdoll') == 0 && (isset($display_column['group_function']) ? $display_column['group_function'] != 'weighted_amount' && $display_column['group_function'] != 'weighted_sum' : true)) { $pos = $display_column['table_key']; $module_name = ''; if ($pos) { $module_name = substr($pos, strrpos($pos, ':') + 1); } $field_name = $this->getColumnFieldName($display_column); if ($module_name == 'currencies' && empty($display_column['fields'][$field_name])) { switch ($display_column['name']) { case 'iso4217': $display = $this->currency_obj->getDefaultISO4217(); break; case 'symbol': $display = $this->currency_obj->getDefaultCurrencySymbol(); break; case 'name': $display = $this->currency_obj->getDefaultCurrencyName(); break; default: $display = $this->layout_manager->widgetDisplay($display_column); } $display_column['fields'][$field_name] = $display; } else { if (!empty($field_name) && isset($display_column['fields'][$field_name])) { $display_column['fields'][$field_name] = $this->db->fromConvert($display_column['fields'][$field_name], $display_column['type']); } $display = $this->layout_manager->widgetDisplay($display_column); } } else { if (isset($display_column['group_function'])) { $field_name = $this->getTruncatedColumnAlias(strtoupper($display_column['table_alias']) . "_" . strtoupper($display_column['group_function']) . "_" . strtoupper($display_column['name'])); } else { unset($field_name); } if (!isset($field_name) || !isset($display_column['fields'][$field_name])) { $field_name = $this->getTruncatedColumnAlias(strtoupper($display_column['table_alias']) . "_" . strtoupper($display_column['name'])); } if (isset($display_column['fields'][$field_name])) { $display = $display_column['fields'][$field_name]; } } if ($display_column['type'] == 'currency' && (strpos($display_column['name'], '_usdoll') !== false || !empty($display_column['group_function']))) { // convert base to user preferred if set in user prefs if ($current_user->getPreference('currency_show_preferred')) { $userCurrency = SugarCurrency::getUserLocaleCurrency(); $raw_display = SugarCurrency::convertWithRate($display_column['fields'][$field_name], 1.0, $userCurrency->conversion_rate); $display = SugarCurrency::formatAmountUserLocale($raw_display, $userCurrency->id); } else { $raw_display = $display_column['fields'][$field_name]; $display = SugarCurrency::formatAmountUserLocale($raw_display, SugarCurrency::getBaseCurrency()->id); } } else { $raw_display = $display; } if (isset($display_column['type']) && $display_column['type'] == 'float') { $display = $this->layout_manager->widgetDisplay($display_column); } if (isset($display_column['type'])) { $alias = $this->alias_lookup[$display_column['table_key']]; $array_key = strtoupper($alias . '__count'); if (array_key_exists($array_key, $display_column['fields'])) { $displayData = $display_column['fields'][$array_key]; if (empty($displayData) && $display_column['type'] != 'bool' && ($display_column['type'] != 'enum' || $display_column['type'] == 'enum' && $displayData != '0')) { $display = ""; } } // if $module_bean = BeanFactory::getBean($this->module); if (is_array($module_bean->field_defs)) { if (isset($module_bean->field_defs[$display_column['type']])) { if (isset($module_bean->field_defs[$display_column['type']]['options'])) { $trans_options = translate($module_bean->field_defs[$display_column['type']]['options']); if (isset($trans_options[$display_column['fields'][$field_name]])) { $display = $trans_options[$display_column['fields'][$field_name]]; } } } } } // if // for charts if ($column_field_name == 'summary_columns' && $this->do_chart) { //_pp($display); $raw_value = ""; $keys = array_keys($fields); foreach ($this->report_def['summary_columns'] as $index => $column) { if ($column['name'] == $display_column['name'] && isset($keys[$index]) && isset($fields[$keys[$index]])) { $raw_value = $fields[$keys[$index]]; break; } } $cell_arr = array('val' => $raw_display, 'key' => $display_column['column_key'], 'raw_value' => $raw_value); //_pp($cell_arr); array_push($chart_cells, $cell_arr); } if ($exporting) { global $app_list_strings; // parse out checkboxes // TODO: wp this should be done in the widget if (preg_match('/type.*=.*checkbox/Uis', $display)) { if (preg_match('/checked/i', $display)) { $display = $app_list_strings['dom_switch_bool']['on']; } else { $display = $app_list_strings['dom_switch_bool']['off']; } } } array_push($cells, $display); } // END foreach $row['cells'] = $cells; // calculate summary rows count as the product of all count fields in summary $count = 1; $count_exists = false; foreach ($db_row as $count_column => $count_value) { if (substr($count_column, -10) == "__allcount" || $count_column == 'count') { $count *= max($count_value, 1); $count_exists = true; } } if ($count_exists) { $row['count'] = $count; } // for charts if ($column_field_name == 'summary_columns' && $this->do_chart) { $chart_row = 0; if (!empty($db_row['count'])) { $chart_row = array('cells' => $chart_cells, 'count' => $db_row['count']); } else { $chart_row = array('cells' => $chart_cells); } array_push($this->chart_rows, $chart_row); } if ($column_field_name == 'summary_columns' && isset($this->chart_group_position) && isset($this->group_header)) { $row['group_pos'] = $this->chart_group_position; $row['group_header'] = $this->group_header; $row['group_column_is_invisible'] = $this->group_column_is_invisible; } return $row; }