Smarty Forum Index Smarty
The discussions here are for Smarty, a template engine for the PHP programming language.
Banded Report Generator
Goto page Previous  1, 2, 3, 4, 5, 6, 7  Next
 
Post new topic   Reply to topic    Smarty Forum Index -> Plugins
View previous topic :: View next topic  
Author Message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Mon Apr 24, 2006 8:56 pm    Post subject: Reply with quote

Okay, I feel like a bit of a fool here, but I had a question that may or maybe not be related to one of the previous ones above, or maybe there's a fix already that I'm just not seeing.

I've got a report I'm generating where I've got repeating information. The database structure is something like this: I have Payouts (X amount of dollars). Each Payout can have one or more people receiving a Commission (Y amount of dollars) from the Payout. Payouts and Commissions are kept in two different tables. It's roughly a tree design with the commissions branching off from Payouts.

What this boils down to is that when I do a report listing Commissions, and I want to show the Payout amounts to the side, the Payout totals at the bottom are completely wrong.

The reason is obvious, the BRG is readding the same total over and over again.

In bad ASCII art, it looks something like this:

Payouts ID = Left column
Payout Amount = Middle
Commission Amount = Right

-------------------
1 | $100 | $5 |
1 | $100 | $3 |
2 | $200 | $2 |
-----------------
n/a | $400 | $10

The Commission total is correct, because none of the Commissions repeat. The Payout amounts on the other hand, should be $300, not $400.

Am I not using the Report Generator correctly or is this just a missing "feature"?

If it's not implemented, then I was thinking I might try to hack in another array where I can bind the amounts to a unique ID and have it check to make sure that value hasn't already been added once before on each iteration. In other words, have it remember which database Payout IDs have been added already.

Does anyone have any opinions on that? Is this a good solution or a one off hack that would only benefit me and no one else? Also, even if it is a good one, not sure where I should tie the ID/Payout together, in the template or in the logic?

Have to say, aside from this one thing, this code has been super handy. Thanks a ton.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Mon Apr 24, 2006 9:23 pm    Post subject: Payouts database normalization Reply with quote

It's never foolish to ask...

It's a deeper problem... You need to normalize your data structure. The BRG will not detect deficiencies in the database design. When your database is properly normalized you'll have a much better time writing 'reports.' That's why you are stuck on this because it is not a limit of the tool it is a limit of the present data structure.

You've got One payoutID that carries two logically distinct sets of information:

The Payout

and

The Commission

ID #1 shows that two commissions were generated from the same amount. Repeated/redundant information between two records is always a clue that your database needs normalizing. The payout amount shows as $100 2x because it is from one payout (ID #1). So, your commissions were different (presumably generated for two different salespeople) but the payout 'transaction' was the same one (ID #1). But, when you present this dataset to the BRG it happily chunks through it and adds the second listing (the redundant $100) a second time.

I suggest you re-build your database so that it has a payout table and a commission table. Each table needs its own primary key and then you need to add a join table to combine them.

It looks to me like the commission does not always correspond to the payout (ID #1) so in order to properly track that (and therefore to be able to use the BRG without hacking it - and let me tell you, you'll learn alot from hacking it, but it may be painful), you have to maintain a payout table AND a commissions table each wth their separate IDs. Then, you need a join table that brings them together, something like 'transaction' that simply pairs payout id and commission id in one record.

Then, you can use the BRG on the output of that new join table because you'll have the proper data structure, that is, you'll get to 'group' by payout id and commission id and your totals will be accurate.

each
(salesman -id)
may have many
commission -id
which has only one
transaction -id linked to it
which also only has one
payout -id linked to it

HTH. I'm sure boots will let me know if I've made any errors in explaining this!
Back to top
View user's profile Send private message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Mon Apr 24, 2006 11:59 pm    Post subject: Reply with quote

Ahh, as my mother used to say: "There are no stupid questions, only stupid questioners." ~_^

Soph, I think you might be misunderstanding my problem, I probably didn't explain myself very well. My apologies.

My database system DOES have a payout table AND a seperate commission table. The fact that the payout id and amount is showing to the left isn't a function of the database design, it's that management wants to see what the total payout was as well as their cut on the same screen. (There's actually other information on the report, like their percentage, the salesman names etc etc). I could query just the commissions or just the payouts and get the correct totals, but it wouldn't have all the data the users want to see.

I'm already using a join to display this data, the database is already fairly well normalized.

The problem, like I said, isn't displaying the data. It's the totals. To break it down into a logical problem format:

1)The report shows every commission.
2) Management wants the payout amount next to the commission.
3) Because I'm listing the payout amount with every commission and a payout can have a theoretically infinite amount of commissions, some commissions get counted twice.
4) Because I need to display some payroll amounts more then once, I need a way to keep the payroll additions unique. Enter my possible solution.

I mean, I could always total the payrolls up outside of the BRG and display that total... But the BRG is so convenient for everything else, it'd be such a hassle to do both.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Tue Apr 25, 2006 5:45 pm    Post subject: normalized data Reply with quote

As Forrest Gump said: Mama always had a way of explaining things so I could understand them.

You say you've got normalized data in your tables that you then de-normalize and send through the BRG...

Ok, I think I understand the issue now. Idea

The BRG is simple. The BRG won't un-de-normalize the data. It really only does the equivalent of SQL's ORDER BY, GROUP BY and COUNT, SUM, AVG. (Though, one cool thing is that it can spit out running counts, averages or sums.) It could also easily support MIN and MAX at the group and/or top level but it doesn't currently.

Can you set up your data as a flat recordset in a way that would allow you to subtotal those values? Here are three approaches I thought of:

1) I'm not so versed in the actual SQL implementation but there's got to be a way to prepare a flat rescordset with null values... maybe an outer join?

2) Or, you could just preprocess the data with your own simple function that targets a column for nullification on repeated values.

foreach array as value
if value changed, keep value
else replace value with null
store value
end foreach

3) You might also try nesting the report tags and pass the flat payout results to the top level and an array of commissions arrays to the nested 'report' tagset that are sorted to the payout ID order. I did some testing with nesting and found that smarty doesn't throw an error so it is theoretically possible.

Think null values in flat recordset. BRG monster like flat recordset.
Back to top
View user's profile Send private message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Tue Apr 25, 2006 7:57 pm    Post subject: Reply with quote

And her other favorite quote: "Untested code is like a box of chocolates, ya never know whatcha gonna get." ~_^

If I'm understanding your suggestions right, you're suggesting I blank out the values so they don't get repeated in the totals? Won't they then not show up in every row? Because having them show up more then once is the desired behavior. I agree that I'm essentially outputting a denormalized dataset but hey...that's what the user wants.

I suppose I could do some fancy Group By stuff to get rows that contain subtotals mixed into the recordset but it just seems like it would cludge up the SQL query and would make it harder to keep the query nimble in resorting options and such.

Now, I am generating a flat recordset and passing those on through some regular ol' SQL table joins. Your solution 2 is basically what I was suggesting, except building it into the BRG itself: Being able to say "This column is linked to this other column (or to itself). If this unique identifier has already been stored, don't add it to the subtotals."

if !in_array(ThisRowID, ProcessedIDs)
{
AddToTotals
ProcessedIDs[] = ThisRowID
}
else
{
DoNothing
}

I took a look at the actual code the other day, maybe I'll hack out a quick patch in the next couple days and come back with that.

By the way, I appreciate all the feedback so far, Soph. Thanks!

EDIT: Maybe I misunderstood you slightly. Are you saying I could duplicate a column that only showed each PayOut amount once and then total those invisibly, using it for my grand total while at the same time keeping the current PayOut column the same but NOT using it for the totals? That's kind of awkward, but it miiight work. I'll play around with that solution too. Thanks again.
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Thu Apr 27, 2006 2:14 am    Post subject: Reply with quote

Hey guys -- sorry I haven't been around to follow this new development in the thread (personal family issue). I *still* don't have time to follow-up properly but as I said previously, this project is moving up on my to-do list and I do have some extensive upgrades in the works. I very much appreciate the feedback and the ideas and code suggestions that are made so please continue to post them when you can.

Oh, and much thanks to soph for picking up the ball in my absence!
Back to top
View user's profile Send private message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Fri May 19, 2006 5:34 pm    Post subject: Whew, long time, no see. Reply with quote

Sorry for the long delay, I got pulled off the accounting project for a bit to work on some other stuff.

Anyways, I played around with some of Soph's suggestions a bit, but I couldn't get the SQL the way I wanted and it made it kludgy for tacking on extra options. I also was loathe to add a preprocessor since I used them on the last version of this and didn't care much for it. So, in the end, I was a bum and hacked out my own patch to the BRG earlier this week.

Warning: This patch is a real hack and is of use to probably no one else on the face of the earth. It's quite possibly full of dangerous bugs that will wipe your hard drive and send your bank info to 419 scammers. I've personally seen it eat puppies and kick babies. The only thing I promise in the way of support is my assurance that it works for me.

It basically adds two features.

The first is Unique Columns. Basically, I can pass an array to the BRG and it will give the behavior I described above: Checking the value of one column against a list of previously used values to determine if it should add this row to the total of another column. Sorry if that makes no sense. ~_^

You define the columns with an array. The key is the column you want to be unique and the value is the column you want it to match against. I haven't tested it heavily, but I think in theory you could have any number of columns, and you could have more then one column check against the same one (i.e. I could have PayPercent use PayID to stay unique as well). It also throws an error if a mentioned column doesn't exist in the recordset.
PHP:
Code:
$smarty->assign('UniqueColumns', array('PayAmount' => 'PayID'));

Template:
Code:
{report recordset=$Data record=rec groups="PeopleGettingPaidOrWhatever" uniquecols=$UniqueColumns}


And that's really about it. It'll build arrays of all the IDs it's already added. I've tried to make it only build those arrays on columns you want unique so as to not make it hog anymore memory then it already does.

The second feature I added is just more stats. Soph mentioned Max and Min and I also added Range (rng). It tracks all those at the Total and Group levels. I used abs() on range to make sure it always spits out a positive number in case some of your values in that column are negative (in which case, I hope your finances go up soon! ~_^)

So, yeah. I doubt anyone else needs or wants this feature and the couple of extra checks would probably slow down performance for everybody else, so I don't think it's really worth putting in the real BRG. The Min/Max/Rng stuff you can pretty easily C&P out if you want that, I guess, although you can probably write better ones. I hacked this out earlier this week, it's worked fine so far, but there's probably a couple bugs left lurking somewhere.

But the code's LGPL, so releasing my changes, may they help some other poor sap saddled with a job similar to mine. ~_^

Code:

53a54,74
>     if (array_key_exists('uniquecols', $params)) {
>         if(!is_array($params['uniquecols'])) {
>            $params['uniquecols'] = array();
>         }
>         else {
>            foreach($params['uniquecols'] as $column => $id_column)
>            {
>               if(!array_key_exists($column, $params['recordset'][0])) {
>                  $smarty->trigger_error("{report}: unique column \"".$column."\" not in recordset.", E_USER_ERROR);
>               }
>               
>               if(!array_key_exists($column, $params['recordset'][0])) {
>                  $smarty->trigger_error("{report}: unique column id \"".$id_column."\" not in recordset.", E_USER_ERROR);
>               }
>            }
>         }                   
>     }
>     else {
>        $params['uniquecols'] = array();
>     }
>
62c83,85
<
---
>     , 'ids'         => array()
>     , 'uniquecols'  => $params['uniquecols']
>     
105d127
<
114a137,144
>             $params['stats']['min'][$field] = $value;
>             $params['stats']['max'][$field] = $value;
>             $params['stats']['rng'][$field] = 0;
>         }
>         //Take the array for unique ids and repopulate it with
>         //empty arrays from the unique columns specified.
>         foreach($params['uniquecols'] as $col => $id) {
>               $params['ids'][$col] = array($record['curr'][($params['uniquecols'][$col])]);
116d145
<
124,127c153,169
<             if (is_numeric($value)) {
<                 $params['stats']['sum'][$field] += $value;
<                 ++$params['stats']['count'][$field];
<                 $params['stats']['avg'][$field] = $params['stats']['sum'][$field]/$params['stats']['count'][$field];
---
>             if(is_numeric($value)) {
>                ($value > $params['stats']['max'][$field]) ? $params['stats']['max'][$field] = $value : null;
>                ($value < $params['stats']['min'][$field]) ? $params['stats']['min'][$field] = $value : null;
>                $params['stats']['rng'][$field] = abs($params['stats']['max'][$field] - $params['stats']['min'][$field]);
>                if(array_key_exists($field, $params['uniquecols'])) {
>                   if(!in_array($record['curr'][($params['uniquecols'][$field])], $params['ids'][$field])) {
>                      $params['stats']['sum'][$field] += $value;
>                      ++$params['stats']['count'][$field];
>                      $params['stats']['avg'][$field] = $params['stats']['sum'][$field]/$params['stats']['count'][$field];
>                      $params['ids'][$field][] = $record['curr'][($params['uniquecols'][$field])];
>                   }
>                 }
>                 else {               
>                    $params['stats']['sum'][$field] += $value;
>                    ++$params['stats']['count'][$field];
>                    $params['stats']['avg'][$field] = $params['stats']['sum'][$field]/$params['stats']['count'][$field];
>                }                             
149c191,199
<                     $group[$_group]['stats']['avg'][$field] = $value;
---
>                     $group[$_group]['stats']['avg'][$field] = $value;
>                     $group[$_group]['stats']['min'][$field] = $value;
>                     $group[$_group]['stats']['max'][$field] = $value;
>                     $group[$_group]['stats']['rng'][$field] = $value;
>                     $group[$_group]['ids'] = array();   
>                 }
>                 
>                 foreach($params['uniquecols'] as $col => $id) {
>                    $group[$_group]['ids'][$col] = array($record['curr'][($params['uniquecols'][$col])]);
154,157c204,220
<                     if (is_numeric($value)) {
<                         $group[$_group]['stats']['sum'][$field] += $value;
<                         ++$group[$_group]['stats']['count'][$field];
<                         $group[$_group]['stats']['avg'][$field] = $group[$_group]['stats']['sum'][$field]/$group[$_group]['stats']['count'][$field];
---
>                     if (is_numeric($value)) {
>                        ($value > $group[$_group]['stats']['max'][$field]) ? $group[$_group]['stats']['max'][$field] = $value : null;
>                        ($value < $group[$_group]['stats']['min'][$field]) ? $group[$_group]['stats']['min'][$field] = $value : null;                       
>                        $group[$_group]['stats']['rng'][$field] = abs($group[$_group]['stats']['max'][$field] - $group[$_group]['stats']['min'][$field]);
>                         if(array_key_exists($field, $params['uniquecols'])) {
>                            if(!in_array($record['curr'][($params['uniquecols'][$field])], $group[$_group]['ids'][$field])) {
>                               $group[$_group]['stats']['sum'][$field] += $value;
>                               ++$group[$_group]['stats']['count'][$field];
>                               $group[$_group]['stats']['avg'][$field] = $group[$_group]['stats']['sum'][$field]/$group[$_group]['stats']['count'][$field];
>                               $group[$_group]['ids'][$col][] = $record['curr'][($params['uniquecols'][$field])];
>                            }               
>                         }
>                         else {
>                            $group[$_group]['stats']['sum'][$field] += $value;
>                            ++$group[$_group]['stats']['count'][$field];
>                            $group[$_group]['stats']['avg'][$field] = $group[$_group]['stats']['sum'][$field]/$group[$_group]['stats']['count'][$field];
>                         }



Last edited by Ross on Fri May 19, 2006 10:39 pm; edited 2 times in total
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Fri May 19, 2006 9:14 pm    Post subject: Reply with quote

Hi Ross.

Can you email me the patched version?

tia.
Back to top
View user's profile Send private message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Fri May 19, 2006 10:37 pm    Post subject: Reply with quote

Hey boots,
Emailed it to the yahoo address listed in the plugin. If that address is no longer valid let me know and I'll resend it.

Thanks again to you and Soph.

-R
Back to top
View user's profile Send private message
netnamues
Smarty Rookie


Joined: 29 Dec 2004
Posts: 30

PostPosted: Fri Aug 04, 2006 8:09 pm    Post subject: Reply with quote

Is there a download file of this plugin available?

Thanks
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Fri Aug 04, 2006 9:28 pm    Post subject: Download BRG Plugin at the wiki Reply with quote

http://smarty.incutio.com/?page=BandedReportGenerator

This is a copy/paste job, but there are nice hints telling you where to put everything.

cheers.
Back to top
View user's profile Send private message
douwe
Smarty n00b


Joined: 13 Oct 2006
Posts: 2

PostPosted: Fri Oct 13, 2006 9:09 am    Post subject: Report_footers not processed Reply with quote

Hello,

Until now I'm a happy user of this plugin, but I bumped into what I think is a bug. I'll try to explain with this sample data:
Code:
 a  b  c  d  e
-- -- -- -- --
 1  2  3  4 10
 1 12  3  4 15
 1 22  3  4 25

The template I use has groups a, b, c and d. As you can see only the value for group b (second level) is changing. The problem is that in this case the report_footer for level d is not processed. It is only processed at the end and $sum.e then contains 50, which is the sum of all records.

It seems to me that if a change only occurs on one of the higher levels only one level deeper is processed. I confirmd this by changing only the first level (column a), in which case neither report_footer c nor report_footer d is processed (except at the end). I think all lower levels should be processed when there is a change on a higher level.

Is there anybody who run into this problem too. Did anybody solve this?

Who can give me a hint where in the source-code I should look? I'm using version 0.1.6. For testing I changed line 170 in block.report.php from
Code:
 $group[$_group]['last'] = false;

to
Code:
 $group[$_group]['last'] = true;


This causes all footers to be processed after every record, but stil $sum isn't reset then. So if I manage to set $group[$_group]['last'] to the right value I still need to change some code somewhere else.

I really hope someone can help me.

With kindly regards,

Douwe.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Fri Oct 13, 2006 1:06 pm    Post subject: yes, it is a bug Reply with quote

this is a known bug. there is some discussion of this in previous posts.

as you discovered through your admirable testing, there is support for one level group above, but no further.

the change to source code would involve maintaining some kind of buffer with records of 'change' in data and where that change occurred all the way up multiple levels. but, you probably knew that already since you seem to be pretty capable Very Happy .

this is a pretty serious bug so it would be great if you want to dig into it. but, please contact boots since he may have some other items in the works for this code.

cheers!
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Fri Oct 13, 2006 10:00 pm    Post subject: Reply with quote

Sophistry is correct: this is a serious bug and I do have alternate code in the works that addresses this and many of the other points that have been discussed in this thread.

In honesty, I started reworking the code many-many-months ago and have had to move away from it midstream -- so it is not near release ready. Unfortunately, it is not BC with the current code. Also, it is written using PHP5 only semantics (and no PHP4 backport is planned) and is a major rewrite with API changes that are incomplete. Long-story short, I am not at all comfortable releasing the next version at this point -- and I don't know exactly when I will return to it. I can say that I am hoping to do so this quarter, but no promises are given on that.

On the plus side, the refreshed code separates the array processing from the Smarty processing as a stand-alone recordset class. This should allow for more advanced sorting and tabulation of standard arrays directly in PHP and the class also provides several PH5 conveniences. Further, the Smarty plugins have been further generalized and abstracted as-well. The aim for that is to provide a reusable machinary to quickly implement other types of specialized block plugins.

SO....while I do intend to ultimately release the refreshed code, if someone is willing and capable of patching the current version at the wiki -- you have my blessing...and my thanks! Smile

Also, many thanks to the many folks who have contributed comments, suggestions and code in this thread! Free Software rocks!
Back to top
View user's profile Send private message
douwe
Smarty n00b


Joined: 13 Oct 2006
Posts: 2

PostPosted: Mon Oct 16, 2006 8:24 pm    Post subject: Reply with quote

Hi boots, sophistry and everybody else.

I made some changes in block.report.php.

Here is a part of the code of function smarty_block_report__process_next. The changes I made are on line 4 and on line 25-28. They are surrounded by /* ADD begin */ and /* ADD end */.

The idea is that if a higher level group is changed you can also flag the lower groups as being changed. So, if the record is marked as 'first' or 'last' for the higher group, it is also the 'first' or 'last' on the lower group levels.

Code:

 1  // process grouping levels
 2  foreach ($params['groups'] as $_group) {
 3      if (!is_null($record['curr'])) {
 4          if ($record['prev'][$_group] != $record['curr'][$_group] OR ($record['prev'][$prev_group] != $record['curr'][$prev_group]) /* ADD start */ OR $group[$prev_group]['first'] == true /* ADD end */ ) {
 5              $group[$_group]['first'] = true;
 6              foreach ($record['curr'] as $field=>$value) {
 7                  $group[$_group]['stats']['sum'][$field] = $value;
 8                  $group[$_group]['stats']['count'][$field] = 1;
 9                  $group[$_group]['stats']['avg'][$field] = $value;
10              }
11          } else {
12              $group[$_group]['first'] = false;
13              foreach ($record['curr'] as $field=>$value) {
14                  if (is_numeric($value)) {
15                      $group[$_group]['stats']['sum'][$field] += $value;
16                      ++$group[$_group]['stats']['count'][$field];
17                      $group[$_group]['stats']['avg'][$field] = $group[$_group]['stats']['sum'][$field]/$group[$_group]['stats']['count'][$field];
18                   }
19              }
20          }
21
22
23          if ($record['last']) {
24              $group[$_group]['last'] = true;
25          /* ADD start */
26          } else if ( $group[$prev_group]['last'] == true ) {
27             $group[$_group]['last'] = true;
28          /* ADD end */
29          } else if (($record['curr'][$_group] == $record['next'][$_group]) AND ($record['curr'][$prev_group] != $record['next'][$prev_group])) {
30             $group[$_group]['last'] = true;
31          } else if (($record['curr'][$_group] != $record['next'][$_group])) {
32              $group[$_group]['last'] = true;
33          } else {
34              $group[$_group]['last'] = false;
35          }
36      } else {
37          $group[$_group]['first'] = is_null($record['prev']);
38          $group[$_group]['last'] = is_null($record['next']);
39      }


My first impression is that these changes solve my problem, but I might have overlooked something. Could you please have a look at it?

With kindly regards,

Douwe.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Smarty Forum Index -> Plugins All times are GMT
Goto page Previous  1, 2, 3, 4, 5, 6, 7  Next
Page 5 of 7

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP