Entity.class.php 37 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987
  1. <?php
  2. require_once __DIR__.'/../constant.php';
  3. require_once(__ROOT__.'class'.SLASH.'Database.class.php');
  4. require_once(__ROOT__.'connector'.SLASH.'Oracle.class.php');
  5. require_once(__ROOT__.'connector'.SLASH.'Mysql.class.php');
  6. require_once(__ROOT__.'connector'.SLASH.'Sqlite.class.php');
  7. require_once(__ROOT__.'connector'.SLASH.'SqlServer.class.php');
  8. /**
  9. * Classe de gestion ORM
  10. * @version 2
  11. * @category sql
  12. * @author Valentin CARRUESCO
  13. **/
  14. class Entity {
  15. public $pdo = null,$baseUid= 'local';
  16. public $created,$updated,$creator,$updater,$joins,$entityLabel = '';
  17. public $links = array();
  18. public $foreignColumns = array();
  19. public $fieldMapping = array();
  20. const ACTIVE = 'published';
  21. const INACTIVE = 'deleted';
  22. public function __construct() {
  23. if (!isset($this->TABLE_NAME)) $this->TABLE_NAME = strtolower(get_called_class());
  24. $this->connect();
  25. $this->fields['created'] = array('type'=>'date', 'label' => 'Date de création');
  26. $this->fields['updated'] = array('type'=>'date', 'label' => 'Date de modification');
  27. $this->fields['updater'] = array('type'=>'text', 'label' => 'Modificateur');
  28. $this->fields['creator'] = array('type'=>'text', 'label' => 'Créateur');
  29. $this->joins = array();
  30. $this->created = time();
  31. $this->fieldMapping = $this->field_mapping($this->fields);
  32. global $myUser;
  33. if(is_object($myUser) && $myUser->login!='') $this->creator = $myUser->login;
  34. }
  35. public function field_mapping($fields = array()){
  36. $fieldMapping = array();
  37. $fieldTypes = FieldType::available();
  38. foreach($fields as $field => $type) {
  39. if(!is_array($type)) $type = array('type'=>$type,'column'=>$field);
  40. if(!isset($type['column'])) $type['column'] = $field;
  41. if(isset($type['link'])) $this->links[$type['column']] = $type['link'];
  42. $type['fieldtype'] = $type['type'];
  43. //conversion des field type en sql type
  44. if(isset($fieldTypes[$type['type']])) $type['type'] = $fieldTypes[$type['type']]->sqlType;
  45. $fieldMapping[$field] = $type;
  46. }
  47. return $fieldMapping;
  48. }
  49. //Connexion à la base
  50. public function connect() {
  51. $this->pdo = Database::instance($this->baseUid);
  52. global $databases_credentials;
  53. $this->connector = $databases_credentials[$this->baseUid]['connector'];
  54. }
  55. public function __toString() {
  56. foreach ($this->toArray() as $key => $value)
  57. echo $key.' : '.$value.','.PHP_EOL;
  58. }
  59. public function __sleep() {
  60. return array_keys($this->toArray());
  61. }
  62. public function __wakeup() {
  63. $this->connect();
  64. }
  65. //Comparaison de deux instances d'une même entité, retourne les champs ayant changés uniquement
  66. public static function compare($obj1,$obj2,$ignore=array()){
  67. $class = get_called_class();
  68. $compare = array();
  69. foreach ($obj1->fields as $field => $type) {
  70. if($field == 'updated' || $field == 'updater' || in_array($field, $ignore)) continue;
  71. if($obj1->$field != $obj2->$field){
  72. if($type=='int' && (($obj1->$field==0 && $obj2->$field =='') || ($obj2->$field=='' && $obj1->$field ==0)) ) continue;
  73. $compare[] = array('field'=>$field,'value1'=>$obj1->$field,'value2'=>$obj2->$field);
  74. }
  75. }
  76. return $compare;
  77. }
  78. public static function fields($onlyKeys = true){
  79. $class = get_called_class();
  80. $instance = new $class();
  81. if($onlyKeys) return array_keys($instance->fields);
  82. return $instance->fieldMapping;
  83. }
  84. public function toArray($decoded=false) {
  85. $fields = array();
  86. foreach ($this->fields as $field => $type) {
  87. $fields[$field] = $decoded ? html_entity_decode($this->$field) : $this->$field;
  88. }
  89. return $fields;
  90. }
  91. public function toText() {
  92. $text = array();
  93. foreach ($this->fields as $field => $type) {
  94. $value = is_object($this->$field) ? '[object]' : $this->$field;
  95. $text[]= $field.' = '.$value;
  96. }
  97. return implode(', ',$text);
  98. }
  99. public function fromArray($array) {
  100. foreach ($array as $field => $value) {
  101. $this->$field = $value;
  102. }
  103. }
  104. public function closeDatabase() {
  105. // $this->close();
  106. }
  107. //Libellé human readable de l'entité
  108. public static function entityLabel() {
  109. $class = get_called_class();
  110. $instance = new $class();
  111. return !empty($instance->entityLabel) ? $instance->entityLabel : $class;
  112. }
  113. public static function tableName($escapeName = false,$instance = null) {
  114. global $databases_credentials;
  115. $class = get_called_class();
  116. if(!isset($instance)) $instance = new $class();
  117. $prefix = isset($databases_credentials[$instance->baseUid]['prefix']) ? $databases_credentials[$instance->baseUid]['prefix'] : '';
  118. $connector = $instance->connector;
  119. return $escapeName ? $connector::table_escape.$prefix.$instance->TABLE_NAME.$connector::table_escape : $prefix.$instance->TABLE_NAME;
  120. }
  121. // GESTION SQL
  122. /**
  123. * Verifie l'existence de la table en base de donnée.
  124. * @category manipulation SQL
  125. * @param <String> créé la table si elle n'existe pas
  126. * @return true si la table existe, false dans le cas contraire
  127. */
  128. public static function checkTable($autocreate = false) {
  129. $class = get_called_class();
  130. $instance = new $class();
  131. $query = 'SELECT count(*) as numRows FROM sqlite_master WHERE type="table" AND name=?';
  132. $statement = $instance->customQuery($query, array($class::tableName(false,$instance)));
  133. if ($statement != false) {
  134. $statement = $statement->fetchArray();
  135. if ($statement['numRows'] == 1) $return = true;
  136. }
  137. if ($autocreate && !$return) self::create();
  138. return $return;
  139. }
  140. public static function install($classDirectory) {
  141. foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
  142. $infos = explode('.', basename($file));
  143. $class = array_shift($infos);
  144. require_once($classDirectory.SLASH.$class.'.class.php');
  145. $reflection = new ReflectionClass($class);
  146. if (!class_exists($class) || !method_exists($class, 'create') || $class == get_class() || $reflection->isAbstract()) continue;
  147. $class::create();
  148. }
  149. }
  150. public static function uninstall($classDirectory) {
  151. foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
  152. $infos = explode('.', basename($file));
  153. $class = array_shift($infos);
  154. require_once($classDirectory.SLASH.$class.'.class.php');
  155. $reflection = new ReflectionClass($class);
  156. if (!class_exists($class) || !method_exists($class, 'drop') || $class == get_class() || $reflection->isAbstract()) continue;
  157. $class::drop();
  158. }
  159. }
  160. /**
  161. * Methode de vidage de l'entité.
  162. * @category manipulation SQL
  163. * @return Aucun retour
  164. */
  165. public static function truncate() {
  166. $class = get_called_class();
  167. $instance = new $class();
  168. $connector = $instance->connector;
  169. $sql = $connector::truncate();
  170. $query = Entity::render($sql,array(
  171. 'table' => $class::tableName(false,$instance),
  172. 'fieldMapping' => $instance->fieldMapping
  173. ));
  174. $instance->customQuery($query);
  175. }
  176. /**
  177. * Methode de creation de l'entité.
  178. * @category manipulation SQL
  179. * @return Aucun retour
  180. */
  181. public static function create() {
  182. $class = get_called_class();
  183. $instance = new $class();
  184. $fields = array();
  185. $connector = $instance->connector;
  186. $types = $connector::types();
  187. $fieldMapping = $instance->field_mapping($instance->fields);
  188. foreach ($instance->fields(false) as $slug => $field)
  189. $fields[$slug] = isset($types[$field['type']]) ? $types[$field['type']] : $types['default'];
  190. $sql = $connector::create();
  191. $query = Entity::render($sql,array(
  192. 'table' => $class::tableName(false,$instance),
  193. 'fields' => $fields,
  194. 'fieldMapping' => $instance->fieldMapping
  195. ));
  196. $instance->customQuery($query);
  197. if(isset($instance->indexes)) $instance->index($instance->indexes);
  198. }
  199. public static function drop() {
  200. $class = get_called_class();
  201. $instance = new $class();
  202. $connector = $instance->connector;
  203. $sql = $connector::drop();
  204. $query = Entity::render($sql,array(
  205. 'table' => $class::tableName(false,$instance),
  206. 'fieldMapping' => $instance->fieldMapping
  207. ));
  208. $instance->customQuery($query);
  209. if(isset($instance->indexes)) $instance->index($instance->indexes,false);
  210. }
  211. /**
  212. * Methode d'insertion ou de modifications d'elements de l'entité.
  213. * @category manipulation SQL
  214. * @param Aucun
  215. * @return Aucun retour
  216. */
  217. public function save() {
  218. global $myUser;
  219. $this->updated = time();
  220. if(is_object($myUser) && $myUser->login!='') $this->updater = $myUser->login;
  221. //update
  222. if (isset($this->id) && $this->id > 0) {
  223. $fields = array();
  224. foreach ($this->fields as $field => $type)
  225. $fields[$field] = $this->{$field};
  226. self::change($fields,array('id'=>$this->id));
  227. //insert
  228. } else {
  229. $connector = $this->connector;
  230. $data = array();
  231. $fields = array();
  232. $i = 0;
  233. foreach ($this->fields as $field => $type) {
  234. if((is_array($type) && $type['type'] == 'key') || $type == 'key') continue;
  235. $data[':'.$i] = $this->{$field};
  236. if((is_array($type) && $type['type'] == 'boolean') || $type == 'boolean') $data[':'.$i] = $data[':'.$i] ? 1 : 0;
  237. $fields[$field] = ':'.$i;
  238. $i++;
  239. }
  240. $sql = $connector::insert();
  241. $query = self::render($sql,array(
  242. 'table' => $this->tableName(false,$this),
  243. 'fields' => $fields,
  244. 'fieldMapping' => $this->fieldMapping
  245. ));
  246. $this->customQuery($query, $data);
  247. }
  248. $this->id = !isset($this->id) || !is_numeric($this->id) ? $this->pdo->lastInsertId() : $this->id;
  249. }
  250. /**
  251. * Methode d'insertion massive de l'entité.
  252. * @category manipulation SQL
  253. * @param $entities tableau des entité a inserer (update non géré)
  254. * @param $maxLines grouper par requete de $maxLines maximum
  255. * @return Aucun retour
  256. */
  257. public static function saveAll($entities,$maxLines = 300) {
  258. global $myUser;
  259. if(empty($entities)) return;
  260. $reference = $entities[0];
  261. $connector = $reference->connector;
  262. $sql_head = $connector::insert_head();
  263. $sql_body = $connector::insert_body();
  264. $time = time();
  265. $login = is_object($myUser) && $myUser->login!='' ? $myUser->login : '';
  266. //sépare en requetes groupées de $maxLines lignes max
  267. $entities_groups = array_chunk($entities, $maxLines);
  268. $tableName = $reference->tableName(false,$reference);
  269. foreach ($entities_groups as $entities_group) {
  270. $end = count($entities_group)-1;
  271. $data = array();
  272. foreach($entities_group as $u=>$entity){
  273. $entity->updated = $time;
  274. $entity->updater = $login;
  275. $fields = array();
  276. $i = 0;
  277. foreach ($entity->fields as $field => $type) {
  278. if ($type == 'key') continue;
  279. $data[':'.$u.'a'.$i] = $type!='boolean' ? $entity->{$field} : ($entity->{$field} ? 1:0);
  280. $fields[$field] = ':'.$u.'a'.$i;
  281. $i++;
  282. }
  283. if($u==0){
  284. $query = self::render($sql_head,array(
  285. 'table' => $tableName,
  286. 'fields' => $fields,
  287. 'fieldMapping' => $reference->fieldMapping
  288. ));
  289. }
  290. $query .= self::render($sql_body,array(
  291. 'table' => $entity->tableName(false,$entity),
  292. 'fields' => $fields,
  293. 'fieldMapping' => $entity->fieldMapping
  294. ));
  295. if($u!=$end) $query .= ',';
  296. }
  297. $reference->customQuery($query, $data);
  298. }
  299. }
  300. /**
  301. * Méthode de modification d'éléments de l'entité.
  302. * @category manipulation SQL
  303. * @param <Array> $colonnes=>$valeurs
  304. * @param <Array> $colonnes (WHERE) =>$valeurs (WHERE)
  305. * @return Aucun retour
  306. */
  307. public static function change($columns, $columns2 = array()) {
  308. $class = get_called_class();
  309. $instance = new $class();
  310. $connector = $instance->connector;
  311. $fields = array();
  312. $i = 0;
  313. $values = array();
  314. foreach ($columns as $field => $value) {
  315. $values[':'.$i] = $value;
  316. $fields[$field] = ':'.$i;
  317. $i++;
  318. }
  319. $filters = array();
  320. foreach($columns2 as $key=>$value){
  321. $filter = array(
  322. 'operator' => '=',
  323. 'field' => $key,
  324. 'postoperator' => ''
  325. );
  326. if(strpos($key,':')!==false){
  327. $infos = explode(':',$key);
  328. $filter['operator'] = $infos[1];
  329. $filter['field'] = $infos[0];
  330. }
  331. $fieldInfos = $instance->fieldMapping[$filter['field']];
  332. $filter['type'] = $fieldInfos['type'];
  333. $filter['column'] = $fieldInfos['column'];
  334. $connector::processField($filter,$value,$values,$i);
  335. $filters[] = $filter;
  336. }
  337. $data = array(
  338. 'table' => $class::tableName(false,$instance),
  339. 'fields' => $fields,
  340. 'filter' => !isset($filters) || count($filters) == 0 ? null: $filters,
  341. 'fieldMapping' => $instance->fieldMapping
  342. );
  343. $sql = $connector::update();
  344. $sql = Entity::render($sql,$data);
  345. $instance->customQuery($sql, $values);
  346. }
  347. /**
  348. * Méthode de selection de tous les elements de l'entité.
  349. * @category manipulation SQL
  350. * @param <String> $ordre=null
  351. * @param <String> $limite=null
  352. * @return <Array<Entity>> $Entity
  353. */
  354. public static function populate($order = null, $limit = null,$selColumn = array('*'),$joins = 0) {
  355. $results = self::loadAll(array(), $order, $limit,$selColumn,$joins);
  356. return $results;
  357. }
  358. /**
  359. * Méthode de selection multiple d'elements de l'entité.
  360. * @category manipulation SQL
  361. * @param <Array> $colonnes (WHERE)
  362. * @param <Array> $valeurs (WHERE)
  363. * @param <String> $ordre=null
  364. * @param <String> $limite=null
  365. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  366. * @return <Array<Entity>> $Entity
  367. */
  368. public static function loadAll($columns = array(), $order = null, $limit = null, $selColumn = array('*'), $joins = 0) {
  369. $class = get_called_class();
  370. $instance = new $class();
  371. $connector = $instance->connector;
  372. $values = array();
  373. $i=0;
  374. $filters = array();
  375. foreach($columns as $key=>$value){
  376. $filter = array(
  377. 'operator' => '=',
  378. 'field' => $key,
  379. 'postoperator' => ''
  380. );
  381. if(strpos($key,':')!==false){
  382. $infos = explode(':',$key);
  383. $filter['operator'] = $infos[1];
  384. $filter['field'] = $infos[0];
  385. }
  386. $fieldInfos = $instance->fieldMapping[$filter['field']];
  387. $filter['type'] = $fieldInfos['type'];
  388. $filter['column'] = $fieldInfos['column'];
  389. $connector::processField($filter,$value,$values,$i);
  390. $filters[] = $filter;
  391. }
  392. if(!empty($order)){
  393. foreach ($order as $key=>$clause) {
  394. foreach ($instance->fieldMapping as $attribute => $infos) {
  395. $order[$key] = str_replace( $attribute,$infos['column'],$order[$key]);
  396. }
  397. }
  398. }
  399. $tableName = $class::tableName(false,$instance);
  400. $data = array(
  401. 'table' => $tableName,
  402. 'selected' => $selColumn,
  403. 'limit' => !isset($limit) || count($limit) == 0 ? null: $limit,
  404. 'orderby' => !isset($order) || count($order) == 0 ? null: $order,
  405. 'filter' => !isset($filters) || count($filters) == 0 ? null: $filters,
  406. 'fieldMapping' => $instance->fieldMapping
  407. );
  408. $data['joins'] = array();
  409. if($joins!=0){
  410. foreach ($data['selected'] as $k=>$column)
  411. $data['selected'][$k] = $tableName.'.'.$column;
  412. $data = self::recursiveJoining($instance,$data,$joins);
  413. }
  414. $sql = $connector::select();
  415. $sql = Entity::render($sql,$data);
  416. return $instance->customQuery($sql, $values, true, $joins);
  417. }
  418. /**
  419. * Méthode privée de gestion du join récursif sur les objets liés
  420. * @category manipulation SQL
  421. * @param <Object> $instance $instance de départ
  422. * @param <Array> $data Tableau de construction de la requete via render()
  423. * @param <Int> $iterations Nombre d'iteration réecurive maximum
  424. * @return <Array> $data
  425. */
  426. private static function recursiveJoining($instance,$data,$iterations,$joinInstanceAlias=''){
  427. if($iterations==0) return $data;
  428. $iterations--;
  429. if(isset($instance->links)){
  430. $instanceTable = $instance::tableName();
  431. foreach ($instance->links as $field => $className) {
  432. $className = str_replace('.class.php','',basename($className));
  433. $linkTable = $className::tableName();
  434. $field2 = 'id';
  435. $classField = explode('.',$className);
  436. if(isset($classField[1]))
  437. list($className,$field2) = $classField;
  438. $alias = substr($linkTable,0,3).'_'.$field;
  439. $joinInstance = new $className();
  440. foreach ($joinInstance->fields as $key=>$type)
  441. $data['selected'][] = $alias.'.'.$key.' as '.$linkTable.'_join_'.$key;
  442. $joinTable1 = $instanceTable;
  443. if(!empty($joinInstanceAlias)) $joinTable1 = $joinInstanceAlias;
  444. $data['joins'][] = array(
  445. 'jointable1' => $joinTable1,
  446. 'jointable2' => $linkTable,
  447. 'jointableAlias' => $alias,
  448. 'field1' => $field,
  449. 'field2' => $field2
  450. );
  451. $data = self::recursiveJoining($joinInstance,$data,$iterations,$alias);
  452. }
  453. }
  454. return $data;
  455. }
  456. /**
  457. * Methode de comptage des éléments de l'entité.
  458. * @category manipulation SQL
  459. * @return<Integer> nombre de ligne dans l'entité'
  460. */
  461. public static function rowCount($columns = array()) {
  462. $values = array();
  463. $class = get_called_class();
  464. $instance = new $class();
  465. $connector = $instance->connector;
  466. $i=0;
  467. $values = array();
  468. $filters = array();
  469. foreach($columns as $key=>$value){
  470. $filter = array(
  471. 'operator' => '=',
  472. 'field' => $key,
  473. 'postoperator' => ''
  474. );
  475. if(strpos($key,':')!==false){
  476. $infos = explode(':',$key);
  477. $filter['operator'] = $infos[1];
  478. $filter['field'] = $infos[0];
  479. }
  480. $fieldInfos = $instance->fieldMapping[$filter['field']];
  481. $filter['type'] = $fieldInfos['type'];
  482. $filter['column'] = $fieldInfos['column'];
  483. $connector::processField($filter,$value,$values,$i);
  484. $filters[] = $filter;
  485. }
  486. $data = array(
  487. 'table' => $class::tableName(false,$instance),
  488. 'selected' => 'id' ,
  489. 'filter' => count($filters) == 0 ? null: $filters,
  490. 'fieldMapping' => $instance->fieldMapping
  491. );
  492. $sql = $connector::count();
  493. $execQuery = $instance->customQuery(Entity::render($sql,$data), $values);
  494. $row = $execQuery->fetch();
  495. return $row['number'];
  496. }
  497. public static function loadAllOnlyColumn($selColumn, $columns, $order = null, $limit = null) {
  498. $objects = self::loadAll($columns, $order, $limit, $selColumn);
  499. if (count($objects) == 0) $objects = array();
  500. return $objects;
  501. }
  502. /**
  503. * Méthode de selection unique d'élements de l'entité.
  504. *
  505. * @category manipulation SQL
  506. *
  507. * @param <Array> $colonnes (WHERE)
  508. * @param <Array> $valeurs (WHERE)
  509. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  510. *
  511. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  512. */
  513. public static function load($columns = array(),$joins =0) {
  514. $objects = self::loadAll($columns, null, array('1'),array('*'),$joins);
  515. if (!isset($objects[0])) $objects[0] = false;
  516. return $objects[0];
  517. }
  518. /**
  519. * Méthode de selection unique d'élements de l'entité.
  520. * @param <Array> $colonnes (WHERE)
  521. * @param <Array> $valeurs (WHERE)
  522. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  523. * @deprecated use byId
  524. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  525. */
  526. public static function getById($id,$joins =0 ) {
  527. return self::byId($id,$joins =0);
  528. }
  529. /**
  530. * Méthode de selection unique d'élements de l'entité.
  531. * @param <Array> $colonnes (WHERE)
  532. * @param <Array> $valeurs (WHERE)
  533. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  534. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  535. */
  536. public static function byId($id,$joins =0 ) {
  537. return self::load(array('id' => $id),$joins);
  538. }
  539. //parsing des templates sql de cnnecteurs avec les filtres/columns/data...
  540. public static function render($sql,$data=array()) {
  541. //loop
  542. $sql = preg_replace_callback('/{{\:([^\/\:\?}]*)}}(.*?){{\/\:[^\/\:\?}]*}}/',function($matches) use ($data) {
  543. $tag = $matches[1];
  544. $sqlTpl = $matches[2];
  545. $sql = '';
  546. if(isset($data[$tag])){
  547. $i = 0;
  548. $values = $data[$tag];
  549. if($tag =='joins'){
  550. //joins
  551. foreach($values as $join){
  552. $occurence = $sqlTpl;
  553. foreach($join as $key=>$value){
  554. $occurence = str_replace(array('{{'.$key.'}}'),array($value),$occurence);
  555. }
  556. $sql.= $occurence;
  557. }
  558. }else if($tag =='filter'){
  559. //filters
  560. foreach($values as $key=>$value){
  561. $i++;
  562. $last = $i == count($values);
  563. $operator = $value['operator'];
  564. $postoperator = $value['postoperator'];
  565. $key = $value['column'];
  566. $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,
  567. $value['tag'],
  568. $operator,
  569. $postoperator),
  570. $sqlTpl);
  571. $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
  572. return $last? '': $matches[1];
  573. },$occurence);
  574. $sql.= $occurence;
  575. }
  576. } else {
  577. //Autre boucles
  578. foreach($values as $key=>$value){
  579. $i++;
  580. $last = $i == count($values);
  581. $operator = isset($data['operator']) ? $data['operator'][0] : '=';
  582. $postoperator = isset($data['postoperator']) ? $data['postoperator'][0] : '';
  583. if(strpos($key,':')!==false){
  584. $infos = explode(':',$key);
  585. $key = $infos[0];
  586. $operator = $infos[1];
  587. if($operator=='IN' || $operator=='NOT IN'){
  588. $operator = $operator.'(';
  589. $postoperator = ')';
  590. }
  591. }
  592. $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,$value,$operator,$postoperator),$sqlTpl);
  593. $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
  594. return $last? '': $matches[1];
  595. },$occurence);
  596. $sql.= $occurence;
  597. }
  598. }
  599. return $sql;
  600. }
  601. return '';
  602. },$sql);
  603. //conditions
  604. $sql = preg_replace_callback('/{{\?([^\/\:\?}]*)}}(.*?){{\/\?[^\/\:\?}]*}}/',function($matches) use ($data) {
  605. $key = $matches[1];
  606. $sql = $matches[2];
  607. return !isset($data[$key]) || (is_array($data[$key]) && count($data[$key])==0) ?'':$sql;
  608. },$sql);
  609. //simple vars
  610. $sql = preg_replace_callback('/{{([^\/\:\;\?}]*)}}/',function($matches) use ($data) {
  611. $key = $matches[1];
  612. return isset($data[$key])?$data[$key]:'';
  613. },$sql);
  614. return $sql;
  615. }
  616. /**
  617. * Methode de définition de l'éxistence d'un moins un des éléments spécifiés en base.
  618. *
  619. * @category manipulation SQL
  620. * @return<boolean> existe (true) ou non (false)
  621. */
  622. public static function exist($columns = array()) {
  623. $result = self::rowCount($columns);
  624. return $result != 0;
  625. }
  626. public static function deleteById($id) {
  627. self::delete(array('id' => $id));
  628. }
  629. /**
  630. * Méthode de Suppression d'elements de l'entité.
  631. * @category manipulation SQL
  632. * @param <Array> $colonnes (WHERE)
  633. * @param <Array> $valeurs (WHERE)
  634. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  635. * @return Aucun retour
  636. */
  637. public static function delete($columns, $limit = array()) {
  638. $values = array();
  639. $class = get_called_class();
  640. $instance = new $class();
  641. $connector = $instance->connector;
  642. $i=0;
  643. $values = array();
  644. $filters = array();
  645. foreach($columns as $key=>$value){
  646. $filter = array(
  647. 'operator' => '=',
  648. 'field' => $key,
  649. 'postoperator' => ''
  650. );
  651. if(strpos($key,':')!==false){
  652. $infos = explode(':',$key);
  653. $filter['operator'] = $infos[1];
  654. $filter['field'] = $infos[0];
  655. }
  656. $fieldInfos = $instance->fieldMapping[$filter['field']];
  657. $filter['type'] = $fieldInfos['type'];
  658. $filter['column'] = $fieldInfos['column'];
  659. $connector::processField($filter,$value,$values,$i);
  660. $filters[] = $filter;
  661. }
  662. $data = array(
  663. 'table' => $class::tableName(false,$instance),
  664. 'limit' => count($limit) == 0 ? null: $limit,
  665. 'filter' => count($filters) == 0 ? null: $filters,
  666. 'fieldMapping' => $instance->fieldMapping
  667. );
  668. $sql = $connector::delete();
  669. return $instance->customQuery(Entity::render($sql,$data), $values);
  670. }
  671. /**
  672. * Méthode d'indexation de la ou les colonnes ciblées
  673. * nb : il est possible d'appeller automatiquement cette méthode sur les classes entity lors du create
  674. * si la classe contient l'attribut $this->indexes = array(...);
  675. * @category manipulation SQL
  676. * @param <Array> | <String> $colonne(s)
  677. * @param <Boolean> Mode (true : ajout, false : suppression)
  678. * @return Aucun retour
  679. */
  680. public static function index($columns,$mode = true){
  681. if(!is_array($columns)) $columns = array($columns);
  682. $columns = array_filter($columns);
  683. $class = get_called_class();
  684. $instance = new $class();
  685. $connector = $instance->connector;
  686. $tableName = $class::tableName(false,$instance);
  687. foreach($columns as $column){
  688. if(!is_array($column)) $column = array($column);
  689. $data = array(
  690. 'table' => $tableName,
  691. 'column' => '`'.implode('`,`',$column).'`',
  692. 'index_name' => $tableName.'_'.implode('_',$column),
  693. 'fieldMapping' => $instance->fieldMapping
  694. );
  695. $results = $class::staticQuery(Entity::render($connector::count_index(),$data));
  696. $exists = $results->fetch();
  697. if($mode){
  698. if($exists['exists'] != 1) $class::staticQuery(Entity::render($connector::create_index(),$data));
  699. }else{
  700. if($exists['exists'] > 0) $class::staticQuery(Entity::render($connector::drop_index(),$data));
  701. }
  702. }
  703. }
  704. //Génération d'une pagination
  705. public static function paginate($itemPerPage,$currentPage,&$query,$data,$alias=''){
  706. $class = get_called_class();
  707. $instance = new $class();
  708. $keys = array_keys($instance->fields, 'key');
  709. $key = count($keys) == 1 ? $keys[0] : 'id';
  710. $tableName = $class::tableName(false,$instance);
  711. $queryNumber = $query;
  712. $queryNumber = preg_replace("/(?<!\([^(\)])(SELECT.+[\n|\t]*FROM[\s\t\r\n])({{table}}|`?".$tableName."`?)(?![^(\)]*\))/iU", 'SELECT DISTINCT '.(!empty($alias) ? $alias : $tableName).'.'.$key.' FROM $2',$queryNumber);
  713. $queryNumber = $class::staticQuery('SELECT COUNT(*) FROM ('.$queryNumber.') number',$data)->fetch();
  714. $number = $queryNumber[0];
  715. $pageNumber = $number / $itemPerPage;
  716. if($currentPage >= $pageNumber) $currentPage = 0;
  717. $limit = ' LIMIT '.($currentPage*$itemPerPage).','.$itemPerPage;
  718. $query .= $limit;
  719. return array(
  720. 'pages' => $pageNumber,
  721. 'current' => $currentPage,
  722. 'total' => $number
  723. );
  724. }
  725. public static function provide($parameter = 'id',$join=0){
  726. global $_;
  727. $class = get_called_class();
  728. return !empty($_[$parameter]) ? $class::getById($_[$parameter],$join) : new $class();
  729. }
  730. // L'alias utilisé pour la colonne du join doit avoir la syntaxe [table]_join_[field]
  731. // Ex : address_join_street
  732. public static function staticQuery($query, $data = array(), $fill = false,$joins = 0) {
  733. $class = get_called_class();
  734. $instance = new $class();
  735. return $instance->customQuery($query, $data, $fill,$joins);
  736. }
  737. public function customQuery($query, $data = array(), $fill = false,$joins = 0) {
  738. $query = str_replace('{{table}}', $this->tableName(true,$this), $query);
  739. $mapping = $this->fieldMapping;
  740. $query = preg_replace_callback('/{{([^}]*)}}/si', function($match) use ($mapping){
  741. return isset($mapping[$match[1]]) && isset($mapping[$match[1]]['column']) ? $mapping[$match[1]]['column'] : $match[0];
  742. }, $query);
  743. try{
  744. if(BASE_DEBUG) self::logFile($query.' :: '.json_encode($data, JSON_UNESCAPED_UNICODE),debug_backtrace());
  745. $results = $this->pdo->prepare($query);
  746. $results->execute($data);
  747. if (!$results) throw new Exception(json_encode($this->pdo->errorInfo()));
  748. }catch(Exception $e){
  749. Log::put("[SQL ERROR] - Erreur : ".$e->getMessage().' - Requete : '.$query.' - Données : '.json_encode($data, JSON_UNESCAPED_UNICODE));
  750. if(BASE_DEBUG) self::logFile( "Erreur : ".$e->getMessage());
  751. throw $e;
  752. }
  753. if (!$fill) return $results;
  754. $class = get_class($this);
  755. $objects = array();
  756. $results = $results->fetchAll(PDO::FETCH_ASSOC);
  757. foreach ($results as $queryReturn) {
  758. $object = new $class();
  759. foreach ($this->fields as $field => $type) {
  760. $dbField = $field;
  761. if(is_array($type)){
  762. if(isset($type['column'])) $dbField = $type['column'];
  763. $type = $type['type'];
  764. }
  765. if (isset($queryReturn[$dbField])) {
  766. $object->{$field} = $queryReturn[$dbField];
  767. unset($queryReturn[$dbField]);
  768. }
  769. }
  770. if($joins>0) $object = self::recursiveJoiningFill($object,$queryReturn,$joins);
  771. foreach ($queryReturn as $key => $value) {
  772. if(!is_numeric($key)) $object->foreignColumns[$key] = $value;
  773. }
  774. $objects[] = $object;
  775. unset($object);
  776. }
  777. return $objects == null ? array() : $objects;
  778. }
  779. //Récuperation d'une/plusieurs colonne non référencée dans l'objet mais récuperée dans une requete static query
  780. public function foreign($key=null){
  781. if(!isset($key)) return $this->foreignColumns;
  782. return isset($this->foreignColumns[$key]) ? $this->foreignColumns[$key] : '';
  783. }
  784. //Renvois une chaine de selecteur sql devant être join
  785. //ex : Client::joinString('cli') --> cli.id client_join_id,cli.label client_join_label ...
  786. public static function joinString($prefix = ''){
  787. $class = get_called_class();
  788. $instance = new $class();
  789. $tableName = $class::tableName(false,$instance);
  790. $columns = array();
  791. foreach($instance->fields() as $field)
  792. $columns[] = $prefix.'.'.$field.' '.$tableName.'_join_'.$field.' ';
  793. return implode(', ',$columns);
  794. }
  795. private static function recursiveJoiningFill($object,$queryReturn,$iterations){
  796. if($iterations == 0) return $object;
  797. $iterations--;
  798. if(isset($object->links)){
  799. foreach ($object->links as $link=>$classLink) {
  800. $classLink = str_replace('.class.php','',basename($classLink));
  801. $classField = explode('.',$classLink);
  802. if(isset($classField[1]))
  803. $classLink = $classField[0];
  804. $instanceLink = new $classLink();
  805. $tableName = $classLink::tableName(false,$instanceLink);
  806. foreach ($instanceLink->fields as $field => $type)
  807. if(isset($queryReturn[$tableName.'_join_'.$field])) $instanceLink->{$field} = $queryReturn[$tableName.'_join_'.$field];
  808. $instanceLink = self::recursiveJoiningFill($instanceLink,$queryReturn,$iterations);
  809. $object->joins[$link] = $instanceLink;
  810. }
  811. }
  812. return $object;
  813. }
  814. /**
  815. * 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
  816. * Nb : cette méthode ne fonctionne que si vous avez placé le parametre joins > 0 dans la méthode LoadALl
  817. * Nb : cette méthode ne fonctionne que si vous avez précisé le lien entre Contact et Adress dans la classe Contact via :
  818. protected $links = array(
  819. 'address' => 'Address'
  820. );
  821. *
  822. * @category manipulation SQL
  823. *
  824. * @param <Array> $colonnes (WHERE)
  825. * @param <Array> $valeurs (WHERE)
  826. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  827. *
  828. * @return Aucun retour
  829. */
  830. public function join($field){
  831. return isset($this->joins[$field])?$this->joins[$field]:'';
  832. }
  833. public static function logFile($msg,$backtrace=null){
  834. if(strpos(ROOT_URL, '127.0.0.1') === false && strpos(ROOT_URL, 'dev.local') === false && strpos(ROOT_URL, 'localhost') === false ) return;
  835. file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', date('H:i:s').' | '.$msg.PHP_EOL,FILE_APPEND);
  836. //A décommenter pour obtenir la stacktrace d'un appel sql dans le fichier sql.debug.sql
  837. //if(isset($backtrace)) file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', json_encode($backtrace,JSON_PRETTY_PRINT).PHP_EOL,FILE_APPEND);
  838. }
  839. public static function log_executed_query($string, $data) {
  840. $indexed = $data==array_values($data);
  841. foreach($data as $k=>$v) {
  842. if(is_string($v)) $v="'$v'";
  843. if($indexed) $string=preg_replace('/\?/',$v,$string,1);
  844. else $string=str_replace(":$k",$v,$string);
  845. }
  846. self::logFile($string);
  847. }
  848. }