/assicurazioniesinistri_mysql/include/Database/QueryBuilder.class.php

<?php
/**
 * File QueryBuilder.class.php
 *
 * This file contains the definition of the class object
 * type QueryBuilder.
 * @author Dott. Marco Novo <mnovo@ncfsistemi.com>
 * @version 1.0
 */
/**
 * This class defines fields and methods of QueryBuilder.
 * Methods of this class are usefull to
 * control Antilope's database query.
 */
class QueryBuilder {
  public $type='mysql';
  public $arrayObject;

  /**
    * Constructor of the class
    */
  function __construct() {
  }

  /**
    * Destructor of the class
    */
  function __destruct() {
  }

  /**
    * Sets the value of field arrayObject
    * @param array $arrayObject
    */
  function setArrayObject($arrayObject){
    $this->arrayObject=$arrayObject;
  }

  /**
    * buildInsert method: build an Insert SQL Query
    * starting from an array representing the Object
    * fixed problem with foreign key ('' is NULL)
    * @param string $Object_name
    * @param string $Object_tableName
    * @param string $Object_foreignKey
    * @return $query
    */
  function buildInsert($Object_name, $Object_tableName, $Object_foreignKey=array()){
    $Object_match='';
    $Insert_set='';
    foreach($this->arrayObject as $key => $value) {
      if ($Object_name!=''){
        $Object_match=explode('__', $key);
        $Object_match=$Object_match[0];
      }
      if ($Object_match==$Object_name){
        $value=str_replace("\'","'",$value);
        $value=str_replace("'","''",$value);
        // If this key is foreign and value = '' sets the database value to NULL
        if (array_search(str_replace($Object_name.'__','',$key), $Object_foreignKey)!==false && $value=='')
          $Insert_set.= ' '.str_replace($Object_name.'__','',$key)."=NULL,";
        else
          $Insert_set.= ' '.str_replace($Object_name.'__','',$key)."='".$value."',";
      }
    }
    $Insert_set=rtrim($Insert_set, ',');
    $query="INSERT INTO $Object_tableName
            SET $Insert_set";
    return $query;
  }

  /**
    * buildUpdate method: build an Update SQL Query
    * starting from an array representing the Object
    * fixed problem with foreign key ('' is NULL)
    * @param string $Object_name
    * @param string $Object_tableName
    * @param string/array $Object_primaryKey
    * @param string/array $Object_foreignKey
    * @return $query
    */
  function buildUpdate($Object_name, $Object_tableName, $Object_primaryKey, $Object_foreignKey=array()){
    $Object_match='';
    $Update_set='';
    $Where_set='';
    foreach($this->arrayObject as $key => $value) {
      if ($Object_name!=''){
        $Object_match=explode('__', $key);
        $Object_match=$Object_match[0];
      }
      if ($Object_match==$Object_name){
        $value=str_replace("\'","'",$value);
        $value=str_replace("'","''",$value);
        $updateKeyMatched=0;
        if (is_array($Object_primaryKey)){
          foreach($Object_primaryKey as $updateKey) {
            if ($updateKey==str_replace($Object_name.'__','',$key)){
              $Where_set.= " AND ".$updateKey." = '".$value."'";
              $updateKeyMatched=1;
            }
            if (!$updateKeyMatched){
              // If this key is foreign and value = '' sets the database value to NULL
              if (array_search(str_replace($Object_name.'__','',$key), $Object_foreignKey)!==false && $value=='')
                $Update_set.= ' '.str_replace($Object_name.'__','',$key)."=NULL,";
              else
                $Update_set.= ' '.str_replace($Object_name.'__','',$key)."='".$value."',";
            }
          }
        }
        else{
          if ($Object_primaryKey==str_replace($Object_name.'__','',$key))
            $Where_set.= " AND ".$Object_primaryKey." = '".$value."'";
          else{
            // If this key is foreign and value = '' sets the database value to NULL
            if (array_search(str_replace($Object_name.'__','',$key), $Object_foreignKey)!==false && $value=='')
              $Update_set.= ' '.str_replace($Object_name.'__','',$key)."=NULL,";
            else
              $Update_set.= ' '.str_replace($Object_name.'__','',$key)."='".$value."',";
          }
        }
      }
    }
    $Update_set=rtrim($Update_set, ',');
    $query="UPDATE $Object_tableName
            SET $Update_set
            WHERE 1 $Where_set";
    return $query;
  }

  /**
    * buildInsertOrUpdate method: build an Update SQL Query
    * starting from an array representing the Object
    * fixed problem with foreign key ('' is NULL)
    * @param string $Object_name
    * @param string $Object_tableName
    * @param string/array $Object_foreignKey
    * @return $query
    */
  function buildInsertOrUpdate($Object_name, $Object_tableName, $Object_foreignKey=array()){
    $Object_match='';
    foreach($this->arrayObject as $key => $value) {
      if ($Object_name!=''){
        $Object_match=explode('__', $key);
        $Object_match=$Object_match[0];
      }
      if ($Object_match==$Object_name){
        $value=str_replace("\'","'",$value);
        $value=str_replace("'","''",$value);
        // If this key is foreign and value = '' sets the database value to NULL
        if (array_search(str_replace($Object_name.'__','',$key), $Object_foreignKey)!==false && $value=='')
          $Insert_set.= ' '.str_replace($Object_name.'__','',$key)."=NULL,";
        else
          $Insert_set.= ' '.str_replace($Object_name.'__','',$key)."='".$value."',";
      }
    }
    $Insert_set=rtrim($Insert_set, ',');
    $query="INSERT INTO $Object_tableName
            SET $Insert_set
            ON DUPLICATE KEY UPDATE
            $Insert_set";
    return $query;
  }

  /**
    * buildDelete method: build a Delete SQL Query
    * starting from an array representing the Object
    * @param string $Object_name
    * @param string $Object_tableName
    * @return $query
    */
  function buildDelete($Object_name, $Object_tableName, $Object_primaryKey){
    $Object_match='';
    $Where_set='';
    foreach($this->arrayObject as $key => $value) {
      if ($Object_name!=''){
        $Object_match=explode('__', $key);
        $Object_match=$Object_match[0];
      }
      if ($Object_match==$Object_name){
        if (is_array($Object_primaryKey)){
          foreach($Object_primaryKey as $updateKey) {
            if ($updateKey==str_replace($Object_name.'__','',$key))
              $Where_set.= " AND ".$updateKey." = '".$value."'";
          }
        }
        else{
          if ($Object_primaryKey==str_replace($Object_name.'__','',$key))
            $Where_set.= " AND ".$Object_primaryKey." = '".$value."'";
        }
      }
    }
    $query="DELETE FROM $Object_tableName
            WHERE 1 $Where_set";
    return $query;
  }

  /**
    * getEnumOptions method: get all enum options of a
    * field and return an array
    * @param string $table
    * @param string $field
    * @param string $db
    * @param boolean $sort
    * @return $finalResult
    */
  function getEnumOptions($table, $field, $db, $sort=1) {
    $finalResult = array();
    if (strlen(trim($table)) < 1)
      return false;
    $query  = "show columns from $table";
    $result = mysql_query($query, $db);
    while ($row = mysql_fetch_array($result)){
      if ($field != $row["Field"])
        continue;
      if (ereg('enum.(.*).', $row['Type'], $match)) {
        $opts = explode("','", $match[1]);
        foreach ($opts as $item)
          $finalResult[] = str_replace("'","",$item);
      }
      else
        return false;
    }
    if ($sort)
      sort($finalResult);
    return $finalResult;
  }

  /**
    * printTable method:outputs any SQL table in an
    * HTML tabular format.
    * @param array $result
    * @return true if there is data in the result set
    * false if there is no data in the result  set
    */
  function printTable($result){
    if($result) {
      if(!$row = mysql_fetch_assoc($result))
        return false;
      print("<table><tr>");
      foreach($row as $key=>$value){
        print("<th>$key</th>");
      }
      print("</tr>");
      do{
        print("<tr>");
        foreach($row as $key=>$value)
          print("<td>$value</td>");
        print("</tr>");
      }
      while ($row = mysql_fetch_assoc($result));
      print("</tr></table>");
      return true;
    }
    else
      return false;
  }

  /*version 2.1*/
  /*author: acoppo@ncfsistemi.com*/

    /**
    * getDistinctFields method: get all distinct fields from table
    * return an array
    * @param string $table
    * @param string $field
    * @param string $db
    * @param boolean $sort
    * @return $finalResult
    */
  function getDistinctFields($table, $field, $db, $sort=1) {
    $finalResult = array();
    if (strlen(trim($table)) < 1)
      return false;
    $query  = "SELECT DISTINCT $field FROM $table";
    $result = mysql_query($query, $db);
    while ($row = mysql_fetch_array($result)){
      $finalResult[] = $row[$field];
    }
    //  else
    //    return false;
    if ($sort)
      sort($finalResult);
    return $finalResult;
  }


    /**
    * getForeignLabel method: get a generic label for the foreing field
    * generally return a string
    * @param string $table
    * @param string $labelField
    * @param int $foreignKey
    * @param string $db
    * @return $label
    */
  function getForeignLabel($table, $labelField, $foreignKey, $db) {
    $finalResult = array();
    if (strlen(trim($table)) < 1)
      return false;
    $query  = "SELECT DISTINCT $labelField FROM $table WHERE id ='$foreignKey'";
    $result = mysql_query($query, $db);
    $row = mysql_fetch_array($result);
    return $row[0];
  }


}
?>