Newer
Older
<?php
/*
* Copyright 2008,2009 Maarch
*
* This file is part of Maarch Framework.
*
* Maarch Framework is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* Maarch Framework is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with Maarch Framework. If not, see <http://www.gnu.org/licenses/>.
*/
/**
* @brief Contains all the function to build a SQL query
*
* @file
* @author Loïc Vinet <dev@maarch.org>
* @author Claire Figueras <dev@maarch.org>
* @date $date$
* @version $Revision$
* @ingroup core
*/
/**
* @brief Contains all the function to build a SQL query (select, insert and update)
*
* @ingroup core
*/
class request extends dbquery
{
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/**
* Constructs the select query and returns the results in an array
*
* @param $select array Query fields
* @param $where string Where clause of the query
* @param $parameters array An indexed or associative array of parameters
* @param $other string Query complement (order by, ...)
* @param $database_type string Type of the database
* @param $limit string Maximum numbers of results (500 by default)
* @param $left_join boolean Is the request is a left join ? (false by default)
* @param $first_join_table string Name of the first join table (empty by default)
* @param $second_join_table string Name of the second join table (empty by default)
* @param $join_key string Key of the join (empty by default)
* @param $add_security string Add the user security where clause or not (true by default)
* @param $distinct_argument Add the distinct parameters in the sql query (false by default)
* @return array Results of the built query
*/
public function PDOselect($select, $where, $parameters = null, $other, $database_type, $limit="default", $left_join=false, $first_join_table="", $second_join_table="", $join_key="", $add_security = true, $catch_error = false, $distinct_argument = false)
{
$db = new Database();
if($limit == 0 || $limit == "default")
{
$limit = $_SESSION['config']['databasesearchlimit'];
}
//Extracts data in the first argument : $select.
$tab_field = array();
$table = '';
$table_string = '';
$field_string = '';
foreach (array_keys($select) as $value)
{
$table = $value;
$table_string .= $table.",";
foreach ($select[$value] as $subvalue)
{
$field = $subvalue;
$field_string .= $table.".".$field.",";
//fix order by alt_identifier
if($field == 'alt_identifier'){
$field_string .= "order_alphanum(alt_identifier),";
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
}
//Query fields and table names have been wrote in 2 strings
}
//Strings need to be cleaned
$table_string = substr($table_string, 0, -1);
$field_string = substr($field_string, 0, -1);
//Extracts data from the second argument : the where clause
if (trim($where) <> "")
{
$where_string = $where;
//$where_string = " where ".$where;
}
else
{
$where_string = "";
}
$join = '';
if($left_join)
{
//Reste table string
$table_string = "";
//Add more table in join syntax
foreach (array_keys($select) as $value)
{
if ($value <> $first_join_table && $value <> $second_join_table)
{
$table_string = $value.",";
}
}
$join = " left join ";
$table_string .= $first_join_table;
$join .= $second_join_table." on ".$second_join_table.".".$join_key." = ".$first_join_table.".".$join_key;
}
if($add_security)
{
foreach(array_keys($_SESSION['user']['security']) as $coll)
{
if(isset($_SESSION['user']['security'][$coll]['DOC']['table']))
{
if(preg_match('/'.$_SESSION['user']['security'][$coll]['DOC']['table'].'/',$table_string) || preg_match('/'.$_SESSION['user']['security'][$coll]['DOC']['view'].'/',$table_string) )
{
if(empty($where_string))
{
$where_string = "( ".$_SESSION['user']['security'][$coll]['DOC']['where']." ) ";
//$where_string = " where ( ".$_SESSION['user']['security'][$coll]['DOC']['where']." ) ";
}
else
{
$where_string = ''.$where_string." and ( ".$_SESSION['user']['security'][$coll]['DOC']['where']." ) ";
}
break;
}
}
}
}
//Time to create the SQL Query
$query = "";
$dist = '';
if($distinct_argument == true)
{
$dist = " distinct ";
}
$query = $db->limit_select(0, $limit, $field_string, $table_string." ".$join, $where_string, $other, $dist);
if (preg_match('/_view/i', $query)) {
$_SESSION['last_select_query'] = $query;
$_SESSION['last_select_query_parameters'] = $parameters;
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
}
$res_query = $db->query($query, $parameters, $catch_error);
if($catch_error && !$res_query)
{
return false;
}
$result=array();
while($line = $res_query->fetch(PDO::FETCH_ASSOC))
{
$temp= array();
foreach (array_keys($line) as $resval)
{
if (!is_int($resval))
{
array_push(
$temp,
array(
'column'=>$resval,
'value'=>functions::xssafe($line[$resval]),
)
);
}
}
array_push($result,$temp);
}
if(count($result) == 0 && $catch_error)
{
return true;
}
return $result;
}
/**
* Builds the insert query and sends it to the database
*
* @param string $table table to insert
* @param array $data data to insert
* @param array $database_type type of the database
* @return bool True if the query was sent ok and processed by the database without error, False otherwise
*/
public function insert($table, $data, $database_type)
{
$parameters = array();
for ($i=0;$i<count($data);$i++) {
if(
trim(strtoupper($data[$i]['value'])) == "SYSDATE"
|| trim(strtoupper($data[$i]['value'])) == "CURRENT_TIMESTAMP"
) {
$value_string .= $data[$i]['value'] . ',';
} else {
$value_string .= "?,";
$parameters[] = $data[$i]['value'];
}
$value_string = substr($value_string, 0, -1);
$field_string = substr($field_string, 0, -1);
$value_string .= ")";
$field_string .= ")";
//Time to create the SQL Query
$query = "INSERT INTO " . $table . " " . $field_string . " VALUES " . $value_string;
/*echo $query . PHP_EOL;
var_dump($parameters);exit;*/
$stmt = $db->query($query, $parameters);
/**
* Constructs the update query and sends it to the database with PDO
*
* @param $table string Table to update
* @param $data array Data to update
* @param $where string Where clause of the query
* @param $parameters array An indexed or associative array of parameters
* @param $databasetype array Type of the database
*/
public function PDOupdate($table, $data, $where, $parametersInit = null, $databasetype)
{
$db = new Database();
$update_string = "";
for ($i=0; $i < count($data);$i++) {
if ($data[$i]['type'] == "string" || $data[$i]['type'] == "date") {
if ($databasetype == "POSTGRESQL" && $data[$i]['type'] == "date"
&& ($data[$i]['value'] == '' || $data[$i]['value'] == ' ')) {
$update_string .= $data[$i]['column']."=NULL,";
} else {
if (trim(strtoupper($data[$i]['value'])) == "SYSDATE") {
$update_string .= $data[$i]['column']."=sysdate,";
} elseif(trim(strtoupper($data[$i]['value'])) == "CURRENT_TIMESTAMP") {
$update_string .= $data[$i]['column']."=CURRENT_TIMESTAMP,";
$update_string .= $data[$i]['column']."=?,";
$parameters[] = $data[$i]['value'];
if ($data[$i]['value'] == 'NULL') {
$update_string .= $data[$i]['column']."=NULL,";
} else {
$update_string .= $data[$i]['column']."=?,";
$parameters[] = $data[$i]['value'];
}
}
}
$update_string = substr($update_string, 0, -1);
if (is_array($parametersInit)) {
for ($cpt=0;$cpt<count($parametersInit);$cpt++) {
$parameters[] = $parametersInit[$cpt];
}
}
//Time to create the SQL Query
$query = "";
$query = "UPDATE " . $table . " SET " . $update_string . $where_string;
/*echo $query . '<br/>';
echo '<pre>';
var_dump($parameters);
echo '</pre>';*/
$stmt = $db->query($query, $parameters);
return $stmt;
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
/*************************************************************************
* Returns instruction to get date or part of the date
*
* Parameters
* (string) date string
* (string) date part name {year | month | day | hour | minute | second}
*
* Return
* (string) date instruction
*
*************************************************************************/
public function extract_date($date_field, $arg = '')
{
switch ($_SESSION['config']['databasetype'])
{
case "SQLSERVER":
return '';
case "MYSQL":
switch($arg)
{
case 'year' : return ' date_format('.$date_field.', %Y)';
case 'month' : return ' date_format('.$date_field.', %m)';
case 'day' : return ' date_format('.$date_field.', %d)';
case 'hour' : return ' date_format('.$date_field.', %k)';
case 'minute' : return ' date_format('.$date_field.', %i)';
case 'second' : return ' date_format('.$date_field.', %s)';
default : return ' date('.$date_field.')';
}
case "POSTGRESQL":
switch($arg)
{
case 'year' : return " date_part( 'year', ".$date_field.")";
case 'month' : return " date_part( 'month', ".$date_field.")";
case 'day' : return " date_part( 'day', ".$date_field.")";
case 'hour' : return " date_part( 'hour', ".$date_field.")";
case 'minute' : return " date_part( 'minute', ".$date_field.")";
case 'second' : return " date_part( 'second', ".$date_field.")";
default : return ' date('.$date_field.')';
}
case "ORACLE":
switch($arg)
{
case 'year' : return " to_char(".$date_field.", 'YYYY')";
case 'month' : return " to_char(".$date_field.", 'MM')";
case 'day' : return " to_char(".$date_field.", 'DD')";
case 'hour' : return " to_char(".$date_field.", 'HH24')";
case 'minute' : return " to_char(".$date_field.", 'MI')";
case 'second' : return " to_char(".$date_field.", 'SS')";
//default : return " to_char(".$date_field.", 'DD/MM/YYYY')";
default : return $date_field;
}
}
}