function getUltimaRevisioVehicle($codi_vehicle)
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT \r\n\t\t\t\t\t\tdata_l, \r\n\t\t\t\t\t\tkms \r\n\t\t\t\t\tFROM \r\n\t\t\t\t\t\trevisio \r\n\t\t\t\t\tWHERE codi IN ( \r\n\t\t\t\t\t\tselect max(rev.codi) \r\n\t\t\t\t\t\tfrom revisio rev \r\n\t\t\t\t\t\twhere rev.codi_vehicle = :codi_vehicle \r\n\t\t\t\t\t\tgroup by rev.codi_vehicle \r\n\t\t\t\t\t)";
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":codi_vehicle", $codi_vehicle);
oci_execute($stid);
$ultima = oci_fetch_object($stid);
return $ultima;
}
private function buscarMesJove()
{
$codi_delegacio = $this->_infos->CODI_DELEGACIO;
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT codi_delegacio, min(data_alta), max(codi) as CODI FROM venedor WHERE codi_delegacio = :codi_delegacio group by codi_delegacio";
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":codi_delegacio", $codi_delegacio);
oci_execute($stid);
$jove = oci_fetch_object($stid);
return $jove->CODI;
}
function comprovaAcces()
{
$user = $this->_usuari;
$pass = $this->_contrasenya;
$oci = new Oci($user, $pass);
$conectat = false;
if ($oci->getConnection() === null) {
$this->_exceptions[] = 'Error al establir la conexió';
} else {
$conectat = true;
$_SESSION['user']['usuari'] = $this->_usuari;
$_SESSION['user']['passwd'] = $this->_contrasenya;
}
return $conectat;
}
function getVehiclesDisponibles()
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT \r\n\t\t\t\t\t\tv.codi, \r\n\t\t\t\t\t\tv.matricula, \r\n\t\t\t\t\t\tv.color, \r\n\t\t\t\t\t\tv.combustible,\r\n\t\t\t\t\t\tm.nom as MODEL \r\n\t\t\t\t\tFROM vehicle v JOIN model m ON v.model_codi = m.codi\r\n\t\t\t\t\tWHERE v.codi NOT IN (\r\n\t\t\t\t\t\tSELECT codi_vehicle \r\n\t\t\t\t\t\tFROM lloguer WHERE dataf is null) \r\n\t\t\t\t\tGROUP BY v.codi, v.matricula, v.color, v.combustible, m.nom";
$stid = oci_parse($oci->getConnection(), $req);
oci_execute($stid);
if ($stid) {
$result_array = array();
$i = 0;
while ($temp = oci_fetch_object($stid)) {
$result_array[$i] = $temp;
$i++;
}
}
oci_free_statement($stid);
$oci->tancarConnexio();
return $result_array;
}
private function consultarBdd()
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT l.codi, \r\n\t\t\t\t\t\tc.nom || ' ' || c.cognoms as CLIENT, \r\n\t\t\t\t\t\tv.nom || ' ' || v.cognoms as VENEDOR, \r\n\t\t\t\t\t\tve.matricula,\r\n\t\t\t\t\t\tl.kmi, \r\n\t\t\t\t\t\tl.datai\r\n\t\t\t\t\tFROM\t\r\n\t\t\t\t\t\tlloguer l JOIN client c ON l.codi_client = c.codi \r\n\t\t \t\t\t\t\t JOIN venedor v ON l.codi_venedor = v.codi\r\n\t\t\t\t\t\t\t JOIN vehicle ve ON l.codi_vehicle = ve.codi \r\n\t\t\t\t\t\tWHERE dataf is null\r\n\t\t\t\t\t";
$stid = oci_parse($oci->getConnection(), $req);
oci_execute($stid);
if ($stid) {
$result_array = array();
$i = 0;
while ($temp = oci_fetch_object($stid)) {
$result_array[$i] = $temp;
$i++;
}
}
oci_free_statement($stid);
$oci->tancarConnexio();
$this->_llista = $result_array;
}
private function consultarBdd()
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = 'SELECT a.descripcio as "DESC", codi_accessori as "CODI" FROM accessori a JOIN model_accessori ma ON a.codi = ma.codi_accessori WHERE codi_model=:cm';
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":cm", $this->_model);
oci_execute($stid);
if ($stid) {
$result_array = array();
$i = 0;
while ($temp = oci_fetch_object($stid)) {
$result_array[$i] = $temp;
$i++;
}
}
oci_free_statement($stid);
$oci->tancarConnexio();
$this->_llista = $result_array;
}
private function consultarBdd()
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = 'SELECT m.codi, m.nom FROM model m
';
$stid = oci_parse($oci->getConnection(), $req);
oci_execute($stid);
if ($stid) {
$result_array = array();
$i = 0;
while ($temp = oci_fetch_object($stid)) {
$result_array[$i] = $temp;
$i++;
}
}
oci_free_statement($stid);
$oci->tancarConnexio();
$this->_llista = $result_array;
}
function save()
{
$res = true;
//realitzar insert
//foreach $infos as $info
if (count($this->_infos) > 0) {
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "INSERT INTO model";
$camps = "(";
$bindeados = "(";
foreach ($this->_infos as $camp) {
$camps .= $camp->getNom() . ",";
$bindeados .= ":" . $camp->getNom() . ",";
}
$camps .= "codi)";
$bindeados .= ":codi)";
$req .= $camps . " VALUES " . $bindeados;
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":codi", $this->_id);
foreach ($this->_infos as $camp) {
oci_bind_by_name($stid, ':' . $camp->getNom(), $camp->getValor());
}
$r = oci_execute($stid);
if (!$r) {
if (DEV) {
$e = oci_error($stid);
print htmlentities($e['message']);
print "\n<pre>\n";
print htmlentities($e['sqltext']);
printf("\n%" . ($e['offset'] + 1) . "s", "^");
print "\n</pre>\n";
} else {
$this->_exceptions[] = "No s'ha pogut inserir el nou vehicle";
}
$res = false;
}
oci_free_statement($stid);
$oci->tancarConnexio();
}
return $res;
}
private function consultarBdd()
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = 'SELECT
v.codi, v.nom, v.cognoms, d.nom as delegacio
FROM
venedor v JOIN delegacio d ON v.codi_delegacio = d.codi
';
$stid = oci_parse($oci->getConnection(), $req);
oci_execute($stid);
if ($stid) {
$result_array = array();
$i = 0;
while ($temp = oci_fetch_object($stid)) {
$result_array[$i] = $temp;
$i++;
}
}
oci_free_statement($stid);
$oci->tancarConnexio();
$this->_llista = $result_array;
}
function save()
{
$res = true;
if (count($this->_infos) > 0) {
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "INSERT INTO lloguer";
$camps = "(";
$bindeados = "(";
//Preparar el insert del lloguer, segons el tipus de dades.
foreach ($this->_infos as $camp) {
$camps .= $camp->getNom() . ",";
if ($camp->getTipus() == "data") {
$bindeados .= "to_date(:" . $camp->getNom() . ", 'DD-MM-YYYY HH24:MI:SS'),";
} else {
$bindeados .= ":" . $camp->getNom() . ",";
}
}
$camps .= "codi)";
$bindeados .= ":codi)";
/*
Ja que necessitem el codi del lloguer per inserir els accesoris, cal que se'ns el retorni. Això es fa amb el returning.
*/
$req .= $camps . " VALUES " . $bindeados . "returning codi into :lastInsertId";
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ':lastInsertId', $lastInsertId, 8);
oci_bind_by_name($stid, ":codi", $this->_id);
foreach ($this->_infos as $camp) {
if ($camp->getTipus == "data") {
oci_bind_by_name($stid, ':' . $camp->getNom(), $camp->getValor());
} else {
oci_bind_by_name($stid, ':' . $camp->getNom(), $camp->getValor());
}
}
//Preparar la request de accessoris
$ok = true;
if (!empty($this->_accessoris)) {
$r = oci_execute($stid, OCI_NO_AUTO_COMMIT);
if ($r) {
//mentres for i a validi ves inserint
foreach ($this->_accessoris as $a) {
$req2 = "INSERT INTO ACCESSORIS_LLOGATS(CODI_LLOGUER,CODI_ACCESSORI) VALUES (:lastInsertId, :codi_accessori)";
$stid2 = oci_parse($oci->getConnection(), $req2);
oci_bind_by_name($stid2, ':lastInsertId', $lastInsertId);
oci_bind_by_name($stid2, ':codi_accessori', $a);
$r2 = oci_execute($stid2);
//Va comprovant si es pot anar fer els insert, a la que falla un, plega
if (!$r2) {
$ok = false;
break;
}
}
//Si s'ha produit un error es fa un rollback
if (!$ok) {
$oci->rollback();
$this->_exception[] = "S'ha produit un error";
} else {
$oci->commit();
}
}
} else {
$r = oci_execute($stid);
}
oci_free_statement($stid);
$oci->tancarConnexio();
}
return $res;
}
