tmp_cube.txt at OpenBizMap

<?php
/*
OpenDataBag - Data Web Interface
Copyright (C) 2004 Nawara

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
*/

global $filter_array;

$title=get('title');
if(!$title)
  $title=$mm2;
if(!$title)
  $title='Cube';


function getIndex($line, $variable_name_array, &$cube_index_array, $drill_array)
{
  $tmp_index_array=array();
  $tmp_index='';
  $tmp_drill=1;
  $full_drill_key='';
  reset($variable_name_array);
  foreach($variable_name_array as $variable_name)
  {
    $tmp_value=read($line,$variable_name);

    $full_drill_key.=$variable_name.'='.$tmp_value.' ';
    $drill_key=($full_drill_key);

    if(!$tmp_drill)
      $tmp_value='&sum;';

    $tmp_index.=strtolower($variable_name.'='.$tmp_value.' ');
    $tmp_index_array[$variable_name]=$tmp_value;

    if(!isset($drill_array[$drill_key]) or $drill_array[$drill_key]==0)
      $tmp_drill=0;
  }
  $tmp_index=($tmp_index);
  $cube_index_array[$tmp_index]=$tmp_index_array;

  return $tmp_index;
}


echo '<a name="top" ></a>';

echo '<h2>';
myecho($title);
echo '</h2>';


//reading get params
for($tmp_ii=0;$tmp_ii<10;$tmp_ii++)
{
  $tmp_value=trim(get('a'.$tmp_ii));
  if($tmp_value)
    $dimension_array['left'][]=$tmp_value;

  $tmp_value=trim(get('b'.$tmp_ii));
  if($tmp_value)
    $dimension_array['up'][]=$tmp_value;

  $tmp_value=trim(get('c'.$tmp_ii));
  if($tmp_value)
    $measure_array[$tmp_value]=trim(get('d'.$tmp_ii));
}

//~ $dimension_array=array(
  //~ 'up' => array( ),//'All', 'sysGroupBy' ),//Datum der AuffÃ¤lligkeit'),
  //~ 'left' => array( 'All ', 'GeschÃ¤ftsstelle', 'Team')
//~ );
//~ $measure_array=array(
  //~ 'Arbeitskosten Gesamt' => 'sum',
  //~ 'Verwaltungskosten Gesamt' => 'sum',
  //~ 'Zinsausfall' => 'sum',
  //~ 'Sonstige Kosten' => 'sum',
  //~ 'Anzahl AuffÃ¤lligkeiten' => 'count',
  //~ 'Gesamtkosten der AuffÃ¤lligkeit' => 'sum',
//~ );

// definition
$max_fields=6;
if(count($dimension_array['up'])>=$max_fields)
  $max_fields=count($dimension_array['up'])+1;
if(count($dimension_array['left'])>=$max_fields)
  $max_fields=count($dimension_array['left'])+1;
if(count($measure_array)>=$max_fields)
  $max_fields=count($measure_array)+1;


echo '<a class="noprint" style="margin-left:10px;" href="#" onclick="document.getElementById(\'reportdef\').style.display=\'block\'; this.style.display=\'none\';">';
  myecho('Show definition');
echo '</a>';


echo('<form action="index.php" method="get">');
echo('<div id="reportdef" style="display:none;"><input name="odb_script" type="hidden" value="cube"></input>');
echo('<input name="odb_search" type="hidden" value="'.$search.'"></input>');

echo '<div class="supergroup">';

echo '<h3>';
myecho('Definition');
echo '</h3>';

echo '<table class="noprint">';

echo '<tr>';
  echo '<th>';
  echo '</th>';

  for($tmp_ii=0;$tmp_ii<$max_fields;$tmp_ii++)
  {
    echo '<th>';
      echo colSymbol($tmp_ii).'.';
    echo '</th>';
  }
echo '</tr>';



echo '<tr>';
  echo '<th>';
    echo 'Left';
  echo '</th>';

  $tmp_ii=0;
  reset($dimension_array['left']);
  foreach($dimension_array['left'] as $dimension_name)
  {
    echo '<td>';
      echo '<input class="fld_" name="a'.$tmp_ii.'" value="'.$dimension_name.'"/>';
    echo '</td>';
    $tmp_ii++;
  }
  for($tmp_ii=count($dimension_array['left']);$tmp_ii<$max_fields;$tmp_ii++)
  {
    echo '<td>';
      echo '<input class="fld_" name="a'.$tmp_ii.'" value=""/>';
    echo '</td>';
  }
echo '</tr>';



echo '<tr>';
  echo '<th>';
    echo 'Up';
  echo '</th>';

  $tmp_ii=0;
  reset($dimension_array['up']);
  foreach($dimension_array['up'] as $dimension_name)
  {
    echo '<td>';
      echo '<input class="fld_" name="b'.$tmp_ii.'" value="'.$dimension_name.'"/>';
    echo '</td>';
    $tmp_ii++;
  }

  for($tmp_ii=count($dimension_array['up']);$tmp_ii<$max_fields;$tmp_ii++)
  {
    echo '<td>';
      echo '<input class="fld_" name="b'.$tmp_ii.'" value=""/>';
    echo '</td>';
  }
echo '</tr>';



echo '<tr>';
  echo '<th>';
    echo 'Values';
  echo '</th>';

  $tmp_ii=0;
  reset($measure_array);
  foreach($measure_array as $measure_name => $measure_type)
  {
    echo '<td>';
      echo '<input class="fld_" name="c'.$tmp_ii.'" value="'.$measure_name.'"/>';
      echo '<select name="d'.$tmp_ii.'">';
      echo '<option>'.$measure_type.'</option>';
      echo '<option>sum</option>';
      echo '<option>count</option>';
      echo '</select>';
    echo '</td>';
    $tmp_ii++;
  }

  for($tmp_ii=count($measure_array);$tmp_ii<$max_fields;$tmp_ii++)
  {
    echo '<td>';
      echo '<input class="fld_" name="c'.$tmp_ii.'" value=""/>';
      echo '<select name="d'.$tmp_ii.'">';
      echo '<option></option>';
      echo '<option>sum</option>';
      echo '<option>count</option>';
      echo '</select>';
    echo '</td>';
  }
echo '</tr>';


echo '</table>';

  echo '<a class="noprint" style="margin-left:10px;" href="index.php?odb_sys=structure" target="_blank">';
    myecho('Show all available fields');
  echo '</a>';
  echo('<br /><input type="submit" class="submit" value="'.myecho('Change definition','RETURN').'" />');


echo '</div>';

echo('</div></form>');


flush();


//--------------------------------------------------------------------------------------
$cube_index_array=array();

$report_link='';
$report_link.='&amp;odb_search='.urlencode($search);
$report_link.='&amp;odb_script='.urlencode($script);
$report_link.='&amp;odb_title='.urlencode($title);
for($ii=0;$ii<10;$ii++)
{
  $report_link.='&amp;a'.$ii.'='.urlencode(get('a'.$ii));
  $report_link.='&amp;b'.$ii.'='.urlencode(get('b'.$ii));
  $report_link.='&amp;c'.$ii.'='.urlencode(get('c'.$ii));
  $report_link.='&amp;d'.$ii.'='.urlencode(get('d'.$ii));
}


//drill settings
$drill_array=unserialize(get('drill'));

$drillin=get('drillin1');
$drillout=get('drillout1');
if($drillin)
  $drill_array[$drillin]=1;
if($drillout)
  $drill_array[$drillout]=0;

$drillin=get('drillin2');
$drillout=get('drillout2');
if($drillin)
  $drill_array[$drillin]=1;
if($drillout)
  $drill_array[$drillout]=0;

$session_array['drill']=serialize($drill_array);


$cube_array=array();

$record_array=$show_result_array;
reset($record_array);
while(list($tmp,$line)=each($record_array))
{
  $up_index=getIndex($line,$dimension_array['up'], $cube_index_array['up'], $drill_array);
  $left_index=getIndex($line,$dimension_array['left'], $cube_index_array['left'], $drill_array);

  reset($measure_array);
  while(list($measure_name,$measure_type)=each($measure_array))
  {
    if($measure_type=='sum')
      $cube_array[$left_index][$up_index][$measure_name]+=read($line,$measure_name);
    else //if($measure_type=='count')
      $cube_array[$left_index][$up_index][$measure_name]+=1;
  }
}

 //$cube_index_array=arsort($cube_index_array);

  $sort_array=array();
  reset($cube_index_array['up']);
  foreach($cube_index_array['up'] as $key => $val)
  {
    $sort_array[$key]=$val;
  }
  ksort($sort_array);
  $cube_index_array['up']=$sort_array;
  
  $sort_array=array();
  reset($cube_index_array['left']);
  foreach($cube_index_array['left'] as $key => $val)
  {
    $sort_array[$key]=$val;
  }
  ksort($sort_array);
  $cube_index_array['left']=$sort_array;
  
// echo '<pre>';
// print_r($cube_index_array['up']);
// echo '</pre>';

//~ echo '<pre>';
//~ print_r($cube_array);
//~ echo '</pre>';

echo '<div class="supergroup">';
echo '<table border="1">';

  // UP dimensions headers
  $up_first_level=0; //span switch
  $left_empty_space_presented=0;
  $full_drill_key_array=array();
  reset($dimension_array['up']);
  foreach($dimension_array['up'] as $tmp_ii => $up_dimension_name)
  {
    echo '<tr>';

      //left empty space
      if(!$left_empty_space_presented)
      {
        echo '<th rowspan="'.(count($dimension_array['up'])).'" colspan="'.count($dimension_array['left']).'"></th>';
        $left_empty_space_presented=1;
      }

      //up dimension name
      echo '<th>';
        echo $up_dimension_name;
      echo '</th>';

      //up specific headers
      /*
      if($up_first_level)
      {
        $span_array=array();
        reset($measure_array);
        reset($cube_index_array['up']);
        foreach($cube_index_array['up'] as $up_index => $up_index_values_array)
        //foreach($measure_array as $measure_name => $tmp)
          $span_array[$up_dimension_name][$up_index_values_array[$up_dimension_name]]++;
      }
      */
      
      reset($measure_array);
      reset($cube_index_array['up']);
      foreach($cube_index_array['up'] as $up_index => $up_index_values_array)
      {
        if(!$up_first_level or $span_array[$up_dimension_name][$up_index_values_array[$up_dimension_name]]>0)
        {
          $full_drill_key_array[$up_index].=$up_dimension_name.'='.$up_index_values_array[$up_dimension_name].' ';
  
          if($up_index_values_array[$up_dimension_name]!='&sum;' and ($tmp_ii!=count($dimension_array['up'])-1))
          {
            $drill_index_array['up'][$up_index]=($full_drill_key_array[$up_index]);
            $drill_key=$drill_index_array['up'][$up_index];
          }
          
          foreach($measure_array as $measure_name => $tmp)
          {
            if($up_first_level)
              echo '<th colspan="'.$span_array[$up_dimension_name][$up_index_values_array[$up_dimension_name]].'">';
            else
              echo '<th>';
              
            if($up_index_values_array[$up_dimension_name]!='&sum;' and ($tmp_ii!=count($dimension_array['up'])-1))
            {
              if(!isset($drill_array[$drill_key]) or $drill_array[$drill_key]==0)
                echo '<a href="index.php?odb_script=cube&amp;odb_drillin1='.$drill_key.'&amp;'.$report_link.'">';
              else
                echo '<a href="index.php?odb_script=cube&amp;odb_drillout1='.$drill_key.'&amp;'.$report_link.'">';
  
              if(strlen($up_index_values_array[$up_dimension_name]))
                echo $up_index_values_array[$up_dimension_name];
              else
                echo '&mdash;';
  
              echo '</a>';
            }
            else
            {
              if(strlen($up_index_values_array[$up_dimension_name]))
                echo $up_index_values_array[$up_dimension_name];
              else
                echo '&mdash;';
            }
  
            echo '</th>';

            if($up_first_level)
              $span_array[$up_dimension_name][$up_index_values_array[$up_dimension_name]]=0;
          }
        }
      }

    echo '</tr>';
    $up_first_level=0;
  }

  // MEASURES headers
  echo '<tr>';

    //left empty space by SPAN
    //measure names row header
    echo '<th rowspan="1" colspan="'.(count($dimension_array['left'])+1).'"></th>';

    //up measure headers
    $col_count=0;
    $up_placeholder_html='<th style="width:10px;"></th>';
    reset($measure_array);
    reset($cube_index_array['up']);
    foreach($cube_index_array['up'] as $up_index => $up_index_values_array)
    foreach($measure_array as $measure_name => $tmp)
    {
      echo '<th>';
      echo $measure_name;
      echo '</th>';

      $up_placeholder_html.='<th>'.colSymbol($col_count).'.</th>';
      $col_count++;
    }

  echo '</tr>';


  // LEFT dimensions headers
  echo '<tr>';

    //left dimension names
    reset($dimension_array['left']);
    foreach($dimension_array['left'] as $left_dimension_name)
    {
      echo '<th>';
      echo $left_dimension_name;
      echo '</th>';
    }

    //up empty space
    echo $up_placeholder_html;

  echo '</tr>';


  //echo '<pre>';
  //print_r($cube_index_array['left']);
  //echo '</pre>';

  
  $rowspan_array=array();
  reset($cube_index_array['left']);
  foreach($cube_index_array['left'] as $left_index => $left_index_values_array)
  {
    foreach($left_index_values_array as $left_dimension_name => $tmp)
    {
      $rowspan_array[$left_dimension_name][$left_index_values_array[$left_dimension_name]]++;
      //echo $left_dimension_name.'==='.$left_index_values_array[$left_dimension_name].'<br />';
      break;
    }
  }
  
  
  // echo '<pre>';
  // print_r($rowspan_array);
  // echo '</pre>';
  
  // MEASURES
  $row_count=1;
  reset($cube_index_array['left']);
  foreach($cube_index_array['left'] as $left_index => $left_index_values_array)
  {
    echo '<tr>';

    //left specific headers
    $left_first_level=0; //span switch
    $full_drill_key='';
    reset($dimension_array['left']);
    foreach($dimension_array['left'] as $tmp_ii => $left_dimension_name)
    {
      if(!$left_first_level or $rowspan_array[$left_dimension_name][$left_index_values_array[$left_dimension_name]]>0)
      {
        if($left_first_level)
          echo '<th rowspan="'.$rowspan_array[$left_dimension_name][$left_index_values_array[$left_dimension_name]].'">';
        else
          echo '<th>';
          
        if($left_index_values_array[$left_dimension_name]!='&sum;' and ($tmp_ii!=count($dimension_array['left'])-1))
        {
          $full_drill_key.=$left_dimension_name.'='.$left_index_values_array[$left_dimension_name].' ';
          $drill_key=($full_drill_key);
          $drill_index_array['left'][$left_index]=$drill_key;
  
          if(!isset($drill_array[$drill_key]) or $drill_array[$drill_key]==0)
            echo '<a href="index.php?odb_script=cube&amp;odb_drillin2='.$drill_key.'&amp;'.$report_link.'">';
          else
            echo '<a href="index.php?odb_script=cube&amp;odb_drillout2='.$drill_key.'&amp;'.$report_link.'">';
  
          if(strlen($left_index_values_array[$left_dimension_name]))
            echo $left_index_values_array[$left_dimension_name];
          else
            echo '&mdash;';
          echo '</a>';
        }
        else
        {
          if(strlen($left_index_values_array[$left_dimension_name]))
            echo $left_index_values_array[$left_dimension_name];
          else
            echo '&mdash;';
        }
  
        echo '</a></th>';
        if($left_first_level)
          $rowspan_array[$left_dimension_name][$left_index_values_array[$left_dimension_name]]=0;
      }
      $left_first_level=0;
    }

    //left empty column - up dimension names placeholder
    echo '<th style="width:10px;">'.$row_count.'.</th>';

    reset($measure_array);
    reset($cube_index_array['up']);
    foreach($cube_index_array['up'] as $up_index => $up_index_values_array)
    foreach($measure_array as $measure_name => $measure_type)
    {
      echo '<td>';

        $show_mode=CFG_SHOW;
        $show_value=$cube_array[$left_index][$up_index][$measure_name];
        if($measure_type=='sum')
          $template='money';
        else
          $template='number';

        //bilding search filter
        $tmp_search=$search.' ';
        reset($up_index_values_array);
        foreach($up_index_values_array as $tmp_key => $tmp_val)
          if($tmp_val=='')
            $tmp_search.='-[$'.$tmp_key.'=] ';
          elseif($tmp_val!='&sum;')
            $tmp_search.='['.$tmp_key.'='.$tmp_val.'$] ';
        reset($left_index_values_array);
  
        foreach($left_index_values_array as $tmp_key => $tmp_val)
          if($tmp_val=='')
            $tmp_search.='-[$'.$tmp_key.'=] ';
          elseif($tmp_val!='&sum;')
            $tmp_search.='['.$tmp_key.'='.$tmp_val.'$] ';
        
        echo '<a href="index.php?odb_search='.urlencode(trim($tmp_search)).'">';
        
        if(file_exists(cfg_data_path.'/config/script/fld_'.$template.'.txt'))
        {
          include(cfg_data_path.'/config/script/fld_'.$template.'.txt');
        }
        else
        {
          if(!file_exists(cfg_data_path.'/script/fld_'.$template.'.txt'))
            $template='';

          include(cfg_data_path.'/script/fld_'.$template.'.txt');
        }


        echo '</a>';
      echo '</td>';
    }

    echo '</tr>';
    $row_count++;
  }

echo '</table>';
echo '</div>';

?>