Author: spadkins
Date: Fri Nov 17 20:36:07 2006
New Revision: 8085
Modified:
p5ee/trunk/App-Repository/lib/App/Repository.pm
Log:
summarize_rows() and appropriate changes for extend_columns
Modified: p5ee/trunk/App-Repository/lib/App/Repository.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository.pm Fri Nov 17 20:36:07 2006
@@ -864,7 +864,12 @@
#}
}
if ($contains_expr) {
- $cols = $self->extend_columns($table, $cols);
+ my $new_cols = $self->extend_columns($table, $cols);
+ # the caller wanted his column list extended
+ if ($#$new_cols > $#$cols && $options->{extend_columns}) {
+ @$cols = @$new_cols; # so copy the columns
+ }
+ $cols = $new_cols; # then point to the new columns
regardless
}
$rows = $self->_get_rows($table, $params, $cols, $options);
@@ -896,7 +901,7 @@
# case, we need to add them.
sub extend_columns {
&App::sub_entry if ($App::trace);
- my ($self, $table, $cols) = @_;
+ my ($self, $table, $cols, $options) = @_;
my (%colidx, $expr_columns, $expr, $extended, $col);
# Take note of which columns are alread in the list of requested columns.
for (my $i = 0; $i <= $#$cols; $i++) {
@@ -928,8 +933,10 @@
foreach my $expr_col (@$expr_columns) {
if (! defined $colidx{$expr_col}) {
if (!$extended) {
- $cols = [ @$cols ]; # make a copy. don't extend original.
$extended = 1;
+ if (!$options->{extend_columns}) {
+ $cols = [ @$cols ]; # make a copy. don't extend
original.
+ }
}
push(@$cols, $expr_col); # extend the column list.
$colidx{$expr_col} = $#$cols;
@@ -942,6 +949,22 @@
return($cols);
}
+sub _contains_expr {
+ my ($self, $table, $columns) = @_;
+ my $contains_expr = 0;
+ my ($column);
+ my $column_defs = $self->{table}{$table}{column};
+ for (my $i = 0; $i <= $#$columns; $i++) {
+ $column = $columns->[$i];
+ if ($column_defs->{$column}{expr}) {
+ $contains_expr = 1;
+ last;
+ }
+ }
+ &App::sub_exit($contains_expr) if ($App::trace);
+ return($contains_expr);
+}
+
#############################################################################
# set_rows()
#############################################################################
@@ -2632,15 +2655,16 @@
=cut
#############################################################################
-# summarize()
+# summarize_rows()
#############################################################################
-=head2 summarize()
+=head2 summarize_rows()
- * Signature: $summarized_rows = $rep->summarize($rows, $columns,
$summcolidx, $formulas);
+ * Signature: $summarized_rows = $rep->summarize_rows($table, $rows,
$columns, $summary_keys, $options);
+ * Param: $table string
* Param: $rows [][]
* Param: $columns []
- * Param: $summcolidx []
+ * Param: $summary_keys []
* Param: $formulas {}
* Return: $summarized_rows []
* Throws: App::Exception::Repository
@@ -2657,90 +2681,147 @@
[ 1, "Ben", "Blue", 22.6, 195, ],
);
@columns = ( "id", "name", "team", "rating", "score" );
- @summcolidx = ( 2 ); # "team"
- %formulas = (
- rating => "{sum(rating)}/{count(rating)}",
+ @summary_keys = ( "team" );
+
+ $summarized_rows = $rep->summarize_rows([EMAIL PROTECTED], [EMAIL
PROTECTED], [EMAIL PROTECTED], \%formulas);
+
+ @rows = (
+ { id=>5, "Jim", "Green", 13.5, 320, },
+ { id=>3, "Bob", "Green", 4.2, 230, },
+ { id=>9, "Ken", "Green", 27.4, 170, },
+ { id=>2, "Kim", "Blue", 11.7, 440, },
+ { id=>7, "Jan", "Blue", 55.1, 90, },
+ { id=>1, "Ben", "Blue", 22.6, 195, },
);
+ @columns = ( "rating", "score" ); # summarize a subset of the columns
+ @summary_keys = ( "team" );
+ %options = ();
- $summarized_rows = $rep->summarize([EMAIL PROTECTED], [EMAIL PROTECTED],
[EMAIL PROTECTED], \%formulas);
+ $summarized_rows = $rep->summarize_rows([EMAIL PROTECTED], [EMAIL
PROTECTED], [EMAIL PROTECTED], \%options);
=cut
-sub summarize {
+sub summarize_rows {
&App::sub_entry if ($App::trace);
- my ($self, $rows, $columns, $summcolidx, $formulas) = @_;
+ my ($self, $table, $rows, $columns, $summary_keys, $options) = @_;
- my (@summary_rows, $summary_row, $create_summary);
- my ($key, $nextkey, $row, $rowidx, $colidx, $numcols, $column, $elem);
- my (%total, $formula);
+ $summary_keys = [] if (!$summary_keys);
- $numcols = $#$columns + 1;
+ my $column_def = $self->{table}{$table}{column};
- for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
- $row = $rows->[$rowidx];
+ my (@summary_rows, $summary_row, %summary_row);
+ my ($key, $row, $hash_rows, $hash_row, $i, $rowidx, $colidx, $column,
$value);
+ my $row_type = "ARRAY";
- $key = "Total";
- $key = join(",", @{$row->[EMAIL PROTECTED])
- if (defined $summcolidx);
+ if (!$rows || $#$rows == -1) {
+ # do nothing
+ }
+ else {
+ $row_type = (ref($rows->[0]) eq "ARRAY") ? "ARRAY" : "HASH";
- # accumulate totals
- for ($colidx = 0; $colidx < $numcols; $colidx++) {
- $column = $columns->[$colidx];
- $elem = $rows->[$rowidx][$colidx];
- if (defined $elem && $elem ne "") {
- if (defined $total{$column}) {
- $total{"sum($column)"} += ($elem+0);
+ # if we are summarizing HASH rows, convert to ARRAY
+ if ($row_type eq "HASH") {
+ $row = $rows->[0];
+ if (!$columns) {
+ $columns = [ ];
+ foreach $column (sort keys %$row) {
+ push(@$columns, $column) if (defined
$column_def->{$column});
+ }
+ }
+ $hash_rows = $rows;
+ $rows = [];
+ foreach $hash_row (@$hash_rows) {
+ $row = [ @[EMAIL PROTECTED] ];
+ push(@$rows, $row);
+ }
+ }
+
+ # find the indexes for each of the columns
+ my (%colidx);
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $colidx{$columns->[$i]} = $i;
+ }
+
+ # get the indexes for the summary keys
+ my (@summary_key_idx, @summary_key_values);
+ if ($summary_keys) {
+ for ($i = 0; $i <= $#$summary_keys; $i++) {
+ $colidx = $colidx{$summary_keys->[$i]};
+ push(@summary_key_idx, $colidx) if (defined $colidx);
+ }
+ }
+
+ # determine which columns should be summable and which have expressions
+ my $sum_column_idx = [];
+ my $expr_column_idx = [];
+ my $contains_expr = 0;
+ $row = $rows->[0];
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $column = $columns->[$i];
+ $value = $row->[$i];
+ if ($column_def->{$column}{expr}) {
+ push(@$expr_column_idx, $i);
+ $contains_expr = 1;
+ }
+ elsif ($column_def->{$column}{is_key}) {
+ # do nothing
+ }
+ elsif (defined $value && $value =~ /^-?[0-9\.]+$/) {
+ push(@$sum_column_idx, $i);
+ }
+ }
+
+ # accumulate the sums of the summable columns
+ for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
+ $row = $rows->[$rowidx];
+ $key = ($#summary_key_idx > -1) ? join("|", @[EMAIL PROTECTED]) :
"";
+ $summary_row = $summary_row{$key};
+ if (!$summary_row) {
+ $summary_row = [];
+ if ($#summary_key_idx > -1) {
+ foreach $i (@summary_key_idx) {
+ $summary_row->[$i] = $row->[$i];
+ }
}
- else {
- $total{"sum($column)"} = ($elem+0);
+ foreach $i (@$sum_column_idx) {
+ $summary_row->[$i] = 0;
}
- $total{"$column"} = $elem;
+ $summary_row{$key} = $summary_row;
+ push(@summary_key_values, $key);
+ }
+ foreach $i (@$sum_column_idx) {
+ $summary_row->[$i] += $row->[$i];
}
- }
- if (defined $total{"count(*)"}) {
- $total{"count(*)"} ++;
- }
- else {
- $total{"count(*)"} = 1;
}
- # look ahead to see if we need to produce a summary row yet
- $create_summary = 0;
- if ($rowidx == $#$rows) {
- $create_summary = 1;
+ # put the summarized rows in the results array
+ foreach $key (@summary_key_values) {
+ push(@summary_rows, $summary_row{$key});
}
- else {
- $nextkey = "Total";
- $nextkey = join(",", @{$rows->[EMAIL PROTECTED])
- if (defined $summcolidx);
- $create_summary = 1 if ($nextkey ne $key);
- }
-
- if ($create_summary) {
- $summary_row = [];
+ # evaluate the expressions of the summarized rows (if they exist)
+ if ($contains_expr) {
+ my $params = {};
+ $self->evaluate_expressions($table, $params, $columns, [EMAIL
PROTECTED], $options);
+ }
- for ($colidx = 0; $colidx < $numcols; $colidx++) {
- $column = $columns->[$colidx];
- $formula = $formulas->{$column};
-
- $elem = "";
- if (defined $formula) { # match {
- $formula =~ s/\{([^\}]+)\}/\$total{"$1"}/g;
- $formula = "\$elem = $formula;";
- eval $formula;
- #$elem = "[$formula] $@" if ($@);
- }
- else {
- $elem = $total{"sum($column)"};
+ # if we started out summarizing HASH rows, convert back from ARRAY to
HASH
+ if ($row_type eq "HASH") {
+ $rows = [ @summary_rows ];
+ $hash_rows = [];
+ @summary_rows = ();
+ foreach $row (@$rows) {
+ $hash_row = {};
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $hash_row->{$columns->[$i]} = $row->[$i];
}
+ push(@summary_rows, $hash_row);
}
-
- push (@summary_rows, $summary_row);
}
}
+
&App::sub_exit([EMAIL PROTECTED]) if ($App::trace);
- [EMAIL PROTECTED];
+ return([EMAIL PROTECTED]);
}
#############################################################################
@@ -3550,3 +3631,60 @@
1;
+__END__
+
+ if (0) { # HASH (or object of some type)
+ # determine which columns should be summable and which have expressions
+ my $agg_columns = [];
+ my $sum_columns = [];
+ my $expr_columns = [];
+ my $contains_expr = 0;
+ my (@summary_keys);
+ $row = $rows->[0];
+ foreach $column (keys %$row) {
+ $value = $row->{$column};
+ if ($column_def->{$column}{expr}) {
+ push(@$agg_columns, $column);
+ push(@$expr_columns, $column);
+ $contains_expr = 1;
+ }
+ elsif ($column_def->{$column}{is_key}) {
+ # do nothing
+ }
+ elsif (defined $value && $value =~ /^-?[0-9\.]+$/) {
+ push(@$agg_columns, $column);
+ push(@$sum_columns, $column);
+ }
+ }
+ # accumulate the sums of the summable columns
+ for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
+ $row = $rows->[$rowidx];
+ $key = ($#$summary_keys > -1) ? join("|", @[EMAIL PROTECTED]) : "";
+ $summary_row = $summary_row{$key};
+ if (!$summary_row) {
+ $summary_row = {};
+ if ($#$summary_keys > -1) {
+ foreach $column (@$summary_keys) {
+ $summary_row->{$column} = $row->{$column};
+ }
+ foreach $column (@$sum_columns) {
+ $summary_row->{$column} = 0;
+ }
+ }
+ $summary_row{$key} = $summary_row;
+ push(@summary_keys, $key);
+ }
+ foreach $column (@$sum_columns) {
+ $summary_row->{$column} += $row->{$column};
+ }
+ }
+ # put the summarized rows in the results array
+ foreach $key (@summary_keys) {
+ push(@summary_rows, $summary_row{$key});
+ }
+ # evaluate the expressions of the summarized rows (if they exist)
+ if ($contains_expr) {
+ my $params = {};
+ $self->evaluate_expressions($table, $params, $columns, [EMAIL
PROTECTED], $options);
+ }
+ }
