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(); global $myUser; if(is_object($myUser) && $myUser->login!='') $this->creator = $myUser->login; } public function connect() { $this->pdo = Database::instance(); } 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){ $class = get_called_class(); $instance = new $class(); $compare = array(); foreach ($obj1->fields as $field => $type) { if($field == 'updated' || $field == 'updater') 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 sgbdType($type) { $sgbd = BASE_SGBD; $types = $sgbd::types(); return isset($types[$type]) ? $types[$type] : $types['default']; } 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() { $class = get_called_class(); $instance = new $class(); return 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 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(); $sgbd = BASE_SGBD; $sql = $sgbd::truncate(); $query = Entity::render($sql,array( 'table' => $instance->tableName() )); $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(); foreach ($instance->fields as $field => $type) $fields[$field] = $instance->sgbdType($type); $sgbd = BASE_SGBD; $sql = $sgbd::create(); $query = Entity::render($sql,array( 'table' => $instance->tableName(), 'fields' => $fields )); $instance->customExecute($query); if(isset($instance->indexes)) $instance->index($instance->indexes); } public static function drop() { $class = get_called_class(); $instance = new $class(); $sgbd = BASE_SGBD; $sql = $sgbd::drop(); $query = Entity::render($sql,array( 'table' => $instance->tableName() )); $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'); 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; $sgbd = BASE_SGBD; $sql = $sgbd::update(); $query = self::render($sql,array( 'table' => $this->tableName(), 'fields' => $fields, '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++; } $sgbd = BASE_SGBD; $sql = $sgbd::insert(); $query = self::render($sql,array( 'table' => $this->tableName(), 'fields' => $fields )); } $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 $colonnes=>$valeurs * @param $colonnes (WHERE) =>$valeurs (WHERE) * @param $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) { $data[':_'.$i] = $value; $filters[$field] = ':_'.$i; $i++; } $sgbd = BASE_SGBD; $sql = $sgbd::update(); $query = Entity::render($sql,array( 'table' => $instance->tableName(), 'fields' => $fields, 'filters' => $filters, )); $instance->customExecute($query, $data); } /** * Méthode de selection de tous les elements de l'entité. * * @author Valentin CARRUESCO * * @category manipulation SQL * * @param $ordre=null * @param $limite=null * * @return > $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 $colonnes (WHERE) * @param $valeurs (WHERE) * @param $ordre=null * @param $limite=null * @param $operation="=" definis le type d'operateur pour la requete select * * @return > $Entity */ public static function loadAll($columns = array(), $order = null, $limit = null, $selColumn = array('*'),$joins = 0) { $values = array(); $i=0; foreach($columns as $key=>$value){ //Gestion du IN if(strlen($key)>=3 && substr($key,-3) == ':IN'){ $columns[$key] = array(); foreach (explode(',',$value) as $v2) { $tag = ':'.$i; $columns[$key][]= $tag; $values[$tag] = $v2; $i++; } $columns[$key] = implode(',',$columns[$key]); //Gestion des opérateurs classiques } else { $tag = ':'.$i; $columns[$key] = $tag; $values[$tag] = $value; $i++; } } $class = get_called_class(); $instance = new $class(); $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($columns) || count($columns) == 0 ? null: $columns ); $data['joins'] = array(); if($joins!=0){ foreach ($data['selected'] as $k=>$column) { $data['selected'][$k] = $instance->tableName().'.'.$column; } $data = self::recursiveJoining($instance,$data,$joins); } $sgbd = BASE_SGBD; $sql = $sgbd::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 $instance $instance de départ * @param $data Tableau de construction de la requete via render() * @param $iterations Nombre d'iteration réecurive maximum * * @return $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 nombre de ligne dans l'entité' */ public static function rowCount($columns = null) { $values = array(); $i=0; foreach($columns as $key=>$value){ $tag = ':'.$i; $columns[$key] = $tag; $values[$tag] = $value; $i++; } $class = get_called_class(); $instance = new $class(); $data = array( 'table' => $class::tableName(), 'selected' => 'id' , 'filter' => count($columns) == 0 ? null: $columns ); $sgbd = BASE_SGBD; $sql = $sgbd::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 $colonnes (WHERE) * @param $valeurs (WHERE) * @param $operation="=" definis le type d'operateur pour la requete select * * @return $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 $colonnes (WHERE) * @param $valeurs (WHERE) * @param $operation="=" definis le type d'operateur pour la requete select * * @return $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 { //filters 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 existe (true) ou non (false) */ public static function exist($columns = null) { $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 $colonnes (WHERE) * @param $valeurs (WHERE) * @param $operation="=" definis le type d'operateur pour la requete select * * @return Aucun retour */ public static function delete($columns, $limit = array()) { $values = array(); $i=0; foreach($columns as $key=>$value){ $tag = ':'.$i; $columns[$key] = $tag; $values[$tag] = $value; $i++; } $class = get_called_class(); $instance = new $class(); $data = array( 'table' => $class::tableName(), 'limit' => count($limit) == 0 ? null: $limit, 'filter' => count($columns) == 0 ? null: $columns ); $sgbd = BASE_SGBD; $sql = $sgbd::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 | $colonne(s) * @param 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); $sgbd = BASE_SGBD; $class = get_called_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), ); $results = $class::staticQuery(Entity::render($sgbd::count_index(),$data)); $exists = $results->fetch(); if($mode){ if($exists['exists'] != 1) $class::staticQuery(Entity::render($sgbd::create_index(),$data)); }else{ if($exists['exists'] > 0) $class::staticQuery(Entity::render($sgbd::drop_index(),$data)); } } } public static function paginate($itemPerPage,$currentPage,&$query,$data){ 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]/iU", 'SELECT DISTINCT '.$obj->tableName().'.'.$key.' FROM ',$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() ; } 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().'`', $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(); self::$lastResult = $results; foreach ($results as $queryReturn) { $object = new $class(); foreach ($this->fields as $field => $type) { if (isset($queryReturn[$field])) { $object->{$field} = $queryReturn[$field]; } } if($joins>0) $object = self::recursiveJoiningFill($object,$queryReturn,$joins); $objects[] = $object; unset($object); } return $objects == null ? array() : $objects; } 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 $colonnes (WHERE) * @param $valeurs (WHERE) * @param $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); } }