/** * Build the data tree for the given list of fields * and limits. This is called by I2CE_List::buildDataTree. * See that for more details * @see I2CE_List::buildDataTree * @param array $fields The fields to build the tree * @param array $forms The selectable forms * @param array $displayed The displayed forms for the tree * @param array $limits The list of limits for each form. * @param array $orders The order fields for each given form * @param int $show_hidden 0=non-hidden, 1=All, 2=hidden only. Defaults to 0 * @return array The ordered list of all entries in the tree. */ public function buildDataTree($fields, $forms, $displayed, $limits, $orders, $show_hidden = 0) { $prev_form = false; $form_aliases = array(); $selects = array(); $displays = array(); $all_orders = array(); $formObjs = array(); $skip_links = array(); $skip_link_fields = array(); $this->preserve_ids = true; foreach ($fields as $formfield) { list($form, $link_field) = $formfield; $cachedForm = new I2CE_CachedForm($form); $cachedForm->generateCachedTable(false); unset($cachedForm); $alias_form = $form . ($link_field ? "+{$link_field}" : ""); if (array_key_exists($alias_form, $limits)) { $limit = $limits[$alias_form]; } elseif (array_key_exists($form, $limits)) { $limit = $limits[$form]; } else { $limit = array(); } self::addFormIdToLimit($form, $limit); $limit = I2CE_List::showHiddenLimit($limit, $show_hidden); $disp_fields = I2CE_List::getDisplayFields($form); $field_list = $disp_fields; $field_list[] = 'id'; if ($link_field && !in_array($link_field, $field_list)) { $field_list[] = $link_field; } if (array_key_exists($form, $orders)) { $order = $orders[$form]; } else { $order = I2CE_List::getSortFields($form); } $sort_list = array(); if (array_key_exists($form, $displayed) && $displayed[$form]) { $displays[$alias_form]['form'] = $form; $displays[$alias_form]['link_field'] = $link_field; foreach ($disp_fields as $disp) { $displays[$alias_form]['fields'][$disp] = "{$alias_form}+{$disp}"; } foreach ($order as $i => $ord) { if (!is_string($ord)) { unset($order[$i]); continue; } if ($ord[0] == '-') { $field = substr($ord, 1); $all_orders[] = "`{$alias_form}`.`{$field}` DESC"; } else { $field = $ord; $all_orders[] = "`{$alias_form}`.`{$field}` ASC"; } if (!in_array($field, $field_list)) { $sort_list[] = $field; } } } else { $skip_link_fields[$alias_form] = $link_field; } if (!array_key_exists($form, $formObjs)) { $formObjs[$form] = I2CE_FormFactory::instance()->createContainer($form); if (!$formObjs[$form] instanceof I2CE_Form) { I2CE::raiseError("Could not instantiate {$form}"); return array(); } } $where = false; if (is_array($limit) && count($limit) > 0) { $where = $formObjs[$form]->generateWhereClause($limit, array("I2CE_FormStorage_cached", "getSQLField")); } $query = $this->getRequiredFieldsQuery($form, array_merge($field_list, $sort_list), null, false, array("I2CE_FormStorage_cached", "getSQLField")); if (is_array($prev_form) && array_key_exists('form', $prev_form)) { if ($prev_form['form'] == $form) { $froms[$alias_form] = " LEFT JOIN "; } else { $froms[$alias_form] = " LEFT JOIN "; } } else { $froms[$alias_form] = ""; } $froms[$alias_form] .= "({$query}" . ($where ? " WHERE " . $where : "") . ") AS `{$alias_form}`"; if ($link_field) { $join_ons = array(); $ff = $formObjs[$form]->getField($link_field); if ($ff instanceof I2CE_FormField_MAPPED) { foreach ($ff->getSelectableForms() as $link_form) { if (array_key_exists($link_form, $form_aliases)) { foreach ($form_aliases[$link_form] as $link_alias) { $join_ons[] = "`{$alias_form}`.`{$link_field}` = `{$link_alias}`.`id`"; if (!array_key_exists($link_alias, $displays) && array_key_exists($link_alias, $skip_link_fields)) { $skip_links[$alias_form . '+' . $link_field] = $link_alias . '+' . $skip_link_fields[$link_alias]; } } } } } else { I2CE::raiseMessage("Not sure what to link for {$form} {$link_field} so guessing."); $join_ons[] = "`{$alias_form}`.`{$link_field}` = `" . $prev_form['alias'] . "`.`id`"; } if (count($join_ons) > 0) { $froms[$alias_form] .= " ON (" . implode(' OR ', $join_ons) . ") "; } else { I2CE::raiseError("Don't know how to join on {$form} {$link_field}"); return array(); } } foreach ($field_list as $field) { $selects[] = "`{$alias_form}`.`{$field}` AS `{$alias_form}+{$field}`"; } $prev_form = array('form' => $form, 'alias' => $alias_form); $form_aliases[$form][] = $alias_form; } $or_wheres = array(); foreach ($forms as $selectable) { if (array_key_exists($selectable, $form_aliases)) { foreach ($form_aliases[$selectable] as $required_form) { $or_wheres[] = "`{$required_form}`.`id` IS NOT NULL"; } } } $join_query = "SELECT " . implode(',', $selects) . " FROM " . implode('', $froms) . (count($or_wheres) > 0 ? " WHERE " . implode(' OR ', $or_wheres) : "") . (count($all_orders) > 0 ? " ORDER BY " . implode(',', $all_orders) : ""); //I2CE::raiseMessage( $join_query ); $res = $this->db->query($join_query); if (I2CE::pearError($res, "Bad query -- {$join_query}")) { return array(); } $prev_ids = array(); $results = array(); $phonebook = array(); $display_string = array(); foreach ($displays as $fix_link_alias => &$fix_link_disp_data) { if (array_key_exists('link_field', $fix_link_disp_data) && $fix_link_disp_data['link_field'] != '') { $full_link_field = strtolower($fix_link_alias . "+" . $fix_link_disp_data['link_field']); while (array_key_exists($full_link_field, $skip_links)) { $full_link_field = $skip_links[$full_link_field]; } $fix_link_disp_data['link_field'] = $full_link_field; } } $display_copy = $displays; while ($data = $res->fetchRow()) { unset($prev_disp); foreach ($displays as $alias => $disp_data) { $id_field = strtolower($alias . "+id"); if ($data->{$id_field} == null || array_key_exists($data->{$id_field}, $phonebook)) { continue; } $curr = array(); $add_this = false; if (in_array($disp_data['form'], $forms)) { $curr['value'] = $data->{$id_field}; $add_this = true; } if (!$add_this) { $check_ok = false; foreach ($display_copy as $alias_copy => $disp_data_copy) { if ($alias_copy == $alias) { $check_ok = true; continue; } if (!$check_ok) { continue; } if (array_key_exists('link_field', $disp_data_copy) && $disp_data_copy['link_field'] != '') { $link_field_copy = $disp_data_copy['link_field']; if ($data->{$id_field} == $data->{$link_field_copy}) { $add_this = true; break; } } } } if (!$add_this) { continue; } if (!array_key_exists($disp_data['form'], $display_string)) { $display_string[$disp_data['form']] = I2CE_List::getDisplayString($disp_data['form']); } $disp_array = array(); $disp_str = $display_string[$disp_data['form']]; $disp_str_arr = explode("%s", $disp_str); $fo = $formObjs[$disp_data['form']]; $disp_count = 0; foreach ($disp_data['fields'] as $field => $dbfield) { $disp_count++; if ($field == $disp_data['link_field']) { // Don't include the data from the link field since it will already // be in the tree above it. if ($disp_count == 1) { unset($disp_str_arr[$disp_count]); } else { unset($disp_str_arr[$disp_count - 1]); } continue; } $dbfield = strtolower($dbfield); $fieldObj = $fo->getField($field); if (!$fieldObj instanceof I2CE_FormField) { I2CE::raiseError("Could not get field {$field}"); continue; } if (isset($data->{$dbfield})) { $fieldObj->setFromDB($data->{$dbfield}); $disp_array[$field] = $fieldObj->getDisplayValue(); } else { $disp_array[$field] = null; } } $disp_str = implode('%s', $disp_str_arr); $display = vsprintf($disp_str, $disp_array); //$display = $data->$disp_data['fields']['name']; $curr['display'] = $display; $phonebook[$data->{$id_field}] =& $curr; if ($disp_data['link_field'] != '') { $link_field = $disp_data['link_field']; if (array_key_exists($data->{$link_field}, $phonebook)) { $add_to =& $phonebook[$data->{$link_field}]; if (!array_key_exists('children', $phonebook[$data->{$link_field}])) { $phonebook[$data->{$link_field}]['children'] = array(); } $phonebook[$data->{$link_field}]['children'][] =& $curr; } else { I2CE::raiseMessage("Couldn't find {$link_field} " . $data->{$link_field} . " in phonebook"); } } else { $results[] =& $curr; } unset($curr); } } $this->preserve_ids = false; return $results; }