/branches/messigdp/include/Database/save_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){

	    if($Object_name=='Utente') $value=str_replace("\'","'",$value);

        else $value=strtoupper(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.= ' NULL,';

        else{

          if(is_numeric($value)) $Insert_set.= ' '.$value.',';

          else $Insert_set.= ' \''.$value.'\',';

        }

      }

    }

    $Insert_set=rtrim($Insert_set, ',');

	

	if((substr($Object_tableName, -3, 3)!='Log')&&(substr($Object_tableName, -4, 4)!='File')) $altro=', 0';

	

    $query='INSERT INTO "'.$Object_tableName.'"

            VALUES ('.last_insert_id($Object_tableName).', '.$Insert_set.$altro.')';

    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){

	    if($Object_name=='Utente') $value=str_replace("\'","'",$value);

        else $value=strtoupper(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{

			  if(is_numeric($value)) $Update_set.= ' "'.str_replace($Object_name.'__','',$key).'"='.$value.',';

			  else $Update_set.= ' "'.str_replace($Object_name.'__','',$key).'"=\''.$value.'\',';

          	}

          }

        }

      }

    }

    $Update_set=rtrim($Update_set, ',');

    $query='UPDATE "'.$Object_tableName.'"

            SET '.$Update_set.'

            WHERE 1=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 = oci_parse($resourceLinkID, $query);

    while ($row = oci_fetch_array($result)){

      if ($field != $row["Field"])

        continue;

      if (ereg('enum.(.*).', $row['Type'], $match)) {

        $opts = explode(',', $match[1]);

        foreach ($opts as $item)

          $finalResult[] = substr($item, 1, strlen($item)-2);

      }

      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 = oci_fetch_array($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 = oci_fetch_array($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 = oci_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 = oci_fetch_array($result);

    return $row[0];

  }





}

?>