/
get_simpledata.php
122 lines (117 loc) · 3.41 KB
/
get_simpledata.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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
79
80
81
82
83
84
85
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
<?php
include './config.php';
function doLog($log){
if (!file_exists(LOG) and !is_dir(LOG)) {
mkdir(LOG);
}
$file = LOG.'/get_simpledata.log';
// Write the contents back to the file
//echo "do loging to $file";
file_put_contents($file, "$log\n", FILE_APPEND | LOCK_EX);
}
if( isset($_POST['data'])){
$data = $_POST['data'];
$name = $data['table']; // get the name
$sfields = $data['selfields']; // get fields to select
$sum = $data['sum'];
$where = $data['where']; //
// [
// "column" = "name"
// "condition" = "="
// "value" = "pierre"
// ]
$sidx = $data['sidx']; // get fields order
$sord = $data['sord']; // get fields order
} else {
die("Error");
}
doLog("Parameters received : \n".
"\ttable = ".$name."\n".
"\tFields = ".$sfields."\n".
"\tHost = ".DB_HOST."\n".
"\tPort = ".DB_PORT."\n".
"\tDBName = ".DB_NAME."\n".
"\tUser = ".DB_USER."\n".
"\tPwd = ".DB_PASSWORD);
$hfield = substr(strtolower($name),0,-1); //header for fields
// connect to pgsql
$connection = pg_Connect(DSN);
if ($connection) {
$response->status = 1;
$response->responseText = "Unable to process the data.";
pg_query($connection,"set names 'utf8'");
$fields = explode(",",$sfields);
$farray = array();
foreach ($fields as $key => $val) {
if (strpos($val,'_') === false) { // the table header is not set
$farray[$val] = $hfield."_".$val;
doLog("Reformated keys : $val => ".$hfield."_".$val);
} else {
$farray[$val] = $val;
doLog("Reformated keys : $val => $val");
}
}
doLog("Field Array : ".print_r($farray,true));
if (!$sum) {
$query = "select ".join($farray,',')." from ".DB_SCHEMA.".$name";
} else {
if (strpos($sum,'_') === false) { // header not set
$query = "select count(".$hfield."_".$sum.") as count from ".DB_SCHEMA.".$name ";
} else {
$query = "select count(".$sum.") as count from ".DB_SCHEMA.".$name where";
}
}
if ($where){
$query .= " WHERE ";
foreach ($where as $cond) {
$query .= $farray[$cond['column']]." ";
$query .= $cond['condition']." ";
$query .= "'".pg_escape_string($cond['value'])."'";
$query .= " AND ";
}
//$query = substr($query,0, -5);
//$query .= " AND ";
$query .= " deleted = 0";
doLog("Query after where : ".$query);
}
if ($sidx && $sord){
$query .= " order by ".$hfield."_".$sidx." $sord";
}
doLog("Executing :".$query);
$result = pg_query($connection,$query);
if ($result) {
$response->status = 0;
$response->responseText = "Query execution Successful.";
doLog("Query successfully executed.");
$response->d = array();
while($row = pg_fetch_assoc($result)) {
doLog("Row Array : ".print_r($row,true));
$a = array();
if ($sum){
$a[] = $row["count"];
} else {
foreach ($farray as $key => $val) {
//remove brackets for the current field if there where
$tmp = preg_replace('/\(.*/', '', $val);
doLog("Storing ". $tmp);
$a[] = $row[$tmp];
}
}
$response->d[] = $a;
}
doLog("Data parsed successfully.");
} else {
$response->status = 1;
$response->responseText = "Cannot execute query, Error ".pg_last_error($connection);
doLog("Failed to execute the query.");
}
} else {
$response->status = 1;
$response->responseText = "Connection Failed, Error ".pg_last_error($connection);
doLog("Connection Failed.");
}
doLog("Preparing the reponse");
header("Content-type: application/json;charset=utf-8");
doLog(json_encode($response));
echo json_encode($response);
?>