1.
<?php
2.
3.
/*
4.
Généalogik
5.
6.
Class for table : depot.
7.
8.
07/03/2021 10:45
9.
10.
lvardon@laposte.net - 2021
11.
12.
Licence libre
13.
14.
Usage examples :
15.
16.
<?php
17.
18.
include_once("classdir/dbconnect.php);
19.
include_once("classdir/class_depot.php);
20.
21.
// Class instance
22.
$mydepot = depot( $db );
23.
24.
// Select all reccords :
25.
$results = $mydepot->selectAll();
26.
foreach($results as $row) {
27.
print_r($row);
28.
}
29.
30.
// Select reccord #12 :
31.
$id = 12;
32.
$row = $mydepot->select($id);
33.
print_r($row);
34.
35.
?>
36.
37.
*/
38.
class _depot {
39.
40.
public $db;
41.
public $lasterror="";
42.
public $lastInsertId=-1;
43.
public $count = 0;
44.
45.
/*
46.
Initialise object with dbHanler.
47.
48.
*/
49.
function __construct( $db ) {
50.
$this->db = $db;
51.
}
52.
53.
/*
54.
Select one reccord from depot.
55.
56.
parameters : $id (reccord id).
57.
return : associative array of reccord.
58.
*/
59.
function select($id) {
60.
61.
$this->lasterror="";
62.
63.
$sqlSt="
64.
select
65.
a.id as 'a.id',
66.
a.name as 'a.name',
67.
a.code as 'a.code',
68.
a.description as 'a.description',
69.
a.icon as 'a.icon',
70.
a.url as 'a.url',
71.
a.adress_1 as 'a.adress_1',
72.
a.adress_2 as 'a.adress_2',
73.
a.city as 'a.city',
74.
a.zip as 'a.zip',
75.
a.country as 'a.country'
76.
77.
78.
from depot a
79.
80.
where a.id = :id;
81.
";
82.
83.
try {
84.
85.
$stmt = $this->db->prepare($sqlSt);
86.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
87.
$stmt->execute();
88.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
89.
return $results;
90.
91.
} catch (Exception $e) {
92.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
93.
return false;
94.
}
95.
}
96.
97.
function selectPrev($id) {
98.
99.
$this->lasterror="";
100.
101.
$sqlSt="
102.
select
103.
a.id as 'a.id',
104.
a.name as 'a.name',
105.
a.code as 'a.code',
106.
a.description as 'a.description',
107.
a.icon as 'a.icon',
108.
a.url as 'a.url',
109.
a.adress_1 as 'a.adress_1',
110.
a.adress_2 as 'a.adress_2',
111.
a.city as 'a.city',
112.
a.zip as 'a.zip',
113.
a.country as 'a.country'
114.
115.
116.
from depot a
117.
118.
where a.id = (SELECT id FROM depot WHERE id < :id ORDER BY id DESC LIMIT 1) ;
119.
";
120.
121.
try {
122.
123.
$stmt = $this->db->prepare($sqlSt);
124.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
125.
$stmt->execute();
126.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
127.
return $results;
128.
129.
} catch (Exception $e) {
130.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
131.
return false;
132.
}
133.
}
134.
135.
136.
function selectNext($id) {
137.
138.
$this->lasterror="";
139.
140.
$sqlSt="
141.
select
142.
a.id as 'a.id',
143.
a.name as 'a.name',
144.
a.code as 'a.code',
145.
a.description as 'a.description',
146.
a.icon as 'a.icon',
147.
a.url as 'a.url',
148.
a.adress_1 as 'a.adress_1',
149.
a.adress_2 as 'a.adress_2',
150.
a.city as 'a.city',
151.
a.zip as 'a.zip',
152.
a.country as 'a.country'
153.
154.
155.
from depot a
156.
157.
where a.id = (SELECT id FROM depot WHERE id > :id ORDER BY id ASC LIMIT 1) ;
158.
";
159.
160.
try {
161.
162.
$stmt = $this->db->prepare($sqlSt);
163.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
164.
$stmt->execute();
165.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
166.
return $results;
167.
168.
} catch (Exception $e) {
169.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
170.
return false;
171.
}
172.
}
173.
174.
/*
175.
Select all reccords from depot (with default order).
176.
177.
parameters : None
178.
return : associative array of reccords.
179.
*/
180.
function selectAll() {
181.
182.
$this->lasterror="";
183.
184.
$sqlSt="
185.
select
186.
a.id as 'a.id',
187.
a.name as 'a.name',
188.
a.code as 'a.code',
189.
a.description as 'a.description',
190.
a.icon as 'a.icon',
191.
a.url as 'a.url',
192.
a.adress_1 as 'a.adress_1',
193.
a.adress_2 as 'a.adress_2',
194.
a.city as 'a.city',
195.
a.zip as 'a.zip',
196.
a.country as 'a.country'
197.
198.
199.
from depot a
200.
201.
;
202.
";
203.
204.
try {
205.
206.
$stmt = $this->db->prepare($sqlSt);
207.
$stmt->execute();
208.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
209.
return $results;
210.
211.
} catch (Exception $e) {
212.
$this->lasterror = $e->getMessage();
213.
$this->count = 0;
214.
return false;
215.
}
216.
}
217.
218.
/*
219.
Update depot
220.
221.
parameters : $id (reccord id) and fields list values.
222.
return : True if no error.
223.
*/
224.
function update( $id, $name,$code,$description,$icon,$url,$adress_1,$adress_2,$city,$zip,$country ) {
225.
226.
$this->lasterror="";
227.
228.
if ($name == '') $name = null;
229.
if ($code == '') $code = null;
230.
if ($description == '') $description = null;
231.
if ($icon == '') $icon = null;
232.
if ($url == '') $url = null;
233.
if ($adress_1 == '') $adress_1 = null;
234.
if ($adress_2 == '') $adress_2 = null;
235.
if ($city == '') $city = null;
236.
if ($zip == '') $zip = null;
237.
if ($country == '') $country = null;
238.
239.
240.
if ($this->checkValue( array(
241.
$name => '',
242.
$code => '',
243.
$description => '',
244.
$icon => '',
245.
$url => '',
246.
$adress_1 => '',
247.
$adress_2 => '',
248.
$city => '',
249.
$zip => '',
250.
$country => ''
251.
252.
) ) == false )
253.
{
254.
$this->lasterror="Check fields false";
255.
return false;
256.
}
257.
258.
$sqlSt="
259.
update depot set
260.
name = :name,
261.
code = :code,
262.
description = :description,
263.
icon = :icon,
264.
url = :url,
265.
adress_1 = :adress_1,
266.
adress_2 = :adress_2,
267.
city = :city,
268.
zip = :zip,
269.
country = :country
270.
271.
where id = :id;
272.
";
273.
274.
try {
275.
276.
$stmt = $this->db->prepare($sqlSt);
277.
278.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
279.
280.
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
281.
$stmt->bindParam(':code', $code, PDO::PARAM_STR);
282.
$stmt->bindParam(':description', $description, PDO::PARAM_STR);
283.
$stmt->bindParam(':icon', $icon, PDO::PARAM_STR);
284.
$stmt->bindParam(':url', $url, PDO::PARAM_STR);
285.
$stmt->bindParam(':adress_1', $adress_1, PDO::PARAM_STR);
286.
$stmt->bindParam(':adress_2', $adress_2, PDO::PARAM_STR);
287.
$stmt->bindParam(':city', $city, PDO::PARAM_STR);
288.
$stmt->bindParam(':zip', $zip, PDO::PARAM_STR);
289.
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
290.
291.
$stmt->execute();
292.
293.
if ($stmt->rowCount() == 0) {
294.
$this->lasterror = 'Erreur Sql update '.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>description</b> = [$description],<b>icon</b> = [$icon],<b>url</b> = [$url],<b>adress_1</b> = [$adress_1],<b>adress_2</b> = [$adress_2],<b>city</b> = [$city],<b>zip</b> = [$zip],<b>country</b> = [$country]";
295.
296.
return false;
297.
}
298.
299.
return true;
300.
301.
} catch (Exception $e) {
302.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>description</b> = [$description],<b>icon</b> = [$icon],<b>url</b> = [$url],<b>adress_1</b> = [$adress_1],<b>adress_2</b> = [$adress_2],<b>city</b> = [$city],<b>zip</b> = [$zip],<b>country</b> = [$country]";
303.
return false;
304.
}
305.
306.
}
307.
308.
/*
309.
Insert new reccord into depot.
310.
311.
Parameters : fields values list.
312.
return : True if no error.
313.
*/
314.
function insert( $name,$code,$description,$icon,$url,$adress_1,$adress_2,$city,$zip,$country ) {
315.
316.
$this->lasterror="";
317.
$this->lastInsertId = -1;
318.
319.
if ($name == '') $name = null;
320.
if ($code == '') $code = null;
321.
if ($description == '') $description = null;
322.
if ($icon == '') $icon = null;
323.
if ($url == '') $url = null;
324.
if ($adress_1 == '') $adress_1 = null;
325.
if ($adress_2 == '') $adress_2 = null;
326.
if ($city == '') $city = null;
327.
if ($zip == '') $zip = null;
328.
if ($country == '') $country = null;
329.
330.
331.
if ($this->checkValue( array(
332.
$name => '',
333.
$code => '',
334.
$description => '',
335.
$icon => '',
336.
$url => '',
337.
$adress_1 => '',
338.
$adress_2 => '',
339.
$city => '',
340.
$zip => '',
341.
$country => ''
342.
343.
) ) == false )
344.
{
345.
$this->lasterror="Check fields error";
346.
return false;
347.
}
348.
349.
$sqlSt="
350.
insert into depot (
351.
name,
352.
code,
353.
description,
354.
icon,
355.
url,
356.
adress_1,
357.
adress_2,
358.
city,
359.
zip,
360.
country
361.
362.
)
363.
values (
364.
:name,
365.
:code,
366.
:description,
367.
:icon,
368.
:url,
369.
:adress_1,
370.
:adress_2,
371.
:city,
372.
:zip,
373.
:country
374.
375.
);
376.
";
377.
378.
try {
379.
380.
$stmt = $this->db->prepare($sqlSt);
381.
382.
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
383.
$stmt->bindParam(':code', $code, PDO::PARAM_STR);
384.
$stmt->bindParam(':description', $description, PDO::PARAM_STR);
385.
$stmt->bindParam(':icon', $icon, PDO::PARAM_STR);
386.
$stmt->bindParam(':url', $url, PDO::PARAM_STR);
387.
$stmt->bindParam(':adress_1', $adress_1, PDO::PARAM_STR);
388.
$stmt->bindParam(':adress_2', $adress_2, PDO::PARAM_STR);
389.
$stmt->bindParam(':city', $city, PDO::PARAM_STR);
390.
$stmt->bindParam(':zip', $zip, PDO::PARAM_STR);
391.
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
392.
393.
394.
$stmt->execute();
395.
$this->lastInsertId = $this->db->lastInsertId();
396.
397.
if ($stmt->rowCount() == 0) {
398.
$this->lasterror = 'Erreur Sql insert'.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>description</b> = [$description],<b>icon</b> = [$icon],<b>url</b> = [$url],<b>adress_1</b> = [$adress_1],<b>adress_2</b> = [$adress_2],<b>city</b> = [$city],<b>zip</b> = [$zip],<b>country</b> = [$country]";
399.
return false;
400.
}
401.
402.
return true;
403.
404.
} catch (Exception $e) {
405.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>description</b> = [$description],<b>icon</b> = [$icon],<b>url</b> = [$url],<b>adress_1</b> = [$adress_1],<b>adress_2</b> = [$adress_2],<b>city</b> = [$city],<b>zip</b> = [$zip],<b>country</b> = [$country]";
406.
return false;
407.
}
408.
}
409.
410.
/*
411.
Delete one reccord from depot.
412.
413.
parameters : $id (reccord id).
414.
return : True if no error.
415.
*/
416.
function delete($id) {
417.
418.
$this->lasterror="";
419.
420.
$sqlSt="
421.
delete from depot
422.
where id = :id;
423.
";
424.
425.
try {
426.
427.
$stmt = $this->db->prepare($sqlSt);
428.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
429.
$stmt->execute();
430.
431.
if ($stmt->rowCount() == 0) {
432.
$this->lasterror = "Erreur Sql delete ".' (<b>Id</b> ='.$id.')';
433.
return false;
434.
}
435.
436.
} catch (Exception $e) {
437.
$this->lasterror = $e->getMessage().' (<b>Id</b>='.$id.')';
438.
return false;
439.
}
440.
return true;
441.
}
442.
443.
/*
444.
Get id min, id max, record count from depot
445.
446.
parameters : None
447.
return : count value or False in case of error
448.
*/
449.
function getCount() {
450.
451.
$this->lasterror="";
452.
453.
$sqlSt="
454.
select min(id) as minid, max(id) as maxid, count(*) as count
455.
from depot
456.
";
457.
458.
try {
459.
460.
$stmt = $this->db->prepare($sqlSt);
461.
$stmt->execute();
462.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
463.
return $results[0];
464.
465.
} catch (Exception $e) {
466.
$this->lasterror = $e->getMessage();
467.
return array( 'minid' => -1, 'maxid' => -1, 'count' => -1);
468.
}
469.
470.
//return $this->count;
471.
return $results[0];
472.
}
473.
474.
475.
476.
477.
public $colmeta = [
478.
'a.id' => array (
479.
'metades' => "Identifiant dépôt",
480.
'sortformat' => "{{sortformat}}",
481.
'listlen' => 6,
482.
'listlib' => 'id',
483.
'listlink' => 'on',
484.
'selectlen' => 0,
485.
'selectlib' => 'Selection',
486.
'selectlink' => 'on',
487.
'selectdisplay' => '',
488.
'selectdisplaywidth' => '0',
489.
'selectdisplayheight' => '0',
490.
'listdisplay' => '',
491.
'listdisplaywidth' => '0',
492.
'listdisplayheight' => '0'
493.
494.
),
495.
'a.name' => array (
496.
'metades' => "Nom du dépôt",
497.
'sortformat' => "",
498.
'listlen' => 45,
499.
'listlib' => 'Nom' ,
500.
'listlink' => '',
501.
'selectlen' => 45,
502.
'selectlib' => 'a.name' ,
503.
'selectlink' => 'on',
504.
'selectdisplay' => 'text',
505.
'selectdisplaywidth' => '0',
506.
'selectdisplayheight' => '0',
507.
'listdisplay' => 'text',
508.
'listdisplaywidth' => '0',
509.
'listdisplayheight' => '0'
510.
),
511.
'a.code' => array (
512.
'metades' => "Code, identifiant du dépôt",
513.
'sortformat' => "",
514.
'listlen' => 4,
515.
'listlib' => 'Code' ,
516.
'listlink' => '',
517.
'selectlen' => 4,
518.
'selectlib' => 'Code' ,
519.
'selectlink' => '',
520.
'selectdisplay' => 'color',
521.
'selectdisplaywidth' => '50',
522.
'selectdisplayheight' => '50',
523.
'listdisplay' => 'color',
524.
'listdisplaywidth' => '50',
525.
'listdisplayheight' => '50'
526.
),
527.
'a.description' => array (
528.
'metades' => "Description",
529.
'sortformat' => "",
530.
'listlen' => 20,
531.
'listlib' => 'Description' ,
532.
'listlink' => '',
533.
'selectlen' => 0,
534.
'selectlib' => 'a_description' ,
535.
'selectlink' => '',
536.
'selectdisplay' => 'text',
537.
'selectdisplaywidth' => '0',
538.
'selectdisplayheight' => '0',
539.
'listdisplay' => 'text',
540.
'listdisplaywidth' => '0',
541.
'listdisplayheight' => '0'
542.
),
543.
'a.icon' => array (
544.
'metades' => "Icone associé à ce dépôt",
545.
'sortformat' => "",
546.
'listlen' => 20,
547.
'listlib' => 'icone' ,
548.
'listlink' => '',
549.
'selectlen' => 20,
550.
'selectlib' => 'icone' ,
551.
'selectlink' => '',
552.
'selectdisplay' => 'text',
553.
'selectdisplaywidth' => '0',
554.
'selectdisplayheight' => '0',
555.
'listdisplay' => 'urlpic',
556.
'listdisplaywidth' => '50',
557.
'listdisplayheight' => '50'
558.
),
559.
'a.url' => array (
560.
'metades' => "Adresse web du dépôt",
561.
'sortformat' => "",
562.
'listlen' => 12,
563.
'listlib' => 'Url' ,
564.
'listlink' => '',
565.
'selectlen' => 0,
566.
'selectlib' => 'a_url' ,
567.
'selectlink' => '',
568.
'selectdisplay' => 'text',
569.
'selectdisplaywidth' => '0',
570.
'selectdisplayheight' => '0',
571.
'listdisplay' => 'url',
572.
'listdisplaywidth' => '0',
573.
'listdisplayheight' => '0'
574.
),
575.
'a.adress_1' => array (
576.
'metades' => "Adresse du dépôt",
577.
'sortformat' => "",
578.
'listlen' => 0,
579.
'listlib' => 'adresse' ,
580.
'listlink' => '',
581.
'selectlen' => 0,
582.
'selectlib' => 'adresse' ,
583.
'selectlink' => '',
584.
'selectdisplay' => 'text',
585.
'selectdisplaywidth' => '0',
586.
'selectdisplayheight' => '0',
587.
'listdisplay' => 'text',
588.
'listdisplaywidth' => '0',
589.
'listdisplayheight' => '0'
590.
),
591.
'a.adress_2' => array (
592.
'metades' => "Seconde ligne de l adresse du dépôt",
593.
'sortformat' => "",
594.
'listlen' => 0,
595.
'listlib' => 'adresse (suite)' ,
596.
'listlink' => '',
597.
'selectlen' => 0,
598.
'selectlib' => 'adresse (suite)' ,
599.
'selectlink' => '',
600.
'selectdisplay' => 'text',
601.
'selectdisplaywidth' => '0',
602.
'selectdisplayheight' => '0',
603.
'listdisplay' => 'text',
604.
'listdisplaywidth' => '0',
605.
'listdisplayheight' => '0'
606.
),
607.
'a.city' => array (
608.
'metades' => "Ville",
609.
'sortformat' => "",
610.
'listlen' => 0,
611.
'listlib' => 'Ville' ,
612.
'listlink' => '',
613.
'selectlen' => 0,
614.
'selectlib' => 'Ville' ,
615.
'selectlink' => '',
616.
'selectdisplay' => 'text',
617.
'selectdisplaywidth' => '0',
618.
'selectdisplayheight' => '0',
619.
'listdisplay' => 'text',
620.
'listdisplaywidth' => '0',
621.
'listdisplayheight' => '0'
622.
),
623.
'a.zip' => array (
624.
'metades' => "Code Postal",
625.
'sortformat' => "",
626.
'listlen' => 0,
627.
'listlib' => 'Code Postal' ,
628.
'listlink' => '',
629.
'selectlen' => 0,
630.
'selectlib' => 'Code Postal' ,
631.
'selectlink' => '',
632.
'selectdisplay' => 'text',
633.
'selectdisplaywidth' => '0',
634.
'selectdisplayheight' => '0',
635.
'listdisplay' => 'text',
636.
'listdisplaywidth' => '0',
637.
'listdisplayheight' => '0'
638.
),
639.
'a.country' => array (
640.
'metades' => "Pays",
641.
'sortformat' => "",
642.
'listlen' => 12,
643.
'listlib' => 'Pays' ,
644.
'listlink' => 'on',
645.
'selectlen' => 0,
646.
'selectlib' => 'Pays' ,
647.
'selectlink' => '',
648.
'selectdisplay' => 'text',
649.
'selectdisplaywidth' => '0',
650.
'selectdisplayheight' => '0',
651.
'listdisplay' => 'text',
652.
'listdisplaywidth' => '0',
653.
'listdisplayheight' => '0'
654.
)
655.
656.
657.
];
658.
659.
/*
660.
Get field property value
661.
662.
Parameters : field name and property name.
663.
return : value of property.
664.
*/
665.
666.
function getMeta( $fieldkey , $metakey ) {
667.
return $this->colmeta [$fieldkey][$metakey];
668.
}
669.
670.
/*
671.
Get labels for list table header.
672.
673.
Parameters : None.
674.
return : String.
675.
*/
676.
677.
function getTableHeaderList() {
678.
679.
$labelsStr ='';
680.
681.
foreach ( $this->colmeta as $k => $v) {
682.
if ( $v['listlen'] > 0 )
683.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['listlib'] .'</th>'."\n";
684.
}
685.
return $labelsStr;
686.
}
687.
688.
function getTableFieldLenList( $field ) {
689.
690.
return $this->colmeta[$field]['listlen'];
691.
}
692.
693.
function getTableFieldLinkList( $field ) {
694.
695.
return $this->colmeta[$field]['listlink'];
696.
}
697.
698.
/*
699.
Get labels for select table header.
700.
701.
Parameters : None.
702.
return : String.
703.
*/
704.
705.
function getTableHeaderSelect() {
706.
707.
$labelsStr ='';
708.
709.
foreach ( $this->colmeta as $k => $v) {
710.
if ( $v['selectlen'] > 0 )
711.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['selectlib'] .'</th>'."\n";
712.
}
713.
return $labelsStr;
714.
}
715.
716.
function getTableFieldLenSelect( $field ) {
717.
718.
return $this->colmeta[$field]['selectlen'];
719.
}
720.
721.
function getTableFieldLinkSelect( $field ) {
722.
723.
return $this->colmeta[$field]['selectlink'];
724.
}
725.
726.
727.
/*
728.
Check user input values for depot.
729.
730.
Parameters : array of fields with regexp paterns. ex: array( $nom =>'^(?!\s*$).+' , $icone => '')
731.
return : False if at last one regexp does not match.
732.
*/
733.
function checkValue( $arrayCheck ) {
734.
735.
$indexfield=1;
736.
foreach ( $arrayCheck as $value => $pattern) {
737.
738.
if ($pattern=='') continue;
739.
740.
$pattern='/'.$pattern.'/';
741.
742.
if (!preg_match($pattern, $value)) {
743.
$this->lasterror = "Erreur champ #".$indexfield. '. Valeur:['.$value.']';
744.
return false;
745.
}
746.
747.
$indexfield+=1;
748.
749.
}
750.
751.
return true;
752.
}
753.
754.
} // end class
755.
756.
?>
757.