source: trunk/admin/modules/Elezioni/query.sql@ 429

<?php

if (!defined('ADMIN_FILE')) {
    die ("You can't access this file directly...");
}

function getaffluenze()
{
        global $dbi,$prefix, $id_cons_gen;
        $sql = "SELECT * FROM ".$prefix."_ele_rilaff where id_cons_gen=$id_cons_gen order by data,orario";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return ($row);
}

function daticonsultazione()
{
        global $dbi,$prefix, $id_cons_gen;
        $sql = "SELECT * FROM ".$prefix."_ele_consultazione where id_cons_gen='$id_cons_gen'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return ($row);


}

function daticonscom()
{
        global $dbi,$prefix, $id_cons;
        $sql = "SELECT * FROM ".$prefix."_ele_cons_comune where id_cons='$id_cons'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return ($row);


}

function datitipo($tipo,$lang)
{
        global $dbi,$prefix;
        $sql = "SELECT * FROM ".$prefix."_ele_tipo where tipo_cons='$tipo' and lingua='$lang'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return ($row);


}


function setconsultazione()
{
global $prefix,$dbi,$perms,$id_comune,$espandi,$id_cons_gen,$aid,$id_cons,$limite;

if ($id_cons_gen) {
        if ($id_comune and $perms<256)
                $sql = "SELECT t1.tipo_cons,t1.descrizione,t2.id_cons_gen FROM ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_cons_comune as t2 where t1.id_cons_gen=t2.id_cons_gen and t2.id_cons_gen='$id_cons_gen'";
        else
                $sql = "SELECT t1.tipo_cons,t1.descrizione,t1.id_cons_gen FROM ".$prefix."_ele_consultazione as t1 where  t1.id_cons_gen='$id_cons_gen'";
}else{
        if($perms>128)
                $sql = "SELECT tipo_cons,descrizione,id_cons_gen FROM ".$prefix."_ele_consultazione order by data_fine desc limit 0,1 ";
        else
                $sql = "SELECT t1.tipo_cons,t1.descrizione,t2.id_cons_gen FROM ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_cons_comune as t2, ".
                $prefix."_ele_operatori as t3 where t3.id_comune=$id_comune and t3.id_comune=t2.id_comune and t1.id_cons_gen=t2.id_cons_gen and t2.chiusa='0' and (t3.id_cons=t2.id_cons or t3.id_cons=0) and t3.permessi>0 and date_add(t1.data_fine, interval 3 day)>CURDATE() and t3.aid='$aid' order by t1.data_fine desc limit 0,1 "; 

}
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        $espandi=0;

        if ($sth->rowCount()) $id_cons_gen=$row[2];
#               list($tipo_cons,$descr_cons,$id_cons_gen) = mysql_fetch_row($res);
        
                $sql = "SELECT t2.id_cons FROM ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_cons_comune as t2 where t1.id_cons_gen=t2.id_cons_gen and t2.id_cons_gen='$id_cons_gen' and t2.id_comune='$id_comune'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
    if ($sth->rowCount()) list($id_cons) = $sth->fetch(PDO::FETCH_NUM); 
        else    $espandi=1;
        if(!isset($tipo_cons))$tipo_cons=0;
        if(!isset($id_cons))$id_cons=0;

        $sql="SELECT t1.limite FROM ".$prefix."_ele_conf as t1 left join ".$prefix."_ele_cons_comune as t2 on t1.id_conf=t2.id_conf where t2.id_cons='$id_cons'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        if ($sth->rowCount()){
                list($limite) = $sth->fetch(PDO::FETCH_NUM); 
        } else $limite=0;
        return($row);
}

function elenco_cons()
{
global $perms,$dbi,$prefix,$currentlang,$aid,$id_comune,$giorniaut;

        if ($perms<64) {
                $sql="select t2.id_cons,t1.descrizione,t4.genere,t1.id_cons_gen,t2.chiusa from ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_cons_comune as t2, ".$prefix."_ele_tipo as t4 where t1.id_cons_gen=t2.id_cons_gen and t2.id_comune=$id_comune and t1.tipo_cons=t4.tipo_cons and date_add(t1.data_fine, interval $giorniaut day)>CURDATE() and t2.id_cons in (select id_cons from ".$prefix."_ele_operatori where aid='$aid' and permessi>0)";
        }
        elseif ($perms<128) { 
                $sql="select t3.id_cons, t2.descrizione,t4.genere, t2.id_cons_gen, t3.chiusa from ".$prefix."_ele_operatori as t1, ".$prefix."_ele_consultazione as t2, ".$prefix."_ele_cons_comune as t3, ".$prefix."_ele_tipo as t4 where t4.lingua='$currentlang' and t2.tipo_cons=t4.tipo_cons and t1.aid='$aid' and t1.permessi>0 and t3.id_cons_gen=t2.id_cons_gen and (t1.id_cons=t3.id_cons or t1.permessi=64) and t1.id_comune=t3.id_comune and t1.id_comune=$id_comune order by t2.data_inizio desc";
        }else{
                $sql="SELECT '', t1.descrizione,t2.genere, t1.id_cons_gen,'' FROM ".$prefix."_ele_consultazione as t1,".$prefix."_ele_tipo as t2 where t2.lingua='$currentlang' and t1.tipo_cons=t2.tipo_cons order by t1.data_inizio desc";
        } #die("$sql");
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return($row);   
}

function elenco_comuni()
{
global $dbi,$prefix;
        $sql="select * from ".$prefix."_ele_comuni order by descrizione asc";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return($row);   

}

function elenco_consultazioni()
{
global $dbi,$prefix;
        $sql="select * from ".$prefix."_ele_consultazione order by descrizione asc";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return($row);   

}

function elenco_cons_comune()
{
$sql="SELECT t1.id_cons, t2.descrizione FROM ".$prefix."_ele_cons_comune as t1 left join ".$prefix."_ele_consultazione as t2 on t1.id_cons_gen=t2.id_cons_gen where t1.id_comune='$id_comune' ";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return($row);   
}

function elenco_tipi()
{
global $dbi,$prefix,$language,$currentlang;
        $sql="SELECT * FROM ".$prefix."_ele_tipo where lingua='$currentlang'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetchAll();
        return($row);   

}

function descr_comune()
{
global $dbi,$prefix,$id_comune;
        $sql="select id_comune,descrizione,id_cons from ".$prefix."_ele_comuni where id_comune='$id_comune'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return($row);   

}

function descr_cons()
{
global $dbi,$prefix,$id_cons_gen;
        $sql="SELECT t1.tipo_cons,t2.genere,t1.descrizione FROM ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_tipo as t2 where t1.tipo_cons=t2.tipo_cons and t1.id_cons_gen='$id_cons_gen' ";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return($row);   

}

function cur_cons()
{
global $dbi,$prefix,$id_cons_gen,$id_comune;
        $sql="SELECT t1.tipo_cons,t2.id_cons FROM ".$prefix."_ele_consultazione as t1, ".$prefix."_ele_cons_comune as t2 where t1.id_cons_gen=t2.id_cons_gen and t2.id_cons_gen='$id_cons_gen' and t2.id_comune='$id_comune'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return($row);   

}

function tipocons()
{
global $dbi,$prefix,$tipo_cons,$currentlang;
        $sql="SELECT genere,voto_g,voto_l,voto_c,circo FROM ".$prefix."_ele_tipo where tipo_cons='$tipo_cons' and lingua='$currentlang'";
        $sth = $dbi->prepare("$sql");
        $sth->execute();        
        $row = $sth->fetch(PDO::FETCH_BOTH);
        return($row);
}

?>