dev-chiefworks
297 lines
10 KiB
PHP
297 lines
10 KiB
PHP
<?php
|
|
|
|
class Booking {
|
|
|
|
const STATUS_ERROR = -1;
|
|
const STATUS_INIT = 1;
|
|
const STATUS_BOOKED = 2;
|
|
const STATUS_CANCELED = 3;
|
|
const STATUS_VENDOR_CANCELED = 4;
|
|
const STATUS_VEHICLE_ARRIVED = 5;
|
|
const STATUS_COMPLETED = 6;
|
|
const STATUS_IN_PROGRESS = 7;
|
|
const STATUS_DISPATCHED = 8;
|
|
const STATUS_LOCATION_UPDATE = 9;
|
|
|
|
/*
|
|
CREATE TABLE booking (
|
|
id serial not null constraint booking_pkey primary key,
|
|
quote_id bigint not null,
|
|
provider_booking_ref varchar(200) not null,
|
|
details json,
|
|
created timestamp default now(),
|
|
updated timestamp,
|
|
completed timestamp,
|
|
status smallint default 0,
|
|
message text,
|
|
cost numeric default 0
|
|
);
|
|
|
|
alter table booking
|
|
add constraint booking_quotes_id_fk
|
|
foreign key (quote_id) references quotes;
|
|
|
|
|
|
CREATE TABLE booking_details (
|
|
id serial not null constraint booking_details_pkey primary key,
|
|
booking_id bigint not null,
|
|
action varchar(64),
|
|
details json,
|
|
created timestamp default now(),
|
|
message text,
|
|
request json
|
|
);
|
|
|
|
alter table booking_details
|
|
add constraint booking_details_booking_id_fk
|
|
foreign key (booking_id) references booking;*/
|
|
|
|
public static function getById($db, $id) {
|
|
syslog( LOG_WARNING, "Booking::getById(\$db, $id)" );
|
|
Logger::debug( "Booking::getById(\$db, $id)" );
|
|
$result = [];
|
|
// $q = "SELECT * FROM booking WHERE id=${id}";
|
|
$q = "SELECT b.*,
|
|
a_s.address as location_start, a_s.latitude as location_start_lat, a_s.longitude as location_start_lng,
|
|
a_e.address as location_end, a_e.latitude as location_end_lat, a_e.longitude as location_end_lng,
|
|
a_s.country as location_country
|
|
FROM booking b
|
|
LEFT JOIN quotes q on b.quote_id = q.id
|
|
LEFT JOIN address a_s on q.location_start_id = a_s.id
|
|
LEFT JOIN address a_e on q.location_end_id = a_e.id
|
|
WHERE b.id=${id}";
|
|
$r = pg_query( $db, $q );
|
|
//syslog(LOG_WARNING,$q);
|
|
if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getAll($db, $params = []) {
|
|
syslog( LOG_WARNING, "Booking::getAll(\$db, $params)" );
|
|
Logger::debug( "Booking::getAll(\$db, $params)" );
|
|
$result = [];
|
|
// $q = "SELECT * FROM booking ";
|
|
$q = "SELECT b.*,
|
|
a.address as location_end, a.latitude as location_end_lat, a.longitude as location_end_lng,
|
|
a.country as location_country
|
|
FROM booking b
|
|
LEFT JOIN quotes q on b.quote_id = q.id
|
|
LEFT JOIN address a on q.location_end_id = a.id ";
|
|
if (count($params)) {
|
|
if (array_key_exists('active', $params) && (bool)$params['active'] == true) {
|
|
$q .= " WHERE b.status = ". Booking::STATUS_BOOKED. " OR b.status = ". Booking::STATUS_IN_PROGRESS. " OR b.status = ". Booking::STATUS_DISPATCHED ;
|
|
} else if (array_key_exists('status', $params) && isset($params['status'])) {
|
|
//$statuses = explode(',', $params['status']);
|
|
$q .= " WHERE b.status IN(". $params['status'] .")";
|
|
}
|
|
if (array_key_exists('limit', $params) && array_key_exists('offset', $params)) {
|
|
$limit = $params['limit'];
|
|
$offset = $params['offset'];
|
|
$q .= " ORDER BY created DESC LIMIT ${limit} OFFSET ${offset} ";
|
|
} else {
|
|
$q .= " ORDER BY created DESC LIMIT 10 OFFSET 0";
|
|
}
|
|
}
|
|
$r = pg_query( $db, $q );
|
|
$result = [];
|
|
if ( $r && pg_num_rows( $r ) ) {
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
array_push($result, $row);
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getByBookingRef($db, $bookingRef) {
|
|
syslog( LOG_WARNING, "Booking::getByBookingRef(\$db, $bookingRef)" );
|
|
Logger::debug( "Booking::getById(\$db, $bookingRef)" );
|
|
$result = [];
|
|
$q = "SELECT * FROM booking WHERE provider_booking_ref='${bookingRef}'";
|
|
$r = pg_query( $db, $q );
|
|
//syslog(LOG_WARNING,$q);
|
|
if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
public static function getByMemberId($db, $id, $params = []) {
|
|
syslog( LOG_WARNING, "Booking::getByMemberId(\$db, $id)" );
|
|
Logger::debug( "Booking::getByMemberId(\$db, $id)" );
|
|
$result = [];
|
|
// $q = "SELECT * FROM booking WHERE member_id=${id} ";
|
|
$q = "SELECT b.*,
|
|
a.address as location_end, a.latitude as location_end_lat, a.longitude as location_end_lng,
|
|
a.country as location_country
|
|
FROM booking b
|
|
LEFT JOIN quotes q on b.quote_id = q.id
|
|
LEFT JOIN address a on q.location_end_id = a.id
|
|
WHERE b.member_id=${id}";
|
|
|
|
if (count($params)) {
|
|
if (array_key_exists('active', $params) && (bool)$params['active'] == true) {
|
|
$q .= " AND b.status = ". Booking::STATUS_BOOKED. " OR b.status = ". Booking::STATUS_IN_PROGRESS. " OR b.status = ". Booking::STATUS_DISPATCHED ;
|
|
} else if (array_key_exists('status', $params) && isset($params['status'])) {
|
|
//$statuses = explode(',', $params['status']);
|
|
$q .= " AND b.status IN(". $params['status'] .")";
|
|
}
|
|
|
|
if (array_key_exists('limit', $params) && array_key_exists('offset', $params)) {
|
|
$limit = $params['limit'];
|
|
$offset = $params['offset'];
|
|
$q .= " ORDER BY created DESC LIMIT ${limit} OFFSET ${offset} ";
|
|
} else {
|
|
$q .= " ORDER BY created DESC LIMIT 10 OFFSET 0";
|
|
}
|
|
}
|
|
|
|
$r = pg_query( $db, $q );
|
|
$result = [];
|
|
if ( $r && pg_num_rows( $r ) ) {
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
array_push($result, $row);
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getLastNotCompletedBookingForMember($db, $member_id) {
|
|
syslog( LOG_WARNING, "Booking::getLastNotCompletedBookingForMember(\$db, $member_id)" );
|
|
Logger::debug( "Booking::getLastNotCompletedBookingForMember(\$db, $member_id)" );
|
|
$result = [];
|
|
$q = "SELECT * FROM booking WHERE member_id=${member_id} AND completed IS NULL AND status = ". Booking::STATUS_BOOKED ." AND cost > 0";
|
|
$r = pg_query( $db, $q );
|
|
//syslog(LOG_WARNING,$q);
|
|
if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function create($db, $data) {
|
|
syslog(LOG_WARNING,'Booking::create($db, $data)');
|
|
|
|
$quote_id = $data["quote_id"];
|
|
$provider_booking_ref = $data["provider_booking_ref"];
|
|
$details = $data["details"];
|
|
$status = (int)$data["status"];
|
|
$memberId = (int)$data["member_id"];
|
|
$cost = (int)$data["cost"];
|
|
$q = "INSERT INTO booking(quote_id, provider_booking_ref, details, status, member_id, cost) VALUES (${quote_id}, '${provider_booking_ref}', '${details}', ${status}, ${memberId}, ${cost}) RETURNING id";
|
|
$log = [
|
|
'message' => 'Insert Booking',
|
|
'data' =>$data,
|
|
'query' =>$q
|
|
];
|
|
Logger::debug($log);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [self::getById($db, $f["id"]),NULL];
|
|
}
|
|
syslog(LOG_WARNING,pg_last_error($db));
|
|
return [NULL,pg_last_error($db)];
|
|
}
|
|
|
|
public static function update($db, $data) {
|
|
syslog(LOG_WARNING,'Booking::update(): '.json_encode($data));
|
|
$q = self::buildUpdateSQL($data);
|
|
syslog(LOG_WARNING,$q);
|
|
$r = pg_query($db, $q);
|
|
$log = [
|
|
'affected_row' => pg_affected_rows($r),
|
|
'message' => 'Update Booking',
|
|
'data' =>$data,
|
|
'query' =>$q
|
|
];
|
|
Logger::debug($log);
|
|
if ($r && pg_affected_rows($r)) {
|
|
return array(self::getById($db, $data["id"]),NULL);
|
|
}
|
|
return array(NULL, pg_last_error($db));
|
|
}
|
|
|
|
public static function buildUpdateSQL($data) {
|
|
$q = "UPDATE booking SET";
|
|
foreach ($data as $key => $value) {
|
|
if ($key != 'id') {
|
|
if (is_int($value) || "now()" == $value) {
|
|
$q .= " ${key}=${value},";
|
|
} else {
|
|
$q .= " ${key}='".pg_escape_string($value)."',";
|
|
}
|
|
}
|
|
}
|
|
$q = substr($q, 0, -1);
|
|
$q .= " WHERE id=".$data["id"];
|
|
return $q;
|
|
}
|
|
|
|
public static function createDetails($db, $data) {
|
|
syslog(LOG_WARNING,'Booking::createDetails($db, $data)');
|
|
|
|
$booking_id = $data['booking_id'];
|
|
$action = $data['action'];
|
|
$details = $data['details'];
|
|
$message = $data['message'];
|
|
$request = $data['request'];
|
|
|
|
$q = "INSERT INTO booking_details(booking_id, action, details, message, request) VALUES (${booking_id}, '${action}', '${details}', '${message}', '${request}') RETURNING id";
|
|
$log = [
|
|
'message' => 'Insert Booking Details',
|
|
'data' =>$data,
|
|
'query' =>$q
|
|
];
|
|
Logger::debug($log);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [self::getDetailsById($db, $f["id"]),NULL];
|
|
}
|
|
syslog(LOG_WARNING,pg_last_error($db));
|
|
return [NULL,pg_last_error($db)];
|
|
}
|
|
|
|
public static function getDetailsById($db, $id) {
|
|
syslog( LOG_WARNING, "Booking::getDetailsById(\$db, $id)" );
|
|
Logger::debug( "Booking::getDetailsById(\$db, $id)" );
|
|
$result = [];
|
|
$q = "SELECT id, booking_id, action, details, message FROM booking_details WHERE id=${id}";
|
|
$r = pg_query( $db, $q );
|
|
if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getDetailsByBookingId($db, $booking_id) {
|
|
syslog( LOG_WARNING, "Booking::getDetailsByBookingId(\$db, $booking_id)" );
|
|
Logger::debug( "Booking::getDetailsByBookingId(\$db, $booking_id)" );
|
|
$result = [];
|
|
$q = "SELECT id, booking_id, action, details, message FROM booking_details WHERE booking_id=${booking_id} ORDER BY id";
|
|
$r = pg_query( $db, $q );
|
|
$result = [];
|
|
if ( $r && pg_num_rows( $r ) ) {
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
array_push($result, $row);
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getMemberBySessionId($db, $sessionId) {
|
|
syslog( LOG_WARNING, "Booking::getMemberBySessionId(\$db, $sessionId)" );
|
|
Logger::debug( "Booking::getMemberBySessionId(\$db, $sessionId)" );
|
|
$result = [];
|
|
$q = "SELECT * FROM public.members_session WHERE session = ${sessionId} LIMIT 1 OFFSET 0";
|
|
$r = pg_query( $db, $q );
|
|
if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r )) {
|
|
return [$f, NULL];
|
|
}
|
|
syslog(LOG_WARNING,pg_last_error($db));
|
|
return [NULL,pg_last_error($db)];
|
|
}
|
|
|
|
|
|
} |