as_reportool - File/program description |
 |
|
|
|
 Back to the listas_reportool: Report building PHP classThis is another PHP class for building HTML report pages from SQL data.
You just set the main SQL query, list of fields to be printed, and list of grouping fields,
if You need sub-totals. All these data can be added by calling respective class methods
or loaded from XML file (if your PHP is 5.x and simpleXML support is on).
Installation is standard - just copy as_reportool.php and as_dbutils.php class files to your site's folder, and add "include(), or require_once() for them to your php script file. Using as_reportool library
require_once('as_reportool.php');
//...
$rep = new CReporTool();
First: You set an SQL query that returns all the data for report.
Method SetQuery() is called to do this.
Next, You'll add all "fields" names that should be printed, in desired order, by AddField() method. Remember, these names must correspond with some field or expression's name in datarow. For example, if your query contains something like "SELECT ... (field1+field2) `field1_field2`", to print this computed value in report, use "field1_field2" name: $rep->AddField('field1_field2','F1 and F2 sum');
If You need some sub-totals (your query should return right ordered records in that case !), use a method AddGroupingField() to set "grouping" fields. Subtotals can be nested with no limit - the first field passed with AddGroupingField will be the "upper level", the next one will be the lower. During getting data rows (and printing report) loop, when some "grouping" field value changes, all subtotals will be printed, beginning from the "lowest" level and up to this field's level in totals hierarchy. Generated report is HTML code that looks adequate not only in the browser, but in the hard-printed page too. CSS styles used for this formatting are built-in into the class, but You can override them by calling SuppressCss() method. In this case "CSS style" block is not printed, so it's your responsibility to include all necessary styles somewhere in Your "style area". They are: td.rep_ltrb, td.rep_lrb, td.rep_rb, td.num, td.cnt, td.newgroup. CReporTool class methods listIf You plan using XML files, make sure Your hosting provider enables PHP5 with simpleXML support, because as_reportool uses simpleXML for XML files parsing ! Here is XML file example <?xml version="1.0" encoding="UTF-8"?> <report_def version="1.00"> <query>SELECT c.categoryid, b.animalid, a.nickname,a.gender,a.birth, a.weight FROM big_zoo a, animals b, animal_categories c WHERE a.animalid=b.animalid AND b.category=c.categoryid ORDER BY c.categoryid, b.animalid </query> <headings><![CDATA[ <tr><td colspan="8" class="rep_ltrb">This is additional headings to print before default columns headings</td></tr> ]]></headings> <nodefaultheadings value="1" /> <summary title="Summary totals (%rowcount%)" /> <delimiters decimal="," thousand=" " /> <grpfield name="categoryid" fconv="GetAnymalCategoryName" title="Animal category " totaltitle="Totals for category" /> <grpfield name="animalid" fconv="GetAnymalClassName" title="Animal class" totaltitle="Totals for class" /> <field name="nickname" title="Nick" fconv="" format="" /> <field name="gender" title="Gender" fconv="DecodeGender" /> <field name="weight" title="Weight, kg" summable="1" format="i" /> </report_def> query tag sets SQL query exactly as SetQuery() method does it. headings tag sets additional headings, like the method SetHeadings() does; nodefaultheadings with value="1" hides generated column headings, like SetHeadings($new_headings,1); grpfield tags is equal to calling AddGroupingField(), field tag used for AddField(); summary tag used for SetSummary(); delimiters tag is uqual to calling SetNumberDelimiters($decimal,$thousand).
If You want sofisticated headings in your report, call method CReporTool::SetHeadings(). First parameter must be an HTML code that contains some table rows, starting with <tr> and with ending </tr>. This code will be echoed right after beginning TABLE tag, and before "default" heading row generated by class. If You have a FULL HTML code for the headings and don't want generated titles row, pass a second non-empty parameter $no_defaultheader, so it turns off default headings generation. $rep->SetFontStyles("font-family: Verdana; font-size:3mm;");
Making report example.OK. Now let's describe some example data tables that we'll use to show reporting functionality. This is a "big zoo" database (our zoo is really big - it has a small "ocean" with oceanic animals like shark and even whale!). This DB has one table for animal categories, another one - for describing animals, and last one is a list of all animals living in the zoo. You can create these tables by running sql file included in distributive (sampledata.sql)
We want to print report for all creatures in our zoo, with subtotals for every animal class and category.
$rep = new CReporTool();
$rep->SetQuery("SELECT c.categoryid, b.animalid, a.nickname,a.gender,a.birth,a.weight
FROM big_zoo a, animals b, animal_categories c
WHERE a.animalid=b.animalid AND b.category=c.categoryid ORDER BY c.categoryid, b.animalid");
$rep->AddGroupingField('categoryid','GetAnymalCategoryName','Animal category ','Totals for category');
$rep->AddGroupingField('animalid','GetAnymalClassName','Animal class ','Totals for class');
$rep->AddField('nickname','Nick');
$rep->AddField('gender','Gender',0,'DecodeGender'); // DecodeGender() will show 'male' for 'm' and female for 'f' value.
$rep->AddField('birth','Birth date',0,'DateToChar'); // your function DateToChar converts DATE value to be more readable
$rep->AddField('weight','Weight, kg',1,'','i'); // this field is summable and will be printed right-aligned and number_format()ted
$rep->DrawReport('Report: All animals in zoo');
You may be want SQL server to build subtotals rows (using GROUP BY WITH ROLLUP option in MySQL, for example) - please don't do it, because CReporTool calculates totals by itself and cannot detect "totals" rows in returned SQL data.
As a result You should see report page like this one: Report: All animals in zoo
You can investigate included report_sample.php file as a starting point for your reports. Change log (Version history)1.00.002 (11/26/2008)First release
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Copyright © 2008-2010 selifan.ru | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
