forked from perryyan/ubcair
/
oci_functions.php
151 lines (129 loc) · 5.33 KB
/
oci_functions.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
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
<?php
$db_conn = OCILogon("ora_c2e8", "a42375105", "ug");
// Changing the format of Oracle's timestamp data for more friendly look,
// mode 1 for timestamp, mode 2 for intervals (result of algebraic operations on timestamps)
function parseDate($value, $mode) {
if ($mode == 1) return substr($value, 0, 17);
if ($mode == 2) return substr($value, 10, 9);
if ($mode == 3) return substr($date, 0,10) . substr($date, 11);
}
// Coordinate printing of detailed information regarding each flight on the search result when clicked
// JSenable: 1 to use the HTML/JS toggle details, 0 to turn off (show as plain text)
function printDetails($route, $it, $JSenable) {
if($JSenable) {
echo "<a href='#' class='toggler' detail-num='$it'>Details</a>"
."<a class='detail$it' style='display:none'>";
}
if (array_key_exists('FIRSTID', $route)) {
$firstid = $route['FIRSTID'];
printDetailsHelper($firstid);
}
if (array_key_exists('SECONDID',$route)) {
$secondid = $route['SECONDID'];
printLayOver($firstid, $secondid);
printDetailsHelper($secondid);
}
if (array_key_exists('THIRDID', $route)) {
$thirdid = $route['THIRDID'];
printLayOver($secondid, $thirdid);
printDetailsHelper($route['THIRDID']);
}
if($JSenable) {
echo "</a>";
}
}
// Actually printing detailed information regarding each flight in search result
function printDetailsHelper($flightid) {
$flight = oci_fetch_array(executePlainSQL("select departap,arrivalap,departtime,arrivaltime,cost,arrivaltime-departtime as ftime from Flight"
." where fid='$flightid'"),OCI_ASSOC);
$departapcode = $flight['DEPARTAP'];
$departap = oci_fetch_array(executePlainSQL("select * from Airport"
." where code='$departapcode'"));
$departapname = $departap['APNAME'];
$departapcity = $departap['CITY'];
$departapcountry = $departap['COUNTRY'];
$departtime = parseDate($flight['DEPARTTIME'], 1);
$arrivalapcode = $flight['ARRIVALAP'];
$arrivalap = oci_fetch_array(executePlainSQL("select * from Airport"
." where code='$arrivalapcode'"));
$arrivalapname = $arrivalap['APNAME'];
$arrivalapcity = $arrivalap['CITY'];
$arrivalapcountry = $arrivalap['COUNTRY'];
$arrivaltime = parseDate($flight['ARRIVALTIME'], 1);
$fduration = parseDate($flight['FTIME'],2);
echo "<br>Depart from $departapname ($departapcode at $departapcity, $departapcountry) on $departtime GMT"
."<br>Flight Duration: $fduration"
."<br>Arrive at $arrivalapname ($arrivalapcode at $arrivalapcity, $arrivalapcountry) on $arrivaltime GMT";
}
// Another helper for printDetails, printing wait time between transfer
function printLayOver($firstid, $secondid) {
$layover = oci_fetch_row(executePlainSQL("select F2.departtime-F1.arrivaltime from Flight F1, Flight F2
where F1.fid='$firstid' AND F2.fid='$secondid'"));
$layovertime = parseDate($layover[0],2);
echo "<br>Lay over for $layovertime";
}
function executePlainSQL($cmdstr) { //takes a plain (no bound variables) SQL command and executes it
//echo "<br>running ".$cmdstr."<br>";
global $db_conn, $success;
$statement = OCIParse($db_conn, $cmdstr); //There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
if (!$statement) {
echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
$e = OCI_Error($db_conn); // For OCIParse errors pass the
// connection handle
echo htmlentities($e['message']);
$success = False;
}
$r = OCIExecute($statement, OCI_DEFAULT);
if (!$r) {
echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
$e = oci_error($statement); // For OCIExecute errors pass the statementhandle
echo htmlentities($e['message']);
$success = False;
} else {
}
return $statement;
}
function executeBoundSQL($cmdstr, $list) {
/* Sometimes a same statement will be excuted for severl times, only
the value of variables need to be changed.
In this case you don't need to create the statement several times;
using bind variables can make the statement be shared and just
parsed once. This is also very useful in protecting against SQL injection. See example code below for how this functions is used */
global $db_conn, $success;
$statement = OCIParse($db_conn, $cmdstr);
if (!$statement) {
echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
$e = OCI_Error($db_conn);
echo htmlentities($e['message']);
$success = False;
}
foreach ($list as $tuple) {
foreach ($tuple as $bind => $val) {
//echo $val;
//echo "<br>".$bind."<br>";
OCIBindByName($statement, $bind, $val);
unset ($val); //make sure you do not remove this. Otherwise $val will remain in an array object wrapper which will not be recognized by Oracle as a proper datatype
}
$r = OCIExecute($statement, OCI_DEFAULT);
if (!$r) {
echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
$e = OCI_Error($statement); // For OCIExecute errors pass the statementhandle
echo htmlentities($e['message']);
echo "<br>";
$success = False;
}
}
}
// This function counts the number of rows (tuples) given a raw query (before parse and execution)
function countRows($db_conn, $query) {
$numrows = 0;
$stmt = oci_parse($db_conn, $query);
$r = oci_execute($stmt, OCI_DEFAULT);
if($r) {
oci_fetch_all($stmt, $result);
$numrows = oci_num_rows($stmt);
oci_free_statement($stmt);
return $numrows;
}
}
?>