/** * Create a data tree from a report of the selectable forms. Deisgned to be fed into tree select * @param array $fields * @param array $forms An unorderd array of form names whose values we allow to be selected * @param array $displayed The displayed forms for the tree * @param array $limits An array with keys form names and value limit data * @param array $orders An array with keys form names and values array of field orders for that form. * If the form name has no orders, we use default ordering for that form based on its displayed firelds * @param int $show_hidden 0=non-hidden, 1=All, 2=hidden only. Defaults to 0 * @param string $report A report name to use for the query instead of building it from form cache or directly. * @return array */ public static function buildReportTree($fields, $forms, $displayed, $limits, $orders = array(), $show_hidden = 0, $report = null) { if (!is_array($report) || !array_key_exists('report', $report)) { return array(); } $report_table = I2CE_CustomReport::getCachedTableName($report['report']); if (!$report_table) { return array(); } $map = array(); if (array_key_exists('map', $report)) { $map = $report['map']; } $where = array(); $displays = array(); $report_alias = array(); $formObjs = array(); $use_link = ''; foreach ($fields as $formfield) { list($form, $link_field) = $formfield; $ff = $form . ($link_field ? "+{$link_field}" : ""); if (array_key_exists($ff, $map)) { $report_alias[$ff] = $map[$ff]; } elseif (array_key_exists($form, $map)) { $report_alias[$form] = $map[$form]; $report_alias[$ff] = $map[$form]; } else { $report_alias[$form] = $form; $report_alias[$ff] = $form; } //if ( array_key_exists( $ff, $limits ) ) { //$limit = $limits[$ff]; //} else if (array_key_exists($report_alias[$ff], $limits)) { $limit = $limits[$report_alias[$ff]]; } elseif (array_key_exists($form, $limits)) { $limit = $limits[$form]; } else { $limit = array(); } self::addFormIdToLimit($form, $limit); $limit = self::showHiddenLimit($limit, $show_hidden); /* if ( !$show_hidden ) { $hidden = $report_alias[$ff] . '+i2ce_hidden'; $where[] = "( `$hidden` = 0 OR ISNULL( `$hidden` ) )"; } */ $disp_fields = I2CE_List::getDisplayFields($form); 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] ) { if (array_key_exists($form, $displayed)) { $alias_form = $report_alias[$ff]; if ($displayed[$form]) { $displays[$alias_form]['form'] = $form; if ($use_link == '') { $displays[$alias_form]['link_field'] = $link_field == '' ? $link_field : "{$alias_form}+{$link_field}"; } else { $displays[$alias_form]['link_field'] = $use_link; $use_link = ''; } 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"; } } } elseif ($use_link == '') { $use_link = $link_field == '' ? $link_field : "{$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(); } } self::$curr_alias = $report_alias[$ff]; $where[] = $formObjs[$form]->generateWhereClause($limit, array("I2CE_DataTree", "getSQLField")); } $where_clause = ""; $order_by = ""; if (count($where) > 0) { $where_clause = " WHERE " . implode(' AND ', $where); } if (count($all_orders) > 0) { $order_by = " ORDER BY " . implode(',', $all_orders); } $qry = "SELECT * FROM {$report_table} {$where_clause} {$order_by}"; $db = MDB2::singleton(); I2CE::raiseMessage($qry); $res = $db->query($qry); if (I2CE::pearError($res, "Invalid report data tree query: ")) { return array(); } $phonebook = array(); $results = array(); $display_string = array(); $display_copy = $displays; while ($data = $res->fetchRow()) { 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 ($dbfield == $disp_data['link_field']) { // Don't include the data from the link field since it will already be there. 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); $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); } } return $results; }
protected function _getMergedReportData($config, &$joins, $merge_field = false, $merge_ref = 'primary_table') { if (!$config instanceof I2CE_MagicDataNode) { return; } if (!$config->is_parent('fields')) { return; } if ($merge_field) { $enabled = false; $config->setIfIsSet($enabled, 'enabled'); if (!$enabled) { return; } //PUT IN LOGIC TO GET THE REPORT MERGE FROM THE formfield and KEY $mergekey = $config->getName(); list($mergeform, $mergefield) = array_pad(explode('+', $merge_field, 2), 2, ''); if (!$mergeform || !$mergefield) { I2CE::raiseError("Bad form field"); return; } $mergereport = false; $mergeconfig = I2CE::getConfig()->traverse("/modules/CustomReports/reports/" . $this->config->report . "/reporting_forms/{$mergeform}/fields/{$mergefield}/merges/{$mergekey}"); if (!$mergeconfig instanceof I2CE_MagicDataNode) { I2CE::raiseError("Bad merge /modules/CustomReports/reports/" . $this->config->report . "/reporting_forms/{$mergeform}/fields/{$mergefield}/merges/{$mergekey}"); return; } $mergeconfig->setIfIsSet($mergereport, "report"); if (!I2CE_MagicDataNode::checkKey($mergereport)) { I2CE::raiseError("Bad merge report"); return; } if (in_array($mergereport, $this->seen_merges)) { //don't allow to merge in a report more than once return; } $this->seen_merges[] = $mergereport; $child_merge_ref = "{$mergekey}:{$mergereport}"; $mergeconfig->setIfIsSet($child_mergeform, "reportForm"); if (!I2CE_MagicDataNode::checkKey($child_mergeform)) { I2CE::raiseError("Bad merge report form"); return; } $join_style = false; $mergeconfig->setIfIsSet($join_style, "join_style"); if (!$join_style) { I2CE::raiseError("Bad join style"); return; } $conditions = array(); foreach ($config->fields as $formFieldName => $formFieldConfig) { if (!$formFieldConfig instanceof I2CE_MagicDataNode) { continue; } $additional = false; $add_reportfield = false; if ($formFieldConfig->setIfIsSet($additional, 'merge_additional')) { I2CE::raiseError("Merge Addiitonal at " . $formFieldConfig->getPath() . ' on ' . $additional); // something like /$report_view/fields/primary_form+job_cadre or /$report_views $additional = explode('/', ltrim($additional, '/')); if (count($additional) == 3) { //it is a report view $add_reportfield = array_pop($additional); //array_pop($additional); //fields //array_pop($additional); //reportview $add_merge = 'primary_table'; } else { if (count($additional) > 3) { $add_reportfield = array_pop($additional); array_pop($additional); $add_merge = array_pop($additional); } } } if ($add_reportfield) { //$conditions[] = '`' . $child_merge_ref . '`.`' . $mergeform . '+'. $mergefield . '` = `' . $add_merge .'`.`'. $add_reportfield .'`'; $conditions[] = '`' . $child_merge_ref . '`.`' . $formFieldName . '` = `' . $add_merge . '`.`' . $add_reportfield . '`'; } } $method = 'mergeOn_' . $join_style; if (!($conditions = $this->{$method}($merge_ref, $mergeform, $mergefield, $child_merge_ref, $child_mergeform, $mergeconfig->getAsArray('join_data'), $conditions))) { I2CE::raiseError("bad join condition"); return; } $merge_ref = $child_merge_ref; $show_blanks = 1; $config->setIfIsSet($show_blanks, "show_blanks"); if ($show_blanks) { $join = ' RIGHT JOIN '; } else { $join = ' JOIN '; } $joins[] = $join . I2CE_CustomReport::getCachedTableName($mergereport) . ' AS `' . $merge_ref . '` ON ((' . implode(') AND (', $conditions) . ')) '; } foreach ($config->fields as $formFieldName => $formFieldConfig) { if (!$formFieldConfig instanceof I2CE_MagicDataNode) { continue; } if (!$formFieldConfig->is_parent("merges")) { continue; } foreach ($formFieldConfig->merges as $mergekey => $mergeConfig) { $this->_getMergedReportData($mergeConfig, $joins, $formFieldName, $merge_ref); } } }