<?php

/**
 * Query builder
 *
 * @author j.koch
 */
class QueryBuilder {

  private $conditions = array();

  function __construct() {

    $this->buildSimpleConditionWithLike('AkzNr');
    
    $this->buildConditionForAkzDatJahr();
    
    $this->buildSimpleConditionWithLike('HerkDaten');
    
    $this->buildConditionWithOrAndLike('WildHerkunft');
    
    $this->buildConditionWithOrAndEquals('ISOCode');
    
    $this->buildSimpleConditionWithLike('Sammler');
    
    $this->buildConditionWithOrAndLike('AndereHerkunft');
    
    $this->buildSimpleConditionWithEquals('BGAbk');
    
    $this->buildSimpleConditionWithLike('B_OrtAbkuerzung');
    
    $this->buildSimpleConditionForLebt();

    $wissNameTax = array('WissNameTax', 'H_WissNameTax');
    $this->buildArrayConditionWithLike($wissNameTax);
    
    $hTaxon = array('HTaxon', 'H_HTaxon');
    $this->buildArrayConditionWithLike($hTaxon);

    $familie = array('Familie', 'H_Familie');
    $this->buildArrayConditionWithLike($familie);

    $deutscheNamen = array('DeutscheNamen', 'H_DeutscheNamen');
    $this->buildArrayConditionWithLike($deutscheNamen);

    $iucnc = array('IUCNC', 'H_IUCNC');
    $this->buildArrayConditionWithOrAndEquals($iucnc);    
  }

  private function buildSimpleConditionWithLike($fieldName) {
    $value = trim($_POST[$fieldName]);
    if ($value) {
      $value = $this->replace($value);
      $this->conditions[] = "$fieldName like '$value'";
    }
  }

  private function buildSimpleConditionWithEquals($fieldName) {
    $value = trim($_POST[$fieldName]);
    if ($value) {
      $this->conditions[] = "$fieldName = '$value'";
    }
  }

  private function buildSimpleConditionForLebt() {
    if (($_POST['Lebt'] != "")) {
      $value = $_POST['Lebt'];
      $this->conditions[] = "Lebt = $value";
    }
  }

  private function buildConditionWithOrAndLike($fieldName) {
    $value = trim($_POST[$fieldName]);
    if ($value) {
      // split the string at the delimiter '|'
      $entries = explode("|", $this->replace($value));
      for ($i = 0; $i < count($entries); $i++) {
        $entry = trim($entries[$i]);
        if ($i == 0) {
          $condition = " ($fieldName like '$entry'";
        } else {
          $condition .= " OR $fieldName like '$entry'";
        }
      }
      $this->conditions[] = $condition . ')';
    }
  }

  private function buildConditionWithOrAndEquals($fieldName) {
    $value = trim($_POST[$fieldName]);
    if ($value) {
      // split the string at the delimiter '|'
      $entries = explode("|", $this->replace($value));
      for ($i = 0; $i < count($entries); $i++) {
        $entry = trim($entries[$i]);
        if ($i == 0) {
          $condition = " ($fieldName = '$entry'";
        } else {
          $condition .= " OR $fieldName = '$entry'";
        }
      }
      $this->conditions[] = $condition . ')';
    }
  }
  
  private function buildArrayConditionWithLike($fieldNames) {
    // the first fieldName (database column) equals the name of the input field
    $value = trim($_POST[$fieldNames[0]]);
    if ($value) {
      $value = $this->replace($value);
      for ($i = 0; $i < count($fieldNames); $i++) {
        if ($i == 0) {
          $condition = " ($fieldNames[0] like '$value'";
        } else {
          $condition .= " OR $fieldNames[$i] like '$value'";
        }
      }
      $this->conditions[] = $condition . ')';
    }
  }

  private function buildArrayConditionWithOrAndEquals($fieldNames) {
    // the first fieldName (database column) equals the name of the input field
    $value = trim($_POST[$fieldNames[0]]);
    if ($value) {
      // split the string at the delimiter '|'
      $entries = explode("|", $this->replace($value));
      for ($i = 0; $i < count($entries); $i++) {
        $entry = trim($entries[$i]);
        for ($k = 0; $k < count($fieldNames); $k++) {
          if ($k == 0 && $i == 0) {
            $condition = " ($fieldNames[0] = '$entry'";
          } else {
            $condition .= " OR $fieldNames[$k] = '$entry'";
          }
        }
      }
      $this->conditions[] = $condition . ')';
    }
  }

  private function buildConditionForAkzDatJahr() {
    // we need numbers only (filtering letters a.o. for simple error handling)
    $akzDatJahr1 = preg_replace("/[^0-9]/", "", $_POST['AkzDatJahr1']);
    $akzDatJahr2 = preg_replace("/[^0-9]/", "", $_POST['AkzDatJahr2']);
    $operator = $this->operator($_POST['opAkzDatJahr']);
    // there is an entry in both akzDatJahr fields
    if ($akzDatJahr1 && $akzDatJahr2) {
      if ($akzDatJahr1 < $akzDatJahr2) {
        $this->conditions[] = 'AkzDatJahr between ' . $akzDatJahr1 . ' and ' . $akzDatJahr2;
      } elseif ($akzDatJahr1 > $akzDatJahr2) {
        $this->conditions[] = 'AkzDatJahr between ' . $akzDatJahr2 . ' and ' . $akzDatJahr1;
      } else {
        $this->conditions[] = 'AkzDatJahr ' . $operator . ' ' . $akzDatJahr1;
      }
      // there is an entry for year 1 only
    } elseif ($akzDatJahr1) {
      $this->conditions[] = 'AkzDatJahr ' . $operator . ' ' . $akzDatJahr1;
      // there is an entry for year 2 only
    } elseif ($akzDatJahr2) {
      $this->conditions[] = 'AkzDatJahr ' . $operator . ' ' . $akzDatJahr2;
    }
  }

  /**
   * Replace user defined placeholder (*) with database defined placeholder (%)
   */
  private function replace($txt) {
    $result = preg_replace("/\*/", "%", $txt);
    return $result;
  }

  /**
   * Map operators defined as hidden parameters to operators used in the database
   */
  private function operator($op) {
    if ($op == "lk") {
      $operator = "like";
    } elseif ($op == "gt") {
      $operator = ">";
    } elseif ($op == "lt") {
      $operator = "<";
    } elseif ($op == "eq") {
      $operator = "=";
    } else {
      $operator = "leer";
    }
    return $operator;
  }

  /**
   *  Conditions for the select statement
   * @return string 
   */
  public function getConditions() {
    return $this->conditions;
  }

}

?>