do

<?php

class Protocol extends DataObject {
   static $db = array(
        'Name' => 'Text',
        'Title' => 'Varchar(255)',
        'Description' => 'HTMLText',
        'URLSegment' => 'Varchar(255)',
        'MetaTitle' => 'Varchar(255)',
        'protocolId' => 'Int',
        'date_debut' => 'Date',
        'date_fin' => 'Date',
        'nb_inc_theorique' => 'Int',
        'EIaction' => 'Varchar(100)',
        'EIactionParameter1' => 'Varchar(100)',
        'EIactionParameter2' => 'Varchar(100)',
        'EIactionParameter3' => 'Varchar(100)',
        'closed' => 'Boolean',
        'update' => 'Int',
    );

    static $has_one = array(
        'Image' => 'Image',
        'InvPrincipal' => 'Member'
    );

    //Relate to the category pages
    static $belongs_many_many = array(
        'Categories' => 'CategoryPage'
    );

    //Documentation
    static $has_many = array(
        'Documents' => 'DocumentProto',
    );

    static $many_many = array('Member' => 'Member');

   function getCMSFields()   {
      $fields = parent::getCMSFields();

        //Main Tab
        $fields->addFieldToTab("Root.Main", new TextField('Title', _t('Protocol.TITLE','Nom: ')));
        $fields->addFieldToTab("Root.Main", new TextField('URLSegment', 'URL Segment'));
        $fields->addFieldToTab("Root.Main", new TextField('MetaTitle', 'Meta Title'));
        $beginDate = new DateField('date_debut', 'D&eacute;but');
        $beginDate->setConfig('showcalendar', true);
        $beginDate->setConfig('dateformat', 'YYYY-MM-dd');
        $fields->addFieldToTab("Root.Main", $beginDate);
        $endDate = new DateField('date_fin', 'Fin');
        $endDate->setConfig('showcalendar', true);
        $endDate->setConfig('dateformat', 'YYYY-MM-dd');
        $fields->addFieldToTab("Root.Main", $endDate);
        $fields->addFieldToTab("Root.Main", new TextField('protocolId', 'Ident V1'));
        $fields->addFieldToTab("Root.Main", new TextField('nb_inc_theorique', 'Nb inclusions pr&eacute;vues'));
        $fields->addFieldToTab("Root.Main", new HiddenField('EIaction', 'Ev&eacute;nement Ind&eacute;sirable : action'));
        $fields->addFieldToTab("Root.Main", new HiddenField('EIaction Parameter1', 'Ev&eacute;nement Ind&eacute;sirable : param&egrave;tre 1'));
        $fields->addFieldToTab("Root.Main", new HiddenField('EIaction Parameter2', 'Ev&eacute;nement Ind&eacute;sirable : param&egrave;tre 2'));
        $fields->addFieldToTab("Root.Main", new HiddenField('EIaction Parameter3', 'Ev&eacute;nement Ind&eacute;sirable : param&egrave;tre 3'));
        $fields->addFieldToTab("Root.Main", new HTMLEditorField('Description'));


        $fields->removeFieldFromTab('Root','Member');

        //Users (with Multiselect)
        $source = DataObject::get('User');
        $map = $source ? $map = $source->toDropdownMap('ID','Name') : array();
        $fields->insertAfter(new MultiSelectField("Member","Member",$map),'Name');

        //Categories
        $Categories = DataObject::get('CategoryPage');
        $fields->addFieldToTab("Root.Categories", new CheckboxsetField('Categories', 'Categories', $Categories));

        //Images
        $fields->addFieldToTab("Root.Images", new ImageField('Image', 'Image', Null, Null, Null, 'Uploads/category_banners'));

        //Documents
        $FilesField = new MultipleFileUploadField('Documents',_t('JobApplication.RESUME','T&eacute;l&eacute;charger des fichiers.'));
        $FilesField->removeFolderSelection();
        $FilesField->uploadFolder = 'Uploads/DocProto';
        $fields->addFieldToTab("Root.Documents", $FilesField);



        return $fields;
   }


    // Complements d'infos
    function ComplementProtocol() {

        $protoID = $this->ID;

        $sqlQuery = new SQLQuery();
        $sqlQuery->select = array(
          'FirstName AS Name',
          'Surname AS Surname'
        );
        $sqlQuery->from = array(
            "Protocol
            JOIN Member ON Protocol.InvPrincipalID = Member.ID
            "
        );
        $sqlQuery->where = array(
            "Protocol.ID = $protoID
            "
        );

        $result = $sqlQuery->execute();
        $docList = "";
        foreach($result as $row) {
            $docList .= $row['Name']." ".$row['Surname'];
        }

        return $docList;

    }

    // Autorized protocols
    function MyProtocols() {

        $me = member::currentUser();
        $myID = $me->ID;
        $protoID = $this->ID;



        $sqlQuery = new SQLQuery();
        $sqlQuery->select = array(
          'COUNT(ID) AS ID'
        );
        $sqlQuery->from = array(
            "Protocol_Member"
        );
        $sqlQuery->where = array(
            "MemberID = $myID",
            "ProtocolID = $protoID"
        );

        $nbProtos = $sqlQuery->execute()->value();

        return $nbProtos;

    }

    // Protocols documentation
    function DocProtocols() {

        $protoID = $this->ID;

        $sqlQuery = new SQLQuery();
        $sqlQuery->select = array(
          'Name AS Name',
          'Filename AS Filename'
        );
        $sqlQuery->from = array(
            "File
            JOIN DocumentProto ON File.ID = DocumentProto.ID
            "
        );
        $sqlQuery->where = array(
            "ProtocolID = $protoID
            "
        );

        $result = $sqlQuery->execute();
        $docList = "";
        foreach($result as $row) {
            $docList .= "<div><a href='".$row['Filename']."' target=\"_blank\">".$row['Name']."</a></div>";
        }

        return $docList;
    }

    // Protocols Sessions
    function SessionsProtocols() {

        $protoID = $this->ID;

        global $databaseConfig;
        $connexion = new PDO('mysql:host='.$databaseConfig['server'].';dbname='.$databaseConfig['database'].'', $databaseConfig['username'], $databaseConfig['password']);


          $list= "";
          $listform= "";
        $sql= "
            SELECT session, startIntervention, FirstName, Surname, Protocol.Name
            FROM session, randomisation, Member, Protocol
            WHERE Protocol.ID LIKE :a
                AND session.session = randomisation.session_ref
                AND randomisation.protocolId = Protocol.ID
                AND session.tag = Member.ID
            ";
        $sth = $connexion->prepare("$sql");
        $sth->bindParam(':a', $protoID);
        $sth->execute();
        $list .= "<table>";
        $m = 1;
        while ($row = $sth->fetch(PDO::FETCH_NUM)) {

                    /*
                     * Récupération du nombre de formulaire enregistrées pour cette session
                     */
                    $sql = "SELECT count(*) as nbForm FROM SubmittedForm WHERE session_ref = :session";
                    $sth1 = $connexion->prepare("$sql");
                    $sth1->bindParam(':session', $row[0]);;
                    $sth1->execute();
                    $result = $sth1->fetch(PDO::FETCH_ASSOC);
                    $nbForm = $result['nbForm'];
                    if ($nbForm == 0){
                        $ExistForm = "<p style=\"font-weight: bold;color: red;\"> Pas de formulaire</p>";
                    }
                    else{
                        if($nbForm == 1){
                            $ExistForm = "<p style=\"font-weight: bold;color: blue;\"> 1 formulaire</p";
                        }
                        else{
                            $ExistForm ="<p style=\"font-weight: bold;color: green;\">".$nbForm." formulaires</p>";
                        }
                    }


            $listform .= "
                <FORM name=\"detailsession_".$m."\" action=\"".Director::baseURL()."detail-d-une-session\" method=\"post\"  target=\"_blank\">
                    <INPUT type=\"hidden\" name=\"numSession\" value=\"$row[0]\"></form>
            ";
          $data = "<tr><td><a href=\"javascript: OnClick=document.detailsession_".$m.".submit();\">".$row[0]."</a></td><td>$row[1]</td><td>$row[2] $row[3]</td><td>$row[4]</td><td>$ExistForm</tr></tr>";
          $list.= $data;
          $m++;
        }
        $list .= "</table>";
        $list .= $listform;

        return $list;

    }

    // Protocols investigateurs
    function InvestigateursProtocols() {

        $protoID = $this->ID;

        $sqlQuery = new SQLQuery();
        $sqlQuery->select = array(
          'FirstName AS Name',
          'Surname AS Surname',
          'Email AS Email2'
          );
        $sqlQuery->from = array(
            "Member
            JOIN Protocol_Member ON Member.ID = Protocol_Member.MemberID
            "
        );
        $sqlQuery->where = array(
            "Protocol_Member.ProtocolID = $protoID
            "
        );

        $sqlQuery->orderby = "FirstName";

        $result = $sqlQuery->execute();
        $docList = "";
        foreach($result as $row) {
            $docList .= "<div>".$row['Name']." ".$row['Surname'].", <a href=\"mailto:".$row['Email2']."\">".$row['Email2']."</a></div>";
        }

        return $docList;
    }

    // paramExportSimple
    function paramExportSimple() {


        $protoID = $this->ID;

        $wh = "AND Protocol.ID = $protoID ";
        $list= "
            <div id=\"id_filtre\">
            <input type=\"hidden\" name=\"filtre\" id=\"filtre\" value=\"\" />
            </div>
            <div id=\"id_wh\">
            <input type=\"hidden\" name=\"wh\" id=\"wh\" value=\"$wh\" />
            </div>
        ";
        return $list;

    }

        function getProtocolID() {
        global $databaseConfig;
        $con = new PDO('mysql:host='.$databaseConfig['server'].';dbname='.$databaseConfig['database'].'', $databaseConfig['username'], $databaseConfig['password']);

        $protoID = $this->ID;
                $SQL = "SELECT * FROM `Protocol` WHERE `ID` = " . $protoID . ";";
                $sth1 = $con->prepare("$SQL");
                $result = $sth1->execute();
                $row = $sth1->fetch(PDO::FETCH_ASSOC);

        $name = htmlentities($row['Name']);
                //var_dump($name);die();

        $list= "
            <div id=\"id_Proto\">
            <input type=\"hidden\" name=\"ProtoID\" value=\"$protoID\" />
                        <input type=\"hidden\" name=\"ProtoName\" value=\"$name\" />
            </div>

        ";
        return $list;


    }


    // Protocols Sessions
    function TableProtocols() {

        Requirements::customScript('
            function libererrando(id) {
                var xhr_object = null;
                var ident = id;
                if(window.XMLHttpRequest) // Firefox
                     xhr_object = new XMLHttpRequest();
                  else if(window.ActiveXObject) // Internet Explorer
                     xhr_object = new ActiveXObject("Microsoft.XMLHTTP");
                  else { // XMLHttpRequest non support&eacute; par le navigateur
                     alert("Votre navigateur ne supporte pas les objets XMLHTTPRequest...");
                     return;
                  }
                  xhr_object.open("POST", "./libererrando.php", true);
                  xhr_object.onreadystatechange = function anonymous() {
                     if(xhr_object.readyState == 4)
                        alert(\'le numero \'+ident+\' est libre.\');
                    }
                xhr_object.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                var data = "ident="+ident;
                xhr_object.send(data);
            }

        ');
        $protoID = $this->ID;

        $me = member::currentUser();
        $myID = $me->ID;

        global $databaseConfig;
        $connexion = new PDO('mysql:host='.$databaseConfig['server'].';dbname='.$databaseConfig['database'].'', $databaseConfig['username'], $databaseConfig['password']);


        $list= "";
        $listform= "";
        $sql= "
            SELECT inclusionId, session_ref, groupe, name, statut, type, date_dmd, FirstName, Surname
            FROM Center, `randomisation`
                LEFT OUTER  JOIN Member ON (`randomisation`.`memberID` = Member.ID)
            WHERE `randomisation`.`centerId` = Center.ID
                AND randomisation.`protocolId` =  :a
        ";
        $sth = $connexion->prepare("$sql");
        $sth->bindParam(':a', $protoID);
        $sth->execute();
        if ($protoID == 42) {
            $list .= "
                <table width=700><tr>
                <td>
                <a href='./list_wait42.php' target=_blank>sessions <br>en attente<br> de logs</a>
                </td>
                <td>
                        <b>
                        Statut de randomisation
                        </h6>
                        <li> -1 = Valeur par d&eacute;faut
                        <li>  0 = session int&eacute;gr&eacute;e
                        <li>  1 = session en attente
                </td><td>
                <b> Type du patient</b><br>
                <li> 00 : < 70ans, non cancer<br>
                <li> 01 : > 70 ans, non cancer<br>
                <li> 10 : < 70ans, cancer<br>
                <li> 11 : > 70 ans, cancer
                </td></tr></table>
                <table>
                    <tr>
                        <td width=50px>incl.</td>
                        <td width=110px>session</td>
                        <td width=50px>groupe</td>
                        <td width=80px>centre</td>
                        <td width=40px>type</td>
                        <td width=140px>r&eacute;servation</td>
                    </tr>
                </table>
            ";
        }
        $list .= "
            <div id=\"table\" style=\"height: 300px;width: 730px;overflow: auto;display:block;\">
            <table>
        ";
        $m = 1;
        while ($row = $sth->fetch(PDO::FETCH_NUM)) {
            $list .= "
                <tr><td width=30px>$row[0]</td><td width=30px>$row[1]</td><td width=30px>$row[2]</td><td width=30px>$row[3]</td><td width=30px>$row[5]</td>
            ";
            #numéros réservés
            if ($row[4] == 1 && ($myID == 1 || $myID == 3 || $myID == 18 )) {
                $liberer = "";
                if ($row[1] == "") {
                    $liberer = "<a  href=\"javascript: OnClick=libererrando($row[0]);\" >(lib&eacute;rer)</a>";
                }

                $list .= "
                    <td width=30px>$row[6]</td><td>$row[7] $row[8] $liberer </td>
                ";
            }
            $list .= "
                </tr>
            ";
        }
        $list .= "</table>";
        $list .= $listform;

        return $list;

    }

    //Generate the link for this protocol
    function Link()
    {
        //if we are on a category page return that
        if(Director::CurrentPage()->ClassName == 'CategoryPage')
        {
            $Category = Director::CurrentPage();
        }
        //Otherwise just grab the first category this protocol is in
        else
        {
            $Category = $this->Categories()->First();
        }
        //Check we have a category then return the link
        if($Category)
        {

            return $Category->absoluteLink() . 'show/' . $this->URLSegment;
        }
    }


}


?>