Report Generator REPGEN

Version 2.00

3.1 Report Definition
3.2 Function Defintion

3.3 Block Definition

3.4 Items

3.4.1 String
3.4.2 Database Field

3.4.3 Block

3.4.4 Term

3.4.5 Textarea

3.4.6 Line

3.4.7 Rectangle

4.Structure of a Report

4.1 Tables
4.2 Structure of an Attribut of an Item

5. Repgen, the Generator

6. Sample

7. Functions

1. General

Repgen 2.0 is written with PHP Version 4.01 or higher and helps to generate a library of report-definitions. Repgen is designed for use with a browser in the Internet.

Repgen 2.0 creates automatic a PDF-Report from a declaration, which is stored in an SQL Table. To generate a PDF-report you need only one call of a PHP-function.

Repgen 2.0 can only be run in release 2.0 Final of FrontAccounting. It uses the new PDF Engine from TCPDF.

Repgen 2.0 can only be run if you are logged in as an administrator, The page_security is set to 15 (admins).

You can create reports printing the result set of one select statement:

a group change, with a new group value on a new page
a group change without change to a new page
a report with one page/record of the result set of the SQL Statement
a report with one line/record

This program consists of two parts:

- A part to define a report with an HTML-Interface and to store this definition in a SQL-table called 'reports'.
- A second part, to create a PDF-File out of the stored definition.

A report is completely stored in the table xx_'reports' and you can therefore print a PDF-File without any manual ineraction. The PDF-File is created in memory and a file named {id}.pdf is created in the /reporting/pdf_files/ folder and finally sent to your web-browser, where your Acrobat Reader - plugin can store or print the record. To create the PDF-File a function called 'create_page($id, $file)' is run in the backgroup (repgen.inc). $id is the Id-number of the report-definition, $file is the filename of the report. All this is done automatically during processing.

The report definition ist based on the stored SQL-statement, which creates a result set. Every record of this resultset can create a whole page or can only create a line in the report. If the value in one specified database field changes, you can define a new Group Header or Group Footer. For every page there is a Page Header and a Page Foot declared. In every section of the page (Page Header, Group Header, Detail, Group Foot and Page Foot) you can define a String, a DB-Field or one of the predeclared functions with the predefined fonts and fontsizes.

2. Databases

You can use Repgen with your own company database. Nothing else is needed to do that. The only recommendation is to use a table prefix 0_ (zero, underscore) in front of all tables from the companies. This way REPGEN will replace this prefix with the correspondent table prefix for the company you are operating on. Should there be no prefix it will be replaced by an empty stirng.

3. Elements

A report-definition is defined with some elements, which are stored in the table 'reports' in the database.

The elements are:

Common Data of the report
Select Statement
predefined Item
Block
Function

3.1 Report Definition
All data of a report defintion is stored in the table 'reports' and has the same numeric id.
The Id of a report is created automatic. The short-name can be choosen from the user, but must be unique. You can give every report a long name, but the creation date is generated automaticly. You can choose the Paper Format(Letter = 612 x 792 points,a4 = 595 x 842,) and the Paper Orientation(Portrait, Landscape ).

With an SQL-Select Statement you can generate a set of data records for the report.

Every report consists of (at least one) paragraphs:

Report Header
Page Header
Group Header
Detail (is a must)
Group Footer
Page Footer
Report Footer

In each paragraph there is an origin of the coordinate-system at the left lower corner. The sum of the y-values of all paragraphs in a page should not be greater than the height of the page.
In the Detail paragraph the view of on data record is declared. In an report with a Page/record there is only the Detail paragraph declared. In all other types of reports there are Page Header, Detail, Page Footer or Page Header, Group Header, Detail, Groupe Footer and Page Footer declared. The Detail paragraph can use mor than one line. If you use only one line, you can declare the order of the items in the line instead of their X/Y value pairs. If you use the order for an item as declaration, only the first item can have an x-value, but no item in the line can have an Y-value. You can use constant Strings, Fields of the data record or Functions as source in all items.

With blocks you can predefine a set of items and use them in several reports in the same manner. Terms are functions in PHP with one string as return value. With Terms you have a great flexibility, but it is possible to crash the system or to use to undermine the security of the web server. So be cautious in using terms.

In reports with a page/record and only detail paragraph you can use the Item Textarea. Textarea is like term, but you declare the x-value of the left, lower position and the width of this item. The text in the textarea is displayed in this boundary with wordwrap and may go over more than one line.

There are three types of reports:

For every data record a page.
In one report for every data record a line, with or without a grid or with a bar
It is possible to declare a field in the data record as group field, with ordering the data records in the select-statement. After every change of the group field you have a group header and a group footer. You can choose, if the report with the new group value should be on a new page or continue on the same page.

If your report should be created with a group change, you can choose between the beginning of the new group on a new page or in the next line.
3.2 Term Definition
A Term is an item based on a PHP-function. The id has an 'F' as first character and the function has to be declared before the first use. A term returns a string, which is displayed on the page. The function can be of the form f() or f($el). $el is a pointer to the report($this) and can be used to get the result of internal functions. You can also use all the functions from FrontAccounting that are included.
Example Code:

Date-function: function RepDate() {return today();} will return the string '02-12-2008' depending of the date format inside FrontAccounting.
Using a select statement inside the function:

function id(){
$res = db_query("select * from xx_reports where typ = 'info'");

$f = db_fetch{$res); // read first record

return $f["id"]; //read field 'id' from first record

}

The sql-statements are in the the form of my_sql.

With $el to get the old group value after a change:

function oldgroup($el){
return $el->get_group_old();

// $this->get_group_old() is a function of the object 'report', declared in repgen.inc'.

}

The item Textarea has the same possibilities as Term.
3.3 Block Definition
A Block is a sum of items in one paragraph. You can use it, to predefine some items in a paragraph and to use it in several reports in the same way. The creation of a lot of reports for a project is mor comfortable with blocks.
The id of an block begins with a capital 'B' and is created automaticly. The short name should be unique.You may change a block after usage in some reports and the change will be effective in all this reports.

3.4 Items
3.4.1 String
The String is a constant. You can declare a string on a position in a paragraph with the X/Y values. The number of characters defines the width of the item. Number of characters may be greater than the actual length of the string. You can take parts out of the string with the parameters 'from' to 'to'. 'From' reach from 1 to the length of the string, 'to' must be greater than 'from' and lower than the the length of the string.
3.4.2 Database Field
The Database field is a field of the record as a result of the select statement. You can declare a DB Field on a position in a paragraph with the X/Y values. The number of characters should not be empty. The number of characters defines the width of the item. Number of characters may be greater than the actual length of the string. You can take parts out of the DB Field with the parameters 'from' to 'to'. 'From' reach from 1 to the length of the DB Field, 'to' must be greater than 'from' and lower than the the length of the DB Field. If the number of characters is less than the length of the Database field then the content of the Database Field is wordwrapped and printed with more lines between the x-Value and the given length.
In the Detail paragraph you can use an order of the fields in the line instead of the x/y values. With the x/y values you can define more than one line per record.

If you want to convert a database value (f.i. 0 or 1) to a more descriptive value you can use the Term function to do this: F.i.

Lets say that you have a database field value of 0 and you wish to write a value of 'No'. A value of 1 should say 'Yes' you may do a function like this:
            function YesNo($el) { if $el->f['id'] == 0) return "No"; else return "Yes"; }
If the Database Field is numeric, then you can print Total sums or Group sums, if you check the Total checkbox. The Total sums are printed in the report footer, the group sum in the group footer. The same font as in the column is used. You can influence the image of the sums with the checkboxes Bold, understrike or overstrike.

3.4.3 Block
As block is a sum of items, the x - position is irrelevant. the position is declared within the items in the block. The use of blocksin blocks (recursive use) is not implemented.
3.4.4 Term
With a Term you can display the value of an PHP-function. The term is defined as you can see in Function definition.
The result of a PHP-function is displayed at the given position in the given font and fontsize.

3.4.5 TextArea
With a Textarea you can display the value of an PHP-function. The term is defined as you can see in Function definition.
The result of a PHP-function is displayed at the given position in the given font and fontsize. Textarea can only be used in a report with page/record definition in the detail paragraph. You can give the x-position of the left upper corner and the width of an rectangle in which the result of textarea is displayed. If the result of the function is greater than one line, the next lines with the same font and fontsize are used to display. Wordwrap is used to organize the result in some lines.

3.4.63 Line
A line is drawn from X1/Y1 position to X2/Y2 in the given linewidth. If you want to wrap Report Headers, Page Header, Group Headers, Page Footers or Report Footers with lines you just give the value of 1 of all the fields.(x, y, x2/width, y2/with and width).
3.4.7 Rectangle
A rectangle is drawn with the lower left corner X1/Y1 and the given width and height int the given linewidth.

4. Structure of a Report

4.1 Tables
Table Report:
id char(10) Identification of report, block or function(block begins with 'B', function with 'F'
typ char(10) Record typ: info, select, item, block, function

attrib text Attribute of this record(The values are separated by |).

info: <short name>|<creation date>|<Author>|<long name>|<paper orientation>|<paper format>|<report type>
select: <select statement>
item: look at Structure of an attribut
block: <short name>|<creation date>|<Author>|<long name>
function: <short name>|<creation date>|<Author>|<long name>|<function definition>

4.2 Structure of an attribut of an Item
An attribut of an item is a, with | separated string.
<Itemtyp : String,DB,Term,Text,Block,Line,Rectangle>|<paragraph:PH,GH,DE,GF,PF>|<fontname>|<fontsize>|<length+justification(c,l,r)>|<x>|<y>|<value>|<order>|<decode>|<from>|<to>

5. Repgen, the Generator

The Report Generator starts with the page repgen_select.php. Here you can create new reports, create new blocks, create new functions. You can also run existing reports as well as edit or delete reports.

Report Selection

If you chose to create a new report, you will come to repgen_create.php. After selection of a report you will be guided to repgen_create.php too. In this screen you define the common properties of the report:

Edit Report

You can test the sql statement and when you are ready, then you can do the next step, the creation of String or Graphic elements:

If you click 'Page Definition', you will come to this screen:

Report Strings

Here is the definition of an item. First you have to declare the paragraph (Mode:), the font and then you may give the X/Y position or the order of the item(only in detail paragraph). In the Report Header, Page Header, Page Footer and Report Footer your first y value of the line starts with 0. Next line with a minus value (-12) and so on. Afterwards you give in, how many place for characters you would like for this item and the alignment(left, right, center). Optional you may only take a substring out of this item.

6. Functions

There are some examples of useful functions:

Current Date-function:

function RepDate() { return today(); }

Current time:

function atime() { return date("h:i:s a"); ]

Page Number:

function PageNo($it) { return $it->pdf->page; };

Old Group after changing:

function oldgroup($it){return $it->group_old;}

New Group:

function newgroup($it){return $it->group_new;}

Total number of records in the report:

function rec_count($it) {return $it->count;}

Number of Records in a group:

function subcount($it) {return $it->subcount;}