/**
* Returns list of items in the specified table related to the currently loaded row or rows specified in options.
*
* @param $pm_rel_table_name_or_num - the table name or table number of the item type you want to get a list of (eg. if you are calling this on an ca_objects instance passing 'ca_entities' here will get you a list of entities related to the object)
* @param $pa_options - array of options. Supported options are:
*
* [Options controlling rows for which data is returned]
* row_ids = Array of primary key values to use when fetching related items. If omitted or set to a null value the 'row_id' option will be used. [Default is null]
* row_id = Primary key value to use when fetching related items. If omitted or set to a false value (null, false, 0) then the primary key value of the currently loaded row is used. [Default is currently loaded row]
* start = Zero-based index to begin return set at. [Default is 0]
* limit = Maximum number of related items to return. [Default is 1000]
* showDeleted = Return related items that have been deleted. [Default is false]
* primaryIDs = array of primary keys in related table to exclude from returned list of items. Array is keyed on table name for compatibility with the parameter as used in the caProcessTemplateForIDs() helper [Default is null - nothing is excluded].
* restrictToBundleValues = Restrict returned items to those with specified bundle values. Specify an associative array with keys set to bundle names and key values set to arrays of values to filter on (eg. [bundle_name1 => [value1, value2, ...]]). [Default is null]
* where = Restrict returned items to specified field values. The fields must be intrinsic and in the related table. This option can be useful when you want to efficiently fetch specific rows from a related table. Note that multiple fields/values are logically AND'ed together – all must match for a row to be returned - and that only equivalence is supported. [Default is null]
* criteria = Restrict returned items using SQL criteria appended directly onto the query. Criteria is used as-is and must be compatible with the generated SQL query. [Default is null]
*
* [Options controlling scope of data in return value]
* restrictToTypes = Restrict returned items to those of the specified types. An array of list item idnos and/or item_ids may be specified. [Default is null]
* restrictToRelationshipTypes = Restrict returned items to those related using the specified relationship types. An array of relationship type idnos and/or type_ids may be specified. [Default is null]
* excludeTypes = Restrict returned items to those *not* of the specified types. An array of list item idnos and/or item_ids may be specified. [Default is null]
* excludeRelationshipTypes = Restrict returned items to those *not* related using the specified relationship types. An array of relationship type idnos and/or type_ids may be specified. [Default is null]
* restrictToType = Synonym for restrictToTypes. [Default is null]
* restrictToRelationshipType = Synonym for restrictToRelationshipTypes. [Default is null]
* excludeType = Synonym for excludeTypes. [Default is null]
* excludeRelationshipType = Synonym for excludeRelationshipTypes. [Default is null]
* restrictToLists = Restrict returned items to those that are in one or more specified lists. This option is only relevant when fetching related ca_list_items. An array of list list_codes or list_ids may be specified. [Default is null]
* fields = array of fields (in table.fieldname format) to include in returned data. [Default is null]
* returnNonPreferredLabels = Return non-preferred labels in returned data. [Default is false]
* returnLabelsAsArray = Return all labels associated with row in an array, rather than as a text value in the current locale. [Default is false]
* dontReturnLabels = Don't include labels in returned data. [Default is false]
* idsOnly = Return one-dimensional array of related primary key values only. [Default is false]
*
* [Options controlling format of data in return value]
* useLocaleCodes = Return locale values as codes (Ex. en_US) rather than numeric database-specific locale_ids. [Default is false]
* sort = Array list of bundles to sort returned values on. The sortable bundle specifiers are fields with or without tablename. Only those fields returned for the related tables (intrinsics, attributes and label fields) are sortable. [Default is null]
* sortDirection = Direction of sort. Use "asc" (ascending) or "desc" (descending). [Default is asc]
* groupFields = Groups together fields in an arrangement that is easier for import to another system. Used by the ItemInfo web service when in "import" mode. [Default is false]
*
* [Front-end access control]
* checkAccess = Array of access values to filter returned values on. Available for any related table with an "access" field (ca_objects, ca_entities, etc.). If omitted no filtering is performed. [Default is null]
* user_id = Perform item level access control relative to specified user_id rather than currently logged in user. [Default is user_id for currently logged in user]
*
* @return array List of related items
*/
public function getRelatedItems($pm_rel_table_name_or_num, $pa_options = null)
{
global $AUTH_CURRENT_USER_ID;
$vn_user_id = isset($pa_options['user_id']) && $pa_options['user_id'] ? $pa_options['user_id'] : (int) $AUTH_CURRENT_USER_ID;
$vb_show_if_no_acl = (bool) ($this->getAppConfig()->get('default_item_access_level') > __CA_ACL_NO_ACCESS__);
// convert options
if (($pa_options['restrictToTypes'] = caGetOption(array('restrictToTypes', 'restrict_to_types', 'restrictToType', 'restrict_to_type'), $pa_options, null)) && !is_array($pa_options['restrictToTypes'])) {
$pa_options['restrictToTypes'] = array($pa_options['restrictToTypes']);
}
if (($pa_options['restrictToRelationshipTypes'] = caGetOption(array('restrictToRelationshipTypes', 'restrict_to_relationship_types', 'restrictToRelationshipType', 'restrict_to_relationship_type'), $pa_options, null)) && !is_array($pa_options['restrictToRelationshipTypes'])) {
$pa_options['restrictToRelationshipTypes'] = array($pa_options['restrictToRelationshipTypes']);
}
if (($pa_options['excludeTypes'] = caGetOption(array('excludeTypes', 'exclude_types', 'excludeType', 'exclude_type'), $pa_options, null)) && !is_array($pa_options['excludeTypes'])) {
$pa_options['excludeTypes'] = array($pa_options['excludeTypes']);
}
if (($pa_options['excludeRelationshipTypes'] = caGetOption(array('excludeRelationshipTypes', 'exclude_relationship_types', 'excludeRelationshipType', 'exclude_relationship_type'), $pa_options, null)) && !is_array($pa_options['excludeRelationshipTypes'])) {
$pa_options['excludeRelationshipTypes'] = array($pa_options['excludeRelationshipTypes']);
}
if (!isset($pa_options['dontIncludeSubtypesInTypeRestriction']) && (isset($pa_options['dont_include_subtypes_in_type_restriction']) && $pa_options['dont_include_subtypes_in_type_restriction'])) {
$pa_options['dontIncludeSubtypesInTypeRestriction'] = $pa_options['dont_include_subtypes_in_type_restriction'];
}
if (!isset($pa_options['returnNonPreferredLabels']) && (isset($pa_options['restrict_to_type']) && $pa_options['restrict_to_type'])) {
$pa_options['returnNonPreferredLabels'] = $pa_options['restrict_to_type'];
}
if (!isset($pa_options['returnLabelsAsArray']) && (isset($pa_options['return_labels_as_array']) && $pa_options['return_labels_as_array'])) {
$pa_options['returnLabelsAsArray'] = $pa_options['return_labels_as_array'];
}
if (!isset($pa_options['restrictToLists']) && (isset($pa_options['restrict_to_lists']) && $pa_options['restrict_to_lists'])) {
$pa_options['restrictToLists'] = $pa_options['restrict_to_lists'];
}
$vb_group_fields = isset($pa_options['groupFields']) ? $pa_options['groupFields'] : false;
$va_primary_ids = isset($pa_options['primaryIDs']) && is_array($pa_options['primaryIDs']) ? $pa_options['primaryIDs'] : null;
$vb_show_current_only = isset($pa_options['showCurrentOnly']) ? $pa_options['showCurrentOnly'] : false;
if (!isset($pa_options['useLocaleCodes']) && (isset($pa_options['returnLocaleCodes']) && $pa_options['returnLocaleCodes'])) {
$pa_options['useLocaleCodes'] = $pa_options['returnLocaleCodes'];
}
$vb_use_locale_codes = isset($pa_options['useLocaleCodes']) ? $pa_options['useLocaleCodes'] : false;
$va_get_where = isset($pa_options['where']) && is_array($pa_options['where']) && sizeof($pa_options['where']) ? $pa_options['where'] : null;
$va_row_ids = isset($pa_options['row_ids']) && is_array($pa_options['row_ids']) ? $pa_options['row_ids'] : null;
$vn_row_id = isset($pa_options['row_id']) && $pa_options['row_id'] ? $pa_options['row_id'] : $this->getPrimaryKey();
$o_db = $this->getDb();
$t_locale = $this->getLocaleInstance();
$o_tep = $this->getTimeExpressionParser();
$vb_uses_effective_dates = false;
if (isset($pa_options['sort']) && !is_array($pa_options['sort'])) {
$pa_options['sort'] = array($pa_options['sort']);
}
$va_sort_fields = isset($pa_options['sort']) && is_array($pa_options['sort']) ? $pa_options['sort'] : null;
$vs_sort_direction = isset($pa_options['sortDirection']) && $pa_options['sortDirection'] ? $pa_options['sortDirection'] : null;
if (!$va_row_ids && $vn_row_id > 0) {
$va_row_ids = array($vn_row_id);
}
if (!$va_row_ids || !is_array($va_row_ids) || !sizeof($va_row_ids)) {
return array();
}
$vb_return_labels_as_array = isset($pa_options['returnLabelsAsArray']) && $pa_options['returnLabelsAsArray'] ? true : false;
$vn_limit = isset($pa_options['limit']) && (int) $pa_options['limit'] > 0 ? (int) $pa_options['limit'] : 1000;
$vn_start = isset($pa_options['start']) && (int) $pa_options['start'] > 0 ? (int) $pa_options['start'] : 0;
if (is_numeric($pm_rel_table_name_or_num)) {
if (!($vs_related_table_name = $this->getAppDatamodel()->getTableName($pm_rel_table_name_or_num))) {
return null;
}
} else {
if (sizeof($va_tmp = explode(".", $pm_rel_table_name_or_num)) > 1) {
$pm_rel_table_name_or_num = array_shift($va_tmp);
}
if (!($o_instance = $this->getAppDatamodel()->getInstanceByTableName($pm_rel_table_name_or_num, true))) {
return null;
}
$vs_related_table_name = $pm_rel_table_name_or_num;
}
if (!is_array($pa_options)) {
$pa_options = array();
}
$vb_is_combo_key_relation = false;
// indicates relation is via table_num/row_id combination key
switch (sizeof($va_path = array_keys($this->getAppDatamodel()->getPath($this->tableName(), $vs_related_table_name)))) {
case 3:
$t_item_rel = $this->getAppDatamodel()->getTableInstance($va_path[1]);
$t_rel_item = $this->getAppDatamodel()->getTableInstance($va_path[2]);
$vs_key = $t_item_rel->primaryKey();
//'relation_id';
break;
case 2:
$t_item_rel = $this->isRelationship() ? $this : null;
$t_rel_item = $this->getAppDatamodel()->getTableInstance($va_path[1]);
$vs_key = $t_rel_item->primaryKey();
break;
default:
// is this related with row_id/table_num combo?
if (($t_rel_item = $this->getAppDatamodel()->getTableInstance($vs_related_table_name)) && $t_rel_item->hasField('table_num') && $t_rel_item->hasField('row_id')) {
$vs_key = $t_rel_item->primaryKey();
$vb_is_combo_key_relation = true;
$va_path = array($this->tableName(), $t_rel_item->tableName());
} else {
// bad related table
return null;
}
break;
}
// check for self relationship
$vb_self_relationship = false;
if ($this->tableName() == $vs_related_table_name) {
$vb_self_relationship = true;
$t_rel_item = $this->getAppDatamodel()->getTableInstance($va_path[0]);
$t_item_rel = $this->getAppDatamodel()->getTableInstance($va_path[1]);
}
$va_wheres = array();
$va_selects = array();
$va_joins_post_add = array();
$vs_related_table = $t_rel_item->tableName();
if ($t_rel_item->hasField('type_id')) {
$va_selects[] = "{$vs_related_table}.type_id item_type_id";
}
if ($t_rel_item->hasField('source_id')) {
$va_selects[] = "{$vs_related_table}.source_id item_source_id";
}
// TODO: get these field names from models
if (($t_tmp = $t_item_rel) || $t_rel_item->isRelationship() && ($t_tmp = $t_rel_item)) {
//define table names
$vs_linking_table = $t_tmp->tableName();
$va_selects[] = "{$vs_related_table}." . $t_rel_item->primaryKey();
// include dates in returned data
if ($t_tmp->hasField('effective_date')) {
$va_selects[] = $vs_linking_table . '.sdatetime';
$va_selects[] = $vs_linking_table . '.edatetime';
$vb_uses_effective_dates = true;
}
if ($t_rel_item->hasField('is_enabled')) {
$va_selects[] = "{$vs_related_table}.is_enabled";
}
if ($t_tmp->hasField('type_id')) {
$va_selects[] = $vs_linking_table . '.type_id relationship_type_id';
require_once __CA_MODELS_DIR__ . '/ca_relationship_types.php';
$t_rel = new ca_relationship_types();
$vb_uses_relationship_types = true;
}
// limit related items to a specific type
if ($vb_uses_relationship_types && isset($pa_options['restrictToRelationshipTypes']) && $pa_options['restrictToRelationshipTypes']) {
if (!is_array($pa_options['restrictToRelationshipTypes'])) {
$pa_options['restrictToRelationshipTypes'] = array($pa_options['restrictToRelationshipTypes']);
}
if (sizeof($pa_options['restrictToRelationshipTypes'])) {
$va_rel_types = array();
foreach ($pa_options['restrictToRelationshipTypes'] as $vm_type) {
if (!$vm_type) {
continue;
}
if (!($vn_type_id = $t_rel->getRelationshipTypeID($vs_linking_table, $vm_type))) {
$vn_type_id = (int) $vm_type;
}
if ($vn_type_id > 0) {
$va_rel_types[] = $vn_type_id;
if (is_array($va_children = $t_rel->getHierarchyChildren($vn_type_id, array('idsOnly' => true)))) {
$va_rel_types = array_merge($va_rel_types, $va_children);
}
}
}
if (sizeof($va_rel_types)) {
$va_wheres[] = '(' . $vs_linking_table . '.type_id IN (' . join(',', $va_rel_types) . '))';
}
}
}
if ($vb_uses_relationship_types && isset($pa_options['excludeRelationshipTypes']) && $pa_options['excludeRelationshipTypes']) {
if (!is_array($pa_options['excludeRelationshipTypes'])) {
$pa_options['excludeRelationshipTypes'] = array($pa_options['excludeRelationshipTypes']);
}
if (sizeof($pa_options['excludeRelationshipTypes'])) {
$va_rel_types = array();
foreach ($pa_options['excludeRelationshipTypes'] as $vm_type) {
if ($vn_type_id = $t_rel->getRelationshipTypeID($vs_linking_table, $vm_type)) {
$va_rel_types[] = $vn_type_id;
if (is_array($va_children = $t_rel->getHierarchyChildren($vn_type_id, array('idsOnly' => true)))) {
$va_rel_types = array_merge($va_rel_types, $va_children);
}
}
}
if (sizeof($va_rel_types)) {
$va_wheres[] = '(' . $vs_linking_table . '.type_id NOT IN (' . join(',', $va_rel_types) . '))';
}
}
}
}
// limit related items to a specific type
$va_type_ids = caMergeTypeRestrictionLists($t_rel_item, $pa_options);
if (is_array($va_type_ids) && sizeof($va_type_ids) > 0) {
$va_wheres[] = "({$vs_related_table}.type_id IN (" . join(',', $va_type_ids) . ')' . ($t_rel_item->getFieldInfo('type_id', 'IS_NULL') ? " OR ({$vs_related_table}.type_id IS NULL)" : '') . ')';
}
$va_source_ids = caMergeSourceRestrictionLists($t_rel_item, $pa_options);
if (method_exists($t_rel_item, "getSourceFieldName") && ($vs_source_id_fld = $t_rel_item->getSourceFieldName()) && is_array($va_source_ids) && sizeof($va_source_ids) > 0) {
$va_wheres[] = "({$vs_related_table}.{$vs_source_id_fld} IN (" . join(',', $va_source_ids) . "))";
}
if (isset($pa_options['excludeType']) && $pa_options['excludeType']) {
if (!isset($pa_options['excludeTypes']) || !is_array($pa_options['excludeTypes'])) {
$pa_options['excludeTypes'] = array();
}
$pa_options['excludeTypes'][] = $pa_options['excludeType'];
}
if (isset($pa_options['excludeTypes']) && is_array($pa_options['excludeTypes'])) {
$va_type_ids = caMakeTypeIDList($vs_related_table, $pa_options['excludeTypes']);
if (is_array($va_type_ids) && sizeof($va_type_ids) > 0) {
$va_wheres[] = "({$vs_related_table}.type_id NOT IN (" . join(',', $va_type_ids) . "))";
}
}
if ($this->getAppConfig()->get('perform_item_level_access_checking')) {
$t_user = new ca_users($vn_user_id, true);
if (is_array($va_groups = $t_user->getUserGroups()) && sizeof($va_groups)) {
$va_group_ids = array_keys($va_groups);
} else {
$va_group_ids = array();
}
// Join to limit what browse table items are used to generate facet
$va_joins_post_add[] = 'LEFT JOIN ca_acl ON ' . $vs_related_table_name . '.' . $t_rel_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_rel_item->tableNum() . "\n";
$va_wheres[] = "(\n\t\t\t\t((\n\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\tOR\n\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t)";
}
if (is_array($va_get_where)) {
foreach ($va_get_where as $vs_fld => $vm_val) {
if ($t_rel_item->hasField($vs_fld)) {
$va_wheres[] = "({$vs_related_table_name}.{$vs_fld} = " . (!is_numeric($vm_val) ? "'" . $this->getDb()->escape($vm_val) . "'" : $vm_val) . ")";
}
}
}
if ($vs_idno_fld = $t_rel_item->getProperty('ID_NUMBERING_ID_FIELD')) {
$va_selects[] = "{$vs_related_table}.{$vs_idno_fld}";
}
if ($vs_idno_sort_fld = $t_rel_item->getProperty('ID_NUMBERING_SORT_FIELD')) {
$va_selects[] = "{$vs_related_table}.{$vs_idno_sort_fld}";
}
$va_selects[] = $va_path[1] . '.' . $vs_key;
if (isset($pa_options['fields']) && is_array($pa_options['fields'])) {
$va_selects = array_merge($va_selects, $pa_options['fields']);
}
// if related item is labelable then include the label table in the query as well
$vs_label_display_field = null;
if (method_exists($t_rel_item, "getLabelTableName") && (!isset($pa_options['dontReturnLabels']) || !$pa_options['dontReturnLabels'])) {
if ($vs_label_table_name = $t_rel_item->getLabelTableName()) {
// make sure it actually has a label table...
$va_path[] = $vs_label_table_name;
$t_rel_item_label = $this->getAppDatamodel()->getTableInstance($vs_label_table_name);
$vs_label_display_field = $t_rel_item_label->getDisplayField();
if ($vb_return_labels_as_array || is_array($va_sort_fields) && sizeof($va_sort_fields)) {
$va_selects[] = $vs_label_table_name . '.*';
} else {
$va_selects[] = $vs_label_table_name . '.' . $vs_label_display_field;
$va_selects[] = $vs_label_table_name . '.locale_id';
if ($t_rel_item_label->hasField('surname')) {
// hack to include fields we need to sort entity labels properly
$va_selects[] = $vs_label_table_name . '.surname';
$va_selects[] = $vs_label_table_name . '.forename';
}
}
if ($t_rel_item_label->hasField('is_preferred') && (!isset($pa_options['returnNonPreferredLabels']) || !$pa_options['returnNonPreferredLabels'])) {
$va_wheres[] = "(" . $vs_label_table_name . '.is_preferred = 1)';
}
}
}
if ($vb_show_current_only && $t_item_rel && $t_item_rel->hasField('source_info') && $t_item_rel->tableName() == 'ca_movements_x_objects') {
// TODO: table check is temporary hack while we get "current" support into non-movement relationships
$va_wheres[] = '(' . $t_item_rel->tableName() . '.source_info = \'current\')';
}
// return source info in returned data
if ($t_item_rel && $t_item_rel->hasField('source_info')) {
$va_selects[] = $vs_linking_table . '.source_info';
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) {
$va_wheres[] = "({$vs_related_table}.access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_rel_item->hasField('deleted')) {
$va_wheres[] = "({$vs_related_table}.deleted = 0)";
}
if (($va_criteria = isset($pa_options['criteria']) ? $pa_options['criteria'] : null) && is_array($va_criteria) && sizeof($va_criteria)) {
$va_wheres[] = "(" . join(" AND ", $va_criteria) . ")";
}
if ($vb_self_relationship) {
//
// START - traverse self relation
//
$va_rel_info = $this->getAppDatamodel()->getRelationships($va_path[0], $va_path[1]);
if ($vs_label_table_name) {
$va_label_rel_info = $this->getAppDatamodel()->getRelationships($va_path[0], $vs_label_table_name);
}
$va_rels = array();
$vn_i = 0;
foreach ($va_rel_info[$va_path[0]][$va_path[1]] as $va_possible_keys) {
$va_joins = array();
$va_joins[] = "INNER JOIN " . $va_path[1] . " ON " . $va_path[1] . '.' . $va_possible_keys[1] . ' = ' . $va_path[0] . '.' . $va_possible_keys[0] . "\n";
if ($vs_label_table_name) {
$va_joins[] = "INNER JOIN " . $vs_label_table_name . " ON " . $vs_label_table_name . '.' . $va_label_rel_info[$va_path[0]][$vs_label_table_name][0][1] . ' = ' . $va_path[0] . '.' . $va_label_rel_info[$va_path[0]][$vs_label_table_name][0][0] . "\n";
}
$vs_other_field = $vn_i == 0 ? $va_rel_info[$va_path[0]][$va_path[1]][1][1] : $va_rel_info[$va_path[0]][$va_path[1]][0][1];
$vs_direction = preg_match('!left!', $vs_other_field) ? 'ltor' : 'rtol';
$va_selects['row_id'] = $va_path[1] . '.' . $vs_other_field . ' AS row_id';
$vs_order_by = '';
$vs_sort_fld = '';
if ($t_item_rel && $t_item_rel->hasField('rank')) {
$vs_order_by = ' ORDER BY ' . $t_item_rel->tableName() . '.rank';
$vs_sort_fld = 'rank';
$va_selects[] = $t_item_rel->tableName() . ".rank";
} else {
if ($t_rel_item && ($vs_sort = $t_rel_item->getProperty('ID_NUMBERING_SORT_FIELD'))) {
$vs_order_by = " ORDER BY {$vs_related_table}.{$vs_sort}";
$vs_sort_fld = $vs_sort;
$va_selects[] = "{$vs_related_table}.{$vs_sort}";
}
}
$vs_sql = "\n\t\t\t\t\tSELECT " . join(', ', $va_selects) . "\n\t\t\t\t\tFROM " . $va_path[0] . "\n\t\t\t\t\t" . join("\n", array_merge($va_joins, $va_joins_post_add)) . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t" . join(' AND ', array_merge($va_wheres, array('(' . $va_path[1] . '.' . $vs_other_field . ' IN (' . join(',', $va_row_ids) . '))'))) . "\n\t\t\t\t\t{$vs_order_by}";
$qr_res = $o_db->query($vs_sql);
if ($vb_uses_relationship_types) {
$va_rel_types = $t_rel->getRelationshipInfo($va_path[1]);
}
$vn_c = 0;
if ($vn_start > 0) {
$qr_res->seek($vn_start);
}
while ($qr_res->nextRow()) {
if ($vn_c >= $vn_limit) {
break;
}
if (is_array($va_primary_ids) && is_array($va_primary_ids[$vs_related_table])) {
if (in_array($qr_res->get($vs_key), $va_primary_ids[$vs_related_table])) {
continue;
}
}
$va_row = $qr_res->getRow();
$vn_id = $va_row[$vs_key] . '/' . $va_row['row_id'];
$vs_sort_key = $qr_res->get($vs_sort_fld);
$vs_display_label = $va_row[$vs_label_display_field];
if (!$va_rels[$vs_sort_key][$vn_id]) {
$va_rels[$vs_sort_key][$vn_id] = $qr_res->getRow();
}
if ($vb_uses_effective_dates) {
// return effective dates as display/parse-able text
if ($va_rels[$vs_sort_key][$vn_id]['sdatetime'] || $va_rels[$vs_sort_key][$vn_id]['edatetime']) {
$o_tep->setHistoricTimestamps($va_rels[$vs_sort_key][$vn_id]['sdatetime'], $va_rels[$vs_sort_key][$vn_id]['edatetime']);
$va_rels[$vs_sort_key][$vn_id]['effective_date'] = $o_tep->getText();
}
}
$vn_locale_id = $qr_res->get('locale_id');
if ($vb_use_locale_codes) {
$va_rels[$vs_v]['locale_id'] = $vn_locale_id = $t_locale->localeIDToCode($vn_locale_id);
}
$va_rels[$vs_sort_key][$vn_id]['labels'][$vn_locale_id] = $vb_return_labels_as_array ? $va_row : $vs_display_label;
$va_rels[$vs_sort_key][$vn_id]['_key'] = $vs_key;
$va_rels[$vs_sort_key][$vn_id]['direction'] = $vs_direction;
$vn_c++;
if ($vb_uses_relationship_types) {
$va_rels[$vs_sort_key][$vn_id]['relationship_typename'] = $vs_direction == 'ltor' ? $va_rel_types[$va_row['relationship_type_id']]['typename'] : $va_rel_types[$va_row['relationship_type_id']]['typename_reverse'];
$va_rels[$vs_sort_key][$vn_id]['relationship_type_code'] = $va_rel_types[$va_row['relationship_type_id']]['type_code'];
}
//
// Return data in an arrangement more convenient for the data importer
//
if ($vb_group_fields) {
$vs_rel_pk = $t_rel_item->primaryKey();
if ($t_rel_item_label) {
foreach ($t_rel_item_label->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['preferred_labels'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
}
foreach ($t_rel_item->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['intrinsic'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
unset($va_rels[$vs_v]['_key']);
unset($va_rels[$vs_v]['row_id']);
}
}
$vn_i++;
}
ksort($va_rels);
// sort by sort key... we'll remove the sort key in the next loop while we add the labels
// Set 'label' entry - display label in current user's locale
$va_sorted_rels = array();
foreach ($va_rels as $vs_sort_key => $va_rels_by_sort_key) {
foreach ($va_rels_by_sort_key as $vn_id => $va_rel) {
$va_tmp = array(0 => $va_rel['labels']);
$va_sorted_rels[$vn_id] = $va_rel;
$va_values_filtered_by_locale = caExtractValuesByUserLocale($va_tmp);
$va_sorted_rels[$vn_id]['label'] = array_shift($va_values_filtered_by_locale);
}
}
$va_rels = $va_sorted_rels;
//
// END - traverse self relation
//
} else {
if (method_exists($this, 'isSelfRelationship') && $this->isSelfRelationship()) {
//
// START - from self relation itself (Eg. get related ca_objects from ca_objects_x_objects); in this case there are two possible paths (keys) to check, "left" and "right"
//
$va_wheres[] = "(" . $this->tableName() . '.' . $this->primaryKey() . " IN (" . join(",", $va_row_ids) . "))";
$vs_cur_table = array_shift($va_path);
$vs_rel_table = array_shift($va_path);
$va_rel_info = $this->getAppDatamodel()->getRelationships($vs_cur_table, $vs_rel_table);
$va_rels = array();
foreach ($va_rel_info[$vs_cur_table][$vs_rel_table] as $vn_i => $va_rel) {
$va_joins = array('INNER JOIN ' . $vs_rel_table . ' ON ' . $vs_cur_table . '.' . $va_rel[0] . ' = ' . $vs_rel_table . '.' . $va_rel[1] . "\n");
$vs_base_table = $vs_rel_table;
foreach ($va_path as $vs_join_table) {
$va_label_rel_info = $this->getAppDatamodel()->getRelationships($vs_base_table, $vs_join_table);
$va_joins[] = 'INNER JOIN ' . $vs_join_table . ' ON ' . $vs_base_table . '.' . $va_label_rel_info[$vs_base_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_label_rel_info[$vs_base_table][$vs_join_table][0][1] . "\n";
$vs_base_table = $vs_join_table;
}
$va_selects[] = $this->tableName() . '.' . $this->primaryKey() . ' AS row_id';
$vs_order_by = '';
if ($t_item_rel && $t_item_rel->hasField('rank')) {
$vs_order_by = ' ORDER BY ' . $t_item_rel->tableName() . '.rank';
$va_selects[] = $t_item_rel->tableName() . '.rank';
} else {
if ($t_rel_item && ($vs_sort = $t_rel_item->getProperty('ID_NUMBERING_SORT_FIELD'))) {
$vs_order_by = " ORDER BY {$vs_related_table}.{$vs_sort}";
$va_selects[] = "{$vs_related_table}.{$vs_sort}";
}
}
$vs_sql = "\n\t\t\t\t\tSELECT DISTINCT " . join(', ', $va_selects) . "\n\t\t\t\t\tFROM " . $this->tableName() . "\n\t\t\t\t\t" . join("\n", array_merge($va_joins, $va_joins_post_add)) . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t" . join(' AND ', $va_wheres) . "\n\t\t\t\t\t{$vs_order_by}\n\t\t\t\t";
//print "<pre>$vs_sql</pre>\n";
$qr_res = $o_db->query($vs_sql);
if ($vb_uses_relationship_types) {
$va_rel_types = $t_rel->getRelationshipInfo($t_item_rel->tableName());
$vs_left_table = $t_item_rel->getLeftTableName();
$vs_direction = $vs_left_table == $this->tableName() ? 'ltor' : 'rtol';
}
$vn_c = 0;
if ($vn_start > 0) {
$qr_res->seek($vn_start);
}
while ($qr_res->nextRow()) {
if ($vn_c >= $vn_limit) {
break;
}
if (is_array($va_primary_ids) && is_array($va_primary_ids[$vs_related_table])) {
if (in_array($qr_res->get($vs_key), $va_primary_ids[$vs_related_table])) {
continue;
}
}
if (isset($pa_options['idsOnly']) && $pa_options['idsOnly']) {
$va_rels[] = $qr_res->get($t_rel_item->primaryKey());
continue;
}
$va_row = $qr_res->getRow();
$vs_v = $va_row['row_id'] . '/' . $va_row[$vs_key];
$vs_display_label = $va_row[$vs_label_display_field];
if (!isset($va_rels[$vs_v]) || !$va_rels[$vs_v]) {
$va_rels[$vs_v] = $va_row;
}
if ($vb_uses_effective_dates) {
// return effective dates as display/parse-able text
if ($va_rels[$vs_v]['sdatetime'] || $va_rels[$vs_v]['edatetime']) {
$o_tep->setHistoricTimestamps($va_rels[$vs_v]['sdatetime'], $va_rels[$vs_v]['edatetime']);
$va_rels[$vs_v]['effective_date'] = $o_tep->getText();
}
}
$vn_locale_id = $qr_res->get('locale_id');
if ($vb_use_locale_codes) {
$va_rels[$vs_v]['locale_id'] = $vn_locale_id = $t_locale->localeIDToCode($vn_locale_id);
}
$va_rels[$vs_v]['labels'][$vn_locale_id] = $vb_return_labels_as_array ? $va_row : $vs_display_label;
$va_rels[$vs_v]['_key'] = $vs_key;
$va_rels[$vs_v]['direction'] = $vs_direction;
$vn_c++;
if ($vb_uses_relationship_types) {
$va_rels[$vs_v]['relationship_typename'] = $vs_direction == 'ltor' ? $va_rel_types[$va_row['relationship_type_id']]['typename'] : $va_rel_types[$va_row['relationship_type_id']]['typename_reverse'];
$va_rels[$vs_v]['relationship_type_code'] = $va_rel_types[$va_row['relationship_type_id']]['type_code'];
}
if ($vb_group_fields) {
$vs_rel_pk = $t_rel_item->primaryKey();
if ($t_rel_item_label) {
foreach ($t_rel_item_label->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['preferred_labels'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
}
foreach ($t_rel_item->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['intrinsic'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
unset($va_rels[$vs_v]['_key']);
unset($va_rels[$vs_v]['row_id']);
}
}
if (!isset($pa_options['idsOnly']) || !$pa_options['idsOnly']) {
// Set 'label' entry - display label in current user's locale
foreach ($va_rels as $vs_v => $va_rel) {
$va_tmp = array(0 => $va_rel['labels']);
$va_tmp2 = caExtractValuesByUserLocale($va_tmp);
$va_rels[$vs_v]['label'] = array_shift($va_tmp2);
}
}
}
//
// END - from self relation itself
//
} else {
//
// BEGIN - non-self relation
//
$va_wheres[] = "(" . $this->tableName() . '.' . $this->primaryKey() . " IN (" . join(",", $va_row_ids) . "))";
$vs_cur_table = array_shift($va_path);
$va_joins = array();
// Enforce restrict_to_lists for related list items
if ($vs_related_table_name == 'ca_list_items' && is_array($pa_options['restrictToLists'])) {
$va_list_ids = array();
foreach ($pa_options['restrictToLists'] as $vm_list) {
if ($vn_list_id = ca_lists::getListID($vm_list)) {
$va_list_ids[] = $vn_list_id;
}
}
if (sizeof($va_list_ids)) {
$va_wheres[] = "(ca_list_items.list_id IN (" . join(",", $va_list_ids) . "))";
}
}
if ($vb_is_combo_key_relation) {
$va_joins = array("INNER JOIN {$vs_related_table_name} ON {$vs_related_table_name}.row_id = " . $this->primaryKey(true) . " AND {$vs_related_table_name}.table_num = " . $this->tableNum());
} else {
foreach ($va_path as $vs_join_table) {
$va_rel_info = $this->getAppDatamodel()->getRelationships($vs_cur_table, $vs_join_table);
$vs_join = 'INNER JOIN ' . $vs_join_table . ' ON ';
$va_tmp = array();
foreach ($va_rel_info[$vs_cur_table][$vs_join_table] as $vn_i => $va_rel) {
$va_tmp[] = $vs_cur_table . "." . $va_rel_info[$vs_cur_table][$vs_join_table][$vn_i][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][$vn_i][1] . "\n";
}
$va_joins[] = $vs_join . join(' OR ', $va_tmp);
$vs_cur_table = $vs_join_table;
}
}
// If we're getting ca_set_items, we have to rename the intrinsic row_id field because the pk is named row_id below. Hence, this hack.
if ($vs_related_table_name == 'ca_set_items') {
$va_selects[] = 'ca_set_items.row_id AS record_id';
}
$va_selects[] = $this->tableName() . '.' . $this->primaryKey() . ' AS row_id';
$vs_order_by = '';
if ($t_item_rel && $t_item_rel->hasField('rank')) {
$vs_order_by = ' ORDER BY ' . $t_item_rel->tableName() . '.rank';
$va_selects[] = $t_item_rel->tableName() . '.rank';
} else {
if ($t_rel_item && ($vs_sort = $t_rel_item->getProperty('ID_NUMBERING_SORT_FIELD'))) {
$vs_order_by = " ORDER BY {$vs_related_table}.{$vs_sort}";
$va_selects[] = "{$vs_related_table}.{$vs_sort}";
}
}
$vs_sql = "\n\t\t\t\tSELECT DISTINCT " . join(', ', $va_selects) . "\n\t\t\t\tFROM " . $this->tableName() . "\n\t\t\t\t" . join("\n", array_merge($va_joins, $va_joins_post_add)) . "\n\t\t\t\tWHERE\n\t\t\t\t\t" . join(' AND ', $va_wheres) . "\n\t\t\t\t{$vs_order_by}\n\t\t\t";
$qr_res = $o_db->query($vs_sql);
if ($vb_uses_relationship_types) {
$va_rel_types = $t_rel->getRelationshipInfo($t_tmp->tableName());
if (method_exists($t_tmp, 'getLeftTableName')) {
$vs_left_table = $t_tmp->getLeftTableName();
$vs_direction = $vs_left_table == $this->tableName() ? 'ltor' : 'rtol';
}
}
$va_rels = array();
$vn_c = 0;
if ($vn_start > 0) {
$qr_res->seek($vn_start);
}
while ($qr_res->nextRow()) {
if ($vn_c >= $vn_limit) {
break;
}
if (is_array($va_primary_ids) && is_array($va_primary_ids[$vs_related_table])) {
if (in_array($qr_res->get($vs_key), $va_primary_ids[$vs_related_table])) {
continue;
}
}
if (isset($pa_options['idsOnly']) && $pa_options['idsOnly']) {
$va_rels[] = $qr_res->get($t_rel_item->primaryKey());
continue;
}
$va_row = $qr_res->getRow();
$vs_v = sizeof($va_path) <= 2 ? $va_row['row_id'] . '/' . $va_row[$vs_key] : $va_row[$vs_key];
$vs_display_label = $va_row[$vs_label_display_field];
//unset($va_row[$vs_label_display_field]);
if (!isset($va_rels[$vs_v]) || !$va_rels[$vs_v]) {
$va_rels[$vs_v] = $va_row;
}
if ($vb_uses_effective_dates) {
// return effective dates as display/parse-able text
if ($va_rels[$vs_v]['sdatetime'] || $va_rels[$vs_v]['edatetime']) {
$o_tep->setHistoricTimestamps($va_rels[$vs_v]['sdatetime'], $va_rels[$vs_v]['edatetime']);
$va_rels[$vs_v]['effective_date'] = $o_tep->getText();
}
}
$vn_locale_id = $qr_res->get('locale_id');
if ($vb_use_locale_codes) {
$va_rels[$vs_v]['locale_id'] = $vn_locale_id = $t_locale->localeIDToCode($vn_locale_id);
}
$va_rels[$vs_v]['labels'][$vn_locale_id] = $vb_return_labels_as_array ? $va_row : $vs_display_label;
$va_rels[$vs_v]['_key'] = $vs_key;
$va_rels[$vs_v]['direction'] = $vs_direction;
$vn_c++;
if ($vb_uses_relationship_types) {
$va_rels[$vs_v]['relationship_typename'] = $vs_direction == 'ltor' ? $va_rel_types[$va_row['relationship_type_id']]['typename'] : $va_rel_types[$va_row['relationship_type_id']]['typename_reverse'];
$va_rels[$vs_v]['relationship_type_code'] = $va_rel_types[$va_row['relationship_type_id']]['type_code'];
}
if ($vb_group_fields) {
$vs_rel_pk = $t_rel_item->primaryKey();
if ($t_rel_item_label) {
foreach ($t_rel_item_label->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['preferred_labels'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
}
foreach ($t_rel_item->getFormFields() as $vs_field => $va_field_info) {
if (!isset($va_rels[$vs_v][$vs_field]) || $vs_field == $vs_rel_pk) {
continue;
}
$va_rels[$vs_v]['intrinsic'][$vs_field] = $va_rels[$vs_v][$vs_field];
unset($va_rels[$vs_v][$vs_field]);
}
unset($va_rels[$vs_v]['_key']);
unset($va_rels[$vs_v]['row_id']);
}
}
if (!isset($pa_options['idsOnly']) || !$pa_options['idsOnly']) {
// Set 'label' entry - display label in current user's locale
foreach ($va_rels as $vs_v => $va_rel) {
$va_tmp = array(0 => $va_rel['labels']);
$va_tmp2 = caExtractValuesByUserLocale($va_tmp);
$va_rels[$vs_v]['label'] = array_shift($va_tmp2);
}
}
//
// END - non-self relation
//
}
}
// Apply restrictToBundleValues
$va_filters = isset($pa_options['restrictToBundleValues']) ? $pa_options['restrictToBundleValues'] : null;
if (is_array($va_filters) && sizeof($va_filters) > 0) {
foreach ($va_rels as $vn_pk => $va_related_item) {
foreach ($va_filters as $vs_filter => $va_filter_vals) {
if (!$vs_filter) {
continue;
}
if (!is_array($va_filter_vals)) {
$va_filter_vals = array($va_filter_vals);
}
foreach ($va_filter_vals as $vn_index => $vs_filter_val) {
// is value a list attribute idno?
$va_tmp = explode('.', $vs_filter);
$vs_element = array_pop($va_tmp);
if (!is_numeric($vs_filter_val) && (($t_element = $t_rel_item->_getElementInstance($vs_element)) && $t_element->get('datatype') == 3)) {
$va_filter_vals[$vn_index] = caGetListItemID($t_element->get('list_id'), $vs_filter_val);
}
}
$t_rel_item->load($va_related_item[$t_rel_item->primaryKey()]);
$va_filter_values = $t_rel_item->get($vs_filter, array('returnAsArray' => true, 'alwaysReturnItemID' => true));
$vb_keep = false;
if (is_array($va_filter_values)) {
foreach ($va_filter_values as $vm_filtered_val) {
if (!is_array($vm_filtered_val)) {
$vm_filtered_val = array($vm_filtered_val);
}
foreach ($vm_filtered_val as $vs_val) {
if (in_array($vs_val, $va_filter_vals)) {
// one match is enough to keep it
$vb_keep = true;
}
}
}
}
if (!$vb_keep) {
unset($va_rels[$vn_pk]);
}
}
}
}
//
// Sort on fields if specified
//
if (is_array($va_sort_fields) && sizeof($va_rels)) {
$va_ids = array();
$vs_rel_pk = $t_rel_item->primaryKey();
foreach ($va_rels as $vn_i => $va_rel) {
$va_ids[] = $va_rel[$vs_rel_pk];
}
// Handle sorting on attribute values
$vs_rel_pk = $t_rel_item->primaryKey();
foreach ($va_sort_fields as $vn_x => $vs_sort_field) {
if ($vs_sort_field == 'relation_id') {
// sort by relationship primary key
if ($t_item_rel) {
$va_sort_fields[$vn_x] = $vs_sort_field = $t_item_rel->tableName() . '.' . $t_item_rel->primaryKey();
}
continue;
}
$va_tmp = explode('.', $vs_sort_field);
if ($va_tmp[0] == $vs_related_table_name) {
$qr_rel = $t_rel_item->makeSearchResult($va_tmp[0], $va_ids);
$vs_table = array_shift($va_tmp);
$vs_key = join(".", $va_tmp);
while ($qr_rel->nextHit()) {
$vn_pk_val = $qr_rel->get($vs_table . "." . $vs_rel_pk);
foreach ($va_rels as $vn_rel_id => $va_rel) {
if ($va_rel[$vs_rel_pk] == $vn_pk_val) {
$va_rels[$vn_rel_id][$vs_key] = $qr_rel->get($vs_sort_field, array("delimiter" => ";", 'sortable' => 1));
break;
}
}
}
}
}
// Perform sort
$va_rels = caSortArrayByKeyInValue($va_rels, $va_sort_fields, $vs_sort_direction);
}
return $va_rels;
}
/**
* Converts list of relationships type codes and/or numeric ids to an id-only list
*/
private function _getRelationshipTypeIDs($pa_relationship_types, $pm_relationship_table_or_id)
{
$t_rel_type = new ca_relationship_types();
$va_type_list = $pa_relationship_types;
foreach ($va_type_list as $vn_i => $vm_type) {
if (!trim($vm_type)) {
unset($pa_relationship_types[$vn_i]);
continue;
}
if (!is_numeric($vm_type)) {
// try to translate item_value code into numeric id
if (!($vn_type_id = $t_rel_type->getRelationshipTypeID($pm_relationship_table_or_id, $vm_type))) {
unset($pa_relationship_types[$vn_i]);
continue;
}
unset($pa_relationship_types[$vn_i]);
$pa_relationship_types[] = $vn_type_id;
} else {
if (!$t_rel_type->load($vm_type)) {
unset($pa_relationship_types[$vn_i]);
continue;
}
$vn_type_id = $t_rel_type->getPrimaryKey();
}
$va_ids = $t_rel_type->getHierarchyChildren($vn_type_id, array('idsOnly' => true));
if (is_array($va_ids)) {
foreach ($va_ids as $vn_id) {
$pa_relationship_types[] = $vn_id;
}
}
}
return $pa_relationship_types;
}
