Thread: [htmltmpl] Nested loop puzzle - variable columns and rows
Brought to you by:
samtregar
From: LDT <per...@ya...> - 2004-02-11 13:08:43
|
I'm a little stumped on how best to approach a particular problem. I have a report I'm building that will span an uncertain number of columns and uncertain number of rows. Below is a sample of what the report might look like: Account Descr Jan Feb Jun Aug Nov Total 51112 Fred $5 $10 $0 $15 $0 $30 51119 Lucy $0 $0 $7 $0 $0 $7 51121 Ethel $0 $20 $0 $0 $3 $23 51178 Ricky $1 $6 $3 $2 $4 $16 The data I pull from the database comes into a result set that looks like this: Account Month Amount 51112 1 5 51112 2 10 51112 7 15 51119 6 7 51121 2 20 51121 11 3 51178 1 1 51178 2 6 51178 6 3 51178 8 2 51178 11 4 So, in effect, only accounts that have amounts in them for any given month will appear in my data set. The question is: how do I create my hash references to push into my loop in the correct order so that when I loop through my template, it all appears in the correct column? I've been able to successfully loop through a structure that has fixed columns and fixed rows, but this one is puzzling me and I don't know where to start. The best I've come up with so far is to get the last account and its amount to appear in the first two columns, repeated 12 times (definitely not what I want!) by looping through each month that I might have (1-12). I looked at several examples of nested loops, but I don't seem to be able to figure out what gets nested where. On the other hand, maybe I need to get my result set to look different?? I don't know SQL that well, but we have folks here who can help me if I need to adjust what the result set looks like. Thanks for any insights and/or examples you can provide. Lori P.S. I was using the model below for nested loops that I found on the web, but I still don't understand what's the @outer_loop_list and what's supposed to be in the @inner_loop_list. The Perl stuff: my @l1; foreach my $outer ( @outer_loop_list ) { my %r1=(outer => $outer); # add more outer-loop stuff here in %r1 my @l2; foreach my $inner ( @inner_loop_list) { my %r2=(inner => $inner); # add more inner-loop stuff here in %r2 push @l2,\%r2; } $r1{innerloop}=\@l2; push @l1,\%r1; } $template->param(outerloop => \@l1); The .tmpl file: <tmpl_loop name=outerloop> <tr><td><tmpl_var name=outer></td> <tmpl_loop name=innerloop> <td><tmpl_var name=inner></td> </tmpl_loop> </tr> </tmpl_loop> --------------------------------- Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online |
From: Roger B. W. <ro...@fi...> - 2004-02-11 13:28:52
|
On Wed, Feb 11, 2004 at 05:08:04AM -0800, LDT wrote: >The question is: how do I create my hash references to push into my loop in the correct order so that when I loop through my template, it all appears in the correct column? I've been able to successfully loop through a structure that has fixed columns and fixed rows, but this one is puzzling me and I don't know where to start. My approach would be to remodel the data into a fixed format, if there are few enough data that you can get away with holding them in memory twice (which for an HTML page you probably can). Also, while I used to use the named hash variable method that's in the example (%r1, %r2, etc.), these days I tend to use an explicitly anonymous hash because I find it more intuitive. So here's some (untested) code which should do what I think you want: my %data; my %account; my %month; # do database query here while (my $account,$description,$month,$amount)=$sth->fetchrow_array) { $data{$account}{$month}=$amount; $account{$account}=$description; $month{$month}=1; # gives us a list of "active" months } my @months=sort {$a <=> $b} keys %month; # or just my @months=(1..12); if you prefer my @accounts=sort {$a <=> $b} keys %account; my @ol; foreach my $account (@accounts) { my @il; my $total=0; foreach my $month (@months) { # use an empty space if no data are available push @il,{amount => $data{$account}{$month} || ''}; $total+=$data{$account}{$month}; } push @ol,{account => $account, description => $account{$account}, total => $total, month => \@il} } $tmpl->param(accounts => \@ol); which would go with a template something like: <tmpl_loop name=accounts> <tr><td><tmpl_var name=account></td> <td><tmpl_var name=description></td> <tmpl_loop name=month> <td>$<tmpl_var name=amount></td></tr> </tmpl_loop> <td>$<tmpl_var name=total></td></tr> </tmpl_loop> You'd generate the table headers separately (perhaps based on the content of @months), and probably use sprintf() for formatting the numbers, but I think that's the core of what you're after. Hope this helps, Roger |
From: Puneet K. <pk...@ei...> - 2004-02-11 13:56:50
|
On Feb 11, 2004, at 7:08 AM, LDT wrote: > I'm a little stumped on how best to approach a particular problem.=A0 = I=20 > have a report I'm building that will span an uncertain number of=20 > columns and uncertain number of rows.=A0 Below is a sample of what the=20= > report might look like: > > > Account=A0=A0 Descr=A0=A0 Jan=A0=A0 Feb=A0=A0 Jun=A0=A0 Aug=A0=A0 = Nov=A0=A0=A0=A0 Total > 51112=A0=A0=A0=A0 Fred=A0=A0=A0=A0 $5=A0=A0 $10=A0=A0=A0 $0=A0=A0 = $15=A0=A0=A0 $0=A0=A0=A0=A0=A0=A0 $30 > 51119=A0=A0=A0=A0 Lucy=A0=A0=A0=A0 $0=A0=A0=A0 $0=A0=A0=A0 $7=A0=A0=A0 = $0=A0=A0=A0 $0=A0=A0=A0=A0=A0=A0=A0 $7 > 51121=A0=A0=A0=A0 Ethel=A0=A0=A0 $0=A0=A0 $20=A0=A0=A0 $0=A0=A0=A0 = $0=A0=A0=A0 $3=A0=A0=A0=A0=A0=A0 $23 > 51178=A0=A0=A0=A0 Ricky=A0=A0=A0 $1=A0=A0=A0 $6=A0=A0=A0 $3=A0=A0=A0 = $2=A0=A0=A0 $4=A0=A0=A0=A0=A0=A0 $16 > > The data I pull from the database comes into a result set that looks=20= > like this: > > Account=A0=A0 Month=A0=A0=A0=A0 Amount > 51112=A0=A0=A0=A0=A0=A0=A0 1=A0=A0=A0=A0=A0=A0=A0=A0=A0 5 > 51112=A0=A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0=A0=A0 10 > 51112=A0=A0=A0=A0=A0=A0=A0 7=A0=A0=A0=A0=A0=A0=A0=A0 15 > 51119=A0=A0=A0=A0=A0=A0=A0 6=A0=A0=A0=A0=A0=A0=A0=A0=A0 7 > 51121=A0=A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0=A0=A0 20 > 51121=A0=A0=A0=A0=A0=A0 11=A0=A0=A0=A0=A0 =A0=A0=A0=A03 > .. Look at the following thread=20 http://www.perlmonks.org/index.pl?node_id=3D324008 I have a complete, working code snippet there for normalizing a=20 denormalized recordset. You can use that as the base to build your own=20= data transformation routine. |