# INSTALLATION INSTRUCTIONS FOR ADVANCED REPORTING FOR VTIGER CRM 6

## Requirements

* vTiger CRM version 6+
* At least 100Mb should be allowed in WEB server to upload module

## Browser compatibility

* Firefox 30 +
* Google Chrome 30 +
* Internet Explorer 11 +

## Before installation

Following directories and files should be writable:

VTIGER_ROOT/modules
VTIGER_ROOT/Smarty

## Installation

1. Create backup of all your vTiger system (files and MySQL). It's better to make full backup of your vTiger system before any changes are made.

2. Log in to your vTiger system as Administrator and go to CRM Settings->Module Manager->Custom Modules->Import New.

3. Select your archived copy of Advanced Reporting module and click on button "Import".

4. License agreement step will appear. Click on "I accept the license agreement" and continue.

5. If all went good - you can use your new Advanced Reporting tool in vTiger :)

6. If you face any issues, please contact us: info@itsapiens.eu


## Third party libraries

In Advanced Reporting tool for vTiger CRM installation is included third party libraries such as:

* jQuery 1.8.3
* jQuery UI
* jQuery Select2
* jQuery Multiselect
* jQuery event drag
* saveSvgAsPng
* Ractive.js
* underscore.js
* D3.js
* NVD3.js
* SlickGrid
* Pace.js
* jsTree
* jsTree-Grid
* PHPExcel


## Translations

Advanced Reporting is delivered in English (en_us) language, to translate Advanced Reporting for your language, you should make modifications in your language files.

In your VTIGER_ROOT/modules/AdvancedReports/language/YOUR_LANGUAGE.lang.php array can be added following translations for Advanced Reporting (You can see example in VTIGER_ROOT/modules/AdvancedReports/language/en_us.lang.php):


## Support

For support, please, contact us: info@itsapiens.eu


## Adding Custom Reports to Advanced Reporting

Requirements: 

The custom modules are in line with vtiger standard modules and is crmentity - i.e. folder that contains module has file "ModuleName.php" (for module "example1" it would be "example1.php" under folder $vtiger_root/modules/example1/ ), in the file module object is defined and have correct values for $table_name, $tab_name_index, fields have to be registered in vtiger_field table.

Each report consists of main module and sub-modules. In report SQL query sub-modules are always joined to main module (directly or via other sub-module).

To add new report with a custom module, the following SQL has to be run:

/* SQL START*/
SET @category_id := 1;
SET @mainModule := '#MAIN_MODULE#';
SET @subModules := '#SUB_MODULES#';
SET @title :='#NEW_TITLE#';
SET @description :='#NEW_DESCRIPTION#';
SET @isCombined := 0;
SET @owner := 1;


/* DO NOT MODIFY BELOW */
SET @sequence := SELECT MAX(`sequence`) FROM vtiger_advancedreports WHERE category_id = @category_id;
INSERT INTO `vtiger_advancedreports`(`category_id`, `sequence`, `module`, `related_data`, `fields`, `filters`, `grouping`, `aggregates`, `totalAggregates`, `title`, `description`, `options`, `chart`, `owner`, `shared`, `sharedlevel`, `iscombined`, `combinedfields`) VALUES (@category_id,@sequence,@mainModule,@subModules,'[]','[]','[]','[]','{}',@title,@description,'	{"includeDetails":false,"isCrosstab":false,"aggregateColumn":false}','	{"chartTitle":"","chartType":"pie","chartLabels":"","chartAxisX":"","chartAxisY":"","chartAxisY2":"","chartYAxisType":"","chartYAxis2Type":"","chartLegend":"","chartStacked":false}',@owner,0,0,@isCombined,'{}');

/* SQL END */


Replace 1 in line "SET @category_id := 1;" with id of desired folder, this is first default folder;
Replace #NEW_TITLE# in line "SET @title :='#NEW_TITLE#';" with title of report;
Replace #NEW_DESCRIPTION# in line "SET @description :='#NEW_DESCRIPTION#';" with description of report;
Replace #MAIN_MODULE# in line "SET @mainModule := '#MAIN_MODULE#';" with main module name of the report (IMPORTANT:this is technical name, the same as module parameter in URL when opening the module or folder name of the module files on server, not the module title);
Replace #SUB_MODULES# in line "SET @subModules := '#SUB_MODULES#';" with sub-module relation description (documented below, requires knowing module tables and relation structure of main and sub-modules)


Sub-module relation description:
It is JSON array of  with an array for each sub module relation info in this order: Module name, relation type (inverse/self), join column 1, join column 2, join table 1 join table 2, join module number
Module name   - see #MAIN_MODULE# above
relation type - either "inverse" or "self", if module described has foreign key field then "inverse", if module that this module is joined to (parent module) has foreign key field for this module then "self"
join column 1 - column of parent module that is used for join
join column 2 - column of described module that is used for join
join table 1  - table of parent module that is used for join
join table 2  - table of current module that is used for join
join module number - if joined to main module, then 0, if joined to another sub-module, then order number of sub-module in #SUB_MODULES#

Example:

report main module is "example1" and report needs modules "mod1" joined to "example1" on column "rel_mod1" and "mod2" joined to "mod1_rel" on column "mod2id"
and data structure:
 - example1 has table "vtiger_example1", 
 - vtiger_example1 table has column rel_mod1
 - mod1 has table vtiger_mod1, module id column is mod1id,
 - vtiger_mod1 table has column mod1id,
 - mod2 has table vtiger_mod2, module id column is mod2id,
 - vtiger_mod2 table has columns mod2id and mod1_rel

replace '#MAIN_MODULE#' with 'example1'
replace '#SUB_MODULES#' with '[["mod1","inverse","rel_mod1","mod1id","vtiger_example1","vtiger_mod1",0],["mod2","self","mod1id","mod1_rel","vtiger_mod1","vtiger_mod2",1]]'
after adding appropriate title and description values, run the sql, after that open the new report from interface as admin, select filters, fields, aggregates, grouping and set approriate ownership, sharing and scheduling (if needed) and save the report from interface