Location: PHPKode > projects > Druid Defect Control > defectcontrol_0.9.4/tts/genquery.php
<?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>&nbsp;</p>

<?php
    include 'queryresult.php';
?>
<p>&nbsp;</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;
}

?>


Return current item: Druid Defect Control