dev-chiefworks
101 lines
4.1 KiB
PHP
101 lines
4.1 KiB
PHP
<?php
|
|
|
|
class QuoteGroup {
|
|
|
|
const BASE_SQL_DATA = "SELECT a.*,ls.address AS location_start,ls.latitude AS location_start_lat,ls.longitude AS location_start_lng,ls.timezone AS location_start_tz,ls.geocoding_date AS location_geocoding_date,le.address AS location_end,le.latitude AS location_end_lat,le.longitude AS location_end_lng ";
|
|
const BASE_SQL_FROM = " FROM quote_group a, address ls, address le WHERE ls.id=a.location_start_id AND le.id=a.location_end_id";
|
|
|
|
public function getById($db, $id) {
|
|
$q = self::BASE_SQL_DATA . self::BASE_SQL_FROM . " AND a.id=${id} ";
|
|
$r = pg_query($db, $q);
|
|
error_log($q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
return $f;
|
|
}
|
|
error_log("ERROR? ".pg_last_error($db));
|
|
return [];
|
|
}
|
|
|
|
public function getByOriginDestinationId($db, $origin, $destination, $tpid, $limit, $offset) {
|
|
$results = [];
|
|
$total = 0;
|
|
$db_origin = pg_escape_string(strtolower($origin));
|
|
$db_destination = pg_escape_string(strtolower($destination));
|
|
$q = "SELECT count(*) ".self::BASE_SQL_FROM." AND lower(ls.address)='${db_origin}'
|
|
AND lower(le.address)='${db_destination}'";
|
|
if ($tpid>0) {
|
|
$q.= " AND a.transport_provider_id=${tpid}";
|
|
}
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
$total = $f[0];
|
|
}
|
|
if ($total>0 && $offset<$total) {
|
|
$q = self::BASE_SQL_DATA . self::BASE_SQL_FROM . " AND lower(ls.address)='${db_origin}'";
|
|
$q.= " AND lower(le.address)='${db_destination}' ".($tpid>0?" AND a.transport_provider_id=${tpid}":"");
|
|
$q.= " ORDER BY a.created DESC LIMIT ${limit} OFFSET ${offset}";
|
|
error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$results[] = $f;
|
|
}
|
|
}
|
|
}
|
|
return array($total, $results);
|
|
}
|
|
|
|
public function create($db, $data) {
|
|
syslog(LOG_WARNING,'QuoteGroup::create()');
|
|
$location_start_id = Address::getAddress($db, $data, "location_start");
|
|
$location_end_id = Address::getAddress($db, $data, "location_end");
|
|
if ($location_start_id==NULL || $location_end_id==NULL) {
|
|
syslog(LOG_WARNING,json_encode($data));
|
|
return array(NULL,"Failed to save address");
|
|
}
|
|
$transport_providers = count($data["transport_providers"]);
|
|
$q = "INSERT INTO quote_group (location_start_id,location_end_id,transport_providers,processed)
|
|
VALUES(${location_start_id},${location_end_id},${transport_providers},0
|
|
) RETURNING id";
|
|
syslog(LOG_WARNING,$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return array(self::getById($db, $f["id"]),NULL);
|
|
}
|
|
return array(NULL,pg_last_error($db));
|
|
}
|
|
|
|
public function update($db, $data) {
|
|
error_log('QuoteGroup::update()');
|
|
// transport_providers='".$data["transport_providers"]."',
|
|
// created='".$data["created"]."',
|
|
$q = "UPDATE quote_group SET cost=".($data["cost"]==""?"NULL":((float)$data["cost"])).",";
|
|
$q.= " transport_provider_id=".($data["transport_provider_id"]==""?"NULL":((int)$data["transport_provider_id"])).",";
|
|
$q.= " quote_id=".($data["quote_id"]==""?"NULL":((int)$data["quote_id"])).",";
|
|
$q.= " completed=".($data["completed"]==""?"NULL":("'".$data["completed"]."'")).",";
|
|
$q.= " location_start_id=".$data["location_start_id"].",";
|
|
$q.= " location_end_id=".$data["location_end_id"].",";
|
|
$q.= " processed=".((int)$data["processed"]);
|
|
$q.= " WHERE id='".$data["id"]."' RETURNING id";
|
|
error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$res = self::getById($db, $f["id"]);
|
|
error_log('res count => '.count($res));
|
|
if (is_array($res) && count($res)) {
|
|
return [$res,NULL];
|
|
}
|
|
return [$data, NULL]; // fallback...
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
}
|
|
|
|
// vi:ts=2
|
|
|