projects / busui.git / blob
search:
summary | shortlog | log | commit | commitdiff | tree
plain | HEAD | history | blame
Refactoring of route list page
[busui.git] / include / db / route-dao.inc.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

<?php
 
function getRoute($routeID) {
        $query = "Select * from routes where route_id = '$routeID' LIMIT 1";
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_assoc($result);   
}
function getRoutes() {
        global $conn;
        $query = "Select * from routes order by route_short_name;";
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_all($result);    
}
 
function getRoutesByNumber($routeNumber = "") {
        global $conn;
        if ($routeNumber != "") {
        $query = "Select distinct routes.route_id,routes.route_short_name,routes.route_long_name,service_id from routes  join trips on trips.route_id =
routes.route_id join stop_times on stop_times.trip_id = trips.trip_id where route_short_name = '$routeNumber' order by route_short_name;";
        } else {
            $query = "SELECT DISTINCT route_short_name from routes order by route_short_name";
        }
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_all($result);    
}
 
function getRouteNextTrip($routeID) {
    $query = "select * from routes join trips on trips.route_id = routes.route_id
join stop_times on stop_times.trip_id = trips.trip_id where
arrival_time > CURRENT_TIME and routes.route_id = '$routeID' order by
arrival_time limit 1";
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_assoc($result);       
  }
 
function getRoutesByDestination($destination = "", $service_period = "") {
    global $conn;
         if ($service_period == "") $service_period = service_period();
         if ($destination != "")  {
             $query = "SELECT DISTINCT trips.route_id,route_short_name,route_long_name, service_id
FROM stop_times join trips on trips.trip_id =
stop_times.trip_id join routes on trips.route_id = routes.route_id
WHERE route_long_name = '$destination' AND  service_id='$service_period' order by route_short_name";
         } else {
        $query = "SELECT DISTINCT route_long_name
FROM stop_times join trips on trips.trip_id =
stop_times.trip_id join routes on trips.route_id = routes.route_id
WHERE service_id='$service_period' order by route_long_name";
    }
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_all($result);
}
 
function getRoutesBySuburb($suburb, $service_period = "") {
         if ($service_period == "") $service_period = service_period();
    global $conn;
        $query = "SELECT DISTINCT service_id,trips.route_id,route_short_name,route_long_name
FROM stop_times join trips on trips.trip_id = stop_times.trip_id
join routes on trips.route_id = routes.route_id
join stops on stops.stop_id = stop_times.stop_id
WHERE zone_id LIKE '%$suburb;%' AND service_id='$service_period' ORDER BY route_short_name";
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_all($result);
}
 
function getRoutesNearby($lat, $lng, $limit = "", $distance = 500) {
 
        
                 if ($service_period == "") $service_period = service_period();
                  if ($limit != "") $limit = " LIMIT $limit "; 
    global $conn;
        $query = "SELECT service_id,trips.route_id,route_short_name,route_long_name,
        min(ST_Distance(position, ST_GeographyFromText('SRID=4326;POINT($lng $lat)'), FALSE)) as distance
FROM stop_times
join trips on trips.trip_id = stop_times.trip_id
join routes on trips.route_id = routes.route_id
join stops on stops.stop_id = stop_times.stop_id
WHERE service_id='$service_period'
AND ST_DWithin(position, ST_GeographyFromText('SRID=4326;POINT($lng $lat)'), $distance, FALSE)
        group by service_id,trips.route_id,route_short_name,route_long_name
        order by distance $limit";
        debug($query,"database");
        $result = pg_query($conn, $query);
        if (!$result) {
                databaseError(pg_result_error($result));
                return Array();
        }
        return pg_fetch_all($result);
}
?>
Unnamed repository; edit this file to name it for gitweb.
GitPHP by Chris Han