<?php
/**
 * Export module - Excel
 *
 * Contains all functions to generate Word file
 *
 * @package linea21\utils\export
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * Define, compose and generate a SDI (Sustainable Development Indicators) Word report
 */


// Giving ability to override HTML_EXPORT only for 'Excel' with EXCEL_HTML_EXPORT constant
// By default EXCEL_HTML_EXPORT = HTML_EXPORT
if(defined('HTML_EXPORT') && HTML_EXPORT == 1) define('EXCEL_HTML_EXPORT', 1);
else define('EXCEL_HTML_EXPORT', 0);

/**
 * cleanHTMLList()
 * remove unique p tag in li
 * @param string $content
 * @return string
 * @see https://simplehtmldom.sourceforge.io/docs/1.9/api/api/
 */

if(!function_exists('cleanHTMLList')) {

    function cleanHTMLList($content) {

        if(empty($content)) return '';

        $html = new simple_html_dom();

        $html->load($content);
        $el = $html->find('ul li, ol li');

        if(is_null($el) || count($el) === 0) return $content;  // necessary to prevent crash
        foreach($el as $e) {
            $ptags = $e->find('p');
            if(is_null($ptags) || count($ptags) === 0) return $content; // necessary to prevent crash
            // if a unique p tag in li we remove it to prevent extra gap on list
            if(count($ptags) === 1) {
                is_null($ptags[0]->innertext) ? $ptags[0]->outertext = '' : $ptags[0]->outertext = $ptags[0]->innertext;
            }
        }
        $clean = $html;
        $html->clear();
        unset($html);

        return $clean;

    }

}

/**
 * rmUnwantedTags()
 * remove unwanted tags
 * @param string $content
 * @return string
 */
if(!function_exists('rmUnwantedTags')) {
    function rmUnwantedTags($content) {

        if(empty($content)) return '';

        $html = new simple_html_dom();
        $html->load($content);

        foreach ($html->find('caption') as $el) {
            $el->outertext = '';
        }
        $content = $html;
        unset($html);

        $content = cleanHTMLList($content); // we also clean lists
        return $content;

    }
}

function html2rawText($content) {

    if(empty($content)) return $content;
    $breaks = array("<br />","<br>","<br/>");
    $content = str_ireplace($breaks, "\n", $content);
    $content = strip_tags($content);
    $content = htmlspecialchars_decode($content);

    return $content;
}

/********************************************************************************************
 *
 * Following functions are used to generate projects reports
 *
 ********************************************************************************************/

if(!function_exists('project_recursive')) {

    function project_recursive($levels, $currentLine) {

        global $ordered_items;
        global $project;
        global $report_settings;

        $debug = false;

        // we retrieve all root levels if not given - when first run
        if($levels === null) $levels = $GLOBALS['sql_object']->DBSelect(SQL_getLevelsList(true));

        // if($debug) print_r($levels);

        // for each root level ...
        foreach($levels as $l) {

            // on affiche ici le nom du thème
            // et la description si demandée
            if($debug) echo "<h1>". $l['level_name'] . "</h1><p>". $l['level_desc_project'] . "</p><br /><br />";

//             if ($report_settings['levels'] ==  true) project_display_level($l, $currentLine);

            // if there is action related to current level we display them
            // first we prepare them
            $associated_projects = array();
            foreach($project as $p) {
                if($p['project_level_id'] == $l['level_id']) {
                    if($debug) echo "on affiche le projet ". $p['project_id'] ." (p-level-id".$p['project_level_id']. ") associé directement à ". $l['level_id']."<br />\n<br />\n";
                    array_push($associated_projects, $p);
                    array_push($ordered_items, $p);
                }
            }

            // we get levels children
            $levelschildren = getLevelChildren($GLOBALS['sql_object']->DBSelect(SQL_getLevelsList()), $l['level_id']);
            if($debug) echo "<h1>----- Here are children -----</h1>";
            if($debug) print_r($levelschildren);

            // we display level if they have associated projects or children levels and or we display empty levels
            if($report_settings['levels'] ==  true && (count($associated_projects) > 0 || count($levelschildren) > 0 || $report_settings['display_empty_levels'] ===  true)) {
                project_display_level($l, $currentLine);
                $currentLine++;
            }

            // we display project if any
            if(count($associated_projects) > 0) {

                $associated_projects = sort_projects($associated_projects);

                foreach($associated_projects as $proj) {
                    project_display_by_levels($proj, $currentLine);
                    $currentLine++;
                }
            }



            if(count($levelschildren) > 0 ) $currentLine = project_recursive($levelschildren, $currentLine++);

        }
        return $currentLine;


    }
}

function sort_projects($projects) {

    // we init values each time we iterate
    $project_range = array();
    $project_name = array();

    // First we prepare multi-sort
    // @see http://php.net/manual/fr/function.array-multisort.php
    foreach ($projects as $key => $row) {
        $project_range[$key] = $row['project_range'];
        $project_name[$key] = $row['project_name'];
    }

    array_multisort($project_range, SORT_ASC, $project_name, SORT_ASC, $projects);

    return $projects;

}

if(!function_exists('display_orphan_projects')) {
    function display_orphan_projects ($currentLine) {

        global $ordered_items;
        global $project;
        global $report_settings;
        global $mainsheet;
        global $docOptions;
        global $lastCol;

        $associated_projects = array();
        foreach($project as $p) {
            if($p['project_level_id'] == 0) {
                array_push($associated_projects, $p);
                array_push($ordered_items, $p);
            }
        }

        if(count($associated_projects) < 1) return $currentLine;

        // doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray
        $styleArray = [
            'font' => [
                'bold' => (boolean) $docOptions['levelFontBold'],
                'size' => (string) $docOptions['levelFontSize'],
                'color' => ['argb' => $docOptions['levelFontColor'] ]
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'color' => ['argb' => $docOptions['levelBackgroundColor'] ]
            ]
        ];

        $mainsheet->setCellValue('A'.$currentLine, html2rawText(_t('report', 'no-first-level')));
        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->applyFromArray($styleArray);

        // we finally style the created row !
        $mainsheet->mergeCells('A'.$currentLine.':'.$lastCol.$currentLine); // we merge cells !
        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->getAlignment()->setWrapText((bool) $docOptions['wrapText']);

        $associated_projects = sort_projects($associated_projects);
        foreach($associated_projects as $proj) {
            $currentLine++;
            project_display_by_levels($proj, $currentLine);

        }

        return $currentLine;

    }
}

if(!function_exists('display_date_value')) {
    function display_date_value($date) {
        if(formatDate($date, true)== '0001-01-01') return empty_nc('');
        else return $date;
    }
}

/**
 * project_display_by_levels()
 * Affiche les infos d'un projet
 *
 * @param int $project
 * @param int $currentLine
 * @return bool true
 */

if(!function_exists('project_display_by_levels')) {
    function project_display_by_levels($project, $currentLine)
    {

        global $report_settings;
        global $itemsCols;
        global $mainsheet;
        global $lastCol;
        global $docOptions;

        $current_record = $project;
        if(defined('EXPORT_LOG') && EXPORT_LOG == 1) file_put_contents('../tmp/export-logs/excel-projects.txt', $current_record['project_id']."\n", FILE_APPEND);

        // HTML helper for formatting content. Only if HTML is enabled for export
        $html = new PhpOffice\PhpSpreadsheet\Helper\Html();


        /////////////////////////////////////////
        /// User defined columns goes here
        // for more visibility !!!!!!
        /////////////////////////////////////////

        ////

        /// Simple elements
        //////////////////////////

        // handle 'id'
        if(isset($itemsCols['id'])) $mainsheet->setCellValue($itemsCols['id'].$currentLine, $current_record['project_id']);

        // handle 'name'
        if(isset($itemsCols['name'])) $mainsheet->setCellValue($itemsCols['name'].$currentLine, mb_ucfirst(html2rawText($current_record['project_name'])));

        // handle 'priority'
        if(isset($itemsCols['priority'])) $mainsheet->setCellValue($itemsCols['priority'].$currentLine, html2rawText($current_record['priority_name']));

        // handle 'goal'
        if(isset($itemsCols['goal'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_goal'] = removeDomElement('img', $current_record['project_goal']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['goal'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_goal']))));
            else $mainsheet->setCellValue($itemsCols['goal'].$currentLine, html2rawText($current_record['project_goal']));
        }

        // handle 'opgoal'
        if(isset($itemsCols['opgoal'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_opgoal'] = removeDomElement('img', $current_record['project_opgoal']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['opgoal'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_opgoal']))));
            else $mainsheet->setCellValue($itemsCols['opgoal'].$currentLine, html2rawText($current_record['project_opgoal']));
        }

        // handle 'body'
        if(isset($itemsCols['body'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_body'] = removeDomElement('img', $current_record['project_body']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['body'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_body']))));
            else $mainsheet->setCellValue($itemsCols['body'].$currentLine, html2rawText($current_record['project_body']));
        }

        // handle 'description'
        if(isset($itemsCols['description'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_description'] = removeDomElement('img', $current_record['project_description']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['description'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_description']))));
            else $mainsheet->setCellValue($itemsCols['description'].$currentLine, html2rawText($current_record['project_description']));
        }

        // handle 'calendar' items and achievement
        if(isset($itemsCols['achievement'])) $mainsheet->setCellValue($itemsCols['achievement'].$currentLine, empty_nc(($GLOBALS['lang']['project']['achievementItems'][$current_record['project_achievement']])));

        if(isset($itemsCols['cal_begin_date'])) $mainsheet->setCellValue($itemsCols['cal_begin_date'].$currentLine, display_date_value($current_record['project_begin_date_display']));
        if(isset($itemsCols['cal_estimated_date'])) $mainsheet->setCellValue($itemsCols['cal_estimated_date'].$currentLine, display_date_value($current_record['project_estimated_date_display']));
        if(isset($itemsCols['cal_end_date'])) $mainsheet->setCellValue($itemsCols['cal_end_date'].$currentLine, display_date_value($current_record['project_end_date_display']));
        if(isset($itemsCols['cal_completion'])) $mainsheet->setCellValue($itemsCols['cal_completion'].$currentLine, html2rawText($current_record['project_completed']));

        // handle 'budget_comment'
        if(isset($itemsCols['cal_comment'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['budget_comment'] = removeDomElement('img', $current_record['project_cal_comment']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['cal_comment'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_cal_comment']))));
            else $mainsheet->setCellValue($itemsCols['cal_comment'].$currentLine, html2rawText($current_record['project_cal_comment']));
        }

        // handle 'elected'
        if(isset($itemsCols['elected'])) $mainsheet->setCellValue($itemsCols['elected'].$currentLine, html2rawText($current_record['project_elected']));

        // handle 'team'
        if(isset($itemsCols['team'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_team'] = removeDomElement('img', $current_record['project_team']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['team'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_team']))));
            else $mainsheet->setCellValue($itemsCols['team'].$currentLine, html2rawText($current_record['project_team']));
        }

        // handle 'budget'
        if(isset($itemsCols['budget'])) $mainsheet->setCellValue($itemsCols['budget'].$currentLine, formatText($current_record['project_budget']));

        // handle 'budget_comment'
        if(isset($itemsCols['budget_comment'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['budget_comment'] = removeDomElement('img', $current_record['project_budget_comment']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['budget_comment'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_budget_comment']))));
            else $mainsheet->setCellValue($itemsCols['budget_comment'].$currentLine, html2rawText($current_record['project_budget_comment']));
        }

        // handle 'comment'
        if(isset($itemsCols['comment'])) {
            if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['project_comment'] = removeDomElement('img', $current_record['project_comment']);
            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['comment'].$currentLine, $html->toRichTextObject(rmUnwantedTags(($current_record['project_comment']))));
            else $mainsheet->setCellValue($itemsCols['comment'].$currentLine, html2rawText($current_record['project_comment']));
        }

        // handle 'manager'
        $managers = getAssociatedManagers($current_record['project_id']);
        if(isset($itemsCols['manager'])) $mainsheet->setCellValue($itemsCols['manager'].$currentLine, html2rawText($managers));

        // handle 'target'
        $targets = getAssociatedActors($current_record['project_id'], 'target');
        if(isset($itemsCols['target'])) $mainsheet->setCellValue($itemsCols['target'].$currentLine, html2rawText($targets));

        // handle 'partners'
        $partners = getAssociatedActors($current_record['project_id'], 'partner');
        if(isset($itemsCols['partners'])) $mainsheet->setCellValue($itemsCols['partners'].$currentLine, html2rawText($partners));

        // handle 'coordinator'
        $coordinators = getAssociatedActors($current_record['project_id'], 'coordinator');
        if(isset($itemsCols['coordinator'])) $mainsheet->setCellValue($itemsCols['coordinator'].$currentLine, html2rawText($coordinators));

        // handle 'status'
        if(isset($itemsCols['status'])) $mainsheet->setCellValue($itemsCols['status'].$currentLine, display_statut($current_record['project_statut']));

        // handle 'parent'
        if(isset($itemsCols['parent'])) $mainsheet->setCellValue($itemsCols['parent'].$currentLine, formatText($current_record['parent_project_name']));

        // handle 'scale'
        if(isset($itemsCols['scale'])) $mainsheet->setCellValue($itemsCols['scale'].$currentLine, formatText($current_record['scale_denomination']));

        // handle 'author'
        if(isset($itemsCols['author'])) $mainsheet->setCellValue($itemsCols['author'].$currentLine, formatText($current_record['user_login']));


        /// Complex elements
        //////////////////////////

        //  we retrieve root level
        $levels = $GLOBALS['sql_object']->DBSelect(SQL_getLevelsList());
        $root_level = getLevelParent($levels, $current_record['level_id'], 0);
        // Root levels
        // root parent has been retrieved before
        if ($report_settings['levels'] !==  true) {
            if(is_array($root_level) && $root_level['id'] != 0) {
                if(!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0) {
                    $out_level = formatText('» ' . mb_ucfirst($current_record['level_label']). ' : ' . html2rawText($current_record['level_name'])) ;
                    $out_level .=  ' / '. formatText( mb_ucfirst($root_level['label']) . ' : ' . html2rawText($root_level['name'])). PHP_EOL;
                } else {
                    $out_level = formatText('» ' . $current_record['level_name']) ;
                    $out_level .=  ' / '. formatText($root_level['name']). PHP_EOL;
                }

            } elseif(!empty($current_record['level_name'])) {
                if(!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0) {
                    $out_level = formatText('» ' . mb_ucfirst($current_record['level_label']). ' : ' . html2rawText($current_record['level_name'])) ;
                } else {
                    $out_level = formatText('» ' . html2rawText($current_record['level_name'])) ;
                }
            }
            if(isset($itemsCols['level'])) $mainsheet->setCellValue($itemsCols['level'].$currentLine, $out_level);
        } else {
            if(isset($itemsCols['level'])) $mainsheet->setCellValue($itemsCols['level'].$currentLine, html2rawText($current_record['level_name']));
        }


        if(isset($itemsCols['tasks']))   project_tasks($current_record, $currentLine);

        if(isset($itemsCols['finalities']))  project_finalities($current_record, $currentLine);

        // dynamic displaying of impacts - WARNING if impactsItems have been overwritten, do change project-schema.json as well !
        if(is_array($GLOBALS['lang']['project']['impactsItems'])) {
            foreach($GLOBALS['lang']['project']['impactsItems'] as $key => $value) {
                project_display_impact($current_record, $currentLine, 'impact-'.$key);
            }
        }


        if(isset($itemsCols['indicators'])) project_associated_indicators($current_record, $currentLine);
        if($report_settings['dashboard_values']) project_associated_indicators_values($current_record, $currentLine);

        // we finally style the row
        // doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray
        $styleArray = [
            'font' => [
                'bold' => (boolean) $docOptions['mainFontBold'],
                'size' => (string) $docOptions['mainFontSize'],
                'color' => ['argb' => $docOptions['mainFontColor'] ]

            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'color' => ['argb' => $docOptions['mainBackgroundColor']]
            ]
        ];

        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->applyFromArray($styleArray);
        $mainsheet->getDefaultRowDimension()->setRowHeight($docOptions['rowHeight']);
        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->getAlignment()->setWrapText((bool) $docOptions['wrapText']);

        if ($report_settings['levels'] ==  true) {
            $mainsheet->getStyle($itemsCols['name'].$currentLine)->getAlignment()->setIndent(1); // margin
        }

        return true;
    }
}

/**
 * project_finalities()
 * Affiche les finalités d'un projet
 *
 * @param int $current_record
 * @param int $currentLine
 * @return bool true
 */
if(!function_exists('project_finalities')) {
    function project_finalities($current_record, $currentLine) {

        global $mainsheet;
        global $itemsCols;

        // finalities project
        if(defined('PROJECT_FINALITIES') && PROJECT_FINALITIES == 1) {

            $sdfinalities = getFinalities($current_record['project_id'], 'array');

            $actives = []; // array of active finalities

            foreach ($sdfinalities as $finality) {
                if($finality['state'] == 'active') array_push($actives, '• '. html2rawText($finality['name']));
            }
            $mainsheet->setCellValue($itemsCols['finalities'].$currentLine, join("\n", $actives));

        }

        return true;

    }
}

/**
 * project_display_impacts()
 * Affiche les impacts de chacun des projets
 *
 * @param int $current_record
 * @param int $currentLine
 * @param string $type
 * @return bool true
 */
if(!function_exists('project_display_impacts')) {
    function project_display_impact($current_record, $currentLine, $type) {

        global $mainsheet;
        global $itemsCols;

        // if not defined or not enabled we return false
        if (!defined('PROJECT_IMPACTS') || PROJECT_IMPACTS == 0) return false;

        $project_impacts = unserialize($current_record['project_impact']);

        // no values, we return empty string
        if (!is_array($project_impacts)) return false;

        $key = str_replace('impact-', '', $type);

        $mainsheet->setCellValue($itemsCols[$type].$currentLine, html2rawText($project_impacts[$key]));
        // $mainsheet->getComment($itemsCols[$type].$currentLine)->getText()->createTextRun($project_impacts[$key] . '/' . PROJECT_IMPACTS);

        return true;
    }


}


/**
 * project_tasks()
 * Affiche les tâches associées au projet
 *
 * @param int $current_record
 * @param int $currentLine
 * @return bool true
 */
if(!function_exists('project_tasks')) {
    function project_tasks($current_record, $currentLine) {

        global $sql_object;
        global $mainsheet;
        global $itemsCols;

        // HTML helper for formatting content. Only if HTML is enabled for export
        $html = new PhpOffice\PhpSpreadsheet\Helper\Html();

        if(defined('PROJECT_TASK') && PROJECT_TASK == 1 && isset($itemsCols['tasks'])) {

            $otask = new projectTask();
            $tasks = $otask->get_project_tasks($current_record['project_id'], $sql_object);

            if(!is_array($tasks)) return true;


            $tasksp =  get_task_progress_bar($current_record['project_id'], true);
            $tasks =   get_tasks_for_report($tasks);

            $content = $tasksp . "\n---\n";
            $htmlcontent = $tasksp . "<br>---<br>";

            // we get properties
            $props = getColProperties('tasks');

            // we set var to empty content if 'display_percentage' is explicitly false
            if(isset($props->display_percentage) && $props->display_percentage == false) {
                $content = '';
                $htmlcontent = '';
            }

            foreach($tasks as $el) {

                if($el['status_raw'] == 'todo') $color = 'dadada';
                if($el['status_raw'] == 'wip') $color = 'fbbf50';
                if($el['status_raw'] == 'done') $color = '61d3a4';

                $content .= mb_strtoupper($el['status']). ' // '.$el['label'];
                $content .= "\n--------\n";

                $htmlcontent .= '<div><font color="#'.$color.'">'.mb_strtoupper($el['status']).'</font> // ';
                $htmlcontent .= '<p>'.nl2br($el['label']).'</p><p>--------</p></div>'."\n";

            }

            if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) $mainsheet->setCellValue($itemsCols['tasks'].$currentLine, $html->toRichTextObject(rmUnwantedTags($htmlcontent)));
            else $mainsheet->setCellValue($itemsCols['tasks'].$currentLine, html2rawText($content));

            return true;

        }
    }
}


if(! function_exists('getRawIndicatorRecord')) {
    function getRawIndicatorRecord($scale_id, $indicator) {

        include_once('../dashboard/common.php');

        $req_sdiav=SQL_getAllValue("SCA", $scale_id, $indicator['sdii_id']);
        $result_value = $GLOBALS['sql_object']-> DBSelect($req_sdiav);

        // if not values yet we return empty string
        if(!is_array($result_value)) return '';

        // we handle multivalues
        if ($indicator['sdii_value_type'] == 'multiple') {
            $data = unserialize($result_value[0]['sdiv_multivalue']);
            if($indicator['sdii_multiple_type'] == 'sum') $current_value = $data[0]['_total'];
            elseif($indicator['sdii_multiple_type'] == 'mean') $current_value = $data[0]['_mean'];
            elseif($indicator['sdii_multiple_type'] == 'none')  $current_value = _t('sdi', 'multiple_type_none');
            // we handle simple values
        } else {
            $a = getBooleanValues($indicator);

            if ($a && ($indicator['sdii_nature'] == 'boolean' || $indicator['sdii_nature'] == 'qualitative')) {
                $current_value = $a[$result_value[0]['sdiv_value']];
            } else {
                if(is_null($result_value[0]['sdiv_value'])) $current_value = _t('dashboard', 'no_data');
                else $current_value = fnumber_format($result_value[0]['sdiv_value'], 'auto', false);
            }
        }

        $aval = $current_value;

        if(isset($result_value[0])) {
            $arr = array_reverse($result_value);
            $lastval = end($arr);
            $adate = $lastval['date_p'];
            $acomment = html2rawText($lastval['sdiv_comment']);
        }

        $aname = formatText($indicator['sdii_name'], '2HTML'). ' [' . formatText($indicator['sdii_unit']) .']';

        return array($aname, $aval, $adate, $acomment);

    }
}

if(! function_exists('project_associated_indicators_values')) {
    function project_associated_indicators_values($record, $currentLine) {

        global $mainsheet;
        global $itemsCols;
        // global $lastCol;
        global $lastColBeforeIndicators;

        $result_sdi = $GLOBALS['sql_object']->DBSelect(SQL_getProjectSdiValues($record['project_id']));
        $scale_id = 1; // scale id value

        // we exit if no associated indicators
        if(!is_array($result_sdi)) return false;

        // we get properties
        $props = getColProperties('indicators-values');
        if(isset($props->limit)) $maxIndicatorsLimit = $props->limit;

        $ordered_indics = array();
        // we prepare ordered data if given
        // 1 - first data stored in $orderedIndicators, then all data
        $tmp = unserialize($record['project_indic_order']);
        if(is_array($tmp)) {
            foreach($tmp as $k => $v) {
                if(is_integer($k)) array_push($ordered_indics, $k); // to be sure it is not empty
            }
        }
        // 2 - then, we iterate on all indicators thare are not already in $ordered_indics
        foreach($result_sdi as $sdi) {
            if($sdi['sdii_statut'] == 'P' && !in_array($sdi['sdii_id'], $ordered_indics)) array_push($ordered_indics, $sdi['sdii_id']); // to be sure it is not empty
        }

        $count = 1;
        $tmpColIndic = $lastColBeforeIndicators;
        foreach($ordered_indics as $k => $v) {

            // we add the record if allowed by limit
            if($count <= $maxIndicatorsLimit) {
                // we search for the key of searched element : $j
                // then we display the full record
                $j = array_search($v, array_column($result_sdi, 'sdii_id'));

                // we get information from getRawIndicatorRecord which return an array to fill cells later
                $indic = getRawIndicatorRecord($scale_id, $result_sdi[$j]);

                // we populate array, only if a value is returned by $indic. If no value yet, we do not display a record
                if(is_array($indic)) {

                    $tmpColIndic++;
                    $sdiNameX = $tmpColIndic; // something like 'AA'
                    $tmpColIndic++;
                    $sdiValueX = $tmpColIndic; // something like 'AB'
                    $tmpColIndic++;
                    $sdiDateX = $tmpColIndic;  // something like 'AC'
                    // $lastCol = $sdiValueX; // we set $lastCol - No !! it is already set in main file

                    if(isset($props->only_year) && $props->only_year == 1) $indic[2] = get_year($indic[2]);

                    $mainsheet->setCellValue($sdiNameX.$currentLine, html2rawText($indic[0]));
                    $mainsheet->setCellValue($sdiValueX.$currentLine, html2rawText($indic[1]));
                    $mainsheet->setCellValue($sdiDateX.$currentLine, html2rawText($indic[2]));

                    // we add comment if comment is given on value
                    if(!empty($indic[3])) $mainsheet->getComment($sdiValueX.$currentLine)->getText()->createTextRun(html2rawText($indic[3]));

                    $count++;
                }
            }
        }
    }
}

/**
 * project_associated_indicators()
 * Affiche la liste des indicateurs associés
 *
 * @param int $current_record
 * @param int $currentLine
 * @return bool true
 */
if(! function_exists('project_associated_indicators')) {
    function project_associated_indicators($current_record, $currentLine) {
        global $sql_object;
        global $mainsheet;
        global $itemsCols;

        $query = SQL_getProjectSdi($current_record['project_id']);
        $data = $GLOBALS['sql_object']->DBSelect($query);

        if(isset($data[0]['sdii_id'])) {

            $tmpIndics = [];

            foreach ($data as $v) {
                array_push($tmpIndics, '• '. strip_tags($v['sdii_name']));
            }
            $mainsheet->setCellValue($itemsCols['indicators'].$currentLine, join("\n", $tmpIndics));
        }

        return true;
    }
}


/**
 * project_display_level()
 * display root level as chapter
 * @param string $level
 * @param int @param int
 */
if(! function_exists('project_display_level')) {
    function project_display_level($level, $currentLine) {

        global $report_settings;
        global $itemsCols;
        global $mainsheet;
        global $lastCol;
        global $docOptions;


        if(!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0) $level_label = $level['level_label'].' : ';
        else $level_label = '';

        // doc : https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#valid-array-keys-for-style-applyfromarray
        $styleArray = [
            'font' => [
                'bold' => (boolean) $docOptions['levelFontBold'],
                'size' => (string) $docOptions['levelFontSize'],
                'color' => ['argb' => $docOptions['levelFontColor'] ]
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'color' => ['argb' => $docOptions['levelBackgroundColor'] ]
            ]
        ];
        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->applyFromArray($styleArray);

        // if 'firstlevel_description' is enabled we add id to the cell
        if ($report_settings['firstlevel_description'] === true && $level['level_parent'] == 0) {

            if(!empty($level['level_desc_project'])) $ldesc = html2rawText("\n\n".$level['level_desc_project']);
            else $ldesc ='';

            $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
            $richText->createText(mb_ucfirst(html2rawText($level_label . $level['level_name'])));
            $cdesc = $richText->createTextRun($ldesc);
            $cdesc->getFont()->setBold(false); // be sure, desc is not in bold
            $cdesc->getFont()->setItalic(true); // to distinguish from title
            // $cdesc->getFont()->setSize($docOptions['levelFontSize']-2);
            $mainsheet->getCell('A'.$currentLine)->setValue($richText);

            //$mainsheet->setCellValue($itemsCols['level'].$currentLine, $richText);

        } else {
            $mainsheet->setCellValue('A'.$currentLine, mb_ucfirst(html2rawText($level_label . $level['level_name'])));
        }
        //$mainsheet->setCellValue($itemsCols['level'].$currentLine, $richText);
        // $mainsheet->setCellValue($itemsCols['level'].$currentLine, mb_ucfirst(html2rawText($level_label . $level['level_name'])));

        // we finally style the created row !
        $mainsheet->mergeCells('A'.$currentLine.':'.$lastCol.$currentLine); // we merge cells !
        $mainsheet->getStyle('A'.$currentLine.':'.$lastCol.$currentLine)->getAlignment()->setWrapText((bool) $docOptions['wrapText']);

    }
}


/********************************************************************************************
 *
 * Following functions are used to generate dashboard reports
 *
 ********************************************************************************************/

if(!function_exists('indicator_recursive')) {
    function indicator_recursive($levels, $bookmark_level) {

        global $ordered_items;
        global $sdi;
        global $section;
        global $report_settings;
        global $pageBreak;

        $debug = false;

        // we retrieve all root levels if not given - when first run
        if($levels === null) $levels = $GLOBALS['sql_object']->DBSelect(SQL_getLevelsList(true));

        // if($debug) print_r($levels);

        // for each root level ...
        foreach($levels as $l) {

            // on affiche ici le nom du thèmes
            // et la description si demandée
            if($debug) echo "<h1>". $l['level_name'] . "</h1><p>". $l['level_desc_dashboard'] . "</p><br /><br />";

            // if ($report_settings['levels'] ==  true) indicator_display_level($l, $bookmark_level, $section);

            // if there is action related to current level we display them
            // first we prepare them
            $associated_indicators = array();
            foreach($sdi as $indicator) {
                if($indicator['sdii_level'] == $l['level_id']) {
                    if($debug) echo "on affiche l'indicateur ". $indicator['sdii_id'] ." (indic-level-id".$indicator['sdii_level']. ") associé directement à ". $l['level_id']."<br />\n<br />\n";
                    array_push($associated_indicators, $indicator);
                    array_push($ordered_items, $indicator);
                }
            }

            // we get levels children
            $levelschildren = getLevelChildren($GLOBALS['sql_object']->DBSelect(SQL_getLevelsList()), $l['level_id']);
            if($debug) echo "<h1>----- Here are children -----</h1>";
            if($debug) print_r($levelschildren);

            // we display level if they have associated indicators or children levels and or we we display empty levels
            if($report_settings['levels'] ==  true && (count($associated_indicators) > 0 || count($levelschildren) > 0 || $report_settings['display_empty_levels'] ===  true)) {
                indicator_display_level($l, $bookmark_level, $section);
            }

            // we display indicator if any
            if(count($associated_indicators) > 0) {

                $associated_indicators = sort_indicators($associated_indicators);

                foreach($associated_indicators as $indicator) indicator_display_by_levels($indicator,$bookmark_level, $section);
            }



            if(count($levelschildren) > 0 ) indicator_recursive($levelschildren, $bookmark_level+1, $section);

        }


    }
}

function sort_indicators($indicators) {

    // we init values each time we iterate
    $indicator_range = array();
    $indicator_name = array();

    // First we prepare multi-sort
    // @see http://php.net/manual/fr/function.array-multisort.php
    foreach ($indicators as $key => $row) {
        $indicator_range[$key] = $row['sdii_range'];
        $indicator_name[$key] = $row['sdii_name'];
    }

    array_multisort($indicator_range, SORT_ASC, $indicator_name, SORT_ASC, $indicators);

    return $indicators;

}


function display_orphan_indicators ($bookmark_level) {

    global $ordered_items;
    global $sdi;
    global $section;
    global $report_settings;

    $associated_indicators = array();
    foreach($sdi as $i) {
        if($i['sdii_level'] == 0) {
            array_push($associated_indicators, $i);
            array_push($ordered_items, $i);
        }
    }

    if(count($associated_indicators) < 1) return false;

    if ($report_settings['levels'] ==  true) {
        $section->addTitle(do_strip(_t('report', 'no-first-level')), $bookmark_level);
        $section->addTextBreak(2);

    }

    $associated_indicators = sort_indicators($associated_indicators);
    foreach($associated_indicators as $ind) indicator_display_by_levels($ind,$bookmark_level, $section);

    return true;

}

/**
 * indicator_display_by_levels()
 * Affiche les infos générales d'un projet
 *
 * @param int $pointer
 * @return bool true
 */
if(!function_exists('indicator_display_by_levels')) {
    function indicator_display_by_levels($indicator, $bookmark_level, $section)
    {

        global $report_settings;
        global $pageBreak;

        if(defined('EXPORT_LOG') && EXPORT_LOG == 1) file_put_contents('../tmp/export-logs/excel-indicators.txt', $indicator['sdii_id']."\n", FILE_APPEND);

        // we override dynamic $bookmark_level
        if($report_settings['levels'] ==  false) $bookmark_level = 0;

        indicator_display_main($indicator, $bookmark_level);
        indicator_display_values($indicator['sdii_id'], $bookmark_level);
        if ($report_settings['display_provider'] === true) indicator_display_provider($indicator, $bookmark_level);
        if ($report_settings['display_reglementation'] === true) indicator_display_rules($indicator, $bookmark_level);
        if ($report_settings['display_evaluation'] === true) indicator_display_assessment($indicator, $bookmark_level);


        // because $section->addPageBreak(); does not work in current version
        // we add page only if this is not a quick export
        if($report_settings['quick_export'] === false) $section->addTextBreak(null, null, $pageBreak);

        return true;
    }
}


/**
 * indicator_display_level()
 * display level as chapter
 * @param string $level
 */
function indicator_display_level($level, $bookmark_level, $section)
{

    // global $section;
    global $report_settings;
    global $pageBreak;
    // global $bookmark_level;

    if(!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0) $level_label = $level['level_label'].' : ';
    else $level_label = '';

    // if level 0 we add page break
    if ($level['level_parent'] == 0) {
        $section->addTextBreak(null, null, $pageBreak);
        $section->addTextBreak(4);
    }
    else $section->addTextBreak(4);
    $section->addTitle(mb_ucfirst(do_strip($level_label.$level['level_name'])), $bookmark_level);
    $section->addTextBreak(2);


    if ($report_settings['firstlevel_description'] === true && $level['level_parent'] == 0) {

        if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($section, formatText(rmUnwantedTags(fixImageWidth($level['level_desc_dashboard']))));
        else $section->addText(do_strip($level['level_desc_dashboard']), 'defaultF', 'defaultP');

        // because $section->addPageBreak(); does not work in current version
        $section->addTextBreak(null, null, $pageBreak);
    } else {
        $section->addTextBreak(2);
    }

}

/**
 * indicator_display_main()
 * Affiche les infos générales d'un IDD
 *
 * @param int $current_record
 * @return bool true
 */
function indicator_display_main($current_record, $bookmark_level)
{
    global $section;
    global $report_settings;

    // titre d'un indicateur
    $out_title = formatText($current_record['sdii_name']);

    // si non publié
    if ($current_record['sdii_statut'] == 'D') {
        $out_title .= ' / ' . strtolower(_t('statut', 'draftpdf'));
    }
    // si non attaché au tableau de bord
    if ($current_record['sdii_to_dashboard'] == 'Y') {
        $out_title .= ' (' . strtolower(_t('sdi', 'attached_to_dashboard').')');
    }

    $section->addTitle($out_title, $bookmark_level+1);

    // affichage du type si existant
    if($current_record['sdii_type'] != 'not-set') {

        if($current_record['sdii_type'] == 'state') $out_type = ' <span style="background-color:pink; color: rgb(255, 255, 255);font-size:11px;">&nbsp;'.mb_ucfirst($GLOBALS['lang']['sdi']['select_type'][$current_record['sdii_type']]).'&nbsp;</span>';
        if($current_record['sdii_type'] == 'follow-up' || $current_record['sdii_type'] == 'pressure') $out_type = ' <span style="background-color: #87d9ba; color:#4f8872;font-size:11px;">&nbsp;'.mb_ucfirst($GLOBALS['lang']['sdi']['select_type'][$current_record['sdii_type']]).'&nbsp;</span>';
        if($current_record['sdii_type'] == 'achievement' || $current_record['sdii_type'] == 'response') $out_type = ' <span style="background-color:#bfa8d5; color:#595a9f;font-size:11px;">&nbsp;'.mb_ucfirst($GLOBALS['lang']['sdi']['select_type'][$current_record['sdii_type']]).'&nbsp;</span>';

        \PhpOffice\PhpWord\Shared\Html::addHtml($section, formatText($out_type));
    }

    // thème d'appartenance
    if ($report_settings['levels'] !==  true) {
        if($current_record['sdii_level'] == 0) $level_name = mb_ucfirst(_t('sdi', 'no_associated_level'));
        else $level_name = mb_ucfirst($current_record['level_name']);
        if($current_record['sdii_level'] != 0 && (!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0)) $out_level = formatText('» ' . mb_ucfirst($current_record['level_label']). ' : ' . $level_name);
        else $out_level = $out_level = formatText('» ' . $level_name );

        // we retrieve root level
        $levels = $GLOBALS['sql_object']->DBSelect(SQL_getLevelsList());
        $a = getLevelParent($levels, $current_record['level_id'], 0);
        if(is_array($a) && $a['id'] != $current_record['level_id']) {
            if(!defined('EXPORT_NOLEVELLABEL') || EXPORT_NOLEVELLABEL == 0) $out_level .=  ' / ' .formatText( mb_ucfirst($a['label']) . ' : ' . $a['name']) . PHP_EOL;
            else $out_level .=  ' / ' .formatText($a['name']) . PHP_EOL;

        }

        $section->addText(do_strip($out_level), 'attachedTheme');
        $section->addTextBreak(1);
    }

    // description
    $out_desc = formatText(do_strip($current_record['sdii_description'])) . PHP_EOL;

    if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['sdii_description'] = removeDomElement('img', $current_record['sdii_description']);
    if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($section, formatText(empty_nc(rmUnwantedTags(fixImageWidth($current_record['sdii_description'])))));
    else $section->addText($out_desc, 'defaultF', 'defaultP');

    $section->addTextBreak(1);

    // objectif
    if(!empty($current_record['sdii_goal'])) {
        $out_goal = formatText(empty_nc(do_strip($current_record['sdii_goal']))) . PHP_EOL;
        $out_goal_item = formatText(mb_ucfirst(_t('sdi', 'goal')));
        // $section->addTitle($out_goal_item, $bookmark_level+2);

        if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['sdii_goal'] = removeDomElement('img', $current_record['sdii_goal']);
        if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($section, formatText(empty_nc(rmUnwantedTags(fixImageWidth($current_record['sdii_goal'])))));
        else $section->addText($out_goal, 'defaultF', 'defaultP');

        $section->addTextBreak(1);
    }

    // conseil d'amélioration
    if(!empty($current_record['sdii_consulting'])) {
        $out_consulting = formatText(empty_nc(do_strip($current_record['sdii_consulting']))) . PHP_EOL;
        $out_consulting_item = formatText(mb_ucfirst(_t('sdi', 'consulting')));
        // $section->addTitle($out_consulting_item, $bookmark_level+2);

        if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['sdii_consulting'] = removeDomElement('img', $current_record['sdii_consulting']);
        if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($section, formatText(empty_nc(rmUnwantedTags(fixImageWidth($current_record['sdii_consulting'])))));
        else $section->addText($out_consulting, 'defaultF', 'defaultP');

        $section->addTextBreak(1);
    }

    // informations de mesure
    $out_mesures_item = formatText(ucfirst(_t('sdi', 'info_mesure')));
    $out_mesures_behavior = ($current_record['sdii_threshold_relative'] == 'Y' ? _t('sdi', 'threshold_relative_Y'): _t('sdi', 'threshold_relative_N'));
    $out_mesures_unit = formatText(mb_ucfirst(_t('sdi', 'unit'))) . ' : ' . $current_record['sdii_unit'] . PHP_EOL;
    // $section->addTitle($out_mesures_item, $bookmark_level+2);


    // unitée de mesure
    $section->addText($out_mesures_unit);
    // $section->addTextBreak(1);

    // tableau de valeurs
    indicator_display_mesures($current_record);
    // comportement
    $section->addText(mb_ucfirst($out_mesures_behavior), array('size' => FONT_SIZE_MIN, 'italic' => true, 'color' => '555555'), array('align' => 'right'));
    $section->addTextBreak(2);
}

/**
 * indicator_display_mesures()
 * Dessine le tableau des valeurs IDD
 *
 * @param  $current_record
 * @return bool true
 */
function indicator_display_mesures($current_record)
{

    global $section;
    global $phpWord;
    global $cellalign;
    global $cellHeaderStyle;
    global $styleTable;
    global $firstRowStyle;

    $a = getBooleanValues($current_record);

    // if indicator is boolean or qualitative and mask is defined
    if(($current_record['sdii_nature'] == 'boolean' || $current_record['sdii_nature'] == 'qualitative') && $a) {
        if($a) {
            $index = array();
            foreach ($a as $key => $value) {
                array_push($index, $key);
            }

            $display_minvalue = $a[min($index)];
            $display_maxvalue = $a[max($index)];
            ($current_record['sdii_threshold_value'] != '') ? $display_threshold = $a[$current_record['sdii_threshold_value']] : $display_threshold = empty_nc('');
        }
    } else {
        $display_minvalue = empty_nc($current_record['sdii_min_value']);
        $display_maxvalue = empty_nc($current_record['sdii_max_value']);
        $display_threshold = empty_none(fnumber_format($current_record['sdii_threshold_value'], 'auto', false));

    }

    $fStyle = array('size' => FONT_SIZE_MIN);
    $cStyle = array('align' => 'center');

    $phpWord->addTableStyle('units', $styleTable, $firstRowStyle);
    $table = $section->addTable('units');


    // row 1 - header
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH / 4, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'min_value')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'max_value')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'initial_threshold_value')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'frequency')), 'arrayHeader', $cStyle);

    // row 2
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH / 4, $cellalign)->addText($display_minvalue, $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellalign)->addText($display_maxvalue, $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellalign)->addText($display_threshold, $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 4, $cellalign)->addText($current_record['sdii_frequency'], $fStyle, $cStyle);

    $section->addTextBreak(1);

    return true;
}

/**
 * indicator_display_provider()
 * Affiche les informations Fournisseurs d'un IDD
 *
 * @param  $current_record
 * @return bool true
 */
function indicator_display_provider($current_record, $bookmark_level)
{
    global $section;

    $lwidth = 0.29;

    $out_name = formatText($current_record['sdip_name']);
    if (!empty($current_record['sdip_service'])) $out_name .= ' - ' . formatText($current_record['sdip_service']);
    if (!empty($current_record['sdip_incharge'])) $out_name .= ' - ' . formatText($current_record['sdip_incharge']) . ' (' . _t('sdi', 'p_incharge') . ')';
    $out_adress = formatText(empty_nc(do_strip($current_record['sdip_address'])));
    $out_phone_fax = mb_ucfirst(_t('sdi', 'p_phone')) . ' : ' . formatText(empty_nc($current_record['sdip_phone']));
    $out_phone_fax .= ' - ' . mb_ucfirst(_t('sdi', 'p_fax')) . ' : ' . formatText(empty_nc($current_record['sdip_fax']));
    $out_email_item = mb_ucfirst(_t('sdi', 'p_email')) . ' : ';
    $out_email = formatText($current_record['sdip_email']);
    $out_desc = formatText(empty_nc(do_strip($current_record['sdip_description'])));
    $out_provider_item = formatText(ucfirst(_t('sdi', 'p_title'))) ;

    // $section->addTitle($out_provider_item, $bookmark_level+2);

    $styleTable = array('cellMargin' => 50);
    $table = $section->addTable($styleTable);

    $fStyle = array();
    $clStyle = array('align' => 'right');
    $crStyle = array('align' => 'left');

    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText('');
    $row->addCell(PAGE_WIDTH * (1 - $lwidth))->addText(do_strip($out_name), $fStyle, $crStyle);

    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText('');
    $s = $row->addCell(PAGE_WIDTH * (1 - $lwidth));

    if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['sdip_address'] = removeDomElement('img', $current_record['sdip_address']);
    if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($s, formatText(empty_nc(rmUnwantedTags(fixImageWidth($current_record['sdip_address'])))));
    else $s->addText($out_adress, $fStyle, $crStyle);

    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText('');
    $row->addCell(PAGE_WIDTH * (1 - $lwidth))->addText($out_phone_fax, $fStyle, $crStyle);

    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText('');
    $cell = $row->addCell(PAGE_WIDTH * (1 - $lwidth));
    $textrun = $cell->addTextRun();
    $textrun->addText($out_email_item, $fStyle);
    if (empty($out_email)) {
        $textrun->addText(empty_nc($out_email), $fStyle);
    } else {
        $textrun->addLink('mailto:' . $out_email, $out_email, $fStyle);
    }

    $section->addTextBreak(1);

    return true;
}

/**
 * indicator_display_rules()
 * Affiche la réglementation d'un IDD
 *
 * @param  $current_record
 * @return bool true
 */
function indicator_display_rules($current_record, $bookmark_level)
{
    global $section;

    $lwidth = 0.29;

    $out_name = formatText(empty_nc(do_strip($current_record['sdir_title']))) . PHP_EOL;
    $out_body = formatText(empty_nc(do_strip($current_record['sdir_body']))) . PHP_EOL;
    $out_uri = formatText(do_strip($current_record['sdir_mask_uri']));
    $out_uri_href = do_strip($current_record['sdir_referer_uri']);

    $out_name_item = mb_ucfirst(_t('sdi', 'r_title')) . ' : ';
    $out_body_item = mb_ucfirst(_t('sdi', 'r_body')) . ' : ';
    $out_uri_item = mb_ucfirst(_t('sdi', 'r_referer_uri')) . ' : ';


    $out_reglementation_item = formatText(ucfirst(_t('sdi', 'add_step4'))) ;

    // $section->addTitle($out_reglementation_item, $bookmark_level+2);

    $styleTable = array('cellMargin' => 50);
    $table = $section->addTable($styleTable);

    $fStyle = array();
    $clStyle = array('align' => 'right');
    $crStyle = array('align' => 'left');

    // nom de l'organisme fournisseur
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText($out_name_item, 'softLabel', $clStyle);
    $row->addCell(PAGE_WIDTH  * (1 - $lwidth))->addText($out_name, $fStyle, $crStyle);

    // détail reglementation
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText($out_body_item, 'softLabel', $clStyle);
    $s = $row->addCell(PAGE_WIDTH  * (1 - $lwidth));

    if(defined('EXPORT_NOIMAGE') && EXPORT_NOIMAGE == 1)  $current_record['sdir_body'] = removeDomElement('img', $current_record['sdir_body']);
    if(defined('EXCEL_HTML_EXPORT') && EXCEL_HTML_EXPORT == 1) \PhpOffice\PhpWord\Shared\Html::addHtml($s, formatText(empty_nc(rmUnwantedTags(fixImageWidth($current_record['sdir_body'])))));
    else $s->addText($out_body, $fStyle, $crStyle);

    // lien internet reglementation
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * $lwidth)->addText($out_uri_item, 'softLabel', $clStyle);
    if (!empty($out_uri_href)) {
        if (!empty($out_uri)) $row->addCell(PAGE_WIDTH  * (1 - $lwidth))->addLink($out_uri_href, $out_uri, $fStyle);
        else $row->addCell(PAGE_WIDTH  * (1 - $lwidth))->addLink($out_uri_href, $out_uri_href, $fStyle);
    } else {
        $row->addCell(PAGE_WIDTH  * (1 - $lwidth))->addText(empty_nc(''), $fStyle, $crStyle);
    }

    $section->addTextBreak(1);

    return true;
}

/**
 * indicator_display_assessment()
 * Affiche le tableau de critères d'évaluation
 *
 * @param int $current_record
 * @return bool true
 */
function indicator_display_assessment($current_record, $bookmark_level)
{
    global $section;
    global $phpWord;
    global $cellalign;
    global $cellHeaderStyle;
    global $styleTable;
    global $firstRowStyle;

    $out_eval_item = formatText(mb_ucfirst(_t('sdi', 'e_title'))) ;


    // $section->addTitle($out_eval_item, $bookmark_level+2);


    $phpWord->addTableStyle('evaluation', $styleTable, $firstRowStyle);
    $table = $section->addTable('evaluation');

    $fStyle = array('size' => FONT_SIZE_MIN);
    $cStyle = array('align' => 'center');

    // header
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_scale_compare')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_fiability')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_accessibility')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_lisibility')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_relevance')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'e_global_performance')), 'arrayHeader', $cStyle);

    // values
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_scale_compare'], $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_fiability'], $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_accessibility'], $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_lisibility'], $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_relevance'], $fStyle, $cStyle);
    $row->addCell(PAGE_WIDTH / 6, $cellalign)->addText($current_record['sdie_global_performance'], $fStyle, $cStyle);

    $section->addTextBreak(1);

    return true;
}


/**
 * indicator_display_values()
 * Affiche les valeurs d'un IDD.
 *
 * @param int $sdi_id
 * @return bool true
 **/

if(!function_exists('indicator_display_values')) {

    function indicator_display_values($sdi_id, $bookmark_level)
    {
        global $section;
        global $report_settings;
        global $scale_array;
        global $sql_object;
        global $scale_denomination_array;

        if(count($scale_array) == 0) return true;

        if ($report_settings['graphic_values'] === true && $report_settings['table_values'] === true) $mode = 'TWICE';
        elseif ($report_settings['graphic_values'] === true || $report_settings['table_values'] === true) $mode = 'ONE';
        else return false;

        $out_values_item = mb_ucfirst(_t('dashboard', 'all_value'));

        // $section->addTitle($out_values_item, $bookmark_level+2);

        $scale_no_values = array(); // recipient no values
        $scale_insuffisant_values = array(); // recipient insuffisant values graph


        for($i = 0; $i < count($scale_array); $i++) {

            // we print chart if asked
            if ($report_settings['graphic_values'] === true) {
                if (indicator_display_graphic_values($sdi_id, $scale_array[$i], indicator_get_status($report_settings['status']), $mode) === false) array_push($scale_insuffisant_values, $scale_array[$i]);
            }

            // we print values if asked
            if ($report_settings['table_values'] === true) {
                if (indicator_table_values($sdi_id, $scale_array[$i], indicator_get_status($report_settings['status']), $mode) === false) array_push($scale_no_values, $scale_array[$i]);
            }

        }
        indicator_no_values_for($scale_no_values, _t('report', 'no_values_for'));
        indicator_no_values_for($scale_insuffisant_values, _t('report', 'insuffisant_values'), $mode, $scale_no_values);

        return true;
    }

}


/**
 * indicator_get_status()
 * Formattage du statut pour requête SQL
 *
 * @param string $current_status
 * @return string $status
 **/
function indicator_get_status($current_status)
{
    switch ($current_status) {
        case 'ALL':
            $status = 'SCA';
            break;
        case 'PUBLIC':
            $status = 'SCP';
            break;
    }
    return $status;
}

/**
 * indicator_no_values_for()
 * Affiche les échelles ne contenant pas de valeurs ou insuffisamment
 *
 * @param array $array_scale
 * @param string $message
 * @param integer $mode
 * @param integer $array
 * @return bool true
 **/
function indicator_no_values_for($array_scale, $message, $mode = -1, $array = -1)
{
    global $section;
    global $scale_denomination_array;
    $content = '';
    $sep = '';
    // si mode TWICE et deuxième tableau fourni. On le soustrait au premier.
    if ($array != -1 && $mode == 'TWICE') $array_scale = array_values(array_diff($array_scale, $array));

    if (count($array_scale) == 0) return true;

    for($i = 0; $i < count($array_scale); $i++) {
        $content .= $sep . $scale_denomination_array[$array_scale[$i]];
        $sep = ', ';
    }
    if (empty($content)) return true;

    $textrun = $section->addTextRun();
    $textrun->addText($message, 'noValueFor');
    $textrun->addText($content, array());
    $section->addTextBreak(1);

    return true;
}

/**
 * indicator_display_graphic_values()
 * Gènère et affiche les valeurs sous forme graphique
 *
 * @param int $sdi_id
 * @param int $current_scale
 * @param string $status
 * @param string $mode
 * @return bool true
 **/
if(!function_exists('indicator_display_graphic_values')) {
    function indicator_display_graphic_values($sdi_id, $current_scale, $status, $mode)
    {
        global $section;
        global $sql_object;
        global $scale_denomination_array;

        // getting chart sizes
        list($width, $height) = explode('x', CHART_DEFAULT_SIZE);

        // We retrieve indicator type to see if we generate graph or not
        $result_sdii = $sql_object -> DBSelect(SQL_getInfoSdi($sdi_id));
        $indicator_type = $result_sdii[0]['sdii_nature'];


        include_once(override('../dashboard/graph.php'));

        $values = $sql_object->DBSelect(SQL_getAllValue($status, $current_scale, $sdi_id));

        // 	// renversement des valeurs pour affichage chronologique
        // 	if(is_array($values)) $values = @array_reverse ($values, false);

        // 	$result_sdii = $sql_object->DBSelect(SQL_getInfoSdi($sdi_id));

        if(defined('CHART_MIN_VALUES') && is_numeric(CHART_MIN_VALUES)) $minChartValues = CHART_MIN_VALUES; else $minChartValues = 2;

        if (!is_array($values) || count($values) < $minChartValues) 	return false;

        if($indicator_type == 'quantitative') {

            generateGraphic($sdi_id, $current_scale, $status, 'auto', false, $width, $height, '../tmp/report');

            // /////////////////////////////
            // Affichage

            // Affichage du titre
            $out_scale_item = $scale_denomination_array[$current_scale];

            $scaleStyle = array('bold' => true);
            $section->addText($out_scale_item, $scaleStyle);
            // $section->addTextBreak(1);

            // Affichage de l'image
            $section->addImage('../tmp/report/'.$sdi_id.'_'.$current_scale.'.png', array('align' => 'center', 'width' => \PhpOffice\PhpWord\Shared\Converter::cmToPixel(12)));

            $section->addTextBreak(1);
        }

        if($indicator_type == 'qualitative') {
            indicator_display_qualitative_array($values,$result_sdii[0]);
        }

        return true;

    }
}

/**
 * indicator_display_qualitative_array()
 * Dessine le tableau des valeurs qualitatives
 *
 * @param  $current_record
 * @return bool true
 */
function indicator_display_qualitative_array($values, $current_record, $dateFormat = 'short') {

    global $section;
    global $phpWord;

    $a = getBooleanValues($current_record);
    // get get color ramp
    $colors = getColorRamp($a);


    $styleTableQ = array('borderSize' => 0, 'borderColor' => 'ffffff',  'cellMargin' => 25, 'width' => 100);
    $firstRowStyle = array('bgColor' => ARRAY_HEADER_BG);

    $cStyle = array('align' => 'center', 'color' => '777777', 'size' => FONT_SIZE_MIN);


    $total = count($values);
    // if there is no result we quit the function
    if(!isset($values[0]['sdiv_value'])) return false;

    $section->addTextBreak(1);
    $phpWord->addTableStyle('qualitative-viz', $styleTableQ, $firstRowStyle);
    $table = $section->addTable('qualitative-viz');


    $values = array_reverse($values);

    // row 1 - header
    $row = $table->addRow();
    foreach ($values as $entry) {
        if($dateFormat == 'short') $date = substr(formatDate($entry['date_p'], true), 0, 4);
        else $date = $entry['date_p'];
        $row->addCell(PAGE_WIDTH / $total, array())->addText($date, $cStyle, array('align' => 'center'));

    }
    // row 2
    $row = $table->addRow();
    foreach ($values as $entry) {
        $fStyle = array('size' => FONT_SIZE_MIN, 'color' => $colors[$entry['sdiv_value']]['color']);
        $c2Style = array('align' => 'center', 'bgColor' => $colors[$entry['sdiv_value']]['background-color']);
        $row->addCell(PAGE_WIDTH / $total, $c2Style)->addText("\n\n".$a[$entry['sdiv_value']]."\n\n", $fStyle, array('align'=> 'center'));
    }

    $section->addTextBreak(2);

    // adding legend
    $table = $section->addTable('qualitative-viz-legend');
    $row = $table->addRow();
    for($i=0; $i<count($a); $i++) {
        $legendColorStyle = array('align' => 'center', 'bgColor' => $colors[$i]['background-color']);
        $row->addCell(PAGE_WIDTH / (count($a)*8), $legendColorStyle);
        $row->addCell(PAGE_WIDTH / (count($a)*1.7), array())->addText($a[$i], $cStyle, array('align' => 'left'));
    }
    $section->addTextBreak(2);

    return true;
}

/**
 * indicator_table_values()
 * Affiche un tableau de valeurs a une échelle donnée
 *
 * @param int $sdi_id
 * @param int $current_scale
 * @param string $status
 * @param string $mode
 * @return bool true
 **/
function indicator_table_values($sdi_id, $current_scale, $status, $mode)
{

    global $section;
    global $phpWord;
    global $sql_object;
    global $scale_denomination_array;
    global $cellalign;
    global $cellHeaderStyle;
    global $styleTable;
    global $firstRowStyle;

    $values = array();


    // setting $default_threshold value
    // By default initial value
    $result_sdii = $sql_object -> DBSelect(SQL_getInfoSdi($sdi_id));
    $default_threshold = $result_sdii[0]['sdii_threshold_value'];

    $sdi_values = $sql_object->DBSelect(SQL_getAllValue($status, $current_scale, $sdi_id));


    if (!is_array($sdi_values) || count($sdi_values) <= 1) return false;

    // we prepare values for displaying
    for($i = 0; $i < count($sdi_values); $i++) {

        if(!empty($sdi_values[$i]['sdiv_multivalue'])) {

            $data = unserialize($sdi_values[$i]['sdiv_multivalue']);
            $current_value = $data;

            if($indicator['sdii_multiple_type'] == 'sum') $cfield = '_total';
            if($indicator['sdii_multiple_type'] == 'mean') $cfield = '_mean';

            if($i == 0) {
                $lastValue = $data[0][$cfield]; // we store data to compute TCAM and global rate
                $year_end = substr(formatDate($sdi_values[$i]['date_p'], true), 0, 4);
            }
            if($i == count($sdi_values)-1) {
                $firstValue = $data[0][$cfield]; // we store data to compute TCAM and global rate
                $year_start = substr(formatDate($sdi_values[$i]['date_p'], true), 0, 4);
            }

        } else {

            $current_value = '';

            // If indicator is boolean or quantitative
            if($result_sdii[0]['sdii_nature'] == 'boolean' || $result_sdii[0]['sdii_nature'] == 'qualitative') {
                $a = getBooleanValues($result_sdii[0]);
                if($a) {
                    $current_value .= $a[$sdi_values[$i]['sdiv_value']];
                } else {
                    $current_value .= empty_none(fnumber_format($sdi_values[$i]['sdiv_value'], 'auto', false));
                }

            } else {
                $current_value .= fnumber_format($sdi_values[$i]['sdiv_value'], 'auto', false);

                if($i == 0) {
                    $lastValue = $sdi_values[$i]['sdiv_value']; // we store data to compute TCAM and global rate
                    $year_end = substr(formatDate($sdi_values[$i]['date_p'], true), 0, 4);
                }
                if($i == count($sdi_values)-1) {
                    $firstValue = $sdi_values[$i]['sdiv_value']; // we store data to compute TCAM and global rate
                    $year_start = substr(formatDate($sdi_values[$i]['date_p'], true), 0, 4);
                }
            }

        }

        // if set, we get it from the table
        if(!is_null($sdi_values[$i]['sdiv_threshold'])) {
            // If indicator is boolean or quantitative
            if($result_sdii[0]['sdii_nature'] == 'boolean' || $result_sdii[0]['sdii_nature'] == 'qualitative') {
                $threshold = $a[$sdi_values[$i]['sdiv_threshold']];
            } else {
                $threshold = empty_none(fnumber_format($sdi_values[$i]['sdiv_threshold'], 'auto', false));
            }

        } else {
            // If indicator is boolean or quantitative
            if($result_sdii[0]['sdii_nature'] == 'boolean' || $result_sdii[0]['sdii_nature'] == 'qualitative') {
                if(!is_null($default_threshold)) $threshold = $a[$default_threshold];
                else $threshold = empty_none('');
            } else {
                $threshold = empty_none('');
            }
        }

        if ($sdi_values[$i]['sdiv_statut'] == 'D') $current_value .= ' *';
        if (!empty($sdi_values[$i]['sdiv_comment'])) {
            if($sdi_values[$i]['sdiv_comment_display']=='Y') {
                $comment_status = _t('dashboard', 'public');
            } else {
                $comment_status = _t('dashboard', 'private');
            }

            $comments = formatText(do_strip($sdi_values[$i]['sdiv_comment'])) .' ('.$comment_status.')'.PHP_EOL;

        } else {
            $comments = '';
        }

        $current_date = formatText($sdi_values[$i]['date_p']);

        $values[$i]['current'] = $current_value;
        $values[$i]['threshold'] = $threshold;
        $values[$i]['date'] = $current_date;
        $values[$i]['comment'] = $comments;

    }

    // Affichage des données

    // Affichage du titre seulement si affichage des valeurs en standalone
    // ou si la génération des graphiques est infaisables car moins de 3 valeurs
    if ($mode != 'TWICE' || (count($sdi_values) >= 1 && count($sdi_values) < 3)) {
        $out_scale_item = $scale_denomination_array[$current_scale];

        $scaleStyle = array('bold' => true);
        $section->addText($out_scale_item, $scaleStyle);
    }

    // Affichage de l'astérisque - avertissement
    $section->addText('* ' . strtolower(_t('statut', 'draftpdf')).' ', array('size' => FONT_SIZE_MIN, 'italic' => true, 'color' => '555555'), array('align' => 'right'));

    $phpWord->addTableStyle('values', $styleTable, $firstRowStyle);
    $table = $section->addTable('values');

    $fStyle = array('size' => FONT_SIZE_MIN);
    $cStyle = array('align' => 'center');
    $clStyle = array('align' => 'left');


    // header
    $row = $table->addRow();
    $row->addCell(PAGE_WIDTH * 0.4, $cellHeaderStyle)->addText(mb_ucfirst(_t('dashboard', 'name_pluriel')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH * 0.2, $cellHeaderStyle)->addText(mb_ucfirst(_t('dashboard', 'threshold_value')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH * 0.2, $cellHeaderStyle)->addText(mb_ucfirst(_t('statut', 'published_on')), 'arrayHeader', $cStyle);
    $row->addCell(PAGE_WIDTH * 0.2, $cellHeaderStyle)->addText(mb_ucfirst(_t('sdi', 'comment')), 'arrayHeader', $cStyle);

    // values
    foreach ($values as &$val) {
        $row = $table->addRow();

        $cell = $row->addCell(PAGE_WIDTH * 0.4, $cellalign);

        if(is_array($val['current'])) {
            foreach($val['current'] as &$el) {

                $textrun = $cell->addTextRun();

                if(!empty($el['label'])) $disp = mb_ucfirst($el['label']). ' : ';
                $disp .= $el['value'];

                $textrun->addText($disp, 'arrayValue');
                if($result_sdii[0]['sdii_multiple_type'] == 'sum') $textrun->addText(' ('.fnumber_format($el['_percentage'], 2, false).' %)', 'percentageValue');

            }
            // we display the total if needed
            if($result_sdii[0]['sdii_multiple_type'] == 'sum') $cell->addText(mb_ucfirst(esc(_t('dashboard', 'multivalue_total')) . ' : ' . fnumber_format($val['current'][0]['_total'], 'auto', false)), array('size' => FONT_SIZE_MIN, 'bold' => true));
            // we display the mean if needed
            if($result_sdii[0]['sdii_multiple_type'] == 'mean') $cell->addText(mb_ucfirst(esc(_t('dashboard', 'multivalue_mean')) . ' : ' . fnumber_format($val['current'][0]['_mean'], 2, false)), array('size' => FONT_SIZE_MIN, 'bold' => true));



        } else {
            $cell->addText($val['current'], 'arrayValue', $cStyle);
        }


        $row->addCell(PAGE_WIDTH * 0.2, $cellalign)->addText($val['threshold'], $fStyle, $cStyle);
        $row->addCell(PAGE_WIDTH * 0.2, $cellalign)->addText($val['date'], $fStyle, $cStyle);
        $row->addCell(PAGE_WIDTH * 0.2, $cellalign)->addText($val['comment'], $fStyle, $clStyle);
    }

    // affichage des taux
    if($result_sdii[0]['sdii_nature'] == 'quantitative') {
        $rates = getRates($firstValue, $lastValue, $year_start, $year_end, 'txt');
        $section->addTextBreak(1);
        $section->addText($rates, array('size' => FONT_SIZE_MIN, 'italic' => false));
    }
    $section->addTextBreak(2);

    return true;
}

/**
 * do_strip()
 * @param string $content
 * @return string
 */
function do_strip($content, $force = false) {

    global $report_settings;

    $replace_array = array("\r\n", "\n\r", "\\r\\n", "\\n\\r", "\r", "\\r", "\\n");


    if($report_settings['export_format'] === 'html' && $force !== true) {

        return $content;

    } else {

        $converted = \Soundasleep\Html2Text::convert($content);

        // @see https://github.com/PHPOffice/PHPWord/issues/384
        // this will convert all "\n" to new lines when generating Word files
        $str = str_replace($replace_array,"\n", $converted); // cleaning new line syntax
        $str = htmlspecialchars($str, ENT_NOQUOTES, CHARSET);
        return $str;
    }
}


?>