<?php include 'usercheck.php' ?>
<?php include 'genform.php' ?>
<?php include 'database.php' ?>
<?php include 'genqueryinc.php' ?>
<?php
if($Submit == "Search..."){
$sql = gq_generateSelectCommon($BuildQueryData);
$sql.=' AND (';
for($i=0; $i < $NumLines; ++$i){
$field=FIELD_VAR.$i;
$cond=CONDITION_VAR.$i;
$union=UNION_VAR.$i;
$value=VALUE_VAR.$i;
// if ($$value == "")
// continue; // skip for the next condition, this is empty
$sql.=" ( ";
$sql.="d.".$FieldData['sqlvalue'][$$field]." ";
switch ($$cond) {
case 1: // contains
$sql.= $Conditions['sqlvalue'][$$cond]." ";
$$value = "%".$$value."%";
break;
default:
$sql.=$Conditions['sqlvalue'][$$cond]." ";
}
if ($FieldData['sqltype'][$$field] == 'int4')
$sql.=$$value;
else
$sql.= "'".$$value."'";
$sql.=") ";
if($i < $NumLines-1)
$sql.=$Unions['sqlvalue'][$$union];
}
$sql.=" )";
$sql.=" ORDER BY ".$OrderBy." ".$OrderByDir;
//print($sql);
$tts_query = $sql;
?>
<html>
<head>
<title>Query form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="style.css">
</head>
<body bgcolor="#FFFFFF">
<?php include 'header.php'; ?>
<h1>Query results</h1>
<p> </p>
<?php
include 'queryresult.php';
?>
<p> </p>
<?php include 'footer.php' ?>
</body>
</html>
<?php
exit();
}
?>
<html>
<head>
<title>Advanced query form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="style.css">
</head>
<body bgcolor="#FFFFFF">
<?php include 'header.php'; ?>
<h1>Advanced query form</h1>
<p>Here you can specify the conditions of your search query.</p>
<form method="post" action="genquery.php" name="querylines">
<p><img src="images/titleback.jpg" valign="middle" width="100%" height="3"></p>
<table width="100%" border="0" cellpadding="2"
class="tableStyle" cellpacing="2">
<?php
ttdb_connect($db);
if($NumLines > 1){
/* Detect if a delete operation is ON */
$deleted=-1;
for($i=0; $i < $NumLines; ++$i){
$deleteButton=DELETE_VAR.$i;
if($$deleteButton == "Delete"){
$deleted=$i;
break;
}
}
if($deleted != -1){
/* adjust values of variables */
for($i=$deleted; $i<$NumLines-1; ++$i){
$field=FIELD_VAR.$i;
$fieldNext=FIELD_VAR.($i+1);
$$field=$$fieldNext;
$field=CONDITION_VAR.$i;
$fieldNext=CONDITION_VAR.($i+1);
$$field=$$fieldNext;
$field=VALUE_VAR.$i;
$fieldNext=VALUE_VAR.($i+1);
$$field=$$fieldNext;
$field=UNION_VAR.$i;
$fieldNext=UNION_VAR.($i+1);
$$field=$$fieldNext;
}
--$NumLines;
}
}
if($NumLines == 0)
$NumLines=1;
if($AddField == "Add condition")
$NumLines++;
for($i=0; $i < $NumLines; ++$i){
$selectedfield=FIELD_VAR.$i;
$selectedcond=CONDITION_VAR.$i;
$selectedunion=UNION_VAR.$i;
$selectedvalue=VALUE_VAR.$i;
print("<tr>");
gq_generateFields($FieldData, $i, FIELD_VAR,
$$selectedfield, "querylines");
gq_generateConditions($Conditions,$i,CONDITION_VAR,$$selectedcond);
gq_generateValue($FieldData,$i,VALUE_VAR,
$$selectedfield,$db,$$selectedvalue);
gq_generateUnion($Unions,$i,UNION_VAR,$$selectedunion);
gq_generateDeleteButton($i,DELETE_VAR,"querylines");
printf("</tr>");
}
?>
</table>
<p><img src="images/titleback.jpg" valign="middle" width="100%" height="3"></p>
<table width="50%" border="0" cellpadding="2"
class="tableStyle" cellpacing="2">
<tr>
<td width="20%"><font size=2>Order by</font></td>
<td width="40%">
<select name="OrderBy">
<?php
foreach($BuildQueryData['selectField']['orderClauseValue']
as $key => $value) {
gq_printOption($value,$value,$OrderBy);
}
?>
</select>
</td>
<td class="tableStyle" width="40%">
<select name="OrderByDir">
<?php
foreach($BuildQueryData['OrderByDir'] as $key => $value){
gq_printOption($key,$value,$OrderByDir);
}
?>
</select>
</td>
</tr>
</table>
<div align="center">
<p>
<input type="submit" name="AddField" value="Add condition">
<input type="submit" name="Submit" value="Search...">
</p>
</div>
<?php print("<input type=\"hidden\" name=\"NumLines\" value=\"".
$NumLines."\">");?>
</form>
</body>
<?php include 'footer.php' ?>
</html>
<?php
function gq_generateFields($Fields,$line,$fieldbasename,$selected,$formName){
gq_printTD("30%");
print("<select name=\"".$fieldbasename.$line.
"\" OnChange='".$formName.".submit()'>");
foreach($Fields['displayname'] as $key => $value)
gq_printOption($key,$value,$selected);
print("</select>");
print("</td>");
}
function gq_generateConditions($Conditions,$line,$fieldbasename,$selected){
gq_printTD("20%");
printf("<select name=\"".$fieldbasename.$line."\">");
foreach($Conditions['displayname'] as $key => $value)
gq_printOption($key,$value,$selected);
printf("</select>");
printf("</td>");
}
function gq_printTD($swidth){
print("<td class=\"tableStyle\" width=\"".$swidth."\">");
}
function gq_printOption($key,$value,$selected){
print("<option value=\"".$key."\"");
if($selected == $key)
print(" selected=\"true\"");
print(">".$value);
}
function gq_generateValue($Fields,
$line,
$fieldbasename,
$selectedField,
$db,
$selectedValue){
gq_printTD("30%");
if($Fields['lookupquery'][$selectedField]!=""){
gq_generateLookupField( $db,
$fieldbasename.$line,
'',
$Fields['lookupquery'][$selectedField],
'iid',
'sname',
$selectedValue);
}
else
printf('<input name="'.$fieldbasename.$line.'" value="'.$selectedValue.'">');
printf("</td>");
}
function gq_generateUnion($Unions,$line,$fieldbasename,$selected){
gq_printTD("10%");
printf("<select name=\"".$fieldbasename.$line."\">");
foreach($Unions['displayname'] as $key => $value)
gq_printOption($key,$value,$selected);
print("</select>");
print("</td>");
}
function gq_generateDeleteButton($line,$varName,$formName){
gq_printTD("10%");
print("<input type=\"submit\" value=\"Delete\" name=\"".$varName.$line."\"".
">");
print("</td>");
}
function gq_generateLookupField($db, $fieldName, $fieldDisplay, $query,
$lookupIdField, $lookupDisplayField, $selected)
{
print("<SELECT NAME=\"$fieldName\">\n");
$lookup = ttdb_execQuery($db, $query);
if ($lookup) {
while (($fields = ttdb_getArray($lookup)) != false) {
$value = $fields[$lookupIdField];
print('<OPTION VALUE="'.$value.'"');
if($value == $selected)
print(" selected=\"true\"");
print('>');
print($fields[$lookupDisplayField]."\n");
}
}
print("</SELECT>\n");
}
function gq_generateSelectCommon($Data){
$sql = "SELECT ";
/* fields in select */
foreach($Data['selectField']['sqlClauseValue'] as $key => $value){
$sql.= $value.' as '.$Data['selectField']['orderClauseValue'][$key];
if($key < count($Data['selectField']['sqlClauseValue']) - 1)
$sql.=', ';
}
/* tables in from */
$sql.=" FROM ";
foreach($Data['fromTable'] as $key => $value){
$sql.= $value;
if($key < count($Data['fromTable']) -1)
$sql.=', ';
}
/* join clauses */
$sql.=' WHERE (';
foreach($Data['selectField']['joinClause'] as $key => $value){
if($value != ""){
$sql.='('.$value.')';
if($key < count($Data['selectField']['joinClause'])-1 ){
$sql.=' AND ';
}
}
}
$sql.=' )';
return $sql;
}
?>