[Sommaire] [Imprimer]

   1  <?php
   2  /*********************************************************************************
   3   * The contents of this file are subject to the SugarCRM Public License Version 1.1.2
   4   * ("License"); You may not use this file except in compliance with the
   5   * License. You may obtain a copy of the License at http://www.sugarcrm.com/SPL
   6   * Software distributed under the License is distributed on an  "AS IS"  basis,
   7   * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
   8   * the specific language governing rights and limitations under the License.
   9   * The Original Code is:  SugarCRM Open Source
  10   * The Initial Developer of the Original Code is SugarCRM, Inc.
  11   * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.;
  12   * All Rights Reserved.
  13   * Contributor(s): ______________________________________.
  14   ********************************************************************************/
  15  
  16  //Fix postgres queries
  17  function fixPostgresQuery($query,$log,$debug)
  18  {
  19      // First select the query fields from the remaining query
  20      $queryFields = substr($query, strlen('select'), strpos($query,'FROM')-strlen('select'));
  21      $queryRecord = substr($query, strpos($query,'FROM'), strlen($query));
  22      $groupClause = "";
  23      $orderClause = "";
  24  
  25      if( $debug)
  26      $log->info( "fixPostgresQuery: ".$query);
  27  
  28      // If we already have an order or group cluase separate ist for later use
  29      if( strpos($queryRecord,'GROUP') > 0)
  30      {
  31      $groupClause = substr($queryRecord, strpos($queryRecord,'GROUP')+strlen('GROUP BY'), strlen($queryRecord));
  32      if( strpos($groupClause,'ORDER') > 0)
  33      {
  34          $orderClause = substr($groupClause, strpos($groupClause,'ORDER'), strlen($groupClause));
  35          $groupClause = substr($groupClause, 0, strpos($groupClause,'ORDER'));
  36      }
  37      $queryRecord = substr($queryRecord, 0, strpos($queryRecord,'GROUP'));
  38      }
  39  
  40      if( strpos($queryRecord,'ORDER') > 0)
  41      {
  42      $orderClause = substr($queryRecord, strpos($queryRecord,'ORDER'), strlen($queryRecord));
  43      $queryRecord = substr($queryRecord, 0, strpos($queryRecord,'ORDER'));
  44      }
  45  
  46      // Construkt the privateGroupList from the filed list by separating combined
  47      // record.field entries
  48      $privateGroupList = array();
  49      $token = strtok( $queryFields, ", ()    ");
  50      while( $token !== false) {
  51      if( strpos( $token, ".") !== false) {
  52          array_push( $privateGroupList, $token);
  53      }
  54      $token = strtok( ", ()    ");
  55      }
  56      sort( $privateGroupList);
  57      $groupFields = "";
  58      $last = "";
  59      for( $i = 0; $i < count($privateGroupList); $i++) {
  60      if( $last != $privateGroupList[$i]) {
  61          if( $groupFields == "")
  62          $groupFields = $privateGroupList[$i];
  63          else
  64          $groupFields .= ",".$privateGroupList[$i];
  65      }
  66      $last = $privateGroupList[$i];
  67      }
  68  
  69      // Rebuild the query
  70      $query = "SELECT ".$queryFields.$queryRecord." GROUP BY ";
  71      if( $groupClause != "" )
  72      $groupClause = $groupClause.",".$groupFields;
  73      else
  74      $groupClause = $groupFields;
  75      $query .= expandStar($groupClause,$log)." ".$orderClause;
  76  
  77      if( $debug)
  78      $log->info( "fixPostgresQuery result: ".$query);
  79  
  80      return( $query);
  81  }
  82  
  83  // Postgres8 will not accept a "tablename.*" entry in the GROUP BY clause
  84  function expandStar($fieldlist,$log)
  85  {
  86      $expanded="";
  87      $field = strtok( $fieldlist, ",");
  88      while( $field != "")
  89      {
  90      //remove leading and trailing spaces
  91      $field = trim( $field);
  92  
  93      //still spaces in the field indicate a complex structure
  94      if( strpos( $field, " ") == 0)
  95      {
  96  
  97          //locate table- and fieldname
  98          $pos = strpos( $field, ".");
  99          if( $pos > 0)
 100          {
 101          $table = substr( $field, 0, $pos);
 102          $subfield = substr( $field, $pos+1, strlen($field)-$pos);
 103  
 104          //do we need to expand?
 105          if( $subfield == "*") 
 106              $field = expandRecord($table,$log);
 107          }
 108  
 109          //add the propably expanded field to the querylist
 110          if( $expanded == "")
 111          $expanded = $field;
 112          else
 113          $expanded .= ",".$field;
 114      }
 115  
 116      //next field
 117      $field = strtok(",");
 118      }
 119  
 120      //return the expanded fieldlist
 121      return( $expanded);
 122  }
 123  
 124  //return an expanded table field list
 125  function expandRecord($table,$log)
 126  {
 127      $result = "";
 128      $log->info( "Debug: expandRecord");
 129      $subfields = array();
 130  
 131      //vtiger_products table
 132      if( $table == "vtiger_products" )
 133      $subfields = array ( "productid", "productname", "productcode", "productcategory", "manufacturer", "product_description", "qty_per_unit", "unit_price", "weight", "pack_size", "sales_start_date", "sales_end_date", "start_date", "expiry_date", "cost_factor", "commissionrate", "commissionmethod", "discontinued", "usageunit", "handler", "contactid", "currency", "reorderlevel", "website", "taxclass", "mfr_part_no", "vendor_part_no", "serialno", "qtyinstock", "productsheet", "qtyindemand", "glacct", "vendor_id", "imagename" );
 134  
 135      //vtiger_activity table
 136      elseif( $table == "vtiger_activity") 
 137      $subfields = array ( "activityid", "subject", "semodule", "activitytype", "date_start", "due_date", "time_start", "time_end", "sendnotification", "duration_hours", "duration_minutes", "status", "eventstatus", "priority", "location", "notime", "visibility" );
 138  
 139      //vtiger_notes table
 140      elseif( $table == "vtiger_notes")
 141      $subfields = array ( "notesid", "contact_id", "title", "filename", "notecontent");
 142  
 143      //vtiger_faq table
 144      elseif( $table == "vtiger_faq")
 145      $subfields = array ( "id", "product_id", "question", "answer", "category", "status");
 146  
 147      //vtiger_profile2field 
 148      elseif( $table == "vtiger_profile2field")
 149      $subfields = array ( "profileid", "tabid", "fieldid", "visible", "readonly");
 150  
 151      //vtiger_field 
 152      elseif( $table == "vtiger_field")
 153      $subfields = array ( "tabid", "fieldid", "columnname", "tablename", "generatedtype", "uitype", "fieldname", "fieldlabel", "readonly", "presence", "selected", "maximumlength", "sequence", "block", "displaytype", "typeofdata", "quickcreate", "quickcreatesequence", "info_type");
 154  
 155      //vtiger_activity
 156      elseif( $table == "vtiger_activity")
 157      $subfields = array ( "activityid", "subject", "semodule", "activitytype", "date_start", "due_date", "time_start", "sendnotification", "duration_hours", "duration_minutes", "status", "eventstatus", "priority", "location", "notime", "visibility");
 158  
 159      //fields of the requested array still undefined
 160      else
 161      $log->info("function expandRecord: please add structural information for table '".$table."'");
 162  
 163      //construct an entity string
 164      for( $i=0; $i<count($subfields); $i++)
 165      {
 166      $result .= $table.".".$subfields[$i].",";
 167      }
 168  
 169      //remove the trailiung ,
 170      if( strlen( $result) > 0)
 171      $result = substr( $result, 0, strlen( $result) -1);
 172      
 173      //return out new string
 174      return( $result);
 175  }
 176  ?>