class_modules_tools_Abstract.php

<?php
/*
*   Copyright 2008-2016 Maarch and Document Image Solutions
*
*   This file is part of Maarch Framework.
*
*   Maarch Framework is free software: you can redistribute it and/or modify
*   it under the terms of the GNU General Public License as published by
*   the Free Software Foundation, either version 3 of the License, or
*   (at your option) any later version.
*
*   Maarch Framework is distributed in the hope that it will be useful,
*   but WITHOUT ANY WARRANTY; without even the implied warranty of
*   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*   GNU General Public License for more details.
*
*   You should have received a copy of the GNU General Public License
*   along with Maarch Framework.  If not, see <http://www.gnu.org/licenses/>.
*/

/**
 * @brief Contains the functions to manage visa and notice workflow.
 *
 * @file
 *
 * @author Nicolas Couture <couture@docimsol.com>
 * @date $date$
 *
 * @version $Revision$
 * @ingroup visa
 */
define('FPDF_FONTPATH', $core_path.'apps/maarch_entreprise/tools/pdfb/fpdf_1_7/font/');
require $core_path.'apps/maarch_entreprise/tools/pdfb/fpdf_1_7/fpdf.php';
require $core_path.'apps/maarch_entreprise/tools/pdfb/fpdf_1_7/fpdi.php';

abstract class visa_Abstract extends Database
{
    public $errorMessageVisa;

    /***
    * Build Maarch module tables into sessions vars with a xml configuration file
    *
    *
    */
    public function build_modules_tables()
    {
        if (file_exists(
            $_SESSION['config']['corepath'].'custom'.DIRECTORY_SEPARATOR
            .$_SESSION['custom_override_id'].DIRECTORY_SEPARATOR.'modules'
            .DIRECTORY_SEPARATOR.'visa'.DIRECTORY_SEPARATOR.'xml'
            .DIRECTORY_SEPARATOR.'config.xml'
        )
        ) {
            $configPath = $_SESSION['config']['corepath'].'custom'
                        .DIRECTORY_SEPARATOR.$_SESSION['custom_override_id']
                        .DIRECTORY_SEPARATOR.'modules'.DIRECTORY_SEPARATOR
                        .'visa'.DIRECTORY_SEPARATOR.'xml'
                        .DIRECTORY_SEPARATOR.'config.xml';
        } else {
            $configPath = 'modules'.DIRECTORY_SEPARATOR.'visa'
                        .DIRECTORY_SEPARATOR.'xml'.DIRECTORY_SEPARATOR
                        .'config.xml';
        }

        $xmlconfig = simplexml_load_file($configPath);
        $conf = $xmlconfig->CONFIG;
        $_SESSION['modules_loaded']['visa']['exeSign'] = (string) $conf->exeSign;
        $_SESSION['modules_loaded']['visa']['showAppletSign'] = (string) $conf->showAppletSign;
        $_SESSION['modules_loaded']['visa']['reason'] = (string) $conf->reason;
        $_SESSION['modules_loaded']['visa']['location'] = (string) $conf->location;
        $_SESSION['modules_loaded']['visa']['licence_number'] = (string) $conf->licence_number;

        $_SESSION['modules_loaded']['visa']['width_blocsign'] = (string) $conf->width_blocsign;
        $_SESSION['modules_loaded']['visa']['height_blocsign'] = (string) $conf->height_blocsign;

        $_SESSION['modules_loaded']['visa']['confirm_sign_by_email'] = (string) $conf->confirm_sign_by_email;

        $routing_template = (string) $conf->routing_template;

        if (file_exists(
            $_SESSION['config']['corepath'].'custom'.DIRECTORY_SEPARATOR
            .$_SESSION['custom_override_id'].DIRECTORY_SEPARATOR.'modules'
            .DIRECTORY_SEPARATOR.'visa'.DIRECTORY_SEPARATOR.'Bordereau_visa_modele.pdf'
        )
        ) {
            $routing_template = $_SESSION['config']['corepath'].'custom'.DIRECTORY_SEPARATOR
            .$_SESSION['custom_override_id'].DIRECTORY_SEPARATOR.'modules'
            .DIRECTORY_SEPARATOR.'visa'.DIRECTORY_SEPARATOR.'Bordereau_visa_modele.pdf';
        }

        $_SESSION['modules_loaded']['visa']['routing_template'] = $routing_template;
    }

    public function checkResponseProject($res_id, $coll_id)
    {
        $this->errorMessageVisa = null;

        $attachmentTypes = \Attachment\models\AttachmentModel::getAttachmentsTypesByXML();

        $noSignableAttachments = [];
        foreach ($attachmentTypes as $key => $value) {
            if (!$value['sign']) {
                $noSignableAttachments[] = $key;
            }
        }

        $db = new Database();
        if (empty($noSignableAttachments)) {
            $stmt = $db->query("SELECT * FROM res_attachments WHERE res_id_master = ? AND status NOT IN ('DEL','OBS','TMP') AND in_signature_book = ?", [$res_id, true]);
        } else {
            $stmt = $db->query("SELECT * FROM res_attachments WHERE res_id_master = ? AND status NOT IN ('DEL','OBS','TMP') AND attachment_type NOT IN (?) AND in_signature_book = ? ", [$res_id, $noSignableAttachments, true]);
        }
        if ($stmt->rowCount() <= 0) {
            $this->errorMessageVisa = _NO_RESPONSE_PROJECT_VISA;

            return false;
        }

        return true;
    }

    public function getWorkflow($res_id, $coll_id, $typeList)
    {
        require_once 'modules/entities/class/class_manage_listdiff.php';
        $listdiff = new diffusion_list();
        $roles = $listdiff->list_difflist_roles();
        $circuit = $listdiff->get_listinstance($res_id, false, $typeList);
        if (isset($circuit['copy'])) {
            unset($circuit['copy']);
        }

        return $circuit;
    }

    public function saveWorkflow($res_id, $coll_id, $workflow, $typeList)
    {
        require_once 'modules/entities/class/class_manage_listdiff.php';
        $diff_list = new diffusion_list();

        $diff_list->save_listinstance(
            $workflow,
            $typeList,
            $coll_id,
            $res_id,
            $_SESSION['user']['UserId'],
            $_SESSION['user']['primaryentity']['id']
        );
    }

    public function saveModelWorkflow($id_list, $workflow, $typeList, $title)
    {
    }

    protected function getWorkflowsNumberByTitle($title)
    {
        $db = new Database();
        $stmt = $db->query('SELECT * FROM listmodels WHERE title = ?', array($title));

        return $stmt->rowCount();
    }

    public function isWorkflowTitleFree($title)
    {
        $nb = $this->getWorkflowsNumberByTitle($title);
        if ($nb == 0) {
            return true;
        } else {
            return false;
        }
    }

    public function nbVisa($res_id, $coll_id)
    {
        $db = new Database();
        $stmt = $db->query('SELECT listinstance_id from listinstance WHERE res_id= ? and coll_id = ? and item_mode = ?', array($res_id, $coll_id, 'visa'));

        return $stmt->rowCount();
    }

    public function getCurrentStep($res_id, $coll_id, $listDiffType)
    {
        $db = new Database();
        $where = 'res_id= ? and coll_id = ? and difflist_type = ? and process_date IS NULL';
        $order = 'ORDER BY listinstance_id ASC';
        $query = $db->limit_select(0, 1, 'sequence, item_mode', 'listinstance', $where, '', '', $order);

        $stmt = $db->query($query, array($res_id, $coll_id, $listDiffType));
        $res = $stmt->fetchObject();
        if ($res->item_mode == 'sign') {
            return $this->nbVisa($res_id, $coll_id);
        }

        return $res->sequence;
    }

    public function getUsersCurrentVis($res_id)
    {
        $db = new Database();
        $result = array();
        $stmt = $db->query("SELECT item_id from listinstance WHERE res_id= ? and difflist_type = 'VISA_CIRCUIT'  ORDER BY sequence ASC", array($res_id));
        while ($res = $stmt->fetchObject()) {
            $result[] = $res->item_id;
        }

        return $result;
    }

    public function getCurrentUserStep($res_id)
    {
        $db = new Database();
        $stmt = $db->query('SELECT item_id from listinstance WHERE res_id= ? and coll_id = ? and difflist_type = ? and process_date ISNULL ORDER BY listinstance_id ASC LIMIT 1', array($res_id, 'letterbox_coll', 'VISA_CIRCUIT'));
        $res = $stmt->fetchObject();

        return $res->item_id;
    }

    public function getStepDetails($res_id, $coll_id, $listDiffType, $sequence)
    {
        $stepDetails = array();
        $db = new Database();
        $order = 'ORDER by listinstance_id ASC';
        $where = 'res_id= ? and coll_id = ? and difflist_type = ? and sequence = ? ';
        $query = $db->limit_select(0, 1, '*', 'listinstance', $where, '', '', $order);

        $stmt = $db->query($query, array($res_id, $coll_id, $listDiffType, $sequence));

        $res = $stmt->fetchObject();
        $stepDetails['listinstance_id'] = $res->listinstance_id;
        $stepDetails['coll_id'] = $res->coll_id;
        $stepDetails['res_id'] = $res->res_id;
        $stepDetails['sequence'] = $res->sequence;
        $stepDetails['item_id'] = $res->item_id;
        $stepDetails['item_type'] = $res->item_type;
        $stepDetails['item_mode'] = $res->item_mode;
        $stepDetails['added_by_user'] = $res->added_by_user;
        $stepDetails['visible'] = $res->visible;
        $stepDetails['viewed'] = $res->viewed;
        $stepDetails['difflist_type'] = $res->difflist_type;
        $stepDetails['process_date'] = $res->process_date;
        $stepDetails['process_comment'] = $res->process_comment;

        return $stepDetails;
    }

    public function processVisaWorkflow($aArgs = [])
    {
        $message = [];
        $db = new Database();
        //enables to process the visa if i am not the item_id
        if ($aArgs['stepDetails']['item_id'] != $_SESSION['user']['UserId']) {
            $db->query(
                'UPDATE listinstance SET process_date = CURRENT_TIMESTAMP '
                .' WHERE listinstance_id = ? AND item_mode = ? AND res_id = ? AND item_id = ? AND difflist_type = ?',
                array($aArgs['stepDetails']['listinstance_id'], $aArgs['stepDetails']['item_mode'], $aArgs['res_id'], $aArgs['stepDetails']['item_id'], 'VISA_CIRCUIT')
            );

            $stmt = $db->query('SELECT firstname, lastname, user_id FROM users WHERE user_id IN (?)', array([$_SESSION['user']['UserId'], $aArgs['stepDetails']['item_id']]));
            foreach ($stmt as $value) {
                if ($value['user_id'] == $_SESSION['user']['UserId']) {
                    $user1 = $value['firstname'].' '.$value['lastname'];
                } else {
                    $user2 = $value['firstname'].' '.$value['lastname'];
                }
            }

            $message[] = ' '._VISA_BY.' '.$user1.' '._INSTEAD_OF.' '.$user2;
        } else {
            $db->query(
                'UPDATE listinstance SET process_date = CURRENT_TIMESTAMP '
                .' WHERE listinstance_id = ? AND item_mode = ? AND res_id = ? AND item_id = ? AND difflist_type = ?',
                array($aArgs['stepDetails']['listinstance_id'], $aArgs['stepDetails']['item_mode'], $aArgs['res_id'], $_SESSION['user']['UserId'], 'VISA_CIRCUIT')
            );
            $message[] = '';
        }

        return $message;
    }

    public function getUsersVis($group_id = null)
    {
        $db = new Database();

        if ($group_id != null) {
            $stmt = $db->query("SELECT users.user_id, users.firstname, users.lastname, usergroup_content.group_id,entities.entity_id from users, usergroup_content, users_entities,entities WHERE users_entities.user_id = users.user_id and users.status <> 'DEL' and 
				users_entities.primary_entity = 'Y' and users.user_id = usergroup_content.user_id AND entities.entity_id = users_entities.entity_id AND group_id IN 
				(SELECT group_id FROM usergroups_services WHERE service_id = ? AND group_id = ?)  order by users.lastname", array('visa_documents', $group_id));
        } else {
            $stmt = $db->query("SELECT distinct on(users.user_id) users.user_id, users.firstname, users.lastname, usergroup_content.group_id,entities.entity_id from users, usergroup_content, users_entities,entities WHERE users_entities.user_id = users.user_id and users.status <> 'DEL' and 
				users_entities.primary_entity = 'Y' and users.user_id = usergroup_content.user_id AND entities.entity_id = users_entities.entity_id AND group_id IN 
				(SELECT group_id FROM usergroups_services WHERE service_id = ?)  
				order by users.user_id,users.lastname", array('visa_documents'));
        }

        $tab_users = array();

        while ($res = $stmt->fetchObject()) {
            array_push($tab_users, array('id' => $res->user_id, 'firstname' => $res->firstname, 'lastname' => $res->lastname, 'group_id' => $res->group_id, 'entity_id' => $res->entity_id));
        }

        return $tab_users;
    }

    public function getEntityVis()
    {
        $db = new Database();

        $stmt = $db->query("SELECT distinct(entities.entity_id) FROM users, usergroup_content, users_entities,entities WHERE users_entities.user_id = users.user_id and 
			users_entities.primary_entity = 'Y' and users.user_id = usergroup_content.user_id AND entities.entity_id = users_entities.entity_id AND group_id IN 
			(SELECT group_id FROM usergroups_services WHERE service_id = ?)  
			order by entities.entity_id", array('visa_documents'));

        $tab_userentities = array();

        while ($res = $stmt->fetchObject()) {
            array_push($tab_userentities, array('entity_id' => $res->entity_id));
        }

        return $tab_userentities;
    }

    public function setStatusVisa($res_id, $coll_id, $inDetails = false)
    {
        $curr_visa_wf = $this->getWorkflow($res_id, $coll_id, 'VISA_CIRCUIT');

        $db = new Database();
        $where = 'res_id= ? and coll_id = ? and difflist_type = ? and process_date IS NULL';
        $order = 'ORDER BY listinstance_id ASC';
        $query = $db->limit_select(0, 1, 'requested_signature', 'listinstance', $where, '', '', $order);

        $stmt = $db->query($query, array($res_id, $coll_id, 'VISA_CIRCUIT'));
        $resListDiffVisa = $stmt->fetchObject();

        // If there is only one step in the visa workflow, we set status to ESIG
        if ($resListDiffVisa->requested_signature) {
            $mailStatus = 'ESIG';
        } else {
            $mailStatus = 'EVIS';
        }

        $db->query('UPDATE res_letterbox SET status = ? WHERE res_id = ? ', array($mailStatus, $res_id));
    }

    public function getList($res_id, $coll_id, $bool_modif = false, $typeList, $isVisaStep = false, $fromDetail = '')
    {
        $core = new core_tools();
        $circuit = $this->getWorkflow($res_id, $coll_id, $typeList);
        $sAllAttachmentSigned = $this->isAllAttachementSigned($res_id);
        if ($sAllAttachmentSigned == 'noAttachment') {
            $str = '<input type="hidden" id="isAllAttachementSigned" value="false"/>';
            $isAllAttachementSigned = '';
            $isAllAttachementSignedInfo = '';
        } elseif ($sAllAttachmentSigned == 'yes') {
            $str = '<input type="hidden" id="isAllAttachementSigned" value="allsigned"/>';
            $isAllAttachementSigned = '';
            $isAllAttachementSignedInfo = _IS_ALL_ATTACHMENT_SIGNED_INFO2;
        } else {
            $str = '<input type="hidden" id="isAllAttachementSigned" value="false"/>';
            $isAllAttachementSigned = '';
        }

        $str .= '<div class="error" id="divErrorVisa" onclick="this.hide();"></div>';
        $str .= '<div class="info" id="divInfoVisa" onclick="this.hide();"></div>';

        //VISA USER LIST
        if ($bool_modif == true) {
            $str .= '<select data-placeholder="'._ADD_VISA_ROLE.'" id="visaUserList" onchange="addVisaUser();">';
            $str .= '<option value="" ></option>';

            $tab_userentities = $this->getEntityVis();
            $tab_users = $this->getUsersVis();
            /* Order by parent entity **/
            foreach ($tab_userentities as $key => $value) {
                $str .= '<optgroup label="'.$tab_userentities[$key]['entity_id'].'">';
                foreach ($tab_users as $user) {
                    if ($tab_userentities[$key]['entity_id'] == $user['entity_id']) {
                        $selected = ' ';
                        if ($user['id'] == $step['user_id']) {
                            $selected = ' selected';
                        }
                        $str .= '<option value="'.$user['id'].'" '.$selected.'>'.$user['lastname'].' '.$user['firstname'].'</option>';
                    }
                }
                $str .= '</optgroup>';
            }
            $str .= '</select>';
            $str .= '<script>';
            $str .= ' $j("#visaUserList").chosen({width: "250px", disable_search_threshold: 10});';
            $str .= '</script>';

            $str .= ' <select data-placeholder="'._ADD_VISA_MODEL.'" name="modelList" id="modelList" onchange="loadVisaModelUsers();">';
            $str .= '<option value=""></option>';
            $str .= '</select>';

            $str .= '<script>';
            $str .= ' $j("#modelList").chosen({width: "250px", disable_search_threshold: 10});';
            $str .= '</script>';
            $str .= '<br/><br/>';
        }
        if (!empty($isAllAttachementSignedInfo)) {
            $str .= '<b style="color:red;">'.$isAllAttachementSignedInfo.'</b>';
        }
        $str .= '<div id="visa_content">';
        //VISA USER IN DOCUMENT
        $i = 1;
        $lastUserVis = true;

        if ((empty($circuit['visa']['users']) || !is_array($circuit['visa']['users']) || count($circuit['visa']['users']) == 0) && (empty($circuit['sign']['users']) || !is_array($circuit['sign']['users']) || count($circuit['sign']['users']) == 0)) {
            $str .= '<div id="emptyVisa"><strong><em>'._EMPTY_VISA_WORKFLOW.'</em></strong></div>';
        } else {
            $str .= '<div id="emptyVisa" style="display:none;"><strong><em>'._EMPTY_VISA_WORKFLOW.'</em></strong></div>';
            if (!empty($circuit['visa']['users']) && is_array($circuit['visa']['users']) && count($circuit['visa']['users']) > 0) {
                $isCurrentVisa = false;
                foreach ($circuit['visa']['users'] as $it => $info_userVis) {
                    if (empty($info_userVis['process_date'])) {
                        if ($lastUserVis == true && $isVisaStep == true && $isCurrentVisa === false) {
                            $vised = ' currentVis';
                            $disabled = '';
                            $link_vis = 'arrow-right ';
                            $del_vis = '<div class="delete_visa"></div>';
                            if ($info_userVis['requested_signature'] && $info_userVis['user_id'] != $_SESSION['user']['UserId']) {
                                $info_vised = '<p style="color:red;">'._SIGN_USER_COU_DESC.' '.$info_userVis['firstname'].' '.$info_userVis['lastname'].'</p>';
                                $dropZone = '';
                            } elseif ($info_userVis['requested_signature'] && $info_userVis['user_id'] == $_SESSION['user']['UserId']) {
                                $info_vised = '<p style="font-weight:normal;">'._SIGN_USER_COU.'</p>';
                                $dropZone = '';
                            } elseif (!$info_userVis['requested_signature'] && $info_userVis['user_id'] != $_SESSION['user']['UserId']) {
                                $info_vised = '<p style="color:red;">'._VISA_USER_COU_DESC.' '.$info_userVis['firstname'].' '.$info_userVis['lastname'].'</p>';
                                $dropZone = '';
                            } else {
                                $info_vised = '<p style="font-weight:normal;">'._VISA_USER_COU.'</p>';
                                $dropZone = '';
                            }
                            if ($core->test_service('modify_visa_in_signatureBook', 'visa', false)) {
                                $modif = 'true';
                                $dropZone = '<i class="fa fa-exchange-alt fa-2x fa-rotate-90" aria-hidden="true" title="'._DRAG_N_DROP_CHANGE_ORDER.'" style="cursor: pointer"></i>';
                                $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                            } else {
                                $modif = 'false';
                            }

                            $info_vised .= '<select style="display:none;" id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            $info_vised .= ' disabled="disabled" ';
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';

                            $info_vised .= '<option value="true"';
                            if (!empty($info_userVis['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
                        } else {
                            $dropZone = '<i class="fa fa-exchange-alt fa-2x fa-rotate-90" aria-hidden="true" title="'._DRAG_N_DROP_CHANGE_ORDER.'" style="cursor: pointer"></i>';
                            $vised = '';
                            if ($bool_modif == true) {
                                $modif = 'true';
                                $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                                $disabled = '';
                            } else {
                                $modif = 'false';
                                $dropZone = '';
                                $del_vis = '';
                                $disabled = ' disabled="disabled"';
                            }

                            $info_vised = '<br/><select id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            if (!empty($info_userVis['signatory'])) {
                                $info_vised .= ' disabled="disabled" ';
                            }
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';

                            $info_vised .= '<option value="true"';
                            if (!empty($info_userVis['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
                            $link_vis = 'hourglass-half';
                        }

                        $lastUserVis = false;
                        $isCurrentVisa = true;
                    } else {
                        $lastUserVis = true;
                        $modif = 'false';

                        $disabled = ' disabled="disabled"';
                        if (preg_match("/\[DEL\]/", $info_userVis['process_comment'])) {
                            $info_vised = '<br/><select id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            if (!empty($info_userVis['signatory'])) {
                                $info_vised .= ' disabled="disabled" ';
                            }
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';

                            $info_vised .= '<option value="true"';
                            if (!empty($info_userVis['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
                            $link_vis = 'times';
                            $vised = ' moved vised';
                            $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                        } else {
                            if (!empty($info_userVis['signatory'])) {
                                $info_vised = '<br/><sub>signé le : '.functions::format_date_db($info_userVis['process_date'], '', '', true).'</sub>';
                                $info_vised .= '<br/><select id="signRequest_'.$i.'" style="width:auto;display:none;" disabled="disabled" '.$isAllAttachementSigned;
                                $info_vised .= '>';
                                $info_vised .= '<option value="false" selected="selected">'._VISA_USER_SEARCH.'</option>';

                                $info_vised .= '<option value="true"';
                                $info_vised .= '>'._SIGNATORY.'</option>';
                                $info_vised .= '</select>';
                            } else {
                                $info_vised = '<br/><sub>visé le : '.functions::format_date_db($info_userVis['process_date'], '', '', true).'</sub>';

                                $info_vised .= '<br/><select id="signRequest_'.$i.'" style="width:auto;display:none;" disabled="disabled" '.$isAllAttachementSigned;
                                $info_vised .= '>';
                                $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';

                                $info_vised .= '<option value="true" selected="selected"';
                                $info_vised .= '>'._SIGNATORY.'</option>';
                                $info_vised .= '</select>';
                            }

                            $link_vis = 'check';
                            $vised = ' vised';
                            $del_vis = '';
                        }
                    }
                    //VISA USER LINE CIRCUIT
                    $str .= '<div class="droptarget'.$vised.'" id="visa_'.$i.'" draggable="'.$modif.'">';
                    $str .= '<span class="visaUserStatus">';
                    $str .= '<i class="fa fa-'.$link_vis.'" aria-hidden="true"></i>';
                    $str .= '</span>';
                    $str .= '<span class="visaUserInfo">';
                    $str .= '<sup class="visaUserPos nbResZero">'.$i.'</sup>&nbsp;&nbsp;';
                    $str .= '<i class="fa fa-user fa-2x" aria-hidden="true"></i> '.$info_userVis['lastname'].' '.$info_userVis['firstname'].' <sup class="nbRes">'.$info_userVis['entity_id'].'</sup>';
                    $str .= '&nbsp;&nbsp; <sub><i id="signedUser_'.$i.'" title="au moins un document a été signé par cet utilisateur" class="visaUserSign fa fa-certificate" aria-hidden="true" style="color:#F99830;';
                    if (empty($info_userVis['signatory'])) {
                        $str .= 'visibility:hidden';
                    }
                    $str .= '"></i>'.$info_vised;
                    $str .= '</span>';
                    $str .= '<span class="visaUserAction">';
                    $str .= $del_vis;
                    $str .= '</span>';
                    $str .= '<span class="visaUserConsigne">';
                    $str .= '<input class="userId" type="hidden" value="'.$info_userVis['user_id'].'"/><input class="visaDate" type="hidden" value="'.$info_userVis['process_date'].'"/><input'.$disabled.' class="consigne" type="text" value="'.$info_userVis['process_comment'].'"/>';
                    $str .= '</span>';

                    $str .= '<span id="dropZone">';
                    $str .= $dropZone;
                    $str .= '</span>';
                    $str .= '</div>';

                    ++$i;
                }
            }

            //FOR USER SIGN
            if (!empty($circuit['sign']['users'])) {
                foreach ($circuit['sign']['users'] as $info_userSign) {
                    if (empty($info_userSign['process_date'])) {
                        if (($lastUserVis == true && $isVisaStep == true)) {
                            $vised = ' currentVis';
                            $modif = 'false';
                            $disabled = '';
                            $del_vis = '';
                            $link_vis = 'arrow-right ';
                            if ($info_userSign['requested_signature'] && $info_userSign['user_id'] != $_SESSION['user']['UserId']) {
                                $dropZone = '';
                                $info_vised = '<p style="color:red;">'._SIGN_USER_COU_DESC.' '.$info_userSign['firstname'].' '.$info_userSign['lastname'].'</p>';
                            } elseif ($info_userSign['requested_signature'] && $info_userSign['user_id'] == $_SESSION['user']['UserId']) {
                                $dropZone = '';
                                $info_vised = '<p style="font-weight:normal;">'._SIGN_USER_COU.'</p>';
                            } elseif (!$info_userSign['requested_signature'] && $info_userSign['user_id'] != $_SESSION['user']['UserId']) {
                                $dropZone = '';
                                $info_vised = '<p style="color:red;">'._VISA_USER_COU_DESC.' '.$info_userSign['firstname'].' '.$info_userSign['lastname'].'</p>';
                            } else {
                                $dropZone = '';
                                $info_vised = '<p style="font-weight:normal;">'._VISA_USER_COU.'</p>';
                            }
                            if ($core->test_service('modify_visa_in_signatureBook', 'visa', false)) {
                                $modif = 'true';
                                $dropZone = '<i class="fa -alt fa-2x fa-rotate-90" aria-hidden="true" title="'._DRAG_N_DROP_CHANGE_ORDER.'" style="cursor: pointer"></i>';
                                $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                            } else {
                                $modif = 'false';
                            }
                            $info_vised .= '<select style="display:none;" id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            $info_vised .= ' disabled="disabled" ';
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';
    
                            $info_vised .= '<option value="true"';
                            if (!empty($info_userSign['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
                        } else {
                            $dropZone = '<i class="fa fa-exchange-alt fa-2x fa-rotate-90" aria-hidden="true" title="'._DRAG_N_DROP_CHANGE_ORDER.'" style="cursor: pointer"></i>';
                            $vised = '';
                            if ($bool_modif == true) {
                                $modif = 'true';
                                $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                                $disabled = '';
                            } else {
                                $dropZone = '';
                                $modif = 'false';
                                $del_vis = '';
                                $disabled = ' disabled="disabled"';
                            }
    
                            $info_vised = '<br/><select id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            if (!empty($info_userSign['signatory'])) {
                                $info_vised .= ' disabled="disabled" ';
                            }
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';
    
                            $info_vised .= '<option value="true"';
                            if (!empty($info_userSign['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
                            $link_vis = 'hourglass-half';
                        }
                    } else {
                        $modif = 'false';
                        if (preg_match("/\[DEL\]/", $info_userSign['process_comment'])) {
                            $info_vised = '<br/><select id="signRequest_'.$i.'" '.$isAllAttachementSigned;
                            if (!empty($info_userSign['signatory'])) {
                                $info_vised .= ' disabled="disabled" ';
                            }
                            $info_vised .= '>';
                            $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';
    
                            $info_vised .= '<option value="true"';
                            if (!empty($info_userSign['requested_signature'])) {
                                $info_vised .= ' selected="selected" ';
                            }
                            $info_vised .= '>'._SIGNATORY.'</option>';
                            $info_vised .= '</select>';
    
                            $link_vis = 'hourglass-half';
                            $link_vis = 'times';
                            $vised = ' moved vised';
                            $del_vis = '<i class="fa fa-trash-alt" aria-hidden="true" onclick="delVisaUser(this.parentElement.parentElement);" title="'._DELETE.'"></i>';
                        } else {
                            $vised = ' vised';
                            $link_vis = 'check';
                            
                            if (!empty($info_userSign['signatory'])) {
                                $info_vised = '<br/><sub>signé le : '.functions::format_date_db($info_userSign['process_date'], '', '', true).'</sub>';
    
                                $info_vised .= '<br/><select id="signRequest_'.$i.'" style="width:auto;display:none;" '.$isAllAttachementSigned;
                                $info_vised .= ' disabled="disabled" ';
                                $info_vised .= '>';
                                $info_vised .= '<option value="false">'._VISA_USER_SEARCH.'</option>';
                                $info_vised .= '<option value="true"';
                                $info_vised .= ' selected="selected" ';
                                $info_vised .= '>'._SIGNATORY.'</option>';
                                $info_vised .= '</select>';
                            } else {
                                $info_vised = '<br/><sub>visé le : '.functions::format_date_db($info_userSign['process_date'], '', '', true).'</sub>';
    
                                $info_vised .= '<br/><select id="signRequest_'.$i.'" style="width:auto;display:none;" '.$isAllAttachementSigned;
                                $info_vised .= ' disabled="disabled" ';
                                $info_vised .= '>';
                                $info_vised .= '<option value="false" selected="selected">'._VISA_USER_SEARCH.'</option>';
                                $info_vised .= '<option value="true"';
                                $info_vised .= '>'._SIGNATORY.'</option>';
                                $info_vised .= '</select>';
                            }
                        }
                    }
                    //VISA USER LINE CIRCUIT
                    $str .= '<div class="droptarget'.$vised.'" id="visa_'.$i.'" draggable="'.$modif.'">';
                    $str .= '<span class="visaUserStatus">';
                    $str .= '<i class="fa fa-'.$link_vis.'" aria-hidden="true"></i>';
                    $str .= '</span>';
                    $str .= '<span class="visaUserInfo">';
                    $str .= '<sup class="visaUserPos nbResZero">'.$i.'</sup>&nbsp;&nbsp;';
                    $str .= '<i class="fa fa-user fa-2x" aria-hidden="true"></i> '.$info_userSign['lastname'].' '.$info_userSign['firstname'].' <sup class="nbRes">'.$info_userSign['entity_id'].'</sup>';
                    $str .= '&nbsp;&nbsp; <sub><i id="signedUser_'.$i.'" title="au moins un document a été signé par cet utilisateur" class="visaUserSign fa fa-certificate" aria-hidden="true" style="color:#F99830;';
                    if (empty($info_userSign['signatory'])) {
                        $str .= 'visibility:hidden';
                    }
                    $str .= '"></i>'.$info_vised;
                    $str .= '</span>';
                    $str .= '<span class="visaUserAction">';
                    $str .= $del_vis;
                    $str .= '</span>';
                    $str .= '<span class="visaUserConsigne">';
                    $str .= '<input class="userId" type="hidden" value="'.$info_userSign['user_id'].'"/><input class="visaDate" type="hidden" value="'.$info_userSign['process_date'].'"/><input'.$disabled.' class="consigne" type="text" value="'.$info_userSign['process_comment'].'"/>';
                    $str .= '</span>';
                    $str .= '<span id="dropZone">';
                    $str .= $dropZone;
                    $str .= '</span>';
                    $str .= '</div>';
                    ++$i;
                }
            }
        }

        $str .= '</div>';

        if ($bool_modif == true) {
            //SAVE VISA CIRCUIT
            $str .= '<input type="button" name="send" id="send" value="'._SAVE_CHANGES.'" class="button" ';
            $str .= 'onclick="updateVisaWorkflow('.$res_id.');" /> ';

            //SAVE AS MODEL
            $str .= '<input type="button" name="save" id="save" value="Enregistrer comme modèle" class="button" onclick="$(\'modalSaveVisaModel\').style.display = \'block\';" />';
            $str .= '<div id="modalSaveVisaModel" >';
            $str .= '<h3>'._SAVE_POSITION.' '._VISA_WORKFLOW.'</h3><br/>';
            $str .= '<label for="titleModel">'._TITLE.'</label> ';
            $str .= '<input type="text" name="titleModel" id="titleModel"/><br/>';
            $str .= '<input type="button" name="saveModel" id="saveModel" value="'._VALIDATE.'" class="button" onclick="saveVisaWorkflowAsModel();" /> ';
            $str .= '<input type="button" name="cancelModel" id="cancelModel" value="'._CANCEL.'" class="button" onclick="$(\'modalSaveVisaModel\').style.display = \'none\';" />';
            $str .= '</div>';
        }
        $str .= '<script>initDragNDropVisa();</script>';

        return $str;
    }

    /* DOSSIER IMPRESSION */
    public function getJoinedFiles($coll_id, $table, $id, $from_res_attachment = false, $filter_attach_type = 'all')
    {
        $joinedFiles = array();
        $db = new Database();
        if ($from_res_attachment === false) {
            $stmt = $db->query(
                'select res_id, description, subject, title, format, filesize, relation, creation_date from '
                . " res_letterbox where res_id = ? and status <> 'DEL'",
                array($id)
            );
        } else {
            require_once 'modules/attachments/attachments_tables.php';
            if ($filter_attach_type == 'all') {
                $stmt = $db->query(
                    'select res_id, description, subject, title, format, filesize, res_id_master, attachment_type, creation_date, typist from '
                    ." res_attachments where res_id_master = ? and attachment_type <> 'converted_pdf' and attachment_type <> 'print_folder' and status <> 'DEL' order by attachment_type, creation_date",
                    array($id)
                );
            } else {
                $stmt = $db->query(
                    'select res_id, description, subject, title, format, filesize, res_id_master, attachment_type, creation_date, typist from '
                    .' res_attachments '
                    ." where res_id_master = ? and attachment_type = '"
                    .$filter_attach_type."' and status not in ('DEL', 'OBS') order by creation_date",
                    array($id)
                );
            }
        }

        while ($res = $stmt->fetchObject()) {
            $pdf_exist = true;
            if ($from_res_attachment) {
                require_once 'modules/attachments/class/attachments_controler.php';
                $ac = new attachments_controler();
                $idFile = $res->res_id;
                $convertedDocument =  \Convert\models\AdrModel::getConvertedDocumentById(['select' => ['docserver_id', 'path', 'filename'], 'type' => 'PDF', 'resId' => $idFile, 'collId' => 'attachments_coll']);
                $viewLink = $_SESSION['config']['businessappurl']
                        .'index.php?display=true&module=attachments&page=view_attachment&res_id_master='
                        .$id.'&id='.$res->res_id;
                
                if (!empty($convertedDocument)) {
                    $docserver = \Docserver\models\DocserverModel::getByDocserverId(['docserverId' => $convertedDocument['docserver_id'], 'select' => ['path_template']]);
                    $pathToDocument = $docserver['path_template'] . str_replace('#', DIRECTORY_SEPARATOR, $convertedDocument['path']) . $convertedDocument['filename'];
                    
                    
                    if (!file_exists($pathToDocument)) {
                        $pdf_exist = false;
                    }
                } else {
                    $pdf_exist = false;
                }
            } else {
                $idFile = $res->res_id;
                $convertedDocument =  \Convert\models\AdrModel::getConvertedDocumentById(['select' => ['docserver_id', 'path', 'filename'], 'type' => 'PDF', 'resId' => $idFile, 'collId' => 'letterbox_coll']);
                $viewLink = $_SESSION['config']['businessappurl']
                        .'index.php?display=true&dir=indexing_searching&page=view_resource_controler&id='
                        .$id;
                if (!empty($convertedDocument)) {
                    $docserver = \Docserver\models\DocserverModel::getByDocserverId(['docserverId' => $convertedDocument['docserver_id'], 'select' => ['path_template']]);
                    $pathToDocument = $docserver['path_template'] . str_replace('#', DIRECTORY_SEPARATOR, $convertedDocument['path']) . $convertedDocument['filename'];
                    
                    if (!file_exists($pathToDocument)) {
                        $pdf_exist = false;
                    }
                } else {
                    $pdf_exist = false;
                }
            }
            $label = '';
            //Tile, or subject or description
            if (strlen(trim($res->title)) > 0) {
                $label = $res->title;
            } elseif (strlen(trim($res->subject)) > 0) {
                $label = $res->subject;
            } elseif (strlen(trim($res->description)) > 0) {
                $label = $res->description;
            }

            if (isset($res->attachment_type) && $res->attachment_type != '') {
                $attachment_type = $res->attachment_type;
            } else {
                $attachment_type = '';
            }

            if (isset($res->typist) && $res->typist != '') {
                $typist = $res->typist;
            } else {
                $typist = '';
            }

            if ($pdf_exist == false) {
                $collIdConv = ($from_res_attachment) ? 'attachments_coll' : 'letterbox_coll';
                
                $viewLinkHtml = '<a id="gen_'.$idFile.'" style="cursor:pointer;" title="'._GENERATE_PDF .'" target="_blank" onclick="generatePdf(\''.$idFile.'\',\''.$collIdConv.'\')">'
                    .'<i id="spinner_'.$idFile.'" class="fa fa-sync-alt fa-2x" title="'._GENERATE_PDF.'"></i>'
                    .'</a>';
            } else {
                $viewLinkHtml = '';
            }
            array_push(
                $joinedFiles,
                array('id' => $idFile, //ID
                      'label' => $label, //Label
                      'format' => $res->format, //Format
                      'filesize' => $res->filesize, //Filesize
                      'creation_date' => $res->creation_date, //Filesize
                      'attachment_type' => $attachment_type, //attachment_type
                      'typist' => $typist, //attachment_type
                      'pdf_exist' => $pdf_exist,
                      'version' => '',
                      'viewLink' => $viewLinkHtml,
                    )
            );
        }

        return $joinedFiles;
    }

    public function showPrintFolder($coll_id, $table, $id)
    {
        require_once 'apps'.DIRECTORY_SEPARATOR.$_SESSION['config']['app_id']
        .DIRECTORY_SEPARATOR.'class'.DIRECTORY_SEPARATOR
        .'class_indexing_searching_app.php';
        $is = new indexing_searching_app();

        require_once 'apps'.DIRECTORY_SEPARATOR.$_SESSION['config']['app_id']
                .DIRECTORY_SEPARATOR.'class'.DIRECTORY_SEPARATOR
                .'class_users.php';

        $users_tools = new class_users();

        require_once 'core/class/class_request.php';

        $request = new request();

        require_once 'core/class/class_security.php';
        $sec = new security();
        $view = $sec->retrieve_view_from_coll_id($coll_id);
        $stmt = $this->query("select subject, contact_society, category_id from $view where res_id = ?", array($id));
        $res = $stmt->fetchObject();
        $str = '';
        $str .= '<div align="left" class="block">';
        $str .= '<div class="error" id="divErrorPrint" name="divErrorPrint" onclick="this.hide();"></div>';

        $str .= '<p><b>Requérant</b> : '.$res->contact_society.'</p>';
        $str .= '<p><b>'._SUBJECT.'</b> : '.$res->subject.'</p>';
        $str .= '<hr/>';
        $str .= '<form style="width:99%;" name="print_folder_form" id="print_folder_form" action="#" method="post">';
        $str .= '<table style="width:99%;" name="print_folder" id="print_folder" >';
        $str .= '<thead><tr><th style="width:25%;text-align:left;"></th><th style="width:40%;text-align:left;">Titre</th><th style="width:20%;text-align:left;">Rédacteur</th><th style="width:10%;text-align:left;">Date</th><th style="width:5%;text-align:left;"><input title="'._SELECT_ALL.'" id="allPrintFolder" type="checkbox" onclick="selectAllPrintFolder();"></th></tr></thead>';
        $str .= '<tbody>';

        if ($res->category_id != 'outgoing') {
            $str .= '<tr><td><h3>+ Courrier entrant</h3></td><td></td><td></td><td></td><td></td></tr>';
            $joined_files = $this->getJoinedFiles($coll_id, $table, $id, false);
            for ($i = 0; $i < count($joined_files); ++$i) {
                //Get data
                $id_doc = $joined_files[$i]['id'];
                $description = $joined_files[$i]['label'];
                $format = $joined_files[$i]['format'];

                $contact = $users_tools->get_user($joined_files[$i]['typist']);
                $dateFormat = explode(' ', $joined_files[$i]['creation_date']);
                $creation_date = $request->dateformat($dateFormat[0]);
                if ($joined_files[$i]['pdf_exist']) {
                    $check = 'class="check checkPrintFolder" checked="checked"';
                } else {
                    $check = ' disabled title="'._NO_PDF_FILE.'"';
                }
                //Show data
                $str .= '<tr><td></td><td>'.$description.'</td><td>'.$res->contact_society
                            .'</td><td>'.$creation_date.'</td><td><input id="join_file_'
                            .$id_doc.'" type="checkbox" name="join_file[]" value="'.$id_doc.'"  '.$check
                            .'/>'.$joined_files[$i]['viewLink'].'</td></tr>';
            }
        }
        //ATTACHMENTS TYPES LOOP
        foreach ($_SESSION['attachment_types'] as $attachmentTypeId => $attachmentTypeLabel) {
            if ($attachmentTypeId != 'print_folder' && $attachmentTypeId != 'converted_pdf') {
                $joined_files = $this->getJoinedFiles($coll_id, $table, $id, true, $attachmentTypeId);
                if (count($joined_files) > 0) {
                    $str .= '<tr><td><h3>+ '.$attachmentTypeLabel.'</h3></td><td></td><td></td><td></td><td></td></tr>';
                    for ($i = 0; $i < count($joined_files); ++$i) {
                        $id_doc = $joined_files[$i]['id'];
                        $description = $joined_files[$i]['label'];
                        $format = $joined_files[$i]['format'];
                        $contact = $users_tools->get_user($joined_files[$i]['typist']);
                        $dateFormat = explode(' ', $joined_files[$i]['creation_date']);
                        $creation_date = $request->dateformat($dateFormat[0]);
                        if ($joined_files[$i]['pdf_exist']) {
                            $check = 'class="check checkPrintFolder" checked="checked"';
                        } else {
                            $check = ' disabled title="'._NO_PDF_FILE.'"';
                        }
                        
                        $str .= '<tr><td></td><td>'.$description.'</td><td>'.$contact['firstname'].' '
                            .$contact['lastname'].'</td><td>'.$creation_date.'</td><td><input id="join_file_'
                            .$id_doc.'" type="checkbox" name="join_attachment[]"  value="'.$id_doc.'"  '.$check
                            .'/>'.$joined_files[$i]['viewLink'].'</td></tr>';
                    }
                }
            }
        }

        //NOTES
        $core_tools = new core_tools();
        if ($core_tools->is_module_loaded('notes')) {
            require_once 'modules'.DIRECTORY_SEPARATOR.'notes'.DIRECTORY_SEPARATOR.'class'.DIRECTORY_SEPARATOR.'class_modules_tools.php';

            $notes_tools = new notes();
            $user_notes = $notes_tools->getUserNotes($id, $coll_id);
            if (count($user_notes) > 0) {
                $str .= '<tr><td><h3>+ '._NOTES.'</h3></td><td></td><td></td><td></td><td></td></tr>';
                for ($i = 0; $i < count($user_notes); ++$i) {
                    //Get data
                    $idNote = $user_notes[$i]['id'];
                    //$noteShort = $request->cut_string($user_notes[$i]['label'], 50);
                    $noteShort = $request->cut_string(
                        str_replace(array("'", "\r", "\n", '"'), array("'", ' ', ' ', '&quot;'), $user_notes[$i]['label']),
                        50
                    );
                    $noteShort = functions::xssafe($noteShort);
                    $note = $user_notes[$i]['label'];
                    $userArray = $users_tools->get_user($user_notes[$i]['author']);
                    $date = $request->dateformat($user_notes[$i]['date']);

                    $check = ' ';

                    $str .= '<tr><td></td><td>'.$noteShort.'</td><td>'
                                 .$userArray['firstname'].' '.$userArray['lastname']
                                 .'</td><td>'.$date.'</td><td><input id="note_'.$idNote.'" class="checkPrintFolder" type="checkbox" name="notes[]"  value="'
                                 .$idNote.'"  '.$check.'/></td></tr>';
                }
            }
        }

        $str .= '</body>';
        $str .= '</table>';

        $path_to_script = $_SESSION['config']['businessappurl']
        .'index.php?display=true&module=visa&page=printFolder_ajax';

        $str .= '<hr/>';
        $str .= '<input style="margin-left:44%" type="button" name="send" id="send" value="Imprimer" class="button" onclick="printFolder(\''.$id.'\', \''.$coll_id.'\', \'print_folder_form\', \''.$path_to_script.'\');" /> ';
        $str .= '</form>';
        $str .= '</div>';

        return $str;
    }

    public function isAllAttachementSigned($res_id)
    {
        $db = new Database();
        $stmt2 = $db->query("SELECT count(1) as nb from res_attachments WHERE in_signature_book = true AND signatory_user_serial_id IS NULL AND status NOT IN ('DEL','OBS','TMP') AND attachment_type NOT IN ('converted_pdf','print_folder','signed_response') AND res_id_master = ?", array($res_id));
        $res2 = $stmt2->fetchObject();
        $stmt3 = $db->query("SELECT count(1) as nb from res_attachments WHERE in_signature_book = true AND status NOT IN ('DEL','OBS','TMP') AND attachment_type NOT IN ('converted_pdf','print_folder','signed_response') AND res_id_master = ?", array($res_id));
        $res3 = $stmt3->fetchObject();
        if ($res3->nb == 0) {
            return 'noAttachment';
        } elseif ($res2->nb == 0) {
            return 'yes';
        } else {
            return false;
        }
    }

    public function currentUserSignRequired($res_id)