DotProject Reports Module
1. Objective
1. To provide the capability to create and store report models including virtually any information (data field) stored in the DotProject project database (projects, tasks, logs, users).
2. To provide tools to define access rights to different report depending on company, project, user.
All reports can be generated in the pdf and CSV format (Excel compatible). However, the CSV format may not be appropriate for tabular presentation reports.
2. Terminology
Report A document that displays a subset of the dP project database in accordance with directives provided through a report model
Report model A list of directives that describe the various database and formatting actions to be taken to prepare a report; these directives include the list and order of the fields to be displayed, the conditions to be used to select specific information in the database, the filters (additional conditions defined by the user before displaying the report), the general layout of the report, etc.
Field the minimum quantum of information that can be displayed in a report; a field may correspond to a database column (such as project_name, task_name, task_priority) or the result of a database operation (for instance task assignees).
Filter a specific condition to be met in order for the target database record to be displayed in the report; there are two kinds of filters:
§ static filters (named conditions) are defined in the report model (for instance: a report model for completed tasks will include the condition that the tasks to be shown are completed (i.e. taks_percent_complete = “100”);
§ dynamic filters (named filters) are set by the user before displaying a report model (i.e. select a specific project for displaying the report)
3. Module structure
The module is built on the standard dP framework. Most of it is compatible with the DotProject HEAD code, except that it does not use Smarty templates for v2.0.4 compatibility. It includes the following php files:
§ reports.class.php define the reports class including standard methods (constructor, load, delete, …) as well as specific methods to access the reports such as:
o getAllowedReportID($uid) : list of the reports available to the user,
o getTargetProjectID : list of reports that can be used to display a specific project,
o show_report : display report information,
o addReportField, getReportField, to retrieve and update report model field list,
o addReportFilter, getReportFilter, to retrieve and update report filters,
o etc.
§ index.php display the list of report models that the user can use (depending on access rights) and provide access to the create and edit functions, if allowed;
§ view.php (1) display on screen the report model parameters and the result of the report model applied to the database and (2) provide links to the generated report(s) in PDF or CSV format, if selected by the user
§ addedit.php provides access to the functions used to create or modify a given report model; it includes most of javascript functions used in other tabs
§ ae_details generates the tab used to create and to modify report definition (report title, layout, available format,…)
§ ae_fields generates the tab used to define the list and order of the fields included in the report model
§ ae_filters generate the tab used to define the filters (selection criteria) for the report model
§ ae_options generate the tab used to provide functions to define the way the fields should be sorted for display in the report and other display options
§ do_report_aed execute the report add/edit/delete function in the database
§ report_functions contains the model of the dP database and utility functions to process this model
§ vw_columnar PHP code used to generate a columnar report
§ vw_tabular PHP code used to generate a tabular report
§ selector a piece of code used to generate pop-up windows to select appropriate field values for filters and for access rights
4. Meta-model
The code is based on a meta-model of the dP project database. This meta-model and the associated functions are defined in report_functions.php.
This meta-model contains the description of each accessible fields and the description of the filters. It is designed in such a way that additional fields can be defined when DotMods are used.
A field is described by the following information
§ field name (used as index in the meta-model field table) is the field heading to be used in the reports
§ database column to be used to generate the field content; in certain circumstances (information relative to users that are stored in a different table) this field is structured as <table name>|<field name>;
§ display function name to be used to display the field content:
o if the function name is set to NULL, the database field content is displayed “as is” in the database;
o if the function matches the REGEX '\{([A-Za-z]+)\}', the field content is displayed using the SysVal array given between the curved brackets (project_type, project_status, etc.);
o otherwise, the display function is defined in report_functions.php;
§ field relative size,
§ field content alignment (left, right, center)
§ a field filter type that describes the possible search clauses:
o 0 = cannot be used as a filter
o 1 = numerical values
o 2 = text values
o 3 = flag value (i.e. milestone, log problem, etc…)
o 4 = SysVal values
o 5 = date values
o 6 = ID field;
o 7 = retrieve the values through a table of pointers (project contacts task contacts, task assignees,…) names indirection table
§ a field value descriptor that contains the name of the DB table to be used to retrieve the filter values that are displayed to the user:
o if a table of pointers is used the field structure is <indirection table name>|<field value table>,
o if a SysVal array must be used, the name of the SysVal array is given with the same format as display function;
o otherwise (filter type = 6) the descriptor contains the table name to be used to retrieve possible values.
The possible search clauses are
§ for all types except flags : equals, not equals (for SysVal values or ID field a list of values can be selected)
§ specific to numeric and date: greater than, lower than, greater or equals to, lower or equals to
§ specific to text: starts with, contains, does not contains
§ specific to flag: is set, is not set
§ specific to assignees : exist and does not exist
Future extensions (not implemented) : the field content alignment description can use the directive ‘<XX’ where XX is the max length of the displayed content in number of characters; in this case, the field content is truncated at the given length and left aligned.
A field description is stored in an array. For instance:
'Project owner' => array('project_owner', 'strusername', 285, 'left', 6, ‘users’),
'Task Name' => array('task_name', NULL, 175, 'left', 2, NULL),
'Assigned Users' => array('task_id', 'strassignees', 175, 'left', 7, ‘user_tasks|users’),
‘Project status’ => array(‘project_status’, ‘{ProjectStatus}’, 50, ‘center’, 4, ‘{project_status}’)
The field descriptions are grouped according to the database table from which they are defined. Each dP project database table is described by the following information:
§ table name (used as index in the meta-model field table)
§ identification field is the primary key of the table in the database
§ name field is the field used to show the name of the record to the user
§ join field is the database column to be used in the SQL JOIN statement to retrieve the fields generated from the table
§ join table is the name of the database table to be used in the SQL JOIN statement to retrieve the fields generated from the table
§ join key is the prefix to be used in the SQL JOIN statement
§ field list is the list of the fields description for this table (as described above)
The root of the report field description depends on the report reference selected by the user:
§ by default, the root table is the projects table, i.e. all fields are retrieved starting from the projects table contents.
§ when report on users is checked, the root table is the users table; this allows to display detailed user information (phone, company, email, ...); when this option is set, the report definition indicates how the users are linked to project information (project owner, task_owner, assignee, ...): this is called the user function.
Selection of records for display in the report is based on two concepts :
conditions are met by all selected records in the report model
filters are conditions set by the user when preparing the report
5. Report model definition
Three types of records in the database define a report model
The main record table (reports) contains the following columns
§ report_id Unique Identification (primary key)
§ report_name Report name (used in report list for selection)
§ report_creator User Id of report creator
§ report_title Title of the report (used in pdf report heading)
§ report_date Creation/last modification date
§ report_description; Description of report content and usage (free text)
§ report_type 0 = public ; 1 = restricted; 2=private; 3=admin
§ report_datefilter Date range filter name
§ report_format comma separated list of authorised formats
§ report_layout ‘0’ = table with headers; ‘1’ = 2 columns
§ report_orientation 0 = ‘landscape’ (default); 1 = ‘portrait’
§ report_sortfields comma separated list of sort fields
§ report_showoptions comma separated flags for display options
§ report_code PHP code file to be used if not view.php(1)
(1) Compatibility with existing reports - Only for admin users
The list of display options are :
§ 0 = display group names (first sort field) for columnar presentation; skip page between groups for tabular presentation,
§ 1 = display times in days instead of hours (worked hours, ...),
§ 2 = display tasks statistics (not implemented)
§ 3 = display project Gantt chart (not implemented)
Each report (except if report code is set = specific code is used to generate the report) is further defined by the list of fields to be included in the report. A record in the report_fields table defines each field:
§ report_field_report Report Id
§ report_field_table dP project database table name
§ report_field_column dP project database column name
§ report_field_name field user name
§ report_field_rank field display rank number
The report_type field describes the access control mode. There are four options:
§ report_type = 0 Public report; any user can have access to this report;
§ report_type = 1 Restricted access: access to this report is limited to a given list of companies or projects or users
§ report_type = 2 Private report : this report can be used only by its owner (creator)
§ report_type = 3 Admin report : this report can be used only by users with admin view rights
When the report access type is “restricted”, the list of projects/companies/users is described in the table report_access. Each report_access record contains the following:
§ report_access_report, Report Id
§ report_access_type, Type of access control
1 = company
2 = project
3 = user
§ report_access_id ID value to be checked for access control
Each report (except if report code is set = specific code is used to generate the report) is further defined by the list of filters to be included in the report. A record in the report_filters table defines each filter:
§ report_filter_report Report Id
§ report_filter_table dP project database table name
§ report_filter_column dP project database column name
§ report_filter_name field user name
§ report_filter_mode either fixed condition ( mode = 0 ) or a user- defined value ( mode = 1 )
§ report_filter_operator comparison operator to be used
0 = none
1 = equals to (all field except flags)
2 = not equals to (all field except flags))
3 = greater than (dates or numerics)
4 = greater than or equals to (dates or numerics)
5 = less than (dates or numerics)
6 = less than or equals to (dates or numerics)
7 = contains (text)
8 = does not contain (text)
9 = starts with (text)
10 = is set (flag)
11 = is not set (flag)
12 = equals (list of values)
13 = not equal (list of values)
14 = is defined (assignee)
15 = is not defined (assignee)
§ report_filter_value value to be checked including special values.
Report filter values can use predefined values (indicated between round brackets):
For users {USER} = current user ID
For dates {PQ} = Previous Quarter
{PM} = Previous Month
{PF} = Previous 2 weeks (fortnight)
{PW} = Previous Week
{PD} = Previous Day (yesterday)
{NOW} = current date
{ND} = Next Day (tomorrow)
{NW} = Next Week
{NF} = Next 2 weeks
{NM} = Next Month
{NQ} = Next Quarter
For companies {MYCOMPANY} = User company
For projects {MYPROJECT} = User owned project
In the report model view, a select option to specify the report file format is always displayed. If the report model does not specify any file format (either PDF or CSV or both), this select is displayed with only one option (no report file).
Any other filter is displayed only if defined in the report model:
§ date range (when the report_datefilter field is set in the report description) uses either user-defined dates (start and end) or standard periods through buttons
§ filters are displayed as
o a select array if filter type is 4 (Sysval), 5 (dates), 6 (IDs) or 7 (indirection table)
o an input field if filter type is 1 (numeric) or 2 (text)
o a check box if filter type is 3 or if a filter value is defined (in this case the filter is a condition that is applied or not depending on user choice)
6. How to include fields from Dotmods
In order to use fields from Dotmods in the report, one must describe the DotMods tables and, if required, add appropriate display functions in the filer report_functions.php. This operation is quite “technical” although, in most cases, it is relatively straightforward.
Let’s take as example, the risks module from CaseySoftware.