[eluser]Sorasora[/eluser]
Hello everybody,
I'm coding a little application with a search form which allows the user to select several options, and I'm having troubles to implement the model part.
I've already made it without codeIgniter, but I want to rebuild all my app in CI, so I would like to use the Active Record. (To have the possibility to switch between POSTgre, mySQL...)
Here is my code in his old version :
Code:
<?php
include 'bd_connexion.php';
$datedebut = htmlspecialchars(trim($_POST['datedebut']));
$datefin = htmlspecialchars(trim($_POST['datefin']));
$jours = htmlspecialchars(trim($_POST['jours']));
$ferie = htmlspecialchars(trim($_POST['ferie']));
$horairedebut = htmlspecialchars(trim($_POST['horairedebut']));
$horairefin = htmlspecialchars(trim($_POST['horairefin']));
$labo = htmlspecialchars(trim($_POST['labo']));
$corres = htmlspecialchars(trim($_POST['corres']));
//Formation of datedebut and datefin
$i=0;
if($datedebut != 'AAAA/MM/JJ' && $datedebut != ''){
$choix[$i++] = "DATE(MIN_VAL_PVT)";
$choix[$i++] = ">='".$datedebut."'";
}
if($datefin != 'AAAA/MM/JJ' && $datefin != ''){
$choix[$i++] = "DATE(MIN_VAL_PVT)";
$choix[$i++] = "<='".$datefin."'";
}
//Formation of jours
$jour = array();
foreach (explode(",",$jours) as $elt){
$jour[] = $elt;
}
if($jours != "null") {
$nom=$i++;
$val=$i++;
if(count($jour) >1){
$choix[$nom] = " (JOUR_PVT= '";}
else{ $choix[$nom] = "JOUR_PVT= '";}
$j=0;
$condition="";
$choix[$val]="";
while($jour[$j]){
if($j>0){$condition = "' OR JOUR_PVT = '";}
$choix[$val].=$condition.$jour[$j];
$j++;
}
$choix[$val].="' ";
if(count($jour) >1){$choix[$val].=") ";}
}
//Formation of ferie
if($ferie == "oui"){
$choix[$i++] = "DATE(MIN_VAL_PVT) NOT IN (";
$choix[$i++] = "SELECT DATE(JourDate) FROM joursferies)";
}
//Formation of horairedebut and horairefin
if ($horairedebut == "00:00" && $horairefin == "23:59"){}
else{
$choix[$i++] = "TIME(MIN_VAL_PVT)";
$choix[$i++] = ">'".$horairedebut."'";
$choix[$i++] = "TIME(MIN_VAL_PVT)";
$choix[$i++] = "<'".$horairefin."'";}
//Formation of labo
$lab = array();
foreach (explode(",",$labo) as $elt){
$lab[] = $elt;
}
if($labo != "null") {
$nom=$i++;
$val=$i++;
if(count($lab) >1){
$choix[$nom] = " (LABO_INTERNE= '";}
else{ $choix[$nom] = "LABO_INTERNE= '";}
$j=0;
$condition="";
$choix[$val]="";
while($lab[$j]){
if($j>0){$condition = "' OR LABO_INTERNE = '";}
$choix[$val].=$condition.$lab[$j];
$j++;
}
$choix[$val].="' ";
if(count($lab) >1){$choix[$val].=") ";}
}
//Formation of corres
$corr = array();
foreach (explode(",",$corres) as $elt){
$corr[] = $elt;
}
if($corres != "null") {
$nom=$i++;
$val=$i++;
if(count($corr) >1){
$choix[$nom] = " (CODE_CORR= '";}
else{ $choix[$nom] = "CODE_CORR= '";}
$j=0;
$condition="";
$choix[$val]="";
while($corr[$j]){
if($j>0){$condition = "' OR CODE_CORR = '";}
$choix[$val].=$condition.$corr[$j];
$j++;
}
$choix[$val].="' ";
if(count($corr) >1){$choix[$val].=") ";}
}
//FORMATION OF THE QUERY :
if (empty($choix))
$critere = "";
else{
$critere = " AND ".$choix[0].$choix[1]." ";
for($j=2;$j<$i;$j+=2){
$critere .=" AND ".$choix[$j].$choix[$j+1]." ";
}
}
$query= "SELECT (AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000) AS delaitec,
(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_BIO,MAX_VAL_TEC)))*1000) AS delaibio,
(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000)+(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_BIO,MAX_VAL_TEC)))*1000) AS delaitotal,
COUNT( MAX_VAL_TEC ) AS Nblignes
FROM nice_bio_delaimoy
WHERE CHAP='SANG_LOCAL'".$critere;
$req = mysql_query($query) or die("echec");
$rows = array();
while($r = mysql_fetch_assoc($req)) {
$rows[] = $r;
}
header('Content-type: application/json');
echo json_encode($rows);
// Disconnect
mysql_close();
?>
The variables aren't the problem here,
the biggest problems are how to build the "WHERE" clause with Active Record, and how to translate my complex SELECT clause in CI ("(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000)").
Thanks for your help !