<?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