<?php

require_once __DIR__.'/../constant.php';
require_once(__ROOT__.'class'.SLASH.'Database.class.php');
require_once(__ROOT__.'connector'.SLASH.'Oracle.class.php');
require_once(__ROOT__.'connector'.SLASH.'Mysql.class.php');
require_once(__ROOT__.'connector'.SLASH.'Sqlite.class.php');
require_once(__ROOT__.'connector'.SLASH.'SqlServer.class.php');

/**
 @version 2
 **/
 class Entity
 {
    public $debug = false,$pdo = null,$baseUid= 'local';
    public $created,$updated,$creator,$updater,$joins;
    public $foreignColumns = array();
    public $fieldMapping = array();

    public static $lastError = '';
    public static $lastResult = '';
    public static $lastQuery = '';

    const ACTIVE = 'published';
    const INACTIVE = 'deleted';

    public function __construct()
    {
        if (!isset($this->TABLE_NAME)) {
            $this->TABLE_NAME = strtolower(get_called_class());
        }

        $this->connect();
        $this->fields['created'] = 'datetime';
        $this->fields['updated'] = 'datetime';
        $this->fields['updater'] = 'string';
        $this->fields['creator'] = 'string';
        $this->joins = array();
        $this->created = time();

       
         foreach ($this->fields as $field => $type) {
            if(!is_array($type)) $type = array('type'=>$type,'column'=>$field);
            if(!isset( $type['column'] )) $type['column'] = $field;
            $this->fieldMapping[$field] = $type;
        }


        global $myUser;
        if(is_object($myUser) && $myUser->login!='') $this->creator = $myUser->login;
    }

    public function connect()
    {
        $this->pdo = Database::instance($this->baseUid);
        global $databases_credentials;

        $this->connector = $databases_credentials[$this->baseUid]['connector'];
    }

    public function __toString()
    {
        foreach ($this->toArray() as $key => $value) {
            echo $key.' : '.$value.','.PHP_EOL;
        }
    }

    public static function debug()
    {
        return array(self::$lastQuery, self::$lastError, self::$lastResult);
    }

    public function __sleep()
    {
        return array_keys($this->toArray());
    }

    public function __wakeup()
    {
        $this->connect();
    }

    //Comparaison de deux instances d'une même entité, retourne les champs ayant changés uniquement
    public static function compare($obj1,$obj2,$ignore=array()){
        $class = get_called_class();
        $instance = new $class();
        $compare = array();
        foreach ($obj1->fields as $field => $type) {
            if($field == 'updated' || $field == 'updater' || in_array($field, $ignore)) continue;
  
            if($obj1->$field != $obj2->$field){
                if($type=='int' && (($obj1->$field==0 && $obj2->$field =='') || ($obj2->$field=='' && $obj1->$field ==0)) ) continue;
                $compare[] = array('field'=>$field,'value1'=>$obj1->$field,'value2'=>$obj2->$field);
            }
        }
        return $compare;
    }

    public function toArray($decoded=false) {
        $fields = array();
        foreach ($this->fields as $field => $type) {
            $fields[$field] = $decoded ? html_entity_decode($this->$field) : $this->$field;
        }
        return $fields;
    }

    public function toText() {
        $text = array();
        foreach ($this->fields as $field => $type) {
            $value = is_object($this->$field) ? '[object]' : $this->$field;
            $text[]= $field.' = '.$value;
        }
        return implode(', ',$text);
    }

    public function fromArray($array) {
        foreach ($array as $field => $value) {
            $this->$field = $value;
        }
    }

 
    public function closeDatabase() {
        // $this->close();
    }

    //Génère un slug unique pour l'element de liste utilisable dans le code
    // $label  => string du label à slugifier
    // $column => la colonne en base où checker les slugs existants
    public static function generateSlug($label, $column){
        $slug = slugify($label);
        $class = get_called_class();
        $item = new $class();

        if(!array_key_exists($column, $item->fields)) return;
        $params = array_key_exists('state', $item->fields) ? array('state'=>$class::ACTIVE) : array();

        $i='';
        while($class::rowCount(array_merge(array($column=>$slug.$i), $params)) > 0 ) $i++;
        return $i==''?$slug:$slug.'-'.$i;
    }

    public static function tableName($escapeName = false)
    {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        return $escapeName ? $connector::table_escape.ENTITY_PREFIX.$instance->TABLE_NAME.$connector::table_escape : ENTITY_PREFIX.$instance->TABLE_NAME;
    }

    // GESTION SQL
    /**
     * Verifie l'existence de la table en base de donnée.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <String> créé la table si elle n'existe pas
     *
     * @return true si la table existe, false dans le cas contraire
     */
    public static function checkTable($autocreate = false)
    {
        $class = get_called_class();
        $instance = new $class();
        $query = 'SELECT count(*) as numRows FROM sqlite_master WHERE type="table" AND name=?';
        $statement = $instance->customQuery($query, array($instance->tableName()));

        if ($statement != false) {
            $statement = $statement->fetchArray();
            if ($statement['numRows'] == 1) {
                $return = true;
            }
        }
        if ($autocreate && !$return) self::create();
        return $return;
    }

    public static function install($classDirectory)
    {
        foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
            $infos = explode('.', basename($file));
            $class = array_shift($infos);
            require_once($classDirectory.SLASH.$class.'.class.php');
            $reflection = new ReflectionClass($class);
            if (!class_exists($class) || !method_exists($class, 'create') || $class == get_class() || $reflection->isAbstract()) {
                continue;
            }

            $class::create();
        }
    }
    
    public static function uninstall($classDirectory)
    {
        foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
            $infos = explode('.', basename($file));
            $class = array_shift($infos);
            require_once($classDirectory.SLASH.$class.'.class.php');
            $reflection = new ReflectionClass($class);
            if (!class_exists($class) || !method_exists($class, 'drop') || $class == get_class() || $reflection->isAbstract()) continue;
            $class::drop();
        }
    }

     /**
     * Methode de vidage de l'entité.
     *
     * @author Valentin CARRUESCO
     * @category manipulation SQL
     * @return Aucun retour
     */
    public static function truncate()
    {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        $sql = $connector::truncate();
        $query = Entity::render($sql,array(
           'table' => $instance->tableName(),
           'fieldMapping' => $instance->fieldMapping
           ));
        
        $instance->customExecute($query);
    }


    /**
     * Methode de creation de l'entité.
     *
     * @author Valentin CARRUESCO
     * @category manipulation SQL
     *
     * @return Aucun retour
     */
    public static function create()
    {
        $class = get_called_class();
        $instance = new $class();
        $fields = array();

        $connector = $instance->connector;
        $types = $connector::types(); 
       
        
        foreach ($instance->fields as $field => $type) 
            $fields[$field] =  isset($types[$type]) ? $types[$type] : $types['default'];
        
        
        $sql = $connector::create();
        $query = Entity::render($sql,array(
           'table' => $instance->tableName(),
           'fields' => $fields,
           'fieldMapping' => $instance->fieldMapping
           ));

        $instance->customExecute($query);
        if(isset($instance->indexes)) $instance->index($instance->indexes);
    }

    public static function drop()
    {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        $sql = $connector::drop();
        $query = Entity::render($sql,array(
           'table' => $instance->tableName(),
           'fieldMapping' => $instance->fieldMapping
           ));
        
        $instance->customExecute($query);
        if(isset($instance->indexes)) $instance->index($instance->indexes,false);
    }

    /**
     * Methode d'insertion ou de modifications d'elements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param  Aucun
     *
     * @return Aucun retour
     */
    public function save()
    {

        $this->updated = time();
        global $myUser;
        if(is_object($myUser) && $myUser->login!='') $this->updater = $myUser->login;

        $data = array();
        $numericType = array('object', 'timestamp', 'datetime', 'date', 'int', 'float', 'decimal');
        $stringType = array('string', 'longstring', 'default');

        $connector = $this->connector;

        if (isset($this->id) && $this->id > 0) {
         
            $fields = array();
            $i = 0;
            foreach ($this->fields as $field => $type) {
                if ($type == 'key') continue;
                $data[':'.$i] = $this->{$field};
                if($type=='boolean') $data[':'.$i] = $data[':'.$i] ? 1:0;
                //if(in_array($type, $numericType) && !$data[':'.$i]) $data[':'.$i] = 0;
                //if(in_array($type, $stringType) && !$data[':'.$i]) $data[':'.$i] = '';
                $fields[$field] = ':'.$i;
                $i++;
            }
            $data[':id'] = $this->id;
            
            $sql = $connector::update();

            $query = self::render($sql,array(
               'table' => $this->tableName(),
               'fields' => $fields,
               'fieldMapping' => $this->fieldMapping,
               'filters' => array('id'=>':id'),
               ));
        } else {
         
            $fields = array();
            $i = 0;
            foreach ($this->fields as $field => $type) {
                if ($type == 'key') continue;
                $data[':'.$i] = $this->{$field};
                if($type=='boolean') $data[':'.$i] = $data[':'.$i] ? 1:0;
                //if(in_array($type, $numericType) && !$data[':'.$i]) $data[':'.$i] = 0;
                //if(in_array($type, $stringType) && !$data[':'.$i]) $data[':'.$i] = '';
                $fields[$field] = ':'.$i;
                $i++;
            }
            
           
            $sql = $connector::insert();

            $query = self::render($sql,array(
               'table' => $this->tableName(),
               'fields' => $fields,
               'fieldMapping' => $this->fieldMapping
            ));
        }
        $this->customExecute($query, $data);
        $this->id = !isset($this->id) || !is_numeric($this->id) ? $this->pdo->lastInsertId() : $this->id;
    }

    /**
     * Méthode de modification d'éléments de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes=>$valeurs
     * @param <Array>  $colonnes           (WHERE) =>$valeurs (WHERE)
     * @param <String> $operation="="      definis le type d'operateur pour la requete select
     *
     * @return Aucun retour
     */
    public static function change($columns, $columns2 = array(), $operation = '=') {
        $class = get_called_class();
        $instance = new $class();
        
        $fields = array();
        $i = 0;
        foreach ($columns as $field => $value) {
            $data[':'.$i] = $value;
            $fields[$field] = ':'.$i;
            $i++;
        }
     
        $filters = array();
        $i = 0;
        foreach ($columns2 as $field => $value) {
            //Gestion du IN
            if(strlen($field)>=3 && substr($field,-3) == ':IN'){
                $filters[$field] = array();
                foreach (explode(',',$value) as $v2) {
                    $tag = ':_'.$i;
                    $filters[$field][] = $tag;
                    $data[$tag] = $v2;
                    $i++;
                }
                $filters[$field] = implode(',',$filters[$field]);
             //Gestion des opérateurs classiques
            } else {
                $tag = ':_'.$i;
                $filters[$field] = $tag;
                $data[$tag] = $value;
                $i++;
            }
        }
        $connector = $instance->connector;
        $sql = $connector::update();
        $query = Entity::render($sql,array(
            'table' => $instance->tableName(),
            'fields' => $fields,
            'filters' => $filters,
            'fieldMapping' => $instance->fieldMapping
        ));

        $instance->customExecute($query, $data);
    }

    /**
     * Méthode de selection de tous les elements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <String> $ordre=null
     * @param <String> $limite=null
     *
     * @return <Array<Entity>> $Entity
     */
    public static function populate($order = null, $limit = null,$selColumn = array('*'),$joins = 0)
    {
        $results = self::loadAll(array(), $order, $limit,$selColumn,$joins);
        return $results;
    }

    /**
     * Méthode de selection multiple d'elements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $ordre=null
     * @param <String> $limite=null
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return <Array<Entity>> $Entity
     */
   
    public static function loadAll($columns = array(), $order = null, $limit = null, $selColumn = array('*'), $joins = 0)
    {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;

        $values = array();
        $i=0;
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
         }
         
         if(!empty($order)){
             foreach ($order as $key=>$clause) {
                foreach ($instance->fieldMapping as $attribute => $infos) {
                    $order[$key] = str_replace( $attribute,$infos['column'],$order[$key]);
                }
             }
         }
         
         $data = array(
            'table' => $instance->tableName(),
            'selected' => $selColumn,
            'limit' =>  !isset($limit) || count($limit) == 0 ? null: $limit,
            'orderby'  => !isset($order) || count($order) == 0 ? null: $order,
            'filter' => !isset($filters) ||  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        $data['joins']  = array();
        if($joins!=0){
            foreach ($data['selected'] as $k=>$column) {
               $data['selected'][$k] = $instance->tableName().'.'.$column;
            }
            $data = self::recursiveJoining($instance,$data,$joins);
        }
        
            
        $sql = $connector::select();
        $sql = Entity::render($sql,$data);

        return $instance->customQuery($sql, $values, true, $joins);
    }

    /**
     * Méthode privée de gestion du join récursif sur les objets liés
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Object>  $instance   $instance de départ
     * @param <Array> $data    Tableau de construction de la requete via render()
     * @param <Int> $iterations Nombre d'iteration réecurive maximum
     *
     * @return <Array> $data
     */
    private static function recursiveJoining($instance,$data,$iterations){
        if($iterations==0) return $data;
        $iterations--;

        if(isset($instance->links)){

            foreach ($instance->links as $field => $className) {
                $field2 = 'id';
                $classField = explode('.',$className);

                if(isset($classField[1]))
                    list($className,$field2) = $classField;
                
                $joinInstance = new $className();
                foreach ($joinInstance->fields as $key=>$type) {
                    $data['selected'][] = $className::tableName().'.'.$key.' as '.$className::tableName().'_join_'.$key;
                }
            
                $data['joins'][] = array(
                    'jointable1' => $instance::tableName(),
                    'jointable2' => $className::tableName(),
                    'field1' => $field,
                    'field2' =>  $field2
                );
                $data = self::recursiveJoining($joinInstance,$data,$iterations);
            }
        }
        return $data;
    }


    /**
     * Methode de comptage des éléments de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     * @return<Integer> nombre de ligne dans l'entité'
     */
    public static function rowCount($columns = array()) {
        $values = array();
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;

        $i=0;
        $values = array();
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
        }

        $data = array(
            'table' => $class::tableName(),
            'selected' => 'id' ,
            'filter' =>  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        
        $sql = $connector::count();
        $execQuery = $instance->customQuery(Entity::render($sql,$data), $values);
        $row = $execQuery->fetch();

        return $row['number'];
    }


    public static function loadAllOnlyColumn($selColumn, $columns, $order = null, $limit = null)
    {
        $objects = self::loadAll($columns, $order, $limit, $operation, $selColumn);
        if (count($objects) == 0) {
            $objects = array();
        }

        return $objects;
    }

    /**
     * Méthode de selection unique d'élements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
     */
    public static function load($columns,$joins =0) {
        $objects = self::loadAll($columns, null, array('1'),array('*'),$joins);
        if (!isset($objects[0])) $objects[0] = false;

        return $objects[0];
    }

    /**
     * Méthode de selection unique d'élements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
     */
    public static function getById($id,$joins =0 ) {
        return self::load(array('id' => $id),$joins);
    }

 public static function render($sql,$data=array()) {
       //loop
        $sql = preg_replace_callback('/{{\:([^\/\:\?}]*)}}(.*?){{\/\:[^\/\:\?}]*}}/',function($matches) use ($data) {
            $tag = $matches[1];
            $sqlTpl = $matches[2];

            $sql = '';
            if(isset($data[$tag])){
                $i = 0;
                $values = $data[$tag];

                if($tag =='joins'){
                    //joins
                    foreach($values as $join){
                        $occurence = $sqlTpl;
                        foreach($join as $key=>$value){
                            $occurence = str_replace(array('{{'.$key.'}}'),array($value),$occurence); 
                        }
                        $sql.= $occurence;
                    }
                }else if($tag =='filter'){
                    //filters

                    foreach($values as $key=>$value){
                        $i++;

                        $last = $i == count($values);
                        $operator = $value['operator'];
                        $postoperator = $value['postoperator'];
                        $key = $value['column'];
                        
                        
                        $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,
                            $value['tag'],
                            $operator,
                            $postoperator),
                        $sqlTpl); 
                        $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
                            return $last? '': $matches[1];
                        },$occurence);

                        $sql.= $occurence;
                    }
                } else {
                    //Autre boucles
                    foreach($values as $key=>$value){
                        $i++;
                        $last = $i == count($values);
                        $operator = isset($data['operator']) ? $data['operator'][0] : '=';
                        $postoperator = isset($data['postoperator']) ? $data['postoperator'][0] : '';
                        if(strpos($key,':')!==false){
                            $infos = explode(':',$key);
                            $key = $infos[0];
                            $operator = $infos[1];
                            
                            if($operator=='IN'){
                                $operator = 'IN(';
                                $postoperator = ')';
                            }
                        } 

                        $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,$value,$operator,$postoperator),$sqlTpl); 
                        $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
                            return $last? '': $matches[1];
                        },$occurence);

                        $sql.= $occurence;
                    }
                }
                return $sql;
            }
            return '';
        },$sql);

        //conditions
        $sql = preg_replace_callback('/{{\?([^\/\:\?}]*)}}(.*?){{\/\?[^\/\:\?}]*}}/',function($matches) use ($data) {
            $key = $matches[1];
            $sql = $matches[2];
            return !isset($data[$key]) || (is_array($data[$key]) && count($data[$key])==0) ?'':$sql;
        },$sql); 

        //simple vars
        $sql = preg_replace_callback('/{{([^\/\:\;\?}]*)}}/',function($matches) use ($data) {
            $key = $matches[1];
            return isset($data[$key])?$data[$key]:'';
        },$sql); 
      
        return $sql;
    }


    /**
     * Methode de définition de l'éxistence d'un moins un des éléments spécifiés en base.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     * @return<boolean> existe (true) ou non (false)
     */
    public static function exist($columns = array()) {
        $result = self::rowCount($columns);
        return $result != 0;
    }

    public static function deleteById($id) {
        self::delete(array('id' => $id));
    }
    
    /**
     * Méthode de Suppression d'elements de l'entité.
     *
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return Aucun retour
     */
    public static function delete($columns, $limit = array()) {
        $values = array();
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        
        $i=0;
        $values = array();
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
        }

        
        $data = array(
           'table' => $class::tableName(),
           'limit' =>  count($limit) == 0 ? null: $limit,
           'filter' =>  count($filters) == 0 ? null: $filters,
           'fieldMapping' => $instance->fieldMapping
           );
        
        $sql = $connector::delete();

        return $instance->customExecute(Entity::render($sql,$data), $values);
    }

    /**
     * Méthode d'indexation de la ou les colonnes ciblées
     * nb : il est possible d'appeller automatiquement cette méthode sur les classes entity lors du create
     * si la classe contient l'attribut $this->indexes = array(...);
     * @author Valentin CARRUESCO
     *
     * @category manipulation SQL
     *
     * @param <Array> | <String>  $colonne(s)  
     * @param <Boolean>  Mode (true : ajout, false : suppression)
     *
     * @return Aucun retour
     */
    public static function index($columns,$mode = true){
        if(!is_array($columns)) $columns = array($columns);
        $columns = array_filter($columns);

        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        
        $class = get_called_class();
        $instance = new $class();
        foreach($columns as $column){
            if(!is_array($column)) $column = array($column);
            $data = array(
                'table' => $class::tableName(),
                'column' =>  '`'.implode('`,`',$column).'`',
                'index_name' =>  $class::tableName().'_'.implode('_',$column),
                'fieldMapping' => $instance->fieldMapping
            );

            $results = $class::staticQuery(Entity::render($connector::count_index(),$data));
            $exists = $results->fetch();
            
            if($mode){
                if($exists['exists'] != 1) $class::staticQuery(Entity::render($connector::create_index(),$data));
            }else{
                if($exists['exists'] > 0) $class::staticQuery(Entity::render($connector::drop_index(),$data));
            }
        }
    }
     
    public static function paginate($itemPerPage,$currentPage,&$query,$data,$alias=''){
        global $_;
        $class = get_called_class();
        $obj = new $class();
        $keys = array_keys($obj->fields, 'key');
        $key = count($keys) == 1 ? $keys[0] : 'id';

        $queryNumber = $query;

        $queryNumber = preg_replace("/(?<!\([^(\)])(SELECT.+[\n|\t]*FROM[\s\t\r\n])({{table}}|`?".$obj->tableName()."`?)(?![^(\)]*\))/iU", 'SELECT DISTINCT '.(!empty($alias) ? $alias : $obj->tableName()).'.'.$key.' FROM $2',$queryNumber);

        $queryNumber = $class::staticQuery('SELECT COUNT(*) FROM ('.$queryNumber.') number',$data)->fetch();

        $number = $queryNumber[0];
        $pageNumber = $number / $itemPerPage;
        if($currentPage > $pageNumber) $currentPage = 0;
        
        $limit = ' LIMIT '.($currentPage*$itemPerPage).','.$itemPerPage;
        $query .= $limit;
        return array(
            'pages' => $pageNumber,
            'current' => $currentPage,
            'total' => $number
        );
    }

    public function customExecute($query, $data = array()) {
        self::$lastQuery = $query;
       
        try {
              if(BASE_DEBUG) self::logFile($query.' :: '.json_encode($data, JSON_UNESCAPED_UNICODE));
            $stm = $this->pdo->prepare($query);
            $stm->execute($data);
        } catch (Exception $e) {
            self::$lastError = $this->pdo->errorInfo();
            //Log l'erreur uniquement si elle ne proviens pas elle même de Log (evite les mysql gone away)
            if(get_class($this) !='Log')
                Log::put("[SQL ERROR] - ".$e->getMessage().' - '.$e->getLine().' - Requete : '.$query.' - Données : '.json_encode($data,JSON_UNESCAPED_UNICODE));
             if(BASE_DEBUG) self::logFile( "Erreur : ".$e->getMessage());
            throw new Exception($e->getMessage().' - '.$e->getLine().' : '.$query.' - '.json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
        }
    }

    public static function provide($parameter = 'id',$join=0){
        global $_;
        $class = get_called_class();
        return !empty($_[$parameter]) ? $class::getById($_[$parameter],$join) : new $class();
    }

    // L'alias utilisé pour la colonne du join doit avoir la syntaxe [table]_join_[field]
    // Ex : address_join_street
    public static function staticQuery($query, $data = array(), $fill = false,$joins = 0) {
        $class = get_called_class();
        $instance = new $class();

        return $instance->customQuery($query, $data, $fill,$joins);
    }

    public function customQuery($query, $data = array(), $fill = false,$joins = 0) {
        $query = str_replace('{{table}}', $this->tableName(true), $query);

        $mapping = $this->fieldMapping;
        $query = preg_replace_callback('/{{([^}]*)}}/si', function($match) use ($mapping){
          
            return isset($mapping[$match[1]]) && isset($mapping[$match[1]]['column']) ? $mapping[$match[1]]['column'] : $match[0];
        }, $query);

        self::$lastQuery = $query;
        
        try{
            if(BASE_DEBUG) self::logFile($query.' :: '.json_encode($data, JSON_UNESCAPED_UNICODE));
            $results = $this->pdo->prepare($query);
            $results->execute($data);
    
            if (!$results) throw new Exception(json_encode($this->pdo->errorInfo()));
        }catch(Exception $e){
            self::$lastError = $e->getMessage();
            //echo "[SQL ERROR] - Erreur : ".$e->getMessage().' - Requete : '.$query.' - Données : '.json_encode($data);
            Log::put("[SQL ERROR] - Erreur : ".$e->getMessage().' - Requete : '.$query.' - Données : '.json_encode($data, JSON_UNESCAPED_UNICODE));
            if(BASE_DEBUG) self::logFile( "Erreur : ".$e->getMessage());
            throw $e;
        }

        if (!$fill) return $results;
        
        $class = get_class($this);
        $objects = array();
        $results = $results->fetchAll(PDO::FETCH_ASSOC);

        self::$lastResult = $results;

        foreach ($results as $queryReturn) {

            $object = new $class();
            foreach ($this->fields as $field => $type) {
                $dbField = $field;
                if(is_array($type)){

                    if(isset($type['column'])) $dbField = $type['column'];
                    $type = $type['type'];

                }

                //if($this->tableName()=='WORKORDER') var_dump($queryReturn);
                if (isset($queryReturn[$dbField])) {
                    $object->{$field} = $queryReturn[$dbField];
                    unset($queryReturn[$dbField]);
                }
            }   
                   
            if($joins>0) $object = self::recursiveJoiningFill($object,$queryReturn,$joins);
            
            foreach ($queryReturn as $key => $value) {
                if(!is_numeric($key)) $object->foreignColumns[$key] = $value;
            }  
            
            $objects[] = $object;
            unset($object);
        }

        return $objects == null ? array()  : $objects;
    }

    //Récuperation d'une/plusieurs colonne non référencée dans l'objet mais récuperée dans une requete static query
    public function foreign($key=null){
        if(!isset($key))  return $this->foreignColumns;
        return isset($this->foreignColumns[$key]) ? $this->foreignColumns[$key]  : '';
    }

    private static function recursiveJoiningFill($object,$queryReturn,$iterations){
        if($iterations == 0) return $object;
        $iterations--;
        if(isset($object->links)){
            foreach ($object->links as $link=>$classLink) {
                $classField = explode('.',$classLink);
                if(isset($classField[1]))
                    $classLink = $classField[0];

                $instanceLink = new $classLink();
                foreach ($instanceLink->fields as $field => $type) {
                    if (isset($queryReturn[$classLink::tableName().'_join_'.$field]))
                         $instanceLink->{$field} = $queryReturn[$classLink::tableName().'_join_'.$field];
                }
                $instanceLink = self::recursiveJoiningFill($instanceLink,$queryReturn,$iterations);
                $object->joins[$link] =  $instanceLink;
            }
        }
        return  $object;
    }

    /**
     * Récupere l'objet join ex : $contact->join("adress")->street; --> récupere l'attribut street de la class Adress dont l'id est spécifié dans la colonne adress de la class Contact
     * Nb : cette méthode ne fonctionne que si vous avez placé le parametre joins > 0 dans la méthode LoadALl
     * Nb : cette méthode ne fonctionne que si vous avez précisé le lien entre Contact et Adress dans la classe Contact via :
        
         protected $links = array(
        'address' => 'Address'
        );
     *
     * @author Valentin CARRUESCO
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return Aucun retour
     */
    public function join($field){
        return isset($this->joins[$field])?$this->joins[$field]:'';
    }


    public static function logFile($msg){
        file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', date('H:i:s').' | '.$msg.PHP_EOL,FILE_APPEND);
    }
/*
    public function __get($name) {
        $pos = strpos($name, '_object');
        if ($pos !== false) {
            $field = strtolower(substr($name, 0, $pos));
            if (array_key_exists($field, $this->fields)) {
                $class = ucfirst($field);

                return $class::getById($this->{$field});
            }
        }
        throw new Exception('Attribut '.get_class($this)."->$name non existant");
    }
*/
    public static function log_executed_query($string, $data) {
        $indexed=$data==array_values($data);
        foreach($data as $k=>$v) {
            if(is_string($v)) $v="'$v'";
            if($indexed) $string=preg_replace('/\?/',$v,$string,1);
            else $string=str_replace(":$k",$v,$string);
        }
        self::logFile($string);
    }
}